Setting up a MySQL database

A fresh checkout defaults to using an SQLite database for ease of setup. The live version of Camdram runs using a MySQL database, which is faster and supports incremental updates to the schema. A MySQL database is therefore required if you wish to do development work that involves modifications to the database schema.

This guide is designed for Debian-based linux distributions (e.g. Ubuntu). The steps for other distros should be similar (feel free to edit this page or create a new wiki page with any differences).

Installing MySQL

Run the following to install MySQL and the MySQL PHP extension from the repository:

sudo apt-get install default-mysql-server php-mysql

The above also installs PHPMyAdmin, which is a useful browser-based tool for administering a MySQL database. (We have previously recommended phpmyadmin but it appears that this will not be released with Debian Buster as it is still incompatible with PHP 7.3.) On Debian, default-mysql-server will actually install MariaDB rather than MySQL proper; this is completely fine and compatible.

During the installation it will ask you to set a root password for MySQL.

Create a database

Create a database and a user for your copy of Camdram to use. You can either do this using PHPMyAdmin, or by running the commands below

mysql -uroot -p
Enter password: [Enter your MySQL root password and press enter]
CREATE DATABASE `camdram` COLLATE utf8_general_ci;
CREATE USER 'camdram'@'localhost' IDENTIFIED BY  'choose-a-password';
GRANT ALL PRIVILEGES ON  `camdram` . * TO  'camdram'@'localhost';

Use Ctrl+D to exit the MySQL shell.

MySQL settings

The sql_mode is a collection of settings for the database; for best results yours should match what is used on the production server. Currently this is NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION. The search functionality also requires additional settings. You should add to your my.cnf (search for the correct location for your OS):

[mysqld]
sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
innodb_ft_min_token_size=2
ft_min_word_len=2

then restart your MySQL server with sudo service mysql restart.

Configure Camdram for MySQL

Open app/config/parameters.yml (in the Camdram checkout directory) and change the following line from:

    env(DATABASE_URL): "sqlite:///%kernel.root_dir%/data/orm.db"

to:

    env(DATABASE_URL): "mysql://camdram:[your password]@localhost/camdram"

Create the database tables and sample data

Run the following two commands. The first runs all the database migrations to date, which creates all the DB tables, and the second generates some sample data

php app/console doctrine:migrations:migrate
php app/console doctrine:fixtures:load

If you get any 'out of memory' errors whilst during this you can allocate more memory for php per-command; see step (4) of the project README.md for details. Doctrine is quite memory-greedy, though 512MB for the process should be sufficient.

Database migrations

The database schema is not created directly - it is inferred automatically from annotations in the 'Entity' classes using a library called Doctrine (see docs) - this is how Camdram can trivially support the syntax differences between SQLite and MySQL. E.g. an abridged excerpt from /src/CamdramBundle/Entity/Show.php is shown below:

class Show
{
    /**
     * @var integer
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var string
     * @ORM\Column(name="title", type="string", length=255, nullable=false)
     */
    private $name;

    /**
     * @var string
     * @ORM\Column(name="description", type="text", nullable=true)
     */
    private $description;

If you make changes to the annotations on any entities, run the following command to generate a set of SQL statements which will apply the changes to the database:

php app/console doctrine:migrations:diff

This will create a file with the current date and time in /app/DoctrineMigrations/. You are advised to check this file to ensure the SQL queries it contains match what you expect. The following command (as used above) will apply all migrations to the database:

php app/console doctrine:migrations:migrate

The migration file is what will eventually be applied to the live database, so make sure it is correct before committing. It may be necessary to modify the automatically generated SQL in certain circumstances (e.g. if you change a field name, it'll remove the old column and add the new one, whereas it should just rename the column so that data is preserved). It is recommended to consolidate multiple migrations into one migration file before committing (e.g. by deleting the new migrations, running the migrations again on a fresh database and doing a diff again).

Loading a database dump

If you wish to import a database dump (e.g. if you have access to Antigone and wish to import a copy of the live database), you are advised to completely delete and recreate the database before importing the .sql file, because while it's easy to delete the tables it's harder to delete all the foreign key constraints.