Syntax

The Katzebase SQL (KBSQL) is very similar to T-SQL. Each statement is self-terminating and does not require delimiters or terminators. It can be used to create and alter objects, schema and indexes. Create, read and delete data. Execute procedures and functions, evaluate engine state and perform various calculations.
The statements are broken down into two types (1) DML or (data manipulation language) which is for selecting, updating, inserting and deleting data and (2) DDL or (data definition language) which are used for modifying database "objects" like schema, procedures and indexes.

That's really it for the basics. There are certainly more advanced topics such as index analysis, partitioning and system functions, but those are addressed in individual topics.



Statement to SELECT all fields from the Word

SELECT * FROM WordList:Word

Statement to SELECT specific fields from Word schema.

SELECT
    Id, Text, LanguageId
FROM
    WordList:Word

Statement to SELECT specific fields from Word schema for a given Id.

SELECT
    Id, Text, LanguageId
FROM
    WordList:Word
WHERE
    Text = 'Dog'

Statement to INSERT a single document into Word.

INSERT INTO WordList:Word
(
    Text = 'Sarah',
    LanguageId = 1
)

Statement to INSERT multiple documents into the Word schema.
You can repeat as many of these sets as you like. Notice that the fields can vary per-set.

INSERT INTO WordList:Word
(
    Id = 0,
    Text = 'Sarah',
    LanguageId = 1
),
(
    Id = 1,
    Text = 'Brightman'
)

Statement to UPDATE a single document in the Word schema based on a given Id.

UPDATE
    WordList:Word
SET
    Text = 'Neward',
    LanguageId = 2
WHERE
    Id = 0

Executes a stored procedure, passing it a parameter.

EXEC WordList:Word:CreateAndGetWord('Neward')

Statement to delete a document from Word schema.

DELETE FROM
    WordList:Word
WHERE
    Text = 'Neward'

Statement to create a schema with the globally configured page size.

CREATE SCEHMA WordList:Definitions

Statement to create a schema with a user-defined page size.
For more information, see PageSize.

CREATE SCEHMA WordList:Definitions WITH (PageSize = 10000)

Statement to delete a schema and all data (and sub-schemas) that it contains.

DROP SCEHMA WordList:Definitions

Statement to create a stored procedure.

CREATE PROCEDURE CreateAndGetWord
(
    @Word as string
) ON WordList:Word AS
(
    INSERT INTO WordList:Word
    (
        Text = @Word,
        GUID = Guid(),
        LanguageId = 1
    )

    SELECT * FROM WordList:Word WHERE Text = @Word
    
    DELETE FROM WordList:Word WHERE Text = @Word    
)

Statement to create an index.

CREATE INDEX IX_Word_Text(
    LanguageId,
    Text
) ON WordList:Word

Statement to create an index with a user-defined partition size.
For more information, see partitions and Rebuild_Index.

CREATE INDEX IX_Word_Text(
    LanguageId,
    Text
) ON WordList:Word WITH(Partitions = 1000)

Statement to drop an existing

DROP INDEX IX_Word_Text ON WordList:Word