[sqlite] SQLite 3.3.1
Hmmm. The number of warnings compiling the 3.3.1 source is up to 10 from 6 in the 3.3.0 source. All of them appear to be related to inappropriate sign comparisons/assignments. -ken
Re: [sqlite] Problem executing SQLite 3.3.0 under MacOS
Thank you, kind sir. Sometimes it is difficult to see the individual ferns on the floor of the massive forest! Oddly enough, the problem seems to have been caused by the lack of the os.c file being compiled into the project at all? Some files that were in the 3.2.8 code stream are no longer present and this new os.c file was added to the 3.3.0 code tree. I had copied to the files from the 3.3.0 code stream into the project subdirectory, but I had missed the fact that there was a new file, and the compilation did not report the problem! I added this file and adjusted the optimization level, and the problem disappeared -- the code is working as before! I have adjusted the code so that the debug mode has optimization turned off, but that it is still enabled in release mode. Thanks once again. -ken On 13-Jan-06, at 8:40 PM, [EMAIL PROTECTED] wrote: Try recompiling with all optimization turned off. Optimizers tend to reorder code which makes single-stepping confusing - both for you and your debugger.
Re: [sqlite] Problem executing SQLite 3.3.0 under MacOS
For the life of me I cannot figure out where this is going wrong. I am not a below average intelligence individual, and I have been working with computers for over 30 years, but this one has me totally at a loss. I should point out that while I own MacOS based systems exclusively for home use, I have never formally developed much on the systems. I have used REALbasic in the past, along with HyperCard and the like, but it is only recently that I have commenced attempting to learn Objective-C and Cocoa programming. I do have years of experience with Digital (PDP, VAX, Alpha), various UNIX and Windows based systems, however, and have used all of the major programming languages at one time or another. Most of my recent professional work is based on C#/.Net under Windows, where we use SQL Server and MSDE (SQL Server Express now) almost exclusively. I wanted to develop some applications to support my hobby and volunteer work -- officiating for Little League Baseball. Along with umpiring games, I also manage the District web site, and have written some code to track and report the results of season games, playoffs and tournaments. The original code was written in REALbasic, but I cannot afford to keep buying the new releases for a hobby that already costs me a significant amount of money each year. To that end I wanted to use the free XCode tools, which meant learning Objective- C and Cocoa for reasonable GUI applications. I was disappointed to find there was not readily available database interface, so I began writing one for myself in Objective-C. I had some problems with the original attempt to develop a SQLite framework, so I decided to simply create an experimental application and embed the SQLite code, along with my framework classes, and the trial code in the same application. I organized the project so that the main project source files were in the project base directory, and created a subdirectory each for the framework code and the SQLite source code. This worked fairly well, and I was able to get enough code written that I could create a new database, create a table, but some data in the table, and execute a query that returned the results into an in-memory result set (vaguely similar to what ADO.Net can do, but FAR simpler). Since none of my immediate applications will result in huge result sets being generated, I deemed this sufficient. I developed this trial application with the SQLite 3.2.8 source code, and it went fairly well. I was even able to step through my framework classes and even down into the SQLite source to track down how some operations actually worked. Life was not bad! Then I decided to replace the SQLite source with the 3.3.0 contents and see if there were any improvements. As I noted, I simply replaced the 51 source files in the subdirectory of my project with the same files from the 3.3.0 source tree, and recompiled. The first thing I noticed was that there were now only 6 warnings about sign differences on variable sin assignments or procedure calls, and that the code seemed to compile just a bit faster. When I attempt to execute the code, however, it signals a bad exception on a call to sqlite3OsLock() on line 1969 of pager.c as I am attempting to execute the SQL statement to create my first table after the database is created. 1. I cannot locate in the 3.3.0 source code how the reference to sqlite3OsLock is converted into the virtual table method. 2. There are no longer an os_win.h or os_unix.h file in the source tree, which is fine. 3. For some reason I can no longer step into my framework code! 4. Naturally, I cannot step into the SQLite code either, since I cannot step into my framework. 5. I can set a breakpoint in my Objective-C code and step from there, but sometimes this steps over instead of into! 6. It almost always fails to step into the SQLite C calls. 7. If I set a breakpoint on a SQLite statement, I hit it, but single stepping is erratic at best, often stepping over and seeming to jump randomly around in the code rather than sequentially following the source code. I am stuck! How do I resolve this problem and get the 3.3.0 code working once more? -ken On 12-Jan-06, at 11:52 PM, [EMAIL PROTECTED] wrote: The whole OS-backend was reworked for version 3.3.0. So do not expect to find the same functions in 3.3.0 that you had in 3.2.8. unixLock() is actually a virtual method on the OsFile object. Since SQLite is written in C not C++, we have to explicitly code the virtual method table. You can find it defined as the IoMethod structure in os.h. When you open a file, (using, for example, the sqlite3UnixOpenReadWrite() routine) you get back an object called OsFile which has as its first field a pointer to the IoMethod structure. sqlite3OsLock() is really a macro that resolves to OsFile->pMethod.xLock which should point to the unixLock()
Re: [sqlite] Problem executing SQLite 3.3.0 under MacOS
Yes, the unixLock() routine is defined in the 3.3.0 source, but not in the 3.2.8 code. Nor can I find the #define for this anywhere in the 3.3.0 source (nor can I find any definition for the sqlite3OsLock (other than a function prototype) in either the 3.2.8 or 3.3.0 source. When I attempt to debug the 3.3.0 source and step into the sqlite3OsLock call, it simply drops me into some assembler and declares the bad instruction signal. -ken On 12-Jan-06, at 8:46 PM, [EMAIL PROTECTED] wrote: Ken & Deb Allen <[EMAIL PROTECTED]> wrote: I had a look, but I do not see where the sqlite3OsLock code is implemented. I see the function prototype, but not the actual implementation (not even a typedef or wrapper for a native call). The real name of the function is unixLock. There is a #define that aliases the name based on architecture. You will find the source code in os_unix.c. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Problem executing SQLite 3.3.0 under MacOS
I have encountered a problem while attempting to execute some code with SQLite embedded. This code was working with the previous version of the source. I am developing a small framework to permit me to use the SQLite engine from within some Cocoa applications I am developing for personal use (managing team scores on a web site). I have created my own class structure for this and it was working, but when I upgraded to the 3.3.0 code and decided to test it before I added new features, I got an EXC_BAD_INSTRUCTION signal from the debugger when I attempt to step into the call to "sqlite3OsLock" procedure as noted in the call stack below. The statement I am executing at step 19 is [theDatabase executeNonQuery:@"CREATE TABLE Table1(PKey INTEGER NOT NULL, Name VARCHAR(32) NOT NULL, BirthDate FLOAT NOT NULL, Relation VARCHAR NULL, PRIMARY KEY(PKey))"]; This statement has worked fine up until now! Does anyone have any idea why this may be the case? #0 0x001a8ba4 in ?? #1 0x943e7e90 in sqlite3OsLock #2 0x00216de0 in pager_wait_on_lock at pager.c:1969 #3 0x00217dcc in sqlite3pager_get at pager.c:2579 #4 0x0005e560 in getPage at btree.c:1450 #5 0x0005f430 in lockBtree at btree.c:1880 #6 0x0005fbf8 in sqlite3BtreeBeginTrans at btree.c:2087 #7 0x0005f7a8 in lockBtreeWithRetry at btree.c:1953 #8 0x00060f78 in sqlite3BtreeCursor at btree.c:2698 #9 0x0024556c in sqlite3InitOne at prepare.c:218 #10 0x00245a7c in sqlite3Init at prepare.c:353 #11 0x00245c34 in sqlite3ReadSchema at prepare.c:393 #12 0x00083e14 in sqlite3StartTable at build.c:790 #13 0x00228c6c in yy_reduce at parse.y:124 #14 0x0022bb14 in sqlite3Parser at parse.c:3221 #15 0x0027a5a8 in sqlite3RunParser at tokenize.c:391 #16 0x002462a8 in sqlite3_prepare at prepare.c:541 #17 0x000ea8dc in sqlite3_exec at legacy.c:56 #18 0x002f3fe0 in -[SQLiteDatabase executeNonQuery:] at SQLiteDatabase.m:224 #19 0x00032df0 in -[DirectoryManager createNewDatabase:] at DirectoryManager.m:62
[sqlite] Compilation warning improvements with 3.3.0
Wow! Am I impressed! A while back I initiated the email concerning the large number of compiler warnings that were being generated when I attempted to compile the SQLite source code using either the Visual Studio 2005 compiler or using XCode 2.2 (gcc 4). This sparked off some acrimonious comments, but it was noted that many of these issues would be considered with the next release. Well, I found time to download the 3.3.0 source code this evening and compiled it with XCode -- and there are now a total of six (6) compiler warnings, all concerning signed/unsigned differences! The errors are in the following three files: build.c @2363 and @3155 pager.c @462 and @473 vdbeaux.c @445 and @447 At least the objects appear to be the same size and only differ in signed nature! I also believe that the code compiled much faster, possibly as a result of there being fewer warnings for the compiler to deal with. This is a huge improvement in the code, in my opinion, as it significantly increases the expectation that the code is behaving as one would expect.
Re: [sqlite] SQLite as a Windows kernel tool
This has been the whole point of my emails -- I am not sufficiently familiar with the actual code details for me to make that determination. There are no comments to indicate whether the implicit cast is 'safe' or not. I know that some casts are a simple fact of life, especially in UN!X based code, where the runtime or system API returns character data in an integer, but the conversion of a 64-bit to an 32-bit value is a different case. If I could have determined the context to a reasonable degree of certainty, it would be one thing, but not being able to determine the actual intent raises some concerns. -ken On 29-Oct-05, at 12:56 PM, [EMAIL PROTECTED] wrote: Ken & Deb Allen <[EMAIL PROTECTED]> wrote: vdbeapi.c e:\SQLITE\327\Source\vdbeapi.c(55) : warning C4244: 'return' : conversion from 'i64' to 'int', possible loss of data e:\SQLITE\327\Source\vdbeapi.c(195) : warning C4244: '=' : conversion from 'double' to 'i64', possible loss of data e:\SQLITE\327\Source\vdbeapi.c(232) : warning C4244: '=' : conversion from 'double' to 'u64', possible loss of data What about these three warnings do you think is a concern? Have you actually looked at the code in question to see if the possibility of data loss is real and unintentional? What makes you think that these warnings are not just a case of the compiler blowing smoke? -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] SQLite as a Windows kernel tool
I have been toying with the idea of checking whether or not I can use SQLite in a Windows device driver as its database. I am working on an existing product that is implemented as a series of Windows device drivers, filter drivers and even a file system driver. The file system driver manages a dedicated disk partition that contains an NTFS file system and a series of files (some flat files and some Btree files) that serve as the database. The code that manages this database is quite old and has been heavily modified several times, but the code is not well structured and contains a number of architectural features that act as hinderances to modifications and performance enhancements. From the beginning it was clear to me that several issues would be greatly minimized or even eliminated if we could use some form of a relational database management package instead of the home-grown file management code, but there do not seem to be any database packages that are available to kernel code. As part of another effort we looked at using SQLite, and performed a number of tests to that end. When I discovered that all of the source code was available, it seemed that we may be able to 'port' the code for use within out file system driver, either as a kernel DLL or directly compiled into the existing component. A number of features would have to be modified, such as the use of malloc() and free() calls, which would need to be replaced with the appropriate calls to kernel routines (if not the development of an extra set of routines for such dynamic memory usage), but we were hopeful. I had used the Visual C++ 6 compiler (in C mode) to compile the existing SQLite source for use in user space, and that was not much of a problem, but the DDK (kernel) compiler was a modified version of the Visual C++ 7 compiler, and a number of compilation features are quite different when compiling code into the kernel as opposed as into user space. We had a number of problems with compiling the SQLite code as part of our kernel build, and so I decided to attempt to compile the latest source code for user space using the Visual C++ 7 compiler (Visual Studio .Net 2003). I have an HTML file that is the output from this attempt, which lists 193 compiler warnings (the code actually runs) based on the 3.2.7 version of the SQLite source code. This mail list does not permit me to attach the file (message rejected as too large), but if anyone wants to see it I can send it to you. The vast majority of the warnings are related to either unsafe implicit data type conversions or signed and unsigned comparison operations. Some of these are not unreasonable, such as a complaint about conversion from "size_t" to "int", which should be the same size objects, but others definitely appear to be significant, such as an implicit conversion from "i64" or "u64" to int, which is a legitimate concern. Some of the signed/unsigned comparisons are also suspect (is an unsigned integer value of 0x81230001 greater than or less than a signed integer value of 0x0001? -- hint, as a signed value the former is a negative number, but as an unsigned it is not). Some of these warnings are simply the compiler being extremely (and appropriately) cautious, but some of them truly do look like odd coding. Why should the code unconditionally and implicitly assign a 64-bit integer value into a 32-bit integer variable? If the conditions under which this is done are such that there can be no loss of data, then there should be a comment explaining this, and the cast should be explicit to enforce that this is expected. Unfortunately, there are no such comments and not all of the implicit casts have a clear context when simply examining isolated lines of code. I had a quick look at some of the code, but I am not certain whether all, or even most, of these warnings can be safely ignored or not. I tried modifying the code to add explicit casts to eliminate all of the warnings, which worked, but I do not know whether or not the resulting code contains runtime errors or not (specifically as a result of data loss at runtime or improper comparison logic). Could someone have a look at some of these and provide some opinions? I know that the coding style used for SQLite is what I have heard referred to as "minimal C", but should the code not be commented concerning such conversions, and the conversion be made explicit? -ken
Re: [sqlite] SQLite3 Explorer dates
This would be more effective if it were also available for MacOS and
Linux.
-ken
On 20-Aug-05, at 5:32 AM, Ralf Junker wrote:
Hello Clark Christensen,
SQLiteSpy executes the query "select date('2005-08-19');" with no
problems. SQLiteSpy is a free SQLite3 database manager and is
similar to EXP. To testdrive SQLiteSpy, download your copy from
http://www.yunqa.de/delphi/sqlitespy/
Regards,
Ralf
If appropriate for a future rev of SQLite3 Explorer (EXP),
I'm requesting an update to the handling of date strings.
Using SQLite3 Explorer (EXP) v1.6 under WinXP, "select
date('2005-08-19')" gives a "not a valid FP number" error.
[sqlite] Problems using SQLITE .Net Data Provider (Windows)
I recently downloaded the (Finisar) .Net Data Provider for SQLITE from SourceForge in order to use this in a C# applications under Windoes XP. I had a few small problems getting the database created at all via .Net, but that is working, and I was able to also define the schema for my database using CREATE TABLE, CREATE TRIGGER and CREATE INDEX statements. Issue #1: I had an original problem where defining a table with the first column as "Record ID INTGER PRIMARY KEY AUTOINCREMENT NOT NULL" works fine when I execute it from the command line, but it chokes on the "AUTOINCREMENT" word when executed via .Net. It was suggested that I simply remove the word, which I did, and which permitted the command to execute without error, but it is my understanding that the behaviour of the table is different with and without this keyword. Issue #2: Another problem is attempting to use UNICODE in the database. If, from the command line, the first command after I open the database is [PRAGMA encoding="UTF-16"], and then I create the schema, when I open the database later from the command line and execute the command [PRAGMA encoding;], the result is reported as "UTF-16le", as expected. When I do the same thing from my C# code, the command line reports the encoding as "UTF-8". Issue #3: If I define a table as [CREATE TABLE Events (EventID INT NOT NULL, EventName VARCHAR(32) NOT NULL, EventLongName VARCHAR(128) NOT NULL, CreationDate DATETIME NOT NULL] via the C# code, then when I attempt to add records to the table I get the following exception. This same command executed from the SQLITE command line executes without an error and adds the record to the table. Anyone have any thoughts on why these errors may be reported by the SQLITE .Net Data Provider? By the way, I am using the SQLITE 3.2.2 DLL file for Windows downloaded from the SQLITE web site and (I believe, since I am home now) version 1.7 of the .Net Data Provider. -ken Database creation results; MSDE database successfully created! (765.6397 milliseconds) SQLITE database successfully created! (93.7518 milliseconds) VistaDB database successfully created! (62.5012 milliseconds) Firebird database successfully created! (1109.3963 milliseconds) —> System.ApplicationException; 5QLITE; error inserting [Event]; no such column; current_timestamp INSERT INTO Events(EventID; EventName; EventLongName; CreationDate) VALUE5(1; 'Event #1', 'Long Event Name #1'; current. Finisar.5QLite.5QLiteException; no such column; current_timestamp al:Finisar.SQLite.sqlite3.Throw() al:Finisar,SQLite,sqlite3,CheckOK() al: Finisar.SQLite.sqlite3.compile(String zSql) al:Finisar.5QLite.One5QL5tatement.Compile() al:Finisar.5QLite.5QLiteCommand.E?;ecul:eNonQuery() al: DatabaseE valuation. 5qliteDatabase.Populal:eEventInformation (Int32 MaxEventID) in f;\testing\databaseevaluation \databaseevaluation^sqlitedatabase.cs;linell6 — End of inner exception stack trace — at DatabaseE valuation. 5qliteDatabase.PopulateEventInformation(Int32 MaxEventID) in f;\testing\databaseevaluation\databaseevaluation\sqlitedatabase.cs; line 130 at DatabaseE valuation. DatabaseEvaluationForm,buttonPrepareDatabases_Clicb(Object sender; EventArgs e) in f;\testing\databaseevaluation\databaseevaluation \databaseevaluationform,cs; line 171 i ^...^.^ i
Re: [sqlite] Manifest Typing question
Will declaring the column as VARCHAR not achieve the same thing? Does SQLITE not translate VARCHAR to TEXT, rather than numeric? -ken On 21-Jun-05, at 5:10 AM, D. Richard Hipp wrote: On Tue, 2005-06-21 at 00:13 -0400, Tito Ciuro wrote: Hello, When I add text to the database, it's getting truncated because SQLite is converting it to a number. For example, I enter "9.0", but SQLite stores it as "9". Is there a way to force the value to be inserted as string? Make the declared datatype of the column TEXT. Example: CREATE TABLE t1(a, b INTEGER, c REAL, d TEXT); INSERT INTO t1 VALUES(9.0,9.0,9.0,9.0); SELECCT * FROM t1; Results in: 9|9|9|9.0 -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] updating records problem
All database tables should be defined with a unique primary key.
Ideally this should consist of one or more integer columns; tect
columns can be used but they are universally less efficient. If the
data being stored does not contain a natural unique identifier, then
one should be added; you can simply name the column "CustomerID" or
"PKey" and set it to be an integer. By ensuring that each record
receives a unique identifier and including that in all queries, the
main issue is resolved.
Of course one problem remains, and that is detecting whether some other
user has changed the record since you saved it. To achieve this, you
define another integer column and store another value in that field,
but this value is updated each time the record is saved. This can be a
simple sequential value that rolls around to zero (or one) again after
reaching some maximum value. The name of the field should reflect its
purpose, so you could name it something like "EditVersion" or
"ChangeFlag". The client does not need to access this field, or the
primary key field, but they can be used to ensure that the record being
updated matches the current record (you could read the current record
first, or use the fields to validate the update).
For example, to continue your example, the SELECT statement would be
modified to include the "CustomerID" and "ChangeFlag" columns, and the
RecordSet.Update() method would be modified to issue a SQL statement
like "UPDATE Customers SET Name = xxx, Age = yyy WHERE CustomerID =
AND ChangeFlag = fff", and the code would check to ensure that
exactly one record was modified.
-ken
On 17-May-05, at 4:46 PM, Will Leshner wrote:
I develop a database wrapper for SQLite and I have an interesting
problem that I'm curious how other people solve. Basically, the users
of my wrapper have the option of editing database records indirectly
through the wrapper, rather than directly, using UPDATE. The database
wrapper is a set of classes and one of those classes is a RecordSet.
So, when the user asks for a RecordSet, with the intention of editing
one or more records, she might do this (where rs is a RecordSet):
rs = db.SQLSelect("SELECT name, age FROM customers")
Now, the user can edit a record in the RecordSet like this:
rs.Edit
rs.Field("name") = "Frank"
rs.Field("age") = 10
rs.Update
What the wrapper does, when it sees the Update, is create SQL and feed
it to SQLite:
UPDATE cusomers SET name='Frank', age=10 WHERE name=
AND age=;
The problem is that the 'name' and 'age' fields are not sufficiently
unique to identify the very row the user wanted to update. Instead
every row that has matching names and ages are going to be updated.
To solve this problem, I've been telling users to explicitly add
'rowid' as one of their columns when they issue SQL to select records
to edit:
rs = db.SQLSelect("SELECT rowid, name, age FROM customers")
This works ok, but I'd really like to get rid of this limitation. I've
considered ways of possibly inserting 'rowid' manually to the user's
SQL, but the idea of modifying the user's SQL kind of leaves a bad
taste in my mouth.
I know that PHP also uses SQLite and I'm wondering if it has the same
problem and how it may have solved it.
Thanks for any help.
Re: [sqlite] timestamp how to ?
Having used other databases extensively, and discovering that SQLITE does not have a native DATETIME data structure, I have elected to store the Date/Time value from the operating system (which is either a 32-bit or 64-bit value) directly into an INT field and then translate it into a string on retrieval. I did some performance tests, and it seemed that converting a date/time string into the 32-bit or 64-bit value in my code and comparing integer values in a query were orders of magnitude faster than using the SQLITE functions for date/time comparisons on the fly. They are fine for formatting the data for display, but where I need to manipulate the data I still retrieve the 'raw' integer value and convert it as I require. -ken On 22-Apr-05, at 9:03 AM, [EMAIL PROTECTED] wrote: "msaka msaka" <[EMAIL PROTECTED]> writes: how can i use timestamp value in sqlite http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
Re: [sqlite] Problem storing integers
When can we expect a 3.2.2 release that includes this correction? I am working on a database that depends on 64-bit integer values. -ken On 15-Apr-05, at 7:35 AM, D. Richard Hipp wrote: On Fri, 2005-04-15 at 10:53 +0100, Richard Boulton wrote: I'm running the latest sqlite 3.2.1 command line tool on Windows XP I've just run some older versions of the command line tool and the last time the value 281474976710655 was stored correctly was 3.0.8 I was missing a couple of releases after 3.0.8 but saw the unexpected behaviour start in 3.1.2. Maybe it was introduced in 3.1? The bug was introduced by check-in [2246] on 2005-Jan-20 just prior to 3.1.0. Any integer between 140737488355328 and 281474976710655 is stored incorrectly. I'll put in a patch sometime today. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] optimize table
P.S. When experimenting with this, try naming this 'second' table first to reduce the work the database has to do in selecting/rejecting records. Ypu can experiment with this by creating this new table, populating it with a SELECT from the existing table (only the records without statistics), and then timing the SELECT/JOIN. -ken On 15-Apr-05, at 6:15 AM, Ken & Deb Allen wrote: I cannot speak for SQLITE for certain, but I know with a number of commercial RDBMS packages the index is not used unless it contains a sufficient degree of distribution. Microsoft SQL Server, for example, is quite explicit in stating that if the distribution of values across the range of records in an index falls below a certain level, then the index is ignored, which is why you are used to put the most dynamic column first in a multi-column index. I do not know if SQLITE performs any of this analysis and makes decisions on whether to use the index or not, but it is possible, especially if a sort is involved, that the index is not helping much if all records have one of two values, and as the majority of he records have the same value, you are looking only for the minority records. I do not know if using a date/time for this files will speed it up or not, since your statistical inclusion query will be for records "WHERE statistic IS NULL" or "WHERE statistic = 0", and as the database grows in size this will be the minority set of records. One 'trick' I have seen in the past was to create a second table that contained an entry for new records, also keyed by the same "id"; as each sale is created, an entry is also made in that table, possibly by a trigger. You select the records for statistics using a JOIN (inner), which will only return records that are in both tables. As the records are processed, the record is removed from the second table, and therefore the records will not be part of the JOIN in the future. In some databases this may be faster than using an index; I have not experimented with this in SQLITE, however. -ken On 15-Apr-05, at 4:30 AM, msaka msaka wrote: i dont need working with dates and times.. i only set 1 records on which was done.. which index is better to create for this problem? must i cut db files? or speed of statistic will be same now and after 1000 000 records? -Pôvodná správa- Od: Gert Rijs [mailto:[EMAIL PROTECTED] Odoslané: 15. apríla 2005 7:13 Komu: sqlite-users@sqlite.org Predmet: Re: [sqlite] optimize table msaka msaka wrote: CREATE TABLE sale( id INTEGER NOT NULL UNIQUE PRIMARY KEY, bill_no integer, bill_item_no integer, item_name varchar(20), qty integer, amount DOUBLE, statistic integer ) Why don't you replace "statistic" with a timestamp formatted like mmdd, perhaps add the time as well. If you add an index on it you can efficiently retrieve the rows for certain date periods without needing to update the table. Gert
Re: [sqlite] optimize table
I cannot speak for SQLITE for certain, but I know with a number of commercial RDBMS packages the index is not used unless it contains a sufficient degree of distribution. Microsoft SQL Server, for example, is quite explicit in stating that if the distribution of values across the range of records in an index falls below a certain level, then the index is ignored, which is why you are used to put the most dynamic column first in a multi-column index. I do not know if SQLITE performs any of this analysis and makes decisions on whether to use the index or not, but it is possible, especially if a sort is involved, that the index is not helping much if all records have one of two values, and as the majority of he records have the same value, you are looking only for the minority records. I do not know if using a date/time for this files will speed it up or not, since your statistical inclusion query will be for records "WHERE statistic IS NULL" or "WHERE statistic = 0", and as the database grows in size this will be the minority set of records. One 'trick' I have seen in the past was to create a second table that contained an entry for new records, also keyed by the same "id"; as each sale is created, an entry is also made in that table, possibly by a trigger. You select the records for statistics using a JOIN (inner), which will only return records that are in both tables. As the records are processed, the record is removed from the second table, and therefore the records will not be part of the JOIN in the future. In some databases this may be faster than using an index; I have not experimented with this in SQLITE, however. -ken On 15-Apr-05, at 4:30 AM, msaka msaka wrote: i dont need working with dates and times.. i only set 1 records on which was done.. which index is better to create for this problem? must i cut db files? or speed of statistic will be same now and after 1000 000 records? -Pôvodná správa- Od: Gert Rijs [mailto:[EMAIL PROTECTED] Odoslané: 15. apríla 2005 7:13 Komu: sqlite-users@sqlite.org Predmet: Re: [sqlite] optimize table msaka msaka wrote: CREATE TABLE sale( id INTEGER NOT NULL UNIQUE PRIMARY KEY, bill_no integer, bill_item_no integer, item_name varchar(20), qty integer, amount DOUBLE, statistic integer ) Why don't you replace "statistic" with a timestamp formatted like mmdd, perhaps add the time as well. If you add an index on it you can efficiently retrieve the rows for certain date periods without needing to update the table. Gert
[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
Re: [sqlite] create table question
So, would this problem still exist if the code were to open two
'connections' to the database, issue the SELECT on one open connection
and then issue the CREATE TABLE via the other open connection?
Does this in any way prevent an application from opening a single
connection, issuing a SELECT, and in the callback handling each of the
rows from the SELECT have the code issue another SELECT and have a
second callback handle the results from that query?
-ken
On 8-Apr-05, at 2:46 PM, Jay Sprenkle wrote:
select_stmt = db.execute("SELECT * FROM people")
# use some, but not all of the rows in select_stmt
create_stmt = db.execute("CREATE TABLE other (a,b)") # error:
database table is locked
Why does this happen?
Anyway around this?
You must finalize select_stmt before running again db.execute
Right. I have an instance where I would like to keep the
select_stmt
_open_ (or not finalized) while I create a new table. Is
this possible?
While you are reding the DB, you can't update it, sqlite support
many
simultaneous readers but only one write; so you can't create
a new table
while your select statement is running.
but he has only one writer. A select is not a writer, the create
statement is.
I couldn't get something like this to work either and ended up
building a list
of updates in memory which I applied after the finalize of the select.
Re: [sqlite] Problem when asking for databases that don't exist
To determine whether a specific database exists, check to see if the
file exists before calling sqlite3_open(), using stat() or some other
call.
To determine if a specific schema exists within an open database, use
the query "SEELCT COUNT(*) FROM sqlite_master WHERE type = 'table' AND
name IN ('Table12', 'Table2', ...)" and check that the resulting count
is the expected value.
-ken
On 9-Apr-05, at 6:18 AM, Jochen Müller wrote:
Hello!
I am new to SQL and have a simple question concerning sqlite. I am
using the functions sqlite3_open, sqlite3_get_table and
sqlite3_free_table and sqlite3_close.
Because i do not yet know how to check whether a database exists or
not, i always call the sqlite3_get_table function. If it returns an
error, the database probably not exist. But if this function returns
an error, all further calls to sqlite3 fail. In detail:
sqlite3_open(...)
sqlite3_get_table(...)
copy the columns to a string grid if sqlite3_get_table(...) succeeded
sqlite3_free_table(...)
sqlite3_close(...)
The first time sqlite3_get_table(...) fails, no further starts of this
sequence work. I get no errors when executing the statements, but
there's no table sqlite3_get_table copies to the given pointer
variables.
There would be two solutions to my problem. I have to check first if a
database exists or i have to make sqlite keep on working after
sqlite3_get_table fails.
Sorry for my poor english.
Regards,
Jochen
