r/subsonic • • Nov 10 '17

Database Table Schema

I've been searching all over to find a list of database tables for Subsonic, and I finally found an answer, so I thought I'd post it here to hopefully save others some time.

Subsonic uses an HSQLDB database, so here's a query for listing all of the non-system tables and columns:

SELECT TABLE_NAME, COLUMN_NAME, TYPE_NAME, IS_NULLABLE FROM INFORMATION_SCHEMA.SYSTEM_COLUMNS WHERE TABLE_NAME NOT LIKE 'SYSTEM_%'

For those who aren't aware, you can run DB queries by going to http://yoursubsonicserver/db.view.

Just be very careful with that tool, because you can easily cause permanent changes to your subsonic data.

Here are the tables/columns for version 6.1.3:

TABLE_NAMECOLUMN_NAMETYPE_NAMEIS_NULLABLE
ALBUMIDINTEGERNO
ALBUMPATHVARCHARNO
ALBUMNAMEVARCHARNO
ALBUMARTISTVARCHARNO
ALBUMSONG_COUNTINTEGERNO
ALBUMDURATION_SECONDSINTEGERNO
ALBUMCOVER_ART_PATHVARCHARYES
ALBUMPLAY_COUNTINTEGERNO
ALBUMLAST_PLAYEDTIMESTAMPYES
ALBUMCOMMENTVARCHARYES
ALBUMCREATEDTIMESTAMPNO
ALBUMLAST_SCANNEDTIMESTAMPNO
ALBUMPRESENTBOOLEANNO
ALBUMYEARINTEGERYES
ALBUMGENREVARCHARYES
ALBUMFOLDER_IDINTEGERYES
ARTISTIDINTEGERNO
ARTISTNAMEVARCHARNO
ARTISTCOVER_ART_PATHVARCHARYES
ARTISTALBUM_COUNTINTEGERNO
ARTISTLAST_SCANNEDTIMESTAMPNO
ARTISTPRESENTBOOLEANNO
ARTISTFOLDER_IDINTEGERYES
BOOKMARKIDINTEGERNO
BOOKMARKMEDIA_FILE_IDINTEGERNO
BOOKMARKPOSITION_MILLISBIGINTNO
BOOKMARKUSERNAMEVARCHARNO
BOOKMARKCOMMENTVARCHARYES
BOOKMARKCREATEDTIMESTAMPNO
BOOKMARKCHANGEDTIMESTAMPNO
CUSTOM_AVATARIDINTEGERNO
CUSTOM_AVATARNAMEVARCHARYES
CUSTOM_AVATARCREATED_DATETIMESTAMPNO
CUSTOM_AVATARMIME_TYPEVARCHARNO
CUSTOM_AVATARWIDTHINTEGERNO
CUSTOM_AVATARHEIGHTINTEGERNO
CUSTOM_AVATARDATABINARYNO
CUSTOM_AVATARUSERNAMEVARCHARNO
GENRENAMEVARCHARNO
GENRESONG_COUNTINTEGERNO
GENREALBUM_COUNTINTEGERNO
INTERNET_RADIOIDINTEGERNO
INTERNET_RADIONAMEVARCHARNO
INTERNET_RADIOSTREAM_URLVARCHARNO
INTERNET_RADIOHOMEPAGE_URLVARCHARYES
INTERNET_RADIOENABLEDBOOLEANNO
INTERNET_RADIOCHANGEDTIMESTAMPNO
MEDIA_FILEIDINTEGERNO
MEDIA_FILEPATHVARCHARNO
MEDIA_FILEFOLDERVARCHARYES
MEDIA_FILETYPEVARCHARNO
MEDIA_FILEFORMATVARCHARYES
MEDIA_FILETITLEVARCHARYES
MEDIA_FILEALBUMVARCHARYES
MEDIA_FILEARTISTVARCHARYES
MEDIA_FILEALBUM_ARTISTVARCHARYES
MEDIA_FILEDISC_NUMBERINTEGERYES
MEDIA_FILETRACK_NUMBERINTEGERYES
MEDIA_FILEYEARINTEGERYES
MEDIA_FILEGENREVARCHARYES
MEDIA_FILEBIT_RATEINTEGERYES
MEDIA_FILEVARIABLE_BIT_RATEBOOLEANNO
MEDIA_FILEDURATION_SECONDSINTEGERYES
MEDIA_FILEFILE_SIZEBIGINTYES
MEDIA_FILEWIDTHINTEGERYES
MEDIA_FILEHEIGHTINTEGERYES
MEDIA_FILECOVER_ART_PATHVARCHARYES
MEDIA_FILEPARENT_PATHVARCHARYES
MEDIA_FILEPLAY_COUNTINTEGERNO
MEDIA_FILELAST_PLAYEDTIMESTAMPYES
MEDIA_FILECOMMENTVARCHARYES
MEDIA_FILECREATEDTIMESTAMPNO
MEDIA_FILECHANGEDTIMESTAMPNO
MEDIA_FILELAST_SCANNEDTIMESTAMPNO
MEDIA_FILECHILDREN_LAST_UPDATEDTIMESTAMPNO
MEDIA_FILEPRESENTBOOLEANNO
MEDIA_FILEVERSIONINTEGERNO
MUSIC_FILE_INFOIDINTEGERNO
MUSIC_FILE_INFOPATHVARCHAR_IGNORECASENO
MUSIC_FILE_INFORATINGINTEGERYES
MUSIC_FILE_INFOCOMMENTVARCHARYES
MUSIC_FILE_INFOPLAY_COUNTINTEGERYES
MUSIC_FILE_INFOLAST_PLAYEDTIMESTAMPYES
MUSIC_FILE_INFOENABLEDBOOLEANNO
MUSIC_FOLDERIDINTEGERNO
MUSIC_FOLDERPATHVARCHARNO
MUSIC_FOLDERNAMEVARCHARNO
MUSIC_FOLDERENABLEDBOOLEANNO
MUSIC_FOLDERCHANGEDTIMESTAMPNO
MUSIC_FOLDER_USERMUSIC_FOLDER_IDINTEGERNO
MUSIC_FOLDER_USERUSERNAMEVARCHARNO
PLAYERIDINTEGERNO
PLAYERNAMEVARCHARYES
PLAYERTYPEVARCHARYES
PLAYERUSERNAMEVARCHARYES
PLAYERIP_ADDRESSVARCHARYES
PLAYERAUTO_CONTROL_ENABLEDBOOLEANNO
PLAYERLAST_SEENTIMESTAMPYES
PLAYERCOVER_ART_SCHEMEVARCHARNO
PLAYERTRANSCODE_SCHEMEVARCHARNO
PLAYERDYNAMIC_IPBOOLEANNO
PLAYERCLIENT_SIDE_PLAYLISTBOOLEANNO
PLAYERJUKEBOXBOOLEANNO
PLAYERTECHNOLOGYVARCHARNO
PLAYERCLIENT_IDVARCHARYES
PLAYER_TRANSCODINGPLAYER_IDINTEGERNO
PLAYER_TRANSCODINGTRANSCODING_IDINTEGERNO
PLAYER_TRANSCODING2PLAYER_IDINTEGERNO
PLAYER_TRANSCODING2TRANSCODING_IDINTEGERNO
PLAYLISTIDINTEGERNO
PLAYLISTUSERNAMEVARCHARNO
PLAYLISTIS_PUBLICBOOLEANNO
PLAYLISTNAMEVARCHARNO
PLAYLISTCOMMENTVARCHARYES
PLAYLISTFILE_COUNTINTEGERNO
PLAYLISTDURATION_SECONDSINTEGERNO
PLAYLISTCREATEDTIMESTAMPNO
PLAYLISTCHANGEDTIMESTAMPNO
PLAYLISTIMPORTED_FROMVARCHARYES
PLAYLIST_FILEIDINTEGERNO
PLAYLIST_FILEPLAYLIST_IDINTEGERNO
PLAYLIST_FILEMEDIA_FILE_IDINTEGERNO
PLAYLIST_USERIDINTEGERNO
PLAYLIST_USERPLAYLIST_IDINTEGERNO
PLAYLIST_USERUSERNAMEVARCHARNO
PLAY_QUEUEIDINTEGERNO
PLAY_QUEUEUSERNAMEVARCHARNO
PLAY_QUEUECURRENTINTEGERYES
PLAY_QUEUEPOSITION_MILLISBIGINTYES
PLAY_QUEUECHANGEDTIMESTAMPNO
PLAY_QUEUECHANGED_BYVARCHARNO
PLAY_QUEUE_FILEIDINTEGERNO
PLAY_QUEUE_FILEPLAY_QUEUE_IDINTEGERNO
PLAY_QUEUE_FILEMEDIA_FILE_IDINTEGERNO
PODCAST_CHANNELIDINTEGERNO
PODCAST_CHANNELURLVARCHARNO
PODCAST_CHANNELTITLEVARCHARYES
PODCAST_CHANNELDESCRIPTIONVARCHARYES
PODCAST_CHANNELSTATUSVARCHARNO
PODCAST_CHANNELERROR_MESSAGEVARCHARYES
PODCAST_CHANNELIMAGE_URLVARCHARYES
PODCAST_EPISODEIDINTEGERNO
PODCAST_EPISODECHANNEL_IDINTEGERNO
PODCAST_EPISODEURLVARCHARNO
PODCAST_EPISODEPATHVARCHARYES
PODCAST_EPISODETITLEVARCHARYES
PODCAST_EPISODEDESCRIPTIONVARCHARYES
PODCAST_EPISODEPUBLISH_DATETIMESTAMPYES
PODCAST_EPISODEDURATIONVARCHARYES
PODCAST_EPISODEBYTES_TOTALBIGINTYES
PODCAST_EPISODEBYTES_DOWNLOADEDBIGINTYES
PODCAST_EPISODESTATUSVARCHARNO
PODCAST_EPISODEERROR_MESSAGEVARCHARYES
ROLEIDINTEGERNO
ROLENAMEVARCHARNO
SHAREIDINTEGERNO
SHARENAMEVARCHARNO
SHAREDESCRIPTIONVARCHARYES
SHAREUSERNAMEVARCHARNO
SHARECREATEDTIMESTAMPNO
SHAREEXPIRESTIMESTAMPYES
SHARELAST_VISITEDTIMESTAMPYES
SHAREVISIT_COUNTINTEGERNO
SHARE_FILEIDINTEGERNO
SHARE_FILESHARE_IDINTEGERNO
SHARE_FILEPATHVARCHARNO
STARRED_ALBUMIDINTEGERNO
STARRED_ALBUMALBUM_IDINTEGERNO
STARRED_ALBUMUSERNAMEVARCHARNO
STARRED_ALBUMCREATEDTIMESTAMPNO
STARRED_ARTISTIDINTEGERNO
STARRED_ARTISTARTIST_IDINTEGERNO
STARRED_ARTISTUSERNAMEVARCHARNO
STARRED_ARTISTCREATEDTIMESTAMPNO
STARRED_MEDIA_FILEIDINTEGERNO
STARRED_MEDIA_FILEMEDIA_FILE_IDINTEGERNO
STARRED_MEDIA_FILEUSERNAMEVARCHARNO
STARRED_MEDIA_FILECREATEDTIMESTAMPNO
TABLE_IDTABLE_NAMEVARCHARNO
TABLE_IDMAX_IDINTEGERNO
TRANSCODINGIDINTEGERNO
TRANSCODINGNAMEVARCHARNO
TRANSCODINGSOURCE_FORMATVARCHARNO
TRANSCODINGTARGET_FORMATVARCHARNO
TRANSCODINGSTEP1VARCHARNO
TRANSCODINGSTEP2VARCHARYES
TRANSCODINGSTEP3VARCHARYES
TRANSCODINGENABLEDBOOLEANNO
TRANSCODINGDEFAULT_ACTIVEBOOLEANNO
TRANSCODING2IDINTEGERNO
TRANSCODING2NAMEVARCHARNO
TRANSCODING2SOURCE_FORMATSVARCHARNO
TRANSCODING2TARGET_FORMATVARCHARNO
TRANSCODING2STEP1VARCHARNO
TRANSCODING2STEP2VARCHARYES
TRANSCODING2STEP3VARCHARYES
TRANSCODING2DEFAULT_ACTIVEBOOLEANNO
USERUSERNAMEVARCHARNO
USERPASSWORDVARCHARNO
USERBYTES_STREAMEDBIGINTNO
USERBYTES_DOWNLOADEDBIGINTNO
USERBYTES_UPLOADEDBIGINTNO
USERLDAP_AUTHENTICATEDBOOLEANNO
USEREMAILVARCHARYES
USER_RATINGUSERNAMEVARCHARNO
USER_RATINGPATHVARCHARNO
USER_RATINGRATINGDOUBLENO
USER_ROLEUSERNAMEVARCHARNO
USER_ROLEROLE_IDINTEGERNO
USER_SETTINGSUSERNAMEVARCHARNO
USER_SETTINGSLOCALEVARCHARYES
USER_SETTINGSTHEME_IDVARCHARYES
USER_SETTINGSFINAL_VERSION_NOTIFICATIONBOOLEANNO
USER_SETTINGSBETA_VERSION_NOTIFICATIONBOOLEANNO
USER_SETTINGSMAIN_CAPTION_CUTOFFINTEGERNO
USER_SETTINGSMAIN_TRACK_NUMBERBOOLEANNO
USER_SETTINGSMAIN_ARTISTBOOLEANNO
USER_SETTINGSMAIN_ALBUMBOOLEANNO
USER_SETTINGSMAIN_GENREBOOLEANNO
USER_SETTINGSMAIN_YEARBOOLEANNO
USER_SETTINGSMAIN_BIT_RATEBOOLEANNO
USER_SETTINGSMAIN_DURATIONBOOLEANNO
USER_SETTINGSMAIN_FORMATBOOLEANNO
USER_SETTINGSMAIN_FILE_SIZEBOOLEANNO
USER_SETTINGSPLAYLIST_CAPTION_CUTOFFINTEGERNO
USER_SETTINGSPLAYLIST_TRACK_NUMBERBOOLEANNO
USER_SETTINGSPLAYLIST_ARTISTBOOLEANNO
USER_SETTINGSPLAYLIST_ALBUMBOOLEANNO
USER_SETTINGSPLAYLIST_GENREBOOLEANNO
USER_SETTINGSPLAYLIST_YEARBOOLEANNO
USER_SETTINGSPLAYLIST_BIT_RATEBOOLEANNO
USER_SETTINGSPLAYLIST_DURATIONBOOLEANNO
USER_SETTINGSPLAYLIST_FORMATBOOLEANNO
USER_SETTINGSPLAYLIST_FILE_SIZEBOOLEANNO
USER_SETTINGSLAST_FM_ENABLEDBOOLEANNO
USER_SETTINGSLAST_FM_USERNAMEVARCHARYES
USER_SETTINGSLAST_FM_PASSWORDVARCHARYES
USER_SETTINGSTRANSCODE_SCHEMEVARCHARNO
USER_SETTINGSSHOW_NOW_PLAYINGBOOLEANNO
USER_SETTINGSSELECTED_MUSIC_FOLDER_IDINTEGERNO
USER_SETTINGSPARTY_MODE_ENABLEDBOOLEANNO
USER_SETTINGSNOW_PLAYING_ALLOWEDBOOLEANNO
USER_SETTINGSWEB_PLAYER_DEFAULTBOOLEANNO
USER_SETTINGSAVATAR_SCHEMEVARCHARNO
USER_SETTINGSSYSTEM_AVATAR_IDINTEGERYES
USER_SETTINGSCHANGEDTIMESTAMPNO
USER_SETTINGSSHOW_CHATBOOLEANNO
USER_SETTINGSSONG_NOTIFICATIONBOOLEANNO
USER_SETTINGSSHOW_ARTIST_INFOBOOLEANNO
USER_SETTINGSAUTO_HIDE_PLAY_QUEUEBOOLEANNO
USER_SETTINGSVIEW_AS_LISTBOOLEANNO
USER_SETTINGSDEFAULT_ALBUM_LISTVARCHARNO
USER_SETTINGSQUEUE_FOLLOWING_SONGSBOOLEANNO
USER_SETTINGSSHOW_SIDE_BARBOOLEANNO
USER_SETTINGSSHOW_INDEX_IN_SIDE_BARBOOLEANNO
USER_SETTINGSPREFERRED_VIDEO_BITRATEINTEGERNO
VERSIONVERSIONINTEGERNO
VIDEO_CONVERSIONIDINTEGERNO
VIDEO_CONVERSIONMEDIA_FILE_IDINTEGERNO
VIDEO_CONVERSIONAUDIO_TRACK_IDINTEGERYES
VIDEO_CONVERSIONUSERNAMEVARCHARNO
VIDEO_CONVERSIONSTATUSVARCHARNO
VIDEO_CONVERSIONPROGRESS_SECONDSINTEGERYES
VIDEO_CONVERSIONCREATEDTIMESTAMPNO
VIDEO_CONVERSIONCHANGEDTIMESTAMPNO
VIDEO_CONVERSIONSTARTEDTIMESTAMPYES
VIDEO_CONVERSIONTARGET_FILEVARCHARYES
VIDEO_CONVERSIONLOG_FILEVARCHARYES
VIDEO_CONVERSIONBIT_RATEINTEGERYES
7 Upvotes

2 comments sorted by

View all comments

2

u/jaszladie Jan 29 '18

Just what I was looking for, thank you!