From e159ae5209a561043ceb89aa640b207df15181b7 Mon Sep 17 00:00:00 2001 From: Jesse Morgan Date: Tue, 10 May 2011 17:06:44 -0700 Subject: Started the database --- design/database.sql | 65 +++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 65 insertions(+) (limited to 'design') diff --git a/design/database.sql b/design/database.sql index e69de29..c056c1c 100644 --- a/design/database.sql +++ b/design/database.sql @@ -0,0 +1,65 @@ +-- Foursquare Community Site +-- +-- Copyright (C) 2011 Foursquare Church. +-- +-- Developers: Jesse Morgan + + +-- 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, + name VARCHAR(30) 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, + + email VARCHAR(255) NOT NULL, + secretid VARCHAR(32) NOT NULL, + + source_id INTEGER UNSIGNED NOT NULL, + + PRIMARY KEY(id), + UNIQUE KEY(secretid) +); + + +-- The following creates some sample data +INSERT INTO category (name) VALUES + ('Jobs'), + ('Housing'), + ('Events'), + ('For Sale'), + ('Needs'); + +INSERT INTO source (name) VALUES ('Foursquare Church'); -- cgit v1.2.3