Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[prod]

SqlMyTunes for MC is an application which exports your MC media library to a chosen SQL server and allows you to write SELECT statements in SQL that create playlists and smartlists, as well as perform updates on your MC library. If you have more than one MC library, all can be exported, and subsequently utilized. That is, you are able to create playlists and smartlists or perform updates based on entries in other libraries, if you wish to.

Currently, SqlMyTunes supports Microsoft SQL Server and SQL Express.

Microsoft SQL Express is free and can be downloaded from here.

Screenshots:





Download

[morrison]

Very interesting startup, I will try it

My main sync task - sync ratings and playstats from my car DB to main home DB. MC can't do this (.
I do it not very often - "removable media" in the car is 2,5 "500GB, frequent synchronization is not required. Statistics now I do not synchronize, and manually compare the ratings (

If you plan to do more examples, you maybe consider this too)

[prod]

Cheers, hope you like it.

Off the top of my head, if HOME is your home database and CAR is your car database;

Copy all the HOME ratings to CAR;

Code: [Select]

CREATE PROCEDURE UpdateCarRatings
@libraryName VARCHAR(255)
AS
SELECT cf.[FileKey], hf.[Rating]
FROM HOME.dbo.[Files] hf
JOIN CAR.dbo.[Files] cf ON cf.[Filename] LIKE hf.[Filename]
WHERE cf.[Rating] != hf.[Rating]

If you also rate tracks in your car database and want a "proper" to-and-fro sync rather than straight copy then it would be a little more complicated, but this gives you a taste.

Update latest play stats;

Code: [Select]

CREATE PROCEDURE UpdateLastPlayed
@libraryName VARCHAR(255)
AS
IF @libraryName = 'My Car MC library'
BEGIN
    SELECT cf.FileKey, hf.[Last Played]
    FROM HOME.dbo.[Files] hf
    JOIN CAR.dbo.[Files] cf ON cf.[Filename] LIKE hf.[Filename]
    WHERE hf.[Last Played] > cf.[Last Played]
END
ELSE IF @libraryName = 'My Home MC library'
BEGIN
    SELECT hf.FileKey, cf.[Last Played]
    FROM CAR.dbo.[Files] cf
    JOIN HOME.dbo.[Files] hf ON hf.[Filename] LIKE cf.[Filename]
    WHERE cf.[Last Played] > hf.[Last Played]
END

The first example would be entered as an Updates to library action in your car library settings, the other in both your car and home settings. I'm using the filename to link the files in each database together.

Let me know if you want me to expand on those examples.

[morrison]

Thank you very much for the detailed explanation prod. I will learn )

[Quixote]

Wow, this whole thing looks very promising!

[prod]

Thanks Quixote let me know how you get on.

If anyone wants some help putting together an SQL script I'm happy to lend a hand.

[prod]

SqlMyTunes for MC v1.1

• Added: Support for custom DPI settings - i.e. sizeable windows and scrollbars. Window sizes are remembered for next time.

[prod]

SqlMyTunes for MC v1.2

• Improved: Made some important improvements to the user interface.

[prod]

SqlMyTunes for MC v1.2.5

• Added: "Check for updates" checkbox to settings. If checked, will check for updates to the software once every week.

[idhToronto]

Hi

The documentation for this SqlMyTunes for MC does not state if it works with MC15 - would someone be able to confirm if it does or not?

Also, I presume that since this exports a library to a SQL database we can the produce reports using a report writer - is that correct?

Thanks

Ian

[prod]

Hi Ian

Yes SqlMyTunes works with MC15, and yes the resulting SQL database can be manipulated however you like - including producing reports with a report writer.

[Lasse_Lus]

Prod, i can't get SQLmyTunes to cowork with MC 16, any suggestions ? 

[prod]

Should work fine with MC16. It might be the old upgrading issue - if you've installed MC16 then uninstalled MC15 (in that order) then that can deregister MC from the COM registry.

Fix is simply rerun the MC16 setup exe. If that doesn't work let me know some details of the problem.

[Lasse_Lus]

Prod, i've tested now on a "fresh" machine with only MC installed never been "touched" by sqlmytunes
version 12,13,14,15 is working like a charm
version 16 says An Error occured communicating with MC. Try loading MC manually first.

i also reset the settings file, tried with a fresh deafault library, running as a admin, reinstalled MC --> no difference

something is different with the lastest MC version

regards L

