Since putting it on production equipment a few things aren't working quite right but it's passable for now. Tomorrow I'll clean up any loose ends and write the story of the port.

The URL rewrites to .aspx filenames are annoying me, but I might leave them to remind me of the old days. If I get motivated over the weekend I might set up some 301's.

Please, If anybody sees anything broken or odd please email me. Now it's time to sleep!

Database

To illustrate how to do this I'll start out with the following MySQL table (the remainder of the post assumes it's filled with sample data). It'll sit underneath the the Document class in an application named "searcher". I'm sort of working backwards here as I've actually generated this table from a model I've included later in the article.

CREATE TABLE searcher_document (
    id integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    url varchar(900) NOT NULL,
    content longtext NOT NULL,
    title longtext NOT NULL
);

Then, just to give us something fun to do, I'll slap a fulltext index on the content and title columns:

CREATE FULLTEXT INDEX FIX ON searcher_document(content, title);

Now I need a stored procedure to call. Here's a simple procedure that performs a fulltext query on the table I just created. In reality I'd probably only employ a procedure for a somewhat more complex task but it'll work for demonstrative purposes.

CREATE PROCEDURE searcher_document_search(
	search_string varchar(255)
) 
BEGIN
	SELECT id, url, content, title 
	FROM searcher_document 
	WHERE MATCH(content, title) AGAINST (search_string);
END

Model

With the database work out of the way I'm ready to define my model:

from django.db import models
from django.db import connection

# Create your models here.

class Document(models.Model):
    # fields
    url = models.CharField(max_length=900)
    content = models.TextField()
    title = models.TextField()

    # static method to perform a fulltext search
    @staticmethod
    def search(search_string):
        # create a cursor
        cur = connection.cursor()
        # execute the stored procedure passing in 
        # search_string as a parameter
        cur.callproc('searcher_document_search', [search_string,])
        # grab the results
        results = cur.fetchall()
        cur.close()

        # wrap the results up into Document domain objects 
        return [Document(*row) for row in results]

And that's about it, the comments tell the tale. I created a cursor and employed its callproc method to execute my procedure. Then I wrapped the results up into a list of Document objects.

Execution

With the business end of this out of the way I can now perform searches in a view as follows:

results = Document.search(request.GET['search_string'])

Conclusion

Like I said before, when developing web applications in ORM based frameworks such as Django there's almost always a way to avoid hand-crafting SQL or using stored procedures. In those situations where it can't be avoided or is inadvisable it's nice to know that you still have the option.

For those of you who are unfamiliar with CouchDB it's a schema-free, document-oriented database system with a REST JSON API and is written in Erlang.

Screenshots

The project is just getting underway and I'd appreciate any help or feedback anyone has to offer.

This makes it far easier to use EF with legacy domain classes and keeps data models clean and unconcerned with their own persistence.

To demonstrate this feature I'll start out with a basic SQL Server table structure and test data that maps employees to departments.

Database

create table Departments (
  Department_ID int identity primary key clustered,
  Name varchar(255) not null
)

create table Employees (
  Employee_ID int identity primary key clustered,
  FirstName varchar(255) not null,
  LastName varchar(255) not null,
  Department_ID int references Departments
)

Then I'll fill them up with some test data.

insert into Departments 
select 'Information Technology'
union
select 'Accounting'

insert into Employees
select 'John', 'Doe', 1
union
select 'Bill', 'Barley', 1
union
select 'Jane', 'Doe', 2
union
select 'Lynn', 'Smith', 2

Entity Model

Now I'll generate an entity model named StaffModel from the database just as I would traditionally. The difference is that afterward I'll remove "EntityModelCodeGenerator" from the entity model's "Custom Tool" property (see section highlighted in green in the image to the right).

Domain Classes

With the entities complete I'll define some basic domain classes to persist. Note that there's no configuration required to map class names to tables and property names to columns. The framework relies purely on naming convention to accomplish the mapping.

public class Department
/* look ma, no subclassing EntityObject*/
{
  public string Name { get; set; }
  public int Department_ID { get; set; }
  public List<Employee> Employees { get; set; }
}

