README

This addon is not compatible with Cockpit CMS v2.

Manage SQL tables with one-to-many (1:m) and many-to-many (m:n) relations in Cockpit CMS.

Cockpit is a leightweight, headless CMS. It's internal logic is based on MongoDB - a schemaless database. It has a wrapper to use the same logic with SQLite, ~~but there's no real SQL implementation, yet~~. When I find the time, I'll test a setup with the new, experimental SQL Driver addon from @piotr-cz.

The Tables addon adds the functionality, to manage a SQL database. Cockpit still needs MongoDB or SQLite for it's internal logic.

This addon needs a lot of cleanup and some restructuring. If you use it in production, be aware of possible structural changes. I can't guarantee for backwards compatibility in this early state. It is not performance optimized for large databases, yet.

This addon is experimental. I wanted to do a lot of code cleanup before publishing it, but I didn't have enough time, yet. If I don't publish it now, it won't happen anytime soon anymore...

Please send me some feedback, if you tested it.

docs

Features

automatic detection of all available tables in the database
automatic detection of foreign key relations
automatic generation of field schema with basic type detection (boolean, number, text, textarea, date)
- with basic validation detection
  - required (NOT NULL)
  - maxlength (VARCHAR(100))
automatic LEFT OUTER JOIN to display 1:m related fields in entries and entry views
if a m:n relation is detected, an extra field is created with a select field for the related content
save and delete values to/in related m:n tables
The automatically generated field schema can be adjusted by changing the field settings, like
- changing text to textarea
- make a field required, that wasn't setup as nullable in the database
- change a relation field to a number field to avoid the automatic join
- remove a m:n extra field, to avoid displaying it in a m:n helper table
- ...
split relation-select field, if the related column contains a lot of rows (optional)
user and group rights management
spreadsheet export (ODS, CSV, XLSX)

Features (enhancement, maybe, in the future...)

RestApi
graphical relation manager
update database schema directly (create new table, add index etc.)
spreadsheet import

Requirements

Cockpit

see Cockpit's requirements

PHP >= 7.0
PDO + SQLite (or MongoDB)
GD extension
mod_rewrite enabled (on apache)

Tables addon

PDO
MySQL version ???
InnoDB schema for MySQL tables (may function, with different schema, but I didn't test it yet)
All tables must have a single column as primary key, which auto-increments. Choose a name, you want - it's not necessary, to name it id.

Installation

Copy this repository into /addons and name it Tables or

cd path/to/cockpit
git clone https://github.com/raffaelj/cockpit_Tables.git addons/Tables

build

install dependencies:

composer install --no-dev --ignore-platform-reqs

update dependencies:

composer update --no-dev --ignore-platform-reqs

Usage/Configuration

Your database with foreign keys exists already.
Install Cockpit CMS.
Copy this addon into Cockpit's addon folder.
Add your database credentials to Cockpit's config file /config/config.yaml.

tables:
  db:
    host: localhost
    database: database_name
    user: root
    password: SuperSecretPassword

Alternatively you can set the path to a config file.

tables:
  db: /path/to/config.php
  # db: /path/to/config.ini
  # db: /path/to/config.yaml

If you don't need Cockpit's core modules, disable them in the config:

modules.disabled:
    - Collections
    - Singletons
    - Forms

Copyright and License

See LICENSE for more information.

Credits and third party resources

I reused a big part of the Collections module from Cockpit CMS, which is released under the MIT License, and modified it. Thanks at Artur Heinze and to all contributors.

For exporting spreadsheets, I used PhpSpreadsheet, which is released under the LGPL 2.1 License.

I used a minimalistic PDO wrapper from phpdelusions.net. Thanks @colshrapnel

For a top scrollbar above tables I used the jQuery plugin jqDoubleScroll from Antoine Vianey, which is dual licensed under the MIT License and GPL License.

For syntax highlighting in the docs, I used highlight.js (Github, authors), which is licensed under the BSD 3-Clause License

raffaelj / cockpit-tables

Maintainers

Details