Syntax for calling “super” in Java 8 Defender methods

This is a very interesting discussion. How to reference default methods from implemented interfaces throughout the class / interface hierarchy?

Situation:

interface K {
  int m() default { return 88; }
}

interface J extends K {
  int m() default { return K.super.m(); }
                        // ^^^^^^^^^^^^ How to express this?
}

Solution ideas:

  • K.super.m()
  • super.K.m()
  • ((K) super).m()
  • K::m()
  • K.default.m()
  • super<K>.m()
  • super(K).m()
  • super(K.class).m()
  • super[K].m()

Any other crazy ideas? See the discussion here:

http://mail.openjdk.java.net/pipermail/lambda-dev/2012-August/005616.html

Serious SQL: A “convex hull” of “correlated tables”

Now THIS is an interesting, and challenging question on the jOOQ user group:
https://groups.google.com/d/topic/jooq-user/6TBBLYt9eR8/discussion

Say you have a big database with lots of tables and foreign key references. Now you would like to know all tables that are somehow inter-connected by their respective foreign key relationship “paths”. You could call this a “convex hull” around all of your “correlated tables”. Here’s a pseudo-algorithm to achieve this:

// Initialise the hull with an "origin" table
Set tables = {"any table"};
int size = 0;

// Grow the "tables" result until no new tables are added
while (size < tables.size) {
  size = tables.size;

  for (table in tables) {
    tables.addAll(table.referencedTables);
    tables.addAll(table.referencingTables);
  }
}

At the end of this algorithm, you would have all tables in the “tables” set, that are somehow connected with the original “any table”.

Calculate this with jOOQ

With jOOQ’s generated classes, you can easily implement the above algorithm in Java. This would be an example implementation

public class Hull {
  public static Set<Table<?>> hull(Table<?>... tables) {
    Set<Table<?>> result =
        new HashSet<Table<?>>(Arrays.asList(tables));

    // Loop as long as there are no new result tables
    int size = 0;
    while (result.size() > size) {
      size = result.size();

      for (Table<?> table : new ArrayList<Table<?>>(result)) {

        // Follow all outbound foreign keys
        for (ForeignKey<?, ?> fk : table.getReferences()) {
          result.add(fk.getKey().getTable());
        }

        // Follow all inbound foreign keys from tables
        // within the same schema
        for (Table<?> other : table.getSchema().getTables()) {
          if (other.getReferencesTo(table).size() > 0) {
            result.add(other);
          }
        }
      }
    }

    return result;
  }

  public static void main(String[] args) {
    // Calculate the "convex hull" for the T_AUTHOR table
    System.out.println(hull(T_AUTHOR));
  }
}

Do it with SQL

Now this still looks straightforward. But we’re SQL pro’s and we love weird queries, so let’s give Oracle SQL a shot at resolving this problem in a single SQL statement. Here goes (warning, some serious SQL ahead)!

-- "graph" denotes an undirected foreign key reference graph
-- for schema "TEST"
with graph as (
  select c1.table_name t1, c2.table_name t2
  from all_constraints c1
    join all_constraints c2
      on c1.owner = c2.r_owner
      and c1.constraint_name = c2.r_constraint_name
  where c1.owner = 'TEST'
  union all
  select c2.table_name t1, c1.table_name t2
  from all_constraints c1
    join all_constraints c2
      on c1.owner = c2.r_owner
      and c1.constraint_name = c2.r_constraint_name
  where c1.owner = 'TEST'
),
-- "paths" are all directed paths within that schema
-- as a #-delimited string
paths as (
  select sys_connect_by_path(t1, '#') || '#' path
  from graph
  connect by nocycle prior t1 = t2
),
-- "subgraph" are all those directed paths that go trough
-- a given table T_AUTHOR
subgraph as (
  select distinct t.table_name,
    regexp_replace(p.path, '^#(.*)#$', '\1') path
  from paths p
  cross join all_tables t
  where t.owner = 'TEST'
  and p.path like '%#' || t.table_name || '#%'
),
-- This XML-trick splits paths and generates rows for every distinct
-- table name
split_paths as (
select distinct table_name origin,
  cast(t.column_value.extract('//text()') as varchar2(4000)) table_names
from
  subgraph,
  table(xmlsequence(xmltype(
      '<x><x>' || replace(path, '#', '</x><x>') ||
'</x></x>').extract('//x/*'))) t
),
-- "table_graphs" lists every table and its associated graph
table_graphs as (
  select
    origin,
    count(*) graph_size,
    listagg(table_names, ', ') within group (order by 1) table_names
  from split_paths
  group by origin
)
select
  origin,
  graph_size "SIZE",
  dense_rank() over (order by table_names) id,
  table_names
