| Предыдущая версия справа и слеваПредыдущая версияСледующая версия | Предыдущая версия |
| lss-server-php:db-datastructure-sqlite [2026/06/15 16:07] – [содержимое скрипта создания системных таблиц] madmin | lss-server-php:db-datastructure-sqlite [2026/06/15 16:28] (текущий) – [SQlite - требования к структуре БД] madmin |
|---|
| ====== SQlite - требования к структуре БД ====== | ====== SQlite - требования к структуре БД ====== |
| | В настоящий момент полноценно реализована поддержка серверов PostgreSql и SQlite. В этом документе описаны требования к **SQlite**, а для PostgreSql требования можно посмотреть здесь: [[db-datastructure-postgresql]] |
| | |
| ===== Создание и инициализация системных таблиц ===== | ===== Создание и инициализация системных таблиц ===== |
| Для корректной работы LSS сервера в базе данных должны присутствовать системные таблицы: | Для корректной работы LSS сервера в базе данных должны присутствовать системные таблицы: |
| create unique index "uidx_sysdbversion_code" on "sysdbversion" ("code") where "id">0; | create unique index "uidx_sysdbversion_code" on "sysdbversion" ("code") where "id">0; |
| </code> | </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_<имя таблицы>_<имя>** |
| | |
| | |
