wolfbeacon-server-sql-commands-OLD.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,
                           name varchar(90));

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

CREATE TABLE hackathon_events (
  id int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  creatorId int(10) NOT NULL,
  name text,
  createdAt timestamp,
  lastUpdatedAt timestamp NULL DEFAULT NULL,
  eventDate timestamp,
  eventStatus int(1),
  eventAddress text,
  city varchar(50),
  state varchar(45),
  zip varchar(8),
  country varchar(45),
  eventPictureUrl text,
  CONSTRAINT hackathon_events_users__fk FOREIGN KEY (creatorId) REFERENCES users(id)
);

CREATE TABLE attending_status (
  userId int(10),
  eventId int(10),
  attendingStatus int(1),
  CONSTRAINT attending_status_events__fk FOREIGN KEY (eventId) REFERENCES hackathon_events(id),
  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;