Camelot a good tool for me

[Lele Gaifax]

Chris Angelico <rosuav at gmail.com> writes:

> On Sat, May 23, 2015 at 5:12 AM, Lele Gaifax <lele at metapensiero.it> wrote:
>> You are conflating two different layers, core and ORM. ORM relationships can
>> be declared either on the parent or on the child, it's up to your taste.
>
> Not sure why that's distinguishable. If I have two tables like this:
>
> Users:
>     id sequential primary key
>     name text
>
> Tasks
>     id sequential primary key
>     owner integer
>     assignee integer
>
> Both the owner and the assignee refer to the Users table; the owner is
> a mandatory connection (every task was created by someone, who
> initially owns it), and the assignee is an optional connection (a
> newly-created task isn't assigned to anyone). With me so far? Okay.
>
> Now, if I were to represent these tables in SQLAlchemy, obviously I
> need to have foreign key relationships encoded in SQLAlchemy. But if
> I'm to enforce these relationships on the underlying database, it's
> equally obvious that I need foreign key constraints. I would expect
> that a relationship encoded in SQLAlchemy should cause the creation of
> a constraint in the database. They're fundamentally the same thing.

No. A "relationship" is an ORM thingie, and can happily exist without an
underlaying constraint in the database.

> My point about backwards is that my tables here are declared in a
> strict order: parent table, then child table. In the child table, a
> constraint is created by saying "references Users", and the Users
> table already exists. At no point is there ever a forward reference.
> Code would look like this:
>
> create table Users (id serial primary key, name text not null default '');
> create table Tasks (id serial primary key, owner integer not null
> references Users, assignee integer references Users);
>
> But with SQLAlchemy, you have a tag in the Users table's definition
> saying that it has a relationship with Tasks, as well as a foreign key
> in Tasks stating the connection to Users. That violates "Define Before
> Use", which isn't a strict policy, but it does feel a little bit
> "dirty".

As said, that's not how I'd code it, as I'd probably implement those classes
as

class User(Base):
     id = Column(...)
     name = Column(...)

class Task(Base):
     id = Column(...)
     owner_id = Column(...)
     assignee_id = Column(...)

     owned_by = relationship('User', primaryjoin='User.id==Task.owner_id',
                             backref='own_tasks')
     assigned_to = relationship('User', primaryjoin='User.id==Task.assigned_id',
                             backref='assigned_tasks')

>>> When magic works, it's great; but when anything goes wrong, it's harder to
>>> see what happened.
>>
>> The same can be said of almost any upper layer in a software stack.
>
> Precisely. All magic has to justify itself. Some can, easily. Some
> can't. A lot is in the middle, where it's part of the tradeoffs.
>
>>> Also, when does a transaction begin and end?
>>
>> When I need transactions (that is, when I'm changing the database) I'm very
>> picky and use explicit begins, commits and rollbacks, so I don't recall
>> experiencing that doubt.
>
> That's all very well when you write your own code. Now try picking up
> someone else's code. Or, for a mid-way concern, try explaining to a
> junior developer how to make sure his transactions are right. With
> psycopg2, it's easy enough to do this:
>
> with conn, conn.cursor() as cur:
>     cur.execute("....")
>     cur.execute("....")

You're kidding, of course: on SA side you imagine a complex code written by
somebody else, while on the other side a plain sequence of statements. You can
write almost the same code either with SA (which at it's low level has a plain
DBAPI connection):

  http://docs.sqlalchemy.org/en/rel_1_0/core/connections.html#using-transactions

> When the with block exits, the transaction is either committed (if all
> went well) or rolled back (if an exception was raised). It's very
> simple, easy to do, and easy to audit ("all SQL queries must be inside
> a with block that grants a cursor", and possibly "cursor-granting with
> blocks must not be nested").

As you can see, there is almost no difference when using the equivalent SA
idiom.

>
>>> If you session.commit() in the middle of iterating over a query, will it
>>> break the query? What if you roll back? Can you see, instantly, in your
>>> code?
>>
>> Why would you do that? Are you closing your files while you iterate them,
>> without leaving the loop in some way at the same time?
>
> There's advice out there on the internet that says that committing
> periodically in the middle of a big job makes your code run faster.

No, I think you mean "flushing" a session, not committing. And again, that's
usually recommended when using the ORM layer, not at the SQL core we are
talking about here.

> It's from the PHP + MySQL school of thought, where the assumption is
> that finishing is the most important thing, finishing quickly is a
> close second, and guaranteeing correctness isn't even on the radar.
> Now try coping with code that was written under that model.

That's not how I code database-based applications, neither when using plain
DBAPI, nor with SA. I cannot even imagine using such a wierd approach.

> Fortunately, I haven't actually seen anything quite like this in
> SQLAlchemy. The worst I saw was a case where someone was iterating
> over a query and performed another query, which did indeed break the
> fetching of results. But the more magic you have, the less obvious
> that is.

I cannot understand your example: it's not so uncommon the need to perform a
query for each row of a previous one, and it's surely well supported at every
level, in SA.

>> I often have to deal with multiple DB engines at the same time, and being
>> able to "write" my queries with an abstract syntax is very valuable for me.
>
> Hmm, I'm not sure there's all that much that this helps with. Thanks
> to a consistent PEP 249 API, changing database engines is often just a
> matter of changing one import and a connection construction line
> (which you'd have to do anyway, given that the credentials will
> change). What else is there for SQLAlchemy to paper over? The
> differences remain; the common ground is already common.

There are a lot of subtle differences in how SQL is implemented by the various
engines out there. Things like "names quoting", "pagination", "data types",
...

Until you do not need a nice way to write the same thing against different
engines you won't appreciate how nice it is being able to do that :)

ciao, lele.
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
lele at metapensiero.it  |                 -- Fortunato Depero, 1929.