Automatic Parental Control Settings - Mythvideo hack/feature request

A quick db hack that enforces Parental Control levels based on a set of rules. This is for advanced users willing to put the fate of their video metadata into their own hands.

Hack: Rule based parental control

While the parental level controls in MythVideo are rudimentary, they are at least enough and get the job done and I'm quite grateful that they are in place. However, when trying to set the levels of large collections of files, the video-by-video nature of the feature is just too cumbersome. The following is an initial hack of the MySQL tables to automatically set the parental control levels based upon filename matching rules.

Intent

Let the parental control levels be automatically set for files in specific locations or for files with specific naming conventions.

Requirements

You should already know how and be able to log into your database from the mysql text console with permissions to administer your MythTv tables. See Category:MySQL for details and answers about the database itself.

Warnings & Caveats

Warning: Backup your database before trying any of this.

Note: It is important to read this entire document before actually starting to alter your own system or use the rule matching.

This is the section where I make scary statements and relieve myself of responsibility for anything that might go wrong and of the responsibility to teach anyone how to use MySQL and database tools.

I categorize this as being for advanced users only because I am advising direct database alterations. If you are confused about what I'm talking about in describing what to do or how the hack works don't just keep plugging along. If you are lost with what is described, please read up on basic database and trigger concepts and how to use the mysql text console.

</scary-section> It's really not that scary if you're at all familiar with the MySQL tools and concepts. Plus, we're dealing with the video metadata which isn't crucial for basic operation. Mythvideo is willing to repopulate the information if it is totally lost, but that won't happen since you backed everything up first :)

Note: I know that I tinkered with my permissions previous to this. While I'm not aware of any permission setting that must be done, it's something that might pop up, so be forewarned.

Implementation

The automatic control setting is accomplished with a small number of additions to the mythconverg database:

• BEFORE INSERT trigger named videoAutolevel_insert
• BEFORE UPDATE trigger named videoAutolevel_update
• function named getVideoAutolevel
• table named videoautolevel

Triggers are configured snippets of SQL that get executed when certain events occur. So by registering a BEFORE INSERT trigger, every time data is about to be put into the videometadata table, the registered SQL snippet gets a chance to alter the data before it is put into the table. Likewise the BEFORE UPDATE trigger allows the registered SQL snippet to similarly massage the incoming data about a movie.

The triggers both call the added getVideoAutolevel function with the video's filename. The function masks off the configured video root and then looks for an entry in the added videoautolevel table. If a match occurs, the associated parental control level is returned. For multiple matches, the highest level is returned. If no match occurs, zero is returned. Return values of zero indicate that the parental control level should not be changed.

Mythvideo stores the full filename for each video in the metadata, but since they are all located in the same root directory, there's no need to also repeat the root portion for the rules.

The initial named patterns set in videoautolevel are as follows:

Pattern Name	Directory Pattern	Control Level
General	rated/g/	1
Supervised	rated/pg/	2
Restricted	rated/r/	3
Not Rated	rated/nr/	4

Note: The use of triggers is convenient but they come with their own set of constraints that sometimes make them ill-suited as a real solution which is why I categorize this as a hack.

Matching Examples

Warning: Matches are NOT case sensitive!

Some example video file names and how each would be rated with the default rating rules. For clarity, the video root value for the sample file paths has not been included in the examples below.

File Path	Control Level Set
rated/g/smurfs.avi	1
rated/Pg/BattleSmurfs.avi	2
rated/R/RowdySmurfs.avi	3
rated/NR/RandySmurfs.avi	4
cartoons/Return of the Smurfs.avi	untouched

Behavior

• Matches are NOT case sensitive.
• If a video's location does not match anything in the table, the showlevel (the parental level) isn't touched.
• If multiple patterns apply to a video's filename, the highest parental control level is used.
• New patterns can be added to the table as one sees fit, but I've not provided a way to do it other than through direct database manipulation. In fact I'm hoping someone runs with this and creates a more user-friendly mode of operation that ties into the larger platform naturally.
• Simple database wildcard matching is being performed for the matches. In SQL, the wildcard is '%' not '*'. Knowing this, one could create a rule like '%/rated/r/' to automatically set control levels for nested directory structures such as 'movies/rated/r/restricted.mpg'.
• Rules can be written to ignore directory structure. In this case, the match is against all of the text of the filename itself, but matching this generally can get tricky and isn't as intuitive. The rule '%simpsons' would match all filenames that contained the text 'simpsons' in it such as:
  • 'cartoons/thesimpsonsshow/episode1.avi'
  • 'tv/simpsons-S45E54.avi'
  • 'movies/circusimpsonparade.avi'
• Once installed, only videos that are altered or added will have their control level automatically set. The installation process does not examine preexisting entries. (If you want to do this, see the 'Tips & Tricks' section below)

Installation

See this page. Feature_Wishlist_(Plugin_Addons)_mythvideo_autolevel_script

Tips & Tricks

Various SQL statements to manage matching rules.

Add a rule

To add a new rule where all files located in the 'coolVideos' directory to be set to level 2. Here's the SQL to do it:

 INSERT INTO videoautolevel(name, path, showlevel) VALUES('cool vids', 'coolVideos/', 2);

This can be saved into a file (coolRule.sql for instance) and executed like this (assuming 'root' can log in and doesn't require a password):

 mysql mythconverg -u root < coolRule.sql

or just entered at the mysql command line:

 ~$ mysql -u root mythconverg -A
 ...
 mysql> INSERT INTO videoautolevel(name, path, showlevel) VALUES('cool vids', 'coolvideos/', 2);
 Query OK, 1 row affected (0.02 sec)
 mysql> exit
 Bye

Delete a rule

To delete the 'cool vids' rule added above:

 DELETE FROM videoautolevel WHERE name='cool vids';

List all rules

To list all rules, here is the SQL statement:

 SELECT * FROM videoautolevel;

Adjust all existing video entries

To retroactively set levels according to rule definitions (Note that this will only alter entries that match against the current rules, you can't for instance remove a rule, run this and get back to where you were before the rule existed):

 UPDATE videometadata 
   SET showlevel = getVideoAutolevel(filename)
   WHERE getVideoAutolevel(filename) > 0 ;

(Yes, I'm calling the function twice, if someone knows how to do this more efficiently AND more succinctly, I'm all ears :) )

Notes

• This could easily be extended to do things like look at the 'rating' of the movie (such as what is entered via IMDB entries) or other criteria to set the showlevel. A non-hacked approach would be to make the showlevel value be explicitly a computed value with user-settable overrides.