⚙️ sql / name_sql / synonym_sql

Definition

The sql property (and related ones) specifies how the class is represented in the database (as a table or a column), what a unique key (sql) for this class is and how its entities are generated from SQL, if it should have any.

Usage

A class can be defined by a table, a column or a SQL. Let's take the example of a table customer(id, name, status).

In this example, Veezoo will generate two classes: kb.Customer and kb.Customer.Status.

Class defined by Table

In the case of kb.Customer, we have a class defined by a table customer.

import: [
    db.postgres.movie_db.public.customer
]

kb {
    class Customer {
        name.en: "Customer"

        from_table: customer
        // e.g. 00042
        sql: "${customer.id}"
        // e.g. [00042] Arthur Dent
        name_sql.en: "'[' || ${customer.id} || '] ' || ${customer.name}"        
        // e.g. Arthur Dent
        synonym_sql.en: "${customer.name}"

        ...
    }
}

Table Classes need to have

a reference to the table from_table: customer and
a SQL expression that uniquely identifies it sql: "${customer.id}", used for Joins as a key.

Optionally, if it is a class with values that you may want to refer to in questions (e.g. a specific customer), it makes sense to define the name for the entities of this class.

To do this you will add a name_sql with a language tag (e.g. .en for English), specifying how the name of the entity will be generated. If no name_sql is defined, Veezoo will use the sql as the default name_sql expression.

There may be synonyms in the table that you also want to use for the entities. These you will define using the property synonym_sql with the appropriate language tag. In our example, after clicking on the Sync Entities button in the Editor when you have the class file open, you will get entities like this:

kb.Customer {
    entity 00042_Arthur_Dent_1234 {         
        name.en: "[00042] Arthur Dent"

        synonym.en: "Arthur Dent"

        id: "00042"
    }
    ...
}

In the case of a class called kb.Order, you may prefer not to have any entities defined. This means name_sql and synonym_sql will be left out.

Class defined by Column

In our example, the class kb.Status was defined by the column status in the customer table. Let's take a look at how it would be defined in VKL.

import: [
    db.postgres.movie_db.public.customer
]

kb {
    // inside the file for the class Customer
    class Customer {

        ...


        class Status {
            name.en: "Status"
        
            // e.g. ACTIVE
            sql: "${customer.status}"
        }
    }
}

Each entity of the class kb.Customer.Status is uniquely defined by the sql expression, which also happens to be the default name_sql for the entities. In this case, there is no synonym_sql defined.

Advanced: Class defined by Table SQL

Let's assume that the customer table is split across two tables corporate_customer and private_customer, both with the same set of columns for simplicity, and you want a single class consisting of both tables. You can achieve this using table_sql with a SQL query that returns their union, replacing from_table as described for table classes.

kb {
    class Customer {
        name.en: "Customer"

        table_sql:
             """(SELECT * FROM movie_db.public.corporate_customer)
                 UNION
                (SELECT * FROM movie_db.public.private_customer)"""
        // These now use $table.column_name, where column_name is the SQL identifier 
        //  (which possibly needs to be escaped, depending on the SQL dialect)
        sql: "$table.id"
        name_sql.en: "'[' || $table.id || '] ' || $table.name"
        synonym_sql.en: "$table.name"

        class Status {
            name.en: "Status"
        
            // Same for attributes of the class, use ${ClassName}.column_name
            sql: "${Customer}.status"
        }
    }
}

table_sql also allows filtering, adding or transforming columns and joining other tables, and is thus very powerful.

Other than replacing from_table by table_sql and adapting SQLs based on it, a class defined using a table SQL will behave the same as a table class.

info

Prefer simple table classes over using table_sql if possible, as Veezoo will be able to help you more, for example you can import columns from the table and Veezoo will generate VKL for them.

Advanced: Class with Custom Entities defined by a SQL

The last way you can define a class is over a SQL query defining the entities, which follows a specific format.

Let's take the example of two tables customer(id, email) and profile(id, customer_id, name). For whatever reason, we have now two tables with information about a customer. For simplicity sake, let's assume that profile only has one entry per customer.

Now, we don't want to make profile accessible over Veezoo, since it is just extra information that was separated technically in different tables. Our business users have no clue why it is separated and they don't want to care about it.

What we will do instead now is to join both these tables so we can generate entities of the class kb.Customer with nice-looking names.

import: [
    db.postgres.movie_db.public.customer
]

kb {
    class Customer {
        name.en: "Customer"

        // this tag is required
        tag: DB_CustomSQLSemanticLink
        
        // e.g. 00042
        sql: "${customer.id}"
        from_table: customer

        entities_custom_sql: """
            SELECT
                c.id AS ID,
                '[' || c.id || '] ' || p.name AS LABEL_EN_MAIN,
                p.name AS LABEL_EN_1
            FROM
                customer c JOIN profile p ON c.id = p.customer_id
        """
    }
}

As you can notice, instead of a name_sql, we defined an entities_custom_sql.

Let's take a closer look at the SQL:

SELECT
    c.id AS ID,
    '[' || c.id || '] ' || p.name AS LABEL_EN_MAIN,
    p.name AS LABEL_EN_1
FROM
    customer c JOIN profile p ON c.id = p.customer_id

An entities_custom_sql always needs to have in the SELECT:

one ID column, coinciding with the sql, and
a LABEL_{LANGUAGE-TAG}_MAIN, which will define the name of entities, similar to a name_sql in the previous cases.

Optionally, every other LABEL_{LANGUAGE-TAG}_* defined is used the same way as synonym_sql is used in the previous sections.

It is important to notice that this SQL is only used to generate entities during Sync time, not query time. If you are interested in using custom SQLs during query time, please refer to virtual_table in the database part.

Custom Entities SQL for Hierarchies

The entities of a hierachy class can also be defined with entities_custom_sql. For this the entities_custom_sql need to define a PARENT column, which contains the ID of the parent entity:

import: [
    db.public.region
]

kb {
    class Region {
        name.en: "Region"
        
        tag: [
            KB_HierarchyClass,
            KB_NonAggregatedHierarchyClass
        ]

        entities_custom_sql: """
            SELECT
                r.id AS ID,
                '[' || r.id || '] ' || r.name AS LABEL_EN_MAIN,
                r.name AS LABEL_EN_1,
                r.parent_region AS PARENT
            FROM
                region r 
        """

        ...
    }
}

Definition​

Usage​

Class defined by Table​

Class defined by Column​

Advanced: Class defined by Table SQL​

Advanced: Class with Custom Entities defined by a SQL​

Custom Entities SQL for Hierarchies​