Re: [sqlite] sqlite3_trace

[Alex Katebi]

Boy I was off on that one. The stmt is not going to help my case. The users
context that is already there can help me.
Sorry,
-Alex

On Wed, Aug 20, 2008 at 8:52 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:

> Hi All,
>
> I am using the sqlite3_trace to track down a bug. It is lacking a vital
> information that is needed for tracing. The stmt handle is necessary in
> order to identify related statements. Notice the sqlite3_stmt that I have
> added below.
>
> void *sqlite3_trace(sqlite3*, void(*xTrace)(void*,const char*, 
> sqlite3_stmt*), void*);
>
> Thankd,
> -Alex
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_trace

[Alex Katebi]

Hi All,

I am using the sqlite3_trace to track down a bug. It is lacking a vital
information that is needed for tracing. The stmt handle is necessary in
order to identify related statements. Notice the sqlite3_stmt that I have
added below.

void *sqlite3_trace(sqlite3*, void(*xTrace)(void*,const char*,
sqlite3_stmt*), void*);

Thankd,
-Alex
___
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(!)

[Dennis Cote]

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

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

[D. Richard Hipp]

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

Re: [sqlite] pragma table_info on a table in attached db

[Kees Nuyt]

On Thu, 21 Aug 2008 02:18:39 +0530, Mrinal wrote:

>>As Dennis said, that looks like a bug.
>
> Created a new ticket (number 3320:
> http://www.sqlite.org/cvstrac/tktview?tn=3320)
> which has been fixed now.

Wow, that's fast.

>- Mrinal.
-- 
  (  Kees Nuyt
  )
c[_]
___
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]

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] pragma table_info on a table in attached db

[Mrinal Kant]

>As Dennis said, that looks like a bug.

