1. About the temperature graph

The temperature graphs are generated using the refresh.sh script.
This script calls other .sh files to query the databases and generates
the .txt and .png files display on the web page.
(NOTE: For testing we use the *_sim table, for the actual temperature
       data remove the '_sim' name from the table in refresh.sh).

refresh.sh should be call from a cron job every 10 min to keep the
data current.

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 is for each projects is standard, see the mysql administrator.

3. spex temperature tables

Using my sql for archive spex's temperatures. We has 3 controller:
  tc330a - bigdog's   temperature controller.
  tc330b - guidedog's temperature controller.
  tc208  - 8 channels of temperature data.

Establish a table for each controller

tc330a - bigdog   temperature controller data.
tc330b - guidedog temperature controller data.
tc208  - spex cryostat temperature monitor.

4. Creating mysql Tables

   # table for tc330a, tc330b, tc330a_sim, tc330b_sim:

   CREATE TABLE tc330a         (
      tv_sec  BIGINT,       # timestamp in unix seconds, 64-bit INT
      cchn    CHAR(1),      # sensor on the control channel: A or B
      schn    CHAR(1),      # sensor on the sample  channel: A or B
      cdat    FLOAT,        # control channel temperature in kelvin.
      sdat    FLOAT,        # sample  channel temperature in kelvin.
      setpt   FLOAT,        # set point temperature in kelvin.
      heater  FLOAT,        # heater output, percent
      range   SMALLINT,     # heater range: 0 - 3 for off, low, med, high., short INT
      tune    SMALLINT,     # auto tune status,, 0=Manual, 1=P, 2=PI, 3=PID
      p       SMALLINT,     # Gain,  or P in PID, short int
      i       SMALLINT,     # Reset, or I in PID, short int
      d       SMALLINT,     # Rate,  or D in PID, short int

      PRIMARY KEY ( tv_sec )
   );

   # table for tc208:
   Repeat the above, but name the table tc330b

   CREATE TABLE tc208  (
      tv_sec  BIGINT,       # timestamp in unix seconds, 64-bit INT
      ch1     FLOAT,        # temperature in kelvin for ch1
      ch2     FLOAT,        # temperature in kelvin for ch2
      ch3     FLOAT,        # temperature in kelvin for ch3
      ch4     FLOAT,        # temperature in kelvin for ch4
      ch5     FLOAT,        # temperature in kelvin for ch5
      ch6     FLOAT,        # temperature in kelvin for ch6
      ch7     FLOAT,        # temperature in kelvin for ch7
      ch8     FLOAT,        # temperature in kelvin for ch8

      PRIMARY KEY ( tv_sec )
   );

   The following table are also created for testing/simulation:

      tc330a_sim
      tc330b_sim
      tc208_sim

5. Adding Data, Quering Data

Shell (sh) scripts are used to add records and query data from the mysql data
base. The following scripts illustrate how to perform these functions.
(NOTE: replace password which 'XXXX' , and appended '.txt' to file so
the can be view in your brower.)

mysql_tc208_insert.sh.txt

mysql_tc208_query.sh.txt


mysql_tc330_insert.sh.txt

mysql_tc330_query.sh.txt


6. Doing your own query

The following command will query the tc208_sim table for 1 hour of data on 5/6/2009::
You can also use the '-s' option on the mysql to NOT display the data in its default
tabular format:

   mysql -h irtfweb -u spex -p
   SHOW DATABASES;
   USE spex;
   SHOW TABLES;
   SELECT  tv_sec, FROM_UNIXTIME(tv_sec) AS date, ch1, ch2, ch3, ch4, ch5, ch6, ch7, ch8
      FROM tc208_sim
      WHERE (tv_sec > UNIX_TIMESTAMP('2009-05-06 00:00:00'))  AND  (tv_sec < UNIX_TIMESTAMP('2009-05-06 01:00:00'))
      ORDER BY tv_sec DESC;

To query all the data, you can leave out the WHERE option:

   SELECT  tv_sec, FROM_UNIXTIME(tv_sec) AS date, ch1, ch2, ch3, ch4, ch5, ch6, ch7, ch8
      FROM tc208_sim
      ORDER BY tv_sec DESC;

Below is an example: example.html