Aerospike JDBC driver: SQL DML compliance

Introduction

My previous post explained SQL DDL commands supported by the Aerospike JDBC driver. This post will discuss the DML commands.

DML - data manipulation language

Data manipulation language consists of the following commands:

• INSERT
• SELECT
• UPDATE
• DELETE

Aerospike JDBC driver treats Aerospoike sets as tables, and bins as columns. However Aerospike is not relational database. It treats key and value differently. Key is not a regular column. Aerospike JDBC driver uses special identifier PK to refer the primary key.

INSERT

Insert for Aerospike is more than insert. Since Aerospike is schemaless, schema is actually defined by the data. Table (set) is created when data is written there. The actual data "defines" the table schema, i.e. types and name of table columns (or set bins using Aerospike terms.)

Insert syntax is not differ from syntax of regular SQL. You can either insert one row:

insert into people (PK, first_name, last_name, year_of_birth) values (1, 'John', 'Lennon', 1940)

or several rows:

insert into people (PK, first_name, last_name, year_of_birth) 
values 
(1, 1, 'John', 'Lennon', 1940), 
(2, 'Paul', 'McCartney', 1942)

Important: primary key (PK) is required in INSERT statement.

SELECT

Select statement is used in order to retrieve data from the table. Either explicit definition of columns to be retrieved or * are supported:

select first_name, last_name from people
select * from people

Data can be filtered using WHERE statement:

select * from people where name = 'John'

Unlike AQL where clause here can use expressions, calculations, functions, parentheses and refer PK or any bin name (column) either indexed or not. Logical operations <, <. >=, <=, =, !=, IN operator and LIKE statement for textual fields are supported as well. BETWEEN statement is supported for numeric values only.

select * from people where name in ('John', 'Bill')
select * from people where PK in (1, 2, 3)
select * from people where PK BETWEEN (1, 42)
select * from people where id + 5 < 10

Rows returned by select can be sorted using ORDER BY and sliced using LIMIT and OFFSET. Inner SELECT statements are supported.

JOIN

Select can be used with join. Inner and left joins are supported. The join expression can use equality expression on any column. PK can be used for the right table. Using PK on the left table is possible if PK is stored in the DB (that does not happen by default). One of the next posts will be dedicated to details of using PK in queries.

UPDATE

Update and insert in AQL are similar. Both commands add new record if specified PK does not exist and update record if PK exists. Update implemented by Aersoplike SQL driver behave like the standard SQL update. It updates records found by criteria specified in WHERE clause. WHERE expression can be complex (see SELECT).

DELETE

Unlike DELETE implemented by AQL that can delete entries only identified by PK this delete works with regular where clause (see update).

Project home

The project is available in GitHub.

What's next

Next article of this series will explain how to use built-in functions.

Previous

Aerospike JDBC driver: SQL DDL compliance