mike cariotoglou wrote: > ----- Original Message ----- > BTW, I have some comments on your database sample: > > 1. the bug was triggered by the definition of foreign keys. you use > the syntax: > > create table x( > device_id integer references Device > > it would be more appropriate to do this: > > create table x( > device_id integer references Device(Device_ID) > > this gives more complete information for the schema.
The syntax I use is SQL:1999 standard. If no specific table column is listed in the references clause it means that named field references the primary key of the referenced table. Anyway, these constraints are ignored by SQLite (At least for now. It looks like there is some code in SQLite to recognize and process foreign key constraints.), and serve only as documentation to others using the database. > also, I noticed that you use mixed semantics in your column typing: > > Integer > Integer(5) > Varchar > varchar(32) > > If you plan to use the strong typing of SqliteExplorer, you should be > more consistent, and use : > > integer > varchar(32) I don't need (or want) any typing rules different than SQLite. These types were names were selected relatively long ago, when SQLite was completely typeless and basically ignored any column type information supplied. They were chosen primarily for documentation purposes. I simply used integer for all fields that held numeric values. The cases where there was a constraint on the length of the number (enforced by my application, not SQLite) were documented with the (number) clause. These should probably be replaced with NUMERIC(n) to comply with SQL:1999 standard since a precision clause is not allowed for integer types. Similarly, I used varchar for all string valued fields. Since SQLite doesn't enforce a length limit on strings, it seems to me that varchar is the perfect description of its behavior. It stores an unpadded variable length string with no length limit. I added the specific length limit to document fields where my application enforced a maximum length on the field. > what is the meaning of varchar without a size ? did you mean a text > blob ? if so, use the definition CLOB, which will give your > column text affinity, which is something you want in order to avoid > unnecessary attempts to convert to numeric values. true, the "varchar" > definition also gives you TEXT affinity, but, since there IS a > "preferred" type to declare text blobs, (CLOB), why not use it ? > Sqlite3Explorer will recognize it, and give you a multi-line editor > to edit/display this field. You are correct that varchar whitout a length is not legal in standard SQL. However, given SQLite's implementation, all varchar fields would have to be typed as varchar(4294967295) which is kind of silly for fileds that normally hold short string values of about 10 bytes. Some smaller length limit could be used, but that would be misleading when neither my application, nor SQLite are enforcing the length limit. The CLOB type does correctly specifiy the behavior of SQLite, but again I think it is less descriptive. It incorrectly implies large strings, and may use other, less efficient (for small strings), storage mechanisms on another database engine. If I ever switch to another database engine (which is unlikely), or SQLite starts using the column type information in a standard manner, I will have to select a reasonable maximum length for these short strings (say 100 or 255 characters) and modify my schema. If you are going to target SQLite with your Sqlite3Explorer program, I think it is incumbent upon it to recognize and accept the syntax used by SQLite, even if it is not completely standard compliant. SQLite's SQL syntax is described at http://www.sqlite.org/lang.html, and under the CREATE TABLE statement the type clause is simply deined as; type ::= typename | typename ( number ) | typename ( number , number ) which is what I used to select the type names in my schema. Note, that the references constraint discussed above is not shown in the SQLite syntax documentation, but is recognized by the SQLite parser. So sometimes, you may have to go to the source to get the details about what syntax it accepts. Anyway, thanks for the quick fix. I will try it out right away. Everything else seemed to work quite well with another database. Sqlite3Explorer looks like it will fit the bill as a GUI database browser for my SQLite3 databases.
