You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
382 lines
14 KiB
PHP
382 lines
14 KiB
PHP
<?php
|
|
$migration_name = 'Initial setup, converted to migration';
|
|
|
|
try {
|
|
$this->database->query('SELECT * FROM entity');
|
|
} catch(mysqli_sql_exception $e) {
|
|
$this->database->query("
|
|
CREATE TABLE `access` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`source_entity_id` int(10) unsigned NOT NULL,
|
|
`dest_entity_id` int(10) unsigned NOT NULL,
|
|
`grant_date` datetime NOT NULL,
|
|
`granted_by` int(10) unsigned NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `source_entity_id_dest_entity_id` (`source_entity_id`, `dest_entity_id`),
|
|
KEY `FK_access_entity_2` (`dest_entity_id`),
|
|
KEY `FK_access_entity_3` (`granted_by`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT
|
|
");
|
|
|
|
$this->database->query("
|
|
CREATE TABLE `access_option` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`access_id` int(10) unsigned NOT NULL,
|
|
`option` enum('command', 'from', 'no-agent-forwarding', 'no-port-forwarding', 'no-pty', 'no-X11-forwarding') NOT NULL,
|
|
`value` text,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `access_id_option` (`access_id`, `option`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
");
|
|
|
|
$this->database->query("
|
|
CREATE TABLE `access_request` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`source_entity_id` int(10) unsigned NOT NULL,
|
|
`dest_entity_id` int(10) unsigned NOT NULL,
|
|
`request_date` datetime NOT NULL,
|
|
`requested_by` int(10) unsigned NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `source_entity_id_dest_entity_id` (`source_entity_id`, `dest_entity_id`),
|
|
KEY `FK_access_request_entity_2` (`dest_entity_id`),
|
|
KEY `FK_access_request_entity_3` (`requested_by`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;
|
|
");
|
|
|
|
$this->database->query("
|
|
CREATE TABLE `entity` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`type` enum('user','server account', 'group') NOT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
");
|
|
|
|
$this->database->query("
|
|
CREATE TABLE `entity_admin` (
|
|
`entity_id` int(10) unsigned NOT NULL,
|
|
`admin` int(10) unsigned NOT NULL,
|
|
PRIMARY KEY (`entity_id`, `admin`),
|
|
KEY `FK_entity_admin_entity_2` (`admin`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;
|
|
");
|
|
|
|
$this->database->query("
|
|
CREATE TABLE `entity_event` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`entity_id` int(10) unsigned NOT NULL,
|
|
`actor_id` int(10) unsigned NOT NULL,
|
|
`date` datetime NOT NULL,
|
|
`details` mediumtext NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `FK_entity_event_entity_id` (`entity_id`),
|
|
KEY `FK_entity_event_actor_id` (`actor_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
");
|
|
|
|
$this->database->query("
|
|
CREATE TABLE `group` (
|
|
`entity_id` int(10) unsigned NOT NULL,
|
|
`name` varchar(100) NOT NULL,
|
|
`active` tinyint(1) unsigned NOT NULL DEFAULT '1',
|
|
`system` tinyint(1) unsigned NOT NULL DEFAULT '0',
|
|
PRIMARY KEY (`entity_id`),
|
|
UNIQUE KEY `name` (`name`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
");
|
|
|
|
$this->database->query("
|
|
CREATE TABLE `group_event` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`group` int(10) unsigned NOT NULL,
|
|
`entity_id` int(10) unsigned NOT NULL,
|
|
`date` datetime NOT NULL,
|
|
`details` mediumtext NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `FK_group_event_group` (`group`),
|
|
KEY `FK_group_event_entity` (`entity_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;
|
|
");
|
|
|
|
$this->database->query("
|
|
CREATE TABLE `group_member` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`group` int(10) unsigned NOT NULL,
|
|
`entity_id` int(10) unsigned NOT NULL,
|
|
`add_date` datetime NOT NULL,
|
|
`added_by` int(10) unsigned NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `group_entity_id` (`group`, `entity_id`),
|
|
KEY `FK_group_member_entity` (`entity_id`),
|
|
KEY `FK_group_member_entity_2` (`added_by`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;
|
|
");
|
|
|
|
$this->database->query("
|
|
CREATE TABLE `public_key` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`entity_id` int(10) unsigned NOT NULL,
|
|
`type` varchar(30) NOT NULL,
|
|
`keydata` mediumtext NOT NULL,
|
|
`comment` mediumtext NOT NULL,
|
|
`keysize` int(11) DEFAULT NULL,
|
|
`fingerprint_md5` char(47) DEFAULT NULL,
|
|
`fingerprint_sha256` varchar(50) DEFAULT NULL,
|
|
`randomart_md5` text,
|
|
`randomart_sha256` text,
|
|
PRIMARY KEY (`id`),
|
|
KEY `FK_public_key_entity` (`entity_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
");
|
|
|
|
$this->database->query("
|
|
CREATE TABLE `public_key_dest_rule` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`public_key_id` int(10) unsigned NOT NULL,
|
|
`account_name_filter` varchar(50) NOT NULL,
|
|
`hostname_filter` varchar(255) NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `FK_public_key_dest_rule_public_key` (`public_key_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
");
|
|
|
|
$this->database->query("
|
|
CREATE TABLE `public_key_signature` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`public_key_id` int(10) unsigned NOT NULL,
|
|
`signature` blob NOT NULL,
|
|
`upload_date` datetime NOT NULL,
|
|
`fingerprint` varchar(50) NOT NULL,
|
|
`sign_date` datetime NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `FK_public_key_signature_public_key` (`public_key_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
");
|
|
|
|
$this->database->query("
|
|
CREATE TABLE `server` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`uuid` varchar(36) DEFAULT NULL,
|
|
`hostname` varchar(150) NOT NULL,
|
|
`ip_address` varchar(64) DEFAULT NULL,
|
|
`deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
|
|
`key_management` enum('none', 'keys', 'other', 'decommissioned') NOT NULL DEFAULT 'keys',
|
|
`authorization` enum('manual', 'automatic LDAP', 'manual LDAP') NOT NULL DEFAULT 'manual',
|
|
`use_sync_client` enum('no', 'yes') NOT NULL DEFAULT 'no',
|
|
`sync_status` enum('not synced yet', 'sync success', 'sync failure', 'sync warning') NOT NULL DEFAULT 'not synced yet',
|
|
`configuration_system` enum('unknown', 'cf-sysadmin', 'puppet-devops', 'puppet-miniops', 'puppet-tvstore', 'none') NOT NULL DEFAULT 'unknown',
|
|
`custom_keys` enum('not allowed', 'allowed') NOT NULL DEFAULT 'not allowed',
|
|
`rsa_key_fingerprint` char(32) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `hostname` (`hostname`),
|
|
KEY `ip_address` (`ip_address`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
");
|
|
|
|
$this->database->query("
|
|
CREATE TABLE `server_account` (
|
|
`entity_id` int(10) unsigned NOT NULL,
|
|
`server_id` int(10) unsigned NOT NULL,
|
|
`name` varchar(50) DEFAULT NULL,
|
|
`sync_status` enum('not synced yet', 'sync success', 'sync failure', 'sync warning', 'proposed') NOT NULL DEFAULT 'not synced yet',
|
|
`active` tinyint(1) unsigned NOT NULL DEFAULT '1',
|
|
PRIMARY KEY (`entity_id`),
|
|
UNIQUE KEY `server_id_name` (`server_id`, `name`),
|
|
KEY `FK_server_account_server` (`server_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
");
|
|
|
|
$this->database->query("
|
|
CREATE TABLE `server_admin` (
|
|
`server_id` int(10) unsigned NOT NULL,
|
|
`entity_id` int(10) unsigned NOT NULL,
|
|
PRIMARY KEY (`server_id`,`entity_id`),
|
|
KEY `FK_server_admin_entity` (`entity_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
");
|
|
|
|
$this->database->query("
|
|
CREATE TABLE `server_event` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`server_id` int(10) unsigned NOT NULL,
|
|
`actor_id` int(10) unsigned NOT NULL,
|
|
`date` datetime NOT NULL,
|
|
`details` mediumtext NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `FK_server_log_server` (`server_id`),
|
|
KEY `FK_server_event_actor_id` (`actor_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
");
|
|
|
|
$this->database->query("
|
|
CREATE TABLE `server_ldap_access_option` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`server_id` int(10) unsigned NOT NULL,
|
|
`option` enum('command', 'from', 'no-agent-forwarding', 'no-port-forwarding', 'no-pty', 'no-X11-forwarding') NOT NULL,
|
|
`value` text,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `server_id_option` (`server_id`, `option`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
");
|
|
|
|
$this->database->query("
|
|
CREATE TABLE `server_note` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`server_id` int(10) unsigned NOT NULL,
|
|
`entity_id` int(10) unsigned NOT NULL,
|
|
`date` datetime NOT NULL,
|
|
`note` mediumtext NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `FK_server_note_server` (`server_id`),
|
|
KEY `FK_server_note_user` (`entity_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
");
|
|
|
|
$this->database->query("
|
|
CREATE TABLE `sync_request` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`server_id` int(10) unsigned NOT NULL,
|
|
`account_name` varchar(50) DEFAULT NULL,
|
|
`processing` tinyint(1) unsigned NOT NULL DEFAULT '0',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `server_id_account_name` (`server_id`,`account_name`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
");
|
|
|
|
$this->database->query("
|
|
CREATE TABLE `user` (
|
|
`entity_id` int(10) unsigned NOT NULL,
|
|
`uid` varchar(50) NOT NULL,
|
|
`name` varchar(100) NOT NULL,
|
|
`email` varchar(100) NOT NULL,
|
|
`superior_entity_id` int(10) unsigned DEFAULT NULL,
|
|
`auth_realm` enum('LDAP','local','external') NOT NULL DEFAULT 'LDAP',
|
|
`active` tinyint(1) unsigned NOT NULL DEFAULT '1',
|
|
`admin` tinyint(1) unsigned NOT NULL DEFAULT '0',
|
|
`developer` tinyint(1) unsigned NOT NULL DEFAULT '0',
|
|
`force_disable` tinyint(1) unsigned NOT NULL DEFAULT '0',
|
|
`csrf_token` binary(128) DEFAULT NULL,
|
|
PRIMARY KEY (`entity_id`),
|
|
UNIQUE KEY `uid` (`uid`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
");
|
|
|
|
$this->database->query("
|
|
CREATE TABLE `user_alert` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`entity_id` int(10) unsigned NOT NULL,
|
|
`class` varchar(15) NOT NULL,
|
|
`content` mediumtext NOT NULL,
|
|
`escaping` int(10) unsigned NOT NULL DEFAULT '1',
|
|
PRIMARY KEY (`id`),
|
|
KEY `FK_user_alert_entity` (`entity_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
");
|
|
|
|
$this->database->query("
|
|
ALTER TABLE `access`
|
|
ADD CONSTRAINT `FK_access_entity` FOREIGN KEY (`source_entity_id`) REFERENCES `entity` (`id`) ON DELETE CASCADE,
|
|
ADD CONSTRAINT `FK_access_entity_2` FOREIGN KEY (`dest_entity_id`) REFERENCES `entity` (`id`) ON DELETE CASCADE,
|
|
ADD CONSTRAINT `FK_access_entity_3` FOREIGN KEY (`granted_by`) REFERENCES `entity` (`id`) ON DELETE CASCADE
|
|
");
|
|
|
|
$this->database->query("
|
|
ALTER TABLE `access_option`
|
|
ADD CONSTRAINT `FK_access_option_access` FOREIGN KEY (`access_id`) REFERENCES `access` (`id`) ON DELETE CASCADE
|
|
");
|
|
|
|
$this->database->query("
|
|
ALTER TABLE `access_request`
|
|
ADD CONSTRAINT `FK_access_request_entity` FOREIGN KEY (`source_entity_id`) REFERENCES `entity` (`id`) ON DELETE CASCADE,
|
|
ADD CONSTRAINT `FK_access_request_entity_2` FOREIGN KEY (`dest_entity_id`) REFERENCES `entity` (`id`) ON DELETE CASCADE,
|
|
ADD CONSTRAINT `FK_access_request_entity_3` FOREIGN KEY (`requested_by`) REFERENCES `entity` (`id`) ON DELETE CASCADE
|
|
");
|
|
|
|
$this->database->query("
|
|
ALTER TABLE `entity_admin`
|
|
ADD CONSTRAINT `FK_entity_admin_entity` FOREIGN KEY (`entity_id`) REFERENCES `entity` (`id`) ON DELETE CASCADE,
|
|
ADD CONSTRAINT `FK_entity_admin_entity_2` FOREIGN KEY (`admin`) REFERENCES `entity` (`id`) ON DELETE CASCADE
|
|
");
|
|
|
|
$this->database->query("
|
|
ALTER TABLE `entity_event`
|
|
ADD CONSTRAINT `FK_entity_event_actor_id` FOREIGN KEY (`actor_id`) REFERENCES `entity` (`id`),
|
|
ADD CONSTRAINT `FK_entity_event_entity_id` FOREIGN KEY (`entity_id`) REFERENCES `entity` (`id`)
|
|
");
|
|
|
|
$this->database->query("
|
|
ALTER TABLE `group`
|
|
ADD CONSTRAINT `FK_group_entity` FOREIGN KEY (`entity_id`) REFERENCES `entity` (`id`) ON DELETE CASCADE
|
|
");
|
|
|
|
$this->database->query("
|
|
ALTER TABLE `group_event`
|
|
ADD CONSTRAINT `FK_group_event_entity` FOREIGN KEY (`entity_id`) REFERENCES `entity` (`id`),
|
|
ADD CONSTRAINT `FK_group_event_group` FOREIGN KEY (`group`) REFERENCES `group` (`entity_id`)
|
|
");
|
|
|
|
$this->database->query("
|
|
ALTER TABLE `group_member`
|
|
ADD CONSTRAINT `FK_group_member_entity` FOREIGN KEY (`entity_id`) REFERENCES `entity` (`id`) ON DELETE CASCADE,
|
|
ADD CONSTRAINT `FK_group_member_entity_2` FOREIGN KEY (`added_by`) REFERENCES `entity` (`id`),
|
|
ADD CONSTRAINT `FK_group_member_group` FOREIGN KEY (`group`) REFERENCES `group` (`entity_id`) ON DELETE CASCADE
|
|
");
|
|
|
|
$this->database->query("
|
|
ALTER TABLE `public_key`
|
|
ADD CONSTRAINT `FK_public_key_entity` FOREIGN KEY (`entity_id`) REFERENCES `entity` (`id`) ON DELETE CASCADE
|
|
");
|
|
|
|
$this->database->query("
|
|
ALTER TABLE `public_key_dest_rule`
|
|
ADD CONSTRAINT `FK_public_key_dest_rule_public_key` FOREIGN KEY (`public_key_id`) REFERENCES `public_key` (`id`) ON DELETE CASCADE
|
|
");
|
|
|
|
$this->database->query("
|
|
ALTER TABLE `public_key_signature`
|
|
ADD CONSTRAINT `FK_public_key_signature_public_key` FOREIGN KEY (`public_key_id`) REFERENCES `public_key` (`id`) ON DELETE CASCADE
|
|
");
|
|
|
|
$this->database->query("
|
|
ALTER TABLE `server_account`
|
|
ADD CONSTRAINT `FK_server_account_entity` FOREIGN KEY (`entity_id`) REFERENCES `entity` (`id`) ON DELETE CASCADE,
|
|
ADD CONSTRAINT `FK_server_account_server` FOREIGN KEY (`server_id`) REFERENCES `server` (`id`) ON DELETE CASCADE
|
|
");
|
|
|
|
$this->database->query("
|
|
ALTER TABLE `server_admin`
|
|
ADD CONSTRAINT `FK_server_admin_entity` FOREIGN KEY (`entity_id`) REFERENCES `entity` (`id`) ON DELETE CASCADE,
|
|
ADD CONSTRAINT `FK_server_admin_server` FOREIGN KEY (`server_id`) REFERENCES `server` (`id`) ON DELETE CASCADE
|
|
");
|
|
|
|
$this->database->query("
|
|
ALTER TABLE `server_event`
|
|
ADD CONSTRAINT `FK_server_event_actor_id` FOREIGN KEY (`actor_id`) REFERENCES `entity` (`id`),
|
|
ADD CONSTRAINT `FK_server_log_server` FOREIGN KEY (`server_id`) REFERENCES `server` (`id`)
|
|
");
|
|
|
|
$this->database->query("
|
|
ALTER TABLE `server_ldap_access_option`
|
|
ADD CONSTRAINT `FK_server_ldap_access_option_server` FOREIGN KEY (`server_id`) REFERENCES `server` (`id`) ON DELETE CASCADE
|
|
");
|
|
|
|
$this->database->query("
|
|
ALTER TABLE `server_note`
|
|
ADD CONSTRAINT `FK_server_note_entity` FOREIGN KEY (`entity_id`) REFERENCES `entity` (`id`),
|
|
ADD CONSTRAINT `FK_server_note_server` FOREIGN KEY (`server_id`) REFERENCES `server` (`id`) ON DELETE CASCADE
|
|
");
|
|
|
|
$this->database->query("
|
|
ALTER TABLE `sync_request`
|
|
ADD CONSTRAINT `FK_sync_request_server` FOREIGN KEY (`server_id`) REFERENCES `server` (`id`) ON DELETE CASCADE
|
|
");
|
|
|
|
$this->database->query("
|
|
ALTER TABLE `user`
|
|
ADD CONSTRAINT `FK_user_entity` FOREIGN KEY (`entity_id`) REFERENCES `entity` (`id`) ON DELETE CASCADE
|
|
");
|
|
|
|
$this->database->query("
|
|
ALTER TABLE `user_alert`
|
|
ADD CONSTRAINT `FK_user_alert_entity` FOREIGN KEY (`entity_id`) REFERENCES `entity` (`id`) ON DELETE CASCADE
|
|
");
|
|
} |