GmailSearchBuilder - A Google Apps Script Library

Preface

I recently wanted to write a simple Google Apps Script for automatically delete old email messages (threads). The reason was a lot of email threads that started to accumulate, which are not important enough for me to keep.

There are solutions out there. All those I had seen are based on assigning a label to the messages to be deleted (e.g. "DELETE ME"). Some are quite naive - iterating over all the threads returned from the GmailLabel object, which is not the best way to go, the performance of this solution is poor. The better solutions use GmailApp.search method with a query such as "label:delete-me older_than:30d" to fetch the threads with the "DELETE ME" label which are older than 30 days. (Good post: Create time-based Gmail filters with Google Apps Script)

Also, you need to assign the label for that to work. Manual assignment is tedious. Of course it can be solved by filters which assign this label automatically. But what if you want to assign other labels, and the "DELETE ME" label is only the secondary label to assign? (e.g. when you filter messages into different labels, but all of them can be deleted after a while). This can also be done by a scheduled job which assigns the "DELETE ME" label to all messages with other specified labels (which are not yet marked for deletion). This can be done similarly using the search query "label:my-other-label -label:delete-me".

After writing those queries, and trying to figure out how to deal with some issues, like what to do with spaces in the label name (one solution- replace all spaces with '-'), I decided to implement a service for building search queries. The goal of this service is to solve for you those questions (another one for example: how to format dates), while letting you all the flexibility of the query syntax.

The Query Builder

Well, it's quite simple. The idea was to have a fluent API using method chaining. The builder needs to give flexibility similar to writing hard-coded query strings, with simplicity and to handle for the developer the tedious technical stuff. Since it's a builder, it can be used for building queries dynamically in run-time. The builder also lets you call the search directly on the builder itself as a syntactic sugar.

Some examples
Assume:

var GSB = GmailSearchBuilder;

1- Building and running a simple query

var query = GSB.newQuery().label("My Other Label").exclude().label("DELETE ME").build();
var threads = GmailApp.search(query);
//The query: label:my-other-label -label:delete-me

2- Building and running directly from the builder

var threads = GSB.newQuery().label("DELETE ME").olderThan(30,DateType.DAY).search();
//The query: label:delete-me older_than:30d

3- Building a query with a sub-query (group)

var threads = GSB.newQuery().subQuery(GSB.newQuery().label("My Label 1").or().label("My Label 2")).exclude().label("DELETE ME").search();
//The query: (label:my-label-1 OR label:my-label-2) -label:delete-me

3- Search for threads between dates

var threads = GSB.newQuery().from("john@acme.com").before(new Date(2014,11,31)).after(new Date(2014,0,1)).larger(1,SizeType.MB).search();
//The query: from:john@acme.com before:2014/12/31 after:2014/01/01 larger:1048576

The source code is available in GitHub:
https://github.com/yinonavraham/GoogleAppsScripts/tree/master/GmailSearchBuilder

"SQL like" Java Collections

In my daily work I do a lot of Java coding, but I also use SQL quite a lot.
I like SQL for its declarative nature. I think it is quite intuitive to spell out queries. In .NET there is LINQ which gives nice query capabilities. So I thought why not have similar API in Java - for querying collections. I tried to look for some existing libraries and found some. The closest solution I found was JoSQL. As stated in its website:

"JoSQL (SQL for Java Objects) provides the ability for a developer to apply a SQL statement to a collection of Java Objects. JoSQL provides the ability to search, order and group ANY Java objects and should be applied when you want to perform SQL-like queries on a collection of Java Objects."

JoSQL uses a Query object which gets a string with the SQL query. When it is called, the query is parsed and executed. I guess, but did not check, that reflection is used extensively there. It looks quite nice, but the main issue that I find with it is that it is error prone. The query validity is not enforced in design-time. If you have an error you will know it only in runtime, and there it is a matter of how much the error message is useful, and hopefully you caught it during development/QA.

With this issue I have decided to implement a different API which will give some query capabilities over collections and its validity will be evaluated in design-time. The main characteristics I chose for my API were:

• Generics
• Fluent API (i.e. method chaining)
• Functional Programming

The library it-self is not complete, I did it only as a POC for myself. It is hosted on Google Code:
https://code.google.com/p/ynn-util-collections/wiki/SQLlikeCollections
Let's see some examples.

First, let's assume we have the following import statements:

import static ynn.util.collections.Predicates.*;
import static ynn.util.collections.SqlLikeCollections.*;

