Pagination in Dataphor

First I created this operator:

create operator GetNext(var AID :Integer) : System.Integer 
begin 
AID:= AID+1; 
result:=AID; 
end ;

and then I used it:

var id := 0; 
select SomeTable add {GetNext(id) rowid}; 

It is nice, but it would be better if there were a way to do this without having to create an operator myself (it should already include one that does this)

Now If I want to do pagination, all I have to do is write:

var id := 0; 
select SomeTable add {GetNext(id) rowid} where rowid between 2 and 5;

That will return rows between 2 and 5 from the “SomeTable” table.

Now one of the most interesting feaures about Dataphor, are relvars (relational variables), that make it possible to do this:

var SomeComplexQuery := SomeTable where …...
var id := 0;
select SomeComplexQuery add {GetNext(id) rowid} where rowid between 2 and 5;

That way, pagination can be added to a query of arbitrary complexity with minimum effort.

Okey, things I miss about NHibernate

Lately I have been working without a formal object relational mapping (you know, using custom "hand made" business objects, and fetching data from the database using stored procedures)

Here are the things I miss from NHibernate:

• Automatic caching of objects by primary key
• Easy support for primary keys with multiple fields
• Polymorphic Queries (and all the benefits of persistent inheritance)
• Queries with "." navigation "object.Relationship.Relationship.Field = :parameter"
• Prefecthing relationships: "load Products with their to one relationship with Vendor preloaded"
• Easy pagination (instead of trying to figure out how to do it in combination with stored procedures for a particular version of a particular database (that can be a real nightmare))
• Interactive single and multicolumn sorting (try to do it with stored procedures, and of course without cheating by using the execute command, remember if you use execute all the so called "security benefits" of stored procedures vs dynamic sql dissapear)
• Concurrency support (automatical increment of a "version" field when saving changes, even if those changes are not just field changes, but relationship changes, custom exception type for concurrency errors, optimistic locking)
  
• Query by example (no need to concatenate strings by hand based on whether a field is null, no need to worry about upper case or lower case comparision when comparing each field, no need to update a query definition if a column is added or dropped from a table)

And, if somehow you have to work with a database that doesn't have support for stored procedures (and you can't use NHibernate)... things get even more "interesting":

• Alias for ugly table names and ugly field names (instead of "select * from PDT where PCKT = 1" you write "select * from Products where PackageType = 1"
• Named Parameters: Instead of "select * from PDT where PCKT = ? and RQTY = ?" you can write: "select * from Products where PackageType= :packageType and RemainingQuantity = :remainingQuantity" this is specially a nightmare when one has to use OleDb, and it gets worse with Inserts or Updates: "INSERT INTO PDT (NN,PCKT,RQTY,DRP,PRNBR) VALUES (?,?,?,?,?)" instead of the simple: "session.save(product)"

I will update this list as it increases...

OMG: Object Orientation and Stored Procedures

OMG! how many times have I read the argument "using stored procedures is object oriented programming" just think of the database as an object, and think that each stored procedure is an method of the database....
Now... I am not going to say that using stored procedures is wrong... and I am not going to say that the only correct way of solving software problems is using object orientation... ( I do prefer to use object orientation, I a do prefer to use an object relational mapper instead of a stored procedures, specially for OLTP applications, I think application built that way are more maintainable, more portable between database, can be built faster, scale better, etc,etc)

But for batch processing, stored procedures have a clear advantage, and in my experience many business problems are solved by using an OLTP application, and a short but very important list of batch process... (of course the problem here is that if you built you application using an ORM, probably you have a lot of you business logic already implemented in a presentation-independent form, and, depending on the size of you database, you could program you batch process using your business objects... that has its disadvantages (slower performance that stored procedures) and its advantages (reutilization)... I believe reutilization and maintenance are more important that performance... specially because it is possible to worry about performance in the wrong way: for example Premature Optimization )

But the main point of this post, is that, first, I didn't think that using stored procedures was object oriented programming... it was "procedural programming"... or perhaps even "relational programming" but certainly not object oriented programming (where is the inheritance? where is the polymorphism?) but... after giving it some more thought... I realized that while it could not be "object oriented" it could be "object based"... stored procedures can encapsulate your tables (one of the major advantages of stored procedures) and you can see them as methods to a your "database" object...

So... now... your (OLTP?) system is a thin presentation layer on top of the "database object"... now... is there something in favor... or against that in object oriented design theory?.... mmm.... it achieves Presentation/Business Logic separation... and that is good... but I still feel there is something that doesn't feel right...

OMG! Well, it turns out there is! : Now the database is a God Object.
From Wikipedia: A God object is an object that knows too much or does too much. The God object is an example of an anti-pattern, it goes against divide & conquer ... using a God object is analogue of failing to use subroutines in procedural programming languages, because so much code references it, it makes maintenance difficult...

So... the next time someone tells you that using stored procedures is good object oriented programming... you could answer him or her: Oh My God (Object)! (You could think of it as object based, but, form an object oriented point if view.. it is a recommended solution? couldn't it be seen as an anti-pattern? I am not saying it is wrong, I am just saying that from an object oriented point of view, it might not be the best way to solve the problem... of course, using object orientation might not be the right way to solve this particular problem, after all, object orientation is not a Silver Bullet)