Re: [sqlite] Simple SQLite-based spreadsheet?
http://www.sqlmaestro.com May do what you want... Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Gilles Ganault [gilles.gana...@free.fr] Sent: Friday, December 07, 2012 7:41 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Simple SQLite-based spreadsheet? On Fri, 7 Dec 2012 13:37:45 +, Simon Slavin slav...@bigfraud.org wrote: Use the free SQLite shell tool downloadable from the SQLite site. Write your own interface in PHP using the sqlite3 interface which does exactly what you want. Thanks but before I build my own, I wanted to check that no one had already built a Windows application with SQLite embedded, available either as free- or share-ware. ___ 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] disk image malformed
Can you try doing table copies using select with offset and limit? See where limit crashes the system and then offset past it perhaps? Just a guess on my part as one possibility. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Durga D [durga.d...@gmail.com] Sent: Thursday, December 06, 2012 11:04 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] disk image malformed i mean in second case. some records corrupted but not all. How to recover this? On Thu, Dec 6, 2012 at 10:27 PM, Durga D durga.d...@gmail.com wrote: Is there anyway to recover? On Thu, Dec 6, 2012 at 10:23 PM, Richard Hipp d...@sqlite.org wrote: On Thu, Dec 6, 2012 at 11:52 AM, Durga D durga.d...@gmail.com wrote: Hi, Is it possible to corrupt a single table among 10 tables in a database? Is it possible to corrupt some records among millions of records in a table? Yes. Yes. Best Regards, ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-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] Tracing latencies
Hmmm...looking at that strace sequencing is there some reason those 24-byte and 1024-byte writes can't be combined? The 1024-byte write is occurring at the end boundary of the 24-byte. That would cut the i/o ops in half and might be a noticeable improvement. A memory copy would be a lot faster than 2 i/o requests. And since disk page size is always a power of 2 would the 1024 buffer be better off as 1000 to align the page i/o better? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Keith Chew [keith.c...@gmail.com] Sent: Wednesday, December 05, 2012 2:11 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Tracing latencies Hi Dan On Wed, Dec 5, 2012 at 6:38 PM, Dan Kennedy danielk1...@gmail.com wrote: If it's not fsync() then IO delays are normally caused by read(). You could try [strace -T -eread ...] to check. Are SELECT statements fast on the same database? How large is the database compared to the machines memory? Ah, do you think the read on the OS level is starving the writes? There are not many DB selects from the app, but there are other read IO activities happening in the background. Still it doesn't make sense, because WAL mode ensures from sqlite's point of view, the reader will not be blocked by the writer. So, sqlite is subjected to similar levels of read IO activity as Mysql (because there are very few DB selects). The tables are small, around 20MB in total, compared to 2GB of memory available. I have done more investigation. Using strace, I waited to capture an insert/update with a long latency, and caught one that is 2s long, below is the strace to the WAL file. We can see that it is very bursty, all the seeks and writes span close to over 1s. I recall doing a strace on Mysql a long time ago, and each insert only does a single seek and write. Maybe it is sqlite's file format that requires it to seek/write multiple places causing the slowness? I am only guessing here, as I do not know how to interpret the strace logs below. [pid 4015] 21:01:53.634099 _llseek(98, 499928, unfinished ... [pid 4015] 21:01:53.634245 write(98, \0\0\0\4\0\0HU^=\226\213\23\10\247+\214\332\260\314Wf , 24 unfinished ... [pid 4015] 21:01:53.634546 _llseek(98, 499952, unfinished ... [pid 4015] 21:01:53.634712 write(98, \r\0\0\0\1\0033\0\0033\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0..., 1024 unfinished ... [pid 4015] 21:01:53.740378 _llseek(98, 500976, [500976], SEEK_SET) = 0 [pid 4015] 21:01:53.740449 write(98, \0\0\0\3\0\0\0\0^=\226\213\23\10\247\327\201\32\227\323\f8, 24) = 24 [pid 4015] 21:01:53.740521 _llseek(98, 501000, [501000], SEEK_SET) = 0 [pid 4015] 21:01:53.740566 write(98, \r\3\221\0\t\3G\1\3\243\3\261\3\352\3\325\3\304\3G\3\177\3m\3\\\0\0\0\0\0\0..., 1024) = 1024 [pid 4015] 21:01:53.740632 _llseek(98, 502024, [502024], SEEK_SET) = 0 [pid 4015] 21:01:53.740677 write(98, \0\0\37X\0\0\0\0^=\226\213\23\10\247dL\17\316\32\30\301\237, 24) = 24 [pid 4015] 21:01:53.740736 _llseek(98, 502048, [502048], SEEK_SET) = 0 [pid 4015] 21:01:53.740781 write(98, \r\0\0\0\5\1*\0\3o\2\336\2L\1\273\1*\0\0\0\0\0\0\0\0\0\0\0\0\0\0..., 1024) = 1024 [pid 4015] 21:01:53.740844 _llseek(98, 503072, [503072], SEEK_SET) = 0 [pid 4015] 21:01:53.740889 write(98, \0\0HR\0\0HU^=\226\213\23\10\247\276\32g\304j\372Q., 24) = 24 [pid 4015] 21:01:53.740945 _llseek(98, 503096, [503096], SEEK_SET) = 0 [pid 4015] 21:01:53.740989 write(98, \n\0\0\0\25\1\264\0\2@\2\\\2x\2\224\2\260\2\314\2\350\3\4\3 \3\3X\3t..., 1024) = 1024 [pid 4015] 21:01:54.636566 _llseek(98, 504120, [504120], SEEK_SET) = 0 [pid 4015] 21:01:54.636636 write(98, \0\0\0\4\0\0HU^=\226\213\23\10\247=]`\3700\351\226n, 24) = 24 [pid 4015] 21:01:54.636812 _llseek(98, 504144, [504144], SEEK_SET) = 0 [pid 4015] 21:01:54.636860 write(98, \r\0\0\0\1\0033\0\0033\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0..., 1024 unfinished ... [pid 6744] 21:01:54.676590 close(98) = 0 Regards Keith ___ 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] Tracing latencies
Run this program (change the diff threshold if you want) on the strace log file.
This will only show the calls that take too long and the time involved.
#include stdio.h
#include stdlib.h
main(int argc, char *argv[])
{
char buf[65535];
double t1=0,t2;
FILE *fp=fopen(argv[1],r);
while(fgets(buf,sizeof(buf),fp)) {
if (t1==0) {
t1 = atof(buf);
}
else {
t2 = atof(buf);
double diff = t2 - t1;
if (diff .0001) {
printf(%.6f %s,t2-t1,buf);
}
t1 = t2;
}
}
}
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Keith Chew [keith.c...@gmail.com]
Sent: Wednesday, December 05, 2012 2:11 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Tracing latencies
Hi Dan
On Wed, Dec 5, 2012 at 6:38 PM, Dan Kennedy danielk1...@gmail.com wrote:
If it's not fsync() then IO delays are normally caused by read().
You could try [strace -T -eread ...] to check.
Are SELECT statements fast on the same database? How large is the
database compared to the machines memory?
Ah, do you think the read on the OS level is starving the writes?
There are not many DB selects from the app, but there are other read
IO activities happening in the background. Still it doesn't make
sense, because WAL mode ensures from sqlite's point of view, the
reader will not be blocked by the writer. So, sqlite is subjected to
similar levels of read IO activity as Mysql (because there are very
few DB selects).
The tables are small, around 20MB in total, compared to 2GB of memory available.
I have done more investigation. Using strace, I waited to capture an
insert/update with a long latency, and caught one that is 2s long,
below is the strace to the WAL file. We can see that it is very
bursty, all the seeks and writes span close to over 1s.
I recall doing a strace on Mysql a long time ago, and each insert only
does a single seek and write. Maybe it is sqlite's file format that
requires it to seek/write multiple places causing the slowness? I am
only guessing here, as I do not know how to interpret the strace logs
below.
[pid 4015] 21:01:53.634099 _llseek(98, 499928, unfinished ...
[pid 4015] 21:01:53.634245 write(98,
\0\0\0\4\0\0HU^=\226\213\23\10\247+\214\332\260\314Wf , 24
unfinished ...
[pid 4015] 21:01:53.634546 _llseek(98, 499952, unfinished ...
[pid 4015] 21:01:53.634712 write(98,
\r\0\0\0\1\0033\0\0033\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0...,
1024 unfinished ...
[pid 4015] 21:01:53.740378 _llseek(98, 500976, [500976], SEEK_SET) = 0
[pid 4015] 21:01:53.740449 write(98,
\0\0\0\3\0\0\0\0^=\226\213\23\10\247\327\201\32\227\323\f8, 24) =
24
[pid 4015] 21:01:53.740521 _llseek(98, 501000, [501000], SEEK_SET) = 0
[pid 4015] 21:01:53.740566 write(98,
\r\3\221\0\t\3G\1\3\243\3\261\3\352\3\325\3\304\3G\3\177\3m\3\\\0\0\0\0\0\0...,
1024) = 1024
[pid 4015] 21:01:53.740632 _llseek(98, 502024, [502024], SEEK_SET) = 0
[pid 4015] 21:01:53.740677 write(98,
\0\0\37X\0\0\0\0^=\226\213\23\10\247dL\17\316\32\30\301\237, 24) =
24
[pid 4015] 21:01:53.740736 _llseek(98, 502048, [502048], SEEK_SET) = 0
[pid 4015] 21:01:53.740781 write(98,
\r\0\0\0\5\1*\0\3o\2\336\2L\1\273\1*\0\0\0\0\0\0\0\0\0\0\0\0\0\0...,
1024) = 1024
[pid 4015] 21:01:53.740844 _llseek(98, 503072, [503072], SEEK_SET) = 0
[pid 4015] 21:01:53.740889 write(98,
\0\0HR\0\0HU^=\226\213\23\10\247\276\32g\304j\372Q., 24) = 24
[pid 4015] 21:01:53.740945 _llseek(98, 503096, [503096], SEEK_SET) = 0
[pid 4015] 21:01:53.740989 write(98,
\n\0\0\0\25\1\264\0\2@\2\\\2x\2\224\2\260\2\314\2\350\3\4\3
\3\3X\3t..., 1024) = 1024
[pid 4015] 21:01:54.636566 _llseek(98, 504120, [504120], SEEK_SET) = 0
[pid 4015] 21:01:54.636636 write(98,
\0\0\0\4\0\0HU^=\226\213\23\10\247=]`\3700\351\226n, 24) = 24
[pid 4015] 21:01:54.636812 _llseek(98, 504144, [504144], SEEK_SET) = 0
[pid 4015] 21:01:54.636860 write(98,
\r\0\0\0\1\0033\0\0033\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0...,
1024 unfinished ...
[pid 6744] 21:01:54.676590 close(98) = 0
Regards
Keith
___
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] Tracing latencies
I'm a bit confused with always aligned. None of the lseeks in this log are 1024 aligned. And I just ran a test with the 3.7.15.1 latest amalgamation and most of these seeks are not aligned. Once in a while it gets lucky. Alignment sure isn't deliberate in this. It appears the first page is 1080 which is already out of alignment. File#4 here is the wal file. lseek(4, 0, SEEK_SET) = 0 lseek(4, 0, SEEK_SET) = 0 lseek(4, 32, SEEK_SET) = 32 lseek(4, 56, SEEK_SET) = 56 lseek(4, 1080, SEEK_SET)= 1080 lseek(4, 1104, SEEK_SET)= 1104 lseek(4, 2128, SEEK_SET)= 2128 lseek(4, 2152, SEEK_SET)= 2152 lseek(4, 3176, SEEK_SET)= 3176 lseek(4, 3200, SEEK_SET)= 3200 Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Dan Kennedy [danielk1...@gmail.com] Sent: Wednesday, December 05, 2012 10:27 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Tracing latencies On 12/05/2012 09:03 PM, Black, Michael (IS) wrote: Hmmm...looking at that strace sequencing is there some reason those 24-byte and 1024-byte writes can't be combined? The 1024-byte write is occurring at the end boundary of the 24-byte. That would cut the i/o ops in half and might be a noticeable improvement. A memory copy would be a lot faster than 2 i/o requests. And since disk page size is always a power of 2 would the 1024 buffer be better off as 1000 to align the page i/o better? Those writes are appending to the WAL file. Writes to the database file are always aligned page-sized (in this case 1024 byte) blocks. For the WAL file, we tried it both ways (combining the 24 and 1024 bytes writes into one and leaving them separate) and found that, on linux, it's faster to call write() twice. In other words, the extra write() call is cheaper than doing a 1048 byte memcpy(). And you can't just use 1048 byte buffers everywhere internally, as memory allocators tend to waste lots of space if you allocate many blocks that are all just a bit larger than a power-of-two. Having said that, there has been at least one closed-source VFS backend that buffers the sequential writes SQLite makes on the journal and WAL files so that it can make mostly 8KB aligned writes to the underlying file-system. So on some systems there is a benefit to writing aligned page blocks even if you are writing sequentially. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Keith Chew [keith.c...@gmail.com] Sent: Wednesday, December 05, 2012 2:11 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Tracing latencies Hi Dan On Wed, Dec 5, 2012 at 6:38 PM, Dan Kennedydanielk1...@gmail.com wrote: If it's not fsync() then IO delays are normally caused by read(). You could try [strace -T -eread ...] to check. Are SELECT statements fast on the same database? How large is the database compared to the machines memory? Ah, do you think the read on the OS level is starving the writes? There are not many DB selects from the app, but there are other read IO activities happening in the background. Still it doesn't make sense, because WAL mode ensures from sqlite's point of view, the reader will not be blocked by the writer. So, sqlite is subjected to similar levels of read IO activity as Mysql (because there are very few DB selects). The tables are small, around 20MB in total, compared to 2GB of memory available. I have done more investigation. Using strace, I waited to capture an insert/update with a long latency, and caught one that is 2s long, below is the strace to the WAL file. We can see that it is very bursty, all the seeks and writes span close to over 1s. I recall doing a strace on Mysql a long time ago, and each insert only does a single seek and write. Maybe it is sqlite's file format that requires it to seek/write multiple places causing the slowness? I am only guessing here, as I do not know how to interpret the strace logs below. [pid 4015] 21:01:53.634099 _llseek(98, 499928,unfinished ... [pid 4015] 21:01:53.634245 write(98, \0\0\0\4\0\0HU^=\226\213\23\10\247+\214\332\260\314Wf , 24 unfinished ... [pid 4015] 21:01:53.634546 _llseek(98, 499952,unfinished ... [pid 4015] 21:01:53.634712 write(98, \r\0\0\0\1\0033\0\0033\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0..., 1024unfinished ... [pid 4015] 21:01:53.740378 _llseek(98, 500976, [500976], SEEK_SET) = 0 [pid 4015] 21:01:53.740449 write(98
Re: [sqlite] Tracing latencies
Could it be waitiing on the prior transaction though? Since disk I/O lies it might be syncing the last transaction causing the new one to wait longer. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Keith Chew [keith.c...@gmail.com] Sent: Tuesday, December 04, 2012 3:45 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Tracing latencies On Wed, Dec 5, 2012 at 10:28 AM, Keith Chew keith.c...@gmail.com wrote: I wonder what could be causing sqlite to hang so long? Will try to remove all indexes to see if that narrows things down. It is not an indexing issue. For one of the UPDATE SQLs, it is updating a table with only 1 record in it. And this takes 350ms... All the other tables have only 1 or 2 indexes, so should not be impacting the inserts/updates. Regards Keith ___ 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] Tracing latencies
Can you re-run your strace as strace -tt and look at the timings to help pinpoint it? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Keith Chew [keith.c...@gmail.com] Sent: Tuesday, December 04, 2012 5:00 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Tracing latencies On Wed, Dec 5, 2012 at 11:10 AM, Keith Chew keith.c...@gmail.com wrote: The strange thing is that I am setting sqlite it to use WAL, autocheckpoint off and synchronous off. Even in this setup, I still see 350ms transactions times for less than 3 TPS. A bit hard to believe, so I am now doing a strace to find out exactly what is hitting the disk. II can confirm using strace that there are no fsyncs happening from the application (which we expect because synchronous=0). So, it must be something else that is causing these blocks. Somekind of file locking issue (particular to my environment)? What else should I be looking at? Regards Keith ___ 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 design preferences
One of my considerations would be whether or not the fields are 1-to-1 to the user or are non-related. In your list for example favorite politician is something non-related to the user and you might want to implement either as a search function or a pulldown list or a tabulated page. So normalizing that to it's own table and putting a foreign key in your user table makes sense and would make maintenance easier (combining duplicate names and such) and GUI entry. Name is relatively unique so leave it alone Shoe size is just a byte so not worth normalizing and probably isn't queried much. phone is unique and also not queried much. address is mostly unique (several people at same address) so you wouldn't save much by normalizing. Why normalize: 1. Query Performance 2. Data loading performance 3. Ease of maintenance 4. When data integrity is less of a concern (such as in read-only databases) and query performance is a higher priority Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Staffan Tylen [staffan.ty...@gmail.com] Sent: Friday, November 30, 2012 9:50 AM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] Database design preferences I'm looking for both administrative and technical advice on the pros and cons of either creating one single database table with many columns or creating multiple tables with fewer but related columns to be JOINed when needed. Assume that the data is all related 1-to-1, like name, home address, primary phone, shoe size, favourite politician (NULL accepted!), etc. At a first glance it seems logical to select a single table as it simplifies access to the data but there may be good reasons that I'm not aware of to split the data over multiple tables. I have only limited experience of SQL so any guidelines are appreciated. Thanks in advance. Staffan ___ 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] Windows (slow) vs. iOS/OSX (fast) Performance
Could this be your problem? http://mattgadient.com/2011/02/18/mac-os-x-slow-for-10-15-minutes-after-boot-the-fix/ Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of David de Regt [dav...@mylollc.com] Sent: Friday, November 30, 2012 11:41 AM To: General Discussion of SQLite Database Subject: EXT :[sqlite] Windows (slow) vs. iOS/OSX (fast) Performance Hey all. I've been struggling with a basic perf issue running the same code on Windows vs. iOS and OSX. Basic query set: CREATE TABLE test (col1 int, col2 text); [loop 500 times]: INSERT INTO TEST (col1,col2) VALUES (4,'test4') I'm coding this using the default C amalgamation release and using prepare/etc. on all platforms in the exact same way (same very simple DB-access class I made). I realize that using a transaction around this would vastly improve perf, but given the atomic nature of the app that this test is simulating, it won't work to wrap it into transactions, so my goal is to improve the atomic performance. These are all being run on the same Macbook Pro, with an SSD, running Windows via boot camp, OSX natively, and iOS via the iOS simulator: With defaults (pragma sync = on, default journal_mode): Windows: 2500ms iOS: 300ms OSX: 280ms With pragma sync = off, journal_mode = memory: Windows: 62ms iOS: 25ms OSX: 25ms Turning off sync doesn't make me feel warm and fuzzy about our lost-power scenario, so with sync on, it seems like something must be fishy for it to be ~8-9x slower than the other platforms. Is there something ridiculous about the windows file system performance that hoses sqlite's open/read/write/close transaction cycle? Is there anything I can do, or just accept it and move on? With how that scales up, we may need to move to something like using embedded MySQL or LocalDB on Windows to get the same performance as we see with SQLite on other platforms, which seems quite ridiculous. 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] Windows (slow) vs. iOS/OSX (fast) Performance
If you'd care to share your code I can test it on XP-64 and Windows 7 to see if I can duplicate your problem. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of David de Regt [dav...@mylollc.com] Sent: Friday, November 30, 2012 11:50 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance Nope, I ran the tests both in Parallels and rebooting directly into boot camp (basically native windows), and had essentially identical performance (+/- 2%, within noise level differences). It also echoes the performance difference I'd been seeing on the database side just watching the real app run on iOS and on my other non-Apple native windows box. Interesting little find, nonetheless, thanks for that. :) To Alex: Unfortunately, Windows is a core platform for us. We can't really just tell them to buzz off, so it's either figure out how to improve SQLite performance or switch DB engines, at least on that platform... -David From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Black, Michael (IS) [michael.bla...@ngc.com] Sent: Friday, November 30, 2012 9:46 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance Could this be your problem? http://mattgadient.com/2011/02/18/mac-os-x-slow-for-10-15-minutes-after-boot-the-fix/ Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of David de Regt [dav...@mylollc.com] Sent: Friday, November 30, 2012 11:41 AM To: General Discussion of SQLite Database Subject: EXT :[sqlite] Windows (slow) vs. iOS/OSX (fast) Performance Hey all. I've been struggling with a basic perf issue running the same code on Windows vs. iOS and OSX. Basic query set: CREATE TABLE test (col1 int, col2 text); [loop 500 times]: INSERT INTO TEST (col1,col2) VALUES (4,'test4') I'm coding this using the default C amalgamation release and using prepare/etc. on all platforms in the exact same way (same very simple DB-access class I made). I realize that using a transaction around this would vastly improve perf, but given the atomic nature of the app that this test is simulating, it won't work to wrap it into transactions, so my goal is to improve the atomic performance. These are all being run on the same Macbook Pro, with an SSD, running Windows via boot camp, OSX natively, and iOS via the iOS simulator: With defaults (pragma sync = on, default journal_mode): Windows: 2500ms iOS: 300ms OSX: 280ms With pragma sync = off, journal_mode = memory: Windows: 62ms iOS: 25ms OSX: 25ms Turning off sync doesn't make me feel warm and fuzzy about our lost-power scenario, so with sync on, it seems like something must be fishy for it to be ~8-9x slower than the other platforms. Is there something ridiculous about the windows file system performance that hoses sqlite's open/read/write/close transaction cycle? Is there anything I can do, or just accept it and move on? With how that scales up, we may need to move to something like using embedded MySQL or LocalDB on Windows to get the same performance as we see with SQLite on other platforms, which seems quite ridiculous. 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 ___ 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] Converting in-memory sqlite database to char array
And if you want to improve latency you can use fifo's on Unix or anonymous pipes on Windows and run a thread to send your data while it's writing since those methods are synchronous. man popen (you open write in one thread and open a read in another) http://msdn.microsoft.com/en-us/library/windows/desktop/aa365141%28v=vs.85%29.aspx On Windows you get 2 handles that you pass the read handle to your other thread. Remember to send a 2nd item (last packet) with how many bytes you sent so the client knows it got what it was supposed to. Otherwise you're sure to get a truncated db some time and die on the client. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Eric Minbiole [eminbi...@gmail.com] Sent: Thursday, November 29, 2012 7:53 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Converting in-memory sqlite database to char array As a first (simple) approach, I might use the standard backup API to back up to a temp file, then stream that file byte by byte over the communication protocol. I'm sure there may be other more direct-to-memory approaches, perhaps using a custom VFS. However, this approach should be simple and easy, and would not require any special serialization library-- just standard file I/O. On Thu, Nov 29, 2012 at 8:19 AM, Map Scape halukcy...@gmail.com wrote: Hi all, I have an in-memory sqlite database which I want to convert to a simple char array, to send over a communication protocol. I want to do this preferably without using any serialization library. Basically I want to do what backup api calls does, but instead of copying database to another database, I will be copying it to a char array/string/stream (whatever you may call 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Converting in-memory sqlite database to char array
I thought a backup was using a snapshot and locking the database? Hadn't considered random access though which I'd wager it does do on write. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Jay A. Kreibich [j...@kreibi.ch] Sent: Thursday, November 29, 2012 8:37 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Converting in-memory sqlite database to char array On Thu, Nov 29, 2012 at 02:05:02PM +, Black, Michael (IS) scratched on the wall: And if you want to improve latency you can use fifo's on Unix or anonymous pipes on Windows and run a thread to send your data while it's writing since those methods are synchronous. I would not assume the backup API writes the file front to back, especially if the database is modified while the backup is taking place. A custom VFS that just writes the file to a big chunk of memory makes the most sense. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ 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 3.4.1 write performance difference between linux kernel 2.6.33 and 3.4.6
The Linux kernel used to default to writeback for a while until 2.6.36 where it
then defaulted to ordered.
So you're seeing the ordered behavior now which is the safest mode.
http://forum.linode.com/viewtopic.php?t=7815
How to convert to writeback if that's what you want...it is a more dangerous
option.
http://ubuntuforums.org/showthread.php?t=107856
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Kevin Liao [kevin...@gmail.com]
Sent: Thursday, November 29, 2012 11:47 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Sqlite 3.4.1 write performance difference between linux
kernel 2.6.33 and 3.4.6
I have a simple propram that issues sqlite update command every few seconds.
The platform is linux based with kernel 2.6.33 and sqlite version is 3.4.1.
The db file is on the partition with EXT3 format. Usually it takes only 11-13
ms to execute the update commands. Recently I upgrade the kernel to 3.4.6 but
find one problem. It takes about 43-51 ms to finish the update command now.
That is, the write performance is almost four times slower that kernel 2.6.33.
The following is source code of the function I used for updating
sqlite. Is there
anything I do wrong or does anyone have the similar problem? Thanks a lot.
int my_db_update_progress(int value)
{
sqlite3* db;
char* zSQL = NULL;
int ret = 0;
int changed = 0;
zSQL = sqlite3_mprintf(UPDATE MY_TASK SET progress = %d WHERE \
task_pid = %d;, value, getpid());
ret = sqlite3_open(/etc/mydb.db, db);
if (ret) {
sqlite3_free(zSQL);
return -1;
}
sqlite3_busy_timeout(db, 2);
ret = sqlite3_exec(db, zSQL, NULL, NULL, NULL);
if (sqlite3_total_changes(db))
changed = 1;
sqlite3_close(db);
sqlite3_free(zSQL);
if (ret != SQLITE_OK || !changed)
return -1;
return 0;
}
Regards,
Kevin
___
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] Replace on fts4 table results in unexpected matchinfo result
Does this make it weirder or what? If you do the replace after the insert you get the expected result. But if you do the replace, followed by 2 more inserts you get this: SQLite version 3.7.14.1 2012-10-04 19:37:12 Enter .help for instructions Enter SQL statements terminated with a ; sqlite CREATE VIRTUAL TABLE IF NOT EXISTS fts USING fts4(body); sqlite REPLACE INTO fts ( docid, body ) VALUES (1, one two three four); sqlite REPLACE INTO fts ( docid, body ) VALUES (2, one two); sqlite SELECT quote(matchinfo(fts,'na')) FROM fts WHERE fts.body match 'three'; X'01000600' sqlite INSERT INTO fts ( docid, body ) VALUES (3, one two three four); sqlite REPLACE INTO fts ( docid, body ) VALUES (4, one two); sqlite SELECT quote(matchinfo(fts,'na')) FROM fts WHERE fts.body match 'three'; X'03000400' X'03000400' 3 rows in table and average columns is 4 now??? Should this still be 4/3 ?? And if you do the INSERT followed by REPLACE you get this which is what you expect. X'04000300' Should this be order dependent? Seems all you need is 1 insert at the beginning and all is as expected. CREATE VIRTUAL TABLE IF NOT EXISTS fts USING fts4(body); INSERT INTO fts ( docid, body ) VALUES (1, one two three four); REPLACE INTO fts ( docid, body ) VALUES (2, one two); REPLACE INTO fts ( docid, body ) VALUES (3, one two three four); REPLACE INTO fts ( docid, body ) VALUES (4, one two); SELECT quote(matchinfo(fts,'na')) FROM fts WHERE fts.body match 'three'; X'04000300' And does this help explain it? Malformed DB after the first REPLACE? SQLite version 3.7.14.1 2012-10-04 19:37:12 Enter .help for instructions Enter SQL statements terminated with a ; sqlite CREATE VIRTUAL TABLE IF NOT EXISTS fts USING fts4(body); sqlite REPLACE INTO fts ( docid, body ) VALUES (1, one two three four); sqlite SELECT quote(matchinfo(fts,'na')) FROM fts WHERE fts.body match 'three'; Error: database disk image is malformed sqlite REPLACE INTO fts ( docid, body ) VALUES (2, one two); sqlite SELECT quote(matchinfo(fts,'na')) FROM fts WHERE fts.body match 'three'; X'01000600' sqlite REPLACE INTO fts ( docid, body ) VALUES (3, one two three four); sqlite SELECT quote(matchinfo(fts,'na')) FROM fts WHERE fts.body match 'three'; X'02000500' X'02000500' sqlite REPLACE INTO fts ( docid, body ) VALUES (4, one two); sqlite SELECT quote(matchinfo(fts,'na')) FROM fts WHERE fts.body match 'three'; X'03000400' X'03000400' Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Eric [ergo...@gmail.com] Sent: Monday, November 26, 2012 10:42 PM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] Replace on fts4 table results in unexpected matchinfo result The following SQL results in X'01000600'. For reference, na option should generate total number of documents and the average number of tokens per document. CREATE VIRTUAL TABLE IF NOT EXISTS fts USING fts4(body); REPLACE INTO fts ( docid, body ) VALUES (1, one two three four); REPLACE INTO fts ( docid, body ) VALUES (2, one two); SELECT quote(matchinfo(fts,'na')) FROM fts WHERE fts.body match 'three'; If REPLACE is replaced with INSERT in the above, the result is X'02000300', as expected. In either case, the number of rows in fts is as expected, body column is correct, and other matchinfo options (pclx at least), seem to function correctly. Is REPLACE not allowed for fts4 tables, or is this a bug? Output of .version in sqlite3: SQLite 3.7.13 2012-06-11 02:05:22 f5b5a13f7394dc143aa136f1d4faba6839eaa6dc -- Eric ___ 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] Anomalously slow performance on updates to early entries in a DB
Hmmm...is this a disk head seeking problem?
You've got several TEXT entries which are either NULL or default to '';
I did a small experiment: on 3.7.13
CREATE TABLE container_stat (
account TEXT,
container TEXT,
created_at TEXT,
put_timestamp TEXT DEFAULT '0',
delete_timestamp TEXT DEFAULT '0',
object_count INTEGER,
bytes_used INTEGER,
reported_put_timestamp TEXT DEFAULT '0',
reported_delete_timestamp TEXT DEFAULT '0',
reported_object_count INTEGER DEFAULT 0,
reported_bytes_used INTEGER DEFAULT 0,
hash TEXT default '',
id TEXT,
status TEXT DEFAULT '',
status_changed_at TEXT DEFAULT '0',
metadata TEXT DEFAULT '',
x_container_sync_point1 INTEGER DEFAULT -1,
x_container_sync_point2 INTEGER DEFAULT -1);
insert into container_stat(id,status,status_changed_at)
values('id1','status1','');
insert into container_stat(id,status,status_changed_at)
values('id2','status2','');
insert into container_stat(id,status,status_changed_at)
values('id3','status3','');
insert into container_stat(id,status,status_changed_at)
values('id4','status4','');
insert into container_stat(id,status,status_changed_at)
values('id5','status5','');
delete from container_stat where account='id1';
insert into container_stat(account,status,status_changed_at)
values('id1','status1 change1 to something else','status_changhed_at_1');
If you look at the order of data BEFORE the delete/insert occurs you get this
(using strings);
id5status5
id4status4
id3status3
id2status2
id1status1
After a delete/insert where one of the fields grows in size you get this:
id1status1 change1 to something
elsestatus_changed_at_1
id5status5
id4status4
id3status3
id2status2
id1status1
So...the probability of an update needing to move due to larger data increases
the closer you are to the beginning of the database. Each update would reduce
the likelihood of that record getting relocated again as the fields grow in
size.
If you use default values that are 2 sigma of the sizes of your strings (or
maybe just 2X the average length or so) you would reduce the head seek time on
updating records.
It basically sounds possibly like your disk head is slewing from the beginning
of the file to the end on many of your updates.
Also...would insert or update help you a bit?
http://www.sqlite.org/lang_conflict.html
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of O'Toole, Eamonn [eamonn.oto...@hp.com]
Sent: Friday, November 09, 2012 5:07 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Anomalously slow performance on updates to early entries
in a DB
Hello all,
First of all, I'm a complete novice with respect to SQLite so apologies if
there is an obvious answer to my question. I've also posted this question in
IRC, and it was suggested that I post the question to this mailing list. We're
running a test-bed of an object store (Openstack Swift) which uses SQLite to
record information on the objects that are stored in a container. The table
itself (called object) is very simple, it contains the modification time of
the object, the size of object, the md5sum of the object, and the content-type
of the object. We are seeing a performance anomaly on updates to existing
object records in the SQLite DB. If the container DB is sufficiently large
(about 10 million objects, 3.3GB) then the time to update records at the
beginning of the database by order of entry is anomalously high. The time is
particularly bad for the first approx. 100K records, is somewhat better for the
next 900K records, and settles down to a consistent average from
approx 1 million records on. If this consistent average time is around 7
seconds for 10,000 updates, then we see times of about 170 seconds for 10,000
updates on the first 100K records. We don't see this anomalously high update
time if we start the updates after the first 1 million records.
Note that table updates are performed by first DELETEing the entry and then
INSERTing the changed entry.
Does anybody have any idea why we're seeing this behaviour, and what we can do
to fix it?
Note
Re: [sqlite] Anomalously slow performance on updates to earlyentries in a DB
[O'Toole, Eamonn] This definitely sounds like it could be an issue. There is just one container_stat entry confirmed by sqlite3_analyzer output which I'll post later). So you're saying that the single container_stat table :entry is potentially being relocated very frequently the closer the update is to the beginning of the db? Yesyou didn't say what your data flow is...but since it's account-based I assume you have a bunch of accounts that get preloaded. So the first n-thousand records are Size1. You then start updating each of those...none of them are big enough...the new records get inserted at the first available empty slot (is that actually how this works or is there another row allocation strategy?). Now you go to update those records again...they can't fit in the 1st block...and some percentage of the data won't fit into the 2nd block (depends on the variability in size). So, let's say half the records get relocatedeventually you reach homeostasis. Also...disk fragmentation could be affecting you too but I wouldn't expect an order of magnitude difference on that. Are you on Windows or Unix? Also...are your records indexed for the updates? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Anomalously slow performance on updates to earlyentries in a DB
What I would do is find the max length of your data fields.
Then dump the database, change the create table to use default values at those
string lengths.
Import it.
See what that does for you. Or just reload your data the way you've been doing
with the new default string lengths.
Also a compound index on name/created_at could help you a lot.
And since you're in a trigger I'm not sure if the insert or replace would help
since that's essentially what you're doing anyways and I believe that's all
wrapped inside a transaction inside triggers. Somebody please correct me if
this is not true.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of O'Toole, Eamonn [eamonn.oto...@hp.com]
Sent: Friday, November 09, 2012 8:53 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Anomalously slow performance on updates to
earlyentries in a DB
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: 09 November 2012 14:26
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Anomalously slow performance on updates to
earlyentries in a DB
[O'Toole, Eamonn] This definitely sounds like it could be an issue.
There is just one container_stat entry confirmed by sqlite3_analyzer
output which I'll post later). So you're saying that the single
container_stat table :entry is potentially being relocated very
frequently the closer the update is to the beginning of the db?
Yesyou didn't say what your data flow is...but since it's account-
based I assume you have a bunch of accounts that get preloaded.
[O'Toole, Eamonn] In Swift the db is used to store information on the
containers. The hierarchy in Swift is account-container-object. The test
targets a specific container of a specific account, which contains information
on 10 million objects, and changes the created_at field for the first 2
million entries. First we create the container, add the 10 million entries to
it, then we start the update cycle. We see this slow performance on every
update run, although if you run updates in succession without any break in
between runs you do see an improvement in performance due to caching.
So the first n-thousand records are Size1.
You then start updating each of those...none of them are big
enough...the new records get inserted at the first available empty slot
(is that actually how this works or is there another row allocation
strategy?).
[O'Toole, Eamonn] The SQL transactions are done through python. This is the
relevant section of code that deals with the object table, the container_stat
table is updated by the triggers:
for rec in item_list:
query = '''
DELETE FROM object
WHERE name = ? AND (created_at ?)
'''
if self.get_db_version(conn) = 1:
query += ' AND deleted IN (0, 1)'
conn.execute(query, (rec['name'], rec['created_at']))
query = 'SELECT 1 FROM object WHERE name = ?'
if self.get_db_version(conn) = 1:
query += ' AND deleted IN (0, 1)'
if not conn.execute(query, (rec['name'],)).fetchall():
conn.execute('''
INSERT INTO object (name, created_at, size,
content_type, etag, deleted)
VALUES (?, ?, ?, ?, ?, ?)
''', ([rec['name'], rec['created_at'], rec['size'],
rec['content_type'], rec['etag'], rec['deleted']]))
Now you go to update those records again...they can't fit in the 1st
block...and some percentage of the data won't fit into the 2nd block
(depends on the variability in size). So, let's say half the records
get relocatedeventually you reach homeostasis.
Also...disk fragmentation could be affecting you too but I wouldn't
expect an order of magnitude difference on that.
[O'Toole, Eamonn] Disk fragmentation is definitely a factor, but as you say
defragging doesn't get you an order of magnitude improvement.
Are you on Windows or Unix?
[O'Toole, Eamonn] Linux (Ubuntu)
Also...are your records indexed for the updates?
[O'Toole, Eamonn] The only object table index that I can see is the
ix_deleted_name index, and that isn't used by the table update logic.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXT :Re: Compiling SQLite3 with MSVC 2010
For gcc try -Wextra and -Wconversion. You'll get tons of warnings. -Wall just does the ones most people are concerned with. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Korot [ikoro...@gmail.com] Sent: Sunday, November 04, 2012 2:12 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] EXT :Re: Compiling SQLite3 with MSVC 2010 Michael, On Sun, Nov 4, 2012 at 5:59 AM, Black, Michael (IS) michael.bla...@ngc.com wrote: Hmmm...interesting...I'm using VS 2010 Express 32-bit and I would've assumed the warnings would match. I also enabled SQLITE_64BIT_STATS to try and force the first warning but that didn't cause it. Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 16.00.40219.01 for 80x86 Well, I'm getting it with Professional build of 32-bits. That's a bit disconcerting actually but I guess Express is less pedantic than Studio. You can always stick this in to shut it up. There's a yin yang to fixing thesesimple enough to throw a cast in there...but down the road if you make other changes on the right-hand-side datatype that could be of use so you would be suppressing a valid warning. It's a mixed bag. So suppressing warnings from picky compilers is the best way to go. Do you still see then on lower levels? Not with /W3. But it's interesting that -Wall does not produce them with gcc... Thank you. #pragma warning(disable: 4244) // possible loss of data Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Jonas Malaco Filho [jonasmalacofi...@gmail.com] Sent: Saturday, November 03, 2012 3:48 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Compiling SQLite3 with MSVC 2010 Actually, on MSVC 2010 I just got the following errors with /W3: -- Rebuild All started: Project: Shell, Configuration: Release x64 -- shell.c sqlite3.c ..\src\sqlite3.c(78502): warning C4244: 'initializing' : conversion from 'sqlite_int64' to 'tRowcnt', possible loss of data ..\src\sqlite3.c(78503): warning C4244: 'initializing' : conversion from 'sqlite_int64' to 'tRowcnt', possible loss of data ..\src\sqlite3.c(78504): warning C4244: 'initializing' : conversion from 'sqlite_int64' to 'tRowcnt', possible loss of data ..\src\sqlite3.c(104145): warning C4244: '=' : conversion from 'i64' to 'double', possible loss of data ..\src\sqlite3.c(104170): warning C4244: '=' : conversion from 'i64' to 'double', possible loss of data Generating code Finished generating code Shell.vcxproj - X:\jonas-malaco-filho\lib\SQLite\Shell\..\bin\x86-64\sqlite3.exe -- Rebuild All started: Project: Shell, Configuration: Release Win32 -- shell.c sqlite3.c ..\src\sqlite3.c(78502): warning C4244: 'initializing' : conversion from 'sqlite_int64' to 'tRowcnt', possible loss of data ..\src\sqlite3.c(78503): warning C4244: 'initializing' : conversion from 'sqlite_int64' to 'tRowcnt', possible loss of data ..\src\sqlite3.c(78504): warning C4244: 'initializing' : conversion from 'sqlite_int64' to 'tRowcnt', possible loss of data ..\src\sqlite3.c(104145): warning C4244: '=' : conversion from 'i64' to 'double', possible loss of data ..\src\sqlite3.c(104170): warning C4244: '=' : conversion from 'i64' to 'double', possible loss of data Generating code Finished generating code Shell.vcxproj - X:\jonas-malaco-filho\lib\SQLite\Shell\..\bin\x86\sqlite3.exe == Rebuild All: 2 succeeded, 0 failed, 0 skipped == *Jonas Malaco Filho* 2012/11/3 Black, Michael (IS) michael.bla...@ngc.com You probably have the warning level turned up high. Up to level 3 it compiles without warnings. Level 4 starts complaining loudly. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Korot [ikoro...@gmail.com] Sent: Friday, November 02, 2012 6:25 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Compiling SQLite3 with MSVC 2010 Richard, On Fri, Nov 2, 2012 at 4:14 PM, Richard Hipp d...@sqlite.org wrote: On Fri, Nov 2, 2012 at 7:05 PM, Igor Korot ikoro...@gmail.com wrote: Hi, ALL, Is anybody trying to compile SQLite with MSVC 2010? Tests 9e and 9f at http://www.sqlite.org/checklists/3071400#c9 were performed using MSVC 2010. I am getting a lot of warnings. Is there any interest in fixing
Re: [sqlite] SUGGESTION: now as alias for strftime('%s','now')
If speed and storage are a concern then as somebody else notedjulianday is
the way to go.
Just don't confuse CURRENT_TIMESTAMP with CURRENT_TIME -- you can still extract
just date from the field if you need it.
sqlite create table t(id,time);
sqlite insert into t values(1,julianday(CURRENT_TIMESTAMP));
sqlite .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t(id,time);
INSERT INTO t VALUES(1,2456236.05462963); -- you can see storage mode is
double
COMMIT;
sqlite select id,date(time) from t;
1|2012-11-04
sqlite select id,datetime(time) from t;
1|2012-11-04 13:18:40
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Григорий Григоренко [grigore...@mail.ru]
Sent: Sunday, November 04, 2012 1:34 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite]SUGGESTION: now as alias for strftime('%s','now')
Fri, 2 Nov 2012 14:11:26 + от Black, Michael (IS)
michael.bla...@ngc.com:
CREATE TABLE t(id,time);
INSERT INTO t VALUES(1,CURRENT_DATE);
INSERT INTO t VALUES(2,CURRENT_TIMESTAMP);
INSERT INTO t VALUES(3,datetime('now'));
INSERT INTO t VALUES(4,date('now'));
SELECT * FROM t;
1|2012-11-02
2|2012-11-02 14:10:15
3|2012-11-02 14:10:15
4|2012-11-02
Perhaps the documentation needs to be better? Apparently you couldn't find
this info...
Indeed, I was never aware of CURRENT_*.
Anyway, all these functions return current moment as _string_ and this is not a
great way to store datetime in db, isn't it?
- more memory occupied;
- slower compare;
- cannot add substract;
etc.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Григорий Григоренко [grigore...@mail.ru]
Sent: Friday, November 02, 2012 8:08 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite]SUGGESTION: now as alias for strftime('%s','now')
Thu, 1 Nov 2012 19:57:42 + от Simon Slavin slav...@bigfraud.org:
On 1 Nov 2012, at 7:55pm, Григорий Григоренко grigore...@mail.ru wrote:
it is a common practice to store datetime values as UNIX time UTC.
Maybe, Sqlite should have some shortcut for evaluating current moment?
Please read
http://www.sqlite.org/lang_datefunc.html
Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking
functions that modify or format date values.
It's about having useful shortcut for getting current moment that doesn't have
(string) parameters and so can be easily remembered and typed.
Compare:
MS SQL: CURRENT_TIMESTAMP
PostgreSQL: now()
Oracle: sysdate
To:
Sqlite: strftime('%s','now')
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] EXT :Re: Compiling SQLite3 with MSVC 2010
Hmmm...interesting...I'm using VS 2010 Express 32-bit and I would've assumed the warnings would match. I also enabled SQLITE_64BIT_STATS to try and force the first warning but that didn't cause it. Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 16.00.40219.01 for 80x86 That's a bit disconcerting actually but I guess Express is less pedantic than Studio. You can always stick this in to shut it up. There's a yin yang to fixing thesesimple enough to throw a cast in there...but down the road if you make other changes on the right-hand-side datatype that could be of use so you would be suppressing a valid warning. It's a mixed bag. So suppressing warnings from picky compilers is the best way to go. Do you still see then on lower levels? #pragma warning(disable: 4244) // possible loss of data Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Jonas Malaco Filho [jonasmalacofi...@gmail.com] Sent: Saturday, November 03, 2012 3:48 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Compiling SQLite3 with MSVC 2010 Actually, on MSVC 2010 I just got the following errors with /W3: -- Rebuild All started: Project: Shell, Configuration: Release x64 -- shell.c sqlite3.c ..\src\sqlite3.c(78502): warning C4244: 'initializing' : conversion from 'sqlite_int64' to 'tRowcnt', possible loss of data ..\src\sqlite3.c(78503): warning C4244: 'initializing' : conversion from 'sqlite_int64' to 'tRowcnt', possible loss of data ..\src\sqlite3.c(78504): warning C4244: 'initializing' : conversion from 'sqlite_int64' to 'tRowcnt', possible loss of data ..\src\sqlite3.c(104145): warning C4244: '=' : conversion from 'i64' to 'double', possible loss of data ..\src\sqlite3.c(104170): warning C4244: '=' : conversion from 'i64' to 'double', possible loss of data Generating code Finished generating code Shell.vcxproj - X:\jonas-malaco-filho\lib\SQLite\Shell\..\bin\x86-64\sqlite3.exe -- Rebuild All started: Project: Shell, Configuration: Release Win32 -- shell.c sqlite3.c ..\src\sqlite3.c(78502): warning C4244: 'initializing' : conversion from 'sqlite_int64' to 'tRowcnt', possible loss of data ..\src\sqlite3.c(78503): warning C4244: 'initializing' : conversion from 'sqlite_int64' to 'tRowcnt', possible loss of data ..\src\sqlite3.c(78504): warning C4244: 'initializing' : conversion from 'sqlite_int64' to 'tRowcnt', possible loss of data ..\src\sqlite3.c(104145): warning C4244: '=' : conversion from 'i64' to 'double', possible loss of data ..\src\sqlite3.c(104170): warning C4244: '=' : conversion from 'i64' to 'double', possible loss of data Generating code Finished generating code Shell.vcxproj - X:\jonas-malaco-filho\lib\SQLite\Shell\..\bin\x86\sqlite3.exe == Rebuild All: 2 succeeded, 0 failed, 0 skipped == *Jonas Malaco Filho* 2012/11/3 Black, Michael (IS) michael.bla...@ngc.com You probably have the warning level turned up high. Up to level 3 it compiles without warnings. Level 4 starts complaining loudly. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Korot [ikoro...@gmail.com] Sent: Friday, November 02, 2012 6:25 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Compiling SQLite3 with MSVC 2010 Richard, On Fri, Nov 2, 2012 at 4:14 PM, Richard Hipp d...@sqlite.org wrote: On Fri, Nov 2, 2012 at 7:05 PM, Igor Korot ikoro...@gmail.com wrote: Hi, ALL, Is anybody trying to compile SQLite with MSVC 2010? Tests 9e and 9f at http://www.sqlite.org/checklists/3071400#c9 were performed using MSVC 2010. I am getting a lot of warnings. Is there any interest in fixing those? No. See http://www.sqlite.org/testing.html#staticanalysis for an explanation. I just read this link. Interesting information. IIUC, all those warnings are harmless and they do not appear on other platforms. Which means that either gcc is more forgiving or that I am trying to compile my application with some very strange configuration. Or maybe it's C++11 that throws the compilation off of track? I'm just trying to understand why those warnings appear and why nobody else see them on other platforms. Thank you. If not what is the policy of using SQLite3 code? I'm using 3.7.14 release. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org
Re: [sqlite] Compiling SQLite3 with MSVC 2010
You probably have the warning level turned up high. Up to level 3 it compiles without warnings. Level 4 starts complaining loudly. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Korot [ikoro...@gmail.com] Sent: Friday, November 02, 2012 6:25 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Compiling SQLite3 with MSVC 2010 Richard, On Fri, Nov 2, 2012 at 4:14 PM, Richard Hipp d...@sqlite.org wrote: On Fri, Nov 2, 2012 at 7:05 PM, Igor Korot ikoro...@gmail.com wrote: Hi, ALL, Is anybody trying to compile SQLite with MSVC 2010? Tests 9e and 9f at http://www.sqlite.org/checklists/3071400#c9 were performed using MSVC 2010. I am getting a lot of warnings. Is there any interest in fixing those? No. See http://www.sqlite.org/testing.html#staticanalysis for an explanation. I just read this link. Interesting information. IIUC, all those warnings are harmless and they do not appear on other platforms. Which means that either gcc is more forgiving or that I am trying to compile my application with some very strange configuration. Or maybe it's C++11 that throws the compilation off of track? I'm just trying to understand why those warnings appear and why nobody else see them on other platforms. Thank you. If not what is the policy of using SQLite3 code? I'm using 3.7.14 release. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-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] SUGGESTION: now as alias for strftime('%s','now')
CREATE TABLE t(id,time);
INSERT INTO t VALUES(1,CURRENT_DATE);
INSERT INTO t VALUES(2,CURRENT_TIMESTAMP);
INSERT INTO t VALUES(3,datetime('now'));
INSERT INTO t VALUES(4,date('now'));
SELECT * FROM t;
1|2012-11-02
2|2012-11-02 14:10:15
3|2012-11-02 14:10:15
4|2012-11-02
Perhaps the documentation needs to be better? Apparently you couldn't find
this info...
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Григорий Григоренко [grigore...@mail.ru]
Sent: Friday, November 02, 2012 8:08 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite]SUGGESTION: now as alias for strftime('%s','now')
Thu, 1 Nov 2012 19:57:42 + от Simon Slavin slav...@bigfraud.org:
On 1 Nov 2012, at 7:55pm, Григорий Григоренко grigore...@mail.ru wrote:
it is a common practice to store datetime values as UNIX time UTC.
Maybe, Sqlite should have some shortcut for evaluating current moment?
Please read
http://www.sqlite.org/lang_datefunc.html
Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking
functions that modify or format date values.
It's about having useful shortcut for getting current moment that doesn't have
(string) parameters and so can be easily remembered and typed.
Compare:
MS SQL: CURRENT_TIMESTAMP
PostgreSQL: now()
Oracle: sysdate
To:
Sqlite: strftime('%s','now')
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
Re: [sqlite] sqlite eclipse
Sounds ilke you're using a 64-bit JDK. Use the 32-bit JDK. That's what the error is telling you64-bit can't load 32-bit DLL. As long as all your code is 32-bit it will run on 32-bit. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Kemayou Nyamen, Carine, WO [carine.kemayounya...@de.bertrandt.com] Sent: Tuesday, October 30, 2012 7:45 AM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] sqlite eclipse Hello, I want to connect to my database sqlite via eclipse. I write a java programm, but It is not possible, I read the Instruction on this site Connecting to SQLite - Eclipsepedia http://wiki.eclipse.org/Connecting_to_SQLite , but when I make test connection, the error is ping failed. By run the java program java.lang.UnsatisfiedLinkError: C:\Users\kemayouc\AppData\Local\Temp\sqlitejdbc.dll: Can't load IA 32-bit .dll on a AMD 64-bit platform . I have Window 7 and 64 Bit Operating System. Thanks in advance for your Help. Mit freundlichen Grüßen i. A. Carine Kemayou Nyamen Elektronik / Software Bertrandt Ingenieurbüro GmbH Krümke 1 D-38479 Tappenbeck Telefon:+49 5366 9611-1845 Telefax: +49 5366 9611-1100 Internet:http://www.bertrandt.com http://www.bertrandt.com/ E-Mail: mailto:carine.kemayounya...@de.bertrandt.com Geschäftsführer: Ulrich Subklew Sitz der Gesellschaft: Tappenbeck, Amtsgericht: Braunschweig, HRB 100280 The contents of this e-mail are confidential. If you are not the named addressee or if this transmission has been addressed to you in error, please notify the sender immediately and then delete this e-mail. Any unauthorized copying and transmission is forbidden. E-mail transmission cannot be guaranteed to be secure. If verification is required, please request a hard copy version. Please note, that incoming e-mail is not checked regularly. This may result in a failure to comply with legal or contractual terms. Therefore it is not sufficient, to send any legal or contractual declarations by e-mail. In no event will Bertrandt be liable to you or any third party for any direct, indirect, consequential, special or exemplary damages or lost profit resulting from this failure. ___ 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 char in sqlite
Here it is with your desire to use system().
The table output you get probably is not going to be formatted the way you like.
You can extend the logic here to put special sequences in the string to then
replace with formatting.
It would really be easier oveall to do this yourself by using the sqlite calls
instead of system().
#include stdio.h
#include stdlib.h
#include string.h
char *str_replace(char *orig, char *rep, char *with) {
char *result; // the return string
char *ins;// the next insert point
char *tmp;// varies
int len_rep; // length of rep
int len_with; // length of with
int len_front; // distance between rep and end of last rep
int count;// number of replacements
if (!orig)
return NULL;
if (!rep || !(len_rep = strlen(rep)))
return NULL;
if ((ins = strstr(orig, rep)) == NULL)
return NULL;
if (!with)
with = ;
len_with = strlen(with);
for (count = 0; (tmp = strstr(ins, rep)); ++count) {
ins = tmp + len_rep;
}
// first time through the loop, all the variable are set correctly
// from here on,
//tmp points to the end of the result string
//ins points to the next occurrence of rep in orig
//orig points to the remainder of orig after end of rep
tmp = result = malloc(strlen(orig) + (len_with - len_rep) * count + 1);
if (!result)
return NULL;
while (count--) {
ins = strstr(orig, rep);
len_front = ins - orig;
tmp = strncpy(tmp, orig, len_front) + len_front;
tmp = strcpy(tmp, with) + len_with;
orig += len_front + len_rep; // move to next end of rep
}
strcpy(tmp, orig);
return result;
}
int main() {
//char *sqlcmd=sqlite3 -html -header t9_engine.db \select
id,partnumber,'img src=\\\'||pic||'\\\ height=220/' from engine where
id7;\ n.html;
FILE *fp;
char buf[65535];
char *sqlcmd=sqlite3 -html -header t9_engine.db \select
id,partnumber,'img src=#quot;'||pic||'#quot; height=222/' from engine where
id7;\ n.html;
system(sqlcmd);
fp = fopen(n.html,r);
while(fgets(buf,sizeof(buf),fp)) {
char *s=str_replace(buf,#quot;,\);
if (s) {strcpy(buf,s);free(s);}
s=str_replace(buf,lt;,);
if (s) {strcpy(buf,s);free(s);}
s=str_replace(buf,gt;,);
if (s) {strcpy(buf,s);free(s);}
printf(%s,buf);
}
fclose(fp);
return 0;
}
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Kees Nuyt [k.n...@zonnet.nl]
Sent: Friday, October 26, 2012 5:08 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] FW: how to select char in sqlite
On Fri, 26 Oct 2012 01:25:24 +,
YAN HONG YE yanhong...@mpsa.com wrote:
char bh1[320];
memset(bh1,0,320);
strcpy(bh1,sqlite3 -html -header t9_engine.db \select
id,partnumber,substr(\'img src=\\'||pic||\'\ height=220/\',1,180) as
img,pcs from engine where id7;\ n.html);
system(bh1); //here couldn't work
error:
sqlite3 -html -header t9_engine.db select id,partnumber,substr('img
src='||pi
c||' height=220/',1,180) as img,pcs from engine where id7; n.htmlError:
n
ear 'img src=': syntax error
'pic' is not recognized as an internal or external command,
operable program or batch file.
The system cannot find the path specified.
char bh1[320];
memset(bh1,0,320);
strcpy(bh1,sqlite3 -html -header t9_engine.db \select
id,partnumber,substr(\'img src=\\'||pic||\'\ height=220/\',1,180) as
img,pcs from engine where id7;\ n.html);
strcpy(bh1,sqlite3 -html -header t9_engine.db \select id,partnumber,'img
src='||pic||' height=220/' as img,pcs from engine where id7;\ n.html);
system(bh1); //here could work
the result is:
TRTD8/TD
TDAA34841687 000 INSONO-SOUS-MOTEUR--/TD
TDlt;img src=C:\t9\images\INSONO-SOUS-MOTEUR.jpg height=220/gt;/TD
//here I wanna add char between 'C:\t9\images\INSONO-SOUS-MOTEUR.jpg'
TD1/TD
/TR
and the best way is change
lt; to
gt; to
You will never get that right. Quoting will always stay a problem.
Forking out from C to a shell is bad practice. Forking out to a DOS
shell is a headache. It's not SQLite related and off topic in this list.
Nevertheless, Michael Black did provide a working solution on Wed, 24
Oct 2012 15:09:24 +, did you read it?
Please have a look at the sample C code I linked to before.
There are more examples there.
http://icculus.org/~chunky/stuff/sqlite3_example/
Good luck!
--
Groet, Cordialement, Pozdrawiam, Regards,
Kees Nuyt
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users
Re: [sqlite] I/O error on creating index with 3.7.14
Hmmm...looks a lot like 32-bit overflow into a 64-bit number. 2^64 18446744073709551616 Your read offset 18446744071873782392 Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Jamie Norrish [ja...@artefact.org.nz] Sent: Friday, October 26, 2012 12:14 AM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] I/O error on creating index with 3.7.14 Using 3.7.14, when creating an index on a 27G database (on the table that contains almost all of the data), I consistently (on Windows XP and Debian GNU/Linux, on three different machines) get a disk I/O error. This does not happen using 3.7.13 (only tested on Debian GNU/Linux), nor does it happen when creating the same index on a smaller (~2G) version of the database. I ran the process under strace; the final relevant lines (as far as I can judge, knowing nothing of this) are: lseek(5, 23934032896, SEEK_SET) = 23934032896 write(5, \231\216\344\271\213\351\235\240\345\261\261\n+\367K\340*\5\2I \1\4\v\3 01\351\276\215\345\202\276\346\271..., 1024) = 1024 lseek(5, 23934033920, SEEK_SET) = 23934033920 write(5, \207\345\210\245\347\267\207\347\264\240\346\211\200\350\254 \202\346\2 11\223\351\274\223\345\274\204\n+\367u\334*\5..., 632) = 632 lseek(5, 0, SEEK_SET) = 0 read(5, 0x7f5462cb06b8, 18446744071873782392) = -1 EFAULT (Bad address) close(5)= 0 The command that causes the error is CREATE INDEX IF NOT EXISTS TextNGramIndex ON TextNGram (text, ngram, size). The database schema is (without the failing index): CREATE TABLE Text ( id INTEGER PRIMARY KEY ASC, filename TEXT UNIQUE NOT NULL, checksum TEXT NOT NULL, label TEXT NOT NULL ); CREATE TABLE TextHasNGram ( text INTEGER NOT NULL REFERENCES Text (id), size INTEGER NOT NULL ); CREATE TABLE TextNGram ( text INTEGER NOT NULL REFERENCES Text (id), ngram TEXT NOT NULL, size INTEGER NOT NULL, count INTEGER NOT NULL ); CREATE UNIQUE INDEX TextHasNGramIndex ON TextHasNGram (text, size); CREATE INDEX TextIndexLabel ON Text (label); Is there more information I should provide as part of a proper bug report, or is this a known issue, or have I missed a trick somewhere? Jamie ___ 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 char in sqlite
You appear to be programming in C so that's what this is...
Here's a complete example where you can control the table formatting yourself.
This is using sqlite3 calls and I made it produce a simple, complete HTML page.
This is, of course, tied to your database due to the specific column names.
It's more work to make it generic.
But this should get you a lot closer to what you really want I hope.
Compile and run like this:
myhtml t9_engine.db select id,partnumber,pic from engine where id7 n.html
#include stdio.h
#include stdlib.h
#include string.h
#include sqlite3.h
void checkrc(int rc,int check,sqlite3 *db)
{
if (rc != check) {
fprintf(stderr,%s\n,sqlite3_errmsg(db));
exit(1);
}
}
void doMySQL(char *dbname, char *sql)
{
sqlite3 *db;
int rc;
sqlite3_stmt *stmt;
rc=sqlite3_open(dbname,db);
checkrc(rc,SQLITE_OK,db);
rc = sqlite3_prepare_v2(db,sql,strlen(sql),stmt,NULL);
checkrc(rc,SQLITE_OK,db);
printf(!DOCTYPE html PUBLIC \-//W3C//DTD HTML 4.0//EN\\n
\http://www.w3.org/TR/REC-html40/strict.dtd\;\n);
printf(html\nhead\ntitleParts List/title/head);
printf(body\n);
printf(table border=1\n);
printf(trthID/ththPart#/ththPicture/th/tr\n);
while((rc=sqlite3_step(stmt))==SQLITE_ROW) {
int id=sqlite3_column_int(stmt,0);
printf(tr\ntd valign=top%d/td\n,id);
char *partnumber = sqlite3_column_text(stmt,1);
printf(td valign=top%s/td\n,partnumber);
char *pic = sqlite3_column_text(stmt,2);
printf(tdimg src=\%s\ height=220 alt=\NOT
FOUND:%s\/td\n,pic,pic);
printf(/tr\n);
}
checkrc(rc,SQLITE_DONE,db);
rc=sqlite3_finalize(stmt);
checkrc(rc,SQLITE_OK,db);
printf(/table\n/body\n/html);
rc = sqlite3_close(db);
checkrc(rc,SQLITE_OK,db);
}
int main(int argc, char *argv[])
{
if (argc !=3) {
fprintf(stderr,Usage: %s database \sql\,argv[0]);
exit(1);
}
doMySQL(argv[1],argv[2]);
return 0;
}
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of YAN HONG YE [yanhong...@mpsa.com]
Sent: Thursday, October 25, 2012 8:25 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] FW: how to select char in sqlite
char bh1[320];
memset(bh1,0,320);
strcpy(bh1,sqlite3 -html -header t9_engine.db \select
id,partnumber,substr(\'img src=\\'||pic||\'\ height=220/\',1,180) as
img,pcs from engine where id7;\ n.html);
system(bh1); //here couldn't work
error:
sqlite3 -html -header t9_engine.db select id,partnumber,substr('img src='||pi
c||' height=220/',1,180) as img,pcs from engine where id7; n.htmlError: n
ear 'img src=': syntax error
'pic' is not recognized as an internal or external command,
operable program or batch file.
The system cannot find the path specified.
char bh1[320];
memset(bh1,0,320);
strcpy(bh1,sqlite3 -html -header t9_engine.db \select
id,partnumber,substr(\'img src=\\'||pic||\'\ height=220/\',1,180) as
img,pcs from engine where id7;\ n.html);
strcpy(bh1,sqlite3 -html -header t9_engine.db \select id,partnumber,'img
src='||pic||' height=220/' as img,pcs from engine where id7;\ n.html);
system(bh1); //here could work
the result is:
TRTD8/TD
TDAA34841687 000 INSONO-SOUS-MOTEUR--/TD
TDlt;img src=C:\t9\images\INSONO-SOUS-MOTEUR.jpg height=220/gt;/TD
//here I wanna add char between 'C:\t9\images\INSONO-SOUS-MOTEUR.jpg'
TD1/TD
/TR
and the best way is change
lt;to
gt;to
___
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 char in sqlite
If Windows get FART (find and replace text) from here: http://blog.secaserver.com/2011/07/windows-find-and-replace-text-command-line-utility/ If Unix learn sed: http://www.thegeekstuff.com/2009/09/unix-sed-tutorial-replace-text-inside-a-file-using-substitute-command/ Then sqlite3 test.db create table engine(id,partnumber,pic); insert into engine values(1,11,'1.jpg'); insert into engine values(2,22,'2.jpg'); insert into engine values(3,33,'3.jpg'); D:\SQLitesqlite3 -html test.db select id,partnumber,'XXLTimg src='||\XXQUOTE\||pic||\XXQUOTE\||' height=220XXGT' from eng ine; fart test.html XXGT fart test.html XXLT fart test.html XXQUOTE '' And you end up with: TRTD1/TD TD11/TD TDimg src='1.jpg' height=220/TD /TR TRTD2/TD TD22/TD TDimg src='2.jpg' height=220/TD /TR TRTD3/TD TD33/TD TDimg src='3.jpg' height=220/TD /TR Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of YAN HONG YE [yanhong...@mpsa.com] Sent: Wednesday, October 24, 2012 4:00 AM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] FW: how to select char in sqlite sqlite3 -html -header t9_engine.db select id,partnumber,\abc.jpg\ as img,pcs from engine where id7; n.html here \abc.jpg\ couldn't work. sqlite3 -html -header t9_engine.db select id,partnumber,'img src=\ '||pic||' \ height=220/' as img,pcs from engine where id7; n.html Same problem. strcpy(bh1,sqlite3 -html -header t9_engine.db \select id,partnumber,substr(\'img src=\\'||pic||\'\ height=220/\',1,180) as img,pcs from engine where id7;\ n.html); Same problem. ___ 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] Getting Error SQLITE_NOTADB
I assume you have some program doing the sqlite_exec?
Care to show us your code?
I just ran a test doing what you describe with SQLite Database Browser Version
2.0b1 and the following program compiled against 3.7.13;
After the 2nd run of this program there are 2 records in test.db which the
browser showsno errors occur.
#include stdio.h
#include sqlite3.h
int main()
{
sqlite3 *db;
int rc;
rc=sqlite3_open(test.db,db);
if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
}
sqlite3_exec(db,create table t (i integer);,NULL,NULL,NULL);
rc=sqlite3_exec(db,insert into t values(1);,NULL,NULL,NULL);
if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
}
sqlite3_close(db);
}
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of kritesh tripathi [tripathi.krit...@gmail.com]
Sent: Tuesday, October 23, 2012 11:58 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Getting Error SQLITE_NOTADB
Hi
I am using Sqlite for my project and getting Error like -SQLITE_NOTADB
whenever i am trying
below scenario-
1- Open and create the database
2- Execute Sqlite_Exec to Insert the record in the database table
3- Close the database
4- Confirm by opening the .db file in sqlite browser tht dtabase table
insert the records
5- Again Open the the database
6- Whenever Executing Sqlite_Exec to Update the colum value of database
table .
.Getting the Errror in Sqlite3_Prepare()---lockBtree(BtShared *pBt)--
if( memcmp(page1, zMagicHeader, 16)!=0 ){
goto page1_init_failed;
Everytimes above if condition True hence page1_init_failed and returns
rc = SQLITE_NOTADB;
Anybody have any idea what would be the reason if db table is creating
properly then why does failing in this condition .
Cheers
kritesh
I am getting Error
lockBtree
--
Regards
kritesh tripathi
___
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] Getting Error SQLITE_NOTADB
OK...here it is again using an updatestill works for me. Does it work for
you?
run it like this...first run with no args creates the table
simple
simple 1 2
simple 2 3
The args just update from/to values so you can see them changing using and
update/where clause.
#1 What OS?
#2 What language
#3 Where's your code?
#include stdio.h
#include sqlite3.h
int main(int argc, char *argv[])
{
sqlite3 *db;
int rc;
rc=sqlite3_open(test.db,db);
if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
}
if (argc == 1) {
sqlite3_exec(db,create table t (i integer);,NULL,NULL,NULL);
rc=sqlite3_exec(db,insert into t values(1);,NULL,NULL,NULL);
}
else {
char sql[4096];
if (argc != 3) {
printf(Need 2 args, old/new values\n);
exit(1);
}
sprintf(sql,update t set i=%s where i=%s,argv[2],argv[1]);
rc=sqlite3_exec(db,sql,NULL,NULL,NULL);
}
if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
}
sqlite3_close(db);
return 0;
}
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of kritesh tripathi [tripathi.krit...@gmail.com]
Sent: Tuesday, October 23, 2012 1:44 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Getting Error SQLITE_NOTADB
hi michale
there is no problem in creating and inserting the records in table ...but
after tht i am trying to update the coloum value of table and using update
command with where clause ..here every time sqlite_exec command failing and
rc returns in sqlite3_prepare such as SQLITE_NOTADB
CHEERS
KRITESH
On Oct 24, 2012 2:10 AM, Black, Michael (IS) michael.bla...@ngc.com
wrote:
I assume you have some program doing the sqlite_exec?
Care to show us your code?
I just ran a test doing what you describe with SQLite Database Browser
Version 2.0b1 and the following program compiled against 3.7.13;
After the 2nd run of this program there are 2 records in test.db which the
browser showsno errors occur.
#include stdio.h
#include sqlite3.h
int main()
{
sqlite3 *db;
int rc;
rc=sqlite3_open(test.db,db);
if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
}
sqlite3_exec(db,create table t (i integer);,NULL,NULL,NULL);
rc=sqlite3_exec(db,insert into t values(1);,NULL,NULL,NULL);
if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
}
sqlite3_close(db);
}
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on behalf of kritesh tripathi [tripathi.krit...@gmail.com]
Sent: Tuesday, October 23, 2012 11:58 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Getting Error SQLITE_NOTADB
Hi
I am using Sqlite for my project and getting Error like -SQLITE_NOTADB
whenever i am trying
below scenario-
1- Open and create the database
2- Execute Sqlite_Exec to Insert the record in the database table
3- Close the database
4- Confirm by opening the .db file in sqlite browser tht dtabase table
insert the records
5- Again Open the the database
6- Whenever Executing Sqlite_Exec to Update the colum value of database
table .
.Getting the Errror in Sqlite3_Prepare()---lockBtree(BtShared *pBt)--
if( memcmp(page1, zMagicHeader, 16)!=0 ){
goto page1_init_failed;
Everytimes above if condition True hence page1_init_failed and returns
rc = SQLITE_NOTADB;
Anybody have any idea what would be the reason if db table is creating
properly then why does failing in this condition .
Cheers
kritesh
I am getting Error
lockBtree
--
Regards
kritesh tripathi
___
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] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22
Have you tried making your own DLL from each source and comparing them when
they are compiled the same?
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Imanuel [my_mailings_addr...@gmx.de]
Sent: Monday, October 22, 2012 4:07 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than
with 3.6.22
Hi Dan
I don't use a wrapper in the application (at least not in the test
application), and I don't issue any PRAGMAs. I have tried changing
cache_size, but that didn't change the results (I have tried 128
(default), 2000 and 10).
I don't know how to intercept calls to the VFS interface, so I guess I
can deny that question, too ;)
All I do is run the three imported dll functions: sqlite3_open,
sqlite3_exec and sqlite3_close.
The time is measured directly before and after sqlite3_exec so there
should be nothing else interfering with it.
I just tested the same command with the Firefox addon SQLite Manager,
which uses 3.7.13. It took 67 seconds to create the index, which is way
faster than my delphi test application with 3.7.14.1, but still slower
than delphi and 3.6.22.
Also, I don't think that delphi just makes SQLite slow, because 3.6.22
is way faster than 3.7.14.1 - so it has to be related to any change that
was made.
I'll make more tests if it is only CREATE INDEX that is slowlier, or
other commands, too.
SELECT statements on indexed columns are very fast with both, but maybe
they are simply too fast to notice any difference.
Imanuel
Am 22.10.2012 06:35, schrieb Dan Kennedy:
On 10/21/2012 03:48 PM, Imanuel wrote:
is there any way to have a fast SQLite
3.7.14.1 via DLL or at least to tell why it's that slow?
I don't think it's the dll itself that is the problem, just
something about the way it is used in Delphi. And I don't
actually know anything about Delphi..
Is the wrapper or the application issuing any PRAGMA statements
to SQLite? Does the wrapper intercept calls to the VFS interface?
Dan.
Imanuel
Am 18.10.2012 17:49, schrieb Imanuel:
No, I can't - 26s vs 15s (old vs new).
But when I run the test in my Delphi test application, 3.7.14.1 takes
285 seconds (tested again right now).
All the time, CPU usage is 25% (on a quad core).
This is my test code:
sqlite3_open('test.db', handle);
t0:=now();
sqlite3_exec(handle, PAnsiChar('CREATE INDEX idx_namen_name ON
Namen(name)'), nil, nil, nil);
showmessage(floattostr((now()-t0)*86400));
sqlite3_close(handle);
The DLL is referenced in this unit:
https://raw.github.com/plashenkov/SQLite3-Delphi-FPC/09d8674805c73d1ab0fa05832750cc0f727102f0/Source/SQLite3.pas
I simply have replaced the dll without changing the linking source code
to test with 3.7.14.1 - I hope that's ok.
Imanuel
Am 18.10.2012 16:49, schrieb Dan Kennedy:
On 10/18/2012 03:32 PM, Imanuel wrote:
Ok, here it is (45mb):
http://www.file-upload.net/download-6707980/CREATE_INDEX_test.7z.html
On Linux here 3.6.22 takes around 61 seconds. Against 23 for a new
version. Are you able to reproduce the performance regression with
these two?
http://www.sqlite.org/sqlite-shell-win32-x86-3062200.zip
http://www.sqlite.org/sqlite-shell-win32-x86-307140100.zip
Dan.
Imanuel
Am 18.10.2012 00:37, schrieb Imanuel:
No, the performance stays the same.
I have also tried using a big cache_size, but that didn't change
anything, too.
Yes, I can share the database - it is currently uploading, I will
mail
the link tomorrow.
Imanuel
Am 17.10.2012 22:08, schrieb Dan Kennedy:
On 10/18/2012 01:32 AM, Imanuel wrote:
Hello
I tested this on an SSD with a database with one single table with
5,553,534 entries:
CREATE TABLE Namen(id INTEGER PRIMARY KEY, geonameid INTEGER, lang
TEXT,
name TEXT, pref INTEGER DEFAULT 0, short INTEGER DEFAULT 0, coll
INTEGER
DEFAULT 0, historic INTEGER DEFAULT 0, sort INTEGER DEFAULT 7)
When running this command:
CREATE INDEX idx_namen_name ON Namen(name)
Version 3.6.22 (from here: http://indasoftware.com/sqlite/)
takes 36
seconds, while 3.7.14.1 (as DLL, too) takes 279 seconds.
Indexing the column geonameid makes 24 vs. 312 seconds.
Neither of the both columns are presorted.
If you set PRAGMA temp_store = memory in 3.7.14.1 is the
performance the same as in 3.6.22?
Are you able to share the database?
___
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
Re: [sqlite] Find first non-NULL values of several columns
How's about you store your interpolated value during insert?
You can use a binary mask of say, 16384, to indicate the value is interpolated
in case you need to know that. In the original data you sent only one value
can be interpolated at record 3. You probably want the interpolation to be
weighted towards the times when separated by more than one time interval. That
should be able to done during the update too I think thought that syntax is a
bit beyond me at the moment.
create table v(a integer primary key,b,c,d,e,f);
create trigger v_insert after insert on v
when new.a 1 and new.b is not null and (select b from v where a=new.a-1) is
null
begin
update v set b=(new.b+(select b from v where v.anew.a-1 and b is not null
order by a desc limit 1))/2|16384 where a=new.a-1;
end;
insert into v values(0,null,null,2,null,9);
insert into v values(1,1 ,null,3,null,8);
insert into v values(2,1 ,null,4,4,7);
insert into v values(3,null,5, ,4,6);
insert into v values(4,1 ,6, 6,null,5);
select * from v;
0|||2||9
1|1||3||8
2|1||4|4|7
3|16385|5|5|4|6
4|1|6|6||5
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Steinar Midtskogen [stei...@latinitas.org]
Sent: Friday, October 19, 2012 2:09 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Find first non-NULL values of several columns
Thank you for all suggestions.
I will need to do such queries often, so it's just a matter of saving
the typing. Unfortunately, views aren't going to be very practical
either, because there are a lot of tables and columns (100+), and new
ones will be added.
The actual use case is as follows:
I have tables with a timestamp (unix time) and columns containing
sensor readings which are inserted continuously. I frequently need to
access the most recent values (or NULL if there is no value within the
latest, say, hour). I would like to do something like:
SELECT coalesce(col_1), ..., coalesce(col_n) FROM v WHERE unix_time
strftime('%s', 'now', '-1 hour') ORDER BY unix_time DESC;
So I would typically want to access the last non-NULL value because of
the DESC keyword. But if I understand things correctly, a statement
like above will never work because an aggregate function reads the
data in no particular order regardless of the ORDER BY statement.
I like Igor's suggestion. Although not quite universal, it's clever.
Ryan's suggestion should work well, except that I will need a first(a,
b) and last(a, b) function (if I want to support both ascending and
descending order) and I can leave out the ORDER BY part. So:
SELECT last(col_1, unix_time), ..., last(col_n, unix_time) FROM v WHERE
unix_time strftime('%s', 'now', '-1 hour');
Yes, it will have run through the whole set, whereas multiple SELECT
col_x FROM v WHERE unix_time strftime('%s', 'now', '-1 hour') AND
col_x IS NOT NULL ORDER BY unix_time DESC LIMIT 1 will stop early.
But this will not be a problem for me since I want to have a modest
upper limit (1 hour) anyway.
--
Steinar Midtskogen
___
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] Find first non-NULL values of several columns
Does a view help you out? Are you just trying to make it easier for somebody to create a query for that answer without typing so much? create table v(a integer primary key,b,c,d,e,f); insert into v values(0,NULL,NULL,2,null,9); insert into v values(1,1,null,3,null,8); insert into v values(2,1,null,4,4,7); insert into v values(3,null,5,5,4,6); insert into v values(4,1,6,6,null,5); SELECT * FROM (SELECT b FROM v WHERE b IS NOT NULL ORDER BY a LIMIT 1), (SELECT c FROM v WHERE c IS NOT NULL ORDER BY a LIMIT 1), (SELECT d FROM v WHERE d IS NOT NULL ORDER BY a LIMIT 1), (SELECT e FROM v WHERE e IS NOT NULL ORDER BY a LIMIT 1), (SELECT f FROM v WHERE f IS NOT NULL ORDER BY a LIMIT 1); create view vb as select b from v where b is not null order by a limit 1; create view vc as select c from v where c is not null order by a limit 1; create view vd as select d from v where d is not null order by a limit 1; create view ve as select e from v where e is not null order by a limit 1; create view vf as select f from v where f is not null order by a limit 1; select * from vb,vc,vd,ve,vf; .headers on b|c|d|e|f 1|5|2|4|9 Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Steinar Midtskogen [stei...@latinitas.org] Sent: Friday, October 19, 2012 7:04 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Find first non-NULL values of several columns Simon Slavin slav...@bigfraud.org writes: Rows do not have an order. Without an ORDER BY clause SELECT can return rows in a random order if it wants. If you would like to define 'order' for me I can give you a SELECT which will find the first non-NULL value in a column, probably something like SELECT c FROM v WHERE c IS NOT NULL ORDER BY rowid LIMIT 1 Ok, so let's say the table v (with a as the primary key) is: a|b|c|d|e|f 0| | |2| |9 1|1| |3| |8 2|1| |4|4|7 3| |5|5|4|6 4|1|6|6| |5 The the question becomes, is there a more convenient way to do: SELECT * FROM (SELECT b FROM v WHERE b IS NOT NULL ORDER BY a LIMIT 1), (SELECT c FROM v WHERE c IS NOT NULL ORDER BY a LIMIT 1), (SELECT d FROM v WHERE d IS NOT NULL ORDER BY a LIMIT 1), (SELECT e FROM v WHERE e IS NOT NULL ORDER BY a LIMIT 1), (SELECT f FROM v WHERE f IS NOT NULL ORDER BY a LIMIT 1); to get 1|5|2|4|9? -- Steinar Midtskogen ___ 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] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22
I used 3.7.14.1 Compiled thusly with Visual Studio Express 2008 cl /O2 sqlite3.c shell.c CREATE INDEX idx_namen_name ON Namen(name); Took 26.6 seconds and one CPU was pegged the whole time. I'm on a 3Ghz 8-core machine. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Dan Kennedy [danielk1...@gmail.com] Sent: Thursday, October 18, 2012 9:50 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22 On 10/18/2012 09:49 PM, Dan Kennedy wrote: On 10/18/2012 03:32 PM, Imanuel wrote: Ok, here it is (45mb): http://www.file-upload.net/download-6707980/CREATE_INDEX_test.7z.html On Linux here 3.6.22 takes around 61 seconds. Against 23 for a new version. Are you able to reproduce the performance regression with these two? http://www.sqlite.org/sqlite-shell-win32-x86-3062200.zip http://www.sqlite.org/sqlite-shell-win32-x86-307140100.zip Second link is incorrect. They should be: http://www.sqlite.org/sqlite-shell-win32-x86-3062200.zip http://www.sqlite.org/sqlite-shell-win32-x86-3071401.zip Dan. Dan. Imanuel Am 18.10.2012 00:37, schrieb Imanuel: No, the performance stays the same. I have also tried using a big cache_size, but that didn't change anything, too. Yes, I can share the database - it is currently uploading, I will mail the link tomorrow. Imanuel Am 17.10.2012 22:08, schrieb Dan Kennedy: On 10/18/2012 01:32 AM, Imanuel wrote: Hello I tested this on an SSD with a database with one single table with 5,553,534 entries: CREATE TABLE Namen(id INTEGER PRIMARY KEY, geonameid INTEGER, lang TEXT, name TEXT, pref INTEGER DEFAULT 0, short INTEGER DEFAULT 0, coll INTEGER DEFAULT 0, historic INTEGER DEFAULT 0, sort INTEGER DEFAULT 7) When running this command: CREATE INDEX idx_namen_name ON Namen(name) Version 3.6.22 (from here: http://indasoftware.com/sqlite/) takes 36 seconds, while 3.7.14.1 (as DLL, too) takes 279 seconds. Indexing the column geonameid makes 24 vs. 312 seconds. Neither of the both columns are presorted. If you set PRAGMA temp_store = memory in 3.7.14.1 is the performance the same as in 3.6.22? Are you able to share the database? ___ 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] EXT :Re: CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22
And using Dan's downloads 3.7.14.1 took 30.4 seconds 3.6.22 took 40.94 (there was a lot of idle time towards the end here...disk I/O I assume) Re-did my compilation again... 3.7.14.1 took 26.8 Recompiled under Visual Studio Express 2010 cl /O2 sqlite3.c shell.c 3.7.14.1 took 26.2 seconds I'm not on an SSD but that shouldn't matter much for this. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Dan Kennedy [danielk1...@gmail.com] Sent: Thursday, October 18, 2012 9:50 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22 On 10/18/2012 09:49 PM, Dan Kennedy wrote: On 10/18/2012 03:32 PM, Imanuel wrote: Ok, here it is (45mb): http://www.file-upload.net/download-6707980/CREATE_INDEX_test.7z.html On Linux here 3.6.22 takes around 61 seconds. Against 23 for a new version. Are you able to reproduce the performance regression with these two? http://www.sqlite.org/sqlite-shell-win32-x86-3062200.zip http://www.sqlite.org/sqlite-shell-win32-x86-307140100.zip Second link is incorrect. They should be: http://www.sqlite.org/sqlite-shell-win32-x86-3062200.zip http://www.sqlite.org/sqlite-shell-win32-x86-3071401.zip Dan. Dan. Imanuel Am 18.10.2012 00:37, schrieb Imanuel: No, the performance stays the same. I have also tried using a big cache_size, but that didn't change anything, too. Yes, I can share the database - it is currently uploading, I will mail the link tomorrow. Imanuel Am 17.10.2012 22:08, schrieb Dan Kennedy: On 10/18/2012 01:32 AM, Imanuel wrote: Hello I tested this on an SSD with a database with one single table with 5,553,534 entries: CREATE TABLE Namen(id INTEGER PRIMARY KEY, geonameid INTEGER, lang TEXT, name TEXT, pref INTEGER DEFAULT 0, short INTEGER DEFAULT 0, coll INTEGER DEFAULT 0, historic INTEGER DEFAULT 0, sort INTEGER DEFAULT 7) When running this command: CREATE INDEX idx_namen_name ON Namen(name) Version 3.6.22 (from here: http://indasoftware.com/sqlite/) takes 36 seconds, while 3.7.14.1 (as DLL, too) takes 279 seconds. Indexing the column geonameid makes 24 vs. 312 seconds. Neither of the both columns are presorted. If you set PRAGMA temp_store = memory in 3.7.14.1 is the performance the same as in 3.6.22? Are you able to share the database? ___ 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] EXT :Re: CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22
I should mention I'm running Windows XP-64. 32-bit compile though. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Black, Michael (IS) [michael.bla...@ngc.com] Sent: Thursday, October 18, 2012 10:16 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] EXT :Re: CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22 And using Dan's downloads 3.7.14.1 took 30.4 seconds 3.6.22 took 40.94 (there was a lot of idle time towards the end here...disk I/O I assume) Re-did my compilation again... 3.7.14.1 took 26.8 Recompiled under Visual Studio Express 2010 cl /O2 sqlite3.c shell.c 3.7.14.1 took 26.2 seconds I'm not on an SSD but that shouldn't matter much for this. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Dan Kennedy [danielk1...@gmail.com] Sent: Thursday, October 18, 2012 9:50 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22 On 10/18/2012 09:49 PM, Dan Kennedy wrote: On 10/18/2012 03:32 PM, Imanuel wrote: Ok, here it is (45mb): http://www.file-upload.net/download-6707980/CREATE_INDEX_test.7z.html On Linux here 3.6.22 takes around 61 seconds. Against 23 for a new version. Are you able to reproduce the performance regression with these two? http://www.sqlite.org/sqlite-shell-win32-x86-3062200.zip http://www.sqlite.org/sqlite-shell-win32-x86-307140100.zip Second link is incorrect. They should be: http://www.sqlite.org/sqlite-shell-win32-x86-3062200.zip http://www.sqlite.org/sqlite-shell-win32-x86-3071401.zip Dan. Dan. Imanuel Am 18.10.2012 00:37, schrieb Imanuel: No, the performance stays the same. I have also tried using a big cache_size, but that didn't change anything, too. Yes, I can share the database - it is currently uploading, I will mail the link tomorrow. Imanuel Am 17.10.2012 22:08, schrieb Dan Kennedy: On 10/18/2012 01:32 AM, Imanuel wrote: Hello I tested this on an SSD with a database with one single table with 5,553,534 entries: CREATE TABLE Namen(id INTEGER PRIMARY KEY, geonameid INTEGER, lang TEXT, name TEXT, pref INTEGER DEFAULT 0, short INTEGER DEFAULT 0, coll INTEGER DEFAULT 0, historic INTEGER DEFAULT 0, sort INTEGER DEFAULT 7) When running this command: CREATE INDEX idx_namen_name ON Namen(name) Version 3.6.22 (from here: http://indasoftware.com/sqlite/) takes 36 seconds, while 3.7.14.1 (as DLL, too) takes 279 seconds. Indexing the column geonameid makes 24 vs. 312 seconds. Neither of the both columns are presorted. If you set PRAGMA temp_store = memory in 3.7.14.1 is the performance the same as in 3.6.22? Are you able to share the database? ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite flush on disk to calc db file hash,how?
Are you maybe using WAL mode? Do you have any other files alongside your database like *.db-shm or *.db-wal? If so, you can just cat all the files together and pipe through md5sum or such. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of lebron james [lebron.m...@gmail.com] Sent: Wednesday, October 17, 2012 4:51 AM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] SQLite flush on disk to calc db file hash,how? I have program which with some period insert row in sqlite database. I need calc hash sum of database file after each insert without close connection. I have some problem with that, after insert database file hash sum are same with they have before insert. Only after closing connection hash sum are changed. How i can solve this problem? platform are windows, and i dont have open transaction, just default opened sqlite db file and simple insert, if i do COMMIT after INSERT query i have exception you dont have open transaction ___ 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] 5. Re: Sqlite, Is it possible to calculate the length of the longest increasing subsequence using an UDF
Maybe I'm missing something (wouldn't surprise me) but I can think of O(n) traversal of the array for doing this. Not in SQL of course but you should be able to write a user function for it. Pseudo-code: lastchar=''; For (char c in array) if (lastchar = '' || c = lastchar+1) curseq.push(c); else curseq.clear(); curseq..push(c); end lastchar = c; if (curseq.size() longest.size()) longest = curseq; end end print longest.size(); Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [itandet...@mvps.org] Sent: Monday, October 15, 2012 6:05 PM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] 5. Re: Sqlite, Is it possible to calculate the length of the longest increasing subsequence using an UDF On 10/15/2012 4:29 PM, Frank Chang wrote: Igor Tandetnik, So what is the purpose of this whole exercise Following the project gurus's example sequence of -- 1,2,3,4,3,5,6,7,8,10 -- the numeric sorted ascending subsequence is found to be 1,2,3,4,5,6,7,8,10 using an automatic variable containing the most recent monotically increasing sequence member value and traversing the array sequentially in Big-O(linear time). What will this algorithm do for a sequence 1, 10, 2, 3, 4, 5, 6, 7, 8, 9 ? What about 1, 7, 2, 8, 3, 9, 4, 5, 6? Generally, there is no known algorithm to find the longest subsequence in O(n) time. You seem to be describing a greedy algorithm: it will certainly find *some* increasing subsequence, but not necessarily the longest one. In any case, you still haven't explained two things that are of interest: a) Why do you care about the longest increasing subsequence in the first place? What do you plan to do with it, once found? This is not a purely academical exercise, I presume. b) Why does the solution have to be in the form of a SQLite user-defined function? -- 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] 5. Re: Sqlite, Is it possible to calculate the length of the longest increasing subsequence using an UDF
I knew I was missing somethingthanks for the correction and pointer...learn something new every day. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [itandet...@mvps.org] Sent: Tuesday, October 16, 2012 8:54 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] 5. Re: Sqlite, Is it possible to calculate the length of the longest increasing subsequence using an UDF Black, Michael (IS) michael.bla...@ngc.com wrote: Pseudo-code: lastchar=''; For (char c in array) if (lastchar = '' || c = lastchar+1) curseq.push(c); else curseq.clear(); curseq..push(c); end The longest increasing subsequence doesn't need to be contiguous. Nor does the difference between two neighboring elements have to be exactly one - it just have to be a positive number. At least as defined at http://en.wikipedia.org/wiki/Longest_increasing_subsequence E.g. for a sequence of (1, 100, 2, 4) your algorithm finds (1), while the correct answer is (1, 2, 4). -- 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] EXT : find sequential groups
Do this work for you? CREATE TABLE Test(ID,Value,Group_Marker); INSERT INTO Test VALUES(1,'D',0); INSERT INTO Test VALUES(2,'X',0); INSERT INTO Test VALUES(3,'X',0); INSERT INTO Test VALUES(4,'X',0); INSERT INTO Test VALUES(5,'A',0); INSERT INTO Test VALUES(6,'B',0); SELECT * FROM Test; CREATE TABLE Groups (Value); insert into Groups select distinct(Value) from test; SELECT * FROM Groups; update test set Group_Marker=(select rowid from Groups where Groups.Value=test.Value); SELECT * FROM Test; sqlite CREATE TABLE Test(ID,Value,Group_Marker); sqlite INSERT INTO Test VALUES(1,'D',0); sqlite INSERT INTO Test VALUES(2,'X',0); sqlite INSERT INTO Test VALUES(3,'X',0); sqlite INSERT INTO Test VALUES(4,'X',0); sqlite INSERT INTO Test VALUES(5,'A',0); sqlite INSERT INTO Test VALUES(6,'B',0); sqlite SELECT * FROM Test; 1|D|0 2|X|0 3|X|0 4|X|0 5|A|0 6|B|0 sqlite CREATE TABLE Groups (Value); sqlite insert into Groups select distinct(Value) from test; sqlite SELECT * FROM Groups; D X A B sqlite update test set Group_Marker=(select rowid from Groups where Groups.Val e=test.Value); sqlite SELECT * FROM Test; 1|D|1 2|X|2 3|X|2 4|X|2 5|A|3 6|B|4 Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Bart Smissaert [bart.smissa...@gmail.com] Sent: Tuesday, October 16, 2012 3:56 PM To: General Discussion of SQLite Database Subject: EXT :[sqlite] find sequential groups Trying to make a query that can mark records, indicating them to belong to a sequential group. Giving the most simple example: IDValue Group_Marker --- 1 D1 2 X 2 3 X 2 4 X 2 5 A 3 6 B 4 Given I have a table with data in the fields ID and Value, but not in Group_Marker, can I make a SQL that will find the values in the field Group_Marker as above and update that field to hold those values. The field Value holds the data indicating a sequential group, so record 2, 3 and 4 are the second group, hence I need the 2 in the field Group_Marker. ID is the field indicating the sequence. This is easy to do in code with a simple loop, but not sure now how to do it in SQL. RBS ___ 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] find sequential groups
Ok...how about with triggers then? This will give a unique number to each sequence as you insert them. CREATE TABLE Test(ID,Value,Group_Marker); CREATE TRIGGER insert_trigger1 after insert on Test WHEN new.id=1 BEGIN UPDATE Test set Group_Marker=1; END; CREATE TRIGGER insert_trigger2 after insert on Test WHEN new.id 1 and ((SELECT Group_Marker from Test where id=new.id-1 and Value=new.Value) IS NOT NULL) BEGIN UPDATE Test set Group_Marker=(select Group_Marker from Test where id=new.id-1) where id=new.id; END; CREATE TRIGGER insert_trigger3 after insert on Test WHEN new.id 1 and ((SELECT Group_Marker from Test where id=new.id-1 and Value!=new.Value) IS NOT NULL) BEGIN UPDATE Test set Group_Marker=(select Group_Marker+1 from Test where id=new.id-1) where id=new.id; END; INSERT INTO Test VALUES(1,'D',0); INSERT INTO Test VALUES(2,'X',0); INSERT INTO Test VALUES(3,'X',0); INSERT INTO Test VALUES(4,'X',0); INSERT INTO Test VALUES(5,'A',0); INSERT INTO Test VALUES(6,'B',0); INSERT INTO Test VALUES(7,'X',0); SELECT * FROM Test; You'll see that # 7 gets a new Group_Marker instead of repeating group#2. 1|D|1 2|X|2 3|X|2 4|X|2 5|A|3 6|B|4 7|X|5 Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Bart Smissaert [bart.smissa...@gmail.com] Sent: Tuesday, October 16, 2012 5:45 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] find sequential groups Thanks, will try that. Yes, the ID field is an integer primary key autoincrement. Still running the old sql with concatenation. Looks I may need to kill that. RBS On Tue, Oct 16, 2012 at 11:38 PM, Igor Tandetnik itandet...@mvps.org wrote: On 10/16/2012 6:29 PM, Bart Smissaert wrote: Actually, it really is slow, made worse by the fact that there is not one grouping field (value in my example), but three. I am running your SQL now, concatenating these 3 fields, but still running and looks will be a long time. Will have to improve it with indexes and maybe avoiding the concatenation. This would avoid concatenation: update MyTable set Group_Marker = ( select count(*) from MyTable t1 where t1.ID = MyTable.ID and not ( select (t2.Value1=t1.Value1 and t2.Value2=t1.Value2 and t2.Value3=t1.Value3) from MyTable t2 where t2.ID t1.ID order by t2.ID desc limit 1 ) ); The only index that would be helful is one on ID, which I suspect you might already have. -- 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] EXT :Re: System.Data.SQLite Field Name are surrounded by double quotes for Views
And if you don't quote the 2nd select it comes out OK. This is 3.6.13:
sqlite SELECT id FROM TESTVIEW; -- Observe the results here.
id
--
test
sqlite SELECT id FROM TESTVIEW; -- Observe the results here.
id
--
Presumably a simple bug for the powers-to-be to fix.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Joe Mistachkin [sql...@mistachkin.com]
Sent: Saturday, October 13, 2012 10:33 AM
To: 'General Discussion of SQLite Database'
Subject: EXT :Re: [sqlite] System.Data.SQLite Field Name are surrounded by
double quotes for Views
Vincent DARON wrote:
http://pastebin.com/q2m5vJky
The double quotes are coming from the SQLite core native library.
Run sqlite3.exe :memory: and execute the following commands:
.mode columns
.headers on
CREATE TABLE TEST (id);
CREATE VIEW TESTVIEW AS SELECT TEST.id FROM TEST;
INSERT INTO TEST (ID) VALUES('test');
SELECT id FROM TEST; -- Observe the results here.
SELECT id FROM TESTVIEW; -- Observe the results here.
--
Joe Mistachkin
___
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] light weight write barriers
There isn't Somebody sure wasted their time on this article then... http://www.linux-magazine.com/w3/issue/78/Write_Barriers.pdf Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Christoph Hellwig [h...@infradead.org] Sent: Thursday, October 11, 2012 12:41 PM To: ? Yang Su Li Cc: linux-fsde...@vger.kernel.org; General Discussion of SQLite Database; linux-ker...@vger.kernel.org; d...@hwaci.com Subject: EXT :Re: [sqlite] light weight write barriers On Thu, Oct 11, 2012 at 11:32:27AM -0500, ? Yang Su Li wrote: I am not quite whether I should ask this question here, but in terms of light weight barrier/fsync, could anyone tell me why the device driver / OS provide the barrier interface other than some other abstractions anyway? I am sorry if this sounds like a stupid questions or it has been discussed before It does not. Except for the legacy mount option naming there is no such thing as a barrier in Linux these days. ___ 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] Seemingly random Access violation errors (resent)
Can you check the difference in stack size between the emulator and the real device? The emulator could well have a larger default stack size and you're getting stack overflow on the real device. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Matthew Dumbleton [msd...@hotmail.com] Sent: Monday, October 08, 2012 11:10 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Seemingly random Access violation errors (resent) Simon, Apologies if it seems like I'm picking on SQLite. As previously stated I am just disappointed that I don't seem to be able to use it in a simple c# app. I'm sure it works great on other platforms/devices etc. just wanted to get in on the act. No insult to c, c++ developers and/or any and all contributors to sqlite intended. I can send you the simple c# app I wrote to recreate the problem if you like but from what Joe is saying this is just a device/platform issue I will have to accept. Fair enough. From: slav...@bigfraud.org Date: Mon, 8 Oct 2012 16:54:00 +0100 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Seemingly random Access violation errors (resent) On 8 Oct 2012, at 3:54pm, Matthew Dumbleton msd...@hotmail.com wrote: So does this mean therefore SQLite will not currently work on a compact framework device? (Or at least not on mine.) SQLite is distributed as C source code. It's the .c and .h files you find when you download the amalgamation from http://www.sqlite.org/download.html You're meant to compile these files into your project and call the C routines directly. If your preferred programming language can call C routines, it can use the SQLite API. What you're trying to use is a DLL. You can by all means complain that a DLL doesn't work, but the DLL is not SQLite, it's someone trying to be helpful and package SQLite into the DLLs some people seem to want, to save them compiling their own. From reading this thread so far, it seems that you have a bug in your application but it's possible that you've found a bug in a .NET wrapper for SQLite. Neither of these are SQLite. 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] Subtract times hh:mm:ss
You haven't provided enough info for anybody to tell what's going on.
What data are you substracting? Can you provide an sql dump of the data that
gets different answers and your code?
You can' even get fractional seconds from those statements as the time format
only supports hr/min/sec
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Bart Smissaert [bart.smissa...@gmail.com]
Sent: Saturday, October 06, 2012 12:38 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
Times I get (65000 records, subtracting 2 fields defined as text in
the same table)
gives me following times:
method with julianday 0.4 secs
method with unixepoch 0.6 secs
using ctime etc. via VB wrapper 1.2 secs
RBS
On Tue, Oct 2, 2012 at 7:39 PM, Igor Tandetnik itandet...@mvps.org wrote:
On 10/2/2012 1:00 PM, Bart Smissaert wrote:
Is there a way to subtract times in the text format hh:mm:ss
and return the difference in the same format?
select time(julianday('03:22:11') - julianday('01:22:33') - .5);
select time(strftime('%s', '03:22:11') - strftime('%s', '01:22:33'),
'unixepoch');
Both of these return '01:59:38'.
--
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] Subtract times hh:mm:ss
You expect the readers on this list to go find your old post and then look at
what you're NOT doing now? You asked how to compute time, we showed you, and
now you are apparently doing it incorrectly.
You need to provide enough info in your current post for people to duplicate
your current problem and want to help you.
You showed you are getting 3 different answers...presumably from the same
record...but you don't show us the fields you are computing it from, nor the
code which does it.
Come to think of of itthere was no question in your last post either.
So help us help you.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Bart Smissaert [bart.smissa...@gmail.com]
Sent: Sunday, October 07, 2012 8:18 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
There are no different answers and I think all the information is in the
first post.
RBS
On Oct 7, 2012 1:21 PM, Black, Michael (IS) michael.bla...@ngc.com
wrote:
You haven't provided enough info for anybody to tell what's going on.
What data are you substracting? Can you provide an sql dump of the data
that gets different answers and your code?
You can' even get fractional seconds from those statements as the time
format only supports hr/min/sec
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on behalf of Bart Smissaert [bart.smissa...@gmail.com]
Sent: Saturday, October 06, 2012 12:38 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
Times I get (65000 records, subtracting 2 fields defined as text in
the same table)
gives me following times:
method with julianday 0.4 secs
method with unixepoch 0.6 secs
using ctime etc. via VB wrapper 1.2 secs
RBS
On Tue, Oct 2, 2012 at 7:39 PM, Igor Tandetnik itandet...@mvps.org
wrote:
On 10/2/2012 1:00 PM, Bart Smissaert wrote:
Is there a way to subtract times in the text format hh:mm:ss
and return the difference in the same format?
select time(julianday('03:22:11') - julianday('01:22:33') - .5);
select time(strftime('%s', '03:22:11') - strftime('%s', '01:22:33'),
'unixepoch');
Both of these return '01:59:38'.
--
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
___
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] Subtract times hh:mm:ss
That makes a LOT more sense...misread times as times :-)
Sorry for any rant on my part.
Are all your times via your VB app?
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Bart Smissaert [bart.smissa...@gmail.com]
Sent: Sunday, October 07, 2012 9:15 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
The word times in my previous post confused/misled you. These are execution
times, not values produced by SQL.
RBS
On Sunday, October 7, 2012, Black, Michael (IS) wrote:
You expect the readers on this list to go find your old post and then look
at what you're NOT doing now? You asked how to compute time, we showed
you, and now you are apparently doing it incorrectly.
You need to provide enough info in your current post for people to
duplicate your current problem and want to help you.
You showed you are getting 3 different answers...presumably from the same
record...but you don't show us the fields you are computing it from, nor
the code which does it.
Come to think of of itthere was no question in your last post either.
So help us help you.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org javascript:; [
sqlite-users-boun...@sqlite.org javascript:;] on behalf of Bart
Smissaert [bart.smissa...@gmail.com javascript:;]
Sent: Sunday, October 07, 2012 8:18 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
There are no different answers and I think all the information is in the
first post.
RBS
On Oct 7, 2012 1:21 PM, Black, Michael (IS)
michael.bla...@ngc.comjavascript:;
wrote:
You haven't provided enough info for anybody to tell what's going on.
What data are you substracting? Can you provide an sql dump of the data
that gets different answers and your code?
You can' even get fractional seconds from those statements as the time
format only supports hr/min/sec
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org javascript:; [
sqlite-users-boun...@sqlite.org javascript:;]
on behalf of Bart Smissaert [bart.smissa...@gmail.com javascript:;]
Sent: Saturday, October 06, 2012 12:38 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
Times I get (65000 records, subtracting 2 fields defined as text in
the same table)
gives me following times:
method with julianday 0.4 secs
method with unixepoch 0.6 secs
using ctime etc. via VB wrapper 1.2 secs
RBS
On Tue, Oct 2, 2012 at 7:39 PM, Igor Tandetnik
itandet...@mvps.orgjavascript:;
wrote:
On 10/2/2012 1:00 PM, Bart Smissaert wrote:
Is there a way to subtract times in the text format hh:mm:ss
and return the difference in the same format?
select time(julianday('03:22:11') - julianday('01:22:33') - .5);
select time(strftime('%s', '03:22:11') - strftime('%s', '01:22:33'),
'unixepoch');
Both of these return '01:59:38'.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
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] Seemingly random Access Violation errors
It would seem to me that if GC.Collect fixes the problem than all you've done is move the problem to someplace else that isn't fatal (at the moment) and could rear its ugly head at most any time. The reason your emulator doesn't throw the error would be for the same reason...different memory allocation strategies. When you run GC.Collect you're going to reclaim lots of small memory blocks which will separate the locations more. Without GC.Collect you're probably getting sequential memory blocks which show up as access violations due to adjacent memory abuse. You're just lucky you see the error at all. I believe you have a buffer overwrite occurring somewhere. Have you tried the run-time checks to see it they can help pinpoint it? http://msdn.microsoft.com/en-us/library/aa290051.aspx#vctchcompilersecuritychecksindepthanchor4 Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Matthew Dumbleton [msd...@hotmail.com] Sent: Friday, October 05, 2012 5:46 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Seemingly random Access Violation errors Joe, I'm afraid this doesn't seem to fix the issue. Just out of interest are you able to run the code I sent on a real device or in an emulator? I'm testing on a Motorola ES400 and didn't initially think to try switching to the emulator which doesn't seem to throw the error (still testing though.) Don't know if it's relevant but I seem to 'fix' the issue if I call a GC.Collect before every connection to the database. But of course this could just be because it runs slower when doing this and in any case probably not good practice. Will continue to test. From: sql...@mistachkin.com To: sqlite-users@sqlite.org Date: Thu, 4 Oct 2012 17:20:18 -0700 Subject: Re: [sqlite] Seemingly random Access Violation errors Matthew Dumbleton wrote: I have tried this change and I'm afraid it hasn't stopped the crashes. I've further refined the locking semantics for the .NET Compact Framework build here: https://system.data.sqlite.org/index.html/info/ac5f4cc084 I think this should correct the problem, although I'm not actually able to reproduce the problem here. -- Joe Mistachkin ___ 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] Issue with SQLite3 for WinRT ARM
I took a wee bit of a look at your project -- don't have Win 8 so can't debug it. And you didn't include the sqlite assembly anyways. But...if I read your comments correctly it appears that the primary difference between what works and what doesn't is the size of the SQL string. So...this sounds a lot like stack corruption to me. Perhaps stack size? #1 -- Put in a breakpoint on your stack size and see if it gets hit -- see this -- http://stackoverflow.com/questions/11667440/monitoring-call-stack-size-in-visual-studio #2 -- strdup(sql.c_str()) and free it inside your exec function. Just to ensure it's not some other funky thing going on. #3 -- Show the length of the SQL it when it runs. #4 -- reduce the create SQL string until it works..which I'm guessing it eventually will. Let us know the results. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Christian Le Gall [firemanchr...@gmail.com] Sent: Friday, September 28, 2012 6:14 PM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Issue with SQLite3 for WinRT ARM Sorry about that. Here is the project: http://dl.dropbox.com/u/4076650/Direct3DApp_ProblemExample.zip This typically indicates a bug in your program, such as threading issues, objects used after being freed, or just general memory corruption. Typically I would agree with you but in the case above I have replicated the issue in a stripped down example project so there should be no rogue memory allocation or threading happening. It even happens if you comment out all unnecessary calls so all you're left with is an empty Metro application and it still happens on ARM. I've sent this to Microsoft developer support who tell me it is most likely a problem with SQLite. Regards, Christian. ___ 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] Problem with Foreign Key constraints
You don't show any code but it sounds like you're using volatile variables. Are you using SQLITE_STATIC instead of SQLITE_TRANSIENT? http://www.sqlite.org/c3ref/bind_blob.html Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Duquette, William H (318K) [william.h.duque...@jpl.nasa.gov] Sent: Monday, October 01, 2012 3:32 PM To: Discussion of SQLite Database Subject: EXT :[sqlite] Problem with Foreign Key constraints Howdy! I have some code that does the following: 1. Takes a snapshot of some number of database tables, e.g., saves the data from those tables as a text string. 2. Later, clears the tables and restores their content from the snapshot. The snapshot is restored by creating a new INSERT statement for each row, with the literal column values in it, and evaluating each of these statements in sequence. The tables contain foreign key constraints with DEFERRABLE INITIALLY DEFERRED specified; thus, I execute all of these INSERTs within a transaction so that I won't get spurious constraint failures. This has been working, but it's slow, so I'm trying to rework the algorithm to use queries with variable references. Then I update the variables once for each row, and call the same query over and over again. This is much faster...but at the end of the transaction I'm getting a foreign key constraint failure. So far as I can tell, all the data is as it should be; and the only difference, so far as I can tell, is that I'm now using variables rather than literals. Any ideas? Will -- Will Duquette -- william.h.duque...@jpl.nasa.gov Athena Development Lead -- Jet Propulsion Laboratory It's amazing what you can do with the right tools. ___ 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] Sending SQLite3 .timeout command in Command-Line Mode
Try the -cmd switch. Probably the easiest solution. set xxx to do shell script sqlite3 -cmd \.timeout 2\databasePath \select * from table1 ;\ Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of John [sql...@johneday.com] Sent: Thursday, September 27, 2012 7:12 AM To: sqlite-users Subject: EXT :[sqlite] Sending SQLite3 .timeout command in Command-Line Mode Hi, I have several different computers running an AppleScript that queries and writes to a SQLite3 database located in a shared folder on the network. Occasionally a database is locked error is produced. Is there a way of sending a .timeout command as if I was working from the shell, in Command-Line Mode? I understand I can write an error handler which will accomplish the same thing but I am trying to avoid that option. property databaseFolder : POSIX path of (path to public folder as text) Databases/ property databaseName : myDatabase property databasePath : quoted form of (databaseFolder databaseName astext) property table1 : Main set xxx to do shell script sqlite3 databasePath \select * from table1 ; \ 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] Sending SQLite3 .timeout command in Command-Line Mode
sqlite3 -help The sqlite3 shell page doesn't explain any switches...couldn't find another page about it. Anybody??? And you're right on timeout -- it's in milliseconds. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of John [sql...@johneday.com] Sent: Thursday, September 27, 2012 8:39 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Sending SQLite3 .timeout command in Command-Line Mode Thank you Michael. I can't find anything in the documentation about the -cmd switch. Will you point me in the right direction? Also, a 2 second timeout would be .timeout 2000 , right? John On Thu, Sep 27, 2012 at 8:36 AM, Black, Michael (IS) michael.bla...@ngc.com wrote: Try the -cmd switch. Probably the easiest solution. set xxx to do shell script sqlite3 -cmd \.timeout 2\databasePath \select * from table1 ;\ Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of John [sql...@johneday.com] Sent: Thursday, September 27, 2012 7:12 AM To: sqlite-users Subject: EXT :[sqlite] Sending SQLite3 .timeout command in Command-Line Mode Hi, I have several different computers running an AppleScript that queries and writes to a SQLite3 database located in a shared folder on the network. Occasionally a database is locked error is produced. Is there a way of sending a .timeout command as if I was working from the shell, in Command-Line Mode? I understand I can write an error handler which will accomplish the same thing but I am trying to avoid that option. property databaseFolder : POSIX path of (path to public folder as text) Databases/ property databaseName : myDatabase property databasePath : quoted form of (databaseFolder databaseName astext) property table1 : Main set xxx to do shell script sqlite3 databasePath \select * from table1 ; \ 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 ___ 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] Sending SQLite3 .timeout command in Command-Line Mode
The command does need to be in quotes if it's more than one word. sqilte3 expects 1 argument for the command. I'm not familiar with applescript but I assume you have to escape quotes to make them actually appear in the output. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of John [sql...@johneday.com] Sent: Thursday, September 27, 2012 9:02 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Sending SQLite3 .timeout command in Command-Line Mode I can't find anything about the -cmd switch outside of the official docs either! Does the command need to be escaped as it is in your example set xxx to do shell script sqlite3 -cmd \.timeout 2\databasePath \select * from table1 ;\ or can I simply use ? set xxx to do shell script sqlite3 -cmd .timeout 2 databasePath \select * from table1 ;\ Thanks again for revealing the (apparent) -cmd switch secret. On Thu, Sep 27, 2012 at 9:44 AM, Black, Michael (IS) michael.bla...@ngc.com wrote: sqlite3 -help The sqlite3 shell page doesn't explain any switches...couldn't find another page about it. Anybody??? And you're right on timeout -- it's in milliseconds. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of John [sql...@johneday.com] Sent: Thursday, September 27, 2012 8:39 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Sending SQLite3 .timeout command in Command-Line Mode Thank you Michael. I can't find anything in the documentation about the -cmd switch. Will you point me in the right direction? Also, a 2 second timeout would be .timeout 2000 , right? John On Thu, Sep 27, 2012 at 8:36 AM, Black, Michael (IS) michael.bla...@ngc.com wrote: Try the -cmd switch. Probably the easiest solution. set xxx to do shell script sqlite3 -cmd \.timeout 2\databasePath \select * from table1 ;\ Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of John [sql...@johneday.com] Sent: Thursday, September 27, 2012 7:12 AM To: sqlite-users Subject: EXT :[sqlite] Sending SQLite3 .timeout command in Command-Line Mode Hi, I have several different computers running an AppleScript that queries and writes to a SQLite3 database located in a shared folder on the network. Occasionally a database is locked error is produced. Is there a way of sending a .timeout command as if I was working from the shell, in Command-Line Mode? I understand I can write an error handler which will accomplish the same thing but I am trying to avoid that option. property databaseFolder : POSIX path of (path to public folder as text) Databases/ property databaseName : myDatabase property databasePath : quoted form of (databaseFolder databaseName astext) property table1 : Main set xxx to do shell script sqlite3 databasePath \select * from table1 ; \ 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 ___ 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] EXT :Re: Sending SQLite3 .timeout command in Command-Line Mode
This works for me just from a shell prompt.
Can you test from your shell?
I had a table thusly created in test.db
create table t(a);
insert into t values('test1');
insert into t values('test2');
sqlite3 -cmd .timeout 2000 test.db select * from t;
test1
test2
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of John [sql...@johneday.com]
Sent: Thursday, September 27, 2012 9:21 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Sending SQLite3 .timeout command in Command-Line Mode
After testing both commands I get the following error:
error sqlite3: unknown option: -cmd
Use -help for a list of options. number 1
On Thu, Sep 27, 2012 at 10:07 AM, Black, Michael (IS)
michael.bla...@ngc.com wrote:
The command does need to be in quotes if it's more than one word. sqilte3
expects 1 argument for the command.
I'm not familiar with applescript but I assume you have to escape quotes
to make them actually appear in the output.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on behalf of John [sql...@johneday.com]
Sent: Thursday, September 27, 2012 9:02 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Sending SQLite3 .timeout command in
Command-Line Mode
I can't find anything about the -cmd switch outside of the official docs
either!
Does the command need to be escaped as it is in your example
set xxx to do shell script sqlite3 -cmd \.timeout 2\databasePath
\select * from table1 ;\
or can I simply use ?
set xxx to do shell script sqlite3 -cmd .timeout 2 databasePath
\select * from table1 ;\
Thanks again for revealing the (apparent) -cmd switch secret.
On Thu, Sep 27, 2012 at 9:44 AM, Black, Michael (IS)
michael.bla...@ngc.com
wrote:
sqlite3 -help
The sqlite3 shell page doesn't explain any switches...couldn't find
another page about it. Anybody???
And you're right on timeout -- it's in milliseconds.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on behalf of John [sql...@johneday.com]
Sent: Thursday, September 27, 2012 8:39 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Sending SQLite3 .timeout command in
Command-Line Mode
Thank you Michael. I can't find anything in the documentation about the
-cmd switch. Will you point me in the right direction? Also, a 2 second
timeout would be .timeout 2000 , right?
John
On Thu, Sep 27, 2012 at 8:36 AM, Black, Michael (IS)
michael.bla...@ngc.com
wrote:
Try the -cmd switch. Probably the easiest solution.
set xxx to do shell script sqlite3 -cmd \.timeout 2\
databasePath
\select * from table1 ;\
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org
]
on behalf of John [sql...@johneday.com]
Sent: Thursday, September 27, 2012 7:12 AM
To: sqlite-users
Subject: EXT :[sqlite] Sending SQLite3 .timeout command in Command-Line
Mode
Hi,
I have several different computers running an AppleScript that queries
and
writes to a SQLite3 database located in a shared folder on the network.
Occasionally a database is locked error is produced. Is there a way
of
sending a .timeout command as if I was working from the shell, in
Command-Line Mode? I understand I can write an error handler which will
accomplish the same thing but I am trying to avoid that option.
property databaseFolder : POSIX path of (path to public folder as
text)
Databases/
property databaseName : myDatabase
property databasePath : quoted form of (databaseFolder databaseName
astext)
property table1 : Main
set xxx to do shell script sqlite3 databasePath \select *
from table1 ;
\
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] EXT :Re: Sending SQLite3 .timeout command in Command-Line Mode
I should note I'm using 3.7.13
-cmd wasn't in there back in 3.7.4 -- not sure when it shows up.
You may need to upgrade.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Black, Michael (IS) [michael.bla...@ngc.com]
Sent: Thursday, September 27, 2012 9:26 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] EXT :Re: Sending SQLite3 .timeout command in Command-Line
Mode
This works for me just from a shell prompt.
Can you test from your shell?
I had a table thusly created in test.db
create table t(a);
insert into t values('test1');
insert into t values('test2');
sqlite3 -cmd .timeout 2000 test.db select * from t;
test1
test2
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of John [sql...@johneday.com]
Sent: Thursday, September 27, 2012 9:21 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Sending SQLite3 .timeout command in Command-Line Mode
After testing both commands I get the following error:
error sqlite3: unknown option: -cmd
Use -help for a list of options. number 1
On Thu, Sep 27, 2012 at 10:07 AM, Black, Michael (IS)
michael.bla...@ngc.com wrote:
The command does need to be in quotes if it's more than one word. sqilte3
expects 1 argument for the command.
I'm not familiar with applescript but I assume you have to escape quotes
to make them actually appear in the output.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on behalf of John [sql...@johneday.com]
Sent: Thursday, September 27, 2012 9:02 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Sending SQLite3 .timeout command in
Command-Line Mode
I can't find anything about the -cmd switch outside of the official docs
either!
Does the command need to be escaped as it is in your example
set xxx to do shell script sqlite3 -cmd \.timeout 2\databasePath
\select * from table1 ;\
or can I simply use ?
set xxx to do shell script sqlite3 -cmd .timeout 2 databasePath
\select * from table1 ;\
Thanks again for revealing the (apparent) -cmd switch secret.
On Thu, Sep 27, 2012 at 9:44 AM, Black, Michael (IS)
michael.bla...@ngc.com
wrote:
sqlite3 -help
The sqlite3 shell page doesn't explain any switches...couldn't find
another page about it. Anybody???
And you're right on timeout -- it's in milliseconds.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on behalf of John [sql...@johneday.com]
Sent: Thursday, September 27, 2012 8:39 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Sending SQLite3 .timeout command in
Command-Line Mode
Thank you Michael. I can't find anything in the documentation about the
-cmd switch. Will you point me in the right direction? Also, a 2 second
timeout would be .timeout 2000 , right?
John
On Thu, Sep 27, 2012 at 8:36 AM, Black, Michael (IS)
michael.bla...@ngc.com
wrote:
Try the -cmd switch. Probably the easiest solution.
set xxx to do shell script sqlite3 -cmd \.timeout 2\
databasePath
\select * from table1 ;\
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org
]
on behalf of John [sql...@johneday.com]
Sent: Thursday, September 27, 2012 7:12 AM
To: sqlite-users
Subject: EXT :[sqlite] Sending SQLite3 .timeout command in Command-Line
Mode
Hi,
I have several different computers running an AppleScript that queries
and
writes to a SQLite3 database located in a shared folder on the network.
Occasionally a database is locked error is produced. Is there a way
of
sending a .timeout command as if I was working from the shell, in
Command-Line Mode? I understand I can write an error handler which will
accomplish the same thing but I am trying to avoid that option.
property databaseFolder : POSIX path of (path to public folder as
text)
Databases/
property databaseName : myDatabase
Re: [sqlite] Sending SQLite3 .timeout command in Command-Line Mode
The other thing you should do is check the exit status of sqlite3. if not 0 then an error occurred. Plus parse the output to see if you get any errors -- in specific handle the errors you know about and show errors that need a handler. So for BUSY and LOCKED you may loop for a while retrying it. Again..don't know how applescript does this. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] :Re: DELETE Query Assistance Please
You said you need to keep something like 30 days, right? Why convert at all? What's wrong with this: delete from mytable where mytime max(mytime)-30 If you want to round it off to whole days: delete from mytable where mytime round(max(mytime)-.5)-30 Or is there something else you need to do? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to get the amalgation as individual files
You may just want to split the amalgamation code...a wee bit easier...see the split utility in this dicussion. http://sqlite.1065341.n5.nabble.com/SQLite-Amalgamation-td11315.html Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Jeff Archer [jsarc...@nanotronicsimaging.com] Sent: Friday, September 21, 2012 3:52 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Is it possible to get the amalgation as individual files On Fri, Sep 21, 2012 at 4:01 PM, Nico Williams n...@cryptonector.comwrote: Install Cygwin, use that to make the amalgamation (after you've made whatever changes to the canonical sources), then build the amalgation in native Windows. Please excuse my ignorance. OK. I have Cygwin. Fossil. I have cloned the repository and opened. I believe I now have the source tree of SQLite. Not sure what I need to do next. I have the Cygwin command prompt open and believe it is running the bash shell. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Store error messages in thread local memory
You don't say how much speed difference you see But a separate connection will have separate caches. So you could just be seeing a difference in caching behavior. One connection uses one cache so will be in L1/L2/L3 cache more often than multiple threads thrashing the cache. http://www.tomshardware.com/forum/266376-28-intel-cache Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems On Thursday, 20. September 2012 at 15:23, Richard Hipp wrote: On Thu, Sep 20, 2012 at 9:12 AM, Sebastian Krysmanski sql...@lists.manski.net (mailto:sql...@lists.manski.net) wrote: Hi, I'm trying to use SQLite in a multi-threaded application. I've done some tests and it seems that using the same connection on multiple threads is faster than having one connection per thread. However, http://www.sqlite.org/c3ref/errcode.html states: When the serialized threading mode ( http://www.sqlite.org/threadsafe.html) is in use, it might be the case that a second error occurs on a separate thread in between the time of the first error and the call to these interfaces. When that happens, the second error will be reported since these interfaces always report the most recent result. So, this is a problem in my application (and I definitely need multi threading). Obtaining an exclusive lock for the database connection, as suggested in the documentation, is not an option for me because even read only statements (SELECT) can potentially return an error. And obtaining an exclusive lock for a read statement eliminates all concurrency there is in SQLite. Every operation on an SQLite database connection operates under an exclusive mutex on that database connection, so you don't have any concurrency anyhow. So the only solution I can come up with is to make sqlite3_errmsg() (and related functions) use thread local memory. Is there (or has there ever been made) any attempt on storing the error message in thread local memory? (I'm a C# and Java developer, so I'm not sure whether thread local memory even exists in C. It does in C# and Java though.) Thread local storage has been available to C code since long before Java and C# were even invented. But it is accessed through library routines that are not cross-platform, so we are not interested in using it in SQLite. Furthermore, making such a change would break backwards compatibility, which is a huge no-no with SQLite. Best regards Sebastian ___ sqlite-users mailing list sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org (mailto:d...@sqlite.org) ___ sqlite-users mailing list sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org (mailto:d...@sqlite.org) ___ sqlite-users mailing list sqlite-users@sqlite.org (mailto: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] Store error messages in thread local memory
I should add that one reason I mention that is that as your database grows the speed difference to to caching disappears. All has to do with the probability of hitting the caches decreases as the database grows. You may find making smaller page sizes might help too as that will reduce the # of cache ejects. Depends on how scattered your select calls are. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Black, Michael (IS) [michael.bla...@ngc.com] Sent: Thursday, September 20, 2012 8:37 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Store error messages in thread local memory You don't say how much speed difference you see But a separate connection will have separate caches. So you could just be seeing a difference in caching behavior. One connection uses one cache so will be in L1/L2/L3 cache more often than multiple threads thrashing the cache. http://www.tomshardware.com/forum/266376-28-intel-cache Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems On Thursday, 20. September 2012 at 15:23, Richard Hipp wrote: On Thu, Sep 20, 2012 at 9:12 AM, Sebastian Krysmanski sql...@lists.manski.net (mailto:sql...@lists.manski.net) wrote: Hi, I'm trying to use SQLite in a multi-threaded application. I've done some tests and it seems that using the same connection on multiple threads is faster than having one connection per thread. However, http://www.sqlite.org/c3ref/errcode.html states: When the serialized threading mode ( http://www.sqlite.org/threadsafe.html) is in use, it might be the case that a second error occurs on a separate thread in between the time of the first error and the call to these interfaces. When that happens, the second error will be reported since these interfaces always report the most recent result. So, this is a problem in my application (and I definitely need multi threading). Obtaining an exclusive lock for the database connection, as suggested in the documentation, is not an option for me because even read only statements (SELECT) can potentially return an error. And obtaining an exclusive lock for a read statement eliminates all concurrency there is in SQLite. Every operation on an SQLite database connection operates under an exclusive mutex on that database connection, so you don't have any concurrency anyhow. So the only solution I can come up with is to make sqlite3_errmsg() (and related functions) use thread local memory. Is there (or has there ever been made) any attempt on storing the error message in thread local memory? (I'm a C# and Java developer, so I'm not sure whether thread local memory even exists in C. It does in C# and Java though.) Thread local storage has been available to C code since long before Java and C# were even invented. But it is accessed through library routines that are not cross-platform, so we are not interested in using it in SQLite. Furthermore, making such a change would break backwards compatibility, which is a huge no-no with SQLite. Best regards Sebastian ___ sqlite-users mailing list sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org (mailto:d...@sqlite.org) ___ sqlite-users mailing list sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org (mailto:d...@sqlite.org) ___ sqlite-users mailing list sqlite-users@sqlite.org (mailto: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
Re: [sqlite] Store error messages in thread local memory
Wow...almost 6X differencewould you care to share your test code? I would imagine 50 threads would be MORE than 2X faster if caching is the cause. So if you run a test and time 10,20,30..100 threads what kind of curve is seen? Then do the same for single connection. The ratio of those entries would be quite enlightening. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Sebastian Krysmanski [sql...@lists.manski.net] Sent: Thursday, September 20, 2012 8:46 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Store error messages in thread local memory I tested with a database containing one table with 50,000 entries. I then ran SELECT * on this table from 100 concurrent threads where each thread randomly selected 20,000 table entries. The results are: * using a single connection for all threads: 11 seconds * using one connection per thread: 59,3 seconds On Thursday, 20. September 2012 at 15:37, Black, Michael (IS) wrote: You don't say how much speed difference you see But a separate connection will have separate caches. So you could just be seeing a difference in caching behavior. One connection uses one cache so will be in L1/L2/L3 cache more often than multiple threads thrashing the cache. http://www.tomshardware.com/forum/266376-28-intel-cache Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems On Thursday, 20. September 2012 at 15:23, Richard Hipp wrote: On Thu, Sep 20, 2012 at 9:12 AM, Sebastian Krysmanski sql...@lists.manski.net (mailto:sql...@lists.manski.net) wrote: Hi, I'm trying to use SQLite in a multi-threaded application. I've done some tests and it seems that using the same connection on multiple threads is faster than having one connection per thread. However, http://www.sqlite.org/c3ref/errcode.html states: When the serialized threading mode ( http://www.sqlite.org/threadsafe.html) is in use, it might be the case that a second error occurs on a separate thread in between the time of the first error and the call to these interfaces. When that happens, the second error will be reported since these interfaces always report the most recent result. So, this is a problem in my application (and I definitely need multi threading). Obtaining an exclusive lock for the database connection, as suggested in the documentation, is not an option for me because even read only statements (SELECT) can potentially return an error. And obtaining an exclusive lock for a read statement eliminates all concurrency there is in SQLite. Every operation on an SQLite database connection operates under an exclusive mutex on that database connection, so you don't have any concurrency anyhow. So the only solution I can come up with is to make sqlite3_errmsg() (and related functions) use thread local memory. Is there (or has there ever been made) any attempt on storing the error message in thread local memory? (I'm a C# and Java developer, so I'm not sure whether thread local memory even exists in C. It does in C# and Java though.) Thread local storage has been available to C code since long before Java and C# were even invented. But it is accessed through library routines that are not cross-platform, so we are not interested in using it in SQLite. Furthermore, making such a change would break backwards compatibility, which is a huge no-no with SQLite. Best regards Sebastian ___ sqlite-users mailing list sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org (mailto:d...@sqlite.org) ___ sqlite-users mailing list sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org (mailto:d...@sqlite.org) ___ sqlite-users mailing list sqlite-users@sqlite.org (mailto:sqlite-users
Re: [sqlite] Store error messages in thread local memory
What's your threading mode? http://www.sqlite.org/threadsafe.html Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Sebastian Krysmanski [sql...@lists.manski.net] Sent: Thursday, September 20, 2012 10:25 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Store error messages in thread local memory It's the whole process including creating threads, opening database connections and waiting for the threads to finish. However, startup time is negligible (as expected). Here are some results where opening and closing of connections as well as compiling statements is excluded from the elapsed time: -- SELECT_COUNT: 1,000,000 THREAD_COUNT: 2 Testing with one connections (ReadWrite) and filled table... Elapsed: 91.0 s Testing with one connections (ReadWrite) and filled table... Elapsed: 66.3 s -- SELECT_COUNT: 133,333 THREAD_COUNT: 15 Testing with one connections (ReadWrite) and filled table... Elapsed: 11.6 s Testing with one connections (ReadWrite) and filled table... Elapsed: 51.6 s -- SELECT_COUNT: 20,000 THREAD_COUNT: 100 Testing with one connections (ReadWrite) and filled table... Elapsed: 11.5 s Testing with one connections (ReadWrite) and filled table... Elapsed: 55.9 s On Thursday, 20. September 2012 at 16:22, Teg wrote: Hello Sebastian, Is this total time or time just of the DB access? I'm wondering how much of this is just opening the connection overhead time versus query time. Assuming the overhead of creating 100 threads is the same. I'm be interested in knowing how long it takes assuming you don't start timing it till after all 100 threads have opened the connections to the file. Wonder if running this same test 100 times in a row for each mode, leaving the connections open in between, would show the timing's converging? Basically reducing the affect of the startup overhead. C Thursday, September 20, 2012, 9:46:07 AM, you wrote: SK I tested with a database containing one table with 50,000 entries. SK I then ran SELECT * on this table from 100 concurrent threads SK where each thread randomly selected 20,000 table entries. SK The results are: SK * using a single connection for all threads: 11 seconds SK * using one connection per thread: 59,3 seconds SK On Thursday, 20. September 2012 at 15:37, Black, Michael (IS) wrote: You don't say how much speed difference you see But a separate connection will have separate caches. So you could just be seeing a difference in caching behavior. One connection uses one cache so will be in L1/L2/L3 cache more often than multiple threads thrashing the cache. http://www.tomshardware.com/forum/266376-28-intel-cache Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems On Thursday, 20. September 2012 at 15:23, Richard Hipp wrote: On Thu, Sep 20, 2012 at 9:12 AM, Sebastian Krysmanski sql...@lists.manski.net (mailto:sql...@lists.manski.net) wrote: Hi, I'm trying to use SQLite in a multi-threaded application. I've done some tests and it seems that using the same connection on multiple threads is faster than having one connection per thread. However, http://www.sqlite.org/c3ref/errcode.html states: When the serialized threading mode ( http://www.sqlite.org/threadsafe.html) is in use, it might be the case that a second error occurs on a separate thread in between the time of the first error and the call to these interfaces. When that happens, the second error will be reported since these interfaces always report the most recent result. So, this is a problem in my application (and I definitely need multi threading). Obtaining an exclusive lock for the database connection, as suggested in the documentation, is not an option for me because even read only statements (SELECT) can potentially return an error. And obtaining an exclusive lock for a read statement eliminates all concurrency there is in SQLite. Every operation on an SQLite database connection operates under an exclusive mutex on that database connection, so you don't have any concurrency anyhow
Re: [sqlite] sqliteman vs sqlite3 tcl package
2 things
#1 Create indexes on testTable.deTestRecordId, dataXyTable.deTestRecordid, and
testTable.testName.
#2 Do you really need the LIKE operator? That's going to scan the entire
table every time. If you can change that to = you'll likely run a lot faster
too.
This shouild speed up both
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of yuiop [hafer...@gmail.com]
Sent: Tuesday, September 18, 2012 12:24 AM
To: sqlite_us...@googlegroups.com
Subject: EXT :[sqlite] sqliteman vs sqlite3 tcl package
I have posted a question in the comp.lang.tcl group
herehttps://groups.google.com/forum/?fromgroups=#!topic/comp.lang.tcl/SF9pxG7J1RY
and
someone suggested that I post here as well. So here it goes...
I am currently trying to use the tcl sqlite3 package to perform a database
query. The database I am trying to extract data from is pretty large and
consists of several tables all linked together by a common column id.
I have a SELECT command that works but it takes a very long time (approx 3
seconds). I am comparing this time to the time it takes for the program
Sqliteman to run the exact same search (approx 0.3 s). Both searches were
performed on the same machine against the same database file.
Is Sqliteman configuring the database somehow to perform more optimal
database queries? Or is tcl truly that much slower?
Other info:
At the moment there are 500,000 rows in 2 different tables. Every hour the
number of rows increases by ~3200. This will go on for about another week.
The time it takes the db eval SELECT ... command to run has been getting
longer and longer in Tcl. At 24 hours (~76000 rows) the command took about
0.5 seconds to run. Now its up around 3 seconds. Here is one of the queries
that I perform. It simply gets all of the x values for a particular test
name from a table named dataXyTable.
puts [time {
dbName eval {
SELECT
dataXyTable.x
FROM
testTable, dataXyTable
WHERE
testTable.deTestRecordId=dataXyTable.deTestRecordId
AND
testTable.testName LIKE '$testName'
}
}
The contents of the {} run in sqliteman.exe approx 10 times faster no
matter how big the database is.
In case its important, the two tables have the following format:
CREATE TABLE testTable(\
fileId INTEGER NOT NULL,\
deTestRecordId INTEGER UNIQUE NOT NULL,\
testName VARCHAR(256) NOT NULL,\
dataType VARCHAR(16) NOT NULL,\
dataTable VARCHAR(20) NOT NULL)
CREATE TABLE dataXyTable(\
fileId INTEGER NOT NULL,\
deTestRecordId INTEGER NOT NULL,\
x REAL NOT NULL,\
y REAL NOT NULL)
Thanks in advance to anyone that can shed some light on this.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Count(*) help
You have a bad table structure which is helping to cause your problem. It's pretty obvious that you don't want one column per year, you want want a membership table that has member,year, and paid status (or whatever info you're keeping for year). The way you have you have to modify your database and code every yearyuckplus your query isn't guaranteed as you've discovered. And you should have one query for every year in order to guarantee your resultsyuck With the right table structure you never have to modify anything again (until year overflows :-). pragma foreign_keys = on; create table member(memberid primary key); create table dues(year integer,status integer,duesid integer, foreign key(duesid) references member(memberid)); insert into member values(1); insert into member values(2); insert into member values(3); insert into member values(4); insert into member values(5); insert into dues values(2007,1,1); insert into dues values(2008,1,1); insert into dues values(2009,1,1); insert into dues values(2010,1,1); insert into dues values(2011,1,1); insert into dues values(2012,1,1); insert into dues values(2013,1,1); insert into dues values(2007,NULL,2); insert into dues values(2008,1,2); insert into dues values(2009,1,2); insert into dues values(2010,1,2); insert into dues values(2011,1,2); insert into dues values(2012,1,2); insert into dues values(2013,1,2); insert into dues values(2007,NULL,3); insert into dues values(2008,NULL,3); insert into dues values(2009,1,3); insert into dues values(2010,1,3); insert into dues values(2011,1,3); insert into dues values(2012,1,3); insert into dues values(2013,1,3); insert into dues values(2007,NULL,4); insert into dues values(2008,NULL,4); insert into dues values(2009,NULL,4); insert into dues values(2010,1,4); insert into dues values(2011,1,4); insert into dues values(2012,1,4); insert into dues values(2013,1,4); select year,count(*) from dues group by year order by year; 2007|4 2008|4 2009|4 2010|4 2011|4 2012|4 2013|4 sqlite select year,count(status) from dues group by year order by year; 2007|1 2008|2 2009|3 2010|4 2011|4 2012|4 2013|4 You can see that count(status) skips the NULL entries. Or, if you for some reason you don't want to store NULLs (perhaps you have multiple possibilities there) you can store 0 for non-paid and do this: select year,count(status) from dues where status=1 group by year order by year; Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of John Clegg [john.cl...@nailsea.net] Sent: Monday, September 17, 2012 1:59 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Count(*) help OK thanks folks. Here is the full query (which is why I can't use WHERE clauses! It has always worked before.. I have removed the double-quoted but it makes no difference. SELECT COUNT( Year2007 ), COUNT( Year2008 ), COUNT( Year2009 ), COUNT( Year2010 ), COUNT( Year2011 ), COUNT( Year2012 ), COUNT( Year2013 ) FROM Members On 16 September 2012 17:48, Bart Smissaert bart.smissa...@gmail.com wrote: Hi John, Funny seeing you here on the SQLite forum. Are these by any chance the ISUG members? Doing a count without a WHERE clause is always likely to give different results with the various SQL implications as far as I know. Why not add a WHERE? RBS On Sun, Sep 16, 2012 at 5:17 PM, John Clegg john.cl...@nailsea.net wrote: I have a table Members with 896 rows and a text field Year2012. It contains Paid 156 times, Comp 13 times and the rest are null (confirmed in sqlitebrowser as empty) Back in the olden days when this table was in Access, select count(Year2013) from Members used to return 169. In LibreOfiice with the data stored in embedded HSQL it returns 169. In LibreOffice connecting to sqlite3 it returns 896. Any ideas please? ___ 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] Count(*) help
Or just fix the existing table: update members set year2007=NULL where year2007=''; update members set year2008=NULL where year2008=''; update members set year2009=NULL where year2009=''; update members set year2010=NULL where year2010=''; update members set year2011=NULL where year2011=''; update members set year2012=NULL where year2012=''; Then the counts should be what you want. I'll note that Oracle doesn't count ''. whereas sqlite3 does. Does the SQL standard say anything about what a NULL value is? And who's correct here if there is a standard? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Count(*) help
That's the nice thing about standards...there's so many to choose from.
One man's NULL is another's length=0, is another's empty string of ''.
http://en.wikipedia.org/wiki/Null_%28SQL%29
IMHO sqlite3 gets it right and Oracle 11.2.0.1.0 gets it wrong...
Purportedly:
Null is defined by the ISO SQL standard as different from both an empty string
or the numerical value 0
In sqlite3 3.7.13:
SQLite version 3.7.13 2012-06-11 02:05:22
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table t(a);
sqlite insert into t values(NULL);
sqlite insert into t values('');
sqlite select count(a) from t;
1
sqlite select count(*) from t where a is null;
1
sqlite select count(*) from t where a ='';
1
sqlite select count(*) from t where length(a) = 0;
1
SQL create table t(a varchar(255));
Table created.
SQL insert into t values(NULL);
1 row created.
SQL insert into t values('');
1 row created.
SQL select count(a) from t;
COUNT(A)
--
0
SQL select count(*) from t where a is null;
COUNT(*)
--
2
SQL select count(*) from t where a = '';
COUNT(*)
--
0
SQL select count(*) from t where length(a) = 0;
COUNT(*)
--
0
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of John Clegg [john.cl...@nailsea.net]
Sent: Monday, September 17, 2012 10:05 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Count(*) help
Yes, that was the problem thanks. Even though sqlitebrowser declared them
as empty with '' !
On 17 September 2012 15:50, Black, Michael (IS) michael.bla...@ngc.comwrote:
Or just fix the existing table:
update members set year2007=NULL where year2007='';
update members set year2008=NULL where year2008='';
update members set year2009=NULL where year2009='';
update members set year2010=NULL where year2010='';
update members set year2011=NULL where year2011='';
update members set year2012=NULL where year2012='';
Then the counts should be what you want.
I'll note that Oracle doesn't count ''. whereas sqlite3 does. Does the
SQL standard say anything about what a NULL value is? And who's correct
here if there is a standard?
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
___
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] Getting query results as new records are added
If you use rowid correctly (always incrementing rowid by using AUTOINCREMENT) you can always query records lastrowid. That's probably easier. http://www.sqlite.org/autoinc.html So something like: startrowid = 0; lastrowid = select max(rowid) from mytable; select * from mytable where rowid = startrowid and .; startrowid = lastrowid + 1; Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Paul Vercellotti [pverce...@yahoo.com] Sent: Thursday, September 13, 2012 2:20 PM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] Getting query results as new records are added Hi there, I'm wondering if there's a way to get incremental results to a query after it's started, that includes new records added after the query began? That is, we've got a UI view that's showing query results, while a background task is adding records to the database, some of which may match our query. We'd like update the query results view with new records as they're added, without having to repeat the whole query and weed out the results we're already showing? Any suggestions are appreciated. -Paul ___ 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] C++ - HOW MANY rows?
Try using this method...you just need to ensure mystmt is set to NULL to start
with and reset to NULL in finalize().
const int mySQLite3::read_int(int pos)
throw(somexception) {
if (mystmt == NULL) { // Ensure mystmt is set to NULL in constructor
rc = sqlite3_prepare_v2(db, SQLStatement.c_str(), -1, mystmt, NULL);
if(rc != SQLITE_OK) {
try {
this-display(rc, FILE, METHOD, LINE);
} catch(somexception e) {
throw e;
}
}
}
rc = sqlite3_step(mystmt);
if(rc == SQLITE_ROW ) {
apint = sqlite3_column_int(mystmt,pos);
counter++;
return apint;
}
if (rc != SQLITE_DONE) {
this-display(rc,FILE,METHOD,LINE+:+sqlite3_errmsg(db));
}
try {
this-finalize(); // ensure mystmt set to null in finalize
} catch(somexception e) {
throw e;
}
return -1; // how do you know when you're done? Will -1 work?
}
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Arbol One [arbol...@gmail.com]
Sent: Tuesday, September 11, 2012 9:44 PM
To: 'General Discussion of SQLite Database'
Subject: EXT :Re: [sqlite] C++ - HOW MANY rows?
Thanks Igor for your prompt response.
Since the call to the sqlite3_step function is inside the mySQLite3::read_*
class-function/method, I call the read_* method from Runner::read_tblName()
for each datum I need to retrieve. Now, in a while loop inside
Runner::read_tblName() I could call the mySQLite3::read_* for the data to be
retrieved, but instead of reading the next row of data, it keeps on reading
the same row again and again. Furthermore, I have no way to let
Runner::read_tblName 'know' that there is no more data, hence stopping the
while loop.
Obviously my problem is in design, but I don't have enough experience using
SQLite3 to come up with a better idea.
My question is, really, how do you, C++ programmers, have resolved this
issue? There must be a solution.
void Runner::read_tblName() {
.
sql_statement = SELECT * FROM name;
//while(there is more data){
// I have no-way to report to this method that there is no more data in the
bank
// I could add a flag to the mySQLite3 class and have the while loop check
on its status,
// or a signal that would be trigger by the ending of the while loop.
db-setStmt(sql_statement);
int pos = 0;
data1 = db-read_int(pos);
db-setStmt(sql_statement);
pos = 1;
data2 = db-read_str(pos);
data3 = db-read_str(++pos);
data4 = db-read_str(++pos);
data5 = db-read_str(++pos);
Glib::ustring str;
str = apstr.format(data1);
str += ;
str += data2;
str += . ;
str += data3;
str += ;
str += data4;
str += ;
str += data5;
apex-setException(str, FILE, METHOD, LINE);
apex-Display();
// } ---
}
const int mySQLite3::read_int(int pos)
throw(somexception) {
rc = sqlite3_prepare_v2(db, SQLStatement.c_str(), -1, mystmt, NULL);
if(rc != SQLITE_OK) {
try {
this-display(rc, FILE, METHOD, LINE);
} catch(somexception e) {
throw e;
}
} else {
counter++;
}
rc = sqlite3_step(mystmt);
if(rc == SQLITE_ROW ) {
apint = sqlite3_column_int(mystmt,pos);
}
try {
this-finalize();
} catch(somexception e) {
throw e;
}
return apint;
}
const Glib::ustring mySQLite3::read_str(const int pos)
throw(somexception) {
//if(pos == 0) {
rc = sqlite3_prepare_v2(db, this-SQLStatement.c_str(), -1, mystmt,
NULL);
//}
if(rc != SQLITE_OK) {
try {
this-display(rc, FILE, METHOD, LINE);
} catch(somexception e) {
throw e;
}
} else {
counter++;
}
rc = sqlite3_step(mystmt);
if(rc == SQLITE_ROW ) {
apstr = (const char*)sqlite3_column_text(mystmt,pos);
}
try {
this-finalize();
} catch(somexception e) {
throw e;
}
return apstr;
}
___
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] selecting real values
A quick experiment shows that 3.7.14 rounds off the last 2 digits of a double-precision. As of 3.7.14 sqlite3 rounds to 15 significant digits when using the internal formatting routines. sqlite3 test.db SQLite version 3.7.14 2012-09-03 15:42:36 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table t(f real); sqlite insert into t values(1.7976931348623157e+308); sqlite select * from t; 1.79769313486232e+308 Full IEEE precision appears to be stored correctly for this value. 1.7976931348623157e+308 = 0x7FEF http://babbage.cs.qc.cuny.edu/IEEE-754/ http://en.wikipedia.org/wiki/Double-precision_floating-point_format od -x test.db | tail -n 2 0003760 010a 0702 ef7f 0004000 Of course, if you print out this value yourself in your program you can get as many digits as you want. As always many caveats apply to real precisionyou have to be very careful with all sorts of things like lack of precision, rounding errors, etc. Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Baruch Burstein [bmburst...@gmail.com] Sent: Tuesday, September 11, 2012 6:18 AM To: General Discussion of SQLite Database Subject: EXT :[sqlite] selecting real values When selecting real (float) values, does the sqlite return (and the shell display) the full precision it has by default, or does it have a higher precision stored in the database than it displays? -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ 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] selecting real values
There are 3 places in 3.7.14 sqlite3.c where %!.15g format is used. Ergo 15
significant digits on output.
57186:sqlite3_snprintf(nByte, pMem-z, %!.15g, pMem-r);
62788:sqlite3XPrintf(out, %!.15g, pVar-r);
85973: sqlite3_snprintf(sizeof(zBuf), zBuf, %!.15g, r1);
For this example, changing 57186 to 17g almost gets the answer correct:
sqlite create table t(f real);
sqlite insert into t values(1.7976931348623157e+308);
sqlite select * from t;
1.7976931348623156e+308
The odds you hitting a random number that isn't represented correctly is pretty
slim.
You can show the loss in when using %!.17g
sqlite insert into t values(1.7976931348623157e+308);
sqlite select * from t;
1.7976931348623156e+308
sqlite insert into t values(1.7976931348623156e+308);
sqlite select * from t;
1.7976931348623156e+308
1.7976931348623154e+308
sqlite insert into t values(1.7976931348623154e+308);
sqlite select * from t;
1.7976931348623156e+308
1.7976931348623154e+308
1.7976931348623152e+308
sqlite insert into t values(1.7976931348623154e+308);
sqlite insert into t values(1.7976931348623152e+308);
sqlite select * from t;
1.7976931348623156e+308
1.7976931348623154e+308
1.7976931348623152e+308
1.7976931348623152e+308
1.797693134862315e+308
1.7976931348623156083e+308 is the highest value for 7FEE
1.79769313486231580793e+308 is the highest value for 7FEF
That's why the last digit of 7 is the most correct as it's the average error.
I found this one added line seems to fix the problem.
/* Significant digits after the decimal point */
while( (precision--)0 ){
*(bufpt++) = et_getdigit(realvalue,nsd);
}
if ( realvalue =5 ) (*(bufpt-1))++; // round up
I'm unsure if any additional checks are required...since everything should be a
power of 2 on the last digit you should only be increasing even numbers so I
don't think roll over should occur to the next higher digit.
With that change you can insert and select and not lose precision
SQLite version 3.7.14 2012-09-03 15:42:36
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table t(f real);
sqlite insert into t values(1.7976931348623156e+308);
sqlite select * from t;
1.7976931348623155e+308
sqlite insert into t values(1.7976931348623155e+308);
sqlite select * from t;
1.7976931348623155e+308
1.7976931348623155e+308
sqlite insert into t values(1.7976931348623157e+308);
sqlite select * from t;
1.7976931348623155e+308
1.7976931348623155e+308
1.7976931348623157e+308
sqlite insert into t values(1.7976931348623158e+308);
sqlite select * from t;
1.7976931348623155e+308
1.7976931348623155e+308
1.7976931348623157e+308
1.7976931348623157e+308
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Tuesday, September 11, 2012 8:01 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] selecting real values
On 11 Sep 2012, at 1:56pm, Black, Michael (IS) michael.bla...@ngc.com wrote:
A quick experiment shows that 3.7.14 rounds off the last 2 digits of a
double-precision.
I think we found that the rounding was happening during the translation from
text input to having the number stored in the file.
Writing a C app which took random 64-bit floats, stored them in a SQLite
database, and retrieved them, yielded unchanged values. Dumping the bit
pattern as actually stored in the files suggested that the numbers were being
stored intact.
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
Re: [sqlite] : C# access to SQLite and Windows 8
Have you looked at this? http://timheuer.com/blog/archive/2012/08/07/updated-how-to-using-sqlite-from-windows-store-apps.aspx Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems _ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
Does this work for you?
SQLite version 3.7.13 2012-06-11 02:05:22
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table t(s);
sqlite insert into t values('Logging in user [aa] from
[10.165.69.247]');
sqlite insert into t values('194|2012-09-07|Logging in user [a] from
[10.296.44.163]');
sqlite insert into t values('160|2012-09-04|Logging in user [aaa] from
[10.164.69.248]');
sqlite insert into t values('136|2012-09-07|Logging in user [aaa] from
[10.168.59.169]');
sqlite insert into t values('132|2012-09-07|Logging in user [aaa] from
[10.169.22.58]');
sqlite select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Sébastien Roux [roux.sebast...@gmail.com]
Sent: Monday, September 10, 2012 10:22 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] instr function or equivalent
Sad! Would you have any link toward SQLite's user defined SQLite function?
Many thanks.
Sébastien Roux
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
It might be faster but it doesn't work for anybody who has any letters in
from in their name.
sqlite insert into t values('132|2012-09-07|Logging in user [tom] from
[10.169.22.59]');
sqlite select rtrim(s,' from [.0123456789]') || ']' from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [t]
The original way still works just fine.
sqlite select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [tom]
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
You have to be very careful when parsing char sets like this to ensure your
barriers are valid.
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Bart Smissaert [bart.smissa...@gmail.com]
Sent: Monday, September 10, 2012 11:19 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] instr function or equivalent
This is slightly faster:
select rtrim(s,' from [.0123456789]') || ']' from t
RBS
On 9/10/12, Bart Smissaert bart.smissa...@gmail.com wrote:
Nice one! Works here.
RBS
On 9/10/12, Black, Michael (IS) michael.bla...@ngc.com wrote:
Does this work for you?
SQLite version 3.7.13 2012-06-11 02:05:22
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table t(s);
sqlite insert into t values('Logging in user [aa] from
[10.165.69.247]');
sqlite insert into t values('194|2012-09-07|Logging in user [a] from
[10.296.44.163]');
sqlite insert into t values('160|2012-09-04|Logging in user [aaa]
from
[10.164.69.248]');
sqlite insert into t values('136|2012-09-07|Logging in user [aaa] from
[10.168.59.169]');
sqlite insert into t values('132|2012-09-07|Logging in user
[aaa]
from [10.169.22.58]');
sqlite select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on
behalf of Sébastien Roux [roux.sebast...@gmail.com]
Sent: Monday, September 10, 2012 10:22 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] instr function or equivalent
Sad! Would you have any link toward SQLite's user defined SQLite
function?
Many thanks.
Sébastien Roux
___
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] C++ - WHERE clause - 2nd update
You're example should work if you only prepare the statement once.
So assuming mystmt is set to NULL on your object creation.
if (mystmt == NULL) {
rc = sqlite_prepare_v2.
}
Then reset it to NULL again when you set apstr=finished. After
sqlite3_finalize(mystmt). That way you're next query will reprepare the
statement again.
}else{
if (rc == SQLITE_DONE) {
apstr = finished;
this-finalize();
mystmt = NULL; // is this being done in your finalize??? It could be put in
there instead of here.
}
else {
apstr = error: + sqlite3_errmsg(db);
this-finalize();
mystmt = NULL;
}
}
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Arbol One [arbol...@gmail.com]
Sent: Friday, September 07, 2012 4:07 AM
To: 'General Discussion of SQLite Database'
Subject: EXT :Re: [sqlite] C++ - WHERE clause - 2nd update
Yes, thank?
I gave you the answer you gave me, obviously I was right.
I need you to prove me right again, how about, haaa yes! if 2x2 is 4,
what is 2x2? Come on! I know you can get it, just try going slowly this
time.
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Marcus Grimm
Sent: Friday, September 07, 2012 3:39 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] C++ - WHERE clause - 2nd update
On 07.09.2012 08:58, Arbol One wrote:
I got this code to work, however, I am getting a segmentation fault on
this code.
I pass to SQLite only one statement [db-setStmt(apstr);], I read the
first of the 'fname', but I don't know how to get to the second
'fname' in the database.
I am not very sure as to what do to tell the program to read the next
row until there are no more [ read_str until SQLITE_DONE ] rows to read.
well.. you already answered your question:
You step thru the result list until you reach SQLITE_DONE.
In your example you re prepare the statement all the time and thus you will
always get the first hit in your data.
The sequence should be:
sqlite3_prepare_v2
while( sqlite3_step(mystmt) == SQLITE_ROW ) {
/** read the data .. **/
}
sqlite3_finalize
...
Help?
Glib::ustring apstr;
Glib::ustring sName;
int apint;
mySQLite3* db;
try {
db = new mySQLite3(db_name.c_str());
} catch(somexception e) {
//do something
}
// SQL statement
Glib::ustring sName;
apstr = SELECT fname FROM ;
apstr += this-db_table_name;
apstr += WHERE title = \';
apstr += token;
apstr += \' ;
apint = 0;
db-setStmt(apstr);
do{
try {
sName = db-read_str(apint);
} catch(jme::Exception e ) {
e.Display();
}
apex.setException(sName, FILE, METHOD, LINE);
apex.Display();
}while(sName != finished);
const Glib::ustring mySQLite3::read_str(const int pos)
throw(somexception) {
rc = sqlite3_prepare_v2(db, this-SQLStatement.c_str(),
-1,mystmt, NULL);
if(rc != SQLITE_OK) {
// do something
}
rc = sqlite3_step(mystmt);
if(rc == SQLITE_ROW ) {
apstr = (const char*)sqlite3_column_text(mystmt,pos);
}else{
apstr = finished; // a small modification
}
try {
this-finalize();
} catch(somexception e) {
throw e;
}
return apstr;
}
What am I doing wrong?
___
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] C++ - WHERE clause
And, when you have problems, you should always examine your SQL by running the
EXACT same string you generate in your program through the sqlite3 shell. This
will help you to figure out if your SQL is wrong or your C++ is wrong.
For example even just your SELECT portion generates the wrong SQL.
You end up with this (I'm already assuming you get rid of the your last += of
(n_id,... as you don't need it for a SELECT statement as already pointed out.
SELECT fname FROM table WHERE title = token
And what you want is
SELECT fname FROM table WHERE title = 'token'
So you need to add a single quote on both sides of adding your token.
apstr += ';
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Baruch Burstein [bmburst...@gmail.com]
Sent: Thursday, September 06, 2012 3:45 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] C++ - WHERE clause
VALUES is used for INSERTing into a table, not for SELECTing. This is not
valid SQL (I would help you fix it, but I can't figure out what you were
trying to achieve.)
Here is a great reference: http://sqlite.org/lang_select.html
On Thu, Sep 6, 2012 at 11:18 AM, Arbol One arbol...@gmail.com wrote:
As many of you know, I am trying to learn SQL using C++.
Below is an error I get when I try using the C++ example below it.
Error Code: 1
Error Message: near VALUES: syntax error
Glib::ustring apstr;
Glib::ustring sName;
int apint;
mySQLite3* db;
try {
db = new mySQLite3(db_name.c_str());
} catch(somexception e) {
//do something
}
// SQL statement
apstr = SELECT fname FROM ;
apstr += this-db_table_name;
apstr += WHERE title = ;
apstr += token;
apstr += (n_id, title, fname, mname, lname) VALUES (?, ?, ?, ?, ?);
// here is where the problem is see the method below
apint = 1;
db-setStmt(apstr);
sName = db-read_str(apint);
const Glib::ustring mySQLite3::read_str(const int pos)
throw(jme::Exception) {
rc = sqlite3_prepare_v2(db, this-SQLStatement.c_str(), -1, mystmt,
NULL);
if(rc != SQLITE_OK) {
// do something
}
rc = sqlite3_step(mystmt);
if(rc == SQLITE_ROW ) {
apstr = (const char*)sqlite3_column_text(mystmt,pos);
}
try {
this-finalize();
} catch(somexception e) {
throw e;
}
return apstr;
}
What am I doing wrong?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
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] C++ - WHERE clause
Yeah -- I should've been in a better teaching mode Trying to keep things simple opens up these type of security problemsthough there are lots of situations where this works just fine and is no problem at all (e.g. when you don't have user input or it's completely under your own control like a learning program). It's good to get in the habit of not doing it the simple and possibly insecure way. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Richard Hipp Bad idea. See SQL Injection Attackhttp://en.wikipedia.org/wiki/SQL_injectionfor a detailed explanation of why this is so bad. A far better approach is to use query parameters with sqlite3_bind_(). Or, failing that, to use sqlite3_mprintf() with the %q or %Q substitutions. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Read-only media
'twould appear so...the shell even knows about a read-only database. $ sqlite3 test.db SQLite version 3.7.9 2011-11-01 00:52:41 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table test(a,b); sqlite insert into table values(1,2); Error: near table: syntax error sqlite insert into test values(1,2); sqlite insert into test values(3,4); sqlite select * from test; 1|2 3|4 sqlite .quit $ chmod a-w . $ !sq sqlite3 test.db SQLite version 3.7.9 2011-11-01 00:52:41 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select * from test; 1|2 3|4 sqlite .quit $ chmod -w test.db $ !sq sqlite3 test.db SQLite version 3.7.9 2011-11-01 00:52:41 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select * from test; 1|2 3|4 sqlite insert into test values(5,6); Error: attempt to write a readonly database Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Baruch Burstein [bmburst...@gmail.com] Sent: Thursday, September 06, 2012 8:39 AM To: General Discussion of SQLite Database Subject: EXT :[sqlite] Read-only media Can sqlite databases be read from a read-only media? I seem to remember seeing something about this on the website, but can't find it. -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ 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] EXT : C++ - WHERE clause - update
You need to :
cout this-SQLStatement.c_str() endl;
Then put that SQL into the sqlite3 shell against your database and ensure you
actually get rows back.
You also need to be sure you're looking at the same database. Many times
people have multiple copies and the one the program uses is not the same as
what they think.
Is your database path complete or relative when you open the database file?
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Arbol One [arbol...@gmail.com]
Sent: Thursday, September 06, 2012 11:14 AM
To: 'Gen eral Discussion of SQLite Database'
Subject: EXT :[sqlite] C++ - WHERE clause - update
rc = sqlite3_step(mystmt);
if(rc == SQLITE_ROW ) {
The code, in this case, does not process this statement!!?? --
apstr = (const char*)sqlite3_column_text(mystmt,pos);
std::cout apstr std::endl; //-- this is not executed
}
Table:
id | tile | fname | mname | lname |
---
void ClassforSQLite3::getList(
const Glib::ustring db_name, // Database name
const Glib::ustring token) // Key word to search in the database
throw(jme::Exception) {
Glib::ustring apstr;
Glib::ustring sName;
int apint;
mySQLite3* db;
try {
db = new mySQLite3(db_name.c_str());
} catch(somexception e) {
...
}
// SQL statement
apstr = SELECT fname FROM ;
apstr += this-db_table_name;
apstr += WHERE title = \';
apstr += token;
apstr += \' ;
apint = 1;
db-setStmt(apstr);
sName = db-read_str(apint); // here is where the problem is see the method
below
const Glib::ustring mySQLite3::read_str(const int pos)
throw(jme::Exception) {
rc = sqlite3_prepare_v2(db, this-SQLStatement.c_str(), -1, mystmt, NULL);
if(rc != SQLITE_OK) {
// do something
}
rc = sqlite3_step(mystmt);
if(rc == SQLITE_ROW ) {
// The code, in this case, does not process this statement!!?? --
apstr = (const char*)sqlite3_column_text(mystmt,pos);
}
try {
this-finalize();
} catch(somexception e) {
throw e;
}
return apstr;
}
What am I doing wrong?
___
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] why no such column in sqlite3 ?
As for sprintf what they didn't tell you is that you don't want to use that due to security considerations. If you are getting ANY data from user input they can craft sql injection attacks which sprintf is very susceptible to. Binding the values helps to ensure they can't do that. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Vaclav Peroutka [vacla...@seznam.cz] Sent: Thursday, August 30, 2012 3:51 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] why no such column in sqlite3 ? Rob Richardson wrote: Put single quotes around Testitem: sprintf( sqlquery, INSERT INTO tblTest ( CINDEX, CDATE, CDESCR, CAMOUNT ) VALUES ( 5, 2012-08-29, 'Testitem', 300 )); And around cdate too. There are no dedicated date type in sqlite, 2012-08- 29 is treated as expression ((2012 - 08) - 29). Result will be 1975, not what you might have expected. sprintf( sqlquery, INSERT INTO tblTest ( CINDEX, CDATE, CDESCR, CAMOUNT ) VALUES ( 5, '2012-08-29', 'Testitem', 300 )); And you likely should use sqlite3_prepare_v2, placeholders, sqlite3_bind_ int and sqlite3_bind_text instead of sprintf. Thank you for answers, single quotes helped. Regarding other functions, is there any example for them ? I used 5 minutes example and there is nothing like that. sprintf formatting works well for me so far. ___ 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 Synchronous=OFF is not working
Tell us what kind of speed you're seeing. And what your insert looks like. Then tell us what you expect. Then we can tell you if your expectations are reasonable or if you're already getting as much speed as one can expect. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of kritesh tripathi [tripathi.krit...@gmail.com] Sent: Tuesday, August 28, 2012 10:04 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working Thanks to suugestion I am using the Sqlite version -3.6.4 and support for WAL mode started from version 3.7.0.If i upgrade to recent sqlite version then is any possibilty to increase in performance or any other good idea . Cheers kritesh On Tue, Aug 28, 2012 at 8:11 PM, Jonathan Engle jon_en...@kace.com wrote: Try WAL mode. On Aug 28, 2012, at 9:38 AM, kritesh tripathi wrote: Hi Pavel, Thanks for the information . I am using all insert under one transection hence may be the reson its not effecting much . But do you suggest me the way to increase more insert speed what i need to do in this scenario. Is same happen for all pragma like Page_Size or Journal_Mode or Cache_size ? i tried to change the values of all pragma but i think no one effect the spped . In case i will bind the values and only prepare the stament once then do you think any improvement ? Cheers kritesh On Tue, Aug 28, 2012 at 7:43 PM, Pavel Ivanov paiva...@gmail.com wrote: If all your inserts are in one transaction then pragma synchronous = OFF won't affect your transaction speed too much. To understand whether this pragma works or not you should measure how long it takes to execute COMMIT (just this one statement). With synchronous = OFF COMMIT will be executed much faster. Pavel On Mon, Aug 27, 2012 at 7:02 AM, tripathi.kritesh tripathi.krit...@gmail.com wrote: Hi , I am executing all below mentioned pragma before start the (BEGIN --COMMIT) transaction in sqlite version (3.6.4) sqlite3_exec(mDb, “PRAGMA synchronous=OFF”, NULL, NULL, errorMessage); sqlite3_exec(mDb, “PRAGMA count_changes=OFF”, NULL, NULL, errorMessage); sqlite3_exec(mDb, “PRAGMA journal_mode=MEMORY”, NULL, NULL, errorMessage); sqlite3_exec(mDb, “PRAGMA temp_store=MEMORY”, NULL, NULL, errorMessage); In transaction , I am inserting the values in the table but I dnt know the specific reason why does not pragma effecting the insert speed .. I am getting the same speed even i use the pragma or not . please help Is these pragma effect take place in transaction ? Cheers kritesh -- View this message in context: http://sqlite.1065341.n5.nabble.com/Pragma-Synchronous-OFF-is-not-working-tp63904.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 -- Regards kritesh tripathi ___ 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 -- Regards kritesh tripathi ___ 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 Synchronous=OFF is not working
I think the first thing you'll hear is to NOT store the video data in the database. Just store a file path. That is much faster and should complete a lot faster than your expectations. Is there some specific reason why you want the blob data in your database? How long does it take you just to read and parse the images and skip the database insert? What exactly are you inserting in those 3 tables? Sounds like some big data. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of kritesh tripathi [tripathi.krit...@gmail.com] Sent: Tuesday, August 28, 2012 10:31 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working Hi Michael, Righ Now i am inserting only 500 records in three different tables in 42 sec . I have 500 imagesor video (.jpg or MP4) in the SD card which i am parsing and then inserting one by one in the video table or image table . I am expecting this in between 10-20 sec . Presently i am using the Micro itron embedded RTOS in my device . ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pragma Synchronous=OFF is not working
So you're already doing the smart thinggoodand I believe you did say you're using a memory database, right? Not storing it on the SD card? Are you able to run your timing test on a standard PC? 50 inserts/sec is definitely NOT fast (that's your .02 number). Thousands/sec is more like it on a standard PC. Did you create any indexes on your data? Are you sure you have the entire load process in one transaction? Care to show us your code? Another thing you can do is output SQL statements to stdout so you can run those inserts inside an sqlite3 shell and test different configurations that way. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of kritesh tripathi [tripathi.krit...@gmail.com] Sent: Tuesday, August 28, 2012 11:09 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working HI Michael, I am not stroing the video data in the table .For example -Suppose i have one image - ABC.jpg in the folder contains in SD Card 1- First i am parsing the image 2- Second Get the values like - Fullpath (Wht is the exact path of image in SD Card),file Size and create unique id . 3-Third , I am inserting these values in Image table . Executing the same steps for all images (folder where all images stored ) in SD card . Total time to parsing and insert the 500 image =42 sec Time to insert one image in table after parsing = .02 Sec Time to parsing one images = .04 sec Cheers kritesh On Tue, Aug 28, 2012 at 9:22 PM, Black, Michael (IS) michael.bla...@ngc.com wrote: I think the first thing you'll hear is to NOT store the video data in the database. Just store a file path. That is much faster and should complete a lot faster than your expectations. Is there some specific reason why you want the blob data in your database? How long does it take you just to read and parse the images and skip the database insert? What exactly are you inserting in those 3 tables? Sounds like some big data. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of kritesh tripathi [tripathi.krit...@gmail.com] Sent: Tuesday, August 28, 2012 10:31 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working Hi Michael, Righ Now i am inserting only 500 records in three different tables in 42 sec . I have 500 imagesor video (.jpg or MP4) in the SD card which i am parsing and then inserting one by one in the video table or image table . I am expecting this in between 10-20 sec . Presently i am using the Micro itron embedded RTOS in my device . ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Regards kritesh tripathi ___ 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 Synchronous=OFF is not working
Oh yeah...you need to upgrade your sqlite. You are hitting the SD card which is dog slow. Keeping your DB in memory may solve all of your database speed concerns. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of kritesh tripathi [tripathi.krit...@gmail.com] Sent: Tuesday, August 28, 2012 11:34 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working Hi, Sorry but i am not using memory database since sqlite version -3.6.4 does not support i guess ..I am not indexing any data and i am sure that all insertion , i am doing under one transaction . Do you think creating index or i n memory database or binding the values using sqlite_binding will effect the performance .. Is performance due to hardware like SD card speed or Filesystem ? Cheers kritesh On Tue, Aug 28, 2012 at 9:55 PM, Black, Michael (IS) michael.bla...@ngc.com wrote: So you're already doing the smart thinggoodand I believe you did say you're using a memory database, right? Not storing it on the SD card? Are you able to run your timing test on a standard PC? 50 inserts/sec is definitely NOT fast (that's your .02 number). Thousands/sec is more like it on a standard PC. Did you create any indexes on your data? Are you sure you have the entire load process in one transaction? Care to show us your code? Another thing you can do is output SQL statements to stdout so you can run those inserts inside an sqlite3 shell and test different configurations that way. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of kritesh tripathi [tripathi.krit...@gmail.com] Sent: Tuesday, August 28, 2012 11:09 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working HI Michael, I am not stroing the video data in the table .For example -Suppose i have one image - ABC.jpg in the folder contains in SD Card 1- First i am parsing the image 2- Second Get the values like - Fullpath (Wht is the exact path of image in SD Card),file Size and create unique id . 3-Third , I am inserting these values in Image table . Executing the same steps for all images (folder where all images stored ) in SD card . Total time to parsing and insert the 500 image =42 sec Time to insert one image in table after parsing = .02 Sec Time to parsing one images = .04 sec Cheers kritesh On Tue, Aug 28, 2012 at 9:22 PM, Black, Michael (IS) michael.bla...@ngc.com wrote: I think the first thing you'll hear is to NOT store the video data in the database. Just store a file path. That is much faster and should complete a lot faster than your expectations. Is there some specific reason why you want the blob data in your database? How long does it take you just to read and parse the images and skip the database insert? What exactly are you inserting in those 3 tables? Sounds like some big data. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of kritesh tripathi [tripathi.krit...@gmail.com] Sent: Tuesday, August 28, 2012 10:31 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working Hi Michael, Righ Now i am inserting only 500 records in three different tables in 42 sec . I have 500 imagesor video (.jpg or MP4) in the SD card which i am parsing and then inserting one by one in the video table or image table . I am expecting this in between 10-20 sec . Presently i am using the Micro itron embedded RTOS in my device . ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Regards kritesh tripathi ___ 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 -- Regards kritesh tripathi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pragma Synchronous=OFF is not working
And...once you confirm an in-memory database helps you, then you can try WAL mode and keep the DB on the SD card and see how that works for you. I don't know what you're trying to do since loading the images is already 66% of your time. I guess that's OK with your application? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of kritesh tripathi [tripathi.krit...@gmail.com] Sent: Tuesday, August 28, 2012 11:34 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working Hi, Sorry but i am not using memory database since sqlite version -3.6.4 does not support i guess ..I am not indexing any data and i am sure that all insertion , i am doing under one transaction . Do you think creating index or i n memory database or binding the values using sqlite_binding will effect the performance .. Is performance due to hardware like SD card speed or Filesystem ? Cheers kritesh On Tue, Aug 28, 2012 at 9:55 PM, Black, Michael (IS) michael.bla...@ngc.com wrote: So you're already doing the smart thinggoodand I believe you did say you're using a memory database, right? Not storing it on the SD card? Are you able to run your timing test on a standard PC? 50 inserts/sec is definitely NOT fast (that's your .02 number). Thousands/sec is more like it on a standard PC. Did you create any indexes on your data? Are you sure you have the entire load process in one transaction? Care to show us your code? Another thing you can do is output SQL statements to stdout so you can run those inserts inside an sqlite3 shell and test different configurations that way. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of kritesh tripathi [tripathi.krit...@gmail.com] Sent: Tuesday, August 28, 2012 11:09 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working HI Michael, I am not stroing the video data in the table .For example -Suppose i have one image - ABC.jpg in the folder contains in SD Card 1- First i am parsing the image 2- Second Get the values like - Fullpath (Wht is the exact path of image in SD Card),file Size and create unique id . 3-Third , I am inserting these values in Image table . Executing the same steps for all images (folder where all images stored ) in SD card . Total time to parsing and insert the 500 image =42 sec Time to insert one image in table after parsing = .02 Sec Time to parsing one images = .04 sec Cheers kritesh On Tue, Aug 28, 2012 at 9:22 PM, Black, Michael (IS) michael.bla...@ngc.com wrote: I think the first thing you'll hear is to NOT store the video data in the database. Just store a file path. That is much faster and should complete a lot faster than your expectations. Is there some specific reason why you want the blob data in your database? How long does it take you just to read and parse the images and skip the database insert? What exactly are you inserting in those 3 tables? Sounds like some big data. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of kritesh tripathi [tripathi.krit...@gmail.com] Sent: Tuesday, August 28, 2012 10:31 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Pragma Synchronous=OFF is not working Hi Michael, Righ Now i am inserting only 500 records in three different tables in 42 sec . I have 500 imagesor video (.jpg or MP4) in the SD card which i am parsing and then inserting one by one in the video table or image table . I am expecting this in between 10-20 sec . Presently i am using the Micro itron embedded RTOS in my device . ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Regards kritesh tripathi ___ 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 -- Regards kritesh tripathi ___ sqlite-users mailing list
Re: [sqlite] EXT :Re: Multi-Thread Reads to SQLite Database
Why should shared cached be serialized when all the threads are reading? I can see it for writing, but not just for reading. There must be some logic that be done to allow this I would think (he said without looking at the code). Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Richard Hipp [d...@sqlite.org] Sent: Friday, August 10, 2012 12:53 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Multi-Thread Reads to SQLite Database On Fri, Aug 10, 2012 at 12:18 PM, esum eric.b@lmco.com wrote: However, when I ran this same test with SQLITE_OPEN_READWRITE | SQLITE_OPEN_SHAREDCACHE for the flags, I get the following [slower] results: Why am I seeing such a high increase in times as I add threads in shared cache mode as opposed to without it? In shared-cache mode, the page cache is shared across threads. That means that each thread must acquire a mutex on the page cache in order to read it. Which means that access to the page cache is serialized. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestions for approximate date
I'd vote for the date-range as that can be indexed and result in fast retrieval. The separate column for accuracy would be a computed range and not indexable. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Oliver Schneider [sqlite-mailingl...@f-prot.com] Sent: Tuesday, August 07, 2012 9:46 AM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] Suggestions for approximate date Hello, I have a decision to make about how to store dates that may not be entirely accurate inside an SQLite DB. There are two options I came up with: 1. store exact date plus (in separate column) value for accuracy 2. store date range corresponding to original accuracy The accuracy can be exact date, only month and year, +/- 1 year, +/- 10 years, +/- 50 years. I reckon for searching the second option could be better. Does anyone here have any better ideas? I'd go for the Julian Day stored as REAL in either case. Thanks, // Oliver ___ 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] C# Dynamic data type
You can use sscanf to determine data type...I've done it before using a method
that's not obvious...
You parse from most restrictive to least restrictive format like this...this
will accept any valid float format including scientific notation.
#include stdio.h
enum {UNKNOWN, FLOAT, INT, STRING};
int datatype(char *s)
{
long i;
double f;
char buf[4096];
int n;
n = sscanf(s,%d%s,i,buf);
if (n == 1) {
printf(INT\n);
return INT;
}
n = sscanf(s,%lg%s,f,buf);
if (n == 1) {
printf(FLOAT\n);
return FLOAT;
}
n = sscanf(s,%s,buf);
if (n == 1) {
printf(STRING\n);
return STRING;
}
else {
printf(UNKNOWN\n);
return UNKNOWN; // should never get here
}
}
main()
{
char *line1=1234;
char *line2=1234.5;
char *line3=x1234.5;
datatype(line1);
datatype(line2);
datatype(line3);
}
~
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Adam DeVita [adev...@verifeye.com]
Sent: Tuesday, August 07, 2012 10:26 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] C# Dynamic data type
Good day,
I've been reading a bit of conflicted stuff online in terms of data type.
The most basic question, in C#, is can you easily determine the data
type of the Nth entry in a column.
{Ex: Create table A( x TEXT, y )
... a few inserts, binding a float, then a string, then an int into y..
select x,y from A
check the type of y before retrieving a value from it.
}
The docs for SQLiteDataReader.GetFieldType() seems to read as if it
will return the column affinity.
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] sqlite3 database unreadable on Mountain Lion
Fully qualified path names may still both load the same shared library. I assume you have ldd available? Run that on the binaries and see which library they'll load. Probably the same one unless they are statically linked. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Doug Currie [doug.cur...@gmail.com] Sent: Monday, August 06, 2012 1:48 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] sqlite3 database unreadable on Mountain Lion On Aug 6, 2012, at 8:26 AM, Simon Slavin slav...@bigfraud.org wrote: So either Apple has made a change between versions, or we have different paths. I use fully qualified pathnames here: ~ e$ /usr/bin/sqlite3 :memory: 'SELECT sqlite_source_id()' 2012-04-03 19:43:07 86b8481be7e7692d14ce762d21bfb69504af ~ e$ /usr/local/bin/sqlite3 :memory: 'SELECT sqlite_source_id()' 2012-05-14 01:41:23 8654aa9540fe9fd210899d83d17f3f407096c004 I never had a pre-release OSX ML installed. I did update /usr/local/bin/sqlite3 from sqlite.org. e ___ 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] Windows I/O (was: Initial read speed greater than subsequent)
You may be interested in this article: http://www.drdobbs.com/parallel/multithreaded-file-io/220300055?pgno=2 Mutli-threaded reading of multiple files (which is basically what you're talking about by splitting a file in half) is only faster if you have multiple disks (in this article that's a RAID-5 system). Random I/O gains a bit by threading due to the probability of intersecting common disk blocks. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Udi Karni [uka...@gmail.com] Sent: Wednesday, August 01, 2012 2:25 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Windows I/O (was: Initial read speed greater than subsequent) You are right. True Parallel Query can get very complicated. I was hoping for something very limited for starters - for example - - only 2 processes - only for simple full scans where the block range can be divided in two - only when there is no ORDER/GROUP BY where sub results from the 2 threads have to be combined Basically only for queries such as SELECT COUNT/MIN/MAX FROM TABLE WHERE Sounds very limited / what's-the-point kind of thing - but it would actually be very useful when working with large data where you find yourself doing a lot of QA and study of the data - how many rows have this range of codes / are null, etc. Having 2 processes working simultaneously might cut run times in half - and save many minutes. Going higher than 2 might hit disk read limitations anyway - so 2 might be plenty for version 1. In other words - nothing grand - just a small optimization that will kick in on simple stuff. Pick some low hanging fruit. A would be nice if not too complicated. On Wed, Aug 1, 2012 at 5:57 PM, Christian Smith csm...@thewrongchristian.org.uk wrote: On Sat, Jul 14, 2012 at 03:17:07PM +0100, Simon Slavin wrote: On 14 Jul 2012, at 3:12pm, Udi Karni uka...@gmail.com wrote: I know nothing about writing DB engines - so I don't know whether adding a 2nd parallel process adds 10K or 10M to the code base. You've reached the limit of what I know about parallelization. I hope someone else can chime in. Using SQLite's VM architecture, I would guess that adding this sort of parallelization would be non-trival. You need a parallel VM, significantly different to the current sequential VM, at at least a way of managing asynchronous IO, with perhaps a callback mechanism into the VM to handle IO completion. shudder While not certain, I guess other databases handle this by using tree based execution plans, where any single execution node can easily be split into branches to another thread/process/machine, then merged in the parent tree node, with each branch handling a certain key range. This might make sense, for example, with a partitioned table, where each partition is on it's own spindle, so a full table scan can be executed in parallel on each spindle and merged as a final step. So, for a table scan between k0 and k3, find intermediate keys to split the query between spindles: (k0-k3) /|\ / | \ / | \ / | \ /|\ (k0-k1] (k1-k2] (k2-k3) | | | disk1disk2disk3 I sat through an Oracle internals course once, and the instructor gave us an example of a setup such as this where data was partitioned across 24 disks, and the resulting full table scans were in fact quicker than index based scans for the data set they were using. Of course, the above would be useless for SQLite anyway, being a single file database. And even with the likes of Oracle, Stripe And Mirror Everything (SAME) might also largely defeat parallel scans. All in all, the added bloat would be measured in MB, rather than KB. Christian disclaimer: Not a practical DB implementation expert. ___ 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] EXT : Unknown module FTS4
You've got me totally confusedyou say shared library and dynamically linked but then say it's embedded in the GUI. Which is it? Are you on Unix/Linux? Can you show us your Makefile or an example build line? What are you compiling with? Have you duplicated your GUI build process on another program using all the same settings and succeeded? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Keith Medcalf [kmedc...@dessus.com] Sent: Monday, July 30, 2012 8:06 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] EXT : Unknown module FTS4 The other library that is getting loaded first is embedded in the GUI application. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Black, Michael (IS) Sent: Sunday, 29 July, 2012 06:22 To: General Discussion of SQLite Database Subject: Re: [sqlite] EXT : Unknown module FTS4 You probably have another shared library in your path that is getting loaded first. Since you said shared library and not DLL I assume you're using Unix of some sort? Run ldd on your GUI app and see what library it says it will use. Also, you should have either strace or truss which can show you which library is getting loaded when it runs. strace myapp myapp.log Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Navaneeth.K.N [navaneet...@gmail.com] Sent: Sunday, July 29, 2012 2:17 AM To: General Discussion of SQLite Database Subject: EXT :[sqlite] Unknown module FTS4 Hello, I have a weird problem. I am working on a shared library, written using C and a GUI application written on C++. GUI application uses the shared library. This shared library uses SQLite amalgamation and links statically. GUI also uses SQLite for some configuration purpose. It is also statically linked. Both of them uses latest SQLite version. My shared library uses FTS4. I have enabled FTS4 by providing the compile time options while compiling the shared library. All works well with the shared library. All my tests in the shared library codebase is passing. Problem happens when I start using this in the GUI program. I am getting error like, Unknown module FTS4. This is weird because I have it linked statically in my shared library and all this GUI program does is to dynamically link to my library. When I set the FTS compilation options to the GUI program, error goes away and all works well. I am not sure why this is happening. Any help would be great! -- -n ___ 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] EXT : Unknown module FTS4
You probably have another shared library in your path that is getting loaded first. Since you said shared library and not DLL I assume you're using Unix of some sort? Run ldd on your GUI app and see what library it says it will use. Also, you should have either strace or truss which can show you which library is getting loaded when it runs. strace myapp myapp.log Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Navaneeth.K.N [navaneet...@gmail.com] Sent: Sunday, July 29, 2012 2:17 AM To: General Discussion of SQLite Database Subject: EXT :[sqlite] Unknown module FTS4 Hello, I have a weird problem. I am working on a shared library, written using C and a GUI application written on C++. GUI application uses the shared library. This shared library uses SQLite amalgamation and links statically. GUI also uses SQLite for some configuration purpose. It is also statically linked. Both of them uses latest SQLite version. My shared library uses FTS4. I have enabled FTS4 by providing the compile time options while compiling the shared library. All works well with the shared library. All my tests in the shared library codebase is passing. Problem happens when I start using this in the GUI program. I am getting error like, Unknown module FTS4. This is weird because I have it linked statically in my shared library and all this GUI program does is to dynamically link to my library. When I set the FTS compilation options to the GUI program, error goes away and all works well. I am not sure why this is happening. Any help would be great! -- -n ___ 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] EXT : open database on Linux. Already db created on Mac.
You familiar with the sqlite3 shell? sqlite-shell here: http://www.sqlite.org/sqlite-shell-linux-x86-3071300.zip sqlite3 filename Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Durga D [durga.d...@gmail.com] Sent: Sunday, July 29, 2012 7:14 AM To: General Discussion of SQLite Database Subject: EXT :[sqlite] open database on Linux. Already db created on Mac. Hi All, What is the procedure to open the sqlite3 database file in Linux Terminal which is already created on Mac. Thanks in advance. Regards, ___ 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] C++ - Finalizing my SQLite interface
Or since in C++ use an unordered_map. Add your statement pointers to that, and
delete them from the map when finalized. Then walk through that map on
destruction to finalize all you haven't cleaned up yourself.
Given the way he's developing I would make a function to do this that
pre-checks that the statement doesn't already exist in the map to catch errors.
#include sstream
#include unordered_set
#include sqlite3.h
// Compiles with gcc 4.4
// g++ -std=c++0x -g -o map map.cpp
using namespace std;
class Statement {
public:
Statement() {};
~Statement();
bool add(sqlite3_stmt **stmt); // returns true if successful
bool remove(sqlite3_stmt **stmt); // returns true if succesful
string errmsg() {
return serrmsg.str();
};
void clear();
private:
stringstream serrmsg;
unordered_setsqlite3_stmt ** statements;
};
Statement::~Statement() {
clear();
}
void Statement::clear() {
for(unordered_setsqlite3_stmt **::iterator it = statements.begin();
it!=statements.end(); ++it) {
cout finalize *it endl;
statements.erase(*it);
}
}
bool Statement::add(sqlite3_stmt **stmt) {
serrmsg.str();
unordered_setsqlite3_stmt **::const_iterator got;
got = statements.find(stmt);
if ( got != statements.end()) {
serrmsg stmt already exists;
return false;
}
serrmsg OK;
statements.insert(stmt);
return true;
}
bool Statement::remove(sqlite3_stmt **stmt) {
serrmsg.str();
unordered_setsqlite3_stmt **::const_iterator got;
got = statements.find(stmt);
if ( got == statements.end()) {
serrmsg stmt does not exist;
return false;
}
serrmsg OK;
statements.erase(stmt);
return true;
}
int main() {
sqlite3_stmt *stmt1,*stmt2,*stmt3;
Statement st;
if (!st.add(stmt1)) { // works
cerr Error#1 putting stmt: st.errmsg() endl;
}
if (!st.add(stmt2)) { // works
cerr Error#1 putting stmt: st.errmsg() endl;
}
if (!st.add(stmt3)) { // works
cerr Error#1 putting stmt: st.errmsg() endl;
}
if (!st.add(stmt1)) { // gives error
cerr Error#2 putting stmt: st.errmsg() endl;
}
if (!st.remove(stmt1)) { // works
cerr Error#3 removing stmt: st.errmsg() endl;
}
if (!st.remove(stmt1)) { // gives error
cerr Error#4 removing stmt: st.errmsg() endl;
}
}
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Roger Binns [rog...@rogerbinns.com]
Sent: Friday, July 27, 2012 5:20 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] C++ - Finalizing my SQLite interface
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 27/07/12 07:22, Arbol One wrote:
Before calling the destructor, I would like to make sure that all the
sqlite3_stmt have been 'finalized', is there a function in SQLite that
that can help me do this, or should I just use 'NULL'?
Your best bet is to use reference counting. Each statement, backup etc
should add one to the database reference count, and subtract one when
finalized. Then only call the database destructor once its reference
count reaches zero.
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)
iEYEARECAAYFAlATFBAACgkQmOOfHg372QTdCQCfS6Y/E3G8lFcI5jDlYFY/l7XC
GkkAoMrxm+adE0WQNsb3kM7hSkWMbTc/
=Lf/f
-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] C++ - All the data in ONE row
In keeping with your example what you want to do is add a done flag to your
write class. So you tell it when your SQL can be executed.
Something like this:
void someClass::write2tblName() {
stmtName = INSERT INTO name (n_id, title, fname, mname, lname) VALUES
(?, ?, ?, ?, ?);
int data1 = 1;
Glib::ustring data2, data3, data4, data5;
data2 = Mr;
data3 = Dennis;
data4 = Father Of C And UNIX;
data5 = Ritchie;
int pos = 1;
try {
db-write(stmtName,pos, data1,0);
db-write(stmtName,++pos, data2,0);
db-write(stmtName,++pos,data3,0);
db-write(stmtName,++pos,data4,0);
db-write(stmtName,++pos,data5,1);
} catch(someException){.}
}
void mySQLite3Class::write(const Glib::ustring sql_stmt, int pos, int data,
int done)
)
throw(someException) {
if (pos == 1) { // prepare statement on 1st field
rc = sqlite3_prepare_v2(db, sql_stmt.c_str(), -1, stmt, NULL);
if(rc != SQLITE_OK) { throw(someException)}
}
rc = sqlite3_bind_int(stmt, pos, data);
if(rc != SQLITE_OK) { throw(someException) }
if (!done) return; // still have more to do so return now
rc = sqlite3_step(stmt);
if(rc != SQLITE_DONE) { throw(someException) }
sqlite3_finalize(stmt);
}
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] read sql script file
You're going to get questions like why do you want to do this so you may as
well tell us now.
The usual way to do his is to execute the sql yourself using statement prepares
and step. It gives you a lot more control over error messages. Why don't you
want to do it this way?
The 2nd way commonly done is to simply call sqlite3.exe as a system call or a
pipe. Less control but easy to understand.
Trying to use the .read function by linking it in seems like a bad idea as
you note. You could've already had the first wo methods done while trying to
figure that one out.
Here's the 2 methods in an example (please, anybody, feel free to
criticizeno pride of authorship here at all).
Change popen and pclose to _popen, _pclose for Windows.
#include stdio.h
#include stdlib.h
#include iostream
#include fstream
#include sstream
#include sqlite3.h
using namespace std;
void dosql(sqlite3 *db,const char *sql)
{
sqlite3_stmt *stmt;
int rc=sqlite3_prepare(db,sql,-1,stmt,0);
if (rc != SQLITE_OK) {
cerr sqlite3_prepare: sqlite3_errmsg(db) endl;
return;
}
rc=sqlite3_step(stmt);
if (rc == SQLITE_ROW) {
cerr multi row sql not implemented: sql endl;
sqlite3_finalize(stmt);
return;
}
if (rc != SQLITE_DONE) {
cerr sqlite3_step: sqlite3_errmsg(db) endl;
}
sqlite3_finalize(stmt);
}
void readfile(char *database,char *sqlfile) {
sqlite3 *db;
int rc = sqlite3_open(database,db);
if (rc != SQLITE_OK) {
cerr sqlite3_errmsg(db) endl;
exit(1);
}
ifstream sql;
sql.open(sqlfile);
if (!sql.is_open()) {
perror(sqlfile);
exit(1);
}
string line;
while(sql.good()) {
getline(sql,line);
if (!sql.eof()) {
cerr X: line endl;
dosql(db,line.c_str());
}
}
sql.close();
sqlite3_close(db);
}
void sqlite3_readfile(char *database,char *sqlfile) {
stringstream ss;
ss sqlite3 database sqlfile;
FILE *fp=popen(ss.str().c_str(),r);
if (fp == NULL) {
perror(sqlite3);
exit(1);
}
char buf[65536];
while(fgets(buf,sizeof(buf),fp)) {
cout buf;
}
pclose(fp);
}
int main(int argc, char *argv[]) {
if (argc != 3) {
cerr USage: argv[0] database filename endl;
exit(1);
}
#if 0
sqlite3_readfile(argv[1],argv[2]);
#else
readfile(argv[1],argv[2]);
#endif
return 0;
}
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of YAN HONG YE [yanhong...@mpsa.com]
Sent: Tuesday, July 24, 2012 5:16 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] read sql script file
in the shell.c source file ,have a function .read file, and I wanna use it to
my c++ code, when I hava a sql script file,such as :
create table test (id integer primary key, value text);
insert into test (id, value) values(1, 'eenie');
insert into test (id, value) values(2, 'meenie');
insert into test (value) values('miny');
insert into test (value) values('mo');
now I wanna use the shell.c function to run the script, but I don't know how to
use c++ code to achieve the target.
I only found on line in shell.c:
.read FILENAME Execute SQL in FILENAME\n in
static char zHelp[]
I suggest it use callback.
static int _is_complete(char *zSql, int nSql){
int rc;
if( zSql==0 ) return 1;
zSql[nSql] = ';';
zSql[nSql+1] = 0;
rc = sqlite3_complete(zSql);
zSql[nSql] = 0;
return rc;
}
___
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] EXT : C++ - sqlite3_extended_result_codes(
Should be this: on = true = !0 = 1 (other !=0 values also work typically) off = false = 0 Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Arbol One [arbol...@gmail.com] Sent: Tuesday, July 24, 2012 3:13 PM To: SqLite Subject: EXT :[sqlite] C++ - sqlite3_extended_result_codes( I would like to turn on the extended result codes, however, the prototype below does not explain what the value for the second parameter should be. Can anybody help? int sqlite3_extended_result_codes(sqlite3*, int onoff); TIA ___ 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 Shell Bug, Ignores Separators in Quotes Sometimes When Importing Data
Hmmmyour data import works just fine in 3.7.9...but you're correct that 3.7.13 burps with that error message doing the same import. So something changed C:\sqlitesqlite3 data.db SQLite version 3.7.9 2011-11-01 00:52:41 Enter .help for instructions Enter SQL statements terminated with a ; sqlite .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; COMMIT; sqlite CREATE TABLE CONFIGURATION ... ( ... RECORD_IDNUMERIC NOT NULL, ... TEXT TEXT, ... NUMERIC_DATA NUMERIC ... ); sqlite sqlite .import data.txt configuration sqlite .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE CONFIGURATION ( RECORD_IDNUMERIC NOT NULL, TEXT TEXT, NUMERIC_DATA NUMERIC ); INSERT INTO CONFIGURATION VALUES(1,'TEXT LINE 1 (72)','43721S'); INSERT INTO CONFIGURATION VALUES(2,'TEXT LINE 2 (72)','43721S'); INSERT INTO CONFIGURATION VALUES(3,'TEXT LINE 3 (72)','43721S'); INSERT INTO CONFIGURATION VALUES(4,'TEXT LINE 4 (72)','43721S'); INSERT INTO CONFIGURATION VALUES(5,'TEXT LINE 5 (72)','43721S'); INSERT INTO CONFIGURATION VALUES(6,'TEXT LINE 6 (72)','43721S'); INSERT INTO CONFIGURATION VALUES(7,'TEXT LINE 7 (72)','43721S'); INSERT INTO CONFIGURATION VALUES(8,'TEXT LINE 8 (72)','43721S'); INSERT INTO CONFIGURATION VALUES(9,'TEXT LINE 9 (72)','43721S'); COMMIT; Works in 3.7.10 too. Reuse the db file for 3.7.13 C:\sqlitesqlite3 data.db SQLite version 3.7.13 2012-06-11 02:05:22 Enter .help for instructions Enter SQL statements terminated with a ; sqlite .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE CONFIGURATION ( RECORD_IDNUMERIC NOT NULL, TEXT TEXT, NUMERIC_DATA NUMERIC ); INSERT INTO CONFIGURATION VALUES(1,'TEXT LINE 1 (72)','43721S'); INSERT INTO CONFIGURATION VALUES(2,'TEXT LINE 2 (72)','43721S'); INSERT INTO CONFIGURATION VALUES(3,'TEXT LINE 3 (72)','43721S'); INSERT INTO CONFIGURATION VALUES(4,'TEXT LINE 4 (72)','43721S'); INSERT INTO CONFIGURATION VALUES(5,'TEXT LINE 5 (72)','43721S'); INSERT INTO CONFIGURATION VALUES(6,'TEXT LINE 6 (72)','43721S'); INSERT INTO CONFIGURATION VALUES(7,'TEXT LINE 7 (72)','43721S'); INSERT INTO CONFIGURATION VALUES(8,'TEXT LINE 8 (72)','43721S'); INSERT INTO CONFIGURATION VALUES(9,'TEXT LINE 9 (72)','43721S'); COMMIT; sqlite .import data.txt configuration Error: data.txt line 10: expected 3 columns of data but found 2 sqlite .separator | sqlite .import data.txt configuration Error: data.txt line 10: expected 3 columns of data but found 2 Even putting single quotes around field 2 3 gives the same error. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Hayes, Michael - IS [michael.ha...@exelisinc.com] Sent: Monday, July 23, 2012 7:37 AM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] SQLite Shell Bug, Ignores Separators in Quotes Sometimes When Importing Data I've got input data that uses double quotes to mean inches. So I have records with a single double quote character in a record. In some cases, SQLite is ignoring separators that are after the quotes. The documentation says that the separator will be honored even inside of quotes. (The SQLite shell will always split fields on the separator character, no matter what comes before or after it. Quotes or backslashes won't escape them.). However, the SQLite shell seems to be behaving differently when there is a single quote in the record. I'm using sqlite-shell-win32-x86-3071300.zip and sqlite-dll-win32-x64-3071300.zip on Windows XP. To reproduce, create this table and import the attached data file: CREATE TABLE CONFIGURATION ( RECORD_IDNUMERIC NOT NULL, TEXT TEXT, NUMERIC_DATA NUMERIC ); If there are an odd number of quotes in the file, the error message is Error: Separator_Ignored_Inside_Quotes.txt line 10: expected 3 columns of data but found 2. If there are an even number of quotes in the file, every other record is imported and the data within the quotes is imported into the column including separators. Edit the attached file to remove the last line and you'll see this behavior. Thanks for looking it this bug and for SQLite. Mike Hayes Exelis Inc., Bowie, MD. Email addresses of ITT Exelis employees have changed from itt.com to exelisinc.com. Please update your favorites and contact information to reflect these changes. This e-mail and any files transmitted with it may be proprietary and are intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error please notify the sender. Please note that any views or opinions presented in
Re: [sqlite] SQLite Shell Bug, Ignores Separators in Quotes Sometimes When Importing Data
Nope -- that doesn't work. Seems to me if the import is going to assume the field is text it should also recognize that if it doesn't start with a quote it shouldn't assume that all quotes are delimiters. Plus, it should recognize that any quotes that aren't at the beginning or end-of-field aren't delimiters either. Only quotes at both ends of the fields may be removed. So these should all work when quotes are NOT the separator but the pipe symbol is: 1|this is a test|1 -- quotes removed field inserted 2|'this is a test'|2 -- single quotes removed and field inserted 3|'thisisatest'|3 -- singled quotes removed but double quotes remain. Or is there some standard that we ought to be following? C:\sqlitemore data2.txt 1|TEXT LINE 1 (72)'|43721 2|TEXT LINE 2 (72)'|43721 3|TEXT LINE 3 (72)'|43721 4|TEXT LINE 4 (72)'|43721 5|TEXT LINE 5 (72)'|43721 6|TEXT LINE 6 (72)'|43721 7|TEXT LINE 7 (72)'|43721 8|TEXT LINE 8 (72)'|43721 9|TEXT LINE 9 (72)'|43721 C:\sqlitesqlite3 data.db SQLite version 3.7.13 2012-06-11 02:05:22 Enter .help for instructions Enter SQL statements terminated with a ; sqlite .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE CONFIGURATION ( RECORD_IDNUMERIC NOT NULL, TEXT TEXT, NUMERIC_DATA NUMERIC ); COMMIT; sqlite .import data2.txt configuration sqlite .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE CONFIGURATION ( RECORD_IDNUMERIC NOT NULL, TEXT TEXT, NUMERIC_DATA NUMERIC ); INSERT INTO CONFIGURATION VALUES(1,'TEXT LINE 1 (72)',43721); INSERT INTO CONFIGURATION VALUES(2,'TEXT LINE 2 (72)',43721); INSERT INTO CONFIGURATION VALUES(3,'TEXT LINE 3 (72)',43721); INSERT INTO CONFIGURATION VALUES(4,'TEXT LINE 4 (72)',43721); INSERT INTO CONFIGURATION VALUES(5,'TEXT LINE 5 (72)',43721); INSERT INTO CONFIGURATION VALUES(6,'TEXT LINE 6 (72)',43721); INSERT INTO CONFIGURATION VALUES(7,'TEXT LINE 7 (72)',43721); INSERT INTO CONFIGURATION VALUES(8,'TEXT LINE 8 (72)',43721); INSERT INTO CONFIGURATION VALUES(9,'TEXT LINE 9 (72)',43721); COMMIT; Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Richard Hipp [d...@sqlite.org] Sent: Monday, July 23, 2012 1:40 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] SQLite Shell Bug, Ignores Separators in Quotes Sometimes When Importing Data On Mon, Jul 23, 2012 at 2:28 PM, Kevin Benson kevin.m.ben...@gmail.comwrote: On Mon, Jul 23, 2012 at 12:05 PM, Richard Hipp d...@sqlite.org wrote: On Mon, Jul 23, 2012 at 8:37 AM, Hayes, Michael - IS michael.ha...@exelisinc.com wrote: The documentation says that the separator will be honored even inside of quotes. (The SQLite shell will always split fields on the separator character, no matter what comes before or after it. Quotes or backslashes won't escape them.). I'm not able to find this statement anywhere in the SQLite documentation. Can you send a link? -- He's quoted from the wiki: http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles Yeah. That wiki is really old. Don't believe it The CVS import for the command-line shell treats as a quoting characters. All content between ... is considered to be part of a single field of the CVS, even if that content includes newline characters. I think it will work to escape your isolated characters by replacing them with four double-quotes in a row: -- -- -- --Ô¿Ô-- K e V i N ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C++ - Creating Table
Just as a sanity check your code does work OK. I made it a standalone program.
#include iostream
#include sqlite3.h
using namespace std;
class mySQLite3Class {
private:
//SQLite3
sqlite3* db; //SQLite3
string dbName; // Database name
string apstr; // All Purpose String
string sql_param_tblName; // Databese table Name parameters
string stmtName; // SQL statement name
public:
void createDB();
void create_tblName();
void createDatabase(const string s);
void createTable(const string s);
mySQLite3Class(const string s) {
createDatabase(s);
}
};
void mySQLite3Class::createDatabase(const string s) {
int rc = sqlite3_open_v2(s.c_str(),
db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
NULL);
if(rc != SQLITE_OK) {
std::cout rc std::endl;
}
}
void mySQLite3Class::createTable(const string s) {
sqlite3_stmt *stmt;
int rc = sqlite3_prepare_v2(db, s.c_str(), s.length(), stmt, NULL );
if(rc != SQLITE_OK) {
std::cout rc std::endl;// error = 1
std::cout sqlite3_errmsg(db)
std::endl; // er-msg = library routine called out of sequence
}
rc = sqlite3_step(stmt);
if(rc != SQLITE_DONE) {
std::cout rc endl;
}
sqlite3_finalize(stmt);
}
int main(int argc,char *argv[])
{
string dbName = 001Database.sql;
string sql_param_tblName = CREATE TABLE name(n_id INTEGER PRIMARY KEY, title
TEXT, fname TEXT, mname TEXT, lname TEXT);
mySQLite3Class *myDB = new mySQLite3Class(dbName);
myDB-createTable(sql_param_tblName);
return 0;
}
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Arbol One [arbol...@gmail.com]
Sent: Monday, July 23, 2012 1:54 PM
To: SqLite
Subject: EXT :[sqlite] C++ - Creating Table
Using SQLite version 3.7.8 amalgamation, under Win7 with MinGW, I compile
the bellow program, but for some strange reason I am getting a runtime error
when creating the table. I hope that one of you would be able to tell me
what I am doing wrong.
TIA
===
class mySQLite3Class {
private:
//SQLite3
sqlite3* db; //SQLite3
Glib::ustring dbName; // Database name
Glib::ustring apstr; // All Purpose String
Glib::ustring sql_param_tblName; // Databese table Name parameters
Glib::ustring stmtName; // SQL statement name
public:
void createDB();
void create_tblName();
mySQLite3Class(const Glib::ustring s){ createDatabase(s);}
};
void mySQLite3Class::createDatabase(const Glib::ustring s) {
rc = sqlite3_open_v2(s.c_str(),
db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
NULL);
if(rc != SQLITE_OK) {
std::cout rc std::endl;
}
}
void mySQLite3Class::createTable(const Glib::ustring s){
rc = sqlite3_prepare_v2(db, s.c_str(), s.length(), stmt, NULL );
if(rc != SQLITE_OK) {
std::cout rc std::endl;// error = 1
std::cout sqlite3_errmsg(db)
std::endl; // er-msg = library routine called out
of sequence
}
rc = sqlite3_step(stmt);
if(rc != SQLITE_DONE) {
std::cout rc stdl;
}
sqlite3_finalize(stmt);
}
myClass{
private:
mySQLite3Class* myDB;
Glib::ustring sql_param_tblName;
Glib::ustring dbName;
public:
myClass();
}
myClass::myClass(){
dbName = 001Database.sql;
sql_param_tblName = CREATE TABLE name(n_id INTEGER PRIMARY KEY, title
TEXT, fname TEXT, mname TEXT, lname TEXT);
myDB = new mySQLite3Class(dbName);
myDB-createTable(sql_param_tblName); == // problem
}
___
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 AND table already exist?
I needed a quick excercise this morning. Never used table_info() before.
table_info() will be faster than doing select * I would think in most all
cases.
#include iostream
#include sstream
#include sstream
#include sqlite3.h
using namespace std;
bool dbExists(string dbName) {
sqlite3 *db;
int rc = sqlite3_open_v2(dbName.c_str(), db, SQLITE_OPEN_READONLY, NULL);
if(rc != SQLITE_OK) {
return false;
}
rc = sqlite3_close(db);
if(rc != SQLITE_OK) {
cerr Error on sqlite3_close?? endl;
}
return true;
}
bool tableExists(string dbName, string table) {
sqlite3 *db;
int rc = sqlite3_open_v2(dbName.c_str(), db, SQLITE_OPEN_READONLY, NULL);
if(rc != SQLITE_OK) {
return false; // db doesn't exist
}
sqlite3_stmt *stmt;
stringstream ss;
ss pragma table_info( table );;
rc = sqlite3_prepare_v2( db, ss.str().c_str() , -1, stmt, NULL );
if(rc != SQLITE_OK) {
cerr Error on sqlite3_prepare_v2: sqlite3_errmsg(db) endl;
}
rc = sqlite3_step(stmt);
if(rc != SQLITE_DONE rc != SQLITE_ROW) {
cerr Error on sqlite3_step: sqlite3_errmsg(db) endl;
}
bool myReturn = false;
if (sqlite3_data_count(stmt) 0) {
myReturn = true;
}
sqlite3_finalize(stmt);
sqlite3_close(db);
return myReturn;
}
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Arbol One [arbol...@gmail.com]
Sent: Friday, July 20, 2012 5:51 AM
To: SqLite
Subject: EXT :[sqlite] database AND table already exist?
Is there a way to find out if a certain database AND table already exist?
In my C++ program I would like to query SQLite3 to find out if a database
already exists and also if a particular table exists in that database. Is
there a way to do this?
I am using std i/o methods to check for the existing SQLite3 file containing
the database, but I don't have a way to find out if the table in question
does in fact exist.
TIA
Freedom of speech does not translate to freedom of insulting
___
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 AND table already exist?
Good pointso probably time equivalent either way. Though table_info will allow the feature creep of does a column exist pretty easily. Not that anybody ever adds requirements Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [itandet...@mvps.org] Sent: Friday, July 20, 2012 7:59 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] database AND table already exist? Black, Michael (IS) michael.bla...@ngc.com wrote: table_info() will be faster than doing select * I would think in most all cases. To check the existence of a table, you don't need to actually run the select statement - just prepare it and check for errors. -- 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
