public override SqlGenerator CreateSqlGenerator()
{
var generator = new OracleSqlGenerator();
generator.MaxItemsInInClause = MAX_ITEMS_IN_INCLAUSE;
return(generator);
}
public override object ConvertToParameterValue(object value)
{
value = base.ConvertToParameterValue(value);
value = OracleSqlGenerator.PrepareConstraintValueInternal(value);
return(value);
}
public void ORM_TableQuery_Oracle()
{
var f = QueryFactory.Instance;
var table = f.Table<Book>();
var code = table.Column(Book.CodeProperty);
var name = table.Column(Book.NameProperty);
var query = f.Query(
selection: f.Array(code, name),
from: table,
where: f.Constraint(column: code, value: "code", op: PropertyOperator.Equal)
);
var generator = new OracleSqlGenerator();
f.Generate(generator, query);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() ==
@"SELECT ""BOOK"".""CODE"", ""BOOK"".""NAME""
FROM ""BOOK""
WHERE ""BOOK"".""CODE"" = {0}");
Assert.IsTrue(sql.Parameters.Count == 1);
Assert.IsTrue(sql.Parameters[0].ToString() == "code");
}
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_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_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_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_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_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_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");
}
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));
}
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_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_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_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 override SqlGenerator CreateSqlGenerator()
{
var generator = new OracleSqlGenerator();
return(generator);
}
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 override SqlGenerator CreateSqlGenerator()
{
var generator = new OracleSqlGenerator();
generator.MaxItemsInInClause = MAX_ITEMS_IN_INCLAUSE;
return generator;
}