PLS HELP! - show databases and tables and columns

[MuZZy]

Hi,

I just wonder if someone can help me with this:
I need to create a sql script which will run when user installs/upgrades my app.
User may already have the database and tables tructure setup on the server, or may not.

The script needs to conditionally create database, conditionally create tables, and conditionally
add columns to the tables if needed.

I could perform this on MS SQL, as the master database has all info about all other
databases/tables/schemas. BUt in MySQL i don't see such an option.

So, i would need this(for description i use a mix of SQL and pseudo-language):

// -------------- CODE START
IF NOT EXISTS( MyDatabase ) CREATE MyDatabase;
USE MyDatabase;
IF NOT EXISTS( MyTable )
CREATE MyTable (
Field1 INT NOT NULL,
Field2 VARCHAR(20),
Field3 INT
)TYPE='MyISAM'

IF NOT EXISTS (Field3 IN MyTable)
ALTER TABLE MyTable ADD Field3 INT
..... etc...
// -------------- CODE END

So if someone could show me the MySQL way to do that, i would really appreciate that!

Thank you,
Andrey

[d]

You could do it in a shell script. Your pseudo code is along the path
to implement it.
--
Jay
http://d2t2fish.blogspot.com

[MuZZy]

d@t@ wrote:

You could do it in a shell script. Your pseudo code is along the path
to implement it.
--
Jay
http://d2t2fish.blogspot.com

I know that, but i asked how do i translate my pseudo-code to MySQL SQL language?
I don't know how to check if database exists or if table exists or if table's column exists

[Bill Karwin]

MuZZy wrote:

I need to create a sql script which will run when user installs/upgrades
my app.
User may already have the database and tables tructure setup on the
server, or may not.
The script needs to conditionally create database, conditionally create
tables, and conditionally add columns to the tables if needed.

You can get partway there using existing MySQL syntax.
See the respect web pages for CREATE DATABASE, DROP TABLE, CREATE TABLE,
etc.

http://dev.mysql.com/doc/mysql/en/create-database.html
CREATE DATABASE IF NOT EXISTS databaseName ...

http://dev.mysql.com/doc/mysql/en/create-table.html
CREATE TABLE IF NOT EXISTS tableName ...

http://dev.mysql.com/doc/mysql/en/drop-table.html
DROP TABLE IF EXISTS tableName ...

But there is no equivalent mechanism for conditional creation of columns.

At least one widely-used project comes to mind that does what you're
describing; runs a script to bring a database schema up to date with
current table/field definitions: Bugzilla.

The setup procedure for Bugzilla involves running a script
checksetup.pl, which can upgrade previous Bugzilla installations to the
current version's required schema. Basically, it does this with
ordinary ALTER TABLE ADD COLUMN statements, but ignores any error
returned (as would happen if the column already exists).

If you want to know more, download Bugzilla at
http://www.bugzilla.org/download/#stable, open the distribution, and
read their checksetup.pl script.

Regards,
Bill K.

[MuZZy]

Bill Karwin wrote:

MuZZy wrote:

I need to create a sql script which will run when user
installs/upgrades my app.
User may already have the database and tables tructure setup on the
server, or may not.

> The script needs to conditionally create database, conditionally create
> tables, and conditionally add columns to the tables if needed.

You can get partway there using existing MySQL syntax.
See the respect web pages for CREATE DATABASE, DROP TABLE, CREATE TABLE,
etc.

http://dev.mysql.com/doc/mysql/en/create-database.html
CREATE DATABASE IF NOT EXISTS databaseName ...

http://dev.mysql.com/doc/mysql/en/create-table.html
CREATE TABLE IF NOT EXISTS tableName ...

http://dev.mysql.com/doc/mysql/en/drop-table.html
DROP TABLE IF EXISTS tableName ...

But there is no equivalent mechanism for conditional creation of columns.

At least one widely-used project comes to mind that does what you're
describing; runs a script to bring a database schema up to date with
current table/field definitions: Bugzilla.

The setup procedure for Bugzilla involves running a script
checksetup.pl, which can upgrade previous Bugzilla installations to the
current version's required schema. Basically, it does this with
ordinary ALTER TABLE ADD COLUMN statements, but ignores any error
returned (as would happen if the column already exists).

If you want to know more, download Bugzilla at
http://www.bugzilla.org/download/#stable, open the distribution, and
read their checksetup.pl script.

Regards,
Bill K.

Thanks a lot, Bill!
That's exactly what i was looking for!