SQL command groups: DDL, DML, DCL, TCL, DQL

The five groups of SQL statements, every KDB statement in its group, and the role each group needs.

• Category: Getting started
• Keywords: DDL, DML, DCL, TCL, DQL, data definition language, data manipulation language, data control language, transaction control language, data query language, command groups, statement types, kinds of statements, sublanguage, which role do I need, TRUNCATE, SAVEPOINT
• See also: SQL basics, CREATE TABLE, ALTER, RENAME and DROP TABLE, Views, Indexes, INSERT, UPDATE and DELETE, Users and roles, Transactions, SELECT, SHOW TABLES, COLUMNS, CREATE TABLE

SQL statements are sorted into five groups by what they do. The group tells you three things at once: what the statement changes, which role a user needs for it, and whether it can go inside BEGIN ... COMMIT.

• Roles are checked only by kdb serve (HTTP, web console and the PostgreSQL port). kdb sql and kdb shell open the files directly and may run everything.
• Some books put SELECT into DML. KDB's help keeps it apart as DQL, because a read needs only the read role.
• The web console lists what DDL made: open the Objects list on the left (see web console).

DDL: Data Definition Language

DDL statements build the shape of a database. They change objects, not rows (except DROP, which removes the rows with the table).

• Role: admin. Databases need admin as the user's own ROLE.
• Notes:
  • Each DDL statement is saved on its own, at once. Most of them can not go inside BEGIN ... COMMIT (see what is not allowed inside a transaction).
  • Look at what DDL made with SHOW TABLES, SHOW COLUMNS FROM t, SHOW CREATE TABLE t, SHOW VIEWS, SHOW INDEXES, SHOW FUNCTIONS, SHOW PROCEDURES and SHOW TRIGGERS (see show-tables).
  • TRUNCATE TABLE t empties a table and keeps it (see UPDATE and DELETE). PostgreSQL counts it as DDL.

Example: DDL: a table, a column added later, a view and an index (sample data: sample)

CREATE TABLE watchlist (symbol STRING PRIMARY KEY, note STRING);
ALTER TABLE watchlist ADD COLUMN target DOUBLE;
CREATE VIEW watchlist_names AS
SELECT w.symbol, s.name, w.target FROM watchlist w JOIN securities s ON s.symbol = w.symbol;
CREATE INDEX watchlist_note ON watchlist (note);
SHOW COLUMNS FROM watchlist;
SHOW VIEWS;

Result:

