Class: Server

Inherits:

Sinatra::Base

• Object
• Sinatra::Base
• Server

Defined in:

app/server.rb

Overview

SQLUI Sinatra server.

Defined Under Namespace

Classes: ClientError

Class Method Summary

• .create_github_cache ⇒ Object
• .init_and_run(config, resources_dir) ⇒ Object

Instance Method Summary

• #big_decimal_to_float(maybe_big_decimal) ⇒ Object
• #execute_query(client, variables, queries) ⇒ Object
• #find_selected_queries(full_sql, selection) ⇒ Object

Class Method Details

.create_github_cache ⇒ Object

# File 'app/server.rb', line 377

def self.create_github_cache
  return Github::Cache.new({}, logger: Sqlui.logger) unless ENV['USE_LOCAL_SAVED_FILES']

  paths = Dir.glob('sql/**/*.sql')
  blobs = paths.map do |path|
    {
      'path' => path,
      'url' => "https://api.github.com/repos/nicholasdower/sqlui/git/blobs/#{Base64.encode64(path)}"
    }
  end
  github_cache_hash = {
    'https://api.github.com/repos/nicholasdower/sqlui/git/trees/master?recursive=true' =>
      Github::Cache::Entry.new(
        {
          'sha' => 'foo',
          'truncated' => false,
          'tree' => blobs
        }, 60 * 60 * 24 * 365
      )
  }
  paths.each do |path|
    github_cache_hash["https://api.github.com/repos/nicholasdower/sqlui/git/blobs/#{Base64.encode64(path)}"] =
      Github::Cache::Entry.new(
        {
          'content' => Base64.encode64(File.read(path))
        }, 60 * 60 * 24 * 365
      )
  end
  Github::Cache.new(github_cache_hash, logger: Sqlui.logger)
end

.init_and_run(config, resources_dir) ⇒ Object

# File 'app/server.rb', line 39

