doobie.util.transactor.Transactor Scala Examples

package com.smartbackpackerapp.repository

import cats.MonadError
import cats.syntax.applicative._
import cats.syntax.applicativeError._
import cats.syntax.option._
import com.smartbackpackerapp.model._
import com.smartbackpackerapp.repository.algebra.HealthRepository
import doobie.free.connection.ConnectionIO
import doobie.implicits._
import doobie.util.invariant.UnexpectedEnd
import doobie.util.query.Query0
import doobie.util.transactor.Transactor

class PostgresHealthRepository[F[_]](xa: Transactor[F])
                                    (implicit F: MonadError[F, Throwable]) extends HealthRepository[F] {

  override def findHealthInfo(from: CountryCode): F[Option[Health]] = {
    val program: ConnectionIO[Health] =
      for {
        c <- HealthStatement.findCountryId(from).unique
        m <- HealthStatement.mandatory(c).to[List]
        r <- HealthStatement.recommendations(c).to[List]
        o <- HealthStatement.optional(c).to[List]
        n <- HealthStatement.healthNotices(c).to[List]
        a <- HealthStatement.healthAlert(c).unique
      } yield {
        val mandatory       = m.map(_.toVaccine)
        val recommendations = r.map(_.toVaccine)
        val optional        = o.map(_.toVaccine)
        val vaccinations    = Vaccinations(mandatory, recommendations, optional)
        val alertLevel      = a.toAlertLevel
        val healthNotices   = n.map(_.toHealthAlert)
        Health(vaccinations, HealthNotices(alertLevel, healthNotices))
      }

    program.map(Option.apply).transact(xa).recoverWith {
      case UnexpectedEnd => none[Health].pure[F]
    }
  }

}

object HealthStatement {

  def findCountryId(from: CountryCode): Query0[Int] = {
    sql"SELECT id FROM countries WHERE code = ${from.value}"
      .query[Int]
  }

  def mandatory(countryId: Int): Query0[VaccineDTO] = {
    sql"SELECT v.disease, v.description, v.categories FROM vaccine_mandatory AS vm INNER JOIN vaccine AS v ON vm.vaccine_id=v.id WHERE vm.country_id = $countryId"
      .query[VaccineDTO]
  }

  def recommendations(countryId: Int): Query0[VaccineDTO] = {
    sql"SELECT v.disease, v.description, v.categories FROM vaccine_recommendations AS vr INNER JOIN vaccine AS v ON vr.vaccine_id=v.id WHERE vr.country_id = $countryId"
      .query[VaccineDTO]
  }

  def  optional(countryId: Int): Query0[VaccineDTO] = {
    sql"SELECT v.disease, v.description, v.categories FROM vaccine_optional AS vo INNER JOIN vaccine AS v ON vo.vaccine_id=v.id WHERE vo.country_id = $countryId"
      .query[VaccineDTO]
  }

  def healthNotices(countryId: Int): Query0[HealthNoticeDTO] = {
    sql"SELECT ha.title, ha.weblink, ha.description FROM health_notice AS hn INNER JOIN health_alert AS ha ON hn.alert_id = ha.id WHERE hn.country_id = $countryId"
      .query[HealthNoticeDTO]
  }

  def healthAlert(countryId: Int): Query0[HealthAlertDTO] = {
    sql"SELECT alert_level FROM health_alert_level WHERE country_id = $countryId"
      .query[HealthAlertDTO]
  }

} 

package com.raphtory.examples.blockchain.spouts

import cats.effect.Blocker
import cats.effect.IO
import com.raphtory.core.components.Spout.SpoutTrait
import doobie.implicits._
import doobie.util.ExecutionContexts
import doobie.util.transactor.Transactor

import scala.concurrent.ExecutionContext.Implicits.global
import scala.concurrent.duration.Duration
import scala.concurrent.duration.MILLISECONDS
import scala.concurrent.duration.SECONDS

class EthereumPostgresSpout extends SpoutTrait {
  var startBlock = System.getenv().getOrDefault("STARTING_BLOCK", "46147").trim.toInt //first block to have a transaction by default
  val batchSize  = System.getenv().getOrDefault("BLOCK_BATCH_SIZE", "100").trim.toInt //number of blocks to pull each query
  val maxblock   = System.getenv().getOrDefault("MAX_BLOCK", "8828337").trim.toInt    //Maximum block in database to stop querying once this is reached

  val dbURL      = System.getenv().getOrDefault("DB_URL", "jdbc:postgresql:ether").trim //db connection string, default is for local with db called ether
  val dbUSER     = System.getenv().getOrDefault("DB_USER", "postgres").trim             //db user defaults to postgres
  val dbPASSWORD = System.getenv().getOrDefault("DB_PASSWORD", "").trim                 //default no password

  // querying done with doobie wrapper for JDBC (https://tpolecat.github.io/doobie/)
  implicit val cs = IO.contextShift(ExecutionContexts.synchronous)
  val dbconnector = Transactor.fromDriverManager[IO](
          "org.postgresql.Driver",
          dbURL,
          dbUSER,
          dbPASSWORD,
          Blocker.liftExecutionContext(ExecutionContexts.synchronous)
  )

  override def ProcessSpoutTask(message: Any): Unit = message match {
    case StartSpout  => AllocateSpoutTask(Duration(1, MILLISECONDS), "nextBatch")
    case "nextBatch" => running()
    case _           => println("message not recognized!")
  }

  protected def running(): Unit = {
    sql"select from_address, to_address, value,block_timestamp from transactions where block_number >= $startBlock AND block_number < ${startBlock + batchSize} "
      .query[
              (String, String, String, String)
      ]                                      //get the to,from,value and time for transactions within the set block batch
      .to[List]                              // ConnectionIO[List[String]]
      .transact(dbconnector)                 // IO[List[String]]
      .unsafeRunSync                         // List[String]
      .foreach(x => sendTuple(x.toString())) //send each transaction to the routers

    startBlock += batchSize                                   //increment batch for the next query
    if (startBlock > maxblock) stop()                         //if we have reached the max block we stop querying the database
    AllocateSpoutTask(Duration(1, MILLISECONDS), "nextBatch") // line up the next batch
  }
} 

package ru.pavkin.booking

import aecor.data.{ Enriched, TagConsumer }
import aecor.journal.postgres.{ Offset, PostgresEventJournal, PostgresOffsetStore }
import aecor.runtime.KeyValueStore
import cats.effect._
import cats.implicits._
import cats.temp.par._
import doobie.implicits._
import doobie.util.transactor.Transactor
import ru.pavkin.booking.booking.entity.EventsourcedBooking._
import ru.pavkin.booking.booking.entity.{ BookingEvent, EventMetadata, EventsourcedBooking }
import ru.pavkin.booking.booking.serialization.BookingEventSerializer
import ru.pavkin.booking.booking.view.PostgresBookingViewRepository
import ru.pavkin.booking.common.models.BookingKey
import ru.pavkin.booking.common.postgres.PostgresTransactor
import ru.pavkin.booking.config.{ AppConfig, PostgresJournals }

final class PostgresWirings[F[_]: Async: Timer: Par] private (val transactor: Transactor[F],
                                                              val journals: PostgresJournals) {

  val offsetStoreCIO = PostgresOffsetStore("consumer_offset")
  val offsetStore: KeyValueStore[F, TagConsumer, Offset] = offsetStoreCIO.mapK(transactor.trans)

  val bookingsJournal =
    new PostgresEventJournal[F, BookingKey, Enriched[EventMetadata, BookingEvent]](
      transactor,
      journals.booking.tableName,
      EventsourcedBooking.tagging,
      BookingEventSerializer
    )

  // views

  val bookingViewRepo = new PostgresBookingViewRepository[F](transactor)
}

object PostgresWirings {
  def apply[F[_]: Async: Timer: Par: ContextShift](
    settings: AppConfig
  ): Resource[F, PostgresWirings[F]] =
    for {
      transactor <- PostgresTransactor.transactor[F](settings.postgres)
      wirings = new PostgresWirings(transactor, settings.postgresJournals)
      _ <- Resource.liftF(
            List(
              wirings.offsetStoreCIO.createTable.transact(transactor),
              wirings.bookingViewRepo.createTable,
              wirings.bookingsJournal.createTable,
            ).parSequence
          )
    } yield wirings
} 

package ru.pavkin.booking.booking.view

import java.sql.Timestamp
import java.time.Instant

import cats.Monad
import cats.implicits._
import doobie._
import doobie.implicits._
import doobie.util.transactor.Transactor
import io.circe.{ Decoder, Encoder, Json }
import io.circe.parser._
import org.postgresql.util.PGobject
import ru.pavkin.booking.common.models._

class PostgresBookingViewRepository[F[_]: Monad](transactor: Transactor[F],
                                                 tableName: String = "bookings")
    extends BookingViewRepository[F] {

  implicit val jsonMeta: Meta[Json] =
    Meta.Advanced
      .other[PGobject]("json")
      .timap[Json](a => parse(a.getValue).leftMap[Json](e => throw e).merge)(a => {
        val o = new PGobject
        o.setType("json")
        o.setValue(a.noSpaces)
        o
      })

  implicit val seatsMeta: Meta[List[Seat]] = jsonMeta.timap(
    j => Decoder[List[Seat]].decodeJson(j).right.get
  )(s => Encoder[List[Seat]].apply(s))

  implicit val ticketsMeta: Meta[List[Ticket]] = jsonMeta.timap(
    j => Decoder[List[Ticket]].decodeJson(j).right.get
  )(s => Encoder[List[Ticket]].apply(s))

  implicit val instantMeta: Meta[Instant] =
    Meta[Timestamp].timap(_.toInstant)(Timestamp.from)

  implicit val bookingStatusMeta: Meta[BookingStatus] =
    Meta[String].timap(BookingStatus.withName)(_.entryName)

  def get(bookingId: BookingKey): F[Option[BookingView]] =
    queryView(bookingId).option.transact(transactor)

  def byClient(clientId: ClientId): F[List[BookingView]] =
    queryForClient(clientId).to[List].transact(transactor)

  def set(view: BookingView): F[Unit] =
    Update[BookingView](setViewQuery).run(view).transact(transactor).void

  def expired(now: Instant): fs2.Stream[F, BookingKey] =
    queryExpired(now).stream.transact(transactor)

  def createTable: F[Unit] = createTableQuery.transact(transactor).void

  private val setViewQuery =
    s"""INSERT INTO $tableName
    (booking_id, client_id, concert_id, seats, tickets, status, confirmed_at, expires_at, version)
    VALUES (?,?,?,?,?,?,?,?,?)
    ON CONFLICT (booking_id)
    DO UPDATE SET
     tickets = EXCLUDED.tickets,
     status = EXCLUDED.status,
     confirmed_at = EXCLUDED.confirmed_at,
     expires_at = EXCLUDED.expires_at,
     version = EXCLUDED.version;"""

  private def queryView(bookingId: BookingKey) =
    (fr"SELECT * FROM " ++ Fragment.const(tableName) ++
      fr"WHERE booking_id = $bookingId;")
      .query[BookingView]

  private def queryExpired(now: Instant) =
    (fr"SELECT booking_id FROM " ++ Fragment.const(tableName) ++
      fr"WHERE status = ${BookingStatus.Confirmed: BookingStatus} AND expires_at < $now;")
      .query[BookingKey]

  private def queryForClient(clientId: ClientId) =
    (fr"SELECT * FROM " ++ Fragment.const(tableName) ++
      fr"WHERE client_id = $clientId;")
      .query[BookingView]

  private val createTableQuery = (fr"""
    CREATE TABLE IF NOT EXISTS """ ++ Fragment.const(tableName) ++
    fr""" (
    booking_id    text      NOT NULL PRIMARY KEY,
    client_id     text      NOT NULL,
    concert_id    text      NOT NULL,
    seats         json      NOT NULL,
    tickets       json      NOT NULL,
    status        text      NOT NULL,
    confirmed_at  timestamptz,
    expires_at    timestamptz,
    version       bigint    NOT NULL
    );
  """).update.run

} 

