MSSQL Extended Properties Library For CodeIgniter

Microsoft SQL Server may not be the best of choice of database software 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 display name, input type, form row order, and help text to be used with a custom scaffolding library I wrote as well.

It makes adding fields to a form as simple as adding a field in the database and settings 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 set.

Library

There really isn’t much to the library. 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 meta-data.

Usage

Load the library:

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

Set a property:

$this->extended_property->set('users', 'email', 'VALIDATION_TYPE', 'email');

Get a property:

$validation_type = $this->extended_property->get('users', 'email', 'VALIDATION_TYPE');

Source Code

<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Extended_property {

	var $CI;

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

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

?>

*Don’t ask me about the “emptyempty” error. Must be a problem with the syntax highlighting.

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 really find any information on accessing MSSQL extended properties in CodeIgniter other than just straight SQL. So, If one exists, point me to it. Until then, this does a fine job for me and maybe someone else can get some use out of it.