.. include:: ../global.rst

**************************************
Project Manager: Learning Objectives
**************************************

This page checks my **Project Manager** app (a Django board / list / card
task manager, in ``roberts/csci.2026/project_manager/project-manager``)
against two sources:

* the **31 competency topics**, and
* the **46 Learning Objectives/Outcomes** in the syllabus.

The syllabus list includes every competency topic, but it splits some of them
in two (for example *LIKE and GLOB Clauses* is listed as *LIKE Clause* and
*GLOB Clause*). It also adds ten objectives that are not on the competency
list. So the main table below follows the syllabus, and the **Comp. #** column
shows which competency topic each row belongs to ("—" means it is in the
syllabus only).

.. note::

   The project never writes SQL by hand. All SQL comes from the Django ORM
   (models, migrations and querysets). Where a row says an objective is
   reached, the SQL quoted is what Django actually generates. I got it from
   ``python manage.py sqlmigrate boards 0001`` and by printing ``queryset.query``.

Summary
=======

.. list-table::
   :header-rows: 1
   :widths: 30 20 20 20

   * - Source
     - Reached
     - Partly reached
     - Not reached
   * - Syllabus objectives (46)
     - 25
     - 5
     - 16
   * - Competency topics (31)
     - 15
     - 4
     - 12

A competency topic that pairs two items (for example *GROUP BY and HAVING*)
counts as **partly reached** when the project covers only one of them.

Objectives the Project Reaches
==============================

