private IList <ITableSourceForeignKeyInfo> GetForeignKeysInfo(iDB2TableSourceInfo tableSource)
{
IList <ITableSourceForeignKeyInfo> foreignKeys = new List <ITableSourceForeignKeyInfo>();
string query = "SELECT FKS.*, RCS.DELETE_RULE AS CASCADE "
+ "FROM SYSIBM.SQLFOREIGNKEYS FKS "
+ "LEFT JOIN SYSIBM.REFERENTIAL_CONSTRAINTS RCS ON RCS.CONSTRAINT_NAME = FKS.FK_NAME "
+ "WHERE FKTABLE_SCHEM = '" + tableSource.Database.Identifier + "' "
+ "and FKTABLE_NAME = '" + tableSource.Name + "'"
+ "and CONSTRAINT_SCHEMA = '" + tableSource.Database.Identifier + "'";
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, query);
cmd.CommandTimeout = QueryTimeout;
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
string foreignKeyName = (string)reader["FK_NAME"];
string columnName = (string)reader["FKCOLUMN_NAME"];
string referencedSchemaName = (string)reader["PKTABLE_SCHEM"];
string referencedTableName = (string)reader["PKTABLE_NAME"];
string referencedColumnName = (string)reader["PKCOLUMN_NAME"];
bool isCascadeDelete = "CASCADE".EqualsIgnoreCase((string)reader["CASCADE"]);
string qualifiedReferencedTableName = GetQualifiedIdentifier(referencedSchemaName, referencedTableName);
var a = tableSource.Database;
ITableSourceInfo referencedTableSource = new iDB2TableSourceInfo(DatabaseServices, tableSource.Database, referencedTableName, qualifiedReferencedTableName);
ITableSourceForeignKeyInfo foreignKeyInfo = new iDB2TableSourceForeignKeyInfo(tableSource, foreignKeyName, columnName, referencedTableSource, referencedColumnName, isCascadeDelete);
foreignKeys.Add(foreignKeyInfo);
}
}
return(foreignKeys);
}
}
private IList <ITableSourceColumnInfo> GetColumnsInfo(iDB2TableSourceInfo tableSource)
{
IList <ITableSourceColumnInfo> columnsInfo = new List <ITableSourceColumnInfo>();
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
string query = @"SELECT COLS.*, PKS.PK_NAME, CHK.CHECK_CLAUSE, UDTS.ORDERING_ROUT_NAME
FROM QSYS2.SYSCOLUMNS COLS
INNER JOIN QSYS2.SYSTABLES TAB ON TAB.TABLE_NAME = COLS.TABLE_NAME AND TAB.TABLE_SCHEMA = COLS.TABLE_SCHEMA
LEFT JOIN SYSIBM.SQLPRIMARYKEYS PKS ON PKS.COLUMN_NAME = COLS.COLUMN_NAME AND PKS.TABLE_NAME = COLS.TABLE_NAME AND PKS.TABLE_SCHEM = COLS.TABLE_SCHEMA
LEFT JOIN QSYS2.SYSCHKCST CHK ON CHK.CHECK_CLAUSE LIKE COLS.COLUMN_NAME ||' IN%'
LEFT JOIN SYSIBM.UDT_S UDTS ON UDTS.UDT_NAME = COLS.USER_DEFINED_TYPE_NAME
WHERE COLS.TABLE_NAME = '" + tableSource.Name + @"'
AND COLS.SYSTEM_TABLE_SCHEMA = '" + tableSource.Database.Identifier + @"'
AND ( CHK.CONSTRAINT_NAME IS NULL
OR (CHK.CONSTRAINT_NAME IN ( SELECT CONSTRAINT_NAME
FROM QSYS2.SYSCST CST
WHERE CST.SYSTEM_TABLE_NAME = TAB.SYSTEM_TABLE_NAME
AND CST.TABLE_SCHEMA = TAB.TABLE_SCHEMA)))";
IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, query);
cmd.CommandTimeout = QueryTimeout;
using (IDataReader reader = cmd.ExecuteReader()) {
int primaryKeyColumnCount = 0;
while (reader.Read())
{
string columnName = (string)reader["COLUMN_NAME"];
IDataTypeInfo datatype = CreateDataTypeInfo(reader["DATA_TYPE"] == DBNull.Value ? null : (string)reader["DATA_TYPE"],
reader["LENGTH"] == DBNull.Value ? 0 : Convert.ToInt32(reader["LENGTH"]),
reader["NUMERIC_PRECISION"] == DBNull.Value ? 0 : Convert.ToInt32(reader["NUMERIC_PRECISION"]),
reader["NUMERIC_SCALE"] == DBNull.Value ? 0 : Convert.ToInt32(reader["NUMERIC_SCALE"]),
reader["CHECK_CLAUSE"] == DBNull.Value ? null : (string)reader["CHECK_CLAUSE"],
reader["ORDERING_ROUT_NAME"] == DBNull.Value ? null : (string)reader["ORDERING_ROUT_NAME"],
reader["CCSID"] == DBNull.Value ? 0 : Convert.ToInt32(reader["CCSID"]));
bool isMandatory = "N".EqualsIgnoreCase((string)reader["IS_NULLABLE"]);
bool isPrimaryKey = (reader["PK_NAME"] != DBNull.Value);
bool isAutoGenerated = "YES".EqualsIgnoreCase((string)reader["IS_IDENTITY"]);
iDB2TableSourceColumnInfo info = new iDB2TableSourceColumnInfo(tableSource, columnName, datatype, isMandatory, isPrimaryKey, isAutoGenerated);
columnsInfo.Add(info);
primaryKeyColumnCount += isPrimaryKey ? 1 : 0;
}
if (primaryKeyColumnCount > 1)
{
//we don't support composite primary keys so setting all to non primary key
IList <ITableSourceColumnInfo> columnsInfoCopy = new List <ITableSourceColumnInfo>();
foreach (ITableSourceColumnInfo c in columnsInfo)
{
columnsInfoCopy.Add(new iDB2TableSourceColumnInfo(c.TableSource, c.Name, c.DataType, c.IsMandatory, false, c.IsAutoGenerated));
}
columnsInfo = columnsInfoCopy;
}
}
}
return(columnsInfo);
}
private bool CheckTableExists(IDbConnection conn, iDB2TableSourceInfo tableSource)
{
String query = "SELECT TABLE_NAME "
+ "FROM QSYS2.SYSTABLES "
+ "WHERE TABLE_NAME = '" + tableSource.Name + "' and SYSTEM_TABLE_SCHEMA = '" + tableSource.Database.Identifier + "'";
IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, query);
cmd.CommandTimeout = QueryTimeout;
using (IDataReader reader = cmd.ExecuteReader()) {
return(reader.Read());
}
}
public override IEnumerable <ITableSourceColumnInfo> GetTableSourceColumns(ITableSourceInfo tableSource)
{
iDB2TableSourceInfo tableInfo = tableSource as iDB2TableSourceInfo;
if (tableInfo == null)
{
return(null);
}
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
if (!CheckTableExists(conn, tableInfo))
{
throw new IntrospectionServiceException("'" + tableSource.DisplayName + "' is not a valid physical table name.");
}
}
return(GetColumnsInfo(tableInfo));
}