176 lines
5.3 KiB
SQL
176 lines
5.3 KiB
SQL
|
|
-- ----------------------------------------
|
|
-- THIS FILE HAS BEEN GENERATED
|
|
-- DO NOT EDIT MANUALLY
|
|
-- ----------------------------------------
|
|
|
|
|
|
CREATE TABLE account_t (
|
|
id serial not null primary key
|
|
,description varchar(128) not null unique
|
|
);
|
|
|
|
GRANT SELECT, INSERT, UPDATE ON account_t TO hv2;
|
|
GRANT SELECT, UPDATE ON account_t_id_seq TO hv2;
|
|
|
|
CREATE TABLE tenant_t (
|
|
id serial not null primary key
|
|
,salutation varchar(128)
|
|
,firstname varchar(128)
|
|
,lastname varchar(128)
|
|
,address1 varchar(128)
|
|
,address2 varchar(128)
|
|
,address3 varchar(128)
|
|
,zip varchar(10)
|
|
,city varchar(128)
|
|
,phone1 varchar(64)
|
|
,phone2 varchar(64)
|
|
,iban varchar(64)
|
|
,account integer not null references account_t (id) unique
|
|
,unique(firstname, lastname)
|
|
);
|
|
|
|
GRANT SELECT, INSERT, UPDATE ON tenant_t TO hv2;
|
|
GRANT SELECT, UPDATE ON tenant_t_id_seq TO hv2;
|
|
|
|
CREATE TABLE premise_t (
|
|
id serial not null primary key
|
|
,description varchar(128) unique
|
|
,street varchar(128) not null
|
|
,zip varchar(10) not null
|
|
,city varchar(128) not null
|
|
,account integer not null references account_t (id) unique
|
|
);
|
|
|
|
GRANT SELECT, INSERT, UPDATE ON premise_t TO hv2;
|
|
GRANT SELECT, UPDATE ON premise_t_id_seq TO hv2;
|
|
|
|
CREATE TABLE flat_t (
|
|
id serial not null primary key
|
|
,description varchar(128)
|
|
,premise integer references premise_t (id)
|
|
,area numeric(10,2) not null
|
|
,flat_no integer
|
|
,unique(description, premise)
|
|
);
|
|
|
|
GRANT SELECT, INSERT, UPDATE ON flat_t TO hv2;
|
|
GRANT SELECT, UPDATE ON flat_t_id_seq TO hv2;
|
|
|
|
CREATE TABLE overhead_advance_t (
|
|
id serial not null primary key
|
|
,description varchar(128) unique
|
|
,amount numeric(10,4) not null
|
|
,startdate date
|
|
,enddate date
|
|
);
|
|
|
|
GRANT SELECT, INSERT, UPDATE ON overhead_advance_t TO hv2;
|
|
GRANT SELECT, UPDATE ON overhead_advance_t_id_seq TO hv2;
|
|
|
|
CREATE TABLE overhead_advance_flat_mapping_t (
|
|
id serial not null primary key
|
|
,overhead_advance integer not null references overhead_advance_t (id)
|
|
,flat integer not null references flat_t (id)
|
|
);
|
|
|
|
GRANT SELECT, INSERT ON overhead_advance_flat_mapping_t TO hv2;
|
|
GRANT SELECT, UPDATE ON overhead_advance_flat_mapping_t_id_seq TO hv2;
|
|
|
|
CREATE TABLE parking_t (
|
|
id serial not null primary key
|
|
,description varchar(128)
|
|
,premise integer references premise_t (id)
|
|
,unique(description, premise)
|
|
);
|
|
|
|
GRANT SELECT, INSERT, UPDATE ON parking_t TO hv2;
|
|
GRANT SELECT, UPDATE ON parking_t_id_seq TO hv2;
|
|
|
|
CREATE TABLE commercial_premise_t (
|
|
id serial not null primary key
|
|
,description varchar(128)
|
|
,premise integer references premise_t (id)
|
|
,area numeric(10,2)
|
|
,unique(description, premise)
|
|
);
|
|
|
|
GRANT SELECT, INSERT, UPDATE ON commercial_premise_t TO hv2;
|
|
GRANT SELECT, UPDATE ON commercial_premise_t_id_seq TO hv2;
|
|
|
|
CREATE TABLE tenancy_t (
|
|
id serial not null primary key
|
|
,description varchar(128) unique
|
|
,tenant integer not null references tenant_t (id)
|
|
,flat integer references flat_t (id)
|
|
,parking integer references parking_t (id)
|
|
,commercial_premise integer references commercial_premise_t (id)
|
|
,startdate date not null
|
|
,enddate date
|
|
,check ((flat is not null and parking is null and commercial_premise is null) or (flat is null and parking is not null and commercial_premise is null) or (flat is null and parking is null and commercial_premise is not null))
|
|
,unique(flat, parking, commercial_premise, startdate)
|
|
);
|
|
|
|
GRANT SELECT, INSERT, UPDATE ON tenancy_t TO hv2;
|
|
GRANT SELECT, UPDATE ON tenancy_t_id_seq TO hv2;
|
|
|
|
CREATE TABLE fee_t (
|
|
id serial not null primary key
|
|
,description varchar(128) unique
|
|
,amount numeric(10,2) not null
|
|
,fee_type varchar(10) not null
|
|
,startdate date
|
|
,enddate date
|
|
,check (fee_type = 'per_area' or fee_type = 'total')
|
|
);
|
|
|
|
GRANT SELECT, INSERT, UPDATE ON fee_t TO hv2;
|
|
GRANT SELECT, UPDATE ON fee_t_id_seq TO hv2;
|
|
|
|
CREATE TABLE tenancy_fee_mapping_t (
|
|
id serial not null primary key
|
|
,tenancy integer not null references tenancy_t (id)
|
|
,fee integer not null references fee_t (id)
|
|
);
|
|
|
|
GRANT SELECT, INSERT ON tenancy_fee_mapping_t TO hv2;
|
|
GRANT SELECT, UPDATE ON tenancy_fee_mapping_t_id_seq TO hv2;
|
|
|
|
CREATE TABLE account_entry_category_t (
|
|
id serial not null primary key
|
|
,description varchar(128) not null unique
|
|
,overhead_relevant boolean not null default true
|
|
);
|
|
|
|
GRANT SELECT, INSERT ON account_entry_category_t TO hv2;
|
|
GRANT SELECT, UPDATE ON account_entry_category_t_id_seq TO hv2;
|
|
|
|
CREATE TABLE account_entry_t (
|
|
id serial not null primary key
|
|
,description varchar(1024) not null
|
|
,account integer not null references account_t (id)
|
|
,created_at timestamp not null default now()
|
|
,fiscal_year integer not null
|
|
,amount numeric(10,2) not null
|
|
,document_no integer unique
|
|
,account_entry_category integer not null references account_entry_category_t (id)
|
|
,unique(description, account, created_at)
|
|
);
|
|
|
|
GRANT SELECT, INSERT ON account_entry_t TO hv2;
|
|
GRANT SELECT, UPDATE ON account_entry_t_id_seq TO hv2;
|
|
|
|
CREATE TABLE note_t (
|
|
id serial not null primary key
|
|
,created_at timestamp not null default now()
|
|
,tenant integer not null references tenant_t (id)
|
|
,note varchar(4096) not null
|
|
);
|
|
|
|
GRANT SELECT, INSERT ON note_t TO hv2;
|
|
GRANT SELECT, UPDATE ON note_t_id_seq TO hv2;
|
|
|
|
|
|
|
|
|