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



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

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 



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

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 



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

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

Executes a create table structured description.



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

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.



709
710
711
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 709

def describe_table(table)
    raise "Unimplemented"
end

#drop_field(table_name, field_name) ⇒ Object

Drops a field from the DB.



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

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.



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

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

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



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

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.



704
705
706
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 704

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.



714
715
716
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 714

def parse_db_column(col)
    col
end

#prepare_value(type, value) ⇒ Object

Prepares a value that will be used on the DB.



220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 220

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



238
239
240
241
242
243
244
245
246
247
248
249
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 238

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 



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

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) 


665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 665

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) 


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

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.



685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 685

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.



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

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.



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

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.



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
547
548
549
550
551
552
553
554
555
556
557
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 519

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 



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

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.



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
351
352
353
354
355
356
357
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 323

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.



360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 360

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

#sql_create_primary_key(table_name, fields) ⇒ Object 



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

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.



500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 500

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.



486
487
488
489
490
491
492
493
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 486

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.



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

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 



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

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 



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

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.



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

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

#sql_insert(insert) ⇒ Object

Returns SQL and values for an insert statement.



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

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.



402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 402

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.



283
284
285
286
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 283

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.



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

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 #



744
745
746
747
748
749
750
751
752
753
754
755
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 744

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.



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

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.



252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 252

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)



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

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.



290
291
292
293
294
295
296
297
298
299
300
301
302
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 290

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.



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

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 



495
496
497
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 495

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

#sql_update(update, allow_all = false) ⇒ Object

Returns SQL and values for an update statement.



459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
 
# File 'lib/spiderfw/model/storage/db/db_storage.rb', line 459

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.



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

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 



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

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
 
# 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')
            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