Re: [sqlite] Simple SQLite-based spreadsheet?

[Black, Michael (IS)]

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
 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

[Black, Michael (IS)]

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  wrote:

> Is there anyway to recover?
>
>
> On Thu, Dec 6, 2012 at 10:23 PM, Richard Hipp  wrote:
>
>> On Thu, Dec 6, 2012 at 11:52 AM, Durga D  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

[Black, Michael (IS)]

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 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,
> [pid  4015] 21:01:53.634245 write(98,
> "\0\0\0\4\0\0HU^=\226\213\23\10<\247+\214\332\260\314Wf ", 24
> 
> [pid  4015] 21:01:53.634546 _llseek(98,

Re: [sqlite] Tracing latencies

[Black, Michael (IS)]

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 
#include 

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  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,  
[pid  4015] 21:01:53.634245 write(98,
"\0\0\0\4\0\0HU^=\226\213\23\10<\247+\214\332\260\314Wf ", 24

[pid  4015] 21:01:53.634546 _llseek(98, 499952,  
[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 
[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 
[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

[Black, Michael (IS)]

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  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,  
[pid  4015] 21:01:53.634245 write(98,
"\0\0\0\4\0\0HU^=\226\213\23\10<\247+\214\332\260\314Wf ", 24

[pid  4015] 21:01:53.634546 _llseek(98, 499952,  
[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 
[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 
[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

[Black, Michael (IS)]

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  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] Tracing latencies

[Black, Michael (IS)]

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  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] Windows (slow) vs. iOS/OSX (fast) Performance

[Black, Michael (IS)]

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] Windows (slow) vs. iOS/OSX (fast) Performance

[Black, Michael (IS)]

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] Database design preferences

[Black, Michael (IS)]

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] Sqlite 3.4.1 write performance difference between linux kernel 2.6.33 and 3.4.6

[Black, Michael (IS)]

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", );
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] Converting in-memory sqlite database to char array

[Black, Michael (IS)]

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] Converting in-memory sqlite database to char array

[Black, Michael (IS)]

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  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] Replace on fts4 table results in unexpected matchinfo result

[Black, Michael (IS)]

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 earlyentries in a DB

[Black, Michael (IS)]

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 Informa

Re: [sqlite] Anomalously slow performance on updates to earlyentries in a DB

[Black, Michael (IS)]

>[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 early entries in a DB

[Black, Michael (IS)]

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?

Re: [sqlite] EXT :Re: Compiling SQLite3 with MSVC 2010

[Black, Michael (IS)]

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

Re: [sqlite] EXT :Re: Compiling SQLite3 with MSVC 2010

[Black, Michael (IS)]

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.
>
&

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Black, Michael (IS)]

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] Compiling SQLite3 with MSVC 2010

[Black, Michael (IS)]

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  wrote:
> On Fri, Nov 2, 2012 at 7:05 PM, Igor Korot  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')"

[Black, Michael (IS)]

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 :

>
On 1 Nov 2012, at 7:55pm, Григорий Григоренко  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
>
>

>
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

[Black, Michael (IS)]

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  , 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 

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

[Black, Michael (IS)]

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 id>7" > n.html



#include 
#include 
#include 
#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,);
  checkrc(rc,SQLITE_OK,db);
  rc = sqlite3_prepare_v2(db,sql,strlen(sql),,NULL);
  checkrc(rc,SQLITE_OK,db);
  printf("http://www.w3.org/TR/REC-html40/strict.dtd\;>\n");
  printf("\n\nParts List");
  printf("\n");
  printf("\n");
  printf("IDPart#Picture\n");
  while((rc=sqlite3_step(stmt))==SQLITE_ROW) {
int id=sqlite3_column_int(stmt,0);
printf("\n%d\n",id);
char *partnumber = sqlite3_column_text(stmt,1);
printf("%s\n",partnumber);
char *pic = sqlite3_column_text(stmt,2);
printf("\n",pic,pic);
printf("\n");
  }
  checkrc(rc,SQLITE_DONE,db);
  rc=sqlite3_finalize(stmt);
  checkrc(rc,SQLITE_OK,db);
  printf("\n\n");
  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(\'\',1,180) as 
img,pcs from engine where id>7;\" >> n.html");
system(bh1);  //here couldn't work

error:
sqlite3 -html -header t9_engine.db "select id,partnumber,substr('',1,180) as img,pcs from engine where id>7;" >> n.htmlError: n
ear "'\',1,180) as 
img,pcs from engine where id>7;\" >> n.html");
strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select id,partnumber,'' as img,pcs from engine where id>7;\" >> n.html");
system(bh1);  //here could work
the result is:
8
AA34841687 000 INSONO-SOUS-MOTEUR--
img src=C:\t9\images\INSONO-SOUS-MOTEUR.jpg height=220/   
//here I wanna add " char between  'C:\t9\images\INSONO-SOUS-MOTEUR.jpg'
1


and the best way is change
to  <
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] I/O error on creating index with 3.7.14

[Black, Michael (IS)]

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

[Black, Michael (IS)]

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 
#include 
#include 

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,'' from engine where 
id>7;\" >> n.html";
FILE *fp;
char buf[65535];
char *sqlcmd="sqlite3 -html -header t9_engine.db \"select 
id,partnumber,'' from engine where 
id>7;\" >> 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,"","<");
if (s) {strcpy(buf,s);free(s);}
s=str_replace(buf,"","<");
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  wrote:

>char bh1[320];
>memset(bh1,0,320);
>strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
>id,partnumber,substr(\'\',1,180) as 
>img,pcs from engine where id>7;\" >> n.html");
>system(bh1);  //here couldn't work
>
>error:
>sqlite3 -html -header t9_engine.db "select id,partnumber,substr('src="'||pi
>c||'" height=220/>',1,180) as img,pcs from engine where id>7;" >> n.htmlError: 
>n
>ear "'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(\'\',1,180) as 
>img,pcs from engine where id>7;\" >> n.html");
>strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select id,partnumber,'src='||pic||' height=220/>' as img,pcs from engine where id>7;\" >> n.html");
>system(bh1);  //here could work
>the result is:
>8
>AA34841687 000 INSONO-SOUS-MOTEUR--
>img src=C:\t9\images\INSONO-SOUS-MOTEUR.jpg height=220/   
>//here I wanna add " char between  'C:\t9\images\INSONO-SOUS-MOTEUR.jpg'
>1
>
>
>and the best way is change
>   to  <
>   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 mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to select " char in sqlite

[Black, Michael (IS)]

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:\SQLite>sqlite3 -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:
1
11


2
22


3
33




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 id>7;" >> n.html
here   \"abc.jpg\" couldn't work.

sqlite3 -html -header t9_engine.db "select id,partnumber,'' as img,pcs from engine where id>7; ">> n.html
Same problem.

strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
id,partnumber,substr(\'\',1,180) as 
img,pcs from engine where id>7;\" >> 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

[Black, Michael (IS)]

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 
#include "sqlite3.h"

int main(int argc, char *argv[])
{
sqlite3 *db;
int rc;
rc=sqlite3_open("test.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 
> #include "sqlite3.h"
>
> int main()
> {
> sqlite3 *db;
> int rc;
> rc=sqlite3_open("test.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-us

Re: [sqlite] Getting Error SQLITE_NOTADB

[Black, Michael (IS)]

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 
#include "sqlite3.h"

int main()
{
sqlite3 *db;
int rc;
rc=sqlite3_open("test.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] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22

[Black, Michael (IS)]

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?

Re: [sqlite] Find first non-NULL values of several columns

[Black, Michael (IS)]

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.a 
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

[Black, Michael (IS)]

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  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] EXT :Re: CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22

[Black, Michael (IS)]

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] EXT :Re: CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22

[Black, Michael (IS)]

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] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22

[Black, Michael (IS)]

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] SQLite flush on disk to calc db file hash,how?

[Black, Michael (IS)]

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] find sequential groups

[Black, Michael (IS)]

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  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 : find sequential groups

[Black, Michael (IS)]

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] 5. Re: Sqlite, Is it possible to calculate the length of the longest increasing subsequence using an UDF

[Black, Michael (IS)]

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] 5. Re: Sqlite, Is it possible to calculate the length of the longest increasing subsequence using an UDF

