Module: Timet::DatabaseSyncer

Included in:
DatabaseSyncHelper
Defined in:
lib/timet/database_syncer.rb

Overview

Module responsible for synchronizing local and remote databases

Constant Summary collapse

ITEM_FIELDS =

Fields used in item operations

%w[start end tag notes pomodoro updated_at created_at deleted].freeze

Instance Method Summary collapse

Instance Method Details

#format_status_message(id, item, source) ⇒ String

Formats item status message

Parameters:

  • id (Integer) —

    Item ID

  • item (Hash) —

    Database item

  • source (String) —

    Source of the item (‘Remote’ or ‘Local’)

Returns:

  • (String) —

    Formatted status message



185
186
187
188
 
# File 'lib/timet/database_syncer.rb', line 185

def format_status_message(id, item, source)
  deleted = item['deleted'].to_i == 1 ? ' and deleted' : ''
  "#{source} item #{id} is newer#{deleted} - #{source == 'Remote' ? 'updating local' : 'will be uploaded'}"
end

#get_item_values(item, include_id_at_start: false) ⇒ Array

Gets the values array for database operations

Parameters:

  • item (Hash) —

    Hash containing item data

  • include_id (Boolean) —

    Whether to include ID at start (insert) or end (update)

Returns:

  • (Array) —

    Array of values for database operation



208
209
210
211
212
 
# File 'lib/timet/database_syncer.rb', line 208

def get_item_values(item, include_id_at_start: false)
  @database_fields ||= ITEM_FIELDS
  values = @database_fields.map { |field| item[field] }
  include_id_at_start ? [item['id'], *values] : values
end

#handle_database_differences(*args) ⇒ void

Note:

This method attempts to sync the databases and handles any errors that occur during the process

This method returns an undefined value.

Handles the synchronization process when differences are detected between databases

Parameters:

  • local_db (SQLite3::Database) —

    The local database connection

  • remote_storage (S3Supabase) —

    The remote storage client for cloud operations

  • bucket (String) —

    The S3 bucket name

  • local_db_path (String) —

    Path to the local database file

  • remote_path (String) —

    Path to the downloaded remote database file



18
19
20
21
22
23
24
25
26
 
# File 'lib/timet/database_syncer.rb', line 18

def handle_database_differences(*args)
  local_db, remote_storage, bucket, local_db_path, remote_path = args
  puts 'Differences detected between local and remote databases'
  begin
    sync_with_remote_database(local_db, remote_path, remote_storage, bucket, local_db_path)
  rescue SQLite3::Exception => e
    handle_sync_error(e, remote_storage, bucket, local_db_path)
  end
end

#handle_sync_error(error, remote_storage, bucket, local_db_path) ⇒ void

Note:

When sync fails, this method falls back to uploading the local database

This method returns an undefined value.

Handles errors that occur during database synchronization

Parameters:

  • error (SQLite3::Exception) —

    The error that occurred during sync

  • remote_storage (S3Supabase) —

    The remote storage client for cloud operations

  • bucket (String) —

    The S3 bucket name

  • local_db_path (String) —

    Path to the local database file



36
37
38
39
40
 
# File 'lib/timet/database_syncer.rb', line 36

def handle_sync_error(error, remote_storage, bucket, local_db_path)
  puts "Error opening remote database: #{error.message}"
  puts 'Uploading local database to replace corrupted remote database'
  remote_storage.upload_file(bucket, local_db_path, 'timet.db')
end

#insert_item_from_hash(db, item) ⇒ void

This method returns an undefined value.

Inserts a new item into the database from a hash

Parameters:

  • db (SQLite3::Database) —

    The database connection

  • item (Hash) —

    Hash containing item data



130
131
132
133
134
135
136
137
 
# File 'lib/timet/database_syncer.rb', line 130

def insert_item_from_hash(db, item)
  fields = ['id', *ITEM_FIELDS].join(', ')
  placeholders = Array.new(ITEM_FIELDS.length + 1, '?').join(', ')
  db.execute_sql(
    "INSERT INTO items (#{fields}) VALUES (#{placeholders})",
    get_item_values(item, include_id_at_start: true)
  )
end

#items_to_hash(items) ⇒ Hash

Converts database items to a hash indexed by ID

Parameters:

  • items (Array<Hash>) —

    Array of database items

Returns:

  • (Hash) —

    Items indexed by ID



165
166
167
 
# File 'lib/timet/database_syncer.rb', line 165

def items_to_hash(items)
  items.to_h { |item| [item['id'], item] }
end

#open_remote_database(remote_path) ⇒ SQLite3::Database

Note:

Validates that the database connection is properly initialized

Opens and validates a connection to the remote database

Parameters:

  • remote_path (String) —

    Path to the remote database file

Returns:

  • (SQLite3::Database) —

    The initialized database connection

Raises:

  • (RuntimeError) —

    If the database connection cannot be established



65
66
67
68
69
70
 
# File 'lib/timet/database_syncer.rb', line 65

def open_remote_database(remote_path)
  db_remote = SQLite3::Database.new(remote_path)
  raise 'Failed to initialize remote database' unless db_remote

  db_remote
end

#process_database_items(local_db, remote_db) ⇒ void

This method returns an undefined value.

Processes items from both databases and syncs them

Parameters:

  • local_db (SQLite3::Database) —

    The local database connection

  • remote_db (SQLite3::Database) —

    The remote database connection



93
94
95
96
97
98
99
100
101
102
 
