Websites/bugs.webkit.org/t/013dbschema.t

# This Source Code Form is subject to the terms of the Mozilla Public
# License, v. 2.0. If a copy of the MPL was not distributed with this
# file, You can obtain one at http://mozilla.org/MPL/2.0/.
#
# This Source Code Form is "Incompatible With Secondary Licenses", as
# defined by the Mozilla Public License, v. 2.0.

##################
#Bugzilla Test 13#
#####schema#######

# Check the Bugzilla database schema to ensure no field names conflict
# with SQL reserved words.

use 5.10.1;
use strict;
use warnings;

use lib qw(. t lib);
use Bugzilla;
use Bugzilla::DB::Schema;


# SQL reserved words
use constant RESERVED_WORDS => qw(
    ABSOLUTE ACTION ACTOR ADD AFTER ALL ALLOCATE ALTER ANY AND ARE AS ASC ASSERTION ASYNC AT
    ATTRIBUTES BEFORE BEGIN BETWEEN BIT BIT_LENGTH BOOLEAN BOTH BREADTH BY CALL CASCADE
    CASCADED CASE CAST CATALOG CHAR CHARACTER_LENGTH CHAR_LENGTH COLLATE
    COLLATION COLUMN COMPLETION CONNECT CONNECTION CONSTRAINT CONSTRAINTS
    CONVERT CORRESPONDING CREATE CROSS CURRENT_DATE CURRENT_PATH CURRENT_TIME
    CURRENT_TIMESTAMP CURRENT_USER CYCLE DATA DATE DAY DEALLOCATE DECLARE DEFAULT DEFERRABLE
    DEFERRED DELETE DEPTH DESC DESCRIBE DESCRIPTOR DESTROY DIAGNOSTICS DICTIONARY
    DISCONNECT DISTINCT DO DOMAIN DROP EACH ELEMENT ELSE ELSEIF END END-EXEC EQUALS EXCEPT
    EXCEPTION EXECUTE EXTERNAL EXTRACT FACTOR FALSE FIRST FOR FROM FULL GENERAL GET
    GLOBAL GRANT GROUP HAVING HOLD HOUR IDENTITY IF IGNORE IMMEDIATE IN INITIALLY INNER INPUT
    INSENSITIVE INSERT INSTEAD INTERSECT INTERVAL IS ISOLATION JOIN LAST LEADING LEAVE
    LEFT LESS LEVEL LIMIT LIST LOCAL LOOP LOWER MATCH MINUTE MODIFY MONTH NAMES
    NATIONAL NATURAL NCHAR NEW NEW_TABLE NEXT NO NONE NOT NULL NULLIF OBJECT
    OCTET_LENGTH OFF OID OLD OLD_TABLE ONLY OPERATION OPERATOR OPERATORS OR ORDER OTHERS
    OUTER OUTPUT OVERLAPS PAD PARAMETERS PARTIAL PATH PENDANT POSITION POSTFIX
    PREFIX PREORDER PREPARE PRESERVE PRIOR PRIVATE PROTECTED READ RECURSIVE REF
    REFERENCING RELATIVE REPLACE RESIGNAL RESTRICT RETURN RETURNS REVOKE RIGHT
    ROLE ROUTINE ROW ROWS SAVEPOINT SCROLL SEARCH SECOND SELECT SENSITIVE SEQUENCE
    SESSION SESSION_USER SIGNAL SIMILAR SIZE SPACE SQLEXCEPTION SQLSTATE
    SQLWARNING START STATE STRUCTURE SUBSTRING SYMBOL SYSTEM_USER TABLE TEMPORARY
    TERM TEST THEN THERE TIME TIMESTAMP TIMEZONE_HOUR TIMEZONE_MINUTE TRAILING
    TRANSACTION TRANSLATE TRANSLATION TRIGGER TRIM TRUE TUPLE UNDER
    UNKNOWN UNION UNIQUE UPDATE UPPER USAGE USING VARCHAR VARIABLE VARYING VIEW VIRTUAL VISIBLE
    WAIT WHEN WHERE WHILE WITH WITHOUT WRITE YEAR ZONE
);

# Few Exceptions are removed from the above list
# i.e. VALUE, TYPE, ALIAS, COALESCE

our $dbh;
our $schema;
our @tables;

BEGIN {
    $schema = Bugzilla::DB::Schema->new("Mysql");
    @tables = $schema->get_table_list();
}

use Test::More tests => scalar(@tables);

foreach my $table (@tables) {
    my @reserved;

    if (grep { uc($table) eq $_ } RESERVED_WORDS) {
        push(@reserved, $table);
    }

    foreach my $column ($schema->get_table_columns($table)) {
        if (grep { uc($column) eq $_ } RESERVED_WORDS) {
            push(@reserved, $column);
        }
    }

    if (scalar @reserved) {
        ok(0, "Table $table use reserved words: " . join(", ", @reserved));
    }
    else {
        ok(1, "Table $table does not use reserved words");
    }
}

exit 0;