17.11. Heterogenous Settings

Several system behaviors can not be easily set up with a few number of relational tables. In order to overcome this barrier, PostERP uses JSON data structure to achieve this purpose. The DDL for table t127 is as follows:

CREATE TABLE t127 (
CONSTRAINT f4 FOREIGN KEY (f4) REFERENCES t13 (f1) ON UPDATE CASCADE ON DELETE CASCADE,
f1	INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,	--ID
f2	SMALLINT CHECK(f2 > 0) NOT NULL,	--setting type code
f3	BOOLEAN DEFAULT FALSE,	--enabled?
f4	SMALLINT,				--screen#
f5	VARCHAR(2048) NOT NULL,	--content
f99	VARCHAR(12)
);
		

Column f5 is for JSON string.

17.11.1. Heterogenous Setting 1

Column f2 with value 1 is meant to configure the mechanism to display on screens the journal# posted from related records. This section uses manufacturing system as an example for explanation.

COPY t127 FROM stdin;
1	1	t	6091	["f1","post_sales",["t_220","t_221","t_222","t_223"]]	tw
2	1	t	6098	["f1","post_recall",["t_230","t_233"]]	tw
3	1	t	6483	["f1","post_pc",["t_276","t_277"]]	tw
4	1	t	6130	["f1","post_receiving",["t_320","t_321","t_322","t_323","t_324"]]	tw
5	1	t	6139	["f1","post_refund",["t_330","t_333"]]	tw
\.
			

The screen with ID 6091 is sales. Its top most database table is t_220 with primary key f1 recording Sales# of data type INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY. We want the look up to the left of t_220.f1 to display the corresponding Journal# after its associated sales records are posted to accounting journal.

Look up field of t_220.f1 is configured as this:

COPY t63 FROM stdin;
t_220	f1	t52	f5	f1	184	f4=6091 AND EXISTS (SELECT 1 FROM t53 WHERE f1=t52.f1)	f	\N	\N	tw
\.
			

PostgreSQL function post_sales() which posts sales records to generate accounting journals is also attached to this screen.

17.11.1.1. Displaying Accounting Journal#

Now an user has entered one record in table t_220 with 24 as Sales#. The records associated with Sales# 24 are also entered in tables t_221, t_222, t_223 etc.

This user then executes PostgreSQL function post_sales() to post (generate) one Journal# whose value is assumed to be 120 in this case.

While Journal# 120 has been generated in database, it must be populated to client program before it shows up in look up control to the right of field t_220.f1 on this user's screen.

post_sales() must return JSON value like this:

CREATE OR REPLACE FUNCTION post_sales(company TEXT,TxId INT8,ro TEXT) RETURNS VARCHAR AS $$
DECLARE
	jid INT8;
	...
BEGIN
	...
	UPDATE t_220 SET f10=jid WHERE f1=TxId AND f10 IS DISTINCT FROM jid;
	RETURN '{"msg":"@185:' || jid || '","hs":{"type":1,"journal":' || jid || '}}';
END $$ LANGUAGE PLPGSQL VOLATILE;
				

post_sales() returns {"msg":"@185:120","hs":{"type":1,"journal":120}}. @185:120 is translated by system to Journal#:120 and displayed by client program in pop-up dialog. The client program parses JSON object "hs":{"type":1,"journal":120} and display 120 in look up control to the right of field t_220.f1 on this user's screen.

17.11.1.2. Cleancing Accounting Journal#

The journal# corresponding to sales is affected by the following data manipulations:

  • update and delete on table t_220

  • insert, update, and delete on tables t_221, t_222, t_223

We therefore must implement PostgreSQL triggers or rules to delete the corresponding Journal# after any sales row has changed like this:

CREATE OR REPLACE FUNCTION tf_220u() RETURNS TRIGGER AS $$
BEGIN
	...
	RETURN NEW;
	DELETE FROM t52 WHERE f0=OLD.f0 AND f4=6091 AND f5=OLD.f1;
END $$ LANGUAGE PLPGSQL VOLATILE;

CREATE TRIGGER t_220u BEFORE UPDATE ON t_220 FOR EACH ROW EXECUTE PROCEDURE tf_220u();
				

After PostgreSQL trigger or rule has deleted the posted journal# from database, mechanism must also be implemented to reset look up control to the right of field t_220.f1 on this user's screen. Configuration ["t_220","t_221","t_222","t_223"] tells client program to do this job.