create_script.ddl

-- Generated by Oracle SQL Developer Data Modeler 18.4.0.339.1536
--   at:        2019-04-20 23:44:52 CEST
--   site:      Oracle Database 11g
--   type:      Oracle Database 11g

--------------------------------------------------------------------------------------------------------------------------------

create or replace procedure DELETE_ALL_TABLES AS
begin
  for iRec in 
    (select distinct OBJECT_TYPE, OBJECT_NAME,
      'drop '||OBJECT_TYPE||' "'||OBJECT_NAME||'"'||
      case OBJECT_TYPE when 'TABLE' then ' cascade constraints purge' else ' ' end as COMMAND
    from USER_OBJECTS where OBJECT_NAME not in ('DELETE_ALL_TABLES', 'SWITCH_FK_OFF', 'SWITCH_FK_ON', 'DELETE_ALL_DATA_FROM_TABLES')
    ) loop
        begin
          dbms_output.put_line('Command: '||irec.COMMAND);
        execute immediate iRec.COMMAND;
        exception
          when others then dbms_output.put_line('FAILED!');
        end;
      end loop;
end;
/

--------------------------------------------------------------------------------------------------------------------------------

create or replace procedure SWITCH_FK_OFF as 
begin
  for cur in (select CONSTRAINT_NAME, TABLE_NAME from USER_CONSTRAINTS where CONSTRAINT_TYPE = 'R' ) 
  loop
    execute immediate 'alter table '||cur.TABLE_NAME||' modify constraint "'||cur.CONSTRAINT_NAME||'" DISABLE';
  end loop;
end SWITCH_FK_OFF;
/

--------------------------------------------------------------------------------------------------------------------------------

create or replace procedure SWITCH_FK_ON as 
begin
  for cur in (select CONSTRAINT_NAME, TABLE_NAME from USER_CONSTRAINTS where CONSTRAINT_TYPE = 'R' ) 
  loop
    execute immediate 'alter table '||cur.TABLE_NAME||' modify constraint "'||cur.CONSTRAINT_NAME||'" enable validate';
  end loop;
end SWITCH_FK_ON;
/

--------------------------------------------------------------------------------------------------------------------------------

create or replace procedure DELETE_ALL_DATA_FROM_TABLES is
begin
  SWITCH_FK_OFF;
  for v_rec in (select distinct TABLE_NAME from USER_TABLES)
  loop
    execute immediate 'truncate table '||v_rec.TABLE_NAME||' drop storage';
  end loop;
  SWITCH_FK_ON;
  
  for v_rec in (select distinct SEQUENCE_NAME  from USER_SEQUENCES)
  loop
    execute immediate 'alter sequence '||v_rec.SEQUENCE_NAME||' restart start with 1';
  end loop;
end DELETE_ALL_DATA_FROM_TABLES;
/

--------------------------------------------------------------------------------------------------------------------------------

exec DELETE_ALL_TABLES;

--------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE battle (
    id_battle       INTEGER NOT NULL,
    location_name   VARCHAR2(25 CHAR) NOT NULL
);

ALTER TABLE battle ADD CONSTRAINT battle_pk PRIMARY KEY ( id_battle,
                                                          location_name );

CREATE TABLE class (
    name             VARCHAR2(15 CHAR) NOT NULL,
    type_of_armor    VARCHAR2(15 CHAR) DEFAULT 'Plate' NOT NULL,
    melee_or_range   VARCHAR2(15 CHAR) DEFAULT 'Both'
);

ALTER TABLE class
    ADD CHECK ( type_of_armor IN (
        'Cloth',
        'Leather',
        'Mail',
        'Plate'
    ) );

ALTER TABLE class
    ADD CHECK ( melee_or_range IN (
        'Both',
        'Melee',
        'Range'
    ) );

ALTER TABLE class ADD CONSTRAINT class_pk PRIMARY KEY ( name );

CREATE TABLE class_weapon (
    class_name    VARCHAR2(15 CHAR) NOT NULL,
    weapon_type   VARCHAR2(25 CHAR) NOT NULL
);

ALTER TABLE class_weapon ADD CONSTRAINT class_weapon_pk PRIMARY KEY ( class_name,
                                                                      weapon_type );

