Schema - Assets

The assets table stores a record for every asset (image, video, document, etc). It enforces certain business rules which have been agreed upon by the participating units. This table is populated by each unit, typically as part of the ingest process, though there may be a mass insert during migration (TBD). The unit will update records over time to request ingest or deletion of an asset, or to update image metadata. Traffic manager will monitor this table to know what actions are requested, and will update rows to record the results of transactions related to a given asset.

Change Log

Derived from: Schema - Assets Google doc

Document modified	By	Description
Tuesday 2017-10-24	Tim Speevack	Combined sheet and doc, added columns lastmodified, plus all image metadata columns.
Thursday, 2018-01-18	Tim Speevack	Added several columns to grid w/best guesses on values (should be reviewed) Added fields: netx_error, preservica_error, cds_error Added netx_folderid column to tell ingest where to put the asset in netx Renamed the netx_updated and preservica_updated columns to netx_ingested and preservica_ingested. since that’s what those datesreally indicate. Removed colormode field
Friday, 2018-01-26	Tim Speevack	Split this into two tables: assets and assetstatus. Added hotfolderpath as a new column.
Wednesday, 2018-01-31	Tim Speevack	Renamed id column to assetid. Renamed checksum column to uploadchecksum
Thursday 2018-02-15	Tim Speevack	Added unit_rank column, removed comment regarding autotask.
Thursday 2018-04-05	Tim Speevack	Added imageheight, imagewidth
Friday 2018-08-03	Youjun Guo	Add cds_meta_changed
Friday 2018-08-10	Youjun Guo	Add date_photo_taken
Mon 2018-08-20	Youjun Guo	Add tombstone
Wed 2024-03-27	Youjun Guo	Add date_of_source

Schema Summary

Column Name	Datatype SQL Server	Column Name PostgreSQL	Datatype PostgreSQL	Nullable	Managed by?	Is Netx Attribute?	Required for insert?	NetX Autotask Sync?	TMAN Sync to NetX?
assetid	int identity(1,1)			not null	DB (auto)		(auto)
created	datetime			not null	Unit		def
lastmodified	datetime			not null	Unit+TMAN	Maybe?	def
login	nvarchar(40)			not null	Unit		Yes
unitcode	nvarchar(4)			not null	Unit	R/O	Yes
filename	nvarchar(100)			not null	Unit		Yes
hotfolderpath	nvarchar(512)			not null	Unit		Yes
uploadchecksum	nchar(32)			not null	Unit		Yes
uuid	nchar(36)			null	Unit	R/O
nativeid	int			null	Unit	R/O
unit_rank	int			null	Unit	R/O
mimetype	nvarchar(80)			null	Unit
cmstype	nchar(4)			not null	Unit	R/O	Yes
cmsid	integer			not null	Unit	R/O	Yes
cdslevel	smallint			not null	Unit + TMAN + Netx	R/W CDS Level	Yes	Yes	Yes
cdsprimary	smallint			null	Unit + TMAN + Netx	R/W CDS Primary		Yes	Yes
cdsrank	smallint			null	Unit + TMAN + Netx	R/W CDS Rank		Yes	Yes
imagecredit	nvarchar(2048)			null	Unit + TMAN + Netx	R/W Image Credit		Yes	Yes
imagecaption	nvarchar(max)			null	Unit + TMAN + Netx	R/W Image Caption		Yes	Yes
imagecopyright	nvarchar(max)			null	Unit + TMAN + Netx	R/W Image Copyright		Yes	Yes
copyrightinfourl	nvarchar(510)			null	Unit + TMAN + Netx	R/W Copyright Info URL		Yes	Yes
copyrightstatus	nvarchar(20)			null	Unit + TMAN + Netx	R/W Copyright Status		Yes	Yes
usageterms	nvarchar(2048)			null	Unit + TMAN + Netx	R/W Usage Terms		Yes	Yes
imagesource	nvarchar(256)			null	Unit + TMAN + Netx	R/W Image Source		Yes	Yes
headline	nvarchar(510)			null	Unit + TMAN + Netx	R/W Headline		Yes	Yes
imagekeywords	nvarchar(max)			null	Unit + TMAN + Netx	R/W Image Keywords		Yes	Yes
jobidentifier	nvarchar(160)			null	Unit + TMAN + Netx	R/W Job Identifier		Yes	Yes
creator	nvarchar(256)			null	Unit + TMAN + Netx	R/W Creator		Yes	Yes
creatorrole	nvarchar(160)			null	Unit + TMAN + Netx	R/W Creator Role		Yes	Yes
imagetitle	nvarchar(2048)			null	Unit + TMAN + Netx	R/W Image Title		Yes	Yes
instructions	nvarchar(1000)			null	Unit + TMAN + Netx	R/W Instructions		Yes	Yes
bitdepth	nvarchar(40)			null	Unit + TMAN + Netx	R/O Bit Depth			Yes
colorprofile	nvarchar(100)			null	Unit + TMAN + Netx	R/O Color Profile			Yes
imagesupplier	nvarchar(256)			null	Unit + TMAN + Netx	R/W Image Supplier		Yes	Yes
lens	nvarchar(80)			null	Unit + TMAN + Netx	R/O Lens			Yes
lensmake	nvarchar(80)			null	Unit + TMAN + Netx	R/O Lens Make			Yes
lensmodel	nvarchar(80)			null	Unit + TMAN + Netx	R/O Lens Model			Yes
serialnumber	nvarchar(80)			null	Unit + TMAN + Netx	R/O Serial Number			Yes
imageheight	int			null	Unit+TMAN	R/O			Yes
imagewidth	int			null	Unit+TMAN	R/O		No	Yes
cds_meta_changed	smallint			null	Unit+TMAN	R/W		No	No
date_photo_taken	datetime			null	Unit	R/O		No	No
tombstone	nvarchar			null	Unit+TMAN	R/O		No	No
date_of_source	date			null	Unit	R/O		No	No

