Pru0ZVtH

1SET SESSION storage_engine = "InnoDB";
2
3DROP DATABASE IF EXISTS demo_blog;
4CREATE DATABASE demo_blog;
5
6USE demo_blog;
7ALTER DATABASE demo_blog CHARACTER SET "utf8";
8
9GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES, CREATE TEMPORARY TABLES ON `blog_demo`.* TO 'Tester'@'localhost' IDENTIFIED BY 'queries';
10
11DROP TABLE IF EXISTS post;
12CREATE TABLE post (
13    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
14    author_id INT NOT NULL REFERENCES author(id),
15    slug VARCHAR(100) NOT NULL UNIQUE,
16    title VARCHAR(400) NOT NULL,
17    content MEDIUMTEXT NOT NULL,
18    published DATETIME NOT NULL,
19    created DATETIME NOT NULL,
20    updated TIMESTAMP NOT NULL,
21    KEY (published)
22    );
23
24DROP TABLE IF EXISTS author;
25CREATE TABLE authors (
26    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
27    username VARCHAR(100) NOT NULL UNIQUE,
28    name VARCHAR(100) NOT NULL
29);
30
31DROP TABLE IF EXISTS tag;
32CREATE TABLE tag (
33    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
34    name VARCHAR(100) NOT NULL
35);
36
37DROP TABLE IF EXISTS post_tag;
38CREATE TABLE post_tag (
39    post_id INT NOT NULL REFERENCES post(id),
40    tag_id INT NOT NULL REFERENCES tag(id),
41);
42
43DROP TABLE IF EXISTS category;
44CREATE TABLE category (
45    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
46    parent_id INT,
47    name VARCHAR(100) NOT NULL
48);
49
50DROP TABLE IF EXISTS post_category;
51CREATE TABLE post_category (
52    post_id INT NOT NULL REFERENCES post(id),
53    category_id INT NOT NULL REFERENCES category(id),
54);