Class: Timet::Database
- Inherits:
-
Object
- Object
- Timet::Database
- 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
-
#add_column(table_name, new_column_name, date_type) ⇒ void
Adds a new column to the specified table if it does not already exist.
-
#all_items ⇒ Array
Fetches all items from the items table that have a start time greater than or equal to today.
-
#close ⇒ void
Closes the database connection.
-
#create_table ⇒ void
Creates the items table if it doesn’t already exist.
-
#delete_item(id) ⇒ void
Deletes an item from the items table.
-
#determine_status(result) ⇒ Symbol
Determines the status of a time tracking result based on the presence and end time of items.
-
#execute_sql(sql, params = []) ⇒ Array
Executes a SQL query and returns the result.
-
#fetch_last_id ⇒ Integer?
Fetches the ID of the last inserted item.
-
#find_item(id) ⇒ Array?
Finds an item by its ID.
-
#initialize(database_path = DEFAULT_DATABASE_PATH) ⇒ void
constructor
Initializes a new instance of the Database class.
-
#insert_item(*args) ⇒ void
Inserts a new item into the items table.
-
#item_status(id = nil) ⇒ Symbol
Determines the status of the last item in the items table.
-
#last_item ⇒ Array?
Fetches the last item from the items table.
-
#move_old_database_file(database_path) ⇒ Object
Moves the old database file to the new location if it exists.
-
#seconds_to_hms(seconds) ⇒ String
Converts a given number of seconds into a human-readable HH:MM:SS format.
-
#update_item(id, field, value) ⇒ void
Updates an existing item in the items table.
-
#update_time_columns ⇒ void
Updates the ‘updated_at` and `created_at` columns for items where either of these columns is null.
Constructor Details
#initialize(database_path = DEFAULT_DATABASE_PATH) ⇒ void
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.
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
The method first checks if the column already exists in the table using ‘pragma_table_info`.
If the column exists, the method returns without making any changes.
If the column does not exist, the method executes an SQL ‘ALTER TABLE` statement to add the column.
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.
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
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.
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
The method closes the SQLite3 database connection.
This method returns an undefined value.
Closes the database connection.
database connection.
238 239 240 |
# File 'lib/timet/database.rb', line 238 def close @db&.close end |
#create_table ⇒ void
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.
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
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.
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
The method checks if the result set is empty and returns :no_items if true.
If the last item in the result set has no end time, it returns :in_progress.
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.
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
The method executes the given SQL query with the provided parameters and returns the result.
Executes a SQL query and returns the result.
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.}" [] end |
#fetch_last_id ⇒ Integer?
The method executes SQL to fetch the ID of the last inserted item.
Fetches the ID of the last inserted item.
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?
The method executes a SQL query to find the item by its ID.
If the item is found, it returns the item as an array.
If the item is not found, it returns nil.
Finds an item by its ID.
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
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.
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
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.
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?
The method executes SQL to fetch the last item from the ‘items’ table.
Fetches the last item from the items table.
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.
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
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.
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
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.
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
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.
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 |