New WoS and ZXDB

This is the place for general discussion and updates about the ZXDB Database. This forum is not specific to Spectrum Computing.

Moderator: druellan

Post Reply
User avatar
PeterJ
Site Admin
Posts: 6858
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: New WoS and ZXDB

Post by PeterJ »

moroz1999 wrote: Thu Jun 18, 2020 2:23 pm And I cannot find our Crystal Kingdom Dizzy 2017 in WoS database for some reason.
Any titles added with new infoseek IDs have been removed.
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New WoS and ZXDB

Post by Einar Saukas »

moroz1999 wrote: Thu Jun 18, 2020 2:23 pm https://worldofspectrum.org/archive/pub ... /moroz1999 - they display me from Russia, but this was long ago corrected to Estonia in ZXDB.
Also a wrong label Death Moroz is credited to me (also fixed in ZXDB a long time ago).
Because the list of publishers in new WoS came mostly from Martijn's WoS internal file publishers.dat, that Lee Fogarty imported back in May 2017 and never fixed. Although the rest of the game data came from ZXDB.

moroz1999 wrote: Thu Jun 18, 2020 2:23 pmAnd I cannot find our Crystal Kingdom Dizzy 2017 in WoS database for some reason.
Because all titles with IDs above 28187 (the last ID added to old WoS) were removed, in an obvious attempt to disguise that new WoS is using ZXDB.
Ralf
Rick Dangerous
Posts: 2279
Joined: Mon Nov 13, 2017 11:59 am
Location: Poland

Re: New WoS and ZXDB

Post by Ralf »

Hahahahaha :lol:
I guess you shoud take it as a compliment and some proof that you were right, Einar.

Actually I expected something like that. That they will be too lazy to search on internet for all titles released after 2013 and will eventually use data from ZXDB.

But i didn't expect that they will turn stealth mode on and will use ZXDB pretending that they are not using it :lol: But will do it in a lame way so it will be obvious that they are using it. And they will cut out all new data.

And we have now a new WOS. which runs slower and lacks a lot of content present on old version but has some fancy animations instead.

Well, I'm really happy that we have Spectrum Computing :)
catmeows
Manic Miner
Posts: 711
Joined: Tue May 28, 2019 12:02 pm
Location: Prague

Re: New WoS and ZXDB

Post by catmeows »

While I understand amusement and satisfaction coming from the fact WOS is using ZXDB, I'm little bit sad because of hostility developing between WOS and SC :shock:
Proud owner of Didaktik M
User avatar
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

Re: New WoS and ZXDB

Post by moroz1999 »

Einar Saukas wrote: Thu Jun 18, 2020 2:39 pm Because the list of publishers in new WoS came mostly from Martijn's WoS internal file publishers.dat, that Lee Fogarty imported back in May 2017 and never fixed. Although the rest of the game data came from ZXDB.

Because all titles with IDs above 28187 (the last ID added to old WoS) were removed, in an obvious attempt to disguise that new WoS is using ZXDB.
Thank you! Sounds really like true.
User avatar
PeterJ
Site Admin
Posts: 6858
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: New WoS and ZXDB

Post by PeterJ »

Hi [mention]catmeows[/mention],

I can understand your comments, but I don't think that anything said here is causing any additional hostility. People have long memories and the hurt for some is strong, which is why things come up in conversations like this. I would actually say in general that comments have been very measured and restrained.

It's more just amazement, rather than anything else.
User avatar
PeterJ
Site Admin
Posts: 6858
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: New WoS and ZXDB

Post by PeterJ »

How things should look:

Image

And, as displayed on WoS:

Image
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New WoS and ZXDB

Post by Einar Saukas »

catmeows wrote: Thu Jun 18, 2020 3:10 pm I'm little bit sad because of hostility developing between WOS and SC :shock:
Me too. This is what I tried to avoid for the last 4 years.

Excluding the times I returned to WoS forum to provide proof against false accusations, the last time I visited WoS forum voluntarily it was to try to convince people from both sites to work together on CSSCGC 2018.

