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

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

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

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!

ALTER TABLE ukhasnet.upload ADD COLUMN rssi INT DEFAULT NULL;

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

Changes to bot, api and specialist gateways.

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)
);

This is a proposal of the changes to make to add Node Type functionality

Creation of new Table to hold node types.

CREATE TABLE ukhasnet.nodetypes (
  id              serial       NOT NULL,
  description     VARCHAR(20)  NOT NULL,
  icon            VARCHAR(20)  NOT NULL DEFAULT 'node.png',
  PRIMARY KEY (id)
);

Changes to nodes table

ALTER TABLE ukhasnet.nodes ADD COLUMN typeid INT;
ALTER TABLE ukhasnet.nodes ADD CONSTRAINT nodes_typeid_fkey FOREIGN KEY(typeid) REFERENCES ukhasnet.nodetypes(id);

Added to Schema 2014-04-26 - Mike

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