Class: GoodData::CloudResources::MSSQLClient

Inherits:
CloudResourceClient show all
Defined in:
lib/gooddata/cloud_resources/mssql/mssql_client.rb

Constant Summary collapse

MSSQL_SEPARATOR_PARAM = 
";"
MSSQL_URL_PATTERN = 
%r{jdbc:sqlserver://([^:]+)(:([0-9]+))?(/)?}
MSSQL_DEFAULT_PORT = 
1433
LOGIN_TIME_OUT = 
30
MSSQL_FETCH_SIZE = 
10_000
VERIFY_FULL = 
'verify-full'
PREFER = 
'prefer'
REQUIRE = 
'require'

Class Method Summary collapse

Instance Method Summary collapse

Methods inherited from CloudResourceClient

descendants

Constructor Details

#initialize(options = {}) ⇒ MSSQLClient

Returns a new instance of MSSQLClient.



36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
 
# File 'lib/gooddata/cloud_resources/mssql/mssql_client.rb', line 36

def initialize(options = {})
  raise("Data Source needs a client to MSSQL to be able to query the storage but 'mssql_client' is empty.") unless options['mssql_client']

  connection = options['mssql_client']['connection']
  if connection.is_a?(Hash)
    @database = connection['database']
    @schema = connection['schema']
    @authentication = connection['authentication']
    @ssl_mode = connection['sslMode']
    @url = connection['url']

    validate
  else
    raise('Missing connection info for MSSQL client')
  end

  Java.com.microsoft.sqlserver.jdbc.SQLServerDriver
end

Class Method Details

.accept?(type) ⇒ Boolean

Returns:

  • (Boolean) 


31
32
33
 
# File 'lib/gooddata/cloud_resources/mssql/mssql_client.rb', line 31

def accept?(type)
  type == 'mssql'
end

Instance Method Details

#build_connection_string ⇒ Object 



109
110
111
112
113
114
115
116
117
118
119
 
# File 'lib/gooddata/cloud_resources/mssql/mssql_client.rb', line 109

def build_connection_string
  encrypt = @ssl_mode != PREFER
  trust_server_certificate = @ssl_mode == REQUIRE

  "#{@url};" \
    "database=#{@database};" \
    "encrypt=#{encrypt};" \
    "trustServerCertificate=#{trust_server_certificate};" \
    "loginTimeout=#{LOGIN_TIME_OUT};" \
    "#{'authentication=ActiveDirectoryPassword;' if @authentication['activeDirectoryPassword']}"
end

#connect ⇒ Object 



83
84
85
86
87
88
89
90
91
92
93
94
 
# File 'lib/gooddata/cloud_resources/mssql/mssql_client.rb', line 83

def connect
  connection_string = build_connection_string
  GoodData.logger.info "Setting up connection to MSSQL #{connection_string}"

  authentication = @authentication['basic'] || @authentication['activeDirectoryPassword']

  prop = java.util.Properties.new
  prop.setProperty('userName', authentication['userName'])
  prop.setProperty('password', authentication['password'])

  @connection = java.sql.DriverManager.getConnection(connection_string, prop)
end

#realize_query(query, _params) ⇒ Object 



55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
# File 'lib/gooddata/cloud_resources/mssql/mssql_client.rb', line 55

def realize_query(query, _params)
  GoodData.gd_logger.info("Realize SQL query: type=mssql status=started")

  connect

  filename = "#{SecureRandom.urlsafe_base64(6)}_#{Time.now.to_i}.csv"
  measure = Benchmark.measure do
    statement = @connection.create_statement
    statement.set_fetch_size(MSSQL_FETCH_SIZE)
    has_result = statement.execute(query)
    if has_result
      result = statement.get_result_set
      metadata = result.get_meta_data
      col_count = metadata.column_count
      CSV.open(filename, 'wb') do |csv|
        csv << Array(1..col_count).map { |i| metadata.get_column_name(i) } # build the header
        csv << Array(1..col_count).map { |i| result.get_string(i)&.to_s } while result.next
      end
    end
  end

  GoodData.gd_logger.info("Realize SQL query: type=mssql status=finished duration=#{measure.real}")
  filename
ensure
  @connection&.close
  @connection = nil
end

#validate ⇒ Object 



96
97
98
99
100
101
102
103
104
105
106
107
 
# File 'lib/gooddata/cloud_resources/mssql/mssql_client.rb', line 96

def validate
  raise "SSL Mode should be prefer, require and verify-full" unless @ssl_mode == 'prefer' || @ssl_mode == 'require' || @ssl_mode == 'verify-full'

  raise "Instance name is not supported" if @url !~ /^[^\\]*$/

  raise "The connection url is invalid. Parameter is not supported." if @url.include? MSSQL_SEPARATOR_PARAM

  url_matches = @url.scan(MSSQL_URL_PATTERN)
  raise "Cannot reach the url" if url_matches.nil? || url_matches.length.zero?

  raise "The authentication method is not supported." unless @authentication['basic'] || @authentication['activeDirectoryPassword']
end