Re: [sqlite] Calculating the size of a backup
After a bit of digging I think (PRAGMA) page_count * (PRAGMA) page_size is roughly the size of the database but is the actual database file that size. Is there a header to take into account too? > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Brown, Daniel > Sent: Friday, April 03, 2009 11:42 AM > To: General Discussion of SQLite Database > Subject: [sqlite] Calculating the size of a backup > > Good Morning List, > > Is there any way to calculate the size of a database backup before > doing a backup? I need to know how big the backup database is going to > before I call the new backup API: as the backup database is using an in > memory VFS. > > Cheers, > > Daniel Brown | Software Engineer > "The best laid schemes o' mice an' men, gang aft agley" > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/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] Calculating the size of a backup
Good Morning List, Is there any way to calculate the size of a database backup before doing a backup? I need to know how big the backup database is going to before I call the new backup API: as the backup database is using an in memory VFS. Cheers, Daniel Brown | Software Engineer "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Saving and loading SQLite pages from a buffer
Hi Richard, Thanks for the advice: I will take a look into a barebones VFS implementation for backup purposes. I assume it won't need the usual locking functionality that normal VFS implementations require: pretty much just read, write, open and close? What is the ETA for 3.6.11? Daniel > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of D. Richard Hipp > Sent: Wednesday, February 04, 2009 3:19 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Saving and loading SQLite pages from a buffer > > > On Feb 4, 2009, at 6:09 PM, Brown, Daniel wrote: > > > > I currently use SQLite by opening ":memory:" and then copying the > > tables > > I want from read only storage into ":memory:" via an attached read > > only > > database, which I drop after copying is complete. My issue is saving > > and loading the ":memory:" database once I've used it and changed its > > contents after this initial creation. I cannot write the backup > > back to > > the file system as it is read only but I can potentially save and > load > > it via an API that saves and loads blocks of memory to a persistent > > memory location on my platform. > > > > So, create a VFS that instead of writing to disk, writes to the > persistent memory location on your platform. That backup to your > VFS. If the only operation your VFS supports is a backup, it can be > very simply - omitting much of the functionality found in a general- > purpose VFS. > > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Saving and loading SQLite pages from a buffer
-Original Message- >The hot backup API has not yet been released. Look for version 3.6.11. I will look forward to seeing that release! Is there anything I can do to help with the development? >If you open your database using the special name ":memory:" then it is >held entirely in memory with no duplication of pages (accept as >required to support ROLLBACK). Such a :memory: database is initially >empty, of course. So then initialize it from a file using the backup >API. I currently use SQLite by opening ":memory:" and then copying the tables I want from read only storage into ":memory:" via an attached read only database, which I drop after copying is complete. My issue is saving and loading the ":memory:" database once I've used it and changed its contents after this initial creation. I cannot write the backup back to the file system as it is read only but I can potentially save and load it via an API that saves and loads blocks of memory to a persistent memory location on my platform. I guess I could implement an in memory VFS to use with the existing backup API but that seems more complicated than extending the backup API to accept save or load from a memory buffer. As I said I'd be happy to contribute some time to extending the back system to support this. Daniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Saving and loading SQLite pages from a buffer
Thanks for the link Richard, very interesting! What version(s) of SQLite contain this experimental functionality? I am using version 3.6.10 non-amalgamation but I can't find the functions mentioned on that page. If I understand this backup API correctly then its functionality is pretty close to what I need. I would need the option for either the source or destination to be a buffer in memory or a database connection. Daniel -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp Sent: Wednesday, February 04, 2009 2:19 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Saving and loading SQLite pages from a buffer On Feb 4, 2009, at 5:12 PM, Brown, Daniel wrote: > Good Afternoon List, > > I've been looking at how best to save/load SQLite database that is > 100% > in memory to and from a memory buffer instead of a file via a VFS > operating system wrapper. I had initially thought that implementing a > Virtual File System (VFS) was a solution but then I realised that > would > double my memory usage. As I would be holding the pages internally in > the VFS and SQLite would also be holding duplicates internally in its > page system, as well as another partial set of duplicates in the > SQLite > page cache. > > After thinking about this for a bit, a more efficient implementation > could be to save or load the database pages held in SQLite's internal > page system. This would effectively involve two new functions being > added to the SQLite API: one to seed the page system with pages > contained in a supplied buffer (load) and another to copy pages out of > the page system and into a buffer (save). > > I've had a look through the O'Reilly eBook "Inside SQLite" and through > the SQLite source code and I think this solution is possible. I would > like to know if this approach seems a reasonable solution or not? I > would also appreciate any advice about safely modifying the page > system > that anyone would be able to share. > http://www.sqlite.org/draft/c3ref/backup_finish.html D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Saving and loading SQLite pages from a buffer
Good Afternoon List, I've been looking at how best to save/load SQLite database that is 100% in memory to and from a memory buffer instead of a file via a VFS operating system wrapper. I had initially thought that implementing a Virtual File System (VFS) was a solution but then I realised that would double my memory usage. As I would be holding the pages internally in the VFS and SQLite would also be holding duplicates internally in its page system, as well as another partial set of duplicates in the SQLite page cache. After thinking about this for a bit, a more efficient implementation could be to save or load the database pages held in SQLite's internal page system. This would effectively involve two new functions being added to the SQLite API: one to seed the page system with pages contained in a supplied buffer (load) and another to copy pages out of the page system and into a buffer (save). I've had a look through the O'Reilly eBook "Inside SQLite" and through the SQLite source code and I think this solution is possible. I would like to know if this approach seems a reasonable solution or not? I would also appreciate any advice about safely modifying the page system that anyone would be able to share. Cheers, Daniel Brown | Software Engineer "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.6.10: Memory savings with large pages
Good morning list, Has anyone else noticed significant memory savings when using larger page sizes since upgrading to 3.6.9 or 3.6.10 (we were on 3.6.1 previously)? We use about 7.32 MB (Peak 9.64 MB) when loading our database into RAM using default settings and using larger page sizes (PRAGMA page_size = 4096) used to not affect memory usage much. Since the upgrade we are now using only 5.65 MB (Peak 8.00 MB) loading the same database using larger pages, very impressive! Cheers, Daniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.10
The frequent releases are not a problem as far as I am concerned. I'd rather have bugs fixed quickly when they are discovered, than wait months for releases containing needed fixes like other libraries. We use the loose pre-generated C files (not the amalgamation) and even then it only takes me about 10-20 minutes to integrate a new release and about another 10-40 minutes to run it through our unit tests. Daniel -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Mike McGonagle Sent: Thursday, January 15, 2009 9:57 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite version 3.6.10 Thank you for these releases, I appreciate (and agree) with your reasons for the many releases. For me, it really is so simple that all I do is take the Amalgamated version and swap it out for the previous version. I can only think that anyone who has troubles with a swap like this may be doing things that are either undocumented, or are to get around things they want to do... just my $0.02... Thanks again. Michael McGonagle On Thu, Jan 15, 2009 at 11:07 AM, D. Richard Hipp wrote: > SQLite version 3.6.10 is now available on the website. Upgrading is > recommended for all users. > >http://www.sqlite.org/ >http://www.sqlite.org/news.html >http://www.sqlite.org/download.html > > SQLite version 3.6.10 fixes a cache coherency bug (Ticket #3584) > introduced by check-in [5864] which was part of version 3.6.5. This > bug might lead to database corruption, hence we felt it was important > to get it out as quickly as possible, even though there had already > been two prior releases this week. > > Some concern has been expressed that we are releasing too frequently. > (Three releases in one week is a lot!) The concern is that this > creates the impression of volatility and unreliability. We have been > told that we should delay releases in order to create the impression > of stability. But the SQLite developers feel that truth is more > important than perception, not the other way around. We think it is > important to make the highest quality and most stable version of > SQLite available to users at all times. This week has seen two > important bugs being discovered shortly after a major release, and so > we have issued two emergency patch releases after the regularly > scheduled major release. This makes us look bad. This puts "egg on our > face." We do not like that. But, three releases also ensures that the > best quality SQLite code base is available available to you at all > times. > > It has been suggested that "beta" releases might find these kinds of > bugs prior to a major release. But our experience indicates otherwise. > The two issues that prompted releases 3.6.9 and 3.6.10 were both > discovered by internal testing and review - not by external users. > And, indeed, most the problems found in SQLite these days are > discovered by our rigorous internal testing protocol, not bug reports > from the field. > > It has also been argued that we should withhold releases "until > testing is finished." The falacy there is that we never finish > testing. We are constantly writing new test cases for SQLite and > thinking of new ways to stress and potentially break the code. This is > a continuous, never-ending, and on-going process. All existing tests > pass before each release. But we will always be writing new tests the > day after a release, regardless of how long we delay that release. And > sometimes those new tests will uncover new problems. > > All this is to say that we believe that SQLite version 3.6.10 is the > most stable, most thoroughly tested, and bug-free version of SQLite > that has ever existed. Please do not be freaked out by three releases > occurring in one week. > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Peace may sound simple-one beautiful word- but it requires everything we have, every quality, every strength, every dream, every high ideal. -Yehudi Menuhin (1916-1999), musician ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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] Converted sqlite3VdbeSerialTypeLen into a macro
Good morning list,
I've been doing some profiling of SQLite 3.6.1 on PC and some of our
other proprietary platforms. It has become evident that the function
'sqlite3VdbeSerialTypeLen' is being hit a lot: enough that the function
is adding up to a fair % of program execution time. As the actual
calculation the function is performing is relatively trivial compared to
the costs of a function call, I propose converting the function into an
macro to reduce the cost. I am suggesting a macro as C has no reliable
cross compiler inline functionality or at least the compilers we have
here don't seem to support the inline functionality added in C99.
I made the following addition to 'vdbeInt.h' and removed the original C
function, prototype and replaced any references to the original
function.
/* Convert sqlite3VdbeSerialTypeLen into a macro: no way to inline in C!
*/
static const u8 g_aSize[] = { 0, 1, 2, 3, 4, 6, 8, 8, 0, 0, 0, 0 };
#define SQLITE3VDBESERIALTYPELEN(serial_type) ( serial_type>=12 ?
(serial_type-12)/2 : g_aSize[serial_type])
Please not that I don't have TCL set-up on this PC so I am unable to
test this change against the current mainline right now. I'd be
interested in any feedback on this proposed change.
Cheers,
Daniel Brown | Software Engineer
"The best laid schemes o' mice an' men, gang aft agley"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite performance woe
Hello Donald, I have managed to speed up some of my SQLite queries by about 300% by writing them from joins to sub queries. I am comparing SQLite against a custom SQL implementation we have here. It looks like it is our cursor implementation that is so much faster than SQLite the regular queries are roughly comparable. I'm going to step through our custom SQL cursor code and see if how it is managing that sort of speed. I can't really post my table schema without first obscuring like I did the original test queries it as it contains sensitive information but I'll try taking a look at the schema output. Daniel -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald Sent: Wednesday, December 03, 2008 3:51 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite performance woe Hi again, Daniel, So I guess you're still having certain queries that take about 200x longer than with your custom code, right? There's nothing magical about sqlite, so it's not surprizing that code customized for an application can outperform a generalized sql engine, but a factor of 200 does seems pretty large. If it's neither especially proprietary nor private, I'd again urge you to post here your schema (and repost the slow queries). You can dump this easily using the command-line utility program, e.g.: sqlite3 myDatabase.db .output mySchema .schema .quit The schema should then be in the file "mySchema" If the database itself is not too private, you may want to compress it (with something like 7zip) and upload it somewhere on the web and provide a link to it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite performance woe
Thank you for the explanation! I now have primary keys and indices added to my tables, my exporter was not previously exporting primary keys correctly to SQLite and I just added index exporting. However I am not seeing any performance gain!! Using EXPLAIN QUERY PLAN is mostly coming back as using PRIMARY KEY or sometimes an index which is good but I would have expected some sort of performance gain. Every table should have at least one primary key and one or more indices now. The file size has increased by about 25-30% too which is unavoidable I guess, I've tried vacuuming but that does not seem to effect the file size much. The sqlite3_analyzer gives the following report summary: Page size in bytes 1024 Pages in the whole file (measured) 2628 Pages in the whole file (calculated).. 2628 Pages that store data. 2628 100.0% Pages on the freelist (per header) 00.0% Pages on the freelist (calculated) 00.0% Pages of auto-vacuum overhead. 00.0% Number of tables in the database.. 77 Number of indices. 152 Number of named indices... 77 Automatically generated indices... 75 Size of the file in bytes. 2691072 Bytes of user payload stored.. 1501970 55.8% -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John Stanton Sent: Tuesday, December 02, 2008 5:06 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite performance woe To efficiently execute the SQL "SELECT * FROM mytab WHERE myid = '1234'" you must have an index on the "myid" colunm. Each row has an index which uses a rowid as a key, and that is how the row is accessed. A "primary key" is a column which is indexed and which has a unique value, duplicates are not encouraged. You may have many indices on a table, and an index my have as its key values from more than one column. Brown, Daniel wrote: > I maybe confused but indices sound similar to what I understand primary > keys do, I already have primary keys on each table. Unless I'm mistaken > as to what primary keys are? From your explanation I guess I'm slightly > confused about the difference in primary keys and indices and that I > need to implement indices to speed up my queries. Are there any general > guidelines for creating indices? Is it as simple as creating an indice > per primary key in a table? > > How do I interpret the output from EXPLAIN QUERY PLAN? > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of John Stanton > Sent: Tuesday, December 02, 2008 2:20 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SQLite performance woe > > Databases work by using indices. A search for a row in a table of 1 > million rows goes from having to do as many as a million row reads to a > handful of index node accesses, from minutes to milliseconds. Note that > > Sqlite is "lite" and only uses one index at a time so thoughtful schema > design and query layout is necessary for optimal results. > > Brown, Daniel wrote: > >>Hello Donald & Others, >> >>I have primary keys set for each of the table but no indicies (that I > > am > >>aware of) as I simply converted the data from our existing database >>system which does not support indicies. As my current system only >>implements primary keys I have no real experience dealing with > > indicies, > >>are they like some sort of extra key column? Are there any guides to >>optimising SQLite performance with indicies? >> >>I tried EXPLAIN QUERY PLAN for the following: >>"SELECT * FROM test_item INNER JOIN test_container ON >>test_item.container_code = test_container.container_code" >> >>The output was: >>0|0|TABLE test_item >>1|1|TABLE test_container >> >>Is there a guide I can check for understanding this output? >> >>Daniel >> >>-Original Message- >>From: [EMAIL PROTECTED] >>[mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald >>Sent: Tuesday, December 02, 2008 9:52 AM >>To: General Discussion of SQLite Database >>Subject: Re: [sqlite] SQLite performance woe >> >>Hi Daniel, >> >>Regarding: >> "What I'd like to know is if there is anything we can do with >>our queries, SQLite set-up or library configuration to improve the >>speed? " >> >>Unless indicies would be inappropriate, did you mention whether you've >>defined any
Re: [sqlite] SQLite performance woe
I maybe confused but indices sound similar to what I understand primary keys do, I already have primary keys on each table. Unless I'm mistaken as to what primary keys are? From your explanation I guess I'm slightly confused about the difference in primary keys and indices and that I need to implement indices to speed up my queries. Are there any general guidelines for creating indices? Is it as simple as creating an indice per primary key in a table? How do I interpret the output from EXPLAIN QUERY PLAN? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John Stanton Sent: Tuesday, December 02, 2008 2:20 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite performance woe Databases work by using indices. A search for a row in a table of 1 million rows goes from having to do as many as a million row reads to a handful of index node accesses, from minutes to milliseconds. Note that Sqlite is "lite" and only uses one index at a time so thoughtful schema design and query layout is necessary for optimal results. Brown, Daniel wrote: > Hello Donald & Others, > > I have primary keys set for each of the table but no indicies (that I am > aware of) as I simply converted the data from our existing database > system which does not support indicies. As my current system only > implements primary keys I have no real experience dealing with indicies, > are they like some sort of extra key column? Are there any guides to > optimising SQLite performance with indicies? > > I tried EXPLAIN QUERY PLAN for the following: > "SELECT * FROM test_item INNER JOIN test_container ON > test_item.container_code = test_container.container_code" > > The output was: > 0|0|TABLE test_item > 1|1|TABLE test_container > > Is there a guide I can check for understanding this output? > > Daniel > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald > Sent: Tuesday, December 02, 2008 9:52 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SQLite performance woe > > Hi Daniel, > > Regarding: >"What I'd like to know is if there is anything we can do with > our queries, SQLite set-up or library configuration to improve the > speed? " > > Unless indicies would be inappropriate, did you mention whether you've > defined any indicies and does EXPLAIN QUERY PLAN show that the proper > index is being used? > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/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 performance woe
Hello Donald & Others, I have primary keys set for each of the table but no indicies (that I am aware of) as I simply converted the data from our existing database system which does not support indicies. As my current system only implements primary keys I have no real experience dealing with indicies, are they like some sort of extra key column? Are there any guides to optimising SQLite performance with indicies? I tried EXPLAIN QUERY PLAN for the following: "SELECT * FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code" The output was: 0|0|TABLE test_item 1|1|TABLE test_container Is there a guide I can check for understanding this output? Daniel -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald Sent: Tuesday, December 02, 2008 9:52 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite performance woe Hi Daniel, Regarding: "What I'd like to know is if there is anything we can do with our queries, SQLite set-up or library configuration to improve the speed? " Unless indicies would be inappropriate, did you mention whether you've defined any indicies and does EXPLAIN QUERY PLAN show that the proper index is being used? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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] SQLite performance woe
Good evening list, I have been profiling the performance of SQLite version 3.6.1 against my current custom (hacktastic) runtime database solution (which I am hoping to replace with SQLite) and I just got a nasty and unexpected result: SQLite is a lot slower! I am running SQLite completely in memory during profiling using an in memory database (:memory:) and I am setting temp_store=MEMORY and journal_mode=MEMORY. I have ten tests statements that select data from an database that I load completely into memory from file before each test begins. I iterate over the results summing the values of a column (usually max) and record the duration over ten iterations to get a rough mean, high and low time count: 1. "SELECT * FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code" 2. "SELECT * FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code WHERE (test_item.item_code > '100')" 3. "SELECT * FROM test_item WHERE (test_item.item_code > '100')" 4. "SELECT * FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code WHERE (test_item.item_code > '100') AND (test_item.max > '50') AND (test_item.initial > '30')" 5. "SELECT max FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code"; 6. "SELECT * FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code ORDER BY max" 7. "SELECT * FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code WHERE (test_item.max > '50') ORDER BY max" 8. "SELECT max FROM test_item" 9. "SELECT * FROM test_item" SQLite: in memory. Test 1 Mean 288.470825, Low 286.080383, High 308.041931. (milliseconds) Test 2 Mean 270.140808, Low 267.758209, High 289.795166. (milliseconds) Test 3 Mean 68.888512, Low 66.573952, High 88.525116. (milliseconds) Test 4 Mean 255.051758, Low 251.905319, High 273.752533. (milliseconds) Test 5 Mean 103.347633, Low 101.023598, High 123.561203. (milliseconds) Test 6 Mean 2050.301025, Low 2047.153442, High 2069.840088. (milliseconds) Test 7 Mean 1927.293213, Low 1923.600952, High 1944.863159. (milliseconds) Test 8 Mean 16.426598, Low 13.990897, High 37.434727. (milliseconds) Test 9 Mean 68.630898, Low 66.280098, High 88.732208. (milliseconds) Custom DB: in memory. Test 1 (milliseconds) mean,low,high, 10.120694,9.463699,10.540760 Test 2 (milliseconds) mean,low,high, 10.226122,9.593320,10.600039 Test 3 (milliseconds) mean,low,high, 9.077908,8.492440,9.602920 Test 4 (milliseconds) mean,low,high, 10.259830,9.591120,10.668540 Test 5 (milliseconds) mean,low,high, 3.890896,3.626060,4.679040 Test 6 (milliseconds) mean,low,high, 10.156658,9.529779,10.696919 Test 7 (milliseconds) mean,low,high, 10.371894,9.598040,11.083039 Test 8 (milliseconds) mean,low,high, 1.155094,1.010600,2.416280 Test 9 (milliseconds) mean,low,high, 1.120254,1.002940,2.032100 As you can see the performance difference is significant, profiling indicates that 75%-80% of the test execution for SQLite is being spent inside sqlite3VdbeExec. What I'd like to know is if that is normal and if there is anything we can do with our queries, SQLite set-up or library configuration to improve the speed? So far I have found force inlining sqlite3VdbeSerialTypeLen helped the performance a bit. I am not using the amalgamation version of the source as I have our my VFS implementations for two of the platforms I work with based on the original win_os.c VFS and the amalgamation does not provide the necessary header files (os_common.h and sqliteInt.h) to make VFS integration possible. Other than by inserting the source for my VFS implementations directly into the amalgamation source, which I'd rather not do as it would make upgrading to new SQLite versions much more complex. I love SQLite's feature set, flexibility, tools and syntax compared to my current solution but I need SQLite to be at least as fast as my current solution to make it worth the switch. Cheers, Daniel Brown | Software Engineer "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Journal files
Cheers adding the extra pragma has stopped the temporary file activity
:)
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Monday, December 01, 2008 6:14 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Journal files
On Dec 1, 2008, at 9:11 PM, Brown, Daniel wrote:
> I'm using "PRAGMA journal_mode = MEMORY" combined with an in memory
> database ":memory:" on version 3.6.1. And I too am seeing lots of
> temporary file activity, which is really killing our performance as
> our
> storage medium is so slow.
Try using
PRAGMA temp_store=MEMORY;
In addition to
PRAGMA journal_mode=MEMORY;
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Stephen
> Abbamonte
> Sent: Monday, December 01, 2008 3:50 PM
> To: 'General Discussion of SQLite Database'
> Subject: Re: [sqlite] Journal files
>
> I just tried this line also and the journal files are still being
> created
> here is the code I am running:
>
>
>int32_t ret = sqlite3_open(filename, m_DatabaseRef);
> if( ret == SQLITE_OK )
>{
> sqlite3_stmt* sqlStmt = NULL;
> ret = sqlite3_prepare_v2(m_DatabaseRef, s_ "PRAGMA
> main.journal_mode = OFF;", -1, &sqlStmt, NULL);
> if( ret == SQLITE_OK )
> {
> sqlite3_step(sqlStmt);
> const unsigned char* colValue =
> sqlite3_column_text(sqlStmt, 0); <- returns "off"
> sqlite3_finalize(sqlStmt);
> }
> }
>
> Anything wrong with the way I am doing this?
> Thanks for the help.
>
>
>> Did you set
>>
>> PRAGMA journal_mode = OFF;
>>
>> ? The way I read the documentation (on a second or third close
>> reading, I think), this only sets the default value for new databases
>> to be attached, but doesn't affect your main connection and any
>> databases that have already been attached. So what I do is
>>
>> PRAGMA main.journal_mode = OFF;
>>
>> IIRC, I needed this line to actually turn off the journals (tested
>> with 3.4.x and 3.5.x versions of SQLite only, though).
>>
>> Cheers,
>> Stefan
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
D. Richard Hipp
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/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] Journal files
I'm using "PRAGMA journal_mode = MEMORY" combined with an in memory
database ":memory:" on version 3.6.1. And I too am seeing lots of
temporary file activity, which is really killing our performance as our
storage medium is so slow.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Stephen Abbamonte
Sent: Monday, December 01, 2008 3:50 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Journal files
I just tried this line also and the journal files are still being
created
here is the code I am running:
int32_t ret = sqlite3_open(filename, m_DatabaseRef);
if( ret == SQLITE_OK )
{
sqlite3_stmt* sqlStmt = NULL;
ret = sqlite3_prepare_v2(m_DatabaseRef, s_ "PRAGMA
main.journal_mode = OFF;", -1, &sqlStmt, NULL);
if( ret == SQLITE_OK )
{
sqlite3_step(sqlStmt);
const unsigned char* colValue =
sqlite3_column_text(sqlStmt, 0); <- returns "off"
sqlite3_finalize(sqlStmt);
}
}
Anything wrong with the way I am doing this?
Thanks for the help.
>Did you set
>
> PRAGMA journal_mode = OFF;
>
>? The way I read the documentation (on a second or third close
>reading, I think), this only sets the default value for new databases
>to be attached, but doesn't affect your main connection and any
>databases that have already been attached. So what I do is
>
> PRAGMA main.journal_mode = OFF;
>
>IIRC, I needed this line to actually turn off the journals (tested
>with 3.4.x and 3.5.x versions of SQLite only, though).
>
>Cheers,
>Stefan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/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] Journal files
Does that control the creation of all temporary files created at runtime? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Monday, December 01, 2008 7:56 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Journal files On Dec 1, 2008, at 10:50 AM, Eric Minbiole wrote: >>I am looking for a way to completely turn off the >> creation >> of journal files. Any help is much appreciated. > > http://www.sqlite.org/pragma.html And in particular http://www.sqlite.org/pragma.html#pragma_journal_mode > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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] Loading database from memory buffer
What will happen if I don't implement the locking functionality does that mean that SQLite would no longer be thread safe? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns Sent: Thursday, November 27, 2008 12:05 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Loading database from memory buffer -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Brown, Daniel wrote: > However writing a new VFS would > seem to be quite an time consuming solution, If you don't have to worry about implementing the locking functionality (ie multi-threaded/process access) then doing your own VFS is very easy. You can just reuse most of the methods from the default VFS. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkku/V0ACgkQmOOfHg372QSQ0QCfeYqlQwCtfCQh2fvik9Qnq0yr AeAAn07M/mos+yHS3QkSvaWzRCKjWuhj =4OfS -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
[sqlite] Loading database from memory buffer
Good afternoon list, Is there any way to open a SQLite databases out of an in memory buffer rather than an operating system file (via a VFS)? The runtime environment of one of our platforms does not have local storage that can be accessed via a normal operating system VFS but via an interface that accesses very slow secure storage. This interface only supports passing files in and out via memory buffers and is hideously slow. On our other platforms we load our database into an completely memory resident database (:memory:) from an database file on read only storage which we attach to during start up and then detach once we've finished copying our data into memory. This approach has worked quite well so far, as it avoids SQLite attempting to access storage (which is read only) except during set-up. So we would quite like to keep using this approach if possible but are unsure about how to solve this new problem with working with the buffers coming from or going into secure storage. One solution would seem to be to write a new VFS that handles this reading and writing of memory buffers. However writing a new VFS would seem to be quite an time consuming solution, especially as current solution of using :memory: databases seems to be working except for this one issue. It would seem the easier solution would be to copy the data out of the buffer and into SQLite's memory pages when setting up and to copy the data from SQLite to the buffer during shutdown but I am unsure if this functionality already exists or if it does not exist how to begin implementing it. Any advice on potential solutions would be most welcome! Cheers, Daniel Brown | Software Engineer "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Generating CRC values for tables
Good Morning list, Is there any functionality built into SQLite to generate CRC values for tables? We would like to be able to verify that the contents of the table we just updated matches the intended contents. Currently using our old database solution we generate a CRC value for each table and compare them. Is there similar functionality in SQLite? Cheers, Daniel Brown | Software Engineer "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Which TCL distrabution for Windows XP for running SQLite tests?
Good afternoon list, If I would like to use the TCL based tests on my version of SQLite on Windows XP what would be the recommend TCL distribution to use? Would ActiveTCL (http://tinyurl.com/5wl6uv) be compatible with the SQLite TCL scripts on Windows XP? Cheers, Daniel Brown | Software Engineer "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Distinguishing between sqlite3_stmts
Almost, I want to be able to compare sqlite3_stmt structures somehow and be able to work out if given two pointers to a sqlite3_stmt are pointing at the same query instance or not, as the address pointed to is not sufficient. In the C++ system I have results objects are returning values from SQLite queries via an internal pointer to a sqlite3_stmt held in another C++ query object which the queries objects have a reference to. As it is possible to have multiple results objects per query object, I am concerned that if the sqlite3_stmt (in the query object) was recycled via finalize/prepare that it would mess up the remaining results objects that were pointing to it unless those result objects could detect the change of query themselves. Thinking about it the function you link to would probably be sufficient. If I either store the original query string or a hash of the query string locally in each result object (as space is at a premium a hash would be preferred). I could then compare the value stored in the results object against the value in the query object to determine if the query object had been recycled or not... Daniel -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Thursday, November 06, 2008 4:54 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Distinguishing between sqlite3_stmts On Nov 6, 2008, at 7:47 PM, Brown, Daniel wrote: > Good afternoon list, > > Are there any methods for distinguishing between sqlite3_stmt > structures > prepared by sqlite3_prepare_v2? I'd like to be able to tell if a > statement structure has been finalized and then prepared with a > different query programmatically. Is there any sort of unique > identifier in those prepared structures? I'm not sure I understand the question. Is http://www.sqlite.org/c3ref/sql.html what you are asking for? D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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] Distinguishing between sqlite3_stmts
Good afternoon list, Are there any methods for distinguishing between sqlite3_stmt structures prepared by sqlite3_prepare_v2? I'd like to be able to tell if a statement structure has been finalized and then prepared with a different query programmatically. Is there any sort of unique identifier in those prepared structures? Cheers, Daniel Brown | Software Engineer "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VFS implementation guidance
Thanks for the pointers Roger and the example tests. Is it possible to change the VFS SQLite is using while SQLite is running? I'm looking at creating two different VFS implementations and it would be great to be able to switch between implementations as required, I'd be looking to switch VFS during program start-up and before any databases are loaded or used. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns Sent: Tuesday, November 04, 2008 12:39 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] VFS implementation guidance -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Brown, Daniel wrote: > Are there any guides to implementing a VFS (sqlite3_vfs) for SQLite? A > good practices guide would be as useful. I already have an existing > file system API/library for the target system so I guess it is mostly > just matching up the API with the VFS implementation via some wrapper > functions? Pretty much just implement the functions as documented. One gotcha is that xRandomness is only called once and is only called on the default VFS which makes testing it fun. Another is that xGetLastError is never called so you don't need to implement it. http://www.sqlite.org/cvstrac/tktview?tn=3337 You then need to run queries that exercise all parts of the VFS. If you want some guidance, this is what I use: http://code.google.com/p/apsw/source/browse/apsw/trunk/tests.py#4759 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkkQssUACgkQmOOfHg372QTuLwCgygTWzPSW3CCHnQONXiEcKXf7 5XEAnR7DYzMf+hvXCORi/I/hpWgWF/t3 =3tEY -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
[sqlite] VFS implementation guidance
Good morning list, Are there any guides to implementing a VFS (sqlite3_vfs) for SQLite? A good practices guide would be as useful. I already have an existing file system API/library for the target system so I guess it is mostly just matching up the API with the VFS implementation via some wrapper functions? Cheers, Daniel Brown | Software Engineer "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Vista frustrations
Has anyone tried to replicate this bug on WindowsXP too? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns Sent: Wednesday, September 17, 2008 4:24 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Vista frustrations -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Robert Simpson wrote: > Ideally, at least on non-CE platforms, I'd like see SQLite not give the OS > any hints about caching. However, I'm not sure what kind of performance hit > (if any) that would have on Windows. It's already been proven that > providing the hint on WinCE is beneficial. I agree. Open a ticket and include measurements in it. With Windows CE, setting the flag prevents compression: Using the FILE_FLAG_RANDOM_ACCESS flag in the RAM file system, which places files in the object store, will prevent a file from being compressed. If performance is an issue, this could be the correct solution. Read and write operations to a compressed file are slower than read and write operations to an uncompressed file. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFI0ZGemOOfHg372QQRAtHSAJ9fSAXY3ekwrK4g9/eTrcYYQInW6QCgvSkB e00SnZbtTmQQ1G8ZxEVTK1M= =Y3uW -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
[sqlite] Dates & SQLite
Good morning list, Could someone point me to the documentation regarding dates and SQLite? I'm having trouble finding anything about what data type I should use to store dates in my SQLite tables, should it be a numerical type (integer or real) or a string? Cheers, Daniel Brown | Software Engineer @ EA Canada "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GCC -ffast-math safe version of sqlite3IsNaN
Thanks for the clarification Roger, I guess it looks like I will need to modify the compiler settings locally then. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns Sent: Tuesday, August 26, 2008 5:00 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] GCC -ffast-math safe version of sqlite3IsNaN -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Brown, Daniel wrote: > In the process of upgrading to 3.6.1 I've run into the error on line 46 > of util.c about int sqlite3IsNaN(double x) not behaving consistently > with the GCC -ffast-math compiler option (which we have enabled), is > there any alternative function I could use that would be compatible with > GCC -ffast-math? Removing the -ffast-math option from our projects > would be highly undesirable for us, as performance is paramount. If you don't use any floating point with SQLite then just remove the #error. However if you do use floating point with SQLite then you can't use fast math. See the following tickets which show a variety of problematic behaviour with -ffast-math: http://www.sqlite.org/cvstrac/tktview?tn=3101 http://www.sqlite.org/cvstrac/tktview?tn=3186 http://www.sqlite.org/cvstrac/tktview?tn=3194 http://www.sqlite.org/cvstrac/tktview?tn=3202 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFItJj7mOOfHg372QQRAlXDAJ9n+/Xe1E/1DszYXxCcVPjb+pxHOwCfcB5J XkV7LD9lbEv59oK9WS+r174= =8d1g -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] GCC -ffast-math safe version of sqlite3IsNaN
Unfortunately our build system is automated and not particularly agile, it can be done but it would be preferred not to have to do that and to replace the offending function instead but I've never had to write a IsNaN test. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Ruck Sent: Tuesday, August 26, 2008 3:59 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] GCC -ffast-math safe version of sqlite3IsNaN Have you tried to compile the util.c/amalgamation file without -ffast-math and use it with your other sources (compiled with -ffast-math)? Mike > -Ursprüngliche Nachricht- > Von: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Im Auftrag von Brown, Daniel > Gesendet: Mittwoch, 27. August 2008 00:38 > An: General Discussion of SQLite Database > Betreff: [sqlite] GCC -ffast-math safe version of sqlite3IsNaN > > Good afternoon list, > > In the process of upgrading to 3.6.1 I've run into the error > on line 46 of util.c about int sqlite3IsNaN(double x) not > behaving consistently with the GCC -ffast-math compiler > option (which we have enabled), is there any alternative > function I could use that would be compatible with GCC > -ffast-math? Removing the -ffast-math option from our > projects would be highly undesirable for us, as performance > is paramount. > > Cheers, > > Daniel Brown > "The best laid schemes o' mice an' men, gang aft agley" > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/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] GCC -ffast-math safe version of sqlite3IsNaN
Good afternoon list, In the process of upgrading to 3.6.1 I've run into the error on line 46 of util.c about int sqlite3IsNaN(double x) not behaving consistently with the GCC -ffast-math compiler option (which we have enabled), is there any alternative function I could use that would be compatible with GCC -ffast-math? Removing the -ffast-math option from our projects would be highly undesirable for us, as performance is paramount. Cheers, Daniel Brown "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_stmt declaration
Thanks for all the advice, I'll use void* pointers for SQLite internal structures which was my plan B if I couldn't find a declaration to feed the compiler to calm it. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brown, Daniel Sent: Monday, August 25, 2008 2:31 PM To: General Discussion of SQLite Database Subject: [sqlite] sqlite3_stmt declaration Good afternoon list, I'm attempting to wrap SQLite with Managed C++ and I'm getting some compiler warnings as the compiler/linker is have trouble finding the declaration of the structure 'sqlite3_stmt', I've tried looking for it manually but I can't find it either all I can find is a typedef on line 2569 of slite3.h. This isn't enough to stop the CLR compiler form complaining, I had a similar warning with the structure 'sqlite3' but including sqlite3Int.h resolved that warning as the structure is defined there, however tracking down the header file that defines 'sqlite3_stmt' seems to be leading no where... Cheers, Daniel Brown "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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] sqlite3_stmt declaration
Good afternoon list, I'm attempting to wrap SQLite with Managed C++ and I'm getting some compiler warnings as the compiler/linker is have trouble finding the declaration of the structure 'sqlite3_stmt', I've tried looking for it manually but I can't find it either all I can find is a typedef on line 2569 of slite3.h. This isn't enough to stop the CLR compiler form complaining, I had a similar warning with the structure 'sqlite3' but including sqlite3Int.h resolved that warning as the structure is defined there, however tracking down the header file that defines 'sqlite3_stmt' seems to be leading no where... Cheers, Daniel Brown | Software Engineer @ EA Canada "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
Interesting, I just tried that in my test application and Dennis's and I
get access violations during the vacuum command execution when trying to
resize the pages from 1k to 4k with my database or Dennis's test
database.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Kees Nuyt
Sent: Saturday, August 23, 2008 8:51 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Reducing SQLite Memory footprint(!)
On Thu, 21 Aug 2008 16:29:10 -0700, you wrote:
>How do I rebuild a database file for another page size
>or did the pragma do that already?
Use PRAGMA page_size={your_new_pagesize} immediately before
a vacuum. It will change the page size of the vacuumed
database. See:
http://www.sqlite.org/pragma.html#pragma_page_size
"As of version 3.5.8, if the page_size pragma is used
to specify a new page size just prior to running the
VACUUM command then VACUUM will change the page size
to the new value."
Demo:
sqlite_version():3.6.0
--
-- new database
PRAGMA page_size=8192;
BEGIN;
CREATE TABLE test (
x integer primary key,
y text
);
INSERT INTO test (y) VALUES ('row1');
INSERT INTO test (y) VALUES ('row2');
COMMIT;
PRAGMA page_size;
8192
PRAGMA schema_version;
1
PRAGMA page_size=1024;
VACUUM;
PRAGMA schema_version;
2
PRAGMA page_size;
1024
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
That script would be great :) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeffrey Becker Sent: Friday, August 22, 2008 1:31 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) At one point I wrote some vbscript to generate a table declaration and insert statements for a csv. I might be able to dig it up if you dont mind vbscript. On Fri, Aug 22, 2008 at 1:58 PM, Brown, Daniel <[EMAIL PROTECTED]> wrote: > I just ran Dennis's test databases through the test application and > we're getting similar results: >1k Pages (17.4 MB) used 18102 KB High 20416 KB >4k Pages (12.2 MB) used 18102 KB, High 26416 KB (not sure why > the high is higher?) > My test database however with the same test application produces the > following: >1k Pages (7.46 MB) used 22735 KB, High 25138 KB. > > So it looks my issue could be data related if my test database going > through the same app is coming out so large, Dennis's database is > expanding to about 101.6% of its original size but mine is expanding to > 297.6% of its original size. This begs the question is the 3rd party > tool (SQLite Analyzer) I'm using to import from an excel file causing > this expansion with bad data type choices? And is there any other way > to import table structure and contents from xls (or csv) to sqlite? > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote > Sent: Friday, August 22, 2008 7:10 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) > > Brown, Daniel wrote: >> 2. And the other thing to try would be if anyone has a fairly meaty > test >> database they don't mind sharing that I could fling at my test >> application to try and rule out the data? >> > > Daniel, > > I can send you copies of the databases I am using for my testing, both > the version with the 1K page size (17.4 MB) and the one with the 4K page > > size (12.2 MB). > > Where would you like me to send them? The zipped versions are each about > > 1.3 MB in size. > > Dennis Cote > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/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] Reducing SQLite Memory footprint(!)
I just ran Dennis's test databases through the test application and we're getting similar results: 1k Pages (17.4 MB) used 18102 KB High 20416 KB 4k Pages (12.2 MB) used 18102 KB, High 26416 KB (not sure why the high is higher?) My test database however with the same test application produces the following: 1k Pages (7.46 MB) used 22735 KB, High 25138 KB. So it looks my issue could be data related if my test database going through the same app is coming out so large, Dennis's database is expanding to about 101.6% of its original size but mine is expanding to 297.6% of its original size. This begs the question is the 3rd party tool (SQLite Analyzer) I'm using to import from an excel file causing this expansion with bad data type choices? And is there any other way to import table structure and contents from xls (or csv) to sqlite? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Friday, August 22, 2008 7:10 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) Brown, Daniel wrote: > 2. And the other thing to try would be if anyone has a fairly meaty test > database they don't mind sharing that I could fling at my test > application to try and rule out the data? > Daniel, I can send you copies of the databases I am using for my testing, both the version with the 1K page size (17.4 MB) and the one with the 4K page size (12.2 MB). Where would you like me to send them? The zipped versions are each about 1.3 MB in size. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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] Reducing SQLite Memory footprint(!)
Thanks I'll try the fossil database tomorrow morning. I just tried creating a new test application using Visual Studios built in C++ wizards (not our proprietary system) using Dennis's test application source code with a copy of the 3.6.1 amalgamation and my test database. I'm still seeing a 7645 KB database file turn into 22735 KB with a high of 25138 KB at runtime. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Thursday, August 21, 2008 5:24 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) On Aug 21, 2008, at 7:33 PM, Brown, Daniel wrote: > if anyone has a fairly meaty test > database they don't mind sharing that I could fling at my test > application... A project repository with "fossil" is a (meaty) SQLite database. You could (for example) go clone one of the various fossil projects out there and use that as your test database. First download a pre- compiled fossil binary: http://www.fossil-scm.org/download.html Then clone a repository: http://www.sqlite.org/experimental (~5MB) http://www.sqlite.org/docsrc (~2MB) http://www.fossil-scm.org/index.html (~6MB) D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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] Reducing SQLite Memory footprint(!)
1. Is anyone else on the list using Visual Studio 2005? It would be handy to see if they got similar results with the test application or not, that would rule out the build environment to an extent. 2. And the other thing to try would be if anyone has a fairly meaty test database they don't mind sharing that I could fling at my test application to try and rule out the data? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Thursday, August 21, 2008 4:23 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) Nicolas Williams wrote: > > I thought the DB was 9MB; forgive me for wasting your time then. If > it's 17.4MB then the memory usage seems a lot more reasonable. Daniel, the OP's, database is 9 MB. I don't have his database file, but I do have his test code. I used a database of my own that is a similar size along with his test code to do my tests. In my tests sqlite behaves as expected. Daniel is seeing much higher memory usage reported from sqlite itself using the same version of sqlite, the same test code, and the same OS. I see a memory usage of about 18 MB for a database copied from a file that is 17.4 MB (1K pages) or 12.2 MB (4K pages). I get an expansion factor of 1.03 or 1.48. Daniel is seeing memory usage of 22.2 MB for a database copied from a file that is 9 MB. Daniel gets an expansion factor of 2.47. This seems high. Since the major difference seems to be the database file we are copying, I would like to repeat his test with his database file if possible. If not possible (perhaps the data is proprietary or personal), then it might make sense to see what factors effect this memory expansion ratio. I was surprised by the magnitude of the change in the size of my database file by simply changing the page size. I also tried to change the page size used for the memory database, but that had no effect (Which is not what I expected, perhaps the page size pragma is ignored for memory databases). Changing the cache size reduced the highwater memory requirement, but didn't change the memory required to hold the database after the copy was completed. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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] Reducing SQLite Memory footprint(!)
Ok so after reading your feedback I tried: 1. "PRAGMA cache_size =10" no change in memory usage. 2. "PRAGMA page_size = 4096" no change in memory usage. I'm doing both those queries (in C++) after the 'sqlite3_open( ":memory:", &m_pDataBase );' in my test but before the database file is attached or anything is copied or created. The rebuilt database file is 7.46 MB and the memory usage I'm seeing is now: 22.20 MB with a high water of 24.55 MB as reported by the sqlite_memory_* functions. I'm not using the amalgamation version of the pre-processed source; I'm using the individual source files of 3.6.1 on Windows XP with Visual Studio 2005. I'm afraid I can't give you a copy of the test database as it's a drop from a live product, could it be using the third party SQLite Analyzer application to import from excel be the issue? Are there any other tools from importing from a .xls to a SQLite database (converting each sheet to a table)? I just tried a "vacuum" after I detach the database from file and that didn't reduce the memory usage either but it did double the high water mark which after reading the documentation sounds about right for making a temporary copy. How do I rebuild a database file for another page size or did the pragma do that already? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Nicolas Williams Sent: Thursday, August 21, 2008 3:13 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) On Thu, Aug 21, 2008 at 04:02:32PM -0600, Dennis Cote wrote: > I built a copy of my test database using a 4096 byte page size and it > reduced the database file size from 17.5 MB to 12.2 MB. When I repeat > the tests using this database file I get the same 18102 KB of memory > used, but a slightly higher highwater mark of 26418 KB used. Again this > was all done with the default page cache size. > > Note, I also tried to vacuum the original file to see if there were a > lot of unused pages in the original 17.5 MB file. After the vacuum it > was reduced to only 17.4 MB, so there were very few free pages in the > database. This database just fits much better on the larger 4K pages. I thought the DB was 9MB; forgive me for wasting your time then. If it's 17.4MB then the memory usage seems a lot more reasonable. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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] Reducing SQLite Memory footprint(!)
Thanks Dennis,
I just tried with rebuilt data from SQLite Analyzer in SQLite 3.6.1, I'm
still seeing a memory usage that is roughly three times the size of the
source database file, looking at your changes to my test there doesn't
seem to be any fixes that would resolve that.
I can see the memory being released when I close the SQLite database in
the teardown stage of my test, so I'm fairly sure the memory is being
used by SQLite and the built in memory profiling would seem to support
that. I haven't had to make any changes locally to get the PC version
of 3.6.1 compiling so I don't think that is the issue, could it be some
sort of configuration or library issue? I'm building in Visual Studio
2005 SP1.
Daniel
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote
Sent: Wednesday, August 20, 2008 3:45 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Reducing SQLite Memory footprint(!)
Brown, Daniel wrote:
> I just upgraded to the latest version (3.6.1) of the pre-processed C
> source code from the website, running the test again gives me similar
> results of 22.2 MB used and 24.55 MB high water from the same 9 MB
file.
> Is there any way it could be the file that is causing the extra memory
> usage? I'll try rebuilding it next.
>
I just tried your test code with sqlite 3.6.1 amalgamation source on Win
XP and I get the following output from a 17.5 MB (18,362,368 bytes)
database file.
Entries.db Used: 18049 KB High: 20357 KB
I had to make a few changes to your code to get it to run. I have copied
the modified code below. This was built with Dev-Cpp using GCC 3.4.2.
There must be some other issue with your program that is causing the
inflated memory usage you are seeing.
Dennis Cote
#include
#include
#include
using namespace std;
int main(int argc, char *argv[])
{
sqlite3* pDataBase = NULL;
const char* ptail = NULL;
sqlite3_stmt* pstatement = NULL;
int result = -1;
int cmdSize = 0;
const int cmdBufferSize = 1024;
char cmdBuffer[cmdBufferSize];
const char* pdatabaseName = "file_db";
const char* pfilename = argv[1];
sqlite3_open( ":memory:", &pDataBase );
// create the attach command
cmdSize = sprintf( cmdBuffer, "ATTACH DATABASE '%s' AS %s",
pfilename,
pdatabaseName );
// attach the on-disk database with ATTACH filename.db AS
filename
result = sqlite3_exec( pDataBase, cmdBuffer, NULL, NULL, NULL );
// You can enumerate all tables in a your on-disk-file in the
mentioned
scenario by
// doing a "SELECT tbl_name FROM filename.sqlite_master WHERE
type =
'table'".
cmdSize = sprintf( cmdBuffer, "SELECT tbl_name FROM
%s.sqlite_master
WHERE type = 'table'", pdatabaseName );
// prepare the statement
result = sqlite3_prepare_v2( pDataBase, cmdBuffer, cmdSize,
&pstatement, &ptail);
while( sqlite3_step( pstatement) == SQLITE_ROW)
{
// Then do a CREATE TABLE tableName AS SELECT * FROM
filename.tableName On each table in the file,
// thus creating an in-memory copy of the DB and having
done a select
on each table (i.e. you'll see how // much cache in
memory will be
used, etc.)
// get the table name
const unsigned char* pname = sqlite3_column_text(
pstatement, 0);
// construct the command
cmdSize = sprintf( cmdBuffer, "CREATE TABLE %s AS SELECT
* FROM
%s.%s", pname, pdatabaseName, pname );
result = sqlite3_exec( pDataBase, cmdBuffer, NULL, NULL,
NULL );
}
sqlite3_finalize(pstatement);
// detach the attached database to leave just the in memory
database
cmdSize = sprintf( cmdBuffer, "DETACH DATABASE %s",
pdatabaseName );
result = sqlite3_exec( pDataBase, cmdBuffer, NULL, NULL, NULL );
sqlite_int64 memHigh = sqlite3_memory_highwater(0);
sqlite_int64 memUsed = sqlite3_memory_used();
printf("%s Used: %d KB High: %d KB\n", pfilename,
(int)(memUsed/1024), (int)(memHigh/1024));
sqlite3_close( pDataBase );
system("PAUSE");
return EXIT_SUCCESS;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/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] Reducing SQLite Memory footprint(!)
Perhaps, when is the next release due? I'd be interested to see the differences, if an upgrade reduces memory overhead that significantly it would be most excellent :) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Wednesday, August 20, 2008 2:43 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) On Aug 20, 2008, at 5:30 PM, Brown, Daniel wrote: > I just upgraded to the latest version (3.6.1) of the pre-processed C > source code from the website, running the test again gives me similar > results of 22.2 MB used and 24.55 MB high water from the same 9 MB > file. > Is there any way it could be the file that is causing the extra memory > usage? I'll try rebuilding it next. > > My tests were based on CVS HEAD, which contains a newly rewritten page cache, and hence entirely new code for managing in-memory databases. Perhaps some kind of bug has been fixed since 3.6.1 that causes it to use less memory. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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] Reducing SQLite Memory footprint(!)
I just upgraded to the latest version (3.6.1) of the pre-processed C source code from the website, running the test again gives me similar results of 22.2 MB used and 24.55 MB high water from the same 9 MB file. Is there any way it could be the file that is causing the extra memory usage? I'll try rebuilding it next. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brown, Daniel Sent: Wednesday, August 20, 2008 1:50 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) That is interesting, all that I've done to the source code locally is added some C++ casts to get rid of compiler warnings, the C++ source I posted earlier is the complete test I'm using, it is running on Windows XP and its SQLite 3.5.1 from the pre-processed source code downloaded from the site. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Wednesday, August 20, 2008 12:14 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) On Aug 20, 2008, at 2:56 PM, Brown, Daniel wrote: > Thank you, I imported our data from the source Excel file (.xls) using > the third party SQLite Analyzer application > (http://www.kraslabs.com/sqlite_analyzer.php) if that makes any > difference? > > The size of the SQLite database on disc is 9,396,224 bytes so I was > surprised when the memory usage ended up about three times that > amount, > I assumed some sort of packing and unpacking could be going on but > from > your comments I gather the memory usage is meant to be approximately > the > size of the file on disc? > There is some extra overhead in memory. But not 3x. At least, not unless you are doing a big transaction or vacuuming the database or something like that. I'm running experiments now. My memory usage is about (1.15*disk + 66184). In other words, I'm seeing the in-memory database use about 15% more space than the on-disk database. I'm not sure what you are doing to get 3x memory usage. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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] Reducing SQLite Memory footprint(!)
That is interesting, all that I've done to the source code locally is added some C++ casts to get rid of compiler warnings, the C++ source I posted earlier is the complete test I'm using, it is running on Windows XP and its SQLite 3.5.1 from the pre-processed source code downloaded from the site. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Wednesday, August 20, 2008 12:14 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) On Aug 20, 2008, at 2:56 PM, Brown, Daniel wrote: > Thank you, I imported our data from the source Excel file (.xls) using > the third party SQLite Analyzer application > (http://www.kraslabs.com/sqlite_analyzer.php) if that makes any > difference? > > The size of the SQLite database on disc is 9,396,224 bytes so I was > surprised when the memory usage ended up about three times that > amount, > I assumed some sort of packing and unpacking could be going on but > from > your comments I gather the memory usage is meant to be approximately > the > size of the file on disc? > There is some extra overhead in memory. But not 3x. At least, not unless you are doing a big transaction or vacuuming the database or something like that. I'm running experiments now. My memory usage is about (1.15*disk + 66184). In other words, I'm seeing the in-memory database use about 15% more space than the on-disk database. I'm not sure what you are doing to get 3x memory usage. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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] Reducing SQLite Memory footprint(!)
Thank you, I imported our data from the source Excel file (.xls) using the third party SQLite Analyzer application (http://www.kraslabs.com/sqlite_analyzer.php) if that makes any difference? The size of the SQLite database on disc is 9,396,224 bytes so I was surprised when the memory usage ended up about three times that amount, I assumed some sort of packing and unpacking could be going on but from your comments I gather the memory usage is meant to be approximately the size of the file on disc? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Wednesday, August 20, 2008 11:44 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) On Aug 20, 2008, at 2:22 PM, Brown, Daniel wrote: > sqlite3_memory_highwater() ~ 25673060 > sqlite3_memory_used() ~ 23222709 > OK. I'll have a look D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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] Reducing SQLite Memory footprint(!)
sqlite3_memory_highwater() ~ 25673060
sqlite3_memory_used() ~ 23222709
I'm doing the following in C++ (I test all return codes but removed the tests
to save bandwidth):
const char* ptail = NULL;
sqlite3_stmt* pstatement = NULL;
int result = -1;
int cmdSize = 0;
const int cmdBufferSize = 1024;
char cmdBuffer[cmdBufferSize];
sqlite3_open( ":memory:", &pDataBase );
// create the attach command
cmdSize = sprintf( cmdBuffer, "ATTACH DATABASE '%s' AS %s", pfilename,
pdatabaseName );
// attach the on-disk database with ATTACH filename.db AS filename
result = sqlite3_exec( pDataBase, cmdBuffer, NULL, NULL, NULL );
// You can enumerate all tables in a your on-disk-file in the mentioned
scenario by
// doing a "SELECT tbl_name FROM filename.sqlite_master WHERE type =
'table'".
cmdSize = sprintf_s( cmdBuffer, "SELECT tbl_name FROM %s.sqlite_master
WHERE type = 'table'", pdatabaseName );
// prepare the statement
result = sqlite3_prepare_v2( pDataBase, cmdBuffer, cmdSize,
&pstatement, &ptail);
while( sqlite3_step( pstatement) == SQLITE_ROW)
{
// Then do a CREATE TABLE tableName AS SELECT * FROM
filename.tableName On each table in the file,
// thus creating an in-memory copy of the DB and having done a
select on each table (i.e. you'll see how// much cache
in memory will be used, etc.)
// get the table name
const unsigned char* pname = sqlite3_column_text( pstatement,
0);
// construct the command
cmdSize = sprintf( cmdBuffer, "CREATE TABLE %s AS SELECT * FROM
%s.%s", pname, pdatabaseName, pname );
result = sqlite3_exec( pDataBase, cmdBuffer, NULL, NULL, NULL );
}
sqlite3_finalize(pstatement);
// detach the attached database to leave just the in memory database
cmdSize = sprintf( cmdBuffer, "DETACH DATABASE %s", pdatabaseName );
result = sqlite3_exec( pDataBase, cmdBuffer, NULL, NULL, NULL );
sqlite_int64 memHigh = sqlite3_memory_highwater(0);
sqlite_int64 memUsed = sqlite3_memory_used();
printf("%s %d KB High %d KB", pfilename, (memUsed/1024),
(memHigh/1024));
sqlite3_close( pDataBase );
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Wednesday, August 20, 2008 11:00 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Reducing SQLite Memory footprint(!)
On Aug 20, 2008, at 1:53 PM, Brown, Daniel wrote:
> Looking in process explorer on XP after the disc database detached
> should a memory size change of 28 MB of RAM in the test application, I
> assumed this was the size of the database in memory.
That would be the peak memory usage by the application. It is not at
all clear to me that SQLite was using all 28 MB. What does
sqlite3_memory_highwater() tell you?
D. Richard Hipp
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/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] Reducing SQLite Memory footprint(!)
Looking in process explorer on XP after the disc database detached should a memory size change of 28 MB of RAM in the test application, I assumed this was the size of the database in memory. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Wednesday, August 20, 2008 10:44 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) On Aug 19, 2008, at 6:24 PM, Brown, Daniel wrote: > SQLite is very memory > intensive compared to our current solution (although SQLite is faster > and more feature rich), e.g. 9MB for our current solution versus 28 MB > for SQLite with the same source data. Where did you get the 28MB figure? The sqlite3_analyzer output you posted tells me that the total database size is a little over 9MB, not 28MB. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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] Reducing SQLite Memory footprint(!)
.. 7 Unused bytes on primary pages. 139119.4% Unused bytes on overflow pages 0 Unused bytes on all pages. 139119.4% -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Tuesday, August 19, 2008 3:43 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) On Aug 19, 2008, at 6:24 PM, Brown, Daniel wrote: > Good afternoon list, > > Are there any known techniques for reducing SQLite's memory footprint? Construct your database into a file (say "test.db"). Then run the sqlite3_analyzer utility (available for download on the SQLite website) over that file and post the results. The sqlite3_analyzer utility will give us additional information that might suggest ways of reducing the size of the database file. See also http://www.hwaci.com/sw/sqlite/prosupport.html#compress D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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] Reducing SQLite Memory footprint(!)
Good afternoon list, Are there any known techniques for reducing SQLite's memory footprint? I am currently evaluating SQLite as a possible replacement to our current proprietary database solution. However SQLite is very memory intensive compared to our current solution (although SQLite is faster and more feature rich), e.g. 9MB for our current solution versus 28 MB for SQLite with the same source data. Due to our platforms limitations we need to be able to run our database solution completely in RAM in our worst case environment we don't have any writable storage available: this prevents the use of SQLite's paging facility except for read only tables. Our current solution achieves its small memory footprint by bit packing where we specify the minimum and maximum values for each column the system then uses the smallest number of bits possible to represent integer, bit and float values, strings are the exception which we use UTF-8 to store. So is there any existing techniques I can leverage to reduce the memory footprint for SQLite? Ideally I'd like to be able to stay inside our original memory footprint of 9 MB, which I think could be achievable with a combination of paging our read only tables from disc and keeping our writable tables 100% in memory with some sort of compression being applied. Cheers, Daniel Brown | Software Engineer "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory profiling SQLite database
Ah excellent, if I am wanting to build the analyzer from source code which C files are required? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Friday, August 15, 2008 3:38 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Memory profiling SQLite database On Aug 15, 2008, at 5:58 PM, Brown, Daniel wrote: > Good afternoon List, > > Is there any way at runtime to find out how much memory each of my > tables in my database are using, other than loading each table > individually into a :memory: database and comparing the before and > after > values? Generate an equivalent database in a disk file. Then run the disk file through the sqlite3_analyzer utility which you can download from the website. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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] Memory profiling SQLite database
Good afternoon List, Is there any way at runtime to find out how much memory each of my tables in my database are using, other than loading each table individually into a :memory: database and comparing the before and after values? Any sort of ability to generate a memory usage breakdown would be great. Cheers, Daniel Brown "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Loading a existing database 100% into memory
Ok I've moved the test database into the same directory as my C++ test application (I'm on windows) to remove the directory reference, my queries now look like: "ATTACH DATABASE 'Test01.db' AS Test01" - returns SQLITE_OK "SELECT tbl_name FROM Test01.sqlite_master WHERE type = 'table'" - returns SQLITE_ERROR I'm still getting the same error message as before 'no such table Test01.sqlite_master', I've opened the test database in SQLite Analyzer and it seems valid. If I take the ' away in the first query I get SQLITE_ERROR with the message 'No such column: Test01.db' if that helps? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brown, Daniel Sent: Thursday, August 14, 2008 4:05 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Loading a existing database 100% into memory Sorry I should have said, I'm using these calls in C++ on windows. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Thursday, August 14, 2008 1:45 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Loading a existing database 100% into memory On Thu, 14 Aug 2008, D. Richard Hipp wrote: > On Aug 14, 2008, at 4:18 PM, Brown, Daniel wrote: > >> Hello Stefan, >> >> I'm trying to use the code snippet you suggested but when I try to >> query >> the master table of the attached database I get and error with the >> following message: >> "SQL error: no such table: 'test.sqlite_master'; >> >> I am running the following queries: >> 1. "ATTACH DATABASE 'data\\test.db' AS test" - this seems to succeed >> (no >> error code). >> 2. "SELECT tbl_name FROM test.sqlite_master' WHERE type = 'table'" - >> this one is failing. >> > > Works when I try it. > > But I'm not running on windows. Do you really need two backslashes in > the filename? Should there be just a single backslash? You do not > quote backslashes in SQL strings as you do in C. > > But I'm not running on windows. Do you really need two backslashes in > the filename? Should there be just a single backslash? You do not Backslashes are illegal in a Windows filenames, except as folder delimiters, so "data\\test.db" is not a valid Windows filename. Hence, if this is on Windows, the ATTACH must be failing. It must be 'data\test.db'. Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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] Loading a existing database 100% into memory
Sorry I should have said, I'm using these calls in C++ on windows. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Thursday, August 14, 2008 1:45 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Loading a existing database 100% into memory On Thu, 14 Aug 2008, D. Richard Hipp wrote: > On Aug 14, 2008, at 4:18 PM, Brown, Daniel wrote: > >> Hello Stefan, >> >> I'm trying to use the code snippet you suggested but when I try to >> query >> the master table of the attached database I get and error with the >> following message: >> "SQL error: no such table: 'test.sqlite_master'; >> >> I am running the following queries: >> 1. "ATTACH DATABASE 'data\\test.db' AS test" - this seems to succeed >> (no >> error code). >> 2. "SELECT tbl_name FROM test.sqlite_master' WHERE type = 'table'" - >> this one is failing. >> > > Works when I try it. > > But I'm not running on windows. Do you really need two backslashes in > the filename? Should there be just a single backslash? You do not > quote backslashes in SQL strings as you do in C. > > But I'm not running on windows. Do you really need two backslashes in > the filename? Should there be just a single backslash? You do not Backslashes are illegal in a Windows filenames, except as folder delimiters, so "data\\test.db" is not a valid Windows filename. Hence, if this is on Windows, the ATTACH must be failing. It must be 'data\test.db'. Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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] Loading a existing database 100% into memory
Hello Stefan, I'm trying to use the code snippet you suggested but when I try to query the master table of the attached database I get and error with the following message: "SQL error: no such table: 'test.sqlite_master'; I am running the following queries: 1. "ATTACH DATABASE 'data\\test.db' AS test" - this seems to succeed (no error code). 2. "SELECT tbl_name FROM test.sqlite_master' WHERE type = 'table'" - this one is failing. Do you have any ideas? I tried looking at the docs but there doesn't seem to be much about it, from what I've read the queries appear to be correct. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Szomraky, Stefan Sent: Thursday, August 07, 2008 12:02 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Loading a existing database 100% into memory What do you mean by loading it into memory? If you want to dump the on-disk tables into memory and also want to check the memory footprint used for caching try this: Open the :memory: database and attach the on-disk database with ATTACH filename.db AS filename Then do a CREATE TABLE tableName AS SELECT * FROM filename.tableName On each table in the file, thus creating an in-memory copy of the DB and having done a select on each table (i.e. you'll see how much cache in memory will be used, etc.) You can enumerate all tables in a your on-disk-file in the mentioned scenario by doing a "SELECT tbl_name FROM filename.sqlite_master WHERE type = 'table'". Best regards, Stefan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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] Loading a existing database 100% into memory
What I am wanting to work out is how much memory SQLite uses if we have
the entire database in RAM only, the reason I'd like to find this out is
that there is some concern here about SQLite paging to disc at
inopportune moments (we're on an embedded system with a lot of other
random disc access going on) so ideally I'd like the option to be able
to run completely from RAM after the initial load from disc, so that if
paging proves too costly we can sacrifice RAM to escape the paging
issue.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Stephen
Oberholtzer
Sent: Wednesday, August 06, 2008 5:17 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Loading a existing database 100% into memory
On Wed, Aug 6, 2008 at 6:26 PM, Brown, Daniel <[EMAIL PROTECTED]> wrote:
> Good afternoon list,
>
> I would like to load my current database file completely into memory,
> mostly as an experiment to check SQLite's maximum memory footprint,
> however searching through the documentation I can only find references
> about how to create new databases that are completely memory resident.
> Is there a way to do this? I'm currently using the SQlite console
> application for my testing if that makes a difference.
What, exactly, is it you're after? I can load a SQLite database 100%
into
memory quite quickly:
int fd = open("sqlitedb.dat");
struct stat info;
fstat(fd, &info);
char *buf = malloc(info.st_size);
read(fd, buf, info.st_size);
I find it extremely unlikely that this is what you want (although it
might
be an interesting academic exercise to make a VFS port of SQLite that
uses
memory arrays for read/write ops.)
At the other end of the spectrum, you could just dump the entire
database on
disk and then insert all the data into a :memory: database. However,
this
doesn't seem like it would be very useful, either.
This sounds like an XY problem. What are you really trying to
accomplish?
What constraints are preventing you from simply using an on-disk
database?
--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/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] Loading a existing database 100% into memory
Good afternoon list, I would like to load my current database file completely into memory, mostly as an experiment to check SQLite's maximum memory footprint, however searching through the documentation I can only find references about how to create new databases that are completely memory resident. Is there a way to do this? I'm currently using the SQlite console application for my testing if that makes a difference. Cheers, Daniel Brown "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Loading from Read Only storage and saving changes to a separate storage unit
Morning List, Another morning another question, what sort of facilities does SQLite provide for Loading from a read only storage location and then saving changes to a separate read/write storage unit? I know I can open a database file with a read only flag but how would I go about managing the changes to that database and storing them to a separate storage device? All I could think of is creating a separate read/write database on the read/write storage but short of duplicating tables between the two databases I can't think of a simple mechanism for organising the two tables so only the changes to the original read only table are stored out into the read/write table with out adding allot of data management logic on the code side. I'd imagine there is some mechanism for dealing with this kind of storage layout if SQLite is used in embedded systems as the sort of storage arrangement I'm describing is fairly common but I'm unsure as to the terms to search in the documentation for as every product seems to use different terms for that sort of facility. Any help or pointers to the correct bit of documentation to look at would be most appreciated! Cheers, Daniel Brown "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cursors in SQlite
Thanks Igor, It occurred to me that sqlite3_step is similar to a cursor about thirty minutes after I sent my email, while I was driving home. That article was very interesting; in our current custom database solution we have cursors but the rest of our solution is so limited compared to SQLite I can't see that not having a comparable cursor will be an issue as we are not doing anything with our cursors that we couldn't do with sqlite3_step. Daniel -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik Sent: Tuesday, July 22, 2008 5:25 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Cursors in SQlite Brown, Daniel <[EMAIL PROTECTED]> wrote: > Does SQLite support/implement cursors of any form sqlite3_step is, in some sense, a forward-only cursor. You might also find this interesting: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor 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] Cursors in SQlite
Good Evening list, Does SQLite support/implement cursors of any form, I've been searching through the documentation about this but I suspect either cursor is the wrong term for SQLite or they are either not supported or implemented. Could anyone clear this up for me? Cheers, Daniel Brown "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Taking precompiled test source files from the online repository
Morning List, As I mentioned yesterday our local build system doesn't support TCL, so I've been taking the appropriate versions of files (we're on 3.5.1 currently) from the online repository for things like the SQLite3 console. I'd quite like to get as many of the original c tests as possible running in our test harness to make sure we don't break anything and looking in the online repository I can see source files that look like tests. Is there likely to be any technical issues I should watch out for if I take copies those files from the repository to set-up a test harness here for our 3.5.1 build? Or do the C tests require TCL too? Cheers, Daniel Brown "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3.exe preprocessed source?
Never mind, I managed to figure out the file I need from the make file in the source repository. For reference the file seems to be shell.c -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brown, Daniel Sent: Thursday, July 17, 2008 3:26 PM To: sqlite-users@sqlite.org Subject: [sqlite] SQLite3.exe preprocessed source? Hello List, Is there anywhere I can get the pre-processed C source for the SQLite3.exe console application? I don't have tcl/make set-up here meaning I can't generate the source myself so I've just been working with the pre-processed source files available on the website but these don't seem to include the sqlite3 application which I'd really like to have. I'm specifically interested in the source of the application for SQLite 3.5.1 and 3.6.0. Cheers, Daniel Brown "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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] SQLite3.exe preprocessed source?
Hello List, Is there anywhere I can get the pre-processed C source for the SQLite3.exe console application? I don't have tcl/make set-up here meaning I can't generate the source myself so I've just been working with the pre-processed source files available on the website but these don't seem to include the sqlite3 application which I'd really like to have. I'm specifically interested in the source of the application for SQLite 3.5.1 and 3.6.0. Cheers, Daniel Brown "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
RE: [sqlite] Testing SQLite
Ah, I just figured out why I thought I've not got the tests. I've been using the pre-processed C code version of SQLite (as I'm working in Visual Studio on Windows), I guess I'll need to get the full SQLite package building with make then to get the test functionality instead of using the pre-processed source? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 13 December 2007 10:14 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Testing SQLite "Brown, Daniel" <[EMAIL PROTECTED]> wrote: > Morning List, > > I've just started experimenting with SQLite to see if I can replace > our current custom embedded database solution with it and trying to > port SQLite to some of our embedded platforms. Are there are any > testing frameworks (unit tests would be great) for SQLite? I'd like > to be able to automatically verify I've not broken anything in my > porting or tinkering, > > I've had a look round the website but I've not managed to find > anything there. > Something like 65% of the SQLite source code in the standard download is devoted exclusively to testing. Only 35% of the code actually becomes part of a production build. The test scripts are written in TCL. You'll need to have a TCL implementation available for your target in order to run the tests. Assuming you have TCL installed, just do: make fulltest and a bunch of tests will run that given on the order of 98% test coverage. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Testing SQLite
Morning List, I've just started experimenting with SQLite to see if I can replace our current custom embedded database solution with it and trying to port SQLite to some of our embedded platforms. Are there are any testing frameworks (unit tests would be great) for SQLite? I'd like to be able to automatically verify I've not broken anything in my porting or tinkering, I've had a look round the website but I've not managed to find anything there. Thanks in advance, Daniel Brown Software Engineer "The best laid schemes o' mice an' men, gang aft agley"
