/// <summary>
/// Test CUBRIDSchemaProvider GetForeignKeys() method
/// </summary>
private static void Test_GetForeignKeys()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
CUBRIDSchemaProvider schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetForeignKeys(new string[] { "game" });
Debug.Assert(dt.Columns.Count == 9);
Debug.Assert(dt.Rows.Count == 2);
Debug.Assert(dt.Rows[0][0].ToString() == "event");
Debug.Assert(dt.Rows[0][1].ToString() == "code");
Debug.Assert(dt.Rows[0][2].ToString() == "game");
Debug.Assert(dt.Rows[0][3].ToString() == "event_code");
Debug.Assert(dt.Rows[0][4].ToString() == "1");
Debug.Assert(dt.Rows[0][5].ToString() == "1");
Debug.Assert(dt.Rows[0][6].ToString() == "1");
Debug.Assert(dt.Rows[0][7].ToString() == "fk_game_event_code");
Debug.Assert(dt.Rows[0][8].ToString() == "pk_event_code");
Debug.Assert(dt.Rows[1][0].ToString() == "athlete");
Debug.Assert(dt.Rows[1][1].ToString() == "code");
Debug.Assert(dt.Rows[1][2].ToString() == "game");
Debug.Assert(dt.Rows[1][3].ToString() == "athlete_code");
Debug.Assert(dt.Rows[1][4].ToString() == "1");
Debug.Assert(dt.Rows[1][5].ToString() == "1");
Debug.Assert(dt.Rows[1][6].ToString() == "1");
Debug.Assert(dt.Rows[1][7].ToString() == "fk_game_athlete_code");
Debug.Assert(dt.Rows[1][8].ToString() == "pk_athlete_code");
}
}
/// <summary>
/// Test CREATE Database Stored Functions calls
/// </summary>
private static void Test_CreateFunction()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
try
{
TestCases.ExecuteSQL("drop function sp1", conn);
}
catch { }
string sql = "CREATE FUNCTION sp1(a int) RETURN string AS LANGUAGE JAVA NAME 'SpTest.test1(int) return java.lang.String'";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
CUBRIDSchemaProvider schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetProcedures(null);
Debug.Assert(dt.Rows.Count == 1);
TestCases.ExecuteSQL("drop function sp1", conn);
}
}
/// <summary>
/// Test CUBRIDSchemaProvider GetColumns() method
/// </summary>
private static void Test_GetColumns()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
CUBRIDSchemaProvider schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetColumns(new string[] { "game" });
Debug.Assert(dt.Columns.Count == 11);
Debug.Assert(dt.Rows.Count == 7);
Debug.Assert(dt.Rows[0][3].ToString() == "host_year");
Debug.Assert(dt.Rows[1][3].ToString() == "event_code");
}
}
/// <summary>
/// Test CUBRIDSchemaProvider GetTables() method
/// </summary>
private static void Test_GetTables()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
CUBRIDSchemaProvider schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetTables(new string[] { "%" });
Debug.Assert(dt.Columns.Count == 3);
Debug.Assert(dt.Rows.Count == 10);
Debug.Assert(dt.Rows[0][0].ToString() == "demodb");
Debug.Assert(dt.Rows[0][1].ToString() == "demodb");
Debug.Assert(dt.Rows[0][2].ToString() == "stadium");
}
}
public List<Table> GetTables(string owner)
{
var tables = new List<Table>();
var conn = new CUBRIDConnection(connectionStr);
conn.Open();
try
{
using (conn)
{
var schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetTables(new[] { "%" });
for (var i = 0; i < dt.Rows.Count; i++)
{
tables.Add(new Table { Name = dt.Rows[i][2].ToString() });
}
}
}
finally
{
conn.Close();
}
tables.Sort((x, y) => String.Compare(x.Name, y.Name, StringComparison.Ordinal));
return tables;
}
public IList<Column> GetTableDetails(Table table, string owner)
{
var columns = new List<Column>();
var conn = new CUBRIDConnection(connectionStr);
conn.Open();
try
{
using (conn)
{
var schema = new CUBRIDSchemaProvider(conn);
DataTable dt_fk = schema.GetForeignKeys(new[] { table.Name.ToLower() });
string sqlInfo = String.Format("select * from [{0}] limit 1", table.Name.ToLower());
var adapter = new CUBRIDDataAdapter(sqlInfo, conn);
var tableInfo = new DataTable();
adapter.FillSchema(tableInfo, SchemaType.Source);
using (var reader = new DataTableReader(tableInfo))
{
DataTable schemaTable = reader.GetSchemaTable();
for (var k = 0; k < schemaTable.Rows.Count; k++)
{
string columnName = schemaTable.Rows[k]["ColumnName"].ToString().ToLower();
var isUnique = (bool)schemaTable.Rows[k]["IsUnique"];
var isNullable = (bool)schemaTable.Rows[k]["AllowDBNull"];
var isPrimaryKey = (bool)schemaTable.Rows[k]["IsKey"];
var isIdentity = (bool)schemaTable.Rows[k]["IsAutoIncrement"];
var dataLength = (int)schemaTable.Rows[k]["ColumnSize"];
int dataPrecision = 0;
if (schemaTable.Rows[k]["NumericPrecision"].ToString() != String.Empty)
{
dataPrecision = (int)schemaTable.Rows[k]["NumericPrecision"];
}
int dataScale = 0;
if (schemaTable.Rows[k]["NumericScale"].ToString() != String.Empty)
{
dataScale = (int)schemaTable.Rows[k]["NumericScale"];
}
bool isForeignKey = false;
string fkTableName = "";
string constraintName = "";
for (var i_fk = 0; i_fk < dt_fk.Rows.Count; i_fk++)
{
if (dt_fk.Rows[i_fk]["FKCOLUMN_NAME"].ToString().ToLower() == columnName)
{
isForeignKey = true;
fkTableName = dt_fk.Rows[i_fk]["PKTABLE_NAME"].ToString();
constraintName = dt_fk.Rows[i_fk]["FK_NAME"].ToString();
break;
}
}
string dataType;
using (var cmd = new CUBRIDCommand(sqlInfo, conn))
{
using (var CUBRIDReader = (CUBRIDDataReader)cmd.ExecuteReader())
{
CUBRIDReader.Read();
dataType = CUBRIDReader.GetColumnTypeName(k);
}
}
var m = new DataTypeMapper();
columns.Add(new Column
{
Name = columnName,
DataType = dataType,
IsNullable = isNullable,
IsUnique = isUnique,
IsPrimaryKey = isPrimaryKey,
IsForeignKey = isForeignKey,
IsIdentity = isIdentity,
DataLength = dataLength,
DataPrecision = dataPrecision,
DataScale = dataScale,
ForeignKeyTableName = fkTableName,
ConstraintName = constraintName,
MappedDataType =
m.MapFromDBType(ServerType.CUBRID, dataType, null, null, null).ToString(),
});
}
}
}
table.Columns = columns;
table.Owner = owner;
table.PrimaryKey = DeterminePrimaryKeys(table);
table.HasManyRelationships = DetermineHasManyRelationships(table);
}
finally
{
conn.Close();
}
return columns;
}
/// <summary>
/// Test CUBRIDSchemaProvider GetIndexes() method
/// </summary>
private static void Test_GetIndexes()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
CUBRIDSchemaProvider schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetIndexes(new string[] { "game" });
Debug.Assert(dt.Columns.Count == 9);
Debug.Assert(dt.Rows.Count == 5);
Debug.Assert(dt.Rows[3][2].ToString() == "pk_game_host_year_event_code_athlete_code"); //Index name
Debug.Assert(dt.Rows[3][4].ToString() == "True"); //Is PK?
}
}
/// <summary>
/// Test CUBRIDSchemaProvider GetProcedures() method
/// </summary>
private static void Test_GetProcedures()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
CUBRIDSchemaProvider schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetProcedures(null);
Debug.Assert(dt.Columns.Count == 7);
Debug.Assert(dt.Rows.Count == 0);
}
}
public void GetDatabasesTwoFilterExceptionTest()
{
CUBRIDConnection connection = new CUBRIDConnection(); // TODO: Initialize to an appropriate value
connection.ConnectionString = DBHelper.connString;
connection.Open();
CUBRIDSchemaProvider target = new CUBRIDSchemaProvider(connection); // TODO: Initialize to an appropriate value
string[] filters = new string[] { "demodb","demodb2" }; // TODO: Initialize to an appropriate value
DataTable expected = new DataTable(); // TODO: Initialize to an appropriate value
DataTable actual;
string ss = null;
try
{
actual = target.GetDatabases(filters);
}
catch (ArgumentException e)
{
ss = e.Message;
}
Assert.IsNotNull(ss.Length);
}
/// <summary>
/// Test CUBRIDSchemaProvider GetUsers() method
/// </summary>
private static void Test_GetUsers()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
CUBRIDSchemaProvider schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetUsers(null);
Debug.Assert(dt.Columns.Count == 1);
Debug.Assert(dt.Rows.Count >= 2);
Debug.Assert(dt.Rows[0][0].ToString().ToUpper() == "DBA");
Debug.Assert(dt.Rows[1][0].ToString().ToUpper() == "PUBLIC");
}
}
/// <summary>
/// Test CREATE Stored Procedures calls
/// </summary>
private static void Test_CreateProcedure()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
try
{
TestCases.ExecuteSQL("drop function sp2", conn);
}
catch { }
string sql = "CREATE PROCEDURE \"sp2\"() AS LANGUAGE JAVA NAME 'SpTest.test2()'";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
CUBRIDSchemaProvider schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetProcedures(null);
Debug.Assert(dt.Rows.Count == 1);
TestCases.ExecuteSQL("drop procedure sp2", conn);
}
}
public void GetViewsTest2()
{
CUBRIDConnection connection = new CUBRIDConnection(); // TODO: Initialize to an appropriate value
connection.ConnectionString = DBHelper.connString;
connection.Open();
DBHelper.ExecuteSQL("drop view if exists test_view", connection);
DBHelper.ExecuteSQL("create view test_view as select * from code;", connection);
CUBRIDSchemaProvider target = new CUBRIDSchemaProvider(connection); // TODO: Initialize to an appropriate value
string[] filters = null; // TODO: Initialize to an appropriate value
DataTable actual;
actual = target.GetViews(filters);
//Assert.AreEqual(1, actual.Rows.Count);
Console.WriteLine(actual.Rows[0][0]);
}
public void GetViewsTest_null()
{
CUBRIDConnection connection = new CUBRIDConnection(); // TODO: Initialize to an appropriate value
connection.ConnectionString=DBHelper.connString;
connection.Open();
CUBRIDSchemaProvider target = new CUBRIDSchemaProvider(connection); // TODO: Initialize to an appropriate value
string[] filters = null; // TODO: Initialize to an appropriate value
DataTable actual;
actual = target.GetViews(filters);
Assert.AreEqual(0, actual.Rows.Count);
}
public void GetTablesNoFilterTest()
{
CUBRIDConnection connection = new CUBRIDConnection(); // TODO: Initialize to an appropriate value
connection.ConnectionString = DBHelper.connString;
CUBRIDSchemaProvider target = new CUBRIDSchemaProvider(connection); // TODO: Initialize to an appropriate value
string[] filters =null; // TODO: Initialize to an appropriate value
DataTable actual;
string ss=null;
try
{
actual = target.GetTables(filters);
}
catch (Exception e) {
ss = e.Message;
}
Assert.IsNotNull(ss.Length);
connection.Close();
}
public void GetDatabasesPercentTwoResultTest()
{
CUBRIDConnection connection = new CUBRIDConnection(); // TODO: Initialize to an appropriate value
connection.ConnectionString = DBHelper.connString;
connection.Open();
CUBRIDSchemaProvider target = new CUBRIDSchemaProvider(connection); // TODO: Initialize to an appropriate value
string[] filters = new string[] { "%demo%" }; // TODO: Initialize to an appropriate value
DataTable expected = new DataTable(); // TODO: Initialize to an appropriate value
DataTable actual;
actual = target.GetDatabases(filters);
Assert.AreEqual(1, actual.Rows.Count);
}
private IList<HasMany> DetermineHasManyRelationships(Table table)
{
var hasManyRelationships = new List<HasMany>();
var conn = new CUBRIDConnection(connectionStr);
conn.Open();
try
{
using (conn)
{
var schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetForeignKeys(new[] { "%" });
for (var i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["PKTABLE_NAME"].ToString() == table.Name)
{
var newHasManyItem = new HasMany
{
Reference = dt.Rows[i]["FKTABLE_NAME"].ToString(),
ConstraintName = dt.Rows[i]["FK_NAME"].ToString(),
ReferenceColumn = dt.Rows[i]["FKCOLUMN_NAME"].ToString()
};
hasManyRelationships.Add(newHasManyItem);
}
}
}
}
finally
{
conn.Close();
}
return hasManyRelationships;
}
public IList<string> GetOwners()
{
var owners = new List<string>();
var conn = new CUBRIDConnection(connectionStr);
conn.Open();
try
{
using (conn)
{
var schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetUsers(new[] { "%" });
for (var i = 0; i < dt.Rows.Count; i++)
{
owners.Add(dt.Rows[i][0].ToString().ToLower());
}
}
}
finally
{
conn.Close();
}
return owners;
}
public void CUBRIDSchemaProviderConstructorTest()
{
CUBRIDConnection connection = new CUBRIDConnection(); ; // TODO: Initialize to an appropriate value
connection.ConnectionString = DBHelper.connString;
connection.Open();
CUBRIDSchemaProvider target = new CUBRIDSchemaProvider(connection);
CUBRIDCommand cmd = new CUBRIDCommand();
cmd.Connection = connection;
cmd.CommandText = "select * from nation order by code asc";
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
reader.Read();
Assert.AreEqual(4, reader.FieldCount);
cmd.Close();
reader.Close();
connection.Close();
}
