Re: [sqlite] Is sqlite thread-safety sufficient for use with "Go" language ?

[jose isaias cabrera]

jus...@postgresql.org wrote...

On 2014-11-06 03:12, nicolas riesch wrote:

  There is a bug in some Linux implementations (RedHat9 is the 
canonical

example) that prevents fcntl() locks created by one thread from being
modified in a different thread.


As a data point, "Red Hat 9" is incredibly ancient, from 2003:

  http://en.wikipedia.org/wiki/Red_Hat_Linux#Version_history

While there is probably some someone - somewhere - still running on it
an ancient PC under a desk... it's not worth worrying about.


Ahem... and what is wrong with that? :-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it a bug ?

[Simon Slavin]

On 6 Nov 2014, at 3:13am, Andrei Yakimov  wrote:

> Problem is incorrect journal file, which is created on system reboot.
> Reproduce this condition relativity simple:
> 
> step 1:  we open db
> step 2:  write/update something to db.
> step 3:  switch journal to memory
> step 4:  write/update something to db.
> Do not close you SW keep it running and DB open
> step 5:  reboot your system or kill you SW not gracefully.

Please see



"The MEMORY journaling mode stores the rollback journal in volatile RAM. This 
saves disk I/O but at the expense of database safety and integrity. If the 
application using SQLite crashes in the middle of a transaction when the MEMORY 
journaling mode is set, then the database file will very likely go corrupt."

In other words, SQLite is operating as designed.  The ability to keep the 
journal in memory is supplied for databases where, if the system crashes, you 
would have to begin the task again anyway.  If you need your database to be 
recoverable after a crash, sorry, but you can't use that mode.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does using e.g. LTRIM function remove collation?

[Richard Hipp]

On Thu, Nov 6, 2014 at 2:15 PM, Michael Suodenjoki <
michael.suodenj...@intrasoft-intl.dk> wrote:

> Hi,
>
> I'm a bit disoriented by the following result - here using SQLite command
> shell:
>
> sqlite> .version
> SQLite 3.8.6 2014-08-15 11:46:33 9491ba7d738528f168657adb43a198238abde19e
> sqlite> CREATE TABLE test( col TEXT COLLATE NOCASE );
> sqlite> INSERT INTO test VALUES ('b'), ('A'), ('B'), ('a');
> sqlite> SELECT * FROM test;
> b
> A
> B
> a
> sqlite> SELECT * FROM test WHERE col<'b';
> A
> a
> sqlite> SELECT * FROM test WHERE LTRIM(col)<'b';
> A
> B
> A
>
> To me this result of the last SELECT statement is a bit unexpected. There
> are no space in the values, but using LTRIM function somehow removes
> collation (and uses the default BINARY collation). I would have expected
> the same result in the last two SELECT statements.
>
> Is this an error or something I haven't understood?
>

Works as designed.  See https://www.sqlite.org/datatype3.html#collation and
in particular the three rules under section 6.1.



>
> /Mike
>
> PS. Is there a way to see the resulting/deduced collation of a SELECT
> statement?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Does using e.g. LTRIM function remove collation?

[Michael Suodenjoki]

Hi,

I'm a bit disoriented by the following result - here using SQLite command shell:

sqlite> .version
SQLite 3.8.6 2014-08-15 11:46:33 9491ba7d738528f168657adb43a198238abde19e
sqlite> CREATE TABLE test( col TEXT COLLATE NOCASE );
sqlite> INSERT INTO test VALUES ('b'), ('A'), ('B'), ('a');
sqlite> SELECT * FROM test;
b
A
B
a
sqlite> SELECT * FROM test WHERE col<'b';
A
a
sqlite> SELECT * FROM test WHERE LTRIM(col)<'b';
A
B
A

To me this result of the last SELECT statement is a bit unexpected. There are 
no space in the values, but using LTRIM function somehow removes collation (and 
uses the default BINARY collation). I would have expected the same result in 
the last two SELECT statements.

Is this an error or something I haven't understood?

/Mike

PS. Is there a way to see the resulting/deduced collation of a SELECT statement?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is it a bug ?

[Andrei Yakimov]

Hi,
 I have using Sqlite for my small embedded projects for quite a while.
Recently we found a condition where is simple select all  does not operate
properly.
Problem is incorrect journal file, which is created on system reboot.
Reproduce this condition relativity simple:

step 1:  we open db
step 2:  write/update something to db.
step 3:  switch journal to memory
step 4:  write/update something to db.
Do not close you SW keep it running and DB open
step 5:  reboot your system or kill you SW not gracefully.

This reboot/kill will dump incorrect incorrect journal file to the file
system.
This will corrupt DB on next start.

