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

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

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
 
# File 'lib/timet/database.rb', line 75

def add_column(table_name, new_column_name, 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

Returns:

  • (Array) —

    An array of items.



189
190
191
192
193
 
# File 'lib/timet/database.rb', line 189

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


238
239
240
 
# File 'lib/timet/database.rb', line 238

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(<<-SQL)
    CREATE TABLE IF NOT EXISTS items (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      start INTEGER,
      end INTEGER,
      tag TEXT
    );
  SQL
end

#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.



132
133
134
135
 
# File 'lib/timet/database.rb', line 132

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

Parameters:

  • result (Array) —

    The result set containing time tracking items.

Returns:

  • (Symbol) —

    The status of the time tracking result. Possible values are



279
280
281
282
283
284
285
286
 
# File 'lib/timet/database.rb', line 279

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

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.



222
223
224
225
226
227
 
# File 'lib/timet/database.rb', line 222

def execute_sql(sql, params = [])
  @db.execute(sql, params)
rescue SQLite3::SQLException => e
  puts "Error: #{e.message}"
  []
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.



145
146
147
148
 
# File 'lib/timet/database.rb', line 145

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]

Parameters:

  • id (Integer) —

    The ID of the item to find.

Returns:

  • (Array, nil) —

    The item as an array if found, nil otherwise.



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

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

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.



97
98
99
100
101
102
 
# File 'lib/timet/database.rb', line 97

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.



206
207
208
209
 
# File 'lib/timet/database.rb', line 206

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

Returns:

  • (Array, nil) —

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



158
159
160
161
 
# File 'lib/timet/database.rb', line 158

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.

Parameters:

  • database_path (String) —

    The path to the new SQLite database file.



291
292
293
294
295
296
297
 
# File 'lib/timet/database.rb', line 291

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'

Parameters:

  • seconds (Integer) —

    The number of seconds to convert.

Returns:

  • (String) —

    The formatted time in HH:MM:SS format.



253
254
255
256
257
258
 
# File 'lib/timet/database.rb', line 253

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.



117
118
119
 
# File 'lib/timet/database.rb', line 117

def update_item(id, field, value)
  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.



314
315
316
317
318
319
320
321
 
# File 'lib/timet/database.rb', line 314

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]
    end_time = item[2]
    execute_sql('UPDATE items SET updated_at = ?, created_at = ? WHERE id = ?', [end_time, end_time, id])
  end
end