Changing SQL tables with update

What is the best way to change tables like add columns when updating plugin?

Should i store "database version" somewhere and add new tables when database version is lower than highest one? Is there a way to detect if column exist?

 

If your database name is foo and your table is timer, this will add the column ip.


DELIMITER $$

CREATE PROCEDURE add_new_col()
BEGIN
IF NOT EXISTS
(
SELECT NULL
FROM information_schema.columns
WHERE table_schema ='foo' AND table_name ='timer' AND column_name
='ip'
)
THEN
ALTER TABLE `foo`.`timer` ADD COLUMN `ip` VARCHAR(16);
END IF;
END$$

DELIMITER ;

CALL add_new_col();

DROP PROCEDURE add_new_col;

Source: http://www.eggheadcafe.com/software/aspnet/35677125/check-if-a-column-doesnt-exist.aspx

 

Wow, thanks.

Does it works in SQLite too?

 

I believe it would work just the same in SQLite. You can always try it and if it works then you're good.

 

Nope

Code:

[ near "DELIMITER": syntax error ]
Exception Name: NS_ERROR_FAILURE
Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement]

And if i remove the DELIMITER line:

Code:

[ near "PROCEDURE": syntax error ]
Exception Name: NS_ERROR_FAILURE
Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement]

I guess SQLite does not support procedures?