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);
}
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);
}
protected virtual string GetColumnsString(ColumnSchemaCollection columns, bool includeParens)
{
StringBuilder sb = new StringBuilder();
bool first = true;
if (includeParens)
{
sb.Append("(");
}
foreach (ColumnSchema column in columns)
{
if (first)
{
first = false;
}
else
{
sb.Append(",");
}
sb.Append(column.Name);
}
if (includeParens)
{
sb.Append(")");
}
return(sb.ToString());
}
public ViewSchema(ViewSchema view)
: base(view)
{
this.isSystemView = view.isSystemView;
this.statement = view.statement;
this.columns = new ColumnSchemaCollection(view.columns);
}
public override ColumnSchemaCollection GetViewColumns(ViewSchema view)
{
ColumnSchemaCollection columns = new ColumnSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(String.Format("DESCRIBE {0}", view.Name));
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.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);
}
conn.Release();
return(columns);
}
public IndexSchema(IndexSchema index)
: base(index)
{
this.tableName = index.tableName;
this.type = index.type;
this.columns = index.columns;
}
/// <summary>
/// Refresh the information associated with this table.
/// </summary>
public override void Refresh()
{
// TODO: Update Name, etc.
columns = null;
constraints = null;
triggers = null;
definition = null;
}
public ForeignKeyConstraintSchema(ForeignKeyConstraintSchema constraint)
: base(constraint)
{
referenceTable = constraint.referenceTable;
referenceColumns = new ColumnSchemaCollection(constraint.referenceColumns);
deleteAction = constraint.deleteAction;
updateAction = constraint.updateAction;
}
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);
}
public TableSchema(TableSchema table)
: base(table)
{
isSystemTable = table.isSystemTable;
tableSpaceName = table.tableSpaceName;
columns = new ColumnSchemaCollection(table.columns);
constraints = new ConstraintSchemaCollection(table.constraints);
triggers = new TriggerSchemaCollection(table.triggers);
}
public TableSchema(ISchemaProvider schemaProvider, string name)
: base(schemaProvider)
{
Name = name;
columns = new ColumnSchemaCollection();
constraints = new ConstraintSchemaCollection();
triggers = new TriggerSchemaCollection();
}
public override ColumnSchemaCollection GetTableColumns(TableSchema table)
{
ColumnSchemaCollection columns = new ColumnSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(
"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 = '" + table.Name + "' " +
"AND su.name = '" + table.OwnerName + "' " +
"AND sc.xusertype = st.xusertype " +
"AND su.uid = so.uid " +
"ORDER BY sc.colid"
);
try {
using (command) {
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);
}
conn.Release();
return(columns);
}
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);
}
public virtual ColumnSchemaCollection GetTableIndexColumns(TableSchema table, IndexSchema index)
{
ColumnSchemaCollection collection = new ColumnSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
try {
//restrictions: database, schema, table, ConstraintName, column
DataTable dt = conn.GetSchema(indexColumnsCollectionString, null, table.SchemaName, table.Name, index.Name);
for (int r = 0; r < dt.Rows.Count; r++)
{
DataRow row = dt.Rows[r];
collection.Add(GetTableIndexColumn(row, table, index));
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(collection);
}
protected string GetTableDefinition(TableSchema table)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("-- Table: {0}\n", table.Name);
sb.AppendFormat("-- DROP TABLE {0};\n\n", table.Name);
sb.AppendFormat("CREATE TABLE {0} (\n", table.Name);
ColumnSchemaCollection columns = table.Columns;
string[] parts = new string[columns.Count];
int i = 0;
foreach (ColumnSchema col in columns)
{
parts[i++] = col.Definition;
}
sb.Append(String.Join(",\n", parts));
ConstraintSchemaCollection constraints = table.Constraints;
parts = new string[constraints.Count];
if (constraints.Count > 0)
{
sb.Append(",\n");
}
i = 0;
foreach (ConstraintSchema constr in constraints)
{
parts[i++] = "\t" + constr.Definition;
}
sb.Append(String.Join(",\n", parts));
sb.Append("\n);\n");
//sb.AppendFormat ("COMMENT ON TABLE {0} IS '{1}';", table.Name, table.Comment);
return(sb.ToString());
}
public override ColumnSchemaCollection GetViewColumns(ViewSchema view)
{
ColumnSchemaCollection columns = new ColumnSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(
"SELECT * FROM \"" + view.Name +
"\" WHERE 1 = 0"
);
try {
using (command) {
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);
}
conn.Release();
return(columns);
}
public ColumnSchemaCollection(ColumnSchemaCollection collection)
: base(collection, false)
{
}
protected ConstraintSchema(ISchemaProvider schemaProvider, ConstraintType constraintType)
: base(schemaProvider)
{
columns = new ColumnSchemaCollection();
this.constraintType = constraintType;
}
protected ColumnSchemaCollection columns; //TODO: create col subclass, to include sort order and length
public IndexSchema(ISchemaProvider schemaProvider)
: base(schemaProvider)
{
columns = new ColumnSchemaCollection();
}
public ForeignKeyConstraintSchema(ISchemaProvider schemaProvider)
: base(schemaProvider, ConstraintType.ForeignKey)
{
referenceColumns = new ColumnSchemaCollection();
}
