This feature will provides rules to standardize sql writing.

There are two parts to this: LightDB DDL Specification Check and LightDB DML Specification Check.

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 "www.hs.net/lightdb/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';

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

At present, there is one check, no paging check: Remind the user when the user SQL does not specify query criteria or paging. The usage of these checks is described in detail below.

How to use it? lightdb_best_practice_level and lightdb_best_practice_mode in the configuration file need to be configured.

By default, DML syntax checking is not enabled. Global modification by modifying the configuration file:

lightdb_best_practice_level = 'error'
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：

select * from table1;
ERROR:  LightDB DML check error! select sql not where/limit/offset clause!

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

lightdb_best_practice_mode = 'no_paging'

This feature will intercept risk sql that match the specified rule in lt_sql_inspect.secure_mode.

Only SQL that operates on the specified database will be blocked, using lt_sql_inspect.secure_enable_db to specify the database

You can define a time frame by lt_sql_inspect.secure_enable_time , and only in this time frame sql will be intercepted. The time frame can only be within a day.

lightdb_best_practice_level controls the log level when SQL is intercepted. default is 'warn'. A value of 'warn' for lightdb_best_practice_level indicates that the error level is warning. A value of 'error' indicates an error.

Refer to lt_sql_inspect.secure_mode for detail rules.