Re: [sqlite] Memory usage (3.1.0 alpha)

[clive]

The environment I am using is RapidPlus. It makes calls directly to the DLL. I
changed the sqlite3 functions just to return in order to eliminate the
possibility of it being a problem with the environment, and there was no memory
loss.

Since I am using sqlite3_exec I do not think I need to use sqlite3_finalize.
Is that correct?
Perhaps the normal behaviour of sqlite3 is to use system memory until there is
non left? I cannot find a #define that specifies how many database pages are
cached in memory.

Clive






Ulrik Petersen <[EMAIL PROTECTED]> on 30-01-2005 00:44:01

Please respond to sqlite-users@sqlite.org

To:   sqlite-users@sqlite.org
cc:(bcc: clive/Emultek)

Subject:  Re: [sqlite] Memory usage (3.1.0 alpha)



Hi Clive,

[EMAIL PROTECTED] wrote:

>I am benchmarking sqlite3 as a potential database for Windows and embedded
>applications.
>I am running the following code in a Rapid development environment  that calls
>the equivalent sqlite3 functions
> in a Window's DLL that I built from the release .
>I am seeing that memory usage goes up and up with every loop, until Windows
runs
> out of virtual memory.
>Am I doing something wrong?
>
>while(true)
> SQL exec: 'BEGIN TRANSACTION';
> for  from 1 to 1000 step 1
>  SQL query: 'INSERT INTO Contacts values(''aaa'',''bbb'',''4'')';
> SQL exec: 'COMMIT TRANSACTION';
>
>
>
>
It looks like you've wrapped it in some sort of Visual Basic.  Is that true?

If you are using the sqlite3_prepare/sqlite3_step/sqlite3_finalize API,
yhe behavior you experience may be because you don't call
sqlite3_finalize.  Do you use that API?

HTH

Ulrik P.

Re: [sqlite] Handling database updates in my program updates

[Darren Duncan]

At 12:19 AM -0500 1/30/05, Mrs. Brisby wrote:
None of this is necessary if you select a durable schema.
Whenever you think you need to "add a field" - add a whole new table and
use joins anywhere you need access to the new field.
You can't "delete" a field, but deleting a field usually means losing
data anyway.
You can't change the nature of a field without changing the domain that
the data exists in. Keeping strict 1NF tables can really help avoid
this.
Your "update procedure" wouldn't be necessary at all.
What you're talking about is only reasonable for minor updates and/or 
temporary transitional updates.  Over the long term, doing that will 
result in cludges piled on cludges, all sorts of fields in 
non-optimal locations and all sorts of empty fields, or the same kind 
of data stored in multiple places etc.  It is more elegant to allow 
for all parts of a schema to change over the long term except perhaps 
for the smallest amount of control information, and said control 
table could just have 2 columns that store key/value pairs; eg, one 
key/value for version or other special clues that tell a program how 
to deal with the rest of the schema. -- Darren Duncan

[sqlite] compiling the demo example

[Alex Bartonek]

I'm running SuSE 9.2..I downloaded 3.0.8 source to compile since
the .RPM didnt install due to a dependency issue (which I couldnt
resolve).. now I have 3.0.8 compiled, works..but my question is..when I
try compiling the demo I dont believe I have the header files..so I
downloaded the 3.0.8-1 .rpm and it didnt install because it was looking
for 3.0.8-1 SQlite3 which I do not have installed.  Where is the 3.0.8
devel files that I can install?

Thanks in advance.
Alex

Re: [sqlite] Handling database updates in my program updates

[Mrs. Brisby]

None of this is necessary if you select a durable schema.

Whenever you think you need to "add a field" - add a whole new table and
use joins anywhere you need access to the new field.

You can't "delete" a field, but deleting a field usually means losing
data anyway.

You can't change the nature of a field without changing the domain that
the data exists in. Keeping strict 1NF tables can really help avoid
this.


Your "update procedure" wouldn't be necessary at all.



