[GROOVY-9159] [GEP] Support LINQ, aka GINQ

• Type: New Feature
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.0.0-beta-1
• Labels: features
• Assignee: Daniel Sun
• Reporter: Daniel Sun
• Created: 05/Jun/19 23:59
• Updated: 13/Jul/22 19:13
• Resolved: 30/Sep/21 01:05

Description

Ⅰ. Background

In order to make querying different types of data sources convenient, we need a unified querying interface, i.e. GINQ

Ⅱ. Solution

Basic rationale

Groovy User ==writes GINQ code==> Parrot Parser ==generates AST==> GINQ Transformation ==transforms AST to Queryable method invocations==> Bytecode Writer

transforms AST to Queryable method invocations will be designed for different cases

1. target objects are all collections
   Queryable method invocations are implemented by Java 8+ stream method invocations
2. target objects are all DB related objects
   Queryable method invocations are implemented by JOOQ method invocations( https://github.com/jOOQ/jOOQ ) 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 )
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

Interfaces & implementations

• Queryable
  https://github.com/danielsun1106/groovy-linq/blob/master/src/main/java/groovy/linq/Queryable.java
• QueryableCollection
  https://github.com/danielsun1106/groovy-linq/blob/master/src/main/java/groovy/linq/QueryableCollection.java

Note:

1. The exact syntax might be altered before introduction, currently working on the general principle.
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 real keywords, e.g. from, where, select, which is the approach applied by C#. In order to avoid breaking existing source code as possible as we can, we should add the new keywords to identifiers.
4. In order to support type inference better, select clause is placed at the end of GINQ expression.
5. select P1, P2 ... Pn is a simplifed syntax of select Tuple.tuple(P1, P2 ... Pn) and will create a List of Tuple sub-class instances when and only when n >= 2

Ⅲ. EBNF

  TBD

Ⅳ. Examples

1. Filtering

@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)]

1.1

def result =
	from persons p
	where p.age > 15 && p.age <= 35
	select p.name

assert ['Daniel', 'Alice'] == result

// interface
from(persons).where(p -> p.age > 15 && p.age <= 35).select(p -> p.name).toList()

// collection implementation
persons.stream().filter(p -> p.age > 15 && p.age <= 35).map(p -> p.name).collect(Collectors.toList())

1.2

def 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

// interface
from(persons).where(p -> p.age > 15 && p.age <= 35).select(p -> p).toList()

// collection implementation
persons.stream().filter(p -> p.age > 15 && p.age <= 35).collect(Collectors.toList())

1.3

def numbers = [1, 2, 3]

def result =
	from numbers t
	where t <= 2
	select t

assert [1, 2] == result

// interface
from(numbers).where(t -> t <= 2).select(t -> t).toList()

// collection implementation
numbers.stream().filter(t -> t <= 2).collect(Collectors.toList())

2. Joining

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(name: 'Shanghai')), new Person(name: 'Peter', age: 10, city: new City(name: 'Beijing')), new Person(name: 'Alice', age: 22, city: new City(name: 'Hangzhou'))]

def cities = [new City(name: 'Shanghai'), new City(name: 'Beijing'), new City(name: 'Guangzhou')]

2.1

// inner join
def result =
	from persons p 
	innerjoin cities c on p.city.name == c.name
	select p.name, c.name

assert [tuple('Daniel', 'Shanghai'), tuple('Peter', 'Beijing')] == result

// interface
from(persons).innerJoin(from(cities), (p, c) -> p.city.name == c.name).select((p, c) -> tuple(p.name, c.name)).toList()

// collection implementation
persons.stream()
	.flatMap(p -> cities.stream().filter(c -> p.city.name == c.name).map(c -> tuple(p.name, c.name)))
	.collect(Collectors.toList())

2.2

def result =
	from persons p
	innerjoin cities c on p.city == c
	select p.name

assert ['Daniel', 'Peter'] == result

// interface
from(persons).innerJoin(from(cities), (p, c) -> p.city == c).select((p, c) -> p.name).toList()

