Simplifying SQL SELECT

In my opinion, any SQL SELECT query, or for  that matter any select query, can be described by four properties:

 

SELECTED OBJECTS

A table, some joined tables or a query. The source always represents one business object, which is optionally extended with one or more other business objects, through a relation. In SQL this goes in the FROM clause, and has effect on the GROUP BY clause.

 

SELECTED PROPERTIES

A list of tablefields, values, functions and derived things. The property always represents a property of a business object that is listed in SELECTED OBJECTS. In SQL this goes in the SELECT clause.

 

CONDITIONS

An evalution that determines wether a record is selected or not. Multiple evaluations can be combined through boolean operators. An evalulation always represents a property or a relation of a business object that is listed in SELECTED OBJECTS. In SQL this goes in the WHERE or HAVING clause.

 

ORDERING

A list of tablefields, values, functions and derived things. The property always represents a property of a business object that is listed in SELECTED OBJECTS, optionally accompanied with a property ‘invert’, which reverses the sort order. In SQL this goes in the ORDER BY clause.

 

The SELECTED PROPERTIES, CONDITIONS and ORDERING are all dependent on SELECTED OBJECTS. With a map from our business model to the database we can generate the SELECT query from a business representation using only these four properties.

On the business side, the structure looks something like this:

object -> property*
object -> relation*
property -> datatype
relation -> object

On the database side, it looks like this:

table -> field*
table -> foreignkey*
field -> datatype
foreignkey -> table

As you can see, the two  models can be mapped very easily. So what are the benefits of using a map instead of directly writing the query?

You can have:

• Nice field names
• Customization of the presentation
• Formatting
• Multilingual support
• Input validation
• Form generation

All these things require some sort of ORM, since most databases do not provide these functionality by default.

As for the select simplification, Doctrine seems to deliver a lot of that:

http://www.doctrine-project.org/docs/orm/2.0/en/reference/query-builder.html

PHP CMS Framework

I am working on creating a PHP CMS framework for myself and others. I know that there are many CMS frameworks already, but the ones I found are very complex and/or difficult to use. In addition, I am a fan of very clean code, that’s why I like to give it a try myself.

The goal is to create something that is extremely easy to use from a programmer, designer, system administrator or end-user perspective.
It should also be as flexbile as possible and synchronize easily with other frameworks.

What I have so far is an idea about the general structure of the project. Feel free to comment criticism.

• application
• kernel
  • App.php
  • Module.php
  • Router.php
  • __autoload.php
  • functions.php
• modules
  • start.php
• install
  • start.php
• boot
  • start.php
• www
• config.php
• start.php
• public_html
  • .htaccess
  • start.php

The ‘application’ folder is where the php application is located.

The ‘public_html’ folder is where the documentroot is pointing to.

The .htaccess file rewrites every non-existent call to the ‘public_html/start.php’.

The ‘public_html/start.php’ then calls the ‘application/start.php’.

This mechanism makes sure that you can keep any other frameworks in place, while you can have the application folder wherever you want, using relative paths.

The file ‘application/start.php’ loads the php files in ‘kernel’.

An instance of the App class can now be instantiated:

$app = new App(‘path/to/application’);

The app can now be started with the following command:

App->Start();

The file ‘modules/start.php’ will be loaded to load the modules.

Each module contains a script that will add the classnames and filenames to the App classes for autoloading.

If the file ‘config.php’ does not exist, the file ‘install/start.php’ will be loaded to start an installation script.

The file ‘boot/start.php’ will be loaded to start custom functions (session, authentication, language).

The class Router will be used to forward execution to a file in the www folder.

So now the user can copy modules to the modules folder and add them to the ‘modules/start.php’.

Installation can be customized by changing the ‘install/start.php’.

Boot proces can be customized by changing the ‘boot/start.php’.

From the www folder php an html can be executed in a regular fashion, but with the framework in place.

Array to object conversion in PHP

I just wrote this simple function to convert arrays to objects in PHP.
For me it is convenient to have such a function at hand for quickly generating objects.


/*
* @param $class the name of the class that should be instantiated
* @param $array the array with key=>value combinations that is used to create the object
* @return an object of type $class with the values from $array
*/
function a2o( $class, $array ) { return array2object( $class, $array ); }
function array2object( $class, $array )
{
$obj = new $class( );
foreach($array as $key => $value)
$obj->$key = $value;
return $obj;
}


The question mark in URLs

When constructing an url via a plugin, you cannot always know at which position the ? will come.
A simple htaccess rule can be used to strip off the first ?:

RewriteEngine On

RewriteCond %{REQUEST_FILENAME} !-f
RewriteRule (.*)&(.*) $1?stripquestionmark=true&($2) [L,NS] [L]

How to parse strings to numbers in T-SQL

If you execute this SQL statement, the function dbo.ParseNumber will be created:

CREATE FUNCTION dbo.ParseNumber
(
@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)
WHILE @IncorrectCharLoc > 0
BEGIN
SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)
END
SET @string = @string
RETURN @string
END
GO

You can now use this function in an SQL statement like this one:

SELECT dbo.ParseNumber(MyStringColumn) FROM MyTable

Or this one:

SELECT * FROM MyTable WHERE dbo.ParseNumber(MyStringColumn) > 10

More information about SQL string parsing can be found here:

http://blog.sqlauthority.com/2007/04/11/sql-server-udf-user-defined-function-to-extract-only-numbers-from-string/

Or here:

http://www.ibm.com/developerworks/data/library/techarticle/0303stolze/0303stolze1.html