On Sun, 2005-01-30 at 11:44 +1300, Murray Moffatt wrote:
> I'm creating a shareware program using Visual Basic and SQLite.  I'm using  
> the SQLiteDB wrapper.
> 
> I want to plan how I am going to handle updates to my program that involve  
> modifing the database, and thus I'd like some advice as I'm sure others  
> have faced this problem before.  I need to be able to send out an update  
> to existing users which keeps all of their existing data in tact.  I  
> imagine that most of the updates will involve adding new fields.
> 
> At the moment my plan is to have a Control table in the database that  
> includes a field that holds the version number of the database.  Then when  
> my program starts up it can check this version against its own version and  
> if the database version is lower then it can tell the user that they need  
> to upgrade their database and automatically run an Update program that  
> will be included with all update releases.
> 
> The Update program will have an empty copy of the latest database (i.e.  
> tables all set up but no actual data), and will import all the records  
>  from the old database into the new one.  Then rename the old database  
> (thus keeping a copy in case something goes wrong) and copy the new one in  
> its place.
> 
> One problem I thought of is what happens if a user skips an update and  
> thus is upgrading from, say, version 1 to version 3.  Rather than create a  
> convoluted system where that Update program can convert from any version  
> to any other, I would like to make the Update program as generic as  
> possible, i.e. it reads the old database and matches the fields in it with  
> the fields in the new database and copies that data.  In this way it won't  
> expect to see certain fields in certain versions, instead it just sees a  
> Name field in the old database and copies the data found in that field  
> into the Name field in the new database.  Obviously all new fields will be  
> empty.
> 
> Does this sound like a logical way of handling database updates?  Does  
> anyone have any suggestions for a better method, or possible pitfalls in  
> my idea?
> 
> One assumption I'm making is that if I upgrade the verson of SQLite that  
> is used by my program then I assume that newer versions will always be  
> able to read databases created by older versions.  Is this correct?

[sqlite] how are people dealing with threading?

[Kervin L. Pierre]

Hello,
I am writing an application that uses sqlite in
a multithreaded environment.  The application is
a plugin for microsoft Outlook
( http://openconnector.org/ ) and hence I do not
have full control over how my objects are passed
amongst threads.
I am trying to keep a hash map of thread-id/sqlite3
pointers per object.  And then check that map
before any database access. But I am getting lots of
"sql schema has changed" and "file not a database"
errors randomly.
I have read the wiki topic on multithreading.
Can a single thread call sqlite3_open() multiple
times?
Also, how are people getting sqlite to work with
threading?  Is there a simple, efficient pattern
someone can recommend?
Has anyone come up against the seemingly random
"sql schema has changed" errors and "file not a
database" errors in sqlite3 and  fixed it?  How did
you?
Thanks,
Kervin

Re: [sqlite] Handling database updates in my program updates

[Eric Scouten]

There are two issues at play here:
(1) Schema updates driven by new versions of your application program. 
The method you described is basically a sound method, though I might 
suggest a couple of modifications. You raised the question of a user 
skipping versions. This is easily addressed by having a file format 
updater which applies updates sequentially. If the file needs to be 
updated from version 1 to version 3, apply the 1->2 update then apply 
the 2->3 update, etc. I wrote code like this a few months ago, and chose 
to keep the schema version as a date string in "-mm-dd" format, so I 
didn't have to think about version numbering and the updates were always 
applied in the right order. ;-)

(2) Database file format updates driven by new versions of SQLite. From 
my reading of this list over the past few months, I'm led to believe 
these are relatively rare. Most version updates come for free (3.0.7 to 
3.0.8, for instance, didn't invalidate any databases; files generated by 
one could be read & written by the other). A few updates (2.x -> 3.x) do 
break compatibility altogether, and you may have to include both 
versions of the SQLite library to maintain the upgrade path. (In this 
case, I gather this is a new application, so you won't have any need for 
2.x, but you may need to keep 3.x should a later version 4.x be unable 
to read 3.x files.) Of course, nobody forces you to upgrade to the 
latest and greatest version, so if a break in database file format 
occurs, you may want to consider staying behind. Plenty of people are 
still using 2.x. YMMV.

Hope this helps...
-Eric
Murray Moffatt wrote:
I'm creating a shareware program using Visual Basic and SQLite.  I'm 
using  the SQLiteDB wrapper.

