Class: Spider::Model::Storage::Db::DbStorage

Inherits:
BaseStorage show all
Defined in:
lib/spiderfw/model/storage/db/db_storage.rb

Overview

Represents a DB connection, and provides methods to execute structured queries on it. This is the class that generates the actual SQL; vendor specific extensions may override the generic SQL methods.

Class Attribute Summary (collapse)

Attributes inherited from BaseStorage

#instance_name, #url

Class Method Summary (collapse)

Instance Method Summary (collapse)

Methods inherited from BaseStorage

#==, base_types, #commit, #commit!, #commit_or_continue, #configure, #connect, #connected?, #connection, connection_alive?, #connection_attributes, connection_attributes, #connection_pool, connection_pools, #create_sequence, #curr, disconnect, #do_commit, #do_rollback, #do_start_transaction, #generate_uuid, get_connection, #in_transaction, #in_transaction?, max_connections, new_connection, #parse_url, #release, release_connection, remove_connection, #rollback, #rollback!, #rollback_savepoint, #savepoint, #sequence_exists?, #sequence_file_path, #sequence_next, sequence_sync, #start_transaction, supports?, #supports?, #supports_transactions?, #transactions_enabled?, #update_sequence, #value_for_condition, #value_for_save

Methods included from Logger

add, close, close_all, datetime_format, datetime_format=, debug, #debug, debug?, #debug?, enquire_loggers, #error, error, error?, #error?, #fatal, fatal, #fatal?, fatal?, info, #info, info?, #info?, log, #log, method_missing, open, reopen, send_to_loggers, unknown, #unknown, #warn, warn, #warn?, warn?

Constructor Details

- (DbStorage) initialize(url)

The constructor takes the connection URL, which will be parsed into connection params.



62
63
64
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 62

def initialize(url)
    super
end

Class Attribute Details

+ (Object) reserved_keywords (readonly)

An Array of keywords that can not be used in schema names.



28
29
30
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 28

def reserved_keywords
  @reserved_keywords
end

+ (Object) safe_conversions (readonly)

Type conversions which do not lose data. See also #safe_schema_conversion?



32
33
34
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 32

def safe_conversions
  @safe_conversions
end

+ (Object) type_synonyms (readonly)

An Hash of DB type equivalents.



30
31
32
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 30

def type_synonyms
  @type_synonyms
end

Class Method Details

+ (Object) inherited(subclass) 



39
40
41
42
43
44
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 39

def inherited(subclass)
    subclass.instance_variable_set("@reserved_keywords", @reserved_keywords)
    subclass.instance_variable_set("@type_synonyms", @type_synonyms)
    subclass.instance_variable_set("@safe_conversions", @safe_conversions)
    super
end

+ (Object) storage_type 



35
36
37
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 35

def storage_type
    :db
end

Instance Method Details

- (Object) alter_table(alter)

Executes an alter table structured description.



558
559
560
561
562
563
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 558

def alter_table(alter)
    sqls = sql_alter_table(alter)
    sqls.each do |sql|
        execute(sql)
    end
end

- (Object) collect_having_fields(condition) 



732
733
734
735
736
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 732

def collect_having_fields(condition)
    c = condition
    c.is_a?(Hash) ? 
        ((c[:group_by_fields] || []) + (c[:values] || []).map{ |v| collect_having_fields(v) }) : []
end

- (Object) column_attributes(type, attributes)

Returns the attributes corresponding to element type and attributes



139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 139

def column_attributes(type, attributes)
    db_attributes = {}
    case type.name
    when 'Spider::DataTypes::PK'
        db_attributes[:autoincrement] = true if supports?(:autoincrement)
        db_attributes[:length] = 11
    when 'String', 'Spider::DataTypes::Text'
        db_attributes[:length] = attributes[:length] if (attributes[:length])
    when 'Float'
        db_attributes[:length] = attributes[:length] if (attributes[:length])
        db_attributes[:precision] = attributes[:precision] if (attributes[:precision])
    when 'BigDecimal'
        db_attributes[:precision] = attributes[:precision] || 65
        db_attributes[:scale] = attributes[:scale] || 2
    when 'Spider::DataTypes::Binary'
        db_attributes[:length] = attributes[:length] if (attributes[:length])
    when 'Spider::DataTypes::Bool'
        db_attributes[:length] = 1
    end
    db_attributes[:autoincrement] = attributes[:autoincrement] if supports?(:autoincrement)
    return db_attributes
