De handleiding»Ontwikkelen met CakePHP»Models»Creating Database Tables

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.

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)

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)

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)

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)

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

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

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

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

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

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

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.

<< Understanding Models | Retrieving Your Data >>