@echo Set view NODE⠒V up;

create or replace view NODE⠒V as
      select N.NAME         as NODE⠒NAME,
             -- N.QUANTITY,
             T.NAME         as NODE_TYPE⠒NAME,
             M.MAP⠒NAME,
             M.MAP⠒CODE,
             M.REGION⠒NAME,
             M.REGION⠒COMPLETION_PERCENTATGE,
             A.CENTRICITY⠒NAME,
             A.DIRECTION⠒CODE,
             N.DESCRIPTION as NODE⠒DESCRIPTION,
             T.DESCRIPTION as NODE_TYPE⠒DESCRIPTION,
             M.MAP⠒DESCRIPTION,
             M.REGION⠒DESCRIPTION,
             A.AREA⠒DESCRIPTION,
             A.CENTRICITY⠒DESCRIPTION,
             A.DIRECTION⠒DESCRIPTION,
             N.ID,
             N.NODE_TYPE⠒ID,
             N.MAP⠒ID,
             M.REGION⠒ID,
             N.AREA⠒ID,
             A.CENTRICITY⠒ID,
             A.DIRECTION⠒ID
        from NODE N
  inner join NODE_TYPE T
          on N.NODE_TYPE⠒ID = T.ID
  inner join MAP⠒V M
          on N.MAP⠒ID = M.ID
  inner join AREA⠒V A
          on N.AREA⠒ID = A.ID;


create or replace rule NODE⠒R_I as
  on insert
  to NODE⠒V
  do instead
    insert into NODE (MAP⠒ID,
                      AREA⠒ID,
                      NODE_TYPE⠒ID,
                      NAME,
                      -- QUANTITY,
                      DESCRIPTION)
        values (/* MAP⠒I */             case
                                           -- ID given
                                           when new.MAP⠒ID is not null then new.MAP⠒ID
                                           -- name or code and region given
                                           when (   new.MAP⠒CODE is not null
                                                 or new.MAP⠒NAME is not null)
                                            and (   new.REGION⠒ID is not null
                                                 or new.REGION⠒NAME is not null) then (select ID
                                                                                         from MAP⠒V
                                                                                        where (   MAP⠒CODE = new.MAP⠒CODE
                                                                                               or MAP⠒NAME = new.MAP⠒NAME)
                                                                                          and (   REGION⠒ID = new.REGION⠒ID
                                                                                               or REGION⠒NAME = new.REGION⠒NAME))
                                           else null
                                        end,
                /* AREA⠒ID */           case
                                            -- ID given
                                            when new.AREA⠒ID is not null then new.AREA⠒ID
                                            -- name given
                                           when (   new.DIRECTION⠒CODE is not null
                                                 or new.DIRECTION⠒ID is not null)
                                            and (   new.CENTRICITY⠒ID is not null
                                                 or new.CENTRICITY⠒NAME is not null) then (select ID
                                                                                             from AREA⠒V
                                                                                            where (   DIRECTION⠒CODE = new.DIRECTION⠒CODE
                                                                                                   or DIRECTION⠒ID = new.DIRECTION⠒ID)
                                                                                              and (   CENTRICITY⠒ID = new.CENTRICITY⠒ID
                                                                                                   or CENTRICITY⠒NAME = new.CENTRICITY⠒NAME))
                                            else null
                                        end,
                /* NODE_TYPE⠒ID */      case
                                            -- ID given
                                            when new.NODE_TYPE⠒ID is not null then new.NODE_TYPE⠒ID
                                            -- name given
                                            when new.NODE_TYPE⠒NAME is not null then (select ID
                                                                                        from NODE_TYPE
                                                                                       where NAME = new.NODE_TYPE⠒NAME)
                                            else null
                                        end,
                /* NAME */              new.NODE⠒NAME,
                -- /* QUANTITY */          new.QUANTITY,
                /* DESCRIPTION */       new.NODE⠒DESCRIPTION);

comment on rule NODE⠒R_I on NODE⠒V is
  'We do not support filling the joined tables, thus they have to be filled already; i.e. no recursion';

create or replace trigger NODE⠒V⠒TR_B_DU_S
  before delete or update
  on NODE⠒V
  for each statement
  execute function REJECT_OPERATION();


comment on view NODE⠒V is
  '
$Header: svn+ssh://thiemo__sourceforge/p/snowrunner/code/trunk/code/postgresql/views/NODE%E2%A0%92V.pg_sql 2 2024-02-24 23:50:08Z thiemo $';


commit;