Question

Hi All!

I've tried to find any topic connected to DB-side stored procedures, but I didn't find one... so I open it!

I feel (and know), that supporting DB-side stored procedures (SPs) is against the conceptual/architectural model of DO4 from some point of view, but SPs are very efficient solutions for many tasks, so it can be a mistake to miss out them at all.

I know, writing SPs in the language of the selected database breaks the database independence, and introducts many point of possible bugs ( lack of static type checking, it does not tracks the evolution of our beautiful domain model, etc... many more thing exists, the main problem is, that SPs are outside of our great developing model (OOP, .NET, ...) and tools (VS, ...) ) I know, modifying DB in SP can break entity caching for instance. And I think, many other things...

On the other hand, SPs are the fastest way of processing medium and big amount of data, anyway, we knows it all.
(no data traveling -> no cost of it, no cost of converting between DB and .NET datatypes, etc...) There are selectable stored procedures in some DBs (Firebird, MSSql), we can express very complex queries (which can't be expressed as database view or any LINQ query) with these, and can use them like views.

The question is, that how many concepts can be injured in DO4 architecture by allowing execution of SPs, and are there any simple way to solve these problems?

A way can be to extend the SQLDOM model with basic stored procedure language elements, and let DO4 to create the stored procedures, it is a labor-intensive, but a relative good solution (although it breaks entity caching...)

Or is it important for DO to be a very clear, pure and safe ORM framework, anyway? Or is it worth to make a compromise?

What is your opinion?

Cs.

Updated at 21.10.2009 9:25:03

Hi Alex!

It's good to see, that supporting SPs (and views) isn't a conceptually impossible thing! I thought, that it can be, because we lost the full control (what .NET and VS gives) over our domain model. I thought, that it can cause performance penalty on the middle tier (of an 3(n)-tier app) to keep the shared entity cache (maybe a distributed one, like Velocity) through persisting everything/invalidate the cache... Isn't it too costly?

Extending SQLDOM is quite a good idea for handling views. There are two approach. One is, that views should be managed on DO side (so DO does creating/modifying of views without manual SQL intervention by developer), to keep model clean, pure and robust. Other is, that DO allows to define manually created DB views like some kind of entities (maybe connect its fields to real entity fields), and developer has the responsibility to keep the views in the DB in consistent state. I think, first approach is better, but it's a question for me, that can we express every SQL select in .NET way (eg. LINQ)? I don't know it. :( Second approach gives more freedom for developer, so he/she can write highly optimized queries, it can be a real benefit, if it's possible, that developer can write more optimized queries, than DO4 query builder can. But if DO4 is smart enough, there is no need for manual optimization.

Extending SQLDOM with something like PSQLDOM could be a big advance in many ways... imagine, that a C# method (a limited one, of course...) can be translated to stored procedure, and if the current database backend and it's provider supports SPs, it will run as stored procedure in the DB. If there is no support, it runs as simple C# method... Or an other way can be to build an expression tree (.NET 4.0 allows it truly), and compile it to stored procedure/.NET method, it depends on SP support in DB and provider... It can solve the problem of DO's own provider...
So it would be a great, very flexible and innovative solution.

I can't estimate how much time it might take to get it working... I should know the inside of DO much better... these are just ideas... kind of brainstorming...

DO is a quite mature framework... Didn't came up the need of views/SPs in the past?

What do you think? And what does anyone think, who uses DO in the world?

Cs.

Updated at 21.10.2009 13:58:29

Well, it seems that views are mostly replaceable with Queryable-s when building a new system, I can agree with it. Using DO with existing systems/databases is not the target of the DO development, and it is quite acceptable. So there is no real need to implement any additional support for them, there are more important thing to do :)

Let's see SPs... I agree, that compile .NET methods or expression trees to SP-language is a hard way... there are many problems with it. So it's a "wrong" way for today. But is there any easier way? Just allow the developer to execute SPs or native sql statements through an SqlCommand-like interface? Some other ORMs provides this way. Others generate .NET methods (at design-time) for executing SPs. DO naturally doesn't generate any code, and personally I like this approach very much, so i think it isn't a good idea to generate .NET methods. Are there other ways? And which can be a good way?

Anyway, do you feel that supporting SPs means enough improvement for DO to work on it for a few days?

Cs.

Updated at 21.10.2009 22:00:17

Ok, I try to understand your examples... :) I will create a real-like business application using DO4 on the weekend, it will helps me a lot... :)

On the other hand, is this logic applicable for those type of SPs, which implements real business logic. They don't give back any kind of DTOs (ok, maybe some errorcode/error message/... which can be treated as DTO). They are also important ones... So they will be implemented in the same way?

I come from a world, where databases contains massive business logic codes (Oracle PL/SQL, Firebird PSQL)... I spend years on working on this way. But nowadays I prefer using .NET and everything the framework gives us to be more effective and fail-safe in development, so I would like to develop everything as possible using .NET fw, but I've never wrote real business logic in .NET so far, just very simple ones... I try to imagine an application with no db-side code (no triggers, no sps, no views, etc..), it's a conceptual turn in my mind... I will try to implement one ASAP, of course with DO4, start it on the weekend... :)

