Why is the Slick forum so quiet?

[Jacobus]

Hi there,

I'm starting to implement a new project's database back-end on Slick, but I'm concerned about how quiet the Slick forum is. Before Slick, I used LiftMapper. In my opinion, Slick is a superior offering, but community support seems to be on the thin side.

Will the open-source community still get backing from the creators now that it's a TypeSafe product, or will Slick become a commercial product only. It's just that some questions go unanswered for days, and without support from the authors the community will have difficulty obtaining a critical mass where more experienced users can help new users.

One problem may also be the re-branding that is a bit confusing, for example, on StackOverflow the Slick tag is hardly used.

Just a thought. To be clear: I think the product is great, and that TypeSafe and the creators of Slick are great people also. I'm just worried that other business pressures may leave the Slick community out in the cold.

Where is the preferred place to ask Slick questions, on this forum or StackOverflow? Is it bad style to ask a question on StackOverflow and then post a link to the question on this forum also, maybe with [so] in the title. If we ask quesions on SO, then other Scala developers will become more aware of Slick.

Lastly, are there any simple and well designed CRUD example of Slick that uses both mapped tables, one or two composed queries and at least a many-to-many mapping that new users can use as a template project? I had a look at the examples - they are clear and informative, but an example that combines the features into a simple real-world app will be worth it's weight in gold.

Kind regards,
Jacobus

[virtualeyes]

It is what it is, no Scala-based community is truly large in the C#/.NET or RoR sense of the word.

Look at Play, Stackoverflow is close to useless as far as asking Play related questions of any depth. Their user group is active, but pretty hit or miss, more than likely your post will be entirely ignored in the daily deluge.

Here on SQ/Slick, the best place to ask questions is, apparently, your own mind. The absolute best place to look for answers is in the source code, challenging as that may be. The 3rd best place to look for answers is here, in this vibrant group of silent introverts.

On occasion the original author shows up, which is like an Elvis sighting, he lives! ;-)

Seriously, the developers are likely deep into code that most of us cannot yet grok, so the lack of timely feedback, whatever, if the product thrives, we all benefit hugely (hello type providers).

Of course, ask questions, I do, I'm just not banking on a 2 minutes from now response, or even 2 days from now for that matter.

[Adam Mackler]

I'm curious what you mean by "mapped" tables.  Are you talking about tables with foreign key references to other tables or something else?

[Jacobus]

Great answer for a silent introvert :-) Thanks for that.

More Elvis sightings would be nice though. Library authors usually
know more than what the source code show - they know the intended use:
the perfect way to apply the library, rather than what the library
itself is.

Anyways, I'll stop being lazy and start reading the source.

One Scala forum with many Elvis sightings is the Spray users group,
not to mention rock solid documentation (http://www.spray.io/). That's
maybe where I got spoiled a bit ;-) Spray and Slick make a great
stack, and they even reference back to Slick as a suggested part of
your software stack. See bottom of http://www.spray.io/introduction/

Thanks again for the chirpy answer.

Cheers,
Jacobus

[Jacobus]

Hi Adam,

I'm referring to the automated mapping between records and case
classes: http://slick.typesafe.com/doc/0.11.2/lifted-embedding.html#mapped-tables
The documentation on it is just a little thin at this point.

I realize there is no magic there, but I'm struggling to get the sweet
spot on how to tie your business object (which may be case classes in
this case) with your database. For one, I rely heavily on link tables,
where the link tables themselves have properties also.

Say for example you have a user and a role and a resource, then these
three would be tied by link tables: user_role and role_resource, where
role_resource could have additional properties, such as user rights
(e.g. edit, update, delete).

Then that needs to be tied to the rest of your core system, so that
your system can do things like add a user, bind a user to a role etc,
while at the same time the database design does not spill over into
your business domain.

It would have been nice to see some real world examples, or to have a
nice starting point, rather than everyone making their own mistakes
over and over again while learning the best way to tie Slick to your
business layer.

Maybe some design patterns could work.... I don't know...

The most important is for things to remain simple. I'm worried that if
I 'role my own' in a hurry, then I will end up with a make-shift
framework that could have been much better if I had more experience
with Slick, or guidance from a nice sample project.

Figuring out how Slick works is not too difficult, but applying it
correctly in a nice system design may be a bit more challenging.

