From 359ddbec333a470266b4f4788c5d9959b083fa19 Mon Sep 17 00:00:00 2001 From: Manuel Bentele Date: Wed, 26 May 2021 16:47:19 +0200 Subject: Build and run master-server as Docker container --- .env | 34 +++++ config/global.properties.example | 46 ------- config/global.properties.tmpl | 47 +++++++ config/mysql.properties.example | 5 - config/mysql.properties.tmpl | 7 + docker-compose.yml | 97 +++++++++++++ extras/content.sql | 57 -------- extras/database.sql | 249 --------------------------------- pkg/docker/master-server_dockerfile | 60 ++++++++ pom.xml | 62 +++++++++ setup/bwlp-01-schema.sql | 268 ++++++++++++++++++++++++++++++++++++ setup/bwlp-01-testdata.sql | 107 ++++++++++++++ 12 files changed, 682 insertions(+), 357 deletions(-) create mode 100644 .env delete mode 100644 config/global.properties.example create mode 100644 config/global.properties.tmpl delete mode 100644 config/mysql.properties.example create mode 100644 config/mysql.properties.tmpl create mode 100644 docker-compose.yml delete mode 100644 extras/content.sql delete mode 100644 extras/database.sql create mode 100644 pkg/docker/master-server_dockerfile create mode 100644 setup/bwlp-01-schema.sql create mode 100644 setup/bwlp-01-testdata.sql diff --git a/.env b/.env new file mode 100644 index 0000000..8b74e32 --- /dev/null +++ b/.env @@ -0,0 +1,34 @@ +# +# Environemt variables for docker-compose.yml +# + +# network 'master' +MASTER_NETWORK_IPV4=192.168.201.0/24 +MASTER_NETWORK_IPV4_GATEWAY=192.168.201.1 +MASTER_NETWORK_IPV6=fd03:4b1d:5707:c9::/64 +MASTER_NETWORK_IPV6_GATEWAY=fd03:4b1d:5707:c9::1 + +# container 'master-server' +MASTER_SERVER_IPV4=192.168.201.10 +MASTER_SERVER_IPV6=fd03:4b1d:5707:c9::a +MASTER_SERVER_STORAGE=/mnt/bwLehrpool +MASTER_KEYSTORE_FILE=master-server.jks +MASTER_KEYSTORE_ALIAS=master +MASTER_KEYSTORE_KEY_SIZE=2048 +MASTER_KEYSTORE_PASSWORD=master +MASTER_FILE_TRANSFER_PORT_PLAIN=9050 +MASTER_FILE_TRANSFER_PORT_SSL=9051 +MASTER_THRIFT_PORT_PLAIN=9090 +MASTER_THRIFT_PORT_SSL=9091 + +# container 'master-database' +MASTER_DATABASE_IPV4=192.168.201.20 +MASTER_DATABASE_IPV6=fd03:4b1d:5707:c9::14 +MASTER_DATABASE_USER=root +MASTER_DATABASE_PASSWORD=master +MASTER_DATABASE_NAME=bwlp +MASTER_DATABASE_DATASET_PATH=setup + +# container 'master-phpmyadmin' +MASTER_PHPMYADMIN_IPV4=192.168.201.30 +MASTER_PHPMYADMIN_IPV6=fd03:4b1d:5707:c9::1e diff --git a/config/global.properties.example b/config/global.properties.example deleted file mode 100644 index 1c0c8eb..0000000 --- a/config/global.properties.example +++ /dev/null @@ -1,46 +0,0 @@ -#fill in properties and rename to global.properties -##################### -# Main # -##################### - -# the dir where the images are stored -storage.dir=/mnt/store/images - -##################### -# Session # -##################### - -# session timeout for users (in seconds) -session.user.timeout=7200 - -# session timeout for servers (in seconds) -session.server.timeout=86400 - -##################### -# SSL # -##################### - -# keystore (.jks format) -ssl.keystore.file=./config/masterserver.jks - -# keystore alias -ssl.keystore.alias=alias_in_keystore - -# keystore password -ssl.keystore.password=password_for_keystore - -############ File transfer - -# port that is used for data connection between satellite and master -filetransfer.port.ssl=9051 -filetransfer.port.plain=9050 - -# timeout for kicking idling clients (in seconds) -filetransfer.timeout=60 -filetransfer.retries=4 - -################## Thrift (RPC) - -thrift.port.plain = 9090 -thrift.port.ssl = 9091 - diff --git a/config/global.properties.tmpl b/config/global.properties.tmpl new file mode 100644 index 0000000..546c091 --- /dev/null +++ b/config/global.properties.tmpl @@ -0,0 +1,47 @@ +# +# Autogenerated configuration. Do not edit! +# + +#------------------------------------------------------------------------------ +# Main +#------------------------------------------------------------------------------ +# the dir where the images are stored +storage.dir = {{ .Env.MASTER_VSTORE_PATH }} + +#------------------------------------------------------------------------------ +# Session +#------------------------------------------------------------------------------ +# session timeout for users (in seconds) +session.user.timeout = {{ .Env.MASTER_SESSION_TIMEOUT_USER }} + +# session timeout for servers (in seconds) +session.server.timeout = {{ .Env.MASTER_SESSION_TIMEOUT_SERVER }} + +#------------------------------------------------------------------------------ +# SSL +#------------------------------------------------------------------------------ +# keystore (.jks format) +ssl.keystore.file = {{ .Env.MASTER_KEYSTORE_FILE }} + +# keystore alias +ssl.keystore.alias = {{ .Env.MASTER_KEYSTORE_ALIAS }} + +# keystore password +ssl.keystore.password = {{ .Env.MASTER_KEYSTORE_PASSWORD }} + +#------------------------------------------------------------------------------ +# File transfer +#------------------------------------------------------------------------------ +# port that is used for data connection between satellite and master +filetransfer.port.ssl = {{ .Env.MASTER_FILE_TRANSFER_PORT_SSL }} +filetransfer.port.plain = {{ .Env.MASTER_FILE_TRANSFER_PORT_PLAIN }} + +# timeout for kicking idling clients (in seconds) +filetransfer.timeout = {{ .Env.MASTER_FILE_TRANSFER_TIMEOUT }} +filetransfer.retries = {{ .Env.MASTER_FILE_TRANSFER_RETRIES }} + +#------------------------------------------------------------------------------ +# Thrift (RPC) +#------------------------------------------------------------------------------ +thrift.port.plain = {{ .Env.MASTER_THRIFT_PORT_PLAIN }} +thrift.port.ssl = {{ .Env.MASTER_THRIFT_PORT_SSL }} diff --git a/config/mysql.properties.example b/config/mysql.properties.example deleted file mode 100644 index 3de7085..0000000 --- a/config/mysql.properties.example +++ /dev/null @@ -1,5 +0,0 @@ -# Fill in credentials and rename to mysql.properties -host=localhost -db=masterserver -user=masterserver -password=geheim diff --git a/config/mysql.properties.tmpl b/config/mysql.properties.tmpl new file mode 100644 index 0000000..6581ebc --- /dev/null +++ b/config/mysql.properties.tmpl @@ -0,0 +1,7 @@ +# +# Autogenerated configuration. Do not edit! +# +host = {{ .Env.DOZMOD_DATABASE_HOST }} +db = {{ .Env.DOZMOD_DATABASE_NAME }} +user = {{ .Env.DOZMOD_DATABASE_USER }} +password = {{ .Env.DOZMOD_DATABASE_PASSWORD }} diff --git a/docker-compose.yml b/docker-compose.yml new file mode 100644 index 0000000..add1cff --- /dev/null +++ b/docker-compose.yml @@ -0,0 +1,97 @@ +version: "3" +services: + master-server: + container_name: master-server + image: master-server:latest + hostname: server.master + build: + context: . + dockerfile: pkg/docker/master-server_dockerfile + args: + MASTER_SERVER_FILE: target/image-master-1.0-SNAPSHOT-jar-with-dependencies.jar + MASTER_CONFIG_FILE_GLOBAL: config/global.properties.tmpl + MASTER_CONFIG_FILE_DATABASE: config/mysql.properties.tmpl + MASTER_VSTORE_PATH: ${MASTER_SERVER_STORAGE} + MASTER_KEYSTORE_FILE: ${MASTER_KEYSTORE_FILE} + MASTER_KEYSTORE_ALIAS: ${MASTER_KEYSTORE_ALIAS} + MASTER_KEYSTORE_KEY_SIZE: ${MASTER_KEYSTORE_KEY_SIZE} + MASTER_KEYSTORE_PASSWORD: ${MASTER_KEYSTORE_PASSWORD} + MASTER_FILE_TRANSFER_PORT_PLAIN: ${MASTER_FILE_TRANSFER_PORT_PLAIN} + MASTER_FILE_TRANSFER_PORT_SSL: ${MASTER_FILE_TRANSFER_PORT_SSL} + MASTER_THRIFT_PORT_PLAIN: ${MASTER_THRIFT_PORT_PLAIN} + MASTER_THRIFT_PORT_SSL: ${MASTER_THRIFT_PORT_SSL} + environment: + MASTER_DATABASE_NAME: ${MASTER_DATABASE_NAME} + MASTER_DATABASE_HOST: ${MASTER_DATABASE_IPV4} + MASTER_DATABASE_USER: ${MASTER_DATABASE_USER} + MASTER_DATABASE_PASSWORD: ${MASTER_DATABASE_PASSWORD} + MASTER_VSTORE_PATH: ${MASTER_SERVER_STORAGE} + MASTER_SESSION_TIMEOUT_USER: 7200 + MASTER_SESSION_TIMEOUT_SERVER: 86400 + MASTER_KEYSTORE_FILE: ${MASTER_KEYSTORE_FILE} + MASTER_KEYSTORE_ALIAS: ${MASTER_KEYSTORE_ALIAS} + MASTER_KEYSTORE_PASSWORD: ${MASTER_KEYSTORE_PASSWORD} + MASTER_FILE_TRANSFER_PORT_PLAIN: ${MASTER_FILE_TRANSFER_PORT_PLAIN} + MASTER_FILE_TRANSFER_PORT_SSL: ${MASTER_FILE_TRANSFER_PORT_SSL} + MASTER_FILE_TRANSFER_TIMEOUT: 60 + MASTER_FILE_TRANSFER_RETRIES: 4 + MASTER_THRIFT_PORT_PLAIN: ${MASTER_THRIFT_PORT_PLAIN} + MASTER_THRIFT_PORT_SSL: ${MASTER_THRIFT_PORT_SSL} + volumes: + - master-server:${MASTER_SERVER_STORAGE} + depends_on: + - master-database + networks: + master: + ipv4_address: ${MASTER_SERVER_IPV4} + ipv6_address: ${MASTER_SERVER_IPV6} + master-database: + container_name: master-database + image: mariadb:latest + restart: on-failure + hostname: database.master + environment: + TZ: Europe/Berlin + MYSQL_DATABASE: ${MASTER_DATABASE_NAME} + MYSQL_ROOT_PASSWORD: ${MASTER_DATABASE_PASSWORD} + volumes: + - master-database:/var/lib/mysql + - ./${MASTER_DATABASE_DATASET_PATH}:/docker-entrypoint-initdb.d + networks: + master: + ipv4_address: ${MASTER_DATABASE_IPV4} + ipv6_address: ${MASTER_DATABASE_IPV6} + master-phpmyadmin: + container_name: master-phpmyadmin + image: phpmyadmin:latest + hostname: phpmyadmin.master + restart: on-failure + environment: + PMA_HOST: ${MASTER_DATABASE_IPV4} + PMA_VERBOSE: database.master + MYSQL_ROOT_PASSWORD: ${MASTER_DATABASE_PASSWORD} + depends_on: + - master-database + ports: + - 8080:80 + networks: + master: + ipv4_address: ${MASTER_PHPMYADMIN_IPV4} + ipv6_address: ${MASTER_PHPMYADMIN_IPV6} +volumes: + master-server: + name: master-server + master-database: + name: master-database +networks: + master: + name: master + driver: bridge + enable_ipv6: true + ipam: + driver: default + config: + - subnet: ${MASTER_NETWORK_IPV4} + gateway: ${MASTER_NETWORK_IPV4_GATEWAY} + - subnet: ${MASTER_NETWORK_IPV6} + gateway: ${MASTER_NETWORK_IPV6_GATEWAY} diff --git a/extras/content.sql b/extras/content.sql deleted file mode 100644 index 40db734..0000000 --- a/extras/content.sql +++ /dev/null @@ -1,57 +0,0 @@ --- MySQL dump 10.16 Distrib 10.1.35-MariaDB, for debian-linux-gnu (i686) --- --- Host: localhost Database: bwlp --- ------------------------------------------------------ --- Server version 10.1.35-MariaDB-1 - -/*!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 utf8 */; -/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; -/*!40103 SET TIME_ZONE='+00:00' */; -/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; -/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; -/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; -/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; - --- --- Dumping data for table `os_x_virt` --- - -LOCK TABLES `os_x_virt` WRITE; -/*!40000 ALTER TABLE `os_x_virt` DISABLE KEYS */; -INSERT INTO `os_x_virt` VALUES (13,'virtualbox','Debian'),(14,'virtualbox','Debian_64'),(13,'vmware','debian8'),(14,'vmware','debian8-64'),(15,'virtualbox','DOS'),(15,'vmware','dos'),(8,'virtualbox','Linux'),(9,'virtualbox','Linux_64'),(6,'virtualbox','OpenSUSE'),(6,'vmware','opensuse'),(7,'virtualbox','OpenSUSE_64'),(7,'vmware','opensuse-64'),(16,'virtualbox','Other'),(16,'vmware','other'),(17,'virtualbox','Other_64'),(17,'vmware','other-64'),(8,'vmware','other3xlinux'),(9,'vmware','other3xlinux-64'),(4,'virtualbox','Ubuntu'),(4,'vmware','ubuntu'),(5,'virtualbox','Ubuntu_64'),(5,'vmware','ubuntu-64'),(11,'vmware','win2000pro'),(18,'virtualbox','Windows10_64'),(11,'virtualbox','Windows2000'),(10,'virtualbox','Windows7'),(10,'vmware','windows7'),(1,'virtualbox','Windows7_64'),(1,'vmware','windows7-64'),(2,'virtualbox','Windows8'),(2,'vmware','windows8'),(3,'virtualbox','Windows8_64'),(3,'vmware','windows8-64'),(18,'vmware','windows9-64'),(19,'virtualbox','WindowsNT'),(12,'virtualbox','WindowsXP'),(19,'vmware','winnt'),(12,'vmware','winxppro'); -/*!40000 ALTER TABLE `os_x_virt` ENABLE KEYS */; -UNLOCK TABLES; - --- --- Dumping data for table `virtualizer` --- - -LOCK TABLES `virtualizer` WRITE; -/*!40000 ALTER TABLE `virtualizer` DISABLE KEYS */; -INSERT INTO `virtualizer` VALUES ('qemukvm','QEMU-KVM'),('virtualbox','VirtualBox'),('vmware','VMware'); -/*!40000 ALTER TABLE `virtualizer` ENABLE KEYS */; -UNLOCK TABLES; - --- --- Dumping data for table `operatingsystem` --- - -LOCK TABLES `operatingsystem` WRITE; -/*!40000 ALTER TABLE `operatingsystem` DISABLE KEYS */; -INSERT INTO `operatingsystem` 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); -/*!40000 ALTER TABLE `operatingsystem` ENABLE KEYS */; -UNLOCK TABLES; -/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; - -/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; -/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; -/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; -/*!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 */; -/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; - --- Dump completed on 2018-10-16 11:55:16 diff --git a/extras/database.sql b/extras/database.sql deleted file mode 100644 index 538de95..0000000 --- a/extras/database.sql +++ /dev/null @@ -1,249 +0,0 @@ --- MySQL dump 10.16 Distrib 10.1.35-MariaDB, for debian-linux-gnu (i686) --- --- Host: localhost Database: bwlp --- ------------------------------------------------------ --- Server version 10.1.35-MariaDB-1 - -/*!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 utf8 */; -/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; -/*!40103 SET TIME_ZONE='+00:00' */; -/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; -/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; -/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; -/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; - --- --- Table structure for table `imagebase` --- - -DROP TABLE IF EXISTS `imagebase`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -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 `imageblock` --- - -DROP TABLE IF EXISTS `imageblock`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -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`), - CONSTRAINT `fk_imageblocksha1_1` FOREIGN KEY (`imageversionid`) REFERENCES `imageversion` (`imageversionid`) 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 `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, - `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 `operatingsystem` --- - -DROP TABLE IF EXISTS `operatingsystem`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -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 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=utf8mb4 COLLATE=utf8mb4_unicode_ci; -/*!40101 SET character_set_client = @saved_cs_client */; - --- --- Table structure for table `organization_suffix` --- - -DROP TABLE IF EXISTS `organization_suffix`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -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 `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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -/*!40101 SET character_set_client = @saved_cs_client */; - --- --- Table structure for table `user` --- - -DROP TABLE IF EXISTS `user`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `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`), - 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 */; -/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; - -/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; -/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; -/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; -/*!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 */; -/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; - --- Dump completed on 2018-10-15 13:36:27 diff --git a/pkg/docker/master-server_dockerfile b/pkg/docker/master-server_dockerfile new file mode 100644 index 0000000..5e8c519 --- /dev/null +++ b/pkg/docker/master-server_dockerfile @@ -0,0 +1,60 @@ +FROM ubuntu:focal + +ARG MASTER_SERVER_FILE +ARG MASTER_CONFIG_FILE_GLOBAL +ARG MASTER_CONFIG_FILE_DATABASE +ARG MASTER_KEYSTORE_FILE +ARG MASTER_KEYSTORE_ALIAS +ARG MASTER_KEYSTORE_KEY_SIZE +ARG MASTER_KEYSTORE_PASSWORD + +ARG MASTER_SERVER_PATH=/opt/bwLehrpool +ARG MASTER_VSTORE_PATH=/mnt/bwLehrpool +ARG MASTER_FILE_TRANSFER_PORT_PLAIN=9050 +ARG MASTER_FILE_TRANSFER_PORT_SSL=9051 +ARG MASTER_THRIFT_PORT_PLAIN=9090 +ARG MASTER_THRIFT_PORT_SSL=9091 +ARG MASTER_KEYSTORE_DC=master +ARG MASTER_KEYSTORE_OU=bwLehrpool +ARG MASTER_KEYSTORE_CN=master-server +ENV MASTER_DATABASE_HOST=localhost +ENV MASTER_DATABASE_PORT=3306 +ENV MASTER_DATABASE_CONNECTION_WAIT_TIMEOUT=1m + +# install required packages +RUN apt-get update -y && \ + apt-get install -y openjdk-8-jre-headless wget + +# make folder for master Java server application and VMs +RUN mkdir -p ${MASTER_SERVER_PATH} +RUN mkdir -p ${MASTER_VSTORE_PATH} + +# change working directory +WORKDIR ${MASTER_SERVER_PATH} + +# install dockerize +ENV DOCKERIZE_VERSION=v0.6.1 +RUN wget -q https://github.com/jwilder/dockerize/releases/download/${DOCKERIZE_VERSION}/dockerize-linux-amd64-${DOCKERIZE_VERSION}.tar.gz && \ + tar -C /usr/local/bin -xzvf dockerize-linux-amd64-${DOCKERIZE_VERSION}.tar.gz && \ + rm dockerize-linux-amd64-${DOCKERIZE_VERSION}.tar.gz + +# copy application into the image +COPY ${MASTER_SERVER_FILE} master-server.jar + +# create config directory +RUN mkdir -p config + +# copy global configuration template into the image +COPY ${MASTER_CONFIG_FILE_GLOBAL} config/global.properties.tmpl + +# copy local configuration template into the image +COPY ${MASTER_CONFIG_FILE_DATABASE} config/mysql.properties.tmpl + +# generate Java keystore +RUN keytool -genkey -alias ${MASTER_KEYSTORE_ALIAS} -keyalg RSA -storetype PKCS12 -keystore ${MASTER_KEYSTORE_FILE} -storepass ${MASTER_KEYSTORE_PASSWORD} -keysize ${MASTER_KEYSTORE_KEY_SIZE} -dname "cn=${MASTER_KEYSTORE_CN},ou=${MASTER_KEYSTORE_OU},dc=${MASTER_KEYSTORE_DC}" + +# expose network ports +EXPOSE ${MASTER_FILE_TRANSFER_PORT_PLAIN} ${MASTER_FILE_TRANSFER_PORT_SSL} ${MASTER_THRIFT_PORT_PLAIN} ${MASTER_THRIFT_PORT_SSL} + +# finalize configuration from environment variables and run the master-server +CMD dockerize -template config/global.properties.tmpl:config/global.properties -template config/mysql.properties.tmpl:config/mysql.properties -wait tcp://${MASTER_DATABASE_HOST}:${MASTER_DATABASE_PORT} -timeout ${MASTER_DATABASE_CONNECTION_WAIT_TIMEOUT} java -jar master-server.jar diff --git a/pom.xml b/pom.xml index 935611b..330d05b 100644 --- a/pom.xml +++ b/pom.xml @@ -14,6 +14,68 @@ UTF-8 + + + dozmod-server:start + + pre-integration-test + + + org.codehaus.mojo + exec-maven-plugin + 3.0.0 + + + docker-compose:up + pre-integration-test + + exec + + + + + docker-compose + + up + --build + --force-recreate + --detach + + + + + + + + dozmod-server:stop + + validate + + + org.codehaus.mojo + exec-maven-plugin + 3.0.0 + + + docker-compose:down + validate + + exec + + + + + docker-compose + + down + + + + + + + + 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 */; -- cgit v1.2.3-55-g7522