[sqlite] Trigger slows down application start-up

2013-10-30 Thread Igor Korot
Hi, ALL, I am developing my application on Windows using MSVC 2010 32-bit and test on Win 7 64-bit. Recently I had to create a trigger (actually 2 triggers to populate data in 2 tables). After those triggers had been created the application start-up time significantly increased (debug version

Re: [sqlite] Trigger slows down application start-up

2013-10-30 Thread Clemens Ladisch
Igor Korot wrote: > After those triggers had been created the application start-up time > significantly increased. > > On start-up it opens connection to the database and queries the table > that is not part of the trigger. > > Any idea what to look for? Are you creating one connection, or do you

Re: [sqlite] Trigger slows down application start-up

2013-10-30 Thread Igor Korot
On Wed, Oct 30, 2013 at 12:36 AM, Clemens Ladisch wrote: > Igor Korot wrote: >> After those triggers had been created the application start-up time >> significantly increased. >> >> On start-up it opens connection to the database and queries the table >> that is not part of

[sqlite] Extra Space Required for Index ?

2013-10-30 Thread Raheel Gupta
Hi, I have the following Table : CREATE TABLE users ( uid INTEGER PRIMARY KEY AUTOINCREMENT, username VARCHAR(100) UNIQUE NOT NULL DEFAULT '', email VARCHAR(255) UNIQUE NOT NULL } I wanted to know if I create an INDEX for the column "email" what isg going to be the extra space the index will

Re: [sqlite] Extra Space Required for Index ?

2013-10-30 Thread Stephan Beal
On Wed, Oct 30, 2013 at 9:37 AM, Raheel Gupta wrote: > I tried to search for any docs on this but couldnt. > This table is going to have 1 Million records and I need to save space > here. > Any any all storage-related requirements, with the possible exception of the page

Re: [sqlite] Extra Space Required for Index ?

2013-10-30 Thread Clemens Ladisch
Raheel Gupta wrote: > CREATE TABLE users ( > uid INTEGER PRIMARY KEY AUTOINCREMENT, > username VARCHAR(100) UNIQUE NOT NULL DEFAULT '', > email VARCHAR(255) UNIQUE NOT NULL > > I wanted to know if I create an INDEX for the column "email" what isg going > to be the extra space the index will occupy

Re: [sqlite] Extra Space Required for Index ?

2013-10-30 Thread Simon Slavin
On 30 Oct 2013, at 8:37am, Raheel Gupta wrote: > email VARCHAR(255) UNIQUE NOT NULL > } > > I wanted to know if I create an INDEX for the column "email" what isg going > to be the extra space the index will occupy ? Two things: A) SQLite interprets "VARCHAR(255)" as

[sqlite] [ANN] ODB C++ ORM 2.3.0 released, adds schema evolution support

2013-10-30 Thread Boris Kolpackov
I am pleased to announce the release of ODB 2.3.0. ODB is an open source object-relational mapping (ORM) system for C++. It allows you to persist C++ objects to a relational database without having to deal with tables, columns, or SQL and without manually writing any of the mapping code. Major

Re: [sqlite] Performance measurement

2013-10-30 Thread Simon Slavin
On 30 Oct 2013, at 3:00am, RSmith wrote: > Secondly, A query that seemingly requires no sorting sometimes give quite a > large figure for the Sort-Ops, what is it about this that I am not > understanding? You may find that adding the information given by EXPLAIN QUERY

Re: [sqlite] Problem with SQLITE_BUSY

2013-10-30 Thread Simon Slavin
On 30 Oct 2013, at 2:32am, Normand Mongeau wrote: > Odd thing is that although I do have a 10 second timeout as soon as C goes > into a begin transaction A receives the SQLITE_BUSY error, in other words I > don't see any 10 second delay. > > I'll try increasing the

Re: [sqlite] compiling the non-AMALGAMATION on visual studio

