Issue 10028

Title
Use of Update-From for migration
Priority
feature
Status
chatting
Nosy list
ced, mrichez, tbruyere
Assigned to
Keywords

Created on 2021-01-25.11:13:56 by mrichez, last changed 3 months ago by ced.

Messages

Author: [hidden] (ced) Tryton committer Tryton translator
Date: 2021-10-17.10:35:24

Not sure we must update former migration because:

  • we know it is working when new code should be tested and it could be difficult for old migration
  • the migration is run only once so for new installation and already migrated installation it has no benefit

Also this is still quiet recent release of SQLite. So starting to use it will require to write both cases with a conditional test about the feature being supported or not (so it doubles the work load).
So for me as long as it is only used for migration we can keep not using this feature and when the release will be old enough we could use it without test. This is valid as long as the ORM does not need it.

Author: [hidden] (mrichez)
Date: 2021-01-25.11:13:55

In previous version of SQLite, the UPDATE-FROM was not supported.

This is now allowed since version 3.33.0:

The UPDATE-FROM idea is an extension to SQL that allows an UPDATE statement to be driven by other tables in the database. The "target" table is the specific table that is being updated. With UPDATE-FROM you can join the target table against other tables in the database in order to help compute which rows need updating and what the new values should be on those rows. UPDATE-FROM is supported beginning in SQLite version 3.33.0 (2020-08-14).

https://sqlite.org/lang_update.html#update_from

We could update some migration scripts in a near future to use this feature.

Example
Before:

...
dimension_columns = ['length', 'length_uom', 'height', 'height_uom']
columns = []
values = []
for c in dimension_columns:
    columns.append(Column(sql_table, c))
    values.append(package_type.select(Column(package_type, c),
        where=package_type.id == sql_table.type))
cursor.execute(*sql_table.update(
    columns=columns,
    values=values))

After:

...
dimension_columns = ['length', 'length_uom', 'height', 'height_uom']
cursor.execute(*sql_table.update(
    columns=[Column(sql_table, c) for c in dimension_columns],
    values=[Column(package_type, c) for c in dimension_columns],
    from_=[package_type],
    where=package_type.id == sql_table.type))
History
Date User Action Args
2021-10-17 10:35:24cedsetmessages: + msg71051
nosy: + ced
status: unread -> chatting
2021-01-25 11:13:56mrichezcreate

Showing 10 items. Show all history (warning: this could be VERY long)