# UGE / L2 / Intro to relational databases / Python project prototype ## Background and goals The introductory course to relational databases ends with a project illustrating the use of a relational database together with a simple CRUD web application. The Python language could be a better fit compared to PHP for this kind of assignment as both students and the teaching staff are already familiar with the former from preceding units. To showcase Python as a platform suitable for this purpose, a small demo project specified in `./demo-project-specs.md` will be implemented. The present document aims to motivate the functional and technical choices which have been made. ## Interpretation of the specifications Undefined aspects of the specifications are clarified as follows, with the intent of keeping the project as simple as possible: * the platform only deals with one currency ([simoleons], arbitrarily), * the platform only supports one language (English, arbitrarily), * each user only has one account/wallet/balance (all three terms synonymous), * there is no notion of financial order (transactions are immediate), * only the effective transactions are logged, the rejected ones are not, * fees are billed on top of internal transfers instead of taking a cut. [simoleons]: https://en.wiktionary.org/wiki/simoleon ## Database model ### Entity-relationship model The database model is fully described as types, tables and constraints in `./sql/tables.sql`. The file is annotated with comments and remarks explaining the design choices and highlights some additional constraints which remain to be enforced at upper levels. An entity-relationship diagram of the model can be generated from the SQL table descriptions using a tool like [SQLFairy] (_note: SQLFairy [crashes] with recent PostgreSQL dialects, so it is not usable here_). [SQLFairy]: http://sqlfairy.sourceforge.net/ [crashes]: https://rt.cpan.org/Public/Bug/Display.html?id=138045 ### Consistency The consistency of the account balance and the financial transaction log can be ensured through multiple means such as executing modification and insertions in SQL transactions at the application level, or using triggers within the database itself. The former option is chosen over the latter to avoid implicit actions. Additional constraints to prevent alterations of the financial transaction log such as deletions are omitted here as the web application will act as the sole gateway to interact with the database and will offer no way of doing this kind of operations. For the same reason, the use of stored procedures was not retained. ## Project structure overview * `./sql/` * `tables.sql`: database initialisation statements * `queries.sql`: annotated SQL queries (from which the DAO is derived) * `./flake.nix`: project runtime and development environment description ## Development environment The development and execution environment is fully described using the [Nix] language and package manager as a [Nix Flake] in `./flake.nix`. This allows creating reproducible environments containing all the software dependencies of the program itself, as well as optional development tools. (_Note: this projects requires Nix version 2.4 or higher_). [Nix]: https://nixos.org/guides/how-nix-works.html [Nix Flake]: https://www.tweag.io/blog/2020-05-25-flakes/ This environment can be used on NixOS, MacOS, or any Linux system having Nix installed. In principle, Nix should also be usable on Windows through the Windows Subsystem for Linux ([WSL]) compatibility layer. Full virtual machines and containers can also be derived from the same description file. [WSL]: https://docs.microsoft.com/en-us/windows/wsl/about All the commands in this section have to be run within the provided development shell, which can be entered by running the following command at the root directory of the project: ```sh nix develop ``` ### Local database The Nix Flake development shell provides its own self-contained PostgreSQL server, configured to operate independently of any other instances running on the same system. All data are written to the `./development_database/pgadata` directory. The database server can be initialised by running the following command: ```sh initdb --no-locale --encoding UTF8 --auth-host reject --auth-local peer ``` The local development PostgreSQL server can then be started by running the following command, with the `$PGHOST` environment variable automatically set by the development shell: ```sh postgres -h "" -k "$PGHOST" -d 2 ``` This server listens to local requests through a UNIX domain socket located at `./development_database/.s.PGSQL.5432`, to which programs run in the development shell will implicitly automatically connect to. The development shell ships with both the `psql` and [`pgcli`][pgcli] tools to interact directly with the database. The latter provides additional features such as syntax highlighting and better auto-completion. [pgcli]: https://www.pgcli.com/ A new local database for the application can be created and its table can be initialised with: ```sh createdb app psql app < ./sql/tables.sql ``` Should the need arise, this database can be deleted with the following command before being created and initialised again: ```sh dropdb app ``` ## Copyright and licensing Copyright (C) 2021 Pacien TRAN-GIRARD. This project is distributed under the terms of European Union Public Licence version 1.2, a copy of which is provided in `./licence.txt`.