Package Documentation

6. Migrations
PHPFUI\ORM Migrations

Note: Referenced namespaces in this document refer to the PHPFUI\ORM defaults.

Migrations convert the data from one version of the database to another. They are atomic and can be run individually or in a group. Even if run in a group, they can still be reverted individually.

Migration Philosophy

PHPFUI/ORM is a schema first ORM. This makes it easy to initialize the ORM from an existing database. Use migrations to alter the schema, then use a variation of the generateCRUD.php script to generate the Definition files.

Migration Table

The table migration keeps track of the current version of the database. It contains the version number and the time it was run for deployment tracking.

Running Migrations

You use the \PHPFUI\ORM\Migrator class to run migrations. An example command line script example:
```
$migrate = new \PHPFUI\ORM\Migrator(); $level = (int)($argv[1] ?? 0); if ($level) { $migrate->migrateTo($level); } else { $migrate->migrate(); } \print_r($migrate->getErrors());
```
You can include this logic in your deployment for full control of when migrations happen. The migrateTo() method will run the migrations in the correct order (up or down). You can also run migrations individually.

Migration Architecture

Migrations are located in the \App\Migration namespace. They must be a class named Migration_X where X is an integer number. Migrations must start with 1 and be contigious integers without gaps to the last migration. Gaps in the sequence are not allowed. Migrations are run by migration number and not date to avoid sync conflicts on release that plague more common migration systems. Conflicts with migrations due to branching are handled by git at merge time.

Typical migration:
```
namespace PHPFUI\ORM\Migration; class Migration_1 extends \PHPFUI\ORM\Migration { public function description() : string { return 'Create migation table'; } public function down() : bool { return $this->dropTable('migration'); } public function up() : bool { // drop the table just in case $this->down(); return $this->runSQL('create table migration (migrationId int(11) NOT NULL primary key, ran TIMESTAMP DEFAULT CURRENT_TIMESTAMP);'); } }
```
Migrations must be inherited from \PHPFUI\ORM\Migration. They must have the following methods defined:
- description() : string, returns a human readable description of what the migration does.
- up() : bool, performs SQL statements to take the database to the next version. Returns true on success.
- down() : bool, reverts the up and leaves the database in the prior version state. Returns true on success.
Running migrations from code

The \PHPFUI\ORM\Migrator class is used to run migrations automatically. Use the migrate() method to go to the latest version, or migrateTo() for a specific version. The class handles running migrations in the correct order. The getStatus() result should be shown to the user.

Caveats
- All migrations need to run reliably up and down and back up again.
- Do not depend on \App\Table or \App\Record classes, as their definition can change over time (due to migrations), so fields existing in a early migration might not exist in the most recent version of the schema. A user upgrading from an old version to a modern version may not have the same definition of a \App\Record and the migration can throw an error.
- Some migrations that are data migrations may not need a functional down() method. It can just return true. Also other database mods (like adding an index) might not need a down migration.
- All migrations should be rerunable even if they fail mid migration.
- Alter statements are cached per table. Use executeAlters() method if you need to update a table after an alter statement.
Available methods to make migrations easier
- getAllTables(string $type = 'BASE TABLE') : array
  - Return array containing table names
- getMySQLSetting(string $variable) : string
  - Get a MySQL setting
- executeAlters() : bool
  - Run all the cached alter statements. You will need to call if this directly if you need to change a table altered in the current migration
- runSQL(string $sql, array $input = []) : bool
  - Runs the current SQL statement immediately
- deleteDuplicateRows(string $table, array $keys) : bool
  - Duplicate rows with the same key values will be deleted
- dropPrimaryKey(string $table) : bool
  - Drops the primary key
- addPrimaryKeyAutoIncrement(string $table, string $field = '', string $newFieldName = '') : bool
  - Adds a primary key to the table. If $field is not specified, it will the primary key will be the table name with Id appended. If $newFieldName is not specified, it will default to $field. This method works on an existing field only.
- dropColumn(string $table, string $field) : bool
  - Drops a column if it exists
- dropTable(string $table) : bool
  - Drops a table if it exists
- renameTable(string $oldName, string $newName) : bool
  - Renames an existing table
- dropTables(array $tables) : void
  - Drops tables contained in the array
- dropView(string $view) : bool
  - Drops a view if it exists
- dropViews(array $views) : void
  - Drops views contained in the array
- addIndex(string $table, array $fields, string $indexType = '') : bool
  - Add an index on the fields in the array.
- dropIndex(string $table, string | array $fields) : bool
  - Drops an index by the name used by addIndex
- dropAllIndexes(string $table) : void
  - Drops all indexes on a table but not the primary key.
- indexExists(string $table, string $indexName) : bool
  - Tests for existance of an index on the table
- dropForeignKey(string $table, array $columns) : bool
  - Drops the foreign key on the table
- addForeignKey(string $toTable, string $referenceTable, array $columns, string $onDelete = 'CASCADE', string $onUpdate = 'CASCADE') : bool
  - Creates a foreign key on the table referencing the given table and columns.
- addColumn(string $table, string $field, string $parameters) : bool
  - Always adds a column
- alterColumn(string $table, string $field, string $parameters, string $newName = '') : bool
  - Alters a column including renaming the column if $newName is provided
8. Translations
PHPFUI\ORM Translation

If you are using validation, you need to provide a translation. This library uses the PHPFUI\Translation class, or provide a callback with the following parameter signature:
```
function translate(string $text, array $values = []) : string;
```
Due to static initialization issues on PHP, you will need to initialize the translation via the setTranslationCallback method.

You will need to copy the provided translations (in ./translations directory) into your project or specify the path to the vendor directory.

