Add support for creating tables in PgSQL.

3 files changed, 153 insertions, 64 deletions

diff --git a/dbx_Pg.pl b/dbx_Pg.pl
index 3893b00..b7ca176 100644
--- a/dbx_Pg.pl
+++ b/dbx_Pg.pl
@@ -1,10 +1,74 @@
 %dbx = (
 	epoch_to_timestamp_fn => 'TO_TIMESTAMP',
+
 	to_hex_string => sub {
 		my ($bin) = @_;
 		return "'\\x" . unpack("H*", $bin) . "'";
 	},
+
 	column_info_type_col => 'pg_type',
+
+	tables => {
+		"report" => {
+			column_definitions 		=> [
+				"serial"		, "bigint"			, "GENERATED ALWAYS AS IDENTITY",
+				"mindate"		, "timestamp without time zone"	, "NOT NULL",
+				"maxdate"		, "timestamp without time zone"	, "NULL",
+				"domain"		, "character varying(255)"	, "NOT NULL",
+				"org"			, "character varying(255)"	, "NOT NULL",
+				"reportid"		, "character varying(255)"	, "NOT NULL",
+				"email"			, "character varying(255)"	, "NULL",
+				"extra_contact_info"	, "character varying(255)"	, "NULL",
+				"policy_adkim"		, "character varying(20)"	, "NULL",
+				"policy_aspf"		, "character varying(20)"	, "NULL",
+				"policy_p"		, "character varying(20)"	, "NULL",
+				"policy_sp"		, "character varying(20)"	, "NULL",
+				"policy_pct"		, "smallint"			, "",
+				"raw_xml"		, "text"			, "",
+				],
+			additional_definitions 		=> "PRIMARY KEY (serial)",
+			table_options			=> "",
+			indexes				=> [
+				"CREATE UNIQUE INDEX report_uidx_domain ON report (domain, reportid);"
+				],
+			},
+		"rptrecord" => {
+			column_definitions 		=> [
+				"id"			, "bigint"						, "GENERATED ALWAYS AS IDENTITY",
+				"serial"		, "bigint"						, "NOT NULL",
+				"ip"			, "bigint"						, "",
+				"ip6"			, "bytea"						, "",
+				"rcount"		, "integer"						, "NOT NULL",
+				"disposition"		, "character varying(20)"				, "",
+				"reason"		, "character varying(255)"				, "",
+				"dkimdomain"		, "character varying(255)"				, "",
+				"dkimresult"		, "character varying(20)"				, "",
+				"spfdomain"		, "character varying(255)"				, "",
+				"spfresult"		, "character varying(20)"				, "",
+				"spf_align"		, "character varying(20)"				, "NOT NULL",
+				"dkim_align"		, "character varying(20)"				, "NOT NULL",
+				"identifier_hfrom"	, "character varying(255)"				, ""
+				],
+			additional_definitions 		=> "PRIMARY KEY (id)",
+			table_options			=> "",
+			indexes				=> [
+				"CREATE INDEX rptrecord_idx_serial ON rptrecord (serial, ip);",
+				"CREATE INDEX rptrecord_idx_serial6 ON rptrecord (serial, ip6);",
+				],
+			},
+		},
+
+	add_column => sub {
+		my ($table, $col_name, $col_type, $col_opts, $after_col) = @_;
+
+		# Postgres only allows adding columns at the end, so $after_col is ignored
+		return "ALTER TABLE $table ADD COLUMN $col_name $col_type $col_opts;"
+	},
+
+	modify_column => sub {
+		my ($table, $col_name, $col_type, $col_opts) = @_;
+		return "ALTER TABLE $table ALTER COLUMN $col_name TYPE $col_type;"
+	},
 );
 
 1;
