Re: [sqlite] How to Modify Table

[Slater, Chad]

Sqlite does not support modifying the unique constraints on a table:

http://www.sqlite.org/lang_altertable.html

So you probably need to do it the 'ol fashioned way:

BEGIN;

CREATE TABLE Vehicles_new (
  VehicleID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  FK_CustomerID INTEGER NOT NULL,
  VehicleNumber INTEGER NOT NULL,
  VehicleTitle TEXT,
  VehicleMake TEXT,
  VehicleModel TEXT,
  VehicleYear TEXT,
  VehicleVIN TEXT,
  VehicleDescription TEXT,
  Active INTEGER DEFAULT 1 NOT NULL,
  CreateDate DATE DEFAULT (date('now','localtime')) NOT NULL,
  UNIQUE (FK_CustomerID, VehicleID));

INSERT INTO Vehicles_new
   SELECT * FROM Vehicles;

DROP TABLE Vehicles;

ALTER TABLE Vehicles_new RENAME TO Vehicles;

COMMIT TRANSACTION;


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of jonwood
Sent: Wednesday, November 12, 2008 8:08 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] How to Modify Table


My database contains the following table:

m_Database.ExecNonQuery(_T("CREATE TABLE Vehicles (")
  _T("VehicleID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,")
  _T("FK_CustomerID INTEGER NOT NULL,")
  _T("VehicleNumber INTEGER NOT NULL,")
  _T("VehicleTitle TEXT,")
  _T("VehicleMake TEXT,")
  _T("VehicleModel TEXT,")
  _T("VehicleYear TEXT,")
  _T("VehicleVIN TEXT,")
  _T("VehicleDescription TEXT,")
  _T("Active INTEGER DEFAULT 1 NOT NULL,")
  _T("CreateDate DATE DEFAULT (date('now','localtime')) NOT NULL,")
  _T("UNIQUE (FK_CustomerID, VehicleID))"));

I would like to change the last line to instead be:

  _T("UNIQUE (FK_CustomerID, VehicleNumber))"));

Is there any way to make this change to the existing table without
losing
data in the table?

Thanks for any suggestions!
-- 
View this message in context:
http://www.nabble.com/How-to-Modify-Table-tp20474500p20474500.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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

[sqlite] DISTINCT clause bug in 3.6.4?

[Slater, Chad]

Hello,

I'm working on upgrading from sqlite 3.5.7 to 3.6.4 and while running
some regression unit tests in my own app I noticed a couple failures.
Upon further investigation it looks like either a bug has been
introduced into sqlite between 3.5.8 and 3.6.4 or my query is wrong. 

Here's some sql to reproduce the issue:

BEGIN;
CREATE TABLE A (id INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT);
INSERT INTO A VALUES(1,'123');
INSERT INTO A VALUES(2,'456');


CREATE TABLE B (id INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT);
INSERT INTO B VALUES(1,1);
INSERT INTO B VALUES(2,2);

CREATE TABLE A_B (B_id INTEGER NOT NULL, A_id INTEGER);
INSERT INTO A_B VALUES(1,1);
INSERT INTO A_B VALUES(2,2);
COMMIT;

The query that I'm executing:

SELECT DISTINCT
   CASE 
  WHEN B.val = 1 THEN 'XYZ' 
  ELSE A.val 
   END AS Col1
FROM B  
LEFT OUTER JOIN A_B ON B.id = A_B.B_id  
LEFT OUTER JOIN A ON A.id = A_B.A_id
ORDER BY Col1 ASC;

I'm expecting the query to return 456 followed by XYZ. But instead it
returns 123 followed by 456. If I remove the DISTINCT clause it returns
what I'm expecting but that doesn't seem like it should matter. I
searched for bugs using the timeline in the wiki but didn't see anything
related to DISTINCT. I'm not sure where else to look...

Is this a bug in sqlite or my query?



Thanks in advance,


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

[sqlite] Sun acquires MySQL

[Slater, Chad]

Or this link if your mail client breaks it up:

http://tinyurl.com/2qqaa9



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] sqlite3_update_hook

[Slater, Chad]

Hello,

I'm trying to use the update hook functionality. I have lookup (aka
join) tables that provide many-to-many relationships between rows in
other tables. The problem is when I get the delete notification for the
join tables the rowid is not useful in that context. I really need to
know the values of the other columns in these tables but I can't select
from within the update hook callback.

I'm using triggers to enforce FK constraints and they work great. Is
there any way I can use triggers to get the values of these rows before
the delete happens (e.g. call a user-defined function in C)?

