### Explicitly install table with install() Source: https://github.com/berlindb/core/blob/master/skills/berlindb/references/schema-table.md Use this for a first install where you explicitly want to create the table. When changing a column or index, bump the Table $version; BerlinDB stores the installed version in WordPress options and compares it with the Table version. ```php ( new WidgetTable() )->install(); ``` -------------------------------- ### Install dependencies and run tests Source: https://github.com/berlindb/core/wiki/Contributing Install dependencies and run the test suite. Use this to set up the development environment before contributing. ```bash composer install bin/run-tests.sh ``` -------------------------------- ### Install PHP dependencies with composer install Source: https://github.com/berlindb/core/blob/master/CONTRIBUTING.md Installs PHP dependencies for the project. Run this after cloning the repository. ```bash composer install ``` -------------------------------- ### Define and install a WidgetTable Source: https://github.com/berlindb/core/blob/master/README.md Defines a table class that references the schema, sets the table name and version, and then installs (creates or upgrades) the table. Run the install() call during your plugin's install or upgrade routine. ```php install(); ``` -------------------------------- ### Install BerlinDB via Composer Source: https://github.com/berlindb/core/wiki/Installation Run this command to install BerlinDB as a Composer dependency. It is the primary installation method. ```bash composer require berlindb/core ``` -------------------------------- ### Install or upgrade a table with maybe_upgrade() Source: https://github.com/berlindb/core/wiki/Managing-Tables-and-Upgrades Instantiates the table and calls maybe_upgrade(), which checks the stored database version and runs installation or pending upgrades as needed. ```php $table = new WidgetsTable(); $table->maybe_upgrade(); ``` -------------------------------- ### Search Example Source: https://github.com/berlindb/core/wiki/Parsers-and-Operators Shows how to use the Search parser to perform a text search across searchable columns. The 'search' key takes a search string. ```php $query->query( array( 'search' => 'customer email', ) ); ``` -------------------------------- ### Composer Install and Autoload Source: https://github.com/berlindb/core/wiki/Troubleshooting Install Composer dependencies and load the autoloader to resolve autoloading failures. ```bash composer install ``` ```php require_once __DIR__ . '/vendor/autoload.php'; ``` -------------------------------- ### Use WidgetQuery to add and get a widget Source: https://github.com/berlindb/core/wiki/Setting-Up-Your-First-Table Shows how to use the WidgetQuery class in application code: instantiate it, add a new item with add_item(), and retrieve it with get_item(). ```php $query = new WidgetQuery(); $widget_id = $query->add_item( array( 'name' => 'Example Widget', 'status' => 'active', ) ); $widget = $query->get_item( $widget_id ); ``` -------------------------------- ### IN Query Example Source: https://github.com/berlindb/core/wiki/Parsers-and-Operators Demonstrates the In parser for column__in clauses. The 'id__in' key takes an array of values to match. ```php $query->query( array( 'id__in' => array( 10, 11, 12 ), ) ); ``` -------------------------------- ### Basic Read with WidgetQuery Source: https://github.com/berlindb/core/wiki/Writing-Queries Instantiate a custom query class and run a basic read with common query arguments. The example uses a fictional Acme\Database\WidgetQuery class and passes status, orderby, order, and number parameters. ```php $query = new Acme\Database\WidgetQuery(); $widgets = $query->query( array( 'status' => 'active', 'orderby' => 'created', 'order' => 'DESC', 'number' => 20, ) ); ``` -------------------------------- ### Run tests manually with Composer and PHPUnit Source: https://github.com/berlindb/core/blob/master/tests/README.md Manual local test run requiring PHP 8.1+, MySQL, SVN, and Composer. Installs dependencies, sets up the test database, and runs PHPUnit. ```bash composer install bin/install-wp-tests.sh berlindb_tests root '' localhost latest vendor/bin/phpunit ``` -------------------------------- ### add_foreign_keys() Source: https://github.com/berlindb/core/wiki/Relationships Documentation for the `add_foreign_keys()` method used to enforce foreign keys after all tables are installed. ```APIDOC ## [DDL] add_foreign_keys ### Description Emits real `FOREIGN KEY` constraints for relationships with `enforce => true`. Deferred by default; call after all referenced tables exist. ### Method DDL ### Endpoint `$table->add_foreign_keys()` ### Parameters No parameters. ### Request Example No request body. ### Response #### Success Response Installs the foreign key constraints on the table. #### Response Example No explicit response example provided in the source. ``` -------------------------------- ### compare_query basic comparison Source: https://github.com/berlindb/core/wiki/Writing-Queries Builds a single WHERE comparison using the compare_query key. The example compares the 'priority' column with '>=' against value 40, producing the SQL comment shown. ```php $query->query( array( 'compare_query' => array( 'key' => 'priority', 'compare' => '>=', 'value' => 40, ), ), ); // ... WHERE priority >= 40 ``` -------------------------------- ### Date Query Example Source: https://github.com/berlindb/core/wiki/Parsers-and-Operators Shows how to use the Date parser to query records created after a specific date. The 'created_query' key expects an array of conditions, here with 'after' and 'inclusive' flags. ```php $query->query( array( 'created_query' => array( array( 'after' => '2026-01-01', 'inclusive' => true, ), ), ) ); ``` -------------------------------- ### Implement PlatformProvider::platform() returning a Platform Source: https://github.com/berlindb/core/blob/master/skills/berlindb/references/extending.md Implement the opt-in Interfaces\PlatformProvider on a Connection to return an Adapters\Platform descriptor. The example returns SQLite version 3.45.0; the platform is read-only and answers named capability questions permissively for unknown and MySQL-family platforms. ```php public function platform(): Platform { return new Platform( Platform::SQLITE, '3.45.0' ); } ``` -------------------------------- ### Compatibility Helper Method get_label() Source: https://github.com/berlindb/core/wiki/Rows-and-Casting Provides a compatibility helper method that returns the name property or an empty string if not set. Use this layer for application presentation and compatibility, not for table installation or SQL generation. ```php class Widget extends Row { public function get_label() { return $this->name ?? ''; } } ``` -------------------------------- ### Register upgrade callbacks with version keys Source: https://github.com/berlindb/core/wiki/Managing-Tables-and-Upgrades Defines a table with a version and an upgrade callback for that version. The upgrade method adds an index. Upgrade methods should be idempotent because a table may be installed fresh, partially upgraded, or checked repeatedly during development. ```php class WidgetsTable extends Table { protected $version = '1.1.0'; protected $upgrades = array( '1.1.0' => 'upgrade_to_110', ); protected function upgrade_to_110() { $this->add_index( array( 'name' => 'status', 'columns' => array( 'status' ), ) ); } } ``` -------------------------------- ### Define application helper function outside query class Source: https://github.com/berlindb/core/wiki/Writing-Queries Keep application convenience functions outside the query class when possible. This example defines a helper function that creates a WidgetQuery instance and retrieves an item by ID. ```php function acme_get_widget( int $id ) { $query = new Acme\Database\WidgetQuery(); return $query->get_item( $id ); } ``` -------------------------------- ### Use operand key with IN/BETWEEN/LIKE/IS NULL Source: https://github.com/berlindb/core/blob/master/skills/berlindb/SKILL.md Use an operand key with a bare value on IN/BETWEEN/LIKE/IS NULL. Example: YEAR(created) IN (2023, 2024). ```php 'key' => array( 'operand' => 'func', 'name' => 'YEAR', 'args' => array( array( 'operand' => 'column', 'name' => 'created' ) ) ), 'compare' => 'IN', 'value' => array( 2023, 2024 ) ``` -------------------------------- ### Hook table upgrade to admin_init Source: https://github.com/berlindb/core/wiki/Installation Example of hooking a table's maybe_upgrade method to the admin_init action. Avoid creating tables too early in the WordPress bootstrap; use this hook or plugin activation routines. ```php add_action( 'admin_init', array( $table, 'maybe_upgrade' ) ); ``` -------------------------------- ### Add a column programmatically with add_column() Source: https://github.com/berlindb/core/wiki/Schemas-Columns-and-Indexes Add a column programmatically to a Schema instance using add_column(). The example creates a varchar column named 'name'. ```php $schema = new Schema(); $schema->add_column( array( 'name' => 'name', 'type' => 'varchar', ) ); ``` -------------------------------- ### Schema Upgrades via $upgrades Map and Callback Source: https://github.com/berlindb/core/blob/master/skills/berlindb/references/schema-table.md Declare an 'upgrades' map and implement a protected callback for each version to add or alter columns/indexes. BerlinDB runs callbacks whose key is greater than the installed version; always return bool, as false stops the upgrade chain. ```php final class WidgetTable extends Table { protected $schema = WidgetSchema::class; protected $name = 'acme_widgets'; protected $version = '202605281'; protected $upgrades = array( '202605281' => '__upgrade_202605281', ); protected function __upgrade_202605281(): bool { if ( ! $this->column_exists( 'notes' ) ) { return $this->is_success( $this->db()->query( "ALTER TABLE {$this->table_name} ADD COLUMN notes longtext NOT NULL DEFAULT ''" ) ); } return true; } } ``` -------------------------------- ### Get Item by ID Source: https://github.com/berlindb/core/wiki/Writing-Queries Fetch a single item by its primary key using the get_item() method. The example passes the integer ID 123. ```php $widget = $query->get_item( 123 ); ``` -------------------------------- ### Get Item by UUID Source: https://github.com/berlindb/core/wiki/Writing-Queries Fetch a single item by a supported single-column lookup using get_item_by(). The example uses the 'uuid' column, which is a cache-key column. ```php $widget = $query->get_item_by( 'uuid', $uuid ); ``` -------------------------------- ### Run tests with Docker Compose (first run) Source: https://github.com/berlindb/core/wiki/Testing-and-Tooling First run of the manual Docker command; creates the database and runs the PHPUnit tests. ```bash WP_VERSION=6.7 docker compose -f docker-compose-phpunit.yml run --rm php ``` -------------------------------- ### Show test runner help with bin/run-tests.sh --help Source: https://github.com/berlindb/core/wiki/Testing-and-Tooling Displays help for the test runner script, showing supported options for PHP, WordPress, MariaDB, and PHPUnit filters. ```bash bin/run-tests.sh --help ``` -------------------------------- ### Inspect Composer archive Source: https://github.com/berlindb/core/blob/master/docs/release-checklist.md Create a Composer archive and list its contents to verify packaging. ```bash composer archive --dir=/private/tmp --file=berlindb-core-package-check --format=zip ``` ```bash zipinfo -1 /private/tmp/berlindb-core-package-check.zip ``` -------------------------------- ### Index Hints Source: https://github.com/berlindb/core/wiki/Writing-Queries Demonstrates how to pass MySQL index hints to the query. ```APIDOC ## Index Hints ### Description Passes MySQL optimizer hints (USE / FORCE / IGNORE INDEX) through to the table reference. Hints only change the query plan, not the result set. ### Method query ### Parameters #### Request Body - **index_hints** (array) - Required - A single spec or list of specs. - **type** (string) - Required - 'use', 'force', or 'ignore'. - **indexes** (array) - Required - Declared index names or 'primary'. - **for** (string) - Optional - '' | 'join' | 'order by' | 'group by' (aliases 'orderby' / 'groupby' accepted). ### Request Example ```php $query->query( array( 'index_hints' => array( 'type' => 'use', 'indexes' => array( 'status_type' ), 'for' => 'order by', ), ) ); ``` ### Response #### Success Response Returns query results as usual. Malformed hints are dropped and logged. ``` -------------------------------- ### Query with date_query clause Source: https://github.com/berlindb/core/wiki/Writing-Queries Filters rows using a date-style clause on a column marked 'date_query'. The example uses 'after', 'before', and 'inclusive' parameters. ```php $query->query( array( 'created_query' => array( array( 'after' => '2026-01-01', 'before' => '2026-12-31', 'inclusive' => true, ), ), ) ); ``` -------------------------------- ### Create and inspect BerlinDB core package archive Source: https://github.com/berlindb/core/blob/master/skills/berlindb/references/verification.md Create a zip archive of the BerlinDB core package and list its contents to verify inclusions and exclusions. ```bash composer archive --dir=/tmp --file=berlindb-core --format=zip unzip -Z1 /tmp/berlindb-core.zip ``` -------------------------------- ### compare_query cast operand Source: https://github.com/berlindb/core/wiki/Writing-Queries Adds a 'cast' key to any scalar operand to wrap it in CAST(... AS ). This example casts the LOWER(name) expression to CHAR. ```php 'compare_query' => array( 'key' => array( 'operand' => 'func', 'name' => 'LOWER', 'args' => array( array( 'operand' => 'column', 'name' => 'name' ) ), 'cast' => 'CHAR', ), 'compare' => '=', 'value' => 'acme', ), // ... WHERE CAST(LOWER(name) AS CHAR) = 'acme' ``` -------------------------------- ### Run default integration tests with bin/run-tests.sh Source: https://github.com/berlindb/core/blob/master/CONTRIBUTING.md Runs the integration tests inside Docker against WordPress and MariaDB using the default helper versions. ```bash bin/run-tests.sh -- --group default ``` -------------------------------- ### Create or upgrade table on admin_init Source: https://github.com/berlindb/core/wiki/Setting-Up-Your-First-Table Shows how to create or upgrade the table from plugin bootstrap code by hooking into admin_init and calling maybe_upgrade() on a new WidgetsTable instance. ```php add_action( 'admin_init', static function () { $table = new Acme\Database\WidgetsTable(); $table->maybe_upgrade(); } ); ``` -------------------------------- ### Wrong identifier for update_item() Source: https://github.com/berlindb/core/blob/master/skills/berlindb/references/debugging.md Incorrect usage: update_item() expects the primary key value, not a slug. This example passes a slug and will fail or update the wrong row. ```php $query->update_item( $slug, $data ); ``` -------------------------------- ### Grouped aggregate query Source: https://github.com/berlindb/core/blob/master/skills/berlindb/references/query-row.md Adds a groupby column to an aggregate query to get one row per group, including the group column and each aggregate alias. ```php $rows = $query->query( array( 'aggregate' => array( 'revenue' => array( 'sum', 'amount' ) ), 'groupby' => 'status', ) ); // $rows = array( array( 'status' => 'complete', 'revenue' => '...' ), ... ) ``` -------------------------------- ### Run BerlinDB test suite Source: https://github.com/berlindb/core/blob/master/CLAUDE.md Run the test suite after every change without being asked. Pin the -w flag to a WordPress version because 'latest' may require network access that the sandbox can block. ```bash bin/run-tests.sh -p 8.2 -w 6.7 -- --group default ``` -------------------------------- ### Query with no_found_rows false Source: https://github.com/berlindb/core/blob/master/skills/berlindb/references/query-row.md Disables the default no_found_rows behavior to get the true total count of matching rows. get_found_items() then returns the total, useful for pagination. ```php $items = $query->query( array( 'number' => 20, 'offset' => 0, 'no_found_rows' => false, ) ); $total = $query->get_found_items(); ``` -------------------------------- ### Run integration tests with bin/run-tests.sh Source: https://github.com/berlindb/core/wiki/Testing-and-Tooling Runs the BerlinDB integration test suite against WordPress and MariaDB using the repository script. ```bash bin/run-tests.sh ``` -------------------------------- ### Override validation and casting with callables Source: https://github.com/berlindb/core/wiki/Schemas-Columns-and-Indexes Override inferred validators and casts for a column by specifying 'validate' and 'cast' callables. The example uses 'absint' for validation and 'intval' for casting. ```php array( 'name' => 'quantity', 'type' => 'bigint', 'validate' => 'absint', 'cast' => 'intval', ) ``` -------------------------------- ### Define a basic Table class with properties Source: https://github.com/berlindb/core/wiki/Managing-Tables-and-Upgrades Shows the minimal structure for a Table subclass, setting the table name, description, prefix, version, database version key, and schema class. ```php class WidgetsTable extends Table { protected $name = 'widgets'; protected $description = 'Acme widgets'; protected $prefix = 'acme'; protected $version = '1.0.0'; protected $db_version_key = 'acme_widgets_db_version'; protected $schema = WidgetSchema::class; } ``` -------------------------------- ### Use Renamed BerlinDB 3.x Methods Source: https://github.com/berlindb/core/blob/master/skills/berlindb/references/migration-2-to-3.md Use get_create_table_string() instead of to_string() and db() instead of get_db(). Deprecated aliases exist but should be updated when touching code. ```php // Schema — was to_string() $sql = $schema->get_create_table_string(); ``` ```php // Environment trait on Query/Table — was get_db() $this->db()->query( $sql ); ``` -------------------------------- ### Skip Database Creation in Docker Test Source: https://github.com/berlindb/core/wiki/Troubleshooting Run the PHPUnit Docker container with SKIP_DB_CREATE=true to skip database creation when the database already exists. ```bash WP_VERSION=6.7 docker compose -f docker-compose-phpunit.yml run -e SKIP_DB_CREATE=true --rm php ``` -------------------------------- ### Group-level negation with criteria not Source: https://github.com/berlindb/core/blob/master/skills/berlindb/references/query-row.md Group-level negation using `criteria` with `'not' => true`. The leaves of `criteria` are parser buckets, not conditions, so this wraps the bucket's combined SQL in `NOT ( ... )`. The example negates an OR of two buckets. ```php 'status' => 'active', 'compare_query' => array( 'key' => 'priority', 'value' => 40, 'compare' => '>=' ), 'criteria' => array( 'relation' => 'OR', 'not' => true, 'columns', // the direct-column bucket (status = 'active') 'compare', // the compare_query bucket (priority >= 40) ), // -> WHERE NOT ( ( status = 'active' ) OR ( priority >= 40 ) ) ``` -------------------------------- ### Import BerlinDB 3.x Kern Namespaces Source: https://github.com/berlindb/core/blob/master/skills/berlindb/references/migration-2-to-3.md Use these imports to reference the current 3.x namespaces for Schema, Table, Query, and Row. ```php use BerlinDB\Database\Kern\Schema; use BerlinDB\Database\Kern\Table; use BerlinDB\Database\Kern\Query; use BerlinDB\Database\Kern\Row; ``` -------------------------------- ### Create or upgrade table with maybe_upgrade() Source: https://github.com/berlindb/core/blob/master/skills/berlindb/references/schema-table.md Call this from an activation or upgrade path to create or upgrade the table. In tests, BerlinDB may call it automatically when testing mode is detected. ```php ( new WidgetTable() )->maybe_upgrade(); ``` -------------------------------- ### WordPress-Compatible Helpers Source: https://github.com/berlindb/core/wiki/Connection-Interface Helper methods that mimic WordPress database functions for compatibility. ```APIDOC ## esc_like ### Description Escapes a string for use in a LIKE query. ### Method esc_like ### Signature ```php public function esc_like( string $text ): string; ``` ### Parameters - **text** (string) - Required - The string to escape. ### Return Value - **string** - The escaped string. ## suppress_errors ### Description Suppresses or enables database error suppression. ### Method suppress_errors ### Signature ```php public function suppress_errors( bool $suppress = true ): bool; ``` ### Parameters - **suppress** (bool) - Optional - Whether to suppress errors. Default true. ### Return Value - **bool** - The previous suppression state. ## get_blog_prefix ### Description Returns the blog prefix for a given blog ID. ### Method get_blog_prefix ### Signature ```php public function get_blog_prefix( int|null $blog_id = null ): string; ``` ### Parameters - **blog_id** (int|null) - Optional - The blog ID. Default null. ### Return Value - **string** - The blog prefix. ## get_insert_id ### Description Returns the ID generated by the last insert operation. ### Method get_insert_id ### Signature ```php public function get_insert_id(): int; ``` ### Return Value - **int** - The insert ID. ## get_charset ### Description Returns the database charset. ### Method get_charset ### Signature ```php public function get_charset(): string; ``` ### Return Value - **string** - The charset. ## get_collation ### Description Returns the database collation. ### Method get_collation ### Signature ```php public function get_collation(): string; ``` ### Return Value - **string** - The collation. ``` -------------------------------- ### Define Casts Map in Row Subclass Source: https://github.com/berlindb/core/wiki/Rows-and-Casting Defines a $casts map in a Row subclass to convert raw database values to PHP values. The example casts id to int, enabled to bool, and metadata via maybe_unserialize. ```php class Widget extends Row { protected $casts = array( 'id' => 'intval', 'enabled' => 'boolval', 'metadata' => 'maybe_unserialize', ); } ``` -------------------------------- ### Pass MySQL index hints with index_hints Source: https://github.com/berlindb/core/wiki/Writing-Queries Passes MySQL optimizer hints (USE / FORCE / IGNORE INDEX) through to the table reference. Hints are RESULTS_INVARIANT and fail open: malformed specs are dropped and logged. Index names are validated against declared indexes plus PRIMARY; type must be use, force, or ignore; the for scope is optional and accepts join, order by, or group by. MySQL forbids mixing USE and FORCE on one table reference, so the first seen wins and later conflicting specs are dropped; IGNORE always coexists. ```php $query->query( array( 'index_hints' => array( 'type' => 'use', // 'use' | 'force' | 'ignore' 'indexes' => array( 'status_type' ), // declared index names, or 'primary' 'for' => 'order by', // '' | 'join' | 'order by' | 'group by' ), ) ); ``` -------------------------------- ### Use SQL function operand on left side (LOWER) Source: https://github.com/berlindb/core/blob/master/skills/berlindb/SKILL.md Use an operand on the left side via the clause key. A bare scalar on the other side is prepared with the function's return type. Example: LOWER(name) = 'jane'. ```php array( 'key' => array( 'operand' => 'func', 'name' => 'LOWER', 'args' => array( array( 'operand' => 'column', 'name' => 'name' ) ) ), 'value' => 'jane' ) ``` -------------------------------- ### Aggregate Query Source: https://github.com/berlindb/core/wiki/Writing-Queries Demonstrates how to use the aggregate container to compute SQL aggregates over matched rows. ```APIDOC ## Aggregate Query ### Description Computes SQL aggregates over matched rows in one cached query, returning values instead of item rows. ### Method query ### Parameters #### Request Body - **aggregate** (array) - Required - Map of alias to array(function, column) or array('function' => ..., 'column' => ..., 'distinct' => true). - **status** (string) - Optional - Filter applied before aggregation. - **groupby** (string) - Optional - Column to group results by. - **having** (array) - Optional - Filter groups by their results, e.g., array('revenue' => array('>', 1000)). - **orderby** (string) - Optional - Alias or group column to order by. - **order** (string) - Optional - Sort direction: 'ASC' or 'DESC'. ### Request Example ```php $totals = $query->query( array( 'aggregate' => array( 'revenue' => array( 'sum', 'amount' ), 'orders' => array( 'count', '*' ), 'peak' => array( 'max', 'created' ), ), 'status' => 'complete', ) ); ``` ### Response #### Success Response Returns an associative array of aggregate values keyed by alias. Empty set is null per alias except count, which is 0. #### Response Example ```php array( 'revenue' => '1234.50', 'orders' => 42, 'peak' => '2026-06-...' ) ``` ``` -------------------------------- ### Group aggregates with groupby, having, and orderby Source: https://github.com/berlindb/core/wiki/Writing-Queries Adds groupby, having, and orderby to an aggregate query to return one row per group filtered by group results. The example returns one row per status whose revenue exceeds 1000, ordered highest first. ```php $rows = $query->query( array( 'aggregate' => array( 'revenue' => array( 'sum', 'amount' ) ), 'groupby' => 'status', 'having' => array( 'revenue' => array( '>', 1000 ) ), 'orderby' => 'revenue', 'order' => 'DESC', ) ); // one row per status whose revenue exceeds 1000, highest first ``` -------------------------------- ### Run integration tests with specific PHP and WordPress versions Source: https://github.com/berlindb/core/blob/master/CONTRIBUTING.md Runs integration tests with a specific PHP version (8.1) and WordPress version (6.7). ```bash bin/run-tests.sh -p 8.1 -w 6.7 -- --group default ``` -------------------------------- ### Query, insert, update, and delete widgets Source: https://github.com/berlindb/core/blob/master/README.md Shows CRUD operations with a WidgetQuery instance: add_item, get_item, query with filtering, ordering, and limiting, update_item, and delete_item. ```php $query = new WidgetQuery(); $widget_id = $query->add_item( array( 'name' => 'Example', 'status' => 'active', ) ); $widget = $query->get_item( $widget_id ); $active_widgets = $query->query( array( 'status__in' => array( 'active', 'pending' ), 'orderby' => 'date_created', 'order' => 'DESC', 'number' => 20, ) ); $query->update_item( $widget_id, array( 'status' => 'archived', ) ); $query->delete_item( $widget_id ); ``` -------------------------------- ### Declare a has_many relationship on the parent key column Source: https://github.com/berlindb/core/blob/master/skills/berlindb/SKILL.md Place this 'relationships' array on the local key that children reference back (the 'one' side, usually 'id'). The 'column' key specifies the remote foreign-key column pointing here. This example is for an 'id' column where many Order_Items point back to this Order. ```php // On 'id' — many Order_Items point back at this Order. 'relationships' => array( array( 'query' => \EDD\Database\Queries\Order_Item::class, 'column' => 'order_id', // remote FK pointing here 'type' => 'has_many', 'name' => 'items', // optional accessor ), ), ``` -------------------------------- ### Run static analysis and coding standards Source: https://github.com/berlindb/core/blob/master/README.md Runs static analysis with PHPStan (memory limit 1G) and coding standards checks with PHPCS. ```bash vendor/bin/phpstan analyse --memory-limit=1G vendor/bin/phpcs ``` -------------------------------- ### Declare a belongs_to relationship on a foreign key column Source: https://github.com/berlindb/core/blob/master/skills/berlindb/SKILL.md Place this 'relationships' array on the local column that holds the foreign key (the owning side). The 'column' key specifies the remote key it references (usually 'id'), and 'name' is an optional accessor. This example is for an 'order_id' column pointing to one Order. ```php // On 'order_id' — this row points at one Order. 'relationships' => array( array( 'query' => \EDD\Database\Queries\Order::class, 'column' => 'id', // remote key referenced 'type' => 'belongs_to', 'name' => 'order', // optional accessor ), ), ``` -------------------------------- ### Run PHPStan analysis Source: https://github.com/berlindb/core/wiki/Testing-and-Tooling Runs PHPStan static analysis with a 512M memory limit. Configuration is in phpstan.neon and WordPress stubs are in phpstan/stubs. ```bash vendor/bin/phpstan analyse --memory-limit=512M ``` -------------------------------- ### Define a WidgetSchema with columns and indexes Source: https://github.com/berlindb/core/blob/master/README.md Defines a widget schema with columns and indexes for the BerlinDB table. Extends the BerlinDB\Database\Kern\Schema class. Column definitions specify types, defaults, and query capabilities such as searchable, sortable, cache_key, and in/not_in filters. Indexes include a primary key on id and a key on status. ```php 'id', 'type' => 'bigint', 'length' => '20', 'unsigned' => true, 'extra' => 'auto_increment', 'default' => false, 'cache_key' => true, 'sortable' => true, ), array( 'name' => 'name', 'type' => 'varchar', 'length' => '200', 'default' => '', 'searchable' => true, 'sortable' => true, ), array( 'name' => 'status', 'type' => 'varchar', 'length' => '20', 'default' => 'active', 'cache_key' => true, 'in' => true, 'not_in' => true, ), array( 'name' => 'date_created', 'type' => 'datetime', 'default' => '', 'created' => true, 'sortable' => true, ), ); public $indexes = array( array( 'type' => 'primary', 'columns' => array( 'id' ), ), array( 'name' => 'status', 'type' => 'key', 'columns' => array( 'status' ), ), ); } ``` -------------------------------- ### compare_query Source: https://github.com/berlindb/core/blob/master/skills/berlindb/references/query-row.md Documentation for the compare_query configuration array, which defines WHERE clause comparisons with structured operand specs. ```APIDOC ## compare_query ### Description Configures a WHERE clause comparison with structured operand specs on either side of the comparison. Supports column references, functions, casts, math expressions, and relative dates. ### Method N/A (configuration array) ### Endpoint N/A (configuration array) ### Parameters #### Request Body - **key** (mixed) - Required - Left operand: a column name (string) or an operand spec array. - **compare** (string) - Required - Comparison operator (e.g., '>', '=', '<'). - **value** (mixed) - Required - Right operand: a scalar value or an operand spec array. ### Operand Specs Each operand spec is an array with an `operand` marker. Supported types: - **column**: `array('operand' => 'column', 'name' => 'column_name')` - references a column. - **func**: `array('operand' => 'func', 'name' => 'FUNCTION_NAME', 'args' => array(...))` - applies an allow-listed SQL function. - **cast**: Add `'cast' => 'TYPE'` to any scalar operand to wrap in `CAST(... AS TYPE)`. `'cast' => true` on a column casts to its own type. - **math**: `array('operand' => 'math', 'operator' => '+', 'operands' => array(...))` - arithmetic expression. - **interval**: `array('operand' => 'interval', 'value' => int, 'unit' => 'DAY')` - only valid as a DATE_SUB/DATE_ADD argument. ### Allowed Functions LOWER, UPPER, LENGTH, ABS, DATE, YEAR, MONTH, DAYOFMONTH, DAYOFYEAR, DAYOFWEEK, WEEKDAY, WEEK, HOUR, MINUTE, SECOND, DATE_FORMAT, COALESCE, GREATEST, LEAST, CONCAT, CONCAT_WS, NOW, DATE_SUB, DATE_ADD. ### Allowed Cast Targets BINARY, CHAR(n), DATE, DATETIME, TIME, SIGNED, UNSIGNED, DECIMAL(p,s). ### Allowed Math Operators + - * / ### Allowed Interval Units SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR (implied by 'SECOND … YEAR'). ### Request Example ```php 'compare_query' => array( 'key' => 'updated', 'compare' => '>', 'value' => array( 'operand' => 'column', 'name' => 'created' ), ), ``` ### Response #### Success Response - **sql** (string) - The generated SQL WHERE clause (e.g., `WHERE \`updated\` > \`created\``). #### Response Example ```sql WHERE `updated` > `created` ``` ### Error Handling - Unresolvable operands fail the clause closed (match no rows, `1 = 0`). - Invalid cast target, cast on non-scalar, or `cast => true` on non-column fails closed. - Math with fewer than two operands or unknown operator fails closed. - Interval used outside DATE_SUB/DATE_ADD fails closed. ``` -------------------------------- ### The `by` Container Source: https://github.com/berlindb/core/wiki/Writing-Queries Documentation for the `by` container shorthand for grouping `__in` filters. ```APIDOC ## The `by` Container ### Description `by` is a shorthand that groups several `{column}__in` filters under one key. Each `column => value(s)` entry folds to the canonical `{column}__in` var - a scalar becomes a single-element list, an array is used as-is. ### Usage ```php $query->query( array( 'by' => array( 'status' => 3, // -> status__in => array( 3 ) 'type' => array( 1, 2 ), // -> type__in => array( 1, 2 ) ), ) ); ``` ### Parameters - `by` (array) - Required - Associative array of `column => value(s)` pairs. Each entry is folded to `{column}__in`. ### Notes - Only columns declared `in => true` can be folded; a non-in-filterable column is logged and ignored. - An empty entry value (`''`, `array()`, or `null`) is skipped. - An explicit top-level `{column}__in` always wins over the same column's `by` entry. - A non-array `by` is malformed (logged and ignored); the container itself is always consumed, never sent to the database. ``` -------------------------------- ### Grouped Aggregate Query Source: https://github.com/berlindb/core/wiki/Writing-Queries Demonstrates grouped aggregate queries with having and ordering. ```APIDOC ## Grouped Aggregate Query ### Description Computes aggregates with grouping, having filters, and ordering. ### Method query ### Parameters #### Request Body - **aggregate** (array) - Required - Map of alias to array(function, column). - **groupby** (string) - Required - Column to group results by. - **having** (array) - Optional - Filter groups by their results. - **orderby** (string) - Optional - Alias or group column to order by. - **order** (string) - Optional - Sort direction: 'ASC' or 'DESC'. ### Request Example ```php $rows = $query->query( array( 'aggregate' => array( 'revenue' => array( 'sum', 'amount' ) ), 'groupby' => 'status', 'having' => array( 'revenue' => array( '>', 1000 ) ), 'orderby' => 'revenue', 'order' => 'DESC', ) ); ``` ### Response #### Success Response Returns one row per group that satisfies the having condition, ordered as specified. ``` -------------------------------- ### Inspect parsed columns with get_columns() Source: https://github.com/berlindb/core/blob/master/skills/berlindb/references/debugging.md Inspect parsed columns and their allow_null flags to diagnose nullable data issues. Logs each column name and its allow_null property. ```php foreach ( $query->get_columns() as $column ) { error_log( $column->name . ' allow_null=' . var_export( $column->allow_null, true ) ); } ``` -------------------------------- ### Query Methods Source: https://github.com/berlindb/core/wiki/Connection-Interface Methods for preparing and executing SQL queries, retrieving variables, rows, results, and columns. ```APIDOC ## prepare ### Description Prepares a SQL query with placeholders and arguments, returning the prepared query string or null on failure. ### Method prepare ### Signature ```php public function prepare( string $query, mixed ...$args ): string|null; ``` ### Parameters - **query** (string) - Required - The SQL query with placeholders. - **args** (mixed) - Optional - Arguments to substitute into the query. ### Return Value - **string|null** - The prepared query string, or null on failure. ## query ### Description Executes a SQL query and returns the number of affected rows or a boolean indicating success. ### Method query ### Signature ```php public function query( string $query ): int|bool; ``` ### Parameters - **query** (string) - Required - The SQL query to execute. ### Return Value - **int|bool** - Number of affected rows on success, or false on failure. ## get_var ### Description Retrieves a single value from the database. ### Method get_var ### Signature ```php public function get_var( string|null $query = null, int $column_offset = 0, int $row_offset = 0 ): string|null; ``` ### Parameters - **query** (string|null) - Optional - The SQL query. Default null. - **column_offset** (int) - Optional - Column offset. Default 0. - **row_offset** (int) - Optional - Row offset. Default 0. ### Return Value - **string|null** - The value from the database, or null if not found. ## get_row ### Description Retrieves a single row from the database. ### Method get_row ### Signature ```php public function get_row( string|null $query = null, string $output = 'OBJECT', int $y = 0 ): array|object|null; ``` ### Parameters - **query** (string|null) - Optional - The SQL query. Default null. - **output** (string) - Optional - Output type (OBJECT, ARRAY_A, etc.). Default 'OBJECT'. - **y** (int) - Optional - Row offset. Default 0. ### Return Value - **array|object|null** - The row as an array or object, or null if not found. ## get_results ### Description Retrieves multiple rows from the database. ### Method get_results ### Signature ```php public function get_results( string|null $query = null, string $output = 'OBJECT' ): array|object|null; ``` ### Parameters - **query** (string|null) - Optional - The SQL query. Default null. - **output** (string) - Optional - Output type. Default 'OBJECT'. ### Return Value - **array|object|null** - An array of rows or an object, or null if no results. ## get_col ### Description Retrieves a column from the database. ### Method get_col ### Signature ```php public function get_col( string|null $query = null, int $column_offset = 0 ): array; ``` ### Parameters - **query** (string|null) - Optional - The SQL query. Default null. - **column_offset** (int) - Optional - Column offset. Default 0. ### Return Value - **array** - An array of column values. ``` -------------------------------- ### Avoid Old BerlinDB 2.x Namespaces Source: https://github.com/berlindb/core/blob/master/skills/berlindb/references/migration-2-to-3.md Avoid these old 2.x namespace references when migrating to BerlinDB 3.x. ```php BerlinDB\Database\Schema BerlinDB\Database\Table BerlinDB\Database\Query BerlinDB\Database\Row ``` -------------------------------- ### Configure index_hints for MySQL/MariaDB read queries Source: https://github.com/berlindb/core/blob/master/skills/berlindb/references/query-row.md Adds a MySQL/MariaDB index hint (USE / FORCE / IGNORE INDEX) to the base table of a read query. Takes one spec or a list of them. Index names are validated against the schema's declared indexes plus PRIMARY; 'primary' (any case) renders as the bare PRIMARY index name. A hint never changes which rows return, so it fails open: unknown index names, unknown types, or illegal USE + FORCE combinations are dropped and logged, and the query runs un-hinted. Multiple specs are declarative, not ordered; an omitted 'for' applies to all scopes on MySQL but FOR JOIN on MariaDB. Applies only to the base table of the read path, not relationship JOIN targets or the delete_items()/update_items() ID-resolution path. Excluded from the result-cache key. MySQL/MariaDB only. ```php 'index_hints' => array( 'type' => 'force', // use | force | ignore 'indexes' => array( 'idx_status' ), // declared index name(s), or 'primary' 'for' => 'join', // optional: join | order by | group by ), // -> FROM ...table a FORCE INDEX FOR JOIN (`idx_status`) ``` -------------------------------- ### Make a table global with $global property Source: https://github.com/berlindb/core/wiki/Managing-Tables-and-Upgrades Sets the $global property to true, making the table use the network/global database prefix behavior provided by the connection adapter. Site-specific tables follow the current blog prefix. ```php protected $global = true; ``` -------------------------------- ### Define WidgetsTable class for the widgets table Source: https://github.com/berlindb/core/wiki/Setting-Up-Your-First-Table Defines the WidgetsTable class extending BerlinDB\Database\Kern\Table. Sets the table name, description, prefix, version, database version key, and schema class. ```php namespace Acme\Database; use BerlinDB\Database\Kern\Table; class WidgetsTable extends Table { protected $name = 'widgets'; protected $description = 'Acme widgets'; protected $prefix = 'acme'; protected $version = '1.0.0'; protected $db_version_key = 'acme_widgets_db_version'; protected $schema = WidgetSchema::class; } ``` -------------------------------- ### Write Methods Source: https://github.com/berlindb/core/wiki/Connection-Interface Methods for inserting, updating, and deleting records in the database. ```APIDOC ## insert ### Description Inserts a row into a table and returns the number of affected rows or false on failure. ### Method insert ### Signature ```php public function insert( string $table, array $data, array|string|null $format = null ): int|false; ``` ### Parameters - **table** (string) - Required - The table name. - **data** (array) - Required - Data to insert (column => value). - **format** (array|string|null) - Optional - Format for each column. Default null. ### Return Value - **int|false** - Number of affected rows on success, or false on failure. ## update ### Description Updates rows in a table and returns the number of affected rows or false on failure. ### Method update ### Signature ```php public function update( string $table, array $data, array $where, array|string|null $format = null, array|string|null $where_format = null ): int|false; ``` ### Parameters - **table** (string) - Required - The table name. - **data** (array) - Required - Data to update (column => value). - **where** (array) - Required - Where conditions (column => value). - **format** (array|string|null) - Optional - Format for data columns. Default null. - **where_format** (array|string|null) - Optional - Format for where columns. Default null. ### Return Value - **int|false** - Number of affected rows on success, or false on failure. ## delete ### Description Deletes rows from a table and returns the number of affected rows or false on failure. ### Method delete ### Signature ```php public function delete( string $table, array $where, array|string|null $where_format = null ): int|false; ``` ### Parameters - **table** (string) - Required - The table name. - **where** (array) - Required - Where conditions (column => value). - **where_format** (array|string|null) - Optional - Format for where columns. Default null. ### Return Value - **int|false** - Number of affected rows on success, or false on failure. ``` -------------------------------- ### Run quality checks before opening a pull request Source: https://github.com/berlindb/core/blob/master/CONTRIBUTING.md Runs all quality checks before opening a pull request: Composer validation, PHPStan analysis, PHPCS, and the default test suite. ```bash composer validate --strict --no-check-publish vendor/bin/phpstan analyse --memory-limit=1G vendor/bin/phpcs bin/run-tests.sh -- --group default ```