modified on local at 2026-07-13 20:26:14
| 1 | 1 | #!/usr/bin/perl |
| 2 | 2 | use strict; |
| 3 | 3 | use warnings; |
| 4 | 4 | use CGI (); |
| 5 | 5 | use MODS::Config; use MODS::DBConnect; use MODS::Template; use MODS::PageWrapper; |
| 6 | 6 | use MODS::Charts; |
| 7 | 7 | use MODS::RangePicker; |
| 8 | 8 | use POSIX (); |
| 9 | 9 | my $cgi = CGI->new; my $db = MODS::DBConnect->new; my $tpl = MODS::Template->new; |
| 10 | 10 | $|=1; print "Content-Type: text/html; charset=utf-8\nCache-Control: no-cache\n\n"; |
| 11 | 11 | my $dbh = $db->db_connect or die "DB connect failed\n"; |
| 12 | 12 | |
| 13 | 13 | my $r = MODS::RangePicker::parse($cgi); |
| 14 | 14 | my $q = $cgi->param('q') || ''; |
| 15 | my $show_baseline = $cgi->param('baseline') ? 1 : 0; | |
| 16 | # Status tab: modified | added | deleted | all | |
| 17 | my $status_filter = lc($cgi->param('status') // 'all'); | |
| 18 | $status_filter = 'all' unless $status_filter =~ /^(modified|added|deleted|all)$/; | |
| 15 | 19 | |
| 16 | my $where = $r->{sql_where}->('sc.change_datetime'); | |
| 17 | $where .= " AND (sc.database_name LIKE " . $dbh->quote('%'.$q.'%') | |
| 18 | . " OR sc.table_name LIKE " . $dbh->quote('%'.$q.'%') . ")" if length $q; | |
| 20 | my $where_base = $r->{sql_where}->('sc.change_datetime'); | |
| 21 | $where_base .= " AND (sc.database_name LIKE " . $dbh->quote('%'.$q.'%') | |
| 22 | . " OR sc.table_name LIKE " . $dbh->quote('%'.$q.'%') . ")" if length $q; | |
| 23 | $where_base .= " AND sc.is_baseline = 0" unless $show_baseline; | |
| 24 | ||
| 25 | # Classify a schema-change row into modified / added / deleted based on the | |
| 26 | # changes text. We derive the tab counts + filter clause from this. | |
| 27 | # added = "first snapshot" (a brand-new table appears in the DB) | |
| 28 | # deleted = "table dropped" (the table went away between scans) | |
| 29 | # modified = anything else (real DDL diff) | |
| 30 | my $status_expr = q~ | |
| 31 | CASE | |
| 32 | WHEN sc.changes LIKE 'first snapshot%' THEN 'added' | |
| 33 | WHEN sc.changes LIKE 'table dropped%' THEN 'deleted' | |
| 34 | ELSE 'modified' | |
| 35 | END | |
| 36 | ~; | |
| 37 | ||
| 38 | my %status_counts = (all => 0, modified => 0, added => 0, deleted => 0); | |
| 39 | { | |
| 40 | my @cnt = $db->db_readwrite_multiple($dbh, qq~ | |
| 41 | SELECT $status_expr AS kind, COUNT(*) AS n | |
| 42 | FROM SCHEMA_CHANGE sc WHERE $where_base GROUP BY kind | |
| 43 | ~, __FILE__, __LINE__); | |
| 44 | foreach my $row (@cnt) { | |
| 45 | my $k = lc($row->{kind} // ''); | |
| 46 | $status_counts{$k} = $row->{n} if exists $status_counts{$k}; | |
| 47 | $status_counts{all} += $row->{n}; | |
| 48 | } | |
| 49 | } | |
| 50 | ||
| 51 | my $where = $where_base; | |
| 52 | if ($status_filter ne 'all') { | |
| 53 | my %sql_extra = ( | |
| 54 | added => "sc.changes LIKE 'first snapshot%'", | |
| 55 | deleted => "sc.changes LIKE 'table dropped%'", | |
| 56 | modified => "sc.changes NOT LIKE 'first snapshot%' AND sc.changes NOT LIKE 'table dropped%'", | |
| 57 | ); | |
| 58 | $where .= " AND (" . $sql_extra{$status_filter} . ")" if $sql_extra{$status_filter}; | |
| 59 | } | |
| 19 | 60 | |
| 20 | 61 | my @rows = $db->db_readwrite_multiple($dbh, qq~ |
| 21 | 62 | SELECT sc.schema_change_id AS id, sc.database_name, sc.table_name, sc.changes, |
| 22 | 63 | UNIX_TIMESTAMP(sc.change_datetime) AS ts_epoch, |
| 23 | sc.change_datetime, sc.is_ts_only, s.server_name | |
| 64 | sc.change_datetime, sc.is_ts_only, sc.is_baseline, s.server_name | |
| 24 | 65 | FROM SCHEMA_CHANGE sc |
| 25 | 66 | LEFT JOIN SERVERS s ON s.server_id = sc.server_id |
| 26 | 67 | WHERE $where |
| 27 | 68 | ORDER BY sc.change_datetime DESC |
| 28 | 69 | LIMIT 200 |
| 29 | 70 | ~, __FILE__, __LINE__); |
| 71 | ||
| 72 | # Baseline hidden count for the header hint | |
| 73 | my $baseline_hidden = 0; | |
| 74 | unless ($show_baseline) { | |
| 75 | my $bwhere = $r->{sql_where}->('sc.change_datetime'); | |
| 76 | $bwhere .= " AND (sc.database_name LIKE " . $dbh->quote('%'.$q.'%') | |
| 77 | . " OR sc.table_name LIKE " . $dbh->quote('%'.$q.'%') . ")" if length $q; | |
| 78 | $bwhere .= " AND sc.is_baseline = 1"; | |
| 79 | my $bh = $db->db_readwrite($dbh, "SELECT COUNT(*) AS n FROM SCHEMA_CHANGE sc WHERE $bwhere", __FILE__, __LINE__); | |
| 80 | $baseline_hidden = ($bh && $bh->{n}) || 0; | |
| 81 | } | |
| 30 | 82 | |
| 31 | 83 | my %spark; |
| 32 | 84 | { |
| 33 | 85 | my %pairs; |
| 34 | 86 | foreach my $row (@rows) { |
| 35 | 87 | my $key = ($row->{database_name} // '') . '|' . ($row->{table_name} // ''); |
| 36 | 88 | $pairs{$key} = [ $row->{database_name}, $row->{table_name} ]; |
| 37 | 89 | } |
| 38 | 90 | my @conds; |
| 39 | 91 | foreach my $p (values %pairs) { |
| 40 | 92 | my $qd = $dbh->quote($p->[0] // ''); |
| 41 | 93 | my $qt = $dbh->quote($p->[1] // ''); |
| 42 | 94 | push @conds, "(database_name = $qd AND table_name = $qt)"; |
| 43 | 95 | } |
| 44 | 96 | if (@conds) { |
| 45 | 97 | my $or = join(' OR ', @conds); |
| 46 | 98 | my @day_rows = $db->db_readwrite_multiple($dbh, qq~ |
| 47 | 99 | SELECT database_name, table_name, |
| 48 | 100 | DATE_FORMAT(change_datetime, '%Y-%m-%d') AS d, |
| 49 | 101 | COUNT(*) AS n |
| 50 | 102 | FROM SCHEMA_CHANGE |
| 51 | 103 | WHERE ($or) |
| 52 | 104 | AND change_datetime >= DATE_SUB(NOW(), INTERVAL 30 DAY) |
| 53 | 105 | GROUP BY database_name, table_name, d |
| 54 | 106 | ~, __FILE__, __LINE__); |
| 55 | 107 | my %by; |
| 56 | 108 | foreach my $dr (@day_rows) { |
| 57 | 109 | my $key = ($dr->{database_name} // '') . '|' . ($dr->{table_name} // ''); |
| 58 | 110 | $by{$key}{$dr->{d}} = $dr->{n}; |
| 59 | 111 | } |
| 60 | 112 | my $now = time; |
| 61 | 113 | my @day_keys; |
| 62 | 114 | for (my $i = 29; $i >= 0; $i--) { |
| 63 | 115 | push @day_keys, POSIX::strftime('%Y-%m-%d', localtime($now - $i * 86400)); |
| 64 | 116 | } |
| 65 | 117 | foreach my $key (keys %pairs) { |
| 66 | 118 | my @series; |
| 67 | 119 | for my $k (@day_keys) { push @series, ($by{$key}{$k} || 0); } |
| 68 | 120 | $spark{$key} = \@series; |
| 69 | 121 | } |
| 70 | 122 | } |
| 71 | 123 | } |
| 72 | 124 | |
| 73 | 125 | foreach my $row (@rows) { |
| 74 | 126 | my $c = $row->{changes} // ''; |
| 75 | 127 | $c =~ s/\s+/ /g; |
| 76 | 128 | $row->{changes_preview} = length($c) > 100 ? substr($c, 0, 97) . '...' : $c; |
| 77 | 129 | $row->{diff_url} = "/diff.cgi?kind=schema&id=$row->{id}"; |
| 78 | 130 | $row->{ts_epoch} //= 0; |
| 79 | 131 | my $key = ($row->{database_name} // '') . '|' . ($row->{table_name} // ''); |
| 80 | 132 | my $series = $spark{$key}; |
| 81 | 133 | $row->{sparkline_svg} = $series |
| 82 | 134 | ? MODS::Charts::sparkline(values => $series, width => 88, height => 20, color => '#f59e0b') |
| 83 | 135 | : ''; |
| 84 | 136 | } |
| 85 | 137 | my $total = scalar @rows; |
| 86 | 138 | |
| 87 | 139 | $db->db_disconnect($dbh); |
| 88 | 140 | |
| 89 | 141 | my $picker_html = MODS::RangePicker::picker_html( |
| 90 | 142 | action => '/schema_changes.cgi', |
| 91 | 143 | range => $r, |
| 92 | 144 | hidden => { q => $q }, |
| 93 | 145 | ); |
| 94 | 146 | |
| 95 | 147 | my $tvars = { |
| 96 | 148 | rows => \@rows, |
| 97 | 149 | has_rows => $total ? 1 : 0, |
| 98 | 150 | total => $total, |
| 99 | 151 | q => $q, |
| 100 | 152 | picker_html => $picker_html, |
| 101 | 153 | range_label => $r->{range_label}, |
| 102 | 154 | is_day_view => $r->{is_day} ? 1 : 0, |
| 103 | 155 | day_label => $r->{on}, |
| 156 | show_baseline => $show_baseline, | |
| 157 | baseline_hidden => $baseline_hidden, | |
| 158 | has_baseline_hidden => $baseline_hidden ? 1 : 0, | |
| 159 | tab_all_active => ($status_filter eq 'all') ? 1 : 0, | |
| 160 | tab_modified_active => ($status_filter eq 'modified') ? 1 : 0, | |
| 161 | tab_added_active => ($status_filter eq 'added') ? 1 : 0, | |
| 162 | tab_deleted_active => ($status_filter eq 'deleted') ? 1 : 0, | |
| 163 | tab_all_count => $status_counts{all}, | |
| 164 | tab_modified_count => $status_counts{modified}, | |
| 165 | tab_added_count => $status_counts{added}, | |
| 166 | tab_deleted_count => $status_counts{deleted}, | |
| 167 | status_filter => $status_filter, | |
| 168 | tab_qs_extra => join('', | |
| 169 | (length $q ? "&q=" . _urlenc($q) : ''), | |
| 170 | ($show_baseline ? "&baseline=1" : ''), | |
| 171 | ), | |
| 104 | 172 | }; |
| 105 | 173 | my @body = $tpl->template('schema_changes.html', $tvars); |
| 106 | 174 | MODS::PageWrapper->new->wrapper( |
| 107 | 175 | page_title => 'Schema changes', page_key => 'schema_changes', |
| 108 | 176 | body_html => join('', @body), userinfo => { display_name => 'Operator' }, |
| 109 | 177 | ); |
| 178 | ||
| 179 | sub _urlenc { | |
| 180 | my $s = shift; $s //= ''; | |
| 181 | $s =~ s/([^A-Za-z0-9\-\._~])/sprintf('%%%02X', ord($1))/eg; | |
| 182 | return $s; | |
| 183 | } |