| managed_application |
Title |
|
/console/info-schema/view.sql?name=managed_application&stats=yes |
[Content](console/info-schema/view.sql?name=managed_application&stats=yes) |
[managed_application (view) Schema](console/info-schema/view.sql?name=managed_application&stats=yes) |
/console/content/view/managed_application.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/managed_application.sql?stats=yes) |
[managed_application (view) Content](console/content/view/managed_application.sql?stats=yes) |
CREATE VIEW managed_application AS
SELECT DISTINCT
json_extract(border.value, '$."a:Value".Properties."a:anyType"[1]."b:Value"."#text"') AS Title
FROM
uniform_resource_transform,
json_each(
CASE
WHEN json_type(json_extract(content, '$.ThreatModel.DrawingSurfaceList.DrawingSurfaceModel')) = 'array'
THEN json_extract(content, '$.ThreatModel.DrawingSurfaceList.DrawingSurfaceModel')
ELSE json_array(json_extract(content, '$.ThreatModel.DrawingSurfaceList.DrawingSurfaceModel'))
END
) AS drawing_surface,
json_each(
json_extract(drawing_surface.value, '$.Borders."a:KeyValueOfguidanyType"')
) AS border
WHERE json_extract(border.value, '$."a:Value"."@i:type"') = 'StencilEllipse'
AND json_extract(border.value, '$."a:Value".Properties."a:anyType"[0]."b:DisplayName"') = 'Managed Application' |
| sql_database |
Title |
|
/console/info-schema/view.sql?name=sql_database&stats=yes |
[Content](console/info-schema/view.sql?name=sql_database&stats=yes) |
[sql_database (view) Schema](console/info-schema/view.sql?name=sql_database&stats=yes) |
/console/content/view/sql_database.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/sql_database.sql?stats=yes) |
[sql_database (view) Content](console/content/view/sql_database.sql?stats=yes) |
CREATE VIEW sql_database AS
SELECT DISTINCT
json_extract(border.value, '$."a:Value".Properties."a:anyType"[1]."b:Value"."#text"') AS Title
FROM
uniform_resource_transform,
json_each(
CASE
WHEN json_type(json_extract(content, '$.ThreatModel.DrawingSurfaceList.DrawingSurfaceModel')) = 'array'
THEN json_extract(content, '$.ThreatModel.DrawingSurfaceList.DrawingSurfaceModel')
ELSE json_array(json_extract(content, '$.ThreatModel.DrawingSurfaceList.DrawingSurfaceModel'))
END
) AS drawing_surface,
json_each(
json_extract(drawing_surface.value, '$.Borders."a:KeyValueOfguidanyType"')
) AS border
WHERE json_extract(border.value, '$."a:Value"."@i:type"') = 'StencilParallelLines'
AND json_extract(border.value, '$."a:Value".Properties."a:anyType"[0]."b:DisplayName"') = 'SQL Database' |
| boundaries |
boundary |
|
/console/info-schema/view.sql?name=boundaries&stats=yes |
[Content](console/info-schema/view.sql?name=boundaries&stats=yes) |
[boundaries (view) Schema](console/info-schema/view.sql?name=boundaries&stats=yes) |
/console/content/view/boundaries.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/boundaries.sql?stats=yes) |
[boundaries (view) Content](console/content/view/boundaries.sql?stats=yes) |
CREATE VIEW boundaries AS
SELECT DISTINCT
json_extract(border.value, '$."a:Value".Properties."a:anyType"[1]."b:Value"."#text"') AS boundary
FROM
uniform_resource_transform,
json_each(
CASE
WHEN json_type(json_extract(content, '$.ThreatModel.DrawingSurfaceList.DrawingSurfaceModel')) = 'array'
THEN json_extract(content, '$.ThreatModel.DrawingSurfaceList.DrawingSurfaceModel')
ELSE json_array(json_extract(content, '$.ThreatModel.DrawingSurfaceList.DrawingSurfaceModel'))
END
) AS drawing_surface,
json_each(
json_extract(drawing_surface.value, '$.Borders."a:KeyValueOfguidanyType"')
) AS border
WHERE
json_extract(border.value, '$."a:Value"."@i:type"') = 'BorderBoundary'
AND (
json_extract(border.value, '$."a:Value".Properties."a:anyType"[0]."b:DisplayName"') IN (
'Other Browsers Boundaries',
'CorpNet Trust Boundary',
'Generic Trust Border Boundary'
)
)
AND json_extract(border.value, '$."a:Value".Properties."a:anyType"[1]."b:Value"."#text"') NOT LIKE '%.%.%.%'
AND json_extract(border.value, '$."a:Value".Properties."a:anyType"[1]."b:Value"."#text"') LIKE '%Boundar%' |
| uniform_resource_file |
uniform_resource_id |
VARCHAR |
/console/info-schema/view.sql?name=uniform_resource_file&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_file&stats=yes) |
[uniform_resource_file (view) Schema](console/info-schema/view.sql?name=uniform_resource_file&stats=yes) |
/console/content/view/uniform_resource_file.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_file.sql?stats=yes) |
[uniform_resource_file (view) Content](console/content/view/uniform_resource_file.sql?stats=yes) |
CREATE VIEW uniform_resource_file AS
SELECT ur.uniform_resource_id,
ur.nature,
p.root_path AS source_path,
pe.file_path_rel,
ur.size_bytes
FROM uniform_resource ur
LEFT JOIN uniform_resource_edge ure ON ur.uniform_resource_id = ure.uniform_resource_id AND ure.nature = 'ingest_fs_path'
LEFT JOIN ur_ingest_session_fs_path p ON ure.node_id = p.ur_ingest_session_fs_path_id
LEFT JOIN ur_ingest_session_fs_path_entry pe ON ur.uniform_resource_id = pe.uniform_resource_id |
| uniform_resource_file |
nature |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_file&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_file&stats=yes) |
[uniform_resource_file (view) Schema](console/info-schema/view.sql?name=uniform_resource_file&stats=yes) |
/console/content/view/uniform_resource_file.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_file.sql?stats=yes) |
[uniform_resource_file (view) Content](console/content/view/uniform_resource_file.sql?stats=yes) |
CREATE VIEW uniform_resource_file AS
SELECT ur.uniform_resource_id,
ur.nature,
p.root_path AS source_path,
pe.file_path_rel,
ur.size_bytes
FROM uniform_resource ur
LEFT JOIN uniform_resource_edge ure ON ur.uniform_resource_id = ure.uniform_resource_id AND ure.nature = 'ingest_fs_path'
LEFT JOIN ur_ingest_session_fs_path p ON ure.node_id = p.ur_ingest_session_fs_path_id
LEFT JOIN ur_ingest_session_fs_path_entry pe ON ur.uniform_resource_id = pe.uniform_resource_id |
| uniform_resource_file |
source_path |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_file&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_file&stats=yes) |
[uniform_resource_file (view) Schema](console/info-schema/view.sql?name=uniform_resource_file&stats=yes) |
/console/content/view/uniform_resource_file.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_file.sql?stats=yes) |
[uniform_resource_file (view) Content](console/content/view/uniform_resource_file.sql?stats=yes) |
CREATE VIEW uniform_resource_file AS
SELECT ur.uniform_resource_id,
ur.nature,
p.root_path AS source_path,
pe.file_path_rel,
ur.size_bytes
FROM uniform_resource ur
LEFT JOIN uniform_resource_edge ure ON ur.uniform_resource_id = ure.uniform_resource_id AND ure.nature = 'ingest_fs_path'
LEFT JOIN ur_ingest_session_fs_path p ON ure.node_id = p.ur_ingest_session_fs_path_id
LEFT JOIN ur_ingest_session_fs_path_entry pe ON ur.uniform_resource_id = pe.uniform_resource_id |
| uniform_resource_file |
file_path_rel |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_file&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_file&stats=yes) |
[uniform_resource_file (view) Schema](console/info-schema/view.sql?name=uniform_resource_file&stats=yes) |
/console/content/view/uniform_resource_file.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_file.sql?stats=yes) |
[uniform_resource_file (view) Content](console/content/view/uniform_resource_file.sql?stats=yes) |
CREATE VIEW uniform_resource_file AS
SELECT ur.uniform_resource_id,
ur.nature,
p.root_path AS source_path,
pe.file_path_rel,
ur.size_bytes
FROM uniform_resource ur
LEFT JOIN uniform_resource_edge ure ON ur.uniform_resource_id = ure.uniform_resource_id AND ure.nature = 'ingest_fs_path'
LEFT JOIN ur_ingest_session_fs_path p ON ure.node_id = p.ur_ingest_session_fs_path_id
LEFT JOIN ur_ingest_session_fs_path_entry pe ON ur.uniform_resource_id = pe.uniform_resource_id |
| uniform_resource_file |
size_bytes |
INTEGER |
/console/info-schema/view.sql?name=uniform_resource_file&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_file&stats=yes) |
[uniform_resource_file (view) Schema](console/info-schema/view.sql?name=uniform_resource_file&stats=yes) |
/console/content/view/uniform_resource_file.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_file.sql?stats=yes) |
[uniform_resource_file (view) Content](console/content/view/uniform_resource_file.sql?stats=yes) |
CREATE VIEW uniform_resource_file AS
SELECT ur.uniform_resource_id,
ur.nature,
p.root_path AS source_path,
pe.file_path_rel,
ur.size_bytes
FROM uniform_resource ur
LEFT JOIN uniform_resource_edge ure ON ur.uniform_resource_id = ure.uniform_resource_id AND ure.nature = 'ingest_fs_path'
LEFT JOIN ur_ingest_session_fs_path p ON ure.node_id = p.ur_ingest_session_fs_path_id
LEFT JOIN ur_ingest_session_fs_path_entry pe ON ur.uniform_resource_id = pe.uniform_resource_id |
| uniform_resource_imap |
uniform_resource_id |
VARCHAR |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
name |
VARCHAR |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
ur_ingest_session_imap_account_id |
VARCHAR |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
email |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
host |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
subject |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
from |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
message |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
date |
DATE |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
ur_ingest_session_imap_acct_folder_id |
VARCHAR |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
ingest_account_id |
VARCHAR |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
folder_name |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
size_bytes |
INTEGER |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
nature |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
content |
BLOB |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap_content |
uniform_resource_id |
VARCHAR |
/console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
[uniform_resource_imap_content (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
/console/content/view/uniform_resource_imap_content.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap_content.sql?stats=yes) |
[uniform_resource_imap_content (view) Content](console/content/view/uniform_resource_imap_content.sql?stats=yes) |
CREATE VIEW uniform_resource_imap_content AS
SELECT
uri.uniform_resource_id,
base_ur.uniform_resource_id baseID,
ext_ur.uniform_resource_id extID,
base_ur.uri as base_uri,
ext_ur.uri as ext_uri,
base_ur.nature as base_nature,
ext_ur.nature as ext_nature,
json_extract(part.value, '$.body.Html') AS html_content
FROM
uniform_resource_imap uri
INNER JOIN uniform_resource base_ur ON base_ur.uniform_resource_id=uri.uniform_resource_id
INNER JOIN uniform_resource ext_ur ON ext_ur.uri = base_ur.uri ||'/json' AND ext_ur.nature = 'json',
json_each(ext_ur.content, '$.parts') AS part
WHERE ext_ur.nature = 'json' AND html_content NOT NULL |
| uniform_resource_imap_content |
baseID |
VARCHAR |
/console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
[uniform_resource_imap_content (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
/console/content/view/uniform_resource_imap_content.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap_content.sql?stats=yes) |
[uniform_resource_imap_content (view) Content](console/content/view/uniform_resource_imap_content.sql?stats=yes) |
CREATE VIEW uniform_resource_imap_content AS
SELECT
uri.uniform_resource_id,
base_ur.uniform_resource_id baseID,
ext_ur.uniform_resource_id extID,
base_ur.uri as base_uri,
ext_ur.uri as ext_uri,
base_ur.nature as base_nature,
ext_ur.nature as ext_nature,
json_extract(part.value, '$.body.Html') AS html_content
FROM
uniform_resource_imap uri
INNER JOIN uniform_resource base_ur ON base_ur.uniform_resource_id=uri.uniform_resource_id
INNER JOIN uniform_resource ext_ur ON ext_ur.uri = base_ur.uri ||'/json' AND ext_ur.nature = 'json',
json_each(ext_ur.content, '$.parts') AS part
WHERE ext_ur.nature = 'json' AND html_content NOT NULL |
| uniform_resource_imap_content |
extID |
VARCHAR |
/console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
[uniform_resource_imap_content (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
/console/content/view/uniform_resource_imap_content.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap_content.sql?stats=yes) |
[uniform_resource_imap_content (view) Content](console/content/view/uniform_resource_imap_content.sql?stats=yes) |
CREATE VIEW uniform_resource_imap_content AS
SELECT
uri.uniform_resource_id,
base_ur.uniform_resource_id baseID,
ext_ur.uniform_resource_id extID,
base_ur.uri as base_uri,
ext_ur.uri as ext_uri,
base_ur.nature as base_nature,
ext_ur.nature as ext_nature,
json_extract(part.value, '$.body.Html') AS html_content
FROM
uniform_resource_imap uri
INNER JOIN uniform_resource base_ur ON base_ur.uniform_resource_id=uri.uniform_resource_id
INNER JOIN uniform_resource ext_ur ON ext_ur.uri = base_ur.uri ||'/json' AND ext_ur.nature = 'json',
json_each(ext_ur.content, '$.parts') AS part
WHERE ext_ur.nature = 'json' AND html_content NOT NULL |
| uniform_resource_imap_content |
base_uri |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
[uniform_resource_imap_content (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
/console/content/view/uniform_resource_imap_content.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap_content.sql?stats=yes) |
[uniform_resource_imap_content (view) Content](console/content/view/uniform_resource_imap_content.sql?stats=yes) |
CREATE VIEW uniform_resource_imap_content AS
SELECT
uri.uniform_resource_id,
base_ur.uniform_resource_id baseID,
ext_ur.uniform_resource_id extID,
base_ur.uri as base_uri,
ext_ur.uri as ext_uri,
base_ur.nature as base_nature,
ext_ur.nature as ext_nature,
json_extract(part.value, '$.body.Html') AS html_content
FROM
uniform_resource_imap uri
INNER JOIN uniform_resource base_ur ON base_ur.uniform_resource_id=uri.uniform_resource_id
INNER JOIN uniform_resource ext_ur ON ext_ur.uri = base_ur.uri ||'/json' AND ext_ur.nature = 'json',
json_each(ext_ur.content, '$.parts') AS part
WHERE ext_ur.nature = 'json' AND html_content NOT NULL |
| uniform_resource_imap_content |
ext_uri |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
[uniform_resource_imap_content (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
/console/content/view/uniform_resource_imap_content.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap_content.sql?stats=yes) |
[uniform_resource_imap_content (view) Content](console/content/view/uniform_resource_imap_content.sql?stats=yes) |
CREATE VIEW uniform_resource_imap_content AS
SELECT
uri.uniform_resource_id,
base_ur.uniform_resource_id baseID,
ext_ur.uniform_resource_id extID,
base_ur.uri as base_uri,
ext_ur.uri as ext_uri,
base_ur.nature as base_nature,
ext_ur.nature as ext_nature,
json_extract(part.value, '$.body.Html') AS html_content
FROM
uniform_resource_imap uri
INNER JOIN uniform_resource base_ur ON base_ur.uniform_resource_id=uri.uniform_resource_id
INNER JOIN uniform_resource ext_ur ON ext_ur.uri = base_ur.uri ||'/json' AND ext_ur.nature = 'json',
json_each(ext_ur.content, '$.parts') AS part
WHERE ext_ur.nature = 'json' AND html_content NOT NULL |
| uniform_resource_imap_content |
base_nature |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
[uniform_resource_imap_content (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
/console/content/view/uniform_resource_imap_content.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap_content.sql?stats=yes) |
[uniform_resource_imap_content (view) Content](console/content/view/uniform_resource_imap_content.sql?stats=yes) |
CREATE VIEW uniform_resource_imap_content AS
SELECT
uri.uniform_resource_id,
base_ur.uniform_resource_id baseID,
ext_ur.uniform_resource_id extID,
base_ur.uri as base_uri,
ext_ur.uri as ext_uri,
base_ur.nature as base_nature,
ext_ur.nature as ext_nature,
json_extract(part.value, '$.body.Html') AS html_content
FROM
uniform_resource_imap uri
INNER JOIN uniform_resource base_ur ON base_ur.uniform_resource_id=uri.uniform_resource_id
INNER JOIN uniform_resource ext_ur ON ext_ur.uri = base_ur.uri ||'/json' AND ext_ur.nature = 'json',
json_each(ext_ur.content, '$.parts') AS part
WHERE ext_ur.nature = 'json' AND html_content NOT NULL |
| uniform_resource_imap_content |
ext_nature |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
[uniform_resource_imap_content (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
/console/content/view/uniform_resource_imap_content.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap_content.sql?stats=yes) |
[uniform_resource_imap_content (view) Content](console/content/view/uniform_resource_imap_content.sql?stats=yes) |
CREATE VIEW uniform_resource_imap_content AS
SELECT
uri.uniform_resource_id,
base_ur.uniform_resource_id baseID,
ext_ur.uniform_resource_id extID,
base_ur.uri as base_uri,
ext_ur.uri as ext_uri,
base_ur.nature as base_nature,
ext_ur.nature as ext_nature,
json_extract(part.value, '$.body.Html') AS html_content
FROM
uniform_resource_imap uri
INNER JOIN uniform_resource base_ur ON base_ur.uniform_resource_id=uri.uniform_resource_id
INNER JOIN uniform_resource ext_ur ON ext_ur.uri = base_ur.uri ||'/json' AND ext_ur.nature = 'json',
json_each(ext_ur.content, '$.parts') AS part
WHERE ext_ur.nature = 'json' AND html_content NOT NULL |
| uniform_resource_imap_content |
html_content |
|
/console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
[uniform_resource_imap_content (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
/console/content/view/uniform_resource_imap_content.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap_content.sql?stats=yes) |
[uniform_resource_imap_content (view) Content](console/content/view/uniform_resource_imap_content.sql?stats=yes) |
CREATE VIEW uniform_resource_imap_content AS
SELECT
uri.uniform_resource_id,
base_ur.uniform_resource_id baseID,
ext_ur.uniform_resource_id extID,
base_ur.uri as base_uri,
ext_ur.uri as ext_uri,
base_ur.nature as base_nature,
ext_ur.nature as ext_nature,
json_extract(part.value, '$.body.Html') AS html_content
FROM
uniform_resource_imap uri
INNER JOIN uniform_resource base_ur ON base_ur.uniform_resource_id=uri.uniform_resource_id
INNER JOIN uniform_resource ext_ur ON ext_ur.uri = base_ur.uri ||'/json' AND ext_ur.nature = 'json',
json_each(ext_ur.content, '$.parts') AS part
WHERE ext_ur.nature = 'json' AND html_content NOT NULL |
| ur_ingest_session_files_stats |
device_id |
VARCHAR |
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
ingest_session_id |
VARCHAR |
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
ingest_session_started_at |
TIMESTAMPTZ |
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
ingest_session_finished_at |
TIMESTAMPTZ |
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
file_extension |
|
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
ingest_session_fs_path_id |
VARCHAR |
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
ingest_session_root_fs_path |
TEXT |
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
total_file_count |
|
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
file_count_with_content |
|
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
file_count_with_frontmatter |
|
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
min_file_size_bytes |
|
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
average_file_size_bytes |
INT |
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
max_file_size_bytes |
|
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
oldest_file_last_modified_datetime |
|
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
youngest_file_last_modified_datetime |
|
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats_latest |
device_id |
VARCHAR |
/console/info-schema/view.sql?name=ur_ingest_session_files_stats_latest&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats_latest&stats=yes) |
[ur_ingest_session_files_stats_latest (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats_latest&stats=yes) |
/console/content/view/ur_ingest_session_files_stats_latest.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats_latest.sql?stats=yes) |
[ur_ingest_session_files_stats_latest (view) Content](console/content/view/ur_ingest_session_files_stats_latest.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats_latest" AS
SELECT iss.*
FROM ur_ingest_session_files_stats AS iss
JOIN ( SELECT ur_ingest_session.ur_ingest_session_id AS latest_session_id
FROM ur_ingest_session
ORDER BY ur_ingest_session.ingest_finished_at DESC
LIMIT 1) AS latest
ON iss.ingest_session_id = latest.latest_session_id |
| ur_ingest_session_files_stats_latest |
ingest_session_id |
VARCHAR |
/console/info-schema/view.sql?name=ur_ingest_session_files_stats_latest&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats_latest&stats=yes) |
[ur_ingest_session_files_stats_latest (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats_latest&stats=yes) |
/console/content/view/ur_ingest_session_files_stats_latest.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats_latest.sql?stats=yes) |
[ur_ingest_session_files_stats_latest (view) Content](console/content/view/ur_ingest_session_files_stats_latest.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats_latest" AS
SELECT iss.*
FROM ur_ingest_session_files_stats AS iss
JOIN ( SELECT ur_ingest_session.ur_ingest_session_id AS latest_session_id
FROM ur_ingest_session
ORDER BY ur_ingest_session.ingest_finished_at DESC
LIMIT 1) AS latest
ON iss.ingest_session_id = latest.latest_session_id |
| ur_ingest_session_files_stats_latest |
ingest_session_started_at |
TIMESTAMPTZ |
/console/info-schema/view.sql?name=ur_ingest_session_files_stats_latest&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats_latest&stats=yes) |
[ur_ingest_session_files_stats_latest (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats_latest&stats=yes) |
/console/content/view/ur_ingest_session_files_stats_latest.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats_latest.sql?stats=yes) |
[ur_ingest_session_files_stats_latest (view) Content](console/content/view/ur_ingest_session_files_stats_latest.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats_latest" AS
SELECT iss.*
FROM ur_ingest_session_files_stats AS iss
JOIN ( SELECT ur_ingest_session.ur_ingest_session_id AS latest_session_id
FROM ur_ingest_session
ORDER BY ur_ingest_session.ingest_finished_at DESC
LIMIT 1) AS latest
ON iss.ingest_session_id = latest.latest_session_id |
| ur_ingest_session_files_stats_latest |
ingest_session_finished_at |
TIMESTAMPTZ |
/console/info-schema/view.sql?name=ur_ingest_session_files_stats_latest&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats_latest&stats=yes) |
[ur_ingest_session_files_stats_latest (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats_latest&stats=yes) |
/console/content/view/ur_ingest_session_files_stats_latest.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats_latest.sql?stats=yes) |
[ur_ingest_session_files_stats_latest (view) Content](console/content/view/ur_ingest_session_files_stats_latest.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats_latest" AS
SELECT iss.*
FROM ur_ingest_session_files_stats AS iss
JOIN ( SELECT ur_ingest_session.ur_ingest_session_id AS latest_session_id
FROM ur_ingest_session
ORDER BY ur_ingest_session.ingest_finished_at DESC
LIMIT 1) AS latest
ON iss.ingest_session_id = latest.latest_session_id |