Различия

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

--- lss-server-php:db-datastructure-sqlite [2026/06/15 16:05] – [Создание и инициализация системных таблиц] madmin
+++ lss-server-php:db-datastructure-sqlite [2026/06/15 16:28] (текущий) – [SQlite - требования к структуре БД] madmin
@@ Строка 1: / Строка 1: @@
 ====== SQlite - требования к структуре БД ======
+В настоящий момент полноценно реализована поддержка серверов PostgreSql и SQlite. В этом документе описаны требования к **SQlite**, а для PostgreSql требования можно посмотреть здесь: [[db-datastructure-postgresql]]
 ===== Создание и инициализация системных таблиц =====
 Для корректной работы LSS сервера в базе данных должны присутствовать системные таблицы:
@@ Строка 14: / Строка 16: @@
 ==== содержимое скрипта создания системных таблиц ====
 <code>
--- Создание системных таблиц для сервера PostgreSql, для инициализации служит скрипт systable-init.sql
+-- Создание системных таблиц для сервера sqlite, для инициализации служит скрипт sqlite-systable-init.sql
 -- Типы полей
 create table "sysfieldtype" (
-	"id" bigserial primary key,
+	"id" integer primary key,
 	"name" varchar(255) not null default '',
 	"isid" boolean not null default '0',
@@ Строка 28: / Строка 30: @@
 	"jstype" varchar(255) not null default '',
 	"lsstype" varchar(255) not null default '',
-	"ord" bigint not null default 0
+	"ord" integer not null default 0
 );
 -- Категории таблиц
 create table "systablecategory" (
-	"id" bigserial primary key,
+	"id" integer primary key,
 	"name" varchar(255) not null default '',
-	"ord" bigint not null default 0
+	"ord" integer not null default 0
 );
 -- Таблицы
 create table "systable" (
-	"id" bigserial primary key,
+	"id" integer primary key,
-	"klssystablecategory" bigint references "systablecategory" on delete cascade deferrable initially deferred,
+	"klssystablecategory" integer references "systablecategory" on delete cascade,
 	"tablename" varchar(255) not null default '',
 	"name" varchar(255) not null default '',
@@ Строка 58: / Строка 60: @@
 -- Поля
 create table "sysfield" (
-	"id" bigserial primary key,
+	"id" integer primary key,
-	"klssystable" bigint references "systable" on delete cascade deferrable initially deferred,
+	"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" bigint references "sysfieldtype" deferrable initially deferred,
+	"klssysfieldtype" integer references "sysfieldtype" on delete restrict,
 	"isnotempty" boolean not null default '0',
 	"ismain" boolean not null default '0',
@@ Строка 70: / Строка 72: @@
 	"dec" integer not null default 0,
 	"isstretch" boolean not null default '0',
-	"klsreftable" bigint references "systable" deferrable initially deferred,
+	"klsreftable" integer references "systable" on delete restrict,
 	"reflink" varchar(255) not null default '',
 	"isrefrestrict" boolean not null default '0',
@@ Строка 78: / Строка 80: @@
 	"islog" boolean not null default '0',
 	"isvirtual" boolean not null default '0',
-	"ord" bigint not null default 0
+	"ord" integer not null default 0
 );
 create index "idx_sysfield_klssystable" on "sysfield" ("klssystable");
@@ Строка 86: / Строка 88: @@
 -- Параметры полей
 create table "sysfieldparams" (
-	"id" bigserial primary key,
+	"id" integer primary key,
-	"klssysfield" bigint references "sysfield" on delete cascade deferrable initially deferred,
+	"klssysfield" integer references "sysfield" on delete cascade,
 	"name" varchar(255) not null default '',
 	"val" text not null default ''
@@ Строка 95: / Строка 97: @@
 -- Ограничения, для руссификации сообщений об ошибках
 create table "sysconstraint" (
-	"id" bigserial primary key,
+	"id" integer primary key,
-	"klssystable" bigint references "systable" on delete cascade deferrable initially deferred,
+	"klssystable" integer references "systable" on delete cascade,
 	"constraintname" varchar(255) not null default '',
 	"message" text not null default ''
@@ Строка 104: / Строка 106: @@
 -- Системный лог фоновых процессов
 create table "sysextlog" (
-	"id" bigserial primary key,
+	"id" integer primary key,
 	"d" date,
 	"tstart" varchar(5) not null default '',
@@ Строка 115: / Строка 117: @@
 -- История правки данных
 create table "sysdblog" (
-	"id" bigserial primary key,
+	"id" integer primary key,
 	"child" varchar(36) not null default '',
 	"childid" varchar(36) not null default '',
@@ Строка 134: / Строка 136: @@
 -- Хранение основных данных сессии в БД
 create table "syssession" (
-	"id" bigserial primary key,
+	"id" integer primary key,
 	"code" varchar(128) not null,
 	"value" text not null default '',
-	"dcreate" timestamp not null default now(),
+	"dcreate" text not null default (datetime('now', 'localtime')),
-	"dlast" timestamp not null default now()
+	"dlast" text not null default (datetime('now', 'localtime'))
 );
 create index "idx_syssession_code" on "syssession" ("code");
@@ Строка 145: / Строка 147: @@
 -- Хранение дополнительных данных сессии в БД
 create table "syssessioninfo" (
-	"id" bigserial primary key,
+	"id" integer primary key,
-	"syssessionid" bigint references "syssession" on delete cascade deferrable initially deferred,
+	"syssessionid" integer references "syssession" on delete cascade,
 	"code" varchar(64) not null default '',
 	"value" text not null default '',
 	"params" text not null default '',
-	"dcreate" timestamp not null default now(),
+	"dcreate" text not null default (datetime('now', 'localtime')),
-	"json" jsonb
+	"json" text
 );
 create index "idx_syssessioninfo_syssessionid" on "syssessioninfo" ("syssessionid","code");
@@ Строка 157: / Строка 159: @@
 -- Системные конфигурации площадки
 create table "sysconfig" (
-	"id" bigserial primary key,
+	"id" integer primary key,
 	"code" varchar(32) not null default '',
 	"val" varchar(64) not null default ''
 );
-alter table "sysconfig" add unique("code") deferrable initially deferred;
+create unique index "uidx_sysconfig_code" on "sysconfig" ("code") where "id">0;
 -- Системный лог выполнения версион скриптов
 create table "sysdbversion" (
-	"id" serial primary key,
+	"id" integer primary key,
 	"code" varchar(64) not null default ''
 );
-create unique index "uidx_sysdbversion_code" on "sysdbversion" ("code");
+create unique index "uidx_sysdbversion_code" on "sysdbversion" ("code") where "id">0;
+</code>
--- автоинкрементный генератор для
+==== содержимое скрипта инициализации содержимого системных таблиц ====
-create sequence sysappend_id_seq AS bigint;
-select setval('sysappend_id_seq', 100000);
--- хранимая процедура удаления view
+<code>
--- вызов call cmd_dropviews('user') - удалить все view, использующие таблицу user
+-- Инициализация системных таблиц для сервера sqlite, для создания служит скрипт sqlite-systable-create.sql
--- вызов call cmd_dropviews() - удалить все view
-create or replace procedure cmd_dropviews(tablename varchar(255) default '')
+PRAGMA foreign_keys = OFF;
-language plpgsql
+delete from "sysfieldtype";
-as $$
+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');
-declare
+insert into "sysfieldtype" ("id","name","isid","isref","isdec","isstr","isdat","defvalue","jstype","lsstype","ord") values ('2','Строка','0','0','0','1','0','','edit','string','20');
-	rec record;
+insert into "sysfieldtype" ("id","name","isid","isref","isdec","isstr","isdat","defvalue","jstype","lsstype","ord") values ('3','Текст','0','0','0','1','0','','memo','memo','30');
-	cmd varchar(1024);
+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');
-begin
+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');
-	if tablename='' then
+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');
-		for rec in
+insert into "sysfieldtype" ("id","name","isid","isref","isdec","isstr","isdat","defvalue","jstype","lsstype","ord") values ('7','Ссылка','0','1','0','0','0','','ref','ref','70');
-			select distinct view_name
+insert into "sysfieldtype" ("id","name","isid","isref","isdec","isstr","isdat","defvalue","jstype","lsstype","ord") values ('8','Список','0','0','0','0','0','','list','list','80');
-			from
+insert into "sysfieldtype" ("id","name","isid","isref","isdec","isstr","isdat","defvalue","jstype","lsstype","ord") values ('9','Иконки','0','0','0','0','0','','icons','icons','90');
-				information_schema.view_table_usage
+insert into "sysfieldtype" ("id","name","isid","isref","isdec","isstr","isdat","defvalue","jstype","lsstype","ord") values ('10','Радиогруппа','0','0','0','0','0','','radio','radio','100');
-			where
+insert into "sysfieldtype" ("id","name","isid","isref","isdec","isstr","isdat","defvalue","jstype","lsstype","ord") values ('11','Множественный список','0','0','0','0','0','','multilist','multilist','110');
-				view_schema='public' and
+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');
-				table_schema='public' and
-				substring(view_name,1,5)='view_'
+insert into "sysconfig" ("code", "val")
-		loop
+select 'dbversion','0'
-			cmd=concat('drop view "',rec.view_name,'"');
+where
-			raise info '%', cmd;
+	not exists(
-			execute cmd;
+		select * from "sysconfig" where "code"='dbversion'
-		end loop;
+	);
-	else
-		for rec in
+PRAGMA foreign_keys = ON;
-			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;
-$$;
 </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_<имя таблицы>_<имя>**