(WiP) Global image sync

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