New Database Model ZXDB
Moderator: druellan
- Einar Saukas
- Bugaboo
- Posts: 3169
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
Yet another ZXDB update is available!
- Einar Saukas
- Bugaboo
- Posts: 3169
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
And another ZXDB release is now available!
Re: New Database Model ZXDB
Thanks @Einar Saukas. I will update the site early next week.
- Einar Saukas
- Bugaboo
- Posts: 3169
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
A new ZXDB release is available!
- Einar Saukas
- Bugaboo
- Posts: 3169
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
Another ZXDB version is now available!
- Einar Saukas
- Bugaboo
- Posts: 3169
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
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!
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!
Re: New Database Model ZXDB
Ouch! I've been bitten by that change.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...
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.)
- Einar Saukas
- Bugaboo
- Posts: 3169
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
This is standard SQL to create a lookup table for library_title:
If that's hard to translate to SQLite, here's the same logic using simpler SQL:
Let me know if that works for you!
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;
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 %';
Re: New Database Model ZXDB
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 || '%';
- Einar Saukas
- Bugaboo
- Posts: 3169
- Joined: Wed Nov 15, 2017 2:48 pm
- Einar Saukas
- Bugaboo
- Posts: 3169
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
Another ZXDB version is available!
Re: New Database Model ZXDB
Thanks Einar,
I will update in the next couple of days.
Re: New Database Model ZXDB
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
The previous version (1.0.183) built OK, so something must have changed in this area.
- Einar Saukas
- Bugaboo
- Posts: 3169
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
Merge request approved. Thank you!
- Einar Saukas
- Bugaboo
- Posts: 3169
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
A new ZXDB version is now available!
Re: New Database Model ZXDB
Thanks @Einar Saukas,
I will update the site on Monday.
I will update the site on Monday.
- Einar Saukas
- Bugaboo
- Posts: 3169
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
Another ZXDB update is available!
- Einar Saukas
- Bugaboo
- Posts: 3169
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
A new ZXDB release is available!
- Einar Saukas
- Bugaboo
- Posts: 3169
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
Another ZXDB update is now available!
- Einar Saukas
- Bugaboo
- Posts: 3169
- Joined: Wed Nov 15, 2017 2:48 pm
Re: New Database Model ZXDB
Yet another ZXDB release is available already!
Re: New Database Model ZXDB
Thanks @Einar Saukas. I will update the site on Monday.
-
- Microbot
- Posts: 117
- Joined: Mon Apr 13, 2020 3:07 pm
Re: New Database Model ZXDB
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).
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).
Re: New Database Model ZXDB
Hi @Patrik Rak,
@Einar Saukas will reply in full, but please read here:
viewtopic.php?p=136543#p136543
Peter
@Einar Saukas will reply in full, but please read here:
viewtopic.php?p=136543#p136543
Peter
-
- Microbot
- Posts: 117
- Joined: Mon Apr 13, 2020 3:07 pm
Re: New Database Model ZXDB
I did, even the original post, but that really doesn't explain the point, IMO, why was this change worth it.
Re: New Database Model ZXDB
Hi @Patrik Rak,
Einar explains it in the linked post.
Einar explains it in the linked post.
I will let him explain further. ThanksHowever 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).