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.
This archive was generated by hypermail 2.0.0 : Mon Jun 21 2010 - 15:52:06 UTC