Re: [sqlite] design question / discussion

[Rich Rattanni]

Adolfo:
I can't tell you how many times I felt a flat file approach would be
better.  However, 2 years ago when the design began there was a
thought of 'Having the ability to mine data on the device would be an
invaluable tool'.  SQLite has proven superb for some aspects of the
system, but not for storing simple flag data I believe someone
name Occam had something to say about this?  Ah the benefits of
hindsight  (sorry for the sarcasm, its the only thing that keeps a
smile on my face).

Ken:
>How do you decide which 20% to clear incase of space treshold?
Oldest 20% is cleared once a max size is reached.  Its kinda
arbitrary... I just figured it was better to clear a large swath of
flags than a delete one, insert one approach.
>Is the downloaded data always deleted once successful?
Yes

Woody:
Good to know, thank you :-).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] design question / discussion

[Harold Wood & Meyuni Gani]

I've done an app like that before with a different db foundation. Basically 2 
different databases, same structure. The logging app hits an ini file before 
each write, if the current db is different than the name in the ini file then 
close the current db, open the new db and write the row to the new db, 
otherwise write the row to the current db.

I had background app that ran as a service and would switch the fb name in the 
ini file when one hour had passed or the db was full.

It worked great .

Woody
from his pda

-Original Message-
From: A.J.Millan <[EMAIL PROTECTED]>
Sent: Wednesday, May 21, 2008 2:29 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] design question / discussion

Rich Rattanni wrote:>Hi I have a general design question.  I have the
following senario...

>In an embedded system running linux 2.6.2x I have a sqlite database
>constantly being updated with data acquired by the system.  I cant
>lose data (hence why I am using sqlite in the first place).  However
>periodically I have download the data contain within the database to a
>central server.  The system cannot stall during the download and must
>continue to record data.  Also, after the download I need to shrink
>the database size, simply because if the database is allowed to grow
>to its max size (~50MB) then every download thereafter would be 50MB,
>which is unacceptable.

After thinking in your's problem, according to yours first exposition, it 
seems that you are using the SQLite dbase as a mere tampon or 
temporarybuffer to the acquired data.  In that condition, with no further 
process of those data in the embedded system, perhaps you can consider 
simply write a flat file appending to it the incoming data (may be 
alternating between two or more files) and then compress and send the data 
to the host where they can be further processed or appended to a dbase.

>From the security point of view, the data in the embedded device are not 
necesarily  safer in a SQLite dbase that in a flat file.  Perhaps that 
layer(SQLite) are not necessary at all in the embedded device.

Just thinking out loud :-)

Adolfo.

___
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 allows "RowID" to be the name of a column

[Darren Duncan]

Ralf Junker wrote:
>> On a separate note, it is best for one to be able to name a table or column 
>> et al anything one wants, with all the choice of names as you can store in 
>> a text column for user data.  Reserved words aren't an issue as long as 
>> entity names are referred to with an unambiguously different syntax, such 
>> as quoted identifiers as SQL does support.  Then database users don't have 
>> to worry about implementation details and can name tables and columns 
>> whatever they want; saying they can't name their column "RowID" is a leaky 
>> abstraction. 
> 
> Sure we all dislike restrictions. Can you suggest an alternative to a single
  reserved name to represent the column which uniquely identifies a database
record under any and all circumstances?

Yes, change the interface to RowID into a routine call rather than a column 
name; eg use "RowID()" rather than "RowID".  Then when using it in a 
SELECT, you can say "RowID() as foo" in the select list where "foo" is 
different than a normal table field.  Such is how 'standard' SQL does it. 
Any manager app can read the database schema first and generate a name 
"foo" that is distinct.

-- Darren Duncan

P.S.  Alternately, you can eliminate RowID entirely as a user-visible 
concept, leaving it just to SQLite's internals.  And also disallow storing 
duplicate rows, so that the values of the row fields themselves will 
uniquely identify a database record under any and all circumstances.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] design question / discussion

[A.J.Millan]

Rich Rattanni wrote:>Hi I have a general design question.  I have the
following senario...

>In an embedded system running linux 2.6.2x I have a sqlite database
>constantly being updated with data acquired by the system.  I cant
>lose data (hence why I am using sqlite in the first place).  However
>periodically I have download the data contain within the database to a
>central server.  The system cannot stall during the download and must
>continue to record data.  Also, after the download I need to shrink
>the database size, simply because if the database is allowed to grow
>to its max size (~50MB) then every download thereafter would be 50MB,
>which is unacceptable.

After thinking in your's problem, according to yours first exposition, it 
seems that you are using the SQLite dbase as a mere tampon or 
temporarybuffer to the acquired data.  In that condition, with no further 
process of those data in the embedded system, perhaps you can consider 
simply write a flat file appending to it the incoming data (may be 
alternating between two or more files) and then compress and send the data 
to the host where they can be further processed or appended to a dbase.

>From the security point of view, the data in the embedded device are not 
necesarily  safer in a SQLite dbase that in a flat file.  Perhaps that 
layer(SQLite) are not necessary at all in the embedded device.

Just thinking out loud :-)

Adolfo.

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

Re: [sqlite] SQLite and Threadsafety (again)

[Virgilio Alexandre Fornazin]

