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 



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

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.



212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
 
# File 'lib/og/store/sql.rb', line 212

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 



136
137
138
139
140
141
142
 
# File 'lib/og/store/sql.rb', line 136

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 



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

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 



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

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 



155
156
157
 
# File 'lib/og/store/sql.rb', line 155

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

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



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

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 



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

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 



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

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 



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

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(val) ⇒ Object

Escape the various Ruby types.



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

def quote(val)
  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

#table(klass) ⇒ Object 



124
125
126
 
# File 'lib/og/store/sql.rb', line 124

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.



120
121
122
 
# File 'lib/og/store/sql.rb', line 120

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