Best regards,
Jacobus

> --
>
>
>

[Adam Mackler]

Assuming I'm understanding you correctly, one way I've been doing things lately, and so far it seems beneficial, is to make a database view whose definition has the joins between the linked tables and then I tell SLICK about the view but not the underlying tables.  Using your example, SLICK would know about a (view) table that has columns for user attributes, attributes of roles, and particular users' role attributes.  Then I put instead-of-insert triggers on the view.  Now I can effectively insert into a join, which is not otherwise possible, and things are much simpler on the SLICK side since it looks like one table but is actually the interface to several linked tables.  Such a view, if it were a base table, would be denormalized, but since it's just a view, the data inconsistency problems associated with denormalization are not present.

I suppose this approach is not specific to SLICK.  Any interface that abstracts away SQL code is going to get clunky the more complex that SQL needs to be, and if you want to use advanced SQL features such as common table expressions or window functions, I don't know that SLICK would be your friend.  So the way I see it, it's better to put complex SQL code into the database inside triggers, stored procedures, and functions, and then to use a host language library such as SLICK to do simple CRUD operations on views that fire those triggers and procedures, rather than trying to get a library like SLICK to generate complex SQL statements.  It also allows for more advanced access control since the user that SLICK connects to the database as can have permission to access the views but not the underlying tables.

Though I'm certainly no master at this stuff, and would be the first to listen to someone with a different opinion.

[Jacobus]

Thanks for the idea Adam.

Personally I steer well clear of views for anything but read-only data.

