LSS Техническая документация

Эта страница только для чтения. Вы можете посмотреть её исходный текст, но не можете его изменить. Сообщите администратору, если считаете, что это неправильно.
====== SQlite - требования к структуре БД ======
В настоящий момент полноценно реализована поддержка серверов PostgreSql и SQlite. В этом документе описаны требования к **SQlite**, а для PostgreSql требования можно посмотреть здесь: [[db-datastructure-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 проекта.

Эти скрипты необходимо последовательно прогнать на пустой БД.
==== содержимое скрипта создания системных таблиц ====
<code>
-- Создание системных таблиц для сервера 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;
</code>

==== содержимое скрипта инициализации содержимого системных таблиц ====

<code>
-- Инициализация системных таблиц для сервера 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;
</code>
===== Создание структуры БД для пользовательских таблиц =====
Вот пример скрипта создания пользовательских таблиц из тестового примера:
==== содержимое скрипта ====
<code>
-- роль доступа
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;
</code>
==== важные замечания ====
  * имена таблиц и полей - маленькие буквы английского алфавита или цифры
  * соглашения на уникальный идентификатор таблицы:
    * у каждой таблицы должен быть уникальный идентификатор, с именем **id**, по нему первичный ключ
    * обычно он автоинкрементный, тип **integer**, значения строго положительные. Такие ключи объявляются как: <code>"id" integer primary key</code>
  * соглашения на объявление ссылок:
    * ссылка должна иметь тип, соответствующий типу **id** таблицы, на которую она ссылается. Таким образом обычно это **integer**
    * для наглядности имя ссылки в конце содержит сочетание символов 'id', например: "machineid" - ссылка на таблицу "machine"
    * на уровне базы данных ссылки объявляются как **nullable**
    * на примере таблицы **machinerepair**, объявление **каскадной** ссылки: <code>"machineid" integer references "machine" on delete cascade</code> Объявление **restrict** ссылки: <code>"repairid" integer references "repair" on delete restrict</code>
    * по каждой ссылке делаем индекс, именуем его как **idx_<имя таблицы>_<имя поля>**
  * соглашения на объявление полей **datetime**:
    * обычно поля для хранения даты и времени nullable, без привязки к часовым поясам
<code>
"datetime1" text,
"datetime2" text not null default (datetime('now', 'localtime')),
</code>
  * соглашения на **nullable** полей:
    * если у поля признак **not null**, то должно быть объявление **default**
    * для типов: число, строка, boolean - поля объявляем как **not null**, **default**
    * для типов: ссылка, дата - поля объявляем как **nullable**, в случае необходимости проверку на заполнение реализуем на слое бизнес логики
  * проверка уникальности реализуется уникальным индексом, с условием **id>0** и именем **uidx_<имя таблицы>_<имя>**