<commit_before>SET SESSION SQL_MODE = 'ansi,strict_all_tables';

-- =============================================================================================
-- Delete permissions due to MySQL stupid integrity ordering

DROP TABLE IF EXISTS "ROLE_PERMISSIONS";
DROP TABLE IF EXISTS "USER_ROLES";
DROP TABLE IF EXISTS "ROLES";
DROP TABLE IF EXISTS "USERS";

DROP TABLE IF EXISTS "AUDIT_LOG";

DROP TABLE IF EXISTS "CASE_ATTRIBUTE_STRINGS";
DROP TABLE IF EXISTS "CASE_ATTRIBUTE_BOOLEANS";
DROP TABLE IF EXISTS "CASE_ATTRIBUTE_DATES";
DROP TABLE IF EXISTS "CASE_ATTRIBUTE_NUMBERS";
DROP TABLE IF EXISTS "CASES";
DROP TABLE IF EXISTS "VIEW_ATTRIBUTES";
DROP TABLE IF EXISTS "VIEWS";
DROP TABLE IF EXISTS "ATTRIBUTES";
DROP TABLE IF EXISTS "STUDIES";

-- =============================================================================================
-- Now for the studies

CREATE TABLE "STUDIES" (
  "ID" INTEGER AUTO_INCREMENT PRIMARY KEY,
  "NAME" VARCHAR(24) NOT NULL,
  "DESCRIPTION" VARCHAR(2048),
  "ABOUT" TEXT,
  "OPTIONS" VARCHAR(2048),
  UNIQUE ("NAME")
);

INSERT INTO "STUDIES" ("NAME", "DESCRIPTION") VALUES ('ADMIN', 'Admin study');
UPDATE "STUDIES" SET "ID" = 0 WHERE "NAME" = 'ADMIN';
INSERT INTO "STUDIES" ("NAME", "DESCRIPTION", "OPTIONS", "ABOUT") VALUES ('DEMO', 'A demo clinical genomics study', '{"dateFormat":"D-MMM-YYYY","stateLabels":{"pending":"label1","returned":"label2"},"stateRules":[{"state":"pending","attribute":"returnRequested","value":"true"}]}', '#### Markdown-based description');
INSERT INTO "STUDIES" ("NAME", "DESCRIPTION", "ABOUT") VALUES ('SECOND', 'A second study', '* Bulleted list description');
INSERT INTO "STUDIES" ("NAME", "DESCRIPTION", "ABOUT") VALUES ('ALPHABETICAL', 'A third study', '* Bulleted list description');

-- =============================================================================================
-- Now for the attributes


CREATE TABLE "ATTRIBUTES" (
  "ID" INTEGER AUTO_INCREMENT PRIMARY KEY,
  "STUDY_ID" INTEGER NOT NULL REFERENCES "STUDIES"("ID"),
  "NAME" VARCHAR(48) NOT NULL,
  "DESCRIPTION" VARCHAR(2048),
  "LABEL" VARCHAR(128) NOT NULL,
  "TYPE" VARCHAR(24) NOT NULL,
  "RANK" INTEGER DEFAULT 0 NOT NULL,
  "OPTIONS" VARCHAR(2048),
  UNIQUE("STUDY_ID", "NAME")
);

INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (1, 1, 1, 'dateEntered', 'Date Entered', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE", "OPTIONS") VALUES (2, 2, 1, 'patientId', 'Patient ID', 'string', '{"unique":true,"display":"pin_left"}');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE", "OPTIONS") VALUES (3, 3, 1, 'mrn', 'MRN', 'string', '{"tags": ["identifiable"]}');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (4, 4, 1, 'primarySite', 'Primary Disease Site', 'string');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (5, 5, 1, 'consentDate', 'Date of Consent', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE", "OPTIONS") VALUES (6, 6, 1, 'physician', 'Treating Physician', 'string', '{"tags": ["identifiable"]}');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE", "OPTIONS") VALUES (7, 7, 1, 'sampleAvailable', 'Sample Available At (Hospital/Lab)', 'option', '{"values": ["LMP","St. Michaels","Toronto East General"]}');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (8, 8, 1, 'specimenNo', 'Specimen #', 'string');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (9, 9, 1, 'procedureDate', 'Date of Procedure', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (10, 10, 1, 'tissueSite', 'Tissue Site', 'string');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (11, 11, 1, 'trackerDate', 'Date Internal Request Entered in Tracker', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (12, 12, 1, 'specimenAvailable', 'Biobank Specimen Available? (Yes/No)', 'boolean');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (13, 13, 1, 'biobankDate', 'Date Column M (Biobank Yes/No) Entered', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (14, 14, 1, 'biobankReason', 'Reason Tissue Not Banked', 'string');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (15, 15, 1, 'requestDate', 'Date Request Processed', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (16, 16, 1, 'LMPComments', 'LMP Comments', 'string');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (17, 17, 1, 'geneMutation', 'Gene >> Mutation (Frequency, Class)', 'string');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (18, 18, 1, 'numberCores', 'Number of cores', 'number');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (19, 19, 1, 'specimenBiobankDate', 'Date Specimen went to Biobank', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (20, 20, 1, 'specimenType', 'Specimen Type Collected by CSP (block/slides)', 'string');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (21, 21, 1, 'diagnosticsDate', 'Date of transfer to Molecular Diagnostics', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (22, 22, 1, 'returnRequested', 'Return Requested? (Yes/No)', 'boolean');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (23, 23, 1, 'returnDate', 'Date of Return to Outside Institution', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (24, 24, 1, 'bloodCollDate', 'Date and Time of Blood Collection', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (25, 25, 1, 'insufficientDate', 'Date Tissue Deemed Insufficient', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE", "OPTIONS") VALUES (26, 26, 1, 'notes', 'Notes', 'string', '{"longtext":true}');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (27, 27, 1, 'study', 'Study', 'string');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE", "OPTIONS") VALUES (28, 28, 1, 'diagnosticsDays', 'Days from consent to diagnostics', 'number', '{"calculated":"netWorkingDays(record.get(\"dateEntered\"), record.get(\"diagnosticsDate\"))"}');

INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (32, 1, 2, 'patientId', 'Patient ID', 'string');

-- =============================================================================================
-- Now for the views

CREATE TABLE "VIEWS" (
  "ID" INTEGER AUTO_INCREMENT PRIMARY KEY,
  "STUDY_ID" INTEGER NOT NULL REFERENCES "STUDIES"("ID"),
  "NAME" VARCHAR(24) NOT NULL,
  "DESCRIPTION" VARCHAR(2048),
  "BODY" CLOB,
  "OPTIONS" VARCHAR(2048),
  UNIQUE ("STUDY_ID", "NAME")
);

INSERT INTO "VIEWS" ("ID", "STUDY_ID", "DESCRIPTION", "NAME") VALUES (1, 1, 'Manages the whole study', 'complete');
INSERT INTO "VIEWS" ("ID", "STUDY_ID", "DESCRIPTION", "NAME") VALUES (2, 1, 'Tracks the study', 'track');
INSERT INTO "VIEWS" ("ID", "STUDY_ID", "DESCRIPTION", "NAME", "OPTIONS") VALUES (3, 1, 'Tracks only secondary', 'secondary', '{"rows":[{"attribute":"study","value":"secondary"}]}');
INSERT INTO "VIEWS" ("ID", "STUDY_ID", "DESCRIPTION", "NAME", "BODY") VALUES (4, 1, 'Enter data', 'enter', '<h3>Enter data</h3>');

INSERT INTO "VIEWS" ("ID", "STUDY_ID", "DESCRIPTION", "NAME") VALUES (5, 2, 'Manages the whole study', 'complete');

-- =============================================================================================
-- Now for the view attributes
<commit_msg>CLOB -> LONGTEXT for MySQL<commit_after>SET SESSION SQL_MODE = 'ansi,strict_all_tables';

-- =============================================================================================
-- Delete permissions due to MySQL stupid integrity ordering

DROP TABLE IF EXISTS "ROLE_PERMISSIONS";
DROP TABLE IF EXISTS "USER_ROLES";
DROP TABLE IF EXISTS "ROLES";
DROP TABLE IF EXISTS "USERS";

DROP TABLE IF EXISTS "AUDIT_LOG";

DROP TABLE IF EXISTS "CASE_ATTRIBUTE_STRINGS";
DROP TABLE IF EXISTS "CASE_ATTRIBUTE_BOOLEANS";
DROP TABLE IF EXISTS "CASE_ATTRIBUTE_DATES";
DROP TABLE IF EXISTS "CASE_ATTRIBUTE_NUMBERS";
DROP TABLE IF EXISTS "CASES";
DROP TABLE IF EXISTS "VIEW_ATTRIBUTES";
DROP TABLE IF EXISTS "VIEWS";
DROP TABLE IF EXISTS "ATTRIBUTES";
DROP TABLE IF EXISTS "STUDIES";

-- =============================================================================================
-- Now for the studies

CREATE TABLE "STUDIES" (
  "ID" INTEGER AUTO_INCREMENT PRIMARY KEY,
  "NAME" VARCHAR(24) NOT NULL,
  "DESCRIPTION" VARCHAR(2048),
  "ABOUT" TEXT,
  "OPTIONS" VARCHAR(2048),
  UNIQUE ("NAME")
);

INSERT INTO "STUDIES" ("NAME", "DESCRIPTION") VALUES ('ADMIN', 'Admin study');
UPDATE "STUDIES" SET "ID" = 0 WHERE "NAME" = 'ADMIN';
INSERT INTO "STUDIES" ("NAME", "DESCRIPTION", "OPTIONS", "ABOUT") VALUES ('DEMO', 'A demo clinical genomics study', '{"dateFormat":"D-MMM-YYYY","stateLabels":{"pending":"label1","returned":"label2"},"stateRules":[{"state":"pending","attribute":"returnRequested","value":"true"}]}', '#### Markdown-based description');
INSERT INTO "STUDIES" ("NAME", "DESCRIPTION", "ABOUT") VALUES ('SECOND', 'A second study', '* Bulleted list description');
INSERT INTO "STUDIES" ("NAME", "DESCRIPTION", "ABOUT") VALUES ('ALPHABETICAL', 'A third study', '* Bulleted list description');

-- =============================================================================================
-- Now for the attributes


CREATE TABLE "ATTRIBUTES" (
  "ID" INTEGER AUTO_INCREMENT PRIMARY KEY,
  "STUDY_ID" INTEGER NOT NULL REFERENCES "STUDIES"("ID"),
  "NAME" VARCHAR(48) NOT NULL,
  "DESCRIPTION" VARCHAR(2048),
  "LABEL" VARCHAR(128) NOT NULL,
  "TYPE" VARCHAR(24) NOT NULL,
  "RANK" INTEGER DEFAULT 0 NOT NULL,
  "OPTIONS" VARCHAR(2048),
  UNIQUE("STUDY_ID", "NAME")
);

INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (1, 1, 1, 'dateEntered', 'Date Entered', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE", "OPTIONS") VALUES (2, 2, 1, 'patientId', 'Patient ID', 'string', '{"unique":true,"display":"pin_left"}');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE", "OPTIONS") VALUES (3, 3, 1, 'mrn', 'MRN', 'string', '{"tags": ["identifiable"]}');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (4, 4, 1, 'primarySite', 'Primary Disease Site', 'string');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (5, 5, 1, 'consentDate', 'Date of Consent', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE", "OPTIONS") VALUES (6, 6, 1, 'physician', 'Treating Physician', 'string', '{"tags": ["identifiable"]}');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE", "OPTIONS") VALUES (7, 7, 1, 'sampleAvailable', 'Sample Available At (Hospital/Lab)', 'option', '{"values": ["LMP","St. Michaels","Toronto East General"]}');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (8, 8, 1, 'specimenNo', 'Specimen #', 'string');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (9, 9, 1, 'procedureDate', 'Date of Procedure', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (10, 10, 1, 'tissueSite', 'Tissue Site', 'string');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (11, 11, 1, 'trackerDate', 'Date Internal Request Entered in Tracker', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (12, 12, 1, 'specimenAvailable', 'Biobank Specimen Available? (Yes/No)', 'boolean');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (13, 13, 1, 'biobankDate', 'Date Column M (Biobank Yes/No) Entered', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (14, 14, 1, 'biobankReason', 'Reason Tissue Not Banked', 'string');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (15, 15, 1, 'requestDate', 'Date Request Processed', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (16, 16, 1, 'LMPComments', 'LMP Comments', 'string');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (17, 17, 1, 'geneMutation', 'Gene >> Mutation (Frequency, Class)', 'string');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (18, 18, 1, 'numberCores', 'Number of cores', 'number');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (19, 19, 1, 'specimenBiobankDate', 'Date Specimen went to Biobank', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (20, 20, 1, 'specimenType', 'Specimen Type Collected by CSP (block/slides)', 'string');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (21, 21, 1, 'diagnosticsDate', 'Date of transfer to Molecular Diagnostics', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (22, 22, 1, 'returnRequested', 'Return Requested? (Yes/No)', 'boolean');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (23, 23, 1, 'returnDate', 'Date of Return to Outside Institution', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (24, 24, 1, 'bloodCollDate', 'Date and Time of Blood Collection', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (25, 25, 1, 'insufficientDate', 'Date Tissue Deemed Insufficient', 'date');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE", "OPTIONS") VALUES (26, 26, 1, 'notes', 'Notes', 'string', '{"longtext":true}');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (27, 27, 1, 'study', 'Study', 'string');
INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE", "OPTIONS") VALUES (28, 28, 1, 'diagnosticsDays', 'Days from consent to diagnostics', 'number', '{"calculated":"netWorkingDays(record.get(\"dateEntered\"), record.get(\"diagnosticsDate\"))"}');

INSERT INTO "ATTRIBUTES" ("ID", "RANK", "STUDY_ID", "NAME", "LABEL", "TYPE") VALUES (32, 1, 2, 'patientId', 'Patient ID', 'string');

-- =============================================================================================
-- Now for the views

CREATE TABLE "VIEWS" (
  "ID" INTEGER AUTO_INCREMENT PRIMARY KEY,
  "STUDY_ID" INTEGER NOT NULL REFERENCES "STUDIES"("ID"),
  "NAME" VARCHAR(24) NOT NULL,
  "DESCRIPTION" VARCHAR(2048),
  "BODY" LONGTEXT,
  "OPTIONS" VARCHAR(2048),
  UNIQUE ("STUDY_ID", "NAME")
);

INSERT INTO "VIEWS" ("ID", "STUDY_ID", "DESCRIPTION", "NAME") VALUES (1, 1, 'Manages the whole study', 'complete');
INSERT INTO "VIEWS" ("ID", "STUDY_ID", "DESCRIPTION", "NAME") VALUES (2, 1, 'Tracks the study', 'track');
INSERT INTO "VIEWS" ("ID", "STUDY_ID", "DESCRIPTION", "NAME", "OPTIONS") VALUES (3, 1, 'Tracks only secondary', 'secondary', '{"rows":[{"attribute":"study","value":"secondary"}]}');
INSERT INTO "VIEWS" ("ID", "STUDY_ID", "DESCRIPTION", "NAME", "BODY") VALUES (4, 1, 'Enter data', 'enter', '<h3>Enter data</h3>');

INSERT INTO "VIEWS" ("ID", "STUDY_ID", "DESCRIPTION", "NAME") VALUES (5, 2, 'Manages the whole study', 'complete');

-- =============================================================================================
-- Now for the view attributes

{'fraction_non_alphanumeric': 0.26401536269590536, 'fraction_numerical': 0.021929009477792232, 'mean_word_length': 7.0318407960199005, 'pattern_counts': {'":': 36, '<': 7, '<?xml version=': 0, '>': 12, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1536859', 'n_tokens_mistral': 6108, 'n_tokens_neox': 5573, 'n_words': 1755}