package io.hydrosphere.mist.master.store

import java.util.concurrent.{ExecutorService, Executors, Future, TimeUnit}

import cats.arrow.FunctionK
import cats.effect._
import com.zaxxer.hikari.{HikariConfig, HikariDataSource}
import doobie.util.transactor.Transactor
import doobie.util.transactor.Transactor.Aux
import io.hydrosphere.mist.utils.Logger

import scala.concurrent.ExecutionContext



  def shutdown(): Unit = {
    if (!ds.isClosed) {
      logger.info("Closing Hikari data source")
      ds.close()
    } else {
      logger.warn("Hikari datasource had not been properly initialized before closing")
    }

    shutdownExecutorService(awaitShutdown, ce, "connections EC")
    shutdownExecutorService(awaitShutdown, te, "tx EC")
  }
} 

package io.github.pauljamescleary.petstore
package infrastructure.repository.doobie

import cats.data.NonEmptyList
import cats.effect.IO
import cats.syntax.applicative._
import doobie.scalatest.IOChecker
import doobie.util.transactor.Transactor
import org.scalatest.funsuite.AnyFunSuite
import PetStoreArbitraries.pet
import org.scalatest.matchers.should.Matchers

class PetQueryTypeCheckSpec extends AnyFunSuite with Matchers with IOChecker {
  override val transactor: Transactor[IO] = testTransactor

  import PetSQL._

  test("Typecheck pet queries") {
    pet.arbitrary.sample.map { p =>
      check(selectByStatus(p.status.pure[NonEmptyList]))
      check(insert(p))
      p.id.foreach(id => check(PetSQL.update(p, id)))
    }

    check(selectTagLikeString("example".pure[NonEmptyList]))
    check(select(1L))
    check(selectAll)
    check(delete(1L))
    check(selectByNameAndCategory("name", "category"))
  }
} 

package io.github.pauljamescleary.petstore
package infrastructure.repository.doobie

import org.scalatest.funsuite.AnyFunSuite
import cats.effect.IO
import doobie.scalatest.IOChecker
import doobie.util.transactor.Transactor

import PetStoreArbitraries.user
import org.scalatest.matchers.should.Matchers

class UserQueryTypeCheckSpec extends AnyFunSuite with Matchers with IOChecker {
  override val transactor: Transactor[IO] = testTransactor

  import UserSQL._

  test("Typecheck user queries") {
    user.arbitrary.sample.map { u =>
      check(insert(u))
      check(byUserName(u.userName))
      u.id.foreach(id => check(update(u, id)))
    }
    check(selectAll)
    check(select(1L))
    check(delete(1L))
  }
} 

package tamer
package db

import java.sql.SQLException
import java.time.Instant

import cats.effect.Blocker
import doobie.hikari.HikariTransactor
import doobie.implicits._
import doobie.util.transactor.Transactor
import eu.timepit.refined.auto._
import fs2.{Chunk, Stream}
import log.effect.LogWriter
import log.effect.zio.ZioLogWriter.log4sFromName
import tamer.config.{DbConfig, QueryConfig}
import zio._
import zio.interop.catz._

import scala.concurrent.ExecutionContext

trait Db extends Serializable {
  val db: Db.Service[Any]
}

object Db {
  implicit class InstantOps(ours: Instant) {
    def -(theirs: Instant): Long = ours.toEpochMilli - theirs.toEpochMilli
  }

  case class ChunkWithMetadata[V](chunk: Chunk[V], pulledAt: Instant = Instant.now())
  case class ValueWithMetadata[V](value: V, pulledAt: Instant = Instant.now())

  trait Service[R] {
    def runQuery[K, V, State](
        tnx: Transactor[Task],
        setup: Setup[K, V, State],
        queryConfig: QueryConfig
    )(state: State, q: Queue[(K, V)]): ZIO[R, DbError, State]
  }

  object > extends Service[Db] {
    override final def runQuery[K, V, State](
        tnx: Transactor[Task],
        setup: Setup[K, V, State],
        queryConfig: QueryConfig
    )(state: State, q: Queue[(K, V)]): ZIO[Db, DbError, State] = ZIO.accessM(_.db.runQuery(tnx, setup, queryConfig)(state, q))
  }

  trait Live extends Db {
    override final val db: Service[Any] = new Service[Any] {
      private[this] val logTask: Task[LogWriter[Task]] = log4sFromName.provide("tamer.Db.Live")
      override final def runQuery[K, V, State](
          tnx: Transactor[Task],
          setup: Setup[K, V, State],
          queryConfig: QueryConfig
      )(state: State, q: Queue[(K, V)]): IO[DbError, State] =
        (for {
          log   <- logTask
          query <- UIO(setup.buildQuery(state))
          _     <- log.debug(s"running ${query.sql} with params derived from $state").ignore
          start <- UIO(Instant.now())
          values <-
            query
              .streamWithChunkSize(queryConfig.fetchChunkSize)
              .chunks
              .transact(tnx)
              .map(c => ChunkWithMetadata(c))
              .evalTap(c => q.offerAll(c.chunk.iterator.to(LazyList).map(v => setup.valueToKey(v) -> v)))
              .flatMap(c => Stream.chunk(c.chunk).map(v => ValueWithMetadata(v, c.pulledAt)))
              .compile
              .toList
          newState <- setup.stateFoldM(state)(
            QueryResult(
              ResultMetadata(values.headOption.fold(Instant.now())(_.pulledAt) - start),
              values.map(_.value)
            )
          )
        } yield newState).mapError { case e: Exception => DbError(e.getLocalizedMessage) }
    }
  }

  def mkTransactor(db: DbConfig, connectEC: ExecutionContext, transactEC: ExecutionContext): Managed[DbError, HikariTransactor[Task]] =
    Managed {
      HikariTransactor
        .newHikariTransactor[Task](db.driver, db.uri, db.username, db.password, connectEC, Blocker.liftExecutionContext(transactEC))
        .allocated
        .map {
          case (ht, cleanup) => Reservation(ZIO.succeed(ht), _ => cleanup.orDie)
        }
        .uninterruptible
        .refineToOrDie[SQLException]
        .mapError(sqle => DbError(sqle.getLocalizedMessage()))
    }
} 

package com.smartbackpackerapp.airlines

import cats.effect.Async
import com.smartbackpackerapp.airlines.parser.{AirlineFile, AirlinesFileParser, AllowanceFile}
import com.smartbackpackerapp.airlines.sql.AirlinesInsertData
import doobie.util.transactor.Transactor

class AirlinesModule[F[_] : Async] {

  val devDbUrl: String  = sys.env.getOrElse("JDBC_DATABASE_URL", "")
  val dbUrl: String     = sys.env.getOrElse("SB_DB_URL", "jdbc:postgresql:sb")

  private val dbDriver  = sys.env.getOrElse("SB_DB_DRIVER", "org.postgresql.Driver")
  private val dbUser    = sys.env.getOrElse("SB_DB_USER", "postgres")
  private val dbPass    = sys.env.getOrElse("SB_DB_PASSWORD", "")

  private val xa = {
    if (devDbUrl.nonEmpty) Transactor.fromDriverManager[F](dbDriver, devDbUrl)
    else Transactor.fromDriverManager[F](dbDriver, dbUrl, dbUser, dbPass)
  }

  def airlinesInsertData(airlineFile: AirlineFile,
                         allowanceFile: AllowanceFile): AirlinesInsertData[F] = {
    val parser = AirlinesFileParser[F](airlineFile, allowanceFile)
    new AirlinesInsertData[F](xa, parser)
  }

} 

package com.smartbackpackerapp.airlines.sql

import cats.effect.Async
import cats.instances.list._
import com.smartbackpackerapp.airlines.parser.AirlinesFileParser
import com.smartbackpackerapp.common.Log
import com.smartbackpackerapp.model.{Airline, BaggagePolicy}
import doobie.free.connection.ConnectionIO
import doobie.implicits._
import doobie.util.transactor.Transactor
import doobie.util.update.{Update, Update0}
import fs2.Stream

class AirlinesInsertData[F[_] : Async](xa: Transactor[F],
                                       airlinesParser: AirlinesFileParser[F])
                                      (implicit L: Log[F]) {

  import AirlineInsertStatement._

  private def program(airline: Airline): ConnectionIO[Unit] =
    for {
      airlineId <- insertAirline(airline.name.value).withUniqueGeneratedKeys[Int]("airline_id")
      policyId  <- insertBaggagePolicy(airlineId, airline.baggagePolicy).withUniqueGeneratedKeys[Int]("policy_id")
      _         <- insertManyBaggageAllowance(policyId).updateMany(airline.baggagePolicy.allowance.toDTO(policyId))
    } yield ()

  def run: Stream[F, Unit] =
    for {
      a <- airlinesParser.airlines
      _ <- Stream.eval(L.info(s"Persisting: $a"))
      _ <- Stream.eval(program(a).transact(xa))
    } yield ()

}

object AirlineInsertStatement {

  def insertAirline(name: String): Update0 = {
    sql"INSERT INTO airline (name) VALUES ($name)"
      .update
  }

