slick/slick

Scala Language Integrated Connection Kit

Matt Fulgo

@mfulgo

Hi folks,
This has been covered a good bit in StackOverflow questions & in other places, but I have yet to find a solution that I'm happy with... I want to create an UPDATE statement with a variable number of columns. Something like

coffees.filter(_.id === id)
  .map(t => change.cols(t))  // cols(t) returns Seq[Rep[_]]
  .update(change.values)  // values returns a Seq[_]

I feel like there could be a way to do this with a custom Shape (like TupleShape but for Seq), but I was hoping for some more experienced folks to tell me if this is a futile endeavor or if it's a viable approach.

There's also this gist but I'd rather not have to create a custom driver or rely on regexes to manipulate the query.

Richard Dallaway

@d6y

I think it'll be tricky to get far with Seq, as I'd imagine a lot of type information (for each column) will get lost away to Any. I'd be tempted to use something like slick.collection.heterogeneous.HList, and there would be some fun/challenging type-level methods to get the types to exactly line up for cols and values.

There might be easier ways. Depending on what you're basing the change on.

If your data is a case class, maybe you can merge the values and update the whole role. As inspiration: https://github.com/davegurnell/bulletin (i.e., drop the map and just do the update; probably not suitable if you're shiping large amounds of data in a row)

Matt Fulgo

@mfulgo

Thanks, Richard. In this case, I'm okay with missing out on some type safety. For the moment, I've ended up with something along these lines:

    SQLActionBuilder(
      Seq(s"UPDATE $tableName SET ",
        cols.map(_.getDumpInfo.mainInfo + " = ?").mkString(", "),
        " WHERE ID = ?",
        " LIMIT 1",
      ),
      (_: Unit, pp: PositionedParameters) => {
        (values :+ id).foreach {
          case v: Boolean => pp.setBoolean(v)
          case v: Byte => pp.setByte(v)
          case v: Date => pp.setDate(v)
          case v: Double => pp.setDouble(v)
          case v: Float => pp.setFloat(v)
          case v: Int => pp.setInt(v)
          case v: Long => pp.setLong(v)
          case v: Short => pp.setShort(v)
          case v: String => pp.setString(v)
          case v: Time => pp.setTime(v)
          case v: Timestamp => pp.setTimestamp(v)
          case v => throw new SQLException("update doesn't know how to handle parameter (" + v + ")")
        }
      }).asUpdate

It's not quite complete because it doesn't handle NULLs, but it works for my current needs.

Martijn Blankestijn

@martijnblankestijn

Hi, been searching everywhere, but was not able to find how to do the following:

Given the following for PostgreSQL

CREATE TABLE IF NOT EXISTS X(id INT PRIMARY KEY, name VARCHAR not null);

select *from X x where (x.id, x.name) in ( (1,'foo'), (2,'bar'))
;

how do I do this with Slick 3.3.3? The compiler never seems to agree with me

Richard Dallaway

@d6y

Ah yes... inSet doesn't seem to support tuples. We might be able to teach Slick how to do that, but the workaround would be to transform into a (id ===1 && name == "foo") or (id === 2 && name === "bar"). Sounds horrible, but the code isn't too much. Old example: https://github.com/d6y/list-of-tuples/blob/eaa2903332d7797ac38d8807fa598fd2a5aad344/src/main/scala/or-example.scala#L52-L55

Martijn Blankestijn

@martijnblankestijn

Will give it a try, thanks @d6y !

mehdi

@hmehdi

Hi, I want to connect from slick to mssql when select to the table contain column type NVarchar don't return the result for me. because slick don't add character N ( Unicode NVARCHAR Strings) . how can cusotme fucntion add N character.
ex) select * from customer where title like N'%tt%'

how can I enable the unicode prefix in slick?

plz help me

Felipe Bonezi

@felipebonezi

Hey guys, is there anyone working on MongoDB support for Slick?

Richard Dallaway

@d6y

@felipebonezi Not that I know of. There was some investigation some years back (there are still open tickets mentioning MongoDB).

Felipe Bonezi

@felipebonezi

@d6y Do you know any way to study more about Slick implementations and your archicture?
I'm intended to try implement MongoProfile or to improve my skills hehehe.

Richard Dallaway

@d6y

Jump over to https://scala-slick.org/docs/, and in the Talks section there are a couple there from Stefan. The Compiling Collections to SQL talk gives you insight into the query compiler (the steps Slick implements to go from a collection to SQL). Maybe start with the talk before that about Lifted embedding.

Felipe Bonezi

@felipebonezi

Thanks @d6y
I've already downloaded the Essential Slick Book

Georgios Larkou

@glarkou

Hello guys one question, we observed that .filterNot(_.name === "test") does not include the null values as intended. Is there a better way to construct my queries instead of filter(x=> x.name.isEmpty || x.name =!= "test")

Artsiom Miklushou

@mikla

Hi,
How can I do constant query similar to SELECT 1 (Query(1)), but I need List(1) for example?

Richard Dallaway

@d6y

Hi @mikla - you can use .to[T[_]], for example: Query(1).to[List]

Artsiom Miklushou

@mikla

@d6y thx, interesting!

What i'm trying to achieve is: mix some constant values to left join.
Something similar to:

SELECT * FROM (VALUES
                      (CAST('316df2ac-14d0-468e-8e2e-0e5a54a8abca' as uuid)),
                      (CAST('47700d3b-b1fc-402f-80e5-a113ae792aca' as uuid)),
                      (CAST('965c0657-0bdd-4842-a127-9547b9c58ca3' as uuid))
                      ) AS q (col1)
