Gossamer Forum
Home : Products : Gossamer Links : Development, Plugins and Globals :

Table Column Add when Upgrading

Quote Reply
Table Column Add when Upgrading
I am sure someone has come across this and there is an easy statement/solution for it (I just am not sure what it is):

When you are putting out an update/upgrade to you plug-in which has additional table columns a previous version did not have, how can you test to see if the new coloumns exist already, and add them only if they do not?

This prevents the user from having to remove the table (and all of their data) and re-install the plug-in with the new coloumn data.

Thanks


http://www.iuni.com/...tware/web/index.html
Links Plugins
Quote Reply
Re: [Ian] Table Column Add when Upgrading In reply to
How about running a query, and then seeing how many rows are returned, or if mysql_error is returned? Just a guess..I'm used to donig this in PHP Tongue

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Table Column Add when Upgrading In reply to
Interesting point...

I am searching docs, etc for something like an " if not COL exists".


http://www.iuni.com/...tware/web/index.html
Links Plugins
Quote Reply
Re: [Ian] Table Column Add when Upgrading In reply to
I'd use the def file or I think there is a way of grabbing a hashref of cols some other way but can't remember off the top of my head.
Quote Reply
Re: [Paul] Table Column Add when Upgrading In reply to
Hi Paul,

I found this in the docs: cols[/url]

Returns a hash(ref) of column name => column definition

I guess the "trick" is dealing with the error when trying to access a column which does not exist.

Hmmmm.


http://www.iuni.com/...tware/web/index.html
Links Plugins
Quote Reply
Re: [Ian] Table Column Add when Upgrading In reply to
Good question. Maybe someone from GT staff could tell us how it is best done.

What I thought of doing is the following:
the plugin always creates the table as it was in the initial version. I use the following code (Jason's suggestion):

if (!$p->create and $GT::SQL::errcode eq 'TBLEXISTS') {
$p->set_defaults();
$p->save_schema();
}

Then add the fields you add in newer version, field by field as

$editor->add_col();

If there is an error, it will be in $GT::SQL::errcode, and in particular, it will be equal to 'COLEXISTS', if the column already exists. But that will not prevent the install routine from running. In this way, all the new fields will be added.

An even better way of handling would be to check the installed version, and proceed depending on which fields have to be added in an upgrade from the installed to the new version.

Ivan
-----
Iyengar Yoga Resources / GT Plugins
Quote Reply
Re: [yogi] Table Column Add when Upgrading In reply to
Hi Yogi,

I currently add (in a slightly different format):

if (!$p->create and $GT::SQL::errcode eq 'TBLEXISTS') {
$p->set_defaults();
$p->save_schema();
}


into my code. So this checks for the whole table. But to check on a column by column basis would be better, as often we are just adding an extra column or two with an upgrade, not a whole table.

$editor->add_col();

If there is an error, it will be in $GT::SQL::errcode, and in particular, it will be equal to 'COLEXISTS', if the column already exists.


Interesting... I will look at this, thanks! But I think you are right - it would be nice if some more light on this could be shed by staff. It seems like this could be a common problem for plug-in upgrades.

EDIT: An even better way of handling would be to check the installed version, and proceed depending on which fields have to be added in an upgrade from the installed to the new version. Good idea also. There is the $VERSION available already in the install.pm, and I have my own $plug_version in the plugin.pm if need be also.


http://www.iuni.com/...tware/web/index.html
Links Plugins

Last edited by:

Ian: Jun 10, 2002, 12:01 PM
Quote Reply
Re: [yogi] Table Column Add when Upgrading In reply to
The layout of this is still baffling me a bit:

Code:
#Upgrade from 1.0.4 to 1.0.5
my $editor = $DB->editor('EMSettings');
if (! $editor->add_col('FontTopXTitle',
{
pos => 6,
type => 'CHAR',
size => '75',
not_null => 1,
default => '<font color="#000080" size="3">'
}
); ) {
$GT::SQL::errcode ||= '';
$GT::SQL::errcode eq 'TBLEXISTS' ? ($message .= "Could not create column FontTopXTitle (column already exists)\n") : EMSettings: $GT::SQL::error)");
}




Also, reading your plug-in version out of a previously installed version is proving harder than I thought. There are so many possible scenarious... like what if nothing is installed?

Edit: Or, why can't $DB->Creator recognise if a table column exists or not, when it is creating the table, in particular when you have added new coloumns between versions.

Some input/insight on this is really needed and appreciated.


http://www.iuni.com/...tware/web/index.html
Links Plugins

Last edited by:

Ian: Jun 10, 2002, 3:47 PM
Quote Reply
Re: [Ian] Table Column Add when Upgrading In reply to
The take into account all existing versions of the installed plugin (not taking into account any user modifications to tables), I would still do the following (do I repeat myself???):

1) create table (as in initial version)
2) edit table, add new columns (per upgrade, i.e. from 1.0.0 to 1.0.1 etc etc).

As for the layout, you could do

Code:
my %col_properties = { pos => .. , ....};
if (! $editor->add_col('FontTopXTitle',%col_properties)) {
your code here
}

Ivan
-----
Iyengar Yoga Resources / GT Plugins
Quote Reply
Re: [Ian] Table Column Add when Upgrading In reply to
Hi,

Hmm, here's one way you could approach it:

Code:
# First, create the latest version of the table.
my $c = $DB->creator('TblName');
$c->cols( { ... } );
$c->pk( ... );
$c->ai( ... );
my $res = $c->create;
if (! $res) {
# If the table create failed, check why.
# If it's because the table already existed, perform an upgrade.
if ($GT::SQL::errcode eq 'TBLEXISTS') {
my $e = $DB->editor('TblName');
# 1.0.0 -> 1.0.1 we add a new column Foo here.
my $res = $e->add_column('Foo', { ... });
# If the add failed, check why. Same logic as the table checks.
if (! $res and ($GT::SQL::errcode ne 'COLEXISTS')) {
Plugins::MyPlugin->error("Could not add column 'Foo'. Reason: $GT::SQL::error");
}
# Perform any more upgrades here.
...
}
# Otherwise, the table didn't already exist, and we could create it.
else {
Plugins::MyPlugin->error("Could not create table: $GT::SQL::error");
}
}


So basically, you always try and create the latest version of your table. If the creation is successful, that's all there is to it. If it's not successful, then you see: Was the reason because the table already exists (i.e. an upgrade), or something else? If it was because it already exists, then you grab an Editor object, and try adding the extra columns. Each time a column, check the error, if it was ok, move on. If there was an error, was it because the column already existed? If so, that's ok. If not, display the error.

Currently there is no access to the previous installed versions. As by the time your install code is run, the other plugin has been removed.

An alternative approach would be for your installer to save it's version number to a file, for example: admin/Plugins/MyPlugin/version.txt. Then during installation, you check to see if this file exists, and if it does, you load it and see what upgrade code you need to run.

Let me know if you have any questions.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] Table Column Add when Upgrading In reply to
Excellent Ideas Alex! Let me study your code a bit.... althought I like the save a file to check the version method... I think I might opt for the code method first. I will have to try it out!!

Thanks very muchSmile


http://www.iuni.com/...tware/web/index.html
Links Plugins