2013-10-30 Thread David Clark
Joe I tried you nmake line on a fresh extract of the .zip file from the download page and got this:         1 file(s) copied. C:\TFS\Main\source\lib\ThirdParty\sqlite_dbg\sqlite-src-3080100>copy /Y .\src\ro wset.c tsrc         1 file(s) copied.

[sqlite] store image file as blob

2013-10-30 Thread d b
Hi, My sqlite database module has to store images (max. 100 KB) on client machine. 1. store images on file system and have reference in database 2. store image as blob in database. Which is the best way to store these images? Any suggestions are welcome. Best Regards, va.

Re: [sqlite] store image file as blob

2013-10-30 Thread Richard Hipp
On Wed, Oct 30, 2013 at 7:30 AM, d b wrote: > Hi, > > My sqlite database module has to store images (max. 100 KB) on client > machine. > > 1. store images on file system and have reference in database > 2. store image as blob in database. > > Which is the best way to

[sqlite] collation for german "Umlaute"

2013-10-30 Thread Ulrich Goebel
Hallo, for a SQLite db I would like to define a collation for german "Umlaute" (don't know the english word for that, sorry) and "Sonderzeichen" (äöü, ÄÖÜ, ß), so that: a=A=ä=Ä o=O=ö=Ö u=U=ü=Ü ß=s (or, better: ß=ss) I want to use such a collation even on columns which are indeces, so I

Re: [sqlite] Problem with SQLITE_BUSY

2013-10-30 Thread Normand Mongeau
Actually, I inspected carefully the code and to avoid any deadlocks and make the intentions very explicit, every time we're about to write we do: Begin immediate transaction Our updates Commit transaction Anytime we read we do Begin transaction read rollback transaction (there was a mix of

Re: [sqlite] collation for german "Umlaute"

2013-10-30 Thread Richard Hipp
On Wed, Oct 30, 2013 at 8:51 AM, Ulrich Goebel wrote: > Hallo, > > for a SQLite db I would like to define a collation for german "Umlaute" > (don't know the english word for that, sorry) and "Sonderzeichen" (äöü, > ÄÖÜ, ß), so that: > > a=A=ä=Ä > o=O=ö=Ö > u=U=ü=Ü > ß=s (or,

Re: [sqlite] store image file as blob

2013-10-30 Thread Teg
Hello d, I prefer them in the DB so, I can move them as a package. I have 30-40 GB DB's filled with image files. Performance is decent. I do keep the blobs in one table and meta-data in another. C Wednesday, October 30, 2013, 7:30:58 AM, you wrote: db> Hi, db> My sqlite database module has

Re: [sqlite] collation for german "Umlaute"

2013-10-30 Thread Igor Tandetnik
On 10/30/2013 8:51 AM, Ulrich Goebel wrote: I want to use such a collation even on columns which are indeces, so I would like to "connect" the collation to the column/index at the time creating the column/index, not only in the later SELECTs. And I suppose, that I really don't have to specify

Re: [sqlite] store image file as blob

2013-10-30 Thread Richard Hipp
On Wed, Oct 30, 2013 at 9:10 AM, Teg wrote: > I do keep the > blobs in one table and meta-data in another. > Good point, and worth repeating. If you store large BLOBs in your database, performance will be much better if you keep them in a separate table something like this:

Re: [sqlite] Problem with SQLITE_BUSY

2013-10-30 Thread Simon Slavin
On 30 Oct 2013, at 1:00pm, Normand Mongeau wrote: > Could this be related to this: in A, the database connection is created in > the main program, but is passed down to a dll that loads another dll that > uses the connection to do the writes. Maybe the dll should open

[sqlite] Unused partial index

2013-10-30 Thread Filip Navara
Hi, I tried to convert our custom tables and triggers into partial indexes. Unfortunately it seems that the query planner is unable to recognize identical terms. Could this be fixed or is there any workaround? Thanks, Filip Navara SQLite version 3.8.1 2013-10-17 12:57:35 Enter ".help" for

Re: [sqlite] Problem with SQLITE_BUSY

2013-10-30 Thread Normand Mongeau
Using the straight C API of SQLite. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: October-30-13 9:39 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Problem with SQLITE_BUSY On 30 Oct

Re: [sqlite] Unused partial index

2013-10-30 Thread Richard Hipp
On Wed, Oct 30, 2013 at 9:47 AM, Filip Navara wrote: > Hi, > > I tried to convert our custom tables and triggers into partial indexes. > Unfortunately it seems that the query planner is unable to recognize > identical terms. Could this be fixed or is there any workaround?

Re: [sqlite] Problem with SQLITE_BUSY

2013-10-30 Thread Normand Mongeau
Well finally found the problem: a forgotten sqlite3_finalize() call. Very disturbing, I'd expect leakage, not the results I was seeing. Normand -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Normand Mongeau Sent:

Re: [sqlite] Problem with SQLITE_BUSY

2013-10-30 Thread Simon Slavin
On 30 Oct 2013, at 4:03pm, Normand Mongeau wrote: > Well finally found the problem: a forgotten sqlite3_finalize() call. > > Very disturbing, I'd expect leakage, not the results I was seeing. SQLite has to keep the state of your SELECT available until you tell it

Re: [sqlite] Problem with SQLITE_BUSY

2013-10-30 Thread Normand Mongeau
Then IMO the documentation for either sqlite3_prepare_xx or SQLITE_BUSY should state this. It would have been helpful in my case, I was pulling my hair looking at the transaction model which was sound. -Original Message- From: sqlite-users-boun...@sqlite.org

Re: [sqlite] Problem with SQLITE_BUSY

2013-10-30 Thread Igor Tandetnik
The documentation for BEGIN TRANSACTION (http://www.sqlite.org/lang_transaction.html) does: An implicit transaction (a transaction that is started automatically, not a transaction started by BEGIN) is committed automatically when the last active statement finishes. A statement finishes when

Re: [sqlite] Problem with SQLITE_BUSY

2013-10-30 Thread Normand Mongeau
Yep OK. I stand corrected. BTW thanks for your help. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: October-30-13 12:16 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Problem with SQLITE_BUSY The

Re: [sqlite] Extra Space Required for Index ?

2013-10-30 Thread Raheel Gupta
>> B) By insisting on your email values being unique you have already required SQLite to make up an index for the column. SQLite needs that index so that when you do an INSERT it can quickly check to see that the value you use doesn't already exist. Agreed. I was just asking the general space

Re: [sqlite] Extra Space Required for Index ?

2013-10-30 Thread Simon Slavin
On 30 Oct 2013, at 6:32pm, Raheel Gupta wrote: > I wanted to create an INDEX (not unique) of the md5sum column. Would the > index also eat up 20 Bytes or more than that ? At least as much space as the total contents of all the md5sum values. Assuming you did a VACUUM

Re: [sqlite] compiling the non-AMALGAMATION on visual studio

2013-10-30 Thread Joe Mistachkin
David Clark wrote: > > NMAKE : fatal error U1045: spawn failed : No error > Stop. > >From my research, it looks like this error is caused by a 64-bit versus 32-bit issue. What version Windows are you running? Is it 32-bit or 64-bit? Which ActiveTcl distribution file did you install? -- Joe

Re: [sqlite] collation for german "Umlaute"

2013-10-30 Thread Keith Medcalf
Search the Internet for an SQLite extension called "unifuzz.c" and see if that does what you want in the way of character folding. I have a copy of the code on my other computer if you cannot find the original authors original code. I didn't write it, but it basically implements a NOCASE

Re: [sqlite] Trigger slows down application start-up

2013-10-30 Thread Igor Korot
Hi, Clemens et al, On Wed, Oct 30, 2013 at 1:11 AM, Igor Korot wrote: > Clemens, > > On Wed, Oct 30, 2013 at 12:47 AM, Igor Korot wrote: >> On Wed, Oct 30, 2013 at 12:36 AM, Clemens Ladisch wrote: >>> Igor Korot wrote: After