The first import statement imports some predefined static predicate methods such as "and", "or", "not", "lessThan", etc. The second import statement imports the entry point static methods for our queries.
Now that we have our basics imported we can write queries.

Examples

Example 1 -

Select all strings in a collection which are not null:

Collection<String> data = Arrays.asList(
    null, "A", "B", "C", null, "D", "E", "F", "G", "H", "I", null);
Collection<String> result = selectFrom(data).where(not(isNull()));

As you can see, this is quite a simple query. You can also notice that the result is typed and keeps the same type as the source (i.e. String). The syntax is quite similar to SQL, which would probably be something like:

SELECT * FROM "DATA" WHERE NOT "DATA"."VALUE" IS NULL

Let's examine some other more complex examples.

Example 2 -

In this example we will have a slightly more complex where clause and we'll also add some sorting. The API has some predefined predicates and utilities for Strings, so we'll import them as well:

import static ynn.util.collections.Predicates.StringPredicates.*;

Now for the example:

Collection<String> result = selectFrom(data)
    .where
        and(
            not(isNull()),
            stringStartsWith("A"),
            stringContains("a")
        )
    )
    .orderBy(LEXICOGRAPHIC_ORDER);

I this example you can see we select all strings which are not null, and it starts with "A", and it contains "a". The result is ordered by lexicographic order. Quite readable, right?

Of course the API is not limited to Strings or just simple objects. Let's see a more complex example.

Example 3 -

In this example we first need to define our Person class:

public class Person {

    private String name;
    private int age;
    private double weight;

    public Person() { }

    public Person(String name, int age, double weight) { 
        this.name = name;
        this.age = age;
        this.weight = weight;
    }

    // Getter & setter methods go here, I omitted them for simplicity
    // Also any other specific implementation (toString, ...)

    /*
     * VALUE PROVIDERS
     */

    public static ElementValueProvider<Person, String> name() {
        return new ElementValueProvider<Person, String>() {
            @Override
            public String getValue(Person element) {
                return element.getName();
            }
        };
    }
        
    public static ElementValueProvider<Person, Integer> age() {
        return new ElementValueProvider<Person, Integer>() {
            @Override
            public Integer getValue(Person element) {
                return element.getAge();
            }
        };
    }
	
}

You probably noticed the static methods under the "VALUE PROVIDERS" comment. Their purpose will be clearer in the example:

List<Person> data = Arrays.asList(
    new Person("John", 2, 2.2),
    new Person("David", 1, 1.1),
    new Person("Bob", 4, 4.4),
    new Person(null, 2, 7.7),
    new Person("Robin", 1, 3.3),
    new Person("Danny", 2, 5.5),
    new Person("Alice", 3, 6.6)
);

Collection<Person> result = 
    selectFrom(data)
    .where(
        not(isNull(Person.name())))
    .orderBy(
        desc(Person.age()), 
        asc(Person.name()));

As you can see above, we wrote a query for selecting all persons whom their name is not NULL. We then sorted the collection, first descending by age, then ascending by name. The corresponding SQL query would probably look something like:

SELECT * FROM "DATA"
WHERE NOT "PERSON"."NAME" IS NULL
ORDER BY "PERSON"."AGE" DESC, "PERSON"."NAME" ASC

Quite similar, right? And it's type safe as well!

One last example - I also started to play with the idea to support mass updates. See the example below.

Example 4 -

In this example we will update all persons with NULL name to have empty string for a name and their age to zero. For the example we need to add the following methods to our Person class:

    /*
     * UPDATERS
     */

    public static Updater<Person> nameTo(final String newName) {
        return new Updater<Person>() {
            @Override
            public void update(Person element) {
                element.setName(newName);
            }
        };
    }

    public static Updater<Person> ageTo(final int newAge) {
        return new Updater<Person>() {
            @Override
            public void update(Person element) {
                element.setAge(newAge);
            }
        };
    }

Now we can write our mass update statement:

update(data)
    .where(
        isNull(Person.name()))
    .set(
        Person.nameTo(""), 
        Person.ageTo(0));

See? nice, right? 

Conclusion

Of course there is a lot to extend, many capabilities missing, the performance of the current implementation can be easily improved, etc. This was a proof of concept. I think having such API can make developers' life easier, especially if they also write real SQL...

By-the-way - now that Java 1.8 is out, with all its new capabilities such as lambda expressions, method reference, functional interfaces, aggregate methods, streams, etc. It gives exactly what I was trying to accomplish here. The main difference is the syntax, but the usage is very similar.