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: sqlite-users@sqlite.org 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