Grooper Infrastructure (Concept): Difference between revisions

From Grooper Wiki
← Older edit
 
(40 intermediate revisions by one other user not shown)
Line 7: Line 7:
'''Grooper''' is an intelligent document processing platform. It automates document processing tasks, provides user interfaces for user-assisted review of those tasks and allows system architects to design configuration settings.
'''Grooper''' is an intelligent document processing platform. It automates document processing tasks, provides user interfaces for user-assisted review of those tasks and allows system architects to design configuration settings.


Document content, configuration objects, and their settings are all stored in a "'''Grooper Repository'''".
Document content, configuration objects, and their settings are all stored in a '''Grooper Repository'''.




Line 13: Line 13:


Fundamentally, a Grooper Repository is a repository if information stored in a database and a file store. However, there is more to Grooper's infrastructure that allows users to interact with the Grooper Repository and execute processing tasks in the Grooper Repository.
Fundamentally, a Grooper Repository is a repository if information stored in a database and a file store. However, there is more to Grooper's infrastructure that allows users to interact with the Grooper Repository and execute processing tasks in the Grooper Repository.


A Grooper Repository is served by five components:
A Grooper Repository is served by five components:
Line 79: Line 80:


=== Grooper Processing Servers ===
=== Grooper Processing Servers ===
This refers to any machine in the '''Grooper''' "processing environment". A processing environment will be composed of the primary '''Grooper''' host server ''and'' any machine dedicated to hosting Grooper '''Activity Processing''' services.
This refers to any machine in the '''Grooper''' "processing environment". A processing environment will be composed of the primary '''Grooper''' host server ''and'' any machine dedicated to hosting Grooper '''Activity Processing''' services.
*<li class="fyi-bullet"> ''A processing environment can have as many servers as is necessary to meet business requirements.''
 
* Windows Server 2016 or later is required.
* Windows Server 2016 or later is required.
* Server RAM should be 16GB or more.
* Server RAM should be 16GB or more.
Line 85: Line 88:
**<li class="attn-bullet"> Be aware this is a ''minimum''.  High volume scenarios will need more cores to process content efficiently.
**<li class="attn-bullet"> Be aware this is a ''minimum''.  High volume scenarios will need more cores to process content efficiently.
* The port for licensing is 13900 by default.
* The port for licensing is 13900 by default.
**<li class="fyi-bullet"> ''A processing environment can have as many servers as is necessary to meet business requirements.''


=== Client Computer ===
=== Client Computer ===
This refers to a machine connecting to a '''Grooper Repository''' via a browser.  The client computer does not need either the main Grooper application or the Grooper Web Client application installed. It just needs to be able to connect to the '''Grooper Repository''' using a supported browser.
This refers to a machine connecting to a Grooper Repository via a browser.  The client computer does not need either the main Grooper application or the Grooper Web Client application installed. It just needs to be able to connect to the Grooper Repository using a supported browser.
* Google Chrome, Microsoft Edge or Apple Safari are required.
* Google Chrome, Microsoft Edge or Apple Safari are required.
** Other browsers may work but are not supported
** Other browsers may work but are not supported
Line 185: Line 187:
The Grooper Repository's database (aka "Grooper Database") is the core component of Grooper. All aspects of Grooper configuration are stored has metadata in the Grooper Database's tables. The processing status of '''Batches''' and their tasks are stored in the Grooper Database. Statistical information and logs are stored in the Grooper Database. There is no Grooper without the Grooper Database.
The Grooper Repository's database (aka "Grooper Database") is the core component of Grooper. All aspects of Grooper configuration are stored has metadata in the Grooper Database's tables. The processing status of '''Batches''' and their tasks are stored in the Grooper Database. Statistical information and logs are stored in the Grooper Database. There is no Grooper without the Grooper Database.


There are 15 tables (as of Grooper version 2024) that make up the '''Grooper''' database:
There are 15 tables (as of Grooper version 2024) that make up the Grooper Database:


''Please note the Grooper database tables are not listed here in alphabetical order. They are listed in terms of how impactful they are to Grooper's overall architecture.''
''Please note the Grooper database tables are not listed here in alphabetical order. They are listed in terms of how impactful they are to Grooper's overall architecture.''
Line 191: Line 193:
====dbo.TreeNode====
====dbo.TreeNode====


The main '''Grooper''' database table. The ''TreeNode'' table contains one entry for every object in the '''Grooper''' node tree.  Composed of the following columns:
The main Grooper database table. The ''TreeNode'' table contains one entry for every object in the Grooper node tree.  Composed of the following columns:
:: {| class="wikitable"
:: {| class="wikitable"
! Column Name !! Data Type !! Size !! Description
! Column Name !! Data Type !! Size !! Description
|-
|-
| Id || uniqueidentifier || 16 || The node’s unique ID.  This is a GUID autogenerated by '''Grooper''' for each node.  
| Id || uniqueidentifier || 16 || The node’s unique ID.  This is a GUID autogenerated by Grooper for each node.
|-
|-
| RowId || int || 4 || Identity Column for the table generated by SQL server.
| RowId || int || 4 || Identity Column for the table generated by SQL server.
|-
|-
| Name || varchar || 256 || The name of the node.
| Name || varchar || 256 || The name of the node
|-
|-
| TypeName || varchar || 64 || The name of the Grooper object type for the node
| TypeName || varchar || 64 || The name of the Grooper object type for the node
Line 209: Line 211:
| Attributes || int || 4 || A flags attribute indicating whether the node has the following attributes: read-only (cannot be edited), fixed contents (no children may be added), sorted (children will always show up in alphabetical order), has ACL (deprecated in 2024), or is a system object (may not be changed).  
| Attributes || int || 4 || A flags attribute indicating whether the node has the following attributes: read-only (cannot be edited), fixed contents (no children may be added), sorted (children will always show up in alphabetical order), has ACL (deprecated in 2024), or is a system object (may not be changed).  
|-
|-
| NumChildren || int || 4 || Number of children.
| NumChildren || int || 4 || Number of children for the node
|-
|-
| Properties || varchar(MAX) || -1 || Column containing the JSON properties for this node. Results from configuring the node's property grid in the Design Page.
| Properties || varchar(MAX) || -1 || Column containing the JSON properties for this node. Results from configuring the node's property grid in the Design Page.
|-
|-
| NodeValues || varchar(MAX) || -1 || Metadata about the node that pertains to '''Grooper''' modules (Seldom used, except by custom scripting).
| NodeValues || varchar(MAX) || -1 || Metadata about the node that pertains to Grooper modules (Seldom used, except by custom scripting)
|-
|-
| LastModified || datetime2 || 8 || Indicates the most recent date and time the node was modified. Used by various Grooper processes that need to know when a node has been changed. This is a more precise replacement for the ''RowVersion'' column in previous Grooper versions.
| LastModified || datetime2 || 8 || Indicates the most recent date and time the node was modified. Used by various Grooper processes that need to know when a node has been changed. This is a more precise replacement for the ''RowVersion'' column in previous Grooper versions.
|}
==== dbo.FileStoreEntry ====
The ''FileStoreEntry'' table is what marries the Grooper Database with the Grooper File Store.
* It is used to associate Grooper nodes in the ''TreeNode'' table with any files they may have in the Grooper File Store.
* These may be files attached to '''Batch Folders''' (PDFs, TIFFs, etc.) or images attached to '''Batch Pages''' (JPEGs, TIFFs, etc.) or files Grooper '''''Activities''''' and commands generate (Grooper.ClassifyResult.json, Grooper.DocumentData.json, etc.)
:: {| class="wikitable"
! Column Name !! Data Type !! Size !! Description
|-
| Id || int || 4 || Unique row identifier
|-
| FileId || uniqueidentifier || 16 || GUID assigned as the name of the object (stored in the file store with this GUID followed by .grp)
|-
| NodeId || uniqueidentifier || 16 || GUID associated with node from the Grooper node tree
|-
| FileStoreId || uniqueidentifier || 16 || The File Store node's GUID in the Grooper node tree.
|-
| Name || nvarchar || 1024 || The file's name. Examples: "Imported Document.pdf" or "Grooper.Image.jpg" or "Grooper.Layout.json"
|}
|}


