[PLUG] SQLite (was: mdbtools)

[Robert Citek]

On Fri, Jul 25, 2008 at 2:53 PM, Michael <michael at jamhome.us> wrote:
> Robert Citek wrote:
>> How do I change the column definition, e.g. text to integer?
>
> First ask yourself "is it necessary?"  SQLite does not enforce types.  You can
> store a string or a blob in a INT column.
>
> See http://www.sqlite.org/datatype3.html#affinity  for the background.
>
> So the easy way is to stuff integers into your text columns.

I'm interested because I want to avoid simple errors.  For example:

$ sqlite3 tmp.db .dump
BEGIN TRANSACTION;
CREATE TABLE bar (foo text);
INSERT INTO "bar" VALUES('a');
INSERT INTO "bar" VALUES('b');
INSERT INTO "bar" VALUES('c');
INSERT INTO "bar" VALUES('8');
INSERT INTO "bar" VALUES('9');
INSERT INTO "bar" VALUES('10');
COMMIT;

$ sqlite3 tmp.db 'select foo from bar ;'
a
b
c
8
9
10

$ sqlite3 tmp.db 'delete from bar where foo in ("a","b","c") ;'

$ sqlite3 tmp.db 'select foo from bar ;'
8
9
10

$ sqlite3 tmp.db 'select foo from bar where foo > 7 ;'
8
9

$ sqlite3 tmp.db 'select foo from bar where +foo > 7 ;'
8
9
10

Now that the column contains only numbers, I would like the affinity
to be integer.  My current solution:

$ sqlite3 tmp.db '
create view vbar as select +foo as "foo" from bar ;
create table bar2 as select foo from vbar ;
drop table bar;
drop view vbar;
alter table bar2 rename to bar ;
'

$ sqlite3 tmp.db 'select foo from bar where foo > 7 ;'
8
9
10

$ sqlite3 tmp.db .dump
BEGIN TRANSACTION;
CREATE TABLE 'bar'(foo);
INSERT INTO "bar" VALUES('8');
INSERT INTO "bar" VALUES('9');
INSERT INTO "bar" VALUES('10');
COMMIT;

Perhaps the best solution is to not assign any affinity at the outset.

Regards,
- Robert