/* $Id$ */ BEGIN; /* -------------------------------------------------------- Structure of table "users" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS users_id_seq; CREATE SEQUENCE users_id_seq; DROP TABLE IF EXISTS users CASCADE; CREATE TABLE users ( id integer DEFAULT nextval('users_id_seq'::text) NOT NULL, login varchar(32) DEFAULT '' NOT NULL, firstname varchar(64) DEFAULT '' NOT NULL, lastname varchar(64) DEFAULT '' NOT NULL, issuer varchar(100) DEFAULT NULL, email varchar(255) DEFAULT '' NOT NULL, phone varchar(32) DEFAULT NULL, position varchar(255) DEFAULT '' NOT NULL, rights text DEFAULT '' NOT NULL, hosts varchar(255) DEFAULT '' NOT NULL, trustedhosts varchar(255) DEFAULT NULL, passwd varchar(255) DEFAULT '' NOT NULL, netpasswd varchar(255) DEFAULT NULL, passwdforcechange smallint NOT NULL DEFAULT 0, ntype smallint DEFAULT NULL, lastlogindate bigint DEFAULT 0 NOT NULL, lastloginip varchar(40) DEFAULT '' NOT NULL, failedlogindate bigint DEFAULT 0 NOT NULL, failedloginip varchar(40) DEFAULT '' NOT NULL, deleted smallint DEFAULT 0 NOT NULL, passwdexpiration bigint DEFAULT 0 NOT NULL, passwdlastchange bigint DEFAULT 0 NOT NULL, access smallint DEFAULT 1 NOT NULL, accessfrom bigint DEFAULT 0 NOT NULL, accessto bigint DEFAULT 0 NOT NULL, settings text NOT NULL DEFAULT '', persistentsettings text NOT NULL DEFAULT '', twofactorauth smallint NOT NULL DEFAULT 0, twofactorauthsecretkey varchar(255) DEFAULT NULL, PRIMARY KEY (id), UNIQUE (login) ); /* -------------------------------------------------------- Structure of table "twofactorauthcodehistory" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS twofactorauthcodehistory_id_seq; CREATE SEQUENCE twofactorauthcodehistory_id_seq; DROP TABLE IF EXISTS twofactorauthcodehistory CASCADE; CREATE TABLE twofactorauthcodehistory ( id integer DEFAULT nextval('twofactorauthcodehistory_id_seq'::text) NOT NULL, userid integer NOT NULL CONSTRAINT twofactorauthcodehistory_userid_fkey REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE, authcode varchar(10) NOT NULL, uts bigint NOT NULL, ipaddr bigint DEFAULT NULL, success smallint NOT NULL DEFAULT 0, PRIMARY KEY (id) ); /* -------------------------------------------------------- Structure of table "twofactorauthtrusteddevices" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS twofactorauthtrusteddevices_id_seq; CREATE SEQUENCE twofactorauthtrusteddevices_id_seq; DROP TABLE IF EXISTS twofactorauthtrusteddevices CASCADE; CREATE TABLE twofactorauthtrusteddevices ( id integer DEFAULT nextval('twofactorauthtrusteddevices_id_seq'::text), userid integer NOT NULL CONSTRAINT twofactorauthtrusteddevices_userid_fkey REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE, cookiename varchar(64) NOT NULL, useragent varchar(256) NOT NULL, ipaddr bigint DEFAULT NULL, expires bigint NOT NULL, PRIMARY KEY (id) ); /* --------------------------------------------------- Structure of table "countries" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS countries_id_seq; CREATE SEQUENCE countries_id_seq; DROP TABLE IF EXISTS countries CASCADE; CREATE TABLE countries ( id integer DEFAULT nextval('countries_id_seq'::text) NOT NULL, name varchar(255) NOT NULL DEFAULT '', ccode varchar(5), PRIMARY KEY (id), UNIQUE (name) ); /* -------------------------------------------------------- Structure of table "location_states" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS location_states_id_seq; CREATE SEQUENCE location_states_id_seq; DROP TABLE IF EXISTS location_states CASCADE; CREATE TABLE location_states ( id integer DEFAULT nextval('location_states_id_seq'::text) NOT NULL, ident varchar(8) NOT NULL, -- TERYT: WOJ name varchar(64) NOT NULL, -- TERYT: NAZWA PRIMARY KEY (id), UNIQUE (name) ); /* -------------------------------------------------------- Structure of table "location_districts" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS location_districts_id_seq; CREATE SEQUENCE location_districts_id_seq; DROP TABLE IF EXISTS location_districts CASCADE; CREATE TABLE location_districts ( id integer DEFAULT nextval('location_districts_id_seq'::text) NOT NULL, name varchar(64) NOT NULL, --TERYT: NAZWA ident varchar(8) NOT NULL, --TERYT: POW stateid integer NOT NULL --TERYT: WOJ REFERENCES location_states (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id), UNIQUE (stateid, name) ); /* -------------------------------------------------------- Structure of table "location_boroughs" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS location_boroughs_id_seq; CREATE SEQUENCE location_boroughs_id_seq; DROP TABLE IF EXISTS location_boroughs CASCADE; CREATE TABLE location_boroughs ( id integer DEFAULT nextval('location_boroughs_id_seq'::text) NOT NULL, name varchar(64) NOT NULL, -- TERYT: NAZWA ident varchar(8) NOT NULL, -- TERYT: GMI districtid integer NOT NULL REFERENCES location_districts (id) ON DELETE CASCADE ON UPDATE CASCADE, type smallint NOT NULL, -- TERYT: RODZ PRIMARY KEY (id), UNIQUE (districtid, name, type) ); /* -------------------------------------------------------- Structure of table "location_city_types" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS location_city_types_id_seq; CREATE SEQUENCE location_city_types_id_seq; DROP TABLE IF EXISTS location_city_types CASCADE; CREATE TABLE location_city_types ( id integer DEFAULT nextval('location_city_types_id_seq'::text) NOT NULL, ident varchar(8) NOT NULL, name varchar(64) NOT NULL, PRIMARY KEY (id), CONSTRAINT location_city_types_name_ukey UNIQUE (name) ); /* -------------------------------------------------------- Structure of table "location_cities" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS location_cities_id_seq; CREATE SEQUENCE location_cities_id_seq; DROP TABLE IF EXISTS location_cities CASCADE; CREATE TABLE location_cities ( id integer DEFAULT nextval('location_cities_id_seq'::text) NOT NULL, ident varchar(8) NOT NULL, -- TERYT: SYM / SYMPOD name varchar(64) NOT NULL, -- TERYT: NAZWA cityid integer DEFAULT NULL, type integer DEFAULT NULL CONSTRAINT location_cities_type_fkey REFERENCES location_city_types (id) ON DELETE CASCADE ON UPDATE CASCADE, boroughid integer DEFAULT NULL REFERENCES location_boroughs (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id) ); CREATE INDEX location_cities_cityid ON location_cities (cityid); CREATE INDEX location_cities_boroughid ON location_cities (boroughid, name); /* -------------------------------------------------------- Structure of table "location_street_types" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS location_street_types_id_seq; CREATE SEQUENCE location_street_types_id_seq; DROP TABLE IF EXISTS location_street_types CASCADE; CREATE TABLE location_street_types ( id integer DEFAULT nextval('location_street_types_id_seq'::text) NOT NULL, name varchar(8) NOT NULL, -- TERYT: CECHA PRIMARY KEY (id) ); /* -------------------------------------------------------- Structure of table "location_streets" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS location_streets_id_seq; CREATE SEQUENCE location_streets_id_seq; DROP TABLE IF EXISTS location_streets CASCADE; CREATE TABLE location_streets ( id integer DEFAULT nextval('location_streets_id_seq'::text) NOT NULL, name varchar(128) NOT NULL, -- TERYT: NAZWA_1 name2 varchar(128) DEFAULT NULL, -- TERYT: NAZWA_2 ident varchar(8) NOT NULL, -- TERYT: SYM_UL typeid integer DEFAULT NULL REFERENCES location_street_types (id) ON DELETE SET NULL ON UPDATE CASCADE, cityid integer NOT NULL REFERENCES location_cities (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id), UNIQUE (cityid, name, ident) ); /* -------------------------------------------------------- Structure of table "location_buildings" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS location_buildings_id_seq; CREATE SEQUENCE location_buildings_id_seq; DROP TABLE IF EXISTS location_buildings CASCADE; CREATE TABLE location_buildings ( id integer DEFAULT nextval('location_buildings_id_seq'::text) NOT NULL, city_id integer NOT NULL CONSTRAINT location_cities_city_id_fkey REFERENCES location_cities (id) ON DELETE CASCADE ON UPDATE CASCADE, street_id integer NULL CONSTRAINT location_cities_street_id_fkey REFERENCES location_streets (id) ON DELETE CASCADE ON UPDATE CASCADE, building_num varchar(20) NULL, zip varchar(10) DEFAULT NULL, latitude numeric(10,6) NULL, longitude numeric(10,6) NULL, updated smallint DEFAULT 0, extid varchar(128) DEFAULT NULL, PRIMARY KEY (id), CONSTRAINT location_buildings_extid_ukey UNIQUE (extid) ); DROP INDEX IF EXISTS location_cityid_index; CREATE INDEX location_cityid_index ON location_buildings (city_id); /* --------------------------------------------------- Structure of table "addresses" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS addresses_id_seq; CREATE SEQUENCE addresses_id_seq; DROP TABLE IF EXISTS addresses CASCADE; CREATE TABLE addresses ( id integer DEFAULT nextval('addresses_id_seq'::text) NOT NULL, name text NULL, state varchar(64) NULL, state_id integer REFERENCES location_states (id) ON DELETE SET NULL ON UPDATE CASCADE, city varchar(100) NULL, city_id integer REFERENCES location_cities (id) ON DELETE SET NULL ON UPDATE CASCADE, postoffice varchar(32) DEFAULT NULL, street varchar(255) NULL, street_id integer REFERENCES location_streets (id) ON DELETE SET NULL ON UPDATE CASCADE, zip varchar(10) NULL, country_id integer REFERENCES countries (id) ON DELETE SET NULL ON UPDATE CASCADE, house varchar(20) NULL, flat varchar(20) NULL, PRIMARY KEY (id) ); /* --------------------------------------------------- Structure of table "divisions" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS divisions_id_seq; CREATE SEQUENCE divisions_id_seq; DROP TABLE IF EXISTS divisions CASCADE; CREATE TABLE divisions ( id integer NOT NULL DEFAULT nextval('divisions_id_seq'::text), shortname varchar(255) NOT NULL DEFAULT '', name text NOT NULL DEFAULT '', label varchar(100) DEFAULT NULL, firstname varchar(128) DEFAULT NULL, lastname varchar(128) DEFAULT NULL, birthdate integer DEFAULT NULL, ten varchar(50) NOT NULL DEFAULT '', regon varchar(255) NOT NULL DEFAULT '', rbe varchar(255) NOT NULL DEFAULT '', rbename varchar(255) NOT NULL DEFAULT '', telecomnumber varchar(255) NOT NULL DEFAULT '', bank varchar(100) DEFAULT NULL, account varchar(48) NOT NULL DEFAULT '', email varchar(255) DEFAULT NULL, phone varchar(255) DEFAULT NULL, servicephone varchar(40) DEFAULT NULL, inv_header text NOT NULL DEFAULT '', inv_footer text NOT NULL DEFAULT '', inv_author text NOT NULL DEFAULT '', inv_cplace text NOT NULL DEFAULT '', inv_paytime smallint DEFAULT NULL, inv_paytype smallint DEFAULT NULL, description text NOT NULL DEFAULT '', status smallint NOT NULL DEFAULT 0, tax_office_code varchar(8) DEFAULT NULL, url text DEFAULT NULL, userpanel_url text DEFAULT NULL, address_id integer DEFAULT NULL REFERENCES addresses (id) ON DELETE SET NULL ON UPDATE CASCADE, office_address_id integer DEFAULT NULL CONSTRAINT divisions_office_address_id_fkey REFERENCES addresses (id) ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY (id), CONSTRAINT divisions_shortname_label_ukey UNIQUE (shortname, label), UNIQUE (shortname) ); /* -------------------------------------------------------- Structure of table "serviceproviders" (serviceproviders) -------------------------------------------------------- */ CREATE SEQUENCE serviceproviders_id_seq; DROP TABLE IF EXISTS serviceproviders; CREATE TABLE serviceproviders ( id integer DEFAULT nextval('serviceproviders_id_seq') NOT NULL, name varchar(64) NOT NULL, PRIMARY KEY (id) ); /* -------------------------------------------------------- Structure of table "customers" (customers) -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS customers_id_seq; CREATE SEQUENCE customers_id_seq; DROP TABLE IF EXISTS customers CASCADE; CREATE TABLE customers ( id integer DEFAULT nextval('customers_id_seq'::text) NOT NULL, lastname varchar(128) DEFAULT '' NOT NULL, name varchar(128) DEFAULT '' NOT NULL, altname varchar(128) DEFAULT NULL, status smallint DEFAULT 0 NOT NULL, type smallint DEFAULT 0 NOT NULL, ten varchar(50) DEFAULT '' NOT NULL, ssn varchar(50) DEFAULT '' NOT NULL, regon varchar(255) DEFAULT '' NOT NULL, rbe varchar(255) DEFAULT '' NOT NULL, -- EDG/KRS rbename varchar(255) DEFAULT '' NOT NULL, ict smallint DEFAULT 0 NOT NULL, -- typ dokumentu tożsamości icn varchar(255) DEFAULT '' NOT NULL, -- numer dokumentu tożsamości icexpires bigint DEFAULT NULL, -- ważność dokumentu tożsamości info text DEFAULT '' NOT NULL, notes text DEFAULT '' NOT NULL, creationdate bigint DEFAULT 0 NOT NULL, moddate bigint DEFAULT 0 NOT NULL, creatorid integer DEFAULT NULL CONSTRAINT customers_creatorid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, modid integer DEFAULT NULL CONSTRAINT customers_modid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, deleted smallint DEFAULT 0 NOT NULL, message text DEFAULT '' NOT NULL, pin varchar(255) DEFAULT 0 NOT NULL, pinlastchange bigint DEFAULT 0 NOT NULL, cutoffstop bigint DEFAULT 0 NOT NULL, divisionid integer DEFAULT NULL CONSTRAINT customers_divisionid_fkey REFERENCES divisions (id) ON DELETE SET NULL ON UPDATE CASCADE, paytime smallint DEFAULT -1 NOT NULL, paytype smallint DEFAULT NULL, documentmemo text DEFAULT NULL, flags smallint NOT NULL DEFAULT 0, karma smallint NOT NULL DEFAULT 0, origin smallint DEFAULT NULL, PRIMARY KEY (id) ); CREATE INDEX customers_lastname_idx ON customers (lastname, name); /* -------------------------------------------------------- Structure of table "customerconsents" (customerconsents) -------------------------------------------------------- */ DROP TABLE IF EXISTS customerconsents; CREATE TABLE customerconsents ( customerid integer NOT NULL CONSTRAINT customerconsents_customerid_fkey REFERENCES customers (id) ON DELETE CASCADE ON UPDATE CASCADE, cdate bigint DEFAULT 0 NOT NULL, type smallint NOT NULL, CONSTRAINT customerconsents_ukey UNIQUE (customerid, type) ); CREATE INDEX customerconsents_cdate_idx ON customerconsents (cdate); CREATE INDEX customerconsents_type_idx ON customerconsents (type); /* -------------------------------------------------------- Structure of table "customerextids" (customerextids) -------------------------------------------------------- */ DROP TABLE IF EXISTS customerextids CASCADE; CREATE TABLE customerextids ( customerid integer NOT NULL CONSTRAINT customerextids_customerid_fkey REFERENCES customers (id) ON DELETE CASCADE ON UPDATE CASCADE, extid varchar(64) NOT NULL, serviceproviderid integer DEFAULT NULL CONSTRAINT customerextids_serviceproviderid_fkey REFERENCES serviceproviders (id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT customerextids_customerid_extid_serviceproviderid_ukey UNIQUE (customerid, extid, serviceproviderid) ); /* -------------------------------------------------------- Structure of table "customernotes" (customernotes) -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS customernotes_id_seq; CREATE SEQUENCE customernotes_id_seq; DROP TABLE IF EXISTS customernotes CASCADE; CREATE TABLE customernotes ( id integer DEFAULT nextval('customernotes_id_seq'::text) NOT NULL, userid integer DEFAULT NULL CONSTRAINT customernotes_userid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, customerid integer NOT NULL CONSTRAINT customernotes_customerid_fkey REFERENCES customers (id) ON DELETE CASCADE ON UPDATE CASCADE, dt bigint NOT NULL, message text NOT NULL, moddate bigint DEFAULT NULL, moduserid integer DEFAULT NULL CONSTRAINT customernotes_moduserid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE SET NULL, PRIMARY KEY (id) ); /* -------------------------------------------------------- Structure of table "customerkarmalastchanges" (customerkarmalastchanges) -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS customerkarmalastchanges_id_seq; CREATE SEQUENCE customerkarmalastchanges_id_seq; DROP TABLE IF EXISTS customerkarmalastchanges; CREATE TABLE customerkarmalastchanges ( id integer NOT NULL DEFAULT nextval('customerkarmalastchanges_id_seq'::text), timestamp bigint NOT NULL, customerid integer NOT NULL CONSTRAINT customerkarmalastchanges_customerid_fkey REFERENCES customers (id) ON DELETE CASCADE ON UPDATE CASCADE, userid integer NOT NULL CONSTRAINT customerkarmalastchanges_userid_fkey REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id) ); CREATE INDEX customerkarmalastchanges_timestamp_idx ON customerkarmalastchanges (timestamp); /* -------------------------------------------------------- Structure of table "customercalls" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS customercalls_id_seq; CREATE SEQUENCE customercalls_id_seq; DROP TABLE IF EXISTS customercalls CASCADE; CREATE TABLE customercalls ( id integer DEFAULT nextval('customercalls_id_seq'::text) NOT NULL, userid integer DEFAULT NULL CONSTRAINT customercalls_userid_fkey REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL, dt bigint DEFAULT 0 NOT NULL, filename varchar(150) NOT NULL, outgoing smallint DEFAULT 0 NOT NULL, phone varchar(20) NOT NULL, duration integer DEFAULT 0 NOT NULL, notes text DEFAULT NULL, PRIMARY KEY (id) ); CREATE INDEX customercalls_dt_idx ON customercalls (dt); CREATE INDEX customercalls_filename_idx ON customercalls (filename); CREATE INDEX customercalls_phone_idx ON customercalls (phone); /* -------------------------------------------------------- Structure of table "customercallassignments" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS customercallassignments_id_seq; CREATE SEQUENCE customercallassignments_id_seq; DROP TABLE IF EXISTS customercallassignments; CREATE TABLE customercallassignments ( id integer DEFAULT nextval('customercallassignments_id_seq'::text) NOT NULL, customercallid integer NOT NULL CONSTRAINT customercallassignments_customercallid_fkey REFERENCES customercalls (id) ON UPDATE CASCADE ON DELETE CASCADE, customerid integer NOT NULL CONSTRAINT customercallassignments_customerid_fkey REFERENCES customers (id) ON UPDATE CASCADE ON DELETE CASCADE, PRIMARY KEY (id), CONSTRAINT customercallassignments_customercallid_ukey UNIQUE (customercallid, customerid) ); /* -------------------------------------------------------- Structure of table "numberplans" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS numberplans_id_seq; CREATE SEQUENCE numberplans_id_seq; DROP TABLE IF EXISTS numberplans CASCADE; CREATE TABLE numberplans ( id integer DEFAULT nextval('numberplans_id_seq'::text) NOT NULL, template varchar(255) DEFAULT '' NOT NULL, period smallint DEFAULT 0 NOT NULL, doctype integer DEFAULT 0 NOT NULL, isdefault smallint DEFAULT 0 NOT NULL, datefrom bigint DEFAULT 0 NOT NULL, dateto bigint DEFAULT 0 NOT NULL, customertype smallint DEFAULT NULL, refflag smallint NOT NULL DEFAULT 0, PRIMARY KEY (id) ); /* --------------------------------------------------- Structure of table "states" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS states_id_seq; CREATE SEQUENCE states_id_seq; DROP TABLE IF EXISTS states CASCADE; CREATE TABLE states ( id integer DEFAULT nextval('states_id_seq'::text) NOT NULL, name varchar(255) NOT NULL DEFAULT '', description text NOT NULL DEFAULT '', PRIMARY KEY (id), UNIQUE (name) ); /* --------------------------------------------------- Structure of table "zipcodes" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS zipcodes_id_seq; CREATE SEQUENCE zipcodes_id_seq; DROP TABLE IF EXISTS zipcodes CASCADE; CREATE TABLE zipcodes ( id integer DEFAULT nextval('zipcodes_id_seq'::text) NOT NULL, zip varchar(10) NOT NULL DEFAULT '', stateid integer DEFAULT NULL CONSTRAINT zipcodes_stateid_fkey REFERENCES states (id) ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY (id), UNIQUE (zip) ); CREATE INDEX zipcodes_stateid_idx ON zipcodes (stateid); /* --------------------------------------------------- Structure of table "customer_addresses" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS customer_addresses_id_seq; CREATE SEQUENCE customer_addresses_id_seq; DROP TABLE IF EXISTS customer_addresses CASCADE; CREATE TABLE customer_addresses ( id integer DEFAULT nextval('customer_addresses_id_seq'::text) NOT NULL, customer_id integer CONSTRAINT customer_addresses_customer_id_fkey REFERENCES customers (id) ON DELETE CASCADE ON UPDATE CASCADE, address_id integer REFERENCES addresses (id) ON DELETE CASCADE ON UPDATE CASCADE, type smallint NULL, ten varchar(50) DEFAULT NULL, entity_type smallint DEFAULT NULL, PRIMARY KEY (id), UNIQUE(customer_id, address_id) ); /* -------------------------------------------------------- Structure of table "documents" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS documents_id_seq; CREATE SEQUENCE documents_id_seq; DROP TABLE IF EXISTS documents CASCADE; CREATE TABLE documents ( id integer DEFAULT nextval('documents_id_seq'::text) NOT NULL, type smallint DEFAULT 0 NOT NULL, number integer DEFAULT 0 NOT NULL, numberplanid integer DEFAULT NULL CONSTRAINT documents_numberplanid_fkey REFERENCES numberplans (id) ON DELETE SET NULL ON UPDATE CASCADE, extnumber varchar(255) DEFAULT '' NOT NULL, cdate bigint DEFAULT 0 NOT NULL, sdate bigint DEFAULT 0 NOT NULL, customerid integer DEFAULT NULL CONSTRAINT documents_customerid_fkey REFERENCES customers (id) ON DELETE SET NULL ON UPDATE CASCADE, userid integer DEFAULT NULL CONSTRAINT documents_userid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, divisionid integer DEFAULT NULL CONSTRAINT documents_divisionid_fkey REFERENCES divisions (id) ON DELETE SET NULL ON UPDATE CASCADE, name varchar(255) DEFAULT '' NOT NULL, address varchar(255) DEFAULT '' NOT NULL, zip varchar(10) NULL DEFAULT NULL, city varchar(100) NULL DEFAULT NULL, countryid integer DEFAULT NULL CONSTRAINT documents_countryid_fkey REFERENCES countries (id) ON DELETE SET NULL ON UPDATE CASCADE, ten varchar(50) DEFAULT '' NOT NULL, ssn varchar(50) DEFAULT '' NOT NULL, paytime smallint DEFAULT 0 NOT NULL, paytype smallint DEFAULT NULL, closed smallint DEFAULT 0 NOT NULL, reference integer DEFAULT NULL CONSTRAINT documents_reference_fkey REFERENCES documents (id) ON DELETE SET NULL ON UPDATE CASCADE, reason varchar(255) DEFAULT '' NOT NULL, div_name text DEFAULT '' NOT NULL, div_shortname text DEFAULT '' NOT NULL, div_address varchar(255) DEFAULT '' NOT NULL, div_city varchar(255) DEFAULT '' NOT NULL, div_zip varchar(255) DEFAULT '' NOT NULL, div_countryid integer DEFAULT NULL CONSTRAINT documents_div_countryid_fkey REFERENCES countries (id) ON DELETE SET NULL ON UPDATE CASCADE, div_ten varchar(255) DEFAULT '' NOT NULL, div_regon varchar(255) DEFAULT '' NOT NULL, div_bank varchar(100) DEFAULT NULL, div_account varchar(48) DEFAULT '' NOT NULL, div_inv_header text DEFAULT '' NOT NULL, div_inv_footer text DEFAULT '' NOT NULL, div_inv_author text DEFAULT '' NOT NULL, div_inv_cplace text DEFAULT '' NOT NULL, fullnumber varchar(50) DEFAULT NULL, cancelled smallint DEFAULT 0 NOT NULL, published smallint DEFAULT 0 NOT NULL, comment text DEFAULT NULL, cuserid integer DEFAULT NULL CONSTRAINT documents_cuserid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, recipient_address_id integer DEFAULT NULL REFERENCES addresses (id) ON DELETE SET NULL ON UPDATE CASCADE, recipient_ten varchar(50) DEFAULT NULL, recipient_type smallint DEFAULT NULL, post_address_id integer DEFAULT NULL REFERENCES addresses (id) ON DELETE SET NULL ON UPDATE CASCADE, template varchar(255) DEFAULT NULL, commitflags smallint DEFAULT 0 NOT NULL, archived smallint DEFAULT 0 NOT NULL, auserid integer DEFAULT NULL CONSTRAINT documents_auserid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, adate bigint DEFAULT 0 NOT NULL, currency varchar(3), currencyvalue numeric(17,10) DEFAULT 1.0, senddate bigint DEFAULT 0 NOT NULL, memo text DEFAULT NULL, confirmdate bigint NOT NULL DEFAULT 0, flags smallint DEFAULT 0 NOT NULL, origin smallint DEFAULT NULL, extid varchar(64) DEFAULT NULL, PRIMARY KEY (id) ); CREATE INDEX documents_cdate_idx ON documents(cdate); CREATE INDEX documents_numberplanid_idx ON documents(numberplanid); CREATE INDEX documents_customerid_idx ON documents(customerid); CREATE INDEX documents_closed_idx ON documents(closed); CREATE INDEX documents_reference_idx ON documents(reference); /* -------------------------------------------------------- Structure of table "documentcontents" -------------------------------------------------------- */ DROP TABLE IF EXISTS documentcontents CASCADE; CREATE TABLE documentcontents ( docid integer NOT NULL REFERENCES documents (id) ON DELETE CASCADE ON UPDATE CASCADE, title text DEFAULT '' NOT NULL, fromdate bigint DEFAULT 0 NOT NULL, todate bigint DEFAULT 0 NOT NULL, description text DEFAULT '' NOT NULL, dynamicperiod smallint DEFAULT 0 NOT NULL, attributes text DEFAULT NULL, UNIQUE (docid) ); CREATE INDEX documentcontents_todate_idx ON documentcontents (todate); CREATE INDEX documentcontents_fromdate_idx ON documentcontents (fromdate); /* -------------------------------------------------------- Structure of table "documentattachments" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS documentattachments_id_seq; CREATE SEQUENCE documentattachments_id_seq; DROP TABLE IF EXISTS documentattachments CASCADE; CREATE TABLE documentattachments ( id integer DEFAULT nextval('documentattachments_id_seq'::text) NOT NULL, docid integer NOT NULL REFERENCES documents (id) ON DELETE CASCADE ON UPDATE CASCADE, filename varchar(255) NOT NULL, contenttype varchar(255) NOT NULL, md5sum varchar(32) NOT NULL, type smallint DEFAULT 1 NOT NULL, cdate bigint NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE (docid, md5sum) ); CREATE INDEX documentattachments_md5sum_idx ON documentattachments (md5sum); /* --------------------------------------------------- Structure of table "cashregs" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS cashregs_id_seq; CREATE SEQUENCE cashregs_id_seq; DROP TABLE IF EXISTS cashregs CASCADE; CREATE TABLE cashregs ( id integer DEFAULT nextval('cashregs_id_seq'::text) NOT NULL, name varchar(255) DEFAULT '' NOT NULL, description text DEFAULT '' NOT NULL, in_numberplanid integer DEFAULT NULL CONSTRAINT cashregs_in_numberplanid_fkey REFERENCES numberplans (id) ON DELETE SET NULL ON UPDATE CASCADE, out_numberplanid integer DEFAULT NULL CONSTRAINT cashregs_out_numberplanid_fkey REFERENCES numberplans (id) ON DELETE SET NULL ON UPDATE CASCADE, disabled smallint DEFAULT 0 NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); /* -------------------------------------------------------- Structure of table "receiptcontents" -------------------------------------------------------- */ DROP TABLE IF EXISTS receiptcontents CASCADE; CREATE TABLE receiptcontents ( docid integer NOT NULL CONSTRAINT receiptcontents_docid_fk REFERENCES documents (id) ON DELETE CASCADE ON UPDATE CASCADE, itemid smallint DEFAULT 0 NOT NULL, value numeric(9,2) DEFAULT 0 NOT NULL, regid integer DEFAULT NULL CONSTRAINT receiptcontents_regid_fkey REFERENCES cashregs (id) ON DELETE SET NULL ON UPDATE CASCADE, description text DEFAULT '' NOT NULL ); CREATE INDEX receiptcontents_docid_idx ON receiptcontents(docid); CREATE INDEX receiptcontents_regid_idx ON receiptcontents(regid); /* -------------------------------------------------------- Structure of table "taxes" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS taxes_id_seq; CREATE SEQUENCE taxes_id_seq; DROP TABLE IF EXISTS taxes CASCADE; CREATE TABLE taxes ( id integer DEFAULT nextval('taxes_id_seq'::text) NOT NULL, value numeric(4,2) DEFAULT 0 NOT NULL, taxed smallint DEFAULT 0 NOT NULL, label varchar(16) DEFAULT '' NOT NULL, validfrom bigint DEFAULT 0 NOT NULL, validto bigint DEFAULT 0 NOT NULL, reversecharge smallint DEFAULT 0 NOT NULL, PRIMARY KEY (id) ); /* --------------------------------------------------- Structure of table "voipaccounts" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS voipaccounts_id_seq; CREATE SEQUENCE voipaccounts_id_seq; DROP TABLE IF EXISTS voipaccounts CASCADE; CREATE TABLE voipaccounts ( id integer NOT NULL DEFAULT nextval('voipaccounts_id_seq'::text), ownerid integer DEFAULT NULL CONSTRAINT voipaccounts_ownerid_fkey REFERENCES customers (id) ON DELETE CASCADE ON UPDATE CASCADE, login varchar(255) NOT NULL DEFAULT '', passwd varchar(255) NOT NULL DEFAULT '', access smallint NOT NULL DEFAULT 1, creationdate integer NOT NULL DEFAULT 0, moddate integer NOT NULL DEFAULT 0, creatorid integer DEFAULT NULL CONSTRAINT voipaccounts_creatorid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, modid integer DEFAULT NULL CONSTRAINT voipaccounts_modid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, balance numeric(12,5) NOT NULL DEFAULT 0, flags smallint NOT NULL DEFAULT 0, cost_limit numeric(12,2) NULL DEFAULT NULL, address_id integer REFERENCES addresses (id) ON DELETE SET NULL ON UPDATE CASCADE, description text NOT NULL DEFAULT '', serviceproviderid integer DEFAULT NULL CONSTRAINT voipaccounts_serviceproviderid_fkey REFERENCES serviceproviders (id) ON DELETE CASCADE ON UPDATE CASCADE, extid varchar(64) DEFAULT NULL, PRIMARY KEY (id) ); /* -------------------------------------------------------- Structure of table "voip_rule_groups" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS voip_rule_groups_id_seq CASCADE; CREATE SEQUENCE voip_rule_groups_id_seq; DROP TABLE IF EXISTS voip_rule_groups CASCADE; CREATE TABLE voip_rule_groups ( id integer DEFAULT nextval('voip_rule_groups_id_seq'::text) NOT NULL, name text NOT NULL, description text NULL, PRIMARY KEY (id) ); /* -------------------------------------------------------- Structure of table "voip_prefix_groups" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS voip_prefix_groups_id_seq; CREATE SEQUENCE voip_prefix_groups_id_seq; DROP TABLE IF EXISTS voip_prefix_groups CASCADE; CREATE TABLE voip_prefix_groups ( id integer DEFAULT nextval('voip_prefix_groups_id_seq'::text) NOT NULL, name text NOT NULL, description text NULL, PRIMARY KEY (id) ); /* -------------------------------------------------------- Structure of table "voip_rules" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS voip_rules_id_seq CASCADE; CREATE SEQUENCE voip_rules_id_seq; DROP TABLE IF EXISTS voip_rules CASCADE; CREATE TABLE voip_rules ( id integer DEFAULT nextval('voip_rules_id_seq'::text) NOT NULL, rule_group_id integer NOT NULL REFERENCES voip_rule_groups (id) ON DELETE CASCADE ON UPDATE CASCADE, prefix_group_id integer NOT NULL REFERENCES voip_prefix_groups (id) ON DELETE CASCADE ON UPDATE CASCADE, settings text NULL, PRIMARY KEY (id) ); /* -------------------------------------------------------- Structure of table "voip_tariffs" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS voip_tariffs_id_seq; CREATE SEQUENCE voip_tariffs_id_seq; DROP TABLE IF EXISTS voip_tariffs CASCADE; CREATE TABLE voip_tariffs ( id integer DEFAULT nextval('voip_tariffs_id_seq'::text) NOT NULL, name varchar(100) NOT NULL, description text NULL DEFAULT NULL, PRIMARY KEY (id) ); /* -------------------------------------------------------- Structure of table "voip_rule_states" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS voip_rule_states_id_seq; CREATE SEQUENCE voip_rule_states_id_seq; DROP TABLE IF EXISTS voip_rule_states CASCADE; CREATE TABLE voip_rule_states ( id integer DEFAULT nextval('voip_rule_states_id_seq'::text) NOT NULL, voip_account_id integer NOT NULL DEFAULT NULL REFERENCES voipaccounts (id) ON DELETE CASCADE ON UPDATE CASCADE, rule_id integer NOT NULL DEFAULT NULL REFERENCES voip_rules (id) ON DELETE CASCADE ON UPDATE CASCADE, units_left integer NULL DEFAULT NULL, PRIMARY KEY(id), UNIQUE(voip_account_id, rule_id) ); /* -------------------------------------------------------- Structure of table "voip_prefixes" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS voip_prefixes_id_seq; CREATE SEQUENCE voip_prefixes_id_seq; DROP TABLE IF EXISTS voip_prefixes CASCADE; CREATE TABLE voip_prefixes ( id integer DEFAULT nextval('voip_prefixes_id_seq'::text) NOT NULL, prefix varchar(30) NOT NULL, groupid integer NOT NULL REFERENCES voip_prefix_groups(id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id), UNIQUE (prefix, groupid) ); /* -------------------------------------------------------- Structure of table "voip_cdr" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS voip_cdr_id_seq; CREATE SEQUENCE voip_cdr_id_seq; DROP TABLE IF EXISTS voip_cdr CASCADE; CREATE TABLE voip_cdr ( id integer DEFAULT nextval('voip_cdr_id_seq'::text) NOT NULL, caller varchar(20) NOT NULL, callee varchar(20) NOT NULL, call_start_time bigint NOT NULL, totaltime bigint NOT NULL, billedtime bigint NOT NULL, price numeric(12,5) NOT NULL, status smallint NOT NULL, direction smallint NOT NULL, type smallint NOT NULL DEFAULT 0, callervoipaccountid integer NULL REFERENCES voipaccounts(id) ON DELETE SET NULL ON UPDATE CASCADE, calleevoipaccountid integer NULL REFERENCES voipaccounts(id) ON DELETE SET NULL ON UPDATE CASCADE, caller_flags smallint NOT NULL DEFAULT 0, callee_flags smallint NOT NULL DEFAULT 0, caller_prefix_group varchar(100) NULL, callee_prefix_group varchar(100) NULL, uniqueid varchar(80) NOT NULL, fraction varchar(256) DEFAULT NULL, prefix varchar(256) DEFAULT NULL, prefixname varchar(256) DEFAULT NULL, incremental smallint NOT NULL DEFAULT 0, PRIMARY KEY (id), CONSTRAINT voip_cdr_direction_uniqueid_ukey UNIQUE (direction, uniqueid) ); CREATE INDEX voip_cdr_caller_idx ON voip_cdr (caller); CREATE INDEX voip_cdr_callee_idx ON voip_cdr (callee); CREATE INDEX voip_cdr_call_start_time_idx ON voip_cdr (call_start_time); CREATE INDEX voip_cdr_direction_idx ON voip_cdr (direction); CREATE INDEX voip_cdr_type_idx ON voip_cdr (type); CREATE INDEX voip_cdr_incremental_idx ON voip_cdr (incremental); /* -------------------------------------------------------- Structure of table "voip_price_groups" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS voip_price_groups_id_seq; CREATE SEQUENCE voip_price_groups_id_seq; DROP TABLE IF EXISTS voip_price_groups CASCADE; CREATE TABLE voip_price_groups ( id integer DEFAULT nextval('voip_price_groups_id_seq'::text) NOT NULL, voip_tariff_id integer NOT NULL REFERENCES voip_tariffs (id) ON DELETE CASCADE ON UPDATE CASCADE, prefix_group_id integer NOT NULL REFERENCES voip_prefix_groups (id) ON DELETE CASCADE ON UPDATE CASCADE, price numeric(12,5) DEFAULT 0 NOT NULL, unitsize smallint DEFAULT 0 NOT NULL, PRIMARY KEY (id) ); /* -------------------------------------------------------- Structure of table "tariffs" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS tariffs_id_seq; CREATE SEQUENCE tariffs_id_seq; DROP TABLE IF EXISTS tariffs CASCADE; CREATE TABLE tariffs ( id integer DEFAULT nextval('tariffs_id_seq'::text) NOT NULL, name varchar(255) DEFAULT '' NOT NULL, type smallint DEFAULT 1 NOT NULL, value numeric(9,3) DEFAULT 0 NOT NULL, taxcategory smallint DEFAULT 0 NOT NULL, period smallint DEFAULT NULL, taxid integer NOT NULL CONSTRAINT tariffs_taxid_fkey REFERENCES taxes (id) ON DELETE CASCADE ON UPDATE CASCADE, numberplanid integer DEFAULT NULL REFERENCES numberplans (id) ON DELETE CASCADE ON UPDATE CASCADE, prodid varchar(255) DEFAULT '' NOT NULL, uprate integer DEFAULT 0 NOT NULL, upceil integer DEFAULT 0 NOT NULL, downrate integer DEFAULT 0 NOT NULL, downceil integer DEFAULT 0 NOT NULL, down_burst_time smallint DEFAULT 0 NOT NULL, down_burst_limit integer DEFAULT 0 NOT NULL, down_burst_threshold integer DEFAULT 0 NOT NULL, up_burst_time smallint DEFAULT 0 NOT NULL, up_burst_limit integer DEFAULT 0 NOT NULL, up_burst_threshold integer DEFAULT 0 NOT NULL, climit integer DEFAULT 0 NOT NULL, plimit integer DEFAULT 0 NOT NULL, dlimit integer DEFAULT 0 NOT NULL, uprate_n integer DEFAULT NULL, upceil_n integer DEFAULT NULL, downrate_n integer DEFAULT NULL, downceil_n integer DEFAULT NULL, down_burst_time_n smallint DEFAULT NULL, down_burst_limit_n integer DEFAULT NULL, down_burst_threshold_n integer DEFAULT NULL, up_burst_time_n smallint DEFAULT NULL, up_burst_limit_n integer DEFAULT NULL, up_burst_threshold_n integer DEFAULT NULL, climit_n integer DEFAULT NULL, plimit_n integer DEFAULT NULL, domain_limit integer DEFAULT NULL, alias_limit integer DEFAULT NULL, sh_limit integer DEFAULT NULL, www_limit integer DEFAULT NULL, mail_limit integer DEFAULT NULL, ftp_limit integer DEFAULT NULL, sql_limit integer DEFAULT NULL, cloud_limit integer DEFAULT NULL, quota_sh_limit integer DEFAULT NULL, quota_www_limit integer DEFAULT NULL, quota_mail_limit integer DEFAULT NULL, quota_ftp_limit integer DEFAULT NULL, quota_sql_limit integer DEFAULT NULL, quota_cloud_limit integer DEFAULT NULL, description text DEFAULT '' NOT NULL, disabled smallint DEFAULT 0 NOT NULL, voip_tariff_id integer DEFAULT NULL REFERENCES voip_tariffs (id) ON DELETE SET NULL ON UPDATE CASCADE, voip_tariff_rule_id integer DEFAULT NULL CONSTRAINT tariffs_voip_tariff_rule_id_fkey REFERENCES voip_rule_groups (id) ON DELETE SET NULL ON UPDATE CASCADE, datefrom bigint NOT NULL DEFAULT 0, dateto bigint NOT NULL DEFAULT 0, authtype smallint DEFAULT 0 NOT NULL, currency varchar(3), flags smallint DEFAULT 0 NOT NULL, netvalue numeric(9,3) DEFAULT NULL, notes text DEFAULT NULL, serviceproviderid integer DEFAULT NULL CONSTRAINT tariffs_serviceproviderid_fkey REFERENCES serviceproviders (id) ON DELETE CASCADE ON UPDATE CASCADE, extid varchar(64) DEFAULT NULL, PRIMARY KEY (id), CONSTRAINT tariffs_name_key UNIQUE (name, value, currency, period) ); CREATE INDEX tariffs_type_idx ON tariffs (type); /* -------------------------------------------------------- Structure of table "voip_numbers" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS voip_numbers_id_seq; CREATE SEQUENCE voip_numbers_id_seq; DROP TABLE IF EXISTS voip_numbers CASCADE; CREATE TABLE voip_numbers ( id integer DEFAULT nextval('voip_numbers_id_seq'::text) NOT NULL, voip_account_id integer NOT NULL REFERENCES voipaccounts (id) ON DELETE CASCADE ON UPDATE CASCADE, phone varchar(20) NOT NULL, number_index smallint, info varchar(255) DEFAULT NULL, tariff_id integer NULL REFERENCES tariffs (id) ON DELETE SET NULL ON UPDATE CASCADE, UNIQUE(phone), UNIQUE(voip_account_id, number_index), PRIMARY KEY (id) ); /* -------------------------------------------------------- Structure of table "voip_pool_numbers" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS voip_pool_numbers_id_seq; CREATE SEQUENCE voip_pool_numbers_id_seq; DROP TABLE IF EXISTS voip_pool_numbers CASCADE; CREATE TABLE voip_pool_numbers ( id integer DEFAULT nextval('voip_pool_numbers_id_seq'::text) NOT NULL, disabled smallint DEFAULT 0, name varchar(30) NOT NULL, poolstart varchar(20) NOT NULL, poolend varchar(20) NOT NULL, description text, type smallint NOT NULL DEFAULT 1, PRIMARY KEY (id), UNIQUE (name) ); /* -------------------------------------------------------- Structure of table "voip_emergency_numbers" -------------------------------------------------------- */ DROP TABLE IF EXISTS voip_emergency_numbers CASCADE; CREATE TABLE voip_emergency_numbers ( location_borough integer NOT NULL REFERENCES location_boroughs (id) ON DELETE CASCADE ON UPDATE CASCADE, number integer NOT NULL, fullnumber varchar(20) NOT NULL, UNIQUE (location_borough, number) ); CREATE INDEX voip_emergency_numbers_number_idx ON voip_emergency_numbers (number); /* -------------------------------------------------------- Structure of table "liabilities" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS liabilities_id_seq; CREATE SEQUENCE liabilities_id_seq; DROP TABLE IF EXISTS liabilities CASCADE; CREATE TABLE liabilities ( id integer DEFAULT nextval('liabilities_id_seq'::text) NOT NULL, value numeric(9,3) DEFAULT 0 NOT NULL, taxcategory smallint DEFAULT 0 NOT NULL, currency varchar(3), name text DEFAULT '' NOT NULL, note text DEFAULT NULL, taxid integer NOT NULL CONSTRAINT liabilities_taxid_fkey REFERENCES taxes (id) ON DELETE CASCADE ON UPDATE CASCADE, prodid varchar(255) DEFAULT '' NOT NULL, type smallint DEFAULT -1 NOT NULL, flags smallint DEFAULT 0 NOT NULL, netvalue numeric(9,3) DEFAULT NULL, PRIMARY KEY (id) ); /* -------------------------------------------------------- Structure of table "promotions" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS promotions_id_seq; CREATE SEQUENCE promotions_id_seq; DROP TABLE IF EXISTS promotions CASCADE; CREATE TABLE promotions ( id integer DEFAULT nextval('promotions_id_seq'::text) NOT NULL, name varchar(255) NOT NULL, description text DEFAULT NULL, disabled smallint DEFAULT 0 NOT NULL, deleted smallint DEFAULT 0 NOT NULL, datefrom bigint DEFAULT 0 NOT NULL, dateto bigint DEFAULT 0 NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); /* -------------------------------------------------------- Structure of table "promotionschemas" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS promotionschemas_id_seq; CREATE SEQUENCE promotionschemas_id_seq; DROP TABLE IF EXISTS promotionschemas CASCADE; CREATE TABLE promotionschemas ( id integer DEFAULT nextval('promotionschemas_id_seq'::text) NOT NULL, name varchar(255) NOT NULL, description text DEFAULT NULL, data text DEFAULT NULL, promotionid integer DEFAULT NULL REFERENCES promotions (id) ON DELETE CASCADE ON UPDATE CASCADE, disabled smallint DEFAULT 0 NOT NULL, deleted smallint DEFAULT 0 NOT NULL, length smallint DEFAULT NULL, datefrom bigint DEFAULT 0 NOT NULL, dateto bigint DEFAULT 0 NOT NULL, PRIMARY KEY (id), CONSTRAINT promotionschemas_promotionid_key UNIQUE (promotionid, name) ); /* -------------------------------------------------------- Structure of table "promotionassignments" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS promotionassignments_id_seq; CREATE SEQUENCE promotionassignments_id_seq; DROP TABLE IF EXISTS promotionassignments CASCADE; CREATE TABLE promotionassignments ( id integer DEFAULT nextval('promotionassignments_id_seq'::text) NOT NULL, promotionschemaid integer DEFAULT NULL REFERENCES promotionschemas (id) ON DELETE CASCADE ON UPDATE CASCADE, tariffid integer DEFAULT NULL REFERENCES tariffs (id) ON DELETE CASCADE ON UPDATE CASCADE, data text DEFAULT NULL, optional smallint DEFAULT 0 NOT NULL, label varchar(60) DEFAULT NULL, orderid integer NOT NULL DEFAULT 0, backwardperiod smallint DEFAULT 0 NOT NULL, PRIMARY KEY (id) ); CREATE INDEX promotionassignments_tariffid_idx ON promotionassignments (tariffid); /* -------------------------------------------------------- Structure of table "promotionattachments" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS promotionattachments_id_seq; CREATE SEQUENCE promotionattachments_id_seq; DROP TABLE IF EXISTS promotionattachments CASCADE; CREATE TABLE promotionattachments ( id integer DEFAULT nextval('promotionattachments_id_seq'::text) NOT NULL, filename varchar(255) NOT NULL, contenttype varchar(255) NOT NULL, label varchar(255) NOT NULL, checked smallint DEFAULT 0, promotionid integer DEFAULT NULL CONSTRAINT promotionattachments_promotionid_fkey REFERENCES promotions (id) ON DELETE CASCADE ON UPDATE CASCADE, promotionschemaid integer DEFAULT NULL CONSTRAINT promotionattachments_promotionschemaid_fkey REFERENCES promotionschemas (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id) ); /* ---------------------------------------------------- Structure of table "assignments" ---------------------------------------------------*/ DROP SEQUENCE IF EXISTS assignments_id_seq; CREATE SEQUENCE assignments_id_seq; DROP TABLE IF EXISTS assignments CASCADE; CREATE TABLE assignments ( id integer default nextval('assignments_id_seq'::text) NOT NULL, tariffid integer DEFAULT NULL CONSTRAINT assignments_tariffid_fkey REFERENCES tariffs (id) ON DELETE CASCADE ON UPDATE CASCADE, liabilityid integer DEFAULT NULL CONSTRAINT assignments_liabilityid_fkey REFERENCES liabilities (id) ON DELETE CASCADE ON UPDATE CASCADE, customerid integer NOT NULL CONSTRAINT assignments_customerid_fkey REFERENCES customers (id) ON DELETE CASCADE ON UPDATE CASCADE, period smallint DEFAULT 0 NOT NULL, backwardperiod smallint DEFAULT 0 NOT NULL, note text DEFAULT NULL, at bigint DEFAULT 0 NOT NULL, datefrom bigint DEFAULT 0 NOT NULL, dateto bigint DEFAULT 0 NOT NULL, invoice smallint DEFAULT 0 NOT NULL, suspended smallint DEFAULT 0 NOT NULL, settlement smallint DEFAULT 0 NOT NULL, pdiscount numeric(5,2) DEFAULT 0 NOT NULL, vdiscount numeric(9,3) DEFAULT 0 NOT NULL, paytime smallint DEFAULT NULL, paytype smallint DEFAULT NULL, numberplanid integer DEFAULT NULL REFERENCES numberplans (id) ON DELETE SET NULL ON UPDATE CASCADE, attribute varchar(255) DEFAULT NULL, recipient_address_id integer DEFAULT NULL CONSTRAINT assignments_recipient_address_id_fkey REFERENCES addresses (id) ON DELETE SET NULL ON UPDATE CASCADE, docid integer DEFAULT NULL CONSTRAINT assignments_docid_fkey REFERENCES documents (id) ON DELETE CASCADE ON UPDATE CASCADE, commited smallint DEFAULT 1 NOT NULL, separatedocument varchar(100) DEFAULT NULL, count numeric(9,3) DEFAULT 1 NOT NULL, promotionschemaid integer DEFAULT NULL CONSTRAINT assignments_promotionschemaid_fkey REFERENCES promotionschemas (id) ON DELETE RESTRICT ON UPDATE CASCADE, separateitem smallint NOT NULL DEFAULT 0, PRIMARY KEY (id) ); CREATE INDEX assignments_tariffid_idx ON assignments (tariffid); CREATE INDEX assignments_customerid_idx ON assignments (customerid); CREATE INDEX assignments_numberplanid_idx ON assignments (numberplanid); /* -------------------------------------------------------- Structure of table "voip_number_assignments" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS voip_number_assignments_id_seq; CREATE SEQUENCE voip_number_assignments_id_seq; DROP TABLE IF EXISTS voip_number_assignments CASCADE; CREATE TABLE voip_number_assignments ( id integer DEFAULT nextval('voip_number_assignments_id_seq'::text) NOT NULL, number_id integer NOT NULL REFERENCES voip_numbers (id) ON DELETE CASCADE ON UPDATE CASCADE, assignment_id integer NOT NULL REFERENCES assignments (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id), CONSTRAINT voip_number_assignments_assignment_id_key UNIQUE (assignment_id, number_id) ); /* -------------------------------------------------------- Structure of table "invoicecontents" -------------------------------------------------------- */ DROP TABLE IF EXISTS invoicecontents CASCADE; CREATE TABLE invoicecontents ( docid integer NOT NULL CONSTRAINT invoicecontents_docid_fkey REFERENCES documents (id) ON DELETE CASCADE ON UPDATE CASCADE, itemid smallint DEFAULT 0 NOT NULL, value numeric(12,5) DEFAULT 0 NOT NULL, taxid integer NOT NULL CONSTRAINT invoicecontents_taxid_fkey REFERENCES taxes (id) ON DELETE CASCADE ON UPDATE CASCADE, prodid varchar(255) DEFAULT '' NOT NULL, content varchar(16) DEFAULT '' NOT NULL, count numeric(9,3) DEFAULT 0 NOT NULL, description text DEFAULT '' NOT NULL, tariffid integer DEFAULT NULL CONSTRAINT invoicecontents_tariffid_fkey REFERENCES tariffs (id) ON DELETE SET NULL ON UPDATE CASCADE, pdiscount numeric(5,2) DEFAULT 0 NOT NULL, vdiscount numeric(9,3) DEFAULT 0 NOT NULL, taxcategory smallint DEFAULT 0 NOT NULL, period smallint DEFAULT 3 ); CREATE INDEX invoicecontents_docid_idx ON invoicecontents (docid); /* -------------------------------------------------------- Structure of table "debitnotecontents" -------------------------------------------------------- */ DROP TABLE IF EXISTS debitnotecontents CASCADE; DROP SEQUENCE IF EXISTS debitnotecontents_id_seq; CREATE SEQUENCE debitnotecontents_id_seq; CREATE TABLE debitnotecontents ( id integer DEFAULT nextval('debitnotecontents_id_seq'::text) NOT NULL, docid integer NOT NULL CONSTRAINT debitnotecontents_docid_fkey REFERENCES documents (id) ON DELETE CASCADE ON UPDATE CASCADE, itemid smallint DEFAULT 0 NOT NULL, value numeric(9,2) DEFAULT 0 NOT NULL, description text DEFAULT '' NOT NULL, PRIMARY KEY (id), CONSTRAINT debitnotecontents_docid_key UNIQUE (docid, itemid) ); /* --------------------------------------------------- Structure of table "cashsources" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS cashsources_id_seq; CREATE SEQUENCE cashsources_id_seq; DROP TABLE IF EXISTS cashsources CASCADE; CREATE TABLE cashsources ( id integer DEFAULT nextval('cashsources_id_seq'::text) NOT NULL, name varchar(32) DEFAULT '' NOT NULL, description text DEFAULT NULL, account varchar(48) NOT NULL DEFAULT '', deleted smallint NOT NULL DEFAULT 0, isdefault smallint NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE (name) ); /* --------------------------------------------------- Structure of table "sourcefiles" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS sourcefiles_id_seq; CREATE SEQUENCE sourcefiles_id_seq; DROP TABLE IF EXISTS sourcefiles CASCADE; CREATE TABLE sourcefiles ( id integer DEFAULT nextval('sourcefiles_id_seq'::text) NOT NULL, userid integer DEFAULT NULL REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, name varchar(255) NOT NULL, idate bigint NOT NULL, PRIMARY KEY (id), CONSTRAINT sourcefiles_idate_key UNIQUE (idate, name) ); CREATE INDEX sourcefiles_userid_idx ON sourcefiles (userid); /* --------------------------------------------------- Structure of table "cashimport" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS cashimport_id_seq; CREATE SEQUENCE cashimport_id_seq; DROP TABLE IF EXISTS cashimport CASCADE; CREATE TABLE cashimport ( id integer DEFAULT nextval('cashimport_id_seq'::text) NOT NULL, date bigint DEFAULT 0 NOT NULL, value numeric(9,2) DEFAULT 0 NOT NULL, customer text DEFAULT '' NOT NULL, description text DEFAULT '' NOT NULL, customerid integer DEFAULT NULL CONSTRAINT cashimport_customerid_fkey REFERENCES customers (id) ON DELETE SET NULL ON UPDATE CASCADE, hash varchar(50) DEFAULT '' NOT NULL, closed smallint DEFAULT 0 NOT NULL, sourceid integer DEFAULT NULL REFERENCES cashsources (id) ON DELETE SET NULL ON UPDATE CASCADE, sourcefileid integer DEFAULT NULL REFERENCES sourcefiles (id) ON DELETE SET NULL ON UPDATE CASCADE, srcaccount varchar(60) DEFAULT NULL, operdate bigint DEFAULT NULL, extid varchar(64) DEFAULT NULL, PRIMARY KEY (id), CONSTRAINT cashimport_sourceid_extid_ukey UNIQUE (sourceid, extid) ); CREATE INDEX cashimport_hash_idx ON cashimport (hash); CREATE INDEX cashimport_customerid_idx ON cashimport (customerid); CREATE INDEX cashimport_sourcefileid_idx ON cashimport (sourcefileid); CREATE INDEX cashimport_sourceid_idx ON cashimport (sourceid); CREATE INDEX cashimport_extid_idx ON cashimport (extid); /* --------------------------------------------------- Structure of table customerbalances ------------------------------------------------------*/ DROP TABLE IF EXISTS customerbalances; CREATE TABLE customerbalances ( customerid integer NOT NULL CONSTRAINT customerbalances_customerid_fkey REFERENCES customers (id) ON DELETE CASCADE ON UPDATE CASCADE, balance numeric(12,2) NOT NULL ); /* -------------------------------------------------------- Structure of table "cash" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS cash_id_seq; CREATE SEQUENCE cash_id_seq; DROP TABLE IF EXISTS cash CASCADE; CREATE TABLE cash ( id integer DEFAULT nextval('cash_id_seq'::text) NOT NULL, time bigint DEFAULT 0 NOT NULL, type smallint DEFAULT 0 NOT NULL, servicetype smallint DEFAULT NULL, userid integer DEFAULT NULL CONSTRAINT cash_userid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, value numeric(9,2) DEFAULT 0 NOT NULL, taxid integer DEFAULT NULL CONSTRAINT cash_taxid_fkey REFERENCES taxes (id) ON DELETE SET NULL ON UPDATE CASCADE, customerid integer DEFAULT NULL CONSTRAINT cash_customerid_fkey REFERENCES customers (id) ON DELETE SET NULL ON UPDATE CASCADE, comment text DEFAULT '' NOT NULL, docid integer DEFAULT NULL CONSTRAINT cash_docid_fkey REFERENCES documents (id) ON DELETE CASCADE ON UPDATE CASCADE, itemid smallint DEFAULT 0 NOT NULL, importid integer DEFAULT NULL CONSTRAINT cash_importid_fkey REFERENCES cashimport (id) ON DELETE CASCADE ON UPDATE CASCADE, sourceid integer DEFAULT NULL CONSTRAINT cash_sourceid_fkey REFERENCES cashsources (id) ON DELETE SET NULL ON UPDATE CASCADE, linktechnology integer DEFAULT NULL, currency varchar(3), currencyvalue numeric(17,10) DEFAULT 1.0, notification smallint DEFAULT 1, PRIMARY KEY (id), CONSTRAINT cash_importid_ukey UNIQUE (importid) ); CREATE INDEX cash_customerid_idx ON cash (customerid); CREATE INDEX cash_docid_idx ON cash (docid); CREATE INDEX cash_importid_idx ON cash (importid); CREATE INDEX cash_sourceid_idx ON cash (sourceid); CREATE INDEX cash_time_idx ON cash (time); CREATE INDEX cash_linktechnology_idx ON cash (linktechnology); CREATE INDEX cash_notification_idx ON cash (notification); /* -------------------------------------------------------- Structure of table "pna" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS pna_id_seq; CREATE SEQUENCE pna_id_seq; DROP TABLE IF EXISTS pna CASCADE; CREATE TABLE pna ( id integer DEFAULT nextval('pna_id_seq'::text) NOT NULL, zip varchar(128) NOT NULL, cityid integer DEFAULT NULL REFERENCES location_cities (id) ON DELETE CASCADE ON UPDATE CASCADE, cityname varchar(100) DEFAULT NULL, streetid integer DEFAULT NULL REFERENCES location_streets (id) ON DELETE CASCADE ON UPDATE CASCADE, streetname varchar(100) DEFAULT NULL, fromnumber smallint DEFAULT NULL, fromletter varchar(8) DEFAULT NULL, tonumber smallint DEFAULT NULL, toletter varchar(8) DEFAULT NULL, parity smallint DEFAULT 0 NOT NULL, PRIMARY KEY (id), CONSTRAINT pna_zip_cityid_streetid_fromnumber_tonumber_key UNIQUE (zip, cityid, cityname, streetid, streetname, fromnumber, fromletter, tonumber, toletter, parity) ); CREATE INDEX pna_fromnumber_idx ON pna (fromnumber); CREATE INDEX pna_tonumber_idx ON pna (tonumber); /* --------------------------------------------------- Structure of table "ewx_channels" (EtherWerX(R)) ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS ewx_channels_id_seq; CREATE SEQUENCE ewx_channels_id_seq; DROP TABLE IF EXISTS ewx_channels CASCADE; CREATE TABLE ewx_channels ( id integer DEFAULT nextval('ewx_channels_id_seq'::text) NOT NULL, name varchar(32) DEFAULT '' NOT NULL, upceil integer DEFAULT 0 NOT NULL, downceil integer DEFAULT 0 NOT NULL, upceil_n integer DEFAULT NULL, downceil_n integer DEFAULT NULL, halfduplex smallint DEFAULT NULL, PRIMARY KEY (id), UNIQUE (name) ); /* --------------------------------------------------- Structure of table "ewx_stm_channels" (EtherWerX(R)) ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS ewx_stm_channels_id_seq; CREATE SEQUENCE ewx_stm_channels_id_seq; DROP TABLE IF EXISTS ewx_stm_channels CASCADE; CREATE TABLE ewx_stm_channels ( id integer DEFAULT nextval('ewx_stm_channels_id_seq'::text) NOT NULL, cid integer DEFAULT NULL CONSTRAINT ewx_stm_channels_cid_fkey REFERENCES ewx_channels (id) ON DELETE SET NULL ON UPDATE CASCADE, upceil integer DEFAULT 0 NOT NULL, downceil integer DEFAULT 0 NOT NULL, halfduplex smallint DEFAULT NULL, PRIMARY KEY (id), UNIQUE (cid) ); /* --------------------------------------------------- Structure of table "hosts" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS hosts_id_seq; CREATE SEQUENCE hosts_id_seq; DROP TABLE IF EXISTS hosts CASCADE; CREATE TABLE hosts ( id integer DEFAULT nextval('hosts_id_seq'::text) NOT NULL, name varchar(255) DEFAULT '' NOT NULL, description text DEFAULT '' NOT NULL, lastreload bigint DEFAULT 0 NOT NULL, reload smallint DEFAULT 0 NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); /* --------------------------------------------------- Structure of table "invprojects" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS invprojects_id_seq; CREATE SEQUENCE invprojects_id_seq; DROP TABLE IF EXISTS invprojects CASCADE; CREATE TABLE invprojects ( id integer DEFAULT nextval('invprojects_id_seq'::text) NOT NULL, name varchar(255) NOT NULL, type smallint DEFAULT 0, divisionid integer DEFAULT NULL REFERENCES divisions (id) ON DELETE SET NULL ON UPDATE CASCADE, cdate bigint DEFAULT NULL, PRIMARY KEY(id) ); /* -------------------------------------------------------- Structure of table "netranges" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS netranges_id_seq; CREATE SEQUENCE netranges_id_seq; DROP TABLE IF EXISTS netranges; CREATE TABLE netranges ( id integer DEFAULT nextval('netranges_id_seq'::text) NOT NULL, buildingid integer NOT NULL CONSTRAINT netranges_buildingid_fkey REFERENCES location_buildings (id) ON DELETE CASCADE ON UPDATE CASCADE, linktype smallint NOT NULL, linktechnology smallint NOT NULL, downlink integer NOT NULL, uplink integer NOT NULL, type smallint NOT NULL, services smallint NOT NULL, foreignentity varchar(20) DEFAULT '' NOT NULL, invprojectid integer DEFAULT NULL CONSTRAINT netranges_invprojectid_fkey REFERENCES invprojects (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id) ); CREATE INDEX netranges_linktype_idx ON netranges (linktype); CREATE INDEX netranges_linktechnology_idx ON netranges (linktechnology); CREATE INDEX netranges_downlink_idx ON netranges (downlink); CREATE INDEX netranges_uplink_idx ON netranges (uplink); CREATE INDEX netranges_type_idx ON netranges (type); CREATE INDEX netranges_services_idx ON netranges (services); /* --------------------------------------------------- Structure of table "netnodes" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS netnodes_id_seq; CREATE SEQUENCE netnodes_id_seq; DROP TABLE IF EXISTS netnodes CASCADE; CREATE TABLE netnodes ( id integer DEFAULT nextval('netnodes_id_seq'::text) NOT NULL, name varchar(255) NOT NULL, type smallint DEFAULT 0, invprojectid integer REFERENCES invprojects (id) ON DELETE SET NULL ON UPDATE CASCADE, status smallint DEFAULT 0, longitude numeric(10,6) DEFAULT NULL, latitude numeric(10,6) DEFAULT NULL, ownership smallint DEFAULT 0, coowner varchar(255) DEFAULT '', uip smallint DEFAULT 0, miar smallint DEFAULT 0, createtime bigint, lastinspectiontime bigint DEFAULT NULL, admcontact text DEFAULT NULL, divisionid integer REFERENCES divisions (id) ON DELETE SET NULL ON UPDATE CASCADE, address_id integer REFERENCES addresses (id) ON DELETE SET NULL ON UPDATE CASCADE, info text DEFAULT NULL, ownerid integer DEFAULT NULL CONSTRAINT netnodes_ownerid_fkey REFERENCES customers (id) ON DELETE SET NULL ON UPDATE CASCADE, flags smallint NOT NULL DEFAULT 0, services varchar(100) NOT NULL DEFAULT '', PRIMARY KEY(id) ); /* -------------------------------------------------------- Structure of table "vlans" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS vlans_id_seq; CREATE SEQUENCE vlans_id_seq; DROP TABLE IF EXISTS vlans CASCADE; CREATE TABLE vlans ( id smallint DEFAULT nextval('vlans_id_seq'::text) NOT NULL, vlanid smallint NOT NULL, description varchar(254) DEFAULT NULL, customerid integer DEFAULT NULL CONSTRAINT vlans_customerid_fkey REFERENCES customers (id) ON DELETE SET NULL ON UPDATE CASCADE, netnodeid integer DEFAULT NULL CONSTRAINT vlans_netnodeid_fkey REFERENCES netnodes (id) ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY (id), CONSTRAINT vlans_customerid_ukey UNIQUE (vlanid, customerid), CONSTRAINT vlans_netnodeid_ukey UNIQUE (vlanid, netnodeid) ); /* -------------------------------------------------------- Structure of table "networks" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS networks_id_seq; CREATE SEQUENCE networks_id_seq; DROP TABLE IF EXISTS networks CASCADE; CREATE TABLE networks ( id integer DEFAULT nextval('networks_id_seq'::text) NOT NULL, name varchar(255) DEFAULT '' NOT NULL, address bigint DEFAULT 0 NOT NULL, snat bigint DEFAULT NULL, mask varchar(16) DEFAULT '' NOT NULL, interface varchar(16) DEFAULT '' NOT NULL, gateway varchar(16) DEFAULT '' NOT NULL, dns varchar(16) DEFAULT '' NOT NULL, dns2 varchar(16) DEFAULT '' NOT NULL, domain varchar(64) DEFAULT '' NOT NULL, wins varchar(16) DEFAULT '' NOT NULL, dhcpstart varchar(16) DEFAULT '' NOT NULL, dhcpend varchar(16) DEFAULT '' NOT NULL, disabled smallint DEFAULT 0 NOT NULL, notes text DEFAULT '' NOT NULL, vlanid smallint DEFAULT NULL CONSTRAINT networks_vlanid_fkey REFERENCES vlans (id) ON DELETE SET NULL ON UPDATE CASCADE, hostid integer NULL REFERENCES hosts (id) ON DELETE SET NULL ON UPDATE CASCADE, authtype smallint DEFAULT 0 NOT NULL, pubnetid integer DEFAULT NULL CONSTRAINT networks_pubnetid_fkey REFERENCES networks (id) ON DELETE SET NULL ON UPDATE CASCADE, allassignable smallint NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE (name), CONSTRAINT networks_address_key UNIQUE (address, hostid) ); CREATE INDEX networks_hostid_idx ON networks (hostid); /* --------------------------------------------------- Structure of table "netdeviceproducers" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS netdeviceproducers_id_seq; CREATE SEQUENCE netdeviceproducers_id_seq; DROP TABLE IF EXISTS netdeviceproducers CASCADE; CREATE TABLE netdeviceproducers ( id integer DEFAULT nextval('netdeviceproducers_id_seq'::text) NOT NULL, name varchar(255) NOT NULL, alternative_name VARCHAR(255), PRIMARY KEY (id), UNIQUE (name) ); /* --------------------------------------------------- Structure of table "netdevicetypes" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS netdevicetypes_id_seq; CREATE SEQUENCE netdevicetypes_id_seq; DROP TABLE IF EXISTS netdevicetypes CASCADE; CREATE TABLE netdevicetypes ( id integer DEFAULT nextval('netdevicetypes_id_seq'::text) NOT NULL, name varchar(50) NOT NULL, passive smallint NOT NULL DEFAULT 0, PRIMARY KEY (id), CONSTRAINT netdevicetypes_name_ukey UNIQUE (name) ); /* --------------------------------------------------- Structure of table "netdevicemodels" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS netdevicemodels_id_seq; CREATE SEQUENCE netdevicemodels_id_seq; DROP TABLE IF EXISTS netdevicemodels CASCADE; CREATE TABLE netdevicemodels ( id integer DEFAULT nextval('netdevicemodels_id_seq'::text) NOT NULL, netdeviceproducerid integer NOT NULL, name varchar(255) NOT NULL, alternative_name VARCHAR(255), type integer DEFAULT NULL CONSTRAINT netdevicemodels_type_fkey REFERENCES netdevicetypes (id) ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY (id), FOREIGN KEY (netdeviceproducerid) REFERENCES netdeviceproducers(id) ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE (name, netdeviceproducerid) ); /* --------------------------------------------------- Structure of table "netdevices" ----------------------------------------------------*/ DROP SEQUENCE IF EXISTS netdevices_id_seq; CREATE SEQUENCE netdevices_id_seq; DROP TABLE IF EXISTS netdevices CASCADE; CREATE TABLE netdevices ( id integer default nextval('netdevices_id_seq'::text) NOT NULL, name varchar(100) DEFAULT '' NOT NULL, description text DEFAULT '' NOT NULL, producer varchar(256) DEFAULT '' NOT NULL, model varchar(256) DEFAULT '' NOT NULL, serialnumber varchar(32) DEFAULT '' NOT NULL, ports integer DEFAULT 0 NOT NULL, purchasetime bigint DEFAULT 0 NOT NULL, guaranteeperiod smallint DEFAULT 0, shortname varchar(32) DEFAULT '' NOT NULL, nastype integer DEFAULT 0 NOT NULL, clients integer DEFAULT 0 NOT NULL, login varchar(60) DEFAULT '' NOT NULL, secret varchar(60) DEFAULT '' NOT NULL, community varchar(50) DEFAULT '' NOT NULL, channelid integer DEFAULT NULL REFERENCES ewx_channels (id) ON DELETE SET NULL ON UPDATE CASCADE, longitude numeric(10, 6) DEFAULT NULL, latitude numeric(10, 6) DEFAULT NULL, netnodeid integer DEFAULT NULL REFERENCES netnodes(id) ON DELETE SET NULL ON UPDATE CASCADE, invprojectid integer DEFAULT NULL REFERENCES invprojects(id) ON DELETE SET NULL ON UPDATE CASCADE, status smallint DEFAULT 0, netdevicemodelid integer DEFAULT NULL REFERENCES netdevicemodels (id) ON UPDATE CASCADE ON DELETE SET NULL, ownerid integer DEFAULT NULL CONSTRAINT netdevices_ownerid_fkey REFERENCES customers (id) ON UPDATE CASCADE ON DELETE SET NULL, address_id integer DEFAULT NULL CONSTRAINT netdevices_address_id_fkey REFERENCES addresses (id) ON UPDATE CASCADE ON DELETE SET NULL, divisionid integer DEFAULT NULL CONSTRAINT netdevices_divisionid_fkey REFERENCES divisions (id) ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY (id) ); CREATE INDEX netdevices_channelid_idx ON netdevices (channelid); /* --------------------------------------------------- Structure of table "netdevicemacs" ----------------------------------------------------*/ DROP SEQUENCE IF EXISTS netdevicemacs_id_seq; CREATE SEQUENCE netdevicemacs_id_seq; DROP TABLE IF EXISTS netdevicemacs CASCADE; CREATE TABLE netdevicemacs ( id integer DEFAULT nextval('netdevicemacs_id_seq'::text) NOT NULL, netdevid integer NOT NULL CONSTRAINT netdevicemacs_netdevid_fkey REFERENCES netdevices (id) ON DELETE CASCADE ON UPDATE CASCADE, label varchar(30) NOT NULL, mac varchar(17) NOT NULL, main smallint DEFAULT 0 NOT NULL, PRIMARY KEY (id), CONSTRAINT netdevicemacs_mac_ukey UNIQUE (mac), CONSTRAINT netdevicemacs_netdevid_label_ukey UNIQUE (netdevid, label) ); CREATE INDEX netdevicemacs_netdevid_idx ON netdevicemacs (netdevid); CREATE INDEX netdevicemacs_label_idx ON netdevicemacs (label); /* --------------------------------------------------- Structure of table "netradiosectors" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS netradiosectors_id_seq; CREATE SEQUENCE netradiosectors_id_seq; DROP TABLE IF EXISTS netradiosectors CASCADE; CREATE TABLE netradiosectors ( id integer DEFAULT nextval('netradiosectors_id_seq'::text) NOT NULL, name varchar(64) NOT NULL, type smallint DEFAULT 0 NOT NULL, azimuth numeric(9,2) DEFAULT 0 NOT NULL, width numeric(9,2) DEFAULT 0 NOT NULL, altitude smallint DEFAULT 0 NOT NULL, rsrange integer DEFAULT 0 NOT NULL, license varchar(64) DEFAULT NULL, technology integer DEFAULT NULL, frequency numeric(9,5) DEFAULT NULL, frequency2 numeric(9,5) DEFAULT NULL, bandwidth numeric(9,5) DEFAULT NULL, netdev integer NOT NULL REFERENCES netdevices (id) ON DELETE CASCADE ON UPDATE CASCADE, secret varchar(64) DEFAULT NULL, PRIMARY KEY (id), UNIQUE (name, netdev) ); /* -------------------------------------------------------- Structure of table "nodes" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS nodes_id_seq; CREATE SEQUENCE nodes_id_seq; DROP TABLE IF EXISTS nodes CASCADE; CREATE TABLE nodes ( id integer DEFAULT nextval('nodes_id_seq'::text) NOT NULL, name varchar(32) DEFAULT '' NOT NULL, login varchar(128) DEFAULT NULL, ipaddr bigint DEFAULT 0 NOT NULL, ipaddr_pub bigint DEFAULT 0 NOT NULL, passwd varchar(256) DEFAULT '' NOT NULL, ownerid integer DEFAULT NULL CONSTRAINT nodes_ownerid_fkey REFERENCES customers (id) ON DELETE CASCADE ON UPDATE CASCADE, netdev integer DEFAULT NULL CONSTRAINT nodes_netdev_fkey REFERENCES netdevices (id) ON DELETE SET NULL ON UPDATE CASCADE, linktype smallint DEFAULT NULL, linkradiosector integer DEFAULT NULL REFERENCES netradiosectors (id) ON DELETE SET NULL ON UPDATE CASCADE, linkspeed integer DEFAULT NULL, linktechnology integer DEFAULT NULL, port smallint DEFAULT 0 NOT NULL, creationdate bigint DEFAULT 0 NOT NULL, moddate bigint DEFAULT 0 NOT NULL, creatorid integer DEFAULT NULL CONSTRAINT nodes_creatorid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, modid integer DEFAULT NULL CONSTRAINT nodes_modid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, access smallint DEFAULT 1 NOT NULL, warning smallint DEFAULT 0 NOT NULL, authtype smallint DEFAULT 0 NOT NULL, chkmac smallint DEFAULT 1 NOT NULL, halfduplex smallint DEFAULT 0 NOT NULL, lastonline bigint DEFAULT 0 NOT NULL, info text DEFAULT '' NOT NULL, nas smallint DEFAULT 0 NOT NULL, longitude numeric(10, 6) DEFAULT NULL, latitude numeric(10, 6) DEFAULT NULL, netid integer DEFAULT 0 NOT NULL REFERENCES networks (id) ON DELETE CASCADE ON UPDATE CASCADE, invprojectid integer DEFAULT NULL REFERENCES invprojects(id) ON DELETE SET NULL ON UPDATE CASCADE, address_id integer DEFAULT NULL REFERENCES addresses(id) ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY (id), UNIQUE (name), UNIQUE (ipaddr, netid) ); CREATE INDEX nodes_netdev_idx ON nodes (netdev); CREATE INDEX nodes_ownerid_idx ON nodes (ownerid); CREATE INDEX nodes_ipaddr_pub_idx ON nodes (ipaddr_pub); CREATE INDEX nodes_linkradiosector_idx ON nodes (linkradiosector); CREATE INDEX nodes_authtype_idx ON nodes (authtype); /* -------------------------------------------------------- Structure of table "routednetworks" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS routednetworks_id_seq; CREATE SEQUENCE routednetworks_id_seq; DROP TABLE IF EXISTS routednetworks; CREATE TABLE routednetworks ( id integer DEFAULT nextval('routednetworks_id_seq'::text) NOT NULL, nodeid integer NOT NULL CONSTRAINT routednetworks_nodeid_fkey REFERENCES nodes (id) ON DELETE CASCADE ON UPDATE CASCADE, netid integer NOT NULL CONSTRAINT routednetworks_netid_fkey REFERENCES networks (id) ON DELETE CASCADE ON UPDATE CASCADE, comment varchar(256) DEFAULT NULL, PRIMARY KEY (id), CONSTRAINT routednetworks_netid_key UNIQUE (netid) ); /* --------------------------------------------------- Structure of table "ewx_stm_nodes" (EtherWerX(R)) ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS ewx_stm_nodes_id_seq; CREATE SEQUENCE ewx_stm_nodes_id_seq; DROP TABLE IF EXISTS ewx_stm_nodes CASCADE; CREATE TABLE ewx_stm_nodes ( id integer DEFAULT nextval('ewx_stm_nodes_id_seq'::text) NOT NULL, nodeid integer DEFAULT NULL CONSTRAINT ewx_stm_nodes_nodeid_fkey REFERENCES nodes (id) ON DELETE SET NULL ON UPDATE CASCADE, mac varchar(20) DEFAULT '' NOT NULL, ipaddr bigint DEFAULT 0 NOT NULL, channelid integer DEFAULT NULL CONSTRAINT ewx_stm_nodes_channelid_fkey REFERENCES ewx_stm_channels (id) ON DELETE SET NULL ON UPDATE CASCADE, uprate integer DEFAULT 0 NOT NULL, upceil integer DEFAULT 0 NOT NULL, downrate integer DEFAULT 0 NOT NULL, downceil integer DEFAULT 0 NOT NULL, halfduplex smallint DEFAULT 0 NOT NULL, PRIMARY KEY (id), UNIQUE (nodeid) ); /* ---------------------------------------------------- Structure of table "nodelocks" ---------------------------------------------------*/ DROP SEQUENCE IF EXISTS nodelocks_id_seq; CREATE SEQUENCE nodelocks_id_seq; DROP TABLE IF EXISTS nodelocks CASCADE; CREATE TABLE nodelocks ( id integer DEFAULT nextval('nodelocks_id_seq'::text) NOT NULL, nodeid integer NOT NULL REFERENCES nodes (id) ON DELETE CASCADE ON UPDATE CASCADE, days smallint DEFAULT 0 NOT NULL, fromsec integer DEFAULT 0 NOT NULL, tosec integer DEFAULT 0 NOT NULL, disabled smallint DEFAULT 0 NOT NULL, PRIMARY KEY (id) ); /* -------------------------------------------------------- Structure of table "macs" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS macs_id_seq; CREATE SEQUENCE macs_id_seq; DROP TABLE IF EXISTS macs CASCADE; CREATE TABLE macs ( id integer DEFAULT nextval('macs_id_seq'::text) NOT NULL, mac varchar(17) DEFAULT '' NOT NULL, nodeid integer NOT NULL REFERENCES nodes (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id), CONSTRAINT macs_mac_key UNIQUE (mac, nodeid) ); /* -------------------------------------------------------- Structure of table "nodegroups" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS nodegroups_id_seq; CREATE SEQUENCE nodegroups_id_seq; DROP TABLE IF EXISTS nodegroups CASCADE; CREATE TABLE nodegroups ( id integer NOT NULL DEFAULT nextval('nodegroups_id_seq'::text), name varchar(255) NOT NULL DEFAULT '', prio integer NOT NULL DEFAULT 0, description text NOT NULL DEFAULT '', PRIMARY KEY (id), UNIQUE (name) ); /* -------------------------------------------------------- Structure of table "nodegroupassignments" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS nodegroupassignments_id_seq; CREATE SEQUENCE nodegroupassignments_id_seq; DROP TABLE IF EXISTS nodegroupassignments CASCADE; CREATE TABLE nodegroupassignments ( id integer NOT NULL DEFAULT nextval('nodegroupassignments_id_seq'::text), nodegroupid integer NOT NULL CONSTRAINT nodegroupassignments_nodegroupid_fkey REFERENCES nodegroups (id) ON DELETE CASCADE ON UPDATE CASCADE, nodeid integer NOT NULL CONSTRAINT nodegroupassignments_nodeid_fkey REFERENCES nodes (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id), CONSTRAINT nodegroupassignments_nodeid_key UNIQUE (nodeid, nodegroupid) ); /* -------------------------------------------------------- Structure of table "nodeassignments" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS nodeassignments_id_seq; CREATE SEQUENCE nodeassignments_id_seq; DROP TABLE IF EXISTS nodeassignments CASCADE; CREATE TABLE nodeassignments ( id integer DEFAULT nextval('nodeassignments_id_seq'::text) NOT NULL, nodeid integer NOT NULL REFERENCES nodes (id) ON DELETE CASCADE ON UPDATE CASCADE, assignmentid integer NOT NULL REFERENCES assignments (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id), CONSTRAINT nodeassignments_nodeid_key UNIQUE (nodeid, assignmentid) ); CREATE INDEX nodeassignments_assignmentid_idx ON nodeassignments (assignmentid); /* -------------------------------------------------------- Structure of table "tariffstags" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS tarifftags_id_seq; CREATE SEQUENCE tarifftags_id_seq; DROP TABLE IF EXISTS tarifftags CASCADE; CREATE TABLE tarifftags ( id integer DEFAULT nextval('tarifftags_id_seq'::text) NOT NULL, name varchar(255) NOT NULL, description text NULL, PRIMARY KEY (id), UNIQUE (name) ); /* -------------------------------------------------------- Structure of table "tariffassignments" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS tariffassignments_id_seq; CREATE SEQUENCE tariffassignments_id_seq; DROP TABLE IF EXISTS tariffassignments CASCADE; CREATE TABLE tariffassignments ( id integer DEFAULT nextval('tariffassignments_id_seq'::text) NOT NULL, tariffid integer NOT NULL REFERENCES tariffs (id) ON DELETE CASCADE ON UPDATE CASCADE, tarifftagid integer NOT NULL REFERENCES tarifftags (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id), CONSTRAINT tariffassignments_tarifftagid_key UNIQUE (tariffid,tarifftagid) ); CREATE INDEX tariffassignments_tarifftagid_idx ON tariffassignments (tarifftagid); /* -------------------------------------------------------- Structure of table "tariffpricevariants" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS tariffpricevariants_id_seq; CREATE SEQUENCE tariffpricevariants_id_seq; DROP TABLE IF EXISTS tariffpricevariants CASCADE; CREATE TABLE tariffpricevariants ( id integer DEFAULT nextval('tariffpricevariants_id_seq'::text) NOT NULL, tariffid integer NOT NULL CONSTRAINT tariffpricevariants_tariffid_fkey REFERENCES tariffs (id) ON DELETE CASCADE ON UPDATE CASCADE, quantity_threshold integer NOT NULL, net_price numeric(9,3) DEFAULT 0 NOT NULL, gross_price numeric(9,3) DEFAULT 0 NOT NULL, PRIMARY KEY (id), CONSTRAINT tariffpricevariants_tariffid_quantity_threshold_ukey UNIQUE (tariffid, quantity_threshold), CONSTRAINT tariffpricevariants_quantity_threshold_ckey CHECK (quantity_threshold > 0) ); /* --------------------------------------------------------- Structure of table "payments" --------------------------------------------------------- */ DROP SEQUENCE IF EXISTS payments_id_seq; CREATE SEQUENCE payments_id_seq; DROP TABLE IF EXISTS payments CASCADE; CREATE TABLE payments ( id integer DEFAULT nextval('payments_id_seq'::text) NOT NULL, name varchar(255) DEFAULT '' NOT NULL, value numeric(9,2) DEFAULT 0 NOT NULL, creditor varchar(255) DEFAULT '' NOT NULL, period smallint DEFAULT 0 NOT NULL, at smallint DEFAULT 0 NOT NULL, description text DEFAULT '' NOT NULL, PRIMARY KEY (id) ); /* -------------------------------------------------------- Structure of table "numberplanassignments" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS numberplanassignments_id_seq; CREATE SEQUENCE numberplanassignments_id_seq; DROP TABLE IF EXISTS numberplanassignments CASCADE; CREATE TABLE numberplanassignments ( id integer DEFAULT nextval('numberplanassignments_id_seq'::text) NOT NULL, planid integer NOT NULL CONSTRAINT numberplanassignments_planid_fkey REFERENCES numberplans (id) ON DELETE CASCADE ON UPDATE CASCADE, divisionid integer NOT NULL CONSTRAINT numberplanassignments_divisionid_fkey REFERENCES divisions (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id), CONSTRAINT numberplanassignments_planid_key UNIQUE (planid, divisionid) ); CREATE INDEX numberplanassignments_divisionid_idx ON numberplanassignments (divisionid); /* -------------------------------------------------------- Structure of table "numberplanusers" -------------------------------------------------------- */ DROP TABLE IF EXISTS numberplanusers CASCADE; CREATE TABLE numberplanusers ( planid integer NOT NULL CONSTRAINT numberplanusers_planid_fkey REFERENCES numberplans (id) ON DELETE CASCADE ON UPDATE CASCADE, userid integer NOT NULL CONSTRAINT numberplanusers_userid_fkey REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT numberplanusers_userid_ukey UNIQUE (planid, userid) ); CREATE INDEX numberplanusers_userid_idx ON numberplanusers (userid); /* -------------------------------------------------------- Structure of table "customergroups" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS customergroups_id_seq; CREATE SEQUENCE customergroups_id_seq; DROP TABLE IF EXISTS customergroups CASCADE; CREATE TABLE customergroups ( id integer DEFAULT nextval('customergroups_id_seq'::text) NOT NULL, name varchar(255) DEFAULT '' NOT NULL, description text DEFAULT '' NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); /* -------------------------------------------------------- Structure of table "customerassignments" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS customerassignments_id_seq; CREATE SEQUENCE customerassignments_id_seq; DROP TABLE IF EXISTS customerassignments CASCADE; CREATE TABLE customerassignments ( id integer DEFAULT nextval('customerassignments_id_seq'::text) NOT NULL, customergroupid integer NOT NULL REFERENCES customergroups (id) ON DELETE CASCADE ON UPDATE CASCADE, customerid integer NOT NULL CONSTRAINT customerassignments_customerid_fkey REFERENCES customers (id) ON DELETE CASCADE ON UPDATE CASCADE, startdate bigint DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::bigint NOT NULL, enddate bigint DEFAULT 0 NOT NULL, PRIMARY KEY (id), CONSTRAINT customerassignments_customergroupid_ukey UNIQUE (customergroupid, customerid, enddate) ); CREATE INDEX customerassignments_customerid_idx ON customerassignments (customerid); CREATE INDEX customerassignments_startdate_idx ON customerassignments (startdate); CREATE INDEX customerassignments_enddate_idx ON customerassignments (enddate); /* -------------------------------------------------------- Structure of table "nodesessions" -------------------------------------------------------- */ DROP SEQUENCE IF EXISTS nodesessions_id_seq; CREATE SEQUENCE nodesessions_id_seq; DROP TABLE IF EXISTS nodesessions CASCADE; CREATE TABLE nodesessions ( id integer DEFAULT nextval('nodesessions_id_seq'::text) NOT NULL, customerid integer DEFAULT NULL CONSTRAINT nodesessions_customerid_fkey REFERENCES customers (id) ON DELETE SET NULL ON UPDATE CASCADE, nodeid integer DEFAULT NULL CONSTRAINT nodesessions_nodeid_fkey REFERENCES nodes (id) ON DELETE SET NULL ON UPDATE CASCADE, ipaddr bigint DEFAULT 0 NOT NULL, mac varchar(17) DEFAULT '' NOT NULL, start bigint DEFAULT 0 NOT NULL, stop bigint DEFAULT 0 NOT NULL, download bigint DEFAULT 0, upload bigint DEFAULT 0, tag varchar(32) DEFAULT '' NOT NULL, terminatecause varchar(32) DEFAULT '' NOT NULL, type smallint DEFAULT 0 NOT NULL, nasipaddr bigint DEFAULT NULL, nasport text DEFAULT NULL, nasid text DEFAULT NULL, location text DEFAULT NULL, PRIMARY KEY (id) ); CREATE INDEX nodesessions_customerid_idx ON nodesessions(customerid); CREATE INDEX nodesessions_nodeid_idx ON nodesessions(nodeid); CREATE INDEX nodesessions_tag_idx ON nodesessions(tag); /* -------------------------------------------------------- Structure of table "stats" -------------------------------------------------------- */ DROP TABLE IF EXISTS stats CASCADE; CREATE TABLE stats ( nodeid integer DEFAULT NULL CONSTRAINT stats_nodeid_fkey REFERENCES nodes (id) ON DELETE SET NULL ON UPDATE CASCADE, dt bigint DEFAULT 0 NOT NULL, upload bigint DEFAULT 0, download bigint DEFAULT 0, nodesessionid integer DEFAULT NULL CONSTRAINT stats_nodesessionid_fkey REFERENCES nodesessions (id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT stats_nodeid_dt_key UNIQUE (nodeid, dt) ); CREATE INDEX stats_dt_idx ON stats(dt); CREATE INDEX stats_nodesessionid_idx ON stats(nodesessionid); /* --------------------------------------------------- Structure of table "netlinks" ----------------------------------------------------*/ DROP SEQUENCE IF EXISTS netlinks_id_seq; CREATE SEQUENCE netlinks_id_seq; DROP TABLE IF EXISTS netlinks CASCADE; CREATE TABLE netlinks ( id integer default nextval('netlinks_id_seq'::text) NOT NULL, src integer NOT NULL CONSTRAINT netlinks_src_fkey REFERENCES netdevices (id) ON DELETE CASCADE ON UPDATE CASCADE, dst integer NOT NULL CONSTRAINT netlinks_dst_fkey REFERENCES netdevices (id) ON DELETE CASCADE ON UPDATE CASCADE, type smallint DEFAULT NULL, speed integer DEFAULT NULL, technology integer DEFAULT NULL, srcport smallint DEFAULT 0 NOT NULL, dstport smallint DEFAULT 0 NOT NULL, routetype smallint DEFAULT NULL, linecount smallint DEFAULT NULL, usedlines smallint DEFAULT NULL, availablelines smallint DEFAULT NULL, srcradiosector integer DEFAULT NULL REFERENCES netradiosectors (id) ON DELETE SET NULL ON UPDATE CASCADE, dstradiosector integer DEFAULT NULL REFERENCES netradiosectors (id) ON DELETE SET NULL ON UPDATE CASCADE, foreignentity varchar(255) DEFAULT NULL, PRIMARY KEY (id), CONSTRAINT netlinks_src_key UNIQUE (src, dst) ); CREATE INDEX netlinks_srcradiosector_idx ON netlinks (srcradiosector); CREATE INDEX netlinks_dstradiosector_idx ON netlinks (dstradiosector); DROP SEQUENCE IF EXISTS netlinkpoints_id_seq; CREATE SEQUENCE netlinkpoints_id_seq; DROP TABLE IF EXISTS netlinkpoints CASCADE; CREATE TABLE netlinkpoints ( id integer DEFAULT nextval('netlinkpoints_id_seq'::text) NOT NULL, longitude numeric(10, 6) DEFAULT NULL, latitude numeric(10, 6) DEFAULT NULL, PRIMARY KEY (id), netlinkid integer NOT NULL CONSTRAINT netlinkpoints_netlinkid_fkey REFERENCES netlinks (id) ON UPDATE CASCADE ON DELETE CASCADE ); /* --------------------------------------------------- Functions for network address translations ------------------------------------------------------*/ CREATE OR REPLACE FUNCTION inet_ntoa(bigint) RETURNS text AS $$ SELECT ($1/(256*256*256))::text ||'.'|| ($1/(256*256) - $1/(256*256*256)*256)::text ||'.'|| ($1/256 - $1/(256*256)*256)::text ||'.'|| ($1 - $1/256*256)::text; $$ LANGUAGE SQL IMMUTABLE; CREATE OR REPLACE FUNCTION inet_aton(text) RETURNS bigint AS $$ SELECT split_part($1,'.',1)::int8*(256*256*256)+ split_part($1,'.',2)::int8*(256*256)+ split_part($1,'.',3)::int8*256+ split_part($1,'.',4)::int8; $$ LANGUAGE SQL IMMUTABLE; CREATE OR REPLACE FUNCTION mask2prefix(bigint) RETURNS smallint AS $$ SELECT length(replace(ltrim(textin(bit_out($1::bit(32))), '0'), '0', ''))::smallint; $$ LANGUAGE SQL IMMUTABLE; CREATE OR REPLACE FUNCTION broadcast(bigint, bigint) RETURNS bigint AS $$ SELECT ($1::bit(32) | ~($2::bit(32)))::bigint; $$ LANGUAGE SQL IMMUTABLE; /* -------------------------------------------------- Tables for RT (Helpdesk) -----------------------------------------------------*/ DROP SEQUENCE IF EXISTS rtqueues_id_seq; CREATE SEQUENCE rtqueues_id_seq; DROP TABLE IF EXISTS rtqueues CASCADE; CREATE TABLE rtqueues ( id integer default nextval('rtqueues_id_seq'::text) NOT NULL, name varchar(255) DEFAULT '' NOT NULL, email varchar(255) DEFAULT '' NOT NULL, description text DEFAULT '' NOT NULL, newticketsubject varchar(255) NOT NULL DEFAULT '', newticketbody text NOT NULL DEFAULT '', newmessagesubject varchar(255) NOT NULL DEFAULT '', newmessagebody text NOT NULL DEFAULT '', resolveticketsubject varchar(255) NOT NULL DEFAULT '', resolveticketbody text NOT NULL DEFAULT '', verifierticketsubject varchar(255) NOT NULL DEFAULT '', verifierticketbody text NOT NULL DEFAULT '', newticketsmsbody text DEFAULT NULL, newmessagesmsbody text DEFAULT NULL, resolveticketsmsbody text DEFAULT NULL, deleted smallint DEFAULT 0 NOT NULL, deltime bigint DEFAULT 0 NOT NULL, deluserid integer DEFAULT NULL CONSTRAINT rtqueues_deluserid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, verifierid integer DEFAULT NULL CONSTRAINT rtqueues_verifierid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, uiconfigsection varchar(64) DEFAULT NULL, PRIMARY KEY (id), UNIQUE (name) ); DROP SEQUENCE IF EXISTS rttickets_id_seq; CREATE SEQUENCE rttickets_id_seq; DROP TABLE IF EXISTS rttickets CASCADE; CREATE TABLE rttickets ( id integer default nextval('rttickets_id_seq'::text) NOT NULL, queueid integer NOT NULL REFERENCES rtqueues (id) ON DELETE CASCADE ON UPDATE CASCADE, requestor varchar(255) DEFAULT NULL, requestor_mail varchar(255) DEFAULT NULL, requestor_phone varchar(32) DEFAULT NULL, requestor_userid integer DEFAULT NULL CONSTRAINT rttickets_requestor_userid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, subject varchar(255) DEFAULT '' NOT NULL, state smallint DEFAULT 0 NOT NULL, cause smallint DEFAULT 0 NOT NULL, owner integer DEFAULT NULL CONSTRAINT rttickets_owner_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, customerid integer DEFAULT NULL CONSTRAINT rttickets_customerid_fkey REFERENCES customers (id) ON DELETE SET NULL ON UPDATE CASCADE, creatorid integer DEFAULT NULL CONSTRAINT rttickets_creatorid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, createtime bigint DEFAULT 0 NOT NULL, customcreatetime bigint DEFAULT NULL, resolvetime bigint DEFAULT 0 NOT NULL, customresolvetime bigint DEFAULT NULL, modtime bigint NOT NULL DEFAULT 0, source smallint DEFAULT 0 NOT NULL, priority smallint DEFAULT NULL, deleted smallint DEFAULT 0 NOT NULL, deltime bigint DEFAULT 0 NOT NULL, deluserid integer DEFAULT NULL CONSTRAINT rttickets_deluserid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, address_id integer DEFAULT NULL CONSTRAINT rttickets_address_id_fkey REFERENCES addresses (id) ON UPDATE CASCADE ON DELETE SET NULL, nodeid integer DEFAULT NULL CONSTRAINT rttickets_nodeid_fkey REFERENCES nodes (id) ON UPDATE CASCADE ON DELETE SET NULL, netnodeid integer DEFAULT NULL, netdevid integer DEFAULT NULL CONSTRAINT rttickets_netdevid_fkey REFERENCES netdevices (id) ON UPDATE CASCADE ON DELETE SET NULL, verifierid integer DEFAULT NULL CONSTRAINT rttickets_verifierid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, verifier_rtime bigint DEFAULT NULL, deadline bigint DEFAULT NULL, service integer DEFAULT NULL, type integer DEFAULT NULL, parentid integer DEFAULT NULL CONSTRAINT rttickets_parentid_fkey REFERENCES rttickets (id) ON DELETE SET NULL ON UPDATE CASCADE, invprojectid integer DEFAULT NULL CONSTRAINT rttickets_invprojectid_fkey REFERENCES invprojects (id) ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY (id) ); CREATE INDEX rttickets_queueid_idx ON rttickets (queueid); CREATE INDEX rttickets_customerid_idx ON rttickets (customerid); CREATE INDEX rttickets_creatorid_idx ON rttickets (creatorid); CREATE INDEX rttickets_createtime_idx ON rttickets (createtime); DROP TABLE IF EXISTS rtticketlastview CASCADE; CREATE TABLE rtticketlastview ( ticketid integer NOT NULL CONSTRAINT rtticketlastview_ticketid_fkey REFERENCES rttickets (id) ON UPDATE CASCADE ON DELETE CASCADE, userid integer NOT NULL CONSTRAINT rtticketlastview_userid_fkey REFERENCES users (id) ON UPDATE CASCADE ON DELETE CASCADE, vdate bigint NOT NULL, CONSTRAINT rtticketlastview_ticketid_key UNIQUE (ticketid, userid) ); CREATE INDEX rtticketlastview_vdate_idx ON rtticketlastview (vdate); DROP SEQUENCE IF EXISTS rtmessages_id_seq; CREATE SEQUENCE rtmessages_id_seq; DROP TABLE IF EXISTS rtmessages CASCADE; CREATE TABLE rtmessages ( id integer default nextval('rtmessages_id_seq'::text) NOT NULL, ticketid integer NOT NULL REFERENCES rttickets (id) ON DELETE CASCADE ON UPDATE CASCADE, userid integer DEFAULT NULL CONSTRAINT rtmessages_userid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, customerid integer DEFAULT NULL CONSTRAINT rtmessages_customerid_fkey REFERENCES customers (id) ON DELETE SET NULL ON UPDATE CASCADE, phonefrom varchar(20) DEFAULT '' NOT NULL, mailfrom varchar(255) DEFAULT '' NOT NULL, subject varchar(255) DEFAULT '' NOT NULL, messageid varchar(255) DEFAULT '' NOT NULL, inreplyto integer DEFAULT NULL CONSTRAINT rtmessages_inreplyto_fkey REFERENCES rtmessages (id) ON DELETE SET NULL ON UPDATE CASCADE, replyto text DEFAULT '' NOT NULL, headers text DEFAULT '' NOT NULL, body text DEFAULT '' NOT NULL, createtime integer DEFAULT 0 NOT NULL, type integer DEFAULT 0 NOT NULL, deleted smallint DEFAULT 0 NOT NULL, deltime integer DEFAULT 0 NOT NULL, deluserid integer DEFAULT NULL CONSTRAINT rtmessages_deluserid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, contenttype varchar(255) DEFAULT 'text/plain', extid varchar(64) DEFAULT NULL, PRIMARY KEY (id) ); CREATE INDEX rtmessages_ticketid_idx ON rtmessages (ticketid); DROP SEQUENCE IF EXISTS rtrights_id_seq; CREATE SEQUENCE rtrights_id_seq; DROP TABLE IF EXISTS rtrights CASCADE; CREATE TABLE rtrights ( id integer DEFAULT nextval('rtrights_id_seq'::text) NOT NULL, userid integer NOT NULL REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE, queueid integer NOT NULL REFERENCES rtqueues (id) ON DELETE CASCADE ON UPDATE CASCADE, rights integer DEFAULT 0 NOT NULL, PRIMARY KEY (id), CONSTRAINT rtrights_userid_key UNIQUE (userid, queueid) ); DROP TABLE IF EXISTS rtattachments CASCADE; CREATE TABLE rtattachments ( messageid integer NOT NULL REFERENCES rtmessages (id) ON DELETE CASCADE ON UPDATE CASCADE, filename varchar(255) DEFAULT '' NOT NULL, contenttype varchar(255) DEFAULT '' NOT NULL, cid varchar(255) DEFAULT NULL ); CREATE INDEX rtattachments_message_idx ON rtattachments (messageid); DROP SEQUENCE IF EXISTS rtcategories_id_seq; CREATE SEQUENCE rtcategories_id_seq; DROP TABLE IF EXISTS rtcategories CASCADE; CREATE TABLE rtcategories ( id integer DEFAULT nextval('rtcategories_id_seq'::text) NOT NULL, name varchar(255) DEFAULT '' NOT NULL, description text DEFAULT '' NOT NULL, style text DEFAULT '', PRIMARY KEY (id), UNIQUE (name) ); DROP SEQUENCE IF EXISTS rtcategoryusers_id_seq; CREATE SEQUENCE rtcategoryusers_id_seq; DROP TABLE IF EXISTS rtcategoryusers CASCADE; CREATE TABLE rtcategoryusers ( id integer DEFAULT nextval('rtcategoryusers_id_seq'::text) NOT NULL, userid integer NOT NULL REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE, categoryid integer NOT NULL REFERENCES rtcategories (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id), CONSTRAINT rtcategories_userid_key UNIQUE (userid, categoryid) ); DROP SEQUENCE IF EXISTS rtticketcategories_id_seq; CREATE SEQUENCE rtticketcategories_id_seq; DROP TABLE IF EXISTS rtticketcategories CASCADE; CREATE TABLE rtticketcategories ( id integer DEFAUlT nextval('rtticketcategories_id_seq'::text) NOT NULL, ticketid integer NOT NULL REFERENCES rttickets (id) ON DELETE CASCADE ON UPDATE CASCADE, categoryid integer NOT NULL REFERENCES rtcategories (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id), CONSTRAINT rtticketcategories_ticketid_key UNIQUE (ticketid, categoryid) ); DROP SEQUENCE IF EXISTS rtqueuecategories_id_seq; CREATE SEQUENCE rtqueuecategories_id_seq; DROP TABLE IF EXISTS rtqueuecategories CASCADE; CREATE TABLE rtqueuecategories ( id integer DEFAULT nextval('rtqueuecategories_id_seq'::text) NOT NULL, queueid integer NOT NULL REFERENCES rtqueues (id) ON DELETE CASCADE ON UPDATE CASCADE, categoryid integer NOT NULL REFERENCES rtcategories (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id) ); /* --------------------------------------------------- Structure of table "rtticketwatchers" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS rtticketwatchers_id_seq; CREATE SEQUENCE rtticketwatchers_id_seq; DROP TABLE IF EXISTS rtticketwatchers CASCADE; CREATE TABLE rtticketwatchers ( id integer DEFAUlT nextval('rtticketwatchers_id_seq'::text) NOT NULL, ticketid integer NOT NULL CONSTRAINT rtticketwatchers_rttickets_fkey REFERENCES rttickets (id) ON DELETE CASCADE ON UPDATE CASCADE, userid integer NOT NULL CONSTRAINT rtticketwatchers_users_fkey REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id), CONSTRAINT rtticketwatchers_ticketid_ukey UNIQUE (ticketid, userid) ); /* --------------------------------------------------- Structure of table "domains" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS domains_id_seq; CREATE SEQUENCE domains_id_seq; DROP TABLE IF EXISTS domains CASCADE; CREATE TABLE domains ( id integer DEFAULT nextval('domains_id_seq'::text) NOT NULL, ownerid integer DEFAULT NULL CONSTRAINT domains_ownerid_fkey REFERENCES customers (id) ON DELETE SET NULL ON UPDATE CASCADE, name varchar(255) DEFAULT '' NOT NULL, description text DEFAULT '' NOT NULL, master varchar(128) DEFAULT NULL, last_check integer DEFAULT NULL, type text DEFAULT '' NOT NULL, notified_serial bigint DEFAULT NULL, account varchar(40) DEFAULT NULL, mxbackup smallint DEFAULT 0 NOT NULL, options text DEFAULT NULL, catalog text DEFAULT NULL, PRIMARY KEY (id), UNIQUE (name), CONSTRAINT domains_name_check CHECK (((name)::text = LOWER((name)::text))) ); CREATE INDEX domains_ownerid_idx ON domains (ownerid); CREATE INDEX domains_catalog_idx ON domains(catalog); /* --------------------------------------------------- Structure of table "passwd" (accounts) ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS passwd_id_seq; CREATE SEQUENCE passwd_id_seq; DROP TABLE IF EXISTS passwd CASCADE; CREATE TABLE passwd ( id integer DEFAULT nextval('passwd_id_seq'::text) NOT NULL, ownerid integer DEFAULT NULL CONSTRAINT passwd_ownerid_fkey REFERENCES customers (id) ON DELETE SET NULL ON UPDATE CASCADE, login varchar(200) DEFAULT '' NOT NULL, password varchar(200) DEFAULT '' NOT NULL, lastlogin bigint DEFAULT 0 NOT NULL, uid integer DEFAULT 0 NOT NULL, home varchar(255) DEFAULT '' NOT NULL, type smallint DEFAULT 0 NOT NULL, expdate bigint DEFAULT 0 NOT NULL, domainid integer NOT NULL CONSTRAINT passwd_domainid_fkey REFERENCES domains (id) ON DELETE CASCADE ON UPDATE CASCADE, realname varchar(255) DEFAULT '' NOT NULL, createtime bigint DEFAULT 0 NOT NULL, quota_sh integer DEFAULT 0 NOT NULL, quota_mail integer DEFAULT 0 NOT NULL, quota_www integer DEFAULT 0 NOT NULL, quota_ftp integer DEFAULT 0 NOT NULL, quota_sql integer DEFAULT 0 NOT NULL, quota_cloud integer DEFAULT 0 NOT NULL, mail_forward varchar(255) DEFAULT '' NOT NULL, mail_bcc varchar(255) DEFAULT '' NOT NULL, description text DEFAULT '' NOT NULL, PRIMARY KEY (id), CONSTRAINT passwd_login_key UNIQUE (login, domainid) ); CREATE INDEX passwd_ownerid_idx ON passwd (ownerid); /* --------------------------------------------------- Structure of table "records" (DNS) ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS records_id_seq; CREATE SEQUENCE records_id_seq; DROP TABLE IF EXISTS records CASCADE; CREATE TABLE records ( id integer DEFAULT nextval('records_id_seq'::text) NOT NULL, domain_id integer DEFAULT NULL REFERENCES domains (id) ON DELETE CASCADE ON UPDATE CASCADE, name varchar(255) DEFAULT NULL, type varchar(10) DEFAULT NULL, content varchar(65535) DEFAULT NULL, ttl integer DEFAULT NULL, prio integer DEFAULT NULL, change_date integer DEFAULT NULL, disabled boolean DEFAULT '0', auth boolean DEFAULT '1', ordername varchar(255) DEFAULT NULL, PRIMARY KEY (id), CONSTRAINT records_name_check CHECK (((name)::text = LOWER((name)::text))) ); CREATE INDEX records_name_type_idx ON records (name, type, domain_id); CREATE INDEX records_domain_id_idx ON records (domain_id); CREATE INDEX records_name_idx ON records (name); CREATE INDEX records_domain_id_ordername_idx ON records (domain_id, ordername text_pattern_ops); /* --------------------------------------------------- Structure of table "domainmetadata" (DNS) ------------------------------------------------------*/ DROP TABLE IF EXISTS domainmetadata CASCADE; CREATE TABLE domainmetadata ( id SERIAL PRIMARY KEY, domain_id integer REFERENCES domains(id) ON DELETE CASCADE ON UPDATE CASCADE, kind varchar(32), content text ); CREATE INDEX domainidmetaindex ON domainmetadata (domain_id); /* --------------------------------------------------- Structure of table "supermasters" (DNS) ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS supermasters_id_seq; CREATE SEQUENCE supermasters_id_seq; DROP TABLE IF EXISTS supermasters CASCADE; CREATE TABLE supermasters ( id integer DEFAULT nextval('supermasters_id_seq'::text) NOT NULL, ip inet NOT NULL, nameserver varchar(255) NOT NULL, account varchar(40) DEFAULT NULL, PRIMARY KEY (id) ); /* --------------------------------------------------- Structure of table "comments" (DNS) ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS comments_id_seq; CREATE SEQUENCE comments_id_seq; DROP TABLE IF EXISTS comments CASCADE; CREATE TABLE comments ( id integer DEFAULT nextval('comments_id_seq'::text) NOT NULL, domain_id integer NOT NULL CONSTRAINT comments_domain_id_fkey REFERENCES domains (id) ON DELETE CASCADE ON UPDATE CASCADE, name varchar(255) NOT NULL, type varchar(10) NOT NULL, modified_at integer NOT NULL, account varchar(40) DEFAULT NULL, comment varchar(65535) NOT NULL, PRIMARY KEY (id), CONSTRAINT comments_lowercase_name CHECK (((name)::text = LOWER((name)::text))) ); CREATE INDEX comments_domain_id_idx ON comments (domain_id); CREATE INDEX comments_name_type_idx ON comments (name, type); CREATE INDEX comments_domain_id_modified_at_idx ON comments (domain_id, modified_at); /* --------------------------------------------------- Structure of table "cryptokeys" (DNS) ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS cryptokeys_id_seq; CREATE SEQUENCE cryptokeys_id_seq; DROP TABLE IF EXISTS cryptokeys CASCADE; CREATE TABLE cryptokeys ( id integer DEFAULT nextval('cryptokeys_id_seq'::text) NOT NULL, domain_id integer CONSTRAINT cryptokeys_domain_id_fkey REFERENCES domains (id) ON DELETE CASCADE ON UPDATE CASCADE, flags integer NOT NULL, active boolean, published boolean DEFAULT 't', content text, PRIMARY KEY (id) ); CREATE INDEX cryptokeys_domain_id_idx ON cryptokeys (domain_id); /* --------------------------------------------------- Structure of table "tsigkeys" (DNS) ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS tsigkeys_id_seq; CREATE SEQUENCE tsigkeys_id_seq; DROP TABLE IF EXISTS tsigkeys CASCADE; CREATE TABLE tsigkeys ( id integer DEFAULT nextval('tsigkeys_id_seq'::text) NOT NULL, name varchar(255), algorithm varchar(50), secret varchar(255), CONSTRAINT tsigkeys_lowercase_name CHECK (((name)::text = LOWER((name)::text))), CONSTRAINT tsigkeys_name_key UNIQUE (name, algorithm) ); /* --------------------------------------------------- Structure of table "aliases" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS aliases_id_seq; CREATE SEQUENCE aliases_id_seq; DROP TABLE IF EXISTS aliases CASCADE; CREATE TABLE aliases ( id integer DEFAULT nextval('aliases_id_seq'::text) NOT NULL, login varchar(255) DEFAULT '' NOT NULL, domainid integer NOT NULL CONSTRAINT aliases_domainid_fkey REFERENCES domains (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id), CONSTRAINT aliases_login_key UNIQUE (login, domainid) ); /* --------------------------------------------------- Structure of table "aliasassignments" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS aliasassignments_id_seq; CREATE SEQUENCE aliasassignments_id_seq; DROP TABLE IF EXISTS aliasassignments CASCADE; CREATE TABLE aliasassignments ( id integer DEFAULT nextval('aliasassignments_id_seq'::text) NOT NULL, aliasid integer NOT NULL CONSTRAINT aliasassignments_aliasid_fkey REFERENCES aliases (id) ON DELETE CASCADE ON UPDATE CASCADE, accountid integer DEFAULT NULL CONSTRAINT aliasassignments_accountid_fkey REFERENCES passwd (id) ON DELETE CASCADE ON UPDATE CASCADE, mail_forward varchar(255) DEFAULT '' NOT NULL, PRIMARY KEY (id), CONSTRAINT aliasassignments_aliasid_key UNIQUE (aliasid, accountid, mail_forward) ); /* --------------------------------------------------- LMS-UI Configuration table ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS uiconfig_id_seq; CREATE SEQUENCE uiconfig_id_seq; DROP TABLE IF EXISTS uiconfig CASCADE; CREATE TABLE uiconfig ( id integer DEFAULT nextval('uiconfig_id_seq'::text) NOT NULL, section varchar(64) NOT NULL DEFAULT '', var varchar(64) NOT NULL DEFAULT '', value text NOT NULL DEFAULT '', description text NOT NULL DEFAULT '', disabled smallint NOT NULL DEFAULT 0, type smallint NOT NULL DEFAULT 0, userid integer DEFAULT NULL CONSTRAINT uiconfig_userid_fkey REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE, divisionid integer DEFAULT NULL CONSTRAINT uiconfig_divisionid_fkey REFERENCES divisions (id) ON DELETE CASCADE ON UPDATE CASCADE, configid integer DEFAULT NULL CONSTRAINT uiconfig_configid_fkey REFERENCES uiconfig (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id), CONSTRAINT uiconfig_section_var_userid_divisionid_ukey UNIQUE (section, var, userid, divisionid) ); /* --------------------------------------------------- Structure of table "events" (Timetable) ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS events_id_seq CASCADE; CREATE SEQUENCE events_id_seq; DROP TABLE IF EXISTS events CASCADE; CREATE TABLE events ( id integer DEFAULT nextval('events_id_seq'::text) NOT NULL, title varchar(255) DEFAULT '' NOT NULL, description text DEFAULT '' NOT NULL, note text DEFAULT '' NOT NULL, date bigint DEFAULT 0 NOT NULL, begintime integer DEFAULT 0 NOT NULL, enddate bigint DEFAULT 0 NOT NULL, endtime integer DEFAULT 0 NOT NULL, userid integer DEFAULT NULL CONSTRAINT events_userid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, customerid integer DEFAULT NULL CONSTRAINT events_customerid_fkey REFERENCES customers (id) ON DELETE SET NULL ON UPDATE CASCADE, private smallint DEFAULT 0 NOT NULL, closed smallint DEFAULT 0 NOT NULL, closeddate bigint DEFAULT 0 NOT NULL, closeduserid integer DEFAULT NULL CONSTRAINT events_closeduserid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, creationdate bigint DEFAULT 0 NOT NULL, moddate bigint DEFAULT 0 NOT NULL, moduserid integer DEFAULT NULL CONSTRAINT events_moduserid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, type smallint DEFAULT 1 NOT NULL, nodeid integer DEFAULT NULL REFERENCES nodes (id) ON DELETE SET NULL ON UPDATE CASCADE, address_id integer DEFAULT NULL CONSTRAINT events_address_id_fkey REFERENCES addresses (id) ON UPDATE CASCADE ON DELETE SET NULL, ticketid integer DEFAULT NULL CONSTRAINT events_ticketid_fk REFERENCES rttickets (id) ON DELETE SET NULL ON UPDATE CASCADE, netnodeid integer DEFAULT NULL CONSTRAINT events_netnodeid_fkey REFERENCES netnodes (id) ON DELETE SET NULL ON UPDATE CASCADE, netdevid integer DEFAULT NULL CONSTRAINT events_netdevid_fkey REFERENCES netdevices (id) ON DELETE SET NULL ON UPDATE CASCADE, divisionid integer DEFAULT NULL CONSTRAINT events_divisionid_fkey REFERENCES divisions (id) ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY (id) ); CREATE INDEX events_date_idx ON events(date); CREATE INDEX events_nodeid_idx ON events(nodeid); CREATE INDEX events_netnodeid_idx ON events (netnodeid); CREATE INDEX events_netdevid_idx ON events (netdevid); CREATE INDEX events_divisionid_idx ON events (divisionid); /* --------------------------------------------------- Structure of table "events" (Timetable) ------------------------------------------------------*/ DROP TABLE IF EXISTS eventassignments CASCADE; CREATE TABLE eventassignments ( eventid integer NOT NULL CONSTRAINT eventassignments_eventid_fkey REFERENCES events (id) ON DELETE CASCADE ON UPDATE CASCADE, userid integer NOT NULL CONSTRAINT eventassignments_userid_fkey REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT eventassignments_eventid_key UNIQUE (eventid, userid) ); /* --------------------------------------------------- Structure of table "sessions" ------------------------------------------------------*/ DROP TABLE IF EXISTS sessions CASCADE; CREATE TABLE sessions ( id varchar(50) NOT NULL DEFAULT '', ctime bigint NOT NULL DEFAULT 0, mtime bigint NOT NULL DEFAULT 0, atime bigint NOT NULL DEFAULT 0, vdata text NOT NULL, content text NOT NULL, PRIMARY KEY (id) ); /* --------------------------------------------------- Structure of table "daemoninstances" (lmsd config) ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS daemoninstances_id_seq; CREATE SEQUENCE daemoninstances_id_seq; DROP TABLE IF EXISTS daemoninstances CASCADE; CREATE TABLE daemoninstances ( id integer DEFAULT nextval('daemoninstances_id_seq'::text) NOT NULL, name varchar(255) DEFAULT '' NOT NULL, hostid integer NOT NULL CONSTRAINT daemoninstances_hostid_fkey REFERENCES hosts (id) ON DELETE CASCADE ON UPDATE CASCADE, module varchar(255) DEFAULT '' NOT NULL, crontab varchar(255) DEFAULT '' NOT NULL, priority integer DEFAULT 0 NOT NULL, description text DEFAULT '' NOT NULL, disabled smallint DEFAULT 0 NOT NULL, PRIMARY KEY (id) ); /* --------------------------------------------------- Structure of table "daemonconfig" (lmsd config) ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS daemonconfig_id_seq; CREATE SEQUENCE daemonconfig_id_seq; DROP TABLE IF EXISTS daemonconfig CASCADE; CREATE TABLE daemonconfig ( id integer DEFAULT nextval('daemonconfig_id_seq'::text) NOT NULL, instanceid integer NOT NULL CONSTRAINT daemonconfig_instanceid_fkey REFERENCES daemoninstances (id) ON DELETE CASCADE ON UPDATE CASCADE, var varchar(64) DEFAULT '' NOT NULL, value text DEFAULT '' NOT NULL, description text DEFAULT '' NOT NULL, disabled smallint DEFAULT 0 NOT NULL, PRIMARY KEY (id), CONSTRAINT daemonconfig_instanceid_key UNIQUE(instanceid, var) ); /* --------------------------------------------------- Structure of table "docrights" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS docrights_id_seq; CREATE SEQUENCE docrights_id_seq; DROP TABLE IF EXISTS docrights CASCADE; CREATE TABLE docrights ( id integer DEFAULT nextval('docrights_id_seq'::text) NOT NULL, userid integer NOT NULL CONSTRAINT docrights_userid_fkey REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE, doctype integer DEFAULT 0 NOT NULL, rights integer DEFAULT 0 NOT NULL, PRIMARY KEY (id), CONSTRAINT docrights_userid_key UNIQUE (userid, doctype) ); /* --------------------------------------------------- Structure of table "cashrights" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS cashrights_id_seq; CREATE SEQUENCE cashrights_id_seq; DROP TABLE IF EXISTS cashrights CASCADE; CREATE TABLE cashrights ( id integer DEFAULT nextval('cashrights_id_seq'::text) NOT NULL, userid integer NOT NULL CONSTRAINT cashrights_userid_fkey REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE, regid integer NOT NULL CONSTRAINT cashrights_regid_fkey REFERENCES cashregs (id) ON DELETE CASCADE ON UPDATE CASCADE, rights integer DEFAULT 0 NOT NULL, PRIMARY KEY (id), CONSTRAINT cashrights_userid_key UNIQUE (userid, regid) ); /* --------------------------------------------------- Structure of table "cashreglog" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS cashreglog_id_seq; CREATE SEQUENCE cashreglog_id_seq; DROP TABLE IF EXISTS cashreglog CASCADE; CREATE TABLE cashreglog ( id integer DEFAULT nextval('cashreglog_id_seq'::text) NOT NULL, regid integer NOT NULL CONSTRAINT cashreglog_regid_fkey REFERENCES cashregs (id) ON DELETE CASCADE ON UPDATE CASCADE, userid integer DEFAULT NULL CONSTRAINT cashreglog_userid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, time bigint DEFAULT 0 NOT NULL, value numeric(9,2) DEFAULT 0 NOT NULL, snapshot numeric(9,2) DEFAULT 0 NOT NULL, description text DEFAULT '' NOT NULL, PRIMARY KEY (id), CONSTRAINT cashreglog_regid_key UNIQUE (regid, time) ); /* --------------------------------------------------- Structure of table "ewx_pt_config" (EtherWerX(R)) ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS ewx_pt_config_id_seq; CREATE SEQUENCE ewx_pt_config_id_seq; DROP TABLE IF EXISTS ewx_pt_config CASCADE; CREATE TABLE ewx_pt_config ( id integer DEFAULT nextval('ewx_pt_config_id_seq'::text) NOT NULL, nodeid integer DEFAULT NULL CONSTRAINT ewx_pt_config_nodeid_fkey REFERENCES nodes (id) ON DELETE SET NULL ON UPDATE CASCADE, name varchar(32) DEFAULT '' NOT NULL, mac varchar(20) DEFAULT '' NOT NULL, ipaddr bigint DEFAULT 0 NOT NULL, passwd varchar(32) DEFAULT '' NOT NULL, PRIMARY KEY (id), UNIQUE (nodeid) ); /* --------------------------------------------------- Structure of table "dbinfo" ------------------------------------------------------*/ DROP TABLE IF EXISTS dbinfo CASCADE; CREATE TABLE dbinfo ( keytype varchar(255) DEFAULT '' NOT NULL, keyvalue varchar(255) DEFAULT '' NOT NULL, PRIMARY KEY (keytype), CONSTRAINT dbinfo_keytype_ukey UNIQUE (keytype) ); /* --------------------------------------------------- Structure of table "customercontacts" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS customercontacts_id_seq; CREATE SEQUENCE customercontacts_id_seq; DROP TABLE IF EXISTS customercontacts CASCADE; CREATE TABLE customercontacts ( id integer DEFAULT nextval('customercontacts_id_seq'::text) NOT NULL, customerid integer NOT NULL CONSTRAINT customercontacts_customerid_fkey REFERENCES customers (id) ON DELETE CASCADE ON UPDATE CASCADE, name varchar(10000) NOT NULL DEFAULT '', contact varchar(255) NOT NULL DEFAULT '', type integer DEFAULT NULL, PRIMARY KEY (id) ); CREATE INDEX customercontacts_customerid_idx ON customercontacts (customerid); CREATE INDEX customercontacts_contact_idx ON customercontacts (contact); /* --------------------------------------------------- Structure of table "customercontactproperties" ------------------------------------------------------*/ DROP TABLE IF EXISTS customercontactproperties CASCADE; CREATE TABLE customercontactproperties ( contactid integer NOT NULL CONSTRAINT customercontactproperties_contactid_fkey REFERENCES customercontacts (id) ON DELETE CASCADE ON UPDATE CASCADE, name varchar(255) NOT NULL, value varchar(255) NOT NULL, CONSTRAINT customercontactproperties_contactid_name_ukey UNIQUE (contactid, name) ); CREATE INDEX customercontactproperties_name_idx ON customercontactproperties (name); CREATE INDEX customercontactproperties_value_idx ON customercontactproperties (value); /* --------------------------------------------------- Structure of table "excludedgroups" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS excludedgroups_id_seq; CREATE SEQUENCE excludedgroups_id_seq; DROP TABLE IF EXISTS excludedgroups CASCADE; CREATE TABLE excludedgroups ( id integer NOT NULL DEFAULT nextval('excludedgroups_id_seq'::text), customergroupid integer NOT NULL REFERENCES customergroups (id) ON DELETE CASCADE ON UPDATE CASCADE, userid integer NOT NULL CONSTRAINT excludedgroups_userid_fkey REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id), CONSTRAINT excludedgroups_userid_key UNIQUE (userid, customergroupid) ); CREATE INDEX excludedgroups_customergroupid_idx ON excludedgroups (customergroupid); /* --------------------------------------------------- Structure of table "messages" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS messages_id_seq; DROP TABLE IF EXISTS messages CASCADE; CREATE SEQUENCE messages_id_seq; CREATE TABLE messages ( id integer DEFAULT nextval('messages_id_seq'::text) NOT NULL, subject varchar(255) DEFAULT '' NOT NULL, body text DEFAULT '' NOT NULL, cdate integer DEFAULT 0 NOT NULL, type smallint DEFAULT 0 NOT NULL, userid integer DEFAULT NULL CONSTRAINT messages_userid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, sender varchar(255) DEFAULT NULL, contenttype varchar(255) DEFAULT 'text/plain', startdate bigint DEFAULT NULL, PRIMARY KEY (id) ); CREATE INDEX messages_cdate_idx ON messages (cdate, type); CREATE INDEX messages_userid_idx ON messages (userid); CREATE INDEX messages_startdate_idx ON messages (startdate); /* --------------------------------------------------- Structure of table "messageitems" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS messageitems_id_seq; DROP TABLE IF EXISTS messageitems CASCADE; CREATE SEQUENCE messageitems_id_seq; CREATE TABLE messageitems ( id integer DEFAULT nextval('messageitems_id_seq'::text) NOT NULL, messageid integer NOT NULL CONSTRAINT messageitems_messageid_fkey REFERENCES messages (id) ON DELETE CASCADE ON UPDATE CASCADE, customerid integer DEFAULT NULL CONSTRAINT messageitems_customerid_fkey REFERENCES customers (id) ON DELETE SET NULL ON UPDATE CASCADE, destination varchar(255) DEFAULT '' NOT NULL, lastdate bigint DEFAULT 0 NOT NULL, status smallint DEFAULT 0 NOT NULL, error text DEFAULT NULL, lastreaddate bigint DEFAULT 0 NOT NULL, externalmsgid varchar(64) DEFAULT NULL, subject varchar(255) DEFAULT NULL, body text DEFAULT NULL, attributes text DEFAULT NULL, attempts smallint DEFAULT NULL, PRIMARY KEY (id) ); CREATE INDEX messageitems_messageid_idx ON messageitems (messageid); CREATE INDEX messageitems_customerid_idx ON messageitems (customerid); CREATE INDEX messageitems_attempts_idx ON messageitems (attempts); /* --------------------------------------------------- Structure of table "nastypes" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS nastypes_id_seq; CREATE SEQUENCE nastypes_id_seq; DROP TABLE IF EXISTS nastypes CASCADE; CREATE TABLE nastypes ( id integer DEFAULT nextval('nastypes_id_seq'::text) NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); /* --------------------------------------------------- Structure of table "managementurls" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS managementurls_id_seq; CREATE SEQUENCE managementurls_id_seq; DROP TABLE IF EXISTS managementurls; CREATE TABLE managementurls ( id integer DEFAULT nextval('managementurls_id_seq'::text) NOT NULL, netdevid integer DEFAULT NULL REFERENCES netdevices (id) ON DELETE CASCADE ON UPDATE CASCADE, nodeid integer DEFAULT NULL REFERENCES nodes (id) ON DELETE CASCADE ON UPDATE CASCADE, url text DEFAULT '' NOT NULL, comment varchar(100) DEFAULT NULL, PRIMARY KEY (id) ); /* --------------------------------------------------- Structure of table "logtransactions" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS logtransactions_id_seq; CREATE SEQUENCE logtransactions_id_seq; DROP TABLE IF EXISTS logtransactions CASCADE; CREATE TABLE logtransactions ( id integer DEFAULT nextval('logtransactions_id_seq'::text) NOT NULL, userid integer DEFAULT NULL CONSTRAINT logtransactions REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, time bigint DEFAULT 0 NOT NULL, module varchar(50) DEFAULT '' NOT NULL, PRIMARY KEY (id) ); CREATE INDEX logtransactions_userid_idx ON logtransactions (userid); CREATE INDEX logtransactions_time_idx ON logtransactions (time); /* --------------------------------------------------- Structure of table "logmessages" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS logmessages_id_seq; CREATE SEQUENCE logmessages_id_seq; DROP TABLE IF EXISTS logmessages CASCADE; CREATE TABLE logmessages ( id integer DEFAULT nextval('logmessages_id_seq'::text) NOT NULL, transactionid integer NOT NULL REFERENCES logtransactions (id) ON DELETE CASCADE ON UPDATE CASCADE, resource integer DEFAULT 0 NOT NULL, operation integer DEFAULT 0 NOT NULL, PRIMARY KEY (id) ); CREATE INDEX logmessages_transactionid_idx ON logmessages (transactionid); CREATE INDEX logmessages_resource_idx ON logmessages (resource); CREATE INDEX logmessages_operation_idx ON logmessages (operation); /* --------------------------------------------------- Structure of table "logmessagekeys" ------------------------------------------------------*/ DROP TABLE IF EXISTS logmessagekeys; CREATE TABLE logmessagekeys ( logmessageid integer NOT NULL REFERENCES logmessages (id) ON DELETE CASCADE ON UPDATE CASCADE, name varchar(32) NOT NULL, value integer NOT NULL ); CREATE INDEX logmessagekeys_logmessageid_idx ON logmessagekeys (logmessageid); CREATE INDEX logmessagekeys_name_idx ON logmessagekeys (name); CREATE INDEX logmessagekeys_value_idx ON logmessagekeys (value); /* --------------------------------------------------- Structure of table "logmessagedata" ------------------------------------------------------*/ DROP TABLE IF EXISTS logmessagedata; CREATE TABLE logmessagedata ( logmessageid integer NOT NULL REFERENCES logmessages (id) ON DELETE CASCADE ON UPDATE CASCADE, name varchar(32) NOT NULL, value text DEFAULT '' ); CREATE INDEX logmessagedata_logmessageid_idx ON logmessagedata (logmessageid); CREATE INDEX logmessagedata_name_idx ON logmessagedata (name); /* --------------------------------------------------- Structure of table "templates" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS templates_id_seq; CREATE SEQUENCE templates_id_seq; DROP TABLE IF EXISTS templates CASCADE; CREATE TABLE templates ( id integer DEFAULT nextval('templates_id_seq'::text) NOT NULL, type smallint NOT NULL, name varchar(100) NOT NULL, subject varchar(255) DEFAULT '' NOT NULL, message text DEFAULT '' NOT NULL, contenttype varchar(255) DEFAULT NULL, PRIMARY KEY (id), UNIQUE (type, name) ); /* --------------------------------------------------- Structure of table "templateattachments" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS templateattachments_id_seq; CREATE SEQUENCE templateattachments_id_seq; DROP TABLE IF EXISTS templateattachments CASCADE; CREATE TABLE templateattachments ( id integer DEFAULT nextval('templateattachments_id_seq'::text) NOT NULL, templateid integer NOT NULL CONSTRAINT templateattachments_templateid_fkey REFERENCES templates (id) ON DELETE CASCADE ON UPDATE CASCADE, filename varchar(255) DEFAULT '' NOT NULL, contenttype varchar(255) DEFAULT '' NOT NULL, PRIMARY KEY (id) ); /* --------------------------------------------------- Structure of table "rttemplatetypes" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS rttemplatetypes_id_seq; CREATE SEQUENCE rttemplatetypes_id_seq; DROP TABLE IF EXISTS rttemplatetypes; CREATE TABLE rttemplatetypes ( id integer DEFAULT nextval('rttemplatetypes_id_seq'::text) NOT NULL, templateid integer NOT NULL CONSTRAINT rttemplatetypes_templateid_fkey REFERENCES templates (id) ON DELETE CASCADE ON UPDATE CASCADE, messagetype integer NOT NULL, PRIMARY KEY (id), CONSTRAINT rttemplatetypes_templateid_key UNIQUE (templateid, messagetype) ); /* --------------------------------------------------- Structure of table "rttemplatequeues" ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS rttemplatequeues_id_seq; CREATE SEQUENCE rttemplatequeues_id_seq; DROP TABLE IF EXISTS rttemplatequeues; CREATE TABLE rttemplatequeues ( id integer DEFAULT nextval('rttemplatequeues_id_seq'::text) NOT NULL, templateid integer NOT NULL CONSTRAINT rttemplatequeues_templateid_fkey REFERENCES templates (id) ON DELETE CASCADE ON UPDATE CASCADE, queueid integer NOT NULL CONSTRAINT rttemplatequeues_queueid_fkey REFERENCES rtqueues (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id), CONSTRAINT rttemplatequeues_templateid_key UNIQUE (templateid, queueid) ); /* --------------------------------------------------- Structure of table usergroups ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS usergroups_id_seq; CREATE SEQUENCE usergroups_id_seq; DROP TABLE IF EXISTS usergroups CASCADE; CREATE TABLE usergroups ( id integer DEFAULT nextval('usergroups_id_seq'::text) NOT NULL, name varchar(255) DEFAULT '' NOT NULL, description text DEFAULT '' NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); /* --------------------------------------------------- Structure of table userassignments ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS userassignments_id_seq; CREATE SEQUENCE userassignments_id_seq; DROP TABLE IF EXISTS userassignments CASCADE; CREATE TABLE userassignments ( id integer DEFAULT nextval('userassignments_id_seq'::text) NOT NULL, usergroupid integer NOT NULL REFERENCES usergroups (id) ON DELETE CASCADE ON UPDATE CASCADE, userid integer NOT NULL REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id), UNIQUE (usergroupid, userid) ); CREATE INDEX userassignments_userid_idx ON userassignments (userid); /* --------------------------------------------------- Structure of table userdivisions ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS userdivisions_id_seq; CREATE SEQUENCE userdivisions_id_seq; DROP TABLE IF EXISTS userdivisions CASCADE; CREATE TABLE userdivisions ( id integer DEFAULT nextval('userdivisions_id_seq'::text) NOT NULL, userid integer NOT NULL CONSTRAINT userdivisions_userid_fkey REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE, divisionid integer NOT NULL CONSTRAINT userdivisions_divisionid_fkey REFERENCES divisions (id) ON DELETE RESTRICT ON UPDATE CASCADE, PRIMARY KEY (id), CONSTRAINT userdivisions_userid_divisionid_ukey UNIQUE (userid, divisionid) ); DROP INDEX IF EXISTS userdivisions_userid_idx; CREATE INDEX userdivisions_userid_idx ON userdivisions (userid); /* --------------------------------------------------- Structure of table passwdhistory ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS passwdhistory_id_seq; CREATE SEQUENCE passwdhistory_id_seq; DROP TABLE IF EXISTS passwdhistory CASCADE; CREATE TABLE passwdhistory ( id integer DEFAULT nextval('passwdhistory_id_seq'::text) NOT NULL, userid integer NOT NULL REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE, hash varchar(255) DEFAULT '' NOT NULL, PRIMARY KEY (id) ); CREATE INDEX passwdhistory_userid_idx ON passwdhistory (userid); /* --------------------------------------------------- Structure of table filecontainers ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS filecontainers_id_seq; CREATE SEQUENCE filecontainers_id_seq; DROP TABLE IF EXISTS filecontainers CASCADE; CREATE TABLE filecontainers ( id integer DEFAULT nextval('filecontainers_id_seq'::text) NOT NULL, creationdate bigint NOT NULL DEFAULT 0, creatorid integer DEFAULT NULL CONSTRAINT filecontainers_creatorid_fkey REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE, description text NOT NULL, netdevid integer DEFAULT NULL CONSTRAINT filecontainers_netdevid_fkey REFERENCES netdevices (id) ON DELETE CASCADE ON UPDATE CASCADE, netnodeid integer DEFAULT NULL CONSTRAINT filecontainers_netnodeid_fkey REFERENCES netnodes (id) ON DELETE CASCADE ON UPDATE CASCADE, messageid integer DEFAULT NULL CONSTRAINT filecontainers_messageid_fkey REFERENCES messages (id) ON DELETE CASCADE ON UPDATE CASCADE, netdevmodelid integer DEFAULT NULL CONSTRAINT filecontainers_netdevmodelid_fkey REFERENCES netdevicemodels (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id) ); /* --------------------------------------------------- Structure of table files ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS files_id_seq; CREATE SEQUENCE files_id_seq; DROP TABLE IF EXISTS files CASCADE; CREATE TABLE files ( id integer DEFAULT nextval('files_id_seq'::text) NOT NULL, containerid integer NOT NULL CONSTRAINT files_containerid_fkey REFERENCES filecontainers (id) ON DELETE CASCADE ON UPDATE CASCADE, filename varchar(255) NOT NULL, contenttype varchar(255) NOT NULL, md5sum varchar(32) NOT NULL, PRIMARY KEY (id), CONSTRAINT files_containerid_key UNIQUE (containerid, md5sum) ); CREATE INDEX files_md5sum_idx ON files (md5sum); /* --------------------------------------------------- Structure of table ksefbatchsessions ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS ksefbatchsessions_id_seq; CREATE SEQUENCE ksefbatchsessions_id_seq; DROP TABLE IF EXISTS ksefbatchsessions CASCADE; CREATE TABLE ksefbatchsessions ( id integer DEFAULT nextval('ksefbatchsessions_id_seq'::text) NOT NULL, ksefnumber varchar(40) NOT NULL, cdate bigint NOT NULL, lastupdate bigint NOT NULL, status smallint NOT NULL DEFAULT 0, statusdescription text DEFAULT NULL, environment smallint NOT NULL DEFAULT 0, PRIMARY KEY (id) ); CREATE INDEX ksefbatchsessions_ksefnumber_idx ON ksefbatchsessions (ksefnumber); CREATE INDEX ksefbatchsessions_status_idx ON ksefbatchsessions (status); /* --------------------------------------------------- Structure of table ksefdocuments ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS ksefdocuments_id_seq; CREATE SEQUENCE ksefdocuments_id_seq; DROP TABLE IF EXISTS ksefdocuments CASCADE; CREATE TABLE ksefdocuments ( id integer DEFAULT nextval('ksefdocuments_id_seq'::text) NOT NULL, batchsessionid integer NOT NULL CONSTRAINT ksefbatchsessions_batchsessionid_fkey REFERENCES ksefbatchsessions (id) ON DELETE CASCADE ON UPDATE CASCADE, docid integer NOT NULL CONSTRAINT ksefdocuments_docid_fkey REFERENCES documents (id) ON DELETE RESTRICT ON UPDATE CASCADE, ordinalnumber integer NOT NULL, ksefnumber varchar(40) DEFAULT NULL, hash varchar(50) NOT NULL, status smallint NOT NULL DEFAULT 0, statusdescription text DEFAULT NULL, statusdetails text DEFAULT NULL, PRIMARY KEY (id) ); CREATE INDEX ksefdocuments_status_idx ON ksefdocuments (status); /* --------------------------------------------------- Structure of table ksefdelays ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS ksefdelays_id_seq; CREATE SEQUENCE ksefdelays_id_seq; DROP TABLE IF EXISTS ksefdelays CASCADE; CREATE TABLE ksefdelays ( id integer DEFAULT nextval('ksefdelays_id_seq'::text) NOT NULL, divisionid integer CONSTRAINT ksefdelays_divisionid_fkey REFERENCES divisions (id) ON DELETE CASCADE ON UPDATE CASCADE, delay integer NOT NULL, PRIMARY KEY (id) ); /* --------------------------------------------------- Structure of table ksefinvoices ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS ksefinvoices_id_seq; CREATE SEQUENCE ksefinvoices_id_seq; DROP TABLE IF EXISTS ksefinvoices CASCADE; CREATE TABLE ksefinvoices ( id integer DEFAULT nextval('ksefinvoices_id_seq'::text) NOT NULL, division_id integer NOT NULL CONSTRAINT ksefinvoices_division_id_fkey REFERENCES divisions (id) ON DELETE RESTRICT ON UPDATE CASCADE, issue_date bigint NOT NULL, permanent_storage_date timestamptz NOT NULL, ksef_number varchar(40) NOT NULL, invoice_number varchar(256) NOT NULL, corrected_ksef_number varchar(44) DEFAULT NULL, corrected_invoice_number varchar(256) DEFAULT NULL, corrected_invoice_issue_date bigint NULL, seller_ten varchar(10) NOT NULL, seller_name varchar(512) NOT NULL, buyer_identifier_type smallint NOT NULL, buyer_identifier_value varchar(50) NOT NULL, buyer_name varchar(512) NOT NULL, net_amount numeric(12,5) NOT NULL, gross_amount numeric(12,5) NOT NULL, vat_amount numeric(12,5) NOT NULL, currency varchar(3) NOT NULL, invoicing_mode smallint NOT NULL, invoice_type smallint NOT NULL, form_system_code varchar(15) NOT NULL, form_schema_version varchar(15) NOT NULL, form_value varchar(15) NOT NULL, invoice_hash varchar(44) NOT NULL, corrected_invoice_hash varchar(44) DEFAULT NULL, bank_account varchar(48) DEFAULT NULL, bank_name varchar(100) DEFAULT NULL, pay_date bigint DEFAULT NULL, pay_type smallint DEFAULT 0, buyer_id varchar(50) DEFAULT NULL, posting smallint DEFAULT 1, settled smallint DEFAULT 0, notes text DEFAULT NULL, PRIMARY KEY (id), CONSTRAINT ksefinvoices_ksef_number_ukey UNIQUE (ksef_number) ); CREATE INDEX ksefinvoices_issue_date_idx ON ksefinvoices (issue_date); CREATE INDEX ksefinvoices_permanent_storage_date_idx ON ksefinvoices (permanent_storage_date); CREATE INDEX ksefinvoices_ksef_number_idx ON ksefinvoices (ksef_number); CREATE INDEX ksefinvoices_corrected_ksef_number_idx ON ksefinvoices (corrected_ksef_number); CREATE INDEX ksefinvoices_seller_ten_idx ON ksefinvoices (seller_ten); CREATE INDEX ksefinvoices_buyer_identifier_value_idx ON ksefinvoices (buyer_identifier_value); CREATE INDEX ksefinvoices_invoice_type_idx ON ksefinvoices (invoice_type); /* --------------------------------------------------- Structure of table ksefinvoicethirdsubjects ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS ksefinvoicethirdsubjects_id_seq; CREATE SEQUENCE ksefinvoicethirdsubjects_id_seq; DROP TABLE IF EXISTS ksefinvoicethirdsubjects CASCADE; CREATE TABLE ksefinvoicethirdsubjects ( id integer DEFAULT nextval('ksefinvoicethirdsubjects_id_seq'::text) NOT NULL, ksefinvoiceid integer NOT NULL CONSTRAINT ksefinvoicethirdsubjects_ksefinvoiceid_fkey REFERENCES ksefinvoices (id) ON DELETE CASCADE ON UPDATE CASCADE, identifier_type smallint NOT NULL, identifier_value varchar(50) NOT NULL, name varchar(512) NOT NULL, role integer NOT NULL, PRIMARY KEY (id) ); CREATE INDEX ksefinvoicethirdsubjects_identifier_type_idx ON ksefinvoicethirdsubjects (identifier_type); CREATE INDEX ksefinvoicethirdsubjects_identifier_value_idx ON ksefinvoicethirdsubjects (identifier_value); CREATE INDEX ksefinvoicethirdsubjects_role_idx ON ksefinvoicethirdsubjects (role); /* --------------------------------------------------- Structure of table ksefinvoiceitems ------------------------------------------------------*/ CREATE TABLE ksefinvoiceitems ( ksef_invoice_id integer NOT NULL CONSTRAINT ksefinvoiceitems_ksef_invoice_id_fkey REFERENCES ksefinvoices (id) ON DELETE CASCADE ON UPDATE CASCADE, item_id smallint NOT NULL, name varchar(512) DEFAULT NULL, prod_id varchar(50) DEFAULT NULL, unit varchar(512) DEFAULT NULL, count numeric(22,6) DEFAULT NULL, net_flag smallint DEFAULT 1, price numeric(24,8) NOT NULL, value numeric(16,2) NOT NULL, tax_rate numeric(4,2), taxed smallint DEFAULT 1, reverse_charge smallint DEFAULT 0, eu smallint DEFAULT 0, export smallint DEFAULT 0, product_service_group smallint DEFAULT 0 ); /* --------------------------------------------------- Structure of table ksefinvoicetags ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS ksefinvoicetags_id_seq; CREATE SEQUENCE ksefinvoicetags_id_seq; DROP TABLE IF EXISTS ksefinvoicetags CASCADE; CREATE TABLE ksefinvoicetags ( id integer DEFAULT nextval('ksefinvoicetags_id_seq'::text) NOT NULL, name text NOT NULL, PRIMARY KEY (id) ); /* --------------------------------------------------- Structure of table ksefinvoicetagassignments ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS ksefinvoicetagassignments_id_seq; CREATE SEQUENCE ksefinvoicetagassignments_id_seq; DROP TABLE IF EXISTS ksefinvoicetagassignments CASCADE; CREATE TABLE ksefinvoicetagassignments ( id integer DEFAULT nextval('ksefinvoicetagassignments_id_seq'::text) NOT NULL, ksef_invoice_id integer NOT NULL CONSTRAINT ksefinvoicetagassignments_ksef_invoice_id_fkey REFERENCES ksefinvoices (id) ON DELETE CASCADE ON UPDATE CASCADE, ksef_invoice_tag_id integer NOT NULL CONSTRAINT ksefinvoicetagassignments_ksef_invoice_tag_id_fkey REFERENCES ksefinvoicetags (id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (id) ); /* --------------------------------------------------- Structure of table "up_rights" (Userpanel) ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS up_rights_id_seq; CREATE SEQUENCE up_rights_id_seq; DROP TABLE IF EXISTS up_rights CASCADE; CREATE TABLE up_rights ( id integer DEFAULT nextval('up_rights_id_seq'::text) NOT NULL, module varchar(255) DEFAULT 0 NOT NULL, name varchar(255) DEFAULT 0 NOT NULL, description varchar(255) DEFAULT 0, setdefault smallint DEFAULT 0, PRIMARY KEY (id) ); /* --------------------------------------------------- Structure of table "up_rights_assignments" (Userpanel) ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS up_rights_assignments_id_seq; CREATE SEQUENCE up_rights_assignments_id_seq; DROP TABLE IF EXISTS up_rights_assignments CASCADE; CREATE TABLE up_rights_assignments ( id integer DEFAULT nextval('up_rights_assignments_id_seq'::text) NOT NULL, customerid integer DEFAULT NULL CONSTRAINT up_rights_assignments_customerid_fkey REFERENCES customers (id) ON DELETE CASCADE ON UPDATE CASCADE, rightid integer DEFAULT 0 NOT NULL, PRIMARY KEY (id), CONSTRAINT up_rights_assignments_customerid_key UNIQUE (customerid, rightid) ); /* --------------------------------------------------- Structure of table "up_customers" (Userpanel) ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS up_customers_id_seq; CREATE SEQUENCE up_customers_id_seq; DROP TABLE IF EXISTS up_customers CASCADE; CREATE TABLE up_customers ( id integer DEFAULT nextval('up_customers_id_seq'::text) NOT NULL, customerid integer DEFAULT NULL CONSTRAINT up_customers_customerid_fkey REFERENCES customers (id) ON DELETE CASCADE ON UPDATE CASCADE, lastlogindate bigint DEFAULT 0 NOT NULL, lastloginip varchar(40) DEFAULT '' NOT NULL, failedlogindate bigint DEFAULT 0 NOT NULL, failedloginip varchar(40) DEFAULT '' NOT NULL, enabled smallint DEFAULT 0 NOT NULL, PRIMARY KEY (id) ); /* --------------------------------------------------- Structure of table "up_help" (Userpanel) ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS up_help_id_seq; CREATE SEQUENCE up_help_id_seq; DROP TABLE IF EXISTS up_help CASCADE; CREATE TABLE up_help ( id integer DEFAULT nextval('up_help_id_seq'::text) NOT NULL, reference integer DEFAULT NULL CONSTRAINT up_help_reference_fkey REFERENCES up_help (id) ON DELETE CASCADE ON UPDATE CASCADE, title varchar(128) DEFAULT 0 NOT NULL, body text DEFAULT '' NOT NULL, PRIMARY KEY (id) ); /* --------------------------------------------------- Structure of table "up_info_changes" (Userpanel) ------------------------------------------------------*/ DROP SEQUENCE IF EXISTS up_info_changes_id_seq; CREATE SEQUENCE up_info_changes_id_seq; DROP TABLE IF EXISTS up_info_changes CASCADE; CREATE TABLE up_info_changes ( id integer DEFAULT nextval('up_info_changes_id_seq'::text) NOT NULL, customerid integer DEFAULT NULL CONSTRAINT up_info_changes_customerid_fkey REFERENCES customers (id) ON DELETE CASCADE ON UPDATE CASCADE, fieldname varchar(255) DEFAULT 0 NOT NULL, fieldvalue varchar(255) DEFAULT 0 NOT NULL, cdate bigint DEFAULT NULL, PRIMARY KEY (id) ); /* --------------------------------------------------- Structure of table "up_sessions" ------------------------------------------------------*/ DROP TABLE IF EXISTS up_sessions CASCADE; CREATE TABLE up_sessions ( id varchar(50) NOT NULL DEFAULT '', customerid integer NOT NULL CONSTRAINT up_sessions_customerid_fkey REFERENCES customers (id) ON UPDATE CASCADE ON DELETE CASCADE, ctime bigint NOT NULL DEFAULT 0, mtime bigint NOT NULL DEFAULT 0, atime bigint NOT NULL DEFAULT 0, vdata text NOT NULL, content text NOT NULL, PRIMARY KEY (id) ); /* --------------------------------------------------- Functions and Views ------------------------------------------------------*/ CREATE OR REPLACE FUNCTION lms_current_user() RETURNS integer AS ' SELECT CASE WHEN current_setting(''lms.current_user'') = '''' THEN 0 ELSE current_setting(''lms.current_user'')::integer END ' LANGUAGE SQL; CREATE VIEW vcustomerassignments AS SELECT ca.* FROM customerassignments ca WHERE startdate <= EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::bigint AND enddate = 0; CREATE VIEW vaddresses AS SELECT a.*, c.ccode AS ccode, country_id AS countryid, city_id AS location_city, street_id AS location_street, house AS location_house, flat AS location_flat, (TRIM(both ' ' FROM (CASE WHEN street IS NOT NULL THEN street ELSE city END) || (CASE WHEN house is NOT NULL THEN (CASE WHEN flat IS NOT NULL THEN ' ' || house || '/' || flat ELSE ' ' || house END) ELSE (CASE WHEN flat IS NOT NULL THEN ' ' || flat ELSE '' END) END) )) AS address, (TRIM(both ' ' FROM (CASE WHEN zip IS NOT NULL THEN zip || ' ' ELSE '' END) || (CASE WHEN postoffice IS NOT NULL AND postoffice <> city THEN postoffice || ', ' ELSE '' END) || (CASE WHEN postoffice IS NULL OR postoffice = city OR street IS NOT NULL THEN city || ', ' ELSE '' END) || (CASE WHEN street IS NOT NULL THEN street ELSE city END) || (CASE WHEN house is NOT NULL THEN (CASE WHEN flat IS NOT NULL THEN ' ' || house || '/' || flat ELSE ' ' || house END) ELSE (CASE WHEN flat IS NOT NULL THEN ' ' || flat ELSE '' END) END) )) AS location FROM addresses a LEFT JOIN countries c ON c.id = a.country_id; /* --------------------------------------------------- Structure of view "vdivisions" ------------------------------------------------------*/ CREATE VIEW vdivisions AS SELECT d.*, a.country_id AS countryid, a.ccode, a.zip AS zip, a.city AS city, a.address, oa.country_id AS office_countryid, oa.ccode AS office_ccode, oa.zip AS office_zip, oa.city AS office_city, oa.address AS office_address, (CASE WHEN d.firstname IS NOT NULL AND d.lastname IS NOT NULL AND d.birthdate IS NOT NULL THEN 1 ELSE 0 END) AS naturalperson FROM divisions d JOIN vaddresses a ON a.id = d.address_id LEFT JOIN vaddresses oa ON oa.id = d.office_address_id; CREATE VIEW vnetworks AS SELECT h.name AS hostname, ne.*, no.ownerid, a.ccode, a.city_id as location_city, a.street_id as location_street, a.house as location_house, a.flat as location_flat, no.chkmac, inet_ntoa(ne.address) || '/' || mask2prefix(inet_aton(ne.mask)) AS ip, no.id AS nodeid, a.location FROM nodes no LEFT JOIN networks ne ON (ne.id = no.netid) LEFT JOIN hosts h ON (h.id = ne.hostid) LEFT JOIN vaddresses a ON no.address_id = a.id WHERE no.ipaddr = 0 AND no.ipaddr_pub = 0; CREATE VIEW customerconsentview AS SELECT c.id AS customerid, SUM(CASE WHEN cc.type = 1 THEN cc.cdate ELSE 0 END)::bigint AS consentdate, SUM(CASE WHEN cc.type = 2 THEN 1 ELSE 0 END)::smallint AS invoicenotice, SUM(CASE WHEN cc.type = 3 THEN 1 ELSE 0 END)::smallint AS mailingnotice, SUM(CASE WHEN cc.type = 8 THEN 1 ELSE 0 END)::smallint AS smsnotice, SUM(CASE WHEN cc.type = 4 THEN 1 ELSE 0 END)::smallint AS einvoice FROM customers c LEFT JOIN customerconsents cc ON cc.customerid = c.id GROUP BY c.id; CREATE VIEW customerview AS SELECT c.*, cc.consentdate AS consentdate, cc.invoicenotice AS invoicenotice, cc.mailingnotice AS mailingnotice, cc.smsnotice AS smsnotice, cc.einvoice AS einvoice, a1.country_id as countryid, a1.ccode, a1.zip as zip, a1.city as city, a1.street as street,a1.house as building, a1.flat as apartment, a2.country_id as post_countryid, a2.ccode AS post_ccode, a2.zip as post_zip, a2.city as post_city, a2.street as post_street, a2.name as post_name, a2.house as post_building, a2.flat as post_apartment, a1.address as address, a1.location AS full_address, a1.postoffice AS postoffice, a2.address as post_address, a2.location AS post_full_address, a2.postoffice AS post_postoffice, ce.extid AS extid FROM customers c JOIN customer_addresses ca1 ON c.id = ca1.customer_id AND ca1.type = 1 LEFT JOIN vaddresses a1 ON ca1.address_id = a1.id LEFT JOIN customer_addresses ca2 ON c.id = ca2.customer_id AND ca2.type = 0 LEFT JOIN vaddresses a2 ON ca2.address_id = a2.id LEFT JOIN customerconsentview cc ON cc.customerid = c.id LEFT JOIN customerextids ce ON ce.customerid = c.id AND ce.serviceproviderid IS NULL WHERE NOT EXISTS ( SELECT 1 FROM vcustomerassignments a JOIN excludedgroups e ON (a.customergroupid = e.customergroupid) WHERE e.userid = lms_current_user() AND a.customerid = c.id) AND (lms_current_user() = 0 OR c.divisionid IN ( SELECT ud.divisionid FROM userdivisions ud WHERE ud.userid = lms_current_user())) AND c.type < 2; CREATE VIEW contractorview AS SELECT c.*, cc.consentdate AS consentdate, cc.invoicenotice AS invoicenotice, cc.mailingnotice AS mailingnotice, cc.smsnotice AS smsnotice, cc.einvoice AS einvoice, a1.country_id as countryid, a1.ccode, a1.zip as zip, a1.city as city, a1.street as street, a1.house as building, a1.flat as apartment, a2.country_id as post_countryid, a2.ccode AS post_ccode, a2.zip as post_zip, a2.city as post_city, a2.street as post_street, a2.house as post_building, a2.flat as post_apartment, a2.name as post_name, a1.address as address, a1.location AS full_address, a1.postoffice AS postoffice, a2.address as post_address, a2.location AS post_full_address, a2.postoffice AS post_postoffice, ce.extid AS extid FROM customers c JOIN customer_addresses ca1 ON c.id = ca1.customer_id AND ca1.type = 1 LEFT JOIN vaddresses a1 ON ca1.address_id = a1.id LEFT JOIN customer_addresses ca2 ON c.id = ca2.customer_id AND ca2.type = 0 LEFT JOIN vaddresses a2 ON ca2.address_id = a2.id LEFT JOIN customerconsentview cc ON cc.customerid = c.id LEFT JOIN customerextids ce ON ce.customerid = c.id AND ce.serviceproviderid IS NULL WHERE c.type = 2; CREATE VIEW customeraddressview AS SELECT c.*, cc.consentdate AS consentdate, cc.invoicenotice AS invoicenotice, cc.mailingnotice AS mailingnotice, cc.smsnotice AS smsnotice, cc.einvoice AS einvoice, a1.country_id as countryid, a1.ccode, a1.zip as zip, a1.city as city, a1.street as street, a1.house as building, a1.flat as apartment, a2.country_id as post_countryid, a2.ccode AS post_ccode, a2.zip as post_zip, a2.city as post_city, a2.street as post_street, a2.house as post_building, a2.flat as post_apartment, a2.name as post_name, a1.address as address, a1.location AS full_address, a1.postoffice AS postoffice, a2.address as post_address, a2.location AS post_full_address, a2.postoffice AS post_postoffice, ce.extid AS extid FROM customers c JOIN customer_addresses ca1 ON c.id = ca1.customer_id AND ca1.type = 1 LEFT JOIN vaddresses a1 ON ca1.address_id = a1.id LEFT JOIN customer_addresses ca2 ON c.id = ca2.customer_id AND ca2.type = 0 LEFT JOIN vaddresses a2 ON ca2.address_id = a2.id LEFT JOIN customerconsentview cc ON cc.customerid = c.id LEFT JOIN customerextids ce ON ce.customerid = c.id AND ce.serviceproviderid IS NULL WHERE c.type < 2; CREATE OR REPLACE FUNCTION int2txt(bigint) RETURNS text AS $$ SELECT $1::text; $$ LANGUAGE SQL IMMUTABLE; CREATE VIEW nas AS SELECT n.id, inet_ntoa(n.ipaddr) AS nasname, d.shortname, d.nastype AS type, d.clients AS ports, d.secret, d.community, d.description FROM nodes n JOIN netdevices d ON (n.netdev = d.id) WHERE n.nas = 1; CREATE VIEW vnodes AS SELECT n.*, m.mac, a.ccode, a.city_id as location_city, a.street_id as location_street, a.house as location_house, a.flat as location_flat, a.location FROM nodes n LEFT JOIN (SELECT nodeid, array_to_string(array_agg(mac), ',') AS mac FROM macs GROUP BY nodeid) m ON (n.id = m.nodeid) LEFT JOIN vaddresses a ON n.address_id = a.id WHERE n.ipaddr <> 0 OR n.ipaddr_pub <> 0; CREATE VIEW vmacs AS SELECT n.*, m.mac, m.id AS macid, a.ccode, a.city_id as location_city, a.street_id as location_street, a.location, a.house as location_building, a.flat as location_flat FROM nodes n JOIN macs m ON (n.id = m.nodeid) LEFT JOIN vaddresses a ON n.address_id = a.id WHERE n.ipaddr <> 0 OR n.ipaddr_pub <> 0; CREATE VIEW vnodetariffs AS SELECT n.*, t.downrate, t.downceil, t.uprate, t.upceil, t.downrate_n, t.downceil_n, t.uprate_n, t.upceil_n, net.mask, net.gateway, net.dns, net.dns2, m.mac, a.ccode, a.city_id as location_city, a.street_id as location_street, a.house as location_house, a.flat as location_flat, a.location FROM nodes n JOIN networks net ON net.id = n.netid LEFT JOIN (SELECT nodeid, array_to_string(array_agg(mac), ',') AS mac FROM macs GROUP BY nodeid) m ON (n.id = m.nodeid) LEFT JOIN vaddresses a ON n.address_id = a.id JOIN ( SELECT n.id AS nodeid, ROUND(SUM(t.downrate * a.count)) AS downrate, ROUND(SUM(t.downceil * a.count)) AS downceil, SUM(t.down_burst_time) AS down_burst_time, SUM(t.down_burst_threshold) AS down_burst_threshold, SUM(t.down_burst_limit) AS down_burst_limit, ROUND(SUM(t.uprate * a.count)) AS uprate, ROUND(SUM(t.upceil * a.count)) AS upceil, SUM(t.up_burst_time) AS up_burst_time, SUM(t.up_burst_threshold) AS up_burst_threshold, SUM(t.up_burst_limit) AS up_burst_limit, ROUND(SUM(COALESCE(t.downrate_n, t.downrate) * a.count)) AS downrate_n, ROUND(SUM(COALESCE(t.downceil_n, t.downceil) * a.count)) AS downceil_n, SUM(COALESCE(t.down_burst_time_n, t.down_burst_time)) AS down_burst_time_n, SUM(COALESCE(t.down_burst_threshold_n, t.down_burst_threshold)) AS down_burst_threshold_n, SUM(COALESCE(t.down_burst_limit_n, t.down_burst_limit)) AS down_burst_limit_n, ROUND(SUM(COALESCE(t.uprate_n, t.uprate) * a.count)) AS uprate_n, ROUND(SUM(COALESCE(t.upceil_n, t.upceil) * a.count)) AS upceil_n, SUM(COALESCE(t.up_burst_time_n, t.up_burst_time)) AS up_burst_time_n, SUM(COALESCE(t.up_burst_threshold_n, t.up_burst_threshold)) AS up_burst_threshold_n, SUM(COALESCE(t.up_burst_limit_n, t.up_burst_limit)) AS up_burst_limit_n FROM nodes n JOIN nodeassignments na ON na.nodeid = n.id JOIN assignments a ON a.id = na.assignmentid JOIN tariffs t ON t.id = a.tariffid LEFT JOIN ( SELECT customerid, COUNT(id) AS allsuspended FROM assignments WHERE tariffid IS NULL AND liabilityid IS NULL AND datefrom <= EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::bigint AND (dateto = 0 OR dateto > EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::bigint) GROUP BY customerid ) s ON s.customerid = n.ownerid WHERE s.allsuspended IS NULL AND a.suspended = 0 AND a.commited = 1 AND a.datefrom <= EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::bigint AND (a.dateto = 0 OR a.dateto >= EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::bigint) AND (t.downrate > 0 OR t.downceil > 0 OR t.uprate > 0 OR t.upceil > 0) GROUP BY n.id ) t ON t.nodeid = n.id WHERE n.ipaddr <> 0 OR n.ipaddr_pub <> 0; CREATE VIEW vnodealltariffs AS SELECT n.*, COALESCE(t1.downrate, t2.downrate, 0) AS downrate, COALESCE(t1.downceil, t2.downceil, 0) AS downceil, COALESCE(t1.down_burst_time, t2.down_burst_time, 0) AS down_burst_time, COALESCE(t1.down_burst_threshold, t2.down_burst_threshold, 0) AS down_burst_threshold, COALESCE(t1.down_burst_limit, t2.down_burst_limit, 0) AS down_burst_limit, COALESCE(t1.uprate, t2.uprate, 0) AS uprate, COALESCE(t1.upceil, t2.upceil, 0) AS upceil, COALESCE(t1.up_burst_time, t2.up_burst_time, 0) AS up_burst_time, COALESCE(t1.up_burst_threshold, t2.up_burst_threshold, 0) AS up_burst_threshold, COALESCE(t1.up_burst_limit, t2.up_burst_limit, 0) AS up_burst_limit, COALESCE(t1.downrate_n, t2.downrate_n, 0) AS downrate_n, COALESCE(t1.downceil_n, t2.downceil_n, 0) AS downceil_n, COALESCE(t1.down_burst_time_n, t2.down_burst_time_n, 0) AS down_burst_time_n, COALESCE(t1.down_burst_threshold_n, t2.down_burst_threshold_n, 0) AS down_burst_threshold_n, COALESCE(t1.down_burst_limit_n, t2.down_burst_limit_n, 0) AS down_burst_limit_n, COALESCE(t1.uprate_n, t2.uprate_n, 0) AS uprate_n, COALESCE(t1.upceil_n, t2.upceil_n, 0) AS upceil_n, COALESCE(t1.up_burst_time_n, t2.up_burst_time_n, 0) AS up_burst_time_n, COALESCE(t1.up_burst_threshold_n, t2.up_burst_threshold_n, 0) AS up_burst_threshold_n, COALESCE(t1.up_burst_limit_n, t2.up_burst_limit_n, 0) AS up_burst_limit_n, net.mask, net.gateway, net.dns, net.dns2, m.mac, a.ccode, a.city_id as location_city, a.street_id as location_street, a.house as location_house, a.flat as location_flat, a.location FROM nodes n JOIN networks net ON net.id = n.netid LEFT JOIN ( SELECT nodeid, array_to_string(array_agg(mac), ',') AS mac FROM macs GROUP BY nodeid ) m ON n.id = m.nodeid LEFT JOIN vaddresses a ON a.id = n.address_id LEFT JOIN ( SELECT n.id AS nodeid, SUM(a.downrate) AS downrate, SUM(a.downceil) AS downceil, SUM(a.down_burst_time) AS down_burst_time, SUM(a.down_burst_threshold) AS down_burst_threshold, SUM(a.down_burst_limit) AS down_burst_limit, SUM(a.uprate) AS uprate, SUM(a.upceil) AS upceil, SUM(a.up_burst_time) AS up_burst_time, SUM(a.up_burst_threshold) AS up_burst_threshold, SUM(a.up_burst_limit) AS up_burst_limit, SUM(a.downrate_n) AS downrate_n, SUM(a.downceil_n) AS downceil_n, SUM(a.down_burst_time_n) AS down_burst_time_n, SUM(a.down_burst_threshold_n) AS down_burst_threshold_n, SUM(a.down_burst_limit_n) AS down_burst_limit_n, SUM(a.uprate_n) AS uprate_n, SUM(a.upceil_n) AS upceil_n, SUM(a.up_burst_time_n) AS up_burst_time_n, SUM(a.up_burst_threshold_n) AS up_burst_threshold_n, SUM(a.up_burst_limit_n) AS up_burst_limit_n FROM nodes n JOIN ( SELECT n.id, ROUND(SUM(t.downrate * a.count)) AS downrate, ROUND(SUM(t.downceil * a.count)) AS downceil, SUM(t.down_burst_time) AS down_burst_time, SUM(t.down_burst_threshold) AS down_burst_threshold, SUM(t.down_burst_limit) AS down_burst_limit, ROUND(SUM(t.uprate * a.count)) AS uprate, ROUND(SUM(t.upceil * a.count)) AS upceil, SUM(t.up_burst_time) AS up_burst_time, SUM(t.up_burst_threshold) AS up_burst_threshold, SUM(t.up_burst_limit) AS up_burst_limit, ROUND(SUM(COALESCE(t.downrate_n, t.downrate)) * a.count) AS downrate_n, ROUND(SUM(COALESCE(t.downceil_n, t.downceil)) * a.count) AS downceil_n, SUM(COALESCE(t.down_burst_time_n, t.down_burst_time)) AS down_burst_time_n, SUM(COALESCE(t.down_burst_threshold_n, t.down_burst_threshold)) AS down_burst_threshold_n, SUM(COALESCE(t.down_burst_limit_n, t.down_burst_limit)) AS down_burst_limit_n, ROUND(SUM(COALESCE(t.uprate_n, t.uprate)) * a.count) AS uprate_n, ROUND(SUM(COALESCE(t.upceil_n, t.upceil)) * a.count) AS upceil_n, SUM(COALESCE(t.up_burst_time_n, t.up_burst_time)) AS up_burst_time_n, SUM(COALESCE(t.up_burst_threshold_n, t.up_burst_threshold)) AS up_burst_threshold_n, SUM(COALESCE(t.up_burst_limit_n, t.up_burst_limit)) AS up_burst_limit_n FROM assignments a JOIN nodeassignments na ON na.assignmentid = a.id JOIN nodes n ON n.id = na.nodeid JOIN tariffs t ON t.id = a.tariffid LEFT JOIN ( SELECT customerid, COUNT(id) AS allsuspended FROM assignments WHERE tariffid IS NULL AND liabilityid IS NULL AND datefrom <= EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::bigint AND (dateto = 0 OR dateto > EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::bigint) GROUP BY customerid ) s ON s.customerid = n.ownerid WHERE s.allsuspended IS NULL AND a.suspended = 0 AND a.commited = 1 AND a.datefrom <= EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::bigint AND (a.dateto = 0 OR a.dateto >= EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::bigint) AND (t.downrate > 0 OR t.downceil > 0 OR t.uprate > 0 OR t.upceil > 0) GROUP BY n.id, a.count ) a ON a.id = n.id GROUP BY n.id ) t1 ON t1.nodeid = n.id LEFT JOIN ( SELECT n.id AS nodeid, SUM(a.downrate) AS downrate, SUM(a.downceil) AS downceil, SUM(a.down_burst_time) AS down_burst_time, SUM(a.down_burst_threshold) AS down_burst_threshold, SUM(a.down_burst_limit) AS down_burst_limit, SUM(a.uprate) AS uprate, SUM(a.upceil) AS upceil, SUM(a.up_burst_time) AS up_burst_time, SUM(a.up_burst_threshold) AS up_burst_threshold, SUM(a.up_burst_limit) AS up_burst_limit, SUM(a.downrate_n) AS downrate_n, SUM(a.downceil_n) AS downceil_n, SUM(a.down_burst_time_n) AS down_burst_time_n, SUM(a.down_burst_threshold_n) AS down_burst_threshold_n, SUM(a.down_burst_limit_n) AS down_burst_limit_n, SUM(a.uprate_n) AS uprate_n, SUM(a.upceil_n) AS upceil_n, SUM(a.up_burst_time_n) AS up_burst_time_n, SUM(a.up_burst_threshold_n) AS up_burst_threshold_n, SUM(a.up_burst_limit_n) AS up_burst_limit_n FROM nodes n JOIN ( SELECT n.id AS nodeid, ROUND(SUM(t.downrate * a.count)) AS downrate, ROUND(SUM(t.downceil * a.count)) AS downceil, SUM(t.down_burst_time) AS down_burst_time, SUM(t.down_burst_threshold) AS down_burst_threshold, SUM(t.down_burst_limit) AS down_burst_limit, ROUND(SUM(t.uprate * a.count)) AS uprate, ROUND(SUM(t.upceil * a.count)) AS upceil, SUM(t.up_burst_time) AS up_burst_time, SUM(t.up_burst_threshold) AS up_burst_threshold, SUM(t.up_burst_limit) AS up_burst_limit, ROUND(SUM((CASE WHEN t.downrate_n IS NOT NULL THEN t.downrate_n ELSE t.downrate END) * a.count)) AS downrate_n, ROUND(SUM((CASE WHEN t.downceil_n IS NOT NULL THEN t.downceil_n ELSE t.downceil END) * a.count)) AS downceil_n, SUM(CASE WHEN t.down_burst_time_n IS NOT NULL THEN t.down_burst_time_n ELSE t.down_burst_time END) AS down_burst_time_n, SUM(CASE WHEN t.down_burst_threshold_n IS NOT NULL THEN t.down_burst_threshold_n ELSE t.down_burst_threshold END) AS down_burst_threshold_n, SUM(CASE WHEN t.down_burst_limit_n IS NOT NULL THEN t.down_burst_limit_n ELSE t.down_burst_limit END) AS down_burst_limit_n, ROUND(SUM((CASE WHEN t.uprate_n IS NOT NULL THEN t.uprate_n ELSE t.uprate END) * a.count)) AS uprate_n, ROUND(SUM((CASE WHEN t.upceil_n IS NOT NULL THEN t.upceil_n ELSE t.upceil END) * a.count)) AS upceil_n, SUM(CASE WHEN t.up_burst_time_n IS NOT NULL THEN t.up_burst_time_n ELSE t.up_burst_time END) AS up_burst_time_n, SUM(CASE WHEN t.up_burst_threshold_n IS NOT NULL THEN t.up_burst_threshold_n ELSE t.up_burst_threshold END) AS up_burst_threshold_n, SUM(CASE WHEN t.up_burst_limit_n IS NOT NULL THEN t.up_burst_limit_n ELSE t.up_burst_limit END) AS up_burst_limit_n FROM assignments a JOIN tariffs t ON t.id = a.tariffid JOIN ( SELECT vn.id, (CASE WHEN nd.id IS NULL THEN vn.ownerid ELSE nd.ownerid END) AS ownerid FROM vnodes vn LEFT JOIN netdevices nd ON nd.id = vn.netdev AND vn.ownerid IS NULL AND nd.ownerid IS NOT NULL WHERE (vn.ownerid IS NOT NULL AND nd.id IS NULL) OR (vn.ownerid IS NULL AND nd.id IS NOT NULL) ) n ON n.ownerid = a.customerid LEFT JOIN ( SELECT customerid, COUNT(id) AS allsuspended FROM assignments WHERE tariffid IS NULL AND liabilityid IS NULL AND datefrom <= EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::bigint AND (dateto = 0 OR dateto > EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::bigint) GROUP BY customerid ) s ON s.customerid = a.customerid WHERE s.allsuspended IS NULL AND a.suspended = 0 AND a.commited = 1 AND a.datefrom <= EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::bigint AND (a.dateto = 0 OR a.dateto >= EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::bigint) AND (t.downrate > 0 OR t.downceil > 0 OR t.uprate > 0 OR t.upceil > 0) AND n.id NOT IN (SELECT nodeid FROM nodeassignments) AND a.id NOT IN (SELECT assignmentid FROM nodeassignments) GROUP BY n.id, a.count ) a ON a.nodeid = n.id GROUP BY n.id ) t2 ON t2.nodeid = n.id WHERE (n.ipaddr <> 0 OR n.ipaddr_pub <> 0) AND ((t1.nodeid IS NOT NULL AND t2.nodeid IS NULL) OR (t1.nodeid IS NULL AND t2.nodeid IS NOT NULL) OR (t1.nodeid IS NULL AND t2.nodeid IS NULL)); CREATE VIEW teryt_terc AS SELECT ident AS woj, 0::text AS pow, 0::text AS gmi, 0 AS rodz, UPPER(name) AS nazwa FROM location_states UNION SELECT s.ident AS woj, d.ident AS pow, 0::text AS gmi, 0 AS rodz, d.name AS nazwa FROM location_districts d JOIN location_states s ON (d.stateid = s.id) UNION SELECT s.ident AS woj, d.ident AS pow, b.ident AS gmi, b.type AS rodz, b.name AS nazwa FROM location_boroughs b JOIN location_districts d ON (b.districtid = d.id) JOIN location_states s ON (d.stateid = s.id); CREATE VIEW teryt_simc AS SELECT s.ident AS woj, d.ident AS pow, b.ident AS gmi, b.type AS rodz_gmi, c.ident AS sym, c.id AS cityid, c.name AS nazwa, COALESCE(cc.ident, c.ident) AS sympod, COALESCE(cc.id, c.id) AS subcityid FROM location_cities c JOIN location_boroughs b ON (c.boroughid = b.id) JOIN location_districts d ON (b.districtid = d.id) JOIN location_states s ON (d.stateid = s.id) LEFT JOIN location_cities cc ON (c.cityid = cc.id); CREATE VIEW teryt_ulic AS SELECT st.ident AS woj, d.ident AS pow, b.ident AS gmi, b.type AS rodz_gmi, c.ident AS sym, s.ident AS sym_ul, s.name AS nazwa_1, s.name2 AS nazwa_2, t.name AS cecha, s.id FROM location_streets s JOIN location_street_types t ON (s.typeid = t.id) JOIN location_cities c ON (s.cityid = c.id) JOIN location_boroughs b ON (c.boroughid = b.id) JOIN location_districts d ON (b.districtid = d.id) JOIN location_states st ON (d.stateid = st.id); CREATE VIEW customermailsview AS SELECT customerid, array_to_string(array_agg(contact), ',') AS email FROM customercontacts WHERE (type & 8) > 0 AND contact <> '' GROUP BY customerid; CREATE VIEW vusers AS SELECT u.*, (u.firstname || ' ' || u.lastname) AS name, (u.lastname || ' ' || u.firstname) AS rname FROM users u LEFT JOIN userdivisions ud ON u.id = ud.userid WHERE lms_current_user() = 0 OR ud.divisionid IN (SELECT ud2.divisionid FROM userdivisions ud2 WHERE ud2.userid = lms_current_user()) GROUP BY u.id; CREATE VIEW vallusers AS SELECT *, (firstname || ' ' || lastname) AS name, (lastname || ' ' || firstname) AS rname FROM users; CREATE OR REPLACE FUNCTION get_invoice_contents(integer) RETURNS TABLE ( docid integer, itemid smallint, value numeric(12,5), pdiscount numeric(5,2), taxid integer, prodid varchar(255), content varchar(16), count numeric(9,3), description text, tariffid integer, vdiscount numeric(9,3), taxcategory smallint, period smallint, netflag integer, netprice numeric(12,5), grossprice numeric(12,5), netvalue numeric(12,5), taxvalue numeric(4,2), grossvalue numeric(12,5), diff_count numeric(9,3), diff_pdiscount numeric(5,2), diff_vdiscount numeric(9,3), diff_netprice numeric(12,5), diff_grossprice numeric(12,5), diff_netvalue numeric(12,5), diff_taxvalue numeric(4,2), diff_grossvalue numeric(12,5), taxrate numeric(4,2) ) AS $$ SELECT ic.docid, ic.itemid, ic.value, ic.pdiscount, ic.taxid, ic.prodid, ic.content, ic.count, ic.description, ic.tariffid, ic.vdiscount, ic.taxcategory, ic.period, CASE WHEN (d.flags & 16) > 0 THEN 1 ELSE 0 END AS netflag, CASE WHEN (d.flags & 16) > 0 THEN round(ic.value, 3) ELSE round(ic.value / (1 + t.value / 100), 3) END AS netprice, CASE WHEN (d.flags & 16) > 0 THEN round(ic.value * (1 + t.value / 100), 3) ELSE round(ic.value, 3) END AS grossprice, CASE WHEN (d.flags & 16) > 0 THEN round(ic.value * abs(ic.count), 2) ELSE round(ic.value * abs(ic.count), 2) - round(round(ic.value * abs(ic.count), 2) * t.value / (100 + t.value), 2) END AS netvalue, CASE WHEN (d.flags & 16) > 0 THEN round(round(ic.value * abs(ic.count), 2) * t.value / 100, 2) ELSE round(round(ic.value * abs(ic.count), 2) * t.value / (100 + t.value), 2) END AS taxvalue, CASE WHEN (d.flags & 16) > 0 THEN round(round(ic.value * abs(ic.count), 2) * (1 + t.value / 100), 2) ELSE round(ic.value * abs(ic.count), 2) END AS grossvalue, ic.count - ic2.count AS diff_count, ic.pdiscount - ic2.pdiscount AS diff_pdiscount, ic.vdiscount - ic2.vdiscount AS diff_vdiscount, CASE WHEN (d.flags & 16) > 0 THEN round(ic.value, 3) - round(ic2.value, 3) ELSE round(ic.value / (1 + t.value / 100), 3) - round(ic2.value / (1 + t.value / 100), 3) END AS diff_netprice, CASE WHEN (d.flags & 16) > 0 THEN round(ic.value * (1 + t.value / 100), 3) - round(ic2.value * (1 + t.value / 100), 3) ELSE round(ic.value, 3) - round(ic2.value, 3) END AS diff_grossprice, CASE WHEN (d.flags & 16) > 0 THEN round(ic.value * abs(ic.count), 2) - round(ic2.value * abs(ic2.count), 2) ELSE round(ic.value * abs(ic.count), 2) - round(round(ic.value * abs(ic.count), 2) * t.value / (100 + t.value), 2) - round(ic2.value * abs(ic2.count), 2) + round(round(ic2.value * abs(ic2.count), 2) * t.value / (100 + t.value), 2) END AS diff_netvalue, CASE WHEN (d.flags & 16) > 0 THEN round(round(ic.value * abs(ic.count), 2) * t.value / 100, 2) - round(round(ic2.value * abs(ic2.count), 2) * t.value / 100, 2) ELSE round(round(ic.value * abs(ic.count), 2) * t.value / (100 + t.value), 2) - round(round(ic2.value * abs(ic2.count), 2) * t.value / (100 + t.value), 2) END AS diff_taxvalue, CASE WHEN (d.flags & 16) > 0 THEN round(round(ic.value * abs(ic.count), 2) * (1 + t.value / 100), 2) - round(round(ic2.value * abs(ic2.count), 2) * (1 + t.value / 100), 2) ELSE round(ic.value * abs(ic.count), 2) - round(ic2.value * abs(ic2.count), 2) END AS diff_grossvalue, CASE WHEN t.reversecharge = 1 THEN -2 ELSE CASE WHEN t.taxed = 0 THEN -1 ELSE t.value END END AS taxrate FROM invoicecontents ic JOIN taxes t ON t.id = ic.taxid JOIN documents d ON d.id = ic.docid LEFT JOIN documents d2 ON d2.id = d.reference LEFT JOIN invoicecontents ic2 ON ic2.docid = d2.id AND ic2.itemid = ic.itemid WHERE $1 IS NULL OR d.customerid = $1 $$ LANGUAGE SQL IMMUTABLE; CREATE VIEW vinvoicecontents AS SELECT * FROM get_invoice_contents(NULL); CREATE OR REPLACE FUNCTION customerbalances_update() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF (TG_OP = 'TRUNCATE') THEN DELETE FROM customerbalances; RETURN NULL; ELSEIF (TG_OP = 'DELETE') THEN IF OLD.customerid IS NULL THEN RETURN NULL; END IF; IF NOT EXISTS (SELECT 1 FROM cash WHERE customerid = OLD.customerid) THEN DELETE FROM customerbalances WHERE customerid = OLD.customerid; ELSE IF EXISTS (SELECT 1 FROM customerbalances WHERE customerid = OLD.customerid) THEN UPDATE customerbalances SET balance = (SELECT SUM(value * currencyvalue) FROM cash WHERE customerid = OLD.customerid) WHERE customerid = OLD.customerid; ELSE INSERT INTO customerbalances (customerid, balance) VALUES (OLD.customerid, (SELECT SUM(value * currencyvalue) FROM cash WHERE customerid = OLD.customerid)); END IF; END IF; RETURN NULL; ELSEIF (TG_OP = 'UPDATE') THEN IF OLD.value = NEW.value AND OLD.currencyvalue = NEW.currencyvalue THEN RETURN NEW; END IF; IF OLD.customerid IS NOT NULL AND OLD.customerid <> NEW.customerid THEN IF EXISTS (SELECT 1 FROM customerbalances WHERE customerid = OLD.customerid) THEN UPDATE customerbalances SET balance = (SELECT SUM(value * currencyvalue) FROM cash WHERE customerid = OLD.customerid) WHERE customerid = OLD.customerid; ELSE INSERT INTO customerbalances (customerid, balance) VALUES (OLD.customerid, (SELECT SUM(value * currencyvalue) FROM cash WHERE customerid = OLD.customerid)); END IF; END IF; IF NEW.customerid IS NULL THEN RETURN NEW; END IF; IF EXISTS (SELECT 1 FROM customerbalances WHERE customerid = NEW.customerid) THEN UPDATE customerbalances SET balance = (SELECT SUM(value * currencyvalue) FROM cash WHERE customerid = NEW.customerid) WHERE customerid = NEW.customerid; ELSE INSERT INTO customerbalances (customerid, balance) VALUES (NEW.customerid, (SELECT SUM(value * currencyvalue) FROM cash WHERE customerid = NEW.customerid)); END IF; RETURN NEW; ELSE IF NEW.customerid IS NULL THEN RETURN NEW; END IF; IF EXISTS (SELECT 1 FROM customerbalances WHERE customerid = NEW.customerid) THEN UPDATE customerbalances SET balance = (SELECT SUM(value * currencyvalue) FROM cash WHERE customerid = NEW.customerid) WHERE customerid = NEW.customerid; ELSE INSERT INTO customerbalances (customerid, balance) VALUES (NEW.customerid, (SELECT SUM(value * currencyvalue) FROM cash WHERE customerid = NEW.customerid)); END IF; RETURN NEW; END IF; END; $$; CREATE TRIGGER cash_customerbalances_update_trigger AFTER INSERT OR UPDATE OR DELETE ON cash FOR EACH ROW EXECUTE PROCEDURE customerbalances_update(); CREATE TRIGGER cash_customerbalances_truncate_trigger AFTER TRUNCATE ON cash EXECUTE PROCEDURE customerbalances_update(); /* --------------------------------------------------- Data records ------------------------------------------------------*/ INSERT INTO rtcategories (name, description, style) VALUES ('default', 'default category', 'background-color:#ffffff;color:#000000'); INSERT INTO up_rights(module, name, description) VALUES ('info', 'edit_addr_ack', 'Customer can change address information with admin acknowlegment'); INSERT INTO up_rights(module, name, description) VALUES ('info', 'edit_addr', 'Customer can change address information'); INSERT INTO up_rights(module, name, description, setdefault) VALUES ('info', 'edit_contact_ack', 'Customer can change contact information with admin acknowlegment', 0); INSERT INTO up_rights(module, name, description) VALUES ('info', 'edit_contact', 'Customer can change contact information'); INSERT INTO countries (name, ccode) VALUES ('Lithuania', 'lt_LT'), ('Poland', 'pl_PL'), ('Romania', 'ro_RO'), ('Slovakia', 'sk_SK'), ('USA', 'en_US'), ('Czech', 'cs_CZ'), ('Guyana', 'en_GY'), ('Great Britain', 'en_GB'), ('United Kingdom', 'en_GB'); INSERT INTO addresses (name) VALUES ('default'); INSERT INTO divisions (shortname, name, address_id) VALUES ('default', 'default', (SELECT MAX(id) FROM addresses)); INSERT INTO nastypes (name) VALUES ('mikrotik_snmp'), ('cisco'), ('computone'), ('livingston'), ('max40xx'), ('multitech'), ('netserver'), ('pathras'), ('patton'), ('portslave'), ('tc'), ('usrhiper'), ('other'); INSERT INTO uiconfig (section, var, value, description, disabled) VALUES ('phpui', 'default_autosuggest_placement','bottom','',0), ('phpui', 'autosuggest_max_length','40','',0), ('phpui', 'lang', '', '', 0), ('phpui', 'allow_from', '', '', 0), ('phpui', 'default_module', 'welcome', '', 0), ('phpui', 'timeout', '600', '', 0), ('phpui', 'settings_timeout', '28800', '', 0), ('customers', 'list_page_limit', '100', '', 0), ('phpui', 'billinglist_pagelimit', '100', '', 0), ('nodes', 'list_page_limit', '100', '', 0), ('phpui', 'balancelist_pagelimit', '100', '', 0), ('phpui', 'invoicelist_pagelimit', '100', '', 0), ('phpui', 'debitnotelist_pagelimit', '100', '', 0), ('rt', 'ticketlist_pagelimit', '100', '', 0), ('phpui', 'accountlist_pagelimit', '100', '', 0), ('phpui', 'domainlist_pagelimit', '100', '', 0), ('phpui', 'aliaslist_pagelimit', '100', '', 0), ('phpui', 'configlist_pagelimit', '100', '', 0), ('phpui', 'receiptlist_pagelimit', '100', '', 0), ('phpui', 'taxratelist_pagelimit', '100', '', 0), ('phpui', 'numberplanlist_pagelimit', '100', '', 0), ('phpui', 'divisionlist_pagelimit', '100', '', 0), ('phpui', 'documentlist_pagelimit', '100', '', 0), ('phpui', 'voipaccountlist_pagelimit', '100', '', 0), ('phpui', 'networkhosts_pagelimit', '256', '', 0), ('phpui', 'messagelist_pagelimit', '100', '', 0), ('phpui', 'recordlist_pagelimit', '100', '', 0), ('phpui', 'cashreglog_pagelimit', '100', '', 0), ('phpui', 'networklist_pagelimit', '100', '', 0), ('phpui', 'reload_type', 'sql', '', 0), ('phpui', 'reload_execcmd', '/bin/true', '', 0), ('phpui', 'reload_sqlquery', '', '', 0), ('phpui', 'lastonline_limit', '600', '', 0), ('timetable', 'default_forward_day_limit', '7', '', 0), ('phpui', 'gd_translate_to', 'ISO-8859-2', '', 0), ('phpui', 'check_for_updates_period', '86400', '', 0), ('phpui', 'homedir_prefix', '/home/', '', 0), ('phpui', 'default_taxrate', '23', '', 0), ('phpui', 'default_zip', '', '', 0), ('customers', 'default_city', '', '', 0), ('phpui', 'default_address', '', '', 0), ('phpui', 'smarty_debug', 'false', '', 0), ('phpui', 'force_ssl', 'false', '', 0), ('phpui', 'allow_mac_sharing', 'false', '', 0), ('phpui', 'big_networks', 'true', '', 0), ('phpui', 'short_pagescroller', 'true', '', 0), ('rt', 'show_stats', 'true', '', 0), ('rt', 'notification_customerinfo', 'true', '', 0), ('rt', 'notification_mail_body_customerinfo_format', '-- Klient: %custname ID: %cid Adres: %address E-mail: %email Telefon: %phone', '', 0), ('rt', 'notification_sms_body_customerinfo_format', 'Klient: %custname ID: %cid Adres: %address Telefon: %phone', '', 0), ('rt', 'backend_mode', 'false', '', 0), ('rt', 'sender_name', '', '', 0), ('rt', 'quote_body', 'false', '', 0), ('rt', 'notification_mail_subject', '[RT#%tid] %subject', '', 0), ('rt', 'notification_mail_body', '%body URL: %url %customerinfo', '', 0), ('rt', 'notification_sms_body', '[RT#%tid] %subject: %body %customerinfo', '', 0), ('phpui', 'use_invoices', 'false', '', 0), ('rt', 'ticket_template_file', 'rtticketprint.html', '', 0), ('phpui', 'use_current_payday', 'false', '', 0), ('phpui', 'default_monthly_payday', '', '', 0), ('rt', 'new_ticket_notify', 'true', '', 0), ('phpui', 'to_words_short_version', 'false', '', 0), ('rt', 'ticketlist_status', '', '', 0), ('phpui', 'ewx_support', 'false', '', 0), ('phpui', 'invoice_check_payment', 'false', '', 0), ('phpui', 'note_check_payment', 'false', '', 0), ('phpui', 'radius', '1', '', 0), ('nodes', 'public_ip', '1', '', 0), ('assignments', 'default_period', '3', '', 0), ('assignments', 'default_document_type', '0', '', 0), ('phpui', 'default_editor', 'html', '', 0), ('logs', 'enabled', 'false', '', 0), ('phpui', 'hide_toolbar', 'false', '', 0), ('customers', 'groups_required_on_add', 'false', '', 0), ('documents', 'margins', '10,5,15,5', '', 0), ('phpui', 'quicksearch_limit', '15', '', 0), ('phpui', 'ping_type', '1', '', 0), ('phpui', 'default_teryt_city', 'false', '', 0), ('phpui', 'passwordhistory', 6, '', 0), ('timetable', 'event_usergroup_selection_type', 'update', '', 0), ('phpui', 'force_global_division_context', 'false', '', 0), ('payments', 'date_format', '%Y/%m/%d', '', 0), ('payments', 'default_unit_name', 'pcs.', '', 0), ('voip', 'default_cost_limit', '200', '', 2), ('invoices', 'template_file', 'FT-0100', '', 0), ('invoices', 'content_type', 'application/pdf', '', 0), ('invoices', 'print_balance_history', 'false', '', 0), ('invoices', 'print_balance_history_limit', '10', '', 0), ('invoices', 'default_printpage', 'original', '', 0), ('invoices', 'type', 'pdf', '', 0), ('invoices', 'attachment_name', '', '', 0), ('invoices', 'paytime', '14', '', 0), ('invoices', 'paytype', '1', '', 0), ('notes', 'template_file', 'note.html', '', 0), ('notes', 'content_type', 'text/html', '', 0), ('notes', 'type', 'html', '', 0), ('notes', 'attachment_name', '', '', 0), ('notes', 'paytime', '14', '', 0), ('receipts', 'template_file', 'receipt.html', '', 0), ('receipts', 'content_type', 'text/html', '', 0), ('receipts', 'type', 'html', '', 0), ('receipts', 'attachment_name', '', '', 0), ('payments', 'suspension_percentage', '0', '', 0), ('mail', 'debug_email', '', '', 0), ('mail', 'smtp_host', '127.0.0.1', '', 0), ('mail', 'smtp_port', '25', '', 0), ('mail', 'backend', 'phpmailer', '', 0), ('mail', 'smtp_secure', 'tls', '', 0), ('zones', 'hostmaster_mail', 'hostmaster.localhost', '', 0), ('zones', 'master_dns', 'localhost', '', 0), ('zones', 'slave_dns', 'localhost', '', 0), ('zones', 'default_ttl', '3600', '', 0), ('zones', 'ttl_refresh', '28800', '', 0), ('zones', 'ttl_retry', '7200', '', 0), ('zones', 'ttl_expire', '604800', '', 0), ('zones', 'ttl_minimum', '86400', '', 0), ('zones', 'default_webserver_ip', '127.0.0.1', '', 0), ('zones', 'default_mailserver_ip', '127.0.0.1', '', 0), ('zones', 'default_mx', 'localhost', '', 0), ('userpanel', 'hint', '', '', 0), ('userpanel', 'style', 'bclean', '', 0), ('userpanel', 'data_consent_text', '', '', 0), ('userpanel', 'disable_transferform', '0', '', 0), ('userpanel', 'disable_invoices', '0', '', 0), ('userpanel', 'invoice_duplicate', '0', '', 0), ('userpanel', 'show_tariffname', '1', '', 0), ('userpanel', 'show_speeds', '1', '', 0), ('userpanel', 'show_period', '1', '', 0), ('userpanel', 'show_discount', '1', '', 0), ('userpanel', 'show_discounted_value', '0', '', 0), ('userpanel', 'show_invoice_flag', '1', '', 0), ('userpanel', 'queues', '1', '', 0), ('userpanel', 'tickets_from_selected_queues', '0', '', 0), ('userpanel', 'allow_message_add_to_closed_tickets', '1', '', 0), ('userpanel', 'limit_ticket_movements_to_selected_queues', '0', '', 0), ('userpanel', 'default_userid', '0', '', 0), ('userpanel', 'debug_email', '', '', 0), ('userpanel', 'lms_url', '', '', 0), ('userpanel', 'hide_nodesbox', '0', '', 0), ('userpanel', 'hide_documentbox', '0', '', 0), ('userpanel', 'logout_url', '', '', 0), ('userpanel', 'owner_stats', '1', '', 0), ('userpanel', 'default_categories', '1', '', 0), ('userpanel', 'auth_type', '1', '', 0), ('userpanel', 'show_confirmed_documents_only', 'false', '', 0), ('userpanel', 'show_unapproved_document_attachments', 'false', '', 0), ('userpanel', 'module_order', '', '', 0), ('userpanel', 'visible_ticket_sources', '0;1;2;3;4;5;6;7', '', 0), ('userpanel', 'change_notification_mail_sender', '', '', 0), ('userpanel', 'change_notification_mail_recipient', '', '', 0), ('userpanel', 'change_notification_mail_subject', '', '', 0), ('userpanel', 'change_notification_mail_body', '', '', 0), ('userpanel', 'change_confirmation_mail_subject', '', '', 0), ('userpanel', 'change_confirmation_mail_body', '', '', 0), ('userpanel', 'change_rejection_mail_subject', '', '', 0), ('userpanel', 'change_rejection_mail_body', '', '', 0), ('userpanel', 'document_notification_mail_dsn_address', '', '', 0), ('userpanel', 'document_notification_mail_mdn_address', '', '', 0), ('userpanel', 'document_notification_mail_reply_address', '', '', 0), ('userpanel', 'document_notification_mail_sender_name', '', '', 0), ('userpanel', 'document_notification_mail_sender_address', '', '', 0), ('userpanel', 'new_document_customer_notification_mail_format', 'text', '', 0), ('userpanel', 'new_document_customer_notification_mail_subject', '', '', 0), ('userpanel', 'new_document_customer_notification_mail_body', '', '', 0), ('userpanel', 'new_document_customer_notification_sms_body', '', '', 0), ('userpanel', 'signed_document_scan_operator_notification_mail_recipient', '', '', 0), ('userpanel', 'signed_document_scan_operator_notification_mail_format', 'text', '', 0), ('userpanel', 'signed_document_scan_operator_notification_mail_subject', '', '', 0), ('userpanel', 'signed_document_scan_operator_notification_mail_body', '', '', 0), ('userpanel', 'signed_document_scan_customer_notification_mail_format', 'text', '', 0), ('userpanel', 'signed_document_scan_customer_notification_mail_subject', '', '', 0), ('userpanel', 'signed_document_scan_customer_notification_mail_body', '', '', 0), ('userpanel', 'document_approval_operator_notification_mail_recipient', '', '', 0), ('userpanel', 'document_approval_operator_notification_mail_format', 'text', '', 0), ('userpanel', 'document_approval_operator_notification_mail_subject', '', '', 0), ('userpanel', 'document_approval_operator_notification_mail_body', '', '', 0), ('userpanel', 'document_approval_customer_notification_mail_format', 'text', '', 0), ('userpanel', 'document_approval_customer_notification_mail_subject', '', '', 0), ('userpanel', 'document_approval_customer_notification_mail_body', '', '', 0), ('userpanel', 'document_approval_customer_notification_attachments', '0', '', 0), ('userpanel', 'document_approval_customer_onetime_password_sms_body', '', '', 0), ('userpanel', 'google_recaptcha_sitekey', '', '', 0), ('userpanel', 'google_recaptcha_secret', '', '', 0), ('userpanel', 'allow_reopen_tickets_newer_than', '0', '', 0), ('userpanel', 'pin_changes', 'true', '', 0), ('userpanel', 'show_customer_sensitive_data', 'false', '', 0), ('userpanel', 'show_last_years', '5', '', 0), ('userpanel', 'aggregate_documents', '0', '', 0), ('userpanel', 'speed_unit_type', '1000', '', 0), ('userpanel', 'speed_unit_aggregation_threshold', '5', '', 0), ('userpanel', 'shortcut_icon', '', '', 0), ('userpanel', 'timeout', '600', '', 0), ('userpanel', 'hide_archived_documents', 'false', '', 0), ('userpanel', 'sms_credential_reminders', 'true', '', 0), ('userpanel', 'mail_credential_reminders', 'true', '', 0), ('userpanel', 'startup_module', 'info', '', 0), ('userpanel', 'pin_validation', 'true', '', 0), ('userpanel', 'show_all_assignments', 'false', '', 0), ('userpanel', 'allowed_document_types', '', '', 0), ('userpanel', 'allowed_customer_status', '', '', 0), ('userpanel', 'authentication_customer_extid_service_provider_id', '', '', 0), ('directories', 'userpanel_dir', 'userpanel', '', 0), ('finances', 'debt_interest_percentages', '1997.01.01:35.0 1998.04.15:33.0 1999.02.01:24.0 1999.05.15:21.0 2000.11.01:30.0 2001.12.15:20.0 2002.07.25:16.0 2003.02.01:13.0 2003.09.25:12.25 2005.01.10:13.5 2005.10.15:11.5 2008.12.15:13.0 2014.12.23:8.0 2016.01.01:7.0 2020.03.18:6.5 2020.04.09:6.0 2020.05.29:5.6 2021.10.07:6.0 2021.11.04:6.75 2021.12.09:7.25 2022.01.05:7.75 2022.02.09:8.25 2022.03.09:9.0 2022.04.07:10.0 2022.05.06:10.75 2022.06.09:11.5 2022.07.08:12.0 2022.09.08:12.25 2023.09.07:11.50 2023.10.05:11.25 2025.05.08:10.75 2025.07.03:10.5 2025.09.04:10.25 2025.10.09:10.0 2025.11.06:9.75 2025.12.04:9.50', '', 0); INSERT INTO invprojects (name, type) VALUES ('inherited', 1); INSERT INTO netdeviceproducers (id, name) VALUES (1, 'Mikrotik'), (2, 'Ubiquiti'); INSERT INTO netdevicemodels (name, alternative_name, netdeviceproducerid) VALUES ('RB750', NULL, 1), ('RB750UP', NULL, 1), ('RB750GL', NULL, 1), ('PowerBox', NULL, 1), ('RB2011iL-IN', NULL, 1), ('RB2011iL-RM', NULL, 1), ('RB2011iLS-IN', NULL, 1), ('RB2011UiAS-IN', NULL, 1), ('RB2011UiAS-RM', NULL, 1), ('RB1100AHx2', NULL, 1), ('CCR1009-8G-1S', NULL, 1), ('CCR1009-8G-1S-1S+', NULL, 1), ('CCR1009-8G-1S-1S+PC', NULL, 1), ('CCR1016-12S-1S+', NULL, 1), ('CCR1016-12G', NULL, 1), ('CCR1036-12G-4S', NULL, 1), ('CCR1036-8G-2S+', NULL, 1), ('CCR1036-12G-4S-EM', NULL, 1), ('CCR1036-8G-2S+EM', NULL, 1), ('RB1100AHx2-LM', NULL, 1), ('RB750G', NULL, 1), ('RB1000', NULL, 1), ('RB1000U', NULL, 1), ('RB1100', NULL, 1), ('RB1100Hx2', NULL, 1), ('RB1100AH', NULL, 1), ('RB1200', NULL, 1), ('RB260GS', NULL, 1), ('RB260GSP', NULL, 1), ('CRS109-8G-1S-2HnD-IN', NULL, 1), ('CRS210-8G-2S+IN', NULL, 1), ('CRS212-1G-10S-1S+IN', NULL, 1), ('CRS125-24G-1S-IN', NULL, 1), ('CRS125-24G-1S-RM', NULL, 1), ('CRS125-24G-1S-2HnD-IN', NULL, 1), ('CRS226-24G-2S+IN', NULL, 1), ('CRS226-24G-2S+RM', NULL, 1), ('RB250GS', NULL, 1), ('NetMetal 5', NULL, 1), ('SXT 5 ac', NULL, 1), ('SXT SA5 ac', NULL, 1), ('NetBox 5', NULL, 1), ('SXT Lite2', NULL, 1), ('SXT Lite5', NULL, 1), ('Groove 52HPn', NULL, 1), ('GrooveA 52HPn', NULL, 1), ('SXT 2', NULL, 1), ('SXT 5', NULL, 1), ('BaseBox 2', NULL, 1), ('BaseBox 5', NULL, 1), ('OmniTIK U-5HnD', NULL, 1), ('SXT HG5', NULL, 1), ('SXT SA5', NULL, 1), ('OmniTIK UPA-5HnD', NULL, 1), ('Metal 9HPn', NULL, 1), ('Metal 2SHPn', NULL, 1), ('Metal 5SHPn', NULL, 1), ('SEXTANT G 5HPnD', NULL, 1), ('QRT 2', NULL, 1), ('QRT 5', NULL, 1), ('SXT Sixpack', NULL, 1), ('SXT 5HnD', NULL, 1), ('SXT G-5HnD', NULL, 1), ('RB951-2n', NULL, 1), ('mAP 2n', NULL, 1), ('cAP 2n', NULL, 1), ('RB951Ui-2HnD', NULL, 1), ('RB951G-2HnD', NULL, 1), ('RB2011UiAS-2HnD-IN', NULL, 1), ('RB751U-2HnD', NULL, 1), ('RB751G-2HnD', NULL, 1), ('911 Lite2', NULL, 1), ('911 Lite5', NULL, 1), ('RB411L', NULL, 1), ('911 Lite5 dual', NULL, 1), ('RB411', NULL, 1), ('RB911G-2HPnD', NULL, 1), ('RB450', NULL, 1), ('RB411GL', NULL, 1), ('RB911G-5HPnD', NULL, 1), ('RB411U', NULL, 1), ('RB911G-5HPacD', NULL, 1), ('RB912UAG-2HPnD', NULL, 1), ('RB912UAG-5HPnD', NULL, 1), ('RB411AR', NULL, 1), ('RB433UL', NULL, 1), ('RB433', NULL, 1), ('RB450G', NULL, 1), ('RB411AH', NULL, 1), ('RB922UAGS-5HPacD', NULL, 1), ('RB850Gx2', NULL, 1), ('RB433GL', NULL, 1), ('RB433AH', NULL, 1), ('RB953GS-5HnT-RP', NULL, 1), ('RB493AH', NULL, 1), ('RB435G', NULL, 1), ('RB493G', NULL, 1), ('RB800', NULL, 1), ('RB433UAH', NULL, 1), ('RB411UAHL', NULL, 1), ('RB493', NULL, 1), ('RB711-5Hn', NULL, 1), ('RB711-2Hn', NULL, 1), ('RB2011L', NULL, 1), ('RB2011LS', NULL, 1), ('RB433L', NULL, 1), ('RB2011UiAS-2HnD', NULL, 1), ('RB711-5Hn-U', NULL, 1), ('RB411UAHR', NULL, 1), ('RB433UAHL', NULL, 1), ('RB711GA-5HnD', NULL, 1), ('RB711A-5Hn-M', NULL, 1), ('CCR1016-12G-BU', NULL, 1), ('RB411R', NULL, 1), ('RB711-5Hn-M', NULL, 1), ('RB600A', NULL, 1), ('Crossroads', NULL, 1), ('RB411A', NULL, 1), ('CA411-711', NULL, 1), ('CA150', NULL, 1), ('CA433U', NULL, 1), ('CA493', NULL, 1), ('CA800', NULL, 1), ('CAOTS', NULL, 1), ('CAOTU', NULL, 1), ('S-RJ01', NULL, 1), ('S-85DLC05D', NULL, 1), ('S-31DLC20D', NULL, 1), ('S-3553LC20D', NULL, 1), ('S+85DLC03D', NULL, 1), ('S+31DLC10D', NULL, 1), ('S+2332LC10D', NULL, 1), ('SFP+ 1m direct attach cable', NULL, 1), ('SFP+ 3m direct attach cable', NULL, 1), ('R11e-2HnD', NULL, 1), ('R11e-2HPnD', NULL, 1), ('R11e-5HnD', NULL, 1), ('R11e-5HacD', NULL, 1), ('R2SHPn', NULL, 1), ('R5SHPn', NULL, 1), ('R52nM', NULL, 1), ('R52H', NULL, 1), ('R52Hn', NULL, 1), ('RB14e', NULL, 1), ('RB14eU', NULL, 1), ('RB44Ge', NULL, 1), ('RB502', NULL, 1), ('RB604', NULL, 1), ('IAMP1E', NULL, 1), ('R5H', NULL, 1), ('R5nH', NULL, 1), ('R2N', NULL, 1), ('R52N', NULL, 1), ('RB44GV', NULL, 1), ('R52', NULL, 1), ('IAMP1', NULL, 1), ('FTC', NULL, 1), ('Flex-guide', NULL, 1), ('MMCX-RPSMA pigtail', NULL, 1), ('2.4Ghz Dipole', NULL, 1), ('RB2011 mount', NULL, 1), ('48 to 24V PoE Converter', NULL, 1), ('18POW', NULL, 1), ('24HPOW', NULL, 1), ('48POW', NULL, 1), ('5VUSB', NULL, 1), ('RBPOE', NULL, 1), ('RBGPOE', NULL, 1), ('ACUFL', NULL, 1), ('ACMMCX', NULL, 1), ('ACSWI', NULL, 1), ('ACSWIM', NULL, 1), ('Radome Cover Kit', NULL, 1), ('mANT30 PA', NULL, 1), ('mANT30', NULL, 1); INSERT INTO netdevicetypes (name, passive) VALUES ('router', 0), ('switch', 0), ('antenna', 1), ('access-point', 0), ('PON OLT', 0), ('PON ONT', 0), ('PON splitter', 1), ('GSM modem', 0), ('DSL modem', 0), ('power line adapter', 0), ('IPTV decoder', 0), ('cable joint', 1), ('VoIP gateway', 0), ('fiber cassette', 1), ('splice tray', 1); INSERT INTO netdevicemodels (name, alternative_name, netdeviceproducerid) VALUES ('3391-A', 'SR71A', 2), ('AF-24', 'AIRFIBER 1.4GBPS+ BACKHAUL 24GHZ (SHIPPED AS SINGLES)', 2), ('AF-5', 'AIRFIBER 1GBPS+ BACKHAUL 5.4-5.8GHZ', 2), ('AG-HP-2G16', '2.4GHZ AIRGRID AIRMAX 16DBI', 2), ('AG-HP-2G20', '5PK 2.4GHZ AIRGRID AIRMAX 20DBI', 2), ('AG-HP-5G23', '5PK 5GHZ AIRGRID AIRMAX 23DBI', 2), ('AG-HP-5G27', '5PK 5GHZ AIRGRID AIRMAX 27DBI', 2), ('AIRCAM', 'AIRCAM', 2), ('AIRCAM-3', 'AIRCAM 3-PACK', 2), ('AIRCAM-DOME', 'AIRCAM DOME', 2), ('AIRCAM-DOME-3', 'AIRCAM-DOME 3PK SOLUTION 3 PACK', 2), ('AIRCAM-MINI', 'AIRCAM-MINI VIDEO SOLUTION', 2), ('AIRGATEWAY', 'AIRGATEWAY INDOOR AP', 2), ('AIRGATEWAY-LR', 'AIRGATEWAY INDOOR AP LONG RANGE ANTENNA', 2), ('AIRROUTER', 'AIRROUTER INDOOR AP', 2), ('AIRROUTER-HP', 'AIRROUTER INDOOR AP HP EXT ANT', 2), ('AIRVISION-C', 'AIRVISION NVR CONTROLLER', 2), ('AM-2G15-120', '2.3-2.7GHZ AIRMAX BASE STATION 15DBI 120 DEG W/ ROCKET KIT', 2), ('AM-2G16-90', '2.3-2.7GHZ AIRMAX BASE STATION 16DBI 90DEG W/ ROCKET KIT', 2), ('AM-3G18-120', '3.3-3.8GHZ AIRMAX BASE STATION 18DBI 120 DEG W/ROCKET KIT', 2), ('AM-5AC21-60', '5G AIRMAX AC 60 DEGREES SECTOR ANTENNA 21DBI', 2), ('AM-5AC22-45', '5G AIRMAX AC 45 DEGREES SECTOR ANTENNA 22DBI', 2), ('AM-5G16-120', '4.9-5.9GHZ AIRMAX BASE STATION 16DBI 120 DEG W/ ROCKET KIT', 2), ('AM-5G17-90', '4.9-5.9GHZ AIRMAX BASE STATION 17DBI 90 DEG W/ROCKET KIT', 2), ('AM-5G19-120', '4.9-5.9GHZ AIRMAX BASE STATION 19DBI 120 DEG W/ ROCKET KIT', 2), ('AM-5G20-90', '4.9-5.9GHZ AIRMAX BASE STATION 20DBI 90 DEG W/ ROCKET KIT', 2), ('AM-9M13-120', '900MHZ AIRMAX BASE STATION 13DBI', 2), ('AM-M-V5G-TI', '5GHZ VARIABLE BEAM BASE STATION MID-GAIN 60-120DEG TI W/ ROCKET KIT', 2), ('AMO-2G10', '2GHZ AIRMAX DUAL OMNI 10DBI W/ ROCKET KIT', 2), ('AMO-2G13', '2GHZ AIRMAX DUAL OMNI 13DBI W/ ROCKET KIT', 2), ('AMO-3G12', '3GHZ AIRMAX DUAL OMNI 12DBI W/ ROCKET KIT', 2), ('AMO-5G10', '5GHZ AIRMAX DUAL OMNI 10DBI W/ ROCKET KIT', 2), ('AMO-5G13', '5GHZ AIRMAX DUAL OMNI 13DBI W/ ROCKET KIT', 2), ('AM-V2G-TI', '2GHZ VARIABLE BEAM BASE STATION 60-120DEG TITANIUM W/ROCKET KIT', 2), ('AM-V5G-TI', '5GHZ VARIABLE BEAM BASE STATION 60-120DEG TITANIUM W/ROCKET KIT', 2), ('AMY-9M16-2', '900MHZ AIRMAX YAGI ANTENNA 16DBI W/ ROCKET KIT 2-PACK', 2), ('BM2-TI', '2.4GHZ BULLET AIRMAX TITANIUM POE/ADAPTER INCL', 2), ('BULLET5', 'BULLET5 CPE', 2), ('BULLETM2-HP', '2.4GHZ BULLET AIRMAX HP', 2), ('BULLETM5-HP', '5GHZ BULLET AIRMAX HP', 2), ('BULLETM5-TI', '5GHZ BULLET AIRMAX TITANIUM POE/ADAPTER INCL', 2), ('ER-8', 'EDGEROUTER 8 5PORT ROUTER', 2), ('ERLITE-3', 'EDGEROUTER LITE 3PORT', 2), ('ERPOE-5', 'EDGEROUTER POE 5PORT ROUTER WITH POE', 2), ('ERPRO-8', 'EDGEROUTER PRO 8 8PORT ROUTER 2 SFP', 2), ('ES-24-250W', 'EDGE SWITCH 24 250W', 2), ('ES-24-500W', 'EDGE SWITCH 24PORT 500W', 2), ('ES-48-500W', 'EDGE SWITCH 48PORT 500W', 2), ('ES-48-750W', 'EDGE SWITCH 48PORT 750W', 2), ('ETH-SP', 'ETHERNET SURGE PROTECTOR', 2), ('INS-8023AF-I', 'INSTANT 8023AF ADAPTER INDOOR', 2), ('INS-8023AF-O', 'INSTANT 8023AF ADAPTER OUTDOOR', 2), ('LOCOM2', '2.4GHZ LOCO MIMO AIRMAX', 2), ('LOCOM5', '5GHZ LOCO MIMO AIRMAX', 2), ('LOCOM900', '900MHZ LOCO MIMO AIRMAX', 2), ('MFI-CS', 'MFI CURRENT SENSOR', 2), ('MFI-DS', 'MFI DOOR SENSOR', 2), ('MFI-LD', 'IN-WALL MANAGEABLE SWITCH/ DIMMER', 2), ('MFI-MPW', 'IN-WALL MANAGEABLE OUTLET', 2), ('MFI-MSC', 'MFI CEILING MOUNT MOTION SENSOR', 2), ('MFI-MSW', 'MFI WALL MOUNT MOTION SENSOR', 2), ('MFI-THS', 'MFI TEMPERATURE SENSOR', 2), ('MPORT', 'MFI MPORT', 2), ('MPORT-S', 'MFI MPORT SERIAL', 2), ('MPOWER', 'MFI 3PORT POWER EU & US WIFI', 2), ('MPOWER-MINI', 'MFI 1PORT POWER EU & US WIFI', 2), ('MPOWER-PRO', 'MFI 8PORT US 6PORT EU POWER WIFI/ETH', 2), ('NANOMOUNT', 'NANOMOUNT', 2), ('NBE-M5-19', '5GHZ NANOBEAM AIRMAX 19DB', 2), ('NBE-M5-400', '5PK 5GHZ NANOBEAM AIRMAX 400MM', 2), ('NBE-RAD-400', 'NANOBEAM RADOME 400MM', 2), ('NBE-WMK', 'NANOBEAM WALL MOUNT KIT', 2), ('NBM3', '3.5GHZ NANOBRIDGE AIRMAX 22DBI 2 PARTS NBM3+NB-OD3', 2), ('NBM365', '3.65GHZ NANOBRIDGE AIRMAX 22DBI 2 PARTS NBM365+NB-OD3', 2), ('NBM9', '900MHZ NANOBRIDGE AIRMAX 13DBI 2 PARTS NBM9+NB-OD9', 2), ('NSM2', '2.4GHZ NANO MIMO AIRMAX', 2), ('NSM3', '3.5GHZ NANO MIMO AIRMAX', 2), ('NSM365', '3.65GHZ NANOBRIDGE AIRMAX 22DBI 2 PARTS NBM365+NB-OD3', 2), ('NSM5', '5GHZ NANO MIMO AIRMAX', 2), ('PBM10', '10GHZ POWERBRIDGE MIMO AIRMAX 2 PARTS - PBM10 $275 + PBM10-DISH 75', 2), ('PBM3', '3.5GHZ POWERBRIDGE MIMO AIRMAX', 2), ('PBM365', '3.65GHZ POWERBRIDGE MIMO AIRMAX', 2), ('PBM5', '5GHZ POWERBRIDGE MIMO AIRMAX', 2), ('PICOM2-HP', '2.4GHZ PICO AIRMAX HP', 2), ('POE-24-12W', 'POE INJECTOR 24VDC 12W', 2), ('POE-24-24W', 'POE INJECTOR 24VDC 24W', 2), ('POE-48-24W', 'POE INJECTOR 48VDC 24W', 2), ('POE-48-24W-G', 'POE INJECTOR 48VDC 24W ROCKETTI UAP-PRO', 2), ('POE-50-60W', 'POE INJECTOR 50V 60W AIRFIBER POE', 2), ('PS2-17D', 'POWERSTATION2-17D CPE', 2), ('PS5-22V', 'POWERSTATION5-22V CPE', 2), ('RAD-2RD', 'RADOME 2 ROCKETDISH EAR99 RADOME FOR ROCKETDISH', 2), ('RAD-3RD', 'RADOME 3 ROCKETDISH EAR99 RADOME FOR ROCKETDISH', 2), ('RD-2G24', '2GHZ ROCKET DISH 24DBI W/ ROCKET KIT', 2), ('RD-3G26', '3GHZ ROCKET DISH 26DBI W/ ROCKET KIT', 2), ('RD-5G30', '5GHZ ROCKET DISH 30DBI W/ ROCKET KIT', 2), ('RD-5G34', '5GHZ ROCKET DISH 34DBI W/ROCKET KIT', 2), ('RM2-TI', '2.4GHZ ROCKET MIMO AIRMAX TITANIUM', 2), ('RM5-TI', '5GHZ ROCKET MIMO AIRMAX TITANIUM', 2), ('ROCKETM2', '2.4GHZ ROCKET MIMO AIRMAX', 2), ('ROCKETM3', '3.5GHZ ROCKET MIMO AIRMAX', 2), ('ROCKETM365', '3.65GHZ ROCKET MIMO AIRMAX', 2), ('ROCKETM5', '5GHZ ROCKET MIMO AIRMAX', 2), ('ROCKETM9', '900MHZ ROCKET MIMO AIRMAX', 2), ('SR2', 'SR2 MINI PCI PCBA', 2), ('SR5', 'SR5 MINI PCI PCBA', 2), ('SR71-15', '11A/N 5GHZ 27DBM 2X2 OUTDOOR', 2), ('SR71E', '11A/B/G/N 200MW 2X2 EXT. ANTENNA', 2), ('SRC', 'SRC CARDBUS', 2), ('TC-CARRIER', 'TOUGH CABLE LEVEL 2 1000 BOX', 2), ('TC-CON', 'TOUGH CABLE CONNEXTRS X 2400 100PCS/BOX', 2), ('TC-GND', 'TOUGH CABLE CONNECTOR GROUND X 1000', 2), ('TC-PRO', 'TOUGH CABLE LEVEL 1 1000 BOX', 2), ('TS-16-CARRIER', 'TOUGHSWITCH 16PORT POE PRO RACKMOUNT', 2), ('TS-5-POE', 'TOUGHSWITCH 5PORT POE', 2), ('TS-8-PRO', 'TOUGHSWITCH 8PORT POE PRO', 2), ('UAP', 'UNIFI AP', 2), ('UAP-3', 'UNIFI AP 3-PACK', 2), ('UAP-AC', 'UNIFI AP 802.11AC', 2), ('UAP-AC-3', 'UNIFI AP AC 3PK', 2), ('UAP-AC-OUTDOOR', 'UNIFI AP AC OUTDOOR', 2), ('UAP-LR', 'UNIFI AP LONG RANGE', 2), ('UAP-LR-3', 'UNIFI AP LONG RANGE 3-PACK', 2), ('UAP-OUTDOOR+', 'UNIFI AP OUTDOOR+ XRF', 2), ('UAP-OUTDOOR-5', 'UNIFI AP OUTDOOR 5GHZ', 2), ('UAP-PRO', 'UNIFI AP PRO GIGE 802.3AF DUAL RADIO', 2), ('UAP-PRO-3', 'UNIFI AP PRO GIGE 802.3AF DUAL RADIO 3PK', 2), ('UB-AM', '10PK UBIQUITI ANTENNA MOUNT', 2), ('USG', 'UNIFI SECURITY GATEWAY', 2), ('UVC', 'UNIFI VIDEO CAMERA IR', 2), ('UVC-3', 'UNIFI VIDEO CAMERA IR 3PK', 2), ('UVC-DOME', 'UNIFI VIDEO CAMERA DOME IR', 2), ('UVC-DOME-3', 'UNIFI VIDEO CAMERA DOME IR 3-PACK', 2), ('UVC-NVR', 'AIRVISION NVR CONTROLLER', 2), ('UVC-PRO', 'UNIFI VIDEO CAMERA PRO', 2), ('UVC-PRO-C', 'UNIFI CAMERA PRO CABLE ACCESSORY', 2), ('UVC-PRO-M', 'UNIFI CAMERA PRO MOUNT ACCESSORY', 2), ('UVP', 'UNIFI VOIP PHONE', 2), ('WS5', 'WS5 SUBSCRIBER', 2), ('WSM5', 'WISPSTATION M SERIES 5GHZ AIRMAX', 2), ('XR2', 'MINI PCI 600MW 2.4GHZ', 2), ('XR5', 'MINI PCI 600MW 5GHZ', 2), ('XR7', 'XR7 MINI PCI PCBA', 2), ('XR9', 'MINI PCI 600MW 900MHZ', 2); INSERT INTO dbinfo (keytype, keyvalue) VALUES ('dbversion', '2026021800'); COMMIT;