/// <summary>
/// 获取指定表信息
/// </summary>
/// <param name="table_name"></param>
/// <returns></returns>
public override ITableSchema GetTableSchema(string table_name)
{
string sql = @"SELECT UO.OBJECT_NAME AS TABLE_NAME,UO.OBJECT_TYPE,UC.COMMENTS,UV.TEXT FROM USER_OBJECTS UO
LEFT JOIN USER_TAB_COMMENTS UC ON UC.TABLE_NAME=UO.OBJECT_NAME
LEFT JOIN USER_VIEWS UV ON UV.VIEW_NAME=UO.OBJECT_NAME
WHERE UO.OBJECT_TYPE IN ('VIEW','TABLE') AND UO.OBJECT_NAME=:TABLE_NAME ORDER BY UO.OBJECT_NAME ASC";
var data = Db.Ado.GetDataTable(sql, new SugarParameter("TABLE_NAME", table_name));
string objectType = data.Rows[0]["OBJECT_TYPE"] + string.Empty;
OracleTableSchema oracleTable = new OracleTableSchema();
oracleTable.Name = table_name;
oracleTable.Comment = data.Rows[0]["COMMENTS"] + string.Empty;
oracleTable.ObjectType = objectType;
if (objectType == "VIEW")
{
oracleTable.ViewScript = data.Rows[0]["TEXT"].ToString();
}
SetColumns(oracleTable);
SetForeignKey(oracleTable);
SetUniqueKey(oracleTable);
SetPrimaryKey(oracleTable);
return(oracleTable);
}
private void SetPrimaryKey(OracleTableSchema oracleTable)
{
if (oracleTable == null)
{
return;
}
if (oracleTable.Columns == null || oracleTable.Columns.Count <= 0)
{
SetColumns(oracleTable);
}
string sql = @"SELECT UCC.CONSTRAINT_NAME, UCC.COLUMN_NAME
FROM USER_CONSTRAINTS UC
LEFT JOIN USER_CONS_COLUMNS UCC
ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
WHERE UC.CONSTRAINT_TYPE = 'P'
AND UC.TABLE_NAME =:TABLE_NAME";
var table = Db.Ado.GetDataTable(sql, new SugarParameter("TABLE_NAME", oracleTable.Name));
Common.PrimaryKey key = new Common.PrimaryKey();
key.Columns = new List <IColumn>();
foreach (DataRow row in table.Rows)
{
string column_name = row["COLUMN_NAME"] + string.Empty;
string constraint_name = row["CONSTRAINT_NAME"] + string.Empty;
key.ConstraintName = constraint_name;
key.Columns.Add(oracleTable.Columns.Find(it => it.Name == column_name));
}
oracleTable.PrimaryKey = key;
}
private void SetColumns(OracleTableSchema oracleTable)
{
if (oracleTable == null)
{
return;
}
oracleTable.Columns = new List <IColumn>();
string sql = @"SELECT TC.COLUMN_NAME,
TC.DATA_TYPE,
NVL(DECODE(TC.CHAR_LENGTH,0,TC.DATA_PRECISION,TC.CHAR_LENGTH),TC.DATA_LENGTH) AS DATA_LENGTH,
TC.DATA_PRECISION,
NVL(TC.DATA_SCALE, -1) DATA_SCALE,
TC.NULLABLE,
TC.DATA_DEFAULT,
CC.COMMENTS
FROM USER_TAB_COLUMNS TC
LEFT JOIN USER_COL_COMMENTS CC
ON TC.COLUMN_NAME = CC.COLUMN_NAME
AND TC.TABLE_NAME = CC.TABLE_NAME
WHERE TC.TABLE_NAME=:TABLE_NAME ORDER BY TC.COLUMN_ID ASC";
List <IColumn> columns = new List <IColumn>();
var table = Db.Ado.GetDataTable(sql, new SugarParameter("TABLE_NAME", oracleTable.Name));
foreach (DataRow row in table.Rows)
{
int scale = Convert.ToInt32(row["DATA_SCALE"]);
string data_type = row["DATA_TYPE"] + string.Empty;
OracleColumn column = new OracleColumn();
column.Name = row["COLUMN_NAME"].ToString();
column.Comment = row["COMMENTS"] + string.Empty;
column.CsharpType = OracleUtils.TransformDatabaseType(data_type, scale);
column.DbType = data_type;
column.DefaultValue = (row["DATA_DEFAULT"] + string.Empty).Trim('\r', '\n');
column.IsNullable = (row["NULLABLE"].ToString().ToUpper()) == "Y";
column.Length = Convert.ToInt32(row["DATA_LENGTH"]);
column.Scale = scale;
column.Table = oracleTable;
column.IsNumeric = OracleUtils.IsNumeric(data_type);
oracleTable.Columns.Add(column);
}
}
private void SetForeignKey(OracleTableSchema oracleTable)
{
if (oracleTable == null)
{
return;
}
if (oracleTable.Columns == null || oracleTable.Columns.Count <= 0)
{
SetColumns(oracleTable);
}
string sql = @"SELECT UCC.CONSTRAINT_NAME, UCC.COLUMN_NAME,UC1.TABLE_NAME FOREIGN_TABLE_NAME
FROM USER_CONSTRAINTS UC
LEFT JOIN USER_CONS_COLUMNS UCC
ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
LEFT JOIN USER_CONSTRAINTS UC1 ON UC.R_CONSTRAINT_NAME=UC1.CONSTRAINT_NAME
WHERE UC.CONSTRAINT_TYPE = 'R'
AND UC.TABLE_NAME =:TABLE_NAME";
var table = Db.Ado.GetDataTable(sql, new SugarParameter("TABLE_NAME", oracleTable.Name));
oracleTable.ForiegnKeys = new List <Common.ForeignKey>();
foreach (DataRow row in table.Rows)
{
string column_name = row["COLUMN_NAME"] + string.Empty;
string constraint_name = row["CONSTRAINT_NAME"] + string.Empty;
Common.ForeignKey key = new Common.ForeignKey();
key.Columns = new List <IColumn>();
key.ConstraintName = constraint_name;
if (key.ForeignTable == null && ContainForeignTable)
{
string forignTable = row["FOREIGN_TABLE_NAME"] + string.Empty;
var fac = new OracleDbOperation(this.Db);
fac.ContainForeignTable = false;
key.ForeignTable = fac.GetTableSchema(forignTable);
}
key.Columns.Add(oracleTable.Columns.Find(it => it.Name == column_name));
oracleTable.ForiegnKeys.Add(key);
}
}
/// <summary>
/// 获得数据库所有表
/// </summary>
/// <returns></returns>
public override DatabaseSchema GetDatabaseSchema()
{
DatabaseSchema db = new DatabaseSchema();
db.Tables = new List <ITableSchema>();
string sql = @"SELECT UO.OBJECT_NAME AS TABLE_NAME,UO.OBJECT_TYPE,UC.COMMENTS,UV.TEXT FROM USER_OBJECTS UO
LEFT JOIN USER_TAB_COMMENTS UC ON UC.TABLE_NAME=UO.OBJECT_NAME
LEFT JOIN USER_VIEWS UV ON UV.VIEW_NAME=UO.OBJECT_NAME
WHERE UO.OBJECT_TYPE IN ('VIEW','TABLE') ORDER BY UO.OBJECT_NAME ASC";
var data = Db.Ado.GetDataTable(sql);
foreach (DataRow row in data.Rows)
{
OracleTableSchema table = new OracleTableSchema();
table.Name = row["TABLE_NAME"] + string.Empty;
table.Comment = row["COMMENTS"] + string.Empty;
db.Tables.Add(table);
}
return(db);
}
