B - Database, XML, XSLT and HTML

Expand / Collapse

B - Database, XML, XSLT and HTML


The data of a SCRATCH project is stored in a database. Besides manipulating project data through the regular SCRATCH user interface, you can also use so called XML-scripting. By saving / loading XML files into SCRATCH you can change shots, timelines, constructs, etc. or copy / render media. This is especially useful when integrating SCRATCH into a workflow that included third party systems. This appendix discusses the details of data manipulation outside the direct user interface of SCRATCH.



All data of a SCRATCH project is stored in the project folder which is by default located in (Win) C:\ProgramData\Assimilator  / (OS X) /Library/Application Support/Assimilator/. Each project has its own sub-folder with the name of the sub-folder being the name of the project. The project sub-folder contains a number of different files:

  • Project.db8; the main (SQLite) project database containing all project meta data. By default SCRATCH saves any changes every time the users switches between CONstructs, enters or exits the Player, periodically while in the Player and when you exit the project. Though not recommended, you can switch off the auto-save option from the System Settings menu. If the auto-save option is not set, you will only be prompted to save changes when exiting SCRATCH. 
  • Project.bak / Project.1.bak; backup files - when entering a project a copy of the project database is made and renamed to .bak. Any previous backups are renamed with a version number. By default there are a maximum of 3 backups maintained but you can adjust this number in the Advanced System Settings.
  • Project.dbl; a file containing the date-time since- and the name of the system that has the project in use. When opening a project this file is locked to prevent updating the project by another SCRATCH in multi-user situation. When closing the project, the file is cleared and unlocked. Se the next paragraph for more information on sharing projects.
  • ProjectCrash.bak / CrashLog.log; in case of a crash, SCRATCH will - when entering a new session - automatically try to create a backup of the last project database that was used and make a copy of both the project database and the log file that was written from the session when the crash occurred.
  • *.reg; old project files. When entering a pre-version 5 project, SCRATCH will ask to upgrade the project data and create a new project database. The old files for storing data is maintained. These can still be used with older versions of SCRATCH but are not updated anymore. It is up to the user to manually remove them from the system.

