FirebirdSQL/問答篇/改變資料表的索引名稱

出自VFP Wiki

(修訂版本間差異)
跳轉到: 導航, 搜尋
(revert)
 
(4個中途的修訂版本沒有顯示)

在2005年4月18日 (一) 01:35的最新修訂版本

By Douglas Tosi (douglas@improart.com.br)


Hello,

This is the solution I was able to get. It isn't beatiful, but works; What does it do? 1- Stores primary keys, foreign keys and its indexes in temporary tables; 2- Drop the stored primary keys and foreign keys. Their indexes will be dropped too; 3- Recreate primary keys using its name for the index.(PK_SAMPLE will use an index called PK_SAMPLE instead of RDB$PRIMARYxxx); 4- Recreate foreign keys using the the FKs name to name its index (as above);

Actually I could not change an index's name, so I created an automated way of recreating ALL FKs and PKs;

Problems: After a backup/restore I noticed duplicate indices; It seems to be random, but in every table there is 1 or 2 indices duplicated (no mored then that). Anyway, one of them will have the name I chose, so I can use the plan statement;


This was all done and tested in IB5.6; Every name is stored as CHAR(31) and that has probably changed in IB6 (sorry no IB6 here yet). A bit of work will be needed to make it work in version 6;

I did not test running it twice against the same db; It'll probably mess up;

I guess that is it. After a good day and a half of IB system tables I need a rest... :)

If you have the time to test it, please let me know if it worked or not!!! Any other comments/suggestions are also greatly appreciated. This code is probably (if nothing goes too wrong) going to arrive to our customers in fev/2001 as an update together with about 150 plan optimized stored procedures;

Thanks,

dog /*begin code*/

CREATE TABLE INDEX_SEGMENTS (

   INDEX_NAME CHAR(31),
   FIELD_NAME CHAR(31),
   FIELD_POSITION SMALLINT);

CREATE TABLE INDICES (

   INDEX_NAME CHAR(31),
   RELATION_NAME CHAR(31),
   UNIQUE_FLAG SMALLINT,
   SEGMENT_COUNT SMALLINT,
   INDEX_INACTIVE SMALLINT,
   INDEX_TYPE SMALLINT,
   FOREIGN_KEY CHAR(31));

CREATE TABLE REF_CONSTRAINTS (

   CONSTRAINT_NAME CHAR(31),
   CONST_NAME_UQ CHAR(31),
   UPDATE_RULE CHAR(11),
   DELETE_RULE CHAR(11));

CREATE TABLE RELATION_CONSTRAINTS (

   CONSTRAINT_NAME CHAR(31),
   CONSTRAINT_TYPE CHAR(11),
   RELATION_NAME CHAR(31),
   INDEX_NAME CHAR(31));

COMMIT WORK; SET TERM ?& ;

CREATE PROCEDURE STORE_CONSTRAINTS AS begin

 DELETE FROM REF_CONSTRAINTS;
 DELETE FROM RELATION_CONSTRAINTS;
 DELETE FROM INDICES;
 DELETE FROM INDEX_SEGMENTS;
 INSERT INTO REF_CONSTRAINTS
 (CONSTRAINT_NAME, CONST_NAME_UQ, UPDATE_RULE, DELETE_RULE) SELECT
  RDB$CONSTRAINT_NAME, RDB$CONST_NAME_UQ, RDB$UPDATE_RULE, RDB$DELETE_RULE
 FROM RDB$REF_CONSTRAINTS;
 INSERT INTO RELATION_CONSTRAINTS
 (CONSTRAINT_NAME, CONSTRAINT_TYPE, RELATION_NAME, INDEX_NAME) SELECT
  RDB$CONSTRAINT_NAME, RDB$CONSTRAINT_TYPE, RDB$RELATION_NAME,

RDB$INDEX_NAME

  FROM RDB$RELATION_CONSTRAINTS
  WHERE RDB$CONSTRAINT_TYPE IN ('FOREIGN KEY', 'PRIMARY KEY');
 INSERT INTO INDICES
 (INDEX_NAME, RELATION_NAME, UNIQUE_FLAG, SEGMENT_COUNT, INDEX_INACTIVE,

INDEX_TYPE, FOREIGN_KEY) SELECT

  RDB$INDEX_NAME, RDB$RELATION_NAME, RDB$UNIQUE_FLAG, RDB$SEGMENT_COUNT,

RDB$INDEX_INACTIVE, RDB$INDEX_TYPE, RDB$FOREIGN_KEY

  FROM RDB$INDICES
  WHERE (RDB$SYSTEM_FLAG IS NULL OR RDB$SYSTEM_FLAG = 0) AND
         RDB$INDEX_NAME IN (SELECT INDEX_NAME FROM RELATION_CONSTRAINTS);
 INSERT INTO INDEX_SEGMENTS
 (INDEX_NAME, FIELD_NAME, FIELD_POSITION) SELECT
  RDB$INDEX_NAME, RDB$FIELD_NAME, RDB$FIELD_POSITION
  FROM RDB$INDEX_SEGMENTS
  WHERE RDB$INDEX_NAME IN (SELECT INDEX_NAME FROM INDICES);

