Для корректной работы LSS сервера в базе данных должны присутствовать системные таблицы:

• systablecategory, systable, sysfield, sysfieldparams, sysconstraint - таблицы для хранения и правки LSS описания БД.
• sysfieldtype - справочник допустимых для LSS типов данных
• sysextlog - лог успешности выполнения плановых работ по обслуживанию системы
• sysdblog - лог для хранения истории правки пользовательских данных
• syssession, syssessioninfo - таблицы для хранения пользовательских данных, привязанных к сессии пользователя.
• sysconfig - таблица для хранения и правки системных настроечных констант, привязанных к площадке сервера.

Для их создания и корректной начальной инициализации служат скрипты sqlite-systable-create.sql и sqlite-systable-init.sql, расположенные в папке /sql/init/sqlite шаблона LSS проекта.

Эти скрипты необходимо последовательно прогнать на пустой БД.

-- Создание системных таблиц для сервера sqlite, для инициализации служит скрипт sqlite-systable-init.sql

-- Типы полей
create table "sysfieldtype" (
	"id" integer primary key,
	"name" varchar(255) not null default '',
	"isid" boolean not null default '0',
	"isref" boolean not null default '0',
	"isdec" boolean not null default '0',
	"isstr" boolean not null default '0',
	"isdat" boolean not null default '0',
	"defvalue" varchar(255) not null default '',
	"jstype" varchar(255) not null default '',
	"lsstype" varchar(255) not null default '',
	"ord" integer not null default 0
);

-- Категории таблиц
create table "systablecategory" (
	"id" integer primary key,
	"name" varchar(255) not null default '',
	"ord" integer not null default 0
);

-- Таблицы
create table "systable" (
	"id" integer primary key,
	"klssystablecategory" integer references "systablecategory" on delete cascade,
	"tablename" varchar(255) not null default '',
	"name" varchar(255) not null default '',
	"info" text not null default '',
	"orderby" text not null default '',
	"sqlview" text not null default '',
	"permmode" varchar(255) not null default '',
	"log" varchar(32) not null default '',
	"isstatic" boolean not null default '0',
	"isdynamic" boolean not null default '0',
	"issystem" boolean not null default '0',
	"tabletype" varchar(32) not null default ''
);
create index "idx_systable_klssystablecategory" on "systable" ("klssystablecategory");

-- Поля
create table "sysfield" (
	"id" integer primary key,
	"klssystable" integer references "systable" on delete cascade,
	"fieldname" varchar(255) not null default '',
	"name" varchar(255) not null default '',
	"info" text not null default '',
	"klssysfieldtype" integer references "sysfieldtype" on delete restrict,
	"isnotempty" boolean not null default '0',
	"ismain" boolean not null default '0',
	"maxlength" integer not null default 0,
	"len" integer not null default 0,
	"dec" integer not null default 0,
	"isstretch" boolean not null default '0',
	"klsreftable" integer references "systable" on delete restrict,
	"reflink" varchar(255) not null default '',
	"isrefrestrict" boolean not null default '0',
	"isrefcascade" boolean not null default '0',
	"isrefclear" boolean not null default '0',
	"isref121" boolean not null default '0',
	"islog" boolean not null default '0',
	"isvirtual" boolean not null default '0',
	"ord" integer not null default 0
);
create index "idx_sysfield_klssystable" on "sysfield" ("klssystable");
create index "idx_sysfield_klssysfieldtype" on "sysfield" ("klssysfieldtype");
create index "idx_sysfield_klsreftable" on "sysfield" ("klsreftable");

-- Параметры полей
create table "sysfieldparams" (
	"id" integer primary key,
	"klssysfield" integer references "sysfield" on delete cascade,
	"name" varchar(255) not null default '',
	"val" text not null default ''
);
create index "idx_sysfieldparams_klssysfield" on "sysfieldparams" ("klssysfield");

-- Ограничения, для руссификации сообщений об ошибках
create table "sysconstraint" (
	"id" integer primary key,
	"klssystable" integer references "systable" on delete cascade,
	"constraintname" varchar(255) not null default '',
	"message" text not null default ''
);
create index "idx_sysconstraint_klssystable" on "sysconstraint" ("klssystable");

-- Системный лог фоновых процессов
create table "sysextlog" (
	"id" integer primary key,
	"d" date,
	"tstart" varchar(5) not null default '',
	"tend" varchar(5) not null default '',
	"name" varchar(64) not null default '',
	"message" text not null default '',
	"iserror" int not null default '0'
);

-- История правки данных
create table "sysdblog" (
	"id" integer primary key,
	"child" varchar(36) not null default '',
	"childid" varchar(36) not null default '',
	"table" varchar(36) not null default '',
	"field" varchar(36) not null default '',
	"rowid" varchar(36) not null default '',
	"operation" varchar(3) not null default '',
	"value" varchar(1024) not null default '',
	"user" varchar(36) not null default '',
	"d" date,
	"t" varchar(8) not null default ''
);
create index "idx_sysdblog_child" on "sysdblog" ("child","childid");
create index "idx_sysdblog_table" on "sysdblog" ("table","rowid");
create index "idx_sysdblog_user" on "sysdblog" ("user");
create index "idx_sysdblog_d" on "sysdblog" ("d");

-- Хранение основных данных сессии в БД
create table "syssession" (
	"id" integer primary key,
	"code" varchar(128) not null,
	"value" text not null default '',
	"dcreate" text not null default (datetime('now', 'localtime')),
	"dlast" text not null default (datetime('now', 'localtime'))
);
create index "idx_syssession_code" on "syssession" ("code");
create index "idx_syssession_dlast" on "syssession" ("dlast");

