StoredProcedureWith::up() - Code Metrics - RocketPropelledTortoise/Core - Measure and Improve Code Quality continuously with Scrutinizer

StoredProcedureWith::up() A
last analyzed 2020-04-19 15:33 UTC

↳ Parent: StoredProcedureWith

Complexity

Conditions	2
Paths	2

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	2
nc	2
nop	0
dl	0
loc	8
rs	10
c	0
b	0
f	0

<?php

use Illuminate\Database\Migrations\Migration;

/**
 * @codeCoverageIgnore
 */
class StoredProcedureWith extends Migration
{
    /**
     * Usage: the standard syntax:
     * WITH RECURSIVE recursive_table AS
     * (initial_SELECT
     *  UNION ALL
     *  recursive_SELECT)
     *  final_SELECT;
     * should be translated by you to
     * CALL WITH_EMULATOR(recursive_table, initial_SELECT, recursive_SELECT, final_SELECT, 0, "").
     *
     * ALGORITHM:
     * 1) we have an initial table T0 (actual name is an argument "recursive_table"),
     *    we fill it with result of initial_SELECT.
     * 2) We have a union table U, initially empty.
     * 3) Loop:
     *      add rows of T0 to U,
     *      run recursive_SELECT based on T0 and put result into table T1,
     *      if T1 is empty
     *          leave loop
     *      else
     *          swap T0 and T1 (renaming) and empty T1
     * 4) Drop T0, T1
     * 5) Rename U to T0
     * 6) run final select, send relult to client
     *
     * This is for *one* recursive table.
     * It would be possible to write a SP creating multiple recursive tables.
     *
     * Largely inspired from :: http://guilhembichot.blogspot.co.uk/2013/11/with-recursive-and-mysql.html
     *
     * @var string
     */
    protected static $PROCEDURE = <<<'SQL'
DROP PROCEDURE IF EXISTS WITH_EMULATOR;
CREATE PROCEDURE WITH_EMULATOR(
  recursive_table      VARCHAR(100),   # name of recursive table
  initial_SELECT       VARCHAR(21845), # seed a.k.a. anchor
  recursive_SELECT     VARCHAR(21845), # recursive member
  final_SELECT         VARCHAR(21845), # final SELECT on UNION result
  max_recursion        INT UNSIGNED,   # safety against infinite loop, use 0 for default
  create_table_options VARCHAR(21845)  # you can add CREATE-TABLE-time options to your recursive_table, to speed up
                                       # initial/recursive/final SELECTs; example: "(KEY(some_column)) ENGINE=MEMORY"
)

BEGIN
  DECLARE new_rows INT UNSIGNED;
  DECLARE recursive_table_next VARCHAR(120);
  DECLARE recursive_table_union VARCHAR(120);
  DECLARE recursive_table_tmp VARCHAR(120);
  SET recursive_table_next = concat(recursive_table, "_next");
  SET recursive_table_union = concat(recursive_table, "_union");
  SET recursive_table_tmp = concat(recursive_table, "_tmp");

