New Database Model ZXDB
Moderator: druellan
- Einar Saukas
- Bugaboo
- Posts: 3120
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
And another ZXDB update is already available!
This time we have lots of new Spectrum titles (thanks [mention]R-Tape[/mention]!), about 60 Sinclair QL titles (thanks [mention]4thRock[/mention]!), about 400 links to BBC Micro versions of Spectrum games (thanks [mention]Stu[/mention]!), over 100 links to Spectrum games at The Cutting Room Floor (thanks again [mention]Stu[/mention]!), about 100 music references (thanks [mention]Rorthron[/mention] and everyone else that contributed with information!)
Our archive is growing faster than ever
This time we have lots of new Spectrum titles (thanks [mention]R-Tape[/mention]!), about 60 Sinclair QL titles (thanks [mention]4thRock[/mention]!), about 400 links to BBC Micro versions of Spectrum games (thanks [mention]Stu[/mention]!), over 100 links to Spectrum games at The Cutting Room Floor (thanks again [mention]Stu[/mention]!), about 100 music references (thanks [mention]Rorthron[/mention] and everyone else that contributed with information!)
Our archive is growing faster than ever
- Einar Saukas
- Bugaboo
- Posts: 3120
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
Another ZXDB update is available!
It contains lots of ZX81 titles (thanks [mention]kolbeck[/mention]!), new hires inlays (thanks [mention]pavero[/mention]!), newly indexed Load'n'Run covertapes (thanks [mention]wallyweek[/mention]!), about 1400 new links to Spectrum versions on other platforms (thanks [mention]Stu[/mention]!), and many other minor improvements.
It contains lots of ZX81 titles (thanks [mention]kolbeck[/mention]!), new hires inlays (thanks [mention]pavero[/mention]!), newly indexed Load'n'Run covertapes (thanks [mention]wallyweek[/mention]!), about 1400 new links to Spectrum versions on other platforms (thanks [mention]Stu[/mention]!), and many other minor improvements.
Re: New Database Model ZXDB
Thanks everyone! I will update the site this evening at sometime between 18:00 and 21:00 BST. The main site will produce errors during the update.
Re: New Database Model ZXDB
Just to add that it doesn't contain the new entries I've been working on. Anyone expecting anything - nearly done, wait till the next update!
Re: New Database Model ZXDB
Just wow!!!
34,699 rows in the entries table now!
34,699 rows in the entries table now!
- Einar Saukas
- Bugaboo
- Posts: 3120
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
Another ZXDB update is already available!
It mostly contains recently released titles. Thanks [mention]R-Tape[/mention]!
It mostly contains recently released titles. Thanks [mention]R-Tape[/mention]!
- Einar Saukas
- Bugaboo
- Posts: 3120
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
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!
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!
Re: New Database Model ZXDB
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.
There will be a short delay to applying the update as I need to adjust php.ini for the significantly larger file size.
Re: New Database Model ZXDB
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!
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/
- Einar Saukas
- Bugaboo
- Posts: 3120
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
Thank you for all your years of effort on putting ZXSR together, you have been doing it much longer than us!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!
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!
- Einar Saukas
- Bugaboo
- Posts: 3120
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
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:
Your website should use this:
This should be a fairly simple change. Instead of this:
Code: Select all
SELECT r.id, r.score FROM magrefs r
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
- Einar Saukas
- Bugaboo
- Posts: 3120
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
A new ZXDB update is available! This version contains:
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:
corresponds now to a query like this:
Although it's much easier to simply obtain all rows from table "notes" corresponding to a certain title, and process them directly:
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!
- 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!
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 = ?
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 = ?
Code: Select all
select id, entry_id, notetype_id, section, text from notes where entry_id = ?
- Einar Saukas
- Bugaboo
- Posts: 3120
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
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]
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]
- Einar Saukas
- Bugaboo
- Posts: 3120
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
As part of our ongoing work to improve ZXDB, we will make the following changes in the next ZXDB update:
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.
- 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:
use this:Code: Select all
select comments from labels where id = ?
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:
use this:Code: Select all
select * from magrefs r inner join issues i on r.issue_id = i.id where r.is_supplement = 1
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:
use this:Code: Select all
select * from entries e left join idioms i where e.idiom_id = i.id
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.
Re: New Database Model ZXDB
Ah that's what those apparently superfluous columns were for. I thought you'd gone round the bend!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.
- Einar Saukas
- Bugaboo
- Posts: 3120
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
These explanations are not mutually exclusive!
Re: New Database Model ZXDB
[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.
- WhatHoSnorkers
- Manic Miner
- Posts: 254
- Joined: Tue Dec 10, 2019 3:22 pm
Re: New Database Model ZXDB
No worries!
I have a little YouTube channel of nonsense
https://www.youtube.com/c/JamesOGradyWhatHoSnorkers
https://www.youtube.com/c/JamesOGradyWhatHoSnorkers
Re: New Database Model ZXDB
Shouldn't it be R, S, E instead of C, H, R? (Combined with notes.section for S for hardware blurp)Einar Saukas wrote: ↑Fri Aug 21, 2020 1:46 amcorresponds now to 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 = ?
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 = ?
/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
zxinfo-file-browser - Cross platform app to manage your files
https://zxinfo.dk - another ZXDB frontend
- Einar Saukas
- Bugaboo
- Posts: 3120
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
Yes, you are right. Sorry!
- Einar Saukas
- Bugaboo
- Posts: 3120
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
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]!)
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]!)
Re: New Database Model ZXDB
Thank you [mention]Einar Saukas[/mention],
I will update the site on Thursday evening.
I will update the site on Thursday evening.
- Einar Saukas
- Bugaboo
- Posts: 3120
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
ZXDB was updated again!
This version mostly contains new titles (thanks [mention]R-Tape[/mention]!) and hires inlays (thanks [mention]pavero[/mention]!)
This version mostly contains new titles (thanks [mention]R-Tape[/mention]!) and hires inlays (thanks [mention]pavero[/mention]!)
- Einar Saukas
- Bugaboo
- Posts: 3120
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
Yet another ZXDB update is available!
It contains more new titles (thanks [mention]R-Tape[/mention]!) and lots of bugfixes (thanks [mention]druellan[/mention]!)
It contains more new titles (thanks [mention]R-Tape[/mention]!) and lots of bugfixes (thanks [mention]druellan[/mention]!)
Re: New Database Model ZXDB
Thanks [mention]Einar Saukas[/mention],
I will update the site on Wednesday.
Peter
I will update the site on Wednesday.
Peter