diff --git a/dbx_mysql.pl b/dbx_mysql.pl
index dda94e3..3c72750 100644
--- a/dbx_mysql.pl
+++ b/dbx_mysql.pl
@@ -1,10 +1,72 @@
 %dbx = (
 	epoch_to_timestamp_fn => 'FROM_UNIXTIME',
+
 	to_hex_string => sub {
 		my ($bin) = @_;
 		return "X'" . unpack("H*", $bin) . "'";
 	},
+
 	column_info_type_col => 'mysql_type_name',
+
+	tables => {
+		"report" => {
+			column_definitions 		=> [
+				"serial"		, "int"			, "unsigned NOT NULL AUTO_INCREMENT",
+				"mindate"		, "timestamp"		, "NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP",
+				"maxdate"		, "timestamp"		, "NULL",
+				"domain"		, "varchar(255)"	, "NOT NULL",
+				"org"			, "varchar(255)"	, "NOT NULL",
+				"reportid"		, "varchar(255)"	, "NOT NULL",
+				"email"			, "varchar(255)"	, "NULL",
+				"extra_contact_info"	, "varchar(255)"	, "NULL",
+				"policy_adkim"		, "varchar(20)"		, "NULL",
+				"policy_aspf"		, "varchar(20)"		, "NULL",
+				"policy_p"		, "varchar(20)"		, "NULL",
+				"policy_sp"		, "varchar(20)"		, "NULL",
+				"policy_pct"		, "tinyint"		, "unsigned",
+				"raw_xml"		, "mediumtext"		, "",
+				],
+			additional_definitions 		=> "PRIMARY KEY (serial), UNIQUE KEY domain (domain, reportid)",
+			table_options			=> "ROW_FORMAT=COMPRESSED",
+			indexes				=> [],
+			},
+		"rptrecord" => {
+			column_definitions 		=> [
+				"id"			, "int"	, "unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY",
+				"serial"		, "int"							, "unsigned NOT NULL",
+				"ip"			, "int"							, "unsigned",
+				"ip6"			, "binary(16)"						, "",
+				"rcount"		, "int"							, "unsigned NOT NULL",
+				"disposition"		, "enum('" . join("','", ALLOWED_DISPOSITION) . "')"	, "",
+				"reason"		, "varchar(255)"					, "",
+				"dkimdomain"		, "varchar(255)"					, "",
+				"dkimresult"		, "enum('" . join("','", ALLOWED_DKIMRESULT) . "')"	, "",
+				"spfdomain"		, "varchar(255)"					, "",
+				"spfresult"		, "enum('" . join("','", ALLOWED_SPFRESULT) . "')"	, "",
+				"spf_align"		, "enum('" . join("','", ALLOWED_SPF_ALIGN) . "')"	, "NOT NULL",
+				"dkim_align"		, "enum('" . join("','", ALLOWED_DKIM_ALIGN) . "')"	, "NOT NULL",
+				"identifier_hfrom"	, "varchar(255)"					, ""
+				],
+			additional_definitions 		=> "KEY serial (serial, ip), KEY serial6 (serial, ip6)",
+			table_options			=> "",
+			indexes				=> [],
+			},
+		},
+
+	add_column => sub {
+		my ($table, $col_name, $col_type, $col_opts, $after_col) = @_;
+
+		my $insert_pos = "FIRST";
+		if ($after_col) {
+			$insert_pos = "AFTER $after_col";
+		}
+		return "ALTER TABLE $table ADD $col_name $col_type $col_opts $insert_pos;"
+	},
+
+	modify_column => sub {
+		my ($table, $col_name, $col_type, $col_opts) = @_;
+		return "ALTER TABLE $table MODIFY COLUMN $col_name $col_type $col_opts;"
+	},
 );
 
 1;
