Properties

Just for the record:

There is a file called properties/xml/process_properties_201-07.sh, which when invoked at the command line with a parameter which is the XML dump of the db will run two XSLT transformations (so far) to produce "complete, enhanced" output in the form of another XML file, which includes transaction chains and lots of other stuff.

These are some of the results coming out of the generation of transaction-chains through XSLT:

This is an example of what I'm pulling out so far, and the sorts of oddities that are being revealed:

<transaction-chain>
<title key="206" property-id="101" property-name="B:103 L:003"/>
  <transaction-chain>
     <title key="249" property-id="101" property-name="B:103 L:003"/>
     <title key="204" property-id="101" property-name="B:103 L:003"/>
     <title key="157" property-id="101" property-name="B:103 L:003"/>
     <title key="25" property-id="71" property-name="B:011 L:026"/>
  </transaction-chain>
  <transaction-chain>
     <title key="157" property-id="101" property-name="B:103 L:003"/>
     <title key="25" property-id="71" property-name="B:011 L:026"/>
  </transaction-chain>
</transaction-chain>

This shows nesting chains. Title 206 is the start of the initial chain; 249 is then split from it (while presumably 206 continues?). 249 becomes 204, then the split is re-joined: 157 has both 206 and 204 as preceding-titles.

I don't know if this makes sense -- can a title be split into itself and another title, as seems to be the case here with 206? There do seem to be lots of examples of this in the database.

My system currently captures splits like this well, but it doesn't yet unify chains which come back together again (so the two interior chains in the above example both have 157 -> 25). A subsequent transformation could easily detect such merges and represent them somehow, but it's not clear how. If we don't do that, then you would end up with two distinct chains:

• 206 -> 249 -> 204 -> 157 -> 25
• 206 -> 157 -> 25

This would be problematic if you were doing stats which depend on the number of transactions. We could, alternatively, collapse all chains of which one is a reduced subset of the other, so you would end up with just one here:

• 206 -> 249 -> 204 -> 157 -> 25

However, this would ignore the fact that 157 has 206 as a preceding title. It's also not clear what should happen with chains which diverge but never re-unite, such as this:

<transaction-chain>
  <title key="606" property-id="211" property-name="B:039 L:005"/>
  <transaction-chain>
     <title key="507" property-id="211" property-name="B:039 L:005"/>
     <title key="421" property-id="211" property-name="B:039 L:005"/>
  </transaction-chain>
  <transaction-chain>
     <title key="510" property-id="214" property-name="B:039 L:008"/>
     <title key="422" property-id="214" property-name="B:039 L:008"/>
  </transaction-chain>
</transaction-chain>

Here you would conceivably have two distinct chains:

• 606 -> 507 -> 421
• 606 -> 510 -> 422

and any stats based on these would end up counting the sale of 606 twice (which might well be legitimate, because it is split, so there are arguably two transactions).

It's worth noting that in most of the complex chains I'm seeing, an initial split into two or more titles is then followed by their being re-united very quickly.

Some quick stats:

• 713 primary chains exist (meaning that there are 713 chains which start from a title which has no preceding-title).
• 411 of these primary chains go nowhere (in other words, there is no subsequent title, so no transactions take place other than the primary title purchase).
• Therefore there are 302 instances of actual usable chains involving one or more sale.
• 253 of those chains are simple, in that there are no splits. (There could be unions, though, because I'm not detecting those yet).
• 53 of the chains split into sub-chains.
• 28 of the chains involve more than one property.
• 186 titles appear in more than one root transaction chain (suggesting there may be up to 100 merges between root chains, something distinct from the examples above where a root chain splits and then merges again).
• 40 root chains feature the same title more than once (meaning that the chain splits, then merges again at some point).

Today I have:

• Written a basic script to run the two saxon transforms on my original source data. This script will have more transformations added to it eventually, forming a full process from db output XML to CSV file for JS-R.
• Added detection of liquidated properties and liquidated property controls (although I'm still working on data that doesn't have the required identification of purchasers to allow detection of actual liquidations -- still waiting on JS-R to add that to the db).
• Implemented basic transaction-chain detection. This is remarkably slow, but does appear to be working. So far it's listing all titles in a single-title transaction chain. Next I need to do something when I reach a fork in the chain (perhaps generate a nested chain, which could be un-nested in the next transformation).

Moving forward. Tomorrow I should be able to finish transaction chains, and presumably get some idea from JS-R of what kind of output format he would like.

I'm now working on a second transform to be applied to the result of the first. This one already detects sale-to-self situations (although it doesn't find any -- waiting for some known data from JS-R to see why) and possible family sales. I'm now working on building the transaction chains, but I'm not sure whether this can actually be done with XSLT or not, because you need to keep a tally of which items have already been processed, and I can't yet figure out a way to do that.

In between other things, I've made a good start on the next stage (based on the notes here). I've coded a transform which does the following (so far):

• Expands each owner to include full ethnicity information inside the owner element.
• Expands each title to include a full property element inside it, as well as a list of expanded owners, and a list of preceding titles.

This is already a long way towards creating the sort of big flat file that will make it easy to trace transaction-chains back in time.

JS-R is able to work with the current spreadsheet for the May presentation, but would like some more elaborate output for the next phase. These are the details we've discussed:

• The initial db XML output should go through a transformation which basically takes all the information encoded in relations and makes it explicit on individual records. So, for instance, all owners should have explicit ethnicities realized on the owner record; each title should have complete copies of all its owners; and so on. This will make it much easier, and faster, to generate other views of the data.
• This output also needs to include some new boolean flags on titles:
  • Sale to self (as currently created during the spreadsheet transaction transform).
  • Possible family sale (ditto).
  • liquidated property: properties sold by a Japanese owner to custodians or the state between beginning 1943 and end 1946. The custodian category would be set as an institution type by JS-R.
  • Control for liquidated property: any title which is not flagged as above, nor is it a family transaction or a sale to self, which takes place from 1943-01-01 through 1946-12-12.
