ConstructionSqlNotebook.v001_seedDML (SQLite SQL Statements)

INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('ignore .git and node_modules paths', 'default', '/(.git|node_modules)/', 'IGNORE_RESOURCE', NULL, NULL, 'Ignore any entry with `/.git/` or `/node_modules/` in the path.', NULL, CURRENT_TIMESTAMP, 'SYSTEM', NULL, NULL, NULL, NULL, NULL) ON CONFLICT(ur_ingest_resource_path_match_rule_id) DO NOTHING;

INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('images-metadata', 'default', '.(?P<nature>jpg|jpeg|png|gif|bmp|tiff|svg|webp)$', 'CONTENT_ACQUIRABLE | CONTENT_ACQUIRABLE_METADATA', '?P<nature>', NULL, 'Images with metadata extraction', NULL, CURRENT_TIMESTAMP, 'SYSTEM', NULL, NULL, NULL, NULL, NULL) ON CONFLICT(ur_ingest_resource_path_match_rule_id) DO NOTHING;

INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('jsonl-content-replace', 'default', '.(?P<nature>jsonl)$', 'CONTENT_ACQUIRABLE | CONTENT_REPLACE_JSON_LINES', '?P<nature>', NULL, 'JSONL files with content replacement', NULL, CURRENT_TIMESTAMP, 'SYSTEM', NULL, NULL, NULL, NULL, NULL) ON CONFLICT(ur_ingest_resource_path_match_rule_id) DO NOTHING;

INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('media-metadata', 'default', '.(?P<nature>mp4|mp3)$', 'CONTENT_ACQUIRABLE | CONTENT_ACQUIRABLE_METADATA', '?P<nature>', NULL, 'Media files with metadata extraction', NULL, CURRENT_TIMESTAMP, 'SYSTEM', NULL, NULL, NULL, NULL, NULL) ON CONFLICT(ur_ingest_resource_path_match_rule_id) DO NOTHING;

INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('pdf-docx-transform-metadata', 'default', '.(?P<nature>pdf|docx)$', 'CONTENT_ACQUIRABLE | CONTENT_ACQUIRABLE_TRANSFORM_MARKITDOWN | CONTENT_ACQUIRABLE_METADATA', '?P<nature>', NULL, 'PDF and DOCX documents with full transformation and metadata extraction', NULL, CURRENT_TIMESTAMP, 'SYSTEM', NULL, NULL, NULL, NULL, NULL) ON CONFLICT(ur_ingest_resource_path_match_rule_id) DO NOTHING;

INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('surveilr-SQL capturable executable', 'default', 'surveilr-SQL', 'CAPTURABLE_EXECUTABLE | CAPTURABLE_SQL', NULL, NULL, 'Any entry with surveilr-SQL in the path will be treated as a capturable SQL executable and allow execution of the SQL', NULL, CURRENT_TIMESTAMP, 'SYSTEM', NULL, NULL, NULL, NULL, NULL) ON CONFLICT(ur_ingest_resource_path_match_rule_id) DO NOTHING;

INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('surveilr-[NATURE] style capturable executable', 'default', 'surveilr[(?P<nature>[^]]*)]', 'CAPTURABLE_EXECUTABLE', '?P<nature>', NULL, 'Any entry with surveilr-[XYZ] in the path will be treated as a capturable executable extracting XYZ as the nature', NULL, CURRENT_TIMESTAMP, 'SYSTEM', NULL, NULL, NULL, NULL, NULL) ON CONFLICT(ur_ingest_resource_path_match_rule_id) DO NOTHING;

INSERT INTO "ur_ingest_resource_path_match_rule" ("ur_ingest_resource_path_match_rule_id", "namespace", "regex", "flags", "nature", "priority", "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log") VALUES ('typical ingestion extensions', 'default', '.(?P<nature>md|mdx|html|json|jsonc|puml|txt|toml|yml|xml|tap|csv|tsv|ssv|psv|tm7)$', 'CONTENT_ACQUIRABLE', '?P<nature>', NULL, 'Ingest the content for text and structured data extensions. Assume the nature is the same as the extension.', NULL, CURRENT_TIMESTAMP, 'SYSTEM', NULL, NULL, NULL, NULL, NULL) ON CONFLICT(ur_ingest_resource_path_match_rule_id) DO NOTHING;

;

INSERT INTO "ur_ingest_resource_path_rewrite_rule" (
          "ur_ingest_resource_path_rewrite_rule_id", "namespace", "regex", "replace", "priority", 
          "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", 
          "deleted_at", "deleted_by", "activity_log"
        ) VALUES (
          '.plantuml -> .puml', 'default', '(\\.plantuml)$', '.puml', NULL, 'Treat .plantuml as .puml files',
          NULL, CURRENT_TIMESTAMP, 'SYSTEM', NULL, NULL, NULL, NULL, NULL
        ) ON CONFLICT(ur_ingest_resource_path_rewrite_rule_id) DO NOTHING;

