Module: Waxx::Pg

Extended by:
Pg
Included in:
Pg
Defined in:
lib/waxx/pg.rb

Overview

The PostgreSQL Object methods

Instance Attribute Summary collapse

Instance Method Summary collapse

Instance Attribute Details

#app ⇒ Object (readonly)

Returns the value of attribute app.



9
10
11
 
# File 'lib/waxx/pg.rb', line 9

def app
  @app
end

#columns ⇒ Object (readonly)

Returns the value of attribute columns.



12
13
14
 
# File 'lib/waxx/pg.rb', line 12

def columns
  @columns
end

#db ⇒ Object (readonly)

Returns the value of attribute db.



10
11
12
 
# File 'lib/waxx/pg.rb', line 10

def db
  @db
end

#joins ⇒ Object (readonly)

Returns the value of attribute joins.



14
15
16
 
# File 'lib/waxx/pg.rb', line 14

def joins
  @joins
end

#orders ⇒ Object (readonly)

Returns the value of attribute orders.



15
16
17
 
# File 'lib/waxx/pg.rb', line 15

def orders
  @orders
end

#pkey ⇒ Object (readonly)

Returns the value of attribute pkey.



13
14
15
 
# File 'lib/waxx/pg.rb', line 13

def pkey
  @pkey
end

#table ⇒ Object (readonly)

Returns the value of attribute table.



11
12
13
 
# File 'lib/waxx/pg.rb', line 11

def table
  @table
end

Instance Method Details

#/(n) ⇒ Object 



78
79
80
 
# File 'lib/waxx/pg.rb', line 78

def /(n)
  @columns[n.to_sym]
end

#[](n) ⇒ Object 



74
75
76
 
# File 'lib/waxx/pg.rb', line 74

def [](n)
  @columns[n.to_sym]
end

#build_joins(n, col_is) ⇒ Object 



57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
 
# File 'lib/waxx/pg.rb', line 57

def build_joins(n, col_is)
  return if col_is.nil?
  [col_is].flatten.each{|str| 
    rt, c = str.split(".")
    r, t = rt.split(":")
    t = r if t.nil?
    j = c =~ /\+$/ ? "LEFT" : "INNER"
    @joins[r] = {
      table: @table,
      col: n.to_s.strip,
      join: j.to_s.strip,
      foreign_table: t.to_s.strip,
      foreign_col: c.to_s.strip.sub('+','')
    }
  }
end

#cast(col, val) ⇒ Object 



262
263
264
265
266
267
268
269
270
271
272
273
274
275
 
# File 'lib/waxx/pg.rb', line 262

def cast(col, val)
  case col[:type].to_sym
  when :int
    val.to_s.empty? ? nil : val.to_i
  when :float, :numeric
    val.to_s.empty? ? nil : val.to_f
  when :bool, :boolean
    val.nil? ? nil : ['t', 'true', 'y', 'yes'].include?(val.to_s.downcase) ? true : false
  when :date, :datetime, :timestamp, :time
    val.to_s.empty? ? nil : val
  else
    val
  end
end

#columns_for(x, table_name, conn_name = :app) ⇒ Object 



287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
 
# File 'lib/waxx/pg.rb', line 287

def columns_for(x, table_name, conn_name=:app)
  # Get the primary key
  pkey = x.db[conn_name].exec("
    select column_name
    from information_schema.key_column_usage
    where table_schema = 'public'
    and table_name = $1
    and constraint_name like '%_pkey'",
    [
      table_name
    ]
  ).first['column_name'] rescue nil
  columns = x.db[conn_name].exec("
    select  column_name, data_type
    from    information_schema.columns 
    where   table_schema = 'public'
    and     table_name = $1
    order   by ordinal_position
    ",[
      table_name
    ]
  )
  columns.map{|c|
    c['pkey'] = c['column_name'] == pkey
    c
  }
end

#connect(str) ⇒ Object

Connect to a postgresql database

Set in config.yaml:

databases:
  app: postgresql://user:pass@host:port/database



23
24
25
26
27
28
 
# File 'lib/waxx/pg.rb', line 23

def connect(str)
  conn = PG.connect( str )
  conn.type_map_for_results = PG::BasicTypeMapForResults.new conn
  conn.type_map_for_queries = PG::BasicTypeMapForQueries.new conn
  conn
end

#debug(str, level = 3) ⇒ Object 



316
317
318
 
# File 'lib/waxx/pg.rb', line 316

def debug(str, level=3)
  Waxx.debug(str, level)
end

#delete(x, id, where: nil) ⇒ Object 



277
278
279
 
# File 'lib/waxx/pg.rb', line 277

