Class: Spider::Model::Storage::Db::SQLite

Inherits:
DbStorage show all
Defined in:
lib/spiderfw/model/storage/db/adapters/sqlite.rb

Class Attribute Summary collapse

Attributes inherited from BaseStorage

#instance_name, #url

Class Method Summary collapse

Instance Method Summary collapse

Methods inherited from DbStorage

#alter_table, #column_attributes, #column_name, #column_type, #create_table, #drop_field, #drop_table, #dump, #foreign_key_name, #function, #get_mapper, inherited, #initialize, #lock, #parse_db_column, #prepare_value, #query, #query_finished, #query_start, #reflect_column, #safe_schema_conversion?, #schema_field_equal?, #sequence_name, #shorten_identifier, #sql_add_field, #sql_alter_field, #sql_alter_table, #sql_condition, #sql_condition_value, #sql_create_primary_key, #sql_create_table, #sql_delete, #sql_drop_field, #sql_drop_foreign_key, #sql_drop_primary_key, #sql_drop_table, #sql_insert, #sql_joins, #sql_keys, #sql_limit, #sql_max, #sql_order, #sql_select, #sql_table_field, #sql_tables, #sql_tables_join, #sql_truncate, #sql_update, #sql_update_values, storage_type, #table_name, #value_to_mapper

Methods inherited from BaseStorage

#==, #commit, #commit!, #commit_or_continue, #configure, #connect, #connected?, #connection, connection_attributes, #connection_attributes, #connection_pool, connection_pools, #create_sequence, #curr, disconnect, #generate_uuid, get_connection, #get_mapper, #in_transaction, inherited, #initialize, #prepare_value, release_connection, remove_connection, #rollback, #rollback!, #rollback_savepoint, #savepoint, #sequence_exists?, #sequence_file_path, #sequence_next, sequence_sync, #start_transaction, storage_type, #supports?, supports?, #supports_transactions?, #transactions_enabled?, #update_sequence, #value_for_condition, #value_to_mapper

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

This class inherits a constructor from Spider::Model::Storage::Db::DbStorage

Class Attribute Details

.reserved_kewords ⇒ Object (readonly)

Returns the value of attribute reserved_kewords.



15
16
17
 
# File 'lib/spiderfw/model/storage/db/adapters/sqlite.rb', line 15

def reserved_kewords
  @reserved_kewords
end

Class Method Details

.base_types ⇒ Object 



21
22
23
 
# File 'lib/spiderfw/model/storage/db/adapters/sqlite.rb', line 21

def self.base_types
    super << Spider::DataTypes::Binary
end

.connection_alive?(conn) ⇒ Boolean

Returns:

  • (Boolean) 


31
32
33
 
# File 'lib/spiderfw/model/storage/db/adapters/sqlite.rb', line 31

def self.connection_alive?(conn)
    !conn.closed?
end

.max_connections ⇒ Object 



17
18
19
 
# File 'lib/spiderfw/model/storage/db/adapters/sqlite.rb', line 17

def self.max_connections
    1
end

.new_connection(file) ⇒ Object 



25
26
27
28
29
 
# File 'lib/spiderfw/model/storage/db/adapters/sqlite.rb', line 25

def self.new_connection(file)
    db = SQLite3::Database.new(file)
    db.results_as_hash = true
    return db
end

Instance Method Details

#assigned_key(key) ⇒ Object 



77
78
79
 
# File 'lib/spiderfw/model/storage/db/adapters/sqlite.rb', line 77

def assigned_key(key)
    curr[:last_insert_row_id]
end

#describe_table(table) ⇒ Object 



144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
 
# File 'lib/spiderfw/model/storage/db/adapters/sqlite.rb', line 144

def describe_table(table)
    columns = {}
    primary_keys = []
    res = execute("PRAGMA table_info('#{table}')")
    res.each do |row|
        name = row['name']
        type = row['type']
        length = nil
        precision = nil
        if type =~ /(.+)\((.+)\)/
            type = $1
            length = $2
        end
        if length && length.include?(",")
            length, precision = length.split(',')
        end
        length = length.to_i if length
        precision = precision.to_i if length
        primary_keys << name if row['pk'] == "1"
        columns[name] = {:type => type, :length => length, :precision => precision}
    end
    # stmt.columns.each_index do |index|
    #     field = stmt.columns[index]
    #     columns[field] ||= {}
    #     if (stmt.types[index] =~ /([^\(]+)(?:\((\d+)\))?/)
    #         columns[field][:type] = $1
    #         columns[field][:length] = $2.to_i if $2
    #     end
    # end
    # stmt.close
    return {:columns => columns, :primary_keys => primary_keys}