# File 'lib/timet/database_syncer.rb', line 93

def process_database_items(local_db, remote_db)
  remote_items = remote_db.execute('SELECT * FROM items ORDER BY updated_at DESC')
  local_items = local_db.execute_sql('SELECT * FROM items ORDER BY updated_at DESC')

  sync_items_by_id(
    local_db,
    items_to_hash(local_items),
    items_to_hash(remote_items)
  )
end

#process_existing_item(*args) ⇒ Symbol

Processes an item that exists in both databases

Parameters:

  • id (Integer) —

    Item ID

  • local_item (Hash) —

    Local database item

  • remote_item (Hash) —

    Remote database item

  • local_db (SQLite3::Database) —

    Local database connection

Returns:

  • (Symbol) —

    :local_update if local was updated, :remote_update if remote needs update



146
147
148
149
150
151
152
153
154
155
156
157
158
159
 
# File 'lib/timet/database_syncer.rb', line 146

def process_existing_item(*args)
  id, local_item, remote_item, local_db = args
  local_time = local_item['updated_at'].to_i
  remote_time = remote_item['updated_at'].to_i

  if remote_wins?(remote_item, remote_time, local_time)
    puts format_status_message(id, remote_item, 'Remote')
    update_item_from_hash(local_db, remote_item)
    :local_update
  elsif local_time > remote_time
    puts format_status_message(id, local_item, 'Local')
    :remote_update
  end
end

#remote_wins?(remote_item, remote_time, local_time) ⇒ Boolean

Determines if remote item should take precedence

Parameters:

  • remote_item (Hash) —

    Remote database item

  • remote_time (Integer) —

    Remote item timestamp

  • local_time (Integer) —

    Local item timestamp

Returns:

  • (Boolean) —

    true if remote item should take precedence



175
176
177
 
# File 'lib/timet/database_syncer.rb', line 175

def remote_wins?(remote_item, remote_time, local_time)
  remote_time > local_time && (remote_item['deleted'].to_i == 1 || remote_time > local_time)
end

#sync_databases(*args) ⇒ void

Note:

This method orchestrates the entire database synchronization process

This method returns an undefined value.

Synchronizes the local and remote databases by comparing and merging their items

Parameters:

  • local_db (SQLite3::Database) —

    The local database connection

  • remote_db (SQLite3::Database) —

    The remote database connection

  • remote_storage (S3Supabase) —

    The remote storage client for cloud operations

  • bucket (String) —

    The S3 bucket name

  • local_db_path (String) —

    Path to the local database file



81
82
83
84
85
86
 
# File 'lib/timet/database_syncer.rb', line 81

def sync_databases(*args)
  local_db, remote_db, remote_storage, bucket, local_db_path = args
  process_database_items(local_db, remote_db)
  remote_storage.upload_file(bucket, local_db_path, 'timet.db')
  puts 'Database sync completed'
end

#sync_items_by_id(local_db, local_items_by_id, remote_items_by_id) ⇒ void

This method returns an undefined value.

Syncs items between local and remote databases based on their IDs

Parameters:

  • local_db (SQLite3::Database) —

    The local database connection

  • local_items_by_id (Hash) —

    Local items indexed by ID

  • remote_items_by_id (Hash) —

    Remote items indexed by ID



110
111
112
113
114
115
116
117
118
119
120
121
122
123
 
# File 'lib/timet/database_syncer.rb', line 110

def sync_items_by_id(local_db, local_items_by_id, remote_items_by_id)
  all_item_ids = (remote_items_by_id.keys + local_items_by_id.keys).uniq

  all_item_ids.each do |id|
    if !remote_items_by_id[id]
      puts "Local item #{id} will be uploaded"
    elsif !local_items_by_id[id]
      puts "Adding remote item #{id} to local"
      insert_item_from_hash(local_db, remote_items_by_id[id])
    else
      process_existing_item(id, local_items_by_id[id], remote_items_by_id[id], local_db)
    end
  end
end

#sync_with_remote_database(*args) ⇒ void

Note:

Configures both databases to return results as hashes for consistent data handling

This method returns an undefined value.

Performs the actual database synchronization by setting up connections and syncing data

Parameters:

  • local_db (SQLite3::Database) —

    The local database connection

  • remote_path (String) —

    Path to the remote database file

  • remote_storage (S3Supabase) —

    The remote storage client for cloud operations

  • bucket (String) —

    The S3 bucket name

  • local_db_path (String) —

    Path to the local database file



51
52
53
54
55
56
57
 
# File 'lib/timet/database_syncer.rb', line 51

def sync_with_remote_database(*args)
  local_db, remote_path, remote_storage, bucket, local_db_path = args
  db_remote = open_remote_database(remote_path)
  db_remote.results_as_hash = true
  local_db.instance_variable_get(:@db).results_as_hash = true
  sync_databases(local_db, db_remote, remote_storage, bucket, local_db_path)
end

#update_item_from_hash(db, item) ⇒ void

This method returns an undefined value.

Updates an existing item in the database with values from a hash

Parameters:

  • db (SQLite3::Database) —

    The database connection

  • item (Hash) —

    Hash containing item data



195
196
197
198
199
200
201
 
# File 'lib/timet/database_syncer.rb', line 195

def update_item_from_hash(db, item)
  fields = "#{ITEM_FIELDS.join(' = ?, ')} = ?"
  db.execute_sql(
    "UPDATE items SET #{fields} WHERE id = ?",
    get_item_values(item)
  )
end