I'm a bit curious why it wouldn't work. I use the same approach right here to 
have a exclusive access to a database table
in the same model I told you. Also, I never used other locking mode that 
exclusive, because if want to write to the database
the write lock should be granted exclusively by a single thread (similar to 
pthreads multiple reader single writer lock scheme).

Also, DRH told in other answer in this thread the problem of using only one 
connection shared with many threads: you cannot 
guarantee that last_insert_rowid() is correct, because someone other can 
started a transaction and write a record to any table,
modifying the last_insert_rowid() result, which lead you to produce bugs if you 
depend on this feature.


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Shawn Wilsher
Sent: quarta-feira, 21 de maio de 2008 14:15
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite and Threadsafety (again)

The problem with the approach you suggest as that that does not work
when PRAGMA locking_mode = EXCLUSIVE, which we use in many places to
improve performance.  Additionally, it's my understanding that when
using the shared cache, that transactions are grouped across
connection objects (it's possible I misread a past e-mail though).  We
use the shared cache for every database connection (although, I've
been wondering as of late if it's really worthwhile).

It is unfortunate that we'll lose the ability to do multiple reads at
the same time, however.  I'm open to suggestions on a better way to
fix this problem.

Cheers,

Shawn

On Wed, May 21, 2008 at 1:05 PM, Virgilio Alexandre Fornazin
<[EMAIL PROTECTED]> wrote:
> Not to putting flame in question, but why not use any connection per thread
> ? At this way you can guarantee:
>
> - Correct transaction processing;
> - Avoid waiting on R/W locks, allowing more than one read to run
> concurrently;
>
> We also use this model with ODBC / ADO database layers.
>
> You don't need to take care if your database drivers provides thread safety,
> handle multiple active result sets
> (client-side cursors), last insert row id concurrency, etc.
>
> We tried to use a single connection per process, but after changed to one
> connection per thread model, the
> gains we got avoiding synchronization was bigger than we imaginated.
>
> To get this changes working best, we created a database connection pool,
> that we use to get the connections by their
> ID´s (yes, it´s a key-value dictionary). The pool also 'recycle' connections
> that will not be used by any thread to
> avoid resource leaking.
>
> Is this case, assuming that the unique ID of the database is the file name
> (SQLite database file name), you can get
> this behaviour to work transparently for your consumers (I assume you´re not
> using directly the sqlite3_* calls inside
> your program, you have some kind of high-level abstraction to use them).
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Shawn Wilsher
> Sent: quarta-feira, 21 de maio de 2008 13:08
> To: General Discussion of SQLite Database
> Subject: [sqlite] SQLite and Threadsafety (again)
>
> Hey all,
>
> I've come to the sad realization that we need to make our sqlite
> wrapper threadsafe so it can be used on multiple threads without
> consumers having to worry about threadsafety themselves.  So, I wanted
> to make sure all my assumptions about sqlite data structures are
> correct so I don't introduce issues before undertaking this task.
>
> First, I know that the sqlite3 object can be accessed on multiple
> threads, but it must only be used by one thread of control at a time.
> It is also my understanding that this same constraint applies to
> sqlite3_stmt objects - they can only be used by one thread of control
> at a time but accessed on multiple ones.  What I am not so sure about,
> however, is if I have to protect the sqlite3 object that "owns" the
> statement when I'm calling methods on it such as sqlite3_bind_*
> interfaces, sqlite3_step, sqlite3_reset, and sqlite3_finalize.
> Conservatively, I'm assuming yes on all of the above, but I hope I'm
> wrong for at least some of those.  I would, however, expect to have to
> protect the sqlite3 object when calling sqlite3_prepare_v2.
>
> Clarification on this would be greatly appreciated.
>
> Cheers,
>
> Shawn Wilsher
> Mozilla Developer
> ___
> 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 

Re: [sqlite] design question / discussion

[Rich Rattanni]

> It seems unclear to me what your requirements are trying to attempt.
>
> Do you need to keep any of this data, if so for how long?
   I have to keep all data until a download.  Downloads can fail too
so I cannot delete data until a download succeeds.
> Do you need to be able to read the older data?
   The device supports viewing the flag information via a webpage.
Not to mention, I only want the device to store a fixed amount (say
5000) flags, and if this limit is reached I will clear some amount
(say 20%) to make room for new data.

> Do you need to be able to subset the data?
   No


>Main.db  = contains download.db and is an attachment point for ancillary db's.
>wrtdb_###.db  = Always write to this location.

>When a download is needed simply close the current wrtdb_###. Create a new 
>wrrtdb_###.db and Incrementing new wrtdb table in the >main.db

Are you saying that when I want to do a download, I copy the data from
the wrtdb_###.db to main?  Then download main?  If so I thought about
that, but then I have to reserve space for 2X the size of wrtdb_###,
because during the copy the data will exist on the unit in duplicate.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite and Threadsafety (again)

[D. Richard Hipp]

On May 21, 2008, at 12:08 PM, Shawn Wilsher wrote:

> Hey all,
>
> I've come to the sad realization that we need to make our sqlite
> wrapper threadsafe so it can be used on multiple threads without
> consumers having to worry about threadsafety themselves.  So, I wanted
> to make sure all my assumptions about sqlite data structures are
> correct so I don't introduce issues before undertaking this task.
>
> First, I know that the sqlite3 object can be accessed on multiple
> threads, but it must only be used by one thread of control at a time.

Beginning with version 3.5.0, SQLite enforces this itself using its  
own internal mutexes, so the application is free to (try to) use the  
same database connection from multiple threads at the same time.

>
> It is also my understanding that this same constraint applies to
> sqlite3_stmt objects - they can only be used by one thread of control
> at a time but accessed on multiple ones.  What I am not so sure about,
> however, is if I have to protect the sqlite3 object that "owns" the
> statement when I'm calling methods on it such as sqlite3_bind_*
> interfaces, sqlite3_step, sqlite3_reset, and sqlite3_finalize.
> Conservatively, I'm assuming yes on all of the above, but I hope I'm
> wrong for at least some of those.  I would, however, expect to have to
> protect the sqlite3 object when calling sqlite3_prepare_v2.
>
> Clarification on this would be greatly appreciated.

SQLite takes care of the serialization for you now.

There are a few routines that might not be purely threadsafe.  For  
example, sqlite3_last_insert_rowid().  All this does it return a 64- 
bit integer out of a structure.  But if a 64-bit integer load is not  
an atomic operation on your platform and a different thread happens to  
be updating the last_insert_rowid at the same time you are reading it,  
you might get back a nonsense value.  But on the other hand, if you  
have one thread trying to find the last_insert_rowid while another  
thread is busy changes the last_insert_rowid, then you already have  
bigger problems.  There are a few similar cases.

Of course, all this mutexing comes at a cost in performance.  When  
version 3.6.0 comes out, it might support a "low-mutex" mode as a  
compile-time option where it is the applications responsibility to  
serialize access to separate database connections again, just like in  
3.4.2.


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] SQLite and Threadsafety (again)

[Shawn Wilsher]

The problem with the approach you suggest as that that does not work
when PRAGMA locking_mode = EXCLUSIVE, which we use in many places to
improve performance.  Additionally, it's my understanding that when
using the shared cache, that transactions are grouped across
connection objects (it's possible I misread a past e-mail though).  We
use the shared cache for every database connection (although, I've
been wondering as of late if it's really worthwhile).

It is unfortunate that we'll lose the ability to do multiple reads at
the same time, however.  I'm open to suggestions on a better way to
fix this problem.

Cheers,

Shawn

On Wed, May 21, 2008 at 1:05 PM, Virgilio Alexandre Fornazin
<[EMAIL PROTECTED]> wrote:
> Not to putting flame in question, but why not use any connection per thread
> ? At this way you can guarantee:
>
> - Correct transaction processing;
> - Avoid waiting on R/W locks, allowing more than one read to run
> concurrently;
>
> We also use this model with ODBC / ADO database layers.
>
> You don't need to take care if your database drivers provides thread safety,
> handle multiple active result sets
> (client-side cursors), last insert row id concurrency, etc.
>
> We tried to use a single connection per process, but after changed to one
> connection per thread model, the
> gains we got avoiding synchronization was bigger than we imaginated.
>
> To get this changes working best, we created a database connection pool,
> that we use to get the connections by their
> ID´s (yes, it´s a key-value dictionary). The pool also 'recycle' connections
> that will not be used by any thread to
> avoid resource leaking.
>
> Is this case, assuming that the unique ID of the database is the file name
> (SQLite database file name), you can get
> this behaviour to work transparently for your consumers (I assume you´re not
> using directly the sqlite3_* calls inside
> your program, you have some kind of high-level abstraction to use them).
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Shawn Wilsher
> Sent: quarta-feira, 21 de maio de 2008 13:08
> To: General Discussion of SQLite Database
> Subject: [sqlite] SQLite and Threadsafety (again)
>
> Hey all,
>
> I've come to the sad realization that we need to make our sqlite
> wrapper threadsafe so it can be used on multiple threads without
> consumers having to worry about threadsafety themselves.  So, I wanted
> to make sure all my assumptions about sqlite data structures are
> correct so I don't introduce issues before undertaking this task.
>
> First, I know that the sqlite3 object can be accessed on multiple
> threads, but it must only be used by one thread of control at a time.
> It is also my understanding that this same constraint applies to
> sqlite3_stmt objects - they can only be used by one thread of control
> at a time but accessed on multiple ones.  What I am not so sure about,
> however, is if I have to protect the sqlite3 object that "owns" the
> statement when I'm calling methods on it such as sqlite3_bind_*
> interfaces, sqlite3_step, sqlite3_reset, and sqlite3_finalize.
> Conservatively, I'm assuming yes on all of the above, but I hope I'm
> wrong for at least some of those.  I would, however, expect to have to
> protect the sqlite3 object when calling sqlite3_prepare_v2.
>
> Clarification on this would be greatly appreciated.
>
> Cheers,
>
> Shawn Wilsher
> Mozilla Developer
> ___
> 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 and Threadsafety (again)

[Virgilio Alexandre Fornazin]

Not to putting flame in question, but why not use any connection per thread
? At this way you can guarantee:

- Correct transaction processing;
- Avoid waiting on R/W locks, allowing more than one read to run
concurrently;

We also use this model with ODBC / ADO database layers. 

You don't need to take care if your database drivers provides thread safety,
handle multiple active result sets 
(client-side cursors), last insert row id concurrency, etc.

We tried to use a single connection per process, but after changed to one
connection per thread model, the
gains we got avoiding synchronization was bigger than we imaginated.

To get this changes working best, we created a database connection pool,
that we use to get the connections by their
ID´s (yes, it´s a key-value dictionary). The pool also 'recycle' connections
that will not be used by any thread to
avoid resource leaking.

Is this case, assuming that the unique ID of the database is the file name
(SQLite database file name), you can get
this behaviour to work transparently for your consumers (I assume you´re not
using directly the sqlite3_* calls inside
your program, you have some kind of high-level abstraction to use them).


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Shawn Wilsher
Sent: quarta-feira, 21 de maio de 2008 13:08
To: General Discussion of SQLite Database
Subject: [sqlite] SQLite and Threadsafety (again)

Hey all,

I've come to the sad realization that we need to make our sqlite
wrapper threadsafe so it can be used on multiple threads without
consumers having to worry about threadsafety themselves.  So, I wanted
to make sure all my assumptions about sqlite data structures are
correct so I don't introduce issues before undertaking this task.

First, I know that the sqlite3 object can be accessed on multiple
threads, but it must only be used by one thread of control at a time.
It is also my understanding that this same constraint applies to
sqlite3_stmt objects - they can only be used by one thread of control
at a time but accessed on multiple ones.  What I am not so sure about,
however, is if I have to protect the sqlite3 object that "owns" the
statement when I'm calling methods on it such as sqlite3_bind_*
interfaces, sqlite3_step, sqlite3_reset, and sqlite3_finalize.
Conservatively, I'm assuming yes on all of the above, but I hope I'm
wrong for at least some of those.  I would, however, expect to have to
protect the sqlite3 object when calling sqlite3_prepare_v2.

Clarification on this would be greatly appreciated.

Cheers,

Shawn Wilsher
Mozilla Developer
___
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] design question / discussion

[Ken]

I think your trying to overcome the read/write concurrency issue with sqlite 
correct?  You want to have the ability to copy data (ie ftp) and recieve new 
data into an overflow database. 

Main.db  = contains download.db and is an attachment point for ancillary db's.
wrtdb_###.db  = Always write to this location.

When a download is needed simply close the current wrtdb_###. Create a new 
wrrtdb_###.db and Incrementing new wrtdb table in the main.db 

It seems unclear to me what your requirements are trying to attempt.

Do you need to keep any of this data, if so for how long?
Do you need to be able to read the older data? (except for downloads)
Do you need to be able to subset the data? 

Ken




Rich Rattanni <[EMAIL PROTECTED]> wrote: > Perhaps i've missed something or 
don't understand it well. Your
> databases is all on the same file or do you have 2 separate sqlite
> sessions to 2 different databases files? In the first scenario you
> must be very fast and in the second you can switch from one database
> to the other, unbind (close) the sqlite, do ftp or what ever you want
> and delete database file.
>
Yes in my code, I was thinking of having two database files on the filesystem
x and x+1.
During the download process I was going to drop any data generated during
the download process into x+1 (that is to say the system continues running
normally while a download is in progress).

> You attach x+1 to x. Why do you need it? If you delete old records on
> x after the ftp you can trash x, work with x+1 and recreate a void x.

I can see where I may not need it. I was just thinking of when the unit powers
back up I need to know which database is the 'main' database and which
database is the 'overflow'.  I would use the rule that x is the main and x+1
is overflow data. Strickly policy.  Incase it is unclear x and x+1 refer to
the actual filename of the database on disk, so I would have
flags.sqlite.0  <- Main
flags.sqlite.1 <- Overflow
***After download and next power up***
flags.sqlite.1 <- Main
flags.sqlite.2 <- Overflow


> I think you only need 2 databases and while you add data to A, you
> copy and delete B. Then switch A and B. Perhaps you need 3 databases,
> and separate the download and . On the other side you can attach the
> databases and reconstruct one big database.
>
Ah the design process I thought I had a good reason for my switching
policy but as I look back perhaps it is overly complex.  My original design
was a two database scheme, but as mentioned I thought the filename
was a slick way of determining which database was the primary (of course
a simple table in each database could do the same, that I join to and
update who is Main and Overflow).

Oh thats right, I actually remember now why I implemented this the way
I did.  The system has file size constraints on the amount of data
stored in the database, and downloads may be interrupted.  In the
event of a cancel I wanted all data to be in one database, hence the
copy of data from X+1 back into X.  I figured this works well because
when I move data from X+1 to X, I can check if storage constriants
have been violated and clear old data if necessary.

Also, I wanted to save the deletion and recreation of databases for
the next powerup, because the device is battery powered.  I have
a backup battery that allows me to run briefly after power is
removed, but this time is limited.  I figured doing this operation at
powerup is the safest bet (in the worst case, the power is removed
and I am back to relying on the backup battery, but on average the
battery is not removed immediately after insertion).

At the heart of the matter is the fact that vacuum's are too costly
(time wise) and while the device is not 'real time' per se, I must
services requests from another processor fairly quickly (<1 sec).

> If you need compression you can check any lzp, lzo or lzrh
> algorithms, they are very fast, and compress the files "on the fly".
> This compression algorithms works well with text data and bad with
> binary data. Take care because sqlite does already compress data in
> the databases files.

I cant reveal the nature of the data I am compressing, but on average, with
gzip, I see a reduction of 50 -> 70% in size.


Thanks for your reply, I implemented something similar to this but I end up
with corrupt databases if a download is performed, and power is removed,
and the sun and the stars alignblah blah blah.   In a word, its buggy.
I think violating sqlite and moving databases around using OS calls is
what is getting me.  I am up against a wall to design a solution
thatworks.  Stupid input specs!  Anyways, thats why I posted to the
list and I really do apprecaite your input
___
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

[sqlite] SQLite and Threadsafety (again)

[Shawn Wilsher]

Hey all,

I've come to the sad realization that we need to make our sqlite
wrapper threadsafe so it can be used on multiple threads without
consumers having to worry about threadsafety themselves.  So, I wanted
to make sure all my assumptions about sqlite data structures are
correct so I don't introduce issues before undertaking this task.

First, I know that the sqlite3 object can be accessed on multiple
threads, but it must only be used by one thread of control at a time.
It is also my understanding that this same constraint applies to
sqlite3_stmt objects - they can only be used by one thread of control
at a time but accessed on multiple ones.  What I am not so sure about,
however, is if I have to protect the sqlite3 object that "owns" the
statement when I'm calling methods on it such as sqlite3_bind_*
interfaces, sqlite3_step, sqlite3_reset, and sqlite3_finalize.
Conservatively, I'm assuming yes on all of the above, but I hope I'm
wrong for at least some of those.  I would, however, expect to have to
protect the sqlite3 object when calling sqlite3_prepare_v2.

Clarification on this would be greatly appreciated.

Cheers,

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

Re: [sqlite] design question / discussion

[Rich Rattanni]

> Perhaps i've missed something or don't understand it well. Your
> databases is all on the same file or do you have 2 separate sqlite
> sessions to 2 different databases files? In the first scenario you
> must be very fast and in the second you can switch from one database
> to the other, unbind (close) the sqlite, do ftp or what ever you want
> and delete database file.
>
Yes in my code, I was thinking of having two database files on the filesystem
x and x+1.
During the download process I was going to drop any data generated during
the download process into x+1 (that is to say the system continues running
normally while a download is in progress).

> You attach x+1 to x. Why do you need it? If you delete old records on
> x after the ftp you can trash x, work with x+1 and recreate a void x.

I can see where I may not need it. I was just thinking of when the unit powers
back up I need to know which database is the 'main' database and which
database is the 'overflow'.  I would use the rule that x is the main and x+1
is overflow data. Strickly policy.  Incase it is unclear x and x+1 refer to
the actual filename of the database on disk, so I would have
flags.sqlite.0  <- Main
flags.sqlite.1 <- Overflow
***After download and next power up***
flags.sqlite.1 <- Main
flags.sqlite.2 <- Overflow


> I think you only need 2 databases and while you add data to A, you
> copy and delete B. Then switch A and B. Perhaps you need 3 databases,
> and separate the download and . On the other side you can attach the
> databases and reconstruct one big database.
>
Ah the design process I thought I had a good reason for my switching
policy but as I look back perhaps it is overly complex.  My original design
was a two database scheme, but as mentioned I thought the filename
was a slick way of determining which database was the primary (of course
a simple table in each database could do the same, that I join to and
update who is Main and Overflow).

Oh thats right, I actually remember now why I implemented this the way
I did.  The system has file size constraints on the amount of data
stored in the database, and downloads may be interrupted.  In the
event of a cancel I wanted all data to be in one database, hence the
copy of data from X+1 back into X.  I figured this works well because
when I move data from X+1 to X, I can check if storage constriants
have been violated and clear old data if necessary.

Also, I wanted to save the deletion and recreation of databases for
the next powerup, because the device is battery powered.  I have
a backup battery that allows me to run briefly after power is
removed, but this time is limited.  I figured doing this operation at
powerup is the safest bet (in the worst case, the power is removed
and I am back to relying on the backup battery, but on average the
battery is not removed immediately after insertion).

At the heart of the matter is the fact that vacuum's are too costly
(time wise) and while the device is not 'real time' per se, I must
services requests from another processor fairly quickly (<1 sec).

> If you need compression you can check any lzp, lzo or lzrh
> algorithms, they are very fast, and compress the files "on the fly".
> This compression algorithms works well with text data and bad with
> binary data. Take care because sqlite does already compress data in
> the databases files.

I cant reveal the nature of the data I am compressing, but on average, with
gzip, I see a reduction of 50 -> 70% in size.


Thanks for your reply, I implemented something similar to this but I end up
with corrupt databases if a download is performed, and power is removed,
and the sun and the stars alignblah blah blah.   In a word, its buggy.
I think violating sqlite and moving databases around using OS calls is
what is getting me.  I am up against a wall to design a solution
thatworks.  Stupid input specs!  Anyways, thats why I posted to the
list and I really do apprecaite your input
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FW: do someone know? DotGnu

[David Alejandro Garcia Garcia]

do some one know how i can conect from dotGnu to sqlite? i have mandriva i hope 
some one can helpmefromDavid Alejandro Garcia Garciathanks
_
Juega y gana, tenemos 3 Xbox a la semana.
http://club.prodigymsn.com/ 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] deleting 100,000 entries

[Stephen Oberholtzer]

Actually, I seem to recall a discussion that revealed that "DELETE FROM Foo"
*does* truncate the table.  The discussion came up because someone
complained that "ON DELETE" triggers were not being fired; the explanation
was that 'DELETE FROM Foo" simply drops and re-creates Foo.  The solution
was to rewrite the code as "DELETE FROM Foo WHERE 1=1" or something similar.

This was pretty easy to verify:

sqlite> explain delete from foo;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 0 explain delete from foo;  00
1 Goto   0 4 000
2 Clear  2 0 0 foo00
3 Halt   0 0 000
4 Transaction0 1 000
5 VerifyCookie   0 1 000
6 Goto   0 2 000

sqlite> explain delete from foo where 1=1;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 0 explain delete from foo where 1=1;
00
1 Goto   0 23000
2 Integer1 2 000
3 Integer1 3 000
4 Ne 3 1426a
5 OpenRead   0 2 000
6 SetNumColumns  0 0 000
7 Rewind 0 14000
8 Integer1 3 000
9 Integer1 2 000
10Ne 2 1336a
11Rowid  0 1 000
12FifoWrite  1 0 000
13Next   0 8 000
14Close  0 0 000
15OpenWrite  0 2 000
16SetNumColumns  0 2 000
17FifoRead   1 21000
18NotExists  0 20100
19Delete 0 1 0 foo00
20Goto   0 17000
21Close  0 0 000
22Halt   0 0 000
23Transaction0 1 000
24VerifyCookie   0 1 000
25TableLock  -12 0 foo00
26Goto   0 2 000



Heading over to
http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/vdbe.c=1.741 and
looking at OP_Clear reveals that "DELETE FROM Foo" is closest in
implementation to "TRUNCATE TABLE Foo".



On Wed, May 21, 2008 at 2:46 AM, Harold Wood & Meyuni Gani <
[EMAIL PROTECTED]> wrote:

> Doesn't sqlite support the truncate table command
>
> Woody
> from his pda
>
> -Original Message-
> From: Carlo S. Marcelo <[EMAIL PROTECTED]>
> Sent: Tuesday, May 20, 2008 8:49 PM
> To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] deleting 100,000 entries
>
> @Barefoot and Keith,
>
> Awesome! It took forever for the web interface to delete, and I had to
> restart httpd for the database to unlock.. the command you provided took
> less than a second to clear everything out, thanks!
>
> Carlo
>
> - Original Message 
> From: BareFeet <[EMAIL PROTECTED]>
> To: General Discussion of SQLite Database 
> Sent: Wednesday, May 21, 2008 11:45:06 AM
> Subject: Re: [sqlite] deleting 100,000 entries
>
> Hi Carlo,
>
> > I want to clear the whole thing out(list)!
>
> Do you mean that you want to delete all rows from the "list" table? If
> so, do this:
>
> delete from list;
>
> See the syntax pages at:
> http://www.sqlite.org/lang.html
> http://www.sqlite.org/lang_delete.html
>
> Tom
> BareFeet
> http://www.tandb.com.au/sqlite/compare/?ml
>
> ___
> 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
>



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

Re: [sqlite] deleting 100,000 entries

[Ken]

The command  "Delete from table" is optimized into a "drop table" then "Create 
table" pair. Effectively making the delete from table a "truncate". 

http://sqlite.org/lang_delete.html

HTH,
Ken


Harold Wood & Meyuni Gani <[EMAIL PROTECTED]> wrote: Doesn't sqlite support the 
truncate table command

Woody
from his pda

-Original Message-
From: Carlo S. Marcelo 
Sent: Tuesday, May 20, 2008 8:49 PM
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] deleting 100,000 entries

