HowTo Convert Mp MyVideos Database to Ant format for MyFilms

[HappyTalk]

If you wanna convert the database used for MediaPortal's MyVideos into an Ant.xml file so you can use it in MyFilms here's an sql script I wrote to do it. If Source is released for MyFilms I'll add an import utility to convert direct to ant.xml without needing to run Ant Import . Thought I'd post it as it may be useful to anyone with a large MyVideos DB who wants to try something else out, or use Ant. You'll need to install Ant Movie Catalog (freeware) and I like to use TextPad to edit text.

-------

1. Make a copy of your VideoDatabaseV5.db3 file open it up in SQLiteBrowser.exe (in ..mediaportal\Docs)

2. Do 'File|Import database from SQL File', select mp_to_ant.sql (below) and say no to prompt. A large DB may take a few minutes to convert so be patient, it will say 'Import completed' when it's done.

3. Export Ant table from SQLite as ant.csv

4. Run Ant do File|Import, select csv file type, set delimiter = , (that's a comma) and load up ant.csv.

5. untick the first row (containing the column names). Assign the SAME named column id's to each column (IGNORING idMovie it is not required!) by selecting in drop down then clicking on column, then click Import. You will be prompted what to do with images you MUST select 'Link'

6. When import has finished save to C:\Program Files\MediaPortal\Thumbs\videos\Title\ant.xml setting type as xml, then close Ant.

7. You now need to open the ant.xml file in a text editor (I use Textpad) and search/replace to strip out the nonsense Ant Import puts in for the picture files entry leaving just the filename (no path), it's at the end of each xml string. For me it was (d:\ant\%#34;)=() And (.jpg%#34;)=(.jpg), again be careful to specify a long enough search string that won't affect anything else. If you don't use movie covers you won't need to do this.

8. Now in MP Config|Plugins|MyFilms select ant.xml, set picture path to your C:\Program Files\MediaPortal\Thumbs\videos\Title\ folder, storage file info = Source, ant id item = media label. Save and exit.

Most recent version of MyFilms (v2.3) now works with MediaPortal v0.2.1.0. You can also import the ant.xml into MyDVD's though with my large db it took forever to load and didn't parse the comma seperated actor or genre fields so was abandoned. Once you're using ant to organise your movies you'll never look back, there are scripts to automate grabbing info from many different web sites. The mp_to_ant.sql is attached to this post and also listed below


mp_to_ant.sql

CREATE TABLE Ant (
idMovie  integer,
MediaLabel text, 
MediaType text,
Source text,
Rating integer, 
OriginalTitle text, 
Director text,
Category text, 
Year integer, 
Length integer, 
Actors text, 
URL text, 
Description text,
Picture text);

INSERT INTO Ant Select 
movie.idMovie,
movie.discid,
'', 
path.strPath || files.strFilename, 
movieinfo.fRating, 
movieinfo.strTitle, 
actors.strActor,
'', 
movieinfo.iYear, 
movieinfo.runtime, 
'', 
'http://imdb.com/title/' || movieinfo.IMDBID, 
movieinfo.strPlot, 
movieinfo.strTitle || 'L.jpg' 
From movie,movieinfo,path,files,actors 
WHERE movieinfo.idMovie = movie.idMovie and 
files.idMovie = movie.idMovie and 
path.idPath = movie.idPath and 
files.idMovie = movie.idMovie and 
actors.idActor = movieinfo.idDirector;


CREATE TABLE tmpActor (idMovie,strActor text);

CREATE TRIGGER insert_ant_actor INSERT ON tmpActor 
  BEGIN
    UPDATE Ant SET Actors = Actors || new.strActor || ',' WHERE Ant.idMovie = new.idMovie;
  END;

INSERT INTO tmpActor 
SELECT actorlinkmovie.idMovie,actors.strActor FROM actorlinkmovie
JOIN actors ON actorlinkmovie.idActor = actors.idActor;


CREATE TABLE tmpGenre (idMovie, strGenre text);

CREATE TRIGGER insert_ant_genre INSERT ON tmpGenre 
  BEGIN
    UPDATE Ant SET Category = Category || new.strGenre || ',' WHERE Ant.idMovie = new.idMovie;
  END;

INSERT INTO tmpGenre 
SELECT genrelinkmovie.idMovie,genre.strGenre FROM genrelinkmovie
JOIN genre ON genrelinkmovie.idGenre = genre.idGenre;


UPDATE ANT SET Category = (SUBSTR(Category,1,LENGTH(Category)-1)); 
UPDATE ANT SET Actors = (SUBSTR(Actors,1,LENGTH(Actors)-1)); 


DROP TRIGGER insert_ant_actor;
DROP TABLE tmpActor;
DROP TRIGGER insert_ant_genre;
DROP TABLE tmpGenre;


UPDATE Ant Set MediaType = "REAL" WHERE LOWER(SUBSTR(Source,LENGTH(source)-2,3)) = '.rm';
UPDATE Ant Set MediaType = "ASF" WHERE LOWER(SUBSTR(Source,LENGTH(source)-2,3)) = 'asf';
UPDATE Ant Set MediaType = "DIVX" WHERE SUBSTR(Source,LENGTH(source)-2,3) = 'avi';
UPDATE Ant Set MediaType = "DVD" WHERE SUBSTR(Source,LENGTH(source)-2,3) = 'ifo';
UPDATE Ant Set MediaType = "MKV" WHERE SUBSTR(Source,LENGTH(source)-2,3) = 'mkv';
UPDATE Ant Set MediaType = "MOV" WHERE SUBSTR(Source,LENGTH(source)-2,3) = 'mov';
UPDATE Ant Set MediaType = "MPEG4" WHERE SUBSTR(Source,LENGTH(source)-2,3) = 'mp4';
UPDATE Ant Set MediaType = "MPEG" WHERE SUBSTR(Source,LENGTH(source)-2,3) = 'mpg';
UPDATE Ant Set MediaType = "OGG" WHERE SUBSTR(Source,LENGTH(source)-2,3) = 'ogm';
UPDATE Ant Set MediaType = "MPEG" WHERE SUBSTR(Source,LENGTH(source)-2,3) = 'peg';
UPDATE Ant Set MediaType = "WMV" WHERE SUBSTR(Source,LENGTH(source)-2,3) = 'wmv';