end?&

CREATE PROCEDURE DROP_CONSTRAINTS AS

 DECLARE VARIABLE CONS_NAME VARCHAR(31);

begin

 FOR SELECT CONSTRAINT_NAME
     FROM RELATION_CONSTRAINTS
     WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
     INTO :CONS_NAME DO
 BEGIN
   DELETE FROM RDB$RELATION_CONSTRAINTS
   WHERE RDB$CONSTRAINT_NAME = :CONS_NAME;
 END
 FOR SELECT CONSTRAINT_NAME
     FROM RELATION_CONSTRAINTS
     WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
     INTO :CONS_NAME DO
 BEGIN
   DELETE FROM RDB$RELATION_CONSTRAINTS
   WHERE RDB$CONSTRAINT_NAME = :CONS_NAME;
 END

end?&

CREATE PROCEDURE RECREATE_PKS AS

 DECLARE VARIABLE CONS_NAME VARCHAR(31);
 DECLARE VARIABLE IDX_NAME VARCHAR(31);
 DECLARE VARIABLE TABLE_NAME VARCHAR(31);
 DECLARE VARIABLE IDX_UNIQUE SMALLINT;
 DECLARE VARIABLE IDX_SEGCOUNT SMALLINT;
 DECLARE VARIABLE IDX_INACTIVE SMALLINT;
 DECLARE VARIABLE IDX_TYPE SMALLINT;
 DECLARE VARIABLE IDX_FOREIGNKEY VARCHAR(31);
 DECLARE VARIABLE IDXSEG_FIELDNAME VARCHAR(31);
 DECLARE VARIABLE IDXSEG_POSITION SMALLINT;

begin

 FOR SELECT RELC.CONSTRAINT_NAME, RELC.INDEX_NAME, IND.RELATION_NAME,
            IND.UNIQUE_FLAG, IND.SEGMENT_COUNT,
            IND.INDEX_INACTIVE, IND.INDEX_TYPE, IND.FOREIGN_KEY
     FROM RELATION_CONSTRAINTS RELC
          INNER JOIN INDICES IND
                ON IND.INDEX_NAME = RELC.INDEX_NAME
     WHERE (RELC.CONSTRAINT_TYPE = 'PRIMARY KEY') AND
           (RELC.CONSTRAINT_NAME <> RELC.INDEX_NAME)
     ORDER BY INDEX_NAME
     INTO :CONS_NAME, :IDX_NAME, :TABLE_NAME,
          :IDX_UNIQUE, :IDX_SEGCOUNT,
          :IDX_INACTIVE, :IDX_TYPE, :IDX_FOREIGNKEY DO
 BEGIN
   INSERT INTO RDB$INDICES
   (RDB$INDEX_NAME, RDB$RELATION_NAME, RDB$UNIQUE_FLAG,
    RDB$SEGMENT_COUNT, RDB$INDEX_INACTIVE, RDB$INDEX_TYPE,

RDB$FOREIGN_KEY)

   VALUES
   (:CONS_NAME, :TABLE_NAME, :IDX_UNIQUE,
    :IDX_SEGCOUNT, :IDX_INACTIVE, :IDX_TYPE, :IDX_FOREIGNKEY);
   FOR SELECT FIELD_NAME, FIELD_POSITION
       FROM INDEX_SEGMENTS
       WHERE INDEX_NAME = :IDX_NAME
       INTO :IDXSEG_FIELDNAME, :IDXSEG_POSITION DO
   BEGIN
     INSERT INTO RDB$INDEX_SEGMENTS
     (RDB$INDEX_NAME, RDB$FIELD_NAME, RDB$FIELD_POSITION)
     VALUES
     (:CONS_NAME, :IDXSEG_FIELDNAME, :IDXSEG_POSITION);
   END
   INSERT INTO RDB$RELATION_CONSTRAINTS
   (RDB$CONSTRAINT_NAME, RDB$CONSTRAINT_TYPE, RDB$RELATION_NAME,

RDB$DEFERRABLE,

    RDB$INITIALLY_DEFERRED, RDB$INDEX_NAME)
   VALUES
   (:CONS_NAME, 'PRIMARY KEY', :TABLE_NAME, 'NO',
    'NO', :CONS_NAME);
 END

end?&

