From ae93d37296b45dd7adee05018741cd50baa97310 Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc kop@karlpinc.com" <kop@karlpinc.com>
Date: Sat, 20 Jan 2024 20:56:03 +0000
Subject: [PATCH] Make a PEOPLE table in the "clean" schema to support bad data
 queries

---
 conversion/clean.sql     | 27 +++++++++++++++++++++++++++
 conversion/load_data.sql |  8 +++-----
 2 files changed, 30 insertions(+), 5 deletions(-)

diff --git a/conversion/clean.sql b/conversion/clean.sql
index 7a23fd9..c88772c 100644
--- a/conversion/clean.sql
+++ b/conversion/clean.sql
@@ -17,3 +17,30 @@
 --
 -- Karl O. Pinc <kop@karlpinc.com>
 
+-- Create a PEOPLE table, so we can query bad data.
+
+CREATE TABLE people (
+   person TEXT PRIMARY KEY NOT NULL
+                     CONSTRAINT "Person: Cannot be empty or nothing but spaces"
+                CHECK(trim(from Person) <> '')
+                     CONSTRAINT "Person: Cannot contain any spaces"
+                CHECK(Person IS NULL OR POSITION(' ' IN Person) = 0)
+  ,name TEXT NOT NULL
+                   CONSTRAINT "Name: Cannot be empty or nothing but spaces"
+                CHECK(trim(from Name) <> '')
+  ,description TEXT NOT NULL
+                     CONSTRAINT "Description: Cannot be nothing but spaces"
+                CHECK(Description IS NULL OR Description = '' OR trim(from Description) <> '')
+);
+
+GRANT SELECT ON PEOPLE TO GROUP reader;
+GRANT SELECT ON PEOPLE TO GROUP writer;
+
+
+INSERT INTO people (person, name, description)
+  VALUES ('EVL', 'EVL', '')
+       , ('JR', 'JR', '')
+       , ('KW', 'KW', '')
+       , ('SF', 'SF', '')
+       , ('ICG', 'ICG', '')
+       , ('UNK', 'Unknown', 'The unknown person');
diff --git a/conversion/load_data.sql b/conversion/load_data.sql
index ff89330..8f245db 100644
--- a/conversion/load_data.sql
+++ b/conversion/load_data.sql
@@ -45,12 +45,10 @@ INSERT INTO entrytypes (entrytype, description)
        , ('O', 'Initiation of close observation');
 
 -- people
+-- Copied from clean.people, which we manually constructed.
 INSERT INTO people (person, name, description, active)
-  VALUES ('EVL', 'EVL', '', true)
-       , ('JR', 'JR', '', true)
-       , ('KW', 'KW', '', true)
-       , ('SF', 'SF', '', true)
-       , ('ICG', 'ICG', '', true);
+  SELECT person, name, description, TRUE
+    FROM clean.people;
 
 
 --  Data tables
-- 
2.34.1