SQLite Alter Table script

[bule]

Alter table (rename / add columns) support for SQLite 2.8 and IndigoRose AutoPlay Media Studio

SQLiteAddColumns accepts the following attributes:
1: SQLite database handle
2: SQLite table name
3: an array with new column names as indexes and column types as strings
4: name of the primary key column (default: 'id')
5: whether to keep the temporary table used in the process (default: false)

There is also a function SQLiteRenameTable(db, oldname, newname, primarykey) at your disposal.

Please note that the processing time of adding a new column to the table depends on the amount of data in your table. In order to implement alter table for the 2.x SQLite, data must be first writen to the temporary table, and than again to the original one!

WARNING: Any triggers or indexes you have on your table will be LOST, so make sure you recreate them after using either SQLiteAddColumns or SQLiteRenameTable functions!

Attached script version: 0.9.
It should work fine, but please test it a bit. It assumes you use SQLite 2 plugin bundled with APMS.
Also, please rename the file extension to .lua

[Imagine Programming]

Impressive, thanks.

[holtgrewe]

Very nice Bule. This is a true time saver for anyone who uses Sqlite.
Thanks for sharing this gem.

[bule]

The following two functions were missing in the upper script:

-- string manipulation before saving it in the database:
function Q(str)
local s=str.."";
s=String.Replace(s, "'", "''", false);
return s;
end

-- string decoding after reading it from the database:
function QD(str)
return tostring(str or "");
end

I use these to replace single quotes with two of the same kind (you can not use single quote in the string because it's a key word for string start/end). You can also do your own additional modification to the strings stored in the database here (at least I do it).

[bule]

This one was missing as well:

function Num(str)
return String.ToNumber((str or 0).."");
end

[Imagine Programming]

This one was missing as well:

function Num(str)
return String.ToNumber((str or 0).."");
end

Num = tonumber; :D that's what I used :yes

[bule]

Nice one... I use Num a lot, it's short and convenient! ;)

[Imagine Programming]

Your right about that, i got used to tonumber and tostring:p