I am using simple workaround:  switch journal to memory before any other db
access.
I will rebuild sqlite with journal to memory by default later.

This is reproduced very stable from 3.7.5  to latest release.
And I do not test  multiple  switching journal file/memory with update db
between switching.

I do not need fix for this. Just was thinking you would like to know about
this problem.

I appreciate what you are doing with this SW.

Andrei
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] virtual tables (was : SQLite as a meta database)

[Laurent Dami]

Hi there,

Since virtual tables were recenty mentioned in the thread "SQLite as a 
meta database" , I take this opportunity to announce that the lastest 
version of the  DBD::SQLite driver for Perl now has support for virtual 
tables (see https://metacpan.org/pod/DBD::SQLite ).


This means that you can implement virtual tables through Perl classes 
instead of writing C code, which (at least in my view) is much easier to 
write and to debug.


Laurent Dami



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is sqlite thread-safety sufficient for use with "Go" language ?

[John McKown]

On Thu, Nov 6, 2014 at 7:51 AM,  wrote:

> On 2014-11-06 03:12, nicolas riesch wrote:
> 
>
>>   There is a bug in some Linux implementations (RedHat9 is the canonical
>> example) that prevents fcntl() locks created by one thread from being
>> modified in a different thread.
>>
>
> As a data point, "Red Hat 9" is incredibly ancient, from 2003:
>
>   http://en.wikipedia.org/wiki/Red_Hat_Linux#Version_history
>
> While there is probably some someone - somewhere - still running on it
> an ancient PC under a desk... it's not worth worrying about.
>

​Present and accounted for! Yes, I have an old Pentium machine which is
from 2002. It is still running a small service for me. And it still running
RH9.​ And you're right. It's not worth worrying about.



>
> Just saying. ;)
>
> Regards and best wishes,
>
> Justin Clift
>


-- 
The temperature of the aqueous content of an unremittingly ogled
culinary vessel will not achieve 100 degrees on the Celsius scale.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is sqlite thread-safety sufficient for use with "Go" language ?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/05/2014 02:05 PM, nicolas riesch wrote:
> This means that EACH SUCCESSIVE function in the sequence above can
> be processed on a DIFFERENT OS THREAD.

That works just fine with SQLite, with one caveat.  You should also
make sure the wrapper itself is threadsafe.  For example what does the
wrapper do if you call close/finalize in one thread and step in
another concurrently?

The caveat is an implementation decision in SQLite and its error apis.
 Usually the OS error model is to have the errors be per thread.
SQLite instead has them be attached to the connection.  This means the
error apis return the most recent error for a connection no matter
which thread it happened on.  (In your case that is possibly
desireable.)  An example of how this can give the wrong information is
if thread 1 does a step followed by looking at the error info, but
between those two calls thread 2 does a sqlite operation on the same
database connection.  The error info thread 1 looks at could be from
its earlier call or from the the thread 2 call.

The second error issue is the api that returns a pointer to the error
string (sqlite3_errmsg).  By the time the pointer is used it could be
pointing to garbage or even now unmapped memory because a SQLite call
elsewhere on the connection caused that pointer to be freed.  Unmapped
memory will cause a crash, and who knows what the garbage will result in.

You can tell if a wrapper got multithreading right if the code looks
like this around every SQLite API call:

  // acquire db mutex
  sqlite3_db_mutex(db)
// make sqlite call
sqlite3_step()
// copy error details if previous gave an error
if (error) {
// make a copy of the error message
saved=strdup(sqlite3_errmsg(db));
}
  // release mutex

Note this has to be done for every sqlite call that can set the error
message which is approximately all of them.  Here for example is the
macro I use in my Python wrapper to do this:

https://github.com/rogerbinns/apsw/blob/master/src/util.c#L36

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlRbsk0ACgkQmOOfHg372QQMNQCfTfLUpodmuqnqUhe3tlXRAUBf
N7EAoJeUlu4Ir2h5WCHY9k1Ey9U7icm/
=vmiH
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] default ignore-glob for sqlite

[E. Timothy Uy]

Thank you Jan.

On Thu, Nov 6, 2014 at 7:46 AM, Jan Nijtmans  wrote:

