ORM type map details
With the help of the Setup plugin you can get a lot of useful info for your current app setup.
One very new useful overview is the ORM type map.
It shows the different types and what classes they are mapped to.
Once the plugin is installed, go to
/admin/setup/backend/type-map
It will also show you the available ones that are not (yet) mapped, including all/loaded plugins.
For a screenshot see the PR.
JsonType with sane defaults
Many applications will need to at least store some data as JSON in a text column at some point.
Most developers are probably not aware that storing any field as type "json" will encode it using 0, so without any flags.
In many cases it can be better to have them stored as JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES, since the DB is UTF8 anyways.
It will also make the JSON much more readable out of the box – and might cut down a bit on character length.
For that you need to set this in your bootstrap:
\Cake\Database\TypeFactory::build('json')
->setEncodingOptions(JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
Check out the default content stored vs using proper options for the following PHP array:
[
"one" => "some/short/path/",
"two" => "Some UTF8 Äußéröugü string",
]
"No flags":
{
"one": "some\/short\/path\/",
"two": "Some UTF8 \u00c4u\u00df\u00e9r\u00f6ug\u00fc string"
}
vs above flags:
{
"one": "some/short/path/",
"two": "Some UTF8 Äußéröugü string"
}
Upgrading to different options
Changing/Adding the flags is always easy for a new or empty table column.
When changing your encoding options, you can break existing (encoded) values, though.
For this you might have to write an upgrade script/command, that takes all the values, decodes them using the old way, then encoding them
the new way and stores them again.
I wrote a script to do that for me: JsonTypeUpgradeCommand
For it to work you need to change each type mapping from json to json_string for your Table classes.
Then you define the list of models to check.
It then goes through each of those models and finds all the "json" columns and reads them.
Each column value will then be json_encoded with the new flags and compared. If there is a different it will be updated and marked for save.
For me this was the output:
Processing
- EventImports: data
- Feedback.FeedbackItems: data
- FileStorage.FileStorage: variants, metadata
EventImports: 689/689 records needed updated and got updated.
Feedback.FeedbackItems: 1/1 records needed updated and got updated.
FileStorage.FileStorage: 2450/2453 records needed updated and got updated.
The next script run should return 0 for updates needed.
EventImports: 0/689 records needed updated and got updated.
Feedback.FeedbackItems: 0/1 records needed updated and got updated.
FileStorage.FileStorage: 0/2453 records needed updated and got updated.
Tip: Run it as --dry-run/-d first to check if all would work out.
Since I couldn’t modify my plugin files on the server, I ran this script for production also from local env, by temporarily putting my live credentials into app_local.php
But again, make sure first run it in dry-run mode and also to back up your data before doing it for your prod DB.
Database overview
If you just want to quickly see the current DB size as well as all tables and a rough count of size, you can check the database part of the Setup plugin backend:
/admin/setup/backend/database
It can look something like this:
