HCMC Blogs

Met with MR, along with JA, GN and CL, and RS on Skype, to discuss the provisioning and admin of VMS for the INKE project. Made good progress; my summary sent out to everyone by email.

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

§ 1.9.21 - ID209, Lycurgus (2) is mortal father of Opheltes (not mentioned in 1.9.21) **Moved to 1.9.14**
§ - Add group "Harpies"? Harpys/Nicothoe/Aellopus and Ocypete/Ocythoe/Ocypode are part of this group **Added all to group "Harpy", created new character "Harpys", edited character "Aeollpusm" to correct spelling, "Aeollopus" instead**
- ID650-ID654, Zetes and Calais chase the Harpies (Aello and Aellopusm are mentioned, but not found in text, instead put Harpys/Nicothoe/Aellopus and Ocypete/Ocythoe/Ocypode?) "event/character/citation" **
- Important note: Apollodorus states in 1.2.6 that Thaumas and Electra had the Harpies, Aello and Ocypete. In 1.9.21 he uses the above names for them, which in the case of Aello he calls her Aellopus in this section. Is this a translation choice or what is happening here? Also there are two entries for "Aello" in the database, without a (#) after their names, please check what's happening here as well. The first has a note in the character tab stating "aka Nicothoe,Aellopus", while the 2nd has nothing attributed. Ought we to delete the 2nd Aello?
- ID655, Remaining Harpy flees (Ocypete is mentioned, add aliases Ocythoe and Ocypode?) "event/character/citation"
- ID656, Harpy falls from exhaustion (Ocypete is mentioned, add aliases Ocythoe and Ocypode?) "event/character/citation"
* - ID227, Harpy falls from exhaustion (Echinadian Islands is mentioned, but these are also known as Strophades Islands, add this place also?) "event/place"
§ 1.9.22 - ID210, Autolycus is mortal father of Polymede (not mentioned in 1.9.22)
§ 1.9.23 - ID211, Hagnias is mortal father of Tiphys (not mentioned in 1.9.23)
- ID234/1252, Apsyrtus is son of Idyia and Aeetes (not specifically mentioned although he is the brother of Medea, is it o.k. to assume this?) **Nevermind**
- Add event where Jason yokes the wild fire-breathing bulls in order to get the fleece? **Nevermind**
§ - ID233, Medea gets Jason the Golden Fleece (misspelled "Colchis" as "Cholcis", change?)
- Important note: There is a place in the database called "Cholcis" with GIS co-ordinates, and its note states "aka Sebastopolis". There are no events or characters related to this place in the text, but a search of "Cholcis" on perseus brings it up once in Ovid's Amores, and nowhere in Apollodorus is it cited.
§ 1.9.24 - ID676, An island speaks to the Argonauts (the text says the ship spoke to them, change?) **Changed to "The Argo speaks to the Argonauts as they pass the Apsyrtides Islands**
§ - ID684/685, Butes swims off … settles him on Lilybaeum (not mentioned in 1.9.24, belongs in 1.9.25)
1.9.25 - Add group "Phaeacians"? (Alcinous is mentioned as being king) **Nevermind**
§ 1.9.26 - ID703, Pelias forces Aeson to kill himself … kills Promachus (not mentioned in 1.9.26, belongs in 1.9.27)
§ - Add event "Argo sails past" and place "Euboea and Locris"? **Added Euboea and Locris to places for new event "Argo sails between"**
1.9.27 - ok
1.9.28 - ID1296/1881, Perses (2) is the son of Perseis and the Sun (Helios) (not mentioned in 1.9.28) **Nevermind this is inferred by being the brother of Aeetes**

BOOK 2
2.1
§ 2.1.1 - ID240, Piren is mortal father of Phoroneus (not mentioned in 2.1.1) "character/character/citation" **Deleted**
§ 2.1.2 - ID738-744, Argos rules the Peloponnese …, Argos marries and has children (Should it not be spelled "Argus"?) "event/character/citation" **Changed to Argus (2)**
2.1.3 - Argus (3) is mortal father of Iasus (1) ("Argus and Ismene…had a son Iasus", add?) "character/character/citation" **Nevermind, already done**
§ - Piren is mortal father of Io ("Hesiod and Acusilaus say that she was a daughter of Piren", add?) "character/character/citation"
- Zeus is god father of Epaphus ("Zeus seduced [Io]…" is this enough to say that he was the father?) "character/character/citation"
§ - ID2695, Telegonus (3) is spouse of Io (add vice versa?) "character/character/citation"
§ - ID756-759, Hermes rescues the cow Ino on Zeus' orders… (change "Ino" to "Io"?) "event/character/citation"
§ - ID769, Io goes looking…finds him in Syra with the wife of king Byblus (change "Syra" to "Syria"?) "event/character/citation"
§ - ID279, Hermes rescues the cow Ino on Zeus' orders… (change "Ino" to "Io"?) "event/place"
§ - ID289, Io goes looking…finds him in Syra with the wife of king Byblus (change "Syra" to "Syria"?) "event/place"
- Important note: the character "Wife of the king of Byblus" is found cited solely in the "character/character/citation" tab of Apollod. 2.1.3 as the foster mother of Epaphus (ID2329). Ought we to keep this character or delete? Specifically, is she important enough (probably not since she's not cited in any event or group) to warrant being in the database? Please check this, I'll keep her in until I hear anything further.

Added more views to the PostgreSQL database to generate lists of values that are present in the census data, for the advanced search form.

vOriginsUsed - used for the "Birthplace", "Birthplace of Father", and "Birthplace of Mother" fields:

CREATE VIEW "vOriginsUsed" AS

SELECT origin_id, origin

FROM "origins"  

WHERE origin IS NOT NULL   

AND origins.origin_id IN (
    SELECT "vCensus1881".birthplace_id FROM "vCensus1881"
    UNION
    SELECT "vCensus1881".birthplace_father_id FROM "vCensus1881"
    UNION
    SELECT "vCensus1891".birthplace_id FROM "vCensus1891"
    UNION
    SELECT "vCensus1891".birthplace_father_id FROM "vCensus1891"
    UNION
    SELECT "vCensus1891".birthplace_mother_id FROM "vCensus1891"
    UNION
    SELECT "vCensus1901".birthplace_id FROM "vCensus1901"
    UNION
    SELECT "vCensus1911".birthplace_id FROM "vCensus1911"
    )  
    
ORDER BY upper(origin)

The origins view is pretty slow because of the large number of SELECT subqueries.

vNationalitiesUsed - used for the "Nationality" search field:

CREATE VIEW "vNationalitiesUsed" AS

SELECT nationality_id, nationality

FROM "nationalities"

WHERE nationality IS NOT NULL

AND nationalities.nationality_id IN (
    SELECT "vCensus1901".nationality_id FROM "vCensus1901"
    UNION
    SELECT "vCensus1911".nationality_id FROM "vCensus1911"
)

ORDER BY upper(nationality)

vRacesUsed - used for the "Race/Ethnic Origin" search field:

CREATE VIEW "vRacesUsed" AS

SELECT race_id, race_name

FROM "races"

WHERE race_name IS NOT NULL

AND races.race_id IN (
    SELECT "vCensus1901".race_id FROM "vCensus1901"
    UNION
    SELECT "vCensus1911".race_id FROM "vCensus1911"
)

ORDER BY upper(race_name)

vBandsUsed - used for the "First Nations Band" search field:

CREATE VIEW "vBandsUsed" AS

SELECT band_id, band

FROM "band"

WHERE band IS NOT NULL

AND band.band_id IN (
    SELECT "vCensus1881".band_id FROM "vCensus1881"
    UNION
    SELECT "vCensus1891".band_id FROM "vCensus1891"
)

ORDER BY upper(band)

vMotherTonguesUsed - used for the "Mother Tongue", "First Language Spoken", and "Second Language Spoken" search fields:

CREATE VIEW "vMotherTonguesUsed" AS

SELECT mother_tongue_id, mother_tongue

FROM "mother_tongues"

WHERE mother_tongue IS NOT NULL

AND mother_tongues.mother_tongue_id IN (
    SELECT "vCensus1901".mother_tongue_id FROM "vCensus1901"
    UNION
    SELECT "vCensus1911".first_language_id FROM "vCensus1911"
    UNION
    SELECT "vCensus1911".second_language_id FROM "vCensus1911"
)

ORDER BY upper(mother_tongue)

Minor updates on DR's instructions. Also an email discussion about the future move to CASCADE; DR would like to be able to edit the site directly.

Met with CC and CH to complete the "project lite web" document, which we've now submitted, along with a new version of the navigation plan, to AR.

CREATE VIEW "vOccupationsUsed" AS 

SELECT DISTINCT ON (upper(occupation_description)) occupation_description AS occupation_description, occupation_id, occupation_code

FROM "occupation"

WHERE 
    occupation_description IS NOT NULL 
    
    AND 
    (
        (occupation.occupation_code IN (SELECT "vOccupations1881".code FROM "vOccupations1881" WHERE "vOccupations1881".count1881 > 0))
        OR
        (occupation.occupation_code IN (SELECT "vOccupations1891".code FROM "vOccupations1891" WHERE "vOccupations1891".count1891 > 0))
        OR
        (occupation.occupation_code IN (SELECT "vOccupations1901".code FROM "vOccupations1901" WHERE "vOccupations1901".count1901 > 0))
        OR
        (occupation.occupation_code IN (SELECT "vOccupations1911".code FROM "vOccupations1911" WHERE "vOccupations1911".count1911 > 0))
    )

ORDER BY upper(occupation_description)

The usual form-filling and photocopying. Mailed in the package and also posted a zip copy as backup.

Following my own instructions in the previous post, got my working build environment set up on the work computer, and ran a test build. I'm now running another build, saving the output to a file so I can examine the errors coming out of tests.