[Black, Michael (IS)]

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] EXT :Re: System.Data.SQLite Field Name are surrounded by double quotes for Views

[Black, Michael (IS)]

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

[Black, Michael (IS)]

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)

[Black, Michael (IS)]

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  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
>
> 
>
> 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

[Black, Michael (IS)]

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.com<javascript:;>
> >
> 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.org<javascript:;>
> >
> > 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

Re: [sqlite] Subtract times hh:mm:ss

[Black, Michael (IS)]

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

[Black, Michael (IS)]

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  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] Seemingly random Access Violation errors

[Black, Michael (IS)]

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] Problem with Foreign Key constraints

[Black, Michael (IS)]

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] Issue with SQLite3 for WinRT ARM

[Black, Michael (IS)]

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] Sending SQLite3 .timeout command in Command-Line Mode

[Black, Michael (IS)]

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

Re: [sqlite] EXT :Re: Sending SQLite3 .timeout command in Command-Line Mode

[Black, Michael (IS)]

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
> > >
> > > 

Re: [sqlite] EXT :Re: Sending SQLite3 .timeout command in Command-Line Mode

[Black, Michael (IS)]

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.
> > > Occas

Re: [sqlite] Sending SQLite3 .timeout command in Command-Line Mode

[Black, Michael (IS)]

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] Sending SQLite3 .timeout command in Command-Line Mode

[Black, Michael (IS)]

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

[Black, Michael (IS)]

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] :Re: DELETE Query Assistance Please

[Black, Michael (IS)]

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

[Black, Michael (IS)]

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 wrote:

> >Install Cygwin, use that to make the amalgamation (after you've made
> >whatever changes to the canonical sources), then build the amalgation
> >in native Windows.


Please excuse my ignorance.
OK.  I have Cygwin.  Fossil.  I have cloned the repository and opened.   I
believe I now have the source tree of SQLite.
Not sure what I need to do next.
I have the Cygwin command prompt open and believe it is running the bash
shell.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
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

[Black, Michael (IS)]

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)
> >

Re: [sqlite] Store error messages in thread local memory

[Black, Michael (IS)]

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.)
&

Re: [sqlite] Store error messages in thread local memory

[Black, Michael (IS)]

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
> > > >
> > >
> > >
> > >
> > >
> > >

Re: [sqlite] Store error messages in thread local memory

[Black, Michael (IS)]

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] sqliteman vs sqlite3 tcl package

[Black, Michael (IS)]

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 
here
 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

[Black, Michael (IS)]

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.com>wrote:

> 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] Count(*) help

[Black, Michael (IS)]

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

[Black, Michael (IS)]

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  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 
> 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] Getting query results as new records are added

[Black, Michael (IS)]

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?

[Black, Michael (IS)]

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, , 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, , 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, ,
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] : C# access to SQLite and Windows 8

[Black, Michael (IS)]

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] selecting real values

[Black, Michael (IS)]

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(, "%!.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(,);
}
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] selecting real values

[Black, Michael (IS)]

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] instr function or equivalent

[Black, Michael (IS)]

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] instr function or equivalent

[Black, Michael (IS)]

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] C++ - WHERE clause - 2nd update

[Black, Michael (IS)]

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,, 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] EXT : C++ - WHERE clause - update

[Black, Michael (IS)]

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, , 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] Read-only media

[Black, Michael (IS)]

'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] C++ - WHERE clause

[Black, Michael (IS)]

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
Attackfor 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] C++ - WHERE clause

[Black, Michael (IS)]

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  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, ,
> 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] why no such column in sqlite3 ?

[Black, Michael (IS)]

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

[Black, Michael (IS)]

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
> &

Re: [sqlite] Pragma Synchronous=OFF is not working

[Black, Michael (IS)]

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/sql

Re: [sqlite] Pragma Synchronous=OFF is not working

[Black, Michael (IS)]

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

[Black, Michael (IS)]

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

