Error deleting institution due to foreign key constraint with view table

[Ghada El-Zoghbi]

Mahara: 19.04.0
OS: Linux
DB: Postgres
Browser: FF

We have an institution which no longer has any users. When trying to delete it, it fails due to the foreign key constraint with the view table - which now has the institution field.

Call stack (most recent first):

    log_message("Array to string conversion", 8, true, true, "/var/www/mahara/htdocs/lib/adodb/adodb...", 45) at /var/www/mahara/htdocs/lib/errors.php:520
    error(8, "Array to string conversion", "/var/www/mahara/htdocs/lib/adodb/adodb...", 45, array(size 7)) at /var/www/mahara/htdocs/lib/adodb/adodb-exceptions.inc.php:45
    ADODB_Exception->__construct("postgres8", "adodb_throw", -1, "ERROR: update or delete on table "host" violates ...", "DELETE FROM "host" WHERE "institution" = ? ", array(size 1), object(ADODB_postgres8)) at /var/www/mahara/htdocs/lib/adodb/adodb-exceptions.inc.php:80
    adodb_throw("postgres8", "adodb_throw", -1, "ERROR: update or delete on table "host" violates ...", "DELETE FROM "host" WHERE "institution" = ? ", array(size 1), object(ADODB_postgres8)) at /var/www/mahara/htdocs/lib/adodb/adodb.inc.php:310
    ADODB_TransMonitor("postgres8", "adodb_throw", -1, "ERROR: update or delete on table "host" violates ...", "DELETE FROM "host" WHERE "institution" = ? ", array(size 1), object(ADODB_postgres8)) at /var/www/mahara/htdocs/lib/adodb/adodb.inc.php:1281
    ADOConnection->_Execute("DELETE FROM "host" WHERE "institution" = ? ", array(size 1)) at /var/www/mahara/htdocs/lib/adodb/adodb.inc.php:1241
    ADOConnection->Execute("DELETE FROM "host" WHERE "institution" = ? ", array(size 1)) at /var/www/mahara/htdocs/lib/dml.php:981
    delete_records("host", "institution", "mytest") at /var/www/mahara/htdocs/admin/users/institutions.php:175
    delete_submit(object(Pieform), array(size 4)) at /var/www/mahara/htdocs/lib/pieforms/pieform.php:544
    Pieform->__construct(array(size 2)) at /var/www/clients/htdocs/lib/pieforms/pieform.php:166
    Pieform::process(array(size 2)) at /var/www/clients/htdocs/lib/mahara.php:5379
    pieform(array(size 2)) at /var/www/mahara/htdocs/admin/users/institutions.php:217

