nonomura nono181...@yahoo.co.jp [firebird-support] <
firebird-support@yahoogroups.com> escreveu no dia terça, 16/10/2018 à(s)
15:14:
>
>
> Analitico and all,
>
> I found that if I add the a extra field in the index like the pk and make
> the index unique it works fine.
>
>
> Many of those collation problems seem to relate to unicode_ci and
> unicode_ci_ai,
> and this relates to the usage of ICU functions.
>
> FB seems to set collation strength properly only where it relates to
> unique index.
> Otherwise the strength is left default, i.e. equivalent of collate
> unicode, for unicode_ci and unicode_ci_ai.
> This is my guess after tested and observed many cases.
>
> If a sorting was ordered by a single column with collate UNICODE,
> the result could be shared with collate UNICODE_CI or UNICODE_CI_AI.
> But when it comes to ordering by multiple columns, it should be a
> different story.
>
The biggest problem that I have now is with foreign keys, even if can make
some indexes unique with most foreign keys its impossible,
example of the problem:
CREATE TABLE M_UNICODE (
S1 VARCHAR(10) NOT NULL COLLATE UNICODE,
S2 VARCHAR(10) NOT NULL COLLATE UNICODE
);
CREATE TABLE D_UNICODE (
S1 VARCHAR(10) NOT NULL COLLATE UNICODE,
S2 VARCHAR(10) NOT NULL COLLATE UNICODE
);
CREATE TABLE M_CI_AI (
S1 VARCHAR(10) NOT NULL COLLATE UNICODE_CI_AI,
S2 VARCHAR(10) NOT NULL COLLATE UNICODE_CI_AI
);
CREATE TABLE D_CI_AI (
S1 VARCHAR(10) NOT NULL COLLATE UNICODE_CI_AI,
S2 VARCHAR(10) NOT NULL COLLATE UNICODE_CI_AI
);
INSERT INTO M_UNICODE (S1, S2) VALUES ('A', 'A');
INSERT INTO M_UNICODE (S1, S2) VALUES ('A', 'B');
INSERT INTO M_CI_AI (S1, S2) VALUES ('A', 'A');
INSERT INTO M_CI_AI (S1, S2) VALUES ('A', 'B');
set term ^ ;
execute block
as
declare variable i bigint = 1000;
begin
while (i > 0) do
begin
insert into d_unicode (s1,s2) values ('A','A');
insert into d_ci_ai (s1,s2) values ('A','A');
i = i-1;
end
insert into d_unicode (s1,s2) values ('A','B');
insert into d_ci_ai (s1,s2) values ('A','B');
end^
set term ; ^
ALTER TABLE M_UNICODE ADD CONSTRAINT M_U PRIMARY KEY (S1, S2);
ALTER TABLE M_CI_AI ADD CONSTRAINT M_CI_AI PRIMARY KEY (S1, S2);
commit work;
ALTER TABLE D_UNICODE ADD CONSTRAINT D_U FOREIGN KEY (S1, S2) REFERENCES
M_UNICODE (S1, S2) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE D_CI_AI ADD CONSTRAINT D_CI_AI FOREIGN KEY (S1, S2) REFERENCES
M_CI_AI (S1, S2) ON DELETE CASCADE ON UPDATE CASCADE;
commit work;
Query
update m_ci_ai set s2 = 'C' where s1 = 'A' and s2 = 'B';
Operations
Read : 9
Writes : 0
Fetches: 2 070
Marks : 6
Enchanced Info:
+---+---+---+-+-+-+-+--+--+--+
| Table Name | Records | Indexed | Non-Indexed |
Updates | Deletes | Inserts | Backouts | Purges | Expunges |
| | Total | reads |reads|
| | | | | |
+---+---+---+-+-+-+-+--+--+--+
|D_CI_AI| 0 | 2002 | 0 |
1 | 0 | 0 |0 |0 |0 |
|M_CI_AI| 0 | 2 | 0 |
1 | 0 | 0 |0 |0 |0 |
+---+---+---+-+-+-+-+--+--+--+
Query
update m_unicode set s2 = 'C' where s1 = 'A' and s2 = 'B';
Operations
Read : 0
Writes : 0
Fetches: 43
Marks : 8
Enchanced Info:
+---+---+---+-+-+-+-+--+--+--+
| Table Name | Records | Indexed | Non-Indexed |
Updates | Deletes | Inserts | Backouts | Purges | Expunges |
| | Total | reads |reads|
| | | | | |
+---+---+---+-+-+-+-+--+--+--+
|D_UNICODE | 0 | 2 | 0 |
1 | 0 | 0 |0 |0 |0 |
|M_UNICODE | 0 | 2 | 0 |
1 | 0 | 0 |0 |0 |0 |
+---+---+---+-+-+-+-+--+--+--+
I made the biggest mistake, I assumed, but I believed that the utf8
colations in firebird where mature.
Thank you
regards
Luis Forra
---
>
