Files Table Schema (files)


Description

description of files, content is stored in sha1 file pool

files table columns

Column Type Size Nulls Auto Default Children Parents Comments
id BIGINT 19 √ null
analytics_used_files.fileid analusedfile_fil2_fk R
file_conversion.destfileid fileconv_des2_fk R
file_conversion.sourcefileid fileconv_sou2_fk R
contenthash VARCHAR 40

sha1 hash of file content
pathnamehash VARCHAR 40

complete file path sha1 hash - unique for each file
contextid BIGINT 19 null
context.id file_con3_fk R

The context id defined in context table - identifies the instance of plugin owning the file
component VARCHAR 100

Full name of the component owning the area
filearea VARCHAR 50

Like "coursefiles". "submission", "intro" and "content" (images and swf linked from summaries), etc.
itemid BIGINT 19 null

Optional - some plugin specific item id (eg. forum post, blog entry or assignment submission, user id for user files)
filepath VARCHAR 255

Optional - relative path to file from module content root, useful in Scorm and Resource mod - most of the mods do not need this
filename VARCHAR 255

The full Unicode name of this file (case sensitive) - some chars are not allowed though
userid BIGINT 19 √ null
user.id file_use2_fk R

Optional - general userid field - meaning depending on plugin
filesize BIGINT 19 null
mimetype VARCHAR 100 √ null

type of file - jpeg image, open document spreadsheet
status BIGINT 19 0

number greater than 0 means something is wrong with this file (virus, missing, etc.)
source LONGTEXT 2147483647 √ null

contains the reference if the file is imported from external sites
author VARCHAR 255 √ null

The original author of the file
license VARCHAR 255 √ null

license of the file to guide reuse
timecreated BIGINT 19 null
timemodified BIGINT 19 null
sortorder BIGINT 19 0

order of files
referencefileid BIGINT 19 √ null
files_reference.id file_ref2_fk R

Use to indicate file is a proxy for repository file

Table contained -1 rows

files table indexes

Constraint Name Type Sort Column(s)
PRIMARY Primary key Asc id
file_comfilconite_ix Performance Asc/Asc/Asc/Asc component + filearea + contextid + itemid
file_con2_ix Performance Asc contextid
file_con_ix Performance Asc contenthash
file_lic_ix Performance Asc license
file_pat_uix Must be unique Asc pathnamehash
file_ref_ix Performance Asc referencefileid
file_use_ix Performance Asc userid

files table relationships

Close relationships within degrees of separation