I want to plan how I am going to handle updates to my program that 
involve  modifing the database, and thus I'd like some advice as I'm 
sure others  have faced this problem before.  I need to be able to 
send out an update  to existing users which keeps all of their 
existing data in tact.  I  imagine that most of the updates will 
involve adding new fields.

At the moment my plan is to have a Control table in the database that  
includes a field that holds the version number of the database.  Then 
when  my program starts up it can check this version against its own 
version and  if the database version is lower then it can tell the 
user that they need  to upgrade their database and automatically run 
an Update program that  will be included with all update releases.

The Update program will have an empty copy of the latest database 
(i.e.  tables all set up but no actual data), and will import all the 
records  from the old database into the new one.  Then rename the old 
database  (thus keeping a copy in case something goes wrong) and copy 
the new one in  its place.

One problem I thought of is what happens if a user skips an update 
and  thus is upgrading from, say, version 1 to version 3.  Rather than 
create a  convoluted system where that Update program can convert from 
any version  to any other, I would like to make the Update program as 
generic as  possible, i.e. it reads the old database and matches the 
fields in it with  the fields in the new database and copies that 
data.  In this way it won't  expect to see certain fields in certain 
versions, instead it just sees a  Name field in the old database and 
copies the data found in that field  into the Name field in the new 
database.  Obviously all new fields will be  empty.

Does this sound like a logical way of handling database updates?  
Does  anyone have any suggestions for a better method, or possible 
pitfalls in  my idea?

One assumption I'm making is that if I upgrade the verson of SQLite 
that  is used by my program then I assume that newer versions will 
always be  able to read databases created by older versions.  Is this 
correct?

Re: [sqlite] Handling database updates in my program updates

[Darren Duncan]

If you are looking for longetivity of your program, as I am doing 
with mine, I would keep and continue to use a Control table such as 
you speak of.  However, this Control table should not contain the 
database version number, but rather the version number of your own 
application program.  That way, if the database schema that your 
program uses gets updated over time to support new program features, 
or you fix bugs in your program that caused data to be stored 
incorrectly in the older versions, you will have the best chance of 
importing the data despite schema changes and have the best chance at 
correcting the data errors, because you can predict what kind of 
errors there would be based on the old program version the data says 
it was used with.  You could also store the SQlite version in the 
table if you want, but that would be mainly useful to recover from 
errors that older SQlite may have introduced, that were later 
detected.  Otherwise, I assume that SQLite itself is smart enough to 
recognize when SQLite files are or are not compatible.  You don't 
necessarily have to worry about this. -- Darren Duncan

At 11:44 AM +1300 1/30/05, Murray Moffatt wrote:
I'm creating a shareware program using Visual Basic and SQLite.  I'm 
using the SQLiteDB wrapper.

I want to plan how I am going to handle updates to my program that 
involve modifing the database, and thus I'd like some advice as I'm 
sure others have faced this problem before.  I need to be able to 
send out an update to existing users which keeps all of their 
existing data in tact.  I imagine that most of the updates will 
involve adding new fields.

At the moment my plan is to have a Control table in the database 
that includes a field that holds the version number of the database. 
Then when my program starts up it can check this version against its 
own version and if the database version is lower then it can tell 
the user that they need to upgrade their database and automatically 
run an Update program that will be included with all update releases.

The Update program will have an empty copy of the latest database 
(i.e. tables all set up but no actual data), and will import all the 
records from the old database into the new one.  Then rename the old 
database (thus keeping a copy in case something goes wrong) and copy 
the new one in its place.

One problem I thought of is what happens if a user skips an update 
and thus is upgrading from, say, version 1 to version 3.  Rather 
than create a convoluted system where that Update program can 
convert from any version to any other, I would like to make the 
Update program as generic as possible, i.e. it reads the old 
database and matches the fields in it with the fields in the new 
database and copies that data.  In this way it won't expect to see 
certain fields in certain versions, instead it just sees a Name 
field in the old database and copies the data found in that field 
into the Name field in the new database.  Obviously all new fields 
will be empty.

Does this sound like a logical way of handling database updates? 
Does anyone have any suggestions for a better method, or possible 
pitfalls in my idea?

