Module: Simple::SQL

Extended by:
Forwardable, SQL
Included in:
SQL
Defined in:
lib/simple/sql.rb,
lib/simple/sql/insert.rb,
lib/simple/sql/logging.rb,
lib/simple/sql/version.rb,
lib/simple/sql/duplicate.rb,
lib/simple/sql/reflection.rb

Overview

The Simple::SQL module

Defined Under Namespace

Modules: Config, Connection, Decoder, Encoder, Logging, Reflection Classes: Duplicator, Fragment, Inserter

Constant Summary collapse

VERSION = 
"0.2.10"

Instance Method Summary collapse

Instance Method Details

#all(sql, *args, &block) ⇒ Object

Runs a query, with optional arguments, and returns the result. If the SQL query returns rows with one column, this method returns an array of these values. Otherwise it returns an array of arrays.

Example:

  • Simple::SQL.all("SELECT id FROM users") returns an array of id values

  • Simple::SQL.all("SELECT id, email FROM users") returns an array of

    arrays `[ <id>, <email> ]`.

Simple::SQL.all “SELECT id, email FROM users” do |id, email|

# do something

end



59
60
61
62
63
64
 
# File 'lib/simple/sql.rb', line 59

def all(sql, *args, &block)
  result  = exec_logged(sql, *args)
  decoder = Decoder.new(result)

  enumerate(result, decoder, block)
end

#ask(sql, *args) ⇒ Object

Runs a query and returns the first result row of a query.

Examples:

  • Simple::SQL.ask "SELECT id FROM users WHERE email=$?", "foo@local" returns a number (or nil)

  • Simple::SQL.ask "SELECT id, email FROM users WHERE email=$?", "foo@local" returns an array [ <id>, <email> ] (or nil)



74
75
76
77
78
79
 
# File 'lib/simple/sql.rb', line 74

def ask(sql, *args)
  catch(:ok) do
    all(sql, *args) { |row| throw :ok, row }
    nil
  end
end

#connect!(database_url = :auto) ⇒ Object

connects to the database specified via the url parameter. If called without argument it tries to determine a DATABASE_URL from either the environment setting (DATABASE_URL) or from a config/database.yml file, taking into account the RAILS_ENV and RACK_ENV settings.



166
167
168
169
170
171
172
173
174
175
 
# File 'lib/simple/sql.rb', line 166

def connect!(database_url = :auto)
  database_url = Config.determine_url if database_url == :auto

  logger.info "Connecting to #{database_url}"
  config = Config.parse_url(database_url)

  require "pg"
  connection = Connection.new(PG::Connection.new(config))
  self.connector = lambda { connection }
end

#default_logger ⇒ Object 



27
28
29
30
31
32
33
34
 
# File 'lib/simple/sql.rb', line 27

def default_logger
  logger = ActiveRecord::Base.logger if defined?(ActiveRecord)
  return logger if logger

  logger = Logger.new(STDERR)
  logger.level = Logger::INFO
  logger
end

#duplicate(table, ids, overrides = {}) ⇒ Object

Creates duplicates of record in a table.

This method handles timestamp columns (these will be set to the current time) and primary keys (will be set to NULL.) You can pass in overrides as a third argument for specific columns.

Parameters:

  • ids: (Integer, Array<Integer>) primary key ids

  • overrides: Hash[column_names => SQL::Fragment]



24
25
26
27
28
29
 
# File 'lib/simple/sql/duplicate.rb', line 24

def duplicate(table, ids, overrides = {})
  ids = Array(ids)
  return [] if ids.empty?

  Duplicator.new(table, overrides).call(ids)
end

#exec(sql) ⇒ Object

execute one or more sql statements. This method does not allow to pass in arguments - since the pg client does not support this - but it allows to run multiple sql statements separated by “;”



39
40
41
42
43
 
# File 'lib/simple/sql.rb', line 39

def exec(sql)
  Logging.yield_logged sql do
    connection.exec sql
  end
end

#fragment(str) ⇒ Object 



8
9
10
 
# File 'lib/simple/sql/duplicate.rb', line 8

def fragment(str)
  Fragment.new(str)
end

#insert(table, records) ⇒ Object 



6
7
8
9
10
11
12
 
# File 'lib/simple/sql/insert.rb', line 6

def insert(table, records)
  if records.is_a?(Hash)
    insert_many(table, [records]).first
  else
    insert_many table, records
  end
end

#logger ⇒ Object 



19
20
21
 
# File 'lib/simple/sql.rb', line 19

def logger
  @logger ||= default_logger
end

#logger=(logger) ⇒ Object 



23
24
25
 
# File 'lib/simple/sql.rb', line 23

def logger=(logger)
  @logger = logger
end

#record(sql, *args, into: Hash) ⇒ Object

Runs a query and returns the first result row of a query as a Hash.



101
102
103
104
105
106
 
# File 'lib/simple/sql.rb', line 101

def record(sql, *args, into: Hash)
  catch(:ok) do
    records(sql, *args, into: into) { |row| throw :ok, row }
    nil
  end
end

#records(sql, *args, into: Hash, &block) ⇒ Object

Runs a query, with optional arguments, and returns the result as an array of Hashes.

Example:

  • Simple::SQL.records("SELECT id, email FROM users") returns an array of

    hashes { id: id, email: email }

Simple::SQL.records “SELECT id, email FROM users” do |record|

# do something

end



93
94
95
96
97
98
 
# File 'lib/simple/sql.rb', line 93

def records(sql, *args, into: Hash, &block)
  result  = exec_logged(sql, *args)
  decoder = Decoder.new(result, :record, into: into)

  enumerate(result, decoder, block)
end