Modify

Opened 23 months ago

Last modified 19 hours ago

#12592 new Developer Task

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

Reported by: dekarl Owned by: dekarl
Priority: minor Milestone: 29.1
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.

Attachments (0)

Change History (5)

comment:1 Changed 20 months ago by stuarta

  • Milestone changed from 0.29 to 29.0

Milestone renamed

comment:2 Changed 20 months 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 18 months ago by dekarl

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

comment:4 Changed 12 months ago by dekarl

  • 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 19 hours ago by stuarta

  • Milestone changed from 29.0 to 29.1

Add Comment

Modify Ticket

Action
as new The owner will remain dekarl.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.