Advanced Report Usage Notes: Avoid Duplicating Redundant Records

Transfers between two managed nodes create two records per file, one in $TBL_TRANSFER_SESSION_FILES and one in $TBL_FILES.

If both source and destination are managed nodes, then both sides log to the database. These records will not be identical--the record logged by the server reports the server-side path, while the record logged by the client reports the client-side path. Sometimes other fields, such as err_desc, may differ as well.

There are several fields in the canonical tables supplied specifically to address this issue:

Field Name	Description	Tables
reported_by_both_sides	0 if transfer was only logged by one side. 1 if transfer was logged by both server and client.	$TBL_TRANSFERS
reported_by_server	0 if the file record was logged by the client. 1 if the file record was logged by the server.	$TBL_FILES $TBL_TRANSFER_SESSION_FILES
initiated_by_source	0 if transfer is a pull (client is the destination). 1 if transfer is a push (client is the source).	$TBL_TRANSFERS $TBL_TRANSFER_SESSIONS $TBL_FILES $TBL_TRANSFER_SESSION_FILES

To ensure that each file is present only once in a result set, we need to use the above fields to give precedence to the record from one side or the other.

Note: The previous caveat about record duplication (Advanced Report Usage Notes: Avoid Duplicating Identical Records) also applies (i.e. the file record reported by the server node could itself be returned multiple times, as well as the record reported by the client node).

Note: Certain edge cases cause a problem even when using the above filter. For example, if both nodes start reporting a transfer session and one node loses its connection to the database, then reported_by_both_sides will equal 1, but not all of the file records will have two records in the file tables.

The following SQL example, taken from the built-in Activity Summary By Contact report, gives the destination-side file record precedence in cases where both sides logged the transfer.

#==================================================
# Set variables to hold report datetime parameters
# (all datetimes are converted to UTC)
#==================================================
SET @report_period_start = '$REPORT_PERIOD_START';
SET @report_period_end = '$REPORT_PERIOD_END';
#===================================================
# PRE-FILTER RECORD IDS
# Initially retrieve just the id columns from
# base tables (improves performance by avoiding
# queries with more than one join)
#===================================================
#---------------------------------------------------
# Create tables to hold the prefiltered record IDs
#---------------------------------------------------
CREATE TABLE $TMP_TRANSFER_IDS (
	id INT NOT NULL PRIMARY KEY
	, reported_by_both_sides TINYINT(1) NOT NULL DEFAULT 0
	);
CREATE TABLE $TMP_TRANSFER_SESSION_IDS (
	id INT NOT NULL PRIMARY KEY
	, reported_by_both_sides TINYINT(1) NOT NULL DEFAULT 0
	);
CREATE TABLE $TMP_FILE_SESSION_IDS (
	id INT NOT NULL PRIMARY KEY
	, transfer_session_id INT NOT NULL
	);
#---------------------------------------------------
# Retrieve IDs
#---------------------------------------------------
#---------------------------------------------------
# Transfers
#---------------------------------------------------
INSERT INTO $TMP_TRANSFER_IDS
SELECT DISTINCT
	t.id
	, t.reported_by_both_sides
FROM $TBL_TRANSFERS t
WHERE
	(t.started_at < @report_period_end
		AND (t.stopped_at >= @report_period_start
			OR t.stopped_at IS NULL
		)
	)
;
#---------------------------------------------------
# Transfer Sessions
# (copy over 'reported_by_both_sides'
# from transfers)
#---------------------------------------------------

INSERT INTO $TMP_TRANSFER_SESSION_IDS
SELECT DISTINCT
	ts.id
	, t.reported_by_both_sides
FROM $TBL_TRANSFER_SESSIONS ts
	JOIN $TMP_TRANSFER_IDS t
		ON ts.transfer_id = t.id
WHERE
	(ts.started_at < @report_period_end
		AND (ts.stopped_at >= @report_period_start
			OR ts.stopped_at IS NULL
		)
	)
;
#---------------------------------------------------
# File Sessions (choose destination-side
# info if both sides logged to db)
#---------------------------------------------------
INSERT INTO $TMP_FILE_SESSION_IDS
SELECT DISTINCT
	fs.id
	, fs.transfer_session_id
FROM $TBL_TRANSFER_SESSION_FILES fs
	JOIN $TMP_TRANSFER_SESSION_IDS ts
		ON fs.transfer_session_id = ts.id
WHERE
	(fs.started_at < @report_period_end
		AND (fs.stopped_at >= @report_period_start
			OR fs.stopped_at IS NULL)
		)
		AND (ts.reported_by_both_sides=0
			OR (
				(fs.reported_by_server=1
					AND fs.initiated_by_source=1)
			OR (fs.reported_by_server=0
				AND fs.initiated_by_source=0)
		)
	)
; 
CREATE INDEX idx_transfer_session_id
	ON $TMP_FILE_SESSION_IDS (transfer_session_id);