Class: CaRuby::SQLExecutor

Inherits:
Object
  • Object
show all
Defined in:
lib/caruby/database/sql_executor.rb

Overview

SQLExecutor executes an SQL statement against the database. Use of this class requires the dbi gem. SQLExecutor is an auxiliary utility class and is not used by the rest of the CaRuby API.

Constant Summary collapse

ACCESS_OPTS =

The database connection access options.

[
  [:database_host, '--database_host HOST', 'the database host name'],
  [:database_port, '--database_port PORT', Integer, 'the database port number'],
  [:database, '--database NAME', 'the database name'],
  [:database_url, '--database_url URL', 'the database connection URL'],
  [:database_type, '--database_type TYPE', 'the database type (mysql or oracle)'],
  [:database_driver, '--database_driver DRIVER', 'the database driver string'],
  [:database_driver_class, '--database_driver_class CLASS', 'the JDBC database driver class name'],
  [:database_user, '--database_user USER', 'the database login user'],
  [:database_password, '--database_password PSWD', 'the database login password']
]

Instance Method Summary collapse

Constructor Details

#initialize(opts) ⇒ SQLExecutor

Creates a new SQLExecutor with the given options.

The default database host is the application :host property value, which in turn defaults to localhost.

The default database type is mysql. The optional :database_port property overrides the default port for the database type.

The default database driver is jdbc:mysql for MySQL, Oracle for Oracle. The default database driver class is com.mysql.jdbc.Driver for MySQL, oracle.jdbc.OracleDriver for Oracle.

The default database url is db_driver://db_host:db_port/+db_name.

Parameters:

  • opts (Hash) —

    the connect options

Options Hash (opts):

  • :database (String) —

    the database name

  • :database_user (String) —

    the database username (not the application login name)

  • :database_password (String) —

    the database password (not the application login password)

  • :database_type (String) —

    the database type (default mysql)

  • :database_host (String) —

    the database host

  • :database_port (Integer) —

    the database port number

  • :database_driver (String) —

    the JDBC driver string, e.g. jdbc:mysql

  • :database_url (String) —

    the database connection URL

  • :database_driver_class (String) —

    the connect driver class name

Raises:



47
48
49
50
51
52
53
54
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/caruby/database/sql_executor.rb', line 47

def initialize(opts)
  if opts.empty? then
    raise CaRuby::ConfigurationError.new("The caRuby database connection properties were not found.") 
  end
  app_host = Options.get(:host, opts, 'localhost')
  db_host = Options.get(:database_host, opts, app_host)
  db_type = Options.get(:database_type, opts, 'mysql')
  db_driver = Options.get(:database_driver, opts) { default_driver_string(db_type) }
  db_port = Options.get(:database_port, opts) { default_port(db_type) }
  db_name = Options.get(:database, opts)
  @db_url = Options.get(:database_url, opts) do
    # If there is a db name, then make the default db url.
    # Otherwise, raise an error.
    if db_name then
     "#{db_driver}://#{db_host}:#{db_port}/#{db_name}"
    else
      raise_missing_option_error(:database)
    end
  end 
  @username = Options.get(:database_user, opts) { raise_missing_option_error(:database_user) }
  @password = Options.get(:database_password, opts) { raise_missing_option_error(:database_password) }
  @driver_class = Options.get(:database_driver_class, opts, default_driver_class(db_type))
  # The effective connection options.
  eff_opts = {
    :database => db_name,
    :database_host => db_host,
    :database_user => @username,
    :database_type => db_type,
    :database_port => db_port,
    :database_driver => db_driver,
    :database_driver_class => @driver_class,
    :database_url => @db_url
  }
  logger.debug { "Database connection parameters (excluding password): #{eff_opts.qp}" }
end

Instance Method Details

#execute {|dbh| ... } ⇒ Object

Connects to the database, yields the DBI handle to the given block and disconnects.

Yields:

  • (dbh) —

    the transaction statements

Yield Parameters:

  • dbh (RDBI::Database) —

    the database handle



87
88
89
90
91
 
# File 'lib/caruby/database/sql_executor.rb', line 87

def execute
  RDBI.connect(:JDBC, :database => @db_url, :user => @username, :password => @password, :driver_class => @driver_class) do |dbh|
    yield dbh
  end
end

#query(sql, *args) {|row| ... } ⇒ Array

Runs the given query.

Parameters:

  • sql (String) —

    the SQL to execute

  • args (Array) —

    the SQL bindings

Yields:

  • (row) —

    operate on the result

  • (Array) —

    the result row

Returns:

  • (Array) —

    the query result



100
101
102
103
104
105
106
107
108
109
110
111
112
 
# File 'lib/caruby/database/sql_executor.rb', line 100

def query(sql, *args, &block)
  fetched = nil
  execute do |dbh|
    result = dbh.execute(sql, *args)
    if block_given? then
      result.each(&block)
    else
      fetched = result.fetch(:all)
    end
    result.finish
  end
  fetched
end

#transact(sql = nil, *args) {|dbh| ... } ⇒ Object

Runs the given SQL or block in a transaction. If SQL is provided, then that SQL is executed. Otherwise, the block is called.

Parameters:

  • sql (String) (defaults to: nil) —

    the SQL to execute

  • args (Array) —

    the SQL bindings

Yields:

  • (dbh) —

    the transaction statements

Yield Parameters:

  • dbh (RDBI::Database) —

    the database handle



124
125
126
127
128
129
130
131
132
133
134
 
# File 'lib/caruby/database/sql_executor.rb', line 124

def transact(sql=nil, *args)
  # Work-around for rcbi nil substitution.
  if sql then
    sql, *args = replace_nil_binds(sql, args)
    transact { |dbh| dbh.execute(sql, *args) }
  elsif block_given? then
    execute { |dbh| dbh.transaction { yield dbh } }
  else
    raise ArgumentError.new("SQL executor is missing the required execution block")
  end
end