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
        }, { Slice => {} });
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 718-723 tables: Link Here
718
          message_transport_type: email
718
          message_transport_type: email
719
          lang: default
719
          lang: default
720
          content:
720
          content:
721
            - "[%~ USE KohaDates ~%]"
721
            - "<h3><<branches.branchname>></h3>"
722
            - "<h3><<branches.branchname>></h3>"
722
            - "Checked out to <<borrowers.title>> <<borrowers.firstname>> <<borrowers.initials>> <<borrowers.surname>> <br />"
723
            - "Checked out to <<borrowers.title>> <<borrowers.firstname>> <<borrowers.initials>> <<borrowers.surname>> <br />"
723
            - "(<<borrowers.cardnumber>>) <br />"
724
            - "(<<borrowers.cardnumber>>) <br />"
Lines 768-782 tables: Link Here
768
            - ""
769
            - ""
769
            - "<hr>"
770
            - "<hr>"
770
            - ""
771
            - ""
772
            - "[% IF additional_contents.count %]"
771
            - "<h4 style=\"text-align: center; font-style:italic;\">News</h4>"
773
            - "<h4 style=\"text-align: center; font-style:italic;\">News</h4>"
772
            - "<news>"
774
            - "[% FOREACH content IN additional_contents %]"
773
            - "<div class=\"newsitem\">"
775
            - "<div class=\"newsitem\">"
774
            - "<h5 style=\"margin-bottom: 1px; margin-top: 1px\"><b><<additional_contents.title>></b></h5>"
776
            - "<h5 style=\"margin-bottom: 1px; margin-top: 1px\"><b>[% content.title %]</b></h5>"
775
            - "<p style=\"margin-bottom: 1px; margin-top: 1px\"><<additional_contents.content>></p>"
777
            - "<p style=\"margin-bottom: 1px; margin-top: 1px\">[% content.content %]</p>"
776
            - "<p class=\"newsfooter\" style=\"font-size: 8pt; font-style:italic; margin-bottom: 1px; margin-top: 1px\">Posted on <<additional_contents.published_on>></p>"
778
            - "<p class=\"newsfooter\" style=\"font-size: 8pt; font-style:italic; margin-bottom: 1px; margin-top: 1px\">Posted on [% content.published_on | $KohaDates %]</p>"
777
            - "<hr />"
779
            - "<hr />"
778
            - "</div>"
780
            - "</div>"
779
            - "</news>"
781
            - "[% END %]"
782
            - "[% END %]"
780
783
781
        - module: circulation
784
        - module: circulation
782
          code: ODUE
785
          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 191-211 DROP TABLE IF EXISTS `additional_contents`; Link Here
191
/*!40101 SET @saved_cs_client     = @@character_set_client */;
191
/*!40101 SET @saved_cs_client     = @@character_set_client */;
192
/*!40101 SET character_set_client = utf8 */;
192
/*!40101 SET character_set_client = utf8 */;
193
CREATE TABLE `additional_contents` (
193
CREATE TABLE `additional_contents` (
194
  `idnew` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for the additional content',
194
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for the additional content category',
195
  `category` varchar(20) NOT NULL COMMENT 'category for the additional content',
195
  `category` varchar(20) NOT NULL COMMENT 'category for the additional content',
196
  `code` varchar(100) NOT NULL COMMENT 'code to group content per lang',
196
  `code` varchar(100) NOT NULL COMMENT 'code to group content per lang',
197
  `location` varchar(255) NOT NULL COMMENT 'location of the additional content',
197
  `location` varchar(255) NOT NULL COMMENT 'location of the additional content',
198
  `branchcode` varchar(10) DEFAULT NULL COMMENT 'branch code users to create branch specific additional content, NULL is every branch.',
198
  `branchcode` varchar(10) DEFAULT NULL COMMENT 'branch code users to create branch specific additional content, NULL is every branch.',
199
  `title` varchar(250) NOT NULL DEFAULT '' COMMENT 'title of the additional content',
200
  `content` mediumtext NOT NULL COMMENT 'the body of your additional content',
201
  `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)',
202
  `published_on` date DEFAULT NULL COMMENT 'publication date',
199
  `published_on` date DEFAULT NULL COMMENT 'publication date',
203
  `updated_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'last modification',
200
  `updated_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'last modification',
204
  `expirationdate` date DEFAULT NULL COMMENT 'date the additional content is set to expire or no longer be visible',
201
  `expirationdate` date DEFAULT NULL COMMENT 'date the additional content is set to expire or no longer be visible',
205
  `number` int(11) DEFAULT NULL COMMENT 'the order in which this additional content appears in that specific location',
202
  `number` int(11) DEFAULT NULL COMMENT 'the order in which this additional content appears in that specific location',
206
  `borrowernumber` int(11) DEFAULT NULL COMMENT 'The user who created the additional content',
203
  `borrowernumber` int(11) DEFAULT NULL COMMENT 'The user who created the additional content',
207
  PRIMARY KEY (`idnew`),
204
  PRIMARY KEY (`id`),
208
  UNIQUE KEY `additional_contents_uniq` (`category`,`code`,`branchcode`,`lang`),
205
  UNIQUE KEY `additional_contents_uniq` (`category`,`code`,`branchcode`),
209
  KEY `additional_contents_borrowernumber_fk` (`borrowernumber`),
206
  KEY `additional_contents_borrowernumber_fk` (`borrowernumber`),
210
  KEY `additional_contents_branchcode_ibfk` (`branchcode`),
207
  KEY `additional_contents_branchcode_ibfk` (`branchcode`),
211
  CONSTRAINT `additional_contents_branchcode_ibfk` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
208
  CONSTRAINT `additional_contents_branchcode_ibfk` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
Lines 213-218 CREATE TABLE `additional_contents` ( Link Here
213
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
210
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
214
/*!40101 SET character_set_client = @saved_cs_client */;
211
/*!40101 SET character_set_client = @saved_cs_client */;
215
212
213
CREATE TABLE `additional_contents_localizations` (
214
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for the additional content',
215
  `additional_content_id` int(10) unsigned NOT NULL COMMENT 'link to the additional content',
216
  `title` varchar(250) NOT NULL DEFAULT '' COMMENT 'title of the additional content',
217
  `content` mediumtext NOT NULL COMMENT 'the body of your additional content',
218
  `lang` varchar(50) NOT NULL DEFAULT '' COMMENT 'lang',
219
  PRIMARY KEY (`id`),
220
  UNIQUE KEY `additional_contents_localizations_uniq` (`additional_content_id`,`lang`),
221
  CONSTRAINT `additional_contents_localizations_ibfk1` FOREIGN KEY (`additional_content_id`) REFERENCES `additional_contents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
222
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
223
/*!40101 SET character_set_client = @saved_cs_client */;
224
216
--
225
--
217
-- Table structure for table `additional_field_values`
226
-- Table structure for table `additional_field_values`
218
--
227
--
219
- 

Return to bug 31383