RE: [sqlite] ON_DELETE_CASCADE constraint is not working
Hello there, Will you please tell me when SQLite will be supporting FOREIGN KEYS and ON_DELETE_CASCADE? Where can I get more information about version and what's new feature? As well as Can someone guide me about how to write and fire trigger that could do work of "ON_DELETE_CASCADE" Regards, Ajay Sonawane > Hello there, > > I have two tables and used constraint ON_DELETE_CASCADE in one of the table > to delete records from second table if records from first table are deleted > which foreign key points to first table. I'm using SQLiteExplorer to > manipulate tables. But it is not working properly. I found all records of > first table as it is though record of foreign key is deleted from second > table. > > Let me know solution. Unfortunately, foreign keys are not supported yet. You can add triggers to achieve the same functionality, although it is not as conveniant. http://www.sqlite.org/omitted.html > __ Discover Yahoo! Stay in touch with email, IM, photo sharing and more. Check it out! http://discover.yahoo.com/stayintouch.html
Re: [sqlite] Tiger: Error: file is encrypted or is not a database
On Jun 6, 2005, at 6:01 PM, Helmut Tschemernjak wrote: Just do a less on the .db files and you can see the information is in clear text. I will investigate into it a little bit more. I did on one file, but it was not clear text. Do you see a SQLite header on the files?
Re: [sqlite] Tiger: Error: file is encrypted or is not a database
Will Leshner wrote: On Jun 6, 2005, at 7:36 AM, Helmut Tschemernjak wrote: The data is not encrypted. What makes you think it is a SQLite database (a slightly rhetorical question as I'm pretty sure I know what makes you think that). But, and this isn't rhetorical, what makes you think it isn't encrypted? Just do a less on the .db files and you can see the information is in clear text. I will investigate into it a little bit more.
Re: [sqlite] Tiger: Error: file is encrypted or is not a database
On Jun 6, 2005, at 7:36 AM, Helmut Tschemernjak wrote: The data is not encrypted. What makes you think it is a SQLite database (a slightly rhetorical question as I'm pretty sure I know what makes you think that). But, and this isn't rhetorical, what makes you think it isn't encrypted?
Re: [sqlite] How to create an empty sqlite3 database?
It appears that SQLite 3 doesn't actually do anything until a table is created. Try this: sqlite3 db SQLite version 3.2.1 Enter ".help" for instructions create table x (y); drop table x; .quit >>> [EMAIL PROTECTED] 6/6/2005 9:31:26 AM >>> I don't seem to get that. Maybe you're using an old version. [EMAIL PROTECTED]:~> ls db ls: db: No such file or directory [EMAIL PROTECTED]:~> touch db [EMAIL PROTECTED]:~> ls -s db 0 db [EMAIL PROTECTED]:~> sqlite3 db SQLite version 3.2.1 Enter ".help" for instructions sqlite> select * from sqlite_master; sqlite> .quit [EMAIL PROTECTED]:~> ls -s db 0 db [EMAIL PROTECTED]:~> sqlite db SQLite version 2.8.15 Enter ".help" for instructions sqlite> select * from sqlite_master; sqlite> .quit [EMAIL PROTECTED]:~> ls -s db 0 db --- Marco Bambini <[EMAIL PROTECTED]> wrote: > What is the best way to create an empty sqlite3 database? > I mean without tables and rows inside it. > > The problem is that if I create a 0 length file, when I try to open > it with sqlite3_open I get the error "file is encrypted or is not a > database". > Maybe a possible solution could be to manually write the SQLITE3 > header into the file, but is it safe? > > Thanks, > Marco Bambini > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] ON_DELETE_CASCADE constraint is not working
Hello there, I have two tables and used constraint ON_DELETE_CASCADE in one of the table to delete records from second table if records from first table are deleted which foreign key points to first table. I'm using SQLiteExplorer to manipulate tables. But it is not working properly. I found all records of first table as it is though record of foreign key is deleted from second table. Let me know solution. Regards, Ajay Sonawane
[sqlite] How to create an empty sqlite3 database?
What is the best way to create an empty sqlite3 database? I mean without tables and rows inside it. The problem is that if I create a 0 length file, when I try to open it with sqlite3_open I get the error "file is encrypted or is not a database". Maybe a possible solution could be to manually write the SQLITE3 header into the file, but is it safe? Thanks, Marco Bambini
Re: [sqlite] Newbie sql: query and joining more than two tables
Thanks for your suggestions! On Monday 06 June 2005 05.22, Ulrik Petersen wrote: > Hi Karim, > > Cláudio Leopoldino wrote: > > You may use EXPLAIN clause and verify the reazon... > > > > Cláudio > > > >> Hi! > >> > >> I hope to get some feedback whether the query time is what I should > >> expect. > >> Running this query below takes several seconds - typically 1-3s. > >> > >> SELECT package.id, package.name, package.description, > >> package.size, package.latest, version.version > >> FROMcategory, package, version > >> WHERE package.idCategory = category.id > >> AND category.name = '" + category + "'" > >> AND version.idPackage = package.id " > >> ORDER BY lower( package.name ); > >> > >> The three tables are like this: > >> CREATE TABLE category ( id INTEGER UNIQUE, > >> name VARCHAR(32) ); > >> CREATE INDEX index_name ON category ( name ); > >> > >> CREATE TABLE package ( id INTEGER UNIQUE, > >> idCategory INTEGER, > >> name VARCHAR(32), > >> latest VARCHAR(32), > >> description > >> VARCHAR(255), > >> size VARCHAR(32), > >> keyword VARCHAR(32)); > >> CREATE INDEX index_name ON package ( name ); > >> > >> CREATE TABLE version ( id INTEGER UNIQUE, > >> idPackage INTEGER, > >> version VARCHAR(32), > >> date VARCHAR(32)); > >> > >> The table category has 136 rows, package 9379 rows and version 19369 > >> rows. > >> > >> Regards, > >> /Karim > > A couple of points: > > 1) You may wish to say "INTEGER PRIMARY KEY" rather than "INTEGER > UNIQUE". The reason can be read here: > > http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning > > (search for "INTEGER PRIMARY KEY"). First I populate a temporary table with has "id INTEGER PRIMARY KEY" then move all rows to this table. Also by using INTEGER UNIQUE it is automatically indexed. http://www.sqlite.org/lang_createtable.html says: "... The UNIQUE constraint causes an index to be created on the specified columns. ..." > > 2) I don't know if this will help, but try moving the > > category.name = '" + category + "'" > > term to the front of the WHERE clause. Gives a slight improvement of ~0.10s. > 3) Have you read Dr. Hipp's slides from PHP2004? > > http://www.sqlite.org/php2004/page-001.html > > On slide 48, it starts talking about how to organize your WHERE clauses > for using indexes: > > http://www.sqlite.org/php2004/page-048.html Yes, I've read the slides. Optimizing joins for two tables is easy and fast - 0.01s. But taking the found rowid and looking up a row on third table is slow... > HTH > > Ulrik P.
