DB schema alignment issue: mismatches between install and upgrade scripts

[kristian-94]

After installing an older version of the plugin and upgrading, the DB schema will be updated according to the upgrade.php scripts. These scripts leave the DB in a different alignment than a fresh install would.
The output I get from php admin/cli/check_database_schema.php is:

$ php admin/cli/check_database_schema.php
-------------------------------------------------------------------------------
plagiarism_turnitin_files
 * column 'itemid' has default 'NULL', expected '0' (I)
 * column 'student_read' has default 'NULL', expected '0' (I)
-------------------------------------------------------------------------------
plagiarism_turnitin_config
 * column 'config_hash' should be NOT NULL (C)
 * column 'config_hash' length is 100, expected 255 (C)
-------------------------------------------------------------------------------
plagiarism_turnitin_users
 * column 'userid' should be NOT NULL (I)
 * column 'turnitin_uid' should be NOT NULL (I)
 * column 'turnitin_utp' should be NOT NULL (I)
-------------------------------------------------------------------------------
plagiarism_turnitin_courses
 * column 'courseid' should be NOT NULL (I)
 * column 'ownerid' should be NOT NULL (I)
 * column 'turnitin_ctl' should be NOT NULL (X)
 * column 'turnitin_cid' should be NOT NULL (I)
-------------------------------------------------------------------------------

It's clear that these issues are code bugs, below I've gone through each section and linked the relevant part of the upgrade scripts that are incorrect. It could also be fixed in the install.xml file instead. We'll need to create a new upgrade.php section that goes through and retroactively fixes the schema.

---

plagiarism_turnitin_files

• column 'itemid' has default 'NULL', expected '0' (I)

We can see the mismatch between the install and upgrade scripts:
https://github.com/turnitin/moodle-plagiarism_turnitin/blob/master/db/install.xml#L15
https://github.com/turnitin/moodle-plagiarism_turnitin/blob/master/db/upgrade.php#L238

• column 'student_read' has default 'NULL', expected '0' (I)

We can see the mismatch between the install and upgrade scripts:
https://github.com/turnitin/moodle-plagiarism_turnitin/blob/master/db/install.xml#L26
https://github.com/turnitin/moodle-plagiarism_turnitin/blob/master/db/upgrade.php#L259

---

plagiarism_turnitin_config

• column 'config_hash' should be NOT NULL (C)
• column 'config_hash' length is 100, expected 255 (C)

Both of these issues are caused by these two lines:
https://github.com/turnitin/moodle-plagiarism_turnitin/blob/master/db/install.xml#L45
https://github.com/turnitin/moodle-plagiarism_turnitin/blob/master/db/upgrade.php#L279

---

plagiarism_turnitin_users

• column 'userid' should be NOT NULL (I)
• column 'turnitin_uid' should be NOT NULL (I)
• column 'turnitin_utp' should be NOT NULL (I)

look at the 3 following lines in the install and upgrade scripts:
https://github.com/turnitin/moodle-plagiarism_turnitin/blob/master/db/install.xml#L55
https://github.com/turnitin/moodle-plagiarism_turnitin/blob/master/db/upgrade.php#L378

---

plagiarism_turnitin_courses

• column 'courseid' should be NOT NULL (I)
• column 'ownerid' should be NOT NULL (I)
• column 'turnitin_ctl' should be NOT NULL (X)
• column 'turnitin_cid' should be NOT NULL (I)

---

Caused by these sections of code:
https://github.com/turnitin/moodle-plagiarism_turnitin/blob/master/db/install.xml#L71
https://github.com/turnitin/moodle-plagiarism_turnitin/blob/master/db/upgrade.php#L321

[TomoTsuyuki]

I got exactly same as this error.
I'm using commit 1859714 (22 Jul 2020), so I updated to the latest one.
Some were fixed by #542 and may be fixed by #585 , but still have issue for plagiarism_turnitin_config table.

[TomoTsuyuki]

I made a PR for fixing the plagiarism_turnitin_config issue.
For fixing all of the original errors in this issue, the code should be included #542 (already integrated), #585 , and #611 .