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, Test::DbStorage

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_or_continue, #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, check_request_level, 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, request_level, send_to_loggers, set_request_level, unknown, #unknown, warn, #warn, warn?, #warn?

Constructor Details

#initialize(url) ⇒ DbStorage

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



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

def initialize(url)
    super
end

Class Attribute Details

.fixed_length_types ⇒ Array (readonly)

Types for which we can safely ignore length in conversions

Returns:

  • (Array) 


38
39
40
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 38

def fixed_length_types
  @fixed_length_types
end

.reserved_keywords ⇒ Array (readonly)

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

Returns:

  • (Array) 


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

def reserved_keywords
  @reserved_keywords
end

.safe_conversions ⇒ Hash (readonly)

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

Returns:

  • (Hash) 


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

def safe_conversions
  @safe_conversions
end

.type_synonyms ⇒ Hash (readonly)

An Hash of DB type equivalents.

Returns:

  • (Hash) 


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

def type_synonyms
  @type_synonyms
end

Class Method Details

.inherited(subclass) ⇒ Object 



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

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)
    subclass.instance_variable_set("@fixed_length_types", @fixed_length_types)
    super
end

.storage_type ⇒ Symbol

Returns A label for the storage’s class.

Returns:

  • (Symbol) —

    A label for the storage’s class.



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

def storage_type
    :db
end

Instance Method Details

#alter_table(alter) ⇒ Object

Executes an alter table structured description.



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

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

#change_field(table_name, field_name, new_field_name, type, attributes) ⇒ Object 



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

def change_field(table_name, field_name, new_field_name, type, attributes)
    sqls = sql_change_field(table_name, field_name, new_field_name, type, attributes)
    sqls.each{ |sql| execute(sql) }

end

#collect_having_fields(condition) ⇒ Object 



806
807
808
809
810
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 806

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

#column_attributes(type, attributes) ⇒ Object

Returns the attributes corresponding to element type and attributes



146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 146

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.



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

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.



124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 124

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_index(table_name, field_name, attribute) ⇒ Object

Executes a create index structured description.



596
597
598
599
600
601
602
603
604
605
606
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 596

def create_index(table_name, field_name, attribute)
    sqls = sql_add_index(table_name, field_name, attribute)
    begin
        sqls.each{ |sql| execute(sql) }
    rescue StorageException => exc
        Spider.logger.error "Duplicate Key during create unique index into table #{table_name} and referred to column #{field_name}"
    rescue => exc
        drop_index(table_name, field_name)
        retry
    end
end

#create_table(create) ⇒ Object

Executes a create table structured description.



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

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.



758
759
760
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 758

def describe_table(table)
    raise "Unimplemented"
end

#drop_field(table_name, field_name) ⇒ Object

Drops a field from the DB.



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

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

#drop_index(table_name, field_name) ⇒ Object

Drop the index from DB.



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

def drop_index(table_name, field_name)
    sqls = sql_drop_index(table_name, field_name)
    begin
        sqls.each{ |sql| execute(sql) }
    rescue => exc
    end
end

#drop_table(table_name) ⇒ Object

Drops a table from the DB.



630
631
632
633
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 630

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

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



767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 767

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 



118
119
120
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 118

def foreign_key_name(name)
    name
end

#function(func) ⇒ Object

Returns the SQL for a QueryFuncs::Function

Raises:

  • (NotImplementedError) 


170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 170

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

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



76
77
78
79
80
81
82
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 76

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.



753
754
755
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 753

def list_tables
    raise "Unimplemented"
end

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



85
86
87
88
89
90
91
92
93
94
95
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 85

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.



763
764
765
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 763

def parse_db_column(col)
    col
end

#prepare_value(type, value) ⇒ Object

Prepares a value that will be used on the DB.



228
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/spiderfw/model/storage/db/db_storage.rb', line 228

def prepare_value(type, value)
    case type.name
    when 'String', 'Spider::DataTypes::Text'
        enc = @configuration['encoding']
        if (enc && enc.downcase != 'utf-8')
            if RUBY_VERSION =~ /1.8/
                begin
                    value = Iconv.conv(enc+'//IGNORE//TRANSLIT', 'utf-8', value.to_s+' ')[0..-2]
                rescue Iconv::InvalidCharacter
                    value = ''
                end
            else
                begin
                    value = (value.to_s).encode(enc, 'UTF-8', :invalid => :replace, :undef => :replace) if value  
                rescue EncodingError
                    value = ''
                end
            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).



254
255
256
257
258
259
260
261
262
263
264
265
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 254

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 



59
60
61
62
63
64
65
66
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 59

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 



55
56
57
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 55

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

#reflect_column(table, column_name, column_attributes) ⇒ Object 



817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 817

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) 


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

