private void AddEntityInsertStatement(IChild child, EntityInfo childInfo, DateTime timeStamp)
{
var builder = new ComplexCommandBuilder();
var statement = new InsertStatement(childInfo.Name);
foreach (var element in childInfo.Elements)
{
var value = element.Name == "TimeStamp" ? timeStamp : element.GetValue(child);
var parameter = value.ToParameter();
statement.Add(element.Name, parameter.Name);
builder.AddParameter(parameter);
}
foreach (var dataType in childInfo.DataTypes)
{
foreach (var element in dataType.Elements)
{
var value = dataType.GetValue(element, child);
var parameter = value.ToParameter();
statement.Add(element.Name, parameter.Name);
builder.AddParameter(parameter);
}
}
builder.AddStatement(statement);
Add(builder);
AddSaveStatements(child, childInfo, timeStamp);
child.Save(timeStamp);
}
public void Insert_Statement_With_Multiple_Value_Sets()
{
// Exercise
InsertStatement statement = ParserFactory.Execute <InsertStatement>(@"
insert into table values (1, 'A'), (2, 'B'), (3, 'C')"
).First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("table", statement.TableName);
Assert.AreEqual(3, statement.Values.Count);
var expected = new[]
{
new { Id = "1", Name = "'A'" },
new { Id = "2", Name = "'B'" },
new { Id = "3", Name = "'C'" }
};
Assert.AreEqual(expected.Length, statement.Values.Count);
for (int index = 0; index < expected.Length; index++)
{
List <string> row = statement.Values[index];
Assert.AreEqual(expected[index].Id, row[0]);
Assert.AreEqual(expected[index].Name, row[1]);
}
}
private List <TableParsingResult> ExtractTablesFromInsertStatement(InsertStatement insertStatement, Dictionary <string, List <TableParsingResult> > cteModel)
{
List <TableParsingResult> result = new List <TableParsingResult>();
if (insertStatement.InsertSpecification.InsertSource != null && insertStatement.InsertSpecification.InsertSource is SelectInsertSource selectInsertSource) //selectinsertsource
{
if (selectInsertSource.Select is QuerySpecification selectQuerySpecification)
{
var items = ExtractTablesFromQuerySpecification(selectQuerySpecification); //ExtractTablesUsedInFromClause(selectQuerySpecification.FromClause);
if (cteModel.Count > 0)
{
foreach (var cte in cteModel)
{
var item = items.Find(x => x.TableName == cte.Key);
if (item != null)
{
items.Remove(item);
foreach (var table in cte.Value)
{
items.AddIfNotExists(table.TableName, table.OperationType, table.Alias);
}
}
}
}
result.AddIfNotExists(items);
}
else if (selectInsertSource.Select is BinaryQueryExpression binaryQueryExpression)
{
var items = ExtractTablesFromBinaryQueryExpression(binaryQueryExpression);
result.AddIfNotExists(items);
}
}
return(result);
}
private void AnalyzeInsertStatement(InsertStatement insertStatement, ParserResults results)
{
insertStatement.PrintTSqlStatementBlockToDebugConsole();
List <TableParsingResult> temp = new List <TableParsingResult>();
Dictionary <string, List <TableParsingResult> > cteModel = new Dictionary <string, List <TableParsingResult> >();
if (insertStatement.WithCtesAndXmlNamespaces?.CommonTableExpressions.Count > 0)
{
foreach (CommonTableExpression cte in insertStatement.WithCtesAndXmlNamespaces.CommonTableExpressions)
{
AnalyzeCommonTableExpression(cteModel, cte);
}
}
if (insertStatement.InsertSpecification.Target is NamedTableReference insertNamedTableReference)
{
string tableName = ExtraceTableNameFromNamedTableRefernce(insertNamedTableReference, out string alias);
temp.AddIfNotExists(tableName, SqlOperationType.INSERT, alias);
var items = ExtractTablesFromInsertStatement(insertStatement, cteModel);
temp.AddIfNotExists(items);
}
else if (insertStatement.InsertSpecification.Target is VariableTableReference variableTableReference)
{
temp.AddIfNotExists(variableTableReference.Variable.Name, SqlOperationType.INSERT, null);
var items = ExtractTablesFromInsertStatement(insertStatement, cteModel);
temp.AddIfNotExists(items);
}
insertStatement.PrintTablesToDebugConsole(temp);
results.AddIfNotExists(temp);
}
public void Process(InsertStatement Fragment)
{
if (Fragment.InsertSpecification.Columns.Count == 0)
{
_smells.SendFeedBack(12, Fragment);
}
switch (FragmentTypeParser.GetFragmentType(Fragment.InsertSpecification.InsertSource))
{
case "SelectInsertSource":
var InsSource = (SelectInsertSource)Fragment.InsertSpecification.InsertSource;
WithCtesAndXmlNamespaces Cte = Fragment.WithCtesAndXmlNamespaces;
_smells.ProcessQueryExpression(InsSource.Select, "RG", false, Cte);
if (Cte != null)
{
ProcessWithCtesAndXmlNamespaces(Cte);
}
break;
case "ExecuteInsertSource":
var ExecSource = (ExecuteInsertSource)Fragment.InsertSpecification.InsertSource;
//ProcessExecuteSpecification(ExecSource.Execute);
ExecutableEntity ExecutableEntity = ExecSource.Execute.ExecutableEntity;
_smells.ExecutableEntityProcessor.ProcessExecutableEntity(ExecutableEntity);
break;
}
}
public override void ExplicitVisit(InsertStatement node)
{
if (node.InsertSpecification.Columns.Count == 0)
{
this.InvalidInsertStatementsWithoutColumnNames.Add(node);
}
}
private Statement Create(XElement statementNode)
{
Enum.TryParse <StatementType>(statementNode.Name.LocalName, out var statementType);
Statement statement = null;
switch (statementType)
{
case StatementType.Insert:
statement = new InsertStatement();
break;
case StatementType.Select:
statement = new SelectStatement();
break;
case StatementType.Update:
statement = new UpdateStatement();
break;
case StatementType.Delete:
statement = new DeleteStatement();
break;
case StatementType.Statement:
default:
statement = new Statement();
break;
}
statement.Id = statementNode.Attribute("Id").Value;
statement.SqlTags = new List <ITag>();
return(statement);
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="context"></param>
/// <param name="statement"></param>
/// <param name="variable"></param>
/// <param name="explanation"></param>
public InsertInListChange(InterpretationContext context, InsertStatement statement, IVariable variable, ExplanationPart explanation)
: base(variable, null, null)
{
Context = context;
Statement = statement;
Explanation = explanation;
}
/// <summary>
/// Renders INSERT statement and code that retrieves the new ID.
/// </summary>
/// <param name="insert">INSERT statement that is being rendered.</param>
/// <param name="nextSequence">Ignored. SQL Server CE doesn't use sequences.</param>
/// <param name="dbms">Target DBMS.</param>
/// <param name="output">StringBuilder to which the SQL code is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
/// <returns><b>null</b> because automatically generated ID must be fetched via SELECT after INSERT.</returns>
public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Renders INSERT statements for DBMSs that support an auto-identity fields.
// Auto-id field may or may not be in the column-value list.
// If auto-incremented field is in the column-value list it will be skipped.
// Table may have only one auto-identity field.
// Method expects that all errors have been identified and processed in the caller.
// Renders all fields except auto-id field; thus -1 if auto-id is contained in the column-value list.
int numberOfFieldsToRender = GetTotalNumberOfFieldsToRender(insert);
AppendInsertIntoTableName(insert, dbms, output);
if (numberOfFieldsToRender > 0)
{
AppendBracketsWithAllFieldsExceptAutoId(insert, dbms, output, numberOfFieldsToRender);
AppendValuesForAllFieldsExceptAutoId(insert, dbms, output, parameters, numberOfFieldsToRender);
}
else
{
AppendDefaultValuesExpression(output);
}
// Auto ID must be fetched via SELECT after INSERT.
DbParameter autoId = null;
return autoId;
}
public void ProcessInsertStatement(InsertStatement insStmt)
{
String TargetType = GetFragmentType(insStmt.InsertSpecification.Target);
switch (TargetType)
{
case "NamedTableReference":
var NTR = (NamedTableReference)insStmt.InsertSpecification.Target;
var TargetObject = (NTR.SchemaObject.DatabaseIdentifier == null ? this.databaseName : NTR.SchemaObject.DatabaseIdentifier.Value) + "." +
(NTR.SchemaObject.SchemaIdentifier == null ? this.schemaName : NTR.SchemaObject.SchemaIdentifier.Value) + "." +
(NTR.SchemaObject.BaseIdentifier == null ? "" : NTR.SchemaObject.BaseIdentifier.Value);
addTargettoCurrentObject(TargetObject);
break;
}
if (insStmt.WithCtesAndXmlNamespaces != null)
{
ProcessWithCtesAndXmlNamespaces(insStmt.WithCtesAndXmlNamespaces);
}
String insType = GetFragmentType(insStmt.InsertSpecification.InsertSource);
switch (insType)
{
default:
break;
}
}
public override object VisitInsert_stmt([NotNull] SqlParser.Insert_stmtContext context)
{
var statement = new InsertStatement();
var columnNames = context.column_name();
if (columnNames != null && columnNames.Length > 0)
{
statement.Columns = new List <string>();
foreach (var item in columnNames)
{
statement.Columns.Add(item.GetText());
}
}
if (context.qualified_table_name() != null)
{
statement.Table = (TableName)VisitQualified_table_name(context.qualified_table_name());
}
if (context.K_VALUES() != null && context.K_DEFAULT() == null)
{
statement.Values = new List <IList <IExpression> >();
foreach (var vl in context.values_list())
{
var valueList = new List <IExpression>();
foreach (var val in vl.expr())
{
valueList.Add((IExpression)VisitExpr(val));
}
statement.Values.Add(valueList);
}
}
return(statement);
}
ParameterValue[] ModifyXPObjectTypeDataCore(SimpleDataLayer dataLayer, InsertStatement insertStatement) {
if (TypeExists(dataLayer, insertStatement)) return null;
if (!IsMainLayer(dataLayer.Connection)) {
_xpoObjectHacker.CreateObjectTypeIndetifier(insertStatement,_dataStoreManager.SimpleDataLayers[DataStoreManager.STR_Default]);
}
return dataLayer.ModifyData(insertStatement).Identities;
}
public static InsertStatementModel <T> Generate <T>(InsertStatement <T> statement, bool isFull = false)
{
var table = TypeHelper.GetType <Attributes.Table>(statement.Type);
// dont take keys fields
//IEnumerable<PropertyInfo> fields = null;
var autoFields = TypeHelper.GetPropertiesForAttribute <Attributes.Field>(table)
.Where(f => f.GetCustomAttribute <Attributes.AutoSequence>() != null);
var fields = TypeHelper.GetPropertiesForAttribute <Attributes.Field>(table)
.Where(f => f.GetCustomAttribute <Attributes.AutoSequence>() == null);
var tableName = TypeHelper.GetAttribute <Attributes.Table>(table).Name;
var schemaAtr = TypeHelper.GetAttribute <Attributes.Schema>(table);
var schemaName = schemaAtr != null ? schemaAtr.Name : null;
return(new InsertStatementModel <T>(CeqlConfiguration.Instance.GetConnectorFormatter())
{
Fields = fields,
AutoFields = autoFields,
TableName = tableName,
SchemaName = schemaName,
IsFull = isFull
});
}
protected override async Task <Func <object> > DoInsertAsync(string executableSql, Dict executableParams, bool ignoreIfDuplicate)
{
InsertStatement statement = new InsertStatement(this.database, executableSql);
bool hasAutoIncrement = statement.StatementFromRefs[0].table.AutoIncrementFieldName != null;
string newExecutableSql = hasAutoIncrement ? $"{executableSql} RETURNING {statement.StatementFromRefs[0].table.AutoIncrementFieldName}" : executableSql;
var result = await ExecuteCommandAsync <int>(async c =>
{
if (!hasAutoIncrement)
{
return(await c.ExecuteNonQueryAsync());
}
var id = 0;
using (var reader = await c.ExecuteReaderAsync())
{
while (reader.Read())
{
int.TryParse(reader[0].ToString(), out id);
}
}
return(id);
}, newExecutableSql, executableParams);
return(() => result);
}
private static void AppendInsertFromColumns(InsertStatement insert, StringBuilder builder)
{
// Columns list
builder.AppendLine(Indentation + "(");
builder.AppendLine(
DoubleIndentation +
string.Join(
SeparatorNoSpace + Environment.NewLine + DoubleIndentation,
insert.ColumnsList));
builder.AppendLine(Indentation + ")");
// Rows
var rowCount = insert.RowCount.GetValueOrDefault(1);
builder.AppendLine("VALUES");
for (int i = 0; i < rowCount; i++)
{
builder.AppendLine(Indentation + "(");
builder.AppendLine(
DoubleIndentation +
string.Join(
SeparatorNoSpace + Environment.NewLine + DoubleIndentation,
insert.ColumnsList.Select(x =>
"@" +
x +
(rowCount == 1 ? string.Empty : i.ToString()))));
builder.Append(Indentation + ")");
if (i < rowCount - 1)
{
builder.AppendLine(",");
}
}
}
public override void ExplicitVisit(InsertStatement node)
{
if (node.InsertSpecification.Columns.Any())
{
return;
}
var source = node.InsertSpecification.InsertSource as ValuesInsertSource;
if (source != null && source.IsDefaultValues)
{
return;
}
if (TolerateTempTables)
{
var target = node.InsertSpecification.Target as NamedTableReference;
if (target != null && !target.SchemaObject.BaseIdentifier.Value.StartsWith("#"))
{
HasInsertWithoutColumnSpecification = true;
}
}
else
{
HasInsertWithoutColumnSpecification = true;
}
}
public void ExecuteTest_Select_Schema_Alias()
{
Table table = new Table {
Name = "EMPLOYEES", Alias = new Alias(null)
{
Name = null
}, Schema = "HR"
};
Table table2 = new Table {
Name = "JOBS", Alias = new Alias(null)
{
Name = "JB", Type = AliasType.As
}, Schema = "HR"
};
string sql = string.Format("INSERT INTO {0}.{1} (id, col2) SELECT id1, id2 FROM {2}.{3} AS {4}", table.Schema, table.Name, table2.Schema, table2.Name, table2.Alias.Name);
IList <IStatement> statements = ParserFactory.Execute(sql);
Assert.AreEqual(1, statements.Count);
IStatement actualStatement = statements[0];
InsertStatement actual = actualStatement as InsertStatement;
Assert.IsNotNull(actual);
Assert.AreEqual(2, actual.Tables.Count);
Assert.IsTrue(AreTablesEqual(table, actual.Tables[0]));
Assert.IsTrue(AreTablesEqual(table2, actual.Tables[1]));
}
public static IList <FieldPairReference> GetFieldPairs(
this InsertStatement insertStatement,
ILogger logger,
SchemaFile file
)
{
// TODO : make use of cteReferences below? or would that mean the scope can get polluted?
var ctePairs = insertStatement
.WithCtesAndXmlNamespaces
?.CommonTableExpressions
.GetFieldPairReferences(logger, file)
.ToList()
?? new List <FieldPairReference>();
var cteReferences = insertStatement
.WithCtesAndXmlNamespaces
?.CommonTableExpressions
.GetSchemaObjectReferences(logger, file)
.ToList()
?? new List <SchemaObjectReference>();
using (new StatementContext(file.FileContext, cteReferences))
{
var insertSpecificationPairs = insertStatement
.InsertSpecification
.GetFieldPairReferences(logger, file)
.ToList();
return(ctePairs
.Concat(insertSpecificationPairs)
.ToList());
}
}
private void AddEntityInsertStatement(IEntity entity, EntityInfo entityInfo, DateTime timeStamp)
{
var builder = new ComplexCommandBuilder();
var statement = new InsertStatement(entityInfo.Name);
foreach (var element in entityInfo.Elements)
{
var value = element.IsTimeStamp ? timeStamp : element.GetValue(entity);
var parameter = value.ToParameter();
statement.Add(element.Name, parameter.Name);
builder.AddParameter(parameter);
}
foreach (var dataType in entityInfo.DataTypes)
{
foreach (var element in dataType.Elements)
{
var value = dataType.GetValue(element, entity);
var parameter = value.ToParameter();
statement.Add(element.Name, parameter.Name);
builder.AddParameter(parameter);
}
}
builder.AddStatement(statement);
Add(builder);
AddSaveStatements(entity, entityInfo, timeStamp);
entity.Save(timeStamp);
}
protected override Task <Func <object> > DoInsertAsync(string executableSql, Dict executableParams, bool ignoreIfDuplicate)
{
InsertStatement executableStatement = new InsertStatement(this.database, executableSql);
var memoryTable = executableStatement.StatementFromRefs[0].table as MemoryTable;
var insertRefs = executableStatement.GetInsertRefs(executableParams);
var fieldValues = BaseStatement.RemapStatementParamsToFieldValues(executableParams, insertRefs);
var dataRow = memoryTable.DataTable.NewRow();
foreach (var nameValuePair in fieldValues)
{
dataRow[nameValuePair.Key] = nameValuePair.Value;
}
memoryTable.DataTable.Rows.Add(dataRow);
this.changedTables.Add(memoryTable);
if (memoryTable.AutoIncrementFieldName == null)
{
return(Task.FromResult <Func <object> >(null));
}
else
{
return(Task.FromResult <Func <object> >(() => dataRow[memoryTable.AutoIncrementFieldName]));
}
}
public void When_inserting_from_select_Then_builds_properly()
{
var select = SqlStatements.Select("Name", "EmailAddress")
.From("Users")
.InnerJoin("Teams", "Users.TeamID = Teams.ID")
.Where("Teams.IsOld = FALSE");
var statement = new InsertStatement(select)
.Into("Users");
var sql = statement.ToSql();
Assert.That(
sql,
SqlCompareConstraint.EqualTo(@"INSERT INTO Users
(
Name,
EmailAddress
)
SELECT
Name, EmailAddress
FROM
Users
INNER JOIN Teams ON Users.TeamID = Teams.ID
WHERE
Teams.IsOld = FALSE"));
}
public override void ExplicitVisit(InsertStatement node)
{
TableNameIllegalCandidate candidate = CreateAndPushCandidate();
base.ExplicitVisit(node);
PopAndSetIllegalStatement(candidate);
}
private InsertStatement CreateStatement(string tableName, string identifier = "INSERTED")
{
var statement = new InsertStatement()
{
InsertSpecification = new InsertSpecification()
};
var schemaObjectName = new SchemaObjectName();
schemaObjectName.Identifiers.Add(new Identifier()
{
Value = tableName
});
statement.InsertSpecification.Target = new NamedTableReference()
{
SchemaObject = schemaObjectName
};
statement.InsertSpecification.OutputClause = new OutputClause();
statement.InsertSpecification.OutputClause.SelectColumns.Add(new SelectStarExpression()
{
Qualifier = new MultiPartIdentifier()
});
((SelectStarExpression)statement.InsertSpecification.OutputClause.SelectColumns.Single()).Qualifier.Identifiers.Add(new Identifier()
{
Value = identifier
});
return(statement);
}
protected override void VisitInsert(InsertStatement statement)
{
VisitCommonTableExpressions(statement.CommonTableExpressions, true);
State.Write(Symbols.INSERT);
if (statement.Conflict.HasValue)
{
State.Write(Symbols.OR);
switch (statement.Conflict.Value)
{
case OnConflict.Abort:
State.Write(Symbols.ABORT);
break;
case OnConflict.Fail:
State.Write(Symbols.FAIL);
break;
case OnConflict.Ignore:
State.Write(Symbols.IGNORE);
break;
case OnConflict.Replace:
State.Write(Symbols.REPLACE);
break;
case OnConflict.Rollback:
State.Write(Symbols.ROLLBACK);
break;
}
}
State.Write(Symbols.INTO);
VisitNameToken(statement.Into);
VisitAliasedTokenSet(statement.Columns, Symbols.OpenParenthesis, Symbols.Comma, Symbols.CloseParenthesis);
if (statement.DefaultValues)
{
State.Write(Symbols.DEFAULT);
State.Write(Symbols.VALUES);
}
else if (statement.Values.Count > 0)
{
var separator = Symbols.VALUES;
foreach (var valuesSet in statement.Values)
{
State.Write(separator);
separator = Symbols.Comma;
VisitTokenSetInParenthesis(valuesSet);
}
}
else if (statement.From != null)
{
VisitStatement(statement.From);
}
}
public override void BuildGetIdentity(InsertStatement insertStatement)
{
BuildCode(Code_Select);
BuildCode(Code_Space);
BuildCode(Code_Global_Parameter_Identity);
}
public override object VisitInsertStatement([NotNull] MiniSQLParser.InsertStatementContext context)
{
InsertStatement obj = new InsertStatement();
obj.TableName = context.tableRef().GetText();
obj.Values = (List <AtomValue>)Visit(context.insertValues());
return(obj);
}
bool TypeExists(DataStoreManagerSimpleDataLayer dataLayer, InsertStatement stm1) {
if (dataLayer.IsMainLayer)
return false;
var session = new Session(dataLayer) { IdentityMapBehavior = IdentityMapBehavior.Strong };
var value = stm1.Parameters.ToList()[0].Value as string;
var xpObjectType = session.FindObject<XPObjectType>(type => type.TypeName == value);
return xpObjectType != null;
}
protected override void VisitValues(IExpressionVisitor visitor, InsertStatement statement, ICollection <IExpression> values, int rounds)
{
//生成OUTPUT(RETURNING)子句
this.VisitOutput(visitor, statement.Returning);
//调用基类同名方法
base.VisitValues(visitor, statement, values, rounds);
}
public override void Visit(InsertStatement node)
{
Inserts++;
var targetName = (node.InsertSpecification.Target as NamedTableReference)?.
SchemaObject.BaseIdentifier.Value ??
(node.InsertSpecification.Target as VariableTableReference)?.Variable.Name;
InsertTables.Add(targetName);
}
public ParameterValue CreateObjectTypeIndetifier(InsertStatement insertStatement, SimpleDataLayer simpleDataLayer,int value) {
var identityParameter = insertStatement.IdentityParameter;
insertStatement.IdentityParameter = null;
insertStatement.Parameters.Add(new ParameterValue(3) { Value = value });
var oidQueryOperand = insertStatement.Operands.OfType<QueryOperand>().Where(operand => operand.ColumnName == "Oid").FirstOrDefault();
if (ReferenceEquals(oidQueryOperand,null))
insertStatement.Operands.Add(new QueryOperand { ColumnName = "Oid", ColumnType = DBColumnType.Int32 });
return identityParameter;
}
private static bool IsAutoIdFieldInColumnValueList(InsertStatement insert)
{
foreach (InsertExpression colValPair in insert.ColumnsAndValues)
{
if (colValPair.Column.AutoIncrement)
return true;
}
return false;
}
string GetInsertMessage(InsertStatement insertStatement, List <QueryOperand> queryOperands)
{
string s = "";
for (int i = 0; i < queryOperands.Count; i++)
{
s += queryOperands[i].ColumnName + "=" + insertStatement.Parameters[i].Value + " AND ";
}
s = s.TrimEnd(" AND ".ToCharArray());
return(s);
}
protected internal override Node VisitInsertStatement([NotNull] InsertStatement node)
{
this.Builder.Append((node.Upsert ? "UPSERT" : "INSERT") + $" INTO{this.NewLine()}{this.Indent()}");
this.Visit(node.Target);
this.Builder.Append($"{this.UnIndent()}{this.NewLine()}");
this.Visit(node.Select);
return(node);
}
public void ExecuteTest_Into_No_Table()
{
string sql = string.Format("INSERT INTO");
IList <IStatement> statements = ParserFactory.Execute(sql);
Assert.AreEqual(1, statements.Count);
IStatement actualStatement = statements[0];
InsertStatement actual = actualStatement as InsertStatement;
Assert.IsNotNull(actual);
Assert.AreEqual(0, actual.Tables.Count);
}
public static int ExecuteInsertBuild(this Database database, Table table, Action <InsertStatement> action, DbTransaction dbTransaction = null, int commandTimeout = CommandTimeout)
{
var builder = database.CreateSQLBuilder();
var insert = new InsertStatement(table);
action(insert);
builder.BuildInsertStatement(insert);
return(database.ExecuteNonQuery(builder.ToSQL(), dbTransaction, commandTimeout));
}
ModificationResult ModifyXPObjectTable(ModificationStatement[] dmlStatements, InsertStatement insertStatement, ModificationResult modificationResult) {
foreach (var simpleDataLayer in _dataStoreManager.SimpleDataLayers) {
var dataLayer = simpleDataLayer.Value;
if (!TypeExists(dataLayer, insertStatement)) {
if (!IsMainLayer(dataLayer.Connection)) {
_xpoObjectHacker.CreateObjectTypeIndetifier(insertStatement, _dataStoreManager.SimpleDataLayers[DataStoreManager.StrDefault]);
}
var modifyData = dataLayer.ModifyData(dmlStatements);
if (modifyData.Identities.Count() > 0)
modificationResult = modifyData;
}
}
return modificationResult;
}
public override void ExplicitVisit(InsertStatement node)
{
base.ExplicitVisit(node);
NewStatement.StartPos = node.StartOffset;
NewStatement.Length = node.FragmentLength;
var whyle = BuildWhileStatement(node.InsertSpecification);
var scriptGen = new Sql110ScriptGenerator();
string script;
scriptGen.GenerateScript(whyle, out script);
NewStatement.NewText = script;
}
ModificationResult ModifyXPObjectTable(ModificationStatement[] dmlStatements, InsertStatement insertStatement, ModificationResult modificationResult) {
foreach (var simpleDataLayer in _dataStoreManager.GetDataLayers(DataStore)) {
if (!simpleDataLayer.Value.IsLegacy) {
var dataLayer = simpleDataLayer.Value;
if (!TypeExists(dataLayer, insertStatement)) {
if (!dataLayer.IsMainLayer) {
_xpoObjectHacker.CreateObjectTypeIndetifier(insertStatement, _dataStoreManager.GetDataLayer(DataStoreManager.StrDefault,DataStore));
}
var modifyData = dataLayer.ModifyData(dmlStatements);
if (modifyData.Identities.Any())
modificationResult = modifyData;
}
}
}
return modificationResult;
}
/// <summary>
/// Renders INSERT statement and code that retrieves the new ID.
/// </summary>
/// <param name="insert">INSERT statement that is being rendered.</param>
/// <param name="nextSequence">Ignored. SQL Server doesn't use sequences.</param>
/// <param name="dbms">Target DBMS. Different auto-id retrieval for SQL 7.0 then in newer versions.</param>
/// <param name="output">StringBuilder to which the SQL code is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
/// <returns>Parameter that contains the ID of the inserted row. <b>null</b> if auto-id field is not used.</returns>
public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Renders INSERT statements for DBMSs that support an auto-identity fields.
// Auto-id field may or may not be in the column-value list.
// If auto-incremented field is in the column-value list it will be skipped.
// Table may have only one auto-identity field.
// Method expects that all errors have been identified and processed in the caller.
// Renders all fields except auto-id field; thus -1 if auto-id is contained in the column-value list.
int numberOfFieldsToRender = GetTotalNumberOfFieldsToRender(insert);
AppendInsertIntoTableName(insert, dbms, output);
if (numberOfFieldsToRender > 0)
{
AppendBracketsWithAllFieldsExceptAutoId(insert, dbms, output, numberOfFieldsToRender);
AppendValuesForAllFieldsExceptAutoId(insert, dbms, output, parameters, numberOfFieldsToRender);
}
else
{
AppendDefaultValuesExpression(output);
}
DbParameter autoId = null;
if (HasAutoIdField(insert.Table))
{
autoId = new DbParameter("___newAutoIdentity___", DbType.Int32) { Direction = ParameterDirection.Output };
if (dbms == DbmsType.SqlServer_7)
{
// @@IDENTITY
output.Append(" ; set ");
autoId.Render(dbms, output, parameters);
output.Append(" = @@IDENTITY");
}
else
{
// scope_identity()
output.Append(" ; set ");
autoId.Render(dbms, output, parameters);
output.Append(" = scope_identity()");
}
// BatchQuery (if ever implemented) reads this value to assign new ID value to InsertStatement.RetrievedData property.
//insert.NewIdOutputParameterIndexAfterRenderCompleted = parameters.Count - 1;
}
// Return auto-id DB parameter. Callers require it to retrieve the new ID value.
return autoId;
}
private static void AppendColumnListInBrackets(InsertStatement insert, DbmsType dbms, StringBuilder output, IDbColumn autoIdField, bool mustAppendAutoIdField)
{
output.Append(" (");
foreach (InsertExpression colValPair in insert.ColumnsAndValues)
{
colValPair.Column.RenderColumnName(dbms, output);
output.Append(", ");
}
if (mustAppendAutoIdField)
{
autoIdField.RenderColumnName(dbms, output);
output.Append(", ");
}
// Remove last ", " string.
output.Remove(output.Length - 2, 2);
output.Append(")");
}
public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Auto ID: sequence if it is defined, or NULL and hope that a trigger will take care of auto ID field.
IDbColumn autoIdField = GetAutoIdField(insert.Table);
bool hasAutoIdField = (autoIdField != null);
bool autoIdIsAlreadyInInsertList = IsAutoIdFieldInColumnValueList(insert);
bool mustAppendAutoIdField = (hasAutoIdField && !autoIdIsAlreadyInInsertList);
AppendInsertIntoTableName(insert, dbms, output);
AppendColumnListInBrackets(insert, dbms, output, autoIdField, mustAppendAutoIdField);
RenderValueListAndNextSequenceExpression(insert, dbms, output, parameters, autoIdField, mustAppendAutoIdField);
if (autoIdField != null && nextSequence != null)
{
// RETURNING id
output.Append(" RETURNING ");
autoIdField.RenderColumnName(dbms, output);
output.Append(" INTO ");
nextSequence.Render(dbms, output, parameters);
}
return nextSequence;
}
public void Process(InsertStatement Fragment)
{
if (Fragment.InsertSpecification.Columns.Count == 0)
{
_smells.SendFeedBack(12, Fragment);
}
switch (FragmentTypeParser.GetFragmentType(Fragment.InsertSpecification.InsertSource))
{
case "SelectInsertSource":
var InsSource = (SelectInsertSource) Fragment.InsertSpecification.InsertSource;
WithCtesAndXmlNamespaces Cte = Fragment.WithCtesAndXmlNamespaces;
_smells.ProcessQueryExpression(InsSource.Select, "RG", false, Cte);
if (Cte != null)
ProcessWithCtesAndXmlNamespaces(Cte);
break;
case "ExecuteInsertSource":
var ExecSource = (ExecuteInsertSource) Fragment.InsertSpecification.InsertSource;
//ProcessExecuteSpecification(ExecSource.Execute);
ExecutableEntity ExecutableEntity = ExecSource.Execute.ExecutableEntity;
_smells.ExecutableEntityProcessor.ProcessExecutableEntity(ExecutableEntity);
break;
}
}
/// <summary>Executes the INSERT command. Automatically generates the code that retrieves the new identity for
/// the supported databases. DBMS specific code depends on the DBMS property of the used ConnectionProvider.</summary>
/// <param name="insert">INSERT statement to execute.</param>
/// <param name="dbms">Target DBMS.</param>
/// <param name="conn">Connection-transaction context to use.</param>
/// <param name="lastExecutedCommandInfo">Output parameter: statistic for executed command.</param>
/// <returns>Automatically generated ID for inserted row, or <b>null</b> if ID is not automatically generated.</returns>
public object Execute(InsertStatement insert, DbmsType dbms, IConnectionProvider conn, out CommandExecutionStatistics lastExecutedCommandInfo, int commandTimeout = 30)
{
// Renderer and DBMS will compute next ID, insert row and retrieve ID in one trip.
StringBuilder cmdtxt = new StringBuilder();
DbParameterCollection parameters = new DbParameterCollection();
IDbColumn autoIdField = GetAutoIdField(insert.Table);
DbParameter newId = (autoIdField != null) ? new DbParameter("newAutoIdentity___", autoIdField.DbType) { Direction = ParameterDirection.Output } : null;
RenderInsert(insert, newId, dbms, cmdtxt, parameters);
string command = cmdtxt.ToString();
object id = null;
lastExecutedCommandInfo = new CommandExecutionStatistics(command);
DbUtil.ExecuteNonQuery(conn, command, parameters, CommandType.Text, commandTimeout);
if (newId != null)
{
if (newId.Value == DBNull.Value)
id = null;
else
id = newId.Value;
}
lastExecutedCommandInfo.StopTime();
return id;
}
public void ProcessInsertStatement(InsertStatement insStmt)
{
String TargetType = GetFragmentType(insStmt.InsertSpecification.Target);
switch(TargetType){
case "NamedTableReference":
var NTR =(NamedTableReference)insStmt.InsertSpecification.Target;
var TargetObject = (NTR.SchemaObject.DatabaseIdentifier == null ? this.databaseName : NTR.SchemaObject.DatabaseIdentifier.Value) + "." +
(NTR.SchemaObject.SchemaIdentifier == null ? this.schemaName : NTR.SchemaObject.SchemaIdentifier.Value) + "." +
(NTR.SchemaObject.BaseIdentifier == null ? "" : NTR.SchemaObject.BaseIdentifier.Value);
addTargettoCurrentObject(TargetObject);
break;
}
if(insStmt.WithCtesAndXmlNamespaces!=null)ProcessWithCtesAndXmlNamespaces(insStmt.WithCtesAndXmlNamespaces);
String insType = GetFragmentType(insStmt.InsertSpecification.InsertSource);
switch(insType){
default:
break;
}
}
public void InsertData()
{
Isolate.Fake.StaticMethods(typeof(UserLookAndFeel));
Isolate.WhenCalled(() => UserLookAndFeel.Default.ActiveSkinName).WillReturn("ActiveSkinName");
var instance = Isolate.Fake.Instance<FilterDataStoreModule>();
var statement = new InsertStatement {Operands = new CriteriaOperatorCollection(),Parameters = new QueryParameterCollection(new OperandValue("skinvalue"))};
statement.Operands.Add(new QueryOperand("Skin","Alias"));
Isolate.WhenCalled(() => instance.InsertData(null)).CallOriginal();
instance.InsertData(new List<InsertStatement> { statement });
Assert.AreEqual("ActiveSkinName", statement.Parameters[0].Value);
}
/// <summary>Executes the INSERT command. Automatically generates the code that retrieves the new identity for
/// the supported databases. DBMS specific code depends on the DBMS property of the used ConnectionProvider.</summary>
/// <param name="insert">INSERT statement to execute.</param>
/// <param name="dbms">Target DBMS.</param>
/// <param name="conn">Connection-transaction context to use.</param>
/// <param name="lastExecutedCommandInfo">Output parameter: statistic for executed command.</param>
/// <param name="cmdTimeout">Sets command timeout for SQL command insert statment.</param>
/// <returns>Automatically generated ID for inserted row, or <b>null</b> if ID is not automatically generated.</returns>
public object Execute(InsertStatement insert, DbmsType dbms, IConnectionProvider conn, out CommandExecutionStatistics lastExecutedCommandInfo, int cmdTimeout = 30)
{
// Renderer and DBMS will compute next ID, insert row and retrieve ID in one trip.
StringBuilder cmdtxt = new StringBuilder();
DbParameterCollection parameters = new DbParameterCollection();
DbParameter newId = RenderInsert(insert, null, dbms, cmdtxt, parameters);
string command = cmdtxt.ToString();
lastExecutedCommandInfo = new CommandExecutionStatistics(command);
DbUtil.ExecuteNonQuery(conn, command, parameters, CommandType.Text, cmdTimeout);
lastExecutedCommandInfo.StopTime();
object id;
if (newId != null && newId.Value != DBNull.Value && newId.Value != null)
id = newId.Value;
else
id = null;
return id;
}
private static void RenderValueListAndNextSequenceExpression(InsertStatement insert, DbmsType dbms, StringBuilder output, DbParameterCollection parameters, IDbColumn autoIdField, bool mustAppendAutoIdField)
{
output.Append(" VALUES (");
foreach (InsertExpression colValPair in insert.ColumnsAndValues)
{
IDbColumn currColumn = colValPair.Column;
if (currColumn.AutoIncrement)
{
AppendAutoIdFieldToValueList(output, currColumn);
}
else
{
IRenderSql itemToInsert = colValPair.ValueExpression;
itemToInsert.Render(dbms, output, parameters);
}
output.Append(", ");
}
if (mustAppendAutoIdField)
{
AppendAutoIdFieldToValueList(output, autoIdField);
output.Append(", ");
}
// Remove last ", " string.
output.Remove(output.Length - 2, 2);
output.Append(")");
}
public override void ExplicitVisit(InsertStatement node)
{
CommandStatistics.InsertsCount++;
base.ExplicitVisit(node);
}
public override void ExplicitVisit(InsertStatement fragment)
{
_fragments.Add(fragment);
}
private static void AppendBracketsWithAllFieldsExceptAutoId(InsertStatement insert, DbmsType dbms, StringBuilder output, int numberOfFieldsToRender)
{
output.Append(" (");
int fieldsProcessed = 0;
foreach (InsertExpression colValPair in insert.ColumnsAndValues)
{
// Skip auto-id field.
if (!colValPair.Column.AutoIncrement)
{
colValPair.Column.RenderColumnName(dbms, output);
fieldsProcessed++;
if (fieldsProcessed < numberOfFieldsToRender)
output.Append(", ");
}
}
output.Append(")");
}
private static void AppendValuesForAllFieldsExceptAutoId(InsertStatement insert, DbmsType dbms, StringBuilder output, DbParameterCollection parameters, int numberOfFieldsToRender)
{
output.Append(" VALUES (");
int valuesProcessed = 0;
foreach (InsertExpression colValPair in insert.ColumnsAndValues)
{
// Skip auto-id field.
if (!colValPair.Column.AutoIncrement)
{
IRenderSql currValueItem = colValPair.ValueExpression;
currValueItem.Render(dbms, output, parameters);
valuesProcessed++;
if (valuesProcessed < numberOfFieldsToRender)
output.Append(", ");
}
}
output.Append(")");
}
InsertStatement ParseInsertStatement()
{
InsertStatement stm = new InsertStatement();
ReadNextToken(); // skip 'insert'
SkipExpected("INTO"); // skip 'into'
stm.TableName = fCurrentToken.Value.TrimQuotation();
ReadNextToken();
if (fCurrentToken.Value == "(")
{
ReadNextToken();
stm.Fields = parseListString();
SkipExpected(")");
}
SkipExpected("VALUES");
while (!AtEndOfSource && fCurrentToken.Value == "(")
{
ReadNextToken();
List<Expression> values = new List<Expression>();
values.Add(ParseExpression());
while (fCurrentToken.Value == ",")
{
ReadNextToken();
values.Add(ParseExpression());
}
stm.Values.Add(values);
SkipExpected(")");
if (!AtEndOfSource && fCurrentToken.Value == ",")
ReadNextToken();
}
skipEmptyStatements();
return stm;
}
private static void AppendInsertIntoTableName(InsertStatement insert, DbmsType dbms, StringBuilder output)
{
output.Append("INSERT INTO ");
insert.Table.RenderTableName(dbms, output);
}
private static int GetTotalNumberOfFieldsToRender(InsertStatement insert)
{
bool autoIdFieldIsInColumnValueList = IsAutoIdFieldInColumnValueList(insert);
int numberOfFieldsToRender = (autoIdFieldIsInColumnValueList) ? insert.ColumnsAndValues.Count - 1 : insert.ColumnsAndValues.Count;
return numberOfFieldsToRender;
}
public void CreateObjectTypeIndetifier(InsertStatement insertStatement, SimpleDataLayer simpleDataLayer) {
var identityValue = FindIdentityValue(insertStatement.Parameters, simpleDataLayer);
CreateObjectTypeIndetifier(insertStatement, simpleDataLayer, identityValue);
}
public override void Visit(InsertStatement node) { this.action(node); }
/// <summary>Executes the INSERT command. Automatically generates the code that retrieves the new identity for
/// the supported databases. DBMS specific code depends on the DBMS property of the used ConnectionProvider.</summary>
/// <param name="insert">INSERT statement to execute.</param>
/// <param name="dbms">Target DBMS.</param>
/// <param name="conn">Connection-transaction context to use.</param>
/// <param name="lastExecutedCommandInfo">Output parameter: statistic for executed command.</param>
/// <param name="cmdTimeout">Timeout for the execution.</param>
/// <returns>Automatically generated ID for inserted row, or <b>null</b> if ID is not automatically generated.</returns>
public object Execute(InsertStatement insert, DbmsType dbms, IConnectionProvider conn, out CommandExecutionStatistics lastExecutedCommandInfo, int cmdTimeout = 30)
{
StringBuilder cmdtxt = new StringBuilder();
DbParameterCollection parameters = new DbParameterCollection();
RenderInsert(insert, null, dbms, cmdtxt, parameters);
string command = cmdtxt.ToString();
lastExecutedCommandInfo = new CommandExecutionStatistics(command);
object id = null;
if (HasAutoIdField(insert.Table))
id = InsertAndSelectAutoNumber(conn, command, parameters, cmdTimeout);
else
Insert(conn, command, parameters, cmdTimeout);
lastExecutedCommandInfo.StopTime();
return id;
}
public override void ExplicitVisit(InsertStatement node)
{
InsertStatements.Add(node);
base.ExplicitVisit(node);
}
