It is indeed possible to do this. However - the following will be required:
- XBMC must use the MySQL database setup
- Amarok must use the same Mysql DB xbmc is using
- the xbmc user has SELECT rights to the amarokdb.
- On the amarokdb - we have to crate a new view
- You are using the same media repository on both sides, means both amarok and xbmc will read the mp3 tags out of the same files.
If these requirements are met - migrating the data over from Amarok to xbmc is possible.
Note - the following examples are working on Amarok 2.7.0 and xbmc 12 (Frodo).
Modifications to be done on the XBMC DB. Here MyMusic32.
# View showing the real data
CREATE ALGORITHM=UNDEFINED VIEW `XTAPCount` AS select `song`.`idSong` AS
`Xid`,`song`.`strTitle` AS `XSTitle`,`album`.`strArtists` AS
`XARTName`,`album`.`strAlbum` AS `XALBName`,`song`.`iTimesPlayed` AS
`XPlaycount` from (`song` join `album`) where (`song`.`idAlbum` =
`album`.`idAlbum`);
We will need an intermediate table to store the local and remote playcounts.
CREATE TABLE IF NOT EXISTS `XTAPCount_last` (
`LXid` int(11) NOT NULL,
`LXSTitle` varchar(512) NOT NULL,
`LXARTName` varchar(512) NOT NULL,
`LXALBName` varchar(512) NOT NULL,
`LXPlaycount` int(11) NOT NULL,
`LXpcdiff` int(11) NOT NULL,
PRIMARY KEY (`LXid`),
KEY `LXSTitle` (`LXSTitle`(333)),
KEY `LXARTName` (`LXARTName`(333)),
KEY `LXALBName` (`LXALBName`(333))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Table to keep the playcount differences.';
The next view will actually show the differences in playcounts.
# View to show the differences in playcounts.
CREATE OR REPLACE
ALGORITHM = UNDEFINED
VIEW `XTAPCount_last_diff` AS
SELECT Xid, XSTitle, XARTName, XALBName, XPlaycount, LXPlaycount, (
XPlaycount - LXPlaycount
) AS XDiff
FROM XTAPCount_last, XTAPCount
WHERE Xid = LXid
AND (
XPlaycount NOT LXPlaycount
);
Let's insert the data for the first time. We take the values as they are
insert INTO XTAPCount_last(LXid, LXSTitle, LXARTName, LXALBName, LXPlaycount )
SELECT Xid,XSTitle,XARTName,XALBName,XPlaycount from XTAPCount;
Update the LXPlaycount (This is the initial run).
UPDATE XTAPCount_last SET LXpcdiff=LXPlaycount;
Let's prepare the same for the Amarok side.
# View showing the real data
CREATE ALGORITHM=UNDEFINED VIEW `ATAPCount` AS select distinct `tracks`.`id`
AS `Aid`,`tracks`.`title` AS `ASTitle`,`artists`.`name` AS
`AARTName`,`albums`.`name` AS `AALBName`,`statistics`.`playcount` AS
`APlaycount` from (((`tracks` join `statistics`) join `artists`) join `albums`)
where ((`tracks`.`id` = `statistics`.`id`) and (`artists`.`id` =
`tracks`.`artist`) and (`tracks`.`album` = `albums`.`id`)) order by
`statistics`.`playcount` desc;
Intermediate Table - where we will store local and remote (here) XBMC Playcount.
CREATE TABLE IF NOT EXISTS `ATAPCount_last` (
`LAid` int(11) NOT NULL,
`LASTitle` varchar(512) COLLATE utf8_bin NOT NULL COMMENT 'Song Title',
`LAARTName` varchar(512) COLLATE utf8_bin NOT NULL COMMENT 'Artist name',
`LAABName` varchar(512) COLLATE utf8_bin NOT NULL COMMENT 'Artist Name',
`LAPlaycount` int(11) NOT NULL COMMENT 'Playcount',
`LApcdiff` int(11) NOT NULL COMMENT 'Playcount difference to last time',
PRIMARY KEY (`LAid`),
KEY `LASTitle` (`LASTitle`(333)),
KEY `LAARTName` (`LAARTName`(333)),
KEY `LAABName` (`LAABName`(333))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table containing last play state.';
LAst but not least, the View to show the differences in playcounts.
CREATE OR REPLACE
ALGORITHM = UNDEFINED
VIEW `ATAPCount_last_diff` AS
SELECT Aid, ASTitle, AARTName, AALBName, APlaycount, LAPlaycount, (
APlaycount - LAPlaycount
) AS ADiff
FROM ATAPCount_last, ATAPCount
WHERE Aid = LAid
AND (
APlaycount LAPlaycount
);
Make the Initial data dump
INSERT INTO ATAPCount_last(LAid, LASTitle, LAARTName, LAABName, LAplaycount )
SELECT Aid,ASTitle,AARTName,AALBName,APlaycount from ATAPCount;
And put the playcount into the temporary Field LApcdiff (Amarok DB only here).
UPDATE ATAPCount_last SET LApcdiff=LAPlaycount;
Now comes the Sync Process. Depending on the number of lines you have in there, it may take some time. With 12K Songs - it takes around 12 Minutes for my DB's to sync.
Sync the Data Amarok -> XBMC
UPDATE MyMusic32.XTAPCount,ATAPCount_last_diff SET
MyMusic32.XTAPCount.XPlaycount=(MyMusic32.XTAPCount.XPlaycount +
ATAPCount_last_diff.ADiff) WHERE ASTitle=XSTitle AND AARTName=XARTName AND
AALBName=XALBName and ADIff > 0;
Once this is done - set the current state with:
UPDATE ATAPCount_last,ATAPCount set LAPlaycount=Aplaycount WHERE LAid=Aid;
Put the playcount into the temporary Field LApcdiff (Amarok DB only here).
UPDATE ATAPCount_last SET LApcdiff=LAPlaycount;
Sync the Data XBMC -> Amarok
UPDATE amarokdb.ATAPCount, XATAPCount_last_diff SET
amarokdb.ATAPCount.APlaycount=(amarokdb.ATAPCount.APlaycount +
XATAPCount_last_diff.XDiff) WHERE ASTitle=XSTitle AND AARTName=XARTName AND
AALBName=XALBName and XDiff > 0;
You're done.
|