Re: [sqlite] algorithm for adding columns to a table
On Tue, Mar 18, 2008 at 1:03 PM, Virgilio Alexandre Fornazin
<[EMAIL PROTECTED]> wrote:
> 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.
Thanks!
Derrell
>
>
>
> -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
>
--
"There is nothing more difficult to plan, more doubtful of success,
nor more dangerous to manage than the creation of a new system. For
the initiator has the enmity of all who would profit by the
preservation of the old system and merely lukewarm defenders in those
who would gain by the new one." --Machiavelli, 1513
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] algorithm for adding columns to a table
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
[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
