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! ++



43
44
45
 
# File 'lib/og/store/sql.rb', line 43

def blob(val)
  val
end

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



161
162
163
164
165
 
# File 'lib/og/store/sql.rb', line 161

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.



229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
 
# File 'lib/og/store/sql.rb', line 229

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. ++



34
35
36
37
 
# File 'lib/og/store/sql.rb', line 34

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

#escape(str) ⇒ Object

Escape an SQL string



14
15
16
17
 
# File 'lib/og/store/sql.rb', line 14

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

#join_class_ordering(class1, class2) ⇒ Object 



153
154
155
156
157
158
159
 
# File 'lib/og/store/sql.rb', line 153

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 



145
146
147
148
149
150
151
 
# File 'lib/og/store/sql.rb', line 145

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 



167
168
169
170
 
# File 'lib/og/store/sql.rb', line 167

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 



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

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

#join_table_info(owner_class, target_class, postfix = nil) ⇒ Object 



195
196
197
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
 
# File 'lib/og/store/sql.rb', line 195

def join_table_info(owner_class, target_class, postfix = nil)
  # some fixes for schema inheritance.
  
  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

  return {
    :table => join_table(owner_class, target_class, postfix),
    :owner_key => owner_key,
    :target_key => target_key,
    :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 



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

def join_table_key(klass)
  klass = klass.schema_inheritance_root_class if klass.schema_inheritance_child?
  "#{klass.to_s.split('::').last.downcase}_oid"
end

#join_table_keys(class1, class2) ⇒ Object 



181
182
183
184
185
186
187
188
 
# File 'lib/og/store/sql.rb', line 181

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 



190
191
192
193
 
# File 'lib/og/store/sql.rb', line 190

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!! ++



94
95
96
 
# File 'lib/og/store/sql.rb', line 94

def parse_blob(val)
  val
end

#parse_boolean(str) ⇒ Object

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



85
86
87
88
 
# File 'lib/og/store/sql.rb', line 85

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. ++



76
77
78
79
 
# File 'lib/og/store/sql.rb', line 76

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

#parse_float(fl) ⇒ Object

Parse a float.



56
57
58
59
 
# File 'lib/og/store/sql.rb', line 56

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

#parse_int(int) ⇒ Object

Parse an integer.



49
50
51
52
 
# File 'lib/og/store/sql.rb', line 49

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

#parse_timestamp(str) ⇒ Object

Parse sql datetime – TODO: Optimize this. ++



66
67
68
69
 
# File 'lib/og/store/sql.rb', line 66

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

#quote(vals) ⇒ Object

Escape the various Ruby types.



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

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.



125
126
127
128
129
130
131
132
 
# File 'lib/og/store/sql.rb', line 125

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

#table(klass) ⇒ Object 



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

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.



137
138
139
 
# File 'lib/og/store/sql.rb', line 137

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. ++



24
25
26
27
 
# File 'lib/og/store/sql.rb', line 24

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