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
Einar Saukas
Bugaboo
Posts: 3102
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: 3102
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

And another ZXDB release is now available!
User avatar
PeterJ
Site Admin
Posts: 6876
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 the site early next week.
User avatar
Einar Saukas
Bugaboo
Posts: 3102
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

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

Re: New Database Model ZXDB

Post by Einar Saukas »

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

Re: New Database Model ZXDB

Post by Einar Saukas »

A new ZXDB version is available!

IMPORTANT: Columns "entries.library_title" and "aliases.library_title" are now removed in this release, as warned in advance 6 months ago. Thus if you have a website or app that uses ZXDB, please double check that you are not using these columns anymore, before upgrading to this new version!
User avatar
SkoolKid
Manic Miner
Posts: 407
Joined: Wed Nov 15, 2017 3:07 pm

Re: New Database Model ZXDB

Post by SkoolKid »

Einar Saukas wrote: Tue Feb 06, 2024 10:36 pm IMPORTANT: Columns "entries.library_title" and "aliases.library_title" are now removed in this release...
Ouch! I've been bitten by that change.

What's the quickest way to get "library_title" back (for software only, if that makes it easier) in a lookup table? I'm using SQLite.

(I have looked at ZXDB_help_search.sql, but it doesn't work on SQLite and I'm still trying to make sense of it.)
SkoolKit - disassemble a game today
Pyskool - a remake of Skool Daze and Back to Skool
User avatar
Einar Saukas
Bugaboo
Posts: 3102
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

This is standard SQL to create a lookup table for library_title:

Code: Select all

create table libraries (
  title varchar(300) collate utf8_unicode_ci not null primary key,
  library_title varchar(300) collate utf8_unicode_ci not null
);

insert into libraries(title, library_title) (select title, '' from entries group by title collate utf8_unicode_ci);
insert into libraries(title, library_title) (select title, '' from aliases where title collate utf8_unicode_ci not in (select title from libraries) group by title collate utf8_unicode_ci);

update libraries s
inner join prefixes p on s.title like concat(p.text,'%')
left join prefixexempts x on s.title like concat(x.text,'%')
set s.library_title = trim(concat(substr(s.title,length(p.text)+1),', ',left(s.title, length(p.text))))
where x.text is null;
If that's hard to translate to SQLite, here's the same logic using simpler SQL:

Code: Select all

create table libraries (
  title varchar(300) collate utf8_unicode_ci not null primary key,
  library_title varchar(300) collate utf8_unicode_ci not null
);

insert into libraries(title, library_title) (select title, '' from entries group by title collate utf8_unicode_ci);
insert into libraries(title, library_title) (select title, '' from aliases where title collate utf8_unicode_ci not in (select title from libraries) group by title collate utf8_unicode_ci);

update libraries set library_title = concat(substr(title,4),', 3D') where title like '3D %';
update libraries set library_title = concat(substr(title,3),', A') where title like 'A %' and title not like 'A B C%' and title not like 'A I AA%' and title not like 'A La Carte%' and title not like 'A Maci %' and title not like 'A to Z%' and title not like 'A to snad%';
update libraries set library_title = concat(substr(title,4),', An') where title like 'An %';
update libraries set library_title = concat(substr(title,4),', As') where title like 'As %';
update libraries set library_title = concat(substr(title,5),', Das') where title like 'Das %';
update libraries set library_title = concat(substr(title,4),', De') where title like 'De %';
update libraries set library_title = concat(substr(title,5),', Der') where title like 'Der %';
update libraries set library_title = concat(substr(title,5),', Des') where title like 'Des %';
update libraries set library_title = concat(substr(title,5),', Die') where title like 'Die %' and title not like 'Die Alien%' and title not like 'Die Hard%';
update libraries set library_title = concat(substr(title,4),', El') where title like 'El %';
update libraries set library_title = concat(substr(title,4),', Em') where title like 'Em %';
update libraries set library_title = concat(substr(title,5),', Het') where title like 'Het %';
update libraries set library_title = concat(substr(title,4),', Il') where title like 'Il %';
update libraries set library_title = trim(concat(substr(title,3),', L''')) where title like 'L''%';
update libraries set library_title = concat(substr(title,4),', La') where title like 'La %' and title not like 'LA Drugs%';
update libraries set library_title = concat(substr(title,5),', Las') where title like 'Las %' and title not like 'Las Vegas%';
update libraries set library_title = concat(substr(title,4),', Le') where title like 'Le %' and title not like 'Le Mans%';
update libraries set library_title = concat(substr(title,5),', Les') where title like 'Les %';
update libraries set library_title = concat(substr(title,4),', Lo') where title like 'Lo %' and title not like 'Lo Profile%';
update libraries set library_title = concat(substr(title,5),', Los') where title like 'Los %' and title not like 'Los Angeles%';
update libraries set library_title = concat(substr(title,3),', O') where title like 'O %';
update libraries set library_title = concat(substr(title,4),', Os') where title like 'Os %' and title not like 'OS DRAW%';
update libraries set library_title = concat(substr(title,5),', The') where title like 'The %';
update libraries set library_title = concat(substr(title,4),', Un') where title like 'Un %' and title not like 'Un Dos%';
update libraries set library_title = concat(substr(title,5),', Una') where title like 'Una %';
update libraries set library_title = concat(substr(title,5),', Une') where title like 'Une %';
Let me know if that works for you!
User avatar
SkoolKid
Manic Miner
Posts: 407
Joined: Wed Nov 15, 2017 3:07 pm

