3

I've looked at ways to use squeryl to delete all records from a table. The only thing I could come up with is

myTable.deleteWhere(r => r.id.isNotNull)  //id is the primary key

This seems strange and possibly inefficient.

What is the cleanest way to delete all records from a table when using squeryl?

iwein
  • 25,788
  • 10
  • 70
  • 111
  • 1
    About effeciency -- it may be true, but why would we need deleting the whole database to be quick? This seems unprobable to be a bottleneck. – VasiliNovikov Apr 12 '13 at 17:58
  • @VasyaNovikov You're right. I'm using it for testing, so I want it to be quick, but then again, I don't need loads of data for testing. – iwein Apr 12 '13 at 18:20

1 Answers1

4

The deleteWhere clause takes any logical boolean, so you could simply say:

myTable.deleteWhere(r => 1 === 1)

Which should output the statement: 

DELETE FROM mytable WHERE 1 = 1

If you want to eliminate the where clause automatically, you could try:

myTable.deleteWhere(r => 1 === 1.inhibitWhen(true))

Which should suppress the where clause altogether.

If you are looking for an even more efficient method and your database supports TRUNCATE or another equivalent function, you can get a java.sql.Connection from org.squeryl.Session and issue the query directly through JDBC. Unfortunately, this would lose some of the type safety Squeryl offers.

jcern
  • 7,798
  • 4
  • 39
  • 47
  • Thanks for the options, very helpful. Strange there is no deleteAll, or delete(everyWhere), or something don't you think? – iwein Apr 11 '13 at 06:58
  • Yeah, there are a few functions that I am sometimes surprised do not exist. However, it does function across database systems - so the cause can sometimes lie there. Since you can usually find something that works or execute native SQL - I can usually work around anything that is missing. – jcern Apr 11 '13 at 07:17
  • By the way, the inhibitWhen doesn't work on MySQL: [error] RuntimeException: Exception while executing statement : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2 [error] errorCode: 1064, sqlState: 42000 [error] delete from Parser [error] where – iwein Apr 11 '13 at 07:35
  • ""Unfortunately, this would lose some of the type safety Squeryl offers." Given we're doing a table truncate here, what might be the impact of this loss of type safety? (i.e. right now I can't see how type safety comes into it) – The Archetypal Paul Apr 11 '13 at 11:33
  • Well, by having everything validated through the compiler, you can avoid errors in the SQL like: `TRUNCTE table` where the misspelling would cause an error. Also, by having the class type you know completely that you have the right table instead of an accidental cut and paste. Granted it is less relevant for the `deleteWhere` clause, it can still be useful. – jcern Apr 11 '13 at 16:16