scala anorm foreach without loading everything into memory

Question

the following code prints all rows using scala anorm:

import anorm._

val query = s"select col_str, col_num from mytable"

val rowParser: RowParser[~[String, Int]] = SqlParser.str(1) ~ SqlParser.int(2)

def f(row: (String, Int)) {
  println(row._1 + "\t" + row._2)
}

val rows: List[(String, Int)] = SQL(query).as(rowParser.*).map(SqlParser.flatten)

rows.foreach(f)

however it needs to load all the data into memory. A solution to avoid loading all data is to use fold, as follows:

SQL(query).fold(Unit, ColumnAliaser.empty) { (_, r: Row) =>
  println(r[String](1) + "\t" + r[Int](2))
  Unit
}

however, here I am not using the rowParser and the flatten. How can I modify the previous code in order to use the rowParser and the flatten, without loading everything into memory? something like this (note: this code does not work):

SQL(query).as(rowParser.*).map(SqlParser.flatten).fold(Unit, ColumnAliaser.empty) { (_, row: (String, Int)) =>
  f(row)
  Unit
}

and, yet more difficult, have to make an implicit forEach2 function, so that I can run it as follows:

SQL(query).as(rowParser.*).map(SqlParser.flatten).forEach2(f)

---

OLD

some previous code I've tried in that direction:

def foreach[T, A, B](sqlQuery: SqlQuery, rowParser: RowParser[~[A, B]], f: (~[A, B]) => T) {
  val result: Either[List[Throwable], Unit.type] = sqlQuery.fold(Unit, ColumnAliaser.empty) { (_, row: Row) =>
    rowParser(row) match {
      case Success(r: ~[A, B]) =>
        f(r)
        Unit
      case Error(err) =>
        throw AnormException(err.toString)
    }
  }

  result.left.foreach { t: Seq[Throwable] =>
    t.foreach(_.printStackTrace)
    t.headOption.foreach { tt => throw tt}
  }
}

def f(row: ~[String, Int]) {
  println(row._1 + "\t" + row._2)
}

foreach(SQL(query), rowParser, f)

This works. However, I need to transform def f(row: (String, Int)) into def f(row: ~[String, String]). How can I remove this ~ in my f function? Also this foreach function expects a row with two columns. how to generalise this to n columns?.

Answer 1

Is this improvement of your foreach good enough? Or do you need something more?

object AnormForEachOps {

  import anorm._
  import java.sql.Connection

  implicit class ForEachOps(val query: SqlQuery) extends AnyVal {
    def foreach[T1, T2, R](rowParser: RowParser[~[T1, T2]], f: R => Unit)(implicit connection: Connection, fl: TupleFlattener[(T1 ~ T2) => R]): Unit = {
      query.fold(Unit, ColumnAliaser.empty) { (_, r: Row) =>
        rowParser(r).map(SqlParser.flatten) match {
          case Success(t) =>
            f(t)
            Unit
          case Error(err) =>
            throw AnormException(err.toString)
        }
        Unit
      }
    }
  }

}

So your example would become something like this:

import AnormForEachOps._
val query = s"select col_str, col_num from mytable"
val rowParser = SqlParser.str(1) ~ SqlParser.int(2)
SQL(query).foreach(rowParser, (r: (String, Int)) => {
    Logger.info(r._1 + "\t" + r._2)
})

The main trick in this modification is the implicit TupleFlattener argument that does conversion from ~ to usual tuple (via SqlParser.flatten call)