[
https://issues.apache.org/jira/browse/GROOVY-9159?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16873699#comment-16873699
]
Daniel Sun commented on GROOVY-9159:
------------------------------------
[~blackdrag] I saw your comment just now... The WITH-Clause was added, please
see the 8th example :)
> [GEP] Support LINQ, aka GINQ
> ----------------------------
>
> Key: GROOVY-9159
> URL: https://issues.apache.org/jira/browse/GROOVY-9159
> Project: Groovy
> Issue Type: New Feature
> Reporter: Daniel Sun
> Priority: Major
> Fix For: 4.x
>
>
> h2. *Ⅰ. Background*
> In order to make querying different types of data sources convenient, we need
> a unified querying interface, i.e. GINQ
> h2. *Ⅱ. Solution*
> The basic rationale can be shown as follows:
> *Groovy User* ==_writes GINQ code_==> *Parrot Parser* ==generates AST==>
> *GINQ Engine* ==_translates AST to Stream-Like method invocations_==>
> *Bytecode Writer*
> h3. {{translates AST to Stream-Like method invocations}} can be designed for
> different cases:
> h4. 1) target objects are all collections
> translates AST to Java 8+ stream method invocations
> h4. 2) target objects are all DB related objects
> translates AST to *JOOQ* method invocations( [https://github.com/jOOQ/jOOQ]
> ), which would be implemented as a {{GINQ provider}} in a seperate
> sub-project(e.g. {{groovy-linq-jooq}}). _Note: *JOOQ* is licensed under
> *APL2* too_( [https://github.com/jOOQ/jOOQ/blob/master/LICENSE] )
> h4. 3) target objects are XML, CSV, etc. related objects, or even mixed types
> of objects
> We can treate the case as a special sub-case of case 1
> h3. *Note:*
> {color:#d04437}1. The exact syntax might be altered before introduction,
> currently working on the general principle.{color}
> 2.GINQ will reuse most of standard SQL syntax, which can make the learning
> curve smooth and avoid infringing the patent of Microsoft.
> 3. All GINQ related keywords are uppercase to avoid breaking existing source
> code as possible as we can, e.g. {{FROM}}, {{WHERE}}, {{SELECT}}, etc.
> 4. In order to support type inference better, {{SELECT}} clause is placed at
> the end of GINQ expression.
> 5. {{alias.VALUE}} is a virtual property and is used to reference the whole
> record as value. It can be simplified as {{alias}}.
> 6. {{SELECT P1, P2 ... Pn}} will create a {{List}} of {{Tuple}} sub-class
> instances when and only when {{n >= 2}}
> h2. *Ⅲ. EBNF*
> h3. TBD
> h2. *Ⅳ. Examples*
> h3. 1. Filtering
> {code:java}
> @groovy.transform.EqualsAndHashCode
> class Person {
> String name
> int age
> }
> def persons = [new Person(name: 'Daniel', age: 35), new Person(name: 'Peter',
> age: 10), new Person(name: 'Alice', age: 22)]
> def result =
> FROM persons p
> WHERE p.age > 15 && p.age <= 35
> SELECT p.name
> assert ['Daniel', 'Alice'] == result
> result =
> FROM persons p
> WHERE p.age > 15 && p.age <= 35
> SELECT p
> assert [new Person(name: 'Daniel', age: 35), new Person(name: 'Alice', age:
> 22)] == result
> {code}
> {code:java}
> def numbers = [1, 2, 3]
> def result =
> FROM numbers t
> WHERE t <= 2
> SELECT t
> assert [1, 2] == result
> {code}
> h3. 2. Joining
> {code:java}
> import static groovy.lang.Tuple.*
> @groovy.transform.EqualsAndHashCode
> class Person {
> String name
> int age
> City city
> }
> @groovy.transform.EqualsAndHashCode
> class City {
> String name
> }
> def persons = [new Person(name: 'Daniel', age: 35, city: new
> City('Shanghai')), new Person(name: 'Peter', age: 10, city: new
> City('Beijing')), new Person(name: 'Alice', age: 22, city: new
> City('Hangzhou'))]
> def cities = [new City('Shanghai'), new City('Beijing'), new
> City('Guangzhou')]
> // inner join
> def result =
> FROM persons p INNER JOIN cities c
> ON p.city.name == c.name
> SELECT p.name
> assert ['Daniel', 'Peter'] == result
> result =
> FROM persons p, cities c
> WHERE p.city.name == c.name
> SELECT p.name
> assert ['Daniel', 'Peter'] == result
> result =
> FROM persons p, cities c
> WHERE p.city == c
> SELECT p.name
> assert ['Daniel', 'Peter'] == result
> // left outer join
> result =
> FROM persons p LEFT JOIN cities c // same to LEFT OUTER JOIN
> ON p.city.name == c.name
> SELECT p.name, c.name
> assert [tuple('Daniel', 'Shanghai'), tuple('Peter', 'Beijing'),
> tuple('Alice', null)] == result
> // right outer join
> result =
> FROM persons p RIGHT JOIN cities c // same to RIGHT OUTER JOIN
> ON p.city.name == c.name
> SELECT p.name, c.name
> assert [tuple('Daniel', 'Shanghai'), tuple('Peter', 'Beijing'), tuple(null,
> 'Guangzhou')] == result
> {code}
> h3. 3. Projection
> {code:java}
> import static groovy.lang.Tuple.*
> @groovy.transform.EqualsAndHashCode
> class Person {
> String name
> int age
> }
> def persons = [new Person(name: 'Daniel', age: 35), new Person(name: 'Peter',
> age: 10), new Person(name: 'Alice', age: 22)]
> def result =
> FROM persons p
> SELECT p.name
> assert ['Daniel', 'Peter', 'Alice'] == result
> result =
> FROM persons p
> SELECT p.name, p.age
> assert [tuple('Daniel', 35), tuple('Peter', 10), tuple('Alice', 22)] == result
> result =
> FROM persons p
> SELECT [name: p.name, age: p.age]
> assert [ [name: 'Daniel', age: 35], [name: 'Peter', age: 10], [name: 'Alice',
> age: 22] ] == result
> result =
> FROM persons p
> SELECT new Person(name: p.name, age: p.age)
> assert persons == result
> result =
> FROM persons p
> SELECT p.VALUE
> assert persons == result
> result =
> FROM persons p
> SELECT p
> assert persons == result
> {code}
> h3. 4. Grouping
> {code:java}
> import static groovy.lang.Tuple.*
> @groovy.transform.EqualsAndHashCode
> class Person {
> String name
> int age
> String gender
> }
> def persons = [new Person(name: 'Daniel', age: 35, gender: 'Male'), new
> Person(name: 'Peter', age: 10, gender: 'Male'), new Person(name: 'Alice',
> age: 22, gender: 'Female')]
> def result =
> FROM persons p
> GROUP BY p.gender
> SELECT p.gender, MAX(p.age)
> assert [tuple('Male', 35), tuple('Female', 22)] == result
> {code}
> h3. 5. Sorting
> {code:java}
> @groovy.transform.EqualsAndHashCode
> class Person {
> String name
> int age
> }
> def persons = [new Person(name: 'Daniel', age: 35), new Person(name: 'Peter',
> age: 10), new Person(name: 'Alice', age: 22)]
> def result =
> FROM persons p
> ORDER BY p.age
> SELECT p.name
> assert ['Peter', 'Alice', 'Daniel'] == result
> result =
> FROM persons p
> ORDER BY p.age desc
> SELECT p.name
> assert ['Daniel', 'Alice', 'Peter'] == result
> {code}
> h3. 6. Pagination
> {code:java}
> def numbers = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
> def result =
> FROM numbers n
> LIMIT 5 OFFSET 2
> SELECT n
> assert [2, 3, 4, 5, 6] == result
> result =
> FROM numbers n
> LIMIT 5
> SELECT n
> assert [0, 1, 2, 3, 4] == result
> {code}
> h3. 7. Nested Queries
> {code:java}
> def numbers = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
> def result =
> FROM (
> FROM numbers n
> WHERE n <= 5
> SELECT n
> ) v
> LIMIT 5 OFFSET 2
> SELECT v
> assert [2, 3, 4, 5] == result
> {code}
> h3. 8. WITH-Clause
> {code:java}
> def numbers = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
> def result =
> WITH v AS (
> FROM numbers n
> WHERE n <= 5
> SELECT n
> )
> FROM v
> LIMIT 5 OFFSET 2
> SELECT v
> assert [2, 3, 4, 5] == result
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