Any other ideas?


Chad

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Performance Question

[Slater, Chad]

I overly simplified my example. I'm actually selecting columns from
table B and C which is why I had this in the where clause:

AND ( JoinAToB.B_id = B.id ) 
AND ( JoinAToB.A_id = A.id )
...

Converting those to explicit JOIN clauses fixed the problem.

Thanks for your help!


-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 12, 2007 4:10 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Performance Question

Slater, Chad wrote:
> Hello,
>
> I'm having trouble with the performance of one of my queries and my
"sql
> kung fu" is limited. Any help with this problem would be greatly
> appreciated
>
> Here's a stripped down version of the tables I'm dealing with:
>
> CREATE TABLE A ( 
> id INTEGER PRIMARY KEY AUTOINCREMENT,
> name TEXT
> )
>
> CREATE TABLE B ( 
> id INTEGER PRIMARY KEY AUTOINCREMENT,
> name TEXT
> )
>
> CREATE TABLE C ( 
> id INTEGER PRIMARY KEY AUTOINCREMENT,
> name TEXT
> )
>
> CREATE TABLE JoinAToB ( 
> A_id INTEGER NOT NULL REFERENCES A ( id ), 
> B_id INTEGER NOT NULL  REFERENCES B ( id ), 
> UNIQUE( A_id, B_id )  
> )
>
> CREATE TABLE JoinAToC ( 
> A_id INTEGER NOT NULL  REFERENCES A ( id ), 
> C_id INTEGER NOT NULL  REFERENCES C ( id ), 
> UNIQUE( A_id, C_id )  
> )
>
> The following query takes so long I end up killing the app before the
> query returns:
>
> SELECT DISTINCT A.id
>   FROM A,  
>C,
>B,
>JoinAToB,
>JoinAToC
>  WHERE 
>( ( ( JoinAToB.B_id IN ( 1 ) ) 
>AND ( JoinAToB.B_id = B.id ) 
>AND ( JoinAToB.A_id = A.id ) )  
>
> OR ( ( JoinAToC.C_id IN  ( 1 ) ) 
>AND ( JoinAToC.C_id = C.id ) 
>AND ( JoinAToC.A_id = A.id ) ) ) ;
>
>
> Table A has approx 13,000 rows
> Table B has 15 rows
> Table C has 5 row
> JoinTableAToB has 11 rows
> JoinTableAToC has approx 450 rows
>
> If I execute either of these queries separately they are very fast:
>
> SELECT DISTINCT A.id
>   FROM A, B,
>JoinAToB
>  WHERE 
>JoinAToB.B_id IN ( 1 ) AND JoinAToB.B_id = B.id AND
JoinAToB.A_id
> = A.id ;
>
>
> SELECT DISTINCT A.id
>   FROM A,  
>C,
>JoinAToC
>  WHERE 
>   JoinAToC.C_id IN  ( 1 ) AND JoinAToC.C_id = C.id AND
JoinAToC.A_id
> = A.id ;
>
>
> Adding the OR clause to combine the results seems to be the culprit
but
> I don't know why...
>
Chad,

You seem to be overly complicating the matter. Your query

SELECT DISTINCT A.id
  FROM A, B,
   JoinAToB
 WHERE 
   JoinAToB.B_id IN ( 1 ) AND JoinAToB.B_id = B.id AND JoinAToB.A_id
= A.id ;


is the same as

SELECT A_id
  FROM JoinAToB
 WHERE JoinAToB.B_id = 1;


Your table JoinAToB relates some A ids to some B ids. You don't need to 
join this to the tables A and B to do a query on the ids in that table. 
Similarly arguments apply to your table JoinAToC.

It looks like you are trying to get all the A ids that are referenced by

these two tables where the B id is 1 or the C id is 1. In SQL this is:

SELECT A_id
FROM JoinAToB
WHERE JoinAToB.B_id = 1
UNION
SELECT A_id
FROM JOINAToC
WHERE JoinAToC.C_id = 1

HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Performance Question

[Slater, Chad]

Hello,

I'm having trouble with the performance of one of my queries and my "sql
kung fu" is limited. Any help with this problem would be greatly
appreciated

Here's a stripped down version of the tables I'm dealing with:

CREATE TABLE A ( 
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
)

CREATE TABLE B ( 
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
)

CREATE TABLE C ( 
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
)

