mahara-contributors team mailing list archive

[Rebecca Blundell <1855798@xxxxxxxxxxxxxxxxxx>]

Orphan tables are stand-alone tables without any foreign key relations,
so they don't have any relationships with other tables. In theory, they
shouldn't exist in a relational database, so I suppose that there are
some foreign key constraints not set up that should be between these
tables and others.

I had a look at blocktype_taggedposts_tags. The block_instance instance
field in there looks to be the id from the block_instance table, however
that relationship is not referenced in the database:

mahara-master=> select * from blocktype_taggedposts_tags;
-[ RECORD 1 ]--+-----
id             | 1
block_instance | 38
tag            | cats
tagtype        | 1


mahara-master=> select * from block_instance
 where id=38;

-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------
id         | 38
blocktype  | taggedposts
title      | Tagged journal entries
configdata | a:5:{s:5:"count";s:2:"10";s:8:"copytype";s:6:"nocopy";s:4:"full";b:0;s:11:"retractable";b:0;s:15:"retractedonload";b:0;}
view       | 11
row        |
column     |
order      |

mahara-master=> \d block_instance
                                      Table "public.block_instance"
   Column   |          Type          | Collation | Nullable |                  Default
------------+------------------------+-----------+----------+--------------------------------------------
 id         | bigint                 |           | not null | nextval('block_instance_id_seq'::regclass)
 blocktype  | character varying(255) |           | not null |
 title      | character varying(255) |           | not null |
 configdata | text                   |           |          |
 view       | bigint                 |           | not null |
 row        | smallint               |           |          | 1
 column     | smallint               |           |          |
 order      | smallint               |           |          |
Indexes:
    "blocinst_id_pk" PRIMARY KEY, btree (id)
    "blocinst_blo_ix" btree (blocktype)
    "blocinst_vie_ix" btree (view)
Foreign-key constraints:
    "blocinst_blo_fk" FOREIGN KEY (blocktype) REFERENCES blocktype_installed(name)
    "blocinst_vie_fk" FOREIGN KEY (view) REFERENCES view(id)
Referenced by:
    TABLE "artefact_peer_assessment" CONSTRAINT "artepeerasse_blo_fk" FOREIGN KEY (block) REFERENCES block_instance(id)
    TABLE "block_instance_dimension" CONSTRAINT "blocinstdime_blo_fk" FOREIGN KEY (block) REFERENCES block_instance(id)
    TABLE "blocktype_wall_post" CONSTRAINT "blocwallpost_ins_fk" FOREIGN KEY (instance) REFERENCES block_instance(id)
    TABLE "framework_evidence" CONSTRAINT "framevid_ann_fk" FOREIGN KEY (annotation) REFERENCES block_instance(id)
    TABLE "view_artefact" CONSTRAINT "viewarte_blo_fk" FOREIGN KEY (block) REFERENCES block_instance(id)
    TABLE "watchlist_queue" CONSTRAINT "watcqueu_blo_fk" FOREIGN KEY (block) REFERENCES block_instance(id)

mahara-master=> \d blocktype_taggedposts_tags
                                        Table "public.blocktype_taggedposts_tags"
     Column     |          Type          | Collation | Nullable |                        Default
----------------+------------------------+-----------+----------+--------------------------------------------------------
 id             | bigint                 |           | not null | nextval('blocktype_taggedposts_tags_id_seq'::regclass)
 block_instance | bigint                 |           | not null |
 tag            | character varying(128) |           | not null |
 tagtype        | bigint                 |           | not null |
Indexes:
    "bloctaggtags_id_pk" PRIMARY KEY, btree (id)
    "bloctaggtags_tagtag_ix" btree (tag, tagtype)

-- 
You received this bug notification because you are a member of Mahara
Contributors, which is subscribed to Mahara.
Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it!
https://bugs.launchpad.net/bugs/1855798

Title:
  Database anomalies and orphan tables

Status in Mahara:
  New

Bug description:
  While investigating how to map out Mahara and see what connects to
  what a discovered a db schema-mapping tool. (Details in the bug
  report: https://bugs.launchpad.net/mahara/+bug/1855630)

  That highlighted some issues with our db schema:

  'Anomalies'
  We have 3 tables that contain a single column:
  artefact_comment_deletedby
  event_type
  view_type

  We have 1 table with incrementing column names, potentially indicating denormalization
  usr_friend

  We have a number of orphan tables listed in the comment below.

To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/1855798/+subscriptions