I've narrowed down my problem to the foreign key definition:
<table name="question" phpName="Question" idMethod="native">
<behavior name="versionable">
<parameter name="version_table" value="question_version" />
</behavior>
<column name="id" phpName="Id" type="INTEGER" primaryKey="true"
autoIncrement="true" required="true"/>
<column name="survey_id" phpName="SurveyId" type="INTEGER"
required="true"/>
<column name="display" phpName="Display" type="VARCHAR" size="255" />
<!-- with this foreign key, versionable fails -->
<foreign-key foreignTable="survey_component">
<reference local="survey_id" foreign="id"/>
</foreign-key>
</table>
The problem is that
foreach ($this->getVersionableReferrers() as $fk) {
$fkTableName = $fk->getTable()->getName();
for postgres returns something in the format "schema.table". I hacked the
4 places that the column name is needed to remove the schema., and now
everything's building okay. I can submit it as a PR, but the solution
isn't very elegant. OTOH, it doesn't break anything, and definitely
improves the situation for PostGRES users with foreign keys using this
behavior.
Francois, can you take a look at this, or point me in the right direction?
Thx,
Tac
On Saturday, November 3, 2012 8:34:57 AM UTC-4, Tac Tacelosky wrote:
> Hmm, if I explicitly set the version_table and version_column, I get a
> little further, but then run into invalid SQL:
> CREATE TABLE survey.survey_component_version
> (
> id INTEGER NOT NULL,
> code VARCHAR(64),
> vers INTEGER DEFAULT 0 NOT NULL,
> survey.question_ids TEXT,
> survey.question_versions TEXT,
> PRIMARY KEY (id,vers)
> );
> The "survey." prefix isn't valid. I imagine this is related to the same
> issue with aggregate behaviors, but that can be solved by adding the schema
> name as a prefix to the table. I'm not sure if there's a solution here,
> other than modifying the source code. PostGRES has much less support right
> now in Propel 1.x (e.g. no escaping column names), hoping that'll change in
> 2.0. In the meantime, any suggestions on a solution?
> Tac
> On Saturday, November 3, 2012 8:21:29 AM UTC-4, Tac Tacelosky wrote:
>> I've used the versionable behavior with a MySQL database with a problem,
>> but now I'm running into an error during the propel:insert:sql command
>> using a PostGRES database:
>> exception 'PDOException' with message 'SQLSTATE[0A000]: Feature not
>> supported: 7 ERROR: cross-database references are not implemented:
>> "survey.survey.question_version"' in
>> /usr/sites/sf/survos/vendor/propel/propel1/generator/lib/util/PropelSqlMana ger.php:232
>> Stack trace:
>> #0
>> /usr/sites/sf/survos/vendor/propel/propel1/generator/lib/util/PropelSqlMana ger.php(232):
>> PDOStatement->execute()
>> #1
>> /usr/sites/sf/survos/vendor/propel/propel-bundle/Propel/PropelBundle/Comman d/SqlInsertCommand.php(104):
>> PropelSqlManager->insertSql('survos')
>> #2
>> /usr/sites/sf/survos/vendor/propel/propel-bundle/Propel/PropelBundle/Comman d/SqlInsertCommand.php(79):
>> Propel\PropelBundle\Command\SqlInsertCommand->doSqlInsert(Object(PropelSqlM anager),
>> Object(Symfony\Component\Console\Output\ConsoleOutput), 'survos')
>> #3
>> It happens on any table I add the versionable behavior to, so I'm
>> wondering if it's an issue with PostGRES and how the 'schema' attribute is
>> defined in the schema.xml file.
>> Tac
