Class: Dbox::Database

Inherits:
Object
  • Object
show all
Includes:
Loggable, Utils
Defined in:
lib/dbox/database.rb

Constant Summary collapse

DB_FILENAME = 
".dbox.sqlite3"
METADATA_COLS =

don’t need to return id

[ :remote_path, :version ]
ENTRY_COLS = 
[ :id, :path, :is_dir, :parent_id, :local_hash, :remote_hash, :modified, :revision ]

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods included from Utils

#calculate_hash, #case_insensitive_difference, #case_insensitive_equal, #case_insensitive_join, #case_insensitive_resolve, #find_nonconflicting_path, #local_to_relative_path, #parse_time, #relative_to_local_path, #relative_to_remote_path, #remote_to_relative_path, #time_to_s, #times_equal?

Methods included from Loggable

included, #log

Constructor Details

#initialize(local_path) ⇒ Database

Returns a new instance of Database.



44
45
46
47
48
49
50
51
52
 
# File 'lib/dbox/database.rb', line 44

def initialize(local_path)
  @local_path = local_path
  FileUtils.mkdir_p(local_path)
  @db = SQLite3::Database.new(File.join(local_path, DB_FILENAME))
  @db.busy_timeout(1000)
  @db.trace {|sql| log.debug sql.strip }
  @db.execute("PRAGMA foreign_keys = ON;")
  ensure_schema_exists
end

Instance Attribute Details

#local_path ⇒ Object (readonly)

Returns the value of attribute local_path.



39
40
41
 
# File 'lib/dbox/database.rb', line 39

def local_path
  @local_path
end

Class Method Details

.create(remote_path, local_path) ⇒ Object 



10
11
12
13
14
15
16
17
18
 
# File 'lib/dbox/database.rb', line 10

def self.create(remote_path, local_path)
  db = new(local_path)
  if db.bootstrapped?
    raise DatabaseError, "Database already initialized -- please use 'dbox pull' or 'dbox push'."
  end
  db.bootstrap(remote_path)
  db.migrate()
  db
end

.exists?(local_path) ⇒ Boolean

Returns:

  • (Boolean) 


29
30
31
 
# File 'lib/dbox/database.rb', line 29

def self.exists?(local_path)
  File.exists?(File.join(local_path, DB_FILENAME))
end

.load(local_path) ⇒ Object 



20
21
22
23
24
25
26
27
 
# File 'lib/dbox/database.rb', line 20

def self.load(local_path)
  db = new(local_path)
  unless db.bootstrapped?
    raise DatabaseError, "Database not initialized -- please run 'dbox create' or 'dbox clone'."
  end
  db.migrate()
  db
end

.migrate_from_old_db_format(old_db) ⇒ Object 



33
34
35
36
37
 
# File 'lib/dbox/database.rb', line 33

def self.migrate_from_old_db_format(old_db)
  new_db = create(old_db.remote_path, old_db.local_path)
  new_db.delete_entry_by_path("") # clear out root record
  new_db.migrate_entry_from_old_db_format(old_db.root)
end

Instance Method Details

#add_entry(path, is_dir, parent_id, modified, revision, remote_hash, local_hash) ⇒ Object 



290
291
292
 
# File 'lib/dbox/database.rb', line 290

def add_entry(path, is_dir, parent_id, modified, revision, remote_hash, local_hash)
  insert_entry(:path => path, :is_dir => is_dir, :parent_id => parent_id, :modified => modified, :revision => revision, :remote_hash => remote_hash, :local_hash => local_hash)
end

#bootstrap(remote_path) ⇒ Object 



234
235
236
237
238
239
240
241
 
# File 'lib/dbox/database.rb', line 234

def bootstrap(remote_path)
  @db.execute(%{
    INSERT INTO metadata (remote_path, version) VALUES (?, ?);
  }, remote_path, 5)
  @db.execute(%{
    INSERT INTO entries (path, is_dir) VALUES (?, ?)
  }, "", 1)
end

#bootstrapped? ⇒ Boolean

Returns:

  • (Boolean) 


243
244
245
246
247
248
 
# File 'lib/dbox/database.rb', line 243

def bootstrapped?
  n = @db.get_first_value(%{
    SELECT count(id) FROM metadata LIMIT 1;
  })
  n && n > 0
end

#contents(dir_id) ⇒ Object

Raises:

  • (ArgumentError) 


280
281
282
283
 
# File 'lib/dbox/database.rb', line 280

def contents(dir_id)
  raise(ArgumentError, "dir_id cannot be null") unless dir_id
  find_entries("WHERE parent_id=?", dir_id)
end

#delete_entry_by_entry(entry) ⇒ Object

Raises:

  • (ArgumentError) 


308
309
310
311
312
313
314
315
316
 
# File 'lib/dbox/database.rb', line 308

def delete_entry_by_entry(entry)
  raise(ArgumentError, "entry cannot be null") unless entry

  # cascade delete children, if any
  contents(entry[:id]).each {|child| delete_entry_by_entry(child) }

  # delete main entry
  delete_entry("WHERE id=?", entry[:id])
