public ColumnSchema (ColumnSchema column) : base (column) { parent = column.parent; //do not clone, this would create an infinite loop dataType = column.dataType; hasDefaultValue = column.hasDefaultValue; defaultValue = column.defaultValue; nullable = column.nullable; position = column.position; constraints = new ConstraintSchemaCollection (column.constraints); }
//white space allow later to allow showing datatype at first column creation at newTable public AbstractColumnFigure(ColumnSchema column, IFigure owner) : base(column.Name+" ") { columnModel = column; tableFigureOwner = owner; columnDataType = null; primaryIcon = IconFactory.GetIcon ("Resources.primarykey.png"); mandatoryIcon = IconFactory.GetIcon ("Resources.mandatory.png"); optionalIcon = IconFactory.GetIcon ("Resources.optional.png"); fkUkIcon = IconFactory.GetIcon ("Resources.foreign_uk.png"); fkIcon = IconFactory.GetIcon ("Resources.foreign.png"); Initialize (); }
protected virtual ColumnSchema GetTableColumn(DataRow row, TableSchema table) { ColumnSchema schema = new ColumnSchema(this, table); schema.SchemaName = table.SchemaName; schema.Name = GetRowString(row, tableColumnItemStrings[0]); schema.DefaultValue = GetRowString(row, tableColumnItemStrings[1]); schema.HasDefaultValue = GetRowBool(row, tableColumnItemStrings[2]); schema.IsNullable = GetRowBool(row, tableColumnItemStrings[3]); schema.Position = GetRowInt(row, tableColumnItemStrings[4]); schema.DataTypeName = GetRowString(row, tableColumnItemStrings[7]); schema.DataType.ScaleRange.Default = GetRowInt(row, tableColumnItemStrings[6]); schema.DataType.PrecisionRange.Default = GetRowInt(row, tableColumnItemStrings[5]); return(schema); }
private ICollection <ColumnSchema> GetTableOrViewColumns(string name) { CheckConnectionState(); List <ColumnSchema> columns = new List <ColumnSchema> (); IDbCommand command = connectionProvider.CreateCommand( "SELECT r.RDB$FIELD_NAME AS field_name, r.RDB$DESCRIPTION AS field_description, " + "r.RDB$DEFAULT_VALUE AS field_default_value, r.RDB$NULL_FLAG AS field_not_null_constraint, " + "f.RDB$FIELD_LENGTH AS field_length, f.RDB$FIELD_PRECISION AS field_precision, " + "f.RDB$FIELD_SCALE AS field_scale, " + "CASE f.RDB$FIELD_TYPE WHEN 261 THEN 'BLOB' WHEN 14 THEN 'CHAR' WHEN 40 THEN 'CSTRING' " + "WHEN 11 THEN 'D_FLOAT' WHEN 27 THEN 'DOUBLE' WHEN 10 THEN 'FLOAT' WHEN 16 THEN 'INT64' " + "WHEN 8 THEN 'INTEGER' WHEN 9 THEN 'QUAD' WHEN 7 THEN 'SMALLINT' WHEN 12 THEN 'DATE' " + "WHEN 13 THEN 'TIME' WHEN 35 THEN 'TIMESTAMP' WHEN 37 THEN 'VARCHAR' ELSE 'UNKNOWN' " + "END AS field_type, f.RDB$FIELD_SUB_TYPE AS field_subtype, " + "coll.RDB$COLLATION_NAME AS field_collation, cset.RDB$CHARACTER_SET_NAME AS field_charset " + "FROM RDB$RELATION_FIELDS r LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME " + "LEFT JOIN RDB$COLLATIONS coll ON f.RDB$COLLATION_ID = coll.RDB$COLLATION_ID " + "LEFT JOIN RDB$CHARACTER_SETS cset ON f.RDB$CHARACTER_SET_ID = cset.RDB$CHARACTER_SET_ID " + "WHERE r.RDB$RELATION_NAME='" + name + "' ORDER BY r.RDB$FIELD_POSITION;" ); using (command) { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { ColumnSchema column = new ColumnSchema(this); column.Name = r.GetString(0); column.DataTypeName = r.GetString(8); column.NotNull = (!r.IsDBNull(3) && r.GetInt32(3) == 1); column.Default = r.GetString(2); column.Comment = r.GetString(1); column.OwnerName = name; column.Length = r.GetInt32(4); column.Precision = r.GetInt32(5); column.Scale = r.GetInt32(6); columns.Add(column); } r.Close(); }; connectionProvider.Close(command.Connection); } return(columns); }
public override ColumnSchemaCollection GetViewColumns(ViewSchema view) { ColumnSchemaCollection columns = new ColumnSchemaCollection(); IPooledDbConnection conn = connectionPool.Request(); IDbCommand command = conn.CreateCommand( "SELECT attname, typname, attlen, attnotnull " + "FROM " + " pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_attrdef adef " + " ON a.attrelid=adef.adrelid " + " AND a.attnum=adef.adnum " + " LEFT JOIN pg_catalog.pg_type t ON a.atttypid=t.oid " + "WHERE " + " a.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname='" + view.Name + "') " + " AND a.attnum > 0 AND NOT a.attisdropped " + " ORDER BY a.attnum;" ); try { using (command) { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { ColumnSchema column = new ColumnSchema(this, view); column.Name = r.GetString(0); column.DataTypeName = r.GetString(1); column.SchemaName = view.SchemaName; column.IsNullable = r.GetBoolean(3); column.DataType.LengthRange.Default = r.GetInt32(2); columns.Add(column); } r.Close(); }; } } catch (Exception e) { QueryService.RaiseException(e); } conn.Release(); return(columns); }
public virtual string GetCreateString(ColumnSchema column) { if (createFormat == null) { return(name); } else { if (createParameters != null) { string[] parameters = createParameters.Split(','); string[] chunks = new string[parameters.Length]; int index = 0; foreach (string param in parameters) { string chunk = param.ToLower().Trim(); if (chunk.Contains("length") || chunk.Contains("size")) { chunks[index] = column.DataType.LengthRange.Default.ToString(); } else if (chunk.Contains("scale")) { chunks[index] = column.DataType.ScaleRange.Default.ToString(); } else if (chunk.Contains("precision")) { chunks[index] = column.DataType.PrecisionRange.Default.ToString(); } else { chunks[index] = String.Empty; } index++; } return(String.Format(System.Globalization.CultureInfo.InvariantCulture, createFormat, chunks)); } return(createFormat); } }
public override ColumnSchemaCollection GetViewColumns(ViewSchema view) { ColumnSchemaCollection columns = new ColumnSchemaCollection(); IPooledDbConnection conn = connectionPool.Request(); IDbCommand command = conn.CreateCommand( "SELECT * FROM \"" + view.Name + "\" WHERE 1 = 0" ); try { using (command) { using (IDataReader r = command.ExecuteReader()) { for (int i = 0; i < r.FieldCount; i++) { ColumnSchema column = new ColumnSchema(this, view); column.Name = r.GetName(i); column.DataTypeName = r.GetDataTypeName(i); column.DefaultValue = ""; column.Definition = ""; column.OwnerName = view.OwnerName; column.SchemaName = view.OwnerName; columns.Add(column); } r.Close(); } } } catch (Exception e) { QueryService.RaiseException(e); } conn.Release(); return(columns); }
public ColumnContainer (ColumnSchema column) { if (column == null) throw new ArgumentNullException ("column"); this.column = column; }
//TODO: context really needed in every table (I think not then remove later) // this should be available fro all tables of the same diagram without taking care if new or altered /* public TableModel (string name, DatabaseConnectionContext context, ISchemaProvider schemaProvider) { /*tableName = name; tableContext = context; tableSchemaProvider = schemaProvider; tableSchema = tableSchemaProvider.CreateTableSchema (name); alteredTable=false; newTable=false; Initialize (); //TODO: delete this only for test purpose indexes.Add (new Index ("DummyIndex2")); triggers.Add (new Trigger ("DummyTrigger1")); } */ public TableModel(string name, DatabaseConnectionContext context, ISchemaProvider schemaProvider, bool create) { //TableSchema newSchema; modelTableName = name; //TODO: remove this attribute use table model newTable = create; alteredTable = false; tableContext = context; tableSchemaProvider = schemaProvider; tableSchema = schemaProvider.CreateTableSchema (name); Initialize (); //Add a first column if (create) { ColumnSchema columnSchema = new ColumnSchema (tableSchemaProvider, tableSchema, "newColumn"); if (storeTypes.Count > 0) { columnSchema.DataTypeName = storeTypes.Keys[0]; columns.Add (new ColumnFigure (columnSchema, storeTypes, null)); tableSchema.Columns.Add (columnSchema); } else { throw new NotImplementedException (); } } /*ColumnSchema column = new ColumnSchema (schemaProvider, tableSchema, name);*/ //TODO: delete this only for test purpose indexes.Add (new Index ("NotImplementedYet")); triggers.Add (new Trigger ("NotImplementedYet")); System.Console.WriteLine ("Tiene Xs: " + tableSchema.Columns.Count); System.Console.WriteLine (schemaProvider.GetTableCreateStatement (tableSchema)); }
public virtual ColumnSchema GetNewColumnSchema(string name, ISchema parent) { ColumnSchema schema = new ColumnSchema(this, parent, name); return(schema); }
public override ICollection<ColumnSchema> GetViewColumns (ViewSchema view) { CheckConnectionState (); List<ColumnSchema> columns = new List<ColumnSchema> (); IDbCommand command = connectionProvider.CreateCommand ( "SELECT * " + " FROM " + view.Name + " WHERE 1 = 0" ); using (command) { using (IDataReader r = command.ExecuteReader()) { while (r.Read ()) { for (int i = 0; i < r.FieldCount; i++) { ColumnSchema column = new ColumnSchema (this); column.Name = r.GetString (0); column.DataTypeName = r.GetString (1); column.SchemaName = view.SchemaName; column.NotNull = r.GetBoolean (3); column.Length = r.GetInt32 (2); columns.Add (column); } } r.Close (); }; connectionProvider.Close (command.Connection); } return columns;
public ColumnNode (DatabaseConnectionContext context, ColumnSchema column) : base (context) { if (column == null) throw new ArgumentNullException ("column"); this.column = column; }
protected virtual ColumnSchema GetTableIndexColumn(DataRow row, TableSchema table, IndexSchema index) { ColumnSchema schema = new ColumnSchema(this, table); return(schema); }
public virtual ColumnSchema CreateColumnSchema (ISchema parent, string name) { ColumnSchema schema = new ColumnSchema (this, parent); schema.Name = name; return schema; }
public ColumnFigure addNewColumn(IFigure owner) { const string initialName="newColumn"; string usedName=initialName; int number=1; bool canUseit; do{ canUseit=true; foreach(ColumnSchema cs in TableSchema.Columns){ if (cs.Name.ToLower().CompareTo(usedName.ToLower())==0){ canUseit=false; usedName=initialName+number; number++; break; } } }while(!canUseit); ColumnSchema columnSchema = new ColumnSchema (tableSchemaProvider, tableSchema, usedName); ColumnFigure newColumn = null; if (storeTypes.Count > 0) { columnSchema.DataTypeName = storeTypes.Keys[0]; newColumn = new ColumnFigure (columnSchema, storeTypes, owner); columns.Add (newColumn); TableSchema.Columns.Add (columnSchema); } else { throw new NotImplementedException (); } return newColumn; }
public override ICollection<ColumnSchema> GetTableColumns (TableSchema table) { CheckConnectionState (); List<ColumnSchema> columns = new List<ColumnSchema> (); IDbCommand command = connectionProvider.CreateCommand ( "select su.name as owner, so.name as table_name, sc.name as column_name, " + " st.name as date_type, sc.length as column_length, " + " sc.prec as data_preceision, sc.scale as data_scale, " + " 0 as isnullable, sc.colid as column_id " + "from dbo.syscolumns sc, dbo.sysobjects so, " + " dbo.systypes st, dbo.sysusers su " + "where sc.id = so.id " + "and so.type in ('U','S') " + "and so.name = '" + table.Name + "' " + "and su.name = '" + table.OwnerName + "' " + "and su.uid = so.uid " + "and sc.usertype = st.usertype " + "order by sc.colid" ); using (command) { using (IDataReader r = command.ExecuteReader()) { while (r.Read ()) { ColumnSchema column = new ColumnSchema (this); column.Name = r.GetString (2); column.DataTypeName = r.GetString (3); column.OwnerName = table.OwnerName; column.NotNull = r.GetInt32 (7) == 8 ? true : false; column.Length = GetCheckedInt32 (r, 4); column.Precision = GetCheckedInt32 (r, 5); column.Scale = GetCheckedInt32 (r, 6); StringBuilder sb = new StringBuilder (); sb.AppendFormat ("{0} {1}{2}", column.Name, column.DataTypeName, (column.Length > 0) ? ("(" + column.Length + ")") : ""); sb.AppendFormat (" {0}", column.NotNull ? "NOT NULL" : "NULL"); if (column.Default.ToString ().Length > 0) sb.AppendFormat (" DEFAULT {0}", column.Default.ToString ()); column.Definition = sb.ToString (); columns.Add (column); } r.Close (); } connectionProvider.Close (command.Connection); } return columns;
public override ICollection<ColumnSchema> GetProcedureColumns (ProcedureSchema procedure) { CheckConnectionState (); List<ColumnSchema> columns = new List<ColumnSchema> (); IDbCommand command = connectionProvider.CreateCommand ( "sp_sproc_columns" ); SybaseParameter owner = new SybaseParameter ("@procedure_owner", procedure.OwnerName); SybaseParameter name = new SybaseParameter ("@procedure_name", procedure.Name); command.Parameters.Add (owner); command.Parameters.Add (name); using (command) { using (IDataReader r = command.ExecuteReader()) { while (r.Read ()) { ColumnSchema column = new ColumnSchema (this); column.Name = (string)r["COLUMN_NAME"]; column.DataTypeName = (string)r["TYPE_NAME"]; columns.Add (column); } r.Close (); } connectionProvider.Close (command.Connection); } return columns;
//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 GetColumnConstraints(TableSchema table, ColumnSchema column) { return(GetConstraints(table, column)); }
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); }
public void changeFkColumnDatatype(ColumnSchema newColumn) { foreach (AbstractColumnFigure cf in Model.columns) { if (cf is ColumnFkFigure) { if ((cf as ColumnFkFigure).sameForeignKey (newColumn.Parent.Name, newColumn.Name)) { cf.ColumnModel.DataTypeName = newColumn.DataTypeName; (cf as ColumnFkFigure).fkDataType = newColumn.DataType; cf.Text = (cf as ColumnFkFigure).originalColumnName + "_" + (cf as ColumnFkFigure).originalTableName + "_fk"; //to force redraw of column name } } } }
protected virtual ColumnSchema GetTableColumn (DataRow row, TableSchema table) { ColumnSchema schema = new ColumnSchema (this, table); schema.SchemaName = table.SchemaName; schema.Name = GetRowString (row, tableColumnItemStrings[0]); schema.DefaultValue = GetRowString (row, tableColumnItemStrings[1]); schema.HasDefaultValue = GetRowBool (row, tableColumnItemStrings[2]); schema.IsNullable = GetRowBool (row, tableColumnItemStrings[3]); schema.Position = GetRowInt (row, tableColumnItemStrings[4]); schema.DataTypeName = GetRowString (row, tableColumnItemStrings[7]); schema.DataType.ScaleRange.Default = GetRowInt (row, tableColumnItemStrings[6]); schema.DataType.PrecisionRange.Default = GetRowInt (row, tableColumnItemStrings[5]); return schema; }
//This functions works as dispatcher to correct one functions //TODO: improve this implementation of data interchange to propagate changes public void Update(bool refresh, bool changeConnection, TableFigure notifier, kindOptionality optionality, bool changeDatatype, ColumnSchema notifierColumn) { if (refresh) { Console.WriteLine ("La tabla " + this.Model.Name + " ya fue Avisada de REFRESCAR!!!!!!!!!!!!!!!!!!!!!!!!!!! de la tabla:" + notifier.Model.Name); refreshForeignKeys (notifier, optionality); } else if (changeConnection) Console.WriteLine ("La tabla " + this.Model.Name + " ya fue Avisada de CAMBIO DE CONEXION!!!!!!!!!!!!!!!!!!!!!!!!!!! de la tabla:" + notifier.Model.Name); else if (changeDatatype) { changeFkColumnDatatype (notifierColumn); Console.WriteLine ("La tabla " + this.Model.Name + " ya fue Avisada de Cambio de tipo de dato en columna !!!!!!!!!!!!!!!!!!!!!!!!!!!" + notifierColumn.Name); } else if (!changeConnection && !refresh && !changeDatatype) { Console.WriteLine ("La tabla " + this.Model.Name + " ya fue Avisada de ELIMINAR FK !!!!!!!!!!!!!!!!!!!!!!!!!!! de la tabla:" + notifier.Model.Name); removeAllForeignKeysFrom(notifier); } }
protected virtual ColumnSchema GetViewColumn (DataRow row, ViewSchema view) { ColumnSchema schema = new ColumnSchema (this, view); schema.SchemaName = view.SchemaName; schema.Name = GetRowString (row, viewColumnItemStrings[0]); return schema; }
private ICollection<ColumnSchema> GetTableOrViewColumns (string name) { CheckConnectionState (); List<ColumnSchema> columns = new List<ColumnSchema> (); IDbCommand command = connectionProvider.CreateCommand ( "SELECT r.RDB$FIELD_NAME AS field_name, r.RDB$DESCRIPTION AS field_description, " + "r.RDB$DEFAULT_VALUE AS field_default_value, r.RDB$NULL_FLAG AS field_not_null_constraint, " + "f.RDB$FIELD_LENGTH AS field_length, f.RDB$FIELD_PRECISION AS field_precision, " + "f.RDB$FIELD_SCALE AS field_scale, " + "CASE f.RDB$FIELD_TYPE WHEN 261 THEN 'BLOB' WHEN 14 THEN 'CHAR' WHEN 40 THEN 'CSTRING' " + "WHEN 11 THEN 'D_FLOAT' WHEN 27 THEN 'DOUBLE' WHEN 10 THEN 'FLOAT' WHEN 16 THEN 'INT64' " + "WHEN 8 THEN 'INTEGER' WHEN 9 THEN 'QUAD' WHEN 7 THEN 'SMALLINT' WHEN 12 THEN 'DATE' " + "WHEN 13 THEN 'TIME' WHEN 35 THEN 'TIMESTAMP' WHEN 37 THEN 'VARCHAR' ELSE 'UNKNOWN' " + "END AS field_type, f.RDB$FIELD_SUB_TYPE AS field_subtype, " + "coll.RDB$COLLATION_NAME AS field_collation, cset.RDB$CHARACTER_SET_NAME AS field_charset " + "FROM RDB$RELATION_FIELDS r LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME " + "LEFT JOIN RDB$COLLATIONS coll ON f.RDB$COLLATION_ID = coll.RDB$COLLATION_ID " + "LEFT JOIN RDB$CHARACTER_SETS cset ON f.RDB$CHARACTER_SET_ID = cset.RDB$CHARACTER_SET_ID " + "WHERE r.RDB$RELATION_NAME='" + name + "' ORDER BY r.RDB$FIELD_POSITION;" ); using (command) { using (IDataReader r = command.ExecuteReader()) { while (r.Read ()) { ColumnSchema column = new ColumnSchema (this); column.Name = r.GetString (0); column.DataTypeName = r.GetString (8); column.NotNull = (!r.IsDBNull (3) && r.GetInt32 (3) == 1); column.Default = r.GetString (2); column.Comment = r.GetString (1); column.OwnerName = name; column.Length = r.GetInt32 (4); column.Precision = r.GetInt32 (5); column.Scale = r.GetInt32 (6); columns.Add (column); } r.Close (); }; connectionProvider.Close (command.Connection); } return columns;
protected virtual ColumnSchema GetTableIndexColumn (DataRow row, TableSchema table, IndexSchema index) { ColumnSchema schema = new ColumnSchema (this, table); return schema; }
//TODO: change for IEnumerable? public List<ColumnFkFigure> addFkConstraint(TableModel source, kindOptionality optionality) { List<ColumnFkFigure> items = new List<ColumnFkFigure> (); ForeignKeyConstraintSchema fkc = new ForeignKeyConstraintSchema (source.TableSchema.SchemaProvider); fkc.ReferenceTableName = source.Name; fkc.ReferenceTable = source.TableSchema; fkc.Name = source.Name + "_" + TableSchema.Name + "_fk"; foreach (ColumnFigure col in source.columns) { if (col.PrimaryKey) { ColumnSchema fkCol = new ColumnSchema (col.ColumnModel); //Remove column level pk if any ConstraintSchema tmp=null; foreach(ConstraintSchema cs in fkCol.Constraints) if(cs is PrimaryKeyConstraintSchema) tmp=cs; if(tmp!=null) fkCol.Constraints.Remove(tmp); //TODO: create a function that just get three letters from table name using a standard fkCol.Name = fkCol.Name + "_" + (col.ColumnModel.Parent as TableSchema).Name + "_fk"; //TODO: should be checked that this name doesn't exists at table yet fkCol.Parent = TableSchema; if(optionality==kindOptionality.optional) fkCol.IsNullable=false; else fkCol.IsNullable=true; fkc.Columns.Add (fkCol); TableSchema.Columns.Add (fkCol); fkc.ReferenceColumns.Add (col.ColumnModel); Console.WriteLine("NO JODA 555 666 CREE Fk figure con:" + " Tabla: "+(col.ColumnModel.Parent as TableSchema).Name +"." +col.ColumnModel.Name); ColumnFkFigure fk = new ColumnFkFigure (fkCol, FigureOwner, col.ColumnModel.Name , (col.ColumnModel.Parent as TableSchema).Name); this.columns.Add (fk); items.Add (fk); } } if(fkc.Columns.Count > 0){ TableSchema.Constraints.Add (fkc); return items; }else return null; }
public virtual ConstraintSchemaCollection GetColumnConstraints (TableSchema table, ColumnSchema column) { throw new NotImplementedException (); }
public virtual ConstraintSchemaCollection GetColumnConstraints(TableSchema table, ColumnSchema column) { throw new NotImplementedException(); }
public virtual ColumnSchema GetNewColumnSchema (string name, ISchema parent) { ColumnSchema schema = new ColumnSchema (this, parent, name); return schema; }
public override ICollection<ColumnSchema> GetTableColumns (TableSchema table) { CheckConnectionState (); List<ColumnSchema> columns = new List<ColumnSchema> (); IDbCommand command = connectionProvider.CreateCommand ( "SELECT OWNER, TABLE_NAME, COLUMN_NAME, " + " DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, " + " NULLABLE, COLUMN_ID, DEFAULT_LENGTH, DATA_DEFAULT " + "FROM ALL_TAB_COLUMNS " + "WHERE OWNER = '" + table.OwnerName + "' " + "AND TABLE_NAME = '" + table.Name + "' " + "ORDER BY OWNER, TABLE_NAME, COLUMN_ID" ); using (command) { using (IDataReader r = command.ExecuteReader()) { while (r.Read ()) { ColumnSchema column = new ColumnSchema (this); column.Name = GetCheckedString (r, 2); column.DataTypeName = GetCheckedString (r, 3); column.OwnerName = table.OwnerName; column.SchemaName = table.OwnerName; column.NotNull = GetCheckedString (r, 7) == "Y"; column.Length = GetCheckedInt32 (r, 4); column.Precision = GetCheckedInt32 (r, 5); column.Scale = GetCheckedInt32 (r, 6); column.ColumnID = GetCheckedInt32 (r, 8); StringBuilder sb = new StringBuilder(); sb.AppendFormat("{0} {1}{2}", column.Name, column.DataTypeName, (column.Length > 0) ? ("(" + column.Length + ")") : ""); sb.AppendFormat(" {0}", column.NotNull ? "NOT NULL" : "NULL"); //if (column.Default.Length > 0) // sb.AppendFormat(" DEFAULT {0}", column.Default); column.Definition = sb.ToString(); columns.Add (column); } r.Close (); }; connectionProvider.Close (command.Connection); } return columns;
public virtual void Select (ColumnSchema column) { if (column == null) throw new ArgumentNullException ("column"); TreeIter iter = GetTreeIter (column); if (!iter.Equals (TreeIter.Zero)) { if (singleCheck) SetSelectState (false); store.SetValue (iter, ColSelectIndex, true); OnColumnToggled (); } }
public void Select (ColumnSchema column) { store.Select (column); }
public override ColumnSchemaCollection GetTableColumns (TableSchema table) { ColumnSchemaCollection columns = new ColumnSchemaCollection (); IPooledDbConnection conn = connectionPool.Request (); IDbCommand command = conn.CreateCommand ( "PRAGMA table_info('" + table.Name + "')" ); try { using (command) { using (IDataReader r = command.ExecuteReader()) { while (r.Read ()) { ColumnSchema column = new ColumnSchema (this, table); column.Position = r.GetInt32 (0); column.Name = r.GetString (1); column.DataTypeName = r.GetString (2); column.IsNullable = r.GetInt32 (3) != 0; column.DefaultValue = r.IsDBNull (4) ? null : r.GetValue (4).ToString (); columns.Add (column); } r.Close (); }; } } catch (Exception e) { QueryService.RaiseException (e); } conn.Release (); return columns; }
public virtual string GetCreateString (ColumnSchema column) { if (createFormat == null) { return name; } else { if (createParameters != null) { string[] parameters = createParameters.Split (','); string[] chunks = new string[parameters.Length]; int index=0; foreach (string param in parameters) { string chunk = param.ToLower ().Trim (); if (chunk.Contains ("length") || chunk.Contains ("size")) { chunks[index] = column.DataType.LengthRange.Default.ToString (); } else if (chunk.Contains ("scale")) { chunks[index] = column.DataType.ScaleRange.Default.ToString (); } else if (chunk.Contains ("precision")) { chunks[index] = column.DataType.PrecisionRange.Default.ToString (); } else { chunks[index] = String.Empty; } index++; } return String.Format (System.Globalization.CultureInfo.InvariantCulture, createFormat, chunks); } return createFormat; } }
protected virtual void AddColumn (ColumnSchema column) { store.AppendValues (false, column.Name, column); column.Changed += delegate (object sender, EventArgs args) { TreeIter iter = GetTreeIter (sender as ColumnSchema); if (!iter.Equals (TreeIter.Zero)) store.SetValue (iter, ColNameIndex, column.Name); }; }
public void AddFkConstraintColumn(ColumnSchema sourceCol, kindOptionality optionality) { AbstractColumnFigure tmp = Model.addFkConstraintColumn (sourceCol, optionality); this.Add (tmp); OnFigureChanged (new FigureEventArgs (this, DisplayBox)); }
public AbstractColumnFigure addFkConstraintColumn(ColumnSchema sourceCol, kindOptionality optionality) { if(sourceCol.Parent is TableSchema){ ForeignKeyConstraintSchema fkc = null; //Add this column to a constraint or create a new one foreach(ConstraintSchema cs in TableSchema.Constraints){ if(cs is ForeignKeyConstraintSchema && (cs as ForeignKeyConstraintSchema).ReferenceTableName==(sourceCol.Parent as TableSchema).Name){ fkc = (cs as ForeignKeyConstraintSchema); } } if(fkc==null){ fkc = new ForeignKeyConstraintSchema ((sourceCol.Parent as TableSchema).SchemaProvider); fkc.ReferenceTableName = (sourceCol.Parent as TableSchema).Name; fkc.ReferenceTable = (sourceCol.Parent as TableSchema); fkc.Name = (sourceCol.Parent as TableSchema).Name + "_" + TableSchema.Name + "_fk"; TableSchema.Constraints.Add(fkc); } ColumnSchema fkCol = new ColumnSchema(sourceCol); if(optionality==kindOptionality.optional) fkCol.IsNullable=false; else fkCol.IsNullable=true; //Remove column level pk if any ConstraintSchema tmp=null; foreach(ConstraintSchema cs in fkCol.Constraints) if(cs is PrimaryKeyConstraintSchema) tmp=cs; if(tmp!=null) fkCol.Constraints.Remove(tmp); fkCol.Name = fkCol.Name + "_" + (sourceCol.Parent as TableSchema).Name + "_fk"; //TODO: should be checked that this name doesn't exists at table yet fkCol.Parent = TableSchema; fkc.Columns.Add (fkCol); TableSchema.Columns.Add (fkCol); fkc.ReferenceColumns.Add (sourceCol); ColumnFkFigure fk = new ColumnFkFigure (fkCol, FigureOwner, sourceCol.Name, (sourceCol.Parent as TableSchema).Name); this.columns.Add (fk); return fk; } return null; }
public void RefreshRelationships(bool refresh, bool changeConnection, TableFigure notifier, kindOptionality optionality, bool changeDatatype, ColumnSchema notifierColumn) { if (refresh && changeConnection) throw new NotImplementedException (); if (NotifyChanged != null) { NotifyChanged (refresh, changeConnection, notifier, optionality, changeDatatype, notifierColumn); } }
public override ICollection<ColumnSchema> GetProcedureColumns (ProcedureSchema procedure) { CheckConnectionState (); List<ColumnSchema> columns = new List<ColumnSchema> (); IDbCommand command = connectionProvider.CreateCommand ( "SELECT RDB$PARAMETER_NAME, RDB$PARAMETER_NUMBER, RDB$DESCRIPTION, RDB$SYSTEM_FLAG " + "FROM RDB$PROCEDURE_PARAMETERS " + "WHERE RDB$PROCEDURE_NAME = '" + procedure.Name + "' " + "AND RDB$PARAMETER_TYPE = 1 " + "ORDER BY 2;" ); using (command) { using (IDataReader r = command.ExecuteReader()) { while (r.Read ()) { ColumnSchema column = new ColumnSchema (this); column.Name = r.GetString (0); column.OwnerName = procedure.Name; column.Comment = r.GetString (2); //TODO: data type columns.Add (column); } r.Close (); } connectionProvider.Close (command.Connection); } return columns;
protected virtual TreeIter GetTreeIter (ColumnSchema column) { TreeIter iter; if (store.GetIterFirst (out iter)) { do { object obj = store.GetValue (iter, ColObjIndex); if (obj == column) return iter; } while (store.IterNext (ref iter)); } return TreeIter.Zero; }
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); }