public class Employee
{
/* look ma, no subclassing EntityObject*/
  public int Employee_ID { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
  public Department Department { get; set; }
}

Object Context

Now I'll write a context that will connect my domain objects to their storage. This is really the only plumbing I'll code by hand.

public class StaffContext : ObjectContext
{
  private ObjectSet<Employee> _employees;
  private ObjectSet<Department> _departments;

  public StaffContext()
    : base("name=StaffEntities", "StaffEntities")  
  {
    _employees = CreateObjectSet<Employee>();
    _departments = CreateObjectSet<Department>();
  }

  public ObjectSet<Employee> Employees
  {
    get 
    { 
      return _employees; 
    }
  }

  public ObjectSet<Department> Departments
  {
    get
    {
      return _departments;
    }
  }
}

Usage

With the EF end of things complete I'll whip up a simple console app to query the model.

class Program
{
  static void Main(string[] args)
  {
    // establish connection
    using (StaffContext context = new StaffContext())
    {
      // query database
      var departments = context.Departments.Include("Employees");

      foreach (Department department in departments)
      {
        Console.WriteLine(department.Name);

        foreach (Employee employee in department.Employees)
        {
          Console.WriteLine(string.Format("\t{0}, {1}", 
            employee.LastName, employee.FirstName));
        }
      }
    }
  }
}

Which produces the following output:

Information Technology
        Barley, Bill
        Doe, John
Accounting
        Doe, Jane
        Smith, Lynn

Conclusion

The inclusion of POCO entities in EF 4 solves several common complaints amongst ADO.NET developers. They assist in separating concerns, easing testability and simplifying integration with existing domain classes.

In my estimation this could spur adoption of EF which has been received somewhat coldly thus far.

For those of you not familiar with SSRS data processing extensions they're simply libraries that teach reporting services how to talk to data sources. They merely require you to implement a few interfaces such as IDbCommand, IDbConnection and IDataReader from Microsoft.ReportingServices.DataProcessing. A great introduction to developing these extensions can be found here.

After a few days of work I got a decent start on a library written in C# 2008 named SimpleDBExtension that I've published here on CodePlex. At the time of writing it's still in early development so please be forgiving (Update 2009/12/07: it's in it's first beta.). There's certainly a major refactoring on the way and several features I'd like to add.

To develop reports in Visual Studio against SimpleDB with this library there's just a few simple steps which are outlined below:

Step 1: Build and install SimpleDBExtension library

After building the SimpleDBExtension project copy the resultant SimpleDBExtension.dll into the directory Visual Studio uses for the Report Designer. On my system that is: C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies

Step 2: Modify RSReportDesigner.config

Add the following node to the data extensions section in RSReportDesigner.config which is located in the aforementioned directory.

<Extension Name="SIMPLEDB" Type="SimpleDBExtension.SimpleDBConnection,SimpleDBExtension"/>

Step 3: Modify RSPreviewPolicy.config

Add the following node to the code groups section in RSPreviewPolicy.config which is also located in the aforementioned directory. You'll have to modify the URI attribute to reflect the directory you're working in.

<CodeGroup class="UnionCodeGroup"
   version="1"
   PermissionSetName="FullTrust"
   Name="SimpleDBExtension"
   Description="Code group for SimpleDb data processing extension">
	  <IMembershipCondition class="UrlMembershipCondition"
		 version="1"
		 Url="C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\SimpleDBExtension.dll"
	   />
</CodeGroup>

Step 4: Develop

Since Visual Studio's Report Designer is prepared we can get down to business. Get started in the typical fashion: create a report project and a report.

Here's where it gets different. An additional data source type will now be available named "SIMPLEDB" which will employ the extension you just installed. Choose that and and enter your access key and secret separated by a semicolon in the connection string.

Now you can create a DataSet using the newly created Data Source. Supply a SimpleDB select query and you're all set. Your SimpleDB data is now accessible to SSRS.

Next Steps

Thus far we've just installed the extension into Visual Studio but this process can be used to deploy the extension to a Report Server.

Again, at the time of writing this project is just a few days old. Any help, guidance, or comments are appreciated.