On Fri, Apr 14, 2023, at 8:03 AM, Lele Gaifax wrote:
> 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.
"readonly" can mean a few different things....
>
> 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.
how does "SELECT s.counter" work if the table does not have an actual "counter"
column?
>
> 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
so here's where "readonly" means different things. if you want to omit
"counter" from your application entirely, you can remove it from the Table()
definition.
Also with some irony I am actually working on a whole different feature right
now where there's a new parameter, which I'm not going to make public yet,
called "_omit_from_statements" for an individual column. From an ORM mapping
perspective, it's essentially the same as if you added this column to the
Mapper exclude_properties list:
https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.Mapper.params.exclude_properties
which will also make that column not present within the mapping or any of the
statements used.
neither of these two things make the column "read only", they just make the
column not really present in the application.
In general, whenever a column has a server side default of this nature, you
want to use FetchedValue or some subclass of this in the column definition, so
the ORM as well as Core knows that something in the server will be generating a
value for this column:
https://docs.sqlalchemy.org/en/20/core/defaults.html#triggered-columns /
https://docs.sqlalchemy.org/en/20/orm/persistence_techniques.html#orm-server-defaults
>
> 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.
Using Computed in this way is equivalent to using FetchedValue. Why is that
a problem?
So far note all of these techniques have to do with "read only", but they are
the usual approaches taken when there's a server generated column that you'd
prefer the application not deal with explicitly.
>
> 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?
true "readonly" at the ORM level, meaning the column is present, it's mapped,
and you want to disallow user code from saying myclass.col = "foo" or some
error is raised, you can only do with attribute events or with a validator:
https://docs.sqlalchemy.org/en/20/orm/mapped_attributes.html#sqlalchemy.orm.validates
or other Python-side object technique.
true "readonly" at the Core level, where any attempt to use
connection.execute() in such a way that data is being sent to this column,
you'd need to use event hooks such as before_cursor_execute()
https://docs.sqlalchemy.org/en/20/core/events.html#sqlalchemy.events.ConnectionEvents.before_cursor_execute
and then do heuristics to figure out what the statement is, and if any of the
parameters point to this column. I would not go this route since it's
overkill and not that reliable for said overkill.
If you want to absolutely disallow client side changes to this column, since
you are setting up PG defaults anyway, using a trigger or a true COMPUTED
column would be the easiest route.
Without using server-side constructs, in a practical sense, simply omitting the
column from the Table or using mapper.exclude_properties is in my experience
sufficient.
>
> 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.
> l...@metapensiero.it | -- 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 sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/87y1mullgf.fsf%40metapensiero.it.
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/1bada8ac-4e60-4952-9974-515a2958719a%40app.fastmail.com.
