1 files changed, 268 insertions, 0 deletions
diff --git a/setup/bwlp-01-schema.sql b/setup/bwlp-01-schema.sql
new file mode 100644
index 0000000..83c232d
--- /dev/null
+++ b/setup/bwlp-01-schema.sql
@@ -0,0 +1,268 @@
+-- phpMyAdmin SQL Dump
+-- version 5.1.0
+-- https://www.phpmyadmin.net/
+--
+-- Host: 192.168.201.20
+-- Generation Time: May 26, 2021 at 02:30 PM
+-- Server version: 10.5.10-MariaDB-1:10.5.10+maria~focal
+-- PHP Version: 7.4.19
+
+SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
+START TRANSACTION;
+SET time_zone = "+00:00";
+
+
+/*!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 utf8mb4 */;
+
+--
+-- Database: `bwlp`
+--
+CREATE DATABASE IF NOT EXISTS `bwlp` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
+USE `bwlp`;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `imagebase`
+--
+
+CREATE TABLE IF NOT EXISTS `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 DEFAULT NULL,
+  `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`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `imageblock`
+--
+
+CREATE TABLE IF NOT EXISTS `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;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `imageversion`
+--
+
+CREATE TABLE IF NOT EXISTS `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,
+  `isprocessed` tinyint(1) NOT NULL,
+  `mastersha1` binary(20) DEFAULT NULL,
+  `virtualizerconfig` blob DEFAULT NULL 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`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `operatingsystem`
+--
+
+CREATE TABLE IF NOT EXISTS `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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `organization`
+--
+
+CREATE TABLE IF NOT EXISTS `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 DEFAULT NULL,
+  PRIMARY KEY (`organizationid`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `organization_suffix`
+--
+
+CREATE TABLE IF NOT EXISTS `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`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `os_x_virt`
+--
+
+CREATE TABLE IF NOT EXISTS `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`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `satellite`
+--
+
+CREATE TABLE IF NOT EXISTS `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`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `user`
+--
+
+CREATE TABLE IF NOT EXISTS `user` (
+  `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` 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`,`userid`),
+  UNIQUE KEY `shibid` (`shibid`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `virtualizer`
+--
+
+CREATE TABLE IF NOT EXISTS `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;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `websession`
+--
+
+CREATE TABLE IF NOT EXISTS `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;
+
+--
+-- Constraints for dumped tables
+--
+
+--
+-- Constraints for table `imagebase`
+--
+ALTER TABLE `imagebase`
+  ADD CONSTRAINT `imagebase_ibfk_1` FOREIGN KEY (`virtid`) REFERENCES `virtualizer` (`virtid`),
+  ADD CONSTRAINT `imagebase_ibfk_2` FOREIGN KEY (`osid`) REFERENCES `operatingsystem` (`osid`),
+  ADD CONSTRAINT `imagebase_ibfk_3` FOREIGN KEY (`ownerid`) REFERENCES `user` (`userid`),
+  ADD CONSTRAINT `imagebase_ibfk_4` FOREIGN KEY (`updaterid`) REFERENCES `user` (`userid`);
+
+--
+-- Constraints for table `imageblock`
+--
+ALTER TABLE `imageblock`
+  ADD CONSTRAINT `fk_imageblocksha1_1` FOREIGN KEY (`imageversionid`) REFERENCES `imageversion` (`imageversionid`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+--
+-- Constraints for table `imageversion`
+--
+ALTER TABLE `imageversion`
+  ADD CONSTRAINT `imageversion_ibfk_1` FOREIGN KEY (`imagebaseid`) REFERENCES `imagebase` (`imagebaseid`),
+  ADD CONSTRAINT `imageversion_ibfk_2` FOREIGN KEY (`uploaderid`) REFERENCES `user` (`userid`);
+
+--
+-- Constraints for table `organization_suffix`
+--
+ALTER TABLE `organization_suffix`
+  ADD CONSTRAINT `organization_suffix_ibfk_1` FOREIGN KEY (`organizationid`) REFERENCES `organization` (`organizationid`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+--
+-- Constraints for table `os_x_virt`
+--
+ALTER TABLE `os_x_virt`
+  ADD CONSTRAINT `os_x_virt_ibfk_2` FOREIGN KEY (`virtid`) REFERENCES `virtualizer` (`virtid`) ON DELETE CASCADE,
+  ADD CONSTRAINT `os_x_virt_ibfk_3` FOREIGN KEY (`osid`) REFERENCES `operatingsystem` (`osid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `satellite`
+--
+ALTER TABLE `satellite`
+  ADD CONSTRAINT `satellite_ibfk_1` FOREIGN KEY (`organizationid`) REFERENCES `organization` (`organizationid`);
+
+--
+-- Constraints for table `user`
+--
+ALTER TABLE `user`
+  ADD CONSTRAINT `user_ibfk_1` FOREIGN KEY (`organizationid`) REFERENCES `organization` (`organizationid`);
+COMMIT;
+
+/*!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 */;