Difference between revisions of "Maintenance ETL"
| Line 8: | Line 8: | ||
When Maintenance cleans a database, it will determine if there are any records in the following tables that do not belong to any media or file. | When Maintenance cleans a database, it will determine if there are any records in the following tables that do not belong to any media or file. | ||
These records are considered to be orphaned. | These records are considered to be orphaned. | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
==Consolidate Depots== | ==Consolidate Depots== | ||
| Line 38: | Line 23: | ||
If a "candidate" depot will surpass the maximum size set for it, it will be closed and its depot status set to "full." | If a "candidate" depot will surpass the maximum size set for it, it will be closed and its depot status set to "full." | ||
If all contents in the "donor" depot have been merged, it will be deleted from the <code>[Depot]</code> table and the name of the file will be renamed to add <code>.ToBeDeleted</code> to the extension. | If all contents in the "donor" depot have been merged, it will be deleted from the <code>[Depot]</code> table and the name of the file will be renamed to add <code>.ToBeDeleted</code> to the extension. | ||
| + | |||
| + | ==Delete Depots== | ||
| + | This will enumerate through the files in the <code>[[Default ETL Options|datadir]]</code> and <code>[[Default ETL Options|depotdir]]</code> folders. | ||
| + | Any file found with an extension of <code>.ToBeDeleted</code> will be deleted. | ||
| + | |||
| + | ==Delete Media== | ||
| + | This will delete the records in the database associated with the given media. | ||
| + | |||
| + | ==Import Content== | ||
| + | This will navigate the given [[TPIF]] file and import any file contents that may have been in there. | ||
| + | It will only import content that doesn't already exist in the database. | ||
| + | If any content has been imported, a message will be sent to reindex the media. | ||
| + | |||
| + | ==Optimize== | ||
| + | As SQL databases grow over time, the query planner may be using stale statistics for optimization. | ||
| + | This task will update the statistics on all tables in the database. | ||
| + | |||
| + | ==Reindex== | ||
| + | This will cause all files with contents to have their contents indexed. | ||
Revision as of 15:31, 25 July 2022
The Maintenance ETL is tasked with performing long running tasks. These tasks can take a significant amount of time and cause the Analyst Desktop to become unresponsive.
Contents
Tasks Performed
Maintenance is responsible for the following tasks.
Clean Database
When Maintenance cleans a database, it will determine if there are any records in the following tables that do not belong to any media or file. These records are considered to be orphaned.
Consolidate Depots
Consolidating depot files is a process where the unique contents of a smaller depot is merged into a larger depot.
In order for a depot file to be considered as a candidate to receive content, it must have a status of "closed" and the size of the depot less than the maximum.
This is determined by the [DepotStatusID] and [MaximumSize] columns of the [Depot] table in the database.
The algorithm for consolidating two depot files is:
- Choose the largest depot that is closed and smaller than the maximum size as the "candidate" depot
- Choose the smallest depot that is closed as the "donor" depot
- Enumerate through the content in the "donor" depot
- If the contents do not exist in any other depot in Truxton, append the "donor" content to the "candidate" depot
- Add a new record to the
[Content]table referencing the area in the "candidate" depot - Remove the record in the
[Content]table that referenced the "donor" depot
If a "candidate" depot will surpass the maximum size set for it, it will be closed and its depot status set to "full."
If all contents in the "donor" depot have been merged, it will be deleted from the [Depot] table and the name of the file will be renamed to add .ToBeDeleted to the extension.
Delete Depots
This will enumerate through the files in the datadir and depotdir folders.
Any file found with an extension of .ToBeDeleted will be deleted.
Delete Media
This will delete the records in the database associated with the given media.
Import Content
This will navigate the given TPIF file and import any file contents that may have been in there. It will only import content that doesn't already exist in the database. If any content has been imported, a message will be sent to reindex the media.
Optimize
As SQL databases grow over time, the query planner may be using stale statistics for optimization. This task will update the statistics on all tables in the database.
Reindex
This will cause all files with contents to have their contents indexed.
