Friday, April 28, 2006

Migrating triggers from SQLServer to MySQL

After a few posts on Devshed forums I decided to summarize some basic directions about porting SQLServer triggers to MySQL.
There are some notable differencies between trigger syntax in SQLServer and MySQL (and other databases in general).
First of all usually you reference old (preexisting) and new (inserted or updated) column values by NEW.column_name and OLD.column_name, but in SQLServer those values are held in two tables named INSERTED and DELETED, on which you'll have to perform selects.
Second relevant difference is that SQLServer doesn't have BEFORE triggers, you'll have to use a rather convoluted syntax for this.
Third difference is that one SQLServer trigger can fire upon multiple actions (one single trigger for insert/update/delete).
Fourth difference, as new/old values are held in a table there is no FOR EACH ROW implicit loop but you'll have to loop through the table values with a cursor (or process records in a block through a DML statement).
Here are two examples:

First is a SQLServer trigger that converts a string into uppercase before inserting.

  1. CREATE TRIGGER upper_all
  2. ON test_table
  3. instead of INSERT
  4. AS
  5. INSERT INTO test_table (a_field) SELECT UPPER(a_field) FROM INSERTED;

Note that the trigger will fire upon insert cancelling the original insert and doing an alternate insert which converts into uppercase the value of a_field column.
The same in MySQL would be:

  1. DELIMITER $$
  2. DROP TRIGGER test.upper_all$$
  3. CREATE TRIGGER upper_all before INSERT ON test_table
  4. FOR EACH ROW
  5. BEGIN
  6. SET NEW.a_field = UPPER(NEW.a_field);
  7. END$$
  8. DELIMITER ;

IMHO this syntax is simpler and clearer.
Another example:

  1. CREATE TRIGGER [mDBpush_Authors] ON [Authors] FOR INSERT, UPDATE,
  2. DELETE AS
  3. IF NOT EXISTS (SELECT * FROM INSERTED) -- "inserted" table is empty so must be delete trigger
  4. INSERT INTO mDBpkstub (TransTime,PK,LayerName,Type,STATUS)
  5. SELECT GetDate(),au_id,"Authors",3,0 FROM deleted
  6. ELSE IF NOT EXISTS (SELECT * FROM DELETED) -- "deleted" table is empty so must be insert trigger
  7. INSERT INTO mDBpkstub (TransTime,PK,LayerName,Type,STATUS)
  8. SELECT GetDate(),au_id,"Authors",2,0 FROM inserted
  9. ELSE -- both tables have entries so must be update trigger
  10. INSERT INTO mDBpkstub (TransTime,PK,LayerName,Type,STATUS)
  11. SELECT GetDate(),au_id,"Authors",1,0 FROM inserted

This trigger fires for inserts/updates and deletes thus has to check which of the three events is causing it to fire and act accordingly.
In MySQL you'll be required to create three triggers, each firing before action, an example (BEFORE UPDATE) is:

  1. DELIMITER $$
  2. DROP TRIGGER test.mDBpush_Authors$$
  3. CREATE TRIGGER mDBpush_Authors BEFORE UPDATE ON Authors
  4. FOR EACH ROW
  5. BEGIN
  6. SET NEW.TransTime = now();
  7. -- PK field (au_id) comes from the update statement, no need to change it
  8. SET NEW.LayerName = "Authors";
  9. SET NEW.Type = 1;
  10. SET NEW.STATUS = 0;
  11. END$$
  12. DELIMITER ;


Hope this helps!
More on this here and here.

Monday, April 24, 2006

An INFORMATION_SCHEMA for Firebird?

Excellent blog post by Lorenzo Alberton! Is INFORMATION_SCHEMA coming to Firebird? I hope so!

EDIT:
The author left out one important bit of info that you can retrieve from Firebird, a list of Stored Procedures, here is the sql query:

SELECT* FROM rdb$procedures; 

Thursday, April 20, 2006

MySQL full LOAD DATA INFILE example

Here is a more detailed example of LOAD DATA INFILE syntax:

First of all create a table to be loaded:

mysql> create table 2beloaded (field_1 char(1), field_2 int, field_3 char(1), fi
eld_4 varchar(50));
Query OK, 0 rows affected (0.15 sec)



Then the text file:

c, 1, a
b, 2, d


