internal FromManyChainer(Chainer prev,
TableArgument firstTable,
TableArgument secondTable,
TableArgument[] otherTables)
: base(prev)
{
CheckNullAndThrow(Arg(() => firstTable, firstTable));
CheckNullAndThrow(Arg(() => secondTable, secondTable));
CheckNullAndThrow(Arg(() => otherTables, otherTables));
if (otherTables.Where(table => table == null).Any())
{
Throw(QueryTalkExceptionType.ArgumentNull, "table = null");
}
Build = (buildContext, buildArgs) =>
{
StringBuilder sql = Text.GenerateSql(200);
ProcessTable(buildContext, buildArgs, firstTable, sql);
ProcessTable(buildContext, buildArgs, secondTable, sql);
Array.ForEach(otherTables, table =>
{
ProcessTable(buildContext, buildArgs, table, sql);
});
TryThrow(buildContext);
return(sql.ToString());
};
}
internal EndRankingChainer(Chainer prev)
: base(prev)
{
Build = (buildContext, buildArgs) =>
{
StringBuilder sql = new StringBuilder();
Chainer node = GetPrev <RankingChainer>();
while (node != this)
{
var append = node.Build(buildContext, buildArgs);
TryThrow(buildContext);
if (append != null)
{
sql.Append(append);
}
node = node.Next;
}
return(Text.GenerateSql(200)
.Append(sql.TrimEnd())
.Append(Text.RightBracket)
.ToString());
};
}
internal EndTableChainer(Chainer prev)
: base(prev)
{
var beginTable = prev.GetPrev <BeginTableChainer>();
if (!beginTable.HasColumns)
{
Throw(QueryTalkExceptionType.InvalidTableDesign,
String.Format("table = {0}", beginTable.TableName));
}
Build = (buildContext, buildArgs) =>
{
var sql = Text.GenerateSql(100)
.NewLine(Text.RightBracket)
.Terminate();
if (beginTable.IsFill)
{
BeginTableChainer.Fill(sql, beginTable.TableName, beginTable.DataView);
}
return(sql.ToString());
};
}
internal GroupByChainer(Chainer prev, GroupingArgument[] columns)
: base(prev)
{
Query.Clause.GroupBy = this;
CheckNullOrEmptyAndThrow(Argc(() => columns, columns));
Query.AddArguments(columns);
Build = (buildContext, buildArgs) =>
{
var sql = Text.GenerateSql(100)
.NewLine(Text.GroupBy).S()
.Append(GroupingArgument.Concatenate(columns, buildContext, buildArgs, false));
if (IsWithCube == true)
{
sql.NewLine(Text.WithCube);
}
else if (IsWithCube == false)
{
sql.NewLine(Text.WithRollup);
}
TryThrow(buildContext);
return(sql.ToString());
};
}
private static void TranslateGraphDeclareTable(DbNode node, Designer root, out StringBuilder nodeTableVar)
{
var nodeTableVarName = GetTableVarName(node.Index);
nodeTableVar = Text.GenerateSql(200)
.Append(Text.Declare).S()
.Append(nodeTableVarName).Append(Text._As_).Append(Text.Table).S()
.Append(Text.LeftBracket);
// add columns of all keys
// note:
// We add all keys in order to assure that all join or where relations will be properly built.
bool first = true;
foreach (var column in node.Map.GetKeys())
{
var nullableDef = column.IsNullable ? Text.Null : Text.NotNull;
if (!first)
{
nodeTableVar.AppendComma().S();
}
nodeTableVar.Append(column.Name.Sql).S().Append(column.DataType.Build()).S().Append(nullableDef);
first = false;
}
nodeTableVar.Append(Text.RightBracket)
.Terminate();
root.TryAddVariableOrThrow(new Variable(new DataType(DT.TableVariable), nodeTableVarName), Text.Method.DesignTable, false);
}
internal DropTempTableIndexChainer(Chainer prev, string table, string index)
: base(prev)
{
CheckNullAndThrow(Arg(() => table, table));
CheckNullAndThrow(Arg(() => index, index));
if (!GetRoot().TempTableExists(table))
{
Throw(QueryTalkExceptionType.UnknownTempTable,
String.Format("temp table = {0}{1} index = {2}", table, Environment.NewLine, index));
}
if (Common.CheckIdentifier(index) != IdentifierValidity.RegularIdentifier)
{
Throw(QueryTalkExceptionType.InvalidIndexName, ArgVal(() => index, index));
}
Build = (buildContext, buildArgs) =>
{
return(Text.GenerateSql(70)
.NewLine(Text.DropIndex).S()
.Append(index).S()
.Append(Text.On).S()
.Append(table)
.Terminate()
.ToString());
};
}
internal ColumnAsChainer(DbColumn column, string alias)
: base(null, alias)
{
if (!CheckNull(Arg(() => column, column)))
{
return;
}
if (chainException != null)
{
return;
}
_columnName = alias;
Build = (buildContext, buildArgs) =>
{
var sql = Text.GenerateSql(20)
.Append(column.Build(buildContext, buildArgs))
.Append(Text._As_)
.Append(Filter.Delimit(Name))
.ToString();
TryThrow(buildContext);
return(sql);
};
}
// initalize by value
private void Initialize(DeclareArgument value)
{
var root = GetRoot();
chainMethod = Text.Method.Declare;
CheckAndThrow();
var dataType = Mapping.ClrMapping[value.ArgType].DefaultDataType;
var variable = new Variable(0, _variableName, dataType, IdentifierType.SqlVariable);
root.TryAddVariableOrThrow(variable, chainMethod, false);
Build = (buildContext, buildArgs) =>
{
var sql = Text.GenerateSql(50)
.NewLine(Text.Declare).S()
.Append(_variableName).S()
.Append(Text.As).S()
.Append(dataType.Build())
.Terminate();
sql.NewLine(Text.Set).S()
.Append(_variableName)
.Append(Text._Equal_)
.Append(value.Build(buildContext, buildArgs));
sql.Terminate();
return(sql.ToString());
};
}
internal string BaseBuildMethod(BuildContext buildContext, BuildArgs buildArgs)
{
string tableSql = TableArgument.Build(buildContext, buildArgs);
TryThrow(buildContext);
StringBuilder sql = Text.GenerateSql(30)
.NewLine(Keyword);
// subquery:
if (TableArgument.Original is View)
{
sql.S().Append(Text.LeftBracket)
.Append(tableSql)
.NewLine(Text.RightBracket);
}
// table:
else
{
sql.S().Append(tableSql);
}
// table with alias
if (_alias != null)
{
return(sql
.S().Append(Text.As)
.S().Append(Filter.Delimit(_alias.Name))
.ToString());
}
else
{
return(sql.ToString());
}
}
private void Initialize(NonSelectColumnArgument column, DataType dataTypeDef)
{
IsNullable = null;
CheckNullAndThrow(Arg(() => column, column));
TryThrow(dataTypeDef.Exception);
Prev.GetPrev <BeginTableChainer>().SetHasColumns();
Build = (buildContext, buildArgs) =>
{
var sql = Text.GenerateSql(300);
sql.AppendLine();
sql.Append(Text.TwoSpaces);
if (!(Prev is BeginTableChainer && !((BeginTableChainer)Prev).IsDesignedByType))
{
sql.Append(Text.Comma);
}
sql.Append(column.Build(buildContext, buildArgs)).S()
.Append(dataTypeDef.Build());
TryThrow(column.Exception);
if (Identity != null)
{
sql.S().Append(Text.Identity)
.EncloseLeft()
.Append(Identity.Seed)
.AppendComma()
.Append(Identity.Increment)
.EncloseRight();
}
if (IsNullable == true)
{
sql.S().Append(Text.Null);
}
else if (IsNullable == false)
{
sql.S().Append(Text.NotNull);
}
if (Check != null)
{
sql.S().Append(Text.Check)
.EncloseLeft().Append(Check.Build(buildContext, buildArgs)).EncloseRight();
TryThrow(buildContext);
}
if (Default != null)
{
sql.S().Append(Text.Default)
.EncloseLeft().Append(Default.Build(buildContext, buildArgs)).EncloseRight();
TryThrow(buildContext);
}
return(sql.ToString());
};
}
private void _Body(Column[] columns, bool isDistinct)
{
IsCollect = false;
UseStringAsValue = false;
Query.Clause.Select = this;
if (columns == null)
{
_columns = new Column[] { Designer.Null };
}
else if (columns.Length == 0)
{
_columns = new Column[] { Wall.Text.Asterisk };
_isEmpty = true;
}
else
{
_columns = columns;
}
// null column correction
for (int i = 0; i < _columns.Length; ++i)
{
if (_columns[i] == null)
{
_columns[i] = Designer.Null;
}
}
if (isDistinct)
{
chainKeyword = Text.SelectDistinct;
chainMethod = Text.Method.SelectDistinct;
IsDistinct = isDistinct;
}
else
{
chainKeyword = Text.Select;
chainMethod = Text.Method.Select;
}
Query.AddArguments(_columns);
Build = (buildContext, buildArgs) =>
{
var sql = Text.GenerateSql(200)
.NewLine(Keyword).S()
.Append(BuildTop(buildContext));
TryAddAsteriskColumns();
sql.Append(Column.Concatenate(_columns, buildContext, buildArgs, _variables));
TryThrow(buildContext);
return(sql.ToString());
};
}
internal static string BuildDropNoCheck(string table)
{
return(Text.GenerateSql(100)
.AppendFormat("IF OBJECT_ID(N'tempdb..{0}')", table).S()
.Append(Text.IsNotNull).S()
.Append(Text.DropTempTable).S().Append(table)
.Terminate()
.ToString());
}
internal UpdateChainer(Chainer prev, string alias)
: base(prev)
{
Query.SetUpdate(this);
alias = base.TryGetTableAlias(alias);
var aliasDelimited = Filter.Delimit(alias);
Build = (buildContext, buildArgs) =>
{
var sql = Text.GenerateSql(200);
var select = GetPrev <SelectChainer>();
TakeTop(select);
select.SkipBuild = true;
sql.NewLine(BuildTop(buildContext, buildArgs))
.Append(aliasDelimited).S();
var columnsObject = Prev.GetPrev <ColumnsChainer>();
columnsObject.Build(buildContext, buildArgs);
columnsObject.SkipBuild = true;
var values = ProcessValueArrayInliner(buildContext, buildArgs, select.Columns);
if (values == null)
{
values = select.Columns;
}
var columns = ProcessValueArrayInliner(buildContext, buildArgs, Columns);
if (columns == null)
{
columns = Columns;
}
sql.NewLine(Text.Set).S();
var i = 0;
foreach (var column in columns)
{
if (i > 0)
{
sql.NewLineIndent(Text.Comma).S();
}
sql.Append(aliasDelimited)
.Append(Text.Dot)
.Append(column.Build(buildContext, buildArgs))
.Append(Text._Equal_)
.Append(values[i].Build(buildContext, buildArgs)).S();
++i;
}
OutputChainer.TryAppendOutput(this, sql, buildContext, buildArgs);
return(sql.ToString());
};
}
internal CaseExpressionChainer(Chainer prev)
: base(prev)
{
Build = (buildContext, buildArgs) =>
{
return(Text.GenerateSql(200)
.Append(BuildCase(buildContext, buildArgs)).S()
.Append(Text.End)
.ToString());
};
}
internal static View ConvertScalar <T>(T data, Type clrType)
{
var type = typeof(T);
var sql = Text.GenerateSql(50)
.Append(Text.Select).S();
AppendScalarColumn(sql, data, clrType);
ViewColumnInfo column = new ViewColumnInfo(Text.SingleColumnName, type, clrType);
var view = new View(sql.ToString(), clrType, new ViewColumnInfo[] { column }, 1);
return(view);
}
private static void AppendScalarColumn(StringBuilder sql, object value, Type clrType)
{
AppendColumn(sql,
Text.GenerateSql(100)
.Append(Text.Cast)
.Append(Text.LeftBracket)
.Append(Mapping.BuildUnchecked(value ?? Designer.Null))
.Append(Text._As_)
.Append(Mapping.ClrMapping[clrType].DefaultDataType.Build())
.Append(Text.RightBracket)
.ToString(),
Text.SingleColumnName);
}
internal static View ConvertDataTable(DataTable dataTable)
{
int rowCount = 0;
if (dataTable == null)
{
throw new QueryTalkException("ViewConverter.ToView", QueryTalkExceptionType.ArgumentNull,
"dataTable = null", Text.Method.ToView);
}
if (dataTable.Columns.Count == 0)
{
throw new QueryTalkException("ViewConverter.ToView", QueryTalkExceptionType.InvalidDataTable,
"type = DataTable", Text.Method.ToView);
}
var columns = new List <ViewColumnInfo>();
var rows = dataTable.AsEnumerable();
int numberOfColumns = dataTable.Columns.Count;
var sqlOuter = Text.GenerateSql(500);
var sqlEmpty = Text.GenerateSql(200)
.NewLineIndent(Text.Select).S();
int i = 0;
i = _BuildDataTableOuterSelect(dataTable, columns, sqlOuter, i);
numberOfColumns = i;
if (numberOfColumns == 0)
{
ThrowInvalidDataClassException(typeof(DataTable));
}
var sqlInner = Text.GenerateSql(500);
bool isEmpty = rows.Count() == 0;
bool firstRow = true;
if (!isEmpty)
{
_BuildDataTableValues(ref rowCount, columns, rows, sqlInner, ref firstRow);
}
else
{
_BuildDataTableEmpty(dataTable, sqlEmpty);
sqlInner.Append(sqlEmpty);
}
return(Finalizer(typeof(DataTable), sqlOuter, sqlInner, columns.ToArray(), rowCount, isEmpty));
}
internal CollateChainer(DbColumn column, string collation)
: base(null)
{
CheckNullAndThrow(Arg(() => collation, collation));
Build = (buildContext, buildArgs) =>
{
return(Text.GenerateSql(50)
.Append(column.Build(buildContext, buildArgs)).S()
.Append(Text.Collate).S()
.Append(collation)
.ToString());
};
}
private string DropTempTables()
{
var sql = Text.GenerateSql(200);
Executable.Arguments.ForEach(argument =>
{
if (argument.DT == DT.TempTable)
{
sql.NewLine(DropTempTableChainer.BuildDropNoCheck(argument.ParamName));
}
});
return((Executable.Arguments.Count > 0) ? sql.ToString() : null);
}
private string BuildStoredProc()
{
return(Text.GenerateSql(1000)
.NewLine(Text.Exec).S()
.Append(Text.Reserved.ReturnValueOuterParam).Append(Text._Equal_)
.Append(_body)
.Append(BuildStoredProcArguments())
.NewLine(Text.Set).S()
.Append(Text.Reserved.ReturnValueInnerParam)
.Append(Text._Equal_)
.Append(Text.Reserved.ReturnValueOuterParam)
.TerminateSingle()
.ToString());
}
internal void BuildProc(ParameterArgument[] arguments)
{
Arguments = arguments;
var root = Mapper.GetRoot();
DbMapping.CreateParams(root, this);
string parameters = null;
if (arguments != null && arguments.Length > 0)
{
var i = 0;
parameters = String.Join(",",
root.AllParams
.Select(p =>
{
var s = p.Name;
if (arguments[i] == null)
{
arguments[i] = Designer.Null;
}
if (arguments[i].IsArgumentOutput)
{
s = String.Format("{0} {1}", s, Text.Output);
}
++i;
return(s);
}
));
}
string sql = Text.GenerateSql(100)
.NewLine(Text.Exec).S()
.Append(Text.Reserved.ReturnValueOuterParam).Append(Text._Equal_)
.Append(Map.Name.Sql).S()
.Append(parameters).Terminate()
.NewLine(Text.Set).S() // SET @_ri = @_ro;
.Append(Text.Reserved.ReturnValueInnerParam)
.Append(Text._Equal_)
.Append(Text.Reserved.ReturnValueOuterParam)
.Terminate()
.ToString();
Mapper.SetSql(sql);
}
internal static string GetParamDeclaration(ParameterArgument argument, bool check, bool outer)
{
var dt = argument.DT;
var dbt = argument.DataType;
if (dt.IsNameType())
{
var sql = Text.GenerateSql(500)
.Append(argument.NameTypeSql);
if (!outer && dt == DT.Udtt)
{
sql.S().Append(Text.Readonly);
}
return(sql.ToString());
}
if (dbt != null)
{
if (dbt.Precision != 0)
{
if (check)
{
CriticalCheckArgumentSize(argument);
}
return(String.Format("{0}({1},{2})", Mapping.SqlMapping[dt].Sql, dbt.Precision, dbt.Scale));
}
else if (dbt.Length != 0)
{
if (check)
{
CriticalCheckArgumentSize(argument);
}
return(String.Format("{0}({1})", Mapping.SqlMapping[dt].Sql, dbt.Length));
}
// if precision/size is not declared, infer it from the value
else
{
return(Mapping.SqlMapping[dt].SqlByValue(argument.Value));
}
}
else
{
return(Mapping.SqlMapping[dt].SqlByValue(argument.Value));
}
}
internal OrderedChainer(System.String identifier, SortOrder sortOrder)
: base(null)
{
CheckNull(Arg(() => identifier, identifier));
Build = (buildContext, buildArgs) =>
{
var sql = Text.GenerateSql(100)
.Append(Filter.DelimitMultiPartOrParam(identifier, IdentifierType.ColumnOrParam, out chainException)).S()
.Append(sortOrder.ToUpperCase())
.ToString();
buildContext.TryTakeException(chainException);
return(sql);
};
}
internal DropTempTableChainer(Chainer prev, string table)
: base(prev)
{
CheckNullAndThrow(Arg(() => table, table));
Build = (buildContext, buildArgs) =>
{
var sql = Text.GenerateSql(100)
.NewLine(BuildDrop(table, GetRoot(), out chainException))
.ToString();
TryThrow();
return(sql);
};
}
private Func <BuildContext, BuildArgs, string> BuildViewMethod(View view, bool sign)
{
return((buildContext, buildArgs) =>
{
var sql = Text.GenerateSql(50)
.NewLine(Text.If).S().Append(sign ? null : Text.Not_)
.Append(Text.Exists).S()
.Append(Text.LeftBracket)
.Append(view.Build(buildContext, buildArgs))
.Append(Text.RightBracket)
.NewLine(Text.Begin)
.ToString();
TryThrow(buildContext);
return sql;
});
}
// unpivot
internal PivotChainer(Chainer prev,
NonSelectColumnArgument valueColumn,
NonSelectColumnArgument rotatingColumn,
string[] rotatedColumns)
: base(prev, true)
{
_method = Text.Method.Unpivot;
CheckNullAndThrow(Arg(() => valueColumn, valueColumn));
CheckNullAndThrow(Arg(() => rotatingColumn, rotatingColumn));
CheckNullOrEmptyAndThrow(Argc(() => rotatedColumns, rotatedColumns));
Array.ForEach(rotatedColumns, column => { if (column == null)
{
Throw(QueryTalkExceptionType.ArgumentNull, "rotatedColumn = null");
}
});
Build = (buildContext, buildArgs) =>
{
// build IN values
List <string> columns = new List <string>();
Array.ForEach(rotatedColumns, column =>
{
columns.Add(Filter.Delimit(column));
});
var sql = Text.GenerateSql(300)
.NewLine(Text.Unpivot).S()
.Append(Text.LeftBracket)
.Append(valueColumn.Build(buildContext, buildArgs)).S()
.Append(Text.For).S()
.Append(rotatingColumn.Build(buildContext, buildArgs)).S()
.NewLineIndent(Text.In).S()
.Append(Text.LeftBracket)
.Append(String.Join(Text.Comma, columns))
.Append(Text.RightBracket).Append(Text.RightBracket)
.Append(Text._As_).Append(Filter.Delimit(Alias.Name))
.ToString();
TryThrow(buildContext);
return(sql);
};
}
private string BuildExecProc(BuildContext buildContext, BuildArgs buildArgs)
{
// assign the current node of the build context
buildContext.Current = _innerObject;
if (ReturnVariable != null)
{
return(Text.GenerateSql(1000)
.Append(Executable.Build(buildContext, buildArgs))
.NewLine(Text.Set).S()
.Append(ReturnVariable).Append(Text._Equal_).Append(Text.Reserved.ReturnValueOuterParam)
.Terminate()
.ToString());
}
else
{
return(Executable.Build(buildContext, buildArgs));
}
}
internal Expression BuildRKPredicate(object[] rkValues, int index, bool parameterization = true)
{
StringBuilder builder = null;
int i = 0;
foreach (var column in SortedRKColumns)
{
string expressionStr;
if (rkValues[i] == null)
{
expressionStr = String.Format("([{0}].{1} IS NULL)",
index, column.Name);
}
else
{
if (parameterization)
{
expressionStr = String.Format("([{0}].{1} = @o{2})",
index, column.Name, i + 1);
}
else
{
var value = rkValues[i];
expressionStr = String.Format("([{0}].{1} = {2})",
index, column.Name, Mapping.Build(value, column.DataType));
}
}
if (builder == null)
{
builder = Text.GenerateSql(200);
}
else
{
builder.Append(Text._And_);
}
builder.Append(expressionStr);
++i;
}
return(builder.ToString().E());
}
internal CollateChainer(Chainer prev, string collation)
: base(prev)
{
CheckNullAndThrow(Arg(() => collation, collation));
Build = (buildContext, buildArgs) =>
{
if (prev is IColumnName)
{
_columnName = ((IColumnName)prev).ColumnName;
}
return(Text.GenerateSql(50)
.Append(prev.Build(buildContext, buildArgs)).S()
.Append(Text.Collate).S()
.Append(collation)
.ToString());
};
}
// Wrap SQL code with EXECUTE AS/REVERT wrapper.
internal void Impersonate(string userToImpersonate)
{
if (userToImpersonate == null)
{
return;
}
_userToImpersonate = userToImpersonate;
var sql = Text.GenerateSql(1000)
.Append(Text.ExecuteAsUser).Append(Text._Equal_)
.Append(Filter.DelimitQuoteNonAsterix(_userToImpersonate))
.Terminate()
.NewLine(_sql)
.NewLine(Text.Revert)
.Terminate();
_sql = sql.ToString();
}