Attachment 143186 Details for Bug 31378 – Bug 31378: DB changes

[patch] Bug 31378: DB changes

Bug-31378-DB-changes.patch (text/plain), 10.12 KB, created by Agustín Moyano on 2022-11-04 12:48:56 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Agustín Moyano

Created: 2022-11-04 12:48:56 UTC

Size: 10.12 KB

patch

obsolete

>From 12cd9e4ce7a250820d8a61d2cf6e167ee9b6b678 Mon Sep 17 00:00:00 2001
>From: Agustin Moyano <agustinmoyano@theke.io>
>Date: Thu, 18 Aug 2022 16:08:20 -0300
>Subject: [PATCH] Bug 31378: DB changes
>
>---
> .../data/mysql/atomicupdate/bug_31378.pl      | 91 +++++++++++++++++++
> installer/data/mysql/kohastructure.sql        | 49 ++++++++++
> 2 files changed, 140 insertions(+)
> create mode 100755 installer/data/mysql/atomicupdate/bug_31378.pl
>
>diff --git a/installer/data/mysql/atomicupdate/bug_31378.pl b/installer/data/mysql/atomicupdate/bug_31378.pl
>new file mode 100755
>index 0000000000..66d796ddeb
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_31378.pl
>@@ -0,0 +1,91 @@
>+use Modern::Perl;
>+use C4::Context;
>+
>+return {
>+    bug_number  => "31378",
>+    description => "Add auth_provider and auth_provider_domains configuration tables",
>+    up => sub {
>+        my ($args) = @_;
>+        my ($dbh, $out) = @$args{qw(dbh out)};
>+
>+        unless (TableExists('auth_providers')) {
>+            $dbh->do(q{
>+                CREATE TABLE `auth_providers` (
>+                `auth_provider_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique key, used to identify the provider',
>+                `code` varchar(20) NOT NULL COMMENT 'Provider code',
>+                `description` varchar(255) NOT NULL COMMENT 'Description for the provider',
>+                `protocol` enum('OAuth', 'OIDC', 'LDAP', 'CAS') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Protocol provider speaks',
>+                `config` longtext NOT NULL DEFAULT '{}' COMMENT 'Configuration of the provider in JSON format',
>+                `mapping` longtext NOT NULL DEFAULT '{}' COMMENT 'Configuration to map provider data to Koha user',
>+                `matchpoint` enum('email','userid','cardnumber') NOT NULL COMMENT 'The patron attribute to be used as matchpoint',
>+                `icon_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Provider icon URL',
>+                PRIMARY KEY (`auth_provider_id`),
>+                UNIQUE KEY (`code`),
>+                KEY `protocol` (`protocol`)
>+                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
>+            });
>+        }
>+
>+        unless (TableExists('auth_provider_domains')) {
>+            $dbh->do(q{
>+                CREATE TABLE `auth_provider_domains` (
>+                    `auth_provider_domain_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique key, used to identify providers domain',
>+                    `auth_provider_id` int(11) NOT NULL COMMENT 'Reference to provider',
>+                    `domain` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Domain name. If null means all domains',
>+                    `auto_register` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Allow user auto register',
>+                    `update_on_auth` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Update user data on auth login',
>+                    `default_library_id` varchar(10) DEFAULT NULL COMMENT 'Default library to create user if auto register is enabled',
>+                    `default_category_id` varchar(10) DEFAULT NULL COMMENT 'Default category to create user if auto register is enabled',
>+                    `allow_opac` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Allow provider from opac interface',
>+                    `allow_staff` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Allow provider from staff interface',
>+                    PRIMARY KEY (`auth_provider_domain_id`),
>+                    UNIQUE KEY (`auth_provider_id`, `domain`),
>+                    KEY `domain` (`domain`),
>+                    KEY `allow_opac` (`allow_opac`),
>+                    KEY `allow_staff` (`allow_staff`),
>+                    CONSTRAINT `auth_provider_domain_ibfk_1` FOREIGN KEY (`auth_provider_id`) REFERENCES `auth_providers` (`auth_provider_id`) ON DELETE CASCADE,
>+                    CONSTRAINT `auth_provider_domain_ibfk_2` FOREIGN KEY (`default_library_id`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE,
>+                    CONSTRAINT `auth_provider_domain_ibfk_3` FOREIGN KEY (`default_category_id`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE
>+                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
>+            });
>+        }
>+
>+        if (C4::Context->preference('GoogleOpenIDConnect')) {
>+            # Print useful stuff here
>+            say $out "Setting google provider";
>+            $dbh->do(q{
>+                INSERT INTO `auth_providers` (name, protocol, config, mapping), auto_register, registration_config, interface)
>+                SELECT  'google' as name,
>+                        'OIDC' as protocol,
>+                        JSON_OBJECT("key", k.value, "secret", s.value, "well_known_url", "https://accounts.google.com/.well-known/openid-configuration", "scope", "openid email profile") as config,
>+                        JSON_OBJECT("email", "email", "firstname", "given_name", "surname", "family_name", "_key", "email") as mapping
>+                FROM
>+                    (SELECT value FROM `systempreferences` where variable = 'GoogleOAuth2ClientID') k
>+                JOIN
>+                    (SELECT value FROM `systempreferences` where variable = 'GoogleOAuth2ClientSecret') s
>+            });
>+
>+            $dbh->do(q{
>+                INSERT INTO `auth_provider_domains` (auth_provider_id, domain, auto_register, update_on_auth, default_library_id, default_category_id, allow_opac, allow_staff)
>+                        p.id as provider_id,
>+                        d.value as domain,
>+                        r.value as auto_register,
>+                        0 as update_on_auth,
>+                        b.value as default_branch,
>+                        c.value as default_category,
>+                        1 as allow_opac,
>+                        0 as allow_interface
>+                FROM
>+                    (SELECT id FROM `auth_provider` WHERE name = 'google') p
>+                JOIN
>+                    (SELECT CASE WHEN value = '' OR value IS NULL THEN NULL ELSE value END as value FROM `systempreferences` where variable = 'GoogleOpenIDConnectDomain') d
>+                JOIN
>+                    (SELECT CASE WHEN value = '' OR value IS NULL THEN '0' ELSE value END as value FROM `systempreferences` where variable = 'GoogleOpenIDConnectAutoRegister') r
>+                JOIN
>+                    (SELECT CASE WHEN value = '' OR value IS NULL THEN NULL ELSE value END as value FROM `systempreferences` where variable = 'GoogleOpenIDConnectDefaultCategory') c
>+                JOIN
>+                    (SELECT CASE WHEN value = '' OR value IS NULL THEN NULL ELSE value END as value FROM `systempreferences` where variable = 'GoogleOpenIDConnectDefaultBranch') b
>+            });
>+        }
>+    },
>+};
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index e88c75f5d4..3b8489fd01 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -849,6 +849,55 @@ CREATE TABLE `auth_header` (
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
> /*!40101 SET character_set_client = @saved_cs_client */;
> 
>+--
>+-- Table structure for table `auth_provider`
>+--
>+
>+DROP TABLE IF EXISTS `auth_providers`;
>+/*!40101 SET @saved_cs_client     = @@character_set_client */;
>+/*!40101 SET character_set_client = utf8 */;
>+CREATE TABLE `auth_providers` (
>+  `auth_provider_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique key, used to identify the provider',
>+  `code` varchar(20) NOT NULL COMMENT 'Provider code',
>+  `description` varchar(255) NOT NULL COMMENT 'Description for the provider',
>+  `protocol` enum('OAuth', 'OIDC', 'LDAP', 'CAS') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Protocol provider speaks',
>+  `config` longtext NOT NULL DEFAULT '{}' COMMENT 'Configuration of the provider in JSON format',
>+  `mapping` longtext NOT NULL DEFAULT '{}' COMMENT 'Configuration to map provider data to Koha user',
>+  `matchpoint` enum('email','userid','cardnumber') NOT NULL COMMENT 'The patron attribute to be used as matchpoint',
>+  `icon_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Provider icon URL',
>+  PRIMARY KEY (`auth_provider_id`),
>+  UNIQUE KEY (`code`),
>+  KEY `protocol` (`protocol`)
>+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
>+/*!40101 SET character_set_client = @saved_cs_client */;
>+
>+--
>+-- Table structure for table `auth_provider`
>+--
>+
>+DROP TABLE IF EXISTS `auth_provider_domains`;
>+/*!40101 SET @saved_cs_client     = @@character_set_client */;
>+/*!40101 SET character_set_client = utf8 */;
>+CREATE TABLE `auth_provider_domains` (
>+  `auth_provider_domain_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique key, used to identify providers domain',
>+  `auth_provider_id` int(11) NOT NULL COMMENT 'Reference to provider',
>+  `domain` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Domain name. If null means all domains',
>+  `auto_register` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Allow user auto register',
>+  `update_on_auth` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Update user data on auth login',
>+  `default_library_id` varchar(10) DEFAULT NULL COMMENT 'Default library to create user if auto register is enabled',
>+  `default_category_id` varchar(10) DEFAULT NULL COMMENT 'Default category to create user if auto register is enabled',
>+  `allow_opac` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Allow provider from opac interface',
>+  `allow_staff` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Allow provider from staff interface',
>+  PRIMARY KEY (`auth_provider_domain_id`),
>+  UNIQUE KEY (`auth_provider_id`, `domain`),
>+  KEY `domain` (`domain`),
>+  KEY `allow_opac` (`allow_opac`),
>+  KEY `allow_staff` (`allow_staff`),
>+  CONSTRAINT `auth_provider_domain_ibfk_1` FOREIGN KEY (`auth_provider_id`) REFERENCES `auth_providers` (`auth_provider_id`) ON DELETE CASCADE,
>+  CONSTRAINT `auth_provider_domain_ibfk_2` FOREIGN KEY (`default_library_id`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE,
>+  CONSTRAINT `auth_provider_domain_ibfk_3` FOREIGN KEY (`default_category_id`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE
>+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
>+
> --
> -- Table structure for table `auth_subfield_structure`
> --
>-- 
>2.25.1

Actions: View | Diff | Splinter Review

Attachments on bug 31378: 141362 | 141363 | 141364 | 141365 | 141366 | 141367 | 141368 | 141369 | 141370 | 141371 | 141372 | 141373 | 141374 | 141375 | 141376 | 141377 | 141378 | 141514 | 141771 | 141772 | 141773 | 141774 | 141775 | 141776 | 141777 | 141778 | 141779 | 141780 | 141781 | 141782 | 141783 | 141784 | 141785 | 141786 | 141787 | 141788 | 141801 | 141802 | 141803 | 141804 | 141805 | 141806 | 141807 | 141808 | 141809 | 141810 | 141811 | 141812 | 141813 | 141814 | 141815 | 141816 | 141817 | 141818 | 141853 | 143056 | 143087 | 143088 | 143089 | 143090 | 143091 | 143092 | 143093 | 143094 | 143095 | 143096 | 143097 | 143098 | 143099 | 143100 | 143101 | 143102 | 143103 | 143104 | 143105 | 143106 | 143107 | 143186 | 143187 | 143188 | 143189 | 143190 | 143191 | 143192 | 143193 | 143194 | 143195 | 143196 | 143197 | 143198 | 143199 | 143200 | 143202 | 143204 | 143205 | 143206 | 143207 | 143208 | 143215 | 143216 | 143217 | 143218 | 143219 | 143220 | 143221 | 143222 | 143223 | 143224 | 143225 | 143226 | 143227 | 143228 | 143229 | 143230 | 143231 | 143232 | 143233 | 143234 | 143235 | 143236 | 143266 | 143267 | 143268 | 143269 | 143270 | 143271 | 143272 | 143273 | 143274 | 143275 | 143277 | 143278 | 143279 | 143280 | 143281 | 143282 | 143283 | 143284 | 143285 | 143286 | 143287 | 143288 | 143396 | 143397 | 143398 | 143399 | 143400 | 143401 | 143402 | 143403 | 143404 | 143405 | 143406 | 143407 | 143408 | 143409 | 143410 | 143411 | 143412 | 143413 | 143414 | 143415 | 143416 | 143417 | 143418 | 143420 | 143421 | 143422 | 143423 | 143424 | 143425 | 143426 | 143427 | 143428 | 143429 | 143430 | 143431 | 143432 | 143433 | 143434 | 143435 | 143436 | 143437 | 143438 | 143439 | 143440 | 143441 | 143442 | 143443 | 143450 | 144071 | 144175