end

- (Object) column_name(name)

Fixes a string to be used as a column name.



105
106
107
108
109
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 105

def column_name(name)
    name = name.to_s
    name += '_field' if (self.class.reserved_keywords.include?(name.downcase)) 
    return name
end

- (Object) column_type(type, attributes)

Returns the db type corresponding to an element type.



117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 117

def column_type(type, attributes)
    case type.name
    when 'String'
        'TEXT'
    when 'Text'
        'LONGTEXT'
    when 'Fixnum'
        'INT'
    when 'Float'
        'REAL'
    when 'BigDecimal', 'Spider::DataTypes::Decimal'
        'DECIMAL'
    when 'Date', 'DateTime', 'Time'
        'DATE'
    when 'Spider::DataTypes::Binary'
        'BLOB'
    when 'Spider::DataTypes::Bool'
        'INT'
    end
end

- (Object) create_table(create)

Executes a create table structured description.



550
551
552
553
554
555
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 550

def create_table(create)
    sqls = sql_create_table(create)
    sqls.each do |sql|
        execute(sql)
    end
end

- (Object) describe_table(table)

Returns a description of the table as currently present in the DB.



684
685
686
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 684

def describe_table(table)
    raise "Unimplemented"
end

- (Object) drop_field(table_name, field_name)

Drops a field from the DB.



566
567
568
569
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 566

def drop_field(table_name, field_name)
    sqls = sql_drop_field(table_name, field_name)
    sqls.each{ |sql| execute(sql) }
end

- (Object) drop_table(table_name)

Drops a table from the DB.



572
573
574
575
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 572

def drop_table(table_name)
    sqls = sql_drop_table(table_name)
    sqls.each{ |sql| execute(sql) }
end

- (Object) dump(stream, tables = nil, options = {}) 



693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 693

def dump(stream, tables=nil, options={})
   tables ||= list_tables
   options = ({
       :include_create => true
   }).merge(options)
   tables.each do |t|
        Spider.logger.info("Dumping table #{t}")
        begin
            if options[:include_create]
                create = get_table_create_sql(t)
                stream << create
                stream << "\n\n"
            end
            dump_table_data(t, stream)
            stream << "\n\n"
        rescue => exc
            Spider.logger.error("Failed to dump table #{t}")
            Spider.logger.error(exc.message)
        end
    end
end

- (Object) foreign_key_name(name) 



111
112
113
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 111

def foreign_key_name(name)
    name
end

- (Object) function(func)

Returns the SQL for a QueryFuncs::Function

Raises:

  • (NotImplementedError) 


163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 163

def function(func)
    fields = func.elements.map{ |func_el|
        if (func_el.is_a?(Spider::QueryFuncs::Function))
            function(func_el)
        else
            func.mapper_fields[func_el.to_s]
        end
    }
    case func.func_name
    when :length
        return "LENGTH(#{fields.join(', ')})"
    when :trim
        return "TRIM(#{fields.join(', ')})"
    when :concat
        return "CONCAT(#{fields.join(', ')})"
    when :substr
        arguments = "#{func.start}"
        arguments += ", #{func.length}" if func.length
        return "SUBSTR(#{fields.join(', ')}, #{arguments})"
    when :subtract
        return "(#{fields[0]} - #{fields[1]})"
    when :rownum
        return "ROWNUM()"
    when :sum, :avg, :count, :first, :last, :max, :min
        return "#{func.func_name.to_s.upcase}(#{fields[0]})"
    end
    raise NotImplementedError, "#{self.class} does not support function #{func.func_name}"
end

- (Object) get_mapper(model)

Returns the default mapper for the storage. If the storage subclass contains a MapperExtension module, it will be mixed-in with the mapper.



69
70
71
72
73
74
75
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 69