Schema Column Details

assetid (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.

created
Datatype	date
Nullable	No
Description	Populated during insert, never updated. Stores the original date of creation of the asset.
Notes	Defaults to sql server getDate(). Not used by TMAN or other systems.

lastmodified
Datatype	date
Nullable	No
Description	Used to track when any field is updated.
Notes	Defaults to sql server getDate(). Updated when any of the fields below are changed. The following processes will update this field: Unit - if they update the record to modify any value TMAN - when any status, error, or other field is changed.

login
Datatype	nvarchar(40)
Nullable	No
Description	Text field which is used by the unit to record the creator of the database entry. This may be the name of an automated process or may be the actual netid/login of the user who created the media.
Notes	Not used by TMAN or other systems.
Sample value	“auto-ingest”, “tms64”, etc.

unitcode
Datatype	nvarchar(4)
Nullable	No
Description	Contains the unit identifier
Notes	This field, combined with filename will guarantee file name uniqueness within the unit. Will be uniquely index on (unitcode, filename).
Sample value	“YPM”, “YCBA”, “YUAG”

filename
Datatype	nvarchar(100)
Nullable	No
Description	Contains the filename of the asset as assigned by the unit. It is up to the unit to maintain their own naming convention or business rules to ensure uniqueness.
Notes	This field, combined with unitcode will guarantee file name uniqueness within the unit. Will be uniquely index on (unitcode, filename).
Sample value	“ag-obj-12345-001-pub.tif”
MM Mapping	UOIS.MASTER_OBJ_NAME, max length today is 88 characters.

hotfolderpath
Datatype	nvarchar(512)
Nullable	No
Description	Contains the relative path underneath the hot folder share. This is a temporary use value, only valid during ingest. After successful ingest the file will have been removed from the hot folder.
Notes	Normally this will be defined as the unitcode alone, i.e. just “yuag”, which means the file is located under \\someshare\yuag. This allows users to create subpaths for organizing large ingest batches with any number of subfolders. The path separators should be forward slashes.
Sample value	yuag/bigbatch/123/456
MM Mapping	n/a

uploadchecksum
Datatype	nchar(32)
Nullable	No
Description	This is the (md5) checksum of the original file, generated by the unit prior to copying to the TMAN hotfolder. This field is used by TMAN to verify that the file was successfully copied and is safe to ingest. This field should not be modifed after insert.
Notes
Sample value	“ecd2f922ec7fb3cc7a32b6fabf863612”
MM Mapping	YALE_COMMON_FIELDS.METADATA_CHECKSUMS

uuid
Datatype	nchar(36)
Nullable	Yes
Description	Optional storage for a type 4 UUID identifier for the media. This will primarily be used by YPM w/EMu, but may be used in the future by other units. This field is uniquely indexed where not NULL
Notes	This could be used as a location to define a common UUID to be used as the unique identifier in other systems like Preservica and CDS. E.g. TMAN could pass the value in this field to Preservica in the SIP.
Sample value	“D079DE34-C348-4136-956B-28456117FC4B”

nativeid
Datatype	integer
Nullable	Yes
Description	Optional storage for an integer identifier for the media. This is primarily intended for use by YPM to store their media’s integer id from EMu.
Notes
Sample value

unit_rank
Datatype	integer
Nullable	Yes
Description	Optional field for sorting in reports, other UIs.
Notes
Sample value

mimetype
Datatype	nvarchar(80)
Nullable	Yes
Description	Optional storage for the mime type of the media.
Notes	This is populated by the unit at the time of ingest and is not necessarily expected to match the mime type stored in NetX or Preservica. It’s primarily intended as a search string for the unit for reporting/dashboard queries.
Sample value	image/tiff
MM Mapping	UOIS.MASTER_OBJ_MIME_TYPE

cmstype
Datatype	nchar(3)
Nullable	No
Description	This field is used by the unit to define the relationship to the cms metadata related to the asset. This field is part of the composite foreign key to the cms_metadata_view.
Notes	Values used in this field will be defined by agreement between the units, but at this time there is no planned restriction on the values, i.e. units could have unique cms types. This field is only written during ingest and should be considered read-only after that point.
Sample value	“obj”, “exb”, “evt”, “lec”, “doc”, etc.

cmsid
Datatype	int
Nullable	No
Description	This field is used by the unit to define the relationship to the cms metadata related to the asset. This field is part of the composite foreign key to the cms_metadata_view.
Notes	Values in this field represent the unique identifier in the unit’s cms system(s). This field is only written during ingest and should be considered read-only after that point.
Sample value	Values will range into the millions. All current cms systems natively represent these as integers.

cdslevel
Datatype	smallint
Nullable	No
Description	Determines what sizes of derivatives will be created by CDS. For details see CDS documentation
Notes
Sample value	values from 0 to 21.
Synced	Yes

cdsprimary
Datatype	smallint
Nullable	Yes
Description	Tells CDS to treat the flagged asset as primary (essentially determines how the asset will be sorted when pulling related assets)
Notes	0/1 for false/true respectively
Synced	Yes

cdsrank
Datatype	smallint
Nullable	Yes
Description	Determines sort order when pulling related assets from CDS
Notes	0/1 for false/true respectively
Synced	Yes

imagecredit
Datatype	nvarchar(2048)
Nullable	Yes
Description	IPTC 2:110: Credit or XMP photoshop:Credit
Notes	exiftool tag: -xmp:credit NOTE: -credit will be truncated if it exceeds the specification length, xmp:credit will only be populated via adobe tools. The appropriate tag may vary between institutions.
Sample value	YALE_IPTC_XMP_EXIF.IMAGE_CREDIT “Yale University Art Gallery”, “Digital Image: Yale Center for British Art”, “Digital Image: Yale Peabody Museum of Natural History”

imagecaption
Datatype	nvarchar(max)
Nullable	Yes
Description	IPTC 2:120 Caption/Abstract or dc:description
Notes	exiftool tag: -caption or -Caption-Abstract
Sample value	YALE_IPTC_XMP_EXIF.DESCR_CAP “recto, unframed”, “Opening Gala for Staff”
Synced	Yes

imagecopyright
Datatype	nvarchar(max)
Nullable	Yes
Description	IPTC 2:116 Copyright Notice or dc:rights
Notes	exiftool -copyrightnotice
Sample value	YALE_IPTC_XMP_EXIF.COPYRIGHT_IMAGE “Yale University Art Gallery”, “Digital Image: © 2011 Yale Center for British Art”, “Copyright Yale Peabody Museum 2012”
Synced	Yes

copyrightinfourl
Datatype	nvarchar(510)
Nullable	Yes
Description	xmp:webstatement
Notes	exiftool -webstatement
Sample value	YALE_IPTC_XMP_EXIF.COPYRIGHT_INFO_URL “http://hdl.handle.net/10079/8931zqj ”, “http://hdl.handle.net/10079/gb5mkww ”, “http://artgallery.yale.edu/pages/info/rights.html”
Synced	Yes

copyrightstatus
Datatype	nvarchar(20)
Nullable	Yes
Description	photoshop:Marked or xmp:rightsMarked
Notes	exiftool -marked False if Public Domain, True otherwise
Sample value	YALE_IPTC_XMP_EXIF.COPYRIGHT_STATUS “false”, “true”, null
Synced	Yes

usageterms
Datatype	nvarchar(2048)
Nullable	Yes
Description	xmpRights:UsageTerms
Notes	exiftool - usageterms
Sample value	YALE_IPTC_XMP_EXIF.RES_IMAGE “http://hdl.handle.net/10079/gb5mkww ”, “http://hdl.handle.net/10079/8931zqj ”
Synced	Yes

imagesource
Datatype	nvarchar(256)
Nullable	Yes
Description	IPTC 2:115 Source or photoshop:Source
Notes	exiftool -xmp:source NOTE: -source will be truncated if it exceeds the specification length, xmp:source will only be populated via adobe tools. The appropriate tag may vary between institutions.
Sample value	YALE_IPTC_XMP_EXIF.SOURCE “Yale Center for British Art”
Synced	Yes

headline
Datatype	nvarchar(510)
Nullable	Yes
Description	IPTC 2:105 Headline or photoshop:Headline
Notes	exiftool -headline
Sample value	YALE_IPTC_XMP_EXIF.CONTENT_HEADLINE “Caro: Close Up, an exhibition on view at the Yale Center for British Art, October 18, 2012–December 30, 2012”, “OYAG Renovation 2010”
Synced	Yes

imagekeywords
Datatype	nvarchar(max)
Nullable	Yes
Description	IPTC 2:25 Keywords or dc:subject
Notes	exiftool -keywords or -subject
Sample value	YALE_IPTC_XMP_EXIF.KEYWORDS “Rapid Imaging”, “back, endpapers”, “Connecticut, CT, Fossils, Skeleton”
Synced	Yes

jobidentifier
Datatype	nvarchar(160)
Nullable	Yes
Description	IPTC 2:103 Original Transmission Reference
Notes	exiftool -transmissionreference
Sample value	YALE_IPTC_XMP_EXIF.JOB_IDENT “Harvey Weiss archives”, “P&D Scanning Project”, “Legacy Project”
Synced	Yes

creator
Datatype	nvarchar(256)
Nullable	Yes
Description	IPTC 2:80 By-line or dc:creator
Notes	exiftool -by-line
Sample value	YALE_IPTC_XMP_EXIF.CREATOR “Jessica Smolinski”, “J. Utrup, 2015”, “Richard Caspole”
Synced	Yes

creatorrole
Datatype	nvarchar(160)
Nullable	Yes
Description	IPTC 2:85 By-line Titlte
Notes	exiftool -by-linetitle
Sample value	YALE_IPTC_XMP_EXIF.CREATOR_ROLE “Photographer”, “Division of Anthropology”, “Senior Photographer”
Synced	Yes

imagetitle
Datatype	nvarchar(2048)
Nullable	Yes
Description	dc:title
Notes	exiftool -title
Sample value	YALE_IPTC_XMP_EXIF.NAME “Family Day”, “VP.001883 Anchisaurus polyzelus”, “ND497.R63 R43 1833”
Synced	Yes

instructions
Datatype	nvarchar(1000)
Nullable	Yes
Description	IPTC 2:40 Special Instruction
Notes	exiftool -instructions
Sample value	YALE_IPTC_XMP_EXIF.INSTRUCTIONS “Input Sharpening: SurfaceBlur Method: Radius 3, Levels 100, opacity 85%”, “Van Otterloo collection”, “May not be reproduced without the permission of the Yale Peabody Museum.”. Current max length is 481 in MM.
Synced	Yes

bitdepth
Datatype	nvarchar(40)
Nullable	Yes
Description	Exif:102 BitsPerSample
Notes	exiftool -BitsPerSample
Sample value	YALE_IPTC_XMP_EXIF.BIT_DEPTH “8”, ”8 8 8”, ”16 16 16”
Synced	Yes

colorprofile
Datatype	nvarchar(100)
Nullable	Yes
Description	This is the name of the color profile embedded in the asset, NOT the profile itself.
Notes	exiftool -icc_profile:profiledescription Note: this just returns the name of the color profile, which is what we’re proposing to store in the database, NOT the color profile itself, which can be extracted using: exiftool -icc_profile -b -w icc c:\tmp\test.tif (which would extract to c:\tmp\test.icc)
Sample value	YALE_IPTC_XMP_EXIF.COLOR_PROFILE “sRGB IEC61966-2.1”, “eciRGB v2 ICCv4”, “Adobe RGB (1998)”
Synced	Yes

imagesupplier
Datatype	nvarchar(256)
Nullable	Yes
Description	plus:ImageSupplier
Notes	exiftool -imagesuppliername
Sample value	YALE_IPTC_XMP_EXIF.IMAGE_SUP “Yale Center for British Art”.
Synced	Yes

lens
Datatype	nvarchar(80)
Nullable	Yes
Description	EXIF 0xa432 LensInfo
Notes	exiftool -lensinfo
Sample value	YALE_IPTC_XMP_EXIF.LENS “5.0-80.0 mm”, “HC 80”, “EF24-105mm f/4L IS USM”
Synced	Yes

lensmake
Datatype	nvarchar(80)
Nullable	Yes
Description	EXIF 0xa433 LensMake
Notes	exiftool - make
Sample value	YALE_IPTC_XMP_EXIF.MAKE “Phase One”, “Canon”, “Hasselblad”
Synced	Yes

lensmodel
Datatype	nvarchar(80)
Nullable	Yes
Description	EXIF 0xa434 LensModel
Notes	exiftool -lensmodel
Sample value	YALE_IPTC_XMP_EXIF.MODEL “DSC-W650”, “LCE-6000”, “Sinarback eVolution 75, Hasselblad”
Synced	Yes

serialnumber
Datatype	nvarchar(80)
Nullable	Yes
Description	EXIF 0xa435 LensSerialNumber
Notes	exiftool -serialnumber
Sample value	YALE_IPTC_XMP_EXIF.SER_NUM “320108843”, “NO= 1002d98e“
Synced	Yes

imageheight
Datatype	int
Nullable	Yes
Description	pixel height of the original image (not the derivative)
Notes	This value can optionally be provided by the unit, but if not provided TMAN will fill in this value. If the unit sends this value it will save one call in the TMAN processing.
Synced	Yes

imagewidth
Datatype	int
Nullable	Yes
Description	pixel width of the original image (not the derivative)
Notes	(see imageheight above)
Synced	Yes

cds_meta_changed
Datatype	smallint
Nullable	Yes
Description	Departments turn it to 1 to indicate there are metadate update need to be push to CDS
Notes	TMAN implement the push and set it back to 0 when finish
Synced	no

date_photo_taken
Datatype	datetime
Nullable	Yes
Description	As named
Notes	TMAN implement the push during ingest
Synced	no

tombstone
Datatype	nvarchar
Nullable	Yes
Description	content for populate netx “description” field
Notes	TMAN implement the push during ingest
Synced	no

date_of_source
Datatype	date
Nullable	Yes
Description	As named
Notes	TMAN implement the push during ingest
Synced	no

tag extraction

exiftool -xmp:credit -caption-abstract -copyrightnotice -webstatement -marked -usageterms -xmp:source -headline -keywords -transmissionreference -by-line -by-linetitle -title -instructions -bitspersample -colormode -icc_profile:profiledescription -imagesuppliername -lensinfo -lensmodel -make -serialnumber -imageheight -imagewidth <filename>

NOTE: be aware of the differences in length between IPTC and XMP ‘shared’ fields. In the command above I’ve used -xmp:source and -xmp:credit. These fields are also available as just -source and -credit, but they pull from different locations and have different lengths. My opinion is that we should be using the -xmp tags, but there’s no guarantee that all media will have those fields populated. For YUAG assets these will always be preferred, but if you’re not using photoshop the values might not be automatically shared between these fields.

I suppose one option is to specify both tags in the exiftool command line, then programmatically parse for both and take the longer of the two. For example:

exiftool -f -xmp:source -source -xmp:credit -credit c:\tmp\ag-evt-000727-097-pub.tif

Source : Yale University Art Gallery, Visual Resources Department

Source : Yale University Art Gallery, Vis

Credit : Yale University Art Gallery

(note the wrapping on the first line of the output)

Cultural Heritage IT

Schema - Assets

Analytics

Change Log

Schema Summary

Schema Column Details

tag extraction

Related content