[Black, Michael (IS)]

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  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 
> 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
> >>  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, );
> >>> sqlite3_exec(mDb, “PRAGMA count_changes=OFF”, NULL, NULL,
> >> );
> >>> sqlite3_exec(mDb, “PRAGMA journal_mode=MEMORY”, NULL, NULL,
> >> );
> >>> sqlite3_exec(mDb, “PRAGMA temp_store=MEMORY”, NULL, NULL,
> >> );
> >>>
> >>>
> >>> 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] EXT :Re: Multi-Thread Reads to SQLite Database

[Black, Michael (IS)]

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  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] C# Dynamic data type

[Black, Michael (IS)]

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 

enum {UNKNOWN, FLOAT, INT, STRING};

int datatype(char *s)
{
  long i;
  double f;
  char buf[4096];
  int n;
  n = sscanf(s,"%d%s",,buf);
  if (n == 1) {
printf("INT\n");
return INT;
  }
  n = sscanf(s,"%lg%s",,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] Suggestions for approximate date

[Black, Michael (IS)]

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] sqlite3 database unreadable on Mountain Lion

[Black, Michael (IS)]

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  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)

[Black, Michael (IS)]

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  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. 
>
> 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

[Black, Michael (IS)]

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 : open database on Linux. Already db created on Mac.

[Black, Michael (IS)]

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] EXT : Unknown module FTS4

[Black, Michael (IS)]

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] C++ - Finalizing my SQLite interface

[Black, Michael (IS)]

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 
#include 
#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_set statements;
};

Statement::~Statement() {
  clear();
}   
  
void Statement::clear() {
  for(unordered_set::iterator it = statements.begin(); 
it!=statements.end(); ++it) {
cout << "finalize " << *it << endl;
statements.erase(*it);
  }
} 

bool Statement::add(sqlite3_stmt **stmt) {
  serrmsg.str("");
  unordered_set::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_set::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()) { // works
cerr << "Error#1 putting stmt: " << st.errmsg() << endl;
  }
  if (!st.add()) { // works
cerr << "Error#1 putting stmt: " << st.errmsg() << endl;
  }
  if (!st.add()) { // works
cerr << "Error#1 putting stmt: " << st.errmsg() << endl;
  }
  if (!st.add()) { // gives error
cerr << "Error#2 putting stmt: " << st.errmsg() << endl;
  }
  if (!st.remove()) { // works
cerr << "Error#3 removing stmt: " << st.errmsg() << endl;
  }
  if (!st.remove()) { // 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

[Black, Michael (IS)]

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, , 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] EXT : C++ - sqlite3_extended_result_codes(

[Black, Michael (IS)]

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] read sql script file

[Black, Michael (IS)]

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 
#include 
#include 
#include 
#include 
#include 
using namespace std;
void dosql(sqlite3 *db,const char *sql)
{
  sqlite3_stmt *stmt;
  int rc=sqlite3_prepare(db,sql,-1,,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,);
  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] C++ - Creating Table

[Black, Michael (IS)]

Just as a sanity check your code does work OK.  I made it a standalone program.

#include 
#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(),
   , 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(), , 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(),

 , 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(), , 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] SQLite Shell Bug, Ignores Separators in Quotes Sometimes When Importing Data

[Black, Michael (IS)]

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|'this"is"a"test'|3 -- singled quotes removed but double quotes remain.



Or is there some standard that we ought to be following?



C:\sqlite>more 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:\sqlite>sqlite3 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 wrote:

> On Mon, Jul 23, 2012 at 12:05 PM, Richard Hipp  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] SQLite Shell Bug, Ignores Separators in Quotes Sometimes When Importing Data

[Black, Michael (IS)]

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:\sqlite>sqlite3 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:\sqlite>sqlite3 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 

Re: [sqlite] database AND table already exist?

[Black, Michael (IS)]

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

Re: [sqlite] database AND table already exist?

[Black, Michael (IS)]

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 
#include 
#include 
#include "sqlite3.h"

using namespace std;

bool dbExists(string dbName) {
  sqlite3 *db;
  int rc = sqlite3_open_v2(dbName.c_str(), , 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(), , 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, , 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