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);
}
private void SourceChanged(object sender, EventArgs args)
{
TreeIter iter;
if (listCheck.Selection.GetSelected(out iter))
{
CheckConstraintSchema check = store.GetValue(iter, colObjIndex) as CheckConstraintSchema;
check.Source = sqlEditor.Text;
store.SetValue(iter, colSourceIndex, sqlEditor.Text);
EmitContentChanged();
}
}
protected virtual void AddClicked(object sender, EventArgs e)
{
CheckConstraintSchema check = schemaProvider.GetNewCheckConstraintSchema("check_new");
int index = 1;
while (constraints.Contains(check.Name))
{
check.Name = "check_new" + (index++);
}
constraints.Add(check);
AddConstraint(check);
EmitContentChanged();
}
protected virtual void AddClicked(object sender, EventArgs e)
{
CheckConstraintSchema check = schemaProvider.CreateCheckConstraintSchema(string.Concat(table.Name,
"_",
"check_new"));
int index = 1;
while (constraints.Contains(check.Name))
{
check.Name = string.Concat(table.Name,
"_",
"check_new",
(index++).ToString());
}
constraints.Add(check);
AddConstraint(check);
EmitContentChanged();
}
protected virtual void OnSelectionChanged(object sender, EventArgs e)
{
TreeIter iter;
if (listCheck.Selection.GetSelected(out iter))
{
buttonRemove.Sensitive = true;
sqlEditor.Editable = true;
CheckConstraintSchema check = store.GetValue(iter, colObjIndex) as CheckConstraintSchema;
sqlEditor.Text = check.Source;
}
else
{
buttonRemove.Sensitive = false;
sqlEditor.Editable = false;
sqlEditor.Text = String.Empty;
}
}
protected virtual void RemoveClicked(object sender, EventArgs e)
{
TreeIter iter;
if (listCheck.Selection.GetSelected(out iter))
{
CheckConstraintSchema check = store.GetValue(iter, colObjIndex) as CheckConstraintSchema;
if (MessageService.Confirm(
AddinCatalog.GetString("Are you sure you want to remove constraint '{0}'?", check.Name),
AlertButton.Remove
))
{
store.Remove(ref iter);
constraints.Remove(check);
EmitContentChanged();
}
}
}
public virtual void FillSchemaObjects()
{
TreeIter iter;
if (store.GetIterFirst(out iter))
{
do
{
CheckConstraintSchema check = store.GetValue(iter, colObjIndex) as CheckConstraintSchema;
check.Name = store.GetValue(iter, colNameIndex) as string;
check.Source = store.GetValue(iter, colSourceIndex) as string;
check.IsColumnConstraint = (bool)store.GetValue(iter, colIsColumnConstraintIndex);
if (check.IsColumnConstraint)
{
string column = store.GetValue(iter, colColumnNameIndex) as string;
check.Columns.Add(columns.Search(column));
}
table.Constraints.Add(check);
} while (store.IterNext(ref iter));
}
}
private void AddConstraint(CheckConstraintSchema check)
{
store.AppendValues(check.Name, String.Empty, false, String.Empty, check);
}
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;
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());
}
