Stored Procedure Support

We asked a while back about stored procedure support for CRUD operations. Our management is pushing us to use stored procedures for our CRUD operations. It was our impression that support should be coming soon. I wanted to make sure that was on track. Can we get an update on when it may be available in a nightly build?

This is something we have added on to the work list for Version 3 (the next version) of LightSpeed, the current plan is to have that released around mid year, however we will likely be pushing out some beta builds prior to that which are just nightlies of our branch - so if you are interested we can let you know once we have committed that functionality into the branch.

Jeremy

We would love to get a version as earlier as possible. It's the only thing holding us back from management buy-in at this point, I think. I think the biggest issue is going to be timing. We are working on architecture at this point and plan to move into development within the next few weeks. What do you think would be the complexity of working with the current version as-is and then migrating to stored procedure CRUD when you get a working version? If we can tell them the migration won't be a big deal that may help.

I have implemented first-cut support for this but need to straighten out the API a bit and test it on a broader range of databases before I commit it.  With luck we will have something for you to try out later this week (though it will probably have some bugs in it!).

At the moment it looks like migration should be fairly simple for supported scenarios.  The current design just calls for you to add an attribute to the entity naming Select, Insert, Update, Delete and "select by ID" procedures, and to each one-to-many association (EntityCollection) naming a "select by XxxId" procedure (e.g. SelectOrdersByCustomerId).  You should not need to change your entity definitions in any other way. One thing I'd like to do is make this even simpler so that you can just supply the attribute, and LightSpeed will infer procedure names (e.g. if the class is called Order and you don't specify procedure names then LightSpeed would infer SelectOrders, SelectOrderById, InsertOrder, etc.); if we can do this then it will minimise the migration effort still further.

