[horde] Problem in Rdo/RelationshipTest testing clotho example in MySQL

[Elier Delgado]

Hi,

I have been testing Rdo, specifically the clotho example available on
the CVS with MySql,
and seem happen an error building the SQL sentence when I tested the
relationship.

Have someone tested this in MySql before?

The ItemMapper find method return this query, that not work in MySQL:

SELECT clotho_wbs_items.item_id, clotho_wbs_items.item_name,
clotho_wbs_items.item_parent, clotho_wbs_items.item_duration,
clotho_wbs_items.item_start, clotho_wbs_items.item_start_fixed,
clotho_wbs_items.item_finish, clotho_wbs_items.item_finish_fixed,
clotho_resources.resource_id AS `clotho_resources at resource_id`,
clotho_resources.resource_type AS `clotho_resources at resource_type`,
clotho_resources.resource_name AS `clotho_resources at resource_name`,
clotho_resources.resource_uid AS `clotho_resources at resource_uid`,
clotho_resources.resource_base_calendar AS
`clotho_resources at resource_base_calendar`,
clotho_resources.resource_start AS `clotho_resources at resource_start`,
clotho_resources.resource_finish AS
`clotho_resources at resource_finish`,
clotho_wbs_items.item_id AS `clotho_wbs_items at item_id`,
clotho_wbs_items.item_name AS `clotho_wbs_items at item_name`,
clotho_wbs_items.item_parent AS `clotho_wbs_items at item_parent`,
clotho_wbs_items.item_duration AS `clotho_wbs_items at item_duration`,
clotho_wbs_items.item_start AS `clotho_wbs_items at item_start`,
clotho_wbs_items.item_start_fixed AS
`clotho_wbs_items at item_start_fixed`, clotho_wbs_items.item_finish AS
`clotho_wbs_items at item_finish`, clotho_wbs_items.item_finish_fixed AS
`clotho_wbs_items at item_finish_fixed`
FROM clotho_wbs_items INNER JOIN clotho_wbs_items ON
clotho_wbs_items.item_parent = clotho_wbs_items.item_id
WHERE clotho_wbs_items.`item_id` = 1 LIMIT 0, 1

It's a join with the self table without use alias, to can work must be
like this:

SELECT a.item_id, a.item_name, a.item_parent, a.item_duration,
a.item_start, a.item_start_fixed, a.item_finish, a.item_finish_fixed,
 clotho_resources.resource_id AS `clotho_resources at resource_id`,
clotho_resources.resource_type AS `clotho_resources at resource_type`,
clotho_resources.resource_name AS `clotho_resources at resource_name`,
clotho_resources.resource_uid AS `clotho_resources at resource_uid`,
clotho_resources.resource_base_calendar AS
`clotho_resources at resource_base_calendar`,
clotho_resources.resource_start AS `clotho_resources at resource_start`,
clotho_resources.resource_finish AS
`clotho_resources at resource_finish`,
 a.item_id AS `clotho_wbs_items at item_id`, a.item_name AS
`clotho_wbs_items at item_name`, a.item_parent AS
`clotho_wbs_items at item_parent`, a.item_duration AS
`clotho_wbs_items at item_duration`, a.item_start AS
`clotho_wbs_items at item_start`, a.item_start_fixed AS
`clotho_wbs_items at item_start_fixed`, a.item_finish AS
`clotho_wbs_items at item_finish`, a.item_finish_fixed AS
`clotho_wbs_items at item_finish_fixed`
FROM clotho_resources, clotho_wbs_items as a INNER JOIN
clotho_wbs_items as b ON a.item_parent = b.item_id
 WHERE a.`item_id` = 3 LIMIT 0, 1

The ResourceMapper find method have similar problem, don't build the
correct sentence,

 SELECT clotho_resources.resource_id, clotho_resources.resource_type,