CREATE TABLE JoinAToB ( 
A_id INTEGER NOT NULL REFERENCES A ( id ), 
B_id INTEGER NOT NULL  REFERENCES B ( id ), 
UNIQUE( A_id, B_id )  
)

CREATE TABLE JoinAToC ( 
A_id INTEGER NOT NULL  REFERENCES A ( id ), 
C_id INTEGER NOT NULL  REFERENCES C ( id ), 
UNIQUE( A_id, C_id )  
)

The following query takes so long I end up killing the app before the
query returns:

SELECT DISTINCT A.id
  FROM A,  
   C,
   B,
   JoinAToB,
   JoinAToC
 WHERE 
   ( ( ( JoinAToB.B_id IN ( 1 ) ) 
   AND ( JoinAToB.B_id = B.id ) 
   AND ( JoinAToB.A_id = A.id ) )  

OR ( ( JoinAToC.C_id IN  ( 1 ) ) 
   AND ( JoinAToC.C_id = C.id ) 
   AND ( JoinAToC.A_id = A.id ) ) ) ;


Table A has approx 13,000 rows
Table B has 15 rows
Table C has 5 row
JoinTableAToB has 11 rows
JoinTableAToC has approx 450 rows

If I execute either of these queries separately they are very fast:

SELECT DISTINCT A.id
  FROM A, B,
   JoinAToB
 WHERE 
   JoinAToB.B_id IN ( 1 ) AND JoinAToB.B_id = B.id AND JoinAToB.A_id
= A.id ;


SELECT DISTINCT A.id
  FROM A,  
   C,
   JoinAToC
 WHERE 
  JoinAToC.C_id IN  ( 1 ) AND JoinAToC.C_id = C.id AND JoinAToC.A_id
= A.id ;


Adding the OR clause to combine the results seems to be the culprit but
I don't know why...


Regards,


Chad

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Performance Question: Ordering of columns

[Slater, Chad]

Hello,

Does the ordering of columns in a table have any impact on performance? 

Chad

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Performance Question

[Slater, Chad]

Hello,

Consider the following lookup table definition:

CREATE TABLE foobar (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
table1_id INTEGER NOT NULL REFERENCES table1,
table2_id INTEGER NOT NULL REFERENCES table2
);

The id primary key column is not necessary for anything in my
application. But I've heard that some database implementations recommend
the primary key for performance reasons. Is this true for sqlite?


TIA,


Chad

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Multiple prepared statements work on Windows... fails on Mac OS X

[Slater, Chad]

Hello,
 

I'm using two prepared statements in a block of cross platform C++ code
like this (very roughly):
 

{
sqlite3_stmt * pstmt1 = NULL;
sqlite3_stmt * pstmt2 = NULL;
 
pstmt1 = PrepareAndBind(...); // Prepare and bind one statement
pstmt2 = PrepareAndBind(...); // Prepare and bind a different
statement

sqlite3_step(pstmt1); // Returns SQLITE_ROW (as expected)
sqlite3_step(pstmt2); // Returns SQLITE_DONE on Mac OS X  but
returns SQLITE_ROW on Windows (same database. same queries)

sqlite3_finalize(pstmt1);
sqlite3_finalize(pstmt2);
 
}
 

The second step function call returns SQLITE_DONE on Mac OS X (when it
should have found records and returned SQLITE_ROW). On Windows, this
always works (both step function calls return SQLITE_ROW and I can
iterate through both sets of records using step until finished).
 
To work around this so it works on both platforms, I iterate through the
records using step for pstmt1 and call sqlite3_finalize before calling
sqlite3_step on pstmt2. This solution works on both platforms.
 
This could easily be a bug in my code (I have built some light wrappers
to accomplish most of this). But I have copied the same database from
Mac to Windows and ran the same queries to make sure the data is in the
db and the queries work fine. And I've commented out one prepared
statement or the other and both return results. But as soon as I
uncomment the other statement, it stops returning rows on the Mac as
I've described above.
 
Are there any known issues with using multiple prepared statements like
I have done? Am I doing something wrong (maybe I should be calling
sqlite3_reset somewhere along the way?)

[sqlite] LIKE operator with prepared statements

[Slater, Chad]

Is it possible to use the LIKE operator with a prepared statement? 

I'm trying to build a query that uses binding for the text in the LIKE
operation as follows:

SELECT x from y WHERE y.x LIKE %?% ;

...And binding text to the positional parameter in hopes to get:

SELECT x from y WHERE y.x LIKE %SomeText% ;

But it results in a sql parse error. Is %Q and sqlite3_mprintf my only
option here?



Chad