---
name: sqlp
description: Guide for writing SqlP query models (.sql files) that compile into Vvveb\Sql\*SQL classes — procedure declarations, IN/OUT parameters and bind variables, result shaping (_ alias, array_key/array_value), macros (@IF, @FILTER, @LIST, @KEYS, @EACH, @SQL_COUNT, @ESC), cross-query @result.* references, import() reuse, multi-engine sync and the offline compile/lint harness. Use when creating or editing sql/mysqli|pgsql|sqlite/*.sql files, adding plugin models, debugging generated models in storage/model/, or calling models from controllers.
---

# SqlP

SqlP (SQL Procedures) is a bridge between PHP and SQL. Queries are written as stored-procedure-like blocks in plain `.sql` files — never as strings inside PHP — and SqlP compiles each file into a PHP model class with one method per procedure.

```
admin/sql/mysqli/product.sql  ──SqlP──►  storage/model/admin/productsql.mysqli.php  ──►  Vvveb\Sql\ProductSQL
```

Source code: `system/sqlp/` (`sqlp.php` parser/generator, `lexer.php` macro tokenizer, `config.php` regexes + macro→PHP map, `mysqli.tpl` / `pgsql.tpl` / `sqlite.tpl` class templates).

## File locations & compilation lifecycle

Query files live per DB engine:

| Location | Namespace | Notes |
|---|---|---|
| `app/sql/<engine>/*.sql` | `Vvveb\Sql\...` | frontend models |
| `admin/sql/<engine>/*.sql` | `Vvveb\Sql\...` | admin models; **fallback** if a file is missing in `app/sql/` |
| `plugins/<slug>/sql/<engine>/*.sql` | `Vvveb\Sql\Plugins\<Name>\...` | plugin models |

`<engine>` is one of `mysqli`, `pgsql`, `sqlite` (`DB_ENGINE` constant).

Compilation happens automatically in the autoloader (`system/core/startup.php`):

1. Any requested class ending in `SQL` (e.g. `Vvveb\Sql\ProductSQL`) is resolved to its source `.sql` file.
2. If missing or stale (source `.sql` mtime newer than generated file), the model is regenerated into `storage/model/<app>/<path>/<model>sql.<engine>.php`.
3. `SQL_CHECK = true` (dev, see `env.php`) forces the mtime check on every request; on production the check is skipped when the generated file exists.

Never edit files under `storage/model/` — they are overwritten.

### Generated class structure

Each procedure becomes one public method taking a single `$params` array; `@FILTER` usage additionally populates `$filters` with live column metadata (used by `validate()`):

```php
<?php // storage/model/admin/productsql.mysqli.php (generated)
namespace Vvveb\Sql;

use \Vvveb\System\Db;

class ProductSQL {
	private $db;
	protected $filters     = [ /* vvveb_product => [column => [nullable, default, ...]] */ ];
	protected $paramTypes  = [ /* method => [param => i/d/s/a/b] */ ];

	public function __construct() { $this->db = Db::getInstance(); }

	public function validate($data, $method, $table = '', $ignoreMissing = false) { /* ... */ }

	function get($params = []) { /* built from the get() procedure body */ }
	function edit($params = []) { /* ... */ }
	function add($params = []) { /* ... */ }
	function getAll($params = []) { /* ... */ }
	function delete($params = []) { /* ... */ }
}
```

A complete real-world model demonstrating every feature together (get/edit/add/getAll/delete with `@FILTER`, `@LIST`, `@KEYS`, `@SQL_COUNT`, multi-query `@result.*` chains and the `_` alias) is `admin/sql/mysqli/product.sql` — copy patterns from it.

### Using models from PHP

```php
$product = model('product');            // Vvveb\Sql\ProductSQL
$message = model('Plugins\\ContactForm\\message'); // Vvveb\Sql\Plugins\ContactForm\MessageSQL

