installer/data/mysql/db_revs/210600016.pl

   1 use Modern::Perl;
   2
   3 return {
   4     bug_number => "24387",
   5     description => "Rename opac_news with additional_contents",
   6     up => sub {
   7         my ($args) = @_;
   8         my $dbh = $args->{dbh};
   9
  10         if( TableExists('opac_news') ) {
  11             $dbh->do(q|
  12                 ALTER TABLE opac_news RENAME additional_contents
  13             |);
  14         }
  15
  16         if ( foreign_key_exists('additional_contents', 'opac_news_branchcode_ibfk') ) {
  17
  18             $dbh->do(q|
  19                 ALTER TABLE additional_contents
  20                 DROP KEY borrowernumber_fk,
  21                 DROP KEY opac_news_branchcode_ibfk,
  22                 DROP FOREIGN KEY borrowernumber_fk,
  23                 DROP FOREIGN KEY opac_news_branchcode_ibfk
  24             |);
  25
  26             $dbh->do(q|
  27                 ALTER TABLE additional_contents
  28                 ADD CONSTRAINT  additional_contents_borrowernumber_fk
  29                     FOREIGN KEY (borrowernumber)
  30                     REFERENCES borrowers (borrowernumber) ON DELETE SET NULL ON UPDATE CASCADE
  31             |);
  32
  33             $dbh->do(q|
  34                 ALTER TABLE additional_contents
  35                 ADD CONSTRAINT  additional_contents_branchcode_ibfk
  36                     FOREIGN KEY (branchcode)
  37                     REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE
  38             |);
  39         }
  40
  41         $dbh->do(q|
  42             UPDATE letter
  43             SET content = REGEXP_REPLACE(content, '<<\\\\s*opac_news\.', '<<additional_contents.')
  44         |);
  45         $dbh->do(q|
  46             UPDATE letter
  47             SET content = REGEXP_REPLACE(content, '\\\\[%\\\\s*opac_news\.', '[% additional_contents.')
  48         |);
  49
  50         $dbh->do(q|
  51             UPDATE systempreferences
  52             SET variable="AdditionalContentsEditor"
  53             WHERE variable="NewsToolEditor"
  54         |);
  55
  56         $dbh->do(q|
  57             UPDATE permissions
  58             SET code="edit_additional_contents"
  59             WHERE code="edit_news"
  60         |);
  61
  62         unless ( column_exists('additional_contents', 'category' ) ) {
  63             $dbh->do(q|
  64                 ALTER TABLE additional_contents
  65                 ADD COLUMN `category` VARCHAR(20) NOT NULL COMMENT 'category for the additional content'
  66                 AFTER `idnew`
  67             |);
  68         }
  69         unless ( column_exists('additional_contents', 'location' ) ) {
  70             $dbh->do(q|
  71                 ALTER TABLE additional_contents
  72                 ADD COLUMN `location` VARCHAR(255) NOT NULL COMMENT 'location of the additional content'
  73                 AFTER `category`
  74             |);
  75         }
  76
  77         unless ( column_exists('additional_contents', 'code' ) ) {
  78             $dbh->do(q|
  79                 ALTER TABLE additional_contents
  80                 ADD COLUMN `code` VARCHAR(100) NOT NULL COMMENT 'code to group content per lang'
  81                 AFTER `category`
  82             |);
  83         }
  84
  85         my $contents = $dbh->selectall_arrayref(q|SELECT * FROM additional_contents|, { Slice => {} });
  86         for my $c ( @$contents ) {
  87             my ( $category, $location, $new_lang );
  88             if ( $c->{lang} eq '' ) {
  89                 $category = 'news';
  90                 $location = 'staff_and_opac';
  91                 $new_lang = 'default';
  92             } elsif ( $c->{lang} eq 'koha' ) {
  93                 $category = 'news';
  94                 $location = 'staff_only';
  95                 $new_lang = 'default';
  96             } elsif ( $c->{lang} eq 'slip' ) {
  97                 $category = 'news';
  98                 $location = 'slip';
  99                 $new_lang = 'default';
 100             } elsif ( $c->{lang} =~ m|_| ) {
 101                 ( $location, $new_lang ) = split '_', $c->{lang};
 102                 $category = 'html_customizations'
 103             } else {
 104                 $category = 'news';
 105                 $location = 'opac_only';
 106                 $new_lang = $c->{lang};
 107             }
 108
 109             die "There is something wrong here, we didn't find a valid category for idnew=" . $c->{idnew} unless $category;
 110
 111             # Now this is getting weird
 112             # We are adding an extra news with the same code when the lang is not "default" (/"en")
 113
 114             $new_lang = "default" if $new_lang eq 'en'; # Assume that "en" is "default"
 115
 116             my $sth_update = $dbh->prepare(q|
 117                 UPDATE additional_contents
 118                 SET category=?, location=?, lang=?
 119                 WHERE idnew=?
 120             |);
 121
 122             my $parent_idnew;
 123             if ( $new_lang ne 'default' ) {
 124                 $dbh->do(q|
 125                     INSERT INTO additional_contents(category, code, location, branchcode, title, content, lang, published_on, updated_on, expirationdate, number, borrowernumber)
 126                     VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
 127                 |, undef, $category, 'tmp_code', $location, $c->{branchcode}, $c->{title}, $c->{content}, 'default', $c->{published_on}, $c->{updated_on}, $c->{expirationdate}, $c->{number}, $c->{borrowernumber});
 128
 129                 $parent_idnew = $dbh->last_insert_id(undef, undef, 'additional_contents', undef);
 130             }
 131             $sth_update->execute($category, $location, $new_lang, $c->{idnew});
 132
 133             my $idnew = $parent_idnew || $c->{idnew};
 134             my $code = ( grep {$_ eq $location} qw( staff_and_opac staff_only opac_only slip ) ) ? "${location}_$idnew" : "News_$idnew";
 135             $dbh->do(q|UPDATE additional_contents SET code=? WHERE idnew = ?|, undef, $code, $parent_idnew) if $parent_idnew;
 136             $dbh->do(q|UPDATE additional_contents SET code=? WHERE idnew = ?|, undef, $code, $c->{idnew});
 137         }
 138
 139         $dbh->do(q|
 140             ALTER TABLE additional_contents
 141             ADD UNIQUE KEY additional_contents_uniq (`category`,`code`,`branchcode`,`lang`)
 142         |);
 143
 144
 145     },
 146 }