class App::MySQL

Constants

CONFIG_FILENAME_STRUCTURE
CONFIG_FILENAME_STRUCTURE_FKS
CONFIG_SCHEMA
MOCK_FILENAME_STRUCTURE
MOCK_FILENAME_STRUCTURE_FKS
MOCK_SCHEMA

Public Class Methods

reset_full_mock_data(site, skip_confirm = false, verbose = false) click to toggle source

Reset the DB. @return void

# File lib/core/mysql.rb, line 17
def self.reset_full_mock_data(site, skip_confirm = false, verbose = false)

    begin

        # TODO - 4/13/18 - IMPLEMENT OPTIONAL CONFIG RESET FLAG.
        Blufin::Terminal::output("Must create flag to make #{CONFIG_SCHEMA} reset optional.", Blufin::Terminal::MSG_TODO)

        @site = Blufin::SiteResolver::validate_site(site)
        @site_name = Blufin::SiteResolver::get_site_name(@site)

        @error_handler = Blufin::SqlErrorHandler.new(@site)

        @sql_mismatch_errors = []
        @vsf_max = []
        @asf_max = []

        @schema_data = Blufin::YmlCacheHandler::get(@site, Blufin::YmlCacheHandler::SCHEMA_DATA)
        @schema_fks_hash = Blufin::YmlCacheHandler::get(@site, Blufin::YmlCacheHandler::SCHEMA_FKS)

        schemas = Blufin::YmlSchemaValidator::VALID_SCHEMAS_GENERATE
        warning_message = ['This process cannot be reversed and all current data will be lost in the following schemas:', nil]
        has_path_to_ruby = !Blufin::Config::get_path('Paths', 'BlufinRuby').nil?

        Blufin::Terminal::output("Path to ruby not found in #{Blufin::Terminal::format_directory(App::CONFIG_FILE)}, #{Blufin::Terminal::format_highlight(App::MySQL::CONFIG_SCHEMA)} table will not be generated.", Blufin::Terminal::MSG_WARNING) unless has_path_to_ruby

        schemas.each do |schema|
            if schema == Blufin::YmlSchemaValidator::CONFIG
                warning_message << CONFIG_SCHEMA if has_path_to_ruby
            elsif schema == Blufin::YmlSchemaValidator::MOCK
                warning_message << MOCK_SCHEMA if has_path_to_ruby
            else
                warning_message << "#{Blufin::SiteResolver::get_site_name(@site)}-#{schema}"
            end
        end

        sql_data_files = get_sql_data_files

        # Validates all the data-insert statements.
        sql_data_files.each { |file| validate_sql_data_file(file) } if sql_data_files.any?

        generate_sql_mismatch_errors if @sql_mismatch_errors.any?

        # If SQL data errors exist, script will not execute past this point.
        return if @error_handler.display_errors_if_any

        # TODO DB - Need to replace with DBResolver.

        cli_host = Blufin::Config::get['Databases'][0]['Host']
        cli_user = Blufin::Config::get['Databases'][0]['User']
        cli_pass = Blufin::Config::get['Databases'][0]['Password']
        cli_host = cli_host == 'nil' ? '' : " -h #{cli_host}"
        cli_user = cli_user == 'nil' ? '' : " -u#{cli_user}"
        cli_pass = cli_pass == 'nil' ? '' : " -p#{cli_pass.gsub('$', '\$')}"
        cli_verbose = verbose ? '' : ' &>/dev/null';

        if skip_confirm || Blufin::Terminal::prompt_yes_no("Are you sure you want to #{Blufin::Terminal::format_highlight('completely reset')} your database(s)?", warning_message)

            commands_truncate = []
            commands_build_schema = []
            commands_build_fks = []
            commands_insert_data = []

            schemas.each do |schema|

                structure_path = get_structure_path(schema)
                data_path = get_data_path(schema)

                # Skip if user doesn't have the PATH_TO_RUBY config value.
                next if structure_path.nil? || data_path.nil?

                site_schema = (schema == Blufin::YmlSchemaValidator::CONFIG) ? CONFIG_SCHEMA : "#{@site_name}-#{schema}"
                structure_file = "#{structure_path}/" + ((schema == Blufin::YmlSchemaValidator::CONFIG) ? CONFIG_FILENAME_STRUCTURE : "#{@site_name}-#{schema}.sql")
                foreign_key_file = "#{structure_path}/" + ((schema == Blufin::YmlSchemaValidator::CONFIG) ? CONFIG_FILENAME_STRUCTURE_FKS : "#{@site_name}-fks-#{schema}.sql")

                commands_truncate << "mysql#{cli_host}#{cli_user}#{cli_pass} -e 'set foreign_key_checks = 0; drop database if exists `#{site_schema}`; create database `#{site_schema}`; set foreign_key_checks = 1;'#{cli_verbose}"
                commands_build_schema << "mysql#{cli_host}#{cli_user}#{cli_pass} #{site_schema} < #{structure_file}#{cli_verbose}" if Blufin::Files::file_exists(structure_file)
                commands_build_fks << "mysql#{cli_host}#{cli_user}#{cli_pass} #{site_schema} < #{foreign_key_file}#{cli_verbose}" if Blufin::Files::file_exists(foreign_key_file)

                path_vcs = "#{data_path}/#{schema}"

                # Generate path(s) if they don't exist.
                unless Blufin::Files::path_exists(path_vcs)
                    Blufin::Files::create_directory(path_vcs)
                    Blufin::Terminal::output(Blufin::Terminal::format_directory(path_vcs), Blufin::Terminal::MSG_GENERATED)
                end

                # Generates all the data-insert commands.
                Blufin::Files::get_files_in_dir(path_vcs).each { |file| commands_insert_data << "mysql#{cli_host}#{cli_user}#{cli_pass} #{site_schema} < #{file}#{cli_verbose}" if file_exists_and_has_data(file) } if sql_data_files.any?

            end

            Blufin::Terminal::info("Destroying #{Blufin::Terminal::format_highlight('schema(s)')}")
            Blufin::Terminal::command(commands_truncate)
            Blufin::Terminal::info("Creating #{Blufin::Terminal::format_highlight('schema(s)')}")
            Blufin::Terminal::command(commands_build_schema)
            Blufin::Terminal::info("Inserting #{Blufin::Terminal::format_highlight('mock data')}")
            Blufin::Terminal::command(commands_insert_data)
            Blufin::Terminal::info("Adding #{Blufin::Terminal::format_highlight('foreign key constraints')}")
            Blufin::Terminal::command(commands_build_fks)
            Blufin::Terminal::success('Reset of MySQL data done', 'You should now have a clean database.')

        end

    rescue => e

        Blufin::Terminal::print_exception(e)

    end

