private void ClearSchema(SqlDriver driver)
{
using (var connection = driver.CreateConnection()) {
connection.Open();
try {
var schema = driver.ExtractSchema(connection, SpecialSchemaAlias);
var foreignKeys = schema.Tables
.Select(t => new {
Table = t,
ForeignKeys = t.TableConstraints.OfType <ForeignKey>()
});
foreach (var dropConstraintText in from foreignKeyInfo in foreignKeys
from foreignKey in foreignKeyInfo.ForeignKeys
select driver.Compile(SqlDdl.Alter(foreignKeyInfo.Table, SqlDdl.DropConstraint(foreignKey))).GetCommandText())
{
using (var command = connection.CreateCommand(dropConstraintText))
command.ExecuteNonQuery();
}
foreach (var table in schema.Tables)
{
var dropTableText = driver.Compile(SqlDdl.Drop(table, true)).GetCommandText();
using (var command = connection.CreateCommand(dropTableText))
command.ExecuteNonQuery();
}
}
finally {
connection.Close();
}
}
}
public SqlCompilationResult Compile(ISqlCompileUnit statement)
{
var options = new SqlCompilerConfiguration {
DatabaseQualifiedObjects = configuration.IsMultidatabase
};
return(underlyingDriver.Compile(statement, options));
}
public void ExtractTest()
{
SqlSelect select = SqlDml.Select();
select.Columns.Add(SqlDml.Extract(SqlDateTimePart.Day, "2006-01-23"));
Console.WriteLine(SqlDriver.Compile(select).GetCommandText());
}
public void PositionTest()
{
SqlSelect select = SqlDml.Select();
select.Columns.Add(SqlDml.Multiply(SqlDml.Position("b", "abc"), 4));
Console.WriteLine(SqlDriver.Compile(select).GetCommandText());
}
public void UnionTest()
{
SqlSelect s1 = SqlDml.Select(SqlDml.TableRef(Catalog.DefaultSchema.Tables["address"]));
s1.Columns.Add(s1.From["address_id"]);
SqlSelect s2 = SqlDml.Select(SqlDml.TableRef(Catalog.DefaultSchema.Tables["address"]));
s2.Columns.Add(s2.From["address_id"]);
SqlSelect s3 = SqlDml.Select(SqlDml.TableRef(Catalog.DefaultSchema.Tables["address"]));
s3.Columns.Add(s3.From["address_id"]);
Console.WriteLine(SqlDriver.Compile(s1.Union(s2)).GetCommandText());
Console.WriteLine(SqlDriver.Compile(s1.Union(s2).Union(s3)).GetCommandText());
Console.WriteLine(SqlDriver.Compile(s1.Union(s2.Union(s3))).GetCommandText());
Console.WriteLine(SqlDriver.Compile(SqlDml.Union(s1, s2)).GetCommandText());
Console.WriteLine(SqlDriver.Compile(SqlDml.Union(s1, s1.Union(s2))).GetCommandText());
Console.WriteLine(SqlDriver.Compile(SqlDml.Union(s1.Union(s2), s1)).GetCommandText());
Console.WriteLine(SqlDriver.Compile(SqlDml.Union(s1.Union(s2), s1.Union(s2))).GetCommandText());
s3.Where = SqlDml.In(50.00, s1.Union(s2));
Console.WriteLine(SqlDriver.Compile(s3).GetCommandText());
SqlQueryRef qr = SqlDml.QueryRef(s1.Union(s2), "qr");
Assert.Greater(qr.Columns.Count, 0);
}
private bool CompareExecuteNonQuery(string commandText, ISqlCompileUnit statement)
{
sqlCommand.CommandText = SqlDriver.Compile(statement).GetCommandText();
sqlCommand.Prepare();
Console.WriteLine(sqlCommand.CommandText);
Console.WriteLine(commandText);
dbCommand.CommandText = commandText;
DbCommandExecutionResult r1, r2;
r1 = GetExecuteNonQueryResult(dbCommand);
r2 = GetExecuteNonQueryResult(sqlCommand);
Console.WriteLine();
Console.WriteLine();
Console.WriteLine(r1);
Console.WriteLine(r2);
if (r1.RowCount != r2.RowCount)
{
return(false);
}
return(true);
}
public void TrueTest()
{
SqlSelect s1 = SqlDml.Select();
s1.Where = true;
Console.WriteLine(SqlDriver.Compile(s1).GetCommandText());
}
public void ConcatTest()
{
SqlSelect select = SqlDml.Select();
select.Columns.Add(SqlDml.Concat("a", "b"));
//select.Columns.Add("User: " + SqlDml.SessionUser()); //NOTE: Not supported by MySQL.
Console.WriteLine(SqlDriver.Compile(select).GetCommandText());
}
public void SubstringTest()
{
SqlSelect select = SqlDml.Select();
select.Columns.Add(SqlDml.Substring("abc", 1, 1));
select.Columns.Add(SqlDml.Substring("Xtensive", 2));
Console.WriteLine(SqlDriver.Compile(select).GetCommandText());
}
public void UniqueTest()
{
SqlSelect s1 = SqlDml.Select();
SqlSelect s2 = SqlDml.Select(SqlDml.TableRef(Catalog.DefaultSchema.Tables["customer"]));
s2.Columns.Add(SqlDml.Asterisk);
s1.Columns.Add(SqlDml.Unique(s2) == true);
Console.WriteLine(SqlDriver.Compile(s1).GetCommandText());
}
public void FreeTextTest()
{
SqlSelect select = SqlDml.Select();
var table = Catalog.DefaultSchema.Tables["Address"];
select.From = SqlDml.QueryRef(SqlDml.FreeTextTable(table, "How can I make my own beers and ales?", EnumerableUtils.One(table.Columns[0].Name).ToList(), EnumerableUtils.One(table.Columns[0].Name).ToList()));
select.Columns.Add(select.From.Asterisk);
Console.WriteLine(SqlDriver.Compile(select).GetCommandText());
}
public void CircularReferencesTest()
{
SqlSelect select = SqlDml.Select();
SqlBinary b = SqlDml.Literal(1) + 2;
SqlBinary rb = b + 3;
rb.Left.ReplaceWith(rb);
select.Where = rb > 1;
Assert.Throws <SqlCompilerException>(() => { Console.WriteLine(SqlDriver.Compile(select).GetCommandText()); });
}
public void TrimTest()
{
SqlSelect select = SqlDml.Select();
select.Columns.Add(SqlDml.Trim(" abc "));
select.Columns.Add(SqlDml.Trim(" abc ", SqlTrimType.Leading));
select.Columns.Add(SqlDml.Trim(" abc ", SqlTrimType.Trailing));
select.Columns.Add(SqlDml.Trim(" abc ", SqlTrimType.Both));
select.Columns.Add(SqlDml.Trim(" abc ", SqlTrimType.Both, " "));
Console.WriteLine(SqlDriver.Compile(select).GetCommandText());
}
private void CompareColumnEquality(ISqlCompileUnit statement)
{
sqlCommand.CommandText = SqlDriver.Compile(statement).GetCommandText();
sqlCommand.Prepare();
using (var command = sqlCommand.Connection.CreateCommand())
{
Console.WriteLine(sqlCommand.CommandText);
using (var reader = sqlCommand.ExecuteReader()) {
Assert.IsTrue(reader.Read());
Assert.AreEqual(reader[0], reader[1], "The columns are not equal!");
}
}
}
public void JoinTest()
{
SqlTableRef tr1 = SqlDml.TableRef(Catalog.DefaultSchema.Tables["address"]);
SqlTableRef tr2 = SqlDml.TableRef(Catalog.DefaultSchema.Tables["address"]);
SqlTableRef tr3 = SqlDml.TableRef(Catalog.DefaultSchema.Tables["address"]);
SqlSelect select = SqlDml.Select(tr1.InnerJoin(tr2, tr1[0] == tr2[0]).InnerJoin(tr3, tr2[0] == tr3[0]));
select.Columns.Add(SqlDml.Asterisk);
sqlCommand.CommandText = SqlDriver.Compile(select).GetCommandText();
Console.WriteLine(SqlDriver.Compile(select).GetCommandText());
sqlCommand.Prepare();
//int i = 0;
//SqlTableRef[] refs = new[] { tr1, tr2, tr3 };
//foreach (SqlTable source in select.From)
// Assert.AreEqual(refs[i++], source);
}
public void ArrayTest()//TODO: Find reason why this pattern is structured like this.(Malisa)
{
SqlArray <int> i = SqlDml.Array(new int[] { 1, 2 });
i.Values[0] = 10;
SqlSelect select = SqlDml.Select();
select.Where = SqlDml.In(1, i);
MemoryStream ms = new MemoryStream();
BinaryFormatter bf = new BinaryFormatter();
bf.Serialize(ms, select);
ms.Seek(0, SeekOrigin.Begin);
select = (SqlSelect)bf.Deserialize(ms);
Console.WriteLine(SqlDriver.Compile(select).GetCommandText());
}
public void ExceptAllTest()
{
SqlSelect s1 = SqlDml.Select(SqlDml.TableRef(Catalog.DefaultSchema.Tables["Address"]));
s1.Columns.Add(SqlDml.Asterisk);
SqlSelect s2 = SqlDml.Select(SqlDml.TableRef(Catalog.DefaultSchema.Tables["Address"]));
s2.Columns.Add(SqlDml.Asterisk);
SqlSelect s3 = SqlDml.Select(SqlDml.TableRef(Catalog.DefaultSchema.Tables["Address"]));
s3.Columns.Add(SqlDml.Asterisk);
Console.WriteLine(SqlDriver.Compile(s1.ExceptAll(s2)).GetCommandText());
Console.WriteLine(SqlDriver.Compile(s1.ExceptAll(s2).ExceptAll(s3)).GetCommandText());
Console.WriteLine(SqlDriver.Compile(s1.ExceptAll(s2.ExceptAll(s3))).GetCommandText());
Console.WriteLine(SqlDriver.Compile(SqlDml.ExceptAll(s1, s2)).GetCommandText());
Console.WriteLine(SqlDriver.Compile(SqlDml.ExceptAll(s1, s1.ExceptAll(s2))).GetCommandText());
Console.WriteLine(SqlDriver.Compile(SqlDml.ExceptAll(s1.ExceptAll(s2), s1)).GetCommandText());
Console.WriteLine(SqlDriver.Compile(SqlDml.ExceptAll(s1.ExceptAll(s2), s1.ExceptAll(s2))).GetCommandText());
}
public void IntersectAllTest()//TODO: Relook into the keyword for INTERSECT ALL
{
SqlSelect s1 = SqlDml.Select(SqlDml.TableRef(Catalog.DefaultSchema.Tables["address"]));
s1.Columns.Add(SqlDml.Asterisk);
SqlSelect s2 = SqlDml.Select(SqlDml.TableRef(Catalog.DefaultSchema.Tables["address"]));
s2.Columns.Add(SqlDml.Asterisk);
SqlSelect s3 = SqlDml.Select(SqlDml.TableRef(Catalog.DefaultSchema.Tables["address"]));
s3.Columns.Add(SqlDml.Asterisk);
Console.WriteLine(SqlDriver.Compile(s1.IntersectAll(s2)).GetCommandText());
Console.WriteLine(SqlDriver.Compile(s1.IntersectAll(s2).IntersectAll(s3)).GetCommandText());
Console.WriteLine(SqlDriver.Compile(s1.IntersectAll(s2.IntersectAll(s3))).GetCommandText());
Console.WriteLine(SqlDriver.Compile(SqlDml.IntersectAll(s1, s2)).GetCommandText());
Console.WriteLine(SqlDriver.Compile(SqlDml.IntersectAll(s1, s1.IntersectAll(s2))).GetCommandText());
Console.WriteLine(SqlDriver.Compile(SqlDml.IntersectAll(s1.IntersectAll(s2), s1)).GetCommandText());
Console.WriteLine(SqlDriver.Compile(SqlDml.IntersectAll(s1.IntersectAll(s2), s1.IntersectAll(s2))).GetCommandText());
}
private bool CompareExecuteDataReader(string commandText, ISqlCompileUnit statement)
{
sqlCommand.CommandText = SqlDriver.Compile(statement).GetCommandText();
sqlCommand.Prepare();
Console.WriteLine(sqlCommand.CommandText);
Console.WriteLine(commandText);
dbCommand.CommandText = commandText;
DbCommandExecutionResult r1, r2;
r1 = GetExecuteDataReaderResult(dbCommand);
r2 = GetExecuteDataReaderResult(sqlCommand);
Console.WriteLine();
Console.WriteLine();
Console.WriteLine(r1);
Console.WriteLine(r2);
if (r1.RowCount != r2.RowCount)
{
return(false);
}
if (r1.FieldCount != r2.FieldCount)
{
return(false);
}
for (int i = 0; i < r1.FieldCount; i++)
{
if (r1.FieldNames[i] != r2.FieldNames[i])
{
return(false);
}
}
return(true);
}
public void TableIsNotSetTest1()
{
SqlDelete delete = SqlDml.Delete();
Assert.Throws <SqlCompilerException>(() => sqlDriver.Compile(delete));
}
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();
}
}
}
}
private SqlCompilationResult Compile(ISqlCompileUnit statement)
{
return(SqlDriver.Compile(statement));
}
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);
}
protected SqlCompilationResult Compile(ISqlCompileUnit statement)
{
return(sqlDriver.Compile(statement));
}