internal FieldModel(FormField field, MetadataColumn model, object data, SectionLayouts sectionLayout)
{
this.Field = field;
this.Model = model;
this.Data = data;
this.SectionLayout = sectionLayout;
}
public static SqlBuilder Update(this SqlBuilder builder, RowData row, string[] Output = null)
{
if (!row.HasChanges)
{
return(builder);
}
string TableName = row.Table;
string Schema = null;
if (TableName.IndexOf('.') > 0)
{
Schema = TableName.Substring(0, TableName.IndexOf('.'));
TableName = TableName.Substring(TableName.IndexOf('.') + 1);
}
UpdateTable up = builder.Table(TableName, Schema);
Metadata.MetadataTable mt = row.Metadata;
foreach (string key in row.ChangedValues.Keys)
{
MetadataColumn c = mt[key];
SqlStatementExtensions.Set(up, key, row.ChangedValues[key], c.SqlDataType, c.DataType, c.Length, c.Scale);
}
if (Output != null && Output.Length > 0)
{
TableParameterField tpf = up.Output();
foreach (string s in Output)
{
MetadataColumn c = mt[s];
SqlStatementExtensions.Column(tpf, s, c.SqlDataType, c.Length, c.Scale);
}
}
builder.WhereConditions = row.PrimaryKey(builder);
return(builder);
}
private static void BuildField(MetadataColumn col, string TableName, string Alias, FormSection section, MetadataColumn IncludeFrom, bool ForceReadOnly = false)
{
if (col.IsRowGuid)
{
return;
}
FormField field = null;
if (col.IsForeignKey)
{
field = new LookupFormField()
{
LookupSource = LookupSources.SqlBuilder,
Builder = col.ToSqlBuilder()
};
}
else
{
field = new FormField();
}
field.DisplayName = (IncludeFrom != null ? IncludeFrom.DisplayName + " " : "") + col.DisplayName;
field.ID = col.Name;
field.Name = col.Name;
field.Alias = Alias;
field.TableName = TableName;
field.NullText = "Enter " + col.Name;
field.IsReadOnly = ForceReadOnly || col.IsReadOnly || col.IsPrimaryKey;
field.IsRequired = !col.Nullable;
ResolveFieldType(col, field);
section.Fields.Add(field);
}
public void AddIfActive(MetadataColumn column)
{
if (column.Enabled)
{
Arguments.Add(column.ToArgument());
}
}
private static IComparable ValToIComp(Sql.value op, ref MetadataColumn[] metadataColumns, ref RowHolder rowHolder)
{
if (op.IsId)
{
Sql.value.Id idVal = (Sql.value.Id)op;
MetadataColumn mc = QueryProcessingAccessors.GetMetadataColumn(idVal.Item, metadataColumns);
return(QueryProcessingAccessors.MetadataColumnRowsetHolderFetcher(mc, rowHolder));
}
else if (op.IsFloat)
{
return(((Sql.value.Float)op).Item);
}
else if (op.IsInt)
{
return(((Sql.value.Int)op).Item);
}
else if (op.IsString)
{
return(((Sql.value.String)op).Item);
}
else
{
Debug.Fail("Invalid type");
}
throw new InvalidProgramException("Invalid state.");
}
public void AddIfActive(MetadataColumn column)
{
if (column.Enabled)
{
AddColumn(column);
}
}
public static SqlBuilder ToSqlBuilder(this MetadataColumn ForeignKeyColumn)
{
MetadataForeignKey FK = ForeignKeyColumn.Parent.FindForeignKeys(ForeignKeyColumn).First();
MetadataTable PK = SqlBuilder.DefaultMetadata.FindTable(FK.ReferencedSchema + "." + FK.ReferencedTable);
string namecol = PK.GuessTitleColumn();
string[] valuecols = FK.ColumnReferences.Where(x => !x.Column.IsComputed).Select(x => x.ReferencedColumn.Name).ToArray();
SqlBuilder Builder = SqlBuilder.Select()
.From(PK.Name, null, PK.Schema)
.Column(namecol)
.Columns(valuecols)
.Builder();
List <MetadataColumnReference> mcrs = FK.ColumnReferences.Where(x => x.Column.IsComputed).ToList();
if (mcrs.Count > 0)
{
MetadataColumnReference first = mcrs.First();
Builder.From(PK.Name, null, PK.Schema)
.Where(PK.Name, first.ReferencedColumn.Name, SqlOperators.Equal, (object)first.Column.Name.Trim('\"'))
.Builder();
foreach (MetadataColumnReference mcr in mcrs.Skip(1))
{
Builder.From(PK.Name, null, PK.Schema)
.Where(PK.Name, mcr.ReferencedColumn.Name, SqlOperators.Equal, (object)mcr.Column.Name.Trim('\"'))
.Builder();
}
}
return(Builder);
}
/// <summary>
/// Creates a new instance of the IDTSOutputColumn enccapsualted in the MetadataSolumn
/// </summary>
/// <param name="output">Output where the new OutputColumn should be created</param>
/// <returns></returns>
public static MetadataColumn CreateNewInstance(IDTSOutput output)
{
IDTSOutputColumn col = output.OutputColumnCollection.New();
col.Name = Resources.RegExExpressionDefaultOutputColumnName + col.ID.ToString();
RegExExtractionTransformation.SetOutputColumnProperties(col, RegExExtractionTransformation.SetPropertyType.All, null, null);
MetadataColumn c = new MetadataColumn(col);
return(c);
}
private static Table JoinInternal(MetadataHelper helper, string Totable, string ToSchema, Join.JoinTypes JoinType)
{
if (helper.Model.PrimaryKey.Columns.Count != 1)
{
throw new InvalidOperationException("Only tables with one primary key field is supported");
}
MetadataColumn FromField = helper.Model.PrimaryKey.Columns.First();
MetadataTable mt = helper.Table.Builder.Metadata.FindTable(ToSchema + "." + Totable);
JoinConditionGroup jcg = To(helper.Table.Builder, helper.Table, helper.Model, FromField, mt, JoinType, false);
return(jcg.ToTable());
}
private OrderByColumn[] GetOrderByColumns(MetadataColumn[] columns, FSharpList <Tuple <string, Sql.dir> > orderBy)
{
OrderByColumn[] orderByColumns = new OrderByColumn[orderBy.Length];
for (int i = 0; i < orderBy.Length; ++i)
{
MetadataColumn column = QueryProcessingAccessors.GetMetadataColumn(orderBy[i].Item1, columns);
OrderByColumn.Direction direction = orderBy[i].Item2 == Sql.dir.Asc ? OrderByColumn.Direction.Asc : OrderByColumn.Direction.Desc;
orderByColumns[i] = new OrderByColumn(column, direction);
}
return(orderByColumns);
}
public static Table WherePrimaryKey(this MetadataHelper helper, object[] keys)
{
MetadataColumn key = helper.Model.PrimaryKey.Columns.First();
if (helper.Table.Builder.WhereConditions.Conditions.Count == 0)
{
helper.Table.Where(helper.Model.Name, key.Name, SqlOperators.Equal, keys[0]);
}
for (int i = 1; i < helper.Model.PrimaryKey.Columns.Count; i++)
{
helper.Table.Builder.WhereConditions.And(helper.Model.Name, helper.Model.PrimaryKey.Columns[i].Name, SqlOperators.Equal, keys[i]);
}
return(helper.Table);
}
private static Table JoinInternal(MetadataHelper helper, string ForeignKeyField, Join.JoinTypes JoinType)
{
MetadataColumn FromField = null;
if (!helper.Model.Columns.TryGetValue(ForeignKeyField, out FromField))
{
throw new ArgumentException("The Field " + ForeignKeyField + " was not found", "FromField");
}
List <MetadataForeignKey> Fks = new List <MetadataForeignKey>(helper.Model.FindForeignKeys(FromField));
if (Fks.Count != 1)
{
throw new ArgumentException("The Field " + ForeignKeyField + " points to more than one table", "FromField");
}
MetadataForeignKey FK = Fks.First();
string table = FK.ReferencedSchema + "." + FK.ReferencedTable;
MetadataTable ToTable = helper.Table.Builder.Metadata.FindTable(table);
if (ToTable == null)
{
throw new InvalidOperationException("The table '" + table + "' was not found in metadata");
}
JoinConditionGroup jcg = To(helper.Table.Builder, helper.Table, helper.Model, FromField, ToTable, JoinType, true);
Table toTable = jcg.ToTable();
if (FromField.IncludeColumns != null)
{
foreach (string include in FromField.IncludeColumns)
{
string iName = include;
string iAlias = null;
if (include.IndexOf('=') > 0)
{
iName = include.Split('=')[0];
iAlias = include.Split('=')[1];
}
else
{
iAlias = FromField.Name + "_" + iName;
}
toTable.Column(iName, iAlias);
}
}
return(toTable);
}
public static MetadataColumn[] MergeColumns(MetadataColumn[] left, MetadataColumn[] right)
{
MetadataColumn[] res = new MetadataColumn[left.Length + right.Length];
left.CopyTo(res, 0);
// Need to update offsets here.
for (int i = 0; i < right.Length; i++)
{
res[i + left.Length] = new MetadataColumn(
right[i].ColumnId + left.Length, // aligning the offset to match new position.
right[i].TableId,
right[i].ColumnName,
right[i].ColumnType);
}
return(res);
}
public static Table AutoJoin(this MetadataHelper helper, string ForeignKeyField)
{
MetadataColumn FromField = null;
if (!helper.Model.Columns.TryGetValue(ForeignKeyField, out FromField))
{
throw new ArgumentException("The Field " + ForeignKeyField + " was not found", "FromField");
}
if (FromField.Nullable)
{
return(LeftJoin(helper, ForeignKeyField));
}
else
{
return(InnerJoin(helper, ForeignKeyField));
}
}
public static string GuessTitleColumn(this MetadataTable Table)
{
if (!string.IsNullOrEmpty(Table.TitleColumn))
{
return(Table.TitleColumn);
}
else
{
foreach (string s in TitleColumns)
{
MetadataColumn mc = Table.Columns.Values.FirstOrDefault(x => x.Name.IndexOf(s, StringComparison.OrdinalIgnoreCase) >= 0);
if (mc != null)
{
return(mc.Name);
}
}
}
return(Table.PrimaryKey.Columns.First().Name);
}
void o_PropertyChanged(object sender, PropertyChangedEventArgs e)
{
if (e.PropertyName == "Name")
{
UIHelper.UpdateTreeViewNodeName(sender, trvOutputColumns);
}
MetadataColumn col = sender as MetadataColumn;
if (col != null && col.AssociatedTreeNode != null)
{
if (col.RegularExpression == string.Empty || col.RegexOutput == string.Empty || col.InputDataColumnLineageID <= 0)
{
col.AssociatedTreeNode.StateImageIndex = 1;
}
else
{
col.AssociatedTreeNode.StateImageIndex = 0;
}
}
}
public FieldModel GetFieldModel(FormField Field, MetadataTable Model, RowData Data)
{
MetadataColumn mc = null;
MetadataTable mt = Model;
if (Field.ID.StartsWith("__"))
{
return(new FieldModel(Field, null, Data.Column(Field.Name), SectionLayout));
}
if (Field.TableName != Model.Fullname)
{
mt = SqlBuilder.DefaultMetadata.FindTable(Field.TableName);
}
if (!mt.Columns.TryGetValue(Field.Name, out mc))
{
throw new InvalidOperationException("Cannot get a model for " + Field.Name);
}
return(new FieldModel(Field, mc, Data.Column(Field.Alias ?? Field.Name), SectionLayout));
}
public Form BuildForm(SqlBuilder Builder, FormLayouts FormLayout = FormLayouts.Vertical, SectionLayouts SectionLayout = SectionLayouts.VerticalTwoColumns)
{
Form form = new Form();
form.FormLayout = FormLayout;
for (int i = 0; i < Builder.Tables.Count; i++)
{
FormSection section = new FormSection()
{
SectionLayout = SectionLayout
};
Table table = Builder.Tables[i];
MetadataTable mt = Builder.Metadata.FindTable(table.FullName);
// section.Legend = StringMap.Default.GetText(SqlBuilder.DefaultCulture.LCID, mt.Fullname, mt.Name);
section.Legend = mt.DisplayName;
string TableName = mt.Fullname;
if (i == 0)
{
form.TitleColumn = mt.GuessTitleColumn();
}
foreach (Field field in table.FieldList)
{
string name = field.Name;
MetadataColumn mc;
if (mt.Columns.TryGetValue(name, out mc))
{
MetadataColumn includedFrom = mt.Columns.Values.FirstOrDefault(x => x.IncludeColumns != null && x.IncludeColumns.Contains(field.Alias ?? field.Name));
BuildField(mc, TableName, field.Alias, section, includedFrom, i > 0);
}
}
if (section.Fields.Count > 0)
{
form.Sections.Add(section);
}
}
form.Sections[0].Fields.Insert(0, new FormField()
{
Name = "__TABLE", ID = "__TABLE", FieldType = FieldTypes.Input, InputType = InputTypes.text, IsHidden = true
});
return(form);
}
private static void CreateColumn(MetadataColumn mc, MetadataTable mt, StringBuilder sb)
{
string format = "\t\tpublic {0}{1}{2} {3}{4}\r\n\r\n";
string FKformat = "\t\t[FK(\"{0}\",\"{1}\",\"{2}\",\"{3}\")]\r\n";
if (mc.IsForeignKey)
{
List <MetadataForeignKey> FKs = new List <MetadataForeignKey>();
try
{
IEnumerable <MetadataForeignKey> fks = mt.FindForeignKeys(mc);
FKs.AddRange(fks);
}
catch (Exception ex)
{
throw ex;
}
if (FKs.Count == 0)
{
throw new InvalidOperationException(string.Format("The foreign key for Column {0} in table {1} cannot be resolved to one key", mc.Name, mt.Fullname));
}
MetadataForeignKey FK = FKs.First();
sb.AppendFormat(FKformat, FK.ReferencedTable, FK.ColumnReferences.First().ReferencedColumn.Name, FK.ReferencedSchema, FK.Name);
}
if (mc.IsPrimaryKey)
{
sb.AppendLine("\t\t[PK]");
}
bool nullable = mc.Nullable && (mc.DataType != typeof(string) && !mc.DataType.IsByRef && !mc.DataType.IsArray);
if (ClassCreationOptions.ColumnAsProperty)
{
sb.AppendFormat(format, (nullable ? "Nullable<" : ""), mc.DataType.Name, nullable ? "> " : " ", mc.Name, " { get; set; }");
}
else
{
sb.AppendFormat(format, nullable ? "Nullable<" : "", mc.DataType.Name, nullable ? "> " : " ", mc.Name, ";");
}
}
private static void CreateColumn(MetadataColumn mc, MetadataTable mt, StringBuilder sb)
{
string format = "\t\tpublic {0}{1}{2} {3}{4}\r\n\r\n";
string FKformat = "\t\t[FK(\"{0}\",\"{1}\",\"{2}\",\"{3}\")]\r\n";
if (mc.IsForeignKey)
{
List<MetadataForeignKey> FKs = new List<MetadataForeignKey>();
try
{
IEnumerable<MetadataForeignKey> fks = mt.FindForeignKeys(mc);
FKs.AddRange(fks);
}
catch (Exception ex)
{
throw ex;
}
if (FKs.Count == 0)
{
throw new InvalidOperationException(string.Format("The foreign key for Column {0} in table {1} cannot be resolved to one key", mc.Name, mt.Fullname));
}
MetadataForeignKey FK = FKs.First();
sb.AppendFormat(FKformat, FK.ReferencedTable,FK.ColumnReferences.First().ReferencedColumn.Name, FK.ReferencedSchema, FK.Name);
}
if (mc.IsPrimaryKey)
{
sb.AppendLine("\t\t[PK]");
}
bool nullable = mc.Nullable && (mc.DataType != typeof(string) && !mc.DataType.IsByRef && !mc.DataType.IsArray);
if (ClassCreationOptions.ColumnAsProperty)
{
sb.AppendFormat(format, (nullable ? "Nullable<" : ""),mc.DataType.Name, nullable ? "> " : " ", mc.Name, " { get; set; }");
}
else
{
sb.AppendFormat(format, nullable ? "Nullable<" : "", mc.DataType.Name, nullable ? "> " : " ", mc.Name, ";");
}
}
private void btnOk_Click(object sender, EventArgs e)
{
List <int> inputLineages = new List <int>();
foreach (TreeNode outputNode in trvOutputColumns.Nodes)
{
foreach (TreeNode node in outputNode.Nodes)
{
MetadataColumn col = node.Tag as MetadataColumn;
if (col != null && col.InputDataColumnLineageID > 0 && !inputLineages.Contains(col.InputDataColumnLineageID))
{
inputLineages.Add(col.InputDataColumnLineageID);
}
}
}
foreach (IDTSVirtualInputColumn iCol in UIHelper.VirtualInput.VirtualInputColumnCollection)
{
DTSUsageType usageType = inputLineages.Contains(iCol.LineageID) ? DTSUsageType.UT_READONLY : DTSUsageType.UT_IGNORED;
UIHelper.DesignTimeComponent.SetUsageType(UIHelper.Input.ID, UIHelper.VirtualInput, iCol.LineageID, usageType);
}
this.Close();
}
public static IComparable MetadataColumnRowsetHolderFetcher(MetadataColumn mc, RowHolder rowHolder)
{
// TODO: Can't use ColumnId as fetcher.
if (mc.ColumnType.ColumnType == ColumnType.Int)
{
return(rowHolder.GetField <int>(mc.ColumnId));
}
else if (mc.ColumnType.ColumnType == ColumnType.Double)
{
return(rowHolder.GetField <double>(mc.ColumnId));
}
else if (mc.ColumnType.ColumnType == ColumnType.String)
{
// TODO: Since char[] doesn't implement IComparable need to cast it to string.
// This is super slow...
// Consider creating your own string type.
return(new string(rowHolder.GetStringField(mc.ColumnId)));
}
else
{
Debug.Fail("Invalid column type");
throw new InvalidProgramException("Invalid state.");
}
}
private static void ResolveFieldType(MetadataColumn col, FormField field)
{
switch (col.SqlDataType)
{
case System.Data.SqlDbType.Text:
case System.Data.SqlDbType.NText:
case System.Data.SqlDbType.VarChar:
case System.Data.SqlDbType.NVarChar:
case System.Data.SqlDbType.Char:
case System.Data.SqlDbType.NChar:
field.FieldType = FieldTypes.Input;
field.InputType = InputTypes.text;
if (col.Length <= 0)
{
field.FieldType = FieldTypes.TextArea;
}
field.MaxLength = col.Length;
break;
case System.Data.SqlDbType.Date:
field.FieldType = FieldTypes.Input;
field.InputType = InputTypes.date;
break;
case System.Data.SqlDbType.SmallDateTime:
case System.Data.SqlDbType.DateTime:
case System.Data.SqlDbType.DateTime2:
field.FieldType = FieldTypes.Input;
field.InputType = InputTypes.datetime_local;
break;
case System.Data.SqlDbType.DateTimeOffset:
field.FieldType = FieldTypes.Input;
field.InputType = InputTypes.datetime;
break;
case System.Data.SqlDbType.Time:
field.FieldType = FieldTypes.Input;
field.InputType = InputTypes.time;
break;
case System.Data.SqlDbType.Bit:
field.FieldType = FieldTypes.Checkbox;
break;
case System.Data.SqlDbType.Decimal:
case System.Data.SqlDbType.Float:
case System.Data.SqlDbType.Int:
case System.Data.SqlDbType.Money:
case System.Data.SqlDbType.BigInt:
case System.Data.SqlDbType.Real:
case System.Data.SqlDbType.SmallInt:
case System.Data.SqlDbType.SmallMoney:
case System.Data.SqlDbType.TinyInt:
field.FieldType = FieldTypes.Input;
field.InputType = InputTypes.number;
break;
case System.Data.SqlDbType.VarBinary:
case System.Data.SqlDbType.Timestamp:
case System.Data.SqlDbType.Structured:
case System.Data.SqlDbType.Binary:
case System.Data.SqlDbType.Variant:
case System.Data.SqlDbType.Xml:
case System.Data.SqlDbType.Image:
case System.Data.SqlDbType.Udt:
case System.Data.SqlDbType.UniqueIdentifier:
default:
field.FieldType = FieldTypes.Input;
field.IsHidden = true;
return;
}
// Special cases
if (col.IsForeignKey)
{
field.FieldType = FieldTypes.SelectList;
}
}
private static void ResolveFieldType(MetadataColumn col, FormField field)
{
switch (col.SqlDataType)
{
case System.Data.SqlDbType.Text:
case System.Data.SqlDbType.NText:
case System.Data.SqlDbType.VarChar:
case System.Data.SqlDbType.NVarChar:
case System.Data.SqlDbType.Char:
case System.Data.SqlDbType.NChar:
field.FieldType = FieldTypes.Input;
field.InputType = InputTypes.text;
if (col.Length <= 0)
{
field.FieldType = FieldTypes.TextArea;
}
field.MaxLength = col.Length;
break;
case System.Data.SqlDbType.Date:
field.FieldType = FieldTypes.Input;
field.InputType = InputTypes.date;
break;
case System.Data.SqlDbType.SmallDateTime:
case System.Data.SqlDbType.DateTime:
case System.Data.SqlDbType.DateTime2:
field.FieldType = FieldTypes.Input;
field.InputType = InputTypes.datetime_local;
break;
case System.Data.SqlDbType.DateTimeOffset:
field.FieldType = FieldTypes.Input;
field.InputType = InputTypes.datetime;
break;
case System.Data.SqlDbType.Time:
field.FieldType = FieldTypes.Input;
field.InputType = InputTypes.time;
break;
case System.Data.SqlDbType.Bit:
field.FieldType = FieldTypes.Checkbox;
break;
case System.Data.SqlDbType.Decimal:
case System.Data.SqlDbType.Float:
case System.Data.SqlDbType.Int:
case System.Data.SqlDbType.Money:
case System.Data.SqlDbType.BigInt:
case System.Data.SqlDbType.Real:
case System.Data.SqlDbType.SmallInt:
case System.Data.SqlDbType.SmallMoney:
case System.Data.SqlDbType.TinyInt:
field.FieldType = FieldTypes.Input;
field.InputType = InputTypes.number;
break;
case System.Data.SqlDbType.VarBinary:
case System.Data.SqlDbType.Timestamp:
case System.Data.SqlDbType.Structured:
case System.Data.SqlDbType.Binary:
case System.Data.SqlDbType.Variant:
case System.Data.SqlDbType.Xml:
case System.Data.SqlDbType.Image:
case System.Data.SqlDbType.Udt:
case System.Data.SqlDbType.UniqueIdentifier:
default:
field.FieldType = FieldTypes.Input;
field.IsHidden = true;
return;
}
// Special cases
if (col.IsForeignKey)
{
field.FieldType = FieldTypes.SelectList;
}
}
internal FieldModel(FormField field, MetadataColumn model, object data, SectionLayouts sectionLayout)
{
this.Field = field;
this.Model = model;
this.Data = data;
this.SectionLayout = sectionLayout;
}
public static SqlBuilder Update(this RowData row, bool OnlyChanges = false, bool OutputPrimaryKey = false, string[] OutputFields = null)
{
if (OnlyChanges && !row.HasChanges)
{
return(null);
}
SqlBuilder builder = SqlBuilder.Update();
string TableName = row.Table;
string Schema = null;
if (TableName.IndexOf('.') > 0)
{
Schema = TableName.Substring(0, TableName.IndexOf('.'));
TableName = TableName.Substring(TableName.IndexOf('.') + 1);
}
UpdateTable up = builder.Table(TableName, Schema);
Metadata.MetadataTable mt = row.Metadata;
if (OnlyChanges)
{
foreach (string key in row.ChangedValues.Keys)
{
object o;
MetadataColumn c;
if (row.ChangedValues.TryGetValue(key, out o) && mt.Columns.TryGetValue(key, out c) && !c.IsReadOnly)
{
SqlStatementExtensions.Set(up, key, o, c.SqlDataType, c.DataType, c.Length, c.Precision, c.Scale);
}
else
{
throw new InvalidOperationException("Cannot get the changed column " + key);
}
}
}
else
{
foreach (string key in row.Columns)
{
MetadataColumn c;
if (mt.Columns.TryGetValue(key, out c) && !c.IsReadOnly)
{
SqlStatementExtensions.Set(up, key, row.Column(key), c.SqlDataType, c.DataType, c.Length, c.Precision, c.Scale);
}
}
}
if (OutputPrimaryKey)
{
TableParameterField tpf = up.Output();
foreach (MetadataColumn key in mt.PrimaryKey.Columns)
{
SqlStatementExtensions.Column(tpf, key.Name, key.SqlDataType, key.Length, key.Precision, key.Scale);
}
}
if (OutputFields != null && OutputFields.Length > 0)
{
TableParameterField tpf = up.Output();
foreach (string s in OutputFields)
{
MetadataColumn c = mt[s];
SqlStatementExtensions.Column(tpf, s, c.SqlDataType, c.Length, c.Precision, c.Scale);
}
}
builder.WhereConditions = row.PrimaryKey(builder);
return(builder);
}
private static JoinConditionGroup To(SqlBuilder Builder, Table FromSqlTable, MetadataTable FromTable, MetadataColumn FromField, MetadataTable ToTable, Join.JoinTypes JoinType, bool PreferForeignKeyOverPrimaryKey = true)
{
MetadataDatabase mdb = Builder.Metadata;
List <MetadataForeignKey> Fks = null;
MetadataForeignKey FK = null;
Join j = null;
MetadataColumnReference mcr = null;
JoinConditionGroup jcg = null;
if (FromField.IsPrimaryKey)
{
if (!FromField.IsForeignKey || !PreferForeignKeyOverPrimaryKey)
{
Fks = ToTable.ForeignKeys.Values.Where(x => x.ReferencedTable.Equals(FromTable.Name) && x.ReferencedSchema.Equals(FromTable.Schema) && x.ColumnReferences.Any(y => y.ReferencedColumn.Equals(FromField))).ToList();
if (Fks.Count != 1)
{
throw new InvalidOperationException(string.Format("The column '{0}' is referenced by {1} keys in the table {2}. Expected 1. Make the join manually",
FromField.Name, Fks.Count, ToTable.Fullname));
}
FK = Fks.First();
j = SqlStatementExtensions.MakeJoin(JoinType, FromSqlTable, ToTable.Name, null, ToTable.Schema.Equals("dbo") ? null : ToTable.Schema);
mcr = FK.ColumnReferences.First();
jcg = j.On(FromField.Name, SqlOperators.Equal, mcr.Name);
return(jcg);
}
}
if (FromField.IsForeignKey)
{
Fks = new List <MetadataForeignKey>(FromTable.FindForeignKeys(FromField, ToTable.Name));
if (Fks.Count != 1)
{
throw new InvalidOperationException(string.Format("The column '{0}' resolves to {1} keys in the table {2}. Expected 1. Make the join manually",
FromField.Name, Fks.Count, ToTable.Fullname));
}
FK = Fks.First();
j = SqlStatementExtensions.MakeJoin(JoinType, FromSqlTable, ToTable.Name, null, ToTable.Schema.Equals("dbo") ? null : ToTable.Schema);
mcr = FK.ColumnReferences.First();
jcg = j.On(FromField.Name, SqlOperators.Equal, mcr.ReferencedColumn.Name);
if (FK.ColumnReferences.Count > 1)
{
foreach (MetadataColumnReference mcr2 in FK.ColumnReferences.Skip(1))
{
if (mcr2.Name.StartsWith("\""))
{
// its a value reference
// jcg.And(FK.ReferencedTable, mcr2.ReferencedColumn.Name, SqlOperators.Equal, mcr2.Name.Trim('\"'),null);
decimal d;
object o;
if (decimal.TryParse(mcr2.Name.Trim('\"'), out d))
{
o = d;
}
else
{
o = (object)mcr2.Name.Trim('\"');
}
jcg.And(mcr2.ReferencedColumn.Name, SqlOperators.Equal, o, null);
}
else
{
jcg.And(mcr2.Column.Name, SqlOperators.Equal, mcr2.ReferencedColumn.Name);
}
}
}
return(jcg);
}
throw new ArgumentException(string.Format("The Column '{0}' in the table '{1}' must be a foreign key or primary key", FromField.Name, FromTable.Fullname), "FromField");
}
public Task <IPhysicalOperator <RowHolder> > BuildStatement(Sql.sqlStatement statement, ITransaction tran, IPhysicalOperator <RowHolder> source, InputStringNormalizer inputStringNormalizer)
{
Sql.columnSelect[] columns = new Sql.columnSelect[0];
bool isStar = false;
string[] projections = new string[0];
if (statement.GroupBy.Any())
{
// no job for me, this is group by.
return(Task.FromResult(source));
}
if (!statement.Columns.IsStar)
{
columns = (((Sql.selectType.ColumnList)statement.Columns).Item).ToArray();
if (columns.Any(c => c.IsAggregate == true))
{
// No job for me, this is aggregation.
return(Task.FromResult(source));
}
projections = columns
.Where(c => c.IsProjection == true)
.Select(c => ((Sql.columnSelect.Projection)c).Item).ToArray();
}
else
{
isStar = true;
}
int?topRows = null;
if (FSharpOption <int> .get_IsSome(statement.Top))
{
topRows = statement.Top.Value;
if (topRows < 1)
{
throw new InvalidTopCountException();
}
}
if (isStar)
{
// no need for project, just return everything.
IPhysicalOperator <RowHolder> projectOp = new PhyOpProject(source, topRows);
return(Task.FromResult(projectOp));
}
else
{
// Project Op.
List <MetadataColumn> columnMapping = new List <MetadataColumn>();
foreach (string columnName in projections)
{
MetadataColumn mc = QueryProcessingAccessors.GetMetadataColumn(columnName, source.GetOutputColumns());
columnMapping.Add(mc);
}
IPhysicalOperator <RowHolder> projectOp = new PhyOpProject(source, columnMapping.Select(mc => mc.ColumnId).ToArray(), topRows);
return(Task.FromResult(projectOp));
}
}
private void BuildVirtualKeys(List<VirtualForeignKey> keys, MetadataTable mt, MetadataDatabase mdb, bool PrimaryKeyIndexOnly)
{
foreach (VirtualForeignKey vfk in keys)
{
MetadataForeignKey mfk = new MetadataForeignKey()
{
ID = 0,
Name = vfk.values[0].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)[0],
ReferencedKey = "",
ReferencedTable = vfk.values[0].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)[1],
ReferencedSchema = vfk.values[0].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)[2],
Parent = mt,
IsVirtual = true
};
MetadataTable mtref = null;
if (!mdb.Tables.TryGetValue(mfk.ReferencedSchema + "." + mfk.ReferencedTable, out mtref))
{
bool self = false;
if (mfk.ReferencedSchema == mt.Schema && mfk.ReferencedTable == mt.Name)
{
self = true;
}
mtref = BuildMetadata(mdb, mfk.ReferencedTable, mfk.ReferencedSchema, PrimaryKeyIndexOnly, self);
}
for (int i = 1; i < vfk.values.Length; i++)
{
MetadataColumnReference mcf = new MetadataColumnReference()
{
ReferencedColumn = mtref[vfk.values[i].Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries)[1]],
};
string from = vfk.values[i].Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries)[0];
if (from.StartsWith("\""))
{
MetadataColumn mcVirtual = new MetadataColumn()
{
Name = from,
IsForeignKey = true,
ID = 0,
SqlDataType = SqlDbType.NVarChar,
Nullable = false,
Length = 0,
IsComputed = true,
DataType = typeof(string),
};
mcf.Column = mcVirtual;
mcf.Name = from;
}
else
{
mcf.Column = mt[from];
mcf.Name = mcf.Column.Name;
}
mfk.ColumnReferences.Add(mcf);
}
mt.ForeignKeys.AddOrUpdate(mfk.Name, mfk, (k, v) => { return mfk; });
}
}
private static void BuildField(MetadataColumn col, string TableName, string Alias, FormSection section, MetadataColumn IncludeFrom, bool ForceReadOnly = false)
{
if (col.IsRowGuid)
{
return;
}
FormField field = null;
if (col.IsForeignKey)
{
field = new LookupFormField()
{
LookupSource = LookupSources.SqlBuilder,
Builder = col.ToSqlBuilder()
};
}
else
{
field = new FormField();
}
field.DisplayName = (IncludeFrom != null ? IncludeFrom.DisplayName + " " : "") + col.DisplayName;
field.ID = col.Name;
field.Name = col.Name;
field.Alias = Alias;
field.TableName = TableName;
field.NullText = "Enter " + col.Name;
field.IsReadOnly = ForceReadOnly || col.IsReadOnly || col.IsPrimaryKey;
field.IsRequired = !col.Nullable;
ResolveFieldType(col, field);
section.Fields.Add(field);
}
public static void BuildField(MetadataColumn Column, string Alias, FormSection Section, bool ForceReadonly = false)
{
BuildField(Column, Column.Parent.Schema + "." + Column.Parent.Name, Alias, Section, null, ForceReadonly);
}
public static void BuildField(MetadataColumn Column, string Alias, FormSection Section, bool ForceReadonly = false)
{
BuildField(Column, Column.Parent.Schema + "." + Column.Parent.Name, Alias, Section, null, ForceReadonly);
}
private MetadataTable BuildMetadata(MetadataDatabase mdb, Microsoft.SqlServer.Management.Smo.Table table, bool PrimaryKeyIndexOnly = true, bool SelfJoin = false)
{
MetadataTable mt = null;
List<VirtualForeignKey> VirtualKeys = new List<VirtualForeignKey>();
table.Refresh();
if (mdb.Tables.TryGetValue(table.Name, out mt))
{
return mt;
}
mt = new MetadataTable()
{
ID = table.ID,
Schema = table.Schema,
Name = table.Name,
//Parent = mdb
};
mt.TitleColumn = GetExtendedProperty("TitleColumn", table.ExtendedProperties);
string[] values = GetExtendedProperty("DisplayName", table.ExtendedProperties, new char[] { '\r', '\n' });
if (values != null)
{
foreach (string value in values)
{
string[] v = value.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries);
mt.DisplayNames.TryAdd(Convert.ToInt32(v[0]), v[1]);
}
}
values = GetExtendedProperty("Lists", table.ExtendedProperties, new char[] { '\r', '\n' });
if (values != null)
{
foreach (string value in values)
{
string[] v = value.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries);
List<string> v2 = v[1].Split(',').ToList();
if (!mt.ListDefinitions.TryAdd(v[0].Trim(), v2))
{
throw new InvalidOperationException(string.Format("The TinySql.Lists extended property is invalid for the table '{0}'", table.Name));
}
}
}
foreach (Microsoft.SqlServer.Management.Smo.Column column in table.Columns)
{
try
{
MetadataColumn col = new MetadataColumn()
{
ID = column.ID,
Parent = mt,
//Database = mdb,
Name = column.Name,
Collation = column.Collation,
Default = column.Default,
IsComputed = column.Computed,
ComputedText = column.ComputedText,
IsPrimaryKey = column.InPrimaryKey,
IsIdentity = column.Identity,
IsForeignKey = column.IsForeignKey,
IdentityIncrement = column.IdentityIncrement,
IdentitySeed = column.IdentitySeed,
Nullable = column.Nullable,
IsRowGuid = column.RowGuidCol
};
BuildColumnDataType(col, column);
values = GetExtendedProperty("DisplayName", column.ExtendedProperties, new char[] { '\r', '\n' });
if (values != null)
{
foreach (string value in values)
{
if (!value.Contains("="))
{
col.DisplayNames.TryAdd(SqlBuilder.DefaultCulture.LCID, value);
}
else
{
string[] v = value.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries);
col.DisplayNames.TryAdd(Convert.ToInt32(v[0]), v[1]);
}
}
}
col.IncludeColumns = GetExtendedProperty("IncludeColumns", column.ExtendedProperties, new char[] { ',' });
values = GetExtendedProperty("FK", column.ExtendedProperties, new char[] { '\r', '\n' });
if (values != null)
{
VirtualKeys.Add(new VirtualForeignKey() { Column = col, values = values });
col.IsForeignKey = true;
}
mt.Columns.AddOrUpdate(col.Name, col, (k, v) => { return col; });
}
catch (Exception exColumn)
{
throw new InvalidOperationException(string.Format("Unable to generate the column {0}", column.Name), exColumn);
}
}
foreach (Index idx in table.Indexes)
{
if (!PrimaryKeyIndexOnly || idx.IndexKeyType == IndexKeyType.DriPrimaryKey)
{
Key key = new Key()
{
ID = idx.ID,
Parent = mt,
Database = mdb,
Name = idx.Name,
IsUnique = idx.IsUnique,
IsPrimaryKey = idx.IndexKeyType == IndexKeyType.DriPrimaryKey
};
foreach (IndexedColumn c in idx.IndexedColumns)
{
key.Columns.Add(mt[c.Name]);
}
mt.Indexes.AddOrUpdate(key.Name, key, (k, v) => { return key; });
}
}
if (!SelfJoin)
{
foreach (ForeignKey FK in table.ForeignKeys)
{
MetadataForeignKey mfk = new MetadataForeignKey()
{
ID = FK.ID,
Parent = mt,
Database = mdb,
Name = FK.Name,
ReferencedKey = FK.ReferencedKey,
ReferencedSchema = FK.ReferencedTableSchema,
ReferencedTable = FK.ReferencedTable
};
MetadataTable mtref = null;
if (!mdb.Tables.TryGetValue(mfk.ReferencedSchema + "." + mfk.ReferencedTable, out mtref))
{
bool self = false;
if ((mfk.ReferencedSchema == mt.Schema && mfk.ReferencedTable == mt.Name) || TablesInProgress.Contains(mfk.ReferencedSchema + "." + mfk.ReferencedTable))
{
self = true;
}
TablesInProgress.Add(mfk.ReferencedSchema + "." + mfk.ReferencedTable);
mtref = BuildMetadata(mdb, mfk.ReferencedTable, mfk.ReferencedSchema, PrimaryKeyIndexOnly, self);
}
foreach (ForeignKeyColumn cc in FK.Columns)
{
mfk.ColumnReferences.Add(new MetadataColumnReference()
{
Name = cc.Name,
Column = mt[cc.Name],
ReferencedColumn = mtref[cc.ReferencedColumn]
});
}
mt.ForeignKeys.AddOrUpdate(mfk.Name, mfk, (key, existing) =>
{
return mfk;
});
}
}
if (VirtualKeys.Count > 0)
{
BuildVirtualKeys(VirtualKeys, mt, mdb, PrimaryKeyIndexOnly);
}
mdb.Tables.AddOrUpdate(mt.Schema + "." + mt.Name, mt, (key, existing) =>
{
return mt;
});
return mt;
}
// TODO: This is just bad.
// It is very hard to keep all type -> agg mappings.
// Needs refactoring.
public static void ApplyAgg(MetadataColumn mc, ref RowHolder inputRowHolder, Sql.aggType aggType, ref RowHolder stateRowHolder)
{
// TODO: Can't use ColumnId as fetcher.
if (mc.ColumnType.ColumnType == ColumnType.Int)
{
int inputValue = inputRowHolder.GetField <int>(mc.ColumnId);
int stateValue = stateRowHolder.GetField <int>(mc.ColumnId);
if (aggType.IsMax)
{
if (inputValue.CompareTo(stateValue) == 1)
{
// Update state.
// TODO: boxing/unboxing hurts perf.
stateRowHolder.SetField <int>(mc.ColumnId, inputValue);
}
}
else if (aggType.IsMin)
{
if (inputValue.CompareTo(stateValue) == -1)
{
// TODO: boxing/unboxing hurts perf.
stateRowHolder.SetField <int>(mc.ColumnId, inputValue);
}
}
else if (aggType.IsSum)
{
stateRowHolder.SetField <int>(mc.ColumnId, inputValue + stateValue);
}
else if (aggType.IsCount)
{
stateRowHolder.SetField <int>(mc.ColumnId, 1 + stateValue);
}
else
{
throw new InvalidProgramException("Aggregate not supported.");
}
}
else if (mc.ColumnType.ColumnType == ColumnType.Double)
{
double inputValue = inputRowHolder.GetField <double>(mc.ColumnId);
double stateValue = stateRowHolder.GetField <double>(mc.ColumnId);
if (aggType.IsMax)
{
if (inputValue.CompareTo(stateValue) == 1)
{
// Update state.
// TODO: boxing/unboxing hurts perf.
stateRowHolder.SetField <double>(mc.ColumnId, (double)inputValue);
}
}
else if (aggType.IsMin)
{
if (inputValue.CompareTo(stateValue) == -1)
{
// TODO: boxing/unboxing hurts perf.
stateRowHolder.SetField <double>(mc.ColumnId, inputValue);
}
}
else if (aggType.IsSum)
{
stateRowHolder.SetField <double>(mc.ColumnId, inputValue + stateValue);
}
else
{
throw new InvalidProgramException("Aggregate not supported.");
}
}
else if (mc.ColumnType.ColumnType == ColumnType.String)
{
// TODO: Since char[] doesn't implement IComparable need to cast it to string.
// This is super slow...
// Consider creating your own string type.
string inputValue = new string(inputRowHolder.GetStringField(mc.ColumnId));
string stateValue = new string(stateRowHolder.GetStringField(mc.ColumnId));
if (aggType.IsMax)
{
if (inputValue.CompareTo(stateValue) == 1)
{
// Update state.
// TODO: boxing/unboxing hurts perf.
stateRowHolder.SetField(mc.ColumnId, inputValue.ToCharArray());
}
}
else if (aggType.IsMin)
{
if (inputValue.CompareTo(stateValue) == -1)
{
// TODO: boxing/unboxing hurts perf.
stateRowHolder.SetField(mc.ColumnId, inputValue.ToCharArray());
}
}
else
{
throw new InvalidProgramException("Aggregate not supported.");
}
}
else
{
Debug.Fail("Invalid column type");
throw new InvalidProgramException("Invalid state.");
}
}
private void BuildColumnDataType(MetadataColumn column, Column col)
{
DataType ColumnType = col.DataType;
switch (ColumnType.SqlDataType)
{
case SqlDataType.UserDefinedDataType:
SqlDatabase.UserDefinedDataTypes.Refresh(true);
UserDefinedDataType udt = SqlDatabase.UserDefinedDataTypes[ColumnType.Name];
column.SqlDataType = (SqlDbType)Enum.Parse(typeof(SqlDbType), udt.SystemType, true);
column.Length = udt.Length;
column.Scale = ColumnType.NumericScale;
column.Precision = ColumnType.NumericPrecision;
break;
case SqlDataType.SysName:
column.SqlDataType = SqlDbType.NVarChar;
column.Length = 128;
break;
case SqlDataType.UserDefinedType:
column.SqlDataType = System.Data.SqlDbType.Udt;
column.Scale = ColumnType.NumericScale;
column.Precision = ColumnType.NumericPrecision;
break;
case SqlDataType.NVarCharMax:
column.SqlDataType = System.Data.SqlDbType.NVarChar;
break;
case SqlDataType.VarCharMax:
column.SqlDataType = System.Data.SqlDbType.VarChar;
break;
case SqlDataType.VarBinaryMax:
column.SqlDataType = SqlDbType.VarBinary;
break;
case SqlDataType.Numeric:
column.SqlDataType = SqlDbType.Decimal;
column.Scale = ColumnType.NumericScale;
column.Precision = ColumnType.NumericPrecision;
break;
case SqlDataType.UserDefinedTableType:
case SqlDataType.HierarchyId:
case SqlDataType.Geometry:
case SqlDataType.Geography:
column.SqlDataType = SqlDbType.Variant;
column.Scale = ColumnType.NumericScale;
column.Precision = ColumnType.NumericPrecision;
break;
default:
column.SqlDataType = (SqlDbType)Enum.Parse(typeof(SqlDbType), ColumnType.SqlDataType.ToString());
switch (column.SqlDataType)
{
case SqlDbType.Decimal:
case SqlDbType.Float:
case SqlDbType.Money:
case SqlDbType.Real:
case SqlDbType.SmallMoney:
column.Scale = ColumnType.NumericScale;
column.Precision = ColumnType.NumericPrecision;
break;
default:
break;
}
break;
}
column.Length = ColumnType.MaximumLength;
column.DataType = ConvertSqlTypeToType(column.SqlDataType);
}
public OrderByColumn(MetadataColumn column, Direction direction)
{
this.column = column;
this.direction = direction;
}