clotho_resources.resource_name, clotho_resources.resource_uid,
clotho_resources.resource_base_calendar,
clotho_resources.resource_start, clotho_resources.resource_finish,
clotho_resource_availability.availability_id AS
`clotho_resource_availability at availability_id`,
clotho_resource_availability.resource_id AS
`clotho_resource_availability at resource_id`,
clotho_resource_availability.availability_date AS
`clotho_resource_availability at availability_date`,
clotho_resource_availability.availability_hours AS
`clotho_resource_availability at availability_hours`,
clotho_wbs_items.item_id AS `clotho_wbs_items at item_id`,
clotho_wbs_items.item_name AS `clotho_wbs_items at item_name`,
clotho_wbs_items.item_parent AS `clotho_wbs_items at item_parent`,
clotho_wbs_items.item_duration AS `clotho_wbs_items at item_duration`,
clotho_wbs_items.item_start AS `clotho_wbs_items at item_start`,
clotho_wbs_items.item_start_fixed AS
`clotho_wbs_items at item_start_fixed`, clotho_wbs_items.item_finish AS
`clotho_wbs_items at item_finish`, clotho_wbs_items.item_finish_fixed AS
`clotho_wbs_items at item_finish_fixed`
FROM clotho_resources  WHERE clotho_resources.`resource_id` = 1 LIMIT 0, 1

Missing this tables in FROM section:
clotho_resource_availability,clotho_wbs_items

Though the ResourceAvailabilityMapper find method work fine ...
Resource Availability (1) 07/15/05 01:08:15 has resource: (1) Test Resource

Can someone help me to solve this ?

Thanks and Regards, Elier


This is the MySQL database script,

-- ----------------------------
-- Table structure for clotho_calendars
-- ----------------------------
CREATE TABLE `clotho_calendars` (
  `calendar_id` int(4) NOT NULL auto_increment,
  `calendar_name` text NOT NULL,
  `calendar_hoursinday` int(4) NOT NULL,
  `calendar_hoursinweek` int(4) NOT NULL,
  `calendar_type` text NOT NULL,
  `calendar_data` text NOT NULL,
  PRIMARY KEY  (`calendar_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for clotho_resource_availability
-- ----------------------------
CREATE TABLE `clotho_resource_availability` (
  `availability_id` int(4) NOT NULL auto_increment,
  `resource_id` int(4) NOT NULL,
  `availability_date` int(4) NOT NULL,
  `availability_hours` decimal(10,0) NOT NULL,
  PRIMARY KEY  (`availability_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for clotho_resources
-- ----------------------------
CREATE TABLE `clotho_resources` (
  `resource_id` int(4) NOT NULL auto_increment,
  `resource_type` text NOT NULL,
  `resource_name` text NOT NULL,
  `resource_uid` text NOT NULL,
  `resource_base_calendar` int(4) NOT NULL,
  `resource_start` int(4) NOT NULL,
  `resource_finish` int(4) NOT NULL,
  PRIMARY KEY  (`resource_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for clotho_wbs_dependencies
-- ----------------------------
CREATE TABLE `clotho_wbs_dependencies` (
  `dependecy_id` int(4) NOT NULL auto_increment,
  `dependecy_type` text NOT NULL,
  `dependency_lhs_item` int(4) NOT NULL,
  `dependency_rhs_item` int(4) NOT NULL,
  `dependency_duration` text NOT NULL,
  PRIMARY KEY  (`dependecy_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for clotho_wbs_items
-- ----------------------------
CREATE TABLE `clotho_wbs_items` (
  `item_id` int(4) NOT NULL auto_increment,
  `item_name` text NOT NULL,
  `item_parent` int(4) NOT NULL,
  `item_duration` text NOT NULL,
  `item_start` int(4) NOT NULL,
  `item_start_fixed` int(4) NOT NULL,
  `item_finish` int(4) NOT NULL,
  `item_finish_fixed` int(4) NOT NULL,
  PRIMARY KEY  (`item_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for clotho_wbs_resources
-- ----------------------------
CREATE TABLE `clotho_wbs_resources` (
  `item_id` int(4) NOT NULL,
  `resource_id` int(4) NOT NULL,
  PRIMARY KEY  (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `clotho_calendars` VALUES ('1', 'Test Calendar', '8',
'40', 'weekly', '');
INSERT INTO `clotho_resource_availability` VALUES ('1', '1', '1121404095', '2');
INSERT INTO `clotho_resources` VALUES ('1', 'M', 'Test Resource', '',
'1', '0', '0');
INSERT INTO `clotho_wbs_dependencies` VALUES ('1', '', '1', '2', '');
INSERT INTO `clotho_wbs_items` VALUES ('1', 'Test Item', '0', '', '0',
'0', '0', '0');
INSERT INTO `clotho_wbs_items` VALUES ('2', 'Test Item 2', '0', '',
'0', '0', '0', '0');
INSERT INTO `clotho_wbs_items` VALUES ('3', 'Child Item', '1', '',
'0', '0', '0', '0');