As recently as last week, when I was falsely accused once more, I was still offering to help them with their database, literally "for the good of WoS".

I cannot even imagine what else I could have done to avoid this situation.
User avatar
Pegaz
Dynamite Dan
Posts: 1209
Joined: Mon Nov 13, 2017 1:44 pm

Re: New WoS and ZXDB

Post by Pegaz »

Einar Saukas wrote: Thu Jun 18, 2020 3:45 pm
catmeows wrote: Thu Jun 18, 2020 3:10 pm I'm little bit sad because of hostility developing between WOS and SC :shock:
Me too. This is what I tried to avoid for the last 4 years.

Excluding the times I returned to WoS forum to provide proof against false accusations, the last time I visited WoS forum voluntarily it was to try to convince people from both sites to work together on CSSCGC 2018.

As recently as last week, when I was falsely accused once more, I was still offering to help them with their database, literally "for the good of WoS".

I cannot even imagine what else I could have done to avoid this situation.
You've already done everything you can.
Unfortunately, it takes two to tango...
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New WoS and ZXDB

Post by Einar Saukas »

PeterJ wrote: Thu Jun 18, 2020 3:42 pm How things should look:

Image

And, as displayed on WoS:

Image
OK, let's make a proper comparison between these comments in new WoS and ZXDB. We will only consider titles currently available in new WoS (excluding IDs above 28187).
  • Old WoS: 2988 of these titles have comments
  • ZXDB 2018: 4559 of these titles have comments
  • New WoS: 4559 of these titles have comments
Hey, what a coincidence! But are they really identical? Well, let's take a closer look.

First, download all comments from new WoS. Open new WoS software page and click on "EXPORT CSV (ALL)". Now if you have MySQL/MariaDB, you can use this script to import this data into a database:

Code: Select all

create table x_newwos (
  rows0 varchar(100),
  id int(11) not null primary key,
  title varchar(500),
  slug varchar(500),
  no_players varchar(100),
  turn_type varchar(100),
  entry_type varchar(100),
  availability varchar(100),
  comments varchar(5000),
  is_x_rated varchar(100),
  is_crap varchar(100),
  clone_of varchar(100),
  old_id int(11),
  title_publisher varchar(500),
  publishers varchar(500),
  all_publishers varchar(500),
  entry_groups varchar(500),
  distribution_status_type varchar(500),
  display_image varchar(500)
);

load data local infile 'software-20200616.csv' 
  into table x_newwos character set utf8 
  fields terminated by ',' optionally enclosed by '"' 
  lines terminated by '\n' ignore 1 lines;
Next, let's download ZXDB 2018 version 1.08 from github and run it into the same database.

Now let's compare them. If you know SQL, making a comparison is very easy:

Code: Select all

select e.id,e.comments,x.comments from entries e 
inner join x_newwos x on e.id = x.old_id
where coalesce(e.comments,'') <> coalesce(x.comments,'');
There are only 15 differences out of 4559 comments!

But wait, aren't they supposed to be all identical?

Well... it turns out the backslash character from ZXDB is missing from new WoS. So let's try again, this time excluding backslashes:

Code: Select all

select e.id,e.comments,x.comments from entries e 
inner join x_newwos x on e.id = x.old_id
where replace(coalesce(e.comments,''),'\\ ',' ') <> coalesce(x.comments,'');
Bingo! Absolutely all 4559 comments are perfectly identical. What a fantastic coincidence.

Out of curiosity, the same comparison between old WoS and new WoS returns 2572 differences in comments :)
Last edited by Einar Saukas on Thu Jun 18, 2020 4:31 pm, edited 1 time in total.
akeley
Dynamite Dan
Posts: 1025
Joined: Sat Feb 01, 2020 5:47 pm

Re: New WoS and ZXDB

Post by akeley »

What strikes me is how completely unnecessary all this actually is. All they had to do is say something to the tune of "ah, let the bygones be bygones, ZXDB is an awesome piece of work and we will use it from now on". Problem solved.
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New WoS and ZXDB

Post by Einar Saukas »

