When you use DDL statements, LightDB will first check whether your statements comply with the specification.

At present, there are five checks: keyword check (including table name, field name, library name and index name), field type check (float,double,text,real, uuid,bytea,boolean,bit,character), whether the table must contain primary key, whether the table contains fields of creation time and last modification time, and the number of columns for operation does not exceed 100.The DDL statement specification supports:

CREATE DATABASE...
CREATE TABLE...
CREATE INDEX...
ALTER TABLE...

The keyword list can be referenced "https://www.postgresql.org/docs/current/sql-keywords-appendix.html"

When you use nonstandard statements, you will report warnings or errors directly. For example:

create database text;
WARNING:  LightDB DDL check warn! keyword not allowed with [db name]:text

create table text(id int);
WARNING:  LightDB DDL check warn! keyword not allowed with [table name]:text
WARNING:  LightDB DDL check warn! keyword not allowed with [column name]:id
WARNING:  LightDB DDL check warn! no primary key!
WARNING:  LightDB DDL check warn! no gmt_create or no gmt_modified!

Where 'text' is an nonstandard database name,So report a warning. When creating a table, the table name is not standard, the column name is not standard, does not contain the primary key, does not contain the creation time and the last modification time, and the fields are warned. Of course, there is the upper limit of 100 operation columns, which is not listed here.

By default, DDL syntax checking is not enabled,There are two ways to turn it on. Global modification by modifying the configuration file:

lightdb_best_practice_level = 'warn'
lightdb_best_practice_mode = 'all'

A value of 'warn' for lightdb_best_practice_level indicates that the error level is warning. A value of 'error' indicates an error. When an nonstandard statement is detected, an error is reported directly：

create database text;
ERROR:  LightDB DDL check error! keyword not allowed with [db name]:text

If lightdb_best_practice_mode is configured as' all ', it means to check 5 types at the same time. Of course, you can combine the checks you want to configure at will, for example:

lightdb_best_practice_mode = 'keyword_not_allowed,must_have_pk'

The above configuration means that only keywords and primary keys are checked.

Another configuration method is session level modification:

set lightdb_best_practice_level to 'warn'; 
set lightdb_best_practice_mode to 'all';

There are five kinds of fully open here, and there is another way:

set lightdb_best_practice_mode to 'keyword_not_allowed,bad_datatype,must_have_pk,must_have_gmt,no_more_100cols';