Database

Tables

The SmartDocumentor solution contains the following tables:

1. Tasks

2. TasksHistory

3. WorkspaceSettings

Tasks

The SmartDocumentor's Tasks table contains the current documents that are still being processed. The following table contains a description of all columns.

Column	Description
ID	Database unique identifier.
TaskID	Internal SmartDocumentor identifier. This identifier will be present in the log files. This helps to identify the error each task had.
Workspace	SmartDocumentor workspace internal identifier
Queue	Current step on where the task is
Priority	Task priority. From 0 to 10. Can be chosen when scanning a document. The default value is 5.
BatchID	Batch Id
BatchName	Batch Name
DocID	Document Id (within the batch)
Pages	Total pages
Task	XML with the metadata information
CreatedOn	Creation date (UTC time zone)
CreatedBy	Created by
Expires	Checkout Expiration Date (UTC time zone)
CheckOutState	Check out state:
	- C -- Checkout
	- R -- Reserved
CheckOutUser	Checkout user. (Domain\Username)

TasksHistory

The TasksHistory table contains all documents that were processed. The following table contains a description of all columns. After the output is generated, the task is moved to this table.

Column	Description
ID	Database unique identifier
TaskID	Internal SmartDocumentor identifier
Workspace	SmartDocumentor's workspace internal identifier
Priority	Task priority. From 0 to 10.
BatchID	Batch Id
BatchName	Batch Name
DocID	Document Id (within the batch)
Pages	Total pages
Task	XML with the metadata information
CreatedOn	Creation date (UTC time zone)

WorkspaceSettings

This table contains all settings used on SmartDocumentor.

Column	Description
ID	Database unique identifier
WorkspaceId	Internal SmartDocumentor identifier
Key	Setting key
Value	Setting value
CreatedOn	Creation date (UTC time zone)
ModifiedOn	Modified date (UTC time zone)

Currently, the only key we use is the batch Id sequence. The key value is "BatchId" and the value is the current batch id sequence. The format is: YYMMDDXXXX. Where YY is year, MM is month, DD is day and XXXX is the day batch id sequence starting in 0001.

Queue States

The following table describes all states that are present in the SmartDocumentor's workflow

Queue Status	Description
ToProcess	After the scan is completed. This is the workflow's first step.
ToReview	After the document is processed and the barcode is read. The SmartDocumentor's review station will only see documents that are in this step.
ToIntegrate	After the document is reviewed.
Deleted	When the document is deleted at the review station. The record on the database will not be deleted. We can keep this record on the database or create a Job to periodic delete these records or move to other location.
Final	When the output ( like XML, PDF or images) where generated and before is moved to another table.
Error	When an error occurred during the processing.

SmartDocumentor Task XML

An XML from SmartDocumentor looks like this:

1. The Property List - This is a list of key/values. The most important keys are:

   1. ScannerLocation -- Scan Location.

   2. _BatchID -- Batch Id.

   3. _BatchName -- Batch Name.

   4. ApplicationId -- Custom custumer field: Application Id.

   5. ReviewedBy - Person that reviewed the document.

2. Task Id -- Internal SmartDocumentor identifier.

3. Last Error Message -- If any error occurred.

4. Document Storage -- The document storage where the file is and the file name on that storage. On the SmartDocumentor storage.config.xml file we can see the path for this storage.

5. The Audit -- List of all the steps the document has passed.

   1. From -- Initial Step. When starts with "Workspace:" means that this is a global step.

   2. To -- Final Step.

   3. Date -- Date on UTC.

   4. Machine -- Computer name that executed this step

.

Documents in Tasks

SELECT 
	TaskID,
	Task.value('(/Task/Document/@ID)[1]', 'varchar(MAX)') AS DocumentId,
	Task,
	BatchID,
	BatchName,
	Task.value('(/Task/PropertyList/TaskProperty[@Name="ApplicationId"]/@Value)[1]', 'varchar(MAX)') as ApplicationId,
	Task.value('(/Task/LastErrorMessage)[1]', 'varchar(MAX)') AS LastErrorMessage,
	Task.value('(/Task/Audit/SDTaskAudit[@To="Error"]/@From)[1]', 'varchar(MAX)') AS LastSDTaskAudit,
	Task.value('(/Task/Audit/SDTaskAudit[@To="Error"]/@Date)[1]', 'varchar(MAX)') AS LastSDTaskAuditDate
FROM Tasks 
WHERE Queue = 'Error'
ORDER BY 5 

 This query returns the tasks that are in Error state on the database. The following table contains a description for all columns.

Result Column Name	Description
TaskID	Internal SmartDocumentor identifier
DocumentId	The document identifier. This is the document name that is on the SDDocs folder.
Task	XML with all metadata information.
BatchID	Batch Id.
BatchName	Batch Name.
ApplicationId	Application Id. If an error occurs before the processing step, this will be NULL.
LastErrorMessage	The error message that put the task in error state. This can be NULL. When this is null, the error will be only on the log folder on the servers.
LastSDTaskAudit	SmartDocumentor last step before the error. With this information, we know to each step we can recover the task. See NOTE
LastSDTaskAuditDate	Last date and time SmartDocumentor processed this task.

NOTE:

We can only recover to the steps that start with "Workspace:". Those steps are the only ones that the SmartDocumentor uses on the database. The other ones are temporary local steps.

To find the step to recover the tasks, we need to follow the workflow and find the previous step that starts with "Workspace:". Then, we need to update the task to that step so it can be reprocessed again. When running the queue state on the database, remove "workspace:" from the queue name. Example:

In this example, the previous step before the error was the "workspace:ToIntegrate". In this case we would recover to queue "ToIntegrate".

Recover Query

UPDATE Tasks
SET Queue = ' RECOVER_STEP'
WHERE 
TaskID IN 
(
 'ID.XML'
)