Schema - Asset Status
- Jeffrey Campbell
The assetStatus table has a one-to-one relationship to the assets table on id. It is primarily to hold process-related data which changes as the ingest, update and delete processes progress.
Schema Summary
Column Name SQL Server | Datatype SQL Server | Column Name PostgreSQL (if different) | Datatype PostgreS | Nullable | Managed by? | Is Netx Attribute? | Required for insert? |
assetstatusid | int identity(1,1) |
|
| not null | DB (auto) |
| (auto) |
assetid | int |
|
| not null | (insertAsset) |
| Yes |
derivativepath | nvarchar(512) |
|
| null | TMAN |
|
|
derivativename | nvarchar(100) |
|
| null | TMAN |
|
|
masterpath | nvarchar(512) |
|
| null | TMAN |
|
|
ingestrequested | smallint |
|
| not null | Unit+TMAN |
| def 0 |
deleterequested | smallint |
|
| not null | Unit+TMAN |
| def 0 |
metadatasyncrequested | smallint |
|
| not null | Unit+TMAN |
| def 0 |
deleted | smallint |
|
| not null | TMAN |
| def 0 |
preservica_du_uuid | nchar(36) |
|
| null | TMAN |
|
|
preservica_fileref_uuid | nchar(36) |
|
| null | TMAN | yes |
|
preservica_ingested | datetime |
|
| null | TMAN |
|
|
preservica_url | nvarchar(512) |
|
| null | TMAN |
|
|
preservica_status | smallint |
|
| null | TMAN |
|
|
preservica_error | nvarchar(max) |
|
| null | TMAN |
|
|
preservica_lastmodified | datetime |
|
| null | TMAN |
|
|
preservica_securitytag | nchar(100) |
|
| not null | Unit |
|
|
preservica_colref | nchar(36) |
|
| not null | Unit |
|
|
netx_id | integer |
|
| null | TMAN |
|
|
netx_folderid | int |
|
| null | TMAN |
|
|
netx_ingested | datetime |
|
| null | TMAN |
|
|
netx_status | smallint |
|
| null | TMAN |
|
|
netx_error | nvarchar(max) |
|
| null | TMAN |
|
|
netx_lastmodified | datetime |
|
| null | TMAN |
|
|
netx_last_sync_checksum | nchar(32) |
|
| null | TMAN |
|
|
cds_uuid | nchar(36) |
|
| null | TMAN |
|
|
cds_lastmodified | datetime |
|
| null | TMAN |
|
|
cds_status | smallint |
|
| null | TMAN |
|
|
cds_error | nvarchar(max) |
|
| null | TMAN |
|
|
cds_last_sync_checksum | nchar(32) |
|
| null | TMAN |
|
|
cdslevel_last_syn | smallint |
|
| null | TMAN |
|
|
Handled in the insertAsset stored procedure, called by the unit
Schema Column Details
assetstatusid (Primary Key) | |
Datatype | Integer (sql server identity field, automatically populated) |
Nullable | No |
Description | Automatic unique row identity |
Notes | Sequential integer, limited to 2bn. If we need more this could be a bigint. Not used by TMAN or other systems. |
assetid (Foreign Key to assets table) | |
Datatype | Integer |
Nullable | No |
Description | Foreign key to assets table. |
Notes | This row is created by the unit AFTER the insert to the assets table is successful. |
derivativepath | |
Datatype | nvarchar(512) |
Nullable | Yes |
Description | Absolute path to the location of the derivative for netx |
Notes | Relative path to shared mount point |
derivativename | |
Datatype | nvarchar(100) |
Nullable | Yes |
Description | Name of the derivative in netx |
Notes |
|
masterpath | |
Datatype | nvarchar(512) |
Nullable | Yes |
Description | Relative path to shared mount point. |
Notes |
|
ingestrequested | |
Datatype | smallint |
Nullable | No |
Description | Indicates the asset is ready for ingest |
Notes | Set to 1 when the upload is complete and the asset is ready for ingest. This field is monitored by TMAN and when set to 1 this triggers the ingest process to kick off. When ingest is successfully completed this field will be set back to zero. If any part of the ingest fails we assume this field will remain set to 1, possibly accompanied by a status code in the preservica_status, netx_status or cds_status fields. |
Sample value | 0/1, defaults to zero. |
deleterequested | |
Datatype | smallint |
Nullable | No |
Description | Indicates this asset should be deleted. |
Notes | Defaults to zero. When a unit wants to delete an asset it sets the value of this field to 1. This triggers TMAN to kick off the delete process. When delete is successfully completed this will be reset to zero (indicating that the request has been fulfilled). If delete fails we assume this field will remain set to 1, typically accompanied by a status code in the preservica_status, netx_status or cds_status fields. |
Sample value | 0/1, defaults to zero. |
metadatasyncrequested | |
Datatype | smallint |
Nullable | No |
Description | Indicates this asset’s image metadata was changed by the unit and should be refreshed in netx. |
Notes | Defaults to zero. When the unit modifies image metadata (in the assets table) they must also update the assetstatus table to set this flag to 1 so that TMAN can monitor this field and trigger image metadata updates from TMAN db to NetX. |
Sample value | 0/1, defaults to zero. |
deleted | |
Datatype | smallint |
Nullable | No |
Description | Indicates that this asset has been deleted. |
Notes | After insert this field is only managed by TMAN processes. When an asset deletion is successfully completed this flag is set to 1.
Note that we do not delete TMAN database records - when an asset is deleted the filename cannot be re-used. To ensure this we create a unique index on (unitcode, filename) and leave the db record in place. |
Sample value | 0/1, defaults to zero. |
preservica_fileref_uuid | |
Datatype | nchar(36) |
Nullable | Yes |
Description | Stores the uuid for the file content in the preservica system. |
Notes | The uuid for this field is either provided prior to Preservica ingest by the unit (e.g., notably by YPM at present) in the field assets.uuid, or, if assets.uuid is blank then the SIP creation process generates a value. The purpose of the unit providing a value in assets.uuid is so the unit can programmatically build a directly accessible URL to the asset For example, for YPM the following two URLs will retrieve the same asset: https://{preservica}/digitalFileContents/{preservica_fileref_uuid https://{preservica}/digitalFileContents/{assets.uuid} TMAN will push this field to NetX during ingest |
Sample value | D079DE34-C348-4136-956B-28456117FC4B |
preservica_du_uuid | |
Datatype | nchar(36) |
Nullable | Yes |
Description | Stores the uuid for the deliverable unit in the preservica system. |
Notes | This value is written by TMAN after the asset has been successfully ingested into the Preservica system. Useful for subsequently making an API call to discover various attributes about the deliverable unit. |
Sample value | D079DE34-C348-4136-956B-28456117FC4B |
preservica_ingested | |
Datatype | datetime |
Nullable | Yes |
Description | This value is written by TMAN after the asset is successfully ingested into the Preservica system. |
Notes | This is assumed to be the timestamp of the successful ingest into preservica. |
Sample value |
|
preservica_status | |
Datatype | smallint |
Nullable | Yes |
Description | This value is written by TMAN when the preservica transaction is complete (i.e. succeeds or fails). |
Notes | These integer values will be logically mapped into specific result statuses which are yet to be determined. TODO - We need a lookup table to map integer status values to descriptions. |
Sample value |
|
preservica_url | |
Datatype | nvarchar(512) |
Nullable | Yes |
Description | Contains a url derived from the preservica ingest which will point directly to the master asset. |
Notes |
|
Sample value | |
preservica_error | |
Datatype | nvarchar(max) |
Nullable | Yes |
Description | Storage location for exceptions, stack traces, error messages or other debugging information when an error occurs during the preservica ingest process |
Notes |
|
Sample value |
|
preservica_lastmodified | |
Datatype | date |
Nullable | No |
Description | Used to track when preservica is updated. |
Notes |
|
preservica_securitytag | |
Datatype | nchar(100) |
Nullable | No |
Description | A string matching an existing security tag in preservica. |
Notes | The values for this field will be provided to the unit from the preservica system. They pass that value during asset+assetstatus insert (see insertAsset stored procedure) so that TMAN can tell preservica what tag to apply to the asset on ingest. |
preservica_colref | |
Datatype | nchar(36) |
Nullable | No |
Description | A uuid string matching an existing collection in the preservica system. |
Notes | The values for this field will be provided to the unit from the preservica system. They pass that value during asset+assetstatus insert (see insertAsset stored procedure) so that TMAN can tell preservica what tag to apply to the asset on ingest. |
netx_id | |
Datatype | int |
Nullable | Yes |
Description | Stores the id for the asset in the NetX system. |
Notes | This value is written by TMAN after the asset is successfully ingested into the NetX system. The id value is generated by NetX |
Sample value |
|
netx_ingested | |
Datatype | datetime |
Nullable | Yes |
Description | This value is written by TMAN after the asset is successfully ingested into the NetX system. |
Notes | This is assumed to be the timestamp of the successful ingest into NetX. |
Sample value |
|
netx_status | |
Datatype | smallint |
Nullable | Yes |
Description | This value is written by TMAN when the NetX transaction is complete (i.e. succeeds or fails). |
Notes | These integer values will be logically mapped into specific result statuses which are yet to be determined. TODO - We need a lookup table to map integer status values to descriptions. |
Sample value |
|
netx_error | |
Datatype | nvarchar(max) |
Nullable | Yes |
Description | Storage location for exceptions, stack traces, error messages or other debugging information when an error occurs during the preservica ingest process |
Notes |
|
Sample value |
|
netx_folderid | |
Datatype | int |
Nullable | No |
Description | Designates the netx folder into which the asset should be ingested. |
Notes | The unit will need to hard-code/store this value locally as part of their ingest process and provide this value during insert of the asset record. |
Sample value |
|
netx_lastmodified | |
Datatype | date |
Nullable | No |
Description | Used to track when netx is modified |
Notes |
|
netx_last_sync_checksum | |
Datatype | nchar(36) |
Nullable | null |
Description | Md5 checksum of fields synchronized by NetX autotask |
Notes | Should be generated by TMAN when asset is first sent to NetX and updated during calls to synchronization routine |
cds_uuid | |
Datatype | nchar(36) |
Nullable | Yes |
Description | Stores the uuid for the asset in the CDS system. |
Notes | This value is written by TMAN after the asset is successfully ingested into the CDS system. The uuid value is either generated by CDS or can be pre-defined in the assets.uuid field at insert. |
Sample value | D079DE34-C348-4136-956B-28456117FC4B |
cds_updated | |
Datatype | datetime |
Nullable | Yes |
Description | This value is written by TMAN after the asset is successfully ingested into the CDS system. |
Notes | This is assumed to be the timestamp of the successful ingest into CDS. |
Sample value |
|
cds_status | |
Datatype | smallint |
Nullable | Yes |
Description | This value is written by TMAN when the CDS transaction is complete (i.e. succeeds or fails). |
Notes | These integer values will be logically mapped into specific result statuses which are yet to be determined. TODO - We need a lookup table to map integer status values to descriptions. |
Sample value |
|
cds_error | |
Datatype | nvarchar(max) |
Nullable | Yes |
Description | Storage location for exceptions, stack traces, error messages or other debugging information when an error occurs during the preservica ingest process |
Notes |
|
Sample value |
|
cds_last_sync_checksum | |
Datatype | nchar(36) |
Nullable | null |
Description | Md5 checksum of metadata fields monitored by CDS |
Notes | Should be generated by TMAN on asset ingest |
cdslevel_last_syn | |
Datatype | smallint |
Nullable | null |
Description | Last cdslevel value that TMAN pushed to CDS |
Notes |
|