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
pavero
Dynamite Dan
Posts: 1569
Joined: Sat Dec 09, 2017 11:49 pm
Location: The Czech Republic
Contact:

Re: New Database Model ZXDB

Post by pavero »

Ralf wrote: Tue Feb 12, 2019 12:13 pm
lots of hi-res inlays (thanks Pavel!)
By the way, are there any up to date rules/guidelines what is the preferred size for hi-res inlays? Or inlays in general?
It was a compromise due to size per file vs. hosting server capacity.

Rules are following:
single jewel case - 900 pixel height
double jewel case / small boxes - 1150 pixel height
big boxes - 1900 pixel height

(all inlays are resized fully automatically by PHP script)

Of couser I still have all inlays in their original size (300 or 600 dpi).

So maybe in the future there will be two links per every inlay - the second would be external link (on Archive.org) with files in their max resolutions.
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 lots of new inlays (thanks Pavero!) and many archive fixes reported in this forum.

Also technically, I'm now working on some internal improvements to the ZXDB schema (I will detail them later). In preparation for these upcoming changes, I have just renamed a couple tables and columns in ZXDB, as follows:
  • Table "relatedlinks" was renamed to "webrefs"
  • Table "origintypes" was renamed to "sourcetypes"
  • Column "downloads.origintype_id" was renamed to "downloads.sourcetype_id", and now it's nullable
  • Column "extras.origintype_id" was renamed to "extras.sourcetype_id", and now it's nullable
As usual, I have kept the old tables and columns, so websites based on ZXDB can update to this new ZXDB release without breaking anything, before making any changes. However if you have a website that uses these tables and columns, please make these changes ASAP, because old tables and columns will be removed in the next update.

Have fun!
Ralf
Rick Dangerous
Posts: 2279
Joined: Mon Nov 13, 2017 11:59 am
Location: Poland

Re: New Database Model ZXDB

Post by Ralf »

Keep the good work Einar !
User avatar
PeterJ
Site Admin
Posts: 6854
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: New Database Model ZXDB

Post by PeterJ »

The latest ZXDB has now been installed. I will let [mention]pavero[/mention] add the details about the new scans. Thanks [mention]Einar Saukas[/mention]

As far as I'm aware there are no new titles in this release.
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!

It contains several new titles (thanks Dave!), several hires inlays from Proxima Software (thanks Pavel!), and several other fixes (thanks everybody!)

Technically table "aliases" now has a composite natural key, instead of artificial key "id". This is part of our ongoing process to improve the database structure. This "id" served no practical purpose in this table, so I doubt removing it will affect anybody.
User avatar
druellan
Dynamite Dan
Posts: 1466
Joined: Tue Apr 03, 2018 7:19 pm

Re: New Database Model ZXDB

Post by druellan »

[mention]Einar Saukas[/mention] is the database tracking games sound capabilities, like Covox, Currah or General Sound? I see the Currah support mentioned sometimes on the comments, but there is still no structure to flag the entries based on the supported hardware, right?
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

druellan wrote: Mon Feb 25, 2019 12:33 pm @Einar Saukas is the database tracking games sound capabilities, like Covox, Currah or General Sound? I see the Currah support mentioned sometimes on the comments, but there is still no structure to flag the entries based on the supported hardware, right?
There's already a tag for "Currah Microspeech Support". Here's a list of games marked with this tag:

https://spectrumcomputing.co.uk/index.p ... up_id=1003

There are so few games supporting these other devices, that it's probably not worth it creating tags for them.
Ralf
Rick Dangerous
Posts: 2279
Joined: Mon Nov 13, 2017 11:59 am
Location: Poland

Re: New Database Model ZXDB

Post by Ralf »

There are a few games with General Sound support but they are mostly Russian mods of existing, standard games and they aren't present in ZXDB

You can check them here, General Sound emulation is supported by Spectaculator for example:
https://vtrd.in/gs.php
User avatar
druellan
Dynamite Dan
Posts: 1466
Joined: Tue Apr 03, 2018 7:19 pm

Re: New Database Model ZXDB

Post by druellan »

Einar Saukas wrote: Mon Feb 25, 2019 4:42 pm There's already a tag for "Currah Microspeech Support". Here's a list of games marked with this tag:
https://spectrumcomputing.co.uk/index.p ... up_id=1003
There are so few games supporting these other devices, that it's probably not worth it creating tags for them.
Oh, I missed the Currah tag, great! About the games using the other interfaces, well, recently I was researching some MODs on TOSEC, and found that some of them have GS support just by chance, and I was thinking that if in the future we add them to the database, a way to indicate they have GS support could be interesting.
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

