How flexible/restricive are SQLite column types?
Recently there has been some debate regarding the flexibility of column types in SQLite. Hence the question, How flexible are SQLite column types?
One argument was that types are restricted to the the main five, namely, TEXT, NUMERIC, INTEGER, REAL and BLOB, and additionally the named column types in the official documentation i.e. :-
INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8, CHARACTER(20), VARCHAR(255), VARYING CHARACTER(255), NCHAR(55), NATIVE CHARACTER(70), NVARCHAR(100), CLOB,no datatype specified (BLOB), DOUBLE, DOUBLE PRECISION, FLOAT, DECIMAL(10,5), BOOLEAN, DATE & DATETIME.
3.1.1. Affinity Name Examples
Another argument was that the list was a list of examples and that column types are more flexible with the 5 rules (as below) being applied virtually universally.
3.1. Determination Of Column Affinity
The affinity of a column is determined by the declared type of the column, according to the following rules in the order shown:
1) If the declared type contains the string "INT" then it is assigned INTEGER affinity.
2) If the declared type 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 declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB.
4) If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity.
5) Otherwise, the affinity is NUMERIC.
Note that the order of the rules for determining column affinity is important. A column whose declared type is "CHARINT" will match both rules 1 and 2 but the first rule takes precedence and so the column affinity will be INTEGER.
3.1. Determination Of Column Affinity
So what are the in and outs/rights and wrongs of SQLite Column Types?
SQLite column types are flexible (dynamic), primarily, it appears to cater for the adoption/adaptation of rigid column types used by other database Management Systems.
Note! this Asnwer is NOT recommending use of weird and wonderful column types.
1) You can actually use virtually any name for a column type, there are however some limitations.
2) Column type is the 2nd value in the column definition e.g. CREATE TABLE table (columnname columntype .....,....), although it may be omitted intentionally or perhaps inadvertently Note see 5a)
3) The first limitation is that mycolumnINTEGER PRIMARY KEY or mycolumnINTEGER PRIMARY KEY AUTOINCREMENT is a special column type. The column is an alias for the rowid which is a unique numeric identifier (AUTOINCREMENT imposes a rule that the rowid must be greater than the last used rowid for the table e.g. if a row uses id (9223372036854775807), then any subsequent attempts to add a row will result in an SQLITE FULL error. ). SQLite Autoincrement
4) Other limitations are that the column type mustn't confuse the SQLite parser. For example a column type of PRIMARY, TABLE, INDEX will result in an SQLite exception (syntax error (code 1)) e.g. when a column type of INDEX is used then:-
android.database.sqlite.SQLiteException: near "INDEX": syntax error (code 1):
occurs.
5) A column type is not mandatory, for example CREATE TABLE mytable (...,PRIMARY_COL,.... in which case a PRAGMA TABLE_INFO(tablename) will show no type e.g. (3rd Line).
08-08 07:56:23.391 13097-13097/? D/TBL_INFO: Col=cid Value=8
08-08 07:56:23.391 13097-13097/? D/ TBLINFO: Col=name Value=PRIMARY_COL
08-08 07:56:23.391 13097-13097/? D/ TBLINFO: Col=type Value=
08-08 07:56:23.391 13097-13097/? D/ TBLINFO: Col=notnull Value=1
08-08 07:56:23.391 13097-13097/? D/ TBLINFO: Col=dflt_value Value=null
08-08 07:56:23.391 13097-13097/? D/ TBLINFO: Col=pk Value=0
5a) In some cases the SQLite Parser will skip to valid KEYWORDS e.g. CREATE TABLE mytable (mycolumn NOT NULL,... results in NOT NULL being used to indicate a NOT NULL column and the type being taken as no type (the table_info above was actually from such a usage).
6) A type is not limited to a single word e.g. VARYING CHARACTER(255) or THE BIG BAD WOLF can be specified as a type as can be seen from this table_info extract :-
08-08 08:23:26.423 4799-4799/? D/ TBLINFO: Col=type Value=THE BIG BAD WOLF
The reason to use non-standard column types in SQLite!
In short there is no reason, as stated at first, the flexibility of column types appears to be primarily to cater for the easy adaptation of SQL from other Database Management Systems.
Column types themselves have little effect as data will be stored according to the what SQLite determines as the storage class to be used. With the exception of rowid (see 3) above) any column can hold values of any type.
With the exception of data stored as a Blob, which must be retrieved using the cursor.getBlob and that cursor.getBlob cannot be used for data not stored as a BLOB (getBlob doesn't fail with data stored as TEXT), You can very much retrieve data (all be it not necessarily useful) using any of the cursor.get???? methods.
Here's some examples:-
For a column where the data long myINT = 556677888; is added (via ContentValues e.g. cv1.put(columnanme,myINT));
Then :-
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: Column=INTEGER_COL<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS INT >>556677888<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS LONG >>556677888<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS STRING >>556677888<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS DOUBLE >>5.56677888E8<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS FLOAT >>5.566779E8<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS SHORT >>15104<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: Unable to handle with getBlob.
getShort does not return to the stored value, getBlob cannot get the stored value.
For Double myREAL = 213456789.4528791134567890109643534276; :-
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes: Column=REAL_COL<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes: VALUE AS INT >>213456789<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes: VALUE AS LONG >>213456789<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes: VALUE AS STRING >>2.13457e+08<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes: VALUE AS DOUBLE >>2.134567894528791E8<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes: VALUE AS FLOAT >>2.1345678E8<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes: VALUE AS SHORT >>6037<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes: Unable to handle with getBlob.
For String myTEXT = "The Lazy Quick Brown Fox Jumped Over the Fence or something like that.";
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: Column=TEXT_COL<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS INT >>0<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS LONG >>0<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS STRING >>The Lazy Quick Brown Fox Jumped Over the Fence or something like that.<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS DOUBLE >>0.0<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS FLOAT >>0.0<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS SHORT >>0<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS BLOB >>[B@2f9e811e<<
And here's a pretty ridiculous example with a column type of my_char_is_not_a_char_but_an_int as per PRAGMA TABLE_INFO :-
08-08 09:19:03.657 13575-13575/mjt.soqanda D/TBL_INFO: Col=cid Value=7
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ TBLINFO: Col=name Value=my_char_is_not_a_char_but_an_int_COL
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ TBLINFO: Col=type Value=my_char_is_not_a_char_but_an_int
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ TBLINFO: Col=notnull Value=0
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ TBLINFO: Col=dflt_value Value=null
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ TBLINFO: Col=pk Value=0
Results (stored as per 'Double' above) are:-
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes: Column=my_char_is_not_a_char_but_an_int_COL<<
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes: VALUE AS INT >>213456789<<
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes: VALUE AS LONG >>213456789<<
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes: VALUE AS STRING >>2.13457e+08<<
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes: VALUE AS DOUBLE >>2.134567894528791E8<<
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes: VALUE AS FLOAT >>2.1345678E8<<
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes: VALUE AS SHORT >>6037<<
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes: Unable to handle with getBlob.
The above was based upon the following:- Datatypes In SQLite Version 3 SQLite Autoincrement PRAGMA Statements
Code was tested/run on a GenyMotion emulated device running API22 compiled with a min version of 14 and target of 26.