• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
CI Migration that defines a MySQL function

#1
[eluser]Unknown[/eluser]
We are using database migrations for our project and I need to define a MySQL function as part of the migration. I am getting a syntax error when I try to change the delimiter as is common when defining a function in SQL. The error is:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$' at line 1"

I don't care how I have to do it, I just need this code to execute as a migration. Any suggestions are welcome.

Would it be possible to keep the SQL in some "migration.sql" file and use CI to tell MySQL to read from that file?

Here is the raw SQL from mysql_dump that needs to be executed:

Code:
DELIMITER $$
DROP FUNCTION IF EXISTS `default_workload`$$
CREATE FUNCTION `default_workload`(`uid` INT, `eid` INT) RETURNS int(11)
  READS SQL DATA
  BEGIN
  DECLARE n integer;
  SELECT U.id into n
  FROM default_workloads_users U
  INNER JOIN (SELECT workload_type
    FROM default_workloads_category
    WHERE exercise_category_id = get_parent_category(eid)
    ORDER BY id ) C ON U.type = C.workload_type
    where U.user_id=uid
    LIMIT 1;
  RETURN (n);
   END$$


Here is what my migration looks like (which gives me a syntax error on the DELIMITER line):


Code:
class Migration_Workloads_Changes extends CI_Migration
{
public function up()
{
  $this->db->trans_start();
  $SQL[] = ' DELIMITER $$';
  $SQL[] = ' DROP FUNCTION IF EXISTS `default_workload`$$ '
  $SQL[] =
<<<SQL
  CREATE FUNCTION `default_workload`(`uid` INT, `eid` INT) RETURNS int(11)
  READS SQL DATA
  BEGIN
  DECLARE n integer;
  SELECT U.id into n
  FROM default_workloads_users U
  INNER JOIN (SELECT workload_type
    FROM default_workloads_category
    WHERE exercise_category_id = get_parent_category(eid)
    ORDER BY id ) C ON U.type = C.workload_type
    where U.user_id=uid
    LIMIT 1;
  RETURN (n);
  END$$
SQL;
  
      foreach($SQL as $query) {
       $this->db->query($query);
      }
  $this->db->trans_complete();
}
}

#2
[eluser]Unknown[/eluser]
Got it, just had to get rid of the DELIMITER lines altogether. Apparently the mysqli driver is ok defining stored procedures with lots of ';'s everywhere...


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.