Hi Rainer,

ok, I see, just one last remark, that at the same time the same logic
doesn't apply for OR and AND expressions, I mean you can't write

SELECT t1.* FROM T t1
WHERE *(t1.A = 1 OR t1.A = 2 OR t1.A = 3)  AND t1.B = 10*

instead the resulting sql will have a lot of parenthesis for each DBExpr.

Regards,
Ivan



Rainer Döbele <doeb...@esteam.de> ezt írta (időpont: 2016. szept. 9., P,
10:01):

> Hi Ivan,
>
> originally it was like you are proposing, but we had to change it and
> introduce the parentheses() function.
>
> I cannot remember exactly when this was done (2-3 years at least), but it
> was a design decision, that the developer must have full control over the
> generated SQL, even if that means that the Java code gets more verbose.
>
> How else would you e.g. achieve be able to achieve this:
>
> SELECT (t1.A + t1.B + t1.C + t1.D) * 2.0
>
> Regards,
> Rainer
>
> ________________________________________
> from: Ivan Nemeth [ivan.nem...@gmail.com]
> to: dev@empire-db.apache.org
> subject: Re: Bug with DBColumnExpr.multiplyWith and divide
>
> Hi Rainer,
>
> (JIRA is now ok, it was my fault.)
>
> Ok, I see that parenthesis solves my problem, but I think that an
> expression builder written in Java should follow Java rules. If I write the
> same expression with BigDecimals
>
> A.add(B).multiply(C) it is translated to (A + B) * C
>
> As a Java developer I would expect that DBColumnExpr works the same way. At
> the time when I write an Empire expression in *Java *I don't want to take
> care how it is translated to *SQL*. I understand that too many parenthesis
> may lead to a hard-to-read sql (have you seen any Hibernate generated sql?
> :D), but with parenthesis() the Java code will be much more verbose. The
> main advantage of a query builder is that you have to work with the
> generated sql only a few times. Not to mention that my query is already
> hard to read with 2-3 subqueries and joins, so I have to use some sql
> formatter to check the generated code.
>
> Moreover what I've suggested is only needed if 1. the expression is a
> DBCalcExpr and 2. the op is multiply or divide.
>
> Regards,
> Ivan
> …
>
>
> >
> >
> >
> >
> > Rainer Döbele <doeb...@esteam.de> ezt írta (időpont: 2016. szept. 8.,
> Cs,
> > 21:49):
> >
> >> Hi Ivan,
> >>
> >> I don't know about the JIRA issue. As far as I know anybody who is
> logged
> >> in can create issues.
> >> I have no knowledge about granting or refusing access to anybody.
> >>
> >> But I do have knowledge about the parenthesis issue.
> >> We do not automatically create parenthesis as it imposes some
> >> restrictions or at least might make complex expressions unreadable.
> >> Hence, just as in real life, you have to specifically make your
> >> parentheses where you need them.
> >>
> >> In your case I guess
> >>
> >>         COL = T.A.plus(T.B).parenthesis().multiplyWith(2.0);
> >>
> >> would be the solutions you are looking for.
> >>
> >> Regards
> >> Rainer
> >>
> >>
> >> > from: Ivan Nemeth [mailto:ivan.nem...@gmail.com]
> >> > to: dev@empire-db.apache.org
> >> > subject: Bug with DBColumnExpr.multiplyWith and divide
> >> >
> >> > Hi,
> >> >
> >> > (Rainer, couldn't create an issue in Jira, please can you check it?
> >> (missing
> >> > permission maybe?))
> >> >
> >> > Empire generates wrong sql if you multiply a DBCalcExpr with some
> value,
> >> > example:
> >> >
> >> > DBDatabase db = new DBDatabase() {
> >> > };
> >> > db.open(new DBDatabaseDriverHSql(), null); class Test extends DBTable
> {
> >> >
> >> > final DBTableColumn A;
> >> > final DBTableColumn B;
> >> > public Test(String name, DBDatabase db) { super(name, db); A =
> >> > addColumn("A", DataType.INTEGER, 64, true); B = addColumn("B",
> >> > DataType.INTEGER, 64, true); } }
> >> >
> >> > Test T = new Test("test", db);
> >> > DBCommand cmd = db.createCommand();
> >> > *DBColumnExpr COL = T.A.plus(T.B).multiplyWith(2.0);*
> >> >
> >> > cmd.select(COL);
> >> >
> >> > System.out.println(cmd.getSelect());
> >> >
> >> > The expected sql would be:
> >> >
> >> > *SELECT (t1.A + t1.B) * 2.0 FROM test t1*
> >> >
> >> > but Empire generates it with no parentheses
> >> >
> >> > *SELECT t1.A + t1.B * 2.0 FROM test t1*
> >> >
> >> > Solution would be to append parentheses in DBCalcExpr.addSql method
> >> > something like this (it is required only for * and / operators):
> >> >
> >> >         *buf.append("(");*
> >> >         expr.addSQL(buf, context);
> >> >         *buf.append(")");  *
> >> >         buf.append(op);
> >> >         // Special treatment for adding days to dates ...
> >> >
> >> > What do you think?
> >> >
> >> >
> >> > Regards,
> >> > Ivan
> >>
> >

Reply via email to