from table_graphs
order by origin

When run against the jOOQ integration test database, this beautiful query will return:

+----------------------+------+----+-----------------------------------------+
| ORIGIN               | SIZE | ID | TABLE_NAMES                             |
+----------------------+------+----+-----------------------------------------+
| T_658_11             |    7 |  3 | T_658_11, T_658_12, T_658_21, T_658_22, |
|                      |      |    | T_658_31, T_658_32, T_658_REF           |
| T_658_12             |    7 |  3 | T_658_11, T_658_12, T_658_21, T_658_22, |
|                      |      |    | T_658_31, T_658_32, T_658_REF           |
| T_658_21             |    7 |  3 | T_658_11, T_658_12, T_658_21, T_658_22, |
|                      |      |    | T_658_31, T_658_32, T_658_REF           |
| T_658_22             |    7 |  3 | T_658_11, T_658_12, T_658_21, T_658_22, |
|                      |      |    | T_658_31, T_658_32, T_658_REF           |
| T_658_31             |    7 |  3 | T_658_11, T_658_12, T_658_21, T_658_22, |
|                      |      |    | T_658_31, T_658_32, T_658_REF           |
| T_658_32             |    7 |  3 | T_658_11, T_658_12, T_658_21, T_658_22, |
|                      |      |    | T_658_31, T_658_32, T_658_REF           |
| T_658_REF            |    7 |  3 | T_658_11, T_658_12, T_658_21, T_658_22, |
|                      |      |    | T_658_31, T_658_32, T_658_REF           |
| T_AUTHOR             |    7 |  1 | T_AUTHOR, T_BOOK, T_BOOK_DETAILS,       |
|                      |      |    | T_BOOK_SALE, T_BOOK_STORE,              |
|                      |      |    | T_BOOK_TO_BOOK_STORE, T_LANGUAGE        |
| T_BOOK               |    7 |  1 | T_AUTHOR, T_BOOK, T_BOOK_DETAILS,       |
|                      |      |    | T_BOOK_SALE, T_BOOK_STORE,              |
|                      |      |    | T_BOOK_TO_BOOK_STORE, T_LANGUAGE        |
| T_BOOK_DETAILS       |    7 |  1 | T_AUTHOR, T_BOOK, T_BOOK_DETAILS,       |
|                      |      |    | T_BOOK_SALE, T_BOOK_STORE,              |
|                      |      |    | T_BOOK_TO_BOOK_STORE, T_LANGUAGE        |
| T_BOOK_STORE         |    7 |  1 | T_AUTHOR, T_BOOK, T_BOOK_DETAILS,       |
|                      |      |    | T_BOOK_SALE, T_BOOK_STORE,              |
|                      |      |    | T_BOOK_TO_BOOK_STORE, T_LANGUAGE        |
| T_BOOK_TO_BOOK_STORE |    7 |  1 | T_AUTHOR, T_BOOK, T_BOOK_DETAILS,       |
|                      |      |    | T_BOOK_SALE, T_BOOK_STORE,              |
|                      |      |    | T_BOOK_TO_BOOK_STORE, T_LANGUAGE        |
| T_DIRECTORY          |    1 |  2 | T_DIRECTORY                             |
| T_LANGUAGE           |    7 |  1 | T_AUTHOR, T_BOOK, T_BOOK_DETAILS,       |
|                      |      |    | T_BOOK_SALE, T_BOOK_STORE,              |
|                      |      |    | T_BOOK_TO_BOOK_STORE, T_LANGUAGE        |
| X_TEST_CASE_64_69    |    4 |  4 | X_TEST_CASE_64_69, X_TEST_CASE_71,      |
|                      |      |    | X_TEST_CASE_85, X_UNUSED                |
| X_TEST_CASE_71       |    4 |  4 | X_TEST_CASE_64_69, X_TEST_CASE_71,      |
|                      |      |    | X_TEST_CASE_85, X_UNUSED                |
| X_TEST_CASE_85       |    4 |  4 | X_TEST_CASE_64_69, X_TEST_CASE_71,      |
|                      |      |    | X_TEST_CASE_85, X_UNUSED                |
| X_UNUSED             |    4 |  4 | X_TEST_CASE_64_69, X_TEST_CASE_71,      |
|                      |      |    | X_TEST_CASE_85, X_UNUSED                |
+----------------------+------+----+-----------------------------------------+

