code_notebook_sql_cell_migratable_not_executed (view) Content
| 01MFY5UWFCJDRXSTPY7F | SQL | ConstructionSqlNotebook | auto_generate_console_content_tabular_sqlpage_files | -- Auto-generate console content for all tables and views -- This recreates the original auto-generation system using Drizzle -- Delete existing auto-generated files DELETE FROM sqlpage_files WHERE path like 'console/content/table/%.auto.sql'; DELETE FROM sqlpage_files WHERE path like 'console/content/view/%.auto.sql'; -- Generate table browser pages for every table INSERT OR REPLACE INTO sqlpage_files (path, contents) SELECT 'console/content/table/' || name || '.auto.sql', 'SELECT ' || quote('dynamic') || ' AS component, sqlpage.run_sql(' || quote('shell/shell.sql') || ') AS properties; SELECT ' || quote('breadcrumb') || ' AS component; SELECT ''Home'' as title,sqlpage.environment_variable(''SQLPAGE_SITE_PREFIX'') || ''/'' AS link; SELECT ''Console'' as title,sqlpage.environment_variable(''SQLPAGE_SITE_PREFIX'') || ''/console'' AS link; SELECT ''Content'' as title,sqlpage.environment_variable(''SQLPAGE_SITE_PREFIX'') || ''/console/content'' AS link; SELECT ''' || name || ' table'' as title, ''#'' AS link; SELECT ' || quote('title') || ' AS component, ''' || name || ' (table) Content'' as contents; SET total_rows = (SELECT COUNT(*) FROM ' || name || '); SET limit = COALESCE($limit, 50); SET offset = COALESCE($offset, 0); SET total_pages = ($total_rows + $limit - 1) / $limit; SET current_page = ($offset / $limit) + 1; SELECT ''text'' AS component, ''[' || name || ' (table) Schema](console/info-schema/table.sql?name=' || name || '&stats=yes)'' AS contents_md; SELECT ''text'' AS component, ''- Start Row: '' || $offset || '' '' || ''- Rows per Page: '' || $limit || '' '' || ''- Total Rows: '' || $total_rows || '' '' || ''- Current Page: '' || $current_page || '' '' || ''- Total Pages: '' || $total_pages as contents_md WHERE $stats IS NOT NULL; -- Display table with pagination SELECT ' || quote('table') || ' AS component, TRUE AS sort, TRUE AS search, TRUE AS hover, TRUE AS striped_rows, TRUE AS small; SELECT * FROM ' || name || ' LIMIT $limit OFFSET $offset; SELECT ''text'' AS component, (SELECT CASE WHEN $current_page > 1 THEN ''[Previous](?limit='' || $limit || ''&offset='' || ($offset - $limit) || '')'' ELSE '''' END) || '' '' || ''(Page '' || $current_page || '' of '' || $total_pages || '') '' || (SELECT CASE WHEN $current_page < $total_pages THEN ''[Next](?limit='' || $limit || ''&offset='' || ($offset + $limit) || '')'' ELSE '''' END) AS contents_md;' FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%' AND name NOT IN ('sqlpage_files', 'sqlpage_aide_navigation'); -- Generate view browser pages for every view INSERT OR REPLACE INTO sqlpage_files (path, contents) SELECT 'console/content/view/' || name || '.auto.sql', 'SELECT ' || quote('dynamic') || ' AS component, sqlpage.run_sql(' || quote('shell/shell.sql') || ') AS properties; SELECT ' || quote('breadcrumb') || ' AS component; SELECT ''Home'' as title,sqlpage.environment_variable(''SQLPAGE_SITE_PREFIX'') || ''/'' AS link; SELECT ''Console'' as title,sqlpage.environment_variable(''SQLPAGE_SITE_PREFIX'') || ''/console'' AS link; SELECT ''Content'' as title,sqlpage.environment_variable(''SQLPAGE_SITE_PREFIX'') || ''/console/content'' AS link; SELECT ''' || name || ' view'' as title, ''#'' AS link; SELECT ' || quote('title') || ' AS component, ''' || name || ' (view) Content'' as contents; SET total_rows = (SELECT COUNT(*) FROM ' || name || '); SET limit = COALESCE($limit, 50); SET offset = COALESCE($offset, 0); SET total_pages = ($total_rows + $limit - 1) / $limit; SET current_page = ($offset / $limit) + 1; SELECT ''text'' AS component, ''[' || name || ' (view) Schema](console/info-schema/view.sql?name=' || name || '&stats=yes)'' AS contents_md; SELECT ''text'' AS component, ''- Start Row: '' || $offset || '' '' || ''- Rows per Page: '' || $limit || '' '' || ''- Total Rows: '' || $total_rows || '' '' || ''- Current Page: '' || $current_page || '' '' || ''- Total Pages: '' || $total_pages as contents_md WHERE $stats IS NOT NULL; -- Display view with pagination SELECT ' || quote('table') || ' AS component, TRUE AS sort, TRUE AS search, TRUE AS hover, TRUE AS striped_rows, TRUE AS small; SELECT * FROM ' || name || ' LIMIT $limit OFFSET $offset; SELECT ''text'' AS component, (SELECT CASE WHEN $current_page > 1 THEN ''[Previous](?limit='' || $limit || ''&offset='' || ($offset - $limit) || '')'' ELSE '''' END) || '' '' || ''(Page '' || $current_page || '' of '' || $total_pages || '') '' || (SELECT CASE WHEN $current_page < $total_pages THEN ''[Next](?limit='' || $limit || ''&offset='' || ($offset + $limit) || '')'' ELSE '''' END) AS contents_md;' FROM sqlite_master WHERE type = 'view'; -- Generate redirect files from .sql to .auto.sql INSERT OR IGNORE INTO sqlpage_files (path, contents) SELECT 'console/content/table/' || name || '.sql', 'SELECT ''redirect'' AS component,sqlpage.environment_variable(''SQLPAGE_SITE_PREFIX'') || ''/console/content/table/' || name || '.auto.sql'' AS link WHERE $stats IS NULL; SELECT ''redirect'' AS component,sqlpage.environment_variable(''SQLPAGE_SITE_PREFIX'') || ''/console/content/table/' || name || '.auto.sql?stats='' || $stats AS link WHERE $stats IS NOT NULL;' FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%' AND name NOT IN ('sqlpage_files', 'sqlpage_aide_navigation'); INSERT OR IGNORE INTO sqlpage_files (path, contents) SELECT 'console/content/view/' || name || '.sql', 'SELECT ''redirect'' AS component,sqlpage.environment_variable(''SQLPAGE_SITE_PREFIX'') || ''/console/content/view/' || name || '.auto.sql'' AS link WHERE $stats IS NULL; SELECT ''redirect'' AS component,sqlpage.environment_variable(''SQLPAGE_SITE_PREFIX'') || ''/console/content/view/' || name || '.auto.sql?stats='' || $stats AS link WHERE $stats IS NOT NULL;' FROM sqlite_master WHERE type = 'view'; | e79b80796b3c784b5c503c325c6c0f8fdc9401d0 | A series of idempotent INSERT statements which will auto-generate "default" content for all tables and views | |||
| 01MFY5UWFB0EK6T798GL | SQL | ConstructionSqlNotebook | session_ephemeral_table | -- Session state ephemeral table is created at the top level of bootstrap -- This ensures it's available for all subsequent operations | 57f63cd30d47d0ddd7b3342d4f66e5681a07acdb | Creates a session_state_ephemeral table for session arguments | |||
| 01MFY5UWFBUQV84I8EBE | SQL | ConstructionSqlNotebook | surveilr_table_size | CREATE TABLE IF NOT EXISTS surveilr_table_size ( table_name TEXT PRIMARY KEY, table_size_mb REAL ); DELETE FROM surveilr_table_size; INSERT INTO surveilr_table_size (table_name, table_size_mb) SELECT name, ROUND(SUM(pgsize) / (1024.0 * 1024), 2) FROM dbstat GROUP BY name; | c9cccd9cce30f7a471cd1d28e306a0324570c20f | Creates a surveilr_table_size to compute the sizes for individual tables for statistical purposes. | |||
| 01MFY5UWFBORBSRDPP95 | SQL | ConstructionSqlNotebook | v001_seedDML | 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; | 738b42724df1b185521020729f85307c5d2c0d05 | Seed data which provides default configuration for surveilr app |
(Page 1 of 1)