Diff -- /var/www/vhosts/3dshawn.com/site1/schema_changes.cgi

O Operator
Diff

/var/www/vhosts/3dshawn.com/site1/schema_changes.cgi

added on local at 2026-07-10 22:19:07

Added
+93
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to 9efbed09b8bf
Restore this content →
Unified diff
Naive LCS-based line diff. Additions in emerald, deletions in rose. Both sides decompressed live from BLOB_STORE.
1#!/usr/bin/perl
2use strict;
3use warnings;
4use CGI ();
5use MODS::Config; use MODS::DBConnect; use MODS::Template; use MODS::PageWrapper;
6use MODS::Charts;
7use POSIX ();
8my $cgi = CGI->new; my $db = MODS::DBConnect->new; my $tpl = MODS::Template->new;
9$|=1; print "Content-Type: text/html; charset=utf-8\nCache-Control: no-cache\n\n";
10my $dbh = $db->db_connect or die "DB connect failed\n";
11
12my $days = int($cgi->param('days') || 30); $days = 1 if $days < 1; $days = 365 if $days > 365;
13my $q = $cgi->param('q') || '';
14
15my $where = "change_datetime >= DATE_SUB(NOW(), INTERVAL $days DAY)";
16$where .= " AND (database_name LIKE " . $dbh->quote('%'.$q.'%') . " OR table_name LIKE " . $dbh->quote('%'.$q.'%') . ")" if length $q;
17
18my @rows = $db->db_readwrite_multiple($dbh, qq~
19 SELECT sc.schema_change_id AS id, sc.database_name, sc.table_name, sc.changes,
20 sc.change_datetime, sc.is_ts_only, s.server_name
21 FROM SCHEMA_CHANGE sc
22 LEFT JOIN SERVERS s ON s.server_id = sc.server_id
23 WHERE $where
24 ORDER BY sc.change_datetime DESC
25 LIMIT 200
26~, __FILE__, __LINE__);
27# --- 30-day sparkline per database.table (schema DDL is much sparser
28# than file activity, so one bar per week reads better than one
29# per day, but we still ask for 30 days of buckets to reuse the
30# helper unchanged).
31my %spark;
32{
33 my %pairs;
34 foreach my $r (@rows) {
35 my $key = ($r->{database_name} // '') . '|' . ($r->{table_name} // '');
36 $pairs{$key} = [ $r->{database_name}, $r->{table_name} ];
37 }
38 my @conds;
39 foreach my $p (values %pairs) {
40 my $qd = $dbh->quote($p->[0] // '');
41 my $qt = $dbh->quote($p->[1] // '');
42 push @conds, "(database_name = $qd AND table_name = $qt)";
43 }
44 if (@conds) {
45 my $or = join(' OR ', @conds);
46 my @day_rows = $db->db_readwrite_multiple($dbh, qq~
47 SELECT database_name, table_name,
48 DATE_FORMAT(change_datetime, '%Y-%m-%d') AS d,
49 COUNT(*) AS n
50 FROM SCHEMA_CHANGE
51 WHERE ($or)
52 AND change_datetime >= DATE_SUB(NOW(), INTERVAL 30 DAY)
53 GROUP BY database_name, table_name, d
54 ~, __FILE__, __LINE__);
55 my %by;
56 foreach my $dr (@day_rows) {
57 my $key = ($dr->{database_name} // '') . '|' . ($dr->{table_name} // '');
58 $by{$key}{$dr->{d}} = $dr->{n};
59 }
60 my $now = time;
61 my @day_keys;
62 for (my $i = 29; $i >= 0; $i--) {
63 push @day_keys, POSIX::strftime('%Y-%m-%d', localtime($now - $i * 86400));
64 }
65 foreach my $key (keys %pairs) {
66 my @series;
67 for my $k (@day_keys) { push @series, ($by{$key}{$k} || 0); }
68 $spark{$key} = \@series;
69 }
70 }
71}
72
73foreach my $r (@rows) {
74 my $c = $r->{changes} // '';
75 $c =~ s/\s+/ /g;
76 $r->{changes_preview} = length($c) > 100 ? substr($c, 0, 97) . '...' : $c;
77 $r->{diff_url} = "/diff.cgi?kind=schema&id=$r->{id}";
78 my $key = ($r->{database_name} // '') . '|' . ($r->{table_name} // '');
79 my $series = $spark{$key};
80 $r->{sparkline_svg} = $series
81 ? MODS::Charts::sparkline(values => $series, width => 88, height => 20, color => '#f59e0b')
82 : '';
83}
84my $total = scalar @rows;
85
86$db->db_disconnect($dbh);
87
88my $tvars = { rows => \@rows, has_rows => $total ? 1 : 0, total => $total, days => $days, q => $q };
89my @body = $tpl->template('schema_changes.html', $tvars);
90MODS::PageWrapper->new->wrapper(
91 page_title => 'Schema changes', page_key => 'schema_changes',
92 body_html => join('', @body), userinfo => { display_name => 'Operator' },
93);