SQL

Below the SQL dialect supported by NitrosBase is described. In all examples, the database described in the Multi-modelity and Demo database documents is used.

CREATE TABLE

Syntax

CREATE TABLE [IF NOT EXISTS] tbl_name 
    ( create_definition [, create_definition] ... )
 
create_definition:
    col_name data_type 
      [NOT NULL | NULL] [DEFAULT literal]
      [COMMENT 'string']
    | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,...) REFERENCES tbl_name 

Example

CREATE TABLE person (
  name varchar, 
  lastname varchar, 
  age bigint, 
  city varchar, 
  income int, 
  dbl double, 
  dtime datetime
);

Due to multi-modelity support considerations, CREATE TABLE automatically creates the PRIMARY KEY id field, as, in the graph or document model, every node or document should have an unique identifier.

FOREIGN KEY may reference PRIMARY KEY field only. Currently, only id field can be a PRIMARY KEY.

CREATE TABLE AS EDGE

A special table for graph edge representation will be created. Such a table could be considered as an intermediate many-to-many table with two standard fields: fromid and toid. These fields will be added automatically.

Syntax

CREATE TABLE link_name AS EDGE from_tablename to_tablename; 

Example

CREATE TABLE owner AS EDGE car person;

Query example

SELECT p.id, name, lastname, age, c.id, c.model 
FROM person p 
  JOIN owner o ON p.id = o.toid 
  JOIN car c ON c.id = o.fromid 
WHERE age > 20 AND model = 'Toyota' 

The following query gives the same result (see also the JOIN expression description):

SELECT p.id, name, lastname, age, c.id, c.model 
FROM person p 
  JOIN car c ON p.id = c.owner 
WHERE age > 20 AND model = 'Toyota'

ALTER TABLE

Syntax

ALTER TABLE tbl_name
    [alter_specification [, alter_specification] ...]

alter_specification:
  table_options
  | ADD [COLUMN] col_name column_definition,...
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (col_name) REFERENCES tbl_name
  | {ALTER|MODIFY} [COLUMN] col_name column_definition,...
  | DROP [COLUMN] col_name


column_definition:
    data_type [NOT NULL | NULL] [DEFAULT literal]

Example

ALTER TABLE person ADD birthday DATE;

CREATE INDEX

Syntax

CREATE [UNIQUE] INDEX index_name ON tbl_name (col_name,...)

Example

CREATE INDEX p_ndx_age ON person (age);

DROP TABLE

Syntax

DROP TABLE [IF EXISTS]
    tbl_name

Example

DROP TABLE person;

DROP INDEX

Syntax

DROP INDEX index_name [ON tbl_name]

Example

DROP INDEX p_ndx_age;

INSERT

Syntax

INSERT [INTO] tbl_name
    [(col_name,...)]
     VALUES (value,...)

Example

INSERT INTO person
     (id, name, lastname, age, city, dbl, dtime)
     VALUES
     ('person5000', 'John', 'Tester', 30, 
     'Lisbon', 1.11, '2018-07-03 01:52:02.65');

BULK INSERT

Syntax

BULK INSERT table_name (col_name,...) 
FROM 'file_path' 
[WITH ([FIRSTROW = number,] [FIELDTERMINATOR = 'character'])]

Example

BULK INSERT person (id, name, lastname, age, city, income, dbl, dtime) 
FROM 'sntest1/person.csv' 
WITH (FIRSTROW = 2, FIELDTERMINATOR = ',');

Note

The FIRSTROW parameter specifies the line number from which the import should be started. For example, one should write FIRSTROW = 2 in order to skip headers.

Filepath is absolute or relative with respect to the directory where the running server file is located. For example, if nbserver.exe file is located in the c:/nitrosbase/bin directory, then the ../data/csv/person.csv path will be expanded as c:/nitrosbase/data/csv/person.csv.

UPDATE

Syntax

UPDATE table_reference
    SET col_name = value,...
    [WHERE where_condition]

UPDATE table_name_or_alias
    SET col_name = value,...
    FROM tablename [alias] | join_expression
    [WHERE where_condition]

Example

UPDATE person SET name = 'LAMAR' WHERE name = 'Lamar'

Note

Syntax of the FROM and WHERE expressions is the same as in the SELECT query.

DELETE

Syntax

DELETE FROM tbl_name
    [WHERE where_condition]
 
DELETE table_name_or_alias FROM join_expression
    [WHERE where_condition]

Example

DELETE FROM person WHERE name = 'Livia'

Note

Syntax of the FROM and WHERE expressions is the same as in the SELECT query.

BACKUP

BACKUP DATABASE TO path

Example

BACKUP DATABASE TO 'c:/data/sntest1/backup'

