public override DbCommandInfo BuildSelectAllPagedCommand(EntityCommand entityCommand)
{
const string SqlSelectAllPaged = @"
SELECT {0}
FROM {1}
{2}
OFFSET @__skiprows ROWS
FETCH NEXT @__maxrows ROWS ONLY;
";
var table = DbModel.LookupDbObject<DbTableInfo>(entityCommand.TargetEntityInfo, throwNotFound: true);
//Build column list
var cmdName = ModelConfig.NamingPolicy.ConstructDbCommandName(entityCommand, table.TableName, "SelectAllPaged");
var outColumns = table.Columns.GetSelectable();
var strColumns = outColumns.GetSqlNameList();
//Build Order by
string strOrderBy = string.Empty;
if (table.DefaultOrderBy == null)
strOrderBy = "ORDER BY " + table.PrimaryKey.KeyColumns.GetSqlNameList() ;
else
strOrderBy = BuildOrderBy(table.DefaultOrderBy);
var sql = string.Format(SqlSelectAllPaged, strColumns, table.FullName, strOrderBy);
var cmdInfo = CreateDbCommandInfo(entityCommand, cmdName, table, DbExecutionType.Reader, sql);
cmdInfo.EntityMaterializer = CreateEntityMaterializer(table, outColumns);
return cmdInfo;
}
public override DbCommandInfo BuildSelectByKeyArrayCommand(EntityCommand entityCommand)
{
if (DbModel.Config.Options.IsSet(DbOptions.ForceArraysAsLiterals))
return base.BuildSelectByKeyArrayCommand(entityCommand);
const string SqlSelectByFkTemplate = @"
SELECT {0}
FROM {1}
WHERE {2}
{3}";
var table = DbModel.LookupDbObject<DbTableInfo>(entityCommand.TargetEntityInfo, throwNotFound: true);
var dbKey = DbModel.LookupDbObject<DbKeyInfo>(entityCommand.SelectKey);
Util.Check(dbKey.KeyColumns.Count == 1, "Cannot construct SelectByKeyArray command for composite keys. Key: {0}", dbKey);
var keyCols = dbKey.KeyColumns.GetNames(removeUnderscores: true);
var cmdName = ModelConfig.NamingPolicy.ConstructDbCommandName(entityCommand, table.TableName, "SelectByArrayOf", keyCols);
var cmdInfo = CreateDbCommandInfo(entityCommand, cmdName, table, DbExecutionType.Reader, null);
//Build column list
var outColumns = table.Columns.GetSelectable();
var strColumns = outColumns.GetSqlNameList();
//build WHERE clause
var whereExpr = dbKey.KeyColumns[0].Column.ColumnName + " IN (SELECT [Value] FROM " + cmdInfo.Parameters[0].Name + ")";
if (!string.IsNullOrWhiteSpace(entityCommand.Filter))
whereExpr = whereExpr + " AND " + ProcessFilter(entityCommand, table);
string orderByExpr = null;
if (dbKey.KeyType == KeyType.PrimaryKey)
orderByExpr = null;
else
orderByExpr = BuildOrderBy(table.DefaultOrderBy);
var sql = string.Format(SqlSelectByFkTemplate, strColumns, table.FullName, whereExpr, orderByExpr);
//Damn postgres reformats the SQL in stored proc body and this screws up comparison; so we are careful here
sql = sql.Trim() + ";";
cmdInfo.Sql = sql;
cmdInfo.EntityMaterializer = CreateEntityMaterializer(table, outColumns);
return cmdInfo;
}
public DbCommandInfo BuildCommand(DbSqlBuilder dbSqlBuilder, EntityCommand entityCommand)
{
switch(entityCommand.Kind) {
case EntityCommandKind.SelectAll:
return dbSqlBuilder.BuildSelectAllCommand(entityCommand);
case EntityCommandKind.SelectAllPaged:
return dbSqlBuilder.BuildSelectAllPagedCommand(entityCommand);
case EntityCommandKind.SelectByKey:
return dbSqlBuilder.BuildSelectByKeyCommand(entityCommand);
case EntityCommandKind.SelectByKeyArray:
return dbSqlBuilder.BuildSelectByKeyArrayCommand(entityCommand);
case EntityCommandKind.SelectByKeyManyToMany:
return dbSqlBuilder.BuildSelectManyToManyCommand(entityCommand);
case EntityCommandKind.Insert:
return dbSqlBuilder.BuildSqlInsertCommand(entityCommand);
case EntityCommandKind.Update:
case EntityCommandKind.PartialUpdate:
return dbSqlBuilder.BuildSqlUpdateCommand(entityCommand);
case EntityCommandKind.Delete:
return dbSqlBuilder.BuildSqlDeleteCommand(entityCommand);
default:
// it is custom command - should be compiled on the fly
return null;
}
}
public override DbCommandInfo BuildSqlDeleteCommand(EntityCommand entityCommand)
{
var hasRowVersion = entityCommand.TargetEntityInfo.Flags.IsSet(EntityFlags.HasRowVersion);
if (hasRowVersion)
return BuildSqlDeleteCommandWithConcurrencyCheck(entityCommand);
else
return base.BuildSqlDeleteCommand(entityCommand);
}
public override DbCommandInfo BuildSelectAllPagedCommand(EntityCommand entCommand)
{
switch(_version) {
case MsSqlVersion.V2012:
return BuildSelectAllPagedCommand2012(entCommand);
case MsSqlVersion.V2008:
default:
return BuildSelectAllPagedCommand2008(entCommand);
}
}
public IList<EntityRecord> ExecuteSelect(EntitySession session, EntityCommand command, object[] args)
{
IList<EntityRecord> records;
if(Cache != null && Cache.TryExecuteSelect(session, command, args, out records))
return records;
records = Database.ExecuteSelect(session, command, args);
if(Cache != null)
Cache.CacheRecords(records);
return records;
}
public override DbCommandInfo CreateDbCommandInfo(EntityCommand entityCommand, string name, DbTableInfo mainTable, DbExecutionType executionType, string sql)
{
var cmdInfo = base.CreateDbCommandInfo(entityCommand, name, mainTable, executionType, sql);
var ent = entityCommand.TargetEntityInfo;
if (cmdInfo.Kind == EntityCommandKind.Insert && ent.Flags.IsSet(EntityFlags.HasIdentity)) {
//Add actions to read identity value
var idPrm = cmdInfo.Parameters.FirstOrDefault(p => p.SourceColumn.Flags.IsSet(DbColumnFlags.Identity));
if (idPrm != null)
cmdInfo.PostUpdateActions.Add(GetLastRowId);
}
return cmdInfo;
}
//Creates default implementation for servers that do not support array parameters.
// The command is implemented as templated SQL
public virtual DbCommandInfo BuildSelectByKeyArrayCommand(EntityCommand entityCommand)
{
const string SqlSelectByFkTemplate = @"
SELECT {0} {1}
FROM {2}
WHERE {3}
{4}";
var table = DbModel.LookupDbObject<DbTableInfo>(entityCommand.TargetEntityInfo, throwNotFound: true);
var dbKey = DbModel.LookupDbObject<DbKeyInfo>(entityCommand.SelectKey);
if (dbKey.KeyColumns.Count > 1)
return null;
var keyCol = dbKey.KeyColumns[0].Column.ColumnName;
var cmdName = ModelConfig.NamingPolicy.ConstructDbCommandName(entityCommand, table.TableName, "SelectByArrayOf_", keyCol);
var descrTag = GetDbCommandDescriptiveTag(entityCommand);
var cmdInfo = new DbCommandInfo(entityCommand, cmdName, table, DbExecutionType.Reader, null, descrTag);
cmdInfo.IsTemplatedSql = true;
//Build column list
var outColumns = table.Columns.GetSelectable();
var strColumns = outColumns.GetSqlNameList();
//build WHERE clause
var whereExpr = '"' + keyCol + '"' + " IN ({0})"; //this {0} will remain in a template
if (!string.IsNullOrWhiteSpace(entityCommand.Filter))
whereExpr = whereExpr + " AND " + ProcessFilter(entityCommand, table);
string orderByExpr = null;
if (dbKey.KeyType == KeyType.PrimaryKey)
orderByExpr = null;
else
orderByExpr = BuildOrderBy(table.DefaultOrderBy);
string strTop = string.Empty;
var sql = string.Format(SqlSelectByFkTemplate, strTop, strColumns, table.FullName, whereExpr, orderByExpr);
sql = sql.Trim() + ";";
cmdInfo.Sql = sql;
cmdInfo.EntityMaterializer = CreateEntityMaterializer(table, outColumns);
//Create parameter for just-in-time formatting of SQL
var entPrm = entityCommand.Parameters[0];
Type elemType;
Util.Check(entPrm.DataType.IsListOfDbPrimitive(out elemType), "Parameter is not list of primitives.");
var elemTypeInfo = GetDbTypeInfo(elemType, 0);
Util.Check(elemTypeInfo != null, "Failed to get db type information for type {0}.", elemType);
var prm = new DbParamInfo(entPrm, "(array)", 0);
prm.ToLiteralConverter = (list) => ConvertList(list, elemTypeInfo);
cmdInfo.Parameters.Add(prm);
return cmdInfo;
}
//
public override DbCommandInfo BuildSqlInsertCommand(EntityCommand entityCommand)
{
const string SqlInsertTemplate = @"
INSERT INTO {0}
({1})
VALUES
({2});
{3}
";
bool useStoredProc = this.DbModel.Config.Options.IsSet(DbOptions.UseStoredProcs);
var table = DbModel.LookupDbObject<DbTableInfo>(entityCommand.TargetEntityInfo, throwNotFound: true);
var idClause = string.Empty;
var listColumns = new List<DbColumnInfo>();
var listValues = new StringList();
var cmdName = ModelConfig.NamingPolicy.ConstructDbCommandName(entityCommand, table.TableName, "Insert");
var dbCmdInfo = CreateDbCommandInfo(entityCommand, cmdName, table, DbExecutionType.NonQuery, null);
foreach (var prm in dbCmdInfo.Parameters) {
var col = prm.SourceColumn;
if (!col.Flags.IsSet(DbColumnFlags.NoInsert)) {
listColumns.Add(col);
listValues.Add(prm.Name);
}
// identity
if (col.Flags.IsSet(DbColumnFlags.Identity)) {
if (useStoredProc)
//append to stored proc
idClause = string.Format("SET {0} = LAST_INSERT_ID();", prm.Name);
else
dbCmdInfo.PostUpdateActions.Add((conn, cmd, rec) => {
var idCmd = conn.DbConnection.CreateCommand();
idCmd.CommandText = "Select LAST_INSERT_ID();";
idCmd.Transaction = conn.DbTransaction;
var id = conn.Database.ExecuteDbCommand(idCmd, conn, DbExecutionType.Scalar); //it is decimal
var intId = Convert.ChangeType(id, prm.SourceColumn.Member.DataType);
rec.SetValueDirect(prm.SourceColumn.Member, intId);
});
}//if identity
}
// this.ModelConfig.Options.IsSet(DbOptions.us)
//build SQL
var strColumns = listColumns.GetSqlNameList();
var strValues = string.Join(", ", listValues);
dbCmdInfo.Sql = string.Format(SqlInsertTemplate, table.FullName, strColumns, strValues, idClause);
return dbCmdInfo;
}
// Builds a SELECT command without paging. For now paging is provider-dependent.
public virtual DbCommandInfo BuildSelectAllCommand(EntityCommand entityCommand)
{
var table = DbModel.LookupDbObject<DbTableInfo>(entityCommand.TargetEntityInfo, throwNotFound: true);
const string SqlSelectAllTemplate =
@"SELECT {0}
FROM {1}{2};"; //note: no space between 1 & 2
//Build column list
var outColumns = table.Columns.GetSelectable();
var strColumns = outColumns.GetSqlNameList();
var strOrderBy = BuildOrderBy(table.DefaultOrderBy);
//Pg SQL is picky about extra spaces - it removes space before ';' when saving stored proc, so let's be careful here
if(!string.IsNullOrEmpty(strOrderBy))
strOrderBy = " " + strOrderBy;
var sql = string.Format(SqlSelectAllTemplate, strColumns, table.FullName, strOrderBy);
var cmdName = ModelConfig.NamingPolicy.ConstructDbCommandName(entityCommand, table.TableName, "SelectAll");
var cmdInfo = CreateDbCommandInfo(entityCommand, cmdName, table, DbExecutionType.Reader, sql);
cmdInfo.EntityMaterializer = CreateEntityMaterializer(table, outColumns);
return cmdInfo;
}
public override DbCommandInfo CreateDbCommandInfo(EntityCommand entityCommand, string name, DbTableInfo mainTable, DbExecutionType executionType, string sql)
{
var cmdInfo = base.CreateDbCommandInfo(entityCommand, name, mainTable, executionType, sql);
var ent = entityCommand.TargetEntityInfo;
if (cmdInfo.Kind == EntityCommandKind.Insert && ent.Flags.IsSet(EntityFlags.HasIdentity)) {
//Add actions to read identity value
var idPrm = cmdInfo.Parameters.FirstOrDefault(p => p.SourceColumn.Flags.IsSet(DbColumnFlags.Identity));
if (idPrm != null) {
cmdInfo.PostUpdateActions.Add((conn, cmd, rec) => {
var idCmd = conn.DbConnection.CreateCommand();
idCmd.CommandText = "Select @@IDENTITY;";
idCmd.Transaction = conn.DbTransaction;
var id = conn.Database.ExecuteDbCommand(idCmd, conn, DbExecutionType.Scalar); //it is decimal
var intId = Convert.ChangeType(id, idPrm.SourceColumn.Member.DataType);
rec.SetValueDirect(idPrm.SourceColumn.Member, intId);
});
}//if IdPrm ...
}
return cmdInfo;
}
public override DbCommandInfo BuildSelectAllPagedCommand(EntityCommand entityCommand)
{
const string SqlSelectAllPaged = @"
SELECT {0}
FROM {1}
{2}
LIMIT @__maxRows
OFFSET @__skipRows
;";
var table = DbModel.LookupDbObject<DbTableInfo>(entityCommand.TargetEntityInfo, throwNotFound: true);
//Build column list
var outColumns = table.Columns.GetSelectable();
var strColumns = outColumns.GetSqlNameList();
string strOrderBy = BuildOrderBy(table.DefaultOrderBy); //might be empty
var sql = string.Format(SqlSelectAllPaged, strColumns, table.FullName, strOrderBy);
var cmdName = ModelConfig.NamingPolicy.ConstructDbCommandName(entityCommand, table.TableName, "SelectAllPaged");
var cmdInfo = CreateDbCommandInfo(entityCommand, cmdName, table, DbExecutionType.Reader, sql);
cmdInfo.EntityMaterializer = CreateEntityMaterializer(table, outColumns);
return cmdInfo;
}
public override DbCommandInfo BuildSelectAllPagedCommand(EntityCommand entityCommand)
{
//Note: PgSql cuts-off some trailing spaces on lines, and the stored proc comparison might fail in
// Db model comparer - identical procs are detected as different. So do not add trailing spaces anywhere
const string SqlSelectAllPaged = @"
SELECT {0}
FROM {1}
{2}
OFFSET {3}__skipRows
LIMIT {3}__maxRows;";
var table = DbModel.LookupDbObject<DbTableInfo>(entityCommand.TargetEntityInfo, throwNotFound: true);
//Build column list
var outColumns = table.Columns.GetSelectable();
var strColumns = outColumns.GetSqlNameList();
string strOrderBy = (table.DefaultOrderBy == null) ? "ORDER BY (SELECT 1)" : BuildOrderBy(table.DefaultOrderBy);
var prmPrefix = GetParameterPrefix();
var sql = string.Format(SqlSelectAllPaged, strColumns, table.FullName, strOrderBy, prmPrefix);
var cmdName = ModelConfig.NamingPolicy.ConstructDbCommandName(entityCommand, table.TableName, "SelectAllPaged");
var cmdInfo = CreateDbCommandInfo(entityCommand, cmdName, table, DbExecutionType.Reader, sql);
cmdInfo.EntityMaterializer = CreateEntityMaterializer(table, outColumns);
return cmdInfo;
}
public virtual DbCommandInfo CreateDbCommandInfo(EntityCommand entityCommand, string name, DbTableInfo mainTable, DbExecutionType executionType, string sql)
{
var descrTag = GetDbCommandDescriptiveTag(entityCommand);
var cmdInfo = new DbCommandInfo(entityCommand, name, mainTable, executionType, sql, descrTag);
//Create parameters from entity command parameters
var policy = DbModel.Config.NamingPolicy;
var prmPrefix = GetParameterPrefix();
for(int i=0; i< entityCommand.Parameters.Count; i++) {
var entParam = entityCommand.Parameters[i];
var paramName = prmPrefix + policy.ConstructDbParameterName(entParam.Name);
DbParamInfo prmInfo;
if (entParam.SourceMember != null) {
var col = mainTable.Columns.FirstOrDefault(c => c.Member == entParam.SourceMember);
Util.Check(col != null, "Failed to find Db column for member {0}, entity {1}.", entParam.SourceMember.MemberName, mainTable.Entity.Name);
prmInfo = cmdInfo.AddParameter(entParam, paramName, col, i);
} else {
var typeInfo = GetDbTypeInfo(entParam.DataType, entParam.Size);
prmInfo = cmdInfo.AddParameter(entParam, paramName, typeInfo, i);
}
// SQL CE does not support output parameters
if (!this.DbModel.Driver.Supports(DbFeatures.OutputParameters))
prmInfo.Direction = ParameterDirection.Input;
if (prmInfo.Direction == ParameterDirection.Output || prmInfo.Direction == ParameterDirection.InputOutput) {
cmdInfo.PostUpdateActions.Add((con, cmd, rec) => {
var prm = (IDbDataParameter) cmd.Parameters[prmInfo.Name];
rec.SetValueDirect(prmInfo.SourceColumn.Member, prm.Value);
});
}
}//foreach entParam
return cmdInfo;
}
public virtual DbCommandInfo BuildSqlUpdateCommand(EntityCommand entityCommand)
{
const string SqlUpdateTemplate =
@"UPDATE {0}
SET {1}
WHERE {2};";
var table = DbModel.LookupDbObject<DbTableInfo>(entityCommand.TargetEntityInfo, throwNotFound: true);
if (entityCommand == null)
return null;
var cmdName = ModelConfig.NamingPolicy.ConstructDbCommandName(entityCommand, table.TableName, "Update");
var cmdInfo = CreateDbCommandInfo(entityCommand, cmdName, table, DbExecutionType.NonQuery, null);
var pkParams = cmdInfo.Parameters.Where(p => p.SourceColumn.Flags.IsSet(DbColumnFlags.PrimaryKey));
// find members to update. For partial update, with explicitly specified members to update, we ignore NoUpdate flag
var excludeFlags = DbColumnFlags.PrimaryKey;
if (entityCommand.Kind == EntityCommandKind.Update)
excludeFlags |= DbColumnFlags.NoUpdate;
var updateParams = cmdInfo.Parameters.Where(p => !p.SourceColumn.Flags.IsSet(excludeFlags));
// Some tables (like many-to-many link entities) might have no columns to update
if (!updateParams.Any())
return null;
//Build Where expression
var whereExpr = BuildWhereClause(pkParams.ToList());
//Build Update clause
var qt = "\"";
var updList = new StringList();
foreach (var prm in updateParams)
updList.Add(qt + prm.SourceColumn.ColumnName + qt + " = " + prm.Name);
var strUpdates = string.Join(", ", updList);
//Build SQL
cmdInfo.Sql = string.Format(SqlUpdateTemplate, table.FullName, strUpdates, whereExpr);
return cmdInfo;
}
public virtual DbCommandInfo BuildSqlInsertCommand(EntityCommand entityCommand)
{
const string SqlInsertTemplate = "INSERT INTO {0} \r\n ({1}) \r\n VALUES \r\n ({2});";
var table = DbModel.LookupDbObject<DbTableInfo>(entityCommand.TargetEntityInfo, throwNotFound: true);
var idClause = string.Empty;
var listColumns = new List<DbColumnInfo>();
var listValues = new StringList();
var cmdName = ModelConfig.NamingPolicy.ConstructDbCommandName(entityCommand, table.TableName, "Insert");
var dbCmdInfo = CreateDbCommandInfo(entityCommand, cmdName, table, DbExecutionType.NonQuery, null);
foreach (var prm in dbCmdInfo.Parameters) {
var col = prm.SourceColumn;
if (!col.Flags.IsSet(DbColumnFlags.NoInsert)) {
listColumns.Add(col);
listValues.Add(prm.Name);
}
}
//build SQL
var strColumns = listColumns.GetSqlNameList();
var strValues = string.Join(", ", listValues);
dbCmdInfo.Sql = string.Format(SqlInsertTemplate, table.FullName, strColumns, strValues) + idClause;
return dbCmdInfo;
}
public IList<EntityRecord> ExecuteSelect(EntitySession session, EntityCommand command, object[] args)
{
EntityInfo entInfo = command.TargetEntityInfo;
var dbCommandInfo = GetDbCommandInfo(command);
if (dbCommandInfo == null) {
//check if it is request for SelectAllPaged and driver does not support paging - then substitute with SelecAll
if (command == entInfo.CrudCommands.SelectAllPaged)
dbCommandInfo = GetDbCommandInfo(entInfo.CrudCommands.SelectAll);
}
if (dbCommandInfo == null)
Util.Throw("Db command not found for entity command " + command.CommandName);
var conn = GetConnection(session);
try {
var cmd = CreateDbCommand(dbCommandInfo, conn);
if (dbCommandInfo.IsTemplatedSql)
FormatTemplatedSql(dbCommandInfo, cmd, args);
else
SetCommandParameterValues(dbCommandInfo, cmd, args);
var records = new List<EntityRecord>();
var result = ExecuteDbCommand(cmd, conn, DbExecutionType.Reader,
(reader) => {
while(reader.Read())
records.Add(dbCommandInfo.EntityMaterializer.ReadRecord(reader, session));
return records.Count;
});
ReleaseConnection(conn);
return records;
} catch {
ReleaseConnection(conn, true);
throw;
}
}
internal void OnExecutedSelect(EntitySession session, EntityCommand command)
{
if (ExecutedSelect != null)
ExecutedSelect(session, new EntityCommandEventArgs(session, command));
}
//Produces Descriptive tag that identifies the proc and its purpose. It is used to map proc in the database to CRUD entity command
public string GetDbCommandDescriptiveTag(EntityCommand command)
{
var table = DbModel.GetTable(command.TargetEntityType, throwIfNotFound: false);
Util.Check(table != null, "Target Db table not found for entity command {0}", command);
var tag = "CRUD/" + table.FullName + "/" + command.Kind;
switch(command.Kind) {
case EntityCommandKind.SelectAll:
case EntityCommandKind.SelectAllPaged:
break;
case EntityCommandKind.SelectByKey:
case EntityCommandKind.SelectByKeyArray:
case EntityCommandKind.SelectByKeyManyToMany:
var dbKey = DbModel.LookupDbObject<DbKeyInfo>(command.SelectKey);
tag += "/" + string.Join(",", dbKey.KeyColumns);
break;
case EntityCommandKind.CustomSelect:
case EntityCommandKind.CustomInsert:
case EntityCommandKind.CustomUpdate:
case EntityCommandKind.CustomDelete:
tag += "/" + command.CommandName;
break;
case EntityCommandKind.PartialUpdate:
tag += "/" + string.Join(string.Empty, command.UpdateMemberNames);
break;
}
if (!string.IsNullOrWhiteSpace(command.Filter))
tag += "/Filter:" + command.Filter;
return tag;
}
protected virtual string ProcessFilter(EntityCommand command, DbTableInfo table)
{
var filter = command.Filter;
if (filter == null || !filter.Contains('{'))
return filter;
foreach (var col in table.Columns) {
var name = "{" + col.Member.MemberName + "}";
if (!filter.Contains(name))
continue;
var colRef = '"' + col.ColumnName + '"';
filter = filter.Replace(name, colRef);
}
filter = "(" + filter + ")";
return filter;
}
//
public override DbCommandInfo BuildSqlInsertCommand(EntityCommand entityCommand)
{
const string SqlInsertTemplate = @"
INSERT INTO {0}
({1})
VALUES
({2});
{3} {4}
";
const string SqlGetIdentityTemplate = "\r\nSET {0} = SCOPE_IDENTITY();";
const string SqlGetRowVersionTemplate = "\r\nSET {0} = @@DBTS;";
var table = DbModel.LookupDbObject<DbTableInfo>(entityCommand.TargetEntityInfo, throwNotFound: true);
var idClause = string.Empty;
var rvClause = string.Empty;
var listColumns = new List<DbColumnInfo>();
var listValues = new StringList();
var cmdName = ModelConfig.NamingPolicy.ConstructDbCommandName(entityCommand, table.TableName, "Insert");
var dbCmdInfo = CreateDbCommandInfo(entityCommand, cmdName, table, DbExecutionType.NonQuery, null);
foreach (var prm in dbCmdInfo.Parameters) {
var col = prm.SourceColumn;
if (col.Flags.IsSet(DbColumnFlags.Identity))
idClause = string.Format(SqlGetIdentityTemplate, prm.Name);
if (col.Member.Flags.IsSet(EntityMemberFlags.RowVersion))
rvClause = string.Format(SqlGetRowVersionTemplate, prm.Name);
if (!col.Flags.IsSet(DbColumnFlags.NoInsert)) {
listColumns.Add(col);
listValues.Add(prm.Name);
}
}
//build SQL
var strColumns = listColumns.GetSqlNameList();
var strValues = string.Join(", ", listValues);
dbCmdInfo.Sql = string.Format(SqlInsertTemplate, table.FullName, strColumns, strValues, idClause, rvClause);
return dbCmdInfo;
}
// Paging is provider-specific. Drivers should overwrite this method to implement paging.
public virtual DbCommandInfo BuildSelectAllPagedCommand(EntityCommand entityCommand)
{
return null;
}
private void LogCommand(EntitySession session, EntityCommand command, object[] args, CacheType cachingType, long time, int rowCount)
{
var logEntry = new CacheCommandLogEntry(session.Context, command.CommandName, args, _timeService.UtcNow, time, rowCount, cachingType);
session.AddLogEntry(logEntry);
}
public DbCommandInfo(EntityCommand entityCommand, string commandName, DbTableInfo table, DbExecutionType executionType, string sql, string tag)
: base(table.DbModel, table.Schema, DbObjectType.Command, entityCommand)
{
EntityCommand = entityCommand;
CommandName = commandName;
Table = table;
ExecutionType = executionType;
Sql = sql;
Description = EntityCommand.Description;
DescriptiveTag = tag;
//derived entities
FullCommandName = Table.DbModel.Driver.GetFullName(Schema, CommandName);
Kind = entityCommand.Kind;
var dbModel = table.DbModel;
dbModel.AddCommand(this);
if(Table != null)
Table.CrudCommands.Add(this);
base.GlobalName = DbModelHelper.GetGlobalName(Schema, commandName);
}
public bool TryExecuteSelect(EntitySession session, EntityCommand command, object[] args, out IList<EntityRecord> records)
{
records = _empty;
if(!Settings.CacheEnabled || session.CacheDisabled)
return false;
if (!string.IsNullOrWhiteSpace(command.Filter))
return false;
var cachingType = command.TargetEntityInfo.CacheType;
if(cachingType == CacheType.None)
return false;
switch(cachingType) {
case CacheType.None: return false;
case CacheType.FullSet:
var start = _timeService.ElapsedMilliseconds;
var result = _fullSetCache.TryExecuteSelect(session, command, args, out records);
if(result) {
var end = _timeService.ElapsedMilliseconds;
var rowCount = records == null ? 0 : records.Count;
LogCommand(session, command, args, cachingType, end - start, rowCount);
}
return result;
case CacheType.Sparse:
var getByPk = command.Kind == EntityCommandKind.SelectByKey && command.SelectKey.KeyType.IsSet(KeyType.PrimaryKey);
if(getByPk) {
var pk = new EntityKey(command.SelectKey, args);
var rec = _sparseCache.Lookup(pk, session);
if(rec != null) {
records = new EntityRecord[] { rec };
LogCommand(session, command, args, cachingType, 0, 1);
return true;
}
}
return false;
}//switch
return false;
}
public EntityCommandEventArgs(IEntitySession session, EntityCommand command)
: base(session)
{
Command = command;
}
public virtual DbCommandInfo BuildSelectByKeyCommand(EntityCommand entityCommand)
{
const string SqlSelectByFkTemplate = @"
SELECT {0} {1}
FROM {2}
WHERE {3}
{4}";
var table = DbModel.LookupDbObject<DbTableInfo>(entityCommand.TargetEntityInfo, throwNotFound: true);
var dbKey = DbModel.LookupDbObject<DbKeyInfo>(entityCommand.SelectKey);
var keyCols = dbKey.KeyColumns.GetNames(removeUnderscores: true);
var cmdName = ModelConfig.NamingPolicy.ConstructDbCommandName(entityCommand, table.TableName, "SelectBy", keyCols);
var cmdInfo = CreateDbCommandInfo(entityCommand, cmdName, table, DbExecutionType.Reader, null);
//Build column list
var outColumns = table.Columns.GetSelectable();
var strColumns = outColumns.GetSqlNameList();
//build WHERE clause
var whereExpr = BuildWhereClause(cmdInfo.Parameters);
if (!string.IsNullOrWhiteSpace(entityCommand.Filter))
whereExpr = whereExpr + " AND " + ProcessFilter(entityCommand, table);
string orderByExpr = null;
if (dbKey.KeyType == KeyType.PrimaryKey)
orderByExpr = null;
else
orderByExpr = BuildOrderBy(table.DefaultOrderBy);
string strTop = string.Empty;
var sql = string.Format(SqlSelectByFkTemplate, strTop, strColumns, table.FullName, whereExpr, orderByExpr);
//Damn postgres reformats the SQL in stored proc body and this screws up comparison; so we are careful here
sql = sql.Trim() + ";";
cmdInfo.Sql = sql;
cmdInfo.EntityMaterializer = CreateEntityMaterializer(table, outColumns);
return cmdInfo;
}
private DbCommandInfo GetDbCommandInfo(EntityCommand entityCommand)
{
var cmd = DbModel.LookupDbObject<DbCommandInfo>(entityCommand);
if (cmd == null) {
Util.Throw("Command {0} not implemented by the database. Most likely driver does not support this type of command.", entityCommand.CommandName);
}
return cmd;
}
public virtual DbCommandInfo BuildSelectManyToManyCommand(EntityCommand entityCommand)
{
const string SqlTemplate = @"
SELECT {0}
FROM {1} L INNER JOIN {2} T ON {3}
WHERE {4}
{5};";
var parentRefKey = entityCommand.SelectKey;
var targetRefKey = entityCommand.SelectKeySecondary;
var targetTable = DbModel.LookupDbObject<DbTableInfo>(entityCommand.TargetEntityInfo);
var dbParentRefKey = DbModel.LookupDbObject<DbKeyInfo>(parentRefKey);
var dbTargetRefKey = DbModel.LookupDbObject<DbKeyInfo>(targetRefKey);
var linkTable = dbParentRefKey.Table;
var parentRefColNames = dbParentRefKey.KeyColumns.GetNames(removeUnderscores: true);
var cmdName = ModelConfig.NamingPolicy.ConstructDbCommandName(entityCommand, targetTable.TableName, "SelectBy", parentRefColNames);
// var cmdInfo = CreateDbCommandInfo(entityCommand, cmdName, targetTable, DbExecutionType.Reader, null);
var cmdInfo = CreateDbCommandInfo(entityCommand, cmdName, linkTable, DbExecutionType.Reader, null);
var outColumns = targetTable.Columns.GetSelectable();
var strOutColumns = outColumns.GetSqlNameList("T");
var whereExpr = BuildWhereClause(cmdInfo.Parameters, "L");
var orderByExpr = BuildOrderBy(linkTable.DefaultOrderBy, "L");
string joinOnClause = BuildJoinClause(dbTargetRefKey, targetTable.PrimaryKey, "L", "T");
cmdInfo.Sql = string.Format(SqlTemplate, strOutColumns, linkTable.FullName, targetTable.FullName, joinOnClause, whereExpr, orderByExpr);
cmdInfo.EntityMaterializer = CreateEntityMaterializer(targetTable, outColumns);
return cmdInfo;
}
public virtual DbCommandInfo BuildSqlDeleteCommand(EntityCommand entityCommand)
{
const string SqlDeleteTemplate = "DELETE FROM {0} WHERE {1};";
var table = DbModel.LookupDbObject<DbTableInfo>(entityCommand.TargetEntityInfo, throwNotFound: true);
//Load by primary key
var cmdName = ModelConfig.NamingPolicy.ConstructDbCommandName(entityCommand, table.TableName, "Delete");
var cmdInfo = CreateDbCommandInfo(entityCommand, cmdName, table, DbExecutionType.NonQuery, null);
var strWhere = BuildWhereClause(cmdInfo.Parameters);
cmdInfo.Sql = string.Format(SqlDeleteTemplate, table.FullName, strWhere);
return cmdInfo;
}
public void AddCommand(EntityCommand command)
{
_commands.Add(command);
}
