Opened 3 years ago

Last modified 11 months ago

#12592 new Developer Task

maintanence work on database schema - move to InnoDB and use referential integrity

Reported by: Karl Egly Owned by: Karl Egly
Priority: minor Milestone: 29.2
Component: MythTV - General Version: Unspecified
Severity: medium Keywords: schemachange
Cc: Ticket locked: no

Description

Random notes around the seektable, this is something I wanted for 0.28 but I'm not getting it ready and tested by tomorrow, so it has to wait until after the branch point of fixes/0.28.

  • spread recordedid around and have it replace chanid+starttime in the tables (includes some work on access functions / Service API, so they can still work with chanid+starttime)
  • work out the defaults that we have to override to get similar results across various versions of MySQL (https://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html file-per-table vs. all tables in one file changed in 5.6.6)
  • switch the small tables to InnoDB
  • enable referential integrity and fix all consistency bugs lurking around
  • switch the big tables (seek table) to InnoDB, choosing the primary key to optimize the data locality
  • reorder columns in tables / indexes (for data locality)
  • decide if the should mix the storage location of tables (e.g. all small ones into the system tablespace and the big ones into their own files)

If someone has a (partial) implementation in their private repo, feel free to just grab this tracking ticket.

Change History (7)

comment:1 Changed 3 years ago by Stuart Auchterlonie

Milestone: 0.2929.0

Milestone renamed

comment:2 Changed 3 years ago by Gary Buhrmaster <gary.buhrmaster@…>

In 3865f57c1aeb7fd7615a6143357aa41fe36f43b9/mythtv:

Update tables related to the program table

In the case that a program is deleted or changed, insure
that the related programrating and programgenres tables
are also modified appropriately (as is the credits table)

Fixes #12729
Refs #12592

(cherry picked from commit 74a1b214a838a7e2429da6eb89758eef20d1f963)
Signed-off-by: Karl Dietz <dekarl@…>

comment:3 Changed 3 years ago by Karl Egly

Here's the plan as it was 5 years ago TaskRecordedFile

comment:4 Changed 2 years ago by Karl Egly

  • merge videocast into people (which could use some housekeeping, people and credits have ~160k entries each on my system) and videometadatacast into credits.
  • convert filemarkup from filename as PK to reference videometadata by intid (file system browse mode must die, or at least get by without bookmarks)

comment:5 Changed 13 months ago by Stuart Auchterlonie

Milestone: 29.029.1

comment:6 Changed 11 months ago by Stuart Auchterlonie

Milestone: 29.10.28.2

Moving remaining open tickets to 0.28.2 milestone

comment:7 Changed 11 months ago by Stuart Auchterlonie

Milestone: 0.28.229.2

Moving remaining open tickets to 29.2 milestone

Note: See TracTickets for help on using tickets.