Re: [sqlite] Last insert in a table

[A.J.Millan]

Puneet, Dennis, Jay:

Thank a lot for yours replies.

Always is nice to have several views of a matter.  I'm agreeing also that 
the Dennis one is the most elegant answer. In my case it have an additional 
benefit, with that table I can also track the last modified row. Sometimes 
I need that also.

Cheers.

A.J.Millan 

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

[sqlite] Complex Query

[Derek Developer]

(2nd attempt... bounced back for some reason...)

In testing a my code I came across this example.
Could someone help me understand what this syntax is doing please
(from the Seinfeld demo database examples)

...m col
...h on
...w 20 17 6 23 6
...e on

Is this some form of typecasting?
This is how they are used


SELECT f.name as food, e1.name, e1.season, e2.name, e2.season 
FROM episodes e1, foods_episodes fe1, foods f, 
 episodes e2, foods_episodes fe2
WHERE 
  -- Get foods in season 4
  (e1.id = fe1.episode_id AND e1.season = 4) AND fe1.food_id = f.id
  -- Link foods with all other epsisodes
  AND (fe1.food_id = fe2.food_id) 
  -- Link with their respective episodes and filter out e1's season
  AND (fe2.episode_id = e2.id AND e2.season != e1.season)
ORDER BY f.name;

SELECT e.name AS Episode, COUNT(f.id) AS Foods 
FROM foods f 
JOIN foods_episodes fe on f.id=fe.food_id
JOIN episodes e on fe.episode_id=e.id
GROUP BY e.id
ORDER BY Foods DESC 
LIMIT 10;

SELECT 1 IN (1,2,3);
SELECT 2 IN (3,4,5);
SELECT COUNT(*) FROM foods WHERE type_id IN (1,2);
SELECT COUNT(*) FROM foods WHERE type_id
IN (SELECT id FROM food_types WHERE name='Bakery' OR name='Cereal');

SELECT name, 
(SELECT COUNT(id) FROM foods_episodes WHERE food_id=f.id) count 
FROM foods f ORDER BY count DESC LIMIT 10;

   
-
Be a better friend, newshound, and know-it-all with Yahoo! Mobile.  Try it now.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sorting NULs with Dynamic Typing

[Derek Developer]

In SQLite a NUL is nothing. Inserting a NUL into a column defined as Integer 
for example creates a zero length entry not an Integer of value zero.

How do traditional databases with static typing deal with this?
Do they check the column delaration and convert the NUL to a zero value upon 
entry?

How does SQLite handle sorting with a column that contains values (including 
zero) and NULs? Are the NULs converted to zero for the purposes of sorting?

   
-
Never miss a thing.   Make Yahoo your homepage.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sorting and Descending Index

[Martin Engelschalk]

Hello All,

I have to select data from a large table (several million records) in 
descending order and created an index for that purpose. However, sqlite 
seems not to use this index for selecting the data.

In the documentation of the "create index" - statement, i found the 
following sentence:

"Each column name can be followed by one of the "ASC" or "DESC" keywords 
to indicate sort order, but the sort order is ignored in the current 
implementation. Sorting is always done in ascending order."

However, the news for Version 3.3.0 of Jan 2006 says:

"Version 3.3.0 adds support for   DESC indices".

Is this a contradiction? Can sqlite use an index for order by ... desc - 
clauses? Or am i doing sonething wrong?

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

Re: [sqlite] Sorting NULs with Dynamic Typing

[Dan]

> How does SQLite handle sorting with a column that contains values  
> (including zero) and NULs? Are the NULs converted to zero for the  
> purposes of sorting?

A null value is considered less than all other values when sorting.

   http://www.sqlite.org/datatype3.html#comparisons

Dan.


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

Re: [sqlite] Sorting NULs with Dynamic Typing

[Igor Tandetnik]

