summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorJesse Morgan <jesse@jesterpm.net>2011-05-10 17:06:44 -0700
committerJesse Morgan <jesse@jesterpm.net ; true>2011-05-10 17:06:44 -0700
commite159ae5209a561043ceb89aa640b207df15181b7 (patch)
tree721ac1376101e769bb109fc494d03b421fb8fe3e
parentd064f7a60951c0278b41541953556d835a849b24 (diff)
Started the database
-rw-r--r--design/database.sql65
1 files changed, 65 insertions, 0 deletions
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 <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,
+ 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');