DPAqK1EM

1CREATE DATABASE mstation;
2
3\connect mstation;
4
5DROP TABLE IF EXISTS artists;
6CREATE TABLE artists (
7	  id SERIAL,
8	  name VARCHAR(128) NOT NULL ,
9	  name_kana VARCHAR(255) NOT NULL ,
10	  CREATEd_at TIMESTAMP NOT NULL ,
11	  updated_at TIMESTAMP NULL ,
12	  deleted_at TIMESTAMP NULL,
13	  constraint artists_pk PRIMARY KEY (id)
14);
15
16DROP TABLE IF EXISTS badges;
17create table badges (
18	  id SERIAL ,
19	  title VARCHAR(255) NOT NULL ,
20	  code VARCHAR(64) NOT NULL ,
21	  badge_desc TEXT NOT NULL ,
22	  type INT NULL ,
23	  cond_type INT NULL ,
24	  has_award BOOLEAN  NOT NULL ,
25	  award_desc TEXT NULL ,
26	  award_url VARCHAR(255) NULL ,
27	  CREATEd_at TIMESTAMP NOT NULL ,
28	  updated_at TIMESTAMP NULL ,
29	  deleted_at TIMESTAMP NULL ,
30	  artist_id INTEGER REFERENCES artists NOT NULL ,
31	  constraint badges_pk PRIMARY KEY (id) 
32);
33
34DROP TABLE IF EXISTS programs;
35create table programs (
36	  id SERIAL ,
37	  airdate TIMESTAMP NOT NULL ,
38	  title VARCHAR(255) NULL ,
39	  subtitle VARCHAR(255) NULL ,
40	  program_desc TEXT NULL ,
41	  badge_id INTEGER REFERENCES badges NOT NULL ,
42	  CREATEd_at TIMESTAMP NOT NULL ,
43	  updated_at TIMESTAMP NULL ,
44	  deleted_at TIMESTAMP NULL ,
45	  constraint programs_pk PRIMARY KEY (id) 
46);
47
48DROP TABLE IF EXISTS ng_words;
49create table ng_words (
50	  id SERIAL ,
51	  word VARCHAR(64) NULL ,
52	  CREATEd_at TIMESTAMP NOT NULL ,
53	  updated_at TIMESTAMP NULL ,
54	  deleted_at TIMESTAMP NULL ,
55	  constraint ng_words_pk PRIMARY KEY (id) 
56);
57
58DROP TABLE IF EXISTS contents;
59create table contents (
60	  id SERIAL ,
61	  title VARCHAR(255) NULL ,
62	  content_desc TEXT NULL ,
63	  count INT NULL DEFAULT 0 ,
64	  CREATEd_at TIMESTAMP NOT NULL ,
65	  updated_at TIMESTAMP NULL ,
66	  deleted_at TIMESTAMP NULL ,
67	  program_id INTEGER REFERENCES programs NOT NULL ,
68	  artist_id INTEGER REFERENCES artists NOT NULL ,
69	  badge_id INTEGER REFERENCES badges NOT NULL ,
70	  constraint contents_pk PRIMARY KEY (id) 
71);
72
73DROP TABLE IF EXISTS users;
74create table users (
75	  id SERIAL ,
76	  username VARCHAR(32) NOT NULL ,
77	  password VARCHAR(32) NOT NULL ,
78	  name VARCHAR(128) NOT NULL ,
79	  CREATEd_at TIMESTAMP NULL ,
80	  updated_at TIMESTAMP NULL ,
81	  deleted_at TIMESTAMP NULL ,
82	  constraint users_pk PRIMARY KEY (id) 
83);
84
85DROP TABLE IF EXISTS notifications;
86create table notifications (
87	  id SERIAL ,
88	  title TEXT NOT NULL ,
89	  notification_desc TEXT NOT NULL ,
90	  statusbar TEXT NOT NULL ,
91	  platform INT NOT NULL ,
92	  send_at TIMESTAMP NOT NULL ,
93	  finished_at TIMESTAMP NULL ,
94	  CREATEd_at TIMESTAMP NULL ,
95	  updated_at TIMESTAMP NULL ,
96	  deleted_at TIMESTAMP NULL ,
97	  constraint notifications_pk PRIMARY KEY (id) 
98);