protected override IEnumerable <Column> GetColumns(IDatabase database, RestrictionDictionary restrictionValues)
{
var parameters = new ParameterCollection();
var columns = new List <Column>();
//如果指定使用表名查询
if (restrictionValues.TryGetValue(nameof(Column.TableName), out string tableName))
{
SqlCommand sql = $@"
PRAGMA main.TABLE_INFO('{tableName}')";
columns.AddRange(GetColumns(database, tableName));
}
else
{
//循环所有表，对每个表进行查询
foreach (var tb in GetTables(database, RestrictionDictionary.Empty))
{
SqlCommand sql = $@"
PRAGMA main.TABLE_INFO('{tb.Name}')";
columns.AddRange(GetColumns(database, tb.Name));
}
}
//如果使用列名进行查询
if (restrictionValues.TryGetValue(nameof(Column.Name), out string columnName))
{
columns = columns.Where(s => s.Name == columnName).ToList();
}
return(columns);
}
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 <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 restrictions = new[] { null, null, restrictionValues.GetValue(nameof(Column.TableName)), restrictionValues.GetValue(nameof(Column.Name)) };
var conn = (OleDbConnection)database.Connection;
conn.TryOpen(true);
var tbPrimary = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new[] { null, null, restrictionValues.GetValue(nameof(Column.TableName)) });
foreach (DataRow row in conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, restrictions).Rows)
{
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 = row["DATA_TYPE"].ToString(),
NumericPrecision = row["NUMERIC_PRECISION"].To <int>(),
NumericScale = row["NUMERIC_SCALE"].To <int>(),
IsNullable = row["IS_NULLABLE"] != DBNull.Value && row["IS_NULLABLE"].To <bool>(),
Length = row["CHARACTER_MAXIMUM_LENGTH"].To <long>(),
Position = row["ORDINAL_POSITION"].To <int>(),
IsPrimaryKey = tbPrimary.Select($"TABLE_NAME = '{row["TABLE_NAME"].ToString().Replace("\"", "").Replace("'", "")}' AND COLUMN_NAME='{row["COLUMN_NAME"].ToString()}'").Length > 0
});
}
}
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 <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 <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 <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 connpar = GetConnectionParameter(database);
string tableType = null;
switch (restrictionValues.GetValue(nameof(Table.Type)))
{
case null:
case "0":
tableType = "TABLE";
break;
case "1":
tableType = "SYSTEM TABLE";
break;
}
var restrictions = new[] { connpar?.Database, null, restrictionValues.GetValue(nameof(Table.Name)), tableType };
var conn = (OleDbConnection)database.Connection;
conn.TryOpen(true);
foreach (DataRow row in conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, restrictions).Rows)
{
yield return(new Table
{
Catalog = row["TABLE_CATALOG"].ToString(),
Schema = row["TABLE_SCHEMA"].ToString(),
Name = row["TABLE_NAME"].ToString(),
Type = row["TABLE_TYPE"].ToString() == "TABLE" ? TableType.BaseTable : TableType.SystemTable,
Description = ""
});
}
}
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 <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 sql = "SHOW DATABASES";
if (restrictionValues.TryGetValue(nameof(Database.Name), out string dbName))
{
sql += $" LIKE '{dbName}'";
}
return(ExecuteAndParseMetadata(database, sql, null, (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 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 <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,
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.TABLES 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(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.GetString(reader, 9) == "PRI",
Default = wrapper.GetString(reader, 10),
Description = wrapper.GetString(reader, 11),
Autoincrement = !wrapper.IsDbNull(reader, 12) && wrapper.GetString(reader, 12).IndexOf("auto_increment") != -1
}));
}
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 <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 <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
FROM
INFORMATION_SCHEMA.VIEWS T
WHERE (T.TABLE_SCHEMA = '{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)
}));
}
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 <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)
}));
}
/// <summary>
/// 获取 <see cref="IndexColumn"/> 元数据序列。
/// </summary>
/// <param name="table">架构信息的表。</param>
/// <param name="action">用于填充元数据的方法。</param>
/// <returns></returns>
protected virtual IEnumerable <IndexColumn> GetIndexColumns(IDatabase database, RestrictionDictionary restrictionValues)
{
yield break;
}
/// <summary>
/// 获取 <see cref="ForeignKey"/> 元数据序列。
/// </summary>
/// <param name="table">架构信息的表。</param>
/// <param name="action">用于填充元数据的方法。</param>
/// <returns></returns>
protected virtual IEnumerable <ForeignKey> GetForeignKeys(IDatabase database, RestrictionDictionary restrictionValues)
{
yield break;
}
protected override IEnumerable <ForeignKey> GetForeignKeys(IDatabase database, RestrictionDictionary restrictionValues)
{
var restrictions = new[] { null, null, restrictionValues.GetValue(nameof(ForeignKey.TableName)), restrictionValues.GetValue(nameof(ForeignKey.Name)) };
var conn = (OleDbConnection)database.Connection;
conn.TryOpen(true);
foreach (DataRow row in conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, restrictions).Rows)
{
yield return(new ForeignKey
{
Catalog = row["FK_TABLE_CATALOG"].ToString(),
Schema = row["FK_TABLE_SCHEMA"].ToString(),
Name = row["FK_NAME"].ToString(),
PKTable = row["PK_TABLE_NAME"].ToString(),
PKColumn = row["PK_COLUMN_NAME"].ToString(),
TableName = row["FK_TABLE_NAME"].ToString(),
ColumnName = row["FK_COLUMN_NAME"].ToString()
});
}
}
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 <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),
}));
}
/// <summary>
/// 获取 <see cref="MetadataCollection"/> 元数据序列。
/// </summary>
/// <param name="table">架构信息的表。</param>
/// <param name="action">用于填充元数据的方法。</param>
/// <returns></returns>
protected virtual IEnumerable <MetadataCollection> GetMetadataCollections(IDatabase database, RestrictionDictionary restrictionValues)
{
yield break;
}
/// <summary>
/// 获取 <see cref="DataType"/> 元数据序列。
/// </summary>
/// <param name="table">架构信息的表。</param>
/// <param name="action">用于填充元数据的方法。</param>
/// <returns></returns>
protected virtual IEnumerable <DataType> GetDataTypes(IDatabase database, RestrictionDictionary restrictionValues)
{
return(dataTypes);
}
