Query Language Examples

preql.txt

# Literals, such as booleans, integers, floats, and character strings are actually vectors of length 1.
# booleans:
true
# or
false

# integers (can have arbitrary _ as separators):
1, 45, 1_000_000

# floats
2.34, 3.14, -6.23e-32

# Integer literals default to i32
# Float literals default to f64
# Using the i8, i16, i32, i64, and i128 suffixes switches to 8-bit, 16-bit, 32-bit, 64-bit, and 128-bit
# There are also unsigned variants: u8, u16, u32, u64, and u128
# Using the f32, and f64 suffixes switches to 32-bit and 64-bit
# The "as" keyword converts a vector of one type to another:
42i32 as i64

# Character strings surrounded by double quotes (`"`):
"Hello, world!"

# Characters can be any valid UTF-8 unicode character sequence
# The r prefix can be used create multiline strings. The string ends when the specified number of
# hash symbols (#) appear after a quote.
r#"Hello,

    World
!!!
"#

# Character strings prefixed with an f are interpolated
let name = "World"
f"Hello, ${name}"

# The r and f prefixes can be combined

# Two vectors can be concatenated using ++, returning a new vector
1 ++ 2

# Two character strings can be concatenated using +
"Hello" + "World"

# Thus, "Hello" ++ "World" creates a vector or length 2, containing two strings
# and "Hello" + "World" creates a string containing "HelloWorld" 

# Tuples are comparable/orderable, fixed-length types where each member can be a different type
("Hello", 123)

# Tuples are compared element-wise.
("a", 123) == ("a", 123)
("a", 123) < ("b", 123)
("a", 123) < ("a", 234)

# Tuples can only be compared to other tuples with the same number and type of elements

# Entities are unordered key/value pairs, where each member can be a different type
# Key names must be unique within entities.
{
  a: 123,
  b: "Hello",
  c: false
}

# Entity types can be declared as interfaces
type Thing = {
  a: i32,
  b: string,
  c: bool
}

# A data source represents a source of entities
# It can be used to retrieve entity metadata
# and for querying records.
# The simplest data source is a vector

# select - get all applications (Applications is a data source)
# type Application = {
#     Name: string,
#     InstallDate: LocalDateTime,
#     Publisher: string
# }
from Application as a
select a

# The distinct keyword following select removes duplicates from the results.

# distinct - get unique applications
from Application as a
select distinct a

# distinct columns - get unique installation dates
from Application as a
select distinct a.InstallDate

# distinct columns - get applications with distinct dates (duplicates discarded)
from Application as a
select unique a by a.InstallDate

# Specific fields can be returned 
# grab all fields from the source but only select some of them
from Application as a
select { a.Name, a.Publisher, a.InstallDate }

# Give subset of fields an alias -- treat it like an entity
# b could then be filtered, mapped, etc.
from Application as a
select { a.Name, a.Publisher, a.InstallDate } as b
select b

# grab all fields from the source but only select some of them with aliases
# Field names can be overwritten using a colon (e.g., { x: y, a: b }).
from Application as a
select {
    AppName: a.Name, 
    PublisherName: a.Publisher, 
    InstallDate: a.InstallDate 
}

# We can filter out records in an entity set using 'where'.
# The 'where' keyword can appear multiple times, anywhere in the query.
# When two wheres appear next to each other, it's the same as '-and'

# Some operators are prefixed by - and can be platform-specific:
# Comparisons: =, <>, !=, <, <=, >, >=, -eq, -ne, -lt, -le, -gt, -ge, -ieq, -ine, -contains, -icontains, -match, -imatch
# Boolean: &&, ||, !, not, and, or, -not, -and, -or
# Arithmetic: +, -, *, /, //, **, %
# Bitwise: <<, >>, >>>, ~, &, |

# select with filter - get applications published by Microsoft
from Application as a
where a.Publisher == "microsoft"
select a

# The next two queries are equivalent
from Application as a
where a.Publisher == "microsoft" and a.InstallDate > Now()
select a

from Application as a
where a.Publisher == "microsoft"
where a.InstallDate > Now()
select a

from Application as a
where a.Publisher == "microsoft" or a.Name == "Adobe Acrobat Reader"
select a

# Entities can be grouped together by specifying the fields to group.

# select with "group by" and "having"
# get the number of applications per publisher
# where there are more than 10 applications per publisher
# We must explicitly name the Count property since the name cannot be derived
from Application as a
group a by a.Publisher as pg
let pgCount = count(pg)
where pgCount >= 10
select { pg.Key.Publisher, Count: pgCount }

# compound group by
# get the number of applications installed on a given day per publisher
# where there are more than 10 applications published per day per publisher
from Application as a
group a by (a.Publisher, a.InstallDate) as pg
where count(pg) >= 10
select { pg.Publisher, pg.InstallDate, Count: count(pg) }

# select with order by
from Application as a
order by a.Name
select a

# compound order by
from Application as a
order by a.Publisher, a.Name, a.InstallDate desc
select a

# paging - order by required
from Application as a
order by a.Name
select a skip 20 take 10

# value/insert
insert into Application { Name: "Microsoft Word", Publisher: "Microsoft", InstallDate: Today() }

# select/insert
from ApplicationSource as a
insert into Application { Name: a.Name, Publisher: a.Publisher, InstallDate: a.InstallDate }