@Barefoot and Keith,

Awesome! It took forever for the web interface to delete, and I had to restart 
httpd for the database to unlock.. the command you provided took less than a 
second to clear everything out, thanks!

Carlo

- Original Message 
From: BareFeet 

To: General Discussion of SQLite Database 
Sent: Wednesday, May 21, 2008 11:45:06 AM
Subject: Re: [sqlite] deleting 100,000 entries

Hi Carlo,

> I want to clear the whole thing out(list)!

Do you mean that you want to delete all rows from the "list" table? If  
so, do this:

delete from list;

See the syntax pages at:
http://www.sqlite.org/lang.html
http://www.sqlite.org/lang_delete.html

Tom
BareFeet
http://www.tandb.com.au/sqlite/compare/?ml

___
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] SQLiteException: Unable to open the database file

[Στράτος Νικολαΐδης]

Hi, I'm working on a vb.net application for PPC (Visual Studio 2008), 
with SQLite DB files and I have a problem on creating a master/detail form.

I have created a dataset file (xsd) containg a single table. I also 
created a form containg a DataGrid, which is connected with the dataset 
through a BindingSource component.

And in the sub MyForm_Load, there is the following code:

Me.MyTableAdapter.Fill(Me.DataSetMyData.mytable)


Everything is just ok.

