summaryrefslogtreecommitdiff
path: root/design/database.sql
blob: 475dac28d29351e08b843c38bcbb854f53d6cb2f (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
-- Foursquare Community Site
-- 
-- Copyright (C) 2011 Foursquare Church.
-- 
-- Developers: Jesse Morgan <jmorgan@foursquarestaff.com>


-- The following cleans up existing data

DROP DATABASE IF EXISTS p4scommunity;
DROP USER p4scommunity@localhost;

-- The following creates a database and user

CREATE DATABASE p4scommunity;

CREATE USER p4scommunity@localhost IDENTIFIED BY 'password';

GRANT ALL ON p4scommunity.* TO p4scommunity@localhost;

USE p4scommunity;

-- The following creates the table structure

CREATE TABLE category (
    id          INTEGER     UNSIGNED NOT NULL AUTO_INCREMENT,
    shortname   VARCHAR(30) NOT NULL,
    name        VARCHAR(30) NOT NULL,
    description VARCHAR(255) NOT NULL,

    PRIMARY KEY(id)
);

CREATE TABLE source (
    id          INTEGER     UNSIGNED NOT NULL AUTO_INCREMENT,
    name        VARCHAR(60) NOT NULL,

    PRIMARY KEY(id),
    UNIQUE  KEY(name)
);

CREATE TABLE post (
    id          INTEGER     UNSIGNED NOT NULL AUTO_INCREMENT,
    name        VARCHAR(60) NOT NULL,
    category_id INTEGER     UNSIGNED NOT NULL,
    created     DATETIME    NOT NULL,
    description TEXT        NOT NULL,
    location    VARCHAR(100) NOT NULL,
    
    email       VARCHAR(255) NOT NULL,
    secretid    VARCHAR(32)  NOT NULL,

    source_id   INTEGER     UNSIGNED NOT NULL,
    reason      VARCHAR(255) NULL,
    stage      ENUM('verification',
                    'moderation',
                    'approved',
                    'rejected',
                    'deleted') NOT NULL DEFAULT 'verification',

    PRIMARY KEY(id),
    UNIQUE  KEY(secretid)
);

CREATE TABLE image (
    id          INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    post_id     INTEGER UNSIGNED NOT NULL,

    PRIMARY KEY(id)
);

CREATE TABLE user (
    id          INTEGER     UNSIGNED NOT NULL AUTO_INCREMENT,
    name        VARCHAR(60) NOT NULL,
    email       VARCHAR(255) NOT NULL,
    password    VARCHAR(40) NOT NULL,
    source_id   INTEGER     NOT NULL,
    admin       TINYINT(1)  NOT NULL DEFAULT 0,
    notify      TINYINT(1)  NOT NULL,

    PRIMARY KEY(id),
    UNIQUE  KEY(email)
);

CREATE TABLE page (
    id          INTEGER     UNSIGNED NOT NULL AUTO_INCREMENT,
    title       VARCHAR(60) NOT NULL,
    url         VARCHAR(60) NOT NULL,
    content     TEXT NOT NULL,

    PRIMARY KEY(id),
    UNIQUE  KEY(url)
);

-- The following creates some sample data
INSERT INTO category (name, shortname) VALUES 
    ('Give',    'give'),
    ('Needs',   'needs'),
    ('Jobs',    'jobs'), 
    ('Housing', 'housing'),
    ('Events',  'events');

INSERT INTO source (name) VALUES ('Foursquare Church');

INSERT INTO user (name, email, password, source_id, admin)
    VALUES ('Jesse Morgan', 'jmorgan@foursquarestaff.com',
        'password-sha1', 1, 1);