diff --git a/dmarcts-report-parser.pl b/dmarcts-report-parser.pl
index f294bc6..5873256 100755
--- a/dmarcts-report-parser.pl
+++ b/dmarcts-report-parser.pl
@@ -1072,106 +1072,69 @@ sub rollback {
 sub checkDatabase {
 	my $dbh = $_[0];
 
-	# display width is deprecated in MySQL 8+ and will not be shown in show create statements
-	my $mysql_main_version = $dbh->selectrow_array("SELECT SUBSTRING_INDEX(VERSION(), '.', 1);") || 5;
-	my $display_width = ( $mysql_main_version <= 5 ? "(10)" : "" );
-
-	my %tables = (
-		"report" => {
-			column_definitions 		=> [
-				"serial"		, "int${ display_width } unsigned NOT NULL AUTO_INCREMENT",
-				"mindate"		, "timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP",
-				"maxdate"		, "timestamp NULL",
-				"domain"		, "varchar(255) NOT NULL",
-				"org"			, "varchar(255) NOT NULL",
-				"reportid"		, "varchar(255) NOT NULL",
-				"email"			, "varchar(255) NULL",
-				"extra_contact_info"	, "varchar(255) NULL",
-				"policy_adkim"		, "varchar(20) NULL",
-				"policy_aspf"		, "varchar(20) NULL",
-				"policy_p"		, "varchar(20) NULL",
-				"policy_sp"		, "varchar(20) NULL",
-				"policy_pct"		, "tinyint unsigned",
-				"raw_xml"		, "mediumtext",
-				],
-			additional_definitions 		=> "PRIMARY KEY (serial), UNIQUE KEY domain (domain,reportid)",
-			table_options			=> "ROW_FORMAT=COMPRESSED",
-			},
-		"rptrecord" =>{
-			column_definitions 		=> [
-				"id"			, "int${ display_width } unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY",
-				"serial"		, "int${ display_width } unsigned NOT NULL",
-				"ip"			, "int${ display_width } unsigned",
-				"ip6"			, "binary(16)",
-				"rcount"		, "int${ display_width } unsigned NOT NULL",
-				"disposition"		, "enum('" . join("','", ALLOWED_DISPOSITION) . "')",
-				"reason"		, "varchar(255)",
-				"dkimdomain"		, "varchar(255)",
-				"dkimresult"		, "enum('" . join("','", ALLOWED_DKIMRESULT) . "')",
-				"spfdomain"		, "varchar(255)",
-				"spfresult"		, "enum('" . join("','", ALLOWED_SPFRESULT) . "')",
-				"spf_align"		, "enum('" . join("','", ALLOWED_SPF_ALIGN) . "') NOT NULL",
-				"dkim_align"		, "enum('" . join("','", ALLOWED_DKIM_ALIGN) . "') NOT NULL",
-				"identifier_hfrom"	, "varchar(255)",
-				],
-			additional_definitions 		=> "KEY serial (serial,ip), KEY serial6 (serial,ip6)",
-			table_options			=> "",
-			},
-	);
+	my $tables = $dbx{tables};
 
 	# Create missing tables and missing columns.
-	for my $table ( keys %tables ) {
+	for my $table ( keys %{$tables} ) {
 
 		if (!db_tbl_exists($dbh, $table)) {
 
 			# Table does not exist, build CREATE TABLE cmd from tables hash.
 			print "$scriptname: Adding missing table <" . $table . "> to the database.\n";
 			my $sql_create_table = "CREATE TABLE " . $table . " (\n";
-			for (my $i=0; $i <= $#{$tables{$table}{"column_definitions"}}; $i+=2) {
-				my $col_name = $tables{$table}{"column_definitions"}[$i];
-				my $col_def = $tables{$table}{"column_definitions"}[$i+1];
+			for (my $i=0; $i <= $#{$tables->{$table}{"column_definitions"}}; $i+=3) {
+				my $col_name = $tables->{$table}{"column_definitions"}[$i];
+				my $col_type = $tables->{$table}{"column_definitions"}[$i+1];
+				my $col_opts = $tables->{$table}{"column_definitions"}[$i+2];
 				# add comma if second or later entry
 				if ($i != 0) {
 					$sql_create_table .= ",\n";
 				}
-				$sql_create_table .= $col_name . " " .$col_def;
+				$sql_create_table .= "$col_name $col_type $col_opts";
 			}
 			# Add additional_definitions, if defined.
-			if ($tables{$table}{"additional_definitions"} ne "") {
-				$sql_create_table .= ",\n" . $tables{$table}{"additional_definitions"};
+			if ($tables->{$table}{"additional_definitions"} ne "") {
+				$sql_create_table .= ",\n" . $tables->{$table}{"additional_definitions"};
 			}
 			# Add options.
-			$sql_create_table .= ") " . $tables{$table}{"table_options"} . ";";
+			$sql_create_table .= ") " . $tables->{$table}{"table_options"} . ";";
 			# Create table.
 			print "$sql_create_table\n" if $debug;
 			$dbh->do($sql_create_table);
+
+			# Create indexes.
+			foreach my $sql_idx (@{$tables->{$table}{indexes}}) {
+				print "$sql_idx\n" if $debug;
+				$dbh->do($sql_idx);
+			}
 		} else {
 
 			#Table exists, get  current columns in this table from DB.
 			my %db_col_exists = db_column_info($dbh, $table);
 
 			# Check if all needed columns are present, if not add them at the desired position.
-			my $insert_pos = "FIRST";
-			for (my $i=0; $i <= $#{$tables{$table}{"column_definitions"}}; $i+=2) {
-				my $col_name = $tables{$table}{"column_definitions"}[$i];
-				my $col_def = $tables{$table}{"column_definitions"}[$i+1];
-				my $short_def = $col_def;
-				$short_def =~ s/ +.*$//;
+			my $insert_pos;
+			for (my $i=0; $i <= $#{$tables->{$table}{"column_definitions"}}; $i+=3) {
+				my $col_name = $tables->{$table}{"column_definitions"}[$i];
+				my $col_type = $tables->{$table}{"column_definitions"}[$i+1];
+				my $col_opts = $tables->{$table}{"column_definitions"}[$i+2];
 				if (!$db_col_exists{$col_name}) {
 					# add column
-					my $sql_add_column = "ALTER TABLE $table ADD $col_name $col_def $insert_pos;";
+					my $sql_add_column = $dbx{add_column}($table, $col_name, $col_type, $col_opts, $insert_pos);
 					print "$sql_add_column\n" if $debug;
 					$dbh->do($sql_add_column);
-				} elsif ($db_col_exists{$col_name} !~ /^\Q$short_def\E/) {
+				} elsif ($db_col_exists{$col_name} !~ /^\Q$col_type\E/) {
 					# modify column
-					my $sql_modify_column = "ALTER TABLE $table MODIFY COLUMN $col_name $col_def;";
+					my $sql_modify_column = $dbx{modify_column}($table, $col_name, $col_type, $col_opts);
 					print "$sql_modify_column\n" if $debug;
 					$dbh->do($sql_modify_column);
 				}
-				$insert_pos = "AFTER $col_name";
+				$insert_pos = $col_name;
 			}
 		}
 	}
+
+	$dbh->commit;
 }
 
 ################################################################################