public void CanMigrateTableWithIdentityData()
{
// Arrange
var create = new CreateTableExpression
{
TableName = "Foo"
,
Columns = new List<ColumnDefinition> { new ColumnDefinition { Name = "Id", Type = DbType.Int32, IsIdentity = true} }
};
var context = GetDefaultContext();
var insert = new InsertDataExpression()
{
TableName = "Foo"
};
insert.Rows.Add(new InsertionDataDefinition { new KeyValuePair<string, object>("Id", 1)});
insert.Rows.Add(new InsertionDataDefinition { new KeyValuePair<string, object>("Id", 100) });
insert.WithIdentity = true;
// Act
MigrateTable(context, create, insert);
// Assert
context.MigrationIndex.ShouldBe(1);
}
public void UpdateVersionInfo(long version, string description)
{
var dataExpression = new InsertDataExpression();
dataExpression.Rows.Add(CreateVersionInfoInsertionData(version, description));
dataExpression.TableName = VersionTableMetaData.TableName;
dataExpression.SchemaName = VersionTableMetaData.SchemaName;
dataExpression.ExecuteWith(Processor);
}
public IMigrationExpression Reverse()
{
var expression = new InsertDataExpression
{
SchemaName = SchemaName,
TableName = TableName
};
expression.Rows.AddRange(Rows);
return(expression);
}
public IMigrationExpression Reverse()
{
var expression = new InsertDataExpression
{
SchemaName = SchemaName,
TableName = TableName
};
expression.Rows.AddRange(Rows);
return expression;
}
public void ExplicitUnicodeStringIgnoredForNonSqlServer()
{
var expression = new InsertDataExpression {TableName = "TestTable"};
expression.Rows.Add(new InsertionDataDefinition
{
new KeyValuePair<string, object>("NormalString", "Just'in"),
new KeyValuePair<string, object>("UnicodeString", new ExplicitUnicodeString("codethinked'.com"))
});
var result = Generator.Generate(expression);
result.ShouldBe("INSERT INTO \"public\".\"TestTable\" (\"NormalString\",\"UnicodeString\") VALUES ('Just''in','codethinked''.com');");
}
public void CanInsertAtAtIdentity()
{
var expression = new InsertDataExpression {TableName = "TestTable"};
expression.Rows.Add(new InsertionDataDefinition
{
new KeyValuePair<string, object>("Id", 1),
new KeyValuePair<string, object>("Name", RawSql.Insert("@@IDENTITY")),
new KeyValuePair<string, object>("Website", "codethinked.com")
});
var result = Generator.Generate(expression);
result.ShouldBe("INSERT INTO [dbo].[TestTable] ([Id], [Name], [Website]) VALUES (1, @@IDENTITY, 'codethinked.com')");
}
public void ExplicitUnicodeQuotesCorrectly()
{
var expression = new InsertDataExpression {TableName = "TestTable"};
expression.Rows.Add(new InsertionDataDefinition
{
new KeyValuePair<string, object>("UnicodeStringValue", new ExplicitUnicodeString("UnicodeString")),
new KeyValuePair<string, object>("StringValue", "AnsiiString")
});
var result = Generator.Generate(expression);
result.ShouldBe("INSERT INTO [dbo].[TestTable] ([UnicodeStringValue], [StringValue]) VALUES (N'UnicodeString', 'AnsiiString')");
}
public override string Generate(InsertDataExpression expression)
{
var insertStrings = new List<string>();
foreach (IDataDefinition row in expression.Rows)
{
IEnumerable<IDataValue> dataValues = evaluator.Evaluate(row).ToArray();
string columns = String.Join(", ", dataValues.Select(dataValue => Quoter.QuoteColumnName(dataValue.ColumnName)).ToArray());
string values = String.Join(", ", dataValues.Select(dataValue => Quoter.QuoteDataValue(dataValue)).ToArray());
insertStrings.Add(String.Format(InsertData, Quoter.QuoteTableName(expression.TableName), columns, values, CommandDelimiter));
}
return "INSERT ALL " + String.Join(" ", insertStrings.ToArray()) + " SELECT 1 FROM DUAL";
}
public IMigrationExpression Reverse()
{
var expression = new InsertDataExpression
{
SchemaName = SchemaName,
TableName = TableName
};
foreach (var row in Rows)
{
var dataDefinition = new InsertionDataDefinition();
dataDefinition.AddRange(row);
expression.Rows.Add(dataDefinition);
}
return(expression);
}
public IMigrationExpression Reverse()
{
var expression = new InsertDataExpression
{
SchemaName = SchemaName,
TableName = TableName
};
foreach (var row in Rows)
{
var dataDefinition = new InsertionDataDefinition();
dataDefinition.AddRange(row);
expression.Rows.Add(dataDefinition);
}
return expression;
}
public override string Generate(InsertDataExpression expression)
{
var columnNames = new List<string>();
var columnValues = new List<string>();
var insertStrings = new List<string>();
foreach (InsertionDataDefinition row in expression.Rows)
{
columnNames.Clear();
columnValues.Clear();
foreach (KeyValuePair<string, object> item in row)
{
columnNames.Add(Quoter.QuoteColumnName(item.Key));
columnValues.Add(Quoter.QuoteValue(item.Value));
}
string columns = String.Join(", ", columnNames.ToArray());
string values = String.Join(", ", columnValues.ToArray());
insertStrings.Add(String.Format(InsertData, Quoter.QuoteTableName(expression.TableName), columns, values));
}
return "INSERT ALL " + String.Join(" ", insertStrings.ToArray()) + " SELECT 1 FROM DUAL";
}
public void CanInsertBinaryData()
{
var expression = new InsertDataExpression
{
TableName = "TestTable1"
};
var row1 = new { Id = 1, Name = "Just'in", Value = Encoding.ASCII.GetBytes("Just'in") };
var row2 = new { Id = 1, Name = "Na\te", Value = Encoding.ASCII.GetBytes("Na\te") };
expression.Rows.AddRange(new IDataDefinition[]
{
new ReflectedDataDefinition(row1),
new ReflectedDataDefinition(row2)
}
);
var sql = generator.Generate(expression);
var expected = "INSERT INTO `TestTable1` (`Id`, `Name`, `Value`) VALUES (1, 'Just''in', UNHEX('4A75737427696E'));";
expected += "\r\nINSERT INTO `TestTable1` (`Id`, `Name`, `Value`) VALUES (1, 'Na e', UNHEX('4E610965'));";
sql.ShouldBe(expected);
}
public void UpdateVersionInfo(long version, Type type, TimeSpan elapsedTime)
{
var dataExpression = new InsertDataExpression();
dataExpression.Rows.Add( CreateVersionInfoInsertionData( version, type, elapsedTime ) );
dataExpression.TableName = VersionTableMetaData.TableName;
dataExpression.SchemaName = VersionTableMetaData.SchemaName;
dataExpression.ExecuteWith( Processor );
}
protected static bool IsUsingIdentityInsert(InsertDataExpression expression)
{
if (expression.AdditionalFeatures.ContainsKey(SqlServerExtensions.IdentityInsert))
{
return (bool)expression.AdditionalFeatures[SqlServerExtensions.IdentityInsert];
}
return false;
}
public virtual void Process(InsertDataExpression expression)
{
Process(Generator.Generate(expression));
}
public static InsertDataExpression GetInsertGUIDExpression()
{
var expression = new InsertDataExpression { TableName = TestTableName1 };
expression.Rows.Add(new InsertionDataDefinition { new KeyValuePair<string, object>("guid", TestGuid) });
return expression;
}
public override string Generate(InsertDataExpression expression)
{
if (IsUsingIdentityInsert(expression))
{
return string.Format("{0}; {1}; {2}",
string.Format(IdentityInsert, Quoter.QuoteTableName(expression.TableName), "ON"),
base.Generate(expression),
string.Format(IdentityInsert, Quoter.QuoteTableName(expression.TableName), "OFF"));
}
return base.Generate(expression);
}
public void IdentityCanInsertMultiple()
{
using (var table = new FirebirdTestTable(Processor, null, "bogus int"))
{
Processor.Process(new CreateColumnExpression
{
TableName = table.Name,
Column = { Name = "id", IsIdentity = true, Type = DbType.Int64, IsPrimaryKey = true }
});
var insert = new InsertDataExpression { TableName = table.Name };
var item = new Model.InsertionDataDefinition();
item.Add(new System.Collections.Generic.KeyValuePair<string, object>("BOGUS", 0));
insert.Rows.Add(item);
//Process 5 times = insert 5 times
Processor.Process(insert);
Processor.Process(insert);
Processor.Process(insert);
Processor.Process(insert);
Processor.Process(insert);
using (DataSet ds = ((DataSetContainer)Processor.ReadTableData(String.Empty, table.Name)).DataSet)
{
ds.Tables.Count.ShouldBe(1);
ds.Tables[0].Rows.Count.ShouldBe(5);
ds.Tables[0].Rows[0]["BOGUS"].ShouldBe(0);
ds.Tables[0].Rows[0]["id"].ShouldBe(1);
ds.Tables[0].Rows[1]["id"].ShouldBe(2);
ds.Tables[0].Rows[2]["id"].ShouldBe(3);
ds.Tables[0].Rows[3]["id"].ShouldBe(4);
ds.Tables[0].Rows[4]["id"].ShouldBe(5);
}
}
}
public static InsertDataExpression GetInsertDataExpression()
{
var expression = new InsertDataExpression();
expression.TableName = TestTableName1;
expression.Rows.Add(new InsertionDataDefinition
{
new KeyValuePair<string, object>("Id", 1),
new KeyValuePair<string, object>("Name", "Just'in"),
new KeyValuePair<string, object>("Website", "codethinked.com")
});
expression.Rows.Add(new InsertionDataDefinition
{
new KeyValuePair<string, object>("Id", 2),
new KeyValuePair<string, object>("Name", @"Na\te"),
new KeyValuePair<string, object>("Website", "kohari.org")
});
return expression;
}
public override string Generate(InsertDataExpression expression)
{
if (compatabilityMode == CompatabilityMode.STRICT)
{
List<string> unsupportedFeatures = expression.AdditionalFeatures.Keys.Where(x => !IsAdditionalFeatureSupported(x)).ToList();
if (unsupportedFeatures.Any())
{
string errorMessage =
string.Format("The following database specific additional features are not supported in strict mode [{0}]",
expression.AdditionalFeatures.Keys.Aggregate((x, y) => x + ", " + y));
return compatabilityMode.HandleCompatabilty(errorMessage);
}
}
List<string> columnNames = new List<string>();
List<string> columnValues = new List<string>();
List<string> insertStrings = new List<string>();
foreach (InsertionDataDefinition row in expression.Rows)
{
columnNames.Clear();
columnValues.Clear();
foreach (KeyValuePair<string, object> item in row)
{
columnNames.Add(Quoter.QuoteColumnName(item.Key));
columnValues.Add(Quoter.QuoteValue(item.Value));
}
string columns = String.Join(", ", columnNames.ToArray());
string values = String.Join(", ", columnValues.ToArray());
insertStrings.Add(String.Format(InsertData, Quoter.QuoteTableName(expression.TableName), columns, values));
}
return String.Join("; ", insertStrings.ToArray());
}
public override string Generate(InsertDataExpression expression)
{
var result = new StringBuilder();
foreach (InsertionDataDefinition row in expression.Rows)
{
List<string> columnNames = new List<string>();
List<object> columnData = new List<object>();
foreach (KeyValuePair<string, object> item in row)
{
columnNames.Add(item.Key);
columnData.Add(item.Value);
}
string columns = GetColumnList(columnNames);
string data = GetDataList(columnData);
result.Append(String.Format(" INTO {0} ({1}) VALUES ({2})", expression.TableName, columns, data));
}
return "INSERT ALL" + result.ToString() + " SELECT 1 FROM DUAL";
}
public override string Generate(InsertDataExpression expression)
{
var result = new StringBuilder();
foreach (var row in expression.Rows)
{
var columnNames = new List<string>();
var columnData = new List<object>();
foreach (var item in row)
{
columnNames.Add(item.Key);
columnData.Add(item.Value);
}
var columns = GetColumnList(columnNames);
var data = GetDataList(columnData);
result.Append(String.Format("INSERT INTO {0}.{1} ({2}) VALUES ({3});", Quoter.QuoteSchemaName(expression.SchemaName), Quoter.QuoteTableName(expression.TableName), columns, data));
}
return result.ToString();
}
public void CanInsertData()
{
var expression = new InsertDataExpression();
expression.TableName = "TestTable";
expression.Rows.Add(new InsertionDataDefinition
{
new KeyValuePair<string, object>("Id", 1),
new KeyValuePair<string, object>("Name", @"Just'in"),
new KeyValuePair<string, object>("Website", "codethinked.com")
});
expression.Rows.Add(new InsertionDataDefinition
{
new KeyValuePair<string, object>("Id", 2),
new KeyValuePair<string, object>("Name", @"Na\te"),
new KeyValuePair<string, object>("Website", "kohari.org")
});
var sql = generator.Generate(expression);
var expected = @"INSERT INTO `TestTable` (Id,Name,Website) VALUES (1,'Just''in','codethinked.com');";
expected += @"INSERT INTO `TestTable` (Id,Name,Website) VALUES (2,'Na\\te','kohari.org');";
sql.ShouldBe(expected);
}
public override string Generate(InsertDataExpression expression)
{
string errors = ValidateAdditionalFeatureCompatibility(expression.AdditionalFeatures);
if (!string.IsNullOrEmpty(errors)) return errors;
List<string> columnNames = new List<string>();
List<string> columnValues = new List<string>();
List<string> insertStrings = new List<string>();
foreach (InsertionDataDefinition row in expression.Rows)
{
columnNames.Clear();
columnValues.Clear();
foreach (KeyValuePair<string, object> item in row)
{
columnNames.Add(Quoter.QuoteColumnName(item.Key));
columnValues.Add(Quoter.QuoteValue(item.Value));
}
string columns = String.Join(", ", columnNames.ToArray());
string values = String.Join(", ", columnValues.ToArray());
insertStrings.Add(String.Format(InsertData, Quoter.QuoteTableName(expression.TableName), columns, values));
}
return String.Join("; ", insertStrings.ToArray());
}
public void CanInsertData()
{
var expression = new InsertDataExpression();
expression.TableName = "TestTable";
expression.Rows.Add(new ExplicitDataDefinition
(
new DataValue("Id", 1),
new DataValue("Name", "Just'in"),
new DataValue("Website", "codethinked.com")
));
expression.Rows.Add(new ExplicitDataDefinition
(
new DataValue("Id", 2),
new DataValue("Name", "Na\\te"),
new DataValue("Website", "kohari.org")
));
var sql = generator.Generate(expression);
var expected = "INSERT INTO \"public\".\"TestTable\" (\"Id\",\"Name\",\"Website\") VALUES (1,'Just''in','codethinked.com');";
expected += "INSERT INTO \"public\".\"TestTable\" (\"Id\",\"Name\",\"Website\") VALUES (2,'Na\\te','kohari.org');";
sql.ShouldBe(expected);
}
private void UpdateVersionInfoWithAppliedMigration(long version)
{
var dataExpression = new InsertDataExpression();
dataExpression.Rows.Add(CreateVersionInfoInsertionData(version));
dataExpression.TableName = _versionTableMetaData.TableName;
dataExpression.ExecuteWith(_migrationProcessor);
}
public void CanInsertGuidData()
{
var gid = Guid.NewGuid();
var expression = new InsertDataExpression { TableName = "TestTable" };
expression.Rows.Add(new ExplicitDataDefinition(new DataValue("guid", gid)));
var sql = generator.Generate(expression);
var expected = String.Format("INSERT INTO \"public\".\"TestTable\" (\"guid\") VALUES ('{0}');", gid);
sql.ShouldBe(expected);
}
public void Truncate(InsertDataExpression expression)
{
expression.TableName = Truncate(expression.TableName);
List<InsertionDataDefinition> insertions = new List<InsertionDataDefinition>();
foreach (InsertionDataDefinition insertion in expression.Rows)
{
InsertionDataDefinition newInsertion = new InsertionDataDefinition();
foreach (var data in insertion)
{
newInsertion.Add(new KeyValuePair<string, object>(Truncate(data.Key), data.Value));
}
insertions.Add(newInsertion);
}
expression.Rows.Clear();
expression.Rows.AddRange(insertions);
}
public override string Generate(InsertDataExpression expression)
{
truncator.Truncate(expression);
return base.Generate(expression);
}
public override string Generate(InsertDataExpression expression)
{
List<string> columnNames = new List<string>();
List<string> columnValues = new List<string>();
List<string> insertStrings = new List<string>();
if (IsUsingIdentityInsert(expression))
{
insertStrings.Add(string.Format(IdentityInsert,
Quoter.QuoteSchemaName(expression.SchemaName),
Quoter.QuoteTableName(expression.TableName),
"ON"));
}
foreach (InsertionDataDefinition row in expression.Rows)
{
columnNames.Clear();
columnValues.Clear();
foreach (KeyValuePair<string, object> item in row)
{
columnNames.Add(Quoter.QuoteColumnName(item.Key));
columnValues.Add(Quoter.QuoteValue(item.Value));
}
string columns = String.Join(", ", columnNames.ToArray());
string values = String.Join(", ", columnValues.ToArray());
insertStrings.Add(String.Format(InsertData
, Quoter.QuoteSchemaName(expression.SchemaName)
, Quoter.QuoteTableName(expression.TableName)
, columns
, values));
}
if (IsUsingIdentityInsert(expression))
{
insertStrings.Add(string.Format(IdentityInsert,
Quoter.QuoteSchemaName(expression.SchemaName),
Quoter.QuoteTableName(expression.TableName),
"OFF"));
}
return String.Join("; ", insertStrings.ToArray());
}
public void CanInsertGuidData()
{
var gid = Guid.NewGuid();
var expression = new InsertDataExpression { TableName = "TestTable" };
expression.Rows.Add(new InsertionDataDefinition { new KeyValuePair<string, object>("guid", gid) });
var sql = generator.Generate(expression);
var expected = String.Format("INSERT INTO `TestTable` (guid) VALUES ('{0}');", gid);
sql.ShouldBe(expected);
}
public abstract string Generate(InsertDataExpression expression);
