Allsky Database

A module can store data in the Allsky database, primarily this is used in the creation of charts but could also be used for other things

The database is configured at installation and in the main Allsky setting. Currently there are two available database types

SQLite3 - SQLite3 is a lightweight, serverless, self-contained SQL database engine that stores the entire database in a single file and requires no separate server process
MySQL - MySQL is a popular open-source relational database management system (RDBMS) that uses SQL, supports multi-user access, and runs as a dedicated client–server database server. We actually use MariaDb but most people will probably have heard of MySQL !

The preference is always to use SQLite3, MySQL is provided for advanced users that may already have a MySQL instance they wish to use. For the rest of this documentation we will assume you are using SQLite3.

Tip

Should you wish to change the database you can either use the main settings or the setup_database.sh script in ~/allsky/scripts/utilities

Database Structure¶

The database conrtains a series of tables, typically a table is named after the module that writes data to it, it is possible for modules two write data to other tables if required.

Each table consists of two predefined columns

id - This is the primary key for the row
timestamp - This is the time the row was created

Why have both? For modules that run in the capture pipelines, day and night, typically the primary key will be the time the last image was captured. For other modules its just a unique id. This mechanism allows other modules that run in the capture pipelines to save their data using the same primary key as the last image captured, this is useful for aligning data on charts.

The Meta Data Database Section¶

The database section controls how data generated byt he module is saved to the database.

Name	Description
enabled	(Optional) “true” if this module will write data to the database. Normally you would set this to true, typically on setting it to false when developing or testing
table	(Optional) If enable then this field IS required and contains the name of the table to write data to. The convention is the module name.
pk	(Optional) If enable then this field IS required and contains the name of the primary key for the database. This should always be 'id'
pk_source	(Optional) If enable then this field IS required and contains the variable that the primary key (id) will be obtained from, If omitted the last ime timestamp or current timestamp will be used
pk_type	(Optional) If enable then this field IS required and contains the primary key. Set this to ‘int’
Include_all	(Optional) If enable then this field IS required and determines if all extra data values are written to the database. If “false” then you can specify which data is written against the extra data fields definitions
time_of_day_save	(Optional) Determines how data is saved for each pipeline. You may not want to save data during the daytime for example

Some examples¶

NOTE: In all of these examples only the extradata prtion of the meta_data is shown

Example 1 - Full save¶

  meta_data = { 
    "extradata": {    
      "database": {
        "enabled": "True",
        "table": "allsky_example3",
        "include_all": "true",
        "pk": "id",
        "pk_type": "int",    
        "time_of_day_save": {
          "day": "always",
          "night": "always",
          "nightday": "always",
          "daynight": "always",
          "periodic": "always"
        }      
      }
    }
  }

This example;

Saves all data to the database defined in the values section of the extradata secion of the meta_data !!!
Uses a table called allsky_example3
Uses 'id' as the name of the primary key for the table
Defines the primary key type as an integer
Saves the data for all pipelines

Example 2 - Limiting Pipelines¶

  meta_data = { 
    "extradata": {    
      "database": {
        "enabled": "True",
        "table": "allsky_example3",
        "include_all": "true",
        "pk": "id",
        "pk_type": "int",    
        "time_of_day_save": {
          "day": "never",
          "night": "always",
          "nightday": "never",
          "daynight": "never",
          "periodic": "never"
        }      
      }
    }
  }

This example;

Saves all data to the database defined in the values section of the extradata secion of the meta_data !!!
Uses a table called allsky_example3
Uses 'id' as the name of the primary key for the table
Defines the primary key type as an integer
Only saves data at night

Example 3 - Setting the primary key¶

  meta_data = { 
    "extradata": {    
      "database": {
        "enabled": "True",
        "table": "allsky_example3",
        "include_all": "true",
        "pk": "id",
        "pk_source": "image_timestamp",
        "pk_type": "int",    
        "time_of_day_save": {
          "day": "never",
          "night": "always",
          "nightday": "never",
          "daynight": "never",
          "periodic": "never"
        }      
      }
    }
  }

This example;

