The Flourish ORM uses a few different conventions to prevent needless configuration and to reduce typing. Most of the conventions have to do with the database schema and various notations.
The whole ORM is built in such a way that all tables you are using with it should have primary keys. Without primary keys, things may start acting weird or breaking. If you don't have primary keys for your tables, consider adding them, they're generally considered a best practice.
The Flourish ORM assumes that all database table and column names are written in
underscore_notation. Below is an example of a correctly implemented database table:
CREATE TABLE users ( first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE, password VARCHAR(100) NOT NULL );
This database table would not be properly detected:
CREATE TABLE Users ( FirstName VARCHAR(255) NOT NULL, LastName VARCHAR(255) NOT NULL, Email VARCHAR(255) NOT NULL UNIQUE, Password VARCHAR(100) NOT NULL );
underscore_notation, numbers should be 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.
Methods for fActiveRecord objects use
lowerCamelCase, like the rest of the methods in Flourish. When dealing with database columns, every method will be in the form
verbColumnName(). Below are some example of working with the
users table defined in Schema Notation:
HTML forms use
underscore_notation just as the database schema should. The method fActiveRecord::populate() will looks for input names in
underscore_notation when populating an object. Below is an example of a valid HTML form that will work with the
users table defined in Schema Notation:
<form action="" method="post" charset="utf-8"> <p> <label for="users-first_name">First Name:</label> <input id="users-first_name" type="text" name="first_name" /> </p> <p> <label for="users-last_name">Last Name:</label> <input id="users-last_name" type="text" name="last_name" /> </p> <p> <label for="users-email">Email:</label> <input id="users-email" type="text" name="email" /> </p> <p> <label for="users-password">Password:</label> <input id="users-password" type="password" name="password" /> </p> </form>
Database table names should always be plural nouns. A proper database table name would be
For existing databases, it is possible to configure a class to model a non- plural table name, or a name that is different than the class. The static method fORM::mapClassToTable() accepts a
$table and will override the default mapping. The fActiveRecord page includes an example of custom mapping.
When two tables are related in a many-to-many relationship the proper way to model the relationship is to use a table consisting of the primary keys from each of the two tables. Flourish uses the term joining table to refer to these.
Currently Flourish only works with single column
FOREIGN KEY constraints, thus these simple tables consist of exactly two columns, each of which have a
FOREIGN KEY constraint. The
PRIMARY KEY of the joining table a multi-column key containing both
FOREIGN KEY columns.
When two database tables are in more than one relationship via
FOREIGN KEY constraints, the Flourish ORM uses the term route to refer to the different ways in which the two tables are related. If two tables only have a single relationship, routes will never need to be specified. Otherwise routes will, and the following rules are used to determine the route name.
PostgreSQL, MSSQL, Oracle and DB2 all have the concept of schemas, although in Oracle and DB2 a schema is simply a specific user's set of database objects. Schemas are used for grouping tables, views, functions and other database objects.
With the Flourish ORM (and in raw SQL) a table in the non-default schema (
public for PostgreSQL,
dbo for MSSQL and the username for Oracle) is represented by
schema.table. Anywhere that a table name can be used in the ORM, a
schema.table string can also be used. This includes methods such as fORM::mapClassToTable(), for mapping a class to a table with a different name or in a different schema, and fRecordSet::build(), when specifying a related table in the
The Flourish ORM supports using multiple databases, both for vertical partition and master-slave setups. Please see the fORMDatabase documentation for more information.
The Flourish ORM is built on top of the principles of relational database systems including transactions and foreign key constraints. MySQL is built in such a way that multiple storage engines are supported to do the work of actually storing data. Choosing the right storage engine for MySQL is essential for getting the Flourish ORM to work to the best of its abilities.
Unfortunately not all of the MySQL storage engines support the necessary features such as transactions and foreign key constraints. In fact the default storage engine, MyISAM, does not support either of these features as of MySQL
however support the necessary features. Because of these feature limitations, developers should be sure to specify the InnoDB storage engine when creating tables to be used with the Flourish ORM.
Below is an example of creating an InnoDB database table in MySQL, note specifically the
ENGINE parameter after the closing
) of the table definition.
CREATE TABLE users ( customer_id INTEGER AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, email VARCHAR(100) NOT NULL UNIQUE, password VARCHAR(100) NOT NULL ) ENGINE=InnoDB;
If the InnoDB engine is not used for tables, foreign key constraints will not be created and Flourish will be unable to automatically detect the relationships between tables. In addition, any operations on multiple records will not be atomic since transactions are not supported. Thus the first record could be successfully changed, but if the second one fails, the first will not be rolled back.
SQLite databases supports the syntax for foreign key constraints, however does not enforce them as of version 3.6.4. In order to enforce the foreign key constraints, triggers must be used instead.
A slightly old, but still relevant, wiki page on the SQLite site explains how triggers can be used to enforce foreign keys. There are a couple of tools mentioned that can automatically generate the appropriate triggers.
In addition, the fDatabase::translatedQuery() method will automatically create appropriate triggers for
CREATE TABLE statements executed through an instance of the fDatabase class. If a
CREATE TABLE statement is executed that includes a
FOREIGN KEY constraint, the clauses will be automatically parsed for the relevant restrictions and the triggers will be created. Both
ON UPDATE and
ON DELETE clauses plus the actions
SET NULL are supported:
// The following will create a users table that actually // enforces the group_id foreign key constraint $db->translatedQuery(" CREATE TABLE users ( customer_id INTEGER AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, email VARCHAR(100) NOT NULL UNIQUE, password VARCHAR(100) NOT NULL, group_id INTEGER REFERENCES groups(group_id) ON DELETE RESTRICT ) ");