CREATE TABLE categories (id BIGINT UNSIGNED AUTO_INCREMENT, delete_rev BIGINT UNSIGNED, root_id BIGINT, lft INT, rgt INT, level SMALLINT, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE category_data (owner_id BIGINT UNSIGNED, lang CHAR(2), revision_id BIGINT UNSIGNED, value VARCHAR(32), created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY(owner_id, lang, revision_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE items (id BIGINT UNSIGNED AUTO_INCREMENT, category_id BIGINT UNSIGNED NOT NULL, delete_rev BIGINT UNSIGNED, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, INDEX category_id_idx (category_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE item_data (owner_id BIGINT UNSIGNED, lang CHAR(2), revision_id BIGINT UNSIGNED, value VARCHAR(32), created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY(owner_id, lang, revision_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE properties (id BIGINT UNSIGNED AUTO_INCREMENT, property_group_id BIGINT UNSIGNED NOT NULL, delete_rev BIGINT UNSIGNED, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, INDEX property_group_id_idx (property_group_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE property_data (owner_id BIGINT UNSIGNED, lang CHAR(2), revision_id BIGINT UNSIGNED, value VARCHAR(32), created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY(owner_id, lang, revision_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE property_groups (id BIGINT UNSIGNED AUTO_INCREMENT, category_id BIGINT UNSIGNED NOT NULL, delete_rev BIGINT UNSIGNED, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, INDEX category_id_idx (category_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE property_group_data (owner_id BIGINT UNSIGNED, lang CHAR(2), revision_id BIGINT UNSIGNED, value VARCHAR(32), created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY(owner_id, lang, revision_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE property_values (id BIGINT UNSIGNED AUTO_INCREMENT, property_id BIGINT UNSIGNED NOT NULL, item_id BIGINT UNSIGNED NOT NULL, delete_rev BIGINT UNSIGNED, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, INDEX property_id_idx (property_id), INDEX item_id_idx (item_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE property_value_data (owner_id BIGINT UNSIGNED, lang CHAR(2), revision_id BIGINT UNSIGNED, value VARCHAR(32), created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY(owner_id, lang, revision_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE revisions (id BIGINT UNSIGNED AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, rev_no BIGINT UNSIGNED NOT NULL, is_head TINYINT(1) NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, INDEX user_id_idx (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE revision_ratings (revision_id BIGINT UNSIGNED, user_id BIGINT UNSIGNED, rating TINYINT(1) NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY(revision_id, user_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE users (id BIGINT UNSIGNED AUTO_INCREMENT, fb_id BIGINT UNSIGNED, username VARCHAR(32) NOT NULL, password_salt VARCHAR(32), password_hash VARCHAR(32), email VARCHAR(255) NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
ALTER TABLE category_data ADD CONSTRAINT category_data_revision_id_revisions_id FOREIGN KEY (revision_id) REFERENCES revisions(id);
ALTER TABLE category_data ADD CONSTRAINT category_data_owner_id_categories_id FOREIGN KEY (owner_id) REFERENCES categories(id);
ALTER TABLE items ADD CONSTRAINT items_category_id_categories_id FOREIGN KEY (category_id) REFERENCES categories(id);
ALTER TABLE item_data ADD CONSTRAINT item_data_revision_id_revisions_id FOREIGN KEY (revision_id) REFERENCES revisions(id);
ALTER TABLE item_data ADD CONSTRAINT item_data_owner_id_items_id FOREIGN KEY (owner_id) REFERENCES items(id);
ALTER TABLE properties ADD CONSTRAINT properties_property_group_id_property_groups_id FOREIGN KEY (property_group_id) REFERENCES property_groups(id);
ALTER TABLE property_data ADD CONSTRAINT property_data_revision_id_revisions_id FOREIGN KEY (revision_id) REFERENCES revisions(id);
ALTER TABLE property_data ADD CONSTRAINT property_data_owner_id_properties_id FOREIGN KEY (owner_id) REFERENCES properties(id);
ALTER TABLE property_groups ADD CONSTRAINT property_groups_category_id_categories_id FOREIGN KEY (category_id) REFERENCES categories(id);
ALTER TABLE property_group_data ADD CONSTRAINT property_group_data_revision_id_revisions_id FOREIGN KEY (revision_id) REFERENCES revisions(id);
ALTER TABLE property_group_data ADD CONSTRAINT property_group_data_owner_id_property_groups_id FOREIGN KEY (owner_id) REFERENCES property_groups(id);
ALTER TABLE property_values ADD CONSTRAINT property_values_property_id_properties_id FOREIGN KEY (property_id) REFERENCES properties(id);
ALTER TABLE property_values ADD CONSTRAINT property_values_item_id_items_id FOREIGN KEY (item_id) REFERENCES items(id);
ALTER TABLE property_value_data ADD CONSTRAINT property_value_data_revision_id_revisions_id FOREIGN KEY (revision_id) REFERENCES revisions(id);
ALTER TABLE property_value_data ADD CONSTRAINT property_value_data_owner_id_property_values_id FOREIGN KEY (owner_id) REFERENCES property_values(id);
ALTER TABLE revisions ADD CONSTRAINT revisions_user_id_users_id FOREIGN KEY (user_id) REFERENCES users(id);
ALTER TABLE revision_ratings ADD CONSTRAINT revision_ratings_user_id_users_id FOREIGN KEY (user_id) REFERENCES users(id);
ALTER TABLE revision_ratings ADD CONSTRAINT revision_ratings_revision_id_revisions_id FOREIGN KEY (revision_id) REFERENCES revisions(id);