Using Propel with CodeIgniter

CodeIgniter is a nice development framework used for PHP applications. Is light, small, compact and it does provide an “Object Oriented” solution for web development. However, it lacks a major component, which is a database infrastructure. CodeIgniter’s models provide a rather quick and dirty solution for database access, which is nice for small projects, because we cannot be thinking on the large solution every time we write a piece of code, but for most decent application, a small approach like that one, just won’t do it.

The solution? Well, using an ORM framework. I’m a defender of ORM mapping solutions and I believe that they are here to stay, just like the Korn song. Anyways, there is no easy way to integrate CodeIgniter with Propel; they just don’t seem to get along. I’ve read a wiki post explaining how to do it, but I found it quite disturbing for a few reasons

  • A solution shouldn’t tell you to change the apache configuration
  • Propel has a really powerful autoloading system, why did they not prepared for using it?

Anyways, the idea I came up with is to integrate Propel as an independent library for the framework using the libraries autoloader, which is much like the post I’m talking about, but I made a few tweaks. CodeIgniter stores it’s plugins into the folder system/libraries, so the best way to start is copying Propel’s runtime into that folder. Create a folder called propel and copy the runtime files (propel/runtime/lib/*) there.

After copying Propel we can create the library’s entry point, which is a file called propel.php and there we will write a class to load Propel:

class CI_Propel
{
  public function CI_Propel()
  {
 define(DIRECTORY_SEPARATOR, “DS”);
     require dirname(__FILE__) . DS . "propel" . DS . "Propel.php";
     Propel::init(BASEPATH . "application/config/propel-conf.php");
  }
}

In this class we require the Propel main file (Propel.php) and we tell it where he can find the configuration file. The configuration file is the xml file generated by Propel storing the database configuration and we must specify it in order to get propel running.

Now we’ve created our driver, we need to tell CodeIgniter to load our library, and this is done by going into the application/config/autoload.php file, where CI stores the libraries to be loaded with the application. We need to look for the line:

$autoload['libraries'] = array();

And change it with the information we need to load propel:

$autoload['libraries'] = array('Propel');

Now, we need to copy Propel’s models into the models folder located in the application folder and the configuration files generated should be on the application/config folder. Now the mapping configuration files should be edited to load the models properly; Propel works with relative routes to the models and it breaks when we try to load those models from a different path. You should look for lines like this one:

'CategoriesTableMap' => 'Northwind/map/CategoriesTableMap.php'

And change them for the real CI path:

'CategoriesTableMap' => BASEPATH . DIRECTORY_SEPARATOR . "application" . 
DIRECTORY_SEPARATOR . 'models/map/CategoriesTableMap.php'

Well, all we need to do now is use Propel! Keep in mind that using Propel’s classes directly from the controller is a bad practice, because you’d be coupling your data access layer to your presentation layer, you can take a look on this post if you want to read a bit more about it.

For our practical purposes we can just call the Propel query object from the controller now, much like this:

function index()
{
  $query = ProductsQuery::create()->limit(10)->find();
  $data =  array();
  $data['rows'] = $query;

  $this->load->view('welcome_message', $data);
}

And it will retrieve the results quickly and using a nice ORM mapper. I hope you’ve found this post useful and as usual commenting is highly encouraged!

New year!

New year starting, and I need to set up a few goals. First, I will be creating my own template, because like Scott Hanselman said, using a provided template in a blog might just look lame.

The second thing I need to do is post more stuff. I’ve been working on some cool stuff, and I think that I should post them, so people can see it and perhaps I can help someone out there!

That’s the things I’m going to be working on this month to boost my blog a bit. Anyone who reads this, just wish u all a happy new year!

Inspecting SQL Server’s tables

A few days ago, I was working on a small project for autogenerating source code from a SQL Server database, and I bumped into the problem of obtaining from a given server the description of the tables in it. If using MySQL this task is quite simple, however, using SQL Server the problem is not that simple.

Long story short, after a few hours I came up with this “small” piece of code:

SELECT DISTINCT
     sys.tables.object_id AS TableId,
     sys.columns.column_id AS ColumnId,
     sys.columns.name AS ColumnName,
     sys.types.name AS TypeName,
     sys.columns.precision AS NumericPrecision,
     sys.columns.scale AS NumericScale,
     sys.columns.is_nullable AS IsNullable,
     ( 
     SELECT
          COUNT(column_name)
     FROM
          INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
     WHERE
          TABLE_NAME = sys.tables.name AND
          CONSTRAINT_NAME =
          ( 
          SELECT
               constraint_name
          FROM
               INFORMATION_SCHEMA.TABLE_CONSTRAINTS
          WHERE
               TABLE_NAME = sys.tables.name AND
               constraint_type = 'PRIMARY KEY' AND
               COLUMN_NAME = sys.columns.name
          )
     ) AS IsPrimaryKey,
     sys.columns.max_length / 2 AS CharMaxLength
FROM
     sys.columns, sys.types, sys.tables
WHERE
     sys.tables.object_id = sys.columns.object_id AND
     sys.types.system_type_id = sys.columns.system_type_id AND
     sys.types.user_type_id = sys.columns.user_type_id AND
     sys.tables.name = '{0}'
ORDER BY
     IsPrimaryKey

Nice uh?

Basically the whole deal after this code is just inspecting into the sys database, which holds the information about the tables in the database. Obviously the ‘{0}’ should be replaced with the table in question. I place the ‘{0}’ because this code was meant to be running using C#, so, all I needed was a call to string.Format.

After retrieving the data with the tables, it would be nice to inspect the relationships between those tables, uh? Well, that is simpler than you might think, just we need to retrieve the relationships list, again from the same database. SQL code for doing that is this one:

SELECT
     sys.foreign_keys.name AS RelationshipName,
     sys.foreign_keys.object_id AS RelationshipId,
     sys.foreign_keys.parent_object_id AS ParentTableId,
     sys.foreign_keys.referenced_object_id AS ReferencedTableId,
     sys.foreign_key_columns.parent_column_id AS ParentColumnId,
     sys.foreign_key_columns.referenced_column_id AS ReferencedColumnId
FROM
     sys.foreign_keys, sys.foreign_key_columns
WHERE
     object_id = constraint_object_id; 

After having all that data, we are in conditions to generate any source code based on any database inspected from a SQL Server 2k5/2k8 database, which is a cool thing.

Helpers vs Extenders

One of the most important features we need to achieve in our life as developers is to write code which at some point can be reused. Reuse is tricky word because sometimes we find ourselves saying “it just needs a tiny modification” when it needs some major rewriting. Making reusable code is not just writing a snippet of code and then copy it and write it down again, it’s having libraries of code that don’t need any sort of modification (in the worst case scenario, some small ones).

This takes me to the point of this post, which is considering method extensions when developing applications. Let’s see the following problem, suppose we need for a certain task to retrieve the names of the properties available in an object, but we need to make this as generically as possible, because we don’t know when we could need the same feature again.

The initial approach is to do a small static class which does the job, most people tend to call these classes helpers. Helpers are there to “help” our code doing stuff that might be needed by several parts of the same application. To our problem, the helper code would look like this:

public static class ReflectionHelper
{
    public static List<string> GetMethods( object who )
    {
        //Implementation of the method
    }
}

This approach does work most times, but is not nice in the refactoring sense of development. We need to keep these kinds of helper objects to the smallest amount in any design we make, because by following this pattern, we can end up in one of two possible scenarios.

The first possible ending is that we might end up with a few helper classes doing just a few functionalities, sometimes providing a really horrible refactoring nightmare. The second possible scenario is that we might end up with one monster helper with a lot of helper methods, which will make the previous nightmare look like a pleasant dream.

There are two more plausible solutions to this problem. The first one applies to the case where retrieving the list of methods is part of the work of our objects; to this case we could apply the scenario of an ORM framework when it’s mapping the properties to an object and retrieves the properties using reflection. Anyways, in this case we could add a base class called ReflectableObject, which provides all the required functionalities.

If we choose to follow the ORM sample, we would have a class diagram having a Model class and to complicate things a bit, we could also have a repository class. Both would need to perform some sort of reflection on the objects they manage, so they would require it. The class diagram would be like this:

This way works pretty well for the cases where we want to include this behavior into the object, but this does pose a few problems:

  • We would need to add an extra layer of classes to our design, thus complicating the design
  • When we have only one class needing to perform the actual operation we need to create, adding another layer of complexity would feel like killing a fly with a rail gun.
  • If by any chance of fate we are working with sealed classes, inheritance is automatically discarded.

Finally, there is one last option: Using extension methods. Extension methods are there to provide a flexible and reusable way to extend objects that are there already, and we do not want to either modify or inherit.

We can think of extensions as a light inheritance, we say: “OK, we have this class and we want to add a few methods to it, but we don’t want to create a new class and inherit from it, so what do we do?”

We extend the class.

The code to extend a class is quite similar to the helper, but instead we include a reference to the class we are planning to extend in the method, like this:

public static class ReflectionHelper 
{ 
     public static List<string> GetMethods( this object who ) 
     {
          //Implementation of the method 
     } 
} 

Is quite simple, suppose that our class is in the ReflectionHelpers namespace, we would be able to do this then:

using ReflectionHelpers; 
using System; 

namespace MyNamespace 
{ 
    public class MyClass 
    { 
        public void Foo() 
        {
            object o = SomeWeirdOperation(); 
            var methods = o.GetMethods(); 
        } 
    } 
}

All objects can call now the GetMethods method! By doing this we are successfully removing the extra layer from the design, we are getting the job done and it doesn’t requires new classes to be added, if we change the ReflectionHelper class name to Reflexive it wouldn’t matter, because all that matters is that the GetMethods method is extending the object class as specified in the object signature.

Extension methods are the base of LINQ, and perhaps one of the most powerful tools implemented in the .NET framework.

Simple class for data access using ADO.NET

Many data access layers we see today are meant for medium to big projects, and building the required models, and loading the required dll’s in a small project sometimes feels like trying to kill a fly with a mortar. So, what do we do most times? Write some plain ADO.NET access code to retrieve the data and that’s it, we don’t want to mess our code with NHibernate, Entity Framework or DataSets only for retrieving data from a single (or two) tables. If we change providers, all we would do is just change the class names.

This approach is simple, but with a little tweaks we can make it reusable, ergo we don’t have to rewrite the code is it would be a fairly plain database access. To accomplish this, we will make a small mix of singletons and dictionaries, to be able to talk to different databases at the same time. I’ve found this feature useful, since I use SQLite for caching my request and MySQL to handle big stuff that I require saving on DB.

So, our singleton code would be like this one:

private static Dictionary<string, Database> _instances;

public static Database Get(string flavour)
{
    if ( _instances == null )
        _instances = new Dictionary<string, Database>();

    if (_instances.ContainsKey(flavour))
        return _instances[flavour];

    var dbProvider = DbProviderFactories.GetFactory(flavour);
    var connectionStrings = ConfigurationManager.ConnectionStrings;
    var cs = connectionStrings[flavour].ConnectionString
                              .Replace("|HomeDir|", Environment.CurrentDirectory)
                              .Replace("'", """);
    
    _instances.Add(flavour, new Database(dbProvider, cs));
    return _instances[flavour];
}

private DbConnection _connection = null;
private DbProviderFactory _factory;

private Database(DbProviderFactory factory, string connectionString)
{
    _factory = factory;
    _connection = _factory.CreateConnection();
    _connection.ConnectionString = connectionString;
}

As you can see, we use the DbProviderFactories class to load the required factory classes for our project. A sample app.settings file would be like this one:



    
        
        
    

    
        
            
            
            
            
            
        
    


The basic idea is to be able to load the factory required for our flavor from an XML file and forget about instantiation of classes. This way, we can truly reuse our code. Now we need to mimic the 3 important methods of any ADO.NET command:

  • ExecuteScalar
  • ExecuteNonQuery
  • ExecuteReader

But, we also need to support parameters, and to do so, we will just pass parameters as a list of KeyValuePair objects. With that in mind, let’s create an utility method to create the command for a given query:

private DbCommand CreateCommand(string query, 
                                params KeyValuePair<string, object>[] args)
{
    var cmd = _factory.CreateCommand();
    cmd.Connection = _connection;
    cmd.CommandText = query;

    foreach (var argument in args)
    {
        var param = _factory.CreateParameter();
        param.ParameterName = argument.Key;
        param.Value = argument.Value;

        cmd.Parameters.Add(param);
    }

    return cmd;
}

With this utility method, creating one of the proxies for ADO.NET is quite simple. The ExecuteScalar proxy would be like this one:

public object ExecuteScalar(string query, params KeyValuePair<string, object>[] args)
{
    var cmd = CreateCommand(query, args);

    try
    {
        _connection.Open();
        object scalar = cmd.ExecuteScalar();

        _connection.Close();
        return scalar;
    }
    catch (Exception)
    { return null; }
}

This one is quite easy uh? Now, we wont return a DbDataReader in the ExecuteReader, because it would be simple to read the values and return a List of dictionaries, where each Dictionary object would represent a given row.
Since I don’t return a DbDataReader, I thought that ExecuteReader wouldn’t be OK, so I changed the name to ExecuteList. The basic layout of the method is like the past one, but once we have the DbDataReader, we would fill the rows, like this:

while (reader.Read())
{
    var row = new Dictionary<string, object>();
    int fcount = reader.FieldCount;

    for (int i = 0; i < fcount; i++)
    {
        string fName = reader.GetName(i);
        object val = reader.GetValue(i);

        if (reader.IsDBNull(i))
            val = null;

        row.Add(fName, val);
    }

    result.Add(row);
}
&#91;/csharp&#93;

<p>And we return the result, which is of type List&lt;Dictionary&lt;string, object&gt;&gt;. Cute uh?</p>
<p>With these three methods, we have a jumpstart for a nice (a very simple) data access class, but what would be of .NET without types, so we would expect to have a typed retrieval method, something like this:</p>
public List<t> ExecuteList<t>(string query, params KeyValuePair<string, object>[] args)

Where T is (hopefully) a POCO class, and we just want to fill it. This is a lame attempt of doing some ORM, but as we said earlier, the idea is to create a small reusable class for data access in small apps, most likely for small jobs or personal projects.
Back to the problem, we would need a method to receive a dictionary and return an object of type T, and using the wondrous Reflection, we can do this:

private T ParseClass<t>(Dictionary<string, object> hash)
{
    Type t = typeof (T);
    var properties = t.GetProperties();
    var instance = Activator.CreateInstance(t);

    foreach (var property in properties)
    {
       if ( property.CanWrite && hash.ContainsKey(property.Name) )
        property.SetValue(instance, hash[property.Name], null);
    }

    return (T)instance;
}

I’m making a few assumptions here.

  1. First, I use classes to access data, not DataTables, since I find Types much more convenient at development time. I think we can agree that this:
        var name = st.Name;
    

    Is much friendlier than this:

        var name = (string)dt.Rows[“Name”];
    

  2. Second, this magic method happens only for simple classes, which work as POCOs so we don’t have to worry about read only properties, or strange fields or highly complex structures, just some small properties, keep in mind, that for medium projects, using a small class like this could (most times IS) fatal to your application/design.

With that in mind, the last method for typed data retrieval would look like this:

public List<t> ExecuteList<t>(string query, params KeyValuePair<string, object>[] args)
{
    var dictionary = ExecuteList(query, args);
    return dictionary.Select(ParseClass<t>).ToList();
}

As I’ve said many times in this post, this class would be only for small projects, but it does help to have a something like that, because we might find a few personal projects with small lines of code using the same data access code and retyping it again and again is not going to make it better. Remember that any snippet of code that you type over and over in projects, no matter how small it is, can be abstracted into a bit more complex (and useful) library.

I hope that this code can help someone out there, feel free to comment!

Using ORM classes as Business models?

For a long time, Object Relational Mapping (ORM) has proved to be a very good solution for data driven applications. Dynamic web applications are those who serve data store in a database; and this data is introduced by a (often not so friendly) user interface, hence the “dynamic” part. As design patterns emerged, one particular pattern became very used in frameworks and applications, which it was the Object Relational Mapping design pattern.

The basic idea behind the ORM pattern is to “map” each table (relation) to a class in our application. Following this notion, then a particular instance of a class was a row in the table, which could, at some point of the application’s lifecycle, be persisted or not. ORM frameworks handled the process of creating, retrieving, updating and deleting rows as objects, finding a way to know if a particular object has been saved or not. The basic layout of the behavior of a ORM tool/framework would be like this:

A class is generated for each relation we found in the database. We make our data model, then we generate the classes for it. Notice how I’ve made some underlining on generate, that is because most of the ORM frameworks has a built in class generator. After all, what good would they do if they don’t bring this?

The two major ORM frameworks I know for PHP, which are Propel and Doctrine, both have built in generators for our models. With these generators, we get a set of classes each handling a task on the tables we have on the data backend. So far, this sounds great! But the good part hasn’t yet arrived. 

For the purpose of this application, let’s create a sample database with the Northwind database script, which has been used for these purposes for a long time due to the fact that has a few relationships, some good data and all the cool things to create a nice playground for testing an sort of data technology.

When we are developing a full blown application, we have some validation rules and some desired behaviors, like for instance, to know the full price of an order we use an equation, in a typical scenario, this would be a simple equation like this one:

Full Price = (Unit Price * Amount) + Shipping + Handling – Discount

This is what we call a business rule, and they are extracted from the client, which sometimes can be ourselves. Business rules define how our application responds to input and are the very core of it, however the can change at any point of our application. We could embed our business rules into the data extraction routine (although is not always possible); for instance, we could store a view in database returning the rows we need, something like this:

SELECT  
   `orders`.OrderID,
   (`orderdetails`.UnitPrice * `orderdetails`.Quantity) AS RawPrice,
   `orders`.Freight AS ShippingHandling,
   `orderdetails`.Discount as Discount,
   (`orderdetails`.UnitPrice * `orderdetails`.Quantity + `orders`.Freight) -  
                   `orderdetails`.Discount AS Total
FROM
   `orderdetails`, `orders`
WHERE
   `orderdetails`.OrderID = `orders`.OrderID
GROUP BY
   `orders`.OrderID
ORDER BY Total DESC

This approach is (sadly) used by many online applications nowadays, where the business logic is embedded in the data source. With this approach, one main issue comes to scene: maintainability. Maintainability is the nightmare of many applications; in this case, the big trouble would occur if any of the calculating equations needs to be changed. To do so, we would require access to the database, change the view and use the new equation. This is a viable solution, because after all, it works! But attaching these application specific calculations to the data store is not good because you would have to change the way your data is stored to make changes, and practice demonstrates that whenever possible you should avoid messing with your database.

The other way to do this is by using the same classes generated by those ORM frameworks we talked about, like Propel (example are using Propel 1.5). The code doing the same function we just did would now be:

   $query = OrderDetailsQuery::create();

$result = 
    $query->joinOrderDetails()
          ->withColumn("`orderdetails`.UnitPrice *  
                        `orderdetails`.Quantity", "RawPrice")
          ->withColumn("`orderdetails`.UnitPrice *  
                        `orderdetails`.Quantity + `orders`.Freight -
                        `orderdetails`.Discount", "Total")
          ->Select(array("RawPrice", "Total", 
                        "Orders.Discount", "Orders.OrderID"))
          ->groupBy("Orders.OrderID")
          ->OrderBy("Total")
          ->find();

The outcome of this query is the same than before, but now our business rules are on the data layer, however, this is not quite yet the result we want. For this example, a development like this one might work since we are just making a simple and plain query, but suppose we have another table called Customer2Product to store the preferences of customers using products and we need to insert a row into this table every time we add a product into a customer’s orders. Obviously this behavior cannot be attached into the data store, but in some cases we might do it in the data access layer.

Having the business rules in the data layer is not good, because we need to keep in our architectures a clean separation of concerns. The data layer is concerned only on managing (CRUD) data, not handling logic rules regarding the application. To handle such rules, we need to create another set of classes, which are the business models.

The business layer should have a common gateway to our Data Access Layer, thus allowing communication with the data source. At first glance, the most important improvement would be the independency of data store. If we choose to change from Propel to Doctrine, or to anything else, we would only need to change our models, and the middle point between the Business classes and the Data Access Layer.

To communicate the Data Access Layer and the Business Layer we need to create a class working as a middle man, let’s call it an Adapter. The Adapter should be an interface, to allow the use of implementations. How this Adapter is implemented is a different problem on every architecture, but do keep in mind the development premise called YAGNI (You ain’t gonna need it). A sample interface would be like this:

public interface ITranslator
{
    public function Select($criteria);
       
    public function Count($criteria);
       
    public function SelectOne();
   
    public function Save($model);
       
    public function Delete($criteria);
}

After this interface is built, we would just need to implement it for each Data Access Provider we might use regardless of the type of provider.

Another setback of having the business (logic) rules of our application in the Data Access Layer is that we are directly introducing a dependency on a 3rd party tool, which is our ORM framework (this applies for every ORM frameworks, even those made by yourself). If at some point of development, we choose to remove the ORM framework in use, because of any reason, we would need to rebuild/remake most of our classes, which would be awful!

You might think… My ORM framework is very strong; I won’t be in need to change it ever!

Well, if that’s the case, then think about the amount of projects failing each day due to dependency problems. Remember that there have been many software crises and that software is a field constantly in development, meaning that tools will evolve and patterns will evolve as well. Every day there are new patterns, new technologies and many new or improved resources available for developers, so we cannot tie our architecture to any specific tool/library just because is the hot thing.

Following the wise words of many designers and architects we need to keep in mind that in software history we have never seen such thing as a “Software Silver Bullet”.