  # create and fill T0
  # If you need to reference recursive_table more than once in recursive_SELECT, remove the TEMPORARY word.
  SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table, " ", create_table_options, " AS ", initial_SELECT);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # create U
  SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table_union, " LIKE ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # create T1
  SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table_next, " LIKE ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  IF max_recursion = 0
  THEN
    SET max_recursion = 100; # a default to protect the innocent
  END IF;
  recursion: REPEAT
    # add T0 to U (this is always UNION ALL)
    SET @str = CONCAT("INSERT INTO ", recursive_table_union, " SELECT * FROM ", recursive_table);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    # we are done if max depth reached
    SET max_recursion = max_recursion - 1;
    IF NOT max_recursion
    THEN
      LEAVE recursion;
    END IF;

    # fill T1 by applying the recursive SELECT on T0
    SET @str = CONCAT("INSERT INTO ", recursive_table_next, " ", recursive_SELECT);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    # we are done if no rows in T1
    SELECT row_count() INTO new_rows;
    IF NOT new_rows
    THEN
      LEAVE recursion;
    END IF;

    # Prepare next iteration:
    # T1 becomes T0, to be the source of next run of recursive_SELECT,
    # T0 is recycled to be T1.
    SET @str = CONCAT("ALTER TABLE ", recursive_table, " RENAME ", recursive_table_tmp);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    # we use ALTER TABLE RENAME because RENAME TABLE does not support temp tables
    SET @str = CONCAT("ALTER TABLE ", recursive_table_next, " RENAME ", recursive_table);
    PREPARE stmt FROM @str;
    EXECUTE stmt;
    SET @str = CONCAT("ALTER TABLE ", recursive_table_tmp, " RENAME ", recursive_table_next);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    # empty T1
    SET @str = CONCAT("TRUNCATE TABLE ", recursive_table_next);
    PREPARE stmt FROM @str;
    EXECUTE stmt;
  UNTIL 0 END REPEAT;

  # eliminate T0
  SET @str = CONCAT("DROP TEMPORARY TABLE ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # Final (output) SELECT uses recursive_table name
  SET @str = CONCAT("ALTER TABLE ", recursive_table_union, " RENAME ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # Run final SELECT on UNION
  SET @str = final_SELECT;
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # Remove old temporary tables
  SET @str = CONCAT("DROP TEMPORARY TABLE IF EXISTS ", recursive_table, ", ", recursive_table_next,
    ", ", recursive_table_tmp);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # We are done :-)
END;
SQL;

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        if (DB::connection()->getDriverName() != 'mysql') {
            return;
        }

        DB::connection()->getPdo()->exec(self::$PROCEDURE);
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        if (DB::connection()->getDriverName() != 'mysql') {
            return;
        }
        DB::connection()->getPdo()->exec('DROP PROCEDURE IF EXISTS WITH_EMULATOR');
    }
}


