Two very interesting reads

Two powerpoints from Jay Pipes - worth a read: http://jpipes.com/index.php?/archives/239-Slides-for-Join-Fu-The-Art-of-SQL-I-and-II.html

I agree with most of it !

In reaction to "Where did all the PHP programmers go?"

Leonid Mamchenkov is ranting about the difficulties of hiring a good PHP dev. I find the question quite valid...

http://mamchenkov.net/wordpress/2008/06/04/where-did-all-the-php-programmers-go/

I've been hiring PHP devs for the last few years, and I too have a test that I find desesperatly easy, and that too many (even senior) applicant fail miserably... It's based on http://www.techinterviews.com/, with questions going from sorting an array (I only ask the applicant what he should use) to the differences between GET and POST (very highlighting question).

I've even been challenged by my collegues, who thought the test was too hard, so I just ran it over a few people I would hire if they'd been available, and of course, they found the test to be just fine...

I still believe PHP is a great language, and we produce software that is really cool with it, with low effort. It is a pleasure to write code in PHP, and the low training curve is a good thing.
Now I've decided I'd rather hire a good professional and train him (as long as he's aware of the particularities of web applications - jumping from java to php is easier than from C++ to PHP - the syntax is one thing, the understanding of stateless software is another). All in all I agree it's very hard to find a good php developper, and I believe they all go to .Net, which I hope they like....

PDFtk

Found PDFtk - one of these tools that save you time, exceptionnal features, free, works on windows and linux. Need no more ! (if you need to merge, split, modify PDF files)

Perfomance trics for your web application

Stéphane Thomas, from Simple Entrepreneur, has gathered a bunch of slideshares from people around town that have worked on scaling web applications:

http://www.simpleentrepreneur.com/2008/03/11/comment-gerer-la-montee-en-charge-d-une-application-web/

It's an interesting read, to complete with http://highscalability.com

Bug Milestone

I've created a mediawiki extension that mimics Trac's ability to follow the progression of a Bugzilla milestone. All you have to do is put some bugs in a milestone, then put a tag in your wiki page with the name of the milestone, and you get a nice clickable progress bar with the bugs that are open or not: http://www.mediawiki.org/wiki/Extension:BugMilestone

Moving from MySQL to MS SQL using PEAR MDB2

MDB2 is a PEAR package that performs some abstraction on the native PHP DB calls. In short, it allows to use $DB->query($sql) instead of mysql_query or mssql_query (plus a few more things).

The following article is the list of pitfalls we had to fix in order to get a fully compatible application, that would run the same on MySQL or MSSQL (for now).

Field types

Field types are not the same between the two engines of course, but all in all it is not too bad. ENUMs don't exist in MS SQL so these will have to be moved to CHAR(1), MEDIUMTEXT should be TEXT.

If you want to use MDB2 Schema, beware that it considers ENUM('Y', 'N') to be booleans if the field starts with an "is_" or "has_", but then builds a database with boolean being TINYINT (we'll have to get back on them about that).

SQL syntax

There's a few things that work in MySQL and breaks in MS SQL. Here's what we found:

• REPLACE INTO: this one is a bugger, we used REPLACE extensively. Well that doesn't exist on MS SQL.


• ISNULL: ISNULL(somefield) should be somefield IS NULL. That's the ANSI way and ISNULL won't work on MS SQL...


• IFNULL: IFNULL is ISNULL on MS SQL, but you should really use the function COALESCE, which works the same (when used with two parameters) and is more ANSI.


• ORDER BY, GROUP BY: on MS SQL, you should be carefull that all the fields that you want to ORDER BY on, should be in the SELECT clause. Same goes for GROUP BY it seems. See further for GROUP BY as it can be a real bugger...


• COUNT: on MS SQL, COUNT only works with one field somehow


• DISTINCT: DISTINCT is a bit touchy on MS SQL - you should be carefull not to have fields that have type TEXT for example.


• NOW: Won't work on MS SQL. MDB2 provides some utility functions to build dates for SQL, but that really does a date('Y-m-d H:i:s')


• TO_DAYS: Won't work on MS SQL either. We really missed that one.


• LIMIT: LIMIT doesn't exist in MS SQL, but MDB2 prevides a setLimit() function that will do the trick on all DBMS. Good enough.

AutoIncremented fields

That's a big one. It'd be a good thing that DBMS makers could standardize on something for autoincremented indexes.

In MySQL, if your field id is an autoincremented field, you could do:

INSERT INTO my_table (id, name) VALUES (NULL, 'first');

That won't work on MS SQL (you can't leave a NULL value on them). The proper way to do it with MDB2 is as follows:

$values = array();

$values['name'] = $GLOBALS['mdb2']->quote("first");

$newId = $GLOBALS['mdb2']->extended->getBeforeID('my_table', 'id', true, true);
if($newId != 'NULL')
 $values['id'] = $newId;

$sql = "INSERT INTO my_table (".implode(',', array_keys($values)).") ".
    " VALUES (" . implode(',', $values) . ")";

$result = $GLOBALS['mdb2']->query($sql);
if (EpiDBTools::IsMDB2Error($result))
 return false;

$newId = $GLOBALS['mdb2']->extended->getAfterID($newId, 'my_table', 'id');
if (PEAR::isError($newId))
{
 $newId = false;
 return false;
}

It's a bit verbose but it works. The getBeforeID is required if you want your code to work some day on PostgreSQL, which uses sequences instead of AutoIncrement values.

Now if you want to insert a row in table with an autoincremented field, and still want to explicit the value of the index, you'll need to enable the IDENTITY_INSERT first, like this:

if ($GLOBALS['mdb2']->dbsyntax == 'mssql')
{
 // On MS SQL, allow temporarilly to insert a row by specifying the id
 $result = $GLOBALS['mdb2']->query('SET IDENTITY_INSERT my_table ON');
 if (PEAR::isError($result))
   return false;
}

$sql = "INSERT INTO my_table (id, name) VALUES (4, 'first')";

$result = $GLOBALS['mdb2']->query($sql);
if (PEAR::isError($result))
 return false;

if ($GLOBALS['mdb2']->dbsyntax == 'mssql')
{
 $result = $GLOBALS['mdb2']->query('SET IDENTITY_INSERT my_table OFF');
 if (PEAR::isError($result))
   return false;
}

Even more verbose, but it works.

MS SQL settings

• ANSI_NULL_DFLT_ON: you should set this parameter to ON, if you don't want all your fields to be NOT NULL...


• IMPLICIT_TRANSACTIONS: beware that if you leave that ON, then you'll have to do COMMITs from time to time, otherwise your changes won't be taken in account in the database. If you come from the MyISAM world, set this one to off (which is ON by default if you set ANSI_DEFAULTS to on).

GROUP BY

In MySQL, I allow myself to do queries like the following to get all the clients that have at least one order:

SELECT * FROM client
INNER JOIN order ON order.client_id = client.id
GROUP BY client.id

That won't work with MS SQL. In the SELECT clause, you are allowed to put things that appear in the GROUP BY clause (ie. client.id), or GROUP BY functions (MAX, AVERAGE, ...). It appears that MySQL is much more flexible than MSSQL regarding to this.

Dynamic inheritence for application extension in PHP

In any big enough PHP application, comes a time when some of the objects that are used in the application might be overriden by some extensions, to customize the functionnality of the application.

Let's take a example: your application has a class Employee that deals with the logic for every employee in your client's organization. Now imagine you have a client that wants to buy your application but wants to synchronize the employee table with its LDAP directory - fair enough.

What we want to do, is really to extend the Employee class, so that we can change the code of a few functions and add a few others to give your final client the behaviour he asks for.

So we add an extension (a folder really) where we put a class that extends Employee. We call that class LDAP_Employee for the sake of clarity:

class LDAP_Employee extends Employee
{
  function LDAP_Employee($id)
  {
    $this->Employee($id);
  }

  // Some more code to deal with the LDAP directory ...
  // ...
}

Now the problem we have, is that everywhere in our application, the instances are still created out of the original Employee class.

The trick is to use function variables to create the instances.

Let's say you have a global variable defined at the top of the application:

$GLOBALS['ClassDefinitions']['Employee'] = 'Employee'; // Name of the original class

Now, to instanciate an employee we just need to do:

$MyEmployee = new $GLOBALS['ClassDefinitions']['Employee']($MyId);

If we want to use the LDAP_Employee class, we just need to change the global once, high enough in the application initialization routine - where the extensions are loaded, basically:

$GLOBALS['ClassDefinitions']['Employee'] = 'LDAP_Employee'; // Now we'll use the overriden class everywhere

The rest of the code is left unchanged, but the behaviour of the whole application is now changed.

The only downside of this method, is that the overriden classes need to know exactly what class they extend (you can't have a variable after the extend keyword). So if you have a second extension that wanted to extends Employee, you're cooked. (you can, however, have a third extension that extends LDAP_Employee, of course)