//The Processing set manager is a one agent per resource pool agent
//Which makes determining the amount of agents per pool desired easy
public override AgentsDesired GetAgentsDesired()
{
int agentCount = 0;
string SQL = @"
SELECT Count(P.[SetQueueID])
FROM [ProcessingSetQueue] P
INNER JOIN [Case] C
ON P.[WorkspaceArtifactID] = C.[ArtifactID]
WHERE C.[ResourceGroupArtifactID] = @ResourceGroupArtifactID";
SqlParameter resourcePoolArtifactIdParam = new SqlParameter("@ResourceGroupArtifactID", System.Data.SqlDbType.Int)
{
Value = AgentAgentResourcePool
};
int jobCount = _eddsDbContext.ExecuteSqlStatementAsScalar <int>(SQL, new SqlParameter[] { resourcePoolArtifactIdParam });
//If nothing is returned, there are no jobs in the queue
if (jobCount > 0)
{
agentCount = 1;
}
return(new AgentsDesired()
{
Guid = Guid,
RespectsResourcePool = RespectsResourcePool,
Count = agentCount
});
}
public override AgentsDesired GetAgentsDesired()
{
int agentCount = 0;
int jobCount = 0;
string SQL = @"
SELECT Count(CACJ.[ClusterID])
FROM [ContentAnalystClusterJob] CACJ WITH(NOLOCK)
INNER JOIN [Case] C WITH(NOLOCK)
ON CACJ.[CaseArtifactID] = C.[ArtifactID]
WHERE C.[ResourceGroupArtifactID] = @ResourceGroupArtifactID";
SqlParameter poolIdParam = new SqlParameter("@ResourceGroupArtifactID", System.Data.SqlDbType.Int)
{
Value = AgentAgentResourcePool
};
jobCount = _eddsDbContext.ExecuteSqlStatementAsScalar <int>(SQL, new SqlParameter[] { poolIdParam });
if (jobCount > 0)
{
agentCount = 1;
}
return(new AgentsDesired()
{
Guid = Guid,
RespectsResourcePool = RespectsResourcePool,
Count = agentCount
});
}
public override AgentsDesired GetAgentsDesired()
{
int agentCount = 0;
int jobCount = 0;
/* Categorization Set Managers are max two per resource pool */
string SQL = @"
SELECT IIF(Count(ACSQ.[SetQueueId]) > 2, 2, Count(ACSQ.[SetQueueId]))
FROM [AnalyticsCategorizationSetQueue] ACSQ WITH(NOLOCK)
INNER JOIN [Case] C WITH(NOLOCK)
ON ACSQ.[WorkspaceArtifactId] = C.[ArtifactID]
WHERE C.[ResourceGroupArtifactID] = @ResourceGroupArtifactID";
SqlParameter poolIdParam = new SqlParameter("@ResourceGroupArtifactID", System.Data.SqlDbType.Int)
{
Value = AgentAgentResourcePool
};
jobCount = _eddsDbContext.ExecuteSqlStatementAsScalar <int>(SQL, new SqlParameter[] { poolIdParam });
if (jobCount > 0)
{
agentCount = jobCount;
}
return(new AgentsDesired
{
Guid = Guid,
RespectsResourcePool = RespectsResourcePool,
Count = agentCount
});
}
public static bool FindFieldOnLayout(IDBContext context, int fieldArtifactID, int layoutArtifactID, IAPILog logger)
{
string sql =
@"SELECT COUNT(1)
FROM LayoutField
WHERE FieldArtifactID = @fieldArtifactID
AND LayoutArtifactID = @layoutArtifactID";
SqlParameter fieldArtifact = new SqlParameter("@fieldArtifactID", SqlDbType.Int);
SqlParameter layoutArtifact = new SqlParameter("@layoutArtifactID", SqlDbType.Int);
fieldArtifact.Value = fieldArtifactID;
layoutArtifact.Value = layoutArtifactID;
logger.LogDebug("Querying for field {fieldArtifactID} on layout {layoutArtifactID}", fieldArtifactID, layoutArtifactID);
var count = context.ExecuteSqlStatementAsScalar <int>(sql, new SqlParameter[] { fieldArtifact, layoutArtifact });
logger.LogDebug("Count returned from LayoutField: {count}", count);
if (count > 0)
{
return(true);
}
return(false);
}
public String RetrieveConfigurationValue(IDBContext eddsDbContext, String sectionName, String name)
{
const String sql = @"SELECT Value
FROM EDDSDBO.Configuration
WHERE Section = @sectionName
AND Name = @name" ;
var sqlParams = new SqlParameter[2];
sqlParams[0] = new SqlParameter("@sectionName", SqlDbType.VarChar)
{
Value = sectionName
};
sqlParams[1] = new SqlParameter("@name", SqlDbType.VarChar)
{
Value = name
};
var result = eddsDbContext.ExecuteSqlStatementAsScalar(sql, sqlParams);
if ((result != null) && (result != DBNull.Value))
{
return(result.ToString());
}
return(String.Empty);
}
public int GetAgentIdToDelete(int agentTypeId, int serverArtifactId)
{
/* Ordering by LastUpdate descending under the assumption
* that agents that are busy will check in less frequently */
string SQL = @"
SELECT TOP 1 AG.[ArtifactID]
FROM [Agent] AG WITH(NOLOCK)
INNER JOIN [Artifact] A WITH(NOLOCK)
ON AG.[ArtifactID] = A.[ArtifactID]
WHERE AG.[AgentTypeArtifactID] = @AgentTypeArtifactID
AND AG.[ServerArtifactID] = @ServerArtifactID
AND A.[DeleteFlag] = 0
ORDER BY AG.[LastUpdate] DESC";
SqlParameter agentTypeIdParam = new SqlParameter("@AgentTypeArtifactID", System.Data.SqlDbType.Char)
{
Value = agentTypeId
};
SqlParameter serverArtifactIdParam = new SqlParameter("@ServerArtifactID", System.Data.SqlDbType.Char)
{
Value = serverArtifactId
};
return(_eddsDbContext.ExecuteSqlStatementAsScalar <int?>(SQL, new SqlParameter[] { agentTypeIdParam, serverArtifactIdParam }).GetValueOrDefault());
}
public override AgentsDesired GetAgentsDesired()
{
int agentCount = 0;
string SQL = @"
SELECT Count(O.[WorkspaceArtifactID])
FROM [OCRSetQueue] O WITH(NOLOCK)
INNER JOIN [Case] C WITH(NOLOCK)
ON O.[WorkspaceArtifactID] = C.[ArtifactID]
WHERE C.[ResourceGroupArtifactID] = @ResourceGroupArtifactID";
SqlParameter resourcePoolArtifactIdParam = new SqlParameter("@ResourceGroupArtifactID", System.Data.SqlDbType.Int)
{
Value = AgentAgentResourcePool
};
int jobCount = _eddsDbContext.ExecuteSqlStatementAsScalar <int>(SQL, new SqlParameter[] { resourcePoolArtifactIdParam });
if (jobCount > 0)
{
agentCount = 1;
}
return(new AgentsDesired()
{
Guid = Guid,
RespectsResourcePool = RespectsResourcePool,
Count = agentCount
});
}
public override AgentsDesired GetAgentsDesired()
{
int agentCount = 0;
string SQL = @"
SELECT Count(D.[setartifactid])
FROM [dtsearchindexqueue] D WITH(nolock)
INNER JOIN [case] C WITH(nolock)
ON D.[workspaceartifactid] = C.[artifactid]
WHERE C.[resourcegroupartifactid] = @ResourceGroupArtifactID";
SqlParameter poolIdParam = new SqlParameter("@ResourceGroupArtifactID", System.Data.SqlDbType.Int)
{
Value = AgentAgentResourcePool
};
int jobCount = _eddsDbContext.ExecuteSqlStatementAsScalar <int>(SQL, new SqlParameter[] { poolIdParam });
if (jobCount > 0)
{
agentCount = 1;
}
return(new AgentsDesired
{
Guid = Guid,
RespectsResourcePool = RespectsResourcePool,
Count = agentCount
});
}
public override AgentsDesired GetAgentsDesired()
{
int agentCount = 0;
string SQL = @"
SELECT D.[WorkspaceArtifactID],
D.[SetArtifactID]
FROM [dtSearchIndexQueue] D WITH(NOLOCK)
INNER JOIN [Case] C WITH(NOLOCK)
ON D.[WorkspaceArtifactID] = C.[ArtifactID]
WHERE C.[ResourceGroupArtifactID] = @ResourceGroupArtifactID";
SqlParameter poolIdParam = new SqlParameter("@ResourceGroupArtifactID", System.Data.SqlDbType.Int)
{
Value = AgentAgentResourcePool
};
DataTable queueResults = _eddsDbContext.ExecuteSqlStatementAsDataTable(SQL, new SqlParameter[] { poolIdParam });
if (queueResults.Rows.Count > 0)
{
foreach (DataRow row in queueResults.Rows)
{
int workspaceId;
int setId;
if (!int.TryParse(row["WorkspaceArtifactID"].ToString(), out workspaceId))
{
throw new Exception("Unable to cast WorkspaceArtifactID returned from database to Int32");
}
if (!int.TryParse(row["SetArtifactID"].ToString(), out setId))
{
throw new Exception("Unable to cast SetArtifactId returned from database to Int32");
}
IDBContext workspaceDbContext = _agentHelper.GetDBContext(workspaceId);
SQL = @"
SELECT Count(ID)
FROM [dtSearchSubIndex] WITH(NOLOCK)
WHERE [dtSearchIndexID] = @DtSearchIndexID";
SqlParameter jobIdParam = new SqlParameter("@DtSearchIndexID", System.Data.SqlDbType.Char)
{
Value = setId
};
agentCount += workspaceDbContext.ExecuteSqlStatementAsScalar <int>(SQL, new SqlParameter[] { jobIdParam });
}
}
return(new AgentsDesired()
{
Guid = Guid,
RespectsResourcePool = RespectsResourcePool,
Count = agentCount
});
}
private string GetDisclaimerObjTableName(IDBContext workspaceDb, Guid discObjGuid)
{
var parameter = new SqlParameter {
ParameterName = "@DisclaimerObjGuid", SqlDbType = SqlDbType.UniqueIdentifier, Value = discObjGuid
};
return((string)workspaceDb.ExecuteSqlStatementAsScalar(SQL.GetDisclaimerObjTableName, parameter));
}
public static int GetArtifactId(this IDBContext dbContext, Guid artifactGuid)
{
var artifactId = dbContext.ExecuteSqlStatementAsScalar <int>(
"select ArtifactId from eddsdbo.ArtifactGuid with (nolock) where ArtifactGuid = @guid",
new SqlParameter("@guid", artifactGuid));
return(artifactId);
}
public static String GetFolderName(Int32 folderArtifactID, IDBContext workspaceDbContext)
{
string sql = String.Format(@"select Name from folder where ArtifactID = {0}", folderArtifactID);
string folderName = workspaceDbContext.ExecuteSqlStatementAsScalar(sql).ToString();
return(folderName);
}
public override kCura.EventHandler.Response Execute()
{
// Update Security Protocol
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
// Get logger
Relativity.API.IAPILog _logger = this.Helper.GetLoggerFactory().GetLogger().ForContext <PreInstallEventHandler>();
// Init general response
kCura.EventHandler.Response response = new kCura.EventHandler.Response()
{
Success = true,
Message = ""
};
// Get current Workspace ID
int workspaceId = this.Helper.GetActiveCaseID();
_logger.LogDebug("Audit Log Elastic Search, current Workspace ID: {workspaceId}", workspaceId.ToString());
// Get database context of the instance
IDBContext instanceContext = Helper.GetDBContext(-1);
// Current Relativity version
string currentRelativityVersion = "";
try
{
// Get Relativity version
currentRelativityVersion = instanceContext.ExecuteSqlStatementAsScalar("SELECT [Value] FROM [eddsdbo].[Relativity] WHERE [Key] = 'Version'").ToString();
_logger.LogDebug("Audit Log Elastic Search, current Relativity version: {version}", currentRelativityVersion);
_logger.LogDebug("Audit Log Elastic Search, minimum required Relativity version: {version}", this.MinRelativityVersion);
}
catch (Exception e)
{
_logger.LogError(e, "Audit Log Elastic Search, Pre Install EventHandler Relativity version error");
response.Success = false;
response.Message = "Pre Install EventHandler Relativity version error";
return(response);
}
// Check Relativity version requirements
if (new Version(currentRelativityVersion) < new Version(this.MinRelativityVersion))
{
_logger.LogError("Audit Log Elastic Search, old Relativity instance (version: {version})", currentRelativityVersion);
response.Success = false;
response.Message = string.Format("This application requires Relativity {0} or later", this.MinRelativityVersion);
return(response);
}
// Log end of Pre Install EventHandler
_logger.LogDebug("Audit Log Elastic Search, Pre Install EventHandler successfully finished");
return(response);
}
public Int32 GetResourceServerByAgentId(IDBContext eddsDbContext, Int32 agentArtifactId)
{
var sql = String.Format(@"
SELECT ISNULL([ServerArtifactID], 0)
FROM [EDDSDBO].[Agent] WITH(NOLOCK)
WHERE ArtifactID = {0}" , agentArtifactId);
return(eddsDbContext.ExecuteSqlStatementAsScalar <Int32>(sql));
}
//Get the ArtifactId of an Application that is associated with a particular object
public static Int32 FindApplicationArtifactIdOfRelatedObject(IDBContext workspaceContext, Guid associatedApplicationObject)
{
var parameters = new List <SqlParameter> {
new SqlParameter {
ParameterName = "@ObjectGuid", SqlDbType = SqlDbType.UniqueIdentifier, Value = associatedApplicationObject
}
};
return(workspaceContext.ExecuteSqlStatementAsScalar <Int32>(SQL.GetApplicationArtifactIdOfRelatedObject, parameters));
}
public string GetDocumentIdentifierFieldName(IDBContext workspaceDbContext)
{
const string sql = @"
SELECT [TextIdentifier] FROM [EDDSDBO].[ExtendedField] WITH(NOLOCK)
WHERE IsIdentifier = 1 AND FieldArtifactTypeID = 10" ;
var columnName = workspaceDbContext.ExecuteSqlStatementAsScalar(sql).ToString();
return(columnName);
}
public static string GetArtifactName(this IDBContext dbContext, Guid artifactGuid)
{
var artifactName = dbContext.ExecuteSqlStatementAsScalar <string>(
@"select a.TextIdentifier from eddsdbo.ArtifactGuid ag with (nolock)
join eddsdbo.Artifact a with(nolock) on a.ArtifactID = ag.ArtifactID
where ArtifactGuid = @guid",
new SqlParameter("@guid", artifactGuid));
return(artifactName);
}
private Boolean ApplicationInstalledInAnotherWorkspace(IDBContext eddsDbContext)
{
var currentWorkspaceId = Helper.GetActiveCaseID();
var applicationGuid = Utility.Constants.Guids.Applications.DisclaimerAcceptanceLog;
var workspaceParameter = new SqlParameter {
ParameterName = "@ApplicationGuid", SqlDbType = SqlDbType.UniqueIdentifier, Value = applicationGuid
};
var firstWorkspaceWhereAppIsInstalled = (int?)eddsDbContext.ExecuteSqlStatementAsScalar(SQL.WorkspacesWhereAppIsInstalled, workspaceParameter);
return(firstWorkspaceWhereAppIsInstalled != null && firstWorkspaceWhereAppIsInstalled != currentWorkspaceId);
}
public int GetFieldArtifactId(string fieldName, IDBContext workspaceDbContext)
{
const string sqlQuery = @"SELECT [ArtifactID] FROM [EDDSDBO].[Field] WHERE [DisplayName] LIKE '%@fieldName%'";
var sqlParams = new List <SqlParameter> {
new SqlParameter("@fieldName", SqlDbType.NVarChar)
{
Value = fieldName
}
};
return(workspaceDbContext.ExecuteSqlStatementAsScalar <int>(sqlQuery, sqlParams));
}
public dynamic ExecuteSql(IDBContext dbContext, Action action, DapiJson dapiJson)
{
List <SqlParameter> sqlParameters = new List <SqlParameter>();
foreach (Parameter param in action.Parameters)
{
SqlParameter sqlParam = new SqlParameter(param.Name, Mapper.Map <SqlDbType>(param.DataType));
if (param.Length > 0)
{
sqlParam.Size = param.Length ?? sqlParam.Size;
}
var dParam = dapiJson.Parameters.Find(d => d.name.Equals(param.Name, StringComparison.OrdinalIgnoreCase));
sqlParam.Value = dParam?.value;
sqlParameters.Add(sqlParam);
}
dynamic result;
try
{
switch (action.Outputs.OutputMode)
{
case OutputMode.NoReturnValue:
result = dbContext.ExecuteNonQuerySQLStatement(action.SQL, sqlParameters);
result = "";
break;
case OutputMode.Scalar:
result = dbContext.ExecuteSqlStatementAsScalar <dynamic>(action.SQL, sqlParameters);
break;
case OutputMode.Rowset:
result = dbContext.ExecuteSqlStatementAsDataTable(action.SQL, sqlParameters);
break;
default:
result = "";
break;
}
DidActionExecute = true;
}
catch (Exception ex)
{
DidActionExecute = false;
result = ex.Message;
throw;
}
return(result);
}
public int GetFieldCount(IDBContext workspaceDbContext, int fieldArtifactId)
{
const string sqlQuery = @"select count(*) from [EDDSDBO].[ExtendedField] where ArtifactID = @fieldArtifactId";
var sqlParams = new List <SqlParameter> {
new SqlParameter("@fieldArtifactId", SqlDbType.NVarChar)
{
Value = fieldArtifactId
}
};
return(workspaceDbContext.ExecuteSqlStatementAsScalar <int>(sqlQuery, sqlParams));
}
public Int32 RetrieveExtractorProfileCountInQueue(IDBContext eddsDbContext, String extractorProfileArtifactIds, String queueTableName)
{
var sql = String.Format(@"
SELECT COUNT(ID) [JobCount]
FROM [EDDSDBO].[{0}] WITH(NOLOCK)
WHERE
ExtractorProfileArtifactID IN ({1})" , queueTableName, extractorProfileArtifactIds);
var profileCount = Convert.ToInt32(eddsDbContext.ExecuteSqlStatementAsScalar(sql));
return(profileCount);
}
public string GetDocumentIdentifierFieldColumnName(IDBContext workspaceDbContext)
{
const string sql = @"
SELECT AVF.ColumnName FROM [EDDSDBO].[ExtendedField] EF WITH(NOLOCK)
JOIN [EDDSDBO].[ArtifactViewField] AVF WITH(NOLOCK)
ON EF.TextIdentifier = AVF.HeaderName
WHERE EF.IsIdentifier = 1 AND EF.FieldArtifactTypeID = 10" ;
var columnName = workspaceDbContext.ExecuteSqlStatementAsScalar(sql).ToString();
return(columnName);
}
public Int32 RetrieveProfileCountFromSetTableByTempTable(IDBContext dbContext, String tempTableName)
{
var sql = String.Format(@"
SELECT COUNT(ES.ArtifactID) [SetCount]
FROM
[EDDSDBO].[{0}] ES WITH(NOLOCK)
INNER JOIN [EDDSResource].[EDDSDBO].[{1}] TT WITH(NOLOCK) ON ES.ExtractorProfile = TT.ArtifactID" , Constant.Tables.ExtractorSet, tempTableName);
var setCount = Convert.ToInt32(dbContext.ExecuteSqlStatementAsScalar(sql));
return(setCount);
}
public String RetrieveExtractorSetStatusBySetArtifactId(IDBContext dbContext, Int32 activeArtifactId)
{
var sql = String.Format(@"
SELECT [Status]
FROM [EDDSDBO].[{0}] WITH(NOLOCK)
WHERE ArtifactID = {1}
" , Constant.Tables.ExtractorSet, activeArtifactId);
var setStatus = dbContext.ExecuteSqlStatementAsScalar(sql).ToString();
return(setStatus);
}
private static int GetTemplateCase(IDBContext context)
{
string getTemplate = @"
SELECT TOP 1 [CaseTemplateID]
FROM [CaseEventHandlerHistory]
WHERE [CaseTemplateID] IS NOT NULL
";
int result = context.ExecuteSqlStatementAsScalar <int>(getTemplate);
return(result);
}
public Int32 RetrieveRegExCountFromTargetTextTableByTempTable(IDBContext dbContext, String tempTableName)
{
var sql = String.Format(@"
SELECT COUNT(TargetT.ArtifactID) [SetCount]
FROM
[EDDSDBO].[{0}] TargetT WITH(NOLOCK)
INNER JOIN [EDDSResource].[EDDSDBO].[{1}] TT WITH(NOLOCK)
ON TargetT.RegularExpressionStartMarker = TT.ArtifactID OR TargetT.RegularExpressionStopMarker = TT.ArtifactID" , Constant.Tables.ExtractorTargetText, tempTableName);
var setCount = Convert.ToInt32(dbContext.ExecuteSqlStatementAsScalar(sql));
return(setCount);
}
public Int32 RetrieveExtractorSetStatusCountByTempTable(IDBContext dbContext, String tempTableName)
{
var sql = String.Format(@"
SELECT COUNT(ES.ArtifactID) [SetCount]
FROM
[EDDSDBO].[{0}] ES WITH(NOLOCK)
INNER JOIN [EDDSResource].[EDDSDBO].[{1}] TT WITH(NOLOCK) ON ES.ArtifactID = TT.ArtifactID
WHERE ES.[Status] IS NOT NULL" , Constant.Tables.ExtractorSet, tempTableName);
var setStatus = Convert.ToInt32(dbContext.ExecuteSqlStatementAsScalar(sql));
return(setStatus);
}
private string GetFileLocation(int workspaceArtifactId, int documentArtifactId)
{
//Set DB Context to current Workspace
IDBContext workspaceContext = Helper.GetDBContext(workspaceArtifactId);
string sql = @"SELECT [Location] FROM [EDDSDBO].[File] WITH(NOLOCK) WHERE [DocumentArtifactId] = @documentArtifactID AND [Type] = 0";
SqlParameter documentArtifactIdParam = new SqlParameter("@documentArtifactID", SqlDbType.Int)
{
Value = documentArtifactId
};
string fileLocation = workspaceContext.ExecuteSqlStatementAsScalar <string>(sql, documentArtifactIdParam);
return(fileLocation);
}
//Get agent artifact type id from artifact type table
public int GetAgentArtifactType()
{
int agentArtifactType = _eddsDbContext.ExecuteSqlStatementAsScalar <int>(@"
SELECT TOP 1 [ArtifactTypeID]
FROM [ArtifactType] WITH(NOLOCK)
WHERE ArtifactType = 'Agent'");
if (agentArtifactType == 0)
{
throw new Exception("Unable to retrieve agent artifact type ID from database");
}
else
{
return(agentArtifactType);
}
}
