From 76273adac85df187367d7f2c60ae5f018915a969 Mon Sep 17 00:00:00 2001
From: Moritz Bunkus <m.bunkus@linet-services.de>
Date: Mon, 17 Jun 2013 16:03:20 +0200
Subject: [PATCH] Script foreign_key_constraints_on_delete als Perl-Script neu
 implementiert
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

Grund: die Foreign Keys können durchaus auch anders
heißen (z.B. "$1").
---
 SL/DBUpgrade2/Base.pm                         | 33 ++++++++++++++
 .../foreign_key_constraints_on_delete.pl      | 43 +++++++++++++++++++
 .../foreign_key_constraints_on_delete.sql     | 40 -----------------
 3 files changed, 76 insertions(+), 40 deletions(-)
 create mode 100644 sql/Pg-upgrade2-auth/foreign_key_constraints_on_delete.pl
 delete mode 100644 sql/Pg-upgrade2-auth/foreign_key_constraints_on_delete.sql

diff --git a/SL/DBUpgrade2/Base.pm b/SL/DBUpgrade2/Base.pm
index 9978b207e..4a817c423 100644
--- a/SL/DBUpgrade2/Base.pm
+++ b/SL/DBUpgrade2/Base.pm
@@ -119,6 +119,24 @@ sub add_print_templates {
   return 1;
 }
 
+sub drop_constraints {
+  my ($self, %params) = @_;
+
+  croak "Missing parameter 'table'" unless $params{table};
+  $params{type}   ||= 'FOREIGN KEY';
+  $params{schema} ||= 'public';
+
+  my $constraints = $self->dbh->selectall_arrayref(<<SQL, undef, $params{type}, $params{schema}, $params{table});
+    SELECT constraint_name
+    FROM information_schema.table_constraints
+    WHERE (constraint_type = ?)
+      AND (table_schema    = ?)
+      AND (table_name      = ?)
+SQL
+
+  $self->db_query(qq|ALTER TABLE auth."$params{table}" DROP CONSTRAINT "${_}"|) for map { $_->[0] } @{ $constraints };
+}
+
 1;
 __END__
 
@@ -259,6 +277,21 @@ used.
 
 =back
 
+=item C<drop_constraints %params>
+
+Drops all constraints of a type (e.g. foreign keys) on a table. One
+parameter is mandatory: C<table>. Optional parameters include:
+
+=over 2
+
+=item * C<schema> -- if missing defaults to C<public>
+
+=item * C<type> -- if missing defaults to C<FOREIGN KEY>. Must be one of
+the values contained in the C<information_schema.table_constraints>
+view in the C<constraint_type> column.
+
+=back
+
 =item C<execute_script>
 
 Executes a named database upgrade script. This function is not
