Translate SQL to OCL?

Question

I have a piece of SQL that I want to translate to OCL. I'm not good at SQL so I want to increase maintainability by this. We are using Interbase 2009, Delphi 2007 with Bold and modeldriven development. Now my hope is that someone here both speaks good SQL and OCL :-) The original 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

After a small simplification:

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

NOTE: There are 2 cases for MessageType, 'IFTMBP' and 'IFTMBF'.

So the table to be listed is ScaniaEdiSolMessage. It has attributes like:

• MessageType: String
• ChassiNumber: String
• ShipFromFinland: Boolean
• Invalidated: Boolean

It has also a link to table Parcel named ReceivingOwner with BoldId as key.

So it seems like it list all rows of ScaniaEdiSolMessage and then have a subquery that also list all rows of ScaniaEdiSolMessage and name it EdiSolMsg. Then it exclude almost all rows. In fact the query above give one hit from 28000 records.

In OCL it is easy to list all instances:

ScaniaEdiSolMessage.allinstances

Also easy to filter rows by select for example:

ScaniaEdiSolMessage.allinstances->select(shipFromFinland and not invalidated)

But I do not understand how I should make a OCL to match the SQL above.

Answer 1

Listen to Gabriel and Stephanie, learn more SQL.

You state that you want to make the code more maintainable, yet the number of developers who understand SQL is greater by far than the number of developers who understand OCL.

If you leave the project tomorrow after converting this to OCL, the chances that you'd be able to find someone who could maintain the OCL are very slim. However, the chances that you could find someone to maintain the SQL are very high.

Don't try to fit a square peg in a round hole just because you're good with round hammers :)