Internal:Datenbankstruktur

Aus BRODEL Wiki
Wechseln zu: Navigation, Suche

Das System basiert auf einer PostgeSQL-Datenbank mit der folgende Struktur.

Systemtabellen

session

Ablage der Session-Daten von Catalyst.

CREATE TABLE session (
  id CHAR(72) PRIMARY KEY,
  session_data TEXT,
  expires INTEGER
);

Maßeinheiten

CREATE TABLE measure (
  key CHAR(3) NOT NULL PRIMARY KEY,
  name VARCHAR(32),
  si_symbol_htm VARCHAR(16),
  si_symbol_pdf VARCHAR(16),
  base_unit CHAR(3),
  factor FLOAT
);

Initialwerte:

INSERT INTO measure VALUES('ANN', 'year', 'Y', 'Y', 'SEC', 31536000);
INSERT INTO measure VALUES('ARE', 'are', , , 'MTK', 100);
INSERT INTO measure VALUES('AS', 'assortment', , , 'C62', NULL);
INSERT INTO measure VALUES('BE', 'bunch', , , 'C62', NULL);
INSERT INTO measure VALUES('BG', 'bag', , , 'C62', NULL);
INSERT INTO measure VALUES('BJ', 'bucket', , , 'MTQ', NULL);
INSERT INTO measure VALUES('BLL', 'barrel', , , 'MTQ', 0.1589873);
INSERT INTO measure VALUES('BO', 'bottle', , , 'MTQ', NULL);
INSERT INTO measure VALUES('BX', 'box', , , 'C62', NULL);
INSERT INTO measure VALUES('C62', 'piece', , , 'C62', 1);
INSERT INTO measure VALUES('CA', 'can', , , 'MTQ', NULL);
INSERT INTO measure VALUES('CG', 'card', , , 'C62', 1);
INSERT INTO measure VALUES('CL', 'coil', , , 'MTR', NULL);
INSERT INTO measure VALUES('CLT', 'centilitre', 'cl', 'cl', 'MTQ', 0.00001);
INSERT INTO measure VALUES('CMK', 'square centimetre', 'cm²', 'cm²', 'MTK', 0.0001);
INSERT INTO measure VALUES('CMT', 'centimetre', 'cm', 'cm', 'MTR', 0.01);
INSERT INTO measure VALUES('CQ', 'cartridge', , , 'C62', NULL);
INSERT INTO measure VALUES('CT', 'cardboard box', , , 'C62', NULL);
INSERT INTO measure VALUES('CWI', 'centner', , , 'KGM', 50);
INSERT INTO measure VALUES('D97', 'pallet', , , 'C62', NULL);
INSERT INTO measure VALUES('DAY', 'day', 'd', 'd', 'SEC', 86400);
INSERT INTO measure VALUES('DLT', 'decilitre', 'dl', 'dl', 'MTQ', 0.0001);
INSERT INTO measure VALUES('DTN', 'double centner', , , 'KGM', 100);
INSERT INTO measure VALUES('DZN', 'dozen', , , 'C62', 12);
INSERT INTO measure VALUES('FOT', 'foot', , , 'MTR', 0.3048);
INSERT INTO measure VALUES('GRM', 'gramme', 'g', 'g', 'KGM', 0.001);
INSERT INTO measure VALUES('GRO', 'gross', , , 'C62', NULL);
INSERT INTO measure VALUES('HAR', 'hectare', 'ha', 'ha', 'MTK', 10000);
INSERT INTO measure VALUES('HLT', 'hectolitre', 'hl', 'hl', 'MTQ', 0.1);
INSERT INTO measure VALUES('HUR', 'hour', 'h', 'h', 'SEC', 3600);
INSERT INTO measure VALUES('KGM', 'kilogramme', 'kg', 'kg', 'KGM', 1);
INSERT INTO measure VALUES('KMK', 'square kilometre', 'km²', 'km²', 'MTK', 1000000);
INSERT INTO measure VALUES('KTM', 'kilometre', 'km', 'km', 'MTR', 1000);
INSERT INTO measure VALUES('LTR', 'litre', 'l', 'l', 'MTQ', 1000);
INSERT INTO measure VALUES('MIN', 'minute', 'min', 'min', 'SEC', 60);
INSERT INTO measure VALUES('MIO', 'million', , , 'C62', 1000000);
INSERT INTO measure VALUES('MLT', 'millilitre', 'ml', 'ml', 'MTQ', 0.000000001);
INSERT INTO measure VALUES('MMK', 'square millimetre', 'mm²', 'mm²', 'MTK', 0.000001);
INSERT INTO measure VALUES('MMT', 'millimetre', 'mm', 'mm', 'MTR', 0.001);
INSERT INTO measure VALUES('MON', 'month', 'mon', 'mon', 'SEC', 2592000);
INSERT INTO measure VALUES('MTK', 'square metre', 'm²', 'm²', 'MTK', 1);
INSERT INTO measure VALUES('MTQ', 'cubic metre', 'm³', 'm³', 'MTQ', 1);
INSERT INTO measure VALUES('MTR', 'meter', 'm', 'm', 'MTR', 1);
INSERT INTO measure VALUES('PA', 'pack', , , 'C62', NULL);
INSERT INTO measure VALUES('PG', 'plate', , , 'C62', NULL);
INSERT INTO measure VALUES('PK', 'package', , , 'C62', NULL);
INSERT INTO measure VALUES('PR', 'pair', , , 'C62', 2);
INSERT INTO measure VALUES('RG', 'ring', , , 'MTR', NULL);
INSERT INTO measure VALUES('RO', 'roll', , , 'MTR', NULL);
INSERT INTO measure VALUES('SEC', 'second', 'sec', 'sec', 'SEC', 1);
INSERT INTO measure VALUES('SET', 'set', , , 'C62', NULL);
INSERT INTO measure VALUES('SMI', 'mile', , , 'MTR', NULL);
INSERT INTO measure VALUES('ST', 'sheet', , , 'C62', 1);
INSERT INTO measure VALUES('TN', 'tin', , , 'C62', 1);
INSERT INTO measure VALUES('TNE', 'ton', 't', 't', 'KGM', 1000);
INSERT INTO measure VALUES('TU', 'tube', , , 'KGM', NULL);


