Database

A database is the main method of keeping track of requests to the telescope, noting its capabilities and logging what it has done. The data base is MariaDB version 10.3. This is a powerful opensource MySQL database with many users.

Here is an E/R/ diagram

There are two databases in use: observatory and weather.

Observatory Table Descriptions

Note: the InnoDB table type is used so that deletes or updates will be restricted or cascade when foreign keys are used (am I speaking English?).

There are also a lot of orphan tables that do not have the correct key associations, the above E/R diagram to see them.

Filter table - list the names, properties, filter wheel locations of the available filters.

+-------------------+----------+-----+-----+------+--+
| FilterID          | int(11)  | NO  | PRI | 0    |  |
| Name              | char(80) | NO  |     |      |  |
| Notes             | char(80) | YES |     | NULL |  |
| CentralWavelength | float    | YES |     | NULL |  |
| Bandwidth         | float    | YES |     | NULL |  |
| Thickness         | float    | YES |     | NULL |  |
| FocusShift        | float    | YES |     | NULL |  |
+-------------------+----------+-----+-----+------+--+

FilterID - filter position number in the range 0-16
Name - popular name of the filter
Notes - part number
CentralWavelength - nanoMeters
Bandwidth - nanoMeters
Thickness - millimeters
FocusShift - millimeters

Fits table - is a listing of the images taken.

+-----------+----------+-----+-----+---------------------+----------------+
| FitsID    | int(11)  | NO  | PRI | NULL                | auto_increment |
| PictureID | int(11)  | YES | MUL | NULL                |                |
| Filestem  | char(20) | NO  | UNI |                     |                |
| Datetime  | datetime | NO  |     | 0000-00-00 00:00:00 |                |
| ImageType | char(20) | YES |     | OBJECT              |                |
+-----------+----------+-----+-----+---------------------+----------------+

FitsID - autoincrement integer primary
PictureID - foreign key
Filestem- is the stem of the filename e.g.a060923003
Datetime - the date/time the picture was made
ImageType - string value of IMAGETYP keyword e.g. default:'OBJECT', 'BIAS'

Focus table - lists best focus measurements. It is generated by the focusanl program. Occasionally it is analyzed and the data are placed in the It is generated by the focus program.

+-------------+-----------+------+-----+---------------------+-------------------------------+
| TimeStamp   | timestamp | NO   | PRI | current_timestamp() | on update current_timestamp() |
| Millimeters | float     | NO   |     | NULL                |                               |
| FilterID    | int(11)   | YES  | MUL | NULL                |                               |
| Temp0       | float     | YES  |     | NULL                |                               |
| Temp1       | float     | YES  |     | NULL                |                               |
| Temp2       | float     | YES  |     | NULL                |                               |
| Temp3       | float     | YES  |     | NULL                |                               |
| Temp4       | float     | YES  |     | NULL                |                               |
| Temp5       | float     | YES  |     | NULL                |                               |
| Temp6       | float     | YES  |     | NULL                |                               |
| Temp7       | float     | YES  |     | NULL                |                               |
+-------------+-----------+------+-----+---------------------+-------------------------------+

TimeStamp - when the data was taken
Millimeters - best focus position
FilterID - ID of the filter used
Temp? - data from the temps sensors

Log table - lists what requests the telescope has observed and whether they succeeded or failed.

+-----------+------------+-----+-----+---------------------+----------------+
| LogID     | int(11)    | NO  | PRI | NULL                | auto_increment |
| RequestID | int(11)    | NO  | MUL | 0                   |                |
| Start     | datetime   | NO  |     | 0000-00-00 00:00:00 |                |
| Stop      | datetime   | YES |     | NULL                |                |
| Success   | tinyint(1) | YES |     | NULL                |                |
| Message   | char(80)   | YES |     | NULL                |                |
+-----------+------------+-----+-----+---------------------+----------------+

LogID - autoincrement integer primary key
Start - DateTime when the observation was started
Stop - DateTime when the observation was finished
RequestID - the key of the request that created this entry
Success - a boolean value that tell whether the request succeeded
Message - why the request did not succeed

Object table - lists RA/Dec of non-orbiting celestial objects.

