|
Registriert seit: 7. Feb 2006
429 Beiträge
|
Re: Index-Probleme mit Interbase
6. Mär 2006, 16:22
bitteschön:
SQL-Code:
/******************************************************************************/
/**** Generated by IBExpert 2006.01.29 02.03.2006 17:11:39 ****/
/******************************************************************************/
SET SQL DIALECT 3;
SET NAMES NONE;
CREATE DATABASE ' C:\Dokumente und Einstellungen\Sancho\Eigene Dateien\LEER.GDB'
USER ' sysdba' PASSWORD ' masterkey'
PAGE_SIZE 4096
DEFAULT CHARACTER SET NONE;
/******************************************************************************/
/**** Domains ****/
/******************************************************************************/
CREATE DOMAIN CHAR_1 AS
CHAR(1) CHARACTER SET UNICODE_FSS;
CREATE DOMAIN CHAR_10 AS
CHAR(10) CHARACTER SET UNICODE_FSS;
CREATE DOMAIN CHAR_100 AS
CHAR(100) CHARACTER SET UNICODE_FSS;
CREATE DOMAIN CHAR_14 AS
CHAR(10) CHARACTER SET UNICODE_FSS;
CREATE DOMAIN CHAR_15 AS
CHAR(15) CHARACTER SET UNICODE_FSS;
CREATE DOMAIN CHAR_20 AS
CHAR(20) CHARACTER SET UNICODE_FSS;
CREATE DOMAIN CHAR_3 AS
CHAR(10) CHARACTER SET UNICODE_FSS;
CREATE DOMAIN CHAR_4 AS
CHAR(4) CHARACTER SET UNICODE_FSS;
CREATE DOMAIN CHAR_40 AS
CHAR(40) CHARACTER SET UNICODE_FSS;
CREATE DOMAIN CHAR_50 AS
CHAR(50) CHARACTER SET UNICODE_FSS;
CREATE DOMAIN CHAR_60 AS
CHAR(60) CHARACTER SET UNICODE_FSS;
CREATE DOMAIN CHAR_80 AS
CHAR(80) CHARACTER SET UNICODE_FSS;
CREATE DOMAIN MEMOBLOB AS
BLOB SUB_TYPE 1 SEGMENT SIZE 4096 CHARACTER SET UNICODE_FSS;
/******************************************************************************/
/**** Generators ****/
/******************************************************************************/
CREATE GENERATOR GEN_AUT_ID;
SET GENERATOR GEN_AUT_ID TO 0;
CREATE GENERATOR GEN_DICENTRIES_ID;
SET GENERATOR GEN_DICENTRIES_ID TO 0;
CREATE GENERATOR GEN_DSKRPTLINK_DICENTRIES_ID;
SET GENERATOR GEN_DSKRPTLINK_DICENTRIES_ID TO 0;
CREATE GENERATOR GEN_DSKRPTLINK_D_LIT_ID;
SET GENERATOR GEN_DSKRPTLINK_D_LIT_ID TO 0;
CREATE GENERATOR GEN_DSKRPTS_ID;
SET GENERATOR GEN_DSKRPTS_ID TO 0;
CREATE GENERATOR GEN_D_LIT_ID;
SET GENERATOR GEN_D_LIT_ID TO 0;
CREATE GENERATOR GEN_LIT_ID;
SET GENERATOR GEN_LIT_ID TO 0;
CREATE GENERATOR GEN_TITELAUFS_LIT_ID;
SET GENERATOR GEN_TITELAUFS_LIT_ID TO 0;
CREATE GENERATOR GEN_TITELWERK_LIT_ID;
SET GENERATOR GEN_TITELWERK_LIT_ID TO 0;
/******************************************************************************/
/**** Tables ****/
/******************************************************************************/
CREATE TABLE AUT (
ID INTEGER NOT NULL,
AUT CHAR_3,
VF CHAR_50,
FUNKTION CHAR_50,
FIRMA1 CHAR_50,
FIRMA2 CHAR_50,
STRASSE CHAR_40,
PLZ CHAR_10,
ORT CHAR_40,
LAND CHAR_20,
TEL CHAR_20,
TELPRIV CHAR_20,
FAX CHAR_20,
ANM CHAR_80,
DATUM DATE,
TYP CHAR_1
);
CREATE TABLE D_LIT (
ID INTEGER NOT NULL,
ID_LIT SMALLINT NOT NULL
);
CREATE TABLE DICENTRIES (
ID INTEGER NOT NULL,
ASTERM CHAR_80,
ASABK CHAR_10,
ASPRGM CHAR_20,
ASSEM CHAR_80,
ZSTERM CHAR_80,
ZSABK CHAR_10,
ZSPRGM CHAR_20,
ZSSEM CHAR_80,
DATUM DATE,
PROJ CHAR_20,
REV CHAR_1,
UPDDATUM DATE,
ASVERW INTEGER,
ZSVERW INTEGER,
ASDEF MEMOBLOB,
ZSDEF MEMOBLOB,
ASAUDIO CHAR_20,
ASVIDEO CHAR_20,
ASABBILDUNG CHAR_20,
ASQCODE INTEGER,
ZSQCODE INTEGER,
AUT INTEGER,
UPDAUT INTEGER,
ZSABBILDUNG CHAR_20,
ZSAUDIO CHAR_20,
ZSVIDEO CHAR_20
);
CREATE TABLE DSKRPTLINK_D_LIT (
ID INTEGER NOT NULL,
ID_D_LIT INTEGER NOT NULL,
ID_DSKRPT SMALLINT NOT NULL
);
CREATE TABLE DSKRPTLINK_DICENTRIES (
ID_DICENTRY INTEGER NOT NULL,
ID_DSKRPT INTEGER NOT NULL,
ID INTEGER NOT NULL
);
CREATE TABLE DSKRPTS (
ASDSKRPT CHAR_10 NOT NULL,
ZSDSKRPT CHAR_10 NOT NULL,
ASLANG CHAR_50,
ZSLANG CHAR_50,
ID INTEGER NOT NULL
);
CREATE TABLE LIT (
ID INTEGER NOT NULL,
VF CHAR_50,
HRSG CHAR_50,
ORT CHAR_20,
VERLAG CHAR_20,
BANDHEFT CHAR_3,
JAHR CHAR_4,
SEITE CHAR_10,
PREIS CHAR_10,
ISBN CHAR_14,
STANDNR CHAR_20,
ANM1 CHAR_60,
ANM2 CHAR_60,
AUT INTEGER,
DATUM DATE,
TYP CHAR_3,
SPRACHEN CHAR_20,
QCODE CHAR_15
);
CREATE TABLE TITELAUFS_LIT (
ID INTEGER NOT NULL,
TITELAUFS CHAR_60 NOT NULL,
LIT_ID INTEGER NOT NULL
);
CREATE TABLE TITELWERK_LIT (
ID INTEGER NOT NULL,
TITELWERK CHAR_60 NOT NULL,
ID_LIT INTEGER NOT NULL
);
/******************************************************************************/
/**** Unique Constraints ****/
/******************************************************************************/
ALTER TABLE DSKRPTS ADD CONSTRAINT UNQ_DSKRPTS UNIQUE (ASDSKRPT, ZSDSKRPT);
/******************************************************************************/
/**** Primary Keys ****/
/******************************************************************************/
ALTER TABLE AUT ADD CONSTRAINT PK_AUT PRIMARY KEY (ID);
ALTER TABLE DICENTRIES ADD PRIMARY KEY (ID);
ALTER TABLE DSKRPTLINK_DICENTRIES ADD PRIMARY KEY (ID);
ALTER TABLE DSKRPTLINK_D_LIT ADD CONSTRAINT PK_DSKRPTLINK_D_LIT PRIMARY KEY (ID);
ALTER TABLE DSKRPTS ADD PRIMARY KEY (ID);
ALTER TABLE D_LIT ADD CONSTRAINT PK_D_LIT PRIMARY KEY (ID);
ALTER TABLE LIT ADD CONSTRAINT PK_LIT PRIMARY KEY (ID);
ALTER TABLE TITELAUFS_LIT ADD CONSTRAINT PK_TITELAUFS_LIT PRIMARY KEY (ID);
ALTER TABLE TITELWERK_LIT ADD CONSTRAINT PK_TITELWERK_LIT PRIMARY KEY (ID);
/******************************************************************************/
/**** Foreign Keys ****/
/******************************************************************************/
ALTER TABLE DICENTRIES ADD CONSTRAINT FK_DICENTRIES_1 FOREIGN KEY (ASVERW) REFERENCES DICENTRIES (ID) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
ALTER TABLE DICENTRIES ADD CONSTRAINT FK_DICENTRIES_2 FOREIGN KEY (ZSVERW) REFERENCES DICENTRIES (ID) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
ALTER TABLE DICENTRIES ADD CONSTRAINT FK_DICENTRIES_3 FOREIGN KEY (ASQCODE) REFERENCES LIT (ID) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
ALTER TABLE DICENTRIES ADD CONSTRAINT FK_DICENTRIES_4 FOREIGN KEY (ZSQCODE) REFERENCES LIT (ID) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
ALTER TABLE DICENTRIES ADD CONSTRAINT FK_DICENTRIES_5 FOREIGN KEY (AUT) REFERENCES AUT (ID) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
ALTER TABLE DICENTRIES ADD CONSTRAINT FK_DICENTRIES_6 FOREIGN KEY (UPDAUT) REFERENCES AUT (ID) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
ALTER TABLE DSKRPTLINK_DICENTRIES ADD CONSTRAINT FK_DSKRPTLINK_DICENTRIES_1 FOREIGN KEY (ID_DICENTRY) REFERENCES DICENTRIES (ID) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
ALTER TABLE DSKRPTLINK_DICENTRIES ADD CONSTRAINT FK_DSKRPTLINK_DICENTRIES_2 FOREIGN KEY (ID_DSKRPT) REFERENCES DSKRPTS (ID) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
ALTER TABLE DSKRPTLINK_D_LIT ADD CONSTRAINT FK_DSKRPTLINK_D_LIT_1 FOREIGN KEY (ID_D_LIT) REFERENCES D_LIT (ID) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
ALTER TABLE DSKRPTLINK_D_LIT ADD CONSTRAINT FK_DSKRPTLINK_D_LIT_2 FOREIGN KEY (ID_DSKRPT) REFERENCES DSKRPTS (ID) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
ALTER TABLE D_LIT ADD CONSTRAINT FK_D_LIT_1 FOREIGN KEY (ID_LIT) REFERENCES LIT (ID) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
ALTER TABLE TITELAUFS_LIT ADD CONSTRAINT FK_TITELAUFS_LIT_1 FOREIGN KEY (LIT_ID) REFERENCES LIT (ID) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
ALTER TABLE TITELWERK_LIT ADD CONSTRAINT FK_TITELWERK_LIT_1 FOREIGN KEY (ID_LIT) REFERENCES LIT (ID) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
/******************************************************************************/
/**** Indices ****/
/******************************************************************************/
CREATE UNIQUE INDEX AUT_IDX1 ON AUT (AUT);
CREATE INDEX AUT_IDX2 ON AUT (FIRMA1);
CREATE INDEX AUT_IDX3 ON AUT (FIRMA2);
CREATE INDEX AUT_IDX4 ON AUT (ORT);
CREATE INDEX AUT_IDX5 ON AUT (PLZ);
CREATE INDEX AUT_IDX6 ON AUT (TYP);
CREATE INDEX AUT_IDX7 ON AUT (VF);
CREATE INDEX DICENTRIES_IDX1 ON DICENTRIES (ASABK);
CREATE INDEX DICENTRIES_IDX10 ON DICENTRIES (ZSQCODE);
CREATE INDEX DICENTRIES_IDX11 ON DICENTRIES (ZSTERM);
CREATE INDEX DICENTRIES_IDX2 ON DICENTRIES (ASTERM);
CREATE INDEX DICENTRIES_IDX3 ON DICENTRIES (DATUM);
CREATE INDEX DICENTRIES_IDX4 ON DICENTRIES (PROJ);
CREATE INDEX DICENTRIES_IDX5 ON DICENTRIES (REV);
CREATE INDEX DICENTRIES_IDX6 ON DICENTRIES (AUT);
CREATE INDEX DICENTRIES_IDX7 ON DICENTRIES (UPDAUT);
CREATE INDEX DICENTRIES_IDX8 ON DICENTRIES (UPDDATUM);
CREATE INDEX DICENTRIES_IDX9 ON DICENTRIES (ZSABK);
CREATE INDEX GEN_DSKRPTLINK_DICENTRIES_IDX1 ON DSKRPTLINK_DICENTRIES (ID_DICENTRY);
CREATE INDEX GEN_DSKRPTLINK_DICENTRIES_IDX2 ON DSKRPTLINK_DICENTRIES (ID_DSKRPT);
CREATE UNIQUE INDEX DSKRPTS_IDX1 ON DSKRPTS (ASDSKRPT);
CREATE UNIQUE INDEX DSKRPTS_IDX2 ON DSKRPTS (ZSDSKRPT);
CREATE INDEX LIT_IDX10 ON LIT (VERLAG);
CREATE INDEX LIT_IDX6 ON LIT (HRSG);
CREATE INDEX LIT_IDX7 ON LIT (QCODE);
CREATE INDEX LIT_IDX8 ON LIT (TYP);
CREATE INDEX LIT_IDX9 ON LIT (VF);
/******************************************************************************/
/**** Triggers ****/
/******************************************************************************/
SET TERM ^ ;
/******************************************************************************/
/**** Triggers for tables ****/
/******************************************************************************/
/* Trigger: AUT_BI0 */
CREATE TRIGGER AUT_BI0 FOR AUT
ACTIVE BEFORE INSERT POSITION 0
AS
begin
new.ID = gen_id(gen_aut_id,1);
end
^
/* Trigger: DICENTRIES_BI0 */
CREATE TRIGGER DICENTRIES_BI0 FOR DICENTRIES
ACTIVE BEFORE INSERT POSITION 0
as
begin
new.ID = gen_id(gen_dicentries_id,1);
end
^
/* Trigger: DSKRPTLINK_DICENTRIES_BI0 */
CREATE TRIGGER DSKRPTLINK_DICENTRIES_BI0 FOR DSKRPTLINK_DICENTRIES
ACTIVE BEFORE INSERT POSITION 0
AS
begin
new.ID = gen_id(GEN_DSKRPTLINK_DICENTRIES_ID,1);
end
^
/* Trigger: DSKRPTLINK_D_LIT_BI0 */
CREATE TRIGGER DSKRPTLINK_D_LIT_BI0 FOR DSKRPTLINK_D_LIT
ACTIVE BEFORE INSERT POSITION 0
AS
begin
new.ID = gen_id(GEN_DSKRPTLINK_D_LIT_ID,1);
end
^
/* Trigger: DSKRPTS_BI0 */
CREATE TRIGGER DSKRPTS_BI0 FOR DSKRPTS
ACTIVE BEFORE INSERT POSITION 0
as
begin
new.ID = gen_id(gen_dskrpts_id,1);
end
^
/* Trigger: D_LIT_BI0 */
CREATE TRIGGER D_LIT_BI0 FOR D_LIT
ACTIVE BEFORE INSERT POSITION 0
AS
begin
new.ID = gen_id(GEN_D_LIT_ID,1);
end
^
/* Trigger: LIT_BI0 */
CREATE TRIGGER LIT_BI0 FOR LIT
ACTIVE BEFORE INSERT POSITION 0
AS
begin
new.ID = gen_id(gen_lit_id,1);
end
^
/* Trigger: TITELAUFS_LIT_BI0 */
CREATE TRIGGER TITELAUFS_LIT_BI0 FOR TITELAUFS_LIT
ACTIVE BEFORE INSERT POSITION 0
AS
begin
new.ID = gen_id(gen_titelaufs_lit_id,1);
end
^
/* Trigger: TITELWERK_LIT_BI0 */
CREATE TRIGGER TITELWERK_LIT_BI0 FOR TITELWERK_LIT
ACTIVE BEFORE INSERT POSITION 0
AS
begin
new.ID = gen_id(gen_titelwerk_lit_id,1);
end
^
SET TERM ; ^
Um Rekursion zu verstehen, muss man zunächst Rekursion verstehen.
|