  def insertBaggagePolicy(airlineId: Int,
                          baggagePolicy: BaggagePolicy): Update0 = {
    sql"INSERT INTO baggage_policy (airline_id, extra, website) VALUES ($airlineId, ${baggagePolicy.extra}, ${baggagePolicy.website})"
      .update
  }

  def insertManyBaggageAllowance(policyId: Int): Update[CreateBaggageAllowanceDTO] = {
    val sql = "INSERT INTO baggage_allowance (policy_id, baggage_type, kgs, height, width, depth) VALUES (?, ?, ?, ?, ?, ?)"
    Update[CreateBaggageAllowanceDTO](sql)
  }

} 

package com.smartbackpackerapp.repository

import cats.MonadError
import cats.syntax.applicative._
import cats.syntax.applicativeError._
import cats.syntax.option._
import com.smartbackpackerapp.model._
import com.smartbackpackerapp.repository.algebra.AirlineRepository
import doobie.free.connection.ConnectionIO
import doobie.implicits._
import doobie.util.invariant.UnexpectedEnd
import doobie.util.query.Query0
import doobie.util.transactor.Transactor

class PostgresAirlineRepository[F[_]](xa: Transactor[F])
                                     (implicit F: MonadError[F, Throwable]) extends AirlineRepository[F] {

  override def findAirline(airlineName: AirlineName): F[Option[Airline]] = {
    val program: ConnectionIO[Airline] =
      for {
        a <- AirlineStatement.findAirline(airlineName).unique
        b <- AirlineStatement.baggageAllowance(a.head).to[List]
      } yield a.toAirline(b)

    program.map(Option.apply).transact(xa).recoverWith {
      case UnexpectedEnd => none[Airline].pure[F]
    }
  }

}

object AirlineStatement {

  def findAirline(airlineName: AirlineName): Query0[AirlineDTO] = {
    sql"SELECT a.airline_id, a.name, b.policy_id, b.extra, b.website FROM airline AS a INNER JOIN baggage_policy AS b ON (a.airline_id = b.airline_id) WHERE a.name=${airlineName.value}"
      .query[AirlineDTO]
  }

  def baggageAllowance(policyId: Int): Query0[BaggageAllowanceDTO] = {
    sql"SELECT baggage_type, kgs, height, width, depth FROM baggage_allowance WHERE policy_id=$policyId"
      .query[BaggageAllowanceDTO]
  }

} 

package com.smartbackpackerapp.repository

import cats.MonadError
import cats.syntax.applicative._
import cats.syntax.applicativeError._
import cats.syntax.option.none
import com.smartbackpackerapp.model.{CountryCode, VisaRequirementsData}
import com.smartbackpackerapp.repository.algebra.VisaRequirementsRepository
import doobie.free.connection.ConnectionIO
import doobie.implicits._
import doobie.util.invariant.UnexpectedEnd
import doobie.util.query.Query0
import doobie.util.transactor.Transactor

class PostgresVisaRequirementsRepository[F[_]](xa: Transactor[F])
                                              (implicit F: MonadError[F, Throwable]) extends VisaRequirementsRepository[F] {

  override def findVisaRequirements(from: CountryCode, to: CountryCode): F[Option[VisaRequirementsData]] = {
    val program: ConnectionIO[VisaRequirementsData] =
      for {
        f <- VisaRequirementsStatement.from(from).unique
        t <- VisaRequirementsStatement.to(to).unique
        v <- VisaRequirementsStatement.visaRequirements(f.head, t.head).unique
      } yield v.toVisaRequirementsData(f, t)

    program.map(Option.apply).transact(xa).recoverWith {
      case UnexpectedEnd => none[VisaRequirementsData].pure[F]
    }
  }

}

object VisaRequirementsStatement {

  def from(from: CountryCode): Query0[CountryDTO] = {
    sql"SELECT id, code, name, currency FROM countries WHERE code = ${from.value}"
      .query[CountryDTO]
  }

  def to(to: CountryCode): Query0[CountryDTO] = {
    sql"SELECT id, code, name, currency FROM countries WHERE code = ${to.value}"
      .query[CountryDTO]
  }

  def visaRequirements(idFrom: Int, idTo: Int): Query0[VisaRequirementsDTO] =
    sql"SELECT vc.name AS category, vr.description FROM visa_requirements AS vr INNER JOIN visa_category AS vc ON vr.visa_category = vc.id WHERE vr.from_country = $idFrom AND vr.to_country = $idTo"
      .query[VisaRequirementsDTO]

} 

package ch.epfl.bluebrain.nexus.cli.postgres

import java.time.OffsetDateTime

import cats.effect.{Blocker, IO}
import ch.epfl.bluebrain.nexus.cli.Console
import ch.epfl.bluebrain.nexus.cli.config.AppConfig
import ch.epfl.bluebrain.nexus.cli.modules.postgres.PostgresProjection
import ch.epfl.bluebrain.nexus.cli.modules.postgres.PostgresProjection.TimeMeta.javatime._
import ch.epfl.bluebrain.nexus.cli.sse.Offset
import doobie.util.transactor.Transactor
import fs2.io

//noinspection SqlNoDataSourceInspection
class PostgresProjectionSpec extends AbstractPostgresSpec {

  "A PostgresProjection" should {
    "project all schemas" in {
      import doobie.implicits._
      (xa: Transactor[IO], proj: PostgresProjection[IO]) =>
        for {
          _                                <- proj.run
          count                            <- sql"select count(id) from schemas;".query[Int].unique.transact(xa)
          _                                 = count shouldEqual 175
          maxImport                        <- sql"select id, count(import) from schema_imports group by id order by count desc limit 1;"
                                                .query[(String, Int)]
                                                .unique
                                                .transact(xa)
          (maxImportSchema, maxImportCount) = maxImport
          _                                 = maxImportSchema shouldEqual "https://neuroshapes.org/commons/entity"
          _                                 = maxImportCount shouldEqual 7
          lastUpdated                      <- sql"select last_updated from schemas where id = 'https://neuroshapes.org/commons/entity'"
                                                .query[OffsetDateTime]
                                                .unique
                                                .transact(xa)
          _                                 = lastUpdated.toInstant.toEpochMilli shouldEqual 1584615316089L
        } yield ()
    }
    "save offset" in { (cfg: AppConfig, blocker: Blocker, proj: PostgresProjection[IO], console: Console[IO]) =>
      implicit val b: Blocker     = blocker
      implicit val c: Console[IO] = console

      for {
        _      <- proj.run
        exists <- io.file.exists[IO](blocker, cfg.postgres.offsetFile)
        _       = exists shouldEqual true
        offset <- Offset.load(cfg.postgres.offsetFile)
        _       = offset.nonEmpty shouldEqual true
      } yield ()
    }
  }
} 

package com.smartbackpackerapp.repository

import cats.MonadError
import cats.syntax.applicative._
import cats.syntax.applicativeError._
import cats.syntax.option._
import com.smartbackpackerapp.model._
import com.smartbackpackerapp.repository.algebra.VisaRestrictionsIndexRepository
import doobie.implicits._
import doobie.util.invariant.UnexpectedEnd
import doobie.util.query.Query0
import doobie.util.transactor.Transactor

class PostgresVisaRestrictionsIndexRepository[F[_]](xa: Transactor[F])
                                                   (implicit F: MonadError[F, Throwable]) extends VisaRestrictionsIndexRepository[F] {

  override def findRestrictionsIndex(countryCode: CountryCode): F[Option[VisaRestrictionsIndex]] = {
    val index = VisaRestrictionsIndexStatement.findIndex(countryCode).unique

    index.map(_.toVisaRestrictionsIndex).map(Option.apply).transact(xa).recoverWith {
      case UnexpectedEnd => none[VisaRestrictionsIndex].pure[F]
    }
  }

}

object VisaRestrictionsIndexStatement {

  def findIndex(countryCode: CountryCode): Query0[RestrictionsIndexDTO] =
    sql"SELECT rank, acc, sharing FROM visa_restrictions_index WHERE country_code = ${countryCode.value}"
      .query[RestrictionsIndexDTO]

} 

package com.smartbackpackerapp.repository

import cats.Monad
import com.smartbackpackerapp.model._
import com.smartbackpackerapp.repository.algebra.CountryRepository
import doobie.free.connection.ConnectionIO
import doobie.implicits._
import doobie.util.query.Query0
import doobie.util.transactor.Transactor

class PostgresCountryRepository[F[_] : Monad](xa: Transactor[F]) extends CountryRepository[F] {

  private def findCountries(query: ConnectionIO[List[CountryDTO]]) = {
    query.map(_.map(_.toCountry)).transact(xa)
  }

  override def findAll: F[List[Country]] = {
    findCountries(CountryStatement.findCountries.to[List])
  }

  override def findSchengen: F[List[Country]] = {
    findCountries(CountryStatement.findSchengen.to[List])
  }

}

object CountryStatement {

  val findCountries: Query0[CountryDTO] = {
    sql"SELECT id, code, name, currency FROM countries ORDER BY name"
      .query[CountryDTO]
  }

  val findSchengen: Query0[CountryDTO] = {
    sql"SELECT id, code, name, currency FROM countries WHERE schengen ORDER BY name"
      .query[CountryDTO]
  }

} 

package com.smartbackpackerapp.scraper.sql

import cats.effect.Async
import cats.instances.list._
import cats.syntax.apply._
import cats.syntax.flatMap._
import com.smartbackpackerapp.model._
import com.smartbackpackerapp.scraper.config.ScraperConfiguration
import doobie.free.connection.ConnectionIO
import doobie.implicits._
import doobie.util.transactor.Transactor
import doobie.util.update.Update

class CountryInsertData[F[_]](scraperConfig: ScraperConfiguration[F],
                              xa : Transactor[F])
                             (implicit F: Async[F]) {

  private def insertCountriesBulk(countries: List[Country]): ConnectionIO[Int] = {
    CountryInsertStatement.insertCountries
      .updateMany(countries.map(c => (c.code.value, c.name.value, c.currency.value, false)))
  }

  private def updateCountriesCurrencyBulk(countries: List[Country]): ConnectionIO[Int] = {
    CountryInsertStatement.updateCountriesCurrency
      .updateMany(countries.map(c => (c.currency.value, c.code.value)))
  }

  private def updateSchengenCountriesBulk(countries: List[CountryCode]): ConnectionIO[Int] = {
    CountryInsertStatement.updateSchengenCountries
      .updateMany(countries.map(_.value))
  }

  private def runSchengenUpdate: F[Unit] = {
    scraperConfig.schengen() flatMap { countries =>
      updateSchengenCountriesBulk(countries).transact(xa) *> F.unit
    }
  }

  private def runCurrencyUpdate: F[Unit] = {
    scraperConfig.countries() flatMap { countries =>
      updateCountriesCurrencyBulk(countries).transact(xa) *> F.unit
    }
  }

  def runUpdate: F[Unit] = {
    runSchengenUpdate.flatMap(_ => runCurrencyUpdate)
  }

  def run: F[Unit] = {
    scraperConfig.countries() flatMap { countries =>
      insertCountriesBulk(countries).transact(xa) *> F.unit
    }
  }

}

