PDA

View Full Version : LMS Extractor Is Now Available



Dogberry2
2014-05-24, 12:44
I decided to put together a little program to extract data from the LMS database and put it out in some useful formats. Specifically, it can create HTML pages, Excel spreadsheets and comma-separated-value (CSV) files suitable for loading into other spreadsheet or database-type software.

This lets me pull out lists of albums (organized by artist or genre or whatever), or tracks, or my pre-defined playlists, or anything, and put them into easily readable Web pages or spreadsheets. It isn't rocket science or earth-shattering; it's just a nice little convenience.

Basically, it can take any SQL query (there are several pre-packaged queries built in, but it can also take new ones as long as they're syntactically correct), run them against the LMS database, and spit out the results in any of those three file formats: HTML, Excel and CSV.

So for example, you can select the "Albums by Genre" query, tell it where your LMS database is, tell it you want an HTML and an Excel file, click the "Run" button, and it will spit out those files with all your albums, ordered by genre. Simple. If you want additional fields that aren't included in the built-in "Albums by Genre" query, you can edit the SQL to suit your needs. (Clearly, to customize the SQL you'll have to know something about SQL and the database fields, or ask somebody who does.) There are 7 built-in queries:

Albums by Artist
Albums by Genre
Albums by Year
Playlists (lists all tracks in all playlists)
Playlist Names (lists just the playlist names and total number of tracks in each)
Tracks by Artist/Album
Tracks by Genre

Any of these can be used as a starting point and then modified or customized to suit you. Or you can just start from scratch and define a completely new query, for whatever information you want.

Defined extracts (either built-in or customized) can be saved in a file, so you can re-use them. I generally re-run extracts after I've added new albums, or made significant changes to tags. You can define a collection of different extracts, and save them all into one file, then just open that file and select the ones you want to run.

I'm making the program available to anyone who wants to use it. But here's the deal:

1) This is not a highly-polished, commercial-grade piece of software, and I have no intention of making a career of continuing development on it. It's just a little utility I threw together quickly, for my own use. It works well for what it is, but I don't plan to spend a lot of time supporting it. If someone has a question or problem, they're welcome to let me know and I will respond, but the response might sometimes be "I don't have time to deal with that right now."

2) It runs on Windows machines only, and there won't be any Linux or OS-X version. If your LMS runs on a non-Windows system you can still use the program, but only if you run it on a Windows machine and either map a drive to the LMS system or make a copy of the LMS database on the Windows system. I've tested it on Windows XP and 7. It should run on Windows 8, but I don't have a system to test it on.

You can find the software and documentation at http://thecosyns.com/lms/lmsextractor.html