Suggested usage:
```
\PHPFUI\ORM::setTranslationCallback(\PHPFUI\Translation\Translator::trans(...)); \PHPFUI\Translation\Translator::setTranslationDirectory(__DIR__ . '/../vendor/phpfui/orm/translations'); \PHPFUI\Translation\Translator::setLocale(\Locale::getDefault());
```
1. Setup
PHPFUI\ORM Setup

PHPFUI\ORM takes a SQL first approach to models. You update the SQL schema first, then generate models from the SQL schema. You can use migrations to change the schema (recommended), or modify the schema by hand.

Database Schema Requirements

PHPFUI\ORM needs a strictly normalized database structure to work. The following conditions must be met:
- Table primary keys must be in the form of (table name)(id suffix). Examples would be member.memberId, purchase_order.purchase_order_id or even OrderDetail.OrderDetailId
- Consistant case is required for field and table names.
- Field names referencing other table's primary key must match the referenced table primary id field name. Example: purchase_order_detail.purchase_order_id would reference the parent purchase_order record.
Models Defined

PHPFUI\ORM mandates the following models and separate namespaces:
- Record - represents a record from a table
- Table - represents the entire SQL table
- Definition - describes the fields in the table
- Validation (optional) - defines validation rules for each field
Once generated, the Record, Table and Validation models will not be overwritten by the generation tools. Developers can add any logic to these classes they desire.

The Definition models should only be modified by the generation tools and not updated by hand.

Deletion of unused models should be done manually when the table is removed from the schema.

PHP Class Naming Conventions

All PHPFUI\ORM classes and namespaces use StudlyCase naming conventions. SQL table names are used the generate the PHP class names. Case is preserved except the first letter is the converted to upper case. Table names with underscores are converted to StudlyCase and underscores are removed. Field names are not affected and left unchanged.

Each table will generate 3 classes (4 if validators are generated) with the same class name, but in the specified namespaces (see below). Definition and Validation namespaces are children of the Record namespace.

Configuration

PHPFUI\ORM needs the following information to work with your code:
- Your Namespace Root - \PHPFUI\ORM::$namespaceRoot, Default: Root namespace directory (__DIR__ . '/../..')
- The Record Model Namespace - \PHPFUI\ORM::$recordNamespace, Default: 'App\Record'
- The Table Model Namespace - \PHPFUI\ORM::$tableNamespace, Default: 'App\Table'
- The Migration Namespace - \PHPFUI\ORM::$migrationNamespace, Default: 'App\Migration'
- Primary Key Suffix - \PHPFUI\ORM::$idSuffix, Default: 'Id'
If you are unable to use the defaults, it is recommended setting the\PHPFUI\ORM static fields immediately after your autoloader and before you open a database connection:

Example Setup

PHPFUI\ORM uses PHP's PDO model exclusively. You must create a valid PDO connection with the \PHPFUI\ORM\PDOInstance class and pass it to \PHPFUI\ORM::addConnection() to allow access to the database.
```
include 'vendor/autoload.php'; \PHPFUI\ORM::$namespaceRoot = __DIR__; \PHPFUI\ORM::$recordNamespace = 'App\\Model\\Record'; \PHPFUI\ORM::$tableNamespace = 'App\\Model\\Table'; \PHPFUI\ORM::$migrationNamespace = 'App\\DB\\Migration'; \PHPFUI\ORM::$idSuffix = '_id'; \PHPFUI\ORM::addConnection(new \PHPFUI\ORM\PDOInstance('sqlite:northwind.db'));
```
Migrations

Add the migrations to your migration namespace. Migration classes must be numbered starting at 1 in the format of Migration_X where X is the migration id. Breaks in the numbering sequence are not allowed.

Numbered migrations ensure all developers on a team run the migrations in the same order. This solves a major problem with time stamped migration in other popular ORM systems. Time stamping a migration means migrations may run in different orders by different developers when working on independent branches. Migrations can also be applied to different enviornments in different orders depending on branch merging and deployments. Numbered migrations are resolved at merge time and all developers have to apply them in the correct order. Developers are responsible for making sure they are on the correct migration before switching to new branches.

Running Migrations

Use the \PHPFUI\ORM\Migrator class to update the schema to any level. You can migrate up or down individually or in groups.

Generating Models

Once you have a compatible schema, or have modified a schema, you need to generate code. Use the \PHPFUI\ORM\Tool\Generate\CRUD class to generate all or individual models. Check out the scripts folder for an example.

Once Record and Table models are generated, they will not be overwritten by the generation tools. Feel free to add methods by hand for your application needs.

You should regenerate models when ever you update this library. Generation will only ever overwrite files in the Definition namespace, which should not be edited by hand. Record, Table, and Validation namespaces will only be generated if the file is missing.

Validation

You can also generate initial validators with the \PHPFUI\ORM\Tool\Generate\Validator class. Once generated, you should modify by hand for your application.

Validation rules can be found here: \PHPFUI\ORM\Validator.
10. Miscellaneous
PHPFUI\ORM Miscellaneous

SQLite Support

While this library is tested against SQLite, there are differences between MySQL/MariaDB syntax and SQLite syntax. Most notabliy is insertOrUpdate() and insertOrIgnore() which are not supported for SQLite. Use the custom SQL query support below instead.

Custom SQL Queries

PHFUI\ORM supports raw string queries for special cases or complex queries. The following static methods of \PHPFUI\ORM are supported:

getArrayCursor(string $sql = 'select 0 limit 0', array $input = []) : \PHPFUI\ORM\ArrayCursor

Returns an ArrayCursor from any SQL statement.

getDataObjectCursor(string $sql = 'select 0 limit 0', array $input = []) : \PHPFUI\ORM\DataObjectCursor