object CountryInsertStatement {

  type CountryDTO       = (String, String, String, Boolean)
  type CurrencyQueryDTO = (String, String)

  val insertCountries: Update[CountryDTO] = {
    val sql = "INSERT INTO countries (code, name, currency, schengen) VALUES (?, ?, ?, ?)"
    Update[CountryDTO](sql)
  }

  val updateCountriesCurrency: Update[CurrencyQueryDTO] = {
    val sql = "UPDATE countries SET currency = ? WHERE code = ?"
    Update[CurrencyQueryDTO](sql)
  }

  val updateSchengenCountries: Update[String] = {
    val sql = "UPDATE countries SET schengen = 't' WHERE code = ?"
    Update[String](sql)
  }

} 

package com.smartbackpackerapp.scraper.sql

import cats.effect.Async
import cats.instances.list._
import cats.syntax.apply._
import com.smartbackpackerapp.model._
import doobie.implicits._
import doobie.util.transactor.Transactor
import doobie.util.update.Update

class VisaRestrictionsIndexInsertData[F[_]](xa: Transactor[F])(implicit F: Async[F]) {

  private def insertVisaIndexBulk(list: List[(CountryCode, VisaRestrictionsIndex)]) = {
    VisaRestrictionsIndexInsertStatement.insertVisaIndex
      .updateMany(list.map { case (code, index) =>
        (code.value, index.rank.value, index.count.value, index.sharing.value)
      })
  }

  def run(list: List[(CountryCode, VisaRestrictionsIndex)]): F[Unit] = {
    // Netherlands is duplicated in ranking 2018
    insertVisaIndexBulk(list.toSet.toList).transact(xa) *> F.unit
  }

}

object VisaRestrictionsIndexInsertStatement {

  type CreateVisaIndexDTO = (String, Int, Int, Int)

  val insertVisaIndex: Update[CreateVisaIndexDTO] = {
    val sql = "INSERT INTO visa_restrictions_index (country_code, rank, acc, sharing) VALUES (?, ?, ?, ?)"
    Update[CreateVisaIndexDTO](sql)
  }

} 

package com.smartbackpackerapp.scraper.sql

import java.sql.BatchUpdateException

import cats.effect.Async
import cats.instances.list._
import cats.syntax.applicativeError._
import cats.syntax.flatMap._
import cats.syntax.functor._
import com.smartbackpackerapp.common.Log
import com.smartbackpackerapp.model._
import com.smartbackpackerapp.scraper.model._
import com.smartbackpackerapp.scraper.parser.AbstractVisaRequirementsParser
import doobie.implicits._
import doobie.util.transactor.Transactor
import doobie.util.update.Update

class VisaRequirementsInsertData[F[_] : Async](xa: Transactor[F],
                                               visaRequirementsParser: AbstractVisaRequirementsParser[F])
                                              (implicit L: Log[F]) {

  private def insertVisaRequirementsBulk(list: List[VisaRequirementsFor]) = {
    VisaRequirementsInsertStatement.insertVisaRequirements
      .updateMany(list.map(_.toVisaRequirementsDTO))
  }

  // For example Algerian Wiki page has Burundi duplicated
  private val errorHandler: PartialFunction[Throwable, F[Unit]] = {
    case e: BatchUpdateException if e.getCause.getMessage.contains("duplicate key value") => L.error(e)
    case e: WikiPageNotFound => L.error(e)
  }

  def run(from: CountryCode): F[Unit] = {
    val program = for {
      _   <- L.info(s"${from.value} >> Gathering visa requirements from Wikipedia")
      req <- visaRequirementsParser.visaRequirementsFor(from)
      _   <- L.info(s"${from.value} >> Starting data insertion into DB")
      rs  <- insertVisaRequirementsBulk(req).transact(xa)
      _   <- L.info(s"${from.value} >> Created $rs records")
    } yield ()
    program.recoverWith(errorHandler)
  }

}

object VisaRequirementsInsertStatement {

  val insertVisaRequirements: Update[VisaRequirementsDTO] = {
    val sql =
      """
        |WITH from_view AS (
        |  SELECT id AS from_id FROM countries WHERE code = ?
        |),
        |to_view AS (
        |  SELECT id AS to_id FROM countries WHERE code = ?
        |),
        |visa_cat_view AS (
        |  SELECT id AS visa_id FROM visa_category WHERE name = ?
        |),
        |desc_view AS (
        |  SELECT ? AS description
        |)
        |INSERT INTO visa_requirements (from_country, to_country, visa_category, description)
        |SELECT from_id, to_id, visa_id, description FROM from_view, to_view, visa_cat_view, desc_view
      """.stripMargin
    Update[VisaRequirementsDTO](sql)
  }

} 

package com.smartbackpackerapp.scraper.sql

import cats.effect.Async
import cats.instances.list._
import cats.syntax.apply._
import com.smartbackpackerapp.model._
import doobie.implicits._
import doobie.util.transactor.Transactor
import doobie.util.update.Update

import scala.reflect.runtime.{universe => ru}

class VisaCategoryInsertData[F[_]](xa : Transactor[F])(implicit F: Async[F]) {

  private def insertVisaCategoriesBulk(categories: List[String]) = {
    VisaCategoryInsertStatement.insertVisaCategories.updateMany(categories)
  }

  private def visaCategories: List[String] = {
    val tpe   = ru.typeOf[VisaCategory]
    val clazz = tpe.typeSymbol.asClass
    clazz.knownDirectSubclasses.map(_.name.toString).toList
  }

  def run: F[Unit] = {
    insertVisaCategoriesBulk(visaCategories).transact(xa) *> F.unit
  }

}

object VisaCategoryInsertStatement {

  val insertVisaCategories: Update[String] = {
    val sql = "INSERT INTO visa_category (name) VALUES (?)"
    Update[String](sql)
  }

} 

package com.smartbackpackerapp.common.sql

import cats.effect.IO
import doobie.h2.H2Transactor
import doobie.util.transactor.Transactor
import org.flywaydb.core.Flyway

object TestDBManager {

  private def testDbUrl(dbName: String): String =
    s"jdbc:h2:mem:test_sb_$dbName;MODE=PostgreSQL;DB_CLOSE_DELAY=-1"

  private val testDbUser = "sa"
  private val testDbPass = ""

  def xa(dbName: String): IO[Transactor[IO]] =
    H2Transactor.newH2Transactor[IO](testDbUrl(dbName), testDbUser, testDbPass)

  def createTables(dbName: String): IO[Unit] =
    IO {
      val flyway = new Flyway
      flyway.setDataSource(testDbUrl(dbName), testDbUser, testDbPass)
      flyway.migrate()
    }

} 

package org.amitayh.invoices.dao

import cats.Monad
import cats.effect.{Async, ContextShift, Resource}
import cats.syntax.functor._
import doobie.free.connection.ConnectionIO
import doobie.hikari.HikariTransactor
import doobie.implicits._
import doobie.util.ExecutionContexts
import doobie.util.transactor.Transactor

class MySqlInvoiceList[F[_]: Monad](transactor: Transactor[F]) extends InvoiceList[F] {
  override def save(record: InvoiceRecord): F[Unit] =
    MySqlInvoiceList.save(record).transact(transactor)

  override def get: F[List[InvoiceRecord]] =
    MySqlInvoiceList.get.transact(transactor)
}

object MySqlInvoiceList {
  def save(record: InvoiceRecord): ConnectionIO[Unit] = {
    import record._
    val sql = sql"""
      INSERT INTO invoices (id, version, updated_at, customer_name, customer_email, issue_date, due_date, total, status)
      VALUES ($id, $version, $updatedAt, $customerName, $customerEmail, $issueDate, $dueDate, $total, $status)
      ON DUPLICATE KEY UPDATE
        version = VALUES(version),
        updated_at = VALUES(updated_at),
        customer_name = VALUES(customer_name),
        customer_email = VALUES(customer_email),
        issue_date = VALUES(issue_date),
        due_date = VALUES(due_date),
        total = VALUES(total),
        status = VALUES(status)
    """
    sql.update.run.void
  }

  def get: ConnectionIO[List[InvoiceRecord]] = {
    val sql = sql"""
      SELECT id, version, updated_at, customer_name, customer_email, issue_date, due_date, total, status
      FROM invoices
      WHERE status IN ('New', 'Paid')
      ORDER BY created_at DESC
    """
    sql.query[InvoiceRecord].to[List]
  }

  def resource[F[_]: Async: ContextShift]: Resource[F, MySqlInvoiceList[F]] = for {
    connectEC <- ExecutionContexts.fixedThreadPool[F](32)
    transactEC <- ExecutionContexts.cachedThreadPool[F]
    transactor <- HikariTransactor.newHikariTransactor[F](
      driverClassName = sys.env("DB_DRIVER"),
      url = sys.env("DB_URL"),
      user = sys.env("DB_USER"),
      pass = sys.env("DB_PASS"),
      connectEC = connectEC,
      transactEC = transactEC)
  } yield new MySqlInvoiceList[F](transactor)
} 

package com.avast.sst.example.service

import doobie.implicits._
import doobie.util.transactor.Transactor
import zio.Task
import zio.interop.catz._

trait RandomService {

  def randomNumber: Task[Double]

}

object RandomService {

  def apply(transactor: Transactor[Task]): RandomService =
    new RandomService {
      override def randomNumber: Task[Double] = {
        sql"select random()"
          .query[Double]
          .unique
          .transact(transactor)
      }
    }

} 

package store.postgresql

import akka.actor.ActorSystem
import cats.effect.Blocker
import doobie.util.transactor.Transactor
import doobie.util.transactor.Transactor.Aux
import env.PostgresqlConfig
import javax.sql.DataSource
import libs.logs.{IzanamiLogger, ZLogger}
import play.api.db.{Database, Databases}
import zio.{Task, UIO, ZManaged}
import zio.interop.catz._

import scala.concurrent.ExecutionContext

case class PostgresqlClient(database: Database, transactor: Transactor[Task])

object PostgresqlClient {

