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); }