From 479fa31398d18f105616de83b5b5108278b75c59 Mon Sep 17 00:00:00 2001
From: Malf Furious <m@lfurio.us>
Date: Sun, 21 Oct 2018 21:18:21 -0400
Subject: issue:  Redesign schema

I found myself complicating the data model of this class of objects and
wanted to take a clean approach to its design.  The key differences are
as follows:

        * We now reference a message object for the issue's OP, as
        opposed to directly containing the message data

        This affords the OP _all_ of the standard features of a Scrott
        message, including separately tracked authorship data, file
        attachments.

        * Multiple assignees is implemented in the design

        Finally.

        * Seen flag is removed

        This can be implicitly tracked via all sub-object messages and
        the views meta-table.
---
 srvs/mysql.sql | 31 +++++++++++++++++++++----------
 1 file changed, 21 insertions(+), 10 deletions(-)

(limited to 'srvs/mysql.sql')

diff --git a/srvs/mysql.sql b/srvs/mysql.sql
index 9bad437..72167d4 100644
--- a/srvs/mysql.sql
+++ b/srvs/mysql.sql
@@ -69,6 +69,23 @@ CREATE TABLE views (
     PRIMARY KEY (guid, viewer)
 );
 
+/*
+ * Scrott issues may have multiple assignees.  This table is used to
+ * co-relate assignees and issues along with additional meta-data.
+ */
+DROP TABLE IF EXISTS assignees;
+CREATE TABLE assignees (
+    guid        varchar(8)          NOT NULL,                   /* guid of issue */
+    assignee    varchar(8)          NOT NULL,                   /* user */
+    assigner    varchar(8)          NOT NULL,                   /* user */
+    assigned    datetime            NOT NULL,                   /* timestamp */
+    dismisser   varchar(8)          NOT NULL    DEFAULT '',     /* user */
+    dismissed   varchar(64)         NOT NULL    DEFAULT '',     /* timestamp */
+    signedoff   varchar(64)         NOT NULL    DEFAULT '',     /* timestamp */
+
+    PRIMARY KEY (guid, assignee)
+);
+
 /*
  * Base table for Scrott objects
  *
@@ -179,16 +196,10 @@ DROP TABLE IF EXISTS issues;
 CREATE TABLE issues (
     guid        varchar(8)          NOT NULL,
     numb        int(32)             NOT NULL    DEFAULT 0,
-    assignee    varchar(8)          NOT NULL    DEFAULT '',
-    author      varchar(8)          NOT NULL    DEFAULT '',
-    closer      varchar(8)          NOT NULL    DEFAULT '',
-    seen        int(1)              NOT NULL    DEFAULT 0,      /* has the assignee seen this yet? */
-    description text                NOT NULL,
-    opened      varchar(64)         NOT NULL    DEFAULT '',
-    assigned    varchar(64)         NOT NULL    DEFAULT '',
-    authored    varchar(64)         NOT NULL    DEFAULT '',
-    closed      varchar(64)         NOT NULL    DEFAULT '',
-    due         varchar(64)         NOT NULL    DEFAULT '',
+    mesg        varchar(8)          NOT NULL    DEFAULT '',
+    closer      varchar(8)          NOT NULL    DEFAULT '',     /* user */
+    closed      varchar(64)         NOT NULL    DEFAULT '',     /* timestamp */
+    due         varchar(64)         NOT NULL    DEFAULT '',     /* timestamp */
     tags        text                NOT NULL,
 
     PRIMARY KEY (guid)
-- 
cgit v1.2.3

