From e6070d1314f3161a346371154beb02c24506454a Mon Sep 17 00:00:00 2001
From: Malf Furious <m@lfurio.us>
Date: Sat, 8 Apr 2017 20:39:33 -0400
Subject: Remove NULLs from database schema

To address potential bugs in the application code, I have updated the data
definition to forbid NULL values anywhere in the Scrott database.
Additionally, the data type for issue due datetimes is updated to
varchar since 'datetime' has issues with my desired default value.
---
 schema.sql | 28 ++++++++++++++--------------
 1 file changed, 14 insertions(+), 14 deletions(-)

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
index e6826cf..9b50f7a 100644
--- a/schema.sql
+++ b/schema.sql
@@ -38,7 +38,7 @@ USE db_scrott;
 DROP TABLE IF EXISTS settings;
 CREATE TABLE settings (
     name        varchar(64)         NOT NULL,
-    value       varchar(64)     DEFAULT NULL,
+    value       varchar(64)         NOT NULL    DEFAULT '',
 
     PRIMARY KEY (name)
 );
@@ -92,8 +92,8 @@ CREATE TABLE views (
 DROP TABLE IF EXISTS objects;
 CREATE TABLE objects (
     guid        varchar(8)          NOT NULL,
-    owner       varchar(8)      DEFAULT NULL,
-    parent      varchar(8)      DEFAULT NULL,
+    owner       varchar(8)          NOT NULL    DEFAULT '',
+    parent      varchar(8)          NOT NULL    DEFAULT '',
     name        varchar(64)         NOT NULL,
     created     datetime            NOT NULL,
     updated     datetime            NOT NULL,
@@ -129,8 +129,8 @@ CREATE TABLE users (
     guid        varchar(8)          NOT NULL,
     auth        varchar(64)         NOT NULL,                   /* user's salted and hashed passwd -- SHA256 */
     salt        varchar(64)         NOT NULL,                   /* random SHA256 output, used as salt for auth */
-    alias       varchar(64)     DEFAULT NULL,
-    email       varchar(64)     DEFAULT NULL,
+    alias       varchar(64)         NOT NULL    DEFAULT '',
+    email       varchar(64)         NOT NULL    DEFAULT '',
     emailVer    varchar(8)          NOT NULL,
     admin       int(1)              NOT NULL    DEFAULT 0,
     reg         int(1)              NOT NULL    DEFAULT 0,      /* if false, user doesn't have valid credentials */
@@ -155,7 +155,7 @@ CREATE TABLE groups (
 DROP TABLE IF EXISTS pads;
 CREATE TABLE pads (
     guid        varchar(8)          NOT NULL,
-    stage       varchar(8)      DEFAULT NULL,
+    stage       varchar(8)          NOT NULL    DEFAULT '',
     issueNumb   int(32)             NOT NULL    DEFAULT 0,
 
     PRIMARY KEY (guid)
@@ -167,7 +167,7 @@ CREATE TABLE pads (
 DROP TABLE IF EXISTS stages;
 CREATE TABLE stages (
     guid        varchar(8)          NOT NULL,
-    stage       varchar(8)      DEFAULT NULL,
+    stage       varchar(8)          NOT NULL    DEFAULT '',
 
     PRIMARY KEY (guid)
 );
@@ -179,11 +179,11 @@ DROP TABLE IF EXISTS issues;
 CREATE TABLE issues (
     guid        varchar(8)          NOT NULL,
     numb        int(32)             NOT NULL,
-    assignee    varchar(8)      DEFAULT NULL,
+    assignee    varchar(8)          NOT NULL    DEFAULT '',
     seen        int(1)              NOT NULL    DEFAULT 0,      /* has the assignee seen this yet? */
-    description text            DEFAULT NULL,
-    due         datetime        DEFAULT NULL,
-    tags        varchar(64)     DEFAULT NULL,
+    description text                NOT NULL    DEFAULT '',
+    due         varchar(64)         NOT NULL    DEFAULT '',
+    tags        varchar(64)         NOT NULL    DEFAULT '',
 
     PRIMARY KEY (guid)
 );
@@ -195,9 +195,9 @@ DROP TABLE IF EXISTS mesgs;
 CREATE TABLE mesgs (
     guid        varchar(8)          NOT NULL,
     author      varchar(8)          NOT NULL,
-    mesg        text            DEFAULT NULL,
-    attachName  varchar(64)     DEFAULT NULL,
-    attachMime  varchar(64)     DEFAULT NULL,
+    mesg        text                NOT NULL    DEFAULT '',
+    attachName  varchar(64)         NOT NULL    DEFAULT '',
+    attachMime  varchar(64)         NOT NULL    DEFAULT '',
 
     PRIMARY KEY (guid)
 );
-- 
cgit v1.2.3