end

#delete_entry_by_path(path) ⇒ Object 



304
305
306
 
# File 'lib/dbox/database.rb', line 304

def delete_entry_by_path(path)
  delete_entry_by_entry(find_by_path(path))
end

#ensure_schema_exists ⇒ Object 



54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
 
# File 'lib/dbox/database.rb', line 54

def ensure_schema_exists
  @db.execute_batch(%{
    CREATE TABLE IF NOT EXISTS metadata (
      id           integer PRIMARY KEY AUTOINCREMENT NOT NULL,
      remote_path  text COLLATE NOCASE UNIQUE NOT NULL,
      version      integer NOT NULL
    );
    CREATE TABLE IF NOT EXISTS entries (
      id           integer PRIMARY KEY AUTOINCREMENT NOT NULL,
      path         text COLLATE NOCASE UNIQUE NOT NULL,
      is_dir       boolean NOT NULL,
      parent_id    integer REFERENCES entries(id) ON DELETE CASCADE,
      local_hash   text,
      remote_hash  text,
      modified     datetime,
      revision     text
    );
    CREATE INDEX IF NOT EXISTS entry_parent_ids ON entries(parent_id);
    CREATE INDEX IF NOT EXISTS entry_path ON entries(path);
  })
end

#find_by_path(path) ⇒ Object

Raises:

  • (ArgumentError) 


275
276
277
278
 
# File 'lib/dbox/database.rb', line 275

def find_by_path(path)
  raise(ArgumentError, "path cannot be null") unless path
  find_entry("WHERE path=?", path)
end

#metadata ⇒ Object 



250
251
252
253
254
255
256
257
258
 
# File 'lib/dbox/database.rb', line 250

def metadata
  cols = METADATA_COLS
  res = @db.get_first_row(%{
    SELECT #{cols.join(',')} FROM metadata LIMIT 1;
  })
  out = { :local_path => local_path }
  out.merge!(make_fields(cols, res)) if res
  out
end

#migrate ⇒ Object 



76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
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
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
 
# File 'lib/dbox/database.rb', line 76

def migrate
  # removing local_path from metadata
  if metadata[:version] < 2
    log.info "Migrating to database schema v2"

    @db.execute_batch(%{
      BEGIN TRANSACTION;
      ALTER TABLE metadata RENAME TO metadata_old;
      CREATE TABLE metadata (
        id           integer PRIMARY KEY AUTOINCREMENT NOT NULL,
        remote_path  text NOT NULL,
        version      integer NOT NULL
      );
      INSERT INTO metadata SELECT id, remote_path, version FROM metadata_old;
      DROP TABLE metadata_old;
      UPDATE metadata SET version = 2;
      COMMIT;
    })
  end

  # migrating to new Dropbox API 1.0 (from integer revisions to
  # string revisions)
  if metadata[:version] < 3
    log.info "Migrating to database schema v3"

    api = API.connect
    new_revisions = {}

    # fetch the new revision IDs from dropbox
    find_entries_with_columns([ :id, :path, :is_dir, :parent_id, :hash, :modified, :revision ]).each do |entry|
      path = relative_to_remote_path(entry[:path])
      begin
        data = api.metadata(path, nil, false)
        # record nev revision ("rev") iff old revisions ("revision") match
        if entry[:revision] == data["revision"]
          new_revisions[entry[:id]] = data["rev"]
        end
      rescue Dbox::ServerError => e
        log.error e
      end
    end

    # modify the table to have a string for revision (blanked out
    # for each entry)
    @db.execute_batch(%{
      BEGIN TRANSACTION;
      ALTER TABLE entries RENAME TO entries_old;
      CREATE TABLE entries (
        id           integer PRIMARY KEY AUTOINCREMENT NOT NULL,
        path         text UNIQUE NOT NULL,
        is_dir       boolean NOT NULL,
        parent_id    integer REFERENCES entries(id) ON DELETE CASCADE,
        hash         text,
        modified     datetime,
        revision     text
      );
      INSERT INTO entries SELECT id, path, is_dir, parent_id, hash, modified, null FROM entries_old;
    })

    # copy in the new revision IDs
    new_revisions.each do |id, revision|
      update_entry_by_id(id, :revision => revision)
    end

    # drop old table and commit
    @db.execute_batch(%{
      DROP TABLE entries_old;
      UPDATE metadata SET version = 3;
      COMMIT;
    })
  end

  if metadata[:version] < 4
    log.info "Migrating to database schema v4"

    # add local_hash column, rename hash to remote_hash
    @db.execute_batch(%{
      BEGIN TRANSACTION;
      ALTER TABLE entries RENAME TO entries_old;
      CREATE TABLE entries (
        id           integer PRIMARY KEY AUTOINCREMENT NOT NULL,
        path         text UNIQUE NOT NULL,
        is_dir       boolean NOT NULL,
        parent_id    integer REFERENCES entries(id) ON DELETE CASCADE,
        local_hash   text,
        remote_hash  text,
        modified     datetime,
        revision     text
      );
      INSERT INTO entries SELECT id, path, is_dir, parent_id, null, hash, modified, revision FROM entries_old;
    })

    # calculate hashes on files with same timestamp as we have (as that was the previous mechanism used to check freshness)
    find_entries_with_columns([ :id, :path, :is_dir, :parent_id, :local_hash, :remote_hash, :modified, :revision ]).each do |entry|
      unless entry[:is_dir]
        path = relative_to_local_path(entry[:path])
        if times_equal?(File.mtime(path), entry[:modified])
          update_entry_by_id(entry[:id], :local_hash => calculate_hash(path))
        end
      end
    end

    # drop old table and commit
    @db.execute_batch(%{
      DROP TABLE entries_old;
      UPDATE metadata SET version = 4;
      COMMIT;
    })
  end

  if metadata[:version] < 5
    log.info "Migrating to database schema v5"

    # make path be case insensitive
    @db.execute_batch(%{
      BEGIN TRANSACTION;

      -- migrate metadata table
      ALTER TABLE metadata RENAME TO metadata_old;
      CREATE TABLE IF NOT EXISTS metadata (
        id           integer PRIMARY KEY AUTOINCREMENT NOT NULL,
        remote_path  text COLLATE NOCASE UNIQUE NOT NULL,
        version      integer NOT NULL
      );
      INSERT INTO metadata SELECT id, remote_path, version FROM metadata_old;
      DROP TABLE metadata_old;

      -- migrate entries table
      ALTER TABLE entries RENAME TO entries_old;
      CREATE TABLE entries (
        id           integer PRIMARY KEY AUTOINCREMENT NOT NULL,
        path         text COLLATE NOCASE UNIQUE NOT NULL,
        is_dir       boolean NOT NULL,
        parent_id    integer REFERENCES entries(id) ON DELETE CASCADE,
        local_hash   text,
        remote_hash  text,
        modified     datetime,
        revision     text
      );
      INSERT INTO entries SELECT id, path, is_dir, parent_id, local_hash, remote_hash, modified, revision FROM entries_old;
      DROP TABLE entries_old;

      -- recreate indexes
      DROP INDEX IF EXISTS entry_parent_ids;
      DROP INDEX IF EXISTS entry_path;
      CREATE INDEX entry_parent_ids ON entries(parent_id);
      CREATE INDEX entry_path ON entries(path);

      -- update version
      UPDATE metadata SET version = 5;
      COMMIT;
    })
  end
