Chris Umbel

Now in Django

Welp, this site has been ported to Django. It's my second Django project but this is the first one that extends beyond two pages.

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!

Wed Aug 19 2009 01:28:00 GMT+0000 (UTC)

Comments

Stored Procedures in Django

Web development platforms like Django go a long way to removing the need for writing hand-crafted SQL with all of their ORM goodness. Sometimes, for some reason or another, it's handy to execute hand-crafted SQL code or even a stored procedure. Naturally that should be a last resort and I don't intend on starting a discussion about when it is and when is not appropriate, but for those situations where it is this article applies.

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.

Sun Aug 09 2009 18:08:37 GMT+0000 (UTC)

Comments

CouchDBExtension

I've recently started work on a new project named CouchDBExtension which is hosted here on codeplex. In short it's a data processing extension that allows you to use Apache's CouchDB databases as a data source in SQL Server Reporting Services.

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.

Thu Aug 06 2009 18:53:22 GMT+0000 (UTC)

Comments

POCO Entities in ADO.NET 4.0

One of the most anticipated features of the Entity Framework 4.0 is the ability to have POCO (Plain Old CLR Object) entities. This allows developers to produce domain objects free of any persistence baggage, with no requirements imposed inheritance-wise. Up till now entity objects were required to either inherit EntityObject or had to implement IEntityWithKey, IEntityWithChangeTracker and IEntityWithRelationships.

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.

Thu Jul 30 2009 16:07:49 GMT+0000 (UTC)

Comments
< 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 >
Follow Chris
RSS Feed
Twitter
Facebook
CodePlex
github
LinkedIn
Google