Afterwards, I added a second table in the dataset file, for 
master/detail relationship. And a second DataGrid on the form, which I 
connected with the dataset and the second table. And the following code 
was added on the _LOAD sub:

Me.MyNewTableAdapter.Fill(Me.DataSetMyData.mynewtable)


When I'm executing the application, a SQLiteException is raised in the 
dataset file on the following code:

Public Overloads Overridable Function Fill(
ByVal dataTable As DataSetMyData.mynewtableDataTable
) As Integer
   Me.Adapter.SelectCommand = Me.CommandCollection(0)
   If (Me.ClearBeforeFill = true) Then
 dataTable.Clear
   End If
   Dim returnValue As Integer = Me.Adapter.Fill(dataTable)
   '  EXCEPTION RAISED  ^^
   Return returnValue
End Function


The exception that is raising on the second table is the following:

SQLiteException: Unable to open the database file

at System.Data.SQLite.SQLite3.Open()
at System.Data.SQLite.SQLiteConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen()
at System.Data.Common.DbDataAdapter.FillInternal()
at System.Data.Common.DbDataAdapter.Fill()
at System.Data.Common.DbDataAdapter.Fill()
at 
MyApplication.PDA.DataSetMyDataTableAdapters.mynewtableTableAdapter.Fill()
at MyApplication.PDA.FormMyData.FormMyData_Load()
at System.Windows.Forms.Form.OnLoad()
at System.Windows.Forms.Form._SetVisibleNotify()
at System.Windows.Forms.Control.set_Visible()
at System.Windows.Forms.Control.Show()
at MyApplication.PDA.Form1.btnTest_Click()
at System.Windows.Forms.Control.OnClick()
at System.Windows.Forms.Control.WnProc()
at System.Windows.Forms.Control._InternalWnProc()
at Microsoft.AGL.Forms.EVL.EnterMainLoop()
at System.Windows.Forms.Application.Run()
at MyApplication.PDA.Form1.Main()



