diff options
Diffstat (limited to 'extras/database.sql')
| -rw-r--r-- | extras/database.sql | 236 |
1 files changed, 188 insertions, 48 deletions
diff --git a/extras/database.sql b/extras/database.sql index aec8e17..54d582b 100644 --- a/extras/database.sql +++ b/extras/database.sql @@ -1,8 +1,8 @@ --- MySQL dump 10.13 Distrib 5.5.40, for debian-linux-gnu (x86_64) +-- MySQL dump 10.13 Distrib 5.5.47, for debian-linux-gnu (i686) -- --- Host: localhost Database: master-test +-- Host: localhost Database: bwlp -- ------------------------------------------------------ --- Server version 5.5.40-0ubuntu0.12.04.1 +-- Server version 5.5.47-0+deb7u1 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; @@ -16,63 +16,154 @@ /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- --- Table structure for table `image` +-- Table structure for table `imagebase` -- -DROP TABLE IF EXISTS `image`; +DROP TABLE IF EXISTS `imagebase`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; -CREATE TABLE `image` ( - `uuid` varchar(36) COLLATE utf8_unicode_ci NOT NULL, - `revision` int(10) unsigned NOT NULL, - `title` varchar(200) COLLATE utf8_unicode_ci NOT NULL, - `path` varchar(255) COLLATE utf8_unicode_ci NOT NULL, - `createtime` int(10) unsigned NOT NULL, - `updatetime` int(10) unsigned NOT NULL, - `ownerid` int(10) unsigned NOT NULL, - `operatingsystem` int(10) unsigned NOT NULL, +CREATE TABLE `imagebase` ( + `imagebaseid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `latestversionid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL, + `displayname` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, + `description` text COLLATE utf8mb4_unicode_ci, + `osid` int(11) DEFAULT NULL, + `virtid` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `createtime` bigint(20) NOT NULL, + `updatetime` bigint(20) NOT NULL, + `ownerid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `updaterid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `istemplate` tinyint(1) NOT NULL, + PRIMARY KEY (`imagebaseid`), + KEY `owner` (`ownerid`), + KEY `fk_imagebase_1_idx` (`osid`), + KEY `fk_imagebase_updater_idx` (`updaterid`), + KEY `fk_imagebase_1_idx1` (`virtid`), + KEY `latestversion_idx` (`latestversionid`), + CONSTRAINT `imagebase_ibfk_1` FOREIGN KEY (`virtid`) REFERENCES `virtualizer` (`virtid`), + CONSTRAINT `imagebase_ibfk_2` FOREIGN KEY (`osid`) REFERENCES `operatingsystem` (`osid`), + CONSTRAINT `imagebase_ibfk_3` FOREIGN KEY (`ownerid`) REFERENCES `user` (`userid`), + CONSTRAINT `imagebase_ibfk_4` FOREIGN KEY (`updaterid`) REFERENCES `user` (`userid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `imageversion` +-- + +DROP TABLE IF EXISTS `imageversion`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `imageversion` ( + `imageversionid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `imagebaseid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL, + `createtime` bigint(20) NOT NULL, + `expiretime` bigint(20) NOT NULL, + `filesize` bigint(20) NOT NULL, + `filepath` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + `uploaderid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, `isvalid` tinyint(1) NOT NULL, - `isdeleted` tinyint(1) unsigned NOT NULL, - `description` text COLLATE utf8_unicode_ci NOT NULL, - `filesize` bigint(20) unsigned NOT NULL, - `missingblocks` text COLLATE utf8_unicode_ci NOT NULL, - PRIMARY KEY (`uuid`,`revision`), - KEY `ownerid` (`ownerid`), - CONSTRAINT `image_ibfk_1` FOREIGN KEY (`ownerid`) REFERENCES `user` (`userid`) ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + `isprocessed` tinyint(1) NOT NULL, + `mastersha1` binary(20) DEFAULT NULL, + `virtualizerconfig` blob COMMENT 'Specific configuration of the virtualizer for this image. For vmware, this is basically a dump of the *.vmx.', + PRIMARY KEY (`imageversionid`), + KEY `version_access` (`imagebaseid`,`isvalid`,`createtime`), + KEY `fk_imageversion_2_idx` (`uploaderid`), + KEY `expire_index` (`expiretime`), + CONSTRAINT `imageversion_ibfk_1` FOREIGN KEY (`imagebaseid`) REFERENCES `imagebase` (`imagebaseid`), + CONSTRAINT `imageversion_ibfk_2` FOREIGN KEY (`uploaderid`) REFERENCES `user` (`userid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- --- Table structure for table `satellite` +-- Table structure for table `operatingsystem` -- -DROP TABLE IF EXISTS `satellite`; +DROP TABLE IF EXISTS `operatingsystem`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; -CREATE TABLE `satellite` ( - `organizationid` varchar(32) NOT NULL, - `address` varchar(64) NOT NULL, - `name` varchar(255) NOT NULL, - `authmethod` varchar(255) NOT NULL, - `publickey` text, +CREATE TABLE `operatingsystem` ( + `osid` int(11) NOT NULL AUTO_INCREMENT, + `displayname` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, + `architecture` varchar(14) COLLATE utf8mb4_unicode_ci NOT NULL, + `maxmem` int(11) NOT NULL DEFAULT '0', + `maxcpu` int(11) NOT NULL DEFAULT '0', + PRIMARY KEY (`osid`) +) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `organization` +-- + +DROP TABLE IF EXISTS `organization`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `organization` ( + `organizationid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + `authmethod` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + `publickey` text COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`organizationid`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- --- Table structure for table `satellite_suffix` +-- Table structure for table `organization_suffix` -- -DROP TABLE IF EXISTS `satellite_suffix`; +DROP TABLE IF EXISTS `organization_suffix`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; -CREATE TABLE `satellite_suffix` ( - `organizationid` varchar(32) NOT NULL, - `suffix` varchar(32) NOT NULL, +CREATE TABLE `organization_suffix` ( + `organizationid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `suffix` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`suffix`), KEY `organizationid` (`organizationid`), - CONSTRAINT `satellite_suffix_ibfk_1` FOREIGN KEY (`organizationid`) REFERENCES `satellite` (`organizationid`) ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; + CONSTRAINT `organization_suffix_ibfk_1` FOREIGN KEY (`organizationid`) REFERENCES `organization` (`organizationid`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `os_x_virt` +-- + +DROP TABLE IF EXISTS `os_x_virt`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `os_x_virt` ( + `osid` int(11) NOT NULL, + `virtid` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL, + `virtoskeyword` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (`osid`,`virtid`), + KEY `virtoskeyword` (`virtoskeyword`), + KEY `virtid` (`virtid`), + CONSTRAINT `os_x_virt_ibfk_2` FOREIGN KEY (`virtid`) REFERENCES `virtualizer` (`virtid`) ON DELETE CASCADE, + CONSTRAINT `os_x_virt_ibfk_3` FOREIGN KEY (`osid`) REFERENCES `operatingsystem` (`osid`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `satellite` +-- + +DROP TABLE IF EXISTS `satellite`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `satellite` ( + `satelliteid` int(11) NOT NULL AUTO_INCREMENT, + `organizationid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `satellitename` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, + `addresses` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL, + `certsha256` binary(32) DEFAULT NULL, + `publickey` text COLLATE utf8mb4_unicode_ci NOT NULL, + `dateline` bigint(20) NOT NULL, + `userid` varchar(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL, + PRIMARY KEY (`satelliteid`), + UNIQUE KEY `organizationid` (`organizationid`,`satellitename`), + KEY `dateline` (`dateline`), + CONSTRAINT `satellite_ibfk_1` FOREIGN KEY (`organizationid`) REFERENCES `organization` (`organizationid`) +) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -83,19 +174,68 @@ DROP TABLE IF EXISTS `user`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `user` ( - `userid` int(10) unsigned NOT NULL AUTO_INCREMENT, - `login` varchar(48) DEFAULT NULL, + `userid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `shibid` varchar(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL, `password` varchar(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, - `organizationid` varchar(32) NOT NULL, - `firstname` varchar(32) NOT NULL, - `lastname` varchar(32) NOT NULL, - `email` varchar(48) NOT NULL, + `organizationid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `firstname` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL, + `lastname` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL, + `email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `fixedname` tinyint(1) NOT NULL, PRIMARY KEY (`userid`), - UNIQUE KEY `username` (`organizationid`,`login`), - CONSTRAINT `user_ibfk_1` FOREIGN KEY (`organizationid`) REFERENCES `satellite` (`organizationid`) ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; + UNIQUE KEY `username` (`organizationid`,`userid`), + UNIQUE KEY `shibid` (`shibid`), + CONSTRAINT `user_ibfk_1` FOREIGN KEY (`organizationid`) REFERENCES `organization` (`organizationid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `virtualizer` +-- + +DROP TABLE IF EXISTS `virtualizer`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `virtualizer` ( + `virtid` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL, + `virtname` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (`virtid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; +-- +-- Table structure for table `websession` +-- + +DROP TABLE IF EXISTS `websession`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `websession` ( + `sid` char(40) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `dateline` bigint(11) NOT NULL, + `data` varchar(3000) COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (`sid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `imageblock` +-- + +CREATE TABLE `imageblock` ( + `imageversionid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `startbyte` bigint(20) NOT NULL, + `blocksize` int(11) NOT NULL, + `blocksha1` binary(20) DEFAULT NULL, + `ismissing` tinyint(1) NOT NULL COMMENT 'true if this block is missing from the file, either because it was not transferred to the server yet, or because it failed an integrity check.', + PRIMARY KEY (`imageversionid`,`startbyte`,`blocksize`), + KEY `checksums` (`blocksha1`,`blocksize`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +ALTER TABLE `imageblock` + ADD CONSTRAINT `fk_imageblocksha1_1` FOREIGN KEY (`imageversionid`) REFERENCES `imageversion` (`imageversionid`) ON DELETE CASCADE ON UPDATE CASCADE; + + /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; @@ -106,4 +246,4 @@ CREATE TABLE `user` ( /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2014-10-20 15:11:09 +-- Dump completed on 2016-04-04 11:35:20 |