Line 230: Line 250:
! Column Name !! Data Type !! Size !! Description
! Column Name !! Data Type !! Size !! Description
|-
|-
| Id || int || 4 || unique row identifier used to tie tasks in the ''ProcessingTasks'' table to the job
| Id || int || 4 || Unique row identifier used to tie tasks in the ''ProcessingTasks'' table to the job
|-
| ActivityType || varchar || 256 || Name of the automated '''''Activity''''' to be completed for the job
|-
|-
| ActivityType || varchar || 256 || name of the automated '''''Activity''''' to be completed for the job
| BatchProcess || varchar || 256 || Name of the parent '''Batch Process''' submitting the processing jobs
|-
|-
| BatchProcess || varchar || 256 || name of the parent '''Batch Process''' submitting the processing jobs
| StepId || uniqueidentifier || 16 || GUID of the '''Batch Process Step''' submitting the job for processing
|-
|-
| StepId || uniqueidentifier || 16 || guid of the '''Batch Process Step''' submitting the job for processing
| StepNo || int || 4 || Numeric position of the '''Batch Process Step''' within its parent '''Batch Process'''
|-
|-
| StepNo || int || 4 || numeric position of the '''Batch Process Step''' within its parent '''Batch Process'''
| StepName || varchar || 256 || Name given to '''Batch Process Step'''
|-
|-
| StepName || varchar || 256 || name given to '''Batch Process Step'''
| Priority || int || 4 || Numeric value (1-5) given to sort processing priority
|-
|-
| Priority || int || 4 || numeric value (1-5) given to sort processing priority
| Status || int || 4 || Job status as indicated numerically (1=Ready, 2=Working, 3=Completed, 4=Paused, 5=Error, 6=Canceled)
|-
|-
| Status || int || 4 || job status as indicated numerically (1=Ready, 2=Working, 3=Completed, 4=Paused, 5=Error, 6=Canceled)
| SubmittedBy || varchar || 256 || Name of user that submitted the job
|-
|-
| SubmittedBy || varchar || 256 || name of user that submitted the job
| BatchId || uniqueidentifier || 16 || GUID of the '''Batch''' in process
|-
|-
| BatchId || uniqueidentifier || 16 || guid of the '''Batch''' in process
| BatchName || varchar || 256 || Name of '''Batch''' in process
|-
|-
| BatchName || varchar || 256 || name of '''Batch''' in process
| ThreadPoolId|| int || 4 || Numeric id of the associated '''Processing Queue''' for the '''Batch Process Step'''
|-
|-
| ThreadPoolId|| int || 4 || numeric id of the associated '''Processing Queue''' for the '''Batch Process Step'''
| Completed || datetime || 8 || Date and time the job was completed
|-
|-
| Completed || datetime || 8 || date/time the job was completed
| Activity || nvarchar || -1 || Allows processing jobs to be submitted from the Search Page.
|}
|}


Line 269: Line 291:
! Column Name !! Data Type !! Size !! Description
! Column Name !! Data Type !! Size !! Description
|-
|-
| Id || int || 4 || unique row identifier
| Id || int || 4 || Unique row identifier
|-
|-
| JobId|| int|| 4 || identifier tying the task to its corresponding Processing Job in the ''ProcessingJob'' table
| JobId|| int|| 4 || Identifier tying the task to its corresponding Processing Job in the ''ProcessingJob'' table
|-
|-
| NodeId || uniqueidentifier || 16 || guid of the node being processed ('''Batch''', '''Batch Folder''' or '''Batch Page''')
| NodeId || uniqueidentifier || 16 || GUID of the node being processed ('''Batch''', '''Batch Folder''' or '''Batch Page''')
|-
|-
| Status || int || 4 || status as indicated numerically (1=Ready, 2=Working, 3=Completed, 4=Paused, 5=Error, 6=Canceled)
| Status || int || 4 || Task status as indicated numerically (1=Ready, 2=Working, 3=Completed, 4=Paused, 5=Error, 6=Canceled)
|-
|-
| UserName || varchar || 256 || windows user that submitted the task
| UserName || varchar || 256 || Windows user that submitted the task
|-
|-
| MachineName || varchar || 256 || hostname of machine running the task
| MachineName || varchar || 256 || Hostname of machine running the task
|-
|-
| Submitted || datetime || 8 || date/time when the task was submitted for processing
| Submitted || datetime || 8 || Date and time when the task was submitted for processing
|-
|-
| Started || datetime || 8 || date/time when the task was started
| Started || datetime || 8 || Date and time when the task was started
|-
|-
| Completed || datetime || 8 || date/time when the task completed
| Completed || datetime || 8 || Date and time when the task completed
|}
|}


====dbo.SessionStats====
==== dbo.ImportJob ====
The entries in this table are related to creating '''Batches''' from imported files. '''Batches''' created from imported are created by "Import Jobs." Import Jobs are created by an '''[[Import Watcher]]''' service in one of two ways:
# Automated by a schedule or polling cycle defined in the '''Import Watcher's''' configuration
# Or, manually created by a user from the Imports Pages.


This table contains statistical information regarding batches in '''Grooper'''. Each row corresponds to a processing job submitted for a step in a '''Batch's''' '''Batch Process'''.
In either case, one row is created in the ''ImportJob'' table for each Import Job to be processed. The row contains important information about the Import Job, including the '''''Import Provider''''' settings.
:: {| class="wikitable"
:: {| class="wikitable"
|-
! Column Name !! Data Type !! Size !! Description
! Column Name !! Data Type !! Size !! Description
|-
|-
| Id || int || 4 || unique row identifier
| Id || uniqueidentifier || 4 || Unique row identifier
|-
| Description || varchar || 256 || Description of the Import Job. Entered by the end user when configuring the Import Job from the Import Watcher or Imports Page.
|-
| Provider || varchar || (-1) || The '''''Import Provider''''' settings used by the Import Job.  Stored as a JSON object. Entered by the end user when configuring the Import Job from the Import Watcher or Imports Page. 
|-
| Priority || int || 4 || Numeric value (1-5) given to sort the Import Job's priority.
|-
| Status || int || 4 || Import Job status as indicated numerically (1=Ready, 2=Working, 3=Completed, 4=Paused, 5=Error, 6=Canceled)
|-
| SubmittedBy || varchar || 256 || Name of the Windows user account that submitted the Import Job
|-
| Submitted || datetime || 8 || Date and time when the Import Job was submitted
|-
| Started || datetime || 8 || Date and time when the Import Job started processing
|-
|-
| BatchId || uniqueidentifier || 16 || guid for batch node
| Completed || datetime || 8 || Date and time when the Import Job completed
|-
|-
| BatchName || varchar || 256 || name of batch
| Files|| int || 4 || Number of files imported by the Import Job
|-
|-
| BatchProcessId || uniqueidentifier || 16 || guid of process node related to the batch
| Bytes || bigint || 8 || Total size of files in the '''Batch''' created by the Import Job
|}
 
==== dbo.ServiceInstance ====
This table contains a list of installed [[Grooper Service]]s. 
 
''Note: If an installed service does not have a reference in this table, it will not show up in Grooper Command Console or on the Machines folder node in a Grooper Repository.''
:: {| class="wikitable"
! Column Name !! Data Type !! Size !! Description
|-
|-
| BatchProcessName || varchar || 256 || name given to process by end user
| Id || int || 4 || unique row identifier
|-
|-
| StepName || varchar || 256 || name of step in the process
| RepositoryId || uniqueidentifier || 16 || Unique ID of the Grooper Repository for which the service is installed
|-
|-
| ProcessingScope || in || 4 || the level of the batch that a step is processing
| MachineName || varchar || 64 || Hostname of the PC running the '''Grooper Service'''
|-
|-
| ModuleName || varchar || 256 || name of '''Grooper''' module
| ServiceName || varchar || 256 || Name of '''Grooper Service''' instance
 
By default, this is "Grooper.ServiceTypeName.##" for each service instance. Ex: The second '''Activity Processing''' service installed for a Grooper Repository would be named "Grooper.ActivityProcessing.02"
|-
|-
| ModuleDisplayName || varchar || 256 || name of '''Grooper''' module given to batch process step
| TypeName || varchar || 256 || Service type name of the '''Grooper Service'''
|-
|-
| MachineName || varchar || 256 || hostname of pc running batch
| PropertiesJson || varchar || 2048 || Property configuration details for the '''Grooper Service'''. Stored as a JSON object.
|-
|-
| UserName || varchar || 256 || windows user under which the batch is running
| UserName || varchar || 64 || Windows user account running the '''Grooper Service'''
|-
|-
| StartTime || datetime || 8 || time batch was initiated
| Password || varchar || 64 || Password hash for the Windows user account running the '''Grooper Service'''
|-
|-
| EndTime || datetime || 8 || time batch ended or was terminated
| InstanceNo || int ||4  || Instance number of the '''Grooper Service'''
|-
|-
| TasksProcessed || int || 4 || number of tasks processed in batch
| ThreadPriority || int || 4 || Numeric value given to the service thread to determine processor priority
|}
|}