akeley wrote: Thu Jun 18, 2020 4:31 pm What strikes me is how completely unnecessary all this actually is. All they had to do is say something to the tune of "ah, let the bygones be bygones, ZXDB is an awesome piece of work and we will use it from now on". Problem solved.
Exactly. Or just claim "I really had a much better database (that nobody ever saw) but the dog ate it so let's use yours".
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New WoS and ZXDB

Post by Einar Saukas »

BTW about my last test, it was based on a list downloaded 2 days ago (June 16, 2020) from new WoS.

If we run the same test using a list downloaded yesterday or today, there will be 1 difference, because this comment was edited yesterday:

https://worldofspectrum.org/archive/sof ... rs-persson

This page says "Updated On: June 17, 2020". It seems they have now started to update new WoS manually.

This is even more bizarre. Between the stupidity of redoing years of work already done, or the immorality of using ZXDB in disguise while criticizing it... they have chosen to do BOTH?
User avatar
PeterJ
Site Admin
Posts: 6858
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: New WoS and ZXDB

Post by PeterJ »

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...

Image
User avatar
Pegaz
Dynamite Dan
Posts: 1209
Joined: Mon Nov 13, 2017 1:44 pm

Re: New WoS and ZXDB

Post by Pegaz »

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...

Image
"Always the two Sith Lords there are, a master and aprentice."

Only in this case, the aprentice is the older one...
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New WoS and ZXDB

Post by Einar Saukas »

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...

Image
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.
User avatar
Mike Davies
Microbot
Posts: 137
Joined: Mon Nov 13, 2017 10:11 am

Re: New WoS and ZXDB

Post by Mike Davies »

Einar Saukas wrote: Thu Jun 18, 2020 12:03 pm It turns out new WoS is using ZXDB! More precisely ZXDB version 1.08 released on September 2, 2018.
This is a remarkable piece of investigative work. Exemplary.

I guess WoS now needs to acknowledge that it's powered by ZXDB, like every site using data from it. If that can be done in good faith - to acknowledge that the many datafixes and improvements in data quality -- that's going to be beneficial to the community as a whole. ZXDB is now the primary source of archival data on ZX Spectrum games.

Congratulations, Einar, you have succeeded in quite the impossible task, with your infinite patience and humility.
namco
Manic Miner
Posts: 247
Joined: Mon Dec 04, 2017 8:55 pm

Re: New WoS and ZXDB

Post by namco »

Essentially this boils down to:

Once a c*nt, always a c*nt

I noticed the change over earlier in the week and wondered how long before someone would mention it here.

I've been having a look at it on and off through the week and was met with slowdown to load the site, 500 server errors, lack of new games (already mentioned here and it's unsurprising) and ajax-type loading on the db inputs being occasionally slow.

WoS as a website has lost its charm.

As I mentioned on Twitter:
End of an era.

Additionally, my Spectrum YT videos used to reference WoS via link (apart from the newer games) as I didn't have an issue with the site itself - nostalgia. They are from now, and in the future, referencing this site.

[edited last line for clarification purposes]
User avatar
ketmar
Manic Miner
Posts: 613
Joined: Tue Jun 16, 2020 5:25 pm
Location: Ukraine

Re: New WoS and ZXDB

Post by ketmar »

offtopic ;-)
Spoiler
i really want to say "thank you alot!" to Lee and others in WoS website team. because i found this site (and ZXDB) only after they rolled up "new WoS", which can't even work with javascript turned off. really great work there. i stoped caring about WoS forums after they switched the engine (their new forum engine is awful), and only used WoS as a games database. (tbh, i was never an active forum participant at the first place.) and when they made the site unusable for me (i'm browsing with JS off), i finally decided to search for a new home. and found ZXDB and Spectrum Computing. with alot of names i remember and respect here. yay. it somewhat renewed my interest in Speccy (you can see that i made quite alot of bugfix commits to Zymosis after 4+ year hiatus, for example).

so, thanks to WoS team for breaking WoS so bad that i had to look for a new home! and thank you all for ZXDB, Spectrum Computing, and all other Speccy-related work!
User avatar
RMartins
Manic Miner
Posts: 776
Joined: Thu Nov 16, 2017 3:26 pm

