3 CREATE DATABASE `kworship` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
5 USE DATABASE `kworship`;
9 CREATE TABLE CssStyleSheet (
10 `id` INT NOT NULL AUTO_INCREMENT,
14 CREATE TABLE CssRule (
15 `id` INT NOT NULL AUTO_INCREMENT,
16 `style_sheet_id` INT NOT NULL,
18 UNIQUE (`style_sheet_id`),
19 FOREIGN KEY (`style_sheet_id`) REFERENCES CssStyleSheet (`id`)
20 ON UPDATE CASCADE ON DELETE RESTRICT
23 CREATE TABLE CssRuleCriteriaScope (
24 `rule_id` INT NOT NULL,
26 `type` VARCHAR(32) NULL,
27 `name` VARCHAR(32) NULL,
28 PRIMARY KEY (`rule_id`, `order`),
29 FOREIGN KEY (`rule_id`) REFERENCES CssRule (`id`)
30 ON UPDATE CASCADE ON DELETE RESTRICT
33 CREATE TABLE CssRuleCriteriaClass (
34 `rule_id` INT NOT NULL,
35 `class` VARCHAR(64) NOT NULL,
36 PRIMARY KEY (`rule_id`, `class`),
37 FOREIGN KEY (`rule_id`) REFERENCES CssRule (`id`)
38 ON UPDATE CASCADE ON DELETE RESTRICT
41 CREATE TABLE CssRuleUsedClass (
42 `rule_id` INT NOT NULL,
43 `class` VARCHAR(64) NOT NULL,
44 PRIMARY KEY (`rule_id`, `class`),
45 FOREIGN KEY (`rule_id`) REFERENCES CssRule (`id`)
46 ON UPDATE CASCADE ON DELETE RESTRICT
49 CREATE TABLE CssRuleStyle (
50 `rule_id` INT NOT NULL,
51 `style` VARCHAR(64) NOT NULL,
52 `value` VARCHAR(256) NOT NULL,
53 PRIMARY KEY (`rule_id`, `style`),
54 FOREIGN KEY (`rule_id`) REFERENCES CssRule (`id`)
55 ON UPDATE CASCADE ON DELETE RESTRICT
61 `id` INT NOT NULL AUTO_INCREMENT,
62 `name` VARCHAR(128) NOT NULL,
63 `css_style_sheet_id` INT NULL,
66 UNIQUE (`css_style_sheet_id`),
67 FOREIGN KEY (`css_style_sheet_id`) REFERENCES CssStyleSheet (`id`)
68 ON UPDATE CASCADE ON DELETE CASCADE
71 CREATE TABLE SongBook (
72 `id` INT NOT NULL AUTO_INCREMENT,
73 `abreviation` VARCHAR(8) NOT NULL,
74 `name` VARCHAR(128) NOT NULL,
75 `description` MEDIUMTEXT NULL,
77 UNIQUE (`abreviation`)
80 CREATE TABLE SongVersion (
81 `id` INT NOT NULL AUTO_INCREMENT,
82 `song_id` INT NOT NULL,
83 `name` VARCHAR(128) NULL,
84 `css_style_sheet_id` INT NULL,
85 `writer` VARCHAR(128) NULL,
86 `copyright` VARCHAR(128) NULL,
87 `lyrics` TEXT NOT NULL
89 UNIQUE (`song_id`, `name`),
90 FOREIGN KEY (`song_id`) REFERENCES Song (`id`)
91 ON UPDATE CASCADE ON DELETE RESTRICT,
92 FOREIGN KEY (`css_style_sheet_id`) REFERENCES CssStyleSheet (`id`)
93 ON UPDATE CASCADE ON DELETE CASCADE
96 CREATE TABLE SongBookSong (
97 `book_id` INT NOT NULL,
98 `book_number` INT NOT NULL,
99 `version_id` INT NOT NULL,
100 PRIMARY KEY (`book_id`, `book_number`),
101 FOREIGN KEY (`book_id`) REFERENCES SongBook (`id`)
102 ON UPDATE CASCADE ON DELETE RESTRICT,
103 FOREIGN KEY (`version_id`) REFERENCES SongVersion (`id`)
104 ON UPDATE CASCADE ON DELETE RESTRICT
107 CREATE TABLE SongMedia (
108 `id` INT NOT NULL AUTO_INCREMENT,
109 `version_id` INT NOT NULL,
110 `url` VARCHAR(128) NOT NULL,
111 `audio_use` BOOLEAN NOT NULL DEFAULT FALSE,
112 `audio_volume` FLOAT NOT NULL DEFAULT 1.0,
113 `video_use` BOOLEAN NOT NULL DEFAULT FALSE,
115 FOREIGN KEY (`version_id`) REFERENCES SongVersion (`id`)
116 ON UPDATE CASCADE ON DELETE RESTRICT
120 /* Populate the database with some data */