-
-
Notifications
You must be signed in to change notification settings - Fork 60
Database Layout
In this document the database layout is shown for version 0.500.
Database Layout - alertR Server
- Internals
- Options
- Nodes
- Sensors
- SensorsAlertLevels
- SensorsDataInt
- SensorsDataFloat
- SensorAlerts
- SensorAlertsDataInt
- SensorAlertsDataFloat
- Alerts
- AlertsAlertLevels
- Managers
Database Layout - alertR Manger Client Database
- Internals
- Options
- Nodes
- Sensors
- SensorsAlertLevels
- SensorsDataInt
- SensorsDataFloat
- SensorAlerts
- SensorAlertsAlertLevels
- SensorAlertsDataInt
- SensorAlertsDataFloat
- Alerts
- AlertsAlertLevels
- Managers
- AlertLevels
- Events
- EventsSensorAlert
- EventsStateChange
- EventsConnectedChange
- EventsSensorTimeOut
- EventsNewVersion
- EventsNewOption
- EventsNewNode
- EventsNewSensor
- EventsNewAlert
- EventsNewManager
- EventsChangeOption
- EventsChangeNode
- EventsChangeSensor
- EventsChangeAlert
- EventsChangeManager
- EventsDeleteNode
- EventsDeleteSensor
- EventsDeleteAlert
- EventsDeleteManager
- EventsDataInt
- EventsDataFloat
In the following the database layout of the alertR server is described.
Used for internal settings/options/values that should not be changed from the outside.
table: internals
- id (primary key)
- type
- value
Used for options of the alert system (at the moment only if active or not).
table options
- id (primary key)
- type
- value
Used for each client that connects to the server.
table nodes
- id (primary key)
- hostname (hostname of the client)
- username (username used by the client; is also the unique identifier of the client)
- nodeType (sensor, alert, manager)
- instance (i.e., alertClientDbus or sensorClientFIFO)
- connected (0 = no, 1 = yes)
- version
- rev
- persistent (0 = no, 1 = yes)
Used for each sensor watched by a client.
table sensors
- id (primary key)
- nodeId (id to the entry in the nodes table)
- remoteSensorId (id used by the client for this sensor)
- description
- state (0 = normal, 1 = triggered)
- lastStateUpdated (unix timestamp)
- alertDelay
- dataType (gives the type of data the sensor holds and consequentially where to find it: 0 = none, 1 = int, 2 = float)
Used for the registered alert levels (you can think of an alert level as a group) for sensors.
table sensorsAlertLevels
- sensorId (id to the entry in the sensors table, primary key)
- alertLevel (primary key)
Used to store the integer data of the corresponding sensor.
table sensorsDataInt
- sensorId (id to the entry in the sensors table, primary key)
- data
Used to store the floating point data of the corresponding sensor.
table sensorsDataFloat
- sensorId (id to the entry in the sensors table, primary key)
- data
Used for a triggered sensor alert (will be deleted after it is processed).
table sensorAlerts
- id (primary key)
- nodeId (id to the entry in the nodes table)
- sensorId (id to the entry in the sensors table)
- state (0 = normal, 1 = triggered)
- timeReceived (unix timestamp)
- dataJson (json string that represents the optional data that was sent by the sensor during an alert)
- changeState (0 = false, 1 = true)
- hasLatestData (0 = false, 1 = true)
- dataType (gives the type of data the sensor alert holds and consequentially where to find it: 0 = none, 1 = int, 2 = float)
Used to store the integer data of the corresponding sensor alert.
table sensorAlertsDataInt
- sensorAlertId (id to the entry in the sensorAlerts table, primary key)
- data
Used to store the floating point data of the corresponding sensor alert.
table sensorAlertsDataFloat
- sensorAlertId (id to the entry in the sensorAlerts table, primary key)
- data
Used for each alert that is managed by a client. It has to be notified by the server when a sensor alert has triggered.
table alerts
- id (primary key)
- nodeId (id to the entry in the nodes table)
- remoteAlertId (id used by the client for this alert)
- description
Used for the registered alert levels (you can think of an alert level as a group) for registered alerts.
table alertsAlertLevels
- alertId (id to the entry in the alerts table, primary key)
- alertLevel (primary key)
Used for all registered manager clients.
table managers
- id (primary key)
- nodeId (id to the entry in the nodes table)
- description
In the following the database layout of the alertR Manger Client Database is described.
Used for internal settings/options/values that should not be changed from the outside.
table: internals
- id (primary key)
- type
- value
Used for options of the alert system (at the moment only if active or not).
table options
- id (primary key)
- type
- value
Used for each client that connects to the server.
table nodes
- id (primary key)
- hostname (hostname of the client)
- nodeType (sensor, alert, manager)
- instance (i.e., alertClientDbus or sensorClientFIFO)
- connected (0 = no, 1 = yes)
- version
- rev
- newestVersion (the newest version that is available in the configured repository)
- newestRev (the newest revision that is available in the configured repository)
Used for each sensor watched by a client.
table sensors
- id (primary key)
- nodeId (id to the entry in the nodes table)
- remoteSensorId (id used by the client for this sensor)
- description
- state (0 = normal, 1 = triggered)
- lastStateUpdated (unix timestamp)
- alertDelay
- dataType (gives the type of data the sensor holds and consequentially where to find it: 0 = none, 1 = int, 2 = float)
Used for the registered alert levels (you can think of an alert level as a group) for sensors.
table sensorsAlertLevels
- sensorId (id to the entry in the sensors table, primary key)
- alertLevel (primary key)
Used to store the integer data of the corresponding sensor.
table sensorsDataInt
- sensorId (id to the entry in the sensors table, unique, primary key)
- data
Used to store the floating point data of the corresponding sensor.
table sensorsDataFloat
- sensorId (id to the entry in the sensors table, unique, primary key)
- data
Used for a triggered sensor alert (will be stored for the time given in the configuration file).
table sensorAlerts
- id (primary key)
- sensorId (id to the entry in the sensors table)
- state (0 = normal, 1 = triggered)
- description
- timeReceived (unix timestamp)
- dataJson (json string that represents the optional data that was sent by the sensor during an alert)
- dataType (gives the type of data the sensor alert holds and consequentially where to find it: 0 = none, 1 = int, 2 = float)
Used for the alert levels that are triggered by the sensor alert.
table sensorAlertsAlertLevels
- sensorAlertId (id to the entry in the sensorAlerts table, primary key)
- alertLevel (primary key)
Used to store the integer data of the corresponding sensor.
table sensorAlertsDataInt
- sensorAlertId (id to the entry in the sensorAlerts table, unique, primary key)
- data
Used to store the floating point data of the corresponding sensor.
table sensorAlertsDataFloat
- sensorAlertId (id to the entry in the sensorAlerts table, unique, primary key)
- data
Used for each alert that is managed by a client.
table alerts
- id (primary key)
- nodeId (id to the entry in the nodes table)
- remoteAlertId (id used by the client for this alert)
- description
Used for the registered alert levels (you can think of an alert level as a group) for registered alerts.
table alertsAlertLevels
- alertId (id to the entry in the alerts table, primary key)
- alertLevel (primary key)
Used for all registered manager clients.
table managers
- id (primary key)
- nodeId (id to the entry in the nodes table)
- description
Used for all registered manager clients.
table alertLevels
- alertLevel (primary key)
- name
- triggerAlways (sets if this alert level always triggers a sensor alert) (0 = no, 1 = yes)
- smtpActivated (sets if an eMail notification should be sent) (0 = no, 1 = yes)
- toAddr (the address to which the eMail notification is sent to)
Used to store all received events.
table events
- id (primary key)
- timeOccurred (unix timestamp)
- type (type of the event)
Used to store all sensor alert events.
table eventsSensorAlert
- eventId (id to the entry in the events table, primary key)
- description
- state (0 = normal, 1 = triggered)
- dataType (gives the type of data the sensor alert holds and consequentially where to find it: 0 = none, 1 = int, 2 = float)
Used to store all state change events.
table eventsStateChange
- eventId (id to the entry in the events table, primary key)
- hostname
- description
- state (0 = normal, 1 = triggered)
- dataType (gives the type of data the sensor holds and consequentially where to find it: 0 = none, 1 = int, 2 = float)
Used to store all connected change events.
table eventsConnectedChange
- eventId (id to the entry in the events table, primary key)
- hostname
- nodeType (sensor, alert, manager)
- instance (i.e., alertClientDbus or sensorClientFIFO)
- connected (0 = no, 1 = yes)
Used to store all sensor time out events.
table eventsSensorTimeOut
- eventId (id to the entry in the events table, primary key)
- hostname
- description
- state (0 = normal, 1 = triggered)
Used to store all new version available events.
table eventsNewVersion
- eventId (id to the entry in the events table, primary key)
- usedVersion
- usedRev
- newVersion
- newRev
- instance (i.e., alertClientDbus or sensorClientFIFO)
- hostname
Used to store all new option appeared events.
table eventsNewOption
- id
- eventId (id to the entry in the events table)
- type
- value
Used to store all new node appeared events.
table eventsNewNode
- eventId (id to the entry in the events table, primary key)
- hostname
- nodeType (sensor, alert, manager)
- instance (i.e., alertClientDbus or sensorClientFIFO)
Used to store all new sensor appeared events.
table eventsNewSensor
- eventId (id to the entry in the events table, primary key)
- hostname
- description
- state (0 = normal, 1 = triggered)
Used to store all new alert appeared events.
table eventsNewAlert
- eventId (id to the entry in the events table, primary key)
- hostname
- description
Used to store all new manager appeared events.
table eventsNewManager
- eventId (id to the entry in the events table, primary key)
- hostname
- description
Used to store all option change events.
table eventsChangeOption
- eventId (id to the entry in the events table, primary key)
- type
- oldValue
- newValue
Used to store all node change events.
table eventsChangeNode
- eventId (id to the entry in the events table, primary key)
- oldHostname
- oldNodeType (sensor, alert, manager)
- oldInstance (i.e., alertClientDbus or sensorClientFIFO)
- oldVersion
- oldRev
- oldUsername
- newHostname
- newNodeType (sensor, alert, manager)
- newInstance (i.e., alertClientDbus or sensorClientFIFO)
- newVersion
- newRev
- newUsername
Used to store all sensor change events.
table eventsChangeSensor
- eventId (id to the entry in the events table, primary key)
- oldAlertDelay
- oldDescription
- oldRemoteSensorId (id used by the client for this sensor)
- newAlertDelay
- newDescription
- newRemoteSensorId (id used by the client for this sensor)
Used to store all alert change events.
table eventsChangeAlert
- eventId (id to the entry in the events table, primary key)
- oldDescription
- oldRemoteAlertId (id used by the client for this alert)
- newDescription
- newRemoteAlertId (id used by the client for this alert)
Used to store all manager change events.
table eventsChangeManager
- eventId (id to the entry in the events table, primary key)
- oldDescription
- newDescription
Used to store all node delete events.
table eventsDeleteNode
- eventId (id to the entry in the events table, primary key)
- hostname
- nodeType (sensor, alert, manager)
- instance (i.e., alertClientDbus or sensorClientFIFO)
Used to store all sensor delete events.
table eventsDeleteSensor
- eventId (id to the entry in the events table, primary key)
- description
Used to store all alert delete events.
table eventsDeleteAlert
- eventId (id to the entry in the events table, primary key)
- description
Used to store all manager delete events.
table eventsDeleteManager
- eventId (id to the entry in the events table, primary key)
- description
Used to store the integer data of the corresponding event.
table eventsDataInt
- eventId (id to the entry in the events table, unique, primary key)
- data
Used to store the floating point data of the corresponding event.
table eventsDataFloat
- eventId (id to the entry in the events table, unique, primary key)
- data