Database model ZXDB

1679111230

Comments

  • Vampyre wrote: »
    Re: Magazines

    This probably won't be of that much use to you as it's far from complete anyway and only covers Your Sinclair, Crash, Your Spectrum and Sinclair User but I started compiling this table ages ago for ZXSR. There's a little bit of extra info, Price, Editor and Number of Pages - but like I say, is far from complete.

    Thank you!!!

    Hopefully Gerard will find it useful :)
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • It had been so long since last time I used MySQL, that I forgot it lacks proper constraint support (the same applies to MariaDB). Therefore the constraints I defined to prevent errors on future updates were being ignored...

    I have now just fixed this issue, along with other database improvements. As usual, latest version is available here.
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • edited September 2016
    Out of curiosity:

    Currently MySQL (and MariaDB) parses and validates CHECK constraints (so it kinda pretends to accept them), but just throws this information away silently. A bug report about it was registed 12 years ago and it's still pending. It's a shame for an otherwise great database...
    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.
  • Out of curiosity:

    Currently MySQL (and MariaDB) parses and validates CHECK constraints (so it kinda pretends to accept them), but just throws this information away silently. A bug report about it was registed 12 years ago and it's still pending. It's a shame for an otherwise great database...

    MySQL is crap, I don't really get why people bother with it. There are free versions of Microsoft SQL Server and Oracle, which are the top tier database engines and just so much better than anything else. They have limitations, but probably nothing that ZXDB is going to go anywhere near.
  • PostgreSQL is the recommended database platform for Inveniosoftware
  • AndyC wrote: »

    MySQL is crap, I don't really get why people bother with it. There are free versions of Microsoft SQL Server and Oracle, which are the top tier database engines and just so much better than anything else. They have limitations, but probably nothing that ZXDB is going to go anywhere near.

    Been using MySQL for the last 10 years in my own ERP app for work and it's been running all that time with no problems - and it's handling 30+ users and thousands of transactions a day.

    That said I'm investigating PostgresSQL with a view to using that at the moment :)
  • The ZXSR site runs off a SQLite database and the portability of that might make sense for ZXDB. It's also really fast.
  • 
    
    
    Vampyre wrote: »
    The ZXSR site runs off a SQLite database and the portability of that might make sense for ZXDB. It's also really fast.

    Hi, are there tools which reliability convert between the two?

  • Frankly any reasonable database will do the job just fine here, it doesn't really matter what's the best database.

    Regarding portability, let's recapitulate what I already wrote at the very beginning of this thread:
    * I didn't bother to make this model perfectly database-independent, since I bet anyone would use it in practice on a free MySQL or MariaDB database anyway. Even so, it only uses database features that can be easily ported to other databases in a couple minutes, such as the database-specific BOOLEAN datatype that I used for convenience only.

    Since it seems there's real interest in running ZXDB in another RDBMS products (unless it was a purely theoretical discussion for no particular reason?), I will update ZXDB to remove all database-specific features in my next update. It will be trivial to do it anyway...
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • Hi Einar,

    Can I just check its 'ZXDB_clean' that always the current latest, and 'ZXDB_import' contains an older version plus update scripts? Thanks
  • Exactly. If you just want the latest version, downloading ZXDB_clean is enough.
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
    Thanked by 1jonesypeter
  • edited September 2016
    Result!

    Capture.png

    Now, I need to understand all the tables!

    How, many rows in entries!

    Capture2.png

    I'm working on hosting a copy of zxdb on my site (nothing there yet)

    Can I make it crystal clear that I am not competing against the new WoS archive (Lee and others are doing a sterling job). My intent is just to make it easier for people to have access to ZXDB, validate inconsistency fixes, and see latest Speccy game releases that are being added to ZXDB while new WoS archive is not ready.

    I have the blessing of Einar, and Lee is aware. so just to clarify once again, I am not and have no intention ever of trying to compete against WoS. Added to this, I wouldn't have the skills even if I wanted to!

    For those who are interested, I'm using a Digital Ocean $10 a month droplet with Ubuntu 16.04, the server software installed from scratch, as well as a LetsEncrypt SSL certificate.

    If you have PHP, CSS, HTML and Bootrap skills, and are willing to help then please PM me.
    Post edited by jonesypeter on
  • +1 letsencrypt
  • +1 letsencrypt

    Indeed. Digital Ocean have some excellent documentation on the installation.

  • Hello Einar,

    Can I ask where the original price is held if its not in the table entries. I've got this SQL the brings out games from Atlantis software, and some of the original prices are Null, but present in the WoS archive. For Example:

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

    Which is entries is '1006'

    Thanks in advance
    SELECT entries.id, entries.title, entrytypes.text,entries.original_price, entries.budget_price,labels.name FROM entries,publishers, labels, entrytypes
    where entries.entrytype_id = entrytypes.id
    and publishers.entry_id = entries.id
    and publishers.label_id = labels.id
    and labels.id = '1371'
    

  • Hi Einar,

    You can see an example of it here:

    http://spectrumcomputing.co.uk/originalprices.php

    Peter
  • Can I ask where the original price is held if its not in the table entries.

    When a price is not available in "entries" (from the main archive), then it must be taken from "wos_topicheaders" (from the SPEX/SPOT archive), as follows:
    SELECT e.id, e.title, t.text, e.original_price, e.tmp_original_price_from_spot, h.orgprice, 
    e.spanish_price, e.budget_price, h.repub2price, h.repub3price, 
    e.cartridge_price, e.microdrive_price, e.disk_price, h.diskprice, b.name 
    FROM entries e  
    INNER JOIN entrytypes t ON e.entrytype_id = t.id
    LEFT JOIN publishers p ON p.entry_id = e.id
    LEFT JOIN labels b ON p.label_id = b.id
    LEFT JOIN wos_topicheaders h ON h.entry_id = e.id
    WHERE b.id = '1371';
    

    After I finish mapping all SPEX/SPOT information to their corresponding main archive entries, I will consolidate this kind of information into a single table.
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • edited September 2016
    Thanks Einar, much appreciated.

    Seems to be some duplication of rows there, which I will look at later, but I have updated the page.
    Post edited by jonesypeter on
  • edited September 2016
    If anyone is interested, I thought I would give a brief update as to how I'm doing.

    I've built a little search box where you can search for a game title. For example Dizzy

    I have remembered the 'mysqli_real_escape_string' and 'strip_tags'! Any other tips on security would be welcome.

    http://spectrumcomputing.co.uk/searchform.html

    More to come next weekend, but I need to go to bed now.
    Post edited by jonesypeter on
  • OK, I removed all database-specific features from ZXDB. Latest version is available here as usual.

    It's now supposed to work with every RDBMS product out there. If anyone tries to load it into a different database but it doesn't work, please give me a detailed report of error message and failed statement, so I can check it out.

    It turns out MariaDB finally provided "partial" support for CHECK CONSTRAINTS in version 10.2 (currently alpha release). Although this latest version of ZXDB should be compatible with MariaDB 10.1 or earlier, keep in mind that CHECK CONSTRAINTS will be ignored unless you use MariaDB 10.2 or later. You have been warned...
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • edited September 2016
    Seems to be some duplication of rows there, which I will look at later, but I have updated the page.

    When there are 2 different SPOT/SPEX rows for the same game in the main archive, that SQL query will give you duplicated rows. This is another redundancy from the original database that I'm working to fix.
    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.
  • Thanks Einar. I look forward to the prices being in the entries table.
  • Can I suggest you add a slug field too? You will need this as a point to online folders...
    My test signature
  • Can I suggest you add a slug field too? You will need this as a point to online folders...

    I suggest using data id as slug. It's more efficient and much easier.

    Notice WoS already does it for software and books (corresponding to id from table "entries"). For instance the id for program "ZX7" is 27996 therefore WoS link is:

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

    Similarly, links to authors and publishers can use id from table "labels", links to download files use id from table "downloads", links to magazine scan pages use id from table "magscans", etc.
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • WoS also has a foldername field so linking is quick and easy. Slugs are better for URL's. It would make sense therefore to use the foldername field as a slug.
    My test signature
  • Instead of storing folder and filename as 2 separate columns, I'm storing them together as a direct file link for convenience. I didn't see the need to provide a link to the folder where a file is stored, just a link to the file itself.

    Do you need me to split it into 2 columns again?
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • Doesn't affect me as I have almost the same as you - the file folder is virtual. *however* I have stored the foldername as a slug for two reasons - the cleaner URL, and users will be able to download a full folder as well as individual files. In the case of the folder, the current file structure is kept.

    I've also set the script to know whether it's an ID or SLUG, so both work as expected, but the htaccess file will redirect correctly without too much hassle.
    My test signature
  • Yet another version is available. Download it here.
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • Doesn't affect me as I have almost the same as you - the file folder is virtual. *however* I have stored the foldername as a slug for two reasons - the cleaner URL, and users will be able to download a full folder as well as individual files. In the case of the folder, the current file structure is kept.

    I've also set the script to know whether it's an ID or SLUG, so both work as expected, but the htaccess file will redirect correctly without too much hassle.

    So you basically need a unique tag for each file folder?

    No problem, I will provide it in next version.
    Creator of ZXDB, BIFROST/NIRVANA, ZX7/RCS, etc. I don't frequent this forum anymore, please look for me elsewhere.
  • 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?
Sign In or Register to comment.