[mythtv] mythvideo - category, country and genre

[MaxPower]

any comments ?
then I send the first patch which modify the database shema :
- add videocategory table
- add videocountry table
- add videometadatacountry table
- add videogenre table
- add videometadatagenre table
- add category field in videometadata
- modify plot field in videometadata to store much larger plot (I need 
this for the french version)

I don't use foreign key because I believe that mysql prerequired version 
for mythtv don't support them


Please apply this patch
Xavier

MaxPower wrote:

> Any comment about this ?
> I would like create 4 patchs:
> 1 - to upgrade the database
> 2 - to upgrade metadata (.h  && .cpp)
> 3 - modify the IMDB graber to allow it to grab country/genre 
> information. And grab french information for french user
> 4 - add the dialog filter
> In this order, it should not break the code !
>
> Xavier
>
> MaxPower wrote:
>
>> I prepare a patch to integrate category, country, genre and complex 
>> filter.
>> I have made this before to switch to 0.13 then I must recode for this 
>> release
>> I explain :
>> I would like create category as Movies, TV Show, Music, Hollidays, 
>> ... (fully configurable by user)
>> A video can be only one category.
>> For this I  have  apply the Johan Bellander's idea :Add a column in 
>> videometadata called category (INT).
>> I add a table called videocategories like this
>> intid INT AUTO_INCREMENT PRIMARY KEY
>> category VARCHAR(128)
>> An other way can be to just create a category field varchar(128)  in 
>> videometadata with an index
>>
>> For country and genre, I grab information from IMDB
>> a video can haw few genre and few country
>> I have create a table videocountries (videogenres) like this
>> intid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
>> country (genre) varchar (128)
>> And an other videometadatacountry (videometadatagenre) with theses 
>> fields
>> idvideo which is the primary key of  videomatadata
>> idcountry (idgenre) which the primary key of videocountries 
>> (videogenres)
>>
>> I have create a dialog which allow the user to filter the video list 
>> with theses fields
>> showlevel  (0,1,2,3,4,5)
>> category (All, category 1, category 2...Not Defined)
>> genre (All, Drama, Action Adventure ...Not Defined)
>> country (All, USA, UK, France, Germany ... Not defined)
>> year (ALL,  2003,2002, .....Not Defined)
>> runtime (ALL, 0 mn < runtime <= 30, 30 < runtime <= 60, 60 < runtime 
>> <=90 ... Not Defined)
>> userrating (All, >9, >8, >7, ... ,Not defined)
>> browse (All, Yes, No)
>>
>> I have added an item in the mythvideo menu that open this dialog. but 
>> this dialog can open too in videobrowser and videomanager.
>>
>> If you don't want to use the feature, you can, it's change nothing 
>> for the user which don't want to use this.
>>
>> Before sending this new feature in few littles patchs, I would like 
>> to know if you have any objection or suggestion
>>
>> For mysql change, tell me how to send patch, I'm not familiar with 
>> the new feature dbcheck
>>
>> Xavier
>>
>> Thanks to read me
>>
>> PS :I you don't understand something, please exuse my poor english 
>> and tell me what is not clear !
>>
>>
>>
>>
>>
>>
>>
>> _______________________________________________
>> mythtv-dev mailing list
>> mythtv-dev at mythtv.org
>> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-dev
>>
>
>
>
> _______________________________________________
> mythtv-dev mailing list
> mythtv-dev at mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-dev
>

-------------- next part --------------
USE mythconverg;
ALTER TABLE videometadata CHANGE plot plot TEXT;
ALTER TABLE ADD COLUMN category INT UNSIGNED;

CREATE TABLE IF NOT EXISTS videocategory
(
	intid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	category VARCHAR(128) NOT NULL
);

CREATE TABLE IF NOT EXISTS videocountry
{
	intid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	country VARCHAR(128) NOT NULL
}; 

CREATE TABLE IF NOT EXISTS videometadatacountry
(
	idvideo INT UNSIGNED NOT NULL,
	idcountry INT UNSIGNED NOT NULL
);

CREATE TABLE IF NOT EXISTS videogenre
(
	intid INT UNSIGNED AUTO_INCREMENT NOT NULL  PRIMARY KEY,
	genre VARCHAR(128) NOT NULL
);
CREATE TABLE IF NOT EXISTS videometadatagenre
(
	idvideo INT UNSIGNED NOT NULL,
	idgenre INT UNSIGNED NOT NULL
);