PDA

View Full Version : SQLite Browser for Windows



w3wilkes
2013-09-20, 10:56
I was looking for a GUI SQLite browser to view the library.db file. I tried SQLiteman and it says the file isn't a recognized database. I also tried SQLite Browser and it just presents a blank view. Anybody know of a GUI tool to browse the library.db table?

JohnB
2013-09-20, 11:40
Firstly I suggest that you copy the library.db to a different folder and then use the copy of library.db instead of the file in use by LMS (it avoids the risk of inadvertently changing the database)

I don't use GUI but use a batch file to extract the tables which I then import into Access (or Excel). If you are interested I can let you have the details.

However, I just googled and there is a plugin for Firefox called "SQLite Manager" which seems to do what you want.

The documentation for the plugin is here: https://code.google.com/p/sqlite-manager/w/list

In Windows 7 Firefox the plugin lurks in the "Web Developer" menu.

IMO it would be much easier to make sense of the tables with a 'clean' database, after doing a "Clear everything and rescan".

w3wilkes
2013-09-20, 11:51
Thanks! I would be interested in your batch file. I'll also give the Firefox thing a shot, I'll have to get Firefox installed too. I do have Access and Excel so I can import to either. On the Clear and rescan, I seem to always do this when I add music or do tagging work. On my ~20,000 track library it only takes 5 minutes so it's not a big deal.

aubuti
2013-09-20, 12:13
Many years ago -- during the slimserver 6.x days, before Squeezebox's temporary detour into MySQL land -- I used SQLite Database Browser (http://sourceforge.net/projects/sqlitebrowser/) successfully to examine the database. It was very easy to use.

w3wilkes
2013-09-20, 13:37
@aubuti, I did try that and even though it doesn't give a error when I attempt to open library.db it shows nothing in the database structure tab and the browse data tab shows 0 records. library.db that it's a little over 26MB.

JJZolx
2013-09-20, 21:02
I've been using SQLite Expert (http://www.sqliteexpert.com/) for a couple of years with good results. The Personal Edition is free.

w3wilkes
2013-09-20, 22:49
@JJZolx, THANKS! Exactly what I was looking for! This works perfectly. Now the big question in the Tracks table is... Since it has a BPM column, why isn't it populated if it exists in the TBPM tag of MP3 files? I was thinking this would be a very useful filter if you were building a SQL/Dynamic playlist for a walking on the treadmill pace.

JohnB
2013-09-21, 10:30
w3wilkes,

You expressed interest in how I use sqlite together with batch files to extract the tables (which I then import into Access). You might no longer be interested after JJZolx's recommendation [thanks from me too, JJZolx] but, as it would be a bit too long winded to post here so I have sent you a pm.

w3wilkes
2013-09-21, 12:30
JohnB,
Thanks. I did grab your zip file just to look through it, I may find pieces useful. And you're correct that with the JJZolx reply I no longer needed it. Thanks again.
Wilkes

JJZolx
2013-09-21, 14:33
I took a look at some of the code and it appears that TBPM is recognized. What kind of values do you have in these tags? I came across a check in the code that makes sure the value is numeric. I believe the following requires that the value be a plain non-zero integer.

So something like "120.0" or "120 BPM" would be rejected.



# Don't insert non-numeric or '0' YEAR fields into the database. Bug: 2610
# Same for DISC - Bug 2821
for my $tag (qw(YEAR DISC DISCC BPM CHANNELS)) {
if (
defined $attributes->{$tag}
&&
( $attributes->{$tag} !~ /^\d+$/ || $attributes->{$tag} == 0 )
) {
delete $attributes->{$tag};
}
}


(This shows the field named BPM, but there's code elsewhere that maps the ID3v2 TBPM frame to LMS' BPM.)

w3wilkes
2013-09-21, 20:25
Yes, I do have TBPM populated using MixMeister BPM Analyzer. My son is a DJ (does mixes, dances and parties) and got me into the habit of adding BPM to all my CD rips. He tells me that MixMeister is about as good a BPM analyzer as you can get and it's free.
http://www.mixmeister.com/download-bpmanalyzer.php
It does do numbers (like "133.56"). Looks like all TBPM values are taken to 2 decimal points and there's no ability to tell it to round to the nearest whole number.

(Hope you're not affected by all the flooding around Boulder!)

w3wilkes
2013-09-23, 11:04
Interesting... I went out to the MP3Tag forum to see about rounding the TBPM to the nearest integer and believe it or not this is the most recent topic!
http://forums.mp3tag.de/index.php?showtopic=17760
In that thread there's a link to a older thread that has action code from "Moonbase" that seems to work flawlessly to do this rounding. If anyone is interested here's the link to that;
http://forums.mp3tag.de/index.php?showtopic=8295
I originally was going to just use the function to trim the last 3 characters of TBPM (the .nn part), but thought I'd look to see if someone had "invented" a $round function since it's not a native MP3Tag action code function and found that there is! This is a way better approach, if you did the trim method twice you destroy the field. You can run the round function as much as you want and if the TBPM has already been "rounded" it stays the same. Be aware that if TBPM is null it will be set to "0", you could always add a last function test to reset to null if TBPM is "0".

w3wilkes
2013-09-23, 14:29
I tested "MoonBase" action in MP3Tag against a couple of albums and then did a clear and full rescan. With the BPM in the tags now being full integers with no decimal places it populates in the SQ database just fine! So now it's time to go to work on the SQL playlist that will filter on Genre and BPM.

w3wilkes
2013-09-23, 19:35
This should be my last entry on this... Using the SQL Playlist plugin I created a "Advanced" Playlist with Genre="Rock" and Year <=1990 and Year >=1970 to get a template. Then I edited the SQL to change the year criteria to bpm values...

-- PlaylistName:TestBPM
-- PlaylistGroups:
select tracks.url from tracks
left join dynamicplaylist_history on
tracks.id=dynamicplaylist_history.id and dynamicplaylist_history.client='PlaylistPlayer'
join genre_track on
tracks.id=genre_track.track
join genres on
genres.id=genre_track.genre and
genres.name='Rock'

where
tracks.audio=1
and dynamicplaylist_history.id is null
and tracks.bpm >= 119
and tracks.bpm <= 121

group by tracks.id
order by random()
limit 10;

I knew that with the BPM test data I'd added to the database that there were 3 38 Special songs that would match up. This works GREAT! You could easily add criteria to get further limits like adding year stuff to the end of the "where" section to limit this to the 60's Rock (for those of us that are old and have a bunch of the old 60's rock & roll).

and tracks.year >= 1960
and tracks.year <= 1969

Hopefully some of you may find all this info useful, Enjoy.

w3wilkes
2013-09-23, 20:27
And one last thing...

Thanks to all those who replied, especially;
JJZolx for the SQLite Expert info for DB browsing and providing the info on what the BPM column had to contain.
and
Erland for the great SQL and Dynamic Playlist plugins that give me the power to put my idea together.

Moonbase
2014-04-21, 15:46
After so many years, it's great to see that my very old code (for MP3Tag) is still being used and still works!

Sorry for waking the old thread, but I ran across this thread and just had to say thanks for checking it out and using it :-)

w3wilkes
2014-04-22, 08:07
@moonbase, Thank you for creating that code for mp3tag!