RSS
 

Posts Tagged ‘Database’

Emoji and CakePHP

11 Nov

You might think: Why do I care.
Well, you would be surprised – but pretty much any website with at least one form on the page will be affected here.
So anyone with even just a basic contact form should think about applying the following changes.

A wile back I got reports that one of my older websites has problems with emoji smileys and alike.
When browsing it with a mobile device and entering a native mobile smiley the emoji and all the following text would be lost when saving.
This was especially annoying with a longer conversation that you then have to retype – many people will not know why the text was truncated and will even run into the same issue again and again.

What’s the issue?

It turns out that native emoji are 4 byte characters, and the normal MySQL database, its tables and fields were all "just" utf8_unicode_ci and only supported less than that.

How to fix it?

First of all: BACK UP YOUR DATA!

With MySQL 5.5+ it is now possible to convert everything to utf8mb4_unicode_ci and then it will safely accept all those characters now, as well.

In your database.php in 2.x or app.php in 3.x you need to change the encoding from utf8 to:

'encoding' => 'utf8mb4',

Then you must convert your database, the tables and all their fields to to the new collation.
You can use my Setup plugin to automate this:

bin/cake Setup.DbMaintenance encoding

But with dry-run param -d you can also just output the SQL commands and manually run them via Migrations plugin, for example.

If you now paste emoji characters and store them, they should be visible just fine on your website now.
Also make sure emails like via contact form are working properly.

Troubleshooting

You can run into the following issue:

SQLSTATE[42000]: Syntax error or access violation: 
1071 Specified key was too long; max key length is 1000 bytes

In that case make sure you modify your fields here. With the 4byte length some fields cannot be varchar(255) anymore, but must be a bit less (250) in oder for the convertion to work out. You could also look into "innodb_large_prefix" and options to increase the limit here that way instead.

Can I use the 3.x shell for 2.x?

Sure, with a trick. See use-3-x-migrations-for-your-2-x-cakephp-app on how to provide 3.x shell access and access to my Tools and Setup shells even for 2.x apps.

Tips

If you have a larger database and tables > 1GB you definitely want to put the site into maintenance mode and do this during the night or a very inactive time-frame, as it will need quite a few minutes to complete.

It will also increase your DB size, in my case from 2.1 to 2.5 GB. But that is worth it if it does not silently truncate data anymore.

Or just use Postgres

It turns out that Postgres natively supports emoji and alike, without any need for mb4 modification.
So maybe, if you still use MySQL it could also be time to switch

Further links

 
 

Schema reference for table less models

01 May

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!

 
3 Comments

Posted in CakePHP

 

Dynamic database switching

25 Feb

Some might remember my old post about development vs. productive setup.
It was mainly about how to switch dynamically based on the current environment. This can be useful if you want to have a single DB config file which will be distributed/uploaded to all environments. In my case: synced via shell script.

Since then I rewrote it quite often and it is now published in my Setup plugin (in /Lib).
It now has some more useful goodies. The config file can be kept more DRY (Don’t Repeat Yourself) and does allow automatic test config.

The most important new feature is the automatic environment awareness in CLI mode (using path).

Basic usage

Install the plugin in your Plugin folder first.

Your database.php then uses the class this way:

