Installation Tables and Columns Help...

[Jaxel]

Okay, lets say I have a mod... and in version1, I have the following install code... (the code is an example, I know its not functioning, but thats got nothing to do with my problem)

$db->query_write("CREATE TABLE IF NOT EXISTS table (
column1 int auto_increment,
column2 int,
PRIMARY KEY (column1)
)");


Then I create a version2, with an extra column... so I made my install code this...

$db->query_write("CREATE TABLE IF NOT EXISTS table (
column1 int auto_increment,
column2 int,
column3 int,
PRIMARY KEY (column1)
)");
$db->query_write("ALTER TABLE table ADD COLUMN column3 int)");


Now I am making a version 3... and there is yet another column to add... I tried the following code, but I got an error when it hit the ADD COLUMN column3 code... because column3 already exists.

$db->query_write("
CREATE TABLE IF NOT EXISTS table (
column1 int auto_increment,
column2 int,
column3 int,
column4 int,
PRIMARY KEY (column1)
)");
$db->query_write("ALTER TABLE table ADD COLUMN column3 int)");
$db->query_write("ALTER TABLE table ADD COLUMN column4 int)");

I tried looking for a possible IF NOT EXIST condition for altering tables to add columns, but it looks like you cant do it. I know I could just delete the add column3 query, and it would work great for people who are updating from version2 to version3. But that line would still be required if people were upgrading from version1. So is there a proper way for me to handle this problem?

[Kirk Y]

You could run a query to retrieve the columns in "table" and then control the flow of logic from there.

if (!in_array('column3', $columns))
{
// create column 3 as it does not exist yet
}

[Jaxel]

Hmm... how would that code be written exactly? What would be the code to construct $columns?

[Kirk Y]

$columns = $db->query_read("SHOW COLUMNS FROM " . TABLE_PREFIX . "table");

You'd need to loop through $columns and take out the "field" key.

[Jaxel]

Huh? I'm confused? What would I need to loop? Wouldn't this be enough?


$columns = $db->query_read("SHOW COLUMNS FROM table");
if (!in_array('column3', $columns))
{
$db->query_write("ALTER TABLE table ADD COLUMN column3 int)");
}
if (!in_array('column4', $columns))
{
$db->query_write("ALTER TABLE table ADD COLUMN column4 int)");
}


Thanks for the help BTW.

[Kirk Y]

If you look at what $columns contains, you'll see it is a two-dimensional array. You need to loop through it after your query to extract the relevant information; in this case - the field names.

$columnquery = $db->query_read("SHOW COLUMNS FROM table");
$columns = array();
while ($column = $db->fetch_array($columnquery))
{
$columns[] = $column['field'];
}Then you can run the in_array check.

[Dismounted]

There is a reason why you are allowed to create multiple install codes in products. In the version 1 install code, use the first query. In the version 2 code, create a query using ALTER and add another column. In the version 3 install code, create another query.

On a new installation of the modification, vBulletin will run the install codes in order of version.

[Jaxel]

So this this shoudl work? the table name is video... and the column is timelength...


$columns = $db->query_read("SHOW COLUMNS FROM video");
$videocolumns = array();
while ($column = $db->fetch_array($columns))
{
$videocolumns[] = $column['field'];
}
if (!in_array('timelength', $videocolumns))
{
echo('<li>Altering Table <strong>' . TABLE_PREFIX . 'video</strong> ... ');
$db->query_write("ALTER TABLE video ADD timelength int(10) unsigned NOT NULL");
}


I will use the install code stuff in future iterations...

[Dismounted]

Why not use the install code functionality now?

[Jaxel]

Because what if someone is upgrading from Version 1 to Version 3... They wont get the changes in Version 2, because its not in the new install code. If I put the new install code in Version 3; then it gets the redundancy error.

Anyway, the code worked, thanks Kirk!

[Kirk Y]

Don't forget to take table prefixes into account in your SHOW COLUMNS query.

[Jaxel]

Don't forget to take table prefixes into account in your SHOW COLUMNS query.
Oh shit... thanks for the heads up!

$vidcolumns = $db->query_read("SHOW COLUMNS FROM " . TABLE_PREFIX . "video");
$videocolumns = array();
while ($vidcolumn = $db->fetch_array($vidcolumns))
{
$videocolumns[] = $vidcolumn['Field'];
}
if (!in_array('timelength', $videocolumns))
{
echo('<li>Altering Table <strong>' . TABLE_PREFIX . 'video</strong> ... ');
$db->query_write("ALTER TABLE video ADD timelength int(10) unsigned NOT NULL");
}