[prod]

OK thanks I'll have a good look over the weekend.

[prod]

By the way, did you try what I suggested and rerun your MC16 setup exe? It sounds awfully like MC has been deregistered.

[Lasse_Lus]

understand prod, i did reinstall..i also went for a third machine with only MC 16 installed, same problem..sorry  thanks for your concern 

[prod]

Lasse_Lus

Sorry for doubting you, you're absolutely right, there is a problem with MC16. The way library information is stored in MC16 is different to previous versions, and so SqlMyTunes is unable to find the MC16 library or libraries. I'm not sure when this change was made.

I'll put a fix together hopefully in the next week.

[Lasse_Lus]

thanks mate 

[prod]

OK I've fixed this MC16-specific library problem in v1.2.6:

http://misterpete.co.uk/files/smtmcv126.zip

I've tested it and it works fine with all MC versions from 12.

Please note this is in beta, I'll make a formal release to the website next week.

Cheers!

[Lasse_Lus]

 , seems to work again...thanks prod

[drc]

I've reviewed the program, but can't get it to sync.  Right off, I know the problem starts with me.  Never really got into SQL, but now I really want to try it.

I've downloaded the MS SQL 2008 R@, it's installed as best I can tell...

From what I've able to summize The SQL Tunes is a GUI for interfacing with MC-15/16.

However, when I test the SQL, it says It can't find it. failed connection.   

Is there a user guide for beginners which shows how to install and map to database.  Looking at your example, I think my problem is that I don't see how to browse to the database I created.

Any help

Thanks

[prod]

Hi drc

Sorry it's taken so long to reply.

There are so many reasons why you might have a failed connection. Usernames, passwords, firewalls, non-existent databases

You should probably test it in the following way;

1. Have everything on the same machine
2. Create a database in SQL
3. Run SqlMyTunes and test the connection.

[broncodan]

Hi Prod,

I was looking at this program and it looks like it will do what I want it to do:  create a smartlist/playlist for artists that have x number of songs with a rating.  But I am not sure how to use this.  I have got it so it connects to the SQL server and I can get it to sync successfully without changing any of the options (I did check export all library entries and selected the fields that I thought were needed - album, artist, name, track number, rating, etc).  I don't have any playlists selected and didn't change any of the settings.

You have the following 'procedure' on your website:

Auto-manage Rated Artist Smartlists

But I do not know where to cut and paste that information to get this to create the smartlists?

Thanks in advance for your help.

[prod]

Hi broncodan

The example query can be pasted into a SQL Management Studio query window.

1. Load up SQL Management Studio
2. Select "New Query"
3. You should now select the SQL database that contains your MC library info. You can use the drop down in the toolbar or type USE database_name and press F5.
4. Paste in the query into the query window and execute it by pressing F5 or clicking the execute button.

That will create a stored procedure in your database that can be executed by SqlMyTunes. You can now add the name of it to the Playlists section in SqlMyTunes.

Hope this helps.

[broncodan]

Hi Prod,

Thanks for your suggestions.  It is getting me closer....   I don't know a thing about SQL but I think I managed to get the query in sql managment studio correctly (as it says there is already one in the database) but I am not sure how to save it?

I am also getting the following error message from SQLmytunes:

<Result EndDateTime="2012-09-23T16:59:22">Could not find stored procedure 'RatedArtistSmartlists'.</Result>

I have attached 2 screen shots.  If I can get this working I will gladly buy the program as I really want to be able to generate these smartlists automatically.

Thanks again for your help.

[broncodan]

Well after playing around a bit I finally got it to work.  Really cool.

I modified your template by changing the rating and number of tracks per artist, add some MC smartlist expressions - to remove duplicates and eliminate Christmas as a genre and it works great.  Not sure how to change the playlist path of RATED as this is noted in a couple of places but easy enough to rename the folder in MC.  

I imagine there are a lot of other awesome playlists you could come up with I would be interested to see if there are any other suggestions out there or to see what other people have used your program for.

I have a question - can you import a playlist and then reference that in the creation of the lists?  The reason I ask is that I have a series of smartlists that end up with a single playlist that has eliminated all duplicate songs, excluded genre's, etc from my library and it would be great to reference the songs in that list and then use that to run the query off of?  Right now I used the sort function to try to get the playlist to select the better versions (based off date) but it doesn't always work well.

