Ask your Symfony questions! Pay money and get answers fast! (more info)

How to handle db columns with reserved names like 'abstract' Symfony

  • SOLVED

I'm working on an old site. The database is MySql. Some of the columns in some of the tables are reserved keywords in PHP, such as "Abstract" and "Protected". These become errors in the model classes. I also get an error when building the models:

[Exception in XML parsing]
exception 'EngineException' with message 'Error setting up column 'protected': Cannot map unknown Propel type '' to native database type.' in /usr/share/php/symfony/vendor/propel-generator/classes/propel/engine/database/model/Column.php:190
Stack trace:
#0 /usr/share/php/symfony/vendor/propel-generator/classes/propel/engine/database/model/XMLElement.php(47): Column->setupObject()
#1 /usr/share/php/symfony/vendor/propel-generator/classes/propel/engine/database/model/Table.php(342): XMLElement->loadFromXML(Array)
#2 /usr/share/php/symfony/vendor/propel-generator/classes/propel/engine/database/transform/XmlToAppData.php(216): Table->addColumn(Array)
#3 /usr/share/php/symfony/vendor/phing/parser/AbstractSAXParser.php(77): XmlToAppData->startElement('column', Array)
#4 [internal function]: AbstractSAXParser->startElement(Resource id #793, 'column', Array)
#5 /usr/share/php/symfony/vendor/phing/parser/ExpatParser.php(129): xml_parse(Resource id #793, '<?xml version="...', 1)
#6 /usr/share/php/symfony/vendor/propel-generator/classes/propel/engine/database/transform/XmlToAppData.php(123): ExpatParser->parse()
#7 /usr/share/php/symfony/vendor/propel-generator/classes/propel/phing/AbstractPropelDataModelTask.php(441): XmlToAppData->parseFile('/home/lawrence/...')
#8 /usr/share/php/symfony/vendor/propel-generator/classes/propel/phing/AbstractPropelDataModelTask.php(139): AbstractPropelDataModelTask->loadDataModels()
#9 /usr/share/php/symfony/vendor/propel-generator/classes/propel/phing/PropelOMTask.php(121): AbstractPropelDataModelTask->getDataModels()
#10 /usr/share/php/symfony/vendor/phing/Task.php(254): PropelOMTask->main()
#11 /usr/share/php/symfony/vendor/phing/Target.php(240): Task->perform()
#12 /usr/share/php/symfony/vendor/phing/Target.php(263): Target->main()
#13 /usr/share/php/symfony/vendor/phing/Project.php(701): Target->performTasks()
#14 /usr/share/php/symfony/vendor/phing/tasks/system/PhingTask.php(275): Project->executeTarget('om-template')
#15 /usr/share/php/symfony/vendor/phing/tasks/system/PhingTask.php(150): PhingTask->processFile()
#16 /usr/share/php/symfony/vendor/phing/tasks/system/PhingCallTask.php(109): PhingTask->main()
#17 /usr/share/php/symfony/vendor/phing/Task.php(254): PhingCallTask->main()
#18 /usr/share/php/symfony/vendor/phing/Target.php(240): Task->perform()
#19 /usr/share/php/symfony/vendor/phing/Target.php(263): Target->main()
#20 /usr/share/php/symfony/vendor/phing/Project.php(701): Target->performTasks()
#21 /usr/share/php/symfony/vendor/phing/tasks/system/PhingTask.php(275): Project->executeTarget('om')
#22 /usr/share/php/symfony/vendor/phing/tasks/system/PhingTask.php(150): PhingTask->processFile()
#23 /usr/share/php/symfony/vendor/phing/Task.php(254): PhingTask->main()
#24 /usr/share/php/symfony/vendor/phing/Target.php(240): Task->perform()
#25 /usr/share/php/symfony/vendor/phing/Target.php(263): Target->main()
#26 /usr/share/php/symfony/vendor/phing/Project.php(701): Target->performTasks()
#27 /usr/share/php/symfony/vendor/phing/Project.php(674): Project->executeTarget('om')
#28 /usr/share/php/symfony/vendor/phing/Phing.php(443): Project->executeTargets(Array)
#29 /usr/share/php/symfony/vendor/pake/tasks/pakePhingTask.class.php(62): Phing->runBuild()
#30 /usr/share/php/data/symfony/tasks/sfPakePropel.php(375): pakePhingTask::call_phing(Object(pakeTask), Array, '/usr/share/php/...', Array)
#31 /usr/share/php/data/symfony/tasks/sfPakePropel.php(169): _call_phing(Object(pakeTask), 'om')
#32 [internal function]: run_propel_build_model(Object(pakeTask), Array, Array)
#33 /usr/share/php/symfony/vendor/pake/pakeTask.class.php(218): call_user_func_array('run_propel_buil...', Array)
#34 /usr/share/php/symfony/vendor/pake/pakeTask.class.php(181): pakeTask->execute(Array, Array)
#35 /usr/share/php/symfony/vendor/pake/pakeApp.class.php(175): pakeTask->invoke(Array, Array)
#36 /usr/share/php/data/symfony/bin/symfony.php(178): pakeApp->run(NULL, NULL, false)
#37 /usr/bin/symfony(39): include('/usr/share/php/...')


