Class: Timet::Database

Inherits:
Object
  • Object
show all
Defined in:
lib/timet/database.rb

Overview

Provides database access for managing time tracking data.

Constant Summary collapse

DEFAULT_DATABASE_PATH =

The default path to the SQLite database file.

File.join(Dir.home, '.timet', 'timet.db')

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(database_path = DEFAULT_DATABASE_PATH) ⇒ void

Note:

The method creates a new SQLite3 database connection and initializes the necessary tables if they

Initializes a new instance of the Database class.

connection and creating the necessary tables.

do not already exist.

Examples:

Initialize a new Database instance with the default path

Database.new

Initialize a new Database instance with a custom path

Database.new('/path/to/custom.db')

Parameters:

  • database_path (String) (defaults to: DEFAULT_DATABASE_PATH) —

    The path to the SQLite database file. Defaults to DEFAULT_DATABASE_PATH.



26
27
28
29
30
31
32
33
34
35
36
37
38
 
# File 'lib/timet/database.rb', line 26

def initialize(database_path = DEFAULT_DATABASE_PATH)
  self.class.move_old_database_file(database_path)

  @db = SQLite3::Database.new(database_path)
  create_table

  add_column('items', 'notes', 'TEXT')
  add_column('items', 'pomodoro', 'INTEGER')
  add_column('items', 'updated_at', 'INTEGER')
  add_column('items', 'created_at', 'INTEGER')
  add_column('items', 'deleted', 'INTEGER')
  update_time_columns
end

Class Method Details

.determine_status(result) ⇒ Symbol

Note:

The method checks if the result set is empty and returns :no_items if true.

Note:

If the last item in the result set has no end time, it returns :in_progress.

Note:

If the last item in the result set has an end time, it returns :complete.

Determines the status of a time tracking result based on the presence and end time of items.

:no_items, :in_progress, or :complete.

Examples:

Determine the status of an empty result set

StatusHelper.determine_status([]) # => :no_items

Determine the status of a result set with an in-progress item

StatusHelper.determine_status([[1, nil]]) # => :in_progress

Determine the status of a result set with a completed item

StatusHelper.determine_status([[1, 1633072800]]) # => :complete

Parameters:

  • result (Array) —

    The result set containing time tracking items.

Returns:

  • (Symbol) —

    The status of the time tracking result. Possible values are



285
286
287
288
289
290
291
292
 
# File 'lib/timet/database.rb', line 285

def self.determine_status(result)
  return :no_items unless result

  last_item_end = result[2]
  return :in_progress unless last_item_end

  :complete
end

.move_old_database_file(database_path) ⇒ Object

Moves the old database file to the new location if it exists.

Parameters:

  • database_path (String) —

    The path to the new SQLite database file.



297
298
299
300
301
302
303
304
 
# File 'lib/timet/database.rb', line 297

def self.move_old_database_file(database_path)
  old_file = File.join(Dir.home, '.timet.db')
  return unless File.exist?(old_file)

  dir = File.dirname(database_path)
  FileUtils.mkdir_p(dir) unless File.directory?(dir)
  FileUtils.mv(old_file, database_path)
end

Instance Method Details

#add_column(table_name, new_column_name, date_type) ⇒ void

Note:

The method first checks if the column already exists in the table using pragma_table_info.

Note:

If the column exists, the method returns without making any changes.

Note:

