New Database Model ZXDB

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

Moderator: druellan

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

Re: New Database Model ZXDB

Post by Einar Saukas »

Another ZXDB update is already available!

It mostly contains recently released titles. Thanks [mention]R-Tape[/mention]!
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

A very large ZXDB update is now available!

ZXDB is now merged with ZXSR!!! It means that, along with the full content of Martijn's original WoS and Jim Grimwood's SPOT/SPEX, now ZXDB also includes the full content of Chris Bourne's ZX Spectrum Reviews. The ZXDB description at the official site was updated to credit ZXSR properly. Many thanks to Chris for working with us on this integration! From now on, ZXSR will continue to get updated and all changes incorporated into ZXDB. Likewise, all ZXDB updates will be reflected back into ZXSR.

Besides ZXSR, this update also includes new titles (thanks [mention]R-Tape[/mention]!), new hires inlays (thanks [mention]pavero[/mention]!), newly indexed Load'n'Run covertapes (thanks [mention]wallyweek[/mention]!), new game credits (thanks [mention]druellan[/mention]!), and many other updates. Also thanks everyone else that's always contributing with additional information for the database!
User avatar
PeterJ
Site Admin
Posts: 6858
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: New Database Model ZXDB

Post by PeterJ »

Thanks [mention]Einar Saukas[/mention],

There will be a short delay to applying the update as I need to adjust php.ini for the significantly larger file size.
User avatar
Vampyre
Manic Miner
Posts: 833
Joined: Wed Nov 15, 2017 2:51 pm
Contact:

Re: New Database Model ZXDB

Post by Vampyre »

Fantastic news and can I also take this opportunity to thank Einar for all of his help and great ideas in tidying up ZXSR's data. He's been such an inspiration how dedicated he is to making ZXDB as good as it could possibly be that I couldn't not do the same with ZXSR.

Secondly, it's a huge relief off my mind that the data is now in the public domain in such a seasoned database. I've worried, as I've got older, that should the worst happen that all the work over the last 20 years would disappear. I'll still be adding reviews to it, currently in the process of adding ZX Computing, and there's still more to come - it's a long way from being complete!
ZX Spectrum Reviews REST API: http://zxspectrumreviews.co.uk/
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

Vampyre wrote: Tue Aug 11, 2020 8:29 pm Fantastic news and can I also take this opportunity to thank Einar for all of his help and great ideas in tidying up ZXSR's data. He's been such an inspiration how dedicated he is to making ZXDB as good as it could possibly be that I couldn't not do the same with ZXSR.

Secondly, it's a huge relief off my mind that the data is now in the public domain in such a seasoned database. I've worried, as I've got older, that should the worst happen that all the work over the last 20 years would disappear. I'll still be adding reviews to it, currently in the process of adding ZX Computing, and there's still more to come - it's a long way from being complete!
Thank you for all your years of effort on putting ZXSR together, you have been doing it much longer than us! :)

Also many thanks for your approval and hard work on this integration! This kind of collaboration and joint effort benefits everyone, it's nice to have the Spectrum community working together like this!
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

On a more technical note, column "magrefs.score" from ZXDB will be removed in the next update. The reason is, it makes no sense to duplicate overall review scores inside a ZXDB table, when the entire ZXSR content is now directly available inside ZXDB.

This should be a fairly simple change. Instead of this:

Code: Select all

SELECT r.id, r.score FROM magrefs r
Your website should use this:

Code: Select all

SELECT r.id, s.score FROM magrefs r LEFT JOIN zxsr_scores s ON r.id=s.magref_id AND s.is_overall=1
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

A new ZXDB update is available! This version contains:
  • New typed instructions from [mention]pavero[/mention], as usual. He's tireless, I cannot even remember last time a ZXDB update did not contain his contributions :)
  • More bugfixes and updates from [mention]druellan[/mention]. This time he finished validating and updating every single post from the Cover Artists thread, that contains almost 500 posts!
  • The integration between ZXDB and ZXSR is now complete. Many thanks again to [mention]Vampyre[/mention] for this incredible amount of new content!
On a more technical note, there's now a new table in ZXDB called "notes". It combines all kinds of text information about each entry: comments, known errors and hardware blurb from ZXDB, awards and info from ZXSR. Now we can have multiple comments for each game, also specific information like music references can be stored properly instead of mixed within other comments. Columns "comments", "spot_comments", "known_errors" and "hardware_blurbs" from ZXDB table "entries" are now empty and they will be removed in the next release. If your website or system is using ZXDB, the easiest way to apply this change is to upgrade ZXDB first (your system will continue to work although comments will be blank), then update your queries to use table "notes".

For each title, there could be (at most) 1 "known errors" note, (at most) 1 "hardware blurb" note, and multiple "comments" note in table "notes". Technically it means a query like this:

Code: Select all

select e.id, e.title, e.comments, e.hardware_blurb, e.known_errors from entries e where e.id = ?
corresponds now to a query like this:

Code: Select all

select e.id, e.title, 
GROUP_CONCAT(c.text ORDER BY c.text SEPARATOR '\n\n') as comments,
h.text as hardware_blurb, 
r.text as known_errors
from entries e 
left join notes c on e.id = c.entry_id and c.notetype_id = 'C'
left join notes h on e.id = h.entry_id and h.notetype_id = 'H'
left join notes r on e.id = r.entry_id and r.notetype_id = 'R'
where e.id = ?
Although it's much easier to simply obtain all rows from table "notes" corresponding to a certain title, and process them directly:

Code: Select all

select id, entry_id, notetype_id, section, text from notes where entry_id = ?
Current note types are Award, Comment, Known Errors, Hardware Blurb, and Music Reference. If anybody has further questions or need help updating their system, just let me know!
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

Another ZXDB update is available!

The main database improvement this time is support for magazine supplements. Technically table "issues" now contains a new column "supplement" that stores the magazine supplement name, and another column "parent_id" that references the parent magazine issue containing this supplement.

Of course this release also includes new hires scans provided by [mention]pavero[/mention] :)
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

As part of our ongoing work to improve ZXDB, we will make the following changes in the next ZXDB update:

  • Column "labels.comments" will be removed. To obtain all comments related to a certain person or company, please use table "notes" instead. The advantage is that we can now store different kinds of comments, for instance awards like this.

    Technically instead of this:

    Code: Select all

    select comments from labels where id = ?
    use this:

    Code: Select all

    select * from notes where label_id = ?

  • Column "magrefs.is_supplement" will also be removed. All supplement pages are now stored under a separate issue ID. The advantage is that we can now store proper details about each supplement, for instance like this.

    Technically instead of this:

    Code: Select all

    select * from magrefs r inner join issues i on r.issue_id = i.id where r.is_supplement = 1
    use this:

    Code: Select all

    select * from magrefs r inner join issues i on r.issue_id = i.id where i.supplement is not null

  • All columns "idiom_id" will be replaced with "language_id", referencing table "languages" instead of "idioms". This is actually a bugfix that was reported here (thanks [mention]Rorthron[/mention]!)

    Technically instead of this:

    Code: Select all

    select * from entries e left join idioms i where e.idiom_id = i.id
    use this:

    Code: Select all

    select * from entries e left join languages i where e.language_id = i.id

All these new tables and columns are already available in the current ZXDB version. Therefore if you are running a website based on ZXDB, you can already make these changes right now, there's no need to wait for the next ZXDB update.
User avatar
R-Tape
Site Admin
Posts: 6353
Joined: Thu Nov 09, 2017 11:46 am

Re: New Database Model ZXDB

Post by R-Tape »

Einar Saukas wrote: Thu Sep 03, 2020 5:46 pm All these new tables and columns are already available in the current ZXDB version. Therefore if you are running a website based on ZXDB, you can already make these changes right now, there's no need to wait for the next ZXDB update.
Ah that's what those apparently superfluous columns were for. I thought you'd gone round the bend!
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

R-Tape wrote: Thu Sep 03, 2020 8:12 pmAh that's what those apparently superfluous columns were for. I thought you'd gone round the bend!
These explanations are not mutually exclusive!
User avatar
R-Tape
Site Admin
Posts: 6353
Joined: Thu Nov 09, 2017 11:46 am

Re: New Database Model ZXDB

Post by R-Tape »

[mention]Frankie[/mention] [mention]wallyweek[/mention] [mention]WhatHoSnorkers[/mention]. I just wanted to say that the last few updates have not included your recent MIA/uMIA. I'm still working on my part of the update, so I'll get it to Einar for the next one.
User avatar
WhatHoSnorkers
Manic Miner
Posts: 252
Joined: Tue Dec 10, 2019 3:22 pm

Re: New Database Model ZXDB

Post by WhatHoSnorkers »

No worries!
I have a little YouTube channel of nonsense
https://www.youtube.com/c/JamesOGradyWhatHoSnorkers
User avatar
kolbeck
Manic Miner
Posts: 309
Joined: Mon Nov 13, 2017 9:04 pm

Re: New Database Model ZXDB

Post by kolbeck »

Einar Saukas wrote: Fri Aug 21, 2020 1:46 am

Code: Select all

select e.id, e.title, e.comments, e.hardware_blurb, e.known_errors from entries e where e.id = ?
corresponds now to a query like this:

Code: Select all

