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 refresh_all.sh accomplishes the same thing, 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 'cshell' exist for the cshell project. A database name 'cshell' exist for the cshell project. This was setup by the mysql admin, like so:

  > mysql -h irtfweb -u root -p
  mysql>  CREATE DATABASE cshell;
  mysql>  GRANT ALL ON cshell.* TO 'cshell'@'%' IDENTIFIED BY 'thepassword';
  mysql>  GRANT ALL ON cshell.* TO 'cshell'@'localhost' IDENTIFIED BY 'thepassword';
  mysql>  quit

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

3. Creating mysql Tables

   > mysql -h irtfweb -u cshell -p
	> use cshell

   # 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. cshell temperature tables

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

   TcA _  - Temperature Controller A:
      tca_t1     = TcA temperature 1, kelvin
      tca_t2     = TcA temperature 2, kelvin
      tca_setpt  = TcA set point, kelvin 
      tca_heat   = TcA heater output, 0-100, percent.
      tca_p      = TcA P value
      tca_i      = TcA I value

      tca_i      = TcA D value
   TcB _  - Temperature Controller B:
      tcb_t1     = TcB temperature 1, kelvin
      tcb_t2     = TcB temperature 2, kelvin
      tcb_setpt  = TcB set point, kelvin 
      tcb_heat   = TcB heater output, 0-100, percent.
      tcb_p      = TcB P value
      tcb_i      = TcB I value
      tcb_i      = TcB D value



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

INSERT INTO Sensors VALUES ('tca_t1',    'TcA temperature 1, kelvin');
INSERT INTO Sensors VALUES ('tca_t2',    'TcA temperature 2, kelvin');
INSERT INTO Sensors VALUES ('tca_setpt', 'TcA set point, kelvin');
INSERT INTO Sensors VALUES ('tca_heat',  'TcA heater output, 0-100, percent');
INSERT INTO Sensors VALUES ('tca_p',     'TcA P value');
INSERT INTO Sensors VALUES ('tca_i',     'TcA I value');
INSERT INTO Sensors VALUES ('tca_d',     'TcA D value');

INSERT INTO Sensors VALUES ('tcb_t1',    'TcB temperature 1, kelvin');
INSERT INTO Sensors VALUES ('tcb_t2',    'TcB temperature 2, kelvin');
INSERT INTO Sensors VALUES ('tcb_setpt', 'TcB set point, kelvin');
INSERT INTO Sensors VALUES ('tcb_heat',  'TcB heater output, 0-100, percent');
INSERT INTO Sensors VALUES ('tcb_p',     'TcB P value');
INSERT INTO Sensors VALUES ('tcb_i',     'TcB I value');
INSERT INTO Sensors VALUES ('tcb_d',     'TcB D value');




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_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 cshell -p
   SHOW DATABASES;
   USE cshell;
   SHOW TABLES;
   SELECT Timestamp, FROM_UNIXTIME(Timestamp) AS Date,
   GROUP_CONCAT(SensorData ORDER BY SensorID SEPARATOR ' ') AS SensorData
   FROM Data
      WHERE (Timestamp > UNIX_TIMESTAMP('2014-11-21 11:00:00'))
      AND (Timestamp < UNIX_TIMESTAMP('2014-11-21 12:00:00'))
      AND SensorID LIKE 'tca_%'
      GROUP BY Timestamp DESC;


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

Below is an example: example.html