{
  "nbformat": 4,
  "nbformat_minor": 5,
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Python triggers\n",
        "\n",
        "Calculate and validate stored totals with Python hooks, compare normal writes with direct SQL, and undo changes in the same in-memory database.\n",
        "\n",
        "[Watch lesson 10](https://asqueel.org/learn/10-python-triggers/)\n",
        "\n",
        "**Run the cells from top to bottom in a fresh Python kernel.** Each notebook includes its own starting state. SQLite runs in memory; restarting the kernel discards the database. To repeat the exercise, restart the kernel and run all cells. Colab execution requires a Google account.\n",
        "\n",
        "\u00a9 2026 Softwell S.r.l. \u00b7 Apache-2.0"
      ],
      "id": "l10-c0-8a1599141ed9"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Before the examples: `_ = ...` is optional\n",
        "\n",
        "It assigns a return value we do not need, preventing its automatic display in the Python console or notebook. The call still runs normally. This is a Python convention, not an Asqueel requirement. When you need the result, use a meaningful variable name."
      ],
      "id": "l10-c1-5261ce0eb0bf"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Prepare the notebook\n",
        "\n",
        "The video starts in a terminal. Here, install the same official package in the notebook kernel. No virtual environment or separate Python console is needed in Colab."
      ],
      "id": "l10-c2-a53efa980848"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%pip install -q \"asqueel[migration]==0.6.0\""
      ],
      "id": "l10-c3-5283bc5543e7",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%config InteractiveShell.ast_node_interactivity = \"all\""
      ],
      "id": "l10-c4-7a5eb4f63b61",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Recreate the starting point\n",
        "\n",
        "The previous lessons are rebuilt below. The four fictional CSV files are included in this notebook and written into a new temporary folder. No downloads or existing database are needed."
      ],
      "id": "l10-c5-26bce07fb451"
    },
    {
      "cell_type": "code",
      "metadata": {
        "jupyter": {
          "source_hidden": true
        },
        "cellView": "form"
      },
      "source": [
        "#@title Prepare the sample CSV files\n",
        "from pathlib import Path\n",
        "import os\n",
        "import tempfile\n",
        "_lesson_folder = tempfile.TemporaryDirectory(prefix=\"asqueel-notebook-\")\n",
        "os.chdir(_lesson_folder.name)\n",
        "_csv_files = {'customers.csv': 'id,name,city\\n1,Ada,Florence\\n2,Grace,London\\n3,Linus,Helsinki\\n4,Guido,Amsterdam\\n5,Elena,Florence\\n6,Alan,Manchester\\n7,Margaret,New York\\n8,Barbara,Boston\\n', 'invoice_rows.csv': 'id,invoice_id,product_code,quantity,unit_price,total\\n1,101,TEA,2,3.50,7.00\\n2,101,COF,1,6.90,6.90\\n3,101,MUG,4,5.25,21.00\\n4,102,BTEA,2,3.20,6.40\\n5,102,HTEA,3,4.10,12.30\\n6,102,HON,4,7.50,30.00\\n7,103,SUG,3,2.30,6.90\\n8,103,CHO,4,3.90,15.60\\n9,103,BIS,1,2.80,2.80\\n10,104,JAM,4,4.60,18.40\\n11,104,OAT,1,2.40,2.40\\n12,104,JUI,2,2.70,5.40\\n13,105,WAT,1,0.90,0.90\\n14,105,LEM,2,1.80,3.60\\n15,105,KET,3,32.90,98.70\\n16,106,POT,2,18.50,37.00\\n17,106,PLT,3,6.40,19.20\\n18,106,BWL,4,7.80,31.20\\n19,107,SPO,3,9.50,28.50\\n20,107,TRY,4,14.90,59.60\\n21,107,JAR,1,8.20,8.20\\n22,108,NAP,4,12.00,48.00\\n23,108,TWL,1,5.60,5.60\\n24,108,FLT,2,2.10,4.20\\n25,109,TIN,1,6.70,6.70\\n26,109,MAT,2,11.40,22.80\\n27,109,CAF,3,29.90,89.70\\n28,110,BOT,2,13.50,27.00\\n29,110,STR,3,4.80,14.40\\n30,110,SCL,4,24.90,99.60\\n31,111,TEA,3,3.50,10.50\\n32,111,COF,4,6.90,27.60\\n33,111,MUG,1,5.25,5.25\\n34,112,BTEA,4,3.20,12.80\\n35,112,HTEA,1,4.10,4.10\\n36,112,HON,2,7.50,15.00\\n', 'invoices.csv': 'id,number,customer_id,invoice_date\\n101,INV-001,1,2026-09-28\\n102,INV-002,2,2026-09-29\\n103,INV-003,3,2026-09-30\\n104,INV-004,4,2026-10-01\\n105,INV-005,5,2026-10-02\\n106,INV-006,6,2026-10-03\\n107,INV-007,7,2026-10-04\\n108,INV-008,8,2026-10-05\\n109,INV-009,1,2026-10-06\\n110,INV-010,2,2026-10-07\\n111,INV-011,5,2026-10-08\\n112,INV-012,3,2026-10-09\\n', 'products.csv': 'code,name,category,stock\\nBTEA,Black tea,Drinks,30\\nHTEA,Herbal tea,Drinks,18\\nHON,Honey,Food,22\\nSUG,Cane sugar,Food,40\\nCHO,Dark chocolate,Food,35\\nBIS,Biscuits,Food,28\\nJAM,Strawberry jam,Food,16\\nOAT,Oat drink,Drinks,32\\nJUI,Apple juice,Drinks,26\\nWAT,Mineral water,Drinks,60\\nLEM,Lemonade,Drinks,30\\nKET,Kettle,Kitchen,9\\nPOT,Teapot,Kitchen,14\\nPLT,Plate,Kitchen,36\\nBWL,Bowl,Kitchen,24\\nSPO,Spoon set,Kitchen,20\\nTRY,Serving tray,Kitchen,11\\nJAR,Storage jar,Kitchen,25\\nNAP,Cotton napkins,Kitchen,18\\nTWL,Tea towel,Kitchen,30\\nFLT,Coffee filters,Kitchen,45\\nTIN,Tea tin,Kitchen,15\\nMAT,Table mats,Kitchen,16\\nCAF,Coffee maker,Kitchen,7\\nBOT,Water bottle,Kitchen,21\\nSTR,Tea strainer,Kitchen,23\\nSCL,Kitchen scale,Kitchen,8\\n'}\n",
        "for _name, _text in _csv_files.items():\n",
        "    _ = Path(_name).write_text(_text, encoding=\"utf-8\")\n"
      ],
      "id": "l10-c6-89efb228754e",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "jupyter": {
          "source_hidden": true
        },
        "cellView": "form"
      },
      "source": [
        "#@title Recreate the previous lessons\n",
        "\n",
        "######## Import our two tools ########\n",
        "\n",
        "from asqueel import AsqueelDb, SqlDatabaseConfig\n",
        "\n",
        "\n",
        "######## Choose SQLite in memory ########\n",
        "\n",
        "model = SqlDatabaseConfig()\n",
        "root = model.source.db(implementation=\"sqlite\")\n",
        "_ = root.connection(name=\":memory:\", implementation=\"sqlite\")\n",
        "\n",
        "\n",
        "######## Describe a product table ########\n",
        "\n",
        "shop = root.packages().package(\"shop\", sqlschema=\"shop\")\n",
        "product = shop.table(\"product\", pkey=\"code\")\n",
        "_ = product.column(\"code\", size=\":10\")\n",
        "_ = product.column(\"name\", size=\":60\")\n",
        "\n",
        "\n",
        "######## Create that table ########\n",
        "\n",
        "db = AsqueelDb(model)\n",
        "print(db.migrate().commands)\n",
        "\n",
        "\n",
        "######## Add our first product ########\n",
        "\n",
        "products = db.table(\"shop.product\")\n",
        "products.insert({\"code\": \"TEA\", \"name\": \"Tea\"})\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Read our product ########\n",
        "\n",
        "products.query(columns=\"$code, $name\").fetch()\n",
        "\n",
        "\n",
        "######## Change its name ########\n",
        "\n",
        "products.update({\"name\": \"Green tea\"}, pkey=\"TEA\")\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Check the update ########\n",
        "\n",
        "products.query(columns=\"$code, $name\").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Track category and stock ########\n",
        "\n",
        "_ = product.column(\"category\", size=\":30\")\n",
        "_ = product.column(\"stock\", dtype=\"I\")\n",
        "\n",
        "\n",
        "######## Load the updated model ########\n",
        "\n",
        "db.reloadModel(model)\n",
        "\n",
        "\n",
        "######## Add those database columns ########\n",
        "\n",
        "print(db.migrate().commands)\n",
        "\n",
        "\n",
        "######## Check our original row ########\n",
        "\n",
        "products = db.table(\"shop.product\")\n",
        "products.query(columns=\"$name, $stock\").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Give tea its stock ########\n",
        "\n",
        "products.update({\"category\": \"Drinks\", \"stock\": 8}, pkey=\"TEA\")\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Read the new values ########\n",
        "\n",
        "products.query(columns=\"$name, $category, $stock\").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Add products from CSV ########\n",
        "\n",
        "from io import StringIO\n",
        "data = StringIO(\n",
        "    \"code,name,category,stock\\n\"\n",
        "    \"COF,Coffee,Drinks,24\\n\"\n",
        "    \"MUG,Mug,Kitchen,12\\n\"\n",
        ")\n",
        "\n",
        "\n",
        "######## Import those rows ########\n",
        "\n",
        "products.sql_insert(data, format=\"csv\")\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## List just the drinks ########\n",
        "\n",
        "products.query(\n",
        "    columns=\"$name, $stock\",\n",
        "    where=\"$category = :category\", category=\"Drinks\",\n",
        "    order_by=\"$stock DESC\",\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "# Continue with lesson two.\n",
        "\n",
        "######## Check our starting point ########\n",
        "\n",
        "products.query(columns=\"$code, $name\").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Describe our customers ########\n",
        "\n",
        "customer = shop.table(\"customer\", pkey=\"id\")\n",
        "_ = customer.column(\"id\", dtype=\"I\")\n",
        "_ = customer.column(\"name\", size=\":60\")\n",
        "_ = customer.column(\"city\", size=\":60\")\n",
        "\n",
        "\n",
        "######## Create the customer table ########\n",
        "\n",
        "db.reloadModel(model)\n",
        "print(db.migrate().commands)\n",
        "\n",
        "\n",
        "######## Describe a dated invoice ########\n",
        "\n",
        "invoice = shop.table(\"invoice\", pkey=\"id\")\n",
        "_ = invoice.column(\"id\", dtype=\"I\")\n",
        "_ = invoice.column(\"number\", size=\":20\")\n",
        "_ = invoice.column(\"invoice_date\", dtype=\"D\")\n",
        "\n",
        "\n",
        "######## Connect invoices to customers ########\n",
        "\n",
        "customer_id = invoice.column(\"customer_id\", dtype=\"I\")\n",
        "_ = customer_id.relation(\n",
        "    \"shop.customer.id\", foreign_key=True, relation_name=\"invoices\",\n",
        ")\n",
        "\n",
        "\n",
        "######## Describe an invoice row ########\n",
        "\n",
        "invoice_row = shop.table(\"invoice_row\", pkey=\"id\")\n",
        "_ = invoice_row.column(\"id\", dtype=\"I\")\n",
        "_ = invoice_row.column(\"quantity\", dtype=\"I\")\n",
        "_ = invoice_row.column(\"unit_price\", dtype=\"N\", size=\"12,2\")\n",
        "_ = invoice_row.column(\"total\", dtype=\"N\", size=\"12,2\")\n",
        "\n",
        "\n",
        "######## Connect the row at both ends ########\n",
        "\n",
        "invoice_id = invoice_row.column(\"invoice_id\", dtype=\"I\")\n",
        "_ = invoice_id.relation(\n",
        "    \"shop.invoice.id\", foreign_key=True, relation_name=\"rows\",\n",
        ")\n",
        "product_code = invoice_row.column(\"product_code\", size=\":10\")\n",
        "_ = product_code.relation(\n",
        "    \"shop.product.code\", foreign_key=True, relation_name=\"rows\",\n",
        ")\n",
        "\n",
        "\n",
        "######## Apply the new model ########\n",
        "\n",
        "db.reloadModel(model)\n",
        "print(db.migrate().commands)\n",
        "\n",
        "\n",
        "######## Get fresh table handles ########\n",
        "\n",
        "products = db.table(\"shop.product\")\n",
        "customers = db.table(\"shop.customer\")\n",
        "invoices = db.table(\"shop.invoice\")\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "\n",
        "\n",
        "######## Inspect the row file ########\n",
        "\n",
        "from pathlib import Path\n",
        "print(\"\\n\".join(Path(\"invoice_rows.csv\").read_text().splitlines()[:4]))\n",
        "\n",
        "\n",
        "######## Expand the product catalogue ########\n",
        "\n",
        "with open(\"products.csv\", newline=\"\", encoding=\"utf-8\") as source:\n",
        "    products.sql_insert(source)\n",
        "\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Count the catalogue ########\n",
        "\n",
        "products.query().count()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Import customers and their cities ########\n",
        "\n",
        "with open(\"customers.csv\", newline=\"\", encoding=\"utf-8\") as source:\n",
        "    customers.sql_insert(source)\n",
        "\n",
        "db.commit()\n",
        "customers.query(columns=\"$name, $city\", limit=3).fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Import the invoices ########\n",
        "\n",
        "with open(\"invoices.csv\", newline=\"\", encoding=\"utf-8\") as source:\n",
        "    invoices.sql_insert(source)\n",
        "\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Import the invoice rows ########\n",
        "\n",
        "with open(\"invoice_rows.csv\", newline=\"\", encoding=\"utf-8\") as source:\n",
        "    rows.sql_insert(source)\n",
        "\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Check how much we imported ########\n",
        "\n",
        "invoices.query().count()\n",
        "rows.query().count()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Read invoices with their customers ########\n",
        "\n",
        "invoices.query(\n",
        "    columns=\"$number, $invoice_date, \"\n",
        "            \"@customer_id.name, @customer_id.city\",\n",
        "    limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Inspect the first invoice ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"@product_code.name, \"\n",
        "            \"$quantity, $unit_price, $total\",\n",
        "    where=\"$invoice_id = :invoice\", invoice=101,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Read across both connections ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"@invoice_id.number, \"\n",
        "            \"@invoice_id.@customer_id.city, \"\n",
        "            \"@product_code.name, $total\",\n",
        "    limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "# Continue with lesson three.\n",
        "\n",
        "######## Find invoices for Florence ########\n",
        "\n",
        "invoices.query(\n",
        "    columns=\"$number\",\n",
        "    where=\"@customer_id.city = :city\", city=\"Florence\",\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Sort invoices by city ########\n",
        "\n",
        "invoices.query(\n",
        "    columns=\"$number, @customer_id.city\",\n",
        "    order_by=\"@customer_id.city\", limit=4,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Show the newest rows first ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"@invoice_id.invoice_date, $total\",\n",
        "    order_by=\"@invoice_id.invoice_date DESC\", limit=3,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Find rows for Florence ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"@product_code.name, $quantity\",\n",
        "    where=\"@invoice_id.@customer_id.city = :city\",\n",
        "    city=\"Florence\", limit=3,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Combine the conditions ########\n",
        "\n",
        "from datetime import date\n",
        "rows.query(\n",
        "    columns=\"@product_code.name, $total\",\n",
        "    where=\"@invoice_id.@customer_id.city = :city \"\n",
        "          \"AND @invoice_id.invoice_date >= :since\",\n",
        "    city=\"Florence\", since=date(2026, 10, 1),\n",
        "    order_by=\"@invoice_id.invoice_date DESC\", limit=4,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Close this read transaction ########\n",
        "\n",
        "db.commit()\n",
        "\n",
        "# Continue with lesson 4.\n",
        "\n",
        "######## Call this path city ########\n",
        "\n",
        "_ = invoice_row.aliasColumn(\n",
        "    \"city\", relation_path=\"@invoice_id.@customer_id.city\",\n",
        ")\n",
        "db.reloadModel(model)\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "\n",
        "\n",
        "######## Read and filter with that name ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"$city, @product_code.name\",\n",
        "    where=\"$city = :city\", city=\"Florence\", limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Calculate quantity times price ########\n",
        "\n",
        "_ = invoice_row.formulaColumn(\n",
        "    \"calculated_total\",\n",
        "    sql_formula=\"ROUND($quantity * $unit_price, 2)\",\n",
        "    dtype=\"N\",\n",
        ")\n",
        "db.reloadModel(model)\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "\n",
        "\n",
        "######## Read the calculated amounts ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"$quantity, $unit_price, $calculated_total\",\n",
        "    where=\"$invoice_id = :invoice\", invoice=101,\n",
        "    order_by=\"$id\",\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Check whether the database needs changing ########\n",
        "\n",
        "db.migration_plan().empty\n",
        "\n",
        "\n",
        "######## Temporarily change the quantity to three ########\n",
        "\n",
        "rows.update({\"quantity\": 3}, pkey=1)\n",
        "rows.query(\n",
        "    columns=\"$quantity, $total, $calculated_total\",\n",
        "    where=\"$id = :id\", id=1,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Undo that change ########\n",
        "\n",
        "db.rollback()\n",
        "\n",
        "# Continue with lesson 5.\n",
        "\n",
        "######## Give each invoice a calculated total ########\n",
        "\n",
        "_ = invoice.formulaColumn(\n",
        "    \"total\", dtype=\"N\",\n",
        "    select=dict(\n",
        "        table=\"shop.invoice_row\",\n",
        "        columns=\"ROUND(SUM($calculated_total), 2)\",\n",
        "        where=\"$invoice_id = #THIS.id\",\n",
        "    ),\n",
        ")\n",
        "db.reloadModel(model)\n",
        "invoices = db.table(\"shop.invoice\")\n",
        "\n",
        "\n",
        "######## Read those totals as ordinary fields ########\n",
        "\n",
        "invoices.query(\n",
        "    columns=\"$number, $total\", order_by=\"$id\", limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Total the invoices of each customer ########\n",
        "\n",
        "_ = customer.formulaColumn(\n",
        "    \"total\", dtype=\"N\",\n",
        "    select=dict(\n",
        "        table=\"shop.invoice\",\n",
        "        columns=\"ROUND(SUM($total), 2)\",\n",
        "        where=\"$customer_id = #THIS.id\",\n",
        "    ),\n",
        ")\n",
        "db.reloadModel(model)\n",
        "customers = db.table(\"shop.customer\")\n",
        "\n",
        "\n",
        "######## Read each customer's sales total ########\n",
        "\n",
        "customers.query(\n",
        "    columns=\"$name, $total\", order_by=\"$id\", limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Total the sales of each product ########\n",
        "\n",
        "_ = product.formulaColumn(\n",
        "    \"total\", dtype=\"N\",\n",
        "    select=dict(\n",
        "        table=\"shop.invoice_row\",\n",
        "        columns=\"ROUND(SUM($calculated_total), 2)\",\n",
        "        where=\"$product_code = #THIS.code\",\n",
        "    ),\n",
        ")\n",
        "db.reloadModel(model)\n",
        "products = db.table(\"shop.product\")\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "\n",
        "\n",
        "######## Read the product totals ########\n",
        "\n",
        "products.query(\n",
        "    columns=\"$code, $name, $total\",\n",
        "    where=\"$code IN :codes\", codes=[\"TEA\", \"COF\", \"MUG\"],\n",
        "    order_by=\"$code\",\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Summarize each customer's invoices ########\n",
        "\n",
        "customers = db.table(\"shop.customer\")\n",
        "customers.query(\n",
        "    columns=\"$name, @invoices.aggr(\"\n",
        "            \"sum($total), count(), avg($total))\",\n",
        "    order_by=\"$id\", limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Count the rows of each invoice too ########\n",
        "\n",
        "invoices = db.table(\"shop.invoice\")\n",
        "invoices.query(\n",
        "    columns=\"$number, $total, @rows.aggr(count())\",\n",
        "    order_by=\"$id\", limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Average the recorded selling prices ########\n",
        "\n",
        "products.query(\n",
        "    columns=\"$code, @rows.aggr(count(), avg($unit_price))\",\n",
        "    where=\"$code IN :codes\", codes=[\"TEA\", \"COF\", \"MUG\"],\n",
        "    order_by=\"$code\",\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Name the average price so we can filter it ########\n",
        "\n",
        "_ = product.formulaColumn(\n",
        "    \"average_price\", dtype=\"N\",\n",
        "    select=dict(\n",
        "        table=\"shop.invoice_row\", columns=\"AVG($unit_price)\",\n",
        "        where=\"$product_code = #THIS.code\",\n",
        "    ),\n",
        ")\n",
        "db.reloadModel(model)\n",
        "products = db.table(\"shop.product\")\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "\n",
        "\n",
        "######## Filter by average price ########\n",
        "\n",
        "products.query(\n",
        "    columns=\"$code, $name, $average_price\",\n",
        "    where=\"$average_price BETWEEN :min_price AND :max_price\",\n",
        "    min_price=3.2, max_price=4.8,\n",
        "    order_by=\"$average_price, $code\",\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "# Continue with lesson 6.\n",
        "\n",
        "######## Total sales by city ########\n",
        "\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "products = db.table(\"shop.product\")\n",
        "rows.query(\n",
        "    columns=\"$city, SUM($calculated_total)\",\n",
        "    group_by=\"$city\", order_by=\"$city\", limit=3,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Name the report columns ########\n",
        "\n",
        "report = rows.query(\n",
        "    columns=\"$city, ROUND(SUM($calculated_total), 2) AS sales, \"\n",
        "            \"COUNT(*) AS row_count\",\n",
        "    group_by=\"$city\", order_by=\"$city\",\n",
        ")\n",
        "report.fetch()\n",
        "\n",
        "\n",
        "######## Filter rows and groups ########\n",
        "\n",
        "selected = rows.query(\n",
        "    columns=\"$city, ROUND(SUM($calculated_total), 2) AS sales\",\n",
        "    where=\"$unit_price >= :min_price\", min_price=5,\n",
        "    group_by=\"$city\",\n",
        "    having=\"SUM($calculated_total) >= :min_sales\", min_sales=100,\n",
        "    order_by=\"$city\",\n",
        ")\n",
        "selected.fetch()\n",
        "db.commit()\n",
        "\n",
        "# Continue with lesson 9.\n",
        "\n",
        "######## Add two teas and commit the row ########\n",
        "\n",
        "from decimal import Decimal\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "_ = rows.insert({\n",
        "    \"id\": 901, \"invoice_id\": 101, \"product_code\": \"TEA\",\n",
        "    \"quantity\": 2, \"unit_price\": Decimal(\"3.50\"),\n",
        "    \"total\": Decimal(\"7.00\"),\n",
        "})\n",
        "rows.query(\n",
        "    columns=\"$id, $quantity, $unit_price, $total\",\n",
        "    where=\"$id = :id\", id=901,\n",
        ").fetchRecord()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Change the quantity to three ########\n",
        "\n",
        "old = dict(rows.query(where=\"$id = :id\", id=901).fetchRecord())\n",
        "changed = dict(old, quantity=3, total=Decimal(\"10.50\"))\n",
        "_ = rows.update(changed, old_record=old)\n",
        "rows.query(\n",
        "    columns=\"$quantity, $unit_price, $total\",\n",
        "    where=\"$id = :id\", id=901,\n",
        ").fetchRecord()\n",
        "\n",
        "\n",
        "######## Update the selection to four teas ########\n",
        "\n",
        "rows.query(where=\"$id = :id\", id=901).update({\n",
        "    \"quantity\": 4, \"total\": Decimal(\"14.00\"),\n",
        "})\n",
        "rows.query(\n",
        "    columns=\"$quantity, $total\", where=\"$id = :id\", id=901,\n",
        ").fetchRecord()\n",
        "\n",
        "\n",
        "######## Delete the row, then undo our pending work ########\n",
        "\n",
        "deleted = rows.query(where=\"$id = :id\", id=901).delete()\n",
        "len(deleted)\n",
        "rows.query(where=\"$id = :id\", id=901).fetch()\n",
        "db.rollback()\n",
        "rows.query(\n",
        "    columns=\"$quantity, $total\", where=\"$id = :id\", id=901,\n",
        ").fetchRecord()\n",
        "\n",
        "\n",
        "######## Clean up and commit the deletion ########\n",
        "\n",
        "_ = rows.query(where=\"$id = :id\", id=901).delete()\n",
        "db.commit()\n",
        "rows.query().count()\n",
        "db.commit()\n"
      ],
      "id": "l10-c7-1474b095509d",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 1 \u2014 What we'll learn in this lesson"
      ],
      "id": "l10-c8-0faae73bfca2"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Welcome back! Here's what we'll learn. In lesson nine we supplied totals ourselves. Now Python hooks will calculate and validate them when we write. We will also see which direct SQL operations bypass this behavior. These are Python hooks in Asqueel, not SQL triggers installed in SQLite. We keep our database in memory."
      ],
      "id": "l10-c9-1e5060d7d869"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "```text\n",
        "What we'll learn in this lesson\n",
        "Python triggers \u2192 calculate and validate writes\n",
        "Reuse insert and update from lesson 9\n",
        "sql_update \u2192 compare the direct SQL path\n",
        "rollback \u2192 undo the demonstration\n",
        "\n",
        "Same SQLite database in memory\n",
        "```"
      ],
      "id": "l10-c10-5e48746af417"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 2 \u2014 Give invoice rows their Python behavior"
      ],
      "id": "l10-c11-e6570ed21a96"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's define the write behavior. Our shared calculation checks quantity and price, then stores their product rounded to cents. The inserting and updating hooks run before the SQL write. The updated hook prints the old and new quantities afterwards. Afterwards means after the statement, not after commit."
      ],
      "id": "l10-c12-b94777080516"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Define the write behavior ########\n",
        "\n",
        "from asqueel import SqlTable\n",
        "from decimal import Decimal\n",
        "\n",
        "class InvoiceRowTable(SqlTable):\n",
        "    def calculate_total(self, record):\n",
        "        if record[\"quantity\"] <= 0:\n",
        "            raise ValueError(\"Quantity must be positive\")\n",
        "        price = Decimal(str(record[\"unit_price\"]))\n",
        "        if price < 0:\n",
        "            raise ValueError(\"Price must not be negative\")\n",
        "        record[\"total\"] = (\n",
        "            record[\"quantity\"] * price\n",
        "        ).quantize(Decimal(\"0.01\"))\n",
        "    def trigger_onInserting(self, record):\n",
        "        self.calculate_total(record)\n",
        "    def trigger_onUpdating(self, record, old_record=None):\n",
        "        self.calculate_total(record)\n",
        "    def trigger_onUpdated(self, record, old_record=None):\n",
        "        if old_record is not None:\n",
        "            print(\"Quantity:\", old_record[\"quantity\"],\n",
        "                  \"->\", record[\"quantity\"])"
      ],
      "id": "l10-c13-cd56a5a49f3a",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's attach it to invoice rows. X table class gives this existing table its Python behavior. Reload the model and get a fresh table handle. True means no migration is needed: we added Python hooks, not columns. Existing rows are not recalculated."
      ],
      "id": "l10-c14-05ee71271b31"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Attach it to invoice rows ########\n",
        "\n",
        "_ = shop.table(\"invoice_row\", x_table_class=InvoiceRowTable)\n",
        "db.reloadModel(model)\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "db.migration_plan().empty"
      ],
      "id": "l10-c15-599f3c4d5f5b",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 3 \u2014 Calculate totals on insert and update"
      ],
      "id": "l10-c16-11ef9a5c3c2c"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's insert two teas. We supply quantity and price, but no total. The inserting hook stores seven. The formula column from lesson four also reads seven. The stored total is written by our hook; the formula is calculated when we query. This change is still uncommitted."
      ],
      "id": "l10-c17-42c98f1d5da3"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Insert two teas ########\n",
        "\n",
        "_ = rows.insert({\n",
        "    \"id\": 901, \"invoice_id\": 101, \"product_code\": \"TEA\",\n",
        "    \"quantity\": 2, \"unit_price\": Decimal(\"3.50\"),\n",
        "})\n",
        "rows.query(\n",
        "    columns=\"$id, $quantity, $unit_price, $total, $calculated_total\",\n",
        "    where=\"$id = :id\", id=901,\n",
        ").fetchRecord()"
      ],
      "id": "l10-c18-f399e94514ea",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's change only the quantity. Query update supplies the complete record and its previous values, as in lesson nine. This time our updating hook recalculates ten fifty automatically. The updated hook prints two to three before commit."
      ],
      "id": "l10-c19-91b2e3d4b34b"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Change only the quantity ########\n",
        "\n",
        "rows.query(where=\"$id = :id\", id=901).update({\"quantity\": 3})\n",
        "rows.query(\n",
        "    columns=\"$quantity, $total, $calculated_total\",\n",
        "    where=\"$id = :id\", id=901,\n",
        ").fetchRecord()"
      ],
      "id": "l10-c20-2b14c5a871da",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 4 \u2014 Direct SQL skips the Python hooks"
      ],
      "id": "l10-c21-97ac2ca5885c"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's compare a direct SQL update. SQL update changes one selected row and returns one. It skips our Python hooks: no message appears, and the stored total stays ten fifty. The formula now reads fourteen. Database constraints still apply. The direct SQL insert used for CSV imports also skips these Python hooks."
      ],
      "id": "l10-c22-72c3d565f1df"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Compare a direct SQL update ########\n",
        "\n",
        "rows.query(where=\"$id = :id\", id=901).sql_update({\"quantity\": 4})\n",
        "rows.query(\n",
        "    columns=\"$quantity, $total, $calculated_total\",\n",
        "    where=\"$id = :id\", id=901,\n",
        ").fetchRecord()"
      ],
      "id": "l10-c23-d211b248f757",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's run the normal write cycle again. Normal query update runs our hooks again. Both totals become seventeen fifty, and the result contains the updated primary key."
      ],
      "id": "l10-c24-9d76fac6e2a3"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Run the normal write cycle again ########\n",
        "\n",
        "rows.query(where=\"$id = :id\", id=901).update({\"quantity\": 5})\n",
        "rows.query(\n",
        "    columns=\"$quantity, $total, $calculated_total\",\n",
        "    where=\"$id = :id\", id=901,\n",
        ").fetchRecord()"
      ],
      "id": "l10-c25-0d906beb71b4",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 5 \u2014 Undo the uncommitted examples"
      ],
      "id": "l10-c26-75737988fec7"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's undo our demonstration writes. Rollback removes our new row and its updates together, returning us to thirty-six original rows. It does not erase messages already printed by a Python hook. Commit here closes the verification read; it does not save the rolled-back examples."
      ],
      "id": "l10-c27-12180896594d"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Undo our demonstration writes ########\n",
        "\n",
        "db.rollback()\n",
        "rows.query(where=\"$id >= :first_demo\", first_demo=901).fetch()\n",
        "rows.query().count()\n",
        "db.commit()"
      ],
      "id": "l10-c28-aeaf89ee2087",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 6 \u2014 Reject an invalid write and recover"
      ],
      "id": "l10-c29-9247ea6c76a8"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's reject a zero quantity. Our inserting hook raises a value error before SQL insertion. After a failed write cycle, roll back before continuing. That rollback affects all pending work in the transaction, not just this row. We began from a clean boundary, so no earlier work is lost. The empty result confirms that row nine hundred and three was never stored."
      ],
      "id": "l10-c30-15a3c087394e"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Reject a zero quantity ########\n",
        "\n",
        "try:\n",
        "    rows.insert({\n",
        "        \"id\": 903, \"invoice_id\": 101, \"product_code\": \"TEA\",\n",
        "        \"quantity\": 0, \"unit_price\": Decimal(\"3.50\"),\n",
        "    })\n",
        "except ValueError as error:\n",
        "    print(str(error))\n",
        "    db.rollback()\n",
        "\n",
        "rows.query(where=\"$id = :id\", id=903).fetch()\n",
        "db.commit()"
      ],
      "id": "l10-c31-7ee43a042e53",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 7 \u2014 What to remember"
      ],
      "id": "l10-c32-20b5f1080e24"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Thanks for following along! Use normal writes for Python validation and calculations. Use direct SQL deliberately, with complete valid data. Keep transaction boundaries explicit. We have added automatic write behavior to the operations from lesson nine."
      ],
      "id": "l10-c33-53b8999555a7"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "```text\n",
        "Thank you!\n",
        "insert / update \u2192 Python hooks + SQL\n",
        "query(...).update(...) \u2192 write cycle for each selected row\n",
        "sql_insert / query(...).sql_update(...) \u2192 direct SQL\n",
        "After a statement \u2260 after commit\n",
        "rollback \u2192 all pending database changes\n",
        "\n",
        "Continue with the course reading lessons\n",
        "```"
      ],
      "id": "l10-c34-96978fc01aa7"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Check your result\n",
        "\n",
        "Run this final cell to check the exercise."
      ],
      "id": "l10-c35-062cfb81c9cc"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "from math import isclose\n",
        "original_rows = [{'id': '1', 'invoice_id': '101', 'product_code': 'TEA', 'quantity': '2', 'unit_price': '3.50', 'total': '7.00'}, {'id': '2', 'invoice_id': '101', 'product_code': 'COF', 'quantity': '1', 'unit_price': '6.90', 'total': '6.90'}, {'id': '3', 'invoice_id': '101', 'product_code': 'MUG', 'quantity': '4', 'unit_price': '5.25', 'total': '21.00'}, {'id': '4', 'invoice_id': '102', 'product_code': 'BTEA', 'quantity': '2', 'unit_price': '3.20', 'total': '6.40'}, {'id': '5', 'invoice_id': '102', 'product_code': 'HTEA', 'quantity': '3', 'unit_price': '4.10', 'total': '12.30'}, {'id': '6', 'invoice_id': '102', 'product_code': 'HON', 'quantity': '4', 'unit_price': '7.50', 'total': '30.00'}, {'id': '7', 'invoice_id': '103', 'product_code': 'SUG', 'quantity': '3', 'unit_price': '2.30', 'total': '6.90'}, {'id': '8', 'invoice_id': '103', 'product_code': 'CHO', 'quantity': '4', 'unit_price': '3.90', 'total': '15.60'}, {'id': '9', 'invoice_id': '103', 'product_code': 'BIS', 'quantity': '1', 'unit_price': '2.80', 'total': '2.80'}, {'id': '10', 'invoice_id': '104', 'product_code': 'JAM', 'quantity': '4', 'unit_price': '4.60', 'total': '18.40'}, {'id': '11', 'invoice_id': '104', 'product_code': 'OAT', 'quantity': '1', 'unit_price': '2.40', 'total': '2.40'}, {'id': '12', 'invoice_id': '104', 'product_code': 'JUI', 'quantity': '2', 'unit_price': '2.70', 'total': '5.40'}, {'id': '13', 'invoice_id': '105', 'product_code': 'WAT', 'quantity': '1', 'unit_price': '0.90', 'total': '0.90'}, {'id': '14', 'invoice_id': '105', 'product_code': 'LEM', 'quantity': '2', 'unit_price': '1.80', 'total': '3.60'}, {'id': '15', 'invoice_id': '105', 'product_code': 'KET', 'quantity': '3', 'unit_price': '32.90', 'total': '98.70'}, {'id': '16', 'invoice_id': '106', 'product_code': 'POT', 'quantity': '2', 'unit_price': '18.50', 'total': '37.00'}, {'id': '17', 'invoice_id': '106', 'product_code': 'PLT', 'quantity': '3', 'unit_price': '6.40', 'total': '19.20'}, {'id': '18', 'invoice_id': '106', 'product_code': 'BWL', 'quantity': '4', 'unit_price': '7.80', 'total': '31.20'}, {'id': '19', 'invoice_id': '107', 'product_code': 'SPO', 'quantity': '3', 'unit_price': '9.50', 'total': '28.50'}, {'id': '20', 'invoice_id': '107', 'product_code': 'TRY', 'quantity': '4', 'unit_price': '14.90', 'total': '59.60'}, {'id': '21', 'invoice_id': '107', 'product_code': 'JAR', 'quantity': '1', 'unit_price': '8.20', 'total': '8.20'}, {'id': '22', 'invoice_id': '108', 'product_code': 'NAP', 'quantity': '4', 'unit_price': '12.00', 'total': '48.00'}, {'id': '23', 'invoice_id': '108', 'product_code': 'TWL', 'quantity': '1', 'unit_price': '5.60', 'total': '5.60'}, {'id': '24', 'invoice_id': '108', 'product_code': 'FLT', 'quantity': '2', 'unit_price': '2.10', 'total': '4.20'}, {'id': '25', 'invoice_id': '109', 'product_code': 'TIN', 'quantity': '1', 'unit_price': '6.70', 'total': '6.70'}, {'id': '26', 'invoice_id': '109', 'product_code': 'MAT', 'quantity': '2', 'unit_price': '11.40', 'total': '22.80'}, {'id': '27', 'invoice_id': '109', 'product_code': 'CAF', 'quantity': '3', 'unit_price': '29.90', 'total': '89.70'}, {'id': '28', 'invoice_id': '110', 'product_code': 'BOT', 'quantity': '2', 'unit_price': '13.50', 'total': '27.00'}, {'id': '29', 'invoice_id': '110', 'product_code': 'STR', 'quantity': '3', 'unit_price': '4.80', 'total': '14.40'}, {'id': '30', 'invoice_id': '110', 'product_code': 'SCL', 'quantity': '4', 'unit_price': '24.90', 'total': '99.60'}, {'id': '31', 'invoice_id': '111', 'product_code': 'TEA', 'quantity': '3', 'unit_price': '3.50', 'total': '10.50'}, {'id': '32', 'invoice_id': '111', 'product_code': 'COF', 'quantity': '4', 'unit_price': '6.90', 'total': '27.60'}, {'id': '33', 'invoice_id': '111', 'product_code': 'MUG', 'quantity': '1', 'unit_price': '5.25', 'total': '5.25'}, {'id': '34', 'invoice_id': '112', 'product_code': 'BTEA', 'quantity': '4', 'unit_price': '3.20', 'total': '12.80'}, {'id': '35', 'invoice_id': '112', 'product_code': 'HTEA', 'quantity': '1', 'unit_price': '4.10', 'total': '4.10'}, {'id': '36', 'invoice_id': '112', 'product_code': 'HON', 'quantity': '2', 'unit_price': '7.50', 'total': '15.00'}]\n",
        "actual = rows.query(columns=\"$id,$quantity,$unit_price,$total\", order_by=\"$id\").fetch()\n",
        "assert len(actual) == len(original_rows) == 36\n",
        "for actual_row, source in zip(actual, sorted(original_rows, key=lambda r: int(r[\"id\"]))):\n",
        "    assert actual_row[\"id\"] == int(source[\"id\"])\n",
        "    assert actual_row[\"quantity\"] == int(source[\"quantity\"])\n",
        "    assert isclose(float(actual_row[\"unit_price\"]), float(source[\"unit_price\"]))\n",
        "    assert isclose(float(actual_row[\"total\"]), float(source[\"total\"]))\n",
        "\n",
        "assert isinstance(rows, InvoiceRowTable)\n",
        "assert db.table(\"shop.product\").query().count() == 30\n",
        "assert db.table(\"shop.customer\").query().count() == 8\n",
        "assert db.table(\"shop.invoice\").query().count() == 12\n",
        "db.commit()\n",
        "rows.insert(dict(id=999, invoice_id=101, product_code=\"TEA\", quantity=2, unit_price=Decimal(\"3.50\")))\n",
        "assert rows.query(where=\"$id=999\").fetchRecord()[\"total\"] == 7\n",
        "assert rows.query(where=\"$id=999\").update({\"quantity\": 3}) == [999]\n",
        "assert rows.query(where=\"$id=999\").fetchRecord()[\"total\"] == Decimal(\"10.50\")\n",
        "assert rows.query(where=\"$id=999\").sql_update({\"quantity\": 4}) == 1\n",
        "assert rows.query(where=\"$id=999\").fetchRecord()[\"total\"] == Decimal(\"10.50\")\n",
        "db.rollback()\n",
        "assert rows.query().count() == 36\n",
        "db.commit()\n",
        "\n",
        "db.commit()\n",
        "db.commit()\n",
        "print(\"Lesson checks passed.\")\n"
      ],
      "id": "l10-c36-ee8f8a97b8e1",
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3",
      "language": "python"
    },
    "language_info": {
      "name": "python"
    },
    "asqueel": {
      "version": "0.6.0",
      "lesson": 10
    }
  }
}