Cs.

This thread was imported from our support forum. The original discussion may contain more detailed answer. Original topic by tao99.

Answer 1

Actually it's not really difficult to add support for SPs and views:

We must persist everything before SP execution (the same happens for any query)
If SP execution has side effects in the database (this must be explicitly declared somewhere), we can simply get rid of any cached info (EntityStates) the same way as we do while crossing transaction boundaries
There is no problems with mapping views or SP execution results to untraceable entities (Key & custom types that aren't Entity descendants). We already can materialize such types in LINQ (moreover, we truly map them). See e.g. SelectNestedDtoTest here. Note that we not just materialize custom DTO via LINQ, but allow using its mapped properties further.
It's really possible to make SQL DOM to "understand" SPs. Moreover, this isn't quite complex.

> The question is, that how many concepts can be injured in DO4 architecture by allowing execution of SPs, and are there any simple way to solve these problems? Or is it important for DO to be a very clear, pure and safe ORM framework, anyway? Or is it worth to make a compromise?

So the answer is: this won't break any concepts there, and there is no need for compromises. We must decide how public API of this part must look like (i.e. how we declare SP in code, how we map it, execute it and so on), and further estimate how much time it might take to get this working (~ one man-month is my preliminary guess; likely, it will be possible to add mapped views as well).

What's completely unclear is how our own providers must deal with this stuff. SP can be mapped to remote method call, but I never planned to add views there. On the other hand, getting exception on attempt to use such features with our own providers is ok as well - you may catch it and fall back to native (for DO4) code.

> I thought, that it can cause performance penalty on the middle tier (of an 3(n)-tier app) to keep the shared entity cache (maybe a distributed one, like Velocity) through persisting everything/invalidate the cache... Isn't it too costly?

All depends on your strategy. E.g. in many cases it's ok to fetch stale data (we'll anyway check versions of any data fetched from cache before updates).

Our upcoming cache API will allow to utilize cache dependencies - in fact, you'll be able to say "cache this query result, but invalidate it on update of any entity it contains", but a decision of dependencies utilization is fully upon you.

> One is, that views should be managed on DO side (so DO does creating/modifying of views without manual SQL intervention by developer), to keep model clean, pure and robust.

I'd prefer to avoid this... LINQ makes views almost useless (just cache the Queryable ;) ). Views with computed columns can be replaced by additional types (that must be updated e.g. by events).

On the other hand, if the goal is to support already existing views (to keep the compatibility level higher), this is good point. But I think this isn't something we must implement ASAP.

> Or an other way can be to build an expression tree (.NET 4.0 allows it truly), and compile it to stored procedure/.NET method, it depends on SP support in DB and provider... It can solve the problem of DO's own provider...

The same opinion ;)

Btw, it's quite difficult to implement a translation of even simple C# program to SQL. Just few reasons:

SQL does not have GC analogue; there are no reference type analogue at all. So it's not clear how to build even an analogue of .NET type from the point of data there.
Even if above would be possible, it will be a hell: we must provide analogues of e.g. such types as Entity and Session there. In SQL ;) Just imagine this. Even if we'd be able to access their models in Expressions, this would be almost impossible: they're dependent on hundreds of .NET types, that must be "translated" as well.

> DO is a quite mature framework... Didn't came up the need of views/SPs in the past?

Well, we never supported this. DO v1.X...3.X were much more distant from compatibility with legacy databases.

> And what does anyone think, who uses DO in the world?

I'm not sure if there were any real-life usages of SPs with DO at all before. At least, I don't remember anything similar. Try to search this forum for "stored procedures" - there must be some topics.

We can e.g. automatically map such SessionBound methods to SPs. Something like this:

[StoredProcedureMapping("sp_ GetSomeDtos")]
public IEnumerable<SomeDto> GetSomeDtos(string name, int age)
{
  // Will be reimplemented by PostSharp
  throw new NotImplementedException();
}

> Anyway, do you feel that supporting SPs means enough improvement for DO to work on it for a few days?

I think it will take ~ 1 - 1.5 weeks in case such guy as Alexey Gamzov (he added support for local collections - there are actually similar DTO object<->row transforms) will do this. But we don't plan to spend time on this ASAP.

Btw, it's possible to even support returning Queryable<t> from such methods. For us such results are similar to local collections. So e.g. it could be possible to run queries like this:

from someDto in Session.GetService<MyStoredProcs>().GetSomeDtos()
where ...
select ...

"Run" means "run fully on server". That could be quite attractive for TVFs.

Much nicer real-life example:

from fullTextMatch in Session.GetService<FullTextSearch>().Search("Alex")
from document in Query<Document>.All
where document.Id==fullTextMatch.DocumentId
orderby fullTextMatch.Rank descending
select new {document, fullTextMatch.Rank}

So in fact, by this way we can implement e.g. full-text search abstraction layer :)

Updated at 21.10.2009 9:25:03

Updated at 21.10.2009 13:58:29

Updated at 21.10.2009 22:00:17

Subscription:

Related questions