SQLite is a widely used database engine (http://www.sqlite.org/) and there are various tools for opening a SQLite database file directly. However, although you will be able to view the basic database structure of a SCRATCH project, most of the data is stored in binary fields. Assimilate does not recommend opening, altering or trying to use the database directly with any such third party tool. If you need the project data to support your workflow and integrate with third party tools we recommend using the XML in/export- and scripting capabilities of SCRATCH. These are discussed later in this appendix.


You can change the default location where SCRATCH reads / creates its projects in the System Settings menu panel of the Start-up screen. This way it is possible to have multiple SCRATCH system point to the same network location and as such share the SCRATCH project folder.

Note: It is of the utmost importance that the central drive is controlled by a proper file-locking mechanism! In some cases SAN-environments lack this, which might result in project database files corruption!

When you have setup a shared project folder properly, SCRATCH will notice if a project is opened by another SCRATCH system and will display a message and (optionally) open the project as read-only. This means that you can play footage normally and even alter the project but none of the changes will be saved to the database.


The media-, render- and cache-paths of a project are not stored in the project database. This means that if you copy a project folder with the database over to a different system (e.g. for archiving) and want to enter the project you will have to set the project paths again manually or an automatic path is assigned. This same accounts for a multi-system setup where the project folder is shared between multiple systems. Each system will have to set the project paths again.

SCRATCH does however have a function to store the default project paths within the project folder: if SCRATCH needs to open a project and does not have the project paths available it will first check for a file called ProjectPaths.xml. If present and properly formatted SCRATCH will use the paths specified in this file. You can have SCRATCH automatically create this file by enabling the 'Store Project default paths' Advanced System Settings or generate the file yourself (possibly using script). The layout of this file is:

<?xml version="1.0" encoding="UTF-8"?>


SCRATCH Project data can be stored in an EXtensible Markup Language (XML) format. The XML contains an abstraction of the actual project data: it contains the various components inside a project: groups, constructs, slots, shots and outputs as well as the main properties of these objects. However, not the full features list: e.g. Layers and secondary grading parameters are not included in the XML. The aim is, to provide you with a subset of core project data that is sufficient for integration with third party systems without getting into detail functionality which is relatively complex and much harder to maintain over different versions of SCRATCH.

There are different ways to interact with SCRATCH through XML

  • Export project XML from the Tools menu in the CONstruct. This creates a Project XML file that contains - depending on your selection - the full project, the current group, the current CONstruct or just the current shot selection. 
  • Auto-Export Project XML. A project has the option to automatically export and XML file when you exit the project (and the project data was changed). This is the same Project XML as when doing a full export  - it is just automated and as such could trigger any updates that you require if a project has changed.
  • (Auto) Export HTML. Parallel to the (auto) export XML options mentioned above, you can also export HTML. The process is then to first generate XML and then use an XSL Transform to create HTML. More information about XSLT options in SCRATCH given later in this appendix.
  • Import XML from the Tool menu in the CONstruct. You can also import a Project XML file. Depending on the specific settings inside the XML, the import will append, update or delete objects and properties in the current project: e.g. adding shots, updating names, deleting slots, etc.
  • Load XML through the Command Line. By adding a file reference to the command line to start the application, SCRATCH will process the xml and then when done automatically close itself again as if it were a command line utility. The exact command line depends on the location where you installed SCRATCH and the location of the xml script but the general syntax should be:
    • Windows: ..\assimilate\bin64\Assimilator.exe -cmd "c:\myscript.xml"
    • OSX: ../applications/assimilate/assimilator.exe -cmd "c:\myscript.xml"
  • Custom Commands. Before executing the script or application that is associated with a Custom Command, SCRATCH will export Project XML to a file in the temp folder of the current project and pass a reference to this file as command line parameter to the script or application. Furthermore, if you put the Custom Command in a blocking mode (SCRATCH waits for it to finish), a second command line parameter is passed to the script/application with the name of the file it can generate Project XML to that SCRATCH will import after the script/application is finished.
  • Import through the Watch Folder. In the System Settings you can set a Watch Folder: any file that is copied or moved into the Watch Folder is picked up by SCRATCH and if it contains valid so called Command XML it is  processed. If not, the file is simply removed. Command XML can contain:
    • a command with regular Project XML to update a particular project. In this case SCRATCH will transform the particular Command XML into an Init XML, which is placed in the project sub-folder. Only when the user opens the project then this XML is imported as regular Project XML. Note that projects are never updated from the Watch Folder process while you are working inside a project.
    • a command to create a new project. The new project is created and the project list in the start-up screen is updated. Note that the default project settings are placed in an Init XML file and only applied when the user opens the new project.
    • a command to start a render in a particular project. SCRATCH will open the project, start the render, wait for it to finish and then close the project again (saving the changes).
    • a command top copy an image sequence from a source location to a destination.
    • a command to delete (part of) an image sequence.
    • a command to clear all pending commands.

In the remainder of this paragraph, the various XML types are explained in more details. Please download the sample files that go along with these description from the link below. Note that these samples are for review / informational purposes only: there are no media files included and the xml samples do contain file references to non-existing media.



The s01_project.xml file from the samples has been generated by SCRATCH through the Tools - Export menu. When you open the file in a text editor or XML viewer you can see the general structure.

The root tag within the XML is <scratch ...> and it contains various attributes with information on the project the XML was generated from and the SCRATCH version that generated it. These attributes can potentially be used by external scripts and applications to determine the source of the project and locations for further actions.

Within the root tag, there is a hierarchy of main objects:

---- SLOTS
----- SLOT
------ SHOTS
------- SHOT
----- SHOT

Depending on the XML scope (Project, Group, Construct) you export the first tag after the root is either groups, group or constructs. It should be clear that in within e.g. the GROUPS-element there can be multiple GROUP-elements, and within the CONSTRUCTS-elements there can be one or more CONSTRUCT-elements, etc. The OUTPUTS-elements lies within the CONSTRUCT-element, just like you define one or more outputs with each CONstruct in SCRATCH. The METADATA-element at the bottom refers to project-level metadata. A SHOT-element has its own METADATA-element.


If you import the Project XML from s01_project.xml into an existing (empty) project, SCRATCH will create a new Group, Construct and small timeline of shots as well as an additional output node. Please note that when you do not have the media / locations, SCRATCH will report this and you will probably only see black frames.

By default when you import Project XML into a project - SCRATCH appends the full structure in the XML to the existing project. However, by adding an action-attribute to the root-node you can change this behavior: either to update existing elements in a project, insert new items within the existing structure or selectively remove elements from a project.

<scratch  .... action="update"  ...>

The possible values for the action attribute are: insert, update or remove. Below the various action are discussed in more detail with the available sample files. Please note that when you load a sample XML file through the Tools - Import menu, you sometimes have to refresh your project to see the actual changes. Some XML is meant to be processed at project-startup.


Open sample s02_insert.xml to review XML that inserts a new shot in the second slot / second version. 

SCRATCH interprets the XML as follows: it will try to identify each element in the xml within the current project. If it finds the element - it will leave it as is. If SCRATCH can not find the element, it will create it. If e.g. you load the s02_Insert.xml file in the Project in which you also loaded s01_project.xml, SCRATCH will detect that 'MyGroup', 'MyConstruct' and 'Slot 2' already exist and leave those elements as they were. Next it gets to the Shot element in the XML that has the 'layer 1' attribute. This is not already present in the project and as such SCRATCH will create a new Shot element and place it as a version in the second slot.

Things to consider:

  • Groups and CONstruct elements are identified by their name or possibly by their guid.
  • Slot elements are identified by their position (Slot-index attribute) or name (Slot-name attribute), 
  • Shot elements are identified through their position (Slot + Shot-layer attribute) or directly by their guid
  • Output-Shot elements are identified though their position (Shot-output_index attribute) or directly by their guid.

When you look at the of the Sample02_insert.xml, specifically the Shot element, you will find there that it has 2 sub elements:

  • the file-element: specifying where the shot to be loaded is located. Note that this can be the full path but also a relative path + using $media$ which substitutes for the media-path of the current project. Alternatively you can also use $import$ which substitutes for the location of the XML file that is being loaded.
  • the Shot-name element: this is actually an example of update-xml. Adding a sub element with the main element will update the main element. In this case the default name of the shot that is newly loaded is updated. 


Open sample s03_update.xml to review XML that updates existing elements in a project. In this case it updates the name of the construct, updates the in/out-points of the first shot on the timeline and adds a note to the second shot. Note that the updating of the name of the CONstruct was done by first identifying the CONstruct with the value in the name-attribute of the main construct element and then including a name sub-element with the new value.

Open sample s04_update.xml to review XML that updates a number of shots by directly referring to the shots through their guid. Note that the main layout of this XML differs from the regular project XML in that it starts with the Shots-element. Next, it lists 3 shots and updates the names of the shots. The exact location of the shots in the project is not reflected in the XML because it is using the shot guid to reference them.


Open sample s05_remove.xml to review XML that removes a shot from the current construct. Note that you can also delete a CONstruct or Group, which automatically implies that all underlying items are deleted as well. There is no undo / recover from this, other than loading a backup project database.

Please note that to run the s05_remove.xml successfully, you need to revert to the original construct created with s01_project.xml as the remove XML uses the original CONstruct names. Also note that you need to refresh the project to see the actual shot being removed from the CONstruct.

more options

The shot-element can also specify a (plug-in) preset- and/or a grade-attribute. These attributes point to a shot-preset (.pls) file and/or a SCRATCH grade (.ccr) file. A plug-in preset (pls) file represents the various node specific settings that are available from the Matrix Shot menu or in the Output menu for output nodes. A preset file is only applicable on shots of the same type: e.g. a preset file created from a r3d node can not be loaded with an ARRI shot. A SCRATCH grade file contains the full primary and secondary grade, including any animations. These attributes open up the complete creative toolset of SCRATCH through XML scripting.

<shot ..... preset="c:\media\mypreset.pls" grade="c:\media\mygrade.ccr" >

You can use the type-attribute within a shot-element to control the type of node that is being created: some file types can be loaded with multiple codecs. If you do not want to use the default for a given extension, you should set the type-identifier. You can find out the guid of a specific type by just loading a shot with the codec and exporting an XML file. This functionality also lets you load an effects plug-in - e.g. a Stereo node.

<shot .... type="7c17dcb0-b0fc-11df-94e2-0800200c9a66" >

A shot-element can also have an inputs-element nested which in turn can have one or more shot-elements. The inner shot-elements are loaded as inputs for the main shot-element. This way you can also set the inputs for e.g. a stereo node. The inputs-element is only evaluated when importing XML. when exporting XML, the inputs-element is not included.

Similar to the inputs-element you can also include a staging-element to populate the staging area of a shot. Again, this applies only for importing XML, exported XML will not contain the staging-element.

Copy grades: when inserting a new shot or updating a shot you can optionally include a copy_grade attribute in the shot-element. The value of this attribute is the version index of another shot in the same slot of which the new shot is to copy its (full)grade from. This way you can e.g. automate importing new versions while maintaining any grade set on prior versions.

Project metadata. When exporting Project XML, the project meta data items are always included. However, when importing Project XML into an existing project, the meta data items are not automatically appended or updated. In most cases you want to import groups or CONstructs into an existing project that already has its own meta data items set. If however you specifically want to import the project meta data then you need to add an 'import' attribute to the metadata-element: <metadata import="y">....

Sample s03_insert.xml showed how to load a shot using XML. Note that the shot physical file does not in all cases have to exist to be able to create the shot entity in the project. If the file is not present, SCRATCH will in most cases continue to load a dummy shot and process the rest of the XML. This works for most file-sequence types, however not for file container types like r3d and Sony F65. This functionality can be useful when e.g. not all vfx versions have been generated yet but you do already want to update your project with the latest batch of new versions. Note that in all cases, an error is generated / displayed.


So far we discussed the layout of XML which you could load directly into your project using the Import function in the Tools menu. In most cases however XML is used for system integration that should not require any human interference. For this purpose SCRATCH can use the Watch Folder: a specific folder that is continuously monitored by SCRATCH for new files that are created or copied into it to be processed by SCRATCH. You set the watch folder in the System Settings menu. As soon as SCRATCH is started it will start processing any files in that folder and are added. Processing only happens while SCRATCH is in its start-up screen. It will not process while you have opened a project.

The XML format that can be used in the Watch Folder is called Command XML. The s06_command.xml contains an examples of the different commands you can use:

  • copy - to copy a file sequence from a source folder to a destination folder. This command has no single project reference.
  • delete - to physically remove a file sequence. This command has no single project reference.
  • create - this creates a new project. Any project settings that are included are converted into Project XML and placed in the init-folder of the (new) project and only actually processed when the user opens the project the first time.
  • append, update, insert and remove - these commands are all converted into Project XML, which are then placed in the init-folder and loaded the next time the user opens the project.
  • killemall - to clear the command queue and remove all currently pending commands 

Note that you can not execute all commands in the s06_command.xml as some of the file references will not be correct. Please update these using a text editor before trying / copying the file in the watch folder.

Monitoring the Command queue

A single Command XML file can contain one or more commands. The root node of the Command XML is scratch and has a log_file attribute:

<scratch log_file="c:\log\cmd_log.txt" >

If included SCRATCH will use this file to log the result of all commands in the Command XML after they have been executed. Each command in turn has a command_id which is also included in the log file.

<command ....  command_id="MyCmd123" >

This way you can automate the monitoring of processing commands. The log file that SCRATCH creates is also in XML. The structure is as follows:

<?xml version="1.0"?>
    <result cmd="" id="" date="" code="" msg="" />

The log file specifies the command-id, the date/time it was executed and the result code / message. Below are the possible result codes:

1: ok
-1: invalid command tag '%s'
-2: command canceled while processing
-3: pending command aborted
-4: pending command flushed
-5: invalid xml, no parameter
-6: invalid project reference
-7: invalid source path
-8: invalid destination path
-9: invalid project-init path
-10: invalid project-media path
-11: file '%s' not available
-12: failed copying file '%s'
-13: no frames to copy
-14: failed to copy frame [%d] '%s' to '%s'
-15: insufficient disk space to copy frame [%d] '%s' to '%s'
-16: source path is not local to project
-17: failed reading project database
-18: project auto-exported is off
-19: failed creating init-xml
-20: failed stopping all pending commands
-21: failed processing project init-xml

In some cases additional details about (un)successful XML processing can be found in the regular SCRATCH log file. Note that SCRATCH will always append to existing command-log files. Old command-log files are never automatically removed.

Copy or delete media file (sequences)

For the Copy- commands you specify a source and destination. The source can be a single file or a file sequence, in which case you specify the first frame in the source element. Optionally you can also specify a range to copy from a file sequence, marked by an in- and out-point. Furthermore, there is also the optional  <image mode> element: this specifies whether the files being copied are to be altered to create real-time playable files. Some image formats, such as TIFF for example, can be written in ways that are still legal files, but cannot be played in real-time. The <img_mode>1</img_mode> tag attempts to convert files into a real-time playable format as part of the copy process.

Note that the Delete-command can, next to the source, also specify an optional in-out range.

Create and update a project: use of the project init-folder

All of the create-, append-, insert-, update- and remove-Commands require a project-attribute to specify the project they apply to. For the create-Command this attribute specifies the name of the new project that is created: note that if a project with that name already exists the command will fail.

For all these types of commands, SCRATCH creates a Project XML file and places that in the init-folder within the project folder. Any XML placed in that folder is loaded and processed by SCRATCH when the user opens that project. You can monitor whether there is init-XML pending for a specific project in the start-up screen: in the Session menu on the far right with the project details it will say "XML Pending". When you are inside a project and init-XML is pending, the project name in the Project Tree panel in the CONstruct will change color. You can then use the Refresh button to auto-load the xml.

Command XML -> Watch Folder -> Project-XML to project init-folder -> User opens Project -> process init-folder XML

The init-folder of a project can contain multiple Project-XML files. All will be processed upon the next time the user opens the project.

The last two command of sample06_command.xml first create a new project and then use insert-Command to add a shot and additional output to that new project.


There are two additional options available with Command- and Project-XML that can be used to build a fully automated render pipeline. First you can add the auto_load attribute to the command element.

<command action="update" project="MyProject" command_id="myId123" auto_load="y">

This attribute will cause SCRATCH to open the project specified in the command after it processed the content of the command. Any Project XML in the init-folder is processed normally when auto-opening the project. However, please note that once the project is open - no more command-XML is processed until the project is closed again (manually by the user).

Next you can add an additional <render> element to the Project-XML. Within this element you specify an output node to be processed. When SCRATCH encounters this element it will add the output node to the regular process queue and start to process. Once SCRATCH is done processing the output node (and no other nodes are pending) SCRATCH will automatically save and close the current project and return to the start-up screen. Ready for processing new Command-XML.

See s07_auto_render.xml where the auto_load attribute and render element are combined to create a command that opens a specific project, renders the specific output and then closes the project again - ready for the next command. In the example a new output node is created before it is rendered.

Command-XML -> Load Project + process Project-XML in init-folder -> Render -> On finish, exit -> Ready for the next Command.


When clicking an Application Custom Command in SCRATCH to execute an external script or application, SCRATCH will pass a filename to the script/application in a command line parameter. The file contains Project XML that, depending on the Custom Command settings, includes the full project, the current group, the current CONstruct or just the current selection.

If only the current selection is needed, the XML will start with a <selection> element which in turn contains one or more <shot> elements. See s08_selection.xml on how this selection-XML is formatted. Note that the top selection-element also contains the group- and construct-attributes to specify where the selection was made.

If you created a so called blocking Custom Command (the Wait Till Finished option enabled), SCRATCH passes a second command line parameter to the script/application. This contains the file reference that should be used by the external process to write any results into, in the form of Project XML. SCRATCH will wait for the external script/application to finish and then load/process the XML. This way you can update your current project from a Custom Command.

A Post Render - Custom Command type allows you to link an external script / application to an output node: when the render of the output node is finished, SCRATCH will execute the external script/application. SCRATCH will pass a command line parameter to the script with a file reference to the script/application. This file contains XML with information on the render. See s09_post_render.xml on how this is formatted. Note that depending on the specific output type the XML elements included might differ slightly.  

At the top there is the <render> node with next the <shot> node that represents the actual output node. Scrolling down there is the <frame_table> node which contains all file references created by the render. By parsing this XML your external script/application has all information on the render for further processing.

The s10_post_render_amt_mxf_win.py / s10_post_render_amt_mxf_osx.py is a sample Python script for Windows / OS X that can be used with a Post Render - Custom Command. Background: a regular MXF AMT Export in SCRATCH results in a series of sub-folder with an MXF file in each folder. As SCRATCH is using the AVID (AMT) Library the name of that MXF file is not the name set in the node naming-mask of the output node. The naming-mask is used for the sub-folder. The script, when executed as post-render - makes a copy of all MXF file in the sub-folders to the main root folder and renames the MXF to the intended name according to the naming-mask. 

To be able to use the Python script you must have the Python run-time environment installed on your system. For more information about Python see https://www.python.org. On Windows it is usually enough to install the Python runtime to be able to execute .py files directly by calling them from a command line or double clicking a file in an explorer window. On OS X to be able to run a .py file directly you have to check if the file contains a proper reference to the runtime: the first line of the .py file should contain a so called "shebang" with the path to the installed Python version - which looks like: #!/usr/bin/python. Furthermore the .py file should also have the proper file attributes set. The following terminal command will set the proper attributes: chmod ugo+=rwx myscript.py

Note that you can use or alter the sample script as you please but do read the disclaimer and warranty statement at the top and when on OSX, check the shebang line in the script. To recap on how to use the script:

  • make sure Python run-time environment is installed on the system and copy the script in an accessible folder.
  • create a Post Render Custom Command that points to the script.
  • when creating an MXF AMT Export node, point to the Post Render Custom Command in the corresponding tab in the Output menu.


XSL stands for EXtensible Stylesheet Language and can be e.g. used to transform XML into more human friendly format, like HTML. With XSL you can customize SCRATCH' standard  reporting tools and create your own customized reports.

SCRATCH uses XSLT when you select HTML as your output format for exporting project data. Alternatively you can create a Custom Command that uses a specific XSLT to generate output. When you export project data to HTML, either from the Tools menu or through the project auto-export option, SCRATCH performs a number of tasks.

  • Create an XML file with the project data. 
  • Create a proxy-image file for each shot in the exported scope (CONstruct, Group, Project). The name of the image file is the uuid of the corresponding shot.
  • (optionally) Create a 1D LUT for each shot in the exported scope (CONstruct, Group, Project). The name of the LUT file is the uuid of the corresponding shot.
  • Perform an XSL Transformation with the (automatically) selected XSL files. The result of the transform is an HTML file.

Depending on the export scope you select (Project, Group or Construct), SCRATCH will search for a corresponding XSL file (project.xslt, group.xslt or construct.xslt) in the settings or installation folder. If no such file is found then SCRATCH will default to use the XSLExport.xslt file. The standard SCRATCH installation includes a sample construct.xsl and group.xsl and xslexport.xsl installed. You can customize these file by first copying them to the \Assimilator\Settings folder first and then edit them. 

Note: Editing XSLT requires specialized know-how and is beyond the scope of this manual. The internet provides many sources of information on XSL and its possible usage.

When creating your own XSL you nee to know that SCRATCH provides two parameters as input for the XSL that you can use:

  • level - indicating the scope of the export; project = 0, group = 1, CONstruct = 2. The XSL needs to be aware of the export level as this determines the XML layout.
  • images - this is the folder name where the proxy-images are saved to. The XSL needs to be aware of the folder to be able to reference the proxy images.

The Custom Commands also allow you to specify a XSL file to be used. With the Custom Command you also specify the extension of the final output file. XSLT also allows you to create e.g. csv files.

Rate this Article:

Last Modified:Monday, March 12, 2018
Type: INFO