Squiggle Two(ish) Minute Tutorial

This short tutorial will take you through the basics of using Squiggle.

Creating a basic SELECT query

The core of Squiggle revolves around the SelectQuery class. When creating a new SELECT statement, you instantiate a new SelectQuery and add query attributes to it.

Instances of Table are used to represent each table participating in a query. Table takes a name as a parameter to the constructor, which is the name of the table in the database.

When instantiating a SelectQuery, a Table must be passed to the constructor that represents the table that this statement will be SELECTing from. (Note: it is possible to SELECT from multiple tables using joins or sub-selects, as we shall see later).

Table orders = new Table("orders_table");
SelectQuery select = new SelectQuery(orders);

Now columns can be added to the query using SelectQuery.addColumn(). This takes two parameters; the Table containing the column and the name of the column in the database.

select.addColumn(orders, "id");
select.addColumn(orders, "total_price");

The generated SQL can be obtained using SelectQuery.toString().

System.out.println(select.toString());

Which outputs:

SELECT
   orders_table.id ,
   orders_table.total_price
FROM
    orders_table

Adding WHERE criteria

The SelectQuery.addCriteria() method can be used to add criteria to the WHERE clause of a query, which takes a Criteria object as a parameter.

Criteria itself is an abstract class as there are many different types of criteria that can be appended to a WHERE clause.

MatchCriteria is used for specifying a simple match such as MY_COL = 'foo' or C <= 6.

select.addCriteria(new MatchCriteria(orders, "status", MatchCriteria.EQUALS, "processed"));
select.addCriteria(new MatchCriteria(orders, "items", MatchCriteria.LESS, 5));

The parameters for MatchCriteria constructor are:

• Table table: The table containing the column used in the match.
• String columnname: The database column name in the table.
• String matchType: The operator to use. MatchCriteria provides some constants for these.
• String/int/float/boolean value: The value to match against. There are multiple overloadded constructors for different types.

The query now looks like:

SELECT
   orders_table.id ,
   orders_table.total_price
FROM
    orders_table
WHERE
    orders_table.status = 'processed' AND
    orders_table.items < 5

Another type of Criteria is the InCriteria, which is used for specifiying MY_COL IN ('a','b','c',....) criteria. The values can be specified using an array or a collection.

select.addCriteria(new InCriteria(orders, "delivery", new String[] { "post", "fedex", "goat" } ));

Which produces:

SELECT
    orders_table.id ,
    orders_table.total_price
FROM
    orders_table
WHERE
    orders_table.status = 'processed' AND
    orders_table.items < 5 AND
    orders_table.delivery IN (
        'post','fedex','goat'
    )

Joining tables

To add a new table to the query using a join, a new Table must be instantiated and then passed to the SelectQuery.addJoin() method.

The parameters for the SelectQuery.addJoin() are:

• Table sourceTable
• String sourceColumn
• Table destTable
• String destColumn

So, this:

Table warehouses = new Table("warehouses_table");
select.addJoin(orders, "warehouse_id", warehouses, "id");

Modifies the SELECT statement to insert the SQL necessary to include the join:

SELECT
    orders_table.id ,
    orders_table.total_price
FROM
    orders_table ,
    warehouses_table
WHERE
    orders_table.status = 'processed' AND
    orders_table.items < 5 AND
    orders_table.delivery IN (
        'post','fedex','goat'
    ) AND
    orders_table.warehouse_id = warehouses_table.id

Of course, joining the table is pretty useless on its own, so you can also use the table in the query in the same way you could use the first table.

select.addColumn(warehouses, "location");
select.addCriteria(new MatchCriteria(warehouses, "size", MatchCriteria.EQUALS, "big"));

Which adds a new column to the selection and a new match criteria:

SELECT
    orders_table.id ,
    orders_table.total_price ,
    warehouses_table.location
FROM
    orders_table ,
    warehouses_table
WHERE
    orders_table.status = 'processed' AND
    orders_table.items < 5 AND
    orders_table.delivery IN (
        'post','fedex','goat'
    ) AND
    orders_table.warehouse_id = warehouses_table.id AND
    warehouses_table.size = 'big'

Performing sub-selects

To perform a sub-select (query within query), you build a seperate query using a new instance of SelectQuery, and then add it to the first using an InCriteria.

So, here's a new select statement:

Table offers = new Table("offers_table");
SelectQuery subSelect = new SelectQuery(offers);
subSelect.addColumn(offers, "location");
subSelect.addCriteria(new MatchCriteria(offers, "valid", MatchCriteria.EQUALS, true));

Which, on its own, produces:

SELECT
    offers_table.location
FROM
    offers_table
WHERE
    offers_table.valid = true

This can then be used as a sub-select in the original query by adding the new SelectQuery as a parameter to InCriteria:

select.addCriteria(new InCriteria(warehouses, "location", subSelect));

Which leaves us with:

SELECT
    orders_table.id ,
    orders_table.total_price ,
    warehouses_table.location
FROM
    orders_table ,
    warehouses_table
WHERE
    orders_table.status = 'processed' AND
    orders_table.items < 5 AND
    orders_table.delivery IN (
        'post','fedex','goat'
    ) AND
    orders_table.warehouse_id = warehouses_table.id AND
    warehouses_table.size = 'big' AND
    warehouses_table.location IN (
        SELECT
            offers_table.location
        FROM
            offers_table
        WHERE
            offers_table.valid = true

    )

Phew!

Next steps

This concludes the two minute tutorial. Maybe it took a bit longer, but hopefully not that much.

You should have a solid idea of how to use Squiggle now, including:

• Building SELECT statements.
• Adding WHERE criteria.
• Joining tables.
• Performing sub-selects.

From here, the best way to learn more about Squiggle is by exploring the API, looking at the source code and experimenting.