From f2eb5f907eca7387a26189a7d890ef278a176d5c Mon Sep 17 00:00:00 2001
From: Dietmar Maurer <dietmar@proxmox.com>
Date: Thu, 28 Dec 2017 11:17:16 +0100
Subject: [PATCH] PMG/DBTools.pm: add missing TicketID column to CMSReceivers

---
 PMG/DBTools.pm | 31 +++++++++++++++++++++++++++++++
 1 file changed, 31 insertions(+)

diff --git a/PMG/DBTools.pm b/PMG/DBTools.pm
index e2e45c3..266e78c 100644
--- a/PMG/DBTools.pm
+++ b/PMG/DBTools.pm
@@ -253,6 +253,7 @@ my $cmailstore_ctablecmd =  <<__EOD;
      CMailStore_RID INTEGER NOT NULL,
      PMail VARCHAR(255) NOT NULL,
      Receiver VARCHAR(255),
+     TicketID INTEGER NOT NULL,
      Status "char" NOT NULL,
      MTime INTEGER NOT NULL);
 
@@ -329,6 +330,17 @@ sub cond_create_dbtable {
     }
 }
 
+sub database_column_exists {
+    my ($dbh, $table, $column) = @_;
+
+    my $sth = $dbh->prepare(
+	"SELECT column_name FROM information_schema.columns " .
+	"WHERE table_name = ? and column_name = ?");
+    $sth->execute(lc($table), lc($column));
+    my $res = $sth->fetchrow_hashref();
+    return defined($res);
+}
+
 sub create_ruledb {
     my ($dbname) = @_;
 
@@ -479,6 +491,25 @@ sub upgradedb {
 		 "PregreetCount INTEGER DEFAULT 0 NOT NULL");
     };
 
+    # add missing TicketID to CMSReceivers
+    if (!database_column_exists($dbh, 'CMSReceivers', 'TicketID')) {
+	eval {
+	    $dbh->begin_work;
+	    $dbh->do("CREATE SEQUENCE cmsreceivers_ticketid_seq");
+	    $dbh->do("ALTER TABLE CMSReceivers ADD COLUMN " .
+		     "TicketID INTEGER NOT NULL " .
+		     "DEFAULT nextval('cmsreceivers_ticketid_seq')");
+	    $dbh->do("ALTER TABLE CMSReceivers ALTER COLUMN " .
+		     "TicketID DROP DEFAULT");
+	    $dbh->do("DROP SEQUENCE cmsreceivers_ticketid_seq");
+	    $dbh->commit;
+	};
+	if (my $err = $@) {
+	    $dbh->rollback;
+	    die $err;
+	}
+    }
+
     # update obsolete content type names
     eval {
 	$dbh->do("UPDATE Object " .
-- 
2.39.5