1 # @tag: custom_variable_configs_column_type_text
2 # @description: Spaltentypen in 'custom_varialbe_configs' anpassen & schärfere Restriktionen
3 # @depends: release_3_0_0
4 package SL::DBUpgrade2::custom_variable_configs_column_type_text;
9 use parent qw(SL::DBUpgrade2::Base);
14 # Fix 'sortkey' column to not contain NULLs
15 my $q_update = qq|UPDATE custom_variable_configs SET sortkey = ? WHERE id = ?|;
16 my $h_update = $self->dbh->prepare($q_update) || die $self->dbh->errstr;
20 FROM custom_variable_configs
21 ORDER BY module ASC, sortkey ASC NULLS LAST, id ASC
24 my $previous_module = '';
26 foreach my $entry (@{ $self->dbh->selectall_arrayref($q_select) }) {
27 $sortkey = $previous_module eq $entry->[1] ? $sortkey + 1 : 1;
28 $previous_module = $entry->[1];
30 $h_update->execute($sortkey, $entry->[0]) || die $self->dbh->errstr;
35 # Apply structure upgrade
37 qq|ALTER TABLE custom_variable_configs ALTER COLUMN type TYPE TEXT|,
38 qq|ALTER TABLE custom_variable_configs ALTER COLUMN module TYPE TEXT|,
40 qq|UPDATE custom_variable_configs SET searchable = FALSE WHERE searchable IS NULL|,
41 qq|UPDATE custom_variable_configs SET includeable = FALSE WHERE includeable IS NULL|,
42 qq|UPDATE custom_variable_configs SET included_by_default = FALSE WHERE included_by_default IS NULL|,
44 qq|ALTER TABLE custom_variable_configs ALTER COLUMN searchable SET NOT NULL|,
45 qq|ALTER TABLE custom_variable_configs ALTER COLUMN includeable SET NOT NULL|,
46 qq|ALTER TABLE custom_variable_configs ALTER COLUMN included_by_default SET NOT NULL|,
47 qq|ALTER TABLE custom_variable_configs ALTER COLUMN name SET NOT NULL|,
48 qq|ALTER TABLE custom_variable_configs ALTER COLUMN description SET NOT NULL|,
49 qq|ALTER TABLE custom_variable_configs ALTER COLUMN type SET NOT NULL|,
50 qq|ALTER TABLE custom_variable_configs ALTER COLUMN module SET NOT NULL|,
51 qq|ALTER TABLE custom_variable_configs ALTER COLUMN sortkey SET NOT NULL|,
53 qq|ALTER TABLE custom_variable_configs
54 ADD CONSTRAINT custom_variable_configs_name_description_type_module_not_empty
58 AND description <> '')|,
60 qq|ALTER TABLE custom_variable_configs
61 ADD CONSTRAINT custom_variable_configs_options_not_empty_for_select
62 CHECK ((type <> 'select') OR (COALESCE(options, '') <> ''))|,
65 $self->db_query($_) for @statements;