SQL Driver for Cockpit CMS

This addon allows to use MySQL/ MariaDB/ PostgreSQL databases instead of default MongoDB/ SQLite.

Requirements

• Cockpit CMS (next, tested up to v0.11.0 or legacy)
• MySQL 5.7.9/ MariaDB 10.2.6/ PostgreSQL 9.4
• PHP 7.1
• Enabled PHP extensions: pdo, pdo_mysql/ pdo_pgsql

Compatibility

To run Cockpit 0.9.2+ at least version 1.0.0-beta.2+ is required.

Installation

Note:

If you installed addon before ever starting Cockpit, some errors may come up once you start it.

To solve it, start Cockpit with database configuration it supports out of the box to trigger Cockpit warmup and then set configuration specific for this addon.

Manual

Download latest release and extract to COCKPIT_PATH/addons/SqlDriver directory

Using Cockpit CLI (development version)

./cp install/addon --name SqlDriver --url https://github.com/piotr-cz/cockpit-sql-driver/archive/master.zip

Using Composer

1. Make sure path to cockpit addons are defined in your projects' composer.json file

   {
       "name": "MY_PROJECT",
       "extra": {
           "installer-paths": {
               "public/cockpit/addons/{$name}": ["type:cockpit-module"]
           }
       }
   }

2. In your project root run command

   composer require piotr-cz/cockpit-sql-driver

Configuration

Example configuration for COCKPIT_PATH/config/config.php file:

<?php
return [
    # Cockpit configuration
    # …

    # Use SQL Driver as main data storage
    'database' => [
        'server' => 'sqldriver',
        # Connection options
        'options' => [
            'connection' => 'mysql',         # One of 'mysql'|'pgsql'
            'host'       => 'localhost',     # Optional, defaults to 'localhost'
            'port'       => 3306,            # Optional, defaults to 3306 (MySQL) or 5432 (PostgreSQL)
            'dbname'     => 'DATABASE_NAME',
            'username'   => 'USER',
            'password'   => 'PASSWORD',
            'charset'    => 'UTF8',          # Optional, defaults to 'UTF8'
            'tablePrefix' => '',             # Optional, database tables prefix (ie. 'cockpit_')
            'bootstrapPriority' => 999,      # Optional, defaults to 999
        ],
        # Connection specific options
        # General: https://www.php.net/manual/en/pdo.setattribute.php
        # MySQL specific: https://www.php.net/manual/en/ref.pdo-mysql.php#pdo-mysql.constants
        'driverOptions' => [],
    ],
];

Rererence: Cockpit docs > Configuration

Database content migration (Cockpit v0.6.0+)

1. Export data to COCKPIT_PATH/migration subdirectory

   mkdir migration
   ./cp export --target migration

2. Switch database to sqldriver (see Configuration)

3. Import data from COCKPIT_PATH/migration subdirectory

   ./cp import --src migration
   rm -rf migration

Reference: Cockpit docs > CLI

Testing

There are integration tests included in the package. These require Cockpit CMS as a dev dependency and use it's MongoHybrid\Client API to run actions on database.

To run tests

1. Install dependencies

   cd COCKPIT_PATH/addons/SqlDriver
   composer install

2. Configure test database

   copy /phpunit.xml.dist to /phpunit.xml and set up variables as in configuration

3. Run tests with PHPUnit

   ./vendor/bin/phpunit

Drawbacks

Cockpit doesn't provide public API to register custom database drivers so this addon monkey-patches Cockpit Driver selector client (MongoHybrid Client). This means that there is no guarantee that this addon will work in future versions of Cockpit.

Collection filters

Not implemented

• $func/ $fn/ $f

• $fuzzy

Work differently

• callable

  Unlike SQLite, PDO MySQL and PostgreSQL drivers don't have support for User Defined Functions in PHP language - so callable is evaluated on every result fetch. If you have lots of documents in collection and care about performance use other filters.

• $in, $nin

  When database value is an array, evaluates to false.

• $regexp

  • MySQL implemented via REGEXP + case insensitive
  • PostgreSQL impemeted via POSIX Regular Expressions + case insensitive

  Wrapping expression in // or adding flags like /foobar/i won't work, as MySQL and PosgreSQL Regexp functions don't support flags.

• $text

  • MySQL implemeted via LIKE
  • PostgreSQL implementad via LIKE

  Filter options are not supported ($minScore, $distance, $search).

Manual database optimizations

By default package creates virtual column _id with unique index on every created collection.

If you would like to speed up filters on other collection fields - add virtual column with suitable index and type.

For example to add virtual column of integer type for field FIELD_NAME in TABLE_NAME collection, use

• MySQL:

  ALTER TABLE
      `{TABLE_NAME}` ADD COLUMN `{FIELD_NAME}_virtual` INT AS (`document` ->> '$.{FIELD_NAME}') NOT NULL,
      ADD UNIQUE | KEY `idx_{TABLE_NAME}_{FIELD_NAME}` (`{FIELD_NAME}_virtual`);

  Reference: MySQL 5.7 > CREATE INDEX

• PosgreSQL:

  CREATE [UNIQUE] INDEX "idx_{TABLE_NAME}_{FIELD_NAME}" ON "{FIELD_NAME}" ((("document" ->> '{FIELD_NAME}')::int));

  Reference: PostgreSQL 9.4 > CREATE INDEX

Known issues

Error: Call to a member function toArray() on null

This happens when starting cockpit for the first time and this addon is installed. The reason is in that native Cockpit modules try to accesss storage which is initialized later (during custom modules bootstrap).

Cockpit must be started for the first time without being configured to use SQL driver.

Solution 1 Start Cockpit with database configuration it supports out of the box and than switch to sqldriver as described here

Solution 2 Manually create file COCKPIT_STORAGE_FOLDER/tmp/webhooks.cache.php with content

<?php return [];

Copyright and license

Copyright since 2019 Piotr Konieczny under the MIT license.