Add Column If Not Exists?

[Jaxel]

Is there a correct procedure to add columns in install scripts? Just running the code below will create errors after subsequent upgrades, since the column already exists:

$db->query("ALTER TABLE `EWRmedio_media` ADD `thread_id` int(10) unsigned NOT NULL DEFAULT '0'");

 

[Jaxel]

I got the idea to do this:

if (false === $db->query("SELECT `thread_id` FROM `EWRmedio_media` LIMIT 0"))
{
	$db->query("ALTER TABLE `EWRmedio_media` ADD `thread_id` int(10) unsigned NOT NULL DEFAULT '0'");
}

Will this work?

 

[Jaxel]

I came up with a solution...

	public function addColumnIfNotExist($db, $table, $field, $attr)
	{
		$exists = false;
		$columns = $db->fetchAll("SHOW columns FROM `".$table."`");

		foreach ($columns AS $column)
		{
			if ($column['Field'] == $field)
			{
				$exists = true;
				break;
			}
		}

		if (!$exists)
		{
			$db->query("ALTER TABLE `".$table."` ADD `".$field."` ".$attr);
		}

		return true;
	}

 

[Kier]

How about this?

public function addColumnIfNotExist($db, $table, $field, $attr)
{
	if ($db->fetchRow('SHOW COLUMNS FROM ' . $table . ' WHERE Field = ?', $field))
	{
		return;
	}
	
	return $db->query('ALTER TABLE ' . $table . ' ADD ' . $field . ' ' . $attr);
}

 

[Jaxel]

I guess that works... and would be faster... thanks Kier.

 

[Lawrence]

Jaxel, you can also use your addons version id to call the appropriate function within your install class, one example:

    public static function install($existingAddOn, $addOnData)
    {

        $start_at = 0;
        $finish_at = $addOnData['version_id'];

        if ($existingAddOn)
        {
                   // if the addon already exists, it has a version id, retrieve it and increase it by one for our new starting point

            $start_at = $existingAddOn['version_id'];
            $start_at++;
        }


        for ($x = $start_at; $x <= $finish_at; $x++)
        {


            switch ($x)
            {
                case 0:
                                // initial DB query here (or function call) as this is the first install
               break;

                case 4:
                                // version updates 2 and 3 needed no DB changes, but version 4 does

               break;
                default:


               break;
            }

        }
        // rebuild caches and all done

        XenForo_Model::create('XenForo_Model_ContentType')->rebuildContentTypeCache();

    }

 

[Jeremy]

And Kier, I stole your code for BBCode Manager. Thanks for that. *runs and hides, waiting for ninja awesomeness attacks to come*

 

[Kier]

And Kier, I stole your code for BBCode Manager. Thanks for that. *runs and hides, waiting for ninja awesomeness attacks to come*

That was the idea behind posting it