wolfbeacon-server-sql-commands-NEW.txt

CREATE DATABASE wolfbeacon_data;
USE wolfbeacon_data;

CREATE TABLE accounts (id int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                       naturalId varchar(100) NOT NULL UNIQUE,
                       firstName varchar(40) NOT NULL,
                       lastName varchar(50) NOT NULL,
                       email varchar(254) NOT NULL,
                       pictureUrl text);

CREATE TABLE accounts_roles (accountId int(10) NOT NULL,
                    role varchar(25) NOT NULL);

CREATE TABLE accounts_permissions (accountId int(10) NOT NULL,
                          permission varchar(50) NOT NULL);


CREATE TABLE mailing_list (id int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                           emailAddress varchar(254) NOT NULL UNIQUE,
                           title varchar(90));

CREATE TABLE users (
  id int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  createdAt timestamp,
  lastUpdatedAt timestamp NULL DEFAULT NULL
);

CREATE TABLE hackathon
(
    id BIGINT(20) PRIMARY KEY NOT NULL,
    title VARCHAR(255) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    last_updated_date DATE,
    year YEAR(4) NOT NULL,
    location VARCHAR(255) NOT NULL,
    host VARCHAR(255),
    length INT(10),
    travel TINYINT(1),
    prize TINYINT(1),
    high_schoolers TINYINT(1),
    cost VARCHAR(255),
    facebook_link TEXT,
    twitter_link TEXT,
    gplus_link TEXT,
    image_link TEXT,
    notes BLOB,
    event_link TEXT,
    latitude DOUBLE,
    longitude DOUBLE,
    size VARCHAR(255)
);

CREATE TABLE attending_status (
  userId int(10),
  eventId int(10),
  attendingStatus int(1),
  CONSTRAINT attending_status_users__fk FOREIGN KEY (userId) REFERENCES users(id)
);

CREATE USER 'wolfbeacon_admin'@'localhost' IDENTIFIED BY 'hackerpass';
GRANT ALL PRIVILEGES ON * . * TO 'wolfbeacon_admin'@'localhost';
FLUSH PRIVILEGES;