/// <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 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 ColumnSchema(ColumnSchema column)
: base(column)
{
parent = column.parent; //do not clone, this would create an infinite loop
dataType = column.dataType;
hasDefaultValue = column.hasDefaultValue;
defaultValue = column.defaultValue;
nullable = column.nullable;
position = column.position;
constraints = new ConstraintSchemaCollection(column.constraints);
}
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();
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())
{
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 = GetNewPrimaryKeyConstraintSchema("pk_" + column.Name);
}
else if (key.Contains("UNI"))
{
constraint = GetNewUniqueConstraintSchema("uni_" + column.Name);
}
else
{
continue;
}
constraint.IsColumnConstraint = true;
constraint.OwnerName = r.GetString(0);
constraints.Add(constraint);
}
r.Close();
};
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(constraints);
}
public virtual ConstraintSchemaCollection GetTableConstraints(TableSchema table)
{
ConstraintSchemaCollection collection = new ConstraintSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
try {
//restrictions: database, schema, table, name
DataTable dt = conn.GetSchema(foreignKeysCollectionString, null, connectionPool.ConnectionContext.ConnectionSettings.Database);
for (int r = 0; r < dt.Rows.Count; r++)
{
DataRow row = dt.Rows[r];
collection.Add(GetTableConstraint(row, table));
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(collection);
}
public override ConstraintSchemaCollection GetTableConstraints(TableSchema table)
{
ConstraintSchemaCollection constraints = new ConstraintSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand("SHOW TABLE STATUS FROM `" + table.SchemaName + "`;");
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
string[] chunks = ((string)r["Comment"]).Split(';');
//the values we are looking for are in the format (`table`) REFER `database\table2` (`table2`)
foreach (string chunk in chunks)
{
if (constraintRegex.IsMatch(chunk))
{
MatchCollection matches = constraintRegex.Matches(chunk);
ForeignKeyConstraintSchema constraint = new ForeignKeyConstraintSchema(this);
constraint.ReferenceTableName = matches[1].Groups[1].ToString();
constraint.Name = matches[0].Groups[1].ToString();
constraints.Add(constraint);
}
}
}
r.Close();
}
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(constraints);
}
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 ConstraintSchemaCollection(ConstraintSchemaCollection collection)
: base(collection, true)
{
}
//http://www.sqlite.org/pragma.html
public virtual ConstraintSchemaCollection GetConstraints(TableSchema table, ColumnSchema column)
{
if (table == null)
{
throw new ArgumentNullException("table");
}
string columnName = column == null ? null : column.Name;
ConstraintSchemaCollection constraints = new ConstraintSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
//fk and unique
IDbCommand command = conn.CreateCommand("SELECT name, tbl_name FROM sqlite_master WHERE sql IS NULL AND type = 'index'");
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ConstraintSchema constraint = null;
if (r.IsDBNull(1) || r.GetString(1) == null)
{
constraint = new UniqueConstraintSchema(this);
}
else
{
ForeignKeyConstraintSchema fkc = new ForeignKeyConstraintSchema(this);
fkc.ReferenceTableName = r.GetString(1);
constraint = fkc;
}
constraint.Name = r.GetString(0);
constraints.Add(constraint);
}
r.Close();
}
}
//pk, column
if (columnName != null)
{
command = conn.CreateCommand(
"PRAGMA table_info('" + table.Name + "')"
);
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
if (r.GetInt32(5) == 1 && r.GetString(1) == columnName)
{
PrimaryKeyConstraintSchema constraint = new PrimaryKeyConstraintSchema(this);
ColumnSchema priColumn = new ColumnSchema(this, table);
priColumn.Name = r.GetString(1);
constraint.Columns.Add(priColumn);
constraint.IsColumnConstraint = true;
constraint.Name = "pk_" + table.Name + "_" + priColumn.Name;
constraints.Add(constraint);
}
}
r.Close();
}
}
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(constraints);
}
public override ConstraintSchemaCollection GetTableConstraints(TableSchema table)
{
ConstraintSchemaCollection constraints = new ConstraintSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand(String.Format(
"SELECT "
+ "pc.conname, "
+ "pg_catalog.pg_get_constraintdef(pc.oid, true) AS consrc, "
+ "pc.contype, "
+ "CASE WHEN pc.contype='u' OR pc.contype='p' THEN ( "
+ " SELECT "
+ " indisclustered "
+ " FROM "
+ " pg_catalog.pg_depend pd, "
+ " pg_catalog.pg_class pl, "
+ " pg_catalog.pg_index pi "
+ " WHERE "
+ " pd.refclassid=pc.tableoid "
+ " AND pd.refobjid=pc.oid "
+ " AND pd.objid=pl.oid "
+ " AND pl.oid=pi.indexrelid "
+ ") ELSE "
+ " NULL "
+ "END AS indisclustered "
+ "FROM "
+ "pg_catalog.pg_constraint pc "
+ "WHERE "
+ "pc.conrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname='{0}' "
+ " AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace "
+ " WHERE nspname='{1}')) "
+ "ORDER BY "
+ "1;", table.Name, table.SchemaName
));
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
ConstraintSchema constraint = null;
//TODO: Add support for Check constraints.
switch (r.GetString(2))
{
case "f":
string match = @".*REFERENCES (.+)\(.*\).*";
constraint = new ForeignKeyConstraintSchema(this);
if (Regex.IsMatch(r.GetString(1), match))
{
(constraint as ForeignKeyConstraintSchema).ReferenceTableName
= Regex.Match(r.GetString(1), match).Groups[0].Captures[0].Value;
}
break;
case "u":
constraint = new UniqueConstraintSchema(this);
break;
case "p":
default:
constraint = new PrimaryKeyConstraintSchema(this);
break;
}
constraint.Name = r.GetString(0);
constraint.Definition = r.GetString(1);
int parenOpen = constraint.Definition.IndexOf('(');
if (parenOpen > 0)
{
int parenClose = constraint.Definition.IndexOf(')');
string colstr = constraint.Definition.Substring(parenOpen + 1, parenClose - parenOpen - 1);
foreach (string col in colstr.Split(','))
{
ColumnSchema column = new ColumnSchema(this, table);
column.Name = col.Trim();
constraint.Columns.Add(column);
}
}
constraints.Add(constraint);
}
r.Close();
}
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(constraints);
}
public override void Refresh()
{
constraints = null;
}
