ZXDB and ZX-Art

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

ZXDB and ZX-Art

Post by moroz1999 »

Hello guys!
First of all, thanks for ZXDB efforts. I've once imported a lot of ZXDB information to ZX-Art, and today I've tried to rerun the import again with some updated functionality.
I've found a strange problem: it seems like IDs of downloads have changed. For example, The Spy Who Loved Me TZX download "SpyWhoLovedMeThe.tzx.zip" previously had ID 228, and now it seems to have ID 226.
This brings some problems to my import procedure: I'm storing each original ID of ZXDB software and downloads, so later I would be able to re-run the import and only update what really changed. This is however impossible if all IDs change in different versions of ZXDB, so I'll have to delete all ZXDB downloads from ZX-Art before each import procedure run.
Did I understand the problem correctly? Or should I recheck my import procedure code to find out why the download IDs have changed?
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: ZXDB and ZX-Art

Post by Einar Saukas »

moroz1999 wrote: Fri Mar 30, 2018 9:38 pmHello guys!
First of all, thanks for ZXDB efforts.
Thanks for the incentive! :)

moroz1999 wrote: Fri Mar 30, 2018 9:38 pmI've once imported a lot of ZXDB information to ZX-Art, and today I've tried to rerun the import again with some updated functionality.
I've found a strange problem: it seems like IDs of downloads have changed.
Sorry, I changed these IDs a while ago because "someone" complained about non-consecutive IDs in ZXDB. But they are meaningless anyway! This column DOWNLOADS.ID only exists for technical reasons. The natural key in this table would be (ENTRY_ID + FILE_LINK), however I couldn't use it because data I imported from original WoS archive contained thousands of empty files and it's still my goal to ensure that ZXDB preserves 100% of the original WoS content.

When importing from table DOWNLOADS, I suggest you skip all rows with FILE_LINK IS NULL, and use (ENTRY_ID + FILE_LINK) as composite key instead.

Please let me know (either here or by email) if you have problems with anything else!

moroz1999 wrote: Fri Mar 30, 2018 9:38 pmThis brings some problems to my import procedure: I'm storing each original ID of ZXDB software and downloads, so later I would be able to re-run the import and only update what really changed. This is however impossible if all IDs change in different versions of ZXDB, so I'll have to delete all ZXDB downloads from ZX-Art before each import procedure run.
As a general rule, IDs in ZXDB rarely change, but only ENTRIES,ID is really guaranteed to never change. Even so, a few entries may sometimes disappear, when someone finds out that 2 different entries are just re-releases of the same program, perhaps under a different name. This is the reason old WoS archive doesn't have contiguous IDs, in the beginning those merges happened all the time!

If you can afford to delete all your ZXDB content and re-import everything periodically, this is by far the easiest way. Otherwise if you can give me more information about your database model (again either here or by email), we can work together to figure out a better integration to make this process easier for you.
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: ZXDB and ZX-Art

Post by Einar Saukas »

Next ZXDB update will remove meaningless IDs, including the one you mentioned. For further details, please take a look at this post:

viewtopic.php?f=32&t=146&start=70#p8414
User avatar
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

Re: ZXDB and ZX-Art

Post by moroz1999 »

Thank you! Now I understood. I'll remove the releases from ZX-Art and reimport the database, since it's still possible to do it. However, in future I'll only update the entries and releases without deletion, because in ZX-Art the releases can be commented and soon could be voted as well, so deleting release will lose this information.
Also, I'm planning to group the software by "entries" (prods in ZX-Art terminology). One entry can contain releases from numerous sources and databases.

How do you think is it possible that FILE_LINK would ever change for existing downloads? My goal is making a continuous integration, where import from ZXDB can be re-run periodically, and only the changes/additions would be imported without deleting anything, so multiple bases could be united in one.
User avatar
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

Re: ZXDB and ZX-Art

Post by moroz1999 »

I'm experimenting with smarter continuous integration. I've got the case with our Crystal Kingdom Dizzy remake. Since it has been added into both databases separately, there is no ZXDB entry ID in ZXArt database yet.
I was going to resolve it using my current common logic I use for all unknown software entries:
1. Check if there is ZXDB entry ID from previous import.
2. If there is not, then check every releases's file MD5. If there is a file with same MD5 in ZXArt, then it's 99.99% the same software entry.
3. If there are not, then try to search for the software with same name and same year.