==== dbo.CustomStats ====
==== dbo.BatchState ====


This table contains a name / value pair of processing activity statistics tied to a session id. It is used with the ''SessionStats'' table to return processing stats when selecting a '''Batch's''' "Statistics" tab in the Batches Page or when returning query results from the Stats Page.
This table contains information about production '''Batches''' and the processing state they are currently in. This table was created to improve the web client's efficiency when loading large numbers of '''Batches''' to various UI elements (such as the Batches Page).


The "name" is the statistic's name (Ex: "Characters - OCR" or "Processing Time"). The "value" will be a whole integer if the statistic indicates a quantity of actions (Ex: number of OCR characters recognized by the '''''Recognize''''' activity), and a floating point decimal if the value represents a cumulative time span (Ex: the time it took to process the activity). The "session id" is an integer id that corresponds to a row in the ''dbo.SessionStats'' table. This ties the activity statistics to the '''Batch''' and step generating the statistics.
:: {| class="wikitable"
:: {| class="wikitable"
! Column Name !! Data Type !! Size !! Description
! Column Name !! Data Type !! Size !! Description
|-
|-
| SessionId || int || 4 ||Corresponds to a row in the ''dbo.SessionStats'' table tying the stat to an executed step in a '''Batch Process'''.
| Id || int || 4 || Identifier generated by SQL on row creation
|-
| NodeId || uniqueidentifier || 16 || The '''Batch's''' Id (GUID) in the ''TreeNode'' table.
|-
| ProcessId || uniqueidentifier || 16 || The Id (GUID) in the ''TreeNode'' table for the '''Batch Process''' assigned to the '''Batch'''.
|-
| Priority || int || 4 || Numeric value (1-5) given to sort the '''Batch's''' priority
|-
| Status || int || 4 || '''Batch''' status as indicated numerically (1=Ready, 2=Working, 3=Completed, 4=Paused, 5=Error, 6=Canceled)
|-
| Name || varchar || 256 || The '''Batch's''' name
|-
| Process || varchar || 256 || The name of the '''Batch Process''' assigned to the '''Batch'''.
|-
| Step || varchar || 256 || The display name of the current step in the '''Batch Process''' being processing.
|-
|-
| Name || Varchar || 64 ||The statistic's name
| Activity || varchar || 256 || The name of the Grooper '''''Activity''''' assigned to the step currently being processed.
|-
| CreatedBy || varchar || 256 || Name of the Windows user account executing the step.
|-
|-
| Value || Float || 8 ||The statistics value
| Created || datetime || 8 || Date and time the '''Batch''' was created.
|}
|}


==== dbo.FileStoreEntry ====
====dbo.SessionStats====
* This table contains the information necessary for associating filestore objects with '''Grooper''' nodes. Note: a node with associated files will also contain those files’ filestore object locations, names, and mimetypes in the “files” column of TreeNode.
 
This table contains statistical information from processing '''Batches''' in Grooper. Each row corresponds to a processing job submitted for a step in a '''Batch's''' '''Batch Process'''. It is used (along with the ''CustomStats'' table) to return processing stats when selecting a '''Batch's''' "Statistics" tab in the Batches Page or when returning query results from the Stats Page.
:: {| class="wikitable"
:: {| class="wikitable"
! Column Name !! Data Type !! Size !! Description
! Column Name !! Data Type !! Size !! Description
|-
|-
| Id || int || 4 || sequential number assigned to object in row
| Id || int || 4 || Identifier generated by SQL on row creation
|-
| BatchId || uniqueidentifier || 16 || The Id (GUID) in the ''TreeNode'' table for the '''Batch Process''' assigned to the '''Batch'''.
|-
| BatchName || varchar || 256 || Name of '''Batch'''
|-
| BatchProcessId || uniqueidentifier || 16 || The Id (GUID) in the ''TreeNode'' table for the '''Batch Process''' assigned to the '''Batch'''.
|-
|-
| FileId || uniqueidentifier || 16 || guid assigned as the name of the object (stored in the filestore with this number followed by .grp)
| BatchProcessName || varchar || 256 || Name of the '''Batch Process'''
|-
|-
| NodeId || uniqueidentifier || 16 || guid associated with node from '''Grooper''' tree
| StepName || varchar || 256 || Name of the step in the '''Batch Process'''
|-
|-
| FileStoreId || uniqueidentifier || 16 || guid given to the node referencing the file store
| ProcessingScope || int || 4 || The level of the '''Batch''' that a step is processing.  
|}
==== dbo.ImportJob ====
: ''This table is not present prior to Grooper 2023''
* The entries in this table are related to the creation of '''Batches''' in '''Grooper''' via the submission of '''Import Jobs''' from an '''''Import Provider'''''. Each row is an '''Import Job''' to be processed.
:: {| class="wikitable"
|-
|-
! Column Name !! Data Type !! Size !! Description
| ModuleName || varchar || 256 || Name of Grooper module. This refers to an '''''Activity's''''' object name in code.
* This differs slightly from the module's "display name."
* Ex: The '''''Classify''''' activity's module name is "Grooper.Activities.ClassifyFolders"
|-
|-
| Id || uniqueidentifier || 4 || unique row identifier
| ModuleDisplayName || varchar || 256 || The Grooper module's display name. This is the '''''Activity's''''' common name.
|-
|-
| Description || varchar || 256 || description of the job as written by the end user
| MachineName || varchar || 256 || Hostname of the PC running the step.
|-
|-
| Provider || varchar || (-1) || the import provider leveraged by this job
| UserName || varchar || 256 || Windows user under which the step is running
|-
|-
| Priority || int || 4 || numeric value (1-5) given to sort batch priority
| StartTime || datetime || 8 || Date and time the step was initiated
|-
|-
| Status || int || 4 || status as indicated numerically (ready, working, completed, paused)
| EndTime || datetime || 8 || Date and time the step ended or was terminated
|-
|-
| SubmittedBy || varchar || 256 || name of account that submitted the import job
| TasksProcessed || int || 4 || Number of tasks processed for the step
|}
 
==== dbo.CustomStats ====
 
This table contains a name / value pair of processing activity statistics tied to a session id. It is used with the ''SessionStats'' table to return processing stats when selecting a '''Batch's''' "Statistics" tab in the Batches Page or when returning query results from the Stats Page.
 
The "name" is the statistic's name (Ex: "Characters - OCR" or "Processing Time"). The "value" will be a whole integer if the statistic indicates a quantity of actions (Ex: number of OCR characters recognized by the '''''Recognize''''' activity), and a floating point decimal if the value represents a cumulative time span (Ex: the time it took to process the activity). The "session id" is an integer id that corresponds to a row in the ''SessionStats'' table. This ties the activity statistics to the '''Batch''' and step generating the statistics.
:: {| class="wikitable"
! Column Name !! Data Type !! Size !! Description
|-
|-
| Submitted || datetime || 8 || date/time when import job was submitted
| SessionId || int || 4 ||Corresponds to a row in the ''SessionStats'' table tying the stat to an executed step in a '''Batch Process'''.
|-
|-
| Started || datetime || 8 || date/time when import job started processing
| Name || Varchar || 64 ||The statistic's name
* Ex: "Characters - OCR"
* Ex: "Processing Time"
|-
|-
| Completed || datetime || 8 || date/time when import job completed
| Value || Float || 8 ||The statistic's value
|}
 
====dbo.NodeReference====
 
This table contains a list of all Grooper nodes that are referenced by another Grooper node. For example: a '''Data Type''' being referenced by a '''Data Field''' to return document data.
* This table is used to protect referenced nodes from being deleted. In Design, Grooper will throw an error if a user attempts to delete a node that is actively being referenced from being deleted.
* This table is also used in determining if nodes can be moved between one '''Project''' and another (or if a node they are referencing must be moved as well).
 