end

#do_commit ⇒ Object 



65
66
67
68
69
 
# File 'lib/spiderfw/model/storage/db/adapters/sqlite.rb', line 65

def do_commit
    return release unless transactions_enabled?
    curr[:conn].commit if curr[:conn]
    release
end

#do_rollback ⇒ Object 



71
72
73
74
75
 
# File 'lib/spiderfw/model/storage/db/adapters/sqlite.rb', line 71

def do_rollback
    return release unless transactions_enabled?
    curr[:conn].rollback
    release
end

#do_start_transaction ⇒ Object 



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

def do_start_transaction
    return unless transactions_enabled?
    connection.transaction
end

#execute(sql, *bind_vars) ⇒ Object 



89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
 
# File 'lib/spiderfw/model/storage/db/adapters/sqlite.rb', line 89

def execute(sql, *bind_vars)
    begin
        if (bind_vars && bind_vars.length > 0)
            debug_vars = bind_vars.map{|var| var = var.to_s; var && var.length > 50 ? var[0..50]+"...(#{var.length-50} chars more)" : var}.join(', ')
        end
        debug("sqlite executing:\n#{sql}\n[#{debug_vars}]")

        result = connection.execute(sql, *bind_vars)
        curr[:last_insert_row_id] = connection.last_insert_row_id
        result.extend(StorageResult)
        curr[:last_result] = result
        if block_given?
            result.each{ |row| yield row }
        else
            return result
        end
    ensure
        release unless in_transaction?
    end
end

#execute_statement(stmt, *bind_vars) ⇒ Object 



116
117
118
 
# File 'lib/spiderfw/model/storage/db/adapters/sqlite.rb', line 116

def execute_statement(stmt, *bind_vars)
    stmt.execute(bind_vars)
end

#in_transaction? ⇒ Boolean

Returns:

  • (Boolean) 


60
61
62
63
 
# File 'lib/spiderfw/model/storage/db/adapters/sqlite.rb', line 60

def in_transaction?
    return false unless transactions_enabled?
    return curr[:conn] && curr[:conn].transaction_active?
end

#list_tables ⇒ Object

Methods to get information from the db #



140
141
142
 
# File 'lib/spiderfw/model/storage/db/adapters/sqlite.rb', line 140

def list_tables
    return execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name").flatten
end

#parse_url(url) ⇒ Object 



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

def parse_url(url)
    if (url =~ /(.+?):\/\/(.+)/)
        @file = $2
        @file = Spider.paths[:root] + '/' + @file[2..-1] if (@file[0..1] == './')
    else
        raise ArgumentError, "SQLite url '#{url}' is invalid"
    end
    @connection_params = [@file]
end

#prepare(sql) ⇒ Object 



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

def prepare(sql)
    debug("sqlite preparing: #{sql}")
    return connection.prepare(sql)
end

#release ⇒ Object 



35
36
37
38
39
40
41
42
 
# File 'lib/spiderfw/model/storage/db/adapters/sqlite.rb', line 35

def release
    begin
        #curr[:conn].close
        super
    rescue
        curr[:conn] = nil
    end
end

#table_exists?(table) ⇒ Boolean

Returns:

  • (Boolean) 


177
178
179
180
181
182
183
184
185
 
# File 'lib/spiderfw/model/storage/db/adapters/sqlite.rb', line 177

def table_exists?(table)
    begin
        stmt = prepare("select * from #{table}")
        stmt.close
        return true
    rescue SQLite3::SQLException
        return false
    end
end

#total_rows ⇒ Object 



120
121
122
123
124
125
126
127
128
129
130
 
# File 'lib/spiderfw/model/storage/db/adapters/sqlite.rb', line 120

def total_rows
    return nil unless curr[:last_query]
    q = curr[:last_query]
    unless (q[:offset] || q[:limit])
        return curr[:last_result] ? curr[:last_result].length : nil
    end
    q[:offset] = q[:limit] = nil
    q[:keys] = ["COUNT(*) AS N"]
    res = execute(sql_select(q), q[:bind_vars])
    return res[0]['N']
end

#value_for_save(type, value, save_mode) ⇒ Object 



81
82
83
84
85
86
87
 
# File 'lib/spiderfw/model/storage/db/adapters/sqlite.rb', line 81

def value_for_save(type, value, save_mode)
    case type.name
    when 'Spider::DataTypes::Binary'
        return SQLite3::Blob.new(value)
    end
    return value
end