/// <summary>返回数据源的架构信息</summary>
/// <param name="collectionName">指定要返回的架构的名称。</param>
/// <param name="restrictionValues">为请求的架构指定一组限制值。</param>
/// <returns></returns>
public DataTable GetSchema(String collectionName, String[] restrictionValues)
{
// 如果不是MetaDataCollections，并且MetaDataCollections中没有该集合，则返回空
if (!collectionName.EqualIgnoreCase(DbMetaDataCollectionNames.MetaDataCollections))
{
if (!MetaDataCollections.Contains(collectionName))
{
return(null);
}
}
return(Database.CreateSession().GetSchema(null, collectionName, restrictionValues));
}
//String _ParamPrefix;
///// <summary>参数前缀</summary>
//public String ParamPrefix
//{
// get
// {
// if (_ParamPrefix == null)
// {
// var dt = GetSchema(DbMetaDataCollectionNames.DataSourceInformation, null);
// if (dt != null && dt.Rows != null && dt.Rows.Count > 0)
// {
// String str = null;
// if (TryGetDataRowValue<String>(dt.Rows[0], DbMetaDataColumnNames.ParameterMarkerPattern, out str) ||
// TryGetDataRowValue<String>(dt.Rows[0], DbMetaDataColumnNames.ParameterMarkerFormat, out str))
// _ParamPrefix = str.StartsWith("\\") ? str.Substring(1, 1) : str.Substring(0, 1);
// }
// if (_ParamPrefix == null) _ParamPrefix = "";
// }
// return _ParamPrefix;
// }
//}
#endregion
#region GetSchema方法
/// <summary>返回数据源的架构信息</summary>
/// <param name="collectionName">指定要返回的架构的名称。</param>
/// <param name="restrictionValues">为请求的架构指定一组限制值。</param>
/// <returns></returns>
public DataTable GetSchema(string collectionName, string[] restrictionValues)
{
// 如果不是MetaDataCollections，并且MetaDataCollections中没有该集合，则返回空
if (!String.Equals(collectionName, DbMetaDataCollectionNames.MetaDataCollections, StringComparison.OrdinalIgnoreCase))
{
if (!MetaDataCollections.Contains(collectionName))
{
return(null);
}
}
return(Database.CreateSession().GetSchema(collectionName, restrictionValues));
}
/// <summary>设置数据定义模式</summary>
/// <param name="schema">数据定义模式</param>
/// <param name="values">其它信息</param>
/// <returns></returns>
public virtual Object SetSchema(DDLSchema schema, params Object[] values)
{
//Object obj = null;
switch (schema)
{
case DDLSchema.CreateTable:
if (MetaDataCollections.Contains(_.Databases))
{
}
break;
case DDLSchema.TableExist:
{
String name;
if (values[0] is IDataTable)
{
name = (values[0] as IDataTable).TableName;
}
else
{
name = values[0].ToString();
}
var dt = GetSchema(_.Tables, new String[] { null, null, name, "TABLE" });
if (dt == null || dt.Rows == null || dt.Rows.Count < 1)
{
return(false);
}
return(true);
}
case DDLSchema.BackupDatabase:
{
Backup((String)values[0], (String)values[1]);
return(true);
}
default:
break;
}
var sql = GetSchemaSQL(schema, values);
if (String.IsNullOrEmpty(sql))
{
return(null);
}
var session = Database.CreateSession();
if (schema == DDLSchema.TableExist || schema == DDLSchema.DatabaseExist)
{
return(session.QueryCount(sql) > 0);
}
// 分隔符是分号加换行，如果不想被拆开执行（比如有事务），可以在分号和换行之间加一个空格
var ss = sql.Split(";" + Environment.NewLine);
if (ss == null || ss.Length < 1)
{
return(session.Execute(sql));
}
foreach (var item in ss)
{
session.Execute(item);
}
return(0);
}
/// <summary>取得所有表构架</summary>
/// <returns></returns>
protected override List <IDataTable> OnGetTables(String[] names)
{
DataTable dt = null;
// 不缺分大小写，并且不是保留字，才转大写
if (names != null)
{
var db = Database as Oracle;
if (db.IgnoreCase)
{
names = names.Select(e => db.IsReservedWord(e) ? e : e.ToUpper()).ToArray();
}
}
// 采用集合过滤，提高效率
String tableName = null;
if (names != null && names.Length == 1)
{
tableName = names.FirstOrDefault();
}
if (tableName.IsNullOrEmpty())
{
tableName = null;
}
var owner = Owner;
//if (owner.IsNullOrEmpty()) owner = UserID;
dt = GetSchema(_.Tables, new String[] { owner, tableName });
if (!dt.Columns.Contains("TABLE_TYPE"))
{
dt.Columns.Add("TABLE_TYPE", typeof(String));
foreach (var dr in dt.Rows?.ToArray())
{
dr["TABLE_TYPE"] = "Table";
}
}
var dtView = GetSchema(_.Views, new String[] { owner, tableName });
if (dtView != null && dtView.Rows.Count != 0)
{
foreach (var dr in dtView.Rows?.ToArray())
{
var drNew = dt.NewRow();
drNew["OWNER"] = dr["OWNER"];
drNew["TABLE_NAME"] = dr["VIEW_NAME"];
drNew["TABLE_TYPE"] = "View";
dt.Rows.Add(drNew);
}
}
var data = new NullableDictionary <String, DataTable>(StringComparer.OrdinalIgnoreCase);
//data["Columns"] = GetSchema(_.Columns, new String[] { owner, tableName, null });
//data["Indexes"] = GetSchema(_.Indexes, new String[] { owner, null, owner, tableName });
//data["IndexColumns"] = GetSchema(_.IndexColumns, new String[] { owner, null, owner, tableName, null });
// 如果表太多，则只要目标表数据
var mulTable = "";
if (dt.Rows.Count > 10 && names != null && names.Length > 0)
{
//var tablenames = dt.Rows.ToArray().Select(e => "'{0}'".F(e["TABLE_NAME"]));
//mulTable = " And TABLE_NAME in ({0})".F(tablenames.Join(","));
mulTable = " And TABLE_NAME in ({0})".F(names.Select(e => "'{0}'".F(e)).Join(","));
}
// 列和索引
data["Columns"] = Get("all_tab_columns", owner, tableName, mulTable);
data["Indexes"] = Get("all_indexes", owner, tableName, mulTable);
data["IndexColumns"] = Get("all_ind_columns", owner, tableName, mulTable, "Table_Owner");
// 主键
if (MetaDataCollections.Contains(_.PrimaryKeys))
{
data["PrimaryKeys"] = GetSchema(_.PrimaryKeys, new String[] { owner, tableName, null });
}
// 序列
data["Sequences"] = Get("all_sequences", owner, null, null, "Sequence_Owner");
// 表注释
data["TableComment"] = Get("all_tab_comments", owner, tableName, mulTable);
// 列注释
data["ColumnComment"] = Get("all_col_comments", owner, tableName, mulTable);
var list = GetTables(dt.Rows.ToArray(), names, data);
return(list);
}
protected override void FixTable(IDataTable table, DataRow dr)
{
base.FixTable(table, dr);
// 主键
if (MetaDataCollections.Contains(_.PrimaryKeys))
{
DataTable dt = null;
if (IsUseOwner)
{
dt = GetSchema(_.PrimaryKeys, new String[] { Owner, table.TableName, null });
}
else
{
dt = GetSchema(_.PrimaryKeys, new String[] { null, table.TableName, null });
}
if (dt != null && dt.Rows.Count > 0)
{
// 找到主键所在索引，这个索引的列才是主键
String name = null;
if (TryGetDataRowValue <String>(dt.Rows[0], _.IndexName, out name) && !String.IsNullOrEmpty(name))
{
IDataIndex di = table.Indexes.FirstOrDefault(i => i.Name == name);
if (di != null)
{
di.PrimaryKey = true;
foreach (IDataColumn dc in table.Columns)
{
dc.PrimaryKey = di.Columns.Contains(dc.ColumnName);
}
}
}
}
}
// 表注释 USER_TAB_COMMENTS
//String sql = String.Format("Select COMMENTS From USER_TAB_COMMENTS Where TABLE_NAME='{0}'", table.Name);
//String comment = (String)Database.CreateSession().ExecuteScalar(sql);
String comment = GetTableComment(table.TableName);
if (!String.IsNullOrEmpty(comment))
{
table.Description = comment;
}
if (table == null || table.Columns == null || table.Columns.Count < 1)
{
return;
}
// 自增
Boolean exists = false;
foreach (IDataColumn field in table.Columns)
{
// 不管是否主键
if (!field.DataType.IsIntType())
{
continue;
}
String name = String.Format("SEQ_{0}_{1}", table.TableName, field.ColumnName);
if (CheckSeqExists(name))
{
field.Identity = true;
exists = true;
break;
}
}
if (!exists)
{
// 检查该表是否有序列，如有，让主键成为自增
String name = String.Format("SEQ_{0}", table.TableName);
if (CheckSeqExists(name))
{
foreach (IDataColumn field in table.Columns)
{
if (!field.PrimaryKey || !field.DataType.IsIntType())
{
continue;
}
field.Identity = true;
exists = true;
break;
}
}
}
//if (!exists)
//{
// // 处理自增，整型、主键、名为ID认为是自增
// foreach (IDataColumn field in table.Columns)
// {
// if (field.DataType.IsIntType())
// {
// if (field.PrimaryKey && field.ColumnName.ToLower().Contains("id")) field.Identity = true;
// }
// }
//}
}
