| Role | Assignee | Notes |
|---|---|---|
| Owner | Brent | |
| Approver (project plan) | Kriti | Needs to approve project plan |
| Approver (backend) | Brent | Needs to approve back end spec |
| Contributor (requirements) | Brent | Creates product spec, requirements, GitHub issues |
| Contributor (requirements) | Dom | Creates product spec, requirements, GitHub issues |
| Contributor | Anish | Coding and reviewing |
| Contributor | Dom | Coding and reviewing |
| Contributor | Mukesh | Coding and reviewing |
Data Query Language (DQL) operations are those that query the data stored in a database. The relevant SQL word is SELECT. These operations require knowledge of the database to work. E.g., a function needs a table’s name to SELECT from it. Currently, we write most SELECT statements in Python using SQLAlchemy, necessitating the reflection of the database state into memory in Python and using that state to build queries.
Our current setup for this is:
All of these problems are related to the fact that we’re building the SQL queries to run DQL operations in Python.
Create a framework that sets up a view for each table in the database with an algorithmically derived name and column names. The view should have a name that can be determined if you have the underlying tables’s OID. The columns of the view should have names determined by the attnums of the corresponding columns in the underlying table. This is already prototyped in the file db/sql/2_msar_views.sql.
Create a function for each desired DQL operation on the databse using SQL or PL/pgSQL.
Replace the current Python functions performing DQL operations with thin wrappers for these functions. Use an as-yet unknown query builder to compose complete queries when (if) needed.
Note that this section may take more lateral thinking than for the DDL and DML phases. The reason is that we’re composing more ad-hoc DQL operations using the data explorer, and so some care needs to be used.
Refactor to remove SQLAlchemy objects from calls using Python DQL functions:
schema_name, (schema_name, table_name), or (schema, table_name, column_name) identifiers. Instead, prefer schema_oid, table_oid or (table_oid, attnum) identifiers (may require modifying callers slightly, or scaffolding).TODO
Note: Parts of this timeline are delayed, since they’re blocked by the removal of DDL, DML, and Brent’s parental leave.
| Date | Outcome |
|---|---|
| 2023-03-20 | Prototyping work starts |
| 2023-05-05 | Implementation spec and prototyping complete |
| 2023-05-12 | Implementation spec approved |
| 2023-05-26 | All needed DQL SQL Functions written |
| 2023-06-02 | All thin python wrappers written |
| 2023-06-09 | Refactor and clean up complete |