private void ExecuteCreateTable(SqlConnection connection, Table table)
{
var createTableQuery = SqlDdl.Create(table);
using (var command = connection.CreateCommand(createTableQuery))
command.ExecuteNonQuery();
}
public virtual void ReferentialConstraintTest()
{
var masterTable = schema.CreateTable(MasterTableName);
var masterId = CreatePrimaryKey(masterTable);
var slaveTable = schema.CreateTable(SlaveTableName);
var slaveId = CreatePrimaryKey(slaveTable);
var fk = slaveTable.CreateForeignKey("foreign_me");
fk.ReferencedTable = masterTable;
fk.ReferencedColumns.Add(masterId);
fk.Columns.Add(slaveId);
ExecuteNonQuery(SqlDdl.Create(masterTable));
ExecuteNonQuery(SqlDdl.Create(slaveTable));
var slaveTableRef = SqlDml.TableRef(slaveTable);
var slaveInsert = SqlDml.Insert(slaveTableRef);
slaveInsert.Values.Add(slaveTableRef[IdColumnName], 1);
AssertExceptionType(slaveInsert, SqlExceptionType.ReferentialConstraintViolation);
var masterTableRef = SqlDml.TableRef(masterTable);
var masterInsert = SqlDml.Insert(masterTableRef);
masterInsert.Values.Add(masterTableRef[IdColumnName], 1);
ExecuteNonQuery(masterInsert);
ExecuteNonQuery(slaveInsert);
var masterDelete = SqlDml.Delete(masterTableRef);
masterDelete.Where = masterTableRef[IdColumnName] == 1;
AssertExceptionType(masterDelete, SqlExceptionType.ReferentialConstraintViolation);
}
public void InsertAndSelectTest()
{
var schema = ExtractDefaultSchema();
EnsureTableNotExists(schema, TableName);
var table = schema.CreateTable(TableName);
var idColumnType = Driver.TypeMappings[typeof(int)].MapType();
var idColumn = table.CreateColumn(IdColumnName, idColumnType);
table.CreatePrimaryKey("PK_" + TableName, idColumn);
for (int columnIndex = 0; columnIndex < typeMappings.Length; columnIndex++)
{
var mapping = typeMappings[columnIndex];
var column = table.CreateColumn(GetColumnName(columnIndex), mapping.MapType());
column.IsNullable = true;
}
ExecuteNonQuery(SqlDdl.Create(table));
var tableRef = SqlDml.TableRef(table);
using (var insertCommand = Connection.CreateCommand()) {
var insertQuery = SqlDml.Insert(tableRef);
var idParameter = insertCommand.CreateParameter();
idParameter.DbType = DbType.Int32;
idParameter.ParameterName = IdParameterName;
insertCommand.Parameters.Add(idParameter);
insertQuery.Values.Add(tableRef[IdColumnName], SqlDml.ParameterRef(IdParameterName));
var parameters = new List <DbParameter>();
for (int columnIndex = 0; columnIndex < typeMappings.Length; columnIndex++)
{
var mapping = typeMappings[columnIndex];
var parameterName = GetParameterName(columnIndex);
SqlExpression parameterExpression = SqlDml.ParameterRef(parameterName);
if (mapping.ParameterCastRequired)
{
parameterExpression = SqlDml.Cast(parameterExpression, mapping.MapType());
}
insertQuery.Values.Add(tableRef[GetColumnName(columnIndex)], parameterExpression);
var parameter = insertCommand.CreateParameter();
parameter.ParameterName = parameterName;
parameters.Add(parameter);
insertCommand.Parameters.Add(parameter);
}
var insertQueryText = Driver.Compile(insertQuery).GetCommandText();
insertCommand.CommandText = insertQueryText;
for (int rowIndex = 0; rowIndex < testValues[0].Length; rowIndex++)
{
idParameter.Value = rowIndex;
for (int columnIndex = 0; columnIndex < typeMappings.Length; columnIndex++)
{
typeMappings[columnIndex].BindValue(parameters[columnIndex], testValues[columnIndex][rowIndex]);
}
insertCommand.ExecuteNonQuery();
}
}
var resultQuery = SqlDml.Select(tableRef);
resultQuery.Columns.Add(SqlDml.Asterisk);
resultQuery.OrderBy.Add(tableRef[IdColumnName]);
VerifyResults(Connection.CreateCommand(resultQuery));
}
public virtual void CheckConstraintTest()
{
var table = schema.CreateTable(CheckedTableName);
CreatePrimaryKey(table);
var valueColumn1 = table.CreateColumn("value1", Driver.TypeMappings[typeof(int)].MapType());
valueColumn1.IsNullable = true;
var valueColumn2 = table.CreateColumn("value2", Driver.TypeMappings[typeof(int)].MapType());
valueColumn2.IsNullable = false;
var tableRef = SqlDml.TableRef(table);
table.CreateCheckConstraint("check_me", tableRef[valueColumn1.Name] > 0);
ExecuteNonQuery(SqlDdl.Create(table));
// violation of NOT NULL constraint
var insert = SqlDml.Insert(tableRef);
insert.Values.Add(tableRef[IdColumnName], 1);
insert.Values.Add(tableRef[valueColumn1.Name], 1);
insert.Values.Add(tableRef[valueColumn2.Name], SqlDml.Null);
AssertExceptionType(insert, SqlExceptionType.CheckConstraintViolation);
// violation of CHECK constraint
insert = SqlDml.Insert(tableRef);
insert.Values.Add(tableRef[IdColumnName], 2);
insert.Values.Add(tableRef[valueColumn1.Name], 0);
insert.Values.Add(tableRef[valueColumn2.Name], 0);
AssertExceptionType(insert, SqlExceptionType.CheckConstraintViolation);
}
public override void CreateExpressionIndexTest()
{
// Creating index
var t = schema.Tables[TableName];
var i = t.CreateIndex(ExpressionIndexName);
var tr = SqlDml.TableRef(t);
i.CreateIndexColumn(tr["first"]);
i.CreateIndexColumn(tr["second"]);
i.CreateIndexColumn(SqlDml.Concat(tr["first"], " ", tr["second"]));
i.CreateIndexColumn(SqlDml.Concat(tr["second"], " ", tr["first"]));
ExecuteNonQuery(SqlDdl.Create(i));
// Extracting index and checking its properties
var c2 = ExtractCatalog();
var s2 = c2.DefaultSchema;
var t2 = s2.Tables[TableName];
var i2 = t2.Indexes[ExpressionIndexName];
Assert.IsNotNull(i2);
Assert.AreEqual(4, i2.Columns.Count);
Assert.IsTrue(!i2.Columns[2].Expression.IsNullReference());
Assert.IsTrue(!i2.Columns[3].Expression.IsNullReference());
}
public virtual void UniqueConstraintTestTest()
{
var table = schema.CreateTable(UniqueTableName);
CreatePrimaryKey(table);
var column = table.CreateColumn("value", Driver.TypeMappings[typeof(int)].MapType());
column.IsNullable = true;
table.CreateUniqueConstraint("unique_me", column);
ExecuteNonQuery(SqlDdl.Create(table));
var tableRef = SqlDml.TableRef(table);
var insert = SqlDml.Insert(tableRef);
insert.Values.Add(tableRef[IdColumnName], 1);
// violation of PRIMARY KEY constraint
ExecuteNonQuery(insert);
AssertExceptionType(insert, SqlExceptionType.UniqueConstraintViolation);
// violation of UNIQUE constraint
insert.Values.Clear();
insert.Values.Add(tableRef[IdColumnName], 2);
insert.Values.Add(tableRef[column.Name], 0);
ExecuteNonQuery(insert);
insert.Values.Clear();
insert.Values.Add(tableRef[IdColumnName], 3);
insert.Values.Add(tableRef[column.Name], 0);
AssertExceptionType(insert, SqlExceptionType.UniqueConstraintViolation);
}
private void ExecuteCreateView(SqlConnection connection, View view)
{
var createViewQuery = SqlDdl.Create(view);
using (var command = connection.CreateCommand(createViewQuery))
command.ExecuteNonQuery();
}
private static void CreateSchemas(SqlConnection connection, Catalog catalog, IEnumerable <string> schemasToCreate)
{
foreach (var schema in schemasToCreate)
{
ExecuteQuery(connection, SqlDdl.Create(catalog.CreateSchema(schema)));
}
}
private void CreateDomain()
{
var schema = ExtractCatalog().DefaultSchema;
var domain = schema.CreateDomain("test_type", new SqlValueType(SqlType.Int64));
var commandText = Driver.Compile(SqlDdl.Create(domain)).GetCommandText();
ExecuteNonQuery(commandText);
}
protected override void CreateTable()
{
Table t;
t = schema.CreateTable(TableName);
t.CreateColumn("first", new SqlValueType(SqlType.VarChar, 50));
t.CreateColumn("second", new SqlValueType(SqlType.VarChar, 50));
ExecuteNonQuery(SqlDdl.Create(t));
}
public void Test160()
{
var t = Catalog.Schemas["main"].Tables["customer"];
Index index = t.CreateIndex("MegaIndex195");
index.CreateIndexColumn(t.TableColumns[0]);
SqlCreateIndex create = SqlDdl.Create(index);
Console.Write(Compile(create));
}
private void PrepareUserDefinedTables(Schema schema)
{
if (schema.Tables[TestEntityName] != null)
{
return;
}
var table = schema.CreateTable(TestEntityName);
var create = SqlDdl.Create(table);
table.CreateColumn(TestEntityField, new SqlValueType(SqlType.VarCharMax));
Execute(create);
}
public void TableCreationTest()
{
var defaultSchema = GetSchema();
var table = defaultSchema.CreateTable(string.Format("Crt1_{0}", TableName));
var column = table.CreateColumn("Id", GetServerTypeFor(typeof(int)));
table.CreatePrimaryKey("PK_Crt_DenyNamesReadingTest", column);
column = table.CreateColumn("CreationDate", GetServerTypeFor(typeof(DateTime)));
var createTableQuery = SqlDdl.Create(table);
TestQueryNamesReadable(createTableQuery, defaultSchema);
}
public void LobTest()
{
var table = testSchema.CreateTable(LobTestTable);
CreateIdColumn(table);
table.CreateColumn("bin_lob", new SqlValueType(SqlType.VarBinaryMax)).IsNullable = true;
table.CreateColumn("char_lob", new SqlValueType(SqlType.VarCharMax)).IsNullable = true;
ExecuteNonQuery(SqlDdl.Create(table));
var tableRef = SqlDml.TableRef(table);
var insert = CreateInsert(tableRef);
insert.Values.Add(tableRef["bin_lob"], SqlDml.ParameterRef("p_bin"));
insert.Values.Add(tableRef["char_lob"], SqlDml.ParameterRef("p_char"));
var charBuffer = Enumerable.Range(0, 10000)
.Select(i => (char)(i % (char.MaxValue - 1) + 1))
.ToArray();
var binBuffer = Enumerable.Range(0, 10000)
.Select(i => (byte)(i % byte.MaxValue))
.ToArray();
using (var binLob = Connection.CreateBinaryLargeObject())
using (var charLob = Connection.CreateCharacterLargeObject())
using (var command = Connection.CreateCommand(insert)) {
var binParameter = command.CreateParameter();
binParameter.ParameterName = "p_bin";
binLob.Write(binBuffer, 0, binBuffer.Length);
binLob.BindTo(binParameter);
var charParameter = command.CreateParameter();
charParameter.ParameterName = "p_char";
charLob.Write(charBuffer, 0, charBuffer.Length);
charLob.BindTo(charParameter);
command.Parameters.Add(charParameter);
command.Parameters.Add(binParameter);
command.ExecuteNonQuery();
}
var select = SqlDml.Select(tableRef);
select.Columns.Add(tableRef["bin_lob"]);
select.Columns.Add(tableRef["char_lob"]);
select.Where = SqlDml.Native("rownum") == 1;
using (var comand = Connection.CreateCommand(select))
using (var reader = comand.ExecuteReader()) {
Assert.IsTrue(reader.Read());
Compare(binBuffer, (byte[])reader[0]);
Compare(charBuffer, ((string)reader[1]).ToCharArray());
}
}
/// <summary>
/// Builds the descriptor of a temporary table.
/// </summary>
/// <param name="modelMapping">Model mapping.</param>
/// <param name="name">The name of the temporary table.</param>
/// <param name="source">The source.</param>
/// <param name="fieldNames">The names of field in temporary table.</param>
/// <returns>Built descriptor.</returns>
public TemporaryTableDescriptor BuildDescriptor(ModelMapping modelMapping, string name, TupleDescriptor source, string[] fieldNames)
{
EnsureTemporaryTablesSupported();
var hasColumns = source.Count > 0;
// TODO: split this method to a set of various simple virtual methods
var driver = Handlers.StorageDriver;
var catalog = new Catalog(modelMapping.TemporaryTableDatabase);
var schema = catalog.CreateSchema(modelMapping.TemporaryTableSchema);
var collation = modelMapping.TemporaryTableCollation != null
? new Collation(schema, modelMapping.TemporaryTableCollation)
: null;
if (fieldNames == null)
{
fieldNames = BuildFieldNames(source);
}
var typeMappings = source
.Select(driver.GetTypeMapping)
.ToArray();
// table
var table = CreateTemporaryTable(schema, name, source, typeMappings, fieldNames, collation);
var tableRef = SqlDml.TableRef(table);
// select statement
var queryStatement = MakeUpSelectQuery(tableRef, hasColumns);
// insert statement
var storeRequestBindings = new List <PersistParameterBinding>();
var insertStatement = MakeUpInsertQuery(tableRef, typeMappings, storeRequestBindings, hasColumns);
var result = new TemporaryTableDescriptor(name)
{
TupleDescriptor = source,
QueryStatement = queryStatement,
CreateStatement = driver.Compile(SqlDdl.Create(table)).GetCommandText(),
DropStatement = driver.Compile(SqlDdl.Drop(table)).GetCommandText(),
StoreRequest = new PersistRequest(Handlers.StorageDriver, insertStatement, storeRequestBindings),
ClearRequest = new PersistRequest(Handlers.StorageDriver, SqlDml.Delete(tableRef), null)
};
result.StoreRequest.Prepare();
result.ClearRequest.Prepare();
return(result);
}
public void FreeTextCreateTest()
{
var table = Catalog.Schemas["Person"].Tables["Address"];
var ftindex = table.CreateFullTextIndex(string.Empty);
var ftColumn = ftindex.CreateIndexColumn(table.Columns[1]);
ftColumn.Languages.Add(new Language("English"));
ftindex.UnderlyingUniqueIndex = "PK_Address_AddressID";
var createIndex = SqlDdl.Create(ftindex);
Console.WriteLine(sqlDriver.Compile(createIndex).GetCommandText());
var dropIndex = SqlDdl.Drop(ftindex);
Console.WriteLine(sqlDriver.Compile(dropIndex).GetCommandText());
}
protected override void TestFixtureSetUp()
{
base.TestFixtureSetUp();
var testSchema = ExtractDefaultSchema();
EnsureTableNotExists(testSchema, TestTable);
var table = testSchema.CreateTable(TestTable);
table.CreateColumn(IdColumn, new SqlValueType(SqlType.Decimal, 10, 0));
ExecuteNonQuery(SqlDdl.Create(table));
tableRef = SqlDml.TableRef(table);
var select = SqlDml.Select(tableRef);
select.Columns.Add(SqlDml.Count());
countQuery = Connection.Driver.Compile(select).GetCommandText();
}
private void PrepareExtensionTable(Schema schema, MetadataMapping mapping)
{
var columnsTypeMap = GetColumnsTypeMap(typeof(Xtensive.Orm.Metadata.Extension));
var table = schema.CreateTable(mapping.Extension);
var create = SqlDdl.Create(table);
table.CreateColumn(mapping.ExtensionName, columnsTypeMap[mapping.ExtensionName]);
table.CreateColumn(mapping.ExtensionText, columnsTypeMap[mapping.ExtensionText]);
Execute(create);
var tableRef = SqlDml.TableRef(table);
var insert = SqlDml.Insert(tableRef);
insert.Values.Add(tableRef[mapping.ExtensionName], "name");
insert.Values.Add(tableRef[mapping.ExtensionText], "text");
Execute(insert);
}
private void PrepareAssemblyTable(Schema schema, MetadataMapping mapping)
{
var columnsTypeMap = GetColumnsTypeMap(typeof(Xtensive.Orm.Metadata.Assembly));
var table = schema.CreateTable(mapping.Assembly);
var create = SqlDdl.Create(table);
table.CreateColumn(mapping.AssemblyName, columnsTypeMap[mapping.AssemblyName]);
table.CreateColumn(mapping.AssemblyVersion, columnsTypeMap[mapping.AssemblyVersion]);
Execute(create);
var tableRef = SqlDml.TableRef(table);
var insert = SqlDml.Insert(tableRef);
insert.Values.Add(tableRef[mapping.AssemblyName], "name");
insert.Values.Add(tableRef[mapping.AssemblyVersion], "version");
Execute(insert);
}
private void PreapareTypeTable(Schema schema, MetadataMapping mapping)
{
var columnsTypeMap = GetColumnsTypeMap(typeof(Xtensive.Orm.Metadata.Type));
var table = schema.CreateTable(mapping.Type);
var create = SqlDdl.Create(table);
_ = table.CreateColumn(mapping.TypeId, columnsTypeMap[mapping.TypeId]);
_ = table.CreateColumn(mapping.TypeName, columnsTypeMap[mapping.TypeName]);
Execute(create);
var tableRef = SqlDml.TableRef(table);
var insert = SqlDml.Insert(tableRef);
insert.Values.Add(tableRef[mapping.TypeId], 1);
insert.Values.Add(tableRef[mapping.TypeName], "name");
Execute(insert);
}
protected override void CreateTable()
{
Table t;
t = schema.CreateTable(TableName);
t.CreateColumn("first", new SqlValueType(SqlType.VarChar, 50));
t.CreateColumn("second", new SqlValueType(SqlType.VarChar, 50));
var c1 = t.CreateColumn("third", new SqlValueType(SqlType.VarChar));
var c2 = t.CreateColumn("forth", new SqlValueType(SqlType.VarChar));
var tr = SqlDml.TableRef(t);
c1.Expression = SqlDml.Concat(tr["first"], " ", tr["second"]);
c1.IsPersisted = false;
c1.IsNullable = true;
c2.Expression = SqlDml.Concat(tr["second"], " ", tr["first"]);
c2.IsPersisted = false;
c2.IsNullable = true;
ExecuteNonQuery(SqlDdl.Create(t));
}
public override void CreateExpressionIndexTest()
{
// Creating index
var t = schema.Tables[TableName];
var i = t.CreateIndex(ExpressionIndexName);
i.CreateIndexColumn(t.TableColumns["third"]);
i.CreateIndexColumn(t.TableColumns["forth"]);
ExecuteNonQuery(SqlDdl.Create(i));
// Extracting index and checking its properties
var c2 = ExtractCatalog();
var s2 = c2.DefaultSchema;
var t2 = s2.Tables[TableName];
var i2 = t2.Indexes[ExpressionIndexName];
Assert.IsNotNull(i2);
Assert.AreEqual(2, i2.Columns.Count);
Assert.IsTrue(!t2.TableColumns["third"].Expression.IsNullReference());
Assert.IsTrue(!t2.TableColumns["forth"].Expression.IsNullReference());
}
public void BatchTest()
{
var table = testSchema.CreateTable(BatchTestTable);
CreateIdColumn(table);
ExecuteNonQuery(SqlDdl.Create(table));
var tableRef = SqlDml.TableRef(table);
var singleStatementBatch = SqlDml.Batch();
singleStatementBatch.Add(CreateInsert(tableRef));
ExecuteNonQuery(singleStatementBatch);
var multiStatementBatch = SqlDml.Batch();
multiStatementBatch.Add(CreateInsert(tableRef));
multiStatementBatch.Add(CreateInsert(tableRef));
ExecuteNonQuery(multiStatementBatch);
var innerEmptyBatch = SqlDml.Batch();
var innerSingleStatementBatch = SqlDml.Batch();
innerSingleStatementBatch.Add(CreateInsert(tableRef));
var innerMultiStatementBatch = SqlDml.Batch();
innerMultiStatementBatch.Add(CreateInsert(tableRef));
var outerBatch = SqlDml.Batch();
outerBatch.Add(innerEmptyBatch);
outerBatch.Add(innerSingleStatementBatch);
outerBatch.Add(multiStatementBatch);
ExecuteNonQuery(outerBatch);
var countQuery = SqlDml.Select(tableRef);
countQuery.Columns.Add(SqlDml.Count());
Assert.AreEqual(6, Convert.ToInt32(ExecuteScalar(countQuery)));
}
public virtual void TimeoutTest()
{
var table = schema.CreateTable(TimeoutTableName);
CreatePrimaryKey(table);
ExecuteNonQuery(SqlDdl.Create(table));
var tableRef = SqlDml.TableRef(table);
var insert = SqlDml.Insert(tableRef);
insert.Values.Add(tableRef[IdColumnName], 1);
using (var connectionOne = Driver.CreateConnection()) {
connectionOne.Open();
connectionOne.BeginTransaction();
using (var connectionTwo = Driver.CreateConnection()) {
connectionTwo.Open();
connectionTwo.BeginTransaction(IsolationLevel.ReadCommitted);
using (var command = connectionTwo.CreateCommand(insert))
command.ExecuteNonQuery();
AssertExceptionType(connectionOne, insert, SqlExceptionType.OperationTimeout);
}
}
}
public virtual void CreateFilteredIndexTest()
{
// Creating index
var t = schema.Tables[TableName];
var i = t.CreateIndex(FilteredIndexName);
i.CreateIndexColumn(t.TableColumns["first"]);
i.CreateIndexColumn(t.TableColumns["second"]);
var tr = SqlDml.TableRef(t);
i.Where = SqlDml.IsNotNull(tr["first"]) && SqlDml.IsNotNull(tr["second"]);
ExecuteNonQuery(SqlDdl.Create(i));
// Extracting index and checking its properties
var c2 = ExtractCatalog();
var s2 = c2.DefaultSchema;
var t2 = s2.Tables[TableName];
var i2 = t2.Indexes[FilteredIndexName];
Assert.IsNotNull(i2);
Assert.AreEqual(2, i2.Columns.Count);
Assert.IsTrue(!i2.Where.IsNullReference());
}
private void CreateSchema()
{
var schema = Catalog.DefaultSchema;
var batch = SqlDml.Batch();
var table = schema.CreateTable("actor");
table.CreateColumn("actor_id", new SqlValueType("SMALLINT UNSIGNED"));
table.CreateColumn("first_name", new SqlValueType(SqlType.VarChar, 45));
table.CreateColumn("last_name", new SqlValueType(SqlType.VarChar, 45));
table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");// add default value and on update event
table.CreatePrimaryKey("pk_actor_actor_id", table.TableColumns["actor_id"]);
var index = table.CreateIndex("inx_actor_last_name");
index.CreateIndexColumn(table.TableColumns["last_name"]);
batch.Add(SqlDdl.Create(table));
batch.Add(SqlDdl.Create(index));
table = schema.CreateTable("country");
table.CreateColumn("country_id", new SqlValueType("SMALLINT UNSIGNED"));
table.CreateColumn("country", new SqlValueType(SqlType.VarChar, 50));
table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");;
table.CreatePrimaryKey("pk_country_country_id", table.TableColumns["country_id"]);
batch.Add(SqlDdl.Create(table));
table = schema.CreateTable("city");
table.CreateColumn("city_id", new SqlValueType("SMALLINT UNSIGNED"));
table.CreateColumn("city", new SqlValueType(SqlType.VarChar, 50));
table.CreateColumn("country_id", new SqlValueType("SMALLINT UNSIGNED"));
table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");;
table.CreatePrimaryKey("pk_city_city_id", table.TableColumns["city_id"]);
var foreignKey = table.CreateForeignKey("fk_city_country");
foreignKey.ReferencedTable = schema.Tables["country"];
foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["country_id"]);
foreignKey.Columns.Add(table.TableColumns["country_id"]);
foreignKey.OnUpdate = ReferentialAction.Cascade;
foreignKey.OnDelete = ReferentialAction.Restrict;
index = table.CreateIndex("inx_city_country_id");
index.CreateIndexColumn(table.TableColumns["country_id"]);
batch.Add(SqlDdl.Create(table));
batch.Add(SqlDdl.Create(index));
table = schema.CreateTable("address");
table.CreateColumn("address_id", new SqlValueType("SMALLINT UNSIGNED"));
table.CreateColumn("address", new SqlValueType(SqlType.VarChar, 50));
var column = table.CreateColumn("address2", new SqlValueType(SqlType.VarChar, 50));
column.IsNullable = true;
column.DefaultValue = SqlDml.Native("NULL");
table.CreateColumn("district", new SqlValueType(SqlType.VarChar, 20));
table.CreateColumn("city_id", new SqlValueType("SMALLINT UNSIGNED"));
column = table.CreateColumn("postal_code", new SqlValueType(SqlType.VarChar, 20));
column.DefaultValue = SqlDml.Native("NULL");
column.IsNullable = true;
table.CreateColumn("phone", new SqlValueType(SqlType.VarChar, 20));
table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
table.CreatePrimaryKey("pk_address_address_id", table.TableColumns["address_id"]);
foreignKey = table.CreateForeignKey("fk_address_city");
foreignKey.ReferencedTable = schema.Tables["city"];
foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["city_id"]);
foreignKey.Columns.Add(table.TableColumns["city_id"]);
foreignKey.OnUpdate = ReferentialAction.Cascade;
foreignKey.OnDelete = ReferentialAction.Restrict;
index = table.CreateIndex("inx_address_city_id");
index.CreateIndexColumn(table.TableColumns["city_id"]);
batch.Add(SqlDdl.Create(table));
batch.Add(SqlDdl.Create(index));
table = schema.CreateTable("category");
table.CreateColumn("category_id", new SqlValueType("SMALLINT UNSIGNED"));
table.CreateColumn("name", new SqlValueType(SqlType.VarChar, 25));
table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");;
table.CreatePrimaryKey("pk_category_category_id", table.TableColumns["category_id"]);
batch.Add(SqlDdl.Create(table));
table = schema.CreateTable("film_text");
table.CreateColumn("film_id", new SqlValueType("SMALLINT UNSIGNED"));
table.CreateColumn("title", new SqlValueType(SqlType.VarChar, 255));
table.CreateColumn("text", new SqlValueType(SqlType.VarCharMax));
table.CreatePrimaryKey("pk_film_text_film_id", table.TableColumns["film_id"]);
var fullTextIndex = table.CreateFullTextIndex("idx_film_text_title_text");
fullTextIndex.CreateIndexColumn(table.TableColumns["title"]);
fullTextIndex.CreateIndexColumn(table.TableColumns["text"]);
batch.Add(SqlDdl.Create(table));
table = schema.CreateTable("language");
table.CreateColumn("language_id", new SqlValueType("TINYINT UNSIGNED"));
table.CreateColumn("name", new SqlValueType(SqlType.Char, 20));
table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
table.CreatePrimaryKey("pk_language_language_id", table.TableColumns["language_id"]);
batch.Add(SqlDdl.Create(table));
table = schema.CreateTable("film");
table.CreateColumn("film_id", new SqlValueType("SMALLINT UNSIGNED"));
table.CreateColumn("title", new SqlValueType(SqlType.VarChar, 255));
column = table.CreateColumn("description", new SqlValueType(SqlType.VarCharMax));
column.DefaultValue = SqlDml.Native("NULL");
column.IsNullable = true;
column = table.CreateColumn("release_year", new SqlValueType("YEAR"));
column.DefaultValue = SqlDml.Native("NULL");
column.IsNullable = true;
table.CreateColumn("language_id", new SqlValueType("TINYINT UNSIGNED"));
column = table.CreateColumn("original_language_id", new SqlValueType("TINYINT UNSIGNED"));
column.IsNullable = true;
column.DefaultValue = SqlDml.Native("NULL");
column = table.CreateColumn("rental_duration", new SqlValueType("TINYINT UNSIGNED"));
column.DefaultValue = 3;
column = table.CreateColumn("rental_rate", new SqlValueType(SqlType.Decimal, 5, 2));
column.DefaultValue = 4.99;
column = table.CreateColumn("length", new SqlValueType("SMALLINT UNSIGNED"));
column.DefaultValue = SqlDml.Native("NULL");
column.IsNullable = true;
column = table.CreateColumn("replacement_cost", new SqlValueType(SqlType.Decimal, 5, 2));
column.DefaultValue = 19.99;
table.CreateColumn("rating", new SqlValueType("ENUM('G', 'PG', 'PG-13', 'R', 'NC-17')")).DefaultValue = 'G';
table.CreateColumn("special_features", new SqlValueType("SET('Trailers','Commentaries','Deleted Scenes','Behind the Scenes')")).DefaultValue = null;
table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");;
table.CreatePrimaryKey("pk_film", table.TableColumns["film_id"]);
index = table.CreateIndex("idx_film_film_id");
index.CreateIndexColumn(table.TableColumns["film_id"]);
foreignKey = table.CreateForeignKey("fk_film_language");
foreignKey.ReferencedTable = schema.Tables["language"];
foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["language_id"]);
foreignKey.Columns.Add(table.TableColumns["language_id"]);
foreignKey.OnUpdate = ReferentialAction.Cascade;
foreignKey.OnDelete = ReferentialAction.Restrict;
foreignKey = table.CreateForeignKey("fk_film_language_1");
foreignKey.ReferencedTable = schema.Tables["language"];
foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["language_id"]);
foreignKey.Columns.Add(table.TableColumns["original_language_id"]);
foreignKey.OnUpdate = ReferentialAction.Cascade;
foreignKey.OnDelete = ReferentialAction.Restrict;
batch.Add(SqlDdl.Create(table));
batch.Add(SqlDdl.Create(index));
table = schema.CreateTable("film_actor");
table.CreateColumn("actor_id", new SqlValueType("SMALLINT UNSIGNED"));
table.CreateColumn("film_id", new SqlValueType("SMALLINT UNSIGNED"));
table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
table.CreatePrimaryKey("pk_film_actor", table.TableColumns["actor_id"], table.TableColumns["film_id"]);
foreignKey = table.CreateForeignKey("fk_film_actor_actor");
foreignKey.ReferencedTable = schema.Tables["actor"];
foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["actor_id"]);
foreignKey.Columns.Add(table.TableColumns["actor_id"]);
foreignKey.OnUpdate = ReferentialAction.Cascade;
foreignKey.OnDelete = ReferentialAction.Restrict;
foreignKey = table.CreateForeignKey("fk_film_actor_film");
foreignKey.ReferencedTable = schema.Tables["film"];
foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["film_id"]);
foreignKey.Columns.Add(table.TableColumns["film_id"]);
foreignKey.OnUpdate = ReferentialAction.Cascade;
foreignKey.OnDelete = ReferentialAction.Restrict;
batch.Add(SqlDdl.Create(table));
table = schema.CreateTable("film_category");
table.CreateColumn("film_id", new SqlValueType("SMALLINT UNSIGNED"));
table.CreateColumn("category_id", new SqlValueType("SMALLINT UNSIGNED"));
table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
table.CreatePrimaryKey("pk_film_category", table.TableColumns["film_id"], table.TableColumns["category_id"]);
foreignKey = table.CreateForeignKey("fk_film_category_film");
foreignKey.ReferencedTable = schema.Tables["film"];
foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["film_id"]);
foreignKey.Columns.Add(table.TableColumns["film_id"]);
foreignKey.OnUpdate = ReferentialAction.Cascade;
foreignKey.OnDelete = ReferentialAction.Restrict;
foreignKey = table.CreateForeignKey("fk_film_category_category");
foreignKey.ReferencedTable = schema.Tables["category"];
foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["category_id"]);
foreignKey.Columns.Add(table.TableColumns["category_id"]);
foreignKey.OnUpdate = ReferentialAction.Cascade;
foreignKey.OnDelete = ReferentialAction.Restrict;
batch.Add(SqlDdl.Create(table));
table = schema.CreateTable("store");
table.CreateColumn("store_id", new SqlValueType("TINYINT UNSIGNED"));
table.CreateColumn("manager_staff_id", new SqlValueType("TINYINT UNSIGNED"));
table.CreateColumn("address_id", new SqlValueType("SMALLINT UNSIGNED"));
table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
table.CreatePrimaryKey("pk_store", table.TableColumns["store_id"]);
table.CreateUniqueConstraint("idx_unique_manager", table.TableColumns["manager_staff_id"]);
index = table.CreateIndex("idx_store_address_id");
index.CreateIndexColumn(table.TableColumns["address_id"]);
foreignKey = table.CreateForeignKey("fk_store_address_id");
foreignKey.ReferencedTable = schema.Tables["address"];
foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["address_id"]);
foreignKey.Columns.Add(table.TableColumns["address_id"]);
foreignKey.OnUpdate = ReferentialAction.Cascade;
foreignKey.OnDelete = ReferentialAction.Restrict;
batch.Add(SqlDdl.Create(table));
batch.Add(SqlDdl.Create(index));
table = schema.CreateTable("staff");
table.CreateColumn("staff_id", new SqlValueType("TINYINT UNSIGNED"));
table.CreateColumn("first_name", new SqlValueType(SqlType.VarChar, 45));
table.CreateColumn("last_name", new SqlValueType(SqlType.VarChar, 45));
table.CreateColumn("address_id", new SqlValueType("SMALLINT UNSIGNED"));
column = table.CreateColumn("picture", new SqlValueType("BLOB"));
column.DefaultValue = SqlDml.Native("NULL");
column.IsNullable = true;
table.CreateColumn("email", new SqlValueType(SqlType.VarChar, 50));
table.CreateColumn("store_id", new SqlValueType("TINYINT UNSIGNED"));
column = table.CreateColumn("active", new SqlValueType(SqlType.Boolean));
column.DefaultValue = SqlDml.Native("TRUE");
table.CreateColumn("username", new SqlValueType(SqlType.VarChar, 16));
column = table.CreateColumn("password", new SqlValueType("VARCHAR(40) BINARY"));
column.DefaultValue = SqlDml.Native("NULL");
column.IsNullable = true;
table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
table.CreatePrimaryKey("pk_staff", table.TableColumns["staff_id"]);
index = table.CreateIndex("idx_staff_address_id");
index.CreateIndexColumn(table.TableColumns["address_id"]);
var secondIndex = table.CreateIndex("idx_staff_store_id");
secondIndex.CreateIndexColumn(table.TableColumns["store_id"]);
foreignKey = table.CreateForeignKey("fk_staff_store");
foreignKey.ReferencedTable = schema.Tables["store"];
foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["store_id"]);
foreignKey.Columns.Add(table.TableColumns["store_id"]);
foreignKey.OnUpdate = ReferentialAction.Cascade;
foreignKey.OnDelete = ReferentialAction.Restrict;
foreignKey = table.CreateForeignKey("fk_staff_address");
foreignKey.ReferencedTable = schema.Tables["address"];
foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["address_id"]);
foreignKey.Columns.Add(table.TableColumns["address_id"]);
foreignKey.OnUpdate = ReferentialAction.Cascade;
foreignKey.OnDelete = ReferentialAction.Restrict;
batch.Add(SqlDdl.Create(table));
batch.Add(SqlDdl.Create(index));
batch.Add(SqlDdl.Create(secondIndex));
foreignKey = schema.Tables["store"].CreateForeignKey("fk_store_staff");
schema.Tables["store"].TableConstraints.Remove(foreignKey);
foreignKey.ReferencedTable = schema.Tables["staff"];
foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["staff_id"]);
foreignKey.Columns.Add(schema.Tables["store"].TableColumns["manager_staff_id"]);
foreignKey.OnUpdate = ReferentialAction.Cascade;
foreignKey.OnDelete = ReferentialAction.Restrict;
batch.Add(SqlDdl.Alter(schema.Tables["store"], SqlDdl.AddConstraint(foreignKey)));
table = schema.CreateTable("customer");
table.CreateColumn("customer_id", new SqlValueType("SMALLINT UNSIGNED"));
table.CreateColumn("store_id", new SqlValueType("TINYINT UNSIGNED"));
table.CreateColumn("first_name", new SqlValueType(SqlType.VarChar, 45));
table.CreateColumn("last_name", new SqlValueType(SqlType.VarChar, 45));
column = table.CreateColumn("email", new SqlValueType(SqlType.VarChar, 50));
column.DefaultValue = SqlDml.Native("NULL");
column.IsNullable = true;
table.CreateColumn("address_id", new SqlValueType("SMALLINT UNSIGNED"));
column = table.CreateColumn("active", new SqlValueType(SqlType.Boolean));
column.DefaultValue = SqlDml.Native("TRUE");
table.CreateColumn("create_date", new SqlValueType(SqlType.DateTime));
table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
table.CreatePrimaryKey("pk_customer", table.TableColumns["customer_id"]);
index = table.CreateIndex("idx_store_id");
index.CreateIndexColumn(table.TableColumns["store_id"]);
secondIndex = table.CreateIndex("idx_address_id");
secondIndex.CreateIndexColumn(table.TableColumns["address_id"]);
var thirdIndex = table.CreateIndex("idx_last_name");
thirdIndex.CreateIndexColumn(table.TableColumns["last_name"]);
foreignKey = table.CreateForeignKey("fk_customer_address");
foreignKey.ReferencedTable = schema.Tables["address"];
foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["address_id"]);
foreignKey.Columns.Add(table.TableColumns["address_id"]);
foreignKey.OnUpdate = ReferentialAction.Cascade;
foreignKey.OnDelete = ReferentialAction.Restrict;
batch.Add(SqlDdl.Create(table));
batch.Add(SqlDdl.Create(index));
batch.Add(SqlDdl.Create(secondIndex));
batch.Add(SqlDdl.Create(thirdIndex));
table = schema.CreateTable("inventory");
table.CreateColumn("inventory_id", new SqlValueType("MEDIUMINT UNSIGNED"));
table.CreateColumn("film_id", new SqlValueType("SMALLINT UNSIGNED"));
table.CreateColumn("store_id", new SqlValueType("TINYINT UNSIGNED"));
table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
table.CreatePrimaryKey("pk_inventory", table.TableColumns["inventory_id"]);
table.CreateIndex("idx_inventory_film_id").CreateIndexColumn(table.TableColumns["film_id"]);
index = table.CreateIndex("idx_inventory_store_id_film_id");
index.CreateIndexColumn(table.TableColumns["store_id"]);
index.CreateIndexColumn(table.TableColumns["film_id"]);
foreignKey = table.CreateForeignKey("fk_inventory_store");
foreignKey.ReferencedTable = schema.Tables["store"];
foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["store_id"]);
foreignKey.Columns.Add(table.TableColumns["store_id"]);
foreignKey.OnUpdate = ReferentialAction.Cascade;
foreignKey.OnDelete = ReferentialAction.Restrict;
foreignKey = table.CreateForeignKey("fk_inventory_film");
foreignKey.ReferencedTable = schema.Tables["film"];
foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["film_id"]);
foreignKey.Columns.Add(table.TableColumns["film_id"]);
foreignKey.OnUpdate = ReferentialAction.Cascade;
foreignKey.OnDelete = ReferentialAction.Restrict;
batch.Add(SqlDdl.Create(table));
batch.Add(SqlDdl.Create(index));
table = schema.CreateTable("rental");
table.CreateColumn("rental_id", new SqlValueType(SqlType.Int32));
table.CreateColumn("rental_date", new SqlValueType(SqlType.DateTime));
table.CreateColumn("inventory_id", new SqlValueType("MEDIUMINT UNSIGNED"));
table.CreateColumn("customer_id", new SqlValueType("SMALLINT UNSIGNED"));
column = table.CreateColumn("return_date", new SqlValueType(SqlType.DateTime));
column.DefaultValue = SqlDml.Native("NULL");
column.IsNullable = true;
table.CreateColumn("staff_id", new SqlValueType("TINYINT UNSIGNED"));
table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
table.CreatePrimaryKey("pk_rental", table.TableColumns["rental_id"]);
table.CreateUniqueConstraint("unique_rental_date_inventory_id_customer_id", table.TableColumns["rental_date"], table.TableColumns["inventory_id"], table.TableColumns["customer_id"]);
index = table.CreateIndex("idx_rental_inventory_id");
index.CreateIndexColumn(table.TableColumns["inventory_id"]);
secondIndex = table.CreateIndex("idx_rental_customer_id");
secondIndex.CreateIndexColumn(table.TableColumns["customer_id"]);
thirdIndex = table.CreateIndex("idx_rental_staff_id");
thirdIndex.CreateIndexColumn(table.TableColumns["staff_id"]);
foreignKey = table.CreateForeignKey("fk_rental_staff");
foreignKey.ReferencedTable = schema.Tables["staff"];
foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["staff_id"]);
foreignKey.Columns.Add(table.TableColumns["staff_id"]);
foreignKey.OnUpdate = ReferentialAction.Cascade;
foreignKey.OnDelete = ReferentialAction.Restrict;
foreignKey = table.CreateForeignKey("fk_rental_inventory");
foreignKey.ReferencedTable = schema.Tables["inventory"];
foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["inventory_id"]);
foreignKey.Columns.Add(table.TableColumns["inventory_id"]);
batch.Add(SqlDdl.Create(table));
batch.Add(SqlDdl.Create(index));
batch.Add(SqlDdl.Create(secondIndex));
batch.Add(SqlDdl.Create(thirdIndex));
table = schema.CreateTable("payment");
table.CreateColumn("payment_id", new SqlValueType("SMALLINT UNSIGNED"));
table.CreateColumn("customer_id", new SqlValueType("SMALLINT UNSIGNED"));
table.CreateColumn("staff_id", new SqlValueType("TINYINT UNSIGNED"));
column = table.CreateColumn("rental_id", new SqlValueType(SqlType.Int32));
column.DefaultValue = SqlDml.Native("NULL");
column.IsNullable = true;
table.CreateColumn("amount", new SqlValueType(SqlType.Decimal, 5, 2));
table.CreateColumn("payment_date", new SqlValueType(SqlType.DateTime));
table.CreateColumn("last_update", new SqlValueType("TIMESTAMP")).DefaultValue = SqlDml.Native("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
table.CreatePrimaryKey("pk_payment", table.TableColumns["payment_id"]);
index = table.CreateIndex("idx_payment_staff_id");
index.CreateIndexColumn(table.TableColumns["staff_id"]);
secondIndex = table.CreateIndex("idx_payment_customer_id");
secondIndex.CreateIndexColumn(table.TableColumns["customer_id"]);
foreignKey = table.CreateForeignKey("fk_payment_staff");
foreignKey.ReferencedTable = schema.Tables["staff"];
foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["staff_id"]);
foreignKey.Columns.Add(table.TableColumns["staff_id"]);
foreignKey.OnUpdate = ReferentialAction.Cascade;
foreignKey.OnDelete = ReferentialAction.Restrict;
foreignKey = table.CreateForeignKey("fk_payment_customer");
foreignKey.ReferencedTable = schema.Tables["customer"];
foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["customer_id"]);
foreignKey.Columns.Add(table.TableColumns["customer_id"]);
foreignKey.OnUpdate = ReferentialAction.Cascade;
foreignKey.OnDelete = ReferentialAction.Restrict;
foreignKey = table.CreateForeignKey("fk_payment_rental");
foreignKey.ReferencedTable = schema.Tables["rental"];
foreignKey.ReferencedColumns.Add(foreignKey.ReferencedTable.TableColumns["rental_id"]);
foreignKey.Columns.Add(table.TableColumns["rental_id"]);
batch.Add(SqlDdl.Create(table));
batch.Add(SqlDdl.Create(index));
batch.Add(SqlDdl.Create(secondIndex));
table = schema.CreateTable("table1");
table.CreateColumn("field1", new SqlValueType(SqlType.Int32));
table.CreateColumn("field2", new SqlValueType(SqlType.VarChar, 20)).IsNullable = true;
batch.Add(SqlDdl.Create(table));
var view = schema.CreateView("customer_list", SqlDml.Native(@"SELECT cu.customer_id AS ID, CONCAT(cu.first_name, _utf8' ', cu.last_name) AS name, a.address AS address, a.postal_code AS `zip code`,
a.phone AS phone, city.city AS city, country.country AS country, IF(cu.active, _utf8'active',_utf8'') AS notes, cu.store_id AS SID
FROM customer AS cu JOIN address AS a ON cu.address_id = a.address_id JOIN city ON a.city_id = city.city_id
JOIN country ON city.country_id = country.country_id"));
batch.Add(SqlDdl.Create(view));
using (var cmd = SqlConnection.CreateCommand()) {
cmd.CommandText = SqlDriver.Compile(batch).GetCommandText();
cmd.ExecuteNonQuery();
}
Catalog = SqlDriver.ExtractCatalog(SqlConnection);
}
public void Test154()
{
SqlCreateSchema create = SqlDdl.Create(Catalog.Schemas["main"]);
Console.Write(Compile(create));
}
public void Test153()
{
SqlCreateView create = SqlDdl.Create(Catalog.Schemas["main"].Views["Invoice Subtotals"]);
Console.Write(Compile(create));
}
private void ManuallyInsertIgnoredItems(Catalog catalog)
{
var validatableSchemas = new List <Schema>();
if (nodeToSchemaMap.Count > 0)
{
validatableSchemas.Add(catalog.Schemas[nodeToSchemaMap[MainNodeId]]);
validatableSchemas.Add(catalog.Schemas[nodeToSchemaMap[AdditionalNodeId]]);
}
else
{
validatableSchemas.Add(catalog.DefaultSchema);
}
foreach (var schema in validatableSchemas)
{
using (var connection = driver.CreateConnection()) {
connection.Open();
var productTable = schema.Tables["Product"];
var hiddenNameColumn = productTable.CreateColumn("HiddenName", GetTypeForString(255));
hiddenNameColumn.IsNullable = true;
var commandText = driver.Compile(SqlDdl.Alter(productTable, SqlDdl.AddColumn(hiddenNameColumn))).GetCommandText();
using (var command = connection.CreateCommand(commandText)) {
_ = command.ExecuteNonQuery();
}
var priceListTable = schema.Tables["PriceList"];
var hiddenCommentColumn = priceListTable.CreateColumn("HiddenComment", GetTypeForString(255));
hiddenCommentColumn.IsNullable = true;
commandText = driver.Compile(SqlDdl.Alter(priceListTable, SqlDdl.AddColumn(hiddenCommentColumn))).GetCommandText();
using (var command = connection.CreateCommand(commandText)) {
_ = command.ExecuteNonQuery();
}
var currencyTable = schema.Tables["Currency"];
var prefixColumnTemplate = "NotInDomain{0}";
var columns = new[] { "Column1", "Column2", "Column3" };
foreach (var column in columns)
{
var prefixColumn = currencyTable.CreateColumn(string.Format(prefixColumnTemplate, column), GetTypeForString(255));
prefixColumn.IsNullable = true;
commandText = driver.Compile(SqlDdl.Alter(currencyTable, SqlDdl.AddColumn(prefixColumn))).GetCommandText();
using (var command = connection.CreateCommand(commandText)) {
_ = command.ExecuteNonQuery();
}
}
var ignoredTable = schema.CreateTable("HiddenTable");
var idColumn = ignoredTable.CreateColumn("Id", new SqlValueType(SqlType.Int64));
idColumn.IsNullable = false;
var name = ignoredTable.CreateColumn("Name", GetTypeForString(255));
name.IsNullable = false;
var pk = ignoredTable.CreatePrimaryKey("PK_HiddenTable", idColumn);
using (var command = connection.CreateCommand(SqlDdl.Create(ignoredTable))) {
_ = command.ExecuteNonQuery();
}
var notInDomainTable1 = schema.CreateTable("NotInDomain1");
idColumn = notInDomainTable1.CreateColumn("Id", new SqlValueType(SqlType.Int64));
idColumn.IsNullable = false;
name = notInDomainTable1.CreateColumn("Name", GetTypeForString(255));
name.IsNullable = false;
pk = notInDomainTable1.CreatePrimaryKey("PK_NotInDomain1", idColumn);
using (var command = connection.CreateCommand(SqlDdl.Create(notInDomainTable1))) {
_ = command.ExecuteNonQuery();
}
var notInDomainTable2 = schema.CreateTable("NotInDomain2");
idColumn = notInDomainTable2.CreateColumn("Id", new SqlValueType(SqlType.Int64));
idColumn.IsNullable = false;
name = notInDomainTable2.CreateColumn("Name", GetTypeForString(255));
name.IsNullable = false;
pk = notInDomainTable2.CreatePrimaryKey("PK_NotInDomain2", idColumn);
using (var command = connection.CreateCommand(SqlDdl.Create(notInDomainTable2))) {
_ = command.ExecuteNonQuery();
}
var notInDomainTable3 = schema.CreateTable("NotInDomain3");
idColumn = notInDomainTable3.CreateColumn("Id", new SqlValueType(SqlType.Int64));
idColumn.IsNullable = false;
name = notInDomainTable3.CreateColumn("Name", GetTypeForString(255));
name.IsNullable = false;
pk = notInDomainTable3.CreatePrimaryKey("PK_NotInDomain3", idColumn);
using (var command = connection.CreateCommand(SqlDdl.Create(notInDomainTable3))) {
_ = command.ExecuteNonQuery();
}
}
}
}
public void Test150()
{
SqlCreateTable create = SqlDdl.Create(Catalog.Schemas["main"].Tables["customer"]);
Console.Write(Compile(create));
}