Module: Waxx::Mysql2

Extended by:
Mysql2
Included in:
Mysql2
Defined in:
waxx/mysql2.rb

Overview

The MySQL Object methods

Instance Attribute Summary collapse

Instance Method Summary collapse

Instance Attribute Details

#appObject (readonly)

Returns the value of attribute app


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

def app
  @app
end

#columnsObject (readonly)

Returns the value of attribute columns


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

def columns
  @columns
end

#dbObject (readonly)

Returns the value of attribute db


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

def db
  @db
end

#joinsObject (readonly)

Returns the value of attribute joins


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

def joins
  @joins
end

#ordersObject (readonly)

Returns the value of attribute orders


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

def orders
  @orders
end

#pkeyObject (readonly)

Returns the value of attribute pkey


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

def pkey
  @pkey
end

#tableObject (readonly)

Returns the value of attribute table


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

def table
  @table
end

Instance Method Details

#/(n) ⇒ Object

[View source]

67
68
69
# File 'waxx/mysql2.rb', line 67

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

#[](n) ⇒ Object

[View source]

63
64
65
# File 'waxx/mysql2.rb', line 63

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

#cast(col, val) ⇒ Object

[View source]

214
215
216
217
218
219
220
221
222
223
# File 'waxx/mysql2.rb', line 214

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
  else
    val
  end
end

#connect(conf = {}) ⇒ Object

Connect to a MySQL/MariaDB database

Set in config.yaml:

databases:
  app: mysql2://user:pass@host:port/database?opt1=val1;opt2=val2
[View source]

23
24
25
# File 'waxx/mysql2.rb', line 23

def connect(conf={})
  Mysql2::Client.new( conf )
end

#delete(x, id) ⇒ Object

[View source]

225
226
227
# File 'waxx/mysql2.rb', line 225

def delete(x, id)
  x.db[@db].query("DELETE FROM #{@table} WHERE #{@pkey} = $1", [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)
[View source]

102
103
104
105
106
107
108
109
110
111
112
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
# File 'waxx/mysql2.rb', line 102

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} = ?",id] if id and where.nil?
  # Block SQL injection in order clause. All order options must be defined in @orders.
  if order
    if not @orders/order
      Waxx.debug("ERROR: Object.get order (#{order}) not found in @orders [#{@orders.keys.join(", ")}]. Sorting by #{@pkey} instead.")
      order = @pkey
    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(", ")
  x.db[@db].prepare(sql.join(" ")).execute(*(vals.flatten))
end

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

[View source]

139
140
141
# File 'waxx/mysql2.rb', line 139

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

#get_cols(*args) ⇒ Object

[View source]

71
72
73
74
75
# File 'waxx/mysql2.rb', line 71

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

#has(opts = nil) ⇒ Object

[View source]

38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
# File 'waxx/mysql2.rb', line 38

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]
    if v[:is]
      r, tc = v[:is].split(":")
      t, c = tc.split(".")
      @joins[r] = {join: "INNER", table: t, col: c}
    end
    if v[:has]
      r, tc = v[:has].split(":")
      t, c = tc.split(".")
      @joins[r] = {join: "LEFT", table: t, col: c}
    end
  }
end

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

[View source]

27
28
29
30
31
32
33
34
35
36
# File 'waxx/mysql2.rb', line 27

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

[View source]

229
230
231
232
233
# File 'waxx/mysql2.rb', line 229

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

[View source]

87
88
89
90
91
92
93
94
95
96
97
98
99
100
# File 'waxx/mysql2.rb', line 87

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

[View source]

144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
# File 'waxx/mysql2.rb', line 144

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/n
      names << n.to_s
      vars << "?"
      vals << cast(v, data/n)
      i += 1
    end
    ret << n.to_s
  }
  sql << names.join(",")
  sql << ") VALUES (#{vars.join(",")})"
  Waxx.debug(sql)
  Waxx.debug(vals)
  x.db[@db].prepare(sql).execute(*vals) 
  id = x.db[@db].last_id
  x.db[@db].prepare("SELECT #{returning || ret.join(",")} FROM #{@table} WHERE #{@pkey} = ?").execute(id).first 
end

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

[View source]

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
# File 'waxx/mysql2.rb', line 175

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 = []
  i = 1
  Waxx.debug "data: #{data}"
  cols.each{|n,v|
    Waxx.debug "col: #{n}: #{v.inspect}"
    if data.has_key? n.to_s or data.has_key? n.to_sym
      set << "#{n} = ?"
      vals << cast(v, data/n)
      ret << n.to_s
      i += 1
    end
  }
  sql << set.join(",")
  sql << " WHERE #{@pkey} = ? #{where}"
  vals << id
  Waxx.debug(sql)
  Waxx.debug(vals)
  x.db[@db].prepare(sql).execute(*vals)
  x.db[@db].prepare("SELECT #{returning || ret.join(",")} FROM #{@table} WHERE #{@pkey} = ?").execute(id).first 
end

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

[View source]

207
208
209
210
211
212
# File 'waxx/mysql2.rb', line 207

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

[View source]

83
84
85
# File 'waxx/mysql2.rb', line 83

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

#runs(opts = nil) ⇒ Object

[View source]

77
78
79
80
81
# File 'waxx/mysql2.rb', line 77

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