view.sql

/**
 * Create a useful view for browsing the audit logs.  MySQL unfortunalte does
 * not support subqueries in the FROM clause of a view definition, so I am
 * forced to create multiple views with ugly (but hopefully alphabetically
 * last) names.
 */

DROP VIEW IF EXISTS zzz_quick_audit_log_1;
CREATE VIEW zzz_quick_audit_log_1 AS
	SELECT
		a.audit_date AS audit_date,
		a.ref_type AS ref_type,
		a.ref_id AS ref_id,
		a.operation AS operation,
		a.id AS audit_log_id,
		IF(ISNULL(f.audit_log_id), '------', f.field) AS field,
		IF(ISNULL(f.audit_log_id), '------', f.value) AS value,
		a.user_id AS user_id
	FROM audit_logs a
	LEFT JOIN audit_log_fields f ON f.audit_log_id = a.id
	ORDER BY a.id
;

DROP VIEW IF EXISTS zzz_quick_audit_log_2;
CREATE VIEW zzz_quick_audit_log_2 AS
	SELECT * FROM zzz_quick_audit_log_1
	UNION
	SELECT '', '', '', '', id + 0.5, '', '', '' FROM audit_logs
;

DROP VIEW IF EXISTS zzz_quick_audit_log_3;
CREATE VIEW zzz_quick_audit_log_3 AS
	SELECT * FROM zzz_quick_audit_log_2 ORDER BY audit_log_id
;

DROP VIEW IF EXISTS quick_audit_log;
CREATE VIEW quick_audit_log AS
	SELECT
		audit_date,
		ref_type,
		ref_id,
		operation,
		IF(audit_log_id MOD 1 = 0.5, '', FLOOR(audit_log_id)) as audit_log_id,
		field,
		value,
		user_id
	FROM zzz_quick_audit_log_3
;

Here's what that looks like:

http://i.imgur.com/Ryv8P8s.png