INSERT INTO "ur_ingest_resource_path_rewrite_rule" (
          "ur_ingest_resource_path_rewrite_rule_id", "namespace", "regex", "replace", "priority", 
          "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", 
          "deleted_at", "deleted_by", "activity_log"
        ) VALUES (
          '.text -> .txt', 'default', '(\\.text)$', '.txt', NULL, 'Treat .text as .txt files',
          NULL, CURRENT_TIMESTAMP, 'SYSTEM', NULL, NULL, NULL, NULL, NULL
        ) ON CONFLICT(ur_ingest_resource_path_rewrite_rule_id) DO NOTHING;

INSERT INTO "ur_ingest_resource_path_rewrite_rule" (
          "ur_ingest_resource_path_rewrite_rule_id", "namespace", "regex", "replace", "priority", 
          "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", 
          "deleted_at", "deleted_by", "activity_log"
        ) VALUES (
          '.yaml -> .yml', 'default', '(\\.yaml)$', '.yml', NULL, 'Treat .yaml as .yml files',
          NULL, CURRENT_TIMESTAMP, 'SYSTEM', NULL, NULL, NULL, NULL, NULL
        ) ON CONFLICT(ur_ingest_resource_path_rewrite_rule_id) DO NOTHING;

INSERT INTO "ur_ingest_resource_path_rewrite_rule" (
          "ur_ingest_resource_path_rewrite_rule_id", "namespace", "regex", "replace", "priority", 
          "description", "elaboration", "created_at", "created_by", "updated_at", "updated_by", 
          "deleted_at", "deleted_by", "activity_log"
        ) VALUES (
          '.ndjson -> .jsonl', 'default', '(\\.ndjson)$', '.jsonl', NULL, 'Treat .ndjson as .jsonl files',
          NULL, CURRENT_TIMESTAMP, 'SYSTEM', NULL, NULL, NULL, NULL, NULL
        ) ON CONFLICT(ur_ingest_resource_path_rewrite_rule_id) DO NOTHING;

;

INSERT INTO "party_type" (
          "party_type_id", "code", "value", "created_at", "created_by", "updated_at", "updated_by", 
          "deleted_at", "deleted_by", "activity_log"
        ) VALUES (
          '01' || upper(substr(hex(randomblob(10)), 1, 25)), 'ORGANIZATION', 'Organization', 
          NULL, NULL, NULL, NULL, NULL, NULL, NULL
        ) ON CONFLICT DO NOTHING;

INSERT INTO "party_type" (
          "party_type_id", "code", "value", "created_at", "created_by", "updated_at", "updated_by", 
          "deleted_at", "deleted_by", "activity_log"
        ) VALUES (
          '01' || upper(substr(hex(randomblob(10)), 1, 25)), 'PERSON', 'Person', 
          NULL, NULL, NULL, NULL, NULL, NULL, NULL
        ) ON CONFLICT DO NOTHING;

;

INSERT INTO "orchestration_nature" (
          "orchestration_nature_id", "nature", "elaboration", "created_at", "created_by", 
          "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log"
        ) VALUES (
          'surveilr-transform-csv', 'Transform CSV', NULL, CURRENT_TIMESTAMP, 'SYSTEM', 
          NULL, NULL, NULL, NULL, NULL
        ) ON CONFLICT(orchestration_nature_id) DO NOTHING;

INSERT INTO "orchestration_nature" (
          "orchestration_nature_id", "nature", "elaboration", "created_at", "created_by", 
          "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log"
        ) VALUES (
          'surveilr-transform-xml', 'Transform XML', NULL, CURRENT_TIMESTAMP, 'SYSTEM', 
          NULL, NULL, NULL, NULL, NULL
        ) ON CONFLICT(orchestration_nature_id) DO NOTHING;

INSERT INTO "orchestration_nature" (
          "orchestration_nature_id", "nature", "elaboration", "created_at", "created_by", 
          "updated_at", "updated_by", "deleted_at", "deleted_by", "activity_log"
        ) VALUES (
          'surveilr-transform-html', 'Transform HTML', NULL, CURRENT_TIMESTAMP, 'SYSTEM', 
          NULL, NULL, NULL, NULL, NULL
        ) ON CONFLICT(orchestration_nature_id) DO NOTHING;

;

INSERT INTO "uniform_resource_graph" ("name", "elaboration") VALUES ('filesystem', '{}') ON CONFLICT(name) DO NOTHING;

INSERT INTO "uniform_resource_graph" ("name", "elaboration") VALUES ('imap', '{}') ON CONFLICT(name) DO NOTHING;

INSERT INTO "uniform_resource_graph" ("name", "elaboration") VALUES ('plm', '{}') ON CONFLICT(name) DO NOTHING;

INSERT INTO "uniform_resource_graph" ("name", "elaboration") VALUES ('osquery-ms', '{}') ON CONFLICT(name) DO NOTHING;