Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
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
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,
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,
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; //