post meeting recap - accessing DB from java apps

[Nimret Sandhu]

Thanks Jason - enjoyed the meeting last night. The AST/Antlr stuff brought
back some memories from a compiler class in college :)

here's another interesting framework I've been looking at which also tries
to simplify DB access and stick closer to the metal. I think I came across
it via dropwizard.
http://www.jdbi.org/

I find https://code.google.com/p/mybatis/ to be relatively easy to work
with also.

cheers,
-
Nimret Sandhu
http://www.nimret.org

[Jason Osgood]

Thanks Nimret, Everyone.

The perils of insufficiently prepared live demos.

Not a parse problem.

Alaso, turns out H2 Database is case sensitive, e.g. “Person” != “PERSON”. So the DatabaseMetaData.getTable(...) method wasn’t finding the table.

Right before the meeting, I changed a H2 setting to preserve the case of column names. So that query SELECT first, last FROM Person would generation method PersonSelectResultSet.getFirst() instead of .getFIRST().

Oof.

Handling of unquoted, unbracketed column and table names, both storage and case sensitivity, is handled inconsistently. I’m still investigating the what-ifs, which mostly means making Fado’s schema inspector more robust.

here's another interesting framework I've been looking at...
http://www.jdbi.org/

Interesting. Very light weight.

Note that the queries are not-SQL, they’re a template-ized SQL. So no straight cut and paste between your project and Squirrel / QueryAnalyzer.

Also, JBBI still has a binding (mapping) step. So it’s "SQL parser” is actually a JDBI template parser.


Cheers, Jason

[George Smith]

Since most of my DB related work is in support of CRUD based UI, I seldom
do table joins. As Jason mentioned last night, I am a fan of ORM
(specifically Toplink - the ORM that made life easier - Not more
complicated), but ORMs were not designed for reporting and the few times I
was "forced" to use an ORM for reporting, it was incredibly frustrating, I
don't care which ORM your using - don't use ORMs for reporting!

While I don't do many joins, I do use a lot of inner/nested selects. Fado
will need to support this, IMO.

George

Post by Jason Osgood
Thanks Nimret, Everyone.
The perils of insufficiently prepared live demos.
Not a parse problem.
Alaso, turns out H2 Database is case sensitive, e.g. “Person” != “PERSON”.
So the DatabaseMetaData.getTable(...) method wasn’t finding the table.
Right before the meeting, I changed a H2 setting to preserve the case of
column names. So that query SELECT first, last FROM Person would generation
method PersonSelectResultSet.getFirst() instead of .getFIRST().
Oof.
Handling of unquoted, unbracketed column and table names, both storage and
case sensitivity, is handled inconsistently. I’m still investigating the
what-ifs, which mostly means making Fado’s schema inspector more robust.
here's another interesting framework I've been looking at...
http://www.jdbi.org/
Interesting. Very light weight.
Note that the queries are not-SQL, they’re a template-ized SQL. So no
straight cut and paste between your project and Squirrel / QueryAnalyzer.
Also, JBBI still has a binding (mapping) step. So it’s "SQL parser” is
actually a JDBI template parser.
Cheers, Jason

[Nimret Sandhu]

Post by George Smith
Since most of my DB related work is in support of CRUD based UI, I seldom
do table joins. As Jason mentioned last night, I am a fan of ORM
(specifically Toplink - the ORM that made life easier - Not more
complicated), but ORMs were not designed for reporting and the few times I
was "forced" to use an ORM for reporting, it was incredibly frustrating, I
don't care which ORM your using - don't use ORMs for reporting!

there are lots of packages for doing reporting rather than rolling your own:
http://java-source.net/open-source/charting-and-reporting

I think the problem is that reporting kinda 'sneaks up' on a product as
business wants to learn more and more about how it's being used.

While I don't do many joins, I do use a lot of inner/nested selects. Fado

Post by George Smith
will need to support this, IMO.

Any fairly complicated enterprise schema has been fairly normalized from my
experience requiring joins.

cheers,
Nimret

[Konstantin Ignatyev]

Thank you Jason I hope your presentation made people step back for a moment
and reevaluate value of ORM-s.

