private void OnSelectColumnsCommandGetColumns(object state)
{
TableNode node = (TableNode)state;
ColumnSchemaCollection columns = node.Table.Columns; //this can invoke the schema provider, so it must be in bg thread
DispatchService.GuiDispatch(delegate() {
SelectColumnDialog dlg = new SelectColumnDialog(true, columns);
if (dlg.Run() == (int)Gtk.ResponseType.Ok)
{
IdentifierExpression tableId = new IdentifierExpression(node.Table.Name);
List <IdentifierExpression> cols = new List <IdentifierExpression> ();
foreach (ColumnSchema schema in dlg.CheckedColumns)
{
cols.Add(new IdentifierExpression(schema.Name));
}
SelectStatement sel = new SelectStatement(new FromTableClause(tableId), cols);
IPooledDbConnection conn = node.ConnectionContext.ConnectionPool.Request();
IDbCommand command = conn.CreateCommand(sel);
conn.ExecuteTableAsync(command, new ExecuteCallback <DataTable> (OnSelectCommandThreaded), null);
}
dlg.Destroy();
});
}
public override ColumnSchemaCollection GetTableColumns(TableSchema table)
{
ColumnSchemaCollection columns = new ColumnSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(
"PRAGMA table_info('" + table.Name + "')"
);
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ColumnSchema column = new ColumnSchema(this, table);
column.Position = r.GetInt32(0);
column.Name = r.GetString(1);
column.DataTypeName = r.GetString(2);
column.IsNullable = r.GetInt32(3) != 0;
column.DefaultValue = r.IsDBNull(4) ? null : r.GetValue(4).ToString();
columns.Add(column);
}
r.Close();
};
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
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 ColumnSchemaCollection GetTableColumns(TableSchema table)
{
ColumnSchemaCollection columns = new ColumnSchemaCollection();
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())
{
ColumnSchema column = new ColumnSchema(this, table);
column.Name = r.GetString(0);
column.DataTypeName = r.GetString(1);
column.IsNullable = String.Compare(r.GetString(2), "YES", true) == 0;
column.DefaultValue = r.IsDBNull(4) ? null : r.GetString(4);
//TODO: if r.GetString (5) constains "auto_increment" ?
column.OwnerName = table.Name;
columns.Add(column);
}
r.Close();
}
} catch (Exception e) {
// Don't raise error, if the table doesn't exists return an empty collection
} finally {
conn.Release();
}
}
}
return(columns);
}
public override UserSchemaCollection GetUsers()
{
UserSchemaCollection users = new UserSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
using (IDbCommand command = conn.CreateCommand(@"SELECT DISTINCT user
from mysql.user where user != '';" )) {
try {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
UserSchema user = new UserSchema(this);
user.Name = r.GetString(0);
users.Add(user);
}
r.Close();
}
} catch (Exception e) {
QueryService.RaiseException(e);
} finally {
conn.Release();
}
}
}
return(users);
}
public MySqlCollationSchemaCollection GetCollations(MySqlCharacterSetSchema characterSet)
{
MySqlCollationSchemaCollection collations = new MySqlCollationSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
using (IDbCommand command = conn.CreateCommand(String.Format("SHOW COLLATION LIKE '{0}%';",
characterSet.Name))) {
try {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
MySqlCollationSchema collation = new MySqlCollationSchema(this);
collation.Name = r.GetString(0);
collation.CharacterSetName = r.GetString(1);
collation.Id = r.GetInt32(2);
collation.IsDefaultCollation = r.GetString(3) == "Yes" ? true : false;
collation.IsCompiled = r.GetString(4) == "Yes" ? true : false;
collation.SortLength = r.GetInt32(5);
collations.Add(collation);
}
r.Close();
}
} catch (Exception e) {
QueryService.RaiseException(e);
} finally {
conn.Release();
}
}
}
return(collations);
}
public virtual SqlServerCollationSchemaCollection GetCollations()
{
SqlServerCollationSchemaCollection collations = new SqlServerCollationSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
conn.DbConnection.ChangeDatabase("master");
using (IDbCommand command = conn.CreateCommand("SELECT * FROM ::fn_helpcollations()")) {
try {
using (IDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
SqlServerCollationSchema coll = new SqlServerCollationSchema(this);
coll.Name = reader.GetString(0);
coll.Description = reader.GetString(1);
collations.Add(coll);
}
reader.Close();
}
} catch (IOException ioex) {
//FIXME: Avoid an IOException AND ObjectDisposedException (https://bugzilla.novell.com/show_bug.cgi?id=556406)
} catch (ObjectDisposedException dex) {
}
catch (Exception e) {
QueryService.RaiseException(e);
} finally {
connectionPool.Release(conn);
}
}
}
return(collations);
}
public override DatabaseSchemaCollection GetDatabases()
{
DatabaseSchemaCollection databases = new DatabaseSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
using (IDbCommand command = conn.CreateCommand("SHOW DATABASES;")) {
try {
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);
}
//http://dev.mysql.com/doc/refman/5.0/en/charset-mysql.html
public MySqlCharacterSetSchemaCollection GetCharacterSets()
{
MySqlCharacterSetSchemaCollection characterSets = new MySqlCharacterSetSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
using (IDbCommand command = conn.CreateCommand("SHOW CHARACTER SET;")) {
try {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
MySqlCharacterSetSchema charset = new MySqlCharacterSetSchema(this);
charset.Name = r.GetString(0);
charset.Comment = r.GetString(1);
charset.DefaultCollactionName = r.GetString(2);
charset.MaxLength = r.GetInt32(3);
characterSets.Add(charset);
}
r.Close();
}
} catch (Exception e) {
QueryService.RaiseException(e);
} finally {
conn.Release();
}
}
}
return(characterSets);
}
public override ColumnSchemaCollection GetViewColumns(ViewSchema view)
{
ColumnSchemaCollection columns = new ColumnSchemaCollection();
using (IPooledDbConnection conn = connectionPool.Request()) {
using (IDbCommand command = conn.CreateCommand(String.Format("DESCRIBE {0}", view.Name))) {
try {
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.IsNullable = r.IsDBNull(2);
column.DefaultValue = r.GetString(4);
column.Comment = r.GetString(5);
column.OwnerName = view.Name;
columns.Add(column);
}
r.Close();
}
} catch (Exception e) {
QueryService.RaiseException(e);
} finally {
conn.Release();
}
}
}
return(columns);
}
public override ColumnSchemaCollection GetTableColumns(TableSchema table)
{
ColumnSchemaCollection columns = new ColumnSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(String.Format("DESCRIBE {0}", table.Name));
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(1);
column.IsNullable = String.Compare(r.GetString(2), "YES", true) == 0;
column.DefaultValue = r.IsDBNull(4) ? null : r.GetString(4);
//TODO: if r.GetString (5) constains "auto_increment"
column.OwnerName = table.Name;
columns.Add(column);
}
r.Close();
};
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(columns);
}
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 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);
}
public override TableSchemaCollection GetTables()
{
TableSchemaCollection tables = new TableSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(
"SELECT name, sql FROM sqlite_master WHERE type = 'table'"
);
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
TableSchema table = new TableSchema(this);
table.SchemaName = "main";
table.Name = r.GetString(0);
table.IsSystemTable = table.Name.StartsWith("sqlite_");
table.Definition = r.GetString(1);
tables.Add(table);
}
r.Close();
}
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(tables);
}
public override ViewSchemaCollection GetViews()
{
ViewSchemaCollection views = new ViewSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(
"SELECT name, sql FROM sqlite_master WHERE type = 'views'"
);
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ViewSchema view = new ViewSchema(this);
view.SchemaName = "main";
view.Name = r.GetString(0);
view.Definition = r.GetString(1);
views.Add(view);
}
r.Close();
}
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(views);
}
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);
}
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();
}
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);
}
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);
}
protected void OnSelectAllCommand()
{
TableNode node = (TableNode)CurrentNode.DataItem;
IdentifierExpression tableId = new IdentifierExpression(node.Table.Name);
SelectStatement sel = new SelectStatement(new FromTableClause(tableId));
IPooledDbConnection conn = node.ConnectionContext.ConnectionPool.Request();
IDbCommand command = conn.CreateCommand(sel);
conn.ExecuteTableAsync(command, new ExecuteCallback <DataTable> (OnSelectCommandThreaded), null);
}
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);
}
//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();
}
//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();
}
}
}
// 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);
}
private void ExecuteQueryCallback(DatabaseConnectionContext context, bool connected, object state)
{
if (!connected)
{
Services.MessageService.ShowErrorFormatted(
GettextCatalog.GetString("Unable to connect to database '{0}'"), context.ConnectionSettings.Name);
return;
}
currentQueryState = new object();
IPooledDbConnection conn = context.ConnectionPool.Request();
IDbCommand command = conn.CreateCommand(Text);
conn.ExecuteSetAsync(command, new ExecuteCallback <DataSet> (ExecuteQueryThreaded), currentQueryState);
}
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);
}
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);
}
private ParameterSchemaCollection GetParams (string names, string directions, string types, IPooledDbConnection conn)
{
ParameterSchemaCollection pars = new ParameterSchemaCollection ();
if (names == string.Empty || directions == string.Empty || types == String.Empty)
return pars;
// Array always start with { and end with }
if (!names.StartsWith ("{") || !names.EndsWith ("}"))
throw new ArgumentOutOfRangeException ("names");
string[] namesArray = names.Substring(1, names.Length -2).Split (',');
string[] directionsArray = directions.Substring(1, directions.Length -2).Split (',');
string[] typesArray = types.Substring(1, types.Length -2).Split (',');
for (int idx = 0; idx < namesArray.Length; idx++) {
ParameterSchema ps = new ParameterSchema (this);
// Name
if (namesArray[idx] != "\"\"")
ps.Name = namesArray[idx];
// Direction
string d = directionsArray[idx];
if (d == "i")
ps.ParameterType = ParameterType.In;
else if (d == "o")
ps.ParameterType = ParameterType.Out;
else
ps.ParameterType = ParameterType.InOut;
// Type
using (IDbCommand cmd = conn.CreateCommand (string.Format (
"select format_type(oid, null) from pg_type WHERE oid = '{0}'::oid", typesArray[idx]))) {
using (IDataReader r = cmd.ExecuteReader())
if (r.Read ())
ps.DataTypeName = r.GetString (0);
}
pars.Add (ps);
}
return pars;
