Database Uploads

Warning

The Push scripts (and webinterface) require the latest version of the database. Even if the server is not running the web inteface. You will need to follow the database upgrade instructions found here Updating the Database

Note

These scripts require a mysql database, even for testing. Unfortunately sqlites table locking mechinism does not play nicely when reading / writing data from several scripts.

Note

Currently the sync is one time / one way only, (ie push from local to remote database) This means that any changes made in either database will not be reflected. This would be an interesting todo for the future.

Overview

The Database Upload (Push) functionality, enables a database on a remote server to be uploaded to a remote (central) server. Without having to resort to command line scripting, mysql dumps etc.

The push function is designed to be run as a daemon process, periodically pushing updates to the central point.

This functionality makes use of the REST interface (See Rest Interface.)

Installing Push Script

The Push Script currently lives in the Base Cogent-house software. Follow the install instructions at this site to install the push script.

Testing / Configuring the push script

Test the connection to the remote server by visiting the <base>/rest/sensortype/ url in your webbrowser (for example http://127.0.0.1:6543/rest/sensortype/ if you are running the web interface via the paste server) There should be a JSON dictionary of sensortype objects displayed.

Configuring the Push Script

Modify the synchronise.conf file (base/push/synchronise.conf) with the details of the remote rest server you visited above.

Running the Push Script

To run the push script:

python RestPusher.py

The script should output logging information to a logfile.

Running the Push Script as a Daemon

Note

Write code / Docs for this

Algorithm Overview

Each Iteration of the sync function does the following:

1. Read the Configuration Files
2. For Each Server that needs to be updated
   1. Ensure all local nodes are in the remote table
   2. Map Active Deployments
   3. Map Active Houses
   4. Synchronise the Location Table
   5. Update Nodes with new locations
   6. Upload the Relevant Node States
   7. Upload the Relevant Readings
   8. Update Configuration Files with last transmitted sample

These steps are described in detail below:

Synchronising Nodes

This method uses SQLA to synchronise the node tables between the local and remote servers. As nodeIds should be static (ie node 69 will allways be node 69) then this function can make use of a simplistic approach.

1. For each local node, where the node id is not on the remote server: #. Create a new node with this Id #. Create a new set of sensors linked to this node

Note

Currently, we only create new sensors when adding a node. At some point we may need to update the code to deal with this

Mapping Deployments and Houses

Note

We Assume that the following parameters are unique

1. Deployment.name
2. House.address

To reduce the amount of network traffic, we only map Active Deployments / Houses, where an active house is defines as one that:

object.endDate == None
object.endDate >= lastUpdate

These database items are fetched from the local database, and mapped to their equivelent on the remote database. If no such item exists on the remote database then

The following queries are first run on the local database

1. Get Deployments / Houses to be updated

   Given the date of the last update, we fetch all deployments and houses where the finishdate is either None, or After the last update. This should reduce the amount of locations mapped to a minimum.

   If for some reason there are no deployments, then we get all active “houses” using the same parameters on the house table.

2. Get Locations to be Updated

   Based on the houseID’s of those houses that need are current. We fetch a set of locations from the local database.

3. Get Rooms to be Updated

   Now we have the location Id’s, it is possible to get a list of rooms that will need updating

4. Create new objects

[locations]

[local]