Skip to content

Instantly share code, notes, and snippets.

@kstromeiraos
Created August 2, 2018 21:32
Show Gist options
  • Save kstromeiraos/5cc84e07cc166033fd28d53d9e2eb1d0 to your computer and use it in GitHub Desktop.
Save kstromeiraos/5cc84e07cc166033fd28d53d9e2eb1d0 to your computer and use it in GitHub Desktop.
Dump of MySQL DB generated by Wasabi
-- MySQL dump 10.13 Distrib 5.6.41, for Linux (x86_64)
--
-- Host: localhost Database: wasabi
-- ------------------------------------------------------
-- Server version 5.6.41
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `wasabi`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `wasabi` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `wasabi`;
--
-- Table structure for table `bucket`
--
DROP TABLE IF EXISTS `bucket`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `bucket` (
`experiment_id` varbinary(16) NOT NULL,
`label` varchar(64) COLLATE utf8_bin NOT NULL,
`allocation_percent` double NOT NULL,
`is_control` tinyint(1) NOT NULL DEFAULT '0',
`payload` varchar(4096) COLLATE utf8_bin NOT NULL DEFAULT '',
`description` varchar(256) COLLATE utf8_bin NOT NULL DEFAULT '',
`state` varchar(16) COLLATE utf8_bin NOT NULL DEFAULT 'OPEN',
PRIMARY KEY (`experiment_id`,`label`),
KEY `experiment_id` (`experiment_id`),
CONSTRAINT `buckets_ibfk_1` FOREIGN KEY (`experiment_id`) REFERENCES `experiment` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `bucket`
--
LOCK TABLES `bucket` WRITE;
/*!40000 ALTER TABLE `bucket` DISABLE KEYS */;
/*!40000 ALTER TABLE `bucket` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `event_action`
--
DROP TABLE IF EXISTS `event_action`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `event_action` (
`user_id` varchar(200) COLLATE utf8_bin NOT NULL,
`experiment_id` varbinary(16) NOT NULL,
`bucket_label` varchar(64) COLLATE utf8_bin NOT NULL,
`timestamp` datetime NOT NULL,
`action` varchar(64) COLLATE utf8_bin NOT NULL,
`payload` varchar(4096) COLLATE utf8_bin DEFAULT NULL,
`context` varchar(200) CHARACTER SET utf8 DEFAULT 'PROD',
KEY `user_id` (`user_id`),
KEY `experiment_id` (`experiment_id`),
KEY `bucket_label` (`bucket_label`),
KEY `timestamp` (`timestamp`),
KEY `action` (`action`),
KEY `event_action_ibfk_1` (`experiment_id`,`bucket_label`),
CONSTRAINT `event_action_ibfk_1` FOREIGN KEY (`experiment_id`, `bucket_label`) REFERENCES `bucket` (`experiment_id`, `label`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `event_action`
--
LOCK TABLES `event_action` WRITE;
/*!40000 ALTER TABLE `event_action` DISABLE KEYS */;
/*!40000 ALTER TABLE `event_action` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `event_impression`
--
DROP TABLE IF EXISTS `event_impression`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `event_impression` (
`user_id` varchar(200) COLLATE utf8_bin NOT NULL,
`experiment_id` varbinary(16) NOT NULL,
`bucket_label` varchar(64) COLLATE utf8_bin NOT NULL,
`timestamp` datetime NOT NULL,
`payload` varchar(4096) COLLATE utf8_bin DEFAULT NULL,
`context` varchar(200) CHARACTER SET utf8 DEFAULT 'PROD',
KEY `user_id` (`user_id`),
KEY `experiment_id` (`experiment_id`),
KEY `bucket_label` (`bucket_label`),
KEY `timestamp` (`timestamp`),
KEY `event_impression_ibfk_1` (`experiment_id`,`bucket_label`),
CONSTRAINT `event_impression_ibfk_1` FOREIGN KEY (`experiment_id`, `bucket_label`) REFERENCES `bucket` (`experiment_id`, `label`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `event_impression`
--
LOCK TABLES `event_impression` WRITE;
/*!40000 ALTER TABLE `event_impression` DISABLE KEYS */;
/*!40000 ALTER TABLE `event_impression` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `experiment`
--
DROP TABLE IF EXISTS `experiment`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `experiment` (
`id` varbinary(16) NOT NULL,
`version` int(11) NOT NULL DEFAULT '0',
`creation_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`modification_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`description` varchar(256) COLLATE utf8_bin NOT NULL DEFAULT '',
`sampling_percent` double NOT NULL,
`start_time` datetime NOT NULL,
`end_time` datetime NOT NULL,
`state` varchar(16) COLLATE utf8_bin NOT NULL DEFAULT 'DRAFT',
`label` varchar(64) COLLATE utf8_bin NOT NULL,
`app_name` varchar(64) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `experiment_unique` (`app_name`,`label`,`version`),
KEY `start_time` (`start_time`),
KEY `end_time` (`end_time`),
KEY `state` (`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `experiment`
--
LOCK TABLES `experiment` WRITE;
/*!40000 ALTER TABLE `experiment` DISABLE KEYS */;
/*!40000 ALTER TABLE `experiment` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`wasabi`@`%`*/ /*!50003 TRIGGER on_update_experiment BEFORE UPDATE ON experiment
for each row begin
if NEW.state = 'DELETED' and OLD.state != 'DELETED' and OLD.version = 0 then
set NEW.version = (SELECT MAX(version)+1 FROM experiment
WHERE app_name=NEW.app_name AND label=NEW.label);
end if;
end */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`wasabi`@`%`*/ /*!50003 TRIGGER on_delete_experiment BEFORE DELETE ON experiment
for each row begin
DELETE FROM experiment_rollup WHERE experiment_id=OLD.id;
DELETE FROM event_impression WHERE experiment_id=OLD.id;
DELETE FROM event_action WHERE experiment_id=OLD.id;
DELETE FROM bucket WHERE experiment_id=OLD.id;
end */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `experiment_rollup`
--
DROP TABLE IF EXISTS `experiment_rollup`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `experiment_rollup` (
`experiment_id` varbinary(16) NOT NULL,
`day` date NOT NULL,
`cumulative` tinyint(1) NOT NULL,
`bucket_label` varchar(64) COLLATE utf8_bin NOT NULL,
`action` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`impression_count` int(11) DEFAULT NULL,
`impression_user_count` int(11) DEFAULT NULL,
`action_count` int(11) DEFAULT NULL,
`action_user_count` int(11) DEFAULT NULL,
`context` varchar(200) CHARACTER SET utf8 DEFAULT 'PROD',
UNIQUE KEY `entry` (`experiment_id`,`day`,`cumulative`,`bucket_label`,`action`,`context`),
KEY `experiment_rollup_ibfk_1` (`experiment_id`,`bucket_label`),
CONSTRAINT `experiment_rollup_ibfk_1` FOREIGN KEY (`experiment_id`, `bucket_label`) REFERENCES `bucket` (`experiment_id`, `label`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `experiment_rollup`
--
LOCK TABLES `experiment_rollup` WRITE;
/*!40000 ALTER TABLE `experiment_rollup` DISABLE KEYS */;
/*!40000 ALTER TABLE `experiment_rollup` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `schema_version`
--
DROP TABLE IF EXISTS `schema_version`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `schema_version` (
`version_rank` int(11) NOT NULL,
`installed_rank` int(11) NOT NULL,
`version` varchar(50) NOT NULL,
`description` varchar(200) NOT NULL,
`type` varchar(20) NOT NULL,
`script` varchar(1000) NOT NULL,
`checksum` int(11) DEFAULT NULL,
`installed_by` varchar(100) NOT NULL,
`installed_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`execution_time` int(11) NOT NULL,
`success` tinyint(1) NOT NULL,
PRIMARY KEY (`version`),
KEY `schema_version_vr_idx` (`version_rank`),
KEY `schema_version_ir_idx` (`installed_rank`),
KEY `schema_version_s_idx` (`success`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `schema_version`
--
LOCK TABLES `schema_version` WRITE;
/*!40000 ALTER TABLE `schema_version` DISABLE KEYS */;
INSERT 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);
/*!40000 ALTER TABLE `schema_version` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `user_experiment_properties`
--
DROP TABLE IF EXISTS `user_experiment_properties`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_experiment_properties` (
`user_id` varchar(200) COLLATE utf8_bin NOT NULL,
`experiment_id` varbinary(16) NOT NULL,
`is_favorite` bit(1) DEFAULT b'0',
PRIMARY KEY (`user_id`,`experiment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `user_experiment_properties`
--
LOCK TABLES `user_experiment_properties` WRITE;
/*!40000 ALTER TABLE `user_experiment_properties` DISABLE KEYS */;
/*!40000 ALTER TABLE `user_experiment_properties` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2018-08-02 15:20:31
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment