Stargate Logo  Secured by phpPhobos

Smurphtools   
  Login  - No Account?  Create One   
Search 
Sunday, 15 December 2019  -
News FeedRSS Feed
rss rdf  
  Home 
  Authentication 
 Documentation 
    Site Documentation 
    About me 
  Legal Notice 
 Applications 
    Web Links 
 RC Models 
    RC Models 
    RC Batteries 
 Modules 
    Downloads 
    Weblinks 
 Blacklists 
    Blacklist 
    Blockout 
    DNS Blacklist 
 Registered Bloggers 
  Blog de Maiwenn 
  Blog de Morgane 
  Joerg's Blog 
 Gallery [Listing]
  > Diving 
  > Martinique 
  > Steampunk 
  > RC Planes 
  > FPV Drones 
  > Indy travels 
  > Budapest 
 FAQ  [ Topics  ]
 Common Linux problem... 
 Routerboard RBxxxAH 
 Apple Mac-mini 
 PHP Phobos 
 Stargate's Backup sc... 
 eBook Reader / PRS-5... 
 RC Models 
 Server in SolLan 
 System Stats [ Index ]
 Router
  > Gateway 
 Server
  > Stargate 
  > Halley 
 Client
  > RemPi 
Question ? Is there a way to migrate playcount data from amarok to xbmc ?   [
View DetailsView details
|
Print ViewPrint view
]

 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. 

Entered by smurphy on Sunday, 10 March 2013 @ 16:35:17  
Nothing Specific - Common Linux problems, # Hits: 53568
  Back Back  
 
Problems to  root(-AT-)solsys(-DOT-)org  - best viewed @ 1920bpp
This site is powered by phpPhobos v2.0b446
© J. Mertin smurphy(-AT-)solsys(-DOT-)org 
Icons - Copyright Breeze artists GPL 2+