The procedures will need to conform to predefined signatures.  For example, the Select procedure must take no arguments, and the SelectById procedure must take exactly one argument called Id.  We do not expect to be able to map to arbitrary CRUD procedures.  It sounds like you are free to define your own sprocs so I assume this will not be an issue for you.  Also at present it looks like optimistic concurrency checking will be the responsibility of the Update procedure (we will not do it for you because we have no way to attach a LockVersion WHERE clause check to the update statement), and cascade deletion will be the responsibility of the Delete procedure (again, we can't append our normal cascade-delete statements).

Finally, the use of procedures will result in behavioural differences.  Among the ones I've noted so far are that there will be no eager loading of associations on select (as with the Find(ProcedureQuery) API) and that there will be no batching of inserts / updates / deletes.  Plus, needless to say, you will not be able to perform arbitrary queries using the Query class or LINQ, though you will still be able to do pre-defined queries using Find(ProcedureQuery) or designer-generated wrappers.

That's great news. We look forward to giving it a whirl. We can definitely provide you with good feedback as we start working with it.

Also, I'm not sure if it matters, but we are using Oracle packages. I'm hoping that we can have multiple procedures inside a package, so we can segregate procedures logically. For example, one package could have procedures related to inventory and another package could have procedures related to ordering information.

Let us know when there's an update.... and thanks again for all the hard work!

I have committed a first cut of this and it will be included in nightly builds dated 11 Mar 2009 and above, available from about 1430 GMT.  We look forward to hearing your feedback -- obviously this is as a new feature you will probably run into a few bugs and we appreciate your help in shaking these out!

Oracle packages should not be an issue -- I have not tested these explicitly but from other work we have done you should just need to qualify the procedure names with the package name e.g. "MyPackage.SelectOrders".  Let us know if this doesn't work for you.

How to use it:

* Apply the CrudProcedure attribute to each entity.  CrudProcedure takes the following five parameters (at present you must specify all of these explicitly, though as previously mentioned we hope to support inferred names in future):

Select: the name of the procedure which selects all entities of the given type.  This should have only one parameter, an out-parameter named results of type ref cursor, and should select everything into that ref cursor.  (The name "results" is mandatory: this is a LightSpeed convention.)  This is used when you do a Find<T>().

SelectById: the name of the procedure which selects a single entity by ID.  This should have two parameters, an in-parameter named id and an out-parameter of type ref cursor named results.  This is used when you do a FindOne<T>, or when LightSpeed traverses a many-to-one (backreference) association.

Insert: the name of the procedure which inserts a new entity.  This should have an in-parameter for each (persistent) property of the entity, named the same as the property.  If you are using the KeyTable or Sequence identity method, this includes the Id property.  If you are using the IdentityColumn identity method, this *excludes* the Id property, and must return the new entity's ID.

Update: the name of the procedure which updates an existing entity.  This should have an in-parameter for each (persistent) property of the entity, including Id.

Delete: the name of the procedure which deletes an existing entity.  This should have one in-parameter named Id.

Here is an example of how an entity might be marked up:

[CrudProcedure(
  Select="SelectPenguins",
  SelectById="SelectPenguin",
  Insert="CreatePenguin",
  Update="UpdatePenguin",
  Delete="DeletePenguin")]
public class Penguin : Entity<int>
{
  public string Name { ... }
  public string Species { ... }
  public int Age { ... }
}

And the procedures might look something like this:

-- Backing table definition:
-- CREATE TABLE Penguins
-- (
--   Id         INT           NOT NULL PRIMARY KEY,
--   Name       VARCHAR2(20)  NOT NULL,
--   Species    VARCHAR2(20)  NOT NULL,
--   Age        INT           NOT NULL
-- );

CREATE PROCEDURE SelectPenguins
(
  results OUT SYS_REFCURSOR
) IS
BEGIN
  OPEN results FOR SELECT * FROM Penguins;
END;
/

CREATE PROCEDURE SelectPenguin
(
  id INT,
  results OUT SYS_REFCURSOR
) IS
BEGIN
  OPEN results FOR SELECT * FROM Penguins WHERE Penguins.Id = SelectPenguin.id;
END;
/

CREATE PROCEDURE CreatePenguin
(
  id INT,
  species VARCHAR2,
  name VARCHAR2,
  age INT
)
AS
BEGIN
  INSERT INTO Penguins(Id, Species, Name, Age) VALUES (id, species, name, age);
END;
/

CREATE PROCEDURE UpdatePenguin
(
  id INT,
  species VARCHAR2,
  name VARCHAR2,
  age INT
)
AS
BEGIN
  UPDATE Penguins SET
    Species = UpdatePenguin.species,
    Name = UpdatePenguin.name,
    Age = UpdatePenguin.age
  WHERE Penguins.Id = UpdatePenguin.id;
END;
/

CREATE PROCEDURE DeletePenguin
(
  id INT
)
AS
BEGIN
  DELETE FROM Penguins WHERE Penguins.Id = DeletePenguin.id;
END;
/

(I have used SELECT * as a shorthand.  It's usually better practice to spell out the selected columns by name.)

* Apply the CrudProcedure attribute to each EntityCollection field (note field, not property).  In this case, you need to specify only the Select parameter.  This should be the name of the procedure which selects members of the collection using the foreign key.  For example, if the attribute is applying to the Customer._orders collection, then this might be called SelectOrdersByCustomerId.

The procedure should take one in-parameter named for the foreign key, and one out-parameter of type ref cursor named results.  E.g.

[CrudProcedure(Select = "SelectOrdersByCustomerId")]
private readonly EntityCollection<Order> _orders = new EntityCollection<Order>();

CREATE PROCEDURE SelectOrdersByCustomerId
(
  customerId INT,
  results OUT SYS_REFCURSOR
) IS
BEGIN
  OPEN results FOR SELECT * FROM Orders WHERE Orders.CustomerId = SelectOrdersByCustomerId.customerId;
END;

Some notes:

* You can override parameter naming conventions by applying ColumnAttribute to the fields.

* You cannot combine a Select stored procedure with a query expression, an "order by" clause, paging, etc.  At the moment these will be silently ignored and LightSpeed will return whatever the sproc returns in whatever order it returns it.  In future these will probably cause an error.  Instead, build ordering logic into the sproc, and if you require custom queries, create them as sprocs and use the Find<T>(ProcedureQuery) method.

* This feature has been tested only with Oracle and with SQL Server.  (I know you are on Oracle but just in case anybody else has read this far!)  MySQL and PostgreSQL will follow.

* There is currently no designer support for CrudProcedure markup.  For entity-level attributes you can apply these via a partial class.  For EntityCollections, you will need to exclude the association from code generation and write the required fields and properties by hand in the partial class.  We will be adding designer support, so this should be only a temporary pain.

Finally, once again please note that this is a first-cut implementation for evaluation and testing.  We appreciate your patience with any bugs or limitations and we welcome any feedback or suggestions!

I just wanted to drop you a quick update. We've started looking at the crud support and have been able to implement your sample code. We'll be trying to incorporate it into some of our own logic shortly and will give you more feedback once we have a chance to do that. Thanks again!

I'm having some trouble loading child collections. Here's my scenario. I added a column to the Penguins table called PARENT and it's a nullable integer. I prepopulated some data. I am now going to have a hierarchical relationships, meaning a penguin can have child penguins.

I created the following procedure:

 Here is my class code that I think should work:

[Table("PENGUINS", IdColumnName = "ID")]

Select="SelectPenguins",

Insert="CreatePenguin",

Delete="DeletePenguin")]

