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.

Direct Known Subclasses

MSSQL, Mysql, Oracle, SQLite

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?, method_missing, open, reopen, send_to_loggers, unknown, #unknown, #warn, warn, warn?, #warn?

Constructor Details

#initialize(url) ⇒ DbStorage

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

.reserved_keywords ⇒ Object (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

.safe_conversions ⇒ Object (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

.type_synonyms ⇒ Object (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

.inherited(subclass) ⇒ Object 



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

.storage_type ⇒ Object 



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

def storage_type
    :db
end

Instance Method Details

#alter_table(alter) ⇒ Object

Executes an alter table structured description.



539
540
541
542
543
544
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 539

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

#column_attributes(type, attributes) ⇒ Object

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

#column_name(name) ⇒ Object

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

#column_type(type, attributes) ⇒ Object

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

#create_table(create) ⇒ Object

Executes a create table structured description.



531
532
533
534
535
536
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 531

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

#describe_table(table) ⇒ Object

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



665
666
667
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 665

def describe_table(table)
    raise "Unimplemented"
end

#drop_field(table_name, field_name) ⇒ Object

Drops a field from the DB.



547
548
549
550
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 547

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

#drop_table(table_name) ⇒ Object

Drops a table from the DB.



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

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

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



674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 674

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

#foreign_key_name(name) ⇒ Object 



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

def foreign_key_name(name)
    name
end

#function(func) ⇒ Object

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
 
# 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]
        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()"
    end
    raise NotImplementedError, "#{self.class} does not support function #{func.func_name}"
end

#get_mapper(model) ⇒ Object

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

#list_tables ⇒ Object

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



660
661
662
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 660

def list_tables
    raise "Unimplemented"
end

#lock(table, mode = :exclusive) ⇒ Object 



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

#parse_db_column(col) ⇒ Object

Post processes column information retrieved from current DB.



670
671
672
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 670

def parse_db_column(col)
    col
end

#prepare_value(type, value) ⇒ Object

Prepares a value that will be used on the DB.



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

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

#query(query) ⇒ Object

Executes a select query (given in struct form).



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

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

#query_finished ⇒ Object 



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

#query_start ⇒ Object 



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

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

#reflect_column(table, column_name, column_attributes) ⇒ Object 



718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 718

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

#safe_schema_conversion?(current, field) ⇒ Boolean

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) 


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

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

#schema_field_equal?(current, field) ⇒ Boolean

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

Returns:

  • (Boolean) 


608
609
610
611
612
613
614
615
616
617
618
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 608

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

#sequence_name(name) ⇒ Object

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

#shorten_identifier(name, length) ⇒ Object

Shortens a DB name up to length.



641
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 641

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

#sql_add_field(table_name, name, type, attributes) ⇒ Object

Returns an array of SQL statements to add a field.



588
589
590
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 588

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

#sql_alter_field(table_name, name, type, attributes) ⇒ Object

Returns an array of SQL statements to alter a field.



593
594
595
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 593

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

#sql_alter_table(alter) ⇒ Object

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



491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 491

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

#sql_condition(query) ⇒ Object

Returns SQL and bound variables for a condition.



303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 303

def sql_condition(query)
    condition = query[:condition]
    return ['', []] unless (condition && condition[:values])
    bind_vars = []
    condition[:values].reject!{ |v| v.is_a?(Hash) && v[:values].empty? }
    mapped = condition[:values].map do |v|
        if (v.is_a? Hash) # subconditions
            # FIXME: optimize removing recursion
            
            sql, vals = sql_condition({:condition => v})
            bind_vars += vals
            sql = nil if sql.empty?
            sql = "(#{sql})" if sql && v[:values].length > 1
            sql
        elsif (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
    return mapped.select{ |p| p != nil}.join(' '+condition[:conj]+' '), bind_vars
end

#sql_condition_value(key, comp, value, bound_vars = true) ⇒ Object

Returns the SQL for a condition comparison.



335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 335

def sql_condition_value(key, comp, value, bound_vars=true)
    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

#sql_create_primary_key(table_name, fields) ⇒ Object 



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

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

#sql_create_table(create) ⇒ Object

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



472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 472

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

#sql_delete(delete, force = false) ⇒ Object

Returns SQL and bound values for a DELETE statement.



458
459
460
461
462
463
464
465
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 458

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

#sql_drop_field(table_name, field_name) ⇒ Object

Returns an array of SQL statements to drop a field.



598
599
600
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 598

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

#sql_drop_foreign_key(table_name, key_name) ⇒ Object 



562
563
564
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 562

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

#sql_drop_primary_key(table_name) ⇒ Object 



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

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

#sql_drop_table(table_name) ⇒ Object

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



603
604
605
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 603

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

#sql_insert(insert) ⇒ Object

Returns SQL and values for an insert statement.



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

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

#sql_joins(joins) ⇒ Object

Returns SQL and values for DB joins.



376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 376

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

#sql_keys(query) ⇒ Object

Returns the SQL for select keys.



264
265
266
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 264

def sql_keys(query)
    query[:keys].join(',')
end

#sql_limit(query) ⇒ Object

Returns the LIMIT and OFFSET SQL.



416
417
418
419
420
421
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 416

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

#sql_max(max) ⇒ Object

Aggregates #



700
701
702
703
704
705
706
707
708
709
710
711
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 700

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

#sql_order(query, replacements = {}) ⇒ Object

Returns SQL for the ORDER part.



405
406
407
408
409
410
411
412
413
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 405

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

#sql_select(query) ⇒ Object

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



243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 243

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?
    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

#sql_table_field(name, type, attributes) ⇒ Object

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



571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 571

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

#sql_tables(query) ⇒ Object

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



270
271
272
273
274
275
276
277
278
279
280
281
282
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 270

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

#sql_tables_join(query, table) ⇒ Object

Returns SQL and bound variables for joins.



285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 285

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

#sql_truncate(table) ⇒ Object 



467
468
469
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 467

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

#sql_update(update) ⇒ Object

Returns SQL and values for an update statement.



432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 432

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

#sql_update_values(update) ⇒ Object

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



451
452
453
454
455
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 451

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

#table_name(name) ⇒ Object

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

#total_rows ⇒ Object 



259
260
261
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 259

def total_rows
    curr[:total_rows]
end

#value_to_mapper(type, value) ⇒ Object

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



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

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