Possible to manually alter Subsonic's database?

[Spooky]

Hello, I was wondering whether it's possible to manually access Subsonic's database?

More specifically, I want to migrate an existing music database (from musikCube or iTunes for instance) into Subsonic.

[Spooky]

Hm, anyone?

[bushman4]

There is an interface for the DB, but not ODBC or anything else (that I have found).

http://Your.Subsonic.Address:Port/db.view

I suppose you could do some fancy scripting to turn a CSV or something else into a bunch of executable SQL strings and use the db.view page. But that might be tedious.

HTH,


Glenn

[Spooky]

Hm, but is it possible to alter the database via that view?

[bushman4]

Sure. Standard SQL commands.

Update
Insert Into
Select
Delete

etc...

Here are the available tables:

custom_avatar
internet_radio
music_file_info
Music_folder
Player
Player_transcoding
podcast_channel
podcast_episode
role
system_avatar
transcoding
user
user_rating
user_role
user_settings
version

HTH,

Glenn

[Spooky]

Ah ok, thx .

[trickydick]

Question 1.) Has anyone attempted this yet? or have build custom tables in the subsonic databse and have pages that can access those tables.

Question 2.) is there a way to pull the results from the db.view page into another page using a query string or something? For instance if I want a custom chat.jsp window (not database driven) to pull the email or username from the current user thats logged on and place it in a form or a login.

Question 3.) Does anyone know the connection string for subsonic? Or if the SA has a password or not?

[prutser]

In the later Subsonic release some tables are added. To see all tables available:
http://<your ip>:4040/db.view

A white box appears. Type there select * from information_schema.system_tables and hit the OK button. Now you will get a lot of output with the tables readable below to the right. In 4.7 it are:
ARTIST
CUSTOM_AVATAR
INTERNET_RADIO
MEDIA_FILE
MUSIC_FILE_INFO
MUSIC_FOLDER
PLAYER
PLAYER_TRANSCODING
PLAYER_TRANSCODING2
PLAYLIST
PLAYLIST_FILE
PLAYLIST_USER
PODCAST_CHANNEL
PODCAST_EPISODE
ROLE
SHARE
SHARE_FILE
STARRED_ALBUM
STARRED_ARTIST
STARRED_MEDIA_FILE
STATISTIC_USER
SYSTEM_AVATAR
TRANSCODING
TRANSCODING2
USER
USER_RATING
USER_ROLE
USER_SETTINGS
VERSION

I cleaned up my playlists, it would'nt delete by itself for one or the other reason. Showed them by typing select * from playlist, and later on deleting by delete from plylist where ID=<whatever ID you like to delete>

A well, it works ... In 4.8 hopefully to old playlist function back again

Kees

[StNickZA]

This is a great feature.

However, I'm having some trouble getting any results from MUSIC_FILE_INFO. When I do a select, I get no results returned, even if I limit to "TOP 10". Anyone had this problem before?

[mikes]

Subsonic uses HSQLDB.

[airjrdn]

I posted this in the 5.2 announcements thread, but didn't get any responses. I'm hoping to get my playlists back, or at least get a list of the songs that were in them so I can recreate them.

I updated to Subsonic 5.0.beta2 and also changed the location of my music and my playlists are now empty. As I understand it though, the playlists are in the database. Given that the location of the music changed, I had to fix that in Subsonic. Is there a way to get my playlists back or at least find out what songs were in them?

I'm able to query the Playlists table, and it shows me the playlists names, but each one says has a file_count of 0 and a duration_seconds of 0.

There's a db.backup folder under c:\subsonic. I stopped the service, renamed the db folder to 'new db', then made a copy of db.backup and renamed it to db. I then started the service and ran the query, but that's what I got (zeros for count and duration).

Any thoughts/ideas?

Edit - I checked for previous versions (thank you to Windows), and I had a version of the folder from 8/22/2014, but there was no difference in the query results. I think I updated to the beta around 9/1 or so.

[airjrdn]

I got them back. I'll explain what I did below, but first let me say that I'm not positive what caused them to go missing because there were two changes involved. I believe it was change #1, but I'm not sure.

Change #1 - I moved my music to a new location
Change #2 - I updated to Subsonic 5 Beta 2

---Before attempting this, make a copy of your c:\subsonic folder---

The basic steps to get them back were:
[list=]
[*]Stop the Subsonic service using the SysTray icon - wait for it to stop
[*]Make a copy of your c:\subsonic folder to somewhere safe (remember this, you'll need it later)
[*]Restore db.backup using Windows Previous Versions to a date where the playlists existed
[*]Delete the db folder
[*]Rename (the now restored) db.backup folder to db
[*]Start the Subsonic service
[*]Login to Subsonic and refresh the left side pane where the playlists and artists/folder names reside
[*]Click on each playlist then export it (this will save each one to a file)
[*]Close out Subsonic in your browser
[*]Stop the Subsonic service
[*]Copy db and db.backup from the copy you made in the beginning to c:\subsonic
[*] - If necessary open the exported Playlists in a text editor and correct the paths if you moved your files like I did
[*]Start the Subsonic service
[*]On the left pane, import each of the exported playlists
[*]Click Refresh on the left pane
[*]If you have empty playlists, go to http://Your.Subsonic.Address:Port/db.view and delete the invalid ones (possibly delete from Playlist where file_count = 0)
[*]Go back to http://Your.Subsonic.Address:Port/
[*]If necessary, click Refresh on the left pane
[/list]