Fauna Query Language for SQL users This section describes a number of common structured query language (SQL) queries and their Fauna Query Language (FQL) equivalents. While it is almost impossible to provide an exhaustive comparison of every variation of all SQL commands, we provide a very basic comparison of the most used DDL (Data Definition Language) and DML (Data Manipulation Language) queries. As you gain more experience with the Fauna Query language, the ease and power of its syntax should become evident. Complex queries that are difficult, or even impossible, in SQL can be composed very easily in FQL. One very important difference between the two is that FQL is not a declarative language as SQL. Hence the actual path of execution needs to be provided for each queryāāāFQL requires the developer to specify an index in most queries. Conceptual equivalents Relational FaunaDB Schema Database Table Class Row Instance Index/Materialized Views Index In these examples below, we use two tables, dept (departments) and emp (Employees) to compare basic operations in both query languages. These tables have been extensively used in Oracle documentation to explain various SQL basics. SQL> DESC emp Name Null? Type ----------------------------------------- -------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL> DESC dept Name Null? Type ----------------------------------------- -------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) ZIP NUMBER Data definition language CREATE DATABASE In some relational databases, like MySQL, a database can be created with: CREATE DATABASE employees; CREATE DATABASE Fauna is a multi-tenant database and databases can be created like a nested tree. CreateDatabase({name: "employees"}) CREATE TABLE CREATE TABLE dept( deptno NUMBER(2,0), dname VARCHAR2(14), loc VARCHAR2(13), CONSTRAINT pk_dept PRIMARY KEY (deptno) ) CREATE CLASS CreateClass({name: "dept"}); FaunaDB doesnāt enforce the structure of a class at the time of creation. However, if we know that every instance of this class should have a deptno field, we can create a unique index on the deptno field which emulates a relational databaseās primary key. CreateIndex({ name: "dept_by_deptno", source: Class("dept"), terms: [{ field: [ "data", "deptno" ] }], unique: true }) ALTER TABLE ADD COLUMN ALTER TABLE dept ADD (zip NUMBER); No direct correlation As instances do not have a predefined schema, there is no straightforward equivalent to adding a term (equivalent to a column) to all instances without any values. The Fauna equivalent would be to run Update on the instance. Update( Ref(Class("dept"), "224507299921658368"), { data: { zip: 10001 } } ) TRUNCATE TABLE In SQL, truncate removes all records, but preserves the structure of the table. TRUNCATE TABLE dept; DELETE INSTANCES In FQL, the equivalent would be to delete all records from the table. Map( Paginate( Match(Index("all_depts")) ), Lambda("X", Delete(Var("X"))) ) The all_depts index is a class index that indexes the entire class. CreateIndex({ name: "all_depts", source: Class("dept") }) DROP TABLE DROP TABLE dept; DELETE CLASSES and INDEXES The Delete command can be used to remove tables and indexes. Unlike in SQL, dropping a table doesnāt remove the underlying indexes automatically. The indexes need to be removed manually. Delete a Class Delete(Class("dept")) Delete an Index Delete(Index("all_depts")) Data manipulation language INSERT RECORD INSERT INTO dept (deptno, dname, loc) VALUES (10, "ACCOUNTING", "NEW YORK"); CREATE INSTANCE Create( Class("dept"), { data: { "deptno": 10, "dname": "ACCOUNTING", "loc": "NEW YORK" } } ) UPDATE UPDATE dept SET loc = "AUSTIN" WHERE deptno = 10; UPDATE Update( Select("ref", Get( Match(Index("dept_by_deptno"), 10) ) ), { data: { loc: "AUSTIN" } } ) Running the Replace command on an entire document is also a form of Update. This is similar to a Delete followed by an Insert. Replace( Ref(Class("dept"), "224572974137606656"), { data: { "deptno": 10, "dname": "ACCOUNTING", "loc": "AUSTIN" } } ) DELETE DELETE FROM dept WHERE deptno = 10; DELETE You can use the reference as the key to delete a specific record. Delete( Ref(Class("dept"), "224532222499095041") ) Alternatively, you can delete a record based on a specific index column. Delete( Select( "ref", Get( Match(Index("dept_by_deptno"), 10) ) ) ) Query SELECT: ALL ROWS SELECT * FROM dept; GET ALL INSTANCES Just like in relational databases, selecting all instances from a class results in a full scan. In SQL, the server automatically selects the appropriate indexes based on the specified columns. In FaunaDB, indexes must be specified explicitly. You need a class index to run a full scan: CreateIndex({ name: "all_depts", source: Class("dept") }) Once the index is in place, run the query below. Map( Paginate( Match(Index("all_depts")) ), Lambda("X", Get(Var("X"))) ) SELECT: Based on a single Parameter SELECT * FROM dept WHERE deptno = 10; GET: Based on a single Parameter We can use the unique index we created earlier to enforce the primary key. Map( Paginate( Match(Index("dept_by_deptno"), 10) ), Lambda("X", Get(Var("X"))) ) SELECT: Based on a single Parameter with a NOT SELECT * FROM dept WHERE deptno != 10; GET: Based on a single Parameter with a NOT Unlike SQL, we create this list as a difference between two indexes, the class index and the unique index on the deptno. Map( Paginate( Difference( Match(Index("all_depts")), Match(Index("dept_by_deptno"), 10) ) ), Lambda("x", Get(Var("x"))) ) SELECT: Based on a condition SELECT * FROM emp WHERE sal > 2000 GET: Based on a condition In order to accomplish this, we need an index on the sal term along with the Refs that point to the location of each instance. CreateIndex({ name: "emp_by_sal", source: Class("emp"), values: [ {field: ["data", "sal"]}, {field: ["ref"]} ], }) After the index has built, we can get the results with: Map( Paginate( Match(Index("emp_by_sal")), { after: 2000 } ), Lambda("x", Get(Select(1, Var("x")))) ) Observe the Lambda function. The Select command gets the Ref fields, and then passes them to the Get command. Alternatively, we could have used Lambda(["sal", "ref"], Get(Var("ref"))) as we know the index returns two different values. SELECT: GROUP BY Query to select the maximum salary by department SELECT MAX(sal), deptno FROM emp GROUP BY deptno; GET: Grouped instances FQL can accomplish such queries using two indexes, the first on deptno and the second on deptno and salary. CreateIndex({ name: "emp_by_deptno", source: Class("emp"), values: [{ field: ["data","deptno"] }] }) CreateIndex({ name: "deptno_by_sal", source: Class("emp"), terms: [{ field: ["data","deptno"] }], values: [{ field: ["data","sal"] }] }) The second index deptno_by_sal stores the values sorted by sal within each deptno group. Since Get() returns the first element that matches the index, it returns the maximum value of sal. Map( Paginate( Distinct( Match(Index("emp_by_deptno")) ) ), gid => Get( Match(Index("deptno_by_sal"), Gid) ) ) EQUI-JOIN two tables SELECT e.* FROM emp e, dept d WHERE e.deptno = d.deptno AND d.dname = "SALES"; GET instances joined by two classes We need two indexes to accomplish this join in FQL. When we JOIN two classes, the value of one index is joined to the term of another index. Index #1 (Class: Dept, Term: dname, Value: deptno) CreateIndex({ name: "dept_by_name", source: Class("dept"), terms: [{ field: ["data", "dname"] }], values: [{ field: ["data","deptno"] }] }) Index #2 (Class: emp, Term: deptno) CreateIndex({ name: "emp_by_deptno", source: Class("emp"), terms: [{ field: ["data","deptno"] }] }) Query Map( Paginate( Join( Match(Index("dept_by_name"), "SALES"), Index("emp_by_deptno") ) ), Lambda("X", Get(Var("X"))) ) Stay tuned for another set of examples in the near future. If you are looking for a specific example, feel free to email me at [email protected].