Saves all data to the database defined in the values section of the extradata secion of the meta_data !!!
Uses a table called allsky_example3
Uses 'id' as the name of the primary key for the table
Defines the primary key type as an integer
Uses the last captured images time stamp as the primary key, by setting pk_source to the Allsky variable image_timestamp
Only saves data at night

Example 4 - Limiting Variables¶

  meta_data = { 
    "extradata": {    
      "database": {
        "enabled": "True",
        "table": "allsky_example3",
        "include_all": "false",
        "pk": "id",
        "pk_source": "image_timestamp",
        "pk_type": "int",    
        "time_of_day_save": {
          "day": "never",
          "night": "always",
          "nightday": "never",
          "daynight": "never",
          "periodic": "never"
        }      
      },
      "values": {
        "AS_EXAMPLE3_TEMP": {
          "name": "${EXAMPLE3_TEMP}",
          "format": "{dp=2|deg}",
          "sample": "",                   
          "group": "User",
          "description": "Example 3 Temp from Openweather API",
          "type": "number",
          "dbtype": "float",     
          "database": {
            "include" : "true"
          }          
        },
        "AS_EXAMPLE3_HUMIDITY": {
          "name": "${EXAMPLE3_HUMIDITY}",
          "format": "{dp=0|per}",
          "sample": "",                   
          "group": "User",
          "description": "Example 3 Humidity from Openweather API",
          "type": "number"
        }        
      }     
    }
  }

This example;

Only saves the AS_EXAMPLE3_TEMP variable by setting the include value in the fields database values to true
Uses a table called allsky_example3
Uses 'id' as the name of the primary key for the table
Defines the primary key type as an integer
Uses the last captured images time stamp as the primary key, by setting pk_source to the Allsky variable image_timestamp
Only saves data at night

Writing across tables¶

It is possible for a module to write its data to another table. This can be handy for adding columns to existing tables. For example you may have several modules that write the data to the same table

Warning

It is not advised to add your own data to core allsky tables. This data could be lost during an upgrade

  meta_data = { 
    "extradata": {    
      "database": {
        "enabled": "True",
        "table": "allsky_example3",
        "include_all": "false",
        "pk": "id",
        "pk_source": "image_timestamp",
        "pk_type": "int",    
        "time_of_day_save": {
          "day": "never",
          "night": "always",
          "nightday": "never",
          "daynight": "never",
          "periodic": "never"
        }      
      },
      "values": {
        "AS_EXAMPLE3_TEMP": {
          "name": "${EXAMPLE3_TEMP}",
          "format": "{dp=2|deg}",
          "sample": "",                   
          "group": "User",
          "description": "Example 3 Temp from Openweather API",
          "type": "number",
          "dbtype": "float",     
          "database": {
            "include" : "true",
            "table": "allsky_whatever"
          }          
        },
        "AS_EXAMPLE3_HUMIDITY": {
          "name": "${EXAMPLE3_HUMIDITY}",
          "format": "{dp=0|per}",
          "sample": "",                   
          "group": "User",
          "description": "Example 3 Humidity from Openweather API",
          "type": "number"
        }        
      }     
    }
  }

This example;

Only saves the AS_EXAMPLE3_TEMP variable by setting the include value in the fields database values to true
Uses a table called allsky_example3 BUT writes the temperature to the allsky_whatever table.
Uses 'id' as the name of the primary key for the table
Defines the primary key type as an integer
Uses the last captured images time stamp as the primary key, by setting pk_source to the Allsky variable image_timestamp
Only saves data at night

Database Purging¶

To prevent the database growing over time a mechanism is provided for purging data from the database. There are several ways to specify the length of time data shoud be kept

The default is the Days To Keep setting from the main Allsky settings
A module can create a small json file in its repository to override this. The file will determine how long data is kept for

Module specific Purging¶

To purge data for a module create the db directory in the module repository and add a file called db_data.json

Basic purging¶

{
  "allsky_example3": {
    "purge_days": {
      "source_type": "settings",
      "source_name": "daystokeep"
    }
  }
}

In this example the purging is based off of the Days To Keep Allsky setting

Defined purging¶

{
  "allsky_example3": {
    "purge_days": 14
  }
}

In this example the purging will be set to 14 days.