I assumed I could get around this by giving the columns a different phpName in the schema.yml, but that didn't seem to work. I tried:

utility:
abstract:
_attributes: { phpName: utility_abstract }


Oddly, I still got the same error.

Can anyone suggest a workaround?

The problem is that the base peer classes end up with constants named "Abstract" and "Protected", which are reserved keywords in PHP, so we end up with errors like this:

Parse error: syntax error, unexpected T_PROTECTED, expecting T_STRING in /home/lawrence/newwine/lib/model/om/BaseUtilityContentPeer.php on line 80


This is the table in the schema (old table we are trying to adapt):


utility_content:
id:
type: BIGINT
required: true
primaryKey: true
state:
type: CHAR
size: 1
required: true
primaryKey: true
default: W
legacyid:
type: VARCHAR
size: 50
textid:
type: VARCHAR
size: 200
content_hint:
type: CHAR
size: 1
aliastext:
type: VARCHAR
size: 20
package_tags:
type: VARCHAR
size: 200
priority:
type: FLOAT
default: 0
reference_link_1:
type: INTEGER
alt_info_1:
type: VARCHAR
size: 200
required: true
alt_info_2:
type: LONGVARCHAR
required: true
parent_content_id:
type: INTEGER
created_at:
type: TIMESTAMP
required: true
created_by:
type: VARCHAR
size: 30
required: true
updated_at:
type: TIMESTAMP
required: true
updated_by:
type: VARCHAR
size: 30
required: true
published_at:
type: TIMESTAMP
published_by:
type: VARCHAR
size: 30
content_type:
type: INTEGER
required: true
protected:
_attributes: { phpName: utility_protected }
type: CHAR
size: 1
required: true
default: D
override_channel:
type: VARCHAR
size: 50
override_adsite:
type: VARCHAR
size: 50
override_adzone:
type: VARCHAR
size: 50
override_mlc:
type: VARCHAR
size: 200
required: true
direct_url:
type: VARCHAR
size: 250
pvar1:
type: VARCHAR
size: 100
required: true
pvar2:
type: LONGVARCHAR
required: true
content_date:
type: TIMESTAMP
live_date:
type: TIMESTAMP
issue_date:
type: DATE
issue_toc_id:
type: INTEGER
issue_page:
type: INTEGER
toc_title:
type: VARCHAR
size: 500
toc_subtitle:
type: LONGVARCHAR
title:
type: VARCHAR
size: 200
required: true
title_link:
type: VARCHAR
size: 150
subtitle:
type: VARCHAR
size: 300
required: true
author1:
type: INTEGER
required: true
default: 0
author2:
type: INTEGER
required: true
default: 0
author3:
type: INTEGER
required: true
default: 0
author4:
type: INTEGER
required: true
default: 0
author_ids:
type: VARCHAR
size: 100
required: true
author_str:
type: VARCHAR
size: 500
required: true
topic_ids:
type: VARCHAR
size: 100
required: true
topic_str:
type: LONGVARCHAR
required: true
assoc1_ids:
type: VARCHAR
size: 200
required: true
assoc1_str:
type: LONGVARCHAR
required: true
assoc2_ids:
type: VARCHAR
size: 200
required: true
assoc2_str:
type: LONGVARCHAR
required: true
assoc3_ids:
type: VARCHAR
size: 200
required: true
assoc3_str:
type: LONGVARCHAR
required: true
hp_include:
type: CHAR
size: 1
required: true
default: N
hp_seq:
type: INTEGER
required: true
default: 0
hp_title:
type: CHAR
size: 200
hp_text:
type: LONGVARCHAR
hp_include_image:
type: CHAR
size: 1
required: true
default: N
hp_image:
type: VARCHAR
size: 200
promo_text_1:
type: VARCHAR
size: 1000
required: true
promo_text_2:
type: VARCHAR
size: 1000
required: true
meta_desc:
type: LONGVARCHAR
required: true
meta_keywords:
type: LONGVARCHAR
required: true
meta_title:
type: VARCHAR
size: 300
required: true
tags:
type: LONGVARCHAR
required: true
abstract:
_attributes: { phpName: utility_abstract }
type: LONGVARCHAR
required: true
pcontent:
type: LONGVARCHAR
required: true
scontent:
type: LONGVARCHAR
altcontent:
type: LONGVARCHAR
orig_content:
type: LONGVARCHAR
suppress_standard_sidebars:
type: CHAR
size: 1
required: true
default: N
use_sidebar:
type: CHAR
size: 1
required: true
default: N
sidebar_title:
type: VARCHAR
size: 1000
sidebar_html:
type: LONGVARCHAR
sidebar_object_1:
type: VARCHAR
size: 50
sidebar_value_1:
type: VARCHAR
size: 200
sidebar_title_1:
type: VARCHAR
size: 150
sidebar_object_2:
type: VARCHAR
size: 50
sidebar_value_2:
type: VARCHAR
size: 200
sidebar_title_2:
type: VARCHAR
size: 150
sidebar_object_3:
type: VARCHAR
size: 50
sidebar_value_3:
type: VARCHAR
size: 200
sidebar_title_3:
type: VARCHAR
size: 150
imgpath1:
type: VARCHAR
size: 150
imgcaption1:
type: VARCHAR
size: 1000
imgcredit1:
type: VARCHAR
size: 500
imglink1:
type: VARCHAR
size: 150
imgpath2:
type: VARCHAR
size: 150
imgcaption2:
type: VARCHAR
size: 1000
imgcredit2:
type: VARCHAR
size: 500
imglink2:
type: VARCHAR
size: 150
imgpath3:
type: VARCHAR
size: 150
imgcaption3:
type: VARCHAR
size: 1000
imgcredit3:
type: VARCHAR
size: 500
imglink3:
type: VARCHAR
size: 150
imgpath4:
type: VARCHAR
size: 150
imgcaption4:
type: VARCHAR
size: 1000
imgcredit4:
type: VARCHAR
size: 500
imglink4:
type: VARCHAR
size: 150
imgpath5:
type: VARCHAR
size: 150
imgcaption5:
type: VARCHAR
size: 1000
imgcredit5:
type: VARCHAR
size: 500
imglink5:
type: VARCHAR
size: 150
aobject1:
type: VARCHAR
size: 50
aparams1:
type: LONGVARCHAR
aobject2:
type: VARCHAR
size: 50
aparams2:
type: LONGVARCHAR
aobject3:
type: VARCHAR
size: 50
aparams3:
type: LONGVARCHAR
allow_comments:
type: CHAR
size: 1
required: true
default: D
show_comments:
type: CHAR
size: 1
required: true
default: D
stripped_search_1:
type: LONGVARCHAR
stripped_search_2:
type: LONGVARCHAR
rel_type_1:
type: VARCHAR
size: 50
rel_data_1:
type: LONGVARCHAR
rel_type_2:
type: VARCHAR
size: 50
rel_data_2:
type: LONGVARCHAR
rel_type_3:
type: VARCHAR
size: 50
rel_data_3:
type: LONGVARCHAR
rel_type_4:
type: VARCHAR
size: 50
rel_data_4:
type: LONGVARCHAR
rel_type_5:
type: VARCHAR
size: 50
rel_data_5:
type: LONGVARCHAR
_indexes:
ByTextID:
- textid
- state
ByContentType:
- content_type
- content_date
ByLegacy:
- legacyid
Issue1:
- issue_date
- issue_toc_id
- issue_page
Retr01:
- state
- content_type
- content_date
- live_date
Retr02:
- state
- content_type
- reference_link_1
- content_date
- live_date
Ref03:
- state
- reference_link_1
- content_date
ByAlias:
- aliastext
- state
ByPackage:
- package_tags
- state



