HCMC Blogs

Working to prep for volunteer training tomorrow, and get some last-minute changes done to Properties db.

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
)

§ 2.5.10 - ID1989/2214, Echidna and Poseidon are god parents of 'an immortal dragon' (if this is referring to Orthus, then can we delete these 2 records? There is no specific mention in 2.5.10 of a dragon borne to Echidna and Poseidon) "character/character/citation" **Moved to 2.5.11**
- ID1342, Hercules puts up pillars (would it be ok to be more specific here, "at the boundaries of Europe and Libya [is this modern day Gibraltar-ish area?]") "event/character/citation" **Nevermind**
§ - ID2622-2623, A bull from Geryon's herd escapes Hercules (Apollo and Hyacinth (1) are mentioned as being involved in this event, but I cannot find them in the text, please clarify) "event/character/citation" **Deleted Apollo & Hyacinth from this event**
§ 2.5.11 - Typhon and Echidna are god parents of the 'immortal dragon' with a hundred heads (add relationships?) "character/character/citation" **Already done**
§ - ID450, Emathion is son of [blank] (change father to "Tithonus"?) "character/character/citation" **Deleted this record, added citation to ID2766 which already cited relationship**
- Should we add an event "Hercules is ordered to do an eleventh labor, fetching golden apples from the Hesperides, since Eurystheus did not acknowledge two of Hercules' labours"?
- ID1395, Phrasius advises the Egyptians to make human sacrifices to Zeus (should this be changed to reflect that in particular strangers were sacrificed, and that Phrasius himself was the first to be sacrificed?) "event/character/citation"
§ - ID1396, Hercules steals a cow and eats it (text says he sacrificed a bullock, or young bull, should this say "bull" instead?) "event/character/citation"
§ 2.5.12 - ID1418-1419, Hercules goes to be initiated (Hercules and Eumolpus (1) are noted, should we also add Pylius? Text says "since [Hercules] proposed to be initiated as the adoptive son of Pylius") "event/character/citation"
2.6
2.6.1 - ok
2.6.2 - Add event "Hercules gets an oracle by himself, which declares that to be free of disease he must be sold, and serve for three years"?
2.6.3 - ID1461-1462, Tmolus rules Lydia, Omphale takes over after he dies (text says "…Omphale, daughter of Iardanes, queen of Lydia, to whom at his death her husband Tmolus had bequeathed the government", change to say "Tmolus rules Lydia with Omphale, after her father Iardanes dies and bequeathes Lydia to Tmolus"?) "event/character/citation" **Please double-check this, I am unsure about it**
§ - ID523, ibid. (change location of event to "Lydia"?) "event/place"
§ - ID1471, Hercules buries Icarus (Hercules is involved in this event, add Icarus also?) "event/character/citation"
§ - ID1475, Hercules musters a group of volunteers to sail to Troy (doesn't belong in 2.6.3, move to 2.6.4) "event/character/citation"
§ - ID532, ibid. "event/place"
§ 2.6.4 - Add Podarces as son of Laomedon (1)? Also, Podarces is referred to as Priam in the text, add Priam as son of Laomedon (1) also?
§ - ID85/499, Hercules' expedition to Ilium (Oicles and Telamon are part of this group, but not Hercules, add?) "group/character/citation"
* - Hercules thanks Telamon by giving him Hesione as a prize, does this mean that she is his spouse? Please clarify, also for future references of spoils of war if humans are taken does that make them spouses or slaves, or both? I suppose context will be very important in issues like this.
2.7
2.7.1 - ok
2.7.2 - ok
§ 2.7.3 - ID547, Hercules attacks the Lacedaemon with Cepheus… (change to "Hercules attacks Lacedaemon…"?) "event/place" **Also added this to the "event/character/citation" tab and cited characters: Hercules, Cepheus (4), Hippocoon (2), Icarius (1), Tyndareus (2), Iphicles, Dorycleus, Scaeus, Enarophorus, Eutiches, Bucolus (2), Lycaethus (1), Tebrus, Hippothous (2), Eurytus (5), Hippocorystes, Alcinus, Alcon. This is already cited in 3.10.5 so I added citations for 2.7.3 as well**
§ 2.7.4 - ID554, A bull from Geryon's herd escapes Hercules (does not belong in 2.7.4) "event/place" **Deleted**
2.7.5 - ok
* 2.7.6 - ID466, Architeles is mortal father of Eunomos (Frazer's footnote says that his name is "variously given as Eunomus, Ennomus, Eurynomus, Archias, Cherias, and Cyathus", if we use Eunomus should we change spelling? Also, text doesn't explicitly name the victim, other than he was a kinsman of Oeneus and son of Architeles) "character/character/citation"
- ID1546-1548, Hercules accidentally kills Eunomos… (ibid., change to "Hercules accidentally kills son of Architeles/Eunomus…"?) "event/character/citation"
- ID564, Nessus ferries Deianira across the river… (place is cited as "Lycormas", but text says "he came to the river Evenus", change to "Evenus"?) "event/place"
§ 2.7.7 - ID1553-1554, Hercules eats on of Thiodamas' bullocks (change spelling to "Hercules eats one of Thiodamas' bullocks"?) "event/character/citation"
§ - ID1557-1560, Hercules fights with Aegimius against the Lapiths, Hercules kills Coronus, kills Dryopes and his children (change to "…kills Laogoras, king of the Dryopes and his children"?) "event/character/citation" **Changed spelling to: "Hercules fights with Aegimius against the Lapiths. Hercules kills Coronus, and kills Laogoras, king of the Dryopes and his children"**
§ - ID1563-1564, Hercules kills Amytor (change spelling to "Amyntor"?) "event/character/citation
* 2.7.8 - ID1414, Eury… is mortal mother of Teleutagoras (the record and text say "Eury…" is the mother, are there any other sources that give the name or is it no longer legible?) "character/character/citation"
§ - ID1381, Exole is mortal mother of Erythas (text says "Erythras", change?) "character/character/citation" **Changed character "Erythas" to "Erythras"**
* - Text mentions Hercules bore "by Anthea …", is this also lost or do we know if any other sources mention whom Hercules had with Anthea?
- I just noticed now that there are 2 entries for "Omphale", but they are not named Omphale (1) and Omphale (2) like other characters, should we check this to see if it needs to be done, or are they supposed to be the same Omphale?
§ - ID1408, Chalciope (2) is mortal mother of Thetallus (text says "Thettalus", change?) "character/character/citation" **Changed character "Thetallus" to "Thettalus"**
2.8
* 2.8.1 - Text says "the Athenians bore the brunt of war with Eurystheus, and slew his sons, Alexander, Iphimedon, Eurybius, Mentor and Perimedes. Eurystheus himself fled in a chariot…" but the records for character/character/citation state that Hercules is the father of these men. Is Eurystheus the father? It would make more sense in the context of the passage. If so, many of the records under 2.8.1 need to be changed to reflect this.
§ - Hercules/Eurystheus is mortal father of Mentor (add?) "character/character/citation" **Cited Hercules as mortal father of Mentor**
- ID1594-1595, Hyllus kills Eurystheus, cuts off his head (add "and gives it to Alcmena"?, add Alcmena as a character involved in said event also?) "event/character/citation" **Nevermind**
§ - ID1586-1591, Hercules sons, pursued from Trachis by Eurystheus…Eurystheus and his sons are killed (change to "Hercules' sons…Eurystheus and Hercules' sons are killed"?) "event/character/citation" **Changed to "Hercules' sons, pursued from Trachis by Eurystheus, take sanctuary at the altar of Mercy. Eurystheus goes to war with the Athenians, Eurystheus and his sons are killed.", also quoted Mentor in this event**
§ - ID581, Hylllus gives Eurystheus' severed head with weaving pins, who gouges out the eyes (correct spelling and change to "Hyllus gives Eurystheus' severed head to Alcmena, who gouges out the eyes of the head with weaving-pins"?) "event/place"
2.8.2 - Text mentions the Heraclids at this point, but they are not a group in the database. Should we create a group of the Heraclids?
- ID1596-1597, The sons of Hercules conquer the Peloponnese… (should we change to "The sons of Hercules, the Heraclids, conquer the Peloponnese…"?) "event/character/citation"
- Add event "The Heraclids quit the Peloponnese and dwell in Marathon", place "Marathon"?
§ 2.8.3 - ID1610, Heraclids fight for the Peloponnese… ("Tismaneus" is involved in this event, but he should be corrected in spelling to "Tisamenus") "event/character/citation" **Changed character to "Tisamenus"**
§ 2.8.4 - ID1613-1616, Temenus, Procles,Eurysthenes and Cresphontes draw lots… (add space after comma "..Procles, Eurysthenes…") "event/character/citation"
§ 2.8.5 - ID2686, Deiphontes is spouse of Hyrnetho (add vice versa) "character/character/citation"
§ - ID2693, Merope (3) is spouse of Cresphontes (ibid) "character/character/citation"
§ - ID2694, Merope (3) is spouse of Polyphontes (1) (ibid) "character/character/citation"
§ - Add event "founders of Argos find a toad" (place "Argos") "event/place" **Also cited character: Temenus**
§ - Add event "founders of Lacedaemon find a serpent" (place "Lacedaemon") "event/place" **Also cited characters: Eurysthenes, Procles**
§ - Add event "founders of Messene find a fox" (place "Messene") "event/place" **Also cited character: Cresphontes**
§ - ID1626, Cresphontes murdered with two of his sons, and usurped (change to "usurped by Polyphontes"? Also, cite Polyphontes as well for this event?) "event/character/citation"

BOOK 3
3.1
3.1.1 - ID2638, Telephassa is spouse of Agenor (3) (add vice versa) "character/character/citation"
*§ - ID431, ID608-609, Europa is mortal father of Minos/Sarpedon (2)/Rhadamanthys (Europa must be a mother, is she god mother? If so, these need to be changed) "character/character/citation" **Changed to mortal mother**
§ - ID610, Laodamia (1) is mortal father of Sarpedon (2) (Laodamia must be a mother, change) "character/character/citation" **Changed to mortal mother**
* - ID1796, Poseidon is god father of Belus (not explicitly mentioned in 3.1.1, but Frazer's footnote relates this to Apollod. 2.1.4 which this record also does, should we keep this citation anyways?) "character/character/citation"
§ - ID1635-1640, Agenor sends his sons , andTelephassa, and Thasus… (correct spelling and punctuation to "Agenor sends his sons, Telephassa, and Thasus…"?) "event/character/citation"
§ - ID606, ibid "event/place"
§ - ID1642, Cilix, unable to find Europa, settle in Cilicia (change spelling to "settles in Cilicia"?) "event/character/citation"
§ - ID608, ibid "event/place"
§ - Add event "Belus reigns over the Egyptians" (character "Belus"?) "event/character/citation" **Cited ID779, Belus rules over Egypt (Apollod. 2.1.4) in 3.1.1 as well**
§ - ibid (place "Egypt"?) "event/place"
§ - Event "Agenor rules Phoenicia" belongs in "event/place" with place "Phoenicia"

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
)

Duplicated half-open tag caused a display bug in the menu. Fixed it at LW's request. Also some discussion of how the PDF handbook should be produced in future.

Ongoing: working my way through JavaScript.

Received invoiced payment from HB (1st Qtr 2011); deposited cheque; obtained receipt, filed paperwork/receipt in HCMC records; sent
email confirmation to MDH,SA.

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

Preparing for meetings and workshops.