1 CREATE DATABASE IF NOT EXISTS `CryAssetDB`;
5 CREATE TABLE `categories` (
6 `category_id` int(10) NOT NULL auto_increment,
7 `category` varchar(255) UNIQUE NOT NULL,
8 `order_id` int(10) NOT NULL,
9 PRIMARY KEY (`category_id`)
12 CREATE TABLE IF NOT EXISTS `tags` (
13 `id` int(10) unsigned NOT NULL auto_increment,
14 `tag` varchar(64) NOT NULL,
15 `category_id` int(10) default NULL,
17 KEY `FK_tags_to_category_id` (`category_id`),
18 UNIQUE KEY `tag` (`tag`),
19 CONSTRAINT `FK_tags_to_category_id` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE
22 CREATE TABLE IF NOT EXISTS `projects` (
23 `id` int(10) unsigned NOT NULL auto_increment,
24 `name` varchar(32) NOT NULL COMMENT 'Convenient project name for easy filtering',
26 UNIQUE KEY `name` (`name`)
29 CREATE TABLE IF NOT EXISTS `asset_inventory` (
30 `id` bigint(20) unsigned NOT NULL auto_increment,
31 `project_id` int(10) unsigned NOT NULL,
32 `relpath` varchar(255) NOT NULL COMMENT 'relpath to the asset',
33 `description` varchar(255) default NULL,
35 KEY `FK_asset_inventory_to_project_id` (`project_id`),
36 CONSTRAINT `FK_asset_inventory_to_project_id` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
39 CREATE TABLE IF NOT EXISTS `asset_tags` (
40 `asset_id` bigint(20) unsigned NOT NULL,
41 `tag_id` int(10) unsigned NOT NULL,
42 PRIMARY KEY (`asset_id`,`tag_id`),
43 KEY `FK_asset_tags_to_asset_id` (`asset_id`),
44 KEY `FK_asset_tags_to_tag_id` (`tag_id`),
45 CONSTRAINT `FK_asset_tags_to_asset_id` FOREIGN KEY (`asset_id`) REFERENCES `asset_inventory` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
46 CONSTRAINT `FK_asset_tags_to_tag_id` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE