/// <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);
}
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 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);
}
