# Query and Routine Language Specification

## 1. Scope

This document specifies the source-language syntax of a SQL-inspired relational and procedural database language.

The language combines:

- SQL-like declarative querying
- Lua-like procedural control flow
- first-class record identities and references
- structured and flexible records
- graph relations
- transactional routines
- subscriptions
- durable changefeeds
- application events
- policies
- specialized indexing

This document is concerned primarily with language surface and grammar. Runtime behavior and semantic requirements are defined separately.

## 2. Source Form

Source text shall be UTF-8.

Keywords are case-insensitive. Lowercase is canonical and should be emitted by formatters and tooling.

Identifiers are case-insensitive and case-preserving.

Strings use single quotes.

Quoted identifiers use double quotes.

```sql
select "display name"
from player
where name = 'sky'
```

Line comments begin with `--`.

Block comments use `/* ... */` and may nest.

## 3. Core Literals

The language defines:

```sql
true
false
unknown
```

`null` is not part of the language.

Numeric literals may be decimal integers or decimal floating-point values.

String literals use single quotes.

## 4. Core Types

The standard scalar types are:

```text
bool

int8
int16
int32
int64

uint8
uint16
uint32
uint64

decimal
float32
float64

text
varchar
bytes

date
time
timestamp
instant
duration

uuid
record
vector
point
line
polygon
```

Parameterized forms include:

```sql
decimal(12, 2)
varchar(64)
record<player>
vector<float32, 768>
```

Arrays use:

```sql
[text]
[int64]
[record<item>]
```

Structured objects use field blocks:

```sql
position {
	x float64,
	y float64,
	z float64
}
```

## 5. Table Definition

```sql
create table player (
	id generated,
	name text not unknown,
	level int32 default 1,
	gold int64 default 0
)
```

Tables are strict by default.

Flexible tables use:

```sql
create table telemetry flexible
```

Flexible nested objects may use:

```sql
metadata {} flexible
```

## 6. Record Identity

Every persistent record has first-class identity.

Canonical record identity syntax is:

```sql
player:42
item:105
guild:'dragons'
```

A record identity may appear wherever a record reference is valid.

```sql
select *
from player:42
```

References are declared with:

```sql
owner record<player>
```

## 7. Constraints

Supported constraints include:

```sql
primary key
unique
not unknown
check(...)
references
```

Examples:

```sql
email text not unknown unique
```

```sql
level int32 not unknown check(level >= 1)
```

```sql
foreign key(account)
references account(id)
on delete cascade
```

Referential action clauses are:

```sql
on delete restrict
on delete cascade
on update restrict
on update cascade
```

Constraints are immediate by default.

Explicit deferment uses:

```sql
deferred
```

## 8. Indexes

Default indexes use:

```sql
create index player_name
on player(name)
```

Included values use:

```sql
create index player_zone
on player(zone)
include(name, position)
```

Specialized indexes use:

```sql
using search
using hnsw
using spatial
using timeseries
```

Examples:

```sql
create index lore_vector
on lore(embedding)
using hnsw
```

```sql
create index item_search
on item(name, description)
using search
```

## 9. Queries

The canonical query form is:

```sql
select select_list
from source
[join_clause ...]
[where predicate]
[group by expression_list]
[having predicate]
[order by ordering_list]
[limit expression]
[offset expression]
```

Example:

```sql
select
	id,
	name,
	level
from player
where level >= 20
order by level desc
limit 50
```

## 10. Projection

Column aliases require `as`.

```sql
select price * quantity as total
from order
```

`select *` is permitted for ad hoc queries.

Persistent schema-bound objects shall name fields explicitly.

## 11. Parameters

Parameters use named syntax:

```sql
:player_id
```

Example:

```sql
select *
from player
where id = :player_id
```

Positional parameter syntaxes are not part of the language.

## 12. Comparison and Unknown Tests

Equality and inequality:

```sql
=
!=
```

Relational comparison:

```sql
<
<=
>
>=
```

Unknown tests:

```sql
is unknown
is not unknown
```

Direct comparison against `unknown` is invalid.

## 13. Logical Operators

```sql
and
or
not
```

## 14. Arithmetic Operators

```text
+
-
*
/
%
div
```

`/` denotes mathematical division.

`div` denotes integer division.

## 15. Text Concatenation

Text concatenation uses:

```sql
||
```

Example:

```sql
first_name || ' ' || last_name
```

## 16. Ordering

Ordering uses:

```sql
order by expression asc
order by expression desc
```

Unknown placement may be overridden:

```sql
unknowns first
unknowns last
```

Example:

```sql
order by last_seen desc unknowns last
```

## 17. Pagination

Only this form is defined:

```sql
limit 50
offset 100
```

## 18. Joins

Supported joins are:

```sql
join
inner join
left join
right join
full join
cross join
```

Join conditions use:

```sql
on
using
```

Example:

```sql
select
	player.name,
	account.email
from player
join account on account.id = player.account
```

Comma joins and `natural join` are not part of the language.

## 19. Reference Traversal

Record references may be traversed with `.`.

```sql
select
	name,
	owner.name
from item
```

Explicit joins remain available.

## 20. Grouping and Aggregation

Grouping uses:

```sql
group by
having
```

Standard aggregates include:

```sql
count()
sum()
avg()
min()
max()
```

Examples:

```sql
select
	guild,
	count(*) as members
from player
group by guild
```

## 21. Insert

```sql
insert into player(
	name,
	level
)
values(
	:name,
	1
)
```

## 22. Returning

`insert`, `update`, and `delete` support:

```sql
returning
```

Example:

```sql
insert into player(name)
values(:name)
returning id
```