Will did presentation on myBatis awhile back
http://www.slideshare.net/williverson/seajug-may-2012-mybatis generally
promoting the same idea that ORM-s oftentimes become impediment as project
grows and evolves.

Speaking of persistence etc. If there will be interest I could share my
experience developing my pet project www.mcytravel.com that started on

Wicket (UI)+ Scala + MongoDB stack,
then evolved into
Wicket (UI)+ Scala + myBatis + my codegenerator (wrote myself
https://github.com/kgignatyev/scala-mybatis-generator) + MySql
and finally settled on
AngularJS + little bit of Wicket (UI) + Scala + Scalatra (REST) customized
to support partial object updates + MongoDB + phantomjs (to make search
engines happy)

the last stack made me 5x more productive than previous stacks and I can
share why (spoiler: many friction points are eliminated).

Note that this project uses different ideology than promoted by
myBatis/FADO. It makes domain objects into cornerstones of application and
everything else derived. That is why I thought it might be interesting
complimentary presentation.

Of course this approach is not for everybody, but has its uses. Very least
that could be a little show case to let everybody judge and decide that
this is horrible contraption that must be avoided at all costs ;)

Regards,

Konstantin

Post by George Smith
Since most of my DB related work is in support of CRUD based UI, I seldom
do table joins. As Jason mentioned last night, I am a fan of ORM
(specifically Toplink - the ORM that made life easier - Not more
complicated), but ORMs were not designed for reporting and the few times I
was "forced" to use an ORM for reporting, it was incredibly frustrating, I
don't care which ORM your using - don't use ORMs for reporting!
While I don't do many joins, I do use a lot of inner/nested selects. Fado
will need to support this, IMO.
George

Post by Jason Osgood
Thanks Nimret, Everyone.
The perils of insufficiently prepared live demos.
Not a parse problem.
Alaso, turns out H2 Database is case sensitive, e.g. “Person” !=
“PERSON”. So the DatabaseMetaData.getTable(...) method wasn’t finding the
table.
Right before the meeting, I changed a H2 setting to preserve the case of
column names. So that query SELECT first, last FROM Person would generation
method PersonSelectResultSet.getFirst() instead of .getFIRST().
Oof.
Handling of unquoted, unbracketed column and table names, both storage
and case sensitivity, is handled inconsistently. I’m still investigating
the what-ifs, which mostly means making Fado’s schema inspector more robust.
here's another interesting framework I've been looking at...
http://www.jdbi.org/
Interesting. Very light weight.
Note that the queries are not-SQL, they’re a template-ized SQL. So no
straight cut and paste between your project and Squirrel / QueryAnalyzer.
Also, JBBI still has a binding (mapping) step. So it’s "SQL parser” is
actually a JDBI template parser.
Cheers, Jason

--
"And the users exclaimed with a laugh and a taunt: It's just what we
asked for but not what we want." -- Unknown

--
Konstantin Ignatyev

PS: If this is a typical day on planet Earth, humans will add fifteen
million tons of carbon to the atmosphere, destroy 115 square miles of
tropical rainforest, create seventy-two miles of desert, eliminate between
forty to one hundred species, erode seventy-one million tons of topsoil,
add 2,700 tons of CFCs to the stratosphere, and increase their population
by 263,000

Bowers, C.A. The Culture of Denial: Why the Environmental Movement Needs a
Strategy for Reforming Universities and Public Schools. New York: State
University of New York Press, 1997: (4) (5) (p.206)

[Nimret Sandhu]

I would be very interested in the presentation. Shoot me an email and I'll
set it up for the next available slot.

cheers,
-
Nimret Sandhu
http://www.nimret.org


On Wed, Jan 22, 2014 at 11:23 AM, Konstantin Ignatyev

