{ "info": { "author": "Rafael Gonzalez", "author_email": "astrorafael@yahoo.es", "bugtrack_url": null, "classifiers": [ "Development Status :: 4 - Beta", "Environment :: No Input/Output (Daemon)", "Intended Audience :: Developers", "Intended Audience :: Science/Research", "License :: OSI Approved :: MIT License", "Operating System :: POSIX :: Linux", "Programming Language :: Python :: 2.7", "Programming Language :: Python :: 3.6", "Programming Language :: SQL", "Topic :: Scientific/Engineering :: Astronomy", "Topic :: Scientific/Engineering :: Atmospheric Science" ], "description": "# tessdb (overview)\n\nLinux service to collect measurements pubished by TESS Sky Quality Meter via MQTT. TESS stands for [Cristobal Garcia's Telescope Encoder and Sky Sensor](http://www.observatorioremoto.com/TESS.pdf)\n\n**tessdb** is being used as part of the [STARS4ALL Project](http://www.stars4all.eu/).\n\n## Description\n\n**tessdb** is a software package that collects measurements from one or several\nTESS instruments into database (currently a SQLite Database). \n\nIt is a [Python Twisted Application](https://twistedmatrix.com/trac/)\nthat uses a [custom Twisted library implementing the MQTT protocol](https://github.com/astrorafael/twisted-mqtt)\n\nDesktop applicatons may query the database to generate reports and graphs\nusing the accumulated, historic data. There are some reports scripts already included in the package, specially an IDA-format monthly report script.\n\n**Note**: The Windows version has been dropped, as it was never used.\n\nThese data sources are available:\n\n+ individual samples (real time, configurable, 1 min. aprox between samples).\n\nInstrument should send their readings at twice the time resolution specified in the config file (in seconds).\n\n**Warning**: Time referencie is always UTC, not local time.\n\n# INSTALLATION\n \n## Requirements\n\nThe following components are needed and should be installed first:\n\n * python 2.7.x (tested on Ubuntu Python 2.7.6) or python 3.6+\n\n**Note:** It is foreseen a Python 3 migration in the future, retaining Python 2.7.x compatibility.\n\n### Installation\n\nInstallation is done from GitHub:\n\n git clone https://github.com/astrorafael/tessdb.git\n cd tessdb\n sudo python setup.py install\n\n**Note:** Installation from PyPi is now obsolete. Do not use the package uploaded in PyPi.\n\n* All executables are copied to `/usr/local/bin`\n* The database is located at `/var/dbase/tess.db` by default\n* The log file is located at `/var/log/tessdb.log`\n* The following required PIP packages will be automatically installed:\n - twisted,\n - twisted-mqtt\n - pyephem\n \n### Start up and Verification\n\n* Type `sudo tessdb -k` to start the service in foreground with console output.\nVerify that it starts without errors or exceptions. When done, abort it with `^C`\n\nFrom tessdb release 1.2.0, the background execution is handled as a `systemd` service instead of the old system V style init script:\n\n `sudo systemctl start tessdb`\n\nalthough the old `sudo service tessdb start` command still works.\n* It is strongly recommended to enable the service at boot time by issuing:\n\n`sudo systemctl enable tessdb`\n\n# CONFIGURATION\n\nThere is a small configuration file for this service:\n\n* `/etc/tessdb/config` (Linux)\n\nThis file is self explanatory. \nIn special, the database file name and location is specified in this file.\nSome of the properities marked in this file are marked as *reloadable property*. This means that this value can be changed and the process reloads its new value on the fly.\n\n## Sunrise / Sunset filtering\nIt is recommended to activate the sunrise/sunset filter to reject TESS readings coming in daytime and avoid unnecessary grouth in the database.\nEach location has a latitude (degrees), longitude (degrees) and elevation (meters) which can be neglected is this filter is not used. \n\nActivating this filter have the following conecuences:\n1. 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).\n2. Instruments assigned to locations found to be in daytime will have their readings rejected.\n3. ***New***: ***Instruments assigned to locations with `NULL` or `Unknown` longitude, latitude or elvation will have their readings accepted anyway***. The only way to enable or disable writting to the database is by using the *tess utility*.\n\n## Logging\n\nLog file is usually placed under `/var/log/tessdb.log` in Linux or under `C:\\tessdb\\log` folder on Windows. \nDefault log level is `info`. It generates very litte logging at this level.\nFile is rotated by logrotate **only under Linux**. \n\n# OPERATION\n\n## Server Status/Start/Stop/Restart\n\n* Service status: `sudo systemctl status tessdb` or `sudo service tessdb status`\n* Start Service: `sudo systemctl start tessdb` or `sudo service tessdb start`\n* Stop Service: `sudo systemctl stop tessdb` or `sudo service tessdb stop`\n* Restart Service: `sudo systemctl restart tessdb` or `sudo service tessdb stop`\n\n## Service Pause/Resume\n\nThe server can be put in *pause mode*, in which will be still receiving incoming MQTT messages but will be internally enqueued and not written to the database. Also, all connections to the database are closed. This allows to perform high risk operations on the database without loss of incoming data. Examples:\n\n* Compact the database using the SQLite `VACUUM` pragma\n* Migrating data from tables.\n* etc.\n\nService pause/resume use internally signals `SIGUSR1` and `SIGUSR2`.\n\nTo pause the server, type: `sudo tessdb_pause` and watch the log file output wit `tail -f /var/log/tessdb.log`:\n\n```\n2018-11-23T13:08:25+0100 [dbase#info] TESS database writer paused\n2018-11-23T13:08:25+0100 [dbase#info] Closed a DB Connection to /var/dbase/tess.db\n```\n\n**Note:** The old `sudo service tessdb pause` command do not work anymore.\n\nTo resume normal operation type `sudo tessdb_resume` and watch the same log file:\n\n```\n2018-11-23T13:10:37+0100 [dbase#info] TESS database writer resumed\n2018-11-23T13:10:37+0100 [dbase#info] Opened a DB Connection to /var/dbase/tess.db\n```\n\n## Service reload\n\nDuring 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 of reloadadble properties is described inside the configuration file.\n\n* Type `sudo systemctl reload tessdb` or `sudo service tessdb reload`. \n\n## Mainrteinance\n\nDatabase mainteinance is made through the `tess` command line utility, installed by the tessdb-reports package.\nMainteninance operations include:\n- create new locations\n- create new TESS instruments (manually)\n- assign locations to instruments\n- enable recording of data received from an instrument\n- listing current instruments\n- listing instruments not assigned to any known location\n- etc.\n\n\n# DATA MODEL\n\n## Dimensional Modelling\n\nThe data model follows the [dimensional modelling]\n(https://en.wikipedia.org/wiki/Dimensional_modeling) approach by Ralph Kimball. More references can also be found in [Star Schemas](https://en.wikipedia.org/wiki/Star_schema).\n\n## The data model\n\nThe figure below shows the layout of **tessdb**.\n\n![TESS Database Model](doc/tessdb-full.png)\n\n### Dimension Tables\n\nThey are:\n\n* `date_t` : preloaded from 2016 to 2026\n* `time_t` : preloaded, with seconds resolution (configurable)\n* `tess_t` : registered TESS instruments collecting data\n* `location_t` : locations where instruments are deployed\n* `tess_units_t` : an assorted collection of unit labels for reports, preloaded with current units.\n* `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.\n\n#### Date Dimension\n\nPretty much standard date table from dimensional modelling. Contains most used attributes plus `julian_day` specific to Astronomy domain.\n\n#### Time of the day Dimension\n\nPretty much standard time of the day table from dimensional modelling. Contains well known attributes.\n\n#### Instrument Dimension\n\nThis dimension holds the current list of TESS instruments. \n\n* The real key is an artificial key `tess_id` linked to the Fact table.\n* The `mac_address` could be the natural key if it weren't for the zero point and filter history tracking.\n* The `name` attribute could be an alternative key for the same reason. TESS instruments send readings using this name in the MQTT payload. A TESS instrument name can be changed as long as there is no other instrument with the same name.\n* The `location_id` is a reference to the current location assigned to the instrument. Location id -1 denotes the \"Unknown\" location.\n* `model` refers to the current TESS model.\n* `firmware` contains the current firmware version.\n* `fov` contains the Field of View, in degrees.\n* `cover_offset` is an additional offset in mag/arcserc^2 to account for an additional optical window attenuation attached tothe TESS itself. Defaults to 0.0.\n* `channel` is the current channel identifier. Default value is 0. Currently, the TESS photometer has only one channel.\n* `authorised` to allow the TESS instrument to store readings on the database. Authorization is a manual process done by the *tess utility*.\n* `registered` shos if the TESS instrument registered itself on the database (\"Automatic\") or it was done by a manual process (\"Manual\") using the *tess utility*. The default value is \"Unknown\" for the TESS instrument registered before adding this feature to the software. It is expected to identify these cases one by one and set them to 'Manual' or 'Automatic'.\n\n##### Version-controlled Attributes\nThese attubutes are version-controlled and a historic of these is maintained. A new change in any of them will generate a new row in the `tess_t` table\n* The `zero_point` holds the current value of the instrument calibration constant. Defaults to '20.5' (uncalibrated photometer).\n* The `filter` holds the current TESS filter (i.e. 'UVIR' or Dichroic Glass). Defaults to 'UVIR'\n* The `azimuth` and `altitude` attributes hold the photometer current orientation, in degrees. Default azimuth is 0.0 and default altitude is 90.0\n\n##### Version Control Attributes\nThese columns manage the version control of a given TESS attributes.\n* Columns `valid_since` and `valid_until` hold the timestamps where the changes to version controlled attributes are valid. \n* Column `valid_state` is an indicator. Its values are either **`Current`** or **`Expired`**. \nThe current valid TESS instrument has its `valid_state` set to `Current` and the expiration date in a far away future (Y2999).\n\n#### Unit dimension\n\nThe `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. \n\n* Columns `valid_since`, `valid_until` and `valid_state` keep track of any units change in a similar technique as above should the units change.\n\n#### Location dimension\n\nThis dimension table holds all known locations where TESS photometers are to be deployed.\n\n* `site`. Unique site name describing the this location.\n* `contact_person`. Person to account for observations.\n* `organization`. Organization where the contact person belongs to or running the facilities in the location.\n* `contact_email`. Contact person email address.\n* `longitude` Location longitude in degrees. West is negative.\n* `latitude`. Location latitude in degrees\n* `elevation`. Location elevation in meters\n* `zipcode`. Location ZIP code\n* `province`. Location country\n* `country`. Location country\n* `timezone`. Time zone (to calculate local time) in standard format described by Wikipedia[https://en.wikipedia.org/wiki/List_of_tz_database_time_zones]\n* `sunrise` & `sunset`. Computed attributes (oce per day) used to filter out readings in daylight.\n\n### Fact Tables\nThey are:\n\n* `tess_readings_t` : Accumulating snapshot fact table containing measurements from several TESS instruments.\n\nTESS devices with accelerometer will send `azimuth` and `altitude` values, otherwise they are `NULL`.\n\nTESS devices with a GPS will send `longitude`, `latitude` and `height` values, otherwise they are `NULL`.\n\n\n## Sample SQL Queries\n\nThe following are samples queries illustraing how to use the data model. They are actually being used by the STARS4ALL project\n\n1. Get a daily report of readings per instrument:\n\n```sh\n#!/bin/bash\nsqlite3 /var/dbase/tess.db < --help`\n\nExample:\n```\npi@rb-tess:~ $ tess location list --help\nusage: /usr/local/bin/tess location list [-h] [-p PAGE_SIZE] [-d DBASE]\n\noptional arguments:\n -h, --help show this help message and exit\n -p PAGE_SIZE, --page-size PAGE_SIZE\n list page size\n -d DBASE, --dbase DBASE\n SQLite database full file path\n```\n\n\n\n### Listing locations\n`tess location list`\n```\n+-----------------------------------------+-------------+------------+-------------+\n| Name | Longitude | Latitude | Elevation |\n+=========================================+=============+============+=============+\n| Laboratorio de Cristobal | -nn.nnnn | nn.nnnn | nnn |\n+-----------------------------------------+-------------+------------+-------------+\n| Centro de Recursos Asociativos El Cerro | -3.5515 | 40.4186 | 650 |\n+-----------------------------------------+-------------+------------+-------------+\n```\n\n### Renaming locations\n`tess location rename `\n\n### Listing TESS instruments\n`tess instrument list`\n\n```\n+---------+---------+-------------+------------+-------------+\n| TESS | Site | Longitude | Latitude | Elevation |\n+=========+=========+=============+============+=============+\n| pruebas | Unknown | Unknown | Unknown | Unknown |\n+---------+---------+-------------+------------+-------------+\n| stars1 | Unknown | Unknown | Unknown | Unknown |\n+---------+---------+-------------+------------+-------------+\n```\n\n\n### Assign a location to an instrument\nThe most important use of the tess utility is to assign an existing location to an instrument, since brand new registered instruments are assigned to a default *Unknown* location. This must be issued with `sudo`, since it requires a DB write.\n\n`sudo tess assign pruebas \"Laboratorio de Cristobal\"`\n\n```\n+---------+--------------------------+-------------+------------+-------------+\n| TESS | Site | Longitude | Latitude | Elevation |\n+=========+==========================+=============+============+=============+\n| pruebas | Laboratorio de Cristobal | -n.nnnnn | nn.nnnn | nnn |\n+---------+--------------------------+-------------+------------+-------------+\n```\n\n`tess instrument list`\n\n```\n+---------+--------------------------+-------------+------------+-------------+\n| TESS | Site | Longitude | Latitude | Elevation |\n+=========+==========================+=============+============+=============+\n| pruebas | Laboratorio de Cristobal | -nn.nnnnnnn | nn.nnnnnnn | nnn.n |\n+---------+--------------------------+-------------+------------+-------------+\n| stars1 | Unknown | Unknown | Unknown | Unknown |\n+---------+--------------------------+-------------+------------+-------------+\n```\n\n### Create a brand new TESS instrument\nIf automatic registration fails, this command allows manual creation of a TESS instrument in the database\n\n`tess instrument create {name} {mac} {zero point} {filter}`\n\n### Enabling/Disabling a TESS instrument\nIn order for a TESS to have its readings stored in the databae, it need to be enabled (authorised). In previous releases of tessdb this happened automatically when the TESS was assigned to a known location, when the daylingth filter was active. Now this is a separate but important procedure\n\n`tess instrument enable stars1`\n\n```\n+--------+--------------------------+--------------+\n| TESS | Site | Authorised |\n+========+==========================+==============+\n| stars1 | Laboratorio de Crist\u00f3bal | 1 |\n+--------+--------------------------+--------------+\n```\n\n\nIf, for soem reason, we must avoid a given TESS to store its readings, we proceed and disable it.\n\n`tess instrument disable stars1`\n\n```\n+--------+--------------------------+--------------+\n| TESS | Site | Authorised |\n+========+==========================+==============+\n| stars1 | Laboratorio de Crist\u00f3bal | 0 |\n+--------+--------------------------+--------------+\n```\n\n\n### Renaming a TESS instrument\nIf for some reason, an instrument needs to change the friendly user name, this command allows you to do so.\n`tess instrument rename {old name} {new name}`\n\n```\n+---------+-------------------+---------------+----------+-------------+------------+-------------+\n| TESS | MAC Addr. | Zero Point | Site | Longitude | Latitude | Elevation |\n+=========+===================+===============+==========+=============+============+=============+\n| pruebas | 18:FE:34:9C:AD:ED | 1.61 | Pamplona | n.nnnnn | nn.nnnn | nnn |\n+---------+-------------------+---------------+----------+-------------+------------+-------------+\n```\n\n### Deleting a TESS instrument\n**Use this option with utmost care, as it will leave orphaned readings**\n\nThis will also erase the instrument history of changes, as shown in the folowing example:\n\n`tess instrument delete pruebas`\n\n```\nAbout to delete\n+---------+-------------------+--------------+----------+--------------------------+-------------+------------+-------------+\n| TESS | MAC Addr. | Zero Point | Filter | Site | Longitude | Latitude | Elevation |\n+=========+===================+==============+==========+==========================+=============+============+=============+\n| pruebas | 18:FE:34:9C:AD:ED | 1.65 | UVIR | Laboratorio de Cristobal | -3.55809 | 40.4246 | 626 |\n+---------+-------------------+--------------+----------+--------------------------+-------------+------------+-------------+\n| pruebas | 18:FE:34:9C:AD:ED | 1.6 | UVIR | Laboratorio de Cristobal | -3.55809 | 40.4246 | 626 |\n+---------+-------------------+--------------+----------+--------------------------+-------------+------------+-------------+\n+--------+-----------------------+\n| TESS | Acumulated Readings |\n+========+=======================+\n| | 0 |\n+--------+-----------------------+\n\n```\n\n### Updating the zero point / filter / azimuth / altitude\nIf, for some reason, we need to change any of these, this command allows you to do so. Note that you must especify at least one magnitude.\n\nSince these attributes versioned columns in the database, a new instrument entry is made with updated `valid_since`, `valid_until` and `valid_state` columns. However, If the `--latest` flag is passed, the update command only changes the **current** TESS zero point or filter. This is useful to fix errors in the current TESS instrument definition.\n\n`tess instrument update {name} --zero-point {new zp} --filter {new filter} --azimuth {new azimuth} --altitude {new altitude} [--latest]`\n\nExample 1:\n`tess instrument update stars3 --zero-point 23.45 --filter BG39`\n\nExample 2:\n`tess instrument update stars3 --zero-point 19.99 --filter UVIR --latest`\n\n\n```\n+--------+--------------+----------+---------+---------------------+---------------------+--------------------------+\n| TESS | Zero Point | Filter | State | Since | Until | Site |\n+========+==============+==========+=========+=====================+=====================+==========================+\n| stars3 | 19.99 | DG | Current | 2016-09-08T13:59:12 | 2999-12-31T23:59:59 | Facultad de Fisicas, UCM |\n+--------+--------------+----------+---------+---------------------+---------------------+--------------------------+\n```\n\n\n### Listing the history changes of a single TESS instrument\n`tess instrument history stars2`\n\n```\n+--------+------+-------------------+--------------+----------+---------------------+---------------------+\n| TESS | Id | MAC Addr. | Zero Point | Filter | Since | Until |\n+========+======+===================+==============+==========+=====================+=====================+\n| stars2 | 7 | 18:FE:34:D3:44:42 | 20 | UVIR | 2016-05-14T18:23:03 | 2016-05-28T07:23:49 |\n+--------+------+-------------------+--------------+----------+---------------------+---------------------+\n| stars2 | 8 | 18:FE:34:D3:44:42 | 20.56 | UVIR | 2016-05-28T07:23:49 | 2016-09-08T11:13:46 |\n+--------+------+-------------------+--------------+----------+---------------------+---------------------+\n| stars2 | 21 | 18:FE:34:D3:44:42 | 20.5 | UVIR | 2016-09-08T11:13:46 | 2999-12-31T23:59:59 |\n+--------+------+-------------------+--------------+----------+---------------------+---------------------+\n```\n\n\n### Listing TESS readings\n`test readings list`\n```\n+--------------+--------+------------+-------------+-------------+\n| Timestamp | TESS | Location | Frequency | Magnitude |\n+==============+========+============+=============+=============+\n+--------------+--------+------------+-------------+-------------+\n```\n\n### Show system maintenance window\nThis command give the start time and duration for a safe system stop. At that time, it is guaranteed that all readings from the different photometers will be discarded due to daylight.\n\n```tess system window\n+---------------------+---------------------+----------+\n| Start Time (UTC) | End Time (UTC) | Window |\n+=====================+=====================+==========+\n| 2017-11-04 13:45:03 | 2017-11-04 14:44:35 | 0:59:32 |\n+---------------------+---------------------+----------+\n```\n\n# Sample SQL Queries\n\nThe following are samples queries illustraing how to use the data model. They are actually being used by the STARS4ALL project\n\n1. Get a daily report of readings per instrument:\n\n```sh\n#!/bin/bash\nsqlite3 /var/dbase/tess.db <