SQlite - требования к структуре БД

В настоящий момент полноценно реализована поддержка серверов PostgreSql и SQlite. В этом документе описаны требования к SQlite, а для PostgreSql требования можно посмотреть здесь: Postgresql - требования к структуре БД

Для корректной работы 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;

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

-- роль доступа
create table "role" (
	"id" integer 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" integer primary key,
	"login" varchar(64) not null default '',
	"password" varchar(255) not null default '',
	"fio" varchar(255) not null default '',
	"roleid" integer references "role" on delete restrict
);
create index "idx_user_roleid" on "user" ("roleid");
create unique index "uidx_user_login" on "user" ("login") where "id">0;

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

-- участок
create table "sector" (
	"id" integer primary key,
	"departmentid" integer references "department" on delete cascade,
	"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" integer primary key,
	"name" varchar(255) not null default ''
);
create unique index "uidx_machinetype_name" on "machinetype" ("name") where "id">0;

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

-- вид ремонта
create table "repair" (
	"id" integer 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" integer primary key,
	"dfrom" date,
	"invnum" varchar(64) not null default '',
	"machinetypeid" integer references "machinetype" on delete restrict,
	"model" varchar(64) not null default '',
	"departmentid" integer references "department" on delete restrict,
	"sectorid" integer references "sector" on delete restrict,
	"dstatus" date,
	"machinestatusid" integer references "machinestatus" on delete restrict
);
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" integer primary key,
	"machineid" integer references "machine" on delete cascade,
	"repairid" integer references "repair" on delete restrict,
	"dplan" date,
	"dfact" date
);
create index "idx_machinerepair_machineid" on "machinerepair" ("machineid");
create index "idx_machinerepair_repairid" on "machinerepair" ("repairid");

-- нормы ремонтов
create table "repairnorm" (
	"id" integer primary key,
	"repairid" integer references "repair" on delete cascade,
	"machinetypeid" integer references "machinetype" on delete cascade,
	"period" integer 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;

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

    "id" integer primary key

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

    "machineid" integer references "machine" on delete cascade

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

    "repairid" integer references "repair" on delete restrict

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

"datetime1" text,
"datetime2" text not null default (datetime('now', 'localtime')),

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