def delete(x, id, where: nil)
  x.db[@db].exec("DELETE FROM #{@table} WHERE #{@pkey} = $1 #{where}", [id])
end

#get(x, select: nil, id: nil, joins: nil, where: nil, having: nil, order: nil, limit: nil, offset: nil, view: nil) {|q| ... } ⇒ Object

Yields:

  • (q) 


113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
 
# File 'lib/waxx/pg.rb', line 113

def get(x, select:nil, id:nil, joins:nil,  where:nil, having:nil, order:nil, limit:nil, offset:nil, view:nil, &blk)
  Waxx.debug "object.get"
  select = parse_select(select, view)
  where = ["#{@table}.#{@pkey} = $1",id] if id and where.nil?
  # Block SQL injection in order clause. All order options must be defined in @orders.
  if order
    # Look in self orders
    if not @orders/order
      # Look in the view's columns
      if view and view.orders/order
        order = view.orders/order
      else
        Waxx.debug("ERROR: Object.get order (#{order}) not found in @orders [#{@orders.keys.join(", ")}]. Sorting by #{@pkey} instead.")
        order = @pkey
      end
    else
      order = @orders/order 
    end
  end
  if joins.nil? and view
    joins = view.joins_to_sql
  end
  q = {select:select, joins:joins, where:where, having:having, order:order, limit:limit, offset:offset}
  yield q if block_given?
  Waxx.debug "object.get.select: #{q[:select]}"
  return [] if q[:select].empty?
  sql = []
  sql << "SELECT #{q[:select] || "*"}"
  sql << "FROM #{@table} #{q[:joins]}"
  sql << "WHERE #{q[:where][0]}" if q[:where] 
  sql << "HAVING #{q[:having[0]]}" if q[:having] 
  sql << "ORDER BY #{q[:order]}" if q[:order]
  sql << "LIMIT #{q[:limit].to_i}" if q[:limit]
  sql << "OFFSET #{q[:offset].to_i}" if q[:offset]
  vals = []
  vals << q[:where][1] if q[:where] and q[:where][1]
  vals << q[:having][1] if q[:having] and q[:having][1]
  #[sql.join(" "), vals.flatten]
  Waxx.debug sql
  Waxx.debug vals.join(", ")
  begin
    x.db[@db].exec(sql.join(" "), vals.flatten)
  rescue => e
    if e =~ /connection/
      x.db[@db].reset
      x.db[@db].exec(sql.join(" "), vals.flatten)
    else
      raise e
    end
  end
end

#get_by_id(x, id, select = nil, view: nil) ⇒ Object Also known as: by_id 



165
166
167
 
# File 'lib/waxx/pg.rb', line 165

def get_by_id(x, id, select=nil, view:nil)
  get(x, id: id, select: select, view: view).first
end

#get_by_ulid(x, ulid, select = nil, view: nil) ⇒ Object Also known as: by_ulid 



170
171
172
 
# File 'lib/waxx/pg.rb', line 170

def get_by_ulid(x, ulid, select=nil, view:nil)
  get(x, select: select, view: view, where: ["ulid = $1", [ulid]]).first
end

#get_cols(*args) ⇒ Object 



82
83
84
85
86
 
# File 'lib/waxx/pg.rb', line 82

def get_cols(*args)
  re = {}
  args.flatten.map{|a| re[a] = @columns[a.to_sym]}
  re
end

#has(opts = nil) ⇒ Object 



41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
 
# File 'lib/waxx/pg.rb', line 41

def has(opts=nil)
  init if @table.nil?
  return @columns if opts.nil?
  @columns = opts
  @columns.each{|n,v|
    v[:table] = @table
    v[:column] = n
    v[:views] = []
    v[:label] ||= Waxx::Util.label(n)
    @orders[n] = v[:order] || n
    @orders["_#{n}".to_sym] = v[:_order] || "#{n} DESC"
    @pkey = n if v[:pkey]
    build_joins(n, v[:is])
  }
end

#init(app: nil, db: "app", table: nil, pk: "id", cols: nil) ⇒ Object 



30
31
32
33
34
35
36
37
38
39
 
# File 'lib/waxx/pg.rb', line 30

def init(app:nil, db:"app", table:nil, pk:"id", cols:nil)
  @app ||= (app || App.table_from_class(name)).to_sym
  @db ||= db.to_sym
  @table ||= (table || App.table_from_class(name)).to_sym
  @pkey ||= pk.to_sym
  @columns ||= {}
  @joins ||= {}
  @orders ||= {}
  has(cols) if cols
end

#order(req_order, default_order = '') ⇒ Object 



