public override ColumnSchemaCollection GetViewColumns(ViewSchema view)
{
ColumnSchemaCollection columns = new ColumnSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
using (IDbCommand command = conn.CreateCommand(string.Format("SELECT * FROM \"{0}\" WHERE 1 = 0",
view.Name)))
try {
using (IDataReader r = command.ExecuteReader()) {
for (int i = 0; i < r.FieldCount; i++)
{
ColumnSchema column = new ColumnSchema(this, view);
column.Name = r.GetName(i);
column.DataTypeName = r.GetDataTypeName(i);
column.DefaultValue = "";
column.Definition = "";
column.OwnerName = view.OwnerName;
column.SchemaName = view.OwnerName;
columns.Add(column);
}
r.Close();
}
} catch (Exception e) {
QueryService.RaiseException(e);
}finally {
conn.Release();
}
}
return(columns);
}
private string GetSource(string objectName)
{
LoggingService.LogDebug("GetSource: " + objectName);
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(
String.Format("EXEC sp_helptext '{0}'", objectName)
);
StringBuilder sb = new StringBuilder();
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
sb.Append(r.GetString(0));
}
r.Close();
}
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(sb.ToString());
}
public override DatabaseSchemaCollection GetDatabases()
{
DatabaseSchemaCollection databases = new DatabaseSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
//we don't have to change it back afterwards, since the connectionpool will do this for us
conn.DbConnection.ChangeDatabase("master");
using (IDbCommand command = conn.CreateCommand("select name from sysdatabases")) {
try {
using (command)
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
DatabaseSchema db = new DatabaseSchema(this);
db.Name = r.GetString(0);
databases.Add(db);
}
r.Close();
}
} catch (Exception e) {
QueryService.RaiseException(e);
} finally {
conn.Release();
}
}
}
return(databases);
}
public override ColumnSchemaCollection GetTableColumns(TableSchema table)
{
ColumnSchemaCollection columns = new ColumnSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
using (IDbCommand command = conn.CreateCommand(string.Format(@"SELECT
su.name as owner,
so.name as table_name,
sc.name as column_name,
st.name as date_type,
sc.length as column_length,
sc.xprec as data_precision,
sc.xscale as data_scale,
sc.isnullable,
sc.colid as column_id
FROM
dbo.syscolumns sc,
dbo.sysobjects so,
dbo.systypes st, dbo.sysusers su
WHERE
sc.id = so.id
AND so.xtype in ('U','S')
AND so.name = '{0}'
AND su.name = '{1}'
AND sc.xusertype = st.xusertype
AND su.uid = so.uid
ORDER BY sc.colid" , table.Name, table.OwnerName)))
try {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ColumnSchema column = new ColumnSchema(this, table);
column.Name = r.GetString(2);
column.DataTypeName = r.GetString(3);
column.DefaultValue = String.Empty;
column.Comment = String.Empty;
column.OwnerName = table.OwnerName;
column.SchemaName = table.SchemaName;
column.IsNullable = r.GetValue(7).ToString() == "0" ? true : false;
column.DataType.LengthRange.Default = r.GetInt16(4);
column.DataType.PrecisionRange.Default = r.IsDBNull(5) ? 0 : (int)r.GetByte(5);
column.DataType.ScaleRange.Default = r.IsDBNull(6) ? 0 : (int)r.GetByte(6);
column.Definition = String.Concat(column.Name, " ", column.DataTypeName, " ",
column.DataType.LengthRange.Default > 0 ? "(" + column.DataType.LengthRange.Default + ")" : "",
column.IsNullable ? " NULL" : " NOT NULL");
//TODO: append " DEFAULT ..." if column.Default.Length > 0
columns.Add(column);
}
r.Close();
}
} catch (Exception e) {
QueryService.RaiseException(e);
}finally {
conn.Release();
}
}
return(columns);
}
public override ConstraintSchemaCollection GetTableConstraints(TableSchema table)
{
ConstraintSchemaCollection constraints = new ConstraintSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
using (IDbCommand command = conn.CreateCommand(string.Format(@"select
sysobjects.name,
sysobjects.xtype
from sysobjects
inner join sysobjects sysobjectsParents ON
sysobjectsParents.id = sysobjects.parent_obj
where
sysobjectsParents.name = '{0}' and
sysobjects.xtype in ('C', 'UQ', 'F','PK','CK')" ,
table.Name)))
try {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ConstraintSchema constraint = null;
switch (r.GetString(1))
{
case "F": //foreign key
constraint = new ForeignKeyConstraintSchema(this);
break;
case "PK": //primary key
constraint = new PrimaryKeyConstraintSchema(this);
break;
case "C":
case "CK": //check constraint
constraint = new CheckConstraintSchema(this);
break;
case "UQ":
constraint = new UniqueConstraintSchema(this);
break;
default:
break;
}
if (constraint != null)
{
constraint.Name = r.GetString(0);
constraints.Add(constraint);
}
}
r.Close();
}
} catch (Exception e) {
QueryService.RaiseException(e);
}finally {
conn.Release();
}
}
return(constraints);
}
private void OnEmptyTableCallback(IPooledDbConnection connection, int result, object state)
{
connection.Release();
DispatchService.GuiDispatch(delegate() {
IdeApp.Workbench.StatusBar.SetMessage(GettextCatalog.GetString("Table emptied"));
});
}
protected virtual int ExecuteNonQuery(string sql)
{
IPooledDbConnection conn = connectionPool.Request();
int result = conn.ExecuteNonQuery(sql);
conn.Release();
return(result);
}
public override ColumnSchemaCollection GetTableColumns(TableSchema table)
{
ColumnSchemaCollection columns = new ColumnSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(
"SELECT a.attname, a.attnotnull, a.attlen, "
+ "typ.typname, adef.adsrc "
+ "FROM "
+ " pg_catalog.pg_attribute a LEFT JOIN "
+ " pg_catalog.pg_attrdef adef "
+ " ON a.attrelid=adef.adrelid "
+ " AND a.attnum=adef.adnum "
+ " LEFT JOIN pg_catalog.pg_type t ON a.atttypid=t.oid, "
+ " pg_catalog.pg_type typ "
+ "WHERE "
+ " a.attrelid = (SELECT oid FROM pg_catalog.pg_class "
+ " WHERE relname='" + table.Name + "') "
+ "AND a.attnum > 0 AND NOT a.attisdropped "
+ "AND a.atttypid = typ.oid "
+ "ORDER BY a.attnum;"
);
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ColumnSchema column = new ColumnSchema(this, table);
column.Name = r.GetString(0);
column.DataTypeName = r.GetString(3);
column.IsNullable = r.GetBoolean(1);
column.DefaultValue = r.IsDBNull(4) ? null : r.GetString(4);
column.DataType.LengthRange.Default = r.GetInt32(2);
// StringBuilder sb = new StringBuilder();
// sb.AppendFormat("{0} {1}{2}",
// column.Name,
// column.DataTypeName,
// (column.DataType.LengthRange.Default > 0) ? ("(" + column.DataType.LengthRange.Default + ")") : "");
// sb.AppendFormat(" {0}", column.IsNullable ? "NULL" : "NOT NULL");
// if (column.DefaultValue.Length > 0)
// sb.AppendFormat(" DEFAULT {0}", column.DefaultValue);
// column.Definition = sb.ToString();
columns.Add(column);
}
r.Close();
};
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(columns);
}
//http://www.sqlite.org/lang_createview.html
public override void CreateView(ViewSchema view)
{
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(view.Definition);
using (command)
conn.ExecuteNonQuery(command);
conn.Release();
}
private void OnSelectCommandThreaded(IPooledDbConnection connection, DataTable table, object state)
{
connection.Release();
DispatchService.GuiDispatch(delegate() {
QueryResultView view = new QueryResultView(table);
IdeApp.Workbench.OpenDocument(view, true);
});
}
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);
}
private void ExecuteQueryThreaded(IPooledDbConnection connection, DataSet result, object state)
{
connection.Release();
TimeSpan duration = DateTime.Now.Subtract(queryStart);
DispatchService.GuiDispatch(delegate() {
notebook.ShowAll();
string msg = String.Concat(
AddinCatalog.GetPluralString("Query executed ({0} result table)",
"Query executed ({0} result tables)", result.Tables.Count),
Environment.NewLine,
AddinCatalog.GetString("Query duration: {0}", duration.ToString())
);
SetQueryState(false, String.Format(msg, result.Tables.Count));
});
if (stoppedQueries.Contains(state))
{
stoppedQueries.Remove(state);
return;
}
if (result != null)
{
foreach (DataTable table in result.Tables)
{
DispatchService.GuiDispatch(delegate() {
MonoDevelop.Database.Components.DataGrid grid = new MonoDevelop.Database.Components.DataGrid();
grid.DataSource = table;
grid.DataBind();
string msg = String.Concat(Environment.NewLine, AddinCatalog.GetString("Table"), ": ", table.TableName,
Environment.NewLine, "\t", AddinCatalog.GetString("Affected Rows"), ": ", table.Rows.Count);
status.Buffer.Text += msg;
TabLabel label = new TabLabel(new Label(table.TableName), ImageService.GetImage("md-db-table", IconSize.Menu));
label.CloseClicked += new EventHandler(OnResultTabClose);
notebook.AppendPage(grid, label);
notebook.ShowAll();
this.Document.ReadOnly = false;
notebook.Page = notebook.NPages - 1;
});
}
}
if (result == null || result.Tables.Count == 0)
{
DispatchService.GuiDispatch(delegate() {
status.Buffer.Text += AddinCatalog.GetString("No Results");
this.Document.ReadOnly = false;
});
}
}
public override TableSchemaCollection GetTables()
{
TableSchemaCollection tables = new TableSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
using (IDbCommand command = conn.CreateCommand(@"SELECT
su.name AS owner,
so.name as table_name,
so.id as table_id,
so.crdate as created_date,
so.xtype as table_type
FROM dbo.sysobjects so,
dbo.sysusers su
WHERE
xtype IN ('S','U')
AND su.uid = so.uid
ORDER BY 1, 2" )) {
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
TableSchema table = new TableSchema(this);
table.Name = r.GetString(1);
if (r.GetString(4) == "S")
{
table.IsSystemTable = true;
}
else
if (Array.Exists(system_tables, delegate(string s) { return(s == table.Name); }))
{
table.IsSystemTable = true;
}
else
{
table.IsSystemTable = false;
}
table.OwnerName = r.GetString(0);
table.Definition = GetTableDefinition(table);
tables.Add(table);
}
r.Close();
}
}
} catch (Exception e) {
QueryService.RaiseException(e);
} finally {
conn.Release();
}
}
}
return(tables);
}
public override UserSchemaCollection GetUsers()
{
UserSchemaCollection users = new UserSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand("SELECT * FROM pg_user;");
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
UserSchema user = new UserSchema(this);
user.Name = r.GetString(0);
user.UserId = String.Format("{0}", r.GetValue(1));
user.Expires = r.IsDBNull(6) ? DateTime.MinValue : r.GetDateTime(6);
//user.Options["createdb"] = r.GetBoolean (2);
//user.Options["createuser"] = r.GetBoolean (3);
user.Password = r.GetString(5);
StringBuilder sb = new StringBuilder();
sb.AppendFormat("-- User: \"{0}\"\n\n", user.Name);
sb.AppendFormat("-- DROP USER {0};\n\n", user.Name);
sb.AppendFormat("CREATE USER {0}", user.Name);
sb.AppendFormat(" WITH SYSID {0}", user.UserId);
if (user.Password != "********")
{
sb.AppendFormat(" ENCRYPTED PASSWORD {0}", user.Password);
}
//sb.AppendFormat (((bool) user.Options["createdb"]) ?
// " CREATEDB" : " NOCREATEDB");
//sb.AppendFormat (((bool) user.Options["createuser"]) ?
// " CREATEUSER" : " NOCREATEUSER");
if (user.Expires != DateTime.MinValue)
{
sb.AppendFormat(" VALID UNTIL {0}", user.Expires);
}
sb.Append(";");
user.Definition = sb.ToString();
users.Add(user);
}
r.Close();
}
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(users);
}
//http://msdn2.microsoft.com/en-us/library/aa258843(SQL.80).aspx
public override void DropDatabase(DatabaseSchema database)
{
using (IPooledDbConnection conn = connectionPool.Request()) {
using (IDbCommand command = conn.CreateCommand(string.Concat("DROP DATABASE ", database.Name)))
try {
command.ExecuteNonQuery();
} catch (Exception e) {
QueryService.RaiseException(e);
}finally {
conn.Release();
}
}
}
//http://msdn2.microsoft.com/en-us/library/aa258257(SQL.80).aspx
public override void CreateDatabase(DatabaseSchema database)
{
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand("CREATE DATABASE " + database.Name);
try {
using (command)
command.ExecuteNonQuery();
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
}
// see: http://dev.mysql.com/doc/refman/5.1/en/views-table.html
public override ViewSchemaCollection GetViews()
{
ViewSchemaCollection views = new ViewSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
using (IDbCommand command = conn.CreateCommand(string.Format(
@"SELECT
TABLE_NAME,
TABLE_SCHEMA
FROM information_schema.VIEWS
where TABLE_SCHEMA = '{0}'
ORDER BY TABLE_NAME" ,
ConnectionPool.ConnectionContext.ConnectionSettings.Database))){
try {
// Views are supported in mysql since version 5.
if (GetMainVersion(command) >= 5)
{
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ViewSchema view = new ViewSchema(this);
view.Name = r.GetString(0);
view.OwnerName = r.GetString(1);
using (IPooledDbConnection conn2 = connectionPool.Request()) {
using (IDbCommand command2 = conn2.CreateCommand(string.Concat(
"SHOW CREATE TABLE `",
view.Name, "`;"))) {
using (IDataReader r2 = command2.ExecuteReader()) {
r2.Read();
view.Definition = r2.GetString(1);
}
}
conn2.Release();
}
views.Add(view);
}
r.Close();
}
}
} catch (Exception e) {
QueryService.RaiseException(e);
} finally {
conn.Release();
}
}
}
return(views);
}
public override ViewSchemaCollection GetViews()
{
ViewSchemaCollection views = new ViewSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(
"SELECT v.schemaname, v.viewname, v.viewowner, v.definition,"
+ " (c.oid <= " + LastSystemOID + "), "
+ "(SELECT description from pg_description pd, "
+ " pg_class pc WHERE pc.oid=pd.objoid AND pc.relname="
+ " v.viewname) "
+ "FROM pg_views v, pg_class c "
+ "WHERE v.viewname = c.relname "
+ "ORDER BY viewname"
);
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ViewSchema view = new ViewSchema(this);
view.Name = r.GetString(1);
view.OwnerName = r.GetString(2);
view.SchemaName = r.GetString(0);
view.IsSystemView = r.GetBoolean(4);
view.Comment = r.IsDBNull(5) ? null : r.GetString(5);
// StringBuilder sb = new StringBuilder();
// sb.AppendFormat ("-- View: {0}\n", view.Name);
// sb.AppendFormat ("-- DROP VIEW {0};\n\n", view.Name);
// sb.AppendFormat ("CREATE VIEW {0} AS (\n", view.Name);
// string core = r.GetString(3);
// sb.AppendFormat (" {0}\n);", core.Substring (0, core.Length-1));
// view.Definition = sb.ToString ();
views.Add(view);
}
r.Close();
}
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(views);
}
//http://msdn2.microsoft.com/en-US/library/aa238878(SQL.80).aspx
private void Rename(string oldName, string newName, string type)
{
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateStoredProcedure(
String.Format("EXEC sp_rename '{0}', '{1}', '{2}'", oldName, newName, type)
);
try {
using (command)
command.ExecuteNonQuery();
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
}
public override ViewSchemaCollection GetViews()
{
ViewSchemaCollection views = new ViewSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
using (IDbCommand command = conn.CreateCommand(@"SELECT
su.name AS owner,
so.name as table_name,
so.id as table_id,
so.crdate as created_date,
so.xtype as table_type
FROM dbo.sysobjects so,
dbo.sysusers su
WHERE
xtype = 'V'
AND su.uid = so.uid
ORDER BY 1, 2" ))
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ViewSchema view = new ViewSchema(this);
view.Name = r.GetString(1);
view.SchemaName = r.GetString(0);
view.OwnerName = r.GetString(0);
StringBuilder sb = new StringBuilder();
sb.AppendFormat("-- View: {0}\n", view.Name);
sb.AppendFormat("-- DROP VIEW {0};\n\n", view.Name);
sb.AppendFormat(" {0}\n);", GetSource("[" + view.OwnerName + "].[" + view.Name + "]"));
view.Definition = sb.ToString();
views.Add(view);
}
r.Close();
}
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
finally {
conn.Release();
}
}
return(views);
}
private void OnCreateTableThreaded(object state)
{
object[] objs = state as object[];
ISchemaProvider provider = objs[0] as ISchemaProvider;
TableSchema table = objs[1] as TableSchema;
BaseNode node = objs[2] as BaseNode;
LoggingService.LogDebug("ADD TABLE: {0}", table.Definition);
IPooledDbConnection conn = provider.ConnectionPool.Request();
conn.ExecuteNonQuery(table.Definition);
conn.Release();
node.Refresh();
}
public override ConstraintSchemaCollection GetTableConstraints(TableSchema table)
{
ConstraintSchemaCollection constraints = new ConstraintSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand("select name, xtype from sysobjects where xtype in ('F','PK','CK')"); //TODO: unique
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ConstraintSchema constraint = null;
switch (r.GetString(1))
{
case "F": //foreign key
constraint = new ForeignKeyConstraintSchema(this);
break;
case "PK": //primary key
constraint = new PrimaryKeyConstraintSchema(this);
break;
case "CK": //check constraint
constraint = new CheckConstraintSchema(this);
break;
default:
break;
}
if (constraint != null)
{
constraint.Name = r.GetString(0);
constraints.Add(constraint);
}
}
r.Close();
}
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(constraints);
}
public override ConstraintSchemaCollection GetColumnConstraints(TableSchema table, ColumnSchema column)
{
ConstraintSchemaCollection constraints = new ConstraintSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
using (IDbCommand command = conn.CreateCommand(String.Format("DESCRIBE {0}", table.Name))) {
try {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
if (r.IsDBNull(3) || String.Compare(r.GetString(0), column.Name, true) != 0)
{
continue;
}
string key = r.GetString(3).ToUpper();
ConstraintSchema constraint = null;
if (key.Contains("PRI"))
{
constraint = CreatePrimaryKeyConstraintSchema("pk_" + column.Name);
}
else if (key.Contains("UNI"))
{
constraint = CreateUniqueConstraintSchema("uni_" + column.Name);
}
else
{
continue;
}
constraint.IsColumnConstraint = true;
constraint.OwnerName = r.GetString(0);
constraints.Add(constraint);
}
r.Close();
}
} catch (Exception e) {
QueryService.RaiseException(e);
} finally {
conn.Release();
}
}
}
return(constraints);
}
public override ProcedureSchemaCollection GetProcedures()
{
ProcedureSchemaCollection procedures = new ProcedureSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
using (IDbCommand command = conn.CreateCommand(@"SELECT
su.name AS owner,
so.name as proc_name,
so.id as proc_id,
so.crdate as created_date,
so.xtype as proc_type
FROM dbo.sysobjects so,
dbo.sysusers su
WHERE xtype = 'P'
AND su.uid = so.uid
ORDER BY 1, 2" ))
try {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ProcedureSchema procedure = new ProcedureSchema(this);
procedure.Name = r.GetString(1);
procedure.OwnerName = r.GetString(0);
procedure.LanguageName = "TSQL";
procedure.Definition = GetSource("[" + procedure.OwnerName + "].[" + procedure.Name + "]");
if (Array.Exists(system_procs, delegate(string s) { return(s == procedure.Name); }))
{
procedure.IsSystemProcedure = true;
}
else
{
procedure.IsSystemProcedure = false;
}
procedures.Add(procedure);
}
r.Close();
}
} catch (Exception e) {
QueryService.RaiseException(e);
}finally {
conn.Release();
}
}
return(procedures);
}
// see: http://dev.mysql.com/doc/refman/5.1/en/routines-table.html
public override ProcedureSchemaCollection GetProcedures()
{
ProcedureSchemaCollection procedures = new ProcedureSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(
"SELECT ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_TYPE FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA ='"
+ ConnectionPool.ConnectionContext.ConnectionSettings.Database +
"' ORDER BY ROUTINE_NAME"
);
try {
using (command) {
if (GetMainVersion(command) >= 5)
{
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ProcedureSchema procedure = new ProcedureSchema(this);
procedure.Name = r.GetString(0);
procedure.OwnerName = r.GetString(1);
procedure.IsSystemProcedure = r.GetString(2).ToLower().Contains("system");
IPooledDbConnection conn2 = connectionPool.Request();
IDbCommand command2 = conn2.CreateCommand("SHOW CREATE PROCEDURE `" + procedure.Name + "`;");
using (IDataReader r2 = command2.ExecuteReader()) {
r2.Read();
procedure.Definition = r2.GetString(2);
}
conn2.Release();
procedures.Add(procedure);
}
r.Close();
}
} //else: do nothing, since procedures are only supported since mysql 5.x
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(procedures);
}
public override ColumnSchemaCollection GetViewColumns(ViewSchema view)
{
ColumnSchemaCollection columns = new ColumnSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(
"SELECT attname, typname, attlen, attnotnull "
+ "FROM "
+ " pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_attrdef adef "
+ " ON a.attrelid=adef.adrelid "
+ " AND a.attnum=adef.adnum "
+ " LEFT JOIN pg_catalog.pg_type t ON a.atttypid=t.oid "
+ "WHERE "
+ " a.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname='"
+ view.Name + "') "
+ " AND a.attnum > 0 AND NOT a.attisdropped "
+ " ORDER BY a.attnum;"
);
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ColumnSchema column = new ColumnSchema(this, view);
column.Name = r.GetString(0);
column.DataTypeName = r.GetString(1);
column.SchemaName = view.SchemaName;
column.IsNullable = r.GetBoolean(3);
column.DataType.LengthRange.Default = r.GetInt32(2);
columns.Add(column);
}
r.Close();
};
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(columns);
}
// see: http://dev.mysql.com/doc/refman/5.1/en/views-table.html
public override ViewSchemaCollection GetViews()
{
ViewSchemaCollection views = new ViewSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(
"SELECT TABLE_NAME, TABLE_SCHEMA FROM information_schema.VIEWS where TABLE_SCHEMA = '"
+ ConnectionPool.ConnectionContext.ConnectionSettings.Database +
"' ORDER BY TABLE_NAME"
);
try {
using (command) {
if (GetMainVersion(command) >= 5)
{
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ViewSchema view = new ViewSchema(this);
view.Name = r.GetString(0);
view.OwnerName = r.GetString(1);
IPooledDbConnection conn2 = connectionPool.Request();
IDbCommand command2 = conn2.CreateCommand("SHOW CREATE TABLE `" + view.Name + "`;");
using (IDataReader r2 = command2.ExecuteReader()) {
r2.Read();
view.Definition = r2.GetString(1);
}
conn2.Release();
views.Add(view);
}
r.Close();
}
} //else: do nothing, since views are only supported since mysql 5.x
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(views);
}
//http://msdn2.microsoft.com/en-us/library/aa258257(SQL.80).aspx
public override void CreateDatabase(DatabaseSchema database)
{
SqlServerDatabaseSchema schema = (SqlServerDatabaseSchema)database;
StringBuilder db = new StringBuilder("CREATE DATABASE ");
string newLine = Environment.NewLine;
db.Append(schema.Name);
if (schema.FileName != string.Empty && schema.Name != string.Empty)
{
db.AppendLine();
db.Append("ON ");
db.AppendFormat("{0}(NAME = {1},", newLine, schema.LogicalName);
db.AppendFormat("{0}FILENAME = '{1}'", newLine, schema.FileName);
if (schema.Size.Size > 0)
{
db.AppendFormat(",{0}SIZE = {1}{2}", newLine, schema.Size.Size.ToString(), schema.Size.Type);
}
if (schema.MaxSize.Size > 0)
{
db.AppendFormat(",{0}MAXSIZE = {1}{2}", newLine, schema.MaxSize.Size.ToString(), schema.MaxSize.Type);
}
if (schema.FileGrowth.Size > 0)
{
db.AppendFormat(",{0}FILEGROWTH = {1}{2}", newLine, schema.FileGrowth.Size.ToString(), schema.FileGrowth.Type == SizeType.PERCENTAGE ? "%" : schema.FileGrowth.Type.ToString());
}
db.Append(")");
}
if (schema.Collation != null)
{
db.AppendFormat("{0}COLLATE {1}{0}", newLine, schema.Collation.Name);
}
using (IPooledDbConnection conn = connectionPool.Request()) {
using (IDbCommand command = conn.CreateCommand(db.ToString()))
try {
command.ExecuteNonQuery();
} catch (Exception e) {
QueryService.RaiseException(e);
}finally {
conn.Release();
}
}
}
protected virtual void TestClickedThreaded(object state)
{
DatabaseConnectionSettings settings = state as DatabaseConnectionSettings;
DatabaseConnectionContext context = new DatabaseConnectionContext(settings);
IDbFactory fac = DbFactoryService.GetDbFactory(settings.ProviderIdentifier);
bool success = false;
string error = null;
FakeConnectionPool pool = null;
IPooledDbConnection conn = null;
try {
pool = new FakeConnectionPool(fac, fac.ConnectionProvider, context);
success = pool.Initialize();
error = pool.Error;
} catch (System.Data.DataException ex) {
error = ex.Message;
success = false;
} finally {
if (conn != null)
{
conn.Release();
conn.Dispose();
}
if (pool != null)
{
pool.Close();
}
}
DispatchService.GuiDispatch(delegate() {
buttonTest.Sensitive = true;
if (success)
{
labelTest.Text = AddinCatalog.GetString("Test Succeeded.");
}
else
{
labelTest.Text = AddinCatalog.GetString("Test Failed: {0}.", error);
}
});
}
public override ProcedureSchemaCollection GetProcedures()
{
ProcedureSchemaCollection procedures = new ProcedureSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(
"SELECT su.name AS owner, so.name as proc_name, so.id as proc_id, " +
" so.crdate as created_date, so.xtype as proc_type " +
"FROM dbo.sysobjects so, dbo.sysusers su " +
"WHERE xtype = 'P' " +
"AND su.uid = so.uid " +
"ORDER BY 1, 2"
);
try {
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.LanguageName = "TSQL";
StringBuilder sb = new StringBuilder();
sb.AppendFormat("-- Procedure: {0}\n", procedure.Name);
sb.AppendFormat(" {0}\n);", GetSource("[" + procedure.OwnerName + "].[" + procedure.Name + "]"));
procedure.Definition = sb.ToString();
// FIXME : get sysproc or not
procedures.Add(procedure);
}
r.Close();
}
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(procedures);
}
private void ExecuteQueryThreaded (IPooledDbConnection connection, DataSet result, object state)
{
connection.Release ();
TimeSpan duration = DateTime.Now.Subtract (queryStart);
DispatchService.GuiDispatch (delegate () {
notebook.ShowAll ();
string msg = String.Concat (
AddinCatalog.GetPluralString ("Query executed ({0} result table)",
"Query executed ({0} result tables)", result.Tables.Count),
Environment.NewLine,
AddinCatalog.GetString ("Query duration: {0}", duration.ToString ())
);
SetQueryState (false, String.Format (msg, result.Tables.Count));
});
if (stoppedQueries.Contains (state)) {
stoppedQueries.Remove (state);
return;
}
if (result != null) {
foreach (DataTable table in result.Tables) {
DispatchService.GuiDispatch (delegate () {
MonoDevelop.Database.Components.DataGrid grid = new MonoDevelop.Database.Components.DataGrid ();
grid.DataSource = table;
grid.DataBind ();
string msg = String.Concat (Environment.NewLine, AddinCatalog.GetString ("Table"), ": ",table.TableName,
Environment.NewLine, "\t", AddinCatalog.GetString ("Affected Rows"), ": ", table.Rows.Count);
status.Buffer.Text += msg;
TabLabel label = new TabLabel (new Label (table.TableName), ImageService.GetImage ("md-db-table", IconSize.Menu));
label.CloseClicked += new EventHandler (OnResultTabClose);
notebook.AppendPage (grid, label);
notebook.ShowAll ();
this.Document.ReadOnly = false;
notebook.Page = notebook.NPages -1;
});
}
}
if (result == null || result.Tables.Count == 0) {
DispatchService.GuiDispatch (delegate () {
status.Buffer.Text += AddinCatalog.GetString ("No Results");
this.Document.ReadOnly = false;
});
}
}
private void OnSelectCommandThreaded (IPooledDbConnection connection, DataTable table, object state)
{
connection.Release ();
DispatchService.GuiDispatch (delegate () {
QueryResultView view = new QueryResultView (table);
IdeApp.Workbench.OpenDocument (view, true);
});
}
private void OnEmptyTableCallback (IPooledDbConnection connection, int result, object state)
{
connection.Release ();
DispatchService.GuiDispatch (delegate () {
IdeApp.Workbench.StatusBar.SetMessage (GettextCatalog.GetString ("Table emptied"));
});
}