421 lines
11 KiB
MySQL
421 lines
11 KiB
MySQL
|
CREATE TABLE feedback (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
user_id INTEGER NOT NULL,
|
||
|
|
body TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE user_role (
|
||
|
|
user_id INTEGER NOT NULL,
|
||
|
|
role_name VARCHAR(64) NOT NULL,
|
||
|
|
PRIMARY KEY (user_id, role_name)
|
||
|
|
);
|
||
|
|
CREATE TABLE role (
|
||
|
|
name VARCHAR(64) NOT NULL,
|
||
|
|
PRIMARY KEY (name)
|
||
|
|
);
|
||
|
|
CREATE TABLE user (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
email TEXT NOT NULL,
|
||
|
|
password_hash CHAR(64) NOT NULL,
|
||
|
|
password_salt BINARY(20) NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP,
|
||
|
|
UNIQUE (email)
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_cc_item (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
item_id INTEGER NOT NULL,
|
||
|
|
variant_id INTEGER NOT NULL,
|
||
|
|
qty INTEGER NOT NULL,
|
||
|
|
opt_qty INTEGER NOT NULL,
|
||
|
|
buy_price INTEGER NOT NULL,
|
||
|
|
sell_price INTEGER NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE cc_item (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
category TEXT NOT NULL,
|
||
|
|
total_qty INTEGER NOT NULL,
|
||
|
|
wishlists INTEGER NOT NULL,
|
||
|
|
buy_price INTEGER NOT NULL,
|
||
|
|
sell_price INTEGER NOT NULL,
|
||
|
|
url TEXT,
|
||
|
|
barcode TEXT,
|
||
|
|
manufacturer_sku TEXT,
|
||
|
|
amazon_asin TEXT,
|
||
|
|
msrp INTEGER,
|
||
|
|
brand TEXT,
|
||
|
|
weight TEXT NOT NULL,
|
||
|
|
description TEXT,
|
||
|
|
max_qty INTEGER,
|
||
|
|
domestic_only INTEGER NOT NULL,
|
||
|
|
tax_exempt INTEGER NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE sold_decks (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
category_id INTEGER,
|
||
|
|
created_by TEXT NOT NULL,
|
||
|
|
sell_price INTEGER,
|
||
|
|
ydk_deck_id INTEGER NOT NULL,
|
||
|
|
deck_sold INTEGER NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE constructed_deck (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
category_id INTEGER,
|
||
|
|
created_by TEXT NOT NULL,
|
||
|
|
sell_price INTEGER,
|
||
|
|
ydk_deck_id INTEGER NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE constructed_deck_item (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
ygo_card_id INTEGER NOT NULL,
|
||
|
|
deck_id INTEGER NOT NULL,
|
||
|
|
sell_price INTEGER,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_set_item (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
item_id INTEGER NOT NULL,
|
||
|
|
variant_id INTEGER NOT NULL,
|
||
|
|
qty INTEGER NOT NULL,
|
||
|
|
opt_qty INTEGER NOT NULL,
|
||
|
|
buy_price INTEGER NOT NULL,
|
||
|
|
sell_price INTEGER NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_price (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
passcode_id INTEGER NOT NULL,
|
||
|
|
cardmarket_price INTEGER NOT NULL,
|
||
|
|
tcgplayer_price INTEGER NOT NULL,
|
||
|
|
ebay_price INTEGER NOT NULL,
|
||
|
|
amazon_price INTEGER NOT NULL,
|
||
|
|
coolstuffinc_price INTEGER NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_alternative_artwork (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
passcode_id INTEGER NOT NULL,
|
||
|
|
alternate_id INTEGER NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_banlist (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
passcode_id INTEGER NOT NULL,
|
||
|
|
ban_goat_id INTEGER,
|
||
|
|
ban_ocg_id INTEGER,
|
||
|
|
ban_tcg_id INTEGER,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP,
|
||
|
|
UNIQUE (passcode_id)
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_format (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
passcode_id INTEGER,
|
||
|
|
common_charity_id INTEGER,
|
||
|
|
duel_links_id INTEGER,
|
||
|
|
edison_id INTEGER,
|
||
|
|
goat_id INTEGER,
|
||
|
|
ocg_id INTEGER,
|
||
|
|
ocg_goat_id INTEGER,
|
||
|
|
speed_duel_id INTEGER,
|
||
|
|
tcg_id INTEGER,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_linkmarker (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
passcode_id INTEGER NOT NULL,
|
||
|
|
bottom_id INTEGER,
|
||
|
|
bottom_left_id INTEGER,
|
||
|
|
bottom_right_id INTEGER,
|
||
|
|
left_id INTEGER,
|
||
|
|
right_id INTEGER,
|
||
|
|
top_id INTEGER,
|
||
|
|
top_left_id INTEGER,
|
||
|
|
top_right_id INTEGER,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_set (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
passcode_id INTEGER NOT NULL,
|
||
|
|
name_id INTEGER NOT NULL,
|
||
|
|
code_id INTEGER NOT NULL,
|
||
|
|
rarity_id INTEGER NOT NULL,
|
||
|
|
rarity_code_id INTEGER NOT NULL,
|
||
|
|
edition_id INTEGER NOT NULL,
|
||
|
|
url TEXT NOT NULL,
|
||
|
|
price INTEGER NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_set_edition (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP,
|
||
|
|
UNIQUE (name)
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_set_rarity (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP,
|
||
|
|
UNIQUE (name)
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_set_code (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP,
|
||
|
|
UNIQUE (name)
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_set_name (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP,
|
||
|
|
UNIQUE (name)
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_card_misc_info (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
passcode_id INTEGER NOT NULL,
|
||
|
|
views INTEGER NOT NULL,
|
||
|
|
viewsweek INTEGER NOT NULL,
|
||
|
|
upvotes INTEGER NOT NULL,
|
||
|
|
downvotes INTEGER NOT NULL,
|
||
|
|
beta_id INTEGER,
|
||
|
|
beta_name_id INTEGER,
|
||
|
|
staple INTEGER,
|
||
|
|
konami_id INTEGER NOT NULL,
|
||
|
|
treated_as_id INTEGER,
|
||
|
|
has_effect INTEGER,
|
||
|
|
question_atk INTEGER,
|
||
|
|
question_def INTEGER,
|
||
|
|
tcg_date TIMESTAMP,
|
||
|
|
ocg_date TIMESTAMP,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_card (
|
||
|
|
id INTEGER NOT NULL PRIMARY KEY,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
desc TEXT NOT NULL,
|
||
|
|
archetype_id INTEGER,
|
||
|
|
attribute_id INTEGER,
|
||
|
|
atk INTEGER,
|
||
|
|
def INTEGER,
|
||
|
|
scale INTEGER,
|
||
|
|
level INTEGER,
|
||
|
|
frame_type_id INTEGER NOT NULL,
|
||
|
|
race_id INTEGER NOT NULL,
|
||
|
|
card_type_id INTEGER NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_card_attribute (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP,
|
||
|
|
UNIQUE (name)
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_card_archetype (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP,
|
||
|
|
UNIQUE (name)
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_card_race (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP,
|
||
|
|
UNIQUE (name)
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_card_frame_type (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP,
|
||
|
|
UNIQUE (name)
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_card_type (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP,
|
||
|
|
UNIQUE (name)
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_card_name (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP,
|
||
|
|
UNIQUE (name)
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_name_mixin (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP,
|
||
|
|
UNIQUE (name)
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_set_rarity_code (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP,
|
||
|
|
UNIQUE (name)
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_linkmarker_name (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_format_name (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_banlist_name (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE variant_language (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE variant_condition (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE variant (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE ydk_deck (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
category_id INTEGER,
|
||
|
|
created_by TEXT NOT NULL,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP,
|
||
|
|
UNIQUE (name)
|
||
|
|
);
|
||
|
|
CREATE TABLE deck_item (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
deck_id INTEGER NOT NULL,
|
||
|
|
kind INTEGER NOT NULL,
|
||
|
|
passcode INTEGER NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_format (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
passcode INTEGER NOT NULL,
|
||
|
|
format TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_misc (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
passcode INTEGER NOT NULL,
|
||
|
|
beta_name INTEGER NOT NULL,
|
||
|
|
views INTEGER NOT NULL,
|
||
|
|
viewsweek INTEGER NOT NULL,
|
||
|
|
upvotes INTEGER NOT NULL,
|
||
|
|
downvotes INTEGER NOT NULL,
|
||
|
|
tcg_date TEXT NOT NULL,
|
||
|
|
ocg_date TEXT NOT NULL,
|
||
|
|
konami_id INTEGER NOT NULL,
|
||
|
|
has_effect INTEGER NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_price (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
passcode INTEGER NOT NULL,
|
||
|
|
cardmarket REAL NOT NULL,
|
||
|
|
tcgplayer REAL NOT NULL,
|
||
|
|
ebay REAL NOT NULL,
|
||
|
|
amazon REAL NOT NULL,
|
||
|
|
coolstuffinc REAL NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_set (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
code TEXT NOT NULL,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
passcode INTEGER NOT NULL,
|
||
|
|
price REAL NOT NULL,
|
||
|
|
rarity TEXT NOT NULL,
|
||
|
|
rarity_code TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE ygo_info (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
desc TEXT NOT NULL,
|
||
|
|
frame_type TEXT NOT NULL,
|
||
|
|
type TEXT NOT NULL,
|
||
|
|
passcode INTEGER NOT NULL,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
race TEXT NOT NULL,
|
||
|
|
archetype TEXT,
|
||
|
|
attribute TEXT,
|
||
|
|
linkmarkers TEXT,
|
||
|
|
atk INTEGER,
|
||
|
|
def INTEGER,
|
||
|
|
level INTEGER,
|
||
|
|
linkval INTEGER,
|
||
|
|
scale INTEGER,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE category (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
left INTEGER NOT NULL,
|
||
|
|
right INTEGER NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE TABLE attachment (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
file_key VARCHAR(255) NOT NULL,
|
||
|
|
content_type VARCHAR(255) NOT NULL,
|
||
|
|
file_size INTEGER NOT NULL,
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP,
|
||
|
|
UNIQUE (file_key)
|
||
|
|
);
|