def get_mapper(model)
    mapper = Spider::Model::Mappers::DbMapper.new(model, self)
    if (self.class.const_defined?(:MapperExtension))
        mapper.extend(self.class.const_get(:MapperExtension))
    end
    return mapper
end

- (Object) list_tables

Returns an array of the table names currently in the DB.



679
680
681
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 679

def list_tables
    raise "Unimplemented"
end

- (Object) lock(table, mode = :exclusive) 



78
79
80
81
82
83
84
85
86
87
88
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 78

def lock(table, mode=:exclusive)
    lockmode = case(mode)
    when :shared
        'SHARE'
    when :row_exclusive
        'ROW EXCLUSIVE'
    else
        'EXCLUSIVE'
    end
    execute("LOCK TABLE #{table} IN #{lockmode} MODE")
end

- (Object) parse_db_column(col)

Post processes column information retrieved from current DB.



689
690
691
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 689

def parse_db_column(col)
    col
end

- (Object) prepare_value(type, value)

Prepares a value that will be used on the DB.



213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 213

def prepare_value(type, value)
    case type.name
    when 'String', 'Spider::DataTypes::Text'
        enc = @configuration['encoding']
        if (enc && enc.downcase != 'utf-8')
            begin
                value = Iconv.conv(enc+'//IGNORE', 'utf-8', value.to_s+' ')[0..-2]
            rescue Iconv::InvalidCharacter
                value = ''
            end
        end
    when 'BigDecimal'
        value = value.to_f if value
    end
    return value
end

- (Object) query(query)

Executes a select query (given in struct form).



231
232
233
234
235
236
237
238
239
240
241
242
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 231

def query(query)
    curr[:last_query] = query
    case query[:query_type]
    when :select
        sql, bind_vars = sql_select(query)
        execute(sql, *bind_vars)
    when :count
        query[:keys] = ['COUNT(*) AS N']
        sql, bind_vars = sql_select(query)
        return execute(sql, *bind_vars)[0]['N'].to_i
    end
end

- (Object) query_finished 



52
53
54
55
56
57
58
59
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 52

def query_finished
    return unless curr[:query_start] # happens if there was no db connection
    now = Time.now
    diff = now - curr[:query_start]
    diff = 0 if diff < 0 # ??? 
    diff = diff*1000
    Spider.logger.info("Db query (#{@instance_name}) done in #{diff}ms")
end

- (Object) query_start 



48
49
50
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 48

def query_start
    curr[:query_start] = Time.now
end

- (Object) reflect_column(table, column_name, column_attributes) 



743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 743

def reflect_column(table, column_name, column_attributes)
    column_type = column_attributes[:type]
    el_type = nil
    el_attributes = {}
    case column_type
    when 'TEXT'
        el_type = String
    when 'LONGTEXT'
        el_type = Text
    when 'INT'
        if (column_attributes[:length] == 1)
            el_type = Spider::DataTypes::Bool
        else
            el_type = Fixnum
        end
    when 'REAL'
        el_type = Float
    when 'DECIMAL'
        el_type = BigDecimal
    when 'DATE'
        el_type = DateTime
    when 'BLOB'
        el_type = Spider::DataTypes::Binary
    end
    return el_type, el_attributes
    
end

- (Boolean) safe_schema_conversion?(current, field)

Checks if the conversion from a current DB field to a schema field is safe, i.e. can be done without loss of data.

Returns:

  • (Boolean) 


642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 642

def safe_schema_conversion?(current, field)
    attributes = field[:attributes]
    safe = self.class.safe_conversions
    if (current[:type] != field[:type])
        if safe[current[:type]] && safe[current[:type]].include?(field[:type])
            return true 
        else
            return false
        end
    end
    return true if ((!current[:length] || current[:length] == 0) \
                    || (attributes[:length] && current[:length] <= attributes[:length])) && \
                   ((!current[:precision] || current[:precision] == 0) \
                   || (attributes[:precision] && current[:precision] <= attributes[:precision]))
    return false
end

- (Boolean) schema_field_equal?(current, field)

Checks if a DB field is equal to a schema field.

Returns:

  • (Boolean) 


627
628
629
630
631
632
633
634
635
636
637
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 627

