# MySQL script for CMR examples
#
# Author: Aslak Hellesøy (aslak.nospam@users.sf.net)
#
# Requirements:
# - MySQL 3.23.43b with InnoDB enabled
# - MM.MySQL 2.0.9 (JDBC driver)
#
# Brief installation instructions:
# mysqld-max --standalone
# (new shell)
# mysqladmin create xdoclet
# mysql xdoclet < mysql.txt
#
# You can generate the java sources using Middlegen: http://middlegen.codehaus.org/
# driver: org.gjt.mm.mysql.Driver
# url: jdbc:mysql://localhost/xdoclet
#
# For some (weird) reason the table names must use small letters for relations to work
# Foreign keys must be indexed too.
#
# I'm not an expert on MySQL/InnoDB, so please ask questions about these
# on appropriate mailing lists, not on XDoclet/Middlegen lists


CREATE TABLE country(
 country_id INT NOT NULL,
 owner_id_fk INT NOT NULL,
 name VARCHAR(32) NOT NULL,
 PRIMARY KEY( country_id ),
 INDEX owner_ind(owner_id_fk),
 FOREIGN KEY (owner_id_fk) REFERENCES country(country_id)
) TYPE=INNODB;

CREATE TABLE city(
 city_id INT NOT NULL,
 country_id_fk INT NOT NULL,
 name VARCHAR(32) NOT NULL,
 PRIMARY KEY( city_id ),
 INDEX country_ind(country_id_fk),
 FOREIGN KEY (country_id_fk) REFERENCES country(country_id)
) TYPE=INNODB;

CREATE TABLE language_code(
 language_code_id INT NOT NULL,
 name CHAR(3) NOT NULL,
 PRIMARY KEY( language_code_id )
) TYPE=INNODB;

CREATE TABLE language(
 language_id INT NOT NULL,
 language_code_id_fk INT NOT NULL,
 name VARCHAR(32) NOT NULL,
 PRIMARY KEY( language_id ),
 INDEX language_code_ind(language_code_id_fk),
 FOREIGN KEY (language_code_id_fk) REFERENCES language_code(language_code_id)
) TYPE=INNODB;

CREATE TABLE language_city(
 language_id_fk INT NOT NULL,
 city_id_fk INT NOT NULL,
 PRIMARY KEY( language_id_fk,city_id_fk ),
 INDEX language_ind(language_id_fk),
 INDEX city_ind(city_id_fk),
 FOREIGN KEY (language_id_fk) REFERENCES language(language_id),
 FOREIGN KEY (city_id_fk) REFERENCES city(city_id)
) TYPE=INNODB;
