On 06/19/10 10:48, Andrus Adamchik wrote:
This is odd since your query is not even using caching, so it hits the
DB and returns the data from there. Do you have Cayenne SQL logging
enabled and does the generated query look like what you expected?
Andrus
This is the log data. It should not have found a row.
2010-06-18 12:30:10,021 [main] INFO
org.apache.cayenne.access.QueryLogger - SELECT
`t0`.`publication_edition_date`, `t0`.`publication_edition_id`,
`t0`.`publication_edition_arrived`, `t0`.`publication_id
` FROM `publication_editions` `t0` JOIN `publications` `t1` ON
(`t0`.`publication_id` = `t1`.`publication_id`) WHERE
(`t1`.`publication_name` = ?) AND (`t0`.`publication_edition_date` = ?)
[bind: 1->publ
ication_name:'Prime research', 2->publication_edition_date:'2010-06-18
12:30:10.012']
2010-06-18 12:30:10,023 [main] INFO
org.apache.cayenne.access.QueryLogger - === returned 1 row. - took 2 ms.
2010-06-18 12:30:10,023 [main] INFO
org.apache.cayenne.access.QueryLogger - +++ transaction committed.
Then later it does an inserting using the same item to another table.
2010-06-18 12:30:10,102 [main] INFO
org.apache.cayenne.access.QueryLogger - INSERT INTO `articles`
(`abstract_key_messages`, `abstract_lead`, `abstract_quotes`,
`article_status_id`, `creator_user_id`, `d
ate_created`, `date_published`, `date_updated`, `etext_id`, `headline`,
`journalist_id`, `project_id`, `publication_edition_id`, `subhead`)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2010-06-18 12:30:10,103 [main] INFO
org.apache.cayenne.access.QueryLogger - [bind:
1->abstract_key_messages:NULL, 2->abstract_lead:NULL,
3->abstract_quotes:NULL, 4->article_status_id:01, 5->creator_user_
id:2000, 6->date_created:'2010-06-18 12:30:10.04',
7->date_published:'2010-06-18 12:30:10.028', 8->date_updated:'2010-06-18
12:30:10.04', 9->etext_id:NULL, 10->headline:'JUNIT Test Article',
11->journali
st_id:NULL, 12->project_id:6, 13->publication_edition_id:237,
14->subhead:NULL]
What doesn't make sense here is what's in the database versus what's
returned.
Publication editions contains the following for the pk 237
publication_edition_id publication_id publication_edition_date
publication_edition_arrived
237 41 2010-02-02 00:00:00 0
Publication 1 is "Prime research" whereas publication 41 is physorg.com.
Here is the MySQL schema for the two tables
CREATE TABLE IF NOT EXISTS `publications` (
`publication_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`publication_category_id` smallint(5) unsigned NOT NULL,
`publication_owner_id` tinyint(3) unsigned NOT NULL,
`country_id` smallint(5) unsigned NOT NULL,
`parent_id` int(11) DEFAULT NULL,
`publication_onsale_days_before` tinyint(3) unsigned NOT NULL,
`publication_name` varchar(255) NOT NULL,
`national_publication` tinyint(1) NOT NULL COMMENT 'national or
regional',
`primary_language` tinyint(3) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`publication_id`),
UNIQUE KEY `publication_country_name` (`country_id`,`publication_name`),
KEY `publication_country` (`country_id`),
KEY `publication_category` (`publication_category_id`),
KEY `publication_office` (`publication_owner_id`),
KEY `publication_parent` (`parent_id`),
KEY `publication_name` (`publication_name`) USING BTREE,
KEY `publication_language` (`primary_language`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2001 ;
CREATE TABLE IF NOT EXISTS `publication_editions` (
`publication_edition_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`publication_id` int(10) unsigned NOT NULL,
`publication_edition_date` datetime NOT NULL,
`publication_edition_arrived` tinyint(1) NOT NULL,
PRIMARY KEY (`publication_edition_id`),
KEY `publication_edition_pub` (`publication_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5087 ;
And from the cayenne map:
<db-entity name="publication_editions" catalog="prime_pcd2009">
<db-attribute name="publication_edition_arrived" type="BOOLEAN"
isMandatory="true"/>
<db-attribute name="publication_edition_date" type="DATE"
isMandatory="true"/>
<db-attribute name="publication_edition_id" type="BIGINT"
isPrimaryKey="true" isGenerated="true" isMandatory="true" length="10"/>
<db-attribute name="publication_id" type="BIGINT" isMandatory="true"
length="10"/>
</db-entity>
...
<db-entity name="publications" catalog="prime_pcd2009">
<db-attribute name="country_id" type="BIGINT" isMandatory="true"
length="10"/>
<db-attribute name="national_publication" type="BOOLEAN"
isMandatory="true"/>
<db-attribute name="parent_id" type="BIGINT" length="10"/>
<db-attribute name="primary_language" type="TINYINT" isMandatory="true"/>
<db-attribute name="publication_category_id" type="BIGINT"
isMandatory="true" length="10"/>
<db-attribute name="publication_id" type="BIGINT" isPrimaryKey="true"
isGenerated="true" isMandatory="true" length="10"/>
<db-attribute name="publication_name" type="VARCHAR" isMandatory="true"
length="255"/>
<db-attribute name="publication_onsale_days_before" type="TINYINT"
isMandatory="true" length="2"/>
<db-attribute name="publication_owner_id" type="TINYINT"
isMandatory="true"/>
</db-entity>
It's quite possible I'm making some obvious mistake here, but there is
only one record for the publication name "Prime research" in the
publication_editions table which is:
<http://mail.primemediaanalysis.com/phpmyadmin/sql.php?db=prime_pcd2009&table=publication_editions&sql_query=DELETE+FROM+%60prime_pcd2009%60.%60publication_editions%60+WHERE+%60publication_editions%60.%60publication_edition_id%60+%3D+1&zero_rows=The+row+has+been+deleted&goto=sql.php%3Fdb%3Dprime_pcd2009%26table%3Dpublication_editions%26sql_query%3DSELECT%2B%252A%2B%2BFROM%2B%2560publication_editions%2560%2BWHERE%2B%2560publication_id%2560%2B%253D%2B1%26zero_rows%3DThe%2Brow%2Bhas%2Bbeen%2Bdeleted%26goto%3Dsql.php%253Fdb%253Dprime_pcd2009%2526amp%253Btable%253Dpublication_editions%2526amp%253Bserver%253D1%2526amp%253Btoken%253Df1870468188ec6208aa4dc611e33fbf1%2526amp%253Bsql_query%253DSELECT%252B%25252A%252B%252BFROM%252B%252560publication_editions%252560%252BWHERE%252B%252560publication_id%252560%252B%25253D%252B1%26server%3D1%26token%3Df1870468188ec6208aa4dc611e33fbf1&server=1&token=f1870468188ec6208aa4dc611e33fbf1>
1 1 2009-09-11 00:00:00 0
I would expect no match.