My problem is that we have different files for releases:
https://spectrumcomputing.co.uk/zxdb/si ... 17.tap.zip
https://zxart.ee/release/id:192279/file ... 017%29.zip

SpectrumComputing copy don't contain the text file and SFV checksum file. Where have you got this copy from? The problems with throwing out the info files are:
* The new version of release file appears even if there are no changes in the game. That's what really bothers me.
* Some authors can get really angry because of this. I can remember a lot of bad blood between zxaaa.net archive and demoscene authors because of release text files being throwing out from original archives on zxaaa.net.

I know it's easy to update Crystal Kingdom Dizzy files, but are there more? Please check.
User avatar
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

Re: ZXDB and ZX-Art

Post by moroz1999 »

So, having entry_id + release_seq as a GUID for ZX-Art release won't help really. It seems like I have to use something like:
md5 (entry_id + release_seq + file_link).

Why? Because of architecture differences: ZXDB can have more than file download for release, and ZX-Art cannot. Why? For example, let's take Castlevania. It's being stored as 1 Entry, 1 Release and 11 downloads. In ZX-Art I would like to store 1 Entry and 11 Releases, each release having one only download file, since each release would have it's format, it's supported hardware and supported language.
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: ZXDB and ZX-Art

Post by Einar Saukas »

moroz1999 wrote: Sun Apr 15, 2018 6:20 pmSpectrumComputing copy don't contain the text file and SFV checksum file.
The text file is available from Spectrum Computing:

https://spectrumcomputing.co.uk/index.p ... 6&id=30381

Unless you mean a different text file?

The SFV checksum file is not provided because, frankly, it's not needed. A SFV checksum doesn't work as proof of authencity (anyone modifying your file on purpose could easily make careful changes to produce the same SFV checksum). Therefore SVF checksum only helps detect corrupted files. However all playable files referenced in ZXDB are always zipped, and the zip format already contains corruption detection.

moroz1999 wrote: Sun Apr 15, 2018 6:20 pmWhere have you got this copy from? The problems with throwing out the info files are:
We didn't throw out files, except for the SVF checksum that would have no purpose here.

Both the TAP and TXT files are available from the same Spectrum Computing. Keeping them accessible individually from the webpage, readable by just clicking on it, actually increases the chance players will actually read it.

moroz1999 wrote: Sun Apr 15, 2018 6:20 pm * The new version of release file appears even if there are no changes in the game. That's what really bothers me.
I'm sorry, but I don't know what you meant here...

moroz1999 wrote: Sun Apr 15, 2018 6:20 pm * Some authors can get really angry because of this. I can remember a lot of bad blood between zxaaa.net archive and demoscene authors because of release text files being throwing out from original archives on zxaaa.net.
Again, we are not throwing out any text files. I doubt any authors would get angry just because we are providing individual links to all their files.

Notice that even ZX-Art allows downloading these files separately:

https://zxart.ee/mobile/eng/software/ga ... 7-v104tap/

moroz1999 wrote: Sun Apr 15, 2018 6:20 pmI know it's easy to update Crystal Kingdom Dizzy files, but are there more? Please check.
Yes, there are about 100,000 more files, with playable files available for download separately from instructions (although they are always displayed together in the same page). Most of these files were inherited from Martijn's original WoS archive, where they always got distributed this way. AFAIK authors never complained about it in 2 decades of WoS existence!

Perhaps a different organization would work better, but frankly it's too late to change it now.
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: ZXDB and ZX-Art

Post by Einar Saukas »

moroz1999 wrote: Tue Apr 17, 2018 11:10 pm So, having entry_id + release_seq as a GUID for ZX-Art release won't help really. It seems like I have to use something like:
md5 (entry_id + release_seq + file_link).

Why? Because of architecture differences: ZXDB can have more than file download for release, and ZX-Art cannot. Why? For example, let's take Castlevania. It's being stored as 1 Entry, 1 Release and 11 downloads. In ZX-Art I would like to store 1 Entry and 11 Releases, each release having one only download file, since each release would have it's format, it's supported hardware and supported language.
1. Whatever you have just called "Release", can you rename it to "Download"?

Now your phrases will read like this:

