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

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



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

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



75
76
77
78
79
80
81
82
83
84
85
86
 
# 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)

  result = execute_sql("SELECT count(*) FROM pragma_table_info('items') where name=?", [new_column_name])
  column_exists = result[0][0].positive?
  return if column_exists

  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



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

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



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

def close
  @db&.close
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)



139
140
141
142
 
# File 'lib/timet/database.rb', line 139

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

#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



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

def determine_status(result)
  return :no_items if result.nil?

  last_item_end = result[2]
  return :in_progress unless last_item_end

  :complete
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])



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

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



152
153
154
155
 
# File 'lib/timet/database.rb', line 152

def fetch_last_id
  result = execute_sql('SELECT id FROM items WHERE deleted IS NULL OR deleted = 0 ORDER BY id DESC LIMIT 1')
  result.empty? ? nil : result[0][0]
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]



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

def find_item(id)
  result = execute_sql('SELECT * FROM items WHERE id = ?', [id])
  result.first.dup if result.any? # Add .dup to create a copy
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')



101
102
103
104
105
106
 
# File 'lib/timet/database.rb', line 101

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

See Also:

  • StatusHelper#determine_status


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

def item_status(id = nil)
  id = fetch_last_id if id.nil?
  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



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

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

#move_old_database_file(database_path) ⇒ Object

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



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

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

  FileUtils.mkdir_p(File.dirname(database_path)) unless File.directory?(File.dirname(database_path))
  FileUtils.mv(old_file, database_path)
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'



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

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



121
122
123
124
125
126
 
# File 'lib/timet/database.rb', line 121

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.



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

def update_time_columns
  result = execute_sql('SELECT * FROM items WHERE updated_at IS NULL OR created_at IS NULL')
  result.each do |item|
    id = item[0]
    start_time = item[1]
    end_time = item[2]
    fallback_time = end_time || start_time || Time.now.to_i
    execute_sql('UPDATE items SET updated_at = ?, created_at = ? WHERE id = ?', [fallback_time, fallback_time, id])
  end
end