#region License
// Copyright 2005-2019 Paul Kohler (https://github.com/paulkohler/minisqlquery). All rights reserved.
// This source code is made available under the terms of the GNU Lesser General Public License v3.0
// https://github.com/paulkohler/minisqlquery/blob/master/LICENSE
#endregion
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
namespace MiniSqlQuery.Core.DbModel
{
/// <summary>
/// The generic schema service.
/// </summary>
public class GenericSchemaService : IDatabaseSchemaService
{
/// <summary>The _connection.</summary>
private string _connection;
/// <summary>Gets or sets a value indicating whether ForeignKeyInformationAvailable.</summary>
/// <value>The foreign key information available.</value>
public bool ForeignKeyInformationAvailable { get; set; }
/// <summary>Gets or sets ProviderName.</summary>
/// <value>The provider name.</value>
public string ProviderName { get; set; }
/// <summary>Gets a database object model that represents the items defined by the <paramref name="connection"/>.</summary>
/// <param name="connection">The connection string.</param>
/// <returns>An instance of <see cref="DbModelInstance"/> describing the database.</returns>
public virtual DbModelInstance GetDbObjectModel(string connection)
{
_connection = connection;
DbModelInstance model = new DbModelInstance();
DbProviderFactory factory = DbProviderFactories.GetFactory(ProviderName);
try
{
using (DbConnection dbConn = factory.CreateConnection())
{
dbConn.ConnectionString = connection;
dbConn.Open();
DataTable tables = dbConn.GetSchema("Tables");
Dictionary<string, DbModelType> dbTypes = GetDbTypes(dbConn);
model.Types = dbTypes;
model.ProviderName = ProviderName;
model.ConnectionString = _connection;
if (tables == null)
{
return model;
}
DataView tablesDV = new DataView(tables, "TABLE_TYPE='TABLE' OR TABLE_TYPE='BASE TABLE'", "TABLE_SCHEMA, TABLE_NAME", DataViewRowState.CurrentRows);
foreach (DataRowView row in tablesDV)
{
string schemaName = SafeGetString(row.Row, "TABLE_SCHEMA");
string tableName = SafeGetString(row.Row, "TABLE_NAME");
DbModelTable dbTable = new DbModelTable { Schema = schemaName, Name = tableName };
model.Add(dbTable);
DataTable schemaTableKeyInfo = GetTableKeyInfo(dbConn, schemaName, tableName);
GetColumnsForTable(dbTable, schemaTableKeyInfo, dbTypes);
}
DataView viewsDV = new DataView(tables, "TABLE_TYPE='VIEW'", "TABLE_SCHEMA, TABLE_NAME", DataViewRowState.CurrentRows);
foreach (DataRowView row in viewsDV)
{
string schemaName = SafeGetString(row.Row, "TABLE_SCHEMA");
string tableName = SafeGetString(row.Row, "TABLE_NAME");
DbModelView dbTable = new DbModelView { Schema = schemaName, Name = tableName };
model.Add(dbTable);
DataTable schemaTableKeyInfo = GetTableKeyInfo(dbConn, schemaName, tableName);
GetColumnsForTable(dbTable, schemaTableKeyInfo, dbTypes);
}
// build FK relationships
if (model.Tables != null)
{
foreach (DbModelTable table in model.Tables)
{
GetForeignKeyReferencesForTable(dbConn, table);
ProcessForeignKeyReferencesForTable(dbConn, table);
}
}
// build FK relationships
if (model.Views != null)
{
foreach (DbModelView view in model.Views)
{
GetForeignKeyReferencesForTable(dbConn, view);
ProcessForeignKeyReferencesForTable(dbConn, view);
}
}
}
}
catch (Exception)
{
// catch all for providers that are not implementing the schema info.
return model;
}
return model;
}
/// <summary>The get db types.</summary>
/// <param name="connection">The connection.</param>
/// <returns>A dictionary of named <see cref="DbModelType"/> objects supported by the database.</returns>
/// <exception cref="ArgumentNullException">If the <paramref name="connection"/> is null.</exception>
public virtual Dictionary<string, DbModelType> GetDbTypes(DbConnection connection)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
Dictionary<string, DbModelType> dbTypes = new Dictionary<string, DbModelType>();
DataTable dataTypes = connection.GetSchema("DataTypes");
foreach (DataRow row in dataTypes.Rows)
{
string typeName = SafeGetString(row, "TypeName");
int columnSize = SafeGetInt(row, "ColumnSize");
DbModelType dbType = new DbModelType(typeName, columnSize);
// Some providers may double up on schema info, check first:
if (!dbTypes.ContainsKey(typeName.ToLower()))
{
dbTypes.Add(typeName.ToLower(), dbType);
dbType.CreateFormat = SafeGetString(row, "CreateFormat");
dbType.CreateParameters = SafeGetString(row, "CreateParameters");
dbType.LiteralPrefix = SafeGetString(row, "LiteralPrefix");
dbType.LiteralSuffix = SafeGetString(row, "LiteralSuffix");
dbType.SystemType = Type.GetType(SafeGetString(row, "DataType"));
dbType.ProviderDbType = SafeGetString(row, "ProviderDbType");
}
}
return dbTypes;
}
/// <returns>The get description of the database.</returns>
public string GetDescription()
{
return "todo";
}
/// <summary>The get columns for table.</summary>
/// <param name="dbTable">The db table.</param>
/// <param name="schemaTableKeyInfo">The schema table key info.</param>
/// <param name="dbTypes">The db types.</param>
protected virtual void GetColumnsForTable(DbModelTable dbTable, DataTable schemaTableKeyInfo, Dictionary<string, DbModelType> dbTypes)
{
if (schemaTableKeyInfo == null)
{
return;
}
foreach (DataRow columnRow in schemaTableKeyInfo.Rows)
{
if (SafeGetBool(columnRow, "IsHidden"))
{
continue;
}
string columnName = SafeGetString(columnRow, "ColumnName");
string dataType = GetDataTypeNameForColumn(dbTable, schemaTableKeyInfo, columnRow);
// note - need a better work around for columns missing the data type info (e.g. access)
if (string.IsNullOrEmpty(dataType))
{
// try using the "ProviderDbType" to match
string providerDbType = SafeGetString(columnRow, "ProviderType");
foreach (var type in dbTypes.Values)
{
if (type.ProviderDbType == providerDbType)
{
dataType = type.Name;
break;
}
}
}
DbModelType dbType = DbModelType.Create(
dbTypes,
dataType,
SafeGetInt(columnRow, "ColumnSize"),
SafeGetInt(columnRow, "Precision"),
SafeGetInt(columnRow, "Scale"),
SafeGetString(columnRow, "DataType"));
// todo - FK info
DbModelColumn dbColumn = new DbModelColumn
{
Name = columnName,
// Name = MakeSqlFriendly(columnName),
Nullable = SafeGetBool(columnRow, "AllowDBNull"),
IsKey = SafeGetBool(columnRow, "IsKey"),
IsUnique = SafeGetBool(columnRow, "IsUnique"),
IsRowVersion = SafeGetBool(columnRow, "IsRowVersion"),
IsIdentity = SafeGetBool(columnRow, "IsIdentity"),
IsAutoIncrement = SafeGetBool(columnRow, "IsAutoIncrement"),
IsReadOnly = SafeGetBool(columnRow, "IsReadOnly"),
DbType = dbType,
};
dbTable.Add(dbColumn);
}
}
/// <summary>The get data type name for column.</summary>
/// <param name="dbTable">The db table.</param>
/// <param name="schemaTableKeyInfo">The schema table key info.</param>
/// <param name="columnRow">The column row.</param>
/// <returns>The get data type name for column.</returns>
protected virtual string GetDataTypeNameForColumn(DbModelTable dbTable, DataTable schemaTableKeyInfo, DataRow columnRow)
{
return SafeGetString(columnRow, "DataTypeName");
}
/// <summary>The get foreign key references for table.</summary>
/// <param name="dbConn">The db conn.</param>
/// <param name="dbTable">The db table.</param>
protected virtual void GetForeignKeyReferencesForTable(DbConnection dbConn, DbModelTable dbTable)
{
// foreach (DbModelColumn column in dbTable.Columns)
// {
// // KF info for DB's varies widley, needs to be implemented by derived class
// column.ForeignKeyReference = DbModelForeignKeyReference.NullForeignKeyReference;
// }
}
/// <summary>The get table key information.</summary>
/// <param name="dbConn">The database connection.</param>
/// <param name="schema">The schema.</param>
/// <param name="name">The name of the table.</param>
/// <returns>A <see cref="DataTable"/> describing the tables columns and key information.</returns>
protected virtual DataTable GetTableKeyInfo(DbConnection dbConn, string schema, string name)
{
DataTable schemaTableKeyInfo = null;
try
{
using (DbCommand command = dbConn.CreateCommand())
{
string tableName = Utility.RenderSafeSchemaObjectName(schema, name);
command.CommandText = "SELECT * FROM " + MakeSqlFriendly(tableName);
using (DbDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo))
{
schemaTableKeyInfo = reader.GetSchemaTable();
}
}
}
catch (Exception exp)
{
// Generic catch all - not all provoders stick to the DbException base:
Debug.WriteLine(GetType().FullName + " ERROR: " + exp.Message);
}
return schemaTableKeyInfo;
}
/// <summary>The make sql friendly.</summary>
/// <param name="name">The name.</param>
/// <returns>The make sql friendly.</returns>
protected virtual string MakeSqlFriendly(string name)
{
return Utility.MakeSqlFriendly(name);
}
/// <summary>The process foreign key references for table.</summary>
/// <param name="dbConn">The db conn.</param>
/// <param name="dbTable">The db table.</param>
protected virtual void ProcessForeignKeyReferencesForTable(DbConnection dbConn, DbModelTable dbTable)
{
}
/// <summary>The safe get bool.</summary>
/// <param name="row">The row.</param>
/// <param name="columnName">The column name.</param>
/// <returns>The safe get bool.</returns>
protected bool SafeGetBool(DataRow row, string columnName)
{
if (row.Table.Columns.Contains(columnName) && !row.IsNull(columnName))
{
string value = row[columnName].ToString();
switch (value.ToLower())
{
case "no":
case "false":
return false;
case "yes":
case "true":
return true;
}
}
return false;
}
/// <summary>The safe get int.</summary>
/// <param name="row">The row.</param>
/// <param name="columnName">The column name.</param>
/// <returns>The safe get int.</returns>
protected virtual int SafeGetInt(DataRow row, string columnName)
{
try
{
int result = -1;
if (row.Table.Columns.Contains(columnName) && !row.IsNull(columnName))
{
result = Convert.ToInt32(row[columnName]);
}
return result;
}
catch (OverflowException)
{
// In Oracle Maximum size for column is larger than Max Int32, instead of changing return value, just coerce on Max.Int32.
return Int32.MaxValue;
}
}
/// <summary>The safe get string.</summary>
/// <param name="row">The row.</param>
/// <param name="columnName">The column name.</param>
/// <returns>The safe get string.</returns>
protected string SafeGetString(DataRow row, string columnName)
{
string result = string.Empty;
if (row.Table.Columns.Contains(columnName) && !row.IsNull(columnName))
{
result = row[columnName].ToString();
}
return result;
}
}
}
|