public static void BuildChildNodes(ITreeBuilder builder, ViewSchema node)
{
builder.AddChild(new ColumnsNode(node.Provider, node));
}
/// <summary>
/// Get a collection of columns within a view
/// </summary>
public override ColumnSchema[] GetViewColumns(ViewSchema view)
{
if (IsOpen == false && Open() == false)
throw new Exception ("No connection to database");
ArrayList collection = new ArrayList();
// TODO:
return (ColumnSchema[]) collection.ToArray (typeof(ColumnSchema));
}
public static void ListView(ViewSchema view)
{
Console.WriteLine("View Definition:\n" + view.Definition);
}
public override ColumnSchema[] GetViewColumns(ViewSchema view)
{
throw new NotImplementedException ();
}
/// <summary>
/// Get a collection of columns within a view
/// </summary>
public override ColumnSchema[] GetViewColumns(ViewSchema view)
{
if (IsOpen == false && Open() == false)
throw new Exception ("No connection to database");
ArrayList collection = new ArrayList();
SybaseConnection con2 = (SybaseConnection) (((ICloneable) connection).Clone ());
if (con2.State == ConnectionState.Closed)
con2.Open();
SybaseCommand command = con2.CreateCommand ();
command.CommandText =
"SELECT * " +
" FROM " + view.Name +
" WHERE 1 = 0";
SybaseDataReader r = command.ExecuteReader();
for (int i = 0; i < r.FieldCount; i++) {
ColumnSchema column = new ColumnSchema();
column.Name = r.GetName(i);
column.DataTypeName = r.GetDataTypeName(i);
column.Default = "";
column.Definition = "";
column.OwnerName = view.OwnerName;
column.SchemaName = view.OwnerName;
collection.Add(column);
}
command.Dispose ();
command = null;
con2.Close ();
con2 = null;
return (ColumnSchema[]) collection.ToArray (typeof(ColumnSchema));
}
/// <summary>
/// Get a collection of views from the system.
/// </summary>
public override ViewSchema[] GetViews()
{
ArrayList collection = new ArrayList();
SybaseCommand command = new SybaseCommand();
command.Connection = connection;
command.CommandText =
"SELECT su.name AS owner, so.name as table_name, so.id as table_id, " +
" so.crdate as created_date, so.type as table_type " +
"FROM dbo.sysobjects so, dbo.sysusers su " +
"WHERE type = 'V' " +
"AND su.uid = so.uid " +
"ORDER BY 1, 2";
SybaseDataReader r = command.ExecuteReader();
while (r.Read()) {
ViewSchema view = new ViewSchema();
view.Provider = this;
try {
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);
string source = GetSource(view.Owner + "." + view.Name);
sb.AppendFormat (" {0}\n);", source);
view.Definition = sb.ToString ();
//view.Comment = r.GetString(5);
} catch (Exception e) {
}
collection.Add(view);
}
r.Close ();
r = null;
command.Dispose();
command = null;
return (ViewSchema[]) collection.ToArray (typeof (ViewSchema));
}
/// <summary>
/// Get a collection of columns within a view
/// </summary>
public override ColumnSchema[] GetViewColumns(ViewSchema view)
{
if (IsOpen == false && Open() == false)
throw new Exception ("No connection to database");
ArrayList collection = new ArrayList();
NpgsqlCommand command = new NpgsqlCommand ();
command.Connection = connection;
command.CommandText =
"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;";
NpgsqlDataReader r = command.ExecuteReader();
while (r.Read()) {
ColumnSchema column = new ColumnSchema();
try {
column.Name = r.GetString(0);
column.Provider = this;
column.DataTypeName = r.GetString(1);
column.Default = "";
column.SchemaName = view.SchemaName;
column.Definition = "";
column.NotNull = r.GetBoolean(3);
column.Length = r.GetInt32(2);
} catch {
} finally {
collection.Add(column);
}
}
r.Close ();
return (ColumnSchema[]) collection.ToArray (typeof(ColumnSchema));
}
/// <summary>
/// Get a collection of views from the system.
/// </summary>
public override ViewSchema[] GetViews()
{
ArrayList collection = new ArrayList();
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = connection;
command.CommandText =
"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";
NpgsqlDataReader r = command.ExecuteReader();
while (r.Read()) {
ViewSchema view = new ViewSchema();
view.Provider = this;
try {
view.Name = r.GetString(1);
view.SchemaName = r.GetString(0);
view.OwnerName = r.GetString(2);
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 ();
view.IsSystemView = (r.GetBoolean(4));
view.Comment = r.GetString(5);
} catch (Exception e) {
}
collection.Add(view);
}
r.Close ();
return (ViewSchema[]) collection.ToArray (typeof (ViewSchema));
}
public virtual ColumnSchema[] GetViewColumns(ViewSchema schema)
{
throw new NotImplementedException ();
}
public virtual void GetViewColumns(SQLCallback Callback, ViewSchema schema)
{
Thread eThread = null;
lock (ThreadSync) {
ThreadedSQLCallback = Callback;
ThreadedViewSchema = schema;
eThread = new Thread (new ThreadStart (GetViewColumnsThreadStart));
eThread.Start ();
}
}
/// <summary>
/// Get a collection of views from the system.
/// </summary>
public override ViewSchema[] GetViews()
{
if (IsOpen == false && Open () == false)
throw new InvalidOperationException ("Invalid connection.");
ArrayList collection = new ArrayList();
OracleCommand command = new OracleCommand();
command.Connection = connection;
command.CommandText =
"SELECT OWNER, VIEW_NAME, TEXT " +
"FROM ALL_VIEWS " +
"ORDER BY OWNER, VIEW_NAME";
OracleDataReader r = command.ExecuteReader();
while (r.Read()) {
ViewSchema view = new ViewSchema();
view.Provider = this;
try {
view.Name = r.GetString(1);
view.SchemaName = r.GetString(0);
view.OwnerName = r.GetString(0);
view.Definition = r.GetString(2);
view.IsSystemView = IsSystem (view.OwnerName);
view.Comment = "";
} catch (Exception e) {
}
collection.Add(view);
}
return (ViewSchema[]) collection.ToArray (typeof(ViewSchema));
}
/// <summary>
/// Get a collection of columns within a view
/// </summary>
public override ColumnSchema[] GetViewColumns(ViewSchema view)
{
if (IsOpen == false && Open () == false)
throw new InvalidOperationException ("Invalid connection.");
ArrayList collection = new ArrayList();
OracleCommand command = new OracleCommand ();
command.Connection = connection;
command.CommandText =
"SELECT * " +
" FROM " + view.Name +
" WHERE 1 = 0";
OracleDataReader r = command.ExecuteReader();
for (int i = 0; i < r.FieldCount; i++) {
ColumnSchema column = new ColumnSchema();
column.Name = r.GetName(i);
column.DataTypeName = r.GetDataTypeName(i);
column.Default = "";
column.Definition = "";
column.OwnerName = view.OwnerName;
column.SchemaName = view.OwnerName;
collection.Add(column);
}
return (ColumnSchema[]) collection.ToArray (typeof(ColumnSchema));
}
/// <summary>
/// Get a collection of views from the system.
/// </summary>
public override ViewSchema[] GetViews()
{
ArrayList collection = new ArrayList();
DataTable table2 = connection.GetSchema ("Views", new string[] {null, null, null});
for (int r = 0; r < table2.Rows.Count; r++) {
DataRow row2 = table2.Rows[r];
string viewName = row2["VIEW_NAME"].ToString();
ViewSchema view = new ViewSchema();
view.Provider = this;
view.Name = viewName;
view.SchemaName = "";
view.OwnerName = "";
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);
view.Definition = "";
view.IsSystemView = false;
view.Comment = "";
collection.Add(view);
}
return (ViewSchema[]) collection.ToArray (typeof (ViewSchema));
}
