....I..... [DEV-2097] added tsdb scripts for easier upgrade to pk

Merge in ZBX/zabbix from feature/DEV-2097-6.0 to release/6.0

* commit '5b58be45b8e0e640ea75f4ff774a5d6b000aa4aa': (21 commits)
  .......... [DEV-2097] various script fixes
  .......... [DEV-2097] tsdb - refactored, fixed old chunk_time_interval retrieval on tsdbv1
  .......... [DEV-2097] tsdb - added immediate start of scheduled compression job
  .......... [DEV-2097] tsdb - added update of config.compression_status
  .......... [DEV-2097] tsdb - removed manual compr job execution due to bug
  .......... [DEV-2097] tsdb - use chunk_time_interval from _old hypertable
  .......... [DEV-2097] tsdb scripts - removed set_integer_now_func on scripts without compression
  .......... [DEV-2097] tsdb scripts - added stop on error
  .......... [DEV-2097] added minor style improvements
  .......... [DEV-2097] separated tsb script into with compression and wo
  .......... [DEV-2097] fixed tsdb script
  .......... [DEV-2097] various fixes
  .......... [DEV-2097] added make clean for sqlite
  .......... [DEV-2097] added generated history pk upgrade scripts to gitignore
  .......... [DEV-2097] fixed main makefile
  .......... [DEV-2097] added inclusion of tsdb sqls to dist
  .......... [DEV-2097] removed old sql files that were not generated
  ....I..... [DEV-2097] added gen_history_pk.pl script to generate history pk migration sqls
  .......... [DEV-2097] fixed version retrieval
  .......... [DEV-2097] added tsdb scripts
  ...

1 files changed, 309 insertions, 0 deletions

