private void ColumnToggled(object sender, EventArgs args) { TreeIter iter; if (listPK.Selection.GetSelected(out iter)) { bool first = true; StringBuilder sb = new StringBuilder(); ConstraintSchema constraint = (ConstraintSchema)store.GetValue(iter, colObjIndex); constraint.Columns.Clear(); foreach (ColumnSchema column in columnSelecter.CheckedColumns) { constraint.Columns.Add(column); if (first) { first = false; } else { sb.Append(','); } sb.Append(column.Name); } store.SetValue(iter, colColumnsIndex, sb.ToString()); EmitContentChanged(); OnPrimaryKeyChanged(); } }
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); }
public override void BuildNode(ITreeBuilder treeBuilder, object dataObject, ref string label, ref Gdk.Pixbuf icon, ref Gdk.Pixbuf closedIcon) { ConstraintSchema constraint = dataObject as ConstraintSchema; label = constraint.Name; icon = Context.GetIcon("md-db-constraint"); //TODO: icon based on constraint type }
/// <summary> /// Generates a property to get a parent row. /// </summary> /// <param name="keyrefSchema">The foreign key that references the parent table.</param> public TableIndexField(ConstraintSchema constraintSchema) { // // The DepartmentKey Index // private DepartmentKeyIndex indexDepartmentKey; this.Type = new CodeTypeReference(string.Format("{0}Index", constraintSchema.Name)); this.Name = string.Format("index{0}", constraintSchema.Name); this.Comments.Add(new CodeCommentStatement(string.Format("The {0} Index", constraintSchema.Name))); }
public override void BuildNode(ITreeBuilder treeBuilder, object dataObject, NodeInfo nodeInfo) { ConstraintSchema constraint = dataObject as ConstraintSchema; nodeInfo.Label = constraint.Name; nodeInfo.Icon = Context.GetIcon("md-db-constraint"); //TODO: icon based on constraint type }
/// <summary> /// Generates the method used to find a record given a unique key. /// </summary> /// <param name="tableSchema">The table to which this method belongs.</param> /// <param name="constraintSchema">The constraint used to find the record.</param> public FindByMethod(TableSchema tableSchema, ConstraintSchema constraintSchema) { // This construct is used several times to generate the method. string rowTypeName = string.Format("{0}Row", tableSchema.Name); // /// <summary> // /// Finds a row in the Department table containing the key elements. // /// </summary> // /// <param name="departmentId">The DepartmentId element of the key.</param> // /// <returns>A DepartmentRow that contains the key elements, or null if there is no match.</returns> // public DepartmentRow FindByDepartmentId(int departmentId) // { // // Use the index to find a row containing the key elements. // try // { // this.ReaderWriterLock.AcquireReaderLock(System.Threading.Timeout.Infinite); // return ((DepartmentRow)(this.Rows.Find(new object[] { // departmentId}))); // } // finally // { // this.ReaderWriterLock.ReleaseReaderLock(); // } // } this.Comments.Add(new CodeCommentStatement("<summary>", true)); this.Comments.Add(new CodeCommentStatement(string.Format("Finds a row in the {0} table containing the key elements.", tableSchema.Name), true)); this.Comments.Add(new CodeCommentStatement("</summary>", true)); foreach (ColumnSchema columnSchema in constraintSchema.Fields) { string camelCaseColumnName = string.Format("{0}", columnSchema.Name[0].ToString().ToLower() + columnSchema.Name.Remove(0, 1)); this.Comments.Add(new CodeCommentStatement(string.Format("<param name=\"{0}\">The {1} element of the key.</param>", camelCaseColumnName, columnSchema.Name), true)); } this.Comments.Add(new CodeCommentStatement("<returns>A DepartmentRow that contains the key elements, or null if there is no match.</returns>", true)); this.Attributes = MemberAttributes.Public | MemberAttributes.Final; this.ReturnType = new CodeTypeReference(rowTypeName); this.Name = "FindBy"; List <CodeExpression> findByArguments = new List <CodeExpression>(); foreach (ColumnSchema columnSchema in constraintSchema.Fields) { string camelCaseColumnName = string.Format("{0}", columnSchema.Name[0].ToString().ToLower() + columnSchema.Name.Remove(0, 1)); this.Name += columnSchema.Name; this.Parameters.Add(new CodeParameterDeclarationExpression(columnSchema.DataType, camelCaseColumnName)); findByArguments.Add(new CodeArgumentReferenceExpression(camelCaseColumnName)); } CodeMethodInvokeExpression codeMethodInvokeExpression = new CodeMethodInvokeExpression(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), "Rows"), "Find", new CodeArrayCreateExpression(typeof(System.Object), findByArguments.ToArray())); CodeTryCatchFinallyStatement tryCatchFinallyStatement = new CodeTryCatchFinallyStatement(); tryCatchFinallyStatement.TryStatements.Add(new CodeCommentStatement("The table must be locked to use the index to find a row containing the key elements.")); tryCatchFinallyStatement.TryStatements.Add(new CodeMethodInvokeExpression(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), "ReaderWriterLock"), "AcquireReaderLock", new CodeFieldReferenceExpression(new CodeTypeReferenceExpression(typeof(System.Threading.Timeout)), "Infinite"))); tryCatchFinallyStatement.TryStatements.Add(new CodeMethodReturnStatement(new CodeCastExpression(rowTypeName, codeMethodInvokeExpression))); tryCatchFinallyStatement.FinallyStatements.Add(new CodeCommentStatement("The table can be accessed by other threads once the row is found.")); tryCatchFinallyStatement.FinallyStatements.Add(new CodeMethodInvokeExpression(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), "ReaderWriterLock"), "ReleaseReaderLock")); this.Statements.Add(tryCatchFinallyStatement); }
protected virtual string GetConstraintString(ConstraintSchema constraint) { if (constraint.ConstraintType == ConstraintType.Check) { return(String.Format("CHECK ({0})", (constraint as CheckConstraintSchema).Source)); } StringBuilder sb = new StringBuilder(); sb.Append("CONSTRAINT "); sb.Append(constraint.Name); sb.Append(' '); switch (constraint.ConstraintType) { case ConstraintType.PrimaryKey: sb.Append("PRIMARY KEY "); sb.Append(GetColumnsString(constraint.Columns, true)); break; case ConstraintType.Unique: sb.Append("UNIQUE KEY "); 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.ReferenceTableName); 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()); }
public override ConstraintSchemaCollection GetColumnConstraints(TableSchema table, ColumnSchema column) { ConstraintSchemaCollection constraints = new ConstraintSchemaCollection(); using (IPooledDbConnection conn = connectionPool.Request()) { using (IDbCommand command = conn.CreateCommand(String.Format("DESCRIBE {0}", table.Name))) { try { 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 = CreatePrimaryKeyConstraintSchema("pk_" + column.Name); } else if (key.Contains("UNI")) { constraint = CreateUniqueConstraintSchema("uni_" + column.Name); } else { continue; } constraint.IsColumnConstraint = true; constraint.OwnerName = 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) { StringBuilder sb = new StringBuilder(); sb.Append("CONSTRAINT "); sb.Append(constraint.Name); sb.Append(' '); switch (constraint.ConstraintType) { case ConstraintType.PrimaryKey: sb.Append("PRIMARY KEY "); sb.Append(GetColumnsString(constraint.Columns, true)); break; case ConstraintType.Unique: sb.Append("UNIQUE "); sb.Append(GetColumnsString(constraint.Columns, true)); break; case ConstraintType.Check: sb.Append("CHECK ("); sb.Append((constraint as CheckConstraintSchema).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; sb.Append(fk.ReferenceTableName); sb.Append(' '); if (fk.ReferenceColumns != null) { sb.Append(GetColumnsString(fk.ReferenceColumns, true)); } break; default: throw new NotImplementedException(); } return(sb.ToString()); }
/// <summary> /// Generates a property to get a parent row. /// </summary> /// <param name="keyrefSchema">The foreign key that references the parent table.</param> public KeyProperty(ConstraintSchema constraintSchema) { // /// <summary> // /// Gets the DepartmentKey index on the Department table. // /// </summary> // public DepartmentKeyIndex DepartmentKey // { // get // { // return this.indexDepartmentKey; // } // } this.Comments.Add(new CodeCommentStatement("<summary>", true)); this.Comments.Add(new CodeCommentStatement(string.Format("Gets the {0} index on the {1} table.", constraintSchema.Name, constraintSchema.Selector.Name), true)); this.Comments.Add(new CodeCommentStatement("</summary>", true)); this.Attributes = MemberAttributes.Public | MemberAttributes.Final; this.Type = new CodeTypeReference(string.Format("{0}Index", constraintSchema.Name)); this.Name = constraintSchema.Name; this.GetStatements.Add(new CodeMethodReturnStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), string.Format("index{0}", constraintSchema.Name)))); }
protected virtual string GetConstraintString (ConstraintSchema constraint) { if (constraint.ConstraintType == ConstraintType.Check) return String.Format ("CHECK ({0})", (constraint as CheckConstraintSchema).Source); StringBuilder sb = new StringBuilder (); sb.Append ("CONSTRAINT "); sb.Append (constraint.Name); sb.Append (' '); switch (constraint.ConstraintType) { case ConstraintType.PrimaryKey: sb.Append ("PRIMARY KEY "); sb.Append (GetColumnsString (constraint.Columns, true)); break; case ConstraintType.Unique: sb.Append ("UNIQUE KEY "); 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.ReferenceTableName); 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 (); }
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.ReferenceTableName); 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 ();
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(); } }
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()); }
//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); }
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 (); }