protected override IEnumerable <ViewColumn> GetViewColumns(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
var connpar = GetConnectionParameter(database);
SqlCommand sql = $@"
SELECT T.TABLE_CATALOG,
T.TABLE_SCHEMA,
T.TABLE_NAME,
T.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS T
JOIN INFORMATION_SCHEMA.VIEWS V ON V.TABLE_NAME = T.TABLE_NAME
WHERE (T.TABLE_SCHEMA = '{connpar.Database}') AND
(T.TABLE_NAME = ?TABLENAME OR ?TABLENAME IS NULL) AND
(T.COLUMN_NAME = ?COLUMNNAME OR ?COLUMNNAME IS NULL)
ORDER BY T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME";
restrictionValues
.Parameterize(parameters, "TABLENAME", nameof(ViewColumn.ViewName))
.Parameterize(parameters, "COLUMNNAME", nameof(ViewColumn.Name));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new ViewColumn
{
Catalog = wrapper.GetString(reader, 0),
Schema = wrapper.GetString(reader, 1),
ViewName = wrapper.GetString(reader, 2),
Name = wrapper.GetString(reader, 3)
}));
}
protected override IEnumerable <Table> GetTables(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
var connpar = GetConnectionParameter(database);
SqlCommand sql = $@"
SELECT
T.TABLE_CATALOG,
T.TABLE_SCHEMA,
T.TABLE_NAME,
T.TABLE_TYPE,
C.TABLE_COMMENT
FROM INFORMATION_SCHEMA.TABLES T
JOIN (
SELECT RELNAME AS TABLE_NAME,CAST(OBJ_DESCRIPTION(RELFILENODE, 'pg_class') AS VARCHAR) AS TABLE_COMMENT FROM PG_CLASS C
WHERE RELKIND = 'r' AND RELNAME NOT LIKE 'pg_%' AND RELNAME NOT LIKE 'sql_%'
) C ON T.TABLE_NAME = C.TABLE_NAME
WHERE (TABLE_CATALOG = '{connpar.Database}')
AND (T.TABLE_NAME = :NAME OR :NAME IS NULL)
ORDER BY T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME";
restrictionValues
.Parameterize(parameters, "NAME", nameof(Table.Name));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Table
{
Catalog = wrapper.GetString(reader, 0),
Schema = wrapper.GetString(reader, 1),
Name = wrapper.GetString(reader, 2),
Type = wrapper.GetString(reader, 3) == "BASE TABLE" ? TableType.BaseTable : TableType.SystemTable,
Description = wrapper.GetString(reader, 4)
}));
}
protected override IEnumerable <Procedure> GetProcedures(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
var connpar = GetConnectionParameter(database);
SqlCommand sql = $@"
SELECT
SPECIFIC_NAME,
ROUTINE_CATALOG,
ROUTINE_SCHEMA,
ROUTINE_NAME,
ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE (ROUTINE_SCHEMA = '{connpar.Database}')
AND (ROUTINE_NAME = @NAME OR (@NAME IS NULL))
AND (ROUTINE_TYPE = @TYPE OR (@TYPE IS NULL))
ORDER BY ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME";
restrictionValues
.Parameterize(parameters, "NAME", nameof(Procedure.Name))
.Parameterize(parameters, "TYPE", nameof(Procedure.Type));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Procedure
{
Catalog = wrapper.GetString(reader, 0),
Schema = wrapper.GetString(reader, 1),
Name = wrapper.GetString(reader, 2),
Type = wrapper.GetString(reader, 6)
}));
}
protected override IEnumerable <Index> GetIndexs(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
var connpar = GetConnectionParameter(database);
SqlCommand sql = $@"
SELECT DISTINCT
DB_NAME() AS CONSTRAINT_CATALOG,
CONSTRAINT_SCHEMA = USER_NAME(O.UID),
CONSTRAINT_NAME = X.NAME,
TABLE_CATALOG = DB_NAME(),
TABLE_SCHEMA = USER_NAME(O.UID),
TABLE_NAME = O.NAME,
INDEX_NAME = X.NAME
FROM SYSOBJECTS O, SYSINDEXES X, SYSINDEXKEYS XK
WHERE O.TYPE IN ('U') AND X.ID = O.ID AND O.ID = XK.ID AND X.INDID = XK.INDID AND XK.KEYNO < = X.KEYCNT AND
(DB_NAME() = '{connpar.Database}') AND
(O.NAME = @TABLE OR (@TABLE IS NULL)) AND
(X.NAME = @NAME OR (@NAME IS NULL)) ORDER BY TABLE_NAME, INDEX_NAME";
restrictionValues
.Parameterize(parameters, "TABLE", nameof(Index.TableName))
.Parameterize(parameters, "NAME", nameof(Index.Name));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Index
{
Catalog = wrapper.GetString(reader, 0),
Schema = wrapper.GetString(reader, 1),
TableName = wrapper.GetString(reader, 5),
Name = wrapper.GetString(reader, 6)
}));
}
protected override IEnumerable <View> GetViews(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
var connpar = GetConnectionParameter(database);
SqlCommand sql = $@"
SELECT T.TABLE_CATALOG,
T.TABLE_SCHEMA,
T.TABLE_NAME,
(SELECT VALUE FROM ::FN_LISTEXTENDEDPROPERTY('MS_Description','user',t.TABLE_SCHEMA,'view',T.TABLE_NAME,NULL,NULL)) COMMENTS
FROM
INFORMATION_SCHEMA.TABLES T
WHERE TABLE_TYPE = 'view'
AND (T.TABLE_CATALOG = '{connpar.Database}')
AND (T.TABLE_NAME = @NAME OR (@NAME IS NULL))
ORDER BY T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME";
restrictionValues.Parameterize(parameters, "NAME", nameof(View.Name));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new View
{
Catalog = wrapper.GetString(reader, 0),
Schema = wrapper.GetString(reader, 1),
Name = wrapper.GetString(reader, 2),
Description = wrapper.GetString(reader, 3)
}));
}
protected override IEnumerable <ViewColumn> GetViewColumns(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
var connpar = GetConnectionParameter(database);
SqlCommand sql = $@"
SELECT
VIEW_CATALOG,
VIEW_SCHEMA,
VIEW_NAME,
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
WHERE (VIEW_CATALOG = '{connpar.Database}')
AND (VIEW_NAME = @TABLE OR (@TABLE IS NULL))
AND (COLUMN_NAME = @COLUMN OR (@COLUMN IS NULL))
ORDER BY VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME";
restrictionValues
.Parameterize(parameters, "TABLE", nameof(ViewColumn.ViewName))
.Parameterize(parameters, "COLUMN", nameof(ViewColumn.Name));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new ViewColumn
{
Catalog = wrapper.GetString(reader, 0),
Schema = wrapper.GetString(reader, 1),
ViewName = wrapper.GetString(reader, 2),
Name = wrapper.GetString(reader, 6)
}));
}
protected override IEnumerable <Table> GetTables(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
var connpar = GetConnectionParameter(database);
SqlCommand sql = $@"
SELECT
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
TABLE_TYPE,
TABLE_COMMENT
FROM INFORMATION_SCHEMA.TABLES T
WHERE (T.TABLE_SCHEMA = '{connpar.Database}')
AND T.TABLE_TYPE <> 'VIEW'
AND (T.TABLE_NAME = ?NAME OR ?NAME IS NULL)
AND ((T.TABLE_TYPE = 'BASE TABLE' AND (@TABLETYPE IS NULL OR @TABLETYPE = 0)) OR (T.TABLE_TYPE = 'SYSTEM TABLE' AND @TABLETYPE = 1))
ORDER BY T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME";
restrictionValues
.Parameterize(parameters, "NAME", nameof(Table.Name))
.Parameterize(parameters, "TABLETYPE", nameof(Table.Type));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Table
{
Schema = wrapper.GetString(reader, 1),
Name = wrapper.GetString(reader, 2),
Type = wrapper.GetString(reader, 3) == "BASE TABLE" ? TableType.BaseTable : TableType.SystemTable,
Description = wrapper.GetString(reader, 4)
}));
}
protected override IEnumerable <Table> GetTables(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
var connpar = GetConnectionParameter(database);
SqlCommand sql = $@"
SELECT T.TABLE_CATALOG,
T.TABLE_SCHEMA,
T.TABLE_NAME,
T.TABLE_TYPE,
(SELECT VALUE FROM ::FN_LISTEXTENDEDPROPERTY('MS_Description','user',T.TABLE_SCHEMA,'table',T.TABLE_NAME,NULL,NULL)) COMMENTS
FROM
INFORMATION_SCHEMA.TABLES T
WHERE TABLE_TYPE <> 'view'
AND (T.TABLE_CATALOG = '{connpar.Database}')
AND (T.TABLE_NAME = @NAME OR (@NAME IS NULL))
AND ((T.TABLE_TYPE = 'BASE TABLE' AND (@TABLETYPE IS NULL OR @TABLETYPE = 0)) OR (T.TABLE_TYPE = 'SYSTEM TABLE' AND @TABLETYPE = 1))
ORDER BY T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME";
restrictionValues
.Parameterize(parameters, "NAME", nameof(Table.Name))
.Parameterize(parameters, "TABLETYPE", nameof(Table.Type));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Table
{
Catalog = wrapper.GetString(reader, 0),
Schema = wrapper.GetString(reader, 1),
Name = wrapper.GetString(reader, 2),
Type = wrapper.GetString(reader, 3) == "BASE TABLE" ? TableType.BaseTable : TableType.SystemTable,
Description = wrapper.GetString(reader, 4)
}));
}
protected override IEnumerable <Column> GetColumns(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
var connpar = GetConnectionParameter(database);
SqlCommand sql = $@"
SELECT
COL.TABLE_CATALOG,
COL.TABLE_SCHEMA,
COL.TABLE_NAME,
COL.ORDINAL_POSITION,
COL.COLUMN_NAME,
COL.DATA_TYPE,
COL.CHARACTER_MAXIMUM_LENGTH,
COL.NUMERIC_PRECISION,
COL.NUMERIC_SCALE,
COL.IS_NULLABLE,
COL.COLUMN_DEFAULT ,
DES.DESCRIPTION,
(CASE WHEN COL.ORDINAL_POSITION = CON.CONKEY[1] THEN 'YES' ELSE 'NO' END) IS_KEY
FROM
INFORMATION_SCHEMA.COLUMNS COL
JOIN (
SELECT RELNAME AS TABLE_NAME,CAST(OBJ_DESCRIPTION(RELFILENODE, 'pg_class') AS VARCHAR) AS TABLE_COMMENT FROM PG_CLASS C
WHERE RELKIND = 'r' AND RELNAME NOT LIKE 'pg_%' AND RELNAME NOT LIKE 'sql_%'
) C
ON col.TABLE_NAME = C.TABLE_NAME
LEFT JOIN PG_CLASS PCL
ON PCL.RELNAME = COL.TABLE_NAME
LEFT JOIN PG_DESCRIPTION DES
ON PCL.OID = DES.OBJOID AND COL.ORDINAL_POSITION = PCL.OID
LEFT JOIN PG_CONSTRAINT CON
ON CON.CONRELID = DES.OBJOID
WHERE (COL.TABLE_CATALOG = '{connpar.Database}')
AND (COL.TABLE_NAME = :TABLENAME OR :TABLENAME IS NULL)
AND (COL.COLUMN_NAME = :COLUMNNAME OR :COLUMNNAME IS NULL)
ORDER BY
COL.TABLE_CATALOG, COL.TABLE_SCHEMA, COL.TABLE_NAME, COL.ORDINAL_POSITION";
restrictionValues
.Parameterize(parameters, "TABLENAME", nameof(Column.TableName))
.Parameterize(parameters, "COLUMNNAME", nameof(Column.Name));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Column
{
Catalog = wrapper.GetString(reader, 0),
Schema = wrapper.GetString(reader, 1),
TableName = wrapper.GetString(reader, 2),
Name = wrapper.GetString(reader, 4),
DataType = wrapper.GetString(reader, 5),
Length = wrapper.GetInt64(reader, 6),
NumericPrecision = wrapper.GetInt32(reader, 7),
NumericScale = wrapper.GetInt32(reader, 8),
IsNullable = wrapper.GetString(reader, 9) == "YES",
IsPrimaryKey = wrapper.GetString(reader, 12) == "YES",
Default = wrapper.GetString(reader, 10),
Description = wrapper.GetString(reader, 11),
}));
}
protected override IEnumerable <Table> GetTables(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
var connpar = GetConnectionParameter(database);
SqlCommand sql = $@"
SELECT * FROM (
SELECT T.OWNER,
T.TABLE_NAME,
DECODE(T.OWNER,
'SYS',
'SYSTEM',
'SYSTEM',
'SYSTEM',
'SYSMAN',
'SYSTEM',
'CTXSYS',
'SYSTEM',
'MDSYS',
'SYSTEM',
'OLAPSYS',
'SYSTEM',
'ORDSYS',
'SYSTEM',
'OUTLN',
'SYSTEM',
'WKSYS',
'SYSTEM',
'WMSYS',
'SYSTEM',
'XDB',
'SYSTEM',
'ORDPLUGINS',
'SYSTEM',
'USER') AS TYPE,
C.COMMENTS
FROM ALL_TABLES T
JOIN ALL_TAB_COMMENTS C
ON T.OWNER = C.OWNER
AND T.TABLE_NAME = C.TABLE_NAME
) T
WHERE (T.OWNER = '{connpar.UserId.ToUpper()}') AND
(T.TABLE_NAME = :TABLENAME OR (:TABLENAME IS NULL)) AND
((T.TYPE = 'USER' AND (:TABLETYPE IS NULL OR :TABLETYPE = 0)) OR (T.TYPE = 'SYSTEM' AND :TABLETYPE = 1))
ORDER BY OWNER, TABLE_NAME";
restrictionValues
.Parameterize(parameters, "TABLENAME", nameof(Table.Name))
.Parameterize(parameters, "TABLETYPE", nameof(Table.Type));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Table
{
Schema = wrapper.GetString(reader, 0),
Name = wrapper.GetString(reader, 1),
Type = wrapper.GetString(reader, 2) == "USER" ? TableType.BaseTable : TableType.SystemTable,
Description = wrapper.GetString(reader, 3)
}));
}
protected override IEnumerable <ForeignKey> GetForeignKeys(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
var connpar = GetConnectionParameter(database);
SqlCommand sql = $@"
SELECT PKCON.CONSTRAINT_NAME AS PRIMARY_KEY_CONSTRAINT_NAME,
PKCON.OWNER AS PRIMARY_KEY_OWNER,
PKCON.TABLE_NAME AS PRIMARY_KEY_TABLE_NAME,
FKCON.OWNER AS FOREIGN_KEY_OWNER,
FKCON.CONSTRAINT_NAME AS FOREIGN_KEY_CONSTRAINT_NAME,
FKCON.TABLE_NAME AS FOREIGN_KEY_TABLE_NAME,
FKCON.SEARCH_CONDITION,
FKCON.R_OWNER,
FKCON.R_CONSTRAINT_NAME,
FKCON.DELETE_RULE,
FKCON.STATUS,
(SELECT cu.COLUMN_NAME
FROM ALL_CONS_COLUMNS CU, ALL_CONSTRAINTS AU
WHERE CU.OWNER = AU.OWNER
AND CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME
AND AU.CONSTRAINT_TYPE = 'P'
and au.constraint_name = FKCON.r_constraint_name
and FKCON.owner = au.OWNER
and rownum = 1) PRIMARY_KEY_COLUMN_NAME,
(SELECT cu.COLUMN_NAME
FROM ALL_CONS_COLUMNS CU, ALL_CONSTRAINTS AU
WHERE CU.OWNER = AU.OWNER
AND CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME
AND AU.CONSTRAINT_TYPE = 'R'
and au.constraint_name = FKCON.CONSTRAINT_NAME
and PKCON.owner = au.OWNER
and rownum = 1) FOREIGN_KEY_COLUMN_NAME
FROM ALL_CONSTRAINTS FKCON, ALL_CONSTRAINTS PKCON
WHERE PKCON.OWNER = FKCON.R_OWNER
AND PKCON.CONSTRAINT_NAME = FKCON.R_CONSTRAINT_NAME
AND FKCON.CONSTRAINT_TYPE = 'R'
and (FKCON.OWNER = '{connpar.UserId.ToUpper()}')
AND (FKCON.TABLE_NAME = :TABLENAME OR :TABLENAME is null) AND (FKCON.CONSTRAINT_NAME = :CONSTRAINTNAME OR :CONSTRAINTNAME is null)";
restrictionValues
.Parameterize(parameters, "TABLENAME", nameof(ForeignKey.TableName))
.Parameterize(parameters, "CONSTRAINTNAME", nameof(ForeignKey.Name));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new ForeignKey
{
Schema = reader["FOREIGN_KEY_OWNER"].ToString(),
Name = reader["FOREIGN_KEY_CONSTRAINT_NAME"].ToString(),
TableName = reader["FOREIGN_KEY_TABLE_NAME"].ToString().Replace("\"", ""),
ColumnName = reader["FOREIGN_KEY_COLUMN_NAME"].ToString(),
PKTable = reader["PRIMARY_KEY_TABLE_NAME"].ToString(),
PKColumn = reader["PRIMARY_KEY_COLUMN_NAME"].ToString()
}));
}
protected override IEnumerable <ProcedureParameter> GetProcedureParameters(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
var connpar = GetConnectionParameter(database);
SqlCommand sql = $@"
SELECT
SPECIFIC_CATALOG,
SPECIFIC_SCHEMA,
SPECIFIC_NAME,
ORDINAL_POSITION,
PARAMETER_MODE,
IS_RESULT,
AS_LOCATOR,
PARAMETER_NAME,
CASE WHEN DATA_TYPE IS NULL THEN USER_DEFINED_TYPE_NAME WHEN DATA_TYPE = 'TABLE TYPE' THEN USER_DEFINED_TYPE_NAME ELSE DATA_TYPE END AS DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH,
COLLATION_CATALOG,
COLLATION_SCHEMA,
COLLATION_NAME,
CHARACTER_SET_CATALOG,
CHARACTER_SET_SCHEMA,
CHARACTER_SET_NAME,
NUMERIC_PRECISION,
NUMERIC_PRECISION_RADIX,
NUMERIC_SCALE,
DATETIME_PRECISION,
INTERVAL_TYPE,
INTERVAL_PRECISION
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE (SPECIFIC_CATALOG = '{connpar.Database}')
AND (SPECIFIC_NAME = @NAME OR (@NAME IS NULL))
AND (PARAMETER_NAME = @PARAMETER OR (@PARAMETER IS NULL))
ORDER BY SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, PARAMETER_NAME";
restrictionValues
.Parameterize(parameters, "NAME", nameof(ProcedureParameter.ProcedureName))
.Parameterize(parameters, "PARAMETER", nameof(ProcedureParameter.Name));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new ProcedureParameter
{
Catalog = wrapper.GetString(reader, 0),
Schema = wrapper.GetString(reader, 1),
ProcedureName = wrapper.GetString(reader, 2),
Name = wrapper.GetString(reader, 7),
Direction = wrapper.GetString(reader, 5) == "YES" ? ParameterDirection.ReturnValue : (wrapper.GetString(reader, 4) == "IN" ? ParameterDirection.Input : ParameterDirection.Output),
NumericPrecision = wrapper.GetInt32(reader, 17),
NumericScale = wrapper.GetInt32(reader, 19),
DataType = wrapper.GetString(reader, 8),
Length = wrapper.GetInt64(reader, 9)
}));
}
protected override IEnumerable <User> GetUsers(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
SqlCommand sql = "SELECT HOST, USER FROM MYSQL.USER WHERE (NAME = ?NAME OR ?NAME IS NULL)";
restrictionValues.Parameterize(parameters, "NAME", nameof(User.Name));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new User
{
Name = wrapper.GetString(reader, 1)
}));
}
protected override IEnumerable <Column> GetColumns(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
var connpar = GetConnectionParameter(database);
SqlCommand sql = $@"
SELECT T.TABLE_CATALOG,
T.TABLE_SCHEMA,
T.TABLE_NAME,
T.COLUMN_NAME,
T.DATA_TYPE AS DATATYPE,
T.CHARACTER_MAXIMUM_LENGTH AS LENGTH,
T.NUMERIC_PRECISION AS PRECISION,
T.NUMERIC_SCALE AS SCALE,
T.IS_NULLABLE AS NULLABLE,
(SELECT COUNT(1) FROM SYSCOLUMNS A
JOIN SYSINDEXKEYS B ON A.ID=B.ID AND A.COLID=B.COLID AND A.ID=OBJECT_ID(T.TABLE_NAME)
JOIN SYSINDEXES C ON A.ID=C.ID AND B.INDID=C.INDID JOIN SYSOBJECTS D ON C.NAME=D.NAME AND D.XTYPE= 'PK' WHERE A.NAME = T.COLUMN_NAME) COLUMN_IS_PK,
T.COLUMN_DEFAULT,
(SELECT VALUE FROM ::FN_LISTEXTENDEDPROPERTY('MS_Description','user',T.TABLE_SCHEMA,'table',T.TABLE_NAME,'column',T.COLUMN_NAME)) COMMENTS,
(SELECT C.COLSTAT FROM SYSCOLUMNS C
LEFT JOIN SYSOBJECTS O ON C.ID = O.ID WHERE O.XTYPE='U' AND O.NAME = T.TABLE_NAME AND C.NAME = T.COLUMN_NAME) AUTOINC
FROM INFORMATION_SCHEMA.COLUMNS T
JOIN INFORMATION_SCHEMA.TABLES O
ON O.TABLE_CATALOG = T.TABLE_CATALOG AND O.TABLE_SCHEMA = T.TABLE_SCHEMA AND T.TABLE_NAME = O.TABLE_NAME
WHERE (O.TABLE_TYPE <> 'view' AND T.TABLE_CATALOG = '{connpar.Database}') AND
(T.TABLE_NAME = @TABLENAME OR (@TABLENAME IS NULL)) AND
(T.COLUMN_NAME = @COLUMNNAME OR (@COLUMNNAME IS NULL))
ORDER BY T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME";
restrictionValues
.Parameterize(parameters, "TABLENAME", nameof(Column.TableName))
.Parameterize(parameters, "COLUMNNAME", nameof(Column.Name));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Column
{
Catalog = wrapper.GetString(reader, 0),
Schema = wrapper.GetString(reader, 1),
TableName = wrapper.GetString(reader, 2),
Name = wrapper.GetString(reader, 3),
DataType = wrapper.GetString(reader, 4),
Length = wrapper.GetInt64(reader, 5),
NumericPrecision = wrapper.GetInt32(reader, 6),
NumericScale = wrapper.GetInt32(reader, 7),
IsNullable = wrapper.GetString(reader, 8) == "YES",
IsPrimaryKey = wrapper.GetInt32(reader, 9) > 0,
Default = wrapper.GetString(reader, 10),
Description = wrapper.GetString(reader, 11),
Autoincrement = wrapper.GetInt32(reader, 12) == 1
}));
}
protected override IEnumerable <Database> GetDatabases(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
SqlCommand sql = @"
SELECT DATNAME FROM PG_DATABASE WHERE (DATNAME = @NAME OR (@NAME IS NULL))";
restrictionValues.Parameterize(parameters, "NAME", nameof(Database.Name));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Database
{
Name = wrapper.GetString(reader, 0)
}));
}
protected override IEnumerable <Database> GetDatabases(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
SqlCommand sql = @"
SELECT NAME AS DATABASE_NAME, CRDATE AS CREATE_DATE FROM MASTER..SYSDATABASES WHERE (NAME = @NAME OR (@NAME IS NULL))";
restrictionValues.Parameterize(parameters, "NAME", nameof(Database.Name));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Database
{
Name = wrapper.GetString(reader, 0),
CreateDate = wrapper.GetDateTime(reader, 1)
}));
}
protected override IEnumerable <User> GetUsers(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
SqlCommand sql = @"
SELECT UID, NAME AS USER_NAME, CREATEDATE, UPDATEDATE FROM SYSUSERS WHERE (NAME = @NAME OR (@NAME IS NULL))";
restrictionValues.Parameterize(parameters, "NAME", nameof(User.Name));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new User
{
Name = wrapper.GetString(reader, 1),
CreateDate = wrapper.GetDateTime(reader, 2)
}));
}
protected override IEnumerable <ForeignKey> GetForeignKeys(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
var connpar = GetConnectionParameter(database);
SqlCommand sql = $@"
SELECT
TC.CONSTRAINT_CATALOG,
TC.CONSTRAINT_SCHEMA,
TC.CONSTRAINT_NAME,
TC.TABLE_NAME,
C.COLUMN_NAME,
FKCU.TABLE_NAME REFERENCED_TABLE_NAME,
FKCU.COLUMN_NAME AS REFERENCED_COLUMN_NAME
FROM [INFORMATION_SCHEMA].[COLUMNS] C
LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] KCU
ON KCU.TABLE_SCHEMA = C.TABLE_SCHEMA
AND KCU.TABLE_NAME = C.TABLE_NAME
AND KCU.COLUMN_NAME = C.COLUMN_NAME
LEFT JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] TC
ON TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
LEFT JOIN [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] FC
ON KCU.CONSTRAINT_SCHEMA = FC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = FC.CONSTRAINT_NAME
LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] FKCU
ON FKCU.CONSTRAINT_SCHEMA = FC.UNIQUE_CONSTRAINT_SCHEMA
AND FKCU.CONSTRAINT_NAME = FC.UNIQUE_CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE = 'FOREIGN KEY' AND
(TC.CONSTRAINT_CATALOG = '{connpar.Database}') AND
(TC.TABLE_NAME = @TABLENAME OR @TABLENAME IS NULL) AND
(TC.CONSTRAINT_NAME = @NAME OR @NAME IS NULL)";
restrictionValues
.Parameterize(parameters, "TABLENAME", nameof(ForeignKey.TableName))
.Parameterize(parameters, "NAME", nameof(ForeignKey.Name));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new ForeignKey
{
Catalog = wrapper.GetString(reader, 0),
Schema = wrapper.GetString(reader, 1),
Name = wrapper.GetString(reader, 2),
TableName = wrapper.GetString(reader, 3),
ColumnName = wrapper.GetString(reader, 4),
PKTable = wrapper.GetString(reader, 5),
PKColumn = wrapper.GetString(reader, 6),
}));
}
protected override IEnumerable <ViewColumn> GetViewColumns(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
var connpar = GetConnectionParameter(database);
SqlCommand sql = $@"
SELECT T.TABLE_CATALOG,
T.TABLE_SCHEMA,
T.TABLE_NAME,
T.COLUMN_NAME,
T.DATA_TYPE,
T.CHARACTER_MAXIMUM_LENGTH,
T.NUMERIC_PRECISION,
T.NUMERIC_SCALE,
T.IS_NULLABLE,
T.COLUMN_KEY,
T.COLUMN_DEFAULT,
T.COLUMN_COMMENT,
T.EXTRA
FROM INFORMATION_SCHEMA.COLUMNS T
JOIN INFORMATION_SCHEMA.VIEWS O
ON O.TABLE_SCHEMA = T.TABLE_SCHEMA AND O.TABLE_NAME = T.TABLE_NAME
WHERE (T.TABLE_SCHEMA = '{connpar.Database}') AND
(T.TABLE_NAME = ?TABLENAME OR ?TABLENAME IS NULL) AND
(T.COLUMN_NAME = ?COLUMNNAME OR ?COLUMNNAME IS NULL)
ORDER BY T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME";
restrictionValues
.Parameterize(parameters, "TABLENAME", nameof(ViewColumn.ViewName))
.Parameterize(parameters, "COLUMNNAME", nameof(ViewColumn.Name));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new ViewColumn
{
Catalog = wrapper.GetString(reader, 0),
Schema = wrapper.GetString(reader, 1),
ViewName = wrapper.GetString(reader, 2),
Name = wrapper.GetString(reader, 3),
DataType = wrapper.GetString(reader, 4),
Length = wrapper.GetInt64(reader, 5),
NumericPrecision = wrapper.GetInt32(reader, 6),
NumericScale = wrapper.GetInt32(reader, 7),
IsNullable = wrapper.GetString(reader, 8) == "YES",
IsPrimaryKey = wrapper.GetString(reader, 9) == "PRI",
Default = wrapper.GetString(reader, 10),
Description = wrapper.GetString(reader, 11),
Autoincrement = false
}));
}
protected override IEnumerable <ForeignKey> GetForeignKeys(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
SqlCommand sql = @"
SELECT
null AS CONSTRAINT_CATALOG,
null AS CONSTRAINT_SCHEMA,
trim(co.rdb$constraint_name) AS CONSTRAINT_NAME,
null AS TABLE_CATALOG,
null AS TABLE_SCHEMA,
trim(co.rdb$relation_name) AS TABLE_NAME,
trim(coidxseg.rdb$field_name) AS COLUMN_NAME,
null as REFERENCED_TABLE_CATALOG,
null as REFERENCED_TABLE_SCHEMA,
trim(refidx.rdb$relation_name) as REFERENCED_TABLE_NAME,
trim(refidxseg.rdb$field_name) AS REFERENCED_COLUMN_NAME,
ref.rdb$update_rule AS UPDATE_RULE,
ref.rdb$delete_rule AS DELETE_RULE
FROM rdb$relation_constraints co
INNER JOIN rdb$ref_constraints ref ON co.rdb$constraint_name = ref.rdb$constraint_name
INNER JOIN rdb$indices tempidx ON co.rdb$index_name = tempidx.rdb$index_name
INNER JOIN rdb$index_segments coidxseg ON co.rdb$index_name = coidxseg.rdb$index_name
INNER JOIN rdb$indices refidx ON refidx.rdb$index_name = tempidx.rdb$foreign_key
INNER JOIN rdb$index_segments refidxseg ON refidxseg.rdb$index_name = refidx.rdb$index_name AND refidxseg.rdb$field_position = coidxseg.rdb$field_position
where co.rdb$constraint_type = 'FOREIGN KEY' AND
(co.rdb$relation_name = @TABLENAME OR (@TABLENAME IS NULL)) AND
(co.rdb$constraint_name = @NAME OR (@NAME IS NULL))";
restrictionValues
.Parameterize(parameters, "TABLENAME", nameof(ForeignKey.TableName))
.Parameterize(parameters, "NAME", nameof(ForeignKey.Name));
var table = database.ExecuteDataTable(sql, parameters: parameters);
foreach (DataRow row in table.Rows)
{
yield return(new ForeignKey
{
Schema = row["CONSTRAINT_SCHEMA"].ToString(),
Name = row["CONSTRAINT_NAME"].ToString(),
TableName = row["TABLE_NAME"].ToString().Replace("\"", ""),
PKTable = row["REFERENCED_TABLE_NAME"].ToString(),
ColumnName = row["COLUMN_NAME"].ToString(),
PKColumn = row["REFERENCED_COLUMN_NAME"].ToString(),
});
}
}
protected override IEnumerable <View> GetViews(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
SqlCommand sql = $@"
SELECT name, type FROM main.sqlite_master
WHERE type LIKE 'view'
AND (name = @NAME OR @NAME IS NULL)";
restrictionValues.Parameterize(parameters, "NAME", nameof(View.Name));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new View
{
Catalog = "main",
Name = wrapper.GetString(reader, 0)
}));
}
protected override IEnumerable <ForeignKey> GetForeignKeys(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
var connpar = GetConnectionParameter(database);
SqlCommand sql = $@"
SELECT
TC.CONSTRAINT_CATALOG,
TC.CONSTRAINT_SCHEMA,
TC.CONSTRAINT_NAME,
TC.TABLE_NAME,
KCU.COLUMN_NAME,
CCU.TABLE_NAME AS FOREIGN_TABLE_NAME,
CCU.COLUMN_NAME AS FOREIGN_COLUMN_NAME,
TC.IS_DEFERRABLE,
TC.INITIALLY_DEFERRED
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU ON TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU ON CCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_CATALOG = '{connpar.Database}' AND
CONSTRAINT_TYPE = 'FOREIGN KEY' AND
(TC.TABLE_NAME = @TABLENAME OR @TABLENAME IS NULL) AND
(TC.CONSTRAINT_NAME = @NAME OR @NAME IS NULL)";
restrictionValues
.Parameterize(parameters, "TABLENAME", nameof(ForeignKey.TableName))
.Parameterize(parameters, "NAME", nameof(ForeignKey.Name));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new ForeignKey
{
Catalog = wrapper.GetString(reader, 0),
Schema = wrapper.GetString(reader, 1),
Name = wrapper.GetString(reader, 2),
TableName = wrapper.GetString(reader, 3),
ColumnName = wrapper.GetString(reader, 4),
PKTable = wrapper.GetString(reader, 5),
PKColumn = wrapper.GetString(reader, 6),
}));
}
protected override IEnumerable <ProcedureParameter> GetProcedureParameters(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
var connpar = GetConnectionParameter(database);
SqlCommand sql = $@"
SELECT
SPECIFIC_CATALOG,
SPECIFIC_SCHEMA,
SPECIFIC_NAME,
ORDINAL_POSITION,
PARAMETER_MODE,
PARAMETER_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_SCALE
WHERE (SPECIFIC_SCHEMA = '{connpar.Database}')
AND (SPECIFIC_NAME = @NAME OR (@NAME IS NULL))
AND (PARAMETER_NAME = @PARAMETER OR (@PARAMETER IS NULL))
ORDER BY SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, PARAMETER_NAME";
restrictionValues
.Parameterize(parameters, "NAME", nameof(ProcedureParameter.ProcedureName))
.Parameterize(parameters, "PARAMETER", nameof(ProcedureParameter.Name));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new ProcedureParameter
{
Catalog = wrapper.GetString(reader, 0),
Schema = wrapper.GetString(reader, 1),
ProcedureName = wrapper.GetString(reader, 2),
Name = wrapper.GetString(reader, 5),
Direction = wrapper.GetString(reader, 4) == "IN" ? ParameterDirection.Input : ParameterDirection.Output,
NumericPrecision = wrapper.GetInt32(reader, 8),
NumericScale = wrapper.GetInt32(reader, 9),
DataType = wrapper.GetString(reader, 6),
Length = wrapper.GetInt64(reader, 7)
}));
}
protected override IEnumerable <IndexColumn> GetIndexColumns(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
var connpar = GetConnectionParameter(database);
SqlCommand sql = $@"
SELECT DISTINCT
DB_NAME() AS CONSTRAINT_CATALOG,
CONSTRAINT_SCHEMA = USER_NAME(O.UID),
CONSTRAINT_NAME = X.NAME,
TABLE_CATALOG = DB_NAME(),
TABLE_SCHEMA = USER_NAME(O.UID),
TABLE_NAME = O.NAME,
COLUMN_NAME = C.NAME,
ORDINAL_POSITION = CONVERT(INT, XK.KEYNO),
KEYTYPE = C.XTYPE,
INDEX_NAME = X.NAME
FROM SYSOBJECTS O, SYSINDEXES X, SYSCOLUMNS C, SYSINDEXKEYS XK
WHERE O.TYPE IN ('U') AND X.ID = O.ID AND O.ID = C.ID AND O.ID = XK.ID AND X.INDID = XK.INDID AND C.COLID = XK.COLID AND XK.KEYNO <= X.KEYCNT AND PERMISSIONS(O.ID, C.NAME) <> 0
AND (DB_NAME() = '{connpar.Database}')
AND (USER_NAME()= @OWNER OR (@OWNER IS NULL))
AND (X.NAME = @CONSTRAINTNAME OR (@CONSTRAINTNAME IS NULL))
AND (C.NAME = @COLUMN OR (@COLUMN IS NULL))
ORDER BY TABLE_NAME, INDEX_NAME";
restrictionValues
.Parameterize(parameters, "TABLE", nameof(IndexColumn.TableName))
.Parameterize(parameters, "CONSTRAINTNAME", nameof(IndexColumn.IndexName))
.Parameterize(parameters, "COLUMN", nameof(IndexColumn.ColumnName));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new IndexColumn
{
Catalog = wrapper.GetString(reader, 0),
Schema = wrapper.GetString(reader, 1),
TableName = wrapper.GetString(reader, 5),
IndexName = wrapper.GetString(reader, 2),
ColumnName = wrapper.GetString(reader, 6)
}));
}
protected override IEnumerable <Table> GetTables(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
SqlCommand sql = @"
SELECT
null AS TABLE_CATALOG,
null AS TABLE_SCHEMA,
trim(rdb$relation_name) AS TABLE_NAME,
(case when rdb$system_flag = 1 then 'SYSTEM_TABLE' else 'TABLE' end) AS TABLE_TYPE,
rdb$owner_name AS OWNER_NAME,
rdb$description AS DESCRIPTION,
rdb$view_source AS VIEW_SOURCE
FROM rdb$relations
WHERE
(rdb$relation_name = @NAME OR (@NAME IS NULL)) AND
((@TABLETYPE = 1 and rdb$system_flag = 1) OR ((@TABLETYPE = 0 or @TABLETYPE IS NULL) and rdb$system_flag = 0))
ORDER BY rdb$system_flag, rdb$owner_name, rdb$relation_name";
restrictionValues
.Parameterize(parameters, "NAME", nameof(Table.Name))
.Parameterize(parameters, "TABLETYPE", nameof(Table.Type));
var table = database.ExecuteDataTable(sql, parameters: parameters);
foreach (DataRow row in table.Rows)
{
yield return(new Table
{
Catalog = row["TABLE_CATALOG"].ToString(),
Schema = row["TABLE_SCHEMA"].ToString(),
Name = row["TABLE_NAME"].ToString(),
Description = row["DESCRIPTION"].ToStringSafely(),
Type = row["TABLE_TYPE"].ToString() == "TABLE" ? TableType.BaseTable : TableType.SystemTable
});
}
}
protected override IEnumerable <ForeignKey> GetForeignKeys(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
var connpar = GetConnectionParameter(database);
SqlCommand sql = $@"
SELECT
T.CONSTRAINT_CATALOG,
T.CONSTRAINT_SCHEMA,
T.CONSTRAINT_NAME,
T.TABLE_NAME,
T.COLUMN_NAME,
T.REFERENCED_TABLE_NAME,
T.REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE T
WHERE (T.CONSTRAINT_SCHEMA = '{connpar.Database}') AND
(T.TABLE_NAME = ?TABLENAME OR ?TABLENAME IS NULL) AND
(T.CONSTRAINT_NAME = ?NAME OR ?NAME IS NULL) AND
REFERENCED_TABLE_NAME IS NOT NULL";
restrictionValues
.Parameterize(parameters, "TABLENAME", nameof(ForeignKey.TableName))
.Parameterize(parameters, "NAME", nameof(ForeignKey.Name));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new ForeignKey
{
Catalog = wrapper.GetString(reader, 0),
Schema = wrapper.GetString(reader, 1),
Name = wrapper.GetString(reader, 2),
TableName = wrapper.GetString(reader, 3),
ColumnName = wrapper.GetString(reader, 4),
PKTable = wrapper.GetString(reader, 5),
PKColumn = wrapper.GetString(reader, 6),
}));
}
protected override IEnumerable <Column> GetColumns(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
var connpar = GetConnectionParameter(database);
SqlCommand sql = $@"
SELECT T.OWNER,
T.TABLE_NAME,
T.COLUMN_NAME,
T.DATA_TYPE AS DATATYPE,
T.DATA_LENGTH AS LENGTH,
T.DATA_PRECISION AS PRECISION,
T.DATA_SCALE AS SCALE,
T.NULLABLE AS NULLABLE,
(CASE
WHEN P.OWNER IS NULL THEN
'N'
ELSE
'Y'
END) PK,
D.DATA_DEFAULT,
C.COMMENTS
FROM ALL_TAB_COLUMNS T
LEFT JOIN ALL_COL_COMMENTS C
ON T.OWNER = C.OWNER
AND T.TABLE_NAME = C.TABLE_NAME
AND T.COLUMN_NAME = C.COLUMN_NAME
LEFT JOIN ALL_TAB_COLUMNS D
ON T.OWNER = D.OWNER
AND T.TABLE_NAME = D.TABLE_NAME
AND T.COLUMN_NAME = D.COLUMN_NAME
LEFT JOIN (SELECT AU.OWNER, AU.TABLE_NAME, CU.COLUMN_NAME
FROM ALL_CONS_COLUMNS CU, ALL_CONSTRAINTS AU
WHERE CU.OWNER = AU.OWNER
AND CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME
AND AU.CONSTRAINT_TYPE = 'P') P
ON T.OWNER = P.OWNER
AND T.TABLE_NAME =P.TABLE_NAME
AND T.COLUMN_NAME = P.COLUMN_NAME
WHERE (T.OWNER = '{connpar.UserId.ToUpper()}') AND
(T.TABLE_NAME = :TABLENAME OR :TABLENAME IS NULL) AND
(T.COLUMN_NAME = :COLUMNNAME OR :COLUMNNAME IS NULL)
ORDER BY T.OWNER, T.TABLE_NAME, T.COLUMN_ID";
restrictionValues
.Parameterize(parameters, "TABLENAME", nameof(Column.TableName))
.Parameterize(parameters, "COLUMNNAME", nameof(Column.Name));
return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Column
{
Schema = wrapper.GetString(reader, 0),
TableName = wrapper.GetString(reader, 1),
Name = wrapper.GetString(reader, 2),
DataType = wrapper.GetString(reader, 3),
Length = wrapper.GetInt32(reader, 4),
NumericPrecision = wrapper.GetInt32(reader, 5),
NumericScale = wrapper.GetInt32(reader, 6),
IsNullable = wrapper.GetString(reader, 7) == "Y",
IsPrimaryKey = wrapper.GetString(reader, 8) == "Y",
Default = wrapper.GetString(reader, 9),
Description = wrapper.GetString(reader, 10),
}));
}
protected override IEnumerable <Column> GetColumns(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
SqlCommand sql = @"
SELECT
null AS TABLE_CATALOG,
null AS TABLE_SCHEMA,
rfr.rdb$relation_name AS TABLE_NAME,
trim(rfr.rdb$field_name) AS COLUMN_NAME,
null AS COLUMN_DATA_TYPE,
fld.rdb$field_sub_type AS COLUMN_SUB_TYPE,
CAST(fld.rdb$field_length AS integer) AS COLUMN_SIZE,
CAST(fld.rdb$field_precision AS integer) AS NUMERIC_PRECISION,
CAST(fld.rdb$field_scale AS integer) AS NUMERIC_SCALE,
CAST(fld.rdb$character_length AS integer) AS CHARACTER_MAX_LENGTH,
CAST(fld.rdb$field_length AS integer) AS CHARACTER_OCTET_LENGTH,
rfr.rdb$field_position AS ORDINAL_POSITION,
rfr.rdb$default_source AS COLUMN_DEFAULT,
coalesce(fld.rdb$null_flag, rfr.rdb$null_flag) AS COLUMN_NULLABLE,
fld.rdb$field_type AS FIELD_TYPE,
rfr.rdb$description AS DESCRIPTION,
(select count(1)
FROM RDB$RELATION_CONSTRAINTS RC
LEFT JOIN RDB$INDICES I ON
(I.RDB$INDEX_NAME = RC.RDB$INDEX_NAME)
LEFT JOIN RDB$INDEX_SEGMENTS S
ON
(S.RDB$INDEX_NAME = I.RDB$INDEX_NAME)
WHERE (RC.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY') and
rc.RDB$RELATION_NAME = rfr.rdb$relation_name and RDB$FIELD_NAME = rfr.rdb$field_name) as COLUMN_IS_PK
FROM rdb$relation_fields rfr
LEFT JOIN rdb$fields fld ON rfr.rdb$field_source = fld.rdb$field_name
LEFT JOIN rdb$character_sets cs ON cs.rdb$character_set_id = fld.rdb$character_set_id
LEFT JOIN rdb$collations coll ON (coll.rdb$collation_id = fld.rdb$collation_id AND coll.rdb$character_set_id = fld.rdb$character_set_id)
WHERE (rfr.rdb$relation_name = @TABLENAME OR (@TABLENAME IS NULL)) AND
(rfr.rdb$field_name = @COLUMNNAME OR (@COLUMNNAME IS NULL))
ORDER BY rfr.rdb$relation_name, rfr.rdb$field_position
";
restrictionValues
.Parameterize(parameters, "TABLENAME", nameof(Column.TableName))
.Parameterize(parameters, "COLUMNNAME", nameof(Column.Name));
var table = database.ExecuteDataTable(sql, parameters: parameters);
foreach (DataRow row in table.Rows)
{
var subtype = 0;
int scale = 0;
if (row["COLUMN_SUB_TYPE"] != DBNull.Value)
{
subtype = row["COLUMN_SUB_TYPE"].To <int>();
}
if (row["NUMERIC_SCALE"] != DBNull.Value)
{
scale = row["NUMERIC_SCALE"].To <int>();
}
var ftype = row["FIELD_TYPE"].To <int>();
yield return(new Column
{
Catalog = row["TABLE_CATALOG"].ToString(),
Schema = row["TABLE_SCHEMA"].ToString(),
TableName = row["TABLE_NAME"].ToString(),
Name = row["COLUMN_NAME"].ToString(),
Default = row["COLUMN_DEFAULT"].ToString(),
DataType = GetDbDataType(ftype, subtype, scale),
NumericPrecision = row["NUMERIC_PRECISION"].To <int>(),
NumericScale = scale,
Description = row["DESCRIPTION"].ToString(),
IsNullable = row["COLUMN_NULLABLE"].To <bool>(),
Length = row["COLUMN_SIZE"].To <long>(),
Position = row["ORDINAL_POSITION"].To <int>(),
IsPrimaryKey = row["COLUMN_IS_PK"].To <bool>()
});
}
}
