| 1 |
lars |
1 |
/* create users table */
|
|
|
2 |
CREATE TABLE users (
|
|
|
3 |
username VARCHAR(128) NOT NULL PRIMARY KEY,
|
|
|
4 |
email VARCHAR(128) NOT NULL,
|
|
|
5 |
password VARCHAR(128) NOT NULL, /* in plain text */
|
|
|
6 |
role INTEGER NOT NULL, /* 0: normal user, 1: administrator */
|
|
|
7 |
first_name VARCHAR(128),
|
|
|
8 |
last_name VARCHAR(128)
|
|
|
9 |
);
|
|
|
10 |
|
|
|
11 |
/* create posts table */
|
|
|
12 |
CREATE TABLE posts (
|
|
|
13 |
post_id INTEGER NOT NULL PRIMARY KEY,
|
|
|
14 |
author_id VARCHAR(128) NOT NULL
|
|
|
15 |
CONSTRAINT fk_author REFERENCES users(username),
|
|
|
16 |
create_time INTEGER NOT NULL, /* UNIX timestamp */
|
|
|
17 |
title VARCHAR(256) NOT NULL, /* title of the post */
|
|
|
18 |
content TEXT, /* post body */
|
|
|
19 |
status INTEGER NOT NULL /* 0: published; 1: draft; 2: pending; 2: denied */
|
|
|
20 |
);
|
|
|
21 |
|
|
|
22 |
/* insert some initial data records for testing */
|
|
|
23 |
INSERT INTO users VALUES ('admin', 'admin@example.com', 'demo', 1, 'Qiang', 'Xue');
|
|
|
24 |
INSERT INTO users VALUES ('demo', 'demo@example.com', 'demo', 0, 'Wei', 'Zhuo');
|
|
|
25 |
INSERT INTO posts VALUES (NULL, 'admin', 1175708482, 'first post', 'this is my first post', 0);
|