User-defined routine in Doctrine 1.2?

Question

I have to write an user-defined routine for my MySQL database (calculating distance function).

Is it possible to define it in yaml schema file?

After defining routine in terminal mysql-client everything is ok until 'doctrine build-all-reload' - function is dropped, which is understandable;

How can I attach a sql script which will be executed everytime I run 'build-all-reload'?

Sorry for my English. Tom

score 1 · Answer 1 · answered Jul 19 '11 at 20:52

I found a solution (similar to yours in some way) by adding following lines to my doctrine.php file:

$q = file_get_contents('../configs/sql/routines.sql');
$conn = Doctrine_Manager::connection();
$conn->execute($q);

Now, everytime I execute ./doctrine build-all-reload a following script is executed:

DROP FUNCTION IF EXISTS DIST;
CREATE FUNCTION DIST (fi11 DOUBLE, ksi11 DOUBLE, fi22 DOUBLE, ksi22 DOUBLE)
  RETURNS DOUBLE
   DETERMINISTIC
    BEGIN
     DECLARE d DOUBLE;
     DECLARE fi1 DOUBLE;
     DECLARE fi2 DOUBLE;
     DECLARE ksi1 DOUBLE;
     DECLARE ksi2 DOUBLE;
     SET fi1 = PI()*(fi11)/180;
     SET fi2 = PI()*(fi22)/180;
     SET ksi1 = PI()*(ksi11)/180;
     SET ksi2 =PI()*(ksi22)/180;
     SET d = ACOS((SIN(fi1)*SIN(fi2))+(COS(fi1)*COS(fi2)*COS((ksi1-ksi2))))*6371.0;
     RETURN d;
    END;

This solution is maybe not elegant, but works for me:) I am not planning to change database from MySQL.

After that i can use DIST function in Doctrine Queries and it works several times faster than using standard build-in-function implementation, and is much shorter.

$q->where('DIST(a.lan, a.lon, b.lan, b.lon) < ?', array(2.0));

istead of

$q->where('ACOS((SIN(...)... ... wrrrr ;-P ))*6371.0 < ?', array(2.0));

Thanks for your help.

Did you simply add the execute($q) at the end of doctrine.php? The problem is if you use your doctrine.php for any command other than "build-all-reload". But you can of course make a dedicated doctrine.php for just build-all-reload, in which case I like your solution :-) — Darren Cook, Jul 26 '11 at 00:37

score 0 · Answer 2 · answered Jul 19 '11 at 00:18

I think this can be done with Doctrine behaviours.

Following my own blog post http://darrendev.blogspot.com/2010/03/creating-doctrine-custom-behaviour-part.html, but using setUp() instead of setTableDefinition(), you create a file with this code:

class DarrenTestable extends Doctrine_Template{
    public function setUp(){
        ...
    }
}

The ... is where you can influence table creation. You attach this behaviour in the yaml file with:

ActsAs:
    DarrenTestable

There may be a neat doctrine-way to do what you need in the "..." part, but after poking around the source code a bit I'm not sure and so, as this is only done once in the lifetime of your application, I'd personally do it all outside Doctrine:

Connect to MySQL
Send the SQL to create your function
Disconnect

P.S. Another approach is to write your distance-calculating function in PHP as a doctrine behaviour (extend Doctrine_Record_Listener); as a bonus you get database portability. But it ties the code to Doctrine, so other applications cannot use the function the way they can with an mysql routine.

User-defined routine in Doctrine 1.2?

2 Answers2