Home » Blog

Archive for October, 2008

Stored procedures and LightSpeed

Tagged as LightSpeed

We’ve had a lot of requests for stored procedure support in LightSpeed, and now that 2.1 is out the door we’ve started to work on this functionality. So we’re pleased to announce that the latest nightly builds now contain a first cut of support for stored procedures.

The main use of procedures in this drop is to load entities. This is accomplished by another overload of IUnitOfWork.Find. This overload takes a ProcedureQuery object, which specifies the name of the procedure and a set of parameters to pass to the query. (We plan to offer more convenient versions of this API once we have gathered some initial user feedback.) It returns a list of entities, just like the existing Find methods. For example:

// Simple stored procedure
IList<ContributionSummary> summaries = 
  _unitOfWork.Find<ContributionSummary>(new ProcedureQuery("SummariseContributions"));
 
// With parameters
ProcedureQuery query = new ProcedureQuery(
  "GetContributions", 
  new ProcedureParameter("maxid", 10));
IList<Contribution> contributions = _unitOfWork.Find<Contribution>(query);

The stored procedure is expected to perform a SELECT of the required entity data. On SQL Server, this is done in the familiar way:

-- Somewhat artificial example - this could have been done as a view
CREATE PROCEDURE SummariseContributions
AS
  SELECT
    Contributions.Id ContributionId,
    Contributions.Title ContributionName,
    Contributions.Description,
    Contributions.CoordinatesGeoX,
    Contributions.CoordinatesGeoY,
    Members.UserName ContributorName
  FROM
    Contributions
    INNER JOIN Members ON Contributions.ContributorId = Members.Id
  ORDER BY
    Contributions.Id

The procedure may of course return records directly from a table (e.g. SELECT * FROM Contributions), but as the example above demonstrates, it can also define its own schema provided that you also define an entity class to match that schema. (That entity class is a normal LightSpeed entity; you don’t need to do anything special in the entity class to support stored procedures.)

Like anything that wants to become a LightSpeed entity, the schema for the returned recordset must have an identity column, and the values in this column must be unique. If the identity column is not named “Id”, then the entity class must have a TableAttribute specifying the IdColumnName. Other attributes also apply as normal, just as if the entity were loading from a table:

[Table(IdColumnName="ContributionId")]
public class ContributionSummary : Entity<int>
{
  [Column("ContributionName")]
  private string _name;
 
  private string _description;
 
  [Column("ContributorName")]
  private string _contributedBy;
 
  [ValueObject]
  private Coordinates _coordinates;
}

One attribute that doesn’t work with stored procedures is EagerLoad. If an entity with associations is loaded through a ProcedureQuery, its associations are always lazily loaded (and no warning is given). This is not usually an issue because a stored procedure is usually designed to return a complete and self-contained set of data.

We also support stored procedures on Oracle, as long as you are using the Oracle9 provider (not the Oracle9Odp provider). However, because Oracle procedures don’t return recordsets directly in the way that SQL Server does, we require the Oracle procedure to follow a defined LightSpeed convention: namely, that the recordset be returned through an out parameter of ref cursor type, named “results”. (We welcome feedback on this convention.) In Oracle, the SummariseContributions procedure therefore looks like this:

CREATE PROCEDURE SummariseContributions
(
  results  OUT SYS_REFCURSOR
) IS
BEGIN
  OPEN results FOR
  SELECT
    Contributions.Id ContributionId,
    Contributions.Title ContributionName,
    Contributions.Description,
    Contributions.CoordinatesGeoX,
    Contributions.CoordinatesGeoY,
    Members.UserName ContributorName
  FROM
    Contributions
    INNER JOIN Members ON Contributions.ContributorId = Members.Id
  ORDER BY
    Contributions.Id;
END;

The “results” convention is required only at the database end of things. At the LightSpeed end, you still just call _unitOfWork.Find < ContributionSummary > (“SummariseContributions”) as if the “results” parameter didn’t exist.

Stored procedures can return additional data via out parameters. In this case you must specify the data type of the parameter and, for strings, the size. You must also specify whether the parameter is an out parameter, an in-out parameter or the return value.

CREATE PROCEDURE GetMostRecentComment
(
  @postedBy NVARCHAR(50) OUT,
  @subject NVARCHAR(128) OUT,
  @postedOn DATETIME OUT
)
AS
BEGIN
  SELECT TOP(1)
    @postedOn = Comments.PostedOn, 
    @subject = Comments.Subject, 
    @postedBy = Members.UserName
  FROM
    Comments
    INNER JOIN Members ON Comments.MemberId = Members.Id
  ORDER BY 
    Comments.PostedOn DESC, Members.UserName;
  SELECT TOP(1) * FROM Comments;
END

ProcedureParameter postedOn = new ProcedureParameter("postedOn", null, 
  ParameterDirection.Output, DbType.DateTime, null);
ProcedureParameter postedBy = new ProcedureParameter("postedBy", null, 
  ParameterDirection.Output, DbType.String, 50);
ProcedureParameter subject = new ProcedureParameter("subject", null, 
  ParameterDirection.Output, DbType.String, 128);
 
