On Thu, Dec 24, 2015 at 10:14 AM, Simon Slavin <slavins at bigfraud.org> wrote:
> > On 24 Dec 2015, at 3:12pm, John McKown <john.archie.mckown at gmail.com> > wrote: > > > ?I'm curious as to why. Doing so would, most likely, require rewriting > the > > entire table. If you want a SELECT * to get the columns in a particular > > order, just create a VIEW with the columns in the order in which you want > > them. > > I think Gunnar just wants forms such as > > ALTER TABLE CREATE COLUMN ... > ALTER TABLE DROP COLUMN ... > > to aid with making small changes to the schema. These are available in > most SQL engines but the way SQLite3 is written makes it difficult or > inefficient to implement them. > > I have a database where one table takes up more than 30 Gigabytes of > space. While developing the software I needed several times to change a > column definition and since SQLite lacks these facilities I had to move 30 > Gig of data around every time I did it. Annoying. But it's not normally > that much of a problem for me. > > Simon. > > ?I did overlook the DROP COLUMN request. I guess I got "shocked" by the OP wanting something (I think) like: ALTER TABLE ADD COLUMN newcol TEXT AFTER oldcol. Where "oldcol" is an existing column name which is not the "last" one. I sometimes have an unusual take on things due to having read about relational algebra _before_ doing SQL work. So I think of tables as relationships, as a "set" for "attributes" which have no inherent order. Read a bit too much by Dr. Codd. Oh, an Joe Celko too, for that matter. And _no_ actual professional experience. Makes me a bit of a theoretician. It really would be nice to be able to have a column defined as, say VARCHAR(20) to be "redefined" as TEXT or VARCHAR(n) (where n>=20) with a simple ALTER. That would be a simple change to the schema with no data alteration. If one allowed to change a VARCHAR new length to be _less_ than the old length, then it would be necessary to verify that all current rows were still compliant with the new length. In that case, I guess it would be "best" if the back end were do to the equivalent of a DELETE and ADD on the now-invalid data, truncating the larger value to its new max size. That would save some I/O by not rewriting compliant rows. I don't see any way to avoid I/O if you want to change an INTEGER (1,2,3,4,6 or 8 bytes) to a FLOAT? (always 8 bytes). You'd need to rewrite every row, either "in place" (8 byte INTEGER to FLOAT) or with a DELETE / ADD to the "end". I would really need to examine the internals to see how much I/O this might be. I'm not aware of any RDMS which allows someone to alter the "type" (e.g. INTEGER to FLOAT) of an existing column. What I have done, in PostgreSQL, is something like: ALTER TABLE table ADD COLUMN new-column FLOAT; UPDATE table SET new-column=old-column; ALTER TABLE table DROP COLUMN old-column; UPDATE table ALTER COLUMN new-column RENAME TO old-name; But the above would "move" those 30 Gig of data round, just more easily from the standpoint of the user. What might be interesting in this type of case would be a "column-oriented DBMS" ( https://en.wikipedia.org/wiki/Column-oriented_DBMS) Depending on how it was implemented, it could be made to do the above operation easily. But that would be a _major_ rewrite of SQLite internally. Hum, it could complicate things, but this might be more easily possible if each column were placed in a different OS file. When you add a new column, just create a new file initialized with the same number of rows which contain NULL or the DEFAULT value. When you drop a column, it would simply delete the row-containing file. Maybe a VFS could be written to do this. But SQLite would need to be enhanced to add the ALTER TABLE ... DROP COLUMN operation. -- Computer Science is the only discipline in which we view adding a new wing to a building as being maintenance -- Jim Horning Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
