New Database Model ZXDB

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
PeterJ
Site Admin
Posts: 6819
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: New Database Model ZXDB

Post by PeterJ »

Thanks @Einar Saukas,

I will do the update on Thursday afternoon.

Peter
User avatar
Einar Saukas
Bugaboo
Posts: 3029
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

A new ZXDB update is available!
User avatar
PeterJ
Site Admin
Posts: 6819
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: New Database Model ZXDB

Post by PeterJ »

Thanks @Einar Saukas,

As the size of the SQL file is getting larger, I was wondering if it would be possible to provide cumulative updates. Obviously, if this is extra work for you don't bother.
User avatar
Einar Saukas
Bugaboo
Posts: 3029
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

Unfortunately it won't help. Some of the scripts I run when preparing each new release (to reimport Speccy Maps, ZXSR, etc) take longer than reloading the entire database.

EDIT: There are database comparison tools like DBDiff you could try. When it's time to update SpectrumComputing, instead of reloading the entire database in the server, you could reload it in your local computer, generate a diff from the server, then apply the diff to the server. This process would also take care of any possible changes previously applied directly to the server from the online forms... At least that's the theory. I have no idea how reliable is this tool. Does anybody have experience using it, or any of the other similar tools?
User avatar
Einar Saukas
Bugaboo
Posts: 3029
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

A new ZXDB update is now available!

Also if you have a web site or application using ZXDB, please read this:
  • Column "entries.mag_ratings" will be removed in the next update. This column was imported from Martijn's old WoS, storing a simplified representation of the main review scores obtained by each program. Since ZXDB is now fully integrated with ZXSR and contains all detailed scores, these simplified (sometimes innacurate) scores are not useful anymore. I highly doubt anybody ever used this information anyway.
User avatar
PeterJ
Site Admin
Posts: 6819
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: New Database Model ZXDB

Post by PeterJ »

Thanks for delaying whilst I was otherwise occupied @Einar Saukas. I will update the site on Tuesday.
User avatar
Einar Saukas
Bugaboo
Posts: 3029
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

Another ZXDB update is available now!

Once again, if you have a web site or application using ZXDB, please read this:
  • Column "members.series_seq" is not used anymore. It still exists, but it will be removed in the next ZXDB update. Instead of it, please use column "members.member_seq" from now on. This new column will have multiple purposes, so it can store order of titles in a series, position of titles in a competition, etc.
User avatar
Einar Saukas
Bugaboo
Posts: 3029
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

Yet another ZXDB update is available!
User avatar
Einar Saukas
Bugaboo
Posts: 3029
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

A new ZXDB update is available!

One more time, if you have a web site or application using ZXDB, please read this:
  • Column "tags.device_id" is not used and it will be removed in the next ZXDB update.
  • The following ZX81 entry IDs were removed from the archive: 28662,28664,28766,28859,28886,29046,29274. These are very dummy programs (for instance multiplying 2 numbers) that came from Martijn's old WoS but it doesn't make sense to keep them in the archive.
If anyone's curious about these dummy programs, you can look at them yourself here:

https://archive.org/download/World_of_S ... /AREA1.ZIP
https://archive.org/download/World_of_S ... /AREA2.ZIP
https://archive.org/download/World_of_S ... /AREA3.ZIP
https://archive.org/download/World_of_S ... /CODES.ZIP
https://archive.org/download/World_of_S ... XAMPLE.ZIP
https://archive.org/download/World_of_S ... ORTEST.ZIP
https://archive.org/download/World_of_S ... LOOPER.ZIP
https://archive.org/download/World_of_S ... ROLLER.ZIP
User avatar
Einar Saukas
Bugaboo
Posts: 3029
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

Another ZXDB update is now available!
User avatar
Einar Saukas
Bugaboo
Posts: 3029
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

A new ZXDB update is available!
User avatar
R-Tape
Site Admin
Posts: 6303
Joined: Thu Nov 09, 2017 11:46 am

Re: New Database Model ZXDB

Post by R-Tape »

And I count that as update #177 :dance

(plus the uncounted ones pre SC era)
User avatar
Einar Saukas
Bugaboo
Posts: 3029
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

Yet another ZXDB release is available.
User avatar
Einar Saukas
Bugaboo
Posts: 3029
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

A new ZXDB update is available !

Also if you have a web site or application using ZXDB, please read this:
  • Columns "entries.library_title" and "aliases.library_title" will be removed in a future update.
Columns "library_title" stores a duplicate of the game title, except in "library format" i.e "The Lords of Midnight" becomes "Lords of Midnight, The".

