We reproduce the queries from the section on querying hierarchical data using Rabbit syntax.
include("../citydb.jl")
using RBT
setdb(citydb)
Department: name :: UTF8String # unique employee (inverse of Employee.department) :: Array{Employee,1} # unique, covering Employee: name :: UTF8String surname :: UTF8String position :: UTF8String salary :: Int64 department :: Department managed_by :: Nullable{Employee} manages (inverse of Employee.managed_by) :: Array{Employee,1} # unique
We can execute a query using @query()
command:
@query(6*(3+4))
42
Find the names of all departments.
@query(department.name)
35-element Array{UTF8String,1}: "WATER MGMNT" "POLICE" "GENERAL SERVICES" "CITY COUNCIL" "STREETS & SAN" ⋮ "BOARD OF ETHICS" "POLICE BOARD" "BUDGET & MGMT" "ADMIN HEARNG" "LICENSE APPL COMM"
Find the names of all employees.
@query(department.employee.name)
32181-element Array{UTF8String,1}: "ELVIA" "VICENTE" "MUHAMMAD" "GIRLEY" "DILAN" ⋮ "NANCY" "DARCI" "THADDEUS" "RACHENETTE" "MICHELLE"
We are not longer restricted by the hierarchical structure of the database, so we can query employees directly.
@query(employee.name)
32181-element Array{UTF8String,1}: "ELVIA" "JEFFERY" "KARINA" "KIMBERLEI" "VICENTE" ⋮ "MICHAEL" "PETER" "MARK" "CARLO" "DARIUSZ"
If the traversal ends at an entity class, an array of records is generated.
@query(employee)
32181-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}: ("ELVIA","A","WATER MGMNT","WATER RATE TAKER",88968) ("JEFFERY","A","POLICE","POLICE OFFICER",80778) ("KARINA","A","POLICE","POLICE OFFICER",80778) ("KIMBERLEI","A","GENERAL SERVICES","CHIEF CONTRACT EXPEDITER",84780) ("VICENTE","A","WATER MGMNT","CIVIL ENGINEER IV",104736) ⋮ ("MICHAEL","Z","GENERAL SERVICES","FRM OF MACHINISTS - AUTOMOTIVE",97448) ("PETER","Z","POLICE","POLICE OFFICER",86520) ("MARK","Z","POLICE","POLICE OFFICER",83616) ("CARLO","Z","POLICE","POLICE OFFICER",86520) ("DARIUSZ","Z","DoIT","CHIEF DATA BASE ANALYST",110352)
Find the number of departments.
@query(count(department))
35
Find the number of employees for each department.
@query(department.count(employee))
35-element Array{Int64,1}: 1848 13570 924 397 2090 ⋮ 9 2 43 39 1
Find the total number of employees.
@query(count(department.employee))
32181
Again, we can query employee
directly.
@query(count(employee))
32181
Find the top salary among all employees.
@query(max(employee.salary))
Nullable(260004)
Find the maximum number of employees per department.
@query(max(department.count(employee)))
Nullable(13570)
For each department, find the number of employees.
@query(department:select(name,count(employee)))
35-element Array{Tuple{UTF8String,Int64},1}: ("WATER MGMNT",1848) ("POLICE",13570) ("GENERAL SERVICES",924) ("CITY COUNCIL",397) ("STREETS & SAN",2090) ⋮ ("BOARD OF ETHICS",9) ("POLICE BOARD",2) ("BUDGET & MGMT",43) ("ADMIN HEARNG",39) ("LICENSE APPL COMM",1)
It is easy to add new columns to the output. Let us add the top salary per department.
@query(
department
:select(
name,
count(employee),
max(employee.salary)))
35-element Array{Tuple{UTF8String,Int64,Nullable{Int64}},1}: ("WATER MGMNT",1848,Nullable(169512)) ("POLICE",13570,Nullable(260004)) ("GENERAL SERVICES",924,Nullable(157092)) ("CITY COUNCIL",397,Nullable(160248)) ("STREETS & SAN",2090,Nullable(157092)) ⋮ ("BOARD OF ETHICS",9,Nullable(131688)) ("POLICE BOARD",2,Nullable(97728)) ("BUDGET & MGMT",43,Nullable(169992)) ("ADMIN HEARNG",39,Nullable(156420)) ("LICENSE APPL COMM",1,Nullable(69888))
Find the employees with salary greater than $200k.
@query(
employee
:filter(salary>200000))
3-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}: ("RAHM","E","MAYOR'S OFFICE","MAYOR",216210) ("GARRY","M","POLICE","SUPERINTENDENT OF POLICE",260004) ("JOSE","S","FIRE","FIRE COMMISSIONER",202728)
Find the departments with more than 1000 employees.
@query(
department
:filter(count(employee)>1000)
:select(name, count(employee)))
7-element Array{Tuple{UTF8String,Int64},1}: ("WATER MGMNT",1848) ("POLICE",13570) ("STREETS & SAN",2090) ("AVIATION",1344) ("FIRE",4875) ("OEMC",1135) ("TRANSPORTN",1200)
Find the number of departments with more than 1000 employees.
@query(
count(
department
:filter(count(employee)>1000)))
7
For each department, find the number of employees with salary higher than $100k.
@query(
department
:select(
name,
count(employee:filter(salary>100000))))
35-element Array{Tuple{UTF8String,Int64},1}: ("WATER MGMNT",179) ("POLICE",1493) ("GENERAL SERVICES",79) ("CITY COUNCIL",54) ("STREETS & SAN",39) ⋮ ("BOARD OF ETHICS",2) ("POLICE BOARD",0) ("BUDGET & MGMT",12) ("ADMIN HEARNG",3) ("LICENSE APPL COMM",0)