// every method takes ONE array argument keyed by the IN parameter names
$data = $product->get(['product_id' => 3]);
$data = $product->getAll(['site_id' => 1, 'start' => 0, 'limit' => 3]);
$product->add(['product' => ['name' => 'Product 1', 'price' => 100]]);
$product->edit(['product' => ['name' => 'X'], 'product_id' => 1]);   // returns affected_rows
$product->delete(['product_id' => [1, 2, 3]]);                       // ARRAY param → IN (...)

// validate() checks data against table column metadata collected from @FILTER
if (! $product->validate($data, 'add')) { ... }
```

The filename maps to the class name: `product.sql` → `ProductSQL`, `order_log.sql` → `OrderLogSQL` (underscores → CamelCase).

## Procedure anatomy

Each procedure becomes one public method. Multiple procedures per file are allowed; redefining a name overrides the earlier one. The `CREATE` keyword is optional (`PROCEDURE getAll(...)` works too).

```sql
CREATE PROCEDURE getAll(
    -- IN parameters: accepted as keys of the $params argument
    IN  language_id INT,
    IN  site_id     INT,
    IN  search      CHAR,
    IN  start       INT,
    IN  limit       INT,
    -- OUT parameters: declare one PER query, in query order
    OUT fetch_all,  -- rows of query #1
    OUT fetch_one   -- single value of query #2
)
BEGIN
    SELECT * FROM product AS product WHERE site_id = :site_id LIMIT :limit OFFSET :start;

    SELECT count(*) FROM product WHERE site_id = :site_id;
END
```

Rules:

- Body is delimited by `BEGIN … END`. Statements are split on `;`, so **every statement must end with `;`**.
- `-- comments` anywhere are stripped before parsing; they are safe documentation.
- The body parser stops at the first `END` **not followed by `@`**. Never use a bare `END` keyword inside the body (e.g. `CASE … END`) — it terminates the procedure early. Use `IF(:x, a, b)` functions instead of `CASE`.
- Table names get `DB_PREFIX` applied automatically — write unprefixed names (`product`, not `vvveb_product`). This covers `SELECT … FROM`, `JOIN`, `INSERT INTO`, `UPDATE`, `CREATE TABLE`.

### IN parameters & types

| Declared type | Bind type |
|---|---|
| `INT` | `i` (int) |
| `DOUBLE`, `DECIMAL` | `d` (double) |
| `BLOB` | `b` |
| `ARRAY` | special (see below) |
| anything else (`CHAR`, `VARCHAR`, …) | `s` (string) |

Parameters are referenced in SQL as `:name`; nested arrays use dot notation — `:order.products` binds `$params['order']['products']`.

**ARRAY parameters**: an `IN ids ARRAY` used as `IN (:ids)` is expanded at execution time by the driver (`paramsToQmark()`) into individual bound placeholders (`IN (?, ?, …)` / `IN ($1, $2, …)`), with each value typed individually. Pass a flat array of scalars from PHP.

### OUT parameters & result shapes

OUT parameters select how each query's result is fetched, **positionally matched to the queries in the body** (query #1 ↔ first OUT param, query #2 ↔ second OUT param, …). Comments after them (`OUT fetch_all, -- products`) are purely documentation.

| OUT name | Result |
|---|---|
| `fetch_all` | all rows (default if no OUT declared) |
| `fetch_row` | first row as associative array (use with `LIMIT 1`) |
| `fetch_one` | first column of first row (e.g. `count(*)`) |
| `insert_id` | last insert id of the INSERT |
| `affected_rows` | number of affected rows for UPDATE/DELETE |

The method returns the accumulated results array (or null if empty). All queries share one result array — see *Result shaping* below for how they merge.

## Result shaping

Every query stores its output under a key derived from its main table name/alias (prefix stripped), so multi-query procedures merge cleanly:

```sql
-- product                          ← key "product"
SELECT product.* FROM product AS product WHERE ...;
-- subscription                     ← key "subscription"
SELECT s.* FROM subscription AS s WHERE ...;
```

```php
['product' => [...], 'subscription' => [...]]
```

### `_` alias — merge into top level

```sql
SELECT * FROM message AS _ WHERE message_id = :message_id LIMIT 1;
```

returns `[...row fields at top level...]` instead of `['message' => [...]]`. With `_`, values from multiple queries are merged with `+` into the same level.

### `array_key` / `array_value` aliases

By default rows come back numerically indexed under the table key:

```sql
SELECT stock_status_id, name FROM stock_status AS stock_status_id;
```
```php
['stock_status_id' => [0 => ['stock_status_id' => 5, 'name' => 'status 1'], 1 => [...]]]
```

Compact list-shaped results (great for lookups) via the special aliases:

```sql
SELECT stock_status_id AS array_key, name AS array_value FROM stock_status;
```

```php
[5 => 'status 1', 8 => 'status 2']
```

With only `array_key`, rows stay intact but are keyed by that column:

```php
['stock_status_id' => [5 => ['stock_status_id' => 5, 'name' => 'status 1'], 8 => [...]]]
```

Aliases work on any column aliased as `AS array_key` / `AS array_value`.

## Macros

Macros run at compile time and expand to PHP string-building code inside the generated method. Conditions can use any PHP expression; `:param` inside macros becomes `$params['param']` (dot notation supported).

### @IF / @ELSE / END @IF

Conditional SQL fragments:

```sql
WHERE 1 = 1
@IF isset(:product_id)
THEN
    AND product_id = :product_id
