Lines 1-19
Link Here
|
1 |
use Modern::Perl; |
1 |
use Modern::Perl; |
2 |
|
2 |
|
3 |
return { |
3 |
return { |
4 |
bug_number => "31383", |
4 |
bug_number => "31383", |
5 |
description => "Split the additional_contents table", |
5 |
description => "Split the additional_contents table", |
6 |
up => sub { |
6 |
up => sub { |
7 |
my ($args) = @_; |
7 |
my ($args) = @_; |
8 |
my ($dbh, $out) = @$args{qw(dbh out)}; |
8 |
my ( $dbh, $out ) = @$args{qw(dbh out)}; |
9 |
unless ( TableExists('additional_contents_localizations') ) { |
9 |
unless ( TableExists('additional_contents_localizations') ) { |
10 |
$dbh->do(q{ |
10 |
$dbh->do( |
|
|
11 |
q{ |
11 |
ALTER TABLE additional_contents |
12 |
ALTER TABLE additional_contents |
12 |
CHANGE COLUMN idnew `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for the additional content category' |
13 |
CHANGE COLUMN idnew `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for the additional content category' |
13 |
}); |
14 |
} |
|
|
15 |
); |
14 |
say $out "Renamed additional_contents.idnew with 'id'"; |
16 |
say $out "Renamed additional_contents.idnew with 'id'"; |
15 |
|
17 |
|
16 |
$dbh->do(q{ |
18 |
$dbh->do( |
|
|
19 |
q{ |
17 |
CREATE TABLE `additional_contents_localizations` ( |
20 |
CREATE TABLE `additional_contents_localizations` ( |
18 |
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for the additional content', |
21 |
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for the additional content', |
19 |
`additional_content_id` int(10) unsigned NOT NULL COMMENT 'link to the additional content', |
22 |
`additional_content_id` int(10) unsigned NOT NULL COMMENT 'link to the additional content', |
Lines 25-48
return {
Link Here
|
25 |
UNIQUE KEY `additional_contents_localizations_uniq` (`additional_content_id`,`lang`), |
28 |
UNIQUE KEY `additional_contents_localizations_uniq` (`additional_content_id`,`lang`), |
26 |
CONSTRAINT `additional_contents_localizations_ibfk1` FOREIGN KEY (`additional_content_id`) REFERENCES `additional_contents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
29 |
CONSTRAINT `additional_contents_localizations_ibfk1` FOREIGN KEY (`additional_content_id`) REFERENCES `additional_contents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
27 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
30 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
28 |
}); |
31 |
} |
|
|
32 |
); |
29 |
say $out "Added new table 'additional_contents_localizations'"; |
33 |
say $out "Added new table 'additional_contents_localizations'"; |
30 |
|
34 |
|
31 |
my $contents = $dbh->selectall_arrayref(q{SELECT MIN(id) AS id, category, code, branchcode FROM additional_contents GROUP BY category, code, branchcode}, { Slice => {} }); |
35 |
my $contents = $dbh->selectall_arrayref( |
32 |
my $sth_insert = $dbh->prepare(q{ |
36 |
q{SELECT MIN(id) AS id, category, code, branchcode FROM additional_contents GROUP BY category, code, branchcode}, |
|
|
37 |
{ Slice => {} } |
38 |
); |
39 |
my $sth_insert = $dbh->prepare( |
40 |
q{ |
33 |
INSERT INTO additional_contents_localizations(additional_content_id, title, content, lang, updated_on) |
41 |
INSERT INTO additional_contents_localizations(additional_content_id, title, content, lang, updated_on) |
34 |
VALUES(?, ?, ?, ?, ?) |
42 |
VALUES(?, ?, ?, ?, ?) |
35 |
}); |
43 |
} |
36 |
for my $content ( @$contents ) { |
44 |
); |
|
|
45 |
for my $content (@$contents) { |
37 |
my $q = q{ |
46 |
my $q = q{ |
38 |
SELECT title, content, lang, branchcode, updated_on |
47 |
SELECT title, content, lang, branchcode, updated_on |
39 |
FROM additional_contents |
48 |
FROM additional_contents |
40 |
WHERE category=? AND code=? AND |
49 |
WHERE category=? AND code=? AND |
41 |
}; |
50 |
}; |
42 |
$q .= defined $content->{branchcode} ? " branchcode = ?" : " branchcode IS NULL"; |
51 |
$q .= defined $content->{branchcode} ? " branchcode = ?" : " branchcode IS NULL"; |
43 |
my $translated_contents = $dbh->selectall_arrayref($q, { Slice => {} }, $content->{category}, $content->{code}, defined $content->{branchcode} ? $content->{branchcode} : ()); |
52 |
my $translated_contents = $dbh->selectall_arrayref( |
44 |
for my $translated_content ( @$translated_contents ) { |
53 |
$q, { Slice => {} }, $content->{category}, $content->{code}, |
45 |
$sth_insert->execute($content->{id}, $translated_content->{title}, $translated_content->{content}, $translated_content->{lang}, $translated_content->{updated_on}); |
54 |
defined $content->{branchcode} ? $content->{branchcode} : () |
|
|
55 |
); |
56 |
for my $translated_content (@$translated_contents) { |
57 |
$sth_insert->execute( |
58 |
$content->{id}, $translated_content->{title}, $translated_content->{content}, |
59 |
$translated_content->{lang}, $translated_content->{updated_on} |
60 |
); |
46 |
} |
61 |
} |
47 |
|
62 |
|
48 |
# Delete duplicates |
63 |
# Delete duplicates |
Lines 51-105
return {
Link Here
|
51 |
WHERE category=? AND code=? AND id<>? AND |
66 |
WHERE category=? AND code=? AND id<>? AND |
52 |
}; |
67 |
}; |
53 |
$q .= defined $content->{branchcode} ? " branchcode = ?" : " branchcode IS NULL"; |
68 |
$q .= defined $content->{branchcode} ? " branchcode = ?" : " branchcode IS NULL"; |
54 |
$dbh->do($q, undef, $content->{category}, $content->{code}, $content->{id}, $content->{branchcode}); |
69 |
$dbh->do( $q, undef, $content->{category}, $content->{code}, $content->{id}, $content->{branchcode} ); |
55 |
} |
70 |
} |
56 |
$dbh->do(q{ |
71 |
$dbh->do( |
|
|
72 |
q{ |
57 |
ALTER TABLE additional_contents |
73 |
ALTER TABLE additional_contents |
58 |
DROP INDEX additional_contents_uniq |
74 |
DROP INDEX additional_contents_uniq |
59 |
}); |
75 |
} |
60 |
$dbh->do(q{ |
76 |
); |
|
|
77 |
$dbh->do( |
78 |
q{ |
61 |
ALTER TABLE additional_contents |
79 |
ALTER TABLE additional_contents |
62 |
ADD UNIQUE KEY `additional_contents_uniq` (`category`,`code`,`branchcode`) |
80 |
ADD UNIQUE KEY `additional_contents_uniq` (`category`,`code`,`branchcode`) |
63 |
}); |
81 |
} |
64 |
$dbh->do(q{ |
82 |
); |
|
|
83 |
$dbh->do( |
84 |
q{ |
65 |
ALTER TABLE additional_contents |
85 |
ALTER TABLE additional_contents |
66 |
DROP COLUMN title, |
86 |
DROP COLUMN title, |
67 |
DROP COLUMN content, |
87 |
DROP COLUMN content, |
68 |
DROP COLUMN lang |
88 |
DROP COLUMN lang |
69 |
}); |
89 |
} |
|
|
90 |
); |
70 |
say $out "Removed 'title', 'content', 'lang' columns from additional_contents"; |
91 |
say $out "Removed 'title', 'content', 'lang' columns from additional_contents"; |
71 |
} |
92 |
} |
72 |
|
93 |
|
73 |
my $notice_templates = $dbh->selectall_arrayref(q{ |
94 |
my $notice_templates = $dbh->selectall_arrayref( |
|
|
95 |
q{ |
74 |
SELECT id, module, code, content |
96 |
SELECT id, module, code, content |
75 |
FROM letter |
97 |
FROM letter |
76 |
WHERE content LIKE "%<news>%" |
98 |
WHERE content LIKE "%<news>%" |
77 |
}, { Slice => {} }); |
99 |
}, { Slice => {} } |
78 |
for my $template ( @$notice_templates ) { |
100 |
); |
|
|
101 |
for my $template (@$notice_templates) { |
79 |
my $new_content = $template->{content}; |
102 |
my $new_content = $template->{content}; |
80 |
$new_content =~ s|<news>|[% FOR content IN additional_contents %]|g; |
103 |
$new_content =~ s|<news>|[% FOR content IN additional_contents %]|g; |
81 |
$new_content =~ s|</news>|[% END %]|g; |
104 |
$new_content =~ s|</news>|[% END %]|g; |
82 |
$new_content =~ s{<<\s*additional_contents\.title\s*>>}{[% content.title | html %]}g; |
105 |
$new_content =~ s{<<\s*additional_contents\.title\s*>>}{[% content.title | html %]}g; |
83 |
$new_content =~ s{<<\s*additional_contents\.content\s*>>}{[% content.content | html %]}g; |
106 |
$new_content =~ s{<<\s*additional_contents\.content\s*>>}{[% content.content | html %]}g; |
84 |
$new_content =~ s{<<\s*additional_contents\.published_on\s*>>}{[% content.published_on | \$KohaDates %]}g; |
107 |
$new_content =~ s{<<\s*additional_contents\.published_on\s*>>}{[% content.published_on | \$KohaDates %]}g; |
85 |
$dbh->do(q{ |
108 |
$dbh->do( |
|
|
109 |
q{ |
86 |
UPDATE letter |
110 |
UPDATE letter |
87 |
SET content = ? |
111 |
SET content = ? |
88 |
WHERE id = ? |
112 |
WHERE id = ? |
89 |
}, undef, $new_content, $template->{id}); |
113 |
}, undef, $new_content, $template->{id} |
|
|
114 |
); |
90 |
|
115 |
|
91 |
say $out "Adjusted notice template '" . $template->{code} . "'"; |
116 |
say $out "Adjusted notice template '" . $template->{code} . "'"; |
92 |
} |
117 |
} |
93 |
|
118 |
|
94 |
$notice_templates = $dbh->selectall_arrayref(q{ |
119 |
$notice_templates = $dbh->selectall_arrayref( |
|
|
120 |
q{ |
95 |
SELECT id, code |
121 |
SELECT id, code |
96 |
FROM letter |
122 |
FROM letter |
97 |
WHERE content LIKE "%<<additional_content%" OR content LIKE "%<< additional_content%" |
123 |
WHERE content LIKE "%<<additional_content%" OR content LIKE "%<< additional_content%" |
98 |
}); |
124 |
} |
|
|
125 |
); |
99 |
|
126 |
|
100 |
if ( @$notice_templates ) { |
127 |
if (@$notice_templates) { |
101 |
say $out "WARNING - Some notice templates need manual adjustments!"; |
128 |
say $out "WARNING - Some notice templates need manual adjustments!"; |
102 |
for my $template ( @$notice_templates ) { |
129 |
for my $template (@$notice_templates) { |
103 |
say $out sprintf "\t %s (id=%s)", $template->{code}, $template->{id}; |
130 |
say $out sprintf "\t %s (id=%s)", $template->{code}, $template->{id}; |
104 |
} |
131 |
} |
105 |
} |
132 |
} |