Information Technology Reference
In-Depth Information
Create Reusable Scripts
When you are writing a script that you plan to reuse, you can
define the attributes in a single file so that you only need to
define them one time for use in all of your manual and automated
scripts, rather than every time you use these attributes.
In Listing 5-3, data-definition.sql is the SQL script that's called by
the Ant script in Listing 5-2. We're using a MySQL database in this
example, so some of the commands are MySQL-dependent. The data-
definition.sql file is responsible for creating the database and its tables,
enforcing data integrity, and applying stored procedures. The follow-
ing is a typical order for this creation process.
1. Database and permissions
2. Tables
3. Sequences
4. Views
5. Stored procedures and functions
6. Triggers
The order of creation within your DDL statements may vary based
on database object dependencies. For example, you may have a func-
tion that depends on a view, or vice versa, so you may need to list the
view first, for example.
data-definition.sql: Sample Database Definition Script
LISTING 5-3
for MySQL
DROP DATABASE IF EXISTS brewery//
…
CREATE DATABASE IF NOT EXISTS brewery//
GRANT ALL PRIVILEGES ON *.* TO 'brewery'@'localhost' IDENTIFIED BY
'brewery' WITH GRANT OPTION//
GRANT ALL PRIVILEGES ON *.* TO 'brewery'@'%' IDENTIFIED BY 'brewery'
WITH GRANT OPTION//
USE brewery//
…
CREATE TABLE beer(id BIGINT(20) PRIMARY KEY, beer_name VARCHAR(50),
brewer VARCHAR(50), date_received DATE);
CREATE TABLE state(state CHAR(2), description VARCHAR(50));//