Re: New Database Model ZXDB

Post by SkoolKid »

Einar Saukas wrote: Fri Feb 09, 2024 3:27 pm Let me know if that works for you!
Thanks! I've converted that for SQLite thus, which seems to work (though I need to do some further checking):

Code: Select all

create table libraries (
  title varchar(300),
  library_title varchar(300)
);

insert into libraries
select title, '' from entries group by title;

insert into libraries
select title, '' from aliases where title not in (select title from libraries) group by title;

update libraries as s
set library_title = trim(substr(s.title, length(p.text)+1) || ', ' || substr(s.title, 1, length(p.text)))
from prefixes as p  
where s.title like p.text || '%';

update libraries as s    
set library_title = title
from prefixexempts as p  
where s.title like p.text || '%';
SkoolKit - disassemble a game today
Pyskool - a remake of Skool Daze and Back to Skool
User avatar
Einar Saukas
Bugaboo
Posts: 3102
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

Excellent!
User avatar
Einar Saukas
Bugaboo
Posts: 3102
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

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

Re: New Database Model ZXDB

Post by PeterJ »

Einar Saukas wrote: Mon Feb 12, 2024 9:43 pm Another ZXDB version is available!
Thanks Einar,

I will update in the next couple of days.
User avatar
SkoolKid
Manic Miner
Posts: 407
Joined: Wed Nov 15, 2017 3:07 pm

Re: New Database Model ZXDB

Post by SkoolKid »

Einar Saukas wrote: Mon Feb 12, 2024 9:43 pm Another ZXDB version is available!
This version fails to build the database from ZXDB_sqlite.sql. It gives the following error:

Code: Select all

Parse error near line 565472: near "CHARACTER": syntax error
  TABLE IF NOT EXISTS prefixes (   text varchar(10) CHARACTER SET utf8 COLLATE u
                                      error here ---^
Parse error near line 565478: no such table: prefixes
Parse error near line 565508: near "CHARACTER": syntax error
   IF NOT EXISTS prefixexempts (   text varchar(50) CHARACTER SET utf8 COLLATE u
                                      error here ---^
Parse error near line 565514: no such table: prefixexempts
I got it working by removing "CHARACTER SET utf8 COLLATE utf8_unicode_ci" from the two offending CREATE TABLE statements.

The previous version (1.0.183) built OK, so something must have changed in this area.
SkoolKit - disassemble a game today
Pyskool - a remake of Skool Daze and Back to Skool
User avatar
Einar Saukas
Bugaboo
Posts: 3102
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

SkoolKid wrote: Tue Feb 13, 2024 12:46 pm This version fails to build the database from ZXDB_sqlite.sql.
Merge request approved. Thank you!
User avatar
Einar Saukas
Bugaboo
Posts: 3102
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

A new ZXDB version is now available!
User avatar
PeterJ
Site Admin
Posts: 6876
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 the site on Monday.
User avatar
Einar Saukas
Bugaboo
Posts: 3102
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: 3102
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

A new ZXDB release is available!
User avatar
Einar Saukas
Bugaboo
Posts: 3102
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: 3102
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

Yet another ZXDB release is available already!
User avatar
PeterJ
Site Admin
Posts: 6876
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 the site on Monday.
Patrik Rak
Microbot
Posts: 117
Joined: Mon Apr 13, 2020 3:07 pm

Re: New Database Model ZXDB

Post by Patrik Rak »

Late to the party, but why was the library_title removed in the first place? Just to save space? Couldn't have it been simply null when it doesn't differ from the title?

As for the provided SQL scripts, unfortunately none worked for me out of the box, not even Skoolkids, perhaps my sqlite is older than his. I will need spend some time on this (which I don't have) to get it back. Couldn't we just have the table there by default?

Also I find it surprising that there is no check for the language - I would expect Der/Die/Das apply only to German, not other languages, similarly A/An/The to English and not Czech (thus avoiding the need for some exemptions).
User avatar
PeterJ
Site Admin
Posts: 6876
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: New Database Model ZXDB

Post by PeterJ »

Hi @Patrik Rak,

@Einar Saukas will reply in full, but please read here:

viewtopic.php?p=136543#p136543

Peter
Patrik Rak
Microbot
Posts: 117
Joined: Mon Apr 13, 2020 3:07 pm

Re: New Database Model ZXDB

Post by Patrik Rak »

I did, even the original post, but that really doesn't explain the point, IMO, why was this change worth it.
User avatar
PeterJ
Site Admin
Posts: 6876
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: New Database Model ZXDB

Post by PeterJ »

Hi @Patrik Rak,

Einar explains it in the linked post.
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).
I will let him explain further. Thanks
Post Reply