[sqlite] Problem building amalgamation
SQLite 3.13.0 I have just grabbed the sqlite3.c and sqlite3.h from another (working) project and dropped then into a new project and I am seeing compile errors. New project is a C++ project in Eclipse. using GCC 5.4.0 from cygwin64. Just trying to make a simple command line app. I'm sure I'm missing something simple but nothing is clicking... g++ -std=c++11 -fpermissive -P -dD -o HelloCPP.exe HelloCPP.cpp sqlite3.c Errors... sqlite3.c:25153:48: error: cannot convert 'SrcList::SrcList_item*' to 'SrcList_item*' in initialization struct SrcList_item *pItem = &pSrc->a[k]; ^ sqlite3.c:25156:18: error: invalid use of incomplete type 'struct SrcList_item' if( pItem->zDatabase ){ ^ sqlite3.c:25157:49: error: invalid use of incomplete type 'struct SrcList_item' sqlite3StrAccumAppendAll(pAccum, pItem->zDatabase); ^ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Crash on Android
Hi All, Any thoughts on this will be greatly appreciated. I am having an issue only on a specific tablet when it is running Android 4.2.2. When Android is upgraded to 4.4.2 problems appears to be gone. I still want to understand root cause to know if problem is really gone. My environment is a little unusual. I am running SQLite amalgamation wrapped by JDBC driver. I was running 3.13.0 when issue was initially found but have updated to 3.17.0 and no change. Problem always occurs while doing same operation but unable to predict when it will occur. Problem occurs while reading in a specific table and a call to sqlite3_column_text16() results in the following error being logged: A/libc(5225): Fatal signal 11 (SIGSEGV) at 0x (code=128), thread 5225 (saltillo.chatpc) and of course kills the process. Also, I find that attaching a debugger or writing too many log messages seems to make the problems disappear also. Thanks, Jeff ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to get these ordered correctly
Hi and thanks in advance for your help. I have a table of words and counts and I am trying to make a query which sorts in descending order of count but with each group of words that have the same count sorted alphabetically. MY table: CREATE TABLE words (id INTEGER PRIMARY KEY AUTOINCREMENT ,cnt INTEGER DEFAULT 1 ,rvw INTEGER DEFAULT 0 ,txt TEXT UNIQUE); sqlite> select txt, cnt, rvw from words where txt!="" and rvw!=0 order by cnt desc; talk |2|1 be |2|1 words|2|1 ready|2|1 to |1|1 need |1|1 The result I am trying to get is: be |2|1 ready|2|1 talk |2|1 words|2|1 need |1|1 to |1|1 T hanks, Jeff ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Order of fields for insert
Sorry, that was just mistake in reducing the code for the email, please ignore. What do you mean "what matters is order of columns in table"? or was that just referring to the typo? Jeff Archer jeffarch...@gmail.com On Thu, Oct 6, 2016 at 9:52 AM, Clemens Ladisch wrote: > Jeff Archer wrote: > > Are there any performance or other considerations of the order of the > > fields for an insert? > > No; what matters is the order of columns in the table. > > > INSERT INTO mytable(wid1,cnt,dat,wid3,wid2) VALUES (?,?,?,?) > > - VS - > > INSERT INTO mytable(wid1,wid2,wid3,cnt,dat) VALUES (?,?,?,?) > > Both statements will result in exactly the same error. ;-) > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Order of fields for insert
Just a quick question. I am actually deciding if I need to do some performance testing of this but thought I might gain some quick insight. My specific insert and table are below but really I am looking for a general answer to the question not just this specific case. Are there any performance or other considerations of the order of the fields for an insert? Are the following equivalent? regardless of number of values inserting? regardless of size of the data being inserted? INSERT INTO mytable( wid1,cnt, dat, wid3,wid2) VALUES (?,?,?,?) - VS - INSERT INTO mytable( wid1,wid2,wid3,cnt ,dat ) VALUES (?,?,?,?) CREATE TABLE mytable ( id INTEGER PRIMARY KEY AUTOINCREMENT" , wid1 INTEGER REFERENCES othertable ( id ) ON DELETE CASCADE , wid2 INTEGER REFERENCES othertable ( id ) ON DELETE CASCADE , wid3 INTEGER REFERENCES othertable ( id ) ON DELETE CASCADE , cnt INTEGER DEFAULT 1 ,dat TEXT ) Jeff ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with savepoints
On Wed, Jul 6, 2016 at 10:46 AM, Dan Kennedy wrote: > On 07/06/2016 09:09 PM, Jeff Archer wrote: > >> Hi All, >> I am a long time SQLite user but have generally used it from C++ in the >> past. In this project however, I am attempting to make a JDBC wrapper and >> use from Android a newer version of SQLite (3.12.2) than that provided by >> Android 4.2.2 (3.7.11). >> > > Group 8 however can only do 26 successful inserts and on the 27th >> sqlite3_step() is returning SQLITE_CANTOPEN (14). >> > > > > SQLite is failing to create a temporary file, as there is nowhere > convenient to do this on Android. The usual fix is to build SQLite to store > temp files in memory: > > -DSQLITE_TEMP_STORE=3 > > https://www.sqlite.org/compile.html#temp_store > > Or execute > > "PRAGMA temp_store = memory" after opening your db connection. > > Dan, many thanks. This does indeed solve my problem. Just for my information, what is the purpose of this temporary file? I see that -journal file is always stored to disk. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem with savepoints
Hi All, I am a long time SQLite user but have generally used it from C++ in the past. In this project however, I am attempting to make a JDBC wrapper and use from Android a newer version of SQLite (3.12.2) than that provided by Android 4.2.2 (3.7.11). Basically, what I have is SQLite compiled with the NDK into a static library and then a Java wrapper to provide the JDBC interfaces. My application is then attempting to use the JDBC interfaces to operate on the database. This is generally working. I can create/open databases, add/retrieve data, etc. including making use of databases that are originally created from the native android SQlite (3.7.11) I have run into a strange issue when attempting to use savepoints. I create first a transaction with "begin", then create the outer savepoint "SAVEPOINT SQLITE_SAVEPOINT_1". This savepoint will be released after 8 groups have been inserted. Next, for each of the 8 groups of data a group save point is created "SAVEPOINT SQLITE_SAVEPOINT_2", data is inserted with sqlite3_step() returning SQLITE_DONE, then savepoint is released "RELEASE SAVEPOINT SQLITE_SAVEPOINT_2" Repeat this successfully for groups 2 through 7. Group 8 however can only do 26 successful inserts and on the 27th sqlite3_step() is returning SQLITE_CANTOPEN (14). This description is of having 128 inserts per group. The exact number of inserts seems to be dependent upon the length of the text being inserted but as long as data does not change the failure point is predictable. CREATE TABLE variables (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, value TEXT NOT NULL) INSERT INTO variables VALUES (null, '', '') sqlite version 3.12.2 Thanks, Jeff ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Trying to use in-memory database
Long time SQLite user but I don't think I have ever tried to do an in-memory database before. Just upgraded to 3.8.3.1 but I am not having any other failures with existing code so I don't think that is any part of the problem. I am trying to open the database with sqlite3_open16() using a filename of L":MEMORY:". I am getting a result code of 14 which is SQLITE_CANTOPEN. It does open if I use a real filename. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Path Length Limit on Windows
>Date: Wed, 28 Aug 2013 14:21:24 -0400 >From: Richard Hipp > >The latest check-in on trunk adds a new windows VFS module called >"win32-longpath" which accepts the full-length 32KB pathnames. Add the >string "win32-longpath" as the 4th argument to sqlite3_open_v2() (on >windows only, of course) and everything should just work after that. Would it be possible to have a sqlite3_open16_v2() also? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Support SQLite in México
>Francisco Puente Moreno fpm.mty74 at hotmail.com >Mon Apr 29 11:46:05 EDT 2013 > >Hello, I'm working for a Mexican company and we are thinking use SQLite for some develops, but >we need to know if there is any company in México that give direct support to SQLite if we have a >contingency. >Thanks in advance! >Regards! >Francisco Puente, Eng. I would recommend that you follow this list for a few weeks while building a sample app using SQLite and using this list for support. You will find the support to be top-notch. I have paid for support from companies and gotten far less. I have been using SQLite3 in my product since 2009. Highly recommended. Jeff Archer Vice President of Software Engineering Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to achieve fastest possible write performance for a strange and limited case
Thanks for everyone's help and thoughts on this issue. My findings on Windows 7 Pro 64 using a PCI based SSD is that for my smallish image the BLOBs were faster than individual files. Basically, in line with a table that someone posted earlier in this thread. After many experiments, with many variations on grouping writes into transactions, the single writer proves to be the most limiting factor. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to achieve fastest possible write performance for a strange and limited case
On Fri, Mar 29, 2013 at 4:46 PM, Richard Hipp wrote: > On Fri, Mar 29, 2013 at 4:41 PM, Michael Black wrote: > >> I think many people would tell you not to store your images in your >> database. >> Just store a filepath to them. >> That will speed things up quite a bit and even possibly prevent having to >> use an SSD. >> >> With the filepath your processing apps can use file locking too if you need >> it. >> > > Lots of people think this. But experiments suggest otherwise, depending on > the size of your images. > > For BLOBs smaller than about 100KB, it is actually faster to read them out > of an SQLite database than it is out of a file. Presumably the extra > overhead of the open/close is what makes the difference. For BLOBs larger > than 100KB it is faster to store them in a file. That's for reading - I've > never run the experiment for writing. Note also that the 100KB is highly > operating-system and file-system and storage-media dependent and so your > mileage may vary. > FWIW. My tests are definitely indicating that BLOBs in database are significantly faster. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance with journal_mode = off
>On Fri, Mar 29, 2013 at 4:05 PM, Simon Slavin wrote: > >No. The two things have nothing to do with one-another. Transactions are >about grouping changes together >so that either they all happen or none happen. Journalling is about surviving >through crashes and >power-failures, and being able to undo changes. Transactions are about >programming. Journalling is about >hardware failure. Either one can be useful without the other. Thank you. This helps my understanding. This would be good information to have on the documentation page for PRAGMA journal_mode; Or, maybe this is common knowledge and I just illustrate my ignorance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance with journal_mode = off
>Date: Thu, 28 Mar 2013 14:13:51 +0100 >From: ibrahim > >... You should compare > >a) Journal mode=on w/wo single transaction >b) Journal mode=off w/wo single transaction So, this means I can use transaction when I have journal_mode = off? I did not understand that this was a possibility? I have re-read the PRAGMA journal_mode documentation and still it sounds to me like this cannot be done: "The OFF journaling mode disables the atomic commit and rollback capabilities of SQLite." Maybe I am not reading throughly... Maybe all it truely says definitively is "Applications must avoid using the ROLLBACK command when the journal mode is OFF." I will rerun my experiment with a single transaction with journal_mode = off. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to achieve fastest possible write performance for a strange and limited case
I have previously made an apparently bad assumption about this so now I would like to go back to the beginning of the problem and ask the most basic question first without any preconceived ideas. This use case is from an image processing application. I have a large amount of intermediate data (way exceeds physical memory on my 24GB machine). So, I need to store it temporarily on disk until getting to next phase of processing. I am planning to use a large SSD dedicated to holding this temporary data. I do not need any recoverability in case of hardware, power or other failure. Each item to be stored is 9 DWORDs, 4 doubles and 2 variable sized BLOBS which are images. I could write directly to a file myself. But I would need to provide some minimal indexing, some amount of housekeeping to manage variable sized BLOBS and some minimal synchronization so that multiple instances of the same application could operate simultaneously on a single set of data. So, then I though that SQLite could manage these things nicely for me so that I don't have to write and debug indexing and housekeeping code that already exists in SQLite. So, question is: What is the way to get the fastest possible performance from SQLite when I am willing to give up all recoverability guarantees? Or, is it simple that I should just write directly to file myself? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance with journal_mode = off
>From: "James K. Lowden" >To: sqlite-users@sqlite.org > > Your experiment is telling you different: transaction control costs more than I/O. But shouldn't transactions be disabled when journal_mode = off? Maybe that is a faulty assumption. If so, what is the point of journal_mode = off? For this purpose, I am very happy to give all the ACID promises. If I understand your point #2, I think you are saying that all of the inserts within a single transaction are not written to the disk (database or journal) until the transaction is committed. But that can't quite be the answer because if kept my transaction open long enough I would simple run out of memory and that doesn't seem to happen even when I have 1 million plus inserts. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance with journal_mode = off
On Thu, Mar 28, 2013 at 8:24 AM, ibrahim wrote: > On 28.03.2013 13:09, Jeff Archer wrote: >> >> On Wed, Mar 27, 2013 at 6:35 PM, Simon Slavin >> wrote: >>> >>> Reasonable figures. With 5764 writes to the disk in separates >>> transactions you have quite a lot of reading of data plus 5764 attempts to >>> update the database file. The updates have to be done in the right order, >>> and each update has to wait for the disk to be rotated into the right >>> position, though each update changes only a small amount of data (probably >>> two sectors). >> >> But my most basic question remains. Why is single transaction faster >> than PRAGMA journal_mode = off? >> >> Seems to me that with no journal there should only be single set of >> writes to the actual db and that journaling would double the number of >> writes because data has to be written to journal file as well. >> >> 2.5 sec with journal >> 5.5 sec without journal <= seems like this sould be the smaller number >> > You should read the sections 3 forward. > > http://www.sqlite.org/atomiccommit.html > > A single transaction happens mostly in memory then is flushed to the OS > Cache in one step. The Journal file (the amount of pages that will be > changed) is small while inserting new data into a database and the OS File > Cache is usually large enough ... > Yes, I have read this. (And now re-read it) So, since much more work must be done when using journal file, why does it take longer to do the inserts when there is NO journal file? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance with journal_mode = off
On Thu, Mar 28, 2013 at 8:35 AM, Niall O'Reilly wrote: > > On 28 Mar 2013, at 12:09, Jeff Archer wrote: > >> But my most basic question remains. Why is single transaction faster >> than PRAGMA journal_mode = off? >> >> Seems to me that with no journal there should only be single set of >> writes to the actual db and that journaling would double the number of >> writes because data has to be written to journal file as well. >> >> 2.5 sec with journal >> 5.5 sec without journal <= seems like this sould be the smaller number > > Your base-line for comparison is the case of multiple transactions > with journalling. > > When you turn off journalling, you save something; when you > consolidate the activity into a single transaction, you save > something else. What you're seeing is that the saving achieved > with reference to your base-line measurement by using a single > transaction exceeds that achieved by disabling journalling. > > /Niall > Agreed. But the question is: Why? Without journaling only half as much writing to disk should occur so why would it take longer? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance with journal_mode = off
On Wed, Mar 27, 2013 at 6:35 PM, Simon Slavin wrote: > > Reasonable figures. With 5764 writes to the disk in separates transactions > you have quite a lot of reading of data plus 5764 attempts to update the > database file. The updates have to be done in the right order, and each > update has to wait for the disk to be rotated into the right position, though > each update changes only a small amount of data (probably two sectors). But my most basic question remains. Why is single transaction faster than PRAGMA journal_mode = off? Seems to me that with no journal there should only be single set of writes to the actual db and that journaling would double the number of writes because data has to be written to journal file as well. 2.5 sec with journal 5.5 sec without journal <= seems like this sould be the smaller number ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance with journal_mode = off
On Wed, Mar 27, 2013 at 5:46 PM, David King wrote: > > > I am populating a database with 5764 records using the exact same data set > > each time into a newly created file. > > When I use no explicit transactions (default atomic commit) it takes 17.7 > > seconds. > > When I set journal_mode = off, same operation takes 5.5 seconds. > > If I do all 5764 inserts within a single transaction only 2.5 seconds. > > > That sounds about right, yeah. With journalling, most disk writes have to be > done twice (once to the journal and once to the data file). Which is why I expected journal_mode = off to make it faster. But it is 3 seconds faster when I leave journaling enabled and do all writes within a single transaction. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Performance with journal_mode = off
Could someone please confirm if this makes sense. It is not what I expected. I have repeated several times so I believe these are the correct numbers. I am populating a database with 5764 records using the exact same data set each time into a newly created file. When I use no explicit transactions (default atomic commit) it takes 17.7 seconds. When I set journal_mode = off, same operation takes 5.5 seconds. If I do all 5764 inserts within a single transaction only 2.5 seconds. Jeff Archer Vice President of Software Engineering Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question about behavior when journal_mode = off
If all connections (1 per thread) of all processes (multiple simultaneous) issue command "PRAGMA journal_mode = off", is access to a common database file located on the local machine still synchronized correctly between the random accesses (reads and writes) occurring across all? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Journal mode
Documentation doesn't make this clear. Does PRAGMA journal_mode = OFF; affect a single connection or will it affect all connections in my process? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite4 questions
Will SQLite4 be capable of reading a SQLite3 database to facilitate upgrade? (I'm guessing 'no') I assume SQLite4 will be distributed in a familiar amalgamation format. Will it be feasible to include the amalgamation of both SQLite3 and SQLite4 into the same application to create an upgrade facility? Is there any ETA on a complete and stable release of SQLite4? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ranking in a view
There was recently a post where someone was trying to update the ranking of records in a table and someone else presented a clever view which did the ranking and thus required no table update. I wanted to try and understand how this view worked but when I have looked back to find it, I can't. Could someone please repost that view or help me find it in some way? Thanks in advance, Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] The database disk image is malformed
I have encountered an error of the database disk image is malformed. Database was send in by a customer so I'm not exactly sure how it came to be this. I have found that this can be repaired by SQLiteExpertPro by the Database | Repair functionality but have not been able yet to find anything in the C API that might be used to implement this functionality. Could someone please point me to some information on how I might make my product be able to recover from this situation. SQLite3 version 3.7.13 Windows 7 x64 Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to get the amalgation as individual files
On Fri, Sep 21, 2012 at 4:01 PM, Nico Williams wrote: > >Install Cygwin, use that to make the amalgamation (after you've made > >whatever changes to the canonical sources), then build the amalgation > >in native Windows. Please excuse my ignorance. OK. I have Cygwin. Fossil. I have cloned the repository and opened. I believe I now have the source tree of SQLite. Not sure what I need to do next. I have the Cygwin command prompt open and believe it is running the bash shell. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to get the amalgation as individual files
Igor Tandetnik Fri Sep 21 15:26:16 EDT 2012 > ..."Canonical Source Code" section I knew about this but unfortunately I am Windows only. So the "Note that a unix-like development environment, including a recent version of Tcl<http://www.tcl.tk/>, is required in order to build from the canonical sources." trips me up. I was hoping there is a way to get the individual files after they have been formulated by the unix-like development tools but before they are combined into the amalgamation file. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is it possible to get the amalgation as individual files
I assume that the amalgation exists as single files before it is combined into one file. Is is possible to get these individual files so that I can build a debug version because Visual Studio debugger does not handle well the 138244 line amalgation file? Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_CONFIG_LOG callback indicating SQLITE_SCHEMA (17) error
I am still chasing this error message that I get through SQLITE_CONFIG_LOG callback during the sqlite3_step(): errcode: SQLITE_SCHEMA (17) message: statement aborts at 80: [INSERT INTO [Scans](ScanID, Timestamp, EndTime, Result) VALUES(NULL, @Timestamp, @Timestamp, @Result);] database schema has changed I have been told this is merely informational and that SQLite has re-prepared the statement however I am still trying to understand the cause out of fear that something unexplained will bite me later. I have a theory that the following statement executing in another process may be triggering SQLite to think the schema has changed. CREATE TABLE IF NOT EXISTS [_dbProperties] (PropIDINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,PropName VARCHAR NOT NULL UNIQUE ,PropValue VARCHAR NOT NULL ); I didn't consider this initially because the _dbProperties table has to exist as it was created by the first process and I was assuming that the "IF NOT EXISTS" would be preventing it from actually doing anything but now I am believing that executing this statement even when the table exists is causing SQLite to think the schema has changed. Does this seem plausible? Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Error indicated through SQLITE_CONFIG_LOG callback but sqlite3_step() does not fail
>Simon Slavin slavins at bigfraud.org Fri Jun 29 17:16:36 EDT 2012 > >Do you do the _prepare() first, then make a change to the database schema ? For instance > >Start of app >Prepare the INSERT statement >CREATE TABLE >Bind the INSERT statement >Step the INSERT statement No. Create Table Then in a separate function Prepare Bind Step Finalize Callback happens during call to sqlite3_step() ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Error indicated through SQLITE_CONFIG_LOG callback but sqlite3_step() does not fail
>Pavel Ivanov paivanof at gmail.com Fri Jun 29 17:06:42 EDT 2012 > >Because SQLite successfully re-prepared this statement behind the >scenes and thus was able to successfully finish sqlite3_step() >function. What could cause it to "re-prepare" the statement? Is this something I need to find and fix? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Error indicated through SQLITE_CONFIG_LOG callback but sqlite3_step() does not fail
>Kevin Benson kevin.m.benson at gmail.com Fri Jun 29 16:32:55 EDT 2012 > >The mention of SQLITE_SCHEMA error and sqlite3VdbeExec() sounds like maybe >you're fighting an expired statement??? Not likely. I do Prepare, Bind, Step, Finalize using a wrapper function. The database file was created only moments before. My biggest question is: Why after this error being indicate through the SQLITE_CONFIG_LOG callback would the sqlite3_step() succeeded? Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Error indicated through SQLITE_CONFIG_LOG callback but sqlite3_step() does not fail
I have been puzzling on this for a couple of days now and am at a loss. I hate to let something like this go because it appears so much like an error, I feel like it will just bite me at a later time. Any suggestions would be greatly appreciated. In attempting to execute the following SQL: "INSERT INTO [Scans](ScanID, Timestamp, EndTime, Result) VALUES(NULL, @Timestamp, @Timestamp, @Result);" I get the following message through the SQLITE_CONFIG_LOG callback during the sqlite3_step(): errcode: SQLITE_SCHEMA (17) message: statement aborts at 80: [INSERT INTO [Scans](ScanID, Timestamp, EndTime, Result) VALUES(NULL, Timestamp, @Timestamp, @Result);] database schema has changed sqlite3_step() returns SQLITE_DONE (101) and the row being inserted does get inserted into the database. I have investigated this with a very small test app and have not seen the log message in this context. I have now instrumented my real applications with output of all SQL to a single log file but am not seeing anything in here that would suggest the schema is being changed. (I'm not sure how to make this available since it can't be attached to this email) Unfortunately, I am on Windows and using the amalgamation (3.7.13) in VisualStudio so I am handicapped in terms of debugging down to the actual line within sqlite. I only see one instance of this log message at line 72025 within sqlite3VdbeExec(). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Not sure how to interrupt this
>Igor Tandetnik itandetnik at mvps.org Thu Jun 28 09:38:27 EDT 2012 >My guess is that a) you have prepared your statement with sqlite3_prepare_v2 (as opposed to sqlite3_prepare)... Statement was prepared with sqlite3_prepare16_v2() Database file was created only moments earlier. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Not sure how to interrupt this
>Marc L. Allen mlallen at outsitenetworks.com Thu Jun 28 09:31:40 EDT 2012 >I think he wants to know why he is receiving what appears to be an error notification via the callback. >Adam DeVita adevita at verifeye.com Thu Jun 28 09:04:44 EDT 2012 >What is the problem? Yes, the error message through the SQLITE_CONFIG_LOG callback is inconsistent with the success indicated by SQLITE_DONE. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Not sure how to interrupt this
>Igor Tandetnik itandetnik at mvps.org Thu Jun 28 09:57:06 EDT 2012 >Did you prepare the INSERT statement in between running CREATE TABLE and CREATE INDEX, >by any chance? Or perhaps CREATE TABLE for a different table? No, the CREATE INDEXs were done right after the CREATE TABLE. I have a wrapper function, Execute(), that does the Prepare, Bind, Step, Reset, Finalize sequence. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Not sure how to interrupt this
>Igor Tandetnik itandetnik at mvps.org Thu Jun 28 09:38:27 EDT 2012 >My guess is that a) you have prepared your statement with sqlite3_prepare_v2 (as opposed to sqlite3_prepare)... Statement was prepared with sqlite3_prepare16_v2() Database file was created only moments earlier. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Not sure how to interrupt this
I am getting back SQLITE_DONE (101) from sqlite3_step() and the statement is clearly being executed. SQL: "INSERT INTO [Scans](ScanID, Timestamp, EndTime, Result) VALUES(NULL, @Timestamp, @Timestamp, @Result);" The table has been created as: CREATE TABLE [Scans] (ScanID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,Timestamp DATETIME NOT NULL UNIQUE ,EndTime DATETIME NOT NULL DEFAULT CURRENT_TIME ,Result VARCHAR ); CREATE INDEX Scans_vwScan_Index on Scans(ScanID, Timestamp, EndTime, Result); CREATE INDEX Scans_Timestamp_Index on Scans(Timestamp); I have SQLITE_CONFIG_LOG callback installed: sqlite3_config(SQLITE_CONFIG_LOG, cb_sqlite_config_log, /*pUserData*/NULL); I get the following message through the SQLITE_CONFIG_LOG callback during the sqlite3_step(): errcode: SQLITE_SCHEMA (17) message: statement aborts at 80: [INSERT INTO [Scans](ScanID, Timestamp, EndTime, Result) VALUES(NULL, @Timestamp, @Timestamp, @Result);] database schema has changed SQLITE_VERSION"3.7.13" - amalgamation Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Something strange
I have seen something that seems strange and wonder if anyone has any thoughts? I have been using SQLite for a long time now and never seen anything similar. Had been using 3.7.6.1 and have now upgraded to 3.7.13 but behavior has not changed. In a Debug build this code works as expected but in Release build I am having only 2 of the 7 rows be inserted. I have found a workaround in that if I Sleep() for 100ms after each INSERT the Release build then works as expected. Unfortunately a lot of code involved so it will take a while to pare down to small test case and since I have a workaround I will hope for a general answer first. Inside a BEGIN IMMEDIATE, I do a SELECT from table Objectives and get back 7 rows which are held in a list. Iterate that list and for each do a SAVEPOINT then INSERT a row into table Calibration then do RELEASE. When finished iterating the list do COMMIT. Windows 7 64bit. Visual Studio 2010. Real HDD not SSD. The file is on local machine but is opened using a network path. i.e. \\MACHINENAME\Data\Calibration.db Thank you in advance for your time on this matter. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiler Error
>I believe any compilation options that require changes in SQL parser >require compiling from original sources as well. They cannot be used >with amalgamation file which has already generated SQL parser's code. > >Pavel OK. But this call in the parser source... sqlite3FinishTrigger (pParse, yymsp[-1].minor.yy203, &all); Should be enclosed in... #ifdef SQLITE_OMIT_TRIGGER sqlite3FinishTrigger(pParse, yymsp[-1].minor.yy203, &all); #endif Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Compile Error
I am attempting to compile SQLite on windows with SQLITE_OMIT_TRIGGER defined and I get the following errors. Sure enought the code in yy_...() is not exclosed in ifndef SQLITE_OMIT_TRIGGER 1> Creating library ..\..\Bin\x64\Debug\SqlUtils.lib and object ..\..\Bin\x64\Debug\SqlUtils.exp 1>sqlite3.obj : error LNK2019: unresolved external symbol sqlite3DeleteTriggerStep referenced in function yy_destructor 1>sqlite3.obj : error LNK2019: unresolved external symbol sqlite3DropTrigger referenced in function yy_reduce 1>sqlite3.obj : error LNK2019: unresolved external symbol sqlite3TriggerSelectStep referenced in function yy_reduce 1>sqlite3.obj : error LNK2019: unresolved external symbol sqlite3TriggerDeleteStep referenced in function yy_reduce 1>sqlite3.obj : error LNK2019: unresolved external symbol sqlite3TriggerInsertStep referenced in function yy_reduce 1>sqlite3.obj : error LNK2019: unresolved external symbol sqlite3TriggerUpdateStep referenced in function yy_reduce 1>sqlite3.obj : error LNK2019: unresolved external symbol sqlite3BeginTrigger referenced in function yy_reduce 1>sqlite3.obj : error LNK2019: unresolved external symbol sqlite3FinishTrigger referenced in function yy_reduce 1>..\..\Bin\x64\Debug\SqlUtils.dll : fatal error LNK1120: 8 unresolved externals Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite3 memory leaks in my c++ dll application
>From: Khanh Nguyen >Date: Wed, 20 Apr 2011 11:00:57 -0500 > >SQLite...memory leaks... The VS debugger will stop at the specific allocation number if you add this line early on in your program execution. _crtBreakAlloc = ; Where is the allocation number reported in the Detected Memory Leaks! I use SQLite from VS 2010 C++ 64 bit very heavily in my product. I am not aware of any memory leaks which can be attibuted to SQLite. Also, you should be aware that under the right circumstances it is possible to trick the debug memory allocator into reporting false positives in the leaked memory. Your said that your are using GetProcAddress() to make calls which makes me think you *may* be getting to these false positives. Basically, I believe it comes down to allocating memory in one dll and deallocating in another. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What happens if you insert more than your RAM
>Tue Apr 19 18:35:27 GMT 2011 Danny dragonslayer2k at yahoo.com > >Depends on access type. If accessing sequentially, paging would be minimal, >that is, you would process the "segment" that fits into memory, then page >in another "segment" and process that, etc., etc. Even so it would now be accessed at disk speeds and thus very slow by comparison. And OBTW, the db won't likely do sequential access. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What happens if you insert more than your RAM
Wouldn't it page to disk, thrash and be very slow first? >On Mon, 18 Apr 2011 09:46:44 -0400, Pavel Ivanov wrote: >You won't be able to insert. The statement will fail. > >On Mon, Apr 18, 2011 at 9:44 AM, Adam DeVita wrote: >> Good day, >> >> What happens if you insert more than your RAM size into an in memory >> database? >> (I'm particularly interested in the Windows context). >> Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Building sqlite for windows in a proper way
>On Mon, Apr 18, 2011 at 8:42 AM, Kuba Nowak wrote: > >Does anyone one know how to build sqlite to get the same binary as on >download page ? > What do you use to compare the speed between the builds of SQLite? Are using debug build from VS2010? My experience is that VS debug builds run most code significantly slower than the release builds. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with error codes
> drh on Tue Apr 12 22:23:38 > >Set your breakpoint on the function winAccess if you want to try to debug >this. Which I cannot do since attaching debugger masks the problem. Now I am really confused. I have modified winAccess to simply return the result of GetFileAttributesExW() if the error is not ERROR_FILE_NOT_FOUND and this appears to eliminate the crash. Could someone please advise on how good or bad of an idea this change is? How well is SQLite tested on Windows 7 64 bit? Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819-4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with error codes
>drh Tue Apr 12 22:23:38 GMT 2011 > >Set your breakpoint on the function winAccess if you want to try to debug >this. If only it were that easy. Its intermittent and attaching a debugger seems to mask the problem. It's never been seen with only a single process and occurs more when there are more simultaneous processes. To process 26 images with 8 processes we are launching 8 processes for the first 8 images, then when each process terminates a new process is launghed on the next image until all are processed. I am using a BEGIN IMMEDIATE transaction at the beginning of each process to check and maybe update the database. This is to have write lock already if update is required and to serialize access to the database for making the check and potentially updating some config data. I'm not really thinking of a good way to approach this. I would greatly appreciate if anyone has any ideas. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help with error codes
I have a process which analyzes an image then writes results to database. (sqlite 3.7.4) When I run multiple of these in parallel, each on its own image but all on the same database, and all on same machine (Windows 7) I get an error from sqlite3_step() when attempting to execute "BEGIN IMMEDIATE". I get proportionaly more errors when I run more processes in parallel. A high percentage of the error occurs just after successful open when trying to check for some existing values to confirm this is correct file. The error looks like it is always from sqlite3_step() on the "BEGIN IMMEDIATE" I am getting a return from sqlite3_step() of 3338. sqlite3_errmsg16() is returning "disk I/O error" sqlite3_prepare16_v2 returned success. And now that I am digging into this I am not sure I have this correct. Am I supposed to get my error code from sqlite3_step() because I have now also noticed sqlite3_extended_errcode()? Is 3338 an extended error code?Do I need to use sqlite3_open_v2 to get the extended error code? Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lack of "decimal" support
>From: Patrick Earl >Subject: Re: [sqlite] Lack of "decimal" support > >If SQLite can't decide on a base-10 format itself, perhaps the answer >lies in enhancing the API to allow for custom type storage and >operators. > So, like a virtual type interface. This would be a nice feature. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to delete all indexes
On Wed, Mar 16, 2011 at 5:59 PM, Simon Slavin wrote: > You should almost never be creating indexes on the fly. Bear in mind that if > SQLite finds a search that would be better with an index, it creates it > itself and it is far better at working out the best index than you are. The > only disadvantage is that it will recreate the index each time you do that > SELECT. > Yes. The code was only supposed to create 1 specific index but bug caused more. Is there any way to know when SQLite has created a temporary index? It would be nice to have an option to allow SQLite to save these temporary indexes when SQLite is being used in a desktop environment. Probably by #define at compile time. Doesn't seem like this would add much test burden since the creation of the temporary indexex must be tested now, likewise saving index is clearly tested now. So I see only new test required for the actual save that would be done in this new execution path. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to delete all indexes
Hello all and thank you in advance for your help. I have just realized that a buggy code has been secretly creating indexes. Thus I have need to make a generic function that can delete all indexes in a database. I have found that I can use this select to get the names of all existing indexes: select name from sqlite_master where type = 'index' and sql is not null; Then I can iterate the result and drop each index. This leaves me with 2 questions. 1. How stable are the column names and table contexts of the sqlite_master table? 2. Is there a better way to accomplish this without resorting to explicit query of the sqlite_master table? Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819-4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query help
>From: Igor Tandetnik [mailto:itandet...@mvps.org] >Sent: Tuesday, March 01, 2011 5:47 PM > >On 3/1/2011 5:01 PM, Jeff Archer wrote: >> And this select which I would like to modify to only return lowest X,Y >> value for each ImageID. > >What does "lowest" mean? If you have two points (100, 200) and (200, 100), >which one is "lower"? >-- >Igor Tandetnik > Sorry, I guess I wasn't very clear. For this purpose either would be fine. The values should tend to be (1.5, 1.5). I think it will just happen to work out if I could get the first row for each ImageID since the values should have been entered in ascending order. I realize this will probably not be guaranteed to get lowest X,Y but for my purpose at the moment this is OK. Thanks, Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query help
Hi all, and thanks in advance for you help. I have the following schema CREATE TABLE [Scans] (ScanIDINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,Timestamp DATETIME NOT NULL UNIQUE ,EndTime DATETIME NOT NULL DEFAULT CURRENT_TIME ,ResultVARCHAR ); CREATE TABLE [Images] (ImageID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,ScanIDINTEGER NOT NULL REFERENCES [Scans] DEFERRABLE INITIALLY DEFERRED ,Filename VARCHAR NOT NULL ,NoINTEGER NOT NULL ,X REAL NOT NULL DEFAULT 0.0 ,Y REAL NOT NULL DEFAULT 0.0 ,Z REAL NOT NULL DEFAULT 0.0 ,R INTEGER NOT NULL DEFAULT 0 ,C INTEGER NOT NULL DEFAULT 0 ,wMicrons REAL NOT NULL DEFAULT 0.0 ,hMicrons REAL NOT NULL DEFAULT 0.0 ,wPixels INTEGER NOT NULL DEFAULT 0 ,hPixels INTEGER NOT NULL DEFAULT 0 ,UNIQUE (ScanID, Filename, No)); CREATE INDEX Images_ScanID_Index on Images(ScanID); CREATE TABLE [Analyzers] (AnalyzerIDINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,Name VARCHAR NOT NULL UNIQUE ); CREATE TABLE [Analysis] (AnalysisIDINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,AnalyzerIDINTEGER NOT NULL REFERENCES [Analyzers] DEFERRABLE INITIALLY DEFERRED ,ScanIDINTEGER NOT NULL REFERENCES [Scans] DEFERRABLE INITIALLY DEFERRED ,Timestamp DATETIME NOT NULL ,EndTime DATETIME NOT NULL DEFAULT CURRENT_TIME ,NumDefectsINTEGER DEFAULT 0 ,ResultVARCHAR ); CREATE INDEX Analysis_AnalyzerID_Index on Analysis(AnalyzerID); CREATE INDEX Analysis_ScanID_Index on Analysis(ScanID); CREATE TABLE [Defects] (DefectID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,ImageID INTEGER NOT NULL REFERENCES [Images] DEFERRABLE INITIALLY DEFERRED ,AnalysisIDINTEGER NOT NULL REFERENCES [Analysis] DEFERRABLE INITIALLY DEFERRED ,X REAL NOT NULL DEFAULT 0.0 ,Y REAL NOT NULL DEFAULT 0.0 ,W REAL NOT NULL DEFAULT 0.0 ,H REAL NOT NULL DEFAULT 0.0 ,Area REAL NOT NULL DEFAULT 0.0 ); And this select which I would like to modify to only return lowest X,Y value for each ImageID. select Defects.DefectID , Defects.ImageID , Defects.AnalysisID , Defects.X , Defects.Y , Defects.W , Defects.H , Defects.Area , Images.X + Defects.X as DefectX , Images.Y + Defects.Y as DefectY from Defects join Images on Defects.ImageID = Images.ImageID I believe that the data is all stored such that the first stored defect for each ImageID will have the lowest X,Y values. At least for now this assumption is probably good enough. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819-4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with join
>Date: Fri, 18 Feb 2011 07:34:06 -0800 >From: Jim Morris > >A correlated sub-query might work for you. > >SELECT >[Analyzers].[AnalyzerID] >, [Analyzers].[Name] AS [Analyzer] >, [Analysis].[AnalysisID] >, [Analysis].[ScanID] >, [Analysis].[Timestamp] >, [Analysis].[EndTime] >, (SELECT COUNT(*) AS NumDefects FROM Defects d where d.AnalysisID = Analysis.AnalysisID) as NumDefects, >, [Analysis].[Result] >FROM [Analysis] >JOIN [Analyzers] ON [Analyzers].[AnalyzerID] = [Analysis].[AnalyzerID] >ORDER BY [Analysis].[Timestamp]; Thank you, Jim. This is very handy technique for me know as I am still a SQL beginner. -- Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiler warnings in R-Tree code under Visual Studio Express
>Date: Fri, 18 Feb 2011 06:13:37 -0800 (PST) >From: Marian Cascaval > >I'm suspecting there's something wrong with Studio Express C++. >I use both 2008 and 2010 and I notice on 2008 that the IDE loses track of code >in case of large source files. Sometimes comments that should have a certain >colour have a different one and so on. > >For example, the first warning at line 120736 there is a variable 'area' which >is of type 'float' defined just 3 lines above. Yet in the line at 120736: > >area = area * (DCOORD(p->aCoord[ii+1]) - DCOORD(p->aCoord[ii])); > >the IntelliSense shows that the second instance of 'area' is of type 'int'. > >To prove this is a problem specific only to Microsoft's IDEs, could you give the >simplest code example (with SQLITE_ENABLE_RTREE=1) to compile it and see if we >get different results with the warnings? > >Marian Cascaval All version of Visual Studio up to and including 2010 have issues with source files longer than 64K lines. The editing works OK but the debugger cannot locate code lines and it confuses Intellisense. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiler warnings in R-Tree code under Visual
>Date: Fri, 18 Feb 2011 13:12:09 + >From: "Black, Michael (IS)" > >I'm of the opinion that all such warnings should be permanently fixed. Such warnings do point to potential problems. >And not by disabling the warning but by fixing the code (explicit casts for example). > >How many people try this and get worried about possible problems? If you simply fix the code once all those people will feel a whole lot better when they crank >up the warnings and see nothing. > >I went through this exercise myself for SQLite at one point and looked at each and every warning to try and determine if there was a possible error caused by all >the miscasts. > >I don't understand the resistance to fixing these. And I'd feel a whole lot better with somebody who KNOWS the code saying "yeah, explicit cast is OK here". I really agree with this. I compile all production code wth highest warning level possible and make it always compile warning free. Why would you NOT want the compiler to point out potential issues. This lets the developer basically make an approval by inserting a cast or whatever. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help with join
Here is my existing schema. I would like to change this and remove the "NumDefects" from the [Analysis] table. To do this I need to update the [vwAnalysis] view to determine the number of defects from the [Defects] table by the AnalysisID. I can't seem to get the right select for the new version of the view. CREATE TABLE [Analyzers] (AnalyzerID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,Name VARCHAR NOT NULL UNIQUE ); CREATE TABLE [Analysis] (AnalysisID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,AnalyzerID INTEGER NOT NULL REFERENCES [Analyzers] DEFERRABLE INITIALLY DEFERRED ,ScanID INTEGER NOT NULL REFERENCES [Scans] DEFERRABLE INITIALLY DEFERRED ,Timestamp DATETIME NOT NULL ,EndTime DATETIME NOT NULL DEFAULT CURRENT_TIME ,NumDefects INTEGER DEFAULT 0 ,Result VARCHAR ); CREATE TABLE [Defects] (DefectID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,ImageID INTEGER NOT NULL REFERENCES [Images] DEFERRABLE INITIALLY DEFERRED ,AnalysisID INTEGER NOT NULL REFERENCES [Analysis] DEFERRABLE INITIALLY DEFERRED ,X REAL NOT NULL DEFAULT 0.0 ,Y REAL NOT NULL DEFAULT 0.0 ,W REAL NOT NULL DEFAULT 0.0 ,H REAL NOT NULL DEFAULT 0.0 ); CREATE VIEW [vwAnalysis] AS SELECT [Analyzers].[AnalyzerID] , [Analyzers].[Name] AS [Analyzer] , [Analysis].[AnalysisID] , [Analysis].[ScanID] , [Analysis].[Timestamp] , [Analysis].[EndTime] , [Analysis].[NumDefects] , [Analysis].[Result] FROM [Analysis] JOIN [Analyzers] ON [Analyzers].[AnalyzerID] = [Analysis].[AnalyzerID] ORDER BY [Analysis].[Timestamp]; This is what I would like to do but I do not understand how to make the JOIN correctly. SELECT [Analyzers].[AnalyzerID] , [Analyzers].[Name] AS [Analyzer] , [Analysis].[AnalysisID] , [Analysis].[ScanID] , [Analysis].[Timestamp] , [Analysis].[EndTime] , COUNT(DefectID) AS NumDefects <<== count of Defects that match the AnalysisID , [Analysis].[Result] FROM [Analysis] JOIN [Analyzers] ON [Analyzers].[AnalyzerID] = [Analysis].[AnalyzerID] ORDER BY [Analysis].[Timestamp]; Thanks in advance for your help with this. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about having 2 db connections on same
>Date: Sun, 13 Feb 2011 22:14:29 -0500 >From: Pavel Ivanov > >> I do a transaction on connection 1 using BEGIN IMMEDIATE, some rows updated, >> COMMIT. >> Then, when I attempt same sequence on connection 2, when I do first call to >>step >> to execute begin transaction and it never returns. > >Do you check result code from COMMIT and is it successful? My guess is >you have some SELECT statements which are not reset or finalized >before COMMIT. And your COMMIT in such situation should return >SQLITE_BUSY. If that's true then second BEGIN IMMEDIATE just can't >finish because another writing transaction is not finished yet. > >Pavel Thanks, Pavel. My wrapper class for SQLite prevents a COMMIT with an outstanding statement. You are however correct, it was a prior failed COMMIT. It just happen on the one place in my code where I do not check return from COMMIT. It was a "constraint failure". Thanks also to others who have offered suggestion. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question about having 2 db connections on same thread
I thought this would be OK but now that I am seeing it fail I am having my doubts. I have created 2 db connections on the same thread, both on the same file. i.e. 2 calls to sqlite3_open16() (use exact same filename both times) have created 2 sqlite3 instances. I do this because I have 2 classes which each have their own set of tables in this single db file. I thought it would be OK because I do not intemix operations on the 2 connections. I do a transaction on connection 1 using BEGIN IMMEDIATE, some rows updated, COMMIT. Then, when I attempt same sequence on connection 2, when I do first call to step to execute begin transaction and it never returns. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 command line utility
From: BareFeetWare list@barefeetware.com Sent: Thu, January 27, 2011 8:50:15 AM > >INSERT INTO "Microscopes" VALUES(null, 'nSpec', 'default'); > >create temp table "Store" (MicroscopeID); > >insert into "Store" select last_insert_rowid(); > >INSERT INTO "Objectives"(ObjectiveID, MicroscopeID, TurretPos, Name, >Description, Magnification, NA, WD) VALUES( 1, (select MicroscopeID from >Store), >0, '5X' , 'CFI LU > >Plan Fluor BD 5X' , 5.0, 0.15, 18.0); > >INSERT INTO "Objectives"(ObjectiveID, MicroscopeID, TurretPos, Name, >Description, Magnification, NA, WD) VALUES( 2, (select MicroscopeID from >Store), >1, '10X' , 'CFI LU > >Plan Fluor BD 10X' , 10.0, 0.30, 15.0); Thank you. This is very convenient. And seems so obvious now that I have seen it. Not sure why I didn't figure this out on my own. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 command line utility
Is it possible to use parameter binding from the sqlite3 command line utility? I would like to make a small illustration of my problem but it only seems to have an issue with I am doing an insert and using a bound parameter for a column which is a foreign key. I have foreign key on by: PRAGMA foreign_keys = ON; BEGIN IMMEDIATE; CREATE TABLE IF NOT EXISTS [Microscopes] (MicroscopeID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,Name VARCHAR NOT NULL ,Description VARCHAR ); CREATE TABLE IF NOT EXISTS [Objectives] (ObjectiveID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,MicroscopeID INTEGER NOT NULL REFERENCES [Microscopes] DEFERRABLE INITIALLY DEFERRED ,TurretPos INTEGER ,Name VARCHAR NOT NULL ,Description VARCHAR NOT NULL ,Magnification REAL NOT NULL ,NA REAL NOT NULL ,WD REAL NOT NULL ); CREATE TABLE IF NOT EXISTS [Calibrations] (CalibrationID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,ObjectiveID INTEGER NOT NULL REFERENCES [Objectives] DEFERRABLE INITIALLY DEFERRED ,MicroscopeID INTEGER NOT NULL REFERENCES [Microscopes] DEFERRABLE INITIALLY DEFERRED ,Timestamp DATETIME NOT NULL UNIQUE ,Description VARCHAR ); INSERT INTO [Microscopes] VALUES(@MicroscopeID, 'nSpec', 'default'); INSERT INTO [Objectives](ObjectiveID, MicroscopeID, TurretPos, Name, Description, Magnification, NA, WD) VALUES( 1, @MicroscopeID, 0, '5X' , 'CFI LU Plan Fluor BD 5X' , 5.0, 0.15, 18.0); INSERT INTO [Objectives](ObjectiveID, MicroscopeID, TurretPos, Name, Description, Magnification, NA, WD) VALUES( 2, @MicroscopeID, 1, '10X' , 'CFI LU Plan Fluor BD 10X' , 10.0, 0.30, 15.0); INSERT INTO [Objectives](ObjectiveID, MicroscopeID, TurretPos, Name, Description, Magnification, NA, WD) VALUES( 3, @MicroscopeID, 2, '20X' , 'CFI LU Plan Fluor BD 20X' , 20.0, 0.45, 4.5); INSERT INTO [Objectives](ObjectiveID, MicroscopeID, TurretPos, Name, Description, Magnification, NA, WD) VALUES( 4, @MicroscopeID, 3, '50X' , 'CFI LU Plan Fluor BD 50X' , 50.0, 0.80, 1.0); INSERT INTO [Objectives](ObjectiveID, MicroscopeID, TurretPos, Name, Description, Magnification, NA, WD) VALUES( 5, @MicroscopeID, 4, '100X', 'CFI LU Plan Fluor BD 100X', 100.0, 0.90, 1.0); COMMIT; The issue is with the @MicroscopeID in the 5 inserts into the Objectives table. If I remove the foreign key constraint on MicroscopeID in the Objectives table I get MicroscopeID of 1 NULL NULL NULL NULL My version is 3.7.3. I have SQLite Expert and have tried same SQL and it appears to work correctly after prompting me for a value of @MicroscopeID 6 times. In my code I have tried making 1 bind to @MicroscopeID and I have tried it with appending the digits 1 to 6 and making 6 calls to bind with same result. My app is 64 bit and SQLite Expert is 32 bit. Any thought or suggestion will be appreciated as I am not sure how to proceed. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Floating point numbers and comparisons
I have noticed a lot of discussion of comparing floating point numbers. I use a set of comparison functions that I have written based on the following article. This is a definitive guide to IEEE floating point numbers and comparisons of such. With this information you can be very specific as to exactly how inexact your floating point comparison is allowed to be. It will also explain why adding .001 or similar is never possible as a general solution. http://www.cygnus-software.com/papers/comparingfloats/Comparing%20floating%20point%20numbers.htm Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite server
From: Simon Slavin Date: Tue, 21 Dec 2010 14:19:25 + >This was my first thought when I considered implementing an SQLite-over-IP >protocol: that there was no user model and therefore no need for passwords. >Mounting a >database on the server would mean that anyone who knew your IP >address, port number and database name could access the data. > >So my conclusion was that you would need to implement, in the first case, a >simple user privilege model, nominating which users (or which IP addresses) >could access >which databases. In SQLiteServer code the user validation could be handled by a callback into the application code that is hosting the SQLiteServer amalgamation code. The application code could leave default of NULL and have no user validation or supply function and provide appropriate user validation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite server
Just my two cents... A SQLite Server would be userful. I have considered creating this myself. I have thought that it would need to be kept zero config and it should be provided in amalgamation form to keep simple to embed in applications. Wouldn't need incredible throughput as it is a ServerLite. Would very conveniently avoid the issues of sharing database file across network. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Troubleshooting...
>> My only guess is that basic_string::c_str() doesn't really provide a pointer >> to >>a null-terminated c-style string, >but a facsimile of one that SQLite doesn't >>like. > >c_str() provides pointer to the data that string has with additional >null byte added at the end. That's it. Whether it is a null-terminated >c-style string depends on what data you put into it. If that data has >null bytes in it then SQLite will see only part of your string, though >I'm not sure that it was the actual problem you have seen. You may probably already know this but maybe I'll remind you. The pointer returned by c_str() is only valid in the statement where it is used or possibly as long as the life of the basic_string it came from. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Just compiled SQLite in Visual Studio
>From: john darnell >Date: Mon, 29 Nov 2010 17:10:04 -0600 > >I just added it to a Visual Studio 8 project, turned off the use of >precompiled >headers (the project is a C++ project) and compiled the SQLite.c >file without >any errors. Is it really that easy, or am I about ready to be hit by a Mac >truck filled with bugaboos? I believe it is that easy. I have been using SQLite 3.6.23 and now 3.7.3 with VS2008 and VS2010 in both 32 and 64 bit applications for a while now with no issues. I have a number of use case with multiple applications (on same machine) in same db file with no issue as long as writer is not too fast. I understand there is a test suite that you can run to test your build of sqlite. I have never run it. I only test the feature functionality of my applications with the assumption that SQLite will work correctly. It hasn't let me down yet. Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make this calculation in VIEW
Thank you, Simon and Owen. I have tried and both solutions work equally well. I have actually used C code to make the calculation as I pull out the data. But to help further my understanding of SQL/SQLite, can anyone comment on potential performance differences of the 2 SQL solutions. CREATE VIEW IF NOT EXISTS [vw_patterns0] AS SELECT Patterns.Name AS Pattern, Patterns.Origin_X, Patterns.Origin_Y, Patterns.wMicrons, Patterns.hMicrons, COUNT(DISTINCT Offset_X) AS nTilesX, COUNT(DISTINCT Offset_Y) AS nTilesY, Patterns.wPixels, Patterns.hPixels, Patterns.wMicrons * COUNT(DISTINCT Offset_X) AS wTotalMicrons, Patterns.hMicrons * COUNT(DISTINCT Offset_Y) AS hTotalMicrons, Patterns.wPixels * COUNT(DISTINCT Offset_X) AS wTotalPixels, Patterns.hPixels * COUNT(DISTINCT Offset_Y) AS hTotalPixels, Patterns.Description FROM [Tiles] INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID GROUP BY Tiles.PatternID; CREATE VIEW IF NOT EXISTS [vw_patterns1] AS SELECT Patterns.Name AS Pattern, Patterns.Origin_X, Patterns.Origin_Y, Patterns.wMicrons, Patterns.hMicrons, COUNT(DISTINCT Offset_X) AS nTilesX, COUNT(DISTINCT Offset_Y) AS nTilesY, Patterns.wPixels, Patterns.hPixels, Patterns.Description FROM [Tiles] INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID GROUP BY Tiles.PatternID; CREATE VIEW IF NOT EXISTS [vw_patterns2] AS SELECT Pattern, Origin_X, Origin_Y, wMicrons, hMicrons, nTilesX, nTilesY, wMicrons * nTilesX AS wTotalMicrons, hMicrons * nTilesY AS hTotalMicrons, wPixels * nTilesX AS wTotalPixels, hPixels * nTilesY AS hTotalPixels, wPixels, hPixels, Description FROM [vw_patterns1]; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to make this calculation in VIEW
First let me say thank you to all for the very good support that receive here. I have the these tables and view // Patterns table CREATE TABLE IF NOT EXISTS [Patterns] ( PatternID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Name VARCHAR NOT NULL UNIQUE, Description VARCHAR NOT NULL, Origin_X REAL NOT NULL DEFAULT 0.0, Origin_Y REAL NOT NULL DEFAULT 0.0, wMicrons REAL NOT NULL DEFAULT 0.0, hMicrons REAL NOT NULL DEFAULT 0.0, wPixels INTEGER NOT NULL DEFAULT 0.0, hPixels INTEGER NOT NULL DEFAULT 0.0 ); // Tiles table CREATE TABLE IF NOT EXISTS [Tiles] (\n" TileID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" PatternID INTEGER NOT NULL REFERENCES [Patterns] DEFERRABLE INITIALLY DEFERRED,\n" Offset_X REAL NOT NULL DEFAULT 0.0,\n" Offset_Y REAL NOT NULL DEFAULT 0.0\n" ); // vwPatterns CREATE VIEW IF NOT EXISTS [vwPatterns] AS SELECT Patterns.Name AS Pattern, Patterns.Origin_X, Patterns.Origin_Y, Patterns.wMicrons, Patterns.hMicrons, COUNT(DISTINCT Offset_X) AS nTilesX, COUNT(DISTINCT Offset_Y) AS nTilesY, Patterns.wPixels, Patterns.hPixels, Patterns.Description FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID GROUP BY Tiles.PatternID; I would like to add these calculated columns to my view but not sure how to make this work. Patterns.wMicrons * nTilesX AS wTotalMicrons, Patterns.hMicrons * nTilesY AS hTotalMicrons, Patterns.wPixels * nTilesX AS wTotalPixels, Patterns.hPixels * nTilesY AS hTotalPixels, i.e. // vwPatterns CREATE VIEW IF NOT EXISTS [vwPatterns] AS SELECT Patterns.Name AS Pattern, Patterns.Origin_X, Patterns.Origin_Y, Patterns.wMicrons, Patterns.hMicrons, COUNT(DISTINCT Offset_X) AS nTilesX, COUNT(DISTINCT Offset_Y) AS nTilesY, Patterns.wPixels, Patterns.hPixels, Patterns.wMicrons * nTilesX AS wTotalMicrons, Patterns.hMicrons * nTilesY AS hTotalMicrons, Patterns.wPixels * nTilesX AS wTotalPixels, Patterns.hPixels * nTilesY AS hTotalPixels, Patterns.Description FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID GROUP BY Tiles.PatternID; But it get error: "no such column: nTilesX" It works if I substitute a constant for the nTilesX and nTilesY so I think I do not know how to correctly reference these. Thank you. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with query
>From: Drake Wilson Sun, November 14, 2010 7:50:19 AM >> SELECT COUNT(Offset_Y) FROM (SELECT DISTINCT Offset_Y FROM Tiles WHERE >>PatternID >> >> = 1); >> >> Is it possible to have a single query that will generate a row for each >> PattenID, COUNT(Offset_Y) combination? > >Does SELECT PatternID, COUNT(DISTINCT Offset_Y) FROM Tiles GROUP BY >PatternID do what you're looking for? That's it. Thank you. I also joined in the pattern name. CREATE VIEW [vwPatterns] AS SELECT Patterns.Name AS Pattern, Patterns.Origin_X, Patterns.Origin_Y, COUNT(DISTINCT Offset_X) AS Width, COUNT(DISTINCT Offset_Y) AS Height, Patterns.Description FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID GROUP BY Tiles.PatternID; Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help with query
I have a table CREATE TABLE [Tiles] ( TileID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, PatternID INTEGER NOT NULL REFERENCES [Patterns] DEFERRABLE INITIALLY DEFERRED, Offset_X REAL NOT NULL DEFAULT 0.0, Offset_Y REAL NOT NULL DEFAULT 0.0 ); And I can get the number of unique Y offsets in a pattern like so: SELECT COUNT(Offset_Y) FROM (SELECT DISTINCT Offset_Y FROM Tiles WHERE PatternID = 1); Is it possible to have a single query that will generate a row for each PattenID, COUNT(Offset_Y) combination? i.e. 1 | 29 2 | 37 3 | 45 Thanks, Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
>From: "Kees Nuyt" >Thursday, November 11, 2010 10:34:51 AM > >Stored procedures don't enforce business rules by >themselves. Constraints and triggers do. >To enforce business rules stored as procedures in the >database, one would need an access system which prevents >direct modification of table data. >As an embedded database SQLite definately is in an other >niche of the DBMS market. >Stored procedures can often be replaced by INSTEAD OF >triggers on views, that's quite powerful. OK, they can't truly enforce business rules but nothing enforces good design on my C++ code either. What it does is allow the business rules to be packaged as a complete unit with the data and I claim that would be better design and more maintainable over the life of the project. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
I hope this doesn't get posted twice. I don't think it will since I sent form wrong account first time. Sorry. >From: "Kees Nuyt" >Thursday, November 11, 2010 10:34:51 AM > >Stored procedures don't enforce business rules by >themselves. Constraints and triggers do. >To enforce business rules stored as procedures in the >database, one would need an access system which prevents >direct modification of table data. >As an embedded database SQLite definately is in an other >niche of the DBMS market. >Stored procedures can often be replaced by INSTEAD OF >triggers on views, that's quite powerful. OK, they can't truly enforce business rules but nothing enforces good design on my C++ code either. What it does is allow the business rules to be packaged as a complete unit with the data and I claim that would be better design and more maintainable over the life of the project. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
>From: "Olaf Schmidt" >Wednesday, November 10, 2010 9:07:19 AM > >[Stored procedures in SQLite] > >IMO stored procedure-support only makes sense in "Server-Instances" which run >on >their own... I disagree. The overall design and structure of applications using SQLite and therefor SQLite itself would benefit from SQLite supporting stored procedures. This would allow all code necessary for enforcing the business rules of the data to be stored in the database itself. This is just a good basic design principal. Please don't anyone take this comment wrongly. I really like SQLite and I am very committed to its use in my projects. Many thanks to all who make SQLite possible. It is an excellent product. And the support available through this mailing list is truly top notch. I have paid (sometimes dearly) for a lot less competent and complete support. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] A question about transactions
I am using SQLite from C++ and I have implemented a class to manager nested transactions using savepoints. I have currently implemented as a stack of transactions such that the first Begin uses BEGIN IMMEDIATE, while subsequent levels use SAVEPOINT T where is a sequentially increasing number starting at 0001. Commit does RELEASE on the latest SAVEPOINT or a real COMMIT once savepoint stack is cleared. Rollback does ROLLBACK TO or a real ROLLBACK once savepoint stack is cleared. Is this OK to mix savepoints with transactions like this? Thanks, Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query help
I have a table containing width and height of images with columns wPixels, hPixels. I would like to select all rows that have either a unique wPixels or a unique hPixels value. for this data: 10, 20 10, 20 10, 30 10, 3015, 10 15, 30 15, 30 15, 30 I would like to select: 10, 20 10, 30 15, 10 15, 30 Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] General questions from a newbe
I'm new to SQLite but have some experience with MS SQL Server. I am looking for a quick confirmation from expert that my planed use of SQLite is reasonable and maybe point out if there are any gotchas. I've done some prototyping and tried most of this out but it is based on how I've done things in the past so maybe not best way in SQLite. My prototypes have only been very small. I will have two general usage patterns. 1) Few and infrequent single row updates. 2) I will start a secondary processes that will scan through a table (1000-2 rows) and for each row run an analysis that will take 1 to 90 seconds and then update another table with the results. I'm on windows. I have compiled SQLite into a dll and wrapped it in some C++ classes. SQLite is not exported from the dll. I plan to use this in both 32 bit and 64 bit applications to access potentially the same files. I will use the following options when I compile SQLite: SQLITE_THREADSAFE=2 SQLITE_DEFAULT_FILE_FORMAT=4 I have my own compilation of sqlite3.exe that I compile the code of sqlite3.c and shell.c into the same application using the same options as above. I call my dll and exe: sqlutils.dll and sqlutil.exe Generally, in the past I have always used normalized data but thought I read this might not always be most efficient in SQLite. Is there any "rule of thumb"? My tables will be relativly small. Most probably in the 1000-2 rows range. I will call stored procedure from C++ for any actually changes to the data. I will use views to make queries simply from c++ code. Thank you for you time in looking into this, Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users