  def postgresqlClient(system: ActorSystem,
                       cf: Option[PostgresqlConfig]): ZManaged[ZLogger, Throwable, Option[PostgresqlClient]] =
    cf.map { config =>
        ZManaged
          .make(
            ZLogger.info(s"Creating database instance") *>
            Task {
              Databases(
                config.driver,
                config.url,
                config = Map(
                  "username" -> config.username,
                  "password" -> config.password,
                  "pool"     -> "hikaricp"
                )
              )
            }
          )(database => UIO(database.shutdown()))
          .mapM { database =>
            ZLogger.info(s"Creating transactor instance") *>
            Task {
              val ce: ExecutionContext = system.dispatchers.lookup("izanami.jdbc-connection-dispatcher")
              val te: ExecutionContext = system.dispatchers.lookup("izanami.jdbc-transaction-dispatcher")
              val transact: Aux[Task, DataSource] = Transactor
                .fromDataSource[Task](database.dataSource, ce, Blocker.liftExecutionContext(te))
              Some(PostgresqlClient(database, transact))
            }
          }
      }
      .getOrElse(ZManaged.effectTotal(None))
} 

package examples.todolist

import cats._
import cats.effect.IO
import cats.implicits._
import com.twitter.finagle.http.{Request, Response}
import com.twitter.finagle.{Http, ListeningServer, Service}
import com.twitter.server.TwitterServer
import com.twitter.util.{Await, Future}
import doobie.util.transactor.Transactor
import examples.todolist.http.Api
import examples.todolist.persistence.Persistence
import examples.todolist.services.Services
import freestyle.tagless.config.ConfigM
import freestyle.tagless.config.implicits._
import freestyle.tagless.effects.error.ErrorM
import freestyle.tagless.effects.error.implicits._
import freestyle.tagless.logging.LoggingM
import freestyle.tagless.loggingJVM.log4s.implicits._
import freestyle.tagless.module
import io.circe.generic.auto._
import io.finch.circe._

@module
trait App[F[_]] {
  val persistence: Persistence[F]
  val services: Services[F]
}

object TodoListApp extends TwitterServer {

  import examples.todolist.runtime.implicits._

  def bootstrap[F[_]: Monad](
      implicit app: App[F],
      handler: F ~> Future,
      T: Transactor[F],
      api: Api[F]): F[ListeningServer] = {

    val service: Service[Request, Response] = api.endpoints.toService
    val log: LoggingM[F]                    = app.services.log
    val cfg: ConfigM[F]                     = app.services.config

    for {
      _      <- log.info("Trying to load application.conf")
      config <- cfg.load
      host = config.string("http.host").getOrElse("localhost")
      port = config.int("http.port").getOrElse("8080")
      _ <- log.debug(s"Host: $host")
      _ <- log.debug(s"Port $port")
    } yield
      Await.ready(
        Http.server.withAdmissionControl
          .concurrencyLimit(maxConcurrentRequests = 10, maxWaiters = 10)
          .serve(s"$host:$port", service)
      )
  }

  def main() =
    bootstrap[IO].unsafeRunAsync {
      case Left(error)   => println(s"Error executing server. ${error.getMessage}")
      case Right(server) => server.close()
    }

} 

package jbok.app.service.store.doobie

import cats.effect.Sync
import cats.implicits._
import doobie.implicits._
import doobie.util.transactor.Transactor
import doobie.util.update.Update
import jbok.app.service.store.TransactionStore
import jbok.common.math.N
import jbok.core.api.HistoryTransaction
import jbok.core.models.{Receipt, Block => CoreBlock}

final class DoobieTransactionStore[F[_]](xa: Transactor[F])(implicit F: Sync[F]) extends TransactionStore[F] with DoobieSupport {
  def findTransactionsByAddress(address: String, page: Int, size: Int): F[List[HistoryTransaction]] =
    sql"""
       SELECT txHash, nonce, fromAddress, toAddress, value, payload, v, r, s, gasUsed, gasPrice, blockNumber, blockHash, location
       FROM transactions
       WHERE (fromAddress = ${address.toString} OR toAddress = ${address.toString})
       ORDER BY blockNumber, location DESC
       limit ${size} offset ${(page - 1) * size}
      """
      .query[HistoryTransaction]
      .to[List]
      .transact(xa)

  def findTransactionByHash(txHash: String): F[Option[HistoryTransaction]] =
    sql"""
       SELECT txHash, nonce, fromAddress, toAddress, value, payload, v, r, s, gasUsed, gasPrice, blockNumber, blockHash, location
       FROM transactions
       WHERE txHash = ${txHash}
       """
      .query[HistoryTransaction]
      .option
      .transact(xa)

  override def findTransactionsByNumber(blockNumber: Int): F[List[HistoryTransaction]] =
    sql"""
       SELECT txHash, nonce, fromAddress, toAddress, value, payload, v, r, s, gasUsed, gasPrice, blockNumber, blockHash, location
       FROM transactions
       WHERE (blockNumber = ${blockNumber})
       ORDER BY blockNumber, location DESC
      """
      .query[HistoryTransaction]
      .to[List]
      .transact(xa)

  override def delByBlockNumber(number: N): F[Unit] =
    sql"""DELETE from transactions WHERE blockNumber = $number""".update.run.void.transact(xa)

  def insertBlockTransactions(block: CoreBlock, receipts: List[Receipt]): F[Unit] = {
    require(block.body.transactionList.length == receipts.length)
    val xs = block.body.transactionList.zip(receipts).zipWithIndex.map {
      case ((stx, receipt), idx) =>
        (
          stx.hash.toHex,
          stx.nonce.toInt,
          stx.senderAddress.map(_.toString).getOrElse(""),
          stx.receivingAddress.toString,
          stx.value.toString,
          stx.payload.toHex,
          stx.v.toString,
          stx.r.toString,
          stx.s.toString,
          receipt.gasUsed.toString,
          stx.gasPrice.toString,
          block.header.number.toLong,
          block.header.hash.toHex,
          idx
        )
    }
    val holes = List.fill(14)("?").mkString(",")
    val sql =
      s"insert into transactions (txHash, nonce, fromAddress, toAddress, value, payload, v, r, s, gasUsed, gasPrice, blockNumber, blockHash, location) values ($holes)"
    Update[(String, Int, String, String, String, String, String, String, String, String, String, Long, String, Int)](sql)
      .updateMany(xs)
      .transact(xa)
      .void
  }
} 

package jbok.app.service.store.doobie

import cats.effect.Sync
import cats.implicits._
import doobie.implicits._
import doobie.util.transactor.Transactor
import jbok.app.service.store.BlockStore
import jbok.common.math.N
import scodec.bits.ByteVector

final class DoobieBlockStore[F[_]](xa: Transactor[F])(implicit F: Sync[F]) extends BlockStore[F] with DoobieSupport {
  override def getBestBlockNumber: F[Option[N]] =
    sql"""
      SELECT blockNumber
      FROM blocks
      ORDER BY blockNumber DESC
      LIMIT 1
      """
      .query[N]
      .option
      .transact(xa)

  override def getBestBlockNumberAndHash: F[(N, ByteVector)] =
    sql"""
      SELECT blockNumber, blockHash
      FROM blocks
      ORDER BY blockNumber DESC
      LIMIT 1
      """
      .query[(N, ByteVector)]
      .unique
      .transact(xa)

  override def getBlockHashByNumber(number: N): F[Option[ByteVector]] =
    sql"""
      SELECT blockHash
      FROM blocks
      WHERE blockNumber = $number
      """
      .query[ByteVector]
      .option
      .transact(xa)

  override def delByBlockNumber(number: N): F[Unit] =
    sql"""
      DELETE FROM blocks where blockNumber = $number
      """.update.run
      .transact(xa)
      .void

  override def insert(number: N, hash: ByteVector): F[Unit] =
    sql"""
      INSERT INTO blocks (blockNumber, blockHash) VALUES ($number, $hash)
      """.update.run.void.transact(xa)
} 

package com.wix.zorechka.repos

import doobie.util.transactor.Transactor
import doobie.util.update.Update0
import doobie.implicits._
import zio.{RIO, Task, ZIO}
import zio.interop.catz._

trait UnusedDepCache {
  val cache: UnusedDepCache.Service
}

object UnusedDepCache {
  trait Service {
    def isCached(githubRepo: String, target: String, hash: String): Task[Boolean]
    def cache(githubRepo: String, target: String, hash: String): Task[Unit]
  }

  trait MysqlUnusedDepCache extends UnusedDepCache {
    protected val tnx: Transactor[Task]

    override val cache: Service = new Service {
      def createTable: Task[Unit] = SQL.createTable.run
        .transact(tnx)
        .foldM(err => Task.fail(err), _ => Task.succeed(()))

      override def isCached(githubRepo: String, target: String, hash: String): Task[Boolean] = SQL.isCached(githubRepo, target, hash)
        .option
        .transact(tnx)
        .map(_.exists(_ == 1))

      override def cache(githubRepo: String, target: String, hash: String): Task[Unit] =
        SQL.insertOrUpdate(githubRepo, target, hash).run
          .transact(tnx)
          .foldM(err => Task.fail(err), _ => Task.succeed(()))
    }
  }

  object SQL {
    def createTable: Update0 = sql"""CREATE TABLE IF NOT EXISTS `unused_deps` (
      `github_repo` varchar(1000) NOT NULL,
      `build_target` varchar(1000) NOT NULL,
      `hash` varchar(256) NOT NULL,
      PRIMARY KEY (`github_repo`, `build_target`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;""".update

    def insertOrUpdate(githubRepo: String, target: String, hash: String): doobie.Update0 =
      sql"""INSERT INTO `unused_deps` (`github_repo`, `build_target`, `hash`) VALUES ($githubRepo, $target, $hash)
           |ON DUPLICATE KEY UPDATE hash = VALUES(hash)
           |""".stripMargin.update

    def isCached(githubRepo: String, target: String, hash: String): doobie.Query0[Int] = {
      sql"""SELECT 1 FROM `unused_deps` WHERE github_repo = $githubRepo AND build_target = $target AND hash = $hash""".query[Int]
    }
  }

  // helpers
  def isCached(githubRepo: String, target: String, hash: String): RIO[UnusedDepCache, Boolean] =
    ZIO.accessM(env => env.cache.isCached(githubRepo, target, hash))

  def cache(githubRepo: String, target: String, hash: String): RIO[UnusedDepCache, Unit] =
    ZIO.accessM(env => env.cache.cache(githubRepo, target, hash))
} 

package io.opencensus.scala.doobie

import cats.Monad
import cats.effect.Bracket
import doobie.ConnectionIO
import doobie.util.transactor.Transactor
import io.opencensus.trace.Span