"Derek Developer"
<[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> In SQLite a NUL is nothing. Inserting a NUL into a column defined as
> Integer for example creates a zero length entry not an Integer of
> value zero.
>
> How do traditional databases with static typing deal with this?

The same way. Every column can store a special NULL value, unless 
explicitly declared NOT NULL.

> Do they check the column delaration and convert the NUL to a zero
> value upon entry?

No.

> How does SQLite handle sorting with a column that contains values
> (including zero) and NULs?

NULL collates before any non-NULL value.

> Are the NULs converted to zero for the
> purposes of sorting?

No.

Igor Tandetnik 



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

Re: [sqlite] Performance degradation after upgrade to 3.5.x on slow flash cards

[Dima Dat'ko]

Thank you Dennis for your answer. I proceed with playing with the issue.

To whom it might be interesting here are timing results for a simple
function demonstrating the problem (please see the code bellow).

sqlite version | flash card type   | time from (1) to (2) [milliseconds]
---++--
 3.4.2   | SD SanDisk (fast)  |   4335
 3.4.2   | CF Toshiba (slow)  |   4401
 3.5.6   | SD SanDisk (fast)  | 43993
 3.5.6   | CF Toshiba (slow)  | 79568

If i comment the UPDATE query (lines from (A) to (B) the results are
the following:

sqlite version | flash card type   | time from (1) to (2) [milliseconds]
---++--
 3.4.2   | SD SanDisk (fast)  |   2025
 3.4.2   | CF Toshiba (slow)  |   2099
 3.5.6   | SD SanDisk (fast)  |   2119
 3.5.6   | CF Toshiba (slow)  |   2310

Here is the test function. Compiler options were all default, no
specific SQLITE or compiler flags used.
8<
const char* const g_ddlCreateSchema =
"CREATE TABLE tblParent("
"keyParent INTEGER PRIMARY KEY AUTOINCREMENT,"
"fkeySelectedChild INTEGER NOT NULL,"
"CreateTimeInMs INTEGER NOT NULL);"
"CREATE TABLE tblChildren("
"keyChild INTEGER PRIMARY KEY AUTOINCREMENT,"
"fkeyParent INTEGER,"
"CreateTimeInMs INTEGER NOT NULL,"
"UpdateTimeInMs INTEGER NOT NULL DEFAULT 0);";

const char* const g_sqlInsertChild  = "INSERT INTO
tblChildren(CreateTimeInMs) VALUES (?);";
const char* const g_sqlInsertParent = "INSERT INTO
tblParent(fkeySelectedChild, CreateTimeInMs) VALUES (?,?);";
const char* const g_sqlUpdateChild  = "UPDATE tblChildren SET
fkeyParent=?, UpdateTimeInMs=? WHERE keyChild=?;";

void test_gen(LPCWSTR aDbFileName)
{
bool ok = true;

DeleteFile(aDbFileName);

sqlite3* db = 0;
int rc = sqlite3_open16(aDbFileName, );
ok = ok && (rc == SQLITE_OK);
ok = ok && (db != 0);

ok = ok && (SQLITE_OK == sqlite3_exec(db, g_ddlCreateSchema, 0, 0, 0));
ok = ok && (SQLITE_OK == sqlite3_exec(db, "BEGIN;", 0, 0, 0));

sqlite3_stmt *stmt_InsertChild = 0, *stmt_InsertParent = 0,
*stmt_UpdateChild = 0;
ok = ok && (SQLITE_OK == sqlite3_prepare_v2(db, g_sqlInsertChild, -1,
_InsertChild, 0));
ok = ok && (SQLITE_OK == sqlite3_prepare_v2(db, g_sqlInsertParent,
-1, _InsertParent, 0));
ok = ok && (SQLITE_OK == sqlite3_prepare_v2(db, g_sqlUpdateChild, -1,
_UpdateChild, 0));


DWORD startTime = ::GetTickCount();
/*(1)*/
for(size_t i = 0; ok && i < 2000; ++i)
{
DWORD currentTime = ::GetTickCount();
ok = ok && (SQLITE_OK == sqlite3_bind_int(stmt_InsertChild, 1,
currentTime - startTime));
ok = ok && (SQLITE_DONE == sqlite3_step(stmt_InsertChild));
ok = ok && (SQLITE_OK == sqlite3_reset(stmt_InsertChild));
int new_child_id = ok ? (int)sqlite3_last_insert_rowid(db) : 0;
currentTime = ::GetTickCount();
ok = ok && (SQLITE_OK == sqlite3_bind_int(stmt_InsertParent, 1,
new_child_id));
ok = ok && (SQLITE_OK == sqlite3_bind_int(stmt_InsertParent, 2,
currentTime - startTime));
ok = ok && (SQLITE_DONE == sqlite3_step(stmt_InsertParent));
ok = ok && (SQLITE_OK == sqlite3_reset(stmt_InsertParent));
int new_parent_id = ok ? (int)sqlite3_last_insert_rowid(db) : 0;
currentTime = ::GetTickCount();
/*(A)*/
ok = ok && (SQLITE_OK == sqlite3_bind_int(stmt_UpdateChild, 1,
new_parent_id));
ok = ok && (SQLITE_OK == sqlite3_bind_int(stmt_UpdateChild, 2,
currentTime - startTime));
ok = ok && (SQLITE_OK == sqlite3_bind_int(stmt_UpdateChild, 3, 
new_child_id));
ok = ok && (SQLITE_DONE == sqlite3_step(stmt_UpdateChild));
ok = ok && (SQLITE_OK == sqlite3_reset(stmt_UpdateChild));
/*(B)*/
}

DWORD currentTime = ::GetTickCount();
/*(2)*/
ok = ok && (SQLITE_OK == sqlite3_finalize(stmt_InsertChild));
ok = ok && (SQLITE_OK == sqlite3_finalize(stmt_InsertParent));
ok = ok && (SQLITE_OK == sqlite3_finalize(stmt_UpdateChild));

ok = ok && (SQLITE_OK == sqlite3_exec(db, "COMMIT;", 0, 0, 0));
ok = (SQLITE_OK == sqlite3_close(db));
}
8<

As you can see in this test the version of 3.5.6 can be about 20 times
slower then 3.4.2. I'm wondering is not it a bug? Should I submit a
ticket?

So far I've tried several tricks with compiler flags and PRAGMAs with
no effect. What else can I try before getting to hack into SQLITE

Re: [sqlite] Sorting NULs with Dynamic Typing

[P Kishor]

On 3/19/08, Derek Developer <[EMAIL PROTECTED]> wrote:
> In SQLite a NUL is nothing. Inserting a NUL into a column defined as Integer 
> for example creates a zero length entry not an Integer of value zero.
>
>  How do traditional databases with static typing deal with this?

Probably all differently. Please, if you do research, do summarize
your findings and post them here as well as on the SQLite wiki. Maybe
someone has already done that.

>  Do they check the column delaration and convert the NUL to a zero value upon 
> entry?
>
>  How does SQLite handle sorting with a column that contains values (including 
> zero) and NULs? Are the NULs converted to zero for the purposes of sorting?
>
>

[12:35 AM] ~$ sqlite3
SQLite version 3.5.6
Enter ".help" for instructions
sqlite> create table a (b integer);
sqlite> insert into a (b) values (1);
sqlite> insert into a (b) values ('2');
sqlite> insert into a (b) values (NULL);
sqlite> insert into a (b) values ('');
sqlite> select count(*) from a;
4
sqlite> select rowid, * from a;
1|1
2|2
3|
4|
sqlite> select rowid, * from a order by b desc;
4|
2|2
1|1
3|
sqlite> select rowid, * from a order by b asc;
3|
1|1
2|2
4|
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Malformed database schema with SQLite version > 3.5.x

[drh]

MarcoN <[EMAIL PROTECTED]> wrote:
> Hello, everybody.
> 
> I have the following problem: I have an old project that uses a database
> created with an older SQLite library version.
> Now, since I updated SQLite to 3.5.5, I can't use the database anymore,
> because any query on the database tables returns:
> 
> SQLite error 11 - Malformed database schema - near ")": syntax error
> 

There is a syntax error in many of your VIEW definitions.
A typical example is _TestViewExtra where you have:

 MonthType IN (1, 2, )

There is an extra comma after the "2".  To fix this, I suggest
dropping all views from the database as follows:

   (1) Start the CLI:  sqlite3 baddatabase.db

   (2) Enter:  "PRAGMA writable_schema=ON".
   
   (3) Enter:  "select * from sqlite_master".  Ignore the error.

   (4) Enter:  "DELETE FROM sqlite_master WHERE type='view'"

   (5) Exit the CLI

Then go back and recreate your views using valid syntax.

There was apparently a bug in older versions of SQLite that allowed
the incorrect syntax to get through without raising an error.  That
bug has now been fixed, which made your database unreadable.

--
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_CONSTRAINT error after sqlite3_step

[Vincent Vega]

Dennis ,
Thanks a lot for this detailed explnation. I will follow your suggestions.
Bottom line, in terms of memory allocations, even if I get SQLITE_CONSTRAINT ,I 
should release the memory I got at sqlite3_mprintf() by calling sqlite3_free()  
and also release the memory I got at sqlite3_prepare_v2() by calling 
sqlite3_finalize() .




- Original Message 
From: Dennis Cote <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Wednesday, March 19, 2008 12:06:04 AM
Subject: Re: [sqlite] SQLITE_CONSTRAINT error after sqlite3_step

Vincent Vega wrote:
> Well , since I do need to avoid a case where duplicated tags are
> inserted ,I have added a treatment to the case of SQLITE_CONSTRAINT.

OK, but what you have shown doesn't do anything useful. If your Tag 
values must really be unique, then it is an error to insert a duplicate 
Tag value. There is really nothing to do except possibly report the 
error. If you believe the Tag value that are being inserted are supposed 
to be unique then you might want to rollback your transaction on a 
constraint error (which is what you get when you break the uniqueness 
constraint on the Tag column).

> I am just curious whether I need to call sqlite3_finalize(Statement)
> after sqlite3_step(Statement) returns SQLITE_CONSTRAINT so that the
> memory that was allocated at sqlite3_mprintf  be released ?
> 

Yes and no. You do need to finalize the statement to release resources 
allocated when the statement was prepared. In addition, you also need to 
call sqlite3_free() to release the memory allocated by 
sqlite3_mprintf(). These are separate allocations and require separate 
release calls.

> Here are the table defenitions :
> CREATE TABLE 'Inventory' (
> 'TagIndex' integer PRIMARY KEY,
> 'Tag' varchar(12) NOT NULL,
> ) ;
> CREATE UNIQUE INDEX InventoryIndex ON Inventory (Tag);
> 

The word unique between create and index is what causes the constraint 
error you are seeing. This only happens if you insert a duplicate Tag value.

> My code looks like this:
> 
> query = sqlite3_mprintf("Insert into Inventory (Tag) values (?)");

This allocates memory and saves a pointer to it in query.

> rc=sqlite3_prepare_v2(DB,query ,-1,,NULL);

This allocates a statement and saves a pointer to it in Statement.

> if (rc!=SQLITE_OK)
>error_print();
> 
> sqlite3_exec(db,"BEGIN;",NULL,NULL,);
> for (i=1;i<500;i ++)
> {
>rc=sqlite3_bind_text(Statement,1,list[i],-1,NULL);

This binds the i'th value in the array list to the parameter that will 
be inserted into the Tag column.

>if (rc!=SQLITE_OK)
>error_print();
>rc=sqlite3_step(Statement);

This executes the statement. Either inserting the value and assigning a 
new TagIndex and then returning SQLITE_DONE, or doing nothing and 
returning SQLITE_CONSTRAINT.

>if (rc!=SQLITE_DONE)
>{
>  if (rc==SQLITE_CONSTRAINT )  
>{

You end up here if your list has duplicate entries.

>  query = sqlite3_mprintf("Insert into Inventory (Tag) values 
> (?)");

This overwrites the pointer stored in query. The previous value is lost 
and memory is leaked. You must use a different pointer or call 
sqlite3_free() to release the old value first.

>  rc=sqlite3_prepare_v2(DB,query ,-1,,NULL);

This overwrites the statement pointer stored in Statement. The previous 
value is lost and memory is leaked. You must use a different pointer or 
call sqlite3_finalize() to release the old value first.

>  if (rc!=SQLITE_OK)
>error_print();
>}
>error_print();
>}
>sqlite3_reset(Statement) ;
> }
> sqlite3_finalize(Statement) ;

This releases the statement allocated by prepare above.

> 
> sqlite3_exec(db,"END;",NULL,NULL,) ;

The string query is never released. Call sqlite3_free() to do so.

I'm not sure what you are trying to accomplish when you detect the 
SQLITE_CONSTRAINT error returned from sqlite3_step(). Re-preparing the 
same SQL statement again will not correct anything. Your list has real 
duplicate values, you have told SQLite not to accept duplicate values, 
and it has told you that you have tried to enter a duplicate value. You 
really need to ignore the duplicate and continue (possibly reporting the 
  duplicate), or abort, fix your list to remove the duplicates, and then 
try again later.


This code should probably be changed to something like this depending 
upon exactly what you want to do if your list contains duplicates:

query = sqlite3_mprintf("Insert into Inventory (Tag) values (?)");
rc=sqlite3_prepare_v2(DB,query ,-1,,NULL);
if (rc!=SQLITE_OK)
error_print();

int abort = 0;
sqlite3_exec(db,"BEGIN;",NULL,NULL,);
for (i=1;i<500;i ++)
{
rc=sqlite3_bind_text(Statement,1,list[i],-1,NULL);
if (rc!=SQLITE_OK)
error_print();
rc=sqlite3_step(Statement);
if (rc!=SQLITE_DONE)
{
// SQLITE_CONSTRAINT error
abort = 1;

// uncomment this line 

Re: [sqlite] Sorting and Descending Index

[drh]

Martin Engelschalk <[EMAIL PROTECTED]> wrote:
> Hello All,
> 
> I have to select data from a large table (several million records) in 
> descending order and created an index for that purpose. However, sqlite 
> seems not to use this index for selecting the data.
> 
> In the documentation of the "create index" - statement, i found the 
> following sentence:
> 
> "Each column name can be followed by one of the "ASC" or "DESC" keywords 
> to indicate sort order, but the sort order is ignored in the current 
> implementation. Sorting is always done in ascending order."
> 
> However, the news for Version 3.3.0 of Jan 2006 says:
> 
> "Version 3.3.0 adds support for   DESC indices".
> 
> Is this a contradiction? Can sqlite use an index for order by ... desc - 
> clauses? Or am i doing sonething wrong?
> 

You have to enable decending indices using a pragma:

   PRAGMA legacy_file_format=OFF;

But a SELECT will use an index to sort the results in
decending order regardless of whether or not the index
is in decending order.  So that isn't your problem.
You are doing something else wrong.

Post your query, your table schema, and your index
definitions and we will have a look.

--
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] Performance degradation after upgrade to 3.5.x on slow flash cards

[Dima Dat'ko]

Thank you all who supports sqlite!

I'm impressed with your work. It happened so that I read the release
notes of the newest 3.5.7 only recently. I tried my test with this
version and the result is

sqlite version | flash card type   | time from (1) to (2) [milliseconds]
---++--
 3.5.7   | CF Toshiba (slow)  | 8242

ten times improvement compared to 3.5.6. still twice slower in this
particular test then 3.4.x but in my application the performance is
good again.

Thanks a lot.

Regards,
Dima Dat'ko


On Wed, Mar 19, 2008 at 3:29 PM, Dima Dat'ko <[EMAIL PROTECTED]> wrote:
> Thank you Dennis for your answer. I proceed with playing with the issue.
>
> To whom it might be interesting here are timing results for a simple
> function demonstrating the problem (please see the code bellow).
>
> sqlite version | flash card type   | time from (1) to (2) [milliseconds]
> ---++--
>  3.4.2   | SD SanDisk (fast)  |   4335
>  3.4.2   | CF Toshiba (slow)  |   4401
>  3.5.6   | SD SanDisk (fast)  | 43993
>  3.5.6   | CF Toshiba (slow)  | 79568
>
> If i comment the UPDATE query (lines from (A) to (B) the results are
> the following:
>
> sqlite version | flash card type   | time from (1) to (2) [milliseconds]
> ---++--
>  3.4.2   | SD SanDisk (fast)  |   2025
>  3.4.2   | CF Toshiba (slow)  |   2099
>  3.5.6   | SD SanDisk (fast)  |   2119
>  3.5.6   | CF Toshiba (slow)  |   2310
>
> Here is the test function. Compiler options were all default, no
> specific SQLITE or compiler flags used.
> 8<
> const char* const g_ddlCreateSchema =
>"CREATE TABLE tblParent("
>"keyParent INTEGER PRIMARY KEY AUTOINCREMENT,"
>"fkeySelectedChild INTEGER NOT NULL,"
>"CreateTimeInMs INTEGER NOT NULL);"
>"CREATE TABLE tblChildren("
>"keyChild INTEGER PRIMARY KEY AUTOINCREMENT,"
>"fkeyParent INTEGER,"
>"CreateTimeInMs INTEGER NOT NULL,"
>"UpdateTimeInMs INTEGER NOT NULL DEFAULT 0);";
>
> const char* const g_sqlInsertChild  = "INSERT INTO
> tblChildren(CreateTimeInMs) VALUES (?);";
> const char* const g_sqlInsertParent = "INSERT INTO
> tblParent(fkeySelectedChild, CreateTimeInMs) VALUES (?,?);";
> const char* const g_sqlUpdateChild  = "UPDATE tblChildren SET
> fkeyParent=?, UpdateTimeInMs=? WHERE keyChild=?;";
>
> void test_gen(LPCWSTR aDbFileName)
> {
>bool ok = true;
>
>DeleteFile(aDbFileName);
>
>sqlite3* db = 0;
>int rc = sqlite3_open16(aDbFileName, );
>ok = ok && (rc == SQLITE_OK);
>ok = ok && (db != 0);
>
>ok = ok && (SQLITE_OK == sqlite3_exec(db, g_ddlCreateSchema, 0, 0, 0));
>ok = ok && (SQLITE_OK == sqlite3_exec(db, "BEGIN;", 0, 0, 0));
>
>sqlite3_stmt *stmt_InsertChild = 0, *stmt_InsertParent = 0,
> *stmt_UpdateChild = 0;
>ok = ok && (SQLITE_OK == sqlite3_prepare_v2(db, g_sqlInsertChild, -1,
> _InsertChild, 0));
>ok = ok && (SQLITE_OK == sqlite3_prepare_v2(db, g_sqlInsertParent,
> -1, _InsertParent, 0));
>ok = ok && (SQLITE_OK == sqlite3_prepare_v2(db, g_sqlUpdateChild, -1,
> _UpdateChild, 0));
>
>
>DWORD startTime = ::GetTickCount();
> /*(1)*/
>for(size_t i = 0; ok && i < 2000; ++i)
>{
>DWORD currentTime = ::GetTickCount();
>ok = ok && (SQLITE_OK == sqlite3_bind_int(stmt_InsertChild, 1,
> currentTime - startTime));
>ok = ok && (SQLITE_DONE == sqlite3_step(stmt_InsertChild));
>ok = ok && (SQLITE_OK == sqlite3_reset(stmt_InsertChild));
>int new_child_id = ok ? (int)sqlite3_last_insert_rowid(db) : 0;
>currentTime = ::GetTickCount();
>ok = ok && (SQLITE_OK == sqlite3_bind_int(stmt_InsertParent, 1,
> new_child_id));
>ok = ok && (SQLITE_OK == sqlite3_bind_int(stmt_InsertParent, 2,
> currentTime - startTime));
>ok = ok && (SQLITE_DONE == sqlite3_step(stmt_InsertParent));
>ok = ok && (SQLITE_OK == sqlite3_reset(stmt_InsertParent));
>int new_parent_id = ok ? (int)sqlite3_last_insert_rowid(db) : 
> 0;
>currentTime = ::GetTickCount();
> /*(A)*/
>ok = ok && (SQLITE_OK == sqlite3_bind_int(stmt_UpdateChild, 1,
> new_parent_id));
>ok = ok && (SQLITE_OK == sqlite3_bind_int(stmt_UpdateChild, 2,
> currentTime - startTime));
>ok = ok && (SQLITE_OK == sqlite3_bind_int(stmt_UpdateChild, 3, 
> new_child_id));
>ok = ok && (SQLITE_DONE == 

Re: [sqlite] Complex Query

[Dennis Cote]

Derek Developer wrote:
> (2nd attempt... bounced back for some reason...)
> 
> In testing a my code I came across this example.
> Could someone help me understand what this syntax is doing please
> (from the Seinfeld demo database examples)
> 
> ...m col
> ...h on
> ...w 20 17 6 23 6
> ...e on
> 
> Is this some form of typecasting?

Someone already figured out that these are sqlite3 shell commands, 
abbreviated as much as possible. The shell can be downloaded from 
http://www.sqlite.org/download.html by clicking on the appropriate link 
(i.e. sqlite-3_5_7.zip for Windows).

These shell commands are described by the .help command as shown below.

SQLite version 3.5.7
Enter ".help" for instructions
sqlite> .help
.bail ON|OFF   Stop after hitting an error.  Default OFF
.databases List names and files of attached databases
.dump ?TABLE? ...  Dump the database in an SQL text format
.echo ON|OFF   Turn command echo on or off
.exit  Exit this program
.explain ON|OFFTurn output mode suitable for EXPLAIN on or off.
.header(s) ON|OFF  Turn display of headers on or off
.help  Show this message
.import FILE TABLE Import data from FILE into TABLE
.indices TABLE Show names of all indices on TABLE
.load FILE ?ENTRY? Load an extension library
.mode MODE ?TABLE? Set output mode where MODE is one of:
  csv  Comma-separated values
  column   Left-aligned columns.  (See .width)
  html HTML  code
  insert   SQL insert statements for TABLE
  line One value per line
  list Values delimited by .separator string
  tabs Tab-separated values
  tcl  TCL list elements
.nullvalue STRING  Print STRING in place of NULL values
.output FILENAME   Send output to FILENAME
.output stdout Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit  Exit this program
.read FILENAME Execute SQL in FILENAME
.schema ?TABLE?Show the CREATE statements
.separator STRING  Change separator used by output mode and .import
.show  Show the current values for various settings
.tables ?PATTERN?  List names of tables matching a LIKE pattern
.timeout MSTry opening locked tables for MS milliseconds
.width NUM NUM ... Set column widths for "column" mode


I'm not sure where all the extra periods you show are coming from. Each 
sqlite3 shell command begins with a single period. The commands you 
showed can be expanded for better readability as:

.mode column
.headers on
.width 20 17 6 23 6
.explain on

This puts the sqlite3 shell in column output mode with headers turned on 
for each column. The width command sets the column widths explicitly. 
These commands determine how the shell displays the results of SQL queries.

The explain command also changes the output mode to a column mode with 
headers and predetermined widths that are suitable for the output of 
"explain statement" queries. It would override the previous commands if 
entered as shown.

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

Re: [sqlite] Sorting and Descending Index

[Dennis Cote]

Martin Engelschalk wrote:
> 
> I have to select data from a large table (several million records) in 
> descending order and created an index for that purpose. However, sqlite 
> seems not to use this index for selecting the data.
> 
> In the documentation of the "create index" - statement, i found the 
> following sentence:
> 
> "Each column name can be followed by one of the "ASC" or "DESC" keywords 
> to indicate sort order, but the sort order is ignored in the current 
> implementation. Sorting is always done in ascending order."
> 
> However, the news for Version 3.3.0 of Jan 2006 says:
> 
> "Version 3.3.0 adds support for   DESC indices".
> 
> Is this a contradiction? Can sqlite use an index for order by ... desc - 
> clauses? Or am i doing sonething wrong?
> 

Martin,

I suspect the create index documentation is out of date. SQLite can use 
either an ascending or descending order index to order records in either 
  ascending or descending order. It simply scans the index from front to 
back, or back to front to get the required order.

This shows that either index order can be used.

sqlite> create table t1 (id, a);
sqlite> create table t2 (id, a);
sqlite> create index t1_a_asc on t1(a asc);
sqlite> create index t2_a_dsc on t2(a desc);
sqlite> explain query plan select * from t1 order by a desc;
0|0|TABLE t1 WITH INDEX t1_a_asc ORDER BY
sqlite> explain query plan select * from t2 order by a desc;
0|0|TABLE t2 WITH INDEX t2_a_dsc ORDER BY

If you post the actual SQL statements you use to create the table and 
index, and the query you are using, someone will be able to figure out 
why it isn't using the index.

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

Re: [sqlite] Complex Query

[BareFeet]

Hi Derek,

> .m col
> .h on
> .w 20 17 6 23 6
> .e on

They are just the abbreviated version of these dot commands:

.mode columns
.headers on
.width 20 17 6 23 6
.echo on

You can get info on each by typing ".help" from within the sqlite3  
command line utility.

Tom
BareFeet

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

Re: [sqlite] algorithm for adding columns to a table

[Derrell Lipman]

On Tue, Mar 18, 2008 at 1:03 PM, Virgilio Alexandre Fornazin
<[EMAIL PROTECTED]> wrote:
> Yes. I did this in my custom version of SQLite. If statement is ALTER TABLE,
>  and SQLite returns error, I check if it´s ALTER TABLE (t) MODIFY COLUMN ou
>  DROP COLUMN, doing the exact flow you did.

Thanks!

Derrell


>
>
>
>  -Original Message-
>  From: [EMAIL PROTECTED]
>  [mailto:[EMAIL PROTECTED] On Behalf Of Derrell Lipman
>  Sent: terça-feira, 18 de março de 2008 11:26
>  To: General Discussion of SQLite Database
>  Subject: [sqlite] algorithm for adding columns to a table
>
>  I could use another set of eyes or three to verify that this algorithm
>  makes sense.  I have legacy sqlite2 databases for which I need a
>  generic function to add columns to tables.  This is the pseudocode for
>  the function.  Am I missing anything?
>
>  In this pseudocode, the parameters are:
>
>   :tableName:
>  The table name being altered
>
>   :newColumnDefinition:
>  The complete text of the new column description, as if it were in a
>  CREATE TABLE command, e.g. "t TIMESTAMP DEFAULT '2008-03-18 10:08:47'"
>
>   :newColumnValue:
> The value to insert into the new column as we add the new column
>  to the table.
>
>  Of course, all queries need error checking which is not included in
>  the pseudocode.  Errors cause an immediate rollback.
>
>  Pseudocode follows...
>
>
>  // If anything fails, ensure we can get back to our original
>  query("begin;")
>
>  // Get the sql to generate the table
>  tableDef = query(
>"SELECT sql
>   FROM sqlite_master
>   WHERE tbl_name == :tableName:
> AND type = 'table';"
>  )
>
>  // Get the indexes associated with this table, excluding automatic indexes
>  indexes = query(
>"SELECT sql
>   FROM sqlite_master
>   WHERE tbl_name == :tableName:
> AND type = 'index'
> AND length(sql) > 0;"
>  )
>
>  // Get the triggers associated with this table
>  triggers = query(
>"SELECT sql
>   FROM sqlite_master
>   WHERE tbl_name == :tableName:
> AND type = 'trigger'
> AND length(sql) > 0;"
>  )
>
>  // Copy all of the data to a temporary table
>  query("CREATE TEMPORARY TABLE __t AS SELECT * FROM :tableName:;")
>
>  // Drop the table being altered
>  query("DROP TABLE :tableName:;")
>
>  // Copy the original table definition so we can modify it
>  sql = tableDef.sql;
>
>  // Find the trailing right parenthesis in the original table definition
>  p = strrchr(sql, ')');
>
>  // Where the right parenthesis was, append a comma and new column definition
>  *p++ = ',';
>  strcpy(p, :newColumnDefinition:);
>  strcat(p, ");");
>
>  // Recreate the table using the new definition
>  query(sql);
>
>  // Copy the data from our temporary table back into this table.
>  query("INSERT INTO :tableName: SELECT *, :newColumnValue: FROM __t;")
>
>  // We don't need the temporary table anymore
>  query("DROP TABLE __t;")
>
>  // Recreate the indexes
>  foreach index in indexes
>  {
> query(index.sql)
>  }
>
>  // Recreate the triggers (after having copied the data back to the table!)
>  foreach trigger in triggers
>  {
> query(trigger.sql)
>  }
>
>  query("commit;")
>
>
>  Thanks for any comments you can provide!
>
>  Derrell
>  ___
>  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
>



-- 
"There is nothing more difficult to plan, more doubtful of success,
nor more dangerous to manage than the creation of a new system. For
the initiator has the enmity of all who would profit by the
preservation of the old system and merely lukewarm defenders in those
who would gain by the new one." --Machiavelli, 1513
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance degradation after upgrade to 3.5.x on slow flash cards

[Dennis Cote]

Dima Dat'ko wrote:
> I proceed with playing with the issue.
> 
> sqlite version | flash card type   | time from (1) to (2) [milliseconds]
> ---++--
>  3.4.2   | SD SanDisk (fast)  |   4335
>  3.4.2   | CF Toshiba (slow)  |   4401
>  3.5.6   | SD SanDisk (fast)  | 43993
>  3.5.6   | CF Toshiba (slow)  | 79568
> 
> If i comment the UPDATE query (lines from (A) to (B) the results are
> the following:
> 
> sqlite version | flash card type   | time from (1) to (2) [milliseconds]
> ---++--
>  3.4.2   | SD SanDisk (fast)  |   2025
>  3.4.2   | CF Toshiba (slow)  |   2099
>  3.5.6   | SD SanDisk (fast)  |   2119
>  3.5.6   | CF Toshiba (slow)  |   2310
> 
> 
> As you can see in this test the version of 3.5.6 can be about 20 times
> slower then 3.4.2. I'm wondering is not it a bug? Should I submit a
> ticket?
> 

Yes, this does look like a pretty severe performance regression in 
SQLite 3.5.6. I think you should create a ticket so the developers can 
look into it.

You have shown quite clearly that it is the update statement that is at 
fault. I will take a look at the VDBE code generated by the update 
statement to see if anything jumps out at me.

Dennis Cote

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

Re: [sqlite] SQL logic error or missing database in version 3.5.6(Bug???)

[Steve Topov]

This is SQLite version 3 database file. I checked header. And I can read
it with version 3.3.5 dll.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Tuesday, March 18, 2008 5:30 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQL logic error or missing database in version
3.5.6(Bug???)

"Steve Topov" <[EMAIL PROTECTED]> wrote:
> Hello,
>  
> Recently I upgraded SQLite to version 3.5.6 and discovered that my
> program can't work anymore with some database files. Sqlite3_open
> returns OK, but when I am trying to execute any SQL statement it
returns
> "SQL logic error or missing database". 
> For example sqlite3_prepare returns 1 instead of 0. Same for
> sqlite3_execute.
> I do not think it is my code that causes the problem because I can
just
> switch SQLite dll from version 3.5.6 to version 3.3.5 and everything
> works fine. 
> I do not know the version of SQLite the database file in question was
> created with. Few database files created with version 3.3.5 does not
> have this problem.
> I tried to upgrade to the version 3.5.7 - same result. 
>  


SQLite versions 3.5.6 and 3.5.7 are suppose to be able to read
and write any database written by any prior version of SQLite
going back to version 3.0.0.

Perhaps you have a version 2 database file?

If you database file is named XYZ.db, what does this command
show you:

od -c XYZ.db | head

--
D. Richard Hipp <[EMAIL PROTECTED]>



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

[sqlite] 3.5.7 compile failure, with SQLITE_OMIT_VIEW

[Ken]

While attempt to compile 3.5.7 the following was generated:

gcc -DSQLITE_OMIT_VIEW -DOS_UNIX=1 -I. -I./src -DNDEBUG -DSQLITE_THREADSAFE=1 
-DSQLITE_THREAD_OVERRIDE_LOCK=-1 -c ./sqlite3.c  -fPIC -DPIC -o .libs/sqlite3.o
./sqlite3.c: In function ‘sqlite3Insert’:
./sqlite3.c:57158: error: syntax error before numeric constant
./sqlite3.c:57189: error: invalid lvalue in assignment
./sqlite3.c: In function ‘sqlite3Update’:
./sqlite3.c:66320: error: syntax error before numeric constant
./sqlite3.c:66357: error: invalid lvalue in assignment


Also Attempting to configure/compile in a different directory than the 
makefile.in resulted in a cp failure while creating the amalgamated source.

Ken




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

[sqlite] Slow List

[Ken]

Is there something wrong with the sqlite list? I sent emails to the list over 
an hour ago and still nothing is posted?

Thanks,
Ken



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

[sqlite] is updating Blobs different than inserting??

[C S]

hi all. wondering if there is any difference in
updating blobs vs inserting? for instance if you have
a small blob and when you update the blob increases in
size and also going from larger to smaller. 

is there anything special that i need to check for or
do to make sure i dont lose any data? 

thanks!


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is updating Blobs different than inserting??

[Igor Tandetnik]

C S <[EMAIL PROTECTED]> wrote:
> hi all. wondering if there is any difference in
> updating blobs vs inserting? for instance if you have
> a small blob and when you update the blob increases in
> size and also going from larger to smaller.
>
> is there anything special that i need to check for or
> do to make sure i dont lose any data?

No.

Igor Tandetnik 



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

Re: [sqlite] 3.5.7 compile failure, with SQLITE_OMIT_VIEW

[Matthew L. Creech]

On Wed, Mar 19, 2008 at 12:49 PM, Ken <[EMAIL PROTECTED]> wrote:
>
>  Also Attempting to configure/compile in a different directory than the 
> makefile.in
> resulted in a cp failure while creating the amalgamated source.
>

This should be fixed in the latest CVS.  I also cleaned up the header
generation, since it was using the default [empty] config.h rather
than the one output by the configure script.  Let me know if you find
any other issues.  Thanks!

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