path: root/06-data-manipulation-in-sql/data-manipulation-in-sql.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "83699cc2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import agh_db_lectures\n",
    "agh_db_lectures.prepare_notebook_for_sql()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c578a63",
   "metadata": {},
   "source": [
    "# Data manipulation in SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e139463",
   "metadata": {},
   "outputs": [],
   "source": [
    "agh_db_lectures.download_install_wordpress_site()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f9fe2b6",
   "metadata": {},
   "source": [
    "## Our site\n",
    "\n",
    "http://localhost:28080/site"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d3cebcd",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "So far we've been learning on a toy database.  Now we are going to work with a database schema that is used **a lot** in the wild.\n",
    "\n",
    "Our demo site is made with Wordpress, one of the most popular (and most often exploited, btw) tools for website creation."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b3eccdd",
   "metadata": {},
   "source": [
    "## Wordpress database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "011634f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql mysql:///agh_it_wordpress?unix_socket=/var/run/mysql/mysql.sock"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3fcfdfb9",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "Wordpress famously supports only MySQL (or its fork MariaDB) as its DMBS.  If a WP site is run with another database engine, it is through unofficial forks or plugins.\n",
    "\n",
    "Note that we are not connecting to the databse over network this time.  We communicate with the DBMS using a special file called \"socket\".  Both Postgres and MariaDB/MySQL (and also many other DBMSes) allow both network and socket connections."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0954062d",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SHOW TABLES"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0baca123",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "Relational DMBSes typically allow querying the set of tables in the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0f25300e",
   "metadata": {},
   "outputs": [],
   "source": [
    "agh_db_lectures.wp_diagram.download_display()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a8bc4c6d",
   "metadata": {},
   "outputs": [],
   "source": [
    "agh_db_lectures.wp_diagram.download_open()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e40ddcc7",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "An entity relationship diagram of the database is additionally available online and we'll use it to our convenience."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "50480c29",
   "metadata": {},
   "source": [
    "## Users table and (not) storing of passwords"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88fa43ca",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "select * from wp_users"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36b6657b",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "Let's look at the single user row in our site's db.  It contains data of the initial user registered from `agh_db_lectures.py`.\n",
    "\n",
    "Important digression: note that password is not stored in plaintext on the server.  A password hash is stored instead.  This is is a good security practice.\n",
    "\n",
    "Upon a login attempt, the provided password is also hashed and the result compared to the hash stored in the db.\n",
    "\n",
    "Note that this is not the only possible password-based login scheme that avoids storing the password."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "277762ca",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT user_login,\n",
    "       SUBSTR(user_pass, 4, 29) AS salt,\n",
    "       SUBSTR(user_pass, 33) AS hash\n",
    "FROM wp_users"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d87a3f2f",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "WP and many other systems store each password hash together with a sequence of bytes called **salt**.  The salt contains some random bytes and is hashed together with the password.  This way, even if the same password is used in multiple systems, hashes stored there shall differ nevertheless.\n",
    "\n",
    "Note the use of `SUBSTR()` builtin function.  It can be used in the same fashion under Postgres.\n",
    "\n",
    "In thi case, the salt also includes configuration of the hashing procedure.\n",
    "\n",
    "- The number of repeated hash function applications (here, 10).\n",
    "- The cryptographic hash function code (here, `2y`, indicating blowfish cipher)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7fab148",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import base64\n",
    "import bcrypt\n",
    "import hmac\n",
    "import hashlib\n",
    "\n",
    "def hash_wp_pwd(pwd, salt):\n",
    "    pwd_bytes = pwd.encode('utf-8')\n",
    "\n",
    "    pwd_hmac = hmac.digest(b'wp-sha384', pwd_bytes, 'sha384')\n",
    "    pwd_hmac_b64 = base64.b64encode(pwd_hmac)\n",
    "\n",
    "    return bcrypt.hashpw(base64.b64encode(pwd_hmac), salt)\n",
    "\n",
    "hash_wp_pwd(\n",
    "    'demo_pwd',\n",
    "    b'' # Paste salt from the output of the code cell above.\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e87585c6",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "A Quick example.  The password used by _demo_user_ is \"demo_pwd\".  We can replicate the hashing procedure of WP in Python.  Note that this example is here for demonstrative purposes, you do not need to understand, e.g., what HMAC is."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a66d7dd",
   "metadata": {},
   "source": [
    "## Inserting data into table in SQL\n",
    "\n",
    "Suppose we want to programatically add a user to the site."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9592737",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "Theodore, how do you think, why could we want to write code that adds a user to a Wordpress site?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d4b87ae8",
   "metadata": {},
   "outputs": [],
   "source": [
    "def gen_hash_with_salt_for_wp(pwd):\n",
    "    salt = bcrypt.gensalt(rounds=10).replace(b'$2b', b'$2y')\n",
    "    \n",
    "    return b'$wp' + hash_wp_pwd(pwd, salt)\n",
    "\n",
    "gen_hash_with_salt_for_wp('') # Type a password."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1665aa97",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "Theodore, are you from Cybersecurity or another course?  Theodore please pick a password for your account.\n",
    "\n",
    "We get salt concatenated with the password hash.  Let's run the code again to simulate another system hashing the same password.  As one can see, the hashes are going to be different."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "41e360c0",
   "metadata": {},
   "source": [
    "The basic syntax for adding a row to an SQL table is as follows.\n",
    "\n",
    "```sql\n",
    "INSERT INTO table_name\n",
    "VALUES('column_1_value', 'column_2_value', 'etc.');\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0ab00b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "INSERT INTO wp_users\n",
    "VALUES(\n",
    "    2,                             -- ID\n",
    "    'theodore',                    -- user_login\n",
    "    -- Paste below the value produced by the code cell above.\n",
    "    '',\n",
    "    'Theodore',                    -- user_nicename\n",
    "    'theo@domain.invalid',         -- user_email\n",
    "    'http://127.0.0.1:28080/site', -- user_url\n",
    "    NOW(),                         -- user_registered\n",
    "    '',                            -- user_activation_key\n",
    "    0,                             -- user_status\n",
    "    'Theodore'                     -- display_name\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85b48320",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "Please do not feel required to bother yourself with understanding the semantic of each of the columns.\n",
    "\n",
    "Fill in the hashed password.\n",
    "\n",
    "The cell below can be used to verify that the row has been added.\n",
    "\n",
    "Note that we cannot have duplicates in the table, the ID of a user is mandated to be unique.  If we re-run the user creation code without changing the ID, it results in an error.\n",
    "\n",
    "Note how the correct behavior of the code depends on the order of columns in the table.\n",
    "\n",
    "Theodore, we'd like to add one more user to the site, in a different way.  Please choose a username for your friend.\n",
    "\n",
    "We can insert a row while specifying values only for certain columns and allowing the DBMS to use default values for the rest.\n",
    "Change the `INSERT` line as follows.\n",
    "\n",
    "```sql\n",
    "INSERT INTO wp_users(-- ID,\n",
    "                     -- user_registered,\n",
    "                     -- user_activation_key,\n",
    "                     -- user_status,\n",
    "                     user_url,\n",
    "                     user_login,\n",
    "                     user_pass,\n",
    "                     user_nicename,\n",
    "                     user_email,\n",
    "                     display_name)\n",
    "```\n",
    "\n",
    "Remove or comment-out the values previously used for `ID`, `user_registered`, `user_activation_key`, and `user_status`.\n",
    "Adapt the values for `user_login`, `user_pass`, `user_nicename`, `user_email`, and `display_name`.  For `user_pass`, `user_nicename`, and `user_email` you can use the values below.  These will allow logging in with the password \"demo_pwd\".  Note how double apostrophe can be used in SQL to create a string that itself contains an apostrophe.\n",
    "\n",
    "```sql\n",
    "    '$wp$2y$10$GHqeJUGnTkGIVZUFrSRri.P3qpTlpJLWmIFiuH3T76toBn1LN3qaK',\n",
    "    'Theodore''s Friend',          -- user_nicename\n",
    "    'friend-of-t@domain.invalid',  -- user_email\n",
    "```\n",
    "\n",
    "Look at the inserted row in the output of cell below.  Note how the `ID` column automagically got the next natural number.  It is common to create tables with such automatic id sequences.\n",
    "\n",
    "Note that the table had no explicitly configured default value for the `user_registered` column.  The implicit default is the `NULL` value.  Note the defaults that were used for the other 2 columns: an empty string and the number 0, respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8840d9ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT * FROM wp_users"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e414dbf",
   "metadata": {},
   "source": [
    "Log in at http://127.0.0.1:28080/site/wp-login.php."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b63d503c",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "We can successfully login as one of the new users.  However, we do not have priveleges to do anything on the website."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e5782606",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT * FROM wp_usermeta"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4dfcf0c",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "Some configuration of our users is stored in the `wp_usermeta` table.  Btw, this is a non-relational key-value store implemented on top of a relational database :)\n",
    "\n",
    "There are quite a few rows corresponding to the initial user.  We could add analogous rows for the other users to grant them admin privileges and other sensible default.\n",
    "\n",
    "Let's modify this query to return the set of rows that we'd like to see added."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "70ad9181",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM wp_usermeta"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d05a7b6c",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "Firstly, the `session_tokens` key is responsible for current logins.  It'd be better not to include it.  Same with `first_name` and similar keys.  Add the code below.\n",
    "\n",
    "```sql\n",
    "WHERE user_id = 1 AND meta_key NOT IN (\n",
    "    'description',\n",
    "    'first_name',\n",
    "    'last_name',\n",
    "    'nickname',\n",
    "    'session_tokens'\n",
    ")\n",
    "```\n",
    "\n",
    "Looks good.  We'd now want each of these rows to appear once with `user_id` of 2 and once with `user_id` of 3.\n",
    "\n",
    "```sql\n",
    "SELECT umeta_id, id AS user_id, meta_key, meta_value\n",
    "FROM wp_usermeta CROSS JOIN (SELECT 2 AS id UNION SELECT 3) ids\n",
    "```\n",
    "\n",
    "Finally, let's get rid of the `umeta_id` column.\n",
    "\n",
    "Those are the entries we'd like to be present for our new users.  SQL allows the result of a query to be used in an `INSERT` command in place of the `VALUES` clause.  Add the following in front of the query.\n",
    "\n",
    "```sql\n",
    "INSERT INTO wp_usermeta(user_id, meta_key, meta_value)\n",
    "```\n",
    "\n",
    "Theodore, is it clear what we are trying to achieve here?\n",
    "\n",
    "Let's check the result in the code cell above (optionally adding `WHERE meta_key <> 'session_tokens'` to make the table narrower and thus more readable).  Then, try to log in again as Theodore or Theodore's friend."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2b1745e",
   "metadata": {},
   "source": [
    "### The \"RETURNING\" keyword"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "319354ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "INSERT INTO wp_usermeta(user_id, meta_key, meta_value)\n",
    "VALUES(3, 'last_name', '') -- Fill in the last name.\n",
    "RETURNING umeta_id"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51480f2e",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "It is convenient to have the database choose an id for the new row and return its value from the `INSERT` query.  The `RETURNING` keyword can be used for this.\n",
    "\n",
    "`RETURNING` is nonstandard but supported by several highly popular database engines.\n",
    "\n",
    "We shall add 1 more row to `wp_usermeta` to demonstrate this.  Theodore, what is your friend's surname?\n",
    "\n",
    "Fill in the surname and run the query."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ac268e0",
   "metadata": {},
   "source": [
    "## Modifying existing rows"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "94251bb8",
   "metadata": {},
   "source": [
    "Let's write a new post on the website, then log out and add an anonymous comment or 2 under it."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a5c07de",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "When we make a comment, it is not displayed automatically.  Approval of a moderator is needed.  Let's look into the `wp_comments` table and see how this is implemented."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "22392170",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM wp_comments"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8dadc948",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "The `comment_approved` column appears relevant.  It holds value 1 for approved comments and value 0 for unapproved ones.\n",
    "\n",
    "Note that MySQL/MariaDB lacks a true `BOOLEAN` type.  Instead, wherever a programmer tries to use the `BOOLEAN` type, a small integer type is used instead.  Try with `SELECT TRUE;`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b1977b5",
   "metadata": {},
   "source": [
    "The SQL syntax for changing rows is as below.\n",
    "\n",
    "```sql\n",
    "UPDATE table_name\n",
    "SET column_1 = 'value 1',\n",
    "    column_2 = 'value 2'\n",
    "    -- , etc.\n",
    "WHERE some_condition\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb403bc2",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "UPDATE -- Fill in.\n",
    "SET -- Fill in.\n",
    "WHERE -- Fill in."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7503e2f",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "Theodore, for which comment do we want to set `comment_approved` to 1?  Maybe both?\n",
    "\n",
    "Theodore, what do you wish to use in the `WHERE` condition?  The comment id or its author's name or email?  Or maybe sth else?\n",
    "\n",
    "Craft a command like below.\n",
    "\n",
    "```sql\n",
    "UPDATE wp_comments\n",
    "SET comment_approved = 1\n",
    "WHERE comment_id = 3\n",
    "```\n",
    "\n",
    "The respective row now has value 1 in that column and the comment is displayed to visitors.  Theodore, is this what we wanted?\n",
    "\n",
    "Of course an attempt to put an invalid value in a row will result in an error.  Try, for example, the code below.\n",
    "\n",
    "```sql\n",
    "UPDATE wp_comments\n",
    "SET comment_id = 1\n",
    "WHERE comment_id <> 1\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b16fb5fd",
   "metadata": {},
   "source": [
    "## Removing rows"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80b33d20",
   "metadata": {},
   "source": [
    "We can use a plugin like Fakerpress to generate a lot of spammy posts and comments on our site."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "100ec73f",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "It needs to be first activated from WP dashboard.  About ~20  generated posts and ~100 comments should be enough for our experiments."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f905c3e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT ID, post_date, post_title FROM wp_posts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "702909d4",
   "metadata": {},
   "source": [
    "The syntax for row deletion from SQL table is as follows.\n",
    "\n",
    "```sql\n",
    "DELETE FROM table_name\n",
    "WHERE some_codition\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3cba6f06",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "DELETE FROM -- fill in\n",
    "WHERE -- fill in"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7f4593b",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "Theodore, what attribute of `wp_posts` would you like to use in our SQL command to determine which rows to remove?  `ID`, `post_date`?  Or maybe sth else?\n",
    "\n",
    "Run a command like below.\n",
    "```sql\n",
    "DELETE FROM wp_posts\n",
    "WHERE ID > 5\n",
    "```\n",
    "\n",
    "Note that data manipulation commands (not just `DELETE`, even though we mention this here) can also utilize subqueries.  E.g. `WHERE ID > (SELECT 5)` would work just as well in this case.\n",
    "\n",
    "Note that MariaDB and Postgres also support `RETURNING` with `DELETE`.\n",
    "\n",
    "```sql\n",
    "DELETE FROM wp_comments\n",
    "WHERE comment_id > 3\n",
    "RETURNING comment_id, comment_post_id\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "42431b23",
   "metadata": {},
   "source": [
    "## The \"UPSERT\" functionality"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "501ac902",
   "metadata": {},
   "source": [
    "E.g., try to insert row(s), but update them instead if already present."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a642c1e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "INSERT INTO wp_users\n",
    "VALUES(\n",
    "    10000001,                      -- ID\n",
    "    'wp_bot',                      -- user_login\n",
    "    '$wp$2y$10$GHqeJUGnTkGIVZUFrSRri.P3qpTlpJLWmIFiuH3T76toBn1LN3qaK',\n",
    "    'Bot',                         -- user_nicename\n",
    "    'bot@domain.invalid',          -- user_email\n",
    "    'http://127.0.0.1:28080/site', -- user_url\n",
    "    NOW(),                         -- user_registered\n",
    "    '',                            -- user_activation_key\n",
    "    0,                             -- user_status\n",
    "    'Bot'                          -- display_name\n",
    ")\n",
    "ON DUPLICATE KEY UPDATE\n",
    "    user_login = 'wp_bot',\n",
    "    user_pass = '$wp$2y$10$GHqeJUGnTkGIVZUFrSRri.P3qpTlpJLWmIFiuH3T76toBn1LN3qaK',\n",
    "    user_nicename = 'Bot',\n",
    "    user_email = 'bot@domain.invalid',\n",
    "    user_url = 'http://127.0.0.1:28080/site',\n",
    "    user_registered = NOW(),\n",
    "    user_activation_key = '',\n",
    "    user_status = 0,\n",
    "    display_name = 'Bot'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "551f2580",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "Theodore, is it clear what this command does?\n",
    "\n",
    "This `UPSERT` functionlity does not belong to standard SQL.  Postgres supports something analogous with slightly different syntax.  Here we used these words.\n",
    "\n",
    "```sql\n",
    "ON DUPLICATE KEY UPDATE\n",
    "```\n",
    "Under Postgres we would instead write.\n",
    "\n",
    "```sql\n",
    "ON CONFLICT DO UPDATE SET\n",
    "```\n",
    "\n",
    "Despite the frequently used name of this functionality, no language keyword `UPSERT` is used here, under either DMBS."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e65109a",
   "metadata": {},
   "source": [
    "### Idempotency"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27f3f777",
   "metadata": {},
   "source": [
    "From [Wikipedia](https://en.wikipedia.org/wiki/Idempotence).\n",
    "\n",
    "> Idempotence is the property of certain operations in mathematics and computer science whereby they can be applied multiple times without changing the result beyond the initial application."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "909ee238",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "Our `UPSERT` command can be executed multiple times.  Subsequent executions do not further change the state.  We can thus call the command we wrote **idempotent**.\n",
    "\n",
    "A plain `INSERT` would not be idempotent… unless its subsequent executions were to fail with error due to key conflict."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "/gnu/store/q35bxk1i5blx0rcgxphhrq9xqmgha9bz-python-3.11.11/bin/python3",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}