+----------------+------------+-----+-----+------+----------------+
| ObjectID       | int(11)    | NO  | PRI | NULL | auto_increment |
| ProjectID      | int(11)    | NO  |     | 0    |                |
| RightAscension | double     | YES |     | NULL |                |
| Declination    | double     | YES |     | NULL |                |
| Equinox        | float      | YES |     | NULL |                |
| Name           | char(80)   | YES |     | anon |                |
| ObserverID     | int(11)    | YES |     | NULL |                |
| IsPlanet       | tinyint(1) | YES |     | 0    |                |
| Epoch          | datetime   | YES |     | NULL |                |
+----------------+------------+-----+-----+------+----------------+

ObjectID - autoincrement integer primary key
RightAscension - in decimal degrees
Declination - in decimal degrees
Equinox - in years
Name - your name for the object (default:'anon')
ObserverID - the ID of the User who inserted it
ProjectID - the ID of the Project who inserted it
IsPlanet - is0 if fixed, 1 if minor planet ,2 if comet (default:0)
Epoch - for comets, the time when coordinate was calculated

Observer table - contains information about the person submitting the request.

+----------------+-------------+-----+-----+------------------------------------------+----------------+
| ObserverID     | int(11)     | NO  | PRI | NULL                                     | auto_increment |
| OrganizationID | int(11)     | NO  |     | 0                                        |                |
| Name           | char(80)    | YES |     | NULL                                     |                |
| Email          | char(80)    | NO  | UNI |                                          |                |
| MaxPriority    | smallint(6) | YES |     | 0                                        |                |
| Password       | char(50)    | YES |     | cf2d1eac7ea105b6b4d6fcc562262178a127d189 |                |
| IsRoot         | tinyint(1)  | YES |     | 0                                        |                |
+----------------+-------------+-----+-----+------------------------------------------+----------------+

ObserverID - autoincrement primary key
OrganizationID - the key of the Organization the observer is associated with
Name - what do you think
Email <-- this is used for login
MaxPriority - the highest priority request that this observer can create (default:0)
Password - encrypted password
IsRoot - true if the observer can add/modify other observers requests (default: False)

Organization table - contains information about the institutions submitting the request.

+----------------+---------------+-----+-----+------+--+
| OrganizationID | int(11)       | NO  | PRI | 0    |  |
| Name           | char(40)      | YES |     |      |  |
| Fraction       | decimal(11,2) | NO  |     | 0.00 |  |
+----------------+---------------+-----+-----+------+--+

OrganizationID - the autoincrement primary key
Name
Fraction - the percentage of the observing time (default:0)

Picture table - lists the particulars of the CCD images to be taken.

+--------------+-------------+-----+-----+------+----------------+
| PictureID    | int(11)     | NO  | PRI | NULL | auto_increment |
| FilterID     | int(11)     | YES | MUL | NULL |                |
| TargetID     | int(11)     | NO  | MUL | 0    |                |
| ExposureTime | float       | NO  |     | 0    |                |
| Binning      | tinyint(4)  | YES |     | 3    |                |
| Amplifier    | char(5)     | YES |     | 2    |                |
| Guide        | tinyint(1)  | YES |     | 0    |                |
| Count        | smallint(6) | YES |     | 1    |                |
+--------------+-------------+-----+-----+------+----------------+

PictureID - autoincrement integer primary key
FilterID - the bolt number
TargetID - foreign key
ExposureTime - time in seconds
Binning - pixel adding number (default:3)
Amplifier - the clocking amplifier, '1', '2', '3', '4'(default:'2')
Guide - boolean whether to guide (default:0)
Count - number of repeat pictures (default:1)

Project table - contains information about the project submitting the request.

+-------------+--------------+----+-----+------+----------------+
| ProjectID   | int(11)      | NO | PRI | NULL | auto_increment |
| Name        | varchar(30)  | NO |     |      |                |
| ObserverID  | int(11)      | YES| MUL | NULL |                |
| Description | varchar(300) | NO |     |      |                |
| Abstract    | varchar(300) | NO |     |      |                |
| AllocTime   | int(11)      | NO |     | 0    |                |
+-------------+--------------+----+-----+------+----------------+

ProjectID - the autoincrement primary key
Name - name associated with the project
ObserverID - leader of the project
Description - abstract describing the project
Abstract - abstract describing the project
AllocTime - I don't know

ProjectTeam table - contains information about the project?

+-----------+---------+------+-----+---------+----------------+
| TeamID    | int(11) | NO   | PRI | NULL    | auto_increment |
| ProjectID | int(11) | NO   | MUL | NULL    |                |
| Member    | int(11) | NO   | MUL | NULL    |                |
+-----------+---------+------+-----+---------+----------------+

