В версии DBeaver 23.3.4.202402060628 у меня есть следующий код SQLite3 (для высокоскоростной машины для литья под давлением деталей и машины для отслеживания упаковок на заводе):
--------------------------------------------------------------------------------
-- SQLite TABLE
DROP TABLE IF EXISTS pkgdata;
CREATE TABLE pkgdata (
DateTime TEXT, -- source format = "DD.MM.YYYY HH:mm:ss.sss"
Weight REAL,
Height REAL,
Boxes INT,
Parts INT,
Spread REAL GENERATED ALWAYS AS (Weight - Height) STORED,
gen_pkg_year INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 7, 4)) STORED,
gen_pkg_month INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 4, 2)) STORED,
gen_pkg_day INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 1, 2)) STORED,
gen_pkg_wkday INT GENERATED ALWAYS AS (strftime('%w',DateTime)) STORED,
gen_pkg_per TEXT GENERATED ALWAYS AS (strftime('%p',DateTime)) STORED,
gen_pkg_hr INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 12, 2)) STORED,
gen_pkg_min INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 15, 2)) STORED,
gen_pkg_sec INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 18, 2)) STORED,
gen_pkg_ms INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 21, 3)) STORED,
gen_ISODate TEXT GENERATED ALWAYS AS (
printf('%04d-%02d-%02d %02d:%02d:%02d.%03d',
gen_pkg_year, gen_pkg_month, gen_pkg_day,
gen_pkg_hr, gen_pkg_min, gen_pkg_sec, gen_pkg_ms)
) STORED,
prev_Weight REAL,
prev_Height REAL,
Weight_diff REAL,
Height_diff REAL,
Weight_move TEXT,
Height_move TEXT,
gap TEXT
);
--------------------------------------------------------------------------------
-- SQLite INDICES - created one line at a time
CREATE INDEX pkg_idx_ymd ON pkgdata (gen_pkg_year, gen_pkg_month, gen_pkg_day);
CREATE INDEX pkg_idx_mon ON pkgdata (gen_pkg_month);
CREATE INDEX pkg_idx_dom ON pkgdata (gen_pkg_day);
CREATE INDEX pkg_idx_dow ON pkgdata (gen_pkg_wkday);
CREATE INDEX pkg_idx_per ON pkgdata (gen_pkg_per);
CREATE INDEX pkg_idx_hr ON pkgdata (gen_pkg_hr);
CREATE INDEX pkg_idx_min ON pkgdata (gen_pkg_min);
--------------------------------------------------------------------------------
-- SQLite TRIGGER
DROP TRIGGER IF EXISTS pkg_tr_fill_calculated_columns;
CREATE TRIGGER pkg_tr_fill_calculated_columns
AFTER INSERT ON pkgdata
BEGIN
UPDATE pkgdata
SET prev_Weight = previous.Weight,
prev_Height = previous.Height,
Weight_diff = NEW.Weight - previous.Weight,
Height_diff = NEW.Height - previous.Height,
Weight_move = CASE
WHEN Weight > prev_Weight THEN 'H'
WHEN Weight = prev_Weight THEN 'S'
WHEN Weight < prev_Weight THEN 'L'
END,
Height_move = CASE
WHEN Height > prev_Height THEN 'H'
WHEN Height = prev_Height THEN 'S'
WHEN Height < prev_Height THEN 'L'
END,
gap = CASE
WHEN Height > prev_Weight THEN 'U'
WHEN Weight < prev_Height THEN 'D'
ELSE ''
END
FROM (SELECT * FROM pkgdata
ORDER BY gen_ISODate DESC
LIMIT 1, 1) AS previous -- get the previous row
WHERE pkgdata.DateTime = NEW.DateTime;
END;
--------------------------------------------------------------------------------
-- SQLite INSERT DATA
INSERT INTO pkgdata (
DateTime, -- TEXT
Weight, -- REAL
Height, -- REAL
Boxes, -- INT
Parts) -- INT
VALUES
('01.01.2010 00:00:00.817',0.89755,0.89725,0,0),
('01.01.2010 00:00:01.157',0.89753,0.89728,0,0),
('01.01.2010 00:00:01.457',0.89755,0.89725,0,0),
('01.01.2010 00:00:01.737',0.89755,0.89725,0,0),
('01.01.2010 00:00:02.417',0.89755,0.89725,0,0),
('01.01.2010 00:00:02.747',0.8975,0.8973,0,0),
('01.01.2010 00:00:06.295',0.89755,0.89725,0,0),
('01.01.2010 00:00:06.566',0.89758,0.89723,0,0),
('01.01.2010 00:00:06.866',0.89755,0.89725,0,0),
('01.01.2010 00:00:09.985',0.89755,0.89725,0,0),
('01.01.2010 00:00:10.325',0.89755,0.89725,0,0),
('01.01.2010 00:00:11.055',0.89755,0.89725,0,0),
('01.01.2010 00:00:12.385',0.89755,0.89725,0,0),
('01.01.2010 00:00:12.604',0.8975,0.8973,0,0),
('01.01.2010 00:00:12.884',0.89755,0.89725,0,0),
('01.01.2010 00:00:13.114',0.89758,0.89723,0,0),
('01.01.2010 00:00:15.283',0.89758,0.89723,0,0),
('01.01.2010 00:00:15.514',0.8975,0.8973,0,0),
('01.01.2010 00:00:15.684',0.8975,0.8973,0,0),
('01.01.2010 00:00:17.083',0.8975,0.8973,0,0),
('01.01.2010 00:00:17.693',0.8975,0.8973,0,0)
returning *;
У меня был рабочий код, но теперь я получаю сообщение об ошибке, когда дохожу до оператора INSERT
(DBeaver принимает каждый блок):
Ошибка SQL 1: [SQLITE_ERROR] Ошибка SQL или отсутствующая база данных (неоднозначное имя столбца: Вес)
Существует только одна таблица с одним столбцом с именем Weight
. Из этого поста я подумал, что что-то еще есть в памяти, поэтому отключил все остальные базы данных, удалил все таблицы из этой и начал с нуля. Я попытался использовать полные имена, добавив имя таблицы:
INSERT INTO pkgdata (
pkgdata.DateTime, -- TEXT
pkgdata.Weight, -- REAL
pkgdata.Height, -- REAL
pkgdata.Boxes, -- INT
pkgdata.Parts) -- INT
VALUES
...
Но затем выдает синтаксическую ошибку для точки.
Итак, теперь вы знаете, что проблема в триггере. Затем начните отладку обновления в нем как отдельный запрос (используя фиктивные значения для полей старой строки).
Вот это в скрипте БД: dbfiddle.uk/SSk1oWVf. Он принимает синтаксис триггера. Я не совсем уверен, как это отладить. Мне помогли его построить (из другого моего поста). Не могли бы вы объяснить, что вы подразумеваете под «отладкой обновления как отдельного запроса»?
«… но теперь я получаю сообщение об ошибке, когда дохожу до оператора
INSERT
…»
Триггер AFTER INSERT
в своем событии UPDATE
не может различить вставленную (до события триггера) и обновленную (после события триггера) связанную строку во всех именах столбцов каждого предложения WHEN
в каждом из трех выражений CASE
, отсюда и ошибка «неоднозначное имя столбца».
«DBeaver принимает каждый блок»
Это не приводит к синтаксической ошибке при выдаче CREATE TRIGGER
, поскольку само выражение CASE допустимо (только в контексте триггерного события оно становится неоднозначным).
«Я пытался использовать полные имена, добавляя к началу имя таблицы: … Но затем в точке выдается синтаксическая ошибка».
Это ожидаемо.
Добавьте в начало имена столбцов с предполагаемой ссылкой на событие триггера до или после (NEW.column-name
или OLD.column-name
) в каждом предложении WHEN
в каждом из выражений UPDATE
события триггера CASE
. Кроме того, рассмотрите возможность использования виртуальных столбцов (или вычислений, которые их генерируют) в предложениях CASE
выражений WHEN
, а не отдельно SET
этих данных в одном и том же триггерном UPDATE
событии, вычисления которого от него зависят (избыточно дублируясь в процессе).
Исправленное утверждение TRIGGER
:
CREATE TRIGGER pkg_tr_fill_calculated_columns
AFTER INSERT ON pkgdata
BEGIN
UPDATE pkgdata
SET prev_Weight = previous.Weight,
prev_Height = previous.Height,
Weight_diff = NEW.Weight - previous.Weight,
Height_diff = NEW.Height - previous.Height,
Weight_move = CASE
WHEN NEW.Weight > previous.Weight THEN 'H'
WHEN NEW.Weight = previous.Weight THEN 'S'
WHEN NEW.Weight < previous.Weight THEN 'L'
END,
Height_move = CASE
WHEN NEW.Height > previous.Height THEN 'H'
WHEN NEW.Height = previous.Height THEN 'S'
WHEN NEW.Height < previous.Height THEN 'L'
END,
gap = CASE
WHEN NEW.Height > previous.Weight THEN 'U'
WHEN NEW.Weight < previous.Height THEN 'D'
ELSE ''
END
FROM (SELECT * FROM pkgdata
ORDER BY gen_ISODate DESC
LIMIT 1, 1) AS previous -- get the previous row
WHERE pkgdata.DateTime = NEW.DateTime;
END;
Исправлено dbfiddle.
Судя по тому, что я читал, виртуальные столбцы генерируются только в памяти и возвращаются в качестве результатов запроса — я хочу, чтобы они постоянно хранились в таблице, поскольку они никогда не изменятся. Я знаю, что это означает больше дискового пространства для хранения БД. Я планирую провести статистический анализ этих данных в режиме реального времени. Если они сохранены, мне не придется пересчитывать их снова и снова. Я хочу вычислить их один раз во время записи, а затем больше никогда.
Хорошо, я обновился и создал таблицу и индексы, но не триггер, и INSERT сработал. Но мне нужен триггер для вычисляемых столбцов — мне нужно, чтобы вычисляемые столбцы вычислялись при вставке каждой строки. Это данные временных рядов, и это очень важно.