Trouble in The Old Republic – A Story of Relations and Relationships

In order to actually run the examples, you have a couple of options:

• If you have a local Oracle database available, you can of course run the below setup-script in any schema you want on that database.

• If you have docker installed, you can very easily create an oracle-xe container of the free-to-use Oracle 21c XE version

docker run -d -p 1521:1521 -e ORACLE_PASSWORD=<your password> --name test-impdb gvenzl/oracle-xe

• Gerald Venzl also put the images on Github

docker pull ghcr.io/gvenzl/oracle-xe:slim

• You can also head straight to Oracle Live-SQL, create a free account and use an online Oracle database to try out the example

If you were going with a new database, you first need to create a user and schema you can use to work with. Working with the SYS user is dangerous and strongly not recommended.

You can use the following script (run as sys) to create a new user GENSO

-- run once as SYS
create user genso
  identified by genso
  default tablespace users
  quota unlimited on users;

grant
  create session,
  create sequence,
  create procedure,
  create type,
  create table,
  create view,
  create synonym,
  create trigger
to genso;

grant alter session to genso;

The following script will set up all the objects you need to start with the Examples.

create table planets (
  id integer generated by default as identity primary key,
  name varchar2(100) not null unique
);

insert into planets ( id, name ) values ( 1, 'Endor');
insert into planets ( id, name ) values ( 2, 'Coruscant');
insert into planets ( id, name ) values ( 3, 'Tatooine');
insert into planets ( id, name ) values ( 4, 'Kashyyk');

create table behaviours (
  id integer generated by default as identity primary key,
  name varchar2(100) unique
);

insert into behaviours (id, name) values (1, 'aggressive');
insert into behaviours (id, name) values (2, 'peaceful');
insert into behaviours (id, name) values (3, 'passive');
insert into behaviours (id, name) values (4, 'nonviolent');

create table sentience_classes (
  id integer generated by default as identity primary key,
  name varchar2(100) unique
);

insert into sentience_classes (id, name) values (1, 'sentient');
insert into sentience_classes (id, name) values (2, 'semi-sentient');
insert into sentience_classes (id, name) values (3, 'non-sentient');

create table species (
  id integer generated by default as identity primary key,
  name varchar2(2000) not null unique,
  data clob
);

insert into species ( id, name, data ) values ( 1, 'Ewok', '{
    "uuid" : "EB7858D7C791011EE053020011AC2D3E",
    "name" : "Ewok",
    "behaviour" : "peaceful",
    "sentience" : "sentient",
    "planets" :
    [
      "Endor"
    ]
  }' );
insert into species ( id, name, data ) values ( 2, 'Human', '{
    "uuid" : "EB7858D7C792011EE053020011AC2D3E",
    "name" : "Human",
    "behaviour" : "aggressive",
    "sentience" : "sentient",
    "planets" :
    [
      "Coruscant",
      "Endor",
      "Tatooine"
    ]
  }' );

select * from species;

create table species_relational (
  id integer generated by default as identity primary key,
  uuid varchar2(36) default on null sys_guid() not null unique,
  name varchar2(2000) not null unique,
  behaviour_id integer
    references behaviours ( id ) on delete set null,
  sentience_id integer
    references sentience_classes ( id ) on delete set null,
  created timestamp default on null systimestamp,
  last_modified timestamp
);


insert into species_relational ( id, name, behaviour_id, sentience_id ) values ( 1, 'Ewok', 2, 1 );
insert into species_relational ( id, name, behaviour_id, sentience_id ) values ( 2, 'Human', 1, 1 );
insert into species_relational ( id, name, behaviour_id, sentience_id ) values ( 3, 'Wookie', 2, 1 );

select * from species_relational;

create table species_occurences (
  species_id integer not null,
  planet_id integer not null,
  first_documented timestamp with local time zone default on null current_timestamp,
  primary key (species_id, planet_id)
);

insert into species_occurences ( species_id, planet_id ) values (1, 1);
insert into species_occurences ( species_id, planet_id ) values (2, 2);
insert into species_occurences ( species_id, planet_id ) values (2, 3);
insert into species_occurences ( species_id, planet_id ) values (2, 1);
insert into species_occurences ( species_id, planet_id ) values (3, 4);

commit;