Re: [sqlite] SQLite v/s SQLite3 Performance Assay

2007-03-12 Thread Dan Kennedy
> My Intentions are towards exploring the reason behind these differences; and > what can be > done to counter these performance differences. I'm seeking some pointers > from the Community. Version 3 has a different default safety-level (default FULL) to version 3 (default NORMAL). So if you

[sqlite] SQLite v/s SQLite3 Performance Assay

2007-03-12 Thread Nitin Kashyap
Hi All, I Carried out a small assay comparing performance between SQLite-2.8.16 & SQLite-3.3.9; Over Multiple platforms & Operating Environments. Please consider the observations of the assay below. There seems to be a very clear and visible difference in performance of SQLite & SQLite3; for

[sqlite] SQLITE_CANTOPEN error in sqlite3_exec function.

2007-03-12 Thread Krishnaveni N
Hello, I am using some of the functions from C/C++ interface of SQLite3. While inserting or updating the database randomly some times it returns error code 14 ie SQLITE_CANTOPEN error. To execute SQL query I am using sqlite3_exec function. ret = sqlite3_exec(mDataBaseConnection,

Re: [sqlite] Sqlite 3.3.13; expr.c; analyzeAggregate

2007-03-12 Thread drh
"Noah Hart" <[EMAIL PROTECTED]> wrote: > In expr.c, around line 2253, there is a nested loop which reads: > > for(i=0; inSrc; i++, pItem++){ > if( pExpr->iTable==pItem->iCursor ){ > for(i=0; inSrc; i++, pItem++){ > if( pCol->iTable==pExpr->iTable && >

[sqlite] Sqlite 3.3.13; expr.c; analyzeAggregate

2007-03-12 Thread Noah Hart
In expr.c, around line 2253, there is a nested loop which reads: switch( pExpr->op ){ case TK_AGG_COLUMN: case TK_COLUMN: { /* Check to see if the column is in one of the tables in the FROM ** clause of the aggregate query */ if( pSrcList ){ struct SrcList_item

Re: [sqlite] notice: embedded system and locked database

2007-03-12 Thread Martin Jenkins
Jakub Ladman wrote: Problem is, that this is pretty obscure system. Renesas SuperH SH4 CPU Heavily patched 2.4.18 kernel. (patches will be presented on internet, but not at this time) Gentoo-embedded linux, based on uclibc 0.9.28 and busybox . Main storage is SD flash card. I must confess,

Re: [sqlite] SQlite 2.8.16 -> SQLite 3

2007-03-12 Thread Martin Jenkins
Mitchell Vincent wrote: So is a 60%ish reduction in DB size from 2.8.16 to 3.3.13 normal? Don't know about "normal" but ISTR version 3 did bring some fairly major improvements in file size. Given that you have your data in both SQLite formats would it not be fairly easy to dump both

Re: [sqlite] SQlite 2.8.16 -> SQLite 3

2007-03-12 Thread Mitchell Vincent
On 3/12/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > So is a 60%ish reduction in DB size from 2.8.16 to 3.3.13 normal? > 3.3.13 typically generates database files that are 30-40% smaller than 2.8.16. 60% seems excessive, but is not outside the range of possibility. What kind of data

[sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

2007-03-12 Thread Stef Mientki
If ask the table sturcture, with pragma table_info() I get of course the basic fields, like: CID,Name,Type, And also SOME special values, like Null, DefaultValue, PrimaryKey But NOT the following special values (and probably a lot more) Unique, AutoIncrement Is this due to the DLL I use,

Re: [sqlite] SQlite 2.8.16 -> SQLite 3

2007-03-12 Thread drh
"Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > I'm converting a bunch of databases from SQLite 2.8.16 to SQLite > 3.3.13 and am seeing something pretty amazing space saving. So good > that it might be too good to be true! > > For example, a 17 meg database is cut down to 7 megs. That's fantastic

Re: [sqlite] SQlite 2.8.16 -> SQLite 3

2007-03-12 Thread P Kishor
On 3/12/07, Mitchell Vincent <[EMAIL PROTECTED]> wrote: I'm converting a bunch of databases from SQLite 2.8.16 to SQLite 3.3.13 and am seeing something pretty amazing space saving. So good that it might be too good to be true! For example, a 17 meg database is cut down to 7 megs. That's

Re: [sqlite] additional functions in C

2007-03-12 Thread Jakub Ladman
I think i do understand it now. Thank you Dne pondělí 12 březen 2007 16:08 Dennis Cote napsal(a): > Jakub Ladman wrote: > > Cause i am really blockheaded - is there somebody so good-hearted, who > > could write simple step by step directions how to add one simple function > > to SQLite? > > > >

[sqlite] SQlite 2.8.16 -> SQLite 3

2007-03-12 Thread Mitchell Vincent
I'm converting a bunch of databases from SQLite 2.8.16 to SQLite 3.3.13 and am seeing something pretty amazing space saving. So good that it might be too good to be true! For example, a 17 meg database is cut down to 7 megs. That's fantastic if it's just that SQlite is *that* much better at

Re: [sqlite] UPDATE colomns based on their values

2007-03-12 Thread Dennis Cote
jose isaias cabrera wrote: sqlite> select * from LSOpenJobs where id = '166'; 166|166|1172158922388||us|MER|3.0 Cards|Technical| Project|2007-02-21|2007-02-21|2007-02-22|318||en2es,en2br||karina|||klmLD|D:\Projects\166||c the last column is the status, which has the value c and the

Re: [sqlite] cast problems sqlite3

2007-03-12 Thread drh
Tom Shaw <[EMAIL PROTECTED]> wrote: > > Using sqlite 3.3.5 You really should try to use something more recent. > > UPDATE av_summary SET rank=((det*100.0)/(tot)); > > sets first row to an integer (serendipity?) and then all the other > rows are real or text which caused problems since I was

Re: [sqlite] cast problems sqlite3

2007-03-12 Thread Tom Shaw
At 1:40 PM + 3/12/07, [EMAIL PROTECTED] wrote: Tom Shaw <[EMAIL PROTECTED]> wrote: Here ya go. >Tom Shaw <[EMAIL PROTECTED]> wrote: >> UPDATE av_summary SET rank=((det*100.0)/(tot)); only sets the >> first row correctly then all the others have bogus data in rank > >That seems

Re: [sqlite] Segmentation fault using sqlite

2007-03-12 Thread drh
"Rich Rattanni" <[EMAIL PROTECTED]> wrote: > I am wondering why my program crashes with a segmentation fault. This > has only occured once, but it seems to be something with sqlite. Is > this a known issue or is there anything I can do to further track this > down? > There are no known

Re: [sqlite] additional functions in C

2007-03-12 Thread Dennis Cote
Jakub Ladman wrote: Cause i am really blockheaded - is there somebody so good-hearted, who could write simple step by step directions how to add one simple function to SQLite? I have found so many inspirations and documentation seems to be very precise, but i do not understand it at its

[sqlite] Segmentation fault using sqlite

2007-03-12 Thread Rich Rattanni
I am wondering why my program crashes with a segmentation fault. This has only occured once, but it seems to be something with sqlite. Is this a known issue or is there anything I can do to further track this down? Program terminated with signal 11, Segmentation fault. #0 0x403cdcb4 in

Re: [sqlite] UPDATE colomns based on their values

2007-03-12 Thread P Kishor
On 3/12/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote: "P Kishor" wrote, > On 3/12/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote: >> >> Ok, I have figured out that I do not have null values on the data of my >> table, even though I do have empty strings. Here is my schema, >> >>

Re[2]: [sqlite] FTS: Custom Tokenizer / Stop Words

2007-03-12 Thread Ion Silvestru
But what about: I am very interested to know if it would be possible to use an FTS indexing module to store the inverted index only, but not the document's text. This would safe disk space if the text to index is stored on disk rather than inside the database.

[sqlite] Possible to get international collation built in to SQLite main distribution?

2007-03-12 Thread Samuel R. Neff
Is there any chance of getting an international-aware case-insensitive collation option put into the core language, at least as a compilation option? Seems that if SQLite is so focused on UTF data and so many users are international, the limitation of available options being English A-Z

Re: [sqlite] cast problems sqlite3

2007-03-12 Thread drh
Tom Shaw <[EMAIL PROTECTED]> wrote: > Here ya go. > > >Tom Shaw <[EMAIL PROTECTED]> wrote: > >> UPDATE av_summary SET rank=((det*100.0)/(tot)); only sets the > >> first row correctly then all the others have bogus data in rank > > > >That seems wrong. Can you post a sample database that

RE: [sqlite] stupid man's manual to sqlite?

2007-03-12 Thread Samuel R. Neff
Apress does have an online index they call "superindex" which indexes all Apress books. But I just did some test searches and found it to be equally useless. You can't search a specific book and some searches that obviously should have returned results didn't. In case you have better luck,

Re: [sqlite] sqlite3_update_hook and transactions

2007-03-12 Thread John Stanton
Make the hook queue a GUI update transaction and execute those transactions following the COMMIT. Discard the list of GUI update transactions on a ROLLBACK. Jef Driesen wrote: I was planning to use the sqlite3_update_hook function to notify my GUI about changes. The idea was that every part

Re: [sqlite] sqlite3_update_hook and transactions

2007-03-12 Thread drh
Jef Driesen <[EMAIL PROTECTED]> wrote: > > I think that approach should work. But the function sqlite3_commit_hook > is marked experimental in the documentation. What does that means? Is it > safe to rely on it? > "Experimental" means that we reserve the right to change it in future releases

Re: [sqlite] Different ROWID and PRIMARY KEY values

2007-03-12 Thread drh
Gerry Snyder <[EMAIL PROTECTED]> wrote: > [T]here must be a good reason why the > constraints are not copied, but I have not seen an explanation. > The statement is of the form: CREATE TABLE AS Where would the constraints come from? If the arbitrary select statement were of the form:

[sqlite] Re: sqlite3_update_hook and transactions

2007-03-12 Thread Jef Driesen
Dan Kennedy wrote: On Mon, 2007-03-12 at 10:51 +0100, Jef Driesen wrote: I was planning to use the sqlite3_update_hook function to notify my GUI about changes. The idea was that every part of the GUI can update itself when a database change is detected. But during testing, I encountered some

Re: [sqlite] cast problems sqlite3

2007-03-12 Thread drh
Tom Shaw <[EMAIL PROTECTED]> wrote: > UPDATE av_summary SET rank=((det*100.0)/(tot)); only sets the > first row correctly then all the others have bogus data in rank That seems wrong. Can you post a sample database that demonstrates this behavior? > [I]s there a "cast" operator in the

Re: [sqlite] FTS: Custom Tokenizer / Stop Words

2007-03-12 Thread drh
Ralf Junker <[EMAIL PROTECTED]> wrote: > I wonder if there is some effort already under way to allow custom tokenizers > by SQLite's full text search? > The plan is to eventually have an API that you can call to add your own custom tokenzizer/stemmer. But for now, you have to modify the code

Re: [sqlite] Different ROWID and PRIMARY KEY values

2007-03-12 Thread Gerry Snyder
Luca Moratto wrote: I already created all the indexes after table creation, but I believed that the constraint of primary key came copied also by "create table ... as ...)... Glad everything is working now. SQLite is so well designed that there must be a good reason why the

[sqlite] cast problems sqlite3

2007-03-12 Thread Tom Shaw
Help is appreciated. I have a table with integer columns rank, tot, det with values in tot and det and I want to put an integer percent (0-100) into rank UPDATE av_summary SET rank=(det/tot)*100; returns 0 I assume because the arithmetic is in integer UPDATE av_summary SET

Re: [sqlite] Re: Argh, this must be a very stupid question ...

2007-03-12 Thread Martin Jenkins
Stef Mientki wrote: so it was indeed a stupid question ! No, it was a well phrased question with a simple answer. A stupid question is when the message is "I CANT OPEN A DATABSE!!! WHY [EMAIL PROTECTED] IT WORK? IS IT A BUG!!!" and nothing else. ;) mj

[sqlite] additional functions in C

2007-03-12 Thread Jakub Ladman
Hi Friends Cause i am really blockheaded - is there somebody so good-hearted, who could write simple step by step directions how to add one simple function to SQLite? I have found so many inspirations and documentation seems to be very precise, but i do not understand it at its width. For

[sqlite] FTS: Custom Tokenizer / Stop Words

2007-03-12 Thread Ralf Junker
I wonder if there is some effort already under way to allow custom tokenizers by SQLite's full text search? I know that custom tokenizers are already on the developer's todo-list, but I would be interested to know if some progess has already been made. Custom tokenizers would be able solve a

RE: [sqlite] journal - "Unable to open the database file"

2007-03-12 Thread Allan, Mark
Yes it would appear that the file is open read only. I cannot open the file in any other program to attempt to change the contents or truncate it. It appears that the file is locked, but when using Unlocker to try to unlock it, it states that there is no locking handle on the file. Try to

Re: [sqlite] sqlite3_update_hook and transactions

2007-03-12 Thread Dan Kennedy
On Mon, 2007-03-12 at 10:51 +0100, Jef Driesen wrote: > I was planning to use the sqlite3_update_hook function to notify my GUI > about changes. The idea was that every part of the GUI can update itself > when a database change is detected. But during testing, I encountered > some problems with

Re: [sqlite] Different ROWID and PRIMARY KEY values

2007-03-12 Thread Luca Moratto
Gerry Snyder ha scritto: Luca Moratto wrote: Thanks Gerry, but I can Create my table in memory from an attached db, I can Insert new rows and I can Select the new rows and all values are correct, except for my key field that is 0. My Insert statement is INSERT INTO myTable VALUES (?,?,?);

[sqlite] sqlite3_update_hook and transactions

2007-03-12 Thread Jef Driesen
I was planning to use the sqlite3_update_hook function to notify my GUI about changes. The idea was that every part of the GUI can update itself when a database change is detected. But during testing, I encountered some problems with this approach together with transactions. When I group some

Re: [sqlite] UPDATE colomns based on their values

2007-03-12 Thread jose isaias cabrera
"P Kishor" wrote, On 3/12/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote: Ok, I have figured out that I do not have null values on the data of my table, even though I do have empty strings. Here is my schema, sqlite> sqlite> .schema CREATE TABLE LSOpenJobs ( id integer

Re: [sqlite] UPDATE colomns based on their values

2007-03-12 Thread P Kishor
On 3/12/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote: Ok, I have figured out that I do not have null values on the data of my table, even though I do have empty strings. Here is my schema, sqlite> sqlite> .schema CREATE TABLE LSOpenJobs ( id integer primary key, ProjID

Re: [sqlite] UPDATE colomns based on their values

2007-03-12 Thread jose isaias cabrera
Ok, I have figured out that I do not have null values on the data of my table, even though I do have empty strings. Here is my schema, sqlite> sqlite> .schema CREATE TABLE LSOpenJobs ( id integer primary key, ProjID integer, parent, children, login, cust, proj, PClass,