def schema_field_equal?(current, field)
    attributes = field[:attributes]
    return false unless current[:type] == field[:type] || 
        (self.class.type_synonyms && self.class.type_synonyms[current[:type]] && self.class.type_synonyms[current[:type]].include?(field[:type]))
    try_method = :schema_field_#{field[:type].downcase}_equal?"
    return send(try_method, current, field) if (respond_to?(try_method))
    current[:length] ||= 0; attributes[:length] ||= 0; current[:precision] ||= 0; attributes[:precision] ||= 0
    return false unless current[:length] == attributes[:length]
    return false unless current[:precision] == attributes[:precision]
    return true
end

- (Object) sequence_name(name)

Fixes a string to be used as a sequence name.



100
101
102
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 100

def sequence_name(name)
    return name.to_s.gsub(':', '_')
end

- (Object) shorten_identifier(name, length)

Shortens a DB name up to length.



660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 660

def shorten_identifier(name, length)
    while (name.length > length)
        parts = name.split('_')
        max = 0
        max_i = nil
        parts.each_index do |i|
            if (parts[i].length > max)
                max = parts[i].length
                max_i = i
            end
        end
        parts[max_i] = parts[max_i][0..-2]
        name = parts.join('_')
        name.gsub!('_+', '_')
    end
    return name
end

- (Object) sql_add_field(table_name, name, type, attributes)

Returns an array of SQL statements to add a field.



607
608
609
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 607

def sql_add_field(table_name, name, type, attributes)
    ["ALTER TABLE #{table_name} ADD #{sql_table_field(name, type, attributes)}"]
end

- (Object) sql_alter_field(table_name, name, type, attributes)

Returns an array of SQL statements to alter a field.



612
613
614
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 612

def sql_alter_field(table_name, name, type, attributes)
    ["ALTER TABLE #{table_name} MODIFY #{sql_table_field(name, type, attributes)}"]
end

- (Object) sql_alter_table(alter)

Returns an array of SQL statements for an alter structured description.



510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 510

def sql_alter_table(alter)
    current = alter[:current]
    table_name = alter[:table]
    add_fields = alter[:add_fields]
    alter_fields = alter[:alter_fields]
    alter_attributes = alter[:attributes]
    sqls = []
    
    add_fields.each do |field|
        name, type, attributes = field
        sqls += sql_add_field(table_name, field[:name], field[:type], field[:attributes])
    end
    alter_fields.each do |field|
        name, type, attributes = field
        sqls += sql_alter_field(table_name, field[:name], field[:type], field[:attributes])
    end
    if (alter_attributes[:primary_keys] && !alter_attributes[:primary_keys].empty?)
        sqls << sql_drop_primary_key(table_name) if (current[:primary_keys] && !current[:primary_keys].empty? && current[:primary_keys] != alter_attributes[:primary_keys])
        sqls << sql_create_primary_key(table_name, alter_attributes[:primary_keys])
    end
    if (alter_attributes[:foreign_key_constraints])
        cur_fkc = current && current[:foreign_key_constraints] ? current[:foreign_key_constraints] : []
        cur_fkc.each do |fkc|
            next if alter_attributes[:foreign_key_constraints].include?(fkc)
            sqls << sql_drop_foreign_key(table_name, foreign_key_name(fkc.name))
        end
        if (alter_attributes[:foreign_key_constraints])
            alter_attributes[:foreign_key_constraints].each do |fkc|
                next if cur_fkc.include?(fkc)
                sql = "ALTER TABLE #{table_name} ADD CONSTRAINT #{foreign_key_name(fkc.name)} FOREIGN KEY (#{fkc.fields.keys.join(',')}) "
                sql += "REFERENCES #{fkc.table} (#{fkc.fields.values.join(',')})"
                sqls << sql
            end
        end
    end
    return sqls
end

- (Object) sql_condition(query, having = false)

Returns SQL and bound variables for a condition.



316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 316

