Automatic Parental Control Settings - Mythvideo hack/feature request

From MythTV Official Wiki
Revision as of 20:08, 25 March 2008 by JMinton (talk | contribs) (Notes)

Jump to: navigation, search

A quick db hack that enforces Parental Control levels based on a set of rules. This is for advanced users willing to put their fate 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 matching rules.

Intent

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

Warnings & Caveats

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 tools or anything else.

First and foremost, this is for advanced users only. 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. Either educate yourself on the topics or resign yourself to wait for someone to make this more user-friendly.

I haven't tested this against anything other than my own vanilla MythTV on Kubuntu install utilizing the MySQL db that came along for the ride... so 'works for me' is the policy of record.

If you're trying to do this, you should at least not be scared of poking around the database yourself and you should know how to start the mysql command line or some equivalent tool or better.

(kmysqladmin was easy and useful if you're looking for something slightly better than the command line for getting the 'big picture' of what is going on but I was a bit surprised at the dearth of freeware db tools on Linux for MySQL.)

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

Backup everything before trying any of this!

Proceed at your own risk!

Read this entire document before trying anything as it contains official warnings!

</scary-section> It's really not that scary once you're familiar with the MySQL tools and concepts plus since this deals with metadata (information about the videos), even if you blow away the table contents, it will be repopulated.

Implementation

This adds BEFORE INSERT and BEFORE UPDATE triggers to the videometadata table. The triggers ultimately consult with a new table named videoautolevel to automatically set the inserted or updated video's parental control level based on filename pattern matching. The initial named patterns 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

Matching Examples

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

Behavior

  • Matches are NOT case sensitive! That was your official warning.
  • If a video's location does not match anything in the table, the showlevel (the parental level) isn't touched.
  • 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.
  • If more than one pattern matches, the highest parental control value is used.
  • Simple database wildcard matching is being done where given a pattern, I prepend the video root location and postpend a wildcard and see if it matches the file name using the 'LIKE' operation. Knowing this, one could create rules like '%/rated/r/' to automatically set it for more nested directory structures. Alternatively, one could even look for text within the filename itself: '%Simpsons' but matching this generally and widely might get tricky and isn't as intuitive.
  • This will only affect videos that are altered or added, it does not process already existing 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

To add a new rule where we want 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

To delete a rule (the one we just added above in this example), here is the SQL statement:

 DELETE FROM videoautolevel WHERE name='cool vids';

To list all rules, here is the SQL statement:

 SELECT * FROM videoautolevel;

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

  • Rule paths are expressed relative to the configured video directory allowing users to change the video root without affecting the rules. It also allows one to express rules without having to know what the directory is.
  • I'm not sure if the BEFORE UPDATE is truly needed but it is included for safety and completeness.
  • 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.