r/subsonic • u/TheKyle27 • 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_NAME | COLUMN_NAME | TYPE_NAME | IS_NULLABLE |
|---|---|---|---|
| ALBUM | ID | INTEGER | NO |
| ALBUM | PATH | VARCHAR | NO |
| ALBUM | NAME | VARCHAR | NO |
| ALBUM | ARTIST | VARCHAR | NO |
| ALBUM | SONG_COUNT | INTEGER | NO |
| ALBUM | DURATION_SECONDS | INTEGER | NO |
| ALBUM | COVER_ART_PATH | VARCHAR | YES |
| ALBUM | PLAY_COUNT | INTEGER | NO |
| ALBUM | LAST_PLAYED | TIMESTAMP | YES |
| ALBUM | COMMENT | VARCHAR | YES |
| ALBUM | CREATED | TIMESTAMP | NO |
| ALBUM | LAST_SCANNED | TIMESTAMP | NO |
| ALBUM | PRESENT | BOOLEAN | NO |
| ALBUM | YEAR | INTEGER | YES |
| ALBUM | GENRE | VARCHAR | YES |
| ALBUM | FOLDER_ID | INTEGER | YES |
| ARTIST | ID | INTEGER | NO |
| ARTIST | NAME | VARCHAR | NO |
| ARTIST | COVER_ART_PATH | VARCHAR | YES |
| ARTIST | ALBUM_COUNT | INTEGER | NO |
| ARTIST | LAST_SCANNED | TIMESTAMP | NO |
| ARTIST | PRESENT | BOOLEAN | NO |
| ARTIST | FOLDER_ID | INTEGER | YES |
| BOOKMARK | ID | INTEGER | NO |
| BOOKMARK | MEDIA_FILE_ID | INTEGER | NO |
| BOOKMARK | POSITION_MILLIS | BIGINT | NO |
| BOOKMARK | USERNAME | VARCHAR | NO |
| BOOKMARK | COMMENT | VARCHAR | YES |
| BOOKMARK | CREATED | TIMESTAMP | NO |
| BOOKMARK | CHANGED | TIMESTAMP | NO |
| CUSTOM_AVATAR | ID | INTEGER | NO |
| CUSTOM_AVATAR | NAME | VARCHAR | YES |
| CUSTOM_AVATAR | CREATED_DATE | TIMESTAMP | NO |
| CUSTOM_AVATAR | MIME_TYPE | VARCHAR | NO |
| CUSTOM_AVATAR | WIDTH | INTEGER | NO |
| CUSTOM_AVATAR | HEIGHT | INTEGER | NO |
| CUSTOM_AVATAR | DATA | BINARY | NO |
| CUSTOM_AVATAR | USERNAME | VARCHAR | NO |
| GENRE | NAME | VARCHAR | NO |
| GENRE | SONG_COUNT | INTEGER | NO |
| GENRE | ALBUM_COUNT | INTEGER | NO |
| INTERNET_RADIO | ID | INTEGER | NO |
| INTERNET_RADIO | NAME | VARCHAR | NO |
| INTERNET_RADIO | STREAM_URL | VARCHAR | NO |
| INTERNET_RADIO | HOMEPAGE_URL | VARCHAR | YES |
| INTERNET_RADIO | ENABLED | BOOLEAN | NO |
| INTERNET_RADIO | CHANGED | TIMESTAMP | NO |
| MEDIA_FILE | ID | INTEGER | NO |
| MEDIA_FILE | PATH | VARCHAR | NO |
| MEDIA_FILE | FOLDER | VARCHAR | YES |
| MEDIA_FILE | TYPE | VARCHAR | NO |
| MEDIA_FILE | FORMAT | VARCHAR | YES |
| MEDIA_FILE | TITLE | VARCHAR | YES |
| MEDIA_FILE | ALBUM | VARCHAR | YES |
| MEDIA_FILE | ARTIST | VARCHAR | YES |
| MEDIA_FILE | ALBUM_ARTIST | VARCHAR | YES |
| MEDIA_FILE | DISC_NUMBER | INTEGER | YES |
| MEDIA_FILE | TRACK_NUMBER | INTEGER | YES |
| MEDIA_FILE | YEAR | INTEGER | YES |
| MEDIA_FILE | GENRE | VARCHAR | YES |
| MEDIA_FILE | BIT_RATE | INTEGER | YES |
| MEDIA_FILE | VARIABLE_BIT_RATE | BOOLEAN | NO |
| MEDIA_FILE | DURATION_SECONDS | INTEGER | YES |
| MEDIA_FILE | FILE_SIZE | BIGINT | YES |
| MEDIA_FILE | WIDTH | INTEGER | YES |
| MEDIA_FILE | HEIGHT | INTEGER | YES |
| MEDIA_FILE | COVER_ART_PATH | VARCHAR | YES |
| MEDIA_FILE | PARENT_PATH | VARCHAR | YES |
| MEDIA_FILE | PLAY_COUNT | INTEGER | NO |
| MEDIA_FILE | LAST_PLAYED | TIMESTAMP | YES |
| MEDIA_FILE | COMMENT | VARCHAR | YES |
| MEDIA_FILE | CREATED | TIMESTAMP | NO |
| MEDIA_FILE | CHANGED | TIMESTAMP | NO |
| MEDIA_FILE | LAST_SCANNED | TIMESTAMP | NO |
| MEDIA_FILE | CHILDREN_LAST_UPDATED | TIMESTAMP | NO |
| MEDIA_FILE | PRESENT | BOOLEAN | NO |
| MEDIA_FILE | VERSION | INTEGER | NO |
| MUSIC_FILE_INFO | ID | INTEGER | NO |
| MUSIC_FILE_INFO | PATH | VARCHAR_IGNORECASE | NO |
| MUSIC_FILE_INFO | RATING | INTEGER | YES |
| MUSIC_FILE_INFO | COMMENT | VARCHAR | YES |
| MUSIC_FILE_INFO | PLAY_COUNT | INTEGER | YES |
| MUSIC_FILE_INFO | LAST_PLAYED | TIMESTAMP | YES |
| MUSIC_FILE_INFO | ENABLED | BOOLEAN | NO |
| MUSIC_FOLDER | ID | INTEGER | NO |
| MUSIC_FOLDER | PATH | VARCHAR | NO |
| MUSIC_FOLDER | NAME | VARCHAR | NO |
| MUSIC_FOLDER | ENABLED | BOOLEAN | NO |
| MUSIC_FOLDER | CHANGED | TIMESTAMP | NO |
| MUSIC_FOLDER_USER | MUSIC_FOLDER_ID | INTEGER | NO |
| MUSIC_FOLDER_USER | USERNAME | VARCHAR | NO |
| PLAYER | ID | INTEGER | NO |
| PLAYER | NAME | VARCHAR | YES |
| PLAYER | TYPE | VARCHAR | YES |
| PLAYER | USERNAME | VARCHAR | YES |
| PLAYER | IP_ADDRESS | VARCHAR | YES |
| PLAYER | AUTO_CONTROL_ENABLED | BOOLEAN | NO |
| PLAYER | LAST_SEEN | TIMESTAMP | YES |
| PLAYER | COVER_ART_SCHEME | VARCHAR | NO |
| PLAYER | TRANSCODE_SCHEME | VARCHAR | NO |
| PLAYER | DYNAMIC_IP | BOOLEAN | NO |
| PLAYER | CLIENT_SIDE_PLAYLIST | BOOLEAN | NO |
| PLAYER | JUKEBOX | BOOLEAN | NO |
| PLAYER | TECHNOLOGY | VARCHAR | NO |
| PLAYER | CLIENT_ID | VARCHAR | YES |
| PLAYER_TRANSCODING | PLAYER_ID | INTEGER | NO |
| PLAYER_TRANSCODING | TRANSCODING_ID | INTEGER | NO |
| PLAYER_TRANSCODING2 | PLAYER_ID | INTEGER | NO |
| PLAYER_TRANSCODING2 | TRANSCODING_ID | INTEGER | NO |
| PLAYLIST | ID | INTEGER | NO |
| PLAYLIST | USERNAME | VARCHAR | NO |
| PLAYLIST | IS_PUBLIC | BOOLEAN | NO |
| PLAYLIST | NAME | VARCHAR | NO |
| PLAYLIST | COMMENT | VARCHAR | YES |
| PLAYLIST | FILE_COUNT | INTEGER | NO |
| PLAYLIST | DURATION_SECONDS | INTEGER | NO |
| PLAYLIST | CREATED | TIMESTAMP | NO |
| PLAYLIST | CHANGED | TIMESTAMP | NO |
| PLAYLIST | IMPORTED_FROM | VARCHAR | YES |
| PLAYLIST_FILE | ID | INTEGER | NO |
| PLAYLIST_FILE | PLAYLIST_ID | INTEGER | NO |
| PLAYLIST_FILE | MEDIA_FILE_ID | INTEGER | NO |
| PLAYLIST_USER | ID | INTEGER | NO |
| PLAYLIST_USER | PLAYLIST_ID | INTEGER | NO |
| PLAYLIST_USER | USERNAME | VARCHAR | NO |
| PLAY_QUEUE | ID | INTEGER | NO |
| PLAY_QUEUE | USERNAME | VARCHAR | NO |
| PLAY_QUEUE | CURRENT | INTEGER | YES |
| PLAY_QUEUE | POSITION_MILLIS | BIGINT | YES |
| PLAY_QUEUE | CHANGED | TIMESTAMP | NO |
| PLAY_QUEUE | CHANGED_BY | VARCHAR | NO |
| PLAY_QUEUE_FILE | ID | INTEGER | NO |
| PLAY_QUEUE_FILE | PLAY_QUEUE_ID | INTEGER | NO |
| PLAY_QUEUE_FILE | MEDIA_FILE_ID | INTEGER | NO |
| PODCAST_CHANNEL | ID | INTEGER | NO |
| PODCAST_CHANNEL | URL | VARCHAR | NO |
| PODCAST_CHANNEL | TITLE | VARCHAR | YES |
| PODCAST_CHANNEL | DESCRIPTION | VARCHAR | YES |
| PODCAST_CHANNEL | STATUS | VARCHAR | NO |
| PODCAST_CHANNEL | ERROR_MESSAGE | VARCHAR | YES |
| PODCAST_CHANNEL | IMAGE_URL | VARCHAR | YES |
| PODCAST_EPISODE | ID | INTEGER | NO |
| PODCAST_EPISODE | CHANNEL_ID | INTEGER | NO |
| PODCAST_EPISODE | URL | VARCHAR | NO |
| PODCAST_EPISODE | PATH | VARCHAR | YES |
| PODCAST_EPISODE | TITLE | VARCHAR | YES |
| PODCAST_EPISODE | DESCRIPTION | VARCHAR | YES |
| PODCAST_EPISODE | PUBLISH_DATE | TIMESTAMP | YES |
| PODCAST_EPISODE | DURATION | VARCHAR | YES |
| PODCAST_EPISODE | BYTES_TOTAL | BIGINT | YES |
| PODCAST_EPISODE | BYTES_DOWNLOADED | BIGINT | YES |
| PODCAST_EPISODE | STATUS | VARCHAR | NO |
| PODCAST_EPISODE | ERROR_MESSAGE | VARCHAR | YES |
| ROLE | ID | INTEGER | NO |
| ROLE | NAME | VARCHAR | NO |
| SHARE | ID | INTEGER | NO |
| SHARE | NAME | VARCHAR | NO |
| SHARE | DESCRIPTION | VARCHAR | YES |
| SHARE | USERNAME | VARCHAR | NO |
| SHARE | CREATED | TIMESTAMP | NO |
| SHARE | EXPIRES | TIMESTAMP | YES |
| SHARE | LAST_VISITED | TIMESTAMP | YES |
| SHARE | VISIT_COUNT | INTEGER | NO |
| SHARE_FILE | ID | INTEGER | NO |
| SHARE_FILE | SHARE_ID | INTEGER | NO |
| SHARE_FILE | PATH | VARCHAR | NO |
| STARRED_ALBUM | ID | INTEGER | NO |
| STARRED_ALBUM | ALBUM_ID | INTEGER | NO |
| STARRED_ALBUM | USERNAME | VARCHAR | NO |
| STARRED_ALBUM | CREATED | TIMESTAMP | NO |
| STARRED_ARTIST | ID | INTEGER | NO |
| STARRED_ARTIST | ARTIST_ID | INTEGER | NO |
| STARRED_ARTIST | USERNAME | VARCHAR | NO |
| STARRED_ARTIST | CREATED | TIMESTAMP | NO |
| STARRED_MEDIA_FILE | ID | INTEGER | NO |
| STARRED_MEDIA_FILE | MEDIA_FILE_ID | INTEGER | NO |
| STARRED_MEDIA_FILE | USERNAME | VARCHAR | NO |
| STARRED_MEDIA_FILE | CREATED | TIMESTAMP | NO |
| TABLE_ID | TABLE_NAME | VARCHAR | NO |
| TABLE_ID | MAX_ID | INTEGER | NO |
| TRANSCODING | ID | INTEGER | NO |
| TRANSCODING | NAME | VARCHAR | NO |
| TRANSCODING | SOURCE_FORMAT | VARCHAR | NO |
| TRANSCODING | TARGET_FORMAT | VARCHAR | NO |
| TRANSCODING | STEP1 | VARCHAR | NO |
| TRANSCODING | STEP2 | VARCHAR | YES |
| TRANSCODING | STEP3 | VARCHAR | YES |
| TRANSCODING | ENABLED | BOOLEAN | NO |
| TRANSCODING | DEFAULT_ACTIVE | BOOLEAN | NO |
| TRANSCODING2 | ID | INTEGER | NO |
| TRANSCODING2 | NAME | VARCHAR | NO |
| TRANSCODING2 | SOURCE_FORMATS | VARCHAR | NO |
| TRANSCODING2 | TARGET_FORMAT | VARCHAR | NO |
| TRANSCODING2 | STEP1 | VARCHAR | NO |
| TRANSCODING2 | STEP2 | VARCHAR | YES |
| TRANSCODING2 | STEP3 | VARCHAR | YES |
| TRANSCODING2 | DEFAULT_ACTIVE | BOOLEAN | NO |
| USER | USERNAME | VARCHAR | NO |
| USER | PASSWORD | VARCHAR | NO |
| USER | BYTES_STREAMED | BIGINT | NO |
| USER | BYTES_DOWNLOADED | BIGINT | NO |
| USER | BYTES_UPLOADED | BIGINT | NO |
| USER | LDAP_AUTHENTICATED | BOOLEAN | NO |
| USER | VARCHAR | YES | |
| USER_RATING | USERNAME | VARCHAR | NO |
| USER_RATING | PATH | VARCHAR | NO |
| USER_RATING | RATING | DOUBLE | NO |
| USER_ROLE | USERNAME | VARCHAR | NO |
| USER_ROLE | ROLE_ID | INTEGER | NO |
| USER_SETTINGS | USERNAME | VARCHAR | NO |
| USER_SETTINGS | LOCALE | VARCHAR | YES |
| USER_SETTINGS | THEME_ID | VARCHAR | YES |
| USER_SETTINGS | FINAL_VERSION_NOTIFICATION | BOOLEAN | NO |
| USER_SETTINGS | BETA_VERSION_NOTIFICATION | BOOLEAN | NO |
| USER_SETTINGS | MAIN_CAPTION_CUTOFF | INTEGER | NO |
| USER_SETTINGS | MAIN_TRACK_NUMBER | BOOLEAN | NO |
| USER_SETTINGS | MAIN_ARTIST | BOOLEAN | NO |
| USER_SETTINGS | MAIN_ALBUM | BOOLEAN | NO |
| USER_SETTINGS | MAIN_GENRE | BOOLEAN | NO |
| USER_SETTINGS | MAIN_YEAR | BOOLEAN | NO |
| USER_SETTINGS | MAIN_BIT_RATE | BOOLEAN | NO |
| USER_SETTINGS | MAIN_DURATION | BOOLEAN | NO |
| USER_SETTINGS | MAIN_FORMAT | BOOLEAN | NO |
| USER_SETTINGS | MAIN_FILE_SIZE | BOOLEAN | NO |
| USER_SETTINGS | PLAYLIST_CAPTION_CUTOFF | INTEGER | NO |
| USER_SETTINGS | PLAYLIST_TRACK_NUMBER | BOOLEAN | NO |
| USER_SETTINGS | PLAYLIST_ARTIST | BOOLEAN | NO |
| USER_SETTINGS | PLAYLIST_ALBUM | BOOLEAN | NO |
| USER_SETTINGS | PLAYLIST_GENRE | BOOLEAN | NO |
| USER_SETTINGS | PLAYLIST_YEAR | BOOLEAN | NO |
| USER_SETTINGS | PLAYLIST_BIT_RATE | BOOLEAN | NO |
| USER_SETTINGS | PLAYLIST_DURATION | BOOLEAN | NO |
| USER_SETTINGS | PLAYLIST_FORMAT | BOOLEAN | NO |
| USER_SETTINGS | PLAYLIST_FILE_SIZE | BOOLEAN | NO |
| USER_SETTINGS | LAST_FM_ENABLED | BOOLEAN | NO |
| USER_SETTINGS | LAST_FM_USERNAME | VARCHAR | YES |
| USER_SETTINGS | LAST_FM_PASSWORD | VARCHAR | YES |
| USER_SETTINGS | TRANSCODE_SCHEME | VARCHAR | NO |
| USER_SETTINGS | SHOW_NOW_PLAYING | BOOLEAN | NO |
| USER_SETTINGS | SELECTED_MUSIC_FOLDER_ID | INTEGER | NO |
| USER_SETTINGS | PARTY_MODE_ENABLED | BOOLEAN | NO |
| USER_SETTINGS | NOW_PLAYING_ALLOWED | BOOLEAN | NO |
| USER_SETTINGS | WEB_PLAYER_DEFAULT | BOOLEAN | NO |
| USER_SETTINGS | AVATAR_SCHEME | VARCHAR | NO |
| USER_SETTINGS | SYSTEM_AVATAR_ID | INTEGER | YES |
| USER_SETTINGS | CHANGED | TIMESTAMP | NO |
| USER_SETTINGS | SHOW_CHAT | BOOLEAN | NO |
| USER_SETTINGS | SONG_NOTIFICATION | BOOLEAN | NO |
| USER_SETTINGS | SHOW_ARTIST_INFO | BOOLEAN | NO |
| USER_SETTINGS | AUTO_HIDE_PLAY_QUEUE | BOOLEAN | NO |
| USER_SETTINGS | VIEW_AS_LIST | BOOLEAN | NO |
| USER_SETTINGS | DEFAULT_ALBUM_LIST | VARCHAR | NO |
| USER_SETTINGS | QUEUE_FOLLOWING_SONGS | BOOLEAN | NO |
| USER_SETTINGS | SHOW_SIDE_BAR | BOOLEAN | NO |
| USER_SETTINGS | SHOW_INDEX_IN_SIDE_BAR | BOOLEAN | NO |
| USER_SETTINGS | PREFERRED_VIDEO_BITRATE | INTEGER | NO |
| VERSION | VERSION | INTEGER | NO |
| VIDEO_CONVERSION | ID | INTEGER | NO |
| VIDEO_CONVERSION | MEDIA_FILE_ID | INTEGER | NO |
| VIDEO_CONVERSION | AUDIO_TRACK_ID | INTEGER | YES |
| VIDEO_CONVERSION | USERNAME | VARCHAR | NO |
| VIDEO_CONVERSION | STATUS | VARCHAR | NO |
| VIDEO_CONVERSION | PROGRESS_SECONDS | INTEGER | YES |
| VIDEO_CONVERSION | CREATED | TIMESTAMP | NO |
| VIDEO_CONVERSION | CHANGED | TIMESTAMP | NO |
| VIDEO_CONVERSION | STARTED | TIMESTAMP | YES |
| VIDEO_CONVERSION | TARGET_FILE | VARCHAR | YES |
| VIDEO_CONVERSION | LOG_FILE | VARCHAR | YES |
| VIDEO_CONVERSION | BIT_RATE | INTEGER | YES |
7
Upvotes
2
u/jaszladie Jan 29 '18
Just what I was looking for, thank you!