protected virtual string GetConstraintString(ConstraintSchema constraint, bool isTableConstraint) { StringBuilder sb = new StringBuilder(); sb.Append("CONSTRAINT "); sb.Append(constraint.Name); sb.Append(' '); switch (constraint.ConstraintType) { case ConstraintType.PrimaryKey: sb.Append("PRIMARY KEY "); if (isTableConstraint) { sb.Append(GetColumnsString(constraint.Columns, true)); } break; case ConstraintType.Unique: sb.Append("UNIQUE "); if (isTableConstraint) { sb.Append(GetColumnsString(constraint.Columns, true)); } break; case ConstraintType.ForeignKey: sb.Append("FOREIGN KEY "); sb.Append(GetColumnsString(constraint.Columns, true)); sb.Append(" REFERENCES "); ForeignKeyConstraintSchema fk = constraint as ForeignKeyConstraintSchema; sb.Append(fk.ReferenceTable); sb.Append(' '); if (fk.ReferenceColumns != null) { sb.Append(GetColumnsString(fk.ReferenceColumns, true)); } break; case ConstraintType.Check: sb.Append("CHECK ("); sb.Append((constraint as CheckConstraintSchema).Source); sb.Append(")"); break; default: throw new NotImplementedException(); } return(sb.ToString()); }
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 override ICollection <ConstraintSchema> GetTableConstraints(TableSchema table) { CheckConnectionState(); List <ConstraintSchema> constraints = new List <ConstraintSchema> (); IDbCommand command = connectionProvider.CreateCommand( "SELECT k.owner, k.table_name, k.constraint_name, " + " k.constraint_type, k.status, k.validated " + "FROM all_constraints k " + "WHERE k.owner = '" + table.OwnerName + "' " + "AND k.table_name = '" + table.Name + "' " + "and k.constraint_type = 'P'" ); using (command) { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { ConstraintSchema constraint = null; switch (r.GetString(4)) { case "P": default: constraint = new PrimaryKeyConstraintSchema(this); break; } constraint.Name = r.GetString(3); constraint.Definition = ""; constraints.Add(constraint); } r.Close(); } connectionProvider.Close(command.Connection); } return(constraints); }
protected virtual string GetConstraintString(ConstraintSchema constraint) { //PRIMARY KEY [sort-order] [ conflict-clause ] [AUTOINCREMENT] //UNIQUE [ conflict-clause ] //CHECK ( expr ) //COLLATE collation-name StringBuilder sb = new StringBuilder(); sb.Append("CONSTRAINT "); sb.Append(constraint.Name); sb.Append(' '); switch (constraint.ConstraintType) { case ConstraintType.PrimaryKey: //PrimaryKeyConstraintSchema pk = constraint as PrimaryKeyConstraintSchema; sb.Append("PRIMARY KEY"); //TODO: auto inc + sort break; case ConstraintType.Unique: //UniqueConstraintSchema u = constraint as UniqueConstraintSchema; sb.Append("UNIQUE"); break; case ConstraintType.Check: CheckConstraintSchema chk = constraint as CheckConstraintSchema; sb.Append("CHECK ("); sb.Append(chk.Source); sb.Append(")"); break; default: throw new NotImplementedException(); } return(sb.ToString()); }
protected ConstraintSchema(ConstraintSchema constraint) : base(constraint) { isColumnConstraint = constraint.isColumnConstraint; constraintType = constraint.constraintType; }
protected ConstraintSchema (ConstraintSchema constraint) : base (constraint) { isColumnConstraint = constraint.isColumnConstraint; constraintType = constraint.constraintType; }
//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); }
protected virtual string GetConstraintString (ConstraintSchema constraint) { //PRIMARY KEY [sort-order] [ conflict-clause ] [AUTOINCREMENT] //UNIQUE [ conflict-clause ] //CHECK ( expr ) //COLLATE collation-name StringBuilder sb = new StringBuilder (); sb.Append ("CONSTRAINT "); sb.Append (constraint.Name); sb.Append (' '); switch (constraint.ConstraintType) { case ConstraintType.PrimaryKey: //PrimaryKeyConstraintSchema pk = constraint as PrimaryKeyConstraintSchema; sb.Append ("PRIMARY KEY"); //TODO: auto inc + sort break; case ConstraintType.Unique: //UniqueConstraintSchema u = constraint as UniqueConstraintSchema; sb.Append ("UNIQUE"); break; case ConstraintType.Check: CheckConstraintSchema chk = constraint as CheckConstraintSchema; sb.Append ("CHECK ("); sb.Append (chk.Source); sb.Append (")"); break; default: throw new NotImplementedException (); } return sb.ToString (); }
protected virtual string GetConstraintString (ConstraintSchema constraint, bool isTableConstraint) { StringBuilder sb = new StringBuilder (); sb.Append ("CONSTRAINT "); sb.Append (constraint.Name); sb.Append (' '); switch (constraint.ConstraintType) { case ConstraintType.PrimaryKey: sb.Append ("PRIMARY KEY "); if (isTableConstraint) sb.Append (GetColumnsString (constraint.Columns, true)); break; case ConstraintType.Unique: sb.Append ("UNIQUE "); if (isTableConstraint) sb.Append (GetColumnsString (constraint.Columns, true)); break; case ConstraintType.ForeignKey: sb.Append ("FOREIGN KEY "); sb.Append (GetColumnsString (constraint.Columns, true)); sb.Append (" REFERENCES "); ForeignKeyConstraintSchema fk = constraint as ForeignKeyConstraintSchema; sb.Append (fk.ReferenceTable); sb.Append (' '); if (fk.ReferenceColumns != null) sb.Append (GetColumnsString (fk.ReferenceColumns, true)); break; case ConstraintType.Check: sb.Append ("CHECK ("); sb.Append ((constraint as CheckConstraintSchema).Source); sb.Append (")"); break; default: throw new NotImplementedException (); } return sb.ToString ();