create table force_powers (
id integer generated by default as identity not null primary key,
structure varchar2(50),
name varchar2(100) not null
)
Table created.
insert into force_powers (structure, name) values ( '1', 'Universal' )
1 row(s) inserted.
insert into force_powers (structure, name) values ( '1.1', 'Telekinesis' )
1 row(s) inserted.
insert into force_powers (structure, name) values ( '1.1.1', 'Force Push' )
1 row(s) inserted.
insert into force_powers (structure, name) values ( '1.1.2', 'Force Pull' )
1 row(s) inserted.
insert into force_powers (structure, name) values ( '1.1.3', 'Force Jump' )
1 row(s) inserted.
insert into force_powers (structure, name) values ( '2', 'Light' )
1 row(s) inserted.
insert into force_powers (structure, name) values ( '2.1', 'Mind Trick' )
1 row(s) inserted.
insert into force_powers (structure, name) values ( '2.1.1', 'Force Persuation' )
1 row(s) inserted.
insert into force_powers (structure, name) values ( '3', 'Dark' )
1 row(s) inserted.
insert into force_powers (structure, name) values ( '3.1', 'Force wound' )
1 row(s) inserted.
insert into force_powers (structure, name) values ( '3.1.1', 'Force grip' )
1 row(s) inserted.
insert into force_powers (structure, name) values ( '3.1.2', 'Force choke' )
1 row(s) inserted.
select * from force_powers
ID | STRUCTURE | NAME | 1 | 1 | Universal | 2 | 1.1 | Telekinesis | 3 | 1.1.1 | Force Push | 4 | 1.1.2 | Force Pull | 5 | 1.1.3 | Force Jump | 6 | 2 | Light | 7 | 2.1 | Mind Trick | 8 | 2.1.1 | Force Persuation | 9 | 3 | Dark | 10 | 3.1 | Force wound | 11 | 3.1.1 | Force grip | 12 | 3.1.2 | Force choke |
---|
with leveled_powers as (
select
id,
structure,
name,
-- Get the value of each level from structure or 0
nvl(to_number(
regexp_substr(structure, '[0-9]+', 1, 1)
), 0) level1,
nvl(to_number(
regexp_substr(structure, '[0-9]+', 1, 2)
), 0) level2,
nvl(to_number(
regexp_substr(structure, '[0-9]+', 1, 3)
), 0) level3
from
force_powers
)
select
id,
structure,
name,
level1,
level2,
level3,
-- Use the level to determine depth of current element
case
when level3 > 0 then
3
when level2 > 0 then
2
when level1 > 0 then
1
else
0
end depth,
-- Concatenate the levels to a sortable string
lpad(level1, 3, '0')
|| lpad(level2, 3, '0')
|| lpad(level3, 3, '0') sort,
-- Get the id of the parent by structure
(select id
from force_powers p
where
p.structure =
case
when base.level3 > 0 then
to_char(base.level1)||'.'||to_char(base.level2)
when base.level2 > 0 then
to_char(base.level1)
end
) parent_id
from
leveled_powers base
ID | STRUCTURE | NAME | LEVEL1 | LEVEL2 | LEVEL3 | DEPTH | SORT | PARENT_ID | 1 | 1 | Universal | 1 | 0 | 0 | 1 | 001000000 | - | 2 | 1.1 | Telekinesis | 1 | 1 | 0 | 2 | 001001000 | 1 | 3 | 1.1.1 | Force Push | 1 | 1 | 1 | 3 | 001001001 | 2 | 4 | 1.1.2 | Force Pull | 1 | 1 | 2 | 3 | 001001002 | 2 | 5 | 1.1.3 | Force Jump | 1 | 1 | 3 | 3 | 001001003 | 2 | 6 | 2 | Light | 2 | 0 | 0 | 1 | 002000000 | - | 7 | 2.1 | Mind Trick | 2 | 1 | 0 | 2 | 002001000 | 6 | 8 | 2.1.1 | Force Persuation | 2 | 1 | 1 | 3 | 002001001 | 7 | 9 | 3 | Dark | 3 | 0 | 0 | 1 | 003000000 | - | 10 | 3.1 | Force wound | 3 | 1 | 0 | 2 | 003001000 | 9 | 11 | 3.1.1 | Force grip | 3 | 1 | 1 | 3 | 003001001 | 10 | 12 | 3.1.2 | Force choke | 3 | 1 | 2 | 3 | 003001002 | 10 |
---|
create or replace type t_numeric_structure force as object
(
-- for the levels are our most basic data
-- lets store them in the object
c_level1 number(3,0),
c_level2 number(3,0),
c_level3 number(3,0),
-- constructor to create structure from string
constructor function t_numeric_structure(
i_struct varchar2 )
return self as result,
-- This is just a little helper function
member function p$_position_for_level(
i_string varchar2,
i_level positiven ) return pls_integer,
-- Functions to get the different levels
member function level1 return pls_integer,
member function level2 return pls_integer,
member function level3 return pls_integer,
-- Function to get the structure string
member function structure return varchar2,
-- Function to get the sort value
member function sort return varchar2,
-- Function to get the depth
member function depth return pls_integer,
-- Function to get an instance of the parent structure
member function parent return t_numeric_structure
);
Type created.
create or replace type body t_numeric_structure as
constructor function t_numeric_structure(
i_struct varchar2 )
return self as result
as
begin
-- We do exactly the same here as in the
-- leveled_powers-with - just a bit more readable
self.c_level1 := p$_position_for_level(i_struct, 1);
self.c_level2 := p$_position_for_level(i_struct, 2);
self.c_level3 := p$_position_for_level(i_struct, 3);
return;
end;
member function p$_position_for_level(
i_string in varchar2,
i_level in positiven ) return pls_integer
as
begin
return
nvl(to_number(
regexp_substr(i_string, '[0-9]+', 1, i_level)
),0);
end;
member function level1 return pls_integer
as
begin
return c_level1;
end;
member function level2 return pls_integer
as
begin
return c_level2;
end;
member function level3 return pls_integer
as
begin
return c_level3;
end;
member function structure return varchar2
as
l_result varchar2(50);
begin
-- Because we use the atomic levels we have
-- to (re-)create the structure
if ( c_level1 > 0 ) then
l_result := to_char(c_level1);
end if;
if ( c_level2 > 0 ) then
l_result := l_result || '.' || to_char(c_level2);
end if;
if ( c_level3 > 0 ) then
l_result := l_result || '.' || to_char(c_level3);
end if;
return l_result;
end;
member function sort return varchar2
as
begin
-- Same logic as in the view
return lpad(c_level1, 3, '0')
|| lpad(c_level2, 3, '0')
|| lpad(c_level3, 3, '0');
end;
member function depth return pls_integer
as
begin
-- The same logic as in the view
-- with different flavour
if c_level3 > 0 then
return 3;
elsif c_level2 > 0 then
return 2;
elsif c_level1 > 0 then
return 1;
else
return 0;
end if;
end;
member function parent return t_numeric_structure
as
begin
-- We basically do the same here as in the subselect
-- for parent_id, but return a new instance of
-- the numeric_strucutre-type or NULL
if ( c_level3 > 0 ) then
return new t_numeric_structure(
to_char(c_level1)||'.'||to_char(c_level2));
elsif ( c_level2 > 0 ) then
return new t_numeric_structure(
to_char(c_level1));
else
return null;
end if;
end;
end;
Type created.
select
base.id,
base.struct.structure() structure,
base.name,
base.struct.level1() level1,
base.struct.level2() level3,
base.struct.level3() level3,
base.struct.depth() depth,
base.struct.sort() sort,
(select id
from force_powers p
where p.structure =
base.struct.parent().structure()
) parent_id
from
(
select
id,
name,
t_numeric_structure(structure) struct
from
force_powers
) base
ID | STRUCTURE | NAME | LEVEL1 | LEVEL3 | LEVEL3 | DEPTH | SORT | PARENT_ID | 1 | 1 | Universal | 1 | 0 | 0 | 1 | 001000000 | - | 2 | 1.1 | Telekinesis | 1 | 1 | 0 | 2 | 001001000 | 1 | 3 | 1.1.1 | Force Push | 1 | 1 | 1 | 3 | 001001001 | 2 | 4 | 1.1.2 | Force Pull | 1 | 1 | 2 | 3 | 001001002 | 2 | 5 | 1.1.3 | Force Jump | 1 | 1 | 3 | 3 | 001001003 | 2 | 6 | 2 | Light | 2 | 0 | 0 | 1 | 002000000 | - | 7 | 2.1 | Mind Trick | 2 | 1 | 0 | 2 | 002001000 | 6 | 8 | 2.1.1 | Force Persuation | 2 | 1 | 1 | 3 | 002001001 | 7 | 9 | 3 | Dark | 3 | 0 | 0 | 1 | 003000000 | - | 10 | 3.1 | Force wound | 3 | 1 | 0 | 2 | 003001000 | 9 | 11 | 3.1.1 | Force grip | 3 | 1 | 1 | 3 | 003001001 | 10 | 12 | 3.1.2 | Force choke | 3 | 1 | 2 | 3 | 003001002 | 10 |
---|