Working with database

Skill MacareuxDigital/concretecms-skills/working-with-database

Agent Skills for Concrete CMS

Install

npx -y skills add MacareuxDigital/concretecms-skills --skill working-with-database

Assembled from the repository path, not quoted from the project. Check it against their README if it does not work.

One thing to look at

9 stars9 stars. Stars are a popularity signal and not a quality one, but at this level it is likely that nobody has read this closely except its author, and you would be relying on your own review.

What its author says it does

Copied from the file, not written here

Connect to databases, execute queries, and manipulate data in Concrete CMS projects. Use this skill when the user asks to build custom block types or connect with database.

SKILL.md

3.0 KB, as published. Nobody here has run it

Working with Database

How to connect

Use $db = $this->app->make(\Concrete\Core\Database\Connection\Connection::class); Do not use the Database facade, it is deprecated.

XML file format for database schemas

Concrete CMS uses "Doctrine XML" file format to define database schema. Ref: https://concretecms.github.io/doctrine-xml/doctrine-xml-0.5.xsd

Here's an example with all the features offered by Doctrine XML:

<?xml version="1.0" encoding="UTF-8"?>
<schema xmlns="http://www.concrete5.org/doctrine-xml/0.5"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.concrete5.org/doctrine-xml/0.5
    https://concretecms.github.io/doctrine-xml/doctrine-xml-0.5.xsd"
>

    <table name="Companies" comment="List of companies">
        <field name="Id" type="integer" comment="Record identifier">
            <unsigned/>
            <autoincrement/>
            <key/>
        </field>
        <field name="Name" type="string" size="50" comment="Company name">
            <notnull/>
            <opt for="mysql" collation="utf8_bin"/>
        </field>
        <opt for="mysql" engine="InnoDB" charset="utf8" collate="utf8_unicode_ci" row_format="compact"/>
    </table>

    <table name="Employees">
        <field name="Id" type="integer">
            <unsigned/>
            <autoincrement/>
            <key/>
        </field>
        <field name="IdentificationCode" type="string" size="20">
            <fixed/>
        </field>
        <field name="Company" type="integer">
            <unsigned/>
            <notnull/>
        </field>
        <field name="FirstName" type="string" size="50">
            <default value=""/>
            <notnull/>
        </field>
        <field name="LastName" type="string" size="50">
            <notnull/>
        </field>
        <field name="Income" type="decimal" size="10.2">
            <default value="1000"/>
        </field>
        <field name="HiredOn" type="datetime">
            <deftimestamp/>
        </field>
        <index>
            <fulltext/>
            <col>FirstName</col>
        </index>
        <index name="IX_EmployeesIdentificationCode">
            <unique/>
            <col>IdentificationCode</col>
        </index>
        <references table="Companies" onupdate="cascade" ondelete="restrict">
            <column local="Company" foreign="Id"/>
        </references>
    </table>

</schema>

AXMLS (Adodb-xmlschema, using <schema version="0.3"></schema>) is a legacy format, so do not use it for new code. No need to refactor XML files already exists.

Best Practices

Avoid using SQL reserved words (especially in MySQL 8+) for column names. For example, lead is reserved in MySQL 8 — do not use it as a column name. Prefer alternatives like summary.