Home » Database » DB_DataObject » Bug #4288

[2005-05-05 17:00 UTC] ej dot grace at imperial dot ac dot uk

Description:
------------
The joinAdd method does not work as advertised for postgresql connections. In the generated SQL it inserts an unwanted <databasename>. prefix to the tables. If anything is to be prefixed automatically it should use <schema> (although an option to ignore schemas would also be good).

Reproduce code:
---------------
Generate tables and objects in the usual manner:

CREATE TABLE person (
id BIGINT DEFAULT nextval('person_seq') NOT NULL,
namelast VARCHAR(255) NOT NULL,
namefirst VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);
DROP SEQUENCE person_seq;
CREATE SEQUENCE person_seq;

--- author table as well as a link to the author table for (current) information.
CREATE TABLE paper_submission_author (
id BIGINT DEFAULT nextval('paper_submission_author_seq') NOT NULL,
paper_submissionid BIGINT NOT NULL,
author_id BIGINT REFERENCES person NOT NULL,
PRIMARY KEY (id)
);
DROP SEQUENCE paper_submission_author_seq;
CREATE SEQUENCE paper_submission_author_seq;

--- A table containing a list of submitted papers
CREATE TABLE paper_submission (
id BIGINT DEFAULT nextval('paper_submission_seq') NOT NULL,
title VARCHAR(512) NOT NULL,
author_corresponding_id BIGINT REFERENCES person (id),
PRIMARY KEY (id)
);
DROP SEQUENCE paper_submission_seq;
CREATE SEQUENCE paper_submission_seq;

Set databasename.links.ini up correctly.

Then set up a script as follows to do a join (N.B. this may have other problems than the one under investigation)

$paper_submission =& DB_DataObject::factory('paper_submission');
$paper_submission->debugLevel(1);
$paper_submission_author =& DB_DataObject::factory('paper_submission_author');

$paper_submission_author->joinAdd($paper_submission);
$paper_submission_author->find();
while ($paper_submission_author->fetch()) {
dump($paper_submission_author->toArray());
}

Expected result:
----------------
The generated SQL statment working correctly

SELECT * FROM paper_submission_author INNER JOIN paper_submission ON paper_submission.id=paper_submission_author.paper_submissionid
dataobjects_paper_submission_author

Actual result:
--------------
dataobjects_paper_submission: databaseStructure: Loaded ini file: /var/www/php/OJ/DataObjects/ojdb.ini
dataobjects_paper_submission: databaseStructure: Loaded links.ini file: /var/www/php/OJ/DataObjects/ojdb.links.ini
dataobjects_paper_submission_author: __find:
dataobjects_paper_submission_author: QUERY: SELECT * FROM paper_submission_author INNER JOIN ojdb.paper_submission ON paper_submission.id=paper_submission_author.paper_submissionid
dataobjects_paper_submission_author: Query Error: [db_error: message="DB Error: no such table" code=-18 mode=return level=notice prefix="" info="SELECT * FROM paper_submission_author INNER JOIN ojdb.paper_submission ON paper_submission.id=paper_submission_author.paper_submissionid [nativecode=ERROR: relation "ojdb.paper_submission" does not exist]"]
dataobjects_paper_submission_author: ERROR: db_error Object ( [error_message_prefix] => [mode] => 1 [level] => 1024 [code] => -18 [message] => DB Error: no such table [userinfo] => SELECT * FROM paper_submission_author INNER JOIN ojdb.paper_submission ON paper_submission.id=paper_submission_author.paper_submissionid [nativecode=ERROR: relation "ojdb.paper_submission" does not exist] [backtrace] => Array ( [0] => Array ( [file] => /usr/share/pear/DB.php [line] => 888 [function] => pear_error [class] => db_error [type] => -> [args] => Array ( [0] => DB Error: no such table [1] => -18 [2] => 1 [3] => 1024 [4] => SELECT * FROM paper_submission_author INNER JOIN ojdb.paper_submission ON

The following segment indicates that the name of the database "ojdb" is being prefixed to table names in the JOIN.

[nativecode=ERROR: relation "ojdb.paper_submission" does not exist] [backtrace] => Array ( [0] => Array ( [file] =>

etc....

Judging by the DataObject->joinAdd() method, line 2828

// not sure how portable adding database prefixes is..
$objTable = $quoteIdentifiers ?
$DB->quoteIdentifier($obj->_database . '.' . $obj->__table) :
$obj->_database . '.' . $obj->__table ;

// add database prefix if they are different databases
if (($obj->_database != $this->_database) && strlen($obj->_database )) {
$objTable = ($quoteIdentifiers ? $DB->quoteIdentifier($obj->_database) : $obj->_database) . '.' . $objTable;
}

I would bet that is the route of the problem. I suspect that adding database prefixes is not what is wanted.

[2005-05-09 14:05 UTC] ej dot grace at imperial dot ac dot uk

It appears this bug has been fixed in CVS v 1.353.