Difference between revisions of "Automatic Parental Control Settings - Mythvideo hack/feature request"
m (→Notes) |
m (→Warnings & Caveats: Link to the current backup/restore page) |
||
(4 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
− | A quick db hack that enforces Parental Control levels based on a set of rules. This is for advanced users willing to put their | + | 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= | =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. | + | 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== | ==Intent== | ||
− | Let the parental control levels be automatically set for files in specific locations or | + | 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== | ==Warnings & Caveats== | ||
− | + | {{Warning box|[[Database_Backup_and_Restore|Backup your database]] before trying any of this.}} | |
− | + | {{Note box|It is important to read this entire document before actually starting to alter your own system or use the rule matching.}} | |
− | I | + | 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. |
− | If you' | + | 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 box|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: | |
+ | * <code>BEFORE INSERT</code> trigger named <code>videoAutolevel_insert</code> | ||
+ | * <code>BEFORE UPDATE</code> trigger named <code>videoAutolevel_update</code> | ||
+ | * function named <code>getVideoAutolevel</code> | ||
+ | * table named <code>videoautolevel</code> | ||
− | ''' | + | Triggers are configured snippets of SQL that get executed when certain events occur. So by registering a '''<code>BEFORE INSERT</code>''' trigger, every time data is about to be put into the <code>videometadata</code> table, the registered SQL snippet gets a chance to alter the data before it is put into the table. Likewise the '''<code>BEFORE UPDATE</code>''' trigger allows the registered SQL snippet to similarly massage the incoming data about a movie. |
− | ' | + | The triggers both call the added <code>getVideoAutolevel</code> function with the video's filename. The function masks off the configured video root and then looks for an entry in the added <code>videoautolevel</code> 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 <code>videoautolevel</code> are as follows: | |
− | |||
{| class="wikitable" border="1" | {| class="wikitable" border="1" | ||
!Pattern Name | !Pattern Name | ||
Line 54: | Line 61: | ||
| style="text-align: center;" | 4 | | style="text-align: center;" | 4 | ||
|} | |} | ||
+ | |||
+ | {{Note box|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== | ==Matching Examples== | ||
+ | {{warning box|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.'' | ||
{| class="wikitable" border="1" | {| class="wikitable" border="1" | ||
− | ! | + | !File Path |
!Control Level Set | !Control Level Set | ||
|- | |- | ||
− | | | + | |rated/g/smurfs.avi |
− | | style="text-align: center;" |1 | + | |style="text-align: center;" |1 |
|- | |- | ||
− | | | + | |rated/Pg/BattleSmurfs.avi |
− | | style="text-align: center;" |2 | + | |style="text-align: center;" |2 |
|- | |- | ||
− | | | + | |rated/R/RowdySmurfs.avi |
| style="text-align: center;" |3 | | style="text-align: center;" |3 | ||
|- | |- | ||
− | | | + | |rated/NR/RandySmurfs.avi |
| style="text-align: center;" |4 | | style="text-align: center;" |4 | ||
|- | |- | ||
− | | | + | |cartoons/Return of the Smurfs.avi |
| style="text-align: center;" |untouched | | style="text-align: center;" |untouched | ||
|} | |} | ||
==Behavior== | ==Behavior== | ||
− | * Matches are '''NOT''' case sensitive | + | * 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 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. | * 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'. | |
− | * Simple database wildcard matching is being | + | * 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== | ==Installation== | ||
Line 91: | Line 107: | ||
==Tips & Tricks== | ==Tips & Tricks== | ||
− | To add a new rule where | + | 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); | INSERT INTO videoautolevel(name, path, showlevel) VALUES('cool vids', 'coolVideos/', 2); | ||
Line 104: | Line 123: | ||
Bye | Bye | ||
− | To delete | + | ===Delete a rule=== |
+ | To delete the 'cool vids' rule added above: | ||
DELETE FROM videoautolevel WHERE name='cool vids'; | DELETE FROM videoautolevel WHERE name='cool vids'; | ||
+ | ===List all rules=== | ||
To list all rules, here is the SQL statement: | To list all rules, here is the SQL statement: | ||
SELECT * FROM videoautolevel; | 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): | 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 | UPDATE videometadata | ||
SET showlevel = getVideoAutolevel(filename) | SET showlevel = getVideoAutolevel(filename) | ||
WHERE getVideoAutolevel(filename) > 0 ; | 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 :) ) | (Yes, I'm calling the function twice, if someone knows how to do this more efficiently AND more succinctly, I'm all ears :) ) | ||
==Notes== | ==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. | * 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. | ||
[[Category:HOWTO]] | [[Category:HOWTO]] | ||
− |
Latest revision as of 04:03, 11 November 2009
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.
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 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 namedvideoAutolevel_insert
-
BEFORE UPDATE
trigger namedvideoAutolevel_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
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.