7 Preview of Plisqin 0.2

>   (require (for-label plisqin/scribblings/racket                     plisqin/private/lang/default-require))

>   (void select + syntax->datum)

>   (current-connection 'cities-example)

>   {if 41.add1 = 21 * 1.add1     "as it should"     "what is wrong?"}

"as it should"

7.1 Setup and Schema

>   (def-table Country)

>   (def-table City)

>   (def-table Organization)

>   (def-table CountryOrganization)

>   (def-fields-of Country   CountryId   CountryName   CountryPopulation)

>   (def-fields-of City   CityId   CountryId   CityName   CityPopulation)

>   (def-fields-of Organization   OrgId   OrgShortName)

>   (def-fields-of CountryOrganization   CountryId   OrgId)

7.2 Query Fundamentals

The above query has 4 clauses: an order-by clause, a limit clause, and 2 select clauses.

Queries are appendable. This means you can add more clauses to a query you already defined elsewhere. For example, let’s start with big-cities and append a where clause that excludes cities with a population over 20 million:

It’s important to understand that the above query has 5 clauses in total: the where clause plus the 4 clauses from big-cities. You might be able to foresee that appendable queries can greatly reduce code duplication.

7.3 Joins

Every City has exactly one Country. Relationships between tables are represented using join. The following query appends to big-cities, joins the Country table, and adds a select clause.

>   (define (explicit-join-example)   {from city (big-cities)         {join country Country               {join-on country.CountryId = city.CountryId}}         {select country.CountryName}})

The above query would work just fine, but joining the Country of a City is something we will be doing often. So let’s make it reusable:

>   (def/append! (Country x)  ; the Country of x ...   [(City? x)              ; if x is a City ...    ; ... is this join:    {join country Country          {join-on country.CountryId = x.CountryId}}])

That’s pretty nice, but we can do even better. Do we really want to say {city.Country.CountryName} every time? Can we just say {city.CountryName} instead? Yes, by expanding the definition of CountryName:

>   (def/append! (CountryName x)  ; the CountryName of x ...   [(City? x)                  ; if x is a City ...    ; ... is this expression:    {x.Country.CountryName}])

7.4 Timeout for more Schema

A Country can have multiple Organizations. For example, Belgium is a member of both the EU and NATO. Obviously, an Organization can have multiple Countries. This many-to-many relationship requires a mapping table, CountryOrganization.

>   (def/append! (CountryOrganizations x)   [(Country? x)    {join co CountryOrganization          {join-on co.CountryId = x.CountryId}}]   [(Organization? x)    {join co CountryOrganization          {join-on co.OrgId = x.OrgId}}])

>   (def/append! (Organizations x)   [(Country? x)    {join org Organization          {join-on org.OrgId = x.CountryOrganizations.OrgId}}])

>   (def/append! (Countries x)   [(Organization? x)    {join country Country          {join-on country.CountryId = x.CountryOrganizations.CountryId}}])

7.5 Grouped Joins and Aggregates

Consider this code:

I call CitiesG a "grouped join." An important quality of a grouped join is that it does not increase the cardinality of the result set with respect to its argument(s). In the above example, the argument x is tested to be a Country. The join is grouped by CountryId. Therefore, there will be at most 1 group per Country.

Grouped joins are used with aggregate functions. In the following example, count and sum are the aggregates. Notice that CitiesG always occurs inside an aggregate:

OK, so we are seeing aggregated City information by Country. Could we not also see aggregated City information by Organization? Yes, because just like a Country, an Organization also has a group of Cities. So first, let’s extend the definition of CitiesG:

>   (def/append! (CitiesG x)   [(Organization? x)    {join ct City          {group-by ct.Country.Organizations.OrgId}          {join-on ct.Country.Organizations.OrgId = x.OrgId}}])

The above join is a valid grouped join because it will return at most one group of Cities per Organization. (Note that a single City might belong to more than one Organization, but that is fine.)

Now city-stats should work on any X for which CitiesG is defined! Which makes it easy to implement the "by Country" and "by Organization" variants:

>   (define (city-stats-by-country)   {from co (city-stats Country)         {select co.CountryName}         {select co.CountryId}})

>   (define (city-stats-by-org)   {from org (city-stats Organization)         {select org.OrgShortName}})

TODO guide what to read next.