[Column("NAME")]

[Column("SPECIES")]

[Column("AGE")]

[Column("PARENT")]

private int? _parentPenguinId;

[ReverseAssociation("ChildPenguins")]

private readonly EntityCollection<Penguin> _childPenguins = new EntityCollection<Penguin>();

private readonly EntityHolder<Penguin> _parentPenguin = new EntityHolder<Penguin>();

get { return Get(ref _parentPenguinId); }

get{return Get(ref _species);}

get { return Get(_parentPenguin); }

I end up getting the following exception:

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SELECTCHILDPENGUINS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I must not have it setup properly to pass the parent id to Select ChildPenguins.

Thoughts?

There are a couple of things I can see might cause a problem here:

* Naming -- I think the childId sproc parameter should probably be named parent.  LightSpeed will be using the "column" name as the name of the parameter, which in your case is Parent rather than ChildId.

* Nullability -- if the parent ID in the entity is null, then LightSpeed will be passing NULL as the first parameter -- I am not sure whether Oracle requires a nullability declaration on the sproc parameter?

If you attach a logger and have a look at the generated SQL then this may clarify the problem.  You can do this by setting LightSpeedContext.Logger = new TraceLogger() or new ConsoleLogger().  (You may also need to set LightSpeedContext.VerboseLogging to true.)  If it's still not clear what the problem is then please post the generated SQL and we will take a look.  Thanks!

So, here's a quick update. I've managed to get the parent/child thing figured out for the most part. I am having on problem still, though.

 Here's my test case.

Repository.Context.Logger.LogSql(typeof(Penguin));

penguin.Species = "Penguin Species 1";

This was a bug in our implementation.  I have committed a fix and it will be available in nightly builds dated 18 Mar 2009 and above, available from about 1430 GMT.

This should also remove the need to specify a default null value on the sproc parameter as we will now explicitly pass a NULL value.

Thanks for letting us know about this, and please let us know if you still see a problem.

It looks like it is not treating the column with the proper data type when you force a null. Here's trace. Notice that PARENT is set as a string in the trace although in the class definition and sproc definition it's an int.

Darden.Foundation.ApplicationSecurity.Model.Penguin
SELECT LightSpeed.NEXTVAL FROM dual

--> Time:   56 ms
--> Result: 1059

CreatePenguin