One assumption I'm making is that if I upgrade the verson of SQLite 
that is used by my program then I assume that newer versions will 
always be able to read databases created by older versions.  Is this 
correct?

Re: [sqlite] Memory usage (3.1.0 alpha)

[Ulrik Petersen]

Hi Clive,
[EMAIL PROTECTED] wrote:
I am benchmarking sqlite3 as a potential database for Windows and embedded
applications.
I am running the following code in a Rapid development environment  that calls
the equivalent sqlite3 functions
in a Window's DLL that I built from the release .
I am seeing that memory usage goes up and up with every loop, until Windows runs
out of virtual memory.
Am I doing something wrong?
while(true)
SQL exec: 'BEGIN TRANSACTION';
for  from 1 to 1000 step 1
 SQL query: 'INSERT INTO Contacts values(''aaa'',''bbb'',''4'')';
SQL exec: 'COMMIT TRANSACTION';
 

It looks like you've wrapped it in some sort of Visual Basic.  Is that true?
If you are using the sqlite3_prepare/sqlite3_step/sqlite3_finalize API, 
yhe behavior you experience may be because you don't call 
sqlite3_finalize.  Do you use that API?

HTH
Ulrik P.

[sqlite] Handling database updates in my program updates

[Murray Moffatt]

I'm creating a shareware program using Visual Basic and SQLite.  I'm using  
the SQLiteDB wrapper.

I want to plan how I am going to handle updates to my program that involve  
modifing the database, and thus I'd like some advice as I'm sure others  
have faced this problem before.  I need to be able to send out an update  
to existing users which keeps all of their existing data in tact.  I  
imagine that most of the updates will involve adding new fields.

At the moment my plan is to have a Control table in the database that  
includes a field that holds the version number of the database.  Then when  
my program starts up it can check this version against its own version and  
if the database version is lower then it can tell the user that they need  
to upgrade their database and automatically run an Update program that  
will be included with all update releases.

The Update program will have an empty copy of the latest database (i.e.  
tables all set up but no actual data), and will import all the records  
from the old database into the new one.  Then rename the old database  
(thus keeping a copy in case something goes wrong) and copy the new one in  
its place.

One problem I thought of is what happens if a user skips an update and  
thus is upgrading from, say, version 1 to version 3.  Rather than create a  
convoluted system where that Update program can convert from any version  
to any other, I would like to make the Update program as generic as  
possible, i.e. it reads the old database and matches the fields in it with  
the fields in the new database and copies that data.  In this way it won't  
expect to see certain fields in certain versions, instead it just sees a  
Name field in the old database and copies the data found in that field  
into the Name field in the new database.  Obviously all new fields will be  
empty.

Does this sound like a logical way of handling database updates?  Does  
anyone have any suggestions for a better method, or possible pitfalls in  
my idea?

One assumption I'm making is that if I upgrade the verson of SQLite that  
is used by my program then I assume that newer versions will always be  
able to read databases created by older versions.  Is this correct?

[sqlite] Memory usage (3.1.0 alpha)

[clive]

I am benchmarking sqlite3 as a potential database for Windows and embedded
applications.
I am running the following code in a Rapid development environment  that calls
the equivalent sqlite3 functions
 in a Window's DLL that I built from the release .
I am seeing that memory usage goes up and up with every loop, until Windows runs
 out of virtual memory.
Am I doing something wrong?

while(true)
 SQL exec: 'BEGIN TRANSACTION';
 for  from 1 to 1000 step 1
  SQL query: 'INSERT INTO Contacts values(''aaa'',''bbb'',''4'')';
 SQL exec: 'COMMIT TRANSACTION';

Re: [sqlite] AUTOINCREMENT of a not PRIMARY KEY

[Marc Durocher]

Hi, can you show us your CREATE statement with
AUTOINCREMENT ?

 --- Serge Ratke <[EMAIL PROTECTED]> a écrit : 
