В настоящий момент серверная платформа LSS лучше всего адаптирована для совместной работы с SQL сервером Postgresql. На его примере и рассмотрим создание структуры БД.

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

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

и дополнительные структуры:

• автоинкрементный генератор sysappend_id_seq - используется для генерации временных отрицательных id при выполнении операции append.
• процедура удаления view cmd_dropviews - позволяет удалить view из БД, мешающие внесению изменений в структуру таблицы.

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

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

-- Создание и инициализация системных таблиц для сервера PostgreSql

-- Типы полей
create table "sysfieldtype" (
	"id" bigserial 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" bigint not null default 0
);

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

-- Таблицы
create table "systable" (
	"id" bigserial primary key,
	"klssystablecategory" bigint references "systablecategory" on delete cascade deferrable initially deferred,
	"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" bigserial primary key,
	"klssystable" bigint references "systable" on delete cascade deferrable initially deferred,
	"fieldname" varchar(255) not null default '',
	"name" varchar(255) not null default '',
	"info" text not null default '',
	"klssysfieldtype" bigint references "sysfieldtype" on delete restrict deferrable initially deferred,
	"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" bigint references "systable" on delete restrict deferrable initially deferred,
	"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" bigint 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" bigserial primary key,
	"klssysfield" bigint references "sysfield" on delete cascade deferrable initially deferred,
	"name" varchar(255) not null default '',
	"val" text not null default ''
);
create index "idx_sysfieldparams_klssysfield" on "sysfieldparams" ("klssysfield");

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

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

-- История правки данных
create table "sysdblog" (
	"id" bigserial 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" bigserial primary key,
	"code" varchar(128) not null,
	"value" text,
	"dcreate" timestamp not null default now(),
	"dlast" timestamp not null default now()
);
create index "idx_syssession_code" on "syssession" ("code");
create index "idx_syssession_dlast" on "syssession" ("dlast");

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

-- Системные конфигурации площадки
create table "sysconfig" (
	"id" bigserial primary key,
	"code" varchar(32) not null default '',
	"val" varchar(64) not null default ''
);
alter table "sysconfig" add unique("code") deferrable initially deferred;

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');
select setval('sysfieldtype_id_seq', GREATEST(1,(select max(id) from "sysfieldtype")));

-- автоинкрементный генератор для 
create sequence sysappend_id_seq AS bigint;
select setval('sysappend_id_seq', 100000);

-- хранимая процедура удаления view
-- вызов call cmd_dropviews('user') - удалить все view, использующие таблицу user
-- вызов call cmd_dropviews() - удалить все view
create or replace procedure cmd_dropviews(tablename varchar(255) default '')
language plpgsql
as $$
declare
	rec record;
	cmd varchar(1024);
begin
	if tablename='' then
		for rec in
			select distinct view_name
			from
				information_schema.view_table_usage
			where
				view_schema='public' and
				table_schema='public' and
				substring(view_name,1,5)='view_'
		loop
			cmd=concat('drop view "',rec.view_name,'"');
			raise info '%', cmd;
			execute cmd;
		end loop;
	else
		for rec in
			select distinct view_name
			from
				information_schema.view_table_usage
			where
				view_schema='public' and
				table_schema='public' and
				table_name=tablename and
				substring(view_name,1,5)='view_'
		loop
			cmd=concat('drop view "',rec.view_name,'"');
			raise info '%', cmd;
			execute cmd;
		end loop;
	end if;
end;
$$;

insert into "sysconfig" ("code", "val") values ('dbversion','0');

Вот пример скрипта создания пользовательских таблиц из тестового примера:

-- роль доступа
create table "role" (
	"id" bigserial primary key,
	"code" varchar(64) not null default '',
	"name" varchar(255) not null default ''
);
create unique index "uidx_role_code" on "role" ("code") where "id">0;
create unique index "uidx_role_name" on "role" ("name") where "id">0;

-- пользователь системы
create table "user" (
	"id" bigserial primary key,
	"login" varchar(64) not null default '',
	"password" varchar(255) not null default '',
	"fio" varchar(255) not null default '',
	"roleid" bigint references "role" deferrable initially deferred
);
create index "idx_user_roleid" on "user" ("roleid");
create unique index "uidx_user_login" on "user" ("login") where "id">0;

