Solution: Databases

Solution explanation

In the pomodoro_test.go file in the setup function, we start by creating a temporary database file:

   t.Helper()
   tf, err := ioutil.TempFile("", "pomo")
   if err != nil {
       t.Fatal(err)
   }
   tf.Close()

We then open a DB connection to the SQLite database. If the connection cannot be made due to some error, the relevant message is thrown and the program is ended:

  dbRepo, err := repository.NewSQLite3Repo(tf.Name())
  if err != nil {
      t.Fatal(err)
  }

  db, err := sql.Open("sqlite3", tf.Name())
  if err != nil {
      t.Fatal(err)
  }

  if err := db.Ping(); err != nil {
      t.Fatal(err)
  }

Insert SQL statement prototype is then given in the program:

   insStmt, err := db.Prepare("INSERT INTO interval VALUES(NULL, ?,?,?,?,?)")
   if err != nil {
       t.Fatal(err)
   }
   defer insStmt.Close()

We then define categories (CategoryPomodoro, CategoryShortBreak):

   categories := []string{
       pomodoro.CategoryPomodoro,
       pomodoro.CategoryShortBreak,
   }

We then define an outer loop (for day until 5) as the data is available for 5 days. Similarly, the inner loop is set for 3 days, as each day should have 3 Pomodoros and 3 short breaks:

   for day := 0; day < 5; day++ {
       for cyclo := 0; cyclo < 3; cyclo++ {
           for i, category := range categories {

The start date is set to January 1, 2001.

start := time.Date(2001, 1, 1+day, 9+cyclo, i*25, 0, 0, time.Local)
duration := time.Duration(25/(4*i+1)) * time.Minute

We then execute the insert statement by providing it with the required parameters within the loop:

_, err := insStmt.Exec(
start,
duration,
duration,
category,
pomodoro.StateDone,
           )
           if err != nil {
                t.Fatal(err)
           }

We then do the same for Long Break, and then execute the insert statement:

       start := time.Date(2001, 1, 1+day, 12, 0, 0, 0, time.Local)
       duration := 15 * time.Minute
       // Exec INSERT statement
       _, err := insStmt.Exec(
           start,
           duration,
           duration,
           pomodoro.CategoryLongBreak,
           pomodoro.StateDone,
       )
       if err != nil {
           t.Fatal(err)
       }
   }

Finally, the repository to access the DB is returned:

   return dbRepo, func() {
       os.Remove(tf.Name())
   }

See the complete solution below and try it out.