PSExpression , or how to do things in the DB from MDriven

One usual backend for MDriven systems is the relational database. We use OCL (object constraints language) and databases use SQL. We do objects but databases do data. We bridge the gap with object-relational-mapping strategies or OR-mapping for short.

We allow for you – the developer – to express your query in OCL (or Linq if you are in code) and we translate this to SQL that fetches the data that builds up the objects.

Normally OCL expressions work with lazy fetch – whenever you access relations that are not yet fetched from db, MDriven fetch them – silently – possibly in the background in the AsyncThread if you use the IAsyncSupportService to always have an accessible UI (recommended for WPF, WinForms and fat client strategies).

But it is also possible to use the OCL-PS (PS for persistence storage, i.e. the database in this case). When doing so the navigation of links do not lazy fetch data – instead the expression is interpreted and translated to SQL. SQL sent to the persistence storage for execution. This execution returns the identities for the objects in the result of the expression. MDriven then use the standard fetch operations in those identities to resolve them to objects.

This is how the multilevel search logic works in MDriven ViewModels (described here).

For a couple of different reasons – all to be made apparent in this article – we have chosen to further enhance the way the OCLPs service can be used in ViewModels.

PSExpressions are typically used when the amount of objects seen by the expression is large – like maybe more than a few 100 or 1000. But PSExpressions may also be used when you – the developer – want to do things that are easy in SQL but hard or unwise in OCL – like finding 1 object in a table of millions – or performing sum,count,max,min or average operations on vast amounts of data in your database.

This article is on how to do this from inside the MDriven framework (you can always access your own database – this goes without saying).

The news

1. The OclPsService has a method called GetValue – this can be used to return data from the database and not merely object keys as the Execute method does.

2. To facilitate exposure of database functions a new Ocl operator has been introduced SqlPassthrough(somesqlasastring,type1ofdataback,type2ofdataback,etc)

3. The OCLPSHandle will get the return type of the expression and choose either Execute or GetValue to do the right thing

4. The ViewModel columns has a new property called IsPSExpression – this will be true if a column starts with the name “PSExpression_”

5. The ViewModel variable selfVM that we described here a few weeks ago has been extended with 2 new methods ExecutePS and PSExpression_Refresh. The ExecutePS(viewmodelclass,viewmodelcolumn) will take arguments to a viewmodel column and execute the expression found there with OclPS service. It will return the list – wether it is tuples of data or objects. The PSExpression_Refresh() will find all IsPSExpression columns and execute their expression in OclPs.

6. The ViewModelWPFUserControl and VMClass has been extended to fulfill these new obligations.

Having these things in place makes it possible to do things like this:

image

In the image above the action at #1 calls the selfvm.PSExpression_Refresh(), that will make the two PSExpressions evaluate in the database. Since the expression at #2 contains a sqlpassthrough operator the provided sql will exeute in the DB. This result is expected to be two integers. These two integers are displayed by the #3 viewmodel class.

The result is shown in the executing application – in the grid named “Thing” we see the sum and max of all attribute2 in the class1 that are bigger than vNewVar that we have assigned the value 3 in this case.

Having done this without the need for instantiating any of the involved Class1 objects is a good thing.

Having made assumptions on how your database actually looks is a bad thing – but the tradeoff is acceptable whenever you get large volumes in your MDriven systems.

This entry was posted in OCL, Tuples, ViewModel and tagged , , , , , , . Bookmark the permalink.

2 Responses to PSExpression , or how to do things in the DB from MDriven

  1. Peter Buchmann says:

    Oh whow, finally. You might know, that we users are now very interested to understand how this cool new feature can be used:

    1) Can it only be used in view models or will there be a API for OclPsService or PSQuery?
    2) It seems that this method can call some pseudo SQL still containing classes. Does this SQL contain the attribute names or the database column names?
    3) Are derived attributs possible or only persistent attributes?
    4) What can be done with this pseudo SQL? Is it actually a pseudo SQL or will class names simply be replaced with the OR-mapped SQL joins?
    5) Are groupings possible?

    These questions should do for now and I’m excited to hear more.

  2. Admin says:

    This functionality is available in OclPS.
    Sqlpassthrough takes plain sql – since plain sql will return data it is only really usable with OclPS.GetValue.

    Plain SQL means that it will be database flavor dependent – negative – but it can do anything your db supports – positive.

    Derive attributes does not weigh in to this except the Expand derivation expressions.

Leave a Reply

Your email address will not be published. Required fields are marked *

*