On Mon, Sep 3, 2018 at 4:40 AM Chris Angelico <ros...@gmail.com> wrote:
> On Mon, Sep 3, 2018 at 6:31 PM, Wes Turner <wes.tur...@gmail.com> wrote:
> >
> >
> > On Mon, Sep 3, 2018 at 4:17 AM Chris Angelico <ros...@gmail.com> wrote:
> >>
> >> On Mon, Sep 3, 2018 at 5:23 PM, Jacco van Dorp <j.van.d...@deonet.nl>
> >> wrote:
> >> > This feels really useful to me to make some quick changes to a
> database
> >> > -
> >> > perhaps a database layer could return an class of type Recordclass,
> and
> >> > then
> >> > you just simply mutate it and shove it back into the database.
> >> > Pseudocode:
> >> >
> >> > record = database.execute("SELECT * FROM mytable WHERE primary_key =
> >> > 15")
> >> > record.mostRecentLoggedInTime = time.time()
> >> > database.execute(f"UPDATE mytable SET mostRecentLoggedInTime =
> >> > {record.mostRecentLoggedInTime} WHERE primary_key =
> >> > {record.primary_key}":)
> >> >
> >> > Or any smart database wrapper might just go:
> >> >
> >> > database.updateOrInsert(table = mytable, record = record)
> >> >
> >> > And be smart enough to figure out that we already have a primary key
> >> > unequal
> >> > to some sentinel value like None, and do an update, while it could do
> an
> >> > insert if the primary key WAS some kind of sentinel value.
> >>
> >> In its purest form, what you're asking for is an "upsert" or "merge"
> >> operation:
> >>
> >> https://en.wikipedia.org/wiki/Merge_(SQL)
> >>
> >> In a multi-user transactional database, there are some fundamentally
> >> hard problems to implementing a merge. I'm not 100% certain, so I
> >> won't say "impossible", but it is certainly *extremely difficult* to
> >> implement an operation like this in application-level software without
> >> some form of race condition.
> >
> >
> >
> http://docs.sqlalchemy.org/en/latest/orm/contextual.html#contextual-thread-local-sessions
> > - scoped_session
> >
> >
> http://docs.sqlalchemy.org/en/latest/orm/session_state_management.html#merging
> >
> > http://docs.sqlalchemy.org/en/latest/orm/session_basics.html
> >
> > obj = ExampleObject(attr='value')
> > assert obj.id is None
> > session.add(obj)
> > session.flush()
> > assert obj.id is not None
> > session.commit()
>
> Yep. What does it do if it's on a back-end database that doesn't
> provide a merge/upsort intrinsic? What if you have a multi-column
> primary key? There are, of course, easier sub-forms of this (eg you
> mandate that the PK be a single column and be immutable), but if there
> is any chance that any other client might simultaneously be changing
> the PK of your row, a perfectly reliable upsert/merge basically
> depends on the DB itself providing that functionality.
>
There's yet another argument for indeed, immutable surrogate primary keys.
With appropriate foreign key constraints,
changing any part of the [composite] PK is a really expensive operation
because all references must also be updated (w/ e.g. ON UPDATE CASCADE),
and that doesn't fix e.g. existing URLs or serialized references in cached
JSON documents.
Far better, IMHO, to just enforce a UNIQUE constraint on those column(s).
UUIDs don't require a central key allocation service
(such as AUTOINCREMENT, which is now fixed in MySQL AFAIU);.
Should the __hash__() of a recordclass change when attributes are modified?
http://www.attrs.org/en/stable/hashing.html has a good explanation.
In general,
neither .__hash__() nor id(obj) are good candidates for a database primary
key
because when/if there are collisions (birthday paradox)
-- e.g. when an INSERT or UPSERT or INSERT OR REPLACE fails --
it has to change.
Sorry getting OT,
something like COW immutability is actually desirable with SQL databases,
too.
Database backups generally require offline intervention in order to
rollback;
if there's even a backup which contains those transactions.
https://en.wikipedia.org/wiki/Temporal_database#Implementations_in_notable_products
(SELECT, )
https://django-reversion.readthedocs.io/en/stable/
> ChrisA
> _______________________________________________
> Python-ideas mailing list
> Python-ideas@python.org
> https://mail.python.org/mailman/listinfo/python-ideas
> Code of Conduct: http://python.org/psf/codeofconduct/
>
_______________________________________________
Python-ideas mailing list
Python-ideas@python.org
https://mail.python.org/mailman/listinfo/python-ideas
Code of Conduct: http://python.org/psf/codeofconduct/
