Initial Schema

Initial Schema In this scenario, development is based on an existing schema. Application designers are often not allowed to significantly update or change a schema, perhaps because it is in use by other applications, or because the database is a legacy system, or for any number of other possible reasons. The point is that the database design in this scenario drives the application development. A schema is given, and may occasionally be embroidered (for example, adding a column), but the overall structure is fixed prior to application development. Listing 4.1 shows the script used to generate the schema for the database in this chapter. Note that this script is written to automatically drop and recreate the tables used in the chapter each time it is run, destroying any data present in the tables (obviously this would not be used in a "real" system). The schema in this example includes several MySQL specific commands. For example, the TYPE=InnoDB command tells MySQL to convert the tables to the InnoDB format, a more modern format for storing database data than the default MySQL format. Similarly, the foreign-key checks are disabled to avoid errors when dropping the tables. The schema described in this chapter is based on MySQL 4.0.18. Depending on the database you are using, you may need to modify this schema script to be able to execute it. Alternatively, you may wish to use the graphical administrative tool included with your database (if any) to create this schema. PORTABLE SCHEMA SCRIPTS If you are used to another database, the MySQL schema commands below may look unfamiliar. Worse, unless you are running MySQL, you'll almost certainly have to edit the script for your own database. This is yet another reason why I strongly prefer working from a mapping file if possible. Hibernate includes schema management functionality (as described in Chapter 11). This allows you to define a common schema structure in your mapping file and rely on Hibernate to generate dialect-specific scripts for a suite of databases. Pay close attention to the commands to create indexes and foreign key constraints at the end of the script. Middlegen (the tool used later in this chapter) reads these constraints and uses them to generate relationship mappings. In other words, Middlegen reads these key relationships and uses them to establish relationships in the generated Hibernate mapping files (and from there in the generated Java source). If you wish to see a graphical representation of the tables generated by this script, you may wish to skip ahead to glance at the tables in Figure 4.2. Listing 4.1. Schema Generation Script [View full width] USE hibernate; SET FOREIGN_KEY_CHECKS=0; DROP TABLE IF EXISTS Course; DROP TABLE IF EXISTS Student; DROP TABLE IF EXISTS Exam; DROP TABLE IF EXISTS ExamResult; SET FOREIGN_KEY_CHECKS=1; CREATE TABLE Course ( ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY ); ALTER TABLE Course ADD title CHAR(100); ALTER TABLE Course ADD quarter CHAR(4); CREATE TABLE Student ( ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY ); ALTER TABLE Student ADD firstName CHAR(100); ALTER TABLE Student ADD lastName CHAR(100); ALTER TABLE Student ADD idString CHAR(20); CREATE TABLE Exam ( ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY ); ALTER TABLE Exam ADD courseID BIGINT UNSIGNED; ALTER TABLE Exam ADD date TIMESTAMP; ALTER TABLE Exam ADD comment CHAR(255); CREATE TABLE ExamResult ( ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY ); ALTER TABLE ExamResult ADD score INT; ALTER TABLE ExamResult ADD studentID BIGINT UNSIGNED; ALTER TABLE ExamResult ADD examID BIGINT UNSIGNED; ALTER TABLE Course TYPE=InnoDB; ALTER TABLE Student TYPE=InnoDB; ALTER TABLE Exam TYPE=InnoDB; ALTER TABLE ExamResult TYPE=InnoDB; ALTER TABLE Exam ADD INDEX (courseID); ALTER TABLE ExamResult ADD INDEX (studentID); ALTER TABLE ExamResult ADD INDEX (examID); ALTER TABLE Exam ADD CONSTRAINT FK_exam_courseID FOREIGN KEY (courseID) REFERENCES Course (ID); ALTER TABLE ExamResult ADD CONSTRAINT FK_examresult_studentID FOREIGN KEY (studentID) REFERENCES Student (ID); ALTER TABLE ExamResult ADD CONSTRAINT FK_examresult_examID FOREIGN KEY (examID) REFERENCES Exam (ID); SHOW TABLES; Figure 4.2. Middlegen Graphical Interface [View full size image]