App::uses('BaseConfig', 'Setup.Lib');
class DATABASE_CONFIG extends BaseConfig {
	public $default = array(
		'environment' => array('localhost', 'domain'), // define the environments - optional
		'path' => array('/some/path/to/app/'), // define the paths - optional
		'datasource' => 'Database/Mysql',
		'persistent' => false,
		'host' => 'localhost',
		'login' => 'root',
		'password' => '',
		'database' => 'table_name',
		'prefix' => 'app_', //optional (I like to use prefixes, though)
		'encoding' => 'utf8'
	);

Actually, using the BaseConfig class you can cut it down to:

public $default = array(
	'environment' => array('localhost', 'domain'),
	'path' => array('/some/path/to/app/'),
	'datasource' => 'Database/Mysql',
	'host' => 'localhost',
	'login' => 'root',
	'password' => '',
	'database' => 'my_app',
	'prefix' => 'app_'
);

encoding defaults to utf8 and persistent defaults to false.

Both environment and path are used to determine the correct config.
The first dynamically selects the correct config based on the $_SERVER['HTTP_HOST'] setting and therefore the domain you are running the application with.
The latter does the same on CLI (Command Line Interface) – using the cake console. There we have to domain and therefore need the paths to find the correct configuration. If you use the shell only locally then you can skip setting the paths, of course.

Test config

That is probably the most useful goodie. You don’t have to take care of this anymore. It will also ensure that your "live" tables will not get touched, modified, overwritten etc.

If you only defined the default config yet, the BaseConfig class will automatically set the test config to the same database, but with a different prefix (defaults to zzz_).
You can overwrite the defaults very easy, though:

public $test = array(
	'database' => 'my_app_test',
	'merge' => true,
	'prefix' => 'app_',
);

In this case we force the merge with our default config and define an own database table as well as the same prefix to completely replicate the default settins on a secondary test evironment. All other config values will be automatically merged.

Manuel fixation

You can also manually enforce a certain DB config in your bootstrap or config.php file:

Configure::write('Environment.name', 'stage');
//or
$config['Environment'] = array(
	'name' => 'stage'
);

This will make the class use the $stage config (needs to be available then, of course).

Example

So for 3 environments, lets call them development, stage and live, we can easily define them like so:

public $default = array(
	'environment' => array('localhost', 'local.domain'),
	'path' => array('/some/path/to/app/'),
	'datasource' => 'Database/Mysql',
	'host' => 'localhost',
	'login' => 'root',
	'password' => '',
	'database' => 'my_app',
);
public $stage = array(
	'environment' => array('test.domain.com'),
	'path' => array('/some/stage/path/to/app/'),
	'datasource' => 'Database/Mysql',
	'host' => 'localhost',
	'login' => 'root',
	'password' => '123',
	'database' => 'my_app_stage',
);
public $live = array(
	'environment' => array('www.domain.com', 'domain.com'),
	'path' => array('/some/live/path/to/app/'),
	'datasource' => 'Database/Mysql',
	'host' => 'localhost',
	'login' => 'root',
	'password' => '456',
	'database' => 'my_app_live',
);

Note: In all cases the test config will adjust itself to the current environment as we didn’t specify anything for it.

Final notes – some pitfalls and tips

Make sure you put the exact paths to your application in path – including the trailing slash!
If you want to confirm that everything works in CLI, as well, use my CurrentConfig shell to output the current configuration for the console:

..\lib\Cake\Console\cake Setup.CurrentConfig

It should then display the default and test DB config as well as the current Cache config.

Other solutions

There are some newer similar solutions in form of plugins available now. Check out for cakephp-environments instance.

 
No Comments

Posted in CakePHP

 

Development vs. Productive Setup

17 Aug

If you want to deploy your cakephp app you usually have to change a few lines of code. But we want to minimize that.

My example setup:

  • development: Windows (>= Vista)
  • productive: Linux (Debian)

Database Setup

Create a library file and extend this from your DATABASE_CONFIG. This way you can let your app automatically select the correct database var at runtime.

class BASE_CONFIG {
	var $environments = array('default', 'sandbox', 'online');
	var $default = array();
	/**
	 * switch between local and live site(s) automatically by domain
	 * or manually by Configure::write('Environment.name')
	 * 2009-05-29 ms
	 */
	function __construct() {
		$environment = $this->getEnvironmentName();
		if ($environment && isset($this->{$environment})) {
			$this->default = array_merge($this->default, $this->{$environment});
		}
		$this->test = $this->default;
		$this->test['prefix'] = 'zzz_';
	}
 	function getEnvironmentName() {
		$environment = (String)Configure::read('Environment.name');
		if (empty($environment) && !empty($_SERVER['HTTP_HOST'])) {
			$server = (String)$_SERVER['HTTP_HOST'];
			if (!empty($server)) {
				foreach ($this->environments as $e) {
					if (isset($this->{$e}) && isset($this->{$e}['environment']) && $this->{$e}['environment'] == $server) {
						$environment = $e;
						break;
					}
				}
			}
		}
		return $environment;
	}
}

Example for your database.php:

App::import('Lib', 'BaseConfig');
class DATABASE_CONFIG extends BASE_CONFIG {
	var $default = array(	// localhost
		'name' => 'default',
		'environment' => 'localhost',
		'driver' => 'mysqli',
		'persistent' => false,
		'host' => 'localhost',
		'login' => 'root',
		'password' => '',
		'database' => 'cake_app',
		'prefix' => 'xyz_',
		'encoding' => 'utf8'
	);
	var $sandbox = array(	// online test
		'name' => 'test',
		'environment' => 'test.domain.com',
		'login' => 'root',
		'password' => '',
		'database' => 'cake_app_test',
	);
	var $online = array(	// online productive
		'name' => 'online',
		'environment' => 'www.domain.com',
		'login' => 'root',
		'password' => '',
		'database' => 'cake_app',
	);
}

The same config file on all 3 locations will now select the one corresponding to the environment url.
You could override this, though, by using Configure::write(‘Environment.name’) – but this is not necessary if the domain doesnt change too often.

Debug Mode

Put this in your core.php (it should ALWAYS be 0 by default!):

Configure::write('debug', 0);
# Enhancement
if (!empty($_SERVER['HTTP_HOST']) && $_SERVER['HTTP_HOST'] == 'localhost') {
	Configure::write('debug', 2);
}

Debug mode is 0 for all online sites and 2 for your local development site.

Custom "Overrides"

There are many variables you need to switch from local to live apps (google.maps key, other api keys, email server credentials, …)
The quick-and-dirty solution would be something like this:

if ($_SERVER['HTTP_HOST'] == 'localhost') {
	$config['Foo'] = array(1,2,3);
} elseif ($_SERVER['HTTP_HOST'] == 'www.domain.com')) {
	$config['Foo'] = array(1,2,4);
} else {
	$config['Foo'] = array(1,5,6);
}

Very fast very ugly.

A little bit cleaner is using two config files: "configs.php" and "configs_private.php". The second one is not synched (or in SVN) – it contains passwords and environment specific content.
Include it in your bootstrap AFTER you included the default configs.

As i just mentioned, it has another upside: Beeing a environment based tmp-file it does not store any sensitive information in the SVN (or whatever other backup tool you use).
This way you can easily set up configuration "stubs" in your configs.php and insert the passwords in your private config file.

Folder Setup and .htaccess

You can use the same folder setup:

  • cake
  • app
  • vendors
    With …/app/webroot/ as "visible folder" and …/app/webroot/index.php as dispatching script.

In my htaccess file i use "!^localhost" to avoid redirects locally:

RewriteCond %{HTTP_HOST} !^www\. [NC]
RewriteCond %{HTTP_HOST} !^localhost [NC]
RewriteRule ^(.*)$ http://www.%{HTTP_HOST}/$1 [R=301,L]

This way the same file can be used for both environments.

Linux/Windows differences

Use the constant DS (= Directory Separator) anywhere you can. If you hardcode it with / (Linux) oder \ (Windows) it might break some code. Usually it doesn’t, but it is cleaner to use DS anyway.

Example:

file_put_contents(TMP.'xyz'.DS.'file.txt', 'some text')

Set up a WINDOWS constant in your bootstrap.php – this way you can dynamically decide what function to use or to not use specific lines of code (like console "exec" commands).

if (substr(PHP_OS, 0, 3) == 'WIN') {
	define('WINDOWS', true);
} else {
	define('WINDOWS', false);
}

Example:

if (WINDOWS) {
		//whatever
} else {
		exec($something);
}

Uploading your changes

I use a shell tool i wrote – which uses "rsynch" and only uploads the delta (changes made so far).
Make sure you DON’T upload any tmp stuff or even worse, override environment based files like uploads, cached files etc.
Using batch scripts you can usually exclude some directories.

Final Tips

Remember that linux needs explicit folder permissions. So you need to manually (or per batch script) set /tmp to 777 recursivly as well as any other folder which you want to write into from your scripts.

Also clear the cache after updating! Otherwise your model relations as well as some constants will be outdated and cause errors or complete failure.

 
6 Comments

Posted in CakePHP