Tie A Recursive Google Cloud SQL Stored Procedure To A Google Apps Script / Spreadsheet Application Part 2: the MySQL stored procedures
______________________
This three-part article shows how to tie a recursive Google Cloud SQL stored procedure to a Google Spreadsheet application.
Part One described the application and explained the steps needed to configure the Cloud SQL instance and the MySQL development tool.
Part Two here will explain the MySQL stored procedures behind the application.
Part Three will show how the front-end Google Apps Script / Google Spreadsheet application and engineering work.
Download the two application component files and scripts for the four MySQL stored procedures from the GitHub repository.
At the March 19, 2015 GDGLA Meetup, I made a presentation covering this article. See the YouTube vid here.
I'd like to say thank you to Mr. Matt Kaufman, the Google Developer Group Los Angeles organizer. At the December 10, 2014 Chromecast Tech Talk, he suggested use of the getScriptProperties object to hold spreadsheet URL and database connection values.
________________
In an earlier article, I showed how recursion works in SQL Server 2005 and beyond. The article started with a stored procedure, a function, and a common table expression that recursively calculate integer factorials - a classic way to show how recursion works. Then, the article showed how to recursively extract the multiple-of-two component integers of a given integer, again with a stored procedure, a function, and a common table expression. This second problem, and its solution, led to the example application for this article.
That earlier article explains that a base-10 integer can "unpack" into base-10 multiples-of-two. For example,
8331 = (2 ^ 13) + (2 ^ 7) + (2 ^ 3) + (2 ^ 1) + (2 ^ 0)
= 8192 + 128 + 8 + 2 + 1
= 8331
As I explained in that article, a looping algorithm will definitely work here, but a recursive solution will also work. MySQL does not have the common table expressions of modern SQL Server, and MySQL functions don't support recursion. Still, MySQL stored procedures can handle recursion, and this example application will rely on it.
In the MySQL workbench, I first built a database called 'DEMO'. Then, I ran stored procedure usp_create_example_table to create a table with base-10 multiple-of-two integers in the ROW_ID column and their string equivalents in the ROW_NAME column. The powers of two range from zero to fifteen.
Next, I built stored procedure usp_build_comma_dlm_string as the core stored procedure for the application. This stored procedure has two parameters: N, the integer it will unpack, and PARSE_STRING, a comma-delimited string of the multiple-of-two components of N. Because this SP returns a value in PARSE_STRING, technically the SP should declare it as an "OUT" parameter. However, in this application, stored procedures
usp_return_comma_dlm_string
and
usp_return_tbl_of_values
both call stored procedure
usp_build_comma_dlm_string
in their own code, which has a few implications because of the recursion machinery. Stored procedure
usp_build_comma_dlm_string
recursively builds PARSE_STRING, the comma-delimited string. For the recursion to work, the stored procedure must use @variable @PARSE_STRING. An @variable, or a user-defined "session" variable, stays alive throughout the stored procedure session. Because of the way MySQL works, a user-defined "session" @variable does not get the "OUT" keyword, with two exceptions. First, in the stored procedure declarations specifically, the @variables don't have the actual "@" character. We'll see this in usp_build_comma_dlm_string. Second, the declaration for a variable intended as a session variable does not have the "@" character in front of the variable name. We'll see this in usp_return_comma_dlm_string.
In stored procedures, MySQL will default system variable max_sp_recursion_depth to zero. This disables recursion and to enable it, I arbitrarily set max_sp_recursion_depth to 20, or 20 recursion levels. This system variable has a maximum value of 255.
If the call to usp_build_comma_dlm_string has a NULL value for @PARSE_STRING, the if-block sets @PARSE_STRING to an empty string value at line 14. This helps prevent crashes, because MySQL defines an empty string as a string with length zero.
The actual recursion happens in the next if-block. If N, the integer to unpack, equals zero at line 26, the stored procedure has hit the recursion base-case and will end as planned, with the recursively-computed value in "session" variable @PARSE_STRING. Because the final character of @PARSE_STRING is a comma at this point, the base-case strips it off at line 27. The recursion step in the else-block uses LOG2, FLOOR, and POWER functions at line 29 to compute the largest base-10 multiple-of-two component integer in parameter / variable N. It places this value in integer variable COMPONENT. Next, it casts COMPONENT as a string and concatenates this value to @PARSE_STRING, with a trailing comma at line 30 to delimit the string. I did not add a space after that comma because spaces will crash the MySQL find_in_set() function. We'll see shortly that stored procedure usp_return_tbl_of_values uses this function. Line 31 subtracts COMPONENT from N, to drive the stored procedure closer to the base-case. Finally, the stored procedure makes a recursive call to itself at line 32.
Stored procedure usp_return_comma_dlm_string has one IN parameter, input_param - the integer to parse / unpack. The stored procedure first declares variable @PARSE_STRING at line 9. Note that this declaration does not have the leading "@". Only in later uses does @PARSE_STRING get the "@" in most cases, as described above. It initializes @PARSE_STRING to NULL, calls stored procedure usp_build_comma_dlm_string, and selects @PARSE_STRING to return the value of this variable to the caller.
Stored procedure usp_return_tbl_of_values works almost the same way. In the SELECT statement, the SP queries DEMO database table EXAMPLE_TABLE at line 12. The SP uses variable @PARSE_STRING inside the WHERE clause FIND_IN_SET function at line 13. This SELECT returns the result set to the caller.
This covered the MySQL stored procedures for the application. Part 3 will show how the front-end Google Apps Script / Google Spreadsheet application / engineering work.
This three-part article shows how to tie a recursive Google Cloud SQL stored procedure to a Google Spreadsheet application.
Part One described the application and explained the steps needed to configure the Cloud SQL instance and the MySQL development tool.
Part Two here will explain the MySQL stored procedures behind the application.
Part Three will show how the front-end Google Apps Script / Google Spreadsheet application and engineering work.
Download the two application component files and scripts for the four MySQL stored procedures from the GitHub repository.
At the March 19, 2015 GDGLA Meetup, I made a presentation covering this article. See the YouTube vid here.
I'd like to say thank you to Mr. Matt Kaufman, the Google Developer Group Los Angeles organizer. At the December 10, 2014 Chromecast Tech Talk, he suggested use of the getScriptProperties object to hold spreadsheet URL and database connection values.
________________
In an earlier article, I showed how recursion works in SQL Server 2005 and beyond. The article started with a stored procedure, a function, and a common table expression that recursively calculate integer factorials - a classic way to show how recursion works. Then, the article showed how to recursively extract the multiple-of-two component integers of a given integer, again with a stored procedure, a function, and a common table expression. This second problem, and its solution, led to the example application for this article.
That earlier article explains that a base-10 integer can "unpack" into base-10 multiples-of-two. For example,
8331 = (2 ^ 13) + (2 ^ 7) + (2 ^ 3) + (2 ^ 1) + (2 ^ 0)
= 8192 + 128 + 8 + 2 + 1
= 8331
As I explained in that article, a looping algorithm will definitely work here, but a recursive solution will also work. MySQL does not have the common table expressions of modern SQL Server, and MySQL functions don't support recursion. Still, MySQL stored procedures can handle recursion, and this example application will rely on it.
In the MySQL workbench, I first built a database called 'DEMO'. Then, I ran stored procedure usp_create_example_table to create a table with base-10 multiple-of-two integers in the ROW_ID column and their string equivalents in the ROW_NAME column. The powers of two range from zero to fifteen.
Next, I built stored procedure usp_build_comma_dlm_string as the core stored procedure for the application. This stored procedure has two parameters: N, the integer it will unpack, and PARSE_STRING, a comma-delimited string of the multiple-of-two components of N. Because this SP returns a value in PARSE_STRING, technically the SP should declare it as an "OUT" parameter. However, in this application, stored procedures
usp_return_comma_dlm_string
and
usp_return_tbl_of_values
both call stored procedure
usp_build_comma_dlm_string
in their own code, which has a few implications because of the recursion machinery. Stored procedure
usp_build_comma_dlm_string
recursively builds PARSE_STRING, the comma-delimited string. For the recursion to work, the stored procedure must use @variable @PARSE_STRING. An @variable, or a user-defined "session" variable, stays alive throughout the stored procedure session. Because of the way MySQL works, a user-defined "session" @variable does not get the "OUT" keyword, with two exceptions. First, in the stored procedure declarations specifically, the @variables don't have the actual "@" character. We'll see this in usp_build_comma_dlm_string. Second, the declaration for a variable intended as a session variable does not have the "@" character in front of the variable name. We'll see this in usp_return_comma_dlm_string.
In stored procedures, MySQL will default system variable max_sp_recursion_depth to zero. This disables recursion and to enable it, I arbitrarily set max_sp_recursion_depth to 20, or 20 recursion levels. This system variable has a maximum value of 255.
If the call to usp_build_comma_dlm_string has a NULL value for @PARSE_STRING, the if-block sets @PARSE_STRING to an empty string value at line 14. This helps prevent crashes, because MySQL defines an empty string as a string with length zero.
The actual recursion happens in the next if-block. If N, the integer to unpack, equals zero at line 26, the stored procedure has hit the recursion base-case and will end as planned, with the recursively-computed value in "session" variable @PARSE_STRING. Because the final character of @PARSE_STRING is a comma at this point, the base-case strips it off at line 27. The recursion step in the else-block uses LOG2, FLOOR, and POWER functions at line 29 to compute the largest base-10 multiple-of-two component integer in parameter / variable N. It places this value in integer variable COMPONENT. Next, it casts COMPONENT as a string and concatenates this value to @PARSE_STRING, with a trailing comma at line 30 to delimit the string. I did not add a space after that comma because spaces will crash the MySQL find_in_set() function. We'll see shortly that stored procedure usp_return_tbl_of_values uses this function. Line 31 subtracts COMPONENT from N, to drive the stored procedure closer to the base-case. Finally, the stored procedure makes a recursive call to itself at line 32.
Stored procedure usp_return_comma_dlm_string has one IN parameter, input_param - the integer to parse / unpack. The stored procedure first declares variable @PARSE_STRING at line 9. Note that this declaration does not have the leading "@". Only in later uses does @PARSE_STRING get the "@" in most cases, as described above. It initializes @PARSE_STRING to NULL, calls stored procedure usp_build_comma_dlm_string, and selects @PARSE_STRING to return the value of this variable to the caller.
Stored procedure usp_return_tbl_of_values works almost the same way. In the SELECT statement, the SP queries DEMO database table EXAMPLE_TABLE at line 12. The SP uses variable @PARSE_STRING inside the WHERE clause FIND_IN_SET function at line 13. This SELECT returns the result set to the caller.
This covered the MySQL stored procedures for the application. Part 3 will show how the front-end Google Apps Script / Google Spreadsheet application / engineering work.