If the column does not exist, the method executes an SQL ‘ALTER TABLE` statement to add the column.

Note:

The method prints a message indicating that the column has been added.

This method returns an undefined value.

Adds a new column to the specified table if it does not already exist.

printing a message.

Examples:

Add a new ‘completed’ column to the ‘tasks’ table

add_column('tasks', 'completed', 'INTEGER')

Parameters:

  • table_name (String) —

    The name of the table to which the column will be added.

  • new_column_name (String) —

    The name of the new column to be added.

  • date_type (String) —

    The data type of the new column (e.g., ‘INTEGER’, ‘TEXT’, ‘BOOLEAN’).



75
76
77
78
79
80
81
82
83
 
# File 'lib/timet/database.rb', line 75

def add_column(table_name, new_column_name, date_type)
  raise 'Invalid table name' unless table_name == 'items'
  raise 'Invalid column name' unless /\A[a-zA-Z0-9_]+\z/.match?(new_column_name)
  raise 'Invalid date type' unless %w[INTEGER TEXT BOOLEAN].include?(date_type)
  return if column_exists?(new_column_name)

  execute_sql("ALTER TABLE #{table_name} ADD COLUMN #{new_column_name} #{date_type}")
  puts "Column '#{new_column_name}' added to table '#{table_name}'."
end

#all_items ⇒ Array

Note:

The method executes SQL to fetch all items from the ‘items’ table that have a start time greater than

Fetches all items from the items table that have a start time greater than or equal to today.

or equal to today.

Examples:

Fetch all items from today

all_items

Returns:

  • (Array) —

    An array of items.



197
198
199
200
201
 
# File 'lib/timet/database.rb', line 197

def all_items
  today = TimeHelper.beginning_of_day.to_i
  execute_sql('SELECT * FROM items WHERE start >= ? AND (deleted IS NULL OR deleted = 0) ORDER BY start DESC',
              [today])
end

#close ⇒ void

Note:

The method closes the SQLite3 database connection.

This method returns an undefined value.

Closes the database connection.

database connection.

Examples:

Close the database connection

close



244
245
246
 
# File 'lib/timet/database.rb', line 244

def close
  @db&.close
end

#column_exists?(new_column_name) ⇒ Boolean

Returns:

  • (Boolean) 


85
86
87
88
 
# File 'lib/timet/database.rb', line 85

def column_exists?(new_column_name)
  execute_sql("SELECT count(*) FROM pragma_table_info('items') where name=?",
              [new_column_name]).first.first.positive?
end

#create_table ⇒ void

Note:

The method executes SQL to create the ‘items’ table with columns for id, start, end, and tag.

This method returns an undefined value.

Creates the items table if it doesn’t already exist.

create the table.

Examples:

Create the items table

create_table



49
50
51
52
53
54
55
56
57
58
 
# File 'lib/timet/database.rb', line 49

def create_table
  execute_sql("    CREATE TABLE IF NOT EXISTS items (\n      id INTEGER PRIMARY KEY AUTOINCREMENT,\n      start INTEGER,\n      end INTEGER,\n      tag TEXT\n    );\n  SQL\nend\n")

#delete_item(id) ⇒ void

Note:

The method executes SQL to delete the item with the given ID from the ‘items’ table.

This method returns an undefined value.

Deletes an item from the items table.

to delete the item.

Examples:

Delete an item with ID 1

delete_item(1)

Parameters:

  • id (Integer) —

    The ID of the item to be deleted.



141
142
143
144
 
# File 'lib/timet/database.rb', line 141

def delete_item(id)
  current_time = Time.now.to_i
  execute_sql('UPDATE items SET deleted = 1, updated_at = ? WHERE id = ?', [current_time, id])
end

#execute_sql(sql, params = []) ⇒ Array

Note:

The method executes the given SQL query with the provided parameters and returns the result.

Executes a SQL query and returns the result.

Examples:

Execute a SQL query

execute_sql('SELECT * FROM items WHERE id = ?', [1])

Parameters:

  • sql (String) —

    The SQL query to execute.

  • params (Array) (defaults to: []) —

    The parameters to bind to the SQL query.

Returns:

  • (Array) —

    The result of the SQL query.



231
232
233
 
# File 'lib/timet/database.rb', line 231

def execute_sql(sql, params = [])
  @db.execute(sql, params)
end

#fetch_last_id ⇒ Integer?

Note:

The method executes SQL to fetch the ID of the last inserted item.

Fetches the ID of the last inserted item.

Examples:

Fetch the last inserted item ID

fetch_last_id

Returns:

  • (Integer, nil) —

    The ID of the last inserted item, or nil if no items exist.



154
155
156
157
 
# File 'lib/timet/database.rb', line 154

def fetch_last_id
  execute_sql('SELECT id FROM items WHERE deleted IS NULL OR deleted = 0 ORDER BY id DESC LIMIT 1')
    .then { |result| result.empty? ? nil : result.first.first }
end

#find_item(id) ⇒ Array?

Note:

The method executes a SQL query to find the item by its ID.

Note:

If the item is found, it returns the item as an array.

Note:

If the item is not found, it returns nil.

Finds an item by its ID.

Examples:

Find an item with ID 1

find_item(1) # => [1, 1678886400, 1678890000, 'work', 'notes', nil, 1678890000, 1678886400, nil]

Parameters:

  • id (Integer) —

    The ID of the item to find.

Returns:

  • (Array, nil) —

    The item as an array if found, nil otherwise.



184
185
186
 
# File 'lib/timet/database.rb', line 184

def find_item(id)
  execute_sql('SELECT * FROM items WHERE id = ?', [id]).first&.dup
end

#insert_item(*args) ⇒ void

Note:

The method executes SQL to insert a new row into the ‘items’ table.

This method returns an undefined value.

Inserts a new item into the items table.

to insert the item.

Examples:

Insert a new item into the items table

insert_item(1633072800, 'work', 'Completed task X')

Parameters:

  • start (Integer) —

    The start time of the item.

  • tag (String) —

    The tag associated with the item.

  • notes (String) —

    The notes associated with the item.



103
104
105
106
107
108
 
# File 'lib/timet/database.rb', line 103

def insert_item(*args)
  # Unpacking args into meaningful variables for clarity
  start, tag, notes, pomodoro, updated_at, created_at = args
  execute_sql('INSERT INTO items (start, tag, notes, pomodoro, updated_at, created_at) VALUES (?, ?, ?, ?, ?, ?)',
              [start, tag, notes, pomodoro, updated_at, created_at])
end

#item_status(id = nil) ⇒ Symbol

Note:

The method executes SQL to fetch the last item and determines its status using the StatusHelper module.

Determines the status of the last item in the items table.

Examples:

Determine the status of the last item

item_status

Parameters:

  • id (Integer, nil) (defaults to: nil) —

    The ID of the item to check. If nil, the last item in the table is used.

Returns:

  • (Symbol) —

    The status of the last item. Possible values are :no_items, :in_progress, or :complete.

See Also:

  • StatusHelper#determine_status


215
216
217
218
 
# File 'lib/timet/database.rb', line 215

def item_status(id = nil)
  id ||= fetch_last_id
  self.class.determine_status(find_item(id))
end

#last_item ⇒ Array?

Note:

The method executes SQL to fetch the last item from the ‘items’ table.

Fetches the last item from the items table.

Examples:

Fetch the last item

last_item

Returns:

  • (Array, nil) —

    The last item as an array, or nil if no items exist.



167
168
169
170
 
# File 'lib/timet/database.rb', line 167

def last_item
  execute_sql('SELECT * FROM items WHERE deleted IS NULL OR deleted = 0 ORDER BY id DESC LIMIT 1')
    .then { |result| result.empty? ? nil : result.first }
end

#seconds_to_hms(seconds) ⇒ String

Note:

The method converts the given number of seconds into hours, minutes, and seconds, and formats

Converts a given number of seconds into a human-readable HH:MM:SS format.

them as HH:MM:SS.

Examples:

Convert 3661 seconds to HH:MM:SS format

seconds_to_hms(3661) # => '01:01:01'

Parameters:

  • seconds (Integer) —

    The number of seconds to convert.

Returns:

  • (String) —

    The formatted time in HH:MM:SS format.



259
260
261
262
263
264
 
# File 'lib/timet/database.rb', line 259

def seconds_to_hms(seconds)
  hours, remainder = seconds.divmod(3600)
  minutes, seconds = remainder.divmod(60)

  format '%<hours>02d:%<minutes>02d:%<seconds>02d', hours: hours, minutes: minutes, seconds: seconds
end

#update_item(id, field, value) ⇒ void

Note:

The method executes SQL to update the specified field of the item with the given ID.

This method returns an undefined value.

Updates an existing item in the items table.

to update the item.

Examples:

Update the tag of an item with ID 1

update_item(1, 'tag', 'updated_work')

Parameters:

  • id (Integer) —

    The ID of the item to be updated.

  • field (String) —

    The field to be updated.

  • value (String, Integer, nil) —

    The new value for the specified field.



123
124
125
126
127
128
 
# File 'lib/timet/database.rb', line 123

def update_item(id, field, value)
  allowed_fields = %w[tag notes start end deleted updated_at created_at]
  raise "Invalid field: #{field}" unless allowed_fields.include?(field)

  execute_sql("UPDATE items SET #{field} = ?, updated_at = ? WHERE id = ?", [value, Time.now.utc.to_i, id])
end

#update_time_columns ⇒ void

Note:

This method directly executes SQL queries on the database. Ensure that the execute_sql method is properly

This method returns an undefined value.

Updates the updated_at and created_at columns for items where either of these columns is null.

This method queries the database for items where the updated_at or created_at columns are null. For each item found, it sets both the updated_at and created_at columns to the value of the end_time column.

defined and handles SQL injection risks.

Examples:

update_time_columns

Raises:

  • (StandardError) —

    If there is an issue executing the SQL queries, an error may be raised.



321
322
323
324
 
# File 'lib/timet/database.rb', line 321

def update_time_columns
  execute_sql('SELECT * FROM items WHERE updated_at IS NULL OR created_at IS NULL')
    .each { |item| update_timestamp_for_item(item[0], item[2] || item[1] || Time.now.to_i) }
end

#update_timestamp_for_item(id, fallback_time) ⇒ Object 



326
327
328
 
# File 'lib/timet/database.rb', line 326

def update_timestamp_for_item(id, fallback_time)
  execute_sql('UPDATE items SET updated_at = ?, created_at = ? WHERE id = ?', [fallback_time, fallback_time, id])
end