Download from
http://www.nyangau.org/dbsetup/dbsetup.zip.
I often make tools available that interact with databases, and when I do, I typically include examples to show them off. This means I need to supply SQL to set up tables, indexes and initial content. Unfortunately, SQL dialects vary quite a bit, and I don't really want to supply multiple SQL files, one per database vendor.
DBSetup is a tool which takes a database description file and generates SQL to create and/or drop types, tables and indexes.
The input file is XML, and a file DBSetup.xsd is provided
declaring what a valid database description file looks like.
If the database description file validates, there is a very good chance
DBSetup can process it.
The main exception is that tables must be defined before foreign keys
which reference them.
It has very limited heuristics for dealing with differences in
primitive data type.
At present, it knows that datetime isn't supported on Oracle,
and rewrites this as timestamp.
It understands the concept of simple user define type names.
This is really a form of type alias rather than "object types".
With Sybase it generates sp_addtypes for them,
but with other database types it substitutes the user defined type name
with what they are an alias for.
It understands tables.
You can specify a Sybase specific lock attribute, so you
can specify datarows or similar on table creation.
You can specify a MySQL specific engine attribute, so you
can specify InnoDB or similar on table creation.
These are ignored on other database types.
It understands primary keys and foreign keys.
It will name a primary constraint as PK_<tablename>,
but allows you to override this.
This is important because Oracle has a 30 character limit.
There is no default constraint name for foreign keys, you must supply one.
It allows you to define indexes. These can be marked as unique and/or clustered. The clustered attribute only makes it into the Sybase DDL at present.
It also provides a mechanism for initializing table content. This is quite crude - you include a set of rows, and DDL is generate that simply does an INSERT statement for each of them.
As soon as you look further, at stored procedures, things start to become very vendor specific, so no attempt to support these is made.
I imagine I'll enhance this tool from time to time, as I need new SQL features supporting, or as I encounter more database types.
Usage :-
$ ./DBSetup -help
usage: DBSetup [flags]
flags: -fn file.xml table specifications (default: DBSetup.xml)
-dbtype type one of MySQL (default), Oracle or Sybase
-create generate DDL to create tables, indexes and initial content
-drop generate DDL to delete indexes and tables
To create Oracle SQL to create the ORDERS database :-
$ ./DBSetup -fn orders.xml -dbtype Oracle -create > orders_create.sql
Here is a simple example, which reflects the example ORDERS database delivered in Tabulator :-
<dbsetup>
<table name="ORDERS">
<column name="id" type="int"/>
<column name="customer" type="varchar(100)"/>
<primaryKey constraint="PK_ORDERS">
<columnRef name="id"/>
</primaryKey>
<index name="ORDERS_id" unique="true">
<columnRef name="id"/>
</index>
<row>1,'Wile Coyote'</row>
<row>2,'Roadrunner'</row>
</table>
<table name="ITEMS">
<column name="id" type="int"/>
<column name="order_id" type="int"/>
<column name="description" type="varchar(200)"/>
<primaryKey constraint="PK_ITEMS">
<columnRef name="id"/>
</primaryKey>
<foreignKey constraint="FK_ORDERS" table="ORDERS">
<columnRef name="order_id"/>
</foreignKey>
<index name="ITEMS_id">
<columnRef name="id"/>
</index>
<index name="ITEMS_order_id">
<columnRef name="order_id"/>
</index>
<row>10,1,'Acme explosives'</row>
<row>11,1,'Acme rocket sledge'</row>
<row>12,1,'Medical insurance'</row>
<row>20,2,'Bird seed'</row>
</table>
</dbsetup>
Editing the database description XML in an editor which can validate against an XSD is a good idea, because the XSD includes constraint checks, such as "all primaryKey and foreignKey constraints must be unique", and "all index columnRef names must refer to columns in the table".
Issue: At this time timestamp literals are a problem in
<row> elements because they must be preceded by
TIMESTAMP when using Oracle.
Often you want a database (or a schema) with a particular user to access it, and then to use the generated SQL to populate it.
I tested using MySQL 5.1.
Simply start it up, create a database, create a user, and as that user run the script to create the tables :-
$ sudo /etc/init.d/mysqld start $ mysql --user root --password mysql> create database mydb ; mysql> use mydb ; mysql> create user 'myuser'@'localhost' identified by 'myuserpassword' ; mysql> grant all on mydb.* to 'myuser'@'localhost' ; mysql> exit $ mysql --user myuser --password=myuserpassword mysql> use mydb ; mysql> source /somepath/somegenerated.sql ; mysql> exit $
Later Linuxes tend to have MariaDB, and use systemd,
so instead start it with :-
$ sudo systemctl start mariadb
Here I use Oracle XE 10.2.0.
Start up Oracle, create a user and grant access to the database and permission to create tables in it. Then as that user, run the generated script.
$ sudo /etc/init.d/oracle-xe start $ su - oracle $ export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server $ export ORACLE_SID=XE $ cd $ORACLE_HOME/bin $ ./sqlplus / as sysdba SQL> create user myuser identified by myuserpassword ; SQL> grant connect, resource to myuser ; SQL> exit $ ./sqlplus myuser myuserpassword SQL> @/somepath/somegenerated.sql ; SQL> exit $
With Oracle XE 11.2.0, you use
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
Here I use the free Sybase ASE 16, developer edition.
I had earlier created a dataserver called myserver
with a user data directory of /home/sybase/myserver.
Start it :-
$ su - sybase $ . /opt/sap/SYBASE.sh $ myserver/ASE-16_0/install/RUN_myserver
The script to run a Sybase dataserver doesn't seem to be like a normal init-script, so we leave it running, and open another window.
When creating the database, we need to first allocate somewhere for it to put its data and logs. In development and test databases, rather than set up log management, we can simply let Sybase try to do it on every checkpoint :-
$ su - sybase $ . /opt/sap/SYBASE.sh $ isql -S MYSERVERHOST:MYSERVERPORT -U sa -P sapassword 1> disk init name="mydb_data",physname="/home/sybase/myserver/data/mydb_data.dat",size="1G" 2> go 1> disk init name="mydb_log",physname="/home/sybase/myserver/data/mydb_log.dat",size="100M" 2> go 1> create database mydb on mydb_data="1G" log on mydb_log="100M" 2> go 1> sp_dboption mydb,"trunc log on chkpt",true 2> go 1> ^D $
We can then run the generated script to create the tables :-
$ isql -S MYSERVERHOST:MYSERVERPORT -U sa -P sapassword -D mydb -i /somepath/somegenerated.sql $
In Sybase, the ability to login is one thing, and its another for that login to be present as a user of a database :-
$ isql -S MYSERVERHOST:MYSERVERPORT -U sa -P sapassword 1> create login myuser with password myuserpassword default database mydb 2> go 1> use mydb 2> go 1> sp_adduser myuser,myuser 2> go 1> ^D $
The trickiest bit is that isql doesn't provide an easy way
to grant access to all tables in a database, but with a little shell, we can :-
$ cat /somepath/somegenerated.sql |\ sed -n 's/CREATE TABLE \(.*\)/grant all on \1 to myuser\ngo/p' |\ isql -S MYSERVERHOST:MYSERVERPORT -U sa -P sapassword -D mydb $
Finally, because its not controlled by an init-script, this is how to shut it down :-
$ isql -S MYSERVERHOST:MYSERVERPORT -U sa -P sapassword 1> shutdown 2> go $
Feel free to copy, its public domain. Caveat Emptor.