/// <summary>
/// Gets all of the schema in one call.
/// </summary>
/// <returns></returns>
public DatabaseSchema ReadAll(List <string> lstLoadType)
{
_fixUp = false;
DataTypes();
if (lstLoadType.Contains("用户"))
{
AllUsers();
}
if (lstLoadType.Contains("表"))
{
AllTables();
}
if (lstLoadType.Contains("视图"))
{
AllViews();
}
if (lstLoadType.Contains("存储过程&函数"))
{
AllStoredProcedures();
}
//oracle extra
DatabaseSchema.Sequences.Clear();
var sequences = _schemaReader.Sequences();
DatabaseSchema.Sequences.AddRange(SchemaProcedureConverter.Sequences(sequences));
_fixUp = true;
UpdateReferences();
return(_db);
}
/// <summary>
/// Gets all stored procedures (no arguments, for Oracle no packages)
/// </summary>
public IList <DatabaseStoredProcedure> StoredProcedureList()
{
DataTable dt = _sr.StoredProcedures();
SchemaProcedureConverter.StoredProcedures(DatabaseSchema, dt);
return(DatabaseSchema.StoredProcedures);
}
/// <summary>
/// Gets all stored procedures (and functions) with their arguments
/// </summary>
/// <remarks>
/// <para>We also get the source (if available)</para>
/// <para>We don't get functions here.</para>
/// <para>In Oracle stored procedures are often in packages. We read the non-packaged stored procedures, then add packaged stored procedures if they have arguments. If they don't have arguments, they are not found.</para>
/// </remarks>
public IList <DatabaseStoredProcedure> AllStoredProcedures()
{
try
{
DataTable functions = _sr.Functions();
DatabaseSchema.Functions.Clear();
DatabaseSchema.Functions.AddRange(SchemaProcedureConverter.Functions(functions));
}
catch (DbException ex)
{
Debug.WriteLine("Cannot read functions - database security may prevent access to DDL\n" + ex.Message);
throw; //or suppress if not applicable
}
DataTable dt = _sr.StoredProcedures();
SchemaProcedureConverter.StoredProcedures(DatabaseSchema, dt);
DatabaseSchema.Packages.Clear();
DatabaseSchema.Packages.AddRange(SchemaProcedureConverter.Packages(_sr.Packages()));
//do all the arguments as one call and sort them out.
//NB: This is often slow on Oracle
DataTable args = _sr.StoredProcedureArguments(null);
if (args.Rows.Count == 0)
{
//MySql v6 won't do all stored procedures. So we have to do them individually.
foreach (var sproc in DatabaseSchema.StoredProcedures)
{
args = _sr.StoredProcedureArguments(sproc.Name);
SchemaProcedureConverter.UpdateArguments(DatabaseSchema, args);
}
foreach (var function in DatabaseSchema.Functions)
{
args = _sr.StoredProcedureArguments(function.Name);
SchemaProcedureConverter.UpdateArguments(DatabaseSchema, args);
}
}
//arguments could be for functions too
SchemaProcedureConverter.UpdateArguments(DatabaseSchema, args);
foreach (var function in DatabaseSchema.Functions)
{
//return types are assigned as arguments (in most platforms). Move them to return type.
function.CheckArgumentsForReturnType();
}
//procedure, function and view source sql
DataTable srcs = _sr.ProcedureSource(null);
SchemaSourceConverter.AddSources(DatabaseSchema, srcs);
UpdateReferences();
return(DatabaseSchema.StoredProcedures);
}
private void AllSequences()
{
RaiseReadingProgress(SchemaObjectType.Sequences);
var sequences = _schemaReader.Sequences();
ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.Sequences);
DatabaseSchema.Sequences.Clear();
DatabaseSchema.Sequences.AddRange(SchemaProcedureConverter.Sequences(sequences));
}
public override IList <DatabasePackage> Packages(string name)
{
var packages = SchemaProcedureConverter.Packages(_schemaReader.Packages());
if (Parameters.SqlType == SqlType.Oracle && !string.IsNullOrEmpty(Owner))
{
packages.RemoveAll(x => x.SchemaOwner != Owner);
}
return(packages);
}
public override IList <DatabaseStoredProcedure> StoredProcedures(string name)
{
var dt = _schemaReader.StoredProcedures();
SchemaProcedureConverter.StoredProcedures(Parameters.DatabaseSchema, dt);
if (Parameters.SqlType == SqlType.Oracle && !string.IsNullOrEmpty(Owner))
{
Parameters.DatabaseSchema.StoredProcedures.RemoveAll(x => x.SchemaOwner != Owner);
}
return(Parameters.DatabaseSchema.StoredProcedures);
}
public override IList <DatabaseArgument> ProcedureArguments(string name)
{
var dt = _schemaReader.StoredProcedureArguments(name);
var converter = new SchemaProcedureConverter
{
PackageFilter = Parameters.Exclusions.PackageFilter,
StoredProcedureFilter = Parameters.Exclusions.StoredProcedureFilter
};
return(converter.Arguments(dt));
}
/// <summary>
/// Gets all of the schema in one call.
/// </summary>
/// <returns></returns>
public DatabaseSchema ReadAll()
{
_fixUp = false;
DataTypes();
AllUsers();
AllTables();
AllViews();
AllStoredProcedures();
//oracle extra
DatabaseSchema.Sequences.Clear();
var sequences = _sr.Sequences();
DatabaseSchema.Sequences.AddRange(SchemaProcedureConverter.Sequences(sequences));
_fixUp = true;
UpdateReferences();
return(_db);
}
public override IList <DatabaseSequence> Sequences(string name)
{
var sequences = _schemaReader.Sequences();
return(SchemaProcedureConverter.Sequences(sequences));
}
public override IList <DatabaseFunction> Functions(string name)
{
var dt = _schemaReader.Functions();
return(SchemaProcedureConverter.Functions(dt));
}
public void Load(CancellationToken ct)
{
if (ct.IsCancellationRequested) return;
try
{
ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.ReadingSchema, SchemaObjectType.Functions);
var functions = _schemaReader.Functions();
ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.Functions);
_databaseSchema.Functions.Clear();
_databaseSchema.Functions.AddRange(SchemaProcedureConverter.Functions(functions));
}
catch (DbException ex)
{
Debug.WriteLine("Cannot read functions - database security may prevent access to DDL\n" + ex.Message);
throw; //or suppress if not applicable
}
if (ct.IsCancellationRequested) return;
ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.ReadingSchema, SchemaObjectType.StoredProcedure);
var dt = _schemaReader.StoredProcedures();
if (ct.IsCancellationRequested) return;
ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.StoredProcedure);
SchemaProcedureConverter.StoredProcedures(_databaseSchema, dt);
var procFilter = _exclusions.StoredProcedureFilter;
if (procFilter != null)
{
_databaseSchema.StoredProcedures.RemoveAll(p => procFilter.Exclude(p.Name));
}
_databaseSchema.Packages.Clear();
_databaseSchema.Packages.AddRange(SchemaProcedureConverter.Packages(_schemaReader.Packages()));
var packFilter = _exclusions.PackageFilter;
if (packFilter != null)
{
_databaseSchema.Packages.RemoveAll(p => packFilter.Exclude(p.Name));
}
if (ct.IsCancellationRequested) return;
//do all the arguments as one call and sort them out.
//NB: This is often slow on Oracle
ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.ReadingSchema, SchemaObjectType.ProcedureArguments);
var args = _schemaReader.StoredProcedureArguments(null);
ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.ProcedureArguments);
var converter = new SchemaProcedureConverter();
converter.PackageFilter = _exclusions.PackageFilter;
converter.StoredProcedureFilter = _exclusions.StoredProcedureFilter;
if (args.Rows.Count == 0)
{
//MySql v6 won't do all stored procedures. So we have to do them individually.
foreach (var sproc in _databaseSchema.StoredProcedures)
{
if (ct.IsCancellationRequested) return;
args = _schemaReader.StoredProcedureArguments(sproc.Name);
converter.UpdateArguments(_databaseSchema, args);
}
foreach (var function in _databaseSchema.Functions)
{
if (ct.IsCancellationRequested) return;
args = _schemaReader.StoredProcedureArguments(function.Name);
converter.UpdateArguments(_databaseSchema, args);
}
}
if (ct.IsCancellationRequested) return;
//arguments could be for functions too
converter.UpdateArguments(_databaseSchema, args);
foreach (var function in _databaseSchema.Functions)
{
//return types are assigned as arguments (in most platforms). Move them to return type.
function.CheckArgumentsForReturnType();
}
if (ct.IsCancellationRequested) return;
//procedure, function and view source sql
ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.ReadingSchema, SchemaObjectType.ProcedureSource);
var srcs = _schemaReader.ProcedureSource(null);
if (ct.IsCancellationRequested) return;
ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.ProcedureSource);
SchemaSourceConverter.AddSources(_databaseSchema, srcs);
}
public void UpdateArgumentsOracleTest()
{
//arrange
var converter = new SchemaProcedureConverter();
var schema = new DatabaseSchema(null, null);
#region deserialize datatable
//from Oracle HR. Captured from var datatable.WriteXml(StringWriter)
const string data = @"<DocumentElement>
<ProcedureParameters>
<Schema>HR</Schema>
<Procedure>GET_CURRENT_TIME</Procedure>
<Name>P_DATE</Name>
<Position>1</Position>
<Sequence>1</Sequence>
<DataType>DATE</DataType>
<Direction>IN/OUT</Direction>
</ProcedureParameters>
<ProcedureParameters>
<Schema>HR</Schema>
<Procedure>ADD_JOB_HISTORY</Procedure>
<Name>P_EMP_ID</Name>
<Position>1</Position>
<Sequence>1</Sequence>
<DataType>NUMBER</DataType>
<Direction>IN</Direction>
<Length>22</Length>
<Precision>6</Precision>
</ProcedureParameters>
<ProcedureParameters>
<Schema>HR</Schema>
<Procedure>ADD_JOB_HISTORY</Procedure>
<Name>P_START_DATE</Name>
<Position>2</Position>
<Sequence>2</Sequence>
<DataType>DATE</DataType>
<Direction>IN</Direction>
</ProcedureParameters>
<ProcedureParameters>
<Schema>HR</Schema>
<Procedure>ADD_JOB_HISTORY</Procedure>
<Name>P_END_DATE</Name>
<Position>3</Position>
<Sequence>3</Sequence>
<DataType>DATE</DataType>
<Direction>IN</Direction>
</ProcedureParameters>
<ProcedureParameters>
<Schema>HR</Schema>
<Procedure>ADD_JOB_HISTORY</Procedure>
<Name>P_JOB_ID</Name>
<Position>4</Position>
<Sequence>4</Sequence>
<DataType>VARCHAR2</DataType>
<Direction>IN</Direction>
<Length>10</Length>
<Charset>CHAR_CS</Charset>
</ProcedureParameters>
<ProcedureParameters>
<Schema>HR</Schema>
<Procedure>ADD_JOB_HISTORY</Procedure>
<Name>P_DEPARTMENT_ID</Name>
<Position>5</Position>
<Sequence>5</Sequence>
<DataType>NUMBER</DataType>
<Direction>IN</Direction>
<Length>22</Length>
<Precision>4</Precision>
</ProcedureParameters>
</DocumentElement>
";
var sr = new StringReader(data);
var ds = new DataSet();
ds.ReadXml(sr);
var dataTable = ds.Tables[0];
#endregion
//act
converter.UpdateArguments(schema, dataTable);
//assert
var sprocs = schema.StoredProcedures;
Assert.AreEqual(2, sprocs.Count);
var addHistory = sprocs.Find(x => x.Name == "ADD_JOB_HISTORY");
Assert.AreEqual(5, addHistory.Arguments.Count);
var empId = addHistory.Arguments.Find(x => x.Name == "P_EMP_ID");
Assert.AreEqual(1, empId.Ordinal);
Assert.AreEqual("NUMBER", empId.DatabaseDataType);
Assert.AreEqual(true, empId.In);
Assert.AreEqual(false, empId.Out);
var currentTime = sprocs.Find(x => x.Name == "GET_CURRENT_TIME");
var date = currentTime.Arguments.Find(x => x.Name == "P_DATE");
Assert.AreEqual(1, date.Ordinal);
Assert.AreEqual("DATE", date.DatabaseDataType);
//inout type!
Assert.AreEqual(true, date.In);
Assert.AreEqual(true, date.Out);
}
public void UpdateArgumentsSqlServerTest()
{
//arrange
var converter = new SchemaProcedureConverter();
var schema = new DatabaseSchema(null, null);
#region deserialize datatable
//from SqlServer Northwind. Captured from var datatable.WriteXml(StringWriter)
const string data = @"<DocumentElement>
<ProcedureParameters>
<SPECIFIC_CATALOG>Northwind</SPECIFIC_CATALOG>
<SPECIFIC_SCHEMA>dbo</SPECIFIC_SCHEMA>
<SPECIFIC_NAME>GetWeekDay</SPECIFIC_NAME>
<ORDINAL_POSITION>0</ORDINAL_POSITION>
<PARAMETER_MODE>OUT</PARAMETER_MODE>
<IS_RESULT>YES</IS_RESULT>
<AS_LOCATOR>NO</AS_LOCATOR>
<PARAMETER_NAME />
<DATA_TYPE>int</DATA_TYPE>
<NUMERIC_PRECISION>10</NUMERIC_PRECISION>
<NUMERIC_PRECISION_RADIX>10</NUMERIC_PRECISION_RADIX>
<NUMERIC_SCALE>0</NUMERIC_SCALE>
</ProcedureParameters>
<ProcedureParameters>
<SPECIFIC_CATALOG>Northwind</SPECIFIC_CATALOG>
<SPECIFIC_SCHEMA>dbo</SPECIFIC_SCHEMA>
<SPECIFIC_NAME>GetWeekDay</SPECIFIC_NAME>
<ORDINAL_POSITION>1</ORDINAL_POSITION>
<PARAMETER_MODE>IN</PARAMETER_MODE>
<IS_RESULT>NO</IS_RESULT>
<AS_LOCATOR>NO</AS_LOCATOR>
<PARAMETER_NAME>@Date</PARAMETER_NAME>
<DATA_TYPE>datetime</DATA_TYPE>
<DATETIME_PRECISION>3</DATETIME_PRECISION>
</ProcedureParameters>
<ProcedureParameters>
<SPECIFIC_CATALOG>Northwind</SPECIFIC_CATALOG>
<SPECIFIC_SCHEMA>dbo</SPECIFIC_SCHEMA>
<SPECIFIC_NAME>GetCurrentDate</SPECIFIC_NAME>
<ORDINAL_POSITION>1</ORDINAL_POSITION>
<PARAMETER_MODE>INOUT</PARAMETER_MODE>
<IS_RESULT>NO</IS_RESULT>
<AS_LOCATOR>NO</AS_LOCATOR>
<PARAMETER_NAME>@p1</PARAMETER_NAME>
<DATA_TYPE>datetime2</DATA_TYPE>
<DATETIME_PRECISION>7</DATETIME_PRECISION>
</ProcedureParameters>
<ProcedureParameters>
<SPECIFIC_CATALOG>Northwind</SPECIFIC_CATALOG>
<SPECIFIC_SCHEMA>dbo</SPECIFIC_SCHEMA>
<SPECIFIC_NAME>Sales by Year</SPECIFIC_NAME>
<ORDINAL_POSITION>1</ORDINAL_POSITION>
<PARAMETER_MODE>IN</PARAMETER_MODE>
<IS_RESULT>NO</IS_RESULT>
<AS_LOCATOR>NO</AS_LOCATOR>
<PARAMETER_NAME>@Beginning_Date</PARAMETER_NAME>
<DATA_TYPE>datetime</DATA_TYPE>
<DATETIME_PRECISION>3</DATETIME_PRECISION>
</ProcedureParameters>
<ProcedureParameters>
<SPECIFIC_CATALOG>Northwind</SPECIFIC_CATALOG>
<SPECIFIC_SCHEMA>dbo</SPECIFIC_SCHEMA>
<SPECIFIC_NAME>Sales by Year</SPECIFIC_NAME>
<ORDINAL_POSITION>2</ORDINAL_POSITION>
<PARAMETER_MODE>IN</PARAMETER_MODE>
<IS_RESULT>NO</IS_RESULT>
<AS_LOCATOR>NO</AS_LOCATOR>
<PARAMETER_NAME>@Ending_Date</PARAMETER_NAME>
<DATA_TYPE>datetime</DATA_TYPE>
<DATETIME_PRECISION>3</DATETIME_PRECISION>
</ProcedureParameters>
</DocumentElement>";
var sr = new StringReader(data);
var ds = new DataSet();
ds.ReadXml(sr);
var dataTable = ds.Tables[0];
#endregion
//act
converter.UpdateArguments(schema, dataTable);
//assert
var sprocs = schema.StoredProcedures;
Assert.AreEqual(2, sprocs.Count); //function GetWeekDay is ignored
Assert.AreEqual(1, schema.Functions.Count); //here's where GetWeekDay is
var salesByYear = sprocs.Find(x => x.Name == "Sales by Year");
Assert.AreEqual(2, salesByYear.Arguments.Count);
var date = salesByYear.Arguments.Find(x => x.Name == "@Beginning_Date");
Assert.AreEqual(1, date.Ordinal);
Assert.AreEqual("datetime", date.DatabaseDataType);
Assert.AreEqual(true, date.In);
Assert.AreEqual(false, date.Out);
var currentTime = sprocs.Find(x => x.Name == "GetCurrentDate");
Assert.AreEqual(1, currentTime.Arguments.Count);
var p1 = currentTime.Arguments.Find(x => x.Name == "@p1");
Assert.AreEqual(1, p1.Ordinal);
Assert.AreEqual("datetime2", p1.DatabaseDataType);
//in-out type!
Assert.AreEqual(true, p1.In);
Assert.AreEqual(true, p1.Out);
var fun = schema.Functions[0];
Assert.AreEqual("int", fun.ReturnType);
Assert.AreEqual(1, fun.Arguments.Count); //even though we had 2 in the data table
var input = fun.Arguments[0];
Assert.AreEqual("@Date", input.Name);
Assert.AreEqual("datetime", input.DatabaseDataType);
Assert.AreEqual(true, input.In);
}
public void Load(CancellationToken ct)
{
if (ct.IsCancellationRequested)
{
return;
}
try
{
ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.ReadingSchema, SchemaObjectType.Functions);
var functions = _schemaReader.Functions();
ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.Functions);
_databaseSchema.Functions.Clear();
_databaseSchema.Functions.AddRange(SchemaProcedureConverter.Functions(functions));
}
catch (DbException ex)
{
Debug.WriteLine("Cannot read functions - database security may prevent access to DDL\n" + ex.Message);
throw; //or suppress if not applicable
}
if (ct.IsCancellationRequested)
{
return;
}
ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.ReadingSchema, SchemaObjectType.StoredProcedure);
var dt = _schemaReader.StoredProcedures();
if (ct.IsCancellationRequested)
{
return;
}
ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.StoredProcedure);
SchemaProcedureConverter.StoredProcedures(_databaseSchema, dt);
var procFilter = _exclusions.StoredProcedureFilter;
if (procFilter != null)
{
_databaseSchema.StoredProcedures.RemoveAll(p => procFilter.Exclude(p.Name));
}
_databaseSchema.Packages.Clear();
_databaseSchema.Packages.AddRange(SchemaProcedureConverter.Packages(_schemaReader.Packages()));
var packFilter = _exclusions.PackageFilter;
if (packFilter != null)
{
_databaseSchema.Packages.RemoveAll(p => packFilter.Exclude(p.Name));
}
if (ct.IsCancellationRequested)
{
return;
}
//do all the arguments as one call and sort them out.
//NB: This is often slow on Oracle
ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.ReadingSchema, SchemaObjectType.ProcedureArguments);
var args = _schemaReader.StoredProcedureArguments(null);
ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.ProcedureArguments);
var converter = new SchemaProcedureConverter();
converter.PackageFilter = _exclusions.PackageFilter;
converter.StoredProcedureFilter = _exclusions.StoredProcedureFilter;
if (args.Rows.Count == 0)
{
//MySql v6 won't do all stored procedures. So we have to do them individually.
foreach (var sproc in _databaseSchema.StoredProcedures)
{
if (ct.IsCancellationRequested)
{
return;
}
args = _schemaReader.StoredProcedureArguments(sproc.Name);
converter.UpdateArguments(_databaseSchema, args);
}
foreach (var function in _databaseSchema.Functions)
{
if (ct.IsCancellationRequested)
{
return;
}
args = _schemaReader.StoredProcedureArguments(function.Name);
converter.UpdateArguments(_databaseSchema, args);
}
}
if (ct.IsCancellationRequested)
{
return;
}
//arguments could be for functions too
converter.UpdateArguments(_databaseSchema, args);
foreach (var function in _databaseSchema.Functions)
{
//return types are assigned as arguments (in most platforms). Move them to return type.
function.CheckArgumentsForReturnType();
}
if (ct.IsCancellationRequested)
{
return;
}
//procedure, function and view source sql
ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.ReadingSchema, SchemaObjectType.ProcedureSource);
var srcs = _schemaReader.ProcedureSource(null);
if (ct.IsCancellationRequested)
{
return;
}
ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.ProcedureSource);
SchemaSourceConverter.AddSources(_databaseSchema, srcs);
}