In the comments around the last post here on Malt Blue, why Kohana is an excellent alternative to the Zend Framework, which received quite a bit of attention socially as well, there was a bit of attention focused on the database layer.
Specifically:
Lubos said:
Very similar to Kohana is Yii, however it offers significantly more robust functionalities - ORM based on PDO supports 5 RDBMS, web2 widgets based on JQuery UI, built-in support for integration with 3rd party libraries.
In the comments around the last post here on Malt Blue, why Kohana is an excellent alternative to the Zend Framework, which received quite a bit of attention socially as well, there was a bit of attention focused on the database layer.
Specifically:
Lubos said:
Very similar to Kohana is Yii, however it offers significantly more robust functionalities - ORM based on PDO supports 5 RDBMS, web2 widgets based on JQuery UI, built-in support for integration with 3rd party libraries.
and hussainweb said:
I have never noticed issues with the ORM (I almost always use MySQL) and as far as widgets are concerned, I have written a little abstraction over views to make it easier to handle such scenarios.
Also, in the tweets around it, @aguimaraes1986 asked about some posts on alternative databases, especially PostgreSQL. So given that, I thought that this post would start to introduce a bit of a database thread to the blog.
So in this week’s post, we’re going to have a look at how a simple feature in PostgreSQL can reduce your development time.
Check Constraints
Specifically, we’re going to be looking at check constraints. Despite the plethora of alternative databases to MySQL and despite the mixed opinion about where it is going, after the acquisition by Oracle in January 2010, it’s still used with an enormous amount of PHP-related software, by default; whether it’s Joomla, Typo3, WordPress, phpBB, Drupal or a wide selection of others.
Now this isn’t a shot at MySQL. But in this writers humble opinion, despite all the great features of it, PostgreSQL is superior. Why? Well, without wanting to get in to a flame war over this, it’s a more rounded product that allows us as developers to avoid re-inventing the wheel repeatedly.
But what are check constraints? To quote the PostgreSQL (8.4) documentation check constraints:
allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression
In other words, you have the ability to ensure that the value stored in a table column is within an acceptable range. For example:
- If the column stores a person’s gender, then the value entered is ’M’ or ‘F’
- If the column stores a yes/no value, then the value stored is either ‘Y’ or ‘F’
- That a price is less than a set value, within a range of values or greater than a set value
- If you’re storing database choice, that the range is within the options you provide
Admittedly, these are simple examples. But you can see how it’s analogous to a MySQL ENUM type on steroids (if you will) - much more flexible and accommodating.
How Can We Use Them?
The best thing about it, is that you already can. You don’t need to look for a special function in either the PHP PostgreSQL or PDO functions. With either command-line or GUI access to a running PostgreSQL database, you can start today!. Let’s go through how with a simple example.
Creating Check Constraints
What we’re going to do is create a table that stores users, only if they’re over 18 years of age - say one to ensure that we don’t sell alcohol and cigarettes to minors (using the Australian laws).
We’ll create a table that simply stores 4 criteria about a user:
- first - character varying(150)
- last - character varying(150)
- emailaddress - character varying(200)
- dateOfBirth - date
Based on the dateOfBirth column, we’ll create a basic check constraint that ensures that we can only enter a user if the year of their date of birth is 18 years before the current year. Word to the wise, this isn’t a perfect solution - care to comment why?
The basic table structure is as follows:
1
2
3
4
5
6
7
8
9
10
11
|
<td class="code">
<pre class="php" style="font-family:monospace;">mbtest<span style="color: #339933;">=></span> \d tbl_users<span style="color: #339933;">;</span>
Table <span style="color: #0000ff;">"public.tbl_users"</span>
Column | Type | Modifiers
————–+————————+———–
id | bigint | not null
first | character varying(150) | not null
last | character varying(150) |
emailaddress | character varying(200) |
dateOfBirth | date | not null
Indexes:
“tbl_users_pkey” PRIMARY KEY, btree (id)
Here in our database, mbtest, we’ve created a table called tbl_users. You can see the schema above. Now we’re going to create a check constraint to enforce our business logic. Have a look at the statement below:
1
2
3
4
5
6
7
|
<td class="code">
<pre class="php" style="font-family:monospace;">mbtest<span style="color: #339933;">=></span> alter table tbl_users add constraint is_over_18 check <span style="color: #009900;">(</span>
<span style="color: #009900;">(</span>
DATE_PART<span style="color: #009900;">(</span><span style="color: #0000ff;">'year'</span><span style="color: #339933;">,</span> <span style="color: #0000ff;">"dateOfBirth"</span><span style="color: #339933;">::</span><span style="color: #004000;">timestamp</span><span style="color: #009900;">)</span> <span style="color: #339933;"><</span>
DATE_PART<span style="color: #009900;">(</span><span style="color: #0000ff;">'year'</span><span style="color: #339933;">,</span> now<span style="color: #009900;">(</span><span style="color: #009900;">)</span> <span style="color: #339933;">-</span> interval <span style="color: #0000ff;">'18 years'</span><span style="color: #009900;">)</span>
<span style="color: #009900;">)</span>
);
ALTER TABLE
Allowing for formatting for readability, what it does is:
- Cast the value being entered for the dateOfBirth column and extract the year component
- Compare that year to the year 18 years before the current one
If the users date of birth year is less than that 18 years ago, then we accept the value, creating the record. Otherwise, we reject it with an error, such as the one below:
1
|
<td class="code">
<pre class="php" style="font-family:monospace;">ERROR<span style="color: #339933;">:</span> <span style="color: #000000; font-weight: bold;">new</span> row <span style="color: #b1b100;">for</span> relation <span style="color: #0000ff;">"tbl_users"</span> violates check constraint <span style="color: #0000ff;">"is_over_18"</span></pre>
</td>
</tr>
After we’ve altered our table, we’ll have a schema that looks like below:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
<td class="code">
<pre class="php" style="font-family:monospace;">mbtest<span style="color: #339933;">=></span> \d tbl_users<span style="color: #339933;">;</span>
Table <span style="color: #0000ff;">"public.tbl_users"</span>
Column | Type | Modifiers
————–+————————+———–
id | bigint | not null
first | character varying(150) | not null
last | character varying(150) |
emailaddress | character varying(200) |
dateOfBirth | date | not null
Indexes:
“tbl_users_pkey” PRIMARY KEY, btree (id)
Check constraints:
“is_over_18” CHECK (
date_part(‘year’::text, “dateOfBirth”::timestamp without time zone) <
date_part(‘year’::text, now() - ‘18 years’::interval)
)
In its simplest form, that’s it. A bit of logic in the constraint and away we go.
Less Code Required
Now, we could write a filter/validator in our code, such as using a combination of PHP DATETIME functions and the Zend_Validate::GreaterThan validator. But well, it’s going to be quicker in the database.
As I said, is a rather simple example. You can do a hell of a lot more if you set your imagination to the task. But I hope that this, brief, post today has at least given you some inspiration about what you can do with PostgreSQL’s check constraints to lessen the development load.
Links
If you’d like to read more about today’s post, check out these excellent links for further reading:
Join the discussion
comments powered by Disqus