object implicits {
  implicit class ConnectionTracingOps[A](ma: ConnectionIO[A]) {
    def tracedTransact[M[_]: Monad](
        xa: Transactor[M],
        transactionName: String
    )(implicit ev: Bracket[M, Throwable]): M[A] =
      xa.trans.apply(ConnectionIOTracing.traceF(ma, transactionName, None))

    def tracedTransact[M[_]: Monad](
        xa: Transactor[M],
        transactionName: String,
        parentSpan: Span
    )(implicit ev: Bracket[M, Throwable]): M[A] =
      xa.trans.apply(
        ConnectionIOTracing.traceF(ma, transactionName, Some(parentSpan))
      )
  }
} 

package daf.dataset.query.jdbc

import cats.effect.IO
import cats.instances.list.catsStdInstancesForList
import daf.dataset.query.{ Count, GroupByClause, Gt, NamedColumn, Query, SelectClause, ValueColumn, WhereClause }
import daf.instances.H2TransactorInstance
import doobie.free.KleisliInterpreter
import doobie.free.connection.AsyncConnectionIO
import doobie.implicits.{ toConnectionIOOps, toSqlInterpolator }
import doobie.util.query.Query0
import doobie.util.transactor.{ Strategy, Transactor }
import doobie.util.update.Update
import org.apache.commons.dbcp.BasicDataSource
import org.scalatest.{ BeforeAndAfterAll, MustMatchers, WordSpec }

class JdbcQueryServiceSpec extends WordSpec with MustMatchers with BeforeAndAfterAll {

  private lazy val service = new JdbcQueryService(null, None) with H2TransactorInstance

  override def beforeAll(): Unit = JdbcQueries.prepare.transact { service.transactor("") }.unsafeRunSync() match {
    case (_     , rows) if rows == 0   => throw new RuntimeException("Unable to start test: [rows] were not created")
    case (_, _)                        => // do nothing
  }

  "A jdbc query service" must {

    "run queries" in  {
      service.exec(JdbcQueries.select, "user", "").map { _.toCsv.toList }.get must be {
        List(
          """"COUNTRY", "COUNTS"""",
          """"Italy", 2""",
          """"Netherlands", 1"""
        )
      }
    }
  }
}

object JdbcQueries {

  type User = (String, String, Int, String)

  private def createTransactor(dataSource: BasicDataSource) = Transactor[IO, BasicDataSource](
    dataSource, a => IO(a.getConnection), KleisliInterpreter[IO].ConnectionInterpreter, Strategy.void
  )

  val ddl =
    sql"""
      CREATE TABLE user(
        id VARCHAR,
        username VARCHAR,
        age SMALLINT,
        country VARCHAR
      )
    """.update.run

  Query0.apply("").stream

  val insert =
    Update[User]("INSERT INTO user(id, username, age, country) VALUES (?, ?, ?, ?)").updateMany[List] {
      List(
        ("id1", "user1", 42, "Italy"),
        ("id2", "user2", 32, "Italy"),
        ("id3", "user3", 27, "Italy"),
        ("id4", "user4", 33, "Netherlands")
      )
    }

  val prepare = for {
    table  <- JdbcQueries.ddl
    insert <- JdbcQueries.insert
  } yield (table, insert)

  val select = Query(
    select  = SelectClause {
      Seq(
        NamedColumn("country"), Count(NamedColumn("id")) as "counts"
      )
    },
    where   = Some {
      WhereClause { Gt(NamedColumn("age"), ValueColumn(30)) }
    },
    groupBy = Some {
      GroupByClause { Seq(NamedColumn("country")) }
    },
    having = None,
    limit  = None
  )

} 

package pms.db.config

import pms.effects._
import pms.effects.implicits._
import doobie.util.transactor.Transactor
import busymachines.pureharm.db.flyway._


object DatabaseConfigAlgebra {

  def transactor[F[_]: Async: ContextShift](config: DatabaseConfig): F[Transactor[F]] = Async[F].delay {
    Transactor.fromDriverManager[F](
      driver = "org.postgresql.Driver",
      url    = config.connection.jdbcURL: String,
      user   = config.connection.username: String,
      pass   = config.connection.password: String,
    )
  }

  def initializeSQLDb[F[_]: Sync](config: DatabaseConfig): F[Int] = {
    val clean =
      if (config.forceClean)
        Flyway.clean(config.connection)
      else Sync[F].unit

    clean *> Flyway.migrate(config.connection, config.flyway)
  }
} 

package pms.server

import pms.effects._
import pms.effects.implicits._
import pms.email._
import pms.algebra.user._
import pms.algebra.imdb.IMDBAlgebraConfig
import pms.server.bootstrap._
import doobie.util.transactor.Transactor
import pms.core.Module


trait ModulePureMovieServerBootstrap[F[_]]
  extends Module[F] with ModulePureMovieServer[F] with ModuleServerBootstrap[F] with ModuleUserBootstrap[F] {

  def bootstrap: F[Unit] =
    serverBootstrapAlgebra.flatMap(sba => Bootstrap.bootstrap(sba))
}

object ModulePureMovieServerBootstrap {

  def concurrent[F[_]](
    gConfig:           GmailConfig,
    imbdAlgebraConfig: IMDBAlgebraConfig,
  )(implicit
    c:                 Concurrent[F],
    t:                 Transactor[F],
    ti:                Timer[F],
  ): F[ModulePureMovieServerBootstrap[F]] = c.delay {
    new ModulePureMovieServerBootstrap[F] {
      implicit override def F: Concurrent[F] = c

      implicit override def timer: Timer[F] = ti

      override def gmailConfig: GmailConfig = gConfig

      override def imdbAlgebraConfig: IMDBAlgebraConfig = imbdAlgebraConfig

      implicit override def transactor: Transactor[F] = t

    }
  }

} 

package pms.server

import doobie.util.transactor.Transactor
import org.http4s._

import pms.core.Module
import pms.effects._
import pms.effects.implicits._
import pms.email._
import pms.algebra.user._
import pms.algebra.imdb._
import pms.algebra.movie._
import pms.algebra.http._
import pms.service.user._
import pms.service.user.rest._
import pms.service.movie._
import pms.service.movie.rest._


trait ModulePureMovieServer[F[_]]
  extends Module[F] with ModuleEmail[F] with ModuleUserAlgebra[F] with ModuleIMDBAlgebra[F] with ModuleMovieAlgebra[F]
  with ModuleUserService[F] with ModuleMovieService[F] with ModuleUserRest[F] with ModuleMovieRest[F] {

  implicit override def F: Concurrent[F]

  override def gmailConfig: GmailConfig

  override def imdbAlgebraConfig: IMDBAlgebraConfig

  implicit override def timer: Timer[F]

  implicit override def transactor: Transactor[F]

  def pureMovieServerRoutes: F[HttpRoutes[F]] = _pureMovieServerRoutes

  //we could delay this even more, but there is little point.
  private lazy val authCtxMiddleware: F[AuthCtxMiddleware[F]] = singleton {
    userAuthAlgebra.map(uaa => AuthedHttp4s.userTokenAuthMiddleware[F](uaa))
  }

  private lazy val _pureMovieServerRoutes: F[HttpRoutes[F]] = singleton {
    for {
      umr <- userModuleRoutes
      routes = NonEmptyList.of[HttpRoutes[F]](umr).reduceK

      mmas <- movieModuleAuthedRoutes
      umar <- userModuleAuthedRoutes
      authed = NonEmptyList.of[AuthCtxRoutes[F]](umar, mmas).reduceK

      middleware <- authCtxMiddleware
      toCombine: HttpRoutes[F] = middleware(authed)
    } yield {
      routes <+> toCombine
    }

  }
}

object ModulePureMovieServer {

  def concurrent[F[_]](gConfig: GmailConfig, imbdAlgebraConfig: IMDBAlgebraConfig)(implicit
    c:                          Concurrent[F],
    t:                          Transactor[F],
    ti:                         Timer[F],
  ): F[ModulePureMovieServer[F]] = c.delay {
    new ModulePureMovieServer[F] {
      override def F: Concurrent[F] = c

      implicit override def timer: Timer[F] = ti

      override def gmailConfig: GmailConfig = gConfig

      override def imdbAlgebraConfig: IMDBAlgebraConfig = imbdAlgebraConfig

      implicit override def transactor: Transactor[F] = t

    }
  }

} 

package pms.server

import pms.effects._
import pms.effects.implicits._
import pms.logger._
import pms.email._
import pms.db.config._
import pms.algebra.imdb.IMDBAlgebraConfig

import doobie.util.transactor.Transactor


final class PureMovieServer[F[_]] private (
  private val timer:          Timer[F],
  private val dbContextShift: ContextShift[F],
)(implicit
  private val F:              Concurrent[F]
) {
  private val logger: PMSLogger[F] = PMSLogger.getLogger[F]

  def init: F[(PureMovieServerConfig, ModulePureMovieServer[F])] =
    for {
      serverConfig      <- PureMovieServerConfig.default[F]
      gmailConfig       <- GmailConfig.default[F]
      imdbAlgebraConfig <- IMDBAlgebraConfig.default[F]
      dbConfig          <- DatabaseConfig.default[F]
      transactor        <- DatabaseConfigAlgebra.transactor[F](dbConfig)(F, dbContextShift)
      nrOfMigs          <- DatabaseConfigAlgebra.initializeSQLDb[F](dbConfig)
      _                 <- logger.info(s"Successfully ran #$nrOfMigs migrations")
      pmsModule         <- moduleInit(
        gmailConfig,
        imdbAlgebraConfig,
        bootstrap = serverConfig.bootstrap,
      )(transactor, timer)
      _                 <- logger.info(s"Successfully initialized pure-movie-server")
    } yield (serverConfig, pmsModule)

  private def moduleInit(
    gmailConfig:      GmailConfig,
    imdblgebraConfig: IMDBAlgebraConfig,
    bootstrap:        Boolean,
  )(implicit
    transactor:       Transactor[F],
    timer:            Timer[F],
  ): F[ModulePureMovieServer[F]] =
    if (bootstrap) {
      //TODO: add better logic for handling boostrap being applied twice
      // important aspects to consider:
      //  1) give good diagnostics of what specifically went wrong so that the developer knows what's up
      //  2) distinguish between recoverable errors in bootstrap, and non-recoverable errors
      logger.warn(
        "BOOTSTRAP — initializing server in bootstrap mode — if this is on prod, you seriously botched this one"
      ) >> ModulePureMovieServerBootstrap
        .concurrent(gmailConfig, imdblgebraConfig)
        .flatTap(_.bootstrap)
        .widen[ModulePureMovieServer[F]]
    }
    else ModulePureMovieServer.concurrent(gmailConfig, imdblgebraConfig)

}

object PureMovieServer {

