New Database Model ZXDB

[PeterJ]

Thanks for delaying whilst I was otherwise occupied @Einar Saukas. I will update the site on Tuesday.

[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.

[Einar Saukas]

Yet another ZXDB update is available!

[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

[Einar Saukas]

Another ZXDB update is now available!

[Einar Saukas]

A new ZXDB update is available!

[R-Tape]

And I count that as update #177

(plus the uncounted ones pre SC era)

[Einar Saukas]

Yet another ZXDB release is available.

[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!

[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  

[Einar Saukas]

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

In this case, you can simply use "aliases.title" instead.

[pavero]

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.

[Einar Saukas]

Got it!

I will add another table "search_by_aliases" in the next ZXDB update.

[Einar Saukas]

A new ZXDB update is now available!

[Einar Saukas]

Another ZXDB update is available!

[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!!!

[PeterJ]

Thanks @Einar Saukas,

To avoid taking the site down over the weekend, I will do the update on Monday. Time TBC.

[Einar Saukas]

A new ZXDB update is now available!

[Einar Saukas]

Another ZXDB update is available!

[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.

[Einar Saukas]

A new ZXDB update is available!

[Einar Saukas]

Another ZXDB update is now available!

[Einar Saukas]

A new ZXDB version is (finally) available!!!

[Einar Saukas]

A new ZXDB update is available!

[Einar Saukas]

Another ZXDB update is now available!