Коррелированный подзапрос (или эквивалент) в операторе SQLite UPDATE?

У меня есть база данных SQLite3, которая для оптимизации производительности использует вычисляемые столбцы, обновляемые триггерами.

Теперь я пытаюсь добавить триггер, аналогичный этому (непроверенному, но, вероятно, действительному) коду ORM SQLAlchemy.

story.read_free = any(link.link_type.read_free for link in story.links)

... но мне трудно понять, как выразить это как предложение UPDATE. Вот что у меня есть:

CREATE TRIGGER IF NOT EXISTS update_link_type AFTER UPDATE ON link_types
  FOR EACH ROW WHEN old.read_free <> new.read_free BEGIN
    UPDATE stories SET
      read_free = CASE WHEN (
        SELECT 1 FROM links as l, link_types as lt WHERE lt.id = new.id AND l.link_type_id = lt.id AND l.story_id = stories.id
      ) THEN 1 ELSE 0 END
    WHERE id = (SELECT story_id from links as l, link_types as lt WHERE  l.link_type_id = lt.id AND lt.id = new.id)
  ;
END;

Моя конкретная проблема заключается в том, что я не могу понять, как обеспечить корреляцию подзапроса в CASE.

Либо SQLite отвергает синтаксис (такие вещи, как UPDATE foo AS bar и UPDATE INNER JOIN ..., которые, по-видимому, используются в других БД), либо, как в примере, который я привел, он действителен, но имеет неправильное значение. (В этом случае «Установите read_free для этой истории, если существует какой-либо тип ссылки с read_free, независимо от того, есть ли в истории ссылки этого типа)

Если существует более четкая и краткая формулировка этого UPDATE, помимо простого решения проблемы, я также был бы признателен за ее знание. Даже если бы это сработало, это было бы очень уродливое решение по сравнению с худшим из остальных моих триггеров.


sql sqlite triggers
person ssokolow    schedule 27.11.2010    source источник
comment
Какова схема таблицы link_types и как выглядит запрос на обновление? Кроме того, какие столбцы вычисляются?   -  person Jay Godse    schedule 16.12.2010
comment
Обновление (помимо того, что оно не работает в его текущей форме) представляет собой большой блок кода в моем вопросе. Я не совсем уверен, что схема link_type имеет какое-то значение, кроме столбца read_free и первичного ключа с именем id, на который ссылается links.   -  person ssokolow    schedule 17.12.2010

Ответы (2)


arrow_upward
1
arrow_downward

Не могли бы вы вместо UPDATE использовать INSERT OR REPLACE? В отличие от UPDATE, INSERT OR REPLACE будет принимать встроенный SELECT, так что вы можете сделать что-то в стиле UPDATE foo AS bar или UPDATE INNER JOIN. Ваш SELECT просто создаст дубликаты строк в stories с изменением только тех столбцов, которые вам нужны.

person Robie Basak    schedule 16.12.2010
comment
Возможно нет. Я большой сторонник свободного использования ограничений CHECK и ограничений внешнего ключа для предотвращения дублирования. На самом деле, чтобы убедиться, что я могу это сделать, я исправил источник данных CakePHP SQLite3, чтобы он выдавал PRAGMA foreign_keys=ON при открытии соединения. Однако я подумаю о том, будет ли работать вторая таблица со схемой типа (story_id, read_free), JOIN, которая всегда выполняется, покрывающий индекс и OR REPLACE. - person ssokolow; 17.12.2010
comment
Я не предлагаю изменить схему или создать дубликаты данных. Я просто предлагаю использовать INSERT OR REPLACE в качестве замены UPDATE в этом конкретном запросе. Ваша база данных останется нормализованной, за исключением уже имеющихся вычисляемых столбцов. Когда я говорю дубликаты строк, я имею в виду, что INSERT OR REPLACE фактически всегда будет REPLACE, потому что вы спроектировали запрос для этого, а встроенный SELECT будет соответствовать существующим данным, поэтому он будет вести себя так же, как UPDATE, который вы хотеть. - person Robie Basak; 17.12.2010
comment
Ааа, точка. Дайте мне немного поэкспериментировать с подзапросом для сохранения/восстановления трех существующих вычисляемых столбцов (вычисленных для другой таблицы), и я дам вам знать, как это получится. - person ssokolow; 17.12.2010

arrow_upward
0
arrow_downward

Составляя предложение INSERT OR REPLACE, предложенное Роби (использование псевдонима REPLACE для упрощения любого потенциального будущего переноса на MySQL), я понял, что мой разум застрял в колее, делая неправильные предположения и чрезмерно усложняя проблему. (Возможно, я начал работать над этим, не выспавшись, и никогда не подвергал сомнению свои первоначальные выводы)

Затем я смог переформулировать свой UPDATE, чтобы потребовать только один JOIN (также не поддерживается SQLite), а затем перепишите это как подзапрос WHERE.

Вот окончательный триггер, который получился:

CREATE TRIGGER IF NOT EXISTS update_link_type AFTER UPDATE ON link_types
FOR EACH ROW WHEN old.read_free <> new.read_free BEGIN
    UPDATE stories SET read_free = new.read_free
        WHERE id IN (SELECT story_id FROM links WHERE link_type_id = new.id)
    ;
END;

Гораздо чище и гораздо удобнее в обслуживании.

Я присуждаю награду Роби по двум причинам: во-первых, потому что я бы никогда не придумал этот ответ, если бы он не выбил меня из этой колеи. Во-вторых, если бы мои требования были такими, как я изначально предполагал, его ответ был бы лучшим.

person ssokolow    schedule 17.12.2010