def self.init_and_run(config, resources_dir)
  Sqlui.logger.info("Starting SQLUI v#{Version::SQLUI}")
  Sqlui.logger.info("Airbrake enabled: #{config.airbrake[:server]&.[](:enabled) || false}")

  WEBrick::HTTPRequest.const_set('MAX_URI_LENGTH', 2 * 1024 * 1024)

  if config.airbrake[:server]&.[](:enabled)
    require 'airbrake'
    require 'airbrake/rack'

    Airbrake.configure do |c|
      c.app_version = Version::SQLUI
      c.environment = config.environment
      c.logger.level = Logger::DEBUG if config.environment != :production?
      config.airbrake[:server].each do |key, value|
        c.send("#{key}=".to_sym, value) unless key == :enabled
      end
    end
    Airbrake.add_filter(Airbrake::Rack::RequestBodyFilter.new)
    Airbrake.add_filter(Airbrake::Rack::HttpParamsFilter.new)
    Airbrake.add_filter(Airbrake::Rack::HttpHeadersFilter.new)
    use Airbrake::Rack::Middleware
  end

  use Rack::Deflater
  use Prometheus::Middleware::Collector
  use Prometheus::Middleware::Exporter

  Mysql2::Client.default_query_options[:as] = :array
  Mysql2::Client.default_query_options[:cast_booleans] = true
  Mysql2::Client.default_query_options[:database_timezone] = :utc
  Mysql2::Client.default_query_options[:cache_rows] = false

  set :logging,         true
  set :bind,            '0.0.0.0'
  set :port,            config.port
  set :environment,     config.environment
  set :raise_errors,    false
  set :show_exceptions, false

  get '/-/health' do
    status 200
    body 'OK'
  end

  get '/?' do
    redirect config.base_url_path, 301
  end

  resource_path_map = {}
  Dir.glob(File.join(resources_dir, '*')).each do |file|
    hash = Digest::MD5.hexdigest(File.read(file))
    basename = File.basename(file)
    url_path = "#{config.base_url_path}/#{basename}"
    case File.extname(basename)
    when '.svg'
      content_type = 'image/svg+xml; charset=utf-8'
    when '.css'
      content_type = 'text/css; charset=utf-8'
    when '.js'
      content_type = 'text/javascript; charset=utf-8'
    else
      raise "unsupported resource file extension: #{File.extname(basename)}"
    end
    resource_path_map[basename] = "#{url_path}?#{hash}"
    get url_path do
      headers 'Content-Type' => content_type
      headers 'Cache-Control' => 'max-age=31536000'
      send_file file
    end
  end

  get "#{config.base_url_path}/?" do
    headers 'Cache-Control' => 'no-cache'
    erb :databases, locals: { config: config, resource_path_map: resource_path_map }
  end

  github_cache = create_github_cache
  config.database_configs.each do |database|
    if (saved_config = database.saved_config)
      tree_client = Github::TreeClient.new(
        access_token: database.saved_config.token,
        cache: github_cache,
        logger: Sqlui.logger
      )
      # Prefetch all trees on startup. This should happen before the health endpoint is available.
      unless ENV.fetch('DISABLE_PREFETCH', '0') == '1'
        tree_client.get_tree(
          owner: saved_config.owner,
          repo: saved_config.repo,
          ref: saved_config.branch,
          regex: saved_config.regex
        )
      end
    end

    get "#{config.base_url_path}/#{database.url_path}/?" do
      redirect "#{database.url_path}/query", 301
    end

    post "#{config.base_url_path}/#{database.url_path}/metadata" do
      tree = nil
      if (saved_config = database.saved_config)
        tree = tree_client.get_tree(
          owner: saved_config.owner,
          repo: saved_config.repo,
          ref: saved_config.branch,
          regex: saved_config.regex
        )
        if params[:file]
          owner, repo, ref, path = Github::Paths.parse_file_path(params[:file])
          raise ClientError, "invalid owner: #{owner}" unless tree.owner == owner
          raise ClientError, "invalid repo: #{repo}" unless tree.repo == repo

          requested_tree = tree_client.get_tree(
            owner: owner,
            repo: repo,
            ref: ref,
            regex: /#{Regexp.escape(path)}/,
            cache: ref == tree.ref
          )
          raise ClientError.new("file not found: #{params[:file]}", status: 404) unless requested_tree[path]

          tree << requested_tree[path]
        end
      end

      metadata = database.with_client do |client|
        {
          server: "#{config.name} - #{database.display_name}",
          base_url_path: config.base_url_path,
          schemas: DatabaseMetadata.lookup(client, database),
          tables: database.tables,
          columns: database.columns,
          joins: database.joins,
          saved: (tree || {}).to_h do |file|
            [
              file.full_path,
              {
                filename: file.full_path,
                path: file.path,
                github_url: file.github_url,
                contents: file.content,
                tree_sha: file.tree_sha
              }
            ]
          end
        }
      end
      status 200
      headers 'Content-Type' => 'application/json; charset=utf-8'
      body metadata.to_json
    end

    post "#{config.base_url_path}/#{database.url_path}/query" do
      data = request.body.read
      request.body.rewind # since Airbrake will read the body on error
      params.merge!(JSON.parse(data, symbolize_names: true))
      raise ClientError, 'ERROR: missing sql' unless params[:sql]

      variables = params[:variables] || {}
      queries = find_selected_queries(params[:sql], params[:selection])

      status 200
      headers 'Content-Type' => 'application/json; charset=utf-8'

      stream do |out|
        database.with_client do |client|
          begin
            query_result = execute_query(client, variables, queries)
          rescue Mysql2::Error => e
            stacktrace = e.full_message(highlight: false)
            message = "ERROR #{e.error_number} (#{e.sql_state}): #{e.message.lines.first&.strip || 'unknown error'}"
            out << { error: message, stacktrace: stacktrace }.compact.to_json
            break
          rescue StandardError => e
            stacktrace = e.full_message(highlight: false)
            message = e.message.lines.first&.strip || 'unknown error'
            out << { error: message, stacktrace: stacktrace }.compact.to_json
            break
          end

          if query_result
            json = "              {\n                \"columns\": \#{query_result.fields.to_json},\n                \"column_types\": \#{MysqlTypes.map_to_google_charts_types(query_result.field_types).to_json},\n                \"selection\": \#{params[:selection].to_json},\n                \"query\": \#{params[:sql].to_json},\n                \"rows\": [\n            RES\n            out << json\n            bytes_written = json.bytesize\n            max_rows_written = false\n            rows_written = 0\n            total_rows = 0\n            query_result.each_with_index do |row, i|\n              total_rows += 1\n              next if max_rows_written\n\n              json = \"\#{i.zero? ? '' : ','}\\n    \#{row.map { |v| big_decimal_to_float(v) }.to_json}\"\n              bytesize = json.bytesize\n              if bytes_written + bytesize > Sqlui::MAX_BYTES\n                max_rows_written = true\n                next\n              end\n\n              out << json\n              bytes_written += bytesize\n              rows_written += 1\n\n              if rows_written == Sqlui::MAX_ROWS\n                max_rows_written = true\n                next\n              end\n            end\n            out << <<~RES\n\n                ],\n                \"total_rows\": \#{total_rows}\n              }\n            RES\n          else\n            out << <<~RES\n              {\n                \"columns\": [],\n                \"column_types\": [],\n                \"total_rows\": 0,\n                \"selection\": \#{params[:selection].to_json},\n                \"query\": \#{params[:sql].to_json},\n                \"rows\": []\n              }\n            RES\n          end\n        end\n      end\n    end\n\n    get \"\#{config.base_url_path}/\#{database.url_path}/download_csv\" do\n      raise ClientError, 'missing sql' unless params[:sql]\n\n      sql = Base64.decode64(params[:sql]).force_encoding('UTF-8')\n      variables = params.map { |k, v| k[0] == '_' ? [k, v] : nil }.compact.to_h\n      queries = find_selected_queries(sql, params[:selection])\n\n      content_type 'application/csv; charset=utf-8'\n      headers 'Cache-Control' => 'no-cache'\n      attachment 'result.csv'\n      status 200\n\n      stream do |out|\n        database.with_client do |client|\n          begin\n            query_result = execute_query(client, variables, queries)\n          rescue Mysql2::Error => e\n            stacktrace = e.full_message(highlight: false)\n            message = \"ERROR \#{e.error_number} (\#{e.sql_state}): \#{e.message.lines.first&.strip || 'unknown error'}\"\n            out << { error: message, stacktrace: stacktrace }.compact.to_json\n            break\n          rescue StandardError => e\n            stacktrace = e.full_message(highlight: false)\n            message = e.message.lines.first&.strip || 'unknown error'\n            out << { error: message, stacktrace: stacktrace }.compact.to_json\n            break\n          end\n          out << CSV::Row.new(query_result.fields, query_result.fields, header_row: true).to_s.strip\n          query_result.each do |row|\n            out << \"\\n\#{CSV::Row.new(query_result.fields, row.map { |v| big_decimal_to_float(v) }).to_s.strip}\"\n          end\n        end\n      end\n    end\n\n    get(/\#{Regexp.escape(\"\#{config.base_url_path}/\#{database.url_path}/\")}(query|graph|saved|structure|help)/) do\n      status 200\n      headers 'Cache-Control' => 'no-cache'\n      client_config = config.airbrake[:client] || {}\n      erb :sqlui, locals: {\n        environment: config.environment.to_s,\n        airbrake_enabled: client_config[:enabled] || false,\n        airbrake_project_id: client_config[:project_id] || '',\n        airbrake_project_key: client_config[:project_key] || '',\n        resource_path_map: resource_path_map\n      }\n    end\n\n    post(\"\#{config.base_url_path}/\#{database.url_path}/save-file\") do\n      raise ClientError, 'saved files disabled' unless (saved_config = database.saved_config)\n      raise ClientError, 'missing base_sha' if (params[:base_sha] || '').strip.empty?\n      raise ClientError, 'missing path' if (params[:path] || '').strip.empty?\n      raise ClientError, 'missing content' if params[:path].nil?\n\n      branch = \"sqlui/\#{Pigs.generate_phrase}\"\n      tree_client = Github::TreeClient.new(\n        access_token: database.saved_config.token,\n        cache: github_cache,\n        logger: Sqlui.logger\n      )\n      tree_client.create_commit_with_file(\n        owner: saved_config.owner,\n        repo: saved_config.repo,\n        base_sha: params[:base_sha],\n        branch: branch,\n        path: params[:path],\n        content: params[:content].gsub(\"\\r\\n\", \"\\n\"),\n        author_name: database.saved_config.author_name,\n        author_email: database.saved_config.author_email\n      )\n      status 201\n      erb :redirect, locals: {\n        resource_path_map: resource_path_map,\n        location: \"https://github.com/\#{saved_config.owner}/\#{saved_config.repo}/compare/\#{branch}\"\n      }\n    end\n  end\n\n  error do |exception|\n    status exception.is_a?(ClientError) ? exception.status : 500\n    stacktrace = exception.full_message(highlight: false)\n    if request.env['HTTP_ACCEPT'] == 'application/json'\n      headers 'Content-Type' => 'application/json; charset=utf-8'\n      message = exception.message.to_s\n      json = { error: message, stacktrace: stacktrace }.compact.to_json\n      body json\n    else\n      erb :error, locals: {\n        resource_path_map: resource_path_map,\n        title: \"SQLUI \#{message}\",\n        heading: \"\#{status} \#{Rack::Utils::HTTP_STATUS_CODES[status]}\",\n        message: exception.message,\n        stacktrace: stacktrace\n      }\n    end\n  end\n\n  run!\nend\n".chomp