"ZX-Art cannot have more than FILE for each DOWNLOAD. In ZX-Art I would like to store Castlevania as 1 ENTRY and 11 DOWNLOADS, each DOWNLOAD having one only FILE, since each DOWNLOAD would have it's format, it's supported hardware and supported language."


2. Now create a new class called "Release".

It should be like this:

Each ENTRY can have many RELEASES. Each RELEASE can have many DOWNLOADS. Each DOWNLOAD can have just one FILE, since each DOWNLOAD would have it's format, it's supported hardware and supported language.
User avatar
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

Re: ZXDB and ZX-Art

Post by moroz1999 »

We just have different views on "releases". For me each release is a different version of the same software. My arguments are:
1. This architecture decision fits better to idea of importing different versions from different sources.
2. One release can only have one file archive. It can surely have some additional materials as screenshots, cover art and so on, but there is only one file which can be started up in emulator.
3. Releases have languages, not the downloads have. For example, what is a supported languages set for cover art? For *.ay file? Or what supported hardware can cover art have? That's just a signal that may be supported hardware/language are a part of release, not download, since all releases can have supported hardware naturally, and supported hardware is not applicable to some types of downloads.
4. Release can only have a single version number. Each time authors make a new release (as with Pro Tracker or Burial Graphics Editor), a new release should be done (or a current one updated with new info), but not combining different versions in single release (as with Castlevania).

May be I should have named it "versions" not "releases". That would fit my concept idea much better.

I'm not telling that's the only "right" way to build software archive architecture, just a bit alternative way to look on things. And I've written it just to demonstrate that integer PK in most cases is not redundant, it's really practical as a GUID for integration. Much more practical than md5 (entry_id + release_seq + file_link) or simply (entry_id + release_seq + file_link).
User avatar
kolbeck
Manic Miner
Posts: 309
Joined: Mon Nov 13, 2017 9:04 pm

Re: ZXDB and ZX-Art

Post by kolbeck »

moroz1999 wrote: Wed Apr 18, 2018 9:51 am 2. One release can only have one file archive. It can surely have some additional materials as screenshots, cover art and so on, but there is only one file
I don't agree on this one. One release can have multiple files (or downloads as Einar mentions) - for example the same release can be available in the following, but not limited to, formats:
.tzx file
.tap file
.z80 snapshot
.sna snapshot

...and all the other emulator formats.

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

Re: ZXDB and ZX-Art

Post by moroz1999 »

Aren't these separated releases then? If I make a program and decide to support different formats, then I compile it separately for each platform, it has differences in code, so I make different versions which are independent from each other and can be used independently. They can even have different bugs in result! So imho they should be separated, so they could be commented separately. That's not a really big deal though.
I understand your logic, but again, it seems like we are calling the different things with the same name. Your logic is based upon a fact that different "versions" are "released" simultaneously by the same author, I understand it.
User avatar
kolbeck
Manic Miner
Posts: 309
Joined: Mon Nov 13, 2017 9:04 pm

Re: ZXDB and ZX-Art

Post by kolbeck »

moroz1999 wrote: Wed Apr 18, 2018 12:58 pm Aren't these separated releases then? If I make a program and decide to support different formats, then I compile it separately for each platform, it has differences in code, so I make different versions which are independent from each other and can be used independently. They can even have different bugs in result! So imho they should be separated, so they could be commented separately. That's not a really big deal though.
I understand your logic, but again, it seems like we are calling the different things with the same name. Your logic is based upon a fact that different "versions" are "released" simultaneously by the same author, I understand it.
I don't see emulator formats related to releases or platforms. Lets take an example, Head over Heels was released by Ocean (lets call that release 1) - and later it was re-released by Hit-Squad (with new inlays and maybe changed text in the game) - lets call that release 2.

So.. back in beginning with emulators, the ONLY way to save was by generating a .SNA or .Z80 emulator snapshot file - in this case, two different formats by the exact same game/release. Later on emulators improved and .TAP file format was invented, which in this case just adds another file to the exact same game. Today we have a bunch of formats, but back then it was only released as release 1. Loading up the formats in the emulators would (in theory) result in the exact same code across all formats) - This is why I talk about different downloads for the same release.

That said, you might have a point when we are talking distribution media - for example tape vs microdrive vs disk. Thundercats where released on tape and on +3 disc, today they are just different downloads of the same game, but maybe they are separate releases or versions?

