Converting Classic SQL Labs to MySQL Advanced Labs

New MySQL zyBooks should use advanced zyLabs by default. These include Main.sql files that reference the required Initialize.sql file automatically. The guide below is for Classic zyLab conversion only.

Any classic zyLabs being converted from Classic to Advanced Labs require an extra step before they're ready for learners. First, convert the Classic SQL lab by clicking the Clone button at the top of the section.

Select Advanced, and a copy of the section with an advanced lab will be created.

Test cases are not transferred with this conversion. Instructions for creating tests are below.

The Initialize.sql file inside of that workspace creates database data necessary for the workspace to function. The Initialize file needs to be run before the workspace is ready for learners.

The method used in our MySQL ZML conversions is the fastest way to alter your converted workspace to work properly on run and with the test bench. First, click edit at the top of the page to enter edit mode. 

Open the workspace settings, and remove Initialize.sql from read-only files:

Now, the Initialize.sql can be edited. In our MySQL textbooks, this includes a line like this one to drop existing tables:

-- Initialize tables
DROP TABLE IF EXISTS Horse;

Next, the Main.sql can be edited to include a line to source the Initialize.sql:

-- Initialize database
source Initialize.sql

Repeat these steps in the model solution to get it working.

Alternatively, in the template, enter this command in the console then hit enter to setup the database: mysql zybooksdb -t < Initialize.sql

The template is now ready. That console command would need to be run in the model solution as well. If the Initialize or Main SQL files create already existing data, then running Main.sql or running the test bench, will throw an error that that data already exists. 

Test Bench

Output Tests

Output tests are an easy way to confirm the SQL data is correct. This can include input commands to look for specific data, or simply checking table data like the output test below.

The 'Generate output from solution' button can be used to run the model solution and duplicate the output into the test.

Use the input section to pass in any SQL commands and the output to gather the expected result of those commands.

Unit Tests

Unit tests can perform more complex interactions with the database, including connecting and running scripts, like the unit test example below. Note the information in the mysql.connector, as these will be the same needed to connect to learner workspaces.

import mysql.connector
import sys
import time
import datetime, decimal

def test_passed(test_feedback):
    points = 0

    # Connect to database
    num_of_tries = 0
    while num_of_tries <= 3:
        try:
            db = mysql.connector.connect(host="127.0.0.1", user="root", db="zybooksdb")
            if (db.is_connected()):
                break
        except Exception as e:
                pass

        if num_of_tries > 2:
            test_feedback.write('FAIL: MySQL did not start. Please try again.')
            return points

        num_of_tries += 1
        time.sleep(2)

    cursor = db.cursor()
    # Get initialize script
    with open('Initialize.sql', 'r') as f_init:
        initialize_script = f_init.read()
    
    # Run student script
    with open('Main.sql', 'r') as f_main:
        student_script = f_main.read()
    student_script = student_script.replace("source Initialize.sql", "")
    test = []
    for result in cursor.execute(initialize_script + student_script, multi=True):
        test += result.fetchall()

    # Test for Horse table
    cursor.execute('show tables;')
    test = cursor.fetchall()
    if ('horse',) not in test:
        test_feedback.write('FAIL: Student table not found')
        return points
    ##############################################################################
    cursor.execute("select RegisteredName, Breed, Height, BirthDate from Horse;")
    test = cursor.fetchall()

    test_list = [
        ('Babe', 'Quarter Horse', decimal.Decimal('15.3'), datetime.date(2015, 2, 10)), 
        ('Independence', 'Holsteiner', decimal.Decimal('16.0'), datetime.date(2017, 3, 13)), 
        ('Ellie', 'Saddlebred', decimal.Decimal('15.0'), datetime.date(2016, 12, 22)), 
        (None, 'Egyptian Arab', decimal.Decimal('14.9'), datetime.date(2019, 10, 12))
    ]
    for result in test_list:
        name = str(result[0])
        if result[0] is None:
            name = 'NULL'
        if (result in test):
            output_feedback = '+2 PASS: Horse ' + name + ' correctly inserted into Horse table'
            test_feedback.write(output_feedback)
            points += 2
        else:
            test_feedback.write('+0 FAIL: Horse ' + name + ' was not inserted correctly')

    if (cursor.rowcount == 4):
        test_feedback.write('+2 PASS: Table has 4 rows')
        points += 2
    else:
        test_feedback.write('+0 FAIL: Table does not have 4 rows')
    return points

MySQL Unit tests default to including workspace data from the student's workspace. If the database from the template is needed for a specific test, open the file options inside the test to view the settings and change them.