//Note: as of May/13, MySql does not preserve comments preceding SP header; they are going to patch this.
// For now, we put tag in a comment right after BEGIN
public override bool ConvertToStoredProc(DbCommandInfo command)
{
var table = command.Table;
string ProcBodyTemplate =
@"BEGIN
{0}
-- Description: {1}
{2}
{3}
END";
string CreateProcTemplate =
@"CREATE PROCEDURE {0}(
{1})
SQL SECURITY INVOKER
{2}
";
command.Schema = table.Schema;
//Build command that creates stored proc and execute it
var listParams = new StringList();
foreach (var prm in command.Parameters)
{
var strOut = (prm.Direction & ParameterDirection.Output) != 0 ? "OUT " : string.Empty;
var prmSpec = string.Format(" {0}{1} {2}", strOut, prm.Name, prm.TypeInfo.SqlTypeSpec);
listParams.Add(prmSpec);
}
var strParams = string.Join(",\r\n", listParams);
var tag = DbDriver.GeneratedCrudProcTagPrefix + command.DescriptiveTag;
command.SourceHash = SourceHasher.ComputeHash(command.FullCommandName, strParams, command.Sql);
command.StoredProcBody = string.Format(ProcBodyTemplate, tag, command.Description, command.Sql, SourceHasher.GetHashLine(command.SourceHash));
command.StoredProcText = string.Format(CreateProcTemplate, command.FullCommandName, strParams, command.StoredProcBody);
return(true);
}
//Note: be careful not to introduce trailing spaces, esp. when some of the template args are empty;
// latest PG driver cuts them off.
// TODO: need better solution for Postgres trailing spaces
private bool ConvertNonQueryToStoredProc(DbCommandInfo command)
{
var table = command.Table;
//TODO: refactor to return #of records
string ProcBodyTemplate =
@"{0}
BEGIN
{1}{2};
{3}
END;";
const string CreateProcTemplate = @"
CREATE OR REPLACE FUNCTION {0}({1}) {2} AS
$$
{3}
$$
LANGUAGE plpgsql;
";
// In Postgres, if function has output parameter(s), it must return scalar or record. If there's no out parameters, you must specify
// something as return type - we specify VOID. If we have out param(s), we skip 'returns' clause, and Postgres adds it automatically.
// If we have OUT parameter for identity field; we retrieve Identity value for INSERT using ' ... RETURNING "Id" INTO p_id; ' clause.
// Insert SQL has already "... RETURNING "Id" ' clause - we add only ' INTO p_Id ' extra.
command.Schema = table.Schema;
var listParams = new StringList();
bool hasOut = false;
var lstTargets = new List <string>();
foreach (var prm in command.Parameters)
{
string strOut = string.Empty;
if ((prm.Direction & ParameterDirection.Output) != 0)
{
hasOut = true;
strOut = "OUT ";
var col = prm.SourceColumn;
if (col != null && col.Flags.IsSet(DbColumnFlags.Identity))
{
lstTargets.Add(prm.Name);
}
}
listParams.Add(" " + strOut + prm.Name + " " + prm.TypeInfo.SqlTypeSpec);
}
string strReturns = hasOut ? string.Empty : "RETURNS VOID";
var strParams = listParams.Count > 0 ? "\r\n " + string.Join(",\r\n ", listParams) + "\r\n" : string.Empty;
var header = BuildProcHeader(command);
var strRetInto = string.Empty;
if (lstTargets.Count > 0)
{
strRetInto = "\r\n INTO " + string.Join(", ", lstTargets);
}
var sql = command.Sql.TrimEnd(' ', '\r', '\n', ';'); //trim ending semicolon so that we can append RETURNING clause
command.SourceHash = SourceHasher.ComputeHash(command.FullCommandName, strParams, sql);
command.StoredProcBody = string.Format(ProcBodyTemplate, header, sql, strRetInto, SourceHasher.GetHashLine(command.SourceHash));
command.StoredProcText =
string.Format(CreateProcTemplate, command.FullCommandName, strParams, strReturns, command.StoredProcBody);
return(true);
}
public override bool ConvertToStoredProc(DbCommandInfo command)
{
const string ProcBodyTemplate =
@"-- Description: {0}
{1}
{2}
{3}
";
const string CreateProcTemplate = @"CREATE PROCEDURE {0}
{1}
AS
BEGIN
SET NOCOUNT ON;
{2}
END
";
var table = command.Table;
command.Schema = table.Schema;
//Build command that creates stored proc and execute it
var listParams = new StringList();
foreach (var prm in command.Parameters)
{
var strOut = (prm.Direction & ParameterDirection.Output) != 0 ? " OUTPUT" : string.Empty;
// Add READONLY for table-type parameters
var strReadOnly = (prm.TypeInfo.VendorDbType.VendorDbType == (int)SqlDbType.Structured) ? " READONLY" : string.Empty;
var prmSpec = " " + prm.Name + " " + prm.TypeInfo.SqlTypeSpec + strReadOnly + strOut;
listParams.Add(prmSpec);
}
var strParams = string.Join(",\r\n", listParams);
var desc = command.EntityCommand.Description;
var tag = DbDriver.GeneratedCrudProcTagPrefix + command.DescriptiveTag;
command.SourceHash = SourceHasher.ComputeHash(command.FullCommandName, strParams, command.Sql);
command.StoredProcBody = string.Format(ProcBodyTemplate, desc, tag, command.Sql, SourceHasher.GetHashLine(command.SourceHash));
command.StoredProcText = string.Format(CreateProcTemplate, command.FullCommandName, strParams, command.StoredProcBody);
return(true);
}
// See examples of stored procs at the end of this file
private bool ConvertQueryToStoredProc(DbCommandInfo command)
{
var table = command.Table;
string ProcBodyTemplate =
@"{0}
DECLARE
ref1 refcursor;
BEGIN
OPEN ref1 FOR
{1}
RETURN ref1;
{2}
END;
";
string CreateProcTemplate =
@"{0}
CREATE OR REPLACE FUNCTION {1} ({2}) RETURNS refcursor AS $$
{3}
$$ LANGUAGE plpgsql;
";
command.Schema = table.Schema;
//Build command that creates stored proc and execute it
var listParams = new StringList();
foreach (var prm in command.Parameters)
{
var strOut = (prm.Direction & ParameterDirection.Output) != 0 ? " OUT" : string.Empty;
listParams.Add(" " + strOut + prm.Name + " " + prm.TypeInfo.SqlTypeSpec);
}
var strParams = listParams.Count > 0 ? "\r\n " + string.Join(",\r\n ", listParams) + "\r\n" : string.Empty;
var header = BuildProcHeader(command);
command.SourceHash = SourceHasher.ComputeHash(command.FullCommandName, strParams, command.Sql);
command.StoredProcBody = string.Format(ProcBodyTemplate, header, command.Sql, SourceHasher.GetHashLine(command.SourceHash));
command.StoredProcText =
string.Format(CreateProcTemplate, header, command.FullCommandName, strParams, command.StoredProcBody);
return(true);
}