Cockpit Sql Driver

SQL Driver for Cockpit CMS
Alternatives To Cockpit Sql Driver
Project NameStarsDownloadsRepos Using ThisPackages Using ThisMost Recent CommitTotal ReleasesLatest ReleaseOpen IssuesLicenseLanguage
Cockroach27,8085024a day ago249August 06, 20216,008otherGo
CockroachDB - the open source, cloud-native distributed SQL database.
Mysql13,6714,89211,214a day ago51April 25, 202373mpl-2.0Go
Go MySQL Driver is a MySQL driver for Go's (golang) database/sql package
Migrate12,2578152 days ago132June 11, 2023294otherGo
Database migrations. CLI and Golang library.
Pq8,2426,11511,010a month ago49April 26, 2023301mitGo
Pure Go Postgres driver for database/sql
Usql8,2232134 days ago152July 18, 202367mitGo
Universal command-line interface for SQL databases
Yugabyte Db8,060
a day ago5,408otherC
YugabyteDB - the cloud native distributed SQL database for mission-critical applications.
Go Sqlmock5,4251,336a month ago21June 28, 202075otherGo
Sql mock driver for golang to test database interactions
Clickhouse Go2,5494172 days ago118August 18, 202362apache-2.0Go
Golang driver for ClickHouse
Sqler1,918
2 years ago2July 12, 2021apache-2.0Go
write APIs using direct SQL queries with no hassle, let's rethink about SQL
Msphpsql1,726
2 days ago37mitPHP
Microsoft Drivers for PHP for SQL Server
Alternatives To Cockpit Sql Driver
Select To Compare


Alternative Project Comparisons
Readme

SQL Driver for Cockpit CMS

Latest Version Build status

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

    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.

Popular Driver Projects
Popular Sql Projects
Popular Libraries Categories
Related Searches

Get A Weekly Email With Trending Projects For These Categories
No Spam. Unsubscribe easily at any time.
Php
Database
Mysql
Postgresql
Sql
Driver
Content Management System
Mariadb
Pdo