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.

You are doing some Scania trucks stuff?:) What you want to do seems not so good to me. First of all SQL is based on relational algebra and OCL on first order predicates in object oriented environment. This makes them both look at the world in a different way. E.g. simple navigation in SQL is about selecting and joining. In OCL you go through associations from one object to others. The object oriented and relational mismatch might cause you many problems in the future... — Gabriel Ščerbák, Jun 10 '10 at 20:28
Yes, there are some truck stuff in the application. Many SQL expressions is relative easy to translate to OCL even if the latter is object oriented (OCL can also filter things). I don't know what kind of mismatch problems you are thinking about, but I want as much as possible in OCL for simplicity. — Roland Bengtsson, Jun 11 '10 at 04:48
Why not learn at least enough SQL to maintain what you have. I agree with Gabriel. Or maybe find a database that speaks OCL instead of/in addition to SQL — Stephanie Page, Jan 05 '11 at 20:54

score 3 · Answer 1 · answered Jan 24 '11 at 13:16

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

Well that is just a matter of preference. We have the policy that use OCL whenever it is possible. Reason for OCL is simplicity and that it is a foundation in Bold that is out framework. But I agree that more people know SQL than OCL. — Roland Bengtsson, Dec 27 '17 at 13:48

score 1 · Answer 2 · edited Sep 15 '12 at 10:38

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.

score 0 · Answer 3 · answered Dec 27 '17 at 08:50

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
        )

allinstances is not an OCL facility; you probably mean allInstances() — Ed Willink, Dec 27 '17 at 10:08
Bold may not follow OCL standard strictly, so allinstances actually works fine. This question is more than 7 years old. A quick check in the code reveals that original SQL is the same :) — Roland Bengtsson, Dec 27 '17 at 13:17
@EdWillink - you are correct - in OCL parenthesis are mandatory even if method does not take arguments. In the MDriven implementation of OCL we do however allow for omitting them when there are no parameters. — Hans Karlsen, Dec 28 '17 at 09:49

Translate SQL to OCL?

3 Answers3