Properties

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.