Schema reference for table less models

If you ever need a table less model with only some manual schema information (mainly for validation and form helper to work), you might find the documentation a little bit incomplete.
There are more than just those types available.
Here is a full list.

PS: Quite some time ago, I used this for my ContactForm. This is a live example on how to use such a table less model and how validation can still be performed on these virtual fields.

How is it used?

class MySchemaLessModelName extends AppModel {
	protected $_schema = array(
	   'status' => array(
	       'type' => 'boolean',
	       'length' => 1,
	       'default' => 0,
	       'null' => false,
	       'comment' => 'some optional comment'
	   ),
	   ...   
	);
}

Why is this necessary/recommended?

By manually describing your table less fields cake you can have the same form helper magic. So the FormHelper will display a checkbox for boolean fields, and textareas for text fields etc.
Also you get all the validation (length or required for example) out of the box. You can also set up the $validate array for additional validation, of course.

Full schema reference

Boolean (tinyint 1):

'approved' => array(
	'type' => 'boolean',
	'length' => 1,
	'default' => 0,
	'null' => false,
	'comment' => 'some optional comment'
),

Best to make this field unsigned in your database (although cake cannot recognize this as of now).

Integer (int 10/11):

'clicks' => array(
	'type' => 'integer',
	'length' => 10,
	'default' => 0,
	'null' => false,
	'comment' => 'some optional comment'
),

This field can also be unsigned if there cannot be any negative values.

String (varchar 1…255):

'subject' => array(
	'type' => 'string',
	'length' => 255,
	'default' => '',
	'null' => false,
	'comment' => 'some optional comment'
),

Text (text):

'comment' => array(
	'type' => 'text',
	'length' => null,
	'default' => '',
	'null' => false,
	'comment' => 'some optional comment'
),

Decimal (float):

'amount' => array(
	'type' => 'float',
	'length' => '6,2',
	'default' => 0.00,
	'null' => false,
	'comment' => 'some optional comment'
),

Foreign key as UUID (char 36):

'user_id' => array(
	'type' => 'string',
	'length' => 36,
	'default' => '',
	'null' => false,
	'comment' => 'some optional comment'
),

Date (date)

'published' => array(
	'type' => 'date',
	'length' => null,
	'default' => null,
	'null' => true,
	'comment' => 'some optional comment'
),

Datetime (datetime)

'modified' => array(
	'type' => 'datetime',
	'length' => null,
	'default' => null,
	'null' => true,
	'comment' => 'some optional comment'
),

Enum (at least my version of it – since cake doesn’t have a core solution here)

'status' => array(
	'type' => 'integer',
	'length' => 2,
	'default' => 0,
	'null' => false,
	'comment' => 'some optional comment'
),

See static-enums-or-semihardcoded-attributes for how to use these tinyint(2) enums.

Note: All fields (except for date/datetime) are set to "not null" in the above examples (which will make the form helper and validation require those). Those can be null, though, too, if desired.

Hot Tip

If you are looking for a quick way to find this out yourself:

Create a table "apples" and an Apple model and add all types of fields you want to debug then call the model schema() like so:

debug($this->Apple->schema());

This is how I confirmed the above.

A second more persistent way would be to run the following command for such a test table (or any other real table for that matter):

cake schema generate

It will dump a schema file in your APP/Config/Schema folder with the current tables as cake schema definition.

Anything missing?

I hope I didn’t forget anything. Otherwise please correct me!

4.00 avg. rating (83% score) - 1 vote

3 Comments

  1. Thanks for the information Mark! I actually was the one who asked about this on stack overload. As I did more research using the tip you gave me and following my nose, I ended up finding a listing in the DBAL for mysql. I think this may be complete list of the type options:
    [code type=php]public $columns = array(
    ‘primary_key’ => array(‘name’ => ‘NOT NULL AUTO_INCREMENT’),
    ‘string’ => array(‘name’ => ‘varchar’, ‘limit’ => ‘255’),
    ‘text’ => array(‘name’ => ‘text’),
    ‘integer’ => array(‘name’ => ‘int’, ‘limit’ => ’11’, ‘formatter’ => ‘intval’),
    ‘float’ => array(‘name’ => ‘float’, ‘formatter’ => ‘floatval’),
    ‘datetime’ => array(‘name’ => ‘datetime’, ‘format’ => ‘Y-m-d H:i:s’, ‘formatter’ => ‘date’),
    ‘timestamp’ => array(‘name’ => ‘timestamp’, ‘format’ => ‘Y-m-d H:i:s’, ‘formatter’ => ‘date’),
    ‘time’ => array(‘name’ => ‘time’, ‘format’ => ‘H:i:s’, ‘formatter’ => ‘date’),
    ‘date’ => array(‘name’ => ‘date’, ‘format’ => ‘Y-m-d’, ‘formatter’ => ‘date’),
    ‘binary’ => array(‘name’ => ‘blob’),
    ‘boolean’ => array(‘name’ => ‘tinyint’, ‘limit’ => ‘1’)
    );[/code]
    Let me know what you think!
    James (xtraorange)

  2. well, this post was already in the publishing queue for the last 5 weeks. never came around to finishing it until you asked 🙂

    except for the fact that you used the wrong brackets ([ instead of {) it looks good. I will update the post. thanks.

  3. Yes, sorry about that. Generally with bbcode and the like it’s [], and there was no preview for me to see I had messed up… once I posted it I saw that it was wrong, but couldn’t edit it either.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.