A11.5 SQL: changing data and referential integrity

The starter builds two tables, robots and runs. runs.robot_id refers to robots(robot_id) with ON DELETE CASCADE. Robot 1 is Ada (the robot on the mat) and robot 2 is Bolt, who already has run 1. legs is a list of (move, cm), where move is "forward", "right" or "backward" and cm is a whole number of centimetres. 1. Switch foreign key checking on. 2. For each leg in order, drive it at speed 40, measure how far the robot really moved from position() before and after (the straight-line distance, rounded to a whole number), and INSERT it as a run for robot 1 with a field list and ? placeholders, letting the database number it. 3. Try to insert a run for robot 9. When the database refuses with sqlite3.IntegrityError, print refused: robot 9 does not exist. 4. UPDATE the runs whose move is right so their move is strafe. 5. DELETE Bolt from robots. 6. With a query that joins runs to robots, print every run in run order as run <run_id> <name> <move> <cm>, then print runs: <n>, the number of records left in runs. Five lines in all, starting with the refusal.

# the two lines every program starts with: the commands, then the robot
from bugbot import *
connect()

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE robots (robot_id INTEGER PRIMARY KEY, name TEXT NOT NULL)")
db.execute("""CREATE TABLE runs (run_id INTEGER PRIMARY KEY,
              robot_id INTEGER NOT NULL REFERENCES robots(robot_id) ON DELETE CASCADE,
              move TEXT, cm INTEGER)""")
db.executemany("INSERT INTO robots VALUES (?, ?)", [(1, "Ada"), (2, "Bolt")])
db.execute("INSERT INTO runs VALUES (1, 2, 'forward', 30)")
db.commit()

legs = [("forward", 20), ("right", 15), ("backward", 10)]

The hint students can ask for: Switch the checking on before anything else touches the database. Measure each leg from position() before and after it. The refusal and the vanishing of Bolt's run should both come from the database, not from if statements of your own.

A solution

# the two lines every program starts with: the commands, then the robot
from bugbot import *
connect()

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE robots (robot_id INTEGER PRIMARY KEY, name TEXT NOT NULL)")
db.execute("""CREATE TABLE runs (run_id INTEGER PRIMARY KEY,
              robot_id INTEGER NOT NULL REFERENCES robots(robot_id) ON DELETE CASCADE,
              move TEXT, cm INTEGER)""")
db.executemany("INSERT INTO robots VALUES (?, ?)", [(1, "Ada"), (2, "Bolt")])
db.execute("INSERT INTO runs VALUES (1, 2, 'forward', 30)")
db.commit()

legs = [("forward", 20), ("right", 15), ("backward", 10)]
db.execute("PRAGMA foreign_keys = ON")
moves = {"forward": forward, "right": right, "backward": backward}

for move, cm in legs:
    x0, y0 = position()
    moves[move](40, distance=cm)
    x1, y1 = position()
    moved = round(((x1 - x0) ** 2 + (y1 - y0) ** 2) ** 0.5)
    db.execute("INSERT INTO runs (robot_id, move, cm) VALUES (?, ?, ?)", (1, move, moved))

try:
    db.execute("INSERT INTO runs (robot_id, move, cm) VALUES (?, ?, ?)", (9, "forward", 5))
except sqlite3.IntegrityError:
    print("refused: robot 9 does not exist")

db.execute("UPDATE runs SET move = 'strafe' WHERE move = 'right'")
db.execute("DELETE FROM robots WHERE robot_id = 2")

for run_id, name, move, cm in db.execute("""SELECT runs.run_id, robots.name, runs.move, runs.cm
                                           FROM runs JOIN robots ON runs.robot_id = robots.robot_id
                                           ORDER BY runs.run_id"""):
    print("run", run_id, name, move, cm)
print("runs:", db.execute("SELECT COUNT(*) FROM runs").fetchone()[0])

Any program that meets the task's checks is marked correct in the simulator; this is one way, not the only way.