Syntax

Table of Contents


Overview
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.

Basic DML Statements
Select
round-pushpin Statement to SELECT all fields from the Word
SELECT * FROM WordList:Word

round-pushpin Statement to SELECT specific fields from Word schema.
SELECT
    Id, Text, LanguageId
FROM
    WordList:Word

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

Insert
round-pushpin Statement to INSERT a single document into Word.
INSERT INTO WordList:Word
(
    Text = 'Sarah',
    LanguageId = 1
)

round-pushpin 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'
)

Update
round-pushpin 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

Execute
round-pushpin Executes a stored procedure, passing it a parameter.
EXEC WordList:Word:CreateAndGetWord('Neward')

Delete
round-pushpin Statement to delete a document from Word schema.
DELETE FROM
    WordList:Word
WHERE
    Text = 'Neward'

Basic DDL Statements
Create Schema
round-pushpin Statement to create a schema with the globally configured page size.
CREATE SCEHMA WordList:Definitions

round-pushpin Statement to create a schema with a user-defined page size.
For more information, see PageSize.
CREATE SCEHMA WordList:Definitions WITH (PageSize = 10000)


Drop Schema
round-pushpin Statement to delete a schema and all data (and sub-schemas) that it contains.
DROP SCEHMA WordList:Definitions


Create Procedure
round-pushpin 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	
)

Create Index
round-pushpin Statement to create an index.
CREATE INDEX IX_Word_Text(
	LanguageId,
	Text
) ON WordList:Word

round-pushpin 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)

Drop Index
round-pushpin Statement to drop an existing
DROP INDEX IX_Word_Text ON WordList:Word

Related