The prefered way to update a database, (MySQL)

[Simon]

Hi,

We have a small utility that uses a database.
We are happy with the structure of the database but we cannot predict any
updates.

What we are particularly concerned about are updates vs. new installs.

We have an install that does something like...

.....
CREATE DATABASE "my_db" DEFAULT CHARACTER SET latin1 COLLATE
latin1_swedish_ ci;
USE my_db;

CREATE TABLE IF NOT EXISTS "my_table" (
"id" int(11) NOT NULL default '0'
)
....


if we create a new field we would update our install script

....
CREATE TABLE IF NOT EXISTS "my_table" (
"id" int(11) NOT NULL default '0',
"anotherid" int(11) NOT NULL default '0'
)
....

But what about users that already have the table? how can I check if the
field needs to be added?

ALTER TABLE "my_table" ADD "anotherid" INT( 11 ) NOT NULL default '0'

Ideally the script would be self sufficient so we don't have versions to
compare. I would check if the field exists, and if it doesn't alter the
table.

Many thanks in advance.

Simon

[NC]

Re: The prefered way to update a database, (MySQL)

Simon wrote:[color=blue]
>
> We have an install that does something like...
>
> ....
> CREATE DATABASE "my_db" DEFAULT CHARACTER SET latin1 COLLATE
> latin1_swedish_ ci;
> USE my_db;
>
> CREATE TABLE IF NOT EXISTS "my_table" (
> "id" int(11) NOT NULL default '0'
> )
> ...
>
> if we create a new field we would update our install script
>
> ...
> CREATE TABLE IF NOT EXISTS "my_table" (
> "id" int(11) NOT NULL default '0',
> "anotherid" int(11) NOT NULL default '0'
> )
> ...
>
> But what about users that already have the table? how can I
> check if the field needs to be added?[/color]

Use any of the following:

SHOW COLUNMS;

http://dev.mysql.com/doc/mysql/en/show-columns.html

SHOW CREATE TABLE;

http://dev.mysql.com/doc/mysql/en/show-create-table.html

DESCRIBE my_table;

http://dev.mysql.com/doc/mysql/en/describe.html

The output will differ, but the names of columns will be listed
anyway.

For a more PHP-oriented solution, see documentation for
mysql_list_fiel ds():

http://www.php.net/mysql_list_fields

Cheers,
NC

[Gordon Burditt]

Re: The prefered way to update a database, (MySQL)

>We have a small utility that uses a database.[color=blue]
>We are happy with the structure of the database but we cannot predict any
>updates.
>
>What we are particularly concerned about are updates vs. new installs.[/color]

You can't do what you want in SQL alone. Or at least not easily.
With a better programming language that interfaces to SQL (and
this includes PHP), you can.
[color=blue]
>But what about users that already have the table? how can I check if the
>field needs to be added?[/color]

You can do things like:
- Query "show tables" and see if the table is there.
- Query "describe table_name" or "show columns from
table_name like 'column_name'" and see what columns are there.
- Query "select field_in_questi on from table_name" and see if it
gets an error (due to field_in_questi on not being present).
- In MySQL 5, query the table information_sch ema.COLUMNS.

This requires that you get a result set and then manipulate it
in PHP, rather than doing it all in SQL.

And if the field isn't there, run something like:[color=blue]
>ALTER TABLE "my_table" ADD "anotherid" INT( 11 ) NOT NULL default '0'[/color]
[color=blue]
>Ideally the script would be self sufficient so we don't have versions to
>compare. I would check if the field exists, and if it doesn't alter the
>table.[/color]

Gordon L. Burditt