Can you beat this? 🙂

I challenge you to write a shorter query and to achieve the same result! Here’s the integration test database:
https://github.com/jOOQ/jOOQ/blob/master/jOOQ-test/src/org/jooq/test/oracle/create.sql

Note that the above query is horribly inefficient. There’s a lot of potential in beating that, too!

You never stop learning about Oracle features

Oracle‘s name is no coincidence. It is truly an oracle, telling you mystical, secret things about your database. It may take great expertise and skill to optimally extract that knowledge from this monster. While it implements vast parts of the SQL:2008 standard, it ships with a lot of features no other database has, but which are likely to make it into the SQL:2011 and subsequent standards, eventually.

One particular syntax clause is the PARTITION BY clause. It is very similar to the GROUP BY clause, but it can appear in various other contexts. The most trivial one is that of window functions / analytical functions. The most advanced one is that of the MODEL clause (which I shall blog about soon). But one of the most hidden ones is that of the OUTER JOIN clause. With Oracle, you can issue a “partitioned outer join”.

The formal definition of an Oracle OUTER JOIN clause can be seen here:

 

The query_partition_clause is a regular “PARTITION BY expr” clause. Now, this useful addition that can be put to the left or to the right of the actual OUTER JOIN clause, allowing to specify a means of how to partition either side in a way that there will be at least one (possibly empty) record for every partition, in case the OUTER JOIN does not produce any records for such a partition. Whew.

In more intelligible English: This clause helps filling your result set with blank records. There!

So, whenever you feel you need to fill in some blank rows with Oracle, remember that you might just have found yourself a use-case for the partitioned outer join! (This clause will be supported by jOOQ 2.5.0)

SLICK, integrating SQL into Scala

Now it’s official – even if version numbers are still preceded by a “zero” major release: SLICK has been publicly announced by Typesafe:

SLICK stands for Scala Language-Integrated Connection Kit, which is more or less the Scala equivalent for LINQ-to-SQL. Note that I say LINQ-to-SQL, not LINQ in general, as Scala already has sufficient means of querying collections using the Scala language itself.

Here’s a sample of what SLICK code will look like (taken from the SLICK website):

object Coffees extends Table[(String, Int, Double)]("COFFEES") {
  def name = column[String]("COF_NAME", O.PrimaryKey)
  def supID = column[Int]("SUP_ID")
  def price = column[Double]("PRICE")
  def * = name ~ supID ~ price
}

Coffees.insertAll(
  ("Colombian",         101, 7.99),
  ("Colombian_Decaf",   101, 8.99),
  ("French_Roast_Decaf", 49, 9.99)
)

val q = for {
  c <- Coffees if c.supID === 101
  //                       ^ comparing Rep[Int] to Rep[Int]!
} yield (c.name, c.price)

println(q.selectStatement)

q.foreach { case (n, p) => println(n + ": " + p) }