left join table on col1 = employee_id;

Guillaume Noireaux

@gnoireaux

Hello people, is it possible to read a bigint column as a String? Rather than as a Long? Thanks!

Richard Dallaway

@d6y

@gnoireaux I believe you can do a table.map(row => row.myBigIntCol.asInstanceOf[String]) but not 100% sure. I'd expect that would CAST as the SQL level. Let us know how you get on.

1 reply

Richard Dallaway

@d6y

@mikla ooh. That's kind of odd looking, but I think you want to have a fixed query of uuids so you can go:

ids.join(table).on(_ === _.employee_id)

The type id ids would need to be a Query[Rep[X]], X, Seq] where X is whatever the type is for the uuid. I

If, for the sake of argument, we were looking at Longs I think ids could be:

  val ids: Query[Rep[Long], Long, Seq] = Query(1L) ++ Query(3L) ++ Query(5L)

Maybe there's something in that you can try. (I've never had to use Query.apply directly like that for a real application)

Artsiom Miklushou

@mikla

@d6y Thank you very much!

Anirudh Vyas

@AnirudhVyas

I am trying to run this query:

sql"""select  distinct r_.server_request_id, r_.raw_request from  #$tableName r_  where  r_.server_request_id in ( #$sids ) and r_.created < #$downTime  and r_.created > #$tillTime""".as[(String, String)]

when I look at raw sql

s""" select  distinct r_.server_request_id, r_.raw_request from  #$tableName r_  where  r_.server_request_id in (#$sids) and r_.created < #$downTime  and r_.created > #$tillTime""".stripMargin

And paste it to run on postgres with values it prints - I get correct result, however with above sql""" syntax I get empty result -- any suggestions for me would be great

I tried writing non sql way too:

table.map(server_request_id, raw_request, created_date).filter(dates).filter(_.server_request_id inSet serverRequestIdList).map(server_request_id, created_date).result

but seems to give me same empty vector as result...

Anirudh Vyas

@AnirudhVyas

the map, filter seems to work. still would be curious to know whats wrong with above sql

Lalli Nuorteva

@lalnuo

Hi,

I have a plain sql statement like this:

    val foobar = Seq("foo", "bar")
    sql"""
      SELECT * FROM foo where x in $foobar    
    """

However placing a Seq inside the plain sql query does not seem to work. Is there any way to make it work?

Antoine Doeraene

@sherpal

you will need a # before your $, see here https://scala-slick.org/doc/3.3.2/sql.html

Lalli Nuorteva

@lalnuo

That will introduce possibility for SQL injection, right?

Antoine Doeraene

@sherpal

yes you should not do that in information from the external world :(

Lalli Nuorteva

@lalnuo

array_position($foobar, foo::text) is not null

seems to be one way to do this, but its not the nicest one

Richard Dallaway

@d6y

@lalnuo @sherpal I've been trying/tracking reasonable ways to do plain sql IN clauses. This is what I have: https://github.com/d6y/plain-in (it's not a library; just example code).

Antoine Doeraene

@sherpal

Oh that's nice! Thanks for sharing

Lalli Nuorteva

@lalnuo

Thats cool! Thanks

Felipe Bonezi

@felipebonezi

Hey guys, I've opened a Pull Request to an issue, but AppVeyor is broking and I don't have idea why.
Can anyone help me? :pray:

slick/slick#2134

Richard Dallaway

@d6y

Looks like the link to download SBT as part of the build is failing. Not sure if it's transitory, but right now https://piccolo.link/sbt-0.13.17.zip is failing with a 500 error. I can't figure out who runs piccolo.link to report this.

Richard Dallaway

@d6y

Let's see if piccolo comes back today... but then someone with AppVeyor permissions will need to request a rebuild (probably, maybe)

Lalli Nuorteva

@lalnuo

Is there a built in way to retry queries in slick? I have some queries running in transaction and i'd like to retry them N times

nafg

@nafg

Richard Dallaway

@d6y

...but you might be able to roll your own with cleanUp perhaps. https://books.underscore.io/essential-slick/essential-slick-3.html#andfinally-and-cleanup

Richard Dallaway

@d6y

...or maybe at the level of Future.

Lalli Nuorteva

@lalnuo

Ok, I'll retry on the future level then, thanks!

dan-vidican

@dan-vidican

private class DbWeatherCalculations(tag: Tag)
    extends Table[WeatherCalculationRepresentation](tag, Some("bill_management_v2"), "weather_calculations") {
    def id           = column[Long]("id", O.PrimaryKey, O.AutoInc)
    def createdAt    = column[Timestamp]("created_at", O.AutoInc)
    def billItemId   = column[Long]("bill_item_id")
    def name         = column[String]("name")
    def value        = column[Double]("value")
    def units        = column[String]("units")
    def billRecordId = column[Long]("bill_record_id")

    def * = (id.?, createdAt.?, billItemId, name, value, units, billRecordId)
        .<>( (WeatherCalculationRepresentation.apply _).tupled, WeatherCalculationRepresentation.unapply)
  }

Hi,
I have this problem where 'units' has it's own data type in postgresql which is kind of enumeration. When I try to make an insertion I get this error:

ERROR: column \"units\" is of type public.units but expression is of type character varying\n Hint: You will need to rewrite or cast the expression.\n

Any suggestion about how I can cast the column 'units' to the one from db? Thanks

Where communities thrive

People

Repo info

Activity