protected override SqlTable VisitSqlTable(SqlTable sqlTable) { if (_table == null) { _table = sqlTable; return sqlTable; } return base.VisitSqlTable(sqlTable); }
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_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_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_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_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"); }
protected virtual SqlTable VisitSqlTable(SqlTable sqlTable) { return sqlTable; }
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_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_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_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_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_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_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_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_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_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_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)); }
protected override SqlTable VisitSqlTable(SqlTable sqlTable) { this.QuoteAppend(sqlTable.TableName); if (!string.IsNullOrEmpty(sqlTable.Alias)) { this.AppendNameCast(); this.QuoteAppend(sqlTable.Alias); } return sqlTable; }
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"); }