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