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 their fate into their own hands.
Contents
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.