## 23. Update

```sql
update player
set gold = gold + 100
where id = :player
```

Record-addressed form:

```sql
update player:42
set gold = gold + 100
```

Joined update uses:

```sql
update player
set guild = unknown
from guild_member
where player.id = guild_member.player
and guild_member.guild = :guild
```

## 24. Delete

```sql
delete from session
where expires_at < :now
```

Record-addressed form:

```sql
delete from item:105
```

Joined delete uses `using`.

## 25. Conflict Handling

```sql
insert into inventory(
	player,
	slot,
	item
)
values(
	:player,
	:slot,
	:item
)
on conflict(player, slot)
do update set
	item = excluded.item
```

`merge` is not part of the language.

## 26. Relations

Graph relationships are first-class records.

Definition:

```sql
create relation friendship
	from player
	to player
{
	since instant,
	trust float32
}
```

Creation:

```sql
relate player:10
	to player:20
	through friendship {
		since = now(),
		trust = 0.8
	}
```

Traversal:

```sql
select friend.*
from player:10
through friendship
to player as friend
where friendship.trust > 0.5
```

## 27. Functions

A function computes values and may be used from queries.

```sql
function combat_rating(level, strength, agility)
	return level * 10 + strength * 2 + agility
end
```

Usage:

```sql
select
	name,
	combat_rating(level, strength, agility) as rating
from player
order by rating desc
```

## 28. Routines

All procedural database behavior uses `routine`.

```sql
routine purchase(item_id)
	local buyer = select *
		from player:auth.player
		for update

	local item = select *
		from item:item_id

	if item is unknown then
		error("item not found")
	end

	if buyer.gold < item.price then
		error("insufficient gold")
	end

	update player:auth.player
	set gold = gold - item.price

	insert into inventory(
		player,
		item
	)
	values(
		auth.player,
		item.id
	)

	emit item_purchased {
		player = auth.player,
		item = item.id
	}
end
```

A routine capable of external I/O uses:

```sql
routine send_webhook() external
	...
end
```

## 29. Local Variables

```sql
local name = expression
```

Optional explicit typing:

```sql
local amount int64 = 100
```

## 30. Conditional Control Flow

```sql
if condition then
	...
elseif condition then
	...
else
	...
end
```

## 31. Iteration

```sql
for row in query do
	...
end
```

Example:

```sql
for auction in select *
	from auction
	where expires <= now()
do
	expire(auction.id)
end
```

General conditional loops use:

```sql
while condition do
	...
end
```

Loop control:

```sql
break
continue
```

## 32. Return

```sql
return
```

or:

```sql
return expression
```

## 33. Errors

Raise an error with:

```sql
error("message")
```

Structured errors use:

```sql
error inventory_full {
	player = auth.player
}
```

Catch errors with:

```sql
try
	...
catch unique_violation
	...
catch permission_error
	...
end
```

## 34. Authentication Context

Client-executed routines and policy expressions may access:

```text
auth.identity
auth.account
auth.player
auth.roles
auth.session
```

`auth` is intrinsic and is not passed as an ordinary parameter.

## 35. Policies

Basic policy:

```sql
create policy own_inventory
on inventory
where player = auth.player
```

Operation-specific policy:

```sql
create policy guild_read
on guild_member
for select
where guild in auth.guilds
```

## 36. Events

Application-semantic events use:

```sql
emit event_name {
	field = expression
}
```

Example:

```sql
emit item_purchased {
	player = auth.player,
	item = item.id,
	price = item.price
}
```

## 37. Durable Changes

Change history is queried with:

```sql
changes player since :cursor
```

`changes` denotes durable mutation history.

## 38. Subscriptions

Reactive query state uses `subscribe`.

```sql
subscribe nearby_players as
select
	id,
	name,
	position
from player
where zone = :zone
```

A subscription may reference `auth` or named parameters.

## 39. Scheduling

Periodic scheduling:

```sql
schedule expire_auctions
every 1 minute
```

Record-driven scheduling:

```sql
schedule expire_auction(auction.id)
at auction.expires
```

## 40. Lifecycle Routines

Predefined lifecycle routine names include:

```sql
routine connect()
	...
end
```

```sql
routine disconnect()
	...
end
```

```sql
routine startup()
	...
end
```

```sql
routine shutdown()
	...
end
```

## 41. Trigger Form

Where explicit data triggers are required, routine syntax is reused.

```sql
routine after insert on player
	...
end
```

Application logic should prefer named routines over hidden trigger chains.

## 42. Full-Text Search

```sql
select *
from item
where search(item_search, :query)
order by score(item_search) desc
```

## 43. Vector Search

```sql
select *
from lore
order by cosine_distance(embedding, :query)
limit 10
```

## 44. Spatial Querying

```sql
select *
from zone
where contains(bounds, :position)
```

## 45. Time-Series Storage Hints

Ordinary tables are used.

Optional partitioning:

```sql
partition metric by time
```

Specialized index:

```sql
create index metric_time
on metric(player, time)
using timeseries
```

## 46. Explicit Transactions

```sql
begin
...
commit
```

Rollback:

```sql
rollback
```

Routine bodies normally do not require explicit transaction syntax.

## 47. Explicit Concurrency

```sql
select *
from auction:42
for update
```

## 48. Minimal Nontraditional Keyword Set

The major additions beyond ordinary SQL are:

```text
unknown

routine
local
if
then
elseif
else
end
for
in
do
while
try
catch
error

relation
relate
through

emit
changes
subscribe

auth
external
schedule
```

The language should not introduce additional keywords where existing syntax can express the same concept clearly.