CREATE PROCEDURE RECREATE_FKS AS

 DECLARE VARIABLE CONS_NAME VARCHAR(31);
 DECLARE VARIABLE IDX_NAME VARCHAR(31);
 DECLARE VARIABLE TABLE_NAME VARCHAR(31);
 DECLARE VARIABLE IDX_UNIQUE SMALLINT;
 DECLARE VARIABLE IDX_SEGCOUNT SMALLINT;
 DECLARE VARIABLE IDX_INACTIVE SMALLINT;
 DECLARE VARIABLE IDX_TYPE SMALLINT;
 DECLARE VARIABLE IDX_FOREIGNKEY VARCHAR(31);
 DECLARE VARIABLE REFC_UPDATERULE VARCHAR(11);
 DECLARE VARIABLE REFC_DELRULE VARCHAR(11);
 DECLARE VARIABLE REFC_NAME VARCHAR(31);
 DECLARE VARIABLE IDXSEG_FIELDNAME VARCHAR(31);
 DECLARE VARIABLE IDXSEG_POSITION SMALLINT;

begin

 FOR SELECT RELC.CONSTRAINT_NAME, RELC.INDEX_NAME, IND.RELATION_NAME,
            IND.UNIQUE_FLAG, IND.SEGMENT_COUNT, IND.FOREIGN_KEY,
            IND.INDEX_INACTIVE, IND.INDEX_TYPE,
            REFC.CONST_NAME_UQ, REFC.UPDATE_RULE, REFC.DELETE_RULE
     FROM RELATION_CONSTRAINTS RELC
          INNER JOIN INDICES IND
                ON IND.INDEX_NAME = RELC.INDEX_NAME
          INNER JOIN REF_CONSTRAINTS REFC
                ON REFC.CONSTRAINT_NAME = RELC.CONSTRAINT_NAME
     WHERE (RELC.CONSTRAINT_TYPE = 'FOREIGN KEY')
     ORDER BY INDEX_NAME
     INTO :CONS_NAME, :IDX_NAME, :TABLE_NAME,
          :IDX_UNIQUE, :IDX_SEGCOUNT, :IDX_FOREIGNKEY,
          :IDX_INACTIVE, :IDX_TYPE,
          :REFC_NAME, :REFC_UPDATERULE, :REFC_DELRULE DO
 BEGIN
   SELECT CONSTRAINT_NAME
   FROM RELATION_CONSTRAINTS
   WHERE INDEX_NAME = :IDX_FOREIGNKEY
   INTO :IDX_FOREIGNKEY;
   INSERT INTO RDB$INDICES
   (RDB$INDEX_NAME, RDB$RELATION_NAME, RDB$UNIQUE_FLAG,
    RDB$SEGMENT_COUNT, RDB$INDEX_INACTIVE, RDB$INDEX_TYPE,

RDB$FOREIGN_KEY)

   VALUES
   (:CONS_NAME, :TABLE_NAME, :IDX_UNIQUE,
    :IDX_SEGCOUNT, :IDX_INACTIVE, :IDX_TYPE, :IDX_FOREIGNKEY);
   FOR SELECT FIELD_NAME, FIELD_POSITION
       FROM INDEX_SEGMENTS
       WHERE INDEX_NAME = :IDX_NAME
       INTO :IDXSEG_FIELDNAME, :IDXSEG_POSITION DO
   BEGIN
     INSERT INTO RDB$INDEX_SEGMENTS
     (RDB$INDEX_NAME, RDB$FIELD_NAME, RDB$FIELD_POSITION)
     VALUES
     (:CONS_NAME, :IDXSEG_FIELDNAME, :IDXSEG_POSITION);
   END
   INSERT INTO RDB$RELATION_CONSTRAINTS
   (RDB$CONSTRAINT_NAME, RDB$CONSTRAINT_TYPE, RDB$RELATION_NAME,

RDB$DEFERRABLE,

    RDB$INITIALLY_DEFERRED, RDB$INDEX_NAME)
   VALUES
   (:CONS_NAME, 'FOREIGN KEY', :TABLE_NAME, 'NO',
    'NO', :CONS_NAME);
   INSERT INTO RDB$REF_CONSTRAINTS
   (RDB$CONSTRAINT_NAME, RDB$CONST_NAME_UQ, RDB$MATCH_OPTION,

RDB$UPDATE_RULE, RDB$DELETE_RULE)

   VALUES
   (:CONS_NAME, :REFC_NAME, 'FULL', :REFC_UPDATERULE, :REFC_DELRULE);
 END

end?&

COMMIT WORK?&

EXECUTE PROCEDURE STORE_PROCEDURE ?& COMMIT WORK?&

EXECUTE PROCEDURE DROP_CONSTRAINTS ?& COMMIT WORK?&

EXECUTE PROCEDURE RECREATE_PKS ?& COMMIT WORK?&

EXECUTE PROCEDURE RECREATE_FKS ?& COMMIT WORK?&


/*end code*/