[sqlite] SQLiteManager 1.1.0 Released
SQLiteManager is a multilingual Web-based tool to manage SQLite databases. Changes: This new release allows SQLiteManager to use SQLite database version 2 or 3. You must just load 'PDO' and 'PDO-SQLITE' extension from PECL, and set the correct version to use in the global configuration file : ./include/user_defined.inc.php You send me your experience with this tool, bugs and feature request! Regards Frédéric HENNINOT <[EMAIL PROTECTED]> http://www.sqlitemanager.org signature.asc Description: Ceci est une partie de message =?ISO-8859-1?Q?num=E9riquement?= =?ISO-8859-1?Q?_sign=E9e=2E?=
Re: [sqlite] Only 128 inserts with NULL value for INTEGER PRIMARY KEY
Tom Deblauwe wrote: D. Richard Hipp wrote: What happens when you try to insert the 129th value? I tried now the following: add entries with NULL => ok until 128 when I first add index 100, and then add values with NULL as index, it stops inserts until 128, then I get the message again about primary key must be unique. If I do a MAX(iID) then I get the value 127, but when I do a select * then I get this: 128 | 100 | 101 | 102 | 103 | 104 | 105 | 106 | 107 | 108 | 109 | 110 | 111 | 112 | 113 | 114 | 115 | 116 | 117 | 118 | 119 | 120 | 121 | 122 | 123 | 124 | 125 | 126 | 127 | When I start at index 129 and then add with NULL, it goes ok, until 384. Maybe it has something to do with padding or something? Regards, Tom,
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?
[sqlite] Tiger: Error: file is encrypted or is not a database
Hello, I try to look into the sqlite database in Mac OS X Tiger at: /.Spotlight-V100/ I cannot dump any data or schema and the file magic is not containing sqlite. The data is not encrypted. It looks like the file is incompatible with sqlite3. Any ideas how to find out what Tiger is storing? Helmut Tschemernjak
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
Re: [sqlite] ON_DELETE_CASCADE constraint is not working
--- Ajay <[EMAIL PROTECTED]> wrote: > 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 > > > Regards, > > Ajay Sonawane > > __ Discover Yahoo! Stay in touch with email, IM, photo sharing and more. Check it out! http://discover.yahoo.com/stayintouch.html
Re: [sqlite] How to create an empty sqlite3 database?
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
Re: [sqlite] Only 128 inserts with NULL value for INTEGER PRIMARY KEY
D. Richard Hipp wrote: What happens when you try to insert the 129th value? I just tried inserting 10 rows using 2.8.16 and a NULL integer primary key and everything worked great. I get the error message that the integer primary key must be unique. It seems that when the new number is generated, it first sets the cursor to the last element, and then does keyToInt, and then reads the value. This goes fine until element 128 is written. After it is written, it goes to the last element, but I get this value: 2130706560(0111 1000) again! So after keyToInt this becomes 127, it adds one, and then it uses 128 again as integer primary key, and then it concludes that the primary key must be unique. regards, Tom,
Re: [sqlite] Only 128 inserts with NULL value for INTEGER PRIMARY KEY
On Mon, 2005-06-06 at 13:02 +0200, Tom Deblauwe wrote: > Hello, > > I have the problem that I can only insert 128 values into my databases What happens when you try to insert the 129th value? I just tried inserting 10 rows using 2.8.16 and a NULL integer primary key and everything worked great. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Only 128 inserts with NULL value for INTEGER PRIMARY KEY
Hello, I have the problem that I can only insert 128 values into my databases if I use NULL als value for the INTEGER PRIMARY KEY field. If I supply myself an unique number, then there is no problem to add more than 128 records! I am running 2.8.16, on an embedded platform, with 32 bit pointers, and no native 64bit integers. What would be the problem? I'd appreciate any help, thanks! regards, Tom,
[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
Re: [sqlite] How to create an empty sqlite3 database?
Hi, just do an sqlite3_open() on a non-existing file. See http://www.sqlite.org/capi3ref.html#sqlite3_open Martin Marco Bambini schrieb: 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
[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.
