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

Инструменты пользователя

Инструменты сайта

Вы находитесь здесь: Платформенное решение разработки отраслевых систем "LSS" » LSS сервер » SQlite - требования к структуре БД
lss-server-php:db-datastructure-sqlite

Различия

Показаны различия между двумя версиями страницы.

Ссылка на это сравнение

Следующая версия		Предыдущая версия
lss-server-php:db-datastructure-sqlite [2026/06/15 15:57] – создано madminlss-server-php:db-datastructure-sqlite [2026/06/15 16:28] (текущий) – [SQlite - требования к структуре БД] madmin
Строка 1: Строка 1:
 ====== SQlite - требования к структуре БД ====== ====== 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_<имя таблицы>_<имя>**
 +
  
lss-server-php/db-datastructure-sqlite.1781528231.txt.gz · Последнее изменение: 2026/06/15 15:57 — madmin
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki