Class: SqliteDatabase::DBBasic

Inherits:
Object
  • Object
show all
Defined in:
lib/data/sqlite/db_basic.rb

Overview

Basic class to open and manage the usage of a sqlite3 database as a storage system. Child classes should use #generate_additional_tables to create required tables for use.

Direct Known Subclasses

DBStudent

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(database) ⇒ DBBasic

initialization

Parameters:

  • database (SQLite3::Database) —

    a reference of the database



12
13
14
15
 
# File 'lib/data/sqlite/db_basic.rb', line 12

def initialize(database)
  @db = database
  create_tables
end

Instance Attribute Details

#db ⇒ Object (readonly, private)

Returns:



122
123
124
 
# File 'lib/data/sqlite/db_basic.rb', line 122

def db
  @db
end

Instance Method Details

#create_tables ⇒ Object (private)

method to create the required tables in the database



125
126
127
128
129
130
131
132
133
134
135
136
 
# File 'lib/data/sqlite/db_basic.rb', line 125

def create_tables
  @db.execute("CREATE TABLE IF NOT EXISTS Persons(Id INTEGER PRIMARY KEY,
               Name TEXT)")
  @db.execute("CREATE TABLE IF NOT EXISTS Tasks(Id INTEGER PRIMARY KEY,
               Start TEXT, End TEXT, Description TEXT)")
  @db.execute("CREATE TABLE IF NOT EXISTS Matching(Id INTEGER PRIMARY KEY,
               P_Id INTEGER, T_Id INTEGER,
               FOREIGN KEY(P_Id) REFERENCES Persons(Id),
               FOREIGN KEY(T_Id) REFERENCES Tasks(Id))")
  generate_additional_tables
  nil
end

#generate_additional_tables ⇒ Object (private)

method for child classes to specify the generation of additional tables



139
140
141
 
# File 'lib/data/sqlite/db_basic.rb', line 139

def generate_additional_tables
  # no additional tables
end

#insert_person(id, name) ⇒ Object

method to insert a Person::Person into the database

Parameters:

  • id (Integer) —

    the id that was generated by the number generator for person entities

  • name (String) —

    the name of the person



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

def insert_person(id, name)
  stmt = @db.prepare("INSERT OR REPLACE INTO Persons(Id, Name)
                      VALUES (?, ?)")
  stmt.execute(id, name)
  nil
end

#insert_task(id, start_time, end_time, description) ⇒ Object

method to insert a Task::Task into the database

Parameters:

  • id (Integer) —

    the id that was generated by the number generator for person entities

  • start_time (Date) —

    the starting point of the task

  • end_time (Date) —

    the ending point of the task

  • description (String) —

    the description of the task



34
35
36
37
38
39
40
 
# File 'lib/data/sqlite/db_basic.rb', line 34

def insert_task(id, start_time, end_time, description)
  stmt = @db.prepare("INSERT OR REPLACE INTO
                      Tasks(Id, Start, End, Description)
                      VALUES (?, ?, ?, ?)")
  stmt.execute(id, start_time.iso8601, end_time.iso8601, description)
  nil
end

#map_task_to_person(p_id, t_id) ⇒ Object

method to add a Task::Task to a person entity

Parameters:

  • p_id (Integer) —

    the unique id of the person

  • t_id (Integer) —

    the unique id of the task



45
46
47
48
49
50
 
# File 'lib/data/sqlite/db_basic.rb', line 45

def map_task_to_person(p_id, t_id)
  stmt = @db.prepare("INSERT OR REPLACE INTO Matching(P_Id, T_Id)
                      VALUES (?, ?)")
  stmt.execute(p_id, t_id)
  nil
end

#query_assignments ⇒ ResultSet

method to query the mapping of assigned tasks to persons

Returns:

  • (ResultSet) —

    the query result



92
93
94
95
 
# File 'lib/data/sqlite/db_basic.rb', line 92

def query_assignments
  stmt = @db.prepare("SELECT * FROM Matching")
  stmt.execute
end

#query_assignments_for_person(p_id) ⇒ ResultSet

method to query the task ids for a Person represented by its id

Parameters:

  • p_id (Integer) —

    the person id

Returns:

  • (ResultSet) —

    the query result



100
101
102
103
 
# File 'lib/data/sqlite/db_basic.rb', line 100

def query_assignments_for_person(p_id)
  stmt = @db.prepare("SELECT T_id FROM Matching WHERE Id = ?")
  stmt.execute(p_id)
end

#query_max_person_id ⇒ ResultSet

method to query the max id of the person table

Returns:

  • (ResultSet) —

    the query result



107
108
109
110
 
# File 'lib/data/sqlite/db_basic.rb', line 107

def query_max_person_id
  stmt = @db.prepare("SELECT Id FROM Persons ORDER BY Id DESC LIMIT 1")
  stmt.execute
end

#query_max_task_id ⇒ ResultSet

method to query the max id of the person table

Returns:

  • (ResultSet) —

    the query result



114
115
116
117
 
# File 'lib/data/sqlite/db_basic.rb', line 114

def query_max_task_id
  stmt = @db.prepare("SELECT Id FROM Tasks ORDER BY Id DESC LIMIT 1")
  stmt.execute
end

#query_person_by_id(id) ⇒ ResultSet

method to query a Person::Person by id

Parameters:

  • id (Integer) —

    the requested id

Returns:

  • (ResultSet) —

    the results



70
71
72
73
 
# File 'lib/data/sqlite/db_basic.rb', line 70

def query_person_by_id(id)
  stmt = @db.prepare("SELECT * FROM Persons WHERE Id = ?")
  stmt.execute(id)
end

#query_person_by_name(name) ⇒ ResultSet

method to query a Person::Person by name

Parameters:

  • name (String) —

    the requested name

Returns:

  • (ResultSet) —

    the results



78
79
80
81
 
# File 'lib/data/sqlite/db_basic.rb', line 78

def query_person_by_name(name)
  stmt = @db.prepare("SELECT * FROM Persons WHERE Name = ?")
  stmt.execute(name)
end

#query_persons ⇒ ResultSet

method to query all stored Person::Persons

Returns:

  • (ResultSet) —

    the results



85
86
87
88
 
# File 'lib/data/sqlite/db_basic.rb', line 85

def query_persons
  stmt = @db.prepare("SELECT * FROM Persons")
  stmt.execute
end

#query_task(id) ⇒ ResultSet

method to query a Task::Task by id

Parameters:

  • id (Integer) —

    the requested id

Returns:

  • (ResultSet) —

    the results



55
56
57
58
 
# File 'lib/data/sqlite/db_basic.rb', line 55

def query_task(id)
  stmt = @db.prepare("SELECT * FROM Tasks WHERE Id = ?")
  stmt.execute(id)
end

#query_tasks ⇒ ResultSet

method to query all stored Task::Task

Returns:

  • (ResultSet) —

    the results



62
63
64
65
 
# File 'lib/data/sqlite/db_basic.rb', line 62

def query_tasks
  stmt = @db.prepare("SELECT * FROM Tasks")
  stmt.execute
end