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 AddConstraint(PrimaryKeyConstraintSchema pk) { System.Text.StringBuilder pk_cols = new System.Text.StringBuilder(); foreach (ColumnSchema col in pk.Columns) { if (pk_cols.Length > 0) { pk_cols.Append(","); } pk_cols.Append(col.Name); } TreeIter iter = store.AppendValues(pk.Name, pk_cols.ToString(), pk); }
protected virtual void AddClicked(object sender, System.EventArgs e) { PrimaryKeyConstraintSchema pk = schemaProvider.GetNewPrimaryKeyConstraintSchema("pk_new"); int index = 1; while (constraints.Contains(pk.Name)) { pk.Name = "pk_new" + (index++); } constraints.Add(pk); AddConstraint(pk); EmitContentChanged(); }
protected virtual void AddClicked(object sender, System.EventArgs e) { PrimaryKeyConstraintSchema pk = schemaProvider.CreatePrimaryKeyConstraintSchema( string.Concat( table.Name, "_", AddinCatalog.GetString("pk_new"))); int index = 1; while (constraints.Contains(pk.Name)) { pk.Name = string.Concat(table.Name, "_", AddinCatalog.GetString("pk_new"), (index++).ToString()); } constraints.Add(pk); AddConstraint(pk); EmitContentChanged(); }
protected virtual void RemoveClicked(object sender, System.EventArgs e) { TreeIter iter; if (listPK.Selection.GetSelected(out iter)) { PrimaryKeyConstraintSchema pk = store.GetValue(iter, colObjIndex) as PrimaryKeyConstraintSchema; if (MessageService.Confirm( AddinCatalog.GetString("Are you sure you want to remove constraint '{0}'?", pk.Name), AlertButton.Remove )) { store.Remove(ref iter); constraints.Remove(pk); EmitContentChanged(); } } }
public virtual void FillSchemaObjects() { TreeIter iter; if (store.GetIterFirst(out iter)) { do { PrimaryKeyConstraintSchema pk = store.GetValue(iter, colObjIndex) as PrimaryKeyConstraintSchema; pk.Name = store.GetValue(iter, colNameIndex) as string; string colstr = store.GetValue(iter, colColumnsIndex) as string; string[] cols = colstr.Split(','); foreach (string col in cols) { ColumnSchema column = columns.Search(col); pk.Columns.Add(column); } table.Constraints.Add(pk); } while (store.IterNext(ref iter)); } }
public virtual void FillSchemaObjects() { TreeIter iter; if (storeColumns.GetIterFirst(out iter)) { do { ColumnSchema column = storeColumns.GetValue(iter, colObjIndex) as ColumnSchema; column.Name = storeColumns.GetValue(iter, colNameIndex) as string; column.DataTypeName = storeColumns.GetValue(iter, colTypeIndex) as string; column.DataType.LengthRange.Default = int.Parse(storeColumns.GetValue(iter, colLengthIndex) as string); column.IsNullable = (bool)storeColumns.GetValue(iter, colNullableIndex); column.Comment = storeColumns.GetValue(iter, colCommentIndex) as string; if ((bool)storeColumns.GetValue(iter, colPKIndex)) { PrimaryKeyConstraintSchema pk = schemaProvider.GetNewPrimaryKeyConstraintSchema("pk_" + column.Name); column.Constraints.Add(pk); } } while (storeColumns.IterNext(ref iter)); } }
public override ConstraintSchemaCollection GetTableConstraints (TableSchema table) { ConstraintSchemaCollection constraints = new ConstraintSchemaCollection (); using (IPooledDbConnection conn = connectionPool.Request ()) { using (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 (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) { // Don't raise error, if the table doesn't exists return an empty collection } finally { conn.Release (); } } } return constraints;
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 PkToggled(object sender, ToggledArgs args) { TreeIter iter; if (storeColumns.GetIterFromString(out iter, args.Path)) { bool val = (bool)storeColumns.GetValue(iter, colPKIndex); storeColumns.SetValue(iter, colPKIndex, !val); if (val) { // Remove Constraint ColumnSchema column = storeColumns.GetValue(iter, colObjIndex) as ColumnSchema; ConstraintSchema delConstraint = null; foreach (ConstraintSchema c in constraints) { if (c is PrimaryKeyConstraintSchema) { foreach (ColumnSchema col in c.Columns) { if (col.Name == column.Name) { c.Columns.Remove(col); delConstraint = c; break; } } } } // If PK doesn't have any columns, delete it. if (delConstraint != null) { if (delConstraint.Columns.Count < 1) { constraints.Remove(delConstraint); } } } else { // Add Constraint ColumnSchema column = storeColumns.GetValue(iter, colObjIndex) as ColumnSchema; // Add the column for an existing PK foreach (ConstraintSchema c in constraints) { if (c is PrimaryKeyConstraintSchema) { c.Columns.Add(column); // Fire the Primary Key Changed Event to tell the other widget that "Primary Key Constraint" // are changed in the Column Editor. OnPrimaryKeyChanged(this, new EventArgs()); EmitContentChanged(); return; } } PrimaryKeyConstraintSchema pk = schemaProvider.CreatePrimaryKeyConstraintSchema(string.Concat( table.Name, "_", AddinCatalog.GetString("pk_new") )); pk.Columns.Add(column); constraints.Add(pk); } OnPrimaryKeyChanged(this, new EventArgs()); EmitContentChanged(); } }
/// <summary> /// Get a collection of constraints within a a table. /// </summary> public override ConstraintSchema[] GetTableConstraints(TableSchema table) { if (IsOpen == false && Open () == false) throw new InvalidOperationException ("Invalid connection"); ArrayList collection = new ArrayList (); NpgsqlCommand command = new NpgsqlCommand (); command.Connection = connection; command.CommandText = 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); NpgsqlDataReader r = command.ExecuteReader (); while (r.Read ()) { ConstraintSchema constraint = null; // XXX: Add support for Check constraints. switch (r.GetString(2)) { case "f": string match = @".*REFERENCES (.+)\(.*\).*"; constraint = new ForeignKeyConstraintSchema (); 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 (); break; case "p": default: constraint = new PrimaryKeyConstraintSchema (); break; } constraint.Name = r.GetString (0); constraint.Definition = r.GetString (1); collection.Add (constraint); } r.Close (); return (ConstraintSchema[]) collection.ToArray (typeof(ConstraintSchema)); }
private void AddConstraint(PrimaryKeyConstraintSchema pk) { store.AppendValues(pk.Name, String.Empty, pk); }
//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); }
/// <summary> /// Get a collection of constraints within a a table. /// </summary> public override ConstraintSchema[] GetTableConstraints(TableSchema table) { if (IsOpen == false && Open () == false) throw new InvalidOperationException ("Invalid connection."); ArrayList collection = new ArrayList (); OracleCommand command = new OracleCommand (); command.Connection = connection; command.CommandText = "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'"; OracleDataReader r = command.ExecuteReader (); while (r.Read ()) { ConstraintSchema constraint = null; switch (r.GetString(4)) { case "P": default: constraint = new PrimaryKeyConstraintSchema(); break; } constraint.Name = r.GetString (3); constraint.Definition = ""; collection.Add (constraint); } return (ConstraintSchema[]) collection.ToArray (typeof(ConstraintSchema)); }
//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; }