[WAR] 55 (lib/errors.php:858) Failed to get a recordset: postgres8 error: [-1: ERROR: update or delete on table "host" violates foreign key constraint "view_sub2_fk" on table "view"
DETAIL: Key (wwwroot)=(https://mytest.com.au) is still referenced from table "view".] in adodb_throw(DELETE FROM "host" WHERE "institution" = ? , Array)Command was: DELETE FROM "host" WHERE "institution" = ? and values was (0:mytest)

There are records in the view table that have submitted pages to this institution. It's setup with XML-RPC with a Moodle.

[Ghada El-Zoghbi (ghada-z)]

Steps to reproduce:

1. Create an institution with xml-rpc integration with a Moodle. Allow submissions from Mahara to Moodle.

2. Add users to the new institution in Mahara.

3. In Moodle, create a course with an activity - Mahara submission. Make sure the Mahara user is enrolled in this course.

4. As a user of the institution, create a page in Mahara and submit the page to Moodle.

5. As an admin of Mahara, remove all users from the institution. This will enable the 'delete' button for the institution.

6. Try to delete the institution. You should receive the error above.

[Mahara Bot (dev-mahara)]

Patch for "master" branch: https://reviews.mahara.org/10437

[Rangi Daymond (rangid)]

- Code/Gerrit: https://reviews.mahara.org/#/c/10437/ patch 4 of 4
- OS: Ubuntu 18.04
- DB: Postgres
- Browser: Firefox

Preconditions:

In Terminal and Postgres:
1. Connect to the database
postgres=# \c mahara-master
You are now connected to database "mahara-master" as user "postgres".

2. Ensure that the Moodle application is present in the Applications table
 mahara-master=# select * from application;
  name | displayname | xmlrpcserverurl | ssolandurl
 --------+-------------+-------------------------+-----------------------
 mahara | Mahara | /api/xmlrpc/server.php | /auth/xmlrpc/land.php
 moodle | Moodle | /mnet/xmlrpc/server.php | /auth/mnet/land.php
(2 rows)

3. Log in to Mahara as the SiteAdministrator
4. Add a new institution via Administration menu > Settings > Add institution button (used Institution name 'moodle")

5. Back in Terminal and Postgres, insert the (fake) moodle host details
mahara-master=# select * from host;
mahara-master=# insert into host values('http://moodle','Moodle', 'moodle','127.0.0.1','moodle','thisisfake','1568586997',0,0);
INSERT 0 1

6. Back in Mahara - create a new page view and note down it's id value (displayed at the end of the browser page URL e.g. address http://mahara.rdaymond.wgtn.cat-it.co.nz/view/blocks.php?id=7, is 7)

7. Update the new page's submittedhost field to the host URL value (see above)
mahara-master=# update view set submittedhost = 'http://moodle' where id = 7;
UPDATE 1

Test step:

SiteAdministrator navigates to Administration menu > Settings then selects the delete/tash can icon that is displayed to the right of the target institution's edit/cog icon.

Expected result: Once the SiteAdministrator confirms the institution delete, the row is removed from the Administer institutions tabe. ✔
When the user attempts to navigate to the created page view via its address (see example used above), a 'Page not found - You tried to access a page that does not exist.' ✔

Catalyst QA Approved ✔

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/10437
Committed: https://git.mahara.org/mahara/mahara/commit/7ba35ae895750ff3215e883cdbd18c473a7a2337
Submitter: Robert Lyon (<email address hidden>)
Branch: master

commit 7ba35ae895750ff3215e883cdbd18c473a7a2337
Author: Cecilia Vela Gurovic <email address hidden>
Date: Fri Oct 11 15:05:54 2019 +1300

Bug 1829943: Release submitted portfolios when deleting an institution

behatnotneeded

Change-Id: I70e3c27b91bb3b6ec5afd749f3a441e23ce5afc0

[Mahara Bot (dev-mahara)]

Patch for "19.10_STABLE" branch: https://reviews.mahara.org/10580

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/10580
Committed: https://git.mahara.org/mahara/mahara/commit/9ddca7c32e8e9d14ea9abb6388a1739adbb55822
Submitter: Robert Lyon (<email address hidden>)
Branch: 19.10_STABLE

commit 9ddca7c32e8e9d14ea9abb6388a1739adbb55822
Author: Cecilia Vela Gurovic <email address hidden>
Date: Fri Oct 11 15:05:54 2019 +1300

Bug 1829943: Release submitted portfolios when deleting an institution

behatnotneeded

Change-Id: I70e3c27b91bb3b6ec5afd749f3a441e23ce5afc0
(cherry picked from commit 7ba35ae895750ff3215e883cdbd18c473a7a2337)

[Mahara Bot (dev-mahara)]

Patch for "19.04_STABLE" branch: https://reviews.mahara.org/10581

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/10581
Committed: https://git.mahara.org/mahara/mahara/commit/46ded34bc7ba35725ba628c0b01dd7b5405bccdf
Submitter: Robert Lyon (<email address hidden>)
Branch: 19.04_STABLE

commit 46ded34bc7ba35725ba628c0b01dd7b5405bccdf
Author: Cecilia Vela Gurovic <email address hidden>
Date: Fri Oct 11 15:05:54 2019 +1300

Bug 1829943: Release submitted portfolios when deleting an institution

behatnotneeded

Change-Id: I70e3c27b91bb3b6ec5afd749f3a441e23ce5afc0
(cherry picked from commit 7ba35ae895750ff3215e883cdbd18c473a7a2337)