1			<?php
2
3			use Illuminate\Database\Migrations\Migration;
4
5			/**
6			* @codeCoverageIgnore
7			*/
8			class StoredProcedureWith extends Migration
9			{
10			/**
11			* Usage: the standard syntax:
12			* WITH RECURSIVE recursive_table AS
13			* (initial_SELECT
14			* UNION ALL
15			* recursive_SELECT)
16			* final_SELECT;
17			* should be translated by you to
18			* CALL WITH_EMULATOR(recursive_table, initial_SELECT, recursive_SELECT, final_SELECT, 0, "").
19			*
20			* ALGORITHM:
21			* 1) we have an initial table T0 (actual name is an argument "recursive_table"),
22			* we fill it with result of initial_SELECT.
23			* 2) We have a union table U, initially empty.
24			* 3) Loop:
25			* add rows of T0 to U,
26			* run recursive_SELECT based on T0 and put result into table T1,
27			* if T1 is empty
28			* leave loop
29			* else
30			* swap T0 and T1 (renaming) and empty T1
31			* 4) Drop T0, T1
32			* 5) Rename U to T0
33			* 6) run final select, send relult to client
34			*
35			* This is for one recursive table.
36			* It would be possible to write a SP creating multiple recursive tables.
37			*
38			* Largely inspired from :: http://guilhembichot.blogspot.co.uk/2013/11/with-recursive-and-mysql.html
39			*
40			* @var string
41			*/
42			protected static $PROCEDURE = <<<'SQL'
43			DROP PROCEDURE IF EXISTS WITH_EMULATOR;
44			CREATE PROCEDURE WITH_EMULATOR(
45			recursive_table VARCHAR(100), # name of recursive table
46			initial_SELECT VARCHAR(21845), # seed a.k.a. anchor
47			recursive_SELECT VARCHAR(21845), # recursive member
48			final_SELECT VARCHAR(21845), # final SELECT on UNION result
49			max_recursion INT UNSIGNED, # safety against infinite loop, use 0 for default
50			create_table_options VARCHAR(21845) # you can add CREATE-TABLE-time options to your recursive_table, to speed up
51			# initial/recursive/final SELECTs; example: "(KEY(some_column)) ENGINE=MEMORY"
52			)
53
54			BEGIN
55			DECLARE new_rows INT UNSIGNED;
56			DECLARE recursive_table_next VARCHAR(120);
57			DECLARE recursive_table_union VARCHAR(120);
58			DECLARE recursive_table_tmp VARCHAR(120);
59			SET recursive_table_next = concat(recursive_table, "_next");
60			SET recursive_table_union = concat(recursive_table, "_union");
61			SET recursive_table_tmp = concat(recursive_table, "_tmp");
62
63			# create and fill T0
64			# If you need to reference recursive_table more than once in recursive_SELECT, remove the TEMPORARY word.
65			SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table, " ", create_table_options, " AS ", initial_SELECT);
66			PREPARE stmt FROM @str;
67			EXECUTE stmt;
68
69			# create U
70			SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table_union, " LIKE ", recursive_table);
71			PREPARE stmt FROM @str;
72			EXECUTE stmt;
73
74			# create T1
75			SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table_next, " LIKE ", recursive_table);
76			PREPARE stmt FROM @str;
77			EXECUTE stmt;
78
79			IF max_recursion = 0
80			THEN
81			SET max_recursion = 100; # a default to protect the innocent
82			END IF;
83			recursion: REPEAT
84			# add T0 to U (this is always UNION ALL)
85			SET @str = CONCAT("INSERT INTO ", recursive_table_union, " SELECT * FROM ", recursive_table);
86			PREPARE stmt FROM @str;
87			EXECUTE stmt;
88
89			# we are done if max depth reached
90			SET max_recursion = max_recursion - 1;
91			IF NOT max_recursion
92			THEN
93			LEAVE recursion;
94			END IF;
95
96			# fill T1 by applying the recursive SELECT on T0
97			SET @str = CONCAT("INSERT INTO ", recursive_table_next, " ", recursive_SELECT);
98			PREPARE stmt FROM @str;
99			EXECUTE stmt;
100
101			# we are done if no rows in T1
102			SELECT row_count() INTO new_rows;
103			IF NOT new_rows
104			THEN
105			LEAVE recursion;
106			END IF;
107
108			# Prepare next iteration:
109			# T1 becomes T0, to be the source of next run of recursive_SELECT,
110			# T0 is recycled to be T1.
111			SET @str = CONCAT("ALTER TABLE ", recursive_table, " RENAME ", recursive_table_tmp);
112			PREPARE stmt FROM @str;
113			EXECUTE stmt;
114
115			# we use ALTER TABLE RENAME because RENAME TABLE does not support temp tables
116			SET @str = CONCAT("ALTER TABLE ", recursive_table_next, " RENAME ", recursive_table);
117			PREPARE stmt FROM @str;
118			EXECUTE stmt;
119			SET @str = CONCAT("ALTER TABLE ", recursive_table_tmp, " RENAME ", recursive_table_next);
120			PREPARE stmt FROM @str;
121			EXECUTE stmt;
122
123			# empty T1
124			SET @str = CONCAT("TRUNCATE TABLE ", recursive_table_next);
125			PREPARE stmt FROM @str;
126			EXECUTE stmt;
127			UNTIL 0 END REPEAT;
128
129			# eliminate T0
130			SET @str = CONCAT("DROP TEMPORARY TABLE ", recursive_table);
131			PREPARE stmt FROM @str;
132			EXECUTE stmt;
133
134			# Final (output) SELECT uses recursive_table name
135			SET @str = CONCAT("ALTER TABLE ", recursive_table_union, " RENAME ", recursive_table);
136			PREPARE stmt FROM @str;
137			EXECUTE stmt;
138
139			# Run final SELECT on UNION
140			SET @str = final_SELECT;
141			PREPARE stmt FROM @str;
142			EXECUTE stmt;
143
144			# Remove old temporary tables
145			SET @str = CONCAT("DROP TEMPORARY TABLE IF EXISTS ", recursive_table, ", ", recursive_table_next,
146			", ", recursive_table_tmp);
147			PREPARE stmt FROM @str;
148			EXECUTE stmt;
149
150			# We are done :-)
151			END;
152			SQL;
153
154			/**
155			* Run the migrations.
156			*
157			* @return void
158			*/
159			public function up()
160			{
161			if (DB::connection()->getDriverName() != 'mysql') {
162			return;
163			}
164
165			DB::connection()->getPdo()->exec(self::$PROCEDURE);
166			}
167
168			/**
169			* Reverse the migrations.
170			*
171			* @return void
172			*/
173			public function down()
174			{
175			if (DB::connection()->getDriverName() != 'mysql') {
176			return;
177			}
178			DB::connection()->getPdo()->exec('DROP PROCEDURE IF EXISTS WITH_EMULATOR');
179			}
180			}
181

RocketPropelledTortoise / Core

StoredProcedureWith::up() A last analyzed 2020-04-19 15:33 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

StoredProcedureWith::up() A
last analyzed 2020-04-19 15:33 UTC