Class: Momomoto::Table

Inherits:
Base
  • Object
show all
Defined in:
lib/momomoto/table.rb

Overview

This class implements access to tables/views. It must not be used directly but you should inherit from this class.

Direct Known Subclasses

Information_schema::Columns

Class Method Summary collapse

Methods inherited from Base

logical_operator=, momomoto_attribute, momomoto_attribute_reader

Class Method Details

.base_table? ⇒ Boolean

is this a base table

Returns:

  • (Boolean) 


31
32
33
 
# File 'lib/momomoto/table.rb', line 31

def base_table?
  table_type == "BASE TABLE"
end

.delete(row) ⇒ Object

delete row from table

Raises:



277
278
279
280
281
282
283
284
285
286
287
288
 
# File 'lib/momomoto/table.rb', line 277

def delete( row )
  raise CriticalError, 'Deleting is only allowed for tables with primary keys' if primary_keys.empty?
  raise Error, "this is a new record" if row.new_record?
  conditions = {}
  primary_keys.each do | field_name |
    raise Error, "Primary key fields must not be empty!" if not row.send( field_name )
    conditions[field_name] = row.send( field_name )
  end
  sql = "DELETE FROM #{full_name} #{compile_where(conditions)};"
  row.new_record = true
  database.execute( sql )
end

.full_name ⇒ Object

get the full name of table including, if set, schema



26
27
28
 
# File 'lib/momomoto/table.rb', line 26

def full_name
  "#{ schema_name ? schema_name + '.' : ''}#{table_name}"
end

.insert(row) ⇒ Object

Creates an insert statement for a row. Do not use it directly but use row.write or Table.write(row) instead.

Raises:



224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
 
# File 'lib/momomoto/table.rb', line 224

def insert( row )
  fields, values = [], []
  columns.each do | field_name, datatype |
    # check for set primary key fields or fetch respective default values
    if primary_keys.member?( field_name ) && row.send( field_name ) == nil
      if datatype.default
        row.send( "#{field_name}=", database.execute("SELECT #{datatype.default};")[0][0] )
      end
      if row.send( field_name ) == nil
        raise Error, "Primary key fields(#{field_name}) need to be set or must have a default"
      end
    end
    next if row.send( field_name ).nil?
    fields << field_name
    values << datatype.escape( row.get_column( field_name ))
  end
  raise Error, "insert with all fields nil" if fields.empty?
  sql = "INSERT INTO " + full_name + '(' + fields.join(',') + ') VALUES (' + values.join(',') + ');'
  row.new_record = false
  database.execute( sql )
end

.new(fields = {}) ⇒ Object

constructor for a record in this table accepts a hash with presets for the fields of the record



143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
 
# File 'lib/momomoto/table.rb', line 143

def new( fields = {} )
  initialize unless initialized
  new_row = const_get(:Row).new( [] )
  new_row.new_record = true
  # set default values
  columns.each do | key, value |
    next if primary_keys.member?( key )
    if value.default
      if value.default.match( /^\d+$/ )
        new_row[ key ] = value.default
      elsif value.default == "true"
        new_row[ key ] = true
      elsif value.default == "false"
        new_row[ key ] = false
      elsif m = value.default.match( /^'([^']+)'::(text|interval|time(stamp)? with(out)? time zone)$/ )
        new_row[ key ] = m[1]
      end
    end
  end
  fields.each do | key, value |
    new_row[ key ] = value
  end
  new_row
end

.select(conditions = {}, options = {}) ⇒ Object

Searches for records and returns an Array containing the records. There are a bunch of different use cases as this method is the primary way to access all rows in the database.

Selecting rows based on expression:

#selects the feeds that match both the given url and author fields
Posts.select(:feed_url => "https://www.c3d2.de/news-atom.xml",:author => "fnord")

Using order statements:

See Order#asc, Order#desc and Order#lower

#Selects conferences depending on start_date, starting with the oldest date.
#If two conferences start at the same date(day) use the second order parameter
#start_time.
Conference.select({},{:order => Momomoto.asc([:start_date,:start_time])} )

Using limit statement:

See Base#compile_limit

#selects five feeds
five_feeds = Feeds.select( {},{:limit => 5} )

Using offset statement:

See Base#compile_offset

#selects five feeds ommitting the first 23 rows
five_feeds = Feeds.select( {}, {:offset => 23, :limit => 5} )

Using logical operators:

See Datatype::Base#operator_sign for basic comparison operators
See Base#logical_operator for the supported logical operators

#selects the posts where the content field case-insensitevely matches
#"surveillance".
Posts.select( :content => {:ilike => 'surveillance'} )

#selects all conferences with a start_date before the current time.
Conferences.select( :start_date => {:le => Time.now} )

feed1 = "https://www.c3d2.de/news-atom.xml"
feed2 = "http://www.c3d2.de/news-atom.xml"
#selects the feeds with a field url that matches either feed1 or feed2
Feeds.select( :OR=>{:url => [feed1,feed2]} )

Selecting only given columns:

See Base#initialize_row for the implementation

#selects title and content for every row found in table Posts.
posts = Posts.select({},{:columns => [:title,:content]} )

The returned rows are special. They do not contain getter and setter
for the rest of the columns of the row. Only the specified columns
and all the primary keys of the table have proper accessor methods.

However, you can still change the rows and write them back to database:
posts.first.title = "new title"
posts.first.write



98
99
100
101
102
103
104
105
106
107
 
