public override ConstraintSchemaCollection GetTableConstraints(TableSchema table) { ConstraintSchemaCollection constraints = new ConstraintSchemaCollection(); using (IPooledDbConnection conn = connectionPool.Request()) { using (IDbCommand command = conn.CreateCommand(string.Format(@"select sysobjects.name, sysobjects.xtype from sysobjects inner join sysobjects sysobjectsParents ON sysobjectsParents.id = sysobjects.parent_obj where sysobjectsParents.name = '{0}' and sysobjects.xtype in ('C', 'UQ', 'F','PK','CK')" , table.Name))) try { 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 "C": case "CK": //check constraint constraint = new CheckConstraintSchema(this); break; case "UQ": constraint = new UniqueConstraintSchema(this); break; default: break; } if (constraint != null) { constraint.Name = r.GetString(0); constraints.Add(constraint); } } r.Close(); } } catch (Exception e) { QueryService.RaiseException(e); }finally { conn.Release(); } } return(constraints); }
private void SourceChanged(object sender, EventArgs args) { TreeIter iter; if (listCheck.Selection.GetSelected(out iter)) { CheckConstraintSchema check = store.GetValue(iter, colObjIndex) as CheckConstraintSchema; check.Source = sqlEditor.Text; store.SetValue(iter, colSourceIndex, sqlEditor.Text); EmitContentChanged(); } }
protected virtual void AddClicked(object sender, EventArgs e) { CheckConstraintSchema check = schemaProvider.GetNewCheckConstraintSchema("check_new"); int index = 1; while (constraints.Contains(check.Name)) { check.Name = "check_new" + (index++); } constraints.Add(check); AddConstraint(check); EmitContentChanged(); }
protected virtual void AddClicked(object sender, EventArgs e) { CheckConstraintSchema check = schemaProvider.CreateCheckConstraintSchema(string.Concat(table.Name, "_", "check_new")); int index = 1; while (constraints.Contains(check.Name)) { check.Name = string.Concat(table.Name, "_", "check_new", (index++).ToString()); } constraints.Add(check); AddConstraint(check); EmitContentChanged(); }
protected virtual void OnSelectionChanged(object sender, EventArgs e) { TreeIter iter; if (listCheck.Selection.GetSelected(out iter)) { buttonRemove.Sensitive = true; sqlEditor.Editable = true; CheckConstraintSchema check = store.GetValue(iter, colObjIndex) as CheckConstraintSchema; sqlEditor.Text = check.Source; } else { buttonRemove.Sensitive = false; sqlEditor.Editable = false; sqlEditor.Text = String.Empty; } }
protected virtual void RemoveClicked(object sender, EventArgs e) { TreeIter iter; if (listCheck.Selection.GetSelected(out iter)) { CheckConstraintSchema check = store.GetValue(iter, colObjIndex) as CheckConstraintSchema; if (MessageService.Confirm( AddinCatalog.GetString("Are you sure you want to remove constraint '{0}'?", check.Name), AlertButton.Remove )) { store.Remove(ref iter); constraints.Remove(check); EmitContentChanged(); } } }
public virtual void FillSchemaObjects() { TreeIter iter; if (store.GetIterFirst(out iter)) { do { CheckConstraintSchema check = store.GetValue(iter, colObjIndex) as CheckConstraintSchema; check.Name = store.GetValue(iter, colNameIndex) as string; check.Source = store.GetValue(iter, colSourceIndex) as string; check.IsColumnConstraint = (bool)store.GetValue(iter, colIsColumnConstraintIndex); if (check.IsColumnConstraint) { string column = store.GetValue(iter, colColumnNameIndex) as string; check.Columns.Add(columns.Search(column)); } table.Constraints.Add(check); } while (store.IterNext(ref iter)); } }
private void AddConstraint(CheckConstraintSchema check) { store.AppendValues(check.Name, String.Empty, false, String.Empty, check); }
public override ConstraintSchemaCollection GetTableConstraints (TableSchema table) { ConstraintSchemaCollection constraints = new ConstraintSchemaCollection (); using (IPooledDbConnection conn = connectionPool.Request ()) { using (IDbCommand command = conn.CreateCommand (string.Format (@"select sysobjects.name, sysobjects.xtype from sysobjects inner join sysobjects sysobjectsParents ON sysobjectsParents.id = sysobjects.parent_obj where sysobjectsParents.name = '{0}' and sysobjects.xtype in ('C', 'UQ', 'F','PK','CK')", table.Name))) try { 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 "C": case "CK": //check constraint constraint = new CheckConstraintSchema (this); break; case "UQ": constraint = new UniqueConstraintSchema (this); break; default: break; } if (constraint != null) { constraint.Name = r.GetString (0); constraints.Add (constraint); } } r.Close (); } } catch (Exception e) { QueryService.RaiseException (e); } finally { conn.Release (); } } return constraints;
protected virtual string GetConstraintString(ConstraintSchema constraint) { bool first = true; //PRIMARY KEY [sort-order] [ conflict-clause ] [AUTOINCREMENT] //UNIQUE [ conflict-clause ] //CHECK ( expr ) //COLLATE collation-name StringBuilder sb = new StringBuilder(); switch (constraint.ConstraintType) { case ConstraintType.PrimaryKey: sb.Append("PRIMARY KEY ("); //TODO: auto inc + sort first = true; foreach (ColumnSchema col in constraint.Columns) { if (!first) { sb.Append(","); } sb.Append(col.Name); first = false; } sb.Append(")"); break; case ConstraintType.Unique: sb.Append("UNIQUE ("); first = true; foreach (ColumnSchema col in constraint.Columns) { if (!first) { sb.Append(","); } sb.Append(col.Name); first = false; } sb.Append(")"); break; case ConstraintType.Check: CheckConstraintSchema chk = constraint as CheckConstraintSchema; sb.Append("CHECK ("); sb.Append(chk.Source); sb.Append(")"); break; case ConstraintType.ForeignKey: sb.Append("FOREIGN KEY "); sb.Append(GetColumnsString(constraint.Columns, true)); sb.Append(" REFERENCES "); ForeignKeyConstraintSchema fk = constraint as ForeignKeyConstraintSchema; string tableName; if (fk.ReferenceTableName.IndexOf('.') > 0) { tableName = fk.ReferenceTableName.Substring(fk.ReferenceTableName.IndexOf('.') + 1); } else { tableName = fk.ReferenceTableName; } sb.Append(tableName); sb.Append(' '); if (fk.ReferenceColumns != null) { sb.Append(GetColumnsString(fk.ReferenceColumns, true)); } sb.Append(Environment.NewLine); sb.Append(" ON DELETE "); sb.Append(GetConstraintActionString(fk.DeleteAction)); sb.Append(Environment.NewLine); sb.Append(" ON UPDATE "); sb.Append(GetConstraintActionString(fk.UpdateAction)); break; default: throw new NotImplementedException(); } return(sb.ToString()); }