Newer
Older
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) NOT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`domain`),
KEY `idx_domain` (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
--
-- 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,
UNIQUE KEY `uk_series` (`version`,`unit`,`x`,`y`,`z`,`r`,`dev`,`t0`,`t1`,`workflow`,`domain`,`type`,`period`),
KEY `idx_series_unit` (`unit`),
KEY `idx_series_domunit` (`domain`,`unit`,`t0`,`t1`),
KEY `idx_series_t0` (`t0`),
KEY `idx_series_t1` (`t1`),
KEY `idx_domain` (`domain`),
KEY `idx_updated_at` (`updated_at`),
KEY `idx_domain_updated_at` (`domain`,`updated_at`),
KEY `idx_domainupdated_at` (`domain`,`updated_at`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
--
-- 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(20) unsigned NOT NULL AUTO_INCREMENT,
`certificate` varchar(64) DEFAULT NULL,
`name` varchar(32) NOT NULL,
`domain` varchar(48) NOT NULL,
`level` varchar(2) NOT NULL,
`enable` tinyint(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_domain` (`domain`),
KEY `idx_certificate` (`certificate`)
) ENGINE=InnoDB AUTO_INCREMENT=80 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; //