@ELSE
    AND product_id = 0
END @IF
```

```sql
@IF isset(:search) && !empty(:search)
THEN
    AND name LIKE :search
END @IF
```

Always guard optional fragments with `isset()`/`empty()` — a parameter that is not passed is substituted as the literal SQL keyword `null`, which is almost never what you want in a condition or comparison.

### @FILTER — whitelist array data against table columns

Reads live column metadata for the table and keeps only real columns (via `$db->filter()`), optionally filling defaults for missing ones:

```sql
-- simple: filter :message against table "message" columns
@FILTER(:message, message)

-- assign result to another variable
:message_data = @FILTER(:attribute_group, attribute_group)

-- flags: addmissing (fill column defaults), array (filter each element of the array)
:products = @FILTER(:order.products, order_product, false, true)
```

Signature: `[@return =] @FILTER(:data, table[, addmissing][, array])`. Tables referenced here also feed the generated `validate()` method.

### @LIST — SET clause for UPDATE

Expands an associative array into quoted `column = :placeholder` pairs:

```sql
UPDATE message SET @LIST(:message) WHERE message_id = :message_id
```

generates ``SET `name` = :message['name'], `email` = :message['email'] …``.

### @KEYS — column list for INSERT

```sql
INSERT INTO message ( @KEYS(:message) ) VALUES ( :message );
```

generates ``( `name`, `email` )`` — pair it with `( :message )` which expands the values positionally. Quotes follow the engine (`backticks` for mysqli/sqlite, `"double"` for pgsql).

Worked example inside an `@EACH` loop — given from PHP:

```php
$product_attribute = [
    0 => ['name' => 'Product 1', 'price' => 30],
    1 => ['name' => 'Product 2', 'price' => 50],
];
```

per iteration `:each` binds the row values (`'Product 1', '30'`) and `@KEYS(:each)` expands the row keys as column names (`` `name`, `price` ``):

### @EACH — loop over an array

Repeats the remainder of the current statement once per element, binding `:each` (value) and `:each_key` (key). For associative elements combine with `@KEYS(:each)`:

```sql
@EACH(:products)
    INSERT INTO order_product ( order_id, @KEYS(:each) )
    VALUES ( @result.order, :each );
```

Note `@EACH` applies until the statement's closing `;` — one INSERT per iteration.

### @SQL_COUNT — count a previous query

Turns the previous SELECT into a COUNT subquery (strips `LIMIT`/`ORDER BY`, replaces the select list):

```sql
SELECT product_id, name FROM product WHERE vendor_id = 1 LIMIT :limit OFFSET :start;

