Database model ZXDB

13468930

Comments

  • edited August 2016
    or alternatively use separate fields for at most 5 publishers and authors, like Author1, Author2, Author3, etc
    I cant figure out the role thing for each author, not before I have a pc at hand again maybe by mid september, at least I have a tv to watch olympic gals :)
    Post edited by hikoki on
  • hikoki wrote: »
    I cant figure out the role thing for each author

    Here's a good example:

    http://www.mobygames.com/game/zx-spectrum/xyz/credits

    Notice that Bob Smith has 3 roles for the same game (Code, Graphics, Levels), also you have many people with the same role (Levels) in this game. Makes sense?

    However you can ignore roles in ZXDB for now. None of the existing WoS entries have roles. It will be easier to wait until a proper WoS archive update system is implemented before starting to worry about registering this information.
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • its been a csv authors string field per game. I think my next move will be up to five fields per game for authors and their corresponding five csv string field for roles. They can be optional to enter.
    Now minimum model is in the title between brackets, I'll dedicate a new field with more choices for clones (atm,evo,uno..) apart from 16k,48k,128k,48k/128k.
    What about ingame pics and loading screens? I haven't included loading screens so far. Is there a way to extract them automatically?
    Genres.. no present either for now. I can place a multichoice form with all the genres from infoseek.
    I'll have Publishers separated from authors too.
    I had a vote system which is disabled. I guess zxdb will take votes into account.
  • Oh, if anyone else wants the SQL Server database they are more than welcome to pick it up from: https://dl.dropboxusercontent.com/u/24607151/ZXDB SQL Server 2008.zip. It's a SQL 2008 compatible backup from a SQL 2014 engine.

    A little note. Unfortunately it isn't compatible with earlier versions of Microsoft Sql Server. I tried to restore it on Sql Server 2008 R2 and Sql Server 2012 with no success. I believe that's the general pain with this technology, it's usually not backwards compatible.

    But I wanted to see it just for curiosity, so it doesn't really matter. Do your job people, it's deeply appreciated ;)
  • hikoki wrote: »
    its been a csv authors string field per game. I think my next move will be up to five fields per game for authors

    If you already have a single field with a comma separated list of authors (or something similar) that's fine! I will be able to import from it also.
    hikoki wrote: »
    and their corresponding five csv string field for roles.

    Keep in mind each author may have multiple roles. It may be easier to postpone adding this information until a new WoS archive implements a proper GUI interface to input this data.
    hikoki wrote: »
    Now minimum model is in the title between brackets, I'll dedicate a new field with more choices for clones (atm,evo,uno..) apart from 16k,48k,128k,48k/128k.

    About this field, it should list the minimum requirement to run each program. So if it runs on Spectrum 128K, Pentagon and Uno, it should be marked as "Spectrum 128K". The only exception are programs that work on both 48K and 128K, but provides additional features when running in 128K mode (for instance AY music), they should be marked as "48K/128K".

    If you see any other special cases, let me know!
    hikoki wrote: »
    What about ingame pics and loading screens? I haven't included loading screens so far.

    The database doesn't contain files, only "meta-data". Even if you take loading screens, I won't use them. They will have to wait until a new WoS archive is implemented.
    hikoki wrote: »
    Genres.. no present either for now. I can place a multichoice form with all the genres from infoseek.

    Excellent!
    hikoki wrote: »
    I'll have Publishers separated from authors too.

    Great!
    hikoki wrote: »
    I had a vote system which is disabled. I guess zxdb will take votes into account.

    Not anymore. Lee doesn't have interest in keeping votes, and Martijn had too much work to avoid voting abuses.

    I think we should just preserve the "top 100" pages from WoS (as static HTML outside the database) for historical reasons only. Site "Spectrum 2.0" provides better support for new votes anyway.
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • The only way I would have implemented votes would have been to require registration. It was planned (in fact the demo site allows it but it's disabled) - this would have opened up the possibility of adding user comments to titles, etc
    My test signature
  • [Not anymore. Lee doesn't have interest in keeping votes, and Martijn had too much work to avoid voting abuses.

    I think we should just preserve the "top 100" pages from WoS (as static HTML outside the database) for historical reasons only. Site "Spectrum 2.0" provides better support for new votes anyway.

    I have the opposite opinión :) I think voting in WoS was really useful and I like it more tan Spectrum.2.0 which looks quite... abandoned. I think the opinión of hundreds of voters make the result more reliable rather than the opinión of just one person, as I have seen reviews over there about very good games giving them a 1 or 2. I really would vote or wish the rating system in WoS stayed ;)

  • edited August 2016
    yep one vote allowed per game and gmail account since each game has an associated googleform response sheet.
    Post edited by hikoki on
  • Ivanzx wrote: »
    I have the opposite opinión :) I think voting in WoS was really useful and I like it more tan Spectrum.2.0 which looks quite... abandoned. I think the opinión of hundreds of voters make the result more reliable rather than the opinión of just one person

    Problem is, sometimes hundreds of votes at WoS came from just one person...

    Anyway it will be trivial for me to add votes into ZXDB and import old votes from WoS at a later date. This information is kinda separated from everything else anyway. Someone just needs to decide what to do about it so I can model the corresponding tables accordingly.
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • Ralf wrote: »
    Oh, if anyone else wants the SQL Server database they are more than welcome to pick it up from: https://dl.dropboxusercontent.com/u/24607151/ZXDB SQL Server 2008.zip. It's a SQL 2008 compatible backup from a SQL 2014 engine.

    A little note. Unfortunately it isn't compatible with earlier versions of Microsoft Sql Server. I tried to restore it on Sql Server 2008 R2 and Sql Server 2012 with no success. I believe that's the general pain with this technology, it's usually not backwards compatible.

    But I wanted to see it just for curiosity, so it doesn't really matter. Do your job people, it's deeply appreciated ;)
    Yeah you can't restore SQL Server backups to a lower version, even if the database is running in a backwards compatible mode. It does load fine into SQL Server Express 2016 though.

  • Database conversion at 90% now!

    Current version is available at the usual link here.
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • Database conversion at 90% now!

    When does it become unstable and critical, and explode like in any good sci-if action movie? >:)

    Anyway, thanks for the good work =D>

    Mark
  • Thanks! :)
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • AndyC wrote: »
    Ralf wrote: »
    Oh, if anyone else wants the SQL Server database they are more than welcome to pick it up from: https://dl.dropboxusercontent.com/u/24607151/ZXDB SQL Server 2008.zip. It's a SQL 2008 compatible backup from a SQL 2014 engine.

    A little note. Unfortunately it isn't compatible with earlier versions of Microsoft Sql Server. I tried to restore it on Sql Server 2008 R2 and Sql Server 2012 with no success. I believe that's the general pain with this technology, it's usually not backwards compatible.

    But I wanted to see it just for curiosity, so it doesn't really matter. Do your job people, it's deeply appreciated ;)
    Yeah you can't restore SQL Server backups to a lower version, even if the database is running in a backwards compatible mode. It does load fine into SQL Server Express 2016 though.

    Oh cock! I completely forgot about that...

    Sorry guys, Can only be restored to a SQL 2014/2016 engine.
  • It's done!!!!!

    The entire WoS archive has been imported into ZXDB. :)

    In this link you will find the following files:

    * "ZXDB_import.zip" - The complete WoS database imported into ZXDB.

    * "ZXDB_clean.zip" - The complete WoS database imported into ZXDB (exactly the same as above), except it's a "clean" re-export version.

    * "SpotSpexSQL.zip" - The separate SPOT/SPEX database imported by Richard.

    These databases, together with a file mirror like Meulie, contain all data information inside the WoS archive (except for a few static pages and this forum obviously). This is an important step towards building a new WoS. :)
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • Great !!!
  • Maybe it is a silly question, but with what program can it be opened? :)
  • ZupZup
    edited August 2016
    I'm setting up a virtual machine with Debian and MariaDB. Let's see if I can import it...

    - ZXDB clean imports fine.
    - spot spex.sql imports fine.
    - wos.sql throws ERROR 1046 (3D000) at line 29: No database selected I guess it needs a USE `wos`somewhere.

    Post edited by Zup on
    I was there, too
    An' you know what they said?
    Well, some of it was true!
  • Maybe it is a silly question, but with what program can it be opened?

    Not silly at all Ivan :)
    Actually it's not that simple as opening Excel sheet and you probably need some experience with databases to use it. I'll try to make a simple layman explanation.

    So there are databases. They consist of tables, in case of WOS, the tables would be Programs, Authors, Publishers, Countries, Languages etc.

    Databases are stored on database server. To install a database you need to install to install the server first.

    There are different servers made by competing companies. For example we have Microsoft Sql Server, MySql, Oracle and several others.

    The common thing for these solutions is that they use the same programming language to read and manipulate the data from tables. The language is called SQL.

    The download given to us by Einar is actually a SQL script - some long SQL code which makes all the tables and fills them with data.

    I said that SQL is common for different database servers but it's not entirely true. It is common but there are small differences in syntax which make in many cases SQL script written for one server not work on another server,

    This script supported by Einar is designed for something called MariaDB. I had to check it and it looks to be a variant of better known server - MySql.

    Hope that helps.
  • Ralf wrote: »
    This script supported by Einar is designed for something called MariaDB. I had to check it and it looks to be a variant of better known server - MySql.

    Hope that helps.

    It's almost the same thing. Some time ago, Oracle purchased MySQL. There were (founded) doubts about the future of the project, and that was not helped by the fact that Oracle supressed some features of the free MySQL and put them only in paid versions. Some people left MySQL project, too.

    MariaDB is a fork of the last free version of MySQL. I guess that MySQL and MariaDB are almost clones, with some minor differences that will grow up in the future. So you can import that file into MySQL safely.
    I was there, too
    An' you know what they said?
    Well, some of it was true!
  • Ivanzx wrote: »
    Maybe it is a silly question, but with what program can it be opened? :)

    If you know SQL, then a database (and optionally a database client) will be enough to use it.

    If you don't, then you will have to wait for a proper user interface (i.e the new WoS archive).
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • edited August 2016
    Mmm I guess this can be opened with portable db browser: http://portableapps.com/apps/development/sqlite_database_browser_portable
    Post edited by hikoki on
  • ZupZup
    edited August 2016
    Nope... SQLite is not the same as MySQL. I opened it with this other app, but now I'm trying to generate a view using Libreoffice Base and ODBC (the database is running on a Debian virtual machine, but I've imported it to a Raspberry Pi).
    Post edited by Zup on
    I was there, too
    An' you know what they said?
    Well, some of it was true!
  • If your a Windows user then something like this will get you started it installs Apache MySQL or MariaDB and PHP in one easy installer.

    https://www.apachefriends.org/index.html

    Then as Ralf says you import the database export either through the command line or with PHPMyAdmin

    This is really for people who want to host their own WoS type database and have PHP, HTML and CSS skills.
  • Although WoS has been fully imported, I'm still planning to dedicate more time to improve ZXDB structure.

    The point is, it took me a lot of time to fix all WoS archive inconsistencies when importing it. However the database still have other problems (mainly redundancies and unindexed entries) to be solved, in order to make it easier to manage it in the future.

    What do I mean? Let me give you a couple examples...

    Most WoS inconsistencies are currently noticeable as broken references. For instance take a look at this entry:

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

    Inside this series, if you click on "Hack Attack 2" you will be taken to the right page. But if you click on "Hack Attack 3" you will get a blank page, because this entry doesn't exist. This is a broken reference.

    Now take a look at this entry:

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

    If you click on the other titles in this series you will be taken to blank pages, although both entries exist. This is another kind of broken reference.

    In ZXDB I have fixed them all already.

    Now take a look at this search:

    http://www.worldofspectrum.org/infoseek.cgi?regexp=microsistemas

    This search will list all matches for keyword "microsistemas", so you will see the label "Micro Sistemas" and all copies of this magazine stored in WoS archive. However if you now click on "Micro Sistemas", you won't see any of these magazines stored under this label. The problem is, the search page only found the magazines because they are stored in WoS archive using similar filenames, but these files are not associated with the magazine itself. These are unindexed entries in WoS archive. Gerard is currently organizing this information so it can be added later to ZXDB properly.

    Another problem is, WoS archive has some redundant information. For instance, in "downloads" table there's a column to store machine model (Spectrum 48K, Spectrum 128K, ZX81 1K, ZX81 2K, etc) and another to store machine type (Spectrum, ZX81, Timex, etc). Obviously there's no need for both, so I will remove the latter after using it to re-validate the former. Another example is, when a program is re-released with a different title (in another idiom for instance), this information is replicated in every download file related to this release. Instead, this alternate title should be stored only once per program release.

    In ZXDB, tables storing unindexed information (i.e all kinds of "lost data") have names starting with "tmp_". Their content must be properly organized and moved somewhere else. Similarly, columns storing redundant information have names starting with "tmp_". Their content must be validated and either deleted or moved to more appropriate columns.
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • In a nutshell, here's my roadmap for ZXDB:

    1.) Further validate this imported database. Actually I have already validated it myself, but I'm not infallible. Hopefully someone else with good SQL skills could run some validations too, such as running queries to compare contents from ZXDB against another database extracted directly from WoS website pages.

    2.) Put together a list of my consistency fixes. It will be a long list so it should take me a while...

    3.) Combine ZXDB and Richard's SPOT/SPEX into a single database.

    4.) Eliminate redundancies from database.

    5.) Add information about magazine issues (to be provided by Gerard), then use it to better organize all magazine related information in ZXDB.

    6.) Add information about latest Spectrum releases. Notice this activity doesn't depend on previous steps. As soon as someone else organizes this information (in a spreadsheet or whatever), just send me and I will insert it already!
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • @zup have you given it a go? I believe it works also on mysql and other databases. It has winmaclinux versions an you don't need to know sql. I cannot try it right now..
  • Although WoS has been fully imported, I'm still planning to dedicate more time to improve ZXDB structure.

    Thanks again for your work Einar
  • edited August 2016
    Thank you very much, for all this work, that I thought was already done before. :P
    ...
    Another problem is, WoS archive has some redundant information. For instance, in "downloads" table there's a column to store machine model (Spectrum 48K, Spectrum 128K, ZX81 1K, ZX81 2K, etc) and another to store machine type (Spectrum, ZX81, Timex, etc). Obviously there's no need for both, so I will remove the latter after using it to re-validate the former. Another example is, when a program is re-released with a different title (in another idiom for instance), this information is replicated in every download file related to this release. Instead, this alternate title should be stored only once per program release.

    I'm sure you know this, but be very careful not presume something is redundant, without being sure first.

    For example a title re-released in other language, could have different screens, different downloads (tape, TZX, etc...), in fact, it's a new release, so in theory it should be a new application entry in ZXDB, eventually linked or related with the original one.

    I would like to suggest you keep the great work you have been doing, but I also believe you should define some form of collaboration, so that others can help you to fix these things.

    Maybe setup a WIKI, BugTracker (Mantis?) or similar for it.
    Post edited by RMartins on
  • Ralf wrote: »
    Thanks again for your work Einar

    You are welcome! :)
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
Sign In or Register to comment.