Database model ZXDB

17810121330

Comments

  • Einar, is there any chance you could write a very short update of what has changed or been added each time you upload a new version?

    Every new update corresponds to a new file number inside package "ZXDB_import.zip". Each file contain comments describing the changes. Thus the information you requested is already available! :)
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • That's super. Thank you!
  • Einar, if I may ask... Are you doing it now just art for art's sake? I remember that Lee said he won't be using it
    Or are there some concrete plans for future about using it?
  • It's worth keeping alive for people to practice connecting, displaying data, etc - I know at least one person making use of it as he's asking me questions. I can work something into WoS so that these sites connect to the central DB when it's live, so it's not all dead. Einar will also be able to re-create the DB from WoS data, so what he has here is a portable version. Someone will find it useful :)
    My test signature
  • +1 for keeping alive. Once the final design is complete, the data cleansed and subject to Einar's permission I'm hoping to fork it into SQLite and drive the ZXSR site from it. So essentially the site will be driven from two database files - ZXDB for all of the "WoS" info and the current database for the reviews text. If ZXDB is kept up to date so much the better as it'll just mean converting to SQLite and dropping straight into the sites data folder and it'll just work :-)
  • Ralf wrote: »
    Einar, if I may ask... Are you doing it now just art for art's sake? I remember that Lee said he won't be using it

    Of course Lee will use it!

    The day after I started ZXDB, Lee posted this was exactly the database he had planned himself:
    That's pretty much the database plan in place - so well done... it shows I was doing the right thing!
    Importing isn't massively easy - there is some cleansing to be done - and there was a plan in place for the community to be involved with data checking.

    Also Lee didn't have done this work yet (except publishers) since he was working instead on management tables not covered by ZXDB, which means our efforts complemented each other:
    Currently the only database tables we have are the publishers as I have been concentrating on the core modules such as user permissions, notifications of changes, change requests, etc - all of the modules that will be re-used.

    Moreover Lee has been asking for ZXDB changes to fit his plans for WoS (such as folder name slugs). He wouldn't be asking me to change ZXDB for him if he was going to throw it away.

    Finally there's the fact that original WoS archive had lots of problems I have fixed already. It would make no sense to discard ZXDB and reinvent the wheel. What makes you think Lee would do it?
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • Mostly right :) The tables are already set up, and there is an import script that sorts the data out. That's part of the management bits. What I hadn't done was test the import script as I went back to add the user verification scripts to publishers ready to copy over. At that point, I stopped.

    It might have been a bit misleading with "the only tables we have" should have been "the only populated tables we have"

    I'll be using the cleaned data and converting to the WoS format - after that, I will get together with Einar to work out the best way of using ZXDB moving forward. It will probably be a case of dumping JSON files into ZXDB. Although there are numerous other methods we can use. On the bright side, I don't have to test the import scripts as Einar has done that work :)
    My test signature
  • Finally there's the fact that original WoS archive had lots of problems I have fixed already. It would make no sense to discard ZXDB and reinvent the wheel. What makes you think Lee would do it?

    I was according to this:
    http://www.worldofspectrum.org/forums/discussion/comment/875952/#Comment_875952

    jonesypeter wrote: »
    The only point on your post I can't agree with is in regard to zxdb. It's a real pity that you can't use Einars (and others hard work) and make this freely downloadable to anyone who wants it. I know there will be APIs in the future but it's just not the same.

    The problem is that the field names/some formats/details will be incompatible as the new WoS already has a structure. There are also fields and tables to account for a huge amount of additional data planned. It's not a show stopper for ZXDB, but I will have to chat to Einar closer to going live about the best way to keep ZXDB going.


    But maybe I misunderstood something. Good to see that you are cooperating anyway. And I'm not really that curious about all technical details, I'd just like to see some visible progress instead of being chased off like some annoying fly.

    Einar certainly does it right, you can download new versions of database, see the progress even if you don't understand everything. So keep the good work mate! :)
  • My dog likes chasing annoying flies. Caught one just yesterday! Just shout out if you want this service :))

    Isn't it always better when people work together :)

    Mark
  • Ralf wrote: »
    Einar certainly does it right, you can download new versions of database, see the progress even if you don't understand everything. So keep the good work mate! :)

    Thank you!!!
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • Interesting number of misquotes on twitter - that didn't take long. Heyho.

    Some of the things to work out is what exactly ZXDB will become. With the new structure we have, WoS goes from being tiny to quite a lot bigger. So what do we export and when? Does ZXDB need magazine reviews? When we start adding users comments in phase 2, are they included? Votes? Or do you just want what's there now?

    For the magazine reviews, they will eventually replace SPOT - do we keep SPOT going and export results to that database?
    My test signature
  • Does ZXDB need magazine reviews?

    It's already there. Table "magrefs" stores all magazine references to programs including news, previews, reviews, tips, maps, type-ins, adverts, etc.

    This table is only partially populated because it depends on Gerard's classification of magazine issues. As soon as I get this information from him, the entire content of temporary tables "tmp_magrefs" and "wos_topicrefs" will be moved into "magrefs" also.
    When we start adding users comments in phase 2, are they included? Votes? Or do you just want what's there now?

    Perhaps. I prefer to concentrate on organizing existing information now, worry about extending the database as needed later. When a database is properly designed, modifying it later to add new features is quite easy.
    For the magazine reviews, they will eventually replace SPOT - do we keep SPOT going and export results to that database?

    No, I'm already merging everything. SPOT and SPEX are already dead, nobody is updating them independently anymore, so maintaining parts of data split into separate databases would just complicate development unnecessarily.
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • The point about the reviews is that text versions will also be stored, so SPOT eventually isn't needed as the search can be set to include references. Plus it will be easier to read reviews on mobile devices. Other details we are adding will be straight away.
    My test signature
  • A new ZXDB version is available already. Download it from here.

    Besides several improvements, this new version of ZXDB also contains most "original prices" moved to table "entries", just to make jonesypeter happy :)

    @Lee: There's also a separate "add-on" file, that must be executed separately after importing the database. It will create a new table "folders" containing the slugs you requested. Notice I created 3 different slugs, just let me know the slug you prefer and I will remove the others. Or if you had in mind a different kind of slug, let me know the details and I will change it accordingly!
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
    Thanked by 1jonesypeter
  • Thanks Einar. I'm very happy!
  • ...Just a quick note to say I haven't got a clue about most of what you guys are talking about, but keep going Einar, sounds like good work..! :)
  • Morkin wrote: »
    ...Just a quick note to say I haven't got a clue about most of what you guys are talking about, but keep going Einar, sounds like good work..! :)
    Think of it as organizing data on sheets of paper. What's the most convenient set of forms to contain the data, what fields should exist for what form, etc. Some sheets may refer to others for further information. In the end you can ask the sql server questions like "how many games were released in 1983", where, like in paper version, the server goes through all the forms and tells you how many it found.
    http://iki.fi/sol | http://iki.fi/sol/speccy/ | https://github.com/jarikomppa/speccy
    http://goo.gl/q2j0NZ - make ZX Spectrum choose your own adventure games, no programming needed (release 3)
  • Morkin wrote: »
    ...Just a quick note to say I haven't got a clue about most of what you guys are talking about, but keep going Einar, sounds like good work..! :)

    Thanks!

    I guess it would be a good idea to summarize now what's exactly going on, for people like you that is not familiar with databases. It should help answer questions like "Why is it taking so long?" and "Why is there still work left to do since everything is already inside the database?" :)

    In a nutshell, importing everything from WoS archive to ZXDB wasn't too much work. The task that really takes time is fixing WoS inconsistencies and reorganizing data in such a way that database constraints will prevent inconsistencies from happening again when it starts getting updated with new information in the future.

    ZXDB now contains 27,876 entries (i.e programs, books, and hardware devices). About 1,000 entries were previously "lost" (not properly indexed) in the WoS archive. For instance, try to click on "Blood Dungeon" at this link:

    http://www.worldofspectrum.org/infoseekid.cgi?id=0018957

    I have already fixed all these problems in ZXDB.

    Right now, ZXDB contains 15,981 labels (i.e. companies, publishers, developers, etc). About 1,300 of them were missing from the WoS archive, another 200 had some kind of error that I investigated and fixed. For instance I already mentioned "Waner Brothers":

    http://www.worldofspectrum.org/infoseekpub.cgi?regexp=^D.C.+Comics+Inc$

    I will eventually compile a full list of all inconsistencies I found and fixed, but I'm still finding and fixing more problems along the way so I prefer to put together this list at the end, when I'm done.

    ZXDB now contains "organized" references to 105,979 download files (associated with entries) and 3,014 company files (associated with labels). About 4,800 of them were previously "lost" inside WoS archive (not associated with anything), I have fixed them all. There are still 2,131 more "lost" files left to be organized, I'm currently working on that.

    ZXDB now contains 1,964 "revised" magazine references, each one associated with an specific program and magazine issue. This information was already in WoS archive but without consistency check (including references to magazine issues that didn't exist). There are still another 264 magazine references to be revised, as soon as Gerard finishes to catalog all issues of each magazine.

    Other already organized information in ZXDB includes 4,090 aliases (alternative program titles), 502 type-ins from books, 249 interviews, 2,574 magazine scans, 1,737 company and author permissions, 2,398 links to Speccy games on other websites, 6,886 links to Speccy titles also available on other platforms, 1,162 licensed IPs from 1171 licensors, 493 recent Speccy title remakes, etc. All this information came from WoS archive but it wasn't easily available, now everything has been properly cataloged in ZXDB.

    Besides the WoS archive, ZXDB also contains all information from SPOT/SPEX archives. They contain 16,042 items, so far I have mapped 13,028 entries and 698 labels, the remaining 2,316 items are still unknown. After I finish mapping them all, I will proceed to "merge" this information into ZXDB. The SPOT/SPEX archive also contains 206,107 magazine references, I'm also depending on Gerard's magazine issues catalog before "merging" this information into ZXDB.

    Here's a good example for the kind of data reorganization I'm doing. A few games were re-released several times by different publishers, for instance "Everyone's a Wally":

    http://www.worldofspectrum.org/infoseekid.cgi?id=0001677

    Each re-release had a release date, release price, sometimes even a different title. WoS archive stored only "original price" and "budget price" so we don't know which re-release that corresponds to this information. Also WoS archive stored individual dates and alternate titles for each file, instead of re-release dates and titles. This is a problem because sometimes exactly the same game re-release contains 2 different dates in WoS, for instance:

    http://www.worldofspectrum.org/infoseekid.cgi?id=0000454

    Since SPOT/SPEX is organized differently (each re-release has its own original price although it's limited to 3 releases), I need to reorganize this information first before I can combine WoS and SPOT/SPEX archives. And of course both archives have duplicated information, or different information missing, so it's kind of a puzzle to find out how those incomplete pieces of data fit together...

    So now you know what's taking so long! :)
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • Thanks for the detailed explanation, Einar - much appreciated!

    You are doing such a great job with all your work on ZXDB. It sounds like you've added some really useful new features (such as multiple release dates for re-issues etc rather than just one per entry), it's extremely future-proof and on top of all that you're tracking down and fixing existing errors too!

    I'm feeling optimistic for the first time in quite a while :)
  • richl wrote: »
    Thanks for the detailed explanation, Einar - much appreciated!

    You are doing such a great job with all your work on ZXDB. It sounds like you've added some really useful new features (such as multiple release dates for re-issues etc rather than just one per entry), it's extremely future-proof and on top of all that you're tracking down and fixing existing errors too!

    I'm feeling optimistic for the first time in quite a while :)

    Yeah, what Rich said :-)

    Mark
  • Thanks! :)
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • edited September 2016
    Just a quick update on my ZXDB powered site:

    Visit it at spectrumcomputing.co.uk/searchform.html

    You can now search for a title, and a bootstrap powered table is produced. Click on the link and you get the beginnings of an info page included loading screens, in-game screens, known issues, and a list of publishers..

    Thanks of course to Einar, and also to Ricardo who has been helping me with the development of Bootsrap for a future release.
    Post edited by jonesypeter on
  • Yet another database update! Download it here.
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • edited September 2016
    Yet another database update! Download it here.

    Will you include the new releases from Timmy's threads? I would have thought that information could be collated and reformatted relatively easily, so we could finally have an updated archive.


    Just a quick update on my ZXDB powered site:

    Visit it at spectrumcomputing.co.uk/searchform.html

    Great stuff.


    Post edited by Rorthron on
    spectrumcomputing.co.uk/forums
    Thanked by 1jonesypeter
  • Rorthron wrote: »
    Will you include the new releases from Timmy's threads? I would have thought that information could be collated and reformatted relatively easily, so we could finally have an updated archive.

    I must admit I haven't been following this thread, but as far as I hear, there are plans to do update the database with newer titles (that can be done without my posts).

    Don't worry about it, but you will know after this has been implemented, but whatever way that is, it's won't be "relatively easily" and more like "me doing a lot of manual data entry".
  • Timmy wrote: »
    Don't worry about it, but you will know after this has been implemented, but whatever way that is, it's won't be "relatively easily" and more like "me doing a lot of manual data entry".
    It'll be relatively easy compared to the way Martijn used to do it.
    I wanna tell you a story 'bout a woman I know...
  • edited September 2016
    Timmy wrote: »
    I must admit I haven't been following this thread, but as far as I hear, there are plans to do update the database with newer titles (that can be done without my posts).

    Don't worry about it, but you will know after this has been implemented, but whatever way that is, it's won't be "relatively easily" and more like "me doing a lot of manual data entry".

    My thinking was that the information in your thread could be copied and pasted into, say, a CSV file with suitable field names and then imported into ZXDB. It would be manual, but not very labour intensive. I'd probably be willing to do it myself.

    Post edited by Rorthron on
    spectrumcomputing.co.uk/forums
  • Hi, I would be happy to start saving the loading screens and in game screens for the newer games if that would help.
  • Rorthron wrote: »
    Will you include the new releases from Timmy's threads? I would have thought that information could be collated and reformatted relatively easily, so we could finally have an updated archive.

    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. He will probably appreciate the help offer! :)
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • edited September 2016
    Rorthron wrote: »
    My thinking was that the information in your thread could be copied and pasted into, say, a CSV file with suitable field names and then imported into ZXDB. It would be manual, but not very labour intensive. I'd probably be willing to do it myself.
    Let's just say that it's more complex than you think, but perhaps it's easier to explain this in a PM.
    Hi, I would be happy to start saving the loading screens and in game screens for the newer games if that would help.
    I've also started collecting screenshots since my Twitter days. There are also at least 2 images stores for new games already that I know of. I cannot say how likely it is these will be incorporated soon. But if you want to collect screens, that's fine too.


    EDIT: I must say, my collection is mainly GIFs and not SCRs. Maybe I should try that too.
    Post edited by Timmy on
Sign In or Register to comment.