Limbo SQL seeding.

limbo.sql

# Assignment 2
# Creates and populates a database for Limbo.
# Authors: Ethan Turkeltaub and Matt Maffa
# Version: 0.1.0

# Create and use the limbo_db database
drop database if exists limbo_db;
create database limbo_db;

use limbo_db;

# Create the users table
create table users (
  id int not null unique auto_increment,
  username text not null,
  email text not null,
  password text not null,
  reg_date datetime not null default Now()
);

explain users;

# Add an admin
insert into users (
  username,
  email,
  password
) values (
  'admin',
  '[email protected]',
  'gaze11e'
);

select * from users;n

# Create the stuff table
create table stuff (
  id int not null unique auto_increment,
  location_id int not null,
  description text not null,
  create_date datetime not null,
  update_date datetime not null,
  room text,
  owner text,
  finder text,
  status text
);

explain stuff;

# Create the locations table
create table locations (
  id int not null unique auto_increment,
  create_date datetime not null default Now(),
  update_date datetime not null default Now(),
  name text not null
);

explain locations;

# Populate the locations table
insert into locations (
  name
) values
  ('Sheahan Hall'),
  ('Marian Hall'),
  ('Champagnat Hall'),
  ('Leo Hall'),
  ('Student Center'),
  ('Dining Hall'),
  ('Donnelly Hall'),
  ('Lowell-Thomas Communications Center'),
  ('Hancock Center'),
  ('Library'),
  ('Our Lady Seat of Wisdom Chapel'),
  ('Byrne House'),
  ('Cornell Boathouse'),
  ('Dyson Center'),
  ('Fern Tor'),
  ('Fontaine Hall'),
  ('Fontaine Annex'),
  ('Foy Townhouses'),
  ('Fulton Street Townhouses'),
  ('New Fulton Townhouses'),
  ('Gartland Commons'),
  ('Greystone Hall'),
  ('Kieran Gatehouse'),
  ('Kirk House'),
  ('Longview Park'),
  ('Lower Townhouses'),
  ('Marist Boathouse'),
  ('McCann Center'),
  ('Midrise Hall'),
  ('New Townhouses'),
  ('St. Ann\'s Hermitage'),
  ('St. Peter\'s'),
  ('Steel Plan Art Studios'),
  ('Rotunda'),
  ('Tenney Stadium'),
  ('Tennis Pavilion'),
  ('Lower West Cedar Townhouses'),
  ('Upper West Cedar Townhouses');

select * from locations;

# mysql> source limbo.sql
# Query OK, 3 rows affected (0.04 sec)

# Query OK, 1 row affected (0.00 sec)

# Database changed
# Query OK, 0 rows affected (0.05 sec)

# +----------+----------+------+-----+-------------------+----------------+
# | Field    | Type     | Null | Key | Default           | Extra          |
# +----------+----------+------+-----+-------------------+----------------+
# | id       | int(11)  | NO   | PRI | NULL              | auto_increment |
# | username | text     | NO   |     | NULL              |                |
# | email    | text     | NO   |     | NULL              |                |
# | password | text     | NO   |     | NULL              |                |
# | reg_date | datetime | NO   |     | CURRENT_TIMESTAMP |                |
# +----------+----------+------+-----+-------------------+----------------+
# 5 rows in set (0.01 sec)

# Query OK, 1 row affected (0.00 sec)

# +----+----------+-----------------+----------+---------------------+
# | id | username | email           | password | reg_date            |
# +----+----------+-----------------+----------+---------------------+
# |  1 | admin    | [email protected] | gaze11e  | 2014-02-20 10:04:52 |
# +----+----------+-----------------+----------+---------------------+
# 1 row in set (0.00 sec)

# Query OK, 0 rows affected (0.01 sec)

# +-------------+----------+------+-----+---------+----------------+
# | Field       | Type     | Null | Key | Default | Extra          |
# +-------------+----------+------+-----+---------+----------------+
# | id          | int(11)  | NO   | PRI | NULL    | auto_increment |
# | location_id | int(11)  | NO   |     | NULL    |                |
# | description | text     | NO   |     | NULL    |                |
# | create_date | datetime | NO   |     | NULL    |                |
# | update_date | datetime | NO   |     | NULL    |                |
# | room        | text     | YES  |     | NULL    |                |
# | owner       | text     | YES  |     | NULL    |                |
# | finder      | text     | YES  |     | NULL    |                |
# | status      | text     | YES  |     | NULL    |                |
# +-------------+----------+------+-----+---------+----------------+
# 9 rows in set (0.01 sec)

# Query OK, 0 rows affected (0.02 sec)

# +-------------+----------+------+-----+-------------------+----------------+
# | Field       | Type     | Null | Key | Default           | Extra          |
# +-------------+----------+------+-----+-------------------+----------------+
# | id          | int(11)  | NO   | PRI | NULL              | auto_increment |
# | create_date | datetime | NO   |     | CURRENT_TIMESTAMP |                |
# | update_date | datetime | NO   |     | CURRENT_TIMESTAMP |                |
# | name        | text     | NO   |     | NULL              |                |
# +-------------+----------+------+-----+-------------------+----------------+
# 4 rows in set (0.00 sec)

# Query OK, 38 rows affected (0.01 sec)
# Records: 38  Duplicates: 0  Warnings: 0

# +----+---------------------+---------------------+-------------------------------------+
# | id | create_date         | update_date         | name                                |
# +----+---------------------+---------------------+-------------------------------------+
# |  1 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Sheahan Hall                        |
# |  2 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Marian Hall                         |
# |  3 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Champagnat Hall                     |
# |  4 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Leo Hall                            |
# |  5 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Student Center                      |
# |  6 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Dining Hall                         |
# |  7 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Donnelly Hall                       |
# |  8 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Lowell-Thomas Communications Center |
# |  9 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Hancock Center                      |
# | 10 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Library                             |
# | 11 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Our Lady Seat of Wisdom Chapel      |
# | 12 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Byrne House                         |
# | 13 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Cornell Boathouse                   |
# | 14 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Dyson Center                        |
# | 15 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Fern Tor                            |
# | 16 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Fontaine Hall                       |
# | 17 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Fontaine Annex                      |
# | 18 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Foy Townhouses                      |
# | 19 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Fulton Street Townhouses            |
# | 20 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | New Fulton Townhouses               |
# | 21 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Gartland Commons                    |
# | 22 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Greystone Hall                      |
# | 23 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Kieran Gatehouse                    |
# | 24 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Kirk House                          |
# | 25 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Longview Park                       |
# | 26 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Lower Townhouses                    |
# | 27 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Marist Boathouse                    |
# | 28 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | McCann Center                       |
# | 29 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Midrise Hall                        |
# | 30 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | New Townhouses                      |
# | 31 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | St. Ann's Hermitage                 |
# | 32 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | St. Peter's                         |
# | 33 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Steel Plan Art Studios              |
# | 34 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Rotunda                             |
# | 35 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Tenney Stadium                      |
# | 36 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Tennis Pavilion                     |
# | 37 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Lower West Cedar Townhouses         |
# | 38 | 2014-02-20 10:04:52 | 2014-02-20 10:04:52 | Upper West Cedar Townhouses         |
# +----+---------------------+---------------------+-------------------------------------+
# 38 rows in set (0.00 sec)