  def concurrent[F[_]: Concurrent](timer: Timer[F], dbContextShift: ContextShift[F]): F[PureMovieServer[F]] =
    Concurrent.apply[F].delay(new PureMovieServer[F](timer, dbContextShift))
} 

package ch14

import java.sql.SQLException

import cats.effect.IO
import ch14.Model.Inventory
import fs2.Stream
import doobie._
import doobie.implicits._
import doobie.util.transactor.Transactor
import cats.implicits._

class Repository(transactor: Transactor[IO]) {

  def deleteArticle(name: String): IO[Boolean] = {
    sql"DELETE FROM article WHERE name = $name".update.run
      .transact(transactor)
      .map { affectedRows =>
        affectedRows == 1
      }
  }

  def createArticle(name: String): IO[Boolean] = {
    sql"INSERT INTO article (name, count) VALUES ($name, 0)".update.run.attempt
      .transact(transactor)
      .map {
        case Right(affectedRows) => affectedRows == 1
        case Left(_)             => false
      }
  }

  def updateStock(inventory: Inventory): Stream[IO, Either[Throwable, Unit]] = {
    val updates = inventory
      .map {
        case (name, count) =>
          sql"UPDATE article set count = count + $count where name = $name".update.run
      }
      .reduce(_ *> _)
    Stream
      .eval(FC.setAutoCommit(false) *> updates *> FC.setAutoCommit(true))
      .attempt
      .transact(transactor)
  }

  def getInventory: Stream[IO, Inventory] =
    queryToInventory(inventoryQuery)

  def getArticle(name: String): Stream[IO, Inventory] =
    queryToInventory( sql"SELECT name, count FROM article where name = $name")

  private val inventoryQuery: Fragment = sql"SELECT name, count FROM article"

  private def queryToInventory(query: Fragment) =
    query
      .query[(String, Int)]
      .stream
      .transact(transactor)
      .fold(Map.empty[String, Int])(_ + _)
} 

package jbok.app

import java.nio.file.Path

import cats.effect._
import distage._
import doobie.util.transactor.Transactor
import jbok.app.service._
import jbok.app.service.store.doobie.{Doobie, DoobieBlockStore, DoobieTransactionStore}
import jbok.app.service.store.{BlockStore, Migration, TransactionStore}
import jbok.common.config.Config
import jbok.core.CoreModule
import jbok.core.api._
import jbok.core.config.FullConfig

class AppModule[F[_]: TagK](implicit F: ConcurrentEffect[F], cs: ContextShift[F]) extends ModuleDef {
  addImplicit[Bracket[F, Throwable]]

  make[Transactor[F]].fromResource((config: FullConfig) => Doobie.xa[F](config.db))
  make[Unit].fromEffect((config: FullConfig) => Migration.migrate[F](config.db))
  make[TransactionStore[F]].from[DoobieTransactionStore[F]]
  make[BlockStore[F]].from[DoobieBlockStore[F]]

  make[ServiceHelper[F]]
  make[AccountAPI[F]].from[AccountService[F]]
  make[AdminAPI[F]].from[AdminService[F]]
  make[BlockAPI[F]].from[BlockService[F]]
  make[ContractAPI[F]].from[ContractService[F]]
  make[MinerAPI[F]].from[MinerService[F]]
  make[PersonalAPI[F]].from[PersonalService[F]]
  make[TransactionAPI[F]].from[TransactionService[F]]
  make[HttpService[F]]

  make[StoreUpdateService[F]]
  make[FullNode[F]]
}

object AppModule {
  def resource[F[_]: TagK](config: FullConfig = CoreModule.testConfig)(implicit F: ConcurrentEffect[F], cs: ContextShift[F], T: Timer[F]): Resource[F, Locator] =
    Injector().produceF[F](new CoreModule[F](config) ++ new AppModule[F]).toCats

  def resource[F[_]: TagK](path: Path)(implicit F: ConcurrentEffect[F], cs: ContextShift[F], T: Timer[F]): Resource[F, Locator] =
    Resource.liftF(Config[F].read[FullConfig](path)).flatMap(config => resource[F](config))
} 

package todo

import cats.effect.{Effect, IO}
import cats.syntax.either._
import cats.syntax.flatMap._
import cats.syntax.functor._
import doobie.util.transactor.Transactor
import examples.todolist.http.Api
import examples.todolist.services.Services
import exapmles.todolist.peristence.Persistence
import freestyle.tagless.config.ConfigM
import freestyle.tagless.config.implicits._
import freestyle.tagless.effects.error.ErrorM
import freestyle.tagless.effects.error.implicits._
import freestyle.tagless.logging.LoggingM
import freestyle.tagless.loggingJVM.log4s.implicits._
import freestyle.tagless.module
import fs2.StreamApp
import org.http4s.HttpService
import org.http4s.implicits._
import org.http4s.server.blaze.BlazeBuilder

@module
trait App[F[_]] {
  val persistence: Persistence[F]
  val services: Services[F]
}

object TodoListApp extends StreamApp[IO] {

  import examples.todolist.runtime.implicits._

  override def stream(
      args: List[String],
      requestShutdown: IO[Unit]): fs2.Stream[IO, StreamApp.ExitCode] =
    bootstrap[IO].unsafeRunSync()

  def bootstrap[F[_]: Effect](
      implicit app: App[F],
      T: Transactor[F],
      api: Api[F]): F[fs2.Stream[F, StreamApp.ExitCode]] = {

    val services: HttpService[F] = api.endpoints
    val log: LoggingM[F]         = app.services.log
    val config: ConfigM[F]       = app.services.config

    for {
      _   <- log.info("Trying to load application.conf")
      cfg <- config.load
      host: String = cfg.string("http.host").getOrElse("localhost")
      port: Int    = cfg.int("http.port").getOrElse(8080)
      _ <- log.debug(s"Host: $host")
      _ <- log.debug(s"Port: $port")
    } yield
      BlazeBuilder[F]
        .bindHttp(port, host)
        .mountService(services)
        .serve
  }
} 

package examples.todolist.persistence.runtime

import cats.Monad
import doobie.implicits._
import doobie.util.transactor.Transactor
import examples.todolist.Tag
import examples.todolist.persistence.TagRepository

class TagRepositoryHandler[F[_]: Monad](implicit T: Transactor[F])
    extends TagRepository.Handler[F] {

  import examples.todolist.persistence.runtime.queries.TagQueries._

  def insert(input: Tag): F[Option[Tag]] =
    insertQuery(input)
      .withUniqueGeneratedKeys[Int]("id")
      .flatMap(getQuery(_).option)
      .transact(T)

  def get(id: Int): F[Option[Tag]] =
    getQuery(id).option.transact(T)

  def update(tag: Tag): F[Option[Tag]] =
    updateQuery(tag).run
      .flatMap(_ => getQuery(tag.id.get).option)
      .transact(T)

  def delete(id: Int): F[Int] =
    deleteQuery(id).run.transact(T)

  def list: F[List[Tag]] =
    listQuery
      .to[List]
      .transact(T)

  def drop: F[Int] =
    dropQuery.run.transact(T)

  def create: F[Int] =
    createQuery.run.transact(T)

  def init: F[Int] =
    dropQuery.run
      .flatMap(
        drops =>
          createQuery.run
            .map(_ + drops))
      .transact(T)
} 

package examples.todolist.persistence.runtime

import cats.Monad
import doobie.implicits._
import doobie.util.transactor.Transactor
import examples.todolist.TodoItem
import examples.todolist.persistence.TodoItemRepository

class TodoItemRepositoryHandler[F[_]: Monad](implicit T: Transactor[F])
    extends TodoItemRepository.Handler[F] {

  import examples.todolist.persistence.runtime.queries.TodoItemQueries._

  def insert(item: TodoItem): F[Option[TodoItem]] =
    insertQuery(item)
      .withUniqueGeneratedKeys[Int]("id")
      .flatMap(getQuery(_).option)
      .transact(T)

  def get(id: Int): F[Option[TodoItem]] =
    getQuery(id).option.transact(T)

  def update(item: TodoItem): F[Option[TodoItem]] =
    updateQuery(item).run
      .flatMap(_ => getQuery(item.id.get).option)
      .transact(T)

  def delete(id: Int): F[Int] =
    deleteQuery(id).run.transact(T)

  def list: F[List[TodoItem]] =
    listQuery
      .to[List]
      .transact(T)

  def drop: F[Int] =
    dropQuery.run.transact(T)

  def create: F[Int] =
    createQuery.run.transact(T)

  def init: F[Int] =
    dropQuery.run
      .flatMap(
        drops =>
          createQuery.run
            .map(_ + drops))
      .transact(T)
} 

package examples.todolist.persistence.runtime

import cats.Monad
import doobie.implicits._
import doobie.util.transactor.Transactor
import examples.todolist.TodoList
import examples.todolist.persistence.TodoListRepository

class TodoListRepositoryHandler[F[_]: Monad](implicit T: Transactor[F])
    extends TodoListRepository.Handler[F] {

  import examples.todolist.persistence.runtime.queries.TodoListQueries._

  def insert(item: TodoList): F[Option[TodoList]] =
    insertQuery(item)
      .withUniqueGeneratedKeys[Int]("id")
      .flatMap(getQuery(_).option)
      .transact(T)

  def get(id: Int): F[Option[TodoList]] =
    getQuery(id).option.transact(T)

  def update(input: TodoList): F[Option[TodoList]] =
    updateQuery(input).run
      .flatMap(_ => getQuery(input.id.get).option)
      .transact(T)

  def delete(id: Int): F[Int] =
    deleteQuery(id).run.transact(T)

  def list: F[List[TodoList]] =
    listQuery
      .to[List]
      .transact(T)

  def drop: F[Int] =
    dropQuery.run.transact(T)

  def create: F[Int] =
    createQuery.run.transact(T)

  def init: F[Int] =
    dropQuery.run
      .flatMap(
        drops =>
          createQuery.run
            .map(_ + drops))
      .transact(T)
} 

package com.azavea.franklin.api.commands

import cats.effect._
import cats.implicits._
import com.lightbend.emoji.ShortCodes.Defaults._
import com.lightbend.emoji.ShortCodes.Implicits._
import com.monovore.decline.Opts
import com.monovore.decline._
import com.monovore.decline.refined._
import doobie.implicits._
import doobie.util.transactor.Transactor
import eu.timepit.refined.types.numeric._

import scala.util.Try

trait DatabaseOptions {

  private val databasePort = Opts
    .option[PosInt]("db-port", help = "Port to connect to database on")
    .withDefault(PosInt(5432))

