/// <summary>
/// 使用 ROWNUM 来进行分页。
/// </summary>
/// <param name="raw">The raw.</param>
/// <param name="pagingInfo">The paging information.</param>
/// <returns></returns>
/// <exception cref="System.ArgumentNullException">pagingInfo</exception>
/// <exception cref="System.InvalidProgramException">必须排序后才能使用分页功能。</exception>
protected override ISqlSelect ModifyToPagingTree(SqlSelect raw, PagingInfo pagingInfo)
{
if (PagingInfo.IsNullOrEmpty(pagingInfo)) { throw new ArgumentNullException("pagingInfo"); }
if (!raw.HasOrdered()) { throw new InvalidProgramException("必须排序后才能使用分页功能。"); }
var startRow = pagingInfo.PageSize * (pagingInfo.PageNumber - 1) + 1;
var endRow = startRow + pagingInfo.PageSize - 1;
var res = MakePagingTree(raw, startRow, endRow);
return res;
}
protected virtual SqlSelect VisitSqlSelect(SqlSelect sqlSelect)
{
if (sqlSelect.Selection != null)
{
this.Visit(sqlSelect.Selection);
}
this.Visit(sqlSelect.From);
if (sqlSelect.Where != null)
{
this.Visit(sqlSelect.Where);
}
if (sqlSelect.HasOrdered())
{
for (int i = 0, c = sqlSelect.OrderBy.Count; i < c; i++)
{
var item = sqlSelect.OrderBy[i] as SqlNode;
this.Visit(item);
}
}
return(sqlSelect);
}
/// <summary>
/// 为指定的原始查询生成指定分页效果的新查询。
/// </summary>
/// <param name="raw">原始查询</param>
/// <param name="pagingInfo">分页信息。</param>
/// <returns></returns>
/// <exception cref="System.ArgumentNullException">pagingInfo</exception>
/// <exception cref="System.InvalidProgramException">必须排序后才能使用分页功能。</exception>
protected virtual ISqlSelect ModifyToPagingTree(SqlSelect raw, PagingInfo pagingInfo)
{
if (PagingInfo.IsNullOrEmpty(pagingInfo)) { throw new ArgumentNullException("pagingInfo"); }
if (!raw.HasOrdered()) { throw new InvalidProgramException("必须排序后才能使用分页功能。"); }
/*********************** 代码块解释 *********************************
*
* 使用 ROW_NUMBER() 函数，此函数 SqlServer、Oracle 都可使用。
* 注意，这个方法只支持不太复杂 SQL 的转换。
*
* 源格式：
* select ...... from ...... order by xxxx asc, yyyy desc
* 不限于以上格式，只要满足没有复杂的嵌套查询，最外层是一个 Select 和 From 语句即可。
*
* 目标格式：
* select * from (select ......, row_number() over(order by xxxx asc, yyyy desc) _rowNumber from ......) x where x._rowNumber<10 and x._rowNumber>5;
**********************************************************************/
var startRow = pagingInfo.PageSize * (pagingInfo.PageNumber - 1) + 1;
var endRow = startRow + pagingInfo.PageSize - 1;
var innerSelect = new SqlSelect();
var selection = new SqlArray();
if (raw.Selection != null)
{
selection.Items.Add(raw.Selection);
}
selection.Items.Add(new SqlNodeList
{
new SqlLiteral { FormattedSql = "row_number() over (" },
raw.OrderBy,
new SqlLiteral { FormattedSql = ") _rowNumber" }
});
innerSelect.Selection = selection;
var subSelect = new SqlSubSelect
{
Select = innerSelect,
Alias = "x"
};
var rowNumberColumn = new SqlTree.SqlColumn
{
Table = subSelect,
ColumnName = "_rowNumber"
};
var pagingSelect = new SqlSelect();
pagingSelect.From = subSelect;
pagingSelect.Where = new SqlTree.SqlBinaryConstraint
{
Left = new SqlTree.SqlColumnConstraint
{
Column = rowNumberColumn,
Operator = SqlColumnConstraintOperator.GreaterEqual,
Value = startRow
},
Opeartor = SqlBinaryConstraintType.And,
Right = new SqlTree.SqlColumnConstraint
{
Column = rowNumberColumn,
Operator = SqlColumnConstraintOperator.LessEqual,
Value = endRow
}
};
return pagingSelect;
}
/// <summary>
/// 访问 sql 语法树中的每一个结点，并生成相应的 Sql 语句。
/// </summary>
/// <param name="tree">The tree.</param>
/// <param name="pagingInfo">The paging information.</param>
public void Generate(SqlSelect tree, PagingInfo pagingInfo = null)
{
ISqlSelect res = tree;
if (!PagingInfo.IsNullOrEmpty(pagingInfo))
{
res = ModifyToPagingTree(tree, pagingInfo);
}
base.Visit(res);
}
protected virtual SqlSelect VisitSqlSelect(SqlSelect sqlSelect)
{
if (sqlSelect.Selection != null)
{
this.Visit(sqlSelect.Selection);
}
this.Visit(sqlSelect.From);
if (sqlSelect.Where != null)
{
this.Visit(sqlSelect.Where);
}
if (sqlSelect.HasOrdered())
{
for (int i = 0, c = sqlSelect.OrderBy.Count; i < c; i++)
{
var item = sqlSelect.OrderBy.Items[i] as SqlNode;
this.Visit(item);
}
}
return sqlSelect;
}
public void ORM_SqlTree_Select_Where_AndOr_ORA()
{
var select = new SqlSelect();
var table = new SqlTable { TableName = "Table1" };
select.Selection = new SqlSelectAll();
select.From = table;
select.Where = new SqlBinaryConstraint
{
Left = new SqlColumnConstraint
{
Column = new SqlColumn { Table = table, ColumnName = "Column1" },
Operator = SqlColumnConstraintOperator.Equal,
Value = "A"
},
Opeartor = SqlBinaryConstraintType.Or,
Right = new SqlBinaryConstraint
{
Left = new SqlBinaryConstraint
{
Left = new SqlColumnConstraint
{
Column = new SqlColumn { Table = table, ColumnName = "Column2" },
Operator = SqlColumnConstraintOperator.Equal,
Value = "A2"
},
Opeartor = SqlBinaryConstraintType.Or,
Right = new SqlColumnConstraint
{
Column = new SqlColumn { Table = table, ColumnName = "Column2" },
Operator = SqlColumnConstraintOperator.Equal,
Value = "B2"
}
},
Opeartor = SqlBinaryConstraintType.And,
Right = new SqlColumnConstraint
{
Column = new SqlColumn { Table = table, ColumnName = "Column1" },
Operator = SqlColumnConstraintOperator.Equal,
Value = "B"
}
}
};
var generator = new OracleSqlGenerator { AutoQuota = false };
generator.Generate(select);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT *
FROM TABLE1
WHERE TABLE1.COLUMN1 = {0} OR (TABLE1.COLUMN2 = {1} OR TABLE1.COLUMN2 = {2}) AND TABLE1.COLUMN1 = {3}");
Assert.IsTrue(sql.Parameters.Count == 4);
Assert.IsTrue(sql.Parameters[0].ToString() == "A");
Assert.IsTrue(sql.Parameters[1].ToString() == "A2");
Assert.IsTrue(sql.Parameters[2].ToString() == "B2");
Assert.IsTrue(sql.Parameters[3].ToString() == "B");
}
public void ORM_SqlTree_Select_Top()
{
var select = new SqlSelect();
select.Selection = new SqlSelectAll();
var t = new SqlTable { TableName = "Table1" }; ;
select.From = t;
select.OrderBy = new SqlOrderByList
{
new SqlOrderBy
{
Column = new SqlColumn
{
Table = t,
ColumnName = "Id",
}
}
};
var generator = new SqlServerSqlGenerator { AutoQuota = false };
generator.Generate(select, new PagingInfo(1, 10));
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT TOP 10 *
FROM Table1
ORDER BY Table1.Id ASC");
}
public void ORM_SqlTree_Select_SelectFromSelectResult_ORA()
{
var userTable = new SqlTable { TableName = "User" };
var subSelectRef = new SqlSubSelect
{
Select = new SqlSelect
{
Selection = new SqlSelectAll(),
From = userTable,
Where = new SqlColumnConstraint
{
Column = new SqlColumn { Table = userTable, ColumnName = "Id" },
Operator = SqlColumnConstraintOperator.Greater,
Value = 0
}
},
Alias = "T"
};
var select = new SqlSelect();
select.Selection = new SqlSelectAll();
select.From = subSelectRef;
select.Where = new SqlColumnConstraint
{
Column = new SqlColumn { Table = subSelectRef, ColumnName = "Id" },
Operator = SqlColumnConstraintOperator.Less,
Value = 100
};
var generator = new OracleSqlGenerator { AutoQuota = false };
generator.Generate(select);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT *
FROM (
SELECT *
FROM USER
WHERE USER.ID > {0}
) T
WHERE T.ID < {1}");
}
public void ORM_SqlTree_Select_Paging()
{
var table = new SqlTable { TableName = "ASN" };
var select = new SqlSelect
{
Selection = new SqlSelectAll(),
From = table,
Where = new SqlColumnConstraint
{
Column = new SqlColumn { Table = table, ColumnName = Entity.IdProperty.Name },
Operator = SqlColumnConstraintOperator.Greater,
Value = 0
},
OrderBy = new SqlOrderByList
{
Items =
{
new SqlOrderBy
{
Column = new SqlColumn{ Table = table, ColumnName = "AsnCode" },
Direction = OrderDirection.Ascending
}
}
}
};
var generator = new SqlServerSqlGenerator { AutoQuota = false };
generator.Generate(select);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() ==
@"SELECT *
FROM ASN
WHERE ASN.Id > {0}
ORDER BY ASN.AsnCode ASC");
generator = new SqlServerSqlGenerator { AutoQuota = false };
generator.Generate(select, new PagingInfo(3, 10));
var pagingSql = generator.Sql;
Assert.IsTrue(pagingSql.ToString() ==
@"SELECT TOP 10 *
FROM ASN
WHERE ASN.Id > {0} AND ASN.Id NOT IN (
SELECT TOP 20 ASN.Id
FROM ASN
WHERE ASN.Id > {1}
ORDER BY ASN.AsnCode ASC
)
ORDER BY ASN.AsnCode ASC");
}
public void ORM_SqlTree_Select_ChildrenAll()
{
var articleTable = new SqlTable { TableName = "Article" };
var userTable = new SqlTable { TableName = "User", Alias = "u" };
var select = new SqlSelect
{
Selection = new SqlSelectAll(),
From = userTable,
Where = new SqlBinaryConstraint
{
Left = new SqlColumnConstraint
{
Column = new SqlColumn { Table = userTable, ColumnName = "Id" },
Operator = SqlColumnConstraintOperator.Greater,
Value = 0
},
Opeartor = SqlBinaryConstraintType.And,
Right = new SqlNotConstraint
{
Constraint = new SqlExistsConstraint
{
Select = new SqlSelect
{
Selection = new SqlLiteral { FormattedSql = "0" },
From = articleTable,
Where = new SqlBinaryConstraint
{
Left = new SqlColumnsComparisonConstraint
{
LeftColumn = new SqlColumn { Table = articleTable, ColumnName = "UserId" },
RightColumn = new SqlColumn { Table = userTable, ColumnName = "Id" }
},
Opeartor = SqlBinaryConstraintType.And,
Right = new SqlColumnConstraint
{
Column = new SqlColumn { Table = articleTable, ColumnName = "Id" },
Operator = SqlColumnConstraintOperator.Greater,
Value = 0
}
}
}
}
}
}
};
var generator = new SqlServerSqlGenerator { AutoQuota = false };
generator.Generate(select);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT *
FROM User AS u
WHERE u.Id > {0} AND NOT (EXISTS (
SELECT 0
FROM Article
WHERE Article.UserId = u.Id AND Article.Id > {1}
))");
}
public void ORM_SqlTree_Select_Alias()
{
var select = new SqlSelect();
var table = new SqlTable { TableName = "Table1", Alias = "t1" };
var column1 = new SqlColumn { Table = table, ColumnName = "Column1", Alias = "c1" };
var column2 = new SqlColumn { Table = table, ColumnName = "Column2", Alias = "c2" };
select.Selection = new SqlArray
{
Items = { column1, column2 }
};
select.From = table;
select.Where = new SqlColumnConstraint
{
Column = column2,
Operator = SqlColumnConstraintOperator.Equal,
Value = "Column2Value"
};
var generator = new SqlServerSqlGenerator { AutoQuota = false };
generator.Generate(select);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT t1.Column1 AS c1, t1.Column2 AS c2
FROM Table1 AS t1
WHERE t1.Column2 = {0}");
Assert.IsTrue(sql.Parameters.Count == 1);
Assert.IsTrue(sql.Parameters[0].ToString() == "Column2Value");
}
public void ORM_SqlTree_Select()
{
var select = new SqlSelect();
var table = new SqlTable { TableName = "Table1" };
var column1 = new SqlColumn { Table = table, ColumnName = "Column1" };
var column2 = new SqlColumn { Table = table, ColumnName = "Column2" };
select.Selection = new SqlArray
{
Items = { column1, column2 }
};
select.From = table;
select.Where = new SqlColumnConstraint
{
Column = column2,
Operator = SqlColumnConstraintOperator.Equal,
Value = "Column2Value"
};
var generator = new SqlServerSqlGenerator();
generator.Generate(select);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() ==
@"SELECT [Table1].[Column1], [Table1].[Column2]
FROM [Table1]
WHERE [Table1].[Column2] = {0}");
Assert.IsTrue(sql.Parameters.Count == 1);
Assert.IsTrue(sql.Parameters[0].ToString() == "Column2Value");
}
public void ORM_SqlTree_Select_Top()
{
var select = new SqlSelect();
select.Top = 10;
select.Selection = new SqlSelectAll();
select.From = new SqlTable { TableName = "Table1" };
var generator = new SqlServerSqlGenerator { AutoQuota = false };
generator.Generate(select);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT TOP 10 *
FROM Table1");
}
public void ORM_SqlTree_Select_Paging_PageNumer1()
{
var table = new SqlTable { TableName = "ASN" };
var pk = new SqlColumn { Table = table, ColumnName = "Id" };
var select = new SqlSelect
{
Selection = new SqlSelectAll(),
From = table,
Where = new SqlColumnConstraint
{
Column = pk,
Operator = SqlColumnConstraintOperator.Greater,
Value = 0
},
OrderBy = new List<SqlOrderBy>
{
new SqlOrderBy
{
Column = new SqlColumn{ Table = table, ColumnName = "AsnCode" },
Direction = OrderDirection.Ascending
}
}
};
var generator = new SqlServerSqlGenerator { AutoQuota = false };
generator.Generate(select);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() ==
@"SELECT *
FROM ASN
WHERE ASN.Id > {0}
ORDER BY ASN.AsnCode ASC");
var pagedSelect = generator.ModifyToPagingTree(select, pk, new PagingInfo(1, 10));
generator = new SqlServerSqlGenerator { AutoQuota = false };
generator.Generate(pagedSelect);
var pagingSql = generator.Sql;
Assert.IsTrue(pagingSql.ToString() ==
@"SELECT TOP 10 *
FROM ASN
WHERE ASN.Id > {0}
ORDER BY ASN.AsnCode ASC");
}
public void ORM_SqlTree_Select_Oracle()
{
var select = new SqlSelect();
var table = new SqlTable { TableName = "Table1" };
var column1 = new SqlColumn { Table = table, ColumnName = "Column1" };
var column2 = new SqlColumn { Table = table, ColumnName = "Column2" };
select.Selection = new SqlArray
{
Items = { column1, column2 }
};
select.From = table;
select.Where = new SqlColumnConstraint
{
Column = column2,
Operator = SqlColumnConstraintOperator.Equal,
Value = "Column2Value"
};
var generator = new OracleSqlGenerator();
generator.Generate(select);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() ==
@"SELECT ""TABLE1"".""COLUMN1"", ""TABLE1"".""COLUMN2""
FROM ""TABLE1""
WHERE ""TABLE1"".""COLUMN2"" = {0}");
Assert.IsTrue(sql.Parameters.Count == 1);
Assert.IsTrue(sql.Parameters[0].ToString() == "Column2Value");
}
public void ORM_SqlTree_Select_OuterJoin_ORA()
{
var select = new SqlSelect();
var table = new SqlTable { TableName = "Article", Alias = "a" };
var userTable = new SqlTable { TableName = "User", Alias = "u" };
select.Selection = new SqlSelectAll { Table = table };
select.From = new SqlJoin
{
Left = table,
JoinType = SqlJoinType.LeftOuter,
Right = userTable,
Condition = new SqlColumnsComparisonConstraint
{
LeftColumn = new SqlColumn { Table = table, ColumnName = "UserId" },
RightColumn = new SqlColumn { Table = userTable, ColumnName = "Id" },
}
};
select.Where = new SqlColumnConstraint
{
Column = new SqlColumn { Table = userTable, ColumnName = "UserName" },
Operator = SqlColumnConstraintOperator.Equal,
Value = "HuQingfang"
};
var generator = new OracleSqlGenerator { AutoQuota = false };
generator.Generate(select);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT A.*
FROM ARTICLE A
LEFT OUTER JOIN USER U ON A.USERID = U.ID
WHERE U.USERNAME = {0}");
Assert.IsTrue(sql.Parameters.Count == 1);
Assert.IsTrue(sql.Parameters[0].ToString() == "HuQingfang");
}
public void ORM_SqlTree_Select_ChildrenExists_ORA()
{
var articleTable = new SqlTable { TableName = "Article" };
var userTable = new SqlTable { TableName = "User", Alias = "u" };
var select = new SqlSelect();
select.Selection = new SqlSelectAll();
select.From = userTable;
select.Where = new SqlBinaryConstraint
{
Left = new SqlColumnConstraint
{
Column = new SqlColumn { Table = userTable, ColumnName = "Id" },
Operator = SqlColumnConstraintOperator.Greater,
Value = 0
},
Opeartor = SqlBinaryConstraintType.And,
Right = new SqlExistsConstraint
{
Select = new SqlSelect
{
Selection = new SqlLiteral { FormattedSql = "0" },
From = articleTable,
Where = new SqlColumnsComparisonConstraint
{
LeftColumn = new SqlColumn { Table = articleTable, ColumnName = "UserId" },
RightColumn = new SqlColumn { Table = userTable, ColumnName = "Id" }
}
}
}
};
var generator = new OracleSqlGenerator { AutoQuota = false };
generator.Generate(select);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT *
FROM USER U
WHERE U.ID > {0} AND EXISTS (
SELECT 0
FROM ARTICLE
WHERE ARTICLE.USERID = U.ID
)");
}
public void ORM_SqlTree_Select_Paging_PageNumer1_ORA()
{
var table = new SqlTable { TableName = "ASN" };
var select = new SqlSelect
{
Selection = new SqlSelectAll(),
From = table,
Where = new SqlColumnConstraint
{
Column = new SqlColumn { Table = table, ColumnName = Entity.IdProperty.Name },
Operator = SqlColumnConstraintOperator.Greater,
Value = 0
},
OrderBy = new SqlOrderByList
{
Items =
{
new SqlOrderBy
{
Column = new SqlColumn{ Table = table, ColumnName = "AsnCode" },
Direction = OrderDirection.Ascending
}
}
}
};
var generator = new OracleSqlGenerator { AutoQuota = false };
generator.Generate(select);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() ==
@"SELECT *
FROM ASN
WHERE ASN.ID > {0}
ORDER BY ASN.ASNCODE ASC");
generator = new OracleSqlGenerator { AutoQuota = false };
generator.Generate(select, new PagingInfo(1, 10));
var pagingSql = generator.Sql;
Assert.IsTrue(pagingSql.ToString() ==
@"SELECT * FROM
(
SELECT T.*, ROWNUM RN
FROM
(
SELECT *
FROM ASN
WHERE ASN.ID > {0}
ORDER BY ASN.ASNCODE ASC
) T
WHERE ROWNUM <= 10
)
WHERE RN >= 1");
}
public void ORM_SqlTree_Select_Count_ORA()
{
var select = new SqlSelect();
select.IsCounting = true;
select.From = new SqlTable { TableName = "Table1" };
var generator = new OracleSqlGenerator { AutoQuota = false };
generator.Generate(select);
Assert.IsTrue(generator.Sql.ToString() == @"SELECT COUNT(0)
FROM TABLE1");
}
public void ORM_SqlTree_Select_Star_ORA()
{
var select = new SqlSelect();
select.Selection = new SqlSelectAll();
select.From = new SqlTable { TableName = "Table1" };
var generator = new OracleSqlGenerator { AutoQuota = false };
generator.Generate(select);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT *
FROM TABLE1");
}
public void ORM_SqlTree_Select_Distinct()
{
var select = new SqlSelect();
select.IsDistinct = true;
select.Selection = new SqlSelectAll();
select.From = new SqlTable { TableName = "Table1" };
var generator = new SqlServerSqlGenerator { AutoQuota = false };
generator.Generate(select);
Assert.IsTrue(generator.Sql.ToString() == @"SELECT DISTINCT *
FROM Table1");
}
public void ORM_SqlTree_Select_Top_ORA()
{
var select = new SqlSelect();
select.Selection = new SqlSelectAll();
var t = new SqlTable { TableName = "Table1" }; ;
select.From = t;
select.OrderBy = new SqlOrderByList
{
new SqlOrderBy
{
Column = new SqlColumn
{
Table = t,
ColumnName = "Id",
}
}
};
var generator = new OracleSqlGenerator { AutoQuota = false };
generator.Generate(select, new PagingInfo(1, 10));
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT * FROM
(
SELECT T.*, ROWNUM RN
FROM
(
SELECT *
FROM TABLE1
ORDER BY TABLE1.ID ASC
) T
WHERE ROWNUM <= 10
)
WHERE RN >= 1");
}
public void ORM_SqlTree_Select_InnerJoin_TwoToSingleTable()
{
var select = new SqlSelect();
var table = new SqlTable { TableName = "Article", Alias = "a" };
var userTable1 = new SqlTable { TableName = "User", Alias = "u1" };
var userTable2 = new SqlTable { TableName = "User", Alias = "u2" };
select.Selection = new SqlSelectAll { Table = table };
select.From = new SqlJoin
{
Left = new SqlJoin
{
Left = table,
JoinType = SqlJoinType.Inner,
Right = userTable1,
Condition = new SqlColumnsComparisonConstraint
{
LeftColumn = new SqlColumn { Table = table, ColumnName = "UserId" },
RightColumn = new SqlColumn { Table = userTable1, ColumnName = "Id" },
}
},
JoinType = SqlJoinType.Inner,
Right = userTable2,
Condition = new SqlColumnsComparisonConstraint
{
LeftColumn = new SqlColumn { Table = table, ColumnName = "AdministratorId" },
RightColumn = new SqlColumn { Table = userTable2, ColumnName = "Id" },
}
};
select.Where = new SqlColumnConstraint
{
Column = new SqlColumn { Table = userTable2, ColumnName = "UserName" },
Operator = SqlColumnConstraintOperator.Equal,
Value = "HuQingfang"
};
var generator = new SqlServerSqlGenerator { AutoQuota = false };
generator.Generate(select);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT a.*
FROM Article AS a
INNER JOIN User AS u1 ON a.UserId = u1.Id
INNER JOIN User AS u2 ON a.AdministratorId = u2.Id
WHERE u2.UserName = {0}");
Assert.IsTrue(sql.Parameters.Count == 1);
Assert.IsTrue(sql.Parameters[0].ToString() == "HuQingfang");
}
public void ORM_SqlTree_Select_WithoutQuota_ORA()
{
var select = new SqlSelect();
var table = new SqlTable { TableName = "Table1" };
var column1 = new SqlColumn { Table = table, ColumnName = "Column1" };
var column2 = new SqlColumn { Table = table, ColumnName = "Column2" };
select.Selection = new SqlArray
{
Items = { column1, column2 }
};
select.From = table;
select.Where = new SqlColumnConstraint
{
Column = column2,
Operator = SqlColumnConstraintOperator.Equal,
Value = "Column2Value"
};
var generator = new OracleSqlGenerator { AutoQuota = false };
generator.Generate(select);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT TABLE1.COLUMN1, TABLE1.COLUMN2
FROM TABLE1
WHERE TABLE1.COLUMN2 = {0}");
Assert.IsTrue(sql.Parameters.Count == 1);
Assert.IsTrue(sql.Parameters[0].ToString() == "Column2Value");
}
public void ORM_SqlTree_Select_InSubSelect_Join()
{
var select = new SqlSelect();
var adminTable = new SqlTable { TableName = "User", Alias = "Administrator" };
var articleTable = new SqlTable { TableName = "Article" };
var subSelect = new SqlSelect
{
Selection = new SqlColumn { Table = articleTable, ColumnName = "UserId" },
From = new SqlJoin
{
Left = articleTable,
JoinType = SqlJoinType.Inner,
Right = adminTable,
Condition = new SqlColumnsComparisonConstraint
{
LeftColumn = new SqlColumn { Table = articleTable, ColumnName = "AdministratorId" },
RightColumn = new SqlColumn { Table = adminTable, ColumnName = "Id" },
}
},
Where = new SqlColumnConstraint
{
Column = new SqlColumn { Table = adminTable, ColumnName = "Id" },
Operator = SqlColumnConstraintOperator.Equal,
Value = 1
}
};
var userTable = new SqlTable { TableName = "User" };
select.Selection = new SqlSelectAll();
select.From = userTable;
select.Where = new SqlColumnConstraint
{
Column = new SqlColumn { Table = userTable, ColumnName = "Id" },
Operator = SqlColumnConstraintOperator.In,
Value = subSelect
};
var generator = new SqlServerSqlGenerator { AutoQuota = false };
generator.Generate(select);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT *
FROM User
WHERE User.Id IN (
SELECT Article.UserId
FROM Article
INNER JOIN User AS Administrator ON Article.AdministratorId = Administrator.Id
WHERE Administrator.Id = {0}
)");
Assert.IsTrue(sql.Parameters.Count == 1);
Assert.IsTrue(sql.Parameters[0].Equals(1));
}
public void ORM_SqlTree_Select_InSubSelect_Join_ORA()
{
var select = new SqlSelect();
var adminTable = new SqlTable { TableName = "User", Alias = "Administrator" };
var articleTable = new SqlTable { TableName = "Article" };
var subSelect = new SqlSelect
{
Selection = new SqlColumn { Table = articleTable, ColumnName = "UserId" },
From = new SqlJoin
{
Left = articleTable,
JoinType = SqlJoinType.Inner,
Right = adminTable,
Condition = new SqlColumnsComparisonConstraint
{
LeftColumn = new SqlColumn { Table = articleTable, ColumnName = "AdministratorId" },
RightColumn = new SqlColumn { Table = adminTable, ColumnName = "Id" },
}
},
Where = new SqlColumnConstraint
{
Column = new SqlColumn { Table = adminTable, ColumnName = "Id" },
Operator = SqlColumnConstraintOperator.Equal,
Value = 1
}
};
var userTable = new SqlTable { TableName = "User" };
select.Selection = new SqlSelectAll();
select.From = userTable;
select.Where = new SqlColumnConstraint
{
Column = new SqlColumn { Table = userTable, ColumnName = "Id" },
Operator = SqlColumnConstraintOperator.In,
Value = subSelect
};
var generator = new OracleSqlGenerator { AutoQuota = false };
generator.Generate(select);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT *
FROM USER
WHERE USER.ID IN (
SELECT ARTICLE.USERID
FROM ARTICLE
INNER JOIN USER ADMINISTRATOR ON ARTICLE.ADMINISTRATORID = ADMINISTRATOR.ID
WHERE ADMINISTRATOR.ID = {0}
)");
Assert.IsTrue(sql.Parameters.Count == 1);
Assert.IsTrue(sql.Parameters[0].Equals(1));
}
public void ORM_SqlTree_Select_InSubSelect_ORA()
{
var select = new SqlSelect();
var articleTable = new SqlTable { TableName = "Article" };
var subSelect = new SqlSelect
{
Selection = new SqlColumn { Table = articleTable, ColumnName = "UserId" },
From = articleTable,
Where = new SqlColumnConstraint
{
Column = new SqlColumn { Table = articleTable, ColumnName = "CreateDate" },
Operator = SqlColumnConstraintOperator.Equal,
Value = DateTime.Today
}
};
var userTable = new SqlTable { TableName = "User" };
select.Selection = new SqlSelectAll();
select.From = userTable;
select.Where = new SqlColumnConstraint
{
Column = new SqlColumn { Table = userTable, ColumnName = "Id" },
Operator = SqlColumnConstraintOperator.In,
Value = subSelect
};
var generator = new OracleSqlGenerator { AutoQuota = false };
generator.Generate(select);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT *
FROM USER
WHERE USER.ID IN (
SELECT ARTICLE.USERID
FROM ARTICLE
WHERE ARTICLE.CREATEDATE = {0}
)");
Assert.IsTrue(sql.Parameters.Count == 1);
Assert.IsTrue(sql.Parameters[0].Equals(DateTime.Today));
}
/// <summary>
/// 生成 Selection 中的语句
/// </summary>
/// <param name="sqlSelect"></param>
protected virtual void GenerateSelection(SqlSelect sqlSelect)
{
if (sqlSelect.IsCounting)
{
_sql.Append("COUNT(0)");
}
else
{
if (sqlSelect.IsDistinct)
{
_sql.Append("DISTINCT ");
}
if (sqlSelect.Selection == null)
{
_sql.Append("*");
}
else
{
this.Visit(sqlSelect.Selection);
}
}
}
public void ORM_SqlTree_Select_OrderBy_ORA()
{
var select = new SqlSelect();
var table = new SqlTable { TableName = "Table1", Alias = "t1" };
var column1 = new SqlColumn { Table = table, ColumnName = "Column1", Alias = "c1" };
var column2 = new SqlColumn { Table = table, ColumnName = "Column2", Alias = "c2" };
select.Selection = new SqlArray
{
Items = { column1, column2 }
};
select.From = table;
select.Where = new SqlColumnConstraint
{
Column = column2,
Operator = SqlColumnConstraintOperator.Equal,
Value = "Column2Value"
};
select.OrderBy = new SqlOrderByList
{
new SqlOrderBy
{
Column = column2,
Direction = OrderDirection.Descending
}
};
var generator = new OracleSqlGenerator { AutoQuota = false };
generator.Generate(select);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT T1.COLUMN1 C1, T1.COLUMN2 C2
FROM TABLE1 T1
WHERE T1.COLUMN2 = {0}
ORDER BY T1.COLUMN2 DESC");
Assert.IsTrue(sql.Parameters.Count == 1);
Assert.IsTrue(sql.Parameters[0].ToString() == "Column2Value");
}
protected override SqlSelect VisitSqlSelect(SqlSelect sqlSelect)
{
_sql.Append("SELECT ");
//SELECT
this.GenerateSelection(sqlSelect);
//FROM
_sql.AppendLine();
_sql.Append("FROM ");
this.Visit(sqlSelect.From);
//WHERE
if (sqlSelect.Where != null)
{
_sql.AppendLine();
_sql.Append("WHERE ");
this.Visit(sqlSelect.Where);
}
//ORDER BY
if (!sqlSelect.IsCounting && sqlSelect.OrderBy != null && sqlSelect.OrderBy.Count > 0)
{
_sql.AppendLine();
this.Visit(sqlSelect.OrderBy);
}
return sqlSelect;
}
private static ISqlSelect MakePagingTree_ReserveMethod(SqlSelect raw, int startRow, int endRow)
{
/*********************** 代码块解释 *********************************
* 源格式：
* SELECT *
* FROM A
* WHERE A.Id > 0
* ORDER BY A.NAME ASC
*
* 目标格式：
* SELECT * FROM
* (SELECT A.*, ROWNUM RN
* FROM A
* WHERE A.Id > 0 AND ROWNUM <= 20
* ORDER BY A.NAME ASC)
* WHERE RN >= 10
*
* 这种方法可能存在问题：
* 因为源 Sql 可能是：Select * From A Join B，这时表示结果集需要显示 A 和 B 的所有字段，
* 但是此方法会转换为：Select A.* From A Join B。比较麻烦，暂不处理
**********************************************************************/
var innerSelect = new SqlSelect
{
IsDistinct = raw.IsDistinct,
From = raw.From,
Where = AppendWhere(raw.Where, new SqlLiteral("ROWNUM <= " + endRow)),
OrderBy = raw.OrderBy
};
//内部的 Select 子句中，不能简单地使用 "*, ROWNUM"，而是需要使用 "A.*, ROWNUM"
var rawSelection = raw.Selection;
if (rawSelection == null)
{
//默认约定第一张表，就是
var table = new FirstTableFinder().Find(raw.From);
rawSelection = new SqlSelectAll
{
Table = table
};
}
innerSelect.Selection = new SqlNodeList
{
rawSelection,
new SqlLiteral(", ROWNUM RN")
};
var res = new SqlSelect
{
Selection = SqlSelectAll.Default,
From = new SqlSubSelect
{
Select = innerSelect
},
Where = new SqlLiteral("RN >= " + startRow)
};
return res;
}
