4 use FS::Record qw(dbdef);
9 FS::h_Common - History table "mixin" common base class
13 package FS::h_tablename;
14 @ISA = qw( FS::h_Common FS::tablename );
16 sub table { 'h_table_name'; }
18 sub insert { return "can't insert history records manually"; }
19 sub delete { return "can't delete history records"; }
20 sub replace { return "can't modify history records"; }
24 FS::h_Common is intended as a "mixin" base class for history table classes to
31 =item sql_h_search END_TIMESTAMP [ START_TIMESTAMP ]
33 Returns an a list consisting of the "SELECT", "EXTRA_SQL", SQL fragments, a
34 placeholder for "CACHE_OBJ" and an "AS" SQL fragment, to search for the
35 appropriate history records created before END_TIMESTAMP and (optionally) not
36 deleted before START_TIMESTAMP.
41 my( $self, $end ) = ( shift, shift );
43 my $table = $self->table;
44 my $real_table = ($table =~ /^h_(.*)$/) ? $1 : $table;
45 my $pkey = dbdef->table($real_table)->primary_key
46 or die "can't (yet) search history table $real_table without a primary key";
49 confess 'Called sql_h_search without END_TIMESTAMP';
52 my( $notdeleted, $notdeleted_mr ) = ( '', '' );
53 if ( scalar(@_) && $_[0] ) {
55 "AND 0 = ( SELECT COUNT(*) FROM $table as notdel
56 WHERE notdel.$pkey = maintable.$pkey
57 AND notdel.history_action = 'delete'
58 AND notdel.history_date > maintable.history_date
59 AND notdel.history_date <= $_[0]
62 "AND 0 = ( SELECT COUNT(*) FROM $table as notdel_mr
63 WHERE notdel_mr.$pkey = mostrecent.$pkey
64 AND notdel_mr.history_action = 'delete'
65 AND notdel_mr.history_date > mostrecent.history_date
66 AND notdel_mr.history_date <= $_[0]
71 #"DISTINCT ON ( $pkey ) *",
74 "AND history_date <= $end
75 AND ( history_action = 'insert'
76 OR history_action = 'replace_new'
79 AND history_date = ( SELECT MAX(mostrecent.history_date)
80 FROM $table AS mostrecent
81 WHERE mostrecent.$pkey = maintable.$pkey
82 AND mostrecent.history_date <= $end
83 AND ( mostrecent.history_action = 'insert'
84 OR mostrecent.history_action = 'replace_new'
90 #ORDER BY $pkey ASC, history_date DESC",
99 =item sql_h_searchs END_TIMESTAMP [ START_TIMESTAMP ]
101 Like sql_h_search, but limited to the single most recent record (before
108 my($select, $where, $cacheobj, $as) = $self->sql_h_search(@_);
109 $where .= ' LIMIT 1';
110 ($select, $where, $cacheobj, $as);
113 =item sql_diff START_TIMESTAMP, END_TIMESTAMP[, WHERE]
115 Returns a complete SQL statement to find all records that were changed
116 between START_TIMESTAMP and END_TIMESTAMP. This finds only replacements,
117 not new or deleted records.
119 For each modified record, this will return I<one> row (not two rows as in
120 the history table) with the primary key of the record, "old_historynum"
121 (the historynum of the last modification before START_TIMESTAMP), and
122 "new_historynum" (the last modification before END_TIMESTAMP). Join these
123 back to the h_* table to retrieve the actual field values.
125 Within the query, the last history records as of START and END are aliased
126 as "old" and "new"; you can append a WHERE clause to take advantage of this.
132 my $table = $class->table;
133 my ($real_table) = ($table =~ /^h_(\w+)$/);
134 my $pkey = dbdef->table($real_table)->primary_key;
135 my @fields = "FS::$real_table"->fields;
137 my ($sdate, $edate) = @_;
138 ($sdate, $edate) = ($edate, $sdate) if $edate < $sdate;
142 'old.historynum AS old_historynum',
143 'new.historynum AS new_historynum',
146 "SELECT DISTINCT ON ($pkey) * FROM $table
147 WHERE history_action = 'replace_new'
148 AND history_date >= $sdate AND history_date < $edate
149 ORDER BY $pkey ASC, history_date DESC";
151 "SELECT DISTINCT ON ($pkey) * FROM $table
152 WHERE (history_action = 'replace_new' OR history_action = 'insert')
153 AND history_date < $sdate
154 ORDER BY $pkey ASC, history_date DESC";
156 my $from = "($new) AS new JOIN ($old) AS old USING ($pkey)";
158 return "SELECT ".join(',', @select)." FROM $from";
168 L<FS::Record>, schema.html from the base documentation