Translate SQL to OCL?

Question

I have a part of SQL that I want to translate to OCL. I am not very good at SQL, so I want to increase support. We use Interbase 2009, Delphi 2007 with Bold and modeldriven. Now I hope someone here both speaks good SQL and OCL :-) 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 little 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 specified is ScaniaEdiSolMessage. It has attributes such as:

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

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

, , ScaniaEdiSolMessage, , ScaniaEdiSolMessage EdiSolMsg. . , 28000 .

OCL :

ScaniaEdiSolMessage.allinstances

, , :

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

, OCL, SQL .

Answer 1

, SQL.

, , , SQL, , , OCL.

OCL, , , OCL, . , , - SQL, .

, :)

Answer 2

Dresden OCL, .

Dresden OCL OCL , UML, EMF Java. , Dresden OCL Java/AspectJ SQL. Dresden OCL , , Eclipse OCL.

, , SQL OCL. , , , , , . paper, OCL- > SQL .

Answer 3

MDriven ( Bold Delphi) :

OCL SQL , , , ocl → , , .

So, in your case, you might have this set:

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

but you also have this set:

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

And you still have the following criteria:

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

If all these sets have the same type of result (ScaniaEdiSolMessage collection), you can simply cross them to get the 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 it, we can reduce it a little:

    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
        )