> 2014-11-06 1:14 GMT+01:00 E. Timothy Uy :
> > Hi, I noticed that the ignore-glob for sqlite is empty. Is it just
> because
> > fossil does not transfer this information? What should I be using?
>
> I'm using the "ignore-glob" versioned setting below.
>
> Regards,
>   Jan Nijtmans
>
> $ cat .fossil-settings/ignore-glob
> tsrc/*
> *.db
> Makefile
> config.h
> config.log
> config.status
> keywordhash.h
> lemon
> lemon.exe
> lempar.c
> libsqlite3.la
> libtclsqlite3.la
> libtool
> mkkeywordhash
> mkkeywordhash.exe
> opcodes.c
> opcodes.h
> parse.c
> parse.h
> parse.h.temp
> parse.out
> parse.y
> shell.c
> sqlite3
> sqlite3.c
> sqlite3.exe
> sqlite3.h
> sqlite3.lo
> sqlite3.o
> sqlite3.pc
> sqlite3ext.h
> tclsqlite.lo
> tclsqlite.o
> testfixture
> testfixture.exe
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] default ignore-glob for sqlite

[Jan Nijtmans]

2014-11-06 1:14 GMT+01:00 E. Timothy Uy :
> Hi, I noticed that the ignore-glob for sqlite is empty. Is it just because
> fossil does not transfer this information? What should I be using?

I'm using the "ignore-glob" versioned setting below.

Regards,
  Jan Nijtmans

$ cat .fossil-settings/ignore-glob
tsrc/*
*.db
Makefile
config.h
config.log
config.status
keywordhash.h
lemon
lemon.exe
lempar.c
libsqlite3.la
libtclsqlite3.la
libtool
mkkeywordhash
mkkeywordhash.exe
opcodes.c
opcodes.h
parse.c
parse.h
parse.h.temp
parse.out
parse.y
shell.c
sqlite3
sqlite3.c
sqlite3.exe
sqlite3.h
sqlite3.lo
sqlite3.o
sqlite3.pc
sqlite3ext.h
tclsqlite.lo
tclsqlite.o
testfixture
testfixture.exe
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is sqlite thread-safety sufficient for use with "Go" language ?

[justin]

On 2014-11-06 03:12, nicolas riesch wrote:

  There is a bug in some Linux implementations (RedHat9 is the 
canonical

example) that prevents fcntl() locks created by one thread from being
modified in a different thread.


As a data point, "Red Hat 9" is incredibly ancient, from 2003:

  http://en.wikipedia.org/wiki/Red_Hat_Linux#Version_history

While there is probably some someone - somewhere - still running on it
an ancient PC under a desk... it's not worth worrying about.

Just saying. ;)

Regards and best wishes,

Justin Clift
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is sqlite thread-safety sufficient for use with "Go" language ?

[Stephan Beal]

On Thu, Nov 6, 2014 at 4:12 AM, nicolas riesch 
wrote:

>   http://www.sqlite.org/cvstrac/wiki?p=MultiThreading


Be aware that the cvstrac pages are all historical, possibly outdated, and
no longer maintained.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] default ignore-glob for sqlite

[Stephan Beal]

On Thu, Nov 6, 2014 at 1:14 AM, E. Timothy Uy  wrote:

> Hi, I noticed that the ignore-glob for sqlite is empty. Is it just because
> fossil does not transfer this information? What should I be using?
>

http://sqlite.org/src/dir

sqlite doesn't have a .fossil-settings dir, i.e. no versioned settings, so
no, they're not synced with clones.
-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a meta database

[Andrea Peri]

SQLite allow to attach more database at session level.
Perhaps here you could find a solution
 Il 06/nov/2014 09:31 "Hugo Mercier"  ha scritto:

> Le 05/11/2014 23:08, Simon Slavin a écrit :
> >
> > On 5 Nov 2014, at 9:28pm, Edward Lau  wrote:
> >
> >> Maybe some time in the future a version 4 be started that incorporates
> many new advancement in the industry.  Version 3 can still be continued for
> backwards compatibility and version 4 will break some but set the stage for
> the future.
> >
> > Like this, you mean ?
> >
> > 
> >
> > You might be particularly interested in the fact that there will be two
> different types of storage engine, both pluggable:
> >
> > 
> >
> > I don't understand the details on that page, but one of those two looks
> like it may allow more flexibility for external data supply than the
> current virtual table implementation.  But I may have misunderstood that.
> >
>
> Thanks for these inputs.
>
> Concurrent execution of SQLite opcodes would be great. But it is far
> from our current needs :)
>
> About pluggable storage, that's interesting, but if I understand
> correctly, there could only be one pluggable storage per database, right
> ? What if you want many foreign tables with different (virtual) storages ?
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a meta database

[Hick Gunter]

xCreate should specify "CREATE TABLE x (label TEXT);" which assumes that rowid 
is the implicit index

xBestIndex needs to return:

1) cost = 1, index = 0  if a single constraint -1 (rowid) and operation 
EQ is provided (assuming the record position can be calculated from the index)
2) cost = n/2, index = 0if a single constraint -1 (rowid) and 
operation LE/GE/LT/GT is provided (as on average, half the records need to be 
searched)
3) cost = ld(n), index = 1  if a single constraint 0 (label) and operation 
EQ is provided (assuming the associative array is implemented via a binary tree)
4) cost = n/2, index = 1if a single constraint 0 (label) and 
operation LE/GE/LT/GT/MATCH is provided (as on average, half the records need 
to be searched)
5) cost = n, index = 0  otherwise (if more than one constraint is 
supplied, pick your favorite)

If a constraint is used, the argv index needs to be set, so ist value can be 
retrieved by xFilter.

Minimally you need to implement 1, 3 and 5. You can implement 2 and 4 later, 
when the others work. I would suggest storing the operation in the idxString 
variable.

xFilter needs to consider the cases:

1) set the current record to the one indexed by the passed index, set cursor to 
expire when xNext is called
3) set the current record to the one located via the passed text, set cursor to 
expire when xNext is called
5) set the current record to the first one, xNext needs to iterate until the 
last record was returned
2) set the record range according to the operation and the passed index, xNext 
needs to point to the record in the range in order
4) set the record range accoridng tot he operation and the passed text, xNext 
needs to point to the record in the range in order

Note that all cases are specializations of "set range" and "set iterator 
function".

You can add processing of the ORDER BY clause later.

-Ursprüngliche Nachricht-
Von: Mike Beddo [mailto:mike.be...@dataventures.com]
Gesendet: Donnerstag, 06. November 2014 01:10
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] SQLite as a meta database

Hi,

I am intruding into this thread, because I face a similar issue. At our company 
we have a "proprietary" database storing customer grocery transactions which 
basically only a C programmer can get to. It is by no stretch of the 
imagination a "relational" database. Basically, everything is an integer and we 
rely on associative tables in our C codes to map integers back into human 
readable labels for output purposes. For instance, a particular UPC 
"0" might map to integer 123, so we work with 123 in our codes, and 
when we output the results we map 123 back to "0".

There is intense interest in providing a SQL front-end. I thought to start 
with, I'd see if I could develop a virtual table for our various associative 
tables. Doing this would help educate me before embarking on the bigger chore 
of handling the database itself. Sounds simple enough, but I could use some 
help/advice.

There are several associative tables that I would need a virtual table for: 
items, stores, customers. I thought something like

CREATE VIRTUAL TABLE items USING aa_module('/path/to/database', 'items');

The arguments to aa_module (the thing I'm trying to write) give the path to the 
database to open, and which associative map to create a virtual table for.

If we think of 'items' as a pointer to the items associative array, for 
instance, then in our code we would use expressions like:

int inx = AA_inx(items, '0'); /* gives the index associated with 
upc 0 */ char *upc = AA_lbl(items, 123); /* gives the upc string 
associated with item inx 123 */

