public override DatabaseSchema GetSchema(DataConnection dataConnection, GetSchemaOptions options = null) { DefaultSchema = dataConnection.Execute<string>("SELECT CURRENT_SCHEMA FROM DUMMY"); _databaseName = ((DbConnection)dataConnection.Connection).Database; _dataSourceName = ((DbConnection) dataConnection.Connection).DataSource; if (String.IsNullOrEmpty(_dataSourceName) || String.IsNullOrEmpty(_databaseName)) { using (var reader = dataConnection.ExecuteReader(@" SELECT HOST, KEY, VALUE FROM M_HOST_INFORMATION WHERE KEY = 'sid'")) { if (reader.Reader.Read()) { _dataSourceName = reader.Reader.GetString(0); _databaseName = reader.Reader.GetString(2); } } } return base.GetSchema(dataConnection, options); }
public override DatabaseSchema GetSchema(DataConnection dataConnection, GetSchemaOptions options = null) { HanaSchemaOptions = options as GetHanaSchemaOptions; DefaultSchema = dataConnection.Execute<string>("SELECT CURRENT_SCHEMA FROM DUMMY"); HaveAccessForCalculationViews = CheckAccessForCalculationViews(dataConnection); return base.GetSchema(dataConnection, options); }
protected override IReadOnlyCollection <PrimaryKeyInfo> GetPrimaryKeys(DataConnection dataConnection, IEnumerable <TableSchema> tables, GetSchemaOptions options) { // https://github.com/dotnet/runtime/issues/35442 return(Array <PrimaryKeyInfo> .Empty); }
protected override DataTable?GetProcedureSchema(DataConnection dataConnection, string commandText, CommandType commandType, DataParameter[] parameters, GetSchemaOptions options) { //bug in drivers, SchemaOnly executes statement using (dataConnection.BeginTransaction()) using (var rd = dataConnection.ExecuteReader(commandText, commandType, CommandBehavior.SchemaOnly, parameters)) { return(rd.Reader !.GetSchemaTable()); } }
public IEnumerable <ExplorerItem> GetItemsAndCode(string nameSpace, string typeName) { typeName = ConvertToCompilable(typeName, false); var connectionString = _cxInfo.DatabaseInfo.CustomCxString; var provider = ProviderHelper.GetProvider(ProviderName).GetDataProvider(connectionString); using (var db = new DataConnection(provider, connectionString)) { db.CommandTimeout = CommandTimeout; _dataProvider = db.DataProvider; _sqlBuilder = _dataProvider.CreateSqlBuilder(); var options = new GetSchemaOptions(); var includeSchemas = (string)_cxInfo.DriverData.Element("includeSchemas"); if (includeSchemas != null) { options.IncludedSchemas = includeSchemas.Split(',', ';'); } var excludeSchemas = (string)_cxInfo.DriverData.Element("excludeSchemas"); if (excludeSchemas != null) { options.ExcludedSchemas = excludeSchemas.Split(',', ';'); } var includeCatalogs = (string)_cxInfo.DriverData.Element("includeCatalogs"); if (includeCatalogs != null) { options.IncludedCatalogs = includeCatalogs.Split(',', ';'); } var excludeCatalogs = (string)_cxInfo.DriverData.Element("excludeCatalogs"); if (excludeCatalogs != null) { options.ExcludedCatalogs = excludeCatalogs.Split(',', ';'); } options.GetProcedures = (string)_cxInfo.DriverData.Element("excludeRoutines") != "true"; _schema = _dataProvider.GetSchemaProvider().GetSchema(db, options); ConvertSchema(typeName); } Code .AppendLine("using System;") .AppendLine("using System.Collections;") .AppendLine("using System.Collections.Generic;") .AppendLine("using System.Data;") .AppendLine("using System.Reflection;") .AppendLine("using System.Linq;") .AppendLine("using LinqToDB;") .AppendLine("using LinqToDB.Common;") .AppendLine("using LinqToDB.Data;") .AppendLine("using LinqToDB.Mapping;") .AppendLine("using System.Net.NetworkInformation;") ; if (_schema.Procedures.Any(_ => _.IsAggregateFunction)) { Code .AppendLine("using System.Linq.Expressions;") ; } if (_schema.ProviderSpecificTypeNamespace.NotNullNorWhiteSpace()) { Code.AppendLine($"using {_schema.ProviderSpecificTypeNamespace};"); } var providerInfo = ProviderHelper.GetProvider(ProviderName); References.AddRange(providerInfo.GetAssemblyLocation(connectionString)); if (providerInfo.Provider.AdditionalNamespaces != null) { foreach (var ns in providerInfo.Provider.AdditionalNamespaces) { Code.AppendLine($"using {ns};"); } } Code .AppendLine($"namespace {nameSpace}") .AppendLine("{") .AppendLine($" public class {typeName} : LinqToDB.LINQPad.LINQPadDataConnection") .AppendLine(" {") .AppendLine($" public {typeName}(string provider, string connectionString)") .AppendLine(" : base(provider, connectionString)") .AppendLine(" {") .AppendLine($" CommandTimeout = {CommandTimeout};") .AppendLine(" }") .AppendLine($" public {typeName}()") .AppendLine($" : base({CSharpTools.ToStringLiteral(ProviderName)}, {CSharpTools.ToStringLiteral(connectionString)})") .AppendLine(" {") .AppendLine($" CommandTimeout = {CommandTimeout};") .AppendLine(" }") ; if (ProviderName == LinqToDB.ProviderName.PostgreSQL) { PreprocessPostgreSQLSchema(); } var schemas = ( from t in ( from t in _schema.Tables select new { t.IsDefaultSchema, t.SchemaName, Table = t, Procedure = (ProcedureSchema)null } ) .Union ( from p in _schema.Procedures select new { p.IsDefaultSchema, p.SchemaName, Table = (TableSchema)null, Procedure = p } ) group t by new { t.IsDefaultSchema, t.SchemaName } into gr orderby !gr.Key.IsDefaultSchema, gr.Key.SchemaName select new { gr.Key, Tables = gr.Where(t => t.Table != null).Select(t => t.Table).ToList(), Procedures = gr.Where(t => t.Procedure != null).Select(t => t.Procedure).ToList(), } ) .ToList(); foreach (var s in schemas) { var items = new List <ExplorerItem>(); if (s.Tables.Any(t => !t.IsView && !t.IsProcedureResult)) { items.Add(GetTables("Tables", ExplorerIcon.Table, s.Tables.Where(t => !t.IsView && !t.IsProcedureResult))); } if (s.Tables.Any(t => t.IsView)) { items.Add(GetTables("Views", ExplorerIcon.View, s.Tables.Where(t => t.IsView))); } if (!_cxInfo.DynamicSchemaOptions.ExcludeRoutines && s.Procedures.Any(p => p.IsLoaded && !p.IsFunction)) { items.Add(GetProcedures( "Stored Procs", ExplorerIcon.StoredProc, s.Procedures.Where(p => p.IsLoaded && !p.IsFunction).ToList())); } if (s.Procedures.Any(p => p.IsLoaded && p.IsTableFunction)) { items.Add(GetProcedures( "Table Functions", ExplorerIcon.TableFunction, s.Procedures.Where(p => p.IsLoaded && p.IsTableFunction).ToList())); } if (s.Procedures.Any(p => p.IsFunction && !p.IsTableFunction)) { items.Add(GetProcedures( "Scalar Functions", ExplorerIcon.ScalarFunction, s.Procedures.Where(p => p.IsFunction && !p.IsTableFunction).ToList())); } if (schemas.Count == 1) { foreach (var item in items) { yield return(item); } } else { yield return(new ExplorerItem( s.Key.SchemaName.IsNullOrEmpty() ? s.Key.IsDefaultSchema ? "(default)" : "empty" : s.Key.SchemaName, ExplorerItemKind.Schema, ExplorerIcon.Schema) { Children = items }); } } Code .AppendLine(" }") .AppendLine(_classCode.ToString()) .AppendLine("}") ; #if DEBUG Debug.WriteLine(Code.ToString()); #endif }
protected override List <ProcedureParameterInfo> GetProcedureParameters(DataConnection dataConnection, IEnumerable <ProcedureInfo> procedures, GetSchemaOptions options) { if (SchemasFilter == null) { return(new List <ProcedureParameterInfo>()); } return(dataConnection.Query(rd => { var schema = rd.GetString(0); var procedure = rd.GetString(1); var parameter = rd.GetString(2); var dataType = rd.IsDBNull(3) ? null : rd.GetString(3); var position = rd.GetInt32(4); var paramType = rd.GetString(5); var isResult = rd.GetBoolean(6); var length = rd.GetInt32(7); var scale = rd.GetInt32(8); var isNullable = rd.GetString(9) == "TRUE"; return new ProcedureParameterInfo { ProcedureID = string.Concat(schema, '.', procedure), DataType = dataType, IsIn = paramType.Contains("IN"), IsOut = paramType.Contains("OUT"), IsResult = isResult, Length = length, Ordinal = position, ParameterName = parameter, Precision = length, Scale = scale, IsNullable = isNullable }; }, @" SELECT SCHEMA_NAME, PROCEDURE_NAME, PARAMETER_NAME, DATA_TYPE_NAME, POSITION, PARAMETER_TYPE, 0 AS IS_RESULT, LENGTH, SCALE, IS_NULLABLE FROM PROCEDURE_PARAMETERS WHERE SCHEMA_NAME " + SchemasFilter + @" UNION ALL SELECT SCHEMA_NAME, FUNCTION_NAME AS PROCEDURE_NAME, PARAMETER_NAME, DATA_TYPE_NAME, POSITION, PARAMETER_TYPE, CASE WHEN PARAMETER_TYPE = 'RETURN' THEN 1 ELSE 0 END AS IS_RESULT, LENGTH, SCALE, IS_NULLABLE FROM FUNCTION_PARAMETERS WHERE NOT (PARAMETER_TYPE = 'RETURN' AND DATA_TYPE_NAME = 'TABLE_TYPE') AND SCHEMA_NAME " + SchemasFilter + @" ORDER BY SCHEMA_NAME, PROCEDURE_NAME, POSITION" ) .ToList()); }
protected override Type?GetSystemType(string?dataType, string?columnType, DataTypeInfo?dataTypeInfo, long?length, int?precision, int?scale, GetSchemaOptions options) { switch (dataType) { case "TINYINT": return(typeof(byte)); case "ST_GEOMETRY": case "ST_GEOMETRYCOLLECTION": case "ST_POINT": case "ST_MULTIPOINT": case "ST_LINESTRING": case "ST_MULTILINESTRING": case "ST_POLYGON": case "ST_MULTIPOLYGON": case "ST_CIRCULARSTRING": return(typeof(byte[])); } return(base.GetSystemType(dataType, columnType, dataTypeInfo, length, precision, scale, options)); }
protected override IReadOnlyCollection <PrimaryKeyInfo> GetPrimaryKeys(DataConnection dataConnection, IEnumerable <TableSchema> tables, GetSchemaOptions options) { var pks = ((DbConnection)dataConnection.Connection).GetSchema("IndexColumns"); return (( from pk in pks.AsEnumerable() where pk.Field <string>("CONSTRAINT") == "PRIMARY KEY" select new PrimaryKeyInfo { TableID = pk.Field <string>("TABLE_SCHEMA") + "." + pk.Field <string>("TABLE_NAME"), PrimaryKeyName = pk.Field <string>("INDEX_NAME"), ColumnName = pk.Field <string>("COLUMN_NAME"), Ordinal = Converter.ChangeTypeTo <int>(pk["POSITION"]), } ).ToList()); }
protected override IReadOnlyCollection <ForeignKeyInfo> GetForeignKeys(DataConnection dataConnection, IEnumerable <TableSchema> tables, GetSchemaOptions options) { if (SchemasFilter == null) { return(new List <ForeignKeyInfo>()); } return(dataConnection.Query <ForeignKeyInfo>(@" SELECT CONSTRAINT_NAME AS ""Name"", SCHEMA_NAME || '.' || TABLE_NAME AS ""ThisTableID"", COLUMN_NAME AS ""ThisColumn"", SCHEMA_NAME || '.' || REFERENCED_TABLE_NAME AS ""OtherTableID"", REFERENCED_COLUMN_NAME AS ""OtherColumn"", POSITION AS ""Ordinal"" FROM REFERENTIAL_CONSTRAINTS WHERE SCHEMA_NAME " + SchemasFilter).ToList()); }
protected override DataTable?GetProcedureSchema(DataConnection dataConnection, string commandText, CommandType commandType, DataParameter[] parameters, GetSchemaOptions options) { switch (dataConnection.DataProvider.Name) { case ProviderName.SqlServer2000: case ProviderName.SqlServer2005: case ProviderName.SqlServer2008: return(CallBase()); } if (options.UseSchemaOnly || commandType == CommandType.Text) { return(CallBase()); } try { var tsql = $"exec {commandText} {parameters.Select(p => p.Name).Aggregate("", (p1, p2) => $"{p1}, {p2}", p => p.TrimStart(',', ' '))}"; var parms = parameters.Select(p => $"{p.Name} {p.DbType}").Aggregate("", (p1, p2) => $"{p1}, {p2}", p => p.TrimStart(',', ' ')); var dt = new DataTable(); dt.Columns.AddRange(new[] { new DataColumn { ColumnName = "DataTypeName", DataType = typeof(string) }, new DataColumn { ColumnName = "ColumnName", DataType = typeof(string) }, new DataColumn { ColumnName = "AllowDBNull", DataType = typeof(bool) }, new DataColumn { ColumnName = "ColumnSize", DataType = typeof(int) }, new DataColumn { ColumnName = "NumericPrecision", DataType = typeof(int) }, new DataColumn { ColumnName = "NumericScale", DataType = typeof(int) }, new DataColumn { ColumnName = "IsIdentity", DataType = typeof(bool) }, }); foreach (var item in dataConnection.QueryProc(new { name = "", is_nullable = false, system_type_name = "", max_length = 0, precision = 0, scale = 0, is_identity_column = false }, "sp_describe_first_result_set", new DataParameter("tsql", tsql), new DataParameter("params", parms) )) { var row = dt.NewRow(); row["DataTypeName"] = item.system_type_name.Split('(')[0]; row["ColumnName"] = item.name ?? ""; row["AllowDBNull"] = item.is_nullable; row["ColumnSize"] = item.max_length; row["NumericPrecision"] = item.precision; row["NumericScale"] = item.scale; row["IsIdentity"] = item.is_identity_column; dt.Rows.Add(row); } return(dt.Rows.Count == 0 ? null : dt); } catch { return(CallBase()); } DataTable?CallBase() { return(base.GetProcedureSchema(dataConnection, commandText, commandType, parameters, options)); } }
protected override IReadOnlyCollection <PrimaryKeyInfo> GetPrimaryKeys(DataConnection dataConnection, IEnumerable <TableSchema> tables, GetSchemaOptions options) { return(dataConnection.Query <PrimaryKeyInfo>( IsAzure ? @" SELECT k.TABLE_CATALOG COLLATE DATABASE_DEFAULT + '.' + k.TABLE_SCHEMA + '.' + k.TABLE_NAME as TableID, k.CONSTRAINT_NAME as PrimaryKeyName, k.COLUMN_NAME as ColumnName, k.ORDINAL_POSITION as Ordinal FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c ON k.CONSTRAINT_CATALOG = c.CONSTRAINT_CATALOG AND k.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA AND k.CONSTRAINT_NAME = c.CONSTRAINT_NAME WHERE c.CONSTRAINT_TYPE='PRIMARY KEY'" : @" SELECT k.TABLE_CATALOG + '.' + k.TABLE_SCHEMA + '.' + k.TABLE_NAME as TableID, k.CONSTRAINT_NAME as PrimaryKeyName, k.COLUMN_NAME as ColumnName, k.ORDINAL_POSITION as Ordinal FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c ON k.CONSTRAINT_CATALOG = c.CONSTRAINT_CATALOG AND k.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA AND k.CONSTRAINT_NAME = c.CONSTRAINT_NAME WHERE c.CONSTRAINT_TYPE='PRIMARY KEY'" ) .ToList()); }
protected override IReadOnlyCollection <ForeignKeyInfo> GetForeignKeys(DataConnection dataConnection, IEnumerable <TableSchema> tables, GetSchemaOptions options) { return(dataConnection.Query <ForeignKeyInfo>(@" SELECT fk.name as Name, DB_NAME() + '.' + SCHEMA_NAME(po.schema_id) + '.' + po.name as ThisTableID, pc.name as ThisColumn, DB_NAME() + '.' + SCHEMA_NAME(fo.schema_id) + '.' + fo.name as OtherTableID, fc.name as OtherColumn, fkc.constraint_column_id as Ordinal FROM sys.foreign_keys fk inner join sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id inner join sys.columns pc ON fkc.parent_column_id = pc.column_id and fkc.parent_object_id = pc.object_id inner join sys.objects po ON fk.parent_object_id = po.object_id inner join sys.columns fc ON fkc.referenced_column_id = fc.column_id and fkc.referenced_object_id = fc.object_id inner join sys.objects fo ON fk.referenced_object_id = fo.object_id ORDER BY ThisTableID, Ordinal" ) .ToList()); }
protected override Type?GetSystemType(string?dataType, string?columnType, DataTypeInfo?dataTypeInfo, long?length, int?precision, int?scale, GetSchemaOptions options) { switch (dataType) { case "tinyint": return(typeof(byte)); case "hierarchyid": case "geography": case "geometry": return(Provider.GetUdtTypeByName(dataType)); case "table type": return(typeof(DataTable)); } return(base.GetSystemType(dataType, columnType, dataTypeInfo, length, precision, scale, options)); }
protected override List <ColumnInfo> GetColumns(DataConnection dataConnection, GetSchemaOptions options) { return(dataConnection.Query <ColumnInfo>( IsAzure ? @" SELECT TABLE_CATALOG COLLATE DATABASE_DEFAULT + '.' + TABLE_SCHEMA + '.' + TABLE_NAME as TableID, COLUMN_NAME as Name, CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END as IsNullable, ORDINAL_POSITION as Ordinal, c.DATA_TYPE as DataType, CHARACTER_MAXIMUM_LENGTH as Length, ISNULL(NUMERIC_PRECISION, DATETIME_PRECISION) as [Precision], NUMERIC_SCALE as Scale, '' as [Description], COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') as IsIdentity, CASE WHEN c.DATA_TYPE = 'timestamp' OR COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') = 1 THEN 1 ELSE 0 END as SkipOnInsert, CASE WHEN c.DATA_TYPE = 'timestamp' OR COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') = 1 THEN 1 ELSE 0 END as SkipOnUpdate FROM INFORMATION_SCHEMA.COLUMNS c" : @" SELECT TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME as TableID, COLUMN_NAME as Name, CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END as IsNullable, ORDINAL_POSITION as Ordinal, c.DATA_TYPE as DataType, CHARACTER_MAXIMUM_LENGTH as Length, ISNULL(NUMERIC_PRECISION, DATETIME_PRECISION) as [Precision], NUMERIC_SCALE as Scale, ISNULL(CONVERT(varchar(8000), x.Value), '') as [Description], COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') as IsIdentity, CASE WHEN c.DATA_TYPE = 'timestamp' OR COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') = 1 THEN 1 ELSE 0 END as SkipOnInsert, CASE WHEN c.DATA_TYPE = 'timestamp' OR COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') = 1 THEN 1 ELSE 0 END as SkipOnUpdate FROM INFORMATION_SCHEMA.COLUMNS c LEFT JOIN sys.extended_properties x ON --OBJECT_ID('[' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']') = x.major_id AND OBJECT_ID('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']') = x.major_id AND COLUMNPROPERTY(OBJECT_ID('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'ColumnID') = x.minor_id AND x.name = 'MS_Description' AND x.class = 1" ) .Select(c => { var dti = GetDataType(c.DataType, options); if (dti != null) { switch (dti.CreateParameters) { case null: c.Length = null; c.Precision = null; c.Scale = null; break; case "scale": c.Length = null; if (c.Scale.HasValue) { c.Precision = null; } break; case "precision,scale": c.Length = null; break; case "max length": if (c.Length < 0) { c.Length = int.MaxValue; } c.Precision = null; c.Scale = null; break; case "length": c.Precision = null; c.Scale = null; break; case "number of bits used to store the mantissa": break; default: break; } } switch (c.DataType) { case "geometry": case "geography": case "hierarchyid": case "float": c.Length = null; c.Precision = null; c.Scale = null; break; } return c; }) .ToList()); }
protected override List <ColumnInfo> GetColumns(DataConnection dataConnection, GetSchemaOptions options) { var cs = ((DbConnection)dataConnection.Connection).GetSchema("Columns"); return (( from c in cs.AsEnumerable() let tschema = c.Field <string>("TABLE_SCHEMA") let schema = tschema == "sqlite_default_schema" ? "" : tschema let dataType = c.Field <string>("DATA_TYPE").Trim() select new ColumnInfo { TableID = c.Field <string>("TABLE_CATALOG") + "." + schema + "." + c.Field <string>("TABLE_NAME"), Name = c.Field <string>("COLUMN_NAME"), IsNullable = c.Field <bool> ("IS_NULLABLE"), Ordinal = Converter.ChangeTypeTo <int> (c["ORDINAL_POSITION"]), DataType = dataType, Length = Converter.ChangeTypeTo <long>(c["CHARACTER_MAXIMUM_LENGTH"]), Precision = Converter.ChangeTypeTo <int> (c["NUMERIC_PRECISION"]), Scale = Converter.ChangeTypeTo <int> (c["NUMERIC_SCALE"]), IsIdentity = c.Field <bool> ("AUTOINCREMENT"), SkipOnInsert = dataType == "timestamp", SkipOnUpdate = dataType == "timestamp", } ).ToList()); }
protected override List <ColumnInfo> GetColumns(DataConnection dataConnection, GetSchemaOptions options) { var cs = dataConnection.Connection.GetSchema("Columns"); return (( from c in cs.AsEnumerable() let typeName = c.Field <string>("TYPE_NAME") let dt = GetDataType(typeName, null, options) let size = Converter.ChangeTypeTo <int?>(c["COLUMN_SIZE"]) let scale = Converter.ChangeTypeTo <int?>(c["DECIMAL_DIGITS"]) select new ColumnInfo { TableID = c.Field <string>("TABLE_CAT") + "." + c.Field <string>("TABLE_SCHEM") + "." + c.Field <string>("TABLE_NAME"), Name = c.Field <string>("COLUMN_NAME") !, IsNullable = c.Field <short> ("NULLABLE") == 1, Ordinal = Converter.ChangeTypeTo <int>(c["ORDINAL_POSITION"]), DataType = dt?.TypeName, Length = dt?.CreateParameters != null && dt.CreateParameters.Contains("length") && size != 0 ? size : null, Precision = dt?.CreateParameters != null && dt.CreateParameters.Contains("precision") ? size : null, Scale = dt?.CreateParameters != null && dt.CreateParameters.Contains("scale") ? scale : null, IsIdentity = typeName == "COUNTER", Description = c.Field <string>("REMARKS") }
protected override List <ProcedureInfo>?GetProcedures(DataConnection dataConnection, GetSchemaOptions options) { return(dataConnection.Query <ProcedureInfo>(@" SELECT SPECIFIC_CATALOG COLLATE DATABASE_DEFAULT + '.' + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME as ProcedureID, SPECIFIC_CATALOG as CatalogName, SPECIFIC_SCHEMA as SchemaName, SPECIFIC_NAME as ProcedureName, CASE WHEN ROUTINE_TYPE = 'FUNCTION' THEN 1 ELSE 0 END as IsFunction, CASE WHEN ROUTINE_TYPE = 'FUNCTION' AND DATA_TYPE = 'TABLE' THEN 1 ELSE 0 END as IsTableFunction, CASE WHEN SPECIFIC_SCHEMA = 'dbo' THEN 1 ELSE 0 END as IsDefaultSchema FROM INFORMATION_SCHEMA.ROUTINES" ) .ToList()); }
protected override IReadOnlyCollection <ForeignKeyInfo> GetForeignKeys(DataConnection dataConnection, IEnumerable <TableSchema> tables, GetSchemaOptions options) { var data = dataConnection.Query <ForeignKeyInfo>( @" SELECT COALESCE(rc.CONSTRAINT_CATALOG, '') + '.' + COALESCE(rc.CONSTRAINT_SCHEMA, '') + '.' + rc.CONSTRAINT_TABLE_NAME ThisTableID, COALESCE(rc.UNIQUE_CONSTRAINT_CATALOG, '') + '.' + COALESCE(rc.UNIQUE_CONSTRAINT_SCHEMA, '') + '.' + rc.UNIQUE_CONSTRAINT_TABLE_NAME OtherTableID, rc.CONSTRAINT_NAME Name, tc.COLUMN_NAME ThisColumn, oc.COLUMN_NAME OtherColumn FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE tc ON tc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE oc ON oc.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME"); return(data.ToList()); }
protected override IReadOnlyCollection <ForeignKeyInfo> GetForeignKeys(DataConnection dataConnection, IEnumerable <TableSchema> tables, GetSchemaOptions options) { return(dataConnection.Query <ForeignKeyInfo>(@" SELECT rc.CONSTRAINT_NAME as Name, fk.TABLE_CATALOG COLLATE DATABASE_DEFAULT + '.' + fk.TABLE_SCHEMA + '.' + fk.TABLE_NAME as ThisTableID, fk.COLUMN_NAME as ThisColumn, pk.TABLE_CATALOG COLLATE DATABASE_DEFAULT + '.' + pk.TABLE_SCHEMA + '.' + pk.TABLE_NAME as OtherTableID, pk.COLUMN_NAME as OtherColumn, pk.ORDINAL_POSITION as Ordinal FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE fk ON fk.CONSTRAINT_CATALOG = rc.CONSTRAINT_CATALOG AND fk.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA AND fk.CONSTRAINT_NAME = rc.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk ON pk.CONSTRAINT_CATALOG = rc.UNIQUE_CONSTRAINT_CATALOG AND pk.CONSTRAINT_SCHEMA = rc.UNIQUE_CONSTRAINT_SCHEMA AND pk.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME WHERE fk.ORDINAL_POSITION = pk.ORDINAL_POSITION ORDER BY ThisTableID, Ordinal" ) .ToList()); }
protected override Type?GetSystemType(string?dataType, string?columnType, DataTypeInfo?dataTypeInfo, long?length, int?precision, int?scale, GetSchemaOptions options) { return((dataType?.ToLower()) switch { "tinyint" => typeof(byte), _ => base.GetSystemType(dataType, columnType, dataTypeInfo, length, precision, scale, options), });
protected override List <ColumnInfo> GetColumns(DataConnection dataConnection, GetSchemaOptions options) { if (SchemasFilter == null) { return(new List <ColumnInfo>()); } var sqlText = @" SELECT combined.SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, CAST(CASE WHEN IS_NULLABLE = 'TRUE' THEN 1 ELSE 0 END AS TINYINT) AS IS_NULLABLE, POSITION, DATA_TYPE_NAME, LENGTH, SCALE, COMMENTS, CAST(CASE WHEN GENERATION_TYPE = 'BY DEFAULT AS IDENTITY' THEN 1 ELSE 0 END AS TINYINT) AS IS_IDENTITY FROM (SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, IS_NULLABLE, POSITION, DATA_TYPE_NAME, LENGTH, SCALE, COMMENTS, GENERATION_TYPE FROM SYS.TABLE_COLUMNS WHERE SCHEMA_NAME " + SchemasFilter + @" UNION ALL SELECT SCHEMA_NAME, VIEW_NAME AS TABLE_NAME, COLUMN_NAME, IS_NULLABLE, POSITION, DATA_TYPE_NAME, LENGTH, SCALE, COMMENTS, GENERATION_TYPE FROM SYS.VIEW_COLUMNS WHERE SCHEMA_NAME " + SchemasFilter + @" ) AS combined JOIN SYS.SCHEMAS AS s ON combined.SCHEMA_NAME = s.SCHEMA_NAME WHERE s.HAS_PRIVILEGES = 'TRUE'" ; var query = dataConnection.Query(x => { var schemaName = x.GetString(0); var tableName = x.GetString(1); var columnName = x.GetString(2); var isNullable = x.GetBoolean(3); var position = x.GetInt32(4); var dataTypeName = x.GetString(5); var length = x.GetInt32(6); var scale = x.IsDBNull(7) ? 0 : x.GetInt32(7); var comments = x.IsDBNull(8) ? null : x.GetString(8); var isIdentity = x.GetBoolean(9); var tableId = schemaName + '.' + tableName; return(new ColumnInfo { DataType = dataTypeName, Description = comments, IsIdentity = isIdentity, IsNullable = isNullable, Length = length, Name = columnName, Ordinal = position, Precision = length, Scale = scale, TableID = tableId }); }, sqlText); return(query.ToList()); }
protected override IReadOnlyCollection <PrimaryKeyInfo> GetPrimaryKeys(DataConnection dataConnection, IEnumerable <TableSchema> tables, GetSchemaOptions options) { var data = dataConnection.Query <PrimaryKeyInfo>( @" SELECT COALESCE(TABLE_CATALOG, '') + '.' + COALESCE(TABLE_SCHEMA, '') + '.' + TABLE_NAME AS TableID, INDEX_NAME AS PrimaryKeyName, COLUMN_NAME AS ColumnName, ORDINAL_POSITION AS Ordinal FROM INFORMATION_SCHEMA.INDEXES WHERE PRIMARY_KEY = 1" ); return(data.ToList()); }
protected override List <ProcedureInfo>?GetProcedures(DataConnection dataConnection, GetSchemaOptions options) { if (SchemasFilter == null) { return(null); } return(dataConnection.Query(rd => { var schema = rd.GetString(0); var procedure = rd.GetString(1); var isFunction = rd.GetBoolean(2); var isTableFunction = rd.GetBoolean(3); var definition = rd.IsDBNull(4) ? null : rd.GetString(4); return new ProcedureInfo { ProcedureID = string.Concat(schema, '.', procedure), CatalogName = null, IsAggregateFunction = false, IsDefaultSchema = schema == DefaultSchema, IsFunction = isFunction, IsTableFunction = isTableFunction, ProcedureDefinition = definition, ProcedureName = procedure, SchemaName = schema }; }, @" SELECT SCHEMA_NAME, PROCEDURE_NAME, 0 AS IS_FUNCTION, 0 AS IS_TABLE_FUNCTION, DEFINITION FROM PROCEDURES WHERE SCHEMA_NAME " + SchemasFilter + @" UNION ALL SELECT F.SCHEMA_NAME, F.FUNCTION_NAME AS PROCEDURE_NAME, 1 AS IS_FUNCTION, CASE WHEN FP.DATA_TYPE_NAME = 'TABLE_TYPE' THEN 1 ELSE 0 END AS IS_TABLE_FUNCTION, DEFINITION FROM FUNCTIONS AS F JOIN FUNCTION_PARAMETERS AS FP ON F.FUNCTION_OID = FP.FUNCTION_OID WHERE FP.PARAMETER_TYPE = 'RETURN' AND F.SCHEMA_NAME " + SchemasFilter) .ToList()); }
protected override List <ColumnInfo> GetColumns(DataConnection dataConnection, GetSchemaOptions options) { var cs = ((DbConnection)dataConnection.Connection).GetSchema("Columns"); return (( from c in cs.AsEnumerable() select new ColumnInfo { TableID = c.Field <string>("TABLE_CATALOG") + "." + c.Field <string>("TABLE_SCHEMA") + "." + c.Field <string>("TABLE_NAME"), Name = c.Field <string>("COLUMN_NAME"), IsNullable = c.Field <string>("IS_NULLABLE") == "YES", Ordinal = Converter.ChangeTypeTo <int> (c["ORDINAL_POSITION"]), DataType = c.Field <string>("DATA_TYPE"), Length = Converter.ChangeTypeTo <long>(c["CHARACTER_MAXIMUM_LENGTH"]), Precision = Converter.ChangeTypeTo <int> (c["NUMERIC_PRECISION"]), Scale = Converter.ChangeTypeTo <int> (c["NUMERIC_SCALE"]), IsIdentity = false, } ).ToList()); }
protected override List <ColumnSchema> GetProcedureResultColumns(DataTable resultTable, GetSchemaOptions options) { return (( from r in resultTable.AsEnumerable() let systemType = r.Field <Type>("DataType") let columnName = GetEmptyStringIfInvalidColumnName(r.Field <string>("ColumnName")) let providerType = Converter.ChangeTypeTo <int>(r["ProviderType"]) let dataType = GetDataTypeByProviderDbType(providerType, options) let columnType = dataType?.TypeName let length = r.Field <int>("ColumnSize") let precision = Converter.ChangeTypeTo <int>(r["NumericPrecision"]) let scale = Converter.ChangeTypeTo <int>(r["NumericScale"]) let isNullable = Converter.ChangeTypeTo <bool>(r["AllowDBNull"]) select new ColumnSchema { ColumnType = GetDbType(options, columnType, dataType, length, precision, scale, null, null, null), ColumnName = columnName, IsNullable = isNullable, MemberName = ToValidName(columnName), MemberType = ToTypeName(systemType, isNullable), SystemType = systemType ?? typeof(object), DataType = GetDataType(columnType, null, length, precision, scale), ProviderSpecificType = GetProviderSpecificType(columnType), } ).ToList()); }
protected override IReadOnlyCollection <PrimaryKeyInfo> GetPrimaryKeys(DataConnection dataConnection, IEnumerable <TableSchema> tables, GetSchemaOptions options) { return(_primaryKeys = dataConnection.Query( rd => new PrimaryKeyInfo { TableID = dataConnection.Connection.Database + "." + rd.ToString(0) + "." + rd.ToString(1), PrimaryKeyName = rd.ToString(2) !, ColumnName = rd.ToString(3) !, Ordinal = Converter.ChangeTypeTo <int>(rd[4]) }, @"
protected override void LoadProcedureTableSchema(DataConnection dataConnection, GetSchemaOptions options, ProcedureSchema procedure, string commandText, List <TableSchema> tables) { CommandType commandType; DataParameter[] parameters; if (procedure.IsTableFunction) { commandText = "SELECT * FROM " + commandText + "("; commandText += string.Join(",", procedure.Parameters.Select(p => ( p.SystemType == typeof(DateTime) ? "'" + DateTime.Now + "'" : DefaultValue.GetValue(p.SystemType)) ?? "''")); commandText += ")"; commandType = CommandType.Text; parameters = new DataParameter[0]; } else { commandType = CommandType.StoredProcedure; parameters = HanaSchemaOptions != null ? (HanaSchemaOptions.GetStoredProcedureParameters(procedure) ?? GetStoredProcedureDataParameters(procedure)) : GetStoredProcedureDataParameters(procedure); } try { var st = GetProcedureSchema(dataConnection, commandText, commandType, parameters, options); procedure.IsLoaded = true; if (st != null) { procedure.ResultTable = new TableSchema() { IsProcedureResult = true, TypeName = ToValidName(procedure.ProcedureName + "Result"), ForeignKeys = new List <ForeignKeySchema>(), Columns = GetProcedureResultColumns(st, options) }; foreach (var column in procedure.ResultTable.Columns) { column.Table = procedure.ResultTable; } procedure.SimilarTables = ( from t in tables where t.Columns.Count == procedure.ResultTable.Columns.Count let zip = t.Columns.Zip(procedure.ResultTable.Columns, (c1, c2) => new { c1, c2 }) where zip.All(z => z.c1.ColumnName == z.c2.ColumnName && z.c1.SystemType == z.c2.SystemType) select t ).ToList(); } } catch (Exception ex) { procedure.ResultException = ex; } }
protected override List <ProcedureParameterInfo> GetProcedureParameters(DataConnection dataConnection, IEnumerable <ProcedureInfo> procedures, GetSchemaOptions options) { // uses ALL_ARGUMENTS view // https://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_1014.htm#REFRN20015 // SELECT * FROM ALL_ARGUMENTS WHERE DATA_LEVEL = 0 AND (OWNER = :OWNER OR :OWNER is null) AND (OBJECT_NAME = :OBJECTNAME OR :OBJECTNAME is null) var pps = ((DbConnection)dataConnection.Connection).GetSchema("ProcedureParameters"); // SEQUENCE filter filters-out non-argument records without DATA_TYPE // check https://llblgen.com/tinyforum/Messages.aspx?ThreadID=22795 return (( from pp in pps.AsEnumerable().Where(_ => Converter.ChangeTypeTo <int>(_["SEQUENCE"]) > 0) let schema = pp.Field <string>("OWNER") // not null let name = pp.Field <string>("OBJECT_NAME") // nullable (???) let direction = pp.Field <string>("IN_OUT") // nullable: IN, OUT, IN/OUT where IncludedSchemas.Count != 0 || ExcludedSchemas.Count != 0 || schema == _currentUser select new ProcedureParameterInfo { ProcedureID = schema + "." + name, ParameterName = pp.Field <string>("ARGUMENT_NAME"), // nullable DataType = pp.Field <string>("DATA_TYPE"), // nullable, but only for sequence = 0 Ordinal = Converter.ChangeTypeTo <int> (pp["POSITION"]), // not null, 0 - return value Length = Converter.ChangeTypeTo <long?>(pp["DATA_LENGTH"]), // nullable Precision = Converter.ChangeTypeTo <int?> (pp["DATA_PRECISION"]), // nullable Scale = Converter.ChangeTypeTo <int?> (pp["DATA_SCALE"]), // nullable IsIn = direction.StartsWith("IN"), IsOut = direction.EndsWith("OUT"), IsNullable = true } ).ToList()); }
protected override List <TableSchema> GetProviderSpecificTables(DataConnection dataConnection, GetSchemaOptions options) { if (!HasAccessForCalculationViews) { return(new List <TableSchema>()); } var result = ( from v in GetViewsWithParameters(dataConnection) join p in GetParametersForViews(dataConnection) on v.TableID equals p.ProcedureID into pgroup where (IncludedSchemas.Count == 0 || IncludedSchemas.Contains(v.SchemaName)) && (ExcludedSchemas.Count == 0 || !ExcludedSchemas.Contains(v.SchemaName)) && (IncludedCatalogs.Count == 0 || IncludedCatalogs.Contains(v.CatalogName !)) && (ExcludedCatalogs.Count == 0 || !ExcludedCatalogs.Contains(v.CatalogName !)) select new ViewWithParametersTableSchema { ID = v.TableID, CatalogName = v.CatalogName, SchemaName = v.SchemaName, TableName = v.TableName, Description = v.Description, IsDefaultSchema = v.IsDefaultSchema, IsView = v.IsView, TypeName = ToValidName(v.TableName), Columns = new List <ColumnSchema>(), ForeignKeys = new List <ForeignKeySchema>(), Parameters = ( from pr in pgroup let dt = GetDataType(pr.DataType, options) let systemType = GetSystemType(pr.DataType, null, dt, pr.Length ?? 0, pr.Precision, pr.Scale, options) orderby pr.Ordinal select new ParameterSchema { SchemaName = pr.ParameterName, SchemaType = GetDbType(options, pr.DataType, dt, pr.Length ?? 0, pr.Precision, pr.Scale, pr.UDTCatalog, pr.UDTSchema, pr.UDTName), IsIn = pr.IsIn, IsOut = pr.IsOut, IsResult = pr.IsResult, Size = pr.Length, ParameterName = ToValidName(pr.ParameterName !), ParameterType = ToTypeName(systemType, !pr.IsIn), SystemType = systemType ?? typeof(object), DataType = GetDataType(pr.DataType, null, pr.Length, pr.Precision, pr.Scale), ProviderSpecificType = GetProviderSpecificType(pr.DataType), IsNullable = pr.IsNullable }
protected override List <ColumnInfo> GetColumns(DataConnection dataConnection, GetSchemaOptions options) { return(dataConnection.Query <ColumnInfo>(@" SELECT TABLE_CATALOG COLLATE DATABASE_DEFAULT + '.' + TABLE_SCHEMA + '.' + TABLE_NAME as TableID, COLUMN_NAME as Name, CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END as IsNullable, ORDINAL_POSITION as Ordinal, c.DATA_TYPE as DataType, CHARACTER_MAXIMUM_LENGTH as Length, ISNULL(NUMERIC_PRECISION, DATETIME_PRECISION) as [Precision], NUMERIC_SCALE as Scale, COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') as IsIdentity, CASE WHEN c.DATA_TYPE = 'timestamp' OR COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') = 1 THEN 1 ELSE 0 END as SkipOnInsert, CASE WHEN c.DATA_TYPE = 'timestamp' OR COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') = 1 THEN 1 ELSE 0 END as SkipOnUpdate FROM INFORMATION_SCHEMA.COLUMNS c" ) .ToList()); }
protected override List <ProcedureParameterInfo> GetProcedureParameters(DataConnection dataConnection, IEnumerable <ProcedureInfo> procedures, GetSchemaOptions options) { return(dataConnection.Query <ProcedureParameterInfo>( @"SELECT SPECIFIC_CATALOG COLLATE DATABASE_DEFAULT + '.' + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME as ProcedureID, ORDINAL_POSITION as Ordinal, PARAMETER_MODE as Mode, PARAMETER_NAME as ParameterName, DATA_TYPE as DataType, CHARACTER_MAXIMUM_LENGTH as Length, NUMERIC_PRECISION as [Precision], NUMERIC_SCALE as Scale, CASE WHEN PARAMETER_MODE = 'IN' OR PARAMETER_MODE = 'INOUT' THEN 1 ELSE 0 END as IsIn, CASE WHEN PARAMETER_MODE = 'OUT' OR PARAMETER_MODE = 'INOUT' THEN 1 ELSE 0 END as IsOut, CASE WHEN IS_RESULT = 'YES' THEN 1 ELSE 0 END as IsResult, USER_DEFINED_TYPE_CATALOG as UDTCatalog, USER_DEFINED_TYPE_SCHEMA as UDTSchema, USER_DEFINED_TYPE_NAME as UDTName, 1 as IsNullable FROM INFORMATION_SCHEMA.PARAMETERS" ) .ToList()); }
protected override Type?GetSystemType(string?dataType, string?columnType, DataTypeInfo?dataTypeInfo, long?length, int?precision, int?scale, GetSchemaOptions options) { if (dataType == "NUMBER" && precision > 0 && (scale ?? 0) == 0) { if (precision < 3) { return(typeof(sbyte)); } if (precision < 5) { return(typeof(short)); } if (precision < 10) { return(typeof(int)); } if (precision < 20) { return(typeof(long)); } } if (dataType?.StartsWith("TIMESTAMP") == true) { return(dataType.EndsWith("TIME ZONE") ? typeof(DateTimeOffset) : typeof(DateTime)); } return(base.GetSystemType(dataType, columnType, dataTypeInfo, length, precision, scale, options)); }