public virtual ForeignKeyConstraintSchema GetNewForeignKeyConstraintSchema(string name)
{
ForeignKeyConstraintSchema schema = new ForeignKeyConstraintSchema(this);
schema.Name = name;
return(schema);
}
public ForeignKeyConstraintSchema(ForeignKeyConstraintSchema constraint)
: base(constraint)
{
referenceTable = constraint.referenceTable;
referenceColumns = new ColumnSchemaCollection(constraint.referenceColumns);
deleteAction = constraint.deleteAction;
updateAction = constraint.updateAction;
}
public ForeignKeyConstraintSchema (ForeignKeyConstraintSchema constraint)
: base (constraint)
{
referenceTable = constraint.referenceTable;
referenceColumns = new ColumnSchemaCollection (constraint.referenceColumns);
deleteAction = constraint.deleteAction;
updateAction = constraint.updateAction;
}
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.ReferenceTable);
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());
}
public RelationshipFigure()
: base()
{
notation = kindNotation.Barker;
fkConstraint=null;
StartTerminal = new RelationshipLineTerminal (this, 8.0, 22.0, kindRelationshipTerminal.OneOne, notation, false);
EndTerminal = new RelationshipLineTerminal (this, 8.0, 22.0, kindRelationshipTerminal.OneMore, notation, false);
start = StartTerminal as RelationshipLineTerminal;
end = EndTerminal as RelationshipLineTerminal;
identifyRelationship = false;
optionalityRelationship = kindOptionality.optional;
this.ConnectionChanged+=ConnectionChangedHandler;
//blockUntilRelease=false;
}
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 ConstraintSchemaCollection GetTableConstraints(TableSchema table)
{
ConstraintSchemaCollection constraints = new ConstraintSchemaCollection();
IPooledDbConnection conn = connectionPool.Request();
IDbCommand command = conn.CreateCommand("SHOW TABLE STATUS FROM `" + table.SchemaName + "`;");
try {
using (command) {
using (IDataReader r = command.ExecuteReader()) {
while (r.Read())
{
string[] chunks = ((string)r["Comment"]).Split(';');
//the values we are looking for are in the format (`table`) REFER `database\table2` (`table2`)
foreach (string chunk in chunks)
{
if (constraintRegex.IsMatch(chunk))
{
MatchCollection matches = constraintRegex.Matches(chunk);
ForeignKeyConstraintSchema constraint = new ForeignKeyConstraintSchema(this);
constraint.ReferenceTableName = matches[1].Groups[1].ToString();
constraint.Name = matches[0].Groups[1].ToString();
constraints.Add(constraint);
}
}
}
r.Close();
}
}
} catch (Exception e) {
QueryService.RaiseException(e);
}
conn.Release();
return(constraints);
}
private void AddConstraint (ForeignKeyConstraintSchema fk)
{
store.AppendValues (fk.Name, String.Empty, false, String.Empty, String.Empty,
fk.DeleteAction.ToString (), fk.UpdateAction.ToString (), fk
);
}
public virtual ForeignKeyConstraintSchema GetNewForeignKeyConstraintSchema (string name)
{
ForeignKeyConstraintSchema schema = new ForeignKeyConstraintSchema (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);
}
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;
}
//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;
}
//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;