2054397 - Test Perl code for use of vendor-specific SQL

Status: RESOLVED FIXED

(Bugzilla :: Testing Suite, enhancement)

Description

[Dave Miller [:justdave]]

I wrote a test to look for vendor-specific SQL in Bugzilla's Perl code. It scans for vendor-specific SQL tokens inside Perl string literals and heredocs, and flags any that have Bugzilla::DB helper alternatives (and suggests which one to use in the test output).

And it found a lot in the current Harmony tree:

#   Failed test 'no raw vendor-specific SQL where a Bugzilla DB helper exists'
#   at t/013db_portability.t line 80.
#          got: '7'
#     expected: '0'
# Bugzilla/Report/SecurityRisk.pm:291: use $dbh->sql_string_concat(...) instead of CONCAT() (            CONCAT(removed) AS removed,
# )
# Bugzilla/Report/SecurityRisk.pm:292: use $dbh->sql_string_concat(...) instead of CONCAT() (            CONCAT(added) AS added
# )
# extensions/BMO/lib/Reports/Triage.pm:317: use $dbh->sql_string_concat(...) instead of CONCAT() (                    WHERE  bugs_1.bug_id = bugs.bug_id AND CONCAT(flagtypes_1.name, flags_1.status) = 'needinfo?')))
# )
# extensions/BMO/lib/Reports/UserActivity.pm:185: use $dbh->sql_date_format(...) instead of DATE_FORMAT() (                   DATE_FORMAT(longdescs.bug_when, '%Y-%m-%d %H:%i:%s') AS ts,
# )
# extensions/BugModal/lib/ActivityStream.pm:361: use $dbh->sql_date_format(...) instead of UNIX_TIMESTAMP() (               UNIX_TIMESTAMP(bug_when), " . $dbh->sql_date_format('bug_when') . ",
# )
# extensions/ProdCompSearch/lib/WebService.pm:210: use $dbh->sql_string_concat(...) instead of CONCAT() (      "CONCAT(products.name, components.name) LIKE "
# )
# extensions/UserProfile/lib/Util.pm:250: use $dbh->sql_string_concat(...) instead of CONCAT() (    SELECT CONCAT('RESOLVED/', added) AS status, COUNT(*) AS count
# )
# Looks like you failed 1 test of 1.
t/013db_portability.t .. 
not ok 1 - no raw vendor-specific SQL where a Bugzilla DB helper exists
1..1
Dubious, test returned 1 (wstat 256, 0x100)
Failed 1/1 subtests 

Test Summary Report
-------------------
t/013db_portability.t (Wstat: 256 (exited 1) Tests: 1 Failed: 1)
  Failed test:  1
  Non-zero exit status: 1
Files=1, Tests=1,  1 wallclock secs ( 0.01 usr  0.01 sys +  0.29 cusr  0.11 csys =  0.42 CPU)
Result: FAIL

Comment 1

[BMO Github Automation]

Attachment: [bugzilla/harmony] Bug 2054397: Test Perl code for vendor-specific SQL (#171)

Comment 2

[Dave Miller [:justdave]]

After adding the scripts directory to the scope of the test, it has the following additional failure:

#   Failed test 'no raw vendor-specific SQL where a Bugzilla DB helper exists'
#   at t/013db_portability.t line 93.
#          got: '1'
#     expected: '0'
# scripts/nagios_blocker_checker.pl:187: use $dbh->sql_date_format(...) instead of UNIX_TIMESTAMP() (        SELECT bug_id, bug_severity, UNIX_TIMESTAMP(bugs.creation_ts) AS ts
# )
# Looks like you failed 1 test of 1.
t/013db_portability.t .. 
not ok 1 - no raw vendor-specific SQL where a Bugzilla DB helper exists
1..1
Dubious, test returned 1 (wstat 256, 0x100)
Failed 1/1 subtests 

Test Summary Report
-------------------
t/013db_portability.t (Wstat: 256 (exited 1) Tests: 1 Failed: 1)
  Failed test:  1
  Non-zero exit status: 1
Files=1, Tests=1,  1 wallclock secs ( 0.03 usr  0.01 sys +  0.29 cusr  0.06 csys =  0.39 CPU)
Result: FAIL

Comment 3

[Dave Miller [:justdave]]

sql_date_format() can't reliably create a unix_timestamp() equivalent on all database engines we support (in fact, that only actually works on sqlite). So I'm going to add a $dbh->sql_date_to_epoch() abstraction method to cover that. Each of the DB engines has a different way to generate a unix timestamp.

Comment 4

[Dave Miller [:justdave]]

Comment on attachment 9607018 [details] [review]
[bugzilla/harmony] Bug 2054397: Test Perl code for vendor-specific SQL (#171)

Landed
https://github.com/bugzilla/harmony/commit/2a84b4de6325f1a8bb9624a83c8a727ce69bb87a