/// <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[1][0].ToString() == "event");
Debug.Assert(dt.Rows[1][1].ToString() == "code");
Debug.Assert(dt.Rows[1][2].ToString() == "game");
Debug.Assert(dt.Rows[1][3].ToString() == "event_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_event_code");
Debug.Assert(dt.Rows[1][8].ToString() == "pk_event_code");
Debug.Assert(dt.Rows[0][0].ToString() == "athlete");
Debug.Assert(dt.Rows[0][1].ToString() == "code");
Debug.Assert(dt.Rows[0][2].ToString() == "game");
Debug.Assert(dt.Rows[0][3].ToString() == "athlete_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_athlete_code");
Debug.Assert(dt.Rows[0][8].ToString() == "pk_athlete_code");
}
}
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();
}
/// <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 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 List <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);
}
private List <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 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);
}
/// <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 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 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);
}
public void GetViewsTest()
{
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);
}
/// <summary>
/// Test CUBRIDSchemaProvider GetIndexColumns() method
/// </summary>
private static void Test_GetIndexColumns()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
CUBRIDSchemaProvider schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetIndexColumns(new string[] { "game" });
Debug.Assert(dt.Columns.Count == 7);
Debug.Assert(dt.Rows.Count == 5);
Debug.Assert(dt.Rows[0][2].ToString() == "pk_game_host_year_event_code_athlete_code");
}
}
/// <summary>
/// Test CUBRIDSchemaProvider GetDatabases() method
/// </summary>
private static void Test_GetDatabases()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
CUBRIDSchemaProvider schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetDatabases(new string[] { "demo%" });
Debug.Assert(dt.Columns.Count == 2);
Debug.Assert(dt.Rows.Count >= 1);
Debug.Assert(dt.Rows[0][0].ToString() == "demodb");
Debug.Assert(dt.Rows[0][1].ToString() == "demodb");
}
}
/// <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 CUBRIDSchemaProvider class
/// </summary>
private static void Test_SchemaProvider_FunctionTypes()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
DataTable rw = CUBRIDSchemaProvider.GetReservedWords();
string[] nf = CUBRIDSchemaProvider.GetNumericFunctions();
string[] sf = CUBRIDSchemaProvider.GetStringFunctions();
Debug.Assert(nf.GetValue(0).ToString() == "AVG");
Debug.Assert(nf.GetValue(nf.Length - 1).ToString() == "VARIANCE");
Debug.Assert(sf.GetValue(0).ToString() == "BIT_LENGTH");
Debug.Assert(sf.GetValue(sf.Length - 1).ToString() == "UPPER");
Debug.Assert(rw.Rows[0][0].ToString() == "ABSOLUTE");
Debug.Assert(rw.Rows[rw.Rows.Count - 1][0].ToString() == "ZONE");
}
}
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();
}
/// <summary>
/// Test CUBRIDSchemaProvider data types functions
/// </summary>
private static void Test_SchemaProvider_DataTypes()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
DataTable dt = CUBRIDSchemaProvider.GetDataTypes();
Debug.Assert(dt.Rows.Count == 19);
//SetDataTypeInfo(dt, "BIGINT", CUBRIDDataType.CCI_U_TYPE_BIGINT, typeof(Int32),
//ToBool(IsAutoIncrementable.Yes), ToBool(IsFixedLength.Yes), ToBool(IsFixedPrecisionScale.Yes), ToBool(IsLong.Yes), ToBool(IsNullable.Yes));
Debug.Assert(dt.Rows[0]["TypeName"].ToString() == "BIGINT");
Debug.Assert((CUBRIDDataType)dt.Rows[0]["ProviderDataType"] == CUBRIDDataType.CCI_U_TYPE_BIGINT);
Debug.Assert((Type)dt.Rows[0]["DbType"] == typeof(Int32));
Debug.Assert(dt.Rows[0]["Size"].ToString() == String.Empty);
Debug.Assert((bool)dt.Rows[0]["IsLong"] == true);
Debug.Assert((bool)dt.Rows[0]["IsFixedLength"] == true);
Debug.Assert((bool)dt.Rows[0]["IsFixedPrecisionScale"] == true);
Debug.Assert((bool)dt.Rows[0]["IsNullable"] == true);
Debug.Assert((bool)dt.Rows[0]["IsAutoIncrementable"] == true);
}
}
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);
}
public Task <IList <Column> > GetTableDetails(Table table, string owner)
{
IList <Column> 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),
});
}
}
}
table.Columns = columns;
table.Owner = owner;
table.PrimaryKey = DeterminePrimaryKeys(table);
table.HasManyRelationships = DetermineHasManyRelationships(table);
}
finally
{
conn.Close();
}
return(Task.FromResult(columns));
}
