From 38e75c552ce440be0ea25c8cedec3b6a30c28a25 Mon Sep 17 00:00:00 2001 From: Manuel Bentele Date: Thu, 20 May 2021 13:27:02 +0200 Subject: [server] Update initial database schema and test dataset --- dozentenmodulserver/setup/sat-01-schema.sql | 548 ++++++++++++++++++++++---- dozentenmodulserver/setup/sat-01-testdata.sql | 120 +++++- 2 files changed, 563 insertions(+), 105 deletions(-) (limited to 'dozentenmodulserver') diff --git a/dozentenmodulserver/setup/sat-01-schema.sql b/dozentenmodulserver/setup/sat-01-schema.sql index 2f60b227..aa99a03c 100644 --- a/dozentenmodulserver/setup/sat-01-schema.sql +++ b/dozentenmodulserver/setup/sat-01-schema.sql @@ -1,26 +1,76 @@ +-- phpMyAdmin SQL Dump +-- version 5.1.0 +-- https://www.phpmyadmin.net/ +-- +-- Host: 192.168.200.20 +-- Generation Time: May 20, 2021 at 09:55 AM +-- 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 utf8 */; +/*!40101 SET NAMES utf8mb4 */; -CREATE DATABASE IF NOT EXISTS `sat` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; +-- +-- Database: `sat` +-- +CREATE DATABASE IF NOT EXISTS `sat` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE `sat`; +-- -------------------------------------------------------- + +-- +-- Table structure for table `actionlog` +-- + +CREATE TABLE IF NOT EXISTS `actionlog` ( + `actionid` int(11) NOT NULL AUTO_INCREMENT, + `dateline` bigint(20) NOT NULL, + `userid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL, + `targetid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL, + `description` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (`actionid`), + KEY `userid` (`userid`,`dateline`), + KEY `targetid` (`targetid`,`dateline`), + KEY `dateline` (`dateline`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- -------------------------------------------------------- + +-- +-- Table structure for table `configuration` +-- + +CREATE TABLE IF NOT EXISTS `configuration` ( + `parameter` varchar(100) CHARACTER SET ascii NOT NULL, + `value` blob NOT NULL, + PRIMARY KEY (`parameter`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- -------------------------------------------------------- + +-- +-- 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) NOT NULL, - `description` TEXT NULL DEFAULT NULL, - `osid` INT NULL DEFAULT NULL, - `virtid` VARCHAR(10) NULL 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, - `sharemode` enum('LOCAL','PUBLISH','DOWNLOAD', 'FROZEN') NOT NULL, + `sharemode` enum('LOCAL','PUBLISH','DOWNLOAD','FROZEN') COLLATE utf8mb4_unicode_ci NOT NULL, `istemplate` tinyint(1) NOT NULL, `canlinkdefault` tinyint(1) NOT NULL, `candownloaddefault` tinyint(1) NOT NULL, @@ -34,12 +84,11 @@ CREATE TABLE IF NOT EXISTS `imagebase` ( KEY `latestversion_idx` (`latestversionid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE IF NOT EXISTS `imagetag` ( - `imagebaseid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, - `tagname` CHAR(32) NOT NULL, - PRIMARY KEY (`imagebaseid`,`tagname`), - KEY `tag_image` (`tagname`,`imagebaseid`) -) 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, @@ -51,6 +100,12 @@ CREATE TABLE IF NOT EXISTS `imageblock` ( KEY `checksums` (`blocksha1`,`blocksize`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +-- -------------------------------------------------------- + +-- +-- Table structure for table `imagepermission` +-- + CREATE TABLE IF NOT EXISTS `imagepermission` ( `imagebaseid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, `userid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, @@ -62,20 +117,39 @@ CREATE TABLE IF NOT EXISTS `imagepermission` ( KEY `fk_imagepermission_2_idx` (`userid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +-- -------------------------------------------------------- + +-- +-- Table structure for table `imagetag` +-- + +CREATE TABLE IF NOT EXISTS `imagetag` ( + `imagebaseid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `tagname` char(32) COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (`imagebaseid`,`tagname`), + KEY `tag_image` (`tagname`,`imagebaseid`) +) 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 NOT NULL, `createtime` bigint(20) NOT NULL, `expiretime` bigint(20) NOT NULL, `filesize` bigint(20) NOT NULL, - `filepath` varchar(200) NOT NULL, + `filepath` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, `uploaderid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, `isrestricted` tinyint(1) NOT NULL, `isvalid` tinyint(1) NOT NULL, `isprocessed` tinyint(1) NOT NULL, `mastersha1` binary(20) DEFAULT NULL, - `virtualizerconfig` blob NULL DEFAULT NULL COMMENT 'Specific configuration of the virtualizer for this image. For vmware, this is basically a dump of the *.vmx.', - `deletestate` ENUM( 'KEEP', 'SHOULD_DELETE', 'WANT_DELETE' ) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'KEEP', + `virtualizerconfig` blob DEFAULT NULL COMMENT 'Specific configuration of the virtualizer for this image. For vmware, this is basically a dump of the *.vmx.', + `deletestate` enum('KEEP','SHOULD_DELETE','WANT_DELETE') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'KEEP', PRIMARY KEY (`imageversionid`), KEY `version_access` (`imagebaseid`,`isvalid`,`createtime`), KEY `fk_imageversion_2_idx` (`uploaderid`), @@ -83,6 +157,12 @@ CREATE TABLE IF NOT EXISTS `imageversion` ( KEY `deletestate` (`deletestate`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +-- -------------------------------------------------------- + +-- +-- Table structure for table `imageversion_x_software` +-- + CREATE TABLE IF NOT EXISTS `imageversion_x_software` ( `imageversionid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, `softwareid` int(11) NOT NULL, @@ -90,34 +170,34 @@ CREATE TABLE IF NOT EXISTS `imageversion_x_software` ( KEY `fk_imageversion_x_software_2_idx` (`softwareid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE IF NOT EXISTS `software_x_tag` ( - `softwareid` int(11) NOT NULL, - `tagname` CHAR(32) NOT NULL, - PRIMARY KEY (`softwareid`,`tagname`), - KEY `fk_software_x_tag_2_idx` (`tagname`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +-- -------------------------------------------------------- + +-- +-- Table structure for table `lecture` +-- CREATE TABLE IF NOT EXISTS `lecture` ( `lectureid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, - `displayname` varchar(100) NOT NULL, - `description` text NOT NULL, - `imageversionid` char(36) CHARACTER SET ascii COLLATE ascii_bin NULL COMMENT 'We reference a specific image version here, not the base image.\nOn update of an image, we update the lecture table for all matching lectures that used the current image version.\nThis way, a tutor can explicitly switch back to an older version of an image.', - `autoupdate` tinyint(1) UNSIGNED NOT NULL DEFAULT '1', - `isenabled` tinyint(1) UNSIGNED NOT NULL DEFAULT '1', - `isprivate` tinyint(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Only users from the lectureuser table can start this lecture', + `displayname` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, + `description` text COLLATE utf8mb4_unicode_ci NOT NULL, + `imageversionid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL COMMENT 'We reference a specific image version here, not the base image.\nOn update of an image, we update the lecture table for all matching lectures that used the current image version.\nThis way, a tutor can explicitly switch back to an older version of an image.', + `autoupdate` tinyint(1) UNSIGNED NOT NULL DEFAULT 1, + `isenabled` tinyint(1) UNSIGNED NOT NULL DEFAULT 1, + `isprivate` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Only users from the lectureuser table can start this lecture', + `islocationprivate` tinyint(1) UNSIGNED NOT NULL DEFAULT 0, `starttime` bigint(20) NOT NULL, `endtime` bigint(20) NOT NULL, - `lastused` bigint(20) NOT NULL DEFAULT '0', - `usecount` int(11) NOT NULL DEFAULT '0', + `lastused` bigint(20) NOT NULL DEFAULT 0, + `usecount` int(11) NOT NULL DEFAULT 0, `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, - `runscript` text DEFAULT NULL, - `nics` VARCHAR(200) CHARACTER SET ascii COLLATE ascii_bin NULL COMMENT 'Freeform text field for future extendability. Format is specified at application layer.', - `netrules` text DEFAULT NULL COMMENT 'user defined firewall rules, applied at the linux base system.', + `runscript` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `nics` varchar(200) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL COMMENT 'Freeform text field for future extendability. Format is specified at application layer.', + `netrules` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'user defined firewall rules, applied at the linux base system.', `isexam` tinyint(1) NOT NULL, - `iswhitelistonly` tinyint(1) NOT NULL DEFAULT '0', + `iswhitelistonly` tinyint(1) NOT NULL DEFAULT 0, `hasinternetaccess` tinyint(1) NOT NULL, `hasusbaccess` tinyint(1) NOT NULL, `caneditdefault` tinyint(1) NOT NULL, @@ -129,6 +209,28 @@ CREATE TABLE IF NOT EXISTS `lecture` ( KEY `list_lookup` (`isenabled`,`isexam`,`isprivate`,`endtime`,`starttime`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +-- -------------------------------------------------------- + +-- +-- Table structure for table `lecturefilter` +-- + +CREATE TABLE IF NOT EXISTS `lecturefilter` ( + `lectureid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `filterpresetid` int(11) DEFAULT NULL, + `filtertype` varchar(24) CHARACTER SET ascii DEFAULT NULL, + `filterkey` varchar(24) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `filtervalue` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + KEY `lectureid` (`lectureid`,`filtertype`), + KEY `filterpresetid` (`filterpresetid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- -------------------------------------------------------- + +-- +-- Table structure for table `lecturepermission` +-- + CREATE TABLE IF NOT EXISTS `lecturepermission` ( `lectureid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, `userid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, @@ -138,6 +240,92 @@ CREATE TABLE IF NOT EXISTS `lecturepermission` ( KEY `fk_lecturepermission_2_idx` (`userid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +-- -------------------------------------------------------- + +-- +-- Table structure for table `lectureuser` +-- + +CREATE TABLE IF NOT EXISTS `lectureuser` ( + `lectureid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `userlogin` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (`lectureid`,`userlogin`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- -------------------------------------------------------- + +-- +-- Table structure for table `lecture_x_location` +-- + +CREATE TABLE IF NOT EXISTS `lecture_x_location` ( + `lectureid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `locationid` int(11) NOT NULL, + PRIMARY KEY (`lectureid`,`locationid`), + KEY `locationid` (`locationid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- -------------------------------------------------------- + +-- +-- Table structure for table `lecture_x_networkrule` +-- + +CREATE TABLE IF NOT EXISTS `lecture_x_networkrule` ( + `lectureid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `ruleid` int(11) NOT NULL, + PRIMARY KEY (`lectureid`,`ruleid`), + KEY `ruleid` (`ruleid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- -------------------------------------------------------- + +-- +-- Table structure for table `lecture_x_runscript` +-- + +CREATE TABLE IF NOT EXISTS `lecture_x_runscript` ( + `lectureid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `runscriptid` int(11) NOT NULL, + PRIMARY KEY (`lectureid`,`runscriptid`), + KEY `runscriptid` (`runscriptid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- -------------------------------------------------------- + +-- +-- Table structure for table `mailqueue` +-- + +CREATE TABLE IF NOT EXISTS `mailqueue` ( + `mailid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `userid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `message` text NOT NULL, + `failcount` int(11) NOT NULL DEFAULT 0, + `dateline` bigint(20) NOT NULL, + PRIMARY KEY (`mailid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- -------------------------------------------------------- + +-- +-- Table structure for table `networkshare` +-- + +CREATE TABLE IF NOT EXISTS `networkshare` ( + `lectureid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `sharepresetid` int(11) DEFAULT NULL, + `sharedata` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + KEY `sharepresetid` (`sharepresetid`), + KEY `fk_lectureid_1` (`lectureid`) +) 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 COMMENT 'Defined on the master server, so no auto_increment!', `displayname` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, @@ -147,119 +335,305 @@ CREATE TABLE IF NOT EXISTS `operatingsystem` ( 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, - `displayname` varchar(255) NOT NULL, - `canlogin` tinyint(1) NOT NULL DEFAULT '0', + `displayname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, + `canlogin` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (`organizationid`), KEY `loginkey` (`canlogin`) ) 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) NOT NULL, - `virtoskeyword` varchar(30) 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 `fk_os_x_virt_2_idx` (`virtid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +-- -------------------------------------------------------- + +-- +-- Table structure for table `presetlecturefilter` +-- + +CREATE TABLE IF NOT EXISTS `presetlecturefilter` ( + `filterid` int(11) NOT NULL AUTO_INCREMENT, + `filtertype` varchar(24) CHARACTER SET ascii NOT NULL, + `filtername` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, + `filterkey` varchar(24) COLLATE utf8mb4_unicode_ci NOT NULL, + `filtervalue` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (`filterid`), + KEY `filtertype` (`filtertype`,`filtername`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- -------------------------------------------------------- + +-- +-- Table structure for table `presetnetworkrule` +-- + +CREATE TABLE IF NOT EXISTS `presetnetworkrule` ( + `ruleid` int(11) NOT NULL AUTO_INCREMENT, + `rulename` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, + `ruledata` text COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (`ruleid`), + KEY `rulename` (`rulename`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- -------------------------------------------------------- + +-- +-- Table structure for table `presetnetworkshare` +-- + +CREATE TABLE IF NOT EXISTS `presetnetworkshare` ( + `shareid` int(11) NOT NULL AUTO_INCREMENT, + `sharename` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, + `sharedata` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL, + `active` tinyint(1) NOT NULL DEFAULT 0, + PRIMARY KEY (`shareid`), + KEY `sharename` (`sharename`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- -------------------------------------------------------- + +-- +-- Table structure for table `presetrunscript` +-- + +CREATE TABLE IF NOT EXISTS `presetrunscript` ( + `runscriptid` int(11) NOT NULL AUTO_INCREMENT, + `scriptname` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, + `content` text COLLATE utf8mb4_unicode_ci NOT NULL, + `extension` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL, + `visibility` tinyint(1) NOT NULL COMMENT '0 = hidden, 1 = normal, 2 = minimized', + `passcreds` tinyint(1) NOT NULL, + `isglobal` tinyint(1) NOT NULL COMMENT 'Whether to apply this script to all lectures', + PRIMARY KEY (`runscriptid`), + KEY `isglobal` (`isglobal`), + KEY `scriptname` (`scriptname`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- -------------------------------------------------------- + +-- +-- Table structure for table `presetrunscript_x_operatingsystem` +-- + +CREATE TABLE IF NOT EXISTS `presetrunscript_x_operatingsystem` ( + `runscriptid` int(11) NOT NULL, + `osid` int(11) NOT NULL, + PRIMARY KEY (`runscriptid`,`osid`), + KEY `osid` (`osid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- -------------------------------------------------------- + +-- +-- Table structure for table `software` +-- + CREATE TABLE IF NOT EXISTS `software` ( `softwareid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'This ID is used internally only, it never leaves the satellite.', - `softwarestring` varchar(120) NOT NULL, - `isrestricted` TINYINT(1) NOT NULL, - `isrestrictedoverride` TINYINT(1) NULL, + `softwarestring` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL, + `isrestricted` tinyint(1) NOT NULL, + `isrestrictedoverride` tinyint(1) DEFAULT NULL, PRIMARY KEY (`softwareid`), UNIQUE KEY `softwarestring_UNIQUE` (`softwarestring`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +-- -------------------------------------------------------- + +-- +-- Table structure for table `software_x_tag` +-- + +CREATE TABLE IF NOT EXISTS `software_x_tag` ( + `softwareid` int(11) NOT NULL, + `tagname` char(32) COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (`softwareid`,`tagname`), + KEY `fk_software_x_tag_2_idx` (`tagname`) +) 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, - `firstname` varchar(50) NOT NULL, - `lastname` varchar(50) NOT NULL, - `email` varchar(50) NOT NULL, + `firstname` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, + `lastname` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, + `email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `organizationid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, `lastlogin` bigint(20) DEFAULT NULL, - `canlogin` tinyint(1) NOT NULL DEFAULT '0', - `issuperuser` tinyint(1) NOT NULL DEFAULT '0', - `emailnotifications` tinyint(1) NOT NULL DEFAULT '1', + `canlogin` tinyint(1) NOT NULL DEFAULT 0, + `issuperuser` tinyint(1) NOT NULL DEFAULT 0, + `emailnotifications` tinyint(1) NOT NULL DEFAULT 1, PRIMARY KEY (`userid`), KEY `fk_user_1_idx` (`organizationid`), KEY `mail_idx` (`email`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +-- -------------------------------------------------------- + +-- +-- Table structure for table `virtualizer` +-- + CREATE TABLE IF NOT EXISTS `virtualizer` ( - `virtid` varchar(10) NOT NULL, - `virtname` varchar(20) NOT NULL, + `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; -CREATE TABLE IF NOT EXISTS `lectureuser` ( - `lectureid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, - `userlogin` VARCHAR(45) NOT NULL, - PRIMARY KEY (`lectureid`, `userlogin`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +-- +-- Constraints for dumped tables +-- -CREATE TABLE IF NOT EXISTS `configuration` ( - `parameter` varchar(100) CHARACTER SET ascii NOT NULL, - `value` blob NOT NULL, - PRIMARY KEY (`parameter`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; - -CREATE TABLE IF NOT EXISTS `mailqueue` ( - `mailid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, - `userid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, - `message` text NOT NULL, - `failcount` int(11) NOT NULL DEFAULT '0', - `dateline` bigint(20) NOT NULL, - PRIMARY KEY (`mailid`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; - -ALTER TABLE `lectureuser` - ADD CONSTRAINT `fk_lectureuser_1` FOREIGN KEY (`lectureid`) REFERENCES `sat`.`lecture` (`lectureid`) ON DELETE CASCADE ON UPDATE CASCADE; +-- +-- Constraints for table `actionlog` +-- +ALTER TABLE `actionlog` + ADD CONSTRAINT `actionlog_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `user` (`userid`) ON DELETE SET NULL ON UPDATE CASCADE; +-- +-- Constraints for table `imagebase` +-- ALTER TABLE `imagebase` + ADD CONSTRAINT `fk_imagebase_1` FOREIGN KEY (`virtid`) REFERENCES `virtualizer` (`virtid`) ON UPDATE CASCADE, ADD CONSTRAINT `fk_imagebase_os` FOREIGN KEY (`osid`) REFERENCES `operatingsystem` (`osid`), ADD CONSTRAINT `fk_imagebase_owner` FOREIGN KEY (`ownerid`) REFERENCES `user` (`userid`) ON UPDATE CASCADE, ADD CONSTRAINT `fk_imagebase_updater` FOREIGN KEY (`updaterid`) REFERENCES `user` (`userid`) ON UPDATE CASCADE, - ADD CONSTRAINT `fk_imagebase_1` FOREIGN KEY (`virtid`) REFERENCES `virtualizer` (`virtid`) ON UPDATE CASCADE, ADD CONSTRAINT `latestversion` FOREIGN KEY (`latestversionid`) REFERENCES `imageversion` (`imageversionid`) ON UPDATE CASCADE; -ALTER TABLE `imagetag` - ADD CONSTRAINT `fk_imagetag_1` FOREIGN KEY (`imagebaseid`) REFERENCES `imagebase` (`imagebaseid`) ON UPDATE CASCADE ON DELETE CASCADE; - +-- +-- Constraints for table `imageblock` +-- ALTER TABLE `imageblock` - ADD CONSTRAINT `fk_imageblocksha1_1` FOREIGN KEY (`imageversionid`) REFERENCES `imageversion` (`imageversionid`) ON UPDATE CASCADE ON DELETE CASCADE; + ADD CONSTRAINT `fk_imageblocksha1_1` FOREIGN KEY (`imageversionid`) REFERENCES `imageversion` (`imageversionid`) ON DELETE CASCADE ON UPDATE CASCADE; +-- +-- Constraints for table `imagepermission` +-- ALTER TABLE `imagepermission` - ADD CONSTRAINT `fk_imagepermission_1` FOREIGN KEY (`imagebaseid`) REFERENCES `imagebase` (`imagebaseid`) ON UPDATE CASCADE ON DELETE CASCADE, - ADD CONSTRAINT `fk_imagepermission_2` FOREIGN KEY (`userid`) REFERENCES `user` (`userid`) ON UPDATE CASCADE ON DELETE CASCADE; + ADD CONSTRAINT `fk_imagepermission_1` FOREIGN KEY (`imagebaseid`) REFERENCES `imagebase` (`imagebaseid`) ON DELETE CASCADE ON UPDATE CASCADE, + ADD CONSTRAINT `fk_imagepermission_2` FOREIGN KEY (`userid`) REFERENCES `user` (`userid`) ON DELETE CASCADE ON UPDATE CASCADE; +-- +-- Constraints for table `imagetag` +-- +ALTER TABLE `imagetag` + ADD CONSTRAINT `fk_imagetag_1` FOREIGN KEY (`imagebaseid`) REFERENCES `imagebase` (`imagebaseid`) ON DELETE CASCADE ON UPDATE CASCADE; + +-- +-- Constraints for table `imageversion` +-- ALTER TABLE `imageversion` ADD CONSTRAINT `fk_imageversion_base` FOREIGN KEY (`imagebaseid`) REFERENCES `imagebase` (`imagebaseid`) ON UPDATE CASCADE, ADD CONSTRAINT `fk_imageversion_creator` FOREIGN KEY (`uploaderid`) REFERENCES `user` (`userid`) ON UPDATE CASCADE; +-- +-- Constraints for table `imageversion_x_software` +-- ALTER TABLE `imageversion_x_software` - ADD CONSTRAINT `fk_imageversion_x_software_1` FOREIGN KEY (`imageversionid`) REFERENCES `imageversion` (`imageversionid`) ON UPDATE CASCADE ON DELETE CASCADE, - ADD CONSTRAINT `fk_imageversion_x_software_2` FOREIGN KEY (`softwareid`) REFERENCES `software` (`softwareid`) ON UPDATE CASCADE ON DELETE CASCADE; + ADD CONSTRAINT `fk_imageversion_x_software_1` FOREIGN KEY (`imageversionid`) REFERENCES `imageversion` (`imageversionid`) ON DELETE CASCADE ON UPDATE CASCADE, + ADD CONSTRAINT `fk_imageversion_x_software_2` FOREIGN KEY (`softwareid`) REFERENCES `software` (`softwareid`) ON DELETE CASCADE ON UPDATE CASCADE; +-- +-- Constraints for table `lecture` +-- ALTER TABLE `lecture` ADD CONSTRAINT `fk_lecture_image` FOREIGN KEY (`imageversionid`) REFERENCES `imageversion` (`imageversionid`) ON UPDATE CASCADE, ADD CONSTRAINT `fk_lecture_owner` FOREIGN KEY (`ownerid`) REFERENCES `user` (`userid`) ON UPDATE CASCADE, ADD CONSTRAINT `fk_lecture_updater` FOREIGN KEY (`updaterid`) REFERENCES `user` (`userid`) ON UPDATE CASCADE; +-- +-- Constraints for table `lecturefilter` +-- +ALTER TABLE `lecturefilter` + ADD CONSTRAINT `filterpresetid` FOREIGN KEY (`filterpresetid`) REFERENCES `presetlecturefilter` (`filterid`) ON DELETE CASCADE ON UPDATE CASCADE, + ADD CONSTRAINT `lectureid` FOREIGN KEY (`lectureid`) REFERENCES `lecture` (`lectureid`) ON DELETE CASCADE ON UPDATE CASCADE; + +-- +-- Constraints for table `lecturepermission` +-- ALTER TABLE `lecturepermission` - ADD CONSTRAINT `fk_lecturepermission_1` FOREIGN KEY (`lectureid`) REFERENCES `lecture` (`lectureid`) ON UPDATE CASCADE ON DELETE CASCADE, - ADD CONSTRAINT `fk_lecturepermission_2` FOREIGN KEY (`userid`) REFERENCES `user` (`userid`) ON UPDATE CASCADE ON DELETE CASCADE; + ADD CONSTRAINT `fk_lecturepermission_1` FOREIGN KEY (`lectureid`) REFERENCES `lecture` (`lectureid`) ON DELETE CASCADE ON UPDATE CASCADE, + ADD CONSTRAINT `fk_lecturepermission_2` FOREIGN KEY (`userid`) REFERENCES `user` (`userid`) ON DELETE CASCADE ON UPDATE CASCADE; +-- +-- Constraints for table `lectureuser` +-- +ALTER TABLE `lectureuser` + ADD CONSTRAINT `fk_lectureuser_1` FOREIGN KEY (`lectureid`) REFERENCES `lecture` (`lectureid`) ON DELETE CASCADE ON UPDATE CASCADE; + +-- +-- Constraints for table `lecture_x_location` +-- +ALTER TABLE `lecture_x_location` + ADD CONSTRAINT `lecture_x_location_ibfk_1` FOREIGN KEY (`lectureid`) REFERENCES `lecture` (`lectureid`) ON DELETE CASCADE ON UPDATE CASCADE; + +-- +-- Constraints for table `lecture_x_networkrule` +-- +ALTER TABLE `lecture_x_networkrule` + ADD CONSTRAINT `lecture_x_networkrule_ibfk_1` FOREIGN KEY (`lectureid`) REFERENCES `lecture` (`lectureid`) ON DELETE CASCADE ON UPDATE CASCADE, + ADD CONSTRAINT `lecture_x_networkrule_ibfk_2` FOREIGN KEY (`ruleid`) REFERENCES `presetnetworkrule` (`ruleid`) ON DELETE CASCADE ON UPDATE CASCADE; + +-- +-- Constraints for table `lecture_x_runscript` +-- +ALTER TABLE `lecture_x_runscript` + ADD CONSTRAINT `lecture_runscript` FOREIGN KEY (`lectureid`) REFERENCES `lecture` (`lectureid`) ON DELETE CASCADE ON UPDATE CASCADE, + ADD CONSTRAINT `lecture_x_runscript_ibfk_1` FOREIGN KEY (`runscriptid`) REFERENCES `presetrunscript` (`runscriptid`) ON DELETE CASCADE ON UPDATE CASCADE; + +-- +-- Constraints for table `networkshare` +-- +ALTER TABLE `networkshare` + ADD CONSTRAINT `fk_lectureid_1` FOREIGN KEY (`lectureid`) REFERENCES `lecture` (`lectureid`) ON DELETE CASCADE ON UPDATE CASCADE; + +-- +-- Constraints for table `os_x_virt` +-- ALTER TABLE `os_x_virt` - ADD CONSTRAINT `fk_os_x_virt_1` FOREIGN KEY (`osid`) REFERENCES `operatingsystem` (`osid`) ON UPDATE CASCADE ON DELETE CASCADE, - ADD CONSTRAINT `fk_os_x_virt_2` FOREIGN KEY (`virtid`) REFERENCES `virtualizer` (`virtid`) ON UPDATE CASCADE ON DELETE CASCADE; + ADD CONSTRAINT `fk_os_x_virt_1` FOREIGN KEY (`osid`) REFERENCES `operatingsystem` (`osid`) ON DELETE CASCADE ON UPDATE CASCADE, + ADD CONSTRAINT `fk_os_x_virt_2` FOREIGN KEY (`virtid`) REFERENCES `virtualizer` (`virtid`) ON DELETE CASCADE ON UPDATE CASCADE; + +-- +-- Constraints for table `presetrunscript_x_operatingsystem` +-- +ALTER TABLE `presetrunscript_x_operatingsystem` + ADD CONSTRAINT `osid` FOREIGN KEY (`osid`) REFERENCES `operatingsystem` (`osid`) ON DELETE CASCADE ON UPDATE CASCADE, + ADD CONSTRAINT `runscriptid` FOREIGN KEY (`runscriptid`) REFERENCES `presetrunscript` (`runscriptid`) ON DELETE CASCADE ON UPDATE CASCADE; + +-- +-- Constraints for table `software_x_tag` +-- +ALTER TABLE `software_x_tag` + ADD CONSTRAINT `fk_software_x_tag_1` FOREIGN KEY (`softwareid`) REFERENCES `software` (`softwareid`) ON DELETE CASCADE ON UPDATE CASCADE; +-- +-- Constraints for table `user` +-- ALTER TABLE `user` ADD CONSTRAINT `fk_user_1` FOREIGN KEY (`organizationid`) REFERENCES `organization` (`organizationid`) ON UPDATE CASCADE; - -ALTER TABLE `software_x_tag` - ADD CONSTRAINT `fk_software_x_tag_1` FOREIGN KEY (`softwareid`) REFERENCES `software` (`softwareid`) ON UPDATE CASCADE ON DELETE CASCADE; +COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; diff --git a/dozentenmodulserver/setup/sat-01-testdata.sql b/dozentenmodulserver/setup/sat-01-testdata.sql index 95378468..57dc842a 100644 --- a/dozentenmodulserver/setup/sat-01-testdata.sql +++ b/dozentenmodulserver/setup/sat-01-testdata.sql @@ -1,13 +1,32 @@ +-- phpMyAdmin SQL Dump +-- version 5.1.0 +-- https://www.phpmyadmin.net/ +-- +-- Host: 192.168.200.20 +-- Generation Time: May 20, 2021 at 09:56 AM +-- 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 utf8 */; +/*!40101 SET NAMES utf8mb4 */; +-- +-- Database: `sat` +-- +CREATE DATABASE IF NOT EXISTS `sat` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE `sat`; +-- +-- 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), @@ -19,41 +38,63 @@ INSERT INTO `operatingsystem` (`osid`, `displayname`, `architecture`, `maxmem`, (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); +(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); -INSERT INTO `virtualizer` (`virtid`, `virtname`) VALUES -('vmware', 'VMware'),('virtualbox', 'VirtualBox'),('qemukvm', 'QEMU'),('docker', 'Docker'); - -INSERT INTO `os_x_virt` VALUES -(6,'vmware','opensuse'),(7,'vmware','opensuse-64'),(8,'vmware','other3xlinux'),(9,'vmware','other3xlinux-64'),(4,'vmware','ubuntu'),(5,'vmware','ubuntu-64'),(10,'vmware','windows7'),(1,'vmware','windows7-64'),(2,'vmware','windows8'),(3,'vmware','windows8-64'); +-- +-- Dumping data for table `organization` +-- INSERT INTO `organization` (`organizationid`, `displayname`, `canlogin`) VALUES ('blblogin.blb-karlsruhe.de', 'Badische Landesbibliothek', 0), ('bsz-bw.de', 'Bibliotheksservice-Zentrum Baden-Württemberg', 0), +('cas.dhbw.de', 'DHBW CAS', 0), ('demo', 'Demo-Satellit', 0), +('dhbw-heidenheim.de', 'DHBW Heidenheim', 0), ('dhbw-karlsruhe.de', 'DHBW Karlsruhe', 0), ('dhbw-loerrach.de', 'DHBW Lörrach', 0), ('dhbw-mannheim.de', 'DHBW Mannheim', 0), +('dhbw-ravensburg.de', 'DHBW Ravensburg', 0), ('dhbw-stuttgart.de', 'DHBW Stuttgart', 0), ('dhbw-vs.de', 'DHBW Villingen-Schwenningen', 0), +('eh-freiburg.ekiba.de', 'Evangelische Hochschule Freiburg', 0), ('fr-test.de', 'Uni Freiburg TEST', 0), +('hdm-stuttgart.de', 'Hochschule der Medien Stuttgart', 0), ('hft-stuttgart.de', 'Hochschule für Technik Stuttgart', 0), +('hfwu.de', 'HfWU Nürtingen-Geislingen', 0), +('hochschule-bc.de', 'Hochschule Biberach', 0), ('hs-albsig.de', 'Hochschule Albstadt-Sigmaringen', 0), ('hs-esslingen.de', 'Hochschule Esslingen', 0), -('hs-furtwangen.de', 'Hochschule Furtwangen University', 0), +('hs-furtwangen.de', 'Hochschule Furtwangen', 0), ('hs-heilbronn.de', 'Hochschule Heilbronn', 0), ('hs-karlsruhe.de', 'Hochschule Karlsruhe - Technik und Wirtschaft', 0), +('hs-kehl.de', 'Hochschule für öffentliche Verwaltung Kehl', 0), +('hs-ludwigsburg.de', 'Hochschule für öffentliche Verwaltung und Finanzen Ludwigsburg', 0), ('hs-mannheim.de', 'Hochschule Mannheim', 0), ('hs-offenburg.de', 'Hochschule Offenburg', 0), +('hs-pforzheim.de', 'Hochschule Pforzheim IdP', 0), ('hs-rottenburg.de', 'Hochschule für Forstwirtschaft Rottenburg (HFR)', 0), +('hs-ulm.de', 'Technische Hochschule Ulm', 0), +('hs-weingarten.de', 'Hochschule Ravensburg-Weingarten', 0), ('htw-aalen.de', 'Hochschule Aalen - Technik und Wirtschaft', 0), ('htwg-konstanz.de', 'HTWG Konstanz', 0), ('kit.edu', 'Karlsruher Institut für Technologie (KIT)', 0), ('lb.ph-ludwigsburg.de', 'PH Ludwigsburg', 0), +('lehre.dhbw-stuttgart.de', 'DHBW Stuttgart', 0), +('mh-freiburg.de', 'Hochschule für Musik Freiburg', 0), ('ph-freiburg.de', 'PH Freiburg', 0), ('ph-gmuend.de', 'PH Schwäbisch Gmünd', 0), +('ph-heidelberg.de', 'Pädagogische Hochschule Heidelberg', 0), ('ph-karlsruhe.de', 'PH Karlsruhe', 0), ('ph-weingarten.de', 'PH Weingarten', 0), +('praesidium.dhbw.de', 'DHBW', 0), ('reutlingen-university-test.de', 'Reutlingen TEST', 0), ('reutlingen-university.de', 'Reutlingen University / Hochschule Reutlingen', 0), ('test-hs-offenburg.de', 'HS Offenburg test', 0), @@ -66,20 +107,63 @@ INSERT INTO `organization` (`organizationid`, `displayname`, `canlogin`) VALUES ('uni-mannheim.de', 'Universität Mannheim', 0), ('uni-stuttgart.de', 'Universität Stuttgart', 0), ('uni-tuebingen.de', 'Universität Tübingen', 0), -('uni-ulm.de', 'Universität Ulm', 0); +('uni-ulm.de', 'Universität Ulm', 0), +('unibas.ch', 'Uni Basel', 0); -INSERT INTO `user` (`userid`, `firstname`, `lastname`, `email`, `organizationid`, `lastlogin`, `canlogin`, `issuperuser`) VALUES -('2f2a7203-1035-11e5-97f5-001999f89615', 'Max', 'Mustermann', 'spam@aol.com', 'fr-test.de', 1434025631, 1, 1); +-- +-- Dumping data for table `virtualizer` +-- -INSERT INTO `imagebase` (`imagebaseid`, `latestversionid`, `displayname`, `description`, `osid`, `virtid`, `createtime`, `updatetime`, `ownerid`, `updaterid`, `sharemode`, `istemplate`, `canlinkdefault`, `candownloaddefault`, `caneditdefault`, `canadmindefault`) VALUES -('ba9e1f3a-1035-11e5-97f5-001999f89615', NULL, 'Test Image', 'Bla bla bla', 1, 'vmware', 1434025865, 1434025865, '2f2a7203-1035-11e5-97f5-001999f89615', '2f2a7203-1035-11e5-97f5-001999f89615', 'LOCAL', 0, 1, 1, 0, 0); +INSERT INTO `virtualizer` (`virtid`, `virtname`) VALUES +('docker', 'Docker'), +('qemukvm', 'QEMU-KVM'), +('virtualbox', 'VirtualBox'), +('vmware', 'VMware'); -INSERT INTO `imagepermission` (`imagebaseid`, `userid`, `canlink`, `candownload`, `canedit`, `canadmin`) VALUES -('ba9e1f3a-1035-11e5-97f5-001999f89615', '2f2a7203-1035-11e5-97f5-001999f89615', 1, 1, 1, 1); +-- +-- Dumping data for table `os_x_virt` +-- -INSERT INTO `imageversion` (`imageversionid`, `imagebaseid`, `createtime`, `expiretime`, `filesize`, `filepath`, `uploaderid`, `isrestricted`, `isvalid`, `isprocessed`, `mastersha1`, `virtualizerconfig`) VALUES -('37613571-1036-11e5-97f5-001999f89615', 'ba9e1f3a-1035-11e5-97f5-001999f89615', 1434026074, 1434026074, 1234, 'y', '2f2a7203-1035-11e5-97f5-001999f89615', 1, 1, 1, NULL, NULL), -('721ab892-103e-11e5-97f5-001999f89615', 'ba9e1f3a-1035-11e5-97f5-001999f89615', 1434029609, 1434029609, 23452345, 'a', '2f2a7203-1035-11e5-97f5-001999f89615', 1, 1, 1, NULL, NULL); +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 */; -- cgit v1.2.3-55-g7522