/********************************************************
* ADO.NET 2.0 Data Provider for SQLite Version 3.X
* Written by Robert Simpson (robert@blackcastlesoft.com)
*
* Released to the public domain, use at your own risk!
********************************************************/
namespace SQLite.Designer.Design
{
using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.ComponentModel.Design;
using System.Drawing.Design;
using System.Windows.Forms;
using System.Security.Permissions;
using System.Globalization;
internal class IndexEditor : CollectionEditor
{
Table _table;
CollectionEditor.CollectionForm _form;
object[] _items;
object[] _orig;
int _count;
internal IndexEditor(Table parent)
: base(typeof(List<Index>))
{
_table = parent;
}
protected override object[] GetItems(object editValue)
{
if (_items == null)
{
List<Index> value = editValue as List<Index>;
int extra = (_table.PrimaryKey.Columns.Count > 0) ? 1 : 0;
_items = new object[value.Count + extra];
_orig = new object[_items.Length];
for (int n = extra; n < _items.Length; n++)
{
_items[n] = ((ICloneable)value[n - extra]).Clone();
_orig[n] = value[n - extra];
}
if (extra > 0)
{
_items[0] = ((ICloneable)_table.PrimaryKey).Clone();
_orig[0] = _table.PrimaryKey;
}
_count = _items.Length;
}
return _items;
}
protected override CollectionEditor.CollectionForm CreateCollectionForm()
{
_form = base.CreateCollectionForm();
_form.Text = "Index Editor";
/* Doing this because I can't figure out how to get the Columns collection editor to notify this editor when a column of an index is updated.
This forces the collection editor form to be "dirty" which calls SetItems() when you hit OK or cancel. Otherwise, if you
change a column around and hit OK, then hit OK on this editor, it won't be dirty and won't update. */
try
{
_form.GetType().InvokeMember("dirty", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.SetField, null, _form, new object[] { true });
}
catch
{
}
foreach (Control c in _form.Controls[0].Controls)
{
PropertyGrid grid = c as PropertyGrid;
if (grid != null)
{
grid.HelpVisible = true;
break;
}
}
_form.Width = (int)(_form.Width * 1.25);
_form.Height = (int)(_form.Height * 1.25);
return _form;
}
protected override object CreateInstance(Type itemType)
{
if (itemType == typeof(Index))
{
return new Index(null, _table, null);
}
throw new NotSupportedException();
}
protected override bool CanRemoveInstance(object value)
{
return !(value is PrimaryKey);
}
protected override object SetItems(object editValue, object[] value)
{
bool dirty = false;
int count = 0;
if (_form.DialogResult == DialogResult.Cancel)
value = _orig;
if (editValue != null)
{
if (!(editValue is IList))
{
return editValue;
}
IList list = (IList)editValue;
list.Clear();
for (int i = 0; i < value.Length; i++)
{
Index idx = value[i] as Index;
if (idx is PrimaryKey)
{
_table.PrimaryKey = (PrimaryKey)idx;
if (idx.IsDirty) dirty = true;
count++;
}
else
{
if (idx != null && idx.Columns.Count > 0)
{
idx.Name = idx.Name;
list.Add(idx);
if (idx.IsDirty) dirty = true;
count++;
}
}
}
}
if ((dirty == true || count != _count) && _form.DialogResult == DialogResult.OK)
_table._owner.MakeDirty();
return editValue;
}
}
internal class IndexColumnEditor : CollectionEditor
{
Index _index;
object[] _items;
object[] _orig;
int _count;
CollectionEditor.CollectionForm _form;
public IndexColumnEditor() : base(typeof(List<IndexColumn>))
{
}
protected override CollectionEditor.CollectionForm CreateCollectionForm()
{
_form = base.CreateCollectionForm();
_form.Text = "Index Columns Editor";
foreach (Control c in _form.Controls[0].Controls)
{
PropertyGrid grid = c as PropertyGrid;
if (grid != null)
{
grid.HelpVisible = true;
break;
}
}
_form.Width = (int)(_form.Width * 1.25);
_form.Height = (int)(_form.Height * 1.25);
return _form;
}
public override object EditValue(ITypeDescriptorContext context, IServiceProvider provider, object value)
{
_index = context.Instance as Index;
_items = null;
_count = 0;
return base.EditValue(context, provider, value);
}
protected override object CreateInstance(Type itemType)
{
if (itemType == typeof(IndexColumn))
{
return new IndexColumn(_index, null);
}
throw new NotSupportedException();
}
protected override object[] GetItems(object editValue)
{
if (_items == null)
{
List<IndexColumn> value = editValue as List<IndexColumn>;
_items = new object[value.Count];
_orig = new object[value.Count];
for (int n = 0; n < _items.Length; n++)
{
_items[n] = ((ICloneable)value[n]).Clone();
_orig[n] = value[n];
}
_count = _items.Length;
}
return _items;
}
protected override object SetItems(object editValue, object[] value)
{
if (_form.DialogResult == DialogResult.Cancel)
value = _orig;
if (editValue != null)
{
if (!(editValue is IList))
{
return editValue;
}
IList list = (IList)editValue;
list.Clear();
for (int i = 0; i < value.Length; i++)
{
IndexColumn idx = value[i] as IndexColumn;
if (idx != null && String.IsNullOrEmpty(idx.Column) == false)
{
list.Add(value[i]);
}
}
}
if ((_index.IsDirty || _index.Columns.Count != _count) && _form.DialogResult == DialogResult.OK)
{
if (_index.Columns.Count > 0 && String.IsNullOrEmpty(_index._name) == true)
_index.Name = _index.Name;
}
return editValue;
}
}
internal class IndexTypeConverter : TypeConverter
{
public override bool CanConvertFrom(ITypeDescriptorContext context, Type sourceType)
{
if (sourceType == typeof(string)) return true;
return base.CanConvertFrom(context, sourceType);
}
public override bool CanConvertTo(ITypeDescriptorContext context, Type destinationType)
{
if (destinationType == typeof(string)) return true;
return base.CanConvertTo(context, destinationType);
}
public override object ConvertTo(ITypeDescriptorContext context, System.Globalization.CultureInfo culture, object value, Type destinationType)
{
if (destinationType == typeof(string))
{
StringBuilder builder = new StringBuilder();
string separator = String.Empty;
foreach (IndexColumn c in (List<IndexColumn>)value)
{
builder.AppendFormat("{0}[{1}]", separator, c.Column);
if (c.SortMode != ColumnSortMode.Ascending)
builder.Append(" DESC");
if (c.Collate != "BINARY")
builder.AppendFormat(" COLLATE {0}", c.Collate.ToUpperInvariant());
separator = ", ";
}
return builder.ToString();
}
else
return base.ConvertTo(context, culture, value, destinationType);
}
}
internal enum ColumnSortMode
{
Ascending = 0,
Descending = 1
}
[DefaultProperty("Column")]
internal class IndexColumn : IHaveConnectionScope, ICloneable
{
internal Index _parent;
private string _column;
private ColumnSortMode _mode = ColumnSortMode.Ascending;
private string _collate = "BINARY";
[Editor(typeof(ColumnsTypeEditor), typeof(UITypeEditor))]
[DisplayName("Base Column")]
[Category("Source")]
[Description("The column name to be included in the index.")]
[NotifyParentProperty(true)]
[RefreshProperties(RefreshProperties.All)]
public string Column
{
get { return _column; }
set
{
if (_column != value)
{
_column = value;
_parent.MakeDirty();
}
}
}
[DefaultValue(ColumnSortMode.Ascending)]
[Category("Constraints")]
[Description("Specifies what order to sort the column in. Descending indexes are not supported when using the SQLite legacy file format.")]
[NotifyParentProperty(true)]
[RefreshProperties(RefreshProperties.All)]
public ColumnSortMode SortMode
{
get { return _mode; }
set
{
if (value != _mode)
{
_mode = value;
_parent.MakeDirty();
}
}
}
[DefaultValue("BINARY")]
[Category("Constraints")]
[Editor(typeof(CollationTypeEditor), typeof(UITypeEditor))]
[Description("The collation sequence to use to generate the index for the specified column.")]
[NotifyParentProperty(true)]
[RefreshProperties(RefreshProperties.All)]
public string Collate
{
get { return _collate; }
set
{
if (String.IsNullOrEmpty(value)) value = "BINARY";
if (value != _collate)
{
_collate = value;
if (_parent is PrimaryKey)
{
PrimaryKey pk = _parent as PrimaryKey;
if (pk.Columns.Count == 1)
{
foreach (Column c in pk.Table.Columns)
{
if (string.Compare(c.ColumnName, Column, StringComparison.OrdinalIgnoreCase) == 0)
{
c.Collate = value;
break;
}
}
}
}
_parent.MakeDirty();
}
}
}
public override string ToString()
{
if (String.IsNullOrEmpty(_column) == true) return "(none)";
return _column;
}
private IndexColumn(IndexColumn source)
{
_parent = source._parent;
_column = source._column;
_mode = source._mode;
_collate = source._collate;
}
internal IndexColumn(Index parent, DataRow row)
{
_parent = parent;
if (row != null)
{
if (!row.IsNull("COLUMN_NAME"))
_column = row["COLUMN_NAME"].ToString();
else
_column = null;
if (row.IsNull("SORT_MODE") == false && (string)row["SORT_MODE"] != "ASC")
_mode = ColumnSortMode.Descending;
if (row.IsNull("COLLATION_NAME") == false)
_collate = row["COLLATION_NAME"].ToString().ToUpperInvariant();
}
}
public object Clone()
{
return new IndexColumn(this);
}
#region IHaveConnectionScope Members
[Browsable(false)]
public string CatalogScope
{
get { return _parent.Table.Catalog; }
}
[Browsable(false)]
public string TableScope
{
get { return _parent.Table.Name; }
}
#endregion
#region IHaveConnection Members
[Browsable(false)]
public ViewTableBase DesignTable
{
get { return _parent.DesignTable; }
}
public DbConnection GetConnection()
{
return ((IHaveConnection)_parent).GetConnection();
}
#endregion
}
public enum IndexTypeEnum
{
Index = 0,
PrimaryKey = 1,
}
[DefaultProperty("Columns")]
internal class Index : IHaveConnection, ICloneable
{
private Table _table;
internal string _name;
private bool _unique;
private List<IndexColumn> _columns = new List<IndexColumn>();
private string _definition;
private bool _calcname;
internal ConflictEnum _conflict = ConflictEnum.Abort;
bool _dirty;
protected Index(Index source)
{
_table = source._table;
_name = source._name;
_unique = source._unique;
_definition = source._definition;
_conflict = source._conflict;
_dirty = source._dirty;
foreach (IndexColumn c in source._columns)
{
IndexColumn copy = ((ICloneable)c).Clone() as IndexColumn;
copy._parent = this;
_columns.Add(copy);
}
}
internal Index(DbConnection cnn, Table table, DataRow index)
{
_table = table;
if (index != null)
{
_name = index["INDEX_NAME"].ToString();
_unique = (bool)index["UNIQUE"];
_definition = index["INDEX_DEFINITION"].ToString();
using (DataTable tbl = cnn.GetSchema("IndexColumns", new string[] { table.Catalog, null, table.Name, Name }))
{
foreach (DataRow row in tbl.Rows)
{
_columns.Add(new IndexColumn(this, row));
}
}
}
}
[DisplayName("Index Type")]
[Category("Storage")]
[Description("Specifies whether this is an index or a primary key.")]
public virtual IndexTypeEnum IndexType
{
get { return IndexTypeEnum.Index; }
}
#region IHaveConnection Members
[Browsable(false)]
public ViewTableBase DesignTable
{
get { return _table; }
}
public DbConnection GetConnection()
{
return ((IHaveConnection)_table).GetConnection();
}
#endregion
internal virtual void WriteSql(StringBuilder builder)
{
string separator = String.Empty;
builder.AppendFormat(CultureInfo.InvariantCulture, "CREATE {0}INDEX [{1}].[{2}] ON [{3}] (", (_unique == true) ? "UNIQUE " : String.Empty, _table.Catalog, Name, _table.Name);
foreach (IndexColumn c in Columns)
{
builder.AppendFormat(CultureInfo.InvariantCulture, "{0}[{1}]", separator, c.Column);
if (c.SortMode != ColumnSortMode.Ascending)
builder.Append(" DESC");
if (String.IsNullOrEmpty(c.Collate) && String.Compare(c.Collate,"BINARY", StringComparison.OrdinalIgnoreCase) != 0)
builder.AppendFormat(CultureInfo.InvariantCulture, " COLLATE {0}", c.Collate.ToUpperInvariant());
separator = ", ";
}
builder.AppendFormat(");");
}
[Browsable(false)]
internal Table Table
{
get { return _table; }
}
[Browsable(false)]
public string OriginalSql
{
get { return _definition; }
}
internal void MakeDirty()
{
_dirty = true;
}
[Browsable(false)]
internal bool IsDirty
{
get { return _dirty; }
}
internal void ClearDirty()
{
_dirty = false;
}
[DefaultValue(false)]
[Description("When set to true, the combination of column(s) of the index must be a unique value.")]
public virtual bool Unique
{
get { return _unique; }
set
{
if (value != _unique)
{
_unique = value;
MakeDirty();
}
}
}
[Browsable(false)]
protected virtual string NamePrefix
{
get { return "IX"; }
}
[Browsable(false)]
protected virtual string NewName
{
get { return "NewIndex"; }
}
[ParenthesizePropertyName(true)]
[RefreshProperties(RefreshProperties.All)]
[Category("Identity")]
[Description("The name of the index.")]
public virtual string Name
{
get
{
if (String.IsNullOrEmpty(_name))
{
if (_calcname == true) return GetHashCode().ToString();
string name = String.Format(CultureInfo.InvariantCulture, "{0}_{1}", NamePrefix, NewName);
if (Columns.Count > 0 && NewName != Table.Name)
{
name = String.Format(CultureInfo.InvariantCulture, "{0}_", NamePrefix);
for (int n = 0; n < Columns.Count; n++)
{
if (n > 0) name += "_";
name += Columns[n].Column;
}
}
int count = 0;
string proposed = name;
_calcname = true;
for (int n = 0; n < _table.Indexes.Count; n++)
{
Index idx = _table.Indexes[n];
proposed = String.Format(CultureInfo.InvariantCulture, "{0}{1}", name, (count > 0) ? count.ToString() : String.Empty);
if (idx.Name == proposed)
{
count++;
n = -1;
}
}
_calcname = false;
return proposed;
}
return _name;
}
set
{
if (value != _name)
{
_name = value;
MakeDirty();
}
}
}
[TypeConverter(typeof(IndexTypeConverter))]
[Editor(typeof(IndexColumnEditor), typeof(UITypeEditor))]
[RefreshProperties(RefreshProperties.All)]
[Category("Source")]
[Description("The column(s) to be indexed.")]
[NotifyParentProperty(true)]
public List<IndexColumn> Columns
{
get { return _columns; }
}
#region ICloneable Members
object ICloneable.Clone()
{
return new Index(this);
}
#endregion
}
public class ColumnsMultiSelectEditor : UITypeEditor
{
private System.Windows.Forms.Design.IWindowsFormsEditorService _edSvc;
private CheckedListBox _list;
private bool _cancel;
public ColumnsMultiSelectEditor()
{
// build selector list
_list = new CheckedListBox();
_list.BorderStyle = BorderStyle.FixedSingle;
_list.CheckOnClick = true;
_list.ThreeDCheckBoxes = false;
_list.KeyPress += new KeyPressEventHandler(_list_KeyPress);
}
[PermissionSet(SecurityAction.LinkDemand, Name = "FullTrust")]
override public UITypeEditorEditStyle GetEditStyle(ITypeDescriptorContext ctx)
{
return UITypeEditorEditStyle.DropDown;
}
[PermissionSet(SecurityAction.LinkDemand, Name = "FullTrust")]
override public object EditValue(ITypeDescriptorContext ctx, IServiceProvider provider, object value)
{
Index idx = ctx.Instance as Index;
Trigger trig = ctx.Instance as Trigger;
ViewTableBase parent = null;
if (idx != null) parent = idx.Table;
else if (trig != null) parent = trig.ViewTableBase;
// initialize editor service
_edSvc = (System.Windows.Forms.Design.IWindowsFormsEditorService)provider.GetService(typeof(System.Windows.Forms.Design.IWindowsFormsEditorService));
if (_edSvc == null)
return value;
if (value == null) value = String.Empty;
if (String.IsNullOrEmpty(value.ToString()) == true) value = String.Empty;
string[] values = value.ToString().Split(',');
// populate the list
_list.Items.Clear();
if (parent is Table)
{
foreach (Column c in ((Table)parent).Columns)
{
CheckState check = CheckState.Unchecked;
for (int n = 0; n < values.Length; n++)
{
if (values[n].Trim() == String.Format(CultureInfo.InvariantCulture, "[{0}]", c.ColumnName))
{
check = CheckState.Checked;
break;
}
}
_list.Items.Add(c.ColumnName, check);
}
}
else
{
try
{
using (DbCommand cmd = trig.GetConnection().CreateCommand())
{
cmd.CommandText = ((View)parent).SqlText;
using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
using (DataTable tbl = reader.GetSchemaTable())
{
foreach (DataRow row in tbl.Rows)
{
CheckState check = CheckState.Unchecked;
for (int n = 0; n < values.Length; n++)
{
if (values[n].Trim() == String.Format(CultureInfo.InvariantCulture, "[{0}]", row[SchemaTableColumn.ColumnName]))
{
check = CheckState.Checked;
break;
}
}
_list.Items.Add(row[SchemaTableColumn.ColumnName].ToString(), check);
}
}
}
}
catch
{
}
}
_list.Height = Math.Min(300, (_list.Items.Count + 1) * _list.Font.Height);
// show the list
_cancel = false;
_edSvc.DropDownControl(_list);
// build return value from checked items on the list
if (!_cancel)
{
// build a comma-delimited string with the checked items
StringBuilder sb = new StringBuilder();
foreach (object item in _list.CheckedItems)
{
if (sb.Length > 0) sb.Append(", ");
sb.AppendFormat("[{0}]", item.ToString());
}
return sb.ToString();
}
// done
return value;
}
// ** event handlers
// close editor if the user presses enter or escape
private void _list_KeyPress(object sender, KeyPressEventArgs e)
{
switch (e.KeyChar)
{
case (char)27:
_cancel = true;
_edSvc.CloseDropDown();
break;
case (char)13:
_edSvc.CloseDropDown();
break;
}
}
}
internal class ColumnsTypeEditor : ObjectSelectorEditor
{
public override UITypeEditorEditStyle GetEditStyle(ITypeDescriptorContext context)
{
return UITypeEditorEditStyle.DropDown;
}
protected override void FillTreeWithData(Selector selector, ITypeDescriptorContext context, IServiceProvider provider)
{
base.FillTreeWithData(selector, context, provider);
IHaveConnectionScope source = context.Instance as IHaveConnectionScope;
ViewTableBase design;
if (source == null) return;
design = source.DesignTable;
if (design.Name != source.TableScope)
{
using (DataTable table = source.GetConnection().GetSchema("Columns", new string[] { source.CatalogScope, null, source.TableScope }))
{
foreach (DataRow row in table.Rows)
{
selector.AddNode(row[3].ToString(), row[3], null);
}
}
}
else
{
Table tbl = design as Table;
if (tbl != null)
{
foreach (Column c in tbl.Columns)
{
selector.AddNode(c.ColumnName, c.ColumnName, null);
}
}
}
}
public override bool IsDropDownResizable
{
get
{
return true;
}
}
}
internal class TablesTypeEditor : ObjectSelectorEditor
{
public override bool IsDropDownResizable
{
get
{
return true;
}
}
public override UITypeEditorEditStyle GetEditStyle(ITypeDescriptorContext context)
{
return UITypeEditorEditStyle.DropDown;
}
protected override void FillTreeWithData(Selector selector, ITypeDescriptorContext context, IServiceProvider provider)
{
base.FillTreeWithData(selector, context, provider);
IHaveConnectionScope source = context.Instance as IHaveConnectionScope;
Table design;
if (source == null) return;
design = source.DesignTable as Table;
using (DataTable table = source.GetConnection().GetSchema("Tables", new string[] { source.CatalogScope }))
{
foreach (DataRow row in table.Rows)
{
bool add = true;
if (design != null && (row[2].ToString() == design.OldName || row[2].ToString() == design.Name))
add = false;
if (add)
selector.AddNode(row[2].ToString(), row[2], null);
}
}
if (design != null)
selector.AddNode(design.Name, design.Name, null);
}
}
}