def sql_condition(query, having=false)
    condition = query[:condition]
    return ['', []] unless (condition && condition[:values])
    bind_vars = []
    condition[:values].reject!{ |v| (v.is_a?(Hash) && v[:values].empty?)}
    vals = condition[:values]

    return nil if !having && condition[:is_having]
    mapped = vals.map do |v|
        if v.is_a? Hash # subconditions
            # FIXME: optimize removing recursion
            sql, vals = sql_condition({:condition => v}, having)
            next unless sql
            bind_vars += vals
            sql = nil if sql.empty?
            sql = "(#{sql})" if sql && v[:values].length > 1
            sql
        elsif !having || condition[:is_having]
            if v[2].is_a? Spider::QueryFuncs::Expression
                sql_condition_value(v[0], v[1], v[2].to_s, false)
            else
                v[1] = 'between' if (v[2].is_a?(Range))
                v[2].upcase! if (v[1].to_s.downcase == 'ilike')
                if (v[1].to_s.downcase == 'between')
                    bind_vars << v[2].first
                    bind_vars << v[2].last
                else
                    bind_vars << v[2] unless v[2].nil?
                end
                sql_condition_value(v[0], v[1], v[2])
            end
        end
    end
    return mapped.reject{ |p| p.nil? }.join(' '+(condition[:conj] || 'and')+' '), bind_vars
end

- (Object) sql_condition_value(key, comp, value, bound_vars = true)

Returns the SQL for a condition comparison.



353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 353

def sql_condition_value(key, comp, value, bound_vars=true)
    key = key.expression if key.is_a?(FieldExpression)
    if (comp.to_s.downcase == 'ilike')
        comp = 'like'
        key = "UPPER(#{key})"
    end
    if (value.nil?)
        comp = comp == '=' ? "IS" : "IS NOT"
        sql = "#{key} #{comp} NULL"
    else
        if comp.to_s.downcase == 'between'
            if bound_vars
                val0 = val1 = '?' 
            else
                val0, val1 = value
            end
            sql = "#{key} #{comp} #{val0} AND #{val1}"
        else
            val = bound_vars ? '?' : value
            sql = "#{key} #{comp} #{val}"
            if comp == '<>'
                sql = "(#{sql} or #{key} IS NULL)"
            end
        end
    end
    return sql
end

- (Object) sql_create_primary_key(table_name, fields) 



585
586
587
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 585

def sql_create_primary_key(table_name, fields)
    "ALTER TABLE #{table_name} ADD PRIMARY KEY ("+fields.join(', ')+")"
end

- (Object) sql_create_table(create)

Returns an array of SQL statements for a create structured description.



491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 491

def sql_create_table(create)
    name = create[:table]
    fields = create[:fields]
    sql_fields = ''
    fields.each do |field|
        attributes = field[:attributes]
        attributes ||= {}
        length = attributes[:length]
        sql_fields += ', ' unless sql_fields.empty?
        sql_fields += sql_table_field(field[:name], field[:type], attributes)
    end
    if (create[:attributes][:primary_keys] && !create[:attributes][:primary_keys].empty?)
        primary_key_fields = create[:attributes][:primary_keys].join(', ')
        sql_fields += ", PRIMARY KEY (#{primary_key_fields})"
    end
    ["CREATE TABLE #{name} (#{sql_fields})"]
end

- (Object) sql_delete(delete, force = false)

Returns SQL and bound values for a DELETE statement.



477
478
479
480
481
482
483
484
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 477

def sql_delete(delete, force=false)
    curr[:last_query_type] = :delete
    where, bind_vars = sql_condition(delete)
    where = "1=0" if !force && (!where || where.empty?)
    sql = "DELETE FROM #{delete[:table]}"
    sql += " WHERE #{where}" if where && !where.empty?
    return [sql, bind_vars]
end

- (Object) sql_drop_field(table_name, field_name)

Returns an array of SQL statements to drop a field.



617
618
619
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 617

def sql_drop_field(table_name, field_name)
    ["ALTER TABLE #{table_name} DROP COLUMN #{field_name}"]
end

- (Object) sql_drop_foreign_key(table_name, key_name) 



581
582
583
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 581

def sql_drop_foreign_key(table_name, key_name)
    "ALTER TABLE #{table_name} DROP FOREIGN KEY #{key_name}"
end

