public virtual PrimaryKeyConstraintSchema GetNewPrimaryKeyConstraintSchema(string name) { PrimaryKeyConstraintSchema schema = new PrimaryKeyConstraintSchema(this); schema.Name = name; return(schema); }
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 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); }
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;
public PrimaryKeyConstraintSchema (PrimaryKeyConstraintSchema constraint) : base (constraint) { }
private void AddConstraint (PrimaryKeyConstraintSchema pk) { store.AppendValues (pk.Name, String.Empty, pk); }
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); }
public PrimaryKeyConstraintSchema(PrimaryKeyConstraintSchema constraint) : base(constraint) { }
public virtual PrimaryKeyConstraintSchema GetNewPrimaryKeyConstraintSchema (string name) { PrimaryKeyConstraintSchema schema = new PrimaryKeyConstraintSchema (this); schema.Name = name; return schema; }
//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); }
//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 void TogglePrimaryKey() { PrimaryKeyConstraintSchema pkConstraint = null; PrimaryKeyConstraintSchema pkColumnConstraint = null; PrimaryKeyConstraintSchema pkTemp = null; bool tableLevel=false; //Table have table, columna level or none primary key if (columnModel.Parent is TableSchema) { foreach (ConstraintSchema item in (columnModel.Parent as TableSchema).Constraints) { if (item is PrimaryKeyConstraintSchema) { pkTemp = item as PrimaryKeyConstraintSchema; foreach (ColumnSchema column in item.Columns) { if (column.Name == columnModel.Name) { pkConstraint = item as PrimaryKeyConstraintSchema; } } } } //Table have a column level primary key int count=0; foreach (ColumnSchema column in (columnModel.Parent as TableSchema).Columns) { foreach (ConstraintSchema constraint in column.Constraints) { if (constraint is PrimaryKeyConstraintSchema) { if(pkColumnConstraint==null && column.Name!=columnModel.Name){ pkColumnConstraint=constraint as PrimaryKeyConstraintSchema; count++; } } } } if(count>1) throw new NotImplementedException (); } if(pkTemp!=null && pkTemp.Columns.Count == 0){ //A pk without columns a table level isn't valid (columnModel.Parent as TableSchema).Constraints.Remove (pkTemp); pkTemp=null; } if(pkTemp!=null || pkConstraint!=null) tableLevel=true; if(tableLevel && pkColumnConstraint!=null) throw new NotImplementedException (); if(tableLevel){ //Remove this column because belong yet to table level pk if(pkConstraint!=null){ pkConstraint.Columns.Remove (columnModel); primaryKey = false; Console.WriteLine("Remove a table level this column"); if (pkConstraint.Columns.Count == 1){ //only one column left then change to column level constraint (columnModel.Parent as TableSchema).Constraints.Remove (pkConstraint); pkConstraint.Columns[0].Constraints.Add(pkConstraint); Console.WriteLine("Last column remain move from table level to column level a:"+pkConstraint.Columns[0].Name); } }else{ //Add this column because don't belong yet primaryKey = true; Console.WriteLine("Addforce a table level this column " + pkTemp.Columns.Count ); pkTemp.Columns.Add (ColumnModel); pkTemp = null; } }else{ if (pkConstraint == null && pkTemp == null) { Console.WriteLine("Taban ambos NULL en one pk column level"); pkConstraint = columnModel.Constraints.GetConstraint (ConstraintType.PrimaryKey) as PrimaryKeyConstraintSchema; if (pkConstraint != null) { Console.WriteLine("Quito uno solo a nivel de column"); primaryKey = false; ColumnModel.Constraints.Remove (pkConstraint); } else { if(pkColumnConstraint!=null){ Console.WriteLine("Constraint a nivel de columna existe en otra col lo convierto a nivel de tabla"); primaryKey = true; if(pkColumnConstraint.Columns.Count==1) pkColumnConstraint.Columns[0].Constraints.Remove ((pkColumnConstraint as ConstraintSchema)); else throw new NotImplementedException (); pkColumnConstraint.Columns.Add (ColumnModel); (columnModel.Parent as TableSchema).Constraints.Add (pkColumnConstraint); }else{ Console.WriteLine("Nuevo Constraint a nivel de esta columna"); primaryKey = true; pkConstraint = new PrimaryKeyConstraintSchema (ColumnModel.SchemaProvider); pkConstraint.Columns.Add (ColumnModel); ColumnModel.Constraints.Add (pkConstraint); } } }else{ throw new NotImplementedException (); } } Console.WriteLine("Tipos dentro de la figura"); foreach(IFigure f in (tableFigureOwner as TableFigure).FiguresEnumerator){ Console.WriteLine("TIPO: " + f.GetType()); } //TODO: fix when multiple fk between same tables if(tableFigureOwner is TableFigure){ //TODO: VERY IMPORTANT KIND OPTIONALITY IS RIGHT NOW FIXED, FIND A WAY OF GET REAL VALUE (tableFigureOwner as TableFigure).RefreshRelationships(true,false,tableFigureOwner as TableFigure,kindOptionality.optional,false,null); }else if (tableFigureOwner==null){ throw new NotImplementedException (); } }
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;