private ProcedureSetStatement CreateSetStatement(ProcedureStatement statement, Column column)
{
ProcedureExpressionStatement rightExpression = new ProcedureExpressionStatement(statement, column.DefaultValue);
rightExpression.Function = ProcedureFunctionType.Snippet; // Value will be written as is
var setStatement = new ProcedureSetStatement(statement, new TableRefColumn(column));
setStatement.RightExpression = rightExpression;
return(setStatement);
}
private static void UpdateProcedure(Procedure procedure)
{
procedure.Parameters[PassPhraseParameterName].DefaultValue = null; // passphrase must be provided
if (procedure.ProcedureType == ProcedureType.SaveEntity)
{
procedure.Body.Visit <ProcedureStatement>(s =>
{
var statement = s as ProcedureSetStatement;
if (statement == null || statement.LeftExpression == null || statement.RightExpression == null || !MustEncrypt(statement.LeftExpression.RefColumn))
{
return;
}
string parameterName = statement.RightExpression.Parameter.Name;
statement.RightExpression.Literal = ProcedureExpressionStatement.CreateLiteral(string.Format("ENCRYPTBYPASSPHRASE(@{0}, @{1})", PassPhraseParameterName, parameterName));
statement.RightExpression.Parameter = null;
// Column is of type varbinary but parameter must be of type string
var parameter = procedure.Parameters[parameterName];
if (parameter != null)
{
parameter.DbType = System.Data.DbType.String;
}
});
return;
}
procedure.Body.Visit <ProcedureStatement>(s =>
{
var procedureExpressionStatement = s as ProcedureExpressionStatement;
if (procedureExpressionStatement != null)
{
if (procedureExpressionStatement.LeftExpression == null || !MustEncrypt(procedureExpressionStatement.LeftExpression.RefColumn))
{
return;
}
var procedureSetStatement = procedureExpressionStatement as ProcedureSetStatement;
if (procedureSetStatement != null)
{
procedureSetStatement.As = new ProcedureExpressionStatement(procedureSetStatement, ProcedureExpressionStatement.CreateLiteral(procedureSetStatement.LeftExpression.RefColumn.Column.Name));
}
procedureExpressionStatement.LeftExpression.Literal = ProcedureExpressionStatement.CreateLiteral(string.Format("CONVERT(nvarchar(max), DECRYPTBYPASSPHRASE(@{0}, {1}))", PassPhraseParameterName, procedureExpressionStatement.LeftExpression.RefColumn.Column.Name));
procedureExpressionStatement.LeftExpression.RefColumn = null;
}
});
}
private ProcedureSetStatement CreateSetStatement(ProcedureStatement statement, Column column)
{
ProcedureExpressionStatement rightExpression = new ProcedureExpressionStatement(statement, column.DefaultValue);
rightExpression.Function = ProcedureFunctionType.Snippet; // Value will be written as is
var setStatement = new ProcedureSetStatement(statement, new TableRefColumn(column));
setStatement.RightExpression = rightExpression;
return setStatement;
}
private ProcedureExpressionStatement CreateParameterTestStatement(ProcedureStatement parent, Parameter parameter, FilterFunctions op)
{
ProcedureExpressionStatement result = null;
TableRefColumn refColumn = new TableRefColumn(parameter.Column);
switch (op)
{
case FilterFunctions.Equals:
result = new ProcedureExpressionStatement(parent, ProcedureOperationType.Equals, refColumn, parameter);
break;
case FilterFunctions.NotEquals:
result = new ProcedureExpressionStatement(parent, ProcedureOperationType.NotEquals, refColumn, parameter);
break;
case FilterFunctions.IsLessThan:
result = new ProcedureExpressionStatement(parent, ProcedureOperationType.IsLesserThan, refColumn, parameter);
break;
case FilterFunctions.IsLessThanOrEqualTo:
result = new ProcedureExpressionStatement(parent, ProcedureOperationType.IsLesserThanOrEqualTo, refColumn, parameter);
break;
case FilterFunctions.IsGreaterThan:
result = new ProcedureExpressionStatement(parent, ProcedureOperationType.IsGreaterThan, refColumn, parameter);
break;
case FilterFunctions.IsGreaterThanOrEqualTo:
result = new ProcedureExpressionStatement(parent, ProcedureOperationType.IsGreaterThanOrEqualTo, refColumn, parameter);
break;
case FilterFunctions.FullTextContains:
case FilterFunctions.NotFullTextContains:
result = new ProcedureExpressionStatement(parent, ProcedureOperationType.Contains, refColumn, parameter);
break;
case FilterFunctions.FreeText:
case FilterFunctions.NotFreeText:
result = new ProcedureExpressionStatement(parent, ProcedureOperationType.FreeText, refColumn, parameter);
break;
case FilterFunctions.StartsWith:
result = new ProcedureExpressionStatement(
parent,
ProcedureOperationType.Like,
new ProcedureExpressionStatement(parent, refColumn),
CreateConcatStatement(parent, parameter, "%"));
break;
case FilterFunctions.EndsWith:
result = new ProcedureExpressionStatement(
parent,
ProcedureOperationType.Like,
new ProcedureExpressionStatement(parent, refColumn),
CreateConcatStatement(parent, "%", parameter));
break;
case FilterFunctions.Contains:
case FilterFunctions.NotContains:
result = new ProcedureExpressionStatement(
parent,
ProcedureOperationType.Like,
new ProcedureExpressionStatement(parent, refColumn),
CreateConcatStatement(parent, "%", parameter, "%"));
break;
}
// Negate statement if needed
switch (op)
{
case FilterFunctions.NotContains:
case FilterFunctions.NotFullTextContains:
case FilterFunctions.NotFreeText:
result = new ProcedureExpressionStatement(parent, ProcedureOperationType.Not, result, null);
break;
}
return result;
}
private void UpdateProcedures()
{
foreach (var procedure in Project.Database.Procedures)
{
ProcedureSelectStatement selectStatement = null;
foreach (var enumParameter in procedure.Parameters)
{
// Find the original parameter
var methodParameter = enumParameter.MethodParameter;
if (methodParameter == null)
continue;
var originalMethodParameter = methodParameter.GetData(NamespaceUri + ":parameter", (MethodParameter)null);
if (originalMethodParameter == null)
continue;
var valueParameter = procedure.Parameters.FirstOrDefault(_ => _.MethodParameter == originalMethodParameter);
if (valueParameter == null || valueParameter.Column == null)
continue;
if (selectStatement == null)
{
// Get the select statement of the procedure
// The procedure should contains only one Select statement
selectStatement = procedure.Body.Statements.OfType<ProcedureSelectStatement>().FirstOrDefault();
if (selectStatement == null)
return;
}
ProcedureExpressionStatement whereStatement = null;
foreach (var enumValue in FilterFunctionsEnumeration.Values)
{
FilterFunctions op = GetFilterFunction(enumValue);
if (!IsSupported(op, procedure, valueParameter))
continue;
ProcedureExpressionStatement testEnumValueStatement = CreateEnumerationEqualsStatement(selectStatement, enumParameter, enumValue);
ProcedureExpressionStatement testValueStatement = CreateParameterTestStatement(selectStatement, valueParameter, op);
if (testValueStatement == null)
continue;
var paramWhereStatement = new ProcedureExpressionStatement(
selectStatement,
ProcedureOperationType.And,
testEnumValueStatement,
testValueStatement);
if (whereStatement == null)
{
whereStatement = paramWhereStatement;
}
else
{
whereStatement = new ProcedureExpressionStatement(selectStatement, ProcedureOperationType.Or, whereStatement, paramWhereStatement);
}
}
// Declare the statement as a search expression
// Search expression is used by SEARCH method to identify dynamic parts of the body
whereStatement.Visit<ProcedureStatement>(statement =>
{
ProcedureExpressionStatement s = statement as ProcedureExpressionStatement;
if (s != null)
{
procedure.MarkSearchExpression(s, valueParameter);
if (!s.IsLiteral)
{
s.SearchExpressionParameter = valueParameter;
}
}
});
selectStatement.AddExpressionToWhere(whereStatement, ProcedureOperationType.And);
}
}
}
private void UpdateProcedure(CodeFluent.Model.Persistence.Procedure procedure)
{
if (procedure.Table == null || procedure.Table.Entity == null)
{
return;
}
// Find columns to add to SELECT
// RowVersion and Identity columns are already part of the SELECT
var columns = procedure.Table.Entity.Properties
.Where(property => property.MustReadOnSave && !property.IsPersistenceIdentity)
.SelectMany(property => property.Columns)
.Where(column => column != null && !column.IsRowVersion)
.ToList();
var properties = procedure.Table.Entity.Properties
.Where(property => property.MustReadOnSave && !property.IsPersistenceIdentity && property.Columns.Count == 0)
.ToList();
if (columns.Count == 0 && properties.Count == 0)
{
return;
}
// Visit the body of the procedure
procedure.Body.Visit <ProcedureStatement>(statement =>
{
// We need to find a block statement (a list of statements) that looks like
// INSERT OR UPDATE statement
// statement (generally IfStatement)
// SELECT ...
/*
*
* -- Statement 1 : ProcedureBlockStatement which contains UPDATE & if(@error)
* UPDATE [Login] SET
* [Login].[Login_ProviderName] = @Login_ProviderName,
* [Login].[_trackLastWriteTime] = GETDATE()
* WHERE (([Login].[Login_Id] = @Login_Id) AND ([Login].[_rowVersion] = @_rowVersion))
*
* SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
* IF(@error != 0)
* BEGIN
* IF @tran = 1 ROLLBACK TRANSACTION
* RETURN
* END
*
* -- Statement 2 : ProcedureIfStatement
* IF(@rowcount = 0)
* BEGIN
* IF @tran = 1 ROLLBACK TRANSACTION
* RAISERROR (50001, 16, 1, 'Login_Save')
* RETURN
* END
*
* -- Statement 3 : ProcedureSelectStatement
* SELECT DISTINCT [Login].[_rowVersion]
* FROM [Login]
* WHERE ([Login].[Login_Id] = @Login_Id)
*
*/
var blockStatement = statement as ProcedureBlockStatement;
if (blockStatement == null)
{
return;
}
bool insertOrUpdate = false;
foreach (var s in blockStatement.Statements)
{
if (IsInsertOrUpdateStatement(s))
{
insertOrUpdate = true;
}
var selectStatement = s as ProcedureSelectStatement;
if (insertOrUpdate && selectStatement != null) // SELECT statement after INSERT or UPDATE statement
{
if (IsReadIdentityStatement(selectStatement)) // SELECT DISTINCT @Id = SCOPE_IDENTITY()
{
continue;
}
foreach (var column in columns)
{
var setStatment = new ProcedureSetStatement(selectStatement, new TableRefColumn(column));
selectStatement.Set.Add(setStatment);
}
foreach (var property in properties) // not persistent : select expression AS 'persistent name'
{
ProcedureExpressionStatement literalExpression = new ProcedureExpressionStatement(selectStatement, ProcedureExpressionStatement.CreateLiteral(GetSelectExpression(property)));
var setStatment = new ProcedureSetStatement(selectStatement, literalExpression, (ProcedureExpressionStatement)null, new ProcedureExpressionStatement(selectStatement, property.PersistenceName));
selectStatement.Set.Add(setStatment);
}
}
}
});
}
private ProcedureExpressionStatement CreateParameterTestStatement(ProcedureStatement parent, Parameter parameter, FilterFunctions op)
{
ProcedureExpressionStatement result = null;
TableRefColumn refColumn = new TableRefColumn(parameter.Column);
switch (op)
{
case FilterFunctions.Equals:
result = new ProcedureExpressionStatement(parent, ProcedureOperationType.Equals, refColumn, parameter);
break;
case FilterFunctions.NotEquals:
result = new ProcedureExpressionStatement(parent, ProcedureOperationType.NotEquals, refColumn, parameter);
break;
case FilterFunctions.IsLessThan:
result = new ProcedureExpressionStatement(parent, ProcedureOperationType.IsLesserThan, refColumn, parameter);
break;
case FilterFunctions.IsLessThanOrEqualTo:
result = new ProcedureExpressionStatement(parent, ProcedureOperationType.IsLesserThanOrEqualTo, refColumn, parameter);
break;
case FilterFunctions.IsGreaterThan:
result = new ProcedureExpressionStatement(parent, ProcedureOperationType.IsGreaterThan, refColumn, parameter);
break;
case FilterFunctions.IsGreaterThanOrEqualTo:
result = new ProcedureExpressionStatement(parent, ProcedureOperationType.IsGreaterThanOrEqualTo, refColumn, parameter);
break;
case FilterFunctions.FullTextContains:
case FilterFunctions.NotFullTextContains:
result = new ProcedureExpressionStatement(parent, ProcedureOperationType.Contains, refColumn, parameter);
break;
case FilterFunctions.FreeText:
case FilterFunctions.NotFreeText:
result = new ProcedureExpressionStatement(parent, ProcedureOperationType.FreeText, refColumn, parameter);
break;
case FilterFunctions.StartsWith:
result = new ProcedureExpressionStatement(
parent,
ProcedureOperationType.Like,
new ProcedureExpressionStatement(parent, refColumn),
CreateConcatStatement(parent, parameter, "%"));
break;
case FilterFunctions.EndsWith:
result = new ProcedureExpressionStatement(
parent,
ProcedureOperationType.Like,
new ProcedureExpressionStatement(parent, refColumn),
CreateConcatStatement(parent, "%", parameter));
break;
case FilterFunctions.Contains:
case FilterFunctions.NotContains:
result = new ProcedureExpressionStatement(
parent,
ProcedureOperationType.Like,
new ProcedureExpressionStatement(parent, refColumn),
CreateConcatStatement(parent, "%", parameter, "%"));
break;
}
// Negate statement if needed
switch (op)
{
case FilterFunctions.NotContains:
case FilterFunctions.NotFullTextContains:
case FilterFunctions.NotFreeText:
result = new ProcedureExpressionStatement(parent, ProcedureOperationType.Not, result, null);
break;
}
return(result);
}
private void UpdateProcedures()
{
foreach (var procedure in Project.Database.Procedures)
{
ProcedureSelectStatement selectStatement = null;
foreach (var enumParameter in procedure.Parameters)
{
// Find the original parameter
var methodParameter = enumParameter.MethodParameter;
if (methodParameter == null)
{
continue;
}
var originalMethodParameter = methodParameter.GetData(NamespaceUri + ":parameter", (MethodParameter)null);
if (originalMethodParameter == null)
{
continue;
}
var valueParameter = procedure.Parameters.FirstOrDefault(_ => _.MethodParameter == originalMethodParameter);
if (valueParameter == null || valueParameter.Column == null)
{
continue;
}
if (selectStatement == null)
{
// Get the select statement of the procedure
// The procedure should contains only one Select statement
selectStatement = procedure.Body.Statements.OfType <ProcedureSelectStatement>().FirstOrDefault();
if (selectStatement == null)
{
return;
}
}
ProcedureExpressionStatement whereStatement = null;
foreach (var enumValue in FilterFunctionsEnumeration.Values)
{
FilterFunctions op = GetFilterFunction(enumValue);
if (!IsSupported(op, procedure, valueParameter))
{
continue;
}
ProcedureExpressionStatement testEnumValueStatement = CreateEnumerationEqualsStatement(selectStatement, enumParameter, enumValue);
ProcedureExpressionStatement testValueStatement = CreateParameterTestStatement(selectStatement, valueParameter, op);
if (testValueStatement == null)
{
continue;
}
var paramWhereStatement = new ProcedureExpressionStatement(
selectStatement,
ProcedureOperationType.And,
testEnumValueStatement,
testValueStatement);
if (whereStatement == null)
{
whereStatement = paramWhereStatement;
}
else
{
whereStatement = new ProcedureExpressionStatement(selectStatement, ProcedureOperationType.Or, whereStatement, paramWhereStatement);
}
}
// Declare the statement as a search expression
// Search expression is used by SEARCH method to identify dynamic parts of the body
whereStatement.Visit <ProcedureStatement>(statement =>
{
ProcedureExpressionStatement s = statement as ProcedureExpressionStatement;
if (s != null)
{
procedure.MarkSearchExpression(s, valueParameter);
if (!s.IsLiteral)
{
s.SearchExpressionParameter = valueParameter;
}
}
});
selectStatement.AddExpressionToWhere(whereStatement, ProcedureOperationType.And);
}
}
}
private void UpdateProcedure(CodeFluent.Model.Persistence.Procedure procedure)
{
if (procedure.Table == null || procedure.Table.Entity == null)
return;
// Find columns to add to SELECT
// RowVersion and Identity columns are already part of the SELECT
var columns = procedure.Table.Entity.Properties
.Where(property => property.MustReadOnSave && !property.IsPersistenceIdentity)
.SelectMany(property => property.Columns)
.Where(column => column != null && !column.IsRowVersion)
.ToList();
var properties = procedure.Table.Entity.Properties
.Where(property => property.MustReadOnSave && !property.IsPersistenceIdentity && property.Columns.Count == 0)
.ToList();
if (columns.Count == 0 && properties.Count == 0)
return;
// Visit the body of the procedure
procedure.Body.Visit<ProcedureStatement>(statement =>
{
// We need to find a block statement (a list of statements) that looks like
// INSERT OR UPDATE statement
// statement (generally IfStatement)
// SELECT ...
/*
*
-- Statement 1 : ProcedureBlockStatement which contains UPDATE & if(@error)
UPDATE [Login] SET
[Login].[Login_ProviderName] = @Login_ProviderName,
[Login].[_trackLastWriteTime] = GETDATE()
WHERE (([Login].[Login_Id] = @Login_Id) AND ([Login].[_rowVersion] = @_rowVersion))
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
IF(@error != 0)
BEGIN
IF @tran = 1 ROLLBACK TRANSACTION
RETURN
END
-- Statement 2 : ProcedureIfStatement
IF(@rowcount = 0)
BEGIN
IF @tran = 1 ROLLBACK TRANSACTION
RAISERROR (50001, 16, 1, 'Login_Save')
RETURN
END
-- Statement 3 : ProcedureSelectStatement
SELECT DISTINCT [Login].[_rowVersion]
FROM [Login]
WHERE ([Login].[Login_Id] = @Login_Id)
*/
var blockStatement = statement as ProcedureBlockStatement;
if (blockStatement == null)
return;
bool insertOrUpdate = false;
foreach (var s in blockStatement.Statements)
{
if (IsInsertOrUpdateStatement(s))
{
insertOrUpdate = true;
}
var selectStatement = s as ProcedureSelectStatement;
if (insertOrUpdate && selectStatement != null) // SELECT statement after INSERT or UPDATE statement
{
if (IsReadIdentityStatement(selectStatement)) // SELECT DISTINCT @Id = SCOPE_IDENTITY()
continue;
foreach (var column in columns)
{
var setStatment = new ProcedureSetStatement(selectStatement, new TableRefColumn(column));
selectStatement.Set.Add(setStatment);
}
foreach (var property in properties) // not persistent : select expression AS 'persistent name'
{
ProcedureExpressionStatement literalExpression = new ProcedureExpressionStatement(selectStatement, ProcedureExpressionStatement.CreateLiteral(GetSelectExpression(property)));
var setStatment = new ProcedureSetStatement(selectStatement, literalExpression, (ProcedureExpressionStatement)null, new ProcedureExpressionStatement(selectStatement, property.PersistenceName));
selectStatement.Set.Add(setStatment);
}
}
}
});
}