As you can see, SLICK neatly integrates with Scala’s own syntax. As with LINQ-to-SQL, SLICK’s goal is to

“write your database queries in Scala instead of SQL”

This is quite orthogonal to what jOOQ is aiming for:

“SQL was never meant to be anything other than… SQL!”

As a reminder, see my previous blog post about how jOOQ integrates with Scala, and how you can write almost-SQL queries in Scala using jOOQ against 13 popular databases. It would be interesting to compare the two approaches side-by-side in an independent evaluation, to see the pro’s and con’s of each one, in terms of

  • Developer productivity
  • Maintainability
  • Performance
  • Feature scope
  • etc.

I think it’s time someone made that evaluation. An example can be seen here:

http://stackoverflow.com/questions/10537766/closest-equivalent-to-sqlalchemy-for-java-scala

Survey about the Java 8 “default method” syntax

Influence the future of Java now! Participate in this official survey by Brian Goetz:
https://www.surveymonkey.com/s/9VPJZQQ

MySQL Bad Idea #384

MySQL is a database of compromise. Compromise between running a production-ready relational database and being popular with all sorts of hackers – mostly the ones that don’t really like SQL. And because they don’t really like SQL, they choose MySQL, as MySQL is very forgiving. It is just as forgiving as their favourite language PHP, which forgives their mistakes involving escaping and quoting through funny things like “magic quotes”. Not only is MySQL forgiving, it allows you to write “wrong” SQL and still does something with it. Here’s what I mean by “wrong” SQL:

In MySQL, you can legally execute the following statement:

SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;

The statement was taken from here:
http://dev.mysql.com/doc/refman/5.6/en/group-by-hidden-columns.html

So what does this statement even mean? What will be returned in the c.name projection? MAX(c.name)? ANY(c.name)? FIRST(c.name)? NULL? 42? According to the documentation, ANY(c.name) would best describe what’s going on. This peculiar syntax is probably quite clever for those few that really know when this is useful. When they know exactly, that o.custid and c.name have a 1:1 correlation, and they can speed things up a little by avoiding writing things like MAX(c.name), or by adding c.name to the GROUP BY clause (“yes, saved yet another 8 characters”).

But the bulk of newbie MySQL users will be confused by this.

  • First, they will be confused because they don’t get the c.name they’d expect.
  • Secondly, they will eventually switch over to another database that gets these things right, and be frustrated all over again, over the funny syntax errors, such as ORA-00979 not a GROUP BY expression

So please,

  • MySQL users: stop using this non-feature. It will only cause pain and suffering, even if you know how/why it works. SQL’s GROUP BY was not meant to work that way.
  • MySQL: Deprecate this non-feature.

The true spirit of Open Source

As an open source developer, I’m often asking myself why the hell am I going through all of this pain in my free time to deliver quality software, when I’m already doing this in my office?? Sure, it’s fun, you can try out new things, deepen your knowledge in a specific field, it helps boost your career, etc. etc.

But every now and then, I’m reminded of another reason:

I believe in open source

This is just like other people saying

I believe in charity work
I believe in militia politics
I believe in volunteer firefighter engagement
I believe in helping that poor grandma across the street

They do, whereas I…

I believe in open source

All these things make the world go round, without most people noticing, as there is no big fame in it. Of course, there’s always that other point of view on open source. You can make as much money with organised open source, as with organised charity work, or organised militia politics (a.k.a. lobbying), and that’s perfectly fine – why shouldn’t you? But the driving force is always the same, regardless of the pay: It’s belief. Here’s another open source project I’ve recently re-discovered, that has a very nice reason of being, along the same lines. It’s EMMA. Citing the EMMA website:

Until recently, the world of Java development had been plagued by an absurd discrepancy: Java developers had excellent free IDEs, free compilers, free test frameworks but had to rely on code coverage tools that charged an arm and a leg in license fees. As a Java pro, I would like to use the same free coverage tool regardless of whether it is a massive commercial project at work or a small fun project at home. I’ve created EMMA to be that tool.

Beautiful!