MythVideo Auto-level Script

From MythTV Official Wiki
Jump to: navigation, search

This is the install instruction page for the autolevel hack.

See Category:MySQL if you are unsure about mysql or database operations in general.

Autolevel install instructions

Prerequisite: Mythvideo configuration

Warning.png Warning: Without following these steps, Mythvideo's parental control value for newly added video files and the new value set by the trigger can get out of step in such a way that until Mythvideo is restarted or the Video Manager runs, the newly added videos will appear to have a parental control level of 1 regardless of what the database says (A quick consult with the database or browsing with the Video Manager will confirm what value is actually stored in the database.)

To provide a snappier experience, Mythvideo tries to minimize the amount of file and database reading that is it has to do. Unfortunately, this fights against letting the trigger hack affect newly scanned files. The logic behind such minimization is perfectly sound in the absence of things like database triggers. However, this hack introduces two database triggers and to fully work properly it requires Mythvideo to consult with the MySQL version of the metadata about a movie even for entries that Mythvideo created only a few operations earlier. Fortunately, the writers of Mythvideo provided enough configuration options so that this hack can operate correctly.

Go to: Utilities / Setup -> Setup -> Media Settings -> Video Settings -> General Settings

On screen 2 of General Settings make sure the following options are turned off (not checked):

  • Video Browser browses files
  • Video Gallery browses files
  • Video List browses files
  • Newly scanned files are browsable by default

On the same screen, now make sure the following option is turned on (checked):

  • Video List loads video metadata

With the above Mythvideo settings in place, the video metadata will come exclusively from the database itself. The down side is that the only way new videos can show up in the library is by running the Video Manager.

Important.png Note: Setting up a cron job to automatically update the video database would be great, but I've not had time to dig into it yet

Install autolevel.sql

  1. Copy the code block labeled 'autolevel.sql' below into a file editor and save it as 'autolevel.sql'
  2. On the machine that runs the MySQL database, do the following (provide your own mysql db username/password if you know it to be something different):
    mysql mythconverg -u root < /storage/myth/autolevel.sql 
  3. Profit!

No really, that's all there is to it. Add movies and enjoy the bliss of not setting the level video-by-video.

SQL script

Should it be needed, this script can be run safely multiple times without issue, but any custom level matching rules you might have had will be dropped. I keep my custom rules in a separate sql file and install them right after these are installed.


Important.png Note: In case you have more than one frontend you need to change the SQL in the getVideoAutolevel function to:

SELECT data FROM settings WHERE value='VideoStartupDir' AND hostname='YOUR_MAIN_HOSTNAME' INTO vid_dir;

where YOUR_MAIN_HOSTNAME is the name of the main frontend (probably the mythbackend holding the videos). Look at the discussion for more infos.


Script.png autolevel.sql
# Creates tables and triggers to automatically set the parental controls
# according to rules defined in the added videoautolevel table

use mythconverg;

DROP TABLE IF EXISTS `videoautolevel`;

CREATE TABLE `videoautolevel` (
  `name` varchar(32) NOT NULL,
  `path` text NOT NULL,
  `showlevel` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT into videoautolevel(name, path, showlevel)
  VALUES('General', 'rated/g/', 1),
        ('Supervised', 'rated/pg/', 2),
        ('Restricted', 'rated/r/', 3),
        ('Not Rated', 'rated/nr/', 4);

delimiter |

DROP FUNCTION IF EXISTS getVideoAutolevel |

CREATE FUNCTION getVideoAutolevel(target_file TEXT) RETURNS INT (10)
NOT DETERMINISTIC READS SQL DATA
BEGIN
  DECLARE found_level INT (10) DEFAULT 0;
  DECLARE sg_used INT (10) DEFAULT 0;
  DECLARE target_pattern TEXT;
  DECLARE vid_dir TEXT;

  SELECT count(*) into sg_used from storagegroup where groupname="Videos";
  IF sg_used > 0 THEN
    SET target_pattern=target_file;
  ELSE
    SELECT data FROM settings WHERE value='VideoStartupDir' INTO vid_dir;
    SELECT SUBSTRING(target_file, LENGTH(vid_dir) + 1) INTO target_pattern;
    SET target_pattern = TRIM(LEADING '/' FROM target_pattern);
  END IF;


  SELECT max(showlevel)
    INTO found_level
    FROM videoautolevel
    WHERE target_pattern LIKE CONCAT(videoautolevel.path, '%');
  RETURN found_level;
END
|

DROP TRIGGER IF EXISTS videoAutolevel_insert |

CREATE TRIGGER videoAutolevel_insert BEFORE INSERT ON videometadata 
FOR EACH ROW
BEGIN
  DECLARE foundLevel INT(10) DEFAULT 0;
  SET foundLevel = getVideoAutolevel(NEW.filename);
  IF foundLevel > 0 THEN
    SET NEW.showLevel = foundLevel;
  END IF;
END
|

DROP TRIGGER IF EXISTS videoAutolevel_update |

CREATE TRIGGER videoAutolevel_update BEFORE UPDATE ON videometadata 
FOR EACH ROW
BEGIN
  DECLARE foundLevel INT(10) DEFAULT 0;
  SET foundLevel = getVideoAutolevel(NEW.filename);
  IF foundLevel > 0 THEN
    SET NEW.showLevel = foundLevel;
  END IF;
END
|

delimiter ;