CREATE TABLE watchlist (

table 'watchlist' created

ALTER TABLE watchlist ADD COLUMN target DOUBLE

table 'watchlist' altered

CREATE VIEW watchlist_names AS SELECT w.symbol, s.name, w.target FROM watchlist AS w JOIN securities

view 'watchlist_names' created

CREATE INDEX watchlist_note ON watchlist (note)

index 'watchlist_note' created on table 'watchlist' (0 rows)

SHOW COLUMNS FROM watchlist

(3 rows)

SHOW VIEWS

(1 row)

DML: Data Manipulation Language

DML statements change the rows of tables. The table itself stays as it is.

• Role: write.
• Notes:
  • Each statement is all or nothing: an INSERT of 5000 rows with one bad row saves no row.
  • To make many DML statements succeed or fail together, put them inside BEGIN ... COMMIT (TCL).
  • Triggers run by themselves after (or before) DML on their table.
  • A view has no rows of its own: DML on a view is refused. Change the table the view reads.

Example: DML: add, change and remove rows (sample data: sample)

Setup:

CREATE TABLE watchlist (symbol STRING PRIMARY KEY, note STRING, target DOUBLE);

INSERT INTO watchlist VALUES ('CITY', 'bank', 520), ('VOLT', 'hydro', 250);
UPDATE watchlist SET target = 540 WHERE symbol = 'CITY';
DELETE FROM watchlist WHERE symbol = 'VOLT' RETURNING symbol, note;
SELECT * FROM watchlist;

Result:

INSERT INTO watchlist VALUES ('CITY', 'bank', 520), ('VOLT', 'hydro', 250)

2 row(s) inserted

UPDATE watchlist SET target = 540 WHERE symbol = 'CITY'

1 row(s) updated

DELETE FROM watchlist WHERE symbol = 'VOLT' RETURNING symbol, note

(1 row)

1 row(s) deleted

SELECT * FROM watchlist

(1 row)

DCL: Data Control Language

DCL statements decide who may connect and what each user may do.

The roles, from least to most: none < read < write < admin. Each role may do everything of the roles before it.

• Role: admin as the user's own ROLE (a GRANT of admin on one database is not enough), because users belong to the whole server. Changing your own password needs no role.
• Notes:
  • The first user must have ROLE admin. From then on, kdb serve asks everyone to log in.
  • Users are kept in the _system database, not in the database you are using.
  • Give each program only what it needs: an app's backend write on its own database, a report script read.

Example: DCL: an admin, a read-only report user, and an app that may write one database (sample data: sample)

Setup:

CREATE USER admin WITH PASSWORD 'Long-Admin-Passw0rd' ROLE admin;

CREATE USER report WITH PASSWORD 'Reports-only-secret-7' ROLE read;
CREATE USER shop_app WITH PASSWORD 'Another-long-secret-42' ROLE none;
GRANT write ON DATABASE demo TO shop_app;
SHOW USERS;

Result:

CREATE USER report WITH PASSWORD '***' ROLE read

user 'report' created with role read

CREATE USER shop_app WITH PASSWORD '***' ROLE none

user 'shop_app' created with role none

GRANT write ON DATABASE demo TO shop_app

user 'shop_app' has role write on database 'demo'

SHOW USERS

(3 rows)

TCL: Transaction Control Language

TCL statements group DML into one transaction: KDB saves all of its changes together, or none of them.

• Role: read to start and end one. The statements inside still need their own role (write for DML).
• Notes:
  • Without BEGIN, every statement is its own small transaction and is saved at once.
  • When a statement inside the transaction fails, KDB undoes the whole transaction.
  • SAVEPOINT, ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT undo part of a transaction (savepoints).
  • One writer at a time: while a transaction changes rows, other writers wait (see one writer at a time).

Example: TCL: a transfer that is saved, and a mistake that is undone (sample data: sample)

Setup:

CREATE TABLE accounts (id INT PRIMARY KEY, owner STRING NOT NULL, balance DECIMAL(2) NOT NULL);
INSERT INTO accounts VALUES (1, 'Ram', 1000), (2, 'Sita', 0);

BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 1;
UPDATE accounts SET balance = balance + 300 WHERE id = 2;
COMMIT;

BEGIN;
DELETE FROM accounts;
ROLLBACK;

SELECT * FROM accounts ORDER BY id;

Result:

BEGIN

transaction started

UPDATE accounts SET balance = balance - 300 WHERE id = 1

1 row(s) updated

UPDATE accounts SET balance = balance + 300 WHERE id = 2

1 row(s) updated

COMMIT

transaction committed: 2 change(s) saved

BEGIN

transaction started

DELETE FROM accounts

2 row(s) deleted

ROLLBACK

transaction rolled back: 1 change(s) undone

SELECT * FROM accounts ORDER BY id

(2 rows)

DQL: Data Query Language

DQL statements read. They never change a row or an object, so they are safe to run at any time.

• Role: read.
• Notes:
  • Selecting from a view is DQL too: the view's query runs, and nothing is changed.
  • SELECT ... FOR UPDATE is the one read that waits for writers; it belongs with TCL.

Example: DQL: a report, a query plan and a list of objects (sample data: sample)

SELECT symbol, count(*) AS trades, sum(quantity) AS shares
FROM trade_log GROUP BY symbol ORDER BY trades DESC, symbol LIMIT 3;
EXPLAIN SELECT * FROM ochl_day WHERE symbol = 'CITY';
SHOW TABLES;

Result:

SELECT symbol, count(*) AS trades, sum(quantity) AS shares FROM trade_log GROUP BY symbol ORDER BY t

(3 rows)

EXPLAIN SELECT * FROM ochl_day WHERE symbol = 'CITY'

(4 rows)

SHOW TABLES

(6 rows)

Which group is my statement?

Other statements belong to looking after the database, not to one of the five groups: BACKUP DATABASE, CHECK TABLE, CHECKPOINT (see backup and safety) and USE (see databases).