Re: [sqlite] Database locked version 3.3.17
Resolved the issue. My issue on the app side. I think the newer version is much faster. So much so that it changed timing characteristics and reveald a logic bug in my code. Appologies, Ken Ken <[EMAIL PROTECTED]> wrote: Hi all, Think I may have hit some form of a bug in 3.3.17: At an insert statement I'm getting a rc=[5] msg=[database is locked] When I recompile the sqlite using version 3.3.13 and re-run the appliation. No problems. Sorry this is pretty vague.. I'll try digging in more and finding more details to reproduce a simple test case. Ken
[sqlite] Database locked version 3.3.17
Hi all, Think I may have hit some form of a bug in 3.3.17: At an insert statement I'm getting a rc=[5] msg=[database is locked] When I recompile the sqlite using version 3.3.13 and re-run the appliation. No problems. Sorry this is pretty vague.. I'll try digging in more and finding more details to reproduce a simple test case. Ken
Re: [sqlite] sql stack using sqlite
I think i found an ok solution: (note I've updated my own insert statements from the OP) drop table purg; create temporary table purg (id integer primary key, tbl); -- Use the fact that the Puging tables C unique ID is always after table A's data. insert into purg select distinct kd.id, 'a' from c, a where c.id = a.id and c.cnt = a.cnt and c.ref = a.ref and a.id < c.id ; -- For ech a record to be purged. Join it to get the ref value and the table B id's insert into purg select distinct b.id, 'b' from purg p, b, a where p.id = a.id and a.id = b.id and a.ref = b.ref ; insert into purg select c.id, 'c' from c; --- Now purge. delete from a where id in (select id from purg where tbl = 'a'); delete from b where id in (select id from purg where tbl = 'b'); delete from c where id in (select id from purg where tbl = 'c'); Any ideas of a better way? Thanks, Ken Ken <[EMAIL PROTECTED]> wrote: Id like to get your ideas on implementing a stack using sql tables. table a, contains references to b table b contains refernce to a table c contains delete entries for A (but b must also be purged!) My processing forces me to load all of a,b and c. There may be cases where table C indicates a complete deletion for table A. As a special case there is another table D that indicates a complete delete. In this instance I can delete by another unique Id that is contained in all of the tables but ommitted for brevity. create table a ( id integer, ref integer, cnt integer ); create table b ( id integer, ref integer, val text ); create table c ( id integer, ref, cnt integer ); insert into a values (1,32,5); insert into b values (11,32,'first data item'); insert into c values (2,32,5) ; insert into a values (2,33,5); insert into b values (12,33,'second data item'); insert into c values (3,5) ; insert into a values (4,34,5); insert into b values (13,34,'third data item'); After processing, Id like to be left with the following: a ( 4, 34,5) b (13, 34, 'third data item') This is easily implemented in a memory stack. but I'm not sure how to implement using sql. thanks for any ideas. Ken
[sqlite] sql stack using sqlite
Id like to get your ideas on implementing a stack using sql tables. table a, contains references to b table b contains refernce to a table c contains delete entries for A (but b must also be purged!) My processing forces me to load all of a,b and c. There may be cases where table C indicates a complete deletion for table A. As a special case there is another table D that indicates a complete delete. In this instance I can delete by another unique Id that is contained in all of the tables but ommitted for brevity. create table a ( id integer, ref integer, cnt integer ); create table b ( id integer, ref integer, val text ); create table c ( id integer, cnt integer ); insert into a values (1,32,5); insert into b values (11,32,'first data item'); insert into c values (1,5) ; insert into a values (1,33,5); insert into b values (11,33,'second data item'); insert into c values (1,5) ; insert into a values (1,34,5); insert into b values (11,34,'third data item'); After processing, Id like to be left with the following: a ( 1, 34,5) b (11, 34, 'third data item') This is easily implemented in a memory stack. but I'm not sure how to implement using sql. thanks for any ideas. Ken
Re: [sqlite] perfomance degradation for expr "foo = X or bar =X"
[EMAIL PROTECTED] wrote:Note that some client/server database engines (ex: PostgreSQL and I think also firebird) will automatically rewrite the original query into something logically similar to my second example above. But the query optimizer in SQLite does not attempt to be quite that clever. -- D. Richard Hipp I think that is a good thing that sqlite isn't that clever! I've seen too many cases of advanced optimizers such as Oracle totally mess up the execution plans for complex query operations. There are simply too many choices for the optimizer to pick the best plan. Ken
Re: [sqlite] reseting primary key
DRH SAID: [EMAIL PROTECTED] wrote:The details of the UPDATE or DELETE are left as an exercise for the reader. Excellent, it reminds me of High School integral proofs. Q.E.D.
Re: [sqlite] Starving for New User Information
The .a file is for static linking. The .sl file is for dynamic linking (shared library) The .lib file is hidden because you were supposed to do a "make install" after make which would install the .libs into whatever "prefix" you gave to configure... Static linking makes it easier for you to deliver a fully embedded single executable. Shared linking is good when you have many excutables and want/need to save some memory space. When the executable runs, it will load the .sl (and resolve function address). Then any other code that runs (assuming lib paths are set correct) will be able to use the same exact sqlite .sl file (in memory now).. Thus a net savings. Its also good when you don't want to rebuild and deploy an entire project when all you need is to install the next version of sqlite( say there is a sqlite bug fix or feature etc). When using HPUX, be sure to look at the chatr attrbutes, you may need to enablethe shared_lib_path variable for your binary in order for it to access your shared_lib_path environment variable when using dynamic linking. km4hr <[EMAIL PROTECTED]> wrote: C.Peachment, You're right, I'm not the smartest or best programmer. I know that. But I often get the job done in spite of my handicaps. To keep from overloading the circuit I'll try to limit the number of questions to one at a time. I'm trying to install sqlite on HPUX. There's no HPUX install package on the sqlite download web site. So I downloaded the sources. The HPUX 11.0 make utility didn't like sqlite's make files. In spite of my handicaps I notice that I have GNU bash installed. I tried the the make utility that comes with bash. Bingo! It worked. So now I have a "bld" directory full of stuff. But I don't see a sqlite library file. I look everywhere. Where is it? In spite of my slowness my tenacity is not lacking. I keep looking. Using "find" I discover libsqlite files in a ".libs" (hidden) directory. Very sneaky. Why would anyone hide the directory holding the most important files? I guess to help weed out the underprivileged. But I'm not the dullest knife in the drawer. I found it! I'm getting around to asking my one question. I see two library files in the ".libs" directory. One named "libsqlite3.sl" and one named "libsqlite3.a". Typically a ".a" extension indicates a library for static linking. So here's my question. What's the ".a" file for? As I said earlier it makes me think it might be possible to create executables that can be run on similar machines without first installing sqlite. I'll guess I'll keep plodding along to see if I can figure it out on my own. I'm going to try to link some files now. That can be a real challenge in itself. I guess if I have linking questions I ask them on an HPUX site. Thanks for the advice. C.Peachment wrote: > > You ask too many questions in one go. I will be surprised if anyone > is willing to provide that much assistance all at once. > > It appears that you need some more fundamental knowledge about > programming on GNU/Linux and maybe even about programming in C. > > Some of your questions are contradictory and suggest you have not > done your own homework before asking others to do it for you. > > e.g. > got a ".so" file. This is obviously a shared library ... > > and > > Is a C program that uses sqlite statically linked? > > You ask: >>Once I've created a C program that uses sqlite can I administer its database >>with sqlite3 or do I have to write utility programs to create a database, >>tables, view table contents, etc? > > Why not try it and see for yourself? > > Others have done so and not all of them are smarter than you :-) > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/I%27m-Starving-for-New-User-Information-tf3701471.html#a10361035 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] May I ask why the source distribution mechanism was changed starting with 3.3.14?
time gcc -m32 -O2 -I. -I../sqliteSrc/sqlite-3.3.17/src -DNDEBUG -DTHREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -c sqlite3.c -fPIC -DPIC -o .libs/sqlite3.o real0m20.266s user0m19.773s sys 0m0.444s time gcc -m32 -O2 -I. -I../sqliteSrc/sqlite-3.3.17/src -DNDEBUG -DTHREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -c sqlite3.c -o sqlite3.o >/dev/null 2>&1 real0m13.050s user0m12.405s sys 0m0.336s Linux suse 10.0 (amd x2 3800) gcc version 4.0.2 20050901 (prerelease) (SUSE Linux) [EMAIL PROTECTED] wrote: "C.Peachment" wrote: > With the suggestion that the problem was a compiler bug > in PellesC for Windows, I posted a message on their forum. > One response suggested a couple of configuration changes > and also said to wait a while because it took a long time to > compile. > Fascinating. GCC (version 4.1.0) takes less than 25 seconds to compile and link a command-line shell using the amalgamation with full optimization (-O3) - which as far as I know is as slow as you can make the compiler run. With all optimization turned off, the whole thing builds in 3.430 seconds (wall-clock time). One of the reasons proffered for not using the amalgamation is that compiles go faster if you do not have to recompile the whole thing. I read that and thought "4 seconds is too slow?" But perhaps GCC is just a really fast compiler and the person who said that is just using a much slower compiler. I'm curious to see a comparison of the size and speed of the executables that result from GCC and its more sluggish competitors. Does anybody have any data? -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] - [EMAIL PROTECTED] wrote: "C.Peachment" wrote: > With the suggestion that the problem was a compiler bug > in PellesC for Windows, I posted a message on their forum. > One response suggested a couple of configuration changes > and also said to wait a while because it took a long time to > compile. > Fascinating. GCC (version 4.1.0) takes less than 25 seconds to compile and link a command-line shell using the amalgamation with full optimization (-O3) - which as far as I know is as slow as you can make the compiler run. With all optimization turned off, the whole thing builds in 3.430 seconds (wall-clock time). One of the reasons proffered for not using the amalgamation is that compiles go faster if you do not have to recompile the whole thing. I read that and thought "4 seconds is too slow?" But perhaps GCC is just a really fast compiler and the person who said that is just using a much slower compiler. I'm curious to see a comparison of the size and speed of the executables that result from GCC and its more sluggish competitors. Does anybody have any data? -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] - [EMAIL PROTECTED] wrote: "C.Peachment" wrote: > With the suggestion that the problem was a compiler bug > in PellesC for Windows, I posted a message on their forum. > One response suggested a couple of configuration changes > and also said to wait a while because it took a long time to > compile. > Fascinating. GCC (version 4.1.0) takes less than 25 seconds to compile and link a command-line shell using the amalgamation with full optimization (-O3) - which as far as I know is as slow as you can make the compiler run. With all optimization turned off, the whole thing builds in 3.430 seconds (wall-clock time). One of the reasons proffered for not using the amalgamation is that compiles go faster if you do not have to recompile the whole thing. I read that and thought "4 seconds is too slow?" But perhaps GCC is just a really fast compiler and the person who said that is just using a much slower compiler. I'm curious to see a comparison of the size and speed of the executables that result from GCC and its more sluggish competitors. Does anybody have any data? -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] May I ask why the source distribution mechanism was changed starting with 3.3.14?
100% concur with Dennis. Thanks again for a great product! Dennis Cote <[EMAIL PROTECTED]> wrote: [EMAIL PROTECTED] wrote: > > Can somebody please explain to my how 2 files is less manageable > than 60? > > > Richard, I think part of the problem is simple inertia. Some people have developed a methodology for using the sqlite source files based on the previous arrangement. They may have patches they apply to the files, existing makefiles, etc. The new amalgamation file breaks those methods. It is not backwards compatible. They had a working method and your change has broken that method. It's not that they can't make it work with the amalgamation, but rather that they don't think they should have to change their processes unnecessarily. It is also often easier to work with the individual source files rather than the amalgamation. Loading, searching and scrolling in a editor are often easier with smaller files, and it's often handy to have several files open to different locations when studying the source, which can be difficult with a single file in many editors. I really think most people who are asking for the individual files would be better served using CVS to get a particular version (if they don't want to track the latest developments in the head versions). Unfortunately, you make this a little harder than it needs to be by not tagging your releases in CVS. The lack of tags requires users to find the release date and time from the website's timeline page before retrieving the files based on that date and time. It would be easier to just select a particular CVS release tag. Using CVS has one drawback for many Windows users though, it doesn't provide the preprocessed source files. They still need to install a unix like toolset such as MinGW/MSYS or Cygwin to use the makefiles which generate those files. This adds additional complexity. I have used all the available methods at various times for various reasons. Now, I most often use the files you have prepared and distribute through your website. It is simply easier for me to let you do the work. I think many others would like you to simply add the amalgamation to the set of files you distribute, rather than replacing the preprocessed source. It will be easier for them if you continue to do the work that you used to do. I would like to take this opportunity to thank you again for all the work you have put into sqlite. You have created a tremendous resource which makes many peoples lives at least a little easier and hence better. I hope the rewards have been worth the effort. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Makefile
Thanks Tom, That was just what I was looking for Regards, Ken Tomash Brechko <[EMAIL PROTECTED]> wrote: On Wed, May 02, 2007 at 11:43:04 -0700, Ken wrote: > Is there a way to disable the -g flag for the library? Assuming you are using configure, ./configure CFLAGS='-O2' After that 'make' will use only -O2, without -g. > Is there a way to tell the Make to build a 32bit version vs a 64 > bit? If not this would be really nice. You may pass arbitrary compilation options as shown above, or you may override the compiler itself with ./configure CC=/path/to/gcc32bit > Can the Make that is provided build a libsqlite3.a and libsqlite3.so > from the amalgamated sqlite3.c ??? No. But the following quick-n-dirty-cut-n-paste patch will (hopefully) do the job :) --- Makefile.in-orig 2007-05-02 19:12:21.0 +0400 +++ Makefile.in 2007-05-03 00:16:07.0 +0400 @@ -130,6 +130,9 @@ LIBOBJ = alter.lo analyze.lo attach.lo a vdbe.lo vdbeapi.lo vdbeaux.lo vdbefifo.lo vdbemem.lo \ where.lo utf.lo legacy.lo vtab.lo +LIBOBJ = sqlite3.lo + + # All of the source code files. # SRC = \ @@ -315,6 +318,9 @@ lemon$(BEXE): $(TOP)/tool/lemon.c $(TOP) # Rules to build individual files # +sqlite3.lo: sqlite3.c + $(LTCOMPILE) -c sqlite3.c + alter.lo: $(TOP)/src/alter.c $(HDR) $(LTCOMPILE) -c $(TOP)/src/alter.c -- Tomash Brechko - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Makefile
Is there a way to disable the -g flag for the library? I've found that the version compiled without the -g flags is about 3 times smaller (right around the 500k mark) but the default compile is about 1.7 meg! Is there a way to tell the Make to build a 32bit version vs a 64 bit? If not this would be really nice. Can the Make that is provided build a libsqlite3.a and libsqlite3.so from the amalgamated sqlite3.c ??? Thanks Ken
Re: [sqlite] 2 Threads - lock after Commit:
Matthew Veenstra <[EMAIL PROTECTED]> wrote: Hello, I was wondering if someone can shed a bit of light on a problem I am having. I am using SQLite in a client setting on Mac and Windows. I have two threads. That I use SQLite in. The main thread uses this to get data and display it to screen. Then I have a worker or secondary thread that batch process many commands with a Transaction using BEGIN: and COMMIT:. As the transaction happens on the second thread the main thread continues about it's business and displays information. This seems to work just fine...until COMMIT: is called. Once COMMIT is called I get database is locked and I am not able to proceed. I am not a master of SQLite so I am not sure if this is expected behavior. Here are some things I am doing that might be relevant. - I have many temp tables that are created and can be opened at this time. - I am using my own built frameworks on the Mac and the default ones on Windows. I build this framework from the preprocessed Windows files. I set these C flags at compile time. -DTHREADSAFE=1 \ -DSQLITE_THREAD_OVERRIDE_LOCK=-1 \ -DOS_UNIX=1 \ -DHAVE_USLEEP=1 \ -DSQLITE_OMIT_CURSOR I would expect SQLite to wait for the COMMIT to finish and then be able to use the data base just as before. Am I missing something? Thx, Matt tribalmedia - To unsubscribe, send email to [EMAIL PROTECTED] - Matt, this is expected behavior. I start every read/write operation by wrapping them with a begin exclusive and then commit. That way I don't get any of the annoying locking issues at commit point. This has a disadvantage that all access is now serialized behind sqlites locking mechanism as even select operations get exclusive access to the DB.
Re: [sqlite] what do I compile with -dthreadsafe=1
configure --enable-threadsafe should do it. Rafi Cohen <[EMAIL PROTECTED]> wrote: Hi, when I read the article about sqlite and multithread in cvstrac, I understood that I need to compile my source files using sqlite functions with -DTHREADSAFE=1. Now, due to a pproblem I had in my project I re-readad this article and began to think I should compile sqlite source files using -DTHREADSAFE=1, which I did not do yet. So I'm confused. Where do i use this? compile sqlite sources? my sources? both? In case of sqlite sources, where do I put this -DTHREADSAFE=1? in a specific makefile? in all makefiles? Please help. I have linux and using sqlite 3.3.12. Thanks, Rafi.
Re: [sqlite] Re: SQLite and nested transactions
Autonomous transactions: ie begin begin autonomous txn1 commit; commit txn1 This transaction has no impact on the outer txn. I believe it can commit either in or out of the parent transaction as well Quite a bit more complicated and really requires the concept of a transaction ID. Maybe this is specific to the DB vendor (ORA$$) I totally agree, in more than 20 years of commercial db development and DBA work, I've only encounterd the use of savepoints 1 time. Dennis Cote <[EMAIL PROTECTED]> wrote: Ken wrote: > Correct me if I'm wrong on this concept: > Adding nested transactions really means adding the ability to demark > internally a transaction ID. So that later that transaction can be rolled > back. > > Consider > begin Main; > step a > savepoint loc1 > step 1 > savepoint loc2 > step 2 > rollback loc2 <- Rolls back step2 > step 2a > savepoint loc3 > step 3 > commit ; > >(result: step a, step 1, step2a and step3 ) > > I think the concept of a savepoint is simpler than a truely nested > transaction. As one doesn't actually need to start a new transaction just > mark a position where a savepoint rollback would stop. Savepoints then are > not really nested transactions but just markers that indicate when to stop > rolling back within the journal file. > Ken, As far as I understand it the two concepts are fundamentally the same. Savepoints can be implemented using simply nested transactions. The savepoint syntax is what is used by the SQ:1999 and later standards. > > But savepoints are usefull in special situations. > Yes they are, but those situations are really quite rare in the real world. > >Instead of Nested Transactions, What about the concept of an autonomous > transaction? > > I don't know what you mean by autonomous transactions as opposed to normal SQL transactions. Can you explain the difference? Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
Dennis Cote <[EMAIL PROTECTED]> wrote: Ramon Ribó wrote:
>
>
> Imagine one application that can import data from a file. You want
> that, in case of computer crash, either all the data of the file is
> imported or none. At the same time, you want the user to manually
> accept or reject every section of the file.
>
> This example can be modelled in a very natural way with a
> transaction covering the full file import and a nested transaction
> covering every section.
>
>
Ramon,
I don't see that where nested transactions are needed for this example.
You seem to be suggesting a loop reading each file section and writing
it into the database in a nested transaction and then rolling back a
nested transaction if the user says they want to skip that section.
begin
for each section in file {
read section
begin nested
insert section
if promp_user(section) == keep
commit nested
else
rollback nested
}
commit
The same thing can be done far more efficiently by prompting the user
first and only inserting the sections the user wants to keep.
begin
for each section in file {
read section
if promp_user(section) == keep
insert section
}
commit
If the program completes all users selected sections are inserted into
the database atomically. If the program crashes the entire file will be
deleted when the incomplete transaction is rolled back. Similarly if an
I/O error occur when reading the file or a disk full condition happens
when inserting a section, those and any other errors would cause the
transaction to be rolled back so that none of the file sections are
inserted. I want to insert all of the user selected sections or none of
them.
Nested transaction only create more work and make the application more
complicated.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Denis,
Correct me if I'm wrong on this concept:
Adding nested transactions really means adding the ability to demark
internally a transaction ID. So that later that transaction can be rolled back.
Consider
begin Main;
step a
savepoint loc1
step 1
savepoint loc2
step 2
rollback loc2 <- Rolls back step2
step 2a
savepoint loc3
step 3
commit ;
(result: step a, step 1, step2a and step3 )
I think the concept of a savepoint is simpler than a truely nested
transaction. As one doesn't actually need to start a new transaction just mark
a position where a savepoint rollback would stop. Savepoints then are not
really nested transactions but just markers that indicate when to stop rolling
back within the journal file.
The examples given thus far are not very compelling for savepoints. But
savepoints are usefull in special situations.
Instead of Nested Transactions, What about the concept of an autonomous
transaction?
Regards,
Ken
Re: [sqlite] Data structure
You might want to check out kazlib for your data structure lookups. It cantains code to implement Linked List, Hast, and Dictionary access data structures. The hashing code is really quite fast for in memory retrievals plus it is dynamic so that you don't have to preconfigure your hash table size. The linked list code is pretty good, it does have the ability to create Memory Pools (node pools) for the list structures. That way the package is not continually calling malloc and free for every node insert/delete etc.. Lloyd <[EMAIL PROTECTED]> wrote: On Wed, 2007-04-11 at 10:00 -0500, P Kishor wrote: > I think, looking from Lloyd's email address, (s)he might be limited to > what CDAC, Trivandrum might be providing its users. > > Lloyd, you already know what size your data sets are. Esp. if it > doesn't change, putting the entire dataset in RAM is the best option. > If you don't need SQL capabilities, you probably can just use > something like BerkeleyDB or DBD::Deep (if using Perl), and that will > be plenty fast. Of course, if it can't be done then it can't be done, > and you will have to recommend more RAM for the machines (the CPU > seems fast enough, just the memory may be a bottleneck). Sorry, I am not talking about the limitations of the system in our side, but end user who uses our software. I want the tool to be run at its best on a low end machine also. I don't want the capabilities of a data base here. Just want to store data, search for presence, remove it when there is no more use of it. Surely I will check out BerkeleyDB. The data set must be in ram, because the total size of it is very small. (Few maga bytes) I just want to spped up the search, which is done millions of times. Thanks, LLoyd __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure
I've used callgrind to get a hierachy of calls, it's good to graphically see where your spending time at in the code. Also you might want to check out oprofile. Its more of a system based profiler. And if you want to spend $$$ Rational Rose (I thinkt its an IBM product now) Purify is an excellent tool. Ken Lloyd <[EMAIL PROTECTED]> wrote: Would anybody suggest a good tool for performance measurement (on Linux) ? On Wed, 2007-04-11 at 10:35 -0500, John Stanton wrote: > You might discover that you can craft a very effective memory > resident > storage system using a compression system like Huffman Encoding and > an > index method appropriate to the key you are using for retrieval. > That > could work very well in an embedded system, have a small footprint in > data and code and be very fast. __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQL help
sum(is_complete) is only the same as "where is_complete = 1" when there is a check constraint guaranteeing that is complete will either be a 0 or 1. Dennis Cote <[EMAIL PROTECTED]> wrote: Clark Christensen wrote: > I have a table, as described below, where I need to find out if the tech_id > in question has at least some modules in a particular collection (coll_id), > and they're all complete. > > At this point, I'm working with variations on: > > select > (select count(*) from tech_modules > where tech_id = ? and coll_id = ?) as mcount, > (select count(*) from tech_modules where tech_id = ? > and coll_id = ? and is_complete = 1) as mcomplete; > > Performing the comparisons on mcount and mcomplete in the app ($mcount > 0 && > $mcomplete == $mcount) to get a boolean $allComplete. > > It performs OK (~50K rows in the table), using the unique index for both > subqueries, but the SQL seems crude. Anybody have a more elegant solution to > share? > > Thanks! > > -Clark > > CREATE TABLE TECH_MODULES ( > TECH_ID integer, > MODULE_ID integer, > COLL_ID integer, > IS_COMPLETE integer default 0, > COMPLETION_TIME date, > COMPLETION_TARGET date, > DELETED integer > ); > > CREATE INDEX tech_modules_by_module_id ON TECH_MODULES (module_id); > > CREATE UNIQUE INDEX tech_modules_unique_module_id on TECH_MODULES (tech_id, > coll_id, module_id); > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > Clark, You could try this: select count(*) as mcount, sum(is_complete) as mcomplete from tech_modules where tech_id = ? and coll_id = ?; HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQL language issue, ticket #2282
Oracle does not allow a when clause on an instead of trigger. when new.key=old.key * ERROR at line 3: ORA-25004: WHEN clause is not allowed in INSTEAD OF triggers [EMAIL PROTECTED] wrote: Ticket #2282 against SQLite http://www.sqlite.org/cvstrac/tktview?tn=2282 complains that in an INSTEAD OF trigger on a view where the trigger has a WHEN clause, if the WHEN clause is false and the trigger does not fire, then the UPDATE statement that provoked the trigger should fail. I am skeptical of this claim, but want the opinion of others before I reject the ticket. Is this really a bug? What do INSTEAD OF triggers with false WHEN clauses do on other SQL database engines? -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] in-memory database bound to a named buffer
short answer is no. The memory structures are not identical to disk structures. Ryan Underwood <[EMAIL PROTECTED]> wrote: Is it possible to bind an in-memory sqlite database (in read-only mode, naturally) to an existing named memory buffer that contains exactly the contents of a sqlite database file? What I want to do is maintain a database as usual, but then take an occasional snapshot, bin2c it and include it in my (platform/OS-independent) project. I looked for functions that would take a mmap'd file (which would be equivalent), but didn't see anything... -- Ryan Underwood, - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PLSQL
John, I've used PL/SQL on Oracle. I think it was derived from another language, maybe PL/1. I think its an excellent language, my main usage was geared at PL/SQL for DBA usage that managed internal oracle objects. Pros: Packages / Procedures/ Functions have a really nice hierarchy. Exception handling was excellent. cursor handing internaly was good. Record handling was good. Cons: Permission sheme was a pain. Ofter requiring direct ownership of objects rather than through grants. Array handling was poor. Var arrays helped but seemed rather cumbersome to utilize. Triggers are implemented via PL/SQL. John Stanton <[EMAIL PROTECTED]> wrote: I am looking for advice experienced DBMS users please. I am currently implementing an application server using Sqlite as an embedded RDBMS and have a need for expanded function capability. My server provides support for AJAX style WWW applications. I have not used PLSQL to any significant degree and would be interested to hear pros and cons from users. I am attracted to have a function language compatible with Oracle and PostgreSQL but wonder if PLSQL is actually a good language to create SQL functions which realise business rules and whether users find it easy to grasp so that they can maintain the PLSQL functions. Does anyone have a better idea? My plan is to compile the function language into a byte code and embed a virtual machine to execute it. The application server already has a virtual machine which executes a simple language which integrates SQL, HTML and Javascript along with some rudimentary control structures to define workflow. The function language would be used to define business rules and be part of a remote procedure call process used by the AJAX applications. I should very much appreciate some wise counsel. BTW, I shall make the server available if it turns out to work as planned. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Difference in these indices?
Joe, In general the storage concept would be nice to extend this to a more general case. Extend the sql language to allow storage to a specified file. This would allow the user to easily seperate the data vs indices. ie Create table ... ( column ) storage-clause storage-clause := store as (fileName, pageSize, initialAlocation) Ken Joe Wilson <[EMAIL PROTECTED]> wrote: --- [EMAIL PROTECTED] wrote: > If you insert records in order of ascending integer primary > key, then the inserts will be very fast. If you insert records > where the integer primary key is randomized, inserts will be > reasonably fast until the size of your table exceeds the size > of your disk cache. Then each insert will need to do multiple > reads and writes to disk as it tries to figure out where in > your massive table is the right place to put the new record, > and then make space for that new record. All this disk I/O > will slow things down dramatically. SQLite is extremely fast in most area of SQL functionality except for bulk inserts of non-ordered data into multi-indexes tables, where it is very slow. This pre-sorting-before-insert trick only works for single indexed tables, and is not useful in the general case. It also defeats the purpose of using a database - why should the user have to pre-sort data to achieve acceptable performance? In the current SQLite insert algorithm there's way too much shuffling around of disk pages in order to minimize database footprint at all costs. There ought to be a PRAGMA to prefer maximizing insert speed when inserting, and if it doubles or quadruples the database size - so be it. Most embedded devices may opt for minimum space; but most PC-based apps would likely opt for maximum insert speed. I believe 2 measures when applied together would significantly improve insert speed of indexed rows: 1. Allot each index/btree a contiguous region of the database file in which to grow without conflicting with the other indexes' pages and pages of the underlying table itself. (Putting indexes in separate files if only temporarily for bulk insert would be better, but hey, it's SQLite). 2. Adjust the btree algorithm to create larger gaps in the data when you make space for new keys in the btree. Such page-sized gaps could minimize much of the page shuffling currently done. When the inserts are done, the user need only issue a VACUUM to bring the database file back to its normal "packed" sized without such gaps if they should so choose. TV dinner still cooling? Check out "Tonight's Picks" on Yahoo! TV. http://tv.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Use of bind variable in sqlite
Amar, Sqlite has bind variables. They are not the same as an Oracle bind variable in that once you bind the address for the variable you do not have to rebind. With sqlite if you change the variable you must rebind. Sqlites version of binding is more like a copy into its own memory area. Also sqlite is missing the array level interface, which is the capability to batch up an array of data items and perform a single dml command where oracle would internally iterate over the array performing the operation. Also if you are planning on having multiple users and high concurrency you'd better stick with oracle. Sqlite is designed for embedded single users systems. Regards, Ken "Amarjeet Kumar (RBIN/ECM4)" <[EMAIL PROTECTED]> wrote: Hi, Can we use the bind variable in sqlite query, if so how? Thanks in advance. Regards, Amar
Re: [sqlite] Finding linked peers
Sample recusrive SQL from another database engine... level is a built in field. This is very useful and powerful syntax allowing one to build tree's (ie parent child relationships) inside of a table. SELECT level,chld FROM tbl1 START WITH value = 'some value' CONNECT BY parent = PRIOR(child) Dennis Cote <[EMAIL PROTECTED]> wrote: Clark Christensen wrote: > So, finally, the question: What might the SQL look like to retrieve a list of > predecessors for 'PC1_v1.3'? Sure, I could prepare a stmt in the app (Perl), > and walk the chain in some fashion. Is this result even possible using plain > SQL? > > Clark, SQLite does not support the recursive SQL queries that could be used to do this kind of processing. So there is no way to follow a chain in SQL. You can convert the problem into pattern matching by having each record store the path along the chain in that record. This is really a variation of the SQL tree problem. I have previously posted a sample implementation of this materialized (or stored) path method at http://www.mail-archive.com/sqlite-users@sqlite.org/msg13225.html HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - Dennis Cote <[EMAIL PROTECTED]> wrote: Clark Christensen wrote: > So, finally, the question: What might the SQL look like to retrieve a list of > predecessors for 'PC1_v1.3'? Sure, I could prepare a stmt in the app (Perl), > and walk the chain in some fashion. Is this result even possible using plain > SQL? > > Clark, SQLite does not support the recursive SQL queries that could be used to do this kind of processing. So there is no way to follow a chain in SQL. You can convert the problem into pattern matching by having each record store the path along the chain in that record. This is really a variation of the SQL tree problem. I have previously posted a sample implementation of this materialized (or stored) path method at http://www.mail-archive.com/sqlite-users@sqlite.org/msg13225.html HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] API enhancement
Denis, Thanks for the great explanation !!! Regards, Ken Dennis Cote <[EMAIL PROTECTED]> wrote: Ken wrote: > It should save some time. How much is questionable. > > Why would sqlite have to bind the Pointer bound variables? Isn't the strategy > of binding to associate a variable with a statment? Why should I have to > continually re-associate the bindings with a statement thats allready been > prepared and bound, just to execute it again after a reset ? > > I guess I'm a bit confused, I'll look at the bind code in sqlite some more. > > > > > Ken, Your idea could save some time but it would require adding a new class of indirect variables (references) to sqlite. The VDBE opcode that loads the variable values would have to be changed to recognize the indirect variables and then create an internal sqlite variable that can be pushed onto the VDBE stack from the external variable. The last part is the same function that the bind routines perform. The bind APIs are fairly lightweight functions, basically just saving the value passed into an internal array. Your scheme would only be saving the overhead of the internal copy operation (from the variable to the stack during the op_variable opcode) and the call to the bind function itself. This scheme would also be adding the cost of the variable type check to every variable lookup. There is also the distinct possibility that a variable may be dereferenced more than once while executing a statement, and this would involve duplicating the work of creating the internal variable from the external memory. There is also the possibility of some nasty SQL bugs due to the value off a variable being changed during the execution of a statement. All in all I don't think the payback is large enough to justify the extra complexity and increased code size in the general case. If you have an application where the overhead of the bind functions calls are a real issue, you could of course create a custom version of sqlite that implements your idea. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] API enhancement
It should save some time. How much is questionable.
Why would sqlite have to bind the Pointer bound variables? Isn't the strategy
of binding to associate a variable with a statment? Why should I have to
continually re-associate the bindings with a statement thats allready been
prepared and bound, just to execute it again after a reset ?
I guess I'm a bit confused, I'll look at the bind code in sqlite some more.
Scott Hess <[EMAIL PROTECTED]> wrote: I don't see how your modified version is
any better than just putting
the sqlite3_bind_int() inside the loop. You've superficially lifted
some code out of the loop, but sqlite3_step() is going to have to go
through and bind all of the "pointer bound" variables in your
suggested API, so it won't save you anything in the end.
-scott
On 3/19/07, ken-33 wrote:
>
> Anyone thoughts?
>
>
> ken-33 wrote:
> >
> > Question for the list,
> >
> > I'd like to optimize my code, using the following pseudo code as an
> > example.
> >
> > ===
> > int i = 0 ;
> > char str[20];
> >
> > sqlite3_prepare_v2( "insert into t1 values (?,?)" )
> > sqlite3_bind_int ( i )
> > sqlite3_bind_text(str)
> >
> > BEGIN TRANSACTION
> > For (i = 0; i < 10; i++) {
> >sqlite3_step ( );
> >sqlite3_reset( )
> > }
> > COMMIT TRANSACTION
> > ==
> >
> > However, the above code will fail to insert the values for i in the loop.
> > It will only insert the value 0, since that was the binding value...
> >
> > An enhancement request would be to allow the user to bind the address to
> > the statement objects. This would be a huge benefit from the standpoint
> > of fewer function calls to sqlite3_bind in the inside loop.
> >
> > So maybe the following API:
> >
> > sqlite3_pbind_int(sqlite3_stmt *, int, int * );
> > sqlite3_pbind_int64(sqlite3_stmt *, int, long long int * );
> > sqlite3_pbind_double(sqlite3_stmt *, int, dobule *);
> > sqlite3_pbind_text(sqlite3_stmt, int, const char *, int *,
> > void(*)(void*));
> > notice the text takes a pointer to the length...
> > sqlite3_pbind_blob(sqlite3_stmt*, int, const char *, int *,
> > void(*)(void*));
> >
> > Concept of SQLITE_STATIC vs SQLITE_TRANSIENT is implied that all data is
> > SQLITE_STATIC.
> >
> > Regards,
> > Ken
> >
> >
> >
> >
> >
>
> --
> View this message in context:
> http://www.nabble.com/API-enhancement-tf3405347.html#a9562311
> Sent from the SQLite mailing list archive at Nabble.com.
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] API enhancement
Anyone thoughts?
ken-33 wrote:
>
> Question for the list,
>
> I'd like to optimize my code, using the following pseudo code as an
> example.
>
> ===
> int i = 0 ;
> char str[20];
>
> sqlite3_prepare_v2( "insert into t1 values (?,?)" )
> sqlite3_bind_int ( i )
> sqlite3_bind_text(str)
>
> BEGIN TRANSACTION
> For (i = 0; i < 10; i++) {
>sqlite3_step ( );
>sqlite3_reset( )
> }
> COMMIT TRANSACTION
> ==
>
> However, the above code will fail to insert the values for i in the loop.
> It will only insert the value 0, since that was the binding value...
>
> An enhancement request would be to allow the user to bind the address to
> the statement objects. This would be a huge benefit from the standpoint
> of fewer function calls to sqlite3_bind in the inside loop.
>
> So maybe the following API:
>
> sqlite3_pbind_int(sqlite3_stmt *, int, int * );
> sqlite3_pbind_int64(sqlite3_stmt *, int, long long int * );
> sqlite3_pbind_double(sqlite3_stmt *, int, dobule *);
> sqlite3_pbind_text(sqlite3_stmt, int, const char *, int *,
> void(*)(void*));
> notice the text takes a pointer to the length...
> sqlite3_pbind_blob(sqlite3_stmt*, int, const char *, int *,
> void(*)(void*));
>
> Concept of SQLITE_STATIC vs SQLITE_TRANSIENT is implied that all data is
> SQLITE_STATIC.
>
> Regards,
> Ken
>
>
>
>
>
--
View this message in context:
http://www.nabble.com/API-enhancement-tf3405347.html#a9562311
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] API enhancement
Anyone
Ken <[EMAIL PROTECTED]> wrote: Question for the list,
I'd like to optimize my code, using the following pseudo code as an example.
===
int i = 0 ;
char str[20];
sqlite3_prepare_v2( "insert into t1 values (?,?)" )
sqlite3_bind_int ( i )
sqlite3_bind_text(str)
BEGIN TRANSACTION
For (i = 0; i < 10; i++) {
sqlite3_step ( );
sqlite3_reset( )
}
COMMIT TRANSACTION
==
However, the above code will fail to insert the values for i in the loop. It
will only insert the value 0, since that was the binding value...
An enhancement request would be to allow the user to bind the address to the
statement objects. This would be a huge benefit from the standpoint of fewer
function calls to sqlite3_bind in the inside loop.
So maybe the following API:
sqlite3_pbind_int(sqlite3_stmt *, int, int * );
sqlite3_pbind_int64(sqlite3_stmt *, int, long long int * );
sqlite3_pbind_double(sqlite3_stmt *, int, dobule *);
sqlite3_pbind_text(sqlite3_stmt, int, const char *, int *, void(*)(void*));
notice the text takes a pointer to the length...
sqlite3_pbind_blob(sqlite3_stmt*, int, const char *, int *, void(*)(void*));
Concept of SQLITE_STATIC vs SQLITE_TRANSIENT is implied that all data is
SQLITE_STATIC.
Regards,
Ken
Re: [sqlite] Strange performance behavior
Did you ever determine the cardinality of the campID field? I'm guessing its
pretty good since your query is now .6 seconds.
Lets say your cardinality was low, ie say less than .3 (arbitrary number).
Then using the index to perform you data lookups would probably be slower than
just reading the entire file. In this case the index would actually hurt rather
than help.
Hubertus <[EMAIL PROTECTED]> wrote: Well now, if this isn't something!
fist I wann thank you for your quick reply. Finally I have to wait for my result
satisfying 0.6 sec. This is great. Now I can add lots more data.
What I did:
- I added a second index for the column "campId" and did the analyze trick
(I had this column in the index before, but that time it was quicker without
that column).
This already improved the waiting time from the former best 3 sec to 1 sec
- I increased the page_size to 4096, this decresed the size of the
database from 650 Mb to 450.
- And at last I replaced the -.99 values with NULL (don't think about it. I
was asked to do this as missing value, now I found the .nullvalue...)
This again decreased the size to stunning 165 Mb!! and improved the
query time to even better 0.6 sec.
To Dennis:
I'm afraid I haven't quite understood the quote thing.
First how can I do a query like
select "42" from data where campId='stream94' and "14">-;
from my shell?
Secondondly usually I use python or R to access the data where I do somthing
like
INSERT = 'SELECT "%i" FROM data where campId="%s"' % col, campId
query <- paste('SELECT "34" AS "N2O_TDL", "29" AS "O3"',
'FROM data where campId="polstar97"')
rs <- dbSendQuery(con, statement = query)
How is this done correctly?
Thanks a lot
Hubertus
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Strange performance behavior
Looks like it is going to do a full scan of the entire database to complete
that querry based upon your where clause.
Are you always accessing the data by campID? What is the cardinality of
campId data?
Depending upon that it might be worth while putting and index on CampID.
Hubertus <[EMAIL PROTECTED]> wrote:
Dear list,
sorry to just come up with another performance question. I build a yet small
database with one table. It has about 650.000 rows, 75 columns and
has at the moment about 650 Mb. It runs on a Intel Pentium M with 2 GHz. The
Laptop runs Suse 10.2 and does basicly nothing but this database.
sqlite3 -version is 3.3.8
This is the schema:
CREATE TABLE 'data'('nr' INTEGER PRIMARY KEY,
'year' INTEGER,
'month' INTEGER,
'day' INTEGER,
'sec' REAL,
'campId' TEXT,
'flightNr' INTEGER,
'1' REAL,
...
'71' REAL
);
CREATE INDEX sec on data(year,month,day,sec);
I experience a big variability of time a query needs:
~database> time sqlite3 data.db 'select "14" from data where campId="polstar98"
and "14">-;' >/dev/null
real 0m3.115s
user 0m1.748s
sys 0m1.368s
~/database> time sqlite3 data.db 'select "14" from data where
campId="polstar98" and "14">-;' >/dev/null
real 0m3.139s
user 0m1.756s
sys 0m1.380s
~/database> time sqlite3 data.db 'select "42" from data where campId="stream94"
and "14">-;' >/dev/null
real 0m50.227s
user 0m4.692s
sys 0m3.028s
I've tried it with and without index and at different times. Most of the
time such simple queries take about 35 sec. Why is that so and what can be done?
I can live with 3 sec of response but not up to one minute and the
database is still not complete. Would pytables with hdf5 be an alternative?
Knowing that this is probably not the right place to ask...
Tips, suggestions, recommendation are gratefuly appreciated!
Thanks in advance
Hubertus
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] sqlite Performance
Dennis, Yes the data will be read later by down stream processing. I do have the option of either putting the data into sqlite at the start (when its read) or putting it into a flat file and then Later loading it into a sqlite db via a downstream job. A great deal of the data columns are simple numeric values and thats where sqlite really shines in that is portable between systems having differing endianness. Here is a summary of the entire processing where a1/b1 are different processes and probably differnt host platforms. a1, read from producing system, generate output data (be it flat file or sqlite). a2. Compress data file. a3. Transfer compressed datafile to target system. a4. Goto a1 b1. Receive datafile and uncompress. b2. Read datafile and load into Master DB. (This might just be a simple attach). b3. Massage data in Master db ??? b4. Read and process data from MasterDb. Delete or mark as deleteable. b5. Delete processed data from MasterDb. (this could be in a seperate thread). b6. Goto step b1. The nice thing about simply attaching as a DB in step b2 is that when all data is processed from step b4 then step b5 to purge is a simple detach and operating system unlink for the underlying datafile. Which I suspect will be infinately faster than a sql delete command. Thanks, Ken Dennis Cote <[EMAIL PROTECTED]> wrote: Ken wrote: > > This is a write only app. 100% insert. > > Ken, Why bother putting the data into a database if you are never going to read it back out? Other formats, such as a flat text file are much better for logs or archives. If, in fact, you will be reading the data at some point then you need to also compare the time it takes to retrieve the data of interest from your flat file vs the sqlite database. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite Performance
DRH, Thanks for your valuable insite. When the DB is closed when in synchrounous mode, is it then persistent at the OS level even from power failures etc? [EMAIL PROTECTED] wrote: Ken wrote: > > I should be able to run with synchronous=off. Since > the application maintains state in a seperate DB elsewhere. > Just to clarify the implications where, if you run with synchronous=off and you take a power failure or an OS crash in the middle of a transaction, then you run a serious risk of ending up with a corruption database file. However, (key point->) an application crash will not hurt the database. Since you have an alternate source of authoritative data and since power failures and OS crashes are reasonably infrequent, your decision to run with synchronous=off may well be the right choice. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to set pragma page_size
Thanks DRH... That worked. Ken [EMAIL PROTECTED] wrote: Ken wrote: > How does one set the page_size ? > > according to the documentation > "The page-size may only be set if the database has not yet been created. > " > > So how do you execute the pragma prior to creating the DB? without calling > sqlite3_open to get a DB handle that is needed to call prepare/step ? > The database file is not actually created until you issue a "CREATE TABLE" statement. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite Performance
Tito, Its even better now! Synchronous=normal and No primary keys (except 1 table) for auto increment. real0m1.975s user0m1.436s sys 0m0.140s Vs flat file test case: real0m0.862s user0m0.228s sys 0m0.188s This is now very respectable. Thanks, Ken Ken <[EMAIL PROTECTED]> wrote: Tito, There are no indices built besides the default ones. Hmm maybe I should try this by dropping the primary Keys.. I'll give that a try as well, GOOD idea! The entire batch of inserts (about 8 tables) is done in a single transaction. As an Oracle DBA, I'm pretty familar with tuning. This was definately an I/O issue and related to the code path vs say a select where the optimizer picked an incorrect plan. Regards, Ken Tito Ciuro wrote: Hello, IIRC (it was a while ago), one way to speed up insertion for large data sets is to drop the indexes, do the inserts (wrapped around a transaction) and then rebuild the indexes. For smaller data sets, the drop/rebuild indexes solution doesn't make sense because the time it takes to do that invalidates the performance gain. However, larger data sets seemed to benefit greatly. Again... that was a while ago... :-) I should dust-off my test app and see what the results are with the latest sources. I'll let you know what I find out. -- Tito On Mar 15, 2007, at 11:42 AM, John Stanton wrote: > There are no free lunches. When Sqlite stores your data item it > not only writes it into a linked list of pages in a file but also > inserts at least on key into a B-Tree index. It does it quite > efficiently so what you are seeing is the inevitable overhead of > storing the data in a structured form. The value of the structure > becomes obvious when you are retrieving a single item from a set of > millions and the index allows you to access it in a tiny fraction > of the time it would take to search an unstructured list like a > flat file. > > The ACID implementation in Sqlite provides data security but is > does involve a significant overhead. You pay a price for not > losing data in a system crash. > > Like all things in life "you pays your money and you takes your > choice". It is somewhat simpler with Sqlite in that you don't pay > your money, you just take your choice. > > If you want faster Sqlite performance use faster disks. The > latency is important so 15,000 rpm disks will be better than 5,400 > rpm ones. > > Ken wrote: >> To answer your question: Yes I can use a flat file at this stage, >> but eventually it needs to be imported into some type of >> structure. So to that end I decided early on to use sqlite to >> write the data out. I was hoping for better performance. The raw >> I/O to read the data and process is around .75 seconds (no write i/ >> o).. So using a flat file output costs about .7 seconds. >> Using sqlite to do the output costs about 2.25 seconds. My >> question is why? And what can be done to improve this >> performance? John Stanton wrote: Ken wrote: >>> I'm looking for suggestions on improving performance of my sqlite >>> application. >>> Here are system timings for a run where the sqlite db has been >>> replaced with a flat file output. >>> real 0m1.459s >>> user0m0.276s >>> sys 0m0.252s >>> This is a run when using sqlite as the output format. >>> real 0m3.095s >>> user0m1.956s >>> sys 0m0.160s >>> As you can see sqlite takes twice as long and almost 8 times the >>> user time. >>> Output size for flat file: 13, 360, 504flatfile.dat >>> Output size fo sqlit file: 11,042,816 sqlt.db f >>> Slite db has the following pragmas set. >>> PRAGMA default_synchronous=FULL >>> PRAGMA temp_store=memory >>> PRAGMA page_size=4096 >>> PRAGMA cache_size=2000 >>> Any ideas how to get the sqlite output timings to a more >>> respectable level would be appreciated. >>> Thanks >>> Ken >> If you want flat file performance, use a flat file. Sqlite is >> built on top of a flat file and cannot be faster or even as fast. >> If your application can use a flat file, why use anything more >> complex? >> - >> >> To unsubscribe, send email to [EMAIL PROTECTED] >> - >> > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite Performance
Tito, There are no indices built besides the default ones. Hmm maybe I should try this by dropping the primary Keys.. I'll give that a try as well, GOOD idea! The entire batch of inserts (about 8 tables) is done in a single transaction. As an Oracle DBA, I'm pretty familar with tuning. This was definately an I/O issue and related to the code path vs say a select where the optimizer picked an incorrect plan. Regards, Ken Tito Ciuro <[EMAIL PROTECTED]> wrote: Hello, IIRC (it was a while ago), one way to speed up insertion for large data sets is to drop the indexes, do the inserts (wrapped around a transaction) and then rebuild the indexes. For smaller data sets, the drop/rebuild indexes solution doesn't make sense because the time it takes to do that invalidates the performance gain. However, larger data sets seemed to benefit greatly. Again... that was a while ago... :-) I should dust-off my test app and see what the results are with the latest sources. I'll let you know what I find out. -- Tito On Mar 15, 2007, at 11:42 AM, John Stanton wrote: > There are no free lunches. When Sqlite stores your data item it > not only writes it into a linked list of pages in a file but also > inserts at least on key into a B-Tree index. It does it quite > efficiently so what you are seeing is the inevitable overhead of > storing the data in a structured form. The value of the structure > becomes obvious when you are retrieving a single item from a set of > millions and the index allows you to access it in a tiny fraction > of the time it would take to search an unstructured list like a > flat file. > > The ACID implementation in Sqlite provides data security but is > does involve a significant overhead. You pay a price for not > losing data in a system crash. > > Like all things in life "you pays your money and you takes your > choice". It is somewhat simpler with Sqlite in that you don't pay > your money, you just take your choice. > > If you want faster Sqlite performance use faster disks. The > latency is important so 15,000 rpm disks will be better than 5,400 > rpm ones. > > Ken wrote: >> To answer your question: Yes I can use a flat file at this stage, >> but eventually it needs to be imported into some type of >> structure. So to that end I decided early on to use sqlite to >> write the data out. I was hoping for better performance. The raw >> I/O to read the data and process is around .75 seconds (no write i/ >> o).. So using a flat file output costs about .7 seconds. >> Using sqlite to do the output costs about 2.25 seconds. My >> question is why? And what can be done to improve this >> performance? John Stanton wrote: Ken wrote: >>> I'm looking for suggestions on improving performance of my sqlite >>> application. >>> Here are system timings for a run where the sqlite db has been >>> replaced with a flat file output. >>> real 0m1.459s >>> user0m0.276s >>> sys 0m0.252s >>> This is a run when using sqlite as the output format. >>> real 0m3.095s >>> user0m1.956s >>> sys 0m0.160s >>> As you can see sqlite takes twice as long and almost 8 times the >>> user time. >>> Output size for flat file: 13, 360, 504flatfile.dat >>> Output size fo sqlit file: 11,042,816 sqlt.db f >>> Slite db has the following pragmas set. >>> PRAGMA default_synchronous=FULL >>> PRAGMA temp_store=memory >>> PRAGMA page_size=4096 >>> PRAGMA cache_size=2000 >>> Any ideas how to get the sqlite output timings to a more >>> respectable level would be appreciated. >>> Thanks >>> Ken >> If you want flat file performance, use a flat file. Sqlite is >> built on top of a flat file and cannot be faster or even as fast. >> If your application can use a flat file, why use anything more >> complex? >> - >> >> To unsubscribe, send email to [EMAIL PROTECTED] >> - >> > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How to set pragma page_size
How does one set the page_size ? according to the documentation "The page-size may only be set if the database has not yet been created. " So how do you execute the pragma prior to creating the DB? without calling sqlite3_open to get a DB handle that is needed to call prepare/step ? Ive also tried setting this via -DSQLITE_DEFAULT_PAGE_SIZE=4096 at compile time and still get the default pagesize of 1024. Thanks
RE: [sqlite] sqlite Performance
Donald, I set the PRAGMA synchronous= OFF and here are the results: real0m2.258s user0m1.736s sys 0m0.168s -- Pragma synchronous= NORMAL real0m2.395s user0m1.520s sys 0m0.128s Pragma synchronous= FULL real0m3.228s user0m2.276s sys 0m0.136s Running with synchronous=off is 43% faster !!! Running with Synchrounous=normal is 33 % faster. I should be able to run with synchronous=off. Since the application maintains state in a seperate DB elsewhere. Thanks for you valuable Input. Ken "Griggs, Donald" <[EMAIL PROTECTED]> wrote: Regarding: Creation of flat file takes 1.5 secs vs 3 seconds to create sqlite db. Flat file is 13 MB, sqlite db is 11 MB. "Any ideas how to get the sqlite output timings to a more respectable level would be appreciated. " I may be way off base if I'm not understanding correctly, but how can one call these values less than respectable? To create an sqlite database (or any other) the system must do the same things it does for the flat file, plus maintain a paging structure, create indices (presumably), rollback journals, etc., etc. To take only twice as long seems great (but I'm no expert here). I'm guessing it might have taken sqlite even longer except that maybe compression of numeric values allowed it to actually need fewer disk writes for the final file (not counting journalling, though). That being said, if the data you're writing out is saved elsewhere (i.e. you can repeat the whole process if it should fail) then you can try turning synchronous OFF, or, if you have to be more conservative, to NORMAL. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite Performance
Scott, The whole job is wrapped in an explicit transaction. Variables are bound and statements prepared only once, using reset. This is a write only app. 100% insert. Ken Scott Hess <[EMAIL PROTECTED]> wrote: Are you using explicit transactions at all? If not, as a quick test, put the _entire_ job in a transaction and see what happens. -scott On 3/15/07, Ken wrote: > To answer your question: > Yes I can use a flat file at this stage, but eventually it needs to be > imported into some type of structure. So to that end I decided early on to > use sqlite to write the data out. > > I was hoping for better performance. The raw I/O to read the data and process > is around .75 seconds (no write i/o).. So using a flat file output costs > about .7 seconds. > > Using sqlite to do the output costs about 2.25 seconds. My question is why? > And what can be done to improve this performance? > > > > John Stanton wrote: Ken wrote: > > I'm looking for suggestions on improving performance of my sqlite > > application. > > > > Here are system timings for a run where the sqlite db has been replaced > > with a flat file output. > > real 0m1.459s > > user0m0.276s > > sys 0m0.252s > > > > This is a run when using sqlite as the output format. > > real 0m3.095s > > user0m1.956s > > sys 0m0.160s > > > > As you can see sqlite takes twice as long and almost 8 times the user time. > > > > Output size for flat file: 13, 360, 504flatfile.dat > > Output size fo sqlit file: 11,042,816 sqlt.db f > > > > Slite db has the following pragmas set. > > > > PRAGMA default_synchronous=FULL > > PRAGMA temp_store=memory > > PRAGMA page_size=4096 > > PRAGMA cache_size=2000 > > > > Any ideas how to get the sqlite output timings to a more respectable level > > would be appreciated. > > > > Thanks > > Ken > > > If you want flat file performance, use a flat file. Sqlite is built on > top of a flat file and cannot be faster or even as fast. If your > application can use a flat file, why use anything more complex? > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] sqlite Performance
ok my bad for poor wording... I'll try with Synchronous off. I may also try disabling the journal file since I can easily recreate the data if it is not successful. Thanks, Ken "Griggs, Donald" <[EMAIL PROTECTED]> wrote: Regarding: Creation of flat file takes 1.5 secs vs 3 seconds to create sqlite db. Flat file is 13 MB, sqlite db is 11 MB. "Any ideas how to get the sqlite output timings to a more respectable level would be appreciated. " I may be way off base if I'm not understanding correctly, but how can one call these values less than respectable? To create an sqlite database (or any other) the system must do the same things it does for the flat file, plus maintain a paging structure, create indices (presumably), rollback journals, etc., etc. To take only twice as long seems great (but I'm no expert here). I'm guessing it might have taken sqlite even longer except that maybe compression of numeric values allowed it to actually need fewer disk writes for the final file (not counting journalling, though). That being said, if the data you're writing out is saved elsewhere (i.e. you can repeat the whole process if it should fail) then you can try turning synchronous OFF, or, if you have to be more conservative, to NORMAL. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite Performance
To answer your question: Yes I can use a flat file at this stage, but eventually it needs to be imported into some type of structure. So to that end I decided early on to use sqlite to write the data out. I was hoping for better performance. The raw I/O to read the data and process is around .75 seconds (no write i/o).. So using a flat file output costs about .7 seconds. Using sqlite to do the output costs about 2.25 seconds. My question is why? And what can be done to improve this performance? John Stanton <[EMAIL PROTECTED]> wrote: Ken wrote: > I'm looking for suggestions on improving performance of my sqlite application. > > Here are system timings for a run where the sqlite db has been replaced with > a flat file output. > real 0m1.459s > user0m0.276s > sys 0m0.252s > > This is a run when using sqlite as the output format. > real 0m3.095s > user0m1.956s > sys 0m0.160s > > As you can see sqlite takes twice as long and almost 8 times the user time. > > Output size for flat file: 13, 360, 504flatfile.dat > Output size fo sqlit file: 11,042,816 sqlt.db f > > Slite db has the following pragmas set. > > PRAGMA default_synchronous=FULL > PRAGMA temp_store=memory > PRAGMA page_size=4096 > PRAGMA cache_size=2000 > > Any ideas how to get the sqlite output timings to a more respectable level > would be appreciated. > > Thanks > Ken > If you want flat file performance, use a flat file. Sqlite is built on top of a flat file and cannot be faster or even as fast. If your application can use a flat file, why use anything more complex? - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite Performance
I'm looking for suggestions on improving performance of my sqlite application. Here are system timings for a run where the sqlite db has been replaced with a flat file output. real 0m1.459s user0m0.276s sys 0m0.252s This is a run when using sqlite as the output format. real 0m3.095s user0m1.956s sys 0m0.160s As you can see sqlite takes twice as long and almost 8 times the user time. Output size for flat file: 13, 360, 504flatfile.dat Output size fo sqlit file: 11,042,816 sqlt.db f Slite db has the following pragmas set. PRAGMA default_synchronous=FULL PRAGMA temp_store=memory PRAGMA page_size=4096 PRAGMA cache_size=2000 Any ideas how to get the sqlite output timings to a more respectable level would be appreciated. Thanks Ken
[sqlite] API enhancement
Question for the list,
I'd like to optimize my code, using the following pseudo code as an example.
===
int i = 0 ;
char str[20];
sqlite3_prepare_v2( "insert into t1 values (?,?)" )
sqlite3_bind_int ( i )
sqlite3_bind_text(str)
BEGIN TRANSACTION
For (i = 0; i < 10; i++) {
sqlite3_step ( );
sqlite3_reset( )
}
COMMIT TRANSACTION
==
However, the above code will fail to insert the values for i in the loop. It
will only insert the value 0, since that was the binding value...
An enhancement request would be to allow the user to bind the address to the
statement objects. This would be a huge benefit from the standpoint of fewer
function calls to sqlite3_bind in the inside loop.
So maybe the following API:
sqlite3_pbind_int(sqlite3_stmt *, int, int * );
sqlite3_pbind_int64(sqlite3_stmt *, int, long long int * );
sqlite3_pbind_double(sqlite3_stmt *, int, dobule *);
sqlite3_pbind_text(sqlite3_stmt, int, const char *, int *, void(*)(void*));
notice the text takes a pointer to the length...
sqlite3_pbind_blob(sqlite3_stmt*, int, const char *, int *, void(*)(void*));
Concept of SQLITE_STATIC vs SQLITE_TRANSIENT is implied that all data is
SQLITE_STATIC.
Regards,
Ken
[sqlite] Link error with -DSQLITE_OMIT_TRIGGER
Compile error with -DSQLITE_OMIT_TRIGGER I get a link error when using: -DSQLITE_OMIT_TRIGGER === gcc -g -DSQLITE_DEFAULT_PAGE_SIZE=8192 -DSQLITE_DEFAULT_CACHE_SIZE=4000 -DSQLITE_OMIT_SHARED_CACHE -DSQLITE_OMIT_AUTHORIZATION -DSQLITE_OMIT_VIRTUAL_TABLE -DSQLITE_OMIT_TRIGGER -DSQLITE_OMIT_LOAD_EXTENSION -DOS_UNIX=1 -DHAVE_USLEEP=1 -DHAVE_FDATASYNC=1 -I. -I../sqliteSrc/sqlite-3.3.13/src -DNDEBUG -DTHREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -DHAVE_READLINE=1 -I/usr/include/readline -o .libs/sqlite3 ../sqliteSrc/sqlite-3.3.13/src/shell.c ./.libs/libsqlite3.so -lpthread -lreadline ./.libs/libsqlite3.so: undefined reference to `sqlite3DropTrigger' ./.libs/libsqlite3.so: undefined reference to `sqlite3BeginTrigger' ./.libs/libsqlite3.so: undefined reference to `sqlite3FinishTrigger' ./.libs/libsqlite3.so: undefined reference to `sqlite3TriggerDeleteStep' ./.libs/libsqlite3.so: undefined reference to `sqlite3DeleteTriggerStep' ./.libs/libsqlite3.so: undefined reference to `sqlite3TriggerSelectStep' ./.libs/libsqlite3.so: undefined reference to `sqlite3TriggerUpdateStep' ./.libs/libsqlite3.so: undefined reference to `sqlite3TriggerInsertStep' = And also from an application link: /home/ixion/LIB/sqlite3/.libs64/libsqlite3.a(parse.o): In function `yy_destructor': /home/ixion/LIB/sqlite3/parse.y:957: undefined reference to `sqlite3DeleteTriggerStep' /home/ixion/LIB/sqlite3/.libs64/libsqlite3.a(parse.o): In function `yy_reduce': /home/ixion/LIB/sqlite3/parse.y:924: undefined reference to `sqlite3FinishTrigger' /home/ixion/LIB/sqlite3/parse.y:930: undefined reference to `sqlite3BeginTrigger' /home/ixion/LIB/sqlite3/parse.y:973: undefined reference to `sqlite3TriggerUpdateStep' /home/ixion/LIB/sqlite3/parse.y:978: undefined reference to `sqlite3TriggerInsertStep' /home/ixion/LIB/sqlite3/parse.y:981: undefined reference to `sqlite3TriggerInsertStep' /home/ixion/LIB/sqlite3/parse.y:985: undefined reference to `sqlite3TriggerDeleteStep' /home/ixion/LIB/sqlite3/parse.y:988: undefined reference to `sqlite3TriggerSelectStep' /home/ixion/LIB/sqlite3/parse.y:1016: undefined reference to `sqlite3DropTrigger' collect2: ld returned 1 exit status
Re: [sqlite] database is locked error with 3.3.13
I have the following sources which one would you like? sqlite-2.8.16.tag.gz sqlite-3.3.8.tar.gz sqlite-3.2.2.tar.gz sqlite-3.3.9.tar.gz sqlite-3.2.8.tar.gz sqlite-3.3.10.tar.gz sqlite-3.3.12.tar.gz sqlite-3.3.13.tar.gz sqlite-3.3.5.tar.gz sqlite-3.3.7.tar.gz Ken T <[EMAIL PROTECTED]> wrote: Hi all, Following up: > I recently installed SQLite 3.3.13, after having used previous > versions. I now get an error: > > Error: database is locked > > when I use the sqlite3 command line tool to access a database on a > shared volume. > > But opening the same file with an earlier version works fine. > > I'm not sure what version introduced this problem. I suspect it's > after 3.3.9. It's definitely after 3.1.3. > > I'm using Mac OS X 10.4.8, with the database file on an AppleShare > mounted volume. I tried using sqlite 3.3.10 (which I had on another machine) and still have the problem. I looked for 3.3.9 to re-install it, to try that version, but couldn't find it on the sqlite.org web site. Have others experienced a locking error on remote volumes? Where can I get 3.3.9 and earlier source code? Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] not sqlite question, but design ? for IoMethod & os_????.c
Can you explain the IoMethod and IoFile from the os.h ??? I see the follwing in the code... #if OS_UNIX #define sqlite3OsOpenReadWrite sqlite3UnixOpenReadWrite . #endif #if OS_WIN #define sqlite3OsOpenReadWrite sqlite3WinOpenReadWrite #if OS_OS2 #define sqlite3OsOpenReadWrite sqlite3Os2OpenReadWrite etc... followed by IoMethod and IoFile type and struct delcarations. I understand the the defines are used as subtitutions in the pre processor for calls. int sqlite3OsOpenReadWrite(const char*, OsFile**, int*); --- Really becomes an instance of one of the above specific calls such as : sqlite3UnixOpenReadWrite My questions is why are the pointers stored in IxMethod ??? Since really the ifdefs above define the various interfaces to the operating system specific calls. why keep reference pointers to the functions inside of IxMethod ??? I'm building an interface into os system calls such as open/fopen read/fread and i'd like to be able to have the calling code dynamically set up which interace to use, so I'm trying to get a handle on the above code as a roadmap... Thanks, Ken
Re: [sqlite] use of sqlite in a multithread C application
I found that although sqlite claims thread safeness it is actually in your
hands to implement a thread safe access pattern.
Here is how I implemented my sqlite thread saftey. Each thread opens its on
connection.
All operations begin with a
do {
BEGIN EXCLUSIVE
if (isbusy) (sleep some amount of time... ).
} while (sqlite isbusy);
YOUR SQL STATEMENTS HERE.
And end with a
COMMIT;
The begin forces sqlite to lock the DB for exclusive access. This makes
the remaining access error handling relatively simple. No need to check for
busy and do rollbacks with restarting logic... Just handle errors, in my log
the error to a file and returns a Failure code which typically causes the
application to exit.
Don't to forget to compile the sqlite library with -DTHREAD_SAFE
Rafi Cohen <[EMAIL PROTECTED]> wrote: Hi, I read the good article on this
subject and also the api refference
in the documentation and still feel that I need to ask the following
question.
My application has 2 threads. The main one needs to retrieve data thru
select commands but does not change the database, while the other thread
will change the database quite often.
My questions are: should I open the database in each thread separately
or can I open it once for the whole program? in the second case, does it
matter inh which thread I open it? last, if the main thread happens to
retrieve data while the other thread is in a transaction changing the
database, I would prefer the main thread wait till the end of the
transaction and retrieve the most updated data. How do you suggest to
implement it?
looping on sqlite3_busy_handler should be the way to go?
Thanks, Rafi.
Re: [sqlite] (newbie) pictures in tables, what's the best way ?
This is a design question really so here are my recomendations.
Persons
- ID( an integer primary key )
- Name
- Birthday
- Picture Type ( your two digit type).
Picture
- ID (An integer Primary Key that matches the ID of persons).
- image (blob)
Thus if you alread have the Persons record, you can get the picture as a
simple select on the Picture.image field by
select image from Picture where id = ?
Stef Mientki <[EMAIL PROTECTED]> wrote:
Cesar Rodas wrote:
> Save into a blob.
>
thanks but that's not what I meant.
I'll try to explain with an example:
I want to create this database
Persons
- Name
- Birthday
- Picture
Now when I want to view this database with a general database browser /
manager,
I'll first find out what tables there are in this database,
Secondly I'm going to ask for an overview of each table by, that will be
displayed in a flat table (2-dimensional matrix)
SELECT * FROM Persons
Now the browser sees the blob field at displays is something like "{blob}"
So I assume, that the browser get's all the data, including all the bits
and bytes of each image in each record,
so a whole lot of not-needed data, because the database browser /
manager can con display 1 picture from a selected record.
So I think, it would be more efficient to create 2 tables:
Persons
- Name
- Birthday
- ID ("this is a picture")
Blobs
- Name
- Picture
Now if I ask for the main table "Persons", I just get an ID (some
integer), that tells me that there is a picture.
The browser / manager can ask for just one record from the table Blobs,
to show me the picture of the selected line.
I've never seen such a construct in examples, so maybe I see ghosts
where there are none.
forgive me my ignorance.
cheers,
Stef Mientki
> here is a C example http://www.sqlite.org/cvstrac/wiki?p=BlobExample
>
> On 23/02/07, Stef Mientki wrote:
>> hello,
>>
>> I've started with SQLite, because it's one of the first dbases with a
>> local filessytem without authenciation.
>> It looks great, compared to ini-files.
>>
>> Now I've a basic problem:
>> I've a table, with 10 simple fields and 1 blob field, containing a
>> (possibly large) image.
>> Uptill now the dbase is small so no performance problems yet.
>> But what if it grows ?
>>
>> I want to look at the table in a table overview,
>> where the blob-field just displays the type of image
>> (identified by the fisrt 2 bytes SOI-code).
>>
>> When I ask for the table through a query,
>> I get all the blob-filed information, while I only need the first 2
>> bytes,
>> and only the full picture of 1 selected record.
>>
>> I can think of several solutions, but as my experience with databases is
>> almost zero, I can't decide
>> (and maybe I'm overlooking some better solutions)
>> solution 1:
>> add a extra field for each blob field, filled with the first 2 bytes of
>> a blob
>> solution 2:
>> put the blob fields in a separate table, with a link to the main table
>>
>> any suggestions would be appreciated.
>> thanks,
>> Stef Mientki
>>
>> -
>>
>>
>> To unsubscribe, send email to [EMAIL PROTECTED]
>> -
>>
>>
>>
>>
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Atomically creating a database and bootstrapping its tables
To bootstrap my db's I create a database template. Then make a physical copy of that. Locking and access is done via flock. So the first process to gain the lock wins and is respoonsible for making the copy, the other just waits until the lock is released and then connects. I make lots of databases and found that creating a template then copying is much faster than using the sqlite api to create the db, then to create the individual tables. Ron Stevens <[EMAIL PROTECTED]> wrote: I have two processes trying to access a database for the first time at roughly the same time. I'm wondering if it's possible to atomically create a database and bootstrap it with some tables from one process so that the other process doesn't open the database either before SQLite has finished writing the system tables or the first process has finished the application specific bootstrapping. In general, does SQLite protect against the database being opened while it's being created so other processes don't open it assuming everything is in a good state? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: Re[2]: [sqlite] how can i speed up inserts ?
Try without the pragma and wrap the inserts with a begin transaction and a commit... The performance will be almost as good as with the pragma, with the added benefit of consistent data and no corruption in the event of a crash or power failure. DragonK <[EMAIL PROTECTED]> wrote: On 2/7/07, Teg wrote: > > Hello ohadp, > > Well, my experience is, particularly when it's users using it and not > a personal project, that corruption happens fairly frequently when you > use this pragma. That's why I don't use it any more in my production > code. > > Transactions are far safer and fast too. > > Indeed, transactions are safer. But I wonder, why did you experienced corruption with this pragma? Was it because of crashes of the OS or the application? Or are there other factors which can corrupt the data if not syncing ? As I understood from the documentation, the only thing that can corrupt data when using this pragma are crashes and power failures. -- ...it's only a matter of time...
Re: [sqlite] database is locked (my solution)
Andrew, I wouldn't worry about it too much, at least your contributing. There are quite a few threads regarding locking, concurrency and multi threading. IMHO, everyone would benefit from your modification. I would especially like to be able to deal with Locking issues at the beginning of a transaction instead of during the middle. You can open a ticket by going into http://www.sqlite.org/cvstrac/wiki and clicking on ticket. Ken Andrew Teirney <[EMAIL PROTECTED]> wrote: > Andrew, > > Nice modification.. Did you buy any chance post this into the sqlite > ticketing system? > > It would be nice if sqlite would let you acquire a shared lock via the BEGIN > statement. > No i didn't actually post this to the ticketing system, to be honest i am very new to all this open source stuff and the procedures and that associated with requesting changes and providing patches. By all means I am okay with posting a ticket, i guess i have this fear that i'll do something not quite right and i'll get flamed for it ;-) Andrew - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] database is locked (my solution)
Andrew, Nice modification.. Did you buy any chance post this into the sqlite ticketing system? It would be nice if sqlite would let you acquire a shared lock via the BEGIN statement. Andrew Teirney <[EMAIL PROTECTED]> wrote: > I have multiple thread reading the database and a thread inserting in or > updating the database. > when i try to simulate this case by executing sqlite3 from shell by > beginning a transaction from a shell and retrieving data from other shell > eveything works fine. > But when it's the cas from my C program it raises the error message > database > is locked. > How can i avoid this issue? > thanks a lot I myself was running into this problem a lot whilst trying to use a lua binding to sqlite where each thread used its own connection (obtained via a connection pool). The reason for getting the "database is locked" is that i was creating situations where there would be a deadlock because of the type of locks held by the various connections, one of them would have to yeild to allow the other to continue. This baffled me for a bit as i noticed my busy handler was not being called. The solution i found was to wrap the complete operation i was doing in a transaction via "BEGIN" ... "END". I did however find the default style of "BEGIN" (which is a deffered style lock) i could end up with database is locked problem, this was because there would have been a deadlock in accessing the database due to the deffered style of locking. So alas i did some more investigation. In the end i started to use "BEGIN IMMEDIATE" which acquires a pending lock on execution of the BEGIN, thus i could be sure that the lock required for the operation i was going to perform was granted, this also meant that any busy handler would be run whilst acquiring the lock. However because of the usage pattern where there were multiple readers and a single writer this was obviously not the best idea, as it meant there could only be one thread/process accessing the database at one time. To get around this i wanted to be able to acquire a "SHARED" lock via a "BEGIN" statement, have any busy handling operating around there. Unfortunately sqlite by default doesn't support acquiring a shared lock when the "BEGIN" is executed, even with a type specifier, for this i extended sqlite to enable a "BEGIN SHARED" style of transaction where this shared lock has been acquired. In doing this i was able to do all my busy handling around the "BEGIN" statements, and have multiple readers read from the db via "BEGIN SHARED", and then have writers call "BEGIN IMMEDIATE". More info in the "BEGIN [TRANSACTION] ..." can be found at http://www.sqlite.org/lang_transaction.html I would strongly suggest reading http://www.sqlite.org/lockingv3.html to get an overview of the different state of locks that can be acquired on the database. If you are interested in the "BEGIN SHARED" transaction i posted a simple patch to this mailing list within the last month if i recall correctly. Hope this helps, Andrew - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Auto-detection of database change in multi-process environment
As I see it you have only 3 options. 1. Polling. Polling on a table in sqlite or depending upon your app. You could simply check the file access modifiers to see when the last modifaction time was. 2. Set up an IPC semaphore 3. Set up a socket. David GIGUET <[EMAIL PROTECTED]> wrote: Hi, Iam working on the design of a multi-process architecture accessing a single database. Is there a way for one of the process to detect that another process has modified the database ? I would like to avoid regular polling of the database to check for modifications and I also would like to avoid ipc or creation of an sqlite server on top of the database. If it does not exist do you think I can open the database file (either with sqlite or with file system), create a thread with a select or sqlite_busy_handler that is pending on detection of file modification ? Thanks for your help, David " Ce courriel et les documents qui y sont attaches peuvent contenir des informations confidentielles. Si vous n'etes pas le destinataire escompte, merci d'en informer l'expediteur immediatement et de detruire ce courriel ainsi que tous les documents attaches de votre systeme informatique. Toute divulgation, distribution ou copie du present courriel et des documents attaches sans autorisation prealable de son emetteur est interdite." " This e-mail and any attached documents may contain confidential or proprietary information. If you are not the intended recipient, please advise the sender immediately and delete this e-mail and all attached documents from your computer system. Any unauthorised disclosure, distribution or copying hereof is prohibited."
Re: [sqlite] How to specify collating sequences in an expression.
select x, y, z from t1 where collate binary x = y ; collating_expr ::= [collate ] expr ; The collating expression would apply to both x and y. [EMAIL PROTECTED] wrote: Dennis Cote wrote: > > In the standard character strings have a couple of attributes, a > character set and a collation. SQLite does not support multiple > character sets, so we can ignore that attribute. This leaves each string > with a collation attribute. This attribute can be specified explicitly > in data type clause of a column definition, or in the data type clause > of a cast expression, or directly with an explicit COLLATE clause after > a string expression, even on a string literal. > > create table t (column1 text COLLATE ) > CAST( AS text COLLATE ) > column1 COLLATE > 'a string' COLLATE What are the precedences. If I say: x COLLATE seq1 || y COLLATE seq2 Does that mean: (x COLLATE seq1) || (y COLLATE seq2) Or does it mean ((x COLLATE seq1) || y) COLLATE seq2 -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to specify collating sequences in an expression.
Based on the example, I was under the impression you were trying to fix a comparison operator. Oracle used hints( ie comments embedded in the sql) to tell the optimizer which index to select. Cant you assign a collating sequence in the ordre by? Why not use that to determine if there is an index to use? It seems that then if one were to do the following Is this ambiguous ??? select x from t1 , t 2where cast(t1.x as text collate nocase) = t2.y order by X collate binary ; Seems to me this is simply a filtering or comparison mechanism not for ordering, as ordering is already handled by the order by clause. In the case of Oracle, any time a function ie (toupper () ) is applied to a column that is part of the index in a join clause, then the index is no longer considered for the join operation and a Full table scan will occur. Since in reallity the two are not really joinable, so for oracle a join operation must be binary at least thats with simple indexes. There are Function based indexes that will allow the user to define any function they choose to apply to the indexed columns. Then the optimizer will select that index when the function is applied to the join attributes that match... IMHO the cast is the way to go to assign a collating sequence. Maybe you need an additional index type ? One where the index is specified with a function. Ken [EMAIL PROTECTED] wrote: "Igor Tandetnik" wrote: > > MS SQL also supports defining multiple indexes on the same table and > field(s), differing only in collation (and the optimizer is smart > enough, most of the time, to use these indexes appropriately). I haven't > tried it with SQLite, maybe it's also supported. > SQLite also supports multiple indices differing only in collation, and the optimizer will use the appropriate index to satisfy the ORDER BY clause. But because the SQLite parser does not currently provide a means to change the collating sequence assigned to an expression, there is no way for the optimizer to use different indices for optimizing access since an expression in the WHERE clause can only have a single collation. That's what I'm trying to fix -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to specify collating sequences in an expression.
After re-reading this: (2) Oracle does the following: Binary and Linguistic sorting: Binary is the default. Linguistic sorting is configured by setting NLS_COMP=LINGUISTIC and Setting NLS_SORT to a language specific sort rule. (these can be set at the session level or DB etc, via an alter session command, alter system command or by environment variables) As such there is no mechanism to change the sorting element for a single column. Case comparisons are handled by toupper, tolower and other sql functions are plentiful. It also utilizes a "Blank-padded/ nonpadded" comparis when dealing with VARCHAR vs CHAR datatypes. But that is probably out of scope since sqlite is loosely typed. (4). I like the following syntax: SELECT * FROM t1 WHERE CAST(x AS TEXT COLLATE NOCASE)='HELLO'; As it pretty clearly modifies the "default" attribute. Ken
Re: [sqlite] How to specify collating sequences in an expression.
If you are dealing with say a chinese char set then wouldn't you want to handle this at a "global" level by modifying the database characteristics, then maybe a Pragma command would be the way to go. [EMAIL PROTECTED] wrote: Ken wrote: > DRH and Sqlite Community, > > Provide two sql functions: toupper() and tolower() that can be > applied. And always use binary comparison. > > so: > select x from where toupper(y) = 'HELLO' ; > would return 1 row... > > But here is the gotcha, more than likely applying that function > would negate the use of an index that would be on column y. It would indeed. But more importantly, this technique only works for case comparisons. I'm looking for a general solution that will work on any collating sequence, such as comparison functions on Chinese text. Toupper() is not help there. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to specify collating sequences in an expression.
DRH and Sqlite Community,
Provide two sql functions: toupper() and tolower() that can be applied. And
always use binary comparison.
so:
select x from where toupper(y) = 'HELLO' ;
would return 1 row...
But here is the gotcha, more than likely applying that function would negate
the use of an index that would be on column y. I would think that apply a
function transformation for use on an indexed column would have pretty bad
performance.
On the other hand something like:
select y from where x= tolower('HELLO' ) ;
would also return 1 row. And should have no problems utilizing an existing
indices since the function is only applied once to the sql, not to the stored
index data values.
Also other systems such as Oracle for instance use things like NLS_COMP,
NLS_SORT to manipulate sorting characteristics. These can be environment
variables ora can be set as part of an "alter session" command (Similar to a
sqlite PRAGMA).
Regards,
Ken
[EMAIL PROTECTED] wrote: SQLite has supported collating sequences since version
3.0.0.
A collating sequence is really a specification on how comparison
operators work on strings.
You can have arbitrary collating sequences in SQLite. But for
the sake of this discussion, lets focus on just two: BINARY
which is the default collating sequence, and NOCASE which does
case insensitive comparisons (on US-ASCII).
Consider this example table:
CREATE TABLE t1(
x TEXT COLLATE BINARY,
y TEXT COLLATE NOCASE
);
INSERT INTO t1 VALUES('hello','Hello');
This query returns one row because comparisons against column y
ignore case:
SELECT * FROM t1 WHERE y='HELLO';
This query returns no rows because comparisons against column x
take case into account.
SELECT * FROM t1 WHERE x='HELLO';
When comparing two columns, the column on the left determines which
collating sequence to use. In the first of the following two queries,
the column on the left uses NOCASE so one row is returned. But in
the second query, the columns are reversed and the left-most column
uses BINARY. As a result, no rows are returned from the second
query:
SELECT * FROM t1 WHERE y=x;
SELECT * FROM t1 WHERE x=y;
This last point seems a little goofy, but SQLite is documented as
working that way and the situation comes up so very rarely that nobody
has yet complained.
The problem with all of the above is that the collating sequence
on a column is specified when the column is declared and cannot
be changed. What many people would like to have is some way to
specify a different collating sequence to override the default
collating sequence for a single comparison. The question is, what
should the syntax be. Here are some ideas:
SELECT * FROM t1 WHERE x='HELLO' COLLATE NOCASE;
Here the = operator has some added syntax at the end. There some
parsing abiguities with this approach, but they can be dealt with
just as they are with the EXCEPT clause on a LIKE operator. The
comparison operator syntax would be like this:
[COLLATE ]
Another idea is to use CAST:
SELECT * FROM t1 WHERE CAST(x AS TEXT COLLATE NOCASE)='HELLO';
Or perhaps
SELECT * FROM t1 WHERE CASE(x COLLATE NOCASE)='HELLO';
A third idea is to invent entirely new syntax, perhaps like this:
SELECT * FROM t1 WHERE COLLATE NOCASE(x='HELLO')
Please note that while I have used the = operator in all of the
examples above, everything applies equally to !=, <, <=, >, and >=.
Questions for SQLite community members:
(1) Is there some standard SQL syntax that I do not know about
for doing this kind of thing?
(2) How do other SQL engines do this kind of thing?
(3) Do you have any additional (hopefully better) ideas for
specifying alternative collating sequences on individual
comparison operators.
(4) What syntax do you prefer?
--
D. Richard Hipp
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Segfault when querying deeply nested view
I reproduced this as well on Suse 10 and 3.3.7
Using the following data:
insert into records values ( date('NOW'), 'D/D', 'NPOWER','20','test
acc','123456') ;
insert into records values(date('2006-01-12'), 'D/D', 'NPOWER','20','test
acc','123456') ;
insert into records values(date('2006-11-15'),'D/D', 'NPOWER','20','test
acc','123456') ;
sqlite> select * from summary;
Segmentation fault
-
Running against 3.3.10 appears to function !!!
SQLite version 3.3.10
Enter ".help" for instructions
sqlite> .read segfault.sql
SQL error near line 4: table records already exists
jan|Energy|-40|nov|Energy|-40
jan|Energy|-40|nov|Energy|-40
Andy Chambers <[EMAIL PROTECTED]> wrote: Hi List,
I've found a situation which causes sqlite to segfault.
System Info
-
OS: Debian Etch
Sqlite Version: 3.3.8
Run the attached sql script to see segfault.
I'd be interested to hear of a workaround if anybody knows of one. Of
course, I'll post to the list if I find one.
-- Raw data (RBS)
create table records (
date real,
type text,
description text,
value integer,
acc_name text,
acc_notext
);
-- Direct Debits
drop view direct_debits;
create view direct_debits as
select *
from records
where type = 'D/D';
drop view monthly_direct_debits;
create view monthly_direct_debits as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
group by strftime('%Y-%m', date);
-- Expense Categories ---
drop view energy;
create view energy as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
where description like '%NPOWER%'
group by strftime('%Y-%m', date);
drop view phone_internet;
create view phone_internet as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
where description like '%BT DIRECT%'
or description like '%SUPANET%'
or description like '%ORANGE%'
group by strftime('%Y-%m', date);
drop view credit_cards;
create view credit_cards as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
where description like '%VISA%'
group by strftime('%Y-%m', date);
-- Overview -
drop view expense_overview;
create view expense_overview as
select 'Energy' as expense, date, value
from energy
union
select 'Phone/Internet' as expense, date, value
from phone_internet
union
select 'Credit Card' as expense, date, value
from credit_cards;
drop view jan;
create view jan as
select 'jan', expense, value
from expense_overview
where date like '%-01';
drop view nov;
create view nov as
select 'nov', expense, value
from expense_overview
where date like '%-11';
drop view summary;
create view summary as
select *
from jan join nov
on (jan.expense = nov.expense);
-- This causes a segfault
select *
from
summary;-
To unsubscribe, send email to [EMAIL PROTECTED]
---
Andy Chambers <[EMAIL PROTECTED]> wrote: Hi List,
I've found a situation which causes sqlite to segfault.
System Info
-
OS: Debian Etch
Sqlite Version: 3.3.8
Run the attached sql script to see segfault.
I'd be interested to hear of a workaround if anybody knows of one. Of
course, I'll post to the list if I find one.
-- Raw data (RBS)
create table records (
date real,
type text,
description text,
value integer,
acc_name text,
acc_notext
);
-- Direct Debits
drop view direct_debits;
create view direct_debits as
select *
from records
where type = 'D/D';
drop view monthly_direct_debits;
create view monthly_direct_debits as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
group by strftime('%Y-%m', date);
-- Expense Categories ---
drop view energy;
create view energy as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
where description like '%NPOWER%'
group by strftime('%Y-%m', date);
drop view phone_internet;
create view phone_internet as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
where description like '%BT DIRECT%'
or description like '%SUPANET%'
or description like '%ORANGE%'
group by strftime('%Y-%m', date);
drop view credit_cards;
create view credit_cards as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
where description like '%VISA%'
group by strftime('%Y-%m', date);
-- Overview -
drop view expense_overview;
create view expense_overview as
select 'Energy' as expense, date, value
from
Re: [sqlite] Re: Shared Lock Transactions
>From os_unix.h:... After reading this, locking makes more sense! Although the Lock may physically be an exclusive lock, the implementation is actually a logcially "SHARED" lock. /* The following describes the implementation of the various locks and ** lock transitions in terms of the POSIX advisory shared and exclusive ** lock primitives (called read-locks and write-locks below, to avoid ** confusion with SQLite lock names). The algorithms are complicated ** slightly in order to be compatible with windows systems simultaneously ** accessing the same database file, in case that is ever required. ** ** Symbols defined in os.h indentify the 'pending byte' and the 'reserved ** byte', each single bytes at well known offsets, and the 'shared byte ** range', a range of 510 bytes at a well known offset. ** ** To obtain a SHARED lock, a read-lock is obtained on the 'pending ** byte'. If this is successful, a random byte from the 'shared byte ** range' is read-locked and the lock on the 'pending byte' released. ** ** A process may only obtain a RESERVED lock after it has a SHARED lock. ** A RESERVED lock is implemented by grabbing a write-lock on the ** 'reserved byte'. ** ** A process may only obtain a PENDING lock after it has obtained a ** SHARED lock. A PENDING lock is implemented by obtaining a write-lock ** on the 'pending byte'. This ensures that no new SHARED locks can be ** obtained, but existing SHARED locks are allowed to persist. A process ** does not have to obtain a RESERVED lock on the way to a PENDING lock. ** This property is used by the algorithm for rolling back a journal file ** after a crash. ** ** An EXCLUSIVE lock, obtained after a PENDING lock is held, is ** implemented by obtaining a write-lock on the entire 'shared byte ** range'. Since all other locks require a read-lock on one of the bytes ** within this range, this ensures that no other locks are held on the ** database. ** ** The reason a single byte cannot be used instead of the 'shared byte ** range' is that some versions of windows do not support read-locks. By ** locking a random byte from a range, concurrent SHARED locks may exist ** even if the locking primitive used is always a write-lock. */ "A. Pagaltzis" <[EMAIL PROTECTED]> wrote: * Jay Sprenkle [2007-01-22 15:20]: > My understanding was that a "shared lock" is a metaphor, and > IMHO, a fairly stupid one. If you lock a room, nobody else can > get in, it's not a mechanism for sharing, it's a mechanism for > preventing sharing. Reasoning by analogy rarely leads to anything but a fallacy. A shared lock prevents exclusive locks from being granted and an exclusive lock prevents shared locks from being granted, so Iâm not sure what sort of sharing/preventing business youâre talking about anyway. Regards, -- Aristotle Pagaltzis // - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: RE: [sqlite] DROP INDEX not freeing up memory
Do you have to drop the index? Why not just keep it around, its obviously useful if you need to create it in the first place right? Dave Gierok <[EMAIL PROTECTED]> wrote: It does in fact look like the memory isn't being freed up entirely. I am properly tracking xMalloc, xRealloc, and xFree. I have a memory database and wrote some test code to loop a few times creating/dropping the same index. The results of that are (numbers are total bytes allocated): 7632746 Before 1st Create Index 7637587 After 1st Create Index 7637434 After 1st Drop Index (and Vacuum -- the vacuum doesn't change memory allocated though) 7637587 After 2nd Create Index 7637434 After 2nd Drop Index (and Vacuum) 7637587 After 3rd Create Index 7637434 After 3rd Drop Index (and Vacuum) Notice that the memory slightly decreases after the 1st Drop Index, but doesn't nearly drop to what it should (it should drop to 7632746 -- the same level as before the 1st Create Index). Also notice that after the 1st create/drop, the memory allocated is the same after each create and after each drop. So it implies there is not a leak -- but we can't get down to the original level before we created the first index. So what's the big deal you might ask -- this example shows 5K that can't be reclaimed. But in our game we create hundreds of indices that take up about 2MB -- 2MB is quite a bit of memory in our world. Any suggestions or explanations? Thanks, Dave -Original Message- From: Dave Gierok [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 17, 2007 9:06 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] DROP INDEX not freeing up memory OK, thanks, I'll do some more digging and let you know. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 17, 2007 8:30 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] DROP INDEX not freeing up memory Dave Gierok wrote: > xTruncate is not being called because this is an in-memory database. Memor= > y databases should have memory freed instead I assume? > In-memory databases call sqliteFree() to release their memory. I checked, and this does appear to work. Perhaps the sqliteFree() call is somehow not making it down into your measurement layer. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] DROP INDEX not freeing up memory
If you are using the OS to determine if memory is freed then perhaps that is
where the problem lies. Many operating systems do not return memory back to the
OS after a call to free. Instead the process will retain that in a "free pool"
for later re-allocation.
Ken
Dave Gierok <[EMAIL PROTECTED]> wrote:
xTruncate is not being called because this is an in-memory database. Memory
databases should have memory freed instead I assume?
Thanks,
Dave
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 16, 2007 3:07 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DROP INDEX not freeing up memory
Dave Gierok wrote:
>
> I am running an in-memory db and am using the functionality where
> we override all the OS calls such that we implement our own memory
> management and file IO. Therefore, I am able to easily track the
> amount of memory Sqlite is taking up. I have noticed that when I
> call CREATE INDEX and then the corresponding DROP INDEX, I do not
> see the memory drop to the same level as it was before the CREATE
> INDEX. I also call VACUUM after DROP INDEX, which makes no difference.
>
I tried this using the built-in database functionality and the
memory is being freed there. I did:
CREATE TABLE t1(x);
INSERT INTO t1 VALUES('hello');
CREATE INDEX i1 ON t1(x);
DROP INDEX i1;
VACUUM;
And then set a breakpoint at pager.c:1972 where the freeing of
memory occurs, and it does happen.
But I have no way to trouble-shoot your overridden OS calls.
The first place I would look would be in your implementation
of sqlite3OsTruncate(). Are you sure you are doing it right?
Does sqlite3OsTruncate() get called when you VACUUM?
--
D. Richard Hipp
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Pager modification question
Peter, I'm in agreement with you, as you say as long as it doesn't loose its core features. John, Its not that I want different. Its that sqlite could be made capable of handling internal synchronization. I certainly don't wish to loose the embedded capability nor its simplicity. I do think the feature of handling a global shared cache, allowing multiple threads access is a very good idea. One that is partially implemented IMHO. I would just like to see that model extended a bit to include page level locking on the shared cache. This would be the best of both, still simple. But a bit more concurrent access. Ken John Stanton <[EMAIL PROTECTED]> wrote: If you want to share a file you have to be able to synchronize access in some way. The POSIX type file locks permit you to do it quite well by giving read and write locks. If you want shared access to a file from multiple processes you either need some form of co-operative lock like a semaphore or a safer method such as the POSIX locks which enforce co-operation. If you are sharing the resource among multiple threads in a single process you have the option of using a co-operative lock like a mutex/critical section or to use the POSIX locks and not bother with all the co-op logic. Underneath the covers Sqlite does an fopen on the file and fcntl's for locking. It shares the file just as any application would share access to a file. If you want different, use a server based RDBMS like PostgreSQL. By having a central server it can do what it likes to implement synchronization. Ken wrote: > If it is a replacement for fopen, then why does it perform locking at all? > Since sqlite has implemented threading and multiple connections, then the > next logical step in its evoloution is to enable advanced locking techniques > for even greater degrees of improved concurrency. > > Ken > > > John Stanton wrote: If Sqlite were to implement its own locking then it > would lose > simplicity and versatility. > > A good way to regard Sqlite is a replacement for fopen. It is a way an > application can access a single file. If you want more than that you > are not looking for "'lite" and should go straight to PostgreSQL rather > than trying to convert Sqlite into PostgreSQL, Oracle or DB/2. > > Ken wrote: > >>Regarding the locking: >> >>Yes certainly only within a single process architecture. I understand that >>SQLITE is usein g file locks to co-ordinate multiple process locks for unix >>is fcntl. (Fcntl is an ugly beast, imho sqlite would be better served >>managing locks internally). >> >> I guess there are really two camps of uses for sqlite: >> Users that build based upon a multi process system. >> And embedded users who build a single process with multiple threading. >> >> So is sqlite geared towards multiple process locking (current design says it >> is). >> >>Why not enable it to also suppport multiple thread with a higher concurrency >>level by using page level locking (or row level for that matter). >> >> >> Either way I think its a great piece of software. Thanks DRH. >> >> >> >>Christian Smith wrote: Ken uttered: >> >> >> >>>Would it be possible to implement a Page level locking system using >>>sqlite? >> >> >>Deadlock detection/recovery and/or prevention is much more complex for >>page level locking. As there is no shared data between SQLite clients >>other than the database file itself, tracking locks in other clients is >>impossible beyond the OS level locking. >> >>A single process with multiple threads could feasibly track page level >>locking, but it would be intra-process only, and would complicate the >>locking significantly for relatively little benefit. >> >>Note that a writer can already operate concurrently with readers until it >>fills it's cache, and only has to have exclusive use of the database when >>synching it's cache on spill or commit. >> >> >> >>>I seem to recall a commercial product Ingress that utilized a page level >>>locking scheme that would escalte to a table level lock when over a >>>certain % of a tables pages were locked. >> >> >> >>If you want multiple concurrent writers, use PostgreSQL instead. It uses >>MVCC to avoid even row level locks. And it has an embeddded SQL >>pre-processor to boot. >> >> >> >> >>>This obviously would be very beneficial for those of us who are >>>implementing threaded applications using sqlite. The shared cache >>>concept would need to be extended
Re: [sqlite] Pager modification question
If it is a replacement for fopen, then why does it perform locking at all? Since sqlite has implemented threading and multiple connections, then the next logical step in its evoloution is to enable advanced locking techniques for even greater degrees of improved concurrency. Ken John Stanton <[EMAIL PROTECTED]> wrote: If Sqlite were to implement its own locking then it would lose simplicity and versatility. A good way to regard Sqlite is a replacement for fopen. It is a way an application can access a single file. If you want more than that you are not looking for "'lite" and should go straight to PostgreSQL rather than trying to convert Sqlite into PostgreSQL, Oracle or DB/2. Ken wrote: > Regarding the locking: > > Yes certainly only within a single process architecture. I understand that > SQLITE is usein g file locks to co-ordinate multiple process locks for unix > is fcntl. (Fcntl is an ugly beast, imho sqlite would be better served > managing locks internally). > > I guess there are really two camps of uses for sqlite: > Users that build based upon a multi process system. > And embedded users who build a single process with multiple threading. > > So is sqlite geared towards multiple process locking (current design says it > is). > > Why not enable it to also suppport multiple thread with a higher concurrency > level by using page level locking (or row level for that matter). > > > Either way I think its a great piece of software. Thanks DRH. > > > > Christian Smith wrote: Ken uttered: > > >>Would it be possible to implement a Page level locking system using >>sqlite? > > > Deadlock detection/recovery and/or prevention is much more complex for > page level locking. As there is no shared data between SQLite clients > other than the database file itself, tracking locks in other clients is > impossible beyond the OS level locking. > > A single process with multiple threads could feasibly track page level > locking, but it would be intra-process only, and would complicate the > locking significantly for relatively little benefit. > > Note that a writer can already operate concurrently with readers until it > fills it's cache, and only has to have exclusive use of the database when > synching it's cache on spill or commit. > > >>I seem to recall a commercial product Ingress that utilized a page level >>locking scheme that would escalte to a table level lock when over a >>certain % of a tables pages were locked. > > > > If you want multiple concurrent writers, use PostgreSQL instead. It uses > MVCC to avoid even row level locks. And it has an embeddded SQL > pre-processor to boot. > > > >>This obviously would be very beneficial for those of us who are >>implementing threaded applications using sqlite. The shared cache >>concept would need to be extended so that a global context for the >>shared cache would be maintained, allowing each individual thread that >>connects (sqlite3_open) to utilize the shared cache for that DB. >> >>The benefit would be to move the Locking from the DB level down to the >>page level. Keep a list of the before image pages in memory so that >>readers are not blocked by the writers (lock holders) of the pages. >> >>When the writer txn completes, Mark it txn as completed in the journal. >> >>I think it would be beneficial to visit the journal design as well for >>this. Mainly don't open/close journals, since a journal is always part >>of the DB pre-build the journal and allow multiple threads of writers >>into the journal. This should also help with some of the areas inside >>the pager/journal code where a random checksum is used to validate the >>journal upon crash recovery. >> >>Just some ideas to improve concurrency that I had and wanted to jot >>down. > > > > The current journal implementation is simple and fast. Using a single > rollback journal, rather than a redo-undo journal minimizes the amount of > data that has to be written, and the moves the needs for asynchronous > commits that would be required for any other journal design. Consider the > alternatives for a minute: > > - Current journal design: >Pros: simple, fast for single writer big transactions. >Cons: Single writer only. Writer block readers. D in ACID requires > multiple file syncs. > > - Write Ahead Redo-Undo log: >Pros: Potentially multiple writers, ACID with single file sync. >Cons: Difficult to coordinate between multiple processes. Requires > async process to write log entries to database fil
Re: [sqlite] Pager modification question
Regarding the locking: Yes certainly only within a single process architecture. I understand that SQLITE is usein g file locks to co-ordinate multiple process locks for unix is fcntl. (Fcntl is an ugly beast, imho sqlite would be better served managing locks internally). I guess there are really two camps of uses for sqlite: Users that build based upon a multi process system. And embedded users who build a single process with multiple threading. So is sqlite geared towards multiple process locking (current design says it is). Why not enable it to also suppport multiple thread with a higher concurrency level by using page level locking (or row level for that matter). Either way I think its a great piece of software. Thanks DRH. Christian Smith <[EMAIL PROTECTED]> wrote: Ken uttered: > Would it be possible to implement a Page level locking system using > sqlite? Deadlock detection/recovery and/or prevention is much more complex for page level locking. As there is no shared data between SQLite clients other than the database file itself, tracking locks in other clients is impossible beyond the OS level locking. A single process with multiple threads could feasibly track page level locking, but it would be intra-process only, and would complicate the locking significantly for relatively little benefit. Note that a writer can already operate concurrently with readers until it fills it's cache, and only has to have exclusive use of the database when synching it's cache on spill or commit. > > I seem to recall a commercial product Ingress that utilized a page level > locking scheme that would escalte to a table level lock when over a > certain % of a tables pages were locked. If you want multiple concurrent writers, use PostgreSQL instead. It uses MVCC to avoid even row level locks. And it has an embeddded SQL pre-processor to boot. > > This obviously would be very beneficial for those of us who are > implementing threaded applications using sqlite. The shared cache > concept would need to be extended so that a global context for the > shared cache would be maintained, allowing each individual thread that > connects (sqlite3_open) to utilize the shared cache for that DB. > > The benefit would be to move the Locking from the DB level down to the > page level. Keep a list of the before image pages in memory so that > readers are not blocked by the writers (lock holders) of the pages. > > When the writer txn completes, Mark it txn as completed in the journal. > > I think it would be beneficial to visit the journal design as well for > this. Mainly don't open/close journals, since a journal is always part > of the DB pre-build the journal and allow multiple threads of writers > into the journal. This should also help with some of the areas inside > the pager/journal code where a random checksum is used to validate the > journal upon crash recovery. > > Just some ideas to improve concurrency that I had and wanted to jot > down. The current journal implementation is simple and fast. Using a single rollback journal, rather than a redo-undo journal minimizes the amount of data that has to be written, and the moves the needs for asynchronous commits that would be required for any other journal design. Consider the alternatives for a minute: - Current journal design: Pros: simple, fast for single writer big transactions. Cons: Single writer only. Writer block readers. D in ACID requires multiple file syncs. - Write Ahead Redo-Undo log: Pros: Potentially multiple writers, ACID with single file sync. Cons: Difficult to coordinate between multiple processes. Requires async process to write log entries to database file. - Write Ahead Redo log: Pros: Simpler than Redo-Undo log. ACID with single file sync. Cons: No better write concurrency than current journal. Still requires async process to write log entries to database file. > > DRH, just reviewd the tokenize.c and pager.c, I concurr that Yacc/bison > have the token passing incorrect. Your implementation of tokenize is > very interesting, what an excelent technique to embedd the tokens using > overlapping strings. > > Can you send me an email address, I have some code that you might find > intersting to utilze in the pager.c for the checksums. Just post it to the list. Perhaps use the ticket tracker and attach your patch(es) to a ticket. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Pager modification question
Regarding the Journal: I was thinking that this would be useful in the context of a single process multiple threads and shared cache. All that would be required is an additional thread to handle the logging. Christian Smith <[EMAIL PROTECTED]> wrote: Ken uttered: > Would it be possible to implement a Page level locking system using > sqlite? Deadlock detection/recovery and/or prevention is much more complex for page level locking. As there is no shared data between SQLite clients other than the database file itself, tracking locks in other clients is impossible beyond the OS level locking. A single process with multiple threads could feasibly track page level locking, but it would be intra-process only, and would complicate the locking significantly for relatively little benefit. Note that a writer can already operate concurrently with readers until it fills it's cache, and only has to have exclusive use of the database when synching it's cache on spill or commit. > > I seem to recall a commercial product Ingress that utilized a page level > locking scheme that would escalte to a table level lock when over a > certain % of a tables pages were locked. If you want multiple concurrent writers, use PostgreSQL instead. It uses MVCC to avoid even row level locks. And it has an embeddded SQL pre-processor to boot. > > This obviously would be very beneficial for those of us who are > implementing threaded applications using sqlite. The shared cache > concept would need to be extended so that a global context for the > shared cache would be maintained, allowing each individual thread that > connects (sqlite3_open) to utilize the shared cache for that DB. > > The benefit would be to move the Locking from the DB level down to the > page level. Keep a list of the before image pages in memory so that > readers are not blocked by the writers (lock holders) of the pages. > > When the writer txn completes, Mark it txn as completed in the journal. > > I think it would be beneficial to visit the journal design as well for > this. Mainly don't open/close journals, since a journal is always part > of the DB pre-build the journal and allow multiple threads of writers > into the journal. This should also help with some of the areas inside > the pager/journal code where a random checksum is used to validate the > journal upon crash recovery. > > Just some ideas to improve concurrency that I had and wanted to jot > down. The current journal implementation is simple and fast. Using a single rollback journal, rather than a redo-undo journal minimizes the amount of data that has to be written, and the moves the needs for asynchronous commits that would be required for any other journal design. Consider the alternatives for a minute: - Current journal design: Pros: simple, fast for single writer big transactions. Cons: Single writer only. Writer block readers. D in ACID requires multiple file syncs. - Write Ahead Redo-Undo log: Pros: Potentially multiple writers, ACID with single file sync. Cons: Difficult to coordinate between multiple processes. Requires async process to write log entries to database file. - Write Ahead Redo log: Pros: Simpler than Redo-Undo log. ACID with single file sync. Cons: No better write concurrency than current journal. Still requires async process to write log entries to database file. > > DRH, just reviewd the tokenize.c and pager.c, I concurr that Yacc/bison > have the token passing incorrect. Your implementation of tokenize is > very interesting, what an excelent technique to embedd the tokens using > overlapping strings. > > Can you send me an email address, I have some code that you might find > intersting to utilze in the pager.c for the checksums. Just post it to the list. Perhaps use the ticket tracker and attach your patch(es) to a ticket. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to handle dual-field Primary Key - only one is Autoincrement
Besides the trigger issue with sqlite. I think you have a design issue with your tables. You are using a composite key. Why not have a master table of customer I'ds that you maintain, whith only the customer_id as the PK and autoincrement. The the table you refer to would then be a child of the customer, that would contain the year... That should eliminate the need for the trigger. Dan Cooperstock <[EMAIL PROTECTED]> wrote: I have a slightly unusual situation in my database design. Several of my tables have primary keys composed of two integer fields, such as customerid and year. The customerid should be an Autoincrement field, in the sense that when a new customer is added, it should get an auto-generated customerid from the database (different from all others). I can't declare the customerid as Primary Key Autoincrement, because it's not unique - it's only unique when I put it together with the year. (The reason for that is that I copy all customer records to new records at each year end, with the same customerid but a new year. Yes, there _is_ a good reason for that.) What I have come up with at this point is declaring the pair of fields to be the Primary Key, and adding a trigger such as: create trigger insert_customer before insert on customer when NEW.customerid is null begin update NEW set customerid = (select max(customerid) + 1 from customer); end This doesn't quite work, though, because I have declared customerid as Not Null, and that stops any insert that doesn't set the customerid, even though the trigger would fix it! I guess the Not Null constraint "fires" before the trigger does. Even with customerid allowing nulls, though, this trigger doesn't work. When I do an insert, I get an error message "No such table: main.NEW". Does that mean we can't update the NEW "table" in a before insert or update trigger? If not, how can I get this sort of effect? Is there any smart way to make this work, hopefully still using Autoincrement, or at least to make the trigger work? I'd also really rather not set customerif to allow nulls, since that would then apply to updates too. Thank you. --- Dan Cooperstock DONATION web site: http://www.FreeDonationSoftware.org DONATION Support forums: http://forums.FreeDonationSoftware.org E-mail: mailto:[EMAIL PROTECTED] Home Phone: 416-423-9064 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite Preprocessor
>From the standpoint of data insertions you wouldn't reall need to deal with >datatype correctness since sqlite is typeless. John Stanton <[EMAIL PROTECTED]> wrote: Why would it be a benefit? You would have to be doing type conversions all the time. Ken wrote: > I think the fact that sqlite is typeless actually is a benefit. > > I see what you mean now about connecting to obtain the meta info. Maybe that > could be embedded in the code as well for the pre-processor. > > Thanks for your input. > > Joe Wilson wrote: ...and I was trying to disagree with you. ;-) > > PRO*C and all such pre-processing SQL code generators actually > _do_ need to connect to a database in order to get the table meta-information > (column types, etc). It is this database meta-information that allows the > user of the pre-processor to write the succinct code you mentioned. > Either you have to supply the column type info, or the tool has to > obtain it somehow. And considering that SQLite is typeless, you have your > work cut out for you. > > But don't let my contempt of SQL code generators disuade you. > > --- Ken wrote: > >> yes thats what I'm thinking.. The thing is the amount of code that can be >> reduced via the >>preprocessor is enormous. Plus the fact that one could also preprocess into >>the resulting C code >>all sorts of wonderful error handling in a very succinct language construct. >> >> I hapen to perfer the conept of PRO*C, I don't like all of the weird errors >> that it generates >>and the various modes of operation. I'm advocating something that is a build >>it like I tell you >>to do, not how you interpret it. Leave out all of the goofy semantic checking >>etc.. Just let the >>normal C compiler handle that bit. There's no real need to connect to a >>sqlitedb just to verify >>the table exists. >> >> Ken >> >> >>Joe Wilson wrote: > Yes, a pre processor, but not a wrapper. A wrapper as >>I've seen from the sqlite.org site is >> >>>simply a layer on top of the sqlite3 api. I've written my own wrapper. I'm >>>really looking to >>>see if instead of inserting an additional layer, the actual code could be >>>compiled inline into >>>the sourcing C file, thus a pre processor. >> >>Okay, I see. All the bad memories are coming back now... >> >>I used the PRO*C pre-processor ten years ago. >>What a disaster that approach is. Never again. >>The code is a mess and debugging is a nightmare. >> >>You actually need a static database schema to run your code-generating >>pre-processor against. You change your database schema and your code >>generator has some obscure syntax error. If your application crashes >>you have to wade through generated code to see WTF is going on. >>In the time it takes to chase down problems you could have written >>a superior application in a traditional language binding/wrapper. >> >>Just my opinion of course. > > > > > > Any questions? Get answers on any topic at www.Answers.yahoo.com. Try it now. > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite Preprocessor
I think the fact that sqlite is typeless actually is a benefit. I see what you mean now about connecting to obtain the meta info. Maybe that could be embedded in the code as well for the pre-processor. Thanks for your input. Joe Wilson <[EMAIL PROTECTED]> wrote: ...and I was trying to disagree with you. ;-) PRO*C and all such pre-processing SQL code generators actually _do_ need to connect to a database in order to get the table meta-information (column types, etc). It is this database meta-information that allows the user of the pre-processor to write the succinct code you mentioned. Either you have to supply the column type info, or the tool has to obtain it somehow. And considering that SQLite is typeless, you have your work cut out for you. But don't let my contempt of SQL code generators disuade you. --- Ken wrote: > yes thats what I'm thinking.. The thing is the amount of code that can be > reduced via the > preprocessor is enormous. Plus the fact that one could also preprocess into > the resulting C code > all sorts of wonderful error handling in a very succinct language construct. > > I hapen to perfer the conept of PRO*C, I don't like all of the weird errors > that it generates > and the various modes of operation. I'm advocating something that is a build > it like I tell you > to do, not how you interpret it. Leave out all of the goofy semantic checking > etc.. Just let the > normal C compiler handle that bit. There's no real need to connect to a > sqlitedb just to verify > the table exists. > > Ken > > > Joe Wilson wrote: > Yes, a pre processor, but not a wrapper. A wrapper as > I've seen from the sqlite.org site is > > simply a layer on top of the sqlite3 api. I've written my own wrapper. I'm > > really looking to > > see if instead of inserting an additional layer, the actual code could be > > compiled inline into > > the sourcing C file, thus a pre processor. > > Okay, I see. All the bad memories are coming back now... > > I used the PRO*C pre-processor ten years ago. > What a disaster that approach is. Never again. > The code is a mess and debugging is a nightmare. > > You actually need a static database schema to run your code-generating > pre-processor against. You change your database schema and your code > generator has some obscure syntax error. If your application crashes > you have to wade through generated code to see WTF is going on. > In the time it takes to chase down problems you could have written > a superior application in a traditional language binding/wrapper. > > Just my opinion of course. Any questions? Get answers on any topic at www.Answers.yahoo.com. Try it now. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Pager modification question
Would it be possible to implement a Page level locking system using sqlite? I seem to recall a commercial product Ingress that utilized a page level locking scheme that would escalte to a table level lock when over a certain % of a tables pages were locked. This obviously would be very beneficial for those of us who are implementing threaded applications using sqlite. The shared cache concept would need to be extended so that a global context for the shared cache would be maintained, allowing each individual thread that connects (sqlite3_open) to utilize the shared cache for that DB. The benefit would be to move the Locking from the DB level down to the page level. Keep a list of the before image pages in memory so that readers are not blocked by the writers (lock holders) of the pages. When the writer txn completes, Mark it txn as completed in the journal. I think it would be beneficial to visit the journal design as well for this. Mainly don't open/close journals, since a journal is always part of the DB pre-build the journal and allow multiple threads of writers into the journal. This should also help with some of the areas inside the pager/journal code where a random checksum is used to validate the journal upon crash recovery. Just some ideas to improve concurrency that I had and wanted to jot down. DRH, just reviewd the tokenize.c and pager.c, I concurr that Yacc/bison have the token passing incorrect. Your implementation of tokenize is very interesting, what an excelent technique to embedd the tokens using overlapping strings. Can you send me an email address, I have some code that you might find intersting to utilze in the pager.c for the checksums. Ken
Re: [sqlite] Sqlite Preprocessor
Yes, but probably simpler and more in the tradition of sqlite. Make it simple and easy to use. John Stanton <[EMAIL PROTECTED]> wrote: Are you proposing an implementation of the existing Embedded SQL standard? Ken wrote: > Yes, a pre processor, but not a wrapper. A wrapper as I've seen from the > sqlite.org site is simply a layer on top of the sqlite3 api. I've written my > own wrapper. I'm really looking to see if instead of inserting an additional > layer, the actual code could be compiled inline into the sourcing C file, > thus a pre processor. > > I'll give this a quick first pass when I get some free time, how difficult > could it really be ? > > would anyone be interested in this as a project? > > > John Stanton wrote: My understanding is that he is advocating a compiler > which would take > his definition of an Sqlite operation and generate correct Sqlite3 API > calls. > > An existing wrapper could well satisfy his requirement. > > Joe Wilson wrote: > >>I not sure what you mean by preprocessor, but if you mean a >>"stored procedure language", sqlite does not support an official one >>within the database itself. >> >>There are, however, dozens of bindings to computer languages >>in addition to the Tcl wrapper that ships with sqlite: >> >>http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers >>http://www.sqlite.org/tclsqlite.html >> >>--- Ken wrote: >> >> >>>Does a preprocessor exist for sqlite and if so where ? >>> >>>If not that might be a really nice project to be able to support >>>syntax as follows: >>> >>>SQLITE_EXEC at :loginhndllogin "dbname.db"; >>>SQLITE_EXEC at :loginhndl declar cursor c1; >>>SQLITE_EXEC at :loginhndl prepare cursor c1 using sqlStr; >>>SQLITE_EXEC at :loginhndl bind x (not sure about this one) >>>SQLITE_EXEC at :loginhndl fetch c1 into :hostvars ; >>>SQLITE_EXEC at :loginhndl close cursor c1; >>>SQLITE_EXEC at :loginhndl close database >>> >>>. The list would go on and on, but you get the idea. >>> >>>Ken >> >> >> >> >> >> >>Do you Yahoo!? >>Everyone is raving about the all-new Yahoo! Mail beta. >>http://new.mail.yahoo.com >> >>- >>To unsubscribe, send email to [EMAIL PROTECTED] >>- >> > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite Preprocessor
Joe, yes thats what I'm thinking.. The thing is the amount of code that can be reduced via the preprocessor is enormous. Plus the fact that one could also preprocess into the resulting C code all sorts of wonderful error handling in a very succinct language construct. I hapen to perfer the conept of PRO*C, I don't like all of the weird errors that it generates and the various modes of operation. I'm advocating something that is a build it like I tell you to do, not how you interpret it. Leave out all of the goofy semantic checking etc.. Just let the normal C compiler handle that bit. There's no real need to connect to a sqlitedb just to verify the table exists. Ken Joe Wilson <[EMAIL PROTECTED]> wrote: > Yes, a pre processor, but not a wrapper. A wrapper as I've seen from the sqlite.org site is > simply a layer on top of the sqlite3 api. I've written my own wrapper. I'm > really looking to > see if instead of inserting an additional layer, the actual code could be > compiled inline into > the sourcing C file, thus a pre processor. Okay, I see. All the bad memories are coming back now... I used the PRO*C pre-processor ten years ago. What a disaster that approach is. Never again. The code is a mess and debugging is a nightmare. You actually need a static database schema to run your code-generating pre-processor against. You change your database schema and your code generator has some obscure syntax error. If your application crashes you have to wade through generated code to see WTF is going on. In the time it takes to chase down problems you could have written a superior application in a traditional language binding/wrapper. Just my opinion of course. Cheap talk? Check out Yahoo! Messenger's low PC-to-Phone call rates. http://voice.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite Preprocessor
Yes, a pre processor, but not a wrapper. A wrapper as I've seen from the sqlite.org site is simply a layer on top of the sqlite3 api. I've written my own wrapper. I'm really looking to see if instead of inserting an additional layer, the actual code could be compiled inline into the sourcing C file, thus a pre processor. I'll give this a quick first pass when I get some free time, how difficult could it really be ? would anyone be interested in this as a project? John Stanton <[EMAIL PROTECTED]> wrote: My understanding is that he is advocating a compiler which would take his definition of an Sqlite operation and generate correct Sqlite3 API calls. An existing wrapper could well satisfy his requirement. Joe Wilson wrote: > I not sure what you mean by preprocessor, but if you mean a > "stored procedure language", sqlite does not support an official one > within the database itself. > > There are, however, dozens of bindings to computer languages > in addition to the Tcl wrapper that ships with sqlite: > > http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers > http://www.sqlite.org/tclsqlite.html > > --- Ken wrote: > >>Does a preprocessor exist for sqlite and if so where ? >> >> If not that might be a really nice project to be able to support >>syntax as follows: >> >> SQLITE_EXEC at :loginhndllogin "dbname.db"; >> SQLITE_EXEC at :loginhndl declar cursor c1; >> SQLITE_EXEC at :loginhndl prepare cursor c1 using sqlStr; >> SQLITE_EXEC at :loginhndl bind x (not sure about this one) >> SQLITE_EXEC at :loginhndl fetch c1 into :hostvars ; >> SQLITE_EXEC at :loginhndl close cursor c1; >> SQLITE_EXEC at :loginhndl close database >> >> . The list would go on and on, but you get the idea. >> >> Ken > > > > > > > Do you Yahoo!? > Everyone is raving about the all-new Yahoo! Mail beta. > http://new.mail.yahoo.com > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Sqlite Preprocessor
Does a preprocessor exist for sqlite and if so where ? If not that might be a really nice project to be able to support syntax as follows: SQLITE_EXEC at :loginhndllogin "dbname.db"; SQLITE_EXEC at :loginhndl declar cursor c1; SQLITE_EXEC at :loginhndl prepare cursor c1 using sqlStr; SQLITE_EXEC at :loginhndl bind x (not sure about this one) SQLITE_EXEC at :loginhndl fetch c1 into :hostvars ; SQLITE_EXEC at :loginhndl close cursor c1; SQLITE_EXEC at :loginhndl close database . The list would go on and on, but you get the idea. Ken
[sqlite] Sqlite Preprocessor
Does a preprocessor exist for sqlite and if so where ? If not that might be a really nice project to be able to support syntax as follows: SQLITE_EXEC at :loginhndllogin "dbname.db"; SQLITE_EXEC at :loginhndl declar cursor c1; SQLITE_EXEC at :loginhndl prepare cursor c1 using sqlStr; SQLITE_EXEC at :loginhndl bind x (not sure about this one) SQLITE_EXEC at :loginhndl fetch c1 into :hostvars ; SQLITE_EXEC at :loginhndl close cursor c1; SQLITE_EXEC at :loginhndl close database . The list would go on and on, but you get the idea. Regards, Ken Dan Kennedy <[EMAIL PROTECTED]> wrote: I don't think so. The master journal file is created (and also deleted, unless a crash occurs) during the final commit processing (i.e. during sqlite3_step() of COMMIT). Up until that point there's no special processing for multi-database transactions. I'm wondering if attaching a database in the middle of a transaction was disallowed purely to save having to test it... Dan. On Wed, 2007-01-10 at 08:37 -0500, Tom Briggs wrote: >Would attaching a database mid-transaction, then making changes in > the newly-attached database, make it impossible to create the correct > master journal file for the overall transaction? Just a curious shot in > the dark. > >-Tom > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > Sent: Monday, January 08, 2007 10:43 AM > > To: sqlite-users@sqlite.org > > Subject: Re: [sqlite] attach in transaction > > > > [EMAIL PROTECTED] wrote: > > > > > > Can someone tell me why attach cannot be called within transaction? > > > > I do not recall. > > > > Clearly a DETACH will not work inside a transaction if the > > table being detached has been accessed or modified within that > > transaction. But ATTACH could work, I would think. > > > > Perhaps we disallowed ATTACH so that people would not expect > > an automatic DETACH if the transaction rolls back? > > > > The prohibition against running ATTACH within transaction > > first appeared in version 3.0.1. Presumably in version 3.0.0 > > you could ATTACH within a transaction. > > > > If you want to experiement, you can comment out the check > > for begin within a transaction in the attach.c source file, > > recompile, try doing various attaches within transactions, > > and see what bad things happen. This might give us a clue > > as to why it is restricted. Usually we do not prohibit > > things without good reason, I just do not recall what that > > reason is. Perhaps the original reason no longer applies > > > > -- > > D. Richard Hipp > > > > > > -- > > --- > > To unsubscribe, send email to [EMAIL PROTECTED] > > -- > > --- > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Shared cache mode issue
Here is a code snipet from my version if the server thread code
I found that it was doing an enable/disable on the shared cache with the
original logic.
You could always implement a sqlite3_open call and store it in the g variable,
and close it when the server quits.
void *sqlite3_server(void *NotUsed){
if( pthread_mutex_trylock() ){
sqlite3_enable_shared_cache(0);
return 0; /* Another server is already running */
}
// Only enable the shared cache 1 time
sqlite3_enable_shared_cache(1);
Peter James <[EMAIL PROTECTED]> wrote: Hey folks...
The context of this message is sqlite library version 3.3.6, using the
shared-cache mode, effectively following the test_server.c example.
Immediately upon switching to shared-cache mode we started seeing errors
like so when preparing statements:
[ERROR] (lib/sqlite/src/build.c:1220) no such collation sequence:
garbage>
Drilling down, this is what I'm understanding to be the case... Collators
are attached to the individual sqlite* handles, remaining valid only while
the connection to which the handle refers is valid. On the other hand, it
appears that indexes are stored inside of the schema, and use a lookup
string ("BINARY", "NOCASE") to find the contained column collators. This
lookup string is actually in memory allocated as part of the collator, and
is freed when the connection is closed, leaving a dangling pointer in the
index.
>From reading mozilla's docs on how they used the shared cache mode, I have
to guess this dangling pointer thing isn't normally a problem since the
"standard" thing to do is open a (dummy) connection at the beginning of the
server and maintain it until the server ends. In which case, the dummy
connection is the one containing default collator defs and that lookup
string's memory is always valid.
The error above surfaced in our initial implementation of the test_server.c
architecture, where we hadn't yet started using a dummy connection. We were
testing functionality and just opening and closing connections as needed
through the server thread, and noticed that if the first connection happened
to close after the second connection opened, we would end up with the above
error. Before realizing this was perhaps an artefact of not having a dummy
connection, we worked around it by doing a strcpy of the collator name into
malloc'd memory for the index, rather than just pointing at the collator..
Is this a correct interpretation of this situation? If there's a dependency
in shared cache mode where the client must maintain at least one persistent
connection, did I miss documentation on this fact?
I hope I explained myself properly. If not, let me know and I'll try
again. :-)
Thanks,
Pete.
Peter James <[EMAIL PROTECTED]> wrote: Hey folks...
The context of this message is sqlite library version 3.3.6, using the
shared-cache mode, effectively following the test_server.c example.
Immediately upon switching to shared-cache mode we started seeing errors
like so when preparing statements:
[ERROR] (lib/sqlite/src/build.c:1220) no such collation sequence:
garbage>
Drilling down, this is what I'm understanding to be the case... Collators
are attached to the individual sqlite* handles, remaining valid only while
the connection to which the handle refers is valid. On the other hand, it
appears that indexes are stored inside of the schema, and use a lookup
string ("BINARY", "NOCASE") to find the contained column collators. This
lookup string is actually in memory allocated as part of the collator, and
is freed when the connection is closed, leaving a dangling pointer in the
index.
>From reading mozilla's docs on how they used the shared cache mode, I have
to guess this dangling pointer thing isn't normally a problem since the
"standard" thing to do is open a (dummy) connection at the beginning of the
server and maintain it until the server ends. In which case, the dummy
connection is the one containing default collator defs and that lookup
string's memory is always valid.
The error above surfaced in our initial implementation of the test_server.c
architecture, where we hadn't yet started using a dummy connection. We were
testing functionality and just opening and closing connections as needed
through the server thread, and noticed that if the first connection happened
to close after the second connection opened, we would end up with the above
error. Before realizing this was perhaps an artefact of not having a dummy
connection, we worked around it by doing a strcpy of the collator name into
malloc'd memory for the index, rather than just pointing at the collator..
Is this a correct interpretation of this situation? If there's a dependency
in shared cache mode where the client must maintain at least one persistent
connection, did I miss documentation on this fact?
I hope I explained myself properly. If not, let me know and I'll try
again. :-)
Thanks,
Pete.
Re: [sqlite] Segfault on sqlite3_reset
If sqlite3_finalize is the destructor, then what happens when it returns sqlite3_busy ? Should finalize be called again? Thanks [EMAIL PROTECTED] wrote: Ken wrote: > sqlite3_step > sqlite3_finalize > sqlite3_reset > > Ok I think I know.. the reset should not have been called. Right. Finalize is the destructor for the sqlite3_stmt object. Bad things happen when you try to call a method on an object which has already been destroyed. > What should one do if you get a SQLITE_BUSY on finalizing If you use sqlite3_prepare_v2() then the result code is returned directly from sqlite3_step(). If you are using the legacy interface (sqlite3_prepare) or if you are ignoring the sqlite3_step() return code then call sqlite3_reset() to find the result. It is OK to call sqlite3_finalize() after sqlite3_reset(), if that is what you want to do. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Segfault on sqlite3_reset
sqlite3_step -> Jan 8 16:50:49 | CRITICAL | test3_proc | Error: Error fetching cursor=[sel_proc] db=[tst_sqlitethrd.db] rc=[5] msg=[database is locked] sqlite3_finalize -> Jan 8 16:50:49 | CRITICAL | test3_proc | Error: Error closing cursor=[sel_proc] db=[tst_sqlitethrd.db] rc=[5] msg=[database is locked] sqlite3_reset -> Jan 8 16:50:49 | CRITICAL | test3_proc | Error: Error resetting cursor=[sel_proc] db=[tst_sqlitethrd.db] rc=[21] msg=[database is locked] Ok I think I know.. the reset should not have been called. What should one do if you get a SQLITE_BUSY on finalizing [EMAIL PROTECTED] wrote: Ken wrote: > Found the issue: > > > Called sqlite3_finalize after recieving a SQLITE_BUSY on on sqlite3_step, > then you get a segfault and all sorts of errors, glibc related. > Unable to reproduce, even before the most recent corrections to sqlite3_prepare_v2(). Please provide additional information. http://www.sqlite.org/cvstrac/chngview?cn=3577 -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Segfault on sqlite3_reset
Found the issue: Called sqlite3_finalize after recieving a SQLITE_BUSY on on sqlite3_step, then you get a segfault and all sorts of errors, glibc related. But if I loop on SQLITE_BUSY, re-calling the sqlite3_step all is well. Can this be the correct thing to do ??? I mean I alreaded prepared the statement. Why can't it be reset of finalized if a sqlite busy is encountered ??? Ken Ken <[EMAIL PROTECTED]> wrote: I'm getting a segfault and sigabort (sporadic) when calling sqlite3_reset. Version 3.3.9 and using sqlite3_prepare_v2... I'll retest using sqlite3_prepare. Ken
[sqlite] Segfault on sqlite3_reset
I'm getting a segfault and sigabort (sporadic) when calling sqlite3_reset. Version 3.3.9 and using sqlite3_prepare_v2... I'll retest using sqlite3_prepare. Ken
Re: [sqlite] When to release version 3.3.10?
I concurr with Dennis. Dennis Cote <[EMAIL PROTECTED]> wrote: [EMAIL PROTECTED] wrote: > The question is: should I rush out 3.3.10 to cover this important > bug fix, wait a week to see if any other bugs surface, or do the > usual 1-2 month release schedule and let people effected by this > bug apply the patch above. What is the opinion of the user community? > > Richard, I think you should immediately post a notice that a new version 3.3.10 will be released on Jan 12 on the home and download pages, and then wait for the week to see if other bugs surface quickly. The notice will let users decide if they want to delay their updates or not. They can use 3.3.9, or they can stay with 3.3.8 for another week. If they need the latest fix they can use the CVS version. The knowledge that the new version will be released on a certain date will comfort most users enough to simply wait (which might also delay the discovery of more bugs). I think it is the open ended waiting for a new release at an unknown future date that sometimes frustrates users. If you don't want to wait for other bugs, then the release early and often approach is probably better than collecting fixes for a release every two or three months. I'm not sure how much work you go through with each release, but suspect that a release after each crash or corruption bug is fixed, or new feature addition, would not be too onerous. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite performance, locking & threading
Emerson, I agree with you somewhat. Not 100% convinced but, I like you am a little dissapointed how sqlite handles "threadsafe" and multiple connections. Even in the "test_server.c" module is not "concurrent" As it serializes all processing to a single thread, this is not concurrent processing. Now if i could take that server thread and create a pool of workers and hand off the working set request to one of those, then this would be concurrent since each thread would have the capability to run independently of the other and at the same time on an SMP hardware system such as Solaris,Hpux etc... But this is specifically disallowed since the connection when using the shared cache must be used by the creating thread. So far I've not been able to successfully find any threadsafe examples of sqlite that actually work in a concurrent manner. Even the threadtest2.c crased (bug fix coming in 3.3.10) but even it does not start up a transaction and run multiple inserts/step commands. I would like to see a working example of how sqlite can be concurrently accessed via multiple threads performingWrite (insert/update/delete) and by other threads performing selects against a single table. IMHO, the sqlite3_step function if it can only be executed serially (ie must be wrapped in a mutex) should do this mutexing internally and not be exposed and left in the hands of the user. If one compiles the code with --enable-threadsafe, shouldn't the API be able to handle areas such as these internally and not generate "misuse" errors. Emerson Clarke <[EMAIL PROTECTED]> wrote: Roger, Of course you can test threading behaviour, yes its not exactly repeatable but under most circumstances and with enough test cases you can catch the problems. I don't think sqlite is such a large and complicated piece of software that it would be impossible to reproduce such errors. Everyone keeps saying its well documented and referring to the fact that the reasoning has already been done, but i havnt heard anything other than the usual "thats the way its always been" responses, or the "its designed that way beacuse its easier" argument. That does not count as documentation or reasoning. If anything the source code is the documentation, but thats not quite the same as an essay on the thread safety of the sqlite vm is it ? Anyway, i can see im flogging a dead horse here. This is as futile as arguing religion with a priest. :P Theres not much point forking any maintaining the code if on one else sees any validity in my arguments. Thanks to everyone anyway, Emerson On 1/5/07, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Emerson Clarke wrote: > > I have to admit i am a little dissapointed. As the primary author of > > the software i would have thought that you would have a good > > understanding of what the thread safety characteristics of your own > > api were. > > He does! It is well documented and tested. *You* are the one who wants > to do various things differently. > > > Suggesting that suppressing the safety checks will result in random > > and non reproducable failures seems a little extreme, if not > > superstitious. > > Err, you are using threading. Explain exactly how if you got a failure > due to threading you could reproduce it exactly. You do know that you > can't run x86 processors in lockstep because at the cycle level they > have effectively random behaviour due to cache eviction and replacement > policies. That one reason for the Itanium if you need two processors to > have exactly the same behaviour on the same code at the cycle level. > > > As i was discussing with Nicholas, api's are not by default thread > > unsafe. In fact it is the other way around, most api's should be > > perfectly thread safe if access to them is synchronised. > > This all comes down to semantics. For trivial libraries, sure you can > just wrap a mutex around it all. For non-trivial libraries, best > practise is for the author to design and implement for whatever thread > usage they believe is best suited for the library and the platforms it > operates one. > > > Im sorry for being so harsh, and i know im not winning any friends > > here, > > So far noone has agreed with you :-) > > > I have tested the changes i made with some 50 threads performing > > probably thousands of statements a second, and had no errors. > > What does that prove? You cannot prove threading by running stuff and > saying "see it didn't crash". The only way to prove threading is by > reasoning about the design and verifying the implementation matches the > design. drh already did that years ago. > > > If you are willing to work through the problem, i am willing to assist > > you in any way that i can. Wether it be writing test cases or making > > modifications to the source. > > You can always maintain your own forked version of SQLite and link to it > from the wiki. > > Roger > -BEGIN PGP
Re: [sqlite] SegFault on threadtest2.c using version 3.3.9
What other tests? [EMAIL PROTECTED] wrote: Ken wrote: > threadtest2 generated a segmentation fault as well as an illegal operations > when running against version 3.3.9 (in 32bit mode). > > I did compile using --enable-threadsafe. > > Does this mean this version is not threadsafe? threadtest2.c is unmaintained. Its functionality has been subsumed by other tests. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] pragma read_uncommitted and threading
I found part of the issue, in switching servers around the test version did not enable the shared cache... My fault.. however I still recieved a RC 5 on a different write operation. Modifed the test_server code to repost the step and all was well. A better solution however is to cause the server to filter, based upon the thread id's this saves a small amount of time due to not recalling the sqlite3 functions just to get another server is locked message. Ken <[EMAIL PROTECTED]> wrote: I ran everything through the test_server "server" and get the following results when using read_uncommitted=1 rc=[5] msg=[database is locked] when performing a "client_step" against a prepared select statement. I'd really like to track this down and find out if my code is incorrect or if I've hit a bug. Thanks, Ken [EMAIL PROTECTED] wrote: Ken wrote: > > My thought was that if i wanted to perform selects concurrently on SMP > system I would need 2 threads and each thread would be able to read > concurrently > > I just don't see how this [test_server.c] improves concurrency... > It improves write concurrancy. One client can read from table A at the same time another client is writing to table B. Or if read_uncommitted is turned on, one client can read from table A at the same time that another client is writing to th e same table. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] ----- [EMAIL PROTECTED] wrote: Ken wrote: > > My thought was that if i wanted to perform selects concurrently on SMP > system I would need 2 threads and each thread would be able to read > concurrently > > I just don't see how this [test_server.c] improves concurrency... > It improves write concurrancy. One client can read from table A at the same time another client is writing to table B. Or if read_uncommitted is turned on, one client can read from table A at the same time that another client is writing to th e same table. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Joining tables in a single file
I think the attach is the way to go, but no need to insert, just select from the attached databases. sqlite3 master.db (master is empty). attach a.db A attach b.db B attach c.db C Then : select from a.A, b.b, c.c where Alberto Simões <[EMAIL PROTECTED]> wrote: Hi I am using SQLite to store ngrams from texts (bigrams, trigrams and tetragrams). This is not really important for the question; just imagine I have three tables A (int,int), B (int, int, int) and C (int, int, int, int). As the table keys are full rows and the tables get big, it is not quite efficient com compute bigrams, trigrams and tetragrams at the same time. Given that I have access to a cluster, I split the job in three tasks that can be computed separately on different cluster nodes. One calculates bigrams, another trigrams, and other to calculate tetragrams. So far, everything fine. The problem is that this results in three different files each with a table. I need to join tables in the same file. There are no dependencies between tables, thus you can imagine that I need something like: cat A.db B.db C.db > full.db # kidding I can do an export and import for each table. But I would like to know if there is any faster method. Thank you Kind regards, Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] pragma read_uncommitted and threading
I ran everything through the test_server "server" and get the following results when using read_uncommitted=1 rc=[5] msg=[database is locked] when performing a "client_step" against a prepared select statement. I'd really like to track this down and find out if my code is incorrect or if I've hit a bug. Thanks, Ken [EMAIL PROTECTED] wrote: Ken wrote: > > My thought was that if i wanted to perform selects concurrently on SMP > system I would need 2 threads and each thread would be able to read > concurrently > > I just don't see how this [test_server.c] improves concurrency... > It improves write concurrancy. One client can read from table A at the same time another client is writing to table B. Or if read_uncommitted is turned on, one client can read from table A at the same time that another client is writing to th e same table. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] ----- [EMAIL PROTECTED] wrote: Ken wrote: > > My thought was that if i wanted to perform selects concurrently on SMP > system I would need 2 threads and each thread would be able to read > concurrently > > I just don't see how this [test_server.c] improves concurrency... > It improves write concurrancy. One client can read from table A at the same time another client is writing to table B. Or if read_uncommitted is turned on, one client can read from table A at the same time that another client is writing to th e same table. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Creating a database from inside a program
I have a nice solution that really works well, at least from C... 1. Create a template Database. (using whatever method you like, either embedded in your code or via sqlite3). 2. Copy the database to a new file, using plane old cp, copy, or if you like an in code copy using open, read/write, close... Then open the newly created copy. I've found that if you need mulitples of a single database structure this is a very fast way to do this, a lot faster than creating the DB and then creating the individual tables/indices. If you want really, really fast, you could load your templated db into memory and then just write this to disk whenever you need a new DB. Kees Nuyt <[EMAIL PROTECTED]> wrote: On Fri, 29 Dec 2006 12:33:46 -0500, you wrote: > Sqlite3 newdatabase.db .read schemafile.txt > > But, when I issue this command from the DOS prompt, > it gives me an error message saying that there > is no command named "read". Try input redirection: Sqlite3 newdatabase.db If your schemafile.txt contains valid SQLite statements, it should work. HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite performance, locking & thread
Emereson, And you can't do this with oracle either, That is create a connection and pass it around between multiple threads.. Oracle would be very very unhappy if you did that. Oracle utilizes a context variable to distinguish between threads and utilizes precompiler flags to enable thread support. Emerson Clarke <[EMAIL PROTECTED]> wrote: Ken, Yes you cannot have multiple threads within the same transaction, and you cannot pass a connection between threads. I think we have an undestanding about the performance situation, and we are getting to the real heart of the issue, which why it is not possible to have a single transaction, single connection and multiple threads. Provided the user synchronises the access to the connection, this should offer the highest performance. But there is some technical reason why sqlite has this (in my opinion unusual behaviour). Perhaps DRH can explain this... Emerson On 12/29/06, Ken wrote: > The test server.c code is an example that has a single thread that performs > the DB interactions each client thread, communicates via a queue interface. > Each client will get serialized into the DBserver thread and get its work > done. Thus eliminating any lower level locking and mutexing inside the > application code. > > Your assumption regarding 1 thread/1 cache is pretty accurate. This is what > the test_server.c code does, however each client thread does however get a > database connection handle. > > If you have a single global transaction you can do insr/selec/upd/delete.. > The thing is that the connection may not be passed around amongst threads. > > You cannot have multiple threads executing within the same transaction!!! To > my understanding, that is not allowed. Please someone else correct me if I'm > wrong... > > The difference is with other DB's they utilize a finer grain of locking > internally, either page locking, row locking etc.. Sqlite uses Database > LOCKING, which is full file. So its really only designed to be used by a > single thread of execution. (again DRH please correct me here if I'm wrong). > > > Emerson Clarke wrote: Ken, > > Thanks. > > Ok, i think i did miss the point with your test case. I assumed that > it was always possible to perform multiple select, insert, delete, and > update statements within a single transaction anyway. Which i guess > relates to you last point. I dont see why if i have a single global > transaction i cant perform all the operations i want within that > transaction to maximise performance. In fact i know i can. because > thats what i do with a single thread. > > I have always assumed that the simplest and highest performance > solution would be to have multiple threads with one transaction and > one cache. I will take a look at the test_server.c code and see if i > can understand what you are suggesting. > > I dont see why writer starvation would be a problem, but again i > probably just dont understand what your suggesting there. Anyway, > thanks for your help. I suspect that you are right and the ultimate > solution will be to write my own synchronisation code, since that will > allow me to have a consistent api across multiple databases like > sqlite and mysql. > > I could achieve this if i could have multiple threads executing > statements within the same transaction since that is the effective > behaviour i get from most other databases which are not in process. > In those cases it is the process boundary and the tcp/ip connections > which are effectivley serialising all interactions which is equivalent > of having only one thread when using sqlite. > > Emerson
[sqlite] pragma read_uncommitted and threading
Hi all, I have a piece of code that utilizes test_server.c, (master thread) there are 3 threads, each performing seperate tasks, that get a conection (shared) and set PRAGMA read_uncommitted=1. My understanding is that this would allow each individual thread to concurrently execute a select statement? I get the following errors: Thread Load: ( begin txn, insert, commit) using the master thread since it is a write. ) rc=[10] msg=[disk I/O error] (after thread proc disconnects) Thread proc: (select not using the master_thread queue, updates using thread queu ) rc=[11] msg=[malformed database schema - table sqlite_master already exists] Thread purg:(select not using the master_thread queue, delete using the master thread queue). rc=[10] msg=[disk I/O error] (on delete statement after thread proc disconnects). I was under the impression that using the PRAGM read uncommitted would not cause blocking amongst readers/writers? So I built an interface that would send write operations to the Writer Queue and would call sqlite3_ commands for selects... Is this not a correct utilization? Also the code dumps core, but sometimes will run to completion as above. Note that If i use and api that sen
Re: [sqlite] sqlite performance, locking & threadi
The test server.c code is an example that has a single thread that performs the DB interactions each client thread, communicates via a queue interface. Each client will get serialized into the DBserver thread and get its work done. Thus eliminating any lower level locking and mutexing inside the application code. Your assumption regarding 1 thread/1 cache is pretty accurate. This is what the test_server.c code does, however each client thread does however get a database connection handle. If you have a single global transaction you can do insr/selec/upd/delete.. The thing is that the connection may not be passed around amongst threads. You cannot have multiple threads executing within the same transaction!!! To my understanding, that is not allowed. Please someone else correct me if I'm wrong... The difference is with other DB's they utilize a finer grain of locking internally, either page locking, row locking etc.. Sqlite uses Database LOCKING, which is full file. So its really only designed to be used by a single thread of execution. (again DRH please correct me here if I'm wrong). Emerson Clarke <[EMAIL PROTECTED]> wrote: Ken, Thanks. Ok, i think i did miss the point with your test case. I assumed that it was always possible to perform multiple select, insert, delete, and update statements within a single transaction anyway. Which i guess relates to you last point. I dont see why if i have a single global transaction i cant perform all the operations i want within that transaction to maximise performance. In fact i know i can. because thats what i do with a single thread. I have always assumed that the simplest and highest performance solution would be to have multiple threads with one transaction and one cache. I will take a look at the test_server.c code and see if i can understand what you are suggesting. I dont see why writer starvation would be a problem, but again i probably just dont understand what your suggesting there. Anyway, thanks for your help. I suspect that you are right and the ultimate solution will be to write my own synchronisation code, since that will allow me to have a consistent api across multiple databases like sqlite and mysql. I could achieve this if i could have multiple threads executing statements within the same transaction since that is the effective behaviour i get from most other databases which are not in process. In those cases it is the process boundary and the tcp/ip connections which are effectivley serialising all interactions which is equivalent of having only one thread when using sqlite. Emerson On 12/29/06, Ken wrote: > > Emerson, > > You just need to lock the entire transaction using a mutex before hand. That > means each thread will have its own sqlite cache. Reread the sqlite locking > and concurrency guide, you'll see that SQLITE is NOT a transaction based > system such as Postgress/mysql/ oracle. It locks the entire database file at > the point of writing, not at the first insert/update/delete. Its better to > think of sqlite as an easy way to perform data access against a FLAT file. > Rather than a transaction oriented system. > > Regarding my test case. You missed the point, there is a select statement in > the middle that each thread executes. The nice thing is that each and every > thread uses a shared cache and can execute the select statement concurrently > without a mutex. The locking when using a shared cache works at the table > level instead of file! > > Another thing you should look at: Create a master DB, then attach the > individual databases to the master. That way you only need one Cache. How do > you know you wouldn't benefit from merging your caches? Have you tried? Do > you have test cases to prove that it doesn't help? > > You stated that you want to put your owner locking mutex wrapper around the > database interactions so that there would be only 1 thread access the DB.. > That means that you need to release the lock after each > statement(insert/update/delete) and not perform a BEGIN transaction/commit... > Which means very slow performance. And besides only one thread regardless > could be accessing the DB. Whereas the test_server.c architecture (you need > to modify this for your own needs) will let you create multiple shared > connections to the database. And each thread can perform selects > concurrently. But all threads are serialized into the single thread for > writing. This takes care of all locking issues and is technically the same as > mutexing the writes across threads. And it addresses writer starvation, which > you have not addressed with your simple mutex locking. > > It seems to me you might want write your own locking implementation or even > abandoning sqlite if it doesn't fit your needs. > > > Emerson Clarke wrote: Ken
Re: [sqlite] sqlite performance, locking & threadin
Emerson, You just need to lock the entire transaction using a mutex before hand. That means each thread will have its own sqlite cache. Reread the sqlite locking and concurrency guide, you'll see that SQLITE is NOT a transaction based system such as Postgress/mysql/ oracle. It locks the entire database file at the point of writing, not at the first insert/update/delete. Its better to think of sqlite as an easy way to perform data access against a FLAT file. Rather than a transaction oriented system. Regarding my test case. You missed the point, there is a select statement in the middle that each thread executes. The nice thing is that each and every thread uses a shared cache and can execute the select statement concurrently without a mutex. The locking when using a shared cache works at the table level instead of file! Another thing you should look at: Create a master DB, then attach the individual databases to the master. That way you only need one Cache. How do you know you wouldn't benefit from merging your caches? Have you tried? Do you have test cases to prove that it doesn't help? You stated that you want to put your owner locking mutex wrapper around the database interactions so that there would be only 1 thread access the DB.. That means that you need to release the lock after each statement(insert/update/delete) and not perform a BEGIN transaction/commit... Which means very slow performance. And besides only one thread regardless could be accessing the DB. Whereas the test_server.c architecture (you need to modify this for your own needs) will let you create multiple shared connections to the database. And each thread can perform selects concurrently. But all threads are serialized into the single thread for writing. This takes care of all locking issues and is technically the same as mutexing the writes across threads. And it addresses writer starvation, which you have not addressed with your simple mutex locking. It seems to me you might want write your own locking implementation or even abandoning sqlite if it doesn't fit your needs. Emerson Clarke <[EMAIL PROTECTED]> wrote: Ken, Thanks i understand your example well. What im looking for is the ability to have multiple threads perform a write operation based on my mutex, not some internal sqlite write mutex. If i am managing the concurrency and performing correct synchronisation, why can i not have multiple threads writing to the database at once and achive a better overall performance. Given that the indexing process i am referring too has several steps which it must perform in successive order, and not all of them are database writes, i am simply trying to use threads to emulate a situation where there is only one thread doing all the database writes, if that makes sense. So in this case, what ever synchronisation sqlite is doing internally is actually getting in the way of what i am trying to do. There were no gains in performance in your test case becuase there was no reason to have multiple threads. The only thing each thread was doing was writing to the database, so of course the only thing you will introduce by using multiple threads is overhead. Emerson On 12/28/06, Ken wrote: > Emerson, > > Is the Database on the same disk as the rest of the file operations? If so is > it possible that you are I/O bound and causing seek issues due to i/o access > patterns? > > Take a look at the test_server.c code in the sqlite/src directory. I used > that as a basis to build a custom library that opens a single DB and then > allows multiple threads to access. The nice thing about this architecture is > that the threads will all get to write and no writer starvation. But all > write operations an single threaded. > > The test code I ran creates any number of threads and performs the following > in each thread: > > outer loop 1- 10 > begin txn > loop 1 -1000 > insert record (using modulo for data so data is unique amongst > threads) > end loop > commit > > prepare statement > loop 1 - 1000 > Select data (using modulo) > end loop > close statement > > begin transaction > loop 1 - 1000 > delete data, using same modulo > end loop > end main loop > > timinng (seconds) Thread count > 1.6651 (transaction > size is 1000) > 1.6352 (transcaction > size is 500) > 3.094 4( txn size is > 250 ) > 5.571 8(txn size is > 125 ) > 7.82216(txn size is 62.5) > > so as the number of
Re: [sqlite] sqlite performance, locking & threading
Emerson, Is the Database on the same disk as the rest of the file operations? If so is it possible that you are I/O bound and causing seek issues due to i/o access patterns? Take a look at the test_server.c code in the sqlite/src directory. I used that as a basis to build a custom library that opens a single DB and then allows multiple threads to access. The nice thing about this architecture is that the threads will all get to write and no writer starvation. But all write operations an single threaded. The test code I ran creates any number of threads and performs the following in each thread: outer loop 1- 10 begin txn loop 1 -1000 insert record (using modulo for data so data is unique amongst threads) end loop commit prepare statement loop 1 - 1000 Select data (using modulo) end loop close statement begin transaction loop 1 - 1000 delete data, using same modulo end loop end main loop timinng (seconds) Thread count 1.6651 (transaction size is 1000) 1.6352 (transcaction size is 500) 3.094 4( txn size is 250 ) 5.571 8(txn size is 125 ) 7.82216(txn size is 62.5) so as the number of threads increase the overall time it takes to insert/select/delete a fixed set of data increases using this architecture. This is because all threads are serialized upon inserts/deletes and are contending on a single writer mutex. So in this particular case fewer threads actually improves performance. Hope this helps, Ken Emerson Clarke <[EMAIL PROTECTED]> wrote: Roger, Thanks for the suggestions. I think using a worker thread and a queue would be equivalent to just running a single thread since it effectively makes the database operations synchronous. Although i can see what your driving at regarding the transactions every n records. The idea is that because i am accessing two databases, and doing several file system operations per document, there should be a large gain by using many threads. There is no actual indexing process, the whole structure is the index, but if anything the database operations take the most time. The filesystem operations have a very small amount of overhead. I have tried the page size pragma setting already, though i read that it is dependent on the cluster size of the particular filesystem that you are running on. Since i only have one connection to each database from each thread i dont think i would benefit from the caching. Im not quite sure why you would ever have more than one connection to the database from a single thread ? The api that i use more or less ensures that under most circumstances there is only one connection. Emerson - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] delayed (batch) transactions
Regarding Oracle: It also batches commits... This means that if two or more users submit commits concurrently (or near the same interupt ) then those will be serviced at the same time. But oracle differs significantly from sqlite, in its architecture as it would not be appropriate for an embedded DB.. Christian Smith <[EMAIL PROTECTED]> wrote: [EMAIL PROTECTED] uttered: > > It takes at least two complete rotations of the disk platter > to do an atomic and durable commit. On a 7200 RPM disk, that > means 60 transactions per second is your speed of light. > > Your question boils down to this: Can you speed up transactions > by dropping the durable property - the D in ACID. Yes you > can. Actually, most client/server database engines already > do this for you without telling you. Very few client/server > databases are really ACID - they are usually on ACI when > confronted with a power failure. Are you sure about this? I'm pretty sure PostgreSQL is ACID by default, and as certain as I can be without proof that the commercial (Oracle etc.) are as ACID as the hardware they run on. They achieve this by not requiring seeks to overwrite data, as the write ahead log is sequential and so can be written at the sequential IO rate of the underlying device in the best case. The client/server databases are also not limited by the filesystem synchronous metadata updates, and so are more likely to achieve the peak IO rate of the underlying device. > > [snip] > > A second approach would be to overload the OS drivers on > the backend of SQLite to support asynchronous I/O. Mozilla > does this in Firefox in order to boost performance on NFS. > There is well-commented sample code showing how to do this > in the SQLite source file "test_async.c". That sample code > does not combine multiple transactions, but you could probably > tweak it to make that happen. When doing some performance testing between different FS on Linux, I found that Ext3 with "data=journal" option was about 50% faster than the next fastest options (XFS and ext3 with "data=writeback"). Again, this is because synchronous meta-data, as well as normal synchronous file data, can be written sequentially in the FS journal at peak IO speed. If I can find the figures, I'll post them. However, I mislaid the test results and haven't got a suitable machine to reproduce at the moment. > -- > D. Richard Hipp > Christian PS. If people are after maximum performance, I can recommend ext3 with "data=journal" option for SQLite. The performance is stunning! PPS. Merry Christmas to those so inclined. -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: server and threading
This is getting ugly. Consider the following given the server.c example: and using counters to track when a txn is begun/committed. a.. Begin exclusive b.. Begin exclusive (succeeds??? ) a. Insert b. Insert (should be ok since we are in a txn.. however this is on a seperate connection and only one can write at a time... Get a sqlite busy .. b. rollback (because of sqlite busy) This undoes the changes for a... insert.. and terminates the transactions running... a. commit... get a no transaction. So all this does is point glaringly that although Sqlite seems to be "threadsafe" it is far from transcational within a threaded environment.. Any ideas on how to "patch this up"?? The server running statements on behalf of clients in a multi threaded enviornment is appealing since it allows a shared cache for each connection and given "read uncommitted" allows parallel read operations while a write is occuring... I think there needs to be some form of GLOBAL write lock. That would cause the calling client thread to block, when another client thread has the lock. These locks should obviously be enqued such that writer starvation will not occur. It occurs to me that the Sample server.c module is broken. The recomendation is to use transactoins (the doc page on multi threading) however when starting a transaction in this mannter it is not really honored. This lends itself to the idea that the server.c code should allow a "callback function", wherin the callback function could execute begin/ statemtnstetc... then finally a commit. This would allow functions to enque work, perform their work autonomously and independently. This is really no different than enabling a global reader/writer lock, at the server level opon entry to "begin" however it does have the advantage of guaranteeing no writer starvation and FirstCome First Serve into the sqlite server thread. It would be nicer if sqlite implemented this internally, considering concurrency levels for attached databaes would be improved over a full write lock at a global level. Any other paradigms or thoughts about this? Thanks Igor Tandetnik <[EMAIL PROTECTED]> wrote: Ken wrote: > I've been looking at the server.c file. And have started some > testing. I'd like to know if the server.c (single process, single > server thread handling incoming requests) can handle SQL statements > such as Begin TXn and Commit? > > From what I've gathered doing a begin exclusive doesnt really seem > to work. I get success on 4 concurrent threads. Then later get a > commit failure indicating "no transaction active".. Be aware that SQLite doesn't support nested transactions. Suppose you get a sequence like BEGIN BEGIN COMMIT COMMIT First BEGIN starts a transaction. Second is simply ignored. First COMMIT ends the transaction. Second commit produces the error you quote. The usual solution is to handle BEGIN and COMMIT statements by maintaining a counter. Increment it when you see a BEGIN, decrement on COMMIT. Only actually execute BEGIN statement when counter moves from 0 to 1, and COMMIT when counter moves from 1 to 0. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: server and threading
Igor thanks for the respons... I have a few more questions... Here is the sequence: (a) open -- Server does a shared cache enable, then open (b) open-- Server does a shared cache anble, then open (a) begin exclusive (b) begin exclusive (From my undestanding this should return back a sqlite_busy However it does not, it returns success ). (a) insert (b) insert (a) commit( success) (b) commit(Gets a cannot commit - no transaction is active ) Is this correct behaviour? Why? I understand what youve stated about nested txn's however these are on seperate connections through a shared cache onto a single database. It sure would be nice to see better documentation in this area, regarding locking. I understand the file locking model but there is little documentation egarding the thread/sql locking and interaction amongst single process connections. I can easilty modify the code to handle the above situation. Simple as you stated. Keeping a counter as follows: Cmdcounter Action begin1 sql Begin begin2 No -op, cntr > 1 commit 1 sql Commit & sql begin. commit 0 sql commit Igor Tandetnik <[EMAIL PROTECTED]> wrote: Ken wrote: > I've been looking at the server.c file. And have started some > testing. I'd like to know if the server.c (single process, single > server thread handling incoming requests) can handle SQL statements > such as Begin TXn and Commit? > > From what I've gathered doing a begin exclusive doesnt really seem > to work. I get success on 4 concurrent threads. Then later get a > commit failure indicating "no transaction active".. Be aware that SQLite doesn't support nested transactions. Suppose you get a sequence like BEGIN BEGIN COMMIT COMMIT First BEGIN starts a transaction. Second is simply ignored. First COMMIT ends the transaction. Second commit produces the error you quote. The usual solution is to handle BEGIN and COMMIT statements by maintaining a counter. Increment it when you see a BEGIN, decrement on COMMIT. Only actually execute BEGIN statement when counter moves from 0 to 1, and COMMIT when counter moves from 1 to 0. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] server and threading
Hi all, I've been looking at the server.c file. And have started some testing. I'd like to know if the server.c (single process, single server thread handling incoming requests) can handle SQL statements such as Begin TXn and Commit? From what I've gathered doing a begin exclusive doesnt really seem to work. I get success on 4 concurrent threads. Then later get a commit failure indicating "no transaction active".. Thanks for your help. Ken
[sqlite] SQLite 3.3.1
Hmmm. The number of warnings compiling the 3.3.1 source is up to 10 from 6 in the 3.3.0 source. All of them appear to be related to inappropriate sign comparisons/assignments. -ken
Re: [sqlite] Problem executing SQLite 3.3.0 under MacOS
Thank you, kind sir. Sometimes it is difficult to see the individual ferns on the floor of the massive forest! Oddly enough, the problem seems to have been caused by the lack of the os.c file being compiled into the project at all? Some files that were in the 3.2.8 code stream are no longer present and this new os.c file was added to the 3.3.0 code tree. I had copied to the files from the 3.3.0 code stream into the project subdirectory, but I had missed the fact that there was a new file, and the compilation did not report the problem! I added this file and adjusted the optimization level, and the problem disappeared -- the code is working as before! I have adjusted the code so that the debug mode has optimization turned off, but that it is still enabled in release mode. Thanks once again. -ken On 13-Jan-06, at 8:40 PM, [EMAIL PROTECTED] wrote: Try recompiling with all optimization turned off. Optimizers tend to reorder code which makes single-stepping confusing - both for you and your debugger.
Re: [sqlite] Problem executing SQLite 3.3.0 under MacOS
For the life of me I cannot figure out where this is going wrong. I am not a below average intelligence individual, and I have been working with computers for over 30 years, but this one has me totally at a loss. I should point out that while I own MacOS based systems exclusively for home use, I have never formally developed much on the systems. I have used REALbasic in the past, along with HyperCard and the like, but it is only recently that I have commenced attempting to learn Objective-C and Cocoa programming. I do have years of experience with Digital (PDP, VAX, Alpha), various UNIX and Windows based systems, however, and have used all of the major programming languages at one time or another. Most of my recent professional work is based on C#/.Net under Windows, where we use SQL Server and MSDE (SQL Server Express now) almost exclusively. I wanted to develop some applications to support my hobby and volunteer work -- officiating for Little League Baseball. Along with umpiring games, I also manage the District web site, and have written some code to track and report the results of season games, playoffs and tournaments. The original code was written in REALbasic, but I cannot afford to keep buying the new releases for a hobby that already costs me a significant amount of money each year. To that end I wanted to use the free XCode tools, which meant learning Objective- C and Cocoa for reasonable GUI applications. I was disappointed to find there was not readily available database interface, so I began writing one for myself in Objective-C. I had some problems with the original attempt to develop a SQLite framework, so I decided to simply create an experimental application and embed the SQLite code, along with my framework classes, and the trial code in the same application. I organized the project so that the main project source files were in the project base directory, and created a subdirectory each for the framework code and the SQLite source code. This worked fairly well, and I was able to get enough code written that I could create a new database, create a table, but some data in the table, and execute a query that returned the results into an in-memory result set (vaguely similar to what ADO.Net can do, but FAR simpler). Since none of my immediate applications will result in huge result sets being generated, I deemed this sufficient. I developed this trial application with the SQLite 3.2.8 source code, and it went fairly well. I was even able to step through my framework classes and even down into the SQLite source to track down how some operations actually worked. Life was not bad! Then I decided to replace the SQLite source with the 3.3.0 contents and see if there were any improvements. As I noted, I simply replaced the 51 source files in the subdirectory of my project with the same files from the 3.3.0 source tree, and recompiled. The first thing I noticed was that there were now only 6 warnings about sign differences on variable sin assignments or procedure calls, and that the code seemed to compile just a bit faster. When I attempt to execute the code, however, it signals a bad exception on a call to sqlite3OsLock() on line 1969 of pager.c as I am attempting to execute the SQL statement to create my first table after the database is created. 1. I cannot locate in the 3.3.0 source code how the reference to sqlite3OsLock is converted into the virtual table method. 2. There are no longer an os_win.h or os_unix.h file in the source tree, which is fine. 3. For some reason I can no longer step into my framework code! 4. Naturally, I cannot step into the SQLite code either, since I cannot step into my framework. 5. I can set a breakpoint in my Objective-C code and step from there, but sometimes this steps over instead of into! 6. It almost always fails to step into the SQLite C calls. 7. If I set a breakpoint on a SQLite statement, I hit it, but single stepping is erratic at best, often stepping over and seeming to jump randomly around in the code rather than sequentially following the source code. I am stuck! How do I resolve this problem and get the 3.3.0 code working once more? -ken On 12-Jan-06, at 11:52 PM, [EMAIL PROTECTED] wrote: The whole OS-backend was reworked for version 3.3.0. So do not expect to find the same functions in 3.3.0 that you had in 3.2.8. unixLock() is actually a virtual method on the OsFile object. Since SQLite is written in C not C++, we have to explicitly code the virtual method table. You can find it defined as the IoMethod structure in os.h. When you open a file, (using, for example, the sqlite3UnixOpenReadWrite() routine) you get back an object called OsFile which has as its first field a pointer to the IoMethod structure. sqlite3OsLock() is really a macro that resolves to OsFile->pMethod.xLock which should point to the unixL
Re: [sqlite] Problem executing SQLite 3.3.0 under MacOS
Yes, the unixLock() routine is defined in the 3.3.0 source, but not in the 3.2.8 code. Nor can I find the #define for this anywhere in the 3.3.0 source (nor can I find any definition for the sqlite3OsLock (other than a function prototype) in either the 3.2.8 or 3.3.0 source. When I attempt to debug the 3.3.0 source and step into the sqlite3OsLock call, it simply drops me into some assembler and declares the bad instruction signal. -ken On 12-Jan-06, at 8:46 PM, [EMAIL PROTECTED] wrote: Ken & Deb Allen <[EMAIL PROTECTED]> wrote: I had a look, but I do not see where the sqlite3OsLock code is implemented. I see the function prototype, but not the actual implementation (not even a typedef or wrapper for a native call). The real name of the function is unixLock. There is a #define that aliases the name based on architecture. You will find the source code in os_unix.c. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Problem executing SQLite 3.3.0 under MacOS
I have encountered a problem while attempting to execute some code with SQLite embedded. This code was working with the previous version of the source. I am developing a small framework to permit me to use the SQLite engine from within some Cocoa applications I am developing for personal use (managing team scores on a web site). I have created my own class structure for this and it was working, but when I upgraded to the 3.3.0 code and decided to test it before I added new features, I got an EXC_BAD_INSTRUCTION signal from the debugger when I attempt to step into the call to "sqlite3OsLock" procedure as noted in the call stack below. The statement I am executing at step 19 is [theDatabase executeNonQuery:@"CREATE TABLE Table1(PKey INTEGER NOT NULL, Name VARCHAR(32) NOT NULL, BirthDate FLOAT NOT NULL, Relation VARCHAR NULL, PRIMARY KEY(PKey))"]; This statement has worked fine up until now! Does anyone have any idea why this may be the case? #0 0x001a8ba4 in ?? #1 0x943e7e90 in sqlite3OsLock #2 0x00216de0 in pager_wait_on_lock at pager.c:1969 #3 0x00217dcc in sqlite3pager_get at pager.c:2579 #4 0x0005e560 in getPage at btree.c:1450 #5 0x0005f430 in lockBtree at btree.c:1880 #6 0x0005fbf8 in sqlite3BtreeBeginTrans at btree.c:2087 #7 0x0005f7a8 in lockBtreeWithRetry at btree.c:1953 #8 0x00060f78 in sqlite3BtreeCursor at btree.c:2698 #9 0x0024556c in sqlite3InitOne at prepare.c:218 #10 0x00245a7c in sqlite3Init at prepare.c:353 #11 0x00245c34 in sqlite3ReadSchema at prepare.c:393 #12 0x00083e14 in sqlite3StartTable at build.c:790 #13 0x00228c6c in yy_reduce at parse.y:124 #14 0x0022bb14 in sqlite3Parser at parse.c:3221 #15 0x0027a5a8 in sqlite3RunParser at tokenize.c:391 #16 0x002462a8 in sqlite3_prepare at prepare.c:541 #17 0x000ea8dc in sqlite3_exec at legacy.c:56 #18 0x002f3fe0 in -[SQLiteDatabase executeNonQuery:] at SQLiteDatabase.m:224 #19 0x00032df0 in -[DirectoryManager createNewDatabase:] at DirectoryManager.m:62