Returns an DataObjectCursor from any SQL statement.

getRecordCursor(\PHPFUI\ORM\Record $crud, string $sql = 'select 0 limit 0', array $input = []) : \PHPFUI\ORM\RecordCursor

Returns an RecordCursor from any SQL statement.

execute(string $sql, array $input = []) : bool

Execute any SQL string. Useful for queries where you don't care about the results, or don't return results. True is returned on success.

getRow(string $sql, array $input = []) : array<string, string>

Returns a single row of the first matching record or an empty array if an error

getRows(string $sql, array $input = [], int $fetchType = \PDO::FETCH_ASSOC) : array

Returns the query results in an array. Because this reads all records into memory at the same time, it is recommended to use an ArrayCusor for interation on the data instead.

getValue(string $sql, array $input = []) : string

Returne the value from the first field in the first row returned by the querry, or blank if error

getValueArray(string $sql, array $input = []) : array

Returns the data values of the first element in each row of the query.

\PHPFUI\ORM\Record Data Cleaning Helpers

The \PHPFUI\ORM\Record class provides some simple data cleaning functions you can use where needed.
- cleanUpperCase(string $field) Converts the field to all upper case
- cleanLowerCase(string $field) Converts the field to all lower case
- cleanNumber(string $field) removes all non-digits (0-9 and -) from string representation of a number
- cleanFloat(string $field, int $decimalPoints = 2) removes all non-digits (0-9, . and -)
- cleanPhone(string $field, string $regExSeparators = '\-\.') removes all non-digits (0-9) and regex separators
- cleanProperName(string $field) Properly capitalizes proper names if in single case. Mixed case strings are not altered.
- cleanEmail(string $field) Lowercases and strips invalid email characters. Does not validate email address.
Example Scripts

It is recommended to make versions of these example scripts customized to you needs:

cleanBackup.php

Removes various attributes of the backup to make it easier to restore on another server.

generateCRUD.php

Generates the models for your application based on your schema. You will want to change the PDO instance to your database connection string.

generateValidators.php

Generates the validators for your models based on your schema. You will want to change the PDO instance to your database connection string.

migrate.php

Example migration script.

Support Namespaces

The following namespaces are used in production but contains support classes or examples.
- PHPFUI\ORM\Tool used by model generation
- PHPFUI\ORM\Record contains example of migration table.
- PHPFUI\ORM\Table contains example of migration table.
3. Active Table
PHPFUI\ORM Active Table

While an Active Record represents a single row in the database table, an Active Table represents the entire table. \PHPFUI\ORM\Table allows you to find, select, update, insert and delete multiple records at a time.

Active Tables are easy to manipulate in code and free you from constructing SQL statements with plain text.

The following things can be set programmatically:
- Join
- Select
- Where
- Having
- Limit
- Union
- Group By
- Order By
WHERE and HAVING Conditions

Conditions for WHERE and HAVING are created using the \PHPFUI\ORM\Condition class.

Equal is the default for a Condition

The following code sets up an equal ('=') condition:
```
$condition = new \PHPFUI\ORM\Condition('lastName', 'Rubble'));
```
You can add an AND or OR clause to the condition easily:
```
$condition->and('firstName', 'Barney');
```
The above will produce the PDO equivalent of lastName = 'Rubble' AND firstName = 'Barney'

You can also OR in a condition like:
```
$condition->or('firstName', 'Fred');
```
The above will now produce the PDO equivalent of lastName = 'Rubble' AND firstName = 'Barney' OR firstName = 'Fred'

You can parenthesize a condition by adding to another condition:
```
$conditionA = new \PHPFUI\ORM\Condition('lastName', 'Rubble'); $conditionA->and('firstName', 'Barney'); $conditionB = new \PHPFUI\ORM\Condition('lastName', 'Flintstone'); $conditionB->and('firstName', 'Fred'); $condition = new \PHPFUI\ORM\Condition(); $condition->or($conditionA); $condition->or($conditionB);
```
The above will produce the PDO equivalent of '(lastName = 'Rubble' AND firstName = 'Barney') OR (lastName = 'Flintstone' AND firstName = 'Fred')'

Other operators

You can also make conditions with the following operators as the third parameter to \PHPFUI\ORM\Condition:
For example:
```
$conditionA = new \PHPFUI\ORM\Condition('lastName', 'R', new \PHPFUI\ORM\Operator\GreaterThanEqual()); $conditionA->and('lastName', 'S', new \PHPFUI\ORM\Operator\LessThan()); $conditionB = new \PHPFUI\ORM\Condition('lastName', 'F', new \PHPFUI\ORM\Operator\GreaterThanEqual()); $conditionB->and('lastName', 'G', new \PHPFUI\ORM\Operator\LessThan()); $condition = new \PHPFUI\ORM\Condition(); $condition->or($conditionA); $condition->or($conditionB);
```
The above will produce the PDO equivalent of (lastName >= 'R' AND lastName < 'S') OR (lastName >= 'F' AND lastName < 'G')

The In and NotIn operators require the second parameter to Condition to be an array. The Like and NotLike operators will respect the % and _ characters, but you are responsible for putting them in the correct positions.

Literal and Field classes

Sometimes you need to compare something to a SQL constant or call a function. You can use new \PHPFUI\ORM\Literal('CURRENT_TIMESTAMP()').

If you need to specify a table for a field to make it unique, you can use new \PHPFUI\ORM\Field('order.order_id', 'orderId'). The second parameter is the AS clause.

Once you have a condition

You can set the table to use a WHERE or HAVING condition:
```
$orderTable = new \App\Table\Order(); $orderTable->setWhere($whereCondition)->setHaving($havingCondition); $cursor = $orderTable->getRecordCursor();
```
Select fields