You see where I wrote

protected:
_attributes: { phpName: utility_protected }

I thought this would change the name of the constant that appears in the peer class, but it is not working. Did I get the syntax wrong?

This is all in Symfony 1.0

Answers (5)

2010-04-20

Bill Hunt answers:

It looks like that error is on a column named protected, not the table name - did you use phpName on the column, too? You might also check that any foreign keys listed are using phpName and refPhpName properly. A dump of the table schema and the relevant parts of the schema file you're using would be helpful, too. :)


Lawrence Krubner comments:

Bill, thanks. I am sorry I didn't provide enough information up front. I've now edited the question to provide the whole yaml schema of the table we are struggling with. Could you give it a look? I'm wondering if maybe I got the syntax on phpName wrong? Seems like phpName should solve the problem, so I'm surprised that it didn't.


Bill Hunt comments:

Hey Lawrence, I know this is an old version of Symfony+Propel, so you might make sure that the XML is being generated properly. php symfony propel-convert-yml-schema is the command to convert to xml from yml. If you open up the schema.xml and it's missing the phpName flags, then you'll need to fix those and then temporarily move schema.yml before rebuilding your models.


Lawrence Krubner comments:

Thanks, Bill, that was super useful for tracking down the problem.

2010-04-20

Jakub Zalas answers:

I don't think it's about either abstract nor protected. I successfully tested bot fields and name wasn't issue.

In stack trace you pasted I noticed that you don't have type set for protected column (or it's invalid):

exception 'EngineException' with message 'Error setting up column 'protected': Cannot map unknown Propel type '' to native database type.' in /usr/share/php/symfony/vendor/propel-generator/classes/propel/engine/database/model/Column.php:190


I can reproduce your error with following schema (stack trace says you use xml):


<?xml version="1.0" encoding="UTF-8"?>
<database name="propel" defaultIdMethod="native" noxsd="true">
<table name="my_table" phpName="MyTable">
<column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true" />
<column name="abstract" type="integer" />
<column name="protected" type="" />
</table>
</database>


Setting type for protected column successfully build the database and the model.

Note: you pasted schema as yml and error is pointing to xml. Maybe you have two schema files and one is just wrong?


Lawrence Krubner comments:

Jakub, thanks for this. I ran "symfony propel-build-model" which converts the yml to xml and then builds the model and peer classes from the xml. The error is happening in the xml, but I assume the original error is arising from the yml file.

Though, I think the root of the problem is having columns with names like "abstract" which is a reserved keyword in PHP.


Jakub Zalas comments:

Since you gave new info I have new answer ;)

You can change the way propel generate constants as it is possible to overload generator. Default class for building *Peer classes is SfPeerBuilder. It's defined in config/propel.ini:


