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

With MDriven (successor of Bold for Delphi) I would do it like this:

When working with OCL to SQL everything becomes easier if you think about the different set's of information you need to check - and then use ocl operators as ->intersection to find the set you are after.

So in your case you might have a set like this:

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

but you also have a set like this:

ScaniaEdiSolMessage.allinstances->select(m|m.ReceivingOwner.MessageType = 'IFTMBP')

And you further more have this criteria:

Parcel.allinstances->select(p|p.Messages->exists(m|m.MessageType = 'IFTMBF')).Messages

If all these Sets have the same result type (collection of ScaniaEdiSolMessage) you can simply intersect them to get your desired result

ScaniaEdiSolMessage.allinstances->select(shipFromFinland and not invalidated)
->intersection(ScaniaEdiSolMessage.allinstances->select(m|m.ReceivingOwner.MessageType = 'IFTMBP'))
->intersection(Parcel.allinstances->select(p|p.Messages->exists(m|m.MessageType = 'IFTMBF')).Messages
    )

And looking at that we can reduce it a bit to:

    ScaniaEdiSolMessage.allinstances
    ->select(m|m.shipFromFinland and (not m.invalidated) and
              (m.ReceivingOwner.MessageType = 'IFTMBP'))
    ->intersection(Parcel.allinstances->select(p|
             p.Messages->exists(m|m.MessageType = 'IFTMBF')).Messages
        )

Answer 2

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 :)

Answer 3

There is a project, Dresden OCL, that might help you.

Dresden OCL provides a set of tools to parse and evaluate OCL constraints on various models like UML, EMF and Java. Furthermore Dresden OCL provides tools for Java/AspectJ and SQL code generation. The tools of Dresden OCL can be either used as a library for other project or as a plug-in project that extends Eclipse with OCL support.

I haven't used it, but there is a demo showing how the tool generates SQL from a model and OCL constraints. I realize you're asking for the opposite, but maybe using this you can figure it out. There is also a paper that describes OCL->SQL transformations by the same people.