I did a test application for windows (win32 platform) and it seems to 
work just fine!!!

What's going wrong?

Thank you in advance.

--
Stratos Nikolaidis
Thessaloniki, Greece
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite allows "RowID" to be the name of a column

[Ralf Junker]

Darren Duncan wrote:

>I think the real problem here is that SQL allows you to have non-distinct 
>rows in a table, when all rows should be distinct.

SQLite's implicit "RowID" does not allow non-distinct values (except for NULL, 
but this is documented behavior and only maintained for backwards 
compatability. It might change in a future version. The sooner, the better, 
IMHO).

>Working within SQL's flaws, the solution here is for every table to have a 
>unique constraint on one or more table columns.  Then applications just use 
>that to uniquely identify the row.

This is exactly the concept of "RowID". Nothing wrong with that. My only 
criticism is that this concept can be rendered non-functional by redefining the 
"RowID" so that it violates the uniqueness constraint. Example:

  CREATE TABLE x (
RowID TEXT);

Now the implicit unique RowID is no longer accessible via the "RowID" column. 
Workarounds are "_rowid_" or "OID", but they can be overwritten as well:

  CREATE TABLE x (
RowID TEXT,
_rowid_ text,
oid text);

For this table, it is no longer possible to access the implicit, unique RowID. 
General database applications (GUI managers, for example) can no longer (re-) 
identify a particular record!

