Hi,
I wonder if there is a way to declare a particular column of a table as
"readonly", either for the purpose of documenting the model, or to get
early error should someone try to update it.
Some context: I have to maintain an old application, based on
PostgreSQL, with several surrounding tools (mainly a PHP (bleach :-!)
frontend and set of "services" built with Python+SQLObject). At the same
time, I'm slowly developing a "future" replacement, rewriting the whole
stack, starting from the DB layer using SQLAlchemy.
Implementing a new "feature", where I have to rewrite the logic that
keeps up-to-date a "counter" column, I thought of replacing that column
with a "computed value" (a.k.a. "virtual column"), using a PostgreSQL
SQL function that compute that counter on-the-fly [1]: this allowed me
to avoid touching dozens of places where that column is read in the PHP
code (what a relief!).
To illustrate, where I had a table such as
CREATE TABLE something (id SERIAL, name TEXT, counter INTEGER)
I now have
CREATE TABLE something (id SERIAL, name TEXT)
CREATE FUNCTION counter(something)
RETURNS INTEGER AS $$
SELECT count(*) FROM something_else se
WHERE se.something_id = $1.id
$$ STABLE SQL
and thus existing queries such as
SELECT s.name, s.counter
FROM something AS s
work as before.
For now, I just left the SA Table definition (I'm using classic mappings)
untouched, that is something like
from sqlalchemy import Column, Integer, MetaData, Table, Text
metadata = MetaData()
something = Table("something", metadata,
Column("id", Integer, primary_key=True),
Column("name", Text),
Column("counter", Integer))
but as said, I looked around to see if there is a way to inform SA that
that "counter" column cannot be updated, just read. I tried to use the
Computed() thing like
from sqlalchemy import Computed
something = Table("something", metadata,
Column("id", Integer, primary_key=True),
Column("name", Text),
Column("counter", Integer, Computed("counter")))
but accordingly to the documentation [2] "SA behavior ... is currently
that the value [assigned to the column] will be ignored", so that does
not bring me any advantage.
On the ORM side, I could "hide" the concrete column and expose it thru a
readonly property, but I'd like to have a similar safety-belt also at
the Core layer, because many services/APIs will be written at that
level.
Do you have any suggestion?
Thanks in advance,
bye, lele.
[1]
https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-COMPOSITE-FUNCTIONS
[2] https://docs.sqlalchemy.org/en/20/core/defaults.html#computed-ddl
--
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
[email protected] | -- Fortunato Depero, 1929.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/87y1mullgf.fsf%40metapensiero.it.
