Add "ID" surrogate key column to "view_access" table

[Lacey Vickery]

It seems during the installation of Mahara several tables are created without primary keys. This caused a headache for us when restoring tables from a pg_dump script, duplicate records were created. Specifically the table 'blocktype_installed_category', caused duplicate block types in the UI (confusing some users). A further check revealed the following tables also missing primary keys:

artefact_log
view_access
view_visit
blocktype_installed_category

Version: 1.4.0
Database: Postgres
OS: Linux/RHEL

[Hugh Davenport (hugh-davenport)]

Technical points:

view_access currently doesn't limit anything at the code level, so the primary key would simply be the entire row. Any thoughts on what to do here?

Same with view_visit (though there are only two fields here)

Review for the other two at
https://reviews.mahara.org/856

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/856
Committed: http://gitorious.org/mahara/mahara/commit/a3f6fbfed906be03624dec54e43baed287810cc0
Submitter: Francois Marier (<email address hidden>)
Branch: master

commit a3f6fbfed906be03624dec54e43baed287810cc0
Author: Hugh Davenport <email address hidden>
Date: Tue Nov 15 15:10:17 2011 +1300

    Add primary keys to some tables missing them

    Bug #845948

    Change-Id: I447f82e2296d20197e138f3f619798fdf16417da
    Signed-off-by: Hugh Davenport <email address hidden>

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/902
Committed: http://gitorious.org/mahara/mahara/commit/68def4d6b84f0edb9d5044a430d79617e629524d
Submitter: Richard Mansfield (<email address hidden>)
Branch: master

commit 68def4d6b84f0edb9d5044a430d79617e629524d
Author: Richard Mansfield <email address hidden>
Date: Wed Nov 30 17:33:40 2011 +1300

    Remove upgrade to add primary key on artefact_log (bug #845948)

    The values of artefact,usr columns of the table are not necessarily
    unique. Perhaps a better option would be to create an id column.
    This primary key was added in a3f6fbfed906be03624dec54e43baed287810cc0.

    Change-Id: Ib151a14f0943d799d45a7697abc00f117883cd95
    Signed-off-by: Richard Mansfield <email address hidden>

[François Marier (fmarier)]

blocktype_installed_category is now done on master but these ones would require adding a new "id" column:

- artefact_log
- view_access
- view_visit

because they currently don't have to be unique.

[Kristina Hoeppner (kris-hoeppner)]

Some recent discussion about a problem that might be related at https://mahara.org/interaction/forum/topic.php?id=4761#post21014

[Robert Lyon (robertl-9)]

It is good practice to have an 'id' column in every table which should be unique and auto incrementing - we probably need to make sure all tables have this even if they are not explicitly accessed by mahara code

[Aaron Wells (u-aaronw)]

Yep, I believe the practice is called a "surrogate key" ( http://en.wikipedia.org/wiki/Surrogate_key ). There are arguments for and against them, and certainly previous Mahara devs didn't care for mandatory surrogate keys.

But I'm in favor of them. In my experience it's a lot more common to find yourself in a situation where you wish a table had an arbitrary unique ID, than to find yourself in a situation where it doesn't.

[Aaron Wells (u-aaronw)]

Current Mahara policy is to add a primary key ID column to every new table, but in most cases to not bother adding them to existing tables.

[Robert Lyon (robertl-9)]

I believe some of the slowness with the view_access table is due to the lack of a primary key as the db system has to work out indexing itself based on a number of rows.

I'll add a patch to add a primary 'id' column to the view_access table

[Mahara Bot (dev-mahara)]

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

[Robert Lyon (robertl-9)]

I'll not worry at this stage about the view_visit table as that is only two columns none of which can be null and the data there is automatically cleaned out after 1 week so there is no old data that could cause problems.

I'll also not worry about the artefact_log table also as the data saved there is not retrieved by the mahara system. It looks to just be a paper trail so devs/admins can see who altered / deleted artefacts within a group.

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/5869
Committed: https://git.mahara.org/mahara/mahara/commit/cd9247b48186eff962c3cd9b577790087c8ba24d
Submitter: Aaron Wells (<email address hidden>)
Branch: master

commit cd9247b48186eff962c3cd9b577790087c8ba24d
Author: Robert Lyon <email address hidden>
Date: Mon Dec 21 13:47:39 2015 +1300

Bug 845948: Add primary key to view_access table for better indexing

Part of the change is to remove duplicate view access rows as there is
no need to have duplicates of the exact same access for a view.

Adding the id to view_access will allow us to have different view
access for a view in multiple collections in the future

behatnotneeded - should be covered by existing tests

Change-Id: I8d3d6f0a011d0ed01a5ff2931e8e16d3000f4f16
Signed-off-by: Robert Lyon <email address hidden>
Signed-off-by: Aaron Wells <email address hidden>