Database model ZXDB

1101113151630

Comments

  • RMartins wrote: »
    Anyway, for preservation purposes, data should be kept as close to original or "canonical" form as possible.

    This is where I strongly support the opinion. If I accepted PNG/GIF upload for images on ZX-Art, then a lot of information would be lost:
    1. There are hidden pixels in images which have cultural value.
    2. Sometimes "emulation" changes. For example, if palette (bright/non-bright levels) would be corrected into more accurated RGB values, then web images would be able to be reconverted automatically from original source files.
    3. Same goes for music. MP3/OGG are fine, but they can be really wrongly emulated, so if player/converter is updated for a more accurate emulation, then automatic correction of MP3/OGG files cache should be possible, which requires original files.
    4. Some ZX Spectrum machines already have Internet. Losing original SCR files will make archive impossible to be browsed from real hardware.

    RMartins wrote: »
    For the special multicolor cases, 8x1, 8x2 and 8x4 SCR can be easily extended so that it remains compatible with previous existing SCR files, by just appending the extra attributes after the end of a regular SCR.

    Any suitable reader can determine the exact format (8x1,8x2 or 8x4) by just checking the file size.
    NOTE: Default readers will just see a regular SCR.

    Extra border effects could also be built into some form of "canonical" format for them (like SCR is for regular screenshots.

    I've made a solution from ZX-Art available as free open source class.
    http://www.worldofspectrum.org/forums/discussion/53382/php-class-for-zx-spectrum-images-parsing-converting
    Briefly, it uses both GIF and PNG for different cases. GIF for flickering modes or flash, PNG for simple cases and high-color.
    Yes, it supports file cache and automatically clears outdated files every N seconds.

    There is no need to invent the wheel with extra formats: there are many formats already in use, for multicolor, for extra-size (more than 256*192, with asm viewer existing as well), for gigascreen, for multicolor+gigascreen, for border images, for border images with multicolor, for ula+.
  • Creating PNG files on the fly is an incredibly bad idea on a busy site.
    My test signature
  • Creating PNG files on the fly is an incredibly bad idea on a busy site.

    so cache them once they exist.

    You're so wrong on this one, Lee: there is valuable source data in scr.

    Transform *a copy* at upload time for display by all means: no one wants to make viewing difficult. But the source data should be preserved. How is this a problem?
  • Creating PNG files on the fly is an incredibly bad idea on a busy site.
    You can cache the result, or you can write some queue to convert images nightly, or you can prerender it after upload - it's up to your needs and situation. Anyway, there is now a converter component publicly available for everybody.
  • Creating PNG files on the fly is an incredibly bad idea on a busy site.

    so cache them once they exist.

    You're so wrong on this one, Lee: there is valuable source data in scr.

    Transform *a copy* at upload time for display by all means: no one wants to make viewing difficult. But the source data should be preserved. How is this a problem?

    I've already said there is a need for some SCR files. Not one per in-game screen though as it's overkill, and people have asked for a gallery - that gallery should be png/jpg. If there is to be multiple SCR files, they should be zipped as one download.
    My test signature
  • I'd maybe recommend SCR for loading screens and PNG for any in-game screens then. JPG for inlays and stuff only.
  • In the meantime... I uploaded yet another ZXDB update here.

    Note: don't forget to load file ZXDB_clean as UTF8 into your favorite SQL client, otherwise you will see some weird characters appearing into fields like original_price!
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • And ZXDB was updated again here.
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
    Thanked by 2jonesypeter 4throck
  • In the meantime... I uploaded yet another ZXDB update here.
    Note: don't forget to load file ZXDB_clean as UTF8 ...

    Thanks again Einar!
    And if we talk about character encoding, does your database support national letters?

    Old WOS generally didn't support them. For example all local Polish letters like ą ć ń ł ż were replaced by English equivalents a c n l z. And other alphabets like Russian one were not supported at all.

    Martijn had in plans to deal with it one day but he didn't and now he's got other priorities :)

    And of course, it would be silly to store Russian game titles written with Russian alphabet as 90% of users here wouldn't be able to make a head or tail of it. But maybe such extra field in database as "original title" could be useful? It would enable everybody to search for the programs using the exact name as they were originally called.
  • edited September 2016
    Ralf wrote: »
    Thanks again Einar!

    You are welcome!

    Most of WoS archive has been converted and organized already. There are about 1,700 "lost" files still left to organize (that I'm slowly analyzing and organizing almost one-by-one right now), a large number of magazine references and scans (that I should be able to process quickly as soon as Gerard finishes his full magazine issues catalog), and I'm half-way on merging SPOT/SPEX into WoS.

    Ralf wrote: »
    And if we talk about character encoding, does your database support national letters?

    Yes!

    The entire database is stored in UTF8 so it supports all special characters. Rendering pages as UTF-8 (per HTML5 standard) should be enough to display everything correctly.

    Ralf wrote: »
    And of course, it would be silly to store Russian game titles written with Russian alphabet as 90% of users here wouldn't be able to make a head or tail of it. But maybe such extra field in database as "original title" could be useful? It would enable everybody to search for the programs using the exact name as they were originally called.

    No problem! ZXDB already supports "aliases" for all titles.

    I suggest keeping main title using "regular" characters and adding an alias with Russian characters, so both searches will work, and both titles will be displayed in the game page, although only the first title will appear in game listings.
    Post edited by Einar Saukas on
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • I'm very impressed, good work.

    I have a domain (http://tabulasinclair.org/) with some space, and I'd like to offer help with hosting. Hopefully, this would make it easier to develop, for people to access and to help validate it (while the new WoS is not yet available).

    If you think it's a worthwhile idea (and you agree with it) then let me know. Thanks.
  • edited September 2016
    Jmk wrote: »
    I'm very impressed, good work.

    I have a domain (http://tabulasinclair.org/) with some space, and I'd like to offer help with hosting. Hopefully, this would make it easier to develop, for people to access and to help validate it (while the new WoS is not yet available).

    If you think it's a worthwhile idea (and you agree with it) then let me know. Thanks.

    Thank you for your offer! I see no problem at all. Right now jonesypeter is already hosting ZXDB at spectrumcomputing.co.uk so there's already a precedent... it wouldn't make sense to give him permission but deny you :)

    However notice you will probably be doing duplicated work, since he has already implemented a few pages to validate ZXDB queries. I recommend you contact him to see how you both can work together and complement each other's efforts to avoid duplicated work. I will give you his email in a PM.


    EDIT: Fixed mispelling.
    Post edited by Einar Saukas on
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • Really nice progress!

    BTW, will new missing titles be imported into the ZXDB? (from years 2013-2016)
  • Pavero wrote: »
    Really nice progress!

    Thank you!

    Pavero wrote: »
    BTW, will new missing titles be imported into the ZXDB? (from years 2013-2016)

    Yes!

    Hikoki is currently working on it. He's putting together a spreadsheet with more detailed information based on Timmy's list and already prepared an input form to fill it.

    As soon as he finishes this work, I will import the spreadsheet data into ZXDB.
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • Hi all. I'm very some very specific help with my site. Would anyone with good PHP skills be able to add to my search results some pagination? I'm using MySQL 5.7.15 & PHP7. Its hosted on Digital Ocean so I can provide you with the current code or give you access to the server. The code would need some documentation so I can implement it across the site.

    At the moment I'm restricting search results to 25 rows, but I want to start linking to results by year, author, software house and genre and I don't want to kill the database! I also plan to show screen shots in searches.

    Having pagination will help me move forward with development.

    If you can help then your name will be featured on the home page, and in a future page of credits. One final thing, all the source code is open source, so you need to be agreeable to your code being shared.

    Thanks in advance.
    Thanked by 1Stefan Drissen
  • Once again, there's another ZXDB update here!
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • edited September 2016
    Thanks to support from Ricardo Nunes, there has been another update to the Spectrum Computing website (Use the link in my signature). The site is now fully Bootstrap enabled and looks much better.

    I've also had offers of help for adding the pagination (details of that in the next update). There is still much to do, but we are very pleased with the results so far.

    Just to say again, that this project is not there to replace WoS, and never will. Its purpose is to show what is possible with ZXDB and in near future will enable the searching of post 2013 games, whilst the new WoS is being built.
    Post edited by jonesypeter on
  • edited September 2016
    I feel glad to help :)

    Just to add that the layout is quite basic, so that stuff can be moved around as the site is being developed.

    The mobile version has some problems (long table lines, fixed size images, etc).
    I'm aware of that, and they will be fixed latter on.
    And yes, the font and color scheme will change to something more custom in due time ;)

    Ricardo
    Post edited by 4throck on
    Thanked by 2jonesypeter 1024MAK
  • By the way, if you ever have a couple of days off, can I borrow your time machine. I would enjoy a trip to the future :D

    Mark
  • Einar, Peter, Ricardo - great work all round.
  • Just noticed, don't you have programs labeled as "ADULT" in ZXDB?
  • edited October 2016
    Ralf wrote: »
    Just noticed, don't you have programs labeled as "ADULT" in ZXDB?

    Yes, I have excluded them from the website until I get around to.programming a filter. They are listed in ZXDB, but there is a flag in the entries table which I use in my SQL.

    Post edited by jonesypeter on
  • ZXDB was updated again here.
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
    Thanked by 1RMartins
  • And it was updated again here.

    In the last few updates, I have been focused on organizing magazine references to Spectrum games (mostly from SPOT/SPEX). So far, I have organized about 106,000 magazine references. There are still another 102,000 magazine references left to organize...
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • @Einar, I've looked at your recent update. Downloaded and imported the ZXDB_clean.sql into a MySQL database to take a look. I'm seeing lots of empty fields, I guess this is work-in-progress.

    I'm firstly interested in the file structure: There's two files: ZXDB_clean.zip and ZXDB_import.zip. What's the purpose of both? ZXCD_import has a list of sql files, any info as to what's in what file. I'm guessing that some of them are what ends up in ZXDB_clean.sql, and the others are the original data?

    Like: 05_zxdb_gen.sql, 06_zxdb_gen.sql, 08_zxdb_adjusts1.sql, 11_wos.sql

    I guess 11_wos.sql is the end result of converting Martijn's original data into SQL inserts, and that's being used as a starting point for cleaning up and putting the data into a ZXDB_clean schema.

    Thanks
  • I've skimmed through the tables created by ZXDB_clean.zip making notes as to what they do. The primary objects seem to be: entries and labels (publisher labels, which includes authors...), the other tables are then sub-objects, lookup tables and references between entries and labels/authors:
      * aliases -- other titles an entry is released under
      * authorings -- reference between `entries` and `authors`
      * authors -- authors, but names are delegated via `label_id` to `labels` and `wos_publishers`
      * availabletypes -- distribution status
      * booktypeins -- sub-entity of entries; maps type-in listings to their source.
      * compilations -- reference between compilation entries and individual entries; groups entries in the same compilation.
      * controls -- reference between `entries` and `controltypes`
      * controltypes -- lookup table of control types, 1 item per control type.
      * countries -- lookup table of country codes
      * downloads -- download items for an entry (images, tzx...).
      * entries -- a product entry.
      * entrytypes -- the entry type; looks like a product genre.
      * extras -- another set of downloadable items for an entry
      * filetypes -- lookup table for downloads and extras
      * groups -- groupings of entries (e.g. sequels, themes)
      * grouptypes -- lookup table for groups
      * idioms -- lookup table for language combinations
      * interviews -- sub-entity of `wos_publisher` for interviews with that person/organisation
      * issues -- an item for each issue of a magazine or periodical.
      * itemtypes -- lookup table for audio/music types
      * labelfiles -- references ownership of files to their labels
      * labels -- publisher labels, includes parent ownership. contains developers and authors too.
      * licenses -- media licenses items
      * licensetypes -- lookup table of the license types (Movie, Brand, Sports event...)
      * licensors -- maps licenses to the owner labels.
      * machinetypes -- lookup table of Sinclair machines
      * magazines -- list of magazines
      * magfiles -- files associated with magazines (cover picture, posters)
      * magrefs -- mapping references to entries in magazine issues.
      * members -- mapping entry members to groups
      * origintypes -- lookup table for origin of the digitised version
      * permissions -- statement of permission from a label
      * permissiontypes -- lookup table for permissions
      * platforms -- lookup table of other computer platforms
      * ports -- ports of an entry to other platforms
      * publicationtypes -- lookup table of publication types (where used?)
      * publishers -- publishers, but delegated via label_id to ()
      * referencetypes -- types of magazine references
      * relatedlinks -- references of entries on other websites (e.g. wikipedia)
      * releases -- a reference table of releases of an entry (but only the year of the release)
      * remakes -- entries that have had remakes
      * roletypes -- lookup table for author roles.
      * schemetypes -- lookup table of schemetypes
      * tmp_files
      * tmp_icontypes
      * tmp_magrefs
      * tmp_magscans
      * tmp_nvgs
      * tmp_utilities
      * turntypes -- lookup table for entry turn types
      * utilfiles -- links to utilities downloads
      * utilities -- of preservation utilities, not released Spectrum software
      * websites -- lookup table of external websites
      * wos_feature -- a table of articles/references?
      * wos_hosts -- references to websites that ???
      * wos_magazines -- list of published magazines (looks like magazines)
      * wos_publishers -- developers, publishers, authors
      * wos_refcodes -- lookup table of reference codes (looks like referencetypes)
      * wos_specials -- lookup table of miscellaneous tags
      * wos_topicheaders -- case-insensitive search index
      * wos_topicrefs -- reverse search tree mapping wos_topicheaders to an entry id(?)
      * wos_types -- lookup table of entry types (looks like entrytypes)
  • edited October 2016
    Clean is for a fresh installation, and import is for updating an existing DB. Each time an update is created the clean db is replaced and an incremental update script is put into imports.

    Entries is indeed the main table. If you look in PHPMyAdmin you can see a graphical representation of how the tables link.
    Post edited by jonesypeter on
  • Each time an update is created the clean db is replaced and an incremental update script is put into imports.

    Ah, that explains why the `wos_*` tables are needed in clean. thank you.

  • 智慧_ wrote: »
    @Einar, I've looked at your recent update. Downloaded and imported the ZXDB_clean.sql into a MySQL database to take a look. I'm seeing lots of empty fields, I guess this is work-in-progress.

    Yes it's WIP. Even so, there are not lots of empty fields... Which fields do you mean?

    智慧_ wrote: »
    I'm firstly interested in the file structure: There's two files: ZXDB_clean.zip and ZXDB_import.zip. What's the purpose of both?

    ZXDB_clean is the latest version, ZXDB_import is the incremental package containing the initial database import, plus all changes applied to them.

    Next time I will rename them to ZXDB_latest and ZXDB_incremental, it will help explain their purpose.

    智慧_ wrote: »
    ZXCD_import has a list of sql files, any info as to what's in what file. I'm guessing that some of them are what ends up in ZXDB_clean.sql, and the others are the original data?

    Like: 05_zxdb_gen.sql, 06_zxdb_gen.sql, 08_zxdb_adjusts1.sql, 11_wos.sql

    I guess 11_wos.sql is the end result of converting Martijn's original data into SQL inserts, and that's being used as a starting point for cleaning up and putting the data into a ZXDB_clean schema.

    No.

    Files 1 to 6 contain the conversion of Martijn's WoS archive. It was originally stored as a disjoint set of files, so I wrote a converter program to cross reference them, apply several fixes and produce an initial database with proper relationships.

    File 11 contains Richard's conversion of SPOT/SPEX. This is a separate archive containing mostly magazine references. In WoS archive you can usually see this information at the bottom of the web page, although just a small part of SPOT/SPEX was associated with WoS entries thus most information from SPOT/SPEX was never displayed.

    The remaining files contain incremental changes I'm applying to the database to fix errors and inconsistencies (I mentioned a few examples in this thread), improve data organization (for instance original structure didn't store the exact release corresponding to each download file), to index additional information previously "lost" (inaccessible) inside WoS archive, and to merge SPOT/SPEX information into WoS so there will be a single consistent database.

    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
    Thanked by 1智慧_
  • 智慧_ wrote: »
    I've skimmed through the tables created by ZXDB_clean.zip making notes as to what they do.

    Your list is mostly correct except:
      * authorings -- associates certain entries to authoring tools (such as text adventures created using PAW or GAC)
      * extras -- download files not related to any entry (program, book, etc) or label (author, publisher, etc). For instance WoS archive contained music files for CPC...
      * interviews -- lists any interviews provided by certain labels (author, publisher, etc)
      * wos_... -- tables from the SPOT/SPEX archive, they will eventually disappear since I'm gradually organizing and moving all this content to the other tables.
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
    Thanked by 1智慧_
Sign In or Register to comment.