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