[sqlite] Testing SQLite

[Brown, Daniel]

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"

RE: [sqlite] Testing SQLite

[Brown, Daniel]

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] SQLite3.exe preprocessed source?

[Brown, Daniel]

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] SQLite3.exe preprocessed source?

[Brown, Daniel]

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] Taking precompiled test source files from the online repository

[Brown, Daniel]

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

[sqlite] Cursors in SQlite

[Brown, Daniel]

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

Re: [sqlite] Cursors in SQlite

[Brown, Daniel]

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] Loading from Read Only storage and saving changes to a separate storage unit

[Brown, Daniel]

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

[sqlite] Loading a existing database 100% into memory

[Brown, Daniel]

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

Re: [sqlite] Loading a existing database 100% into memory

[Brown, Daniel]

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

Re: [sqlite] Loading a existing database 100% into memory

[Brown, Daniel]

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

[Brown, Daniel]

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] Memory profiling SQLite database

[Brown, Daniel]

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] Memory profiling SQLite database

[Brown, Daniel]

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] Reducing SQLite Memory footprint(!)

[Brown, Daniel]

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] Reducing SQLite Memory footprint(!)

[Brown, Daniel]

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

Re: [sqlite] Reducing SQLite Memory footprint(!)

[Brown, Daniel]

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

[Brown, Daniel]

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:",  );

// 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, 
, );

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

[Brown, Daniel]

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

[Brown, Daniel]

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

[Brown, Daniel]

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

[Brown, Daniel]

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

[Brown, Daniel]

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:",  );

// 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, 
, );

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

[Brown, Daniel]

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:", _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(!)

[Brown, Daniel]

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

[Brown, Daniel]

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

[Brown, Daniel]

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

[Brown, Daniel]

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

[Brown, Daniel]

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

[sqlite] sqlite3_stmt declaration

[Brown, Daniel]

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] sqlite3_stmt declaration

[Brown, Daniel]

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] GCC -ffast-math safe version of sqlite3IsNaN

[Brown, Daniel]

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] GCC -ffast-math safe version of sqlite3IsNaN

[Brown, Daniel]

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

Re: [sqlite] GCC -ffast-math safe version of sqlite3IsNaN

[Brown, Daniel]

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

[sqlite] Dates & SQLite

[Brown, Daniel]

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] Vista frustrations

[Brown, Daniel]

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] VFS implementation guidance

[Brown, Daniel]

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] VFS implementation guidance

[Brown, Daniel]

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] Distinguishing between sqlite3_stmts

[Brown, Daniel]

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] Distinguishing between sqlite3_stmts

[Brown, Daniel]

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] Which TCL distrabution for Windows XP for running SQLite tests?

[Brown, Daniel]

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

[sqlite] Generating CRC values for tables

[Brown, Daniel]

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] Loading database from memory buffer

[Brown, Daniel]

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

Re: [sqlite] Loading database from memory buffer

[Brown, Daniel]

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

Re: [sqlite] Journal files

[Brown, Daniel]

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] Journal files

[Brown, Daniel]

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

[Brown, Daniel]

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

[sqlite] SQLite performance woe

[Brown, Daniel]

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] SQLite performance woe

[Brown, Daniel]

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

Re: [sqlite] SQLite performance woe

[Brown, Daniel]

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

[Brown, Daniel]

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 indicies and does EXPLAIN QUERY PLA

Re: [sqlite] SQLite performance woe

[Brown, Daniel]

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

[sqlite] Converted sqlite3VdbeSerialTypeLen into a macro

[Brown, Daniel]

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

[Brown, Daniel]

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] SQLite version 3.6.10: Memory savings with large pages

[Brown, Daniel]

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

[sqlite] Saving and loading SQLite pages from a buffer

[Brown, Daniel]

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

Re: [sqlite] Saving and loading SQLite pages from a buffer

[Brown, Daniel]

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

Re: [sqlite] Saving and loading SQLite pages from a buffer

[Brown, Daniel]

-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

[Brown, Daniel]

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

[sqlite] Calculating the size of a backup

[Brown, Daniel]

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] Calculating the size of a backup

[Brown, Daniel]

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