TeamID - the autoincrement primary key
ProjectID - foreign key of the project
member - foreign key of other observers

ProjectQuota table - contains information about something

+----------------+---------------+-----+-----+------+----------------+
| ProjectQuotaID | int(11)       | NO  | PRI | NULL | auto_increment |
| ProjectID      | int(11)       | YES |     | NULL |                |
| ObserverID     | int(11)       | YES |     | NULL |                |
| Fraction       | decimal(11,5) | YES |     | NULL |                |
| EditPerm       | int(1)        | YES |     | NULL |                |
+----------------+---------------+-----+-----+------+----------------+

ProjectQuotaID - the autoincrement primary key
ProjectID - key of the project
ObserverID - key of the observer
Fraction - who knows
EditPerm - who knows

Request table - is the main table that holds information about the requested observation.

+--------------+-------------+-----+-----+---------------------+-------------------------------+
| RequestID    | int(11)     | NO  | PRI | NULL                | auto_increment                |
| ProjectID    | int(11)     | YES |     | NULL                |                               |
| Name         | char(80)    | YES |     | no label            |                               |
| ObsTime      | int(11)     | YES |     | NULL                |                               |
| Count        | smallint(6) | YES |     | 1                   |                               |
| NumberDone   | int(11)     | YES |     | 0                   |                               |
| Timestamp    | timestamp   | NO  |     | current_timestamp() | on update current_timestamp() |
| Priority     | smallint(6) | YES |     | 0                   |                               |
| Twilight     | float       | YES |     | -12                 |                               |
| Airmass      | char(20)    | YES |     | 3.0                 |                               |
| SunStart     | float       | YES |     | NULL                |                               |
| UTStart      | char(10)    | YES |     | NULL                |                               |
| MoonDistance | float       | YES |     | 30                  |                               |
| MoonPhase    | float       | YES |     | 1                   |                               |
| Earliest     | datetime    | YES |     | NULL                |                               |
| Latest       | datetime    | YES |     | NULL                |                               |
| Interval     | float       | YES |     | NULL                |                               |
| Active       | tinyint(4)  | YES |     | 1                   |                               |
+--------------+-------------+-----+-----+---------------------+-------------------------------+

RequestID - autoincrement integer primary key
ProjectID - key of the project that this request is associated with
Name - user useful label about the request
ObsTime - time (in seconds) needed for observation -optional
Count - number of repeat requests (default:1)
NumberDone - number of times request successfully observed (default:0)
Timestamp - when the request was received (automatically entered)
Priority - how important bigger is better (default: 0)
Twilight - maximum altitude of sun for observation (default:-12.0)
Airmass - maximum airmass (range: see notes) for observation (default:3.0)
SunStart - used to schedule tasks at specific sun altitude in degrees (default:NULL)
UTStart - Universal time (range: see notes) to start observation (default:NULL)
MoonDistance - moon avoidance angle in degrees (default: 30 degrees)
MoonPhase - maximum moon phase angle 1.0-full (default: 1.0)
Earliest - earliest DateTime the observation can start (default: NULL)
Latest - latest DateTime the observation can start (default: NULL)
Interval*** - interval in hours to repeat observation (default: NULL)
Active - whether request should be used (default: NULL)

Notes: If the count field is set to NULL, it has the action of a never ending request going once per night. A Count of 0 will cause the request to be ignored (the request is expired).. A Cascade delete has been placed on this table to the target file so if the request is deleted, the associated targets are also deleted.

The SunStart altitude is not corrected for the elevation of the observatory.

UTStart may contain a dash to allow a span of Universal time start time. For example: 12:12 - 14:30.

Airmass may contain a dash to allow a span of allowable airmasses.

Schedule table - is generated by the telescope scheduler program insgen. It lists the active requests, i.e. those for which the requested number of observations have not been made yet and when they will be observed.

+------------+----------+----+-----+---------------------+--+
| ScheduleID | int(11)  | NO | PRI | 0                   |  |
| RequestID  | int(11)  | NO | PRI | 0                   |  |
| Start      | datetime | NO | UNI | 0000-00-00 00:00:00 |  |
| BlockTime  | int(11)  | NO |     | 0                   |  |
+------------+----------+----+-----+---------------------+--+

ScheduleID - integer primary key
RequestID - the key of the request that has created this entry
Start - the date/time when the request is scheduled
BlockTime - the estimated time the observation will take (seconds)

