[bug_fixed] Improve Sqlite3 Column Affinity

[Runspect]

Your ZeosLib DBO Version:
The IDE you are using: Delphi
Version (and maybe patchlevel) of the IDE:
The database you are using: SQLite3
Version of the database:
Used protocol:
[hr]Error description and post

In order to Column Affinity, see:
http://www.sqlite.org/datatype3.html , "2.1 Determination Of Column Affinity"

In ZDbcSqLiteUtils.pas, I suggest changing:

line 150: else if TypeName = 'FLOAT' then
line 159: else if TypeName = 'DOUBLE' then

by:
line 150: else if StartsWith(TypeName, 'FLOA')
line 159: else if StartsWith(TypeName, 'DOUB')

Best regards.

[mdaems]

Hi Runspect,

What's the difference justifying the extra string comparison function? Do other datatypes exist startting with these prefixes?

Mark

[Runspect]

Code: Select all

Do other datatypes exist startting with these prefixes? 

Not really, but according to the sqlite specifications:

If the datatype for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity

By example a field named as "MyFloatField", that contains "Floa", should be considered as REAL. Although into that field can be stored another distinct data type

[mdaems]

That's a wrong argument. In your quote they talk about datatype, not field name.
The real question is "Does SQLite have other datatypes starting with these prefixes OR does SQLite allows user to create custom datatypes?". If this question can be answered "Yes", your feature request will be applied. otherwise it's useless as the only matches are the current ones...

Mark

[aducom]

I always recommend to fully type out the types. SQLite is of origin typeless and datatypes needs to be emulated. In version 3 there are a few datatypes, but what's the advantage of 'crypling' names, just the few bytes to type or to prevent typo's?

[salvois]

The SQL-92 standard defines the "DOUBLE PRECISION" data type and an optional precision parameter for the "FLOAT(precision)" data type (edit: I've just seen the latter is already addressed). The SQLite definition accommodates to this, the current Zeos implementation (as of 6.6.2rc) does not. The fix proposed by the original poster solves this, but in order to be compliant with the SQLite definition you need Pos. Please note that the SQLite definitions talks about "data type" not "field name".
Please note that the same applies to other types, for example "VARCHAR" that may be written as "CHARACTER VARYING" and "CHAR" that may be written as "CHARACTER".

[mdaems]

I've checked the SQLite documentation and can agree partially to the statements in this request.
I'll not try to make zeoslib behave like the full Affinity rules describe as it is a little extreme in my opinion.
To the interest of other readers :(from sqlite 3 docs)

The type affinity of a column is determined by the declared type of the column, according to the following rules:
1. If the datatype contains the string "INT" then it is assigned INTEGER affinity.
2. If the datatype of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.
3. If the datatype for a column contains the string "BLOB" or if no datatype is specified then the column has affinity NONE.
4. If the datatype for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity
5. Otherwise, the affinity is NUMERIC.

I changed the code like this:

Code: Select all

Index: ZDbcSqLiteUtils.pas
===================================================================
--- ZDbcSqLiteUtils.pas	(revision 374)
+++ ZDbcSqLiteUtils.pas	(working copy)
@@ -145,9 +145,9 @@
     Result := stInteger
   else if TypeName = 'BIGINT' then
     Result := stLong
-  else if TypeName = 'REAL' then
+  else if StartsWith(TypeName, 'REAL') then
     Result := stDouble
-  else if TypeName = 'FLOAT' then
+  else if StartsWith(TypeName, 'FLOAT') then
     Result := stDouble
   else if (TypeName = 'NUMERIC') or (TypeName = 'DECIMAL')
     or (TypeName = 'NUMBER') then
@@ -156,11 +156,11 @@
       Result := stInteger
     else} Result := stDouble;
   end
-  else if TypeName = 'DOUBLE' then
+  else if StartsWith(TypeName, 'DOUB') then
     Result := stDouble
   else if TypeName = 'MONEY' then
     Result := stBigDecimal
-  else if TypeName = 'CHAR' then
+  else if StartsWith(TypeName, 'CHAR') then
     Result := stString
   else if TypeName = 'VARCHAR' then
     Result := stString
@@ -178,6 +178,8 @@
     Result := stTimestamp
   else if Pos('BLOB', TypeName) > 0 then
     Result := stBinaryStream
+  else if Pos('CLOB', TypeName) > 0 then
+    Result := stAsciiStream
   else if Pos('TEXT', TypeName) > 0 then
     Result := stAsciiStream;
     //Result := stString;

I don't want to go any further, as I understand from documentation that it's just about 'Affinity' not really strict datatypes as used in other databases. Actually, SQLite just doesn't really care about what you put in a field, even if you gave it an explicit datatype.

Even the change above can lead to trouble. Imagine a table keeping some information about materials.(Iron, wood, ...) and the programmer created a field Floating_in_water of type 'floatingbehaviour' where he systematically inserted the strings 'Floats' and 'Sinks'. Before the change this fields was automatically handled by zeos as a string. And SQLite just stores, even if it's Affinity indicates it's a real. Now it will be handled by zeos as a stDouble. Which certainly causes troubles...

Please, correct me if I'm wrong. But if I'm not, let's just stop discussing this.

Applied in SVN revision 375. Please test.

Mark