Class: TimeTrackr::SqliteDatabase

Inherits:
Database
  • Object
show all
Defined in:
lib/timetrackr/sqlite.rb

Instance Method Summary collapse

Methods inherited from Database

#close, create, #time

Constructor Details

#initialize(path) ⇒ SqliteDatabase

Returns a new instance of SqliteDatabase.



4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
# File 'lib/timetrackr/sqlite.rb', line 4

def initialize(path)
  @log_path = path
  if !File.exist? @log_path
    @db = SQLite3::Database.new(@log_path)
    sql_events = "CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  task TEXT,
  start TIME,
  stop TIME,
  notes TEXT);"
  @db.execute(sql_events)
  else
    @db = SQLite3::Database.open(@log_path)
  end
  @db.type_translation = true
end

Instance Method Details

#clear(task) ⇒ Object 



65
66
67
68
 
# File 'lib/timetrackr/sqlite.rb', line 65

def clear(task)
  sql = "DELETE FROM events WHERE task = :task;"
  @db.execute(sql, 'task' => task)
end

#current ⇒ Object 



21
22
23
24
25
26
 
# File 'lib/timetrackr/sqlite.rb', line 21

def current
  sql = "SELECT DISTINCT task FROM events WHERE stop IS NULL;"
  @db.execute(sql).collect{|row|
    row.first
  }
end

#history(task, p_begin = nil, p_end = nil) ⇒ Object 



53
54
55
56
57
58
 
# File 'lib/timetrackr/sqlite.rb', line 53

def history(task, p_begin=nil, p_end=nil)
  sql = "SELECT start, stop, notes FROM events WHERE task = :task ORDER BY start;"
  @db.execute(sql,'task' => task).collect{ |row|
    Period.new(task,row[0],row[1],row[2])
  }
end

#rename(from, to) ⇒ Object 



60
61
62
63
 
# File 'lib/timetrackr/sqlite.rb', line 60

def rename(from, to)
  sql = "UPDATE events SET task = :to WHERE task = :from;"
  @db.execute(sql, 'to' => to, 'from' => from)
end

#start(task, notes) ⇒ Object 



35
36
37
38
39
40
41
42
 
# File 'lib/timetrackr/sqlite.rb', line 35

def start(task, notes)
  sql = "SELECT id FROM events WHERE task = :task AND stop IS NULL;"
  exists = @db.get_first_value(sql, 'task' => task)
  if !exists
    sql = "INSERT INTO events (task,start,notes) VALUES (:task,:start,:notes);"
    @db.execute(sql,'task' => task, 'start' => Time.now.to_s, 'notes' => notes)
  end
end

#stop(task) ⇒ Object 



44
45
46
47
48
49
50
51
 
# File 'lib/timetrackr/sqlite.rb', line 44

def stop(task)
  sql = "SELECT id FROM events WHERE task = :task AND stop IS NULL;"
  exists = @db.get_first_value(sql, 'task' => task)
  if exists
    sql = "UPDATE events SET stop = :stop WHERE id = :current;"
    @db.execute(sql, 'current' => exists, 'stop' => Time.now.to_s)
  end
end

#tasks ⇒ Object 



28
29
30
31
32
33
 
# File 'lib/timetrackr/sqlite.rb', line 28

def tasks
  sql = "SELECT DISTINCT task FROM events;"
  @db.execute(sql).collect{ |row|
    row.first
  }
end