private IList <ITableSourceForeignKeyInfo> GetForeignKeysInfo(DB2LUWTableSourceInfo 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 DB2LUWTableSourceInfo(DatabaseServices, tableSource.Database, referencedTableName, qualifiedReferencedTableName);
ITableSourceForeignKeyInfo foreignKeyInfo = new DB2LUWTableSourceForeignKeyInfo(tableSource, foreignKeyName, columnName, referencedTableSource, referencedColumnName, isCascadeDelete);
foreignKeys.Add(foreignKeyInfo);
}
}
return(foreignKeys);
}
}
private IList <ITableSourceColumnInfo> GetColumnsInfo(DB2LUWTableSourceInfo tableSource)
{
IList <ITableSourceColumnInfo> columnsInfo = new List <ITableSourceColumnInfo>();
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
string query = @"SELECT COLS.*, PKS.PK_NAME, cast(CHK.TEXT as varchar(2000)) as CHECK_CLAUSE, UDTS.ORDERING_ROUT_NAME
FROM SYSCAT.COLUMNS COLS
INNER JOIN SYSCAT.TABLES TAB ON TAB.TABNAME = COLS.TABNAME AND TAB.TABSCHEMA = COLS.TABSCHEMA
LEFT JOIN SYSIBM.SQLPRIMARYKEYS PKS ON PKS.COLUMN_NAME = COLS.COLNAME AND PKS.TABLE_NAME = COLS.TABNAME AND PKS.TABLE_SCHEM = COLS.TABSCHEMA
LEFT JOIN SYSCAT.TABDEP DEP ON DEP.TABSCHEMA = TAB.TABSCHEMA and DEP.TABNAME = TAB.TABNAME
LEFT JOIN SYSCAT.COLCHECKS COL_CHK
ON (TAB.TYPE = 'T' and COL_CHK.TABNAME = TAB.TABNAME and COL_CHK.TABSCHEMA = TAB.TABSCHEMA and COLS.COLNAME = COL_CHK.COLNAME)
OR (TAB.TYPE in ('V', 'L') and COL_CHK.TABNAME = DEP.BNAME and COL_CHK.TABSCHEMA = TAB.TABSCHEMA and COLS.COLNAME = COL_CHK.COLNAME )
LEFT JOIN SYSCAT.CHECKS CHK on COL_CHK.constname = CHK.constname and COL_CHK.tabschema = CHK.tabschema and COL_CHK.tabname = CHK.tabname
LEFT JOIN SYSIBM.UDT_S UDTS ON UDTS.UDT_NAME = COLS.TYPENAME
WHERE COLS.TABNAME = '" + tableSource.Name + "' and TAB.TABSCHEMA = '" + tableSource.Database.Identifier + "'";
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["COLNAME"];
IDataTypeInfo datatype = CreateDataTypeInfo(reader["TYPENAME"] == DBNull.Value ? null : (string)reader["TYPENAME"],
reader["LENGTH"] == DBNull.Value ? 0 : Convert.ToInt32(reader["LENGTH"]),
reader["LENGTH"] == DBNull.Value ? 0 : Convert.ToInt32(reader["LENGTH"]),
reader["SCALE"] == DBNull.Value ? 0 : Convert.ToInt32(reader["SCALE"]),
reader["CHECK_CLAUSE"] == DBNull.Value ? null : (string)reader["CHECK_CLAUSE"],
reader["ORDERING_ROUT_NAME"] == DBNull.Value ? null : (string)reader["ORDERING_ROUT_NAME"],
reader["CODEPAGE"] == DBNull.Value ? 0 : Convert.ToInt32(reader["CODEPAGE"]));
bool isMandatory = "N".EqualsIgnoreCase((string)reader["NULLS"]);
bool isPrimaryKey = (reader["PK_NAME"] != DBNull.Value);
bool isAutoGenerated = "Y".EqualsIgnoreCase((string)reader["IDENTITY"]);
DB2LUWTableSourceColumnInfo info = new DB2LUWTableSourceColumnInfo(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 DB2LUWTableSourceColumnInfo(c.TableSource, c.Name, c.DataType, c.IsMandatory, false, c.IsAutoGenerated));
}
columnsInfo = columnsInfoCopy;
}
}
}
return(columnsInfo);
}
private bool CheckTableExists(IDbConnection conn, DB2LUWTableSourceInfo tableSource)
{
String query = "SELECT TABNAME as TABLE_NAME "
+ "FROM SYSCAT.TABLES "
+ "WHERE TABNAME = '" + tableSource.Name + "' and TABSCHEMA = '" + 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)
{
DB2LUWTableSourceInfo tableInfo = tableSource as DB2LUWTableSourceInfo;
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));
}