select e.id, e.title, 
GROUP_CONCAT(c.text ORDER BY c.text SEPARATOR '\n\n') as comments,
h.text as hardware_blurb, 
r.text as known_errors
from entries e 
left join notes c on e.id = c.entry_id and c.notetype_id = 'C'
left join notes h on e.id = h.entry_id and h.notetype_id = 'H'
left join notes r on e.id = r.entry_id and r.notetype_id = 'R'
where e.id = ?
Shouldn't it be R, S, E instead of C, H, R? (Combined with notes.section for S for hardware blurp)

/Thomas
https://api.zxinfo.dk/v3/ - ZXDB API for developers
zxinfo-file-browser - Cross platform app to manage your files
https://zxinfo.dk - another ZXDB frontend
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

kolbeck wrote: Wed Sep 09, 2020 8:59 am Shouldn't it be R, S, E instead of C, H, R? (Combined with notes.section for S for hardware blurp)
Yes, you are right. Sorry!
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

Another ZXDB update is available!

It contains new titles (thanks [mention]R-Tape[/mention] and [mention]pavero[/mention]!), new magazine issues (thanks [mention]Andre Leao[/mention]!), more indexed Load'n'Run covertapes (thanks [mention]wallyweek[/mention]!), and more bugfixes (thanks [mention]druellan[/mention]!)
User avatar
PeterJ
Site Admin
Posts: 6858
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: New Database Model ZXDB

Post by PeterJ »

Thank you [mention]Einar Saukas[/mention],

I will update the site on Thursday evening.
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

ZXDB was updated again!

This version mostly contains new titles (thanks [mention]R-Tape[/mention]!) and hires inlays (thanks [mention]pavero[/mention]!)
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

Yet another ZXDB update is available!

It contains more new titles (thanks [mention]R-Tape[/mention]!) and lots of bugfixes (thanks [mention]druellan[/mention]!)
User avatar
PeterJ
Site Admin
Posts: 6858
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: New Database Model ZXDB

Post by PeterJ »

Thanks [mention]Einar Saukas[/mention],

I will update the site on Wednesday.

Peter
User avatar
R-Tape
Site Admin
Posts: 6353
Joined: Thu Nov 09, 2017 11:46 am

Re: New Database Model ZXDB

Post by R-Tape »

Einar would like to dedicate this update to Kosmic Kanga by Micromania, his favourite game of all time.
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

Another ZXDB update is available!

This time it contains lots of bugfixes (thanks [mention]druellan[/mention]!), new hires inlays (thanks [mention]pavero[/mention]!), new magazines (thanks [mention]Andre Leao[/mention]!), indexed Load'n'Run issues (thanks [mention]wallyweek[/mention]!) and probably more stuff I'm forgetting to mention :)
User avatar
PeterJ
Site Admin
Posts: 6858
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: New Database Model ZXDB

Post by PeterJ »

Thanks [mention]Einar Saukas[/mention],

I will update the site later today.

Peter
User avatar
kolbeck
Manic Miner
Posts: 309
Joined: Mon Nov 13, 2017 9:04 pm

Re: New Database Model ZXDB

Post by kolbeck »

Hi Einar,

Looks like there is a problem running ZXDB_help_search.sql - it gives me the following error:

Code: Select all

ERROR 1062 (23000) at line 18: Duplicate entry 'ar cnidos-5147' for key 'PRIMARY'
As far as I can see, the problem is that entry 18241 has alias, which happens to be the the same title as entry 5147 ?

/Thomas
https://api.zxinfo.dk/v3/ - ZXDB API for developers
zxinfo-file-browser - Cross platform app to manage your files
https://zxinfo.dk - another ZXDB frontend
Ralf
Rick Dangerous
Posts: 2279
Joined: Mon Nov 13, 2017 11:59 am
Location: Poland

Re: New Database Model ZXDB

Post by Ralf »

Thanks for the update, as usual!

Personally I'm really happy about indexing Load'n'Run. Now you can see all titles on each cassette and
in the case of cracks there is even a proper name of of the cracked game, along with the name of the crack.

Could we push it a bit more? ;)

Now when I check for example Load'n'Run 26:
https://www.spectrumcomputing.co.uk/ent ... n_issue_26

I can see at the top of the list that it contains "Esploratore lunare" which is in fact Nodes of Yesod.

When I type "Esploratore lunare" into the search it returns indeed Nodes of Yesod.
But Nodes of Yesod entry itself don't have info that it was released on Load'n'Run under different title.

Type "Esploratore" into search - you'll get Nodes of Yesod and Sabre Wulf ;) "Why???" someone could ask.
It may be really confusing for someone who doesn't know the story behind Italian cracks.

Would it be possible to add "Released unofficially as XXX on LOAD'N'Run NN' on entries of such games?
Or something like that?
Post Reply