Creating a PostgreSQL and MySQL driver
By Tony Marston2005-09-20
As well as differences in the syntax for creating tables there is also a difference in the syntax of various SQL queries.
CONCAT
The following in MySQL:
SELECT CONCAT(first_name, ' ', last_name) AS person_name FROM ....
has to be converted to the following in PostgreSQL:
SELECT first_name || last_name AS person_name FROM ....
I can deal with this difference by including a new function in the PostgreSQL class to perform the necessary conversion, thus enabling me to leave the original MySQL syntax intact.
JOIN
The following query produces ERROR: JOIN/ON clause refers to "a", which is not part of JOIN.
SELECT a.a_id, b.b_id, b.b_desc, x.a_id
FROM a, b
LEFT JOIN x ON (x.b_id = b.b_id AND x.a_id = a.a_id)
WHERE (a.a_id = 'whatever')
This was cured by changing it to the following:
SELECT a.a_id, b.b_id, b.b_desc, x.a_id
FROM a, b
LEFT JOIN x ON (x.b_id = b.b_id AND x.a_id = 'whatever')
WHERE (a.a_id = 'whatever')
This error seems rather dubious to me as it may not be possible to have every part of the ON clause limited to the two tables 'b' and 'x'. Provided that at least one JOIN condition specifies those two tables then any other JOIN condition should not be so limited. In the above example I was able to get around the problem by specifying a literal instead of a column name, but this may not be possible in all circumstances.
In another script I also found that changing the order of the table names in the FROM clause produced the same error. Thus FROM b,a fails while FROM a,b works. MySQL would appear to be more intelligent as it uses the ON conditions to work out how to read the joined table rather than insisting that any table name in the ON condition is either the table immediately following the JOIN clause or the table immediately preceding the JOIN clause. This means, for example, that using PostgreSQL I would not be unable to perform a JOIN on anything other than the last table specified in the FROM clause. The following code would therefore be invalid in PostgreSQL:
SELECT a.a_id, b.b_id, b.b_desc, x.a_id, z.z_desc
FROM a, b
LEFT JOIN z ON (z.z_id = a.z_id)
LEFT JOIN x ON (x.b_id = b.b_id AND x.a_id = 'whatever')
WHERE (a.a_id = 'whatever')
However, this same code DOES work in MySQL, so it's a simple matter to convert all JOIN statements so they work with both databases.
GROUP BY
The following query produces ERROR: column "a.a_desc" must appear in the GROUP BY clause or be used in an aggregate function.
SELECT a.a_id, a.a_desc, COUNT(b.a_id) AS child_count
FROM a
LEFT JOIN b ON (b.a_id=a.a_id)
GROUP BY a.a_id
This is perfectly valid in MySQL as it does not insist that the GROUP BY clause contains the names of ALL selected columns. This gives performance advantages for common queries. This seems reasonable to me as on table 'a' the column 'a_id' is the primary key while 'a_desc' is merely a textual description and therefore irrelevant as far as any grouping is concerned.
PostgreSQL is not so forgiving, therefore every column in the SELECT clause must also appear in the GROUP BY clause, as in the following:
SELECT a.a_id, a.a_desc, COUNT(b.a_id) AS b_count
FROM a
LEFT JOIN b ON (b.a_id=a.a_id)
GROUP BY a.a_id, a.a_desc
Just as with the JOIN the solution is to amend the SQL statement until it works with both databases.
INSERT
In standard SQL there are two forms of the INSERT command:
INSERT INTO tablename VALUES ('value1','value2',...)
INSERT INTO tablename (col1,col2,...) VALUES ('value1','value2',...)
What I like about MySQL is that it also allows UPDATE-style syntax, as follows:
UPDATE tablename SET col1='value1', col2='value2', ...
INSERT INTO tablename SET col1='value1', col2='value2', ...
I prefer this approach as it keeps column names and values together instead of in two separate lists. This seems such a common sense idea that I'm surprised it is not part of the standard already. Why allow 2 formats for INSERT and a completely different 3rd one for UPDATE?
Even though PostgreSQL is not so accommodating, the fact in my infrastructure all QUERY statements are constructed and issued within the relevant database driver means that I can customise each driver according to the whims of the individual database. The remainder of my infrastructure remains totally oblivious to the internal workings of any individual driver.
AUTO_INCREMENT
MySQL makes it very easy to use a technical key in any table. All the DBA need do is include the AUTO_INCREMENT keyword in the table definition, as in the following:
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL auto_increment,
`desc` varchar(40) default NULL,
PRIMARY KEY (`id`)
);
Although the term AUTO_INCREMENT does not exist in PostgreSQL, it has the equivalent in SERIAL, as in the following:
CREATE TABLE test (
id serial NOT NULL,
desc varchar(40),
PRIMARY KEY (id)
);
The only other difference is the method used to obtain the last number issued. In MySQL there is a simple function:
$id = mysql_insert_id($link);
With PostgreSQL the same can be achieved with:
$query = "SELECT currval('" .$tablename .'_' .$pkey ."_seq')";
$result = pg_query($link, $query) or trigger_error($this, E_USER_ERROR);
$id = pg_fetch_result($result, 0, 0);
This is because the SERIAL keyword makes use of a counter with the default name of <tablename>_<fieldname>_seq. This can be accessed using the currval() and nextval() functions.
Tutorial pages:
|
|
|||||||||
You might also want to check these out:
|
Link to This Tutorial Page!
