extras/MonoDevelop.Database/MonoDevelop.Database.Designer/Widgets/ForeignKeyConstraintEditorWidget.cs

   1 //
   2 // Authors:
   3 //    Ben Motmans  <ben.motmans@gmail.com>
   4 //
   5 // Copyright (c) 2007 Ben Motmans
   6 //
   7 // Permission is hereby granted, free of charge, to any person obtaining a copy
   8 // of this software and associated documentation files (the "Software"), to deal
   9 // in the Software without restriction, including without limitation the rights
  10 // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
  11 // copies of the Software, and to permit persons to whom the Software is
  12 // furnished to do so, subject to the following conditions:
  13 //
  14 // The above copyright notice and this permission notice shall be included in
  15 // all copies or substantial portions of the Software.
  16 //
  17 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
  18 // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
  19 // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
  20 // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
  21 // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
  22 // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
  23 // THE SOFTWARE.
  24 //
  25
  26 using Gtk;
  27 using System;
  28 using System.Text;
  29 using System.Collections.Generic;
  30 using MonoDevelop.Core;
  31 using MonoDevelop.Core.Gui;
  32 using MonoDevelop.Components;
  33 using MonoDevelop.Database.Sql;
  34 using MonoDevelop.Database.Components;
  35
  36 namespace MonoDevelop.Database.Designer
  37 {
  38         [System.ComponentModel.Category("widget")]
  39         [System.ComponentModel.ToolboxItem(true)]
  40         public partial class ForeignKeyConstraintEditorWidget : Gtk.Bin
  41         {
  42                 public event EventHandler ContentChanged;
  43
  44                 private ISchemaProvider schemaProvider;
  45                 private TableSchema table;
  46                 private TableSchemaCollection tables;
  47                 private ColumnSchemaCollection columns;
  48                 private ConstraintSchemaCollection constraints;
  49
  50                 private const int colNameIndex = 0;
  51                 private const int colReferenceTableIndex = 1;
  52                 private const int colIsColumnConstraintIndex = 2;
  53                 private const int colColumnsIndex = 3;
  54                 private const int colReferenceColumnsIndex = 4;
  55                 private const int colDeleteActionIndex = 5;
  56                 private const int colUpdateActionIndex = 6;
  57                 private const int colObjIndex = 7;
  58
  59                 private ListStore store;
  60                 private ListStore storeActions;
  61                 private ListStore storeTables;
  62
  63                 private SchemaActions action;
  64                 private ForeignKeyConstraintEditorSettings settings;
  65
  66                 //TODO: difference between columns and reference columns + combo events
  67                 public ForeignKeyConstraintEditorWidget (ISchemaProvider schemaProvider, SchemaActions action, ForeignKeyConstraintEditorSettings settings)
  68                 {
  69                         if (schemaProvider == null)
  70                                 throw new ArgumentNullException ("schemaProvider");
  71                         if (settings == null)
  72                                 throw new ArgumentNullException ("settings");
  73
  74                         this.schemaProvider = schemaProvider;
  75                         this.action = action;
  76                         this.settings = settings;
  77
  78                         this.Build();
  79
  80                         store = new ListStore (typeof (string), typeof (string), typeof (bool), typeof (string), typeof (string), typeof (string), typeof (string), typeof (object));
  81                         listFK.Model = store;
  82
  83                         storeActions = new ListStore (typeof (string), typeof (int));
  84                         storeTables = new ListStore (typeof (string));
  85
  86                         if (settings.SupportsCascade)
  87                                 storeActions.AppendValues ("Cascade", ForeignKeyAction.Cascade);
  88                         if (settings.SupportsRestrict)
  89                                 storeActions.AppendValues ("Restrict", ForeignKeyAction.Restrict);
  90                         if (settings.SupportsNoAction)
  91                                 storeActions.AppendValues ("No Action", ForeignKeyAction.NoAction);
  92                         if (settings.SupportsSetNull)
  93                                 storeActions.AppendValues ("Set Null", ForeignKeyAction.SetNull);
  94                         if (settings.SupportsSetDefault)
  95                                 storeActions.AppendValues ("Set Default", ForeignKeyAction.SetDefault);
  96
  97                         foreach (TableSchema tbl in tables)
  98                                 if (tbl.Name != table.Name)
  99                                         storeTables.AppendValues (tbl.Name);
 100
 101                         TreeViewColumn colName = new TreeViewColumn ();
 102                         TreeViewColumn colRefTable = new TreeViewColumn ();
 103                         TreeViewColumn colIsColumnConstraint = new TreeViewColumn ();
 104                         TreeViewColumn colDeleteAction = new TreeViewColumn ();
 105                         TreeViewColumn colUpdateAction = new TreeViewColumn ();
 106
 107                         colName.Title = AddinCatalog.GetString ("Name");
 108                         colRefTable.Title = AddinCatalog.GetString ("Reference Table");
 109                         colIsColumnConstraint.Title = AddinCatalog.GetString ("Column Constraint");
 110                         colDeleteAction.Title = AddinCatalog.GetString ("Delete Action");
 111                         colUpdateAction.Title = AddinCatalog.GetString ("Update Action");
 112
 113                         colRefTable.MinWidth = 120;
 114
 115                         CellRendererText nameRenderer = new CellRendererText ();
 116                         CellRendererCombo refTableRenderer = new CellRendererCombo ();
 117                         CellRendererToggle isColumnConstraintRenderer = new CellRendererToggle ();
 118                         CellRendererCombo deleteActionRenderer = new CellRendererCombo ();
 119                         CellRendererCombo updateActionRenderer = new CellRendererCombo ();
 120
 121                         nameRenderer.Editable = true;
 122                         nameRenderer.Edited += new EditedHandler (NameEdited);
 123
 124                         refTableRenderer.Model = storeTables;
 125                         refTableRenderer.TextColumn = 0;
 126                         refTableRenderer.Editable = true;
 127                         refTableRenderer.Edited += new EditedHandler (RefTableEdited);
 128
 129                         isColumnConstraintRenderer.Activatable = true;
 130                         isColumnConstraintRenderer.Toggled += new ToggledHandler (IsColumnConstraintToggled);
 131
 132                         deleteActionRenderer.Model = storeActions;
 133                         deleteActionRenderer.TextColumn = 0;
 134                         deleteActionRenderer.Editable = true;
 135                         deleteActionRenderer.Edited += new EditedHandler (DeleteActionEdited);
 136
 137                         updateActionRenderer.Model = storeActions;
 138                         updateActionRenderer.TextColumn = 0;
 139                         updateActionRenderer.Editable = true;
 140                         updateActionRenderer.Edited += new EditedHandler (UpdateActionEdited);
 141
 142                         colName.PackStart (nameRenderer, true);
 143                         colRefTable.PackStart (refTableRenderer, true);
 144                         colIsColumnConstraint.PackStart (isColumnConstraintRenderer, true);
 145                         colDeleteAction.PackStart (deleteActionRenderer, true);
 146                         colUpdateAction.PackStart (updateActionRenderer, true);
 147
 148                         colName.AddAttribute (nameRenderer, "text", colNameIndex);
 149                         colRefTable.AddAttribute (refTableRenderer, "text", colReferenceTableIndex);
 150                         colIsColumnConstraint.AddAttribute (isColumnConstraintRenderer, "active", colIsColumnConstraintIndex);
 151                         colDeleteAction.AddAttribute (deleteActionRenderer, "text", colDeleteActionIndex);
 152                         colUpdateAction.AddAttribute (updateActionRenderer, "text", colUpdateActionIndex);
 153
 154                         listFK.AppendColumn (colName);
 155                         listFK.AppendColumn (colRefTable);
 156                         listFK.AppendColumn (colIsColumnConstraint);
 157                         listFK.AppendColumn (colDeleteAction);
 158                         listFK.AppendColumn (colUpdateAction);
 159
 160                         columnSelecter.ColumnToggled += new EventHandler (ColumnToggled);
 161                         referenceColumnSelecter.ColumnToggled += new EventHandler (ReferenceColumnToggled);
 162                         listFK.Selection.Changed += new EventHandler (SelectionChanged);
 163
 164                         ShowAll ();
 165                 }
 166
 167                 public void Initialize (TableSchemaCollection tables, TableSchema table, ColumnSchemaCollection columns, ConstraintSchemaCollection constraints)
 168                 {
 169                         if (columns == null)
 170                                 throw new ArgumentNullException ("columns");
 171                         if (table == null)
 172                                 throw new ArgumentNullException ("table");
 173                         if (constraints == null)
 174                                 throw new ArgumentNullException ("constraints");
 175                         if (tables == null)
 176                                 throw new ArgumentNullException ("tables");
 177
 178                         this.table = table;
 179                         this.tables = tables;
 180                         this.columns = columns;
 181                         this.constraints = constraints;
 182                 }
 183
 184                 protected virtual void AddClicked (object sender, EventArgs e)
 185                 {
 186                         ForeignKeyConstraintSchema fk = schemaProvider.CreateForeignKeyConstraintSchema ("fk_new");
 187                         int index = 1;
 188                         while (constraints.Contains (fk.Name))
 189                                 fk.Name = "fk_new" + (index++);
 190                         constraints.Add (fk);
 191                         AddConstraint (fk);
 192                         EmitContentChanged ();
 193                 }
 194
 195                 protected virtual void RemoveClicked (object sender, EventArgs e)
 196                 {
 197                         TreeIter iter;
 198                         if (listFK.Selection.GetSelected (out iter)) {
 199                                 ForeignKeyConstraintSchema fk = store.GetValue (iter, colObjIndex) as ForeignKeyConstraintSchema;
 200
 201                                 if (MessageService.Confirm (
 202                                         AddinCatalog.GetString ("Are you sure you want to remove constraint '{0}'?", fk.Name),
 203                                         AlertButton.Remove
 204                                 )) {
 205                                         store.Remove (ref iter);
 206                                         constraints.Remove (fk);
 207                                         EmitContentChanged ();
 208                                 }
 209                         }
 210                 }
 211
 212                 private void SelectionChanged (object sender, EventArgs args)
 213                 {
 214                         columnSelecter.DeselectAll ();
 215
 216                         TreeIter iter;
 217                         if (listFK.Selection.GetSelected (out iter)) {
 218                                 columnSelecter.Sensitive = true;
 219                                 SetSelectionFromIter (iter);
 220                         } else {
 221                                 columnSelecter.Sensitive = false;
 222                         }
 223                 }
 224
 225                 private void SetSelectionFromIter (TreeIter iter)
 226                 {
 227                         bool iscolc = (bool)store.GetValue (iter, colIsColumnConstraintIndex);
 228                         columnSelecter.SingleCheck = iscolc;
 229
 230                         string colstr = store.GetValue (iter, colColumnsIndex) as string;
 231                         string[] cols = colstr.Split (',');
 232                         foreach (string col in cols)
 233                                 columnSelecter.Select (col);
 234
 235                         colstr = store.GetValue (iter, colReferenceColumnsIndex) as string;
 236                         cols = colstr.Split (',');
 237                         foreach (string col in cols)
 238                                 referenceColumnSelecter.Select (col);
 239                 }
 240
 241                 private void RefTableEdited (object sender, EditedArgs args)
 242                 {
 243                         TreeIter iter;
 244                         if (store.GetIterFromString (out iter, args.Path)) {
 245                                 if (tables.Contains (args.NewText)) {
 246                                         store.SetValue (iter, colReferenceTableIndex, args.NewText);
 247                                         SetSelectionFromIter (iter);
 248                                         EmitContentChanged ();
 249                                 } else {
 250                                         string oldText = store.GetValue (iter, colReferenceTableIndex) as string;
 251                                         (sender as CellRendererText).Text = oldText;
 252                                 }
 253                         }
 254                 }
 255
 256                 private void ColumnToggled (object sender, EventArgs args)
 257                 {
 258                         TreeIter iter;
 259                         if (listFK.Selection.GetSelected (out iter)) {
 260                                 store.SetValue (iter, colColumnsIndex, GetColumnsString (columnSelecter.CheckedColumns));
 261                                 EmitContentChanged ();
 262                         }
 263                 }
 264
 265                 private void ReferenceColumnToggled (object sender, EventArgs args)
 266                 {
 267                         TreeIter iter;
 268                         if (listFK.Selection.GetSelected (out iter)) {
 269                                 store.SetValue (iter, colReferenceColumnsIndex, GetColumnsString (referenceColumnSelecter.CheckedColumns));
 270                                 EmitContentChanged ();
 271                         }
 272                 }
 273
 274                 private void IsColumnConstraintToggled (object sender, ToggledArgs args)
 275                 {
 276                         TreeIter iter;
 277                         if (store.GetIterFromString (out iter, args.Path)) {
 278                                 bool val = (bool) store.GetValue (iter, colIsColumnConstraintIndex);
 279                                 store.SetValue (iter, colIsColumnConstraintIndex, !val);
 280                                 EmitContentChanged ();
 281                         }
 282                 }
 283
 284                 private void NameEdited (object sender, EditedArgs args)
 285                 {
 286                         TreeIter iter;
 287                         if (store.GetIterFromString (out iter, args.Path)) {
 288                                 if (!string.IsNullOrEmpty (args.NewText)) {
 289                                         store.SetValue (iter, colNameIndex, args.NewText);
 290                                         EmitContentChanged ();
 291                                 } else {
 292                                         string oldText = store.GetValue (iter, colNameIndex) as string;
 293                                         (sender as CellRendererText).Text = oldText;
 294                                 }
 295                         }
 296                 }
 297
 298                 private void UpdateActionEdited (object sender, EditedArgs args)
 299                 {
 300                         TreeIter iter;
 301                         if (store.GetIterFromString (out iter, args.Path)) {
 302                                 if (IsValidForeignKeyAction (args.NewText)) {
 303                                         store.SetValue (iter, colUpdateActionIndex, args.NewText);
 304                                         EmitContentChanged ();
 305                                 } else {
 306                                         string oldText = store.GetValue (iter, colUpdateActionIndex) as string;
 307                                         (sender as CellRendererText).Text = oldText;
 308                                 }
 309                         }
 310                 }
 311
 312                 private void DeleteActionEdited (object sender, EditedArgs args)
 313                 {
 314                         TreeIter iter;
 315                         if (store.GetIterFromString (out iter, args.Path)) {
 316                                 if (IsValidForeignKeyAction (args.NewText)) {
 317                                         store.SetValue (iter, colDeleteActionIndex, args.NewText);
 318                                         EmitContentChanged ();
 319                                 } else {
 320                                         string oldText = store.GetValue (iter, colDeleteActionIndex) as string;
 321                                         (sender as CellRendererText).Text = oldText;
 322                                 }
 323                         }
 324                 }
 325
 326                 private bool IsValidForeignKeyAction (string name)
 327                 {
 328                         foreach (string item in Enum.GetNames (typeof (ForeignKeyAction))) {
 329                                 if (item == name)
 330                                         return true;
 331                         }
 332                         return false;
 333                 }
 334
 335                 private void AddConstraint (ForeignKeyConstraintSchema fk)
 336                 {
 337                         store.AppendValues (fk.Name, String.Empty, false, String.Empty, String.Empty,
 338                                 fk.DeleteAction.ToString (), fk.UpdateAction.ToString (), fk
 339                         );
 340                 }
 341
 342                 protected virtual void EmitContentChanged ()
 343                 {
 344                         if (ContentChanged != null)
 345                                 ContentChanged (this, EventArgs.Empty);
 346                 }
 347
 348                 public virtual bool ValidateSchemaObjects (out string msg)
 349                 {
 350                         TreeIter iter;
 351                         if (store.GetIterFirst (out iter)) {
 352                                 do {
 353                                         string name = store.GetValue (iter, colNameIndex) as string;
 354                                         string columns = store.GetValue (iter, colColumnsIndex) as string;
 355
 356                                         if (String.IsNullOrEmpty (columns)) {
 357                                                 msg = AddinCatalog.GetString ("Unique Key constraint '{0}' must be applied to one or more columns.", name);
 358                                                 return false;
 359                                         }
 360                                 } while (store.IterNext (ref iter));
 361                         }
 362                         msg = null;
 363                         return true;
 364                 }
 365
 366                 public virtual void FillSchemaObjects ()
 367                 {
 368                         TreeIter iter;
 369                         if (store.GetIterFirst (out iter)) {
 370                                 do {
 371                                         ForeignKeyConstraintSchema fk = store.GetValue (iter, colObjIndex) as ForeignKeyConstraintSchema;
 372
 373                                         fk.Name = store.GetValue (iter, colNameIndex) as string;
 374                                         fk.IsColumnConstraint = (bool)store.GetValue (iter, colIsColumnConstraintIndex);
 375                                         fk.ReferenceTableName = store.GetValue (iter, colReferenceTableIndex) as string;
 376
 377                                         fk.DeleteAction = GetForeignKeyAction (iter, colDeleteActionIndex);
 378                                         fk.UpdateAction = GetForeignKeyAction (iter, colUpdateActionIndex);
 379
 380                                         string colstr = store.GetValue (iter, colColumnsIndex) as string;
 381                                         string[] cols = colstr.Split (',');
 382                                         foreach (string col in cols) {
 383                                                 ColumnSchema column = columns.Search (col);
 384                                                 fk.Columns.Add (column);
 385                                         }
 386
 387                                         colstr = store.GetValue (iter, colReferenceColumnsIndex) as string;
 388                                         cols = colstr.Split (',');
 389                                         foreach (string col in cols) {
 390                                                 ColumnSchema column = columns.Search (col);
 391                                                 fk.ReferenceColumns.Add (column);
 392                                         }
 393
 394                                         table.Constraints.Add (fk);
 395                                 } while (store.IterNext (ref iter));
 396                         }
 397                 }
 398
 399                 private string GetColumnsString (IEnumerable<ColumnSchema> collection)
 400                 {
 401                         bool first = true;
 402                         StringBuilder sb = new StringBuilder ();
 403                         foreach (ColumnSchema column in collection) {
 404                                 if (first)
 405                                         first = false;
 406                                 else
 407                                         sb.Append (',');
 408
 409                                 sb.Append (column.Name);
 410                         }
 411                         return sb.ToString ();
 412                 }
 413
 414                 private ForeignKeyAction GetForeignKeyAction (TreeIter colIter, int colIndex)
 415                 {
 416                         string name = store.GetValue (colIter, colIndex) as string;
 417
 418                         TreeIter iter;
 419                         if (storeActions.GetIterFirst (out iter)) {
 420                                 do {
 421                                         string actionName = storeActions.GetValue (iter, 0) as string;
 422                                         if (actionName == name)
 423                                                 return (ForeignKeyAction)storeActions.GetValue (iter, 1);
 424                                 } while (storeActions.IterNext (ref iter));
 425                         }
 426                         return ForeignKeyAction.None;
 427                 }
 428         }
 429
 430         public class ForeignKeyConstraintEditorSettings
 431         {
 432                 private bool supportsCascade = true;
 433                 private bool supportsRestrict = true;
 434                 private bool supportsNoAction = true;
 435                 private bool supportsSetNull = true;
 436                 private bool supportsSetDefault = true;
 437
 438                 public bool SupportsCascade {
 439                         get { return supportsCascade; }
 440                         set { supportsCascade = value; }
 441                 }
 442
 443                 public bool SupportsRestrict {
 444                         get { return supportsRestrict; }
 445                         set { supportsRestrict = value; }
 446                 }
 447
 448                 public bool SupportsNoAction {
 449                         get { return supportsNoAction; }
 450                         set { supportsNoAction = value; }
 451                 }
 452
 453                 public bool SupportsSetNull {
 454                         get { return supportsSetNull; }
 455                         set { supportsSetNull = value; }
 456                 }
 457
 458                 public bool SupportsSetDefault {
 459                         get { return supportsSetDefault; }
 460                         set { supportsSetDefault = value; }
 461                 }
 462         }
 463 }