I hope this is useful to some of you. Let me know if you have problems installing or using it (or even if you don't have problems).

Dogberry2
2014-05-24, 12:57
Here are a couple of sample HTML pages created by LMS Extractor. These are just simple examples; you can extract any data you want to see.

http://thecosyns.com/lms/albums.html

http://thecosyns.com/lms/playlistnames.html

get.amped
2014-05-24, 14:04
Very nice. Looking forward to taking it for a test drive.

DJanGo
2014-05-25, 03:56
Let me know if you have problems installing or using it (or even if you don't have problems).
Hi,


qtintf70.dll is missing:confused:

Dogberry2
2014-05-25, 05:02
Hi,

:confused:My apologies. Please re-download the .zip file and try again. Should be OK now.

usbethjim
2014-05-26, 06:48
I've installed and tried your extractor - simple and speedy.

I know nothing about SQL so I used your built-in queries.

However to be of use to me, I would have to either have you change a couple of basic things in the queries, have you tell me how to do it myself or I need to learn SQL.

Here's the thing - Every album I have has an "ARTIST SORT" tag:

Bob Dylan's is "Dylan, Bob" - he show's up in the D's not the B's as "Bob Dylan" in LMS listings.

Led Zeppelin's is "Led Zeppelin" - they show up in the L's as "Led Zeppelin" in LMS listings.

So any SQL query would have to use the same logic.

Is this a simple thing to do?

I understand "I don't have time to deal with that right now" completely!

Thanks for putting this out there.

Jim

Dogberry2
2014-05-26, 07:35
Here's the thing - Every album I have has an "ARTIST SORT" tag:

Bob Dylan's is "Dylan, Bob" - he show's up in the D's not the B's as "Bob Dylan" in LMS listings.

Led Zeppelin's is "Led Zeppelin" - they show up in the L's as "Led Zeppelin" in LMS listings.

So any SQL query would have to use the same logic.

Is this a simple thing to do?
Jim

I don't use ARTIST SORT tags myself, so I can't really tell for sure whether this will give you what you want. But you can give it a try, and let me know.

In the query you're using (Albums by Artist, or any of the others), find where it says:

co.name

and change it to

co.namesearch

If that works for you, change any of the other queries you use in the same way, and save the modified Extracts into a file you can re-use (described in the documentation). That way you won't have to go in and edit the query every time.

Please let me know whether that works for you or not. There are a lot of different ways people use their systems and tags, and a therefore a lot of different ways to query the database, and I certainly don't have any real expertise on how that data is organized and used. I found some things that work for me, but as your case shows, some tweaks will be needed to make it work the way others use their system. I think it's flexible enough to make those tweaks possible, but knowing what tweaks to make is another matter, and in this case I'm just taking a guess.

Thanks for letting me know you've tried the program, and I'm sure we can find the tweaks you need, even if it takes a little trial-and-error.

Apesbrain
2014-05-26, 08:18
Thank you for this. It works well and makes the whole SQL query thing a lot less intimidating.

get.amped
2014-05-26, 09:00
Sometime a little nudge is what we need :)

I had been meaning to install and get familiar with SQLite Expert but have found any number of reasons not to. But when I decided to try LMS Extractor it was immediately obvious that I would need something to reveal the table structures if I wanted to do much beyond your default queries.

So I now am using both, SQLite Expert as a query building tool and LMS Extractor as an easy way to save the results in a useful format.

I have long wanted a way to build a catalog of my albums and in addition to the artist, title, genre and date, identify which format (codec and bitrate) they are in.

The following SQL query works pretty well. It's not necessarily 100% accurate as it only uses data from the first track of the album, but it's close enough for my needs. Note the need for single quotes around Compilation. That threw me for a few minutes when "1" kept showing up as the Artist for compilation albums.

SELECT
CASE
WHEN al.compilation = 1 THEN CAST('Compilation' AS CHAR(150))
ELSE TRIM(CAST(co.name AS CHAR(150)))
END AS "Artist",
TRIM(CAST(al.title AS CHAR(150))) AS "Album Title",
TRIM(CAST(gn.name AS CHAR(150))) AS "Genre" ,
al.year AS "Year",
CASE
WHEN tr.content_type = "flc" THEN "FLAC"
WHEN tr.content_type = "mp3" THEN "MP3"
WHEN tr.content_type = "mp4" THEN "MP4"
WHEN tr.content_type = "wma" THEN "WMA"
ELSE tr.content_type
END AS "Codec",
ROUND((tr.bitrate/1000),0) AS "Bitrate (Kbps)",
CASE
WHEN tr.vbr_scale = 1 THEN "VBR"
ELSE "CBR"
END AS "VBR/CBR",
CASE
WHEN tr.lossless = 1 THEN "YES"
ELSE "NO"
END AS "Lossless"
FROM albums al,
tracks tr,
contributor_track ct,
contributors co,
genre_track gt,
genres gn
WHERE al.id = tr.album
AND tr.tracknum = "1"
AND ct.track = tr.id
AND ct.role IN (1, 5)
AND co.id = ct.contributor
AND gt.track = tr.id
AND gn.id = gt.genre
ORDER BY 1, 2

garym
2014-05-26, 09:09
stupid question. How do I create a new SQL extract. I wanted to try the one above, but when I copy it I can't seem to paste it into one of the SQL extract windows in the program. I can paste it into a text document, not into the LMS extractor appropriate window (i.e., the window where I see the "sql language" from an existing extraction option).

edit: I can write into the SQL window itself, but can't paste into it.

Dogberry2
2014-05-26, 09:25
stupid question. How do I create a new SQL extract. I wanted to try the one above, but when I copy it I can't seem to paste it into one of the SQL extract windows in the program. I can paste it into a text document, not into the LMS extractor appropriate window (i.e., the window where I see the "sql language" from an existing extraction option).

edit: I can write into the SQL window itself, but can't paste into it.I'm not sure what to tell you. I can paste into the "SQL For Extract" box (in the Extract Definition form). I use the standard Windows paste shortcuts, either Shift-Insert or Ctrl-V, and they work just fine for me. Can you tell me how you're trying to paste?

get.amped
2014-05-26, 09:34
stupid question. How do I create a new SQL extract. I wanted to try the one above, but when I copy it I can't seem to paste it into one of the SQL extract windows in the program. I can paste it into a text document, not into the LMS extractor appropriate window (i.e., the window where I see the "sql language" from an existing extraction option).

edit: I can write into the SQL window itself, but can't paste into it.

I just went through it from the beginning:

Highlight text from post
Ctrl C
Open LMS Extractor
Choose library.db
Select Edit -> Add
Enter Extract Name
Tick CSV and enter name
Set focus to SQL For Extract box
Ctrl V
Click OK
Select Extract Name
Click Run arrow

Dogberry2
2014-05-26, 09:39
Sometime a little nudge is what we need :)

I had been meaning to install and get familiar with SQLite Expert but have found any number of reasons not to. But when I decided to try LMS Extractor it was immediately obvious that I would need something to reveal the table structures if I wanted to do much beyond your default queries.

So I now am using both, SQLite Expert as a query building tool and LMS Extractor as an easy way to save the results in a useful format.

<*SNIP*>
Glad to help you get off the schneid with SQL. :)