By default all fields (*) are selected for a table. You can add specific selects with addSelect. Use the second parameter for the as option. Or specify a complete Select clause with setSelectFields().

Limits and Offsets

You can specify a start offset by calling setOffset($offset). The number of records to return is setLimit($limit). For easy pagination, you can use setLimit($limit, $page) where $page is the page number (zero based) to start on. The offset will be computed by the $limit specified.

Group By and Order By

You can specify Group By and Order By with:
- addGroupBy($field), add an additional group by clause
- setGroupBy($field), resets the group by clause to the field specified
- addOrderBy($field), add an additional order by clause, defaults to ascending, use the second parameter to specify 'desc'
- setOrderBy($field), resets the order by clause, defaults to ascending, use the second parameter to specify 'desc'
Joins

Joins on a the primary key of the joined table can be easily accomplished with:
```
$orderDetailTable = new \App\Table\OrderDetail(); $orderDetailTable->addJoin('order');
```
If a more complicated on condition is required, you can pass a \PHPFUI\ORM\Condition object as the second parameter to addJoin

The third parameter is the join type (LEFT, INNER, OUTER, RIGHT, FULL, CROSS). Default is LEFT.

The forth parameter is the AS option.

Unions

Unions can be implemented by configuring another table with a matching number of selects and then calling addUnion. Unions can have full Join, Where, Group By, Having, Order By and Limit clauses.
```
$table = new \Tests\App\Table\InventoryTransactionType(); $table->addSelect('inventory_transaction_type_id', 'id'); $table->addSelect('inventory_transaction_type_name', 'name'); $table->addUnion(new \Tests\App\Table\OrderDetailStatus()); $table->addUnion(new \Tests\App\Table\OrderStatus()); $table->addUnion(new \Tests\App\Table\OrderTaxStatus()); $table->addUnion(new \Tests\App\Table\PurchaseOrderStatus()); $table->addOrderBy('name');
```
Explain

You can get the execution plan for the current \PHPFUI\ORM\Table query with the getExplainRows method. The results of this are dependent on the underlying database and can change.
4. Cursors
PHPFUI\ORM Cursors

Data Cursors are implemented on low level database cursors. They can be iterated over without having to read in all the records returned from a query into an array, which can use up large chunks of memory.

Three types of Data Cursors
Common properties
- All methods in the Iterator interface.
- count() returns the number of records returned in this specific query affected by the limit clause.
- total() returns the total number of records for the query without the limit clause.
All three cursors can iterate over the results of a join query. The RecordCursor will only have the fields from the table set, while ArrayCursor and DataObjectCursor will have all fields specified by the join.

ArrayCursor

An ArrayCursor returns an actual array for every interation representing a row from the query. This code list all customers with a first name of 'Fred' in the database.
```
$customerTable = new \App\Table\Customer(); $customerTable->setWhere(new \PHPFUI\ORM\Condition('first_name', 'Fred')); foreach ($customerTable->getArrayCursor() as $row) { echo $row['first_name'] . ' ' . $row['last_name'] . "\n"; }
```
DataObjectCursor

A DataObjectCursor returns a DataObject that uses object notation to access fields. It does not have any settable properties or methods associated with it. It implements ArrayAccess, but is not an array. It also can return related records on valid fields.
```
$customerTable = new \App\Table\Customer(); $customerTable->setWhere(new \PHPFUI\ORM\Condition('first_name', 'Fred')); foreach ($customerTable->getDataObjectCursor() as $record) { echo "Name: {$record->first_name} {$record->last_name}\n"; echo "Cell: {$record['cellPhone']\n"; }
```
RecordCursor

A RecordCursor returns a \PHPFUI\ORM\Record typed from the table. It is a fully functional Active Record. It also implements ArrayAccess, but is not an array.
```
$customerTable = new \App\Table\Customer(); $customerTable->setWhere(new \PHPFUI\ORM\Condition('first_name', 'Fred')); foreach ($customerTable->getDataObjectCursor() as $record) { echo "Name: {$record->fullName()}\n"; echo "Cell: {$record['cellPhone']\n"; $record->first_name = ucwords($record->first_name); $record->last_name = ucwords($record->last_name); $return->update(); }
```
Please Note: The \PHPFUI\ORM\Record reuses the same \PHPFUI\ORM\Record instance to conserve memory, so they will need to be cloned if added to an array or collection. DataObjectCursor and ArrayCursor return new objects.
5. Virtual Fields
PHPFUI\ORM Virtual Fields

Note: Referenced namespaces in this document refer to the PHPFUI\ORM defaults.

You can define virtual fields with get and set semantics for any \App\Record class. Virtual fields are evaluated before any database field, so you can override the database defaults if needed, or create new functionality.

Every \App\Record class has a static $virtualFields array defined. The key of the array is the name of the virtual field. The value for each virtual field is an array of strings. The first string is the virtual field class name. Subsequent parameters are are passed to the getValue and setValue methods.

Note that virtual fields are created each time they are accessed and not stored or cached in the \PHPFUI\ORM\Record object. This means you can not change the virtual field on the Record object itself. You can only assign it to the Record object, which will store the value represented by the virtual field in the object, but not the virtual field itself.

The VirtualField class has two properties that will always be defined for use by the derived class:
- $currentRecord is the current record that the virtual field should be based on.
- $fieldName the field name that the VirtualField object was created from. This is the key of the $virtualFields array in the Record class.
One To One and Parent Related Record Relationships

If a field is named the same way as a corresponding table and suffixed with the proper ID, PHPFUI\ORM will automatically generate a One To One or parent relationship for you.

