Bake can't handle column name containing ':'?

My MariaDB doesn’t seem to have a problem with column names containing a ‘:’.

Using the bake-command doesn’t show any problems either.

But when trying to use the baked controller, there’s a database-error.

The hint is: If you are using SQL keywords as table column names, you can enable identifier quoting for your database connection in config/app.php.

Can imagine the ‘:’ is somewhere interfering with the SQL, but the section on identifier-quoting, can’t figure out if that’s the problem.

What will be the way to get things working?

If its an option, restructure the table and remove the special character. Its a little disturbing its there in the first place!

Otherwise, if that’s no option, you’re probably up for hand writing the controller, entity, table and templates - as I doubt anyone will be jumping to fix the bake command to handle a colon.

I would definitely recommend you to follow the CakePHP naming conventions according to how tables and columns should be named.
https://book.cakephp.org/4/en/intro/conventions.html#database-conventions

Only if you follow these CakePHP and the Bake Plugin can do 100% of their magic and help you as much as possible.
If you can’t change that you will have to manually adjust your baked files to what is present in the database since bake expects you to follow the convention.

Ok, it’s good to know bake can’t handle these. So I don’t have to look for some changes in configs etc.

Database contains data about fatty acids, the ‘:’ is just part of their name.

IMHO there’s nothing written about what characters not to use in column names …

Well its generally good practice to not contain special characters in databases, tables, index, columns, aliases, views etc. at least in MySQL.
See MySQL :: MySQL 5.6 Reference Manual :: 9.2 Schema Object Names

An identifier may be quoted or unquoted. If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it.

Permitted characters in unquoted identifiers:

  • ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)

It could be that this is different for your used DBMS but it just (as you have seen) creates unforseen problems.

You can enable that for your Datasource via setting that flag in your config to true

If you overwrite that via your app_local.php then add that key=>value to that files datasource.

But also as the comment says its not recommended to do so if you can avoid it because escaping all the identifiers for every query decreases the performance

1 Like

Don’t have database error anymore, so the original hint cakephp gave was correct:

So, it seemed that configuration would solve the problem after all, but …

now bake is giving me a problem within a view:

<td><?= $this->Number->format($individual->data_1) ?></td>
<td><?= $this->Number->format($individual->data_2) ?></td>
<td><?= $this->Number->format($individual->data:3) ?></td>

The ‘:’ is off course giving a syntax error now: unexpected ‘:’, expecting ‘)’

Do you still have a column with a : in the name there, or did you fail to re-bake after updating your schema?

Well first of all thank you for showing us a special case where the provided logic of the bake plugin breaks.
We are currently in the process to give a better warning/error if someone tries to bake files based upon such a database schema.
See change validateNames logic to prevent special chars in column names by LordSimal · Pull Request #806 · cakephp/bake · GitHub

But we are unfortunately not going to change/support your usecase since it goes against the way cakephp works. Sorry :sweat_smile:

All I can say is that you should instead replace all your : with _ in your column names, rebake all the files and you will have a better time

If you can and do restructure, before you do anything else (after the restructure) run this command: -
bin/cake cache clear_all
and save yourself a world of hair-pulling!

In the past I already had my part of hair-pulling on the caches, don’t think they’re the issue here.

On my testbed, the shown example, I restructured back and forth etc., and the results stay consistent.

In this case I still have the ‘:’ in the name.

On second thoughts, the bake doesn’t seem the problem, it outputs the right code as far as my little knowledge goes.

First problem was that the query builder couldn’t handle the ‘:’ well. With the ‘‘quoteIdentifiers’ => true’ the query builder seems to produce well defined output.

With the first problem solved, the second gets to the surface.Where is the real problem, a ‘parsing’ problem, skip the ‘:’ as a delimiter, escape it. Or can’t the variable in the array have a name with a ‘:’? Basic stuff for most of you, not for me.

So, when not baking but coding by hand I would come up with the same code (I think/hope), and having the same problem.

Only when it’s possible to treat the ‘data:3’ as one part by adding some tokens or whatever, in that case the bake has an issue. Well, along my line of thought with my little knowledge.

Wasn’t expecting some fundamental issues here with some ‘:’ :slight_smile:

As stated before, the ‘:’ is within the name of fatty acids:

The database has imports/exports/other applications, it’s no option to rename the column-names, all have to be changed, some by third parties. This is not going to happen (costs, time, riscs etc.)

An other solution I used in the past was having a database-view with renamed columns. Always had a lot of discussion with DBA’s and a lot of paperwork to do to get them in place. And could take long time or short time, the view would give trouble (update’s etc.)

Next in line was keeping the renaming within application in some way or another. Apart from the extra work, long-term maintenance gets a problem. It’s not the way to go.

Any other options?

Well, apart from being my usecase, imho it’s a core cakephp question in to what extend it keeps up with database-features (and if not doing so for 100%, it’s ok, but you want to know).

The fact that it’s possible to use ‘‘quoteIdentifiers’ => false/true’ indicates that cakephp is aware of the feature, the query-builder seems to adjust it’s behaviour.

There’s seems to be one more step to take (and it’s ok if you have to change the baked code by hand).

Well the base problem here is the fact, that CakePHP maps PHP Object properties to column names.
And since $object->f18:0_g is not a valid property just by PHP syntax standard you would have to somehow map custom property names to your columns. But I am currently not aware of how to do that.

Ah, that’s the root of the problem, thanks.

So I’m searching for a solution for an issue between MariaDB and PHP, will not be the first person to have this issue, will find something (the not-wanted databaseview will be plan b).

Well you can’t use the ORM (which maps your table data to entities in PHP) but you can for sure just use the query builder as normal and just disable the hydration (converting sql data into php objects)
https://book.cakephp.org/4/en/orm/query-builder.html#fetching-arrays-instead-of-entities

With that you don’t get entities and instead just the data as an array as it is present in the DB.
Now you just have to manually adjust your templates to access the data via array keys instead of properties.

Little known fun fact, PHP actually accepts all sorts of characters for properties, you just cannot use regular accessors, but would have to use curly braces (aka variable property access):

$obj = new stdClass();
$obj->{'foo:bar'} = 42;

var_dump(get_object_vars($obj));
var_dump($obj->{'foo:bar'});

This will work fine. With CakePHP entities you can use curly braces too, or, alternatively, you can use the get/set methods:

$entity->{'foo:bar'} = 42;
var_dump($entity->{'foo:bar'});

$entity->set('foo:bar', 24);
var_dump($entity->get('foo:bar'));

Being aware of that and identifier quoting, is there still an issue? And if so, what is it exactly?

Well I didnt know that :sweat_smile:

Yeah, I guess it would’ve been best for humanity if it would’ve been kept a secret. It’s ugly, error prone, hard to inspect, etc… but oh well, PHP gonna do what PHP does :slight_smile:

It should absolutely be possible to work with such identifiers in CakePHP, but it will definitely involve some extra work, and one shouldn’t rely on Bake being able to support it. I guess it would be possible to even make bake work with it, but such column names are usually just legacy baggage, and seeing how PHP slowly starts discontinuing dynamic properties, this isn’t something that the framework should waste time on.

1 Like
`column`, `column2', etc

I had a problem also with a reserved word, just tick column names.