How to add column if not exists when installing a component?

Question

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 my_schema explicitly?

Or

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.

Answer 1

Use install/update script. You have access to the framework in this and you can run SQL queries to check for table columns.