diff --git a/docker/mariadb/Dockerfile b/docker/mariadb/Dockerfile index 0c907e49..87cda56f 100644 --- a/docker/mariadb/Dockerfile +++ b/docker/mariadb/Dockerfile @@ -1,4 +1,5 @@ FROM mariadb:10.9 COPY mysql-init/setup.sh /docker-entrypoint-initdb.d/ +COPY mysql-init/roundcube.mysql.initial.sql /home/ COPY mysql-update/update.sh /always-initdb.d/ diff --git a/docker/mariadb/mysql-init/roundcube.mysql.initial.sql b/docker/mariadb/mysql-init/roundcube.mysql.initial.sql new file mode 100644 index 00000000..d87fd438 --- /dev/null +++ b/docker/mariadb/mysql-init/roundcube.mysql.initial.sql @@ -0,0 +1,247 @@ +-- This is a copy of roundcubemail/SQL/mysql.initial.sql (besides this comment) +-- Roundcube Webmail initial database structure + + +SET FOREIGN_KEY_CHECKS=0; + +-- Table structure for table `session` + +CREATE TABLE `session` ( + `sess_id` varchar(128) NOT NULL, + `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', + `ip` varchar(40) NOT NULL, + `vars` mediumtext NOT NULL, + PRIMARY KEY(`sess_id`), + INDEX `changed_index` (`changed`) +) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + + +-- Table structure for table `users` + +CREATE TABLE `users` ( + `user_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, + `username` varchar(128) BINARY NOT NULL, + `mail_host` varchar(128) NOT NULL, + `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', + `last_login` datetime DEFAULT NULL, + `failed_login` datetime DEFAULT NULL, + `failed_login_counter` int(10) UNSIGNED DEFAULT NULL, + `language` varchar(16), + `preferences` longtext, + PRIMARY KEY(`user_id`), + UNIQUE `username` (`username`, `mail_host`) +) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + + +-- Table structure for table `cache` + +CREATE TABLE `cache` ( + `user_id` int(10) UNSIGNED NOT NULL, + `cache_key` varchar(128) BINARY NOT NULL, + `expires` datetime DEFAULT NULL, + `data` longtext NOT NULL, + PRIMARY KEY (`user_id`, `cache_key`), + CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, + INDEX `expires_index` (`expires`) +) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + + +-- Table structure for table `cache_shared` + +CREATE TABLE `cache_shared` ( + `cache_key` varchar(255) BINARY NOT NULL, + `expires` datetime DEFAULT NULL, + `data` longtext NOT NULL, + PRIMARY KEY (`cache_key`), + INDEX `expires_index` (`expires`) +) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + + +-- Table structure for table `cache_index` + +CREATE TABLE `cache_index` ( + `user_id` int(10) UNSIGNED NOT NULL, + `mailbox` varchar(255) BINARY NOT NULL, + `expires` datetime DEFAULT NULL, + `valid` tinyint(1) NOT NULL DEFAULT '0', + `data` longtext NOT NULL, + CONSTRAINT `user_id_fk_cache_index` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, + INDEX `expires_index` (`expires`), + PRIMARY KEY (`user_id`, `mailbox`) +) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + + +-- Table structure for table `cache_thread` + +CREATE TABLE `cache_thread` ( + `user_id` int(10) UNSIGNED NOT NULL, + `mailbox` varchar(255) BINARY NOT NULL, + `expires` datetime DEFAULT NULL, + `data` longtext NOT NULL, + CONSTRAINT `user_id_fk_cache_thread` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, + INDEX `expires_index` (`expires`), + PRIMARY KEY (`user_id`, `mailbox`) +) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + + +-- Table structure for table `cache_messages` + +CREATE TABLE `cache_messages` ( + `user_id` int(10) UNSIGNED NOT NULL, + `mailbox` varchar(255) BINARY NOT NULL, + `uid` int(11) UNSIGNED NOT NULL DEFAULT '0', + `expires` datetime DEFAULT NULL, + `data` longtext NOT NULL, + `flags` int(11) NOT NULL DEFAULT '0', + CONSTRAINT `user_id_fk_cache_messages` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, + INDEX `expires_index` (`expires`), + PRIMARY KEY (`user_id`, `mailbox`, `uid`) +) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + + +-- Table structure for table `collected_addresses` + +CREATE TABLE `collected_addresses` ( + `address_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, + `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', + `name` varchar(255) NOT NULL DEFAULT '', + `email` varchar(255) NOT NULL, + `user_id` int(10) UNSIGNED NOT NULL, + `type` int(10) UNSIGNED NOT NULL, + PRIMARY KEY(`address_id`), + CONSTRAINT `user_id_fk_collected_addresses` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, + UNIQUE INDEX `user_email_collected_addresses_index` (`user_id`, `type`, `email`) +) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + + +-- Table structure for table `contacts` + +CREATE TABLE `contacts` ( + `contact_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, + `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', + `del` tinyint(1) NOT NULL DEFAULT '0', + `name` varchar(128) NOT NULL DEFAULT '', + `email` text NOT NULL, + `firstname` varchar(128) NOT NULL DEFAULT '', + `surname` varchar(128) NOT NULL DEFAULT '', + `vcard` longtext NULL, + `words` text NULL, + `user_id` int(10) UNSIGNED NOT NULL, + PRIMARY KEY(`contact_id`), + CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, + INDEX `user_contacts_index` (`user_id`,`del`) +) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + + +-- Table structure for table `contactgroups` + +CREATE TABLE `contactgroups` ( + `contactgroup_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, + `user_id` int(10) UNSIGNED NOT NULL, + `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', + `del` tinyint(1) NOT NULL DEFAULT '0', + `name` varchar(128) NOT NULL DEFAULT '', + PRIMARY KEY(`contactgroup_id`), + CONSTRAINT `user_id_fk_contactgroups` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, + INDEX `contactgroups_user_index` (`user_id`,`del`) +) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + + +-- Table structure for table `contactgroupmembers` + +CREATE TABLE `contactgroupmembers` ( + `contactgroup_id` int(10) UNSIGNED NOT NULL, + `contact_id` int(10) UNSIGNED NOT NULL, + `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', + PRIMARY KEY (`contactgroup_id`, `contact_id`), + CONSTRAINT `contactgroup_id_fk_contactgroups` FOREIGN KEY (`contactgroup_id`) + REFERENCES `contactgroups`(`contactgroup_id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `contact_id_fk_contacts` FOREIGN KEY (`contact_id`) + REFERENCES `contacts`(`contact_id`) ON DELETE CASCADE ON UPDATE CASCADE, + INDEX `contactgroupmembers_contact_index` (`contact_id`) +) ROW_FORMAT=DYNAMIC ENGINE=INNODB; + + +-- Table structure for table `identities` + +CREATE TABLE `identities` ( + `identity_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, + `user_id` int(10) UNSIGNED NOT NULL, + `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', + `del` tinyint(1) NOT NULL DEFAULT '0', + `standard` tinyint(1) NOT NULL DEFAULT '0', + `name` varchar(128) NOT NULL, + `organization` varchar(128) NOT NULL DEFAULT '', + `email` varchar(128) NOT NULL, + `reply-to` varchar(128) NOT NULL DEFAULT '', + `bcc` varchar(128) NOT NULL DEFAULT '', + `signature` longtext, + `html_signature` tinyint(1) NOT NULL DEFAULT '0', + PRIMARY KEY(`identity_id`), + CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, + INDEX `user_identities_index` (`user_id`, `del`), + INDEX `email_identities_index` (`email`, `del`) +) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + + +-- Table structure for table `dictionary` + +CREATE TABLE `dictionary` ( + `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, -- redundant, for compat. with Galera Cluster + `user_id` int(10) UNSIGNED DEFAULT NULL, -- NULL here is for "shared dictionaries" + `language` varchar(16) NOT NULL, + `data` longtext NOT NULL, + CONSTRAINT `user_id_fk_dictionary` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, + UNIQUE `uniqueness` (`user_id`, `language`) +) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + + +-- Table structure for table `searches` + +CREATE TABLE `searches` ( + `search_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, + `user_id` int(10) UNSIGNED NOT NULL, + `type` int(3) NOT NULL DEFAULT '0', + `name` varchar(128) NOT NULL, + `data` text, + PRIMARY KEY(`search_id`), + CONSTRAINT `user_id_fk_searches` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, + UNIQUE `uniqueness` (`user_id`, `type`, `name`) +) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +-- Table structure for table `filestore` + +CREATE TABLE `filestore` ( + `file_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, + `user_id` int(10) UNSIGNED NOT NULL, + `context` varchar(32) NOT NULL, + `filename` varchar(128) NOT NULL, + `mtime` int(10) NOT NULL, + `data` longtext NOT NULL, + PRIMARY KEY (`file_id`), + CONSTRAINT `user_id_fk_filestore` FOREIGN KEY (`user_id`) + REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, + UNIQUE `uniqueness` (`user_id`, `context`, `filename`) +) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +-- Table structure for table `system` + +CREATE TABLE `system` ( + `name` varchar(64) NOT NULL, + `value` mediumtext, + PRIMARY KEY(`name`) +) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +SET FOREIGN_KEY_CHECKS=1; + +INSERT INTO `system` (`name`, `value`) VALUES ('roundcube-version', '2020122900'); diff --git a/docker/mariadb/mysql-init/setup.sh b/docker/mariadb/mysql-init/setup.sh index e9965e93..a8698c1c 100755 --- a/docker/mariadb/mysql-init/setup.sh +++ b/docker/mariadb/mysql-init/setup.sh @@ -1,149 +1,101 @@ #!/bin/bash MYSQL_PWD=$MARIADB_ROOT_PASSWORD mysql --protocol=socket -uroot -hlocalhost --socket="/run/mysqld/mysqld.sock" << EOF CREATE DATABASE ${DB_HKCCP_DATABASE}; CREATE USER '${DB_HKCCP_USERNAME}'@'%' IDENTIFIED BY '${DB_HKCCP_PASSWORD}'; GRANT ALL PRIVILEGES ON ${DB_HKCCP_DATABASE}.* TO '${DB_HKCCP_USERNAME}'@'%' IDENTIFIED BY '${DB_HKCCP_PASSWORD}'; FLUSH PRIVILEGES; EOF MYSQL_PWD=$MARIADB_ROOT_PASSWORD mysql --protocol=socket -uroot -hlocalhost --socket="/run/mysqld/mysqld.sock" << EOF CREATE DATABASE ${DB_KOLAB_DATABASE}; CREATE USER ${DB_KOLAB_USERNAME}@'%' IDENTIFIED BY '${DB_KOLAB_PASSWORD}'; GRANT ALL PRIVILEGES ON ${DB_KOLAB_DATABASE}.* TO ${DB_KOLAB_USERNAME}@'%' IDENTIFIED BY '${DB_KOLAB_PASSWORD}'; FLUSH PRIVILEGES; EOF MYSQL_PWD=$MARIADB_ROOT_PASSWORD mysql --protocol=socket -uroot -hlocalhost --socket="/run/mysqld/mysqld.sock" << EOF CREATE DATABASE IF NOT EXISTS $DB_RC_DATABASE CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE USER IF NOT EXISTS $DB_RC_USERNAME@'%' IDENTIFIED BY '$DB_RC_PASSWORD'; ALTER USER $DB_RC_USERNAME@'%' IDENTIFIED BY '$DB_RC_PASSWORD'; GRANT ALL PRIVILEGES ON $DB_RC_DATABASE.* TO $DB_RC_USERNAME@'%'; FLUSH PRIVILEGES; EOF -MYSQL_PWD=$DB_RC_PASSWORD mysql --protocol=socket -uroot -hlocalhost --socket="/run/mysqld/mysqld.sock" ${DB_RC_USERNAME}<< EOF -CREATE TABLE users ( - user_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT, - username varchar(128) BINARY NOT NULL, - mail_host varchar(128) NOT NULL, - created datetime NOT NULL DEFAULT '1000-01-01 00:00:00', - last_login datetime DEFAULT NULL, - failed_login datetime DEFAULT NULL, - failed_login_counter int(10) UNSIGNED DEFAULT NULL, - language varchar(16), - preferences longtext, - PRIMARY KEY(user_id), - UNIQUE username (username, mail_host) -) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; - - -CREATE TABLE identities ( - identity_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT, - user_id int(10) UNSIGNED NOT NULL, - changed datetime NOT NULL DEFAULT '1000-01-01 00:00:00', - del tinyint(1) NOT NULL DEFAULT '0', - standard tinyint(1) NOT NULL DEFAULT '0', - name varchar(128) NOT NULL, - organization varchar(128) NOT NULL DEFAULT '', - email varchar(128) NOT NULL, - reply-to varchar(128) NOT NULL DEFAULT '', - bcc varchar(128) NOT NULL DEFAULT '', - signature longtext, - html_signature tinyint(1) NOT NULL DEFAULT '0', - PRIMARY KEY(identity_id), - CONSTRAINT user_id_fk_identities FOREIGN KEY (user_id) - REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE, - INDEX user_identities_index (user_id, del), - INDEX email_identities_index (email, del) -) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; - - -CREATE TABLE filestore ( - file_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT, - user_id int(10) UNSIGNED NOT NULL, - context varchar(32) NOT NULL, - filename varchar(128) NOT NULL, - mtime int(10) NOT NULL, - data longtext NOT NULL, - PRIMARY KEY (file_id), - CONSTRAINT user_id_fk_filestore FOREIGN KEY (user_id) - REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, - UNIQUE uniqueness (user_id, context, filename) -) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; - -EOF +# Required because the webapp starts first to provide authentication, +# but the seeder will fail if the roundcube tables are not available. +cat /home/roundcube.mysql.initial.sql | MYSQL_PWD=$DB_RC_PASSWORD mysql --protocol=socket -uroot -hlocalhost --socket="/run/mysqld/mysqld.sock" ${DB_RC_USERNAME} # Powerdns setup according to https://github.com/PowerDNS/pdns/blob/master/modules/gmysqlbackend/schema.mysql.sql # Required for the first boot, afterwards the laravel migration will take over. # This is only required so pdns can start cleanly, indexes etc are handled by the laravel migration. MYSQL_PWD=$MARIADB_ROOT_PASSWORD mysql --protocol=socket -uroot -hlocalhost --socket="/run/mysqld/mysqld.sock" ${DB_HKCCP_DATABASE} << EOF CREATE TABLE powerdns_domains ( id INT AUTO_INCREMENT, name VARCHAR(255) NOT NULL, master VARCHAR(128) DEFAULT NULL, last_check INT DEFAULT NULL, type VARCHAR(8) NOT NULL, notified_serial INT UNSIGNED DEFAULT NULL, account VARCHAR(40) CHARACTER SET 'utf8' DEFAULT NULL, options VARCHAR(64000) DEFAULT NULL, catalog VARCHAR(255) DEFAULT NULL, PRIMARY KEY (id) ) Engine=InnoDB CHARACTER SET 'latin1'; CREATE TABLE powerdns_records ( id BIGINT AUTO_INCREMENT, domain_id INT DEFAULT NULL, name VARCHAR(255) DEFAULT NULL, type VARCHAR(10) DEFAULT NULL, content VARCHAR(64000) DEFAULT NULL, ttl INT DEFAULT NULL, prio INT DEFAULT NULL, disabled TINYINT(1) DEFAULT 0, ordername VARCHAR(255) BINARY DEFAULT NULL, auth TINYINT(1) DEFAULT 1, PRIMARY KEY (id) ) Engine=InnoDB CHARACTER SET 'latin1'; CREATE TABLE powerdns_masters ( ip VARCHAR(64) NOT NULL, nameserver VARCHAR(255) NOT NULL, account VARCHAR(40) CHARACTER SET 'utf8' NOT NULL, PRIMARY KEY (ip, nameserver) ) Engine=InnoDB CHARACTER SET 'latin1'; CREATE TABLE powerdns_comments ( id INT AUTO_INCREMENT, domain_id INT NOT NULL, name VARCHAR(255) NOT NULL, type VARCHAR(10) NOT NULL, modified_at INT NOT NULL, account VARCHAR(40) CHARACTER SET 'utf8' DEFAULT NULL, comment TEXT CHARACTER SET 'utf8' NOT NULL, PRIMARY KEY (id) ) Engine=InnoDB CHARACTER SET 'latin1'; CREATE TABLE powerdns_cryptokeys ( id INT AUTO_INCREMENT, domain_id INT NOT NULL, flags INT NOT NULL, active BOOL, published BOOL DEFAULT 1, content TEXT, PRIMARY KEY(id) ) Engine=InnoDB CHARACTER SET 'latin1'; CREATE TABLE powerdns_tsigkeys ( id INT AUTO_INCREMENT, name VARCHAR(255), algorithm VARCHAR(50), secret VARCHAR(255), PRIMARY KEY (id) ) Engine=InnoDB CHARACTER SET 'latin1'; EOF touch /tmp/initialized