I would like to know when installing a component over an existing one, if it's possible to add a new column if it doesn't exist.
For e.g. inside install.mysql.utf8.sql, I shall have something like:
IF NOT EXISTS( SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='new_column' AND TABLE_NAME='#__my_table' AND TABLE_SCHEMA='my_schema' ) THEN ALTER TABLE `#__my_table` ADD COLUMN `new_column` VARCHAR (30) NOT NULL COLLATE utf8_general_ci; END IF;
As being shown above, the problem is that how will we get the TABLE_SCHEMA instead of defining
Is there any better alternative?
NOTE: The reason we can't use updates to add a new column is that we have many websites created using the same custom-components but with different additional columns which have already been added manually to the individual tables and are not included in the first version.
Use install/update script. You have access to the framework in this and you can run SQL queries to check for table columns.