How to ALTER sqlite column (iPhone)?

Question

For the iPhone, since sqlite3 doesn't support ALTER for a column, how do I change the data type of a column on a table that needs to preserve its data?

Answer 1

SQLite uses Manifest (Dynamic) typing. The column types are a guide only and so Altering the column data type is not necessary. Column Affinity is used just to provide compatibility with statically typed databases.

From the SQLite documentation:

SQLite uses dynamic typing. It does not enforce data type constraints. Any data can be inserted into any column. You can put arbitrary length strings into integer columns, floating point numbers in boolean columns, or dates in character columns. The datatype you assign to a column in the CREATE TABLE command does not restrict what data can be put into that column...

SQLite does use the declared type of a column as a hint that you prefer values in that format. So, for example, if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. If not, it inserts the string

They go on to say that this is not a bug but a feature. If it is a problem for your application I guess SQlite may not be the best choice for you.

Answer 2

Ash is correct, but I'd like to add that if a column's type affinity is causing problems for you (for example, if strings that look like numbers are being converted and stored inaccurately), your only option is to copy all the data to a temporary table, drop the original, recreate it with the right definition, and copy the data back. As far as I know this is not considered a feature. ;^)

Answer 3

This feature sucks, thats why inserts are so slow.