You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@turbine.apache.org by Jason van Zyl <jv...@apache.org> on 2001/09/29 06:20:18 UTC
Using SQL to create Databases
Hi,
Been talking with Jeff and John and it appears that most databases (if not
all, not sure) will allow the creation of databases via SQL. When I made the
TorqueCreateDatabase task I wasn't sure that all databases could do this so
I went the sure root of generating native scripts.
It would be nice if we could get rid of the script generation and just use
standard SQL to create the databases. Here's the information collected so
far. Looks like the 'CREATE DATABASE' might be standard. I haven't looked it
up.
Sybase:
USE MASTER
DROP DATABASE
CREATE DATABASE name
MSSQL:
Probably the same as Sybase?
MySQL
DROP DATABASE
CREATE DATABASE [IF NOT EXISTS] name
Postgres:
DROP DATABASE
CREATE DATABASE name
Hypersonic:
Database creations happens automatically.
Oracle:
Don't know myself.
I am also curious how the JDBC drivers behave with respect to connecting
to the system database for reach RDB. I will try some tests tomorrow but I'm
not sure how the Ant <sql> will work with this scenerio. Is there a standard
system database name for each RDB. If we can collect all the relevant info
than we can get rid of native scripts that are currently used for creating
the databases. That would be very nice :-)
--
jvz.
Jason van Zyl
http://tambora.zenplex.org
http://jakarta.apache.org/turbine
http://jakarta.apache.org/velocity
http://jakarta.apache.org/alexandria
http://jakarta.apache.org/commons
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-dev-help@jakarta.apache.org
Re: Using SQL to create Databases
Posted by Daniel Rall <dl...@finemaltcoding.com>.
Jason van Zyl <jv...@apache.org> writes:
> I am not expecting that a single statement will work with all the databases.
> We will continue to have templates for each of the database vendors to
> account for all the irregularities: all I'm really interested in doing is
> getting rid of having to create shell scripts and batch files in order to
> create the databases. Even if the SQL was different for each of the
> databases, most of them are the same though, we can use the <sql> task to
> create the databases instead of native scripts.
I concur w/ Jason. The database descriptors will be able to handle
most irregularities.
>> Do you have access to SQL99 standard ?
>
> I'm sure I could dig it up if I had to :-)
I've found O'Reilly's "SQL In A Nutshell" to be a decent SQL99
reference.
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-dev-help@jakarta.apache.org
Re: Using SQL to create Databases
Posted by Jason van Zyl <jv...@apache.org>.
On 9/29/01 10:28 PM, "Gunnar Rønning" <gu...@polygnosis.com> wrote:
> * Jason van Zyl <jv...@apache.org> wrote:
> |
> | It would be nice if we could get rid of the script generation and just use
> | standard SQL to create the databases. Here's the information collected so
> | far. Looks like the 'CREATE DATABASE' might be standard. I haven't looked it
> | up.
>
> I don't find any "create database" statement in the SQL-99 standard. It does
> however have "create schema". "create database" is really a misnomer for
> "create schema". The SQL standard talks about catalogs and schemas, but not
> about databases. A catalog can contain a set of schemas, while a schema
> contains table definitions.
>
> create database is inherently non-portable as you usually need to
> supply different configuration options for the different DBMSes.
I am not expecting that a single statement will work with all the databases.
We will continue to have templates for each of the database vendors to
account for all the irregularities: all I'm really interested in doing is
getting rid of having to create shell scripts and batch files in order to
create the databases. Even if the SQL was different for each of the
databases, most of them are the same though, we can use the <sql> task to
create the databases instead of native scripts.
> Do you have access to SQL99 standard ?
I'm sure I could dig it up if I had to :-)
--
jvz.
Jason van Zyl
http://tambora.zenplex.org
http://jakarta.apache.org/turbine
http://jakarta.apache.org/velocity
http://jakarta.apache.org/alexandria
http://jakarta.apache.org/commons
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-dev-help@jakarta.apache.org
Re: Using SQL to create Databases
Posted by Gunnar Rønning <gu...@polygnosis.com>.
* Jason van Zyl <jv...@apache.org> wrote:
|
| It would be nice if we could get rid of the script generation and just use
| standard SQL to create the databases. Here's the information collected so
| far. Looks like the 'CREATE DATABASE' might be standard. I haven't looked it
| up.
I don't find any "create database" statement in the SQL-99 standard. It does
however have "create schema". "create database" is really a misnomer for
"create schema". The SQL standard talks about catalogs and schemas, but not
about databases. A catalog can contain a set of schemas, while a schema
contains table definitions.
create database is inherently non-portable as you usually need to
supply different configuration options for the different DBMSes.
Do you have access to SQL99 standard ?
--
Gunnar Rønning - gunnar@polygnosis.com
Senior Consultant, Polygnosis AS, http://www.polygnosis.com/
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-dev-help@jakarta.apache.org
Re: Using SQL to create Databases
Posted by Daniel Rall <dl...@finemaltcoding.com>.
Jason van Zyl <jv...@apache.org> writes:
> Sybase:
> USE MASTER
> DROP DATABASE
> CREATE DATABASE name
>
> MSSQL:
> Probably the same as Sybase?
The second two lines I just verified, don't know about the first.
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-dev-help@jakarta.apache.org
RE: Using SQL to create Databases
Posted by Russ Fish <rf...@oz.net>.
For the record, here are example Oracle database/tablespace create
statements. IMHO the goal of removing native scripts and using SQL is
possible, but I'd bet against a completely generic solution.
--Russ
/* create database */
CREATE DATABASE "newdb" ARCHIVELOG
MAXLOGFILES 16 /* not sure what defaults are here */
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 100
LOGFILE /* need at least two of these */
GROUP 1 ('/usr/oradata/newdb/redo01.log' ) SIZE 500K,
GROUP 2 ('/u01/oradata/newdb/redo02.log' ) SIZE 500K,
GROUP 3 ('/u02/oradata/newdb/redo03.log' ) SIZE 500K;
/* create tablespace */
CREATE TABLESPACE DB_DATA DATAFILE
'/u01/oradata/newdb/my_data.dbf' SIZE 40M default storage
(initial 16K next 16K pctincrease 50 minextents 1 maxextents 505);
> -----Original Message-----
> From: Jason van Zyl [mailto:jvanzyl@apache.org]
> Sent: Friday, September 28, 2001 9:20 PM
> To: turbine-dev@jakarta.apache.org
> Subject: Using SQL to create Databases
>
>
> Hi,
>
> Been talking with Jeff and John and it appears that most databases (if not
> all, not sure) will allow the creation of databases via SQL. When
> I made the
> TorqueCreateDatabase task I wasn't sure that all databases could
> do this so
> I went the sure root of generating native scripts.
>
> It would be nice if we could get rid of the script generation and just use
> standard SQL to create the databases. Here's the information collected so
> far. Looks like the 'CREATE DATABASE' might be standard. I
> haven't looked it
> up.
>
> Sybase:
> USE MASTER
> DROP DATABASE
> CREATE DATABASE name
>
> MSSQL:
> Probably the same as Sybase?
>
> MySQL
> DROP DATABASE
> CREATE DATABASE [IF NOT EXISTS] name
>
> Postgres:
> DROP DATABASE
> CREATE DATABASE name
>
> Hypersonic:
> Database creations happens automatically.
>
> Oracle:
> Don't know myself.
>
> I am also curious how the JDBC drivers behave with respect to connecting
> to the system database for reach RDB. I will try some tests
> tomorrow but I'm
> not sure how the Ant <sql> will work with this scenerio. Is there
> a standard
> system database name for each RDB. If we can collect all the relevant info
> than we can get rid of native scripts that are currently used for creating
> the databases. That would be very nice :-)
>
>
> --
>
> jvz.
>
> Jason van Zyl
>
> http://tambora.zenplex.org
> http://jakarta.apache.org/turbine
> http://jakarta.apache.org/velocity
> http://jakarta.apache.org/alexandria
> http://jakarta.apache.org/commons
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: turbine-dev-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-dev-help@jakarta.apache.org