A child record with an ID field of the parent record automatically has a parent relationship via the build in related record functionality.

In the northwind schema, order_detail records are children of an order record, as they have an order_id key.
```
$orderDetail = new \App\Record\OrderDetail(27); $parentOrder = $orderDetail->order;
```
Likewise, the Invoice record has a relationship to its Order record, and from the order, we can get the shipper's company field.
```
$invoice = new \App\Record\Invoice(7); echo $invoice->order->shipper->company;
```
Custom Related Record Relationships

Sometimes you can't name a related record with the name of the table. For example you might have an Employee table, but yet need to have several references to different employees in the same table. You might have the following fields which are all Employee Ids:
- salesPerson_id
- packedBy_id
- inspectedBy_id
You can make them all return employees with the following virtual field definitions:
```
class Order extends \Tests\App\Record\Definition\Order { protected static array $virtualFields = [ 'salesPerson' => [\PHPFUI\ORM\RelatedRecord::class, \App\Record\Employee::class], 'packedBy' => [\PHPFUI\ORM\RelatedRecord::class, \App\Record\Employee::class], 'inspectedBy' => [\PHPFUI\ORM\RelatedRecord::class, \App\Record\Employee::class], ]; }
```
Usage
```
echo 'Sales Person : ' . $order->salesPerson->fullName() . "\n"; echo 'Packed By : ' . $order->packedBy->initials() . "\n"; echo 'Inspected By : ' . $order->inspectedBy->first_name . "\n";
```
You can also assign records to the related record if it the right type.
```
$employee = new \App\Record\Employee(['last_name' => 'Zare']); $order->salesPerson = $employee;
```
Custom Virtual Fields

You can write custom classes to create virtual fields on any record. Here we are adding a gross virtual to the OrderDetail record.
```
class Gross extends \PHPFUI\ORM\VirtualField { public function getValue(array $parameters) : mixed { return number_format($currentRecord->unit_price * $currentRecord->quantity - $currentRecord->discount, 2); } } class OrderDetail extends \Tests\App\Record\Definition\OrderDetail { protected static array $virtualFields = [ // define a new virtual field using the above Gross class 'gross' => [Gross::class], ]; }
```
Child Records

OrderDetail children for an Order record can be defined as:
```
class Order extends \Tests\App\Record\Definition\Order { protected static array $virtualFields = [ // the OrderDetailChildren will be returned in order_detail_id order. Leave off the third array element to let SQL determine the order if you don't care. 'orderDetailChildren' => [\PHPFUI\ORM\Children::class, \Tests\App\Table\OrderDetail::class, 'order_detail_id'], ]; }
```
By default, child records will be automatically deleted when the parent record is deleted. You can disable this functionality for a specific \PHPFUI\ORM\Record class by setting the static property $deleteChildren to false, or by using your own Children class.

Usage
```
$order = new \App\Record\Order(31); foreach ($order->orderDetailChildren as $orderDetail) { echo "Gross {$orderDetail->gross} for product {$orderDetail->product->product_name}\n"; }
```
Many To Many

Many To Many relationships can be easily constructed with a junction table containing the primary keys of the two tables you want with a many to many relationship.

For the Product and Supplier tables, you need to add this:
```
class Product extends \Tests\Fixtures\Record\Definition\Product { protected static array $virtualFields = [ 'suppliers' => [\PHPFUI\ORM\ManyToMany::class, \Tests\App\Table\ProductSupplier::class, \Tests\App\Table\Supplier::class], ]; } class Supplier extends \Tests\Fixtures\Record\Definition\Supplier { protected static array $virtualFields = [ 'products' => [\PHPFUI\ORM\ManyToMany::class, \Tests\App\Table\ProductSupplier::class, \Tests\App\Table\Product::class], ]; }
```
Many To Many relationships also support adding records to the relations with a simple assignment. The added record is inserted automatically and should not be previously inserted.

Morph Many

Morph Many relationships (ala Eloquent) can be easily constructed with a junction table containing the primary keys of the two tables you want with a many to many relationship.

For the Product and Employee tables to share image records, you need to add this:
```
class Product extends \Tests\Fixtures\Record\Definition\Product { protected static array $virtualFields = [ 'photos' => [\PHPFUI\ORM\MorphMany::class, \Tests\App\Table\Image::class, 'imagable', ], ]; } class Employee extends \Tests\Fixtures\Record\Definition\Employee { protected static array $virtualFields = [ 'photos' => [\PHPFUI\ORM\MorphMany::class, \Tests\App\Table\Image::class, 'imagable', ], ]; }
```
Morph Many relationships also support adding records to the relations with a simple assignment. The added record is inserted automatically and should not be previously inserted.

Usage
```
$product = new \App\Record\Product(4); $suppliers = $product->suppliers; echo "There are {$suppliers->count()} for product {$product->product_code} - {$product->product_name}:\n"; foreach ($suppliers as $supplier) { echo $supplier->company . "\n"; }
```
Cast Virtual Field

Often you want to use PHP class instead of a native scalar type (string, int, float, bool) to make your life easier. The Carbon class is an excellent example of a widely used package. It would be nice to get and set Carbon objects instead of strings formatted to the MySQL date format.

Use \PHPFUI\ORM\Cast virtual field to accommplish this. The Cast virtual field works with a wide variety of packages, as its only requirements are to implement __toString and construct from a value.

Usage
```
class Invoice extends \Tests\App\Record\Definition\Order { protected static array $virtualFields = [ 'due_date' => [\PHPFUI\ORM\Cast::class, \Carbon\Carbon::class], 'invoice_date' => [\PHPFUI\ORM\Cast::class, \Carbon\Carbon::class], ]; } $invoice = new Invoice(20); echo 'Lead Weeks: ' . $invoice->invoice_date->diffInWeeks($invoice->due_date);
```
Type Safe Enum Support