As an FYI I had to use the beta version posted in this thread to get it to work with MC17.  Not sure if you want to update your website.  Off to pay for this....Hopefully my key will work with the version I am using.

[broncodan]

Hi Prod,

I was hoping you could help me with an SQL question.  I have used your program to generate playlists for individual years, genres, rated artists, etc - but I was wondering what I would need to enter to get the playlists to be created in groups of 10 or something based off a range (e.g. - create smartlists based of BPM values - so I would get a smartlist for each block of 20 - BPM 0 - 20; 21 - 40; 41 - 60; or for decades - so I would get a smartlist for files that had year - 1960 - 1969; 1970 - 1979; 1980 - 1989; etc)

CREATE PROCEDURE [dbo].[BPMsmartlists]
@libraryName NVARCHAR(255) = NULL
AS

SELECT [BPM]
INTO #t
FROM [Files]
WHERE [Rating] >= 1
AND [Media Type] = 'Audio'
GROUP BY [BPM]..............................................................................H ow do I get the range here?
HAVING COUNT(*) > 5

SELECT DISTINCT 'BPM' [PlaylistPath],
      [BPM] [PlaylistName],
      '[BPM]=[' + [BPM] + '] [Rating]=>=1  ~nodup=[Artist],[Name] ~sort=Random' [Expression],
      'BPM: ' + [BPM] + CHAR(13) + CHAR(10) + 'Rating: 1 Stars' + CHAR(13) + CHAR(10) + 'Sort: Random' [Notes]
INTO #t2
FROM [Files]
WHERE [BPM] IN (SELECT [BPM] FROM #t)

SELECT * FROM #t2

SELECT [Path] [PlaylistPath], [Name] [PlaylistName], CAST(1 AS BIT) [Delete]
FROM [Playlists] p1
WHERE [Path] LIKE 'BPM'
AND NOT EXISTS
   (
      SELECT *
      FROM #t2 p2
      WHERE p1.[Path] = p2.[PlaylistPath]
      AND p1.[Name] = p2.[PlaylistName]
   )

DROP TABLE #t
DROP TABLE #t2


Any help or suggestions would be good (as any fyi - I actually haven't tried the above yet - I just copied one of your recipes and changed from Artist to BPM.

Thanks.

[prod]

Hi Dan

You'd use a CASE statement here, so briefly -

SELECT
  CASE
  WHEN [BPM] >= 10 AND [BPM] < 20 THEN '10-19'
  WHEN [BPM] >= 20 AND [BPM] < 30 THEN '20-29'
  END [BPMRange]
FROM etc etc
GROUP BY
  CASE
  WHEN [BPM] >= 10 AND [BPM] < 20 THEN '10-19'
  WHEN [BPM] >= 20 AND [BPM] < 30 THEN '20-29'
  END

or something like that... note you can't GROUP BY calculated columns, that's why there's duplication.

Hope that at least points you in the right direction!

[milehigh]

Hi,

I use this for MC and import the playlists to iTunes using your mc itunessync program.  I know you have this for iTunes also and I thought I would go and get it and just generate the playlists directly in iTunes to make it faster - when I go to your old website - misterpete.co.uk it redirects me to the mcitunessync thread....  is this still available for iTunes?

[prod]

Hi,

I use this for MC and import the playlists to iTunes using your mc itunessync program.  I know you have this for iTunes also and I thought I would go and get it and just generate the playlists directly in iTunes to make it faster - when I go to your old website - misterpete.co.uk it redirects me to the mcitunessync thread....  is this still available for iTunes?

Hi,

I took down the site recently as I switched cloud storage provider and haven't got round to putting it back up.

But yes the iTunes version is here. It's a little different to the MC version but there are instructions in the zip file.

Cheers.

[milehigh]

Thank you.

[milehigh]

Hi Prod,

You use to have some examples for itunes playlists for sql my tunes - are those examples still available somewhere?  I am looking to create a few different types of playlists (all songs rated by an artist being one of them).

Thanks again.

[danrien]

Neat idea!

[broncodan]

Hi Prod,

I was wondering if this was still active?  I tried to use it with MC22 and I am getting the Try loading MC manually first.  I don't know how to do this.  Just wondering if it doesn't work with the newer versions of MC.

Thanks in advance.

[broncodan]

never mind - got it to run.  I started sql first, i also reinstalled MC18 and MC22 and I am guessing there was a missing entry in the registry.

Anyway - works great as usual.

Thanks.