Re: New WoS and ZXDB

Post by RMartins »

I'm lost for words, to even comment this. :?

I just wish I could have access to my account to fix my threads in WOS to have the image links corrected.
But my account is still locked.

Apparently, they don't even value the content they have in there.
User avatar
djnzx48
Manic Miner
Posts: 729
Joined: Wed Dec 06, 2017 2:13 am
Location: New Zealand

Re: New WoS and ZXDB

Post by djnzx48 »

Einar Saukas wrote: Thu Jun 18, 2020 5:08 pm BTW about my last test, it was based on a list downloaded 2 days ago (June 16, 2020) from new WoS.

If we run the same test using a list downloaded yesterday or today, there will be 1 difference, because this comment was edited yesterday:

https://worldofspectrum.org/archive/sof ... rs-persson

This page says "Updated On: June 17, 2020". It seems they have now started to update new WoS manually.

This is even more bizarre. Between the stupidity of redoing years of work already done, or the immorality of using ZXDB in disguise while criticizing it... they have chosen to do BOTH?
I see they have a 'Change Request' form on each page for people to suggest corrections. Perhaps that was just a one-off addition from someone visiting the site?
User avatar
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

Re: New WoS and ZXDB

Post by moroz1999 »

djnzx48 wrote: Fri Jun 19, 2020 1:59 am I see they have a 'Change Request' form on each page for people to suggest corrections. Perhaps that was just a one-off addition from someone visiting the site?
Yep, this actually works. I've used it yesterday to suggest a country change for myself, and admin have changed it.
User avatar
cha05e90
Dizzy
Posts: 66
Joined: Sun Nov 19, 2017 7:02 pm

Re: New WoS and ZXDB

Post by cha05e90 »

moroz1999 wrote: Fri Jun 19, 2020 7:20 am Yep, this actually works. I've used it yesterday to suggest a country change for myself, and admin have changed it.
Ah, ok. That makes sense. So they want us to bring WoS DB to ZXDB 2020 state. May last some months/years, but it's not impossible. Especially when you copy and paste stuff from Spectrum Computing to WoS' change requests... :twisted:
48K/+/+/+/+128K/Vega/Next&80/81&88
User avatar
Vampyre
Manic Miner
Posts: 833
Joined: Wed Nov 15, 2017 2:51 pm
Contact:

Re: New WoS and ZXDB

Post by Vampyre »

cha05e90 wrote: Fri Jun 19, 2020 7:31 am
moroz1999 wrote: Fri Jun 19, 2020 7:20 am Yep, this actually works. I've used it yesterday to suggest a country change for myself, and admin have changed it.
Ah, ok. That makes sense. So they want us to bring WoS DB to ZXDB 2020 state. May last some months/years, but it's not impossible. Especially when you copy and paste stuff from Spectrum Computing to WoS' change requests... :twisted:
Yup, like you say, certainly not impossible. The only thing that makes it unlikely for me is Einar mentioning the new WOS IDs he added that suddenly appeared in new WOS (and I take it have now disappeared?). The likelihood of adding these, in the exact same order Einar has to generate the same ID, is highly unlikely without an import. Plus - given the comments made by WOS admins in regards to ZXDB over the years - why would they give a damn what ID's ZXDB is using? So I think this data is an import - which once you know the structures of both databases shouldn't be that hard to do, given time.
ZX Spectrum Reviews REST API: http://zxspectrumreviews.co.uk/
User avatar
ketmar
Manic Miner
Posts: 613
Joined: Tue Jun 16, 2020 5:25 pm
Location: Ukraine

Re: New WoS and ZXDB

Post by ketmar »

i wonder if WoS will start adding new software in random order now. and will they blame ZXDB for being "desynced" then?

their stupid ego games only makes things worse for everyone. i still hope that they will come to their senses, and will simply import the whole ZXDB (with proper credits). a snowball's chance in hell, i know. but i still hope.
Post Reply