>Rows should be identifiable by user-visible data, not hidden data, since a 
>database is supposed to model reality and people identify things based on 
>their someway-visible attributes.

This is what INTEGER PRIMARY KEY is for: It works as a duplicate for the 
implicity "RowID":

  CREATE TABLE x (
ID INTEGER PRIMARY KEY);

For this table, the visible "ID" and the implicit "RowID" access the same 
unique data. This is the recommended usage and poses no problems. Problems only 
arise if "RowID" is re-defined differently as demonstrated above!

>On a separate note, it is best for one to be able to name a table or column 
>et al anything one wants, with all the choice of names as you can store in 
>a text column for user data.  Reserved words aren't an issue as long as 
>entity names are referred to with an unambiguously different syntax, such 
>as quoted identifiers as SQL does support.  Then database users don't have 
>to worry about implementation details and can name tables and columns 
>whatever they want; saying they can't name their column "RowID" is a leaky 
>abstraction. 

Sure we all dislike restrictions. Can you suggest an alternative to a single 
reserved name to represent the column which uniquely identifies a database 
record under any and all circumstances?

Ralf 

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

Re: [sqlite] SQLite allows "RowID" to be the name of a column

[Ralf Junker]

Federico Granata wrote:

>have you seen here http://www.sqlite.org/autoinc.html ? 

