View | Details | Raw Unified | Return to bug 31383
Collapse All | Expand All

(-)a/installer/data/mysql/atomicupdate/bug_31383.pl (+99 lines)
Line 0 Link Here
1
use Modern::Perl;
2
3
return {
4
    bug_number => "31383",
5
    description => "Split the additional_contents table",
6
    up => sub {
7
        my ($args) = @_;
8
        my ($dbh, $out) = @$args{qw(dbh out)};
9
        unless ( TableExists('additional_contents_localizations') ) {
10
            $dbh->do(q{
11
                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
            });
14
            say $out "Renamed additional_contents.idnew with 'id'";
15
16
            $dbh->do(q{
17
                CREATE TABLE `additional_contents_localizations` (
18
                    `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',
20
                    `title` varchar(250) NOT NULL DEFAULT '' COMMENT 'title of the additional content',
21
                    `content` mediumtext NOT NULL COMMENT 'the body of your additional content',
22
                    `lang` varchar(50) NOT NULL DEFAULT '' COMMENT 'lang',
23
                    PRIMARY KEY (`id`),
24
                    UNIQUE KEY `additional_contents_localizations_uniq` (`additional_content_id`,`lang`),
25
                    CONSTRAINT `additional_contents_localizations_ibfk1` FOREIGN KEY (`additional_content_id`) REFERENCES `additional_contents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
26
                  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
27
            });
28
            say $out "Added new table 'additional_contents_localizations'";
29
30
            my $contents = $dbh->selectall_arrayref(q{SELECT MIN(id) AS id, category, code, branchcode FROM additional_contents GROUP BY category, code, branchcode}, { Slice => {} });
31
            my $sth_insert = $dbh->prepare(q{
32
                INSERT INTO additional_contents_localizations(additional_content_id, title, content, lang)
33
                VALUES(?, ?, ?, ?)
34
            });
35
            for my $content ( @$contents ) {
36
                my $q = q{
37
                    SELECT title, content, lang, branchcode
38
                    FROM additional_contents
39
                    WHERE category=? AND code=? AND
40
                };
41
                $q .= defined $content->{branchcode} ? " branchcode = ?" : " branchcode IS NULL";
42
                my $translated_contents = $dbh->selectall_arrayref($q, { Slice => {} }, $content->{category}, $content->{code}, defined $content->{branchcode} ? $content->{branchcode} : ());
43
                for my $translated_content ( @$translated_contents ) {
44
                    $sth_insert->execute($content->{id}, $translated_content->{title}, $translated_content->{content}, $translated_content->{lang});
45
                }
46
            }
47
            $dbh->do(q{
48
                ALTER TABLE additional_contents
49
                DROP INDEX additional_contents_uniq
50
            });
51
            $dbh->do(q{
52
                ALTER TABLE additional_contents
53
                ADD UNIQUE KEY `additional_contents_uniq` (`category`,`code`,`branchcode`)
54
            });
55
            $dbh->do(q{
56
                ALTER TABLE additional_contents
57
                DROP COLUMN title,
58
                DROP COLUMN content,
59
                DROP COLUMN lang
60
            });
61
            say $out "Removed 'title', 'content', 'lang' columns from additional_contents";
62
        }
63
64
        my $notice_templates = $dbh->selectall_arrayref(q{
65
            SELECT id, module, code, content
66
            FROM letter
67
            WHERE content LIKE "%<news>%"
68
        });
69
        for my $template ( @$notice_templates ) {
70
            my $new_content = $template->{content};
71
            $new_content =~ s|<news>|[% FOR content IN additional_contents %]|g;
72
            $new_content =~ s|</news>|[% END %]|g;
73
            $new_content =~ s{<<\s*additional_contents\.title\s*>>}{[% content.title | html %]}g;
74
            $new_content =~ s{<<\s*additional_contents\.content\s*>>}{[% content.content | html %]}g;
75
            $new_content =~ s{<<\s*additional_contents\.published_on\s*>>}{[% content.published_on | \$KohaDates %]}g;
76
            $dbh->do(q{
77
                UPDATE letter
78
                SET content = ?
79
                WHERE id = ?
80
            }, undef, $new_content, $template->{id});
81
82
            say $out "Adjusted notice template '" . $template->{code} . "'";
83
        }
84
85
        $notice_templates = $dbh->selectall_arrayref(q{
86
            SELECT id, code
87
            FROM letter
88
            WHERE content LIKE "%<<additional_content%" OR content LIKE "%<< additional_content%"
89
        });
90
91
        if ( @$notice_templates ) {
92
            say $out "WARNING - Some notice templates need manual adjustments!";
93
            for my $template ( @$notice_templates ) {
94
                say $out sprintf "\t %s (id=%s)", $template->{code}, $template->{id};
95
            }
96
        }
97
98
    },
99
};
(-)a/installer/data/mysql/en/mandatory/sample_notices.yml (-5 / +8 lines)
Lines 658-663 tables: Link Here
658
          message_transport_type: email
658
          message_transport_type: email
659
          lang: default
659
          lang: default
660
          content:
660
          content:
661
            - "[%~ USE KohaDates ~%]"
661
            - "<h3><<branches.branchname>></h3>"
662
            - "<h3><<branches.branchname>></h3>"
662
            - "Checked out to <<borrowers.title>> <<borrowers.firstname>> <<borrowers.initials>> <<borrowers.surname>> <br />"
663
            - "Checked out to <<borrowers.title>> <<borrowers.firstname>> <<borrowers.initials>> <<borrowers.surname>> <br />"
663
            - "(<<borrowers.cardnumber>>) <br />"
664
            - "(<<borrowers.cardnumber>>) <br />"
Lines 708-722 tables: Link Here
708
            - ""
709
            - ""
709
            - "<hr>"
710
            - "<hr>"
710
            - ""
711
            - ""
712
            - "[% IF additional_contents.count %]"
711
            - "<h4 style=\"text-align: center; font-style:italic;\">News</h4>"
713
            - "<h4 style=\"text-align: center; font-style:italic;\">News</h4>"
712
            - "<news>"
714
            - "[% FOREACH content IN additional_contents %]"
713
            - "<div class=\"newsitem\">"
715
            - "<div class=\"newsitem\">"
714
            - "<h5 style=\"margin-bottom: 1px; margin-top: 1px\"><b><<additional_contents.title>></b></h5>"
716
            - "<h5 style=\"margin-bottom: 1px; margin-top: 1px\"><b>[% content.title %]</b></h5>"
715
            - "<p style=\"margin-bottom: 1px; margin-top: 1px\"><<additional_contents.content>></p>"
717
            - "<p style=\"margin-bottom: 1px; margin-top: 1px\">[% content.content %]</p>"
716
            - "<p class=\"newsfooter\" style=\"font-size: 8pt; font-style:italic; margin-bottom: 1px; margin-top: 1px\">Posted on <<additional_contents.published_on>></p>"
718
            - "<p class=\"newsfooter\" style=\"font-size: 8pt; font-style:italic; margin-bottom: 1px; margin-top: 1px\">Posted on [% content.published_on | $KohaDates %]</p>"
717
            - "<hr />"
719
            - "<hr />"
718
            - "</div>"
720
            - "</div>"
719
            - "</news>"
721
            - "[% END %]"
722
            - "[% END %]"
720
723
721
        - module: circulation
724
        - module: circulation
722
          code: ODUE
725
          code: ODUE
(-)a/installer/data/mysql/en/optional/sample_news.yml (-26 / +37 lines)
Lines 22-45 description: Link Here
22
22
23
tables:
23
tables:
24
  - additional_contents:
24
  - additional_contents:
25
      translatable: [ title, content ]
25
      translatable: []
26
      multiline: [ content ]
26
      multiline: []
27
      rows:
27
      rows:
28
        - title: "Welcome to Koha"
28
        - code: "News_1"
29
          category: "news"
29
          category: "news"
30
          code: "News_1"
31
          location: "staff_only"
30
          location: "staff_only"
32
          content:
33
            - "Welcome to Koha. Koha is a full-featured open-source ILS. Developed initially in New Zealand by Katipo Communications Ltd and first deployed in January of 2000 for Horowhenua Library Trust, Koha is currently maintained by a team of software providers and library technology staff from around the globe."
34
          lang: "default"
35
          published_on: "2007-10-29 05:25:58"
31
          published_on: "2007-10-29 05:25:58"
36
          expirationdate: "2099-01-10"
32
          expirationdate: "2099-01-10"
37
          number: 1
33
          number: 1
38
34
39
        - title: "What's Next?"
35
        - code: "News_2"
40
          category: "news"
36
          category: "news"
41
          location: "staff_only"
37
          location: "staff_only"
42
          code: "News_2"
38
          published_on: "2007-10-29 05:34:45"
39
          expirationdate: "2099-01-10"
40
          number: 2
41
42
        - code: "CatalogConcernHelp_1"
43
          category: "html_customizations"
44
          location: "CatalogConcernHelp"
45
          published_on: "2007-10-29 05:25:58"
46
          expirationdate: "2099-01-10"
47
          number: 1
48
49
        - code: "CatalogConcernTemplate_1"
50
          category: "html_customizations"
51
          location: "CatalogConcernTemplate"
52
          published_on: "2007-10-29 05:25:58"
53
          expirationdate: "2099-01-10"
54
          number: 1
55
56
  - additional_contents_localizations:
57
      translatable: [ title, content ]
58
      multiline: [ content ]
59
      rows:
60
        - additional_content_id: 1
61
          title: "Welcome to Koha"
62
          content:
63
            - "Welcome to Koha. Koha is a full-featured open-source ILS. Developed initially in New Zealand by Katipo Communications Ltd and first deployed in January of 2000 for Horowhenua Library Trust, Koha is currently maintained by a team of software providers and library technology staff from around the globe."
64
          lang: "default"
65
        - additional_content_id: 2
66
          title: "What's Next?"
43
          content:
67
          content:
44
            - "Now that you've installed Koha, what's next? Here are some suggestions:"
68
            - "Now that you've installed Koha, what's next? Here are some suggestions:"
45
            - "<ul>"
69
            - "<ul>"
Lines 52-76 tables: Link Here
52
            - "</ul>"
76
            - "</ul>"
53
            - ""
77
            - ""
54
          lang: "default"
78
          lang: "default"
55
          published_on: "2007-10-29 05:34:45"
56
          expirationdate: "2099-01-10"
57
          number: 2
58
79
59
        - title: "Catalog concern help text"
80
        - additional_content_id: 3
60
          category: "html_customizations"
81
          title: "Catalog concern help text"
61
          location: "CatalogConcernHelp"
62
          code: "CatalogConcernHelp_1"
63
          content:
82
          content:
64
              - "Please describe your concern clearly and the library will try to deal with it as quickly as possible"
83
              - "Please describe your concern clearly and the library will try to deal with it as quickly as possible"
65
          lang: "default"
84
          lang: "default"
66
          published_on: "2007-10-29 05:25:58"
67
          expirationdate: "2099-01-10"
68
          number: 1
69
85
70
        - title: "Catalog concern template text"
86
        - additional_content_id: 4
71
          category: "html_customizations"
87
          title: "Catalog concern template text"
72
          location: "CatalogConcernTemplate"
73
          code: "CatalogConcernTemplate_1"
74
          content:
88
          content:
75
            - "**Describe the concern**"
89
            - "**Describe the concern**"
76
            - "A clear and concise description of what the concern is."
90
            - "A clear and concise description of what the concern is."
Lines 85-90 tables: Link Here
85
            - "**Expected behavior**"
99
            - "**Expected behavior**"
86
            - "A clear and concise description of what you expected to happen."
100
            - "A clear and concise description of what you expected to happen."
87
          lang: "default"
101
          lang: "default"
88
          published_on: "2007-10-29 05:25:58"
89
          expirationdate: "2099-01-10"
90
          number: 1
(-)a/installer/data/mysql/kohastructure.sql (-7 / +15 lines)
Lines 190-210 DROP TABLE IF EXISTS `additional_contents`; Link Here
190
/*!40101 SET @saved_cs_client     = @@character_set_client */;
190
/*!40101 SET @saved_cs_client     = @@character_set_client */;
191
/*!40101 SET character_set_client = utf8 */;
191
/*!40101 SET character_set_client = utf8 */;
192
CREATE TABLE `additional_contents` (
192
CREATE TABLE `additional_contents` (
193
  `idnew` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for the additional content',
193
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for the additional content category',
194
  `category` varchar(20) NOT NULL COMMENT 'category for the additional content',
194
  `category` varchar(20) NOT NULL COMMENT 'category for the additional content',
195
  `code` varchar(100) NOT NULL COMMENT 'code to group content per lang',
195
  `code` varchar(100) NOT NULL COMMENT 'code to group content per lang',
196
  `location` varchar(255) NOT NULL COMMENT 'location of the additional content',
196
  `location` varchar(255) NOT NULL COMMENT 'location of the additional content',
197
  `branchcode` varchar(10) DEFAULT NULL COMMENT 'branch code users to create branch specific additional content, NULL is every branch.',
197
  `branchcode` varchar(10) DEFAULT NULL COMMENT 'branch code users to create branch specific additional content, NULL is every branch.',
198
  `title` varchar(250) NOT NULL DEFAULT '' COMMENT 'title of the additional content',
199
  `content` mediumtext NOT NULL COMMENT 'the body of your additional content',
200
  `lang` varchar(50) NOT NULL DEFAULT '' COMMENT 'location for the additional content(koha is the staff interface, slip is the circulation receipt and language codes are for the opac)',
201
  `published_on` date DEFAULT NULL COMMENT 'publication date',
198
  `published_on` date DEFAULT NULL COMMENT 'publication date',
202
  `updated_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'last modification',
199
  `updated_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'last modification',
203
  `expirationdate` date DEFAULT NULL COMMENT 'date the additional content is set to expire or no longer be visible',
200
  `expirationdate` date DEFAULT NULL COMMENT 'date the additional content is set to expire or no longer be visible',
204
  `number` int(11) DEFAULT NULL COMMENT 'the order in which this additional content appears in that specific location',
201
  `number` int(11) DEFAULT NULL COMMENT 'the order in which this additional content appears in that specific location',
205
  `borrowernumber` int(11) DEFAULT NULL COMMENT 'The user who created the additional content',
202
  `borrowernumber` int(11) DEFAULT NULL COMMENT 'The user who created the additional content',
206
  PRIMARY KEY (`idnew`),
203
  PRIMARY KEY (`id`),
207
  UNIQUE KEY `additional_contents_uniq` (`category`,`code`,`branchcode`,`lang`),
204
  UNIQUE KEY `additional_contents_uniq` (`category`,`code`,`branchcode`),
208
  KEY `additional_contents_borrowernumber_fk` (`borrowernumber`),
205
  KEY `additional_contents_borrowernumber_fk` (`borrowernumber`),
209
  KEY `additional_contents_branchcode_ibfk` (`branchcode`),
206
  KEY `additional_contents_branchcode_ibfk` (`branchcode`),
210
  CONSTRAINT `additional_contents_branchcode_ibfk` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
207
  CONSTRAINT `additional_contents_branchcode_ibfk` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
Lines 212-217 CREATE TABLE `additional_contents` ( Link Here
212
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
209
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
213
/*!40101 SET character_set_client = @saved_cs_client */;
210
/*!40101 SET character_set_client = @saved_cs_client */;
214
211
212
CREATE TABLE `additional_contents_localizations` (
213
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for the additional content',
214
  `additional_content_id` int(10) unsigned NOT NULL COMMENT 'link to the additional content',
215
  `title` varchar(250) NOT NULL DEFAULT '' COMMENT 'title of the additional content',
216
  `content` mediumtext NOT NULL COMMENT 'the body of your additional content',
217
  `lang` varchar(50) NOT NULL DEFAULT '' COMMENT 'lang',
218
  PRIMARY KEY (`id`),
219
  UNIQUE KEY `additional_contents_localizations_uniq` (`additional_content_id`,`lang`),
220
  CONSTRAINT `additional_contents_localizations_ibfk1` FOREIGN KEY (`additional_content_id`) REFERENCES `additional_contents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
221
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
222
/*!40101 SET character_set_client = @saved_cs_client */;
223
215
--
224
--
216
-- Table structure for table `additional_field_values`
225
-- Table structure for table `additional_field_values`
217
--
226
--
218
- 

Return to bug 31383