druellan wrote: Tue Feb 26, 2019 12:14 am
Einar Saukas wrote: Mon Feb 25, 2019 4:42 pm There's already a tag for "Currah Microspeech Support". Here's a list of games marked with this tag:
https://spectrumcomputing.co.uk/index.p ... up_id=1003
There are so few games supporting these other devices, that it's probably not worth it creating tags for them.
Oh, I missed the Currah tag, great! About the games using the other interfaces, well, recently I was researching some MODs on TOSEC, and found that some of them have GS support just by chance, and I was thinking that if in the future we add them to the database, a way to indicate they have GS support could be interesting.
Sure!

Right now It seems we have only 3 games that support GS in ZXDB. A tag is not worth it. However if we later add more titles, please remind me to create a tag for 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 »

Einar Saukas wrote: Fri Feb 15, 2019 7:36 amAlso technically, I'm now working on some internal improvements to the ZXDB schema (I will detail them later).
I recently mentioned I'm working on ZXDB improvements. Let's talk about it!

According to the old WoS model, each game in the archive could be either a mod of one other game, or inspired by one other game. However there are quite a few exceptions to this rule. For instance, Ku-Ku is a mod of 2 different games: Project Future (game code/engine) and Sabre Wulf (graphics). Miner Willy's Nightmare is a mod of Atic Atac, and at the same time inspired by Manic Miner. A few other titles were inspired by two different games, combining ideas from both. And so on... Since ZXDB schema was originally based on the old WoS site, it also had the same restrictions. Not anymore. ZXDB now contains a new table "relations" to store all kinds of NxN relationships between titles, so each game can now have multiple inspirations, and it can be a mod from multiple titles.

The old WoS site didn't have a proper place to store information about game editors. Instead, this information was simply stored in comments. For instance, Chaos has a comment "There also was a Chaos Editor. There is a patch (Additional material) to create a 256-colour edition for the ULAPlus", and Chaos Editor has a comment "An editor for Chaos". This information was mixed with other comments, and harder to search. Now this information will be also moved to the new table "relations".

This table "relations" will also store graphics engines (like Churrera and NIRVANA), game engines (like PAW and AGD), libraries (like FZX and ZX7), and other development tools currently displayed under the description "authored with". This information was previously stored in "frameworks", but we don't need a separate table anymore since "relations" is much more flexible.

Technically, this new table "relations" will replace table "framework", and columns "entries.is_mod" and "entries.original_id". As usual, I didn't remove the old table and fields yet, so websites based on ZXDB can have time to make the proper changes. But they will be removed in the next ZXDB update.
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!
User avatar
druellan
Dynamite Dan
Posts: 1466
Joined: Tue Apr 03, 2018 7:19 pm

Re: New Database Model ZXDB

Post by druellan »

Einar Saukas wrote: Tue Feb 26, 2019 3:11 am The old WoS site didn't have a proper place to store information about game editors. Instead, this information was simply stored in comments. For instance, Chaos has a comment "There also was a Chaos Editor. There is a patch (Additional material) to create a 256-colour edition for the ULAPlus", and Chaos Editor has a comment "An editor for Chaos". This information was mixed with other comments, and harder to search. Now this information will be also moved to the new table "relations".
Cool. I was checking that recently and found that it was very confusing, since there are games that have external non-official tools, but others with the editor included on the code or on the side B of the cassette. Now we can have a better classification for those cases too.
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

Column "entries.spanish_price" will be removed in the next ZXDB update. I just finished moving this information to the proper releases, using SPA2 as reference. Many thanks to Juan for providing access to SPA2!

Thus if you run a website based on ZXDB, please make the required changes in your queries before the next update.
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 release is available!

This update contains a few new titles, lots of new inlays, and the database structure improvements described in my latest posts :)
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!

As usual, [mention]R-Tape[/mention] had added recent releases to ZXDB. And [mention]druellan[/mention] is now working on the task of processing corrections reported by users in this forum. Many thanks to both!

My apologies to [mention]pavero[/mention] that I couldn't add the latest instructions and inlays he provided yet. I promise they will be in the next update!
User avatar
PeterJ
Site Admin
Posts: 6854
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: New Database Model ZXDB

Post by PeterJ »

Thanks everyone!

SC has now been updated. Notes will follow shortly.

Peter
User avatar
jpablo
Drutt
Posts: 46
Joined: Sun Jan 13, 2019 12:05 am
Location: Zaragoza, Spain
Contact:

Re: New Database Model ZXDB

Post by jpablo »

PeterJ wrote: Fri Apr 05, 2019 11:07 am SC has now been updated. Notes will follow shortly.
Excellent. Thank you!

I noticed that there is no "main file downloads" block in here:

https://spectrumcomputing.co.uk/index.p ... 96&id=1217

I may be missing something - I was wondering if that's normal
User avatar
PeterJ
Site Admin
Posts: 6854
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: New Database Model ZXDB

Post by PeterJ »

