Различия

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

--- lss-server-php:db-datastructure-postgresql [2026/03/31 11:44] – [содержимое скрипта] madmin
+++ lss-server-php:db-datastructure-postgresql [2026/04/28 12:23] (текущий) – [важные замечания] madmin
@@ Строка 13: / Строка 13: @@
   * процедура удаления view **cmd_dropviews** - позволяет удалить view из БД, мешающие внесению изменений в структуру таблицы.
-Для их создания и корректной начальной инициализации служит скрипт pgsql-systable.sql, расположенный в папке /sql/init шаблона LSS проекта.
+Для их создания и корректной начальной инициализации служат скрипты **pgsql-systable-create.sql** и **pgsql-systable-init.sql**, расположенные в папке **/sql/init** шаблона LSS проекта.
-Этот скрипт необходимо прогнать на пустой БД.
+Эти скрипты необходимо последовательно прогнать на пустой БД.
-==== содержимое скрипта ====
+==== содержимое скрипта создания системных таблиц ====
 <code>
--- Создание и инициализация системных таблиц для сервера PostgreSql
+-- Создание системных таблиц для сервера PostgreSql, для инициализации служит скрипт systable-init.sql
 -- Типы полей
@@ Строка 153: / Строка 153: @@
 	"syssessionid" bigint references "syssession" on delete cascade deferrable initially deferred,
 	"code" varchar(64) not null default '',
-	"value" text,
+	"value" text not null default '',
-	"params" text,
+	"params" text not null default '',
 	"dcreate" timestamp not null default now(),
 	"json" jsonb
@@ Строка 174: / Строка 174: @@
 );
 create unique index "uidx_sysdbversion_code" on "sysdbversion" ("code");
-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")));
 -- автоинкрементный генератор для
@@ Строка 234: / Строка 221: @@
 end;
 $$;
+</code>
+==== содержимое скрипта инициализации содержимого системных таблиц ====
+<code>
+-- Инициализация системных таблиц для сервера PostgreSql, для создания служит скрипт systable-create.sql
+SET session_replication_role = replica;
+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 "sysfieldtype" ("id","name","isid","isref","isdec","isstr","isdat","defvalue","jstype","lsstype","ord") values ('13','Время','0','0','0','0','1','null','time','time','57');
+select setval('sysfieldtype_id_seq', GREATEST(1,(select max(id) from "sysfieldtype")));
-insert into "sysconfig" ("code", "val") values ('dbversion','0');
+insert into "sysconfig" ("code", "val")
+select 'dbversion','0'
+where
+	not exists(
+		select * from "sysconfig" where "code"='dbversion'
+	);
 </code>
 ===== Создание структуры БД для пользовательских таблиц =====
@@ Строка 352: / Строка 365: @@
     * на примере таблицы **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_<имя таблицы>_<имя поля>**
+  * соглашения на объявление полей **datetime**:
+    * не используем часовые пояса
+    * обычно поля для хранения даты и времени nullable
+<code>
+"datetime1" timestamp,
+"datetime2" timestamp default now()
+</code>
   * соглашения на **nullable** полей:
     * если у поля признак **not null**, то должно быть объявление **default**