Doctrine 1.2中用户定义的例程？

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

1. 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;

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

Sorry for my English. Tom

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:

1. Connect to MySQL
2. Send the SQL to create your function
3. 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.

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.