File Metadata

2024-03-29

When files are added to the File Repository, metadata about each file is recorded in the table exp.Data. The information about each file includes:
  • File Name
  • URL
  • Download Link
  • Thumbnail Link
  • Inline Thumbnail image
  • etc...

View the Query Browser

To access the table, developers and admins can:

  • Go to Admin > Go to Module > Query.
  • Click to open the schema exp and the query Data.
  • Click View Data.
By default, the name, runID (if applicable) and URL are included. You can use the (Grid views) > Customize Grid option to show additional columns or create a custom grid view.

Create a Query Web Part

To create a grid that shows the metadata columns, do one of the following:

  • Create a custom view of the grid exp.Data, and expose the custom view by adding a Query web part.
    • Or
  • Create a custom query using exp.Data as the base query, and add the columns using SQL. For example, the following SQL query adds the columns InlineThumbnail and DownloadLink to the base query:
SELECT Data.Name,
Data.Run,
Data.DataFileUrl,
Data.InlineThumbnail,
Data.DownloadLink
FROM Data

Pulling Metadata from File Paths

You can pull out more metadata, if you conform to a path naming pattern. In the following example, CT and PET scans have been arranged in the following folder pattern:

scans
├───patient1
│ ├───CT
│ └───PET
└───patient2
├───CT
└───PET

The following SQL query harvests out the patient id and the scan type from the directory structure above.

SELECT Data.Name as ScanName,
Data.DataFileUrl,
split_part(DataFileUrl, '/', 8) as Ptid,
split_part(DataFileUrl, '/', 9) as ScanType
FROM Data
WHERE locate('/@files/scans', DataFileUrl) > 0

The result is a table of enhanced metadata about the files, which provides new columns you can filter on, to narrow down to the files of interest.

Related Topics