In PHP 8.1 and above, you can add enum support easily. Assume this is your enum:
```
namespace App\Enum; enum IncludeMembership : int { case NO = 0; case NEW_MEMBERS_ONLY = 1; case EXTEND_MEMBERSHIP = 2; case RENEW_MEMBERSHIP = 3; }
```
You can define the event.includeMembership field to use enums instead of integer values.
```
class Event extends \App\Record\Definition\Event { protected static array $virtualFields = [ 'includeMembership' => [\PHPFUI\ORM\Enum::class, \App\Enum\IncludeMembership::class], ]; }
```
Your code would now look like this:
```
if (\App\Enum\IncludeMembership::NEW_MEMBERS_ONLY == $event->includeMembership)
```
You can also set and save the enum directly:
```
$event->includeMembership = \App\Enum\IncludeMembership:NO; $event->update();
```
Enum assignments are type safe. Attempting to set the enum with an incorrect type will throw an exception.
9. Transactions
PHPFUI\ORM Transactions

While PHPFUI\ORM supports the traditional beginTransaction(), commit() and rollBack() on the PDO object, it is recommended you use the \PHPFUI\ORM\Transaction class.
```
$transaction = new \PHPFUI\ORM\Transaction(); // do some stuff if ($allGood) { $transaction->commit(); } else { $transaction->rollBack(); }
```
The above creates a transaction on the current database. Commit and rollback will also be called on the correct database even if you are working on another database at the time.

The main advantage of a Transaction object, it that will will rollback any changes on a thrown exception assuming the transaction object is properly scoped.

PHPFUI\ORM Validation

Note: Referenced namespaces in this document refer to the PHPFUI\ORM defaults.

Validator is an abstract class for \App\Record validation See \PHPFUI\ORM\Validator namespace for examples.

Individual validators are listed in the table below. Validators can be combined. For example, a field can be required, and have a minlength and maxlength. Validators can have parameters. Parameters are separated by a colon (:) and then commas for each separate parameter.

Usage

$record = new \App\Record\Example(); $record->setFrom($_POST); $validationErrors = $record->validate(); if (! validationErrors) { $insertedId = $record->insert(); }

$validationErrors is an array indexed by field name containing an array of translated errors.

foreach ($validationErrors as $field => $fieldErrors) { echo "Field {$field} has the following errors:\n"; foreach ($fieldErrors as $error) { echo $error . "\n"; } }

