public static void DropComplexIndex(DbConnection cnn, ContentConstraintDAL constraint)
{
var dbType = GetDbType(cnn);
var indexName = $"constraint_{constraint.Id}_ind";
var asyncIndexName = $"constraint_{constraint.Id}_async_ind";
var pgCheck = dbType == DatabaseType.Postgres ? " IF EXISTS " : "";
var sqlCheckTemplate = "if exists(select name from sysindexes where name = '{0}')";
var sqlCheck = dbType == DatabaseType.SqlServer ? String.Format(sqlCheckTemplate, indexName) : "";
var sqlAsyncCheck = dbType == DatabaseType.SqlServer ? String.Format(sqlCheckTemplate, asyncIndexName) : "";
if (dbType == DatabaseType.SqlServer)
{
indexName = $"content_{constraint.ContentId}.{indexName}";
asyncIndexName = $"content_{constraint.ContentId}_async.{asyncIndexName}";
}
var indexTemplate = $"{{0}} DROP INDEX {pgCheck} {{1}};";
var sql = String.Format(indexTemplate, sqlCheck, indexName);
ExecuteSql(cnn, sql);
var asyncSql = String.Format(indexTemplate, sqlAsyncCheck, asyncIndexName);;
ExecuteSql(cnn, asyncSql);
}
public static void CreateComplexIndex(QPModelDataContext ctx, DbConnection cnn, ContentConstraintDAL constraint)
{
var dbType = GetDbType(cnn);
var ids = constraint.Rules.Select(n => n.FieldId).ToArray();
var indexName = $"constraint_{constraint.Id}_ind";
var asyncIndexName = $"constraint_{constraint.Id}_async_ind";
var tableName = "content_" + constraint.ContentId;
var asyncTableName = tableName + "_async";
var fields = ctx.FieldSet.Where(n => ids.Contains(n.Id)).OrderBy(n => n.Order)
.Select(n => Escape(dbType, n.Name)).ToArray();
var indexTemplate = $"CREATE INDEX {{0}} on {{1}} ({String.Join(",", fields)});";
var sql = String.Format(indexTemplate, indexName, tableName);
ExecuteSql(cnn, sql);
var asyncSql = String.Format(indexTemplate, asyncIndexName, asyncTableName);
ExecuteSql(cnn, asyncSql);
}