Ignoring the initial article ("The", "An", etc) in a title is convenient, to make titles like "A Life of Crime", "The Lords of Midnight" and "Lord of the Rings" get listed together (as if starting with letter "L") in a large list of titles. This way, you can easily find any game in a list, without having to worry if the exact game title is "Arc of Yesod" or "The Arc of Yesod", for instance.

However if this column is so useful, why are we removing it from the database? Because we don't need to store it anymore. This column was originally filled manually in ZXDB, but now we use a routine to generate it automatically (the online form doesn't even have an option to enter it manually).

Accessing the generated "library_title" is very easy. There's a script in ZXDB called "ZXDB_help_search.sql", that automatically generates a separate table with this information for you. So if you wanted a list of all titles ordered by library_title, instead of this:

Code: Select all

select e.id,e.title,e.library_title from entries e 
order by e.library_title
you should use this:

Code: Select all

select e.id,e.title,x.library_title from entries e
inner join search_by_origins x on x.entry_id = e.id
order by x.library_title
Although this is a simple change, this time we will wait longer than usual before we drop this column, to give plenty of time for web sites and applications to make this change. If anyone has any problems, please let me know!
User avatar
pavero
Dynamite Dan
Posts: 1539
Joined: Sat Dec 09, 2017 11:49 pm
Location: The Czech Republic
Contact:

Re: New Database Model ZXDB

Post by pavero »

Any example how to replace "aliases.library_title", e.g. in this query? ;)

Code: Select all

     
     SELECT publishers.release_seq, labels.name as name, aliases.library_title as lib
     FROM publishers
     LEFT JOIN labels ON publishers.label_id = labels.id 
     LEFT JOIN aliases ON publishers.entry_id = aliases.entry_id and  publishers.release_seq =  aliases.release_seq
     WHERE publishers.entry_id = $entryid
     ORDER BY publishers.release_seq  
User avatar
Einar Saukas
Bugaboo
Posts: 3029
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

pavero wrote: Tue Aug 15, 2023 10:54 pm Any example how to replace "aliases.library_title", e.g. in this query? ;)
In this case, you can simply use "aliases.title" instead.
User avatar
pavero
Dynamite Dan
Posts: 1539
Joined: Sat Dec 09, 2017 11:49 pm
Location: The Czech Republic
Contact:

Re: New Database Model ZXDB

Post by pavero »

Einar Saukas wrote: Tue Aug 15, 2023 11:35 pm In this case, you can simply use "aliases.title" instead.
No you can't, because it's something else.

One example (how online forms work).

I have entry_id (3787) and label_id of the re-releaser (14779), nothing more. And from these two numbers I need to generate the full title like this:

"Podraz3(Stig3The)(TotalComputerGang)"
https://spectrumcomputing.co.uk/entry/3 ... m/Podraz_3

But if I use "aliases.title" I will get wrongly:
"Podraz3(TheStig3)(TotalComputerGang)"

Yes, I can use my own PHP function which creates "aliases.library_title" from "aliases.title" automatically.

But the main problem is somewhere else:
The "search_by_origins" structure can replace "e.library_title", but it doesn't replace "aliases.library_title" at all.
Last edited by pavero on Wed Aug 16, 2023 11:33 am, edited 1 time in total.
User avatar
Einar Saukas
Bugaboo
Posts: 3029
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

Got it!

I will add another table "search_by_aliases" in the next ZXDB update.
User avatar
Einar Saukas
Bugaboo
Posts: 3029
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

A new ZXDB update is now available!
User avatar
Einar Saukas
Bugaboo
Posts: 3029
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

Another ZXDB update is available!
User avatar
Einar Saukas
Bugaboo
Posts: 3029
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

A new ZXDB version is available!

This update now includes the latest version of ZXSR, including everything reported so far in the ZXSR bugs thread.

Many thanks to @Vampyre for all his hard work!!!
User avatar
PeterJ
Site Admin
Posts: 6819
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: New Database Model ZXDB

Post by PeterJ »

Thanks @Einar Saukas,

To avoid taking the site down over the weekend, I will do the update on Monday. Time TBC.
User avatar
Einar Saukas
Bugaboo
Posts: 3029
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

A new ZXDB update is now available!
User avatar
Einar Saukas
Bugaboo
Posts: 3029
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

Another ZXDB update is available!
User avatar
PeterJ
Site Admin
Posts: 6819
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: New Database Model ZXDB

Post by PeterJ »

Thanks @Einar Saukas. I will update at around 20:00BST on Friday.

As normal I will post a banner message on the index page in the morning. For the benefit of @1024MAK and others who don't routinely access the index page, I will try and remember to show the banner on all pages for at least an hour before I commence the update.

Of course, this post might be read too.
Post Reply