diff --git a/sql/Pg-upgrade2-auth/foreign_key_constraints_on_delete.pl b/sql/Pg-upgrade2-auth/foreign_key_constraints_on_delete.pl
new file mode 100644
index 000000000..5423e91c2
--- /dev/null
+++ b/sql/Pg-upgrade2-auth/foreign_key_constraints_on_delete.pl
@@ -0,0 +1,43 @@
+# @tag: foreign_key_constraints_on_delete
+# @description: Ändert "FOREIGN KEY" constraints auf "ON DELETE CASCADE"
+# @depends: clients
+# @ignore: 0
+package SL::DBUpgrade2::foreign_key_constraints_on_delete;
+
+use Data::Dumper;
+
+
+use strict;
+use utf8;
+
+use parent qw(SL::DBUpgrade2::Base);
+
+sub run {
+  my ($self) = @_;
+
+  $self->drop_constraints(schema => 'auth', table => $_) for qw(clients_groups clients_users group_rights session_content user_config user_group);
+
+  my @add_constraints = (
+    qq|ALTER TABLE auth.clients_groups ADD FOREIGN KEY (client_id) REFERENCES auth.clients (id) ON DELETE CASCADE|,
+    qq|ALTER TABLE auth.clients_groups ADD FOREIGN KEY (group_id)  REFERENCES auth."group" (id) ON DELETE CASCADE|,
+
+    qq|ALTER TABLE auth.clients_users ADD FOREIGN KEY (client_id) REFERENCES auth.clients (id) ON DELETE CASCADE|,
+    qq|ALTER TABLE auth.clients_users ADD FOREIGN KEY (user_id)   REFERENCES auth."user"  (id) ON DELETE CASCADE|,
+
+    qq|ALTER TABLE auth.group_rights ADD FOREIGN KEY (group_id) REFERENCES auth."group" (id) ON DELETE CASCADE|,
+
+
+    qq|ALTER TABLE auth.session_content ADD FOREIGN KEY (session_id) REFERENCES auth.session (id) ON DELETE CASCADE|,
+
+    qq|ALTER TABLE auth.user_config ADD FOREIGN KEY (user_id) REFERENCES auth."user" (id) ON DELETE CASCADE|,
+
+    qq|ALTER TABLE auth.user_group ADD FOREIGN KEY (user_id)  REFERENCES auth."user"  (id) ON DELETE CASCADE|,
+    qq|ALTER TABLE auth.user_group ADD FOREIGN KEY (group_id) REFERENCES auth."group" (id) ON DELETE CASCADE|,
+  );
+
+  $self->db_query($_) for @add_constraints;
+
+  return 1;
+}
+
+1;
diff --git a/sql/Pg-upgrade2-auth/foreign_key_constraints_on_delete.sql b/sql/Pg-upgrade2-auth/foreign_key_constraints_on_delete.sql
deleted file mode 100644
index 197bd8e11..000000000
--- a/sql/Pg-upgrade2-auth/foreign_key_constraints_on_delete.sql
+++ /dev/null
@@ -1,40 +0,0 @@
--- @tag: foreign_key_constraints_on_delete
--- @description: Ändert "FOREIGN KEY" constraints auf "ON DELETE CASCADE"
--- @depends: clients
--- @charset: utf-8
-
--- auth.clients_groups
-ALTER TABLE auth.clients_groups DROP CONSTRAINT clients_groups_client_id_fkey;
-ALTER TABLE auth.clients_groups DROP CONSTRAINT clients_groups_group_id_fkey;
-
-ALTER TABLE auth.clients_groups ADD FOREIGN KEY (client_id) REFERENCES auth.clients (id) ON DELETE CASCADE;
-ALTER TABLE auth.clients_groups ADD FOREIGN KEY (group_id)  REFERENCES auth."group" (id) ON DELETE CASCADE;
-
--- auth.clients_users
-ALTER TABLE auth.clients_users DROP CONSTRAINT clients_users_client_id_fkey;
-ALTER TABLE auth.clients_users DROP CONSTRAINT clients_users_user_id_fkey;
-
-ALTER TABLE auth.clients_users ADD FOREIGN KEY (client_id) REFERENCES auth.clients (id) ON DELETE CASCADE;
-ALTER TABLE auth.clients_users ADD FOREIGN KEY (user_id)   REFERENCES auth."user"  (id) ON DELETE CASCADE;
-
--- auth.group_rights
-ALTER TABLE auth.group_rights DROP CONSTRAINT group_rights_group_id_fkey;
-
-ALTER TABLE auth.group_rights ADD FOREIGN KEY (group_id) REFERENCES auth."group" (id) ON DELETE CASCADE;
-
- -- auth.session_content
-ALTER TABLE auth.session_content DROP CONSTRAINT session_content_session_id_fkey;
-
-ALTER TABLE auth.session_content ADD FOREIGN KEY (session_id) REFERENCES auth.session (id) ON DELETE CASCADE;
-
- -- auth.user_config
-ALTER TABLE auth.user_config DROP CONSTRAINT user_config_user_id_fkey;
-
-ALTER TABLE auth.user_config ADD FOREIGN KEY (user_id) REFERENCES auth."user" (id) ON DELETE CASCADE;
-
--- auth.user_group
-ALTER TABLE auth.user_group DROP CONSTRAINT user_group_user_id_fkey;
-ALTER TABLE auth.user_group DROP CONSTRAINT user_group_group_id_fkey;
-
-ALTER TABLE auth.user_group ADD FOREIGN KEY (user_id)  REFERENCES auth."user"  (id) ON DELETE CASCADE;
-ALTER TABLE auth.user_group ADD FOREIGN KEY (group_id) REFERENCES auth."group" (id) ON DELETE CASCADE;
-- 
2.20.1