Re: [sqlite] algorithm for adding columns to a table

[Virgilio Alexandre Fornazin]

Yes. I did this in my custom version of SQLite. If statement is ALTER TABLE,
and SQLite returns error, I check if it´s ALTER TABLE (t) MODIFY COLUMN ou
DROP COLUMN, doing the exact flow you did.

-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Derrell Lipman
Sent: terça-feira, 18 de março de 2008 11:26
To: General Discussion of SQLite Database
Subject: [sqlite] algorithm for adding columns to a table

I could use another set of eyes or three to verify that this algorithm
makes sense.  I have legacy sqlite2 databases for which I need a
generic function to add columns to tables.  This is the pseudocode for
the function.  Am I missing anything?

In this pseudocode, the parameters are:

  :tableName:
     The table name being altered

  :newColumnDefinition:
     The complete text of the new column description, as if it were in a
     CREATE TABLE command, e.g. "t TIMESTAMP DEFAULT '2008-03-18 10:08:47'"

  :newColumnValue:
    The value to insert into the new column as we add the new column
to the table.

Of course, all queries need error checking which is not included in
the pseudocode.  Errors cause an immediate rollback.

Pseudocode follows...


// If anything fails, ensure we can get back to our original
query("begin;")

// Get the sql to generate the table
tableDef = query(
   "SELECT sql
      FROM sqlite_master
      WHERE tbl_name == :tableName:
        AND type = 'table';"
)

// Get the indexes associated with this table, excluding automatic indexes
indexes = query(
   "SELECT sql
      FROM sqlite_master
      WHERE tbl_name == :tableName:
        AND type = 'index'
        AND length(sql) > 0;"
)

// Get the triggers associated with this table
triggers = query(
   "SELECT sql
      FROM sqlite_master
      WHERE tbl_name == :tableName:
        AND type = 'trigger'
        AND length(sql) > 0;"
)

// Copy all of the data to a temporary table
query("CREATE TEMPORARY TABLE __t AS SELECT * FROM :tableName:;")

// Drop the table being altered
query("DROP TABLE :tableName:;")

// Copy the original table definition so we can modify it
sql = tableDef.sql;

// Find the trailing right parenthesis in the original table definition
p = strrchr(sql, ')');

// Where the right parenthesis was, append a comma and new column definition
*p++ = ',';
strcpy(p, :newColumnDefinition:);
strcat(p, ");");

// Recreate the table using the new definition
query(sql);

// Copy the data from our temporary table back into this table.
query("INSERT INTO :tableName: SELECT *, :newColumnValue: FROM __t;")

// We don't need the temporary table anymore
query("DROP TABLE __t;")

// Recreate the indexes
foreach index in indexes
{
    query(index.sql)
}

// Recreate the triggers (after having copied the data back to the table!)
foreach trigger in triggers
{
    query(trigger.sql)
}

query("commit;")


Thanks for any comments you can provide!

Derrell
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users