TDM 20100: Project 13 — 2022

Begin by copying the database from the previous project to your $HOME directory. Open up a terminal and run the following.

Go ahead and launch sqlite3 and connect to the database.

From within sqlite3, test things out to make sure the data looks right.

With any luck, things should be working just fine.

Let’s go ahead and create a trigger. A trigger is what it sounds like, given a specific action, do a specific action. This is a powerful tool. One of the most common uses of a trigger that you will see in the wild is the "updated_at" field. This is a field that stores a datetime value, and uses a trigger to automatically update to the current date and time anytime a record in the database is updated.

First, we need to create a new column called "updated_at", and set the default value to something. In our case, lets set it to January 1, 1970 at 00:00:00.

If you query the table now, you will see all of the values have been properly added, great!

Now add a trigger called "update_updated_at" that will update the "updated_at" column to the current date and time whenever a record is updated. Check out the official documentation here for examples of triggers.

Once your trigger has been written, go ahead and test it out by updating the following record.

If it worked right, your updated_at column should have been updated to the current date and time, cool!

Next, we will touch on views. A view is essentially a virtual table that is created from some query and given a name. Why would you want to create such a thing? Well, there could be many reasons.

Maybe you have a complex query that you need to run frequently, and it would just be easier to see the final result with a click? Maybe the database has horrible naming conventions and you want to rename things in a view to make it more readable and/or queryable?

After some thought, it may occur to you that we’ve had such an instance where a view could be nice using our imdb.db database!

Copy the imdb.db to your $SCRATCH directory, and navigate to your $SCRATCH directory.

Sometimes, it would be nice to have the rating and votes from the ratings table available directly from the titles table, wouldn’t it? It has been a bit of a hassle to access that information and use a JOIN whenever we’ve had a need to see rating information. In fact, if you think about it, the rating information living in its own table doesn’t really make that much sense.

Create a view called titles_with_ratings that has all of the information from the titles table along with the rating and votes from the ratings table. You can find the official documentation here.

Read the offical sqlite3 documentation for transactions here. As you will read, you’ve already been using transactions each time you run a query! What we will focus on is how to use transactions to rollback changes, as this is probably the most useful use case you’ll run into.

Connect to our flights_sample.db database from question (1), start a deferred transaction, and update a row, similar to what we did before, using the following query.

Now, query the record to see what it looks like.

This is pretty great, until you realized that the year should most definitely not be 7777, but rather be 5555. Oh no! Well, at this stage you haven’t committed your transaction yet, so you can just rollback the changes and everything will be back to normal. Give it a try (again, following the official documentation).

After rolling back, run the following query.

As you can see, nothing appears! Let’s try with the correct year.

Nice! Note only was our Year field rolled back to the original values after question (1), but our updated_at field was too, excellent! As you can imagine, this is pretty powerful stuff, especially if you are writing to a database and want to make sure things look right before committing the changes.

SQL and sqlite3 are powerful tools, and we’ve barely scratched the surface. Check out the offical documentation, and demonstrate another feature of sqlite3 that we haven’t yet covered.

Some suggestions, if you aren’t interested in browsing the documentation: window functions, math functions, date and time functions, and core functions (there are many we didn’t use!)

Please make sure the queries you run are run from an sql cell in your Jupyter notebook.

There are two other interesting features of sqlite3: savepoints (kind of a named transaction) and attach and detach. Demonstrate one or both of these functionalities and write 1-2 sentences stating whether or not you think they are practical or useful features, and why or why not?