Module: PGI::Dataset

Defined in:
lib/pgi/dataset.rb,
lib/pgi/dataset/query.rb,
lib/pgi/dataset/utils.rb

Defined Under Namespace

Modules: Utils Classes: Query

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.[](database, table, **options) ⇒ Object 



160
161
162
163
164
165
166
167
168
 
# File 'lib/pgi/dataset.rb', line 160

def [](database, table, **options)
  raise "Invalid table name: #{table}" unless table.to_s =~ /\A[a-z_][a-z0-9_]*\z/

  mod = clone
  mod.instance_variable_set("@database", database)
  mod.instance_variable_set("@table", table)
  mod.instance_variable_set("@options", options)
  mod
end

.extended(klass) ⇒ Object 



170
171
172
173
174
175
176
 
# File 'lib/pgi/dataset.rb', line 170

def extended(klass)
  raise "Database table not specified" unless @table

  klass.instance_variable_set("@database", @database)
  klass.instance_variable_set("@table", @table)
  klass.instance_variable_set("@options", @options)
end

Instance Method Details

#all ⇒ Array

Get all rows

Returns:

  • (Array) —

    list of Models, Hashes



90
91
92
 
# File 'lib/pgi/dataset.rb', line 90

def all
  _to_models Query.new(@database, @table, nil, **@options).limit(nil).to_a
end

#count ⇒ Integer

Get number of rows

Returns:

  • (Integer) —

    number of rows in the table



111
112
113
 
# File 'lib/pgi/dataset.rb', line 111

def count
  Query.new(@database, @table, nil, **@options).count
end

#delete(id) ⇒ Model, Hash

Delete row

Parameters:

  • id (*) —

    ID of row

Returns:

  • (Model, Hash) 


74
75
76
77
 
# File 'lib/pgi/dataset.rb', line 74

def delete(id)
  command = "DELETE FROM #{@table}"
  _to_model Query.new(@database, @table, command, **@options).where(id: id).limit(nil).to_a.first
end

#find(id) ⇒ Model, Hash

Get a row by its id

Parameters:

  • id (*) —

    ID of row

Returns:

  • (Model, Hash) 


83
84
85
 
# File 'lib/pgi/dataset.rb', line 83

def find(id)
  _to_model Query.new(@database, @table, nil, **@options).where(id: id).first
end

#first(sort_by = :id) ⇒ Model, Hash

Get first row by column (default: :id)

Returns:

  • (Model, Hash) 


97
98
99
 
# File 'lib/pgi/dataset.rb', line 97

def first(sort_by = :id)
  _to_model where.order(sort_by.to_sym, :asc).first
end

#insert(**attributes) ⇒ Model, Hash

Insert new row

Parameters:

  • args (Hash|Object) —

    row data

Returns:

  • (Model, Hash) 


28
29
30
31
32
 
# File 'lib/pgi/dataset.rb', line 28

def insert(**attributes)
  attributes = Utils.strip_uninsertable(attributes)

  insert!(**attributes)
end

#insert!(**attributes) ⇒ Object 



34
35
36
37
38
39
40
41
42
43
44
45
 
# File 'lib/pgi/dataset.rb', line 34

def insert!(**attributes)
  columns, placeholders, values = sql_params(attributes)
  command = "INSERT INTO #{@table}"
  command <<
    if columns.empty?
      " DEFAULT VALUES"
    else
      " (#{columns.join(", ")}) VALUES (#{placeholders.join(", ")}) "
    end

  _to_model Query.new(@database, @table, command, params: values).limit(nil).to_a.first
end

#last(sort_by = :id) ⇒ Model, Hash

Get last row by column (default: :id)

Returns:

  • (Model, Hash) 


104
105
106
 
# File 'lib/pgi/dataset.rb', line 104

def last(sort_by = :id)
  _to_model where.order(sort_by.to_sym, :desc).first
end

#page(cursor = nil, size = 10, sort_by = :id, sort_dir = :asc, *where) ⇒ Array

Get a page of results using keyset pagination.

Sorting by a column other than :id requires a composite index on (sort_by, id) for seek performance — see README.

Parameters:

  • cursor (*, nil) (defaults to: nil) —

    id of the last row from the previous page, or nil for the first page

  • size (Integer) (defaults to: 10) —

    number of rows per page

  • sort_by (Symbol) (defaults to: :id) —

    column to sort by

  • sort_dir (Symbol) (defaults to: :asc) —

    :asc or :desc

  • where (Array) —

    optional WHERE clause forwarded to Query#where

Returns:

  • (Array) —

    list of Models or Hashes



126
127
128
 
# File 'lib/pgi/dataset.rb', line 126

def page(cursor = nil, size = 10, sort_by = :id, sort_dir = :asc, *where)
  _to_models Query.new(@database, @table, nil, **@options).where(*where).limit(size).keyset(sort_by, cursor, sort_dir).to_a
end

#select(*args) ⇒ Query

Select specific columns

Parameters:

  • args (String|Array) —

    list of columns to include in result set

Returns:



10
11
12
13
14
 
# File 'lib/pgi/dataset.rb', line 10

def select(*args)
  columns = args.empty? ? "*" : Utils.sanitize_columns(args, @table).join(", ")
  command = "SELECT #{columns} FROM #{@table}"
  Query.new(@database, @table, command, **@options)
end

#update(id, **args) ⇒ Model, Hash

Update row

Parameters:

  • id (*) —

    ID of row

  • args (Hash) —

    data for update

Returns:

  • (Model, Hash) 


52
53
54
55
56
 
# File 'lib/pgi/dataset.rb', line 52

def update(id, **args)
  args = Utils.strip_unupdateable(args)

  update!(id, **args)
end

#update!(id, **args) ⇒ Object 



58
59
60
61
62
63
64
65
66
67
68
 
# File 'lib/pgi/dataset.rb', line 58

def update!(id, **args)
  columns, placeholders, values = sql_params(args)
  set_clause = columns.zip(placeholders).map { |c, p| "#{c} = #{p}" }.join(", ")
  command = "UPDATE #{@table} SET #{set_clause} " \
            "WHERE #{Utils.sanitize_column(:id)} = $#{values.size + 1} RETURNING *"

  # TODO: Query throws `PG::IndeterminateDatatype: ERROR:  could not determine data type of parameter $2`
  # _to_model Query.new(@database, @table, command, params: values + [id]).where(id: id).limit(nil)

  _to_model @database.exec_stmt(Utils.stmt_name(@table, command), command, values + [id])&.first
end

#where ⇒ Query

Select specfic rows

Parameters:

  • args (String|Array) —

    conditions to search for

Returns:



20
21
22
 
# File 'lib/pgi/dataset.rb', line 20

def where(*)
  Query.new(@database, @table, nil, **@options).where(*)
end