:: {| class="wikitable"
! Column Name !! Data Type !! Size !! Description
|-
|-
| Files|| int || 4 || number of files imported with job
| NodeId || uniqueidentifier || 16 || The Id (GUID) of the node in the ''TreeNode'' table that is being referenced.
 
Ex: A '''Data Field''' references a '''Data Type'''. This is the '''Data Type'''
|-
|-
| Bytes || bigint || 8 || size of files in batch created on import
| ReferencingNodeId || uniqueidentifier || 16 || The Id (GUID) of the node in the ''TreeNode'' table that is making the reference.
 
Ex: A '''Data Field''' references a '''Data Type'''. This is the '''Data Field'''
|}
|}
==== dbo.Lock ====
==== dbo.Lock ====
* This table contains one row per locked node; prevents overlapping access to various resources.
This table contains one row per locked node. Nodes are commonly locked when an '''Activity Processing''' service is actively processing it. This prevents overlapping access to various resources.
:*<li class="fyi-bullet"> Locks typically resolve on their own. Grooper will automatically lock and unlock nodes as needed. In circumstances where Grooper does not unlock a node, users may manually remove the lock from the node's Advanced tab in the Design Page. Only in extreme circumstances would a user need to delete the lock by deleting the row from this database table.
:: {| class="wikitable"
:: {| class="wikitable"
! Column Name !! Data Type !! Size !! Description
! Column Name !! Data Type !! Size !! Description
|-
|-
| NodeId || uniqueidentifier || 16 || guid of node from '''Grooper''' hierarchy tree
| Id || int || 4 || Identifier generated by SQL on row creation
|-
|-
| UserName || varchar || 64 || windows user that applied the unlock
| NodeRowId || int || 4 || The "RowId" of the node in the ''TreeNode'' table that is locked.
|-
|-
| MachineName || varchar || 64 || hostname of the machine where the unlock occurred
| UserName || varchar || 64 || Windows user account that applied the lock
|-
|-
| ModuleName || varchar || 64 || '''Grooper''' module that executed the unlock
| MachineName || varchar || 64 || Hostname of the machine where the lock occurred
|-
|-
| ProcedureName || varchar || 64 || the procedure executed to apply the unlock
| ModuleName || varchar || 64 || Grooper module that applied the lock
|-
|-
| CreateDate || smalldatetime || 4 || date and time the unlock occurred
| ProcedureName || varchar || 64 || Grooper procedure that applied the lock
|-
| CreateDate || smalldatetime || 4 || Date and time the lock occurred
|}
|}
==== dbo.Log ====
 
* This table contains the '''Grooper''' log, which is represented in the '''Event Viewer''' of '''Grooper Design Studio'''.  
==== dbo.DocIndex ====
This table contains one row for each '''Batch Folder''' and their '''''Content Type''''' (e.g. '''Document Type'''). Along with the ''IndexState'' table, the ''DocIndex'' table was added to improve the efficiency of the '''[[Indexing Service]]'''.
 
''Note: Batch Folders with no Content Type assigned will not appear in this table.''
 
