PeterJ wrote: ↑Thu Jun 18, 2020 5:19 pm
Having read through all that Einar has posted, and links to various comments, this from the 6th June is the one that makes me smile the most...
It's interesting to read how much hard work they have done to fix thousands and thousands of errors. In particular, they mentioned fixing the order of certain releases (some re-releases were shown as released first, and vice-versa). For instance, it was recently mentioned
here and
here.
It turns out that's easy to check too!
Let's take a look at the same list of titles downloaded from new WoS. Columns "publishers" and "all_publishers" are just garbage (they are either empty or just contain the word "Array"). But it's possible to obtain original publisher, together with original title, from column "title_publisher".
This column is inconsistent for programs with unknown publishers. In Earthbound, this column contains "Earthbound ()", but in Spectrofon this column is empty. No problem, that's easy to handle. The tricky part is supporting games originally co-published by different publishers:
Code: Select all
select * from (
select e.id,e.title,group_concat(b.name order by p.publisher_seq separator ',') as pub
from entries e left join publishers p on e.id = p.entry_id and p.release_seq = 0
left join labels b on b.id = p.label_id group by e.id) as z
inner join x_newwos x on z.id = x.old_id
where x.title_publisher <> concat(z.title,' (',coalesce(z.pub,''),')')
and not (title_publisher = '' and pub is null);
We will get 218 differences. That's a lot...
But wait! Do you remember I mentioned that the list of publishers in new WoS came mostly from old WoS, rest of game content came from ZXDB? It turns out a few publishers had slightly different names in old WoS. For instance "U.S. Gold Ltd" in ZXDB was "US Gold Ltd" in old WoS, "Unique Computer Services [1]" in ZXDB was "Unique [1]" in old WoS, etc. Let's try this query again, this time applying these conversions:
Code: Select all
select * from (
select e.id,e.title,group_concat(b.name order by p.publisher_seq separator ',') as pub
from entries e left join publishers p on e.id = p.entry_id and p.release_seq = 0
left join labels b on b.id = p.label_id group by e.id) as z
inner join x_newwos x on z.id = x.old_id
where x.title_publisher <> replace(replace(replace(replace(replace(replace(replace(
concat(z.title,' (',coalesce(z.pub,''),')'),
'U.S. Gold Ltd','US Gold Ltd'),
'Unique Computer Services [1]','Unique [1]'),
'PowerSoftware','Powersoftware'),
'CheetahSoft Ltd','Cheetahsoft Ltd'),
'MmcM','MMCM'),
'(Raf,','(RAF,'),
'(Raf)','(RAF)')
and not (title_publisher = '' and pub is null);
Now we will get only 14 differences in original publishers. That's better...
But wait! What if they messed up in the order of certain co-publishers when extracting this information from ZXDB? Even co-publishers of the same release were stored in a specific order in old WoS, but perhaps they didn't know it. Let's try to keep exactly the same original co-publishers, and just switch the order they are listed:
Code: Select all
select * from (
select e.id,e.title,group_concat(b.name order by p.publisher_seq separator ',') as pub
from entries e left join publishers p on e.id = p.entry_id and p.release_seq = 0
left join labels b on b.id = p.label_id group by e.id) as z
inner join x_newwos x on z.id = x.old_id
where x.title_publisher <> replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(concat(z.title,' (',coalesce(z.pub,''),')'),
'U.S. Gold Ltd','US Gold Ltd'),
'Unique Computer Services [1]','Unique [1]'),
'PowerSoftware','Powersoftware'),
'CheetahSoft Ltd','Cheetahsoft Ltd'),
'MmcM','MMCM'),
'(Raf,','(RAF,'),
'(Raf)','(RAF)'),
'CityAceE,AlexUzer,Grand','AlexUzer,CityAceE,Grand'),
'Digital Prawn,Einar Saukas,Dr BEEP','Einar Saukas,Digital Prawn,Dr BEEP'),
'Einar Saukas,Dr BEEP,Digital Prawn','Einar Saukas,Digital Prawn,Dr BEEP'),
'Infotek Team,Alliance Team,Optimal Group','Alliance Team,Infotek Team,Optimal Group'),
'J. Korytkowski,A.J. Lipski,A. Ferenc','A. Ferenc,A.J. Lipski,J. Korytkowski'),
'Kiwi,RetroCoder,Sadako,Lee_Dc','Kiwi,Lee_Dc,RetroCoder,Sadako'),
'Marek Radkowski,Janusz Zietal,Jarek Druzbinski','Janusz Zietal,Jarek Druzbinski,Marek Radkowski'),
'Mark Woodmass,Paul Harrison,Dean Hickingbottom','Dean Hickingbottom,Mark Woodmass,Paul Harrison'),
'O. Hohlov,K. Afendikov,V. Rubtsov','K. Afendikov,O. Hohlov,V. Rubtsov'),
'Richard Swann,Miles Kinloch,ewgf','Miles Kinloch,Richard Swann,ewgf'),
'Simbols,Milytia,Triebkraft','Milytia,Simbols,Triebkraft'),
'Tankard,Hammer,Kamikaze','Hammer,Kamikaze,Tankard'),
'Triebkraft,4th Dimension [2],bfox','4th Dimension [2],Triebkraft,bfox'),
'Tygrys,Cat-Man,Voyager','Cat-Man,Tygrys,Voyager')
and not (title_publisher = '' and pub is null);
Bingo! Absolutely all 24368 titles have exactly the same title and original publishers in both new WoS and ZXDB 2018. Yet another fantastic coincidence.