propel.builder.peer.class = addon.propel.builder.SfPeerBuilder
propel.builder.object.class = addon.propel.builder.SfObjectBuilder

propel.builder.objectstub.class = addon.propel.builder.SfExtensionObjectBuilder
propel.builder.peerstub.class = addon.propel.builder.SfExtensionPeerBuilder
propel.builder.objectmultiextend.class = addon.propel.builder.SfMultiExtendObjectBuilder
propel.builder.mapbuilder.class = addon.propel.builder.SfMapBuilderBuilder
propel.builder.interface.class = propel.engine.builder.om.php5.PHP5InterfaceBuilder
propel.builder.node.class = propel.engine.builder.om.php5.PHP5NodeBuilder
propel.builder.nodepeer.class = propel.engine.builder.om.php5.PHP5NodePeerBuilder
propel.builder.nodestub.class = propel.engine.builder.om.php5.PHP5ExtensionNodeBuilder
propel.builder.nodepeerstub.class = propel.engine.builder.om.php5.PHP5ExtensionNodePeerBuilder


You can start by looking at SfPeerBuilder. In one of its parent classes (PHP5BasicPeerBuilder) there is a addColumnNameConstants() method which generates constants. It could be that you have to overload more as other parts of code could rely on the way constants are generated. Below the original method:

<pre>
protected function addColumnNameConstants(&$script)
{
foreach ($this->getTable()->getColumns() as $col) {

$script .= "
/** the column name for the ".strtoupper($col->getName()) ." field */
const ".$this->getColumnName($col) ." = '".$this->getTable()->getName().".".strtoupper($col->getName())."';
";
} // foreach
}
</pre>


Lawrence Krubner comments:

addColumnNamesConstants in an interesting avenue to explore. I ended going [[LINK href="http://www.mail-archive.com/[email protected]/msg08504.html"]]to this discussion[[/LINK]] where I read:

<blockquote>
> Propel provides a "peerName" attribute for each column. You can use
> this to rename the peer constant.</blockquote>

So I tried this in schema.yml:

protected:
_attributes: { peerName: utility_protected, phpName: utility_protect }
type: CHAR
size: 1
required: true
default: D


and rebuilt the model, but the constant is still getting the name of "protected", and therefore still causing errors. I tried putting the values in quotes ("utility_protected"), too, but that had no effect.


Jakub Zalas comments:

Try the following:

protected:
peerName: utility_protected
phpName: utility_protect
type: CHAR
size: 1
required: true
default: D


Lawrence Krubner comments:

Thanks, Jakub, that works great.

2010-04-20

Scott Meves answers:

Would you mind posting a portion of your schema that has the offending table and/or column names? Also, which version of symfony are you using?


Lawrence Krubner comments:

Sorry, Scott, I should have posted all that at the beginning. I'll edit the question right now and add that in.

2010-04-20

Marcos Ibañez answers:

The problem seems to be specifically with the abstract keyword since when using the protected keyword with phpName works like a charm.

My suggestion will be to change the "abstract" table to another name, if the project is coded right it shouldnt be too much work to do so.


Lawrence Krubner comments:

"If the project is coded right" is a big assumption. Some of the system we are working with is very old.

2010-04-20

Martin Palacio answers:

When having such issues, I prefer to build a view on top of the problematic table(s) with my own naming scheme and standards.
Then, use the view instead of the table in your project/ORM (ex. remove the table definition on schema.yml)

Et voilà... it works like a charm!

Caveats:
* You need to include a "virtual" column named "id" (at least with Propel)
* You can't define constraints from/to your view...AFAIK.

Maybe this approach results for you...

EDIT: To figure you out, the view works like an "interface" between your PHP project and your database "legacy" schema.