[sqlite] Sqlite inside MySQL as one of the pluggable "storage mechanisms" ?
I had a musing while reading: http://news.com.com/2100-7344_3-6063599.html?part=rss=6063599=news where it reminded me of one of MySQL's features: MySQL's database is built so that it can use a range of different storage mechanisms, tuned for different purposes, such as transactions or indexing large amounts of text. "Rather than have one perfect engine, it's better to have a pluggable architecture," Urlocker said. "The idea is you can mix and match within a single application because data will be used in different ways." Perhaps adding the appropriate bindings to MySQL to register sqlite as a storage mechanism would allow the use of Sqlite in a network environment with out bloating the core Sqlite code? Maybe the above makes no sense, but I find the notion appealing that Sqlite could remain as tight little sql engine but could use the "network server" of MySQL should ever such a thing be needed. Would that be possible?
Re: [sqlite] Reuse of TCL array causes memory leak
"Aldobino Braga" <[EMAIL PROTECTED]> wrote:
> I've run into an issue where the reuse of a TCL array (to hold query
> results) causes a catastrophic memory leak. The "eval" command is called
> with the same array name again and again. Each successive query / array
> population exponentially consumes more and more memory (until the system
> crashes).
>
> Since "eval" is responsible for the array creation, it seems to me that it
> should be responsible for calling "array unset" before it populates the
> data. When I call "array unset" myself, everything works as expected.
>
> Perhaps this is the expected behavior, however I was unable to find anything
> in the documentation regarding it.
> Would anyone care to comment?
>
A common idiom is to do this:
db eval {SELECT * FROM table} v break
After this statement runs, the array variable v contains
the content of the first row of the table. Sometimes you
see two or three SELECT statements in a row populating the
same array:
db eval {SELECT * FROM table1} v break
db eval {SELECT * FROM table2} v break
...
db eval {SELECT * FROM tableN} v break
All of the about (which is commonly used) would break if
SQLite tried to do unsets for you.
I think, therefore, that the current behavior is correct.
--
D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Rule of thumb for estimating index size
Rusty Conover <[EMAIL PROTECTED]> wrote: > Hi, > > Is there an easy way to get an estimate on the space used by an > existing index? If not what is a good guess on how to estimate the > size? > > My guess would be (assuming text fields are being indexed): > > [total length of all index keys] + [number of rows]*8 (for the offset > location) > > Is that close? I realize disregards all space used by page allocation > overhead. > The sqlite3_analyzer executable available on the website will tell you exactly how much space an index uses. This utility is not suitable for use at run-time, but you can use it to validate the accuracy of your estimate. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Getting free pages count
Alexey Belyaev wrote: > I mean freepages.c under "not official methods" :) > > I know. :) I'm hoping that someday it will be officially supported. If Dr. Hipp feels that it adds unnecessary bloat to the library, then maybe it can be "supported" but IFDEFd out by default, and those of us that want it can set the define and add it back in. I just feel that my code was a horrible hack. I had to copy many private structural definitions from other parts of the source code to make it work.
[sqlite] Reuse of TCL array causes memory leak
I've run into an issue where the reuse of a TCL array (to hold query results) causes a catastrophic memory leak. The "eval" command is called with the same array name again and again. Each successive query / array population exponentially consumes more and more memory (until the system crashes). Since "eval" is responsible for the array creation, it seems to me that it should be responsible for calling "array unset" before it populates the data. When I call "array unset" myself, everything works as expected. Perhaps this is the expected behavior, however I was unable to find anything in the documentation regarding it. Would anyone care to comment? Regards, Al Braga
[sqlite] Re: sqlite3_prepare with non-scalar variables?
Nicholas Bastin <[EMAIL PROTECTED]> wrote: Is there any way to effectively prepare the following statement: SELECT * FROM some_table WHERE id IN (?); where the contents of the variable are a vector? (1,3,5,9,34,57), etc. I've been building up these just using text and using exec, but it seems you'd want to have a sqlite3_bind_int64_vector or something similaram I missing that this is implemented somewhere? As far as I can tell, you can't directly do what you want. The closest you can get to it is as follows: create a temporary table create temp table list (id integer); Insert your values into it: insert into temp.list(id) values(?); Finally, reorganize your select statement to use this table: select * from some_table join temp.list on (some_table.id = temp.list.id); All statements used here can be prepared. Igor Tandetnik
[sqlite] sqlite3_prepare with non-scalar variables?
Is there any way to effectively prepare the following statement: SELECT * FROM some_table WHERE id IN (?); where the contents of the variable are a vector? (1,3,5,9,34,57), etc. I've been building up these just using text and using exec, but it seems you'd want to have a sqlite3_bind_int64_vector or something similaram I missing that this is implemented somewhere? -- Nick
Re: [sqlite] Re: SQL statements in progress error message
On 4/20/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > Nicholas Bastin <[EMAIL PROTECTED]> > wrote: > > When trying to commit a transaction, I'm getting the following error > > message from sqlite3_exec(): > > > > "cannot commit transaction - SQL statements in progress" > > > > Is there any way to find out what statements are in progress? Also, > > what could cause this? I can guarantee that sqlite3_step has returned > > SQLITE_DONE on all my statements before I get to this point. Is that > > not sufficient? > > No, it is not sufficient. You have to sqlite3_reset or sqlite3_finalize > all statements in progress before you can commit. Ok, thanks. I'll make sure to reset all statements before trying to commit the transaction. (Is this mentioned somewhere in the documentation?) -- Nick
Re: [sqlite] Use sqlite on solaris with java in multithreaded web application
> I will send you schema and query for which I feel there is problem. > On the other hand the driver I used for connection between java and sqlite > (javasqlite- http://www.ch-werner.de/javasqlite/) states that this driver is > tested only in Linux. I was wondering even if I should use SQLite with java > on solaris in live applications. I don't know about the java/sun interface to sqlite. I have used it on linux and windows and it's worked very well for me. Others on this list might know more.
RE: [sqlite] Use sqlite on solaris with java in multithreaded web application
Hi Jay, Thanks for your immediate reply. I will send you schema and query for which I feel there is problem. On the other hand the driver I used for connection between java and sqlite (javasqlite- http://www.ch-werner.de/javasqlite/) states that this driver is tested only in Linux. I was wondering even if I should use SQLite with java on solaris in live applications. Regards, Nitin -Original Message- From: Jay Sprenkle [mailto:[EMAIL PROTECTED] Sent: Friday, April 21, 2006 6:59 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Use sqlite on solaris with java in multithreaded web application > Hi, > > I want to use sqlite as database in Solaris Environment in Java based web > application. There can be multiple read only requests coming to database at > same time. I tried using javasqlite driver for using sqlite with Java but as > number of users or request increase response time increase drastically and > sometimes jsp page doesn't even render in browser. > > Is it advisable to use sqlite in solaris with javasqlite driver in > multithreaded environment? OR there is any other better driver. I even tried > using 3.3.5 version of sqlite. I would look at the indexes on your database. Which queries are slowing response time? You might share the query and schema with us so we can help you optimize them.
Re: [sqlite] Use sqlite on solaris with java in multithreaded web application
> Hi, > > I want to use sqlite as database in Solaris Environment in Java based web > application. There can be multiple read only requests coming to database at > same time. I tried using javasqlite driver for using sqlite with Java but as > number of users or request increase response time increase drastically and > sometimes jsp page doesn't even render in browser. > > Is it advisable to use sqlite in solaris with javasqlite driver in > multithreaded environment? OR there is any other better driver. I even tried > using 3.3.5 version of sqlite. I would look at the indexes on your database. Which queries are slowing response time? You might share the query and schema with us so we can help you optimize them.
[sqlite] Use sqlite on solaris with java in multithreaded web application
Hi, I want to use sqlite as database in Solaris Environment in Java based web application. There can be multiple read only requests coming to database at same time. I tried using javasqlite driver for using sqlite with Java but as number of users or request increase response time increase drastically and sometimes jsp page doesn't even render in browser. Is it advisable to use sqlite in solaris with javasqlite driver in multithreaded environment? OR there is any other better driver. I even tried using 3.3.5 version of sqlite. Any help will be appreciated. Regards, Nitin Mahajan
Re: [sqlite] Getting free pages count
I mean freepages.c under "not official methods" :) -- View this message in context: http://www.nabble.com/Getting-free-pages-count-t1485769.html#a4025820 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Rule of thumb for estimating index size
Hi Christian, thanks for the explanation, when you explain it like that it's so simple, the index in the book concept didnt even come to me. lol. John. On 21/04/06, Christian Smith <[EMAIL PROTECTED]> wrote: > > On Fri, 21 Apr 2006, Rusty Conover wrote: > > >Hi, > > > >Is there an easy way to get an estimate on the space used by an > >existing index? If not what is a good guess on how to estimate the > >size? > > > >My guess would be (assuming text fields are being indexed): > > > >[total length of all index keys] + [number of rows]*8 (for the offset > >location) > > > >Is that close? I realize disregards all space used by page allocation > >overhead. > > > The page overhead will be pretty constant per index entry. > > One underestimate from above is the effect of overflow pages and internal > fragmentation. If the key doesn't fit in the btree node, overflow pages > are used to store the rest of the key, and the pages are used in their > entirety, and not shared with other entries. Thus, if your keys are quite > long, the internal fragmentation must be taken into account in the total > key length. If 1/4 of your rows cause overflow, the the extra overhead can > be approximated as: > > pagesize/2 * [num of rows]/4 > > This assumes that the last overflow page is on average half full. > > > > > >Thanks, > > > >Rusty > >-- > >Rusty Conover > >InfoGears Inc. > > > > > > > > > > -- > /"\ > \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL > X - AGAINST MS ATTACHMENTS > / \ >
Re: [sqlite] Getting free pages count
Alexey Belyaev wrote: > Hi! > > In current versions sqlite (3.2.7 and later) not enought functions, > for receive information abut free pages count (or used pages count). > I have to use not official methods :( > May I expect that this functions will be introduced in next versions sqlite? > > I too would like an officially supported method for getting the free page count. I hacked up the following based on James P. Lyon's prior sqlite 2.xx work: http://unwg.no-ip.com/freepages.c
Re: [sqlite] Rule of thumb for estimating index size
On Fri, 21 Apr 2006, Rusty Conover wrote: >Hi, > >Is there an easy way to get an estimate on the space used by an >existing index? If not what is a good guess on how to estimate the >size? > >My guess would be (assuming text fields are being indexed): > >[total length of all index keys] + [number of rows]*8 (for the offset >location) > >Is that close? I realize disregards all space used by page allocation >overhead. The page overhead will be pretty constant per index entry. One underestimate from above is the effect of overflow pages and internal fragmentation. If the key doesn't fit in the btree node, overflow pages are used to store the rest of the key, and the pages are used in their entirety, and not shared with other entries. Thus, if your keys are quite long, the internal fragmentation must be taken into account in the total key length. If 1/4 of your rows cause overflow, the the extra overhead can be approximated as: pagesize/2 * [num of rows]/4 This assumes that the last overflow page is on average half full. > >Thanks, > >Rusty >-- >Rusty Conover >InfoGears Inc. > > > > -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Rule of thumb for estimating index size
On Fri, 21 Apr 2006, John Newby wrote: >A little off subject I know, but what is the purpose of an index? Just like in a book. If you want to search for particular terms in a book, you look up the page number in the index. In a database, fields are indexed, so you can see which rows contain particular field values without searching the whole table. For example, consider the following rows: rowid | field1 | field2 --++--- 0 | foo| bar 1 | foo| foo 2 | bar| foo 3 | bar| bar If the field1 is indexed, then your index will look like key | rowid --+-- foo | 0 foo | 1 bar | 2 bar | 3 If we want all rows where field1 = 'foo', then we can use the index to locate the only two rows that satisfy the condition (0 and 1 in this case). If we want to find all rows where field2 = 'foo', then we have to search the whole of the table. Indexes come into their own when tables have thousands or millions of rows with largely disparate field values. Finding a particular row in these cases can be as quick as a single index lookop followed by a single row lookup, instead of a multi-million row table scan. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Rule of thumb for estimating index size
A little off subject I know, but what is the purpose of an index? On 21/04/06, Rusty Conover <[EMAIL PROTECTED]> wrote: > > Hi, > > Is there an easy way to get an estimate on the space used by an > existing index? If not what is a good guess on how to estimate the > size? > > My guess would be (assuming text fields are being indexed): > > [total length of all index keys] + [number of rows]*8 (for the offset > location) > > Is that close? I realize disregards all space used by page allocation > overhead. > > Thanks, > > Rusty > -- > Rusty Conover > InfoGears Inc. > > > >
[sqlite] Rule of thumb for estimating index size
Hi, Is there an easy way to get an estimate on the space used by an existing index? If not what is a good guess on how to estimate the size? My guess would be (assuming text fields are being indexed): [total length of all index keys] + [number of rows]*8 (for the offset location) Is that close? I realize disregards all space used by page allocation overhead. Thanks, Rusty -- Rusty Conover InfoGears Inc.
[sqlite] Getting free pages count
Hi! In current versions sqlite (3.2.7 and later) not enought functions, for receive information abut free pages count (or used pages count). I have to use not official methods :( May I expect that this functions will be introduced in next versions sqlite? Thanks, Alexey -- View this message in context: http://www.nabble.com/Getting-free-pages-count-t1485769.html#a4023973 Sent from the SQLite forum at Nabble.com.
[sqlite] Probleb with running 3.2.7 and 3.3.4 under qnx6.3.1 (armle)
Hi! I have some problem with running sqlite on board with arm7 processor under QNX6.3.0 (flash filesystem) Library and command shell (sqlite3) were compiled on self-hosted (x86-pc), gcc 3.3.5, qnx neutrino 6.3.0 #configure --host=arm-unknown-nto-qnx6.3.0 #make CPU:ARM Release:6.3.0 FreeMem:10Mb/16Mb BootTime:Mar 07 09:24:07 UTC 2006 Processor1: 1098936834 arm720 74MHz running sqlite3(command shell) on arm-board log: -- # sqlite3 /var/test_base SQLite version 3.3.4 Enter ".help" for instructions sqlite> create table TEST_TABLE (A integer, B integer, primary key (A)); sqlite> insert into TEST_TABLE (A,B) values (1,2); sqlite> insert into TEST_TABLE (A,B) values (3,4); sqlite> select * from TEST_TABLE; 1|2 3|4 sqlite> select * from TEST_TABLE where (A>0) order by A; SQL error: database is locked sqlite> insert into TEST_TABLE (A,B) values (5,3); sqlite> select * from TEST_TABLE order by B; SQL error: database is locked sqlite> .quit # # ls -l /var total 0 -rw-r--r-- 1 root root 0 Mar 07 14:39 test_base # -- 1) SQL error: database is locked - what does it mean ? 2) why /var/test_database have zero-size after quit ? And one more feature: I wrote a test program (C), which do insertion of two records into test_table, and "select * from TEST_TABLE order by A;", i got an error : code=1 what=SQL logic error or missing database Any advise ? -- View this message in context: http://www.nabble.com/Probleb-with-running-3.2.7-and-3.3.4-under-qnx6.3.1-%28armle%29-t1240122.html#a3281991 Sent from the SQLite forum at Nabble.com.
