uniform_resource
| Column |
Type |
PK |
Required |
Default |
| uniform_resource_id |
VARCHAR |
Yes |
Yes |
|
| device_id |
VARCHAR |
No |
Yes |
|
| ingest_session_id |
VARCHAR |
No |
Yes |
|
| ingest_fs_path_id |
VARCHAR |
No |
No |
|
| ingest_session_imap_acct_folder_message |
VARCHAR |
No |
No |
|
| ingest_issue_acct_project_id |
VARCHAR |
No |
No |
|
| uri |
TEXT |
No |
Yes |
|
| content_digest |
TEXT |
No |
Yes |
|
| content |
BLOB |
No |
No |
|
| nature |
TEXT |
No |
No |
|
| size_bytes |
INTEGER |
No |
No |
|
| last_modified_at |
TIMESTAMPTZ |
No |
No |
|
| content_fm_body_attrs |
TEXT |
No |
No |
|
| frontmatter |
TEXT |
No |
No |
|
| elaboration |
TEXT |
No |
No |
|
| created_at |
TIMESTAMPTZ |
No |
No |
CURRENT_TIMESTAMP |
| created_by |
TEXT |
No |
No |
'UNKNOWN' |
| updated_at |
TIMESTAMPTZ |
No |
No |
|
| updated_by |
TEXT |
No |
No |
|
| deleted_at |
TIMESTAMPTZ |
No |
No |
|
| deleted_by |
TEXT |
No |
No |
|
| activity_log |
TEXT |
No |
No |
|
Foreign Keys
| Column Name |
Foreign Key |
| ingest_issue_acct_project_id |
ingest_issue_acct_project_id references ur_ingest_session_plm_acct_project.ur_ingest_session_plm_acct_project_id |
| ingest_session_imap_acct_folder_message |
ingest_session_imap_acct_folder_message references ur_ingest_session_imap_acct_folder_message.ur_ingest_session_imap_acct_folder_message_id |
| ingest_fs_path_id |
ingest_fs_path_id references ur_ingest_session_fs_path.ur_ingest_session_fs_path_id |
| ingest_session_id |
ingest_session_id references ur_ingest_session.ur_ingest_session_id |
| device_id |
device_id references device.device_id |
Indexes
| Column Name |
Index Name |
| device_id |
idx_uniform_resource__device_id__uri |
| uri |
idx_uniform_resource__device_id__uri |
| device_id |
sqlite_autoindex_uniform_resource_2 |
| content_digest |
sqlite_autoindex_uniform_resource_2 |
| uri |
sqlite_autoindex_uniform_resource_2 |
| size_bytes |
sqlite_autoindex_uniform_resource_2 |
| uniform_resource_id |
sqlite_autoindex_uniform_resource_1 |
SQL DDL
CREATE TABLE "uniform_resource" (
"uniform_resource_id" VARCHAR PRIMARY KEY NOT NULL,
"device_id" VARCHAR NOT NULL,
"ingest_session_id" VARCHAR NOT NULL,
"ingest_fs_path_id" VARCHAR,
"ingest_session_imap_acct_folder_message" VARCHAR,
"ingest_issue_acct_project_id" VARCHAR,
"uri" TEXT NOT NULL,
"content_digest" TEXT NOT NULL,
"content" BLOB,
"nature" TEXT,
"size_bytes" INTEGER,
"last_modified_at" TIMESTAMPTZ,
"content_fm_body_attrs" TEXT CHECK(json_valid(content_fm_body_attrs) OR content_fm_body_attrs IS NULL),
"frontmatter" TEXT CHECK(json_valid(frontmatter) OR frontmatter IS NULL),
"elaboration" TEXT CHECK(json_valid(elaboration) OR elaboration IS NULL),
"created_at" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
"created_by" TEXT DEFAULT 'UNKNOWN',
"updated_at" TIMESTAMPTZ,
"updated_by" TEXT,
"deleted_at" TIMESTAMPTZ,
"deleted_by" TEXT,
"activity_log" TEXT,
FOREIGN KEY("device_id") REFERENCES "device"("device_id"),
FOREIGN KEY("ingest_session_id") REFERENCES "ur_ingest_session"("ur_ingest_session_id"),
FOREIGN KEY("ingest_fs_path_id") REFERENCES "ur_ingest_session_fs_path"("ur_ingest_session_fs_path_id"),
FOREIGN KEY("ingest_session_imap_acct_folder_message") REFERENCES "ur_ingest_session_imap_acct_folder_message"("ur_ingest_session_imap_acct_folder_message_id"),
FOREIGN KEY("ingest_issue_acct_project_id") REFERENCES "ur_ingest_session_plm_acct_project"("ur_ingest_session_plm_acct_project_id"),
UNIQUE("device_id", "content_digest", "uri", "size_bytes")
)