The purpose of this new thread is to focus the discussion, garner support, and tweak the outlined draft proposal for the next iteration of XBMC database support.
I'll keep this first post as the WIP until the majority are content. This first post may be better facilitated by a wiki page but I'll leave that decision to the XBMC team.
Proposals are inline, in BLUE. These proposals are designed to become part of the official documentation as appropriate.
Ideally it would be great to get input from as many as possible to ensure we get it right first time round and that it is scalable from the word go.
Background
The current implementation utilises a separate database for each of Video, Music and Program information. Each database schema shares some similarities in structure and naming convention but do not conform to a strict standard.
A visual representation of each schema is provided in the attachments.
The database backend implementation is based on SQLite3 (SQLite version 3). SQLite[1] is an embedded SQL database engine, in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.
Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file.
Moving Forward
The aim of this document is to standardise the design and structure of the database to facilitate the future scaling of XBMC in both a standalone configuration and when networked.
The current SQLite implementation stores the database information locally only. Each user profile has there own databases stored in userdata://Database
This works well for single XBMC setups, however, it does not scale well with multiple XBMC setups that utilise a single media repository. In the latter case each XBMC instance requires a dedicated database to store and retrieve information. Currently, to have the same information across all instances requires the databases to be manually replicated.
A solution to this problem is for XBMC to communicate to a variety of database backends; locally and remotely.
Database Servers
The additional database backends to be supported initially include the existing SQLite3 as well as MySQL[2] and PostgreSQL[3].
The inclusion of MySQL and PostgreSQL will allow for remote database connections that facilitate information storage and retrieval from a common database to multiple XBMC instances.
Data types
Due to the current SQLite implementation the data types are already suitably constrained to be well supported across multiple database backends. In SQLite the data type of a value is associated with the value itself, not with the container in which it is stored. Fortunately The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statement that work on statically typed databases should would the same way in SQLite.
The data types referenced in the existing schema are:
- INTEGER
- TEXT
- BOOLEAN
- DOUBLE
- FLOAT
Proposed: The data types DOUBLE and FLOAT are to be consolidated to the use of FLOAT only.
Schema Naming
The current naming of columns appears to follow a standard for the majority but a number of fields are clearly not conforming.
Proposed: All table names are to utilise CamelCase with the initial identifier representing the data type of that column with the exception of Primary Key and Foreign Key identifiers which are always to be of type INTEGER. The identifiers for the data types are:
- INTEGER (i)
- TEXT (s)
- BOOLEAN (b)
- FLOAT (f)
- PRIMARY KEY / FOREIGN KEY (id)
Examples of the usage are:
- idArtist - Integer representing a Primary/Foreign Key for each unique artist
- sArtist - String representing the Artist’s name
- iTrack - Integer representing the track number of a song on an album
- bSubtitlesOn - Boolean representing whether subtitles should be displayed
- fAudioDelay - Float representing the delay of audio stream relative to the video stream.
Map table names do not conform to any particular standard, for example in the Music database the table “exgenrealbum” maps the “genre” table to the “album” table, whilst the table “extragenresong” maps the “genre” table to the “song” table. In the Video database we have an “actorlinkmovie” table which maps the “actor” table to the “movie” table.
Proposed: Mapping tables are to be named “map_primarytable_secondarytable”. The previous tables would then be named as “map_genre_album”, “map_genre_song” and “map_actor_movie” respectively.
Indexs are to be prefixed with “idx_”.
Views are to be prefixed with “vw_”.
Database Joining
The current XBMC database implementation separates the Music, Video and Program information into separate databases. This does not easily support the ability to link a music video (from the Video database) to an artist (from the Music database).
Proposed: The Video, Music, and possible the Program, databases to be merged into one Media database. The existing conflicts of table names are to be addressed with a “music_”, “video_” or “program_” prefix were appropriate. The version table will be consolidated to a single table.
Database Settings
Proposed: The advancedsettings.xml will need to be modified to specify the database backend you wish to communicate with. The default will be the existing SQLite3 implementation.
The available tags for configuring the database are:
Code:
<database>
<type></type>
<name></name>
<host></host>
<port></port>
<user></user>
<pass></pass>
</database>The available tags are described as follows:
- Type is a string containing one of the following key words: “sqlite3”, “mysql”, or “postgresql”.
- Name is a string that represents the database name. For SQLite3 this is the name of the file.
- Host is a string that represents the network name, network IP address or for MySQL and PostgreSQL configurations or the directory path for SQLite3 configurations.
- Port is an integer that represents the listening port of the database backend. This is ignored for SQLite3 configurations.
- User is a string represents the user name for access to the database. This is ignored for SQLite3 configurations.
- Pass is a string represents the password associated with the aforementioned user name for access to the database. This is ignored for SQLite3 configurations.
A typical default installation will look like the following:
Code:
<database>
<backend>sqlite3</backend>
<name>MyMedia.db</name>
<host>userdata://Database</host>
<port></port>
<user></user>
<pass></pass>
</database>Database Abstraction Library
The current database abstraction library utilised dbiplus for which their only exists and SQLite3 driver.
Proposed: OpenDBX[4] will be used as the database abstraction library. OpenDBX supports the initially desired backends SQLite, MySQL and PostgreSQL. OpenDBX supports a number of other backends which may be supported at a later stage.
Missing Tables
In the current implementation there appears to be a number of tables missing due to the existence of mapping tables that link to undefined Primary Keys. These tables are director and writer.
Proposed: Add the missing tables director and writer.
Footnotes:
[1] SQLite - http://www.sqlite.org
[2] MySQL - http://www.mysql.com
[3] PostgreSQL - http://www.postgresql.org
[4] OpenDBX – http://www.linuxnetworks.de/doc/index.php/OpenDBX
Attachments:
Video Database Schema [Current]
![[Image: VideoDatabase_thumb.png]](http://www.securixlive.com/images/VideoDatabase.png)
Music Database Schema [Current]
![[Image: MusicDatabase_thumb.png]](http://www.securixlive.com/images/MusicDatabase.png)
Search
Help