/T
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: ZXDB and ZX-Art

Post by Einar Saukas »

moroz1999 wrote: Wed Apr 18, 2018 9:51 amMay be I should have named it "versions" not "releases". That would fit my concept idea much better.
Agreed!

We should definitely avoid giving the same name to different things.

Therefore you will have this:
* Each ENTRY can have many VERSIONS.
* Each VERSION can have just one playable file (but perhaps many additional materials as screenshots, cover art and so on). Moreover each VERSION will have it's format, it's supported hardware and supported language.

The question is, will you also implement support for RELEASES? In this case, each ENTRY could have many RELEASES, and each RELEASE could have many VERSIONS.

moroz1999 wrote: Wed Apr 18, 2018 9:51 amAnd I've written it just to demonstrate that integer PK in most cases is not redundant, it's really practical as a GUID for integration. Much more practical than md5 (entry_id + release_seq + file_link) or simply (entry_id + release_seq + file_link).
Frankly, it makes no difference. Using an arbitrary number ID as PK, or using a number calculated from composite natural keys as PK, is just a technicality that affects the size of the PK you need to store, that's all. You still need to understand, map and handle the same underlying information in exactly the same way.

Anyway I have already restored ID in table DOWNLOADS, thus feel free to use it for integration. I still plan to add CRC32 in this table later but, when I do it, I will preserve these IDs separately (but easily accessible) for you, so it won't affect your integration. OK?
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: ZXDB and ZX-Art

Post by Einar Saukas »

moroz1999 wrote: Wed Apr 18, 2018 12:58 pmI understand your logic, but again, it seems like we are calling the different things with the same name. Your logic is based upon a fact that different "versions" are "released" simultaneously by the same author, I understand it.
Exactly!

These are different VERSIONS that were RELEASED simultaneously by the same author/publisher.

This sentence perfectly fits the concept of VERSIONS in ZX-Art and RELEASES in ZXDB.

Perhaps I should consider to have both RELEASES and VERSIONS in ZXDB. And perhaps you should consider to have both RELEASES and VERSIONS in ZX-Art :)
User avatar
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

Re: ZXDB and ZX-Art

Post by moroz1999 »

Einar Saukas wrote: Wed Apr 18, 2018 2:53 pm Perhaps I should consider to have both RELEASES and VERSIONS in ZXDB. And perhaps you should consider to have both RELEASES and VERSIONS in ZX-Art :)
I'm too tired already to even think about it :lol: :lol:
User avatar
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

Re: ZXDB and ZX-Art

Post by moroz1999 »

I've got a new problem, now with authors. It seems like at some point some label ids have been changed. For example, Alexander Haydukov was earlier 18742, but now he has ID 18743. How do you think, is it possible?

The problem is that I'm using author IDs to link back to Spectrum Computing:
https://zxart.ee/eng/authors/a/akio-tenshi - like here.

I can use label names instead of IDs, since names don't change that much, but would I be able to make a backlink to author's profile in that case?
Please advise.

What I'm trying to achieve: I'm making a large aggregator of all known ZX-related archives, which would incorporate the information from all sources and make it available via web-based JSON API (already done, will post the documentation here soon). This includes disk adaptations, tape cracks, scene demos and so on - everything. Games clearly denied for distribution are not distributed, this is for sure.
User avatar
Joefish
Rick Dangerous
Posts: 2041
Joined: Tue Nov 14, 2017 10:26 am

Re: ZXDB and ZX-Art

Post by Joefish »

On the subject of ZX-Art, aren't hosts of ZXDB expected to secure their own permissions for hosting downloads?
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: ZXDB and ZX-Art

Post by Einar Saukas »

moroz1999 wrote: Wed Apr 10, 2019 9:20 amI've got a new problem, now with authors. It seems like at some point some label ids have been changed. For example, Alexander Haydukov was earlier 18742, but now he has ID 18743. How do you think, is it possible?
Until last year, IDs were dynamically assigned by SQL automatically generated from spreadsheets. Changes to the spreadsheet data always regenerated the SQL thus frequently reassigned IDs.

Since we started using ZX-Ed for all updates in January, this problem doesn't happen anymore (except for download IDs that are not managed by ZX-Ed yet).

Sorry about it!
User avatar
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

Re: ZXDB and ZX-Art

Post by moroz1999 »