Systemweite Währungen

CREATE TABLE currency (
  key CHAR(3) NOT NULL PRIMARY KEY,
  name VARCHAR(32),
  format VARCHAR(32),
  format_htm VARCHAR(32),
  format_pdf VARCHAR(32),
  format_txt VARCHAR(32),
  exchange_rate FLOAT,
  last_update DATE
);

Initialwerte

INSERT INTO currency VALUES('EUR', 'Euro', '%0.2f', '%0.2f €', '%0.2f €', '%0.2f €', 1, NULL);
INSERT INTO currency VALUES('USD', 'United States Dollars', '%0.2f', '$ %0.2f', '$ %0.2f', '$ %0.2f', NULL, NULL);
INSERT INTO currency VALUES('GBP', 'United Kingdom Pounds', '%0.2f', '%0.2f £', '£ %0.2f', '£ %0.2f', NULL,  NULL);
INSERT INTO currency VALUES('CHF', 'Switzerland Francs', '%0.2f', '%0.2f CHF', '%0.2f CHF', '%0.2f CHF', NULL, NULL);

Mandanten-Tabellen

Mandant

CREATE TABLE mandator (
  id BIGSERIAL PRIMARY KEY,

  sitename VARCHAR(24) UNIQUE NOT NULL,
  hostname VARCHAR(96),
  title VARCHAR(32),
  slogan VARCHAR(96),

  organisation_name VARCHAR(96),
  street VARCHAR(96),
  postal_code VARCHAR(10),
  city VARCHAR(96),
  country VARCHAR(24),
  fon VARCHAR(32),
  fax VARCHAR(32),
  email VARCHAR(96),
  website VARCHAR(96),
  vat_id VARCHAR(16),

  default_vat_rate DECIMAL(5,1),
  reduced_vat_rate DECIMAL(5,1),

  shop_view SMALLINT DEFAULT 0,
  shop_cart SMALLINT DEFAULT 0,
  shop_attribs SMALLINT DEFAULT 0,
  
  backend_theme VARCHAR(24) DEFAULT 'redmond',
  backend_icons VARCHAR(24) DEFAULT 'nuvola',

  admin_email VARCHAR(96),
  email_hoster VARCHAR(24),
  imap_host VARCHAR(96),
  imap_port SMALLINT,
  imap_user VARCHAR(96),
  imap_pass VARCHAR(96),
  smtp_host VARCHAR(96),
  smtp_port SMALLINT,
  smtp_auth SMALLINT,
  folder_sent VARCHAR(24),
  folder_received VARCHAR(24),

  status SMALLINT,
  attribs SMALLINT,
  message_info TEXT,
  message_error TEXT,

  contract_type SMALLINT,
  contract_start DATE,
  contract_end DATE,
  hotline_number VARCHAR(24),
  remarks TEXT,

  available_currencies VARCHAR(96) DEFAULT 'EUR',
  default_currency VARCHAR(3) DEFAULT 'EUR',
  available_languages VARCHAR(96) DEFAULT 'de',
  default_language VARCHAR(5) DEFAULT 'de',
  available_modules TEXT DEFAULT 'administration system',
  repository TEXT,
  passphrase TEXT,
  
  cache_type SMALLINT DEFAULT 0, -- added
  cache_server TEXT, -- added
  cache_sshkey TEXT, -- added
  cache_domain TEXT, -- added
);

