[sqlite] Lock files....

[Eddy Macnaghten]

Hi there

I am new to this list, so please excuse me if this has been covered
before, or is not the right place for this.

I am writing a development language/environment that has a SQL back
end.  I am (planning to) put in a number of SQL engine connectivities,
and SQLite is ideal for the low end type implementations - that is a
small database (less than 1,000,000 records in any table) small number
of users (5 or less).

However, reading the documentation it seems that SQLite is not hot on
concurrent access through networks, or across platforms, due to the
funnies of fcntl, or incompatibilities between Windows and Linux and so
on.

Now SQLite would be ideal for the low end implementation (being a client
oritented database there is hardly any administration and so on),
however, I will need it to be multi user - not much multi - 5 users or
so - but still multi user, and to be so across platforms.

To achieve this I have written a mechanism that creates a "lock" file,
(in the same directory as the database, with the same name with ".lock"
after it).  This is used by SQLite clients to register, and deregister
locks (what fcntl would do under the current Linux clients).  I have
designed it so it does not use fcntl, but could be used to perform the
locking SQLite requires to guarantee concurrency and integrity.

Of course, when this locking mechanism is in use there is a performance
hit, as extra network processing and read/writes are required each time
a lock is required, so therefore this is not right for all scenarios,
though the performance hit would probably not be significant for what I
want to use it for.

Ideally, I would like an indicator in the SQLite database file header
record to determine if this file locking is required, and set
accordingly when the database is created, and possibly be switched on
and off with a PRAGMA command or similar, or maybe it's own utility.

What are people's views on this?

-- 
Edward A. Macnaghten
http://www.edlsystems.com

Re: [sqlite] cross-compile sqlite

[Doug Currie]

Thursday, September 23, 2004, 6:01:40 PM, Daniel wrote:

> [...] I want to use the
> configure-script. Can you give me a hint?
> I tried --prefix=($PREFIX) --host=cris-axis-linux-gnu
> --target=cris-axis-linux-gnu --build=cris-axis-linux-gnu.

--host=cris-axis-linux-gnu ???

> [...]
 
> The last lines from the output:

> [...]
> gcc-cris -isystem
> /var/axis/axis/devboard_82/target/cris-axis-linux-gnu/include
> -mlinux -g -O2 -o temp temp.c
> ./temp >config.h
> /bin/sh: line 1: ./temp: cannot execute binary file

./temp must be compiled for the target, but is run on the host.

This seems unlikely to work in general!

Perhaps you can create a config.h file manually and remove it as a
target of the Makefile.

e

Re: [sqlite] cross-compile sqlite

[Daniel=20W=FCrfel]

Thank you for your reply.
But it doesn't help me really. I want to use the configure-script. Can you give me a 
hint?
I tried --prefix=($PREFIX) --host=cris-axis-linux-gnu --target=cris-axis-linux-gnu 
--build=cris-axis-linux-gnu. This has worked in combination to a target-makefrag in 
the topleveldirectory and some exported environment variables, when I compiled BlueZ 
for the devboard. $PREFIX is the target directory.
I commented out some lines in the configure script which created an error when 
crosscompiling.
tcl.h and readline.h are'nt found while configuring. When making install, it seems a 
binary file created by cris-gcc should be executed, but it doesn't work.
Do you have an idea?
Regards
Daniel
 
The last lines from the output:

checking for library containing Tcl_Init... no
checking TCL header files... not specified: still searching...
checking tcl.h usability... no
checking tcl.h presence... no
checking for tcl.h... no
checking for /usr/local/include/tcl.h...
../configure: line 20030: test: =: unary operator expected
checking for /usr/X11/include/tcl.h...
../configure: line 20030: test: =: unary operator expected
checking for /usr/X11R6/include/tcl.h...
../configure: line 20030: test: =: unary operator expected
checking for /usr/pkg/include/tcl.h...
../configure: line 20030: test: =: unary operator expected
checking for /usr/contrib/include/tcl.h...
../configure: line 20030: test: =: unary operator expected
checking for /usr/include/tcl.h...
../configure: line 20030: test: =: unary operator expected
checking for library containing tgetent... no
checking for readline in -lreadline... no
checking readline header files... not specified: still searching...
checking readline.h usability... no
checking readline.h presence... no
checking for readline.h... no
checking for /usr/include/readline.h...
../configure: line 20437: test: =: unary operator expected
checking for /usr/include/readline/readline.h...
../configure: line 20463: test: =: unary operator expected
checking for /usr/local/include/readline.h...
../configure: line 20437: test: =: unary operator expected
checking for /usr/local/include/readline/readline.h...
../configure: line 20463: test: =: unary operator expected
checking for /usr/local/readline/include/readline.h...
../configure: line 20437: test: =: unary operator expected
checking for /usr/local/readline/include/readline/readline.h...
../configure: line 20463: test: =: unary operator expected
checking for /usr/contrib/include/readline.h...
../configure: line 20437: test: =: unary operator expected
checking for /usr/contrib/include/readline/readline.h...
../configure: line 20463: test: =: unary operator expected
checking for /mingw/include/readline.h...
../configure: line 20437: test: =: unary operator expected
checking for /mingw/include/readline/readline.h...
../configure: line 20463: test: =: unary operator expected
checking for usleep... yes
configure: creating ./config.status
config.status: creating Makefile
config.status: creating sqlite3.pc
make -C sqlite/build
make[2]: Entering directory `/var/axis/axis/devboard_82/apps/database/sqlite/build'
sed -e s/--VERS--/`cat ../VERSION`/ \
../src/sqlite.h.in >sqlite3.h
echo '#include ' >temp.c
echo 'int main(){printf(' >>temp.c
echo '"#define SQLITE_PTR_SZ %d",sizeof(char*));' >>temp.c
echo 'exit(0);}' >>temp.c
gcc-cris -isystem /var/axis/axis/devboard_82/target/cris-axis-linux-gnu/include 
-mlinux -g -O2 -o temp temp.c
./temp >config.h
/bin/sh: line 1: ./temp: cannot execute binary file
make[2]: *** [config.h] Error 126
make[2]: Leaving directory `/var/axis/axis/devboard_82/apps/database/sqlite/build'
make[1]: *** [buildsqlite] Error 2
make[1]: Leaving directory `/var/axis/axis/devboard_82/apps/database'
make: *** [install-recurse] Error 1
[EMAIL PROTECTED] devboard_82]#


> > I want to crosscompile sqlite to an axis devboard82. I use gcc-cris 
> > (cris-dist-1.56).
> > Has anybody gained general experience with crosscompiling sqlite?
> > How have I to define options as host, build, etc.? Have I to use the "with 
> > package"-options?
> > Any feedback appreciated, thanks in advance,
> > Daniel
> > 
> 
> The windows binaries on the website are cross-compiled on a Linux system.
> The script that does everything is in the top-level directory of the source
> tree and is named publish.sh.  You might look at it for ideas.
> 
>http://www.sqlite.org/cvstrac/getfile/sqlite/publish.sh
> 
> 
> -- 
> D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
> 



Aufnehmen, abschicken, nah sein - So einfach ist 
WEB.DE Video-Mail: http://freemail.web.de/?mc=021200

Re: [sqlite] Need Type info with Views

[EzTools Support]

I'm just wondering why I have not received any comment regarding this 
issue...?

EzTools Support wrote:
Hello DRH.  This is further to the previous query regarding no type 
information being returned with Views.  I had previously asked if this 
could be added, but have not received any comment.

I'm sure anyone would agree that getting the column type data type 
back with views is no less important than getting it back with 
SELECTs.  Why would it be? I need type info for any row-returning SQL 
command, be if from directly via SELECTs or indirectly from Views.  
Until this is fixed, I must tell my 3.0 customers they can't use Views 
:-(

regards
-brett

Re: [sqlite] Encryption?

[Will Leshner]

Bryan Ashby wrote:
I have searched around and cannot find a definite answer -- We are working on a project that _requires_ a small, embedded and encrypted database. SQLite fits all of our needs except for the encryption part .. which I'm not totally sure about. I notice that in sqlite3.h there is a sqlite3_key() function, though it states it is not available to the public. Why is this? How can we add our own encryption (without breaking SQL searches, etc.!). I see how sqlite3_key() is used in the tcl stuff but I don't have any code for it :(

[sqlite] Encryption?

[Bryan Ashby]

I have searched around and cannot find a definite answer -- We are working on a 
project that _requires_ a small, embedded and encrypted database. SQLite fits all of 
our needs except for the encryption part .. which I'm not totally sure about. I notice 
that in sqlite3.h there is a sqlite3_key() function, though it states it is not 
available to the public. Why is this? How can we add our own encryption (without 
breaking SQL searches, etc.!). I see how sqlite3_key() is used in the tcl stuff but I 
don't have any code for it :(

Please help!

Thanks,

Bryan Ashby

[sqlite] Performance of using Intersect; sanity check for newbie

[Ken Cooper]

I'm implementing a simple full text search in a sqlite database, using an
additional table for the indexed words, associated record ids, and word
offsets. A typical query will have multiple keywords, plus some additional
fields specified.

 

CREATE TABLE words  (word TEXT, recordid INTEGER, word_offset INTEGER);

CREATE TABLE labels (label TEXT, recordid INTEGER);

 

I'm thinking of writing my query like this:

 

SELECT recordid FROM words WHERE word = "whitebeard"

INTERSECT

(SELECT recordid FROM words WHERE word = "wizard"

INTERSECT 

(SELECT recordid FROM labels WHERE label = "well-hidden"))

 

My question is, is this an efficient way to execute such a query? Are each
of the individual selects performed independently, or do they filter based
on the intersect? I will be able to determine which keywords are likely to
return more results, and therefore can order my selects. Right now I'm
operating under the assumption that they filter based on the intersect,
querying from left to right.

 

Thanks,

 

Ken

 

Re: [sqlite] cross-compile sqlite

[D. Richard Hipp]

Daniel Würfel wrote:
I want to crosscompile sqlite to an axis devboard82. I use gcc-cris (cris-dist-1.56).
Has anybody gained general experience with crosscompiling sqlite?
How have I to define options as host, build, etc.? Have I to use the "with 
package"-options?
Any feedback appreciated, thanks in advance,
Daniel
The windows binaries on the website are cross-compiled on a Linux system.
The script that does everything is in the top-level directory of the source
tree and is named publish.sh.  You might look at it for ideas.
  http://www.sqlite.org/cvstrac/getfile/sqlite/publish.sh
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

[sqlite] cross-compile sqlite

[Daniel=20W=FCrfel]

I want to crosscompile sqlite to an axis devboard82. I use gcc-cris (cris-dist-1.56).
Has anybody gained general experience with crosscompiling sqlite?
How have I to define options as host, build, etc.? Have I to use the "with 
package"-options?
Any feedback appreciated, thanks in advance,
Daniel


Verschicken Sie romantische, coole und witzige Bilder per SMS!
Jetzt neu bei WEB.DE FreeMail: http://freemail.web.de/?mc=021193

RE: [sqlite] In-Memory Performance Comparisons

[Drew, Stephen]

Many thanks. 

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 23, 2004 1:46 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] In-Memory Performance Comparisons

Drew, Stephen wrote:
>  
> Can anyone tell me (or point me to any documentation) about the 
> differences in speed between using an on-disk DB and an in-memory one?
>  

Try it for yourself.  Like this:

drh$ rm test.db
drh$ time sqlite3 test.db /dev/null
real0m2.908s
user0m1.683s
sys 0m0.291s
drh$ time sqlite3 :memory: /dev/null
real0m1.697s
user0m1.635s
sys 0m0.063s

Your mileage will vary according to the content of workload.sql, of
course.  As a rule of thumb, the user time (the amount of time the
process spends in user mode) will be similar.  But for an in-memory
database, the sys time (the amount of time used by the
OS) will be much less, since there is no disk I/O.  And realtime (a.k.a.
wallclock time) will also typically be less since there are no waits for
disk controllers.

--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

Re: [sqlite] In-Memory Performance Comparisons

[D. Richard Hipp]

Drew, Stephen wrote:
 
Can anyone tell me (or point me to any documentation) about the 
differences in speed between using an on-disk DB and an in-memory one?
 
Try it for yourself.  Like this:
   drh$ rm test.db
   drh$ time sqlite3 test.db /dev/null
   real0m2.908s
   user0m1.683s
   sys 0m0.291s
   drh$ time sqlite3 :memory: /dev/null
   real0m1.697s
   user0m1.635s
   sys 0m0.063s
Your mileage will vary according to the content of workload.sql,
of course.  As a rule of thumb, the user time (the amount of time
the process spends in user mode) will be similar.  But for an
in-memory database, the sys time (the amount of time used by the
OS) will be much less, since there is no disk I/O.  And realtime
(a.k.a. wallclock time) will also typically be less since there
are no waits for disk controllers.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

Re: [sqlite] In-Memory Performance Comparisons

[WeiChin3]

 
In a message dated 9/23/2004 7:55:20 AM Eastern Daylight Time,  
[EMAIL PROTECTED] writes:

Can anyone tell me (or  point me to any documentation) about the differences 
in speed between using an  on-disk DB and an in-memory one?



The speed difference would depends on how you use the database.
 
If you use the default mode, which is synchronous=full, and do a lot of  
insert/update/delete without transaction, you can gain speed 100 folder by using  
a in-memory database.
 
However, if you do a lot of insert/update/delete, but wrap them in a  
transaction, then the performance gain is much much less.
 
Wei
 
 
 
 

[sqlite] In-Memory Performance Comparisons

[Drew, Stephen]

Hello 
all,
 
Can anyone tell me (or 
point me to any documentation) about the differences in speed between using an 
on-disk DB and an in-memory one?
 
Regards,
Steve

Re: [sqlite] legacy sources

[D. Richard Hipp]

Miguel Angel Latorre wrote:
The encode and decode sources were removed from the src dir, it would be
nice to have them such files and alike (md5, etc) in the csv contrib dir.

The complete CVS source tree, including all historical versions of
all files, is available on the website.
  http://www.sqlite.org/cvstrac/dir?d=sqlite
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

Re: [sqlite] ON INSERT trigger

[Alex]

> If I have time at the weekend, I might have a bash at producing a patch
> that will implement what is required. No promises, mind.
>
> What would be preferred? Convert the trigger to an update trigger, or fire
> a delete trigger followed by an insert trigger?
>
> I'd prefer the former (update) as requested in the ticket. I think it may
> be easier to code, as well, but not sure.

Yes, I agree, firing the UPDATE trigger would be better. But I would also be
satisfied with an additional DELETE trigger call. BTW, if you release this
patch, which SQLite version will it be available for? Only 3.x or also 2.x?

> What is stopping you checking for an existing row in the trigger body
> yourself? Something like (based on your example):
>   create trigger table_replace_check
>   before insert on 'table'
>   for each row
>   begin
> insert into table_log
>   select 'D', id, SecDbKey from 'table'
>   where id=NEW.id;
>   end;
>
> Then you just keep a regular post insert trigger to put the new updated
> values into the log.

The example I posted is a simple case I use for testing. Other people will
create different tables with differend fields, and I have no idea how these
tables will look like. So, how can I code a trigger for an arbitrary table
without doing havy parsing of the incoming "CREATE TABLE" query? No, firing
the UPDATE trigger is definitely a better solution :)

Regards,
Alex

[sqlite] Can i use the command line tools--"sqlite" creating a database file ?

[duyu]

   -