Module: Og::SqlUtils

Included in:
MysqlUtils, SqlStore, SqlStore, SqlserverUtils
Defined in:
lib/og/store/sql.rb

Overview

A collection of useful SQL utilities.

Instance Method Summary collapse

Instance Method Details

#blob(val) ⇒ Object

– TODO: implement me! ++



46
47
48
 
# File 'lib/og/store/sql.rb', line 46

def blob(val)
  val
end

#build_join_name(class1, class2, postfix = nil) ⇒ Object 



164
165
166
167
168
 
# File 'lib/og/store/sql.rb', line 164

def build_join_name(class1, class2, postfix = nil)
  # Don't reorder arguments, as this is used in places that
  # have already determined the order they want.
  "#{Og.table_prefix}j_#{tableize(class1)}_#{tableize(class2)}#{postfix}"
end

#create_join_table_sql(join_table_info, suffix = 'NOT NULL', key_type = 'integer') ⇒ Object

Subclasses can override this if they need a different syntax.



242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
 
# File 'lib/og/store/sql.rb', line 242

def create_join_table_sql(join_table_info, suffix = 'NOT NULL', key_type = 'integer')
  join_table = join_table_info[:table]
  first_index = join_table_info[:first_index]
  first_key = join_table_info[:first_key]
  second_key = join_table_info[:second_key]
  second_index = join_table_info[:second_index]

  sql = []

  sql << %{      
    CREATE TABLE #{join_table} (
      #{first_key} integer NOT NULL,
      #{second_key} integer NOT NULL,
      PRIMARY KEY(#{first_key}, #{second_key})
    )
  }

  # gmosx: not that useful?
  # sql << "CREATE INDEX #{first_index} ON #{join_table} (#{first_key})"
  # sql << "CREATE INDEX #{second_index} ON #{join_table} (#{second_key})"

  return sql
end

#date(date) ⇒ Object

Output YYY-mm-dd – TODO: Optimize this. ++



37
38
39
40
 
# File 'lib/og/store/sql.rb', line 37

def date(date)
  return nil unless date
  return "#{date.year}-#{date.month}-#{date.mday}" 
end

#escape(str) ⇒ Object

Escape an SQL string



17
18
19
20
 
# File 'lib/og/store/sql.rb', line 17

