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