On Tue, Dec 4, 2018 at 7:36 AM Stanislav Lobanov <n10101...@gmail.com> wrote: > > Hello. > > I have a table with schema: > > name: users > fields: id int, name text, start datetime, end datetime > primary key: id, start, end > > This is kind of a historical table, where each row defines separate > "historical version" of an object. There are a single business User entity > (model) with possibly many historical versions. > > Such table structure makes it very hard to define relationships and work with > related objects. Also it is hard to work with "current" version of User > entity, because to retreive it we need to query it with "now() between start > and end" constraint. > > So i thought that maybe i can create a view for that table that will hold > only current versions and map that view onto User entity to hide all > historical complexities and compound PK from sqlalchemy. > > The question: is it possible to implement a mapping that will read from view > but write into real table?
so first off, I have extensive experience many years ago working on a large system that was architected in this way. Everything we read from was a view. I will tell you that this system had enormous problems in that the views out of the gate had performance implications which would multiply almost exponentially when you tried to do things like simple joins and such, because the Oracle query optimizer couldn't handle that you had these views which each represented a non-simple SELECT to start with, which were then being joined to form what were effectively joins of subqueries. to overcome the performance limitation of a view, people use a materialized view, that is, the database is actually re-persisting your view of data separately. Early SQLAlchemy versions expected there would be a strong notion of this kind of operation, that is, where the same mapped class would have many different ways of being read, and for that reason it was expected that there'd be lots of mapper() objects for one particular class. You can still use this pattern using the so-called non-primary mapper pattern, introduced at https://docs.sqlalchemy.org/en/latest/orm/nonstandard_mappings.html?highlight=non%20primary%20mapper#multiple-mappers-for-one-class and still somewhat relevant for in particular a certain kind of relationship pattern. But using this pattern as a first class query interface is probably not well suited for the modern Query interface, since it means you'd need to build up queries using the "mapper()" object and not the mapped class, and there's a lot of patterns that probably don't work very well by doing things this way. another way this might be possible would be to map entirely separate classes to the views vs. the table. If I truly had to use the view/table pattern, I'd probably do it this way. For persistence, there are two ways to make it work. One is to build a before_flush() handler that receives the view-mapped classes, copies their changes over to table-mapped instances of each class, then flushes those instead. The other is to actually rewrite the INSERT/UPDATE/DELETE queries using a before_execute() or before_cursor_execute() event. This is probably pretty easy to do if your views map to the tables in a 1-1 fashion and you are just omitting some WHERE criteria. However, the general problem space you refer to right up top, that is, where you need to SELECT/CRUD a set of rows in a table that are limited by some flag or date range is very common in SQLAlchemy and it is dealt with by altering the Query object to simply add the additional WHERE criteria to queries against the tables themselves, that is, don't use a "view", define your "view" in terms of your Python code. For a simple case like tables that need to filter some rows, this is a lot easier than using views as you don't need to maintain those views, suffer the performance implications of them, or struggle with materialized views including that they aren't always up to date. The current canonical example for the "filtered query" is on the wiki (still converting to Github's syntax but the code is there) which I have recently modernized, and two examples can be seen at https://github.com/sqlalchemy/sqlalchemy/wiki/FilteredQuery. > > For example, view can have fields id (pk) and name. > > I know that there are great examples of versioning with sqlalchemy but i want > to hide non-functional implementation details from my business code/entities > with view. > > Thanks! > > -- > 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 post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
