Hi Ken,
I don't know enough to respond to all of your questions, but maybe the
following will help for a few of them.
Locking and concurrancy info:
http://www.sqlite.org/lockingv3.html
Date/timestamp variables:
http://www.sqlite.org/lang_createtable.html
Date/time manipulation
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
Donald Griggs
Opinions are not necessarily those of Misys Healthcare Systems nor its board
of directors.
-----Original Message-----
From: Ken & Deb Allen [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 13, 2005 8:59 PM
To: [email protected]
Subject: [sqlite] Some Functional Questions
I have been experimenting with SQLITE for a little over a week now, and
I must say that I am fairly impressed with many of its capabilities. I
have been experimenting with performance from several aspects, and the
numbers are quite respectable. I have several years experience with
Sybase, Access, Oracle, SQL Server and other relational databases, so I
am fairly comfortable with evaluation features.
While I intend to write some more programs to test more facilities, I
do have some questions that others may be able to answer, or provide
comments, and thereby save me some considerable amount of time.
1. I notice that there are only four (4) data types in SQLITE, which is
OK, but there is no TIMESTAMP type, which is an incredibly useful type.
Using this type makes it very easy to test whether a specific record
has been updated or not, since the database automatically updates the
value to a unique value (at least within that table) each time a record
is inserted or updated. I suspect that if I want this capability in
SQLITE I shall have to resort to some form of trigger or manually
control an incrementing field value. Are there any other options?
2. How does SQLITE handle the case where one program or thread (using
its own open handle) attempts to read records that are being modified
within a transaction from another program or thread? Will the rows read
be the original values, the values from the transaction, or will the
query fail?
3. One of the projects were I am considering using SQLITE is from
within a Windows filter driver, to act as an intelligent repository for
control information (of which there may be a significant amount). I
know that the code as provided will not compile within the Windows
kernel, as it depends on user runtime calls like 'malloc', 'free' and
'FlushFileBuffers', but I am considering changing these calls to more
internal forms (sqlite3_malloc, sqlite3_free and
sqlite3_flushfilebuffers, for example), and then creating conditional
compilation to define these to use either the user mode call or a
kernel level call. Does anyone know if this has been attempted before,
or if there are likely to be considerable problems with attempting
this?
4. In some circumstances the information I want to store may represent
a million or more records in each of a dozen or more tables. Is the
organization of the data more efficient if I store each table in its
own database file, or if I use a single file?
5. I also have cases where I may have a significant number of deleted
records, which will produce a significant amount of free space that can
be used for new records. I know that VACUUM can be used to dump the
contents of the database to a clone, drop the database and rename it,
but that takes a considerable amount of disk space, and in some cases
that disk space may not be available. How 'expensive' is using
auto-vacuum mode? I understand that this is going to attempt to reduce
the size of the database file by releasing unused 'pages', but does
that not require that data be moved around so that the empty pages are
at the end of the database file?
Thanks in advance for any and all assistance.
-Ken