Validator Name	Description	Parameters
alpha_numeric	Numbers and characters only (ctype_alnum)	None
alpha	Characters only (ctype_alpha)	None
bool	Must be one or zero	None
card	Credit card number (LUHN validation)	None
color	HTML color (#fff or #fafbfc, '#' is optional)	None
contains	Field must contain (case sensitive)	comma separated list of strings
cvv	Credit card cvv number	None
date	Loosely formatted date (Y-M-D)	None
dateISO	Strictly formatted ISO Date (YYYY-MM-DD)	None
datetime	Loosely formatted date (Y-M-D) followed by time format	None
day_month_year	Loosely formatted date (D-M-Y)	None
domain	Valid domain	None
email	Valid email	None
ends_with	Field must end with (case sensitive)	comma separated list of strings
enum	MySQL enum value, case insensitive	comma separated list of identifiersExample: enum:Get,Post,Put,Delete
enum_exact	MySQL enum value, case sensitive	comma separated list of identifiersExample: enum:ssl,tls
eq_field	Equal to field	field, required
equal	Value must be equal	value, required
gt_field	Greater Than field	field, required
gte_field	Greater Than or Equal to field	field, required
icontains	Field must contain (case insensitive)	comma separated list of strings
iends_with	Field must end with (case insensitive)	comma separated list of strings
integer	Whole number, no fractional part	None
istarts_with	Field must start with (case insensitive)	comma separated list of strings
lt_field	Less Than field	field, required
lte_field	Less Than or Equal to field	field, required
maxlength	Length must be less than or equal	Optional length, else MySQL limit
maxvalue	Value must be less than or equal	value, required
minlength	Must be greater than or equal	number, default field size
minvalue	Must be greater than or equal	value, required
month_day_year	Loosely formatted date (M-D-Y)	None
month_year	Loosely formatted Month Year	None
neq_field	Not Equal to field	field, required
not_equal	Value must not be equal	value, required
number	Floating point number or whole number	None
required	Field is required, can't be null or blank, 0 is OK	None
starts_with	Field must start with (case sensitive)	comma separated list of strings
time	Time (ampm or military), : separators	None
unique	Column must be a unique value	See Below
url	Valid URL (ftp, http, etc)	None
website	Valid URL (http or https only)	None
year_month	Loosely formatted Year Month	None

Field Comparison Validators

You can compare one field to another on the same \App\Record with the field validators.

gt_field
lt_field
gte_field
lte_field
eq_field
neq_field

Field validators take another field name as a parameter and perform the specified condition test. To compare against a specific value, use minvalue, maxvalue, equal or not_equal.

Unique Parameters

Without any parameters, the unique validator will make sure no other record has a matching value for the field being validated. The current record is always exempted from the unique test so it can be updated.

If there are parameters, the first parameter must be a field of the current record. If this is the only parameter, or if the next parameter is also a field of the record, then the unique test is only done with the value of this field set to the current record's value.

If the next parameter is not a field of the record, it is used as a value to match for the preceeding field for the unique test.

The above repeats until all parameters are exhausted.

Examples:

Suppose you have a table with the following fields:

name
company
division
type

You want the name to be unique per company: unique:company You want the name to be unique per division with in the company: unique:company,division You want the name to be unique for a specific type in the division: unique:type,shoes,division You want the name to be unique for a specific type and division: unique:type,shoes,division,10

NOT Operator

You can reverse any validator by preceding the validator with an ! (exclamation mark).

Example: !starts_with:/ will fail if the field starts with a /

OR Operator

You can validate a field if any one of the validators passes. Use the vertical bar (|) to separate validators. If one of the validators passes, then the the field is valid.

Example: website|starts_with:/ will validate a fully qualified http url, or a root relative url.

Optional Validation

You may need to do additional checks for a specific record type. A second parameter can be passed to the contructor which would represent the original values of the record.

You can also pass an optional method to validate to perform more complex validation. If you use an optional method, the validator will not perform the standard validations unless you specifically call the validate() method again without the optional method parameter.

Multi Validator Example

class Order extends \PHPFUI\ORM\Validator { /** @var array<string, string[]> */ public static array $validators = [ 'order_date' => ['required', 'maxlength', 'datetime', 'minvalue:2000-01-01', 'maxvalue:2099-12-31'], ]; }

2. Active Record
PHPFUI\ORM Active Record

Note: Referenced namespaces in this document refer to the PHPFUI\ORM defaults.

All database tables have a corresponding Record class in the \App\Record namespace named after the table name with an upper cased first letter.

An instance of an active record object represents on row of data in the corresponding table. All database fields are represented as publically accessible members of the active record object. For example:
```
$customer = new \App\Record\Customer(10); echo $customer->first_name . ' ' . $customer->last_name;
```
The above will read record 10 from the customer table and print the first and last name. You can also add methods to the \App\Record\Customer class to get or set common things. For example you could also use $customer->fullName() in the above example by adding the following to the Customer class:
```
public function fullName() : string { return $this->first_name . ' ' . $this->last_name; }
```
This will update the customer record.
```
$customer->first_name = 'Fred'; $customer->update();
```
Record::__construct

A Record constructor attempts to read the specified row from the table. It can be constructed 4 ways:
- int primary key value, will load object values if the primary key value exists.
- string primary key value, will load object values if the primary key value exists.
- array record is attempted to be read from database using the values of the fields provided.
- \PHPFUI\ORM\DataObject record is constructed from an existing DataObject
- null (default) constructs an empty object.
Both int and string parameters to the constructor are type checked. Calling the constructor with a parameter can be see as the same as the following, but with type checking:
```
$customer = new \App\Record\Customer(); $customer->read($value);
```
The basic CRUD methods:
- insert() or create()
  - Adds the current record to the database. If the primary key already exists in the database, the insert fails. The auto increment primary key is updated with the value inserted.
  - insert() returns the primary key value inserted, true if no primary key and the record was successfully inserted or false on error.
- insertOrUpdate() or save()
  - Will try to insert the record and on a duplicate key, will update the record with the current values.
  - insertOrUpdate() returns the same values as insert().
  - If the record only consists of primary keys, then this method is equivalent to insertOrIgnore().
- insertOrIgnore()
  - Will try to insert the record and on a duplicate key, will not update.
  - insertOrIgnore() returns the same values as insert().
- read(int | string | array $find)
  - Will try to load the first record matching the values passed in. If $find is an array, each key is used as a where condition equal to the value.
  - If not an array, read uses $find to search by primary key.
  - read() returns true on success or false if no match found
- update()
  - Returns true if the record saved to the database.
- delete()
  - Deletes the record from the database. Defined child records are also deleted. You can overload delete() to do other custom work, like deleting an associated file if desired.
  - delete() returns true on success
Other useful methods:
- empty()
  - Returns true if all current values are the defaults
- loaded()
  - Returns true if actually read from the database, rather than being created programmatically.
- reload()
  - Gets the most recent version from the database and overwrites existing data.
- setEmpty()
  - Sets all the record values to defaults.
- setFrom(array)
  - Sets fields from the key / value array passed in.
Advanced methods:
- clean()
  - Can be overridden to perform actions before any write to the database.
- setCustomValidator(string $className)
  - Overrides the default validator with this class name.
- validate(string $optionalMethod = '', ?self $originalRecord = NULL)
  - Validates the record. You can pass an optional method to validate against and original record if required by the validation.
  - Returns an array of errors indexed by field name. Empty array means the record has correctly validated.
Related Records

Related records are indicated by field name ending in the id suffix (default: 'Id'). The field name before the 'Id' must be the same as the corresponding table name. See See Virtual Fields for more advanced Related Records.

Accessing Related Records

You access the related record by the base field name (without the id suffix). The field with the id suffix is the primary key of the related record.

The following are all valid for the northwind database:
```
$orderDetail = new \App\Record\OrderDetail(40); echo $orderDetail->order->employee->company . "\n"; echo $orderDetail-product->list_price . "\n"; echo $orderDetail->purchase_order->supplier->company . "\n";
```
Null records will return the default value.

Since a related record is read from the database every time it is accessed, if you need to do more than one thing with the record, it is best of create a local copy and perform actions on the local copy to avoid multiple database reads.
```
$orderDetail = new \App\Record\OrderDetail(40); $supplier = $orderDetail->purchase_order->supplier; echo "Supplier Address:\n{$supplier->company}\nATTN: {$supplier->first_name} {$supplier->last_name}\n{$supplier->address}\n{$supplier->city} {$supplier->state} {$supplier->zip_postal_code}\n{$supplier->country_region}\n";
```
Setting Related Records

You can also set a related record. An (incomplete) example of creating a new order:
```
$customer = new \Tests\App\Record\Customer(); $customer->address = '123 Broadway'; $customer->business_phone = '212-987-6543'; $customer->city = 'New York'; $customer->company = 'PHPFUI'; $customer->country_region = 'USA'; $customer->fax_number = '212-345-6789'; $customer->home_phone = '987-654-3210'; $customer->job_title = 'Head Honcho'; $customer->mobile_phone = '123-456-7890'; $customer->state_province = 'NY'; $customer->web_page = 'http://www.phpfui.com'; $customer->zip_postal_code = '10021'; $order = new \Tests\App\Record\Order(); $order->employee_id = 9; $order->customer = $customer; $order->order_date = date("Y-m-d H:i:s"); $shipper = new \Tests\App\Record\Shipper(); $shipper->read(['company' => 'Shipping Company B']); $order->shipper = $shipper; $order->ship_name = $customer->company; $order->ship_address = $customer->address; $order->ship_city = $customer->city; $order->ship_state_province = $customer->state_province; $order->ship_zip_postal_code = $customer->zip_postal_code; $order->ship_country_region = $customer->country_region; $order->shipping_fee = 12.95; $order->taxes = 2.37; $order->payment_type = 'PO 54321'; $order->notes = 'Test Order'; $order->tax_rate = 5.25; $order->order_tax_status_id = 1; $order->order_status_id = 1; $order->insert();
```
Notice that we did not have to save the customer record. By assigning it to the order record, it was automatically saved to generate the required primary key value. The related record is not saved if it already has been assigned a primary key, it is your responsiblity to save it if you changed an existing record.

Alternate Way To Set Related Records

You can always just assign the id's directly: $orderDetail->purchase_order_id = $purchase_order->purchase_order_id;. Saving the OrderDetail record is up to you.

Other Types Of Related Records

See Virtual Fields for information on how to implement child or many to many relationships.

Multi Database Support

Related Records will always return a record from the currently selected database. Care must be taken when using multiple databases that any references to related records are done while the correct database instance is active. Cursors will continue to use the database in effect when they were created.

A future version of this libray may offer better multi database support.

Namespaces: \PHPFUI\ORM\Operator; \PHPFUI\ORM\Record; \PHPFUI\ORM\Schema; \PHPFUI\ORM\Table; \PHPFUI\ORM\Tool
Classes: PHPFUI\ORM\ArrayCursor; PHPFUI\ORM\BaseCursor; PHPFUI\ORM\Cast; PHPFUI\ORM\Children; PHPFUI\ORM\Condition; PHPFUI\ORM\ConsoleErrorLogger; PHPFUI\ORM\DataObject; PHPFUI\ORM\DataObjectCursor; PHPFUI\ORM\Enum; PHPFUI\ORM\Exception; PHPFUI\ORM\Field; PHPFUI\ORM\Literal; PHPFUI\ORM\ManyToMany; PHPFUI\ORM\Migration; PHPFUI\ORM\Migrator; PHPFUI\ORM\MorphMany; PHPFUI\ORM\Operator; PHPFUI\ORM\PDOInstance; PHPFUI\ORM\Record; PHPFUI\ORM\RecordCursor; PHPFUI\ORM\RelatedRecord; PHPFUI\ORM\StandardErrorLogger; PHPFUI\ORM\Table; PHPFUI\ORM\Transaction; PHPFUI\ORM\Validator; PHPFUI\ORM\VirtualField

Package Documentation

PHPFUI\ORM Migrations

Migration Philosophy

Migration Table

Running Migrations

Migration Architecture

Running migrations from code

Caveats

Available methods to make migrations easier

PHPFUI\ORM Translation

Suggested usage:

PHPFUI\ORM Setup

Database Schema Requirements

Models Defined

PHP Class Naming Conventions

Configuration

Example Setup

Migrations

Running Migrations

Generating Models

Validation

PHPFUI\ORM Miscellaneous

SQLite Support

Custom SQL Queries

getArrayCursor(string $sql = 'select 0 limit 0', array $input = []) : \PHPFUI\ORM\ArrayCursor

getDataObjectCursor(string $sql = 'select 0 limit 0', array $input = []) : \PHPFUI\ORM\DataObjectCursor

getRecordCursor(\PHPFUI\ORM\Record $crud, string $sql = 'select 0 limit 0', array $input = []) : \PHPFUI\ORM\RecordCursor

execute(string $sql, array $input = []) : bool

getRow(string $sql, array $input = []) : array<string, string>

getRows(string $sql, array $input = [], int $fetchType = \PDO::FETCH_ASSOC) : array

getValue(string $sql, array $input = []) : string

getValueArray(string $sql, array $input = []) : array

\PHPFUI\ORM\Record Data Cleaning Helpers

Example Scripts

cleanBackup.php

generateCRUD.php

generateValidators.php

migrate.php

Support Namespaces

PHPFUI\ORM Active Table

WHERE and HAVING Conditions

Equal is the default for a Condition

Other operators

Literal and Field classes

Once you have a condition

Select fields

Limits and Offsets

Group By and Order By

Joins

Unions

Explain

PHPFUI\ORM Cursors

Three types of Data Cursors

Common properties

ArrayCursor

DataObjectCursor

RecordCursor

PHPFUI\ORM Virtual Fields

One To One and Parent Related Record Relationships

Custom Related Record Relationships

Usage

Custom Virtual Fields

Child Records

Usage

Many To Many

Morph Many

Usage

Cast Virtual Field

Usage

Type Safe Enum Support

PHPFUI\ORM Transactions

PHPFUI\ORM Validation

Usage

Field Comparison Validators

Unique Parameters

NOT Operator

OR Operator

Optional Validation

Multi Validator Example

PHPFUI\ORM Active Record