end

#migrate_entry_from_old_db_format(entry, parent = nil) ⇒ Object 



318
319
320
321
322
323
324
325
326
327
 
# File 'lib/dbox/database.rb', line 318

def migrate_entry_from_old_db_format(entry, parent = nil)
  # insert entry into sqlite db
  add_entry(entry.path, entry.dir?, (parent ? parent[:id] : nil), entry.modified_at, entry.revision, nil, nil)

  # recur on children
  if entry.dir?
    new_parent = find_by_path(entry.path)
    entry.contents.each {|child_path, child| migrate_entry_from_old_db_format(child, new_parent) }
  end
end

#remote_path ⇒ Object 



260
261
262
 
# File 'lib/dbox/database.rb', line 260

def remote_path
  metadata()[:remote_path]
end

#root_dir ⇒ Object 



271
272
273
 
# File 'lib/dbox/database.rb', line 271

def root_dir
  find_entry("WHERE parent_id is NULL")
end

#subdirs(dir_id) ⇒ Object

Raises:

  • (ArgumentError) 


285
286
287
288
 
# File 'lib/dbox/database.rb', line 285

def subdirs(dir_id)
  raise(ArgumentError, "dir_id cannot be null") unless dir_id
  find_entries("WHERE parent_id=? AND is_dir=1", dir_id)
end

#update_entry_by_id(id, fields) ⇒ Object

Raises:

  • (ArgumentError) 


294
295
296
297
 
# File 'lib/dbox/database.rb', line 294

def update_entry_by_id(id, fields)
  raise(ArgumentError, "id cannot be null") unless id
  update_entry(["WHERE id=?", id], fields)
end

#update_entry_by_path(path, fields) ⇒ Object

Raises:

  • (ArgumentError) 


299
300
301
302
 
# File 'lib/dbox/database.rb', line 299

def update_entry_by_path(path, fields)
  raise(ArgumentError, "path cannot be null") unless path
  update_entry(["WHERE path=?", path], fields)
end

#update_metadata(fields) ⇒ Object 



264
265
266
267
268
269
 
# File 'lib/dbox/database.rb', line 264

def update_metadata(fields)
  set_str = fields.keys.map {|k| "#{k}=?" }.join(",")
  @db.execute(%{
    UPDATE metadata SET #{set_str};
  }, *fields.values)
end