Landscapes of Injustice

At AK's request, wrote some XQuery to extract a list of Japanese owners who were the last owners of a property prior to the dispossession date.

Per request from AG, created a new test db copied from Powell St but basically empty, for error-checking duplicate insertions.

After a lengthy discussion and agreement, I've added the same "extinguished properties" feature to the Vancouver db as exists in the Maple Ridge db. SQL:

DROP TABLE IF EXISTS `titles_to_ext_properties`;
CREATE TABLE IF NOT EXISTS `titles_to_ext_properties` (
  `tte_tte_id` int(11) NOT NULL auto_increment,
  `tte_prop_id_fk` int(11) NOT NULL,
  `tte_title_id_fk` int(11) NOT NULL,
  PRIMARY KEY  (`tte_tte_id`),
  KEY `tte_prop_id_fk` (`tte_prop_id_fk`,`tte_title_id_fk`),
  KEY `tte_title_id_fk` (`tte_title_id_fk`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `titles_to_ext_properties`
  ADD CONSTRAINT `titles_to_ext_properties_ibfk_1` FOREIGN KEY (`tte_prop_id_fk`) REFERENCES `props` (`prp_property_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `titles_to_ext_properties_ibfk_2` FOREIGN KEY (`tte_title_id_fk`) REFERENCES `titles` (`ttl_title_id`) ON DELETE CASCADE ON UPDATE CASCADE;

Additions to local_classes.php:

//In title class, before preceding titles field:
$this->addField(new MdhOneToManyField('ttl_ext_properties', 'Extinguished properties',
                                'ttl_title_id', 
                                'props',
                                'prp_property_id',
                                'prp_desc',
                                'titles_to_ext_properties',
                                'tte_tte_id',
                                'tte_title_id_fk',
                                'tte_prop_id_fk',
                                true, 'prp_desc', true));
//In properties class, before related properties field:
$this->addField(new MdhOneToManyField('own_extinguishing_titles', 'Extinguished by titles',
                                'prp_property_id', 
                                'titles',
                                'ttl_title_id',
                                'ttl_desc',
                                'titles_to_ext_properties',
                                'tte_tte_id',
                                'tte_prop_id_fk',
                                'tte_title_id_fk',
                                true, 'ttl_desc', true));

Tested in dev, rolled out to live, awaiting any anomaly reports from the RAs in the field.

Long and confusing email discussion about the difficulties in entering data about titles extinguishing properties, especially when moving backwards through title records; you end up having to re-edit a later title you've already entered when you discover from an earlier title that it actually extinguished some lots. I don't see a good technical solution to this as yet, but something may emerge from the discussions.

Responded to all JSR's comments, added some additional sections, and reformatted to fit the journal submission guidelines.

Per AG's request, added a new Range field to the property table, reconfigured the triggers to take account of it, and then updated all the desc fields (since we also made a small change in April). Code used is in the repo as trigger_update_2017-05.txt; tested on dev then run on live. Backups taken before and after.

Responding to all JSR's notes and doing a few other tweaks. I'm about half-way through.

I've now revealed (through local_classes.php) the location table and associated location field in the props table, so the team can enter properties from other locations.

I've slightly modified the triggers for the Maple Ridge dbs on AG's instructions, to add block info into the description used for dropdowns:

DROP TRIGGER IF EXISTS `landscapes_mapridgelive`.`prp_desc_insert`;
DELIMITER //
CREATE TRIGGER `landscapes_mapridgelive`.`prp_desc_insert` BEFORE INSERT ON `landscapes_mapridgelive`.`props`
 FOR EACH ROW BEGIN
  DECLARE next_id INT;
  SET next_id = (SELECT `AUTO_INCREMENT` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`=DATABASE() AND `TABLE_NAME`='props');
	SET NEW.`prp_desc` = 
	concat(
          IF (NEW.`prp_township` != '', concat('T:', LPAD(NEW.`prp_township`, 5, '0'), ' '), ''),
          IF (NEW.`prp_district` != '', concat('DL:', LPAD(NEW.`prp_district`, 5, '0'), ' '), ''),
          IF (NEW.`prp_section` != '', concat('S:', LPAD(NEW.`prp_section`, 5, '0'), ' '), ''),
          IF (NEW.`prp_block` != '', concat('B:', LPAD(NEW.`prp_block`, 5, '0'), ' '), ''),
          IF (NEW.`prp_quadrant` != '', concat('Q:', NEW.`prp_quadrant`, ' '), ''),
          IF (NEW.`prp_plan` != '', concat('PL:', LPAD(NEW.`prp_plan`, 5, '0'), ' '), ''),
          IF (NEW.`prp_sketch` != '', concat('SK:', LPAD(NEW.`prp_sketch`, 5, '0'), ' '), ''),
          IF (NEW.`prp_lot` != '', concat('L:', LPAD(NEW.`prp_lot`, 5, '0'), ' '), ''),
          IF (NEW.`prp_parcel` != '', concat('PC:', LPAD(NEW.`prp_parcel`, 5, '0'), ' '), ''),
          IF (NEW.`prp_acreage` != '', concat('A:', NEW.`prp_acreage`, ' '), ''),
          IF (NEW.`prp_exception` != '', 'EX', ''),
          ' (#', next_id, ')'
        );
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `landscapes_mapridgelive`.`prp_desc_update`;
DELIMITER //
CREATE TRIGGER `landscapes_mapridgelive`.`prp_desc_update` BEFORE UPDATE ON `landscapes_mapridgelive`.`props`
 FOR EACH ROW BEGIN
	SET NEW.`prp_desc` = 
	concat(
          IF (NEW.`prp_township` != '', concat('T:', LPAD(NEW.`prp_township`, 5, '0'), ' '), ''),
          IF (NEW.`prp_district` != '', concat('DL:', LPAD(NEW.`prp_district`, 5, '0'), ' '), ''),
          IF (NEW.`prp_section` != '', concat('S:', LPAD(NEW.`prp_section`, 5, '0'), ' '), ''),
          IF (NEW.`prp_block` != '', concat('B:', LPAD(NEW.`prp_block`, 5, '0'), ' '), ''),
          IF (NEW.`prp_quadrant` != '', concat('Q:', NEW.`prp_quadrant`, ' '), ''),
          IF (NEW.`prp_plan` != '', concat('PL:', LPAD(NEW.`prp_plan`, 5, '0'), ' '), ''),
          IF (NEW.`prp_sketch` != '', concat('SK:', LPAD(NEW.`prp_sketch`, 5, '0'), ' '), ''),
          IF (NEW.`prp_lot` != '', concat('L:', LPAD(NEW.`prp_lot`, 5, '0'), ' '), ''),
          IF (NEW.`prp_parcel` != '', concat('PC:', LPAD(NEW.`prp_parcel`, 5, '0'), ' '), ''),
          IF (NEW.`prp_acreage` != '', concat('A:', NEW.`prp_acreage`, ' '), ''),
          IF (NEW.`prp_exception` != '', 'EX', ''),
          ' (#', NEW.`prp_property_id`, ')'
        );
END
//
DELIMITER ;

I haven't yet updated all the old descriptions, since most of them don't have block info, but I'll do that when I have enough time to be cautious about it.

Made a table to replace the diagram per JSR; conducted interviews; discussed outcomes.