Dateien

CREATE TABLE mandator_template (
  id BIGSERIAL PRIMARY KEY,
  mandator BIGINT NOT NULL,
  filename VARCHAR(96),
  filetype VARCHAR(96),
  filesize BIGINT,
  description TEXT,
  padding_top SMALLINT DEFAULT 0, -- deleted
  padding_bottom SMALLINT DEFAULT 1, -- deleted
  padding_left SMALLINT DEFAULT 1, -- deleted
  padding_right SMALLINT DEFAULT 0, -- deleted
  file BYTEA,

  FOREIGN KEY(mandator) REFERENCES mandator(id)
);

Lieferarten

CREATE TABLE mandator_delivery_method (
  id BIGSERIAL PRIMARY KEY,
  mandator BIGINT NOT NULL,
  dm_position SMALLINT,
  dm_type SMALLINT,
  dm_lang VARCHAR(5),
  dm_name VARCHAR(32),
  dm_description TEXT,
  dm_information TEXT,
  dm_max_weight FLOAT,
  dm_max_length FLOAT,
  dm_packaging DECIMAL(10,2),
  dm_shipping DECIMAL(10,2),
  dm_cod_charge DECIMAL(10,2),
  dm_shp BOOLEAN,
  dm_erp BOOLEAN,

  FOREIGN KEY(mandator) REFERENCES mandator(id)
);

Zahlungsarten

CREATE TABLE mandator_payment_method (
  id BIGSERIAL PRIMARY KEY,
  mandator BIGINT NOT NULL,
  pm_position SMALLINT,
  pm_type SMALLINT,
  pm_lang VARCHAR(5),
  pm_name VARCHAR(32),
  pm_description TEXT,
  pm_information TEXT,

  pm_max_amount FLOAT,
  pm_days INTEGER,
  pm_discount FLOAT,
  pm_discount_days INTEGER,
  pm_shp BOOLEAN,
  pm_erp BOOLEAN,

  FOREIGN KEY(mandator) REFERENCES mandator(id)
);

Systemvariablen

CREATE TABLE config (
  id BIGSERIAL PRIMARY KEY,
  mandator BIGINT NOT NULL,
  key VARCHAR(32),
  type INTEGER,
  value TEXT,

  FOREIGN KEY(mandator) REFERENCES mandator(id)
);