Something I've learned from enterprise systems: When you look at
databases it's always good to make a split between operational and
statistical data, where operational data is typically data against
which you would do your normal CRUD operations. Statistical data is
usually processed to the point where the data is normalised
(flattened) and you know that the data is either valid, or invalid,
e.g. tables showing accounts that balance (everything's ok) and tables
that show accounts that does not balance (something went nutty).
Statistical data would be read-only from a user perspective.

For operational data you want the logic (as far as possible) to remain
under control of your software. You want to centralise your business
logic as far as possible. This also makes it possible to move between
databases more readily.

The way I see Slick queries, and I may be wrong here, is that the
whole idea behind Slick queries is that complex queries can be
composed out of simple queries. That way you will keep your logic
manageable, as shown in this example (from
http://slick.typesafe.com/talks/2012-10-17_Typesafe-Slick-presentation-at-BASE/2012-10-17_Typesafe-Slick-presentation-at-BASE.pdf).

def personByAge( from:Int, to:Int ) =
Persons.filter( p => p.age >= from && p.age <= to )

// Interests of people between 20 and 25
for( p <- personByAge(20, 25); i <- Interests; if i.personId ===
p.id) yield i.text

// Cars of people between 55 and 65
for( p <- personByAge(55, 65); c <- Cars; if c.personId === p.id) yield c.model

You will notice that personByAge is re-used in other queries. This
works because Slick makes a manageable distinction between query
creation and query execution. As a matter of fact, composable queries
the single feature of Slick that won me over - it gives you a way to
divide and conquer your database problems, and to re-use tried and
tested queries inside other queries (something that would otherwise be
done as stored procedures).

Kind regards,
Jacobus

[virtualeyes]

Interesting approach, the DBA way ;-)

One thing out the window with views, as Jacobus mentions, is query composition, which for me is the single most awesome feature in SQ/Slick. Check this out, with a few building blocks:

val team = for {

    t <- Teams

    s <- Schools if t.schoolID is s.id

  } yield (t, s)

  val player = for {

    r <- Rosters

    p <- Players if r.playerID is p.id

  } yield (r, p)

  val playerDetail = for {

    (r, p) <- player

  } yield (p.id, p.firstName, p.lastName, r.jerseyID, r.position, r.gamesPlayed)

  val scoring = for {

    (r, p) <- player

    s <- Scorings if p.id is s.playerID

    detail <- playerDetail

  } yield (r, p, s, detail)

  val scoringDetail = for {

    (r, p, s, detail) <- scoring

    val (total, goals, assists) = (

      s.playerID.count, s.goal.sum, (s.assist1.sum + s.assist2.sum)

    )

    val ppg = ( (s.playerID.count / r.gamesPlayed) ).asColumnOf[Option[Double]]

  } yield (goals, assists, total, ppg)

You can then compose fairly complex queries to generate scoring leader stats for a given league, team, or player. Here's a composed query to generate stats for a given league:

val q = for {

  start ~ end ~ id ~ gender <- Parameters[JodaTime,JodaTime,Int,String]

  (r, p, score, player) <- scoring

  g <- Games if (score.gameID is g.id) && (g.gameDate between(start,end))

  (t, school) <- team if p.teamID is t.id

  (goals, assists, total, ppg) <- scoringDetail

  if(t.leagueID is id) && (t.gender is gender)

   _ <- Query groupBy p.id having { _ => p.id.count >= gamesMin }

   _ <- Query orderBy (ppg.desc, total.desc, goals.desc)

} yield ( ((player),(goals, assists, total, ppg)), t.id,school)

So far I've been able to replicate previously hand written queries type safely, all mapped to case class/db-tables. It is pretty cool stuff, the only catch is, yes, boilerplate in the mappings, something that type providers will address in future.

I started out using views, but then query composition grabbed me. Once type providers are in place we should be able to define the schema in code, letting the application layer run the entire show. Can't imagine that is going to happen anytime soon though, sounds like a huge endeavor...

[Jacobus]

Wow, that's a great example.

I see you are using val's instead of def's, I suppose that is possible
because the queries are created, but not executed. Also, I've not come
across

for { start ~ end ~ id ~ gender <- Parameters[JodaTime,JodaTime,Int,String]

....

I can only guess it's part of the Slick DSL and prepares it for the
(r, p, score, player)
bit?

[Adam Mackler]

First, thanks very much to VE for posting this extended example.  I am wondering though if this is something you actually have working.  The reason I'm asking is that as I've been (slowly) working my way through it I am coming up against an error.  Specifically you have defined a val named scoringDetail that has total goals, total assists, total players, and points-per-game.  The first three are aggregates.  The select statement that Query generates is:

select x2.x3, x2.x4, x2.x5, x2.x6

from (select count(x7."player_id") as x5,

             sum(x7."goal") as x3,

             sum(x7."assist") as x4,

             cast((count(x7."player_id") / x8."games_played") as FLOAT) as x6

      from   "roster" x8, "player" x9, "scoring" x7, "roster" x10, "player" x11

      where ((x8."player_id" = x9."id") and (x9."id" = x7."player_id"))

      and   (x10."player_id" = x11."id")

) x2;

Postgres won't process that, complaining:

ERROR:  column "x8.games_played" must appear in the GROUP BY clause or be used in an aggregate function

LINE 1: ...x7."assist") as x4, cast((count(x7."player_id") / x8."games_...

                                                             ^

It seems to me that SLICK ought not to be capable of producing invalid SQL, but maybe it's possible if you use it wrongly.  So I'm not sure whether this is the fault of SLICK for making a bad statement, your fault for bad Scala code, or my fault for copying your code wrong, which is certainly possible because there are several steps before I get the error and I may have introduced an error.  Another possibility is that your intention is that scoringDetail be merely an intermediate Query and not intended to produce valid SQL except as part of some greater expression.

So before I go any further I'm interested in whether the code you posted does in fact work for you in the way I am attempting, and in particular would be interested in looking at the SQL statement that scoringDetail.selectStatement returns for you.

Thanks again.

On Wednesday, October 31, 2012 3:11:00 AM UTC-4, virtualeyes wrote:

Check this out, with a few building blocks:

....

--

Adam Mackler

 

[virtualeyes]

absolutely working ;-)

furthermore, the generated SQL is very close to  (short of Slick's t1, t2 aliasing) what I wrote by hand, and is why SQ/Slick is my favorite Scala library.

Defining your queries with vals and parameterizing the passed in params also generates a cached prepared statement at compile time, so it's ripping fast to boot.

The views approach, with MySQL at least, is comparatively slow, prepared statements are not an option.

Here's the generated SQL:

************

SELECT t1.id,t1.firstName,t1.lastName,t2.jerseyID,t2.position,t2.gamesPlayed,sum(t3.goal),(sum(t3.assist1) + sum(t3.assist2)),count(t3.playerID),{fn convert((count(t3.playerID) / t2.gamesPlayed),DOUBLE)},t4.id,t5.id,t5.name,t5.acronym,t5.mascot,t5.address,t5.city,t5.state,t5.postal,t5.country,t5.arenaName,t5.enrollment,t5.logo,t5.googleMap,t5.url 

FROM _school t5,_scoring t3,_team t4,_composite t6,_player t1,_roster t2 WHERE (t1.id=t2.playerID) AND (t1.id=t3.playerID) AND (t1.id=t2.playerID) AND (t3.gameID=t6.id) AND (not (t6.gameType=?)) AND t6.gameDate between ? and ? AND (t1.teamID=t4.id) AND (t4.schoolID=t5.id) AND ((t1.id > ?) and (t2.gamesPlayed >= ?)) AND (t4.gender=?) AND (t4.divID=?) AND (t1.id=t2.playerID) AND (t1.id=t3.playerID) AND (t1.id=t2.playerID) 

GROUP BY t1.id HAVING (count(t1.id) >= ?) ORDER BY {fn convert((count(t3.playerID) / t2.gamesPlayed),DOUBLE)} desc,count(t3.playerID) desc,sum(t3.goal) desc

*****************

Being able to compose queries in this way consolidates the model in ways I never imagined possible in the application layer (maybe you could do query aggregation in the DB layer, but even then, unlikely that it would have the Scala nice-ness)

[Adam Mackler]

Thank you very much for taking the time to post that.  I can see this is a very useful example, and I would very much like to get it working on my end so I can dissect it and understand it.  I do have some more questions:

First, this statement you just posted, this is q.selectStatement, is that correct?  In other words, the select statement produced by the query for the stats for a given league that was the last code you posted two posts ago?

Assuming that's correct, I'm wondering what type JodaTime is.  Is that an alias for org.joda.time.DateTime?

Assuming that's correct, I'm not able to get that query to compile due to the type parameterization of the Parameters object.  I'm not familiar with that object, but the source code I'm looking at (file lifted/Parameters.scala) says it has one type variable, but in your code there are four: [JodaTime,JodaTime,Int,String].  What am I missing?  The exact error message is:

<console>:17: error: wrong number of type parameters for method apply: [U](implicit shape: scala.slick.lifted.Shape[U, U, _])scala.slick.lifted.Parameters[U,shape.Packed] in object Parameters

         start ~ end ~ id ~ gender <- Parameters[JodaTime,JodaTime,Int,String]

                                                ^

I should also probably ask what version you're using.  I'm using 0.11.2, and apparently your code is for an earlier version because I'm getting deprecation warnings about the "val" keyword in the for comprehensions, and also I notice SQL keywords are capitalized in the statement text that you posted whereas SLICK consistently gives me lowercase keywords.  Those things in themselves aren't problems, but the version difference might be.

Again, thank you for assisting me in trying to get my head around all this.

On Sunday, November 4, 2012 5:00:43 PM UTC-5, virtualeyes wrote:

absolutely working ;-)

[Stefan Zeiger]

On 2012-10-30 19:07, Jacobus wrote:

Will the open-source community still get backing from the creators now that it's a TypeSafe product, or will Slick become a commercial product only. It's just that some questions go unanswered for days, and without support from the authors the community will have difficulty obtaining a critical mass where more experienced users can help new users.

Sorry, I've been on vacation last week and I'm just catching up on everything. (OK, that's not such a good excuse, as I don't find the time to read and post here as much as I'd like when I'm not on vacation). To answer your question, Slick will stay open source and we're planning to make it a part of the (modularized) Scala standard library in the long term. Typesafe is offering commercial support for Slick and there will be a closed-source slick-extensions package with drivers for (initially) Oracle and DB2 for Typesafe Stack subscribers (free for development/evaluation use for everyone).

Lastly, are there any simple and well designed CRUD example of Slick that uses both mapped tables, one or two composed queries and at least a many-to-many mapping that new users can use as a template project? I had a look at the examples - they are clear and informative, but an example that combines the features into a simple real-world app will be worth it's weight in gold.

We're working on a Play sample application that uses Slick for database access. I hope I'll have some more details about that in a few weeks.

Regarding activity in this forum: There has never been as much as last month. From the old Google Groups UI:

Archive

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2010									11	24	14	16
2011	6	16	37	14	61	21	53	20	22	37	43	22
2012	12	26	20	32	34	24	83	109	66	126	51

(I can't seem to find this info in the new UI. Have they removed that feature?)

--

Stefan Zeiger
Typesafe - The software stack for applications that scale
Twitter: @StefanZeiger

Elvis has left the building.

[Stefan Zeiger]

On 2012-11-04 22:16, Adam Mackler wrote:

First, thanks very much to VE for posting this extended example.  I am wondering though if this is something you actually have working.  The reason I'm asking is that as I've been (slowly) working my way through it I am coming up against an error.  Specifically you have defined a val named scoringDetail that has total goals, total assists, total players, and points-per-game.  The first three are aggregates.  The select statement that Query generates is:

select x2.x3, x2.x4, x2.x5, x2.x6

from (select count(x7."player_id") as x5,

             sum(x7."goal") as x3,

             sum(x7."assist") as x4,

             cast((count(x7."player_id") / x8."games_played") as FLOAT) as x6

      from   "roster" x8, "player" x9, "scoring" x7, "roster" x10, "player" x11

      where ((x8."player_id" = x9."id") and (x9."id" = x7."player_id"))

      and   (x10."player_id" = x11."id")

) x2;

This looks like an aggregation that is missing the GROUP BY clause. Can you open a ticket with a snippet to reproduce it?

It seems to me that SLICK ought not to be capable of producing invalid SQL, but maybe it's possible if you use it wrongly.

It is possible but this case looks like a bug in the query compiler or code generator. The new groupBy/aggregation semantics (unlike what ScalaQuery used) should make it impossible to write such a query with aggregations without actually performing the groupBy.

 So I'm not sure whether this is the fault of SLICK for making a bad statement, your fault for bad Scala cod

[Jacobus]

Hi Stefan,

Thank you very much for responding to this, and for the positive and informative feedback. The Slick library is superb, and I was just worried that it may disappear in the noise.

I had a second look at the MultiDBCakeExample and think that answers most of the questions I've had so far, and also serves as a great starting point for many real world projects.

Lastly, vacations are the best excuse ever for not answering posts - hope it was great.

Cheers,
Jacobus

--
 
 
 

[Adam Mackler]

Ticket opened here.

On Wednesday, November 7, 2012 11:25:24 AM UTC-5, Stefan Zeiger wrote:

This looks like an aggregation that is missing the GROUP BY clause. Can you open a ticket with a snippet to reproduce it?

--

Adam Mackler 

[virtualeyes]

Yes, JodaTime is a type alias for org.joda.time.DateTime, which I have available in a package object

You need to pass 4 params in to the query:

start ~ end ~ id ~ gender <- Parameters[JodaTime,JodaTime,Int,String]

Means you have to do something like q(jodaVal,jodaVal,10,"M").list

q.selectStatement does not work (on 0.10) with paramaterized queries -- just turn on query logging in postgres and "tail -f path/to/log" to see the generated sql.

[Stefan Zeiger]

On 2012-11-08 10:25, virtualeyes wrote:

You need to pass 4 params in to the query:

start ~ end ~ id ~ gender <- Parameters[JodaTime,JodaTime,Int,String]

Means you have to do something like q(jodaVal,jodaVal,10,"M").list

q.selectStatement does not work (on 0.10) with paramaterized queries -- just turn on query logging in postgres and "tail -f path/to/log" to see the generated sql.

It does work: https://github.com/slick/slick/blob/0.10.0/src/test/scala/scala/slick/test/ql/TemplateTest.scala#L51

Of course, you won't see any parameter values in the query (which you might get from the database log).

[virtualeyes]

On ScalaQuery 0.10?

Anyway, yes, the query log does show the params as well, so a bit easier to see why a query is returning unexpected results...

[Stefan Zeiger]

On 2012-11-08 16:27, virtualeyes wrote:

On ScalaQuery 0.10?

Same thing: https://github.com/slick/slick/blob/0.10.0-M1/src/test/scala/org/scalaquery/test/TemplateTest.scala#L52

This should work on pretty much any version ever since QueryTemplates existed.

[Adam Mackler]

Today I'm somewhat less bothered by my inability to understand your example, or at least the part that I was questioning you about.  In the opinion of Stefan Zeiger "it does not make any sense."  Apparently it may have made sense under an older version that I am not using.

Details are here.

Nonetheless, I do appreciate that you posted the example, since it does give ideas of what is possible, even if it isn't a complete and perfect whole.