On Sat, 3 Dec 2005, Luiz Americo Pereira Camara wrote:
> Em Sáb, 2005-12-03 às 15:17 +0100, Michael Van Canneyt escreveu: > > > > On Fri, 2 Dec 2005, Luiz Americo Pereira Camara wrote: > > > > > Em Sex, 2005-12-02 às 18:26 +0100, Michael Van Canneyt escreveu: > > > > > > > > On Fri, 2 Dec 2005, Luiz Americo wrote: > > > > > > > > > > De: Michael Van Canneyt <[EMAIL PROTECTED]> > > > > > > > > > > >> How about sqliteds? > > > > > > > > > > > > I'm still working on that; I'm using the components for an article, > > > > > > so you can be sure that. > > > > > > > > > > Fixing it's easy. I can do that. > > > > > > > > > > > But there were some more problems with > > > > > > sqliteds. It doesn't recognize VARCHAR fields, for instance. > > > > > > > > > > This issue was discussed ealier in this list. > > > > > See http://article.gmane.org/gmane.comp.ide.lazarus.general/1401 > > > > > > > > > > Anyway i'd like to know your thoughs > > > > > > > > Well, I have the following SQL > > > > > > > > CREATE TABLE PUPIL ( > > > > PU_ID INTEGER NOT NULL, > > > > PU_FIRSTNAME VARCHAR(50) NOT NULL, > > > > PU_LASTNAME VARCHAR(50) NOT NULL, > > > > CONSTRAINT PUPIL_PK PRIMARY KEY (PU_ID) > > > > ); > > > > > > > > And TSQliteDataset doesn't eat > > > > > > > > SELECT * FROM PUPIL; > > > > > > > > I fixed TSQLIteDataset already so it works with this and with > > > > CHAR(10) definitions as well. > > > > > > How did you do that? > > > > Check for VARCHAR at the beginning of the type name, and then extract > > the declared length. > > > > > I think we can do that, letting the developers aware of the > > > inconsistencies i mentioned in the previous post. > > > > You mean that SQLite doesn't actually check the field types against > > declaration when inserting/updating, allowing to store 50 characters > > in a 10 length field? > > Yes. In fact you can assign any name to a field type, like DUMMY, > MYTYPEX, Y and even none. > It also allows to insert any data in any field type. Let's say you have > a NUMERIC field. You can insert "XCVXVCXV" that sqlite won't complain. > The size that a record can have is determined by the sqlite internal > memory constraint and not by the field type. > > Sqlite3 add the concept of affinity which try to guess what is the best > way to store a value, but is still allowed to insert any datatype to any > field. > > > This is a problem, and the main reason why I wouldn't use SQLite. > > > > (I'm actually surprised that the SQLite team describes this as a 'feature' > > !) > > > > > > > > To allow this construct is easy. Just do like before, if it does not > > > recognizes the field type then treat as a String field, it's safe and > > > the code continues simple and clear. > > > > Nono, just recognize all allowed fields. declaring something like > > MyField VarChar > > (no length) Is not generally valid SQL. > > As i explained above this is not valid to sqlite. Any fieldtype is > allowed and TSqliteDataset uses the fieldtype info only to map the > correct TField type. > > > > As an extension i could rename the field aliases to the most used in SQL > > > language. For example, today the alias to a Float field is the string > > > FLOAT, but the most used, it seems, is NUMERIC. The same for MEMO and > > > TEXT. This would break existing datafiles but since sqliteds is in early > > > development it's acceptable and the tool i wrote could convert easily to > > > the new layout. > > > > Why would you want to do that ? I don't see the advantage ? > > Because some complains that, in file created outside > TSqliteDataset.CreateTable, field [VARCHAR(X), NUMERIC, TEXT] are not > recognized. > Previously i was not inclined to use the common field aliases, because > they are useless in sqlite context and could lead to ambiguosity. But i > was not considering the possibility of table creation using SQL. The > advantage i see is that if you create a table using sql (with the common > datatypes) you'll get the expected TField type instead of none, as is > today, or TStringField, as it was before. Well. The main reason we should recognize them is that if you want to write a database application that should run on many databases (mysql, postgres, interbase, sqlite, whatever) you want to have 1 database creation script, and you expect the results from queries to return the same field types on all engines. (assuming you don't use any engine-specific types) If I get a float field from Interbase in some query, I expect to have a float field when I run the same query on MySQL and SQLite as well. I have written a test program that I run on a database I created in IB, MYSQL and SQLite . The program must return the same field types on all databases. Techniques like Zeos, DBXpress, ADO rely on this condition. Also, persistent fields won't work without this. > The drawback is that it would be necessary to convert the existing data, > something easy. > > > I mean, I would add support for all field type declarations > > that we know SQLite supports, but I wouldn't give any preferred > > treatment to any of them. > See above Let's agree that we support all known ANSI SQL field types, plus some common aliases. You can put a map in CustomSQLiteDS. Michael. _________________________________________________________________ To unsubscribe: mail [EMAIL PROTECTED] with "unsubscribe" as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