ProcedureQuery query = new ProcedureQuery("GetMostRecentComment", 
  postedBy, subject, postedOn);
_unitOfWork.Find<Comment>(query);
 
// out-parameters now available via postedOn.Value, postedBy.Value
// and subject.Value

We welcome feedback on how widely you use stored procedure out-parameters so that we can determine whether to put effort into streamlining this API.

We do not yet support stored procedures that do not return recordsets, except for ExecuteScalar-type (e.g. SELECT COUNT) procedures on SQL Server (accessed via IUnitOfWork.Calculate(ProcedureQuery)). We will soon be adding support for ExecuteNonQuery-type procedures on both SQL Server and Oracle, and looking at support for MySQL. Again, your feedback will help us prioritise this work.

Finally, what I’ve described here is the low-level raw API. We plan to add designer support for stored procedures, so that we can generate friendly wrapper functions — for example:

public class SampleUnitOfWork : UnitOfWork
{
  public IList<ContributionSummary> SummariseContributions() { ... }
  public IList<Contribution> GetContributions(int maxid) { ... }
  public IList<Comment> GetMostRecentComment(out DateTime postedOn, out string postedBy, out string subject) { ... }
}

We’re at an early stage with this work and the earlier you give us your feedback the easier it will be for us to adapt our designs and priorities. So download the latest nightly — Express Edition from the download site or retail editions from the store — and take it for a spin today!

Add your comments
Posted by Ivan Towlson on 20 October 2008

LightSpeed 2.1 Released!

Tagged as General, LightSpeed, Products

Here it is, LightSpeed 2.1 is fresh from the oven and ready for download!

A quick run-through of the changes:

Search Engine

Want full text search? Your database engine doesn’t support it? LightSpeed 2.1 is here to help.

Shipping with LightSpeed 2.1 is a full provider-model based full text search engine built on the fantastic Lucene.Net search engine. This means that the full text search is completely independent of your choice in database and has incredible performance.

Query query = new Query();
query.SearchQuery = "video";
 
using (var unitOfWork = LightSpeedContext.BeginUnitOfWork())
{
  unitOfWork.Find<Comment>(query);
}

More advanced search engine capabilities exist, for example, being able to search over multiple entity types at once, perform a query that matches between the search index and data only stored in your database (e.g. “find me all entities that have the word ‘car’ in them and who have an owner id of 99″).

We’re excited to see how users will make use of this functionality and appreciate any feedback on this new feature and your ability to perform Google-style queries with your object relational mapper.

The search provider infrastructure is extensible so if you don’t want to use Lucene.Net you are welcome to plug-in your own full text search provider.

VistaDB, Firebird and SQL Server Compact Support

Adding to the existing databases that LightSpeed supports, now you can work with VistaDB, Firebird and SQL Server Compact Edition.

Designer Improvements

We have been blown away by the feedback on our Visual Studio 2008 integration – much of it has been praise along the lines of “wow – a designer that doesn’t suck!” but with all the different databases and versions out there, some folks have run into issues with their particular databases. We’ve heard that feedback and been incorporating it throughout the life span of LightSpeed 2.0, with the result that the designer in 2.1 is faster, smarter and more robust, so that the out-of-the-box experience for developers is as hassle free as possible.

We’ve also rounded out designer support for the core LightSpeed feature set, and added the ability for projects to use their own custom templates for generating the entity code.

Delete and update queries

Previously when deleting data you were required to retrieve an entity and then delete it with a unit of work. Fetching data just to delete it went against our performance focused view of the world so we made things more efficient – simply pass in a query and delete all data matching that query. The old mechanism still exists for backward compatibility but if you’re eager to keep making your code as fast as possible, you now have more options.

var query = new Query(typeof(Contribution)) {Identifier = 1};
 
_unitOfWork.Remove(query);
_unitOfWork.SaveChanges(true);

There’s a similar feature for updates, which allows you to modify database data without having to download it into an entity.

Many more little tweaks

There have been many other small tweaks to improve the development experience that aren’t listed here. Click here to read the complete LightSpeed 2.1 change log.

What’s next?

We have plans for the next version of LightSpeed, including first class WCF support, stored procedure support and other, more secret, new features to help developers create better solutions faster. What is super helpful however is getting feedback on things you would like to see in the next version – they can be far out ideas or just little things that annoy you about building domain driven applications today. So, leave a comment, we’d love to hear your thoughts!

Add your comments
Posted by John-Daniel Trask on 15 October 2008

Vote for us…

Tagged as General

…and all your wildest dreams will come true! Or something to that effect :) It seems that it’s that time again where politics is dominating a lot of news time and as much as I’m sure we’re all tired of it already – I’d love for you to cast a vote, for Mindscape, in the New Zealand Startup awards.

We’re almost outside the age range (coming up 2 years old) but still qualify. If you’re a fan of our products, support or just want to help out, please visit the voting site: http://www.squidoo.com/nztop10startups08

We’re sitting around the number two spot currently, listed as www.mindscape.co.nz.

Thanks for your support :)

Add your comments
Posted by John-Daniel Trask on 13 October 2008