Hello I'm trying to create a stored procedure using Laravel 5.1 migrations. So far I tried with DB::connection()->getPdo()->exec(), DB::raw() and DB::unprepared(), but no luck. Here's how my code looks like:
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
use Wryttnapp\Models\User;
class AddCentroidFieldsToUsersTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
DB::connection()->getPdo()->exec("
ALTER TABLE `users`
ADD COLUMN centroid POINT NULL
AFTER `avatar`;");
DB::unprepared("SET @OLD_SQL_MODE=@@SQL_MODE;
DELIMITER ;;
DROP PROCEDURE IF EXISTS UPDATE_USER_CENTROID;;
CREATE PROCEDURE UPDATE_USER_CENTROID(userId INT)
NOT DETERMINISTIC
BEGIN
DECLARE bDone INT;
DECLARE tempLatLon VARCHAR(100);
DECLARE counter INT;
DECLARE firstLatLon VARCHAR(100);
DECLARE polygonDefinition TEXT;
DECLARE geometry POINT;
DECLARE curs CURSOR FOR SELECT DISTINCT CONCAT(`latitude`, ' ', `longitude`) FROM `user_locations` WHERE `user_id` = userId ORDER BY `id` ASC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
OPEN curs;
SET bDone = 0;
SET counter = 0;
SET polygonDefinition = ',';
FETCH curs INTO tempLatLon;
WHILE NOT bDone DO
SET polygonDefinition = CONCAT(polygonDefinition, ',', tempLatLon);
IF counter = 0 THEN
SET firstLatLon = tempLatLon;
END IF;
SET counter = counter + 1;
FETCH curs INTO tempLatLon;
END WHILE;
CLOSE curs;
SET polygonDefinition = CONCAT(polygonDefinition, ',', firstLatLon);
SET polygonDefinition = SUBSTRING(polygonDefinition, 3);
SET polygonDefinition = CONCAT('POLYGON((', polygonDefinition, '))');
SET geometry = ST_Centroid(ST_GeomFromText(polygonDefinition));
UPDATE `users` SET centroid = geometry WHERE id = userId;
END;;
DELIMITER ;
");
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
DB::connection()->getPdo()->exec("
ALTER TABLE `users` DROP COLUMN centroid;
DROP INDEX centroid_index ON `users`;
DROP FUNCTION IF EXISTS `UPDATE_USER_CENTROID`;");
}
}
Please help! Thanks!
This is how I've been doing procedures in migrations so far...
I believe I've had some trouble setting the delimiters so I dropped them and used different unprepared()
for each statement.
$procedure = "
CREATE PROCEDURE `procedure_name`(procedure_param_1 TEXT, procedure_param_2 TEXT)
BEGIN
// Your SP here
END
";
DB::unprepared("DROP procedure IF EXISTS procedure_name");
DB::unprepared($procedure);
This seems to be related: Laravel migration raw sql error