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(-)
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