some suggestions for improvements of check-commands

[Geert Schroeder]

In my eyes it would be nice if additonal to the current state
a SqlBase-database could be checked with a chooseable level
of quality enabling a harder (and more time-consuming) check
as today.

For example, it would be good by a higher check-level to tell
the database server to look if the keycolumn-values of an index
and the keycolumn-values to that rowadress (given by the first
16bit of the rowid, I think) on the datapage are realy identical.

In the moment SqlBase (7.5.1 unpatched - ptf1/ptf2 I don't know) does
not check this - but this error can happen.

- Another thing to check harder could be foreign key restrictions.
- Another another think could be to check if the database model is in
such way ok, that an unloaded database, that was checked as the last
step before the unload, can realy be loaded afterwards without any
error messages (my opinion is that this should always be so if done
in this order !).

For example, a problem can be a column with a FK on it, the FK
declared with clause "on delete set null" and the column declared
as "not null".
The "check database"-command does not find this error but when
reloading the DB you will get it.

Because these or other check-functionalties consume time and would not
always be required, but typically before unload/load, it maybe good if
the quality-level of the check could be chosen by a kind of parameter -
assuming that some of these or additionaly other clever
tests-functionalities would be built in the check commands.

Best Regards
Geert Schröder
MICHEL Software GmbH

[Neil]

Geert Schroeder wrote:

> For example, it would be good by a higher check-level to tell the database
> server to look if the keycolumn-values of an index and the keycolumn-values to
> that rowadress (given by the first 16bit of the rowid, I think) on the
> datapage are realy identical.

I thought it did. In fact, there is a bug in certain versions of SQLBase.

CREATE TABLE X (X CHAR(1));
INSERT INTO X VALUES ('X');
CREATE INDEX X ON X (@LOWER(X), X);
CHECK INDEX X;

Because of the bug the created index is corrupt but the index check detects
this.

[Geert Schroeder]

Hello Neil

My case was a little bit different (from your "defect-producing-index-type") :

I have a table for the article-positions of a customer-order:

create table(
auft_nr decimal(12,0) not null,
position integer null,
artikelnummer integer,
......);

create unique index idx_aufp on aufp(artiklelnummer,position);
alter table aufp primary key(auft_nr,position);

Now I got the defect-situation:

INDEX PAGE:
===========
AUFT_NR / POSITION:

200010030944 / 10
200010030944 / 20
200010030944 / 30
200010030944 / 40 <== CORRECT
200010030944 / 50
200010030944 / 60
200010030944 / 70
200010030944 / 80
200010030944 / 90
200010030944 / 100
200010030944 / 110
200010030944 / 120

DATA PAGE:
===========
AUFT_NR / POSITION:

200010030944 / 10
200010030944 / 20
200010030944 / 30
200010031344 / 40 <== ERROR
200010030944 / 50
200010030944 / 60
200010030944 / 70
200010030944 / 80
200010030944 / 90
200010030944 / 100
200010030944 / 110
200010030944 / 120

Neither CHECK INDEX nor CHECK TABLE did find this error.

I think this in an error the CHECK-commands should find out.
(I observed this error one time with SqlBase 6.1.2PTF11 and
one time with SqlBase 7.5.1-unpatched [the example above].)

Best Regards
Geert

[Neil]

Geert Schroeder wrote:

> My case was a little bit different (from your "defect-producing-index-type") :

Strange. When I first tried it (on a big table) the check index found the error,
but on my test case the check index failed!