Addons/data/sqlite/Quirks

Sqlite varying column types

The sqlite database allows for columns that have more than one datatype (which is unusual for SQL databases). For example, a column of integers may have one or more values that are character strings. The data/sqlite addon assumes that all values in a column are of the same datatype, as would be the case for a J array, and would be the case for any practical use of sqlite from J.

For example, create a table with int, float and text columns:

   db=: sqlcreate_psqlite_ '~temp/test.db'
   sqlcmd__db 'create table tab (a int,b float,c text)'
   sqlinsert__db 'tab';(;:'a b c');<1 2;3.1 5.4;<'one';'two'
   sqlreads__db 'tab'
┌─┬───┬───┐
│a│b  │c  │
├─┼───┼───┤
│1│3.1│one│
│2│5.4│two│
└─┴───┴───┘

Now set the second row to text, text, integer types:

   sqlcmd__db 'update tab set a="alan",b="bill",c=123 where rowid=2'

The read becomes:

   sqlreads__db 'tab'
┌─┬───┬───┐
│a│b  │c  │
├─┼───┼───┤
│1│3.1│one│
│0│  0│123│
└─┴───┴───┘

Note that the values were written correctly to the database, as can be seen by using sqlite itself:

~$ sqlite3 test.db
sqlite> .header on
sqlite> .mode column
sqlite> select * from tab;
a           b           c         
----------  ----------  ----------
1           3.1         one       
alan        bill        123