Build and run master-server as Docker container

2 files changed, 375 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 */;
diff --git a/setup/bwlp-01-testdata.sql b/setup/bwlp-01-testdata.sql
new file mode 100644
index 0000000..f169b47
--- /dev/null
+++ b/setup/bwlp-01-testdata.sql
@@ -0,0 +1,107 @@
+-- phpMyAdmin SQL Dump
+-- version 5.1.0
+-- https://www.phpmyadmin.net/
+--
+-- Host: 192.168.201.20
+-- Generation Time: May 26, 2021 at 02:31 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`
+--
+USE `bwlp`;
+
+--
+-- Dumping data for table `operatingsystem`
+--
+
+INSERT INTO `operatingsystem` (`osid`, `displayname`, `architecture`, `maxmem`, `maxcpu`) VALUES
+(1, 'Windows 7 (64 Bit)', 'AMD64', 196608, 256),
+(2, 'Windows 8 (32 Bit)', 'x86', 4096, 32),
+(3, 'Windows 8 (64 Bit)', 'AMD64', 131072, 256),
+(4, 'Ubuntu (32 Bit)', 'x86', 0, 0),
+(5, 'Ubuntu (64 Bit)', 'AMD64', 0, 0),
+(6, 'OpenSUSE (32 Bit)', 'x86', 0, 0),
+(7, 'OpenSUSE (64 Bit)', 'AMD64', 0, 0),
+(8, 'Other Linux (32 Bit)', 'x86', 0, 0),
+(9, 'Other Linux (64 Bit)', 'AMD64', 0, 0),
+(10, 'Windows 7 (32 Bit)', 'x86', 4096, 32),
+(11, 'Windows 2000 Professional', 'x86', 4096, 4),
+(12, 'Windows XP (32 Bit)', 'x86', 4096, 8),
+(13, 'Debian (32Bit)', 'x86', 0, 0),
+(14, 'Debian (64Bit)', 'AMD64', 0, 0),
+(15, 'DOS', 'x86', 32, 1),
+(16, 'Anderes (32 Bit)', 'x86', 0, 0),
+(17, 'Anderes (64 Bit)', 'AMD64', 0, 0),
+(18, 'Windows 10 (64 Bit)', 'AMD64', 131072, 256),
+(19, 'Windows NT 4', 'x86', 4096, 4);
+
+--
+-- Dumping data for table `virtualizer`
+--
+
+INSERT INTO `virtualizer` (`virtid`, `virtname`) VALUES
+('docker', 'Docker'),
+('qemukvm', 'QEMU-KVM'),
+('virtualbox', 'VirtualBox'),
+('vmware', 'VMware');
+
+--
+-- Dumping data for table `os_x_virt`
+--
+
+INSERT INTO `os_x_virt` (`osid`, `virtid`, `virtoskeyword`) VALUES
+(1, 'virtualbox', 'Windows7_64'),
+(1, 'vmware', 'windows7-64'),
+(2, 'virtualbox', 'Windows8'),
+(2, 'vmware', 'windows8'),
+(3, 'virtualbox', 'Windows8_64'),
+(3, 'vmware', 'windows8-64'),
+(4, 'virtualbox', 'Ubuntu'),
+(4, 'vmware', 'ubuntu'),
+(5, 'virtualbox', 'Ubuntu_64'),
+(5, 'vmware', 'ubuntu-64'),
+(6, 'virtualbox', 'OpenSUSE'),
+(6, 'vmware', 'opensuse'),
+(7, 'virtualbox', 'OpenSUSE_64'),
+(7, 'vmware', 'opensuse-64'),
+(8, 'virtualbox', 'Linux'),
+(8, 'vmware', 'other3xlinux'),
+(9, 'virtualbox', 'Linux_64'),
+(9, 'vmware', 'other3xlinux-64'),
+(10, 'virtualbox', 'Windows7'),
+(10, 'vmware', 'windows7'),
+(11, 'virtualbox', 'Windows2000'),
+(11, 'vmware', 'win2000pro'),
+(12, 'virtualbox', 'WindowsXP'),
+(12, 'vmware', 'winxppro'),
+(13, 'virtualbox', 'Debian'),
+(13, 'vmware', 'debian8'),
+(14, 'virtualbox', 'Debian_64'),
+(14, 'vmware', 'debian8-64'),
+(15, 'virtualbox', 'DOS'),
+(15, 'vmware', 'dos'),
+(16, 'virtualbox', 'Other'),
+(16, 'vmware', 'other'),
+(17, 'virtualbox', 'Other_64'),
+(17, 'vmware', 'other-64'),
+(18, 'virtualbox', 'Windows10_64'),
+(18, 'vmware', 'windows9-64'),
+(19, 'virtualbox', 'WindowsNT'),
+(19, 'vmware', 'winnt');
+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 */;