I am using Docker Desktop 4.12.0 (85629) for Windows (use Windows Sub-Linux), Windows 11 pro x64. Folder structure
For a DevOps task, I have file D:\github\spring_jwt\vy_notes\database_postgres_part_001_100.sql (1) has content
DROP TABLE IF EXISTS tenant;
CREATE TABLE tenant
(
id smallint primary key,
company_tax_code character varying(14),
period character varying(16), -- 2021070420220705
created timestamp with time zone
);
CREATE OR REPLACE FUNCTION set_id_tenant()
RETURNS trigger AS
$$
DECLARE
BEGIN
new.id = (select coalesce(max(id), -32769) from tenant) + 1;
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER trigger_insert_without_id_tenant
BEFORE INSERT
ON tenant
FOR EACH ROW
EXECUTE PROCEDURE set_id_tenant();
CREATE INDEX tenant_idx ON tenant (id);
COMMIT;
--------------------------------------------------------------------------------
-- 1.
DROP TABLE IF EXISTS account;
CREATE TABLE account
(
id smallint,
account_number character varying(32) not null unique,
account_name character varying(128) not null,
account_name_english character varying(128),
account_name_chinese character varying(128),
account_name_korean character varying(128),
description character varying(512),
parent_id smallint,
internal_code_id character varying(128),
grade smallint,
is_parent boolean not null,
account_category_kind smallint not null,
is_postable_in_foreign_currency boolean not null,
detail_by_account_object boolean not null,
account_object_type smallint,
detail_by_bank_account boolean not null,
detail_by_job boolean not null,
detail_by_job_kind smallint,
detail_by_project_work boolean not null,
detail_by_project_work_kind smallint,
detail_by_order boolean not null,
detail_by_order_kind smallint,
detail_by_contract boolean not null,
detail_by_contract_kind smallint,
detail_by_expense_item boolean not null,
detail_by_expense_item_kind smallint,
detail_by_department boolean not null,
detail_by_department_kind smallint,
detail_by_list_item boolean not null,
detail_by_list_item_kind smallint,
active_status boolean not null,
created timestamp with time zone,
created_by character varying(64),
modified timestamp with time zone,
modified_by character varying(64),
sort_internal_code_id character varying(128),
detail_by_pu_contract boolean not null,
detail_by_pu_contract_kind smallint,
tenant_id smallint,
PRIMARY KEY (id, tenant_id) -- ,
-- CONSTRAINT fk_tenant FOREIGN KEY (tenant_id) REFERENCES tenant (id)
);
CREATE OR REPLACE FUNCTION account_setId()
RETURNS trigger AS
$$
DECLARE
BEGIN
new.id = (select coalesce(max(id), -32769) from account where tenant_id = new.tenant_id) + 1;
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER account_trig_insertWithoutId
BEFORE INSERT
ON account
FOR EACH ROW
EXECUTE PROCEDURE account_setId();
CREATE INDEX account_idx ON account (id, tenant_id);
COMMENT ON TABLE public.account IS 'Danh mục tài khoản';
COMMENT ON COLUMN public.account.id IS 'PK Tài khoản';
COMMENT ON COLUMN public.account.account_number IS 'Số hiệu tài khoản';
COMMENT ON COLUMN public.account.account_name IS 'Tên tài khoản';
COMMENT ON COLUMN public.account.account_name_english IS 'Tên tài khoản bằng Tiếng Anh';
COMMENT ON COLUMN public.account.account_name_chinese IS 'Tên tài khoản bằng Tiếng Trung';
COMMENT ON COLUMN public.account.account_name_korean IS 'Tên tài khoản bằng Tiếng Hàn Quốc';
COMMENT ON COLUMN public.account.description IS 'Diễn giải';
COMMENT ON COLUMN public.account.parent_id IS 'TK Tổng hợp';
COMMENT ON COLUMN public.account.grade IS 'Cấp bậc';
COMMENT ON COLUMN public.account.is_parent IS 'Là TK tổng hợp';
COMMENT ON COLUMN public.account.account_category_kind IS 'Tính chất tài khoản: 0: Dư nợ; 1: Dư có; 2: Lưỡng tính';
COMMENT ON COLUMN public.account.is_postable_in_foreign_currency IS 'Có hạch toán ngoại tệ';
COMMENT ON COLUMN public.account.detail_by_account_object IS 'Chi tiết theo đối tượng';
COMMENT ON COLUMN public.account.account_object_type IS 'Loại đối tượng: 0 - Nhà cung cấp, 1- Khách hàng, 2- Nhân viên';
COMMENT ON COLUMN public.account.detail_by_bank_account IS 'Chi tiết theo tài khoản ngân hàng';
COMMENT ON COLUMN public.account.detail_by_job IS 'Chi tiết theo đối tượng tập hợp chi phí';
COMMENT ON COLUMN public.account.detail_by_job_kind IS '0 = Chỉ cảnh báo; 1 = Bắt buộc nhập';
COMMENT ON COLUMN public.account.detail_by_project_work IS 'Chi tiết theo công trình, vụ việc';
COMMENT ON COLUMN public.account.detail_by_project_work_kind IS '0 = Chỉ cảnh báo; 1 = Bắt buộc nhập';
COMMENT ON COLUMN public.account.detail_by_order IS 'Chi tiết theo đơn hàng';
COMMENT ON COLUMN public.account.detail_by_order_kind IS '0 = Chỉ cảnh báo; 1 = Bắt buộc nhập';
COMMENT ON COLUMN public.account.detail_by_contract IS 'Chi tiết theo hợp đồng';
COMMENT ON COLUMN public.account.detail_by_contract_kind IS '0 = Chỉ cảnh báo; 1 = Bắt buộc nhập';
COMMENT ON COLUMN public.account.detail_by_expense_item IS 'Chi tiết theo Khoản mục CP';
COMMENT ON COLUMN public.account.detail_by_expense_item_kind IS '0 = Chỉ cảnh báo; 1 = Bắt buộc nhập';
COMMENT ON COLUMN public.account.detail_by_department IS 'Chi tiết theo đơn vị';
COMMENT ON COLUMN public.account.detail_by_department_kind IS '0 = Chỉ cảnh báo; 1 = Bắt buộc nhập';
COMMENT ON COLUMN public.account.detail_by_list_item IS 'Chi tiết theo mã thống kê';
COMMENT ON COLUMN public.account.detail_by_list_item_kind IS '0 = Chỉ cảnh báo; 1 = Bắt buộc nhập';
COMMENT ON COLUMN public.account.active_status IS 'Trạng thái theo dõi';
COMMENT ON COLUMN public.account.sort_internal_code_id IS 'Cột dùng để sort trên báo cáo. Không sử dụng trên giao diện.';
COMMIT;
I have file docker-compose.yml
version: "3.9"
services:
postgres:
container_name: postgres-14
# image: postgres:latest
image: postgres:latest
restart: unless-stopped
ports:
- "5432:5432"
volumes:
- ./create-databases.sh:/docker-entrypoint-initdb.d/create-databases.sh
- ./db_persist:/var/lib/postgresql/data
environment:
- POSTGRES_HOST_AUTH_METHOD=trust
- POSTGRES_USER=postgres
- POSTGRES_PASSWORD=postgres
- POSTGRES_DB=tenant_single_db
hostname: postgres
pgadmin:
container_name: pgadmin4
image: dpage/pgadmin4:6.12
restart: unless-stopped
ports:
- "8080:80"
environment:
- POSTGRES_HOST_AUTH_METHOD=trust
- PGADMIN_DEFAULT_EMAIL=admin@example.com
- PGADMIN_DEFAULT_PASSWORD=123456a@
hostname: pgadmin
# docker compose up
# pgAdmin: http://localhost:8080/
I have file D:\github\spring_jwt\create-databases.sh (2) has content
#!/bin/bash
set -eu
function create_database() {
local database=$1
echo " Creating database '$database'"
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
CREATE DATABASE $database;
GRANT ALL PRIVILEGES ON DATABASE $database TO $POSTGRES_USER;
EOSQL
}
How to append content of to (1) to a position what stand before EOSQL in file (2)?