  private val databaseHost = Opts
    .option[String]("db-host", help = "Database host to connect to")
    .withDefault("database.service.internal")

  private val databaseName = Opts
    .option[String]("db-name", help = "Database name to connect to")
    .withDefault("franklin")

  private val databasePassword = Opts
    .option[String]("db-password", help = "Database password to use")
    .withDefault("franklin")

  private val databaseUser = Opts
    .option[String]("db-user", help = "User to connect with database with")
    .withDefault("franklin")

  def databaseConfig(implicit contextShift: ContextShift[IO]): Opts[DatabaseConfig] =
    ((
      databaseUser,
      databasePassword,
      databaseHost,
      databasePort,
      databaseName
    ) mapN DatabaseConfig).validate(
      e":boom: Unable to connect to database - please ensure database is configured and listening at entered port"
    ) { config =>
      val xa =
        Transactor
          .fromDriverManager[IO](config.driver, config.jdbcUrl, config.dbUser, config.dbPass)
      val select = Try {
        fr"SELECT 1".query[Int].unique.transact(xa).unsafeRunSync()
      }
      select.toEither match {
        case Right(_) => true
        case Left(_)  => false
      }
    }
} 

package com.azavea.franklin.api.services

import cats.effect._
import cats.implicits._
import com.azavea.franklin.api.endpoints.SearchEndpoints
import com.azavea.franklin.api.implicits._
import com.azavea.franklin.database.{SearchFilters, StacItemDao}
import com.azavea.franklin.datamodel.SearchMethod
import doobie.implicits._
import doobie.util.transactor.Transactor
import eu.timepit.refined.types.numeric.NonNegInt
import eu.timepit.refined.types.string.NonEmptyString
import io.circe._
import io.circe.syntax._
import org.http4s._
import org.http4s.dsl.Http4sDsl
import sttp.tapir.server.http4s._

class SearchService[F[_]: Sync](
    apiHost: NonEmptyString,
    defaultLimit: NonNegInt,
    enableTiles: Boolean,
    xa: Transactor[F]
)(
    implicit contextShift: ContextShift[F]
) extends Http4sDsl[F] {

  def search(searchFilters: SearchFilters, searchMethod: SearchMethod): F[Either[Unit, Json]] = {
    for {
      searchResult <- StacItemDao
        .getSearchResult(
          searchFilters,
          searchFilters.limit getOrElse defaultLimit,
          apiHost,
          searchMethod
        )
        .transact(xa)
    } yield {
      val updatedFeatures = searchResult.features.map { item =>
        (item.collection, enableTiles) match {
          case (Some(collectionId), true) => item.addTilesLink(apiHost.value, collectionId, item.id)
          case _                          => item
        }
      }
      Either.right(searchResult.copy(features = updatedFeatures).asJson)
    }
  }

  val routes: HttpRoutes[F] =
    SearchEndpoints.searchGet.toRoutes(searchFilters => search(searchFilters, SearchMethod.Get)) <+> SearchEndpoints.searchPost
      .toRoutes {
        case searchFilters => search(searchFilters, SearchMethod.Post)
      }
} 

package com.azavea.franklin.database

import cats.effect._
import com.zaxxer.hikari.{HikariConfig, HikariDataSource}
import doobie.util.transactor.Transactor

import scala.util.Properties

object DatabaseConfig {
  var jdbcDriver: String = "org.postgresql.Driver"

  val jdbcNoDBUrl: String =
    Properties.envOrElse(
      "POSTGRES_URL",
      "jdbc:postgresql://localhost/"
    )

  val jdbcDBName: String =
    Properties.envOrElse("POSTGRES_NAME", "franklin")
  val jdbcUrl: String = jdbcNoDBUrl + jdbcDBName
  val dbUser: String  = Properties.envOrElse("POSTGRES_USER", "franklin")

  val dbPassword: String =
    Properties.envOrElse("POSTGRES_PASSWORD", "franklin")

  val dbStatementTimeout: String =
    Properties.envOrElse("POSTGRES_STATEMENT_TIMEOUT", "30000")

  val dbMaximumPoolSize: Int =
    Properties.envOrElse("POSTGRES_DB_POOL_SIZE", "5").toInt

  def nonHikariTransactor[F[_]: Async](databaseName: String)(implicit cs: ContextShift[F]) = {
    Transactor.fromDriverManager[F](
      "org.postgresql.Driver",
      jdbcNoDBUrl + databaseName,
      dbUser,
      dbPassword
    )
  }

  val hikariConfig = new HikariConfig()
  hikariConfig.setPoolName("franklin-pool")
  hikariConfig.setMaximumPoolSize(dbMaximumPoolSize)
  hikariConfig.setConnectionInitSql(
    s"SET statement_timeout = ${dbStatementTimeout};"
  )
  hikariConfig.setJdbcUrl(jdbcUrl)
  hikariConfig.setUsername(dbUser)
  hikariConfig.setPassword(dbPassword)
  hikariConfig.setDriverClassName(jdbcDriver)

  val hikariDS = new HikariDataSource(hikariConfig)
} 

package org.atnos.eff.addon.doobie

import java.util.concurrent.Executors

import cats.effect._
import cats.implicits._
import doobie.free.connection.{ConnectionIO, close, commit, delay, rollback, setAutoCommit}
import doobie.util.transactor.{Strategy, Transactor}
import org.h2.jdbcx.JdbcConnectionPool

import scala.concurrent.ExecutionContext

object H2TestableTransactor {

  final class OpHistory {
    var calls: List[String] = List.empty[String]

    def registerConnection(): Unit = calls :+= "connection"
    def registerBefore(): Unit     = calls :+= "before"
    def registerAfter(): Unit      = calls :+= "after"
    def incrementOops(): Unit      = calls :+= "oops"
    def registerAlways(): Unit     = calls :+= "always"
  }

  def create[M[_]: ContextShift](url: String = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1",
                   user: String = "sa",
                   pass: String = "",
                   before: ConnectionIO[Unit] = setAutoCommit(false),
                   after:  ConnectionIO[Unit] = commit,
                   oops:   ConnectionIO[Unit] = rollback,
                   always: ConnectionIO[Unit] = close)(
      implicit async: Async[M]): (Transactor[M], OpHistory) = {
    
    val pool = JdbcConnectionPool.create(url, user, pass)

    val c = new OpHistory()

    val ec = ExecutionContext.fromExecutorService(Executors.newCachedThreadPool)

    val blocker = Blocker.liftExecutionContext(ec)

    val pre = Transactor.fromDataSource.apply(pool, ec, blocker)

    val t = pre.copy(
      connect0 = con => pre.connect(con).evalTap(async.pure(_) <* async.pure(c.registerConnection())),
      strategy0 = Strategy(
        before = before.flatMap(a => delay(c.registerBefore()).map(_ => a)),
        after  = after .flatMap(a => delay(c.registerAfter()) .map(_ => a)),
        oops   = oops  .flatMap(a => delay(c.incrementOops()) .map(_ => a)),
        always = always.flatMap(a => delay(c.registerAlways()).map(_ => a))
      )
    )

    (t, c)
  }

} 

package ch.epfl.bluebrain.nexus.cli.modules.postgres

import cats.Parallel
import cats.effect.{ConcurrentEffect, ContextShift, Timer}
import ch.epfl.bluebrain.nexus.cli.config.AppConfig
import distage.{ModuleDef, TagK}
import doobie.util.transactor.Transactor
import izumi.distage.model.definition.StandardAxis.Repo
import izumi.distage.model.recursive.LocatorRef

final class PostgresModule[F[_]: Parallel: ContextShift: ConcurrentEffect: Timer: TagK] extends ModuleDef {
  make[Postgres[F]].tagged(Repo.Prod).from { locatorRef: LocatorRef => Postgres[F](Some(locatorRef)) }
  make[Transactor[F]].tagged(Repo.Prod).from { (cfg: AppConfig) =>
    Transactor.fromDriverManager[F](
      "org.postgresql.Driver",
      cfg.postgres.jdbcUrl,
      cfg.postgres.username,
      cfg.postgres.password
    )
  }
  make[PostgresProjection[F]].tagged(Repo.Prod)
}

object PostgresModule {
  final def apply[F[_]: Parallel: ContextShift: ConcurrentEffect: Timer: TagK]: PostgresModule[F] =
    new PostgresModule[F]
} 

package ch.epfl.bluebrain.nexus.cli.postgres

import cats.effect.IO
import ch.epfl.bluebrain.nexus.cli.AbstractCliSpec
import ch.epfl.bluebrain.nexus.cli.config.AppConfig
import ch.epfl.bluebrain.nexus.cli.postgres.PostgresDocker.PostgresHostConfig
import doobie.util.transactor.Transactor
import izumi.distage.model.definition.{Module, ModuleDef}

import scala.concurrent.duration._

class AbstractPostgresSpec extends AbstractCliSpec {

  override protected def defaultModules: Module = {
    super.defaultModules ++ new PostgresDocker.Module[IO]
  }

  override def testModule: ModuleDef =
    new ModuleDef {
      make[AppConfig].fromEffect { host: PostgresHostConfig =>
        copyConfigs.flatMap {
          case (envFile, postgresFile, _) =>
            AppConfig.load[IO](Some(envFile), Some(postgresFile)).flatMap {
              case Left(value)  => IO.raiseError(value)
              case Right(value) =>
                val postgresOffsetFile = postgresFile.getParent.resolve("postgres.offset")
                val cfg                = value.copy(postgres =
                  value.postgres.copy(
                    host = host.host,
                    port = host.port,
                    offsetFile = postgresOffsetFile,
                    offsetSaveInterval = 100.milliseconds
                  )
                )
                IO.pure(cfg)
            }
        }
      }
      make[Transactor[IO]].fromEffect { (_: PostgresDocker.Container, cfg: AppConfig) =>
        val xa = Transactor.fromDriverManager[IO](
          "org.postgresql.Driver",
          cfg.postgres.jdbcUrl,
          cfg.postgres.username,
          cfg.postgres.password
        )
        waitForPostgresReady(xa).as(xa)
      }
    }

  private def waitForPostgresReady(xa: Transactor[IO], maxDelay: FiniteDuration = 30.seconds): IO[Unit] = {
    import doobie.implicits._
    import retry.CatsEffect._
    import retry.RetryPolicies._
    import retry._
    val policy = limitRetriesByCumulativeDelay[IO](maxDelay, constantDelay(1.second))
    retryingOnAllErrors(
      policy = policy,
      onError = (_: Throwable, _) => IO.delay(println("Postgres Container not ready, retrying..."))
    ) {
      sql"select 1;".query[Int].unique.transact(xa)
    } *> IO.unit
  }
}