-- цех
create table "department" (
	"id" bigserial primary key,
	"name" varchar(255) not null default ''
);
create unique index "uidx_department_name" on "department" ("name") where "id">0;

-- участок
create table "sector" (
	"id" bigserial primary key,
	"departmentid" bigint references "department" on delete cascade deferrable initially deferred,
	"name" varchar(255) not null default ''
);
create index "idx_sector_departmentid" on "sector" ("departmentid");
create unique index "uidx_sector_unique" on "sector" ("departmentid", "name") where "id">0;

-- тип оборудования
create table "machinetype" (
	"id" bigserial primary key,
	"name" varchar(255) not null default ''
);
create unique index "uidx_machinetype_name" on "machinetype" ("name") where "id">0;

-- состояние оборудования
create table "machinestatus" (
	"id" bigserial primary key,
	"name" varchar(255) not null default ''
);
create unique index "uidx_machinestatus_name" on "machinestatus" ("name") where "id">0;

-- вид ремонта
create table "repair" (
	"id" bigserial primary key,
	"name" varchar(255) not null default '',
	"npp" int not null default 0
);
create unique index "uidx_repair_name" on "repair" ("name") where "id">0;

-- паспорт оборудования
create table "machine" (
	"id" bigserial primary key,
	"dfrom" date,
	"invnum" varchar(64) not null default '',
	"machinetypeid" bigint references "machinetype" deferrable initially deferred,
	"model" varchar(64) not null default '',
	"departmentid" bigint references "department" deferrable initially deferred,
	"sectorid" bigint references "sector" deferrable initially deferred,
	"dstatus" date,
	"machinestatusid" bigint references "machinestatus" deferrable initially deferred
);
create index "idx_machine_machinetypeid" on "machine" ("machinetypeid");
create index "idx_machine_departmentid" on "machine" ("departmentid");
create index "idx_machine_sectorid" on "machine" ("sectorid");
create index "idx_machine_machinestatusid" on "machine" ("machinestatusid");
create unique index "uidx_machine_invnum" on "machine" ("invnum") where "id">0;

-- ремонт оборудования
create table "machinerepair" (
	"id" bigserial primary key,
	"machineid" bigint references "machine" on delete cascade deferrable initially deferred,
	"repairid" bigint references "repair" deferrable initially deferred,
	"dplan" date,
	"dfact" date
);
create index "idx_machinerepair_machineid" on "machinerepair" ("machineid");
create index "idx_machinerepair_repairid" on "machinerepair" ("repairid");

-- нормы ремонтов
create table "repairnorm" (
	"id" bigserial primary key,
	"repairid" bigint references "repair" on delete cascade deferrable initially deferred,
	"machinetypeid" bigint references "machinetype" on delete cascade deferrable initially deferred,
	"period" bigint not null default 0
);
create index "idx_repairnorm_repairid" on "repairnorm" ("repairid");
create index "idx_repairnorm_machinetypeid" on "repairnorm" ("machinetypeid");
create unique index "uidx_repairnorm_unique" on "repairnorm" ("machinetypeid","repairid") where "id">0;

• все таблицы размещаются в схеме public
• имена таблиц и полей - маленькие буквы английского алфавита или цифры
  • единственное исключение, отсутствующее в этом примере - поля начинающиеся с символа '~'. Есть соглашение, что такие поля содержат служебную информацию, рассчитываемую алгоритмами и используются для оптимизации частых SQL запросов.
• соглашения на уникальный идентификатор таблицы:
  • у каждой таблицы должен быть уникальный идентификатор, с именем id, по нему первичный ключ
  • обычно он автоинкрементный, тип bigint, значения строго положительные. Такие ключи объявляются как:

    "id" bigserial primary key

• соглашения на объявление ссылок:
  • ссылка должна иметь тип, соответствующий типу id таблицы, на которую она ссылается. Таким образом обычно это bigint
  • на уровне базы данных ссылки объявляются как nullable
  • срабатывание constraint ссылки должно быть отнесено на конец транзакции, соответственно:

    deferrable initially deferred

    Это соглашение имеет исторические корни, возможно в будущем от него будем отказываться, но пока для единообразия используем

  • на примере таблицы machinerepair, объявление каскадной ссылки:

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

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

    "repairid" bigint references "repair" deferrable initially deferred

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

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