• We need a view which constitutes a chain of transactions, constructed by preceding title. The way to construct the chains is:
  • Order titles by date ascending.
  • Start from the first.
  • Look for another title which as this one as its preceding title. Add that to the chain, and continue.
  • Every time you add a title to a chain, flag the title as having been used.
  • If you find two titles with the current title as preceding, then you have a fork. Annotate the end of the current chain to point to those two titles, and start new chains from each of those titles. Annotate the first link in the new chains to point back to the fork title.
  • If your current chain hits a title which has already been used, then you have a merge. In that case, split the previously-constructed chain into two, and annotate the break points, and stop your current chain, annotating the end of it, so that you end up with two chains which end, pointing to another single chain which continues.
  This view will have to be realized in XML (although it's not clear whether XSLT can be used to create it -- probably) because it's not a 2-dimensional matrix. Since it's in XML, each title can bring along a full copy of all its data, including flags such as LIQ and LIQ_CONTROL, and we can then generate matrix views of the chains which are flattened in various ways. Titles in this view have an inherent generation number, which can be output to spreadsheets.
• In the current spreadsheet output, the LIQ and LIQ_CONTROL flags would be output, along with a generation number for any title which has one of these flags, constituting the count of transactions subsequent to the custodian transaction (in the case of LIQ titles), or the first transaction following 1943-01-01 (in the case of LIQ_CONTROL properties).

At JS-R's request, added a new "Description" field to the Ethnicities table.

Generated output for JS-R, who's finished the current batch of edits. Also He's asked for a large Notes field on the Owners table or the Ethnicity table (not clear yet which).

Completed the tasks set yesterday, as follows:

• Generated a list of all surnames for which multiple ethnicities are associated with owners bearing those surnames (so, for instance, if there are several owners with the surname Lee, and some have ethnicity Chinese while others have Unknown, that surname is added to the list). I used XQuery running against the XML output of the db to do this:

  xquery version "1.0";
  
  (: The purpose of this query is to find all examples where the same 
     owner surname has been associated with different ethnicities. :)
  
  declare namespace saxon="http://saxon.sf.net/";
  declare option saxon:output "method=text";
  
  let $surnames := distinct-values(//own_surname),
  (:return count($surnames):)
  $names := 
  for $name in $surnames
  order by $name
  return <name>
  <surname>{$name}</surname>
  <ids>
  {let $ids := //owners[own_surname = $name]/own_owner_id
  for $id in $ids return xs:string($id)
  }
  </ids>
  <ethnicities>
  {let $ids := //owners[own_surname = $name]/own_owner_id, $eths := //owners_to_ethnicities[ote_owner_id_fk = $ids]
  for $eth in $eths
  return <eth>{$eth/ote_ethnicity_id_fk/text()}</eth>
  }
  </ethnicities>
  </name>
  
  return 
  <names>
  {for $n in $names
  where count(distinct-values($n/ethnicities/eth)) gt 1
  return 
  concat(string-join(($n/surname, ': ',
  for $e in distinct-values($n//eth)
  return //ethnicities[eth_ethnicity_id = $e]/eth_name/text()), ' '), '&#xa;')
  }
  return 
  </names>
  

• Generated a list of owners who have the same surname and forename, and are associated with titles on the same property. These are likely to be either duplicate owners or cross-generation family transactions. Again, this was done with XQuery:

  xquery version "1.0";
  
  (: The purpose of this query is to pull out instances of 
     owners who have the same surname and forename, 
     and who are associated with titles that have the same
     property. :)
     
     (:declare namespace map="http://www.w3.org/2005/xpath-functions/map";:)
     
  declare namespace saxon="http://saxon.sf.net/";
  declare option saxon:output "method=text";
     
     let $owners := //owners,
     $dupes := for $curr in $owners where $curr/following-sibling::owners[own_surname = $curr/own_surname and own_forenames = $curr/own_forenames and $curr/own_surname != '' and $curr/own_forenames != ''] return $curr,
     $dupeIds := for $d in $dupes 
        let $owner_set := //owners[own_surname = $d/own_surname and own_forenames = $d/own_forenames]
        return <group>
        <surname>{$d/own_surname/text()}</surname>
        <forenames>{$d/own_forenames/text()}</forenames>
        {$owner_set//own_owner_id}
        <properties>
        {let $owner_ids := $owner_set//own_owner_id/text(),
              $titles_for_group := (//owners_to_titles[ott_owner_id_fk = $owner_ids], //sellers_to_titles[stt_owner_id_fk = $owner_ids])
          for $t in $titles_for_group
          return
          if ($t/ott_owner_id_fk) then
          <title><title_id>{$t/ott_title_id_fk/text()}</title_id> <property_id>{//titles[ttl_title_id = $t/ott_title_id_fk/text()]/ttl_property_id_fk/text()}</property_id></title>
          else
          <title><title_id>{$t/stt_title_id_fk/text()}</title_id> <property_id>{//titles[ttl_title_id = $t/stt_title_id_fk/text()]/ttl_property_id_fk/text()}</property_id></title>
        </properties>
        </group>
        
        
     
     return 
     <result>
     {for $d in
     $dupeIds
     where count($d//property_id) gt count(distinct-values($d//property_id))
     order by $d/surname, $d/forenames
     return 
     ('&#x0a;',
     $d/surname/text(),
     ', ',
     $d/forenames/text(),
     '&#x0a;owner ids: ',
     for $o in $d//own_owner_id
     return ($o/text(), ' '),
     for $t in $d//title return
      (
      '&#x0a;&#09;title: ', $t/title_id/text(),
      '&#09;&#09;property: ', $t/property_id/text()
      ),
     '&#x0a;&#x0a;'
     )
     }
  </result>
  

An unwanted side-effect from the way I've deduped owners was that I've ended up with duplicate records in the owners_to_ethnicities table (different primary keys, but the same owner and ethnicity). This is a quick way to fix that, which I've now implemented:

DELETE ote2
FROM owners_to_ethnicities AS ote1, owners_to_ethnicities AS ote2
WHERE ote1.ote_owner_id_fk = ote2.ote_owner_id_fk
AND ote1.ote_ethnicity_id_fk = ote2.ote_ethnicity_id_fk
AND ote2.ote_ote_id > ote1.ote_ote_id

Similar processes may have to be run on other linking tables. It's probably safer to allow the duplicate records to be created by the merge, then examine and de-dupe them, than it would be to make the de-dupe process itself, which is already complicated enough, more messy.

Got a new batch of owners to de-dupe, ran the generator script and ran the SQL on the db to merge them. SQL is below for the record.

Two new tasks for tomorrow:

• Generate a list of owners who have the same surname and forename, and who appear on titles which are linked to the same property (either as sellers or buyers).
• Generate a list of owners who have multiple ethnicities, including id and names.

UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1921" WHERE jtt_owner_id_fk = "1900"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1921" WHERE ltt_owner_id_fk = "1900"; UPDATE owners_to_titles SET ott_owner_id_fk = "1921" WHERE ott_owner_id_fk = "1900"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1921" WHERE stt_owner_id_fk = "1900"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1921" WHERE ote_owner_id_fk = "1900"; DELETE FROM owners WHERE own_owner_id = "1900";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2356" WHERE jtt_owner_id_fk = "2340"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2356" WHERE ltt_owner_id_fk = "2340"; UPDATE owners_to_titles SET ott_owner_id_fk = "2356" WHERE ott_owner_id_fk = "2340"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2356" WHERE stt_owner_id_fk = "2340"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2356" WHERE ote_owner_id_fk = "2340"; DELETE FROM owners WHERE own_owner_id = "2340";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2496" WHERE jtt_owner_id_fk = "2049"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2496" WHERE ltt_owner_id_fk = "2049"; UPDATE owners_to_titles SET ott_owner_id_fk = "2496" WHERE ott_owner_id_fk = "2049"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2496" WHERE stt_owner_id_fk = "2049"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2496" WHERE ote_owner_id_fk = "2049"; DELETE FROM owners WHERE own_owner_id = "2049";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2477" WHERE jtt_owner_id_fk = "2259"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2477" WHERE ltt_owner_id_fk = "2259"; UPDATE owners_to_titles SET ott_owner_id_fk = "2477" WHERE ott_owner_id_fk = "2259"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2477" WHERE stt_owner_id_fk = "2259"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2477" WHERE ote_owner_id_fk = "2259"; DELETE FROM owners WHERE own_owner_id = "2259";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2473" WHERE jtt_owner_id_fk = "2246"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2473" WHERE ltt_owner_id_fk = "2246"; UPDATE owners_to_titles SET ott_owner_id_fk = "2473" WHERE ott_owner_id_fk = "2246"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2473" WHERE stt_owner_id_fk = "2246"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2473" WHERE ote_owner_id_fk = "2246"; DELETE FROM owners WHERE own_owner_id = "2246";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2486" WHERE jtt_owner_id_fk = "2279"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2486" WHERE ltt_owner_id_fk = "2279"; UPDATE owners_to_titles SET ott_owner_id_fk = "2486" WHERE ott_owner_id_fk = "2279"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2486" WHERE stt_owner_id_fk = "2279"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2486" WHERE ote_owner_id_fk = "2279"; DELETE FROM owners WHERE own_owner_id = "2279";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2211" WHERE jtt_owner_id_fk = "2475"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2211" WHERE ltt_owner_id_fk = "2475"; UPDATE owners_to_titles SET ott_owner_id_fk = "2211" WHERE ott_owner_id_fk = "2475"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2211" WHERE stt_owner_id_fk = "2475"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2211" WHERE ote_owner_id_fk = "2475"; DELETE FROM owners WHERE own_owner_id = "2475";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2211" WHERE jtt_owner_id_fk = "2278"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2211" WHERE ltt_owner_id_fk = "2278"; UPDATE owners_to_titles SET ott_owner_id_fk = "2211" WHERE ott_owner_id_fk = "2278"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2211" WHERE stt_owner_id_fk = "2278"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2211" WHERE ote_owner_id_fk = "2278"; DELETE FROM owners WHERE own_owner_id = "2278";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2211" WHERE jtt_owner_id_fk = "2485"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2211" WHERE ltt_owner_id_fk = "2485"; UPDATE owners_to_titles SET ott_owner_id_fk = "2211" WHERE ott_owner_id_fk = "2485"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2211" WHERE stt_owner_id_fk = "2485"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2211" WHERE ote_owner_id_fk = "2485"; DELETE FROM owners WHERE own_owner_id = "2485";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2481" WHERE jtt_owner_id_fk = "2263"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2481" WHERE ltt_owner_id_fk = "2263"; UPDATE owners_to_titles SET ott_owner_id_fk = "2481" WHERE ott_owner_id_fk = "2263"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2481" WHERE stt_owner_id_fk = "2263"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2481" WHERE ote_owner_id_fk = "2263"; DELETE FROM owners WHERE own_owner_id = "2263";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2139" WHERE jtt_owner_id_fk = "2462"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2139" WHERE ltt_owner_id_fk = "2462"; UPDATE owners_to_titles SET ott_owner_id_fk = "2139" WHERE ott_owner_id_fk = "2462"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2139" WHERE stt_owner_id_fk = "2462"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2139" WHERE ote_owner_id_fk = "2462"; DELETE FROM owners WHERE own_owner_id = "2462";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2139" WHERE jtt_owner_id_fk = "1912"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2139" WHERE ltt_owner_id_fk = "1912"; UPDATE owners_to_titles SET ott_owner_id_fk = "2139" WHERE ott_owner_id_fk = "1912"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2139" WHERE stt_owner_id_fk = "1912"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2139" WHERE ote_owner_id_fk = "1912"; DELETE FROM owners WHERE own_owner_id = "1912";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2487" WHERE jtt_owner_id_fk = "2276"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2487" WHERE ltt_owner_id_fk = "2276"; UPDATE owners_to_titles SET ott_owner_id_fk = "2487" WHERE ott_owner_id_fk = "2276"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2487" WHERE stt_owner_id_fk = "2276"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2487" WHERE ote_owner_id_fk = "2276"; DELETE FROM owners WHERE own_owner_id = "2276";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2465" WHERE jtt_owner_id_fk = "2134"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2465" WHERE ltt_owner_id_fk = "2134"; UPDATE owners_to_titles SET ott_owner_id_fk = "2465" WHERE ott_owner_id_fk = "2134"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2465" WHERE stt_owner_id_fk = "2134"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2465" WHERE ote_owner_id_fk = "2134"; DELETE FROM owners WHERE own_owner_id = "2134";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2466" WHERE jtt_owner_id_fk = "2202"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2466" WHERE ltt_owner_id_fk = "2202"; UPDATE owners_to_titles SET ott_owner_id_fk = "2466" WHERE ott_owner_id_fk = "2202"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2466" WHERE stt_owner_id_fk = "2202"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2466" WHERE ote_owner_id_fk = "2202"; DELETE FROM owners WHERE own_owner_id = "2202";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2484" WHERE jtt_owner_id_fk = "2277"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2484" WHERE ltt_owner_id_fk = "2277"; UPDATE owners_to_titles SET ott_owner_id_fk = "2484" WHERE ott_owner_id_fk = "2277"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2484" WHERE stt_owner_id_fk = "2277"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2484" WHERE ote_owner_id_fk = "2277"; DELETE FROM owners WHERE own_owner_id = "2277";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2482" WHERE jtt_owner_id_fk = "2264"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2482" WHERE ltt_owner_id_fk = "2264"; UPDATE owners_to_titles SET ott_owner_id_fk = "2482" WHERE ott_owner_id_fk = "2264"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2482" WHERE stt_owner_id_fk = "2264"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2482" WHERE ote_owner_id_fk = "2264"; DELETE FROM owners WHERE own_owner_id = "2264";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2023" WHERE jtt_owner_id_fk = "2459"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2023" WHERE ltt_owner_id_fk = "2459"; UPDATE owners_to_titles SET ott_owner_id_fk = "2023" WHERE ott_owner_id_fk = "2459"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2023" WHERE stt_owner_id_fk = "2459"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2023" WHERE ote_owner_id_fk = "2459"; DELETE FROM owners WHERE own_owner_id = "2459";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2023" WHERE jtt_owner_id_fk = "2495"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2023" WHERE ltt_owner_id_fk = "2495"; UPDATE owners_to_titles SET ott_owner_id_fk = "2023" WHERE ott_owner_id_fk = "2495"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2023" WHERE stt_owner_id_fk = "2495"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2023" WHERE ote_owner_id_fk = "2495"; DELETE FROM owners WHERE own_owner_id = "2495";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2474" WHERE jtt_owner_id_fk = "2247"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2474" WHERE ltt_owner_id_fk = "2247"; UPDATE owners_to_titles SET ott_owner_id_fk = "2474" WHERE ott_owner_id_fk = "2247"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2474" WHERE stt_owner_id_fk = "2247"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2474" WHERE ote_owner_id_fk = "2247"; DELETE FROM owners WHERE own_owner_id = "2247";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2478" WHERE jtt_owner_id_fk = "2260"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2478" WHERE ltt_owner_id_fk = "2260"; UPDATE owners_to_titles SET ott_owner_id_fk = "2478" WHERE ott_owner_id_fk = "2260"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2478" WHERE stt_owner_id_fk = "2260"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2478" WHERE ote_owner_id_fk = "2260"; DELETE FROM owners WHERE own_owner_id = "2260";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2008" WHERE jtt_owner_id_fk = "2471"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2008" WHERE ltt_owner_id_fk = "2471"; UPDATE owners_to_titles SET ott_owner_id_fk = "2008" WHERE ott_owner_id_fk = "2471"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2008" WHERE stt_owner_id_fk = "2471"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2008" WHERE ote_owner_id_fk = "2471"; DELETE FROM owners WHERE own_owner_id = "2471";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2008" WHERE jtt_owner_id_fk = "2090"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2008" WHERE ltt_owner_id_fk = "2090"; UPDATE owners_to_titles SET ott_owner_id_fk = "2008" WHERE ott_owner_id_fk = "2090"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2008" WHERE stt_owner_id_fk = "2090"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2008" WHERE ote_owner_id_fk = "2090"; DELETE FROM owners WHERE own_owner_id = "2090";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2008" WHERE jtt_owner_id_fk = "1992"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2008" WHERE ltt_owner_id_fk = "1992"; UPDATE owners_to_titles SET ott_owner_id_fk = "2008" WHERE ott_owner_id_fk = "1992"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2008" WHERE stt_owner_id_fk = "1992"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2008" WHERE ote_owner_id_fk = "1992"; DELETE FROM owners WHERE own_owner_id = "1992";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2022" WHERE jtt_owner_id_fk = "2458"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2022" WHERE ltt_owner_id_fk = "2458"; UPDATE owners_to_titles SET ott_owner_id_fk = "2022" WHERE ott_owner_id_fk = "2458"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2022" WHERE stt_owner_id_fk = "2458"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2022" WHERE ote_owner_id_fk = "2458"; DELETE FROM owners WHERE own_owner_id = "2458";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2022" WHERE jtt_owner_id_fk = "2494"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2022" WHERE ltt_owner_id_fk = "2494"; UPDATE owners_to_titles SET ott_owner_id_fk = "2022" WHERE ott_owner_id_fk = "2494"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2022" WHERE stt_owner_id_fk = "2494"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2022" WHERE ote_owner_id_fk = "2494"; DELETE FROM owners WHERE own_owner_id = "2494";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2476" WHERE jtt_owner_id_fk = "2248"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2476" WHERE ltt_owner_id_fk = "2248"; UPDATE owners_to_titles SET ott_owner_id_fk = "2476" WHERE ott_owner_id_fk = "2248"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2476" WHERE stt_owner_id_fk = "2248"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2476" WHERE ote_owner_id_fk = "2248"; DELETE FROM owners WHERE own_owner_id = "2248";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2488" WHERE jtt_owner_id_fk = "2024"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2488" WHERE ltt_owner_id_fk = "2024"; UPDATE owners_to_titles SET ott_owner_id_fk = "2488" WHERE ott_owner_id_fk = "2024"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2488" WHERE stt_owner_id_fk = "2024"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2488" WHERE ote_owner_id_fk = "2024"; DELETE FROM owners WHERE own_owner_id = "2024";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2453" WHERE jtt_owner_id_fk = "2025"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2453" WHERE ltt_owner_id_fk = "2025"; UPDATE owners_to_titles SET ott_owner_id_fk = "2453" WHERE ott_owner_id_fk = "2025"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2453" WHERE stt_owner_id_fk = "2025"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2453" WHERE ote_owner_id_fk = "2025"; DELETE FROM owners WHERE own_owner_id = "2025";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2455" WHERE jtt_owner_id_fk = "2026"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2455" WHERE ltt_owner_id_fk = "2026"; UPDATE owners_to_titles SET ott_owner_id_fk = "2455" WHERE ott_owner_id_fk = "2026"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2455" WHERE stt_owner_id_fk = "2026"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2455" WHERE ote_owner_id_fk = "2026"; DELETE FROM owners WHERE own_owner_id = "2026";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2454" WHERE jtt_owner_id_fk = "2018"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2454" WHERE ltt_owner_id_fk = "2018"; UPDATE owners_to_titles SET ott_owner_id_fk = "2454" WHERE ott_owner_id_fk = "2018"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2454" WHERE stt_owner_id_fk = "2018"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2454" WHERE ote_owner_id_fk = "2018"; DELETE FROM owners WHERE own_owner_id = "2018";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2490" WHERE jtt_owner_id_fk = "2018"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2490" WHERE ltt_owner_id_fk = "2018"; UPDATE owners_to_titles SET ott_owner_id_fk = "2490" WHERE ott_owner_id_fk = "2018"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2490" WHERE stt_owner_id_fk = "2018"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2490" WHERE ote_owner_id_fk = "2018"; DELETE FROM owners WHERE own_owner_id = "2018";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2021" WHERE jtt_owner_id_fk = "2457"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2021" WHERE ltt_owner_id_fk = "2457"; UPDATE owners_to_titles SET ott_owner_id_fk = "2021" WHERE ott_owner_id_fk = "2457"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2021" WHERE stt_owner_id_fk = "2457"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2021" WHERE ote_owner_id_fk = "2457"; DELETE FROM owners WHERE own_owner_id = "2457";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2021" WHERE jtt_owner_id_fk = "2493"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2021" WHERE ltt_owner_id_fk = "2493"; UPDATE owners_to_titles SET ott_owner_id_fk = "2021" WHERE ott_owner_id_fk = "2493"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2021" WHERE stt_owner_id_fk = "2493"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2021" WHERE ote_owner_id_fk = "2493"; DELETE FROM owners WHERE own_owner_id = "2493";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2491" WHERE jtt_owner_id_fk = "2019"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2491" WHERE ltt_owner_id_fk = "2019"; UPDATE owners_to_titles SET ott_owner_id_fk = "2491" WHERE ott_owner_id_fk = "2019"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2491" WHERE stt_owner_id_fk = "2019"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2491" WHERE ote_owner_id_fk = "2019"; DELETE FROM owners WHERE own_owner_id = "2019";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2020" WHERE jtt_owner_id_fk = "2456"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2020" WHERE ltt_owner_id_fk = "2456"; UPDATE owners_to_titles SET ott_owner_id_fk = "2020" WHERE ott_owner_id_fk = "2456"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2020" WHERE stt_owner_id_fk = "2456"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2020" WHERE ote_owner_id_fk = "2456"; DELETE FROM owners WHERE own_owner_id = "2456";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2020" WHERE jtt_owner_id_fk = "2492"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2020" WHERE ltt_owner_id_fk = "2492"; UPDATE owners_to_titles SET ott_owner_id_fk = "2020" WHERE ott_owner_id_fk = "2492"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2020" WHERE stt_owner_id_fk = "2492"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2020" WHERE ote_owner_id_fk = "2492"; DELETE FROM owners WHERE own_owner_id = "2492";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2489" WHERE jtt_owner_id_fk = "2017"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2489" WHERE ltt_owner_id_fk = "2017"; UPDATE owners_to_titles SET ott_owner_id_fk = "2489" WHERE ott_owner_id_fk = "2017"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2489" WHERE stt_owner_id_fk = "2017"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2489" WHERE ote_owner_id_fk = "2017"; DELETE FROM owners WHERE own_owner_id = "2017";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "338" WHERE jtt_owner_id_fk = "337"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "338" WHERE ltt_owner_id_fk = "337"; UPDATE owners_to_titles SET ott_owner_id_fk = "338" WHERE ott_owner_id_fk = "337"; UPDATE sellers_to_titles SET stt_owner_id_fk = "338" WHERE stt_owner_id_fk = "337"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "338" WHERE ote_owner_id_fk = "337"; DELETE FROM owners WHERE own_owner_id = "337";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2463" WHERE jtt_owner_id_fk = "2140"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2463" WHERE ltt_owner_id_fk = "2140"; UPDATE owners_to_titles SET ott_owner_id_fk = "2463" WHERE ott_owner_id_fk = "2140"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2463" WHERE stt_owner_id_fk = "2140"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2463" WHERE ote_owner_id_fk = "2140"; DELETE FROM owners WHERE own_owner_id = "2140";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2469" WHERE jtt_owner_id_fk = "2146"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2469" WHERE ltt_owner_id_fk = "2146"; UPDATE owners_to_titles SET ott_owner_id_fk = "2469" WHERE ott_owner_id_fk = "2146"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2469" WHERE stt_owner_id_fk = "2146"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2469" WHERE ote_owner_id_fk = "2146"; DELETE FROM owners WHERE own_owner_id = "2146";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2472" WHERE jtt_owner_id_fk = "2009"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2472" WHERE ltt_owner_id_fk = "2009"; UPDATE owners_to_titles SET ott_owner_id_fk = "2472" WHERE ott_owner_id_fk = "2009"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2472" WHERE stt_owner_id_fk = "2009"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2472" WHERE ote_owner_id_fk = "2009"; DELETE FROM owners WHERE own_owner_id = "2009";

To get test data against which to check my XSLT implementation, I ran this against the database to get back instances of buyers and sellers sharing surnames (some because they were identical, some not):

Searching for family transactions:

SELECT titles.ttl_title_id, titles.ttl_title_code, 
sellers.own_owner_id AS seller_id, buyers.own_owner_id AS buyer_id 
FROM titles 
LEFT JOIN owners_to_titles on titles.ttl_title_id = owners_to_titles.ott_title_id_fk
LEFT JOIN owners AS buyers on owners_to_titles.ott_owner_id_fk = buyers.own_owner_id
LEFT JOIN sellers_to_titles on titles.ttl_title_id = sellers_to_titles.stt_title_id_fk
LEFT JOIN owners AS sellers on sellers_to_titles.stt_owner_id_fk = sellers.own_owner_id
WHERE buyers.own_surname = sellers.own_surname
AND buyers.own_owner_id IS NOT NULL
AND sellers.own_owner_id IS NOT NULL
LIMIT 0, 500

Then I wrote the XSLT to generate those two fields. It's a bit tricky to disambiguate the two fields -- all instances of Buyer_is_seller do have the same surname, of course, so you have to exclude them -- but I think I have it working OK.

In the process, I discovered a lot more candidate owner duplicates, so my process last week was obviously too cautious. I've re-run it and generated a new list, which JS-R will look at, then I'll de-dupe those.

Discussion with JS-R by phone, during which we determined some immediate tasks:

• DONE: Fix the proliferation of new individual owners added with only display_name info (no surname or forename). Discovered 262 of this, like this:

  SELECT * FROM `owners` WHERE own_institutional=0 AND own_surname=""
  

  and at JS-R's request, fixed 260 of them with this:

  UPDATE `owners` SET `own_surname` = (SELECT RIGHT( `own_display_name` , LOCATE( ' ', REVERSE( `own_display_name` ) ) - 1 )) WHERE CHAR_LENGTH(`own_surname`) = 0 and own_institutional=0;
  
  UPDATE `owners` SET `own_forenames` = (SELECT LEFT( `own_display_name` , CHAR_LENGTH( `own_display_name`) - LOCATE( ' ', REVERSE( `own_display_name` ) ) )) WHERE CHAR_LENGTH(`own_forenames`) = 0 and own_institutional = 0;
  

• DONE: Generate a list of owners not associated with any title. That was done with this:

  SELECT owners.* FROM owners 
       LEFT JOIN owners_to_titles ON owners.own_owner_id=owners_to_titles.ott_owner_id_fk
  WHERE owners_to_titles.ott_owner_id_fk IS NULL;
  

  and sent to JS-R in the form of a spreadsheet. These owners may be linked in some other way, of course -- as sellers, or through mortgages. "owners" is actually "actors" (individuals or companies who act in transactions).
• TODO: Add a new flag to the new spreadsheet generated from XML, which is true when the same individual is involved both as a buyer and a seller in a transaction (buyer_is_seller).
• TODO: Add a new flag to the new spreadsheet generated from XML, which is true when there is a buyer who has the same surname as a seller, but is a different individual (poss_family_transaction).

Got back a spreadsheet with owners who can be merged, and ran the automated process, testing first on the dev db. One issue was with multiple owners who are all to be merged into one; these operations obviously have to be done in the right order. Once that was sorted out, and some two-way dupes (x=y and y=x) taken care of, the process seems to have gone smoothly. This is the dupe list and the SQL, for the record:

48|902
49|51
65|155
73|253
101|104
101|436
104|436
187|250
202|252
330|332
459|553
460|555
483|502
544|454
569|471
1131|1141
1863|1864
1870|1872
1870|1999
1870|2076
1872|1999
1872|2076
1875|1877
1887|1889
1897|1898
1917|1926
1946|2100
1979|1980
1979|1981
1980|1981
1999|2076
2015|2034
2024|2016
2032|2053
2041|2165
2041|2166
2070|2158
2075|2077
2092|2093
2163|2164
2165|2166
2171|2172
2194|2154
2220|2221
2286|2287
2452|2024
2452|2024
2452|2016


UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "902" WHERE jtt_owner_id_fk = "48"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "902" WHERE ltt_owner_id_fk = "48"; UPDATE owners_to_titles SET ott_owner_id_fk = "902" WHERE ott_owner_id_fk = "48"; UPDATE sellers_to_titles SET stt_owner_id_fk = "902" WHERE stt_owner_id_fk = "48"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "902" WHERE ote_owner_id_fk = "48"; DELETE FROM owners WHERE own_owner_id = "48";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "51" WHERE jtt_owner_id_fk = "49"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "51" WHERE ltt_owner_id_fk = "49"; UPDATE owners_to_titles SET ott_owner_id_fk = "51" WHERE ott_owner_id_fk = "49"; UPDATE sellers_to_titles SET stt_owner_id_fk = "51" WHERE stt_owner_id_fk = "49"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "51" WHERE ote_owner_id_fk = "49"; DELETE FROM owners WHERE own_owner_id = "49";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "155" WHERE jtt_owner_id_fk = "65"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "155" WHERE ltt_owner_id_fk = "65"; UPDATE owners_to_titles SET ott_owner_id_fk = "155" WHERE ott_owner_id_fk = "65"; UPDATE sellers_to_titles SET stt_owner_id_fk = "155" WHERE stt_owner_id_fk = "65"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "155" WHERE ote_owner_id_fk = "65"; DELETE FROM owners WHERE own_owner_id = "65";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "253" WHERE jtt_owner_id_fk = "73"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "253" WHERE ltt_owner_id_fk = "73"; UPDATE owners_to_titles SET ott_owner_id_fk = "253" WHERE ott_owner_id_fk = "73"; UPDATE sellers_to_titles SET stt_owner_id_fk = "253" WHERE stt_owner_id_fk = "73"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "253" WHERE ote_owner_id_fk = "73"; DELETE FROM owners WHERE own_owner_id = "73";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "104" WHERE jtt_owner_id_fk = "101"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "104" WHERE ltt_owner_id_fk = "101"; UPDATE owners_to_titles SET ott_owner_id_fk = "104" WHERE ott_owner_id_fk = "101"; UPDATE sellers_to_titles SET stt_owner_id_fk = "104" WHERE stt_owner_id_fk = "101"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "104" WHERE ote_owner_id_fk = "101"; DELETE FROM owners WHERE own_owner_id = "101";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "436" WHERE jtt_owner_id_fk = "101"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "436" WHERE ltt_owner_id_fk = "101"; UPDATE owners_to_titles SET ott_owner_id_fk = "436" WHERE ott_owner_id_fk = "101"; UPDATE sellers_to_titles SET stt_owner_id_fk = "436" WHERE stt_owner_id_fk = "101"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "436" WHERE ote_owner_id_fk = "101"; DELETE FROM owners WHERE own_owner_id = "101";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "436" WHERE jtt_owner_id_fk = "104"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "436" WHERE ltt_owner_id_fk = "104"; UPDATE owners_to_titles SET ott_owner_id_fk = "436" WHERE ott_owner_id_fk = "104"; UPDATE sellers_to_titles SET stt_owner_id_fk = "436" WHERE stt_owner_id_fk = "104"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "436" WHERE ote_owner_id_fk = "104"; DELETE FROM owners WHERE own_owner_id = "104";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "250" WHERE jtt_owner_id_fk = "187"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "250" WHERE ltt_owner_id_fk = "187"; UPDATE owners_to_titles SET ott_owner_id_fk = "250" WHERE ott_owner_id_fk = "187"; UPDATE sellers_to_titles SET stt_owner_id_fk = "250" WHERE stt_owner_id_fk = "187"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "250" WHERE ote_owner_id_fk = "187"; DELETE FROM owners WHERE own_owner_id = "187";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "252" WHERE jtt_owner_id_fk = "202"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "252" WHERE ltt_owner_id_fk = "202"; UPDATE owners_to_titles SET ott_owner_id_fk = "252" WHERE ott_owner_id_fk = "202"; UPDATE sellers_to_titles SET stt_owner_id_fk = "252" WHERE stt_owner_id_fk = "202"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "252" WHERE ote_owner_id_fk = "202"; DELETE FROM owners WHERE own_owner_id = "202";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "332" WHERE jtt_owner_id_fk = "330"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "332" WHERE ltt_owner_id_fk = "330"; UPDATE owners_to_titles SET ott_owner_id_fk = "332" WHERE ott_owner_id_fk = "330"; UPDATE sellers_to_titles SET stt_owner_id_fk = "332" WHERE stt_owner_id_fk = "330"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "332" WHERE ote_owner_id_fk = "330"; DELETE FROM owners WHERE own_owner_id = "330";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "553" WHERE jtt_owner_id_fk = "459"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "553" WHERE ltt_owner_id_fk = "459"; UPDATE owners_to_titles SET ott_owner_id_fk = "553" WHERE ott_owner_id_fk = "459"; UPDATE sellers_to_titles SET stt_owner_id_fk = "553" WHERE stt_owner_id_fk = "459"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "553" WHERE ote_owner_id_fk = "459"; DELETE FROM owners WHERE own_owner_id = "459";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "555" WHERE jtt_owner_id_fk = "460"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "555" WHERE ltt_owner_id_fk = "460"; UPDATE owners_to_titles SET ott_owner_id_fk = "555" WHERE ott_owner_id_fk = "460"; UPDATE sellers_to_titles SET stt_owner_id_fk = "555" WHERE stt_owner_id_fk = "460"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "555" WHERE ote_owner_id_fk = "460"; DELETE FROM owners WHERE own_owner_id = "460";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "502" WHERE jtt_owner_id_fk = "483"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "502" WHERE ltt_owner_id_fk = "483"; UPDATE owners_to_titles SET ott_owner_id_fk = "502" WHERE ott_owner_id_fk = "483"; UPDATE sellers_to_titles SET stt_owner_id_fk = "502" WHERE stt_owner_id_fk = "483"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "502" WHERE ote_owner_id_fk = "483"; DELETE FROM owners WHERE own_owner_id = "483";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "454" WHERE jtt_owner_id_fk = "544"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "454" WHERE ltt_owner_id_fk = "544"; UPDATE owners_to_titles SET ott_owner_id_fk = "454" WHERE ott_owner_id_fk = "544"; UPDATE sellers_to_titles SET stt_owner_id_fk = "454" WHERE stt_owner_id_fk = "544"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "454" WHERE ote_owner_id_fk = "544"; DELETE FROM owners WHERE own_owner_id = "544";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "471" WHERE jtt_owner_id_fk = "569"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "471" WHERE ltt_owner_id_fk = "569"; UPDATE owners_to_titles SET ott_owner_id_fk = "471" WHERE ott_owner_id_fk = "569"; UPDATE sellers_to_titles SET stt_owner_id_fk = "471" WHERE stt_owner_id_fk = "569"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "471" WHERE ote_owner_id_fk = "569"; DELETE FROM owners WHERE own_owner_id = "569";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1141" WHERE jtt_owner_id_fk = "1131"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1141" WHERE ltt_owner_id_fk = "1131"; UPDATE owners_to_titles SET ott_owner_id_fk = "1141" WHERE ott_owner_id_fk = "1131"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1141" WHERE stt_owner_id_fk = "1131"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1141" WHERE ote_owner_id_fk = "1131"; DELETE FROM owners WHERE own_owner_id = "1131";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1864" WHERE jtt_owner_id_fk = "1863"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1864" WHERE ltt_owner_id_fk = "1863"; UPDATE owners_to_titles SET ott_owner_id_fk = "1864" WHERE ott_owner_id_fk = "1863"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1864" WHERE stt_owner_id_fk = "1863"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1864" WHERE ote_owner_id_fk = "1863"; DELETE FROM owners WHERE own_owner_id = "1863";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1872" WHERE jtt_owner_id_fk = "1870"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1872" WHERE ltt_owner_id_fk = "1870"; UPDATE owners_to_titles SET ott_owner_id_fk = "1872" WHERE ott_owner_id_fk = "1870"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1872" WHERE stt_owner_id_fk = "1870"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1872" WHERE ote_owner_id_fk = "1870"; DELETE FROM owners WHERE own_owner_id = "1870";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1999" WHERE jtt_owner_id_fk = "1870"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1999" WHERE ltt_owner_id_fk = "1870"; UPDATE owners_to_titles SET ott_owner_id_fk = "1999" WHERE ott_owner_id_fk = "1870"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1999" WHERE stt_owner_id_fk = "1870"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1999" WHERE ote_owner_id_fk = "1870"; DELETE FROM owners WHERE own_owner_id = "1870";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2076" WHERE jtt_owner_id_fk = "1870"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2076" WHERE ltt_owner_id_fk = "1870"; UPDATE owners_to_titles SET ott_owner_id_fk = "2076" WHERE ott_owner_id_fk = "1870"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2076" WHERE stt_owner_id_fk = "1870"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2076" WHERE ote_owner_id_fk = "1870"; DELETE FROM owners WHERE own_owner_id = "1870";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1999" WHERE jtt_owner_id_fk = "1872"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1999" WHERE ltt_owner_id_fk = "1872"; UPDATE owners_to_titles SET ott_owner_id_fk = "1999" WHERE ott_owner_id_fk = "1872"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1999" WHERE stt_owner_id_fk = "1872"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1999" WHERE ote_owner_id_fk = "1872"; DELETE FROM owners WHERE own_owner_id = "1872";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2076" WHERE jtt_owner_id_fk = "1872"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2076" WHERE ltt_owner_id_fk = "1872"; UPDATE owners_to_titles SET ott_owner_id_fk = "2076" WHERE ott_owner_id_fk = "1872"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2076" WHERE stt_owner_id_fk = "1872"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2076" WHERE ote_owner_id_fk = "1872"; DELETE FROM owners WHERE own_owner_id = "1872";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1877" WHERE jtt_owner_id_fk = "1875"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1877" WHERE ltt_owner_id_fk = "1875"; UPDATE owners_to_titles SET ott_owner_id_fk = "1877" WHERE ott_owner_id_fk = "1875"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1877" WHERE stt_owner_id_fk = "1875"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1877" WHERE ote_owner_id_fk = "1875"; DELETE FROM owners WHERE own_owner_id = "1875";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1889" WHERE jtt_owner_id_fk = "1887"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1889" WHERE ltt_owner_id_fk = "1887"; UPDATE owners_to_titles SET ott_owner_id_fk = "1889" WHERE ott_owner_id_fk = "1887"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1889" WHERE stt_owner_id_fk = "1887"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1889" WHERE ote_owner_id_fk = "1887"; DELETE FROM owners WHERE own_owner_id = "1887";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1898" WHERE jtt_owner_id_fk = "1897"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1898" WHERE ltt_owner_id_fk = "1897"; UPDATE owners_to_titles SET ott_owner_id_fk = "1898" WHERE ott_owner_id_fk = "1897"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1898" WHERE stt_owner_id_fk = "1897"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1898" WHERE ote_owner_id_fk = "1897"; DELETE FROM owners WHERE own_owner_id = "1897";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1926" WHERE jtt_owner_id_fk = "1917"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1926" WHERE ltt_owner_id_fk = "1917"; UPDATE owners_to_titles SET ott_owner_id_fk = "1926" WHERE ott_owner_id_fk = "1917"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1926" WHERE stt_owner_id_fk = "1917"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1926" WHERE ote_owner_id_fk = "1917"; DELETE FROM owners WHERE own_owner_id = "1917";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2100" WHERE jtt_owner_id_fk = "1946"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2100" WHERE ltt_owner_id_fk = "1946"; UPDATE owners_to_titles SET ott_owner_id_fk = "2100" WHERE ott_owner_id_fk = "1946"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2100" WHERE stt_owner_id_fk = "1946"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2100" WHERE ote_owner_id_fk = "1946"; DELETE FROM owners WHERE own_owner_id = "1946";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1980" WHERE jtt_owner_id_fk = "1979"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1980" WHERE ltt_owner_id_fk = "1979"; UPDATE owners_to_titles SET ott_owner_id_fk = "1980" WHERE ott_owner_id_fk = "1979"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1980" WHERE stt_owner_id_fk = "1979"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1980" WHERE ote_owner_id_fk = "1979"; DELETE FROM owners WHERE own_owner_id = "1979";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1981" WHERE jtt_owner_id_fk = "1979"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1981" WHERE ltt_owner_id_fk = "1979"; UPDATE owners_to_titles SET ott_owner_id_fk = "1981" WHERE ott_owner_id_fk = "1979"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1981" WHERE stt_owner_id_fk = "1979"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1981" WHERE ote_owner_id_fk = "1979"; DELETE FROM owners WHERE own_owner_id = "1979";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "1981" WHERE jtt_owner_id_fk = "1980"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "1981" WHERE ltt_owner_id_fk = "1980"; UPDATE owners_to_titles SET ott_owner_id_fk = "1981" WHERE ott_owner_id_fk = "1980"; UPDATE sellers_to_titles SET stt_owner_id_fk = "1981" WHERE stt_owner_id_fk = "1980"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "1981" WHERE ote_owner_id_fk = "1980"; DELETE FROM owners WHERE own_owner_id = "1980";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2076" WHERE jtt_owner_id_fk = "1999"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2076" WHERE ltt_owner_id_fk = "1999"; UPDATE owners_to_titles SET ott_owner_id_fk = "2076" WHERE ott_owner_id_fk = "1999"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2076" WHERE stt_owner_id_fk = "1999"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2076" WHERE ote_owner_id_fk = "1999"; DELETE FROM owners WHERE own_owner_id = "1999";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2034" WHERE jtt_owner_id_fk = "2015"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2034" WHERE ltt_owner_id_fk = "2015"; UPDATE owners_to_titles SET ott_owner_id_fk = "2034" WHERE ott_owner_id_fk = "2015"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2034" WHERE stt_owner_id_fk = "2015"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2034" WHERE ote_owner_id_fk = "2015"; DELETE FROM owners WHERE own_owner_id = "2015";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2053" WHERE jtt_owner_id_fk = "2032"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2053" WHERE ltt_owner_id_fk = "2032"; UPDATE owners_to_titles SET ott_owner_id_fk = "2053" WHERE ott_owner_id_fk = "2032"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2053" WHERE stt_owner_id_fk = "2032"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2053" WHERE ote_owner_id_fk = "2032"; DELETE FROM owners WHERE own_owner_id = "2032";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2165" WHERE jtt_owner_id_fk = "2041"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2165" WHERE ltt_owner_id_fk = "2041"; UPDATE owners_to_titles SET ott_owner_id_fk = "2165" WHERE ott_owner_id_fk = "2041"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2165" WHERE stt_owner_id_fk = "2041"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2165" WHERE ote_owner_id_fk = "2041"; DELETE FROM owners WHERE own_owner_id = "2041";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2166" WHERE jtt_owner_id_fk = "2041"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2166" WHERE ltt_owner_id_fk = "2041"; UPDATE owners_to_titles SET ott_owner_id_fk = "2166" WHERE ott_owner_id_fk = "2041"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2166" WHERE stt_owner_id_fk = "2041"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2166" WHERE ote_owner_id_fk = "2041"; DELETE FROM owners WHERE own_owner_id = "2041";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2158" WHERE jtt_owner_id_fk = "2070"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2158" WHERE ltt_owner_id_fk = "2070"; UPDATE owners_to_titles SET ott_owner_id_fk = "2158" WHERE ott_owner_id_fk = "2070"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2158" WHERE stt_owner_id_fk = "2070"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2158" WHERE ote_owner_id_fk = "2070"; DELETE FROM owners WHERE own_owner_id = "2070";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2077" WHERE jtt_owner_id_fk = "2075"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2077" WHERE ltt_owner_id_fk = "2075"; UPDATE owners_to_titles SET ott_owner_id_fk = "2077" WHERE ott_owner_id_fk = "2075"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2077" WHERE stt_owner_id_fk = "2075"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2077" WHERE ote_owner_id_fk = "2075"; DELETE FROM owners WHERE own_owner_id = "2075";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2093" WHERE jtt_owner_id_fk = "2092"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2093" WHERE ltt_owner_id_fk = "2092"; UPDATE owners_to_titles SET ott_owner_id_fk = "2093" WHERE ott_owner_id_fk = "2092"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2093" WHERE stt_owner_id_fk = "2092"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2093" WHERE ote_owner_id_fk = "2092"; DELETE FROM owners WHERE own_owner_id = "2092";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2164" WHERE jtt_owner_id_fk = "2163"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2164" WHERE ltt_owner_id_fk = "2163"; UPDATE owners_to_titles SET ott_owner_id_fk = "2164" WHERE ott_owner_id_fk = "2163"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2164" WHERE stt_owner_id_fk = "2163"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2164" WHERE ote_owner_id_fk = "2163"; DELETE FROM owners WHERE own_owner_id = "2163";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2166" WHERE jtt_owner_id_fk = "2165"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2166" WHERE ltt_owner_id_fk = "2165"; UPDATE owners_to_titles SET ott_owner_id_fk = "2166" WHERE ott_owner_id_fk = "2165"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2166" WHERE stt_owner_id_fk = "2165"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2166" WHERE ote_owner_id_fk = "2165"; DELETE FROM owners WHERE own_owner_id = "2165";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2172" WHERE jtt_owner_id_fk = "2171"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2172" WHERE ltt_owner_id_fk = "2171"; UPDATE owners_to_titles SET ott_owner_id_fk = "2172" WHERE ott_owner_id_fk = "2171"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2172" WHERE stt_owner_id_fk = "2171"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2172" WHERE ote_owner_id_fk = "2171"; DELETE FROM owners WHERE own_owner_id = "2171";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2154" WHERE jtt_owner_id_fk = "2194"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2154" WHERE ltt_owner_id_fk = "2194"; UPDATE owners_to_titles SET ott_owner_id_fk = "2154" WHERE ott_owner_id_fk = "2194"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2154" WHERE stt_owner_id_fk = "2194"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2154" WHERE ote_owner_id_fk = "2194"; DELETE FROM owners WHERE own_owner_id = "2194";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2221" WHERE jtt_owner_id_fk = "2220"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2221" WHERE ltt_owner_id_fk = "2220"; UPDATE owners_to_titles SET ott_owner_id_fk = "2221" WHERE ott_owner_id_fk = "2220"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2221" WHERE stt_owner_id_fk = "2220"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2221" WHERE ote_owner_id_fk = "2220"; DELETE FROM owners WHERE own_owner_id = "2220";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2287" WHERE jtt_owner_id_fk = "2286"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2287" WHERE ltt_owner_id_fk = "2286"; UPDATE owners_to_titles SET ott_owner_id_fk = "2287" WHERE ott_owner_id_fk = "2286"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2287" WHERE stt_owner_id_fk = "2286"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2287" WHERE ote_owner_id_fk = "2286"; DELETE FROM owners WHERE own_owner_id = "2286";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2024" WHERE jtt_owner_id_fk = "2452"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2024" WHERE ltt_owner_id_fk = "2452"; UPDATE owners_to_titles SET ott_owner_id_fk = "2024" WHERE ott_owner_id_fk = "2452"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2024" WHERE stt_owner_id_fk = "2452"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2024" WHERE ote_owner_id_fk = "2452"; DELETE FROM owners WHERE own_owner_id = "2452";
UPDATE joint_tenants_to_titles SET jtt_owner_id_fk = "2016" WHERE jtt_owner_id_fk = "2452"; UPDATE lawyers_to_titles SET ltt_owner_id_fk = "2016" WHERE ltt_owner_id_fk = "2452"; UPDATE owners_to_titles SET ott_owner_id_fk = "2016" WHERE ott_owner_id_fk = "2452"; UPDATE sellers_to_titles SET stt_owner_id_fk = "2016" WHERE stt_owner_id_fk = "2452"; UPDATE owners_to_ethnicities SET ote_owner_id_fk = "2016" WHERE ote_owner_id_fk = "2452"; DELETE FROM owners WHERE own_owner_id = "2452";

Tomorrow a list of duplicate owners to be merged will come back from JS-R. In preparation, I've written a script that will automatically produce the correct SQL to do the job (which involves changes to five tables), along with some SQL that can be used to check that the process went OK.

I've spent most of the day working on building the new transaction view in the form of a spreadsheet, and I think it's about 2/3 done. I have ethnicity-munging working in the XSLT, as well as munging of institution types for multiple institutions. In the process, I've found and fixed some data-entry issues (as always).

I now have a two-stage XSLT process. The first stage, working on the XML output from the db, takes several minutes; this generates a view of the database which is centred specifically on transactions, importing all relevant data for each transaction into the transaction itself. Once this is done, the second-stage process then processes that output to create a spreadsheet; this is much faster (a few seconds), and it's easier to work on and test the second process without having to run the first process over and over again.

I have a script currently generating a list of candidate duplicate owners. This is how it was done:

• Export the owners table as XML.
• Process with XSLT to sort by surname and forename fields.
• Edit so each each record constitutes a single line.
• Remove bracketing XML container, and add five blank lines.
• Run the following script:

#!/bin/bash

#This script is designed to run a series of comparison tests of xml-encoded owner
#records in an attempt to discover possible duplicates, which are then to be investigated
#by the PI manually.


#Threshold below which to consider a possible dupe
MINSIM=0.1

#First, paths to files.
USM_JAR=/home/mholmes/WorkData/netbeans/uniSimMetric/dist/uniSimMetric.jar
NCD_COMMAND="ncd -l "


INPUTFILE=/home/mholmes/WorkData/history/stanger-ross/properties/xml/owners_12_04_27_flattened.txt
OUTFILE="/home/mholmes/WorkData/history/stanger-ross/properties/xml/owner_dupe_candidates_`date +%Y%m%d`.txt"

#Echo the start out to the output file.
echo "Possible duplicate owners found by string comparison using USM">$OUTFILE
echo "">$OUTFILE

#Initialize a counter
C=0
#Read in the inputs line by line
cat $INPUTFILE | while read line; 
do 
#Ignore empty lines. This ensures we can read five lines forward (there are five empty lines at the end of the file).
	let "C=$C+1"
	LEN=${#line}
	if [ $LEN -gt "3" ]; 
		then
		for ((N=$C+1; N<$C+6; N++))
		do
			STR2=`awk NR==${N} $INPUTFILE`; 
#Call the USM to compare them.
			USM=`java -jar $USM_JAR -compare -str1="$line" -str2="$STR2"`
#Call NCD to compare them
#			NCD=`$NCD_COMMAND "$line" "$STR2"`
#NCD outputs the second string on the command line before the score; we need to remove it.
#			NCD=${NCD/$STR2}
#If the threshold similarity is greater than the specified value, output info to the output file.
			if [[ "$USM" < "$MINSIM" ]];
			then
				echo "Found similarity"
				echo $line | sed -n 's/.*<owners><own_owner_id>\(.*\)<\/own_owner_id>.*/\1/p'>>$OUTFILE
				echo $STR2 | sed -n 's/.*<owners><own_owner_id>\(.*\)<\/own_owner_id>.*/\1/p'>>$OUTFILE
				echo "">>$OUTFILE
			fi
		done
	fi
		 
done

#Display the output file.
`gedit $OUTFILE`

echo "Done!"
exit

This is successfully producing a list of candidate matches right now, outputting the ids of the two candidates followed by a blank line, for each candidate match.

Isolated seven more problematic transactions, and fixed two of them (bad dates); started creating xsl:keys to speed up processing, and mapped tract and property information into transactions.

Suggestion from JS-R:

Maybe three variables.

1. A "true"/"false" variable--is at least one of the buyers an institution?

2. If true, what is the institution type:
	(1) Ethnic
	(2) Private
	(3) Public
	(4) Multiple

3. If there is only one institution among the buyers, what is its name?

At JS-R's request, dealt with the problem of owner names which were prefixed with "Transfer: " in the following way:

Created a new column for this info:

ALTER TABLE `owners` ADD COLUMN `own_diff_trans_name` BOOLEAN DEFAULT False NOT NULL AFTER `own_display_name`;

Checked how many rows would be affected (139):

SELECT * FROM `owners` WHERE LEFT(`own_display_name` , 10 ) = "Transfer: ";

Moved the info from the display name field to the new field:

UPDATE `owners` SET `own_diff_trans_name` = True WHERE LEFT( `own_display_name` , 10 ) = "Transfer: ";

UPDATE `owners` SET `own_display_name` = REPLACE(`own_display_name`, "Transfer: ", "");

Checked the results:

SELECT * FROM `owners` WHERE `own_diff_trans_name` = True;

One task from the meeting the other day was to create a new view of transactions which adds a number of financial fields. I've now done that, using the following SQL:

DROP VIEW IF EXISTS VW_trans_composite_eth_prop_2;

CREATE VIEW VW_trans_composite_eth_prop_2 AS

(
SELECT 

seller_titles.ttl_title_id AS seller_title_id, 
seller_titles.ttl_date AS seller_title_date, 
seller_titles.ttl_title_code AS seller_title_code,
seller_titles.ttl_consideration AS seller_title_consideration,
seller_titles.ttl_declaredvalue AS seller_title_declaredvalue,
seller_titles.ttl_marketvalue AS seller_title_marketvalue,
buyer_titles.ttl_title_id AS buyer_title_id, 
buyer_titles.ttl_date AS buyer_title_date, 
buyer_titles.ttl_title_code AS buyer_title_code,
DATEDIFF(buyer_titles.ttl_date, seller_titles.ttl_date) as seller_duration_days,
buyer_titles.ttl_consideration AS buyer_title_consideration,
buyer_titles.ttl_declaredvalue AS buyer_title_declaredvalue,
buyer_titles.ttl_marketvalue AS buyer_title_marketvalue,
census_tracts.census_tract_code,
props.*,
sellers.concat_owners AS concat_sellers, 
sellers.concat_ethnicities AS seller_ethnicities, 
sellers.total_owners AS total_sellers,
sellers.munged_ethnicity AS seller_munged_eth,
sellers.total_institutional AS institutional_sellers,
buyers.concat_owners AS concat_buyers, buyers.concat_ethnicities AS buyer_ethnicities, buyers.total_owners AS total_buyers,
buyers.munged_ethnicity AS buyer_munged_eth,
buyers.total_institutional AS institutional_buyers

FROM titles AS buyer_titles
LEFT JOIN titles_to_prectitles ON buyer_titles.ttl_title_id = titles_to_prectitles.ttp_title_id_fk
LEFT JOIN titles AS seller_titles ON seller_titles.ttl_title_id = titles_to_prectitles.ttp_prectitle_id_fk
LEFT JOIN VW_titles_composite_eth AS sellers ON seller_titles.ttl_title_id = sellers.ttl_title_id
LEFT JOIN VW_titles_composite_eth AS buyers ON buyer_titles.ttl_title_id = buyers.ttl_title_id

LEFT JOIN props AS props ON seller_titles.ttl_property_id_fk = props.prp_property_id
LEFT JOIN census_tracts ON props.prp_census_tract_id_fk = census_tracts.census_tract_id

ORDER BY seller_titles.ttl_title_id
)

Met with JS-R and had long discussions about the next phase of the project, and immediate needs. Arising out of this, two short-term tasks for me:

1. Find a way to generate candidates for identity between different owner records. Use USM to generate data from concatenation of all text fields in the owners table. Start by sorting the table by surname, and test each record against the following five records.
2. Duplicate VW_trans_composite_eth_prop, but add titles.ttl_consideration, ttl_declaredvalue and ttl_marketvalue, and include only instances where ttle_consideration != NULL. Also, add a field that is TRUE where there's the same individual is one of the buyers and one of the sellers. Next, add a field which is TRUE where one of the buyers and one of the sellers share a surname, and neither of the two is own_institutional (family_transaction).

Tested on the dev db, then rolled out the changes to the live DB. All seems well. Created a couple of tasks in the Adaptive DB project that need to be done: there's data in the version-controlled core files which should be in a project-specific config file.

Following our meeting last week, I've now drafted a preliminary shot at a project proposal for phase two, and forwarded it to JS-R for additional data, edits and gap-filling.

There are 117. Here's how I found them, for future reference:

SELECT a.`ttl_title_id`, b.`ttl_title_id`, a.`ttl_title_code`
FROM `titles` a, `titles` b
WHERE a.`ttl_title_code` = b.`ttl_title_code`
AND a.`ttl_title_id` <> b.`ttl_title_id`
AND a.`ttl_title_id` < b.`ttl_title_id`
ORDER BY a.`ttl_title_id`
LIMIT 0, 1000

Negative duration values, in the case of 11 records in the VW_trans_composite_eth_prop view, have revealed some data entry errors in the "preceding titles" and "related titles (newer)" fields. This suggests that there may be other such errors which are not apparent, because their dates happen to be in the correct sequence. Therefore we propose creating an automated check on the data, to locate such inconsistencies. This is an outline of how the check would be done:

• For each distinct property:
  • List all the titles for that property in date order. For each title:
    • Check that its preceding-title links (there can be more than one) point to titles which precede it in date order.
    • Check that its related-title-newer links (again, multiple) point to titles which follow it in date order.

However, I see a problem with this. If a title is created as a result of a merger of two or more preceding titles, each of which has a different date, it will be impossible to tell whether there is an error or not. Ditto in the case of subsequent titles, in the case of a subdivision. Waiting for word from JS-R on how to handle this.

I have now carried out the insertion, and constructed a detailed audit trail to pass on to JS-R and VG. Here's the opening section of the audit document (the rest is very long, and consists of precise records of every operation):

____AUDIT TRAIL FOR THE AUTOMATED INSERTION OF SPREADSHEET DATA____

Use this document to check the validity of inserted records and trace the source of any errors that may be found.


WHAT HAS BEEN DONE:

 - A new title record has been inserted for each record in the spreadsheet.

 - The title has been linked to its property. All properties referred to in the spreadsheet appear to have already existed in the database. so no new property records were created.

 - All owners recorded in the spreadsheet have either been:
      - discovered as already existing in the database, or 
      - inserted as new records into the database
   and each owner has been linked to their title(s).

 - A new mortgage record has been inserted for each mortgage in the spreadsheet.

 - All lenders recorded in the spreadsheet have either been:
      - discovered as already existing in the database, or 
      - inserted as new records into the database
   and each lender has been linked to their mortgage(s).

 - A simple algorithm has been used to determine whether owners and lenders are individual or institutional, and their records have been configured accordingly. I have strong confidence in this algorithm, but there may be occasional errors.

 - All "other documents" recorded in the spreadsheet have either been:
      - discovered as already existing in the database, or 
      - inserted as new records into the database
   and each document has been linked to its title(s).


WHAT HAS NOT BEEN DONE:

 - No attempt has been made to set values for the ethnicity of lenders or owners. This cannot be done in an automated fashion.

 - No attempt has been made to discover seller information for the new records. This is also best done by humans, but it's possible we might be able to automate it if time permits.



BACKUPS

I have backups of the database taken immediately before and immediately after the process.


AUTO_INCREMENT VALUES PRIOR TO INSERTION OF NEW RECORDS FROM SPREADSHEET

These values represent the FIRST id and LAST id of a record inserted as part of this process. Within this range, data in the table has been inserted by the automated process.

    TABLE               FIRST ID          LAST ID

titles                    2300             2738
owners                    1863             2292
lenders                    553              691
mortgages                  929             1306
other_docs                 388              518
owners_to_titles          9678            10363
mortgages_to_titles       3246             3623
docs_to_titles            1086             1328
lenders_to_mortgages      1130             1564

No new records were inserted in the props table; presumably all the new titles relate to properties already in the database.


FULL INSERTION RECORD

This is the complete record of record creation through the process. Use this to figure out exactly what happened in the case of any given record.

Operation # 1

Processing record with id # d1e1150
Found 1 property records.
Using property id #109
Inserted title record # 2300
Processing owners
Inserted owner id # 1863
Inserted owner-to-title link id # 9678
Inserted mortgage id # 929
Inserted mortgage-to-title link id # 3246
Processing lenders for this mortgage.
Inserted lender id # 553
Inserted lender_to_mortgage link id # 1130
Processing other documents.
Inserted other_doc id # 388
Inserted doc-to-title link id # 1086
Finished processing record d1e1150.

[...]

I've now finished and tested the code for importing records into the db. The end result (which creates 438 primary title inserts, but thousands of ancillary records) was too large to run in a single operation, so I'm using <xsl:result-document> to split it into five sub-operations. Each one produces a detailed report on what it's done, so that, combined with the source XML file, should be all we need in terms of an audit trail.

It took a little longer than expected because of two additional aspects I hadn't planned for initially. Both the lenders and the owners tables have mechanisms for distinguishing between individuals and institutions, and I ended up adding some code to determine whether new records being added fell into one or the other category, by examining the name of the new owner or lender. In addition, I decided to parse out the forenames and surnames of individuals.

I would post the script here, but it's very long, very messy, based on invented XML in the source document, and it's a one-shot task that we won't have to do again, so it's not of any general use. However, the techniques I've figured out for this probably will be used again.

I haven't yet run it against the live database, only against the dev db; I don't want to make major changes right at the end of the day, so I'm going to do it tomorrow morning, after taking a fresh backup, and recording the upper bounds of the current db tables, so that I can report to J-SR and VG, and they can look at the results.

Working with a single record, I've got the following stages done:

• Property identification or creation.
• Title creation.
• Owner identification or creation.
• Owner linking to title.
• Mortgage creation.
• Mortgage linking to title.

I'm now working on lenders. Once I have one record working, I'll start trying blocks of 5, 10, 20, 50, etc. till the whole thing works.

I've decided to go the PHP route for the moment. These two blocks of code would be run before and after running the PHP process, and I'm putting them here so I can get them when I need them. They only need to be run once, and I'll certainly be hacking away with my scripts for many hours in between.

    # Add the extra columns we need.
ALTER TABLE `props` ADD `temp_tracking` VARCHAR(1024);
ALTER TABLE `titles` ADD `temp_tracking` VARCHAR(1024);
ALTER TABLE `owners` ADD `temp_tracking` VARCHAR(1024);
ALTER TABLE `lenders` ADD `temp_tracking` VARCHAR(1024);
ALTER TABLE `mortgages` ADD `temp_tracking` VARCHAR(1024);
ALTER TABLE `other_docs` ADD `temp_tracking` VARCHAR(1024);
    
    # Delete the extra columns we added.
    
ALTER TABLE `props` DROP COLUMN `temp_tracking`;
ALTER TABLE `titles` DROP COLUMN `temp_tracking`;
ALTER TABLE `owners` DROP COLUMN `temp_tracking`;
ALTER TABLE `lenders` DROP COLUMN `temp_tracking`;
ALTER TABLE `mortgages` DROP COLUMN `temp_tracking`;
ALTER TABLE `other_docs` DROP COLUMN `temp_tracking`;

I've written the basic code to generate the SQL to add the core title table records, built in the PHP framework for running queries, and tested with a single record to demonstrate that it's practical. Now I can start building the more complicated queries.

The insertion code I'm writing needs to use stored procedures in order to do slightly complex things such as IF THEN ELSE structures. However, I've discovered that it's impossible to call a stored procedure which has more than one statement in it from inside phpMyAdmin because the CLIENT_MULTI_STATEMENTS flag is not set by phpMyAdmin in its connection string. This is not configurable in phpMyAdmin; you'd need to change the actual source to make this possible.

I confirmed this by running a test at the mysql command line on mysqldev.hcmc.uvic.ca (like this: mysql -u hcmc -p properties_dev). From the CLI, I could successfully define a procedure and call it, but from inside phpMyAdmin I could only define the procedure; attempts to call it result in Error 1312 (0A000): PROCEDURE proc_name can't return a result set in the given context. The same applies to SQL scripts uploaded into phpMyAdmin via the Import function.

So it seems to me that one useful working method would be one whereby I have phpMyAdmin open (for easy checking of the results of my script), and I copy my script from the local machine (where it's generated by XSLT which runs against the XML data from the spreadsheet) over to mysqldev; then I run the script from the command line, and check the results. Not ideal at all. But possible.

Another approach would be to generate not SQL but PHP, with embedded SQL, and run the PHP script; the PHP connection method enables you to set the requisite flag when making the connection. That might be easier, but again I'd need to save it and copy it over to home1t in order to be able to run it; and the extra embedding, given the scale of the operation I want to do, might be a problem. So the first option is probably the best.

It's taken me half the day to figure this out. Curses.

Now that I have the data in a usable XML format (an example record is below), I need to plan the process by which SQL statements are generated and sequenced in order to add it to the database. There are a number of key issues that need to be carefully handled:

• Insertions will be made into the following tables:
  • Properties
  • Titles (the core table)
  • Owners
  • Lenders
  • Mortgages
  • Other documents
• The insertions will have to be made in such a way that links can be established between related records. For this reason, each record has a unique id, and each mortgage does too (because mortgages and lenders will have to be linked).
• Because there are lots of one-to-many relationships, and also because many of the items such as owners and lenders being added will be duplicates of existing ones, we need to stage the insertions carefully.

This is the proposed sequence:

• First, add a temporary "tracking" field to each of the tables we're using. This will be used to store a list of ids to which the record is linked, so we can create links later on.
• For each record:
  • Handle its property information:
    • Check for an existing record with the same property information as this one. If it exists, add the record id to its tracking field (followed by a space). If it doesn't, create it, and add the id.
  • Create its title record (we assume that these titles don't yet exist in the database). Place the record id in its tracking field.
  • Create the owners. For each owner:
    • Check for an existing owner with the same display name, street number and street name. If it exists, add the record id to its tracking field. If it doesn't exist, create it, and place the record id in its tracking field.
  • Create the mortgages. For each mortgage:
    • Create a new mortgage record (we assume that mortgages are unique to titles, and therefore unique in the db). Store the mortgage id in its tracking field.
    • Create the lenders. For each lender:
      • Check for an existing lender with the same display name, street number and street name. If it exists, add the mortgage id to its tracking field. If it doesn't exist, create it, and place the mortgage id in its tracking field.
  • Handle the "other documents". For each document:
    • Check for an existing record with the same document number. If it exists, add the record id to its tracking field; if not, create it, and place the record id in its tracking field.
• Create the links. For each title with an id in its tracking field (meaning we've just added it), get its ttl_title_id, and:
  • Get the first record in the property table which has the record's tracking id in its tracking field (there should only be one), and set the title record's ttl_property_id_fk field to its prp_property_id.
  • For each owner which has the same id in its tracking field, get its own_owner_id, and create a new record in the owners_to_titles table.
  • For each mortgage which has the record id as the middle part of its tracking id:
    • Get its mgg_mortgage_id.
    • Create a new record in the mortgages_to_titles table.
    • For each lender which has the mortgage's tracking id in its tracking field:
      • Get its lnd_lender_id.
      • Create a record in the lenders_to_mortgages table.
  • For each record in the other_docs table which has the record's id in its tracking field, get its odc_doc_id and create a record in the docs_to_titles table.
• Empty all the tracking fields. We may as well keep them, in case they're useful in future.

<record gen-id="d1e30029" orig-id="5701075">
      <property>
         <district>196</district>
         <plan>196</plan>
         <block>70</block>
         <lot>2</lot>
         <census-tract>57.01</census-tract>
      </property>
      <title>
         <title-code>J68726L</title-code>
         <traces/>
         <t1-doc-lots>1, 2 and 3</t1-doc-lots>
         <traces>H9471L,H9472L, na, na, na, na, na, na, na, na, na, na</traces>
         <date>1981-09-02</date>
         <ownership-split>Shek Holdings Co. Ltd. (Incorporation no. 241563): 7/10
A. Youngson Investments Ltd (Incorporation no. 240974): 3/10</ownership-split>
         <notes/>
      </title>
      <owners>
         <owner>
            <name>Shek Holdings Co. Ltd. (Incorporation no. 241563)</name>
            <occupation/>
            <street-num>539</street-num>
            <street>Main St</street>
            <city/>
            <province/>
            <post-code/>
            <undivided-interest>7/10</undivided-interest>
         </owner>
         <owner>
            <name>A. Youngson Investments Ltd (Incorporation no. 240974)</name>
            <occupation/>
            <street-num>940</street-num>
            <street>West King Edward Ave</street>
            <city>n</city>
            <province/>
            <post-code/>
            <undivided-interest>3/10</undivided-interest>
         </owner>
      </owners>
      <mortgages>
         <mortgage gen-id="mort_d1e30029_d1e30236">
            <doc-num>M H6566</doc-num>
            <year/>
            <cancelled>n</cancelled>
            <cancelled-date/>
            <value/>
            <modified/>
            <modified-date/>
            <modified-by/>
            <term-date/>
            <interest/>
            <remarks>Lots 1 and 2</remarks>
            <lenders>
               <lender>The Imperial Life Assurance Company of Canada</lender>
            </lenders>
         </mortgage>
         <mortgage gen-id="mort_d1e30029_d1e30263">
            <doc-num>M H7796</doc-num>
            <year>1980</year>
            <cancelled>y</cancelled>
            <cancelled-date>1981</cancelled-date>
            <value/>
            <modified/>
            <modified-date/>
            <modified-by/>
            <term-date/>
            <interest/>
            <remarks>Lots 1 and 2</remarks>
            <lenders>
               <lender>Canadian Imperial Bank of Commerce</lender>
            </lenders>
         </mortgage>
         <mortgage gen-id="mort_d1e30029_d1e30295">
            <doc-num>M H45708</doc-num>
            <year>1980</year>
            <cancelled>y</cancelled>
            <cancelled-date>1981</cancelled-date>
            <value/>
            <modified/>
            <modified-date/>
            <modified-by/>
            <term-date/>
            <interest/>
            <remarks>Lots 1 and 2</remarks>
            <lenders>
               <lender>Canadian Imperial Bank of Commerce</lender>
            </lenders>
         </mortgage>
      </mortgages>
      <other-docs>
         <doc>
            <doc-num>AR H6567</doc-num>
            <year>1980</year>
            <parties>The Imperial Life Assurance Company of Canada</parties>
            <remarks>Lots 1 and 2</remarks>
         </doc>
      </other-docs>
   </record>

I've now finished the second stage of this work, with nest_data.xsl complete and working; all mortgages, lenders and associated documents are now organized into a structured XML document which can be used to generate SQL. In the process, I had to manually fix some of the column headers in unwrapped_fods.xml, because they were inconsistent in terms of spelling and case; that means the original unwrap_fods.xsl can't be run again to re-run the whole process without doing the same manual intervention. However, I don't think that will be necessary, since the data in unwrapped_fods.xml is complete. So now we have unwrapped_fods_nested.xml, and I can start generating SQL from that.

I've spent most of today figuring out an approach for importing the old spreadsheet data discussed in previous posts. This is what I've got to so far:

• I opened the file in LibreOffice, deleted all but the first sheet (only the first sheet has corrected data), and saved it as a FODS file.
• I wrote an transformation called unwrap_fods.xsl to eliminate all elements and attributes we don't need, and to unravel colspans so that we have the same number of cells in each row. At the same time, I eliminated all instances of "na" from cells where it was the only content; this is (I assume) the same as empty.
• I've written most of a second XSLT stylesheet called nest_data.xsl which is generating a single record from each row, but which uses nesting to manage the owner, mortgage etc. blocks in a logical manner. At the moment, every row has a complete set of cells for up to eight owners, even where there's only a single owner. I'm now processing all this data into a single <owners> element containing only one <owner> element for each owner who actually exists. (The XML format is invented just for this purpose, because it's purely transitional.) I now have to do the same for the 22 mortgage blocks.

Once I have this XML, the plan is to create four XSL stylesheets which produce SQL inserts for the property, mortgage, owner and title tables. Each of these tables will have a new field added to it which can be used to store a unique value for each source record (row in the original table); then these fields can be used to create records in the linking tables. Finally, a process will have to be worked out which can generate lists of potential duplicate records, which can then be checked by a human, and merged if appropriate. This problem is not directly related to the current one, but the issue of duplicates will be much worsened by the automated adding of data through this process.

The following remaining tasks from yesterday are now completed:

• DONE: The ethnicities table values have been abused to store information about institutions; we need to separate out that data. Copy data from the owners_to_ethnicities table to the owners table as follows:
  • DONE: If a record exists for a given owner with the ethnicity value 11 (ethnic institution), and there's also one with the value 10 (private institution), delete the latter, since the former supercedes it (for now).
  • DONE: For each record where the ethnicity value is 11, set the corresponding owner field to 2.
  • DONE: For each record where the ethnicity value is 10, set the corresponding owner field to 3.
  • DONE: For each record where the ethnicity value is 9, set the corresponding owner field to 4.
  • DONE: Delete all records in the owners_to_ethnicities table where the ethnicities values are 9, 10 or 11.
• DONE: Integrate recent changes in the trunk of the AdaptiveDB codebase into Properties. This may give a small increase in performance, and a couple of useful improvements.

This remains to be done:

• Make changes to the AdaptiveDB code for this and other projects to split the tabs into separate files. That will improve page loading dramatically.

This is the SQL for the db side of the changes:

/**
**  SECOND PHASE: June 30 changes. 
*/


/* Set the institution type field to 1 (the default) for all records. */
UPDATE `owners` SET `own_insttype_id_fk` = "1";

/* 
** I wanted to delete records in the owners_to_ethnicities table where there's both an 11
** and a 10; in this case, we don't need the 10 because the 11 supercedes it.
*/

/* 
** First check what we have. There were 97 records before deletion.
*/

SELECT * FROM `owners_to_ethnicities` AS ote1 WHERE ote1.ote_ethnicity_id_fk = "10" AND EXISTS (SELECT * FROM `owners_to_ethnicities` AS ote2 WHERE ote1.ote_owner_id_fk = ote2.ote_owner_id_fk AND ote2.ote_ethnicity_id_fk = "11");

/* 
** We can't actually delete records because you can't query the same table in a subquery as you're deleting from in the main clause; this is a bit of a showstopper. So we'll have to take a different approach. We first set the value for the ethnicity 10 (Private institution), then override it where there's an 11. Finally we set the value for 9.
*/

SELECT * FROM `owners` WHERE EXISTS (SELECT * FROM `owners_to_ethnicities` AS ote1 WHERE ote1.ote_ethnicity_id_fk = "10" AND owners.own_owner_id = ote1.ote_owner_id_fk);

/* 266 results. */

UPDATE `owners` SET `own_insttype_id_fk` = "3" WHERE EXISTS (SELECT * FROM `owners_to_ethnicities` AS ote1 WHERE ote1.ote_ethnicity_id_fk = "10" AND owners.own_owner_id = ote1.ote_owner_id_fk);

SELECT * FROM `owners` WHERE EXISTS (SELECT * FROM `owners_to_ethnicities` AS ote1 WHERE ote1.ote_ethnicity_id_fk = "11" AND owners.own_owner_id = ote1.ote_owner_id_fk);

/* 97 results */

UPDATE `owners` SET `own_insttype_id_fk` = "2" WHERE EXISTS (SELECT * FROM `owners_to_ethnicities` AS ote1 WHERE ote1.ote_ethnicity_id_fk = "11" AND owners.own_owner_id = ote1.ote_owner_id_fk);

SELECT * FROM `owners` WHERE EXISTS (SELECT * FROM `owners_to_ethnicities` AS ote1 WHERE ote1.ote_ethnicity_id_fk = "9" AND owners.own_owner_id = ote1.ote_owner_id_fk);

/* 10 results */

UPDATE `owners` SET `own_insttype_id_fk` = "4" WHERE EXISTS (SELECT * FROM `owners_to_ethnicities` AS ote1 WHERE ote1.ote_ethnicity_id_fk = "9" AND owners.own_owner_id = ote1.ote_owner_id_fk);

/* 
** Now we can delete the three values from the ethnicities table, which will result in the deletion of the linked records.
*/

SELECT * FROM ethnicities where eth_ethnicity_id in (9,10,11); 

/* 3 results */

SELECT * FROM owners_to_ethnicities where ote_ethnicity_id_fk in (9,10,11);

/* 373 results: 266 + 97 + 10 */

DELETE FROM ethnicities where eth_ethnicity_id in (9,10,11); 

/* Check that linking records were deleted. */

SELECT * FROM owners_to_ethnicities where ote_ethnicity_id_fk in (9,10,11);

/* 0 results */

From the task list in the previous post, this is what I've accomplished this afternoon (tested in dev, and migrated to live):

• DONE: Rename the Titles ttl_price field to ttl_consideration.
• DONE: Modify the local_classes.php file to reflect this change, and move the field to just before the mortgages.
• DONE: Add two more integer fields right after it: ttl_declaredvalue and ttl_marketvalue.
• DONE: Add these two fields to the local_classes.php file.
• DONE: Add a new date field, ttl_transfer_date, to the Titles table, right after the existing date field.
• DONE: Add this field to the local_classes.php file.
• DONE: Add a text field for Head Office to the Owners table.
• DONE: Add this field to the local_classes.php file.
• DONE: Create a sellers_to_titles table, linked to the owners and the titles.
• DONE: Create a lawyers_to_titles table, linked ditto.
• DONE: Create one-to-many fields in the local_classes.php file for each of the above.
• DONE: Create a new table called institution_type, with id, name and description.
• DONE: Populate the table with 1. "" (the default), 2. Ethnic institution, 3. Private institution, 4. Public institution.
• DONE: Add a lookup field to the owners table which points at the new table.
• DONE: Update scripts such as copy_live_to_dev_db.sql to reflect changes in structure.

The following remain to be completed:

• The ethnicities table values have been abused to store information about institutions; we need to separate out that data. Copy data from the owners_to_ethnicities table to the owners table as follows:
  • If a record exists for a given owner with the ethnicity value 11 (ethnic institution), and there's also one with the value 10 (private institution), delete the latter, since the former supercedes it (for now).
  • For each record where the ethnicity value is 11, set the corresponding owner field to 2.
  • For each record where the ethnicity value is 10, set the corresponding owner field to 3.
  • For each record where the ethnicity value is 9, set the corresponding owner field to 4.
  • Delete all records in the owners_to_ethnicities table where the ethnicities values are 9, 10 or 11.
• NEW: Integrate recent changes in the trunk of the AdaptiveDB codebase into Properties. This may give a small increase in performance, and a couple of useful improvements.
• NEW: Make changes to the AdaptiveDB code for this and other projects to split the tabs into separate files. That will improve page loading dramatically.

This is the SQL for the db side of the changes:

/*
** This is a list of changes in db structure and massages to data
** which were done starting June 29 2011. See the blog posting of
** the same date for more details and explanation.
*/


ALTER TABLE titles CHANGE COLUMN ttl_price ttl_consideration int(11) default NULL;

ALTER TABLE titles ADD ttl_declaredvalue int(11) default NULL;
ALTER TABLE titles ADD ttl_marketvalue int(11) default NULL;

ALTER TABLE titles ADD ttl_transferdate date not NULL AFTER ttl_date;

ALTER TABLE owners ADD own_head_office varchar(128);


CREATE TABLE IF NOT EXISTS `sellers_to_titles` (
  `stt_stt_id` int(11) NOT NULL auto_increment,
  `stt_owner_id_fk` int(11) NOT NULL,
  `stt_title_id_fk` int(11) NOT NULL,
  PRIMARY KEY  (`stt_stt_id`),
  KEY `stt_owner_id_fk` (`stt_owner_id_fk`),
  KEY `stt_title_id_fk` (`stt_title_id_fk`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


ALTER TABLE `sellers_to_titles`
  ADD CONSTRAINT `sellers_to_titles_ibfk_1` FOREIGN KEY (`stt_owner_id_fk`) REFERENCES `owners` (`own_owner_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `sellers_to_titles_ibfk_2` FOREIGN KEY (`stt_title_id_fk`) REFERENCES `titles` (`ttl_title_id`) ON DELETE CASCADE ON UPDATE CASCADE;
  
  
  
CREATE TABLE IF NOT EXISTS `lawyers_to_titles` (
  `ltt_ltt_id` int(11) NOT NULL auto_increment,
  `ltt_owner_id_fk` int(11) NOT NULL,
  `ltt_title_id_fk` int(11) NOT NULL,
  PRIMARY KEY  (`ltt_ltt_id`),
  KEY `ltt_owner_id_fk` (`ltt_owner_id_fk`),
  KEY `ltt_title_id_fk` (`ltt_title_id_fk`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


ALTER TABLE `lawyers_to_titles`
  ADD CONSTRAINT `lawyers_to_titles_ibfk_1` FOREIGN KEY (`ltt_owner_id_fk`) REFERENCES `owners` (`own_owner_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `lawyers_to_titles_ibfk_2` FOREIGN KEY (`ltt_title_id_fk`) REFERENCES `titles` (`ttl_title_id`) ON DELETE CASCADE ON UPDATE CASCADE;


CREATE TABLE IF NOT EXISTS `institution_types` (
  `ins_ins_id` int(11) NOT NULL auto_increment,
  `ins_name` varchar(45) collate utf8_unicode_ci NOT NULL,
  `ins_desc` varchar(256) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`ins_ins_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `institution_types` (ins_ins_id, ins_name, ins_desc) VALUES (1, "", "Not an institution."), (2, "Ethnic institution", "Insert description of what this means."), (3, "Private institution", "Insert description of what this means."), (4, "Public institution", "Insert description of what this means.") ;


ALTER TABLE `owners` ADD `own_insttype_id_fk` int(11) default 1;
ALTER TABLE `owners`
  ADD CONSTRAINT `owners_ibfk_1` FOREIGN KEY (`own_insttype_id_fk`) REFERENCES `institution_types` (`ins_ins_id`) ON DELETE CASCADE ON UPDATE CASCADE;

Met with JS-R and VG to discuss the next phase of data entry. These are the changes we need to make:

• Rename the Titles ttl_price field to ttl_consideration.
• Modify the local_classes.php file to reflect this change, and move the field to just before the mortgages.
• Add two more integer fields right after it: ttl_declaredvalue and ttl_marketvalue.
• Add these two fields to the local_classes.php file.
• Add a new date field, ttl_transfer_date, to the Titles table, right after the existing date field.
• Add this field to the local_classes.php file.
• Add a text field for Head Office to the Owners table.
• Add this field to the local_classes.php file.
• Create a sellers_to_titles table, linked to the owners and the titles.
• Create a lawyers_to_titles table, linked ditto.
• Create one-to-many fields in the local_classes.php file for each of the above.
• Create a new table called institution_type, with id, name and description.
• Populate the table with 1. n/a (the default), 2. Ethnic institution, 3. Private institution, 4. Public institution.
• Add a lookup field to the owners table which points at the new table.
• The ethnicities table values have been abused to store information about institutions; we need to separate out that data. Copy data from the owners_to_ethnicities table to the owners table as follows:
  • If a record exists for a given owner with the ethnicity value 11 (ethnic institution), and there's also one with the value 10 (private institution), delete the latter, since the former supercedes it (for now).
  • For each record where the ethnicity value is 11, set the corresponding owner field to 2.
  • For each record where the ethnicity value is 10, set the corresponding owner field to 3.
  • For each record where the ethnicity value is 9, set the corresponding owner field to 4.
  • Delete all records in the owners_to_ethnicities table where the ethnicities values are 9, 10 or 11.
• Update scripts such as copy_live_to_dev_db.sql to reflect changes in structure.

Coming down the pipe, there's also a requirement to parse a complex spreadsheet containing many hundreds of title records into the db somehow. I'm thinking this:

• CSV to XML via a save from LibreOffice.
• XML to more rational XML using XSLT; this would remove empty cells, expand spans, and generally clean up the structure.
• XML to SQL using XSLT. This would generate a series of inserts for each row. The main problem is detecting whether (for instance) owners already exist in the db, and linking owners with titles. It would be necessary to insert the title first, perhaps using a special temporary field in which an XSLT-generated unique id could be inserted; then when the owner records are being constructed, we can first check for the existence of that owner, then insert if necessary; and then we can use the unique id for the current row to construct the links between titles and owners. Ditto with other relational data.

Currently we're using ethnicity to store info which is not really suited to it (Private_institution and Public_institution). This info belongs in the institution field, but that's currently a boolean, so it needs to be turned into a lookup to another table. Then the data from the ethnicities field needs to be migrated to it, and deleted from the ethnicities tables, simplifying ethnicity and munging.

Right now we have a system which munges ethnicities based on this algorithm:

(SELECT CASE
WHEN group_concat(ethnicities.eth_name separator ', ') LIKE "%Japanese%" AND group_concat(ethnicities.eth_name separator ', ') LIKE "%Chinese%" THEN "c"
WHEN group_concat(ethnicities.eth_name separator ', ') LIKE "%Japanese%" AND (SELECT COUNT(DISTINCT(ethnicities.eth_name))) > 1 THEN "b"
WHEN group_concat(ethnicities.eth_name separator ', ') LIKE "%Chinese%" AND (SELECT COUNT(DISTINCT(ethnicities.eth_name))) > 1 THEN "a"
WHEN (SELECT COUNT(DISTINCT(ethnicities.eth_name))) > 1 THEN "d"
ELSE SUBSTRING_INDEX(group_concat(ethnicities.eth_name separator ', '), ',', 1)
END) AS munged_ethnicity

However, there are now more ethnicities, including Private_institution, which need their own specific handling during the munge process. That algorithm needs to be rewritten, and in the process we should replace the anonymous variables with something more useful.

On JS-R's instructions, simplified the view which creates the abbreviated pseudo-field in the titles table which contains property information:

RENAME TABLE `props_abbr` TO `props_abbr_old`;

CREATE VIEW `props_abbr` AS select `props`.`prp_property_id` AS `prpabbr_property_id`,concat(_utf8' B:',LPAD(`props`.`prp_block`, 3, '0'),_utf8' L:',`props`.`prp_lot`) AS `prpabbr_property_info` from `props`;

This also pads the block data with leading zeroes. We can't do that with the lot info because lot identifiers are not numeric.

It appears that we should not have based the titles_to_prectitles table on the traces data; this did include some links to previous titles, but also some title codes which were simply written on the record and were not necessarily intended to point to previous titles. Therefore this is what I've done:

• Emptied titles_to_titles and restored it from a backup from two days ago (removing 214 entries generated by reference to the titles_to_prectitles data which is now suspect).
• Emptied titles_to_prectitles and regenerated it from titles_to_titles using the SQL from the previous post. They are now exact mirrors of each other, and all the data comes from titles_to_titles, which is known to be good.

Following my previous post, this is the SQL to complete the titles_to_prectitles table based on the content of the titles_to_titles table:

INSERT INTO titles_to_prectitles (ttp_title_id_fk, ttp_prectitle_id_fk) 
SELECT ttt_newer_title_id_fk, ttt_older_title_id_fk FROM titles_to_titles as ttt
WHERE NOT
EXISTS (
SELECT *
FROM titles_to_prectitles AS ttp
WHERE (
ttp.ttp_title_id_fk = ttt.ttt_newer_title_id_fk
AND ttp.ttp_prectitle_id_fk = ttt.ttt_older_title_id_fk
)
)

This results in the insertion of 647 new records into titles_to_prectitles.

The same operation can be done in reverse, to add 214 more records to titles_to_titles:

INSERT INTO titles_to_titles (ttt_newer_title_id_fk, ttt_older_title_id_fk) 
SELECT ttp_title_id_fk, ttp_prectitle_id_fk FROM titles_to_prectitles as ttp
WHERE NOT
EXISTS (
SELECT *
FROM titles_to_titles AS ttt
WHERE (
ttp.ttp_title_id_fk = ttt.ttt_newer_title_id_fk
AND ttp.ttp_prectitle_id_fk = ttt.ttt_older_title_id_fk
)
)

This results in both tables having 2193 records.

It appears that many forward links were created (titles_to_titles) from titles to subsequent titles which were not mirrored by corresponding backward links (titles_to_prectitles). Since we only used titles_to_prectitles in our views, many title progressions are not yet reflected accurately.

The solution is to create titles_to_prectitles links directly from the titles_to_titles table -- they should essentially be mirrors of each other. This should be possible fairly simply using SQL, and changes would immediately be reflected in the views. We should also do the reverse, ensuring that both tables are in sync.

This SQL gives us (if I'm correct in my assumptions) an overview of the situation we're in with regard to records missing from titles_to_prectitles:

SELECT ttt.*,
old_titles.ttl_title_code AS older_title_code,
new_titles.ttl_title_code AS newer_title_code
FROM `titles_to_titles` AS ttt
LEFT JOIN titles AS old_titles on ttt.ttt_older_title_id_fk = old_titles.ttl_title_id
LEFT JOIN titles AS new_titles on ttt.ttt_newer_title_id_fk = new_titles.ttl_title_id
WHERE NOT
EXISTS (
SELECT *
FROM titles_to_prectitles AS ttp
WHERE (
ttp.ttp_title_id_fk = ttt.ttt_newer_title_id_fk
AND ttp.ttp_prectitle_id_fk = ttt.ttt_older_title_id_fk
)
)
LIMIT 0 , 700

This can be extended to create tehe missing records in titles_to_prectitles, and then re-engineered to do the reverse and create any missing records in titles_to_titles.

In breaks between workshop sessions, I tried to address the problem of the length of time queries are taking by using temporary tables constructed directly in the query instead of joining to views. The approach I took (joining to a temporary table created in the join clause) had no effect -- the query took exactly the same time. But there may be a way to create a temporary table once earlier in the query, and then join to it later. Still working on this.

Reworked VW_owners_full so that it includes more records and data (a regular join changed to a left join):

DROP VIEW IF EXISTS owners_full;

CREATE VIEW VW_owners_full AS 
(
SELECT owners.*,
owners_to_ethnicities.ote_ethnicity_id_fk,
ethnicities.eth_name,
owners_to_titles.ott_title_id_fk AS title_id,
titles.ttl_title_id,
titles.ttl_title_code,
titles.ttl_date
FROM owners_to_titles 
LEFT JOIN owners ON owners.own_owner_id = owners_to_titles.ott_owner_id_fk
LEFT JOIN titles ON owners_to_titles.ott_title_id_fk = titles.ttl_title_id
LEFT JOIN owners_to_ethnicities ON owners.own_owner_id = owners_to_ethnicities.ote_owner_id_fk 
LEFT JOIN ethnicities ON owners_to_ethnicities.ote_ethnicity_id_fk = ethnicities.eth_ethnicity_id
order by owners.own_surname
)

DROP VIEW IF EXISTS VW_trans_composite_eth_prop;

CREATE VIEW VW_trans_composite_eth_prop AS

(
SELECT 

seller_titles.ttl_title_id AS seller_title_id, seller_titles.ttl_date AS seller_title_date, seller_titles.ttl_title_code AS seller_title_code,
buyer_titles.ttl_title_id AS buyer_title_id, buyer_titles.ttl_date AS buyer_title_date, buyer_titles.ttl_title_code AS buyer_title_code,
DATEDIFF(buyer_titles.ttl_date, seller_titles.ttl_date) as seller_duration_days,
census_tracts.census_tract_code,
props.*,
sellers.concat_owners AS concat_sellers, sellers.concat_ethnicities AS seller_ethnicities, sellers.total_owners AS total_sellers,
sellers.munged_ethnicity AS seller_munged_eth,
sellers.total_institutional AS institutional_sellers,
buyers.concat_owners AS concat_buyers, buyers.concat_ethnicities AS buyer_ethnicities, buyers.total_owners AS total_buyers,
buyers.munged_ethnicity AS buyer_munged_eth,
buyers.total_institutional AS institutional_buyers

FROM titles AS buyer_titles
LEFT JOIN titles_to_prectitles ON buyer_titles.ttl_title_id = titles_to_prectitles.ttp_title_id_fk
LEFT JOIN titles AS seller_titles ON seller_titles.ttl_title_id = titles_to_prectitles.ttp_prectitle_id_fk
LEFT JOIN VW_titles_composite_eth AS sellers ON seller_titles.ttl_title_id = sellers.ttl_title_id
LEFT JOIN VW_titles_composite_eth AS buyers ON buyer_titles.ttl_title_id = buyers.ttl_title_id

LEFT JOIN props AS props ON seller_titles.ttl_property_id_fk = props.prp_property_id
LEFT JOIN census_tracts ON props.prp_census_tract_id_fk = census_tracts.census_tract_id

ORDER BY seller_titles.ttl_title_id
)

Revised the VW_titles_composite_eth view so that it includes even owners who have no ethnicity (are institutional):

DROP VIEW IF EXISTS VW_titles_composite_eth;

CREATE VIEW VW_titles_composite_eth AS
(
SELECT 
titles.ttl_title_id,
titles.ttl_title_code,
titles.ttl_date,
CAST(group_concat(owners.own_owner_id separator ', ') AS CHAR) AS concat_owners,
SUM(owners.own_institutional) AS total_institutional,
CAST(group_concat(owners_to_ethnicities.ote_ethnicity_id_fk separator ', ') AS CHAR) AS concat_eth_ids,
group_concat(ethnicities.eth_name separator ', ') AS concat_ethnicities,
(SELECT IF (COUNT(owners.own_owner_id) > 1, 'multiple', 'single')) AS multi_owner,
(COUNT(owners.own_owner_id)) AS total_owners,
(SELECT CASE
WHEN group_concat(ethnicities.eth_name separator ', ') LIKE "%Japanese%" AND group_concat(ethnicities.eth_name separator ', ') LIKE "%Chinese%" THEN "c"
WHEN group_concat(ethnicities.eth_name separator ', ') LIKE "%Japanese%" AND (SELECT COUNT(DISTINCT(ethnicities.eth_name))) > 1 THEN "b"
WHEN group_concat(ethnicities.eth_name separator ', ') LIKE "%Chinese%" AND (SELECT COUNT(DISTINCT(ethnicities.eth_name))) > 1 THEN "a"
WHEN (SELECT COUNT(DISTINCT(ethnicities.eth_name))) > 1 THEN "d"
ELSE SUBSTRING_INDEX(group_concat(ethnicities.eth_name separator ', '), ',', 1)
END) AS munged_ethnicity
FROM titles
LEFT JOIN owners_to_titles ON owners_to_titles.ott_title_id_fk = titles.ttl_title_id
LEFT JOIN owners ON owners_to_titles.ott_owner_id_fk = owners.own_owner_id
LEFT JOIN owners_to_ethnicities ON owners.own_owner_id = owners_to_ethnicities.ote_owner_id_fk
LEFT JOIN ethnicities ON owners_to_ethnicities.ote_ethnicity_id_fk = ethnicities.eth_ethnicity_id
GROUP BY titles.ttl_title_id
)

The latest requirement is a field containing a comma-separated list of the owner names where the owners are institutional.

JS-R needed to add the full property information to the transactions view, so I've added a new view:

DROP VIEW IF EXISTS VW_trans_composite_eth_prop;

CREATE VIEW VW_trans_composite_eth_prop AS

(
SELECT 

seller_titles.ttl_title_id AS seller_title_id, seller_titles.ttl_date AS seller_title_date, seller_titles.ttl_title_code AS seller_title_code,
buyer_titles.ttl_title_id AS buyer_title_id, buyer_titles.ttl_date AS buyer_title_date, buyer_titles.ttl_title_code AS buyer_title_code,
DATEDIFF(buyer_titles.ttl_date, seller_titles.ttl_date) as seller_duration_days,
census_tracts.census_tract_code,
props.*,
sellers.concat_owners AS concat_sellers, sellers.concat_ethnicities AS seller_ethnicities, sellers.total_owners AS total_sellers,
sellers.munged_ethnicity AS seller_munged_eth,
buyers.concat_owners AS concat_buyers, buyers.concat_ethnicities AS buyer_ethnicities, buyers.total_owners AS total_buyers,
buyers.munged_ethnicity AS buyer_munged_eth

FROM titles AS buyer_titles
LEFT JOIN titles_to_prectitles ON buyer_titles.ttl_title_id = titles_to_prectitles.ttp_title_id_fk
LEFT JOIN titles AS seller_titles ON seller_titles.ttl_title_id = titles_to_prectitles.ttp_prectitle_id_fk
LEFT JOIN VW_titles_composite_eth AS sellers ON seller_titles.ttl_title_id = sellers.ttl_title_id
LEFT JOIN VW_titles_composite_eth AS buyers ON buyer_titles.ttl_title_id = buyers.ttl_title_id

LEFT JOIN props AS props ON seller_titles.ttl_property_id_fk = props.prp_property_id
LEFT JOIN census_tracts ON props.prp_census_tract_id_fk = census_tracts.census_tract_id

ORDER BY seller_titles.ttl_title_id
)

This takes even longer to generate than the original, unfortunately.

Had to use a separate view to aggregate owners against titles. It's working, but it's mighty slow!

DROP VIEW IF EXISTS VW_trans_composite_eth;

CREATE VIEW VW_trans_composite_eth AS

(
SELECT 

seller_titles.ttl_title_id AS seller_title_id, seller_titles.ttl_date AS seller_title_date, seller_titles.ttl_title_code AS seller_title_code,
buyer_titles.ttl_title_id AS buyer_title_id, buyer_titles.ttl_date AS buyer_title_date, buyer_titles.ttl_title_code AS buyer_title_code,
DATEDIFF(buyer_titles.ttl_date, seller_titles.ttl_date) as seller_duration_days,
census_tracts.census_tract_code,
sellers.concat_owners AS concat_sellers, sellers.concat_ethnicities AS seller_ethnicities, sellers.total_owners AS total_sellers,
sellers.munged_ethnicity AS seller_munged_eth,
buyers.concat_owners AS concat_buyers, buyers.concat_ethnicities AS buyer_ethnicities, buyers.total_owners AS total_buyers,
buyers.munged_ethnicity AS buyer_munged_eth

FROM titles AS buyer_titles
LEFT JOIN titles_to_prectitles ON buyer_titles.ttl_title_id = titles_to_prectitles.ttp_title_id_fk
LEFT JOIN titles AS seller_titles ON seller_titles.ttl_title_id = titles_to_prectitles.ttp_prectitle_id_fk
LEFT JOIN VW_titles_composite_eth AS sellers ON seller_titles.ttl_title_id = sellers.ttl_title_id
LEFT JOIN VW_titles_composite_eth AS buyers ON buyer_titles.ttl_title_id = buyers.ttl_title_id

LEFT JOIN props ON seller_titles.ttl_property_id_fk = props.prp_property_id
LEFT JOIN census_tracts ON props.prp_census_tract_id_fk = census_tracts.census_tract_id

ORDER BY seller_titles.ttl_title_id
)

I've completed two of the three tasks from this morning: the VW_sellers_duration view has been modified to rename its fields, and the VW_mortgate_ethnicity view has been created.

DROP VIEW IF EXISTS VW_mortgage_ethnicity;
CREATE VIEW VW_mortgage_ethnicity AS
(SELECT mortgages.*,
titles.ttl_title_id, titles.ttl_title_code, titles.ttl_date,
census_tracts.census_tract_code,
CAST(group_concat(owners.own_owner_id separator ', ') AS CHAR) AS concat_owners,
CAST(group_concat(owners_to_ethnicities.ote_ethnicity_id_fk separator ', ') AS CHAR) AS concat_eth_ids,
group_concat(ethnicities.eth_name separator ', ') AS concat_ethnicities,
(SELECT IF (COUNT(owners.own_owner_id) > 1, 'multiple', 'single')) AS multi_owner,
(COUNT(owners.own_owner_id)) AS total_owners,
(SELECT CASE
WHEN group_concat(ethnicities.eth_name separator ', ') LIKE "%Japanese%" AND group_concat(ethnicities.eth_name separator ', ') LIKE "%Chinese%" THEN "c"
WHEN group_concat(ethnicities.eth_name separator ', ') LIKE "%Japanese%" AND (SELECT COUNT(DISTINCT(ethnicities.eth_name))) > 1 THEN "b"
WHEN group_concat(ethnicities.eth_name separator ', ') LIKE "%Chinese%" AND (SELECT COUNT(DISTINCT(ethnicities.eth_name))) > 1 THEN "a"
WHEN (SELECT COUNT(DISTINCT(ethnicities.eth_name))) > 1 THEN "d"
ELSE SUBSTRING_INDEX(group_concat(ethnicities.eth_name separator ', '), ',', 1)
END) AS munged_ethnicity

FROM mortgages
LEFT JOIN mortgages_to_titles ON mortgages.mgg_mortgage_id = mortgages_to_titles.mtt_mortgage_id_fk
LEFT JOIN titles on mortgages_to_titles.mtt_title_id_fk = titles.ttl_title_id
LEFT JOIN owners_to_titles ON owners_to_titles.ott_title_id_fk = titles.ttl_title_id
LEFT JOIN owners ON owners_to_titles.ott_owner_id_fk = owners.own_owner_id
LEFT JOIN owners_to_ethnicities ON owners.own_owner_id = owners_to_ethnicities.ote_owner_id_fk
LEFT JOIN ethnicities ON owners_to_ethnicities.ote_ethnicity_id_fk = ethnicities.eth_ethnicity_id
LEFT JOIN props ON titles.ttl_property_id_fk = props.prp_property_id
LEFT JOIN census_tracts ON props.prp_census_tract_id_fk = census_tracts.census_tract_id
GROUP BY mortgages.mgg_mortgage_id)

I'm now working on the transaction view, but I've run into an issue variously known as the "Cartesian product" or "Cascading aggregates" problem: in simple terms, when I aggregate both the buyers and the sellers in a single row, I end up with multiple instances. For example, if there are two buyers and three sellers, then each buyer appears three times and each seller twice, for spurious totals of six buyers and six sellers.

What this means is that my strategy for munging buyers OR sellers works only when we're only looking at one of them; when we need to munge both of them in the same row, a different strategy has to be adopted. Right now, I don't know what that strategy might be, but I'm doing some research.

Met with JS-R and worked through requirements for new views of the data:

• Existing view should not be discarded; they're all potentially useful.
• VW_sellers_duration needs some renamed fields: anything beginning with "old_" should be renamed "seller_", "new_" s/b "buyer_", and "duration_days" s/b "seller_duration_days".
• A new view needs to be created like VW_sellers_duration, but rooted on the title instead; it should included munged seller ethnicity and munged buyer ethnicity.
• A new view of mortgages is required, with 1 row per mortgage, including all the mortgage info, and the core title info, followed by the munged owners, and also incorporating property info.

Started experimenting with building a view of the titles table which will include a sort of composite ethnicity column, as detailed in the preceding post. I now have the key owner and ethnicity data munged together in a view created like this:

CREATE VIEW VW_titles_composite_eth AS
(SELECT 
titles.ttl_title_id,
titles.ttl_title_code,
titles.ttl_date,
CAST(group_concat(owners.own_owner_id separator ', ') AS CHAR) AS concat_owners,
CAST(group_concat(owners_to_ethnicities.ote_ethnicity_id_fk separator ', ') AS CHAR) AS concat_eth_ids,
group_concat(ethnicities.eth_name separator ', ') AS concat_ethnicities,
(SELECT IF (COUNT(owners.own_owner_id) > 1, 'multiple', 'single')) AS multi_owner,
(COUNT(owners.own_owner_id)) AS total_owners,
(SELECT CASE
WHEN group_concat(ethnicities.eth_name separator ', ') LIKE "%Japanese%" AND group_concat(ethnicities.eth_name separator ', ') LIKE "%Chinese%" THEN "c"
WHEN group_concat(ethnicities.eth_name separator ', ') LIKE "%Japanese%" AND (SELECT COUNT(DISTINCT(ethnicities.eth_name))) > 1 THEN "b"
WHEN group_concat(ethnicities.eth_name separator ', ') LIKE "%Chinese%" AND (SELECT COUNT(DISTINCT(ethnicities.eth_name))) > 1 THEN "a"
WHEN (SELECT COUNT(DISTINCT(ethnicities.eth_name))) > 1 THEN "d"
ELSE SUBSTRING_INDEX(group_concat(ethnicities.eth_name separator ', '), ',', 1)
END) AS munged_ethnicity
FROM titles
JOIN owners_to_titles ON owners_to_titles.ott_title_id_fk = titles.ttl_title_id
JOIN owners ON owners_to_titles.ott_owner_id_fk = owners.own_owner_id
JOIN owners_to_ethnicities ON owners.own_owner_id = owners_to_ethnicities.ote_owner_id_fk
JOIN ethnicities ON owners_to_ethnicities.ote_ethnicity_id_fk = ethnicities.eth_ethnicity_id
GROUP BY titles.ttl_title_id)

This has the following values, as previously requested:

• a = Mixed ethnicity with Chinese
• b = Mixed Ethnicity with Japanese (include Japanese provisional)
• c = Mixed ethnicity with Chinese and Japanese
• d = Mixed ethnicity--ALL Others (all not included above, including mixes that include the Asia: other category but neither Japanese or Chinese, if they include both Asia other and Japanese or Chinese, categorize as indicated above)
• ELSE--> copy the single ethnicity (or shared ethnicity) here

JS-R has made the following request for a new column in the titles table, which would handle a kind of munged-together value for all the owners' ethnicities. It would be a string field, presumably (unless we want to give these numbers to keep them short).

Maybe we need two new variables as characteristics of titles:

 1. Multiple or single owner:
      a. multiple
      b. single

 2. Ownership ethnicity (don't worry here whether they are individuals or groups):
     a. Mixed ethnicity with Chinese
     b. Mixed Ethnicity with Japanese (include Japanese provisional)
     c. Mixed ethnicity with Chinese and Japanese
     d. Mixed ethnicity--ALL Others (all not included above, including mixes that include the Asia: other category but neither Japanese or Chinese, if they include both Asia other and Japanese or Chinese, categorize as indicated above)
     e. ELSE-->  copy the single ethnicity (or shared ethnicity) here

It turns out that this one should be buyers instead of vendors: Vendor ethnicity by mortgage institutional or not (we will fill in this data as we go) (with analysis possible by date and by sample). So here we go with:

Buyer ethnicity by mortgage institutional or not (we will fill in this data as we go) (with analysis possible by date and by sample)

Here's the SQL:

CREATE VIEW VW_buyers_and_lenders AS

(SELECT 
titles.ttl_title_id, titles.ttl_title_code, titles.ttl_date, 
owners.own_owner_id, owners.own_surname, owners.own_forenames,
buyer_ethnicities.eth_name AS buyer_ethnicity,
mortgages.mgg_mortgage_id, mortgages.mgg_doc_num, mortgages.mgg_value, mortgages.mgg_interest,
lenders.lnd_display_name, lenders.lnd_individual,
lender_ethnicities.eth_name AS lender_ethnicity

FROM titles
JOIN owners_to_titles ON titles.ttl_title_id = owners_to_titles.ott_title_id_fk
LEFT JOIN owners ON owners_to_titles.ott_owner_id_fk = owners.own_owner_id
LEFT JOIN owners_to_ethnicities ON owners.own_owner_id = owners_to_ethnicities.ote_owner_id_fk
LEFT JOIN ethnicities AS buyer_ethnicities ON owners_to_ethnicities.ote_ethnicity_id_fk = buyer_ethnicities.eth_ethnicity_id
JOIN mortgages_to_titles ON titles.ttl_title_id = mortgages_to_titles.mtt_title_id_fk
LEFT JOIN mortgages ON mortgages_to_titles.mtt_mortgage_id_fk = mortgages.mgg_mortgage_id
LEFT JOIN lenders_to_mortgages ON mortgages.mgg_mortgage_id = lenders_to_mortgages.ltm_mortgage_id_fk
LEFT JOIN lenders ON lenders_to_mortgages.ltm_lender_id_fk = lenders.lnd_lender_id
LEFT JOIN lenders_to_ethnicities ON lenders.lnd_lender_id = lenders_to_ethnicities.lte_lender_id_fk
LEFT JOIN ethnicities AS lender_ethnicities ON lenders_to_ethnicities.lte_ethnicity_id_fk = lender_ethnicities.eth_ethnicity_id)

I've included lender ethnicities and lender "individual", even though no data has been entered for those yet.

We now have six views, which I've renamed to make them more obviously views in the phpMyAdmin interface:

• VW_buyers_and_lenders
• VW_buyers_and_sellers
• VW_owners_full
• VW_sellers_duration
• VW_trans_by_date
• VW_trans_by_property

I think these cover all the current needs. VW_owners_full is just a view of all the core data about owners; I was intending to use it as part of other views, but didn't need to. I'll leave it there in case it's useful.

Created a new view for sellers with full details of the transaction, but also including ownership duration:

CREATE VIEW VW_sellers_duration AS

(SELECT 

old_titles.ttl_title_id AS old_title_id, old_titles.ttl_date AS old_title_date, old_titles.ttl_title_code AS old_title_code,
new_titles.ttl_title_id AS new_title_id, new_titles.ttl_date AS new_title_date, new_titles.ttl_title_code AS new_title_code,
DATEDIFF(new_titles.ttl_date, old_titles.ttl_date) as duration_days,
census_tracts.census_tract_code,
sellers.own_owner_id AS seller_id, sellers.own_surname AS seller_surname, sellers.own_forenames AS seller_forenames,
seller_eth.eth_name as seller_ethnicity

FROM titles AS new_titles
JOIN titles_to_prectitles ON new_titles.ttl_title_id = titles_to_prectitles.ttp_title_id_fk
JOIN titles AS old_titles ON old_titles.ttl_title_id = titles_to_prectitles.ttp_prectitle_id_fk
JOIN owners_to_titles AS sellers_to_titles on old_titles.ttl_title_id = sellers_to_titles.ott_title_id_fk
JOIN owners AS sellers ON sellers_to_titles.ott_owner_id_fk = sellers.own_owner_id
LEFT JOIN owners_to_ethnicities AS sellers_to_ethnicities ON sellers.own_owner_id = sellers_to_ethnicities.ote_owner_id_fk
LEFT JOIN ethnicities AS seller_eth ON sellers_to_ethnicities.ote_ethnicity_id_fk = seller_eth.eth_ethnicity_id 
LEFT JOIN props ON old_titles.ttl_property_id_fk = props.prp_property_id
LEFT JOIN census_tracts ON props.prp_census_tract_id_fk = census_tracts.census_tract_id)

This slightly amended version gets all buyers linked to a title even where other info is missing:

SELECT 
buyers.own_owner_id AS buyer_id, buyers.own_surname AS buyer_surname, buyers.own_forenames AS buyer_forenames,
buyer_eth.eth_name AS buyer_ethnicity,
census_tracts.census_tract_code,
titles.ttl_title_id, titles.ttl_title_code, titles.ttl_date, 
titles_to_prectitles.ttp_prectitle_id_fk,
sellers.own_owner_id AS seller_id, sellers.own_display_name AS seller_name,
seller_eth.eth_name AS seller_ethnicity

FROM owners AS buyers
LEFT JOIN owners_to_titles on buyers.own_owner_id = owners_to_titles.ott_owner_id_fk
LEFT JOIN titles on owners_to_titles.ott_title_id_fk = titles.ttl_title_id
LEFT JOIN titles_to_prectitles ON titles.ttl_title_id = titles_to_prectitles.ttp_title_id_fk
LEFT JOIN owners_to_ethnicities ON buyers.own_owner_id = owners_to_ethnicities.ote_owner_id_fk
LEFT JOIN ethnicities AS buyer_eth ON owners_to_ethnicities.ote_ethnicity_id_fk = buyer_eth.eth_ethnicity_id
JOIN owners_to_titles AS sellers_to_titles ON titles_to_prectitles.ttp_prectitle_id_fk = sellers_to_titles.ott_title_id_fk
LEFT JOIN owners AS sellers ON sellers_to_titles.ott_owner_id_fk = sellers.own_owner_id
LEFT JOIN owners_to_ethnicities AS sellers_to_ethnicities ON sellers.own_owner_id = sellers_to_ethnicities.ote_owner_id_fk
LEFT JOIN ethnicities AS seller_eth ON sellers_to_ethnicities.ote_ethnicity_id_fk = seller_eth.eth_ethnicity_id 
LEFT JOIN props ON titles.ttl_property_id_fk = props.prp_property_id
LEFT JOIN census_tracts ON props.prp_census_tract_id_fk = census_tracts.census_tract_id
ORDER BY buyers.own_surname

Here are the latest requests:

• ethnicities of all sellers (with info for date, census tract etc)
• ethnicities of all buyers (with date etc)

I have code elsewhere on the blog which is close to the second of these. The first is more convoluted.

The next view is this one:

Vendor ethnicity by buyer ethnicity (with analysis possible by date and by sample) 

For this we need an individual "transfer" record which includes the following columns:

• title id
• title code
• title date
• preceding title code
• preceding title id
• census tract
• vendor info (name, ethnicity)

I could start by creating a comprehensive view of owners, though; I could then join to this view in the creation of other views, making the process a bit more modular. Here's the sql:

DROP VIEW IF EXISTS owners_detail

CREATE VIEW owners_full AS (SELECT owners.*, owners_to_ethnicities.ote_ethnicity_id_fk, ethnicities.eth_name, owners_to_titles.ott_title_id_fk AS title_id, titles.ttl_title_id, titles.ttl_title_code, titles.ttl_date FROM (((owners RIGHT JOIN owners_to_titles ON owners.own_owner_id = owners_to_titles.ott_owner_id_fk) JOIN titles on owners_to_titles.ott_title_id_fk = titles.ttl_title_id) JOIN owners_to_ethnicities ON owners.own_owner_id = owners_to_ethnicities.ote_owner_id_fk) JOIN ethnicities ON owners_to_ethnicities.ote_ethnicity_id_fk = ethnicities.eth_ethnicity_id ORDER BY owners.own_surname)

However, I ended up building the entire thing as a single view, like this:

DROP VIEW IF EXISTS buyers_and_sellers

CREATE VIEW buyers_and_sellers AS
(SELECT 
buyers.own_owner_id AS buyer_id, buyers.own_surname AS buyer_surname, buyers.own_forenames AS buyer_forenames,
buyer_eth.eth_name AS buyer_ethnicity,
census_tracts.census_tract_code,
titles.ttl_title_id, titles.ttl_title_code, titles.ttl_date, 
titles_to_prectitles.ttp_prectitle_id_fk,
sellers.own_owner_id AS seller_id, sellers.own_display_name AS seller_name,
seller_eth.eth_name AS seller_ethnicity

FROM owners AS buyers
JOIN owners_to_titles on buyers.own_owner_id = owners_to_titles.ott_owner_id_fk
JOIN titles on owners_to_titles.ott_title_id_fk = titles.ttl_title_id
JOIN titles_to_prectitles ON titles.ttl_title_id = titles_to_prectitles.ttp_title_id_fk
JOIN owners_to_ethnicities ON buyers.own_owner_id = owners_to_ethnicities.ote_owner_id_fk
JOIN ethnicities AS buyer_eth ON owners_to_ethnicities.ote_ethnicity_id_fk = buyer_eth.eth_ethnicity_id
JOIN owners_to_titles AS sellers_to_titles ON titles_to_prectitles.ttp_prectitle_id_fk = sellers_to_titles.ott_title_id_fk
JOIN owners AS sellers ON sellers_to_titles.ott_owner_id_fk = sellers.own_owner_id
JOIN owners_to_ethnicities AS sellers_to_ethnicities ON sellers.own_owner_id = sellers_to_ethnicities.ote_owner_id_fk
JOIN ethnicities AS seller_eth ON sellers_to_ethnicities.ote_ethnicity_id_fk = seller_eth.eth_ethnicity_id 
JOIN props ON titles.ttl_property_id_fk = props.prp_property_id
JOIN census_tracts ON props.prp_census_tract_id_fk = census_tracts.census_tract_id
ORDER BY buyers.own_surname)

Right now this brings back 500 records, so I assume there are only 500 instances where we have a transfer from one person to another in which we know both the buyer and the seller. However, I need to check this carefully; I'll also probably have to loosen up the query to get us transfers for which less info is available.

The next view I've completed is the following:

Total transfers by property (sortable by decade and by sample)

because we have all the data for it.

• We need a view with the following columns:
  • Property id
  • Some other property data -- columns from the props table
  • Census tract
  • Title id
  • Title code
  • Title date
  The first block comes from the properties table (and the census tracts table), and the second from the titles table. We want a row for each title (presumably), since we'll be including dates, and those only exist in the titles table; so we need a right join.
• This code gets us a complete table of all the titles with property and census tract information included:

  SELECT props.*, titles.ttl_title_id, titles.ttl_title_code, titles.ttl_date, census_tracts.census_tract_code FROM (`props` RIGHT JOIN titles on titles.ttl_property_id_fk = props.prp_property_id) JOIN census_tracts on props.prp_census_tract_id_fk = census_tracts.census_tract_id ORDER BY titles.ttl_date
  

• We then need to limit the results to transfers; that means limiting it to titles which have a preceding title; we can do this with a further right join, like this:

  SELECT props.*, titles.ttl_title_id, titles.ttl_title_code, titles.ttl_date, census_tracts.census_tract_code, titles_to_prectitles.ttp_prectitle_id_fk AS `prectitle_id` FROM ((`props` RIGHT JOIN titles on titles.ttl_property_id_fk = props.prp_property_id) JOIN census_tracts on props.prp_census_tract_id_fk = census_tracts.census_tract_id) RIGHT JOIN titles_to_prectitles on titles_to_prectitles.ttp_title_id_fk = titles.ttl_title_id ORDER BY titles.ttl_date
  

• Turning that into a view gives us this:

  DROP VIEW IF EXISTS trans_by_property
  
  CREATE VIEW trans_by_property AS (SELECT props.prp_property_id, props.prp_property_code, props.prp_district, props.prp_plan, props.prp_block, props.prp_lot, titles.ttl_title_id, titles.ttl_title_code, titles.ttl_date, census_tracts.census_tract_code, titles_to_prectitles.ttp_prectitle_id_fk AS `prectitle_id` FROM ((`props` RIGHT JOIN titles on titles.ttl_property_id_fk = props.prp_property_id) JOIN census_tracts on props.prp_census_tract_id_fk = census_tracts.census_tract_id) RIGHT JOIN titles_to_prectitles on titles_to_prectitles.ttp_title_id_fk = titles.ttl_title_id ORDER BY props.prp_property_id)
  

Sent an XLS dump of this view to JS-R, and also created a user for him so he can read and export views himself.

We're beginning the process of creating specific views which can be used for statistical analysis of the data. This is the set of views requested by JS-R:

• Total transfers by decade by sample
• Vendor ethnicity by buyer ethnicity (with analysis possible by date and by sample)
• Vendor ethnicity by duration of ownership (with analysis possible by date and by sample)
• Vendor ethnicity by mortgage institutional or not (we will fill in this data as we go) (with analysis possible by date and by sample)
• Total transfers by property (sortable by decade and by sample)

Now we have to formalize those in terms of the various tables and fields that need to be involved. This is my first attempt at the first view:

CREATE VIEW trans_by_date AS SELECT titles_to_prectitles.ttp_ttp_id, titles.ttl_title_id, titles_to_prectitles.ttp_prectitle_id_fk, titles.ttl_title_code, titles.ttl_date, props.prp_census_tract_id_fk, census_tracts.census_tract_code FROM ((titles_to_prectitles JOIN titles on titles_to_prectitles.ttp_title_id_fk = titles.ttl_title_id) JOIN props on titles.ttl_property_id_fk = props.prp_property_id) JOIN census_tracts on props.prp_census_tract_id_fk = census_tracts.census_tract_id ORDER BY titles.ttl_date

With help from JN, this is the SQL that creates links where there is a match:

INSERT INTO `titles_to_prectitles` (`ttp_title_id_fk`, `ttp_prectitle_id_fk`)  SELECT `tt2`.`ttl_title_id` AS `id_1` , `tt1`.`ttl_title_id` AS `id2`
FROM `titles` AS `tt1`
INNER JOIN `titles` AS `tt2` ON tt1.ttl_title_code = tt2.ttl_traces

This checks that there are no duplicate records in the linking table (in case the manual data entry had already created one of the ones we've just created):

SELECT ttp1.ttp_ttp_id, ttp2.ttp_ttp_id FROM titles_to_prectitles ttp1, titles_to_prectitles ttp2
WHERE ttp1.ttp_title_id_fk = ttp2.ttp_title_id_fk and ttp1.ttp_prectitle_id_fk = ttp2.ttp_prectitle_id_fk and ttp1.ttp_ttp_id < ttp2.ttp_ttp_id

This clears out the Traces field wherever these links have been created:

UPDATE titles ttl1 SET ttl_traces = "" WHERE ttl_traces IN (SELECT * FROM (SELECT ttl_title_code FROM titles ttl2) as codes)

Note that the nested subquery is necessary because MySQL won't allow you to use the table you're updating in a WHERE clause.

These are the results:

From a total of 2274 titles:

• 1315 titles have at least one link to a preceding title.
• 423 titles have more than one link to a preceding title.
• 1526 links between a title and a preceding title exist.

1115 titles still have data in their Traces field. Some of these items might be linkable by a human. For instance:

• There are two titles with the code 40821I.
• Both have 40616I in their Traces field.
• There is no title 40616I, but there are two candidates:
  • 40616I-24 (Property 169 B:16 L:30)
  • 40616I-30 (Property 059 B:11 L:24)
  • #17 Title 40821I is Property 167 B:16 L:32, so it doesn't look like it could be matched to anything.
  • #19 Title 40821I is Property 169 B:16 L:30 so it could be linked to 40616I-24.

Obviously these sorts of decisions can only be made by a human.

This simpler query shows up more potential hits:

SELECT *
FROM `titles` AS `tt1`
WHERE EXISTS (

SELECT *
FROM `titles`
WHERE tt1.ttl_title_code = titles.ttl_traces
)

This query brings back the candidate ids:

SELECT `tt1`.`ttl_title_id` AS `id_1`, `tt2`.`ttl_title_id` AS `id2`
FROM `titles` AS `tt1` LEFT JOIN `titles` AS `tt2`
ON tt1.ttl_title_code = tt2.ttl_traces

We'll put more work into this tomorrow.

Came to automate the linking of titles to preceding titles as specified in the previous post, but found that with this data, manual intervention was essential. I initially isolated 38 candidate records like this:

SELECT `ttl_title_id` FROM `titles` AS `ts1` WHERE NOT EXISTS (SELECT * FROM `titles_to_prectitles` WHERE `ttp_title_id_fk` = `ts1`.`ttl_title_id`) AND EXISTS (SELECT `ttl_title_id` FROM `titles` AS `ts2` WHERE TRIM(`ts1`.`ttl_traces`) = `ts2`.`ttl_title_id`) ORDER BY `ts1`.`ttl_title_id`

But then I looked at each candidate individually, because in many cases, there was only a partial entry in the Traces field. For instance, looking at a record with "2144K" in the Traces field, we find there are two possible candidates for the precedint title, 2144K-19 and 2144K-21. We can select the correct one by checking the Property field, so (for instance) we can tell that if the Property field for the candidate record has 254 B:39 L:19, and 2144K-19 also has 254 B:39 L:19, then that's the correct preceding title.

With this method I was able to match nearly all the 38 candidates to the correct preceding record(s).

We end up with the following:

• 491 links between titles and preceding titles
• 288 titles which are linked to preceding titles
• 301 titles which are preceding titles for other titles

Don't know how useful this will be.

The manual work on preceding titles has been done, so the automated component can now be undertaken. This is the task:

• Where there is only one entry in the ttl_traces field (no returns)...
• ...and there is no entry in the Preceding titles field (meaning there are no records in the titles_to_prectitles table for this ttp_title_id_fk...
• ...check to see if the entry in the ttl_traces field corresponds with the ttl_title_code of any existing record...
• ...and if it does, then create a record in the titles_to_prectitles table linking the two.

Caveat: there are some items where there are brackets around one or more characters; we'll probably have to ignore those (and lookups will fail anyway).

Meeting with JS-R and his RA today, preparing for the next phase of data entry. Mostly this will be manual fixes to the existing records, but there are a couple of things I need to do.

I had to programmatically split out the forenames and surnames into separate fields from the display name field, in both the owners and lenders table. We agreed to split on the last space, and then fix the results manually. Here's how to get the surname:

SELECT RIGHT( `own_display_name` , LOCATE( ' ', REVERSE( `own_display_name` ) ) - 1 )
 FROM `owners`

and this is the bit preceding the surname:

SELECT LEFT( `own_display_name` , CHAR_LENGTH( `own_display_name`) - LOCATE( ' ', REVERSE( `own_display_name` ) ) )
 FROM `owners`

This is the update command for surnames:

UPDATE `owners` SET `own_surname` = (SELECT RIGHT( `own_display_name` , LOCATE( ' ', REVERSE( `own_display_name` ) ) - 1 )) WHERE CHAR_LENGTH(`own_surname`) = 0

and this is for forenames:

UPDATE `owners` SET `own_forenames` = (SELECT LEFT( `own_display_name` , CHAR_LENGTH( `own_display_name`) - LOCATE( ' ', REVERSE( `own_display_name` ) ) )) WHERE CHAR_LENGTH(`own_forenames`) = 0

These are the commands for lenders:

UPDATE `lenders` SET `lnd_surname` = (SELECT RIGHT( `lnd_display_name` , LOCATE( ' ', REVERSE( `lnd_display_name` ) ) - 1 )) WHERE CHAR_LENGTH(`lnd_surname`) = 0

UPDATE `lenders` SET `lnd_forenames` = (SELECT LEFT( `lnd_display_name` , CHAR_LENGTH( `lnd_display_name`) - LOCATE( ' ', REVERSE( `lnd_display_name` ) ) )) WHERE CHAR_LENGTH(`lnd_forenames`) = 0

Tested on dev, and implemented on live.

Updates mentioned in the preceding post:

ALTER TABLE `props` ADD `prp_street_num` varchar(45) collate utf8_unicode_ci default NULL;
ALTER TABLE `props` ADD `prp_street` varchar(45) collate utf8_unicode_ci default NULL;

In class PropProperty:

      $this->addField(new MdhStrField('prp_street_num', 'Street number', '', array(), 45, true));
      $this->addField(new MdhStrField('prp_street', 'Street name', '', array(), 45, true));

New table for ethnicities for lenders:

--
-- Table structure for table `lenders_to_ethnicities`
--

CREATE TABLE IF NOT EXISTS `lenders_to_ethnicities` (
  `lte_lte_id` int(11) NOT NULL auto_increment,
  `lte_lender_id_fk` int(11) NOT NULL,
  `lte_ethnicity_id_fk` int(11) NOT NULL,
  PRIMARY KEY  (`lte_lte_id`),
  KEY `lte_ethnicity_id_fk_idx` (`lte_ethnicity_id_fk`),
  KEY `lte_lender_id_fk_idx` (`lte_lender_id_fk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

--
-- Constraints for table `lenders_to_ethnicities`
--
ALTER TABLE `lenders_to_ethnicities`
  ADD CONSTRAINT `lenders_to_ethnicities_ibfk_1` FOREIGN KEY (`lte_lender_id_fk`) REFERENCES `lenders` (`lnd_lender_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `lenders_to_ethnicities_ibfk_2` FOREIGN KEY (`lte_ethnicity_id_fk`) REFERENCES `ethnicities` (`eth_ethnicity_id`) ON DELETE CASCADE ON UPDATE CASCADE;

In class PropLender:

$this->addField(new MdhOneToManyField('lnd_ethnicities', 'Ethnicities',
                                'lnd_lender_id', 
                                'ethnicities',
                                'eth_ethnicity_id',
                                'eth_name',
                                'lenders_to_ethnicities',
                                'lte_lte_id',
                                'lte_lender_id_fk',
                                'lte_ethnicity_id_fk',
                                true,
                                'eth_name',
                                true));

Linking titles to preceding titles:

--
-- Table structure for table `titles_to_prectitles`
--

CREATE TABLE IF NOT EXISTS `titles_to_prectitles` (
  `ttp_ttp_id` int(11) NOT NULL auto_increment,
  `ttp_title_id_fk` int(11) NOT NULL,
  `ttp_prectitle_id_fk` int(11) NOT NULL,
  PRIMARY KEY  (`ttp_ttp_id`),
  KEY `ttp_title_id` (`ttp_title_id_fk`),
  KEY `ttp_prectitle_id` (`ttp_prectitle_id_fk`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3157 ;

--
-- Constraints for table `titles_to_prectitles`
--
ALTER TABLE `titles_to_prectitles`
  ADD CONSTRAINT `titles_to_prectitles_ibfk_1` FOREIGN KEY (`ttp_title_id_fk`) REFERENCES `titles` (`ttl_title_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `titles_to_prectitles_ibfk_2` FOREIGN KEY (`ttp_prectitle_id_fk`) REFERENCES `titles` (`ttl_title_id`) ON DELETE CASCADE ON UPDATE CASCADE;

In class PropTitles:

$this->addField(new MdhOneToManyField('ttl_prec_titles', 'Preceding titles',
                                'ttl_title_id', 
                                'titles',
                                'ttl_title_id',
                                'ttl_title_code',
                                'titles_to_prectitles',
                                'ttp_ttp_id',
                                'ttp_title_id_fk',
                                'ttp_prectitle_id_fk',
                                true, 'ttl_title_code', true));

My original intention was to populate this last "Preceding titles" field programmatically by parsing the data in the current Traces field, which should also be pointing back at previous title data. However, after looking at some of that data, I think it's going to have to be done by a human. Here's an example of why:

If you go to the Titles table, then choose the third property in the drop-down Property list (005 B:103 L:B), and filter by it, you'll see five results. These should, in theory, represent a succession of titles relating to the same property, by date; the Traces field for each one should point back to the preceding one. However:

• The latest title is 561978L, from 1967. Its Traces field points back to 561975L, but there is no such title in the db.
• The title preceding that by date, 403327L from 1959, points back to two separate titles, 272233L and 256807L. The former does not exist in the database.
• The latter does, though, and this relationship seems logical, pointing back to a 1951 title, which in turn points back to another 1951 entry, which then points back to yet another entry, A.F.B. 26-148-4041E; but this last one has no date. We can (as humans) guess from this that the last one is the earliest, but its absence of date will make coming to that conclusion programmatically a bit of a stretch.

In short, writing code that does a reasonable job of gleaning the preceding title data from what exists in the Traces field is not going to be practical or reliable; a human should go through the data and work this out, I think.

Points arising out of a meeting with JS-R this morning:

The main realization that emerged for me was that the core of the research focus relates to transactions (events whereby one property is transferred from one owner to another); but transactions do not constitute datapoints in the records themselves. Transactions are primarily represented by titles. Each title has a date (which is the date ownership changed), and information about the new owners; but it doesn't have any information about the previous title or owners. Titles are related only in that they relate to the same property.

What we need to do is to generate a Transactions view which brings together all the data about each transaction: property, date, old title, new title, old owner(s), new owner(s), and ethnicities of both sets of owners. This is more complicated than it seems. I think the first stage is probably to create a field in the existing Titles table which hard-links to the previous title on the same property. This can be computed based on Property and date, but there is also the Traces field which should already contain a list of older titles on the same property. The first thing to do is to calculate the previous title based on property and date, and then compare it against the data in the text field to see if we have an acceptable degree of consistency. There are also issues regarding the unification of titles; one title may have multiple precursors rather than just one.

The following is a set of questions that we are going to attempt to answer through queries against the current database, using specially-constructed views, to test the difficulty of addressing research questions through a programmer writing SQL; these will then act as a testbed for an attempt to imagine ways in which an interface could be coded which would allow the researcher to frame such questions without the intervention of a programmer, and get tabular results.

• How did the ethnic composition of owners shift over time (by sample)? (This probably needs a Transactions view which can be organized by date, owner ethnicity and census tract.)
• Did owners sell to co-ethnics (by time, by sample, by ethnicity)? (Ditto -- Transactions view again.)
• Did the Powell Street properties (CT 58.P) shift to Chinese owners in the 40s and 50s? (Ditto.)
• Did absentee ownership increase over time? (This requires a definition of absentee ownership, which will have to be derived by examination of owner's address compared with property location; this is likely to be a boolean field (absentee or not), or a distance field (how far away the owner lives); the extra field will have to be added to the Titles table once this has been decided, and then completed by a human.)
• Did sources of mortgage funding increase over time (by sample, by ethnicity)? This will require that mortgages be added to the Transactions view.)

Immediate tasks for me:

1. Add two fields to the Properties table: Street # and Street Name (as in Owners table).
2. Add an ethnicity field to lenders.
3. Add a "previous title" field to Titles, and populate it programmatically.
4. Create a Transactions View.

Meanwhile, JS-R and his RA will be adding ethnicity data to Owners and Lenders, and splitting out their names, as well as adding the new address info to Titles.

These requested features have been completed:

• The Lenders table now has a checkbox for "individual" (as opposed to institutional).
• The Owners table now has a checkbox for "institutional" (as opposed to individual).
• The Titles table now has a field for "price".
• The Owners table now has a one-to-many field for "Ethnicities".
• Search now provides a Spreadsheet button which will retrieve the results of a search in the form of a CSV file which can be opened by a spreadsheet program.

These features remain to be completed:

• Click on a column to sort the search results by that column.
• Customized VIEWs of the data for specialized searching (this requires JS-R's input for details of what needs to be in the view).

I'm now working through the list of requested changes to see how many of them are completed already, and what remains to be done.

Since the AdaptiveDB code is now much cleaned up and improved, I've done an export from Subversion into the Properties DEV db and tested it there. It seems to work fine, with the exception that memory requirements for spreadsheet output are significant (getting 207 records out required that I up the memory allocation to 256MB). We're now at the stage where we should be building the VIEW that's required for proper querying, and I need to consult with JS-R about what should be shown in the view.

The first four items in the new plan have been completed, first on dev, then after testing, migrated to live.

• First, the codebase was updated to include all the recent updates from the Adaptive DB project. One setting in the Properties project was migrated in the reverse direction (a CSS tweak that controls the size of filter text boxes in table headers).
• Next, the db structure was modified with this script:

  ALTER TABLE `lenders` ADD `lnd_individual` int(11) NOT NULL default '0';
  ALTER TABLE `owners` ADD `own_institutional` int(11) NOT NULL default '0';
  ALTER TABLE `titles` ADD  `ttl_price` int(11) default NULL;
  
  DROP TABLE IF EXISTS `owners_to_ethnicities`;
  DROP TABLE IF EXISTS `ethnicities`;
  
  CREATE TABLE IF NOT EXISTS `ethnicities` (
    `eth_ethnicity_id` int(11) NOT NULL auto_increment,
    `eth_name` varchar(128) collate utf8_unicode_ci NOT NULL,
    PRIMARY KEY  (`eth_ethnicity_id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  
  CREATE TABLE IF NOT EXISTS `owners_to_ethnicities` (
    `ote_ote_id` int(11) NOT NULL auto_increment,
    `ote_owner_id_fk` int(11) NOT NULL,
    `ote_ethnicity_id_fk` int(11) NOT NULL,
    PRIMARY KEY  (`ote_ote_id`),
    KEY `ote_ethnicity_id_fk_idx` (`ote_ethnicity_id_fk`),
    KEY `ote_owner_id_fk_idx` (`ote_owner_id_fk`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  
  ALTER TABLE `owners_to_ethnicities`
    ADD CONSTRAINT `owners_to_ethnicities_ibfk_1` FOREIGN KEY (`ote_owner_id_fk`) REFERENCES `owners` (`own_owner_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `owners_to_ethnicities_ibfk_2` FOREIGN KEY (`ote_ethnicity_id_fk`) REFERENCES `ethnicities` (`eth_ethnicity_id`) ON DELETE CASCADE ON UPDATE CASCADE;
  

• The local_classes.php file was updated to include all the new fields and the Ethnicities table.

What remains now is the enriched View/Search described in the preceding post.

Meeting with JS-R to outline the next steps:

• Add a price field to the title table. This would always be round numbers of CAD dollars.
• Add a checkbox field called institutional to the owners table, to be checked when the owner is an institution rather than an individual.
• Add a checkbox field called individual to the lenders table, to be checked when the owner is an individual rather than an institution (the reverse of the above case; owners are usually individuals, whereas lenders are usually institutions).
• Create a one-to-many field in the owners table for owner ethnicity, with a separate table for ethnicities.
• Create an enriched view/search system (see below).
• Once the enriched view/search is available, JS-R can use it to identify lists of owners and lenders who are actually the same person or institution; he will send those to us, and we'll run a script to "collapse" them into one (changing all references in other tables to the first or preferred record, and then deleting the duplicates).

The "enriched view/search" interface can be developed in tandem with similar requirements for the AdaptiveDB project. It will be based on Views, which are constructed based on instructions from the researcher. In this case, we would be creating enriched views of some of the core tables, pulling in data from other tables (so that owners, lenders, census tract, etc. would be pulled in to the table as string fields). Then a search can be done on the enriched views, with the usual ability to check only the fields you want to see; and finally, the results of a search can be retrieved as a CSV file for use in a spreadsheet program. The CSV would include ALL results from the search (whereas the page usually displays only a limited number). We should also make the results table sortable by column, with a sort request operating to re-run the query with a sort parameter (so it doesn't just sort the e.g. 20 rows returned initially, it re-queries, sorts, and sends back a new 20 rows).

What I think we can realistically achieve is a spreadsheet specification that
1) captures the necessary fields and relationships to other documents
2) is feasible for you to enter the necessary data
3) is suitable for Martin and me to process into tables and fields when we do have to time to work further on this project.

That will allow JM and JSR to collect data this summer, and that data will be in a state that will facilitate further work.

JM and JSR work out a spreadsheet spec
send it to Martin and me
meet to review spec and confirm.

Confirm Martin's guess that there's a fair bit of work to incorporate information obtained from mortgage documents.
HCMC won't be able to do development work until late summer or early fall at earliest, but I asked a number of questions so that they could at least normalize their spreadsheet data, making it easier to process it later.

Got JSR and JM to clarify:

lender = mortgagee = assignee
mortgagor = assignor
guarantor = covenator

We'll need to add a Guarantors table. We'll also need to create a table
which relates a guarantor to a mortgage (and possibly an owner). Details to be worked out.

We'll need to add a Solicitors table. We'll also need to add a
solicitors_clients_mortgages table which associates a solicitor, a
client and a mortgage transaction.

If we want to relate a mortgage to one or more previous mortgages, we
will have to create a mortgage_to_mortgage table. Are the conventions
for treating this going to be similar to those used for previous title?
If we're creating a mortgage_to_mortgage table, does it make sense to
also accommodate the relationship between simultaneous first and second
mortgages? i.e. any second mortgage gets a record in the
mortgage_to_mortgage table associating it with a first mortgage.

The various "status" fields could be complicated if we want to do
anything more than display the text. Right now you are using it for two
things:
1 to describe an attribute of an individual party (e.g. longshoreman),
2 to describe the relationships between two of the parties (e.g. wife of).
Those two kinds of information should be tracked in separate fields. The
attribute information is a simple field, but for the relationship
information if we want to be able to query related people we must also
track who is the other person in the relationship.

JM and JS-R have proposed a new table, called Transfers, which will involve some significant work. I'm still trying to clarify exactly what's required; it's not clear to what extent fields in the new table are going to be lookups to existing records in other tables.

Migrated yesterday's changes to the live db (JM starts work at 9 in the archives, so this needed to be done first thing).

Used this code to create a VIEW which concatenates some of the fields from the Properties table:

CREATE VIEW `properties_dev`.`props_abbr` AS SELECT `prp_property_id` AS `prpabbr_property_id`, CONCAT(`prp_property_code`, " B:", `prp_block`, " L:", `prp_lot`) as `prpabbr_property_info` FROM `properties_dev`.`props`

For tomorrow, this is the variant to run on the live db:

CREATE VIEW `properties`.`props_abbr` AS SELECT `prp_property_id` AS `prpabbr_property_id`, CONCAT(`prp_property_code`, " B:", `prp_block`, " L:", `prp_lot`) as `prpabbr_property_info` FROM `properties`.`props`

In the local_classes.php file, changed this line:

$this->addField(new MdhStrSelectField('ttl_property_id_fk', 'Property', 0, 'props', 'prp_property_id', 'prp_property_code', true, true));

to this:

$this->addField(new MdhStrSelectField('ttl_property_id_fk', 'Property', 0, 'props_abbr', 'prpabbr_property_id', 'prpabbr_property_info', true, true));

Finally, when trying to view large record sets (200+ records) in a table, I was getting out-of-memory errors, so in connect.php I had to change this:

ini_set("memory_limit","24M");

to this:

ini_set("memory_limit","48M");

Tested the results, and all seems to be working OK.

JM wrote requesting a couple of changes, two of which I've done, and the other which is feasible but will take a little work, so I'm getting more clarification on the details before proceeding.

I've moved the Traces field so that it's next to Title Code, and changed the sort order back to sorting by id. I haven't changed the sort order of Titles when they appear in drop-down lists in other tables; I'm waiting for JM's response on whether I should do that.

The other request involves showing data from the Properties table in the Titles table. This would require creating a View of the Properties table which amalgamates several of its fields, and using that View as the source for the Property field in the Titles table, rather than the Properties table directly. The fields requested now are Block and Lot, but I want to be sure that's all that's required before I proceed. This is what I understand from JM's message:

The Property field in the Titles table currently shows only the property code. However, it would be better if it showed not only the property code, but also some other information from the Properties table -- for instance, instead of showing just

 147

it would help if it showed:

 147 B:21 L:8

meaning "Property code 147, Block 21, Lot 8".

I estimate an hour or two to put this in place, test it, and roll it out, so it's on the table for this month once I get the details.

JM wrote to describe a situation in which the same title number is associated with two different properties, in which case a new record is required in the Titles table, with only its id differing from the original. This could be a accomplished through a "clone record" function, which would create a new record, but populate it with data from an existing record. It's not a simple process -- I estimate about four to five hours with testing -- and I don't have the time right now. I'm still waiting for word on how often this situation will arise; if it's only a handful of times, then it's not worth the new feature (unless we think it's a good idea for other reasons), but if it's going to be hundreds of times, then it's probably worth it, and I could schedule the work for August.

The Title table, at the heart of the db, was originally sorting in reverse id order, so that the latest-added items would sort to the top, per the original requirements specified. This turns out not to be ideal, so I've changed the sort order so that they sort by tt_title_code field, ascending, and made the same change for the various contexts in which titles show up in relational tables. Tested with dev before publishing to live. Also, as of today, dev contains a current copy of the live db.

Done at JM's request.

By default, one-to-many fields, when retrieved in the form of a <select> element which is used to add one to a related table, are sorted by the text field in them, ascending. However, this project will be more efficient if the latest-added item is sorted to the top -- so they should be sorted by id, descending. I added this feature as a pair of settings in the MdhOneToManyField class, after fixing another bug in the Search interface (see the AdaptiveDB blog). While I was at it, I figured it would also make more sense for ascending sorting to be available to all tables, so I've made it a feature of the MdhRecord class, and turned it on for all the tables in this project. That means all new items show at the top of lists, which makes sense to me.

The live site and dev site are now identical, featuring all tables. Next I have to look at the possibility of sorting relational field drop-downs in descending order of id (to bring the newest to the top).

Implemented GUIs for the last few tables. We decided to enable the editing of some relationships in more than one location (e.g. owners can be linked with titles in both the title and owner record, and ditto for mortgages and titles). There are more of these types of relationship which could be made visible in two places, but we'll see first if this facility is helpful or just confusing to the data entry folks.

I've now implemented and tested GUI components for the following tables:

• census_tracts
• other_docs
• owners
• props
• props_to_props
• titles (a bit more to do there)
• docs_to_titles
• joint_tenants_to_titles
• owners_to_titles
• lenders
• titles_to_titles

Remaining:

• mortgages
• lenders_to_mortgages
• mortgages_to_titles

Following the model used in the AdaptiveDB project, I've set up a parallel database called properties_dev, and a parallel PHP structure, so we now have site and site_dev on the server. I've also written a script to copy over the live data from the live db to the dev db (not a simple process because of all the foreign keys; everything has to be done in exactly the right order to avoid failures). I've tested this in a limited way -- there's only a tiny bit of data in two tables at the moment, because the other table definitions haven't been created yet, so it can't be fully tested. But this will give us the ability to build the remaining GUI components and test them in a separate instance, while the academics work on the existing system.

Implemented the editing GUI for the first two tables, census_tracts and props. These only depend on each other, so entering of that data can begin immediately if JS-R and JM want to get going. I'll add more tables over the next few weeks.

Went through an interesting process building the db from Workbench. I generated the build script from Workbench, and tried importing into MySQL, but it kept stumbling, first on foreign keys and then on indexes. I removed the foreign key index code, and got it to build the tables, then manually entered all the indexes and keys. In the process, I noticed a couple of typos in table and field names, and fixed them.

After completing the db, I exported it and then imported the dump into Workbench, to see what might be different. Then we discovered that we'd misunderstood how Workbench builds foreign keys; we had assumed that it was creating the local column for the foreign key as part of the foreign key creation process, but it wasn't; that was the cause of the failures in the create script.

Rebuilt the diagram and sent it to JS-R and JM. Now I can start building the AdaptiveDB interface for it.

Before starting on this I tested several db schema design applications, looking for a replacement for ModelRight, which I'd used on Windows. Tried ModelRight under Wine, but no joy. Ended up settling on MySQL Workbench, which is not perfect, but pretty good.

Worked out a complete schema for the project, which Workbench can export as SVG, PDF and various other types of thing. It's also able to export SQL to build the db, but this fails due to a bug in the version of MySQL that we're running, apparently; it gacks on foreign key creation altogether, and even has trouble with creating indexes. I guess I'll have to hand-edit the SQL and work table-by-table in PHPMyAdmin. Curses.

Met with SA, JS-R and JM to work out a basic database schema, which we'll implement. The whiteboard contains our very messy diagrams, which will have to be turned into a proper model tomorrow; then we'll start work on implementing the db and a user interface based on the Adaptive DB code, starting with the "properties" and "census_tracts" tables. I'll post a model document when we've finished it.