Now that we have a firm foundation in Flask and how the user interface is designed, we are ready to begin writing the code for each of the versions of the application starting with the relational database version.
As you will see, the evolution of the application from a pure relational model to a document model demonstrates how we can avoid some of the messier aspects of using relational data—even in the hybrid example. One element that may surprise you is the length and complexity of the code for the document store version is considerably shorter and easier to understand. What better reason to consider writing all your future applications using the MySQL Document Store!
Version 1—relational database: implements a traditional relational database model using only the nondocument store features.
Version 2—relational database + JSON fields (hybrid): implements a relational database model augmented with JSON fields.
Version 3—document store: implements a pure document store (NoSQL) solution.
The following sections present the entire code for the database components for each version along with the appropriate changes to the user interface. Rather than present snippets of code potentially out of context, the entire code for each version is presented for clarity and completeness. As a result, this chapter is a bit longer.
Note
Recall from Chapter 8 that we used a directory structure to organize the code. For example, we have folders named version1, version2, and version3 for each version of the application. If you are following along, be sure to place the files discussed in the appropriate folder.
Version 1: Relational Database
This version implements a traditional relational database solution in which we model the data based on the views or data items. For the purposes of demonstration, we will implement the database code in a code module that we can import into the application code. This code module will use the older MySQL Connector/Python protocol and API. That is, we will not be using the X DevAPI and will rely on SQL statements to work with the data.
Let’s start with a brief overview of the database design. Because relational database is familiar to most readers interested in the MySQL Document Store, we will skip any lengthy discussions and present the database with a brief introduction and a look at the SQL CREATE statements.
Database Design
ERD diagram—library database (version 1)
We also need a way to retrieve the primary keys from the authors table for a given book via the ISBN column. We use this data when we query the database for the data for a given book. To make it easier to maintain, we will create a stored routine (function) to retrieve a comma-separated list of the AuthorId column in the authors table. We use this to populate the SelectMultipleField in the book template. Finally, we will need another stored routine (function) that retrieves the author names for a given book via the ISBN column. We then will use this data to populate the list view of the books table.
Listing 9-1 shows the CREATE statements for all seven of these objects. If you want to follow along building this version of the application as you read, you should create a file named library_v1.sql so you can recreate the database later if needed. The database uses only tables and stored routines to keep the discussion short.
Library Version 1 Database Create Script (library_v1.sql)
Now that the database has been created, let’s see the code for the database class.
Tip
There is a database creation script for each version in the sample code for this book. See the Apress web site for this book to download the source code.
Database Code
The code for working with the database is placed in a file named library_v1.py in the database folder under the version1 folder as described in Chapter 8 under the section, “Preparing the Directory Structure.” Because most of the code is common to older Python applications that use the MySQL Connector/Python connector, we discuss only the salient points for each portion of the code.
That said the code implements four classes: one for each of the data views (author, publisher, book) and another class for interfacing with the server. These classes are named Author, Publisher, Book, and Library, respectfully.
Note
To use the library placed in the database folder, you must create an empty file named __init__.py in the database folder.
SQL Strings
To make the code easier to maintain and to modify it if any changes are needed for the SQL statements, we place these in the preamble of the code module as strings that we can reference later in the code. Doing this also helps keep code line lengths shorter. Listing 9-2 shows the preamble for the library_v1.py code module. Note that it begins with importing the MySQL Connector/Python library.
Initialization and SQL Statements (library_v1.py)
That’s a lot of SQL, isn’t it? If it seems daunting, consider that most relational database applications have a similar set of SQL statements. Consider also that this example application is purposefully small and limited. Taking those into account, imagine the number and complexity of SQL statements for a much larger application. Wow.
Next, let’s look at the Author class.
Author Class
The Author class is the least complicated and forms a model for how the other data classes are constructed. In particular, we save an instance of the Library class via the constructor and reference this instance whenever we execute queries (or use any of the methods in the Library class). We then build four functions—one each for create, read, update, and delete. Listing 9-3 shows the Author class code. The read operation is designed to return one row if the primary key is passed as a parameter or all rows if no parameter is supplied.
Note that we use the Library class function sql() to execute queries. For example, self.library.sql(“COMMIT”) executes the COMMIT SQL command. We use the strings created previously using the format() function to fill in the optional parameters. We will see this function in more detail later in this section. Take some time to read through the code to ensure you understand it.
Author Class (library_v1.py)
Next, let’s look at the Publisher class.
Publisher Class
The Publisher class is very similar to the Author class and is implemented in the same manner. The only difference is in the SQL statements used. To be complete, Listing 9-4 shows the complete code for the Publisher class.
Publisher Class (library_v1.py)
Next, a look at the Book class.
Book Class
The Book class has the same methods as the last two, but the code for create, update, and delete is a little more complex. This is because we must execute multiple statements to work with the data. Therefore, we implicitly start a transaction inside a try block and if any of the queries fail, we rollback the transaction. This is very common for relational database solutions. Listing 9-5 shows the complete code for the Book class. Take your time to read through the code to understand how it was constructed.
Book Class (library_v1.py)
Finally, we look at the Library class.
Library Class
Recall, the Library class is used to encapsulate working with the MySQL server. Thus, we create functions for working with the connection (connect, disconnect, is_connected). We also create a function that we can use to execute queries. This is mainly for convenience and not generally required. The function is named sql() and handles returning result sets or errors as necessary. The last function is used to return an abbreviated dataset of the books in the database, which is used to populate the book list page. Listing 9-6 shows the code for the Library class. As you will see, it too is very straightforward.
Library Class (library_v1.py)
Now that we have the database code module, let’s look at the application code.
Application Code
There are only a few areas in the application code from the base code we saw earlier where we need to add more code. This includes adding the import statement for the database code module, setting up the Library class instance, and adding code to the author, publisher, and book view functions to use the classes in the database code module. It is fortunate that the template files we created in the user interface discussion can be used without modification.
To build this version of the application, you should copy the base/mylibrary_base.py file to version1/mylibrary_v1.py and either enter the code below or retrieve it from the Apress book web site.
Although the code may appear to be long, it isn’t complicated. Further, except for the book view function, the logic is the same pattern for the author and publisher view. The book view has more logic to enable the add note feature. The following sections discuss the changes needed for each area. Recall, we need the mylibrary_base.py code we saw in an earlier section.
Setup and Initialization
The code for setup and initialization for the Library class is simple. We need only import the classes from the code module and then create an instance of the Library class and call the connect() function as shown in the following. The import statement goes at the end of the other import statements and the Library setup code can go anywhere after that. In the example code, this code is placed before the first form class function.
Note
Be sure to change the <user> and <password> entries to match your MySQL configuration. These are placeholders for the user account and password.
List View Function
The list view function requires only a few modifications. We will use the Library class instance (named library) to get the data from the database to be displayed in the list on the page. For books, this is simply calling the library.get_books() function. For authors, we instantiate an instance of the Author and Publisher classes then call the read() function without any parameters. Recall from the previous sections, this results in reading all the rows in the table. Listing 9-7 shows the changes needed for the simple_list() view function. The new lines of code are in bold. As you can see, we’re only adding five lines of code. Simple!
List View Function (Version 1)
Author View Function
The changes for the author view function are a bit more complicated. Because the author, publisher, and book view functions follow the same pattern, we will discuss the pattern in general first then see the code for view each function. Because the concept of GET and POST operations may be new to you, we will take a moment and discuss the differences.
Operations (Modes) for View Functions
Operation | Type | Action |
|---|---|---|
Add | GET | Present an empty form and provide a submit field named Add |
Create | POST | Save data to the database for one data item |
Read | GET | Show data from the database for one data item and provide submit fields named Update and Delete |
Update | POST | Save updated data to the database for existing data item |
Delete | POST | Remove the data item from the database |
Note that there are two GET operations and three POST operations. The GET operations are to present either an empty form or to read from a row in the table. The POST operations are events that occur when the user clicks on one of the submit fields resulting in either a create, update, or delete.
Returning the to the author view function, we need to add code for the operations listed above. Rather than discuss the code at length then present it, Listing 9-8 shows the completed code for the author view function. Line numbers have been added to make it easier to see the lines of code discussed. Detailed discussions of the database code are included after the listing.
Author View Function (Version 1)
The first thing we do is add an instance of the Author class and pass it to the Library class instance as shown in line 2. Next, to cover operations where we need the data from the form, we place code at the top of the view function to copy data from the form to local variables as shown in lines 4–7. This ensures if the view function is called again for a POST operation that we capture any data entered by the user. If we hadn’t done this, we could not use the view function for new and existing data.
Next, we must cover the route where we pass in the primary key (in this case author_id). If the author_id is present, we change the label of one of the submit fields (add) to Update. We also know we must read data from the database, which we do with the author.read(author_id) call on line 14 and if we retrieve the row without errors, we place the data from the table into the fields in lines 17—19. If the author_id variable is not present, we remove the delete submit field on line 21.
At this point, we’ve covered the operations of add and read shown in Table 9-1. The create, update, and delete operations are executed only when the request is a POST. To determine this, we check the value of the request.method attribute on line 22. If it is POST, we then must decide which operation is active. We can do this by checking the text of the submit fields. We use a default of create but change it to update or delete based on which submit field was clicked. You can see these operations in lines 24–29.
In particular, if a submit field is clicked, on POST, the data attribute will be True. Thus, we can see which operation we need to perform based on which button was clicked. In the case of the create button, we know it is create unless the label was changed to update and in that case the operation matches—update. On the other hand, if the delete button was not removed and it was clicked, the operation is delete. This is one method of how you can reuse a view function for multiple operations.
Now that we know which operation is active, we execute the action. However, we only do so if the fields have all passed their validation checks. The code on line 30 will return True if all fields have been validated. Thus, we only execute the active operation if the form fields are validated.
The create operation is shown in lines 33–38. Note that we use a try block to detect errors. To create a new author, we simply call the author.create() function with the data from the form. Likewise, the update operation is shown in lines 40–45. Once again, we use a try block to detect errors. To update an existing author, we call the author.update() function with the data from the form. Finally, the delete operation is shown in lines 46–53. Again, we use a try block to detect errors. To delete an existing author, we call the author.delete() function with the author_id from the form.
Now, let’s look at the publisher view function, which is very similar.
Publisher View Function
Because the publisher view function is very similar to the author view function (it is the same design or pattern), I only summarize the code describing only the database operations in detail. Listing 9-9 shows the completed code for the publisher view function. Line numbers have been added to make it easier to see the lines of code discussed. Detailed discussions of the database code are included after the listing.
Publisher View Function (Version 1)
A with the author view function, line 2 instantiates an instance of the Publisher class and lines 4–8 fetch data from the form for use later. Line 12 begins the section to read data from the database, line 14 changes the label of the add submit button to update, and lines 16–22 store that data in the form. Finally, lines 27–32 determine the active operation for a POST request and line 33 ensures the form fields are validated before we execute the database operations.
The create operation is shown in lines 36–41. To create a new publisher, we simply call the publisher.create() function with the data from the form. Likewise, the update operation is shown in lines 43–49. To update an existing publisher, we call the publisher.update() function with the data from the form. Finally, the delete operation is shown in lines 51–56. To delete an existing publisher, we call the publisher.delete() function with the publisher_id from the form.
Pop Quiz
Did you notice a small difference in how we handled the publisher_id? Instead of using the variable from the route, we get the publisher id from the hidden field on the form. This was done intentionally to show an alternative way to save data to the form.
But there is a good reason to use this technique even though it does duplicate a small bit of data. For example, it is possible the user will want to change the ISBN. Because the ISBN is the primary key for the table, if we use the ISBN from the GET request (the /book/978-1-4842-2724-4 route), the database operation will fail to located the row because the ISBN was changed on the form.
This also demonstrates how surrogate primary keys such as auto increment fields can help save you from this potential data land mine.
Now, let’s look at the book view function, which follows the same pattern but requires a bit more logic.
Book View Function
The book view function is more complicated than the author or publisher view function for three reasons: 1) it has more fields, 2) there are select fields that require populating, and 3) there is an additional feature for update operations to add notes to the database for the book.
However, the code follows the same pattern as the previous view functions. Listing 9-10 shows the book view function code in its entirety. Once again, line numbers have been added to enhance readability and discussion of the code follows the listing.
Book View Function (Version 1)
First, you may notice we have a new variable named notes, which is set to None. We do this here because we will use this variable to contain all the notes for the book as read from the database. More on that later.
As with the author and publisher view functions, line 3 instantiates an instance of the Book class and lines 6–12 fetch data from the form for use later. Next is the code for populating the select fields with values from the database. We do this because the book table is dependent on the authors (technically via the books_authors join table, and publishers tables. Thus, we need to fetch the rows from both tables to populate the dropdown and multiple select lists.
Lines 16–21 are for the publisher data. Here, we first instantiate an instance of the Publisher class then retrieve all the data from the table. Next, we loop through the rows adding the publisher id and name to a list, which is then assigned to the data attribute for the select field choices attribute (form.publisher.choices). Why do we include the publisher id? Because the publisher id is only stored in the book table.
Likewise, lines 22–27 do the same for author data creating an instance of the Author class retrieving all the rows then looping through the data to add the author id, and concatenated last and first name. As with the select field, we populate the field data with the new array. At this point, we have both select fields populated. How to set the value to match the rows comes next along with retrieving the data from the database.
Line 32 begins the section to read data from the database. Lines 34–57 retrieve the data from the database and populates for form. For the select fields, setting the data attribute ensures the values are selected. In the case of the publisher, we set the select field data and the item that matches is selected by default. In the case of the select multiple field, we pass a comma-separated list as shown in lines 50–51 where we retrieve a list of author ids from the database. Next, we retrieve the notes for the book and populate an array we use in the template to populate the HTML table.
Wow! That’s a lot of work, isn’t it? All of that work was to setup the form for the add and read operations. Fortunately, the create, update, and delete setup is the same as the other view functions. You can see this in lines 63–9.
The database operations thankfully are familiar. The create operation is shown in lines 72–79. To create a new book, we simply call the book.create() function with the data from the form. Likewise, the update operation is shown in lines 81–89. To update an existing book, we call the book.update() function with the data from the form. Finally, the delete operation is shown in lines 91–96. To delete an existing book, we call the book.delete() function with the isbn from the form.
Templates
There were no changes to the template files from the base version. All you need to do is create a new folder and copy the files from the base. In particular, copy base/templates/* to version1/templates/.
The only change you need to make is to change the “base” text in the base.html file to “V1” as shown in the difference example in the following where the line with the “-” is removed and the line with the “+” is added.
Now that we have the code updated, let’s see how it works!
Executing the Code
Now that the code is written, let’s give it a test drive. Be sure to create the database and any tables necessary first. If you saved the previous SQL statements in a file named library_v1.sql, you can use the SOURCE command in the mysql client as follows.
To execute the application, you can launch it with the Python interpreter specifying the runserver command. The following shows an example of executing the application. Note that we used the port option to specify the port. You should enter this command from the version1 folder. Note that we specify the port as 5001. We will use 5002 for version 2 and 5003 for version 3. This will allow you to run all three versions simultaneously.
Library application book list (version 1)
Notes list example (version 1)
Before we move on to version 2, let’s take a moment to discuss some observations about this version of the application.
Observations
Lengthy code: The code for the application is quite long (over 400 lines).
Lengthy database code module: The code for the database code module is also quite long (over 400 lines).
Over-designed tables: The many-to-many table is unnecessarily complicated, which makes working with SQL a bit more difficult.
Database design can be improved: Savvy database administrators will undoubtedly spot areas that can be improved in the database design. For example, the use of a view can replace the query used in the get_books() function of the Library class.
Over-analyzed data: One of the banes of relational database design is overuse of normal forms in the face of usability. In this case, it is unlikely the user will care to know a list of authors because there is no additional meaningful information—just the author’s first and last name.
Simplistic read: The default mechanism to view data is a list. Although this works fine for authors and publishers, it is restrictive for books because you must click on the Modify link to see the notes for the book. This can be improved with a simple read-only mode rather than update.
Older protocols: There is no X DevAPI integration.
Now, let’s look at the next version of the application.
Version 2: Relational Database + JSON Fields (Hybrid)
This version implements a relational database augmented with JSON fields. We model the data based on the views or data items but use a JSON field to eliminate one of the issues of traditional relational database solutions: many-to-many joins. For the purposes of demonstration, we will implement the database code in a code module that we can import into the application code. Although we will use MySQL Connector/Python as in version 1, we will be using the X DevAPI using SQL statements to work with the data. The goal is to demonstrate how to migrate to using the X DevAPI but preserving the SQL interface. Thus, this version presents a hybrid solution.
The many-to-many relationship in the version 1 database was so that we could make a link from a book to one or more authors and that we may have more than one book with the same authors. However, like most applications, the database design has revealed a case where we have more sophistication than what is needed. In particular, we have a table for authors but find that we only store (or care about) the first and last name. Further, use of the application has shown we have no use cases for querying author data other than to list them with the book.
Therefore, we can eliminate the many-to-many relationship storing the list of author names in a JSON field instead. This has led to other minor changes such as the stored routines and other additions.
Let’s start with a brief overview of the database design after the changes. Because the database is the same as version 1 with minor changes, we will present a brief overview concentrating only on the differences.
Database Design
ERD diagram—library database (version 2)
By eliminating the many-to-many relationship, we can remove the select multiple field for the authors on the book view. We can replace it with a simple comma-separated list, which is easy to convert to JSON. Thus, we need a way to retrieve the names from the JSON field returning the comma-separated list. We can do this with a stored routine (function).
Listing 9-11 shows the CREATE statements for all the objects. If you want to follow along building this version of the application while you read, you should create a file named library_v2.sql so that you can recreate the database later.
Library Version 2 Database Create Script (library_v2.sql)
Note the new function named get_author_names(). The function retrieves the JSON document from the row matching the ISBN and creates a comma-separated list of authors. This is used in the presentation of the author data to make it easier for users to view.
Now that we’ve got the database created, let’s see the code for the database class.
Database Code
The code for working with the database is placed in a file named library_v2.py in the database folder under the version2 folder as described in Chapter 8 under the section, “Preparing the Directory Structure.” The code is based on version 1 converted to use the X DevAPI, and there is no longer a need for a class for the authors table. That said the code implements three classes: one for each of the data views—publisher and book—and another class for interfacing with the server. These classes are named Publisher, Book, and Library, respectfully.
The ALL_BOOKS query is considerably shorter and easier to maintain.
A new GET_PUBLISHER_NAME query is added to populate the book list.
The INSERT_BOOK query needs an additional column for the authors JSON document.
All the queries for the authors table are removed.
We change GET_AUTHOR_IDS to GET_AUTHOR_NAMES since we're only working with names in the JSON document.
The database name changes from library_v1 to library_v2.
To create the file, you can simply copy the file from version1/database/library_v1.py to version2/database/library_v2.py.
Code Deleted
Begin by deleting the Authors class and the SQL statements for the author table. They will not be needed.
SQL Strings
Because this version also uses SQL statements, place these in the preamble of the code module as strings that can be referenced later in the code. Listing 9-12 shows the preamble for the library_v2.py code module, which replaces what was used for the first version. Note that it begins with importing the MySQL Connector/Python X DevAPI library. The changes listed earlier (aside from the version 1 to 2 rename) are shown in bold in the listing.
Initialization and SQL Statements (library_v2.py)
If you recall the length of this same code from version 1, note that we’ve reduced the number of strings quite a lot. This is largely due to removing the authors table and the many-to-many relationship. So, adding a single JSON field has had a huge impact!
Before we discuss the changes to the Publisher and Book classes, let’s discuss the changes to the Library class.
Library Class
The library class is based on version 1, but because we’re using the X DevAPI, things will work quite differently. In particular, we will open a session to make a connection to the MySQL server on port 33060 (the default for the X Protocol), and we will use a SQLStatement object for executing SQL statements. The following summarizes the changes to the Library class.
We use a session object instead of a connection object.
The connect() function is changed to retrieve a session from the mysql_x library.
The sql() function is vastly simplified to only return the result from session.sql()—a SQLStatment object.
We add a make_rows() function to convert the row results from the SQLStatement object into an array.
The get_books() function calls the make_rows() function chaining the SQLStatement execute() function (passed as a parameter).
Note
The changes to the Library class are designed to demonstrate how to migrate from the old protocol to using the X DevAPI. As you will see, it is possible to minimize changes to existing database libraries as well as dependent code by using the same methods but with different database access methods.
Listing 9-13 shows the modified Library class. The changes from version 1 are shown in bold. Note that we have the same methods as the first version but instead of a connection object, we use a session object and the renamed functions to get and check the session. These are always good functions to include as you may need them as you develop more advanced features.
Library Class (library_v2.py)
Note how much shorter the sql() function is compared to version 1. Recall, the sql() function from version 1 was 30 lines in length. Using the SQLStatement object instance has saved us a lot of coding! We will see this theme continue in version 3. In fact, we see the get_books() function is also a bit shorter. Nice.
There is a new function as previously mentioned. The function, make_rows() takes the result object, fetches all of the rows, and converts it to a list. There may be more effective ways to do this, but this demonstrates some of what you may need to do to transition your existing code to use the X DevAPI.
Next, let’s look at the Publisher class.
Publisher Class
The Publisher class is nearly the same as the version 1 code except we adapt it for use with the X DevAPI. In particular, because we are getting a SQLStatement object returned from the sql() function in the Library class, we can chain that with the execute() function of the SQLStatement instance and get the result. We also utilize the make_rows() function of the Library class to make an array for the rows in a result. Listing 9-14 shows the complete code for the Publisher class with the changes shown in bold for clarity.
Publisher Class (library_v2.py)
As you can see, the changes are minimal and once again demonstrate how easy it is to migrate code to the new X DevAPI.
Now let’s look at the Book class, which has a similar short list of changes.
Book Class
We use the sql() function of the Library class chaining the execute() function to execute the SQL statement.
We prepare the SQLStatement object instance before we call the make_rows() function of the Library class.
We add a function make_authors_json() to convert a comma-separated list of author names to a JSON document.
We remove the code for working with the books_authors table.
Listing 9-15 shows the complete code for the Book class for clarity with the changes shown in bold. As you will see, despite adding more lines for working with JSON documents, the code is a bit shorter than the previous version.
Book Class (library_v2.py)
Note the new function, make_author_json(), which demonstrates how to build a JSON document. In this case, it is a simple JSON array built using the Python JSON encoder. We also see in the update() function how to incorporate the JSON document into our UPDATE SQL statement. Sweet!
That wasn’t too bad, was it? Now, let’s look at the changes to the application code.
Application Code
There are some minor changes in the application code from the version 1 code we saw earlier. This includes adapting the user interface to remove the authors view and add the authors list to the book view form. Fortunately, most of the code from version 1 can be used without modification.
Remove the Author class from the import statement.
Change the port from 3306 to 33060.
Remove the NewSelectMultipleField class as it is no longer needed (it was used in the book view form to show a list of authors to choose).
Remove the author view function and template.
Replace the multiple select field on the book detail page to a text field.
Remove the author list from the list view function.
Change the code to read a list of author names rather than ids.
Add the author list to create and update calls to the Book class.
Pass the list of author names from the new text field to the render_template() function in the book view function.
No changes are needed for the publisher view function, form class, or template.
No changes are needed for the list form class or template.
The base template was changed to indicate version 2 of the application.
We look at the changes starting with the changes to setup and initialization.
Setup and Initialization
Changes to the setup and initialization are trivial. We need only remove the Author class from the imports, change library_v1 to library_v2, and change the default port in the connect() function as shown in the following.
Form Classes
First, we can remove the AuthorForm and NewSelectMultipleField classes because we don't need them. It is fortunate that there are no changes needed for the PublisherForm class. Even the BookForm class has only a minor change to switch the multiple select field to a text field. Listing 9-16 shows the modified BookForm class code with the changes in bold. As you will see, it is only one line of code to change.
Book Form Class (Version 2)
View Functions
First, we can remove the author() view function as it is no longer needed. It is fortunate that there are no changes needed for the publisher view function.
However, we need to modify the simple_list() view function to remove the author list option. Listing 9-17 shows the modified template with the area where code was removed as shown in bold.
List View Function (Version 2)
We change the authorids list of ids to author_list to contain the comma-separated list.
We remove the Author() class code.
We change fetching a list of author ids to a list retrieving the comma-separated list.
We do not need the author list for the template file.
Listing 9-18 shows the changes to the book view function with the changes shown in bold.
Book View Function (Version 2)
No additional changes are needed for the application code. Once again, that wasn’t so bad. We’re not done just yet. There are some minor changes needed for the templates.
Templates
The changes to the template files are minor. If you haven’t already done so, copy the templates from version 1 to version 2. For example, copy all the files from version1/templates/* to version2/templates. Once copied, you can remove the author.html template as we no longer need it.
We also need to make two small changes to the base.html file to change the version number and remove the author list from the navigation bar. Listing 9-19 shows an excerpt from the base.html file with the changes shown in bold.
Base Template (Version 2)
We must also make two small changes to the book.html template to show a text field for the comma-separated list of authors. Listing 9-20 shows an excerpt of the modified template with the changes in bold.
Book Template (Version 2)
Ok, that’s it for the changes now let’s see the code in action.
Executing the Code
Now that we’ve got the code written, let’s give it a test drive. To execute the application, you can launch it with the Python interpreter specifying the runserver command. The following shows an example of executing the application. Note that we used the port option to specify the port. You should enter this command from the version2 folder.
Library application book list (version 2)
Book detailed view (version 2)
Note that the authors entry is now a text field instead of a multiple select list. Some may see this as more intuitive while others may feel the multiple select list is better. The comma-separated list was chosen for demonstration purposes, but feel free to experiment with your own ideas for how to collect and display information about the authors for a book.
The publisher view is unchanged from version 1.
Before we move on to version 3, let’s take a moment to discuss some observations about this version of the application.
Observations
Further simplify database with JSON: The notes table also can be converted to a JSON field in the books table because there is no need to query the notes table without viewing it in context with a book and one row in the notes table matches one and only one row in the books table.
The database code is shorter: We need less code in the database code module to implement the application.
The application code is shorter: We need less code in the application.
Some conversion code is needed for JSON: Although Python provides a library for working with JSON and it is possible to use JSON documents directly in Python as data structures, we need to add code to convert JSON to a more human readable form. In this case, it was working with a list of author names.
Author list may need to be improved: Although designed for demonstration purposes, the comma-separated list may not be the best choice for novice users.
Now, let’s look at the last version of the application.
Version 3: Document Store
This version implements a pure document store version of the data. For the purposes of demonstration, we will implement the database code in a code module that we can import into the application code. We will be using the X DevAPI managing a collection to store and retrieve the data. The goal is to demonstrate how to migrate to using JSON documents instead of the SQL interface.
To do this, we will flatten the database from multiple tables to a single collection of documents—more specifically, a collection of books. Let’s start with a brief overview of the design of the database.
Database Design
Calling this a database design is a little archaic as we aren’t working with a database logically but a schema in the X DevAPI terminology. Implementation wise it is still a database in MySQL and will show as such in the SHOW DATABASES command as shown in the following (library_v3) in the output from the MySQL Shell.
The database (schema) contains only one table, which was created as a collection. You can do this using the commands shown here in the MySQL Shell.
Note that we get a session then create the schema and finally create the collection. This new collection will appear in the library_v3 database as a table named books, but its CREATE statement looks very different. The following shows the CREATE statement for the table. You should never need to use this statement and should always use the MySQL Shell and X DevAPI to create schemas, collections, or any object in the X DevAPI pantheon.
Don’t worry that the CREATE statement looks strange. It’s supposed to look this way. After all, it is a collection implemented as a table of rows containing a document id and a JSON field. Note that there is a primary key defined so that will make lookups by id fast. Cool.
Now that we’ve got the schema (database) and collection (table) created, let’s see the code for the database class.
Database Code
The changes to the database code for this version of the application are a bit longer than the last version. Although we switched to using the X DevAPI in version 2, we were still using an SQL interface. This version uses a pure X DevAPI interface.
Add the JSONEncoder import statement.
Remove all the SQL statements (yes!).
Remove the Publisher class.
Rename the Book class to Books.
Change the Books class to use session, schema, and collection object instances.
Remove the make_authors_json() function.
Remove the Library class moving the utility functions to the Books class.
The database name changes from library_v2 to library_v3.
To create the file, you can simply copy the file from version2/database/library_v2.py to version3/database/library_v3.py.
Code Deleted
Begin by deleting the Publishers class and all the SQL statements. We won’t need those. You also will need to delete the Library class, but we will still use some of the methods in that class. See the section on the Books class for more details.
Setup and Initialization
Once you’ve deleted the SQL statements, we need to add an import statement to import the Python JSON encoder class. The following code should be placed at the end of the imports section.
Books Class
This class is where the rest of the code changes appear. The following sections briefly describe each of the changes followed by a complete listing of the modified class. Note that the first thing we do is change the class name from Book to Books because we are modeling a collection of books rather than a single book.
Tip
This is one of the fundamental “think” changes one must make when working with collections. Although you can model a single document, most will gravitate naturally to modeling a collection of document (things).
Class Declaration
The following shows the modified class declaration. Note that we renamed the class and comments to reflect the changes to the class model. Note we also declare two more class variables in the constructor; book_schema and book_col. The class variable book_schema is used to store an instance of the schema object and the book_col variable is used to store an instance of the collection object. These will be initialized in the connect() function.
Create Function
Next, we change the create() function to work better with a collection. In this case, we change the parameter list to include the three pieces of data for the publisher; name, city, and URL. We also use a function we will write to make the JSON complex document. The new function is named make_book_json() and will be described later. You also can remove the make_authors_json() function. Finally, we change the code to use the book_col class variable to add the book to the collection followed by a lookup for the document id of the new book. Recall, the document id is assigned by the server so retrieving it this way allows us to use it to quickly locate the document. Listing 9-21 shows the modified create() statement with the changed areas in bold.
Note
The indentation for the functions in the library class should be indented 4 spaces. Spaces are omitted in the listings for readability.
Create Function (Version 3)
Read Function
The read() function is greatly simplified now that we are using the collection object. Indeed, all we need to do is call the find() function passing in the document id and then fetch the document—all in one line of code! Auxiliary read functions from version 2 are removed, see the “Utility Functions” section later for more details.
Update Function
The update function is where a lot of code is changed. This is due to how we form the chain of modify() clauses for updating a collection. More specific, the code has been changed to detect when a data element is changed and if it has changed, call the modify().set().execute() chain to modify the data. Because we are doing more than one set of these changes potentially, we use the session class variable to start a transaction then if all statements succeed, commit it to the collection. If not, we rollback the changes.
The other changes have to do with how we handle the notes and author arrays. The publisher data is easy because we will place text boxes on the web page to hold the data. Listing 9-22 shows the modified create() function. Most of this code differs from version 2.
Update Function (Version 3)
Take some time to read through this code to ensure you see how we’ve gone from simply updating the entire data row to checking to see what items need to change and to set those. The nature of the X DevAPI is such that it enables (and encourages) such behavior as we only want to change what has changed and nothing else to save processing time (and more).
Delete Function
The delete() function also undergoes some change, but much less than the create() function. As with the read() function, the X DevAPI makes it much easier for us to perform a delete operation. Instead of having to execute a series of deletes as we did in version 1 and 2, we only need to use the remove_one() convenience function of the collection to find the book by document id and delete it. Nice! The following shows the modified delete() function.
Utility Functions
Remove functions no longer needed.
Move the connect() and get_books() functions from the old Library class to the Books.
Add new functions for working with JSON documents.
There are a lot of extra functions that are not needed for a pure document store code module. We deleted the make_authors_json(), read_notes(), add_note(), read_authors(), get_session(), is_connected(), disconnect(), make_rows(), and sql() functions from the library class as we do not need them anymore. Because the author and publisher data is part of the document, we treat the collection as an object rather than a gateway to a database server.
The connect() function requires some minor changes to allow us to work with the session object. Listing 9-23 shows the modified connect() function with the changes in bold. This function is moved to the Books class. Here, we attempt to get a session using the connection parameters passed (note there is no database parameter), then get the schema object for the library_v3 schema and finally get the collection object for the books collection.
Connect( ) Function (Version 3)
The get_books( ) function is simplified over version 2 because instead of issuing an SQL statement read a book, we use the collection object to find all of the books. We also use a rewrite of an older function to return an array of documents that we can use in Python. This new function is named make_row_array() and will be explained in the next sections.
make_authors_str(<array>): Given an array of authors, return a comma-separated list of first name last name.
make_authors_dict_list(<string>): Given a comma-separated list of author names, return a list (array) of dictionaries containing author first and last names.
make_book_json(<params>): Given a parameter list of the data from the fields on the web page, return a JSON document populated with the data.
make_row_array(<array or JSON documents>): Given an array of JSON documents, return an array of dictionaries containing a subset of the JSON document elements. Note that this is used to show the list of books in the collection.
Completed Code
Because there are a lot of changes necessary to this version of the database code and the Books class in particular, it is a good idea to see the completed code in its entirety. Listing 9-24 shows the complete code for the Books class. You can study this code to see how the many changes above were implemented.
Books Class (Version 3)
Wow, that was a lot of changes! This version shows how much different the code for working with collections is from even a hybrid solution. Now, let’s look at the changes to the application code.
Application Code
Import the Books class from the library_v3 module.
Switch the Library class to the Books class and call the connect() function.
Remove the NewSelectField() class.
Remove the PublisherForm() class.
Change the BookForm class to list the publisher data as fields.
Change the BookForm class to add hidden fields for the document id and JSON string.
Remove the publisher option from the ListForm template.
Remove the publisher view function.
Modify the book view function to work with the JSON document.
The following sections show the details of the three major areas for changes: setup and initialization, form class, and view functions. To create the file, you can simply copy the file from version1/database/library_v2.py to version2/database/library_v3.py.
Setup and Initialization
The changes to the setup and initialization sections are minor. We must import the Books class from the library_v3 code module and change the code to use the Books() object instead of the Library() object in version 2. The following shows the changes in bold.
Form Classes
The form classes changes are also minor. First, we delete the NewSelectField() and PublisherForm() classes as we don’t need them anymore. Second, we must modify the BookForm() form class to use text fields for the publisher data. Recall, this is name, city, and URL. We also want to add two hidden fields: one for the document id, and another for the JSON document.
The document id will be critical in making it easy to retrieve or update the JSON document and the JSON document stored in the form will allow us to detect when data has changed. Recall from the discussion of the Books class in the database code module we do exactly that in the update() function. Using hidden fields to contain data like this is common, but you should use the technique sparingly because data in hidden fields are like any other field—you must ensure you update the data in your code otherwise you could be working with stale data.
Listing 9-25 shows the updated BookForm for class with changes shown in bold.
Book Form Class (Version 3)
Book View Function
Change publisher list to fields.
Remove populating the publisher select field.
Change the variable for the route from the ISBN to document id.
Use the books() instance instead of book().
When retrieving data from the collection, use the JSON document directly in Python accessing data items by array index and dictionary keys.
Detect when data elements are missing for optional fields.
Call the CRUD functions with the modified parameter lists adding the publisher fields.
As mentioned, the book view function requires modification to include publisher data that is now represented as fields, so we no longer have to populate a select field thereby simplifying the code a bit.
Because the data is now in JSON, we can use the document id as the key thereby eliminating the concern over users changing the primary key (e.g. the ISBN). In fact, using JSON documents allows users to change any field (or add new ones) without creating problems with keys and indexes. Neat!
When retrieving information from the books collection, we have a JSON document that we can access the data in Python as if it were a big dictionary. For example, we can access data items by name like data["ISBN"] where ISBN is the key in the dictionary, data. Nice! We see these changes in the section after the if id_selected: conditional. For those fields that are optional, we can check the dictionary (JSON object) to see if the key exists and if it does, retrieve the data.
We also see where we’ve added assignments to save the document id and the original JSON document to the hidden fields. Finally, we must also make a small change to how we call the CRUD functions as we have the extra parameters for the publisher data. Listing 9-26 shows the complete, modified code for the book view function with the modified sections in bold.
Book View Function (Version 3)
Finally, we can remove the publisher() view function and the publisher section in the simple_list() view as we don’t need those either.
Templates
The changes to the template files are minor. If you haven’t already done so, copy the templates from version 2 to version 3. For example, copy all the files from version2/templates/* to version3/templates. Once copied, you can remove the publisher.html template as it is no longer needed.
We also need to make two small changes to the base.html file to change the version number and remove the publisher list from the navigation bar. Listing 9-27 shows an excerpt from the base.html file with the changes shown in bold.
Base Template (Version 3)
We also must make small changes to the book.html template to show a text field for the comma-separated list of authors. Listing 9-28 shows an excerpt of the modified template with the changes in bold.
Book Template (Version 3)
Ok, that’s it for the changes; now let’s see the code in action.
Executing the Code
Now that we’ve got the code written, let’s give it a test drive. To execute the application, you can launch it with the Python interpreter specifying the runserver command. The following shows an example of executing the application. Note that we used the port option to specify the port. You should enter this command from the version3 folder.
Library application book list (version 3)
Book detailed view (version 3)
Now, let’s take a moment to discuss some observations about this version of the application.
Observations
Database code much shorter: We need less code for working with collections and documents using the X DevAPI.
Database code easier to understand: Working with JSON documents are a natural extension of Python (and other languages).
Application code significantly shorter: We need less code for the application because we simplified the user interface. In fact, the code is almost 50% of the size of version 1.
There is one other observation that bears discussing. The changes to the user experience using the three versions of the application are minor. In fact, one of the goals was to keep the user interface changes to a minimum to demonstrate that migrating from a traditional relational database model to a hybrid and ultimately a pure document store model does not mean one must redesign the entire user interface!
Although there may be some changes necessary to facilitate changes in how the data is stored and retrieved—like what we saw with the authors and books_authors join tables, the changes often help solve problems with the database design or in this case help eliminate a false premise that the specific author and publisher data is meaningful outside the context of a book. In this example application, it was not. So, designing separate tables (or documents) for storing the information wasn’t necessary and added complexity we didn’t need. Such are the challenges and rewards of a designing your data around JSON documents and the MySQL document store engine.
Challenges
ISBN lookup service: Add the ability to retrieve information about books using an ISBN lookup service such as isbntools ( http://isbntools.readthedocs.io/en/latest/ ) or SearchUPC ( http://www.searchupc.com ). Some services require creating accounts while others may be fee-based services.
Separate library modules: Break the library file into separate code modules (book, author, publisher, library) for version 1 and 2.
Separate code modules: Larger Flask applications typically break out the views (form classes) in the main code file into separate code modules.
Remove hardcoded values: Make the user, password, host, and port data for the MySQL parameters rather than hard coded values (hint: use argparse).
Expand the data: Modify the database or document store to store additional media such as magazines, articles, and online references.
Summary
There is no doubt that the new MySQL 8 release is set to be the biggest, most significant release in MySQL history. The addition of the JSON data type and the X DevAPI is simply groundbreaking for MySQL applications.
In this chapter, we explored the differences between a relational database solution and a relational database solution augmented with JSON fields, and finally a pure document store solution. As we discovered, the new X DevAPI makes developing MySQL solutions easier, faster, and with less code than a relational database solution. This gives us many reasons to start adopting the document store going forward.
In Chapter 10, I conclude my exploration of the MySQL 8 Document Store with a look at how you can prepare your existing and future application plans to incorporate the document store. This includes notes about upgrading to MySQL 8 and tips for how to migrate to document store solutions.