public override ISqlExpression GetIdentityExpression(SqlTable table, SqlField identityField, bool forReturning)
{
if (table.SequenceAttributes != null)
return new SqlExpression("GEN_ID(" + table.SequenceAttributes[0].SequenceName + ", 1)", Precedence.Primary);
return base.GetIdentityExpression(table, identityField, forReturning);
}
public override ISqlExpression GetIdentityExpression(SqlTable table)
{
if (!table.SequenceAttributes.IsNullOrEmpty())
return new SqlExpression("GEN_ID(" + table.SequenceAttributes[0].SequenceName + ", 1)", Precedence.Primary);
return base.GetIdentityExpression(table);
}
public virtual void SetTable(SqlTable table, MemberInfo member, IEnumerable<Expression> arguments, IEnumerable<ISqlExpression> sqlArgs)
{
table.SqlTableType = SqlTableType.Function;
table.Name = Name ?? member.Name;
table.PhysicalName = Name ?? member.Name;
table.TableArguments = ConvertArgs(member, sqlArgs.ToArray());
}
public static SqlTable getTableFromRawSql(string rawSql)
{
SqlTable sqlTable = new SqlTable();
using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["BROWSitContext"].ConnectionString))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(rawSql, connection))
{
try
{
adapter.Fill(sqlTable.contents);
}
catch (SqlException e)
{
//sqlTable.errors = e.Errors;
foreach (SqlError se in e.Errors)
{
sqlTable.errorStrings.Add("Message: " + se.Message
+ " | Number: " + se.Number
+ " | Line: " + se.LineNumber
+ " | Source: " + se.Source
+ " | Procedure: " + se.Procedure);
}
}
}
}
return sqlTable;
}
public void ExpressionType_ReturnsJoin()
{
var table = new SqlTable("dbo.Users", "u");
var predicate = SqlExpression.Equal("u.Id", 5);
var expression = new SqlJoin(SqlJoinType.Left, table);
Assert.Equal(SqlExpressionType.Join, expression.ExpressionType);
}
public void Ctor_WithTableName_SetsTableNameProperty()
{
var table = new SqlTable("[dbo].[Users]");
Assert.NotNull(table.TableName);
Assert.Equal("dbo", table.TableName.Segments[0]);
Assert.Equal("Users", table.TableName.Segments[1]);
}
public override void SetTable(MappingSchema mappingSchema, SqlTable table, MemberInfo member, IEnumerable<Expression> arguments, IEnumerable<ISqlExpression> sqlArgs)
{
table.SqlTableType = SqlTableType.Expression;
table.Name = Expression ?? member.Name;
table.TableArguments = ConvertArgs(member, sqlArgs.ToArray());
if (Schema != null) table.Owner = Schema;
if (Database != null) table.Database = Database;
}
public void Test()
{
var schema = new MappingSchema();
schema.SetDataType(typeof (decimal), new SqlDataType(DataType.Decimal, 19, 4));
var table = new SqlTable<Foo>(schema);
Assert.That(table.Fields.Single().Value.Precision, Is.EqualTo(19));
Assert.That(table.Fields.Single().Value.Scale, Is.EqualTo(4));
}
public void ImplicitConversion_WithAlias_ReturnsColumn()
{
var table = new SqlTable("[dbo].[Users]", "u");
var column = table + "Id";
Assert.NotNull(column);
Assert.Equal(2, column.ColumnName.Segments.Length);
Assert.Equal("u", column.ColumnName.Segments.First());
Assert.Equal("Id", column.ColumnName.Segments.Last());
}
public override ISqlExpression GetIdentityExpression(SqlTable table)
{
if (!table.SequenceAttributes.IsNullOrEmpty())
{
var attr = GetSequenceNameAttribute(table, false);
if (attr != null)
return new SqlExpression(attr.SequenceName + ".nextval", Precedence.Primary);
}
return base.GetIdentityExpression(table);
}
public override ISqlExpression GetIdentityExpression(SqlTable table, SqlField identityField, bool forReturning)
{
if (table.SequenceAttributes != null)
{
var attr = GetSequenceNameAttribute(table, false);
if (attr != null)
return new SqlExpression(attr.SequenceName + ".nextval", Precedence.Primary);
}
return base.GetIdentityExpression(table, identityField, forReturning);
}
public override void SetTable(SqlTable table, MemberInfo member, IEnumerable<Expression> expArgs, IEnumerable<ISqlExpression> sqlArgs)
{
var aargs = sqlArgs.ToArray();
var arr = ConvertArgs(member, aargs).ToList();
var method = (MethodInfo)member;
{
var ttype = method.GetGenericArguments()[0];
var tbl = new SqlTable(ttype);
var database = Convert(tbl.Database);
var owner = Convert(tbl.Owner);
var physicalName = Convert(tbl.PhysicalName);
var name = "";
if (database != null)
name = database + "." + (owner == null ? "." : owner + ".");
else if (owner != null)
name = owner + ".";
name += physicalName;
arr.Add(new SqlExpression(name, Precedence.Primary));
}
{
var field = ((ConstantExpression)expArgs.First()).Value;
if (field is string)
{
arr[0] = new SqlExpression(field.ToString(), Precedence.Primary);
}
else if (field is LambdaExpression)
{
var body = ((LambdaExpression)field).Body;
if (body is MemberExpression)
{
var name = ((MemberExpression)body).Member.Name;
if (name.Length > 0 && name[0] != '[')
name = "[" + name + "]";
arr[0] = new SqlExpression(name, Precedence.Primary);
}
}
}
table.SqlTableType = SqlTableType.Expression;
table.Name = "FREETEXTTABLE({6}, {2}, {3}) {1}";
table.TableArguments = arr.ToArray();
}
public void Select_WithGroupByWithRealLifeQuery_ReturnsQuery()
{
SqlTable profiles = new SqlTable("Profile", "p");
var actual = Sql
.Select(profiles + "Age", SqlAggregate.Count(profiles + "Id", "Count"))
.From(profiles)
.GroupBy(profiles + "Age")
.Having(SqlExpression.GreaterThanOrEqual(profiles + "Age", 18))
.ToSql();
const string expected = "SELECT [p].[Age], COUNT([p].[Id]) AS [Count] FROM [Profile] [p] GROUP BY ([p].[Age]) HAVING [p].[Age] >= 18";
Assert.Equal(expected, actual);
}
public void Ctor_WithTableAndPredicate_SetsPropertyValues()
{
var table = new SqlTable("dbo.Users", "u");
var predicate = SqlExpression.Equal("u.Id", 5);
var expression = new SqlJoin(SqlJoinType.Left, table, predicate);
Assert.NotNull(expression.Table);
Assert.Same(table, expression.Table);
Assert.NotNull(expression.On);
Assert.NotNull(expression.On.Predicate);
Assert.Same(predicate, expression.On.Predicate);
Assert.Equal(SqlJoinType.Left, expression.Type);
}
public void Union_WithRealLifeQuery_ReturnsQuery()
{
SqlTable suppliers = new SqlTable("Supplier", "s");
SqlTable customers = new SqlTable("Customer", "c");
var actual = Sql
.Union(
Sql.Select(suppliers + "City").From(suppliers),
Sql.Select(customers + "City").From(customers))
.ToSql();
const string expected = "(SELECT [s].[City] FROM [Supplier] [s]) UNION (SELECT [c].[City] FROM [Customer] [c])";
Assert.Equal(expected, actual);
}
protected override SqlQuery ProcessQuery(SqlQuery sqlQuery)
{
if (sqlQuery.QueryType == QueryType.Insert && sqlQuery.Set.Into.Name == "Parent")
{
var expr =
new QueryVisitor().Find(sqlQuery.Set, e =>
{
if (e.ElementType == QueryElementType.SetExpression)
{
var se = (SqlQuery.SetExpression)e;
return ((SqlField)se.Column).Name == "ParentID";
}
return false;
}) as SqlQuery.SetExpression;
if (expr != null)
{
var value = ConvertTo<int>.From(((IValueContainer)expr.Expression).Value);
if (value == 555)
{
var tableName = "Parent1";
var dic = new Dictionary<IQueryElement,IQueryElement>();
sqlQuery = new QueryVisitor().Convert(sqlQuery, e =>
{
if (e.ElementType == QueryElementType.SqlTable)
{
var oldTable = (SqlTable)e;
if (oldTable.Name == "Parent")
{
var newTable = new SqlTable(oldTable) { Name = tableName, PhysicalName = tableName };
foreach (var field in oldTable.Fields.Values)
dic.Add(field, newTable.Fields[field.Name]);
return newTable;
}
}
IQueryElement ex;
return dic.TryGetValue(e, out ex) ? ex : null;
});
}
}
}
return sqlQuery;
}
public void Select_WithRealLifeQuery_ReturnsQuery()
{
SqlTable users = new SqlTable("User", "u");
SqlTable profiles = new SqlTable("Profile", "p");
var actual = Sql
.Select(users + "Id", users + "Name", users + "Email", profiles + "Avatar", profiles + "Birthday")
.From(users)
.LeftJoin(profiles).On(SqlExpression.Equal(users + "Id", profiles + "UserId"))
.Where(SqlExpression.Equal(profiles + "Age", 18))
.OrderBy(users + "Name")
.ToSql();
const string expected = "SELECT [u].[Id], [u].[Name], [u].[Email], [p].[Avatar], [p].[Birthday] FROM [User] [u] LEFT JOIN [Profile] [p] ON [u].[Id] = [p].[UserId] WHERE [p].[Age] = 18 ORDER BY [u].[Name]";
Assert.Equal(expected, actual);
}
public void ToSql_WithSelectWithInnerJoinWithOrderByClauseWithWhereClause_WritesCorrectSql()
{
var foo = new SqlTable("foo", "f");
var bar = new SqlTable("bar", "b");
var sql = Sql
.Select(foo + "Id", foo + "Name", bar + "Email")
.From(foo)
.InnerJoin(bar, SqlExpression.Equal(foo + "Id", bar + "FooId"))
.Where(SqlExpression.Equal(foo + "Id", 5))
.OrderBy("Id", SqlSortOrder.Descending)
.OrderBy("Name")
.ToSql();
Assert.AreEqual("SELECT [f].[Id], [f].[Name], [b].[Email] FROM [foo] [f] INNER JOIN [bar] [b] ON ([f].[Id] = [b].[FooId]) WHERE ([f].[Id] = 5) ORDER BY [Id] DESC, [Name] ASC", sql);
}
public void CreateTable(SqlTable table)
{
var columns = GetColumns(table);
if (columns.Count > 0)
{
if (!CompareColumns(table, columns))
{
var from = new SqlTable(table.Name, columns);
database.Query(AlterTableQuery(from, table));
}
}
else
{
database.Query(CreateTableQuery(table));
}
}
/// <summary>
/// Alter a table from source to destination
/// </summary>
/// <param name="from">Must have name and column names. Column types are not required</param>
/// <param name="to">Must have column names and column types.</param>
/// <returns></returns>
public string AlterTableQuery(SqlTable from, SqlTable to)
{
var rstr = rand.NextString(20);
var alter = "ALTER TABLE '{0}' RENAME TO '{1}_{0}'".SFormat(from.Name, rstr);
var create = CreateTableQuery(to);
//combine all columns in the 'from' variable excluding ones that aren't in the 'to' variable.
//exclude the ones that aren't in 'to' variable because if the column is deleted, why try to import the data?
var insert = "INSERT INTO '{0}' ({1}) SELECT {1} FROM {2}_{0}".SFormat(from.Name, string.Join(", ", from.Columns.Where(c => to.Columns.Any(c2 => c2.Name == c.Name)).Select(c => c.Name).ToArray()), rstr);
var drop = "DROP TABLE '{0}_{1}'".SFormat(rstr, from.Name);
return "{0}; {1}; {2}; {3};".SFormat(alter, create, insert, drop);
/*
ALTER TABLE "main"."Bans" RENAME TO "oXHFcGcd04oXHFcGcd04_Bans"
CREATE TABLE "main"."Bans" ("IP" TEXT PRIMARY KEY ,"Name" TEXT)
INSERT INTO "main"."Bans" SELECT "IP","Name" FROM "main"."oXHFcGcd04oXHFcGcd04_Bans"
DROP TABLE "main"."oXHFcGcd04oXHFcGcd04_Bans"
*
*/
}
public override void SetTable(MappingSchema mappingSchema, SqlTable table, MemberInfo member, IEnumerable<Expression> expArgs, IEnumerable<ISqlExpression> sqlArgs)
{
var method = member as MethodInfo;
if (method == null)
throw new ArgumentNullException("member");
var paramsList = method.GetParameters().ToList();
var valuesList = expArgs.Cast<ConstantExpression>().ToList();
if (paramsList.Count != valuesList.Count)
throw new TargetParameterCountException("Invalid number of parameters");
var sqlValues = new List<ISqlExpression>();
for(var i = 0; i < paramsList.Count; i++)
{
var val = valuesList[i].Value;
if (val == null)
continue;
var p = paramsList[i];
sqlValues.Add(new SqlValue("$$" + p.Name + "$$"));
sqlValues.Add(new SqlValue(ValueToString(val)));
}
var arg = new ISqlExpression[1];
arg[0] = new SqlExpression(
String.Join(", ",
Enumerable.Range(0, sqlValues.Count)
.Select(x => "{" + x + "}")),
sqlValues.ToArray());
table.SqlTableType = SqlTableType.Expression;
table.Name = "{0}('PLACEHOLDER' = {2}) {1}";
table.TableArguments = arg.ToArray();
}
void restoreBackupTable(BackupTable bkup, DataAccessObject dao, int totalCount, ref int currentCount)
{
SqlTable table = new SqlTable(bkup.TableName);
int progressCount = 0;
foreach (object[] row in bkup.Data) {
currentCount++;
progressCount++;
if (progressCount > 17) {
if (WFContext.BreakExecution) return;
WFContext.Description = "Inserting record " + currentCount + " of " + totalCount + " to \"" + table.TableName + "\"...";
WFContext.SetProgress(currentCount, totalCount);
progressCount = 0;
}
SqlQuery q = new SqlQuery(dao);
q.Insert(table);
int f = -1;
foreach (object value in row) {
switch ((DataValueType)bkup.FieldTypes[++f]) {
case DataValueType.BooleanType: q.Values(new SqlFieldBoolean(bkup.FieldNames[f], table), (bool)value); break;
case DataValueType.DateTimeType: q.Values(new SqlFieldDateTime(bkup.FieldNames[f], table), (DateTime)value); break;
case DataValueType.FloatType: q.Values(new SqlFieldFloat(bkup.FieldNames[f], table), (double)value); break;
case DataValueType.IntegerType: q.Values(new SqlFieldInteger(bkup.FieldNames[f], table), (int)value); break;
case DataValueType.ShortStringType: q.Values(new SqlFieldShortString(bkup.FieldNames[f], table), (string)value); break;
case DataValueType.LongStringType: q.Values(new SqlFieldLongString(bkup.FieldNames[f], table), (string)value); break;
default: break;
}
}
q.ExecuteNonQuery();
}
}
public override ISqlExpression GetIdentityExpression(SqlTable table)
{
if (!table.SequenceAttributes.IsNullOrEmpty())
{
var attr = GetSequenceNameAttribute(table, false);
if (attr != null)
{
var name = Convert(attr.SequenceName, ConvertType.NameToQueryTable).ToString();
var database = GetTableDatabaseName(table);
var owner = GetTableOwnerName (table);
var sb = BuildTableName(new StringBuilder(), database, owner, name);
return new SqlExpression("nextval('" + sb + "')", Precedence.Primary);
}
}
return base.GetIdentityExpression(table);
}
internal override SqlTable VisitTable(SqlTable tab) {
SqlTable nt = new SqlTable(tab.MetaTable, tab.RowType, tab.SqlRowType, tab.SourceExpression);
this.nodeMap[tab] = nt;
foreach (SqlColumn c in tab.Columns) {
nt.Columns.Add((SqlColumn)this.Visit(c));
}
return nt;
}
internal GeneratedTypeDeclaration(CodeTypeDeclaration t, SqlTable table)
: base(t)
{
this.GenericMappings.Add("`1", "<" + table.Name + ">");
}
protected override IBuildContext BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
{
var deleteType = methodCall.Method.Name switch
{
nameof(LinqExtensions.DeleteWithOutput) => DeleteContext.DeleteType.DeleteOutput,
nameof(LinqExtensions.DeleteWithOutputInto) => DeleteContext.DeleteType.DeleteOutputInto,
_ => DeleteContext.DeleteType.Delete,
};
var sequence = builder.BuildSequence(new BuildInfo(buildInfo, methodCall.Arguments[0]));
if (methodCall.Arguments.Count == 2 && deleteType == DeleteContext.DeleteType.Delete)
{
sequence = builder.BuildWhere(buildInfo.Parent, sequence, (LambdaExpression)methodCall.Arguments[1].Unwrap(), false);
}
var deleteStatement = new SqlDeleteStatement(sequence.SelectQuery);
sequence.Statement = deleteStatement;
// Check association.
//
if (sequence is SelectContext ctx && ctx.IsScalar)
{
var res = ctx.IsExpression(null, 0, RequestFor.Association);
if (res.Result)
{
var isTableResult = res.Context !.IsExpression(null, 0, RequestFor.Table);
if (!isTableResult.Result)
{
throw new LinqException("Can not retrieve Table context from association.");
}
var atc = (TableBuilder.TableContext)isTableResult.Context !;
deleteStatement.Table = atc.SqlTable;
}
else
{
res = ctx.IsExpression(null, 0, RequestFor.Table);
if (res.Result && res.Context is TableBuilder.TableContext context)
{
var tc = context;
if (deleteStatement.SelectQuery.From.Tables.Count == 0 || deleteStatement.SelectQuery.From.Tables[0].Source != tc.SelectQuery)
{
deleteStatement.Table = tc.SqlTable;
}
}
}
}
var indexedParameters
= methodCall.Method.GetParameters().Select((p, i) => Tuple.Create(p, i)).ToDictionary(t => t.Item1.Name, t => t.Item2);
Expression GetArgumentByName(string name)
{
return(methodCall.Arguments[indexedParameters[name]]);
}
LambdaExpression GetOutputExpression(Type outputType)
{
if (!indexedParameters.TryGetValue("outputExpression", out var index))
{
var param = Expression.Parameter(outputType);
return(Expression.Lambda(param, param));
}
return((LambdaExpression)methodCall.Arguments[index].Unwrap());
}
IBuildContext? outputContext = null;
LambdaExpression?outputExpression = null;
if (deleteType != DeleteContext.DeleteType.Delete)
{
outputExpression = GetOutputExpression(methodCall.Method.GetGenericArguments().Last());
deleteStatement.Output = new SqlOutputClause();
var deletedTable = SqlTable.Deleted(methodCall.Method.GetGenericArguments()[0]);
outputContext = new TableBuilder.TableContext(builder, new SelectQuery(), deletedTable);
deleteStatement.Output.DeletedTable = deletedTable;
if (deleteType == DeleteContext.DeleteType.DeleteOutputInto)
{
var outputTable = GetArgumentByName("outputTable");
var destination = builder.BuildSequence(new BuildInfo(buildInfo, outputTable, new SelectQuery()));
UpdateBuilder.BuildSetter(
builder,
buildInfo,
outputExpression,
destination,
deleteStatement.Output.OutputItems,
outputContext);
deleteStatement.Output.OutputTable = ((TableBuilder.TableContext)destination).SqlTable;
}
}
if (deleteType == DeleteContext.DeleteType.DeleteOutput)
{
return(new DeleteWithOutputContext(buildInfo.Parent, sequence, outputContext !, outputExpression !));
}
return(new DeleteContext(buildInfo.Parent, sequence));
}
public static bool CreateTable(SqlTable table)
{
return(sqlcreator.EnsureTableStructure(table));
}
static Query <int> CreateQuery(IDataContext dataContext, string tableName, string databaseName, string schemaName, Type type)
{
var sqlTable = new SqlTable <T>(dataContext.MappingSchema);
if (tableName != null)
{
sqlTable.PhysicalName = tableName;
}
if (databaseName != null)
{
sqlTable.Database = databaseName;
}
if (schemaName != null)
{
sqlTable.Schema = schemaName;
}
var sqlQuery = new SelectQuery();
var updateStatement = new SqlUpdateStatement(sqlQuery);
sqlQuery.From.Table(sqlTable);
var ei = new Query <int>(dataContext, null)
{
Queries = { new QueryInfo {
Statement = updateStatement,
} }
};
var keys = sqlTable.GetKeys(true).Cast <SqlField>().ToList();
var fields = sqlTable.Fields.Values.Where(f => f.IsUpdatable).Except(keys).ToList();
if (fields.Count == 0)
{
if (Configuration.Linq.IgnoreEmptyUpdate)
{
return(null);
}
throw new LinqException(
keys.Count == sqlTable.Fields.Count ?
$"There are no fields to update in the type '{sqlTable.Name}'. No PK is defined or all fields are keys." :
$"There are no fields to update in the type '{sqlTable.Name}'.");
}
foreach (var field in fields)
{
var param = GetParameter(type, dataContext, field);
ei.Queries[0].Parameters.Add(param);
updateStatement.Update.Items.Add(new SqlSetExpression(field, param.SqlParameter));
}
foreach (var field in keys)
{
var param = GetParameter(type, dataContext, field);
ei.Queries[0].Parameters.Add(param);
sqlQuery.Where.Field(field).Equal.Expr(param.SqlParameter);
if (field.CanBeNull)
{
sqlQuery.IsParameterDependent = true;
}
}
SetNonQueryQuery(ei);
return(ei);
}
public DeleteFromBlock(SqlTable sqlTable) : this(new TableEntity(sqlTable ?? throw new ArgumentException(nameof(sqlTable))))
public override void SetTable(ISqlBuilder sqlBuilder, MappingSchema mappingSchema, SqlTable table, MemberInfo member, IEnumerable <Expression> expArgs, IEnumerable <ISqlExpression> sqlArgs)
{
var arg = sqlArgs.ElementAt(1);
var ed = mappingSchema.GetEntityDescriptor(table.ObjectType !);
if (arg is SqlParameter p)
{
var exp = expArgs.ElementAt(1).Unwrap();
// TODO: ValueConverter contract nullability violations
if (exp is ConstantExpression constExpr)
{
if (constExpr.Value is Func <string> )
{
p.ValueConverter = l => ((Func <string>)l !)();
}
else
{
p.ValueConverter = GetXmlConverter(mappingSchema, table) !;
}
}
else if (exp is LambdaExpression)
{
p.ValueConverter = l => ((Func <string>)l !)();
}
}
var columns = ed.Columns
.Select((c, i) => string.Format("{0} {1} path 'c{2}'",
sqlBuilder.ConvertInline(c.ColumnName, ConvertType.NameToQueryField),
string.IsNullOrEmpty(c.DbType) ?
GetDataTypeText(
new SqlDataType(
c.DataType == DataType.Undefined ? SqlDataType.GetDataType(c.MemberType).Type.DataType : c.DataType,
c.MemberType,
c.Length,
c.Precision,
c.Scale,
c.DbType)) :
c.DbType,
i))
.Aggregate((s1, s2) => s1 + ", " + s2);
table.SqlTableType = SqlTableType.Expression;
table.Name = $"XmlTable(\'/t/r\' PASSING XmlType({{2}}) COLUMNS {columns}) {{1}}";
table.TableArguments = new[] { arg };
}
public override string?GetTableSchemaName(SqlTable table)
{
return(table.Schema == null && table.TableOptions.IsTemporaryOptionSet() ? "SESSION" : base.GetTableSchemaName(table));
}
protected SqlSelectBuilderBase RightJoin(SqlTable sqlTable, Operator on = null)
{
return(Join(JoinType.Right, sqlTable, on));
}
protected SqlSelectBuilderBase FullOuterJoin(SqlTable sqlTable, Operator on = null)
{
return(Join(JoinType.FullOuter, sqlTable, on));
}
public ForeignKeyRegistrationToTableWithUniqueKeySingleColumn(SqlTable table, SchemaAndTableName referredTableName, string singleFkColumnName, bool isNullable, string fkName)
: base(table, referredTableName, isNullable, fkName)
{
SingleFkColumnName = singleFkColumnName;
}
internal static void ParseSet(
ExpressionBuilder builder,
BuildInfo buildInfo,
LambdaExpression extract,
Expression update,
IBuildContext select,
SqlTable table,
List <SqlQuery.SetExpression> items)
{
var ext = extract.Body;
if (!ExpressionHelper.IsConstant(update.Type) && !builder.AsParameters.Contains(update))
{
builder.AsParameters.Add(update);
}
while (ext.NodeType == ExpressionType.Convert || ext.NodeType == ExpressionType.ConvertChecked)
{
ext = ((UnaryExpression)ext).Operand;
}
if (ext.NodeType != ExpressionType.MemberAccess || ext.GetRootObject() != extract.Parameters[0])
{
throw new LinqException("Member expression expected for the 'Set' statement.");
}
var body = (MemberExpression)ext;
var member = body.Member;
if (member is MethodInfo)
{
member = TypeHelper.GetPropertyByMethod((MethodInfo)member);
}
var members = body.GetMembers();
var name = members
.Skip(1)
.Select(ex =>
{
var me = ex as MemberExpression;
if (me == null)
{
return(null);
}
var m = me.Member;
if (m is MethodInfo)
{
m = TypeHelper.GetPropertyByMethod((MethodInfo)m);
}
return(m);
})
.Where(m => m != null && !TypeHelper.IsNullableValueMember(m))
.Select(m => m.Name)
.Aggregate((s1, s2) => s1 + "." + s2);
if (table != null && !table.Fields.ContainsKey(name))
{
throw new LinqException("Member '{0}.{1}' is not a table column.", member.DeclaringType.Name, name);
}
ISqlExpression column;
if (table != null)
{
column = table.Fields[name];
}
else
{
var sql = select.ConvertToSql(
body, 1, ConvertFlags.Field);
//Expression.MakeMemberAccess(Expression.Parameter(member.DeclaringType, "p"), member), 1, ConvertFlags.Field)[0].Sql;
if (sql.Length == 0)
{
throw new LinqException("Member '{0}.{1}' is not a table column.", member.DeclaringType.Name, member.Name);
}
column = sql[0].Sql;
}
var expr = builder.ConvertToSql(select, update, false, false);
SetConverter(builder, member, expr);
items.Add(new SqlQuery.SetExpression(column, expr));
}
internal override SqlTable VisitTable(SqlTable tab) {
foreach(SqlColumn col in tab.Columns) {
this.VisitColumn(col);
}
return tab;
}
public string CreateTableQuery(SqlTable table)
{
var columns = table.Columns.Select(c => "'{0}' {1} {2} {3} {4}".SFormat(c.Name, DbTypeToString(c.Type, c.Length), c.Primary ? "PRIMARY KEY" : "", c.AutoIncrement ? "AUTOINCREMENT" : "", c.NotNull ? "NOT NULL" : "", c.Unique ? "UNIQUE" : ""));
return "CREATE TABLE '{0}' ({1})".SFormat(table.Name, string.Join(", ", columns.ToArray()));
}
public static bool SchemaAndTableNameEquals(Row row, SqlTable table, string schemaNameColumn = "schema_name", string tableNameColumn = "table_name")
{
return(row.GetAs <string>(tableNameColumn) == table.SchemaAndTableName.TableName &&
(string.IsNullOrEmpty(table.SchemaAndTableName.Schema) || row.GetAs <string>(schemaNameColumn) == table.SchemaAndTableName.Schema));
}
public static async Task <bool> CreateTableAsync(SqlTable table)
{
return(await Task.Run(() => sqlcreator.EnsureTableStructure(table)));
}
private DbmlContent GetContent(string dbmlPath)
{
var xml = new XmlDocument();
xml.Load(dbmlPath);
List <SqlTable> tables = new List <SqlTable>();
List <SqlFunction> functions = new List <SqlFunction>();
XmlNode database = xml.GetElementsByTagName(DbmlXmlTags.Database).Item(0);
foreach (XmlNode node in database.ChildNodes)
{
if (node.Name == DbmlXmlTags.Table)
{
var name = node.Attributes[DbmlXmlAttributes.Name].Value;
var fullName = name.Split('.');
var table = new SqlTable
{
TableName = fullName[1],
SchemaName = fullName[0],
Columns = new List <SqlColumn>()
};
var type = node.FirstChild.Attributes[DbmlXmlAttributes.Name].Value;
table.ClassName = type;
foreach (XmlNode tableNode in node.FirstChild.ChildNodes)
{
if (tableNode.Name == DbmlXmlTags.Column)
{
SqlColumn column = GetColumnFromNode(tableNode);
column.Table = table;
table.Columns.Add(column);
}
}
tables.Add(table);
}
else if (node.Name == DbmlXmlTags.Function)
{
var name = node.Attributes[DbmlXmlAttributes.Name].Value;
var fullName = name.Split('.');
var function = new SqlFunction
{
TableName = fullName[1],
SchemaName = fullName[0],
Parameters = new List <SqlParameter>()
};
foreach (XmlNode functionNode in node.ChildNodes)
{
if (functionNode.Name == DbmlXmlTags.Parameter)
{
var column = new SqlParameter
{
Name = functionNode.Attributes[DbmlXmlAttributes.Name].Value,
DbType = FixColumnType(functionNode.Attributes[DbmlXmlAttributes.DbType].Value),
};
function.Parameters.Add(column);
}
if (functionNode.Name == DbmlXmlTags.ElementType)
{
var attr = functionNode.Attributes[DbmlXmlAttributes.Name];
if (attr != null)
{
var result = new SqlResultType
{
Name = attr.Value,
Columns = new List <SqlColumn>()
};
foreach (XmlNode columnNode in functionNode.ChildNodes)
{
var column = GetColumnFromNode(columnNode);
result.Columns.Add(column);
}
function.ResultType = result;
}
}
}
functions.Add(function);
}
}
return(new DbmlContent
{
Tables = tables,
Functions = functions
});
}
static Query <int> CreateQuery(IDataContext dataContext, string tableName, string databaseName, string schemaName, Type type)
{
var fieldDic = new Dictionary <SqlField, ParameterAccessor>();
var sqlTable = new SqlTable(dataContext.MappingSchema, type);
if (tableName != null)
{
sqlTable.PhysicalName = tableName;
}
if (databaseName != null)
{
sqlTable.Database = databaseName;
}
if (schemaName != null)
{
sqlTable.Schema = schemaName;
}
var sqlQuery = new SelectQuery();
ParameterAccessor param;
var insertOrUpdateStatement = new SqlInsertOrUpdateStatement(sqlQuery);
insertOrUpdateStatement.Insert.Into = sqlTable;
insertOrUpdateStatement.Update.Table = sqlTable;
sqlQuery.From.Table(sqlTable);
var ei = new Query <int>(dataContext, null)
{
Queries = { new QueryInfo {
Statement = insertOrUpdateStatement,
} }
};
var supported = ei.SqlProviderFlags.IsInsertOrUpdateSupported && ei.SqlProviderFlags.CanCombineParameters;
// Insert.
//
foreach (var field in sqlTable.Fields.Select(f => f.Value))
{
if (field.IsInsertable)
{
if (!supported || !fieldDic.TryGetValue(field, out param))
{
param = GetParameter(type, dataContext, field);
ei.Queries[0].Parameters.Add(param);
if (supported)
{
fieldDic.Add(field, param);
}
}
insertOrUpdateStatement.Insert.Items.Add(new SqlSetExpression(field, param.SqlParameter));
}
else if (field.IsIdentity)
{
throw new LinqException("InsertOrReplace method does not support identity field '{0}.{1}'.", sqlTable.Name, field.Name);
}
}
// Update.
//
var keys = sqlTable.GetKeys(true).Cast <SqlField>().ToList();
var fields = sqlTable.Fields.Values.Where(f => f.IsUpdatable).Except(keys).ToList();
if (keys.Count == 0)
{
throw new LinqException("InsertOrReplace method requires the '{0}' table to have a primary key.", sqlTable.Name);
}
var q =
(
from k in keys
join i in insertOrUpdateStatement.Insert.Items on k equals i.Column
select new { k, i }
).ToList();
var missedKey = keys.Except(q.Select(i => i.k)).FirstOrDefault();
if (missedKey != null)
{
throw new LinqException("InsertOrReplace method requires the '{0}.{1}' field to be included in the insert setter.",
sqlTable.Name,
missedKey.Name);
}
if (fields.Count == 0)
{
throw new LinqException("There are no fields to update in the type '{0}'.", sqlTable.Name);
}
foreach (var field in fields)
{
if (!supported || !fieldDic.TryGetValue(field, out param))
{
param = GetParameter(type, dataContext, field);
ei.Queries[0].Parameters.Add(param);
if (supported)
{
fieldDic.Add(field, param = GetParameter(typeof(T), dataContext, field));
}
}
insertOrUpdateStatement.Update.Items.Add(new SqlSetExpression(field, param.SqlParameter));
}
insertOrUpdateStatement.Update.Keys.AddRange(q.Select(i => i.i));
// Set the query.
//
if (ei.SqlProviderFlags.IsInsertOrUpdateSupported)
{
SetNonQueryQuery(ei);
}
else
{
MakeAlternativeInsertOrUpdate(ei);
}
return(ei);
}
static bool IsTemporary(SqlTable table)
{
return(table.TableOptions.IsTemporaryOptionSet() || table.PhysicalName !.StartsWith("#"));
}
internal static void ParseSet(
ExpressionBuilder builder,
BuildInfo buildInfo,
LambdaExpression extract,
LambdaExpression update,
IBuildContext select,
SqlTable table,
List <SelectQuery.SetExpression> items)
{
var ext = extract.Body;
while (ext.NodeType == ExpressionType.Convert || ext.NodeType == ExpressionType.ConvertChecked)
{
ext = ((UnaryExpression)ext).Operand;
}
if (ext.NodeType != ExpressionType.MemberAccess || ext.GetRootObject() != extract.Parameters[0])
{
throw new LinqException("Member expression expected for the 'Set' statement.");
}
var body = (MemberExpression)ext;
var member = body.Member;
if (member is MethodInfo)
{
member = ((MethodInfo)member).GetPropertyInfo();
}
var members = body.GetMembers();
var name = members
.Skip(1)
.Select(ex =>
{
var me = ex as MemberExpression;
if (me == null)
{
return(null);
}
var m = me.Member;
if (m is MethodInfo)
{
m = ((MethodInfo)m).GetPropertyInfo();
}
return(m);
})
.Where(m => m != null && !m.IsNullableValueMember())
.Select(m => m.Name)
.Aggregate((s1, s2) => s1 + "." + s2);
if (table != null && !table.Fields.ContainsKey(name))
{
throw new LinqException("Member '{0}.{1}' is not a table column.", member.DeclaringType.Name, name);
}
var column = table != null ?
table.Fields[name] :
select.ConvertToSql(
body, 1, ConvertFlags.Field)[0].Sql;
//Expression.MakeMemberAccess(Expression.Parameter(member.DeclaringType, "p"), member), 1, ConvertFlags.Field)[0].Sql;
var sp = select.Parent;
var ctx = new ExpressionContext(buildInfo.Parent, select, update);
var expr = builder.ConvertToSqlExpression(ctx, update.Body);
builder.ReplaceParent(ctx, sp);
items.Add(new SelectQuery.SetExpression(column, expr));
}
public OutputObject GenerateWebServiceControllerClass(SqlTable sqlTable, List <string> namespaceIncludes, IDictionary <string, bool> options)
{
if (sqlTable == null)
{
throw new ArgumentException("Sql table cannot be null");
}
string controllerName = NameFormatter.ToCSharpClassName(sqlTable.Name);
var output = new OutputObject
{
Name = $"{controllerName}Controller.cs",
Type = OutputObject.eObjectType.CSharp
};
var sb = new StringBuilder();
sb.AppendLine();
sb.AppendLine("using Microsoft.AspNetCore.Mvc;");
sb.AppendLine("using System;");
sb.AppendLine("using TelemetryManager;");
sb.AppendLine();
if (namespaceIncludes != null && namespaceIncludes.Count > 1)
{
sb.AppendLine(GenerateNamespaceIncludes(namespaceIncludes));
}
sb.AppendLine($"namespace {CONTROLLER_NAMESPACE}");
sb.AppendLine("{");
sb.AppendLine(AddTabs(1) + "[ApiController]");
if (options[GENERATE_BASE_CONTROLLER])
{
sb.AppendLine(AddTabs(1) + $"public class {controllerName}Controller : ApiControllerBase");
}
else
{
sb.AppendLine(AddTabs(1) + $"public class {controllerName}Controller : ControllerBase");
}
sb.AppendLine(AddTabs(1) + "{");
sb.AppendLine(AddTabs(2) + $"public {controllerName}Controller(IConfigProvider config, ILogger log, IMetrics metrics, IDataProvider datasource, ISecurityProvider security)");
sb.AppendLine(AddTabs(3) + $": base(config, log, metrics, datasource, security) {{ }}");
sb.AppendLine();
// controllers
sb.AppendLine(GenerateGetById(controllerName, options));
sb.AppendLine();
sb.AppendLine(GenerateGetAll(controllerName, options));
sb.AppendLine();
//sb.AppendLine(GenerateGetAllPaged(sqlTable.Name));
//sb.AppendLine();
//sb.AppendLine(GenerateInsert(sqlTable.Name));
//sb.AppendLine();
//sb.AppendLine(GenerateUpdate(sqlTable.Name));
//sb.AppendLine();
//sb.AppendLine(GenerateDelete(sqlTable.Name));
//sb.AppendLine();
sb.AppendLine("}");
output.Body = sb.ToString();
return(output);
}
public SqlInsertBuilderHelper(SqlTable sqlTable) : base(sqlTable)
{
}
public PrimaryKey(SqlTable sqlTable, string name)
: base(sqlTable, name)
{
}
StringBuilder AppendOutputTableVariable(SqlTable table)
{
StringBuilder.Append('@').Append(table.PhysicalName).Append("Output");
return(StringBuilder);
}
public SqlDeleteBuilderHelper(SqlTable sqlTable) : base(sqlTable)
{
}
public override string?GetTableSchemaName(SqlTable table)
{
return(table.Schema == null || table.TableOptions.HasIsTemporary() ? null : ConvertInline(table.Schema, ConvertType.NameToSchema));
}
public ComputedExpressionSqlVisitor(SqlTable from, SqlTable to)
{
this.from = from;
this.to = to;
}
/// <summary>
/// Visits the specified <see cref="SqlTable"/>.
/// </summary>
/// <param name="expression">
/// The expression to visit.
/// </param>
public virtual void Visit(SqlTable expression)
{
}
protected virtual SqlExpression VisitTable(SqlTable expr)
{
return(expr);
}
public List<SqlColumn> GetColumns(SqlTable table)
{
var ret = new List<SqlColumn>();
using (var reader = database.QueryReader("PRAGMA table_info({0})".SFormat(table.Name)))
{
while (reader.Read())
ret.Add(new SqlColumn(reader.Get<string>("name"), StringToDbType(reader.Get<string>("type"))) { NotNull = (reader.Get<int>("notnull") != 0), Primary = (reader.Get<int>("pk") != 0) });
}
return ret;
}
static Query <int> CreateQuery(
IDataContext dataContext,
EntityDescriptor descriptor,
T obj,
InsertOrUpdateColumnFilter <T>?columnFilter,
string?tableName,
string?serverName,
string?databaseName,
string?schemaName,
TableOptions tableOptions,
Type type)
{
var fieldDic = new Dictionary <SqlField, ParameterAccessor>();
var sqlTable = new SqlTable(dataContext.MappingSchema, type);
if (tableName != null)
{
sqlTable.PhysicalName = tableName;
}
if (serverName != null)
{
sqlTable.Server = serverName;
}
if (databaseName != null)
{
sqlTable.Database = databaseName;
}
if (schemaName != null)
{
sqlTable.Schema = schemaName;
}
if (tableOptions.IsSet())
{
sqlTable.TableOptions = tableOptions;
}
var sqlQuery = new SelectQuery();
ParameterAccessor?param = null;
var insertOrUpdateStatement = new SqlInsertOrUpdateStatement(sqlQuery);
insertOrUpdateStatement.Insert.Into = sqlTable;
insertOrUpdateStatement.Update.Table = sqlTable;
sqlQuery.From.Table(sqlTable);
var ei = new Query <int>(dataContext, null)
{
Queries = { new QueryInfo {
Statement = insertOrUpdateStatement,
} }
};
var supported = ei.SqlProviderFlags.IsInsertOrUpdateSupported && ei.SqlProviderFlags.CanCombineParameters;
// Insert.
//
foreach (var field in sqlTable.Fields)
{
if (field.IsInsertable && !field.ColumnDescriptor.ShouldSkip(obj !, descriptor, SkipModification.Insert))
{
if (columnFilter == null || columnFilter(obj, field.ColumnDescriptor, true))
{
if (!supported || !fieldDic.TryGetValue(field, out param))
{
param = GetParameter(type, dataContext, field);
ei.Queries[0].AddParameterAccessor(param);
if (supported)
{
fieldDic.Add(field, param);
}
}
insertOrUpdateStatement.Insert.Items.Add(new SqlSetExpression(field, param.SqlParameter));
}
}
public static string GetXmlData <T>(MappingSchema mappingSchema, IEnumerable <T> data)
{
var sqlTable = new SqlTable(mappingSchema, typeof(T));
return(GetXmlData(mappingSchema, sqlTable, data));
}
public override void SetTable(SqlTable table, MemberInfo member, IEnumerable <Expression> arguments, IEnumerable <ISqlExpression> sqlArgs)
{
table.SqlTableType = SqlTableType.Expression;
table.Name = Expression ?? member.Name;
table.TableArguments = ConvertArgs(member, sqlArgs.ToArray());
}
/// <summary>
///
/// </summary>
/// <param name="table"></param>
/// <param name="columns"></param>
/// <returns>True if the columns match, otherwise false</returns>
bool CompareColumns(SqlTable table, List<SqlColumn> columns)
{
if (table.Columns.Count != columns.Count)
return false;
foreach (var col in table.Columns)
{
var tcol = columns.FirstOrDefault(s => s.Name == col.Name);
if (tcol == null)
return false;
if (tcol.Type != col.Type ||
tcol.Primary != col.Primary ||
tcol.NotNull != col.NotNull)
return false;
}
return true;
}
static string GetXmlData <T>(MappingSchema mappingSchema, SqlTable sqlTable, IEnumerable <T> data)
{
var converter = OracleXmlTableAttribute.GetXmlConverter(mappingSchema, sqlTable);
return(converter(data));
}
internal override SqlTable VisitTable(SqlTable tab) {
string name = tab.Name;
this.WriteName(name);
return tab;
}
internal static Func <object, string> GetXmlConverter(MappingSchema mappingSchema, SqlTable sqlTable)
{
var ed = mappingSchema.GetEntityDescriptor(sqlTable.ObjectType !);
return(o => ValueConverter(
ed.Columns.Select <ColumnDescriptor, Action <StringBuilder, object> >(c =>
{
var conv = mappingSchema.ValueToSqlConverter;
return (sb, obj) =>
{
var value = c.GetValue(obj);
if (value is string && c.MemberType == typeof(string))
{
var str = conv.Convert(new StringBuilder(), value).ToString();
if (str.Length > 2)
{
str = str.Substring(1);
str = str.Substring(0, str.Length - 1);
sb.Append(str);
}
}
else
{
conv.Convert(sb, value);
}
};
}).ToList(),
o));
}
