Page MenuHomeIn-Portal Phabricator

INP-1811 - Support partitions in grids and direct INSERT/UPDATE clauses
ClosedPublic

Authored by alex on Sep 8 2021, 11:04 AM.

Details

Test Plan

Part 1 (doInsert)

  1. open /index.php file for editing
  2. replace line after $application->Init(); line with $application->Conn->doInsert(array('F1' => 'V1'), 'sample table');
  3. save changes
  4. open Front-End
  5. confirm, that INSERT SQL error, shown in debugger, has table name (the sample table) unescaped

Part 2 (doUpdate)

  1. open /index.php file for editing
  2. replace line after $application->Init(); line with $application->Conn->doUpdate(array('F1' => 'V1'), 'sample table', 'TRUE');
  3. save changes
  4. open Front-End
  5. confirm, that UPDATE SQL error, shown in debugger, has table name (the sample table) unescaped

Part 3 (prepare data)

  1. login to Admin Console
  2. go to ConfigurationWebsiteAdvanced section
  3. enable Track database changes to change log system setting
  4. save changes
  5. re-login (to create session log record) to the Admin Console
  6. execute this SQL (use actual table prefix instead of "52x_"):
ALTER TABLE `52x_Users` DROP INDEX `ResourceId`, ADD UNIQUE INDEX `ResourceId` (`ResourceId`,`PortalUserId`) USING BTREE;

ALTER TABLE 52x_Users PARTITION BY RANGE (PortalUserId) (
  PARTITION FirstHundredUsers VALUES LESS THAN (100),
  PARTITION OtherUsers VALUES LESS THAN (MAXVALUE)
);

Part 3 (test itself)

Test Data

Try this test with every LEFT JOIN clause from below:

  • LEFT JOIN '.TABLE_PREFIX.'Users ON '.TABLE_PREFIX.'Users.PortalUserId = %1$s.PortalUserId (for this test replace 'u. in calculated fields with TABLE_PREFIX . 'Users., and once finished replace back)
  • LEFT JOIN '.TABLE_PREFIX.'Users AS u ON u.PortalUserId = %1$s.PortalUserId
  • LEFT JOIN '.TABLE_PREFIX.'Users AS u USE INDEX (Username) ON u.PortalUserId = %1$s.PortalUserId
  • LEFT JOIN '.TABLE_PREFIX.'Users PARTITION (FirstHundredUsers) AS u USE INDEX (Username) ON u.PortalUserId = %1$s.PortalUserId
  • LEFT JOIN '.TABLE_PREFIX.'Users PARTITION (FirstHundredUsers) AS u ON u.PortalUserId = %1$s.PortalUserId

Test

  1. open core/units/logs/session_logs/session_logs_config.php file for editing
  2. change LEFT JOIN clause of the ListSQL to above provided
  3. save changes
  4. login to Admin Console
  5. go to Logs & ReportsSession Log section
  6. open debugger
  7. confirm, that COUNT(*) clause for this grid doesn't have "LEFT JOIN" to "Users" table
  8. confirm, that no SQL error happened
  9. confirm, that no notices/warnings/errors happens
  10. filter by any non-empty value in the "First Name" column
  11. confirm, that COUNT(*) clause for this grid has "LEFT JOIN" to "Users" table
  12. confirm, that no SQL error happened
  13. confirm, that no notices/warnings/errors happens
  14. remove filter

Diff Detail

Repository
rINP In-Portal
Lint
Automatic diff as part of commit; lint not applicable.
Unit
Automatic diff as part of commit; unit tests not applicable.

Event Timeline

alex created this revision.Sep 8 2021, 11:04 AM
alex requested review of this revision.Sep 8 2021, 11:04 AM
alex edited the test plan for this revision. (Show Details)Sep 8 2021, 11:04 AM
alex updated this revision to Diff 1031.Sep 9 2021, 3:52 AM

Handle case, when table alias isn't used.

alex edited the test plan for this revision. (Show Details)Sep 9 2021, 3:53 AM
erik accepted this revision.Sep 22 2021, 10:37 AM
This revision is now accepted and ready to land.Sep 22 2021, 10:37 AM
This revision was landed with ongoing or failed builds.Nov 13 2023, 2:43 AM
This revision was automatically updated to reflect the committed changes.