Creating and Testing the Project Trailblazer Database
| The Project Trailblazer database will store field data from various Silverjack resort locations, for access by field devices and Web services. This database consists of tables that store location, temperature, guest, access, and image information. CGI bash scripts will populate and perform queries on these tables. The trailblazerdbbuild.sql script, shown in Listing 12.1, creates the Project Trailblazer database, grants access to it, creates the database tables, and inserts some testing data. Listing 12.1 The trailblazerdbbuild.sql Script# 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); You can use the following steps to create and test the Project Trailblazer database on tbdev1:
TIP You can use the command mysqldump to obtain a text file that contains a database's definition and data. You can use this file to back up or re-create a database. The trailblazerdbbuild.sql script is an edited version of a mysqldump output file. |
trailblazerdbbuild.sql| Top |
Embedded LinuxR. Hardware, Software, and Interfacing
ISBN: N/A
EAN: N/A
EAN: N/A
Year: 2001
Pages: 103
Pages: 103