Yes, I did. This documentation actually made me realize that the problem is not 
an implementation flaw but a design error, IMO. See my other answer in this 
thread for more rationale.

Ralf 

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

Re: [sqlite] SQLite allows "RowID" to be the name of a column

[Darren Duncan]

Ralf Junker wrote:
> But thinking more about hijacking "RowID" I am glad this is now a separate 
> thread.
  Lack of a reseverd "RowID" column name to guarantee unambiguous record 
operations
  by general SQLite tools is a potential thread to data security IMO.

I think the real problem here is that SQL allows you to have non-distinct 
rows in a table, when all rows should be distinct.  Working within SQL's 
flaws, the solution here is for every table to have a unique constraint on 
one or more table columns.  Then applications just use that to uniquely 
identify the row.  Rows should be identifiable by user-visible data, not 
hidden data, since a database is supposed to model reality and people 
identify things based on their someway-visible attributes.

On a separate note, it is best for one to be able to name a table or column 
et al anything one wants, with all the choice of names as you can store in 
a text column for user data.  Reserved words aren't an issue as long as 
entity names are referred to with an unambiguously different syntax, such 
as quoted identifiers as SQL does support.  Then database users don't have 
to worry about implementation details and can name tables and columns 
whatever they want; saying they can't name their column "RowID" is a leaky 
abstraction.

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