Post by Konstantin Ignatyev
Thank you Jason I hope your presentation made people step back for a
moment and reevaluate value of ORM-s.
Will did presentation on myBatis awhile back
http://www.slideshare.net/williverson/seajug-may-2012-mybatis generally
promoting the same idea that ORM-s oftentimes become impediment as project
grows and evolves.
Speaking of persistence etc. If there will be interest I could share my
experience developing my pet project www.mcytravel.com that started on
Wicket (UI)+ Scala + MongoDB stack,
then evolved into
Wicket (UI)+ Scala + myBatis + my codegenerator (wrote myself
https://github.com/kgignatyev/scala-mybatis-generator) + MySql
and finally settled on
AngularJS + little bit of Wicket (UI) + Scala + Scalatra (REST) customized
to support partial object updates + MongoDB + phantomjs (to make search
engines happy)
the last stack made me 5x more productive than previous stacks and I can
share why (spoiler: many friction points are eliminated).
Note that this project uses different ideology than promoted by
myBatis/FADO. It makes domain objects into cornerstones of application and
everything else derived. That is why I thought it might be interesting
complimentary presentation.
Of course this approach is not for everybody, but has its uses. Very least
that could be a little show case to let everybody judge and decide that
this is horrible contraption that must be avoided at all costs ;)
Regards,
Konstantin

Post by George Smith
Since most of my DB related work is in support of CRUD based UI, I seldom
do table joins. As Jason mentioned last night, I am a fan of ORM
(specifically Toplink - the ORM that made life easier - Not more
complicated), but ORMs were not designed for reporting and the few times I
was "forced" to use an ORM for reporting, it was incredibly frustrating, I
don't care which ORM your using - don't use ORMs for reporting!
While I don't do many joins, I do use a lot of inner/nested selects.
Fado will need to support this, IMO.
George

Post by Jason Osgood
Thanks Nimret, Everyone.
The perils of insufficiently prepared live demos.
Not a parse problem.
Alaso, turns out H2 Database is case sensitive, e.g. “Person” !=
“PERSON”. So the DatabaseMetaData.getTable(...) method wasn’t finding the
table.
Right before the meeting, I changed a H2 setting to preserve the case of
column names. So that query SELECT first, last FROM Person would generation
method PersonSelectResultSet.getFirst() instead of .getFIRST().
Oof.
Handling of unquoted, unbracketed column and table names, both storage
and case sensitivity, is handled inconsistently. I’m still investigating
the what-ifs, which mostly means making Fado’s schema inspector more robust.
here's another interesting framework I've been looking at...
http://www.jdbi.org/
Interesting. Very light weight.
Note that the queries are not-SQL, they’re a template-ized SQL. So no
straight cut and paste between your project and Squirrel / QueryAnalyzer.
Also, JBBI still has a binding (mapping) step. So it’s "SQL parser” is
actually a JDBI template parser.
Cheers, Jason

--
"And the users exclaimed with a laugh and a taunt: It's just what we
asked for but not what we want." -- Unknown

--
Konstantin Ignatyev
PS: If this is a typical day on planet Earth, humans will add fifteen
million tons of carbon to the atmosphere, destroy 115 square miles of
tropical rainforest, create seventy-two miles of desert, eliminate between
forty to one hundred species, erode seventy-one million tons of topsoil,
add 2,700 tons of CFCs to the stratosphere, and increase their population
by 263,000
Bowers, C.A. The Culture of Denial: Why the Environmental Movement Needs a
Strategy for Reforming Universities and Public Schools. New York: State
University of New York Press, 1997: (4) (5) (p.206)

[Jason Osgood]

Hi George.

While I don't do many joins, I do use a lot of inner/nested selects. Fado will need to support this, IMO.

Fado’s “generic” SQL grammar accounts for nested SELECTs. It probably mostly works.

Fado’s schema inference does not recurse thru parse trees containing those nested SELECTs. That’s a bit more work; the accounting involved in percolating result columns back to the top. Just requires more effort, and I haven’t specifically needed it, so its in the backlog.

The hardest part of Fado, which concerns me the most, is testing the grammars. Whenever someone shares their grammars, they tend to omit the test cases and data.

ANTLR does have gUnit. I peeked at it. I didn’t understand it.

Eventually, I have to divine or choose a grammar testing strategy. Until then, I should harvest interesting SQL queries, e.g. SQL For Dummies books.


Cheers, Jason