Yes, I assumed that my handful of basic queries would be just a starter kit, and since people use different tags and approach the organization of their libraries differently, most people will want to go after things in slightly different ways. The main purpose was just to give me a quick-and-easy way to run (and re-run) canned queries and output some standard file formats. Once I started using it I thought others might also find it useful.

I think your combination of using SQLite Expert to build and test queries, and LMS Extractor to save and run them, is a perfect approach. There is a lot of information buried in the LMS database, and a little digging will no doubt open up all kinds of possibilities. Have fun! And thanks for letting me know you got the Extractor working.

garym
2014-05-26, 09:40
I said it was stupid. ;-)
I forgot to try Ctrl-V (which worked perfectly).
(I was simply trying to right click my mouse to get the popup menu containing "paste".

get.amped's extract works nicely (I too like to see the genre and file type). Only odd thing I noticed in running this extract was that any artist that was lower case first letter gets added to the very end (after "Z"). Examples, "subdudes" or "moe."

get.amped
2014-05-26, 09:43
I said it was stupid. ;-)
I forgot to try Ctrl-V (which worked perfectly).
(I was simply trying to right click my mouse to get the popup menu containing "paste".

get.amped's extract works nicely (I too like to see the genre and file type). Only odd thing I noticed in running this extract was that any artist that was lower case first letter gets added to the very end (after "Z"). Examples, "subdudes" or "moe."

That's SQL's default sort order.

Dogberry2
2014-05-26, 09:47
I said it was stupid. ;-)
I forgot to try Ctrl-V (which worked perfectly).
(I was simply trying to right click my mouse to get the popup menu containing "paste".

get.amped's extract works nicely (I too like to see the genre and file type). Only odd thing I noticed in running this extract was that any artist that was lower case first letter gets added to the very end (after "Z"). Examples, "subdudes" or "moe."

I should have added a "right-click" menu to the SQL entry box. I just didn't think of it, 'cause I always use the keyboard for cut'n'paste. In the next update I'll put one in.

The sort order is defined by the DB engine (in this case SQLite), and the upper-case-first is pretty standard. You can overcome that by changing everything to upper case for the ORDER clause (you can still have the artist field returned in its original case). I don't have time right now to go in and futz with a query to give you an example, but if you don't get there on your own, I'll take a look later.

get.amped
2014-05-26, 10:04
Replace the last line with:

ORDER BY UPPER("Artist"), UPPER("Album Title")

garym
2014-05-26, 10:26
Replace the last line with:

ORDER BY UPPER("Artist"), UPPER("Album Title")

works perfectly thanks. Now I'm trying to understand why it shows more than one listing per album (other than the fact that there are multiple disks, which I understand). For example, on a Bob Dylan tribute compilation (4 CDs), it shows a listing of 8 albums. For the other multidisk CDs I've looked at, it will show one listing for each disc (so the Bob Dylan should be 4 listings, not 8). I looked at the files in mp3tag and all are exact same album artist, comp tag, album name, genre, etc. And there are definitely only 4 disk subdirectories under the album name directory. Anyhow, good stuff!!!

get.amped
2014-05-26, 10:36
works perfectly thanks. Now I'm trying to understand why it shows more than one listing per album (other than the fact that there are multiple disks, which I understand). For example, on a Bob Dylan tribute compilation (4 CDs), it shows a listing of 8 albums. For the other multidisk CDs I've looked at, it will show one listing for each disc (so the Bob Dylan should be 4 listings, not 8). I looked at the files in mp3tag and all are exact same album artist, comp tag, album name, genre, etc. And there are definitely only 4 disk subdirectories under the album name directory. Anyhow, good stuff!!!

Probably has something to do with my "cheat" of pulling the genre, codec and bitrate from the the first track of the album. I was reasonably sure that wouldn't be an issue in my database because I don't use any disc numbering tags. For example, AC/DC - Live (Collector's Edition) is a two disc set so disc one is Live (Collector's Edition) [CD1] and disc two is Live (Collector's Edition) [CD2], both for the album meta-tag and the physical directory name.

garym
2014-05-26, 10:46
Probably has something to do with my "cheat" of pulling the genre, codec and bitrate from the the first track of the album. I was reasonably sure that wouldn't be an issue in my database because I don't use any disc numbering tags. For example, AC/DC - Live (Collector's Edition) is a two disc set so disc one is Live (Collector's Edition) [CD1] and disc two is Live (Collector's Edition) [CD2], both for the album meta-tag and the physical directory name.

I've narrowed it down a bit. This only seems to happen on a compilation where it is multidisk and there is an ALBUM ARTIST entered. And it exactly doubles the number of albums listed. Most of my compilations have ALBUM ARTIST blank. Note that the Genre, ALBUM ARTIST, codec, album name, etc. are identical across all tracks. bitrate is a different (FLAC files).

get.amped
2014-05-26, 11:37
I've narrowed it down a bit. This only seems to happen on a compilation where it is multidisk and there is an ALBUM ARTIST entered. And it exactly doubles the number of albums listed. Most of my compilations have ALBUM ARTIST blank. Note that the Genre, ALBUM ARTIST, codec, album name, etc. are identical across all tracks. bitrate is a different (FLAC files).

So for those compilations, what is displayed in the Artist column?

I think the doubling is due to this line:

AND ct.role IN (1, 5)

which I did not change from the default SQL included with the program.

ct.role = 1 represents the contributor is the Artist.

ct.role = 5 represents the contributor is the AlbumArtist.

You can use this query to find all of them:

SELECT
TRIM(CAST(al.title AS CHAR(150))) AS "Album", TRIM(CAST(tr.title AS CHAR(150))) AS "Track", TRIM(CAST(co.name AS CHAR(150))) AS "Contributor"
FROM
albums al,
tracks tr,
contributor_track ct,
contributors co
WHERE al.id = tr.album
AND tr.id = ct.track
AND co.id = ct.contributor
AND ct.role = 5

And change the value of ct.role to find 4 (Band). I don't know how 6 is different than 5; I get the same list when I query for either.

Considering I have tried to be careful and not include AlbumArtist or Band, at least now I know which tags to clean up :) Only 58 tracks for AlbumArtist but 443 have Band.

Audiotic
2014-05-26, 11:41
Nice. But in what language is it written, i.e. can it be made to run on a Mac as well?

Dogberry2
2014-05-26, 12:54
Nice. But in what language is it written, i.e. can it be made to run on a Mac as well?

Well, as I said up front, the way it stands for now it's gonna have to be strictly a Windows program. I threw it together using the compiler I had available at the company I work for, during a couple slow work days, so it's actually written in Delphi, because that was handy and I could cobble it together quickly. When I started, it was just to be a one-off to give me the data I wanted; I didn't start out with the intention of passing it around to anyone else. So portability wasn't really a consideration. I'm an old-timer (I spent about 20 years working strictly in assembler code on large-scale mainframes) and don't do much coding anymore. I'd be happy to see a younger generation do a re-write of the thing in something like Java, or Perl with a browser interface or something, if anybody wants to do that.

But as I said before, you can run it against any LMS database, even if your LMS is on a Linux or Mac box. (My LMS runs on a Linux server, in fact.) You just need to have a Windows machine that can get to the database, either via a network or on an external drive or something.

garym
2014-05-26, 13:14
So for those compilations, what is displayed in the Artist column?


ALBUM ARTIST is displayed in artist column (in this case Bob Dylan). And in this case Bob Dylan is also the composer but is not track artist. (and I've NOT ticked composer or conductor or band/orchestra to show up in artists in my LMS Settings.

Audiotic
2014-05-26, 13:14
OK, I'll run it in Parallels :-)
Great initiative!

get.amped
2014-05-26, 14:24
ALBUM ARTIST is displayed in artist column (in this case Bob Dylan). And in this case Bob Dylan is also the composer but is not track artist. (and I've NOT ticked composer or conductor or band/orchestra to show up in artists in my LMS Settings.

I can't say I'm surprised that the anomaly has to do with compilation/album artist designation(s). That whole issue (and sure, go ahead and throw composer into the mix as well) has been confounding since digital music files first got meta tags. I decided way back when that either an album is a compilation or it's not. If it is, I don't care what kind of crazy artist names go in the artist tag because they aren't going to fill my browse by artist list with one-off entries. Compilations have the COMPILATIONS=1 tag set. If it's not a compilation, the artist is the artist. Which can lead to single name artists teaming up to make a new artist (Stills & Nash, Page & Plant, Plant & Krauss, etc.) and I'm fine with that. There's no ALBUMARTIST. Period. And the few that snuck in are now gone. There's no BAND. There's no COMPOSER.

For albums with guest artists who are featured on a particular track, I append [featuring ...] or [with ...] to the track name. That way they show up in global searches under songs, but not in albums by artist.

For classical albums, I prepend the name (often just the last name) of the composer to the track name. So I can find works by Beethoven or Mozart easily enough. The artist for those albums is the name of the musician or group of musicians playing the work. Generally, for orchestral works, I will append [conducted by ...] or [directed by ...] to the name of the symphony or orchestra.

Probably a bit simplistic, but it's made my role as the curator of my collection considerably less daunting.

Dogberry2
2014-05-27, 04:35
OK, I'll run it in Parallels :-)
Great initiative!

Thanks. Hope it runs OK for you in Parallels.

amey01
2014-05-29, 22:15
Frikin' awesome. Works perfectly. Thank you!

Dogberry2
2014-05-30, 07:39
Frikin' awesome. Works perfectly. Thank you!

You're welcome! I'm glad to hear it's of some use to other people.

I hope anyone who has a cool query will share it, maybe by posting it in this thread, so others can make use of it. Everybody has their own ideas/needs/approaches to cataloging their library, and hearing what other people are doing can sometimes spark us into "huh, I never thought of that! I could do that too!" So if you come up with improvements to existing queries, or something new, pass it around.

get.amped
2014-05-30, 09:13
I thought it might be useful to know the table structures. Here's some screen shots from SQLite Expert:

15973

15974

15975

15976

15977

More in next post (5 image/post limit)

get.amped
2014-05-30, 09:16
15978

15979

15980

15981

15982

continued in next post...

get.amped
2014-05-30, 09:18
15983

15984

15985

15986

15987

continued and completed in next post...

get.amped
2014-05-30, 09:21
15988

15989

15990

15991

That's all folks!

Of course, there are other database files. Not sure how much more value there is in querying them.

JJZolx
2014-05-30, 10:12
Does this do anything that Mp3tag's Export configurations can't do?

One thing about working directly with LMS' data is that you have to work around the shortcomings of the LMS database and LMS quirky scanning logic. If you've witnessed the struggles they've had over the years doing (what you would think are) simple things in LMS, then you should realize that working with this data can be a headache.

There are a few export configurations included with Mp3tag, but additional ones are easily added. The Mp3tag Export Configuration Archive is a good place to start if you want to customize something for yourself.

http://forums.mp3tag.de/index.php?showtopic=1563

get.amped
2014-05-30, 11:41
Does this do anything that Mp3tag's Export configurations can't do?

One thing about working directly with LMS' data is that you have to work around the shortcomings of the LMS database and LMS quirky scanning logic. If you've witnessed the struggles they've had over the years doing (what you would think are) simple things in LMS, then you should realize that working with this data can be a headache.

There are a few export configurations included with Mp3tag, but additional ones are easily added. The Mp3tag Export Configuration Archive is a good place to start if you want to customize something for yourself.

http://forums.mp3tag.de/index.php?showtopic=1563

I wonder if/when mp3tag chokes on the number of entries? I'm not sure it how it will handle 300K+ tracks...

garym
2014-05-30, 13:59
I wonder if/when mp3tag chokes on the number of entries? I'm not sure it how it will handle 300K+ tracks...

Dont know but I've had 225,000 tracks selected in mp3tag...

Dogberry2
2014-05-31, 13:53
Does this do anything that Mp3tag's Export configurations can't do?
I don't know about MP3Tag's Export configurations, so I can't answer that. It seems that to sort and filter the data, MP3Tag would first have to read all the track files and build its own internal database from the tags. Since LMS has already scanned the track tags and built a database, to me it was the obvious place to go after the data. Comme ci comme ša.

Dogberry2
2014-05-31, 15:44
I thought it might be useful to know the table structures. Here's some screen shots from SQLite Expert:
Nice of you to post those shots. There used to be some basic table structure info in the Wiki, but that was from back in the MySQL days, and I'm sure nothing has been updated since the switch (back) to SQLite. I'm not aware that there was ever any real documentation or ER diagrams or anything like that.

JohnB
2014-06-02, 03:28
For a while I've been periodically extracting the LMS db tables using sqlite3.exe before importing them into Access (which I am more familiar with). However, before I do that I always clear the cache and do a complete rescan to ensure that all the data in the db is "live" and current.

You guys are probably much more experienced than me so can deal with the "dead" info in the LMS db without doing that - but I just thought I'd mention it.

DJanGo
2014-06-02, 04:33
For a while I've been periodically extracting the LMS db tables using sqlite3.exe before importing them into Access (which I am more familiar with). However, before I do that I always clear the cache and do a complete rescan to ensure that all the data in the db is "live" and current.

You guys are probably much more experienced than me so can deal with the "dead" info in the LMS db without doing that - but I just thought I'd mention it.

:confused:


always use a clean & rescan cause of otherwise eg. first artist did not has id1
you can use a odbc connection to the sqlite db as well (if you only read from that db not writing to it)


btw: a simple sqlite3 /var/lib/squeezeboxserver/cache/library.db .schema uis the equivalent to a describe tabele from mysql.
/edit
Sampleoutput from 7.8
\edit

CREATE TABLE albums (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title blob,
titlesort text,
titlesearch text,
customsearch text,
compilation bool,
year smallint(5),
artwork char(8), -- pointer to a track coverid that contains artwork
disc tinyint(1),
discc tinyint(1),
replay_gain float,
replay_peak float,
musicbrainz_id varchar(40), -- musicbrainz uuid (36 bytes of text)
musicmagic_mixable bool,
contributor int(10)
);
CREATE TABLE comments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
track int(10),
value text, -- needs to be text so that searches are case insensitive.
FOREIGN KEY (`track`) REFERENCES `tracks` (`id`) ON DELETE CASCADE
);
CREATE TABLE contributor_album (
role int(10),
contributor int(10),
album int(10),
PRIMARY KEY (role,contributor,album),
FOREIGN KEY (`album`) REFERENCES `albums` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`contributor`) REFERENCES `contributors` (`id`) ON DELETE CASCADE
);
CREATE TABLE contributor_track (
role int(10),
contributor int(10),
track int(10),
PRIMARY KEY (role,contributor,track),
FOREIGN KEY (`track`) REFERENCES `tracks` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`contributor`) REFERENCES `contributors` (`id`) ON DELETE CASCADE
);
CREATE TABLE contributors (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name blob,
namesort text,
namesearch text,
customsearch text,
musicbrainz_id varchar(40), -- musicbrainz uuid (36 bytes of text)
musicmagic_mixable bool
);
CREATE TABLE dbix_migration (
name CHAR(64) PRIMARY KEY,
value CHAR(64)
);
CREATE TABLE genre_track (
genre int(10),
track int(10),
PRIMARY KEY (genre,track),
FOREIGN KEY (`track`) REFERENCES `tracks` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`genre`) REFERENCES `genres` (`id`) ON DELETE CASCADE
);
CREATE TABLE genres (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name blob,
namesort text,
namesearch text,
customsearch text,
musicmagic_mixable bool
);
CREATE TABLE images (
id INTEGER PRIMARY KEY,
hash char(8) NOT NULL,
url text NOT NULL,
title blob,
titlesort text,
titlesearch text,
image_codec varchar(128),
mime_type varchar(32),
dlna_profile varchar(32),
width int(10),
height int(10),
mtime int(10),
added_time int(10),
filesize int(10)
, updated_time int(10) default NULL, original_time int(10) NOT NULL DEFAULT 0, orientation int(10) NOT NULL DEFAULT 0, album blob default NULL);
CREATE TABLE metainformation (
name varchar(255) NOT NULL DEFAULT '',
value varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (name)
);
CREATE TABLE playlist_track (
id INTEGER PRIMARY KEY AUTOINCREMENT,
position int(10),
playlist int(10),
track text NOT NULL,
FOREIGN KEY (`playlist`) REFERENCES `tracks` (`id`) ON DELETE CASCADE
);
CREATE TABLE pluginversion (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name varchar(255),
version int(10)
);
CREATE TABLE progress (
id INTEGER PRIMARY KEY AUTOINCREMENT,
type varchar(255),
name varchar(255),
active bool,
total int(10),
done int(10),
start int(10),
finish int(10),
info varchar(255)
);
CREATE TABLE rescans (
id INTEGER PRIMARY KEY AUTOINCREMENT,
files_scanned int(10),
files_to_scan int(10),
start_time int(10),
end_time int(10)
);
CREATE TABLE scanned_files (
url text NOT NULL,
timestamp int(10),
filesize int(10)
);
CREATE TABLE tracks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
url text NOT NULL COLLATE NOCASE, -- bug 17457: url needs to be case insensitive, or some import actions on Windows might fail
title blob,
titlesort text,
titlesearch text,
customsearch text,
album int(10),
tracknum int(10),
content_type varchar(255),
timestamp int(10),
filesize int(10),
audio_size int(10),
audio_offset int(10),
year smallint(5),
secs float,
cover blob,
vbr_scale varchar(255),
bitrate float,
samplerate int(10),
samplesize int(10),
channels tinyint(1),
block_alignment int(10),
endian bool,
bpm smallint(5),
tagversion varchar(255),
drm bool,
disc tinyint(1),
audio bool,
remote bool,
lossless bool,
lyrics text COLLATE NOCASE, -- needs to be text so that searches are case insensitive.
musicbrainz_id varchar(40), -- musicbrainz uuid (36 bytes of text)
musicmagic_mixable bool,
replay_gain float,
replay_peak float,
extid varchar(64),
primary_artist int(10),
urlmd5 char(32) NOT NULL default '0',
coverid char(8) default NULL,
cover_cached char(1) default NULL,
virtual char(1) default NULL,
added_time int(10) default NULL,
updated_time int(10) default NULL, dlna_profile varchar(32), hash char(8),
FOREIGN KEY (`album`) REFERENCES `albums` (`id`) ON DELETE CASCADE
);
CREATE TABLE unreadable_tracks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
rescan int(10),
url text NOT NULL,
reason text NOT NULL
);
CREATE TABLE videos (
id INTEGER PRIMARY KEY,
hash char(8) NOT NULL,
url text NOT NULL,
title blob,
titlesort text,
titlesearch text,
video_codec varchar(128),
audio_codec varchar(128),
mime_type varchar(32),
dlna_profile varchar(32),
width int(10),
height int(10),
mtime int(10),
added_time int(10),
filesize int(10),
secs float,
bitrate float,
channels tinyint(1)
, updated_time int(10) default NULL, album blob default NULL);
CREATE TABLE years (
id smallint(5),
PRIMARY KEY (id)
);
CREATE INDEX albumsArtworkIndex ON albums (artwork);
CREATE INDEX albumsCustomSearchIndex ON albums (customsearch);
CREATE INDEX albumsDiscCountIndex ON albums (discc);
CREATE INDEX albumsDiscIndex ON albums (disc);
CREATE INDEX albumsSearchIndex ON albums (titlesearch);
CREATE INDEX albumsSortIndex ON albums (titlesort);
CREATE INDEX albumsTitleIndex ON albums (title);
CREATE INDEX albumsYearIndex ON albums (year);
CREATE INDEX comments_trackIndex ON comments (track);
CREATE INDEX compilationSortIndex ON albums (compilation);
CREATE INDEX contributor_albumAlbumIndex ON contributor_album (album);
CREATE INDEX contributor_albumContribIndex ON contributor_album (contributor);
CREATE INDEX contributor_albumRoleIndex ON contributor_album (role);
CREATE INDEX contributor_trackContribIndex ON contributor_track (contributor);
CREATE INDEX contributor_trackRoleIndex ON contributor_track (role);
CREATE INDEX contributor_trackTrackIndex ON contributor_track (track);
CREATE INDEX contributorsCustomSearchIndex ON contributors (customsearch);
CREATE INDEX contributorsNameIndex ON contributors (name);
CREATE INDEX contributorsSearchIndex ON contributors (namesearch);
CREATE INDEX contributorsSortIndex ON contributors (namesort);
CREATE INDEX coveridIndex ON tracks (coverid);
CREATE INDEX ctSortIndex ON tracks (content_type);
CREATE INDEX genreCustomSearchIndex ON genres (customsearch);
CREATE INDEX genreNameIndex ON genres (name);
CREATE INDEX genreSearchIndex ON genres (namesearch);
CREATE INDEX genreSortIndex ON genres (namesort);
CREATE INDEX genre_trackGenreIndex ON genre_track (genre);
CREATE INDEX genre_trackTrackIndex ON genre_track (track);
CREATE INDEX imageDateTimeOriginal ON images (original_time);
CREATE INDEX imageHashIndex ON images (hash);
CREATE INDEX imageURLIndex ON images (url);
CREATE INDEX playlistIndex ON playlist_track (playlist);
CREATE INDEX positionIndex ON playlist_track (position);
CREATE INDEX scannedUrlIndex ON scanned_files (url);
CREATE INDEX trackAlbumIndex ON tracks (album);
CREATE INDEX trackAudioIndex ON tracks (audio);
CREATE INDEX trackBitrateIndex ON tracks (bitrate);
CREATE INDEX trackCustomSearchIndex ON tracks (customsearch);
CREATE INDEX trackDiscIndex ON tracks (disc);
CREATE INDEX trackExtId ON tracks (extid);
CREATE INDEX trackFilesizeIndex ON tracks (filesize);
CREATE INDEX trackHashIndex ON tracks (hash);
CREATE INDEX trackLosslessIndex ON tracks (lossless);
CREATE INDEX trackLyricsIndex ON tracks (lyrics);
CREATE INDEX trackRemoteIndex ON tracks (remote);
CREATE INDEX trackSearchIndex ON tracks (titlesearch);
CREATE INDEX trackSortIndex ON tracks (titlesort);
CREATE INDEX trackTimestampIndex ON tracks (timestamp);
CREATE INDEX trackTitleIndex ON tracks (title);
CREATE INDEX trackTracknumIndex ON tracks (tracknum);
CREATE INDEX unreadableRescanIndex ON unreadable_tracks (rescan);
CREATE INDEX urlIndex ON tracks (url);
CREATE INDEX urlmd5Index ON tracks (urlmd5);
CREATE INDEX videoHashIndex ON videos (hash);
CREATE INDEX videoURLIndex ON videos (url);