--> Parameter  DbType      Value
--> ---------  ----------  -----
--> ID         Int32       1049
--> AGE        Int32       99
--> NAME       String      Penguin Test 1
--> PARENT     String      (null)
--> SPECIES    String      Penguin Species 1

Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
Mindscape.LightSpeed.UnitOfWork.SaveChanges(Boolean reset)
Mindscape.LightSpeed.UnitOfWorkBase.SaveChanges()
TestModel.UnitTestPenguin.CreateNewPenguin() in C:\....\TestModel\UnitTestPenguin.cs: line 64

 Test method TestModel.UnitTestPenguin.CreateNewPenguin threw exception:  Oracle.DataAccess.Client.OracleException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CREATEPENGUIN'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored.

That's odd -- I can see the same behaviour of it reporting string rather than int for the actual parameter type, but in my tests that doesn't seem to cause a problem (I've tried it on Oracle 9 and Oracle 11 XE) -- the INT sproc parameter is happy to accept a NULL even though the client has tagged it as a string.

I'm therefore wondering if the actual error is something different, possibly a mismatch in the names?  Could you post the CREATE PROCEDURE SQL for CreatePenguin please, and I will see if I can reproduce the problem here?  Thanks!

I just wanted to give you an update on our working with the Stored Procedure CRUD support. Things are working pretty well since we've figured out the right way to configure the procedures and associations. We need to pull down a new build, since we haven't done that in a while now.

I'm not quite sure of the technical limitations, but do you think there is any possible way that eager loading of associations on select would be possible? I'm guessing that the limitation is not being able to batch multiple stored procedure calls on one call. Batching of inserts / updates / deletes would be a nice to have, but isn't that big of a deal if it can't be done.

Thanks for the update.  Unfortunately I think eager loading on select would be quite tough to do.  Not only would we need to batch the stored procedure calls, which I think would mean manually generating dynamic execute statements instead of using the provider's built-in stored procedure support, we would also need to define some sort of convention for the procedure(s) to be called to retrieve the descendant entities.

The latter is the real showstopper.  Although it is feasible in a single-level Find (select all) or FindOne (select one) scenario, it gets tricky when you have multiple levels of eager load.  Suppose we have an eager load graph from A to B to C.  Loading a single A loads all the Bs associated with that A, which is just the SelectBsByAId procedure, but then we need to load all the Cs associated with *any* of the Bs.  This requires either calling the SelectCsByBId procedure in a loop, or having a way to specify additional procedures for multi-level ancestry queries, e.g. SelectCsByBsAId (and SelectDsByCsBsAId!), or defining a SelectCsByBIdsMultiple procedure which accepted multiple B Ids and did an IN query (select ... from C where C.BId in ...) and passing the set of B IDs to that within the batch.  We would of course be open to suggestions if you can see a simple way of handling this scenario -- we may be overlooking something obvious here!

By the way, we've just committed designer support for CRUD procedures.  It will be in nightlies dated 27 Mar 2009 and above.  Please let us know if you run into any problems or have any feedback.

To specify CRUD procedures on an entity, select the entity and set its Access Method to StoredProcedures.  Five additional properties will appear -- Select Procedure, Select By Id Procedure, etc. -- where you can enter the procedure names.  (We don't have a way of inferring these at the moment, which I realise could make for some tedious typing.  We'd be interested to hear whether guessing names using a naming convention would be useful to you, or any other suggestions you have for making this more convenient.)

To specify the select procedure for a one-to-many association, select the association arrow and set its Select Procedure to the name of the collection selection sproc (e.g. SelectChildrenByParentId).

One decision that we'd particularly welcome your feedback on is that entities declared as accessed by stored procedures are excluded from designer synchronisation.  The reason for this is that the schema for these entities depends on the sprocs, and we can't readily synchronise with sprocs.  On the other hand, if the procedures are purely conventional, just thin wrappers over a conventionally named table, then maybe you do want to be able to sync with a table.  What are your thoughts on this?