// collection implementation
persons.stream()
	.flatMap(p -> cities.stream().filter(c -> p.city == c).map(c -> p.name))
	.collect(Collectors.toList())

2.3

// left outer join
def result =
	from persons p 
	leftjoin cities c on p.city.name == c.name //  same with left outer join
	select p.name, c.name

assert [tuple('Daniel', 'Shanghai'), tuple('Peter', 'Beijing'), tuple('Alice', null)] == result

// interface
from(persons).leftjoin(from(cities), (p, c) -> p.city.name == c.name).select((p, c) -> tuple(p.name, c.name)).toList()

// collection implementation
persons.stream()
    .flatMap(p -> 
        cities.stream()
            .map(c -> p.city.name == c.name ? c : GinqConstant.NULL)
            .reduce(new LinkedList(), (r, e) -> {
                int size = r.size()
                if (0 == size) {
                    r.add(e)
                    return r
                }
                
                int lastIndex = size - 1
                Object lastElement = r.get(lastIndex)
                
                if (GinqConstant.NULL != e) {
                    if (GinqConstant.NULL == lastElement) {
                        r.set(lastIndex, e)
                    } else {
                        r.add(e)
                    }
                }
                
                return r
            }).stream()
                .map(c -> Tuple.tuple(p.name, GinqConstant.NULL == c ? GinqConstant.NULL : c.name)))
                .collect(Collectors.toList())

2.4

// right outer join
def result =
	from persons p 
	rightjoin cities c on p.city.name == c.name //  same with right outer join
	select p.name, c.name

assert [tuple('Daniel', 'Shanghai'), tuple('Peter', 'Beijing'), tuple(null, 'Guangzhou')] == result

3. Projection

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)]

3.1

def result =
	from persons p
	select p.name

assert ['Daniel', 'Peter', 'Alice'] == result

3.2

def result =
	from persons p
	select p.name, p.age

assert [tuple('Daniel', 35), tuple('Peter', 10), tuple('Alice', 22)] == result

3.3

def 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

3.4

def result =
	from persons p
	select new Person(name: p.name, age: p.age)

assert persons == result

3.5

def result =
	from persons p
	select p

assert persons == result

4. Grouping

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')]

4.1

def result =
	from persons p
	groupby p.gender
	select p.gender, max(p.age)

assert [tuple('Male', 35), tuple('Female', 22)] == result

5. Having

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')]

5.1

def result =
	from persons p
	groupby p.gender
	having p.gender == 'Male'
	select p.gender, max(p.age)

assert [tuple('Male', 35)] == result

6. Sorting

@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)]

6.1

def result =
	from persons p
	orderby p.age
	select p.name

assert ['Peter', 'Alice', 'Daniel'] == result

6.2

def result =
	from persons p
	orderby p.age desc
	select p.name

assert ['Daniel', 'Alice', 'Peter'] == result

7. Pagination

def numbers = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]

7.1

def result =
	from numbers n
	limit 2, 5
	select n

assert [2, 3, 4, 5, 6] == result

7.2

def result =
	from numbers n
	limit 5
	select n

assert [0, 1, 2, 3, 4] == result

8. Nested Queries

def numbers = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]

8.1

def result =
	from (
		from numbers n
		where n <= 5
		select n
	) v
	limit 2, 5
	select v

assert [2, 3, 4, 5] == result

9. WITH-Clause

def numbers = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]

9.1

def result =
	with v as (
		from numbers n
		where n <= 5
		select n
	)
	from v
	limit 2, 5
	select v

assert [2, 3, 4, 5] == result

10. Union

def numbers1 = [0, 1, 2]
def numbers2 = [2, 3, 4]

10.1

def result =
	from numbers1 n
	select n
	unionall
	from numbers2 n
	select n

assert [0, 1, 2, 2, 3, 4] == result

10.2

def result =
	from numbers1 n
	select n
	union
	from numbers2 n
	select n
	
assert [0, 1, 2, 3, 4] == result

Issue Links

• relates to: GROOVY-8258 [GEP] Create a LINQ-like DSL (basic cases)