Ziele Untersuchung
mit Columbo Integrität von
Datenbanken Interaktion und
Portierbarkeit Ergonomie der
Schnittstellen

Angebot Produkte Projekt Beratung

Mittel Analytik Modellierung Sprachen Algebra Logik Hardware Denken Kreativität

Zusammenhänge Gesellschaft Wirtschaft Branche Firma


products/sources/formale Sprachen/Cobol/Test-Suite/SQL M/ Datei vom 4.1.2008 mit Größe 18 kB

Quelle dml117.mco Sprache: unbekannt

-- SQL Test Suite, V6.0, SQL Module dml117.mco
-- 59-byte ID
--
MODULE DML117
LANGUAGE COBOL
AUTHORIZATION FLATER

PROCEDURE SUB1 SQLCODE SQLSTATE
    :UIDX CHAR(18);
      SELECT USER INTO :UIDX FROM HU.ECCO;

PROCEDURE SUB2 SQLCODE SQLSTATE;
      ROLLBACK WORK;

PROCEDURE SUB3 SQLCODE SQLSTATE;
      CREATE TABLE LUSERS (
                    PRIMARY KEY (LUSER_ID),
                    NAAM         CHAR (10),
                    LUSER_ID     INT,
                    FILE_QUOTA   INT,
                    FILE_USAGE   INT NOT NULL,
                    CHECK (FILE_USAGE >= 0 AND
                    (FILE_QUOTA IS NULL OR FILE_QUOTA >= FILE_USAGE)))
                 ;

PROCEDURE SUB4 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB5 SQLCODE SQLSTATE;
      CREATE TABLE LUSER_DATA (
                    FOREIGN KEY (LUSER_ID) REFERENCES LUSERS ON DELETE
                  CASCADE,
                    PRIMARY KEY (FILE_NAME, LUSER_ID),
                    FILE_NAME     CHAR (8) NOT NULL,
                    LUSER_ID     INT NOT NULL,
                    LUSER_DATA   CHAR (30));

PROCEDURE SUB6 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB7 SQLCODE SQLSTATE;
      CREATE TABLE AUDIT_CODES (
                    ACTION_KEY       INT PRIMARY KEY,
                    LUSER_ACTION     CHAR (6) NOT NULL,
                    CHECK (LUSER_ACTION = 'INSERT' OR LUSER_ACTION = 'ACCVIO'
                    OR LUSER_ACTION = 'DELETE'));

PROCEDURE SUB8 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB9 SQLCODE SQLSTATE;
      CREATE TABLE ALL_USER_IDS (LUSER_ID INT UNIQUE)
                 ;

PROCEDURE SUB10 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB11 SQLCODE SQLSTATE;
      CREATE TABLE AUDIT_RECORDS (
                    FOREIGN KEY (LUSER_ID) REFERENCES LUSERS ON DELETE SET
                  NULL,
                    LUSER_ID         INT,
                    SAVED_LUSER_ID   INT NOT NULL
                    REFERENCES ALL_USER_IDS (LUSER_ID) ON DELETE NO ACTION,
                    ACTION_KEY       INT DEFAULT 0 NOT NULL
                    REFERENCES AUDIT_CODES ON DELETE SET DEFAULT);

PROCEDURE SUB12 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB13 SQLCODE SQLSTATE;
      INSERT INTO AUDIT_CODES VALUES (0, 'ACCVIO')
                 ;

PROCEDURE SUB14 SQLCODE SQLSTATE;
      INSERT INTO AUDIT_CODES VALUES (1, 'INSERT')
                 ;

PROCEDURE SUB15 SQLCODE SQLSTATE;
      INSERT INTO AUDIT_CODES VALUES (2, 'DELETE')
                 ;

PROCEDURE SUB16 SQLCODE SQLSTATE;
      INSERT INTO LUSERS VALUES ('root', 0, NULL, 2)
                 ;

PROCEDURE SUB17 SQLCODE SQLSTATE;
      INSERT INTO LUSERS VALUES ('BIFF', 1, 0, 0)
                 ;

PROCEDURE SUB18 SQLCODE SQLSTATE;
      INSERT INTO LUSERS VALUES ('Kibo', 2, 1, 1)
                 ;

PROCEDURE SUB19 SQLCODE SQLSTATE;
      INSERT INTO ALL_USER_IDS VALUES (0);