> Hi List.
> 
> What i need is a Table which is quite simple
> 
> CRATE TABLE entry (
>   id INTEGER,
>   parent_id INTEGER,
>   name TEXT,
>   PRIMARY KEY(parent_id, name)
> );
> 
> Now the problem is, i want the id get incremented by
> every INSERT. I tried
> some things like AUTOINCREMENT (which has no effect
> at all, since id is
> not a primary key) and UNIQUE.
> 
> Right now i see only one solution for this.
> 
> 1. create a trigger on the INSERT of the entry
> table. this trigger will
> INSERT a row into a second table, which has the
> following scheme
> 
> CREATE TABLE sequence (
>   id INTEGER PRIMARY KEY
> );
> 
> 2. create another trigger on the INSERT of the
> sequence table. this
> trigger will UPDATE the id in the entry table and
> set the entry.id to the
> new generated sequence.id
> 
> Don't know if this will actually work, i didn't try
> it by now. I hope
> there is an easier solution.
> 
> Thanks in advance
> 
> -- 
> Using Opera's revolutionary e-mail client:
> http://www.opera.com/m2/
>  

__
Lèche-vitrine ou lèche-écran ?
magasinage.yahoo.ca

Re: [sqlite] AUTOINCREMENT of a not PRIMARY KEY

[Will Leshner]

On Sat, 29 Jan 2005 12:07:31 +0100, Serge Ratke <[EMAIL PROTECTED]> wrote:
> Hi List.
> 
> What i need is a Table which is quite simple
> 
> CRATE TABLE entry (
>   id INTEGER,
>   parent_id INTEGER,
>   name TEXT,
>   PRIMARY KEY(parent_id, name)
> );
> 
> Now the problem is, i want the id get incremented by every INSERT. I tried
> some things like AUTOINCREMENT (which has no effect at all, since id is
> not a primary key) and UNIQUE.

Couldn't you make 'id' the primary key and then create a UNIQUE index
for parent_id,name? I think, basically, PRIMARY KEY(parent_id, name)
is just creating such an index anyway.

Re: [sqlite] Win CE support

[Mateusz Łoskot]

Hi,
User Krzysztof Kowalczyk wrote::
If I was to provide patches for Windows CE support (and maintain win
ce code in the future):
a) would they get accepted (assuming the quality is up to sqlite's
quality) ? Basically I'm asking if it's worthwile for me to spend time
on doing that (it wouldn't if e.g. the patches wouldn't get integrated
into official sources no matter how good they are)
I'm not sqlite-wince project maintainer but I think if you have
some improvements / patches you should commit it.
b) what's the best way to make it happen? open a bug and attach diff to it?
There is a Patches link which is what you need. So prepare patch and 
submit it.

As SF.net and CVS documentation recommends patches should be created 
that way:

$ cvs diff -Nua  | gzip > my_patch.gz
Greets
--
Mateusz Łoskot, mateusz (at) loskot (dot) net
Registered Linux User #220771, Debian (Sarge)

[sqlite] AUTOINCREMENT of a not PRIMARY KEY

[Serge Ratke]

Hi List.
What i need is a Table which is quite simple
CRATE TABLE entry (
 id INTEGER,
 parent_id INTEGER,
 name TEXT,
 PRIMARY KEY(parent_id, name)
);
Now the problem is, i want the id get incremented by every INSERT. I tried
some things like AUTOINCREMENT (which has no effect at all, since id is
not a primary key) and UNIQUE.
Right now i see only one solution for this.
1. create a trigger on the INSERT of the entry table. this trigger will
INSERT a row into a second table, which has the following scheme
CREATE TABLE sequence (
 id INTEGER PRIMARY KEY
);
2. create another trigger on the INSERT of the sequence table. this
trigger will UPDATE the id in the entry table and set the entry.id to the
new generated sequence.id
Don't know if this will actually work, i didn't try it by now. I hope
there is an easier solution.
Thanks in advance
--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Re: [sqlite] System date and time in Tcl

[Paolo Vernazza]

Anirban Sarkar wrote:
How do I pick the system date and time in TCL?
Any early help will be appreciated since it is very urgent.
Thanking in advance.
Your's sincerely,
Anirban Sarkar
 

This isn't a TCL mailing list.
Please, stop asking questions regarding TCL ("System date and time in 
Tcl", "Formatting system date and time in TCL", "Expr error in TCL", 
etc...).

Paolo Vernazza