Linux service to collect measurements pubished by TESS Sky Quality Meter
via MQTT. TESS stands for `Cristobal Garcia's Telescope Encoder and Sky
**tessdb** is a software package that collects measurements from one or
several TESS instruments into a SQLite Database.
It is a `Python Twisted Application <https://twistedmatrix.com/trac/>`__
that uses a `custom Twisted library implementing the MQTT
Desktop applicatons may query the database to generate reports and
graphs using the accumulated, historic data.
The Windows version works as a Windows service, but the Linux version
has further functionality like servcie reload and automatic daily
logfile and rotation.
These data sources are available:
- individual samples (real time, 5 min. aprox between samples).
Instrument should send their readings at twice the time resolution
specified in the config file (in seconds).
**Warning**: Time is UTC, not local time.
Linux installation (Debian)
The following components are needed and should be installed first:
- python 2.7.x (tested on Ubunti python 2.7.6)
Installation via PyPi repository
``sudo pip install tessdb``
or from GitHub:
git clone https://github.com/astrorafael/tessdb.git
sudo python setup.py install
- All executables are copied to ``/usr/local/bin``
- The database is located at ``/var/dbase/tess.db`` by default
- The log file is located at ``/var/log/tessdb.log``
- The following required PIP packages will be automatically installed:
Start up Verification
Type ``sudo tessdb -k`` to start the service in foreground with console
Type ``sudo service tessdb start`` to start it as a backgroud service.
Type ``sudo update-rc.d tessdb defaults`` to start it at boot time.
(Tested on Windows XP SP1 & python 2.7.10) \* Have `Python 2.7 for
Windows <https://www.python.org/downloads/windows/>`__ installed. \*
installed. select the latest build fpr the **Pyhton2.7 version** \* Have
the `Microsoft Visual C++ Compiler for Python
installed. Thos is necessary to install ``twisted`` later on. Systems
requirements state for Windows 7+, but it works fine for Windows XP,
The Windows python 2.7 distro comes with the pip utility included.
1. Open a ``CMD.exe`` console, **with Administrator privileges for
Windows 7 and higher**
2. Inside this console type:
``pip install twisted``
Twisted will install (15.5.0 at this moment)
You can test that this installation went fine by opening a python
command line (IDLE or Python CMD) and type:
>>> import twisted
>>> print twisted.__version__
3. Inside this new created folder type:
``pip install tessdb``
- The executables (.bat files) are located in the same folder
- The database is located at ``C:\tessdb\dbase`` by default. It is
strongly recommeded that you leave it there.
- The log file is located at ``C:\tessdb\log\tessdb.log``
- The following required PIP packages will be automatically installed:
Start up and Verification
In the same CMD console, type\ ``.\tessdb.bat``\ to start it in
forground and verify that it works.
Go to the Services Utility and start the TESSDB database service.
There is a small configuration file for this service:
- ``/etc/tessdb/config`` (Linux)
- ``C:\tessdb\config.ini`` (Windows)
This file is self explanatory. In special, the database file name and
location is specified in this file. Some of the properities marked in
this file are makred as *reloadable property*. This means that this
value can be changed and the process reloads its new value on the fly.
Log file is usually placed under ``/var/log/tessdb.log`` in Linux or
under ``C:\tessdb\log`` folder on Windows. Default log level is
``info``. It generates very litte logging at this level. File is rotated
by logrotate **only under Linux**. For Windows, it requires support from
an exteral log rotator software such as
- Service status: ``sudo service emadb status``
- Start Service: ``sudo service emadb start``
- Stop Service: ``sudo service emadb stop``
- Restart Service: ``sudo service emadb restart``. A service restart
kills the process and then starts a new one
The start/stop/pause operations can be performed with the Windows
service GUI tool **If the config.ini file is not located in the usual
locatioon, you must supply its path to the tool as extra arguments**
The server can be put in *pause mode*, in which will be still receiving
incoming MQTT messages but will be internally enquued and not written to
the database. This is usefull to perform delicate operations on the
database without loss of data. Examples:
- Compact the database using the SQLite VACUUM pragma
- Migrating data from tables.
To pause the server, type: ``sudo service emadb pause`` and watch the
log file output wit ``tail -f /var/log/emadb.log``
To resume normal operation type ``sudo service tessdb resume`` and
wautch the same log file. Service pause/resume use signals ``SIGUSR1``
Server pause/resume is made through the standard service GUI tool by
clicking the Pasue and Resume buttons.
During a reload the service is not stopped and re-reads the new values
form the configuration file and apply the changes. In general, all
aspects not related to maintaining the current connection to the MQTT
broker or changing the database schema can be reloaded. The full list is
described in the section B above.
- *Linux:* The ``service emadb reload`` will keep the MQTT connection
- *Windows:* There is no GUI button in the service tool for a reload.
You must execute an auxiliar script ``C:\emadb\winreload.py`` by
double-clicking on it.
In both cases, watch the log file to ensure this is done.
The data model follows the [dimensional modelling]
(https://en.wikipedia.org/wiki/Dimensional\_modeling) approach by Ralph
Kimball. More references can also be found in `Star
The data model
The figure below shows the layout of **tessdb**.
.. figure:: doc/tessdb-full.png
:alt: TESS Database Model
TESS Database Model
- ``date_t`` : preloaded from 2016 to 2026
- ``time_t`` : preloaded, with seconds resolution (configurable)
- ``tess_t`` : registered TESS instruments collecting data
- ``location_t`` : locations where instruments are deployed
- ``tess_units_t`` : an assorted collection of unit labels for reports,
preloaded with current units.
- ``tess_v`` : View with TESS instrument and current location. It is
recommended that reporting applications use this view, instead of the
underlying ``tess_t`` and ``location_t`` tables.
This dimension holds the current list of TESS instruments.
- The real key is an artificial key ``tess_id`` linked to the Fact
- The ``mac_address`` could be the natural key if it weren't for the
calibration constant history tracking.
- The ``name`` attribute could be an alternative key for the same
reason. TESS instruments send readings using this name.
- A TESS instrument name can be changed as long as there is no other
instrument with the same name.
- The ``location_id`` is a reference to the current location assigned
to the instrument.
- Location id -1 denotes the "Unknown" location.
- The ``calibration_k`` holds the current value of the instrument
- A history of calibration constant changes are maintained in the
``tess_t`` table if the instrument is ever recalibrated.
- Columns ``calibrated_since`` and ``calibrated_until``\ hold the
timestamps where the calibration constant is valid.
- Column ``calibrated_state`` is an indicator. Its values are either
**``Current``** or **``Expired``**.
- The current calibration constant has its indicator set to ``Current``
and the expiration date in a far away future (Y2999).
The ``tess_units_t`` table is what Dr. Kimball denotes as a *junk
dimension*. It collects various labels denoting the current measurement
units of samples in the fact table.
- Columns ``valid_since``, ``valid_until`` and ``valid_state`` keep
track of units change in a similar technique as above should the
- ``tess_readings_t`` : Accumulating snapshot fact table containing
measurements from several TESS instruments.
TESS devices with accelerometer will send ``azimuth`` and ``altitude``
values, otherwise they are ``NULL``.
TESS devices with a GPS will send ``longitude``, ``latitude`` and
``height`` values, otherwise they are ``NULL``.
OPERATION & MAINTENANCE
TESS instruments and locations
There is no master file concerning TESS instruments since they register
themselves on power-up.
There is a master file of all locations relevant to the deployment of
TESS instruments: ``config/locations.json``).
It is not possible to assign beforehand where a given TESS instruments
will be deployed. Besides, a given TESS instruments could be temporally
moved from one site to another. So, to maintain the current location
where a given TESS is deployed, there is another file named
``config/tess_location.json`` where this relationship is established.
*For personal use only*. In order to reject readings from other TESS
instruments coming to your own personal database, you can specify an
instrument name (or a list of instrument names). All readings whose
instrument names do no match the one defined in the configuration file
will be silently discarded.
Sunrise / Sunset filtering
It is recommended to activate the sunrise/sunset filter to reject TESS
readings coming in daytime and avoid unnecessary grouth in the database.
Each location has a latitude (degrees), longitude (degrees) and
elevation (meters) which can be neglected is this filter is not used.
Activating this filter have the following conecuences: 1. Once a day, at
arount 00:00 UTC, all locations will have their sunrise and sunset time
computed, according to the local horizon defined (configurable). 2.
Instruments assigned to locations found to be in daytime will have their
readings rejected. 2. ***Instruments not assigned to a known location
will have their readings rejected***. 3. Instruments assigned to
Locations with ``NULL`` or ``Unknown`` longitude, latitude or elevation
columns will have their readings rejected.
SQLite Database Maintenance
***Only in Linux*** The database and log file are rotated daily by a
cron script file at 12:00 UTC. This is done to prevent a costly file
copy at midnight, precisely when the database is busy writting samples.
The program is first put in pause mode, do the copy and then resumes
TODO: Brief introduction on what you do with files - including link to relevant help section.