Hi [mention]jpablo[/mention]

We don't currently offer Ocean Software titles for download, although this will hopefully change in the near future.

Peter
User avatar
jpablo
Drutt
Posts: 46
Joined: Sun Jan 13, 2019 12:05 am
Location: Zaragoza, Spain
Contact:

Re: New Database Model ZXDB

Post by jpablo »

I see - thanks for clarifying!
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!

It contains lots of new titles (thanks [mention]R-Tape[/mention]!), lots of corrections (thanks [mention]druellan[/mention]!), lots of improved inlays and instructions (thanks [mention]pavero[/mention]!), and probably lots of other stuff I can't remember right now :)
User avatar
PeterJ
Site Admin
Posts: 6854
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: New Database Model ZXDB

Post by PeterJ »

Einar Saukas wrote: Thu Apr 18, 2019 9:26 pm A new ZXDB update is available!

It contains lots of new titles (thanks @R-Tape!), lots of corrections (thanks @druellan!), lots of improved inlays and instructions (thanks @pavero!), and probably lots of other stuff I can't remember right now :)
Thanks everyone. I will update SC over the weekend.
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

Here's some technical information that's only really relevant to websites based on ZXDB:

As part of our ongoing effort to improve ZXDB structure, we will soon remove table "formattypes", and the corresponding column "formattype_id" from tables "downloads", "extras", "labelfiles", and "magfiles".

This column currently stores the data format of each file. For instance, if it's a +3 disk image, then it contains the ID for "+3 disk (DSK)". If it's a PDF, it contains the ID for "Document (PDF)". And so on...

The problem is, this information is completely redundant. Just looking at the filename extension will give you exactly the same information. All +3 disk files have extension ".dsk.zip", all PDF documents have extension ".pdf", all TXT documents have extension ".txt", etc. Sometimes the people contributing with ZXDB updates have made mistakes filling this information, so it doesn't match the actual file type. Therefore this is a field that's unnecessary at best, plain wrong at worst.

There's now a new table called "extensions", that store an explanation for each file extension supported by ZXDB. Websites based on ZXDB can simply check the data formats themselves. If anyone needs help with SQL changes here (or anything else really), please let me know!

The main file extensions supported by ZXDB (as detailed in table "extensions") are listed below:

Code: Select all

.avi.zip            Video (AVI)
.ay.zip             Music (AY)
.d80.zip            Didaktik disk (D80)
.dck.zip            Timex expansion (DCK)
.dsk.zip            +3 disk (DSK)
.fdi.zip            TR-DOS disk (FDI)
.gif                Picture (GIF)
.html               Webpage (HTML)
.ifl                Screen dump 8x2 (IFL)
.img.zip            Disciple/+D disk (IMG)
.jpg                Picture (JPG)
.mdr.zip            Microdrive cartridge (MDR)
.mgt.zip            Disciple/+D disk (MGT)
.mid.zip            Music (MIDI)
.mlt                Screen dump 8x1 (MLT)
.mp3.zip            Music (MP3)
.mpg.zip            Video (MPG)
.opd.zip            Opus Discovery disk (OPD)
.p.zip              ZX81 tape (P)
.pdf                Document (PDF)
.png                Picture (PNG)
.pok                Pokes (POK)
.rom.zip            ROM image dump (ROM)
.rzx.zip            Game recording (RZX)
.scl.zip            TR-DOS disk (SCL)
.scr                Screen dump (SCR)
.slt.zip            Snapshot (SLT)
.sna.zip            Snapshot (SNA)
.szx.zip            Snapshot (SZX)
.tap.zip            Tape (TAP)
.trd.zip            TR-DOS disk (TRD)
.txt                Document (TXT)
.tzx.zip            Perfect tape (TZX)
.wdr.zip            Wafadrive wafer disk (WDR)
.wmv.zip            Video (WMV)
.z80.zip            Snapshot (Z80)
.z81.zip            ZX81 tape (81)
.zxk                ZX Vega keymap (ZXK)
_Fonts.zip          Fonts package
_SourceCode.zip     Source code package
This modification will happen gradually. For now, both formattypes 54 (document ".pdf") and 55 (document ".txt") will be removed in the next ZXDB update. OK?
User avatar
PeterJ
Site Admin
Posts: 6854
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: New Database Model ZXDB

Post by PeterJ »

Thanks [mention]Einar Saukas[/mention]

Sounds good to me.
User avatar
pavero
Dynamite Dan
Posts: 1569
Joined: Sat Dec 09, 2017 11:49 pm
Location: The Czech Republic
Contact:

Re: New Database Model ZXDB

Post by pavero »

I agree, this information was really redundant.

But big number of PHP scripts as SC site will be probably complete nonfunctional after removing formattype_id attribute from ZXDB.
Post Reply