> -----Original Message-----
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Frank Missel
> Sent: 16 October 2011 15:37
>
> With this open philosophy of the SQLite database I thought that perhaps
> there really isn't any compelling reason to disallow changing the declared
> data type of an existing column since the actual stored data of a column
> might be of any type - again, according to the basic philosophy of SQLite.
> Thus changing a declared data type of an existing column should not break
> anything between the schema and the stored data. But I am not sure if it
is
> even possible. If not, I will move my many Gigabytes of data around, but I
> thought, it would be worth just checking first.
Well, I looked a bit more into it and found that indeed the declared data
type of a column in an existing table can be changed without breaking
anything. In the example below the data type "DECIMAL" is changed to "REAL"
(which will make the real numbers transferred to Excel via the ODBC driver
behave as such):
1. Backup the database
2. SQL statement:
pragma writable_schema = 1;
3. SQL statement:
update sqlite_master
set sql = replace(sql, 'DECIMAL', 'REAL ')
where type = 'table'
and sql like '%decimal%'
;
4. 2. SQL statement:
pragma writable_schema = 0;
Notice the three spaces after REAL. They are not really needed but in case
there are extra field attributes after the data type in the CREATE TABLE
statement and the attributes are column aligned, the alignment will be
retained after the change.
I guess other aspects of the tables can also be changed, e.g. renaming
columns -- but here one has to remember any indexes, constraints, etc. So
that is a bit more daring but probably doable. For dropping columns one
needs to copy the data to a new table as far as I can see.
I take a bow: Hurrah for SQLite. This is truly magnificent!
WARNING:
Be sure make a backup copy of the database before doing anything as changing
the schema can corrupt it.
Any tinkering with the sqlite_master table is done at your own risk. Don't
blame me if anything goes wrong :-).
Also, I tried out the above on a small test database of the same structure
as a production database and after changing contents of sqlite_master I
tested various statements against the database to see that everything worked
as it should.
I will wait a while before changing my production databases, just in case
someone might see anything wrong with the above.
/Frank Missel
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
