Home of the Squeezebox™ & Transporter® network music players.
Results 1 to 8 of 8
  1. #1
    Member
    Join Date
    Apr 2006
    Location
    lower upper NY
    Posts
    42

    Understanding the DB

    Let me see if I have this right.

    What I think I read ...
    The folder on my PC where I've ripped and tagged all my CDs is, in effect, my Music Database.
    Squeezebox Server knows how to access the tags in this DB and supplies me with some data access ability but predominantly playlist manipulation.
    Some kind soul named Erland wrote a plugin for Squeezebox Server that allows me to access the Music Database to extract information such as - all occurrences of doodahday in the Comment field -.
    The data is then extracted to somewhere in CSV format or an XML file for me to do with as I wish.

    My Questions ...
    The data in Music Database must have to be converted into a relational database before queries are run against it (I suppose). Is that what Squeezebox Server does when it scans Music Database?

    If so, where does it stash this new database and what is it called? I read somewhere in the Help file that older versions of Slimserver created a database named slimserversql.db. What does Squeezebox do?

    If it does create a relational DB somewhere, can it be accessed using a generic SQL program (whatever THAT may be)?



    It's been a long long time since I was a programmer (even before SQL), so please excuse the ignorance.
    Thanks
    EdP

  2. #2
    Senior Member
    Join Date
    Apr 2005
    Location
    Buckinghamshire, England
    Posts
    9,983
    squeezebox.db is the name of the relational database that is created when you perform a music scan
    You want to see the signal path BEFORE it gets onto a CD/vinyl...it ain't what you'd call minimal...
    Touch(wired/W7)+Teddy Pardo PSU - Audiolense 3.3/2.0+INGUZ DRC - MF M1 DAC - Linn 5103 - full Aktiv 5.1 system (6x LK140's, ESPEK/TRIKAN/KATAN/SEIZMIK 10.5), Pekin Tuner, Townsend Supertweeters,VdH Toslink,Kimber 8TC Speaker & Chord Signature Plus Interconnect cables
    Stax4070+SRM7/II phones
    Kitchen Boom, Outdoors: SB Radio, Harmony One remote for everything.

  3. #3
    Senior Member erland's Avatar
    Join Date
    Dec 2005
    Location
    Sweden
    Posts
    10,893
    Quote Originally Posted by EdPell View Post
    My Questions ...
    The data in Music Database must have to be converted into a relational database before queries are run against it (I suppose). Is that what Squeezebox Server does when it scans Music Database?
    Yes, the Squeezebox Server scanning process reads the tags in the music files and fills a relational database with the read data. The relational database is used in all browse menus later on to for example get all albums for a specific artist.

    Quote Originally Posted by EdPell View Post
    If so, where does it stash this new database and what is it called? I read somewhere in the Help file that older versions of Slimserver created a database named slimserversql.db. What does Squeezebox do?
    In all Squeezebox Server releases between 6.5 and 7.5.* a MySQL database is used, it stored in the "MySQL" directory inside the cache directory. You will find the location of the cache directory by looking in Settings/Information in the Squeezebox Server web interface. It's shown as the parameter "Cache Folder" in this page.

    In Squeezebox Server 7.6 and later a SQLite relational database will be used instead, it's stored as a squeezebox.db and squeezebox-persistent.db file inside the cache directory. The 7.6 release is only available as beta through nightly builds so far. The built-in server on the Squeezebox Touch also uses the SQLite database.

    Quote Originally Posted by EdPell View Post
    If it does create a relational DB somewhere, can it be accessed using a generic SQL program (whatever THAT may be)?
    Yes, for the MySQL database you need a SQL client tool, for example SQLyog, and the just connect to port 9092 (instead of the default 3306) and connect to the database called "slimserver". On some operating systems, it might use the standard 3306 port if MySQL is installed as a service.

    An alternative to use an external SQL tool is to install the Database Query plugin which makes it possible to run custom SELECT SQL statements through the Squeezebox Server web interface. Just go to Extras/Database Query and select to create a "Free form query" and enter your SQL statement.

    There are some kind of description of the database tables in the following wiki page:
    http://wiki.slimdevices.com/index.ph...abaseStructure

    There is a graphical view of the database structure available as a forum post somewhere, but I can't find that at the moment.

    Some plugins, adds a number of additional tables to the database. I know about at least the following cases:
    - TrackStat: to handle statistics
    - Custom Scan: to handle custom tags
    - Multi Library: to handle multiple libraries
    - Dynamic Playlist: to handle dynamic playlists

    The data stored by the above plugin is used by at least the following plugins:
    - Custom Browse: To create new browse menus based on SQL statements
    - SQL Playlist: To create smart playlists based on SQL statements
    Erland Isaksson (My homepage)
    (Developer of many plugins/applets (both free and commercial).
    If you like to encourage future presence on this forum and/or third party plugin/applet development, consider purchasing some plugins)

    Interested in the future of music streaming ? ickStream - A world of music at your fingertips.

  4. #4
    Member
    Join Date
    Apr 2006
    Location
    lower upper NY
    Posts
    42
    Erland ...

    Thank you kindly for that wealth of information and for answering unasked but obvious follow-up questions.

    I have a little more reading and testing to do now.

    EdP

  5. #5
    I have tried connecting to the MYSQL slimserver DB using MYSQL Query Browser using the following parameters:-

    Server Host name = localhost
    Port 9092
    Username slimserver
    Password left blank

    and get the following error message:-

    Could not connect to host 'localhost'.
    MySQL Error Nr. 1045
    Access denied for user 'slimserver'@'localhost' (using password: NO)

    During installing the squeezeboxserver 7.5.1 I was repeated told not to set a password for MYSQL so didn't!

    Any ideas why I can't connect please? Music is playing fine so it's not an urgent issue but I would like to be able to get at the database to feed into some php programs.

  6. #6
    Senior Member erland's Avatar
    Join Date
    Dec 2005
    Location
    Sweden
    Posts
    10,893
    Quote Originally Posted by JerryS View Post
    I have tried connecting to the MYSQL slimserver DB using MYSQL Query Browser using the following parameters:-

    Server Host name = localhost
    Port 9092
    Username slimserver
    Password left blank

    and get the following error message:-

    Could not connect to host 'localhost'.
    MySQL Error Nr. 1045
    Access denied for user 'slimserver'@'localhost' (using password: NO)

    During installing the squeezeboxserver 7.5.1 I was repeated told not to set a password for MYSQL so didn't!

    Any ideas why I can't connect please? Music is playing fine so it's not an urgent issue but I would like to be able to get at the database to feed into some php programs.
    Try if it works better to specify host as 127.0.0.1, this has helped on some of my installations.
    Erland Isaksson (My homepage)
    (Developer of many plugins/applets (both free and commercial).
    If you like to encourage future presence on this forum and/or third party plugin/applet development, consider purchasing some plugins)

    Interested in the future of music streaming ? ickStream - A world of music at your fingertips.

  7. #7
    Senior Member
    Join Date
    Apr 2005
    Location
    Colorado
    Posts
    10,978
    Quote Originally Posted by JerryS View Post
    I have tried connecting to the MYSQL slimserver DB using MYSQL Query Browser using the following parameters:-

    Server Host name = localhost
    Port 9092
    Username slimserver
    Password left blank

    and get the following error message:-

    Could not connect to host 'localhost'.
    MySQL Error Nr. 1045
    Access denied for user 'slimserver'@'localhost' (using password: NO)

    During installing the squeezeboxserver 7.5.1 I was repeated told not to set a password for MYSQL so didn't!

    Any ideas why I can't connect please? Music is playing fine so it's not an urgent issue but I would like to be able to get at the database to feed into some php programs.
    The MySQL server run by Squeezebox Server on port 9092 can't be connected to from another machine until you change a startup parameter telling it to listen on all IP addresses. Until you do this it can only be connected to from the same machine.

    To change this behavior find the Squeezebox Server installation directory, then the /server/MySQL directory and the my.tt file. Edit the my.tt file, and either remove the following line or place a # sign at the beginning of the line to comment it out:

    Code:
    bind-address          = [% bindAddress %]
    Then restart Squeezebox Server. If you still can't connect it means that the SbS instance of MySQL didn't restart. If you can't figure out another means of restarting it, reboot the machine.

  8. #8
    Thanks erland, 127.0.0.1 works fine!

    Thanks also Jim, I should have said I am on the same machine as the server but your explanation was useful since I had already come across the bind fix and didn't understand the purpose which I do now.

    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •