ddl.sql

-- PostgreSQL Benchmark
-- Code generated by Data Dictionary Builder (c) Valery Lobachev (https://github.com/valerylobachev/data-dictionary-builder).
-- Date: 2025-05-21 08:51:09



CREATE TABLE "fin_business_partners" (
  "id" varchar(20) PRIMARY KEY NOT NULL,
  "name" varchar(200) NOT NULL,
  "updated_by" varchar(20) NOT NULL,
  "updated_at" timestamptz NOT NULL
);

CREATE TABLE "fin_accounts" (
  "id" varchar(20) PRIMARY KEY NOT NULL,
  "name" varchar(200) NOT NULL,
  "updated_by" varchar(20) NOT NULL,
  "updated_at" timestamptz NOT NULL
);

CREATE TABLE "fin_materials" (
  "id" varchar(20) PRIMARY KEY NOT NULL,
  "name" varchar(200) NOT NULL,
  "next_std_price" decimal(20,2) NOT NULL,
  "updated_by" varchar(20) NOT NULL,
  "updated_at" timestamptz NOT NULL
);

CREATE TABLE "fin_material_periods" (
  "material_id" varchar(20) NOT NULL,
  "period" integer NOT NULL,
  "std_price" decimal(20,2) NOT NULL,
  "mov_avg_price" decimal(20,2) NOT NULL,
  "actual_price" decimal(20,2) NOT NULL,
  "sell_price" decimal(20,2) NOT NULL,
  "stock" decimal(20,3) NOT NULL,
  "updated_by" varchar(20) NOT NULL,
  "updated_at" timestamptz NOT NULL,
  PRIMARY KEY ("material_id", "period")
);

CREATE TABLE "fin_ledger_items" (
  "id" bigserial PRIMARY KEY NOT NULL,
  "period" integer NOT NULL,
  "doc_no" varchar(40) NOT NULL,
  "posting_date" date NOT NULL,
  "account_id" varchar(20) NOT NULL,
  "material_id" varchar(20),
  "business_partner_id" varchar(20),
  "debt_credit" varchar(1) NOT NULL,
  "amount" decimal(20,2) NOT NULL,
  "debt" decimal(20,2) NOT NULL,
  "credit" decimal(20,2) NOT NULL,
  "quantity" decimal(20,3) NOT NULL,
  "updated_by" varchar(20) NOT NULL,
  "updated_at" timestamptz NOT NULL
);

CREATE TABLE "bm_benchmarks" (
  "id" bigserial PRIMARY KEY NOT NULL,
  "name" varchar(200) NOT NULL,
  "date" timestamptz NOT NULL,
  "db_lib" varchar(40) NOT NULL,
  "customers" integer NOT NULL,
  "vendors" integer NOT NULL,
  "materials" integer NOT NULL,
  "users" integer NOT NULL,
  "start_year" integer NOT NULL,
  "years" integer NOT NULL,
  "operations" integer NOT NULL
);

CREATE TABLE "bm_metrics" (
  "id" bigserial PRIMARY KEY NOT NULL,
  "benchmark_id" bigint NOT NULL,
  "year" integer NOT NULL,
  "period" integer,
  "index" bigint NOT NULL,
  "user_no" integer NOT NULL,
  "action" varchar(20) NOT NULL,
  "material_id" varchar(20),
  "business_partner_id" varchar(20),
  "quantity" decimal(20,3),
  "price" decimal(20,2),
  "amount" decimal(20,2),
  "doc_no" varchar(40),
  "sale_doc_no" varchar(40),
  "cogs_doc_no" varchar(40),
  "posting_date" date,
  "duration" float8 NOT NULL
);

CREATE TABLE "bm_statistics" (
  "id" bigserial PRIMARY KEY NOT NULL,
  "benchmark_id" bigint NOT NULL,
  "action" varchar(20) NOT NULL,
  "year" integer,
  "month" integer,
  "total_count" bigint NOT NULL,
  "total_duration" float8 NOT NULL,
  "ops_per_sec" float8 NOT NULL,
  "min" float8 NOT NULL,
  "p50" float8 NOT NULL,
  "p75" float8 NOT NULL,
  "p95" float8 NOT NULL,
  "p99" float8 NOT NULL,
  "p99_9" float8 NOT NULL,
  "max" float8 NOT NULL,
  "mean" float8 NOT NULL,
  "std_dev" float8 NOT NULL
);


CREATE INDEX "fin_ledger_items_doc_no" ON "fin_ledger_items" ("period", "doc_no");

CREATE INDEX "fin_ledger_items_account_id" ON "fin_ledger_items" ("account_id");

CREATE INDEX "fin_ledger_items_business_partner_id" ON "fin_ledger_items" ("period", "business_partner_id");

CREATE INDEX "fin_ledger_items_acc_per" ON "fin_ledger_items" ("account_id", "period");

CREATE INDEX "fin_ledger_items_mat_acc" ON "fin_ledger_items" ("material_id", "account_id", "period");

CREATE INDEX "fin_ledger_items_mat_acc_dc" ON "fin_ledger_items" ("material_id", "account_id", "debt_credit", "period");


COMMENT ON TABLE "fin_business_partners" IS 'BusinessPartner';

COMMENT ON COLUMN "fin_business_partners"."id" IS 'Business partner id';

COMMENT ON COLUMN "fin_business_partners"."name" IS 'Name';

COMMENT ON COLUMN "fin_business_partners"."updated_by" IS 'Updated by user';

COMMENT ON COLUMN "fin_business_partners"."updated_at" IS 'Updated at';

COMMENT ON TABLE "fin_accounts" IS 'Account';

COMMENT ON COLUMN "fin_accounts"."id" IS 'Account id';

COMMENT ON COLUMN "fin_accounts"."name" IS 'Name';

COMMENT ON COLUMN "fin_accounts"."updated_by" IS 'Updated by user';

COMMENT ON COLUMN "fin_accounts"."updated_at" IS 'Updated at';

COMMENT ON TABLE "fin_materials" IS 'Material';

COMMENT ON COLUMN "fin_materials"."id" IS 'Material id';

COMMENT ON COLUMN "fin_materials"."name" IS 'Name';

COMMENT ON COLUMN "fin_materials"."next_std_price" IS 'Price';

COMMENT ON COLUMN "fin_materials"."updated_by" IS 'Updated by user';

COMMENT ON COLUMN "fin_materials"."updated_at" IS 'Updated at';

COMMENT ON TABLE "fin_material_periods" IS 'MaterialPeriod';

COMMENT ON COLUMN "fin_material_periods"."material_id" IS 'Material id';

COMMENT ON COLUMN "fin_material_periods"."period" IS 'Period';

COMMENT ON COLUMN "fin_material_periods"."std_price" IS 'Price';

COMMENT ON COLUMN "fin_material_periods"."mov_avg_price" IS 'Price';

COMMENT ON COLUMN "fin_material_periods"."actual_price" IS 'Price';

COMMENT ON COLUMN "fin_material_periods"."sell_price" IS 'Price';

COMMENT ON COLUMN "fin_material_periods"."stock" IS 'Quantity';

COMMENT ON COLUMN "fin_material_periods"."updated_by" IS 'Updated by user';

COMMENT ON COLUMN "fin_material_periods"."updated_at" IS 'Updated at';

COMMENT ON TABLE "fin_ledger_items" IS 'LedgerItem';

COMMENT ON COLUMN "fin_ledger_items"."id" IS 'Ledger item id';

COMMENT ON COLUMN "fin_ledger_items"."period" IS 'Period';

COMMENT ON COLUMN "fin_ledger_items"."doc_no" IS 'Doc no';

COMMENT ON COLUMN "fin_ledger_items"."account_id" IS 'Account id';

COMMENT ON COLUMN "fin_ledger_items"."material_id" IS 'Material id';

COMMENT ON COLUMN "fin_ledger_items"."business_partner_id" IS 'Business partner id';

COMMENT ON COLUMN "fin_ledger_items"."debt_credit" IS 'Debt/credit';

COMMENT ON COLUMN "fin_ledger_items"."amount" IS 'Amount';

COMMENT ON COLUMN "fin_ledger_items"."debt" IS 'Amount';

COMMENT ON COLUMN "fin_ledger_items"."credit" IS 'Amount';

COMMENT ON COLUMN "fin_ledger_items"."quantity" IS 'Quantity';

COMMENT ON COLUMN "fin_ledger_items"."updated_by" IS 'Updated by user';

COMMENT ON COLUMN "fin_ledger_items"."updated_at" IS 'Updated at';

COMMENT ON TABLE "bm_benchmarks" IS 'Benchmark';

COMMENT ON COLUMN "bm_benchmarks"."id" IS 'Benchmark id';

COMMENT ON COLUMN "bm_benchmarks"."name" IS 'Name';

COMMENT ON COLUMN "bm_benchmarks"."date" IS 'Date';

COMMENT ON COLUMN "bm_benchmarks"."customers" IS 'Customers';

COMMENT ON COLUMN "bm_benchmarks"."vendors" IS 'Vendors';

COMMENT ON COLUMN "bm_benchmarks"."materials" IS 'Materials';

COMMENT ON COLUMN "bm_benchmarks"."users" IS 'Users';

COMMENT ON COLUMN "bm_benchmarks"."start_year" IS 'Start year';

COMMENT ON COLUMN "bm_benchmarks"."years" IS 'Years';

COMMENT ON COLUMN "bm_benchmarks"."operations" IS 'Operations';

COMMENT ON TABLE "bm_metrics" IS 'Metric';

COMMENT ON COLUMN "bm_metrics"."id" IS 'Metric id';

COMMENT ON COLUMN "bm_metrics"."benchmark_id" IS 'Benchmark id';

COMMENT ON COLUMN "bm_metrics"."year" IS 'Year';

COMMENT ON COLUMN "bm_metrics"."period" IS 'Period';

COMMENT ON COLUMN "bm_metrics"."index" IS 'index';

COMMENT ON COLUMN "bm_metrics"."user_no" IS 'User no';

COMMENT ON COLUMN "bm_metrics"."action" IS 'Action';

COMMENT ON COLUMN "bm_metrics"."material_id" IS 'Material id';

COMMENT ON COLUMN "bm_metrics"."business_partner_id" IS 'Business partner id';

COMMENT ON COLUMN "bm_metrics"."quantity" IS 'Quantity';

COMMENT ON COLUMN "bm_metrics"."price" IS 'Amount';

COMMENT ON COLUMN "bm_metrics"."amount" IS 'Amount';

COMMENT ON COLUMN "bm_metrics"."doc_no" IS 'Doc no';

COMMENT ON COLUMN "bm_metrics"."sale_doc_no" IS 'Doc no';

COMMENT ON COLUMN "bm_metrics"."cogs_doc_no" IS 'Doc no';

COMMENT ON COLUMN "bm_metrics"."duration" IS 'Duration';

COMMENT ON TABLE "bm_statistics" IS 'Statistic';

COMMENT ON COLUMN "bm_statistics"."id" IS 'Statistics id';

COMMENT ON COLUMN "bm_statistics"."benchmark_id" IS 'Benchmark id';

COMMENT ON COLUMN "bm_statistics"."action" IS 'Action';

COMMENT ON COLUMN "bm_statistics"."year" IS 'Year';

COMMENT ON COLUMN "bm_statistics"."month" IS 'Month';

COMMENT ON COLUMN "bm_statistics"."total_count" IS 'Total count';

COMMENT ON COLUMN "bm_statistics"."total_duration" IS 'Total duration';

COMMENT ON COLUMN "bm_statistics"."ops_per_sec" IS 'Operations per second';

COMMENT ON COLUMN "bm_statistics"."min" IS 'Min';

COMMENT ON COLUMN "bm_statistics"."p50" IS 'p50';

COMMENT ON COLUMN "bm_statistics"."p75" IS 'p75';

COMMENT ON COLUMN "bm_statistics"."p95" IS 'p95';

COMMENT ON COLUMN "bm_statistics"."p99" IS 'p99';

COMMENT ON COLUMN "bm_statistics"."p99_9" IS 'p99_9';

COMMENT ON COLUMN "bm_statistics"."max" IS 'Max';

COMMENT ON COLUMN "bm_statistics"."mean" IS 'Mean';

COMMENT ON COLUMN "bm_statistics"."std_dev" IS 'Standard deviation';



ALTER TABLE "fin_material_periods" ADD CONSTRAINT "fin_material_periods_material_id" FOREIGN KEY ("material_id") REFERENCES "fin_materials" ("id");

ALTER TABLE "fin_ledger_items" ADD CONSTRAINT "fin_ledger_items_account_id" FOREIGN KEY ("account_id") REFERENCES "fin_accounts" ("id");

ALTER TABLE "fin_ledger_items" ADD CONSTRAINT "fin_ledger_items_material_id" FOREIGN KEY ("material_id") REFERENCES "fin_materials" ("id");

ALTER TABLE "fin_ledger_items" ADD CONSTRAINT "fin_ledger_items_business_partner_id" FOREIGN KEY ("business_partner_id") REFERENCES "fin_business_partners" ("id");

ALTER TABLE "bm_metrics" ADD CONSTRAINT "bm_metrics_benchmark_id" FOREIGN KEY ("benchmark_id") REFERENCES "bm_benchmarks" ("id");

ALTER TABLE "bm_statistics" ADD CONSTRAINT "bm_statistics_benchmark_id" FOREIGN KEY ("benchmark_id") REFERENCES "bm_benchmarks" ("id");