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: Inserter

Constant Summary collapse

VERSION = 
"0.3.0"

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



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

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)



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

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.



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

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
35
 
# File 'lib/simple/sql.rb', line 27

def default_logger
  if defined?(ActiveRecord)
    ActiveRecord::Base.logger
  else
    logger = Logger.new(STDERR)
    logger.level = Logger::INFO
    logger
  end
end

#duplicate(table, ids, except: []) ⇒ Object 



6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
# File 'lib/simple/sql/duplicate.rb', line 6

def duplicate(table, ids, except: [])
  ids = Array(ids)
  return [] if ids.empty?

  timestamp_columns = Reflection.timestamp_columns(table)
  primary_key_columns = Reflection.primary_key_columns(table)

  # duplicate all columns in the table that need to be SELECTed.
  #
  columns_to_dupe = Reflection.columns(table)

  # Primary keys will not be selected from the table, they should be set
  # automatically by the database, via a DEFAULT role on the column.
  columns_to_dupe -= primary_key_columns

  # timestamp_columns will not be selected from the table, they will be
  # set to now() explicitely.
  columns_to_dupe -= timestamp_columns

  # If some other columns must be excluded they have to be added in the
  # except: keyword argument. This is helpful for UNIQUE columns, but
  # a column which is supposed to be UNIQUE and NOT NULL can not be dealt
  # with.
  columns_to_dupe -= except.map(&:to_s)

  # build query
  select_columns = columns_to_dupe + timestamp_columns
  select_values  = columns_to_dupe + timestamp_columns.map { |col| "now() AS #{col}" }

  Simple::SQL.all "    INSERT INTO \#{table}(\#{select_columns.join(', ')})\n    SELECT \#{select_values.join(', ')} FROM \#{table} WHERE id = ANY($1) RETURNING id\n  SQL\nend\n", ids

#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 “;”



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

def exec(sql)
  Logging.yield_logged sql do
    connection.exec sql
  end
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.



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

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



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

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

  enumerate(result, decoder, block)
end