Hello All,
I'm having a problem with the 'manifest typing' concept used in SQLite3.
The problem is as follows:
1. I have a table in SQLite 2.8 database, containing list of city telephone
codes. It is important that the codes are text values because leading zeros
is significant in this case. So the table definition is
create table codes(code TEXT);
2. Then i run e.g. 2 insert statements
insert into codes values('00');
insert into codes values('011');
3. Now i dump my SQLite 2.8. database using .dump command and got following
script:
BEGIN TRANSACTION;
create table codes(code TEXT);
INSERT INTO codes VALUES(00);
INSERT INTO codes VALUES(011);
COMMIT;
4. It works great when recreating the SQLite 2.8 database, but when run on
SQLite 3 it creates table codes filled as follows:
select code from codes;
0
11
The leading zeros are lost and my data is no longer valid.
Generally, the problem arised when i tried to convert my SQLite 2.8 database
to SQLite 3 and i solved it by changing the dump callback function so it
quotes all values from certain columns.
I intended to use the dump function of both SQLite 2.8 and SQLite 3 for
database backups to make the backups to be version independent. But the
'manifest typing' makes it a bit troublesome to do and i also have quite a
lot of existing backups (in form of SQL scripts) for SQLite 2.8 databases.
Also i have quite a few SQL patches for my codes table and some other tables
having same kind of fields, generated from SQLite 2.8. Is there a way to
make all them to be SQLite 3 comatible (in sense of the above description)
automatically, so them can be used on SQLite 3?
Please let me know if there is a workaround for this problem. Any feedback
is greatly appreciated.
Many thanks,
Sergey.
