yenu/resources/migrations/20170302232609-create-initial-structure.up.sql

40 lines
882 B
MySQL
Raw Permalink Normal View History

CREATE TABLE images (
id INTEGER PRIMARY KEY AUTOINCREMENT,
hash TEXT NOT NULL UNIQUE,
title TEXT,
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
--;;
CREATE TABLE tags (
id INTEGER PRIMARY KEY AUTOINCREMENT,
tagname TEXT NOT NULL UNIQUE
);
--;;
CREATE TABLE image_tags (
id INTEGER PRIMARY KEY AUTOINCREMENT,
image_id INTEGER NOT NULL,
tag_id INTEGER NOT NULL
)
--;;
CREATE TABLE comments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
image_id INTEGER NOT NULL,
author TEXT,
comment TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
--;;
CREATE INDEX hash_index ON images (hash);
CREATE INDEX tagname_index ON tags (tagname);
CREATE INDEX image_tags_index ON image_tags (image_id, tag_id);
CREATE INDEX author_index ON comments (author);