diff --git a/create/bin/gen_history_pk.pl b/create/bin/gen_history_pk.pl
new file mode 100755
index 00000000000..0cc1a1c8ef2
--- /dev/null
+++ b/create/bin/gen_history_pk.pl
@@ -0,0 +1,309 @@
+#!/usr/bin/env perl
+
+use strict;
+use warnings;
+
+my ($db, $table, $tsdb_compression) = @ARGV;
+
+my @dbs = ('mysql', 'oracle', 'postgresql', 'timescaledb');
+my @tables = ('history', 'history_uint', 'history_str', 'history_log', 'history_text');
+
+my %mysql = (
+	'alter_table' => 'RENAME TABLE %TBL TO %TBL_old;',
+	'create_table_begin' => 'CREATE TABLE `%TBL` (',
+	'create_table_end' => ') ENGINE=InnoDB;',
+	'pk_constraint' => "\t" . 'PRIMARY KEY (itemid,clock,ns)',
+	'history' => <<'HEREDOC'
+	`itemid` bigint unsigned NOT NULL,
+	`clock` integer DEFAULT '0' NOT NULL,
+	`value` DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
+	`ns` integer DEFAULT '0' NOT NULL,
+HEREDOC
+	, 'history_uint' => <<'HEREDOC'
+	`itemid` bigint unsigned NOT NULL,
+	`clock` integer DEFAULT '0' NOT NULL,
+	`value` bigint unsigned DEFAULT '0' NOT NULL,
+	`ns` integer DEFAULT '0' NOT NULL,
+HEREDOC
+	, 'history_str' => <<'HEREDOC'
+	`itemid` bigint unsigned NOT NULL,
+	`clock` integer DEFAULT '0' NOT NULL,
+	`value` varchar(255) DEFAULT '' NOT NULL,
+	`ns` integer DEFAULT '0' NOT NULL,
+HEREDOC
+	, 'history_log' => <<'HEREDOC'
+	`itemid` bigint unsigned NOT NULL,
+	`clock` integer DEFAULT '0' NOT NULL,
+	`timestamp` integer DEFAULT '0' NOT NULL,
+	`source` varchar(64) DEFAULT '' NOT NULL,
+	`severity` integer DEFAULT '0' NOT NULL,
+	`value` text NOT NULL,
+	`logeventid` integer DEFAULT '0' NOT NULL,
+	`ns` integer DEFAULT '0' NOT NULL,
+HEREDOC
+	, 'history_text' => <<'HEREDOC'
+	`itemid` bigint unsigned NOT NULL,
+	`clock` integer DEFAULT '0' NOT NULL,
+	`value` text NOT NULL,
+	`ns` integer DEFAULT '0' NOT NULL,
+HEREDOC
+);
+
+my %oracle = (
+	'alter_table' => 'RENAME %TBL TO %TBL_old;',
+	'create_table_begin' => 'CREATE TABLE %TBL (',
+	'create_table_end' => ');',
+	'pk_constraint' => "\t" . 'CONSTRAINT PK_%UTBL PRIMARY KEY (itemid,clock,ns)',
+	'history' => <<'HEREDOC'
+	itemid                   number(20)                                NOT NULL,
+	clock                    number(10)      DEFAULT '0'               NOT NULL,
+	value                    BINARY_DOUBLE   DEFAULT '0.0000'          NOT NULL,
+	ns                       number(10)      DEFAULT '0'               NOT NULL,
+HEREDOC
+	, 'history_uint' => <<'HEREDOC'
+	itemid                   number(20)                                NOT NULL,
+	clock                    number(10)      DEFAULT '0'               NOT NULL,
+	value                    number(20)      DEFAULT '0'               NOT NULL,
+	ns                       number(10)      DEFAULT '0'               NOT NULL,
+HEREDOC
+	, 'history_str' => <<'HEREDOC'
+	itemid                   number(20)                                NOT NULL,
+	clock                    number(10)      DEFAULT '0'               NOT NULL,
+	value                    nvarchar2(255)  DEFAULT ''                ,
+	ns                       number(10)      DEFAULT '0'               NOT NULL,
+HEREDOC
+	, 'history_log' => <<'HEREDOC'
+	itemid                   number(20)                                NOT NULL,
+	clock                    number(10)      DEFAULT '0'               NOT NULL,
+	timestamp                number(10)      DEFAULT '0'               NOT NULL,
+	source                   nvarchar2(64)   DEFAULT ''                ,
+	severity                 number(10)      DEFAULT '0'               NOT NULL,
+	value                    nclob           DEFAULT ''                ,
+	logeventid               number(10)      DEFAULT '0'               NOT NULL,
+	ns                       number(10)      DEFAULT '0'               NOT NULL,
+HEREDOC
+	, 'history_text' => <<'HEREDOC'
+	itemid                   number(20)                                NOT NULL,
+	clock                    number(10)      DEFAULT '0'               NOT NULL,
+	value                    nclob           DEFAULT ''                ,
+	ns                       number(10)      DEFAULT '0'               NOT NULL,
+HEREDOC
+);
+
+my %postgresql = (
+	'alter_table' => 'ALTER TABLE %TBL RENAME TO %TBL_old;',
+	'create_table_begin' => 'CREATE TABLE %TBL (',
+	'create_table_end' => ');',
+	'pk_constraint' => "\t" . 'PRIMARY KEY (itemid,clock,ns)',
+	'history' => <<'HEREDOC'
+	itemid                   bigint                                    NOT NULL,
+	clock                    integer         DEFAULT '0'               NOT NULL,
+	value                    DOUBLE PRECISION DEFAULT '0.0000'          NOT NULL,
+	ns                       integer         DEFAULT '0'               NOT NULL,
+HEREDOC
+	, 'history_uint' => <<'HEREDOC'
+	itemid                   bigint                                    NOT NULL,
+	clock                    integer         DEFAULT '0'               NOT NULL,
+	value                    numeric(20)     DEFAULT '0'               NOT NULL,
+	ns                       integer         DEFAULT '0'               NOT NULL,
+HEREDOC
+	, 'history_str' => <<'HEREDOC'
+	itemid                   bigint                                    NOT NULL,
+	clock                    integer         DEFAULT '0'               NOT NULL,
+	value                    varchar(255)    DEFAULT ''                NOT NULL,
+	ns                       integer         DEFAULT '0'               NOT NULL,
+HEREDOC
+	, 'history_log' => <<'HEREDOC'
+	itemid                   bigint                                    NOT NULL,
+	clock                    integer         DEFAULT '0'               NOT NULL,
+	timestamp                integer         DEFAULT '0'               NOT NULL,
+	source                   varchar(64)     DEFAULT ''                NOT NULL,
+	severity                 integer         DEFAULT '0'               NOT NULL,
+	value                    text            DEFAULT ''                NOT NULL,
+	logeventid               integer         DEFAULT '0'               NOT NULL,
+	ns                       integer         DEFAULT '0'               NOT NULL,
+HEREDOC
+	, 'history_text' => <<'HEREDOC'
+	itemid                   bigint                                    NOT NULL,
+	clock                    integer         DEFAULT '0'               NOT NULL,
+	value                    text            DEFAULT ''                NOT NULL,
+	ns                       integer         DEFAULT '0'               NOT NULL,
+HEREDOC
+);
+
+my $tsdb_compress_sql = <<'HEREDOC'
+	PERFORM set_integer_now_func('%HISTTBL', 'zbx_ts_unix_now', true);
+
+	ALTER TABLE %HISTTBL
+	SET (timescaledb.compress,timescaledb.compress_segmentby='itemid',timescaledb.compress_orderby='clock,ns');
+
+	IF (tsdb_version_major < 2)
+	THEN
+		PERFORM add_compress_chunks_policy('%HISTTBL', (
+				SELECT (p.older_than).integer_interval
+				FROM _timescaledb_config.bgw_policy_compress_chunks p
+				INNER JOIN _timescaledb_catalog.hypertable h ON (h.id=p.hypertable_id)
+				WHERE h.table_name='%HISTTBL_old'
+			)::integer
+		);
+	ELSE
+		SELECT add_compression_policy('%HISTTBL', (
+			SELECT extract(epoch FROM (config::json->>'compress_after')::interval)
+			FROM timescaledb_information.jobs
+			WHERE application_name LIKE 'Compression%%' AND hypertable_schema='public'
+				AND hypertable_name='%HISTTBL_old'
+			)::integer
+		) INTO jobid;
+
+		IF jobid IS NULL
+		THEN
+			RAISE EXCEPTION 'Failed to add compression policy';
+		END IF;
+
+		PERFORM alter_job(jobid, scheduled => true, next_start => now());
+	END IF;
+HEREDOC
+;
+
+my $tsdb = <<'HEREDOC'
+\set ON_ERROR_STOP on
+
+\copy (select * from %HISTTBL_old) TO '/tmp/%HISTTBL.csv' DELIMITER ',' CSV;
+
+CREATE TEMP TABLE temp_%HISTTBL (
+%TEMPTBLDDL
+);
+
+\copy temp_%HISTTBL FROM '/tmp/%HISTTBL.csv' DELIMITER ',' CSV
+
+DO $$
+DECLARE
+	tsdb_version_major	INTEGER;
+	chunk_tm_interval	INTEGER;
+	jobid			INTEGER;
+BEGIN
+	SELECT substring(extversion, '^\d+') INTO tsdb_version_major FROM pg_extension WHERE extname='timescaledb';
+
+	IF (tsdb_version_major < 2)
+	THEN
+		SELECT (upper(ranges[1]) - lower(ranges[1])) INTO chunk_tm_interval FROM chunk_relation_size('%HISTTBL')
+			ORDER BY ranges DESC LIMIT 1;
+
+		IF NOT FOUND THEN
+			chunk_tm_interval = 86400;
+		END IF;
+
+		PERFORM create_hypertable('%HISTTBL', 'clock', chunk_time_interval => chunk_tm_interval, migrate_data => true);
+	ELSE
+		PERFORM create_hypertable('%HISTTBL', 'clock', chunk_time_interval => (
+			SELECT integer_interval FROM timescaledb_information.dimensions WHERE hypertable_name='%HISTTBL_old'
+		), migrate_data => true);
+	END IF;
+
+	INSERT INTO %HISTTBL SELECT * FROM temp_%HISTTBL ON CONFLICT (itemid,clock,ns) DO NOTHING;
+
+%COMPRESS
+END $$;
+
+%CONFIG_COMPR
+HEREDOC
+;
+
+sub output_table {
+	my ($db, $tbl, $pk_substitute_tbl) = @_;
+	my $alter_table = @$db{'alter_table'};
+
+	$alter_table =~ s/%TBL/$tbl/g;
+
+	my $create_table = @$db{'create_table_begin'};
+	$create_table =~ s/%TBL/$tbl/g;
+
+	my $pk_constraint = @$db{'pk_constraint'};
+	if ($pk_substitute_tbl == 1)
+	{
+		my $utbl = uc($tbl);
+		$pk_constraint =~ s/%UTBL/$utbl/g;
+	}
+
+	my $create_table_end = @$db{'create_table_end'};
+
+	print $alter_table . "\n";
+	print $create_table . "\n";
+	print @$db{$tbl};
+	print $pk_constraint . "\n";
+	print $create_table_end . "\n\n";
+}
+
+sub output_tsdb {
+	my ($tbl) = @_;
+
+	my $tsdb_out = $tsdb;
+
+	if (not(defined $tsdb_compression))
+	{
+		$tsdb_out =~ s/%COMPRESS//g;
+		$tsdb_out =~ s/%CONFIG_COMPR/UPDATE config SET compression_status=0;/g;
+	}
+	elsif ($tsdb_compression eq 'with_compression')
+	{
+		$tsdb_out =~ s/%COMPRESS/$tsdb_compress_sql/g;
+		$tsdb_out =~ s/%CONFIG_COMPR/UPDATE config SET compression_status=1;/g;
+	}
+
+	my $temp_ddl = $postgresql{$tbl};
+	chomp($temp_ddl);
+	$temp_ddl =~ s/,$//;
+	$tsdb_out =~ s/%TEMPTBLDDL/$temp_ddl/g;
+	$tsdb_out =~ s/%HISTTBL/$tbl/g;
+	print $tsdb_out;
+}
+
+sub validate_args {
+	die 'No arguments were provided' if (!$db);
+	die 'Wrong database was provided' if (! grep { $_ eq $db } @dbs);
+
+	if ($db eq 'timescaledb')
+	{
+		die 'Table name should be provided to generate timescaledb per-table migration script' if (!$table);
+		die 'Non-existent table name was provided' if (! grep { $_ eq $table } @tables);
+	}
+}
+
+validate_args();
+
+if ($db eq 'timescaledb')
+{
+	output_tsdb($table);
+}
+else
+{
+	if ($db eq 'mysql')
+	{
+		foreach my $tbl (@tables)
+		{
+			output_table(\%mysql, $tbl, 0);
+		}
+	}
+	elsif ($db eq 'oracle')
+	{
+		foreach my $tbl (@tables)
+		{
+			output_table(\%oracle, $tbl, 1);
+		}
+	}
+	elsif ($db eq 'postgresql')
+	{
+		foreach my $tbl (@tables)
+		{
+			output_table(\%postgresql, $tbl, 0);
+		}
+	}
+	elsif ($db eq 'timescaledb')
+	{
+		foreach my $tbl (@tables)
+		{
+			output_tsdb($tbl);
+		}
+	}
+}
+