Skipped table - is generated by the telescope scheduler program insgen. It lists the active requests that cannot be scheduled. The reasons may be due to errors in the request file, telescope limitations, the fact the celestial object is not up at the right time, or there is no room.

+-----------+------------+-----+-----+------+--+
| SkippedID | int(11)    | NO  | PRI | 0    |  |
| RequestID | int(11)    | NO  | PRI | 0    |  |
| Rejected  | tinyint(1) | YES |     | 0    |  |
| Reason    | char(80)   | YES |     | NULL |  |
+-----------+------------+-----+-----+------+--+

SkippedID - primary key
RequestID - the key of the request that has created this entry
Rejected - 1 if insgen has found fatal error in request (default:0)
Reason - text describing the reason the observation is skipped

Target table - lists the observations associated with the request.

+-----------+-------------+-----+-----+-------+----------------+
| TargetID  | int(11)     | NO  | PRI | NULL  | auto_increment |
| RequestID | int(11)     | YES | MUL | NULL  |                |
| ObjectID  | int(11)     | YES | MUL | NULL  |                |
| Procedure | char(80)    | YES |     | photo |                |
| Count     | smallint(6) | YES |     | 1     |                |
+-----------+-------------+-----+-----+-------+----------------+

TargetID - autoincrement primary key
Procedure*** - string name of the observing procedure default:'photo'
RequestID - the key of the request that has created this entry
ObjectID - the key of the fixed celestial object if any
Count - number of repeat target (default:1)

The telescope will point to the fixed object if that is specified, or calculate the position of the orbiting object if that is specified. A Cascade delete has been placed on this table to the Picture file so if the Target is deleted, the associated pictures are also deleted.

Window table - contains information about the request used for graphical display and debugging. It is generated by insgen.

+------------+----------+-----+-----+------+--+
| WindowID   | int(11)  | NO  | PRI | 0    |  |
| ScheduleID | int(11)  | YES | MUL | NULL |  |
| RequestID  | int(11)  | NO  |     | 0    |  |
| UpStart    | datetime | YES |     | NULL |  |
| UpEnd      | datetime | YES |     | NULL |  |
| NightStart | datetime | YES |     | NULL |  |
| NightEnd   | datetime | YES |     | NULL |  |
| BestStart  | datetime | YES |     | NULL |  |
| BestEnd    | datetime | YES |     | NULL |  |
+------------+----------+-----+-----+------+--+

WindowID - primary key
ScheduleID - foreign key, 0- night limits, negative: skipped
RequestID - the key of the request that has created this entry
NightStart - when darkness begins
NightEnd - when darkness ends
UpStart - when the request comes above airmass or telescope limits
UpEnd - when the request goes below airmass or telescope limits
BestStart - the ideal time to start observation
BestEnd - the ideal time to stop observations

*** Caution: the words Procedure and Interval are MySQL reserved words and have to be properly cited using backquotes or the table name. For example:

SELECT `Interval` FROM Request LIMIT 5;
or SELECT Request.Interval FROM Request LIMIT 5;
SELECT `Procedure` FROM Target LIMIT 5;
or SELECT Target.Procedure FROM Target LIMIT 5;

Weather Description

Mets table contains the weather data. It is filled in by the weatherd daemon.

+--------------------+-------------+-----+-----+---------------------+-------------------------------+
| Timestamp          | timestamp   | NO  | PRI | current_timestamp() | on update current_timestamp() |
| OAT                | float       | YES |     | NULL                |                               |
| WindSpeed          | smallint(6) | YES |     | NULL                |                               |
| windDirection      | smallint(6) | YES |     | NULL                |                               |
| Humidity           | tinyint(4)  | YES |     | NULL                |                               |
| BarometricPressure | float       | YES |     | NULL                |                               |
| Rain               | tinyint(1)  | YES |     | NULL                |                               |
| Cloud              | float       | YES |     | NULL                |                               |
| CCDTemp            | float       | YES |     | NULL                |                               |
| Slit               | tinyint(4)  | YES |     | NULL                |                               |
| Temps              | char(60)    | YES |     | NULL                |                               |
+--------------------+-------------+-----+-----+---------------------+-------------------------------+

CREATING THE DATABASE

There are scripts in ~treffers/Documents/Database that are for creating the database:

renew - script that deletes previous database and installs the new one. It executes Script.sql
populate - installs the Filter list and the users

RCT Index