The table create string to use in xCreate/xConnect would be "CREATE TABLE x(inx 
INTEGER, upc VARCHAR(13));"I think that in my xBestIndex function that if the 
constraint involves equality then the cost is 1 lookup, but for any other type 
of constraint the cost is the number of items in the AA map, since this thing 
isn't sorted.

Could someone give me an idea of what a minimal xBestIndex/xFilter skeleton 
might look like? I walked though "ext/misc/amatch.c" from the 
www.sqlite.org/src/artifact tree, but I'm a little lost.

Thanks,

Mike Beddo

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Hick Gunter
Sent: Wednesday, November 05, 2014 6:16 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] SQLite as a meta database

Hi,

we have extensive experience with respect to the use of virtual tables in 
SQLite. In fact, the only native SQLite tables we use are in a configuration 
checking tool.

We have "providers" from in-memory indexed tables, CTree (r) files, Oracle 
tables (read only), structured disk files, in-memory structures, binary 
records, etc.

The trick is to be able to formulate your queries solely via comparison 
oper

Re: [sqlite] SQLite as a meta database

[Hugo Mercier]

Le 05/11/2014 23:08, Simon Slavin a écrit :
> 
> On 5 Nov 2014, at 9:28pm, Edward Lau  wrote:
> 
>> Maybe some time in the future a version 4 be started that incorporates many 
>> new advancement in the industry.  Version 3 can still be continued for 
>> backwards compatibility and version 4 will break some but set the stage for 
>> the future.
> 
> Like this, you mean ?
> 
> 
> 
> You might be particularly interested in the fact that there will be two 
> different types of storage engine, both pluggable:
> 
> 
> 
> I don't understand the details on that page, but one of those two looks like 
> it may allow more flexibility for external data supply than the current 
> virtual table implementation.  But I may have misunderstood that.
> 

Thanks for these inputs.

Concurrent execution of SQLite opcodes would be great. But it is far
from our current needs :)

About pluggable storage, that's interesting, but if I understand
correctly, there could only be one pluggable storage per database, right
? What if you want many foreign tables with different (virtual) storages ?


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users