public static void BuildChildNodes(ITreeBuilder builder, ColumnSchema node)
{
}
public override ColumnSchema[] GetTableColumns(TableSchema table)
{
if (IsOpen == false && Open () == false)
throw new InvalidOperationException ("Invalid connection");
ArrayList collection = new ArrayList ();
using (MySqlCommand command = new MySqlCommand ()) {
command.Connection = Connection;
// XXX: Use String.Format cause mysql parameters suck assmar.
command.CommandText =
String.Format ("DESCRIBE {0}", table.Name);
MySqlDataReader r = command.ExecuteReader ();
while (r.Read ()) {
ColumnSchema column = new ColumnSchema ();
column.Provider = this;
column.Name = r.GetString (0);
column.DataTypeName = r.GetString (1);
column.NotNull = r.IsDBNull (2);
column.Default = r.GetString (4);
column.Options["extra"] = r.GetString (5);
collection.Add (column);
}
r.Close ();
}
return (ColumnSchema[]) collection.ToArray (typeof (ColumnSchema));
}
/// <summary>
/// Get columns for a table.
/// </summary>
public override ColumnSchema[] GetTableColumns(TableSchema table)
{
if (IsOpen == false && Open () == false)
throw new InvalidOperationException ("Invalid connection");
ArrayList collection = new ArrayList();
SybaseConnection con2 = (SybaseConnection) (((ICloneable) connection).Clone ());
if (con2.State == ConnectionState.Closed)
con2.Open();
SybaseCommand command = con2.CreateCommand ();
command.CommandText =
"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.prec as data_preceision, sc.scale as data_scale, " +
" 0 as 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.type in ('U','S') " +
"and so.name = '" + table.Name + "' " +
"and su.name = '" + table.OwnerName + "' " +
"and su.uid = so.uid " +
"and sc.usertype = st.usertype " +
"order by sc.colid";
SybaseDataReader r = command.ExecuteReader();
while (r.Read()) {
ColumnSchema column = new ColumnSchema();
try { column.Name = r.GetString(2); } catch {}
column.Provider = this;
try { column.DataTypeName = r.GetString(3); } catch {}
try { column.Default = ""; } catch {}
column.Comment = "";
column.OwnerName = table.OwnerName;
column.SchemaName = table.OwnerName;
try { column.NotNull = r.GetValue(7).ToString() == "0" ? true : false; } catch {}
try { column.Length = r.GetInt32(4); } catch {}
//try { column.Precision = GetInt(r, 5)); } catch {}
//try { column.Scale = GetIn(r, 6); } catch {}
StringBuilder sb = new StringBuilder();
sb.AppendFormat("{0} {1}{2}",
column.Name,
column.DataTypeName,
(column.Length > 0) ? ("(" + column.Length + ")") : "");
sb.AppendFormat(" {0}", column.NotNull ? "NOT NULL" : "NULL");
//if (column.Default.Length > 0)
// sb.AppendFormat(" DEFAULT {0}", column.Default);
column.Definition = sb.ToString();
collection.Add(column);
}
r.Close ();
r = null;
command.Dispose ();
command = null;
con2.Close ();
con2 = null;
return (ColumnSchema[]) collection.ToArray(typeof(ColumnSchema));
}
/// <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 columns for a table.
/// </summary>
public override ColumnSchema[] GetTableColumns(TableSchema table)
{
if (IsOpen == false && Open () == false)
throw new InvalidOperationException ("Invalid connection");
ArrayList collection = new ArrayList();
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = connection;
command.CommandText = "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;";
NpgsqlDataReader r = command.ExecuteReader();
while (r.Read()) {
ColumnSchema column = new ColumnSchema();
try { column.Name = r.GetString(0); } catch {}
column.Provider = this;
try { column.DataTypeName = r.GetString(3); } catch {}
try { column.Default = r.GetString(4); } catch {}
column.Comment = "";
column.OwnerName = "";
column.SchemaName = table.SchemaName;
try { column.NotNull = r.GetBoolean(1); } catch {}
try { column.Length = r.GetInt32(2); } catch {}
StringBuilder sb = new StringBuilder();
sb.AppendFormat("{0} {1}{2}",
column.Name,
column.DataTypeName,
(column.Length > 0) ? ("(" + column.Length + ")") : "");
sb.AppendFormat(" {0}", column.NotNull ? "NOT NULL" : "NULL");
if (column.Default.Length > 0)
sb.AppendFormat(" DEFAULT {0}", column.Default);
column.Definition = sb.ToString();
collection.Add(column);
}
r.Close ();
return (ColumnSchema[]) collection.ToArray(typeof(ColumnSchema));
}
/// <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));
}
public override ColumnSchema[] GetProcedureColumns(ProcedureSchema schema)
{
if (IsOpen == false && Open () == false)
throw new InvalidOperationException ("Invalid connection");
ArrayList collection = new ArrayList ();
// FIXME: Won't work properly with overload functions.
// Maybe check the number of columns in the parameters for
// proper match.
NpgsqlCommand command = new NpgsqlCommand ();
command.Connection = connection;
command.CommandText = String.Format (
"SELECT format_type (prorettype, NULL) "
+ "FROM pg_proc pc, pg_language pl "
+ "WHERE pc.prolang = pl.oid "
+ "AND pc.proname = '{0}';", schema.Name);
NpgsqlDataReader r = command.ExecuteReader ();
while (r.Read ()) {
ColumnSchema column = new ColumnSchema ();
column.Provider = this;
column.DataTypeName = r.GetString (0);
column.Name = r.GetString (0);
collection.Add (column);
}
r.Close ();
return (ColumnSchema[]) collection.ToArray (typeof (ColumnSchema));
}
public override ColumnSchema[] GetProcedureColumns(ProcedureSchema schema)
{
if (IsOpen == false && Open () == false)
throw new InvalidOperationException ("Invalid connection");
ArrayList collection = new ArrayList ();
using (SqlCommand cmd = connection.CreateCommand ()) {
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_sproc_columns";
SqlParameter owner = cmd.Parameters.Add ("@procedure_owner", SqlDbType.VarChar);
SqlParameter name = cmd.Parameters.Add ("@procedure_name", SqlDbType.VarChar);
owner.Value = schema.OwnerName;
name.Value = schema.Name;
using (SqlDataReader reader = cmd.ExecuteReader ()) {
while (reader.Read ()) {
ColumnSchema column = new ColumnSchema ();
column.Provider = this;
column.Name = (string) reader ["COLUMN_NAME"];
column.DataTypeName = (string) reader ["TYPE_NAME"];
collection.Add (column);
}
}
}
return (ColumnSchema []) collection.ToArray (typeof (ColumnSchema));
}
/// <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 columns for a table.
/// </summary>
public override ColumnSchema[] GetTableColumns(TableSchema table)
{
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, TABLE_NAME, COLUMN_NAME, " +
" DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, " +
" NULLABLE, COLUMN_ID, DEFAULT_LENGTH, DATA_DEFAULT " +
"FROM ALL_TAB_COLUMNS " +
"WHERE OWNER = '" + table.OwnerName + "' " +
"AND TABLE_NAME = '" + table.Name + "' " +
"ORDER BY OWNER, TABLE_NAME, COLUMN_ID";
OracleDataReader r = command.ExecuteReader();
while (r.Read()) {
ColumnSchema column = new ColumnSchema();
try { column.Name = r.GetValue(2).ToString(); } catch {}
column.Provider = this;
try { column.DataTypeName = r.GetValue(3).ToString(); } catch {}
column.Default = "";
column.Comment = "";
column.OwnerName = table.OwnerName;
column.SchemaName = table.OwnerName;
try { column.NotNull = r.GetValue(7).ToString() == "Y" ? true : false; } catch {}
try { column.Length = GetInt(r, 4); } catch {}
try { column.Precision = GetInt(r, 5); } catch {}
try { column.Scale = GetInt(r, 6); } catch {}
try { column.ColumnID = GetInt(r, 8); } catch {}
StringBuilder sb = new StringBuilder();
sb.AppendFormat("{0} {1}{2}",
column.Name,
column.DataTypeName,
(column.Length > 0) ? ("(" + column.Length + ")") : "");
sb.AppendFormat(" {0}", column.NotNull ? "NOT NULL" : "NULL");
//if (column.Default.Length > 0)
// sb.AppendFormat(" DEFAULT {0}", column.Default);
column.Definition = sb.ToString();
collection.Add(column);
}
return (ColumnSchema[]) collection.ToArray(typeof(ColumnSchema));
}
/// <summary>
/// Get columns for a table.
/// </summary>
public override ColumnSchema[] GetTableColumns(TableSchema table)
{
if (IsOpen == false && Open () == false)
throw new InvalidOperationException ("Invalid connection");
ArrayList collection = new ArrayList();
DataTable table2 = connection.GetSchema ("Columns", new string[] {null, null, table.Name, null});
for (int r = 0; r < table2.Rows.Count; r++) {
DataRow row2 = table2.Rows[r];
string columnName = row2["COLUMN_NAME"].ToString();
string dataType = row2["COLUMN_DATA_TYPE"].ToString();
int columnSize = 0;
if (row2["COLUMN_SIZE"] != DBNull.Value)
columnSize = (int) row2["COLUMN_SIZE"];
int precision = 0;
if (row2["NUMERIC_PRECISION"] != DBNull.Value)
precision = (int) row2["NUMERIC_PRECISION"];
int scale = 0;
if (row2["NUMERIC_SCALE"] != DBNull.Value)
scale = (int) row2["NUMERIC_SCALE"];
//bool isNullable = false; // FIXME: is nullable
//short n = 0;
//if (row2["IS_NULLABLE"] != DBNull.Value)
// n = (short) row2["IS_NULLABLE"];
//
//if (n == 1)
// isNullable = true;
//int pos = 0; // FIXME: ordinal position
//if (row2["ORDINAL_POSITION"] != DBNull.Value)
// pos = (int) row2["ORDINAL_POSITION"];
ColumnSchema column = new ColumnSchema();
column.Name = columnName;
column.Provider = this;
column.DataTypeName = dataType;
column.Default = "";
column.Comment = "";
column.OwnerName = "";
column.SchemaName = table.SchemaName;
column.NotNull = false; // TODO
column.Length = columnSize;
column.Precision = precision;
column.Scale = scale;
StringBuilder sb = new StringBuilder();
sb.AppendFormat("{0} {1}{2}",
column.Name,
column.DataTypeName,
(column.Length > 0) ? ("(" + column.Length + ")") : "");
sb.AppendFormat(" {0}", column.NotNull ? "NOT NULL" : "NULL");
if (column.Default.Length > 0)
sb.AppendFormat(" DEFAULT {0}", column.Default);
column.Definition = sb.ToString();
collection.Add(column);
}
return (ColumnSchema[]) collection.ToArray(typeof(ColumnSchema));
}
