Being a mod means

This time I turned the kind gift of the Devshed guys into:

"Financial Intelligence" an interesting book which I hope will help in my ever evolving job, I really need to understand what financial people are saying and what they really mean.

This one looks promising, it's a side of my professional life that I've never really dug into, but it's very interesting (and somehow fun) .

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?

Powerline, the good, the bad and the ugly ...

After much esitance I decided to jump on the bandwagon of Powerline (a.k.a. Homeplug), that's it, ethernet over power!
At first it sounds great, I mean, no cabling, no pesky radiations going round like with WiFi and so on ... an easy way of sharing internet connection for my 2 PCs at home.
I tested WiFi 802.11b before and wasn't impressed with it's performance, so I reached my purse and got a pair of Powerline adaptors, plugged them in and ... voilà, instant networking [the good].
This looked extremely good, easier than WiFi, but after some usage I noticed that it was a quite slow connection, so, unfortunately, I took a peak at the manual and discovered that max theoretical (including channel control data) speed is 85Mbit/s, while effective maximum network speed can't exceed 25Mbit/s :-( [the bad (1)] and that you need to install more software/drivers in order to add security to your network, much like WEP for WiFi, which adds to complication and is likely to subtract network speed [the bad (2)].
Network speed was unimpressive, so I run a quick test with JGaa's NetCPS and here are the results for you to read, as seen from both sides

C:\>netcps -s

NetCPS 1.0 - Entering server mode. Press ^C to quit

Waiting for new connection...

Client connected from 192.168.1.9

---> CPS   1519616.00  KPS:  1484.00  MPS: 1.45

Avrg CPS   1515546.00  KPS:  1480.03  MPS: 1.45

Peek CPS   1542144.00  KPS:  1506.00  MPS: 1.47

Client disconnected. 104857600 Kb transferred in 69.19 seconds.

C:\>netcps 192.168.1.5

NetCPS 1.0 - Entering client mode. Press ^C to quit

Connecting to 192.168.1.5 port 4455... Connected!

---> CPS   1518097.88   KPS:  1482.52  MPS: 1.45

Avrg CPS   1515524.13  KPS:  1480.00  MPS: 1.45

Peek CPS   1553373.25  KPS:  1516.97  MPS: 1.48

Done. 104857600 Kb transferred in 69.19 seconds.

I'd not say very fast ...
You read about [the good], [the bad] and here is [the ugly]



(when I'll be able to retrieve a photo from my mobile) Ok, I've made it

the plug is very light, but quite big, or at least bigger than what I expected.
After all this could seem a bashing post, so I'm adding the results of the same test under 802.11b

C:\>netcps -s

NetCPS 1.0 - Entering server mode. Press ^C to quit

Waiting for new connection...

Client connected from 192.168.1.10

---> CPS    495616.00  KPS:   484.00  MPS: 0.47

Avrg CPS    487532.91  KPS:   476.11  MPS: 0.46

Peek CPS    526336.00  KPS:   514.00  MPS: 0.50

Client disconnected. 104857600 Kb transferred in 215.08 seconds.

As you can see homeplug is much faster than 802.11b, don't have a more recent 802.11x device to compare :-(

Signs of MySQL usage at BBC.CO.UK

A very interesting document from BBC's Standards and Guidelines (Database Design and Development Standards v1.00) mentions only two databases, guess what? Oracle and MySQL!!!
BTW the document is also a source of good design tips like:

Normally, use only alphanumeric characters plus the underscore (_) character for table names.

MySQL
Table names are case sensitive under UNIX (or Linux). To avoid confusion, define/declare table names in upper case in any database creation SQL scripts.

No SQL language reserved words should be used as column names

Identify primary, unique, and foreign keys (to enforce important or critical data integrity) as part of the initial design process

To guard against slow queries degrading performance on the shared database infrastructure, the most commonly performed SQL queries should be analysed with an explain plan. This should use sufficient data to generate representative query execution plans

Indexes should be used where appropriate, use explain/explain_plan to demonstrate

All database APIs must define error handling, and success/failure notification

Designs should be as RDBMS agnostic as possible: for example, minimize use of sets and abstract data types, unless these are key to the design. It is accepted that that there will inevitably be some RDBMS specific data typing

MySQL offers a number of storage types. The only ones that should be used are:
MyIsam – for general (especially read only) data.
InnoDB – for transactional data, or data with important referential integrity.

Any database data loading processes MUST create a summary log, an error file (in the event of any errors, and a bad file for records that failed to load.)

This reminds me of a feature request I made for a bad file option for LOAD DATA (bug 11480)

Database users should only be granted sufficient privileges to enable them to perform the tasks that they need to

... and many more.

I think that such a document is of general interest and very useful expecially to newbies.

Playing with transactions on MySQL

Here is my attempt, failed as per the video quality, at showing a small example of transactions usage on MySQL 5.

Falcon, the new MySQL Storage Engine

While taking a quick look at the newly released docs for Falcon, the new storage engine for the MySQL database from MySQL AB (and this is important, as actually InnoDB and the now ditched BDB storage engines for MySQL database are from Oracle) I noticed two "strange" things, at least to someone thinking of large databases running on dedicated hardware.
First of all the data storage, chapter 14.6.5.1. Falcon data file and data structures reads:

A single Falcon database file stores all record data, indexes, database structure and other information. ...

This doesn't sound like a good idea for me as it's better to keep indexes and data separated on different disks and something like Oracle's implementation of tablespaces and the ability to assign indexes and data to different tablespaces hosted on different disks is important (I noticed that in some cases up to 40% of the size of my datawarehouse is made up of indexes).

Then data compression, chapter 14.6.5.6. Data Compression reads:

Data stored in the Falcon tablespace is compressed on disk, but is stored in an uncompressed format in memory. Compression occurs automatically when data is committed to disk.

won't this compress/uncompress loop badly affect bulk load speed and concurrency (forced writes ...)?
Hope the engine will be made available in binaries quickly and proper benchmarks will clear any doubt.

Creating an auto incrementing column in Firebird

or in any database that supports triggers and generators (aka sequences) ...
The answer is well known however the question comes out quite often, be it in an Oracle, Firebird or PostgreSQL forum.
Well, the PostgreSQL guys took a radical approach and embedded the common solution into a datatype named SERIAL, what follows is an example aimed at the Firebird newbie.

Say you have a table like this

CREATE TABLE new_one
(
col1 integer NOT NULL,
col2 CHAR(10),
PRIMARY KEY (col1)
); 

You want col1 to behave like an autoincrement/identity field.
You create a generator

CREATE GENERATOR my_gen_id; 

(you could set more options).
Now you want it to kick in automatically, just like an autoincrement, no need to explictly call it,
you can achieve this through a trigger:

CREATE TRIGGER autoincrementor_id FOR new_one
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.col1 IS NULL) THEN
NEW.col1 = GEN_ID(my_gen_id,1);
END 

And as shammat pointed out here "Basically the same technique as with Oracle's sequences".

To retrieve the current value after an insert (if you aren't using Firebird 2.1 RETURNING clause):

SELECT GEN_ID( MY_GEN_ID, 0 ) FROM RDB$DATABASE;

Addendum:
Sometimes people might want to empty the table and reset the "autoincrement" id (a questionable approach IMHO if that value has any kind of meaning).
This can be accomplished by a simple:

SET GENERATOR MY_GEN_ID TO 1; 

Where 1 is the chosen restart value.
NOTE that it will be the current value so your trigger will start with 2, you might want to set it to 0 instead of 1.

Addendum 2:
Firebird 2 introduced a new (and better) synthax fully documented in the Firebird Generator Guide.

Addendum 3:
Firebird 3.0, thanks to Adriano dos Santos Fernandes will support IDENTITY columns, so no need to use this trick anymore.

An Opensource approach to engineering

I love this guys!

Image extracted with GIMP and it's excellent SIOX tool

How to use BLOB field in VB 6 with Firebird

As per this post on Devshed forums I'm pasting here some sample code (taken from Mike Hillyer's excellent "Accessing MySQL BLOB columns using Visual Basic 6"), hope it helps.
Code uses a simpler table structure and due to the fact that I didn't bother about reproducing the "autoincrement" field it will get you into troubles if trying to run the sample multiple times.
You'll notice that basically the only different thing is the connection string!

'CREATE CONNECTION OBJECT AND ASSIGN CONNECTION STRING
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
 
conn.ConnectionString = "DRIVER={Firebird/Interbase(r) Driver};DBNAME=localhost:C:\Programmi\Firebird\Firebird_2_0\examples\empbuild\test.fdb;UID=SYSDBA;PW  D=masterkey"
 
conn.CursorLocation = adUseClient
conn.Open
 
'OPEN RECORDSET FOR WRITING
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
 
Dim mystream As ADODB.Stream
Set mystream = New ADODB.Stream
 
mystream.Type = adTypeBinary
 
rs.Open "SELECT * FROM files WHERE 1=0", conn, adOpenStatic, adLockOptimistic
 
rs.AddNew
 
mystream.Open
mystream.LoadFromFile "c:\adaptor.jpg"
 
rs!file_id = 1
rs!file_name = "adaptor.jpg"
rs!file_size = mystream.Size
rs!file_blob = mystream.Read
rs.Update
mystream.Close
rs.Close
 
'OPEN RECORDSET TO READ BLOB
rs.Open "Select * from files WHERE files.file_id = 1", conn
mystream.Open
mystream.Write rs!file_blob
mystream.SaveToFile "c:\newimage.jpg", adSaveCreateOverWrite
mystream.Close
rs.Close
 
'OPEN RECORDSET FOR UPDATE OF BLOB COLUMN
rs.Open "Select * from files WHERE files.file_id = 1", conn, adOpenStatic, adLockOptimistic
mystream.Open
mystream.LoadFromFile "c:\adaptor.jpg"
rs!file_blob = mystream.Read
rs.Update
 
mystream.Close
rs.Close
 
'OPEN RECORDSET TO READ UPDATED IMAGE
rs.Open "Select * from files WHERE files.file_id = 1", conn
mystream.Open
mystream.Write rs!file_blob
mystream.SaveToFile "c:\newupdatedimage.jpg", adSaveCreateOverWrite
 
mystream.Close
rs.Close
 
conn.Close
 
MsgBox "Success! Check your C:\ directory for newimage.jpg and newupdatedimage.jpg" 

Custom ordering for your results

... or, taming the ORDER BY clause.
Say you want to implement a custom ordering for your queries, as an example, you want to display each customer's orders with shipped orders first, then waiting orders and open orders last.
That's ordering by the 'status' column and you can't use alphabetical ordering!
In this case you'll have to implement some kind of logic in your order by clause, and a CASE is very handy, like this:

SELECT * FROM SALES
ORDER BY 
cust_no,
CASE WHEN order_status = 'shipped' THEN 1
WHEN order_status = 'waiting' THEN 2
WHEN order_status = 'open' THEN 3
ELSE 4
END; 

Note how the ordering is performed on the values substituted by the CASE statement and not
for the original column values.
This example is based on Firebird's EXAMPLE.FDB database.

The MySQL guys could also use a trick like the FIELD function

... ORDER BY FIELD(order_status,'shipped','waiting','open')

but this is a NON standard way of doing things that I don't recommend at all.

Of correlated and uncorrelated subqueries

Many SQL questions on Devshed forums are easily solved using correlated and uncorrelated subqueries, but many don't know of them at all, or at least are not shure about the syntax, usage and performance implications, they sometimes rave about a stored procedure to loop and select other values ...
The definition (taken from O'Reilly's "Learning SQL on SQL Server 2005") of a correlated subquery:
A correlated subquery is an inner subquery whose information is referenced by the main, outer query such that the inner query may be thought of as being executed repeatedly.
And the definition of an uncorrelated subquery:
A noncorrelated subquery is a subquery that is independent of the outer query. In other words, the subquery could be executed on its own.
This is all fine and dandy, but what's the use?
I'll put up two simple examples of usage, which should make clear which kind of questions can be answered with (un)correlated subqueries:

Example 1: get the number of "open" orders, the number of "waiting" orders and the open vs. waiting ratio

SELECT 
COUNT(*) AS o_open, 
(SELECT COUNT(*) FROM sales WHERE order_status = 'waiting') AS o_waiting,
(COUNT(*))/ 
(SELECT COUNT(*) FROM sales WHERE order_status = 'waiting') AS open_waiting_ratio
FROM SALES WHERE order_status = 'open'; 

You can see that the queries are uncorrelated, and they better be, as the where conditions are different.

Example 2: we want to see the customer number, the amount of the customer's last order and the total amount ordered by each customer

SELECT 
s.cust_no,
s.total_value AS order_value,
(SELECT SUM(i_s.total_value) FROM sales i_s WHERE i_s.cust_no = s.cust_no) total_ordered
FROM sales s
WHERE
s.order_date =
(SELECT MAX(ii_s.order_date) FROM sales ii_s WHERE ii_s.cust_no = s.cust_no); 

You can see that both subqueries are correlated, the first one gets total amount ordered for each customer, the second one gets the last order date (field is datetime) and helps determine the last order in the main query.

This should make you aware that some kind of set based logic is often disguised as procedural and usually leads to ugly cursor usage in stored procedures instead of direct queries by the inexperienced.

Another example of a correlated subquery to display customers and one randomly selected purchase order for each of them:

SELECT 
 a.CUST_NO, 
 a.CUSTOMER,
 b.PO_NUMBER
FROM 
 CUSTOMER a
  INNER JOIN
   sales b
    ON a.CUST_NO = b.CUST_NO
WHERE 
 b.PO_NUMBER = 
(
  SELECT 
   first 1 c.PO_NUMBER 
  FROM 
   sales c 
  WHERE 
   c.CUST_NO = b.CUST_NO 
  ORDER BY 
   rand()
) 

Both examples are run against Firebird's standard EMPLOYEE.FDB.


Correlated subqueries are also useful to specify updates and deletes on a table conditioned to values of another table (instead of joining them) see this example:

UPDATE 
 stops s
SET 
 s.inservice=1 
WHERE EXISTS
(
 SELECT
  ls.stopid
 FROM 
  linestop ls
 WHERE
  s.stopid=ls.stopid 
 AND 
  ls.signid=79
); 

Firebird's explain PLAN

After a post on Devshed forums I'm putting here an excerpt from a little tutorial about Firebird I wrote some time ago, this is about the explain plan of a query:

More on speeding … the Explain plan

An extremely useful feature of relational databases is the “explain plan”,which
shows us how the database engine is going to execute a query. It allows the DBA to
check if indexes are used and how tables are queried. One of the most effective ways of
using it is to use the Firebird option “PLANONLY” which means that the query to be
analyzed is submitted to the server and the plan retrieved, but the query is not executed!

Note that this is also an effective way of checking the SQL syntax of a statement, much
like Oracle’s parse statement.

The planonly option can be set to ON or OFF at your option, remember to turn it
off to obtain results for your queries.

The plan is accessed through ISQL this way:

>isql
SQL> CONNECT “C:\firedata\first_db.fdb” user ‘SYSDBA’ password ‘guess_me’;
…
SQL> SET PLANONLY ON;
SQL> SELECT a.emp_no, b.currency from employee a inner join country b on
a.job_country = b.country;

After submitting the query we retrieve the plan, which shows usage of the primary key of
table country:

PLAN JOIN (A NATURAL,B INDEX (RDB$PRIMARY1))
SQL> SET PLANONLY OFF;

This way we can check the execution of more complex queries and find areas of
improvement

You can see an example of it's usage here.

Audacity 1.3.2 is coming

See release notes for Audacity 1.3.2

Handy MySQL function

Here is a simple function that can help coping with wrong database design, when you have a mix of NULLs and blank fields meaning the same, no value, which they shouldn't.

DELIMITER $$
 
DROP FUNCTION IF EXISTS `test`.`is_initial` $$
CREATE FUNCTION `test`.`is_initial` (f varchar(255)) RETURNS BOOL
BEGIN
 
 
SET @is_initial = false;
 
CASE f
WHEN NULL THEN SET @is_initial = TRUE;
WHEN '' THEN SET @is_initial = TRUE;
ELSE SET @is_initial = FALSE;
END CASE;
 
RETURN @is_initial;
 
 
END $$
 
DELIMITER ; 

Something you'll need for shure in Poland

Well, if you don't want your laptop to lie dead soon the first thing to buy when in Poland is an adaptor like this from DPM Elektronik.

Php and SOAP, getting to the corporate business intelligence repository

What follows is a snippet taken from a very old experiment I did, integrating a php application with BusinessObject's WebIntelligence 2.7.x through it's (very experimental) webservices toolkit, which was really promising but Java and .NET oriented ;)

// include the class
include("nusoap.php");
 
// create a instance of the SOAP client object
 
// remember that this script is the client,
// accessing the web service provided by BusinessObjects
 
$soapclient = new soapclient("http://192.168.1.99:8080/wiws/services/wiws.businessobjects.com");
 
// uncomment the next line to see debug messages
// $soapclient->debug_flag = 1;
 
// set up an array containing input parameters to be
// passed to the remote procedure
$params = array(
     'userName' => 'Administrator',                    // username
     'password'   => '',                        // password
     'deltaUTC' => 0,                             // utc delta
);
 
// invoke the method on the server
$result = $soapclient->call("login", $params); 
 
// print the session ID
print_r($result);
 
// show corporate documents
//$params3 = array(
//  'session' => $result,
//  'listType' => 'corporate',
//  'category' => '',
//  'sortOn' => 0,
//  'refresh' => 1,
//);
 
//$corporate = $soapclient->call("getDocumentList", $params3);
 
//    if (is_array($corporate['resultElements']))
//    {
//      foreach ($corporate['resultElements'] as $r)
//      {
//        echo "
" .
//$r['title'] . "";
//        echo "
";
//        echo $r['snippet'] . "(" .
//$r['cachedSize'] . ")";
//        echo "
";
//      }
//    }
 
 
// logout
$params2 = array(
     'session' => $result,                          // sessionid
);
 
// invoke the method on the server
$result2 = $soapclient->call("logout", $params2); 
 
// print the results of the search
print_r($result2);
?>