This addon allows to use MySQL/ MariaDB/ PostgreSQL databases instead of default MongoDB/ SQLite.
- 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
To run Cockpit 0.9.2+
at least version 1.0.0-beta.2+
is required.
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.
Download latest release and extract to COCKPIT_PATH/addons/SqlDriver
directory
./cp install/addon --name SqlDriver --url https://github.com/piotr-cz/cockpit-sql-driver/archive/master.zip
-
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"] } } }
-
In your project root run command
composer require piotr-cz/cockpit-sql-driver
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
-
Export data to
COCKPIT_PATH/migration
subdirectorymkdir migration ./cp export --target migration
-
Switch database to sqldriver (see Configuration)
-
Import data from
COCKPIT_PATH/migration
subdirectory./cp import --src migration rm -rf migration
Reference: Cockpit docs > CLI
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
-
Install dependencies
cd COCKPIT_PATH/addons/SqlDriver composer install
-
Configure test database
copy
/phpunit.xml.dist
to/phpunit.xml
and set up variables as in configuration -
Run tests with PHPUnit
./vendor/bin/phpunit
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.
-
$func
/$fn
/$f
-
$fuzzy
-
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
Filter options are not supported ($minScore, $distance, $search).
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
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 since 2019 Piotr Konieczny under the MIT license.