Re: [sqlite] How to exit from sqlite shell?
In windows shell Control-Z should be equivalent of Control-D on Unix (it sends EOF to stdin). Pavel On Mon, May 4, 2009 at 2:58 PM, Kees Nuyt wrote: > On Mon, 4 May 2009 14:00:45 -0400, "D. Richard Hipp" > wrote: > >> >>On May 4, 2009, at 1:44 PM, Sam Carleton wrote: >> >>> prefix with a period: >>> >>> .exit >> >>Yes. Also ".quit" or ".q" or Control-D (on most Unix systems >> - I don't know if Control-D works on windows) > > Control-D doesn't work in sqlite3.exe on windows, Control-C > does, both in the windows shell (CMD.EXE) and in msys > (mingw32) bash. > -- > ( Kees Nuyt > ) > c[_] > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL error: database disk image is malformed
Is it just me or somebody else is seeing too that the sql statement "select blobid, fbid from sig group by peerid" is invalid and shouldn't be executed or prepared at all? Pavel On Mon, May 4, 2009 at 2:51 PM, Joanne Pham wrote: > Hi All, > I ran the following sql statement: > select blobid, fbid from sig group by peerid; > return about 10 rows > 22 > ... > 33 > return about 10 rows and I got the error message: > SQL error: database disk image is malformed > > but when I ran the following sql statement: > select blobid, fbid from sig; > I didn't see any error message. So why the first sql statement has problem > but not the second. > Thanks > JP > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Performance of in-memory database and memory journal
Hi, all! Recently I've discovered that my application works faster if it uses journal_mode = PERSIST instead of MEMORY. After running under callgrind I've found that with journal_mode = MEMORY application spends a lot of time inside malloc() which is called from memjrnlWrite(). Is there a way to override this? I mean, I'd be happy to implement my own vfs for memory journal to avoid such intensive use of malloc(), but it seams that there's no way to do that. Am I wrong and there is actually a way to do it? Now I want to add into application work with in-memory database. For this kind of databases there's only 2 possible values for journal_mode - MEMORY and OFF. I'm afraid that with MEMORY I'll get the same negative performance impact, so I'm leaning to OFF. But if I have journal turned off, make insert into the table and it fails because of unique constraint will it be able to rollback this insert statement? And more general: has journal any meaning at all when all my transactions contain only one auto-committing statement? Thank you, Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite in shell loop
You should do for i in $HANDLER Notice: NO quotes. Though be careful - it will not work if row contains text field with spaces. Pavel On Wed, May 6, 2009 at 5:24 PM, Daniel Wolny wrote: > 2009/5/6 Kees Nuyt : >> On Wed, 6 May 2009 23:01:24 +0200, Daniel Wolny >> wrote: >> It should be possible, sqlite sends its output to stdout, so >> it works like any other unix utility. >> >> Just give it a go. Experiment. >> And enjoy. >> > > It doesn't work to me: > > #!/bin/sh > > HANDLER=`sqlite -noheader /root/adm/var/database/vhosts "SELECT * FROM > vhosts WHERE login='nightwalker';"` > > for i in "$HANDLER" > do > echo "$i" dupa > done > > Result: > 1|nightwalker|nightwalker.szelka.net|1 > 3|nightwalker|stolezka.pl|1 > 4|nightwalker|czteroipolkilogramowyarbuz.pl|1 > 259|nightwalker|satan.edu.pl|1 > 260|nightwalker|prison.net.pl|1 dupa > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance of in-memory database and memory journal
> At the top of file memjournal.c there is a #define called > "JOURNAL_CHUNKSIZE" > that sets the size of the allocations used to store the in-memory > journal. I was interested more in the way to do it without recompilation. So there's no such way - that's ok for now, I can adapt myself to it. > In this case it will only matter if you have statements that affect more > than one row that could hit a constraint. i.e. an UPDATE or an INSERT > INTO > ... SELECT statement. So, if I always do INSERT ... VALUES (), i.e. always insert only one row by one statement, then this insert will be able to rollback harmlessly even without journal? Pavel On Thu, May 7, 2009 at 12:27 AM, Dan wrote: > > On May 7, 2009, at 3:27 AM, Pavel Ivanov wrote: > >> Hi, all! >> >> Recently I've discovered that my application works faster if it uses >> journal_mode = PERSIST instead of MEMORY. After running under >> callgrind I've found that with journal_mode = MEMORY application >> spends a lot of time inside malloc() which is called from >> memjrnlWrite(). Is there a way to override this? I mean, I'd be happy >> to implement my own vfs for memory journal to avoid such intensive use >> of malloc(), but it seams that there's no way to do that. Am I wrong >> and there is actually a way to do it? > > At the top of file memjournal.c there is a #define called > "JOURNAL_CHUNKSIZE" > that sets the size of the allocations used to store the in-memory > journal. > Currently it is defined to (just under) 1024. Maybe you could try > setting > it to a larger value and recompiling. > >> Now I want to add into application work with in-memory database. For >> this kind of databases there's only 2 possible values for journal_mode >> - MEMORY and OFF. I'm afraid that with MEMORY I'll get the same >> negative performance impact, so I'm leaning to OFF. But if I have >> journal turned off, make insert into the table and it fails because of >> unique constraint will it be able to rollback this insert statement? >> And more general: has journal any meaning at all when all my >> transactions contain only one auto-committing statement? > > In this case it will only matter if you have statements that affect more > than one row that could hit a constraint. i.e. an UPDATE or an INSERT > INTO > ... SELECT statement. > > Dan. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Combining .output and .read in a batch file
Maybe you want to do sqlite3 test.db ".output o.txt; .read r.sql" Pavel On Thu, May 7, 2009 at 6:44 AM, Leo Freitag wrote: > Hallo, > > I try to get run the following in a batch file > > Open database "test.db" > Set output to "o.txt" > Read sql-statement form "r.sql" > > === r.sql - Start === > select * from table1; > === r.sql - End === > > Thinks like the following didn't work: > sqlite3 test.db .output o.txt .read r.sql > > > Thanks for your help. > Leo > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.14 and async vfs
Hi! It's great to hear about performance improvements and especially about asynchronous I/O extension. Thank you very much for your work! I have one question though: taking quick look at the sources of async vfs I've noticed that even closing the file is just a task in the async queue and thus after closing sqlite connection file remains opened for some time. It sounds pretty reasonable, but here stands the question: what if I want to do something with the database file after I close sqlite connection to it (e.g. move to the archive directory, zip it etc.)? With sync vfs I could be sure that after closing connection file is closed and I can do with it whatever I want. Is there a way to catch the moment of actual file closing with async vfs? And another question just to be sure that I understand it correctly: async vfs holds only one queue for all opened database files, right? Pavel On Wed, May 6, 2009 at 10:36 PM, D. Richard Hipp wrote: > SQLite version 3.6.14 is now available on the SQLite website > > http://www.sqlite.org/ > > Version 3.6.14 contains performance enhances in the btree and pager > subsystems. In addition, the query optimizer now knows how to take > advantage of OR and IN operators on columns of a virtual table. > > A new optional extension is included that implements an asynchronous I/ > O backend for SQLite on either windows or unix. The asynchronous I/O > backend processes all writes using a background thread. This gives > the appearance of faster response time at the cost of durability and > additional memory usage. See http://www.sqlite.org/asyncvfs.html for > additional information. > > This release also includes many small bug fixes and documentation > improvements. > > As always, please let me know if you encounter any difficulties. > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.14 and async vfs
Shutdown is not an option at all. I need vfs to continue working on other databases but to be notified (or have possibility to check) when one particular database is no longer opened. Pavel On Thu, May 7, 2009 at 12:00 PM, Ken wrote: > > --- On Thu, 5/7/09, Virgilio Alexandre Fornazin > wrote: > >> From: Virgilio Alexandre Fornazin >> Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs >> To: "'General Discussion of SQLite Database'" >> Date: Thursday, May 7, 2009, 10:50 AM >> Close should wait for all file >> operations complete to meet that needs. >> I think asynchronous VFS should take care of waiting in >> sqlite3_close() >> call. >> >> -Original Message- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] >> On Behalf Of Pavel Ivanov >> Sent: quinta-feira, 7 de maio de 2009 12:33 >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs >> >> Hi! >> >> It's great to hear about performance improvements and >> especially about >> asynchronous I/O extension. Thank you very much for your >> work! >> >> I have one question though: taking quick look at the >> sources of async >> vfs I've noticed that even closing the file is just a task >> in the >> async queue and thus after closing sqlite connection file >> remains >> opened for some time. It sounds pretty reasonable, but here >> stands the >> question: what if I want to do something with the database >> file after >> I close sqlite connection to it (e.g. move to the archive >> directory, >> zip it etc.)? With sync vfs I could be sure that after >> closing >> connection file is closed and I can do with it whatever I >> want. Is >> there a way to catch the moment of actual file closing with >> async vfs? >> >> And another question just to be sure that I understand it >> correctly: >> async vfs holds only one queue for all opened database >> files, right? >> >> Pavel >> >> On Wed, May 6, 2009 at 10:36 PM, D. Richard Hipp >> wrote: >> > SQLite version 3.6.14 is now available on the SQLite >> website >> > >> > http://www.sqlite.org/ >> > >> > Version 3.6.14 contains performance enhances in the >> btree and pager >> > subsystems. In addition, the query optimizer now >> knows how to take >> > advantage of OR and IN operators on columns of a >> virtual table. >> > >> > A new optional extension is included that implements >> an asynchronous I/ >> > O backend for SQLite on either windows or unix. The >> asynchronous I/O >> > backend processes all writes using a background >> thread. This gives >> > the appearance of faster response time at the cost of >> durability and >> > additional memory usage. See http://www.sqlite.org/asyncvfs.html for >> > additional information. >> > >> > This release also includes many small bug fixes and >> documentation >> > improvements. >> > >> > As always, please let me know if you encounter any >> difficulties. >> > >> > D. Richard Hipp >> > d...@hwaci.com >> > >> > > > Without actually looking at the async code I think that instead of using the > sqlite3_close to cause a block there should be a "shutdown" that would wait > for the shutdown of the async thread to complete. So maybe a better name > would be sqlite3Async_close or something similar. > > Ken > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.14 and async vfs
According to the fact that queue in async vfs is one for all databases and along with closing of one database there could be some writings to another ones, method of catching the return from sqlite3async_run() can be significantly delayed if work at all... Ok, thank you for the extension anyway. I will think what can I do in this situation. Pavel On Thu, May 7, 2009 at 12:54 PM, Dan wrote: > > On May 7, 2009, at 10:33 PM, Pavel Ivanov wrote: > >> Hi! >> >> It's great to hear about performance improvements and especially about >> asynchronous I/O extension. Thank you very much for your work! >> >> I have one question though: taking quick look at the sources of async >> vfs I've noticed that even closing the file is just a task in the >> async queue and thus after closing sqlite connection file remains >> opened for some time. It sounds pretty reasonable, but here stands the >> question: what if I want to do something with the database file after >> I close sqlite connection to it (e.g. move to the archive directory, >> zip it etc.)? With sync vfs I could be sure that after closing >> connection file is closed and I can do with it whatever I want. Is >> there a way to catch the moment of actual file closing with async vfs? > > Not easily. With the current code you could call sqlite3async_control() > to configure the background thread to return when the write-queue is > empty > (SQLITEASYNC_HALT_IDLE). When the call to sqlite3async_run() returns you > can be sure that the queue is empty and thus any close-file operation > must > have been flushed through. > >> And another question just to be sure that I understand it correctly: >> async vfs holds only one queue for all opened database files, right? > > True statement. > > Dan. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] setting a date in a BEFORE INSERT trigger
According to the syntax of CREATE TRIGGER statement you can use in the
trigger body only insert/update/delete/select statements. There's no
support of simple assignments or any other programming language
extensions.
Pavel
On Sat, May 9, 2009 at 11:11 AM, Sam Carleton
wrote:
> This is my first BEFORE INSERT trigger in SQLite and I am getting an error:
>
> SQL error: near "new": syntax error
>
> My goal is that on an insert only the insertedby value is provide.
> The trigger will set that to the updatedby, insertedon and updatedon
> fields. I searched the web and the only examples I could find was of
> an AFTER INSERT, am I better off with that approach? I would think
> not.
>
> Here is SQL for the table and trigger:
> ---
> CREATE TABLE Customer (
> CustomerId INTEGER PRIMARY KEY AUTOINCREMENT,
> IsInSlideShow INTEGER NOT NULL,
> Username CHAR(50) NOT NULL,
> Password CHAR(50),
> insertedby CHAR(50) NOT NULL,
> instertedon DATE NOT NULL,
> updatedby CHAR(50) NOT NULL,
> updatedon DATE NOT NULL,
> UNIQUE (username));
>
> CREATE TRIGGER ti_Customer BEFORE INSERT ON Customer
> BEGIN
> new.instertedon = DATETIME('NOW');
> new.updatedon = new.instertedon;
> new.updatedby = new.insertedby;
> END;
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple counts between two tables
Try something like this: SELECT tag_name.id, SUM(CASE WHEN tag_link.app = 't' THEN 1 ELSE 0) AS cntTwt, SUM(CASE WHEN tag_link.app = 'd' THEN 1 ELSE 0) AS cntDel FROM tag_name, tag_link WHERE tag_link.tag_id = tag_name.id GROUP BY tag_name.id Pavel On Sun, May 10, 2009 at 6:09 PM, S Fiedler wrote: > > Hi Kees, > > thanks for your help. Thats a neater way than I structured my JOIN version > before. But my goal is to have all tag COUNTs for each application in one > result row + id and name of the tag. Like: > > tag-id | tag-name | count_app_t | count_app_d > - > 1 | sql | 9 | 2 > 2 | xml | 61 | 0 > 3 | foo | 47 | 826 > > Until now no 'JOIN construction' allowed more than one COUNT. Thats why I > tried out the SELECT (SELECT …), (SELECT) way – which didn't though errors, > but produced the freeze of the script. > > Regards, > -steffen > > > Kees Nuyt wrote: >> >> On Sun, 10 May 2009 13:51:29 -0700 (PDT), ckeen >> wrote: >> >>> >>>Hi, I'm trying to count two different column combinations using two > tables. >>>Table one (tag_name) contains the 'id' and the 'name' for a tag. Table two >>>is stuffed with entries that links those tags with different applications >>>and their contents. >>>Now I would like to select how often each tag is used in each application. >>>Some of my previous tries worked fine (using JOIN ON) – but only one COUNT >>>was possible. Now I'm trying to get a statement to work which returns me > the >>>tag amounts for both apps. >>> >>>tag_name >>>id | tag >>> >>>1 | sql >>>2 | xml >>>3 | foo >>> >>>tag_link >>>id | app | app_id | tag_id >>> >>>1 | d | 331 | 2 >>>2 | t | 49 | 1 >>> >>>Here is my current statement: >>>SELECT >>> tag_name.id, >>> (SELECT COUNT(*) FROM tag_link WHERE tag_link.tag_id = tag_name.id AND >>>tag_link.app = 't') AS cntTwt, >>> (SELECT COUNT(*) FROM tag_link WHERE tag_link.tag_id = tag_name.id AND >>>tag_link.app = 'd') AS cntDel >>>FROM >>> tag_name >>>GROUP BY >>> tag_name.id >> >> >> sqlite_version():3.6.13 >> CREATE TABLE tag_name ( >> id INTEGER PRIMARY KEY, >> tag TEXT >> ); >> CREATE TABLE app_name ( >> id INTEGER PRIMARY KEY, >> app TEXT >> ); >> CREATE TABLE tag_link ( >> app_id INTEGER, >> tag_id INTEGER, >> PRIMARY KEY (app_id,tag_id) >> ); >> INSERT INTO tag_name values (1,'sql'); >> INSERT INTO tag_name values (2,'xml'); >> INSERT INTO tag_name values (3,'foo'); >> >> INSERT INTO app_name values (30,'a'); >> INSERT INTO app_name values (39,'b'); >> INSERT INTO app_name values (49,'t'); >> INSERT INTO app_name values (331,'d'); >> >> INSERT INTO tag_link values (331,1); >> INSERT INTO tag_link values (331,2); >> INSERT INTO tag_link values (49,1); >> INSERT INTO tag_link values (30,1); >> INSERT INTO tag_link values (39,2); >> INSERT INTO tag_link values (331,3); >> INSERT INTO tag_link values (49,3); >> >> SELECT app_name.id,app,COUNT(tag_link.tag_id) AS nrtags >> FROM tag_link >> INNER JOIN app_name ON (app_name.id = tag_link.app_id) >> GROUP BY tag_link.app_id; >> >> app_name.id|app_name.app|nrtags >> 30|a|1 >> 39|b|1 >> 49|t|2 >> 331|d|3 >> >> >>>The parser returns no error, only seems to freeze. Tipps, hints – all kind >>>of advice. >>>sincerely, ckeen >> >> Hope this helps. >> -- >> ( Kees Nuyt >> ) >> c[_] >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > -- > View this message in context: > http://www.nabble.com/Multiple-counts-between-two-tables-tp23473911p23474562.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple counts between two tables
Sorry, forgot END after each case, so it should look like SELECT tag_name.id, SUM(CASE WHEN tag_link.app = 't' THEN 1 ELSE 0 END) AS cntTwt, SUM(CASE WHEN tag_link.app = 'd' THEN 1 ELSE 0 END) AS cntDel FROM tag_name, tag_link WHERE tag_link.tag_id = tag_name.id GROUP BY tag_name.id Pavel On Sun, May 10, 2009 at 7:40 PM, Pavel Ivanov wrote: > Try something like this: > > SELECT > tag_name.id, > SUM(CASE WHEN tag_link.app = 't' THEN 1 ELSE 0) AS cntTwt, > SUM(CASE WHEN tag_link.app = 'd' THEN 1 ELSE 0) AS cntDel > FROM > tag_name, tag_link > WHERE > tag_link.tag_id = tag_name.id > GROUP BY > tag_name.id > > > Pavel > > On Sun, May 10, 2009 at 6:09 PM, S Fiedler wrote: >> >> Hi Kees, >> >> thanks for your help. Thats a neater way than I structured my JOIN version >> before. But my goal is to have all tag COUNTs for each application in one >> result row + id and name of the tag. Like: >> >> tag-id | tag-name | count_app_t | count_app_d >> - >> 1 | sql | 9 | 2 >> 2 | xml | 61 | 0 >> 3 | foo | 47 | 826 >> >> Until now no 'JOIN construction' allowed more than one COUNT. Thats why I >> tried out the SELECT (SELECT …), (SELECT) way – which didn't though errors, >> but produced the freeze of the script. >> >> Regards, >> -steffen >> >> >> Kees Nuyt wrote: >>> >>> On Sun, 10 May 2009 13:51:29 -0700 (PDT), ckeen >>> wrote: >>> >>>> >>>>Hi, I'm trying to count two different column combinations using two >> tables. >>>>Table one (tag_name) contains the 'id' and the 'name' for a tag. Table two >>>>is stuffed with entries that links those tags with different applications >>>>and their contents. >>>>Now I would like to select how often each tag is used in each application. >>>>Some of my previous tries worked fine (using JOIN ON) – but only one COUNT >>>>was possible. Now I'm trying to get a statement to work which returns me >> the >>>>tag amounts for both apps. >>>> >>>>tag_name >>>>id | tag >>>> >>>>1 | sql >>>>2 | xml >>>>3 | foo >>>> >>>>tag_link >>>>id | app | app_id | tag_id >>>> >>>>1 | d | 331 | 2 >>>>2 | t | 49 | 1 >>>> >>>>Here is my current statement: >>>>SELECT >>>> tag_name.id, >>>> (SELECT COUNT(*) FROM tag_link WHERE tag_link.tag_id = tag_name.id AND >>>>tag_link.app = 't') AS cntTwt, >>>> (SELECT COUNT(*) FROM tag_link WHERE tag_link.tag_id = tag_name.id AND >>>>tag_link.app = 'd') AS cntDel >>>>FROM >>>> tag_name >>>>GROUP BY >>>> tag_name.id >>> >>> >>> sqlite_version():3.6.13 >>> CREATE TABLE tag_name ( >>> id INTEGER PRIMARY KEY, >>> tag TEXT >>> ); >>> CREATE TABLE app_name ( >>> id INTEGER PRIMARY KEY, >>> app TEXT >>> ); >>> CREATE TABLE tag_link ( >>> app_id INTEGER, >>> tag_id INTEGER, >>> PRIMARY KEY (app_id,tag_id) >>> ); >>> INSERT INTO tag_name values (1,'sql'); >>> INSERT INTO tag_name values (2,'xml'); >>> INSERT INTO tag_name values (3,'foo'); >>> >>> INSERT INTO app_name values (30,'a'); >>> INSERT INTO app_name values (39,'b'); >>> INSERT INTO app_name values (49,'t'); >>> INSERT INTO app_name values (331,'d'); >>> >>> INSERT INTO tag_link values (331,1); >>> INSERT INTO tag_link values (331,2); >>> INSERT INTO tag_link values (49,1); >>> INSERT INTO tag_link values (30,1); >>> INSERT INTO tag_link values (39,2); >>> INSERT INTO tag_link values (331,3); >>> INSERT INTO tag_link values (49,3); >>> >>> SELECT app_name.id,app,COUNT(tag_link.tag_id) AS nrtags >>> FROM tag_link >>> INNER JOIN app_name ON (app_name.id = tag_link.app_id) >>> GROUP BY tag_link.app_id; >>> >>> app_name.id|app_name.app|nrtags >>> 30|a|1 >>> 39|b|1 >>> 49|t|2 >>> 331|d|3 >>> >>> >>>>The parser returns no error, only seems to freeze. Tipps, hints – all kind >>>>of advice. >>>>sincerely, ckeen >>> >>> Hope this helps. >>> -- >>> ( Kees Nuyt >>> ) >>> c[_] >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >> >> -- >> View this message in context: >> http://www.nabble.com/Multiple-counts-between-two-tables-tp23473911p23474562.html >> Sent from the SQLite mailing list archive at Nabble.com. >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepared statements must be generated inside your transaction
I believe, these matters are very specific for each database server (though I can't recall server for which it's true what you say). What specific server is talked about in this book? What's the name of this book? As long as SQLite is a concern, I prepare statements outside of transaction and then use them across different transactions without any problems but with huge performance improvement compared to when I've prepared statements before each transaction. Pavel On Tue, May 12, 2009 at 12:32 PM, Joanne Pham wrote: > Hi All, > I have read one of the performance document and it stated that "prepared > statements must be generated inside transaction". Is that correct. > The document also stated that " While trying to improve the performance of > bulk imports in our C++ project, we found that creating the prepared > statements was a large hit. Creating them once at the > construction of our class, though, made the problem worse! It turns > out that prepared statements that are generated before the transaction start > do not work with the transaction. The fix was simply to > create new prepared statements once per transaction." > > So I have to do this: > begin transaction > prepared statement > .. > end transaction. > > I though the prepare statement must be outside of the transaction. Can any > one confirm this? > Thanks, > JP > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepared statements must be generated inside your transaction
I think last-last paragraph answers your question and proves incorrectness of previous paragraph: "I've used this technique and it seems to work just fine. I had to remember to reset() the prepared statements immediately after I used them, however." :) Pavel On Tue, May 12, 2009 at 12:48 PM, Joanne Pham wrote: > Thanks for quick responde my email > This is sqlite documentation. Below is the link and last paragraph in this > document has stated that. > > http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning > > > ________ > From: Pavel Ivanov > To: General Discussion of SQLite Database > Sent: Tuesday, May 12, 2009 9:43:01 AM > Subject: Re: [sqlite] Prepared statements must be generated inside your > transaction > > I believe, these matters are very specific for each database server > (though I can't recall server for which it's true what you say). What > specific server is talked about in this book? What's the name of this > book? > > As long as SQLite is a concern, I prepare statements outside of > transaction and then use them across different transactions without > any problems but with huge performance improvement compared to when > I've prepared statements before each transaction. > > Pavel > > On Tue, May 12, 2009 at 12:32 PM, Joanne Pham wrote: >> Hi All, >> I have read one of the performance document and it stated that "prepared >> statements must be generated inside transaction". Is that correct. >> The document also stated that " While trying to improve the performance of >> bulk imports in our C++ project, we found that creating the prepared >> statements was a large hit. Creating them once at the >> construction of our class, though, made the problem worse! It turns >> out that prepared statements that are generated before the transaction start >> do not work with the transaction. The fix was simply to >> create new prepared statements once per transaction." >> >> So I have to do this: >> begin transaction >> prepared statement >> .. >> end transaction. >> >> I though the prepare statement must be outside of the transaction. Can any >> one confirm this? >> Thanks, >> JP >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Program is crashed on sqlite3_free
Double call to sqlite3_finalize() ? Pavel On Tue, May 12, 2009 at 5:22 PM, Joanne Pham wrote: > Hi all, > I have the application and occasionally it is crashed on sqlite3_free when it > callled sqlite3_finalized and I really didn't know what problem it was. > Below is stack trace. > Do you have any idea what is caused this problem.? Any hints will be greatly > appreciated. > > #0 0xb5d90c31 in sqlite3_free () from /opt/phoenix/i386/lib/libsqlite3.so.0 > #1 0xb5db69cd in sqlite3VdbeMemRelease () from > /opt/phoenix/i386/lib/libsqlite3.so.0 > #2 0xb5db3cd3 in sqlite3VdbeChangeToNoop () from > /opt/phoenix/i386/lib/libsqlite3.so.0 > #3 0xb5db49c8 in sqlite3VdbeHalt () from > /opt/phoenix/i386/lib/libsqlite3.so.0 > #4 0xb5db4c12 in sqlite3VdbeReset () from > /opt/phoenix/i386/lib/libsqlite3.so.0 > #5 0xb5db4d60 in sqlite3VdbeFinalize () from > /opt/phoenix/i386/lib/libsqlite3.so.0 > #6 0xb5db2b9d in sqlite3_finalize () from > /opt/phoenix/i386/lib/libsqlite3.so.0 > Thanks, > JP > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bad UPDATE performance
Just a couple of questions that can clarify situation: Do you work with pragma synchronous = FULL? Does the behavior of the journal you have described remains if you change pragma journal_mode = PERSIST? Pavel On Tue, May 12, 2009 at 4:15 PM, Ofir Neuman wrote: > Hi, > > Using version 3.6.10.0. > > I have a problem that my application becomes slow after awhile with no > specific reasonI got a chance to reproduce it on development environment and > have noticed that UPDATE of 200 records within transaction takes more than > 20 sec. > > When the application first run the same update for the same records takes > something like 1-2 sec. > > Since I had the problem while debugging I try to figure out the problem and > have noticed the following things: > > 1. When I suffer bad UPDATE performance the journal file is 3 times bigger > than on regular run (when UPDATE command takes 1-2 sec) > 2. Most of the time spent on the UPDATE itself and not on the COMMIT > operation. > 3. I tried to open new db connection while the application still running and > do the same operation and guess what... Everything works fine the > application is responsive and the same update took something like a second. > > I didn't find a specific scenario but I can reproduce it if I do the same > operation on the DB multiple timesafter a while everything will become slow. > > It looks like the UPDATE operation doesn't run within a transaction even > though that I got a successful result after calling to BEGIN IMMEDIATE > TRANSACTIONand I can see that during the UPDATE operation the journal file > become bigger and bigger and removed after calling to COMMIT TRANSACTION. > > Is this a known bug? > > Should I close the DB connection after a while? > > I have not idea how to solve this issue without closing the DB connection > and open a new one. > > Thanks. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction isolation
SQLite doesn't support several simultaneous transactions on the same
connection to database. So in fact your select and insert statements
execute in the same transaction. And even more: your inserts are not
committed until your select is completely finished. So for your task
you should use different connections for select and inserts. But it
will not work anyway, because SQLite doesn't support row-level
locking, it locks entire database. Thus your select statement on one
connection will block execution of inserts on another connection. So
you should stick with one of the solutions mentioned or switch to
another database engine that will fit you better.
BTW, ACID that you mentioned has nothing to do with snapshot isolation
that you want to achieve. AFAIK only Oracle supports this kind of
statement isolation level.
Pavel
On Mon, May 18, 2009 at 12:41 PM, Yang Zhang wrote:
> John Elrick wrote:
>> Yang Zhang wrote:
>>> Roger Binns wrote:
>>>
Yang Zhang wrote:
> for i in (str(row[0]) for row in conn.cursor().execute('SELECT key FROM
> shelf ORDER BY ROWID')):
>
You are converting the key which is an integer into a string for no
apparent reason.
>>> I copied and pasted this code straight from my actual application, which
>>> uses blobs instead of integers, which I need to convert into strings
>>> (since Python interfaces with blobs using the `buffer` type, not `str`).
>>>
>>>
If you also ask for the ROWID you will see that what is happening is a
new rowid is generated for the replaced row so that if you are iterating
over the table while modifying it then you effectively have an infinite
length table.
>>> This is unusual for a RDBMS that claims to provide ACID properties - in
>>> particular, this is not even as strong an isolation level as snapshot
>>> isolation, as a reader transaction is able to see a concurrent writer
>>> transaction's effects. In fact, this is weaker than the weakest
>>> isolation level in (say) Postgresql, which is READ COMMITTED (in which
>>> any statement is guaranteed to not see the effects of a transaction that
>>> is committed after the query has started execution).
>>
>> As I am not an expert in the Python wrapper, I could be incorrect;
>> however, your code as written appears to be equivalent to the following:
>>
>> begin transaction
>> for select(
>> insert stuff
>> end
>> commit
>>
>> rather than your intended:
>>
>> s = select(...
>> begin transaction
>> for s...
>> insert stuff
>> end
>> commit
>>
>> I say this because your example implies that the Python wrapper starts
>> the transaction automatically inside the execute, and I would not be
>> surprised if it did so BEFORE executing the SQL parameter.
>
> The cursor() method that I call on the conn for the SELECT should give
> me a separate transaction.
> --
> Yang Zhang
> http://www.mit.edu/~y_z/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in documentation of SELECT?
I didn't notice it earlier and now I'm a bit surprised. Can I ask a more elaborate example which will include WHERE and/or GROUP BY but not include FROM? Pavel On Mon, May 18, 2009 at 2:32 PM, D. Richard Hipp wrote: > > On May 18, 2009, at 2:18 PM, Mitchell L Model wrote: > >> Is it really possible to have a SELECT with no FROM? If so, could >> someone provide an example; i > > SELECT sqlite_version(); > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in documentation of SELECT?
Actually I wanted to know if it can be useful somewhere. :-) Pavel On Mon, May 18, 2009 at 2:52 PM, Noah Hart wrote: > Just because the syntax allows it, doesn't mean that it will be useful > > SELECT '1', sqlite_version() as A where A < 'a' group by 1 order by 1 > > Noah > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov > Sent: Monday, May 18, 2009 11:37 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] error in documentation of SELECT? > > I didn't notice it earlier and now I'm a bit surprised. Can I ask a > more elaborate example which will include WHERE and/or GROUP BY but > not include FROM? > > Pavel > > > > > CONFIDENTIALITY NOTICE: > This message may contain confidential and/or privileged information. If you > are not the addressee or authorized to receive this for the addressee, you > must not use, copy, disclose, or take any action based on this message or any > information herein. If you have received this message in error, please advise > the sender immediately by reply e-mail and delete this message. Thank you for > your cooperation. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fw: Question about reading all database data into memory
Why do you expect performance improvement in your approach as opposed
to implemented now?
Pavel
On Thu, May 21, 2009 at 1:56 PM, rajyalakshmi bommaraju
wrote:
> I did not get any reply so adding bit more explanation for what I need.
>
> In a C program, I am trying to read data from database. I use
> sqlite3_prepare_v2 to execute "Select * from abc", sqlite3_step to get a row,
> and sqlite3_col_name,sqlite3_col_val to get column values. I read columns
> one by one for each row. Instead I would like to read (x,y,z) { example sake,
> assume that x,y,z columns exist} from a row at a time into variables as a
> tuple. Can I do that?
>
> Is there a way, I can attach tuple to the database query and get the data as
> an array of tuple? Is it possible.
>
> Experts, please throw some light on this.
>
> Thanks
> Raji
> 5/20/09, rajyalakshmi bommaraju wrote:
>
> From: rajyalakshmi bommaraju
> Subject: [sqlite] Question about reading all database data into memory
> To: sqlite-users@sqlite.org
> Date: Wednesday, May 20, 2009, 9:16 PM
>
> HI,
>
> . If I have to read all the database data into memory, I needed to execute
> sqlite3_prepare_v2 statement,and then sqlite3_step to access the rows, and
> processing each row to get the rows content. This does not look efficient.
> Is there any other way to do this?
>
> Thanks
> Raji
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fw: Question about reading all database data into memory
You don't have to ask column name every time. As long as you know what
column names are (select statement you know, right?) you can get
column values by index. And this will have the same performance as
requesting all values by tuple (someone will have get all values and
insert into tuple anyway) except that there should be some routine of
copying tuple from SQLite-owned memory to your application memory.
Pavel
On Thu, May 21, 2009 at 4:44 PM, rajyalakshmi bommaraju
wrote:
> If there are x columns in the row , now I have to keep saying getnext
> columnname,get next column value for that name for x times. instead I get
> values into tuple in one call.
>
> -Raji
>
> --- On Thu, 5/21/09, Pavel Ivanov wrote:
>
> From: Pavel Ivanov
> Subject: Re: [sqlite] Fw: Question about reading all database data into memory
> To: "General Discussion of SQLite Database"
> Date: Thursday, May 21, 2009, 11:33 AM
>
> Why do you expect performance improvement in your approach as opposed
> to implemented now?
>
> Pavel
>
> On Thu, May 21, 2009 at 1:56 PM, rajyalakshmi bommaraju
> wrote:
>> I did not get any reply so adding bit more explanation for what I need.
>>
>> In a C program, I am trying to read data from database. I use
>> sqlite3_prepare_v2 to execute "Select * from abc", sqlite3_step to get a
>> row, and sqlite3_col_name,sqlite3_col_val to get column values. I read
>> columns one by one for each row. Instead I would like to read (x,y,z) {
>> example sake, assume that x,y,z columns exist} from a row at a time into
>> variables as a tuple. Can I do that?
>>
>> Is there a way, I can attach tuple to the database query and get the data as
>> an array of tuple? Is it possible.
>>
>> Experts, please throw some light on this.
>>
>> Thanks
>> Raji
>> 5/20/09, rajyalakshmi bommaraju wrote:
>>
>> From: rajyalakshmi bommaraju
>> Subject: [sqlite] Question about reading all database data into memory
>> To: sqlite-users@sqlite.org
>> Date: Wednesday, May 20, 2009, 9:16 PM
>>
>> HI,
>>
>> . If I have to read all the database data into memory, I needed to execute
>> sqlite3_prepare_v2 statement,and then sqlite3_step to access the rows, and
>> processing each row to get the rows content. This does not look efficient.
>> Is there any other way to do this?
>>
>> Thanks
>> Raji
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] a problem with sqlite3_get_table
Instead of
exe_query(query.str());
try to do this:
size_t len = query.pcount();
exe_query(string(query.str(), len).c_str());
Stringstream never puts 0 byte at the end of the string.
Pavel
On Tue, May 26, 2009 at 12:12 PM, Enrico Piccinini
wrote:
> Hy to all, I'm very new to database and sqlite.
>
> I'm writing a C++ code to execute some queries.
>
> A query of mine does a select with some inner join and a left join as
> reported here:
> //inline int getFunctList(const string& testName, vector& result,
> int& nCol, int& nRow)
> //{
>
> [...]
> ostringstream query;
> // query << "SELECT
> functs_tbl.Description,resources_tbl.Name,functs_tbl.FunctionalString FROM
> seq.test_list_tbl "
> // "INNER JOIN seq.functs_tbl on functs_tbl.FN_ID=test_tbl.FN_ID "
> // "INNER JOIN seq.test_tbl on test_tbl.TL_ID=test_list_tbl.TL_ID "
> // "LEFT JOIN conf.resources_tbl ON
> resources_tbl.RS_ID=functs_tbl.RS_ID "
> // "WHERE test_list_tbl.Name=\"" << testName << "\";" << ends;
> //exe_query(query.str());
> [...]
> //}
>
> "exe_quey" is a simple wrapper of sqlite3_get_table.
>
> The execution of this line code makes may program to crash.
>
> The same query with the same parameter (testName) executed from tcl command
> line works perfectly.
>
> I've also tried to execute this query with sqlite3_prepare, step_reset, but
> the problem is staing on.
>
> I hope that some one can give me some advice because about the solution of
> the problem.
>
> Thank you in advance.
> Enrico
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Global cache size restriction
Hi, all! Is there a way to set global restriction on cache size in SQLite? I like the default cache_size value for each database. But if I open a lot of different databases then total cache size for all of them could be too big for me. So I'd want to set some global_cache_size to limit it. Is it possible to do so? Or I have to implement my own page cache to do it? Thank you, Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 2 columns as primary key?
What create table statement did you use? Pavel 2009/5/29 "Andrés G. Aragoneses" : > I just tried to create a primary key with 2 columns and got this error: > > "sqlite error" "table X has more than one primary key" > > > Doesn't SQLite support this?? :o > > Andres > > -- > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUPY BY alias backward incompatibility
> * The query that works on SQLite all versions fails on Oracle. False conclusion. Did you try to make only one row in t? > * Behaviour is inconsistent between MySQL and Oracle. I believe this conclusion is also false. Did you try several rows in t on MySQL? If it worked I wonder how it showed you the results? > * The query that fails on SQLite > 3.5.3 works on both MySQL and Oracle. Regardless of that my opinion is that the query is nonsense and any effort should not be taken to make it work. Pavel On Sat, May 30, 2009 at 4:30 AM, Ralf Junker wrote: > At 15:37 28.05.2009, D. Richard Hipp wrote: > >>Have you tried these two queries on other SQL database engines besides >>SQLite? What do PostgreSQL and MySQL make of them? > > I could now run the queries on Oracle Database 10g Express Edition Release > 10.2.0.1.0. > > Prepare the table: > > create table t (c integer); > > insert into t values (1); > insert into t values (2); > > Query with t_outer (the one that fails SQLite > 3.5.3) runs without error: > > select > (select count(*) from t t_inner > group by t_outer.c) -- t_outer !!! > from t t_outer; > > Query with t_inner (which works on all SQLite versions) fails with error > "ORA-01427: single-row subquery returns more than one row": > > select > (select count(*) from t t_inner > group by t_inner.c) -- t_inner !!! > from t t_outer; > > Preliminary conclusion: > > * Behaviour is inconsistent between MySQL and Oracle. > > * The query that fails on SQLite > 3.5.3 works on both MySQL and Oracle. > > * The query that works on SQLite all versions fails on Oracle. > > Additional findings from other DB engines would be helpful. Anyone? > > Other than that, I believe it would be desirable if SQLite would support the > t_outer query as it did up to 3.5.3. > > Ralf > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Join performance in SQLite
> Do other SQL database engines not have this same limitation? Are > MySQL and PostgreSQL and Firebird and MS-SQL and Oracle creating > phantom indices on-the-fly to help them do joins faster, for example? Sort of. There's 2 types of join methods in Oracle for this - Hash joins and Sort merge joins - when server creates in memory (or in temporary storage in general) sorted data for one or both merging data sets and then merges these sets. You can read about it here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i51523. Not sure though if it's worth to implement such technique in SQLite. Pavel On Sat, May 30, 2009 at 11:11 AM, D. Richard Hipp wrote: > There has been a recent flurry of comments about SQLite at > > http://www.reddit.com/r/programming/comments/8oed5/how_sqlite_is_tested/ > http://news.ycombinator.com/item?id=633151 > > One of the criticisms of SQLite is that it is slow to do joins. That > is true if SQLite is unable to figure out how to use an index to speed > the join. I was under the impression that SQLite actually did a > fairly reasonable job of making use of indices, if they exist. But > without indices, an k-way join takes time proportional to N^k. > > Do other SQL database engines not have this same limitation? Are > MySQL and PostgreSQL and Firebird and MS-SQL and Oracle creating > phantom indices on-the-fly to help them do joins faster, for example? > Or do their optimizers do a better job of finding ways to use indices > in a join? Can somebody supply me with specific examples of joins > that other database engines do efficiently but that SQLite does > slowly? Is join efficiency really a frustration to many SQLite users? > > Curiously, in some of our own internal tests, SQLite is much, much > faster than MS-SQL, MySQL, and PostgreSQL for k-way joins where k is > large - greater than 20 or 30. (SQLite can handle up to a 64-way > join.) This is because SQLite uses a O(k*k) greedy algorithm for > selecting the ordering of tables in the join whereas the other guys > all do a much more extensive search. So the performance loss in the > other engines is due to the excessive time spent in the query planner, > not the time actually running the query. SQLite can plan a 64-way > join in the blink of an eye, whereas PostgreSQL requires several > minutes. > > But for the tests described in the previous paragraph, there were > always good indices so that the time to actually run the join was > approximately linear. What about situations where you have a 4- or 5- > way join on tables that are not indexed? Do other database engines > handle those more efficiently than SQLite somehow? Is this something > we need to look into? > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE TRIGGER works in all records
Your trigger basically does this:
UPDATE t SET b = DATETIME('now','localtime') WHERE 1 != 0;
So it updates all rows in the table. Try to change it to this:
UPDATE t SET b = DATETIME('now','localtime') WHERE rowid = new.rowid;
Pavel
On Sun, May 31, 2009 at 7:44 AM, Oliver Peters wrote:
> After an UPDATE in a record I want the update time stored in a column of this
> record - the problem is that the trigger I use doesn't work only in this
> record but in all others
>
>
> Here's my script for reproduction:
> -
>
> CREATE TABLE IF NOT EXISTS t(
> a TEXT NOT NULL,
> b TEXT DEFAULT NULL
> );
>
> CREATE TRIGGER IF NOT EXISTS t_update_a
> AFTER UPDATE ON t
> BEGIN
> UPDATE t SET b = DATETIME('now','localtime') WHERE new.a != old.a;
> END;
>
> INSERT INTO t
> (a) VALUES('V');
>
> INSERT INTO t
> (a) VALUES('S');
>
> UPDATE t
> SET a = 'K' WHERE a ='S';
>
> SELECT * FROM t;
>
>
> Thank you for your help
> ___
> Nur bis 31.05.: WEB.DE FreeDSL Komplettanschluss mit DSL 6.000 Flatrate
> und Telefonanschluss für 17,95 Euro/mtl.! http://produkte.web.de/go/02/
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can we solve IF EXIST in SQLite
If you have unique index on stock_tab.prod_batch_code then you can
re-write your trigger as this:
INSERT OR REPLACE INTO stock_tab
(stock_id, prod_batch_code, stock_qty, stock_date)
SELECT new.purchase_id+2, new.prod_batch_code,
new.purchase_qty + ifnull(b.stock_qty, 0), new.purchase_date
FROM (SELECT new.prod_batch_code) a
LEFT JOIN stock_tab b ON b.prod_batch_code = a.prod_batch_code
Pavel
On Tue, Jun 2, 2009 at 8:20 AM, robinsmathew wrote:
>
> guys i ll clarify the problem
> this is the purchase table here purchase id is PK
>
> purchase_id prod_batch_code vendor_type_code purchase_qty purchase_date
> --- ---
> ---
> 1 1000 1 100 2009-05-26
> 18:19:27
> 2 1001 1 100 2009-05-26
> 18:19:31
> 3 1002 1 100 2009-05-26
> 18:19:35
> 4 1003 1 100 2009-05-26
> 18:19:49
>
> this is the stock table here stock_id is PK and prod_batch_code is FK
>
> stock_id prod_batch_code stock_qty stock_date
> -- --- -- ---
> 20001 1001 105 2009-05-26 18:19:27
> 20002 1002 100ps 2009-05-26 18:19:31
> 20003 1003 100ps 2009-05-26 18:19:35
> 20004 1003 100ps 2009-05-26 18:19:43
> 20005 1002 100ps 2009-05-26 18:19:44
> 20006 1001 100ps 2009-05-26 18:19:49
> 20007 1000 85 2009-05-26 18:19:50
> 20008 1000 85 2009-05-26 18:19:51
>
> i wrote a trigger
> CREATE TRIGGER insert_stock_from_product
> AFTER INSERT ON purchase_tab
> BEGIN
> INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty, stock_date)
> values (new.purchase_id+2, new.prod_batch_code, new.purchase_qty,
> new.purchase_date );
> END;
>
> instead of inserting the same products repeatedly in the stock table i jus
> want the quantity as well as the dates to be updated . and wen i insert a
> new product_batch_code to the purchase table its shuld be inserted in the
> stock table also...
>
> Edzard Pasma wrote:
>>
>> Sorry, this was written down without testing. I see now that
>> prod_batch_code must be the primary key, instead of stock_id, for the
>> REPLACE to work as expected. Then some other expression must be used to
>> fill stock_id, e.g. IF_NULL (s.stock_id, 29). I also see that this
>> message crosses Kees Nuyt's idea which may be more comfortable if you like
>> to keep the SQL simple..
>> Edzard
>>
>> --- edz...@volcanomail.com wrote:
>>
>> From: "Edzard Pasma"
>> To: "General Discussion of SQLite Database"
>> Cc:
>> Subject: Re: [sqlite] how can we solve IF EXIST in SQLite
>> Date: Tue, 2 Jun 2009 04:19:33 -0700
>>
>> Hello, you are cleverer than you think. Your initial idea to use INSERT OR
>> REPLACE might look like:
>>
>> INSERT OR REPLACE INTO stock_tab(stock_id, prod_batch_code, stock_qty,
>> stock_date)
>> SELECT
>> s.stock_id,
>> p.prod_batch_code,
>> IF_NULL (s.stock_qty, 0) + p.purchase_qty
>> DATETIME('NOW')
>> FROM purchase_tab p
>> LEFT OUTER JOIN stock_tab s
>> ON s.prod_batch_code = p.prod_batch_code
>> WHERE p.product_batch_code=1000
>> /
>> (assuming stock_id PRIMARY KEY)
>>
>> Best regards, Edzard
>>
>> --- engelsch...@codeswift.com wrote:
>>
>> From: Martin Engelschalk
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] how can we solve IF EXIST in SQLite
>> Date: Tue, 02 Jun 2009 12:46:58 +0200
>>
>> Hi,
>>
>> as far as I know, you cannot do what you want to do in pure SQL.
>> However, perhaps someone cleverer can contradict me.
>>
>> You could first execute the update statement, check if there was a row
>> which was updated using sqlite3_changes() (see
>> http://www.sqlite.org/capi3ref.html#sqlite3_changes), and then issue the
>> insert if there was none.
>>
>> Martin
>>
>> robinsmathew wrote:
>>> hey thanx for the reply... u leave the things happening inside.. wat i
>>> jus
>>> wanna do is i wanna insert a new row to a table
>>> the table will be like this
>>> stock_id PK product_id FK quantity stock_date
>>> 1 1000 10 28-05-2009
>>> 10001 1001 5 27-05-2009
>>>
>>> and wen i insert a new row with values NULL, 1000, 15, 30-05-2009
>>> i dont want want it as a new recorde i jus want to update the first row
>>> coz
>>> its also having the same product id i jus want set the quantity = 10+15
>>> and
>>> the date new date that is 30-05-2009
>>> and suppose if i insert row with different product_id it should be
>>> inserted
>>> as it is..
>>>
>>> Martin Engelschalk wrote:
>>>
H
Re: [sqlite] How to change the default values option when build SQLite 3.6.14
You can do during configuration: ../sqlite/configure -DSQLITE_THREADSAFE=2 Pavel On Wed, Jun 3, 2009 at 2:27 PM, Joanne Pham wrote: > Hi All, > I would like to build the SQLite 3.6.14 to following the steps as mentioned > in the document > tar xzf sqlite.tar.gz ;# Unpack the source tree into "sqlite" > mkdir bld ;# Build will occur in a sibling directory > cd bld ;# Change to the build directory > ../sqlite/configure ;# Run the configure script > make ;# Run the makefile. > make install ;# (Optional) Install the build products > That is the build to use the default option but I want to change the of > SQLITE_THREADSAFE=1 to SQLITE_THREADSAFE=2. How to change this option at the > compiler time. > Thanks, > JP > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "not an error" error inserting data trough a view on fts3 table
"Not an error" is SQLITE_OK. Are you sure that you don't do anything
between getting error code from SQLite and obtaining error message?
Maybe in another thread?
Pavel
On Fri, Jun 5, 2009 at 3:50 AM, Vladimir Kharitonov
wrote:
> I have a problem inserting data through a view.
>
> This is my DB:
>
> CREATE TABLE data (
> id BLOB PRIMARY KEY,
> created REAL NOT NULL
> );
>
> CREATE VIRTUAL TABLE text USING FTS3();
>
> CREATE VIEW data_view AS
> SELECT d.id, d.rowid, d.created, t.content
> FROM data d INNER JOIN text t ON d.rowid = t.rowid;
>
> CREATE TRIGGER data_view_insert INSTEAD OF INSERT ON data_view
> BEGIN
> INSERT INTO data (id , created) VALUES (new.id, new.created);
> INSERT INTO text (docid, content) VALUES (last_insert_rowid(), new.content);
> END;
>
> When I insert rows in the data_view:
>
> PRAGMA count_changes = 1;
> INSERT INTO data_view (id, created, content)
> VALUES (randomblob(16), datetime('now'), 'data');
>
> I receive strange SQLite Error: “not an error”.
> I use version 3.6.13
>
> Any help?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Working with a new DB every 5 minutes
I'd create another special file (maybe even database) that will keep information about current file that your program should be working with. So separate process will create new database and then update this file. And program will just read this file and then work with database mentioned in the file. If you work on Unix you even will not have problems deleting old databases - after updating this special file just unlink old database and after last file handle to it is closed it will be gone... Pavel On Mon, Jun 8, 2009 at 3:07 PM, Mohit Sindhwani wrote: > Hi Everyone, > > I'm having a problem that I'm trying to find an elegant solution to. I > have a database that stores real-time information - this information is > replaced by new values every 5 minutes and has about 30,000 entries. > Once a new database is made available, I should start using that one. > > This database is used as part of an HTTP server that responds to > requests based on the data stored in the database. So, I'm running into > a design issue trying to solve how to "switch" the database from the old > one to the new one. With multiple incoming requests, I'm not sure how/ > when to update the database. Also, due to the large number of records > (30,000) that are to be put in every 5 minutes, I don't think I should > just keep adding to the database since it takes quite some time - it > feels better to let a separate process create the database and alert my > program that a new file is ready! > > Any suggestions on how I should approach this problem? > > Cheers, > Mohit. > 6/9/2009 | 3:07 AM. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Working with a new DB every 5 minutes
> * Getting rid of the old files - I'm on Windows and would need to see > the equivalent way of your Unix suggestion. I don't think you have good options here. One option is to make continuous retries while remove() returns error. Another option I believe would be to change SQLite code to open databasses with FILE_SHARE_DELETE flag. In this case you will be able to work the same way as on Unix. Pavel On Mon, Jun 8, 2009 at 3:24 PM, Mohit Sindhwani wrote: > Pavel Ivanov wrote: >> I'd create another special file (maybe even database) that will keep >> information about current file that your program should be working >> with. So separate process will create new database and then update >> this file. And program will just read this file and then work with >> database mentioned in the file. If you work on Unix you even will not >> have problems deleting old databases - after updating this special >> file just unlink old database and after last file handle to it is >> closed it will be gone... >> > Hi Pavel > > Thanks for your (blazing fast) reply. I did think of something like > this though I was thinking of something like a sqlite database file to > store the recent file. That said, I was concerned about a couple of things: > * I guess there could be times when I would get database busy errors - > but that can be dealt with using a minor delay if it happens. > * Getting rid of the old files - I'm on Windows and would need to see > the equivalent way of your Unix suggestion. > > But this does seem like an interesting way to proceed! We have used > SQLite for other things in this system and it really seems faster > compared to an un-optimized instance of PostgreSQL running on the server. > > Cheers, > Mohit. > 6/9/2009 | 3:24 AM. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Order by term not in result set
It mentions "from" and "column_name" where column name in this particular case is "selected". ;-) @Igor: I thought that sql standard in this case doesn't guarantee that outer select will return rows in the same order that were enforced in inner select by "order by", does it? Pavel On Wed, Jun 10, 2009 at 11:15 AM, Rand Huck wrote: > Just out of curiosity, where in the syntax documentation does it mention > "selected from"? > > On Wed, Jun 10, 2009 at 10:57 AM, Igor Tandetnik wrote: > >> Rand Huck wrote: >> > In addition to what was already said, if you absolutely to omit the >> > 'name' from the final result set, you should be able to put the >> > result of the union in a temporary table. >> >> Or else add an extra layer of indirection: >> >> select id, url, selected from ( >> SELECT id, url, selected, name FROM db1.test UNION >> SELECT id, url, selected, name FROM db2.test >> ORDER BY name ASC, id DESC LIMIT 100 >> ); >> >> This essentially instructs SQLite to create a temporary table for the >> duration of this statemet (also known as ephemeral tables). >> >> Igor Tandetnik >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow select from a single table when using a view
I think you should try to rewrite condition to exclude OR like this: WHERE C >= 1 AND C <= 2. You can even do like this: WHERE C >= 1 AND C <= 3 AND (C = 1 OR C = 3). I think it will be faster than just ORed conditions alone. Pavel On Thu, Jun 11, 2009 at 5:19 AM, Antti Nietosvaara wrote: > On Thursday 11 June 2009 11:50:56 Simon Slavin wrote: >> On 11 Jun 2009, at 8:24am, Antti Nietosvaara wrote: >> > CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE (C=1 OR C=2); >> > where (C=1 OR C=2) will reflect the permissions of the user. >> > >> > There is also an index for C: >> > CREATE INDEX idx_C ON T(C); >> > >> > I have a problem with performance when using the view for simple >> > selects. >> > The following query returns the result immediately: >> > SELECT min(C) from T; >> > >> > However the same query on the view takes a very long time: >> > SELECT min(C) from T_view; >> >> You didn't create an index on T_view ? > > No, it seems you cannot create an index on views. Trying to do so resulted: > "SQL error: views may not be indexed" > > I just noticed that if there I use only one C filter, for example: > CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE C=1; > the queries will be just as fast as with using T directly. Also, explain query > plan returns the string ending with ORDER BY, just like it does when selecting > from T. > With this single filter my query took about 8 ms. With two filters (C=1 OR > C=2) > the time went to over 6 seconds. With (C=1 OR C=2 OR C=3) about 13 seconds. > > -- > Antti Nietosvaara > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.
I bet "synchronous"ness will not be your only bottleneck. Opening connection, preparing statement and closing connection will take in total much longer than executing statement itself. So that doing all these operations 500 times per second will not be possible I think. If you keep pool of connections along with already prepared statements then your application will have chances for survival in such contention environment. And yes, your application will not have any chances without "synchronous = OFF". Without it you're able to do only 10 to 20 transactions per second. Pavel On Thu, Jun 11, 2009 at 9:53 AM, Robel Girma wrote: > Thanks all for your input, very helpful. And yes, there will be 500 separate > connections to the db per seconds, each updating 1 record. I've read about > setting PRAGMA synchronous=OFF to cause SQLite to not wait on data to reach > the disk surface, which will make write operations appear to be much faster. > "But if you lose power in the middle of a transaction, your database file > might go corrupt"==> I can live with this risk if it makes an huge > improvement with the possible contention issue I'm facing. Any input with > this setting you can provide will be greatly appreciated as always. > Robel > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin > Sent: Thursday, June 11, 2009 4:49 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the > following app. > > > On 11 Jun 2009, at 8:23am, Roger Binns wrote: > >> It depends very strongly on how the app is structured and in >> particular >> if there are a few persistent connections to the SQLite database, or >> if >> each request involves a separate connection to the database. If you >> have lots of connections then there will be contention. > > 500 connections a second, each from a different computer. If the OP > handles each one with a separate run of his/her application, that's > 500 connections to the database a second, each updating one record in > one table. > >> If the work done during contention is quick and simple then you are >> fine. If it is long running then you will benefit from a server based >> approach. But when you have commits then disk access is serialized >> and >> you will have performance limitations no matter what the database >> server or SQLite. (That is the point Florian is making.) > > As far as I can tell, with correct programming each query would be one > connection for all the data the query would want. So the OP's > objective is /probably/ achievable with SQLite but I'd want to > prototype it to be sure. > > Sorry, Robel, but we can only guess. Try it. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE with inline view/ derived table
According to this http://www.sqlite.org/lang_update.html you have invalid syntax. I believe you can achieve the same by this (assuming that id is unique in fud28_thread): UPDATE fud28_read SET user_id=2, last_view=1244710953, msg_id=(SELECT last_post_id FROM fud28_thread WHERE id = fud28_read.thread_id) WHERE user_id=2 AND thread_id in (SELECT id FROM fud28_thread WHERE forum_id=4 AND last_post_date > 0) Pavel On Thu, Jun 11, 2009 at 2:17 PM, Frank Naude wrote: > Hi, > > I need some help getting this UPDATE to work with sqlite 3.3.8: > > UPDATE fud28_read > SET user_id=2, msg_id=t.last_post_id, last_view=1244710953 > FROM (SELECT id, last_post_id FROM fud28_thread WHERE forum_id=4 AND > last_post_date > 0) t > WHERE user_id=2 AND thread_id=t.id > > Error: near "FROM" - syntax error: HY000 > > Does sqlite support inline views/ derived tables within UPDATE > statements? Any suggestions on how to get it to work? > > Best regards. > > Frank > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database inserts gradually slowing down
How do you do step 5? Like "delete from table" or "delete from table where ..."? Do you see any degrade in disk space used by database along with slowness? Pavel On Tue, Jun 16, 2009 at 8:06 AM, Jens Páll Hafsteinsson wrote: > Hi > > I've been running some tests against sqlite and have found that inserts are > gradually slowing down. Since I'm new to sqlite I might be doing something > wrong and thought I'd ask here if anyone has seen this before or know what > might be causing this. > > The test I'm running creates a database containing a single table (int, int, > varchar(100)) along with an index (not unique) on the first field. I then > perform the following operations (all records have unique data in the first > field): > > > 1. start a transaction > > 2. insert 1000 records > > 3. commit > > 4. repeat steps 1-3 100 times > > 5. delete everything from the table > > 6. Start again at step 1 > > The time taken to execute steps 1-4 increases gradually from taking around 16 > seconds the first time to just over 28 seconds after about 2400 iterations. > To me, this is alarming since this time increase seems not to be asymptotic > (not sure though, there is a slight curve on the graph and I haven't done any > curve fitting) and I fear that I will end up with an unusable slow database > after some time of use. Even if I shut down the test application and start > again (on the same database and without re-creating the table), it just > continues running as before it was stopped, that is, taking 28 seconds (and > increasing) if I stop it after 2400 iterations. > > At first I ran this for shorter periods without an index and think I saw the > same behavior, but I have to verify that to be sure. > > I'm using sqlite version 3.6.14 on Windows XP, compiled using Visual Studio > 2008. > > If anyone is interested I can e-mail the collected data and the test program > I'm using. > > Any pointers appreciated. > > Thanks, > JP > > - > "What you take away is more important than what you add." Chuck Moore > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Rather SQL quiestion
Try this: select c.* from players a, registrations b, players c where a.device_id = b.device_id and b.mesg_token='123456' and a.table_group_id = c.table_group_id and a.table_id = c.table_id Pavel On Tue, Jun 16, 2009 at 11:25 AM, Boris Ioffe wrote: > > Hello folks, > I am having trouble understanding one very important concept about IN > operator. Let me show you my situation below. > > sqlite> select sqlite_version(); > sqlite_version() > > 3.3.6 > * > Here is my table schema > > CREATE TABLE players > (device_id varchar(40) NOT NULL, table_group_id integer, table_id > integer, > role integer, create_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, > > FOREIGN KEY (table_id,table_group_id) REFERENCES tables(id, group_id), > FOREIGN KEY (device_id) REFERENCES users(device_id), > PRIMARY KEY (device_id, table_group_id, table_id)); > > > > * > select table_group_id , table_id from players join registrations on > players.device_id = registrations.device_id where mesg_token='123456'; > > table_group_id table_id > -- -- > 1 1 > 1 2 > > Now I need to select all players who happened to be playing at the same table > (table_id,table_group_id) REFERENCES tables(id, group_id). > > > Obviously something like that doesn't work: > > select * from players where (table_group_id,table_id) in (select > table_group_id , table_id from players join registrations on > players.device_id = registrations.device_id where mesg_token='123456') > > -It would work only with ONE field. > > select * from players where table_group_id in (select table_group_id from > players join registrations on players.device_id = registrations.device_id > where mesg_token='123456') and table_group_id in (select table_id ...) > > it is not quite the same as it will not match the pair. > Same puzzles me with self join route. I hate to mindlessly traverse all > pairs using code, rather having one transaction. > > I appreciate any help and suggestions. > Thanks, > -B > > > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Literal replacements in prepared statements
You're trying identifier replacement, not literal replacement. It's not allowed. You have to write table name without binding. Pavel On Fri, Jun 19, 2009 at 9:58 AM, Shaun Seckman (Firaxis) wrote: > I'm trying to add some literal replacements in my prepared SQL statement > but I'm currently getting a SQL syntax error. > > Here's a snippit of what I'm trying to do: > > > > ... > > sqlite3_stmt* stmt; > > sqlite3_prepare_v2(db, "Select * from ?", -1, &stmt, NULL); <-- near > "?": syntax error > > sqlite3_bind_text(stmt, 1, tableName, -1, SQLITE_TRANSIENT); > > ... > > > > Any idea what I'm doing wrong? > > > > -Shaun > > > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Literal replacements in prepared statements
Yes, it's impossible to replace table or column names. You can replace any constant values like this: select table.column2, column3 + ? from table where column1 = ? and column2 + ? > column3 limit ?, ? Pavel On Fri, Jun 19, 2009 at 10:06 AM, Shaun Seckman (Firaxis) wrote: > Not sure I fully understand what you mean. Is it not possible to replace the > table name in the prepared statement? What sort of things can I replace then? > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov > Sent: Friday, June 19, 2009 10:03 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Literal replacements in prepared statements > > You're trying identifier replacement, not literal replacement. It's > not allowed. You have to write table name without binding. > > Pavel > > On Fri, Jun 19, 2009 at 9:58 AM, Shaun Seckman > (Firaxis) wrote: >> I'm trying to add some literal replacements in my prepared SQL statement >> but I'm currently getting a SQL syntax error. >> >> Here's a snippit of what I'm trying to do: >> >> >> >> ... >> >> sqlite3_stmt* stmt; >> >> sqlite3_prepare_v2(db, "Select * from ?", -1, &stmt, NULL); <-- near >> "?": syntax error >> >> sqlite3_bind_text(stmt, 1, tableName, -1, SQLITE_TRANSIENT); >> >> ... >> >> >> >> Any idea what I'm doing wrong? >> >> >> >> -Shaun >> >> >> >> >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer primary key autoincrement & sqlite_sequence
Hi, Richard!
Could you explain why this scenario doesn't cause infinite call cycle
of the trigger by itself? Is there some protection in SQLite which
breaks such cycles?
Pavel
On Mon, Jun 22, 2009 at 4:10 PM, D. Richard Hipp wrote:
>
> On Jun 22, 2009, at 3:33 PM, Oliver Peters wrote:
>
>> Hello out there,
>>
>>
>> to my mind I get false entries in sqlite_sequence using this code:
>>
>>
>> CREATE TABLE IF NOT EXISTS a(
>> id INTEGER PRIMARY KEY AUTOINCREMENT,
>> code VARCHAR NOT NULL,
>> name VARCHAR NOT NULL,
>> identity INTEGER DEFAULT NULL,
>> creator INTEGER DEFAULT NULL,
>> timestamp TIMESTAMP DEFAULT
>> (datetime('now','localtime'))
>> );
>>
>>
>> CREATE TRIGGER IF NOT EXISTS test
>> BEFORE INSERT ON "a"
>> BEGIN
>> INSERT INTO a(code,name,identity)
>> VALUES(new."code",new."name",new."identity");
>> SELECT RAISE(IGNORE);
>> END;
>>
>>
>> INSERT INTO a(code,name,identity) VALUES('V','abc',1);
>> INSERT INTO a(code,name,identity) VALUES('S','def',1);
>>
>>
>> As a result I get 2 records in sqlite_sequence:
>>
>> name seq
>> a 1
>> a NULL
>
> Please file a bug report and I will fix it when I get a chance.
>
>
>>
>> shouldn't there only 1 record (a 2)?
>>
>> The ids in table a are as expected (1 & 2).
>>
>>
>> __
>> GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
>> Jetzt freischalten unter http://movieflat.web.de
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How can I specify that a column is equal to another?
You can do only where movies.id = 'tt0426459' or where user.id = 'tt0426459' What to choose depends on your needs. And you're wrong that these variants are identical and movies.id is always equal to user.id because you're making left join. They will be identical if you will make inner join. But even in this case I don't understand why you consider bad or inconvenient explicitly mentioning table name. Pavel On Wed, Jul 1, 2009 at 12:24 PM, Yuzem wrote: > > I have this: > select title,my_rating > from movies left join user on movies.id = user.id > where id = 'tt0426459' > The result: > ambiguous column name: id > > I could use: > select movies.id ids,title,my_rating > from movies left join user on movies.id = user.id > where ids = 'tt0426459' > > but I don't want to select the id > > Another solution: > where movies.id = 'tt0426459' > > Is there any way to specify that movies.id is equal to user.id so I can use > just id in my query? > Thanks in advance! > -- > View this message in context: > http://www.nabble.com/How-can-I-specify-that-a-column-is-equal-to-another--tp24292794p24292794.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3: Database is sometimes locked when a reader is attached with open_v2
>From http://www.sqlite.org/lockingv3.html: -- Eventually, the writing process will want to update the database file, either because its memory cache has filled up or because it is ready to commit its changes. Before this happens, the writer must make sure no other process is reading the database and ... ... Obtain a PENDING lock and then an EXCLUSIVE lock on the database file. If other processes still have SHARED locks, the writer might have to wait until those SHARED locks clear before it is able to obtain an EXCLUSIVE lock. -- I believe this waiting by writer has some timeout and eventually he will get "locked" error if reader doesn't release its SHARED lock for a long time. Pavel On Thu, Jul 2, 2009 at 9:38 AM, Tino Lange wrote: > Hi all, > > I have written a program that opens a SQLIte3 database and writes in it most > of the time via replace/update. > > If I do select like (no writes, really only reads) statements from some > other process that carefully opens the database with "sqlite3_open_v2(..., > SQLITE_OPEN_READONLY, ...)" the main (writing) application might get > errcode=5 ermmsg='database is locked' errors when it tries to write while > the other application (only!) reads. > > How is that possible? How to prevent? > > I would expect that the reading application might (of course) sometimes get > "database is locked" errors, but the writing application should never. > > [ I have also realized that the sqlite3 commandline tool uses sqlite_open() > and always opens in read/write mode. That's why I wrote my own reading > application using sqlite3_open_v2 ] > > Thanks > > Tino > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nested Inner Join Help
I believe you need this: SELECT A.ID, A.Column1, A.Column2, B.Column1, C.Column1 FROM A INNER JOIN B ON A.Column3 = B.ID INNER JOIN C ON B.Column2 = C.ID Pavel On Thu, Jul 2, 2009 at 2:53 PM, Ed Hawke wrote: > Hi all, > > I'm having problems getting nested inner joins to work with SQLite. As > far as I can tell from various resources the correct way of joining > multiple tables is this: > > SELECT A.ID, A.Column1, A.Column2, B.Column1, C.Column1 FROM A INNER > JOIN B (INNER JOIN C ON B.Column2 = C.ID) ON A.Column3 = B.ID > > However depending upon where I put the parentheses I get various > different errors from the viewer I use (SQLite Manager for Firefox). A > normal Inner Join without the nesting works fine. > > Can anyone tell me what I'm doing wrong? > > Regards, > > Ed > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] there is problem when getting data by sqlite3's c apis in signal-handling func
> there is problem when getting data by sqlite3's c apis in signal-handling
> func.
What problem?
And as a hint: are you sure that your SQLite is in correct
thread-safety mode and you're not trying to dead-lock your
application?
Pavel
On Mon, Jul 6, 2009 at 9:58 PM, liubin liu<7101...@sina.com> wrote:
>
> there is problem when getting data by sqlite3's c apis in signal-handling
> func.
>
>
> _
>
>
> #include // for printf()
> #include // for signal()
> #include // for alarm()
> #include // for system()
> #include // for sqlite3_***
>
>
>
> sqlite3 *db = NULL;
>
>
> int sqlite3_helper_create_db (void);
>
>
> int sqlite3_helper_insert_data (void);
>
>
> // psf - sqlite3_prepare_v2()+sqlite3_step()+sqlite3_finalize()
> int sqlite3_helper_get_data_psf_from_tb1 ( int id );
>
> void timer_handler()
> {
> printf ( "# IN timer_handler()\n" );
>
>
> int ret = -1;
> int i = 9;
>
> char *query_format2 = "SELECT * FROM ts2 WHERE id=%d;";
> char *query_string = NULL;
>
>
> sqlite3_stmt *p_stmt = NULL;
>
> printf ( "# IN timer_handler(), ago sqlite3_mprintf()\n" );
> query_string = sqlite3_mprintf ( query_format2, i%500 );
>
> printf ( "# IN timer_handler(), ago sqlite3_prepare_v2()\n" );
> ret = sqlite3_prepare_v2 ( db, query_string, -1, &p_stmt, NULL );
> printf ( "# IN timer_handler(), ago sqlite3_step()\n" );
> ret = sqlite3_step ( p_stmt );
> printf ( "# IN timer_handler(), ago printf()\n" );
> if ( SQLITE_ROW == ret )
> printf ( "# IN timer_handler(), id: %d, length: %d\n",
> sqlite3_column_int( p_stmt, 0 ), sqlite3_column_int( p_stmt, 1 ) );
> printf ( "# IN timer_handler(), ago sqlite3_free_table()\n" );
> sqlite3_finalize ( p_stmt );
>
>
> alarm(1);
> }
>
> int inittimer()
> {
> signal ( SIGALRM, timer_handler );
> alarm(1);
> return 0;
> }
>
>
> int main ( void )
> {
> int ret = -1;
> int i = 0;
>
>
> // open db file
> ret = sqlite3_open ( "testsignal.db", &db );
>
> // create tables: ts1 and ts2
> sqlite3_helper_create_db ();
>
> // insert datas into the tables(ts1&ts2)
> sqlite3_helper_insert_data ();
>
> // init the timer
> ret = inittimer();
>
> // get datas from table ts1
> for ( i=0; ; i++ )
> ret = sqlite3_helper_get_data_psf_from_tb1 ( i );
>
> // close db file
> ret = sqlite3_close ( db );
>
> // get the num of records
> system ( "sqlite3 testsignal.db \"SELECT COUNT(*) FROM ts1;\"" );
>
> // delete db file
> //system ( "rm testsignal.db -rf" );
>
> return 0;
> }
>
>
> int sqlite3_helper_create_db (void)
> {
> int ret = -1;
> char *cr_tb1 = "CREATE TABLE ts1 (id INTEGER PRIMARY KEY, length
> INTEGER, data CHAR(50));";
> char *cr_tb2 = "CREATE TABLE ts2 (id INTEGER PRIMARY KEY, length
> INTEGER, data CHAR(50));";
>
> ret = sqlite3_exec ( db, cr_tb1, NULL, NULL, NULL );
> //printf ( "ret: %d\n", ret );
> ret = sqlite3_exec ( db, cr_tb2, NULL, NULL, NULL );
> //printf ( "ret: %d\n", ret );
> if ( SQLITE_OK == ret )
> printf ( "# IN main(), create tables, DONE~\n" );
>
>
> return 0;
> }
>
>
> int sqlite3_helper_insert_data (void)
> {
> int ret = -1;
> int i = 0;
>
> sqlite3_stmt *p_stmt = NULL;
>
>
> char *sql_format1 = "INSERT OR REPLACE INTO ts1 (id, length, data)
> VALUES (%d, %d, %Q);";
> char *sql_format2 = "INSERT OR REPLACE INTO ts2 (id, length, data)
> VALUES (%d, %d, %Q);";
> char *sql = NULL;
>
>
> ret = sqlite3_exec ( db, "BEGIN", NULL, NULL, NULL );
> for ( i=0; i<500; i++ )
> {
> sql = sqlite3_mprintf ( sql_format1, i, i%10,
> "datadatadatadatadatadatadatadatadatadatadata" );
> ret = sqlite3_prepare_v2 ( db, sql, -1, &p_stmt, NULL );
> ret = sqlite3_step ( p_stmt );
> sqlite3_free ( sql );
> ret = sqlite3_finalize ( p_stmt );
>
> sql = sqlite3_mprintf ( sql_format2, i, i%10,
> "datadatadatadatadatadatadatadatadatadatadata" );
> ret = sqlite3_prepare_v2 ( db, sql, -1, &p_stmt, NULL );
> ret = sqlite3_step ( p_stmt );
> sqlite3_free ( sql );
> ret = sqlite3_finalize ( p_stmt );
> }
> ret = sqlite3_exec ( db, "COMMIT", NULL, NULL, NULL );
> if ( SQLITE_OK == ret )
> printf ( "# IN main(), insert datas, DONE~\n" );
>
> return 0;
> }
>
>
> int sqlite3_helper_get_data_psf_from_tb1 ( int id )
> {
> int ret = -1;
>
> sqlite3_stmt *p_stmt = NULL;
>
>
> char *query_format1 = "SELECT * FROM ts1 WHERE id=%d;";
> char *query_string = NULL;
>
> query_string = sqlite3_mprintf ( query_format1, id%500 );
> ret = sqlite3_prepare_v2 ( db, query_string, -1, &p_stmt, NULL );
> ret = sqlite3_step ( p_stmt );
> if ( SQLITE_ROW == ret )
> printf ( "# IN main(), id: %d, length: %d\n", sqlite3_column_int(
> p_stmt, 0 ), sqlite3_column_int( p_stmt, 1 ) );
> sqlite3_finalize ( p_stmt );
>
> return 0;
> }
>
>
Re: [sqlite] unable to backup using command line
Did you try sqlite3 accounts.db ".backup main a.db" (note the quotes)? Pavel On Tue, Jul 7, 2009 at 2:28 PM, Eric Peterson wrote: > > > > C:\InWork\rsm\weekly_status>sqlite3 accounts.db .database > seq name file > --- --- --- > 0 main C:\InWork\rsm\weekly_status\accounts.db > > > > C:\InWork\rsm\weekly_status>sqlite3 accounts.db .help > > > > > But I can not do the .backup command from the command-line. > > > C:\InWork\rsm\weekly_status>sqlite3 accounts.db .backup main a.db > unknown command or invalid arguments: "backup". Enter ".help" for help > > > > Any ideas why? > > Eric > > > _ > Insert movie times and more without leaving Hotmail®. > http://windowslive.com/Tutorial/Hotmail/QuickAdd?ocid=TXT_TAGLM_WL_HM_Tutorial_QuickAdd_062009 > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_OMIT_AUTOVACUUM seems to need small change in btree.c
Is this related to your problem? http://www.sqlite.org/cvstrac/tktview?tn=3940 Pavel On Wed, Jul 8, 2009 at 4:06 AM, Yan Bertrand wrote: > Hello, > > > > I am compiling SQLite with options to reduce the size, and with hopes to > reduce the duration of some operations on portable devices. This > includes the use of the compilation switch SQLITE_OMIT_AUTOVACUUM. > > I noticed that btree.c will not compile withmy current combination of > switches. I changed it to compile and thought that it could be > interesting for someone to have a look, review and approve it you want > to keep it for the future ? > > > > Attached : > > - Btree.c modified to compile > > - Config.h with my current config > > - Vtab.c with lines inverted for the include of the header that > leads to config.h, and the check for the compilation directive defined > in config.h :-) > > > > > > I am new to SQLite and compiling it for the second week. So feel free to > point me at my mistakes (if any). > > Best regards, > > > > Yan > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is the sqlite3's C-api used in thread-safety mode ? or non-reentrant?
Why do you re-post your code as if it's another question not related
to previous one (along with the answer)?
> does it mean that the sqlite3's C-api isn't reentrant or thread-safety?
If SQLite is compiled with SQLITE_THREADSAFE = 1 or 2 then API is
thread-safe (with some limitations when it's 2), if SQLITE_THREADSAFE
= 0 then API is not thread-safe. But in both cases I have a doubt
about signal-safety and also API is not re-entrant for sure except for
some specially designed functions.
Pavel
On Thu, Jul 9, 2009 at 3:52 AM, liubin liu<7101...@sina.com> wrote:
>
> does it mean that the sqlite3's C-api isn't reentrant or thread-safety?
>
>
>
>
>
> #include // for printf()
> #include // for signal()
> #include // for alarm()
> #include // for system()
> #include // for pthread_create()
> #include // for sqlite3_***
>
>
>
> sqlite3 *db = NULL;
>
> int timer_handle_mark1 = 0;
> int timer_handle_mark2 = 9;
>
>
>
> pthread_t trd1;
>
> void trd1_task ( void );
>
>
>
> int sqlite3_helper_create_db (void);
>
> int sqlite3_helper_insert_data (void);
>
> // psf - prepare, step, finalize
> int sqlite3_helper_get_data_psf_from_tb1 ( int id );
>
> void timer_handler()
> {
> int ret = -1;
> int i = 9;
>
> char *query_format2 = "SELECT * FROM ts2 WHERE id=%d;";
> char *query_string = NULL;
>
>
> sqlite3_stmt *p_stmt = NULL;
>
> timer_handle_mark2 = 0;
> query_string = sqlite3_mprintf ( query_format2, i%500 );
> timer_handle_mark1++;
>
> timer_handle_mark2 = 1;
> ret = sqlite3_prepare_v2 ( db, query_string, -1, &p_stmt, NULL );
> timer_handle_mark1++;
>
> timer_handle_mark2 = 2;
> ret = sqlite3_step ( p_stmt );
> timer_handle_mark1++;
>
> //if ( SQLITE_ROW == ret )
> // printf ( "# IN timer_handler(), id: %d, length: %d\n",
> sqlite3_column_int( p_stmt, 0 ), sqlite3_column_int( p_stmt, 1 ) );
>
> timer_handle_mark2 = 3;
> sqlite3_finalize ( p_stmt );
> timer_handle_mark1++;
>
>
> alarm(1);
> }
>
> int inittimer()
> {
> signal ( SIGALRM, timer_handler );
> alarm(1);
> return 0;
> }
>
> void trd1_task ( void )
> {
> sleep (30);
> printf ( "# IN thread 1, after 30 seconds, timer_handle_mark1: %d,
> timer_handle_mark2: %d\n", timer_handle_mark1, timer_handle_mark2 );
> sleep (50);
> printf ( "# IN thread 1, after 50 seconds, timer_handle_mark1: %d,
> timer_handle_mark2: %d\n", timer_handle_mark1, timer_handle_mark2 );
> }
>
>
>
> int main ( void )
> {
> int ret = -1;
> int i = 0;
>
> ret = pthread_create ( &trd1, 0, (void *)trd1_task, 0 );
>
> ret = sqlite3_open ( "testsignal.db", &db );
>
> sqlite3_helper_create_db ();
>
> sqlite3_helper_insert_data ();
>
> ret = inittimer();
>
> for ( i=0; ; i++ )
> ret = sqlite3_helper_get_data_psf_from_tb1 ( i );
>
> ret = sqlite3_close ( db );
>
> system ( "sqlite3 testsignal.db \"SELECT COUNT(*) FROM ts1;\"" );
>
> return 0;
> }
>
>
> int sqlite3_helper_create_db (void)
> {
> int ret = -1;
> char *cr_tb1 = "CREATE TABLE ts1 (id INTEGER PRIMARY KEY, length
> INTEGER, data CHAR(50));";
> char *cr_tb2 = "CREATE TABLE ts2 (id INTEGER PRIMARY KEY, length
> INTEGER, data CHAR(50));";
>
> ret = sqlite3_exec ( db, cr_tb1, NULL, NULL, NULL );
> //printf ( "ret: %d\n", ret );
> ret = sqlite3_exec ( db, cr_tb2, NULL, NULL, NULL );
> //printf ( "ret: %d\n", ret );
> if ( SQLITE_OK == ret )
> printf ( "# IN main(), create db file, SUCCESS!\n" );
>
>
> return 0;
> }
>
>
> int sqlite3_helper_insert_data (void)
> {
> int ret = -1;
> int i = 0;
>
> sqlite3_stmt *p_stmt = NULL;
>
>
> char *sql_format1 = "INSERT OR REPLACE INTO ts1 (id, length, data)
> VALUES (%d, %d, %Q);";
> char *sql_format2 = "INSERT OR REPLACE INTO ts2 (id, length, data)
> VALUES (%d, %d, %Q);";
> char *sql = NULL;
>
>
> ret = sqlite3_exec ( db, "BEGIN", NULL, NULL, NULL );
> for ( i=0; i<500; i++ )
> {
> sql = sqlite3_mprintf ( sql_format1, i, i%10,
> "datadatadatadatadatadatadatadatadatadatadata" );
> ret = sqlite3_prepare_v2 ( db, sql, -1, &p_stmt, NULL );
> ret = sqlite3_step ( p_stmt );
> sqlite3_free ( sql );
> ret = sqlite3_finalize ( p_stmt );
>
> sql = sqlite3_mprintf ( sql_format2, i, i%10,
> "datadatadatadatadatadatadatadatadatadatadata" );
> ret = sqlite3_prepare_v2 ( db, sql, -1, &p_stmt, NULL );
> ret = sqlite3_step ( p_stmt );
> sqlite3_free ( sql );
> ret = sqlite3_finalize ( p_stmt );
> }
> ret = sqlite3_exec ( db, "COMMIT", NULL, NULL, NULL );
> if ( SQLITE_OK == ret )
> printf ( "# IN main(), insert data, SUCCESS!\n" );
>
> return 0;
> }
>
>
> int sqlite3_helper_get_data_gt_from_tb1 ( int id )
> {
> int ret = -1;
>
>
> char *query_format1 = "SELECT * FROM ts1 WHERE id=%d;";
> char *query_string = NULL;
>
> char **resultp = NULL;
> int row =
Re: [sqlite] is the sqlite3's C-api used in thread-safety mode ? or non-reentrant?
> SQLite does the right thing > for multi-threaded applications and many use it that way - you are not > the first. Roger, note that OP is talking about re-entrance, not thread-safety. He wants to know which functions can be called from signal handlers which can be called in the middle of some other SQLite call in the same thread. I doubt that many people use SQLite with this pattern. Pavel On Fri, Jul 10, 2009 at 8:00 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > liubin liu wrote: >> How could I know which SQLite3's api is reentrant? > > As a general rule they all are. The documentation page for each api > gives further details and considerations. SQLite does the right thing > for multi-threaded applications and many use it that way - you are not > the first. > > http://www.sqlite.org/threadsafe.html > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (GNU/Linux) > > iEYEARECAAYFAkpX1ikACgkQmOOfHg372QQUyACgugrjN65qDEwEnn1c3Rkpd926 > kHgAnjrXLUd620KE6FbIPPiuKR7WpJFH > =wcje > -END PGP SIGNATURE- > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] range enclosing a number
Jay, you're pretty much mistaken: > I'm pretty sure you don't want to do it this way. What this does is > gather every row that meets the WHERE condition and then runs a max() > or min() aggregation function across all of those rows. That means > that even if the column "number" has an index on it, between these > two statements you're going to end up scanning the whole table. All database engines optimize queries which ask for min/max on indexed column with condition including only <, > or = on this very column. And SQLite is among these too: > rm test.db > ( echo "create table t (i integer); begin;"; for ((i = 0;i<1000;++i)); do > echo "insert into t values ($i);"; done; echo "end; create index t_i on t > (i);" ) | sqlite3 test.db > # First force the full table scan > time sqlite3 test.db "select count(*) from t where i * i < 500;" 223607 real0m1.610s user0m1.469s sys 0m0.125s > # now using index > time sqlite3 test.db "select * from t where i < 500 order by i desc limit > 1;" 499 real0m0.043s user0m0.001s sys 0m0.005s > time sqlite3 test.db "select max(i) from t where i < 500;" 499 real0m0.005s user0m0.001s sys 0m0.001s As you see using max() works better than "order by ... limit 1". But of course your other point is true - if you want some other data from table along with min/max value, you need to make additional select in case of using min/max. Pavel On Mon, Jul 13, 2009 at 9:44 PM, Jay A. Kreibich wrote: > On Mon, Jul 13, 2009 at 07:40:48PM -0400, Wes Freeman scratched on the wall: >> Yeah, sorry about that. In two statements: >> >> select max(number) from table where number < ? >> select min(number) from table where number > ? > > I'm pretty sure you don't want to do it this way. What this does is > gather every row that meets the WHERE condition and then runs a max() > or min() aggregation function across all of those rows. That means > that even if the column "number" has an index on it, between these > two statements you're going to end up scanning the whole table. > > You also have the problem that you can't return the rest of the row. > The min() and max() functions will extract the right answer, but > something like this will not: > > SELECT min(number), other_column, FROM table WHERE number > ? > > In that case, it is VERY likely that the value of "other_column" will > not come from the same row as "min(number)". > > For example: > > sqlite> create table t ( i integer, s char ); > sqlite> insert into t values (1, 'a'); > sqlite> insert into t values (2, 'b'); > sqlite> insert into t values (3, 'c'); > sqlite> select min(i), s from t; > 1|c > sqlite> > > This is because the min() and max() aggregations imply a GROUP BY. > Since none is given, the whole result is grouped. That works fine > for min() and max() and gives you the right answer, but any other > column you specify is simply going to return the value for the last > row processed. That's why the above example returns 'c' for the > second column. > > >> >>> select * from table where number <= ? order by number desc limit 1; >> >>> select * from table where number>= ? order by number asc limit 1; > > This is the best solution, especially if the "number" column has an > index on it. In that case the correct row can be extracted directly > from the next and results are nearly instant, no matter how large the > table is. > > As others have pointed out, you do, of course, want to use < and >, > and not <= and >=. > > -j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Our opponent is an alien starship packed with atomic bombs. We have > a protractor." "I'll go home and see if I can scrounge up a ruler > and a piece of string." --from Anathem by Neal Stephenson > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] range enclosing a number
Not to continue argument with Jay but just to express my opinion in comparison: > The ORDER/LIMIT approach is much more resilient to changes, however, > and should more or less behave the same no matter what you do to the > rest of the query. Seriously, I don't believe this. There's no way to change the query so that min/max will scan all table and order/limit will take just value from index. They both will take the same approach in all cases (except of course old SQLite which I fortunately didn't work with :) ). > The ORDER/LIMIT approach is, arguable, less graceful, but > it is also (IMHO) a lot easier to break down into logical blocks that > a newbie can follow and understand both the design and intent-- even > if the query is a bit strung out. This is where my opinion is exactly opposite: query with min/max is more readable by newbie just getting introduced to the code - it clearly states what requester is trying to do: "get minimum among records with this condition". Order/limit approach requires more thinking before you clearly understand what was the intention behind the query - I've struggled myself trying to understand which sign (< or >) should go with which order by (desc or asc). But... Here are opinions and they have already become an off-topic because the OP's case looks like more complicated and couldn't be solved by simple min/max. And in response to Bogdan's letter: > Adding the second column as in:select max(ticks),time from time_pair where > ticks <= ?; > seems to work, although I don't understand the GROUP BY comments. The essence of "GROUP BY comments" is that if you write query this way then what is returned in time column is implementation dependent (not all sql engines even support this syntax) and you better think that in this case value in time column is completely arbitrary and unrelated to actual data in the table. So if you want to return both columns you should use either order/limit approach or already mentioned "select * ... where ticks = (select max(ticks) ...)" approach. Pavel On Tue, Jul 14, 2009 at 12:35 AM, Jay A. Kreibich wrote: > On Mon, Jul 13, 2009 at 10:33:00PM -0400, Pavel Ivanov scratched on the wall: >> Jay, you're pretty much mistaken: >> >> > I'm pretty sure you don't want to do it this way. What this does is >> > gather every row that meets the WHERE condition and then runs a max() >> > or min() aggregation function across all of those rows. That means >> > that even if the column "number" has an index on it, between these >> > two statements you're going to end up scanning the whole table. >> >> All database engines optimize queries which ask for min/max on indexed >> column with condition including only <, > or = on this very column. >> And SQLite is among these too: > > > Not "all." This type of optimization is actually a fairly new > addition to SQLite (considering the product lifetime) and first > appeared in 3.5.5, which was released in early 2008. > > > And I'd still go with ORDER/LIMIT. Here's why: > > > For my tests I just used the default build under the current version > of Mac OS X, which is a bit old (3.4). Under that build, the > ORDER/LIMIT is clearly faster, as this is before the optimization > existed: > > (using the same test set you did) > > Full scan, 3.4: > - > real 0m5.99s > user 0m4.73s > sys 0m0.84s > > Using ORDER/LIMIT, 3.4: > - > real 0m0.00s > user 0m0.01s > sys 0m0.00s > > Using min/max, 3.4: > - > real 0m5.97s > user 0m2.94s > sys 0m0.38s > > In this case, it is clear that min/max are NOT integrated into the > optimizer, and requires half a table scan, just as I stated. > > I also have a build of the current 3.6.16 around, and in that case, > the numbers are better: > > Using ORDER/LIMIT, 3.6.16 > - > real 0m0.12s > user 0m0.01s > sys 0m0.03s > > Using min/max, 3.6.16 > - > real 0m0.04s > user 0m0.01s > sys 0m0.03s > > This clearly shows that the optimization does exist, and that for > this very basic case my assumptions were incorrect. > > With the current 3.6.16 build, using min/max seems a tad faster-- but > only in "real" time. In terms of user/sys times, the results shown > here (and you're own numbers, which were 0.043/0.001/0.005 and > 0.005/0.001/0.001) were pretty typical (i.e. very very close). > That might just be an I/O fluke. We're getting small enough that > to really say anything definite requires better profil
Re: [sqlite] Optimizing insert or replace speed
I believe your choice of query is not good enough. Try this one: insert or replace into main.masterlist select d.* from delta.masterlist d left outer join main.masterlist M on d.sn = M.sn where d.write_out_ok=0 and d.record_updatetime >= ifnull(M.record_updatetime, '') Pavel On Tue, Jul 14, 2009 at 12:54 PM, Adam DeVita wrote: > Good day, > > Could someone explain where I'm going wrong with this? > > I've identified the following query as a bottle neck in a utility I've > written. > > insert or replace into main.masterlist select * from delta.masterlist d > where d.write_out_ok=0 and > d.sn not in(select M.sn from main.masterlist M where M.record_updatetime > > d.record_updatetime) > > The purpose is to import a data from a remotely created change file, with > only new/newer records. (Due to the fact that the subject of the data is > shipping / receiving product serial numbers and that data moves faster than > product there is no way independent nodes can create a change to a record at > the same time. Also, deleting is not allowed.) > > The change file is attached as 'delta' > > The structure of masterlist in the main database is: > sqlite> .schema masterlist > CREATE TABLE MasterList (SN int primary key not null, TypeID int default 0 > references Product_type_dictionary(TypeID) , ConstructionDate text, MFGID > int default 0 references MFG_dictionary (MFGID), Init_cust_ship_date text, > record_updatetime text default "2000.00.00.00", write_out_ok int default 0); > > CREATE INDEX IDX_MasterList on MasterList (write_out_ok, MFGID, TypeID, SN); > > main.masterlist has 36,000 records > deltas.masterlist has 9,000 records > > Notes about fields: > write_out_ok is a flag indicating that the record has been imported. States > are 1 or 0. > MFGID is a manufacturer, about 4 different ints can be used. > TypeID is a product Type, about 7 different types, > > The index is ordered by cardinality, and all int. > record_updatetime is the modified date & time GMT (UTC), > .mm.dd.hh.MM.ss > > > > Experimenting with indexes on the delta file with > No indexes: > 7 min 22s > > CREATE INDEX IDX_MasterList on MasterList ( SN); > 14min 52s > > CREATE INDEX IDX_MasterList on MasterList (write_out_ok, MFGID, TypeID, SN); > 20 min, 07s > > --- > Dropped indexes on both main and delta. > ~20 min. > - > > Is the real problem a poor choice of index in main? > > > regards, > Adam > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Protect against SQL injection inside of the database?
Does Tcl supports binding of parameters to prepared statement?
If yes then do just that and you will not need to do any "quotes" and
think of any "ifs".
Pavel
On Thu, Jul 16, 2009 at 3:49 AM, Fredrik Karlsson wrote:
> Dear list,
>
> Sorry for jumping onto the list mainly to ask a question, but it is an
> imporant one, and I have failed to find the answer on Google.
> I am developing a prototype of an application in Tcl using sqlite as
> the backend database. Now, I know that I will be dealing with quite
> naïve users, who will not think that "!' and simialar characters are
> evil and potentially dangerous in a SQL database context. So, now I
> need to make sure that I am taking all the precautions I can to
> protect the database from evil / naïve users, and since parts of the
> application may be ported to C for speed later, I would prefer as much
> of it to happen in the SQL queries themselves, in order to make sure
> that the behaviour stays constant when porting.
>
> My currrent strategy is to use a combination of quote() and trim() (as
> blank space at the ends of a string is not important in my
> application). So, for each string value I get from the user, I do
> something similar to
>
> set out [format {select * from X where label == quote(trim("%s")) and
> id > %d } $myStringValue $compId ]
>
> (Please ignore the Tcl part if you are not familiar with it.. format
> is basically (almost) sprintf in a new name )
>
> So, my questions are now:
>
> 1) Can I feel safe that the string value is now "safe" (to some
> degree) regarding SQL injection?
> 2) Have I done something that will prevent me from matching values I
> really want to match by altering the original string value?
> 3) Is the integer value reasonably secure, or shouls something be done
> for that too (and then, what?)
>
> Sorry for these questions, but I would rather dot all the i:s before
> moving on in the application development. I have seen before how
> creative naïve users can be when it comes to making applications crash
> due to unforseen actions. :-)
>
> Of course, any input in this would be greatly appreciated.
>
> /Fredrik
>
> --
> "Life is like a trumpet - if you don't put anything into it, you don't
> get anything out of it."
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance issue on records retrieval :: 100, 000 records
> Suppose, if I don't have index, is this the desired behavior of Sqlite > to take this much time to fetch just 10 records or am I missing > something here. You're missing that SQLite have to fetch all records satisfying your condition into memory storage, sort all these records in memory and then pick 10 first out of them. So in this particular case it could be expected. And I think index on column b should speed it up. Pavel On Thu, Jul 16, 2009 at 1:22 PM, MADHAVAN VINOD wrote: > > Hello Michal, > > Thanks for the reply. Please see my comments inline. > >>>if you always have condition a=1 (or something similar which uses = > for >>>comparison) you should have index which starts with this field. > > The possible values for this field are 1/2. And mostly all the records > have the value 1. Hence I do not see any improvement in the query even > when I created index on this column (a). > > > Moreover, I tried simplifying the WHERE clause to have just "a=1 AND b < > c AND d=0" and ORDER BY clause to have just "b ASC". > > The execution of this query itself took me around 3 seconds. > > Suppose, if I don't have index, is this the desired behavior of Sqlite > to take this much time to fetch just 10 records or am I missing > something here. > > > Best Regards, > Vinod N.M. > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michal Seliga > Sent: Thursday, July 16, 2009 8:37 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Performance issue on records retrieval :: 100,000 > records > > > > MADHAVAN VINOD wrote: >> >> 5) No INDEX created. >> >> The retrieval logic is such that to retrieve the oldest 10 records > along >> with some additional constraints (say a, b and c are columns and the >> constraints are like a=1 AND b < c). >> >> >> >> So my WHERE clause is like "CurrTime <= ExpireTime AND CurrTime >= >> NextProcessingTime AND a=1 AND b < c" >> >> And my ORDER BY clause is " CurrTime - NextProcessingTime DESC, b ASC" >> >> >> >> > > > you need index for this, otherwise lookup goes through whole table > question is what index would help you the most. > > now i am not sure if i understood you correctly, are ExpireTime and > NextProcessingTime database fields? if yes, then in addition you should > have at > the end of index columns one of ExpireTime or NextProcessingTime, you > should > choose one which can help you more (one which will help database engine > to limit > row count the most) > > so for situation you wrote i would recommend to have one of indices: > create index ... on ...(a,ExpireTime) > or > > create index ... on ...(a,NextProcessingTime ) > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Heirarchical queries question
Maybe this: select childs.Path, count(*) from Directory childs, Directory hierarchy where childs.ParentDirID = ? and hierarchy.Path like childs.Path || '%' group by childs.Path You should have indexes on ParentDirID and on Path to make this query somewhat effective. And you shouldn't have '%' and '_' signs in the Path (or add another column where will be some modification of Path suitable for this query). Pavel On Fri, Jul 17, 2009 at 11:39 AM, Doug wrote: > I'm trying to get my head around doing hierarchies in SQL. I've been > Googling and it appears Oracle and MS SQL have some extensions to help, but > I'm trying to figure out what can be done with 'plain' SQL. > > > > Imagine a directory table: > > CREATE TABLE IF NOT EXISTS Directory > > ( > > DirID INTEGER, > > Path TEXT, > > ParentDirID INTEGER > > ); > > > > and some data that represents this table structure: > > / > > /users > > /users/doug > > /users/brett > > /users/brett/work > > /users/brett/research > > /users/brett/research/SQL > > > > INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (1, '/', 0); > > INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (2, '/users', 1); > > INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (3, '/users/doug', > 2); > > INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (4, '/users/brett', > 2); > > INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (5, > '/users/brett/work', 4); > > INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (6, > '/users/brett/research', 4); > > INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (7, > '/users/brett/research/SQL', 6); > > > > Assuming I have /users (or the DirID of 2), is there a query that can return > the number of subdirectories each child directory has? Ie an output of: > > /users/doug 0 > > /users/brett 3 > > > > (or if the child was counted > > /users/doug 1 > > /users/brett 4 > > ) > > > > I suppose I could manually grab all entries where ParentDirID=2 (ie the > /users/doug and /users/brett) and then for each of those run a query: > SELECT COUNT(*) FROM Directory WHERE Path LIKE '/users/doug%'; > > SELECT COUNT(*) FROM Directory WHERE Path LIKE '/users/brett%'; > > > > At least that's an algorithm where the only input is '/users', but > ultimately I'd like a SELECT statement where the only input is '/users'. > > > > But is there any way that SQL can accomplish that without needing to > iterate? I keep thinking a GROUP BY that used LIKE instead of = might get > me closer, but as far as I know that's not an option anyway (I don't want to > use a custom function if possible - trying to end up with portable SQL as > much as possible). > > > > I'm looking forward to see what insight you guys have. (This list always > impresses) > > > > Thanks > > Doug > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Heirarchical queries question
Look here http://www.sqlite.org/lang_expr.html in section "The LIKE
and GLOB operators".
---
The operand to the right contains the pattern, the left hand operand
contains the string to match against the pattern. A percent symbol
("%") in the pattern matches any sequence of zero or more characters
in the string. An underscore ("_") in the pattern matches any single
character in the string.
---
Pavel
On Fri, Jul 17, 2009 at 1:00 PM, Doug wrote:
> Wow Pavel, that's a cool approach.
>
> I understand the issue about having % in the path (which is a problem I need
> to work around), but what is special about '_' ?
>
> Thanks
> Doug
>
>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>> boun...@sqlite.org] On Behalf Of Pavel Ivanov
>> Sent: Friday, July 17, 2009 10:53 AM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] Heirarchical queries question
>>
>> Maybe this:
>>
>> select childs.Path, count(*)
>> from Directory childs, Directory hierarchy
>> where childs.ParentDirID = ?
>> and hierarchy.Path like childs.Path || '%'
>> group by childs.Path
>>
>> You should have indexes on ParentDirID and on Path to make this query
>> somewhat effective. And you shouldn't have '%' and '_' signs in the
>> Path (or add another column where will be some modification of Path
>> suitable for this query).
>>
>> Pavel
>>
>> On Fri, Jul 17, 2009 at 11:39 AM, Doug wrote:
>> > I'm trying to get my head around doing hierarchies in SQL. I've been
>> > Googling and it appears Oracle and MS SQL have some extensions to
>> help, but
>> > I'm trying to figure out what can be done with 'plain' SQL.
>> >
>> >
>> >
>> > Imagine a directory table:
>> >
>> > CREATE TABLE IF NOT EXISTS Directory
>> >
>> > (
>> >
>> > DirID INTEGER,
>> >
>> > Path TEXT,
>> >
>> > ParentDirID INTEGER
>> >
>> > );
>> >
>> >
>> >
>> > and some data that represents this table structure:
>> >
>> > /
>> >
>> > /users
>> >
>> > /users/doug
>> >
>> > /users/brett
>> >
>> > /users/brett/work
>> >
>> > /users/brett/research
>> >
>> > /users/brett/research/SQL
>> >
>> >
>> >
>> > INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (1, '/', 0);
>> >
>> > INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (2, '/users',
>> 1);
>> >
>> > INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (3,
>> '/users/doug',
>> > 2);
>> >
>> > INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (4,
>> '/users/brett',
>> > 2);
>> >
>> > INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (5,
>> > '/users/brett/work', 4);
>> >
>> > INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (6,
>> > '/users/brett/research', 4);
>> >
>> > INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (7,
>> > '/users/brett/research/SQL', 6);
>> >
>> >
>> >
>> > Assuming I have /users (or the DirID of 2), is there a query that can
>> return
>> > the number of subdirectories each child directory has? Ie an output
>> of:
>> >
>> > /users/doug 0
>> >
>> > /users/brett 3
>> >
>> >
>> >
>> > (or if the child was counted
>> >
>> > /users/doug 1
>> >
>> > /users/brett 4
>> >
>> > )
>> >
>> >
>> >
>> > I suppose I could manually grab all entries where ParentDirID=2 (ie
>> the
>> > /users/doug and /users/brett) and then for each of those run a query:
>> > SELECT COUNT(*) FROM Directory WHERE Path LIKE '/users/doug%';
>> >
>> > SELECT COUNT(*) FROM Directory WHERE Path LIKE '/users/brett%';
>> >
>> >
>> >
>> > At least that's an algorithm where the only input is '/users', but
>> > ultimately I'd like a SELECT statement where the only input is
>> '/users'.
>> >
>> >
>> >
>> > But is there any way that SQL can accomplish that without needing to
>> > iterate? I keep thinking a GROUP BY that used LIKE instead of =
>> might get
>> > me closer, but as far as I know that's not an option anyway (I don't
>> want to
>> > use a custom function if possible - trying to end up with portable
>> SQL as
>> > much as possible).
>> >
>> >
>> >
>> > I'm looking forward to see what insight you guys have. (This list
>> always
>> > impresses)
>> >
>> >
>> >
>> > Thanks
>> >
>> > Doug
>> >
>> >
>> >
>> >
>> >
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Some SQL statements are not executing
Sounds like you're running several instances of your application in
several different processes simultaneously on the same database. Are
you sure you don't do that?
Pavel
On Mon, Jul 20, 2009 at 11:08 AM, Gary
Verhaegen wrote:
> Hi everybody,
>
> I just subscribed and this is my first post - I hope I am not making
> any deadly mistake by posting about a problem I have with SQLite. As
> the title suggests, I have some trouble with SQL statements randomly
> not executing. Here are the details.
>
> First, the machine. It is running a fully updated Arch Linux system, with:
> $uname -a -> Linux iridia-pc20 2.6.30-ARCH #1 SMP PREEMPT Sat Jul 4
> 02:24:43 CEST 2009 x86_64 Pentium(R) Dual-Core CPU E5200 @ 2.50GHz
> GenuineIntel GNU/Linux
> $sqlite3 --version -> gives 3.6.15
> $gcc --version -> gcc (GCC) 4.4.0 20090630 (prerelease)
> Copyright (C) 2009 Free Software Foundation, Inc.
> This is free software; see the source for copying conditions. There is NO
> warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
> $ruby --version -> ruby 1.8.7 (2009-06-12 patchlevel 174) [x86_64-linux]
> $gem --version -> 1.3.3
> $gem list --local -> sqlite3-ruby (1.2.4)
>
> As Arch Linux is essentially a binary distribution, I do not know of
> the compilation flags, though I would gladly post them should anyone
> tell me how I can get to know them.
>
> I am trying to implement some kind of graph algorithm using the
> database, because almost all the computations involved are easy and
> natural to describe as SQL statements. I first implemented the
> algorithm in Ruby, using the sqlite3 gem, and it works fine, but is a
> bit slow. Furthermore, once I will be confident I have rooted out all
> the bugs, I intend to run this algorithm in parallel on multiple
> processors. I have thus tried to translate the algorithm to C (for
> speed and OS-level multithreading, though the version of the program
> I'm speaking about here is a direct translation of the ruby code, with
> no threading added yet), which seemed quite easy, but now yields this
> weird result. I have narrowed the problem to the few following lines :
>
>
> #define DEBUG
>
> int db_execute(sqlite3 *db, char stmt[STR_LEN]) {
> DEBUG PRINT("\nentering db_execute with statement %s.", stmt);
> int ret=0;
> sqlite3_stmt *s;
> int err = sqlite3_prepare_v2(db, stmt, -1,&s,0);
> DEBUG if(err != SQLITE_OK) {
> DEBUG PRINT("\nSQLite error %d.\n", err);
> DEBUG PRINT(stmt);
> DEBUG exit(err);
> DEBUG }
> if(sqlite3_step(s) == SQLITE_ROW)
> ret = sqlite3_column_int(s,0);
> sqlite3_finalize(s);
> DEBUG PRINT("\nleaving db_execute with value %d\n",ret);
> DEBUG system("sqlite3 tmpc/db.sqlite3 \"SELECT * FROM slinks;\" >> temp");
> return ret;
> }
> void remove_link(sqlite3 *db, int current) {
> DEBUG PRINT("\nentering remove_link");
> db_execute(db, "DELETE FROM slinks WHERE subgraph = -2;");
> [...]
> }
>
> And, in the file "temp", what I get at some point of the execution is
>
> entering remove_link
> entering db_execute with statement DELETE FROM slinks WHERE subgraph = -2;.
> leaving db_execute with value 0
> 4146406|3032044|-2|0|14688
> 4169484|3032044|-1|0|15741
> 4281995|3032044|-1|0|21582
> 4503874|3032044|-1|0|32113
> 451|3032044|-1|0|32975
> 4558479|3032044|-1|0|34754
> 4589986|3032044|-1|0|36247
> 4626358|3032044|-1|0|37945
> 4749478|3032044|-1|0|43661
> 4776954|3032044|-1|0|44842
> 4778599|3032044|-1|0|44923
> 4839063|3032044|-1|0|47672
> 4950393|3032044|-1|0|52562
> 6274047|3032044|-1|0|94705
> 6406621|3032044|-1|0|97782
>
> where the third column is subgraph. Of course, the next steps rely on
> the fact that the DELETE statement should have deleted the first row.
> Here is the schema of this table :
> CREATE TABLE slinks (linking INTEGER, linked INTEGER, subgraph
> INTEGER, marked INTEGER, subid INTEGER PRIMARY KEY);
>
> Now, this code is called a lot of times in the course of the
> execution, and it usually works. It seems to randomly crash, which is
> quite puzzling to me as this application is not multi-threaded and
> does not use any manual memory management (no mallocs and no frees;
> this means as far as I can tell that it should not be a memory
> allocation problem nor a race conditions one). I really can't see
> where this randomness can come from.
>
> It is not always this DELETE statement which seems to be skipped. On
> another run of the program, I got the following output :
>
> entering db_execute with statement UPDATE slinks SET subgraph = -1
> WHERE (linked = 3029162 OR linking = 3029162) AND subgraph = 83;.
> leaving db_execute with value 0
> 3859144|3029162|-2|0|69
> 4011107|3029162|83|0|7895
> 4268582|3029162|83|0|20898
> 4402764|3029162|83|0|27330
> 4934254|3029162|83|0|51846
>
> which is as clearly bogus as the first example, as all these "83"
> should have been replaced by "-1". I also get the same kind of
> problems on anothe
Re: [sqlite] Subqueries
Although your query doesn't make sense without any explanation of what did you mean and how it is supposed too work I can provide you a couple of observations: 1) Do you realize that select * doesn't make any sense in this query? The only meaningful field will be ac.AcNum, all others will be essentially trashed? 2) Looking at your query I can assume that none of your tables contain column auNum. But nonetheless you're having "having auNum = 0" at the most inner query. I guess it's not what you supposed to write there. If these are not your problem then you better explain what do you want to obtain from this query and what does it return to you. Pavel On Tue, Jul 21, 2009 at 9:14 AM, Hubboo wrote: > > Hi, > > I am doing an assignment using SQLite and was wondering if someone could > tell me why this doesn't work and maybe offer some help please? > > select *, count(distinct au.acNum) as auNum, count(int.acNum) as intNum > from academic ac > LEFT OUTER JOIN author au on ac.AcNum = au.AcNum > LEFT OUTER JOIN interest int on int.AcNum = ac.AcNum > group by ac.AcNum > having count(distinct au.acNum) = > > (select Max(int.acNumCount) > from (select count(int.acNum) as int.AcNumCount > from academic ac > LEFT OUTER JOIN author au on ac.AcNum = au.AcNum > LEFT OUTER JOIN interest int on int.AcNum = ac.AcNum > group by ac.AcNum > having auNum = 0)) > -- > View this message in context: > http://www.nabble.com/Subqueries-tp24587437p24587437.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subqueries
Then I guess your initial query was almost correct. Try to change it like this: select ac.AcNum, count(au.acNum) as auNum, count(int.acNum) as intNum from academic ac LEFT OUTER JOIN author au on ac.AcNum = au.AcNum LEFT OUTER JOIN interest int on int.AcNum = ac.AcNum group by ac.AcNum having count(au.acNum) = 0 and count(int.acNum) = (select max(acNumCount) from (select count(*) as int.AcNumCount from interest int group by int.AcNum)) I assumed that table interest has at least 1 row for at least 1 academic. Pavel On Tue, Jul 21, 2009 at 9:49 AM, Hubboo wrote: > > Thanks for replying > > > OK we have several tables for our assignment and for this particular > question we are asked > > Q. Among the academics who have no papers, who has the greatest number of > interests.. > > I used the * just return all attributes to start with. > > When I use > > SELECT * , count( Au.AcNum ) AS num, count( i.AcNum ) AS num2 > FROM academic a > LEFT OUTER JOIN author Au ON a.AcNum = Au.AcNum > LEFT OUTER JOIN interest i ON i.AcNUm = a.AcNum > GROUP BY A.AcNum > HAVING num =0 > > This gives me the academics with 0, that part seems to be working OK, I am > struggling on how to count the second part of the question.. > > Database looks like > > Department(DeptNum, Descrip, Instname, DeptName, State, Postcode) > Academic(AcNum, DeptNum, FamName, GiveName, Initials, Title) > Paper(PaNum, Title) > Author(PaNum, AcNum) > Field(FieldNum, ID, Title) > Interest(FieldNum, AcNum, Descrip) > > -- > View this message in context: > http://www.nabble.com/Subqueries-tp24587437p24588040.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subqueries
Sorry, mis-looked: "as int.AcNumCount" should be "as AcNumCount". Don't see other syntax errors. Pavel On Tue, Jul 21, 2009 at 10:21 AM, Hubboo wrote: > > Thanks. Returns an error > > Likely SQL syntax error: select ac.AcNum, count(au.acNum) as auNum, > count(int.acNum) as intNum > from academic ac > LEFT OUTER JOIN author au on ac.AcNum = au.AcNum > LEFT OUTER JOIN interest int on int.AcNum = ac.AcNum > group by ac.AcNum > having count(au.acNum) = 0 > and count(int.acNum) = (select max(acNumCount) from (select count(*) as > int.AcNumCount from interest int group by int.AcNum)) > > [ near " ": syntax error ] > Exception Name: NS_ERROR_FAILURE > Exception Message: Component returned failure code: 0x80004005 > (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement] > > -- > View this message in context: > http://www.nabble.com/Subqueries-tp24587437p24588626.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory leak on inserting from sqlite c/c++ interfaces
> If I remove or comment out the
> three lines with //* in the code above, I get no memory leaks.
So basically you're saying that if you don't insert any data into your
database and thus effectively don't do with your database anything and
thus SQLite don't have to cache anything from database then you
observe no memory leaks, right? Is it by any chance a growing database
cache is what you see? What if you execute at the very beginning of
your application this:
pragma cache_size = 1;
Pavel
On Tue, Jul 21, 2009 at 7:01 PM, Zachary Turner wrote:
> Hello, I'm a bit new to sqlite, I wonder if someone can advise me here.
>
> I'm using the Sqlite C/C++ interfaces, and I'm trying to do some very
> basic things. Currently I'm just creating a database with 1 table,
> and this table has 1 column of type blob. I then read some data out
> of a file and insert it into the database, where each row will contain
> some number N of bytes from the file, where N may not necessarily be
> the same in each row.
>
> I'm doing this using the following pseudocode:
>
> ---Initialization---
> 1) db = sqlite3_open_v2("C:\\foo.db")
> 2) sqlite3_exec(db, "CREATE TABLE DummyTable (DummyColumn BLOB)")
> 3) insert_query = sqlite3_prepare_v2(db, "INSERT INTO DummyTable
> (DummyColumn) VALUES (?1)")
> 4) commit_query = sqlite3_prepare_v2(db, "commit")
> 5) begin_query = sqlite3_prepare_v2(db, "begin")
>
>
> ---When I want to write a chunk of the file into the database---
> if (!active_transaction_)
> {
> //begin a new transaction
>
> sqlite3_step(begin_query)
> active_transaction_ = true;
> }
>
> //bind the data to the query and execute the query
> sqlite3_bind_blob(insert_query, data, length)
> sqlite3_step(insert_query) // *
> sqlite3_clear_bindings(insert_query) // *
> sqlite3_reset(insert_query) // *
>
> //128 is a made up number, just for the sake of illustration
> if (++count >= 128)
> {
> //commit the transaction
>
> sqlite3_step(commit_query)
> active_transaction_ = false;
> }
>
>
> When I run this code for a while my memory usage grows extremely
> quickly, and I don't understand why. If I remove or comment out the
> three lines with //* in the code above, I get no memory leaks. If it
> makes a difference when I call sqlite3_bind_blob I'm using
> SQLITE_TRANSIENT for the final parameter, but my understanding is that
> this is supposed to automatically free the memory when it's no longer
> needed. Furthermore, the bind itself isn't what's causing the
> problem, because if I leave the bind in and only comment out the
> insert, I don't get the leak anymore.
>
>
> Am I using the interfaces incorrectly or is perhaps something else
> going on that I need to be aware of?
>
> Thanks
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determine if UPDATE has worked.
Did you look into this: http://www.sqlite.org/c3ref/changes.html ?
Pavel
On Tue, Jul 21, 2009 at 7:57 PM, Steve Martin wrote:
> Hi List,
>
> I am new to sqlite and having trouble determining if an update has worked.
>
> I am using the C/C++ API.
>
> For an update, when sqlite3_step is executed it returns SQLITE_DONE when
> a record is updated or if a record does not exist. I have not found an
> answer by reading and searching the documentation and searching the
> internet
>
> This is the same for the command line tool.
>
> Using code based on "http://www.sqlite.org/cvstrac/wiki?p=SimpleCode";
>
> $ fred testdb "create table bob(p1 text PRIMARY KEY, p2 text)"
> SQLITE_DONE: 101 : unknown error
> $ fred testdb "insert into bob (p1, p2) values('foo', 'sue')"
> SQLITE_DONE: 101 : unknown error
> $ fred testdb "insert into bob (p1, p2) values('foo', 'sue')"
> Error: 19 : constraint failed
> $ fred testdb "update bob set p2 = 'fred' where p1 = 'foo'"
> SQLITE_DONE: 101 : unknown error <-- update ok
> $ fred testdb "update bob set p2 = 'fred' where p1 = 'fo1'"
> SQLITE_DONE: 101 : unknown error <-- update fail
> $ fred testdb "select count(*) from bob"
> count(*) = 1
> SQLITE_DONE: 101 : unknown error
> $ fred testdb "select * from bob"
> p1 = foo
> p2 = fred
> SQLITE_DONE: 101 : unknown error
> $
>
> When using the command line tool.
> SQLite version 3.6.16
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table bob(p1 text PRIMARY KEY, p2 text);
> sqlite> .header on
> sqlite> .show
> echo: off
> explain: off
> headers: on
> mode: list
> nullvalue: ""
> output: stdout
> separator: "|"
> width:
> sqlite> update bob set p2 = 'fred' where p1 = 'foo';
> sqlite> insert into bob (p1, p2, p3) values('foo', 'sue', 'todd');
> SQL error: table bob has no column named p3
> sqlite> insert into bob (p1, p2) values('foo', 'sue');
> sqlite> select * from bob;
> p1|p2
> foo|sue
> sqlite> update bob set p2 = 'fred' where p1 = 'foo';
> sqlite> select * from bob;
> p1|p2
> foo|fred
> sqlite> update bob set p2 = 'fred' where p1 = 'fo1';
> sqlite>
>
>
> Thanks
> Steve
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory leak on inserting from sqlite c/c++ interfaces
SQLite synchronizes with disk during every commit (either issued by yourself or automatic) at least 2 times (I don't know exact number). So it's quite natural that it spends most of the time in winSync(). But I still didn't understand from your explanation how exactly your application works and whether it's SQLite uses memory or your application does. BTW, how do you measure memory usage and how do you see leakage? What does sqlite3_memory_used() returns for you? Pavel On Wed, Jul 22, 2009 at 11:26 AM, Zachary Turner wrote: > On Tue, Jul 21, 2009 at 6:45 PM, Pavel Ivanov wrote: >>> If I remove or comment out the >>> three lines with //* in the code above, I get no memory leaks. >> >> So basically you're saying that if you don't insert any data into your >> database and thus effectively don't do with your database anything and >> thus SQLite don't have to cache anything from database then you >> observe no memory leaks, right? Is it by any chance a growing database >> cache is what you see? What if you execute at the very beginning of >> your application this: >> pragma cache_size = 1; >> >> Pavel > > Thanks for your suggestion. I didn't have too much luck with it > unfortunately, but I did learn a few more things about the problem. > Just to make sure I understood you though, after creating the database > with sqlite3_open_v2 I ran the command > > sqlite3_exec(db_, "pragma cache_size=1;"); > > The return value was successful so I assume it worked. > > This doesn't appear to change the allocation behavior. I feel like it > might be related to me issuing INSERT queries too fast for sqlite to > deal with. This is asynchronous (not using the experimental Sqlite > asynchronous vfs but a completely different async model I wrote > myself) and the thread that issues inserts to sqlite is different than > the thread that reads data from the file. > > I changed the code a little so that it permanently stops reading more > data from the file after some fixed amount (I chose about 500MB) but > continues writing until all data has been committed to the database. > when I do this, I notice that after I stop my reads, it takes a *long > time* before all the writes are finished in sqlite. 99% of this time > is spent in the winSync() function in the sqlite source code > constantly calling FlushFileBuffers(). > > Upon reflection it makes sense that this would happen in an > asynchronous environment, but I thought that after a certain threshold > running an insert statement on a non-in-memory table would generate a > sync and simply block my sqlite background thread until there was no > more buffered data. > > In my async model I post messages between threads, so for example the > main thread posts a message to the sqlite thread saying "write this > data", which runs the pseudocode I posted in the OP, and after that's > done it posts a message to the main thread saying "I'm done writing > that data". the main thread only issues new read requests as a result > of getting that message, so if sqlite were syncing every so often then > I could guarantee that I wouldn't be in this situation because the > main thread wouldn't be able to issue any more reads until the sync > was done. > > > Am I misunderstanding sqlite's filesystem sync'ing algorithm? > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory leak on inserting from sqlite c/c++ interfaces
> Assuming I don't do any manual commits, what does sqlite do with the > data that has yet to be committed? If you don't do commits (and "begins") of transactions by yourself then SQLite does that automatically after each executed statement. So when sqlite3_step returns you can be sure that everything is committed and everything is on disk. SQLite doesn't do any write-through caching. And as your transactions are small in volume then journal size is also always small. > Unfortunately I don't have access > to memory leak detection tools, otherwise that would obviously be > ideal. Again, try to call sqlite3_memory_used() several times during work of your application. What does it say to you? Pavel On Wed, Jul 22, 2009 at 2:30 PM, Zachary Turner wrote: > On Wed, Jul 22, 2009 at 10:47 AM, Pavel Ivanov wrote: >> SQLite synchronizes with disk during every commit (either issued by >> yourself or automatic) at least 2 times (I don't know exact number). >> So it's quite natural that it spends most of the time in winSync(). >> But I still didn't understand from your explanation how exactly your >> application works and whether it's SQLite uses memory or your >> application does. >> BTW, how do you measure memory usage and how do you see leakage? What >> does sqlite3_memory_used() returns for you? >> >> Pavel > > I was measuring memory usage by just looking at windows task manager. > If I watch it for about 20 seconds, it goes up indefinitely until I > stop reading more data from the file (and thus stop issuing insert > statements), at which point it steadly declines for a while. > > Assuming I don't do any manual commits, what does sqlite do with the > data that has yet to be committed? I thought it would store it in the > journal file, but the journal file always remains consistently very > small (around 8K max), and data gets written to the actual database > file even when I'm not doing commits. > > I have some ideas about the memory consumption problem that turns out > to be related to my own code (I agree it's amazingly complicated, but > it has to be for reasons outside of what we're doing with sqlite). I > will investigate that further and post back if I am able to pinpoint > the issue to sqlite more closely. Unfortunately I don't have access > to memory leak detection tools, otherwise that would obviously be > ideal. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reporting on summary data
> And how can I calculate the value for Action? Just do the same approach with CASE you've already used: Select ID, CASE WHEN d1='X' AND d2 IS NULL THEN 'NEW' WHEN d1 IS NULL AND d2='X' THEN 'DROP' ELSE 'CHANGE' END AS Action, Sum(CASE WHEN d1='X' THEN AMT ELSE 0 END) - Sum(CASE WHEN d2='X' THEN AMT ELSE 0 END) AS Net FROM Source GROUP BY ID ORDER BY 2 DESC; Pavel On Fri, Jul 24, 2009 at 2:17 PM, David Bicking wrote: > I am trying to create a report. The source table can have one or two records > for each given ID. If there is a record with D1='X' but no record with > D2='X', then it is a "NEW" action. If there are both D1 and D2 record, it is > a "CHANGE" and the Net amount is the D1 amount less the D2 amount. If there > is no D1, then it is a "DROP" and is the negative of the D2 amount. > > I can get close, but I have no idea how to calculate the Action words. > > Select * from Source > > ID | D1 | D2 | AMT > 1 | X | | 15 > 2 | X | | 6 > 2 | | X | 7 > 3 | | X | 12 > > Select ID, ??? AS Action, Sum(CASE WHEN d1='X' THEN AMT ELSE 0 END) - > Sum(CASE WHEN d2='X' THEN AMT ELSE 0 END) AS Net FROM Source GROUP BY ID > ORDER BY 2 DESC; > > ID | Action | Net > 1 | NEW | 15 > 3 | DROP | -12 > 2 | CHANGE | -1 > > Is the approach I am taking a "good" approach? And how can I calculate the > value for Action? > > Thanks, > David > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nested transactions
First of all in case you've mentioned it will be not nested
transaction but one transaction including all deletes and it will be
committed when select statement will be finished.
As a second note: it's pretty bad idea to change table which is used
in not yet finished select statement. In SQLite it can lead to
unpredictable behavior. E.g. rows that you've already deleted can
still be selected in subsequent fetches or new rows that you've
inserted would be returned by issued earlier select statement. And
this can lead effectively to infinite loop.
So indeed you better use some memory structures for storing results of
your select first, before changing the table.
Pavel
On Fri, Jul 24, 2009 at 3:12 PM, Guido Ostkamp wrote:
> Hello,
>
> just a short question:
>
> With the current sqlite version, is it possible to have nested
> transactions, e.g.
>
> exec select ... from table
> while (fetch row) {
> if (criteria)
> exec delete from t where ...
> }
>
> which means execute some 'select', fetching the results in a loop and
> inside the loop executing e.g. 'delete' statements on the same table based
> on the data returned by the fetch?
>
> The 'definitive guide to sqlite' book that I have (which is based on an
> now outdated version of sqlite) says it isn't and I have to put the
> results of the select into another temporary table or local storage to
> work around the problem, but I thought I had heard about new support for
> nested transactions.
>
> Thanks for any insight.
>
> Regards
>
> Guido
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reporting on summary data
Oops, sorry! I misunderstood what you need. Try this: select id, case when act_sum = 1 then 'NEW' when act_sum = 0 then 'CHANGE' else 'DROP' end as Action, net from ( select id, sum(case when d1='X' then 1 else -1 end) as act_sum, sum(case when d1='X' then amt else -amt end) as net from Source group by id ) order by 2 desc; Pavel On Fri, Jul 24, 2009 at 3:44 PM, David Bicking wrote: > > >> From: Pavel Ivanov >> >> Just do the same approach with CASE you've already used: >> >> Select ID, >> CASE WHEN d1='X' AND d2 IS NULL THEN 'NEW' >> WHEN d1 IS NULL AND d2='X' THEN 'DROP' >> ELSE 'CHANGE' END AS Action, >> Sum(CASE WHEN d1='X' THEN AMT ELSE 0 END) - Sum(CASE WHEN >> d2='X' THEN >> AMT ELSE 0 END) AS Net >> FROM Source GROUP BY ID ORDER BY 2 DESC; >> >> Pavel >> > > Thanks, unfortunately, the ELSE 'CHANGE' never fires as all records match > either the first or second WHEN clauses. > > Using your query, I got > 1 | NEW | 15 > 2 | DROP | -1 > 3 | DROP | -12 > > And it occurs to me that I misstated the problem slightly. The source is > actually; > > 1 | X | Y | 15 > 2 | X | Z | 6 > 2 | A | X | 7 > 3 | B | X | 12 > > Where A,B,Y and Z are arbitrary values that aren't = 'X'. Probably Y and Z > would be greater than X, and A and B would be less than X, but it isn't > guaranteed. > > David > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fastest way to insert lots of data
Try to add to your SQLite test these steps: 3.5) Execute statement BEGIN (you can do it via prepared statement). 5.5) Say every 10 000 records execute COMMIT and then BEGIN again. Or if you indeed want to measure maximum write speed then skip this step at all. And your words that pragma synchronous didn't have any effect at all show that either you did something wrong or you are working with some weird OS settings preventing respect of data flushing request... Pavel On Fri, Jul 24, 2009 at 5:29 PM, Zachary Turner wrote: > What kind of performance can I expect to get from sqlite inserting > many many rows of blob data into a database? I know this depends on > many factors, but I'm a little puzzled because I have written some > tests that allow me to test various ways of inserting data and as far > as I can tell I've tested almost every combination of pragmas and > sqlite3_config options that might have an effect on performance, and I > cannot find a way to insert any faster than about 20MB / second. On > this same disk, I wrote a similar program to test the speed of writing > sequentially to a file and in this case I was able to get around 75 MB > / second. Here was what I've done in the two scenarios: > > Raw Disk Write Test (75 MB / second) > --- > 1) Open a very large input file (a few GB) from physical disk 1 > 2) Open an output file on physical disk 2 > 2) Read from the input file sequentially in 4KB chunks > 3) For each 4KB chunk, write the chunk to the output file > > > Sqlite Test (20 MB / second) > -- > 1) (Same as above) > 2) Create a sqlite database on physical disk 2 with one table that has > 1 column of type BLOB > 3) Created a prepared statement "INSERT INTO MyTable (ChunkData) values (?1)" > 4) Read from the input file sequentially in 4KB chunks > 5) For each 4KB chunk,use sqlite3_bind_blob with the SQLITE_TRANSIENT > flag, execute the prepared statement, then reset the prepared > statement. > > > In addition, I've tried changing pretty much every sqlite option I can > find, including replacing the sqlite_mem_methods with an extremely > fast allocator using sqlite3_config(SQLITE_CONFIG_MALLOC, ...), > disabling memory stat collection with > sqlite3_config(SQLITE_CONFIG_MEMSTATUS, ...), pragma > default_cache_size, pragma page_size, and pragma synchronous. > > Even pragma synchronous, which I thought would surely increase > performance if I set it to 0, has no effect at all. I also tried > creating the database with SQLITE_OPEN_NOMUTEX and this also had no > effect on performance. 20MB / second seems awfully slow given that I > write almost 4x that fast using regular disk i/o. > > Am I doing something wrong? I was hoping to be able to achieve at > least 40MB / second of throughput. > > Regards, > Zach > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .lib file?
You can take sqlite3.c from amalgamation package and include it into your project. This way SQLite will be included into your application and you won't need any .dll or .lib files. Pavel On Sat, Jul 25, 2009 at 10:41 AM, Paul Claessen wrote: > Greetings, > > > > I'm trying to build and use the short sample C code on the quick start page > (http://www.sqlite.org/quickstart.html) for windows as a > simple win32 console app, using Visual Studio 2008. > > In order to provide linkage to the DLL, I need to link with the .lib file > that is usually created when creating a DLL. Where can I > find that file? > > Or are there other ways to actually build the above mentioned app, without > such a library? > > There is a windows test console app, but I can't find the project files for > it. Does anyone have them, and knows how to build a > simple console app, using the dell, in Visual Studio? > > > > Kind regards, > > > > ~ Paul Claessen > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date Modifier '%w' Does Not Behave as INTEGER
For me it's pretty obvious that strftime() returns text data type
because it returns your date formatted as you like. And SQLite have
never compared text and integer as equal even if they have the same
notation when converted to text:
sqlite> select 1 where '1' = 1;
sqlite> select 1 where '1' = '1';
1
sqlite> select typeof(strftime('%w', date()));
text
sqlite>
Pavel
On Mon, Jul 27, 2009 at 11:20 AM, Bill Welsh wrote:
> I find that I must cast the result of a '%w' modifier to INTEGER to use it
> in a select.
>
> I have a table of TV programs that has title and an integer representing the
> show's start time as unix epoch.
>
> This does not produce any results, though I have shows beginning on Monday:
>
> select title, datetime(start_time,'unixepoch','localtime') from show_list
> where strftime('%w',start_time,'unixepoch','localtime') = 1;
>
> I find if I create a temp table:
>
> create temp table foo as select title, start_time,
> strftime('%w',start_time,'unixepoch','localtime') dow from show_list;
>
> I get an odd schema where there is no data type for the dow column:
>
> .schema foo
>
> sqlite> .schema foo2
> CREATE TABLE foo2(title_long TEXT,start_time INTEGER,dow);
> sqlite>
>
> And that
>
> select * from foo where dow = 1;
>
> still gives me no results.
>
> However, if I
>
> create temp table foo2 as select title, start_time,
> cast(strftime('%w',start_time,'unixepoch','localtime') as integer) dow from
> show_list;
>
>
> I get results from the query for dow = 1 on foo2.
>
>
> Similarly, I can use the cast in the earlier query and get a result set. I
> can also use the un-cast strftime('%w'...) in a group by clause, which gives
> me expected results.
>
> But this all seems odd, given how forgiving SQLite is regarding data types.
> It appears at first blush that because the result of the strftime('%w...)
> has no type, it's not comparing correctly with my INT constant in the
> query.
>
> So the question is: What's going on? Is this expected behavior or a bug?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to disable fsync() in SQLite
Execute pragma synchronous = off; after opening connection to database. More info: http://www.sqlite.org/pragma.html#pragma_synchronous. Pavel On Mon, Jul 27, 2009 at 2:31 PM, W.-H. Gu wrote: > Hi, > > Is there a way to disable fsync() in my SQLite application on Linux? I > saw a discussion of SQLite performance at > http://www.sqlite.org/speed.htmland it has some results with option > 'nosync.' I am wondering if I can do the > same thing as there to see how fsync() effects the performance in my > applicaion. > > Thanks, > > WeiHsin > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] a system for arbitrarily tagging rows -- revisited
First of all you've mentioned schema which is not in agreement slightly with sql you've given. But of course we can guess... Second your sql seems to do slightly different thing from what you're saying. In particular I'm concerned about this sentence: > If the tag is the same as the 'current_tag', add a 'classtype' of > 'tag_a', else 'tag_b' (the effect of the 'classtype' can be seen by > clicking on any of the tags and seeing the color of the relevant tags > change); What sql does is: if tag is among those assigned to pages having also 'current_tag' then add a classtype 'tag_a' else 'tag_b'. But keeping all that in mind if I were you I'd write select in the following way: SELECT CASE WHEN t_cur.tag_id is null THEN 'tag_b' ELSE 'tag_a' END AS classtype, t.tag_id, t.tag_name, count(*) tag_num FROM pages_tags pt_p JOIN pages_tags pt_np on pt_p.page_id = pt_np.page_id AND pt_np.tag_id != 1 JOIN tags t on pt_np.tag_id = t.tag_id LEFT OUTER JOIN ( SELECT DISTINCT pt_pcur.tag_id FROM pages_tags pt_cur JOIN pages_tags pt_pcur on pt_pcur.page_id = pt_cur.page_id WHERE pt_cur.tag_id = ?) t_cur on t_cur.tag_id = t.tag_id WHERE pt_p.tag_id = 1 GROUP BY t.tag_id, t.tag_name ORDER BY t.tag_name It eliminates executing of select for each row and also shows clearly how it's intended to be executed... Pavel On Tue, Jul 28, 2009 at 7:21 PM, P Kishor wrote: > Following up on my recent question about arbitrarily tagging rows in a > table, my website has certain pages that are tagged as 'Photos' and > other tags (http://www.punkish.org/Photos). The table schema is as > follows > > TABLE pages (p_id INTEGER PRIMARY KEY, p_name TEXT); > TABLE tag (t_id INTEGER PRIMARY KEY, t_name TEXT); > TABLE pages_tags (p_id INTEGER, t_id INTEGER); > > Given a parameter ? = 'current_tag' > > Find all the non-'Photos' tags and their Counts for the pages that are > tagged as 'Photos' (tag_id = 1); > If the tag is the same as the 'current_tag', add a 'classtype' of > 'tag_a', else 'tag_b' (the effect of the 'classtype' can be seen by > clicking on any of the tags and seeing the color of the relevant tags > change); > > > SELECT > -- set 'classtype' to 'tag_a' or 'tag_b' > CASE > WHEN t.tag_id IN ( > SELECT DISTINCT tag_id > FROM pages_tags > WHERE page_id IN ( > SELECT p.page_id > FROM pages p JOIN pages_tags pt ON p.page_id = pt.page_id > WHERE pt.tag_id = ? > ) > ) > THEN 'tag_a' > ELSE 'tag_b' > END AS classtype, > t.tag_id, > t.tag_name, > Count(tag_name) AS tag_num > > FROM tags t JOIN pages_tags pt ON t.tag_id = pt.tag_id > > WHERE > -- all tags that are not 'Photos' > t.tag_id != 1 AND > > -- all pages that are tagged as 'Photos' > pt.page_id IN ( > SELECT page_id FROM pages_tags WHERE tag_id = 1 > ) > > GROUP BY classtype, t.tag_id, tag_name > ORDER BY tag_name > > Question: The above works just fine, but seems awfully convoluted, > which could be a result of my thinking too much about it. > Particularly, it seems the two nested SELECTs in the CASE clause would > be executed for each row in the result set. Could I do this more > elegantly? > > > > -- > Puneet Kishor http://www.punkish.org > Carbon Model http://carbonmodel.org > Charter Member, Open Source Geospatial Foundation http://www.osgeo.org > Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor > Nelson Institute, UW-Madison http://www.nelson.wisc.edu > --- > Assertions are politics; backing up assertions with evidence is science > === > Sent from Madison, WI, United States > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] a system for arbitrarily tagging rows -- revisited
> I can't understand what that != 1 constraint is doing in the FROM > clause. Shouldn't a constraint be in the WHERE clause while the FROM > clause should only describe the source tables? AFAIK, when you're writing your FROM clause in the form "table1 JOIN table2 ON condition" you can write pretty much any condition on table2 right here in the FROM clause (I don't remember exactly but maybe you can put condition on table1 here too). But when you're writing query in the form "FROM table1, table2 WHERE conditions" then yes, of course, all conditions should go into WHERE clause. And this is exactly why I don't like "table1 JOIN table2" form (one can easily confuse himself by putting related conditions to different places) and write my queries always in the form "FROM table1, table2". In this particular case I've decided to continue to write in your syntax preference and put related conditions (pt_p.page_id = pt_np.page_id AND pt_np.tag_id != 1) in one place because I think when they stand nearby intention of the query is more understandable. Pavel On Tue, Jul 28, 2009 at 9:59 PM, P Kishor wrote: > On Tue, Jul 28, 2009 at 7:45 PM, Pavel Ivanov wrote: >> First of all you've mentioned schema which is not in agreement >> slightly with sql you've given. But of course we can guess... >> Second your sql seems to do slightly different thing from what you're >> saying. In particular I'm concerned about this sentence: >> >>> If the tag is the same as the 'current_tag', add a 'classtype' of >>> 'tag_a', else 'tag_b' (the effect of the 'classtype' can be seen by >>> clicking on any of the tags and seeing the color of the relevant tags >>> change); >> >> What sql does is: if tag is among those assigned to pages having also >> 'current_tag' then add a classtype 'tag_a' else 'tag_b'. >> >> But keeping all that in mind if I were you I'd write select in the >> following way: >> >> SELECT >> CASE WHEN t_cur.tag_id is null THEN 'tag_b' ELSE 'tag_a' >> END AS classtype, >> t.tag_id, >> t.tag_name, >> count(*) tag_num >> FROM pages_tags pt_p >> JOIN pages_tags pt_np on pt_p.page_id = pt_np.page_id >> AND pt_np.tag_id != 1 >> JOIN tags t on pt_np.tag_id = t.tag_id >> LEFT OUTER JOIN ( >> SELECT DISTINCT pt_pcur.tag_id >> FROM pages_tags pt_cur >> JOIN pages_tags pt_pcur on pt_pcur.page_id = >> pt_cur.page_id >> WHERE pt_cur.tag_id = ?) t_cur >> on t_cur.tag_id = t.tag_id >> WHERE pt_p.tag_id = 1 >> GROUP BY t.tag_id, t.tag_name >> ORDER BY t.tag_name >> >> It eliminates executing of select for each row and also shows clearly >> how it's intended to be executed... > > > Yes, indeed. Your query works well. One question... what is happening with > > FROM > pages_tags pt_p JOIN pages_tags pt_np ON pt_p.page_id = pt_np.page_id > AND pt_np.tag_id != 1 > > > I can't understand what that != 1 constraint is doing in the FROM > clause. Shouldn't a constraint be in the WHERE clause while the FROM > clause should only describe the source tables? > > >> >> Pavel >> >> On Tue, Jul 28, 2009 at 7:21 PM, P Kishor wrote: >>> Following up on my recent question about arbitrarily tagging rows in a >>> table, my website has certain pages that are tagged as 'Photos' and >>> other tags (http://www.punkish.org/Photos). The table schema is as >>> follows >>> >>> TABLE pages (p_id INTEGER PRIMARY KEY, p_name TEXT); >>> TABLE tag (t_id INTEGER PRIMARY KEY, t_name TEXT); >>> TABLE pages_tags (p_id INTEGER, t_id INTEGER); >>> >>> Given a parameter ? = 'current_tag' >>> >>> Find all the non-'Photos' tags and their Counts for the pages that are >>> tagged as 'Photos' (tag_id = 1); >>> If the tag is the same as the 'current_tag', add a 'classtype' of >>> 'tag_a', else 'tag_b' (the effect of the 'classtype' can be seen by >>> clicking on any of the tags and seeing the color of the relevant tags >>> change); >>> >>> >>> SELECT >>> -- set 'classtype' to 'tag_a' or 'tag_b' >>> CASE >>> WHEN t.tag_id IN ( >>> SELECT DISTINCT tag_id >>>
Re: [sqlite] except and select
Maybe this way: select t.* from table_name t left outer join table_name t_del on t_del.operation = 'Deleted' and t_del.Filename = t.RenameTo where t.operation = 'Renamed' and t_del.ID is null union all select t.* from table_name t left outer join table_name t_ren on t_ren.operation = 'Renamed' and t_ren.RenameTo = t.Filename where t.operation = 'Deleted' and t_ren.ID is null Pavel On Wed, Jul 29, 2009 at 10:17 AM, Gene Allen wrote: > I need to come up with a query where I can remove records that match a > certain condition. > > > > I have file auditing data and I need the query to show all the files that > were deleted and the files that were renamed but not the files that was > deleted AND renamed TO. Problem is the Delete is one record and the Rename > is another one. > > > > For example, in the sample table below, I want records 3 and 4, since A was > deleted (record 1) AND renamed to (record 2) > > > > ID Filename operation RenamedTo > > 1 A Deleted > > 2 B Renamed A > > 3 C Renamed D > > 4 E Deleted > > > > I tried to use an except, but that won't work since the data won't match up > between the records. Record 1 and 2 don't match. > > > > Due to a limitation in my program, I have to do this in a single select > statement. > > > > Any advice would be wonderful! > > > > Thanks, > > > > Gene > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_get_table and probable memory leak
0x490 = 1168
Sounds like memory required to store one page of size 1024 in SQLite
database cache. Does your database has this size of pages? Will this
leak be gone if you close database connection?
Pavel
On Thu, Jul 30, 2009 at 7:00 AM, Maciej
Miszczak wrote:
> Hi,
>
> I have a simple function (used to get only one value from a database),
> as shown below, and I've struggled with it for a long time, without
> success. I use libsqlite3.6.13.
>> int Database::execute(std::string query, std::string* result,
>> std::string* errMsg)
>> {
>> int rc, tmpRowsCount, tmpColsCount;
>> char* zErrMsg;
>> char** tmpRes;
>> ostringstream tmp;
>>
>> OS.logger->log(string("Executing query: ") + query, LOG);
>>
>> rc = sqlite3_get_table(db, query.c_str(), &tmpRes, &tmpRowsCount,
>> &tmpColsCount, &zErrMsg);
>> if( rc!=SQLITE_OK )
>> {
>> tmp << "SQL error: " << zErrMsg;
>> if (errMsg != NULL)
>> {
>> errMsg->assign(zErrMsg);
>> sqlite3_free(zErrMsg);
>> }
>> OS.logger->log(tmp.str(), ERR);
>> tmp.str("");
>> sqlite3_free_table(tmpRes);
>> return -1;
>> }
>>
>> if ((tmpRowsCount != 1) || (tmpColsCount != 1)) // TODO: check row
>> count
>> {
>> tmp << "Invalid number of rows (" << tmpRowsCount << ") or
>> columns (" << tmpColsCount << ")";
>> OS.logger->log(tmp.str(), ERR);
>> tmp.str("");
>> sqlite3_free_table(tmpRes);
>> return -2; // TODO: define error
>> }
>>
>> if (result != NULL)
>> result->assign(tmpRes[1]);
>>
>> sqlite3_free_table(tmpRes);
>>
>> return 0;
>> }
> The problem is that after calling above function i get from mtrace:
>> Memory not freed:
>> -
>> Address Size Caller
>> 0x0005f9d0 0xf at 0x401e8ef8
>> 0x00062828 0x490 at 0x4012c920
> Not freed 0xf at 0x0005f9d0 is OK, because it's caused by
> result->assign(tmpRes[1]). What I'm confused with is the 0x490 of not
> freed memory.
>
> Here is raw mtrace log:
>> = Start
>> @ /usr/lib/libstdc++.so.6:(_Znwj+0x6c)[0x401e8ef8] + 0x61310 0x56
>> @ /usr/lib/libstdc++.so.6:(_Znwj+0x6c)[0x401e8ef8] + 0x61370 0x1e
>> @ /usr/lib/libstdc++.so.6:(_Znwj+0x6c)[0x401e8ef8] + 0x61398 0x67
>> @ /usr/lib/libstdc++.so.6:(_ZNSsD1Ev+0x1c)[0x401d14e0] - 0x61398
>> @ /usr/lib/libstdc++.so.6:(_ZNSsD1Ev+0x1c)[0x401d14e0] - 0x61370
>> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x5fbe8 0x58
>> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x62828 0x658
>> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x61370 0xe0
>> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x62e88 0x408
>> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x61458 0x230
>> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x61458
>> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x62828
>> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x61458 0x160
>> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x62828 0x490
>> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x5fe00 0x20
>> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x5f9b8 0x10
>> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x61458
>> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x62e88
>> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x61370
>> @ /usr/lib/libsqlite3.so.0:[0x4012c8ec] < 0x5fbe8
>> @ /usr/lib/libsqlite3.so.0:[0x4012c8ec] > 0x5fbe8 0x18
>> @ /usr/lib/libstdc++.so.6:(_Znwj+0x6c)[0x401e8ef8] + 0x5f9d0 0xf
>> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x5fe00
>> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x5f9b8
>> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x5fbe8
>> @ /usr/lib/libstdc++.so.6:(_ZNSsD1Ev+0x1c)[0x401d14e0] - 0x61310
>> = End
> sqlite3_free_table is called for sure, because if I comment it, I get:
>> Memory not freed:
>> -
>> Address Size Caller
>> 0x0005f9b8 0x10 at 0x4012c920
>> 0x0005f9d0 0xf at 0x401e8ef8
>> 0x000601a8 0x18 at 0x4012c8ec
>> 0x00060208 0x20 at 0x4012c920
>> 0x00062828 0x490 at 0x4012c920
> Thanks in advance for any hints.
>
> Cheers,
> Maciej Miszczak
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Use of in memory db
> What I assume is if I share an in memory db connection handle across threads > then it will not be sqlite's responsibility but the user's responsibility to > protect multiple insert/update statements. On the other hand if the > connection to physical db is shared amongst threads then sqlite takes care of > synchronization between multiple insert/update from multiple threads. Your assumption is wrong. When you share the same connection among several threads SQLite works with the same scenario no matter if you connection to in-memory database or to physical file. And this scenario depends on compilation options and flags given to sqlite3_open. With default compilation options if you gave flag SQLITE_OPEN_NOMUTEX then it's your responsibility to protect with mutexes, if you gave flag SQLITE_OPEN_FULLMUTEX then SQLite makes this work for you. What you meant to say maybe is that if you have different connections in each thread to the same physical file then SQLite can make more fine-grained synchronization between threads. This scenario is not applicable to in-memory database because there's no way you can open several connections to the same in-memory database. > Basically a broader question is that, Is there any way to avoid sharing of > connection handle and even then be able to work on the same memory db from > multiple threads. As I've already said: no. > Or any one can suggest how best the mutex can be used if I share the > connection and then perform transactions (insert, update). If you have resource shared between threads there's only one way to use it - protect each access to it with mutex. There's no other ways to do it and no tricks can be done in here. The only trick that you can do is to avoid sharing of the resource and develop some memory structure that will allow you concurrent access from different threads with the pattern your application needs. But SQLite is not your fellow here - it doesn't offer this kind of memory structure. Pavel On Thu, Jul 30, 2009 at 7:16 AM, Sharma, Gaurav wrote: > Hi All, > > Is there any way through which without using the mutex lock mechanism > multiple threads can perform INSERT in bulk on same memory db. What I assume > is if I share an in memory db connection handle across threads then it will > not be sqlite's responsibility but the user's responsibility to protect > multiple insert/update statements. On the other hand if the connection to > physical db is shared amongst threads then sqlite takes care of > synchronization between multiple insert/update from multiple threads. > > Basically a broader question is that, Is there any way to avoid sharing of > connection handle and even then be able to work on the same memory db from > multiple threads. I really wish to avoid the burden of using mutex for every > (insert or update) in multi threaded scenario. That actually supposed to put > lot of overhead on my application. > > Or any one can suggest how best the mutex can be used if I share the > connection and then perform transactions (insert, update). > > With Best Regards > Gaurav Sharma > > > The information contained in this electronic mail transmission > may be privileged and confidential, and therefore, protected > from disclosure. If you have received this communication in > error, please notify us immediately by replying to this > message and deleting it from your computer without copying > or disclosing it. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_get_table and probable memory leak
> Is lowering the PRAGMA cache_size a good way to fix this? Yes, it's the only way to fix this. Though IIRC you cannot lower it below 10, so 10 pages will be always stored in memory. OTOH you can implement your own version of page cache that will not leave pages in memory at all (for more information see http://www.sqlite.org/c3ref/pcache_methods.html). > I see now I have probably missed some basic rules > of using SQLite. I just created a Database class with constructor > opening a database connection and storing a sqlite3* pointer and > destructor closing this connection. I think you've implemented it correctly. I doubt you want to open and close connection for executing of each statement because it's some serious overhead. Pavel On Thu, Jul 30, 2009 at 8:25 AM, Maciej Miszczak wrote: > Pavel, you're great! There's nothing better than to just ask the right > people. Calling sqlite3_close after the mentioned function solved the > problem. By the way I got something like this: >> - 0x0005e688 Free 28 was never alloc'd 0x400f3ec4 >> - 0x0005e750 Free 29 was never alloc'd 0x400f3ec4 >> - 0x0005e768 Free 30 was never alloc'd 0x400f3ec4 >> - 0x0005e788 Free 31 was never alloc'd 0x400f3ec4 > and so on, but I think there's nothing to worry about. My database has > the page_size of 1024. I see now I have probably missed some basic rules > of using SQLite. I just created a Database class with constructor > opening a database connection and storing a sqlite3* pointer and > destructor closing this connection. In this situation every > Database::execute call produced, as you said, not freed cache. It seems > to be the cause of all my problems with "leaks". Is lowering the PRAGMA > cache_size a good way to fix this? My database works on embedded > platform with 64MB of RAM. > > Thanks again, cheers, > Maciej > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date Comparisons SQL
> How do you properly do a Date comparison in SELECT so that the only rows > returned are those that do not exceed the date found in my sDateTemp > variable? As a simple string comparison. You made it perfectly right except that your Date field should be stored in a format '-mm-dd' in database. Without it comparison will not work. Pavel On Mon, Aug 3, 2009 at 2:19 PM, Rick Ratchford wrote: > This works: > > SQLString = "SELECT Format$(Date, '-mm-dd') as Date, Year, Month, > Day, Open, High, Low, Close, DayNum, 0 as IsSwingTop1, 0 as > IsSwingBtm1, 0 as IsSwingTop2, 0 as IsSwingBtm2, Null as Delta1, Null > as Delta2, 0 as Offset1, 0 as Offset2 FROM [" & sTable & "] GROUP BY > Year, Month, Day" > > > This does not: > > SQLString = "SELECT Format$(Date, '-mm-dd') as Date, Year, Month, > Day, Open, High, Low, Close, DayNum, 0 as IsSwingTop1, 0 as > IsSwingBtm1, 0 as IsSwingTop2, 0 as IsSwingBtm2, Null as Delta1, Null > as Delta2, 0 as Offset1, 0 as Offset2 FROM [" & sTable & "] WHERE Date > < " & sDateTemp & " GROUP BY Year, Month, Day" > > The difference is that I want the second statement to only retrieve > records (rows) that do not exceed the date listed in sDateTemp. > > I've converted the local date format to "-mm-dd" in sDateTemp > before using it in this SQL statement. But that didn't seem to solve > the problem. I'm still getting a single blank record. > > How do you properly do a Date comparison in SELECT so that the only rows > returned are those that do not exceed the date found in my sDateTemp > variable? > > Thanks. > Rick > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date Comparisons SQL
> I've attached a view of the results from the working SQL statement that does > not perform the WHERE. Attachments do not come through to this list. > Also, as stated in my previous post, I have sDateTemp formatted in the same > format as that which is in the table. Could you elaborate: your sDateTemp is "the same format as in the table" or "formatted as '-mm-dd'"? If the former then what the format is? And format exactly in the table? Pavel On Mon, Aug 3, 2009 at 2:44 PM, Rick Ratchford wrote: > That's the clincer. > > The resulting DATE column is actually the format of the equation as well. > > I've attached a view of the results from the working SQL statement that does > not perform the WHERE. > > "WHERE Format$(Date, '-mm-dd') < sDateTemp" does not work. > > Also, as stated in my previous post, I have sDateTemp formatted in the same > format as that which is in the table. > > That's why I'm puzzled. > > Rick > > > > #>-Original Message- > #>From: sqlite-users-boun...@sqlite.org > #>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik > #>Sent: Monday, August 03, 2009 1:38 PM > #>To: sqlite-users@sqlite.org > #>Subject: Re: [sqlite] Date Comparisons SQL > #> > #>Rick Ratchford wrote: > #>> The Date is being stored as -mm-dd. Note the "Format$(Date, > #>> '-mm-dd') as Date" that assures this. > #> > #>The "Date" that appears in the WHERE clause is the value of > #>the Date column in the table, not the value of the expression > #>with the "Date" > #>alias. You can't actually use aliases in the WHERE clause. > #>You are confusing yourself by using the same identifier both > #>for the column name and for the alias. > #> > #>You could write > #> > #>WHERE Format$(Date, '-mm-dd') < sDateTemp > #> > #>Or else, express sDateTemp in the same format that you have > #>dates stored in the table - the format you get when you just > #>run "SELECT Date from mytable". > #> > #>Igor Tandetnik > #> > #> > #> > #>___ > #>sqlite-users mailing list > #>sqlite-users@sqlite.org > #>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > #> > #> > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to create a data of unsigned int?
int64 type is okay to store data of type unsigned int. And int64 is exactly the type used for all integers in SQLite. Pavel On Fri, Aug 7, 2009 at 5:53 AM, liubin liu<7101...@sina.com> wrote: > > I want to use a integer to save the ip address of ipv4. Because they all are > 4 bytes. > > but it is better when the data type is unsigned int. > > How to create a data of unsigned int? > 1334 > 1335 // tcp->gate_addr[0]是地址的高8位 > 1336 for ( i=0, m=1; i<4; i++, m*=0x100 ) > 1337 gateaddr = gateaddr + tcp->gate_addr[3-i] * m; > 1338 > > -- > View this message in context: > http://www.nabble.com/How-to-create-a-data-of-unsigned-int--tp24861945p24861945.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to create a data of unsigned int?
SQLite doesn't support unsigned types. And int64 is not too wasting for you unless you work with negative numbers (which you are not because you work with unsigned numbers). It's because SQLite always does compacting of integer numbers - if number fits into 1 byte then it stores 1 byte, if it fits in 2 bytes then it stores 2 etc. So in your case it most probably will store 5 bytes which is not too bad. IIRC, only for negative numbers SQLite always uses 8 bytes. Pavel On Fri, Aug 7, 2009 at 9:11 AM, liubin liu<7101...@sina.com> wrote: > > Thanks, :) > > But now I think that's ok to use the int type to store the four byte. > Because I could get the value by using forced conversion. > > But Do SQLite3 support the unsigned type? > > And You know that int64 is too wasting here. > > > > Pavel Ivanov-2 wrote: >> >> int64 type is okay to store data of type unsigned int. And int64 is >> exactly the type used for all integers in SQLite. >> >> Pavel >> >> On Fri, Aug 7, 2009 at 5:53 AM, liubin liu<7101...@sina.com> wrote: >>> >>> I want to use a integer to save the ip address of ipv4. Because they all >>> are >>> 4 bytes. >>> >>> but it is better when the data type is unsigned int. >>> >>> How to create a data of unsigned int? >>> 1334 >>> 1335 // tcp->gate_addr[0]是地址的高8位 >>> 1336 for ( i=0, m=1; i<4; i++, m*=0x100 ) >>> 1337 gateaddr = gateaddr + tcp->gate_addr[3-i] * m; >>> 1338 >>> >>> -- >>> View this message in context: >>> http://www.nabble.com/How-to-create-a-data-of-unsigned-int--tp24861945p24861945.html >>> Sent from the SQLite mailing list archive at Nabble.com. >>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > -- > View this message in context: > http://www.nabble.com/How-to-create-a-data-of-unsigned-int--tp24861945p24864619.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concurrency Question
> If I have 2 separate processes, 1 of which will attempt to Read and Write a > Database (Process A) > and the 2nd which will only Read the database (Process B), then if Process A > is in the middle of a Write when Process B tries to read what will happen? If Process A is in the middle of writing transaction but has not actually written anything to disk yet (all changes are in cache) then Process B will proceed reading unlocked. If Process A is in the middle of committing transaction or it has written some changes to disk (there're so many changes that they don't fit into cache) then API in Process B will return SQLITE_BUSY error. But only unless you've called sqlite3_busy_timeout() with non-zero value. In the latter case Process B will return SQLITE_BUSY only after mentioned timeout has gone and Process A does not finish transaction yet. And when SQLITE_BUSY is returned then indeed it's up to you to try again later. > And Vice versa, What happens if Process B is reading while A tries to write? Process A will continue unblocked until it will have to actually write to disk. At this point it will wait for Process B to finish its reading and after that it will actually write everything it needs. Pavel On Fri, Aug 7, 2009 at 11:50 AM, JimmyKryptonite wrote: > > I'm looking to start a project using SQLite to handle some database > transactions. I have question about how SQLite handles Concurrency. I read > up on SQLite.org about the file locking and concurrency but I didn't really > get the info I wanted (at least I didn't walk away with an answer in the > terms I wanted it). > > My question is the following: > > If I have 2 separate processes, 1 of which will attempt to Read and Write a > Database (Process A) > and the 2nd which will only Read the database (Process B), then if Process A > is in the middle of a Write when Process B tries to read what will happen? > Will the Read request be blocked and Process B will wait or will SQLite > return some kind of Busy Error Code to process B and it is up to Process B > to try again later? > > And Vice versa, What happens if Process B is reading while A tries to write? > Same answer as above? > > I'm very much a newbie to SQLite so patience is requested. > > Thanks in advance > -- > View this message in context: > http://www.nabble.com/Concurrency-Question-tp24867278p24867278.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Busy Handler in Shared Cache Mode?
Maybe you could benefit of using sqlite3_unlock_notify()? See more info here: http://www.sqlite.org/c3ref/unlock_notify.html. Pavel On Fri, Aug 7, 2009 at 1:45 PM, Nikolaus Rath wrote: > Hello, > > I have program that continuously runs several threads (about 4 to 10) > which concurrently read and write to an SQLite database. From > http://www.sqlite.org/sharedcache.html it seems to me that I should be > using shared cache mode. > > Until now, I have set the busy timeout to 500 msec and never had to deal > with SQLITE_BUSY errors. However, there seems to be no corresponding > function for the SQLITE_LOCKED errors that are generated in shared cache > mode. So I changed the code manually sleep for a random amount (0 to 100 > msec) and then try to execute the statement again if it encounters > SQLITE_LOCKED. But now the threads are often waiting for more than 1 > second before they finally get their query executed. > > I suspect this is because the random sleep is wasting a lot of time, > while without shared cache (and with enabled busy timeout) a thread > blocks and gets revived as soon as the lock on the database is > released. > > > How can I avoid this problem? Is there a way to set a busy timeout for > SQLITE_LOCKED as well? Or a more clever method instead of sleeping for > random amounts? Or should I just avoid using shared cache mode? > > > Thanks, > > > -Nikolaus > > -- > »Time flies like an arrow, fruit flies like a Banana.« > > PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table Exists Query
Why not make it this way: select 'database1', lists.* from database1.lists union all select 'database2', lists.* from database2.lists; This way you will know exactly which lists persist in which database. Pavel On Sat, Aug 15, 2009 at 10:57 AM, Andrew Gatt wrote: > Simon Slavin wrote: >> On 15 Aug 2009, at 3:12pm, Andrew Gatt wrote: >> >> >>> but by my scheme you can't be sure if the list_1 table exists in >>> database2, >>> >> >> Add a column to your 'lists' tables which says which database they're >> part of. So every row of the database will have the same value. >> >> >> > Unfortunately the database names change (they are assigned as and when > the storage is attached) and also the list will only return all the > items if all the databases are present, a smaller subset otherwise. So > i'd still have to check if the tables in the databases existed - wouldn't i? > > Andrew > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple indexes in SQLite, and selecting which to use
> Is there any way the code can 'suggest' SQLite use a certain index? Does INDEXED BY clause work for you? http://www.sqlite.org/lang_select.html Pavel On Sat, Aug 15, 2009 at 2:08 AM, His Nerdship wrote: > > Good day, > We have a puzzling problem with a large (1GB+) database. > Most of our queries are based on 3 columns, say X, Y and Z. > X is always the first in the index. However, sometimes the query involves a > small range of Y and a larger range of Z, and sometimes the reverse. We > first had an index based on X, Y & Z (in that order), and noticed that the > results are fast when there was one X, one Y and many Z's. I check if the > range is a single value, and if it is, I change the SQL to a straight '=', > e.g: > ..WHERE X = x AND Y = y AND Z BETWEEN z1 AND z20; > > According to Mike Owens, using an equality or IN operator on Y allows Z to > be indexed, speeding up the search. If Y is a range and we use "BETWEEN y1 > AND y2" on it, then Z will not be indexed. This is what we found - the > second search was much slower. > > However because sometimes the numbers are reversed, such that there are many > Y's and few Z's, we added another index based on X, Z and Y, in that order. > In this case, though, it didn't make any difference. It seems like SQLite > does not select the correct index to use - it uses XYZ instead of XZY. > I know Mr Hipp is reluctant to add the ability to specify which index to use > - it is 'un-RDBMS like' in his words. > Is there any way the code can 'suggest' SQLite use a certain index? Or at > least confirm which index is being used? > > Also, is there a sensible maximum no of values we can put in an IN clause? > Many of the queries involve all records over a month, and hitherto we have > used, say, ..WHERE Date BETWEEN 20090701 AND 20090731; > In this case would it work better with ..WHERE Date IN (20090701, 20090702, > 20090703, ..., 20090731)? > Thanks in advance > -- > View this message in context: > http://www.nabble.com/Multiple-indexes-in-SQLite%2C-and-selecting-which-to-use-tp24981846p24981846.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Rows where people connected in the past 2 weeks?
Just try these and you'll see why.
sqlite> select julianday('18-08-2009');
sqlite> select julianday('now') - julianday('18-08-2009');
Pavel
On Tue, Aug 18, 2009 at 12:58 PM, Gilles Ganault wrote:
> Hello
>
> I thought this query would work to read a date column that holds the
> DD-MM- date when the user last logged on, and check how many of
> them logged on in the past two weeks for the last time:
>
> SELECT COUNT(id) FROM members WHERE (julianday('now') -
> julianday(dateconnection)) < 15;
>
> This is returning no row, even though I know a lot of rows have a
> connection date within the last two weeks.
>
> Any idea why this is wrong?
>
> Thank you.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Increment a value if constraint violated
You can always do insert into table (id, count) values (?1, (select count(*) + 1 from table where id = ?1)) Though I'd be cautious about race condition that seems like possible here when after select returned some value and before insert was made another process made another insert with the same id. Looks like you have to prepend it with BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Pavel On Tue, Aug 18, 2009 at 1:06 PM, andrew fabbro wrote: > What is the most efficient way to code either a trigger or the app so that > it increments a "count" field if the "id" field exists? > > For example, say you have a table with fields of "id" and "count". > > First row is an id of 3, so "count" is set to 1. > Next row is an id of 4, so "count" is set to 1. > Third row is an id of 3 again, so "count" is incremented to 2. > > I could do this: > > 1. try insert > 2. if SQLITE_CONSTRAINT, fetch the value of count > 3. increment count > 4. issue an UPDATE > > This seems wasteful...is there an efficient way to do this, say as a > trigger? > > Thanks! > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Increment a value if constraint violated
> Cannot happen. If not executed within an implicit transaction, each > SQL statement is effectively wrapped in a transaction all of its own. So executing the insert/update/delete statement places RESERVED lock right from the start before executing nested selects? Pavel On Tue, Aug 18, 2009 at 1:54 PM, Dan Kennedy wrote: > > On Aug 19, 2009, at 12:35 AM, Pavel Ivanov wrote: > >> You can always do >> >> insert into table (id, count) values (?1, (select count(*) + 1 from >> table where id = ?1)) >> >> Though I'd be cautious about race condition that seems like possible >> here when after select returned some value and before insert was made >> another process made another insert with the same id... > > Cannot happen. If not executed within an implicit transaction, each > SQL statement is effectively wrapped in a transaction all of its own. > > Dan. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Increment a value if constraint violated
>> So executing the insert/update/delete statement places RESERVED lock >> right from the start before executing nested selects? > > Yes. Then I stand corrected. Thank you. Pavel On Tue, Aug 18, 2009 at 2:04 PM, Dan Kennedy wrote: > > On Aug 19, 2009, at 1:01 AM, Pavel Ivanov wrote: > >>> Cannot happen. If not executed within an implicit transaction, each >>> SQL statement is effectively wrapped in a transaction all of its own. >> >> So executing the insert/update/delete statement places RESERVED lock >> right from the start before executing nested selects? > > Yes. > > Dan. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Increment a value if constraint violated
> insert or replace into mytable(id, count) > values (:id, coalesce((select count from mytable where id=:id), 0) + 1); I guess it doesn't work as OP intended: sqlite> select * from t; id|count 1|1 1|2 1|3 1|4 sqlite> insert or replace into t(id, count) values (1, coalesce((select count from t where id=1), 0) + 1); sqlite> select * from t; id|count 1|1 1|2 1|3 1|4 1|2 But this does: sqlite> insert or replace into t(id, count) values (1, coalesce((select count from t where id=1 order by count desc), 0) + 1); sqlite> select * from t; id|count 1|1 1|2 1|3 1|4 1|5 sqlite> delete from t; sqlite> insert or replace into t(id, count) values (1, coalesce((select count from t where id=1 order by count desc), 0) + 1); sqlite> select * from t; id|count 1|1 Pavel On Wed, Aug 19, 2009 at 12:14 AM, Igor Tandetnik wrote: > andrew fabbro wrote: >> What is the most efficient way to code either a trigger or the app so >> that it increments a "count" field if the "id" field exists? >> >> For example, say you have a table with fields of "id" and "count". >> >> First row is an id of 3, so "count" is set to 1. >> Next row is an id of 4, so "count" is set to 1. >> Third row is an id of 3 again, so "count" is incremented to 2. > > insert or replace into mytable(id, count) > values (:id, coalesce((select count from mytable where id=:id), 0) + 1); > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL documentation
Personally for me the current documentation style is more understandable at a glance. Looking at it it's easier for me to understand the sequence of terms I should use, what can be omitted, what terms cannot be used together and so on. Old style looks for me more like list of requirements for programmer who will implement parser than user-readable documentation... So apparently it's the matter of taste. :) And it looks like this documentation style is not proprietary to SQLite. Look for example here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7002.htm#i2201774. But unfortunately to me this style is not so popular as I've expected: http://msdn.microsoft.com/en-us/library/ms174979.aspx http://dev.mysql.com/doc/refman/5.1/en/create-table.html The only issue on which I can agree with you (and maybe something should be done about it) is SQLite has no searchable text version (Oracle has it though also not very convenient I think). I'm not sure though how severe this issue is. Pavel On Wed, Aug 19, 2009 at 3:01 AM, Glenn Maynard wrote: > SQLite had extremely readable SQL documentation: > http://www.3rd-impact.net/Document/SQLite/Translation/Current/Original/lang_createtable.html. > It was clear and intuitive; I can understand it at a glance. > > At some point, it was replaced with > http://sqlite.org/lang_createtable.html. It's closer to a rendering > of a parse tree than human documentation; I find it nearly unreadable. > With the text all baked into an image, it's also not searchable, and > I have to scroll up and down to read what used to fit in half a page. > > Is the text SQL documentation available for current SQLite versions? > > -- > Glenn Maynard > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Huge numbers of savepoints.
But how do you expect your application to deal with restarts and/or OS crashes? Do you want to still be able to revert to "marks" set in previous application run or not? And what about accessing to the data stored between "marks" from other processes? Pavel On Wed, Aug 19, 2009 at 4:07 AM, Chris Dew wrote: > http://www.sqlite.org/lang_savepoint.html > I'm looking for a datastore with the following properties: > * I need to 'mark' the state frequently (sub second interval). > * I need to be able to revert the datastore to a previous mark (with no > appreciable delay). > * I only need to keep the last few hundred 'marks'. (i.e. I never need to > revert to a datastore marked more than a few minutes ago.) > > The savepoint functionality in sqlite looks to almost fit the bill, but its > savepoints seem to be nested - i.e. you cannot 'forget about' old savepoints > while retaining recent savepoints. Is my understanding correct here? I'm > concerned that this would cause a performance issue when millions of nested > savepoints have accumulated. > > Obviously I can roll my own data structure here, but is sqlite was feasible, > it would be good. > > Does anyone have any suggestions? > > Thanks, > > Chris. > > -- > > http://www.finalcog.com/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA locking_mode could stand a rewrite
> 1. When is a lock released during a SELECT? Is it after the first > call to step()? The last call to step()? The call to finalize()? > Yes, I'm using the C API. Lock is released during the call to finalize() or reset(). > 2. If while in the step() process of a SELECT, there is a change made > to the db, will those changes affect SELECT's result set? In MySQL > I'm used to the result set of a SELECT reflecting the state of a > db at an instant, and not subject to change by any subsequent > INSERTs and UPDATEs, but I don't know if it's the same w/SQLite. There cannot be any changes in database while you're in process of stepping through select statement. Of course unless you're doing changes through the same SQLite connection. > 3. If I have a permanent write lock, and I go to > "locking_mode=NORMAL" will a SELECT remove the permanent write lock? Yes. Documentation seems to clearly state that. > Will what I'm trying to do work? Probably not like you intended. There cannot be any INSERT made while you're doing your d) step, so with high probability e) step will return no rows. It will return something only if somebody will quick enough to lock the database for insertion before you began to step through e). Pavel On Wed, Aug 19, 2009 at 9:07 AM, Angus March wrote: > For one thing, they shouldn't be using the word "exclusive" to mean two > different things. There's "locking_mode=EXCLUSIVE" meaning "permanent" > and "exclusive lock" meaning "write lock". At least I think that's what > they mean. > But my problem is understanding exactly when a lock is released > during a SELECT and if a SELECT will release a write lock after going > "locking_mode=NORMAL": > > 1. When is a lock released during a SELECT? Is it after the first > call to step()? The last call to step()? The call to finalize()? > Yes, I'm using the C API. > 2. If while in the step() process of a SELECT, there is a change made > to the db, will those changes affect SELECT's result set? In MySQL > I'm used to the result set of a SELECT reflecting the state of a > db at an instant, and not subject to change by any subsequent > INSERTs and UPDATEs, but I don't know if it's the same w/SQLite. > 3. If I have a permanent write lock, and I go to > "locking_mode=NORMAL" will a SELECT remove the permanent write lock? > > If the minutia of the above is confusing, here's what I want to do. I > have a column defined with > IsNew INTEGER NOT NULL DEFAULT 1 > INSERTs made to this table do not mention IsNew so the column indicates > which rows have recently been added. I want to process all the rows in > the db, then go back and process any rows that were added during the > first phase of processing. Here's what I imagined doing: > > a) PRAGMA locking_mode=EXCLUSIVE;//this is to cause atomicity between b) > and d) > b) UPDATE the_table SET IsNew=0; > c) PRAGMA locking_mode=NORMAL; > d) SELECT * FROM the_table;//at this point I would want all rows, and > only those rows, affected in b) to be returned here, INSERTs to > the_table to be allowed, and those INSERTs *not* to affect the result > set returned > e) SELECT * FROM the_table WHERE IsNew=1;//don't worry about locking > here, I'm going to be performing some extra-SQLite locking to ensure > that no other operations are performed on the db at this point > > Will what I'm trying to do work? > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select data from 2 lines in one line?
Without looking at your select statement it's very hard to help. But general suggestion is insert your results into temporary table and then issue a select on that table joined with itself with condition like t.rowid = prev.rowid + 1. Pavel On Wed, Aug 19, 2009 at 10:10 AM, Mário Anselmo Scandelari Bussmann wrote: > I have a table like this: > > petr4 > --- > rowid|data|preabe|premax|premin|preult|voltot > 1|2007-01-02|50.0|50.45|49.76|50.45|256115409.0 > 2|2007-01-03|50.16|50.4|48.01|48.7|492591256.0 > 3|2007-01-04|48.5|48.75|47.4|47.65|505916186.0 > 4|2007-01-05|47.2|47.99|45.1|46.19|581485748.0 > 5|2007-01-08|46.5|47.14|45.8|46.59|452501627.0 > 6|2007-01-09|45.97|46.39|44.61|45.52|587958198.0 > 7|2007-01-10|44.51|45.26|44.3|45.25|470899428.0 > 8|2007-01-11|44.9|46.43|44.76|45.21|542684895.0 > 9|2007-01-12|45.3|45.61|44.8|45.15|478912234.0 > 10|2007-01-15|45.61|45.85|44.89|44.89|317073087.0 > > I need a select that returns data,preult,previous data and previous preult: > > 2007-01-03|48.7|2007-01-02|50.45 > 2007-01-04|47.65|2007-01-03|48.7 > 2007-01-05|46.19|2007-01-04|47.65 > 2007-01-08|46.59|2007-01-05|46.19 > 2007-01-09|45.52|2007-01-08|46.59 > 2007-01-10|45.25|2007-01-09|45.52 > 2007-01-11|45.21|2007-01-10|45.25 > 2007-01-12|45.15|2007-01-11|45.21 > 2007-01-15|44.89|2007-01-12|45.15 > > How can I do that using only sql (no python, c or perl, no cursor)? > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Increment a value if constraint violated
> Are you sure that was the OP's intention? No, I'm not sure, of course, because OP didn't answer yet. But words "First row with id 3" and "Third row with id 3" suggested to me that they are different rows. And mentioning SQLITE_CONSTRAINT I've interpreted as "unique constraint on id-count pair". So let's wait for the answer. :) Pavel On Wed, Aug 19, 2009 at 12:05 PM, Igor Tandetnik wrote: > Pavel Ivanov wrote: >>> insert or replace into mytable(id, count) >>> values (:id, coalesce((select count from mytable where id=:id), 0) + >>> 1); >> >> I guess it doesn't work as OP intended: >> >> sqlite> select * from t; >> id|count >> 1|1 >> 1|2 >> 1|3 >> 1|4 > > Are you sure that was the OP's intention? His pseudocode suggests to me > that id is supposed to be unique, and the count should reflect how many > times this particular id was inserted. He definitely mentions > SQLITE_CONSTRAINT, while your table doesn't appear to be constrained in > any way. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] update - select
If you have synonym for rowid (i.e. column INTEGER PRIMARY KEY) or some other unique columns combination then you can do something like this: INSERT OR REPLACE INTO table_1 (rowid, field_a, field_b, field_c, field_d) SELECT table_1.rowid, table_2.field_a, table_2.field_b, table_2.field_c, table_2.field_d FROM table_1, table_2 WHERE … Pavel On Thu, Aug 20, 2009 at 6:01 AM, Gerald Ebner wrote: > Dear all, > > it seems that sqlite does not allow update statements of this kind: > > UPDATE table_1 SET (field_a, field_b, field_c, field_d) = ( > SELECT field_a, field_b, field_c, field_d FROM table_2 WHERE … > ) > WHERE > > I succeeded only with > > UPDATE table_1 SET > field_a = (SELECT field_a FROM table_2 WHERE … ), > field_b = (SELECT field_b FROM table_2 WHERE … ), > field_c = (SELECT field_c FROM table_2 WHERE … ), > field_d = (SELECT field_d FROM table_2 WHERE … ) > WHERE > > and that's may be not th optimal way, dealing with tables of a dozen of > fields > > thanks in advance > Geraldo > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
> If they are stored with wchar_t, then using the '16' APIs is > probably easiest to use (ie sqlite3_open16, sqlite3_prepare16_v2, etc). Just don't forget that wchar_t on some platforms (reportedly on Linux for example) is 32-bit integer. So conversion between wchar_t and UCS-2 encoding is not always as easy as you can think. Pavel On Fri, Aug 21, 2009 at 9:44 AM, Doug wrote: > Hi Erick -- > > I can only help a little with #3. How are your strings stored in your > program? If they are stored with wchar_t, then using the '16' APIs is > probably easiest to use (ie sqlite3_open16, sqlite3_prepare16_v2, etc). > That's what I do and all sorts of European and Asian customers don't have > any issues with storing and retrieving local strings. If you don't use the > wide-char (16) APIs, you would need to explicitly convert your strings to > UTF-8 (which is not the same as ASCII) before handing to SQLite. > > Doug > > >> -Original Message- >> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >> boun...@sqlite.org] On Behalf Of e...@sitadella.com >> Sent: Thursday, August 20, 2009 4:21 PM >> To: sqlite-users@sqlite.org >> Subject: [sqlite] (no subject) >> >> Hi guys, >> >> This is my first post. I am creating a simple document archiving >> program >> for small businesses. I am creating it in a scripting language called >> www.autohotkey.com. I intend to place the SQLite database file on a >> network share and use sqlite.dll to access and manipulate it. >> >> In general, everything is on a relatively small scale: there will be >> less >> than 10 users who will occasionally interact with the database, there >> will >> be around 4 tables and based on experience with a similar system, I >> don't >> expect a total of more than 5 records after a few years of use. The >> client computers will be Windows XP or newer and the database file will >> be >> located on a network share on a Windows 2000 server or newer. >> >> 1. I have read that the file locking mechanisms on older windows >> networks >> are not very reliable and that it is not advisable to use SQLite on NFS >> or >> network shares. Given the robustness and efficiency of SQLite and the >> low >> frequency of use of my application, do I still need to worry about >> placing >> the database on a network share? >> >> 2. Should I modify any of the default settings to better suit this >> environment? >> >> 3. I am having problems reading and writing international characters to >> and from the database, specifically the norwegian characters æ, ø and >> å. >> If I use sqlite.exe to create a records containing æ, ø or å, I can >> read >> the record using sqlite.exe without any problems. Likewise, if I use >> SQLiteSpy to create a record containing ø, æ or å I can read the record >> using SQLiteSpy without any problems. But if I create a record in >> sqlite.exe and try to read it with SQLiteSpy or vice versa, it doesn't >> work as expected and the special characters are converted to all sorts >> of >> oddball symbols like squares and question marks. I assume this is >> somehow >> due to different ASCII/UTF encodings, but how can these problems be >> avoided? >> >> 4. Select commands are case sensitive with æ, ø and å. Is there a >> simple >> workaround for this? >> >> >> Regards, >> Erik >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
> Is there really anyone using UCS-2 now or did you mean UTF-16? No, I meant exactly UCS-2. Because UCS-2 guarantees that all symbols are represented by 2 bytes when UTF-16 does not. And I had an understanding that Doug said about this 16-bit guarantee. Also if we're talking about encoding where any character can be represented by a single variable of type wchar_t then we can talk only about UCS-2 or UCS-4, not about UTF-* variants. Though of course someone can talk about UTF-16 keeping in mind and relying on the fact that he will not ever deal with characters not fitting into 2 bytes in UTF-16 encoding and thus he effectively will work with UCS-2. Pavel On Fri, Aug 21, 2009 at 11:33 AM, Jean-Christophe Deschamps wrote: > Hi Pavel, > > ´¯¯¯ >>So conversion between wchar_t and >>UCS-2 encoding is not always as easy as you can think. > `--- > > Is there really anyone using UCS-2 now or did you mean UTF-16? > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Huge numbers of savepoints.
With these requirements you can't implement it just on database level because it doesn't fit into the standard savepoint/transaction paradigm of databases. Only committed data and finished transactions will be available after OS crash or to other processes. After transaction is committed it cannot be rollbacked - data is saved in database and DBMS cannot do anything with it until application says what exactly it should do. So what you need to do can be implemented only on application level. You can do some sort of logging of your actions and doing opposite actions and cleaning the log when you need it. Pavel On Sun, Aug 23, 2009 at 6:46 AM, Chris Dew wrote: > Hi, thanks for your questions. > > 1. restarts or OS crashes should leave the data in a sane state - the > last savepoint would be fine. > 2. there's no requirement to revert to old savepoints set during a > previous application run. > 3. no need for more than one process to access the data, though > letting other processes see the data at the last save point would be > nice. > > Note: this is not for production code, just an experiment in keeping a > history of application 'state', allowing current state to be > recalculated if an historic input is received 'late'. See > http://www.finalcog.com/haskell-decoupling-time-from-physics for a > similar idea (implemented in Haskell). > > Regards, > > Chris. > > On Aug 19, 2:36 pm, Pavel Ivanov wrote: >> But how do you expect your application to deal with restarts and/or OS >> crashes? Do you want to still be able to revert to "marks" set in >> previous application run or not? And what about accessing to the data >> stored between "marks" from other processes? >> >> Pavel >> >> >> >> On Wed, Aug 19, 2009 at 4:07 AM, Chris Dew wrote: >> >http://www.sqlite.org/lang_savepoint.html >> > I'm looking for a datastore with the following properties: >> > * I need to 'mark' the state frequently (sub second interval). >> > * I need to be able to revert the datastore to a previous mark (with no >> > appreciable delay). >> > * I only need to keep the last few hundred 'marks'. (i.e. I never need to >> > revert to a datastore marked more than a few minutes ago.) >> >> > The savepoint functionality in sqlite looks to almost fit the bill, but its >> > savepoints seem to be nested - i.e. you cannot 'forget about' old >> > savepoints >> > while retaining recent savepoints. Is my understanding correct here? I'm >> > concerned that this would cause a performance issue when millions of nested >> > savepoints have accumulated. >> >> > Obviously I can roll my own data structure here, but is sqlite was >> > feasible, >> > it would be good. >> >> > Does anyone have any suggestions? >> >> > Thanks, >> >> > Chris. >> >> > -- >> >> >http://www.finalcog.com/ >> > ___ >> > sqlite-users mailing list >> > sqlite-us...@sqlite.org >> >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> ___ >> sqlite-users mailing list >> sqlite-us...@sqlite.orghttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] datetime('now', 'utc') vs. CURRENT_TIMESTAMP
> Do I misunderstand something fundamental?
According to http://www.sqlite.org/lang_datefunc.html datetime('now')
returns date and time already as UTC. If you add 'utc' modifier then
it makes datetime() think that it's your local time and convert it to
'utc' thus adding 4 hours (apparently you're in GMT -4 timezone).
Pavel
On Mon, Aug 24, 2009 at 6:21 PM, Wilson, Ronald wrote:
> According to the documentation for CURRENT_TIMESTAMP, it should insert
> the current UTC date/time: http://www.sqlite.org/lang_createtable.html.
>
>
>
> However, there appears to be a mismatch with datetime('now', 'utc'):
>
>
>
> SQLite version 3.6.10
>
> Enter ".help" for instructions
>
> Enter SQL statements terminated with a ";"
>
> sqlite> create table test(stamp DATETIME default CURRENT_TIMESTAMP);
>
> sqlite> insert into test (rowid) values(1);
>
> sqlite> insert into test values (CURRENT_TIMESTAMP);
>
> sqlite> select * from test;
>
> 2009-08-24 22:19:43
>
> 2009-08-24 22:19:55
>
> sqlite> select datetime('now', 'utc');
>
> 2009-08-25 02:20:10
>
> sqlite>
>
>
>
> If you ignore the minor time diff from my slow typing, there is a clear
> 4 hour difference between CURRENT_TIMESTAMP and datetime('now', 'utc').
> Do I misunderstand something fundamental?
>
>
>
> RW
>
>
>
> Ron Wilson, Engineering Project Lead
>
> (o) 434.455.6453, (m) 434.851.1612, www.harris.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
