New Database Model ZXDB

[Einar Saukas]

Yet another ZXDB update is available!

[Einar Saukas]

And another ZXDB release is now available!

[PeterJ]

Thanks @Einar Saukas. I will update the site early next week.

[Einar Saukas]

A new ZXDB release is available!

[Einar Saukas]

Another ZXDB version is now available!

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

[SkoolKid]

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

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

[SkoolKid]

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 || '%';

[Einar Saukas]

Excellent!

[Einar Saukas]

Another ZXDB version is available!

[PeterJ]

Another ZXDB version is available!

Thanks Einar,

I will update in the next couple of days.

[SkoolKid]

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.

[Einar Saukas]

This version fails to build the database from ZXDB_sqlite.sql.

Merge request approved. Thank you!

[Einar Saukas]

A new ZXDB version is now available!

[PeterJ]

Thanks @Einar Saukas,

I will update the site on Monday.

[Einar Saukas]

Another ZXDB update is available!

[Einar Saukas]

A new ZXDB release is available!

[Einar Saukas]

Another ZXDB update is now available!

[Einar Saukas]

Yet another ZXDB release is available already!

[PeterJ]

Thanks @Einar Saukas. I will update the site on Monday.

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

[PeterJ]

Hi @Patrik Rak,

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

viewtopic.php?p=136543#p136543

Peter

[Patrik Rak]

I did, even the original post, but that really doesn't explain the point, IMO, why was this change worth it.

[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