# File 'lib/momomoto/table.rb', line 98

def select( conditions = {}, options = {} )
  initialize unless initialized
  row_class = build_row_class( options )
  sql = compile_select( conditions, options )
  data = []
  database.execute( sql ).each do | row |
    data << row_class.new( row )
  end
  data
end

.select_or_new(conditions = {}, options = {}) ⇒ Object

Tries to select the specified record or creates a new one if it does not find it. Raises an exception if multiple records are found. You can pass a block which has to deliver the respective values for the primary key fields.

# selects the feed row matching the specified URL or creates a new
# row based on the given URL.
Feeds.select_or_new( :url => "https://www.c3d2.de/news-atom.xml" )



176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
 
# File 'lib/momomoto/table.rb', line 176

def select_or_new( conditions = {}, options = {} )
  begin
    if block_given?
      conditions = conditions.dup
      primary_keys.each do | field |
        conditions[ field ] = yield( field ) if not conditions[ field ]
        raise ConversionError if not conditions[ field ]
      end
    end
    rows = select( conditions, options )
  rescue ConversionError
  end
  if rows && rows.length > 1
    raise Too_many_records, "Multiple values found in select_or_new for #{self}:#{conditions.inspect}"
  elsif rows && rows.length == 1
    rows.first
  else
    new( options[:copy_values] != false ? conditions : {} )
  end
end

.select_outer_join(conditions = {}, options = {}) ⇒ Object

experimental

Searches for records and returns an array containing the records



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
138
139
140
 
# File 'lib/momomoto/table.rb', line 112

def select_outer_join( conditions = {}, options = {} )
  initialize unless initialized
  join_table = options[:join]
  fields = columns.keys.map{|field| full_name+'."'+field.to_s+'"'}
  fields += join_table.columns.keys.map{|field| join_table.full_name+'."'+field.to_s+'"'}

  sql = "SELECT " + fields.join( "," ) + " FROM "
  sql += full_name
  sql += " LEFT OUTER JOIN " + join_table.full_name + " USING(#{join_columns(join_table).join(',')})"
  sql += compile_where( conditions )
  sql += compile_order( options[:order] ) if options[:order]
  sql += compile_limit( options[:limit] ) if options[:limit]
  sql += compile_offset( options[:offset] ) if options[:offset]
  data = {}
  database.execute( sql ).each do | row |
    new_row = row[0, columns.keys.length]
    data[new_row] ||= []
    join_row = row[columns.keys.length,join_table.columns.keys.length]
    data[new_row] << join_table.const_get(:Row).new( join_row ) if join_row.nitems > 0
  end
  result = []
  data.each do | new_row, join_row |
    new_row = const_get(:Row).new( new_row )
    new_row.instance_variable_set(:@join, join_row)
    new_row.send( :instance_eval ) { class << self; self; end }.send(:define_method, join_table.table_name ) do join_row end
    result << new_row
  end
  result
end

.select_single(conditions = {}, options = {}) ⇒ Object

Select a single row from the database raises Momomoto::Nothing_found if no row matched. raises Momomoto::Too_many_records if more than one record was found.



200
201
202
203
204
205
206
207
 
# File 'lib/momomoto/table.rb', line 200

def select_single( conditions = {}, options = {} )
  data = select( conditions, options )
  case data.length
    when 0 then raise Nothing_found, "nothing found in #{full_name}"
    when 1 then return data[0]
    else raise Too_many_records, "too many records found in #{full_name}"
  end
end

.update(row) ⇒ Object

Creates an update statement for a row. Do not call update directly but use row.write or Table.write(row) instead.

Get the value of row.new_record? before writing to database to find out if you are updating a row that already exists in the database.

feed = Feeds.select_single( :url => "http://www.c3d2.de/news-atom.xml" )
feed.new_record? => false
feed[:url] = "https://www.c3d2.de/news-atom.xml"
feed.new_record? => false

feed = Feeds.select_or_new( :url => "http://astroblog.spaceboyz.net/atom.rb" )
feed.new_record? => true
feed.write => true
feed.new_record? => false

Raises:



261
262
263
264
265
266
267
268
269
270
271
272
273
274
 
# File 'lib/momomoto/table.rb', line 261

def update( row )
  raise CriticalError, 'Updating is only allowed for tables with primary keys' if primary_keys.empty?
  setter, conditions = [], {}
  row.class.columns.each do | field_name, data_type |
    next if not row.dirty.member?( field_name )
    setter << field_name.to_s + ' = ' + data_type.escape(row.get_column(field_name))
  end
  primary_keys.each do | field_name |
    raise Error, "Primary key fields must not be empty!" if not row.send( field_name )
    conditions[field_name] = row.send( field_name )
  end
  sql = 'UPDATE ' + full_name + ' SET ' + setter.join(',') + compile_where( conditions ) + ';'
  database.execute( sql )
end

.view? ⇒ Boolean

is this a view

Returns:

  • (Boolean) 


36
37
38
 
# File 'lib/momomoto/table.rb', line 36

def view?
  table_type == "VIEW"
end

.write(row) ⇒ Object

Writes row back to database. This method is called by Momomoto::Row#write



211
212
213
214
215
216
217
218
219
220
 
# File 'lib/momomoto/table.rb', line 211

def write( row ) # :nodoc:
  if row.new_record?
    insert( row )
  else
    return false unless row.dirty?
    update( row )
  end
  row.clean_dirty
  true
end