SELECT

Syntax

SELECT
    [ DISTINCT ]
    select_expression [, select_expression ...]
    FROM tablename | join_expression
    [WHERE where_condition]
    [GROUP BY col_name [, col_name ...]]
    -- [HAVING where_condition]
    [ORDER BY col_name [ASC | DESC] [, col_name …] ]
    [LIMIT row_count] 
    [OFFSET offset]
    [INDEX WHERE where_condition]

Example

DROP INDEX p_ndx_age;

SELECT

  • List of fields

    select name, lastname from person
    
  • All fields

    select * from person
    select p.*, c.model from person p join car c on p.id = c.owner
    
  • AS

    select count(*) as countofpersons from person
    
  • Simple functions and expressions

    select upper(name) as uname, age*2 as dbl from person
    
  • Aggregate functions

    select count(age), min(age), max(age), avg(age) from person
    
  • DISTINCT

    select distinct name, lastname from person
    

WHERE clause

  • Comparison and logical operators

    select * from person where age > 20 and name = 'Lamar'
    
  • Simple functions and expressions

    select * from person where age*2 - income/20 > 0 and upper(name) = 'LAMAR'
    
  • IN operator

    select name, age from person 
    where age in (20, 22, 24) and name in ('Lamar', 'Susan')
    
  • NULL check

    select * from person WHERE age IS NULL
    select * from person WHERE age IS NOT NULL
    

FROM and JOIN clauses

  • Single table query

    select * from person 
    
  • JOIN between two tables via the FOREIGN KEY

    select name, model from person p join car c on p.id = c.owner
    
  • JOIN between multiple tables

    SELECT p.id, name, lastname, age, c.id, c.model
    FROM person p 
      JOIN owner o ON p.id = o.toid 
      JOIN car c ON c.id = o.fromid
    WHERE age > 20 AND model = 'Toyota'
    
  • JOIN between two tables using intermediate table

    SELECT p.id, name, lastname, age, c.id, c.model
    FROM person p 
      JOIN owner o ON p.id = o.toid 
      JOIN car c ON c.id = o.fromid
    WHERE age > 20 AND model = 'Toyota'
    

    NitrosBase allows you to simplify the above query and consider the intermediate table as a FOREIGN key column with multiple values:

    SELECT p.id, name, lastname, age, c.id, c.model
    FROM person p 
      JOIN car c ON p.id = c.owner
    WHERE age > 20 AND model = 'Toyota'
    

Implementation details

NitrosBase speeds up JOIN operations storing links using graph subsystem capabilities.

In relational view, node links (edges) are represented as FOREIGN KEYs referencing the id column of the same or another table. JOIN condition may use the = operator only.

GROUP BY clause

  • Grouping by single field

    select count(dbl), avg(dbl), min(dbl), max(dbl) 
    from person 
    group by city
    
  • Grouping by multiple fields

    select count(dbl), avg(dbl), min(dbl), max(dbl) 
    from person 
    group by city, name
    

ORDER BY clause

  • Simple sorting by single field

    select name, lastname, age
    from person 
    order by name
    
  • Sorting by multiple fields with order specified

    select name, lastname, age
    from person
    order by name, lastname asc, age desc
    

LIMIT and OFFSET clauses

LIMIT and OFFSET are used to specify the number of results

  • First 100 results:

    select * from person limit 100
    
  • 10 results starting from 100th (i. e. skipping first 100 results? since numbering starts from zero):

    select * from person limit 10 offset 100
    

Operators

  • comparison operators:

    >, >=, <, <=, =, <>, !=, IS NULL, IS NOT NULL, IN

    select * from person where age is null
    
  • logical operators:

    and (&&), or (||), not

    select * from person
    where age > 50 and (name = 'Lamar' or lastname = 'Wurdeman')
    
  • arithmetic operators:

    +, -, *, /

    select age*2 from person where age*100/income > 3
    

Functions

Function Description
ASCII Returns the ASCII value for the specific character
CONCAT Adds two or more expressions together
LENGTH Returns the length of a string (in bytes)
LOWER Converts a string to lower-case
LPAD Left-pads a string with another string, to a certain length
POSITION Returns the position of the first occurrence of a substring in a string
REPEAT Repeats a string as many times as specified
REPLACE Replaces all occurrences of a substring within a string
REVERSE Reverses a string and returns the result
STRLEFT Extracts a number of characters from a string (starting from left)
STRRIGHT Extracts a number of characters from a string (starting from right)
RPAD Right-pads a string with another string, to a certain length
REGEX Match regex pattern
SUBSTR Extracts a substring from a string (starting at any position)
TRIM Removes leading and trailing spaces from a string
UPPER Converts a string to upper-case