Textbausteine

CREATE TABLE text_module (
  id BIGSERIAL PRIMARY KEY,
  mandator BIGINT NOT NULL,
  key VARCHAR(50),
  language VARCHAR(5),
  title TEXT,
  text TEXT,

  FOREIGN KEY(mandator) REFERENCES mandator(id)
);

Kostenarten und Kostenträger

attributes is bitarray

Bit 0: cost/revenue object, Bit 1: cost center / profit center, Bit 2: cost type, Bit 3: revenue type

CREATE TABLE costing (
  id BIGSERIAL PRIMARY KEY,
  mandator BIGINT NOT NULL,
  ptype SMALLINT DEFAULT 0,
  number VARCHAR(8),
  name VARCHAR(96),
  description TEXT,
  measure CHAR(3), -- deleted
  reposting_rules TEXT, -- deleted
  activity SMALLINT, -- deleted
  hourly_rate NUMERIC(10,2), -- deleted
  employee BIGINT, -- deleted

  FOREIGN KEY(mandator) REFERENCES mandator(id)
);

Wechselkurse

CREATE TABLE exchange (
  id BIGSERIAL PRIMARY KEY,
  mandator BIGINT NOT NULL,
  ex_currency CHAR(3) NOT NULL,
  ex_date DATE,
  ex_rate_sales FLOAT,
  ex_rate_purchse FLOAT,
  ex_modified_by BIGINT,
  ex_modified_at TIMESTAMP,

  FOREIGN KEY(mandator) REFERENCES mandator(id)
);

Haupttabellen

Master

CREATE TABLE master (
  id BIGSERIAL PRIMARY KEY,
  class SMALLINT NOT NULL,
  type SMALLINT NOT NULL,
  reference VARCHAR(32) NOT NULL,
  subject VARCHAR(255),
  date TIMESTAMP,
  priority SMALLINT NOT NULL,
  status SMALLINT NOT NULL,
  mandator BIGINT NOT NULL,
  read_perm SMALLINT NOT NULL, -- deprecated
  edit_perm SMALLINT NOT NULL, -- deprecated
  created_by BIGINT,
  created_at TIMESTAMP,
  modified_by BIGINT,
  modified_at TIMESTAMP,
  foreign_id BIGINT,

  FOREIGN KEY(mandator) REFERENCES mandator(id)
);
ALTER SEQUENCE master_id_seq RESTART WITH 10000; 

Ein paar Beschleunigungen:

CREATE INDEX master_mandator_class_idx on master (mandator, class, created_at);
CREATE INDEX master_reference_idx on master (reference);
CREATE INDEX master_subject_idx on master (subject);
CREATE INDEX master_type_idx on master (type);
CREATE INDEX master_status_idx on master (status);
CREATE INDEX master_created_idx on master (created_at);

Beispiel-Analyse:

EXPLAIN ANALYZE select * from master 
where mandator=179 
 and (class=32 or class=40 or class=42 or class=43) 
 and (status & 131)=128
 and (type & 4)=4
order by reference desc;

Querverweise

CREATE TABLE master_relation (
  id bigint NOT NULL,
  parent bigint NOT NULL,
  role smallint, -- changed
  remarks text,
  vtype smallint,
  position smallint,

  PRIMARY KEY (id,parent),
  FOREIGN KEY(id) REFERENCES master(id),
  FOREIGN KEY(parent) REFERENCES master(id)
);

Dateien

CREATE TABLE master_attachment (
  id BIGSERIAL PRIMARY KEY,
  master BIGINT NOT NULL,
  lft SMALLINT,
  rgt SMALLINT,
  title TEXT,
  description TEXT,
  attributes SMALLINT DEFAULT 0,
  web BOOLEAN,
  top BOOLEAN,
  pdf BOOLEAN,
  version SMALLINT DEFAULT 1,
  checkedin_by BIGINT,
  checkedin_at TIMESTAMP,
  checkedout_by BIGINT,
  checkedout_at TIMESTAMP,
  filename VARCHAR(96),
  filetype VARCHAR(96),
  filesize BIGINT,
  file BYTEA,
  repository TEXT,
  crypt_key BYTEA,
  crypt_cipher TEXT,
  sha1 BYTEA,
  extra TEXT,
  thumb BYTEA,
  preview BYTEA,

  FOREIGN KEY(master) REFERENCES master(id)
);

