Syntax

Table of Contents



The Katzebase SQL dialect (KBSQL) is very similar to T-SQL. Statements do not need to be separated by semicolons or any other terminator; a script can simply list one statement after another. Statements are used to create and alter schemas and indexes; to insert, read, update and delete documents; to manage accounts and permissions; and to run system procedures.
The statements fall into two groups: (1) DML, or data manipulation language, for selecting, inserting, updating and deleting documents, and (2) DDL, or data definition language, for objects such as schemas, indexes, accounts and roles. See DML Glossary and DDL Glossary.

Sample data

The examples use the WordList sample database (see the home page) and the built-in Single schema.


A schema is a container of documents, indexes and other schemas. Schemas are nested and their names are separated with colons, for example WordList:Word is the Word schema inside the WordList schema. A document is a set of field/value pairs. Documents in the same schema do not need to have the same fields.
  • Keywords, schema names, field names and function names are not case-sensitive.
  • String comparisons with =, != and LIKE are not case-sensitive.
  • Strings are enclosed in single (or double) quotes.
  • Comments start with -- (until the end of the line) or are enclosed in /* and */.

See Expressions for literals, operators and variables.


round-pushpin Selects all fields from the Word schema.
SELECT * FROM WordList:Word

round-pushpin Selects specific fields from the Word schema for a given Text.
SELECT
    Id, Text, LanguageId
FROM
    WordList:Word
WHERE
    Text = 'Dog'


round-pushpin Inserts two documents using a field list and a values list.
INSERT INTO WordList:Word (Id, Text, LanguageId)
VALUES (1000, 'Sarah', 1), (1001, 'Brightman', 1)

round-pushpin Inserts two documents using field: value pairs. The fields can be different for each document.
INSERT INTO WordList:Word
(Id: 1002, Text: 'Neward', LanguageId: 1),
(Id: 1003, Text: 'Katzebase')


round-pushpin Updates the document with the given Id.
UPDATE
    WordList:Word
SET
    Text = 'Newark',
    LanguageId = 2
WHERE
    Id = 1002


round-pushpin Deletes documents by Text.
DELETE FROM
    WordList:Word
WHERE
    Text = 'Newark'



round-pushpin Creates a schema inside the existing WordList schema.
CREATE SCHEMA WordList:Definitions


round-pushpin Drops a schema with all of its documents, indexes and child schemas.
DROP SCHEMA WordList:Definitions


round-pushpin Creates a composite index.
CREATE INDEX IX_Word_LanguageId_Text
(
    LanguageId,
    Text
) ON WordList:Word


round-pushpin Drops an index.
DROP INDEX IX_Word_LanguageId_Text ON WordList:Word


round-pushpin Lists the connected processes.
EXEC ShowProcesses




SQL :: Right
Returns the given number of characters from the end of the value.
SQL :: Round
Rounds a number to the given number of decimal places. Midpoint values are rounded to the nearest even number (2.5 rounds to 2).
SQL :: Scalar Functions
Scalar functions are evaluated once per row.
SQL :: Sha1
Returns the SHA-1 hash of the value's UTF-8 bytes as a lower case hex string.
SQL :: Sha1Agg
Returns a single SHA-1 hash of all of the values in each group, as a lower case hex string. Useful for detecting changes to a set of documents.
SQL :: Sha256
Returns the SHA-256 hash of the value's UTF-8 bytes as a lower case hex string.
SQL :: Sha256Agg
Returns a single SHA-256 hash of all of the values in each group, as a lower case hex string. Useful for detecting changes to a set of documents.
SQL :: Sha512
Returns the SHA-512 hash of the value's UTF-8 bytes as a lower case hex string.
SQL :: Sha512Agg
Returns a single SHA-512 hash of all of the values in each group, as a lower case hex string. Useful for detecting changes to a set of documents.
SQL :: ShowAggregateFunctions
Lists the built-in aggregate functions with their parameters and descriptions.