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.3.7"

Instance Method Summary collapse

Instance Method Details

#all(sql, *args, into: nil, &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



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

def all(sql, *args, into: nil, &block)
  result = exec_logged(sql, *args)
  enumerate(result, into: into, &block)
end

#ask(sql, *args, into: nil) ⇒ 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, into: nil)
  catch(:ok) do
    all(sql, *args, into: into) { |row| throw :ok, row }
    nil
  end
end

#configuration(database_url = :auto) ⇒ Object

returns a configuration hash, either from the passed in database URL, from a DATABASE_URL environment value, or from the config/database.yml file.



152
153
154
155
 
# File 'lib/simple/sql.rb', line 152

def configuration(database_url = :auto)
  database_url = Config.determine_url if database_url == :auto
  Config.parse_url(database_url)
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.



161
162
163
164
165
166
167
168
169
170
 
# File 'lib/simple/sql.rb', line 161

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.pg_connection(PG::Connection.new(config))
  self.connector = lambda { connection }
end

#default_logger ⇒ Object 



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

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



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

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, on_conflict: nil) ⇒ Object

  • table_name - the name of the table

  • records - a single hash of attributes or an array of hashes of attributes

  • on_conflict - uses a postgres ON CONFLICT clause to ignore insert conflicts if true



11
12
13
14
15
16
17
 
# File 'lib/simple/sql/insert.rb', line 11

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

#logger ⇒ Object 



21
22
23
 
# File 'lib/simple/sql.rb', line 21

def logger
  @logger ||= default_logger
end

#logger=(logger) ⇒ Object 



25
26
27
 
# File 'lib/simple/sql.rb', line 25

def logger=(logger)
  @logger = logger
end

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

Deprecated

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

as a Hash.



89
90
91
 
# File 'lib/simple/sql.rb', line 89

def record(sql, *args, into: Hash)
  ask sql, *args, into: (into || Hash)
end

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

Deprecated

Runs a query, with optional arguments, and returns the

result as an array of Hashes.



83
84
85
 
# File 'lib/simple/sql.rb', line 83

def records(sql, *args, into: Hash, &block)
  all sql, *args, into: (into || Hash), &block
end