About the temperature graphs

1. About the temperature graph

The temperature graphs are generated using the index.php file. The PHP file calls a .sh file to query the database and generates the .txt and another .sh file to graph the .png files display on the web page.

The index.php files checks the file time stamps, and regenerate the page periodically. The are refresh_all.sh accomplishes the same think, if you what to manually refresh the graphs. To start, it a good ideal to run refresh_all.sh, since it creates all the data files and set the proper file permissions.

2. MYSQL server notes

The IRTF mysql database server is 'irtfweb'. The user name 'spex' exist for the spex project. A database name 'spex' exist for the spex project. This was setup by the mysql admin, like so:

  mysql>  CREATE DATABASE spex;
  mysql>  GRANT ALL ON spex.* TO 'spex'@'%' IDENTIFIED BY 'thepassword';
  mysql>  GRANT ALL ON spex.* TO 'spex'@'localhost' IDENTIFIED BY 'thepassword';

The password for each projects is standard, see the mysql administrator.

3. Creating mysql Tables

   # table for Data:
   # NOTE: Also create a Data_sim table for putting in test data.

   CREATE TABLE Data(
      SensorID        varchar(10),    # sensor Identification
      Timestamp       int(10),        # timestamp in unix seconds, 64-bit INT
      SensorData      varchar(12)     # sensor data collected,
   );

   # table for Sensors:

   CREATE TABLE Sensors (
      SensorID    varchar(10),    # sensor Identification
      Notes       varchar(250),   # comments, descriptions, etc.
      PRIMARY KEY ( SensorID )
   );

4. spex temperature tables

Spex saves it data using an Timestamp, SensorID, and Data record. Similar to IQUP databases. For spex's temperature we established the following SensorID:

   bd_  - bigdog's   temperature controller:
      bd_cdat   = bigdog control temperature, kelvin
      bd_sdat   = bigdog sample  temperature, kelvin
      bd_setpt  = bigdog control set point, kelvin
      bd_heater = bigdog heater output, 0-100, percent.
      bd_range  = bigdog heater range 0,1,2,3 for off, low, med, high.
      bd_p      = bigdog P value
      bd_i      = bigdog I value
      bd_d      = bigdog D value
   gd_   - guidedog's temperature controller:
      gd_cdat   = guidedog control temperature, kelvin
      gd_sdat   = guidedog sample  temperature, kelvin
      gd_setpt  = guidedog control set point, kelvin
      gd_heater = guidedog heater output, 0-100, percent.
      gd_range  = guidedog heater range 0,1,2,3 for off, low, med, high.
      gd_p      = guidedog P value
      gd_i      = guidedog I value
      gd_d      = guidedog D value
   tc218 - 8 channels of temperature data:
     spex_ch1 = spex dewar temperature channel 1, kelvin
     spex_ch2 = spex dewar temperature channel 2, kelvin
     spex_ch3 = spex dewar temperature channel 3, kelvin
     spex_ch4 = spex dewar temperature channel 4, kelvin
     spex_ch5 = spex dewar temperature channel 5, kelvin
     spex_ch6 = spex dewar temperature channel 6, kelvin
     spex_ch7 = spex dewar temperature channel 7, kelvin
     spex_ch8 = spex dewar temperature channel 8, kelvin



There how to add the records to the Sensor's ID.

INSERT INTO Sensors VALUES ('bd_cdat',   'bigdog control temperature, kelvin');
INSERT INTO Sensors VALUES ('bd_sdat',   'bigdog sample  temperature, kelvin');
INSERT INTO Sensors VALUES ('bd_setpt',  'bigdog control set point, kelvin');
INSERT INTO Sensors VALUES ('bd_heater', 'bigdog heater output, 0-100, percent.');
INSERT INTO Sensors VALUES ('bd_range',  'bigdog heater range 0,1,2,3 for off, low, med, high.');
INSERT INTO Sensors VALUES ('bd_p',      'bigdog P value');
INSERT INTO Sensors VALUES ('bd_i',      'bigdog I value');
INSERT INTO Sensors VALUES ('bd_d',      'bigdog D value');

INSERT INTO Sensors VALUES ('gd_cdat',   'guidedog control temperature, kelvin');
INSERT INTO Sensors VALUES ('gd_sdat',   'guidedog sample  temperature, kelvin');
INSERT INTO Sensors VALUES ('gd_setpt',  'guidedog control set point, kelvin');
INSERT INTO Sensors VALUES ('gd_heater', 'guidedog heater output, 0-100, percent.');
INSERT INTO Sensors VALUES ('gd_range',  'guidedog heater range 0,1,2,3 for off, low, med, high.');
INSERT INTO Sensors VALUES ('gd_p',      'guidedog P value');
INSERT INTO Sensors VALUES ('gd_i',      'guidedog I value');
INSERT INTO Sensors VALUES ('gd_d',      'guidedog D value');

INSERT INTO Sensors VALUES ('spex_ch1', 'spex dewar temperature channel 1, kelvin');
INSERT INTO Sensors VALUES ('spex_ch2', 'spex dewar temperature channel 2, kelvin');
INSERT INTO Sensors VALUES ('spex_ch3', 'spex dewar temperature channel 3, kelvin');
INSERT INTO Sensors VALUES ('spex_ch4', 'spex dewar temperature channel 4, kelvin');
INSERT INTO Sensors VALUES ('spex_ch5', 'spex dewar temperature channel 5, kelvin');
INSERT INTO Sensors VALUES ('spex_ch6', 'spex dewar temperature channel 6, kelvin');
INSERT INTO Sensors VALUES ('spex_ch7', 'spex dewar temperature channel 7, kelvin');
INSERT INTO Sensors VALUES ('spex_ch8', 'spex dewar temperature channel 8, kelvin');



5. Adding Data, Quering Data

Shell (sh) scripts are used to add records and query data from the mysql database. The following scripts illustrate how to perform these functions.
(NOTE: replace password with 'XXXXX' , and append '.txt' to file so that it can be viewed in your browser.)



mysql_query.sh.txt

mysql_tc218_insert.sh.txt

mysql_tc335_insert.sh.txt




6. Doing your own query

The following command will query the Data table for 1 hour of data on 11/6/2009::
You can also use the '-s' option when you sign on to mysql to NOT display the data in its default
tabular format:
   mysql -h irtfweb -u spex -p
   SHOW DATABASES;
   USE spex;
   SHOW TABLES;
   SELECT Timestamp, FROM_UNIXTIME(Timestamp) AS Date,
   GROUP_CONCAT(SensorData ORDER BY SensorID SEPARATOR ' ') AS SensorData
   FROM Data
      WHERE (Timestamp > UNIX_TIMESTAMP('2009-11-06 00:00:00'))
      AND (Timestamp < UNIX_TIMESTAMP('2009-11-06 01:00:00'))
      AND SensorID LIKE 'spex%'
      GROUP BY Timestamp DESC;


To query all the data, you can leave out the WHERE Timestamp... options:

Below is an example: example.html