Mnyb
2014-06-03, 13:03
I don't know about MP3Tag's Export configurations, so I can't answer that. It seems that to sort and filter the data, MP3Tag would first have to read all the track files and build its own internal database from the tags. Since LMS has already scanned the track tags and built a database, to me it was the obvious place to go after the data. Comme ci comme ša.

The difference should be that LMS does not save all tag data only stuff thats of use for its own browse modes etc . And its possibly also optimised for this in some way . LMS own logic re compilations and artist may also skew the result ?

stereoptic
2014-12-13, 15:19
I need to brush up on my SQL.
What would be an example of listing the albumname, the sort album name , album artist and sort album artist from the albums (?) table?
I would be using the database query function in LMS, I don't have any other SQL loaded on this PC right now.

Thanks!

Dogberry2
2014-12-13, 17:18
I need to brush up on my SQL.
What would be an example of listing the albumname, the sort album name , album artist and sort album artist from the albums (?) table?
I would be using the database query function in LMS, I don't have any other SQL loaded on this PC right now.

Thanks!
I think something kinda like this should get you started:

SELECT TRIM(CAST(al.title AS CHAR(150))) AS "Album Name",
al.titlesort AS "Sort Album Name",
TRIM(CAST(co.name AS CHAR(150))) AS "Album Artist",
co.namesort AS "Sort Album Artist"
FROM albums al,
contributor_album ca,
contributors co
WHERE ca.album = al.id
AND co.id = ca.contributor
ORDER BY 1, 3

