From 52314eae56cba00907502997f2007e91221158e9 Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Tue, 17 Jul 2018 15:47:57 +0200 Subject: [PATCH] Bug 21066: Database revision Content-Type: text/plain; charset=utf-8 *Convert* timestamp to publisheddate, add updated_on as 'real' timestamp. --- installer/data/mysql/atomicupdate/opac_news.perl | 15 +++++++++++++++ installer/data/mysql/kohastructure.sql | 3 ++- 2 files changed, 17 insertions(+), 1 deletion(-) create mode 100644 installer/data/mysql/atomicupdate/opac_news.perl diff --git a/installer/data/mysql/atomicupdate/opac_news.perl b/installer/data/mysql/atomicupdate/opac_news.perl new file mode 100644 index 0000000..f420f76 --- /dev/null +++ b/installer/data/mysql/atomicupdate/opac_news.perl @@ -0,0 +1,15 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + $dbh->do( q| +ALTER TABLE opac_news ADD COLUMN (publisheddate DATE NOT NULL) + |); + $dbh->do( q| +UPDATE opac_news SET publisheddate = CAST(timestamp AS DATE) + |); + $dbh->do( q| +ALTER TABLE opac_news DROP COLUMN timestamp, ADD COLUMN (updated_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) + |); + + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (opac_news: updated_on)\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index dafc208..4521063 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1836,10 +1836,11 @@ CREATE TABLE `opac_news` ( -- data from the news tool `title` varchar(250) NOT NULL default '', -- title of the news article `new` text NOT NULL, -- the body of your news article `lang` varchar(25) NOT NULL default '', -- location for the article (koha is the staff client, slip is the circulation receipt and language codes are for the opac) - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, -- pulibcation date and time + `publisheddate` date NOT NULL, -- pulibcation date `expirationdate` date default NULL, -- date the article is set to expire or no longer be visible `number` int(11) default NULL, -- the order in which this article appears in that specific location `borrowernumber` int(11) default NULL, -- The user who created the news article + `updated_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`idnew`), CONSTRAINT `borrowernumber_fk` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT opac_news_branchcode_ibfk FOREIGN KEY (branchcode) REFERENCES branches (branchcode) -- 2.1.4