Termin

ev_type: (0x0001) holiday, (0x0002) anniversary, (0x0004) birthday, (0x0008) appointment, (0x0008) task, (0x0100) private, ev_visibility: (0x0001) confidential, (0x0002) default, (0x0004) private, (0x0008) public ev_status: (0x0001) canceled, (0x0002) confirmed, (0x0004) tentative, (0x0008) finished

CREATE TABLE master_event (
  id bigserial PRIMARY KEY,
  master bigint NOT NULL,
  account bigint,
  ev_type smallint,
  ev_visibility smallint,
  ev_status smallint,
  ev_color integer,
  ev_is_allday boolean DEFAULT false,
  ev_start timestamp with time zone,
  ev_end timestamp with time zone,
  ev_reminder_alarm timestamp with time zone,
  ev_reminder_action text,
  ev_title text,
  ev_content text,

  FOREIGN KEY(master) REFERENCES master(id),
  FOREIGN KEY(account) REFERENCES master(id)
);

Termin

attribs is bitarray: Bit 1-2: sync direction (0 = no sync, 1 = intern->extern, 2 = extern->intern, 3 = both) Bit 3: extern created Bit 4: synced Bit 5: deleted

CREATE TABLE master_event_sync (
  account bigint NOT NULL,
  event bigint NOT NULL,
  evs_status smallint DEFAULT 0,  
  evs_sync_id text,

  PRIMARY KEY (account, event),
  FOREIGN KEY(account) REFERENCES master(id),
  FOREIGN KEY(event) REFERENCES master(id)
);

Stichwörter

CREATE TABLE master_keyword (
  id BIGSERIAL PRIMARY KEY,
  master BIGINT NOT NULL,
  keyword VARCHAR(32),
  keygroup SMALLINT DEFAULT 0,
  weight SMALLINT DEFAULT 0,

  FOREIGN KEY(master) REFERENCES master(id)
);

Merkmale

CREATE TABLE master_property (
  id BIGSERIAL PRIMARY KEY,
  master BIGINT NOT NULL,
  lft SMALLINT,
  rgt SMALLINT,
  ptype SMALLINT,
  lang VARCHAR(5),
  name TEXT,
  value TEXT,
  unit VARCHAR(10),
  web BOOLEAN,
  pdf BOOLEAN,
  erp BOOLEAN,
  identifier VARCHAR(24),

  FOREIGN KEY(master) REFERENCES master(id)
);

Volltext-Index

CREATE TABLE master_fulltext (
  master BIGINT NOT NULL,
  language VARCHAR(5),
  fulltext TSVECTOR,

  PRIMARY KEY (master,language),
  FOREIGN KEY(master) REFERENCES master(id)
);
CREATE INDEX master_fulltext_ftidx ON master_fulltext USING gist(fulltext);

Preis

CREATE TABLE master_price (
  id BIGSERIAL PRIMARY KEY,
  master BIGINT NOT NULL,
  ptype SMALLINT,
  contact BIGINT,
  position INTEGER,
  number VARCHAR(96),
  name VARCHAR(96),
  text TEXT,
  sales_area TEXT,
  price_group VARCHAR(24),
  valid_from DATE,
  valid_to DATE,
  ordering_unit CHAR(3),
  bu_per_ou FLOAT,
  quantity FLOAT DEFAULT 1,
  price DECIMAL(12,4), -- updated
  currency CHAR(3),
  vat_type SMALLINT,
  
  FOREIGN KEY(master) REFERENCES master(id),
  FOREIGN KEY(contact) REFERENCES master(id)
);