[Qgis-developer] Re: QGIS API: change layer field length

[a.furieri at lqt.it]

On Fri, 15 Jul 2011 11:38:56 +0200, Tim Sutton wrote
> Hi
> 
> I'm speaking under correction, but last time I read the sqllite docs 
> I think it was the case that spatialite fields have no type and no length.
> 

Hi Tim,

you are absolutely right.
SQLite has very loose typing: and the same is
then obviously inherited by SpatiaLite too.

Data-type declarations in CREATE TABLE or
ALTER TABLE ADD COLUMN merely are intended
to be "cosmetic", but aren't enforced at all
when performing INSERT / UPDATE

And different "cells" into the same column
can legitimately store any data type at your
will. So, when using SQLite, each single "cell"
surely store some value belonging to a well 
defined data-type, but this doesn't affects 
the column as a whole.

SQLite "cell values" can store the following
'native' data-types:

SQLITE_NULL (self-explanatory)

SQLITE_INTEGER [8-bit, 16-bit, 32-bit or 64-bit:
  depending on actual value precision]

SQLITE_FLOAT [f.p. DOUBLE PRECISION]

SQLITE_TEXT [arbitrary length: usually ranging
  from 1 byte to 1MB, but depends on build-opts]

SQLITE_BLOB [arbitrary length: usually ranging
  from 1 byte to 1GB, but depends on build-opts]
 

some useful related SQL functions:
----------------------------------
SELECT DISTINCT typeof(col-name) from tbl-name;
  [useful to identify actual column data-types]

SELECT Max(length(col-name)) FROM tbl-name
WHERE typeof(col-name) = 'text';
  [useful to identify max-length for text columns]

bye Sandro