SELECT count(*) FROM (
    @SQL_COUNT(product.product_id, product)
) as count;
```

First argument is the counted column expression, second is the main table name.

### @ESC — sanitize identifier-ish input

Outputs the parameter with all non-word characters stripped:

```sql
ORDER BY @ESC(:sort_by)
```
Use only for parameters that can't be binded like ORDER BY values
 

## Cross-query references: @result.*

Values from earlier queries in the same procedure (insert ids, selected columns) are available as `@result.key`, where `key` is the earlier query's table alias (see *Result shaping*):

```sql
INSERT INTO attribute_group ( @KEYS(:attribute_group) ) VALUES ( :attribute_group );

INSERT INTO attribute_group_description ( attribute_group_id, language_id, name )
VALUES ( @result.attribute_group, :language_id, :name );
```

- After `INSERT INTO t`, `@result.t` holds the insert id.
- After a SELECT aliased `t`, `@result.t.column` accesses nested values; with the `_` alias use `@result.column` directly.
- Guard with `@IF isset(@result.admin) THEN … END @IF` when the previous step may not have run.

## import() — sharing SQL between engines/apps

Inlines another `.sql` file before parsing, so engine-specific files can reuse a common definition instead of copy-pasting:

```sql
-- app/sql/mysqli/comment.sql
import(/admin/comment.sql);
```

- `/admin/file.sql` → `DIR_ROOT/admin/sql/<engine>/file.sql`
- `/plugins/contact-form/message.sql` → plugin path
- `file.sql` (relative) → `DIR_SQL . file.sql`

Missing imports resolve to an empty string silently.

## Multi-engine notes

- Keep the three directories in sync: `sql/mysqli/*.sql`, `sql/pgsql/*.sql`, `sql/sqlite/*.sql` — the runtime loads only `DB_ENGINE`'s copy.
- Quote identifiers with backticks for mysqli/sqlite, double quotes for pgsql.
- Use `@SQL_LIMIT` instead of engine-specific LIMIT syntax.
- When a query must differ fundamentally per engine, keep separate files and mirror procedure signatures exactly.

## Verification workflow (no DB server needed)

Models compile fully offline — generation only needs the parser plus stubbed constants. Pattern:

```php
// /tmp/t_<model>_<engine>.php — must be a plain PHP file, no shebang/BOM
<?php
namespace {
    define('DB_PREFIX', 'vvveb_');
    define('DB_ENGINE', 'mysqli');   // mysqli | pgsql | sqlite
    define('DIR_ROOT', '/home/www/vvveb/vvveb/');
    define('DS', DIRECTORY_SEPARATOR);
}

// stub lives under Vvveb\System because sqlp.php does `use Vvveb\System\Db`
namespace Vvveb\System {
    class Db {
        public static function getInstance() { return new self(); }
        public $quote = '`';             // use '"' for pgsql
        public $prefix = 'vvveb_';
        // return column defs (name => ['n' => bool nullable, 'd' => default, ...]) to test @FILTER
        public function getColumnsMeta($table) { return []; }
    }
}

namespace {
    require DIR_ROOT . 'system/functions.php';  // provides Vvveb\dotToArrayKey()
    require DIR_ROOT . 'system/sqlp/lexer.php';
    require DIR_ROOT . 'system/sqlp/sqlp.php';

    $sqlp = new \Vvveb\System\Sqlp\Sqlp();
    $sqlp->parseSqlPfile('/path/to/model.sql');
    file_put_contents('/tmp/generated.php', "<?php \n" . $sqlp->generateModel());
    passthru('php -l /tmp/generated.php');   // syntax-check the generated model
}
```

Run it for each engine you touched and lint the output. To lint a whole directory at once, glob the `.sql` files and loop the parse/generate/lint steps. To smoke-test logic without a server, instantiate the generated class with a fake `Db` driver (stub `execute()`, `insert_id`, etc.) and call its methods.

Checklist before committing changes to `.sql` files:

1. Every procedure body ends with a lone `END`; statements end with `;`; no bare `END` keywords inside bodies.
2. OUT parameter count/order matches the number of queries.
3. Optional params guarded by `@IF isset(...)`; pagination uses `@SQL_LIMIT`; listing queries ship an `@SQL_COUNT` companion.
4. All three engines have matching files (`git status` shows untracked siblings).
5. Generated models pass `php -l` for every engine via the harness above.