CREATE TABLE continent (
    name VARCHAR2(25 CHAR) NOT NULL
);

ALTER TABLE continent ADD CONSTRAINT continent_pk PRIMARY KEY ( name );

CREATE TABLE creature (
    name            VARCHAR2(25 CHAR) NOT NULL,
    class_name      VARCHAR2(15 CHAR) NOT NULL,
    mount_name      VARCHAR2(75 CHAR),
    race_name       VARCHAR2(15 CHAR) NOT NULL,
    location_name   VARCHAR2(25 CHAR) NOT NULL,
    weapon_type     VARCHAR2(25 CHAR) NOT NULL,
    gender          VARCHAR2(7 CHAR) DEFAULT 'Male' NOT NULL
);

ALTER TABLE creature
    ADD CHECK ( gender IN (
        'Female',
        'Male'
    ) );

ALTER TABLE creature ADD CONSTRAINT creature_pk PRIMARY KEY ( name );

CREATE TABLE healer (
    class_name       VARCHAR2(15 CHAR) NOT NULL,
    specialization   VARCHAR2(25 CHAR) DEFAULT 'Combined',
    popularity       INTEGER
);

ALTER TABLE healer
    ADD CHECK ( specialization IN (
        'Combined',
        'Many_targets',
        'Solo_target'
    ) );

ALTER TABLE healer ADD CHECK ( popularity BETWEEN 0 AND 100 );

ALTER TABLE healer ADD CONSTRAINT healer_pk PRIMARY KEY ( class_name );

CREATE TABLE location (
    name             VARCHAR2(25 CHAR) NOT NULL,
    type             VARCHAR2(15 CHAR) NOT NULL,
    continent_name   VARCHAR2(25 CHAR) NOT NULL
);

ALTER TABLE location
    ADD CHECK ( type IN (
        'Desert',
        'Forest',
        'Mountainous',
        'Plain',
        'Snowy',
        'Underground',
        'Water'
    ) );

ALTER TABLE location ADD CONSTRAINT location_pk PRIMARY KEY ( name );

CREATE TABLE mount (
    name     VARCHAR2(75 CHAR) NOT NULL,
    type     VARCHAR2(15 CHAR) DEFAULT 'Ground' NOT NULL,
    rarity   INTEGER
);

ALTER TABLE mount
    ADD CHECK ( type IN (
        'Flying',
        'Ground',
        'Water'
    ) );

ALTER TABLE mount ADD CHECK ( rarity BETWEEN 0 AND 100 );

ALTER TABLE mount ADD CONSTRAINT mount_pk PRIMARY KEY ( name );

CREATE TABLE mount_location (
    mount_name      VARCHAR2(75 CHAR) NOT NULL,
    location_name   VARCHAR2(25 CHAR) NOT NULL
);

ALTER TABLE mount_location ADD CONSTRAINT mount_location_pk PRIMARY KEY ( mount_name,
                                                                          location_name );

CREATE TABLE race (
    name       VARCHAR2(15 CHAR) NOT NULL,
    fraction   VARCHAR2(15 CHAR) NOT NULL
);

ALTER TABLE race
    ADD CHECK ( fraction IN (
        'Alliance',
        'Horde',
        'Neutral'
    ) );

ALTER TABLE race ADD CONSTRAINT race_pk PRIMARY KEY ( name );

CREATE TABLE race_class (
    class_name   VARCHAR2(15 CHAR) NOT NULL,
    race_name    VARCHAR2(15 CHAR) NOT NULL
);

ALTER TABLE race_class ADD CONSTRAINT race_class_pk PRIMARY KEY ( class_name,
                                                                  race_name );

CREATE TABLE tank (
    class_name   VARCHAR2(15 CHAR) NOT NULL,
    popularity   INTEGER
);

ALTER TABLE tank ADD CHECK ( popularity BETWEEN 0 AND 100 );

ALTER TABLE tank ADD CONSTRAINT tank_pk PRIMARY KEY ( class_name );

CREATE TABLE weapon (
    type             VARCHAR2(25 CHAR) NOT NULL,
    offhand_weapon   VARCHAR2(25 CHAR)
);