Joefish wrote: Wed Apr 10, 2019 12:29 pm On the subject of ZX-Art, aren't hosts of ZXDB expected to secure their own permissions for hosting downloads?
In ideal world - yes. But, unfortunately, there we have not many resources to deal with this question, so the current way is:
1. If something is denied for WoS/ZXDB, then it's not distributed on ZX-Art.
2. If something is undefined for WoS/ZXDB, then it's distributed on ZX-Art the same way as it's distributed on WoS.
3. If something is allowed for WoS/ZXDB, then it's distributed on ZX-Art until the first demand on removal.

That's not ideal, but unfortunately it's either this way or no way at all.
User avatar
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

Re: ZXDB and ZX-Art

Post by moroz1999 »

Einar Saukas wrote: Wed Apr 10, 2019 2:10 pm Until last year, IDs were dynamically assigned by SQL automatically generated from spreadsheets. Changes to the spreadsheet data always regenerated the SQL thus frequently reassigned IDs.

Since we started using ZX-Ed for all updates in January, this problem doesn't happen anymore (except for download IDs that are not managed by ZX-Ed yet).

Sorry about it!
Thank you! No problem, I will just re-import the labels information into existing authors and groups. I will then stick to IDs, so I could properly make a backlink to all known profiles on all known websites.
The main goal of having persistant IDs is having an accurate database covering same persons/groups from official games, crack scene and demoscene. My goal is preserving all the software related to ZX and making it available for backing up/copying where not prohibited by law.
User avatar
Joefish
Rick Dangerous
Posts: 2041
Joined: Tue Nov 14, 2017 10:26 am

Re: ZXDB and ZX-Art

Post by Joefish »

moroz1999 wrote: Wed Apr 10, 2019 8:26 pm In ideal world - yes. But, unfortunately, there we have not many resources to deal with this question, so the current way is:
1. If something is denied for WoS/ZXDB, then it's not distributed on ZX-Art.
2. If something is undefined for WoS/ZXDB, then it's distributed on ZX-Art the same way as it's distributed on WoS.
3. If something is allowed for WoS/ZXDB, then it's distributed on ZX-Art until the first demand on removal.

That's not ideal, but unfortunately it's either this way or no way at all.
So when an item is specifically marked, for example:
"Redistribution of this game is only permitted on sites and services with the explicit consent of the author".
You're not even going to ask a signed up member of your own site if they mind? *ahem* :mrgreen:
User avatar
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

Re: ZXDB and ZX-Art

Post by moroz1999 »

Sorry, couldn't have traced everything :D
I've marked the game on ZX-Art as "denied" currently - there was no intention to make any harm or demotivate anybody, it was automatically imported and since it has an "allowed" status, it was not automatically hidden for downloading/playing online.

This made me thinking about a new type of permission in ZXDB database: someting like "Allowed explicitly on ZXDB", so I could automatically mark such releases as denied on ZX-Art with an external link to a permitted download.
User avatar
PeterJ
Site Admin
Posts: 6854
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: ZXDB and ZX-Art

Post by PeterJ »

[mention]moroz1999[/mention]

I will email you a list.

Peter
User avatar
Joefish
Rick Dangerous
Posts: 2041
Joined: Tue Nov 14, 2017 10:26 am

Re: ZXDB and ZX-Art

Post by Joefish »

I didn't mean that! I just meant you could have asked!
Argggh!

Yes, OK, you can host Buzzsaw+ (Foxton Locks Mix) and Buzzsaw+ (Dim Sun Edition) on ZXArt. I like the site!
But please post the same message about downloading and hosting permissions.

You probably have other members who are coders you could ask too...
User avatar
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

Re: ZXDB and ZX-Art

Post by moroz1999 »

Seems like releases.releases_seq has been changed after all? So my key strings based on entry_id + release_seq are now wrong and pointing to wrong releases?
This is why ID number is better than some combinations of properties. You can assign another entry_id, you can rearrange releases by changing release_seq, but releases would have stayed the same.
I will now have to figure out the way to reassign automatically all releases from ZXDB to releases on ZXArt. And that's not really easy task, because I cannot just delete everything and create it from scratch: the information has already been aggregated from other databases and sources.
I think I will stop rechecking the whole database and only add the new entries, that's too frustrating to newly invent the same wheel after almost every update of ZXDB.
Post Reply