stereoptic
2014-12-14, 10:46
I think something kinda like this should get you started:...
Thanks for taking the time for that!

Is there a flag that indicates which contributor is the album artist? I am getting multiple entries per album (Composer as well as album artists. album artists appears twice for some reason.
EG.:
Album Name;Sort Album Name;Album Artist
!Viva! VIVA (D. Amis / E. Angel / S. Esbeck / L. J. Lester / B. Vaughn)
!Viva! VIVA (D. Amis / E. Angel / S. Esbeck / L. J. Lester)
!Viva! VIVA (Danny Amis)
!Viva! VIVA (Eddie Angel)
!Viva! VIVA (Re. Angel / D. Amis)
!Viva! VIVA Los Straitjackets
!Viva! VIVA Los Straitjackets


100 Days. 100 Nights 100 DAYS 100 NIGHTS (B. Mann / H. Steinweiss)
100 Days. 100 Nights 100 DAYS 100 NIGHTS (B. Mann)
100 Days. 100 Nights 100 DAYS 100 NIGHTS (H. Steinweiss)
100 Days. 100 Nights 100 DAYS 100 NIGHTS (J. Bignon)
100 Days. 100 Nights 100 DAYS 100 NIGHTS (N. Sugarman / S. Jones)
100 Days. 100 Nights 100 DAYS 100 NIGHTS (T. Brenneck / H. Steinweiss)
100 Days. 100 Nights 100 DAYS 100 NIGHTS Sharon Jones & The Dap-Kings
100 Days. 100 Nights 100 DAYS 100 NIGHTS Sharon Jones & The Dap-Kings

Dogberry2
2014-12-15, 05:18
Is there a flag that indicates which contributor is the album artist?
I don't have the schema in front of me, but I think there is a "role" field in the contributor_album table. Try adding another filter on the WHERE clause: "AND ca.role = 1".

JJZolx
2014-12-15, 05:35
Here are the contributor roles:

1 Artist
2 Composer
3 Conductor
4 Band
5 Album Artist
6 Track Artist

They may be a little difficult to use in a single query, however, unless you've tagged every album with an explicit album artist. Otherwise, the defacto album artist for an album will have a role of Artist. Then you also have to deal with having multiple artists (or album artists) for an album.

stereoptic
2014-12-15, 09:45
Thanks guys! AND CA.ROLE = 5 did the trick!

treefern
2014-12-27, 02:54
I would like to try your program but I have a problem in that I migrated my LMS database from WHS 2011 to Linux on QNAP NAS and I cannot get under the bonnet to get to /opt/ssods4/var/home/SqueezeboxServer/Cache/library.db.

I guess best option is to copy database to windows and run the program. Is there a way (like CMD in windows) to get to non network folders?

Regards

David McEwing

Dogberry2
2014-12-30, 13:32
I would like to try your program but I have a problem in that I migrated my LMS database from WHS 2011 to Linux on QNAP NAS and I cannot get under the bonnet to get to /opt/ssods4/var/home/SqueezeboxServer/Cache/library.db.

I guess best option is to copy database to windows and run the program. Is there a way (like CMD in windows) to get to non network folders?
I'm not familiar with QNAPs, but the way I handle it with my own NAS (which is running Linux) is to create a link in a network-accessible folder, pointing to the library.db file (in your case /opt/ssods4/var/home/SqueezeboxServer/Cache/library.db). That way I can map a drive in Windows pointing to the shared folder, and access the file directly that way.

So if you log in to your NAS you could do something like:

ln /opt/ssods4/var/home/SqueezeboxServer/Cache/library.db lsm-library.db

Then the "file" lsm-library.db would actually be a link to the LSM database file, and you could access it as such from a Windows drive mapped to the share where you created the link.

d6jg
2015-01-04, 15:13
I can confirm that should work on a QNAP

venus light
2015-02-08, 12:25
This looks like an awesome tool. What would be a good resource to discover how to write different SQL queries on this database?

I'm not super versed on SQL, but am pretty computer-savvy, so I think I could figure it out if someone pointed me in the right direction.

The pre-sets are great, but I'd like to do a little customization and don't want to bother folks in this thread to create new code just for me.


TIA

Dogberry2
2015-02-09, 14:15
This looks like an awesome tool. What would be a good resource to discover how to write different SQL queries on this database?

I'm not super versed on SQL, but am pretty computer-savvy, so I think I could figure it out if someone pointed me in the right direction.

The pre-sets are great, but I'd like to do a little customization and don't want to bother folks in this thread to create new code just for me.
TIA
Earlier in this thread get.amped posted some helpful snapshots of the primary tables of interest in the LMS database. Those should give you the table & field names for just about everything you're likely to be interested in. There is plenty of documentation for SQLite available online, to give you some guidance for putting queries together. With the presets and other code examples from this thread, that should be a good start.