Tie A Recursive Google Cloud SQL Stored Procedure To A Google Apps Script / Spreadsheet Application Part 3: the application engineering

______________________

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 explain the steps needed to configure the Cloud SQL instance and the MySQL development tool.

Part Two explained the MySQL stored procedures behind the application.

Part Three here 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.
________________

We'll now look at the Google Apps Spreadsheet application. A user with Google account rights to the application will receive at least a URL to open the web page
1. The application web page
of the application. The integer spinner ranges from 1 to 65535. I arbitrarily set these positive integer limits for the entire application, although I could easily change them. To use the application, either click on the spinner controls, or type a number in the spinner text box. Note that this application has no input / error / etc. checking. When the spinner has the chosen value - 47, for example - click SUBMIT. The page will show
2. The application with computed results
both stored procedure result sets. A user with Google account rights to the application can also see the spreadsheet
3. The Google Spreadsheet tied to
the application web page
with the correct URL. The spreadsheet data mirrors the web page data, but for this application, the work flow moves one way, from the web page to the spreadsheet.

On the web page, RESET will delete all result set data on the web page and the spreadsheet, and reset the web page spinner to 1. We'll explore this code shortly.

Two files have the application code:

   Code.gs

and

   CloudSQLDemoApp.html

In the Google Apps Script script editor, drill down to

   File -> Project properties -> Script properties

to place key / value pairs
4. Enter key / value pairs in
Project properties
for the database connection values and the spreadsheet URL value. Although these values could go in the code files, this approach helps maintain application security if developers distribute those files. The localSpreadSheet variable at line 27 uses the spreadSheetURL value and we'll see shortly that the returnFormParams function in Code.gs uses the other values.

We first set the "MySQL values" in the MySQL Workbench tool we used to create the database in Google Cloud. As seen in Part 1, Developers Console -> Access Control shows where to find the IP address value which will then go into the Cloud SQL instance IP address string.

The component files have plenty of comments to explain everything at a close-in level. Because the overall application engineering gets a little complicated, I will now explain how it works. I'll break out everything in numbered steps.

1) When the spreadsheet first opens, an "On open" trigger calls the onOpen() function of line 29 in the Code.gs file. This function writes text in cells, formats the spreadsheet columns / fonts, etc. If no one opens / looks at the spreadsheet, this trigger never fires and this function never runs. In the script editor, drill down to

   Resources -> Current Project's Triggers

to set up triggers based on pre-defined application events.

2) When the application web page actually runs, execution starts in the doGet() function of line 80 in the Code.gs file. This function "activates" the web page, in this case CloudSQLDemoApp.html. Without this function and the

   HtmlService.createTemplateFromFile("CloudSQLDemoApp.html").evaluate();

call at line 86 inside the Code.gs doGet() function, the web page URL will not load the HTML for the CloudSQLDemoApp.html  web page.

3) The web page loads with the HTML / JavaScript / jQuery ready to go. If the spinner has an integer and the user hits SUBMIT, the submitData() JavaScript function of line 53 in CloudSQLDemoApp.html runs. This function grabs the value in the spinner and runs this statement

   google.script.run.withFailureHandler(alertString).withSuccessHandler(callBack).
   returnFormParams(spinnerVal);

at line 81 as one single line, with no spaces or hard returns between
"(callback)." and "returnFormParams".

4) The statement in 3) calls the returnFormParams() function of line 89 over in the Code.gs file, passing the picked spinner value as parameter spinnerVal.

5) The returnFormParams() function in Code.gs at line 89 uses values from the scriptProperties object to set up the database connection and get the query result sets. Specifically, this function

   a) sets up a connection out to the Cloud SQL database at line 109
   b) calls the stored procedures it needs at lines 125 and 126
   c) catches the result sets at lines 125 and 126
   d) calls a function to write the result sets on the spreadsheet at
       lines 144 and 145
   e) places the result sets in array resultSetArray at line 166
   f) returns the resultSetArray array back to the function call inside
       the CloudSQLDemoApp.html function submitData(), which
       made the original call to returnFormParams(). This
       submitData() function call originally happened in 3) above

6) Back in the last line of the submitData() function in the
CloudSQLDemoApp.html file at line 81, the

         withSuccessHandler(callBack)

part calls the callBack() function in HTML file CloudSQLDemoApp.html. That
callBack() function catches the resultSetArray array. The
callBack() function then draws the data in that array on the web page.
_______
_______________
____

The RESET button calls jQuery function

         $("#RESET").click()

in 

         CloudSQLDemoApp.html

at line 88 to reset the spinner to 1 and delete all result set data on the web page. To keep the spreadsheet consistent, the last line of this jQuery function at line 103 calls the clearSheet() function of line 66 in Code.gs with a

 google.script.run.withFailureHandler(alertString).withSuccessHandler().clearSheet();

statement. This statement works almost exactly like the statement of step 3) above. The statement has no parameters and no callbacks.

Cloud SQL Demo shows how to easily front-end a Google Cloud MySQL database instance with a Google Apps Script project. It also shows how to weave recursion into that database. This example shows that developers can focus on the solutions they want to build - their tools can handle the product requirements.

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.

Tie A Recursive Google Cloud SQL Stored Procedure To A Google Apps Script / Spreadsheet Application Part 1: set up the tools

______________________

This three-part article shows how to tie a recursive Google Cloud SQL stored procedure to a Google Spreadsheet application.

Part One here will describe the application and explain the steps needed to configure the Cloud SQL instance and the MySQL development tool.

Part Two 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 Google Apps Script 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.
________________

For a recent project, I saw that a web front-end solution over a Google Cloud SQL database would work the best. The customer then mentioned spreadsheets, so the solution I built awhile back, to front-end BigQuery with Google Spreadsheets, came in handy. It turned out that some recursive stored procedures would really help. Don't ask - it's a long story. Fortunately, I have some experience with this, and everything worked out. The whole solution became the core idea for this article.

Except for description of a few important details, this article will focus more on the software I built, and less on how to set up the MySQL / Google Cloud SQL / Google Apps Script machinery. A YouTube video helped me set up Cloud SQL, and I described how to set up Google Apps Script for Google Spreadsheets in my earlier articles here at the BitVectors blog.

After some research and the video, I set up a Cloud SQL project and then I installed MySQL locally to front-end that project. The Developer's Console made it quite clear that it expected me to use MySQL 5.5 - anything past that simply won't work.
1. Cloud SQL expects MySQL 5.5
I found a Windows MySQL 5.5 download and I proceeded with that installation.

Next, I opened the local MySQL workbench and built a new connection to the Cloud SQL project. I placed the IP address of the Cloud SQL instance found here
2. The Cloud SQL instance IP address
in the Developers Console
in the MySQL workbench connection configuration

3. In the local MySQL workbench, set up a new connection
and type the Google Cloud SQL project IP address
in the Hostname textbox
as shown here. Then, in the Developers Console ACCESS CONTROL tab

4. In the Developers Console, configure the authorized networks
I configured the authorized networks as suggested in the Setup for Google Cloud SQL section here. I made sure I included the IP address for the machine I used for development as one of those authorized networks.

This completed the tool configuration for the application. Part 2 will look at the MySQL stored procedures behind the application.