- (Object) sql_drop_primary_key(table_name) 



577
578
579
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 577

def sql_drop_primary_key(table_name)
    "ALTER TABLE #{table_name} DROP PRIMARY KEY"
end

- (Object) sql_drop_table(table_name)

Returns an array of SQL statements needed to drop a table.



622
623
624
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 622

def sql_drop_table(table_name)
    ["DROP TABLE #{table_name}"]
end

- (Object) sql_insert(insert)

Returns SQL and values for an insert statement.



443
444
445
446
447
448
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 443

def sql_insert(insert)
    curr[:last_query_type] = :insert
    sql = "INSERT INTO #{insert[:table]} (#{insert[:values].keys.map{ |k| k.name }.join(', ')}) " +
          "VALUES (#{insert[:values].values.map{'?'}.join(', ')})"
    return [sql, insert[:values].values]
end

- (Object) sql_joins(joins)

Returns SQL and values for DB joins.



395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 395

def sql_joins(joins)
    types = {
        :inner => 'INNER', :outer => 'OUTER', :left => 'LEFT OUTER', :right => 'RIGHT OUTER'
    }
    values = []
    sql = joins.map{ |join|
        to_t = join[:as] || join[:to]
        sql_on = join[:keys].map{ |from_f, to_f|
            to_field = to_f.is_a?(FieldExpression) ? to_f.expression : "#{to_t}.#{to_f.name}"
            if from_f.is_a?(FieldExpression)
                "#{to_field} = #{from_f.expression}"
            else
                "#{from_f} = #{to_field}"
            end
        }.join(' AND ')
        if (join[:condition])
            condition_sql, condition_values = sql_condition({:condition => join[:condition]})
            sql_on += " and #{condition_sql}"
            values += condition_values
        end
        j = "#{types[join[:type]]} JOIN #{join[:to]}"
        j += " #{join[:as]}" if join[:as]
        j += " ON (#{sql_on})"
        j
    }.join(" ")
    return [sql, values]
end

- (Object) sql_keys(query)

Returns the SQL for select keys.



276
277
278
279
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 276

def sql_keys(query)
    query = {:keys => query} unless query.is_a?(Hash)
    query[:keys].join(',')
end

- (Object) sql_limit(query)

Returns the LIMIT and OFFSET SQL.



435
436
437
438
439
440
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 435

def sql_limit(query)
    sql = ""
    sql += "LIMIT #{query[:limit]} " if query[:limit]
    sql += "OFFSET #{query[:offset]} " if query[:offset]
    return sql
end

- (Object) sql_max(max)

Aggregates #



719
720
721
722
723
724
725
726
727
728
729
730
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 719

def sql_max(max)
    values = []
    from_sql, from_values = sql_tables(max)
    values += from_values
    sql = "SELECT MAX(#{max[:field]}) AS M FROM #{from_sql}"
    if (max[:condition])
        condition_sql, condition_values = sql_condition(max)
        sql += " WHERE #{condition_sql}"
        values += condition_values
    end
    return sql, values
end

- (Object) sql_order(query, replacements = {})

Returns SQL for the ORDER part.



424
425
426
427
428
429
430
431
432
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 424

def sql_order(query, replacements={})
    return '' unless query[:order]
    replacements ||= {}
    return query[:order].map{|o| 
        repl = replacements[o[0].to_s]
        ofield = repl ? repl : o[0]
        "#{ofield} #{o[1]}"
    }.join(' ,')
end

- (Object) sql_select(query)

Returns a two element array, containing the SQL for given select query, and the variables to bind.



245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 245

def sql_select(query)
    curr[:last_query_type] = :select
    bind_vars = query[:bind_vars] || []
    tables_sql, tables_values = sql_tables(query)
    sql = "SELECT #{sql_keys(query)} FROM #{tables_sql} "
    bind_vars += tables_values
    where, vals = sql_condition(query)
    bind_vars += vals
    sql += "WHERE #{where} " if where && !where.empty?
    having, having_vals = sql_condition(query, true)
    unless having.blank?
        group_fields = (
            query[:keys].select{ |k| !k.is_a?(FieldExpression)
        } + collect_having_fields(query[:condition])).flatten.uniq
        group_keys = sql_keys(group_fields)
        sql += "GROUP BY #{group_keys} "
        sql += "HAVING #{having} "
        bind_vars += having_vals
    end
    order = sql_order(query)
    sql += "ORDER BY #{order} " if order && !order.empty?
    limit = sql_limit(query)
    sql += limit if limit
    return sql, bind_vars
