Review main Merged

57218[TASK] Reduce number of needed SQL queries for resorting of records

Markus Klein CR +2 V +2 Expert +560 −442 updated 11/01/19, 12:24

Commit message

This reduces the resorting to require only a single update statement
in contrast to "number of records" statements.
It also only updates the records after the current one, so less records
are touched when resorting.

Now handling of the sorting value is following:
- if no record exists: set interval as sorting number
- if inserted before an element: put in the middle of the existing elements
- if inserted behind the last element: add interval to last sorting number
- if collision: move all subsequent records by 2 * interval, insert
  new record with sorting = collision + interval

Before, in case of the collision all the records from given pid were
resorted. The first item was getting 512 (2*sortIntervals) as sorting,
following records were getting value of
previous record + sortIntervals (768,1024,...).

Note:
I case we have multiple records with the same sorting value,
resorting will not fix that, but just move them up.

New algorithm drawback:
Sorting column values will grow quicker in time as we're always
increasing the sorting value and never tidying the whole table
(thus possibly lowering the max used value).
The current implementation still allows to insert some 80 million
records per colpos if sizeof(int) is only 32bit.
A lot more if we have 64bit.

Changes to the DataHandler->getSortNumber method are mostly comments and
formatting (added guard clause), no change in behavior except for
additional sorting by uid. The additional sorting was required to have
the same test results across database servers.

Description of updated tests:

- workspaces/.../IRRE/ForeignField/Publish/DataSet/copyPage.csv
  workspaces/.../IRRE/ForeignField/Publish/DataSet/copyPageWHotelBeforeParentContent.csv
  workspaces/.../IRRE/ForeignField/PublishAll/DataSet/copyPage.csv
  workspaces/.../IRRE/ForeignField/PublishAll/DataSet/copyPageWHotelBeforeParentContent.csv

  Now copied tx_irretutorial_1nff_price records have the same
  sorting values/order as original records (1,2,3 instead of 1,3,2).
  So workspace actions result in the same values as live operations.

- backend/.../Controller/Page/Localization/CSV/DataSet/CreatedElementOrdering.csv
  Order of the records in both languages is kept (before and after the patch),
  It might look wrong that a record 2.5 in translation is between 1 and 2
  and not between 2 and 3, but keep in mind that colpos is taken into account.
  So record 2.5 has just to be after record 1 (only the two are in colpos 0).

- install/.../Updates/RowUpdater/DataSet/recordsCanBeUpdated.csv
  core/.../IRRE/ForeignField/Modify/DataSet/localizePageWSynchronization.csv
  core/.../IRRE/ForeignField/Modify/DataSet/createNLocalizeParentContentNHotelNOfferChildren.csv
  core/.../IRRE/ForeignField/Modify/DataSet/createNLocalizeParentContentNHotelNOfferChildrenWOSortBy.csv
  core/.../IRRE/ForeignField/Modify/DataSet/localizeNCopyPageWSynchronization.csv
  core/.../IRRE/ForeignField/Modify/DataSet/localizePageWExclude.csv
  core/.../IRRE/ForeignField/Modify/DataSet/localizePageWithSynchronizationAndCustomLocalizedHotel.csv
  core/.../IRRE/ForeignField/Modify/DataSet/localizeParentContentNCreateNestedChildrenWLanguageSynchronization.csv
  core/.../IRRE/ForeignField/Modify/DataSet/localizeParentContentSynchronization.csv
  core/.../IRRE/ForeignField/Modify/DataSet/localizeParentContentChainLanguageSynchronizationSource.csv
  core/.../IRRE/ForeignField/Modify/DataSet/localizeParentContentWAllChildren.csv
  core/.../IRRE/ForeignField/Modify/DataSet/localizePageAddMonoglotHotelChildNCopyPageWSynchronization.csv
  core/.../IRRE/ForeignField/Modify/DataSet/localizePageNAddMonoglotHotelChildWSynchronization.csv
  core/.../IRRE/ForeignField/Modify/DataSet/localizePageNAddHotelChildWSynchronization.csv
  core/.../IRRE/ForeignField/Modify/DataSet/localizePageNAddHotelChildWExclude.csv
  core/.../IRRE/ForeignField/Modify/DataSet/copyParentContentToLanguageWAllChildren.csv
  workspaces/.../IRRE/ForeignField/Publish/DataSet/createNLocalizeParentContentNHotelNOfferChildren.csv
  workspaces/.../IRRE/ForeignField/PublishAll/DataSet/localizeParentContentWAllChildren.csv
  workspaces/.../IRRE/ForeignField/PublishAll/DataSet/createNLocalizeParentContentNHotelNOfferChildren.csv
  workspaces/.../IRRE/ForeignField/PublishAll/DataSet/createNLocParentNHotelNOfferChildrenNDiscardCreatedParent.csv
  workspaces/.../IRRE/ForeignField/PublishAll/DataSet/createNLocParentNHotelNOfferChildrenNDiscardLocParent.csv
  workspaces/.../IRRE/ForeignField/Modify/DataSet/copyPage.csv
  workspaces/.../IRRE/ForeignField/Modify/DataSet/localizeParentContentWAllChildren.csv
  workspaces/.../IRRE/ForeignField/Modify/DataSet/createNLocalizeParentContentNHotelNOfferChildren.csv
  workspaces/.../IRRE/ForeignField/Modify/DataSet/createNLocalizeParentContentNHotelNOfferChildrenWOSortBy.csv
  workspaces/.../IRRE/ForeignField/Modify/DataSet/createNLocParentNHotelNOfferChildrenNDiscardCreatedParent.csv
  workspaces/.../IRRE/ForeignField/Modify/DataSet/copyPageWHotelBeforeParentContent.csv
  workspaces/.../IRRE/ForeignField/Publish/DataSet/localizeParentContentWAllChildren.csv
  workspaces/.../IRRE/ForeignField/Publish/DataSet/createNLocParentNHotelNOfferChildrenNDiscardCreatedParent.csv
  workspaces/.../IRRE/ForeignField/Publish/DataSet/createNLocParentNHotelNOfferChildrenNDiscardLocParent.csv

  Order of the records is kept, less records have changed sorting values,
  as now we're just resorting records after the current one.

- impexp/.../DatabaseAssertions/importPagesAndRelatedTtContentWithDifferentImageToExistingData.csv
  impexp/.../DatabaseAssertions/importPagesAndRelatedTtContentWithSameImageToExistingData.csv
  now existing page sorting is not changed when importing data.

- workspaces/.../IRRE/CSV/Publish/DataSet/moveParentContentToDifferentPageNChangeSorting.csv
  workspaces/.../IRRE/CSV/Modify/DataSet/moveParentContentToDifferentPageNChangeSorting.csv
  workspaces/.../IRRE/CSV/PublishAll/DataSet/moveParentContentToDifferentPageNChangeSorting.csv

  in CSV relation we don't care about the child sorting field as
  the order is determined by the value of the CSV field.

Review

  • Code review CR +2

    Imported before Forger kept the single votes, the next import adds them.

  • Verified V +2

    Imported before Forger kept the single votes, the next import adds them.

Issues 1

Files 46

Show 26 more files