MSSQL Extended Properties Library for CodeIgniter

Microsoft SQL Server may not be the best of choice of database system but I'm required to use it at work. So, I make the best of things. Here I'll show you a simple library for utilizing user-defined properties in your database.

See, MSSQL has a nifty feature: Extended Properties. Meta-data stored for each field in a table. I use these properties to store information such as form display name, input type, row order, and help text. This opens the door to many possibilities. Such as a custom scaffolding library!

Adding fields to a form is as simple as adding a field in the database and setting it's extended properties. To ease the task of modifying these properties I developed a management system that allows most any element of a form to be changed without having to modify any actual code. Even the SQL query for a select element's options can be automatically generated or set manually from this interface.

Library

There library is fairly cut and dry. It only contains get and set methods, but really it doesn't need to do more than become the bridge between your PHP code and a field's potential meta-data.

Usage

• Load the library:
  

  $this->load->library('extended_property');
  

• Set a property:
  

  $this->extended_property->set('table', 'field', 'PROPERTY', 'value');
  

• Get a property:
  

  $property_value = $this->extended_property->get('table', 'field', 'PROPERTY');

Source Code

class Extended_property {

   var $CI;

   function Extended_property()
   {
      this->CI =& get_instance();
   }

   function get($table, $column, $property)
   {
      $result = $this->CI->db
           ->query('
                SELECT value
                FROM ::fn_listextendedproperty(
                     N\''.$property.'\',
                     N\'user\', N\'dbo\',
                     N\'table\', N\''.$table.'\',
                     N\'column\', N\''.$column.'\')')
           ->limit(1)
           ->row();

      return (!empty(@$result->value)) ? $result->value : false;
   }

   function set($table, $column, $property, $value)
   {
      $this->CI->db
           ->query('
                IF EXISTS(
                     SELECT value
                     FROM ::fn_listextendedproperty(
                          N\''.$property.'\',
                          N\'user\', N\'dbo\',
                          N\'table\', N\''.$table.'\',
                          N\'column\', N\''.$column.'\'))
                EXEC sp_dropextendedproperty
                     N\''.$property.'\',
                     N\'user\', N\'dbo\',
                     N\'table\', N\''.$table.'\',
                     N\'column\', N\''.$column.'\';
                EXEC sp_addextendedproperty
                     N\''.$property.'\', N\''.$value.'\',
                     N\'user\', N\'dbo\',
                     N\'table\', N\''.$table.'\',
                     N\'column\', N\''.$column.'\'');
   }

}

Instead of updating a property, I decided to check if it exists and then drop it before adding a new one. This is because sp_updateextendedproperty throws and error when trying to update a property that does not yet exist.

I searched the Internet and couldn't locate any information on accessing MSSQL extended properties within CodeIgniter other than handwritten queries. So, if a library exists, point me to it. Until then, this does a fine job for me and maybe you can benefit from it as well!