From 8c8b2279b29fd261a724243c2240db4fcb661cbb Mon Sep 17 00:00:00 2001
From: Martin Persson <xarragon@gmail.com>
Date: Tue, 26 May 2015 13:23:34 +0200
Subject: [PATCH] Bug 14246: Add borrowernumber to koha_news

This patch adds a new column to koha_news that links a
borrowernumber to each item. This allows Koha to display the
author for each entry which makes it suitable as a simple CMS.

Changes (from rejected patch):
* Added missing kohastructure.sql changes.
* Turned the atomic update file into a SQL file and changed name
  in accordance with wiki guidelines.
* Changed SQL syntax and naming to be consistent with existing code.
* Attached test plan to commit message.

Test plan:

* You need at least one news item, log in and add one from
  'Administration' -> 'Tools' -> 'News'.
* Apply the patch.
* Apply database upgrade (directly or indirectly).
* Go back to Staff interface and post a new news item.
* Use the mysql command line client to connect to the Koha installation's database:
  mysql -u kohaadmin -p -D koha (change username and database to values
  appropiate for your system).
* Execute the following query: SELECT title, borrowernumber FROM opac_news;
* Inspect the results, the 'borrowernumber' column should be NULL for all news items
  created before the patch was applied, and non-NULL for the new item created AFTER
  the patch was applied.

Optional: Remove database changes via:
  ALTER TABLE opac_news DROP FOREIGN KEY borrowernumber_fk;
  ALTER TABLE opac_news DROP COLUMN borrowernumber;

Sponsored-By: Halland County Library
---
 installer/data/mysql/atomicupdate/bug_14246-add_news_author.sql | 2 ++
 installer/data/mysql/kohastructure.sql                          | 2 ++
 tools/koha-news.pl                                              | 1 +
 3 files changed, 5 insertions(+)
 create mode 100644 installer/data/mysql/atomicupdate/bug_14246-add_news_author.sql

diff --git a/installer/data/mysql/atomicupdate/bug_14246-add_news_author.sql b/installer/data/mysql/atomicupdate/bug_14246-add_news_author.sql
new file mode 100644
index 0000000..1319d37
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_14246-add_news_author.sql
@@ -0,0 +1,2 @@
+ALTER TABLE `opac_news` ADD `borrowernumber` INT(11) default NULL;
+ALTER TABLE `opac_news` ADD CONSTRAINT `borrowernumber_fk` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers`(`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL;
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index c41710c..5eb7eed 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1694,7 +1694,9 @@ CREATE TABLE `opac_news` ( -- data from the news tool
   `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, -- pulibcation date and time
   `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
   PRIMARY KEY  (`idnew`),
+  CONSTRAINT `borrowernumber_fk` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
   CONSTRAINT opac_news_branchcode_ibfk FOREIGN KEY (branchcode) REFERENCES branches (branchcode)
     ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
diff --git a/tools/koha-news.pl b/tools/koha-news.pl
index cf1e2b4..3a6c596 100755
--- a/tools/koha-news.pl
+++ b/tools/koha-news.pl
@@ -114,6 +114,7 @@ elsif ( $op eq 'add' ) {
                 timestamp      => $timestamp,
                 number         => $number,
                 branchcode     => $branchcode,
+                borrowernumber => $borrowernumber,
             }
         );
         print $cgi->redirect("/cgi-bin/koha/tools/koha-news.pl");
-- 
2.1.4