On Sun, May 15, 2011 at 10:39:22PM +0100, Simon Slavin scratched on the wall:
>
> On 15 May 2011, at 10:33pm, romtek wrote:
> > So, I am asking developers of SQLite to make it easy for tool developers to
> > offer the ability to rename attributes.
>
> The SQL specification does not use the term 'attribute' in any way
> that would give them names.
The term "attribute" comes from Relational Theory. In the Relational
world, an attribute is a {name, type} pair. The header of a relation
contains a formal set of attributes which define the overall type of
the relation. The body of a relation contains a formal set of tuples
that consists of {attribute, value} pairs. For a tuple to be a valid
member of a relation's body, the tuple's attribute set must match the
relation's header's attribute set.
So, in gross terms, an attribute of a relation is "like" a column of
a table. But like most things having to do with the differences
between the Relational Model and SQL, that "like" is only a "sort-of,
kind-of, but not really" similarity.
As for renaming a column, here's the quick-and-very-very-dirty,
playing-with-explosives, don't-try-this-at-home, don't-be-stupid,
better-know-what-you're-doing, never-use-in-production,
better-have-backups, we'll-laugh-at-you-when-you-destroy-all-your-data,
you-have-been-warned way to do it:
sqlite> BEGIN;
sqlite> PRAGMA writable_schema=1;
sqlite> UPDATE sqlite_master SET
...> sql=replace( sql, <OLD COL NAME>, <NEW COL NAME> )
...> WHERE name = <TABLE NAME>;
-- Modify indexes, triggers, and any tables with FKs
sqlite> PRAGMA writable_schema=0;
sqlite> SELECT sql FROM sqlite_master WHERE name = <TABLE NAME>;
-- TRIPLE CHECK EVERYTHING!!!
-- Don't forget to check other modifications
sqlite> COMMIT;
Once you set "writable_schema" (and COMMIT) it is trivial to
destroy the database file, making it unrecoverable. You have been
warned. If you have indexes, foreign keys, triggers, or anything else
that references column names, you need to change those at the same
time. You have been warned. Figuring out the required changes for
indexes/triggers/FKs is left as an exercise for the reader. You have
been warned. Even then, this is very, very likely to blow up. You
have been warned. Be very, very sure the <OLD COL NAME> sub-string
only appears once in the CREATE TABLE statement, or you'll be very,
very sorry. You have been warned.
You have been warned.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
