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: 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 »

And another ZXDB release is now 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 the site early next week.
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 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 »

Another ZXDB version 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 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: 403
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: 3029
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: 403
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: 3029
Joined: Wed Nov 15, 2017 2:48 pm

Re: New Database Model ZXDB

Post by Einar Saukas »

Excellent!
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 version 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 »

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: 403
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: 3029
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!
Post Reply