Best way for Schema.sql

[URBANZ]

So i'm currently in the process of packaging a module up for the market place and currently figuring out the best way for the SQL's to run when installed. 

At the moment i'm using this to check if the column exists.

ALTER TABLE `userStats` ADD COLUMN IF NOT EXISTS US_vip INT(11) NOT NULL DEFAULT '0';

I am aware this only works for MariaDB which not everyone will be running. What is a good alternative that will work with both as i haven't run a basic MySQL server in years so not sure the simplest way for this. 

[Sim]

I think that works for mySQL as well.

[URBANZ]

2 minutes ago, Sim said:

I think that works for mySQL as well.

I did try it as a test and get invalid syntax when used on MySQL server so i believe that function is only part of MariaDB and cant find a workaround without selecting the information schema table and selecting the table from the database but again some users might not have permissions to do this. i think for now just sticking with the MariaDB way.

[KyleMassacre]

IF COL_LENGTH('table_name', 'column_name') IS NULL
BEGIN
    ALTER TABLE table_name
    ADD column_name INT
END

You can try something like this:

 

[URBANZ]

33 minutes ago, KyleMassacre said:

IF COL_LENGTH('table_name', 'column_name') IS NULL BEGIN ALTER TABLE table_name ADD column_name INT END

IF COL_LENGTH('table_name', 'column_name') IS NULL
BEGIN
    ALTER TABLE table_name
    ADD column_name INT
END

You can try something like this:

 

Thank you Kyle i did see this earlier and gave it a try but couldn't get it working correctly, keep getting invalid syntax. how it stands think i'm just going to stick to the MariaDB, most people should be running that anyway.

[SwiftGameR]

On 4/16/2020 at 11:31 PM, urbanmafia said:

So i'm currently in the process of packaging a module up for the market place and currently figuring out the best way for the SQL's to run when installed. 

At the moment i'm using this to check if the column exists.

ALTER TABLE `userStats` ADD COLUMN IF NOT EXISTS US_vip INT(11) NOT NULL DEFAULT '0';

ALTER TABLE `userStats` ADD COLUMN IF NOT EXISTS US_vip INT(11) NOT NULL DEFAULT '0';

I am aware this only works for MariaDB which not everyone will be running. What is a good alternative that will work with both as i haven't run a basic MySQL server in years so not sure the simplest way for this. 

ALTER TABLE tablename ADD IF NOT EXISTS columname int(11) NOT NULL DEFAULT 0;