New Database Model ZXDB

[Einar Saukas]

Ah that's what those apparently superfluous columns were for. I thought you'd gone round the bend!

These explanations are not mutually exclusive!

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

[WhatHoSnorkers]

No worries!

[kolbeck]

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

[Einar Saukas]

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!

[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]!)

[PeterJ]

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

I will update the site on Thursday evening.

[Einar Saukas]

ZXDB was updated again!

This version mostly contains new titles (thanks [mention]R-Tape[/mention]!) and hires inlays (thanks [mention]pavero[/mention]!)

[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]!)

[PeterJ]

Thanks [mention]Einar Saukas[/mention],

I will update the site on Wednesday.

Peter

[R-Tape]

Einar would like to dedicate this update to Kosmic Kanga by Micromania, his favourite game of all time.

[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

[PeterJ]

Thanks [mention]Einar Saukas[/mention],

I will update the site later today.

Peter

[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

[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?

[PeterJ]

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

Thanks for the heads up [mention]kolbeck[/mention]. I will hang fire on updating SC.

[Einar Saukas]

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'

Thanks for reporting it!!!

I'm currently traveling so I cannot fix it right now, but here's an easy workaround. After importing ZXDB, just before running "ZXDB_help_search.sql", execute this:

Code: Select all

update compilations set alias=null where alias='Aracnidos';

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 ?

The exact problem is that both entries have aliases that are mispellings of the main title (same letters but missing accentuation). Because of this, GROUP BY recognizes them as 2 different names, but the collation used in this table recognizes them as the same. Later I will have to figure out how to use collation in this query!

[Einar Saukas]

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?

Good point!

This is now fixed in both main and compact views:

https://www.spectrumcomputing.co.uk/ent ... s_of_Yesod
https://www.spectrumcomputing.co.uk/alt ... s_of_Yesod

[Ralf]

Good point!
This is now fixed in both main and compact views

Wow, that was fast! Thanks a lot!

[Einar Saukas]

You are welcome!

[Einar Saukas]

For people running sites based on ZXDB:

The column "notes.is_zxsr" is not used anymore and it will be removed in the next ZXDB release.

Please ensure you don't use it in your system before the next update!

[Einar Saukas]

Yet another ZXDB update is available!

This time we have a HUGE update. We created more than 550Kb of database scripts (about 1200 SQL statements) just for this version! As usual, it contains a lot of bugfixes (thanks [mention]druellan[/mention]!), new hires inlays (thanks [mention]pavero[/mention]!), new titles (thanks [mention]R-Tape[/mention]!), and lots of additional content from many other contributors.

Thanks everyone!!!

[R-Tape]

That makes #93! (plus the as yet uncounted BSCE* ones).

*Before Spectrum Computing Era

[Einar Saukas]

A new ZXDB update is available!

This release is really HUGE. It contains dozens of new titles (thanks [mention]R-Tape[/mention] and [mention]pavero[/mention]!), hundreds of bugfixes (thanks [mention]druellan[/mention]!), over a thousand new hires inlays (thanks [mention]pavero[/mention] again!), and lots more content from other contributors (thanks everyone!)

I previously wrote that the last update was huge. with more than 550Kb of database scripts (about 1200 SQL statements). This time, it's about twice the size: more than 900Kb of database scripts (about 2000 SQL statements).

However this update was so huge that I didn't have time to include everything that people sent me. In particular, there are still hundreds more bugfixes that [mention]druellan[/mention] prepared, that I didn't merge yet. I will continue to work on the remaining material and I'm planning another ZXDB update next week, with even more content. Therefore if you notice a bugfix marked as fixed, that's not ZXDB yet, please wait for another ZXDB update before pointing it out. My apologies for the delay!

[4thRock]

Hope all that ATM information that alonecoder helped me with (screenshots, new titles, corrections) makes it on the next update.
But no rush, these things take time to cross reference and validate. Better be "slow" than to add incorrect information!