#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;
namespace MiniSqlQuery.Core.DbModel
{
/// <summary>SQL Compact Edition schema service.
/// Made possible with contributions form ExportSQLCE project.
/// http://exportsqlce.codeplex.com/
/// http://erikej.blogspot.com/</summary>
public class SqlCeSchemaService : GenericSchemaService
{
/// <summary>The ma x_ binar y_ colum n_ size.</summary>
internal static readonly int MAX_BINARY_COLUMN_SIZE = 8000;
/// <summary>The ma x_ imag e_ colum n_ size.</summary>
internal static readonly int MAX_IMAGE_COLUMN_SIZE = 1073741823;
/// <summary>The ma x_ ncha r_ colum n_ size.</summary>
internal static readonly int MAX_NCHAR_COLUMN_SIZE = 4000;
/// <summary>The ma x_ ntex t_ colum n_ size.</summary>
internal static readonly int MAX_NTEXT_COLUMN_SIZE = 536870911;
/// <summary>The _connection.</summary>
private string _connection;
/// <summary>The get db object model.</summary>
/// <param name="connection">The connection.</param>
/// <returns></returns>
public override DbModelInstance GetDbObjectModel(string connection)
{
_connection = connection;
DbModelInstance model = new DbModelInstance();
DbProviderFactory factory = DbProviderFactories.GetFactory(ProviderName);
using (DbConnection dbConn = factory.CreateConnection())
{
dbConn.ConnectionString = connection;
dbConn.Open();
QueryTableNames(dbConn, model);
Dictionary<string, DbModelType> dbTypes = GetDbTypes(dbConn);
model.Types = dbTypes;
model.ProviderName = ProviderName;
model.ConnectionString = _connection;
// build all table info
foreach (DbModelTable table in model.Tables)
{
DataTable schemaTableKeyInfo = GetTableKeyInfo(dbConn, null, table.Name);
GetColumnsForTable(table, schemaTableKeyInfo, dbTypes);
}
// build FK relationships
foreach (DbModelTable table in model.Tables)
{
GetForeignKeyReferencesForTable(dbConn, table);
ProcessForeignKeyReferencesForTable(dbConn, table);
}
return model;
}
}
/// <summary>Gets the db types for the SQL CE provider.</summary>
/// <param name="connection">The connection (not required).</param>
/// <returns></returns>
public override Dictionary<string, DbModelType> GetDbTypes(DbConnection connection)
{
Dictionary<string, DbModelType> dbTypes = new Dictionary<string, DbModelType>();
string dataTypesSql = "SELECT * FROM information_schema.provider_types";
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = dataTypesSql;
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string typeName = (string)reader["TYPE_NAME"];
int columnSize = Convert.ToInt32(reader["COLUMN_SIZE"]);
DbModelType dbType = new DbModelType(typeName, columnSize);
dbType.CreateParameters = Convert.ToString(reader["CREATE_PARAMS"]);
dbType.LiteralPrefix = Convert.ToString(reader["LITERAL_PREFIX"]);
dbType.LiteralSuffix = Convert.ToString(reader["LITERAL_SUFFIX"]);
dbType.ProviderDbType = Convert.ToString(reader["DATA_TYPE"]);
FixCreateFormat(dbType);
FixMaxLengths(dbType);
AssignSystemTypes(dbType);
dbTypes.Add(typeName, dbType);
}
}
}
return dbTypes;
}
/// <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 override string GetDataTypeNameForColumn(DbModelTable dbTable, DataTable schemaTableKeyInfo, DataRow columnRow)
{
return SafeGetString(columnRow, "ProviderType");
}
/// <summary>The get foreign key references for table.</summary>
/// <param name="dbConn">The db conn.</param>
/// <param name="dbTable">The db table.</param>
protected override void GetForeignKeyReferencesForTable(DbConnection dbConn, DbModelTable dbTable)
{
ForeignKeyInformationAvailable = true;
try
{
using (var cmd = dbConn.CreateCommand())
{
cmd.CommandText =
string.Format(
@"SELECT
KCU1.TABLE_NAME AS FK_TABLE_NAME,
KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME,
KCU1.COLUMN_NAME AS FK_COLUMN_NAME,
KCU2.TABLE_NAME AS UQ_TABLE_NAME,
KCU2.CONSTRAINT_NAME AS UQ_CONSTRAINT_NAME,
KCU2.COLUMN_NAME AS UQ_COLUMN_NAME,
RC.UPDATE_RULE,
RC.DELETE_RULE,
KCU2.ORDINAL_POSITION AS UQ_ORDINAL_POSITION,
KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 ON KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION AND KCU2.TABLE_NAME = RC.UNIQUE_CONSTRAINT_TABLE_NAME
WHERE KCU1.TABLE_NAME = '{0}'
ORDER BY
FK_TABLE_NAME,
FK_CONSTRAINT_NAME,
FK_ORDINAL_POSITION
",
dbTable.Name);
cmd.CommandType = CommandType.Text;
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
dbTable.Constraints.Add(new DbModelConstraint
{
ConstraintTableName = dr.GetString(0),
ConstraintName = dr.GetString(1),
ColumnName = dr.GetString(2),
UniqueConstraintTableName = dr.GetString(3),
UniqueConstraintName = dr.GetString(4),
UniqueColumnName = dr.GetString(5),
UpdateRule = dr.GetString(6),
DeleteRule = dr.GetString(7)
});
}
}
}
}
catch (DbException)
{
ForeignKeyInformationAvailable = false;
}
}
/// <summary>The process foreign key references for table.</summary>
/// <param name="dbConn">The db conn.</param>
/// <param name="dbTable">The db table.</param>
protected override void ProcessForeignKeyReferencesForTable(DbConnection dbConn, DbModelTable dbTable)
{
// todo - check GetGroupForeingKeys
foreach (DbModelConstraint constraint in dbTable.Constraints)
{
var column = dbTable.Columns.Find(c => c.Name == constraint.ColumnName);
var refTable = dbTable.ParentDb.FindTable(
Utility.RenderSafeSchemaObjectName(constraint.UniqueConstraintTableSchema, constraint.UniqueConstraintTableName));
var refColumn = refTable.Columns.Find(c => c.Name == constraint.UniqueColumnName);
DbModelForeignKeyReference fk = new DbModelForeignKeyReference(column, refTable, refColumn);
fk.UpdateRule = constraint.UpdateRule;
fk.DeleteRule = constraint.DeleteRule;
column.ForeignKeyReference = fk;
}
}
/// <summary>The assign system types.</summary>
/// <param name="dbType">The db type.</param>
private void AssignSystemTypes(DbModelType dbType)
{
switch (dbType.Name.ToLower())
{
case "smallint":
dbType.SystemType = typeof(byte);
break;
case "int":
dbType.SystemType = typeof(int);
break;
case "tinyint":
dbType.SystemType = typeof(byte);
break;
case "bigint":
dbType.SystemType = typeof(long);
break;
case "float":
dbType.SystemType = typeof(double); // yes, float is double ;-)
break;
case "numeric":
case "money":
case "real":
dbType.SystemType = typeof(decimal);
break;
case "bit":
dbType.SystemType = typeof(bool);
break;
case "uniqueidentifier":
dbType.SystemType = typeof(Guid);
break;
case "nvarchar":
case "nchar":
case "ntext":
dbType.SystemType = typeof(string);
break;
case "datetime":
dbType.SystemType = typeof(DateTime);
break;
case "varbinary":
case "binary":
case "image":
case "rowversion":
dbType.SystemType = typeof(byte[]);
break;
}
}
/// <summary>The fix create format.</summary>
/// <param name="dbType">The db type.</param>
private void FixCreateFormat(DbModelType dbType)
{
switch (dbType.Name.ToLower())
{
case "nchar":
case "nvarchar":
case "binary":
case "varbinary":
dbType.CreateFormat = dbType.Name.ToLower() + "({0})";
break;
case "decimal":
case "numeric":
dbType.CreateFormat = dbType.Name.ToLower() + "({0}, {1})";
break;
}
}
/// <summary>The fix max lengths.</summary>
/// <param name="dbType">The db type.</param>
private void FixMaxLengths(DbModelType dbType)
{
switch (dbType.Name.ToLower())
{
case "nchar":
case "nvarchar":
dbType.Length = MAX_NCHAR_COLUMN_SIZE;
break;
case "ntext":
dbType.Length = MAX_NTEXT_COLUMN_SIZE;
break;
case "binary":
case "varbinary":
dbType.Length = MAX_BINARY_COLUMN_SIZE;
break;
case "image":
dbType.Length = MAX_IMAGE_COLUMN_SIZE;
break;
}
}
/// <summary>The query table names.</summary>
/// <param name="dbConn">The db conn.</param>
/// <param name="model">The model.</param>
private void QueryTableNames(DbConnection dbConn, DbModelInstance model)
{
using (var cmd = dbConn.CreateCommand())
{
cmd.CommandText = "SELECT table_name FROM information_schema.tables WHERE TABLE_TYPE = N'TABLE'";
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DbModelTable table = new DbModelTable();
table.Name = (string)reader["table_name"];
model.Add(table);
}
}
}
}
}
}
|