.. list-table::
   :header-rows: 1
   :widths: 18 7 10 65

   * - Objective
     - Comp. #
     - Status
     - Where in the project
   * - Normalization
     - —
     - Reached
     - ``boards/models.py``: the data is split into three tables, ``boards``,
       ``lists`` and ``cards``, so each fact is stored once. Each child row
       points to its parent with a foreign key (``owner_id``, ``board_id``,
       ``list_id``) instead of repeating the parent's data.
   * - API Interface
     - —
     - Reached
     - ``boards/views.py:134`` ``card_move`` is a JSON endpoint
       (``POST cards/<id>/move/``, body ``{"list_id": 3, "position": 1}``)
       called by ``static/js/board.js``. The whole app uses the Django ORM
       as its database API.
   * - Data Type
     - 1
     - Reached
     - ``boards/models.py``: ``CharField`` → ``varchar(100)``/``varchar(200)``,
       ``PositiveIntegerField`` → ``integer unsigned``, ``DateField`` → ``date``,
       ``DateTimeField`` → ``datetime``, ``BigAutoField`` → ``integer`` primary key.
   * - CREATE Database
     - —
     - Reached
     - ``config/settings.py:56`` sets up the SQLite database ``db.sqlite3``.
       ``python manage.py migrate`` (README step 2) creates the file.
   * - CREATE Table
     - —
     - Reached
     - ``boards/migrations/0001_initial.py`` generates
       ``CREATE TABLE "boards"``, ``"lists"`` and ``"cards"``.
       The table names come from ``db_table`` in ``models.py``.
   * - INSERT Query
     - —
     - Reached
     - ``boards/views.py:39`` (new board), ``:40`` ``bulk_create`` (three default
       lists in one INSERT), ``:93`` (new list), ``:108`` (new card).
       ``accounts/views.py:12`` inserts a new user at sign-up.
   * - SELECT Query
     - —
     - Reached
     - Every page reads data, for example ``boards/views.py:47`` (board list),
       ``:62`` (board detail with prefetched lists and cards), ``:18``–``:27``
       (look up one row by primary key).
   * - Operators
     - 4
     - Reached
     - Comparison: ``"boards"."owner_id" = 1`` and ``NOT ("cards"."id" = 2)``
       (``.exclude()`` in ``boards/ordering.py:34``), and ``>=`` in the
       ``CHECK ("position" >= 0)`` constraint.
   * - Expressions
     - 5
     - Reached
     - The CHECK expression ``"position" >= 0`` in the migration, and the
       aggregate expressions ``COUNT(DISTINCT "lists"."id")`` and
       ``MAX("position")`` (``boards/views.py:49``, ``boards/ordering.py:16``).
   * - WHERE Clause
     - 6
     - Reached
     - Every ``.filter()``. For example ``boards/views.py:48`` →
       ``WHERE "boards"."owner_id" = ?``. This is also how each user sees
       only their own boards.
   * - AND & OR Operators
     - 7
     - Partly
     - **AND** is used: ``boards/views.py:52`` →
       ``WHERE ("cards"."due_date" IS NOT NULL AND "boards"."owner_id" = ?)``.
       **OR** is never used (there is no ``Q(...) | Q(...)``).
   * - UPDATE Query
     - —
     - Reached
     - ``boards/ordering.py:40`` and ``:47`` ``bulk_update`` change the
       ``list`` and ``position`` of moved cards. ``boards/views.py:118``
       saves an edited card.
   * - DELETE Query
     - —
     - Reached
     - ``boards/views.py:78`` (delete a board) and ``boards/ordering.py:55``
       (delete a card). ``on_delete=models.CASCADE`` (``models.py:31``, ``:44``)
       also deletes a board's lists and cards.
   * - LIMIT Clause
     - 9
     - Reached
     - ``boards/views.py:54`` ``[:5]`` → ``LIMIT 5`` for the "due soon" panel.
   * - ORDER BY Clause
     - 9
     - Reached
     - ``boards/views.py:54`` ``ORDER BY "cards"."due_date" ASC``, and the
       ``Meta.ordering`` default ordering in ``models.py`` (``-created_at``;
       ``position, id``).
   * - Functions
     - 10
     - Reached
     - The aggregate functions ``COUNT`` (``boards/views.py:49``) and ``MAX``
       (``boards/ordering.py:16``, used to find the next position).
   * - GROUP BY Clause
     - 11
     - Reached
     - ``boards/views.py:49``: ``.annotate(Count(...))`` generates
       ``GROUP BY "boards"."id", ...`` to count the lists and cards on each board.
   * - DISTINCT Keyword
     - 12
     - Reached
     - ``boards/views.py:49`` ``Count("lists", distinct=True)`` →
       ``COUNT(DISTINCT "lists"."id")``. Without it, the join to the cards
       table would count each list more than once.
   * - Constraints
     - 14
     - Reached
     - The migration creates ``PRIMARY KEY``, ``NOT NULL``,
       ``REFERENCES`` (foreign keys) and ``CHECK ("position" >= 0)``.
   * - JOINS
     - 15
     - Reached
     - ``boards/views.py:49``: two ``LEFT OUTER JOIN``\ s (boards → lists → cards).
       ``boards/views.py:53``: two ``INNER JOIN``\ s (cards → lists → boards).
       The ``_user_card`` lookup (``views.py:27``) joins three tables to check
       who owns a card.
   * - NULL Values
     - 17
     - Reached
     - ``Card.due_date`` allows NULL (``models.py:46``, ``due_date date NULL``).
       ``boards/views.py:52`` filters ``IS NOT NULL``, and ``due_status``
       (``models.py:57``) treats a NULL date as "no chip".
   * - ALIAS Syntax
     - 18
     - Reached
     - ``boards/views.py:49`` → ``COUNT(...) AS "list_count"`` and
       ``AS "card_count"``. ``boards/ordering.py:16`` aliases ``MAX`` as ``m``.
   * - Transactions
     - 25
     - Reached
     - ``boards/ordering.py:25`` and ``:52`` ``@transaction.atomic`` make each
       card move or delete all-or-nothing. The migration itself runs inside
       ``BEGIN; ... COMMIT;``.
   * - AUTO INCREMENT
     - 27
     - Reached
     - Every table's ``id`` is
       ``integer NOT NULL PRIMARY KEY AUTOINCREMENT`` (from ``BigAutoField``).
   * - Injection
     - 28
     - Reached
     - It is **prevented**: the ORM sends values as bound parameters and never
       pastes them into the SQL text. ``card_move`` (``boards/views.py:144``)
       turns the input into ``int`` and rejects anything else. Every lookup is
       limited to the logged-in user. Forms use ``{% csrf_token %}``.
       ``boards/tests.py:62``–``:72`` checks that one user cannot reach
       another user's data.
   * - Date & Time
     - 30
     - Reached
     - ``Board.created_at`` (``datetime``, set automatically), ``Card.due_date``
       (``date``), the ``due_status`` date math in ``models.py:57``, and
       ``TIME_ZONE = "America/New_York"`` in ``config/settings.py:71``.
       The date math is done in Python, not with SQLite's ``date()`` function.
   * - DBMS Commands
     - —
     - Partly
     - Django management commands drive the database: ``makemigrations``,
       ``migrate``, ``createsuperuser``, ``sqlmigrate`` (README). The project
       does not use the ``sqlite3`` shell dot-commands (``.tables``,
       ``.schema``).
   * - Syntax
     - —
     - Partly
     - Correct SQL syntax is produced for CREATE, SELECT, INSERT, UPDATE and
       DELETE, but Django generates all of it. None of it is written by hand.
   * - Indexes
     - 20
     - Partly
     - The migration creates ``CREATE INDEX "cards_list_id_..." ON "cards"
       ("list_id")`` (and the same on ``boards.owner_id`` and
       ``lists.board_id``). Django adds these automatically for foreign keys.
       There is no index I designed myself (for example on ``position`` or
       ``due_date``).
   * - Hierarchical
     - 31
     - Partly
     - The data is a fixed four-level hierarchy: user → board → list → card.
       Lookups walk it with ``list__board__owner`` (``views.py:27``). There is
       no self-referencing table or recursive ``WITH RECURSIVE`` query.