Instance Method Details

#big_decimal_to_float(maybe_big_decimal) ⇒ Object

# File 'app/server.rb', line 438

def big_decimal_to_float(maybe_big_decimal)
  # TODO: This BigDecimal thing needs some thought.
  if maybe_big_decimal.is_a?(BigDecimal)
    big_decimal_string = maybe_big_decimal.to_s('F')
    float = maybe_big_decimal.to_f
    if big_decimal_string == float.to_s
      float
    else
      big_decimal_string
    end
  else
    maybe_big_decimal
  end
end

#execute_query(client, variables, queries) ⇒ Object

# File 'app/server.rb', line 428

def execute_query(client, variables, queries)
  variables.each do |name, value|
    client.query("SET @#{name} = #{value};")
  end
  queries[0..-2].map do |current|
    client.query(current, stream: true)&.free
  end
  client.query(queries[-1], stream: true)
end

#find_selected_queries(full_sql, selection) ⇒ Object

# File 'app/server.rb', line 408

def find_selected_queries(full_sql, selection)
  if selection
    if selection.include?('-')
      # sort because the selection could be in either direction
      selection = selection.split('-').map { |v| Integer(v) }.sort
    else
      selection = Integer(selection)
      selection = [selection, selection]
    end

    if selection[0] == selection[1]
      [SqlParser.find_statement_at_cursor(full_sql, selection[0])]
    else
      SqlParser.split(full_sql[selection[0], selection[1]])
    end
  else
    SqlParser.split(full_sql)
  end
end