public ProcedureSchema (ProcedureSchema proc)
: base (proc)
{
this.parameters = new ParameterSchemaCollection (parameters);
this.language = proc.language;
this.isSystemProcedure = proc.isSystemProcedure;
}
public ProcedureEditorDialog (ISchemaProvider schemaProvider, ProcedureSchema procedure, bool create)
{
if (schemaProvider == null)
throw new ArgumentNullException ("schemaProvider");
if (procedure == null)
throw new ArgumentNullException ("procedure");
this.schemaProvider = schemaProvider;
this.procedure = procedure;
this.action = create ? SchemaActions.Create : SchemaActions.Alter;
this.Build();
if (create)
Title = GettextCatalog.GetString ("Create Procedure");
else
Title = GettextCatalog.GetString ("Alter Procedure");
notebook = new Notebook ();
sqlEditor = new SqlEditorWidget ();
sqlEditor.TextChanged += new EventHandler (SqlChanged);
notebook.AppendPage (sqlEditor, new Label (GettextCatalog.GetString ("Definition")));
IDbFactory fac = schemaProvider.ConnectionPool.DbFactory;
if (fac.IsCapabilitySupported ("Procedure", action, ProcedureCapabilities.Comment)) {
commentEditor = new CommentEditorWidget ();
notebook.AppendPage (commentEditor, new Label (GettextCatalog.GetString ("Comment")));
}
entryName.Text = procedure.Name;
if (!create) {
sqlEditor.Text = schemaProvider.GetProcedureAlterStatement (procedure);
commentEditor.Comment = procedure.Comment;
}
vboxContent.PackStart (notebook, true, true, 0);
vboxContent.ShowAll ();
SetWarning (null);
}
public ProcedureSchemaContainer (ProcedureSchema schema)
{
if (schema == null)
throw new ArgumentNullException ("schema");
this.schema = schema;
}
public ParametersNode (DatabaseConnectionContext context, ProcedureSchema procedure)
: base (context)
{
if (procedure == null)
throw new ArgumentNullException ("procedure");
this.procedure = procedure;
}
public override ParameterSchemaCollection GetProcedureParameters (ProcedureSchema procedure)
{
ParameterSchemaCollection parameters = new ParameterSchemaCollection ();
// FIXME: Won't work properly with overload functions.
// Maybe check the number of columns in the parameters for
// proper match.
IPooledDbConnection conn = connectionPool.Request ();
IDbCommand command = conn.CreateCommand (String.Format (
"SELECT format_type (prorettype, NULL) "
+ "FROM pg_proc pc, pg_language pl "
+ "WHERE pc.prolang = pl.oid "
+ "AND pc.proname = '{0}';", procedure.Name
));
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read ()) {
ParameterSchema param = new ParameterSchema (this);
param.DataTypeName = r.GetString (0);
param.Name = r.GetString (0);
parameters.Add (param);
}
r.Close ();
}
}
} catch (Exception e) {
QueryService.RaiseException (e);
}
conn.Release ();
return parameters;
public virtual string GetProcedureAlterStatement (ProcedureSchema procedure)
{
throw new NotImplementedException ();
}
public override ICollection <ParameterSchema> GetProcedureParameters(ProcedureSchema procedure)
{
CheckConnectionState();
//TODO:
throw new NotImplementedException();
}
protected virtual ParameterSchema GetProcedureParameter (DataRow row, ProcedureSchema procedure)
{
ParameterSchema schema = new ParameterSchema (this);
schema.SchemaName = procedure.SchemaName;
schema.Name = GetRowString (row, procedureParameterItemStrings[0]);
schema.DataTypeName = GetRowString (row, procedureParameterItemStrings[1]);
schema.Position = GetRowInt (row, procedureParameterItemStrings[2]);
string paramType = GetRowString (row, procedureParameterItemStrings[3]);
schema.ParameterType = String.Compare (paramType, "IN", true) == 0 ?
ParameterType.In : (String.Compare (paramType, "OUT", true) == 0 ?
ParameterType.Out : ParameterType.InOut);
return schema;
}
public virtual void RenameProcedure (ProcedureSchema procedure, string name)
{
throw new NotImplementedException ();
}
//http://dev.mysql.com/doc/refman/5.1/en/drop-procedure.html
public override void DropProcedure(ProcedureSchema procedure)
{
ExecuteNonQuery("DROP PROCEDURE IF EXISTS " + procedure.Name + ";");
}
public override string GetProcedureAlterStatement(ProcedureSchema procedure)
{
return(String.Concat("DROP PROCEDURE IF EXISTS ", procedure.Name, "; ", Environment.NewLine, procedure.Definition));
}
public override ParameterSchemaCollection GetProcedureParameters(ProcedureSchema procedure)
{
ParameterSchemaCollection parameters = new ParameterSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(
"SELECT param_list FROM mysql.proc where name = '" + procedure.Name + "'"
);
try {
using (command) {
if (GetMainVersion(command) >= 5)
{
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
if (r.IsDBNull(0))
{
continue;
}
string[] field = Encoding.ASCII.GetString((byte[])r.GetValue(0)).Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string chunk in field)
{
ParameterSchema param = new ParameterSchema(this);
param.Definition = chunk;
string[] tmp = chunk.TrimStart(new char[] { ' ' }).Split(new char[] { ' ' });
int nameIndex = 0;
if (String.Compare(tmp[0], "OUT", true) == 0)
{
nameIndex = 1;
param.ParameterType = ParameterType.Out;
}
else if (String.Compare(tmp[0], "INOUT", true) == 0)
{
nameIndex = 1;
param.ParameterType = ParameterType.InOut;
}
else
{
param.ParameterType = ParameterType.In;
}
param.Name = tmp[nameIndex];
param.OwnerName = procedure.Name;
param.DataTypeName = tmp[nameIndex + 1];
parameters.Add(param);
}
}
r.Close();
}
} //else: do nothing, since procedures are only supported since mysql 5.x
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(parameters);
}
//http://dev.mysql.com/doc/refman/5.1/en/alter-procedure.html
public override void AlterProcedure(ProcedureSchema procedure)
{
ExecuteNonQuery(procedure.Definition);
}
public virtual void CreateProcedure(ProcedureSchema procedure)
{
throw new NotImplementedException();
}
public virtual string GetProcedureAlterStatement(ProcedureSchema procedure)
{
throw new NotImplementedException();
}
public virtual void RenameProcedure(ProcedureSchema procedure, string name)
{
throw new NotImplementedException();
}
protected virtual ProcedureSchema GetProcedure (DataRow row)
{
ProcedureSchema schema = new ProcedureSchema (this);
schema.SchemaName = GetRowString (row, procedureItemStrings[0]);
schema.Name = GetRowString (row, procedureItemStrings[1]);
return schema;
}
public override ICollection<ColumnSchema> GetProcedureColumns (ProcedureSchema procedure)
{
CheckConnectionState ();
List<ColumnSchema> columns = new List<ColumnSchema> ();
//TODO:
return columns;
public virtual ParameterSchemaCollection GetProcedureParameters (ProcedureSchema procedure)
{
ParameterSchemaCollection collection = new ParameterSchemaCollection ();
IPooledDbConnection conn = connectionPool.Request ();
try {
//restrictions: database, schema, name, type, parameter
DataTable dt = conn.GetSchema (procedureParametersCollectionString, null, procedure.SchemaName, procedure.Name);
for (int r = 0; r < dt.Rows.Count; r++) {
DataRow row = dt.Rows[r];
collection.Add (GetProcedureParameter (row, procedure));
}
} catch (Exception e) {
QueryService.RaiseException (e);
}
conn.Release ();
return collection;
}
public override ICollection<ParameterSchema> GetProcedureParameters (ProcedureSchema procedure)
{
CheckConnectionState ();
//TODO:
throw new NotImplementedException ();
public virtual void DropProcedure (ProcedureSchema procedure)
{
throw new NotImplementedException ();
}
public bool ShowProcedureEditorDialog (ISchemaProvider schemaProvider, ProcedureSchema procedure, bool create)
{
return RunDialog (new ProcedureEditorDialog (schemaProvider, procedure, create));
public virtual ProcedureSchema GetNewProcedureSchema (string name)
{
ProcedureSchema schema = new ProcedureSchema (this);
schema.Name = name;
return schema;
}
public override ICollection<ParameterSchema> GetProcedureParameters (ProcedureSchema procedure)
{
CheckConnectionState ();
List<ParameterSchema> parameters = new List<ParameterSchema> ();
IDbCommand command = connectionProvider.CreateCommand (
"SELECT RDB$PARAMETER_NAME, RDB$PARAMETER_NUMBER, RDB$DESCRIPTION, RDB$SYSTEM_FLAG "
+ "FROM RDB$PROCEDURE_PARAMETERS "
+ "WHERE RDB$PROCEDURE_NAME = '" + procedure.Name + "' "
+ "AND RDB$PARAMETER_TYPE = 0 "
+ "ORDER BY 2;"
);
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read ()) {
ParameterSchema parameter = new ParameterSchema (this);
parameter.Name = r.GetString (0);
parameter.OwnerName = procedure.Name;
parameter.Comment = r.GetString (2);
//TODO: data type
parameters.Add (parameter);
}
r.Close ();
}
connectionProvider.Close (command.Connection);
}
return parameters;
public bool ShowProcedureEditorDialog(ISchemaProvider schemaProvider, ProcedureSchema procedure, bool create)
{
return(RunDialog(new ProcedureEditorDialog(schemaProvider, procedure, create)));
}
public override string GetProcedureAlterStatement (ProcedureSchema procedure)
{
//'CREATE ' <-- after this we insert
return procedure.Definition.Insert (6, "OR REPLACE ");
public override ICollection<ProcedureSchema> GetProcedures ()
{
CheckConnectionState ();
List<ProcedureSchema> procedures = new List<ProcedureSchema> ();
IDbCommand command = connectionProvider.CreateCommand (
"SELECT RDB$PROCEDURE_NAME, RDB$SYSTEM_FLAG, RDB$OWNER_NAME, RDB$DESCRIPTION, RDB$PROCEDURE_SOURCE FROM RDB$PROCEDURES;"
);
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read ()) {
ProcedureSchema procedure = new ProcedureSchema (this);
procedure.Name = r.GetString (0);
procedure.IsSystemProcedure = (!r.IsDBNull (1) && r.GetInt32 (1) != 0);
procedure.OwnerName = r.GetString (2);
procedure.Comment = r.GetString (3);
//TODO: procedure.Definition = 4 (ascii blob)
procedures.Add (procedure);
}
r.Close ();
}
connectionProvider.Close (command.Connection);
}
return procedures;
public override string GetProcedureAlterStatement(ProcedureSchema procedure)
{
//'CREATE ' <-- after this we insert
return(procedure.Definition.Insert(6, "OR REPLACE "));
}
public void Initialize (ProcedureSchema procedure)
{
if (procedure == null)
throw new ArgumentNullException ("procedure");
this.procedure = procedure;
entryName.Text = procedure.Name;
if (action == SchemaActions.Alter) {
sqlEditor.Text = schemaProvider.GetProcedureAlterStatement (procedure);
if (commentEditor != null)
commentEditor.Comment = procedure.Comment;
} else
sqlEditor.Text = procedure.Definition;
}
public override ICollection<ProcedureSchema> GetProcedures ()
{
CheckConnectionState ();
List<ProcedureSchema> procedures = new List<ProcedureSchema> ();
IDbCommand command = connectionProvider.CreateCommand (
"SELECT su.name AS owner, so.name as proc_name, so.id as proc_id, "
+ "so.crdate as created_date, so.type as proc_type "
+ "FROM dbo.sysobjects so, dbo.sysusers su "
+ "WHERE type = 'P' AND su.uid = so.uid "
+ "ORDER BY 1, 2"
);
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read ()) {
ProcedureSchema procedure = new ProcedureSchema (this);
procedure.Name = r.GetString (1);
procedure.OwnerName = r.GetString (0);
procedure.IsSystemProcedure = r.GetString (4).Trim ().Equals ("S");
StringBuilder sb = new StringBuilder ();
sb.AppendFormat ("-- Procedure: {0}\n", procedure.Name);
sb.AppendFormat ("-- DROP PROCEDURE {0};\n\n", procedure.Name);
string source = GetSource (procedure.Owner + "." + procedure.Name);
sb.AppendFormat (" {0}\n);", source);
procedure.Definition = sb.ToString ();
procedures.Add (procedure);
}
r.Close ();
}
connectionProvider.Close (command.Connection);
}
return procedures;
public override ICollection<ColumnSchema> GetProcedureColumns (ProcedureSchema procedure)
{
CheckConnectionState ();
List<ColumnSchema> columns = new List<ColumnSchema> ();
IDbCommand command = connectionProvider.CreateCommand (
"sp_sproc_columns"
);
SybaseParameter owner = new SybaseParameter ("@procedure_owner", procedure.OwnerName);
SybaseParameter name = new SybaseParameter ("@procedure_name", procedure.Name);
command.Parameters.Add (owner);
command.Parameters.Add (name);
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read ()) {
ColumnSchema column = new ColumnSchema (this);
column.Name = (string)r["COLUMN_NAME"];
column.DataTypeName = (string)r["TYPE_NAME"];
columns.Add (column);
}
r.Close ();
}
connectionProvider.Close (command.Connection);
}
return columns;
public override ProcedureSchemaCollection GetProcedures ()
{
ProcedureSchemaCollection procedures = new ProcedureSchemaCollection ();
IPooledDbConnection conn = connectionPool.Request ();
IDbCommand command = conn.CreateCommand (
"SELECT pc.proname, pc.oid::integer, pl.lanname, pc.prosrc "
+ "FROM "
+ " pg_proc pc, "
+ " pg_user pu, "
+ " pg_type pt, "
+ " pg_language pl "
+ "WHERE pc.proowner = pu.usesysid "
+ "AND pc.prorettype = pt.oid "
+ "AND pc.prolang = pl.oid "
+ "UNION "
+ "SELECT pc.proname, pt.oid::integer, pl.lanname, pc.prosrc "
+ "FROM "
+ " pg_proc pc, "
+ " pg_user pu, "
+ " pg_type pt, "
+ " pg_language pl "
+ "WHERE pc.proowner = pu.usesysid "
+ "AND pc.prorettype = 0 "
+ "AND pc.prolang = pl.oid;"
);
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read ()) {
ProcedureSchema procedure = new ProcedureSchema (this);
procedure.Name = r.GetString (0);
procedure.Definition = r.GetString (3);
procedure.LanguageName = r.GetString (2);
if (!r.IsDBNull (1) && r.GetInt32 (1) <= LastSystemOID)
procedure.IsSystemProcedure = true;
procedures.Add (procedure);
}
r.Close ();
}
}
} catch (Exception e) {
QueryService.RaiseException (e);
}
conn.Release ();
return procedures;
