void IDBTraits.ReplaceStoredProcedureBody(Func <DbCommand> commandCreator, StoredProcedureReference procedure, StoredProcedureBody newBody)
{
string[] package = GetPackage(commandCreator, procedure.packageName, "PACKAGE BODY");
var position = FindProcedureBodyPosition(package, procedure);
StringBuilder packageBuilder = new StringBuilder();
packageBuilder.AppendLine("create or replace");
for (int i = 0; i < position.header; i++)
{
packageBuilder.Append(package[i]);
}
packageBuilder.Append(FormatStoredProcedure(procedure, newBody));
for (int i = position.end + 1; i < package.Length; i++)
{
packageBuilder.AppendFormat(package[i]);
}
/*Console.WriteLine();
* Console.WriteLine("===NEW PACKAGE===");
* Console.WriteLine(packageBuilder.ToString());
* Console.WriteLine("===END PACKAGE===");*/
using (DbCommand command = commandCreator())
{
command.CommandText = packageBuilder.ToString();
command.ExecuteNonQuery();
}
}
void IDBTraits.RemoveStoredProcedure(Func <DbCommand> commandCreator, StoredProcedureReference procedure)
{
{
string[] packageSpec = GetPackage(commandCreator, procedure.packageName, "PACKAGE");
int position = FindProcedureHeader(packageSpec, procedure);
StringBuilder packageSpecBuilder = new StringBuilder();
packageSpecBuilder.AppendLine("create or replace");
for (int i = 0; i < position; i++)
{
packageSpecBuilder.Append(packageSpec[i]);
}
for (int i = position + 1; i < packageSpec.Length; i++)
{
packageSpecBuilder.Append(packageSpec[i]);
}
using (DbCommand command = commandCreator())
{
command.CommandText = packageSpecBuilder.ToString();
/*Console.WriteLine();
* Console.WriteLine("=====PACKAGE SPEC=====");
* Console.WriteLine(command.CommandText);
* Console.WriteLine("=====END PACKAGE SPEC=====");*/
command.ExecuteNonQuery();
}
}
{
string[] package = GetPackage(commandCreator, procedure.packageName, "PACKAGE BODY");
var position = FindProcedureBodyPosition(package, procedure);
StringBuilder packageBuilder = new StringBuilder();
packageBuilder.AppendLine("create or replace");
for (int i = 0; i < position.header; i++)
{
packageBuilder.Append(package[i]);
}
for (int i = position.end + 1; i < package.Length; i++)
{
packageBuilder.Append(package[i]);
}
using (DbCommand command = commandCreator())
{
command.CommandText = packageBuilder.ToString();
/*Console.WriteLine();
* Console.WriteLine("=====PACKAGE BODY=====");
* Console.WriteLine(command.CommandText);
* Console.WriteLine("=====END PACKAGE BODY=====");*/
command.ExecuteNonQuery();
}
}
}
private static string FormatStoredProcedure(StoredProcedureReference procedure, StoredProcedureBody body)
{
StringBuilder result = new StringBuilder();
result.Append(FormatStoredProcedureHeader(procedure));
result.AppendLine();
result.AppendLine("is");
if (body.declarations.Trim() != "")
{
result.AppendLine(body.declarations.TrimEnd().Trim('\r', '\n'));
}
result.AppendLine("begin");
if (body.body.Trim() != "")
{
result.AppendLine(body.body.TrimEnd().Trim('\r', '\n'));
}
result.AppendFormat("end {0};", procedure.procedureName);
result.AppendLine();
return(result.ToString());
}
private static int FindProcedureHeader(string[] package, StoredProcedureReference procedure)
{
for (int i = 0; i < package.Length; i++)
{
var line = package[i].Trim().TrimEnd(';');
if (PROCEDURE_BODY_HEADER_REGEX.IsMatch(line))
{
var match = PROCEDURE_BODY_HEADER_REGEX.Match(line);
string name = match.Groups["name"].Value;
string[] parameters = match.Groups["params"].Value.Split(',');
if (name == procedure.procedureName)
{
if (parameters.Length != procedure.parameters.Length)
{
throw new ApplicationException(String.Format("Parameters number mismatch for procedure {0}: expected {1}, got {2}", procedure.procedureName, procedure.parameters.Length, parameters.Length));
}
for (int j = 0; j < parameters.Length; j++)
{
string[] parts = parameters[j].Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
if (parts[0] != procedure.parameters[j].name)
{
throw new ApplicationException(String.Format("Parameter {1} name mismatch for procedure {0}: expected {2}, got {3}", j, procedure.procedureName, procedure.parameters[j].name, parts[0]));
}
if (parts[1].ToLower() != DIRECTIONS[procedure.parameters[j].direction])
{
throw new ApplicationException(String.Format("Parameter {1} direction mismatch for procedure {0}: expected {2}, got {3}", procedure.procedureName, procedure.parameters[j].name, DIRECTIONS[procedure.parameters[j].direction], parts[1]));
}
if (parts[2] != procedure.parameters[j].type)
{
throw new ApplicationException(String.Format("Parameter {1} type mismatch for procedure {0}: expected {2}, got {3}", j, procedure.procedureName, procedure.parameters[j].type, parts[2]));
}
}
return(i);
}
}
}
throw new ProcedureNotFoundException(String.Format("Procedure {0} header not found", procedure.procedureName));
}
void IDBTraits.CreateStoredProcedure(Func <DbCommand> commandCreator, StoredProcedureReference procedure, StoredProcedureBody body)
{
throw new NotImplementedException();
}
private static string FormatStoredProcedureHeader(StoredProcedureReference procedure)
{
throw new NotImplementedException();
}
StoredProcedureBody IDBTraits.GetStoredProcedureBody(Func <DbCommand> commandCreator, StoredProcedureReference procedure)
{
throw new NotImplementedException();
}
void IDBTraits.CreateStoredProcedure(Func <DbCommand> commandCreator, StoredProcedureReference procedure, StoredProcedureBody body)
{
string packageEnd = string.Format("end {0};", procedure.packageName);
string packageEndMarker = packageEnd.ToLower();
{
string[] package = GetPackage(commandCreator, procedure.packageName, "PACKAGE BODY");
try
{
var position = FindProcedureBodyPosition(package, procedure);
throw new ApplicationException(String.Format("Procedure {0} body is already declared", procedure.procedureName));
} catch (ProcedureNotFoundException)
{
}
StringBuilder packageBuilder = new StringBuilder();
packageBuilder.AppendLine("create or replace");
for (int i = 0; i < package.Length; i++)
{
if (package[i].Trim().ToLower() == packageEndMarker)
{
if (package.Length > i + 1)
{
throw new ApplicationException("Unexpected package end");
}
break;
}
packageBuilder.Append(package[i]);
}
packageBuilder.Append(FormatStoredProcedure(procedure, body));
packageBuilder.AppendLine(packageEnd);
using (DbCommand command = commandCreator())
{
command.CommandText = packageBuilder.ToString();
/*Console.WriteLine();
* Console.WriteLine("=====PACKAGE BODY=====");
* Console.WriteLine(command.CommandText);
* Console.WriteLine("=====END PACKAGE BODY=====");*/
command.ExecuteNonQuery();
}
}
{
string[] packageSpec = GetPackage(commandCreator, procedure.packageName, "PACKAGE");
try
{
var position = FindProcedureHeader(packageSpec, procedure);
throw new ApplicationException(String.Format("Procedure {0} is already declared", procedure.procedureName));
} catch (ProcedureNotFoundException)
{
}
StringBuilder packageSpecBuilder = new StringBuilder();
packageSpecBuilder.AppendLine("create or replace");
for (int i = 0; i < packageSpec.Length; i++)
{
if (packageSpec[i].Trim().ToLower() == packageEndMarker)
{
if (packageSpec.Length > i + 1)
{
throw new ApplicationException("Unexpected package end");
}
break;
}
packageSpecBuilder.Append(packageSpec[i]);
}
packageSpecBuilder.Append(FormatStoredProcedureHeader(procedure));
packageSpecBuilder.AppendLine(";");
packageSpecBuilder.AppendLine(packageEnd);
using (DbCommand command = commandCreator())
{
command.CommandText = packageSpecBuilder.ToString();
/*Console.WriteLine();
* Console.WriteLine("=====PACKAGE SPEC=====");
* Console.WriteLine(command.CommandText);
* Console.WriteLine("=====END PACKAGE SPEC=====");*/
command.ExecuteNonQuery();
}
}
}
private static string FormatStoredProcedureHeader(StoredProcedureReference procedure)
{
return(string.Format("procedure {0}({1})", procedure.procedureName, String.Join(", ", (from param in procedure.parameters select String.Format("{0} {1} {2}", param.name, DIRECTIONS[param.direction], param.type)).ToArray())));
}
StoredProcedureBody IDBTraits.GetStoredProcedureBody(Func <DbCommand> commandCreator, StoredProcedureReference procedure)
{
string[] package = GetPackage(commandCreator, procedure.packageName, "PACKAGE BODY");
var position = FindProcedureBodyPosition(package, procedure);
StringBuilder declarationsBuilder = new StringBuilder();
for (int i = position.header + 2; i < position.begin; i++)
{
declarationsBuilder.Append(package[i]);
}
StringBuilder bodyBuilder = new StringBuilder();
for (int i = position.begin + 1; i < position.end; i++)
{
bodyBuilder.AppendFormat(package[i]);
}
return(new StoredProcedureBody(declarationsBuilder.ToString(), bodyBuilder.ToString()));
}
private static ProcedureBodyPosition FindProcedureBodyPosition(string[] package, StoredProcedureReference procedure)
{
int header = FindProcedureHeader(package, procedure);
int?begin = null;
int?end = null;
if (package.Length <= header + 2)
{
throw new ApplicationException("Unexpected package end");
}
if (package[header + 1].Trim().ToLower() != "is")
{
throw new ApplicationException(String.Format("Incorrect line follows procedure {0} header: '{1}'", procedure.procedureName, package[header + 1].Trim()));
}
for (int i = header + 2; i < package.Length; i++)
{
if (package[i].Trim().ToLower() == "begin")
{
begin = i;
break;
}
}
if (!begin.HasValue)
{
throw new ApplicationException(String.Format("Unable to find body begin for procedure {0}", procedure.procedureName));
}
if (package.Length <= begin + 1)
{
throw new ApplicationException("Unexpected package end");
}
for (int i = begin.Value + 1; i < package.Length; i++)
{
string line = package[i].Trim();
if (PROCEDURE_BODY_FOOTER_REGEX.IsMatch(line))
{
var match = PROCEDURE_BODY_FOOTER_REGEX.Match(line);
var name = match.Groups["name"].Value;
if (name != procedure.procedureName)
{
throw new ApplicationException(String.Format("Wrong procedure footer: expected {0}, got {1}", procedure.procedureName, name));
}
end = i;
break;
}
}
if (!end.HasValue)
{
throw new ApplicationException("Procedure footer not found");
}
return(new ProcedureBodyPosition
{
header = header,
begin = begin.Value,
end = end.Value,
});
}
public void CreateStoredProcedure(StoredProcedureReference procedure, StoredProcedureBody body)
{
this.traits.CreateStoredProcedure(this.CreateTextCommand, procedure, body);
}
public void RemoveStoredProcedure(StoredProcedureReference procedure)
{
this.traits.RemoveStoredProcedure(this.CreateTextCommand, procedure);
}
public void ReplaceStoredProcedureBody(StoredProcedureReference procedure, StoredProcedureBody newBody)
{
this.traits.ReplaceStoredProcedureBody(this.CreateTextCommand, procedure, newBody);
}
//SELECT LINE, TEXT FROM ALL_SOURCE WHERE TYPE = 'PACKAGE BODY' AND NAME = '' AND OWNER = (SELECT USER FROM DUAL) for update
public StoredProcedureBody GetStoredProcedureBody(StoredProcedureReference procedure)
{
return(this.traits.GetStoredProcedureBody(this.CreateTextCommand, procedure));
}