/// <summary>
/// Gets the list of database tables
/// </summary>
/// <returns>The list of table descriptions</returns>
protected IList<Table> GetTables()
{
IList<Table> result = new List<Table>();
var connString = System.Configuration.ConfigurationManager.ConnectionStrings[ConnectionStringNames.MySQLDB].ConnectionString;
var databaseName = new MySqlConnectionStringBuilder(connString).Database;
using (var connection = new MySqlConnection(connString))
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = " SELECT t.TABLE_NAME AS 'Table Name', (SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ',') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = t.TABLE_SCHEMA AND TABLE_NAME = t.TABLE_NAME) AS 'Columns', (SELECT GROUP_CONCAT(CONSTRAINT_NAME SEPARATOR ', ') FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = t.TABLE_SCHEMA AND TABLE_NAME = t.TABLE_NAME AND REFERENCED_TABLE_NAME IS NOT NULL) AS 'Foreign Keys' FROM information_schema.TABLES t WHERE (t.TABLE_TYPE = 'BASE TABLE') AND t.TABLE_SCHEMA = @SchemaName";
command.Parameters.Add(new MySqlParameter("@SchemaName", MySqlDbType.Text) { Value = databaseName });
var reader = command.ExecuteReader();
while (reader.Read())
{
Table table = new Table()
{
Name = (string)reader["Table Name"],
Columns = (reader["Columns"] != System.DBNull.Value) ? ((string)reader["Columns"]).Split(',').ToList() : new List<string>(),
ForeignKeys = (reader["Foreign Keys"] != System.DBNull.Value) ? ((string)reader["Foreign Keys"]).Split(',').ToList() : new List<string>(),
};
result.Add(table);
}
}
return result;
}
private void ValidateResult(IList<Table> tables)
{
// Category Table
var expectedCategoryTable = new Table()
{
Name = "CATEGORY",
Columns = new string[] { "category_id", "name", "version" },
};
ValidateTable(expectedCategoryTable, tables);
// Category Product Table
var expectedCategoryProductTable = new Table()
{
Name = "CATEGORY_PRODUCT",
Columns = new string[] { "category_id", "product_id" },
};
ValidateTable(expectedCategoryProductTable, tables);
// Customer Table
var expectedCustomerTable = new Table()
{
Name = "CUSTOMER",
Columns = new string[] { "customer_id", "first_name", "last_name", "code", "date_of_birth", "address_street", "address_number", "address_city_id", "version" },
};
ValidateTable(expectedCustomerTable, tables);
// Product Table
var expectedProductTable = new Table()
{
Name = "PRODUCT",
Columns = new string[] { "product_id", "name", "description", "version" },
};
ValidateTable(expectedProductTable, tables);
// Products Order Table
var expectedProductsOrderTable = new Table()
{
Name = "PRODUCTS_ORDER",
Columns = new string[] { "products_order_id", "date", "shipping_address_street", "shipping_address_number", "shipping_address_city_id", "customer_id", "version" },
};
ValidateTable(expectedProductsOrderTable, tables);
// Products Order Items Table
var expectedProductsOrderItemsTable = new Table()
{
Name = "PRODUCTS_ORDER_ITEMS",
Columns = new string[] { "products_order_id", "item_index", "quantity", "price", "product_id" },
};
ValidateTable(expectedProductsOrderItemsTable, tables);
// City Table
var expectedCityTable = new Table()
{
Name = "CITY",
Columns = new string[] { "city_id", "name", "zip_code_id", "state_id", "version" },
};
ValidateTable(expectedCityTable, tables);
// City Table
var expectedStateTable = new Table()
{
Name = "STATE",
Columns = new string[] { "state_id", "name", "version" },
};
ValidateTable(expectedStateTable, tables);
// Zip Code Table
var expectedZipCodeTable = new Table()
{
Name = "ZIP_CODE",
Columns = new string[] { "zip_code_id", "postal_code", "city_id", "version" },
};
ValidateTable(expectedZipCodeTable, tables);
}
private void ValidateResult(IList<Table> tables)
{
// Category Table
var expectedCategoryTable = new Table()
{
Name = "category",
Columns = new string[] { "categoryId", "name", "version" },
};
ValidateTable(expectedCategoryTable, tables);
// Category Product Table
var expectedCategoryProductTable = new Table()
{
Name = "category_product",
Columns = new string[] { "categoryId", "productId" },
};
ValidateTable(expectedCategoryProductTable, tables);
// Customer Table
var expectedCustomerTable = new Table()
{
Name = "customer",
Columns = new string[] { "customerId", "firstName", "lastName", "code", "dateOfBirth", "address_street", "address_number", "address_cityId", "version" },
};
ValidateTable(expectedCustomerTable, tables);
// Product Table
var expectedProductTable = new Table()
{
Name = "product",
Columns = new string[] { "productId", "name", "description", "version" },
};
ValidateTable(expectedProductTable, tables);
// Products Order Table
var expectedProductsOrderTable = new Table()
{
Name = "products_order",
Columns = new string[] { "productsOrderId", "date", "shippingAddress_street", "shippingAddress_number", "shippingAddress_cityId", "customerId", "version" },
};
ValidateTable(expectedProductsOrderTable, tables);
// Products Order Items Table
var expectedProductsOrderItemsTable = new Table()
{
Name = "products_order_items",
Columns = new string[] { "productsOrderId", "itemIndex", "quantity", "price", "productId" },
};
ValidateTable(expectedProductsOrderItemsTable, tables);
// City Table
var expectedCityTable = new Table()
{
Name = "city",
Columns = new string[] { "cityId", "name", "zipCodeId", "stateId", "version" },
};
ValidateTable(expectedCityTable, tables);
// City Table
var expectedStateTable = new Table()
{
Name = "state",
Columns = new string[] { "stateId", "name", "version" },
};
ValidateTable(expectedStateTable, tables);
// Zip Code Table
var expectedZipCodeTable = new Table()
{
Name = "zip_code",
Columns = new string[] { "zipCodeId", "postalCode", "cityId", "version" },
};
ValidateTable(expectedZipCodeTable, tables);
}
private void ValidateTable(Table expectedTable, IList<Table> result)
{
// Table & Name
var actualTable = result.SingleOrDefault(t => t.Name.Equals(expectedTable.Name));
Assert.IsNotNull(actualTable);
// Columns
Assert.AreEqual(expectedTable.Columns.Count, actualTable.Columns.Count);
Assert.IsTrue(expectedTable.Columns.All(c => actualTable.Columns.Contains(c)));
}
/// <summary>
/// Gets the list of database tables
/// </summary>
/// <returns>The list of table descriptions</returns>
protected IList<Table> GetTables()
{
IList<Table> result = new List<Table>();
var connString = System.Configuration.ConfigurationManager.ConnectionStrings[ConnectionStringNames.SQLServerDB].ConnectionString;
using (var connection = new SqlConnection(connString))
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "SELECT t.Table_Schema, t.Table_Name, (SELECT STUFF((SELECT ',' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = t.TABLE_NAME FOR XML PATH('')) ,1,1,'')) as Column_Names, (SELECT STUFF((SELECT ',' + [name] FROM sysobjects WHERE [xtype] = 'PK'AND [parent_obj] = OBJECT_ID(t.Table_Schema + '.' + t.Table_Name) FOR XML PATH('')) ,1,1,'')) as Primary_Keys, (SELECT STUFF((SELECT ',' + [name] FROM sysobjects WHERE [xtype] = 'F'AND [parent_obj] = OBJECT_ID(t.Table_Schema + '.' + t.Table_Name) FOR XML PATH('')) ,1,1,'')) as Foreign_Keys FROM information_schema.tables t WHERE table_type = 'BASE TABLE' ORDER BY t.Table_Name";
var reader = command.ExecuteReader();
while (reader.Read())
{
Table table = new Table()
{
Schema = (string)reader["Table_Schema"],
Name = (string)reader["Table_Name"],
Columns = (reader["Column_Names"] != System.DBNull.Value) ? ((string)reader["Column_Names"]).Split(',').ToList() : new List<string>(),
PrimaryKey = (reader["Primary_Keys"] != System.DBNull.Value) ? ((string)reader["Primary_Keys"]).Split(',').FirstOrDefault() : default(string),
ForeignKeys = (reader["Foreign_Keys"] != System.DBNull.Value) ? ((string)reader["Foreign_Keys"]).Split(',').ToList() : new List<string>(),
};
result.Add(table);
}
}
return result;
}
