3.7.2 Creating Database Tables
While CakePHP can have datasources that aren't database driven, most of the time, they are. CakePHP is designed to be agnostic and will work with MySQL, MSSQL, Oracle, PostgreSQL and others. You can create your database tables as you normally would. When you create your Model classes, they'll automatically map to the tables that you've created.
Table names are by convention lowercase and pluralized with multi-word table names separated by underscores. For example, a Model name of Ingredient expects the table name ingredients. A Model name of EventRegistration would expect a table name of event_registrations. CakePHP will inspect your tables to determine the data type of each field and uses this information to automate various features such as outputting form fields in the view.
Field names are by convention lowercase and separated by underscores.
Model to table name associations can be overridden with the useTable attribute of the model explained later in this chapter.
In the rest of this section, you'll see how CakePHP maps database field types to PHP data types and how CakePHP can automate tasks based on how your fields are defined.
3.7.2.1 Data Type Associations by Database
Every RDMS defines data types in slightly different ways. Within the datasource class for each database system, CakePHP maps those types to something it recognizes and creates a unified interface, no matter which database system you need to run on.
This breakdown describes how each one is mapped.
3.7.2.1.1 MySQL
| CakePHP Type | Field Properties |
|---|---|
| primary_key | NOT NULL auto_increment |
| string | varchar(255) |
| text | text |
| integer | int(11) |
| float | float |
| datetime | datetime |
| timestamp | datetime |
| time | time |
| date | date |
| binary | blob |
| boolean | tinyint(1) |
3.7.2.1.2 MySQLi
| CakePHP Type | Field Properties |
|---|---|
| primary_key | DEFAULT NULL auto_increment |
| string | varchar(255) |
| text | text |
| integer | int(11) |
| float | float |
| datetime | datetime |
| timestamp | datetime |
| time | time |
| date | date |
| binary | blob |
| boolean | tinyint(1) |
3.7.2.1.3 ADOdb
| CakePHP Type | Field Properties |
|---|---|
| primary_key | R(11) |
| string | C(255) |
| text | X |
| integer | I(11) |
| float | N |
| datetime | T (Y-m-d H:i:s) |
| timestamp | T (Y-m-d H:i:s) |
| time | T (H:i:s) |
| date | T (Y-m-d) |
| binary | B |
| boolean | L(1) |
3.7.2.1.4 DB2
| CakePHP Type | Field Properties |
|---|---|
| primary_key | not null generated by default as identity (start with 1, increment by 1) |
| string | varchar(255) |
| text | clob |
| integer | integer(10) |
| float | double |
| datetime | timestamp (Y-m-d-H.i.s) |
| timestamp | timestamp (Y-m-d-H.i.s) |
| time | time (H.i.s) |
| date | date (Y-m-d) |
| binary | blob |
| boolean | smallint(1) |
3.7.2.1.5 Firebird/Interbase
| CakePHP Type | Field Properties |
|---|---|
| primary_key | IDENTITY (1, 1) NOT NULL |
| string | varchar(255) |
| text | BLOB SUB_TYPE 1 SEGMENT SIZE 100 CHARACTER SET NONE |
| integer | integer |
| float | float |
| datetime | timestamp (d.m.Y H:i:s) |
| timestamp | timestamp (d.m.Y H:i:s) |
| time | time (H:i:s) |
| date | date (d.m.Y) |
| binary | blob |
| boolean | smallint |
3.7.2.1.6 MS SQL
| CakePHP Type | Field Properties |
|---|---|
| primary_key | IDENTITY (1, 1) NOT NULL |
| string | varchar(255) |
| text | text |
| integer | int |
| float | numeric |
| datetime | datetime (Y-m-d H:i:s) |
| timestamp | timestamp (Y-m-d H:i:s) |
| time | datetime (H:i:s) |
| date | datetime (Y-m-d) |
| binary | image |
| boolean | bit |
3.7.2.1.7 Oracle
| CakePHP Type | Field Properties |
|---|---|
| primary_key | number NOT NULL |
| string | varchar2(255) |
| text | varchar2 |
| integer | numeric |
| float | float |
| datetime | date (Y-m-d H:i:s) |
| timestamp | date (Y-m-d H:i:s) |
| time | date (H:i:s) |
| date | date (Y-m-d) |
| binary | bytea |
| boolean | boolean |
| number | numeric |
| inet | inet |
3.7.2.1.8 PostgreSQL
| CakePHP Type | Field Properties |
|---|---|
| primary_key | serial NOT NULL |
| string | varchar(255) |
| text | text |
| integer | integer |
| float | float |
| datetime | timestamp (Y-m-d H:i:s) |
| timestamp | timestamp (Y-m-d H:i:s) |
| time | time (H:i:s) |
| date | date (Y-m-d) |
| binary | bytea |
| boolean | boolean |
| number | numeric |
| inet | inet |
3.7.2.1.9 SQLite
| CakePHP Type | Field Properties |
|---|---|
| primary_key | integer primary key |
| string | varchar(255) |
| text | text |
| integer | integer |
| float | float |
| datetime | datetime (Y-m-d H:i:s) |
| timestamp | timestamp (Y-m-d H:i:s) |
| time | time (H:i:s) |
| date | date (Y-m-d) |
| binary | blob |
| boolean | boolean |
3.7.2.1.10 Sybase
| CakePHP Type | Field Properties |
|---|---|
| primary_key | numeric(9,0) IDENTITY PRIMARY KEY |
| string | varchar(255) |
| text | text |
| integer | int(11) |
| float | float |
| datetime | datetime (Y-m-d H:i:s) |
| timestamp | timestamp (Y-m-d H:i:s) |
| time | datetime (H:i:s) |
| date | datetime (Y-m-d) |
| binary | image |
| boolean | bit |
3.7.2.2 Titles
An object, in the physical sense, often has a name or a title in which to refer to it by. A person has a name like John or Mac or Buddy. A blog post has a title. A category has a name.
By specifying a title or name field, CakePHP will automatically use this label in various circumstances:
- Scaffolding — page titles, fieldset labels
- Lists — normally used for
<select>drop-downs - TreeBehavior — reordering, tree views
If you have a title and name field in your table, the title will be used.
3.7.2.3 created and modified
By defining a created or modified field in your database table as datetime fields, CakePHP will recognize those fields and populate them automatically whenever a record is created or saved to the database.
The created and modified fields will be set to the current date and time when the record is initially added. The modified field will be updated with the current date and time whenever the existing record is saved.
Note: A field named updated will exhibit the same behavior as modified. These fields need to be datetime fields with the default value set to NULL to be recognized by CakePHP.
3.7.2.4 Using UUIDs as Primary Keys
Primary keys are normally defined as INT fields. The database will automatically increment the field, starting at 1, for each new record that gets added. Alternatively, if you specify your primary key as a CHAR(36), CakePHP will automatically generate UUIDs when new records are created.
A UUID is a 32 byte string separated by four hyphens, for a total of 36 characters. For example:
550e8400-e29b-41d4-a716-446655440000
UUIDs are designed to be unique, not only within a single table, but also across tables and databases. If you require a field to remain unique across systems then UUIDs are a great approach.