end

Private Class Methods

add_sql_mismatch_error(data_fields, path_and_file, schema, table) click to toggle source

Adds a SQL MISMATCH error to tmp Array for later processing. To get around the spacing problem. @return void

# File lib/core/mysql.rb, line 233
def self.add_sql_mismatch_error(data_fields, path_and_file, schema, table)

    valid_schema = {}
    actual_schema = {}
    error_line = nil
    error_group = nil
    idx_counter = 0

    @schema_data[schema][table].keys.each do |key|
        next if key =~ /\A(#{Blufin::YmlSchemaValidator::VALID_SCHEMAS_REGEX})\.[a-z_]+\[\]\z/ || key =~ /\A(#{Blufin::YmlSchemaValidator::VALID_SCHEMAS_REGEX})\.[a-z_]+\z/ || key =~/\A[a-z_.]+\[#{Blufin::YmlSchemaValidator::LINK}\]/
        valid_schema[idx_counter] = key
        idx_counter += 1
    end

    data_fields.each_with_index do |data_group, group_idx|
        actual_schema_group = {}
        data_group[1].each_with_index do |data, data_idx|
            actual_schema_group[data_idx] = data[1][0]
            if error_line.nil? && data[1][0] != valid_schema[data_idx]
                error_line = data[1][1]
                error_group = group_idx
            end
        end
        actual_schema[group_idx] = actual_schema_group
    end

    if actual_schema.keys.length > 0
        valid_schema.each do |field_idx, field|
            actual_schema.each do |group_idx, group_data|

                if error_line.nil? && group_data[field_idx] != field

                    index_in_group = field_idx + 1
                    valid_group_lines = (valid_schema.keys.length * 2) + 4

                    # This 'might' not be 100% accurate.
                    error_line = (valid_group_lines * group_idx) + index_in_group + 1
                    error_group = group_idx

                end

            end

        end

    end

    unless error_line.nil?
        vsf = []
        vsc = []
        asf = []
        asc = []
        valid_schema.each { |vs| vsf << vs[1] }
        actual_schema[error_group].each { |as| asf << as[1] }
        @vsf_max << vsf.max_by(&:length).length + 5
        @asf_max << asf.max_by(&:length).length + 5
        (0..([vsf.length, asf.length].max - 1)).each do |n|
            vsc << (vsf[n].nil? ? '' : vsf[n])
            asc << (asf[n].nil? ? '' : asf[n])
        end
        @sql_mismatch_errors << [path_and_file, error_line, vsc, asc]
    end
end
file_exists_and_has_data(path_and_file) click to toggle source

Checks that SQL Data file exists and has data in it. @return Boolean

# File lib/core/mysql.rb, line 132
def self.file_exists_and_has_data(path_and_file)
    begin
        Blufin::Files::read_file(path_and_file).length > 0
    rescue
        false
    end
end
generate_sql_mismatch_errors() click to toggle source

Generates SQL MISMATCH errors with even spacing. @return void

# File lib/core/mysql.rb, line 299
def self.generate_sql_mismatch_errors

    @sql_mismatch_errors.each do |error_array|

        multi_line_content = []
        vsf = error_array[2]
        asf = error_array[3]
        multi_line_content << ''
        multi_line_content << "  \x1B[38;5;246m#{'EXPECTED'.ljust(@vsf_max.max, ' ')}\x1B[0m \x1B[38;5;196m#{'ACTUAL'.ljust(@asf_max.max, ' ')}\x1B[0m"
        multi_line_content << ''
        (0..([vsf.length, asf.length].max - 1)).each do |n|
            valid = vsf[n].nil? ? '' : vsf[n]
            actual = asf[n].nil? ? '' : asf[n]
            color = (valid == actual) ? 118 : 196
            multi_line_content << "  \x1B[38;5;240m#{valid.ljust(@vsf_max.max, ' ')}\x1B[0m \x1B[38;5;#{color}m#{actual.ljust(@asf_max.max, ' ')}\x1B[0m"
        end
        multi_line_content << ''
        @error_handler.add_error(Blufin::SqlErrorHandler::ERROR_SQL_SCHEMA_MISMATCH, error_array[0], error_array[1], multi_line_content)

    end

end
get_data_path(schema) click to toggle source

@return Array

# File lib/core/mysql.rb, line 323
def self.get_data_path(schema)
    if schema == Blufin::YmlSchemaValidator::CONFIG || schema == Blufin::YmlSchemaValidator::MOCK
        path_to_blufin_ruby = Blufin::Config::get_path('Paths', 'BlufinRuby')
        return nil if path_to_blufin_ruby.nil?
        "#{path_to_blufin_ruby}/#{App::Opt::BLUFIN}#{App::Opt::OPT_PATH_SQL}/data"
    else
        "#{Blufin::SiteResolver::get_site_location(@site)}/#{Blufin::Site::PATH_TO_SQL_DATA}"
    end

end
get_sql_data_files() click to toggle source

Gets an Array of files containing all the SQL data. @return Array

# File lib/core/mysql.rb, line 142
def self.get_sql_data_files
    sql_data_files = []
    @schema_data.keys.each do |schema|
        unless @schema_data[schema].keys.nil?
            @schema_data[schema].keys.each do |table|
                path_and_file = "#{get_data_path(schema)}/#{schema}/data-#{table.gsub('_', '-')}.sql"
                sql_data_files << path_and_file if Blufin::Files::file_exists(path_and_file)
            end
        end
    end
    sql_data_files
end
get_structure_path(schema) click to toggle source

@return Array

# File lib/core/mysql.rb, line 335
def self.get_structure_path(schema)
    if schema == Blufin::YmlSchemaValidator::CONFIG || schema == Blufin::YmlSchemaValidator::MOCK
        path_to_blufin_ruby = Blufin::Config::get_path('Paths', 'BlufinRuby')
        return nil if path_to_blufin_ruby.nil?
        "#{path_to_blufin_ruby}/#{App::Opt::BLUFIN}#{App::Opt::OPT_PATH_SQL}/structure"
    else
        "#{Blufin::SiteResolver::get_site_location(@site)}/#{Blufin::Site::PATH_TO_SQL_STRUCTURE}"
    end
end
validate_sql_data_file(path_and_file) click to toggle source

Validates that the data contained in the template is still valid. If not, will add an entry to the @error Array which will prevent MySQL update from happening. @return void

# File lib/core/mysql.rb, line 158
def self.validate_sql_data_file(path_and_file)

    # TODO - Commented this out because it was rubbish!
    # TODO - Need to write a new parser/validator that STRIPS ALL WHITESPACE/NEW LINES first and then validate from that.
    return

    line_number = 0
    data_group = 0
    data_index = 0
    data_fields = {}
    data_values = {}
    extract_fields = false
    extract_values = false

    parts = path_and_file.strip.split('/')
    table = parts[parts.length - 1].gsub(/\Adata-/, '').gsub(/\.sql\z/, '').gsub('-', '_')
    schema = parts[parts.length - 2]

    Blufin::Files::read_file(path_and_file).each do |line|

        line = line.gsub("\n", '')
        line_number += 1

        if line =~ /\AINSERT IGNORE INTO\s\S+\s\(\z/
            @error_handler.add_error(Blufin::SqlErrorHandler::ERROR_SQL_STATEMENT_NOT_EXPECTED, path_and_file, line_number) if extract_fields || extract_values
            extract_fields = true
            data_index = 0
        elsif line =~ /\A  `[a-z0-9_]{2,}`,?/
            data_fields[data_group] = {} if data_fields[data_group].nil?
            field = line.match(/`[a-z0-9_]{2,}`/)
            field = field[0].gsub(/\A`/, '').gsub(/`\z/, '')
            data_fields[data_group][data_index] = [field, line_number]
            data_index += 1
        elsif line =~ /\A\)\s+VALUES\s+\(\z/
            @error_handler.add_error(Blufin::SqlErrorHandler::ERROR_SQL_STATEMENT_NOT_EXPECTED, path_and_file, line_number) unless extract_fields && !extract_values
            extract_fields = false
            extract_values = true
            data_index = 0
        elsif line =~ /\A  '(.)*',?/
            data_values[data_group] = {} if data_values[data_group].nil?
            value = line.match(/'(.)*',?/)
            value = value[0].gsub(/\A'/, '').gsub(/',?\z/, '')
            data_values[data_group][data_index] = [value, line_number]
            data_index += 1
            # TODO - I WROTE THIS THEN DECIDED IT WASN'T NECESSARY.
            # TODO - WE SHOULD PROBABLY TAKE NULL INTO CONSIDERATION FOR INTEGERS/DECIMALS... LEAVIN THIS HERE JUST SO IT GETS COMMITTED TO GIT.
            # elsif line =~ /\A  ([0-9\-\.]+|NULL),?/
            #     data_values[data_group] = {} if data_values[data_group].nil?
            #     value = line.match(/([0-9\-\.]+|NULL),?/)
            #     value = value[0].gsub(/,?\z/, '')
            #     data_values[data_group][data_index] = [value, line_number]
            #     data_index += 1
        elsif line =~ /\A  NULL,?( -- FK: )?\S*\z/
            data_values[data_group] = {} if data_values[data_group].nil?
            data_values[data_group][data_index] = [nil, line_number]
            data_index += 1
        elsif line =~ /\A\);\z/
            @error_handler.add_error(Blufin::SqlErrorHandler::ERROR_SQL_STATEMENT_NOT_EXPECTED, path_and_file, line_number) unless extract_values && !extract_fields
            extract_values = false
            data_group += 1
        elsif line == ''
        elsif line =~ /\Aset\s+(session|global)\s+sql_mode\s*=\s*'[A-Za-z_]+';\z/i
        else
            @error_handler.add_error(Blufin::SqlErrorHandler::ERROR_SQL_REGEX_MISMATCH, path_and_file, line_number)
        end

    end

    # Add error (if exists).
    add_sql_mismatch_error(data_fields, path_and_file, schema, table)

end