The Forum for Discussion about The Third Manifesto and Related Matters

Please or Register to create posts and topics.

Almost (but not quite) an ANNOUNCE

This isn't an official announcement, yet, but my Wrapd database abstraction layer and schema manager for Java has reached the point where release-early/release-often makes sense so I've made it public.

Much work is still to do but if you're interested in its early form, see https://github.com/DaveVoorhis/Wrapd

I'm the forum administrator and lead developer of Rel. Email me at dave@armchair.mb.ca with the Subject 'TTM Forum'. Download Rel from https://reldb.org
Quote from Dave Voorhis on July 26, 2021, 12:08 pm

Much work is still to do but if you're interested in its early form, see https://github.com/DaveVoorhis/Wrapd

Thanks Dave, I love the punning.

Otherwise: 'fraid I can't see "SQL" and 'rapt' in the same sentence; I'm not a Java aficionado, and I hate everything about SQL syntax, so perhaps most of my distaste arises from Java-smell reacting toxically with SQL fumes ...

  • Far too much passed as strings, so they presumably don't get validated/type-checked until run time. I know this is how Java interacts with SQL currently. It's just wrong. No static type safety.
  • "SELECT * FROM mytable WHERE x > ? AND x < ?" I really don't like positionally-based parameters; and in complex queries it'd be far too easy to mix up your ?s. Is there (planned to be) some way to name those parameters? (At least usually SQL doesn't use position basis.)
  • .forEach(tuple -> ... tuple.x ... tuple.y) (those who were arguing against tuples in that earlier thread are not going to be happy).

 

I'm not happy because that stinks of RBAR. I'd want to map a function, in which the tuple argument is implicit, maybe

.map(System.out.println("x = " + x + ", y = " + y)) The free terms x, y are taken to be field selectors applying to the implicit elements of the collection.

I guess there's an implicit convert x, y  to string in there as well?

  • database.createTupleFromQueryAll(getCodeDir(), "MyTable", "SELECT * FROM mytable");

So if you're setting up an association with some "MyTable" object, why do I need to specify both the object and the table name again in the database.query( )? Verbosity heaped upon verbosity.

"SELECT * FROM mytable" really? not just mytable -- and not as a string -- isn't this a variable with scope in the Java?

(Why is it "MyTable" as string in the create but MyTable.class not as string in the query?)

  • As a minimum, I'd be looking for some way to express queries in an algebra -- probably something close to the benighted forms (of alleged Codd operators) taught in Undergrad courses, so that it's easy to see the translation to SQL.

And this would be a teachable moment to get people into the habit of SELECT DISTINCT ....

You're presumably not parsing/validating those strings passed to SQL(?) I would have thought it would be an opportunity to put some controls and smarts around database access, even if you end up passing the strings through.

Quote from AntC on July 27, 2021, 2:33 am
Quote from Dave Voorhis on July 26, 2021, 12:08 pm

Much work is still to do but if you're interested in its early form, see https://github.com/DaveVoorhis/Wrapd

Thanks Dave, I love the punning.

Otherwise: 'fraid I can't see "SQL" and 'rapt' in the same sentence; I'm not a Java aficionado, and I hate everything about SQL syntax, so perhaps most of my distaste arises from Java-smell reacting toxically with SQL fumes ...

Yes, Wrapd is a toxic reaction between SQL and Java, particularly the standard Java machinery for accessing SQL databases -- JDBC -- and the standard (rather functional-language inspired) Java machinery for manipulating collections of class instances in-memory -- Java Streams.

In short, Wrapd is broadly a bridge between SQL and Java, and specifically between JDBC and Java Streams, to hopefully reduce the pain of some of the usual SQL-in-Java pain points. There is -- and this is an important point -- intentionally no attempt to hide SQL (you use it the way you usually use it in JDBC) and every attempt to make it easy to use the results of SELECT queries in Java Streams.

That's pretty much it. That's its main selling point: JDBC-to-Streams, bridged.

It's by no means the only such attempt. There are many. This one is mine.

Such attempts vary in complexity. Some are simpler than mine, like a negligible skin over JDBC and nothing to do with Streams. Some are much, much, much more complex.

The most popular complex one is called Hibernate. It is a full-featured ORM. It's huge and hulking and vast and has its own SQL-like HQL query language. It hides SQL. It also hides a gaggle of gotchas, bugs, weirdness, complexity, awkwardness, and general pain. I hate it.

  • Far too much passed as strings, so they presumably don't get validated/type-checked until run time. I know this is how Java interacts with SQL currently. It's just wrong. No static type safety.

Queries are not checked at the query definition level in Java, that's true, except to the extent that JDBC and the SQL DBMS itself checks them.

They are type-checked -- per the usual Java static type-checking -- when you use them via Java methods.

First, you create your database. This is not a "code first" library, where the programming language code implicitly creates SQL and defines the database from that. This is a "SQL first" library, where the presumption is that the database exists, either via Wrapd's schema mechanisms or externally defined.

Then, you create your SQL queries inside Java.

Wrapd encourages you to define each query once using SQL in something akin to a unit test, which runs the query (with example parameters if appropriate.) That "SQL unit test" emits compiled Java code: a class to define rows (tuples) to hold the query result, and a method to invoke the query via a Java method -- with type-checked parameters, if needed.

public class MyQueryDefinitions extends QueryDefiner {
      ...
      public QueryDefinition QueryDefinition01() {
          return new QueryDefinition("MyTableQuery01", 
              "SELECT * FROM mytable WHERE x > ? AND x < ?", 3, 7);
      }
      ...
}

That's just one query. You could define ten or dozens or hundreds in the same class and yet more in other classes. These define (and test!) the interface to the database. Collectively, they define the database abstraction layer. Outside of the database abstraction layer (and the almost entirely separate schema migration stuff, if you use it) there is no exposed SQL (unless you need some ad-hoc SQL somewhere, but hopefully not.)

There's some machinery (not shown here) that iterates all the query definitions and generates Java source code from them and compiles the source code to binary .class files.

The result is that you automatically get code that allows you to invoke the above query like this:

MyTableQuery01.query(database, 6, 12)
              .forEach(tuple -> System.out.println("x = " + tuple.x + ", y = " + tuple.y));

In other words, you invoke your previously-defined SQL queries via auto-generated Java methods with type-checked parameters (and no visible SQL -- that's back in the definition) that emit Java Streams with native, compile-time-checked attributes.

The hastily-hacked work-in-progress documentation -- which is little more than my notes to myself, at this point, and made of minimally-edited copies of some of the Wrapd unit tests -- also includes a nasty example of an ad-hoc (ish) query because I didn't want to forget to document it, and it's arguably presented in the wrong order and isn't the friendliest example. I should remove it. (I will remove it.)

  • "SELECT * FROM mytable WHERE x > ? AND x < ?" I really don't like positionally-based parameters; and in complex queries it'd be far too easy to mix up your ?s. Is there (planned to be) some way to name those parameters? (At least usually SQL doesn't use position basis.)

It's how JDBC does it. I could, I suppose, lightly wrap it with some text-replacement machinery, though I'm trying to use existing JDBC/Streams facilities wherever possible, without altering or adding layers to them. But I can see the value. Must think on this.

  • .forEach(tuple -> ... tuple.x ... tuple.y) (those who were arguing against tuples in that earlier thread are not going to be happy).

.forEach(...) is a canonical Streams operator, chosen because I thought it might be helpfully illustrative. There are many Streams methods. I could have chosen others (including map) for example purposes, but I didn't.

I'm not happy because that stinks of RBAR. I'd want to map a function, in which the tuple argument is implicit, maybe

.map(System.out.println("x = " + x + ", y = " + y)) The free terms x, y are taken to be field selectors applying to the implicit elements of the collection.

Aside from slight difference in syntax (I think), you can do exactly that if I recall correctly.

But that's all Java Streams stuff. Wrapd simply emits a Stream from certain methods. What you do with the Streams after that is up to you and Java.

I guess there's an implicit convert x, y  to string in there as well?

Yes, in that context, Java implicitly invokes x.toString() and y.toString().

  • database.createTupleFromQueryAll(getCodeDir(), "MyTable", "SELECT * FROM mytable");

So if you're setting up an association with some "MyTable" object, why do I need to specify both the object and the table name again in the database.query( )? Verbosity heaped upon verbosity.

Yes, this is some of the ad-hoc (ish) query stuff, which I wanted to get down on paper/page but ideally no one will ever use it except me, as part of Wrapd itself. The example I've given above, here, is more reflective of intended practical use.

"SELECT * FROM mytable" really? not just mytable -- and not as a string -- isn't this a variable with scope in the Java?

No, it's how JDBC works. Hibernate (for example) can represent SQL tables as Java variables, but... So much complexity, overhead, pitfalls, gotchas, epic breakage (in production!) if you fat-finger an annotation or XML configuration file somewhere. Wrapd doesn't have annotations or XML configuration files.

(Why is it "MyTable" as string in the create but MyTable.class not as string in the query?)

"MyTable" in createTupleFromQueryAll(...) says, "I want you to use the result of evaluating this query to generate and compile a new Java class called 'MyTable'."

MyTable.class is using the compiled Java class created by createTupleFromQueryAll(...)

What's not shown is how these would normally be completely distinct steps, possibly even two separate subprojects. One project hosts and tests SQL query (and schema) definitions and emits Java code. The other hosts the collection of Java methods that internally invoke the SQL queries -- i.e., the database abstraction layer -- and the application code that uses them (or maybe that's even a third subproject.)

  • As a minimum, I'd be looking for some way to express queries in an algebra -- probably something close to the benighted forms (of alleged Codd operators) taught in Undergrad courses, so that it's easy to see the translation to SQL.

It's not a teaching tool. It's a tool for me to write production Java applications, particularly ones with vast quantities of predefined queries, and that require schema migration as part of deployment. If anyone else can use it too, that's a bonus.

Expressing queries in an (in-language) algebra (ish) is, again, the Hibernate approach. (Well, sort of...)

And this would be a teachable moment to get people into the habit of SELECT DISTINCT ....

I might consider that as the documentation evolves, but as the target user base (outside of me) is a typical Java developer for whom such subtleties are more likely to launch arguments than enlightenment, I'm going to keep the SQL as typical and uncontroversial as possible.

You're presumably not parsing/validating those strings passed to SQL(?)

No more or less than JDBC does, and only at definition time. The code that uses the queries is a collection of conventional type-checked Java methods where Wrapd provides Java Streams machinery to make the query result sets convenient to use and type-safe too.

That's one of the big benefits of Wrapd over raw JDBC.  With raw JDBC, accessing columns in a result set (collection of result rows) is only dynamic. Spell a column name incorrectly and the result is a run-time error. With Wrapd, accessing columns in a result set (Stream) is via Java-native attributes.

I would have thought it would be an opportunity to put some controls and smarts around database access, even if you end up passing the strings through.

Wrapd does put controls and smarts around database access. In short, verified SQL text queries are used to emit static code. Then you use the static code, not the SQL text. SQL query text appears only in what are effectively tests which also generate the (safe, string-free) Java code to invoke them.  Each SQL query string is parsed and validated once at definition time, and isn't visible to -- or alterable by -- the Java method that runs it.

But validation is wholly within the existing JDBC and Java Streams facilities. The idea is not to replace or even augment them, but to create a narrow bridge between them that didn't really exist before.

Otherwise, we'd be going in the Hibernate direction.

I'm going in the opposite direction.

I'm the forum administrator and lead developer of Rel. Email me at dave@armchair.mb.ca with the Subject 'TTM Forum'. Download Rel from https://reldb.org
Quote from Dave Voorhis on July 27, 2021, 8:31 am
Quote from AntC on July 27, 2021, 2:33 am
Quote from Dave Voorhis on July 26, 2021, 12:08 pm

Much work is still to do but if you're interested in its early form, see https://github.com/DaveVoorhis/Wrapd

Thanks Dave, I love the punning.

Otherwise: 'fraid I can't see "SQL" and 'rapt' in the same sentence; I'm not a Java aficionado, and I hate everything about SQL syntax, so perhaps most of my distaste arises from Java-smell reacting toxically with SQL fumes ...

Yes, Wrapd is a toxic reaction between SQL and Java, particularly the standard Java machinery for accessing SQL databases -- JDBC -- and the standard (rather functional-language inspired) Java machinery for manipulating collections of class instances in-memory -- Java Streams.

In short, Wrapd is broadly a bridge between SQL and Java, and specifically between JDBC and Java Streams, to hopefully reduce the pain of some of the usual SQL-in-Java pain points. There is -- and this is an important point -- intentionally no attempt to hide SQL (you use it the way you usually use it in JDBC) and every attempt to make it easy to use the results of SELECT queries in Java Streams.

That's pretty much it. That's its main selling point: JDBC-to-Streams, bridged.

Thanks Dave for the comprehensive reply. So you're making a travel-pack for a land I've never travelled, and am unlikely to visit. I'll shut up and leave you to it.

Just one more small q: how does this relate to SQL Stored procedures? How would that compare to "create your SQL queries inside Java"? And wouldn't Stored Procedures give stronger validation/type-safety at 'compile time'? Also a better mechanism for passing run-time parameters?

 

... Then, you create your SQL queries inside Java.

Wrapd encourages you to define each query once using SQL in something akin to a unit test, which runs the query (with example parameters if appropriate.) That "SQL unit test" emits compiled Java code: a class to define rows (tuples) to hold the query result, and a method to invoke the query via a Java method -- with type-checked parameters, if needed.

 

Quote from AntC on July 27, 2021, 10:49 am
Quote from Dave Voorhis on July 27, 2021, 8:31 am
Quote from AntC on July 27, 2021, 2:33 am
Quote from Dave Voorhis on July 26, 2021, 12:08 pm

Much work is still to do but if you're interested in its early form, see https://github.com/DaveVoorhis/Wrapd

Thanks Dave, I love the punning.

Otherwise: 'fraid I can't see "SQL" and 'rapt' in the same sentence; I'm not a Java aficionado, and I hate everything about SQL syntax, so perhaps most of my distaste arises from Java-smell reacting toxically with SQL fumes ...

Yes, Wrapd is a toxic reaction between SQL and Java, particularly the standard Java machinery for accessing SQL databases -- JDBC -- and the standard (rather functional-language inspired) Java machinery for manipulating collections of class instances in-memory -- Java Streams.

In short, Wrapd is broadly a bridge between SQL and Java, and specifically between JDBC and Java Streams, to hopefully reduce the pain of some of the usual SQL-in-Java pain points. There is -- and this is an important point -- intentionally no attempt to hide SQL (you use it the way you usually use it in JDBC) and every attempt to make it easy to use the results of SELECT queries in Java Streams.

That's pretty much it. That's its main selling point: JDBC-to-Streams, bridged.

Thanks Dave for the comprehensive reply. So you're making a travel-pack for a land I've never travelled, and am unlikely to visit. I'll shut up and leave you to it.

Just one more small q: how does this relate to SQL Stored procedures? How would that compare to "create your SQL queries inside Java"?

From the Java/JDBC point of view, invoking a stored procedure is roughly the same as invoking a parametric query / prepared statement.

And wouldn't Stored Procedures give stronger validation/type-safety at 'compile time'? Also a better mechanism for passing run-time parameters?

You have the essential Java→JDBC→DBMS→JDBC→Java journey and associated issues whether the DBMS executes a stored procedure or evaluates a query.

Though there might be some possible automation around retrieving stored procedure metadata from the DBMS and using it to automatically generate Java methods to invoke corresponding stored procedures. I had a student explore that as a final-year undergrad dissertation a few years ago and the results looked promising.

I'm the forum administrator and lead developer of Rel. Email me at dave@armchair.mb.ca with the Subject 'TTM Forum'. Download Rel from https://reldb.org
Dave, this looks pretty great!
public class MyQueryDefinitions extends QueryDefiner {
...
public QueryDefinition QueryDefinition01() {
return new QueryDefinition("MyTableQuery01",
"SELECT * FROM mytable WHERE x > ? AND x < ?", 3, 7);
}
...
}
public class MyQueryDefinitions extends QueryDefiner { ... public QueryDefinition QueryDefinition01() { return new QueryDefinition("MyTableQuery01", "SELECT * FROM mytable WHERE x > ? AND x < ?", 3, 7); } ... }
public class MyQueryDefinitions extends QueryDefiner {
      ...
      public QueryDefinition QueryDefinition01() {
          return new QueryDefinition("MyTableQuery01", 
              "SELECT * FROM mytable WHERE x > ? AND x < ?", 3, 7);
      }
      ...
}

One thing that would make it even better is named parameters in the QueryDefinition, so perhaps something like this:

public class MyQueryDefinitions extends QueryDefiner {
 ... 
  public QueryDefinition QueryDefinition01() {
    return new QueryDefinition("MyTableQuery01", "SELECT * FROM mytable WHERE x > ?min AND x < ?max AND y > ?min AND y < ?max", Map.entry("min", 3), Map.entry("max", 7));
 }
 ...
}

Actually, the "Map.entry" could be overkill, just expect alternating keys and values as in Map.of

Quote from tobega on September 11, 2021, 6:46 pm
Dave, this looks pretty great!
Thanks!
...

One thing that would make it even better is named parameters in the QueryDefinition ...

Antc mentioned that too, and I agree that it would be good. At the moment, it's a very minimal skin around existing JDBC facilities. But as I mentioned in my reply to Antc, I could, I suppose, lightly wrap it with some text-replacement machinery, though I'm trying to use existing JDBC/Streams facilities wherever possible without altering or adding layers to them. But I can see the value. Must (continue to) think on this.

I'm the forum administrator and lead developer of Rel. Email me at dave@armchair.mb.ca with the Subject 'TTM Forum'. Download Rel from https://reldb.org
Quote from Dave Voorhis on September 11, 2021, 8:23 pm
Quote from tobega on September 11, 2021, 6:46 pm
Dave, this looks pretty great!
Thanks!
...

One thing that would make it even better is named parameters in the QueryDefinition ...

Antc mentioned that too, and I agree that it would be good. At the moment, it's a very minimal skin around existing JDBC facilities. But as I mentioned in my reply to Antc, I could, I suppose, lightly wrap it with some text-replacement machinery, though I'm trying to use existing JDBC/Streams facilities wherever possible without altering or adding layers to them. But I can see the value. Must (continue to) think on this.

Great!

I also forgot the second part of the ask, to have a query builder instead of positional arguments when running the query.

MyTableQuery01.builder().min(6).max(12).query(database)
              .forEach(tuple -> System.out.println("x = " + tuple.x + ", y = " + tuple.y));

 

Quote from tobega on September 12, 2021, 8:01 am
Quote from Dave Voorhis on September 11, 2021, 8:23 pm
Quote from tobega on September 11, 2021, 6:46 pm
Dave, this looks pretty great!
Thanks!
...

One thing that would make it even better is named parameters in the QueryDefinition ...

Antc mentioned that too, and I agree that it would be good. At the moment, it's a very minimal skin around existing JDBC facilities. But as I mentioned in my reply to Antc, I could, I suppose, lightly wrap it with some text-replacement machinery, though I'm trying to use existing JDBC/Streams facilities wherever possible without altering or adding layers to them. But I can see the value. Must (continue to) think on this.

Great!

I also forgot the second part of the ask, to have a query builder instead of positional arguments when running the query.

MyTableQuery01.builder().min(6).max(12).query(database)
.forEach(tuple -> System.out.println("x = " + tuple.x + ", y = " + tuple.y));
MyTableQuery01.builder().min(6).max(12).query(database) .forEach(tuple -> System.out.println("x = " + tuple.x + ", y = " + tuple.y));
MyTableQuery01.builder().min(6).max(12).query(database)
              .forEach(tuple -> System.out.println("x = " + tuple.x + ", y = " + tuple.y));

 

My concern is that approach implies run-time error checking and thus run-time failures. E.g., what if you write MyTableQuery01.builder().query(database) ?

Or, MyTableQuery01.builder().min(6).query(database) ?

Presumably, the compiler accepts it and it doesn't fail until runtime?

If the query is invoked as MyTableQuery01.query(database, 6, 12), the compiler obligates both parameters be supplied (and checks their type, of course.) It's also less verbose and it's just another Java method invocation like all the others.

I could maybe make it so it has to be something like MyTableQuery01.query(database, MyTableQuery01.min(6), MyTableQuery01.max(12)), but I'm not sure you'd be gaining over the  additional verbosity, unless you're already regularly doing that sort of thing with all/many of your Java methods.

I'm the forum administrator and lead developer of Rel. Email me at dave@armchair.mb.ca with the Subject 'TTM Forum'. Download Rel from https://reldb.org
Quote from Dave Voorhis on September 12, 2021, 4:47 pm
Quote from tobega on September 12, 2021, 8:01 am
Quote from Dave Voorhis on September 11, 2021, 8:23 pm
Quote from tobega on September 11, 2021, 6:46 pm
Dave, this looks pretty great!
Thanks!
...

One thing that would make it even better is named parameters in the QueryDefinition ...

Antc mentioned that too, and I agree that it would be good. At the moment, it's a very minimal skin around existing JDBC facilities. But as I mentioned in my reply to Antc, I could, I suppose, lightly wrap it with some text-replacement machinery, though I'm trying to use existing JDBC/Streams facilities wherever possible without altering or adding layers to them. But I can see the value. Must (continue to) think on this.

Great!

I also forgot the second part of the ask, to have a query builder instead of positional arguments when running the query.

MyTableQuery01.builder().min(6).max(12).query(database)
.forEach(tuple -> System.out.println("x = " + tuple.x + ", y = " + tuple.y));
MyTableQuery01.builder().min(6).max(12).query(database) .forEach(tuple -> System.out.println("x = " + tuple.x + ", y = " + tuple.y));
MyTableQuery01.builder().min(6).max(12).query(database)
              .forEach(tuple -> System.out.println("x = " + tuple.x + ", y = " + tuple.y));

 

My concern is that approach implies run-time error checking and thus run-time failures. E.g., what if you write MyTableQuery01.builder().query(database) ?

Or, MyTableQuery01.builder().min(6).query(database) ?

Presumably, the compiler accepts it and it doesn't fail until runtime?

If the query is invoked as MyTableQuery01.query(database, 6, 12), the compiler obligates both parameters be supplied (and checks their type, of course.) It's also less verbose and it's just another Java method invocation like all the others.

I could maybe make it so it has to be something like MyTableQuery01.query(database, MyTableQuery01.min(6), MyTableQuery01.max(12)), but I'm not sure you'd be gaining over the  additional verbosity, unless you're already regularly doing that sort of thing with all/many of your Java methods.

Ah, that's a good point, of course. I suppose YMMV but in my experience it is more common to confuse which integer is which, rather than trying to supply a string instead of an integer.

Although here we also need to supply all parameters. There are ways to do that in a typesafe way, by returning a new type for receiving every parameter, but that probably isn't worth the effort. So how bad is a runtime crash? If it happens because I supplied too few parameters, it will crash on the first test I try, with a clear error message, so really not a huge problem.