How to SELECT *?

[kutchar]

I couldn't figure out how I can run a SELECT *. I would assume that select() (without providing column names) would mean SELECT * by default.

[brendanator]

select statements are parameterised by the types of the columns that are being selected.

This means you can write select(MyTable.col1, MyTable.col2).from(MyTable).fetchAll and result type will be List[(Int, String)] - assuming col1 is an Int and col2 is a String

Writing select().from(MyTable) creates a select with no columns but this can be used with an extractor as hamish as shown - the extractor knows which columns it uses so injects them into the select

If you want to fetch all of the columns for a table then a possible workaround would be to define * in the table definition to contain all the columns

class MyTable(alias: Option[String]) extends Table("mytable", alias) {
  val col1 = column[Int]("col1")
  val col2 = column[String]("col2")

  val * = (col1, col2)
}
object MyTable extends MyTable(None)

You would then be able to write select(MyTable.*).from(MyTable)

I've create a branch select-star with a first stab of changing the select statement to allow this. You can see it in action here: https://github.com/jhc-systems/sqlest/blob/select-star/sqlest/src/test/scala/sqlest/executor/ExecutorSpec.scala#L72. Note this wouldn't work if you wanted to select * from multiple tables

What do you think?

[kutchar]

I see. The only issue with the * approach above is that I won't be able to select all the columns of a table if the table has more than 22 columns. I think a shapeless HList might be a better alternative.

[kutchar]

nm the 22 limit, I just figured out that I can use the following style too val * = List(col1, col2, col3, ...)

BTW, why is the apply method parameter type scala.collection.immutable.List which is an abstract class, wouldn't it better to use scala.collection.Seq which is a trait?

[brendanator]

You can't actually run a select statement that uses the val * = List(col1, col2, col3, ...)

In fact if you try you will get a compilation error. This is because you can only use the fetch... methods with columns that have a Extractable typeclass implementation available for them:

    def fetchHead[SingleResult](implicit extractable: Extractable.Aux[ResultSet, A, SingleResult]): SingleResult = ...

There is no Extractable typeclass instance for List[AliasedColumn[_]]. The only reason that the apply method you have seen is there is because it is used internally to sqlest. It will be removed when I work out how

Your other suggestion to use an HList is definitely the way to go. However I think it would be dangerous to make the API to sqlest depend on an external library - people using different versions would not be able to use the project . However by implementing the Extractable typeclass for HList you will be able to do this

I'll make some changes to enable doing this and create an example

[kutchar]

That figures, I was getting the compilation error when trying to use the tuple style apply method.

As far as the HList and 22 limit, I gave it some thought and it might not be that useful of a feature. After all Scala case classes can't have more than 22 fields, so you won't be able to use it anyways.

[hrj]

@kutchar The restriction on 22 fields in case classes was removed in scala 2.11.

[kutchar]

@hrj good to know. Thanks.

[brendanator]

Unfortunately sqlest doesn't currently support case classes with more that 22 columns