

drop table ipalias;
drop table ipaddr;

create table ipalias (
    dom_id          integer         not null,
    ipa_name        varchar(30)     not null,
    ip_addr         numeric(10)     not null,
    ipa_alias       boolean         not null,
    ipa_descr       varchar(200)    null,
    du_id_ins       integer         not null,
    date_ins        timestamp       not null,
    du_id_upd       integer         not null,
    date_upd        timestamp       not null,
    du_id_del       integer         null,
    date_del        timestamp       not null,
    constraint ipalias_pk primary key ( dom_id, ipa_name, date_del )
);

create table ipaddr (
    ip_addr         numeric(10)     not null,
    ipr_id          integer         not null,
    ip_hw_addr      varchar(30)     null,
    ip_temp         boolean         not null,
    ip_until        date            null,
    du_id_ins       integer         not null,
    date_ins        timestamp       not null,
    du_id_upd       integer         not null,
    date_upd        timestamp       not null,
    du_id_del       integer         null,
    date_del        timestamp       not null,
    constraint ipaddr_pk primary key ( ip_addr, date_del )
);

grant select, insert, update, delete on ipaddr to dns;
grant select, insert, update, delete on ipalias to dns;


insert into ipalias ( dom_id, ipa_name, ip_addr, ipa_alias, ipa_descr,
du_id_ins, date_ins, du_id_upd, date_upd, du_id_del, date_del
) values ( 1, 'wall', 167772161, false, 'wall', 2,
CURRENT_TIMESTAMP, 2, CURRENT_TIMESTAMP, null,
to_date( '1970010100', 'YYYYMMDDHH24' );

insert into ipalias ( dom_id, ipa_name, ip_addr, ipa_alias,
ipa_descr, du_id_ins, date_ins, du_id_upd, date_upd, du_id_del,
date_del ) values ( 1, 'wall', 167772161, false, 'wall', 2,
CURRENT_TIMESTAMP, 2, CURRENT_TIMESTAMP, null,
to_date( '1970010100', 'YYYYMMDDHH24' ) );

insert into ipaddr ( 
ip_addr, ipr_id, ip_hw_addr, 
ip_temp, ip_until, ip_descr, 
du_id_ins, date_ins, 
du_id_upd, date_upd, 
du_id_del, date_del ) values ( 
167772161, 19, null, 
false, null, 
2, CURRENT_TIMESTAMP, 
2, CURRENT_TIMESTAMP, 
null, to_date( '1970010100', 'YYYYMMDDHH24' ) )










drop table ipaddr;
drop table ipalias;
create table ipalias (
    dom_id          integer         not null,
    ipa_name        varchar(30)     not null,
    ipa_addr         numeric(10)     not null,
    ipa_descr       varchar(200)    null,
    du_id_ins       integer         not null,
    date_ins        timestamp       not null,
    du_id_upd       integer         not null,
    date_upd        timestamp       not null,
    du_id_del       integer         null,
    date_del        timestamp       not null,
    constraint ipalias_pk primary key ( dom_id, ipa_name, date_del )
);
grant select, insert, update, delete on ipalias to dns;

create table ipaddr (
    ipr_id          integer         not null,
    ip_hw_addr      varchar(30)     null,
    ip_temp         boolean         not null,
    ip_until        date            null
    constraint ipaddr_pk primary key ( dom_id, ipa_name, date_del ),
) inherits( ipalias );
grant select, insert, update, delete on ipaddr to dns;

    constraint ipaddr_uk unique ( ipa_addr, date_del )

insert into ipaddr values (
1, 'xxx',
123456,
'hello',
1, CURRENT_TIMESTAMP,
1, CURRENT_TIMESTAMP,
null, to_date( '1970.01.01 00:00:00', 'YYYY.MM.DD HH24:MI:SS' ),
1, null, false, null
);

insert into ipalias values (
1, 'xxx',
123456,
'hello',
1, CURRENT_TIMESTAMP,
1, CURRENT_TIMESTAMP,
null, to_date( '1970.01.01 00:00:00', 'YYYY.MM.DD HH24:MI:SS' )
);











drop table ipaddr;
drop table ipalias;
create table ipaddr (
    dom_id          integer         not null,
    ip_name         varchar(30)     not null,
    ip_addr         numeric(10)     not null,
	ip_alias        boolean         not null,
    ipr_id          integer         not null,
    ip_hw_addr      varchar(30)     null,
    ip_temp         boolean         not null,
    ip_until        date            null,
    ip_descr        varchar(200)    null,
    du_id_ins       integer         not null,
    date_ins        timestamp       not null,
    du_id_upd       integer         not null,
    date_upd        timestamp       not null,
    du_id_del       integer         null,
    date_del        timestamp       not null,
    constraint ipaddr_pk primary key ( dom_id, ip_name, date_del )
);
create unique index ipaddr_uk on ipaddr ( ip_addr, date_del )
    where ip_alias = false;
grant select, insert, update, delete on ipalias to dns;

    constraint ipaddr_pk primary key ( dom_id, ipa_name, date_del ),
grant select, insert, update, delete on ipaddr to dns;

    constraint ipaddr_uk unique ( ipa_addr, date_del )

insert into ipaddr values (
1, 'xxx',
123456,
'hello',
1, CURRENT_TIMESTAMP,
1, CURRENT_TIMESTAMP,
null, to_date( '1970.01.01 00:00:00', 'YYYY.MM.DD HH24:MI:SS' ),
1, null, false, null
);

insert into ipalias values (
1, 'xxx',
123456,
'hello',
1, CURRENT_TIMESTAMP,
1, CURRENT_TIMESTAMP,
null, to_date( '1970.01.01 00:00:00', 'YYYY.MM.DD HH24:MI:SS' )
);