:: {| class="wikitable"
:: {| class="wikitable"
! Column Name !! Data Type !! Size !! Description
! Column Name !! Data Type !! Size !! Description
|-
|-
| Id || int || 4 || unique row identifier
| Id || int || 4 || Identifier generated by SQL on row creation
|-
| TimeStamp || datetime || 8 || date and time the event was logged
|-
|-
| Type || int || 4 || type of event logged
| NodeId || uniqueidentifier || 16 || The '''Batch Folder's''' Id (GUID) in the ''TreeNode'' table.
|-
|-
| ProcName || varchar(MAX) || -1 || name of procedure associated with the event
| TypeId || uniqueidentifier || 16 || The Id (GUID) in the ''TreeNode'' table for the '''''Content Type''''' assigned to the '''Batch Folder'''.
|}
 
==== dbo.IndexState ====
This table contains one row for each document indexed according to an '''''[[Indexing Behavior]]'''''. Along with the ''DocIndex'' table, the ''IndexState'' table was added to improve the efficiency of the '''[[Indexing Service]]'''
 
:: {| class="wikitable"
! Column Name !! Data Type !! Size !! Description
|-
|-
| Message || varchar(MAX) || -1 || message displayed in and defining, in general, the event
| Id || int || 4 || Identifier generated by SQL on row creation
|-
|-
| UserAccount || varchar || 64 || windows user under which the event triggered
| FolderId || uniqueidentifier || 16 || The '''Batch Folder's''' Id (GUID) in the ''TreeNode'' table.
|-
|-
| MachineName || varchar || 64 || hostname of the machine under which the event triggered
| TypeId || uniqueidentifier || 16 || The Id (GUID) in the ''TreeNode'' table for the '''''Content Type''''' assigned to the '''Batch Folder'''.
|-
|-
| NodeId || uniqueidentifier || 16 || guid of parent node associated with ‘NodeName’
| IndexName || varchar || 128 || The index's name. Defined in the '''''Indexing Behavior'''''.
|-
|-
| NodeName || varchar || 256 || Item Name, or name of node associated with the triggering of the event
| LastUpdated || datetime2 || 7 || Date and time the document was updated in the index.
|-
|-
| NodeType || varchar || 64 || type of '''Grooper''' Node defining the ‘NodeName’
| PropHash || varchar || 64 || Hash of root properties at last index. Used to determine if the index needs to be updated when the document is modified.
|}
'''dbo.NodeReference'''
* This table contains a list of all referenced nodes, and what other nodes they are referenced by. Used to protect referenced nodes from deletion, and in determining what nodes are necessary on exporting.
:: {| class="wikitable"
! Column Name !! Data Type !! Size !! Description
|-
|-
| NodeId || uniqueidentifier || 16 || guid given to node in '''Grooper''' hierarchy tree
| TextHash || varchar || 64 || Hash of text content at last index. Used to determine if the index needs to be updated when the document's text data (OCR or native text) is modified.
|-
|-
| ReferencingNodeId || uniqueidentifier || 16 || guid of parent node associated with ‘NodeId’
| DataHash || varchar || 64 || Hash of index data at last index. Used to determine if the index needs to be updated when the document's index data ('''Data Model''') is modified.
|}
|}


==== dbo.ServiceInstance ====
==== dbo.Log ====
* This table contains a list of installed services.  Note: If an installed service does not have a reference in this table, it will not show up in '''Grooper''' Config.
* This table contains the Grooper log, which is represented in the '''Event Viewer''' of '''Grooper'''.  
:: {| class="wikitable"
:: {| class="wikitable"
! Column Name !! Data Type !! Size !! Description
! Column Name !! Data Type !! Size !! Description
Line 439: Line 541:
| Id || int || 4 || unique row identifier
| Id || int || 4 || unique row identifier
|-
|-
| RepositoryId || uniqueidentifier || 16 ||  
| TimeStamp || datetime || 8 || date and time the event was logged
|-
|-
| MachineName || varchar || 64 || hostname of pc running '''Grooper''' service
| Type || int || 4 || type of event logged
|-
|-
| ServiceName || varchar || 256 || name of '''Grooper''' service running
| ProcName || varchar(MAX) || -1 || name of procedure associated with the event
|-
|-
| TypeName || varchar || 256 || type of '''Grooper''' service running
| Message || varchar(MAX) || -1 || message displayed in and defining, in general, the event
|-
|-
| PropertiesJson || varchar || 2048 || json information defining /Node Properties/Properties
| UserAccount || varchar || 64 || windows user under which the event triggered
|-
|-
| UserName || varchar || 64 || windows user running '''Grooper''' service
| MachineName || varchar || 64 || hostname of the machine under which the event triggered
|-
|-
| Password || varchar || 64 || password of windows user running '''Grooper''' service
| NodeId || uniqueidentifier || 16 || GUID of parent node associated with ‘NodeName’
|-
|-
| InstanceNo || int ||4  || number of a specific service on a machine
| NodeName || varchar || 256 || Item Name, or name of node associated with the triggering of the event
|-
|-
| ThreadPirority || int || 4 || numeric value given to service thread to determine processor priority
| NodeType || varchar || 64 || type of Grooper Node defining the ‘NodeName’
|}
|}


Line 467: Line 569:
| Name || varchar || 64 || The name is always "DatabaseVersion"
| Name || varchar || 64 || The name is always "DatabaseVersion"
|-
|-
| Value || varchar || 1024 || Current installed '''Grooper''' version
| Value || varchar || 1024 || Current installed Grooper version
|}
|}


Using a SQL database as the core of '''Grooper''' allows for great efficiency. Every property of every object in '''Grooper''', as a result of being a simple entry in a table, can be loaded into memory and accessed nearly instantly.  This would not be the case otherwise (if for example '''Grooper''' repositories were defined by something like a project file), as file i/o is one of the slowest aspects of modern computing. This also allows the discrete management of objects on an individual basis to allow multiple users to work in one environment and prevent work overlap by locking objects.
==== Benefits of database architecture ====
 
Using a SQL database as the core of Grooper is part of what makes it so efficient. Every property of every node in Grooper is a simple entry in a table. As a result, these objects and their properties can be loaded into memory and accessed nearly instantly.  As file I/O is one of the slowest aspects of modern computing, this would not be the case otherwise (For example: If Grooper Repositories were defined by something like a project file). This also allows objects to be managed discretely on an individual basis, which allows multiple users to work in one environment and prevent overlapping work by locking objects.


===The File Store===
===The File Store===
The '''[[File Store]]''' in '''Grooper''' is a file share in a Windows environment. It houses the files associated with objects in '''Grooper''' that have information that would otherwise be inefficient to store in (a cell in) a database table.
The '''[[File Store]]''' in Grooper is a file share in a Windows environment. It houses the files associated with nodes in Grooper that have information that would otherwise be inefficient to store in a cell in a database table.


The '''Grooper Filestore''' exists at a user-specified location.  This may be a local or an network path, but if a filestore is given a local path, computers connecting to that repository remotely will not be able to access it.  If you want to set up a repository so that other computers can connect to it, make sure you reference the filestore using a UNC path!  
The Grooper File Store exists at a user-specified location.  This should always be a network path (UNC path). If a File Store is given a local path, computers connecting to that repository remotely will not be able to access it.  To set up a Grooper Repository so that other computers can connect to it, make sure you reference the File Store using a UNC path!  


The filestore contains three levels of directories.  A typical filestore entry will exist on disk as, e.g. 00 > 00 > 00 > [guid].grp.  Each of the lowest-level folders in the filestore will have a maximum of 256 files, at which point a new folder at that level will be created.  If the lowest level contains 256 folders, a new folder will be created at the level above; this gives the '''Grooper''' filestore a limit of 256 ^ 4 = 4,294,967,296 files stored on disk.  
The File Store contains three levels of directories.  A File Store entry will exist on disk as a file in the lowest level with a .grp extension (e.g. 00 > 00 > 00 > 00000000-0000-0000-0000-000000000000.grp).  Each of the lowest-level folders in the File Store will have a maximum of 256 files, at which point a new folder at that level will be created.  If the lowest level contains 256 folders, a new folder will be created at the level above; this gives the Grooper File Store a limit of 256 ^ 4 = 4,294,967,296 files stored on disk.


While the filestore entries are all given .grp extensions, the contents of the file are unaltered from their “actual” form.  If you navigate, for example, to the .grp file associated with an pdf imported using full import, you can open it and view it with a pdf viewer.  The files in the filestore are intentionally obfuscated to prevent users from interacting with them outside of '''Grooper''', as they are essentially '''Grooper-internal''' objects.  
While the File Store entries are all given .grp extensions, the contents of the file are unaltered from their "actual" form.  If you navigate, for example, to the GRP file associated with an pdf imported using full import, you can open it and view it with a PDF viewer.  The files in the file store are intentionally obfuscated to prevent users from interacting with them outside of Grooper, as they are essentially "Grooper-internal" objects.  


Although the majority of files in the filestore relate to batch objects (split pages, imported documents, image processing undo objects, etc.), some files are the result of other in-'''Grooper''' processes such as layout data, OCR character data, etc.
Although the majority of files in the File Store relate to '''Batch''' objects (a page's image or imported files), some files are the result of other "in-Grooper" processes, such as layout data, OCR character data, extracted index data, and more.


<!---
<!---

Latest revision as of 10:20, 6 November 2024

This article is about the current version of Grooper.

Note that some content may still need to be updated.

20242023.1

Grooper Infrastructure refers to the computing underpinnings of what makes up a Grooper Repository and the software that allows the Grooper platform to automate tasks and users to interface with it.

What is Grooper?

Grooper is an intelligent document processing platform. It automates document processing tasks, provides user interfaces for user-assisted review of those tasks and allows system architects to design configuration settings.

Document content, configuration objects, and their settings are all stored in a Grooper Repository.


What is the infrastructure that serves a Grooper Repository's?

Fundamentally, a Grooper Repository is a repository if information stored in a database and a file store. However, there is more to Grooper's infrastructure that allows users to interact with the Grooper Repository and execute processing tasks in the Grooper Repository.


A Grooper Repository is served by five components:

  1. The Grooper Database
    • A Grooper Repository consists of a database and a file store.
    • The Grooper Database stores information about Grooper objects (including Batches, Content Models and Batch Processes), information related processing document content, statistical and log information and more.
    • The Grooper Database is made up of 15 tables in a SQL Server database.
  2. The Grooper File Store
    • A Grooper Repository consists of a database and a file store.
    • The Grooper File Store houses files associated with objects in the Grooper Database.
    • The most common thing stored in the Grooper File Store are images/page files associated with Batch Pages in a Batch and files (PDFs, TIFFs, ZIPs etc) associated with a Batch Folder in a Batch.
    • The Grooper File Store is made up of a folder location containing subfolders with GRP files in a Windows file share.
  3. The Grooper application layer
    • The Grooper software application "sits on top of" one or more Grooper Repositories.
    • The information stored in a Grooper Repository's Database and File Store is displayed to the user by the Grooper software.
    • The Grooper application layer is what allows users to interact with document content, design and test configuration objects, and execute manual tasks like user-driven review tasks.
  4. The Grooper Web Server
    • The Grooper Web Server is required to run Grooper's web client.
    • The Grooper Web Server hosts the Grooper application's program files and delivers the Grooper software to end users via a web browser.
  5. Grooper Processing Servers
    • Automated tasks in Grooper are executed by "Activity Processing" services. These services are created in Grooper, installed as Windows services on a server and run in the background, picking up and executing tasks as content flows through the Grooper Repository.
    • Any machine installed with Activity Processing services is referred to as a "Processing Server".
      • For smaller environments, the Grooper Web Server may have all the necessary processing resources to execute Grooper tasks. In this case, the Grooper Web Server will also be a Grooper Processing Server.
      • For larger environments, they will have multiple Grooper Processing Servers, as many as needed to handle the volume of tasks flowing through the Grooper Repository.

Understanding Grooper's underlying system architecture sets a groundwork for knowing how to interact with the application. Knowing how it's built or "what's under the hood" can be a great way to better understand how to use it.

System Architecture and Requirements

The Grooper Database: SQL Server

This refers to any machine installed with Microsoft SQL Server hosting a Grooper Database.

  • Windows Server 2016 or later is required.
  • SQL Server 2014 or later is required.
  • A Grooper Database should allow for a size of 20GB or more.
  • The scaling of a Grooper Database should be set to percentage, ideally, not the default of a few MB. This will reduce frequent scaling that hinders performance.
  • The SQL Port is 1433.
    • For smaller installs, the Grooper SQL Server may be the same machine as the primary Grooper host server. For larger installs, we recommend a dedicated server for the Grooper SQL Server.

⚠

Be aware. We do not recommend using SQL as a Service (SQLaaS) solutions to sand up the Grooper Database for most scenarios. Please refer to our Cloud Deployment Guidance below for more information.

The Grooper File Store: Windows File Share

This refers to the machine hosting the Grooper File Store folders.

  • Windows Server 2016 or later is required
  • The ideal file size would accommodate for roughly 150GB for small to medium installs.
  • The authentication requires CIFS or SMB.
  • For smaller installs, the Grooper File Store may be located on the primary Grooper host server. For larger installs, the Grooper File Store should be located on its own server.

The Grooper Web Server

This refers to the server hosting the Grooper Web Client application. Also referred to as the "Grooper host server" as it is the server hosting Grooper's software components.

  • Windows Server 2016 or later is required
  • Server RAM should be 16GB or more.
  • The CPU should consist of 4 or more cores.
  • The port for licensing is 13900 by default.
  • Internet Information Services (IIS) must be installed and properly configured.
    • For all configuration requirements visit the Installing IIS portion of the "Install and Setup" article.
  • The Grooper app pool identity configured in IIS must have read/write access to the Grooper Filestore and Database.
  • Requires an SSL Certificate for encrypted connections.
    • BE AWARE: The Grooper application (GrooperInstall_##.##.####.exe) must be installed on this server. The Grooper Web Client application (GrooperWebClient_##.##.####.exe) leverages Grooper DLLs to function.
    • For smaller installs, the Grooper Web Server and Grooper SQL Server may be the same machine. For larger installs, this is not recommended. We recommend a dedicated server for the Grooper Web Server.

Grooper Processing Servers

This refers to any machine in the Grooper "processing environment". A processing environment will be composed of the primary Grooper host server and any machine dedicated to hosting Grooper Activity Processing services.

  • A processing environment can have as many servers as is necessary to meet business requirements.
  • Windows Server 2016 or later is required.
  • Server RAM should be 16GB or more.
  • The CPU should consist of 4 or more cores.
    • Be aware this is a minimum. High volume scenarios will need more cores to process content efficiently.
  • The port for licensing is 13900 by default.

Client Computer

This refers to a machine connecting to a Grooper Repository via a browser. The client computer does not need either the main Grooper application or the Grooper Web Client application installed. It just needs to be able to connect to the Grooper Repository using a supported browser.

  • Google Chrome, Microsoft Edge or Apple Safari are required.
    • Other browsers may work but are not supported
  • User logs in using Windows Account (Domain or Local) on Grooper Web Server.

Additional requirements for remote scanning users only

This refers to a client machine hooked up to a scanner, used to scan documents into Grooper using the web client. These machines need to have Grooper Desktop installed and running.

Grooper Desktop is a lightweight application installed separately from the main installation of Grooper. It is used to allow a computer with a physical document scanner to upload scanned documents directly to the Grooper Web Server.

Windows Prerequisites

The following are included in the installation package of Grooper and are required for installation (listed are x64 packages.)

  • vc_redist_2012_update4_x64
    Microsoft Visual C++ 2012 Redistributable (x64) - 11.0.61030
  • vc_redist_2010_sp1_x64
    Microsoft Visual C++ 2010 x64 Redistributable Setup
  • vc_redist_2013_x64
    Microsoft Visual C++ 2013 Redistributable (x64) - 12.0.30501
  • vc_redist.x64
    Microsoft Visual C++ 2017 Redistributable (x64) - 14.10.25017
Visual C++ Redistributable Packages install runtime components of Visual C++ Libraries on a computer that does not have Visual C++ installed. The libraries are required to run applications that are developed by using the corresponding version of Visual C++.
  • NDP462-DevPack-KB3151934-ENU
    Microsoft .NET Framework 4.6.2 Developer Pack
.NET is an integral part of many apps running on Windows and provides common functionality for those apps to run. For developers, the .NET Framework provides a comprehensive and consistent programming model for building apps that have visually stunning user experiences and seamless and secure communication.
  • SQLSysClrTypes
In LINQ to SQL, the data model of a relational database maps to an object model that is expressed in the programming language of your choice. When the application runs, LINQ to SQL translates the language-integrated queries in the object model into SQL and sends them to the database for execution. When the database returns the results, LINQ to SQL translates the results back to objects that you can work with in your own programming language.
In order to translate data between the object model and the database, a type mapping must be defined. LINQ to SQL uses a type mapping to match each common language runtime (CLR) type with a particular SQL Server type. You can define type mappings and other mapping information, such as database structure and table relationships, inside the object model with attribute-based mapping. Alternatively, you can specify the mapping information outside the object model with an external mapping file.
  • AccessDatabaseEngine_x64
Microsoft Access Database Engine contains essential components that make it possible for Microsoft Office programs to interact with software services and applications that do not belong to Microsoft. For instance, if you are working in a program that gives you the possibility to export tables directly to Microsoft Access, then you need the database engine to make that happen.
The Microsoft Access Database Engine is available in multiple editions, which mainly depend on which Microsoft Office product you’re using. The hint is in the year. As such, if you use Microsoft Office 2016, then you need Microsoft Access Database Engine 2016.
  • ReportViewer
Used for viewing built in Grooper Reports, which uses a Microsoft Reporting framework.

About thread allocation for Grooper services

Be aware of the "n minus one" rule!

Services are assigned a number of CPU threads when you install them. For some services, like Import Watcher, the will always run using a single thread. Activity Processing services can run using multiple threads.

Keep in mind, your machine only has a certain number of processing threads available. You will run into errors if you over-allocate your available threads.

Remember too, the operating system itself must always have a single thread available to run. So, the absolute maximum number of threads you can assign to all your services should not go beyond the total number of threads available minus one reserved for the operating system. Hence, the "n minus one" rule.

The "n minus one" rule is as follows:

  • If "n" is the maximum number of threads available on your machine, the maximum number of threads you can distribute to Grooper services is "n" minus one.

Be aware of the "n minus x" rule!

Other programs running in the background will need threads to run as well.

  • If SQL is installed on the same machine as your Grooper services, you should follow an "n minus two" rule, reserving one for the OS and one for SQL.
  • If IIS and SQL are installed on the same machine as your Grooper services, you should follow an "n minus three" rule, reserving one for the OS, one for SQL, and one for IIS.
  • If other applications, such as anti-virus software, are running in the background you will need to reserve threads for those applications as well.
  • This is one of the many reasons why we recommend distributing SQL, IIS, and processing services across multiple machines.

THE LONG STORY SHORT HERE IS DO NOT OVER-ALLOCATE YOUR AVAILABLE THREADS! GROOPER CAN BEHAVE ERRATICALLY IF YOU DO!

Cloud Deployment Guidance

Grooper can be deployed using a variety of cloud-based resources. At its core, Grooper needs access to a SQL database and a Windows file system to set up a Grooper Repository. Whether you’re deploying using Amazon Web Services or Microsoft Azure or some other cloud computing platform, you should be able to deploy Grooper as long as you have those two things.

However, in practice, we have found not all cloud services are created equal.

SQL Guidance

For high volume environments, we recommend hosting SQL on a VM rather than using a “SQL as a Service” solution (for example Azure SQL Database).

  • High volume environments have a high transaction rate with the Grooper Database which can present several issues when using a SQLaaS solution.
  • High volume environments will experience better performance using “Simple” recovery mode as opposed to full. If the SQLaaS does not allow you to use “Simple” recovery, do not use it.
  • If you already have SQL licensing, it may also be more cost effective for your organization to host SQL on a VM.

For all environments, you may experience time out issues when using a SQLaaS solution over a VM.

  • For small (mostly user directed) environments, this will be less of an issue. For larger (more automated and larger volume) environments, this issue will be more apparent.

Even when hosting SQL on a VM, we recommend configuring autogrow by fairly small increments.

  • In a recent high-volume deployment using Microsoft Azure, we found an autogrow setting of 2048 Mb to be problematic. The VM could not grow the database in time to keep up with Grooper’s transaction rate. However, dropping autogrow down to 256 Mb resolved the issue.

File Store Guidance

We do not recommend using Azure Files currently. Users should use Azure Disk Storage instead.

  • Azure Files creates files in such a way that we have experienced multithreading issues when an item is created/modified in the file store by one thread and then (nearly) immediately accessed by another thread.
    • A common scenario would be a Split Pages step creating a page in the file store then a subsequent Image Processing step immediately attempting to process the created page in the next step. The page is saved to the file store in such a way that Grooper may “know” a file exists, but is not able to immediately access it for the Image Processing activity to process it, resulting in an error being thrown.
    • This will be more common in high volume environments with large amounts of threads allocated to processing tasks. However, it will likely affect even small environments.

Grooper Repository details

A Grooper Repository is fundamentally two things:

  • A database which stores Grooper objects and their property configurations (and information related to executing and processing those objects)
  • A file store which stores files associated with Grooper objects.

But what makes up that database and file store? Here, you will find more details on the tables contained in a Grooper Database and the file structure of a Grooper File Store.

The Database

The Grooper Repository's database (aka "Grooper Database") is the core component of Grooper. All aspects of Grooper configuration are stored has metadata in the Grooper Database's tables. The processing status of Batches and their tasks are stored in the Grooper Database. Statistical information and logs are stored in the Grooper Database. There is no Grooper without the Grooper Database.

There are 15 tables (as of Grooper version 2024) that make up the Grooper Database:

Please note the Grooper database tables are not listed here in alphabetical order. They are listed in terms of how impactful they are to Grooper's overall architecture.

dbo.TreeNode

The main Grooper database table. The TreeNode table contains one entry for every object in the Grooper node tree. Composed of the following columns:

Column Name Data Type Size Description
Id uniqueidentifier 16 The node’s unique ID. This is a GUID autogenerated by Grooper for each node.
RowId int 4 Identity Column for the table generated by SQL server.
Name varchar 256 The name of the node
TypeName varchar 64 The name of the Grooper object type for the node
ParentId uniqueidentifier 16 ID of the node’s parent. FYI: The Grooper Root node's "parent id" is always 00000000-0000-0000-0000-000000000000.
NodeIndex int 4 Index of the node in its parent container. These values are zero indexed. Ex: a Content Model that is the first node inside a Project (its parent container) will have an index value of "0". A Content Model that is the tenth node inside a Project will have an index value of "9".
Attributes int 4 A flags attribute indicating whether the node has the following attributes: read-only (cannot be edited), fixed contents (no children may be added), sorted (children will always show up in alphabetical order), has ACL (deprecated in 2024), or is a system object (may not be changed).
NumChildren int 4 Number of children for the node
Properties varchar(MAX) -1 Column containing the JSON properties for this node. Results from configuring the node's property grid in the Design Page.
NodeValues varchar(MAX) -1 Metadata about the node that pertains to Grooper modules (Seldom used, except by custom scripting)
LastModified datetime2 8 Indicates the most recent date and time the node was modified. Used by various Grooper processes that need to know when a node has been changed. This is a more precise replacement for the RowVersion column in previous Grooper versions.

dbo.FileStoreEntry

The FileStoreEntry table is what marries the Grooper Database with the Grooper File Store.

  • It is used to associate Grooper nodes in the TreeNode table with any files they may have in the Grooper File Store.
  • These may be files attached to Batch Folders (PDFs, TIFFs, etc.) or images attached to Batch Pages (JPEGs, TIFFs, etc.) or files Grooper Activities and commands generate (Grooper.ClassifyResult.json, Grooper.DocumentData.json, etc.)
Column Name Data Type Size Description
Id int 4 Unique row identifier
FileId uniqueidentifier 16 GUID assigned as the name of the object (stored in the file store with this GUID followed by .grp)
NodeId uniqueidentifier 16 GUID associated with node from the Grooper node tree
FileStoreId uniqueidentifier 16 The File Store node's GUID in the Grooper node tree.
Name nvarchar 1024 The file's name. Examples: "Imported Document.pdf" or "Grooper.Image.jpg" or "Grooper.Layout.json"

dbo.ProcessingJob

This table contains "Processing Jobs" submitted for Activity processing for a production Batch.

The ProcessingJobs table is related to the ProcessingTasks table. A single Processing Job will consist of one or more "Processing Tasks", depending on how many objects are in the Activity's scope (the Batch itself, its Batch Folders or Batch Pages).

For example, a Batch advances to the "Recognize" step in its Batch Process. There are 20 Batch Pages in scope to be processed.

  • A single row will be created in the ProcessingJob table for the job.
  • 20 corresponding rows will be created in the ProcessingTasks table, one for each task in scope (i.e one for each Batch Page processed).
Column Name Data Type Size Description
Id int 4 Unique row identifier used to tie tasks in the ProcessingTasks table to the job
ActivityType varchar 256 Name of the automated Activity to be completed for the job
BatchProcess varchar 256 Name of the parent Batch Process submitting the processing jobs
StepId uniqueidentifier 16 GUID of the Batch Process Step submitting the job for processing
StepNo int 4 Numeric position of the Batch Process Step within its parent Batch Process
StepName varchar 256 Name given to Batch Process Step
Priority int 4 Numeric value (1-5) given to sort processing priority
Status int 4 Job status as indicated numerically (1=Ready, 2=Working, 3=Completed, 4=Paused, 5=Error, 6=Canceled)
SubmittedBy varchar 256 Name of user that submitted the job
BatchId uniqueidentifier 16 GUID of the Batch in process
BatchName varchar 256 Name of Batch in process
ThreadPoolId int 4 Numeric id of the associated Processing Queue for the Batch Process Step
Completed datetime 8 Date and time the job was completed
Activity nvarchar -1 Allows processing jobs to be submitted from the Search Page.

dbo.ProcessingTask

This table contains "Processing Tasks" for "Processing Jobs" submitted for Activity processing for a production Batch.

The ProcessingTasks table is related to the ProcessingJobs table. A single Processing Job will consist of one or more Processing Tasks, depending on how many objects are in the Activity's scope (the Batch itself, its Batch Folders or Batch Pages).

For example, a Batch advances to the Recognize step in its Batch Process. There are 20 Batch Pages in scope to be processed.

  • A single row will be created in the ProcessingJob table for the job.
  • 20 corresponding rows will be created in the ProcessingTasks table, one for each task in scope (i.e one for each Batch Page processed).
Column Name Data Type Size Description
Id int 4 Unique row identifier
JobId int 4 Identifier tying the task to its corresponding Processing Job in the ProcessingJob table
NodeId uniqueidentifier 16 GUID of the node being processed (Batch, Batch Folder or Batch Page)
Status int 4 Task status as indicated numerically (1=Ready, 2=Working, 3=Completed, 4=Paused, 5=Error, 6=Canceled)
UserName varchar 256 Windows user that submitted the task
MachineName varchar 256 Hostname of machine running the task
Submitted datetime 8 Date and time when the task was submitted for processing
Started datetime 8 Date and time when the task was started
Completed datetime 8 Date and time when the task completed

dbo.ImportJob

The entries in this table are related to creating Batches from imported files. Batches created from imported are created by "Import Jobs." Import Jobs are created by an Import Watcher service in one of two ways:

  1. Automated by a schedule or polling cycle defined in the Import Watcher's configuration
  2. Or, manually created by a user from the Imports Pages.

In either case, one row is created in the ImportJob table for each Import Job to be processed. The row contains important information about the Import Job, including the Import Provider settings.

Column Name Data Type Size Description
Id uniqueidentifier 4 Unique row identifier
Description varchar 256 Description of the Import Job. Entered by the end user when configuring the Import Job from the Import Watcher or Imports Page.
Provider varchar (-1) The Import Provider settings used by the Import Job. Stored as a JSON object. Entered by the end user when configuring the Import Job from the Import Watcher or Imports Page.
Priority int 4 Numeric value (1-5) given to sort the Import Job's priority.
Status int 4 Import Job status as indicated numerically (1=Ready, 2=Working, 3=Completed, 4=Paused, 5=Error, 6=Canceled)
SubmittedBy varchar 256 Name of the Windows user account that submitted the Import Job
Submitted datetime 8 Date and time when the Import Job was submitted
Started datetime 8 Date and time when the Import Job started processing
Completed datetime 8 Date and time when the Import Job completed
Files int 4 Number of files imported by the Import Job
Bytes bigint 8 Total size of files in the Batch created by the Import Job

dbo.ServiceInstance

This table contains a list of installed Grooper Services.

Note: If an installed service does not have a reference in this table, it will not show up in Grooper Command Console or on the Machines folder node in a Grooper Repository.

Column Name Data Type Size Description
Id int 4 unique row identifier
RepositoryId uniqueidentifier 16 Unique ID of the Grooper Repository for which the service is installed
MachineName varchar 64 Hostname of the PC running the Grooper Service
ServiceName varchar 256 Name of Grooper Service instance

By default, this is "Grooper.ServiceTypeName.##" for each service instance. Ex: The second Activity Processing service installed for a Grooper Repository would be named "Grooper.ActivityProcessing.02"

TypeName varchar 256 Service type name of the Grooper Service
PropertiesJson varchar 2048 Property configuration details for the Grooper Service. Stored as a JSON object.
UserName varchar 64 Windows user account running the Grooper Service
Password varchar 64 Password hash for the Windows user account running the Grooper Service
InstanceNo int 4 Instance number of the Grooper Service
ThreadPriority int 4 Numeric value given to the service thread to determine processor priority

dbo.BatchState

This table contains information about production Batches and the processing state they are currently in. This table was created to improve the web client's efficiency when loading large numbers of Batches to various UI elements (such as the Batches Page).

Column Name Data Type Size Description
Id int 4 Identifier generated by SQL on row creation
NodeId uniqueidentifier 16 The Batch's Id (GUID) in the TreeNode table.
ProcessId uniqueidentifier 16 The Id (GUID) in the TreeNode table for the Batch Process assigned to the Batch.
Priority int 4 Numeric value (1-5) given to sort the Batch's priority
Status int 4 Batch status as indicated numerically (1=Ready, 2=Working, 3=Completed, 4=Paused, 5=Error, 6=Canceled)
Name varchar 256 The Batch's name
Process varchar 256 The name of the Batch Process assigned to the Batch.
Step varchar 256 The display name of the current step in the Batch Process being processing.
Activity varchar 256 The name of the Grooper Activity assigned to the step currently being processed.
CreatedBy varchar 256 Name of the Windows user account executing the step.
Created datetime 8 Date and time the Batch was created.

dbo.SessionStats

This table contains statistical information from processing Batches in Grooper. Each row corresponds to a processing job submitted for a step in a Batch's Batch Process. It is used (along with the CustomStats table) to return processing stats when selecting a Batch's "Statistics" tab in the Batches Page or when returning query results from the Stats Page.

Column Name Data Type Size Description
Id int 4 Identifier generated by SQL on row creation
BatchId uniqueidentifier 16 The Id (GUID) in the TreeNode table for the Batch Process assigned to the Batch.
BatchName varchar 256 Name of Batch
BatchProcessId uniqueidentifier 16 The Id (GUID) in the TreeNode table for the Batch Process assigned to the Batch.
BatchProcessName varchar 256 Name of the Batch Process
StepName varchar 256 Name of the step in the Batch Process
ProcessingScope int 4 The level of the Batch that a step is processing.
ModuleName varchar 256 Name of Grooper module. This refers to an Activity's object name in code.
  • This differs slightly from the module's "display name."
  • Ex: The Classify activity's module name is "Grooper.Activities.ClassifyFolders"
ModuleDisplayName varchar 256 The Grooper module's display name. This is the Activity's common name.
MachineName varchar 256 Hostname of the PC running the step.
UserName varchar 256 Windows user under which the step is running
StartTime datetime 8 Date and time the step was initiated
EndTime datetime 8 Date and time the step ended or was terminated
TasksProcessed int 4 Number of tasks processed for the step

dbo.CustomStats

This table contains a name / value pair of processing activity statistics tied to a session id. It is used with the SessionStats table to return processing stats when selecting a Batch's "Statistics" tab in the Batches Page or when returning query results from the Stats Page.

The "name" is the statistic's name (Ex: "Characters - OCR" or "Processing Time"). The "value" will be a whole integer if the statistic indicates a quantity of actions (Ex: number of OCR characters recognized by the Recognize activity), and a floating point decimal if the value represents a cumulative time span (Ex: the time it took to process the activity). The "session id" is an integer id that corresponds to a row in the SessionStats table. This ties the activity statistics to the Batch and step generating the statistics.

Column Name Data Type Size Description
SessionId int 4 Corresponds to a row in the SessionStats table tying the stat to an executed step in a Batch Process.
Name Varchar 64 The statistic's name
  • Ex: "Characters - OCR"
  • Ex: "Processing Time"
Value Float 8 The statistic's value

dbo.NodeReference

This table contains a list of all Grooper nodes that are referenced by another Grooper node. For example: a Data Type being referenced by a Data Field to return document data.

  • This table is used to protect referenced nodes from being deleted. In Design, Grooper will throw an error if a user attempts to delete a node that is actively being referenced from being deleted.
  • This table is also used in determining if nodes can be moved between one Project and another (or if a node they are referencing must be moved as well).
Column Name Data Type Size Description
NodeId uniqueidentifier 16 The Id (GUID) of the node in the TreeNode table that is being referenced.

Ex: A Data Field references a Data Type. This is the Data Type

ReferencingNodeId uniqueidentifier 16 The Id (GUID) of the node in the TreeNode table that is making the reference.

Ex: A Data Field references a Data Type. This is the Data Field

dbo.Lock

This table contains one row per locked node. Nodes are commonly locked when an Activity Processing service is actively processing it. This prevents overlapping access to various resources.

  • Locks typically resolve on their own. Grooper will automatically lock and unlock nodes as needed. In circumstances where Grooper does not unlock a node, users may manually remove the lock from the node's Advanced tab in the Design Page. Only in extreme circumstances would a user need to delete the lock by deleting the row from this database table.
Column Name Data Type Size Description
Id int 4 Identifier generated by SQL on row creation
NodeRowId int 4 The "RowId" of the node in the TreeNode table that is locked.
UserName varchar 64 Windows user account that applied the lock
MachineName varchar 64 Hostname of the machine where the lock occurred
ModuleName varchar 64 Grooper module that applied the lock
ProcedureName varchar 64 Grooper procedure that applied the lock
CreateDate smalldatetime 4 Date and time the lock occurred

dbo.DocIndex

This table contains one row for each Batch Folder and their Content Type (e.g. Document Type). Along with the IndexState table, the DocIndex table was added to improve the efficiency of the Indexing Service.

Note: Batch Folders with no Content Type assigned will not appear in this table.

Column Name Data Type Size Description
Id int 4 Identifier generated by SQL on row creation
NodeId uniqueidentifier 16 The Batch Folder's Id (GUID) in the TreeNode table.
TypeId uniqueidentifier 16 The Id (GUID) in the TreeNode table for the Content Type assigned to the Batch Folder.

dbo.IndexState

This table contains one row for each document indexed according to an Indexing Behavior. Along with the DocIndex table, the IndexState table was added to improve the efficiency of the Indexing Service

Column Name Data Type Size Description
Id int 4 Identifier generated by SQL on row creation
FolderId uniqueidentifier 16 The Batch Folder's Id (GUID) in the TreeNode table.
TypeId uniqueidentifier 16 The Id (GUID) in the TreeNode table for the Content Type assigned to the Batch Folder.
IndexName varchar 128 The index's name. Defined in the Indexing Behavior.
LastUpdated datetime2 7 Date and time the document was updated in the index.
PropHash varchar 64 Hash of root properties at last index. Used to determine if the index needs to be updated when the document is modified.
TextHash varchar 64 Hash of text content at last index. Used to determine if the index needs to be updated when the document's text data (OCR or native text) is modified.
DataHash varchar 64 Hash of index data at last index. Used to determine if the index needs to be updated when the document's index data (Data Model) is modified.

dbo.Log

  • This table contains the Grooper log, which is represented in the Event Viewer of Grooper.
Column Name Data Type Size Description
Id int 4 unique row identifier
TimeStamp datetime 8 date and time the event was logged
Type int 4 type of event logged
ProcName varchar(MAX) -1 name of procedure associated with the event
Message varchar(MAX) -1 message displayed in and defining, in general, the event
UserAccount varchar 64 windows user under which the event triggered
MachineName varchar 64 hostname of the machine under which the event triggered
NodeId uniqueidentifier 16 GUID of parent node associated with ‘NodeName’
NodeName varchar 256 Item Name, or name of node associated with the triggering of the event
NodeType varchar 64 type of Grooper Node defining the ‘NodeName’

dbo.Setting

This table contains only the Grooper version number, including build number.

Column Name Data Type Size Description
Name varchar 64 The name is always "DatabaseVersion"
Value varchar 1024 Current installed Grooper version

Benefits of database architecture

Using a SQL database as the core of Grooper is part of what makes it so efficient. Every property of every node in Grooper is a simple entry in a table. As a result, these objects and their properties can be loaded into memory and accessed nearly instantly. As file I/O is one of the slowest aspects of modern computing, this would not be the case otherwise (For example: If Grooper Repositories were defined by something like a project file). This also allows objects to be managed discretely on an individual basis, which allows multiple users to work in one environment and prevent overlapping work by locking objects.

The File Store

The File Store in Grooper is a file share in a Windows environment. It houses the files associated with nodes in Grooper that have information that would otherwise be inefficient to store in a cell in a database table.

The Grooper File Store exists at a user-specified location. This should always be a network path (UNC path). If a File Store is given a local path, computers connecting to that repository remotely will not be able to access it. To set up a Grooper Repository so that other computers can connect to it, make sure you reference the File Store using a UNC path!

The File Store contains three levels of directories. A File Store entry will exist on disk as a file in the lowest level with a .grp extension (e.g. 00 > 00 > 00 > 00000000-0000-0000-0000-000000000000.grp). Each of the lowest-level folders in the File Store will have a maximum of 256 files, at which point a new folder at that level will be created. If the lowest level contains 256 folders, a new folder will be created at the level above; this gives the Grooper File Store a limit of 256 ^ 4 = 4,294,967,296 files stored on disk.

While the File Store entries are all given .grp extensions, the contents of the file are unaltered from their "actual" form. If you navigate, for example, to the GRP file associated with an pdf imported using full import, you can open it and view it with a PDF viewer. The files in the file store are intentionally obfuscated to prevent users from interacting with them outside of Grooper, as they are essentially "Grooper-internal" objects.

Although the majority of files in the File Store relate to Batch objects (a page's image or imported files), some files are the result of other "in-Grooper" processes, such as layout data, OCR character data, extracted index data, and more.


Retrieved from "https://wiki.grooper.com/index.php?title=Grooper_Infrastructure_(Concept)&oldid=26544"