<<css mode="next" class="sidebar"></css>> (((
- '''<a href="/docs/fActiveRecord">Class Documentation</a>''' - <a href="/api/fActiveRecord">API Reference</a> - <a href="https://github.com/flourishlib/flourish-classes/blob/master/fActiveRecord.php" target="_blank">Source Code</a>
<<toc></toc>>
- '''fActiveRecord''' - fRecordSet
- fORM - fORMColumn - fORMDatabase - fORMDate - fORMFile - fORMJSON - fORMMoney - fORMOrdering - fORMRelated - fORMSchema - fORMValidation
)))
The fActiveRecord class is an abstract class that follows the active record pattern. It provides an object-oriented interface for creating, retrieving, storing and deleting a single row (or record) in a database. All interaction with the database is done automatically without the need to write any SQL.
In addition to providing an interface to the columns in a single table, data from other database tables related via `FOREIGN KEY` constraints can be easily and efficiently retrieved. To query for and return multiple fActiveRecord objects, please see the class fRecordSet.
The following discussion is built on top of the content of ORM Conventions. Topics include database schema structure, various notation standards and information about MySQL and SQLite databases. Please be sure to read over it since it include important information, such as the fact that column and table names must use `underscore_notation` by default.
In order to use the fActiveRecord class, a database table must exist to be modeled. Below is an example users table—please note that the table has been designed to demonstrate the features of the class, not as an example of a well-designed schema.
Once a table has been created, a PHP class will need to be made. The name of the class should be in `UpperCamelCase` notation and should be a singular form of the table name. Thus for the `users` table a class `User` would be created that extends fActiveRecord.
A blank extension of the ::configure() method has been included since that is where all class functionality configuration is placed. This method is called exactly once per script execution and is the preferred location to call code to extend or set up the class, but not the individual object.
In addition to the fActiveRecord class and database table, a method to connect to the database needs to be set up. To do this, an instance of the fDatabase class needs to be passed to fORMDatabase::attach() — this is commonly done in the site’s initilization script.
The fDatabase instance attached to fORMDatabase will also be used by fRecordSet.
It is possible to change the class to table association for modeling an existing incompatible database. The static method fORM::mapClassToTable() will accept the `$class` and the `$table` to map to the class to.
This method should be called in the site-wide configuration and should not be called in the ::configure() method.
When a class models a table in a non-default schema (`public` for PostgreSQL, `dbo` for MSSQL and the username for Oracle and DB2), the static method fORM::mapClassToTable() should be called with first parameter the `$class` to map, and the second parameter, `$table`, should be in the format `schema.table`.
This method should be called in the site-wide configuration and should not be called in the ::configure() method.
When multiple databases are configured via fORMDatabase, classes can model tables on the non-`default` database by calling the method fORM::mapClassToDatabase(). The first parameter is the `$class` to map, and the second is the `$name` of the database set in fORMDatabase::attach().
Like fORM::mapClassToTable(), this method should be called in the site-wide configuration and should not be call in the ::configure() method. This method is not required for classes modeling tables in the default database — if no `$name` was provided to fORMDatabase::attach(), then the database is the default.
A new record can be created by simply creating an object without any parameters.
Existing records can be loaded from the database by passing the primary key value to the constructor. If a primary key has multiple columns, use an associative array with the keys being the columns.
It is also possible to load a record based on the values from columns in a `UNIQUE` constraint. When loading via a `UNIQUE` constraint, an associative array must be used, even if there is only a single column in the constraint.
Please note that an fActiveRecord object is a reference object. All objects of the same class will share the same data and any operations will affect all instances. Thus if the `User` object for user `3` has the first name changed, all other objects representing user `3` will also have the first name changed.
For every column in a record there are at least five different operations that can be performed. ORM plugins can change these default behaviors or add even more.
|| Action || Description || || `get` || Retrieves the column’s value || || `set` || Sets a new value for the column - empty string `` are converted to `NULL` || || `encode` || Encodes all special HTML characters — should be used when content is not trusted or for displaying in HTML tag attributes || || `prepare` || Encodes all special HTML characters, but leaves HTML tags and entities unencoded — should only be used for trusted content || || `inspect` || Returns information about the column, including information such as the data type and valid values ||
These five operations are combined with the column name into a `camelCase` method name. Below are some examples:
As mentioned on the ORM Conventions page, all columns in the database should be created using `underscore_notation`. This assumes that numbers are separated from words by an underscore, such as `address_2`. If a number is not separated by an underscore, or you are having other notation conversion issues, you man need to customize the notation conversion using fGrammar.
When dealing with date, time or timestamp columns, the `prepare` and `encode` methods require a single parameter, `$date_formatting_string`. The formatting string can be any valid `date()` formatting string, or a format name that was created with fTimestamp::defineFormat().
Floating point columns without an explicit precision require a single parameter, `$decimal_places`, when calling the `prepare` and `encode` methods. Floating point columns with an explicit precision can optionally pass an integer for `$decimal_places`.
Columns that are string columns (`VARCHAR`, `CHAR` and `TEXT`) can optionally pass `TRUE` to their `prepare` and `encode` methods to cause all email addresses and website addresses to be converted to HTML links and to cause content without block-level HTML to have newline characters converted to `
` tags.
Every column has an `inspect` method that will return an associative array of data about the column. It is also possible to retrieve a single value by passing the optional parameter, `$element`.
The fActiveRecord class supports storing both scalar values (strings, integers, booleans, etc) and objects in columns. The only special consideration with storing objects in columns is that they should have a `__toString()` method so that they can be converted to a scalar to be saved in the database.
All of the Flourish value objects include such a `__toString()` method, making them work perfectly with fActiveRecord. In fact, fActiveRecord even loads `date`, `time` and `timestamp` columns out of the database into fDate, fTime and fTimestamp objects respectively. Nothing needs to be called or configured to enable this functionality. Thus, when getting `date`, `time` or `timestamp` values from a record, be sure to treat them as fDate, fTime and fTimestamp objects.
The following methods allow manipulation of an active record object:
|| Method || Description || || ::store() || Performs ::validate() and then executes an `INSERT` or `UDPATE` query || || ::validate() || Ensures a record can be successfully saved to the database without actually doing it || || ::delete() || Deletes a record from the database || || ::load() || Reloads a record’s values from the database || || ::populate() || Values from the HTTP request will automatically be set to the various columns of the record || || ::replicate() / `clone` || Creates a copy of the record, cloning all contained objects and removing any auto incrementing primary key, can also replicate related records || || ::exists() || Indicates if a record has already been stored in the database || || ::reflect() || Returns a string containing the method signature for every method of the record ||
The ::store() method will ensure that the record can be properly saved in the database and will store it there. If any errors are found, an fValidationException will be thrown with a message that is suitable for display to end users. The validation is performed by `store()` calling ::validate().
`store()` will also automatically begin a database and filesystem transaction if they are not already in progress. This allows database and filesystem actions to be performed in extending code and child objects without the need to keep track of changes manually and revert them.
There is a single optional parameter, `$force_cascade`, that affects how related records in one-to-many and one-to-one relationships are stored. See the [#RelatedRecordsOperations] section for more details about how related records can be accessed and manipulated.
If related records (which we will call child records) have been set via an `associate` or `populate` method, and one or more of the original child records is no longer associated, and that child records has child records (grandchildren of the original) with an `ON DELETE RESTRICT` or `ON DELETE NO ACTION` clause in the `FOREIGN KEY` constraint, that child and the associated grandchildren records will all be deleted anyway. Normally an exception would be thrown indicating the child to be deleted had a grandchild record referencing it.
By default this parameter is set to `FALSE` to obey the restrictions in the database schema.
This forced cascade effect is accomplished by first finding the related records and explicitly deleting them before the originally associated record.
Validation of a record is performed based on the database schema and any additional validation rules set via the fORMValidation class, ORM plugins and fORM hooks.
The following rules are used to determine if a record is valid to store in the database:
1. All data must be compatible with the data type of the column it is being stored in 2. If a column has a `NOT NULL` constraint and does not have a `DEFAULT` value, a value other than an empty string must be set 3. If a value has a `FOREIGN KEY` constraint, the value must reference a valid value 4. If a column has a `UNIQUE` or `PRIMARY KEY` contraint, the value must be `NULL` or unique 5. If a column has a `CHECK` constraint with an `IN (...)` expression, the value must be in the list - for MySQL this holds true for `ENUM` columns 6. If a column is a `VARCHAR` or `CHAR` column, the value string length must be less than or equal to the size of the column
If any of these validation checks do not work out, an fValidationException will be thrown contains an error message suitable for end users.
There are many additional validation rules that can be added to a record via fORMValidation. If the desired functionality is not available via fORMValidation, an ORM hook can be used. Please see the Adding Functionality to fActiveRecord and Custom Validation Using a Hook section of the fORM page for details and example code.
Many of the various ORM plugins that come with Flourish (fORMColumn, fORMDate, fORMFile, fORMMoney and fORMOrdering) add additional validation rules.
Since this method is automatically called when executing ::store(), it will typically only be called in situations where storing is not possible, such as multi-page forms.
It is also possible to return an array of errors by passing `TRUE` to the first parameter of ::validate(), `$return_messages`. This prevents an exception from being thrown.
The returned array will have keys in the following format with the value being the error message.
- Errors involving a single column: the key will be the database column name - Errors involving multiple columns: the key will be the database column names joined by `,` - Errors involving related tables: the key will be the related table name - Errors involving columns in one-to-one related tables: the key will be the related table name ollowed by `::` followed by the column name (or column names joined by `,`) - Errors involving columns in one-to-many related tables: the key will be the related table name followed by `[`]`. The value of this key will be an associative array containing two keys, name and errors. The name key will have a user-friendly name for the related record and the errors key will contain an array of error messages for the related record.
Below is an example of a returned array:
If you are interested in changing the `name` of a one-to-many child record, please see the fORMRelated section Overriding Child Record Validation Names.
When passing `TRUE` to `$return_messages`, it is also possible to pass `TRUE` to the second parameter, `$remove_column_names`. This will remove the names of the columns from the error messages themselves, leaving the array with the regular keys, but anonymous messages useful for inclusion next to inputs.
The returned array would look like:
The ::delete() method will remove the record from the database. Obviously, any cascading `FOREIGN KEY` constraints could cause other records to be deleted as well. This method will throw an fValidationException if the record is referenced by another record via a `FOREIGN KEY` constraint with an `ON DELETE RESTRICT` or `ON DELETE NO ACTION` clause.
There is an optional parameter, `$force_cascade`, that when set to `TRUE` will delete all records that reference the record being deleted, even if they have an `ON DELETE RESTRICT` or `ON DELETE NO ACTION` clause in the `FOREIGN KEY` constraint. By default this is set to `FALSE` to obey the restrictions in the database schema.
This is accomplished by first finding the related records and explicitly deleting them before the original record.
The ::load() method causes the values for the record to be reloaded from the database, overwriting any values that have been changed since the object was first loaded.
The ::populate() method sets the values for a record from the fields and value contained in an HTTP request. For values to be pulled from the request, the HTML field names should be exactly the same as the database column names. Values from the form that are a blank strings are automatically converted to `NULL`.
The following HTML form when combined with the `populate()` method would cause the `first_name`, `last_name` and `email` column values to be set:
By default, ::populate() calls the individual `set` methods for each column in a record. This ensures that overridden methods are correctly called. The `set` methods in fActiveRecord will by default convert an empty string value `` to `NULL`. This treats empty HTML form input as if the user entered nothing.
Blank strings can be stored in a database columninstead of `NULL` by setting the column to `NOT NULL` and `DEFAULT `. When fActiveRecord finds a `NOT NULL` column with a `NULL` value and a non-`NULL` default, the default is substituted in place of the `NULL`.
When using checkboxes in forms to be populated, it is best to preceed the checkbox input with a hidden input using the same name but a `FALSE` value. This way, if the checkbox is unchecked, PHP gets the value from the hidden input, changing the column to a `FALSE` value. When the checkbox is checked, PHP gets the value from the checkbox input, changing the column to a `TRUE` value.
Please note that there IS a difference between setting the hidden input to a blank string value and a `FALSE` value such as `0`. As mentioned in the previous section, empty strings are converted to `NULL`, whereas a value such as `0` or `false` would not be.
Certain fORMValidation methods check to see if a column has a non-null value. If an empty string is used for the hidden input, the validation method would see that no value has been selected. If a non-null false value is used, the validation method would see that a non-null value has been selected. Depending on the requirements of the application, this distinction may be very important.
The ::replicate() method, passed no parameters is equivalent to a record being cloned. When an fActiveRecord object is cloned, all values and cache entries are copied (or cloned if the value is an object), all related records and old values are purged, any auto incrementing primary keys are erased and the record is changed to look as though it does not yet exist in the database.
It is also possible to replicate related records along with a record by passing plural class names into ::replicate(). There may be any number of class names and the classes must be for related records that are in a many-to-many or one-to-many relationship with the record. Below is an example of replicating a user and including all of their group memberships and favorites (see [#RelatedRecordsOperations] for the database schema).
If a record is has more than one relationship route to the related record class, the route should be specified between curly braces (`{` and `}`), like below:
The ::exists() method simply returns a boolean indicating if the record was loaded from the database.
This function is mostly useful when dealing with an object in a different scope than that which it was created. For instance, checking a record that has been passed into a function or an fActiveRecord hook callback.
Please note that this only checks to see if the object was constructed by passing a primary or unique key into the constructor. It can not be used with `set` methods to check and see if a record exists in the database.
The following code will NOT check to see if a user with the id 3 exists in the database. This call to exists will always return `FALSE` since the object was constructed without any parameters.
The proper way to check if a record exists in the database is to try and create an instance, catching an fNotFoundException:
From a technical perspective, for the first example to work, fActiveRecord object would have to have a mutable identity, meaning the object would change what row it represented over its lifetime. This would likely cause many other unintended side effect in common code.
Since fActiveRecord classes have dynamic functionality based on the structure of a database table, sometimes it can be useful to check and see what exactly is available for a specific class. The ::reflect() method returns a preformatted text block containing the method signature for every method, concrete and dynamic, in the class.
would output something like
It is also possible to pass a `TRUE` parameter to `reflect()` to also return the PHPDoc doc block for a method.
One of the useful features of fActiveRecord is that it automatically finds all related tables in a database schema by looking at `FOREIGN KEY` constraints. Absolutely no configuration is needed in the class.
The following tables definitions will be used for the examples below and are purposefully simple to help focus on the features, not the SQL.
For the sake of the examples below, assume that the following classes have been defined to extend fActiveRecord:
Where there are multiple relationships between two table, such as from `users` to `resources`, fActiveRecord contains optional parameters for all related record methods that allows the proper relationship route to be specified. For details, please see the [#RelationshipRoutes] section.
The detection of related tables allows for the following functionality to be provided:
When a column in a table contains a `FOREIGN KEY` constraint to another table, the two tables are in either a many-to-one or one-to-one relationship. The relationship would only be one-to-one if there was a `UNIQUE` constraint on the column with the `FOREIGN KEY`.
For instance, the `resources` table references the `users` table via the `owner` column. Because this `FOREIGN KEY` exists, it is possible to call a `create` action to instantiate the related `User` object if one exists, or an empty `User` object if none exists.
See Method Naming for info about `create` and other method verbs.
Since the `user_details` table references the `user_id` as a `PRIMARY KEY`, there can only be a single `user_details` record for each entry in `users`. This makes it a one-to-one relationship. For one-to-one relationships, it is also possible to call the [#Populating] and [#BuildingListingCountingandHas] actions just like *-to-many relationships. When working with one-to-one relationships, the related record name is singular rather than plural.
When a column is referenced by a `FOREIGN KEY` constraint in another table, the two tables involved will end up being in either a one-to-many or many-to-many relationship. Many-to-many relationships happen when a joining table is used and the `FOREIGN KEY` constraints live in the joining table.
For the examples below the one-to-many relationship between the `users` table and the `favorites` table will be used along with the many-to-many relationship between the `users` and `groups` tables.
Each user on the system can have multiple favorites simply by creating new favorites and having each one reference the same user. The `build` action will create an fRecordSet of all records in such a relationship:
The `$favorites` record set may be empty, or it may contain quite a number of records.
In situations where the related records don’t need to be created, but a primary key will suffice, it is also possible to `list` related records. This will return an array of related primary keys.
The `count` action can be called for any related record and it will return the number of related records.
If a number of records is not required, but just that related records exist, the `has` action can be used.
Both one-to-many and many-to-many relationships support the `build`, `count`, `list` and `has` actions.
In many-to-many relationships, both types of records can exist without directly referencing each other. Thus often it is necessary to take one set of records and associate it with a specific record. The `associate` action will do this, such as below where a user is being associated with every group.
`associate` methods will accept an fRecordSet, an array of fActiveRecord objects or an array of primary keys. It is also possible to call `associate` methods to associate records in a one-to-many relationship, however the `link` action discussed next only works with many-to-many relationships.
It is also possible to parse associations from the fields in an HTTP request. In that situation the HTML form fields must be named in the format `{plural_underscore_related_class}::{foreign_column}[]`. The `link` action will grab values from the HTTP request and use them for associating records in a many-to-many relationship.
Please note that ::store() must be called to actually save the new relationships between the two tables.
For records that have "child" object in one-to-many relationships, the `populate` action will call the ::populate() method for each of a list of related records. In addition, when ::store() is called on the master record, all of the child records will be saved too.
Below is an example of the kind of HTML form that is needed for creating and populating child objects. Normally the HTML for the child object would be added and removed from the page on the fly using javascript, otherwise the ::validate() method from a child object could stop the records from being saved when a child object’s values are not complete. The initial printing of the HTML form elements is normally handled server side by iterating trough the fRecordSet of related records.
Each set of inputs for child object should always contain the `PRIMARY KEY` column and the column with the `FOREIGN KEY` constraint that references this table. Any other columns should only be included if new data is desired.
Each input for a related record needs to be prefixed with the plural `underscore_notation` version of the class name plus `::`. In addition, each input should use array syntax at the end with a key shared for all inputs of the same record. The key can be any number or string, but must be the same for each input of the record. The example below uses `0`, `1` and `2` as a simple example.
The following PHP would actually create 3 `Favorite` objects and would set them to be saved when ::store() is called on the `User` object.
When there are multiple one-to-many, many-to-many or *-to-one relationships for two tables, the proper route must be specified when calling the various related record methods. The appropriate route name can be determined by viewing the Relationship Routes section of the ORM Conventions page.
Routes can be specified in any of the following methods even if only one route exists, however that route will be automatically detected if not specified.
The `build`, `count`, `create`, `link`, `list` and `populate` action methods all optionally accept the route as the first parameter.
`associate` action methods optionally accept the route as the second parameter.
When working with relationship routes in HTML forms, the relationship route name should be enclosed in `{}` directly after the foreign table name. This applies to forms being used with both the `link` and `populate` actions. Below is an example of a form for selecting the resources related to a user:
As mentioned in the [#Setup], the instance method ::configure() is called once per script execution for each classes that extends fActiveRecord. This method is designed to be the preferred place to execute any configuration code for an active record class.
The following classes provide methods that extends and change the way that fActiveRecord classes work. Each class’s documentation page will include the necessary details on how to configure each bit of functionality and how it affects the standard use of active record classes.
|| fORM || Provides functionality to override database table to class mapping, change the names used for records and column and extends fActiveRecord and fRecordSet through registering callbacks for various hooks || || fORMColumn || Allows changing the validation of columns to require email addresses or links, can configure columns to always create fNumber objects when loaded or have a column filled with a random string the first time a record is saved || || fORMDate || Can set columns to automatically save the date a record was created or last updated and can tie a timestamp column to another column to allow for saving timezones || || fORMFile || Provides functionality to automatically handle file or image uploads, including options to automatically duplicate and manipulate images || || fORMJSON || Extends both fActiveRecord and fRecordSet to have `toJSON()` methods || || fORMMoney || Can set columns to be loaded out of the database as fMoney objects and can tie fMoney columns to a second column to store currencies || || fORMOrdering || Allows configuring a column (either individually or in a group of columns) as the arbitrary ordering column for a class || || fORMRelated || Provides functionality to set the order in which related records are returned or modify what they are called (in context) || || fORMValidation || Allows setting additional validation rules (including conditional, one-or-more, many-to-many, etc), set the order for validation messages and configure `UNIQUE` constraints as case-insensitive ||