02-smartdata_v1.sql

CREATE DATABASE IF NOT EXISTS `smartdata_v1`;

USE `smartdata_v1`;

--
-- Table structure for table `counters`
--
DROP TABLE IF EXISTS `counters`;
CREATE TABLE `counters` (`domain` varchar(48) PRIMARY KEY, c INT);
DELIMITER //
CREATE OR REPLACE FUNCTION `counter` (_domain varchar(48)) RETURNS INT
  BEGIN
    UPDATE `counters` SET c = c + 1 WHERE `domain` = _domain;
    RETURN (SELECT c FROM `counters`  WHERE `domain` = _domain LIMIT 1);
  END //
DELIMITER ;

--
-- Table structure for table `series`
--
DROP TABLE IF EXISTS `rows`;
DROP TABLE IF EXISTS `series`;
CREATE TABLE `series` (
  `id`         bigint unsigned NOT NULL AUTO_INCREMENT,
  `version`    tinyint unsigned NOT NULL,
  `unit`       int unsigned NOT NULL,
  `x`          int NOT NULL,
  `y`          int NOT NULL,
  `z`          int NOT NULL,
  `r`          int unsigned NOT NULL,
  `dev`        int unsigned NOT NULL DEFAULT '0',
  `t0`         bigint unsigned NOT NULL,
  `t1`         bigint unsigned NOT NULL,
  `workflow`   int unsigned NOT NULL DEFAULT '0',
  `domain`     varchar(48) NOT NULL DEFAULT 'public',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `type` char(3) DEFAULT 'OLD',
  `period` bigint(20) DEFAULT 0,
  `event` varchar(300) DEFAULT NULL,
  `count` int(11) DEFAULT NULL,
  `uncertainty` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_series` (`version`,`unit`,`x`,`y`,`z`,`r`,`dev`,`t0`,`t1`,`workflow`,`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `rows`
--
DROP TABLE IF EXISTS `rows`;
CREATE TABLE `rows` (
  `id`         bigint unsigned NOT NULL AUTO_INCREMENT,
  `x`          int NOT NULL,
  `y`          int NOT NULL,
  `z`          int NOT NULL,
  `dev`        int unsigned NOT NULL DEFAULT '0',
  `count`      bigint unsigned NOT NULL DEFAULT '0',
  `row_id`     bigint unsigned NOT NULL,
  `series_id`  bigint unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_rows` (`x`,`y`,`z`,`dev`,`row_id`,`series_id`),
  CONSTRAINT `fk_row_series`
    FOREIGN KEY (`series_id`) REFERENCES `series` (`id`)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `trackers`
--
DROP TABLE IF EXISTS `trackers`;
CREATE TABLE `trackers` (
  `id`         bigint unsigned NOT NULL AUTO_INCREMENT,
  `version`    tinyint unsigned NOT NULL,
  `unit`       int unsigned NOT NULL,
  `dev`        int unsigned NOT NULL DEFAULT '0',
  `t0`         bigint unsigned NOT NULL,
  `t1`         bigint unsigned NOT NULL,
  `signature`  bigint unsigned NOT NULL,
  `count`      bigint unsigned NOT NULL DEFAULT '0',
  `workflow`   int unsigned NOT NULL DEFAULT '0',
  `domain`     varchar(48) NOT NULL DEFAULT 'public',
  `row_id`     bigint unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `type` char(3) DEFAULT 'OLD',
  `period` bigint(20) DEFAULT 0,
  `event` varchar(300) DEFAULT NULL,
  `uncertainty` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_trackers` (`version`,`unit`,`signature`,`t0`,`t1`,`dev`,`workflow`,`domain`,`row_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `clients`
--
DROP TABLE IF EXISTS `clients`;
CREATE TABLE `clients` (
  `id`          bigint unsigned NOT NULL AUTO_INCREMENT,
  `certificate` varchar(64)     NOT NULL,
  `name`        varchar(64)     NOT NULL,
  `domain`      varchar(48)     NOT NULL,
  `level`       varchar(2)      NOT NULL,
  `enable`      boolean         NOT NULL DEFAULT false,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DELIMITER //
CREATE TRIGGER `check_level` BEFORE INSERT ON `clients` FOR EACH ROW
BEGIN
  IF NEW.`level` NOT IN ('r', 'w', 'rw') THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = '[table:clients] - `level` column is not valid';
  END IF;
END; //