Another handy MySQL function

This time it's a quick function to validate email addresses, based on regexp.
It can be used in a trigger to add data validation, or to check data already in your database that needs a clean up ... really a simple wrapper around a simple regexp query, but it can be helpful.
Here it is:

DELIMITER $$
 
DROP FUNCTION IF EXISTS `test`.`is_valid_email` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `is_valid_email`(p_email varchar(64)) RETURNS tinyint(1)
BEGIN
CASE
WHEN NOT (SELECT p_email REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$')
THEN
-- bad data
RETURN FALSE;
ELSE
-- good email
RETURN TRUE;
END CASE;
END $$
 
DELIMITER ;

As per Mushu's comment, this is much cleaner, oops:

DELIMITER $$
 
DROP FUNCTION IF EXISTS `test`.`is_email_valid` $$
CREATE FUNCTION `test`.`is_email_valid` (p_email varchar(64)) RETURNS tinyint(1)
BEGIN
CASE
WHEN p_email REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$'
THEN
-- good email
RETURN TRUE;
ELSE
-- bad data
RETURN FALSE;
END CASE;
END $$
 
DELIMITER ; 

Also, if you can move such checks into the application code, it may be a wise thing to do as executing regular expressions can easly consume all CPU resources.

Of course doing checks in the application code is cleaner, but the ability to add them later at database level, or to run them in the database without interaction with external apps is also handy ;-)

Missing DLLs when starting GIMP 2.3.x

I've just downloaded and installed Gimp 2.3.14 for windows and noticed what seems to be a widespread problem, startup errors due to missing dlls. Of the many solutions listed here I found out that:
1. Changing the shortcut icon on the desktop (point 5 in the page linked)
2. Changing the shortcut icon in Start -> Programs -> GIMP -> Gimp 2


3. Adding a registry key (point 6 in the page linked)


solved my problem.

Hope this helps.

MySQL spanning different timezones

So what, if you are planning a centralized MySQL server and many clients all around the world?
First of all, check the manual
Then install timezone description tables from here
Then run a small example to see how the server and client timezones can interact.
Here is the standard server setup

mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+
1 row in set (0.00 sec)

Now add a table for our experiments

mysql> create table time_table (datetime_col datetime);
Query OK, 0 rows affected (0.11 sec)

Let's add some data in that table.
Note that I'm not setting any option for the client time_zone

mysql> insert into time_table (datetime_col) values (now());
Query OK, 1 row affected (0.05 sec)

mysql> select * from time_table;
+---------------------+
| datetime_col        |
+---------------------+
| 2007-02-11 13:57:00 |
+---------------------+
1 row in set (0.00 sec)

Ok, that's the current time (I'm connecting from the command line to a server running on the same PC), now I'll set the client timezone, on the same connection!

mysql> set time_zone = 'Europe/London';
Query OK, 0 rows affected (0.01 sec)

mysql> insert into time_table (datetime_col) values (now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from time_table;
+---------------------+
| datetime_col        |
+---------------------+
| 2007-02-11 13:57:00 |
| 2007-02-11 12:57:28 |
+---------------------+
2 rows in set (0.00 sec)

Hey I just set a timezone and voilà, time travelling ;)
Now I'm connecting from another client, no special timezone setting

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.33 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> insert into time_table (datetime_col) values (now());
Query OK, 1 row affected (0.02 sec)

mysql>

Back to the first connection:

mysql> select * from time_table;
+---------------------+
| datetime_col        |
+---------------------+
| 2007-02-11 13:57:00 |
| 2007-02-11 12:57:28 |
| 2007-02-11 13:58:32 |
+---------------------+
3 rows in set (0.00 sec)

So I really set the timezone for a specific connection!!
This is handy, but could bite, see

mysql> select * from time_table order by datetime_col desc;
+---------------------+
| datetime_col        |
+---------------------+
| 2007-02-11 13:58:32 |
| 2007-02-11 13:57:00 |
| 2007-02-11 12:57:28 |
+---------------------+
3 rows in set (0.02 sec)

Rows were NOT inserted in this order!
But when the remote client loads that row will shurely want to see date and time in his own terms.

I know of the CONVERT_TZ() function, but I don't see it useful in this case if a "datetime with timezone" or "timestamp with timezone" data type is not supported (PostgreSQL does support it), I know I could store in another column the timezone but it's not a really practical solution.
Can anyone shed any light on this?