I) Install II) Manually creating a db III) Upgrading your db IV) The DB Schema I) Installing the DB via the installer NOTE: in RC10, the installer may not be working properly, particularly in regards to postgres. Manual DB creation works fine, see below. You can use the full installer for calliope by: cd /usr/local/calliope/installer su ./install You can use the installer to install just the db, if you've edited the DB vars in the conf file, and altered etc/db/data.sql and mysql.sql by: cd /usr/local/calliope/installer su ./install -d II) Manually creating DB Mysql -- /usr/local/calliope/etc/db/schemas/schema_.mysql You may wish to edit schema_.mysql for default data, user names and passwords as defined within the file. If should work fine as is, security notwithstanding. To create a new DB: su cd /usr/local/calliope/etc/db ./createdb_mysql [-p] Where is a two digit code that indicates which schema version to use (typically, the highest number). To drop the old database and database user, create a new DB, and populate it with both default data and song data, use: su cd /usr/local/calliope/etc/db ./deletedb_mysql [-p] ./createdb_mysql [-p] The '-p' is optional, and is used when you have a password for mysql. The is a two digit number (e.g. "01") representing which schema version to use. This should match the conf file, and should generally be the highest available number. Postgresql -- /usr/local/calliope/etc/db/schemas/schema_.psql You may wish to edit schema.psql for default data, user names and passwords as defined within the file. If should work fine as is, security notwithstanding. To create a new DB: su postgres cd /usr/local/calliope/etc/db ./createdb_psql Where is a two digit code that indicates which schema version to use (typically, the highest number). To drop the old database and database user, create a new DB, and populate it with both default data and song data, use: su postgres cd /usr/local/calliope/etc/db ./deletedb_psql ./createdb_psql As of RC10, there are some unicode / ISO character issues, and you may see this: DBD::Pg::st execute failed: ERROR: invalid byte sequence for encoding "UNICODE": 0xe87269 which is a known bug involving international character strings. III) Upgrading the db As of RC13, calliope has a db versioning system, and the software must match the db schema in use. If you download a software update you can upgrade your db: cd /usr/local/calliope/etc/db su ./upgradedb [-p] the database your upgrading needs to be the one in the conf file. that is, you can't upgrade your postgres database if your conf file is using the DB::mysql mod. '-p' indicates the need for a mysql root password. If you're upgrading from an earlier version, you will need to drop and re-create your database: cd /usr/local/calliope/etc/db ./deletedb_mysql ./createdb_mysql For postgres, you must be the postgres user: su postgres ./deletedb_psql ./createdb_psql IV) Calliope DB Schema ~/etc/db/schemas/schema_.mysql or schema_.psql Calliope is designed around a relational data architecture. The main goal was to break away from storing data in a heirarchical fashion (the way it is on your hard drive), or in the same structure as some standard (e.g. ID3v.x). Structuring the DB this way allows for features such as multi-genre associations, multi-artist associations, etc. It also makes the task of integrating new interface and searching features much easier (add a new data table and a new relation table). Another key goal has been to provide a data structure that wont have to change as the software evolves, or as standards change. Above all, I wanted to provide for as many future features as possible, and allow for additional data to be folded in without DB architecture changes (vs additions, which are easier). The table structure is highly regular, and that structure is very rigid. Given a type of data (i.e. "artist") that we want to store, the table name gets an 's' (i.e. "artists"), and will contain, at a minimum, a id and field (i.e. "artistid" and "artist"). For example: create table artists ( artistid int not null primary key auto_increment, artist varchar(255) not null, # e.g. "johnny cash" fileunder varchar(255) not null, # e.g. "cash, johnny" xwhen timestamp, # last modified notes text, index fileunderindex(fileunder), index xindex(xwhen), index artistindex(artist) ); If a given data type is to be associated with another data type, a relational table is defined that links the ids of those two types. For instance, if we want to associate artist(s) with a song, we need a "songartist" table. In general, the name of the table will correspond to a one -> many relationship, that is, for a given song, there will be many artists, thus the table is "songartist" not "artistsong". It's imperative that the relation table have a name that includes the two table names it is associating, less the "s". Thus, a "songartist" table requires that there is a "songs" table and an "artist" table. At a minimum, the relation table will list the ids of the two tables it relations, and those fields will again be named based on the table names being associated. For the "songartist" table, there must be a "song" id and an "artist" id, e.g. "songid" and "artistid". All this design regularity allows the DB code to be "smart" and use the known semantics of table naming and structure to build queries, rather than having to define every query in a long list. For example: create table songartist ( songid int not null, artistid int not null );