· 8 years ago · Aug 02, 2018, 10:18 PM
1-- MySQL dump 10.13 Distrib 5.6.41, for Linux (x86_64)
2--
3-- Host: localhost Database: wasabi
4-- ------------------------------------------------------
5-- Server version 5.6.41
6
7/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10/*!40101 SET NAMES utf8 */;
11/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
12/*!40103 SET TIME_ZONE='+00:00' */;
13/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
17
18--
19-- Current Database: `wasabi`
20--
21
22CREATE DATABASE /*!32312 IF NOT EXISTS*/ `wasabi` /*!40100 DEFAULT CHARACTER SET latin1 */;
23
24USE `wasabi`;
25
26--
27-- Table structure for table `bucket`
28--
29
30DROP TABLE IF EXISTS `bucket`;
31/*!40101 SET @saved_cs_client = @@character_set_client */;
32/*!40101 SET character_set_client = utf8 */;
33CREATE TABLE `bucket` (
34 `experiment_id` varbinary(16) NOT NULL,
35 `label` varchar(64) COLLATE utf8_bin NOT NULL,
36 `allocation_percent` double NOT NULL,
37 `is_control` tinyint(1) NOT NULL DEFAULT '0',
38 `payload` varchar(4096) COLLATE utf8_bin NOT NULL DEFAULT '',
39 `description` varchar(256) COLLATE utf8_bin NOT NULL DEFAULT '',
40 `state` varchar(16) COLLATE utf8_bin NOT NULL DEFAULT 'OPEN',
41 PRIMARY KEY (`experiment_id`,`label`),
42 KEY `experiment_id` (`experiment_id`),
43 CONSTRAINT `buckets_ibfk_1` FOREIGN KEY (`experiment_id`) REFERENCES `experiment` (`id`)
44) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
45/*!40101 SET character_set_client = @saved_cs_client */;
46
47--
48-- Dumping data for table `bucket`
49--
50
51LOCK TABLES `bucket` WRITE;
52/*!40000 ALTER TABLE `bucket` DISABLE KEYS */;
53/*!40000 ALTER TABLE `bucket` ENABLE KEYS */;
54UNLOCK TABLES;
55
56--
57-- Table structure for table `event_action`
58--
59
60DROP TABLE IF EXISTS `event_action`;
61/*!40101 SET @saved_cs_client = @@character_set_client */;
62/*!40101 SET character_set_client = utf8 */;
63CREATE TABLE `event_action` (
64 `user_id` varchar(200) COLLATE utf8_bin NOT NULL,
65 `experiment_id` varbinary(16) NOT NULL,
66 `bucket_label` varchar(64) COLLATE utf8_bin NOT NULL,
67 `timestamp` datetime NOT NULL,
68 `action` varchar(64) COLLATE utf8_bin NOT NULL,
69 `payload` varchar(4096) COLLATE utf8_bin DEFAULT NULL,
70 `context` varchar(200) CHARACTER SET utf8 DEFAULT 'PROD',
71 KEY `user_id` (`user_id`),
72 KEY `experiment_id` (`experiment_id`),
73 KEY `bucket_label` (`bucket_label`),
74 KEY `timestamp` (`timestamp`),
75 KEY `action` (`action`),
76 KEY `event_action_ibfk_1` (`experiment_id`,`bucket_label`),
77 CONSTRAINT `event_action_ibfk_1` FOREIGN KEY (`experiment_id`, `bucket_label`) REFERENCES `bucket` (`experiment_id`, `label`)
78) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
79/*!40101 SET character_set_client = @saved_cs_client */;
80
81--
82-- Dumping data for table `event_action`
83--
84
85LOCK TABLES `event_action` WRITE;
86/*!40000 ALTER TABLE `event_action` DISABLE KEYS */;
87/*!40000 ALTER TABLE `event_action` ENABLE KEYS */;
88UNLOCK TABLES;
89
90--
91-- Table structure for table `event_impression`
92--
93
94DROP TABLE IF EXISTS `event_impression`;
95/*!40101 SET @saved_cs_client = @@character_set_client */;
96/*!40101 SET character_set_client = utf8 */;
97CREATE TABLE `event_impression` (
98 `user_id` varchar(200) COLLATE utf8_bin NOT NULL,
99 `experiment_id` varbinary(16) NOT NULL,
100 `bucket_label` varchar(64) COLLATE utf8_bin NOT NULL,
101 `timestamp` datetime NOT NULL,
102 `payload` varchar(4096) COLLATE utf8_bin DEFAULT NULL,
103 `context` varchar(200) CHARACTER SET utf8 DEFAULT 'PROD',
104 KEY `user_id` (`user_id`),
105 KEY `experiment_id` (`experiment_id`),
106 KEY `bucket_label` (`bucket_label`),
107 KEY `timestamp` (`timestamp`),
108 KEY `event_impression_ibfk_1` (`experiment_id`,`bucket_label`),
109 CONSTRAINT `event_impression_ibfk_1` FOREIGN KEY (`experiment_id`, `bucket_label`) REFERENCES `bucket` (`experiment_id`, `label`)
110) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
111/*!40101 SET character_set_client = @saved_cs_client */;
112
113--
114-- Dumping data for table `event_impression`
115--
116
117LOCK TABLES `event_impression` WRITE;
118/*!40000 ALTER TABLE `event_impression` DISABLE KEYS */;
119/*!40000 ALTER TABLE `event_impression` ENABLE KEYS */;
120UNLOCK TABLES;
121
122--
123-- Table structure for table `experiment`
124--
125
126DROP TABLE IF EXISTS `experiment`;
127/*!40101 SET @saved_cs_client = @@character_set_client */;
128/*!40101 SET character_set_client = utf8 */;
129CREATE TABLE `experiment` (
130 `id` varbinary(16) NOT NULL,
131 `version` int(11) NOT NULL DEFAULT '0',
132 `creation_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
133 `modification_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
134 `description` varchar(256) COLLATE utf8_bin NOT NULL DEFAULT '',
135 `sampling_percent` double NOT NULL,
136 `start_time` datetime NOT NULL,
137 `end_time` datetime NOT NULL,
138 `state` varchar(16) COLLATE utf8_bin NOT NULL DEFAULT 'DRAFT',
139 `label` varchar(64) COLLATE utf8_bin NOT NULL,
140 `app_name` varchar(64) COLLATE utf8_bin NOT NULL,
141 PRIMARY KEY (`id`),
142 UNIQUE KEY `experiment_unique` (`app_name`,`label`,`version`),
143 KEY `start_time` (`start_time`),
144 KEY `end_time` (`end_time`),
145 KEY `state` (`state`)
146) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
147/*!40101 SET character_set_client = @saved_cs_client */;
148
149--
150-- Dumping data for table `experiment`
151--
152
153LOCK TABLES `experiment` WRITE;
154/*!40000 ALTER TABLE `experiment` DISABLE KEYS */;
155/*!40000 ALTER TABLE `experiment` ENABLE KEYS */;
156UNLOCK TABLES;
157/*!50003 SET @saved_cs_client = @@character_set_client */ ;
158/*!50003 SET @saved_cs_results = @@character_set_results */ ;
159/*!50003 SET @saved_col_connection = @@collation_connection */ ;
160/*!50003 SET character_set_client = latin1 */ ;
161/*!50003 SET character_set_results = latin1 */ ;
162/*!50003 SET collation_connection = latin1_swedish_ci */ ;
163/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
164/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
165DELIMITER ;;
166/*!50003 CREATE*/ /*!50017 DEFINER=`wasabi`@`%`*/ /*!50003 TRIGGER on_update_experiment BEFORE UPDATE ON experiment
167for each row begin
168 if NEW.state = 'DELETED' and OLD.state != 'DELETED' and OLD.version = 0 then
169 set NEW.version = (SELECT MAX(version)+1 FROM experiment
170 WHERE app_name=NEW.app_name AND label=NEW.label);
171 end if;
172end */;;
173DELIMITER ;
174/*!50003 SET sql_mode = @saved_sql_mode */ ;
175/*!50003 SET character_set_client = @saved_cs_client */ ;
176/*!50003 SET character_set_results = @saved_cs_results */ ;
177/*!50003 SET collation_connection = @saved_col_connection */ ;
178/*!50003 SET @saved_cs_client = @@character_set_client */ ;
179/*!50003 SET @saved_cs_results = @@character_set_results */ ;
180/*!50003 SET @saved_col_connection = @@collation_connection */ ;
181/*!50003 SET character_set_client = latin1 */ ;
182/*!50003 SET character_set_results = latin1 */ ;
183/*!50003 SET collation_connection = latin1_swedish_ci */ ;
184/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
185/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
186DELIMITER ;;
187/*!50003 CREATE*/ /*!50017 DEFINER=`wasabi`@`%`*/ /*!50003 TRIGGER on_delete_experiment BEFORE DELETE ON experiment
188for each row begin
189 DELETE FROM experiment_rollup WHERE experiment_id=OLD.id;
190 DELETE FROM event_impression WHERE experiment_id=OLD.id;
191 DELETE FROM event_action WHERE experiment_id=OLD.id;
192 DELETE FROM bucket WHERE experiment_id=OLD.id;
193end */;;
194DELIMITER ;
195/*!50003 SET sql_mode = @saved_sql_mode */ ;
196/*!50003 SET character_set_client = @saved_cs_client */ ;
197/*!50003 SET character_set_results = @saved_cs_results */ ;
198/*!50003 SET collation_connection = @saved_col_connection */ ;
199
200--
201-- Table structure for table `experiment_rollup`
202--
203
204DROP TABLE IF EXISTS `experiment_rollup`;
205/*!40101 SET @saved_cs_client = @@character_set_client */;
206/*!40101 SET character_set_client = utf8 */;
207CREATE TABLE `experiment_rollup` (
208 `experiment_id` varbinary(16) NOT NULL,
209 `day` date NOT NULL,
210 `cumulative` tinyint(1) NOT NULL,
211 `bucket_label` varchar(64) COLLATE utf8_bin NOT NULL,
212 `action` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
213 `impression_count` int(11) DEFAULT NULL,
214 `impression_user_count` int(11) DEFAULT NULL,
215 `action_count` int(11) DEFAULT NULL,
216 `action_user_count` int(11) DEFAULT NULL,
217 `context` varchar(200) CHARACTER SET utf8 DEFAULT 'PROD',
218 UNIQUE KEY `entry` (`experiment_id`,`day`,`cumulative`,`bucket_label`,`action`,`context`),
219 KEY `experiment_rollup_ibfk_1` (`experiment_id`,`bucket_label`),
220 CONSTRAINT `experiment_rollup_ibfk_1` FOREIGN KEY (`experiment_id`, `bucket_label`) REFERENCES `bucket` (`experiment_id`, `label`)
221) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
222/*!40101 SET character_set_client = @saved_cs_client */;
223
224--
225-- Dumping data for table `experiment_rollup`
226--
227
228LOCK TABLES `experiment_rollup` WRITE;
229/*!40000 ALTER TABLE `experiment_rollup` DISABLE KEYS */;
230/*!40000 ALTER TABLE `experiment_rollup` ENABLE KEYS */;
231UNLOCK TABLES;
232
233--
234-- Table structure for table `schema_version`
235--
236
237DROP TABLE IF EXISTS `schema_version`;
238/*!40101 SET @saved_cs_client = @@character_set_client */;
239/*!40101 SET character_set_client = utf8 */;
240CREATE TABLE `schema_version` (
241 `version_rank` int(11) NOT NULL,
242 `installed_rank` int(11) NOT NULL,
243 `version` varchar(50) NOT NULL,
244 `description` varchar(200) NOT NULL,
245 `type` varchar(20) NOT NULL,
246 `script` varchar(1000) NOT NULL,
247 `checksum` int(11) DEFAULT NULL,
248 `installed_by` varchar(100) NOT NULL,
249 `installed_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
250 `execution_time` int(11) NOT NULL,
251 `success` tinyint(1) NOT NULL,
252 PRIMARY KEY (`version`),
253 KEY `schema_version_vr_idx` (`version_rank`),
254 KEY `schema_version_ir_idx` (`installed_rank`),
255 KEY `schema_version_s_idx` (`success`)
256) ENGINE=InnoDB DEFAULT CHARSET=latin1;
257/*!40101 SET character_set_client = @saved_cs_client */;
258
259--
260-- Dumping data for table `schema_version`
261--
262
263LOCK TABLES `schema_version` WRITE;
264/*!40000 ALTER TABLE `schema_version` DISABLE KEYS */;
265INSERT INTO `schema_version` VALUES (1,1,'001','Create experiment table','SQL','V001__Create_experiment_table.sql',-1944830733,'readwrite','2018-08-02 15:19:21',30,1),(2,2,'002','Create bucket table','SQL','V002__Create_bucket_table.sql',769232990,'readwrite','2018-08-02 15:19:21',18,1),(3,3,'003','Create event impression table','SQL','V003__Create_event_impression_table.sql',-845472073,'readwrite','2018-08-02 15:19:21',25,1),(4,4,'004','Create event action table','SQL','V004__Create_event_action_table.sql',-548966472,'readwrite','2018-08-02 15:19:21',26,1),(5,5,'005','Create experiment rollup table','SQL','V005__Create_experiment_rollup_table.sql',-629328271,'readwrite','2018-08-02 15:19:21',18,1),(6,6,'006','Create on delete experiment trigger','SQL','V006__Create_on_delete_experiment_trigger.sql',-424289502,'readwrite','2018-08-02 15:19:21',9,1),(7,7,'007','Create on update experiment trigger','SQL','V007__Create_on_update_experiment_trigger.sql',837527935,'readwrite','2018-08-02 15:19:21',8,1),(8,8,'008','Alter bucket table','SQL','V008__Alter_bucket_table.sql',1786531217,'readwrite','2018-08-02 15:19:21',22,1),(9,9,'009','alter event impression and action tables','SQL','V009__alter_event_impression_and_action_tables.sql',890781961,'readwrite','2018-08-02 15:19:21',67,1),(10,10,'010','add event context','SQL','V010__add_event_context.sql',1263537245,'readwrite','2018-08-02 15:19:22',63,1),(11,11,'011','alter rollups table','SQL','V011__alter_rollups_table.sql',608469223,'readwrite','2018-08-02 15:19:22',24,1),(12,12,'012','alter context','SQL','V012__alter_context.sql',684163967,'readwrite','2018-08-02 15:19:22',108,1),(13,13,'013','Create user experiment properties','SQL','V013__Create_user_experiment_properties.sql',-209783663,'readwrite','2018-08-02 15:19:22',14,1),(14,14,'014','alter user id','SQL','V014__alter_user_id.sql',-895350313,'readwrite','2018-08-02 15:19:22',331,1);
266/*!40000 ALTER TABLE `schema_version` ENABLE KEYS */;
267UNLOCK TABLES;
268
269--
270-- Table structure for table `user_experiment_properties`
271--
272
273DROP TABLE IF EXISTS `user_experiment_properties`;
274/*!40101 SET @saved_cs_client = @@character_set_client */;
275/*!40101 SET character_set_client = utf8 */;
276CREATE TABLE `user_experiment_properties` (
277 `user_id` varchar(200) COLLATE utf8_bin NOT NULL,
278 `experiment_id` varbinary(16) NOT NULL,
279 `is_favorite` bit(1) DEFAULT b'0',
280 PRIMARY KEY (`user_id`,`experiment_id`)
281) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
282/*!40101 SET character_set_client = @saved_cs_client */;
283
284--
285-- Dumping data for table `user_experiment_properties`
286--
287
288LOCK TABLES `user_experiment_properties` WRITE;
289/*!40000 ALTER TABLE `user_experiment_properties` DISABLE KEYS */;
290/*!40000 ALTER TABLE `user_experiment_properties` ENABLE KEYS */;
291UNLOCK TABLES;
292/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
293
294/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
295/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
296/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
297/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
298/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
299/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
300/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
301
302-- Dump completed on 2018-08-02 15:20:31