我有一段想要翻译成OCL的SQL代码。由于我不擅长SQL,因此希望通过这种方式提高代码的可维护性。我们使用Interbase 2009、Delphi 2007以及Bold和modeldriven开发。现在我希望在这里找到会说好SQL和OCL的人:-) 原始的SQL代码:
Select Bold_Id, MessageId, ScaniaId, MessageType, MessageTime, Cancellation, ChassieNumber, UserFriendlyFormat, ReceivingOwner, Invalidated, InvalidationReason,
(Select Parcel.MCurrentStates From Parcel
Where ScaniaEdiSolMessage.ReceivingOwner = Parcel.Bold_Id) as ParcelState From ScaniaEdiSolMessage
Where MessageType = 'IFTMBP' and
not Exists (Select * From ScaniaEdiSolMessage EdiSolMsg
Where EdiSolMsg.ChassieNumber = ScaniaEdiSolMessage.ChassieNumber and EdiSolMsg.ShipFromFinland = ScaniaEdiSolMessage.ShipFromFinland and EdiSolMsg.MessageType = 'IFTMBF') and
invalidated = 0 Order By MessageTime desc
经过简化后:
Select Bold_Id, (Select Parcel.MCurrentStates From Parcel
where ScaniaEdiSolMessage.ReceivingOwner = Parcel.Bold_Id) From ScaniaEdiSolMessage
Where MessageType = 'IFTMBP' and not Exists (Select * From ScaniaEdiSolMessage
EdiSolMsg Where EdiSolMsg.ChassieNumber = ScaniaEdiSolMessage.ChassieNumber and
EdiSolMsg.ShipFromFinland = ScaniaEdiSolMessage.ShipFromFinland and
EdiSolMsg.MessageType = 'IFTMBF') and invalidated = 0
注意: MessageType有两种情况,'IFTMBP'和'IFTMBF'。
因此,要列出的表是ScaniaEdiSolMessage。它具有以下属性:
- MessageType: 字符串
- ChassiNumber: 字符串
- ShipFromFinland: 布尔值
- Invalidated: 布尔值
它还与名为ReceivingOwner的表Parcel相关联,其BoldId为关键字。
因此,它似乎列出了ScaniaEdiSolMessage的所有行,然后有一个子查询,也列出了ScaniaEdiSolMessage的所有行,并将其命名为EdiSolMsg。然后排除几乎所有行。实际上,上面的查询从28000条记录中仅返回一个结果。
在OCL中,列出所有实例很容易:
ScaniaEdiSolMessage.allinstances
还可以通过选择过滤行,例如:
ScaniaEdiSolMessage.allinstances->select(shipFromFinland and not invalidated)
但是我不明白如何制作一个OCL来匹配上面的SQL。