Re: [sqlite] SQLite allows "RowID" to be the name of a column

[Federico Granata]

>
> But thinking more about hijacking "RowID" I am glad this is now a separate
> thread. Lack of a reseverd "RowID" column name to guarantee unambiguous
> record operations by general SQLite tools is a potential thread to data
> security IMO.
>
> I would very much appreciate if this could be addressed in a future version
> of SQLite!
>
have you seen here http://www.sqlite.org/autoinc.html ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite allows "RowID" to be the name of a column

[Ralf Junker]

Bradley A. Town wrote:

>Ralf Junker wrote:
>
>> This alerts me to a potential danger for SQLite managers which must rely on 
>> some means to retrieve THE RowID which uniquely identifies a record for 
>> in-grid table editing. If the "RowID" name can be hijacked by other columns 
>> and given another purpose, it poses the danger that wrong wrong columns are 
>> updated and data is corrupted.
>>
>> How can I access the "RowID" given the above table declaration? I know about 
>> the "OID" and "_ROWID_" synonyms, but searching the documentation I find 
>> that they, too, can be used by other columns.
>>
>> I can therefore not see any non-ambiguous, reserved column name or API call 
>> to retrieve the implicit RowID value in such cases, especially if no primary 
>> key has been set like in the above schema.
>>
>> Any thoughts, especially from the SQLite developers?
>>
>> Thanks, Ralf
>>
>Creating another thread for this to avoid thread hijacking.

Thanks, I did not mean to hijack the thread.

But thinking more about hijacking "RowID" I am glad this is now a separate 
thread. Lack of a reseverd "RowID" column name to guarantee unambiguous record 
operations by general SQLite tools is a potential thread to data security IMO.

I would very much appreciate if this could be addressed in a future version of 
SQLite!

Ralf 

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

Re: [sqlite] design question / discussion

[Eduardo Morras]

At 19:12 20/05/2008, you wrote:
>Actually my reason for writing into a seperate database is more...
>well crude.  I tar several databases together then encrypt using
>openSSL. Then an FTP like program transmits the data a central server.
>  I must suspend writing into the database for the duration of the tar
>operation since tar does not abide by sqlites file locking rules.

Perhaps i've missed something or don't understand it well. Your 
databases is all on the same file or do you have 2 separate sqlite 
sessions to 2 different databases files? In the first scenario you 
must be very fast and in the second you can switch from one database 
to the other, unbind (close) the sqlite, do ftp or what ever you want 
and delete database file.

You attach x+1 to x. Why do you need it? If you delete old records on 
x after the ftp you can trash x, work with x+1 and recreate a void x.

I think you only need 2 databases and while you add data to A, you 
copy and delete B. Then switch A and B. Perhaps you need 3 databases, 
and separate the download and . On the other side you can attach the 
databases and reconstruct one big database.

If you need compression you can check any lzp, lzo or lzrh 
algorithms, they are very fast, and compress the files "on the fly". 
This compression algorithms works well with text data and bad with 
binary data. Take care because sqlite does already compress data in 
the databases files.

HTH



--
With sufficient thrust, pigs fly just fine. However, this is not
necessarily a good idea. It is hard to be sure where they are going to
land, and it could be dangerous sitting under them as they fly
overhead. -- RFC 1925  

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

Re: [sqlite] deleting 100,000 entries

[Harold Wood & Meyuni Gani]

Doesn't sqlite support the truncate table command

Woody
from his pda

-Original Message-
From: Carlo S. Marcelo <[EMAIL PROTECTED]>
Sent: Tuesday, May 20, 2008 8:49 PM
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] deleting 100,000 entries

@Barefoot and Keith,

Awesome! It took forever for the web interface to delete, and I had to restart 
httpd for the database to unlock.. the command you provided took less than a 
second to clear everything out, thanks!

Carlo

- Original Message 
From: BareFeet <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Wednesday, May 21, 2008 11:45:06 AM
Subject: Re: [sqlite] deleting 100,000 entries

Hi Carlo,

> I want to clear the whole thing out(list)!

Do you mean that you want to delete all rows from the "list" table? If  
so, do this:

delete from list;

See the syntax pages at:
http://www.sqlite.org/lang.html
http://www.sqlite.org/lang_delete.html

Tom
BareFeet
http://www.tandb.com.au/sqlite/compare/?ml

___
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