private static string _CreateSQLFromFindOptions(IDatabaseService db,
string objectFullName,
ObjectFindOptions options,
ObjectIndex[] indexes,
string templateSql,
IList<IDataParameter> parameters)
{
var logic = options.Logic;
var oper = options.Operator;
var limit = options.Limit;
bool useOr = ObjectFindLogic.Or == logic;
bool useLike = ObjectFindOperator.Like == oper;
bool isNull = ObjectFindOperator.IsNull == oper;
var limitSql = (0 == limit) ? SQLStatements.NoLimit : string.Format(SQLStatements.Limit, limit);
var tableName = _CreateTableName(db, objectFullName);
var sqlConstraint = new StringBuilder();
if (null != indexes && 0 < indexes.Length)
{
// initialize the where clause
sqlConstraint.Append("WHERE ");
for (int i = 0; indexes.Length > i; i++)
{
var idx = indexes[i];
// skip over null objects
if (null == idx)
{
continue;
}
if (0 < i)
{
if (useOr)
{
sqlConstraint.Append(" OR ");
}
else
{
sqlConstraint.Append(" AND ");
}
}
var paramName = "p" + i + idx.Name;
var value = idx.GetObjectValue();
sqlConstraint.Append(db.MakeQuotedName(idx.Name));
if (useLike)
{
sqlConstraint.Append(' ');
sqlConstraint.Append(db.MakeLikeParamReference(paramName));
parameters.Add(ObjectIndexProvider.MakeLikeParameter(db, paramName, value));
}
else if (isNull)
{
sqlConstraint.Append(" IS NULL");
}
else
{
sqlConstraint.Append(" = ");
sqlConstraint.Append(db.MakeParamReference(paramName));
parameters.Add(db.MakeParam(paramName, value));
}
}
}
var orderBySql = _CreateOrderBySQL(db, options.Order);
return string.Format(templateSql, tableName, sqlConstraint.ToString(), orderBySql, limitSql);
}
private static string _CreateOrderBySQL(IDatabaseService db, OrderOptions order)
{
var orderBySql = SQLStatements.NoOrder;
if (null != order)
{
string orderColSql = string.Join(",", order.Indexes.Select(i => db.MakeQuotedName(i)).ToArray());
if (order.Descending)
{
orderBySql = string.Format(SQLStatements.OrderDesc, orderColSql);
}
else
{
orderBySql = string.Format(SQLStatements.OrderAsc, orderColSql);
}
}
return orderBySql;
}
private void _StageBulkIndexValues(IDatabaseService db, string objectFullName, ObjectMetadata metadata, IEnumerable<object[]> records)
{
var stagingTableName = _CreateStagingTableName(db, objectFullName);
if (!_TableExists(db, stagingTableName))
{
_ProvisionIndexStaging(db, metadata);
}
var colNames = new List<string>();
colNames.Add(db.MakeQuotedName(IDColumn));
foreach (var idx in metadata.Indexes)
{
colNames.Add(db.MakeQuotedName(idx.Name));
}
db.ExecuteBulkInsert(
records,
"[ZeroG]." + db.MakeQuotedName(stagingTableName),
colNames.ToArray());
}
private void _ProvisionIndexStaging(IDatabaseService db, ObjectMetadata metadata)
{
var tableName = _CreateStagingTableName(db, metadata.ObjectFullName);
string idColName = db.MakeQuotedName(IDColumn);
string colDefs = idColName + " [int] NOT NULL";
string colIndexNames = idColName;
if (null != metadata.Indexes && 0 < metadata.Indexes.Length)
{
colDefs += "," + string.Join(",", metadata.Indexes.Select(i => _CreateColumnDef(db, i)).ToArray());
colIndexNames += "," + string.Join(",", metadata.Indexes.Select(i => db.MakeQuotedName(i.Name)).ToArray());
}
var createTableSQL = string.Format(SQLStatements.CreateStagingTableIfNotExists, tableName, colDefs, _FileGroup);
db.ExecuteNonQuery(createTableSQL);
}
private void _MergeBulkIndexValues(IDatabaseService db, string objectFullName, ObjectMetadata metadata)
{
string tableName = _CreateTableName(db, objectFullName);
string stagingTableName = _CreateStagingTableName(db, objectFullName);
var colNameList = new List<string>();
var idCol = db.MakeQuotedName(IDColumn);
colNameList.Add(idCol);
string setStatement = idCol + " = source." + idCol;
string valuesStatement = "source." + idCol;
foreach (var idx in metadata.Indexes)
{
var idxName = db.MakeQuotedName(idx.Name);
colNameList.Add(idxName);
setStatement += "," + idxName + " = source." + idxName;
valuesStatement += ",source." + idxName;
}
var sql = string.Format(@"MERGE [ZeroG].{0} AS target
USING (SELECT {1} FROM [ZeroG].{2}) AS source ({1})
ON (target.{3} = source.{3})
WHEN MATCHED THEN
UPDATE SET {4}
WHEN NOT MATCHED THEN
INSERT ({1})
VALUES ({5});",
db.MakeQuotedName(tableName), // 0
string.Join(",", colNameList.ToArray()), // 1
db.MakeQuotedName(stagingTableName), // 2
idCol, // 3
setStatement, // 4
valuesStatement // 5
);
db.ExecuteNonQuery(sql);
}
private void _CleanStageBulkIndexValues(IDatabaseService db, string objectFullName)
{
var stagingTableName = _CreateStagingTableName(db, objectFullName);
if (_TableExists(db, stagingTableName))
{
db.ExecuteNonQuery("TRUNCATE TABLE [ZeroG]." + db.MakeQuotedName(stagingTableName));
}
}