Objectives the Project Does Not Reach
=====================================

.. list-table::
   :header-rows: 1
   :widths: 18 7 75

   * - Objective
     - Comp. #
     - Why not / how it could be added
   * - ATTACH Database
     - 2
     - The project only uses one database file, ``db.sqlite3``.
   * - DETACH Database
     - 2
     - No database is ever attached, so there is nothing to detach.
   * - DROP Table
     - 3
     - There is only one migration and no table has been removed yet.
   * - LIKE Clause
     - 8
     - There is no search. A card search box using ``title__icontains``
       would generate ``LIKE``.
   * - GLOB Clause
     - 8
     - Not used. Django has no lookup that generates ``GLOB``, so it would
       need raw SQL.
   * - HAVING Clause
     - 11
     - ``GROUP BY`` is used, but the grouped results are never filtered. For
       example, "boards with more than 10 cards" would use
       ``.filter(card_count__gt=10)`` after ``annotate``.
   * - PRAGMA
     - 13
     - The project issues no PRAGMA statements. (Django turns on
       ``PRAGMA foreign_keys`` internally, but that is not project code.)
   * - UNION Clause
     - 16
     - No query combines two result sets.
   * - Triggers
     - 19
     - Card positions are kept in order by Python code (``ordering.py``),
       not by a database trigger.
   * - INDEXED BY Clause
     - 21
     - No query forces a particular index.
   * - ALTER TABLE Command
     - 22
     - The schema has never changed after ``0001_initial``. A second
       migration that adds a field (for example a card description)
       would generate ``ALTER TABLE``.
   * - TRUNCATE TABLE Command
     - 23
     - Never used. (SQLite has no ``TRUNCATE``; the equivalent is
       ``DELETE FROM`` with no ``WHERE``.)
   * - Views
     - 24
     - There are no SQL views. Django's "views" (``boards/views.py``) are
       Python request handlers and have nothing to do with ``CREATE VIEW``.
   * - Subqueries
     - 26
     - Related data is loaded with separate prefetch queries and joins, not
       nested ``SELECT``\ s.
   * - EXPLAIN
     - 29
     - No query plan is ever examined. ``queryset.explain()`` would run it.
   * - VACUUM
     - 29
     - The database file is never compacted.

Competency Topics at a Glance
=============================

.. list-table::
   :header-rows: 1
   :widths: 15 85

   * - Status
     - Competency topics
   * - Reached (15)
     - 1 Data Type, 4 Operators, 5 Expressions, 6 WHERE, 9 LIMIT and ORDER BY,
       10 Functions, 12 DISTINCT, 14 Constraints, 15 JOINS, 17 NULL Values,
       18 ALIAS, 25 Transactions, 27 AUTO INCREMENT, 28 Injection,
       30 Date and Time
   * - Partly (4)
     - 7 AND and OR (only AND), 11 GROUP BY and HAVING (only GROUP BY),
       20 Indexes (only automatic ones), 31 Hierarchical (fixed hierarchy,
       no recursive query)
   * - Not reached (12)
     - 2 ATTACH and DETACH, 3 DROP Table, 8 LIKE and GLOB, 13 PRAGMA,
       16 UNION, 19 Triggers, 21 INDEXED BY, 22 ALTER TABLE,
       23 TRUNCATE TABLE, 24 Views, 26 Subqueries, 29 EXPLAIN and VACUUM