/// <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; }