ALTER TABLE weapon
    ADD CHECK ( type IN (
        'Axe',
        'Bow',
        'Crossbow',
        'Dagger',
        'Fist_Weapon',
        'Hammer',
        'Mace',
        'Rifle',
        'Staff',
        'Sword',
        'Wand',
        'Warglaive'
    ) );

ALTER TABLE weapon
    ADD CHECK ( offhand_weapon IN (
        'Dagger',
        'Fist_Weapon',
        'Grimoire',
        'Shield',
        'Warglaive'
    ) );

ALTER TABLE weapon ADD CONSTRAINT weapon_pk PRIMARY KEY ( type );

ALTER TABLE battle
    ADD CONSTRAINT battle_location_fk FOREIGN KEY ( location_name )
        REFERENCES location ( name );

ALTER TABLE class_weapon
    ADD CONSTRAINT class_weapon_class_fk FOREIGN KEY ( class_name )
        REFERENCES class ( name );

ALTER TABLE class_weapon
    ADD CONSTRAINT class_weapon_weapon_fk FOREIGN KEY ( weapon_type )
        REFERENCES weapon ( type );

ALTER TABLE creature
    ADD CONSTRAINT creature_class_fk FOREIGN KEY ( class_name )
        REFERENCES class ( name );

ALTER TABLE creature
    ADD CONSTRAINT creature_location_fk FOREIGN KEY ( location_name )
        REFERENCES location ( name );

ALTER TABLE creature
    ADD CONSTRAINT creature_mount_fk FOREIGN KEY ( mount_name )
        REFERENCES mount ( name );

ALTER TABLE creature
    ADD CONSTRAINT creature_race_fk FOREIGN KEY ( race_name )
        REFERENCES race ( name );

ALTER TABLE creature
    ADD CONSTRAINT creature_weapon_fk FOREIGN KEY ( weapon_type )
        REFERENCES weapon ( type );

ALTER TABLE healer
    ADD CONSTRAINT healer_class_fk FOREIGN KEY ( class_name )
        REFERENCES class ( name );

ALTER TABLE location
    ADD CONSTRAINT location_continent_fk FOREIGN KEY ( continent_name )
        REFERENCES continent ( name );

ALTER TABLE mount_location
    ADD CONSTRAINT mount_location_location_fk FOREIGN KEY ( location_name )
        REFERENCES location ( name );

ALTER TABLE mount_location
    ADD CONSTRAINT mount_location_mount_fk FOREIGN KEY ( mount_name )
        REFERENCES mount ( name );

ALTER TABLE race_class
    ADD CONSTRAINT race_class_class_fk FOREIGN KEY ( class_name )
        REFERENCES class ( name );

ALTER TABLE race_class
    ADD CONSTRAINT race_class_race_fk FOREIGN KEY ( race_name )
        REFERENCES race ( name );

ALTER TABLE tank
    ADD CONSTRAINT tank_class_fk FOREIGN KEY ( class_name )
        REFERENCES class ( name );



-- Oracle SQL Developer Data Modeler Summary Report: 
-- 
-- CREATE TABLE                            13
-- CREATE INDEX                             0
-- ALTER TABLE                             40
-- CREATE VIEW                              0
-- ALTER VIEW                               0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           0
-- ALTER TRIGGER                            0
-- CREATE COLLECTION TYPE                   0
-- CREATE STRUCTURED TYPE                   0
-- CREATE STRUCTURED TYPE BODY              0
-- CREATE CLUSTER                           0
-- CREATE CONTEXT                           0
-- CREATE DATABASE                          0
-- CREATE DIMENSION                         0
-- CREATE DIRECTORY                         0
-- CREATE DISK GROUP                        0
-- CREATE ROLE                              0
-- CREATE ROLLBACK SEGMENT                  0
-- CREATE SEQUENCE                          0
-- CREATE MATERIALIZED VIEW                 0
-- CREATE MATERIALIZED VIEW LOG             0
-- CREATE SYNONYM                           0
-- CREATE TABLESPACE                        0
-- CREATE USER                              0
-- 
-- DROP TABLESPACE                          0
-- DROP DATABASE                            0
-- 
-- REDACTION POLICY                         0
-- 
-- ORDS DROP SCHEMA                         0
-- ORDS ENABLE SCHEMA                       0
-- ORDS ENABLE OBJECT                       0
-- 
-- ERRORS                                   0
-- WARNINGS                                 0