On 10/16/2011 02:37 PM, Frank Missel wrote:
When I first learned about SQLite I never like the relaxed handling of data
where column data types from the schema are not enforced strictly on the
actual data in the tables. This felt instinctively wrong as it is very
contrary to other databases and many programming languages that I have
worked with. However, I have come to appreciate SQLite as a powerful data
container that you can use to handle data in various ways according to need.
With e.g. check restraints one can enforce data types on columns at a basic
level. On the other hand, it is sometimes more useful to just let data enter
the database and then later on use data validation to the extent that it is
needed.
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.
I'm not sure I completely understand the question (is there one?),
but consider this:
CREATE TABLE t(a INTEGER);
CREATE INDEX i ON t(a);
INSERT INTO t VALUES(1);
INSERT INTO t VALUES(2);
Then:
SELECT * FROM t WHERE a=1;
The two values in table "t" are stored in integer form. Were you to
magically change the database schema without rebuilding the underlying
b-trees:
CREATE TABLE t(a TEXT);
and then execute the same SELECT, it would not work. SQLite would search
the index for text value '1', not integer value 1 (since it assumes
that all values had the TEXT affinity applied to them when they were
inserted). And the query would return no rows.
If you use 3.7.8, and delay creating any indexes until after all
the data has been inserted into the new tables, SQLite will use
an external merge-sort to build the new indexes. This should be much
faster than using an earlier version.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
