Object Query Language 使用指南.doc

Object Query Language使用指南基本功能1 Object Query Language提供了一种基于对象的查询语言。 2 支持OPath语法风格的路径表达式。3 支持属于多个Entity的多列。4 OO风格指定列：如OrderLine.Order.Customer.ContactName。5 支持聚合函数，SQL99标准函数（基于Entity语义）。6 支持列间表达式计算。7 支持Group & Order & As(列别名)8 支持Entity View定义，可以在Query定义中使用已有的Entity View!9 支持名空间的使用，支持中文10 引入from子句，可支持left join, right join, full join, cross join11 在select, from, where子句中支持子查询12 用OQL别名指代OPath, 使OQL的表达更加简洁13 UDF(User Defined Function)的直接支持。14 union, union all.15 临时表的支持。16 OQL风格指南17 New！对风格a ？b ：c 条件表达式和Case When 表达式的支持（第页）18 New！对标准日期时间函数的支持（第页）19 New！对cast/convert类型转换函数的支持 （第7页）20 New！支持Update & Delete语句！ （第7页）2优势与比较直接基于实体模型，避免手工执行从实体到表的映射。3安装将$DevPubRefBusinessUFSoft.UBF.View.Query.dll添加到项目引用中, $DevPubRefBusinessUFSoft.UBF.Business.Adapter.dll ，$DevPubRefSystem下UFSoft.UBF.Util.Expression.dll, UFSoft.UBF.Util.dll, UFSoft.UBF.Util.SQLBuilder.dll，UFSoft.UBF.Util.Log.dll, Tools.dll需要拷贝到运行目录。environment.xml中有一新的配置节：/environment/view/creator, 如$Dev/PubRef/Business下的environment.xml所示。 4. 语言指南UBF对象查询语言有两种风格：简单条件过滤与复杂报表查询。简单条件过滤在概念上是针对一个强类型的对象集合指定一个过滤条件，对集合中的每个对象应用此过滤条件进行筛选，结果是所有满足条件的对象所构成的子集合。这种场景主要在UBF BE编程中，由某种BE.Finder.Find(xxx)返回对象集合。复杂报表查询基本上是SQL select语句在Object-Oriented中的对应物，它用对象的类名，属性名等概念表达SQL中原来用表名，列名来表达的查询概念，但返回的是一个表格状的数据。你可以使用order by/group by 等SQL类似的语言结构。在BE中有EntityDataQuery API，可以获取DataTable 或DataReader来对结果进行操纵。由于简单条件过滤仅在原单一类型上过滤/排序，因此可以返回该类型的对象集合，但复杂报表查询可以任意组合不同的对象类型的属性作为返回列，这种组合不对应任意已有的类，因此返回结果是弱类型的表格形式。考虑到与C#风格一致，OQL是大小写敏感的，因此所有的关键字都必须小写。 简单形式兼容ObjectSpace OPath（历史上最早由OPath启发而来）, 表示查询条件，使用时应当指定对应的主实体，即条件是在该主实体上过滤。OQL表示条件：（仅用在BE.Finder.Find API 编程中, 总是针对实体类型而言。）简单条件：Customer.ID = ALFKI, Customer是当前实体的一个属性，类型为Customer，ID是Customer的一个属性，在元源数据中有定义，如Customer映射到Customers表， ID映射为CustomerID列。多Entity Join条件：Customer.Order.ShipName = ,QueryEngine将Order视为Customer类的一个属性，仍然没有即报错。最后一列总是属性，此处ShipName是Order实体的属性。OPath风格指定条件：Customer.Order.ID 100 and Customer.Order.ID != 0,更复杂的路径表达式:Customer.Order.OrderLine.ProductID is not null,注意is not null是针对ProductID列是否为空。Between and:Order.OrderLine.UnitPrice between 23 and 50,逻辑或条件 + 参数:OrderLine.UnitPrice 14 or OrderLine.Quantity 100,join 就是inner join, 与直接使用OPath的效果一样。总共支持left, right, full, 三种外连接，其中的outer关键字是可选的，inner也是可选的。另外，cross join的效果可直接用关键字表达。select * from Item item join item.Bids bid where item.Description like %gc% and bid.Amount 100,select * from Item i full join i.Seller u where u.UserName = steve,select item.ID from Item item right join item.Bids where bid.Amount 100,select item.ID, item.Description, bid.Amount from Item item right outer join item.Bids bid where bid.Amount 100,select * from Cat as cat inner join cat.Mate as mate left outer join cat.Kittens as kitten,select * from Cat as cat cross join cat.Mate.Kittens as kittens,select * from Cat as cat left outer join cat.Kittens as kitten on kitten.BodyWeight 10.0,Cross join.select * from User user, LogRecord log where user.UserName = log.UserName,Distinct关键字select distinct item.Description from Item item,数据库函数upper, 任意这样的数据库函数，用户自定义函数可随意使用select item.StartDate, item.EndDate, upper(item.Name) from Item item,Count（*）select count(*) from Item,Countselect count(item.SuccessfulBid) from Item item,Min， Maxselect min(bid.Amount), max(bid.Amount) from Bid bid where bid.Item.ID = 1,select max(BodyWeight) as max, min(BodyWeight) as min, count(*) as n from Cat cat,Count(distinct )select count(distinct item.Description) from Item item,Group byselect bid.Item.ID, count(bid), avg(bid.Amount) from Bid bid where bid.Item.SuccessfulBid is null group by bid.Item.ID,select user.LastName, count(user) from User user group by user.LastName having user.LastName like A%,相关子查询（correlated subquery）select * from User u where 10 = (select max(b.Amount) from Bid b),全称子查询select * from Item item where 100 all(select b.Amount from item.Bids b),存在子查询select * from Item item where 100 (select avg(cat.Weight) from DomesticCat cat),标量子查询用在select子句中select cat.id, (select max(kit.weight) from cat.kitten kit) from Cat as cat,Union 与 Union All(select * from Cat a) union (select * from Cat b),(select * from Cat a) union all (select * from Cat b),子查询用在from子句中作为”derived class”select * from Cat a left join (select p.Namer from Player p where 3 count(p.Scores) b on c.Name = b.Name where c.Age 3,New！ 对Case When 表达式的支持C风格的条件表达式，是表达简单if else逻辑的推荐方式/ Simple Conditional Expressionselect p.UnitPrice 100 ? p.UnitPrice : p.UnitPrice * 1.3 as UnitPrice from Products as p,条件表达式可以嵌套，可用于表达复杂的if elseif else 判定逻辑/ Nested Conditional Expressionselect p.CategoryID = 1 ? p.UnitPrice * 1.1 : (p.CategoryID = 2? p.UnitPrice * 1.2: p.UnitPrice) as DiscountPrice from Products p,对应于ANSI SQL 标准，有简单CASE表达式和搜索CASE表达式简单CASE表达式/ Simple CASE Expressionselect p.UnitPrice as UnitPrice, case p.CategoryIDwhen 1 then p.UnitPrice * 1.1when 2 then p.UnitPrice * 1.2else p.UnitPriceend as DiscountPrice from Products as p,上述简单CASE表达式可用搜索CASE表达式来代替/ Search CASE Expression can replace Simple CASE Expressionselect p.UnitPrice as UnitPrice,case when p.CategoryID = 1 then p.UnitPrice * 1.1when p.CategoryId = 2 then p.UnitPrice * 1.2else p.UnitPriceend as DiscountPrice from Products as p,一个复杂搜索CASE表达式的例子/ More Complex Search CASE Expressionselect o.Customer.Name as Customer, count(*) as Orders,case when count(*) between 1 and 10 then *when count(*) between 11 and 20 then *when count(*) between 21 and 30 then *else *end as Rating from Orders o group by o.CustomerID,New！ 对dateadd/datediff/datepart/datename中关键字的支持现在可以支持在dateadd/datediff/datepart/datename四个函数调用中用T-SQL风格的关键字day/year/month, etc, 但是这不是一种标准和同一的风格，建议用字符串即单引号的方式函数名大小写均可。select dateadd(day, 3, 20061031) from Productsselect dateadd(day, 3, 20061031) from Products;select datediff(year, 20061031, getdate() from Products;select datediff(dd, 20061031, getdate() from Products;select datepart(month, 20061031) from Products;select datepart(month, getdate() from Products;select datename(Hour, getdate() from Products;select getdate() from Products;select getutcdate() from Products;select day(getdate() from Products;select month(getdate() from Products;select year(getdate() from Products;New！ 对cast类型转换函数的支持OQL中cast必须以下述纯函数风格调用select cast(abc, varchar(5) from Products;convert函数使用没有特殊限制select CONVERT(bigint, convert(varbinary(20), 5.1) from Products;注意字符串函数中, 由于小写的left right在left join 和 right join中被用作关键字，因此left right用作字符串函数名时不能用小写，全大写或大小写混合均可。select Left(abc, 2) from Products;select RIGHT(abc, 2) from Products;New！ 对Update & Delete语句的支持update UFIDA:U9:CBO:PL:PriceList:PriceList p set p.ID = 10232 where p.ID 0;update UFIDA:U9:CBO:PL:PriceList:PriceList set ID = 10232 where ID 100,/ right joinselect * from Northwind:Order o right join o.OrderLines ol where o.Description like %Around% and ol.Quantity 100,/ full joinselect * from Northwind:Order o full join o.Customer u where u.Name = steve,/ distinct keywordselect distinct item.Description from Northwind:Order item,/ database function, e.g. upperselect upper(item.Customer.Name) from Northwind:Order item,/ count(*)select count(*) from Northwind:Order,/ count database functionselect count(item.ID) from Northwind:Order item,/ min, max functionselect min(line.Quantity), max(line.Quantity) from Northwind:OrderLine line where line.Order.ID = 1,/ count(distinct .)select count(distinct item.Description) from Northwind:Order item,/ group byselect line.Order.ID, count(line.Quantity), avg(line.Quantity) from Northwind:OrderLine line where line.Order.Customer is null group by line.Order.ID,/ group byselect line.Order.ID, count(line.Quantity), avg(line.Quantity) from Northwind:OrderLine line group by line.Order.ID order by line.Order.ID,/ group by & havingselect user.Name, count(user.ID) from Northwind:Customer user group by user.Name having user.Name like A%,/ correlated subqueryselect * from Northwind:Order o where 10 all(select b.Quantity from o.OrderLines b),/ correlated subquery, any predicate(existential quantification).select * from Northwind:Order o where 100 = (select max(b.Quantity) from Northwind:OrderLine b),/ union (select a.Name from Northwind:Customer a) union (select b.Name from Northwind:Employee b),/ union all(select a.Name from Northwind:Customer a) union all (select b.Name from Northwind:Employee b),/ subquery in select clause.select ID, Quantity - (select avg(Quantity) from Northwind:OrderLine) as Difference from Northwind:OrderLine ,/ subquery in from clause.select * from Northwind:Order,select ID from (select * from Northwind:OrderLine),select a.ID from (select * from Northwind:OrderLine) a,/ Now it seems that subquery and entity view will also take part in OQLs path based aliasing system./ So difficult!select a.ID from (select * from Northwind:OrderLine) as a left join (select * from Northwind:Order) as b on a.Order = b.ID,select a.ID, b.ID from (select * from Northwind:Order) as a, (select * from Northwind:OrderLine) b,OQL风格指南从上面这些例子可以看出，使用别名方式是OQL的标准，:连接的命空间.属性名，如FIDA:U9:AR:Receival:ReceivalBillLine.LineNum的历史风格只是为了兼容SQL习惯，即表名.列名，但实践表明这种习惯在OQL中是笨拙的，并且减慢解析速度，已经被废弃。风格统一成别名形式和主实体 + 相对于主实体开始的导航表达式两种。别名形式：select 2 as ApplyDocType,3 as DocTag,billhead.id as DocDataTag,billhead.DocNo + billline.LineNum as DataNo,billhead.ReceivalDate as DataDate,0 as PriorityGrade,0 as DocOrderNo,billhead.ReceivalCurrency as DocCurrency,billhead.RCToFCExchRateType as DocExchangeRateType,billhead.RCToFCExchRate as DocExchangeRate,0 as CanEnjoyDiscount,billline.SettlementMethodClass as SettlementMethod,billhead.Department.Name as Department,isnull(billhead.Employee, billhead.ReceivalOperator) as Transactor,billhead.Receiver as Receiver,billhead.Project.Name as Project,null as Item from UFIDA:U9:AR:Receival:ReceivalBillHead billhead inner join billhead.ReceivalBillLine billline where billhead.Organization = 1002006051600036001 and billhead.HasSubReceive=0 and billhead.Customer.CustomerCode = C01 and billhead.ReceivalCurrency = 1105400 and billhead.ID = 25809101使用别名风格从本质上更体现了Object Query的本质语义，即OQL其实是在查询集合，billhead, billline 可以理解成foreach 的循环变量，理解这种风格会使我们U9的应用开发人员在C# 3.0 即Visual Studio 2008上市的时候很快速地掌握C# 3.0的语言查询功能。需要精确控制join的，用别名形式，形如UFIDA:U9:AR:Receival:ReceivalBillHead a inner join a.ReceivalBillLine b ，注意在from子句中的导航表达式只支持两级，即.B形式，若要.B.C,可写为： from A a left join a.B b left join b.C c, 在其它地方如select子句，where子句，导航表达式的级数没有限制。主实体 + 相对于主实体开始的导航表达式：select 2 as ApplyDocType,3 as DocTag, id as DocDataTag,DocNo + ReceivalBillLine.LineNum as DataNo,ReceivalDate as DataDate,0 as PriorityGrade,0 as DocOrderNo,ReceivalCurrency as DocCurrency,RCToFCExchRateType as DocExchangeRateType,RCToFCExchRate as DocExchangeRate,0 as CanEnjoyDiscount,ReceivalBillLine.SettlementMethodClass as SettlementMethod,Department.Name as Department,isnull(Employee, ReceivalOperator) as Transactor,Receiver as Receiver,Project.Name as Project,null as Item from UFIDA:U9:AR:Receival:ReceivalBillHead where Organization = 1002006051600036001 and HasSubReceive=0 and Customer.CustomerCode = C01 and ReceivalCurrency = 1105400 and ID = 25809101简单情形，不关心join时可用上述简单形式，当然也可用别名，别名是OQL的标准形式。SQL风格的类名.属性名是不支持的。不是用类名和属性名表达的，请多参考使用文档，不要硬套的习惯和特征。用户自定义函数与SQL标准函数：SQL标准函数不用任何特殊处理，直接在OQL语句中当函数使用，只是对应的参数用对象风格的导航表达式来表达。 OQL翻译引擎直接将函数名转换成同名的SQL标准函数。用户自定义函数需要在如下格式的xml文件(udf.xml)中注册：其中hello定义如下：create function hello() returns nvarchar(40) asbegin return Hello Faint!end如果不指定owner, OQL默认dbo这样：select ID as ID , hello(), DocType.ID as DocType from UFIDA:U9:CBO:PL:PriceList:PriceList将被翻译为：select A1.ID as ID, dbo.hello(), A2.ID as DocType from CBO_PriceList as A inner join Base_Doc as A1 on (A.ID = A1.ID) left join Base_DocType as A2 on (A1.DocType = A2.ID)“临时表”或用更OO的术语，临时集合，将基于Entity View, a.k.a. Named OQL来实现，将有专门的API来体现，在语法上与Entity View的使用没有区别。 以下是一个通过BE封装的， 在报表DataCommand中临时集合使用例子。注意EntityViewQuery可用using语句。using System;using System.Data; using UFSoft.UBF.Report.Entity ; using UFSoft.UBF.Report ; using UFSoft.UBF.Report.Interface ;using UFSoft.UBF.Sys.Database;using UFSoft.UBF.Business;using UFSoft.UBF.View.Query;namespace U8.UAP.Report.Extention.Test/ / OQLReportCommand 的摘要说明。/ public class OQLReportCommand : BaseReportDataCommandprivate EntityViewQuery q = new EntityViewQuery();public OQLReportCommand()/ TODO: 在此处添加构造函数逻辑/public override IDataReader ExecuteDataReader()/ 创建一个对象查询，可以执行它，也