|
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 |
} |