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);
}
