# trailblazerdbbuild.sql v1.0 12/31/01
# www.embeddedlinuxinterfacing.com
#
# The original location of this code is
# http://www.embeddedlinuxinterfacing.com/chapters/12/
#
# Copyright (C) 2001 by Craig Hollabaugh
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU Library General Public License as
# published by the Free Software Foundation; either version 2 of the
# License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
# Library General Public License for more details.
#
# You should have received a copy of the GNU Library General Public
# License along with this program; if not, write to the
# Free Software Foundation, Inc.,
# 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA

# The Project Trailblazer database uses the MySQL database engine. See
# MySQL Documentation at http://www.mysql.com/doc/ for a comprehensive
# discussion of MySQL features and functionality.

#
# As root,
# mysql < trailblazerdbbuild.sql
#

# First, we need to create the database itself
CREATE database trailblazer;

# Now grant privileges.
# grant select, insert and update privileges on all trailblazer database
# tables to a user called trailblazer who must access the database
# from the host running the database. This user, trailblazer, has a
# password tb.
GRANT SELECT, INSERT, UPDATE on trailblazer.*
                             to trailblazer@localhost identified by 'tb';

# start using the trailblazer database
USE trailblazer;
# create the locations table
# This table stores Silverjack location information
CREATE TABLE locations (
  location smallint DEFAULT '0' NOT NULL,  # this last IP address octet
  description char(30),                    # text description of location
  PRIMARY KEY (location)                   # the table's key
);

# insert some testing data into locations table
INSERT INTO locations VALUES (30,'Lift 1 Base'),
INSERT INTO locations VALUES (31,'Lift 1 Top'),
INSERT INTO locations VALUES (32,'Lift 4 Base'),
INSERT INTO locations VALUES (33,'Lift 4 Top'),
INSERT INTO locations VALUES (34,'Lift 9 Base'),
INSERT INTO locations VALUES (35,'Lift 9 Top'),

# create the guests table
# This table stores Silverjack guest information
CREATE TABLE guests (
  timestamp timestamp(14),                  # record creation timestamp
  first char(20),                           # guest first name
  last char(20),                            # guest last name
  passID char(20) NOT NULL,                 # their pass' number
  passValid tinyint,                        # is the pass valid or not?
  PRIMARY KEY (passID)                      # the table's key
);

# insert some testing data into guests table
INSERT INTO guests VALUES (20011227130620,'Melanie','Kline',  1000,1);
INSERT INTO guests VALUES (20011227130649,'Robert', 'Cort',   1001,1);
INSERT INTO guests VALUES (20011227130720,'John',   'Stephen',1002,1);
INSERT INTO guests VALUES (20011227130729,'Scott',  'Kidner', 1003,1);
INSERT INTO guests VALUES (20011227133729,'Maggi',  'Ann',    1004,0);

# create the accesses table
# This table stores Silverjack access information. For example,
# if a guest accesses lift 1, an entry for that access is stored
# here.
CREATE TABLE accesses (
  timestamp timestamp(14),  # timestamp of access
  location smallint,        # the access location
  passID char(20)           # the pass that accessed the location
);

# insert some testing data into accesses table
INSERT INTO accesses VALUES (20011227133001,30,1000);
INSERT INTO accesses VALUES (20011227133006,30,1001);
INSERT INTO accesses VALUES (20011227133008,30,1002);
INSERT INTO accesses VALUES (20011227133010,30,1003);
INSERT INTO accesses VALUES (20011227134311,34,1000);
INSERT INTO accesses VALUES (20011227134327,34,1003);
INSERT INTO accesses VALUES (20011227135323,32,1002);
INSERT INTO accesses VALUES (20011227135354,32,1001);

# create the images table
# This table stores Silverjack image information. When a field
# device stores an image file, it will inform the database of
# that storage with location and filename.

CREATE TABLE images (
  timestamp timestamp(14),    # timestamp of image storage
  location smallint,          # the image location
  filename char(30)           # the image filename
);

# create the temperatures table
# This table stores Silverjack temperature information.

CREATE TABLE temperatures (
  timestamp timestamp(14),    # timestamp of temperature storage
  location smallint,          # the temperature location
  temperature tinyint         # the actual temperature
);

# insert some testing data into temperatures table
INSERT INTO temperatures VALUES (20011229000000,30,32);
INSERT INTO temperatures VALUES (20011229040000,30,10);
INSERT INTO temperatures VALUES (20011229080000,30,15);
INSERT INTO temperatures VALUES (20011229120000,30,20);
INSERT INTO temperatures VALUES (20011229160000,30,25);
INSERT INTO temperatures VALUES (20011229200000,30,27);
INSERT INTO temperatures VALUES (20011230000000,30,30);