New Database Model ZXDB
Moderator: druellan
Re: New Database Model ZXDB
Thanks for delaying whilst I was otherwise occupied @Einar Saukas. I will update the site on Tuesday.
- Einar Saukas
- Bugaboo
- Posts: 3160
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
Another ZXDB update is available now!
Once again, if you have a web site or application using ZXDB, please read this:
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.
- Einar Saukas
- Bugaboo
- Posts: 3160
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
Yet another ZXDB update is available!
- Einar Saukas
- Bugaboo
- Posts: 3160
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
A new ZXDB update is available!
One more time, if you have a web site or application using ZXDB, please read this:
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
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.
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
- Einar Saukas
- Bugaboo
- Posts: 3160
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
Another ZXDB update is now available!
- Einar Saukas
- Bugaboo
- Posts: 3160
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
A new ZXDB update is available!
Re: New Database Model ZXDB
And I count that as update #177
(plus the uncounted ones pre SC era)
(plus the uncounted ones pre SC era)
- Einar Saukas
- Bugaboo
- Posts: 3160
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
Yet another ZXDB release is available.
- Einar Saukas
- Bugaboo
- Posts: 3160
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
A new ZXDB update is available !
Also if you have a web site or application using ZXDB, please read this:
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:
you should use this:
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!
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.
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
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
- pavero
- Dynamite Dan
- Posts: 1593
- Joined: Sat Dec 09, 2017 11:49 pm
- Location: The Czech Republic
- Contact:
Re: New Database Model ZXDB
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
- Einar Saukas
- Bugaboo
- Posts: 3160
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
In this case, you can simply use "aliases.title" instead.
- pavero
- Dynamite Dan
- Posts: 1593
- Joined: Sat Dec 09, 2017 11:49 pm
- Location: The Czech Republic
- Contact:
Re: New Database Model ZXDB
No you can't, because it's something else.Einar Saukas wrote: ↑Tue Aug 15, 2023 11:35 pm In this case, you can simply use "aliases.title" instead.
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.
- Einar Saukas
- Bugaboo
- Posts: 3160
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
Got it!
I will add another table "search_by_aliases" in the next ZXDB update.
I will add another table "search_by_aliases" in the next ZXDB update.
- Einar Saukas
- Bugaboo
- Posts: 3160
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
A new ZXDB update is now available!
- Einar Saukas
- Bugaboo
- Posts: 3160
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
Another ZXDB update is available!
- Einar Saukas
- Bugaboo
- Posts: 3160
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
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!!!
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!!!
Re: New Database Model ZXDB
Thanks @Einar Saukas,
To avoid taking the site down over the weekend, I will do the update on Monday. Time TBC.
To avoid taking the site down over the weekend, I will do the update on Monday. Time TBC.
- Einar Saukas
- Bugaboo
- Posts: 3160
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
A new ZXDB update is now available!
- Einar Saukas
- Bugaboo
- Posts: 3160
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
Another ZXDB update is available!
Re: New Database Model ZXDB
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.
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.
- Einar Saukas
- Bugaboo
- Posts: 3160
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
A new ZXDB update is available!
- Einar Saukas
- Bugaboo
- Posts: 3160
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
Another ZXDB update is now available!
- Einar Saukas
- Bugaboo
- Posts: 3160
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
A new ZXDB version is (finally) available!!!
- Einar Saukas
- Bugaboo
- Posts: 3160
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
A new ZXDB update is available!
- Einar Saukas
- Bugaboo
- Posts: 3160
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
Another ZXDB update is now available!