def escape(str)
  return nil unless str
  return str.gsub(/'/, "''")
end

#join_class_ordering(class1, class2) ⇒ Object 



156
157
158
159
160
161
162
 
# File 'lib/og/store/sql.rb', line 156

def join_class_ordering(class1, class2)
  if class1.to_s <= class2.to_s
    return class1, class2
  else
    return class2, class1, true
  end
end

#join_object_ordering(obj1, obj2) ⇒ Object 



148
149
150
151
152
153
154
 
# File 'lib/og/store/sql.rb', line 148

def join_object_ordering(obj1, obj2)
  if obj1.class.to_s <= obj2.class.to_s
    return obj1, obj2
  else
    return obj2, obj1, true
  end
end

#join_table(class1, class2, postfix = nil) ⇒ Object 



170
171
172
173
 
# File 'lib/og/store/sql.rb', line 170

def join_table(class1, class2, postfix = nil)
  first, second = join_class_ordering(class1, class2)
  build_join_name(first, second, postfix)
end

#join_table_index(key) ⇒ Object 



175
176
177
 
# File 'lib/og/store/sql.rb', line 175

def join_table_index(key)
  "#{key}_idx"
end

#join_table_info(relation, postfix = nil) ⇒ Object 



198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
 
# File 'lib/og/store/sql.rb', line 198

def join_table_info(relation, postfix = nil)

  # some fixes for schema inheritance.

  owner_class, target_class = relation.owner_class, relation.target_class
  
  raise "Undefined owner_class in #{target_class}" unless owner_class
  raise "Undefined target_class in #{owner_class}" unless target_class
  
  owner_class = owner_class.schema_inheritance_root_class if owner_class.schema_inheritance_child?
  target_class = target_class.schema_inheritance_root_class if target_class.schema_inheritance_child?

  owner_key, target_key = join_table_keys(owner_class, target_class)
  first, second, changed = join_class_ordering(owner_class, target_class)

  if changed
    first_key, second_key = target_key, owner_key
  else
    first_key, second_key = owner_key, target_key
  end

  table = (relation.table ?
    relation.table :
    join_table(owner_class, target_class, postfix)
  )
  
  return {
    :table => table,
    :owner_key => owner_key,
    :owner_table => table(owner_class),
    :target_key => target_key,
    :target_table => table(target_class),
    :first_table => table(first),
    :first_key => first_key,
    :first_index => join_table_index(first_key),
    :second_table => table(second),
    :second_key => second_key,
    :second_index => join_table_index(second_key)
  }
end

#join_table_key(klass) ⇒ Object 



179
180
181
182
 
# File 'lib/og/store/sql.rb', line 179

def join_table_key(klass)
  klass = klass.schema_inheritance_root_class if klass.schema_inheritance_child?
  "#{klass.to_s.demodulize.underscore.downcase}_oid"
end

#join_table_keys(class1, class2) ⇒ Object 



184
185
186
187
188
189
190
191
 
# File 'lib/og/store/sql.rb', line 184

def join_table_keys(class1, class2)
  if class1 == class2
    # Fix for the self-join case.
    return join_table_key(class1), "#{join_table_key(class2)}2"
  else
    return join_table_key(class1), join_table_key(class2)
  end
end

#ordered_join_table_keys(class1, class2) ⇒ Object 



193
194
195
196
 
# File 'lib/og/store/sql.rb', line 193

def ordered_join_table_keys(class1, class2)
  first, second = join_class_ordering(class1, class2)
  return join_table_keys(first, second)
end

#parse_blob(val) ⇒ Object

– TODO: implement me!! ++



97
98
99
 
# File 'lib/og/store/sql.rb', line 97

def parse_blob(val)
  val
end

#parse_boolean(str) ⇒ Object

Parse a boolean true, 1, t => true other => false



88
89
90
91
 
# File 'lib/og/store/sql.rb', line 88

def parse_boolean(str)
  return true if (str=='true' || str=='t' || str=='1')
  return false
end

#parse_date(str) ⇒ Object

Input YYYY-mm-dd – TODO: Optimize this. ++



79
80
81
82
 
# File 'lib/og/store/sql.rb', line 79

def parse_date(str)
  return nil unless str
  return Date.strptime(str)
end

#parse_float(fl) ⇒ Object

Parse a float.



59
60
61
62
 
# File 'lib/og/store/sql.rb', line 59

def parse_float(fl)
  fl = fl.to_f if fl
  fl
end

#parse_int(int) ⇒ Object

Parse an integer.



52
53
54
55
 
# File 'lib/og/store/sql.rb', line 52

def parse_int(int)
  int = int.to_i if int
  int
end

#parse_timestamp(str) ⇒ Object

Parse sql datetime – TODO: Optimize this. ++



69
70
71
72
 
# File 'lib/og/store/sql.rb', line 69

def parse_timestamp(str)
  return nil unless str
  return Time.parse(str)    
end

#quote(vals) ⇒ Object

Escape the various Ruby types.



103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
 
# File 'lib/og/store/sql.rb', line 103

def quote(vals)
  vals = [vals] unless vals.is_a?(Array)
  quoted = vals.inject("") do |s,val|
    s += case val
      when Fixnum, Integer, Float
        val ? val.to_s : 'NULL'
      when String
        val ? "'#{escape(val)}'" : 'NULL'
      when Time
        val ? "'#{timestamp(val)}'" : 'NULL'
      when Date
        val ? "'#{date(val)}'" : 'NULL'
      when TrueClass
        val ? "'t'" : 'NULL'
      else
        # gmosx: keep the '' for nil symbols.
        val ? escape(val.to_yaml) : ''
    end + ','
  end
  quoted.chop!
  vals.size > 1 ? "(#{quoted})" : quoted
end

#quote_array(val) ⇒ Object Also known as: quotea

Escape the Array Ruby type.



128
129
130
131
132
133
134
135
 
# File 'lib/og/store/sql.rb', line 128

def quote_array(val)
  case val
    when Array
      val.collect{ |v| quotea(v) }.join(',')
    else
      quote(val)
  end
end

#table(klass) ⇒ Object 



144
145
146
 
# File 'lib/og/store/sql.rb', line 144

def table(klass)
  klass.ann.self[:sql_table] || klass.ann.self[:table] || "#{Og.table_prefix}#{tableize(klass)}"
end

#tableize(klass) ⇒ Object

Apply table name conventions to a class name.



140
141
142
 
# File 'lib/og/store/sql.rb', line 140

def tableize(klass)
  "#{klass.to_s.gsub(/::/, "_").downcase}"
end

#timestamp(time = Time.now) ⇒ Object

Convert a ruby time to an sql timestamp. – TODO: Optimize this. ++



27
28
29
30
 
# File 'lib/og/store/sql.rb', line 27

def timestamp(time = Time.now)
  return nil unless time
  return time.strftime("%Y-%m-%d %H:%M:%S")
end