UKHASnet Wiki

The database behind ukhas.net is a PostgreSQL Database.

**NB:** Details of the tables will be provided here with diagrams however the layout is being worked upon.

====== Proposed changes ======
When making changes please comment as to what you've changed

===== Node Timestamps =====

===== Node Display =====
<code>
2014-05-05
22:53 < craag> mfa298: Could we have another field in the nodes table that selects whether a node is displayed on the
               map?
22:54 < craag> I've got a few of my mbed nodes that I'm not using, but are really cluttering.
22:56 < mfa298> I'm sure we can
</code>

**Node Type of '1' now prevents display on the map - Phil 6/5/2014**
===== Upload RSSI =====

This is a proposal to add the optional field 'RSSI' to the upload of rows - Phil

This allows the RSSI of a message as received by the gateway to be uploaded with the message and displayed in the logtail, as this is in the database future views of this data are of course possible!

<code sql>
ALTER TABLE ukhasnet.upload ADD COLUMN rssi INT DEFAULT NULL;
</code>

Ability to add this to the HTTP POST and to insert it will need to be added to the API.

**Added to Schema 2014-04-26 - Mike **


===== !msg function =====
Changes to bot, api and specialist gateways.

<code sql>
CREATE TYPE ukhasnet.msgstate AS ENUM ('Pending', 'Sent');
CREATE TABLE ukhasnet.irc_msg (
  id              serial       NOT NULL,
  time            TIMESTAMP    NOT NULL DEFAULT now(),
  src_nick        VARCHAR(20)  NOT NULL,
  src_chan        VARCHAR(20)  NOT NULL,
  gatewayid       INT          NOT NULL,
  nodeid          INT          NOT NULL,
  message         VARCHAR(100)  NOT NULL,
  status          ukhasnet.msgstate NOT NULL DEFAULT 'Pending',
  PRIMARY KEY (id),
  FOREIGN KEY (nodeid) REFERENCES ukhasnet.nodes(id),
  FOREIGN KEY (gatewayid) REFERENCES ukhasnet.nodes(id)
);
</code>
===== Node Types =====
This is a proposal of the changes to make to add Node Type functionality

Creation of new Table to hold node types.

<code sql>
CREATE TABLE ukhasnet.nodetypes (
  id              serial       NOT NULL,
  description     varchar(20)  NOT NULL,
  icon            varchar(20)  NOT NULL default 'node.png',
  PRIMARY KEY (id)
);
</code>

Changes to nodes table

<code sql>
ALTER TABLE ukhasnet.nodes ADD COLUMN typeid int;
ALTER TABLE ukhasnet.nodes ADD CONSTRAINT nodes_typeid_fkey FOREIGN KEY(typeid) REFERENCES ukhasnet.nodetypes(id);
</code>

**Added to Schema 2014-04-26 - Mike **
====== Notifications ======

//WIP//

Notifications are used to signal events within the db to interested apps.

**upload_row**

Emitted whenever a row in the 'upload' table is edited
  * Data: JSON encoded row that was changed (new data)
    * Format TBC

**map_location**

Emitted whenever the location of a node changes (in the 'nodes' table)
  * Data: JSON encoded node id, new location
    * Format TBC

**data_packet**

Emirted whenever a new data packet is parsed
  * Data: JSON encoded raw packet, list of new data points
UKHASnet Wiki

User Tools

Site Tools

Page Tools