PROCEDURE SUB20 SQLCODE SQLSTATE;
      INSERT INTO ALL_USER_IDS VALUES (1);

PROCEDURE SUB21 SQLCODE SQLSTATE;
      INSERT INTO ALL_USER_IDS VALUES (2);

PROCEDURE SUB22 SQLCODE SQLSTATE;
      INSERT INTO LUSER_DATA VALUES ('ROOT1',
                    0, 'BIFF is a total loser');

PROCEDURE SUB23 SQLCODE SQLSTATE;
      INSERT INTO LUSER_DATA VALUES ('ROOT2',
                    0, 'Kibo wastes disk space');

PROCEDURE SUB24 SQLCODE SQLSTATE;
      INSERT INTO AUDIT_RECORDS VALUES (0, 0, 1)
                 ;

PROCEDURE SUB25 SQLCODE SQLSTATE;
      INSERT INTO AUDIT_RECORDS VALUES (0, 0, 1)
                 ;

PROCEDURE SUB26 SQLCODE SQLSTATE;
      INSERT INTO AUDIT_RECORDS VALUES (2, 2, 1)
                 ;

PROCEDURE SUB27 SQLCODE SQLSTATE;
      INSERT INTO AUDIT_RECORDS VALUES (1, 1, 0)
                 ;

PROCEDURE SUB28 SQLCODE SQLSTATE;
      INSERT INTO AUDIT_RECORDS VALUES (2, 2, 0)
                 ;

PROCEDURE SUB29 SQLCODE SQLSTATE;
      INSERT INTO AUDIT_RECORDS VALUES (2, 2, 2)
                 ;

PROCEDURE SUB30 SQLCODE SQLSTATE;
      INSERT INTO AUDIT_RECORDS VALUES (2, 2, 1)
                 ;

PROCEDURE SUB31 SQLCODE SQLSTATE;
      INSERT INTO LUSER_DATA VALUES ('HAHA',
                    2, 'I G0T KIB0Z PASSW0RD!!!');

PROCEDURE SUB32 SQLCODE SQLSTATE;
      DELETE FROM LUSERS WHERE NAAM <> 'root';

PROCEDURE SUB33 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1 FROM LUSER_DATA
                 ;

PROCEDURE SUB34 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1 FROM LUSERS;

PROCEDURE SUB35 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1 FROM AUDIT_RECORDS
                 ;

PROCEDURE SUB36 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1 FROM AUDIT_RECORDS
                    WHERE LUSER_ID IS NULL;

PROCEDURE SUB37 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1 FROM AUDIT_RECORDS
                    WHERE SAVED_LUSER_ID IS NULL;

PROCEDURE SUB38 SQLCODE SQLSTATE;
      DELETE FROM AUDIT_CODES
                    WHERE LUSER_ACTION = 'DELETE';

PROCEDURE SUB39 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1 FROM AUDIT_RECORDS
                    WHERE ACTION_KEY = 2;

PROCEDURE SUB40 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1 FROM AUDIT_RECORDS
                    WHERE ACTION_KEY = 0;

PROCEDURE SUB41 SQLCODE SQLSTATE;
      DELETE FROM ALL_USER_IDS;

PROCEDURE SUB42 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB43 SQLCODE SQLSTATE;
      DROP TABLE AUDIT_RECORDS CASCADE;

PROCEDURE SUB44 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB45 SQLCODE SQLSTATE;
      DROP TABLE ALL_USER_IDS CASCADE;

PROCEDURE SUB46 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB47 SQLCODE SQLSTATE;
      DROP TABLE AUDIT_CODES CASCADE;

PROCEDURE SUB48 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB49 SQLCODE SQLSTATE;
      DROP TABLE LUSER_DATA CASCADE;

PROCEDURE SUB50 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB51 SQLCODE SQLSTATE;
      DROP TABLE LUSERS CASCADE;

PROCEDURE SUB52 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB53 SQLCODE SQLSTATE;
      INSERT INTO HU.TESTREPORT
                      VALUES('0645','pass','MCO');

PROCEDURE SUB54 SQLCODE SQLSTATE;
      INSERT INTO HU.TESTREPORT
                      VALUES('0645','fail','MCO');

PROCEDURE SUB55 SQLCODE SQLSTATE;
      COMMIT WORK;