database_setup_script

sqlite3 vesta.db

BEGIN;
CREATE TABLE IF NOT EXISTS Devices (
  devKey INTEGER,
  userName TEXT,
  modelName TEXT,
  manufName TEXT,
  nwkId TEXT,
  eui64 TEXT,
  devType TEXT,
  endPoints TEXT,
  inClusters TEXT,
  outClusters TEXT,
  binding TEXT,
  reporting TEXT,
  iasZoneType TEXT,
  Unused INTEGER,
  firmwareVersion TEXT);

# Add new entries to Devices above;
# xxxReporting format is <minS>,<maxS>,<delta>
ALTER TABLE Devices ADD COLUMN batteryReporting TEXT;
ALTER TABLE Devices ADD COLUMN temperatureReporting TEXT;
ALTER TABLE Devices ADD COLUMN powerReporting TEXT;
ALTER TABLE Devices ADD COLUMN energyConsumedReporting TEXT;
ALTER TABLE Devices ADD COLUMN energyGeneratedReporting TEXT;
ALTER TABLE Devices ADD COLUMN checkInFrequency TEXT;
ALTER TABLE Devices ADD COLUMN pirSensitivity TEXT;

CREATE TABLE IF NOT EXISTS BatteryPercentage (
  timestamp DATETIME, value INTEGER, devKey INTEGER,
  FOREIGN KEY(devKey) REFERENCES Devices(devKey));

CREATE TABLE IF NOT EXISTS TemperatureCelsius (
  timestamp DATETIME, value INTEGER, devKey INTEGER,
  FOREIGN KEY(devKey) REFERENCES Devices(devKey));

CREATE TABLE IF NOT EXISTS SignalPercentage (
  timestamp DATETIME, value INTEGER, devKey INTEGER,
  FOREIGN KEY(devKey) REFERENCES Devices(devKey));

CREATE TABLE IF NOT EXISTS Presence (
  timestamp DATETIME, value TEXT, devKey INTEGER,
  FOREIGN KEY(devKey) REFERENCES Devices(devKey));

CREATE TABLE IF NOT EXISTS PowerReadingW (
  timestamp DATETIME, value INTEGER, devKey INTEGER,
  FOREIGN KEY(devKey) REFERENCES Devices(devKey));

CREATE TABLE IF NOT EXISTS EnergyConsumedWh (
  timestamp DATETIME, value INTEGER, devKey INTEGER,
  FOREIGN KEY(devKey) REFERENCES Devices(devKey));

CREATE TABLE IF NOT EXISTS EnergyGeneratedWh (
  timestamp DATETIME, value INTEGER, devKey INTEGER,
  FOREIGN KEY(devKey) REFERENCES Devices(devKey));

CREATE TABLE IF NOT EXISTS Events (
  timestamp DATETIME, event TEXT, devKey INTEGER,
  FOREIGN KEY(devKey) REFERENCES Devices(devKey));

CREATE TABLE IF NOT EXISTS Groups (
  userName TEXT,
  devKeyList TEXT);

CREATE TABLE IF NOT EXISTS Rules (rule TEXT);
# .import rules.txt Rules;

CREATE TABLE IF NOT EXISTS AppState (Name TEXT PRIMARY KEY, Value TEXT);

CREATE TABLE IF NOT EXISTS Users (
    id INTEGER PRIMARY KEY,
    name varchar(64),
    passwordHash varchar(255),
    email varchar(64));
    CREATE UNIQUE INDEX name_UNIQUE ON users (name ASC);
    CREATE UNIQUE INDEX email_UNIQUE ON users (email ASC);

COMMIT;
.quit
# Next line needed to allow Python to write back to the database
chmod 666 vesta.db
# Next line needed to allow SQL from Apache to write back to the database
chmod 777 ..