[kusskowe]

i am looking at adding mpaa movie ratings (pg, r, etc.) to movie database but it appears i'd have to add another column to the movie info db table.  any thoughts on what the approach for this should be?

-  make db incompatible so you have to nuke the current one before updating to newer software?

-  make code detect/update the table on load if it's an older version?

-  highjack another field such as imdb rating  (stored as text) to store both imdb and mpaa ratings?

i would also like to be able to sort/view on rating same as date, genre ...

update: pretty quiet out there so for now i decided to extend the schema and rev the video database version since that seems to be what was done in the past. this means all imdb info is lost on software upgrade. also thought about it and decided a new view on pg, r, etc. wasn't all that useful since there are only like 4-5 buckets. anyway will upload a patch at some point and let dev's decide.

[wabidwoveren]

honestly who cares, and besides i dont wanna rescan my videos.

[kusskowe]

honestly who cares, and besides i dont wanna rescan my videos.

obviously i care which is why i added the code.

i agree it sucks to have to rescan your videos which is the point of my original post. it doesn't seem like there is any built in plan or framework to get around this. i'll post the patch and if you are lucky it will be ignored.

[jmarshall]

thanks for your work on this - it is appreciated.

i have been thinking that there should be a way to add stuff to the db without having to completely delete the old stuff.

there should be a way to check if the returned datasets contain a given field or not. i'm not a guru at sql so can't say for sure, but i'd assume that something like this exists.

assuming it does, all you'd need to do is:

1. alter the insert code to get the new info into the database.
2. alter the retreival code to check whether the new piece of info is in the database before attempting to retrieve it.

that way, all new imdb scans will go nicely, whereas all the old info will still be available. any new fields that have been added will obviously have to have sensible default values that the old data can take.

let me know if you find anything out about this.

cheers,
jonathan

[kusskowe]

hi jonathan, thanks for the reply.

i'm not really a sql/db guy either so maybe there is some magic that can be done here but i have been thinking about this more and it seems like reving the db version was the right thing to do. afterall the schema did change when we added fields right?

seems like what is really missing is some upgrade code to get you from the current db version to the next version. this could still be added on quite easily where:

1. you know that you are on db version 4 if you have a myvideos4.db you just open and use it. if you don't then you go to step 2.

2. check if you have a myvideos3.db file and either copy it over to myvideos4.db file and then in place upgrade the tables/schema or copy all of the data from one to the other. if you don't have a myvideos3.db file you just create a blank myvideos4.db.

3. if you had a myvideos3.db delete it to cleanup (or leave it in place in case of a downgrade?)

this would provide a nice bolt-on upgrade process without complicating the existing code/logic needlessly. i think i was trying to make it too fancy before and then just decided to punt.

on the other hand you would have to decide moving forward how many upgrade paths are going to be supported. i.e. can you go from 3->5 or 3->6 or is the upgrade only supported from past rev? probably once you part from the mindset that everything is lost on upgrade you have this issue to consider either way. at least with a bolt-on upgrade process this code doesn't muddy the main db logic.

what do you think? should i give this a shot and see how it goes?

k

[jmarshall]

yeah, that was my idea exactly. seems like the most sensible thing to do.

as for future upgrade paths, this should be easily handled by a similar method. (upgrade from 3->4 is in the code, so just add the upgrade path from 4->5, and do load, upgrade, load, upgrade, load)
sure, it's not pretty, but it'll work.

the "refresh" button can then be used by users on a per-file basis if they wish to get the information for the added fields.

what do you think?

cheers,
jonathan

[kusskowe]

yep, i agree. i'll throw some code together and see how it comes out. what do you think about deleting the old db? keep it around just in case a user runs multiple versions or delete it to cleanup old stuff? i'm thinking leave it since it seems like alot of people run a stable version and try out cvs builds from time to time just to see how it's going.

k

[jsd]

the typical sql way to do this is not to have a table with a bunch of columns like title, year, rating, etc.

instead, you would create a table with a list of attribute ids (1 = title, 2 = year, 3 = rating). then your data set is id#, attribute_id# and attribute value. so a movie (say it's id 123) with a title and a rating would have two rows:

123 1 kill bill
123 3 r

this way you can have an arbitrary number of pieces of information for each movie. if you want to start supporting a new thing like rating, you just start adding rows with attribute id 3. nothing breaks. you can no longer do select * from movie where id = 123 to find out everything about a movie though. you have to get more involved, although it's simple enough to create a function in code to construct the object representing a movie. also if your db supports views you can make a view so that select * from movie_view works.

sqlishly yours,
-jsd-

[HarshReality]

i know currently the db (video) is parsing the mpaa rating and writing it to the xml. trick is i am looking in the source and cant locate the calls for the thing so i can tweak the skin to display it.....

suggestions?

hell just point me in the right direction, a line of reference or anything.

[Gamester17]

has this patch (link) already been added to cvs?

[kusskowe]

i have the original patch updated to latest cvs but have not yet posted it.

i am curious if somebody can tell me why imdb files are now stored to disk and sometimes loaded within videodatabase.cpp. should the mpaa text not be stored within the sql database and just loaded from the text files when needed?

either way i'll probably leave the db column but limit it to basic rating since eventually i would like to add a view for this (like year/genre)

couple of other quick things:

1. i've noticed that some of the imdb icons are wider now (i think they are meant to show multi-disc sets like alexander). should i tweak the skin for this or otherwise scale thumbnails?

2. in order to make room for mpaa text i moved the year up to the title similiar to the way imdb does it. so alexander (2004) vs. burning a line for just the year. does this offend anybody?

sorry about the super long delay on this ...

[jmarshall]

hi there,

i moved the imdb data into .xml files on disk, as i didn't see the point of having it all in the database, when being in .xml files makes it way easier to add extra stuff in future (like the mpaa stuff) - saves messing with the database, and doing updates to the database format etc.

however you do it on screen is fine - just make it use a separate label control so that skinners can place it where they like.

thanks for the patch

cheers,
jonathan

[kusskowe]

thanks for the reply jonathan ... i guess i'll take the cowards way out then and not update the db for now which makes this a pretty small patch. :thumbsup:

i added two new controls one for mpaa and one for combined title/date then switched pmiii skin to use new controls. i assume the old controls just don't do anything when i set them if they are no longer in the skin but this will still work for other skins ... right?

for thumbs i tweaked the skin a bit to make it look ok for all of the movie covers i have. somebody explicitly set the save aspect ratio option so i guess at some point it was decided that different sized thumbs were better than stretched out or squished ones. i also pushed things out to borders more to give a little bit more text space.

one more cvs update, full build/test and i'll upload the patch.

btw - thanks for all the work on the html scrapper made this update a breeze. most people won't even have to rescan to get the mpaa info since it's been sitting there for ages.

[jmarshall]

sounds good. you are correct about the controls - if it isn't in the skin, it won't be shown, so other skins should be fine.

yeah - we decided that keeping the aspect ratio of thumbs was more important than the actual size that it ends up (most from imdb are reasonably the same - similarly for amazon.com)

i was just too lazy to add the controls/code for the mpaa stuff on the xbmc side (it was trivial to get it in the dll) - glad you had the time to do it

cheers,
jonathan

[kraqh3d]

one little annoyance, however, is that the xml files and the db hold some duplicate information. this needs to be changed so that a particular item of information is only held in one location -- either the db or the xml file.