end

- (Object) sql_table_field(name, type, attributes)

Returns the SQL for a field definition (used in create and alter table)



590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 590

def sql_table_field(name, type, attributes)
    f = "#{name} #{type}"
    if (type == 'DECIMAL')
        f += "(#{attributes[:precision]}, #{attributes[:scale]})"
    else
        if attributes[:length] && attributes[:length] != 0
            f += "(#{attributes[:length]})"
        elsif attributes[:precision]
            f += "(#{attributes[:precision]}"
            f += "#{attributes[:scale]}" if attributes[:scale]
            f += ")"
        end
    end
    return f
end

- (Object) sql_tables(query)

Returns an array containing the 'FROM' part of an SQL query (including joins), and the bound variables, if any.



283
284
285
286
287
288
289
290
291
292
293
294
295
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 283

def sql_tables(query)
    values = []
    sql = query[:tables].map{ |table|
        str = table.name
        if (query[:joins] && query[:joins][table])
            join_str, join_values = sql_tables_join(query, table)
            str += " "+join_str
            values += join_values
        end
        str
    }.join(', ')
    return [sql, values]
end

- (Object) sql_tables_join(query, table)

Returns SQL and bound variables for joins.



298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 298

def sql_tables_join(query, table)
    str = ""
    values = []
    query[:joins][table].each_key do |to_table|
        join, join_values = sql_joins(query[:joins][table][to_table])
        str += " "+join
        values += join_values
        if (query[:joins][to_table])
            query[:joins][to_table].delete(table) # avoid endless loop
            sub_str, sub_values = sql_tables_join(query, to_table)
            str += " "+sub_str
            values += sub_values
        end
    end
    return str, values
end

- (Object) sql_truncate(table) 



486
487
488
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 486

def sql_truncate(table)
    "TRUNCATE #{table}"
end

- (Object) sql_update(update)

Returns SQL and values for an update statement.



451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 451

def sql_update(update)
    curr[:last_query_type] = :update
    values = []
    tables = update[:table].to_s
    if (update[:joins] && update[:joins][update[:table]])
        join_str, join_values = sql_tables_join(update, update[:table])
        tables += " "+join_str
        values += join_values
    end
    values += update[:values].values.reject{ |v| v.is_a?(Spider::QueryFuncs::Expression) }
    sql = "UPDATE #{tables} SET "
    sql += sql_update_values(update)
    where, bind_vars = sql_condition(update)
    values += bind_vars
    sql += " WHERE #{where}"
    return [sql, values]
end

- (Object) sql_update_values(update)

Returns the COLUMN = val, ... part of an update statement.



470
471
472
473
474
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 470

def sql_update_values(update)
    update[:values].map{ |k, v| 
        v.is_a?(Spider::QueryFuncs::Expression) ? "#{k.name} = #{v}" : "#{k.name} = ?"
    }.join(', ')
end

- (Object) table_name(name)

Fixes a string to be used as a table name.



95
96
97
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 95

def table_name(name)
    return name.to_s.gsub(':', '_')
end

- (Object) total_rows 



271
272
273
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 271

def total_rows
    curr[:total_rows]
end

- (Object) value_to_mapper(type, value)

Converts a value loaded from the DB to return it to the mapper.



198
199
200
201
202
203
204
205
206
207
208
209
210
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 198

def value_to_mapper(type, value)
    if (type.name == 'String' || type.name == 'Spider::DataTypes::Text')
        enc = @configuration['encoding']
        if (enc && enc.downcase != 'utf-8')
            begin
                value = Iconv.conv('utf-8//IGNORE', enc, value.to_s+' ')[0..-2] if value
            rescue Iconv::InvalidCharacter
                value = ''
            end
        end
    end
    return value
end