ISQL ignore index and ordering in UNIQUE CONSTRAINT for generate metadate script [CORE1261]

[firebird-automations]

Submitted by: @tonal

Assigned to: Claudio Valderrama C. (robocop)

Is related to QA173

Command line:
>isql -u sysdba -p masterkey -x BASE.FDB
Output part:
...
CREATE TABLE COST_METHOD (ID D_ID,
TITLE D_TITLE,
VERS D_VERS,
CONSTRAINT PK_COST_METHOD PRIMARY KEY (ID),
CONSTRAINT UNQ_COST_METHOD_TITLE UNIQUE (TITLE),
CONSTRAINT UNQ_COST_METHOD_VERS UNIQUE (VERS));
...

Bat IBExpert script show:
...
CREATE TABLE COST_METHOD (
ID D_ID /* D_ID = INTEGER NOT NULL */,
TITLE D_TITLE /* D_TITLE = VARCHAR(252) */,
VERS D_VERS /* D_VERS = INTEGER NOT NULL */
);
ALTER TABLE COST_METHOD ADD CONSTRAINT UNQ_COST_METHOD_TITLE UNIQUE (TITLE);
ALTER TABLE COST_METHOD ADD CONSTRAINT UNQ_COST_METHOD_VERS UNIQUE (VERS)
USING DESCENDING INDEX UNQ_COST_METHOD_VERS;
...

Commits: 78e090e b495af9 b261c3d eb51d83

[firebird-automations]

Modified by: Claudio Valderrama C. (robocop)

assignee: Claudio Valderrama C. [ robocop ]

[firebird-automations]

Commented by: Claudio Valderrama C. (robocop)

This is the list of constraints as seen in the system tables from a test db I created:

SQL> set list;
SQL> select * from rdb$relation_constraints order by rdb$constraint_type, rdb$constraint_name;

RDB$CONSTRAINT_NAME INTEG_5

RDB$CONSTRAINT_TYPE NOT NULL
RDB$RELATION_NAME BB

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME <null>

RDB$CONSTRAINT_NAME Z

RDB$CONSTRAINT_TYPE PRIMARY KEY
RDB$RELATION_NAME BB

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME Z

RDB$CONSTRAINT_NAME A

RDB$CONSTRAINT_TYPE UNIQUE
RDB$RELATION_NAME BB

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME A

RDB$CONSTRAINT_NAME AC

RDB$CONSTRAINT_TYPE UNIQUE
RDB$RELATION_NAME T

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME AI

RDB$CONSTRAINT_NAME AC2

RDB$CONSTRAINT_TYPE UNIQUE
RDB$RELATION_NAME T2

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME AC2

RDB$CONSTRAINT_NAME INTEGRAL

RDB$CONSTRAINT_TYPE UNIQUE
RDB$RELATION_NAME TEST

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME INTEGRAL

RDB$CONSTRAINT_NAME INTEG_6

RDB$CONSTRAINT_TYPE UNIQUE
RDB$RELATION_NAME DUP

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME RDB$3

RDB$CONSTRAINT_NAME INTEG_7

RDB$CONSTRAINT_TYPE UNIQUE
RDB$RELATION_NAME T3

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME I3

RDB$CONSTRAINT_NAME INTEG_8

RDB$CONSTRAINT_TYPE UNIQUE
RDB$RELATION_NAME NN

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME NN0

RDB$CONSTRAINT_NAME Z1

RDB$CONSTRAINT_TYPE UNIQUE
RDB$RELATION_NAME NN

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME NN1

RDB$CONSTRAINT_NAME Z2

RDB$CONSTRAINT_TYPE UNIQUE
RDB$RELATION_NAME NN

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME NN2

This is the new output:

F:\fb2dev\fbbuild\firebird2\temp\debug\firebird\bin>isql CONSTRAINT.FDB -x
Database: CONSTRAINT.FDB

SET SQL DIALECT 3;

/* CREATE DATABASE 'CONSTRAINT.FDB' DEFAULT CHARACTER SET NONE */

/* Table: BB, Owner: ATENEA\ADMINISTRATOR */
CREATE TABLE BB (Z INTEGER NOT NULL,
A INTEGER,
CONSTRAINT Z PRIMARY KEY (Z),
CONSTRAINT A UNIQUE (A));

/* Table: DUP, Owner: ATENEA\ADMINISTRATOR */
CREATE TABLE DUP (A INTEGER,
UNIQUE (A));

/* Table: NN, Owner: ATENEA\ADMINISTRATOR */
CREATE TABLE NN (A INTEGER,
B INTEGER,
C INTEGER,
UNIQUE (A) USING DESCENDING INDEX NN0,
CONSTRAINT Z1 UNIQUE (B) USING INDEX NN1,
CONSTRAINT Z2 UNIQUE (C) USING DESCENDING INDEX NN2);

/* Table: T, Owner: ATENEA\ADMINISTRATOR */
CREATE TABLE T (A INTEGER,
CONSTRAINT AC UNIQUE (A) USING DESCENDING INDEX AI);

/* Table: T2, Owner: ATENEA\ADMINISTRATOR */
CREATE TABLE T2 (A2 INTEGER,
CONSTRAINT AC2 UNIQUE (A2));

/* Table: T3, Owner: ATENEA\ADMINISTRATOR */
CREATE TABLE T3 (A3 INTEGER,
UNIQUE (A3) USING INDEX I3);

/* Table: TEST, Owner: ATENEA\ADMINISTRATOR */
CREATE TABLE TEST (A INTEGER,
CONSTRAINT INTEGRAL UNIQUE (A));

[firebird-automations]

Modified by: Claudio Valderrama C. (robocop)

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.1 Beta 2 [ 10190 ]

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to QA173 [ QA173 ]

[firebird-automations]

Modified by: @pcisar

Workflow: jira [ 12111 ] => Firebird [ 15569 ]

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test