# insert/select - returns inserted record with all updated values (primary keys, etc.)
insert into Application { Name: "Microsoft Word", Publisher: "Microsoft", InstallDate: Now() } as a
select a

# select/update
from Application as a
let now = Now()
where a.InstallDate > now
update a { InstallDate: now }

# update/select - returns updated record with all updated values (defaults, etc.)
from Application as a
let now = Now()
where a.InstallDate > now
update a { InstallDate: now }
select a

# update based on current record
from Application as a
where a.Name == "Microsoft Word"
update a { RunCount: a.RunCount + 1 }

# select/delete
from Application as a
where a.Publisher == "microsoft"
delete a

## concatenation
let msApps = from Application as a
             where a.Publisher == "microsoft"
             select a
let nsApps = from Application as b
             where b.Publisher == "nullsoft"
             select b
from msApps ++ nsApps as c
select c

# set union
let msApps = from Application as a
             where a.Publisher == "microsoft"
             select a
let nsApps = from Application as b
             where b.Publisher == "nullsoft"
             select b
from msApps union nsApps as c
select c

# set difference (except or diff?)
let msApps = from Application as a
             where a.Publisher == "microsoft"
             select a
let nsApps = from Application as b
             where b.Publisher == "nullsoft"
             select b
from msApps except nsApps as c
select c

# set symmetric difference
let msApps = from Application as a
             where a.Publisher == "microsoft"
             select a
let recentApps = from Application as b
                 where b.InstallDate >= Today()
                 select b
from (msApps except recentApps) union (recentApps except msApps) as c
select c

# set intersect
let msApps = from Application as a
             where a.Publisher == "microsoft"
             select a
let nsApps = from Application as b
             where b.Publisher == "nullsoft"
             select b
from msApps intersect nsApps as c
select c

# joins
# For left, right and full joins, the entity on the right, left or both can
# be null. After the join condition, the query must explicitly check for null
# when comparing to non-nullable columns. This check will not be necessary if
# the column being compared to is already nullable.

# inner join (applications with their dependencies)
from Application as a
where a.Publisher = "microsoft"
join ApplicationDependency as ad on a.ApplicationId == ad.ApplicationId
join Dependency as d on ad.DependencyId == d.DependencyId
select { AppName: a.Name, DepName: d.Name }

# left join (applications, regardless of whether they have dependencies) (WIP)
from Application as a
where a.Publisher = "microsoft"
left join ApplicationDependency as ad on a.ApplicationId == ad.ApplicationId
left join Dependency as d on ad.DependencyId == d.DependencyId
select { AppName: a.Name, DepName: d.Name }

# Compound join condition
from Customer as c
left join EntityNote as en on (c.CustomerId, "Customer") == (en.EntityId, en.EntityType)
select { CustomerId: c.CustomerId, Note: en.Content }

# right join (dependencies, regardless of whether they have applications [joins reversed]) (WIP)
from Application as a
where a.Publisher == "microsoft"
right join ApplicationDependency as ad on d.DependencyId == ad.DependencyId
right join Dependency as d on ad.ApplicationId == a.ApplicationId
select { AppName: a.Name, DepName: d.Name }

# full outer join (applications and dependencies, regardless of relationship) (WIP)
from Application as a
where a.Publisher == "microsoft"
full join ApplicationDependency as ad on d.DependencyId == ad.DependencyId
full join Dependency as d on ad.ApplicationId == a.ApplicationId
select { AppName: a.Name, DepName: d.Name }

# Recursive queries (get employee and his managers) (WIP)
let getEmployee = (id) =>
(
    from Employee as e
    where e.EmployeeId == id
    left join getEmployee(e.ManagerId) as m on e.ManagerId == m.EmployeeId
    let level = m?.Level + 1 # Null coalescing arithmetic operation
    select
    { 
        e.EmployeeId,
        e.Name,
        Level: level,
        ManagerId: m?.EmployeeId,
        ManagerName: m?.Name
    }
)
from getEmployee(123) as e
order by e.Level
select { e.EmployeeId, e.Name, e.ManagerId, e.ManagerName }

# range
from 1 ++ 2 ++ 3 as v
where (v % 2) == 0
select v

from 1..10 as v
where (v % 2) == 0
select v

from 2..10:2 as v
select v
                                                 
# reduce
from 1..10 as v
reduce v from 0 with (a, b) -> a + b

# any
from Application as a
where a.Publisher == "microsoft"
reduce some(a)

# count
from Application as a
where a.Publisher == "microsoft"
reduce count(a)

# distinct
from Application as a
where a.Publisher == "microsoft"
select distinct a.Name

# first
from Application as a
where a.Publisher == "microsoft"
reduce first(a)

from Application as a
where a.Publisher == "microsoft"
select a.Name as n
reduce first(n, "")

# last
from Application as a
where a.Publisher == "microsoft"
last a

from Application as a
where a.Publisher == "microsoft"
select a.Name as n
reduce last(n, "")

# skip
from Application as a
where a.Publisher == "microsoft"
select a skip 10

# take
from Application as a
where a.Publisher == "microsoft"
select a take 10

# skip and take
from Application as a
where a.Publisher == "microsoft"
select a skip 10 take 5