Class: SqliteDatabase::DBStudent

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

Overview

Child class of DBBasic to add the capabilities to the database to handle Person::Student objects

Instance Attribute Summary

Attributes inherited from DBBasic

#db

Instance Method Summary collapse

Methods inherited from DBBasic

#create_tables, #insert_person, #insert_task, #map_task_to_person, #query_assignments, #query_assignments_for_person, #query_max_person_id, #query_max_task_id, #query_person_by_id, #query_person_by_name, #query_persons, #query_task, #query_tasks

Constructor Details

#initialize(database) ⇒ DBStudent

initialization

Parameters:

  • database (SQLite3::Database) —

    a reference of the database



9
10
11
 
# File 'lib/data/sqlite/db_student.rb', line 9

def initialize(database)
  super(database)
end

Instance Method Details

#generate_additional_tables ⇒ Object (private)

overwritten method from the parent class to specify the creation of additional required tables



76
77
78
79
80
81
 
# File 'lib/data/sqlite/db_student.rb', line 76

def generate_additional_tables
  @db.execute("CREATE TABLE IF NOT EXISTS
               Students(Id INTEGER PRIMARY KEY,
               P_Id INTEGER, Mat_Nr INTEGER,
               FOREIGN KEY(P_Id) REFERENCES Persons(Id))")
end

#insert_student(id, name, mat_nr) ⇒ Object

method to insert a Person::Student into the database

Parameters:

  • id (Integer) —

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

  • name (String) —

    the name of the student

  • mat_nr (Integer) —

    the matriculation number



18
19
20
21
22
23
24
 
# File 'lib/data/sqlite/db_student.rb', line 18

def insert_student(id, name, mat_nr)
  insert_person(id, name)
  stmt = @db.prepare("INSERT OR REPLACE INTO Students(P_Id, Mat_Nr)
                      VALUES (?, ?)")
  stmt.execute(id, mat_nr)
  nil
end

#query_matnr_for_student(id) ⇒ ResultSet

method to query a metriculcation number for a given id

Parameters:

  • id (Integer) —

    the query id

Returns:

  • (ResultSet) —

    the result



67
68
69
70
 
# File 'lib/data/sqlite/db_student.rb', line 67

def query_matnr_for_student(id)
  stmt = @db.prepare("SELECT s.Mat_Nr FROM Students s WHERE s.P_Id = ?")
  stmt.execute(id)
end

#query_student_by_id(id) ⇒ ResultSet

method to query a Person::Student by id

Parameters:

  • id (Integer) —

    the requested id

Returns:

  • (ResultSet) —

    the results



29
30
31
32
33
34
 
# File 'lib/data/sqlite/db_student.rb', line 29

def query_student_by_id(id)
  stmt = @db.prepare("SELECT p.Id, p.Name, s.Mat_Nr FROM
                      Students s LEFT JOIN Persons p on p.id=s.p_id
                      WHERE p.id = ?")
  stmt.execute(id)
end

#query_student_by_matnr(mat_nr) ⇒ ResultSet

method to query a Person::Student by matriculation number

Parameters:

  • mat_nr (Integer) —

    the requested matriculation number

Returns:

  • (ResultSet) —

    the results



49
50
51
52
53
54
 
# File 'lib/data/sqlite/db_student.rb', line 49

def query_student_by_matnr(mat_nr)
  stmt = @db.prepare("SELECT p.Id, p.Name, s.Mat_Nr FROM
                      Students s LEFT JOIN Persons p on p.id=s.p_id
                      WHERE s.Mat_Nr = ?")
  stmt.execute(id)
end

#query_student_by_name(name) ⇒ ResultSet

method to query a Person::Student by id

Parameters:

  • name (String) —

    the requested name

Returns:

  • (ResultSet) —

    the results



39
40
41
42
43
44
 
# File 'lib/data/sqlite/db_student.rb', line 39

def query_student_by_name(name)
  stmt = @db.prepare("SELECT p.Id, p.Name, s.Mat_Nr FROM
                      Students s LEFT JOIN Persons p on p.id=s.p_id
                      WHERE p.name = ?")
  stmt.execute(name)
end

#query_students ⇒ ResultSet

method to query all Person::Students

Returns:

  • (ResultSet) —

    the results



58
59
60
61
62
 
# File 'lib/data/sqlite/db_student.rb', line 58

def query_students
  stmt = @db.prepare("SELECT p.Id, p.Name, s.Mat_Nr FROM
                      Students s LEFT JOIN Persons p on p.id=s.p_id")
  stmt.execute
end