Created a new ticket (number 3320:
http://www.sqlite.org/cvstrac/tktview?tn=3320) which has been fixed
now.

- Mrinal.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problems building for VxWorks...

[Alex Katebi]

Write the missing functions with using the equivalent VxWorks functions.

example:

int exit(int a)
{
vxworks_exit(a);
}

On Wed, Aug 20, 2008 at 4:16 AM, Kershaw, Anthony (UK) <
[EMAIL PROTECTED]> wrote:

>
> Hi,
> Have been trying to compile (and run) the amalgamated version of sqlite
> 3.6.1 for VxWorks   5.5,
> which is proving quite trouble-some.
>
> I am currently using these switches
> -DSQLITE_OMIT_BUILTIN_TEST
> -DSQLITE_TEMP_STORE=3
> -DSQLITE_HOMEGROWN_RECURSIVE_MUTEX
> -DSQLITE_OMIT_AUTOVACUUM
> -DSQLITE_OMIT_LOAD_EXTENSION
> -DSQLITE_OMIT_LOCALTIME
> -DSQLITE_OMIT_TCL_VARIABLE
> -DNO_GETTOD
>
> Which will allow it to compile, but there are missing symbols, mainly
> because they don't occur in vxworks, the following functions don't
> exist.
> fctrl
> dup
> fsync
> access
> getpid
>
> Any work arounds people could think of would be greatly appreciated. I
> appreciate this heavily hits the locking mechanism code within sqlite...
>
> Regards,
>  Ant
>
>
>
> 
> This email and any attachments are confidential to the intended
> recipient and may also be privileged. If you are not the intended
> recipient please delete it from your system and notify the sender.
> You should not copy it or use it for any purpose nor disclose or
> distribute its contents to any other person.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma table_info on a table in attached db

[Kees Nuyt]

On Wed, 20 Aug 2008 06:33:17 +0530, Mrinal wrote:

>>Try:
>>PRAGMA [database.]table_info(table-name);
>
>>  (  Kees Nuyt
>
>Thankyou Kees for the solution

yw.

>But, I am facing another problem 

[...]

As Dennis said, that looks like a bug.
I just confirmed SQLite 3.6.0 behaves the same way.
Apparently temp is only treated like an attached database
for specific (syntactical) purposes. 
Its schema is maintained in the main database, in the
sqlite_temp_master table. 

In my view, it's not very common to give two different
tables in the same logical database (main) the same name.

sqlite_version():3.6.0
.bail off

create table trial (col_main);
select * from sqlite_master;
table|trial|trial|2|CREATE TABLE trial (col_main)
pragma main.table_info(trial);
0|col_main||0||0

create temp table trial (col_temp);
select * from sqlite_temp_master;
table|trial|trial|2|CREATE TABLE trial (col_temp)
pragma main.table_info(trial);
0|col_temp||0||0

attach 'tmp/test_34.db3' as db34;
create table db34.trial (db34_main);
select * from db34.sqlite_master;
table|trial|trial|2|CREATE TABLE trial (db34_main)
pragma db34.table_info(trial);
0|db34_main||0||0

.databases
seq  name file ---  ---
-
0main \opt\research\tmp\test_33.db3
1temp  
2db34 \opt\research\tmp\test_34.db3

pragma main.table_info(trial);
0|col_temp||0||0

pragma temp.table_info(trial);
0|col_temp||0||0

pragma db34.table_info(trial);
0|db34_main||0||0

-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[D. Richard Hipp]

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

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

[D. Richard Hipp]

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

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

[Jeffrey Becker]

More over, the memory usage reported by process explorer is subject to
the vagrancies of the windows memory allocator.  Generally the memory
usage you see reported in tools is quite a bit higher than the actual
memory usage of your application.  First off windows manages memory in
fixed size chunks so if you ask for X bytes of memory, windows
allocates n contiguous chunks such that n*chunk_size >= x.  However
windows just reports ~ # of allocated chunks * chunk_size as your
memory usage (if you use the right api).  More over most applications
simply report the total size of an application's "working set" which
includes lots of pages of memory that are potentially shared across
many applications.

On Wed, Aug 20, 2008 at 2:00 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
>
> 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]

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

[D. Richard Hipp]

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

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

[D. Richard Hipp]

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

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

[Brown, Daniel]

SQLite Analyser results, I only included the eight largest tables the
others are all tiny:

/** Disk-Space Utilization Report For test.db
*** As of 2008-Aug-19 16:10:41

Page size in bytes 1024  
Pages in the whole file (measured) 9176  
Pages in the whole file (calculated).. 9176  
Pages that store data. 9176   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.. 35
Number of indices. 18
Number of named indices... 18
Automatically generated indices... 0 
Size of the file in bytes. 9396224   
Bytes of user payload stored.. 7243721 77.1% 

*** Page counts for all tables with their indices 

TABLE_1... 504555.0% 
TABLE_2... 161517.6% 
TABLE_3... 853  9.3% 
TABLE_4... 733  8.0% 
TABLE_5... 285  3.1% 
TABLE_6... 215  2.3% 
TABLE_7... 187  2.0% 
TABLE_8... 84   0.92% 
TABLE_9... 38   0.41% 
SQLITE_MASTER. 29   0.32% 
TABLE_10.. 21   0.23% 
TABLE_11.. 17   0.19% 
TABLE_12.. 14   0.15% 
TABLE_13.. 70.076% 
TABLE_14.. 40.044% 
TABLE_15.. 40.044% 
TABLE_16.. 20.022% 
TABLE_17.. 20.022% 
TABLE_18.. 20.022% 
TABLE_19.. 20.022% 
TABLE_20.. 20.022% 
TABLE_21.. 20.022% 
TABLE_22.. 10.011% 
TABLE_23.. 10.011% 
TABLE_24.. 10.011% 
SQLITE_STAT1.. 10.011% 
TABLE_25.. 10.011% 
TABLE_26.. 10.011% 
TABLE_27.. 10.011% 
TABLE_28.. 10.011% 
TABLE_29.. 10.011% 
TABLE_30.. 10.011% 
TABLE_31.. 10.011% 
TABLE_32.. 10.011% 
TABLE_33... 10.011% 

*** All tables and indices ***

Percentage of total database.. 100.0%
Number of entries. 112673
Bytes of storage consumed. 9396224   
Bytes of payload.. 7701449 82.0% 
Average payload per entry. 68.35 
Average unused bytes per entry 9.50  
Average fanout 92.00 
Fragmentation.   5.3%
Maximum payload per entry. 2587  
Entries that use overflow. 10   0.009% 
Index pages used.. 91
Primary pages used 9070  
Overflow pages used... 15
Total pages used.. 9176  
Unused bytes on index pages... 25313   27.2% 
Unused bytes on primary pages. 1045045 11.3% 
Unused bytes on overflow pages 518  3.4% 
Unused bytes on all pages. 1070876 11.4% 

*** All tables ***

Percentage of total database..  92.6%
Number of entries. 63999 
Bytes of storage consumed. 8704000   
Bytes of payload.. 7267915 83.5% 
Average payload per entry. 113.56
Average unused bytes per entry 15.10 
Average fanout 92.00 
Fragmentation.   3.0%
Maximum payload per entry. 2587  
Entries that use overflow. 10   0.016% 
Index pages used.. 91
Primary pages used 8394  
Overflow pages used... 15
Total pages used.. 8500  
Unused bytes on index pages... 25313   27.2% 
Unused bytes on primary pages. 940417  10.9% 
Unused bytes on overflow pages 518  

Re: [sqlite] 2 Questions from a newbie

[Keith Goodman]

On Wed, Aug 20, 2008 at 6:03 AM, cstrader <[EMAIL PROTECTED]> wrote:
> Well it turns out that the db created this way (including the index) is no
> smaller than the one with 2400 tables, and the read is no faster!  Does that
> make any sense?

Do you often pull your data one stock at a time? If so an index on the
stock ID column will speed things up.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 2 Questions from a newbie

[Ken]

yes, but you now have only 1 table to deal with. The DB has to store roughly 
the same amount of data.

But at least now when you need to add a column you only need to do so on 1 
table.

And it reduces the amount of parsing ie not 2400 different select but only 1.

And you won't need to write custom code to get say stock prices for a day for 
all stocks. 
Instead you could write a Single select statement as follows (of course I'm 
making up my own column names).

   select ticker, quote_date, day_high, day_low, close
 from stock
    where quote_Date = '20080510' ;

The above assumes you have only 1 row per day, if you have more than 1 you'll 
need to add additional where clause fields or do a Max, min etc.



--- On Wed, 8/20/08, cstrader <[EMAIL PROTECTED]> wrote:
From: cstrader <[EMAIL PROTECTED]>
Subject: Re: [sqlite] 2 Questions from a newbie
To: "General Discussion of SQLite Database" 
Date: Wednesday, August 20, 2008, 8:03 AM

Well it turns out that the db created this way (including the index) is no 
smaller than the one with 2400 tables, and the read is no faster!  Does that 
make any sense?

Thanks


- Original Message - 
From: "Jeff Hamilton" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; "General Discussion of SQLite Database" 

Sent: Tuesday, August 19, 2008 12:51 PM
Subject: Re: [sqlite] 2 Questions from a newbie


> If all 2,400 tables have the same 9 columns you could try using a
> single table and adding a column for the ticker of the stock and then
> add an index to that column to allow quick lookups based on the
> ticker.
>
> -Jeff
>
> On Tue, Aug 19, 2008 at 9:44 AM, .:UgumugU:. <[EMAIL PROTECTED]>
wrote:
>> Hi cstrader,
>>
>> Just send some table structure and the explain what you are trying
todo.
>> In my opinion it is not a good way to deal with 2400 tables :)
>>
>> ugumugu
>>
>> cstrader yazm?s,:
>>> I'm just starting with SQLite (from vb.net) and could use some
advice.
>>>
>>> I have a set of 2400 (could get substantially higher) tables in a
single
>>> database (each table contains daily stock prices for a different
stock).
>>> Each table has 9 columns (all text for now) and some several
thousand 
>>> rows.
>>> So far so good.
>>>
>>> I need to read those tables sequentially and operate on them.  It
takes
>>> about 18 seconds to open each of  the tables in sequence.  (i.e.
loop
>>> through 2400 tables with a select command opening each one)
>>>
>>> First question... does that sound about right in terms of speed? 
Is 
>>> there a
>>> way to store the data that might be faster?  The data are
primarily 
>>> single
>>> precision -- I'm using text format, but perhaps that's not
best?
>>>
>>> Second question:
>>>
>>> When I open each table I need to add some blank columns to it.  So
I 
>>> include
>>> some "0 as NewCol" lines to create the new columns with
initial 0s.
>>>
>>> However, adding 20 new columns in this manner increases the total
time 
>>> for
>>> the loop from 19 seconds to  49 seconds.  This seems like a long
time. 
>>> Is
>>> there a faster way to get these empty columns in?
>>>
>>> More questions later I'm sure...
>>>
>>> Thanks in advance
>>>
>>> cs
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma table_info on a table in attached db

[Dennis Cote]

Mrinal Kant wrote:
>> Try:
>> PRAGMA [database.]table_info(table-name);
> 
>>  (  Kees Nuyt
> 
> Thankyou Kees for the solution
> But, I am facing another problem which is as follows: I open an sqlite
> db and create a table "trial". pragma main.table_info(trial) shows
> correct info. Then I create a temp table with the same name "trial".
> Now, pragma main.table_info(trial) shows the info about the temp
> table. pragma temp.table_info(trial) also shows info about the temp
> table. How do I get the table_info of the main.trial in this case?
> 
> sqlite3.exe trial.sqlite
> SQLite version 3.5.2
> Enter ".help" for instructions
> sqlite> create table trial (col_main);
> sqlite> pragma main.table_info(trial);
> 0|col_main||0||0
> sqlite> create temp table trial (col_temp);
> sqlite> pragma main.table_info(trial);
> 0|col_temp||0||0
> sqlite> pragma temp.table_info(trial);
> 0|col_temp||0||0
> sqlite>
> 

That looks like a bug in sqlite that should be reported at 
http://www.sqlite.org/cvstrac/captcha?nxp=/cvstrac/tktnew

Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 2 Questions from a newbie

[cstrader]

Well it turns out that the db created this way (including the index) is no 
smaller than the one with 2400 tables, and the read is no faster!  Does that 
make any sense?

Thanks


- Original Message - 
From: "Jeff Hamilton" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; "General Discussion of SQLite Database" 

Sent: Tuesday, August 19, 2008 12:51 PM
Subject: Re: [sqlite] 2 Questions from a newbie


> If all 2,400 tables have the same 9 columns you could try using a
> single table and adding a column for the ticker of the stock and then
> add an index to that column to allow quick lookups based on the
> ticker.
>
> -Jeff
>
> On Tue, Aug 19, 2008 at 9:44 AM, .:UgumugU:. <[EMAIL PROTECTED]> wrote:
>> Hi cstrader,
>>
>> Just send some table structure and the explain what you are trying todo.
>> In my opinion it is not a good way to deal with 2400 tables :)
>>
>> ugumugu
>>
>> cstrader yazm?s,:
>>> I'm just starting with SQLite (from vb.net) and could use some advice.
>>>
>>> I have a set of 2400 (could get substantially higher) tables in a single
>>> database (each table contains daily stock prices for a different stock).
>>> Each table has 9 columns (all text for now) and some several thousand 
>>> rows.
>>> So far so good.
>>>
>>> I need to read those tables sequentially and operate on them.  It takes
>>> about 18 seconds to open each of  the tables in sequence.  (i.e. loop
>>> through 2400 tables with a select command opening each one)
>>>
>>> First question... does that sound about right in terms of speed?  Is 
>>> there a
>>> way to store the data that might be faster?  The data are primarily 
>>> single
>>> precision -- I'm using text format, but perhaps that's not best?
>>>
>>> Second question:
>>>
>>> When I open each table I need to add some blank columns to it.  So I 
>>> include
>>> some "0 as NewCol" lines to create the new columns with initial 0s.
>>>
>>> However, adding 20 new columns in this manner increases the total time 
>>> for
>>> the loop from 19 seconds to  49 seconds.  This seems like a long time. 
>>> Is
>>> there a faster way to get these empty columns in?
>>>
>>> More questions later I'm sure...
>>>
>>> Thanks in advance
>>>
>>> cs
>>>
>>> ___
>>> 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] Help with Sqlite

[Jeffrey Needle]

On Wed, 2008-08-20 at 09:36 +0100, Brandon, Nicholas (UK) wrote:
> > I'm using the install of Firefox that comes with the Wubi 
> > install of Linux.  I like sqlite, but have a little problem.  
> > Perhaps someone can help.
> > 
> > When I add a new record to a database, an entry screen comes 
> > up with my fields and the ability to enter the new record.  
> > But the information I type into the input field seems placed 
> > in the field a bit too low -- about half of each letter is 
> > cut off at the bottom and I can't really read what I'm typing.
> > 
> > Is there a way to fix this?  Has anyone else had this experience?
> 
> I suspect you might be using the SQLite Manager add-on to Firefox. You
> can check the add-ons used in Firefox by going to the menu
> "Tools->Add-ons". When the window pops up select the "Extensions" tab at
> the top.
> 
> More information about SQLite Manager can be found at:
> http://code.google.com/p/sqlite-manager/
> 
> Nick
> 

Indeed, this is what I'm using.  I'll look in the proper forums for
help.

Thanks.



> 
> This email and any attachments are confidential to the intended
> recipient and may also be privileged. If you are not the intended
> recipient please delete it from your system and notify the sender.
> You should not copy it or use it for any purpose nor disclose or
> distribute its contents to any other person.
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
-- 


Jeffrey Needle
[EMAIL PROTECTED]

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with Sqlite

[Jeffrey Needle]

Thanks to all for the guidance.  Yes, I'm using it as a FF extension.
I'll have to look further in the proper forms. 

Thanks again.

On Wed, 2008-08-20 at 11:20 +0530, Graeme wrote:
> As the previous rely said, this is not a sqlite problem. I am not sure what 
> it 
> is because Firefox by itself does not seem to allow direct editing of the 
> sqlite databases it uses (are you using some extension?).
> 
> I suggest you ask on the Ubuntu forums. Also, look for the issue in the Wubi 
> launchpad bug tracker. If you are using a FF extension see what support the 
> author of that offers. Incidentally, Wubi has some limitations compared to a 
> proper (i.e. on its own partition) Linux install and is probably better for 
> trying out Ubuntu rather than permanent use.
> 
> Graeme
> 
> On Wednesday 20 August 2008 10:01:51 Jeffrey Needle wrote:
> > I'm using the install of Firefox that comes with the Wubi install of
> > Linux.  I like sqlite, but have a little problem.  Perhaps someone can
> > help.
> >
> > When I add a new record to a database, an entry screen comes up with my
> > fields and the ability to enter the new record.  But the information I
> > type into the input field seems placed in the field a bit too low --
> > about half of each letter is cut off at the bottom and I can't really
> > read what I'm typing.
> >
> > Is there a way to fix this?  Has anyone else had this experience?
> >
> > Thanks.
> 
> 
> 
-- 


Jeffrey Needle
[EMAIL PROTECTED]

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Correct SQL name resolution on AS clauses in a SELECT?

[Brad Stiles]

D. Richard Hipp <[EMAIL PROTECTED]> wrote:

> CREATE TABLE t1(a INTEGER, b INTEGER);
> INSERT INTO t1 VALUES(1,2);
> INSERT INTO t1 VALUES(9,8);

MSSQL Server 2000

> SELECT a AS b, b AS a FROM t1 ORDER BY a;

b   a
--- ---
1   2
9   8

> SELECT b AS a, a AS b FROM t1 ORDER BY a;

a   b
--- ---
2   1
8   9

> SELECT a, b AS a FROM t1 ORDER BY a;

a   a
--- ---
1   2
9   8

> SELECT a AS x, b AS x ORDER BY x;

Msg 207, Level 16, State 3, Line 1
Invalid column name 'a'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'b'.

> SELECT a AS b, b AS a WHERE a=1;

Msg 207, Level 16, State 3, Line 1
Invalid column name 'a'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'b'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'a'.

> SELECT a AS b, b AS a WHERE a=2;

Msg 207, Level 16, State 3, Line 1
Invalid column name 'a'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'b'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'a'.


> SELECT a AS x, b AS x WHERE x=1;

Msg 207, Level 16, State 3, Line 1
Invalid column name 'a'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'b'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'x'.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite 3 and php

[Amy Gibbs]

I'm trying to use php to maniupltate an sqlite database using the  
following code, but it doesn't seem to be running the update query.  
I'm not getting any error, but the datbase values are not changing.

Can anyone help?

query($query);
$result=$statement->fetchAll();
$statement=null;


foreach ($result as $product) {
$prodname=$product[0];
$prodqty = $product[1];
$prodid=$product[2];


$sql = "UPDATE ZITEM SET ZQUANTITY=0 WHERE ZPRODUCTID={$prodid}";
$sesdb->exec($sql);

$sesdb2 = null;

}

$query="SELECT ZNAME, ZQUANTITY, ZPRODUCTID FROM ZITEM WHERE  
ZCategory != 14";

$statement2 = $sesdb->query($query);
$result2 = $statement2->fetchAll();
echo "";
foreach ($result2 as $product) {
echo "".$product[0]."".$product[1]."". 
$product[2]."";

}
echo "";

?>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with Sqlite

[Brandon, Nicholas (UK)]

> I'm using the install of Firefox that comes with the Wubi 
> install of Linux.  I like sqlite, but have a little problem.  
> Perhaps someone can help.
> 
> When I add a new record to a database, an entry screen comes 
> up with my fields and the ability to enter the new record.  
> But the information I type into the input field seems placed 
> in the field a bit too low -- about half of each letter is 
> cut off at the bottom and I can't really read what I'm typing.
> 
> Is there a way to fix this?  Has anyone else had this experience?

I suspect you might be using the SQLite Manager add-on to Firefox. You
can check the add-ons used in Firefox by going to the menu
"Tools->Add-ons". When the window pops up select the "Extensions" tab at
the top.

More information about SQLite Manager can be found at:
http://code.google.com/p/sqlite-manager/

Nick


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple connection to in-memory database

[Brandon, Nicholas (UK)]

> 
> I would like to use transactions from separate threads, each 
> thread having one connection to a single in-memory db.
> 

If your production environment is a modern linux distribution you may
find the temporary directory ("/tmp") is already a memory drive using
the tmpfs filesystem. If not it is very easy to create one. Search the
internet for more information.

You can then access the database by multiple processes/threads by
referring to the file path.

Nick


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problems building for VxWorks...

[Kershaw, Anthony (UK)]

Hi,
Have been trying to compile (and run) the amalgamated version of sqlite
3.6.1 for VxWorks   5.5,
which is proving quite trouble-some. 
 
I am currently using these switches 
-DSQLITE_OMIT_BUILTIN_TEST 
-DSQLITE_TEMP_STORE=3 
-DSQLITE_HOMEGROWN_RECURSIVE_MUTEX 
-DSQLITE_OMIT_AUTOVACUUM 
-DSQLITE_OMIT_LOAD_EXTENSION 
-DSQLITE_OMIT_LOCALTIME 
-DSQLITE_OMIT_TCL_VARIABLE 
-DNO_GETTOD 
 
Which will allow it to compile, but there are missing symbols, mainly
because they don't occur in vxworks, the following functions don't
exist. 
fctrl
dup
fsync
access
getpid 
 
Any work arounds people could think of would be greatly appreciated. I
appreciate this heavily hits the locking mechanism code within sqlite...
 
Regards,
  Ant
 



This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users