| Предыдущая версия справа и слеваПредыдущая версияСледующая версия | Предыдущая версия |
| lss-server-php:database-postgresql [2025/12/14 17:29] – [Создание и заполнение содержимым системных таблиц] madmin | lss-server-php:database-postgresql [2025/12/19 12:36] (текущий) – удалено madmin |
|---|
| ====== Создание структуры базы данных ====== | |
| В настоящий момент серверная платформа 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 проекта. | |
| |
| Этот скрипт необходимо прогнать на пустой БД. | |
| |
| ==== содержимое скрипта ==== | |
| <code> | |
| -- Создание и инициализация системных таблиц для сервера 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'); | |
| </code> | |
| ===== Создание структуры БД для пользовательских таблиц ===== | |
| Вот пример скрипта создания пользовательских таблиц для тестового примера: | |
| ==== содержимое скрипта ==== | |
| <code> | |
| -- роль доступа | |
| 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;</code> | |
| ==== важные замечания ==== | |
| * все таблицы размещаются в схеме **public** | |
| * имена таблиц и полей - маленькие буквы английского алфавита или цифры | |
| * единственное исключение, отсутствующее в этом примере - поля начинающиеся с символа '~'. Есть соглашение, что такие поля содержат служебную информацию, рассчитываемую алгоритмами и используются для оптимизации частых SQL запросов. | |
| * соглашения на уникальный идентификатор таблицы: | |
| * у каждой таблицы должен быть уникальный идентификатор, с именем **id**, по нему первичный ключ | |
| * обычно он автоинкрементный, тип **bigint**, значения строго положительные. Такие ключи объявляются как: <code>"id" bigserial primary key</code> | |
| * соглашения на объявление ссылок: | |
| * ссылка должна иметь тип, соответствующий типу **id** таблицы, на которую она ссылается. Таким образом обычно это **bigint** | |
| * на уровне базы данных ссылки объявляются как **nullable** | |
| * срабатывание **constraint** ссылки должно быть отнесено на конец транзакции, соответственно: <code>deferrable initially deferred</code> Это соглашение имеет исторические корни, возможно в будущем от него будем отказываться, но пока для единообразия используем | |
| * на примере таблицы **machinerepair**, объявление **каскадной** ссылки: <code>"machineid" bigint references "machine" on delete cascade deferrable initially deferred</code> Объявление **restrict** ссылки: <code>"repairid" bigint references "repair" deferrable initially deferred</code> у restrict ссылки никаких дополнительных описаний на **on delete** не пишем, это позволяет отнести проверку на конец транзакции | |
| * по каждой ссылке делаем индекс, именуем его как **idx_<имя таблицы>_<имя поля>** | |
| * соглашения на **nullable** полей: | |
| * если у поля признак **not null**, то должно быть объявление **default** | |
| * для типов: число, строка, boolean - поля объявляем как **not null**, **default** | |
| * для типов: ссылка, дата - поля объявляем как **nullable**, в случае необходимости проверку на заполнение реализуем на слое бизнес логики | |
| |
| |
