Revision 76273ada
Von Moritz Bunkus vor etwa 12 Jahren hinzugefügt
| SL/DBUpgrade2/Base.pm | ||
|---|---|---|
|
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__
|
||
|
|
||
| ... | ... | |
|
|
||
|
=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
|
||
| sql/Pg-upgrade2-auth/foreign_key_constraints_on_delete.pl | ||
|---|---|---|
|
# @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;
|
||
| sql/Pg-upgrade2-auth/foreign_key_constraints_on_delete.sql | ||
|---|---|---|
|
-- @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;
|
||
Auch abrufbar als: Unified diff
Script foreign_key_constraints_on_delete als Perl-Script neu implementiert
Grund: die Foreign Keys können durchaus auch anders
heißen (z.B. "$1").