public void BasicTest_ORDERBY() { SelectStmtInfo stmtInfo = new SelectStmtInfo(); stmtInfo.Columns.Add(new Column() { Expression = new ColumnExpression(ExpressionType.ColumnName) { ColumnName = "userId" } }); stmtInfo.Columns.Add(new Column() { Expression = new ColumnExpression(ExpressionType.ColumnName) { ColumnName = "userName" } }); stmtInfo.TableDescriptor = new TableDescriptor() { TableName = "me", TableReadType = TableReadType.NONE }; stmtInfo.OrderBy.Add(new OrderByCondition() { Expression = "userId", Direction = OrderByDirection.ASC }); IGenerator g = new DefaultSqlGenerator(); var sql = g.Generate(stmtInfo); Assert.AreEqual("SELECT userId, userName FROM me ORDER BY userId ASC", sql); }
public string Generate(SelectStmtInfo stmtInfo) { string predictWord = ""; string columnSql = ""; string tableName = ""; string orderBySql = ""; string whereSql = ""; string finalSql = ""; predictWord = Generate_PredictWord(stmtInfo); tableName = Generate_TableName(stmtInfo); columnSql = Generate_ColumnSql(stmtInfo); orderBySql = Generate_OrderBySql(stmtInfo); whereSql = Generate_WhereSql(stmtInfo); if (string.IsNullOrEmpty(predictWord)) { finalSql = string.Format("SELECT {1} FROM {2}{3}{4}", predictWord, columnSql, tableName, whereSql, orderBySql); } else { finalSql = string.Format("SELECT {0} {1} FROM {2}{3}{4}", predictWord, columnSql, tableName, whereSql, orderBySql); } return(finalSql); }
private static string Generate_TableName(SelectStmtInfo stmtInfo) { string sql = stmtInfo.TableDescriptor.TableName; switch (stmtInfo.TableDescriptor.TableReadType) { case TableReadType.NONE: //Nothing to do break; case TableReadType.NOLOCK: sql += "(NOLOCK)"; break; case TableReadType.READPAST: sql += "(READPAST)"; break; default: throw new NotSupportedException(); break; } return(sql); }
public void BasicTest_Alias() { SelectStmtInfo stmtInfo = new SelectStmtInfo(); stmtInfo.Columns.Add(new Column() { Expression = new ColumnExpression(ExpressionType.ColumnName) { ColumnName = "userId" }, HasAlias = true, Alias = "UID" }); stmtInfo.Columns.Add(new Column() { Expression = new ColumnExpression(ExpressionType.ColumnName) { ColumnName = "userName" } }); stmtInfo.TableDescriptor = new TableDescriptor() { TableName = "me", TableReadType = TableReadType.NONE }; IGenerator g = new DefaultSqlGenerator(); var sql = g.Generate(stmtInfo); Assert.AreEqual("SELECT userId AS UID, userName FROM me", sql); }
public void BasicTest_PREDICT_NO_WHERE_NO_ORDERBY() { SelectStmtInfo stmtInfo = new SelectStmtInfo(); stmtInfo.Column_PredictExists = true; stmtInfo.Column_PredictWord = "DISTINCT"; stmtInfo.Columns.Add(new Column() { Expression = new ColumnExpression(ExpressionType.ColumnName) { ColumnName = "userId" } }); stmtInfo.Columns.Add(new Column() { Expression = new ColumnExpression(ExpressionType.ColumnName) { ColumnName = "userName" } }); stmtInfo.TableDescriptor = new TableDescriptor() { TableName = "me", TableReadType = TableReadType.NONE }; IGenerator g = new DefaultSqlGenerator(); var sql = g.Generate(stmtInfo); Assert.AreEqual("SELECT DISTINCT userId, userName FROM me", sql); }
private static string Generate_PredictWord(SelectStmtInfo stmtInfo) { string predictWord = ""; if (stmtInfo.Column_PredictExists) predictWord = stmtInfo.Column_PredictWord; return predictWord; }
private static string Generate_PredictWord(SelectStmtInfo stmtInfo) { string predictWord = ""; if (stmtInfo.Column_PredictExists) { predictWord = stmtInfo.Column_PredictWord; } return(predictWord); }
private static string Generate_OrderBySql(SelectStmtInfo stmtInfo) { StringBuilder sb = new StringBuilder(); if (stmtInfo.OrderBy != null && stmtInfo.OrderBy.Count > 0) { sb.Append(" ORDER BY "); foreach(var orderByTerm in stmtInfo.OrderBy) sb.AppendFormat("{0} {1}, ", orderByTerm.Expression, orderByTerm.Direction.ToString()); } return sb.ToString().TrimEnd(", ".ToCharArray()); }
public void BasicTest_2_Condition() { SelectStmtInfo stmtInfo = new SelectStmtInfo(); stmtInfo.Columns.Add(new Column() { Expression = new ColumnExpression(ExpressionType.ColumnName) { ColumnName = "userId" } }); stmtInfo.Columns.Add(new Column() { Expression = new ColumnExpression(ExpressionType.ColumnName) { ColumnName = "userName" } }); stmtInfo.TableDescriptor = new TableDescriptor() { TableName = "me", TableReadType = TableReadType.NONE }; stmtInfo.WhereCondition = new WhereCondition(WhereConditionNodeType.Statement); stmtInfo.WhereCondition.Statement_Operator = "AND"; stmtInfo.WhereCondition.Statement_LeftNode = new WhereCondition(WhereConditionNodeType.Condition); stmtInfo.WhereCondition.Statement_LeftNode.Condition_LeftExpression = "userId"; stmtInfo.WhereCondition.Statement_LeftNode.Condition_Operator = ">"; stmtInfo.WhereCondition.Statement_LeftNode.Condition_RightExpression = "5"; stmtInfo.WhereCondition.Statement_RightNode = new WhereCondition(WhereConditionNodeType.Condition); stmtInfo.WhereCondition.Statement_RightNode.Condition_LeftExpression = "sex"; stmtInfo.WhereCondition.Statement_RightNode.Condition_Operator = "="; stmtInfo.WhereCondition.Statement_RightNode.Condition_RightExpression = "1"; stmtInfo.OrderBy.Add(new OrderByCondition() { Expression = "userId", Direction = OrderByDirection.ASC }); IGenerator g = new DefaultSqlGenerator(); var sql = g.Generate(stmtInfo); Assert.AreEqual("SELECT userId, userName FROM me WHERE (userId>5) AND (sex=1) ORDER BY userId ASC", sql); }
private static string Generate_OrderBySql(SelectStmtInfo stmtInfo) { StringBuilder sb = new StringBuilder(); if (stmtInfo.OrderBy != null && stmtInfo.OrderBy.Count > 0) { sb.Append(" ORDER BY "); foreach (var orderByTerm in stmtInfo.OrderBy) { sb.AppendFormat("{0} {1}, ", orderByTerm.Expression, orderByTerm.Direction.ToString()); } } return(sb.ToString().TrimEnd(", ".ToCharArray())); }
private static string Generate_WhereSql(SelectStmtInfo stmtInfo) { if (stmtInfo.WhereCondition == null) { return(string.Empty); } var conditions = _Generate_WhereSql(stmtInfo.WhereCondition); if (string.IsNullOrEmpty(conditions)) { return(string.Empty); } return(string.Format(" WHERE {0}", conditions)); }
private static string Generate_ColumnSql(SelectStmtInfo stmtInfo) { StringBuilder sb = new StringBuilder(); foreach(var column in stmtInfo.Columns) { string cur_sql = string.Empty; cur_sql = column.Expression.ColumnName; if(column.HasAlias) cur_sql+=string.Format(" AS {0}", column.Alias); sb.AppendFormat("{0}, ", cur_sql); } return sb.ToString().TrimEnd(", ".ToCharArray()); }
private static string Generate_ColumnSql(SelectStmtInfo stmtInfo) { StringBuilder sb = new StringBuilder(); foreach (var column in stmtInfo.Columns) { string cur_sql = string.Empty; cur_sql = column.Expression.ColumnName; if (column.HasAlias) { cur_sql += string.Format(" AS {0}", column.Alias); } sb.AppendFormat("{0}, ", cur_sql); } return(sb.ToString().TrimEnd(", ".ToCharArray())); }
private static string Generate_TableName(SelectStmtInfo stmtInfo) { string sql=stmtInfo.TableDescriptor.TableName; switch (stmtInfo.TableDescriptor.TableReadType) { case TableReadType.NONE: //Nothing to do break; case TableReadType.NOLOCK: sql += "(NOLOCK)"; break; case TableReadType.READPAST: sql += "(READPAST)"; break; default: throw new NotSupportedException(); break; } return sql; }
public string Generate(SelectStmtInfo stmtInfo) { string predictWord = ""; string columnSql = ""; string tableName = ""; string orderBySql = ""; string whereSql = ""; string finalSql = ""; predictWord = Generate_PredictWord(stmtInfo); tableName = Generate_TableName(stmtInfo); columnSql = Generate_ColumnSql(stmtInfo); orderBySql = Generate_OrderBySql(stmtInfo); whereSql = Generate_WhereSql(stmtInfo); if(string.IsNullOrEmpty(predictWord)) finalSql = string.Format("SELECT {1} FROM {2}{3}{4}", predictWord, columnSql, tableName, whereSql, orderBySql); else finalSql = string.Format("SELECT {0} {1} FROM {2}{3}{4}", predictWord, columnSql, tableName, whereSql, orderBySql); return finalSql; }
private static string Generate_WhereSql(SelectStmtInfo stmtInfo) { if (stmtInfo.WhereCondition == null) return string.Empty; var conditions=_Generate_WhereSql(stmtInfo.WhereCondition); if(string.IsNullOrEmpty(conditions)) return string.Empty; return string.Format(" WHERE {0}", conditions); }