-- Хранение дополнительных данных сессии в БД
create table "syssessioninfo" (
	"id" integer primary key,
	"syssessionid" integer references "syssession" on delete cascade,
	"code" varchar(64) not null default '',
	"value" text not null default '',
	"params" text not null default '',
	"dcreate" text not null default (datetime('now', 'localtime')),
	"json" text
);
create index "idx_syssessioninfo_syssessionid" on "syssessioninfo" ("syssessionid","code");

-- Системные конфигурации площадки
create table "sysconfig" (
	"id" integer primary key,
	"code" varchar(32) not null default '',
	"val" varchar(64) not null default ''
);
create unique index "uidx_sysconfig_code" on "sysconfig" ("code") where "id">0;

-- Системный лог выполнения версион скриптов
create table "sysdbversion" (
	"id" integer primary key,
	"code" varchar(64) not null default ''
);
create unique index "uidx_sysdbversion_code" on "sysdbversion" ("code") where "id">0;

-- Инициализация системных таблиц для сервера sqlite, для создания служит скрипт sqlite-systable-create.sql

PRAGMA foreign_keys = OFF;
delete from "sysfieldtype";
insert into "sysfieldtype" ("id","name","isid","isref","isdec","isstr","isdat","defvalue","jstype","lsstype","ord") values ('1','Id','1','0','1','0','0','','id','id','10');
insert into "sysfieldtype" ("id","name","isid","isref","isdec","isstr","isdat","defvalue","jstype","lsstype","ord") values ('2','Строка','0','0','0','1','0','','edit','string','20');
insert into "sysfieldtype" ("id","name","isid","isref","isdec","isstr","isdat","defvalue","jstype","lsstype","ord") values ('3','Текст','0','0','0','1','0','','memo','memo','30');
insert into "sysfieldtype" ("id","name","isid","isref","isdec","isstr","isdat","defvalue","jstype","lsstype","ord") values ('4','Число','0','0','1','0','0','0','num','num','40');
insert into "sysfieldtype" ("id","name","isid","isref","isdec","isstr","isdat","defvalue","jstype","lsstype","ord") values ('5','Дата','0','0','0','0','1','null','date','date','50');
insert into "sysfieldtype" ("id","name","isid","isref","isdec","isstr","isdat","defvalue","jstype","lsstype","ord") values ('6','Галочка','0','0','1','0','0','0','check','check','60');
insert into "sysfieldtype" ("id","name","isid","isref","isdec","isstr","isdat","defvalue","jstype","lsstype","ord") values ('7','Ссылка','0','1','0','0','0','','ref','ref','70');
insert into "sysfieldtype" ("id","name","isid","isref","isdec","isstr","isdat","defvalue","jstype","lsstype","ord") values ('8','Список','0','0','0','0','0','','list','list','80');
insert into "sysfieldtype" ("id","name","isid","isref","isdec","isstr","isdat","defvalue","jstype","lsstype","ord") values ('9','Иконки','0','0','0','0','0','','icons','icons','90');
insert into "sysfieldtype" ("id","name","isid","isref","isdec","isstr","isdat","defvalue","jstype","lsstype","ord") values ('10','Радиогруппа','0','0','0','0','0','','radio','radio','100');
insert into "sysfieldtype" ("id","name","isid","isref","isdec","isstr","isdat","defvalue","jstype","lsstype","ord") values ('11','Множественный список','0','0','0','0','0','','multilist','multilist','110');
insert into "sysfieldtype" ("id","name","isid","isref","isdec","isstr","isdat","defvalue","jstype","lsstype","ord") values ('12','Дата и время','0','0','0','0','1','null','datetime','datetime','55');

insert into "sysconfig" ("code", "val")
select 'dbversion','0'
where
	not exists(
		select * from "sysconfig" where "code"='dbversion'
	);

PRAGMA foreign_keys = ON;

• имена таблиц и полей - маленькие буквы английского алфавита или цифры
• соглашения на уникальный идентификатор таблицы:
  • у каждой таблицы должен быть уникальный идентификатор, с именем id, по нему первичный ключ
  • обычно он автоинкрементный, тип integer, значения строго положительные. Такие ключи объявляются как:

    "id" integer primary key

• соглашения на объявление ссылок:
  • ссылка должна иметь тип, соответствующий типу id таблицы, на которую она ссылается. Таким образом обычно это integer
  • для наглядности имя ссылки в конце содержит сочетание символов 'id', например: «machineid» - ссылка на таблицу «machine»
  • на уровне базы данных ссылки объявляются как nullable
  • на примере таблицы machinerepair, объявление каскадной ссылки:

    "machineid" bigint references "machine" on delete cascade deferrable initially deferred

    Объявление restrict ссылки:

    "repairid" bigint references "repair" deferrable initially deferred

    у restrict ссылки никаких дополнительных описаний на on delete не пишем, это позволяет отнести проверку на конец транзакции

  • по каждой ссылке делаем индекс, именуем его как idx_<имя таблицы>_<имя поля>
• соглашения на объявление полей datetime:
  • не используем часовые пояса
  • обычно поля для хранения даты и времени nullable

"datetime1" timestamp,
"datetime2" timestamp default now()

• соглашения на nullable полей:
  • если у поля признак not null, то должно быть объявление default
  • для типов: число, строка, boolean - поля объявляем как not null, default
  • для типов: ссылка, дата - поля объявляем как nullable, в случае необходимости проверку на заполнение реализуем на слое бизнес логики
• проверка уникальности реализуется уникальным индексом, с условием id>0 и именем uidx_<имя таблицы>_<имя>