ZXDB architecture

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
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

ZXDB architecture

Post by moroz1999 »

I know that ZXDB has appeared from the structure used by original WOS database. But what do you think about improving it?

1. Releases are just downloads of known types. Shouldn't they have their own tables?
2. Cassete inlays are connected to entries. Shouldn't they be connected to releases instead, as now publishers do?
3. What about supported hardware? AY Sound is in "features" sections, it's more like a "tag". May be a proper hardware features table should be added instead? So then "controls" would become a part of it.
4. What about storing MD5 in database for downloads?

I'm saying because I'm doing it exactly like this in ZX-Art, just thought that may be some of these ideas could be useful maybe.
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: ZXDB architecture

Post by Einar Saukas »

moroz1999 wrote: Fri Mar 30, 2018 10:36 pmI know that ZXDB has appeared from the structure used by original WOS database. But what do you think about improving it?
Improvement suggestions are always welcome!

moroz1999 wrote: Fri Mar 30, 2018 10:36 pm1. Releases are just downloads of known types. Shouldn't they have their own tables?
It's already the way you suggest. ZXDB already have separate tables DOWNLOADS and RELEASES. Or do you mean something else?

moroz1999 wrote: Fri Mar 30, 2018 10:36 pm2. Cassete inlays are connected to entries. Shouldn't they be connected to releases instead, as now publishers do?
It's already the way you suggest. Cassette inlays are stored in table DOWNLOADS. Everything in table DOWNLOADS is associated to a specific release in table RELEASES through columns (ENTRY_ID + RELEASE_SEQ).

moroz1999 wrote: Fri Mar 30, 2018 10:36 pm3. What about supported hardware? AY Sound is in "features" sections, it's more like a "tag".
Correct. Most "groups" in ZXDB (feature, competition, theme, etc) correspond to "tags" in ZX-Art.

moroz1999 wrote: Fri Mar 30, 2018 10:36 pmMay be a proper hardware features table should be added instead? So then "controls" would become a part of it.
Right now, controls is the only relevant "multiple selection" hardware characteristic in ZXDB. We also store minimum hardware requirement, but that's a single choice for each title. If anyone is willing to provide more detailed hardware information for the thousands of entries we have, I will gladly modify ZXDB to accommodate this information!

moroz1999 wrote: Fri Mar 30, 2018 10:36 pm4. What about storing MD5 in database for downloads?
Both MD5 and CRC32 values for files in ZXDB table DOWNLOADS are stored in ZX Pokemaster database. You just need to import this information from there:

https://github.com/eklipse2009/ZX-Pokemaster
https://sourceforge.net/projects/zx-pokemaster/

moroz1999 wrote: Fri Mar 30, 2018 10:36 pmI'm saying because I'm doing it exactly like this in ZX-Art, just thought that may be some of these ideas could be useful maybe.
Thanks! :)
User avatar
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

Re: ZXDB architecture

Post by moroz1999 »

Thank you! It seems like all I really needed is opening my eyes wider, since almost everything I thought is already implemented :D
User avatar
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

Re: ZXDB architecture

Post by moroz1999 »

Is there such a language Yugoslavian? Shouldn't it be Serbo-Croatian with ISO code sh?
User avatar
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

Re: ZXDB architecture

Post by moroz1999 »

ZXDB-to-ZX-Art languages codes table for PHP:
protected $languages = [
"be" => ["be"],
"bs" => ["bs"],
"ca" => ["ca"],
"cs" => ["cs"],
"da" => ["da"],
"de" => ["de"],
"el" => ["el"],
"en" => ["en"],
"eo" => ["eo"],
"es" => ["es"],
"eu" => ["eu"],
"fi" => ["fi"],
"fr" => ["fr"],
"gl" => ["gl"],
"hr" => ["hr"],
"hu" => ["hu"],
"is" => ["is"],
"it" => ["it"],
"la" => ["la"],
"lt" => ["lt"],
"lv" => ["lv"],
"m-" => ["fr"],
"nl" => ["nl"],
"no" => ["no"],
"pl" => ["pl"],
"pt" => ["pt"],
"ro" => ["ro"],
"ru" => ["ru"],
"sh" => ["sh"],
"sk" => ["sk"],
"sl" => ["sl"],
"sr" => ["sr"],
"sv" => ["sv"],
"tr" => ["tr"],
"y-" => ["sh"],

"?r" => ["bs", "hr", "sr"],
"?l" => ["ca", "en", "it", "es"],
"?0" => ["hr", "en"],
"?1" => ["cz", "en"],
"?n" => ["cz", "en", "it", "pl", "ru", "es"],
"?m" => ["cz", "en", "ru", "sk"],
"?a" => ["cz", "en", "sk"],
"?2" => ["nl", "en"],
"?o" => ["en", "eo, es"],
"?k" => ["en", "fr"],
"?b" => ["en", "fr", "de"],
"?s" => ["en", "fr", "de", "it", "pt", "ru", "es", "sv"],
"?q" => ["en", "fr", "de", "it", "pt", "es"],
"?c" => ["en", "fr", "de", "it", "es"],
"?e" => ["en", "fr", "es"],
"?3" => ["en", "de"],
"?h" => ["en", "de", "hu", "ru"],
"?d" => ["en", "de", "it", "pt", "es"],
"?i" => ["en", "de", "it", "es"],
"?u" => ["en", "hu"],
"?t" => ["en", "it"],
"?p" => ["en", "it", "pl", "ru", "es"],
"?4" => ["en", "pl"],
"?f" => ["en", "pl", "ru", "es"],
"?5" => ["en", "pt"],
"?6" => ["en", "ru"],
"?g" => ["en", "ru", "es"],
"?7" => ["en", "sk"],
"?8" => ["en", "es"],
"?j" => ["la", "es"],
"?9" => ["es", "ca"],
];
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: ZXDB architecture

Post by Einar Saukas »

moroz1999 wrote: Tue Apr 17, 2018 11:42 pm Is there such a language Yugoslavian? Shouldn't it be Serbo-Croatian with ISO code sh?
Serbo-Croatian is a somewhat generic term that covers several similar (but not exactly identical) idioms in the region. I'm not sure if it's exactly the same that was referred as Yugoslavian in the 1980s.

I would prefer to wait for confirmation from someone very knowledgeable about these idioms, before making any changes. If I combine Serbo-Croatian and Yugoslavian now, but it turns out I shouldn't, it will be a lot harder to split them again later.
User avatar
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

Re: ZXDB architecture

Post by moroz1999 »

Sounds reasonable! There is also Monaco language with the same question.
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: ZXDB architecture

Post by Einar Saukas »

moroz1999 wrote: Wed Apr 18, 2018 9:20 am Sounds reasonable! There is also Monaco language with the same question.
Actually I just realized that Monaco isn't used. I will remove it in the next ZXDB update.

Advanced search at WoS also provides option to search for "Monaco" as message language, but it returns no results.
User avatar
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

Re: ZXDB architecture

Post by moroz1999 »

Thanks! One more question - are filetypes IDs reliable or should I stick to their text? Either they have once changed at some point or I have some mystical problems for investigation.
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: ZXDB architecture

Post by Einar Saukas »

moroz1999 wrote: Wed Apr 18, 2018 11:38 pm Thanks! One more question - are filetypes IDs reliable or should I stick to their text? Either they have once changed at some point or I have some mystical problems for investigation.
You can use these IDs. They changed once a long time ago, but they won't be changed anymore.

Now that several systems are integrating with ZXDB, I'm much more careful about any changes.
User avatar
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

Re: ZXDB architecture

Post by moroz1999 »

Thanks. And the same question goes for the genretype_ids. Either I made a mistake or they have changed at some point. All the compilations are imported into Electronic Magazines somewhy on ZX-Art.
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: ZXDB architecture

Post by Einar Saukas »

moroz1999 wrote: Fri Apr 20, 2018 11:02 am Thanks. And the same question goes for the genretype_ids. Either I made a mistake or they have changed at some point. All the compilations are imported into Electronic Magazines somewhy on ZX-Art.
Same answer!
User avatar
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

Re: ZXDB architecture

Post by moroz1999 »

Thank you :) The I'll recheck them and re-run the import procedure once again.
So far so good, 99% of releases are re-indexed via contents of files. That is only required to be done once now, after that appropriate stable GUIDs would be used. At the moment import takes about 7-8 hours, that's mostly because of re-downloading all the release files for reindexing.
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: ZXDB architecture

Post by Einar Saukas »

Good work! :)
Post Reply