281
282
283
284
285
 
# File 'lib/waxx/pg.rb', line 281

def order(req_order, default_order='')
  return default_order if req_order.nil?
  return orders[req_order.to_sym] if orders.has_key? req_order.to_sym
  @pkey
end

#parse_select(select, view) ⇒ Object 



98
99
100
101
102
103
104
105
106
107
108
109
110
111
 
# File 'lib/waxx/pg.rb', line 98

def parse_select(select, view)
  raise "Can not define both select and view in Waxx::Object.parse_select (#{name})." if select and view
  return select || "*" if view.nil?
  view.columns.map{|n,c|             
    raise "Column #{n} not defined in #{view}" if c.nil?
    if c[:sql_select]
       "#{c[:sql_select]} AS #{n}"
    elsif n != c[:column]
      "#{c[:table]}.#{c[:column]} AS #{n}"
    else 
      "#{c[:table]}.#{c[:column]}"
    end
  }.join(", ")
end

#post(x, data, cols: nil, returning: nil, view: nil, &blk) ⇒ Object 



175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
 
# File 'lib/waxx/pg.rb', line 175

def post(x, data, cols:nil, returning:nil, view:nil, &blk)
  if view
    cols = view.columns.select{|n,c| c[:table] == @table}
  else
    cols ||= @columns
  end
  data = blk.call if block_given?
  sql = "INSERT INTO #{@table} ("
  names = []
  vars = []
  vals = []
  ret = []
  i = 1
  cols.each{|n,v|
    if data.has_key? n or data.has_key? n.to_s
      names << n.to_s
      # Make empty array array literals
      if v[:type].to_s == 'array' and ((data/n).nil? or (data/n).empty?)
        vars << "'{}'"
      else
        if data/n === :default
          vars << "DEFAULT"
        elsif data/n === :now
          vars << "NOW()"
        else
          vars << "$#{i}"
          vals << cast(v, data/n)
          i += 1
        end
      end
    end
    ret << n.to_s
  }
  sql << names.join(",")
  sql << ") VALUES (#{vars.join(",")})"
  sql << " RETURNING #{returning || ret.join(",")}"
  Waxx.debug(sql)
  Waxx.debug(vals)
  x.db[@db].exec(sql, vals).first
end

#put(x, id, data, cols: nil, returning: nil, view: nil, where: nil, &blk) ⇒ Object Also known as: patch 



216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
 
# File 'lib/waxx/pg.rb', line 216

def put(x, id, data, cols:nil, returning:nil, view:nil, where:nil, &blk)
  if view
    cols = view.columns.select{|n,c| c[:table] == @table}
  else
    cols ||= @columns
  end
  data = blk.call if block_given?
  sql = "UPDATE #{@table} SET "
  set = []
  vals = []
  ret = [@pkey]
  i = 1
  cols.each{|n,v|
    if data.has_key? n.to_s or data.has_key? n.to_sym
      if data/n === :default
        set << "#{n} = DEFAULT"
      elsif data/n === :now
        set << "#{n} = NOW()"
      else
        if !(data/n).nil? and (v/:type).to_s =~ /\[\]$/ and (data/n).empty?
          set << "#{n} = ARRAY[]::#{v/:type}"
        else
          set << "#{n} = $#{i}"
          vals << cast(v, data/n)
          i += 1
        end
      end
      ret << n.to_s
    end
  }
  sql << set.join(",")
  sql << " WHERE #{@pkey} = $#{i} #{where} RETURNING #{returning || ret.join(",")}"
  vals << id
  Waxx.debug(sql)
  Waxx.debug(vals)
  x.db[@db].exec(sql, vals).first
end

#put_post(x, id, data, cols: nil, returning: nil, view: nil) ⇒ Object 



255
256
257
258
259
260
 
# File 'lib/waxx/pg.rb', line 255

def put_post(x, id, data, cols:nil, returning:nil, view: nil)
  q = nil
  q = get_by_id(x, id, @pkey) if id.to_i > 0
  return post(x, data, cols: cols, returning: returning, view: view) if q.nil?
  put(x, id, data, cols: cols, returning: returning, view: view)
end

#run(x, act, meth, *args) ⇒ Object 



94
95
96
 
# File 'lib/waxx/pg.rb', line 94

def run(x, act, meth, *args)
  App[@app][act.to_sym][meth.to_sym][x, *args]
end

#runs(opts = nil) ⇒ Object 



88
89
90
91
92
 
# File 'lib/waxx/pg.rb', line 88

def runs(opts=nil)
  init if @app.nil?
  return App[@app] if opts.nil?
  App[@app] = opts
end