aboutsummaryrefslogtreecommitdiff
path: root/readme.md
diff options
context:
space:
mode:
Diffstat (limited to 'readme.md')
-rw-r--r--readme.md53
1 files changed, 52 insertions, 1 deletions
diff --git a/readme.md b/readme.md
index 4b6e222..7500744 100644
--- a/readme.md
+++ b/readme.md
@@ -64,13 +64,64 @@ of operations. For the same reason, the use of stored procedures was not
64retained. 64retained.
65 65
66 66
67## Project structure overview 67## Web application
68
69### Platform
70
71#### Interface with the database
72
73Object-Relational Mappers ([ORM]) such as [SQLAlchemy] are sometimes used to
74partially or fully abstract the database and its entities behind objects in the
75Object-Oriented Programming ([OOP]) sense. With those tools, it is possible to
76generate tables, indexes, and constraints from annotated classes. They also
77allow automatic querying and implicit joins to perform information retrieval,
78and inserting and updating records in the database directly by setting fields
79on linked objects.
80
81[ORM]: https://en.wikipedia.org/wiki/Object-relational_mapping
82[SQLAlchemy]: https://www.sqlalchemy.org/
83[OOP]: https://en.wikipedia.org/wiki/Object-oriented_programming
84
85However, the [object-relational impedance mismatch problems][ORIM] often render
86the use of ORMs impractical especially for complex queries, more often than not
87resulting in the developers bypassing the ORM altogether. The tools also tend
88to introduce performance issues due to the implicit queries they generate in
89situations in which developers do not always expect them to.
90
91[ORIM]: https://en.wikipedia.org/wiki/Object-relational_impedance_mismatch
92
93In the context of an database course aiming to teach SQL, such level of
94abstraction is also a hindrance by itself. Therefore, the use of an ORM has
95been excluded for this project.
96
97More direct and less intricate forms of mappings, such as transparent result
98tuples to Python immutable named tuples conversion, are preferred instead.
99Features of this kind, which are directly provided by database drivers like
100[psycopg], are often sufficient by themselves.
101
102[psycopg]: https://www.psycopg.org/
103
104It remains desirable to segregate SQL queries into their own modules in order
105to follow the Single-Responsibility Principle ([SRP]). Fortunately, libraries
106such as [embrace] allow easily deriving Data Access Objects ([DAO]) from sets
107of annotated raw SQL queries, exposing them as callable Python functions.
108
109[SRP]: https://en.wikipedia.org/wiki/Single_responsibility_principle
110[embrace]: https://pypi.org/project/embrace/
111[DAO]: https://en.wikipedia.org/wiki/Data_access_object
112
113### Project structure overview
68 114
69* `./sql/` 115* `./sql/`
70 * `tables.sql`: database initialisation statements 116 * `tables.sql`: database initialisation statements
71 * `queries.sql`: annotated SQL queries (from which the DAO is derived) 117 * `queries.sql`: annotated SQL queries (from which the DAO is derived)
72* `./flake.nix`: project runtime and development environment description 118* `./flake.nix`: project runtime and development environment description
73 119
120### Security considerations
121
122* SQL injections are prevented by using proper query parameters substitution,
123 automatically handled by the embrace and psycopg libraries.
124
74 125
75## Development environment 126## Development environment
76 127