summaryrefslogtreecommitdiffstats
path: root/extras
diff options
context:
space:
mode:
authorSimon Rettberg2016-04-13 18:41:29 +0200
committerSimon Rettberg2016-04-13 18:41:29 +0200
commitf5618c87e63deb99920710787f6dcd34d4b17425 (patch)
treeafc5fb21b632921094d0576f1e57dbcff240ae6d /extras
parentFix getUSerInfo call on Session class (diff)
downloadmasterserver-f5618c87e63deb99920710787f6dcd34d4b17425.tar.gz
masterserver-f5618c87e63deb99920710787f6dcd34d4b17425.tar.xz
masterserver-f5618c87e63deb99920710787f6dcd34d4b17425.zip
(WiP) Global image sync
Diffstat (limited to 'extras')
-rw-r--r--extras/database.sql236
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