(named 2beloaded.txt and placed in c:\, so it's a file created on Windows platform!!)

Now the query to load it, we want to load the file setting proper line termination and all, but also to load field_4 of the table with the file name, here it is:

mysql> load data infile 'c:\\2beloaded.txt' into table 2beloaded fields terminat
ed by ',' optionally enclosed by '"' lines terminated by '\r\n' (field_1, field_2, field_3)
set field_4 = '2beloaded.txt';
Query OK, 2 rows affected (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0


Note that the file name is properly escaped, that the line is terminated by the usual windows sequence, that we are telling the server to load the first 3 fields of the table with data coming from the file and to load field_4 with a value provided by us (the file name).
Now check the result:

mysql> select * from 2beloaded;
+---------+---------+---------+---------------+
| field_1 | field_2 | field_3 | field_4 |
+---------+---------+---------+---------------+
| c | 1 | a | 2beloaded.txt |
| b | 2 | d | 2beloaded.txt |
+---------+---------+---------+---------------+
2 rows in set (0.01 sec)

Another example, with a file that has values separated by a space:

mysql> load data infile 'd:/file.txt' into table target
-> fields terminated by ' ' lines terminated by '\r\n'
-> (status, value)
-> set date_time = now();
Query OK, 3 rows affected (0.05 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from target;
+---------------------+--------+-------+
| date_time | status | value |
+---------------------+--------+-------+
| 2006-08-18 16:56:58 | a | 1 |
| 2006-08-18 16:56:58 | b | 2 |
| 2006-08-18 16:56:58 | c | 3 |
+---------------------+--------+-------+
3 rows in set (0.00 sec)

mysql>

Tuesday, April 18, 2006

MySQL triggers and Master/Detail tables

Have you ever wondered how to update the status of an order to "Closed" when the whole ordered quantity has been received?
If you are on a database that supports triggers like me (I'm on MySQL 5.0.16 right now) you can have something like:

Table structure:

Orders table

  1. DROP TABLE IF EXISTS `test`.`orders`;
  2. CREATE TABLE `test`.`orders` (
  3. `order_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  4. `order_date` datetime DEFAULT NULL,
  5. `order_status` CHAR(1) NOT NULL DEFAULT '',
  6. PRIMARY KEY (`order_id`)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Order lines table

  1. DROP TABLE IF EXISTS `test`.`order_lines`;
  2. CREATE TABLE `test`.`order_lines` (
  3. `order_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  4. `order_line_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  5. `item` varchar(45) NOT NULL DEFAULT '',
  6. `order_quantity` float NOT NULL DEFAULT '0',
  7. PRIMARY KEY (`order_line_id`,`order_id`),
  8. KEY `FK_order_lines_1` (`order_id`),
  9. CONSTRAINT `FK_order_lines_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`)
  10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Order deliveries table

  1. DROP TABLE IF EXISTS `test`.`order_schedule_lines`;
  2. CREATE TABLE `test`.`order_schedule_lines` (
  3. `order_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  4. `order_line_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  5. `schedule_line_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  6. `delivery_quantity` float NOT NULL DEFAULT '0',
  7. PRIMARY KEY (`order_id`,`order_line_id`,`schedule_line_id`),
  8. CONSTRAINT `FK_order_schedule_lines_1` FOREIGN KEY (`order_id`, `order_line_id`) REFERENCES `order_lines` (`order_id`, `order_line_id`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

You see, a lot of nice things, InnoDB tables, Foreign Keys ...
But now the real meat, a trigger that, after every insert in the "order_schedule_lines" table, where deliveries for each order line are kept, will compute the difference between ordered quantity and delivered quantity and will update the order status to closed when that difference is 0.

Trigger code:

  1. DELIMITER $$
  2. DROP TRIGGER test.close_order $$
  3. CREATE TRIGGER close_order
  4. AFTER INSERT
  5. ON order_schedule_lines
  6. FOR EACH ROW
  7. BEGIN
  8. DECLARE a float;
  9. SELECT
  10. (ol.order_quantity - COALESCE(SUM(osl.delivery_quantity), 0)) INTO a
  11. FROM orders o
  12. INNER JOIN order_lines ol ON o.order_id = ol.order_id
  13. LEFT OUTER JOIN order_schedule_lines osl ON ol.order_id = osl.order_id AND ol.order_line_id = osl.order_line_id
  14. WHERE osl.order_id = NEW.order_id
  15. GROUP BY
  16. o.order_id;
  17. IF a = 0 THEN
  18. UPDATE orders SET order_status = 'C' WHERE order_id = NEW.order_id;
  19. END IF;
  20. END $$
  21. DELIMITER ;

Of course it would be good to have another check (a before insert trigger? ;-)) that prevents further deliveries on closed orders ...

Partly inspired by this blog entry

Wednesday, April 12, 2006

Php PDO and Firebird

A quick snippet ...

  1. try {
  2. $dbh = new PDO("firebird:dbname=localhost:C:\\Programmi\\Firebird\\Firebird_2_0\\examples\\empbuild\\EMPLOYEE.FDB", "SYSDBA", "masterkey");
  3. foreach ($dbh->query('SELECT COUNTRY from COUNTRY') as $row) {
  4. print_r($row);
  5. }
  6. $dbh = null;
  7. } catch (PDOException $e) {
  8. print "Error!: " . $e->getMessage() . "
    "
    ;
  9. die();
  10. }
  11. ?>

Tuesday, April 11, 2006

Php Firebird (ehm Interbase) functions ...

A post on Devshed forums showed that the php manual did not have any detailed info about ibase_service_attach and some other Interbase/Firebird functions, of course the question has been answered in the forums and the user promised to add a note on the official docs, anyway here is a small example of use of that specific function:
...
// get server version and implementation strings
if (($service = ibase_service_attach('localhost', 'sysdba', 'masterkey')) != FALSE) {
$server_info = ibase_server_info($service, IBASE_SVC_SERVER_VERSION)
. ' / '
. ibase_server_info($service, IBASE_SVC_IMPLEMENTATION);
ibase_service_detach($service);
}
else {
$ib_error = ibase_errmsg();
}
echo $server_info;

...

Which echoes something like

WI-T2.0.0.12484 Firebird 2.0 Release Candidate 1 / Firebird/x86/Windows NT

A more useful example would be using the whole thing to create a new user, you'll have to attach to server as a user with appropriate privileges (like SYSDBA) and then issue an:

ibase_add_user($service, 'pippo', 'pp')


where $service is the resorce got from the ibase_service_attach, 'pippo' is the username and 'pp' is the password


Sample code taken and adapted from the ibWebAdmin source code.

Replicating from MsSQL Server to PostgreSQL

Very interesting post from Magnus Hagander, take a look at it.

Saturday, April 01, 2006

OpenLDAP Win32

Good news for OpenLDAP fans!!
Lucas Bergman with help from Matthias Mohr has put up a win32 build of OpenLDAP 2.2.29.
Thank you very much and keep up the good work.