def safe_schema_conversion?(current, field)
    attributes = field[:attributes].clone
    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
    cur = current
    return true if self.class.fixed_length_types.include?(current[:type])
    return true if ((!cur[:length] || cur[:length] == 0) \
                    || (attributes[:length] && current[:length] <= attributes[:length])) && \
                   ((!cur[:precision] || cur[: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) 


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

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))
    unless self.class.fixed_length_types.include?(field[:type])
        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]
    end
    return true
end

#sequence_name(name) ⇒ Object

Fixes a string to be used as a sequence name.



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

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

#shorten_identifier(name, length) ⇒ Object

Shortens a DB name up to length.



734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 734

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.



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

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

#sql_add_index(table_name, field, attribute) ⇒ Object 



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

def sql_add_index(table_name, field, attribute)
    ["CREATE #{(attribute.to_s.upcase == 'UNIQUE' ? attribute.to_s.upcase : '')} INDEX #{field}_idx ON #{table_name} (#{field})"]
end

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

Returns an array of SQL statements to alter a field.



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

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.



539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 539

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.unshift sql_drop_primary_key(table_name) if (current[:primary_keys] && !current[:primary_keys].empty? && current[:primary_keys] != alter_attributes[:primary_keys])
        # unshift avoids problems with a field being created with primary key before the drop
        sql_pk = sql_create_primary_key(table_name, alter_attributes[:primary_keys])
        sqls << sql_pk if sql_pk
    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_change_field(table_name, field, new_field, type, attributes) ⇒ Object 



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

def sql_change_field(table_name, field, new_field, type, attributes)
    ["ALTER TABLE #{table_name} CHANGE #{field} #{sql_table_field(new_field, type, attributes)}"]
end

#sql_condition(query, having = false) ⇒ Object

Returns SQL and bound variables for a condition.



339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 339

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

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

Returns the SQL for a condition comparison.



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
403
404
405
406
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 376

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 (comp.to_s.downcase == 'nlike')
        comp = 'not 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 



643
644
645
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 643

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.



520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 520

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.



506
507
508
509
510
511
512
513
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 506

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.



675
676
677
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 675

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 



639
640
641
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 639

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

#sql_drop_index(table_name, field) ⇒ Object 



692
693
694
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 692

def sql_drop_index(table_name, field)
    ["DROP INDEX #{field}_idx ON #{table_name}"]
end

#sql_drop_primary_key(table_name) ⇒ Object 



635
636
637
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 635

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.



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

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

#sql_insert(insert) ⇒ Object

Returns SQL and values for an insert statement.



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

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.



422
423
424
425
426
427
428
429
430
431
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 422

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.



299
300
301
302
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 299

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

#sql_limit(query) ⇒ Object

Returns the LIMIT and OFFSET SQL.



463
464
465
466
467
468
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 463

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 #



793
794
795
796
797
798
799
800
801
802
803
804
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 793

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.



451
452
453
454
455
456
457
458
459
460
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 451

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 = ofield.name if ofield.is_a?(FieldExpression)
        "#{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.



268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 268

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 if vals
    sql += "WHERE #{where} " if where && !where.empty?
    having, having_vals = sql_condition(query, true)
    unless having.blank? && query[:group_by].blank?
        group_fields = query[:group_by] || (
            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} " unless having.blank?
        bind_vars += having_vals if 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

#sql_table_field(name, type, attributes) ⇒ Object

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



648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 648

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.



306
307
308
309
310
311
312
313
314
315
316
317
318
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 306

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.



321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 321

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 



515
516
517
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 515

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

#sql_update(update, allow_all = false) ⇒ Object

Returns SQL and values for an update statement.



479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 479

def sql_update(update, allow_all=false)
    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
    raise "Update without conditions" if where.blank? && !allow_all
    sql += " WHERE #{where}" unless where.blank?
    return [sql, values]
end

#sql_update_values(update) ⇒ Object

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



499
500
501
502
503
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 499

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.



102
103
104
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 102

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

#total_rows ⇒ Object 



294
295
296
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 294

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.



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

def value_to_mapper(type, value)
    if (type.name == 'String' || type.name == 'Spider::DataTypes::Text')
        enc = @configuration['encoding']
        if (enc && enc.downcase != 'utf-8')
            if RUBY_VERSION =~ /1.8/
                begin
                    value = Iconv.conv('utf-8//IGNORE//TRANSLIT', enc, value.to_s+' ')[0..-2] if value
                rescue Iconv::InvalidCharacter
                    value = ''
                end
            else
                begin
                    value = (value.to_s).encode('UTF-8', enc, :invalid => :replace, :undef => :replace) if value  
                rescue EncodingError
                    value = ''
                end
            end
        end
    end
    return value
end