Transactional processing of objects and data

Generators

The generator is a SQL query that returns the set of values that can be used in a step.

Placeholders - is a variables in GEN_NSP_FLD_X, GEN_OBJ_FLD_X format, which are replaced by the values returned by generator, where X is the field index from the generator tuple. A field with zero index cannot be used, because reserved for a service command (like vacuum) that is not recorded in the Action Tracker (dbc_* tables). In case when a maintenance command is not required, the maint field must equal null or zero-length string. An alias maint is required.

There are two types of generators:

• schema generator - has major priority and is intended for listing schemas
• object generator - has minor priority and is intended for listing objects located in schemas (tables, indexes, ...)

An object generator is always a nested loop for a schema generator. Generators can also be used separately (schema generator only or object generator only).

The logic of generators and placeholders is shown on the diagram:

A basic illustration of using generators:

mkdir packets/test_gen

cat > packets/test_gen/01_gen_obj.sql << EOL
    SELECT '' as maint, generate_series(1,3)
EOL

cat > packets/test_gen/01_step.sql << EOL
    SELECT GEN_OBJ_FLD_1 as v
EOL

python3 db_converter.py --packet-name=test_gen

	Info: =====> DBC 1.3 started
	Info: =====> Hold lock for packet test_gen in DB dbc
	Info: Thread 'lock_observer_dbc' runned! Observed pids: []
	Info: --------> Packet 'test_gen' started for 'dbc' database!
	Info: lock_observer_dbc: iteration done. Sleep on 5 seconds...
	Info: Thread 'lock_observer_dbc': Observed pids: []
	Info: Thread 'manager_db_dbc', DB 'dbc', PID 9402, Packet 'test_gen', Step '01_step.sql': progress 0.0%
	Info:
	-----
	| v |
	-----
	| 1 |

	Info:
	-----
	| v |
	-----
	| 2 |

	Info:
	-----
	| v |
	-----
	| 3 |

	Info: <-------- Packet 'test_gen' finished for 'dbc' database!
	Info: Thread lock_observer_dbc finished!
	Info: <===== DBC 1.3 finished

Each step can have one or two different types of generators. For cases where there are a schema generator and an object generator for a certain step, all iterations of the object generator are performed per one iteration of the schema generator. Such functionality is provided for processing several objects according to a certain set of schemas. For example, the database has one hundred schemas nsp_a, nsp_b ... nsp_x with tables a_tbl_1, a_tbl_2 ... a_tbl_N and needs to rename tables starting with the a_ prefix into tables with the b_ prefix in all schemas, for this it is enough to create one generator:

mkdir packets/rename_tbls

cat > packets/rename_tbls/01_gen_obj.sql << EOL
	select
		'' as maint,    -- required system field
		n.nspname,                              -- GEN_NSP_FLD_1 placeholder
		c.relname,                              -- GEN_NSP_FLD_2 placeholder
		'b_' || substring(c.relname from 3 for length(c.relname))   -- GEN_NSP_FLD_3 placeholder
	from pg_namespace n
	join pg_class c on n.oid = c.relnamespace
	where n.nspname like 'nsp_%' and relname ilike 'a_tbl_%'
EOL

cat > packets/rename_tbls/01_step.sql << EOL
 	ALTER TABLE GEN_NSP_FLD_1.GEN_NSP_FLD_2
		RENAME TO GEN_NSP_FLD_3
EOL

In this case, there is no fundamental difference in how to name the generator schema generator or object generator. The naming was introduced for a more convenient perception of file names and placeholder names.

In the considered above example, one generator was enough to solve the task. Two generators could be required if the list of tables was strictly defined and not selected by the mask. Then the solution would look like this:

mkdir packets/rename_tbls

cat > packets/rename_tbls/01_gen_nsp.sql << EOL
	select
		'' as maint,
		n.nspname       -- GEN_NSP_FLD_1
	from pg_namespace n
	where n.nspname like 'nsp_%'
EOL

cat > packets/rename_tbls/01_gen_obj.sql << EOL
	select
		'' as maint,
		'customer'      -- GEN_OBJ_FLD_1
	union
	select '', 'accounts'
	union
	select '', 'history'
EOL

cat > packets/rename_tbls/01_step.sql << EOL
	ALTER TABLE GEN_NSP_FLD_1.GEN_OBJ_FLD_1
		RENAME TO old_GEN_OBJ_FLD_1
EOL

In some cases, the generator can use objects created during packet execution, for this, there is a run_once.sql file that is executed as the first step. It is in this file that the creation of objects that be used in generators. Such an implementation was made because before executing of the first step, the generators are started first, and the results of generators are stored in memory, and then, in the process of performing the steps, they are substituted in the places of the corresponding placeholders. This approach validates the contents of generators before the changes are applied to the database.

Placeholders

db_converter implements two types of placeholders:

• placeholders from generators (GEN_NSP_FLD_X, GEN_OBJ_FLD_X), considered above
• placeholders from the command line (DBC_PL_X)

Placeholders can also be initialized from the command line. For this, a special placeholder prefix is provided DBC_PL and --placeholders key.

--placeholders="{'key':'value'}"

Note: json object keys and values must be enclosed in single quotes.

DBC_PL - is a required prefix by which the search and replacement of a placeholder with a value occur.