Как правильно обновить id после удаления записи

@КАВ · Регистрация: 16.04.2016

Студворк — интернет-сервис помощи студентам

Всех приветствую. У меня есть таблица,в которой первичный ключ не autoincrement rowid,поэтому я сам создал колонку rowid:

SQL
1
CREATE TABLE IF NOT EXISTS  favourite_posts (ROWID INTEGER,id INTEGER,name TEXT,DATE TEXT,author TEXT,content TEXT,url TEXT,PRIMARY KEY(id),UNIQUE(ROWID))

Я не делал rowid autoincrement,т.к я хочу,чтобы id были упорядоченными,а autoincrement за этим не следит. К примеру,после удаления поста с rowid 2 rowid будут равны 1 и 3,чего мне бы не хотелось. После удаления поста я пытаюсь обновить таблицу так:

SQL
1
UPDATE TABLE favourite_posts SET ROWID =ROWID-1 WHERE ROWID >deleted_row_id

Но,к примеру,при попытке удалить пост с rowid 1 и обновить rowid для значений 2,3 я получаю ошибку типа unique constraint,поскольку,как я понимаю,sqlite вначале пытается обновить rowid со значением 3,заменив его на 2,но ведь значение 2 уже есть. Подскажите пожалуйста,как изменить порядок обновления колонки ROWID,или как наиболее оптимально обновить записи в бд после удаления записи. Понимаю,что многие скажут мне,что-то типа того,я занимаюсь ерундой,но во-первых мне будет приятнее,чтобы roid был упорядоченным,а во-вторых я прочитал,что limit с offset менее оптимальны (а они мне будут нужны в запросах) чем выбор по условию where. Я могу гарантировать,что будет удаляться только один пост (хотя я не откажусь,если мне кто-то напишет запрос,который будет работать,если мы удалим сразу несколько постов) и что следующий ROWID всегда на 1 больше предыдущего. Заранее всех благодарю за помощь.

@КАВ · 20.12.2023, 19:12 **[ТС]**

Не то чтоб быстрее, просто его выполнение будет размазано между действиями пользователя и затеряется

Что значит будет размазано и затеряется?

через ";" см https://www.sqlite.org/lang_createtrigger.html

Значит мне всё же придётся делать два триггера,т.к всё что после ; android framework игнорирует.

Он там не явный, сама таблица выстроена в дерево по Rowid? может поэтому и пишет что ищет в таблице а не индексе

Т.е хоть индекс явно не пишется,это всё равно будет работать быстрее,чем scan table?

@Swa111 · 21.12.2023, 07:16

Что значит будет размазано и затеряется?

То что удаляться будет вместе со связкой в одно и то же время. незначительно увеличивая время удаления. при этом для пользователя это будет не заметно

Значит мне всё же придётся делать два триггера,т.к всё что после ; android framework игнорирует.

Ни когда не разрабатывал на андроид но мб Вы сможете подготовить бд отдельно а потом подложить в приложение как ассет?

Т.е хоть индекс явно не пишется,это всё равно будет работать быстрее,чем scan table?

Да. поиск по rowid или др. колонке задекларированной как integer primary key будет работать достаточно быстро

@КАВ · 22.12.2023, 01:59 **[ТС]**

Ни когда не разрабатывал на андроид но мб Вы сможете подготовить бд отдельно а потом подложить в приложение как ассет?

Могу но,на мой взгляд,это не имеет смысла. К тому же вроде в sqlite нельзя создавать триггер для нескольких таблиц. Кстати я сделал триггер по Вашему образцу,но у меня появляется синтаксическая ошибка near end; (поле в таблице с категориями и в ассоциативной таблице называется одинаково id).

SQL
1
CREATE TRIGGER IF NOT EXISTS post_category_relationship_trigger after DELETE ON post_category_relationship WHEN (NOT EXISTS(SELECT 1 FROM post_category_relationship WHERE id=OLD.id)) BEGIN DELETE FROM categories WHERE id=OLD.id END;

@Swa111 · 22.12.2023, 07:43

Не хватает ; после выражение delete

SQL
1
2
3
4
CREATE TRIGGER IF NOT EXISTS post_category_relationship_trigger after DELETE ON post_category_relationship WHEN (NOT EXISTS(SELECT 1 FROM post_category_relationship WHERE id=OLD.id)) 
BEGIN 
  DELETE FROM categories WHERE id=OLD.id;
END;

К тому же вроде в sqlite нельзя создавать триггер для нескольких таблиц.

Нельзя, триггер, обслуживает только одну таблицу, самих триггеров может быть множество

@Usaga · 22.12.2023, 08:00

А зачем тут вообще триггер? Запросом обычным всё это никак нельзя сделать?

@Swa111 · 22.12.2023, 08:04

Я понимаю, что лень прочитать предыдущие 3 страницы. Если в кратце - можно.

@КАВ · 22.12.2023, 23:36 **[ТС]**

Теперь вроде триггер работает,хотя в запросе была точка с запятой в середине запроса (вроде на developers.android.com было написано,что поддерживается 1 statement). Вы писали,что в sqlite триггеры связаны с одной таблицей,но перед этим Вы писали,что можно назначить триггер на удаление постов и из категорий,и из тегов (я про ассоциативные таблицы),или я Вас не правильно понял,и нужно использовать два триггера,как это работает сейчас? Кстати меня гложат сомнения,может вместо id в некоторых местах триггера нужно использовать table_name.id,или в моём случае всё нормально? Когда я создал индекс для id в ассоциативной таблице с категориями,то теперь тоже нет пересканирования таблиц. Но теперь появилась другая засада,которая не связана с индексами ��,о которой я не подумал. Когда я перехожу на следующую страницу,то я выполняю запрос для получения следующей порции постов:

SQL
1
SELECT id,name,DATE FROM FAVOURITE_POSTS WHERE ROWID >(SELECT ROWID FROM favourite_posts WHERE id=latest_post_id) LIMIT page_size

И всё отрабатывает отлично,но когда я хочу вернутся на предыдущую страницу и выполняю запрос:

SQL
1
SELECT id,name,DATE FROM FAVOURITE_POSTS WHERE ROWID <(SELECT ROWID FROM favourite_posts WHERE id=first_post_id) LIMIT page_size

То у меня возвращаются первые page_size поста,что и не удивительно,т.е как будто я вернулся на первую страницу. Теперь мне надо как-то исхитрится и вернуть page_size последние поста,при этом,если у меня сортировка по возрастанию,чтобы колонка rowid возрастала,а если по убыванию - убывала. Неужели мне снова придётся вернуться к offset и забыть про оптимизацию,или есть способ получше?

@КАВ · 23.12.2023, 18:19 **[ТС]**

Может в sqlite есть встроенная функция,которая решает мою задачу ведь,при сортировке по убыванию и при перелистывании назад,меня тоже ждёт такая же проблема.

@Аватар · 23.12.2023, 18:55

юзер по ходу у тебя один и база не правится при просмотре. тогда при листании вперед запоминай в массив например ид от которого плясать на каждой странице . его и используй при листании назад хоть до самой 1-ой страницы.

@КАВ · 23.12.2023, 23:49 **[ТС]**

Я тоже про это думал,но если у меня будет много страниц,то массив может получиться большой,да и запрос может усложнится,хотя с моей идеей он тоже усложнится. Моя идея в том,чтобы при листании назад в подзапросе сортировать строки по убыванию (если сортировка по убыванию,то по возрастанию),и делать limit page_size,а в основном запросе сортировать результат по возрастанию/по убыванию (зависит от сортировки). Очень жаль,если в sqlite нет простой функции,чтобы быстро добиться того,что я хочу.

@Аватар · 24.12.2023, 00:23

Сообщение от КАВ

будет много страниц,то массив может получиться большой

это расчет на то, что будут сотни-тысячи страниц и юзер часами будет пялится на экран листая туда и обратно? сомнительно что это будет кем то серьезно восстребовано.. да и массив на 10000 идешек это меньше 40 кбайт. кого сейчас это пугает на устройствах с гигами оперативы?

@КАВ · 24.12.2023, 18:23 **[ТС]**

да и массив на 10000 идешек это меньше 40 кбайт.

Это Вы имеете ввиду 4 (размер long)*10000. Но массив это объект,а значит размер может быть больше,хотя,наверное,не на много.

кого сейчас это пугает на устройствах с гигами оперативы?

Да,Вы правы. На устройствах с гигами оперативы будет стоять только одно моё приложение,при этом там будет такой android,который будет очень мало потреблять оперативной памяти. А если серьёзно,то если никто не подскажет более лучшего решения,придётся так и поступить,т.е делать большой массив,что мне бы лично делать не хотелось,но за не имением лучшего,придётся так поступить.

@Swa111 · 24.12.2023, 21:12

Теперь вроде триггер работает,хотя в запросе была точка с запятой в середине запроса (вроде на developers.android.com было написано,что поддерживается 1 statement).

Не знаю как в андроид но в SQLite внутри create trigger может быть несколько команд при этом вся конструкция считается одним статментом.

Вы писали,что в sqlite триггеры связаны с одной таблицей,но перед этим Вы писали,что можно назначить триггер на удаление постов и из категорий,и из тегов (я про ассоциативные таблицы),или я Вас не правильно понял,и нужно использовать два триггера,как это работает сейчас?

На каждую таблицу свой триггер

Кстати меня гложат сомнения,может вместо id в некоторых местах триггера нужно использовать table_name.id,или в моём случае всё нормально?

Можете и table_name.id использовать, но так как таблица одна то можно не уточнять откуда колонка.

И всё отрабатывает отлично,но когда я хочу вернутся на предыдущую страницу и выполняю запрос:

То у меня возвращаются первые page_size поста,что и не удивительно,т.е как будто я вернулся на первую страницу. Теперь мне надо как-то исхитрится и вернуть page_size последние поста,при этом,если у меня сортировка по возрастанию,чтобы колонка rowid возрастала,а если по убыванию - убывала. Неужели мне снова придётся вернуться к offset и забыть про оптимизацию,или есть способ получше?

Нужно доабавить сортировку

SQL
1
SELECT id,name,DATE FROM FAVOURITE_POSTS WHERE ROWID >(SELECT ROWID FROM favourite_posts WHERE id=latest_post_id) ORDER BY rowid LIMIT page_size

SQL
1
SELECT id,name,DATE FROM FAVOURITE_POSTS WHERE ROWID <(SELECT ROWID FROM favourite_posts WHERE id=first_post_id) ORDER BY rowid DESC LIMIT page_size

@КАВ · 24.12.2023, 23:23 **[ТС]**

Можете и table_name.id использовать, но так как таблица одна то можно не уточнять откуда колонка.

SQL
1
WHEN (NOT EXISTS(SELECT 1 FROM post_category_relationship WHERE id=OLD.id))

А после begin

SQL
1
DELETE FROM categories WHERE id=OLD.id

Как sqlite понимает,что id берётся именно из таблицы categories,или та таблица,которая используется в from и есть та таблица для sqlite,из которой нужно брать id? Вдруг я хочу взять id с post_category_relationship? Такой же вопрос у меня,если использовать подзапросы select,или объединять таблицы через join? В общем когда в sqlite явно нужно указывать имя таблицы,чтобы указать имя столбца,а когда это делать необязательно?

SQL
1
(SELECT ROWID FROM favourite_posts WHERE id=first_post_id) ORDER BY rowid DESC LIMIT page_size

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

@Swa111 · 25.12.2023, 07:09

Как sqlite понимает,что id берётся именно из таблицы categories,или та таблица,которая используется в from и есть та таблица для sqlite,из которой нужно брать id? Вдруг я хочу взять id с post_category_relationship? Такой же вопрос у меня,если использовать подзапросы select,или объединять таблицы через join? В общем когда в sqlite явно нужно указывать имя таблицы,чтобы указать имя столбца,а когда это делать необязательно?

Да, поле ищется в таблице в from. Если поле уникально даже при соединении нескольких таблиц, то имя таблицы можно не указывать. Если есть конфликт то запрос не выполнится. С exists не много сложнее, сначала поле ищется а таблице коррелирующего запроса потом в таблицах основного запроса. таким образом если есть одноименные поля, то указание таблицы обязательно. Что бы не сильно вникать когда нужно а когда нет, то рекомендуют указывать таблицы всегда.

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

Пересортируйте как нужно

SQL
1
2
3
4
5
6
SELECT id,name,DATE FROM (
  SELECT rowid,id,name,DATE 
  FROM FAVOURITE_POSTS 
  WHERE ROWID <(SELECT ROWID FROM favourite_posts WHERE id=first_post_id) 
  ORDER BY rowid DESC LIMIT page_size
) AS t ORDER BY rowid

@КАВ · 25.12.2023, 15:18 **[ТС]**

таким образом если есть одноименные поля, то указание таблицы обязательно.

Т.е в exists в триггере в моём мне нужно обязательно указать имя таблицы,или здесь это не обязательно? А какой конфликт может возникнуть с join,т.е что запрос может не выполнится?

Пересортируйте как нужно

Наверное тогда лучше запоминать rowid,как писали до этого,т.к получается вначале мне нужно пересортировать все записи по убыванию,даже которые мне не нужны,а потом выбрать из них n нужных записей и заново их пересортировать. Хотя Вы же вроде писали про какую-то функцию,которая позволит получить номер текущей записи,возвращаемой в запросе. Если эта функция реально есть,можно просто добавить в условие проверку,чтобы разность размера записей,который мне вернул запрос,и номера записи не превышал размера страницы,т.е сколько записей я хочу получить. Хотя,поскольку android framework поддерживает 1 statement,значит реализовать такое,наверное,будет непросто.

@Swa111 · 25.12.2023, 17:51

Т.е в exists в триггере в моём мне нужно обязательно указать имя таблицы,или здесь это не обязательно? А какой конфликт может возникнуть с join,т.е что запрос может не выполнится?

Если имя поля уникально в соединении нескольких таблиц или если таблица одна, то СУБД сама определит к какой таблице относится поле. Если несколько таблиц в запросе имеют одноименные поля, то указание таблицы или ее алиаса обязательно, в противном случае запрос не выполнится (будет ошибка). Если Вам трудно разобраться где нужно ставить, а где нет, то ставьте везде, ошибки от этого не будет.

Наверное тогда лучше запоминать rowid,как писали до этого,т.к получается вначале мне нужно пересортировать все записи по убыванию,даже которые мне не нужны,а потом выбрать из них n нужных записей и заново их пересортировать.

Реальная сортировка будет только одна (последняя на page_size строк). Первая скажет СУБД сканировать индекс в обратном направлении и отобрать page_size записей.

Хотя Вы же вроде писали про какую-то функцию,которая позволит получить номер текущей записи,возвращаемой в запросе.

Это Вам не поможет так как требует сортировки не явной.

@КАВ · 25.12.2023, 23:43 **[ТС]**

Если несколько таблиц в запросе имеют одноименные поля, то указание таблицы или ее алиаса обязательно, в противном случае запрос не выполнится (будет ошибка).

Т.е при всём желании я не смогу перепутать таблицы,колонки которых я хочу получить?

Реальная сортировка будет только одна (последняя на page_size строк). Первая скажет СУБД сканировать индекс в обратном направлении и отобрать page_size записей.

Это по Вашему опыту,или это задокументировано в sqlite? Т.е sqlite определяет,что вначале не нужно ничего пересортировывать? Интересно,у меня это отразится в explain query plan? И это будет работать также,когда я захочу использовать сортировку по убыванию,заменив где нужно asc на desc и наоборот,а значит вообще не будет разницы,если у меня,к примеру в бд 5000 записей,или всего одна запись,а разница будет только от размера страницы? Кстати в начальном запросе я могу использовать select *,т.к в подзапросе я указываю,что я выбираю три поля.

@Swa111 · 26.12.2023, 07:16

Т.е при всём желании я не смогу перепутать таблицы,колонки которых я хочу получить?

если указывать таблицы перед именами колонок то не перепутаете.

Это по Вашему опыту,или это задокументировано в sqlite? Т.е sqlite определяет,что вначале не нужно ничего пересортировывать?

см тут. Если есть возможность отказаться от сортировки используя индекс то СУБД не будет делать сортировку. Для этого нужно построить запрос так что бы и выборка и сортировка использовала один и тот же индекс.

Интересно,у меня это отразится в explain query plan?

Скорее всего это можно увидеть через консоль sqlite с включенной сборкой статистки , там будет показано сколько строк в каждой ноде плана было прочитано

И это будет работать также,когда я захочу использовать сортировку по убыванию,заменив где нужно asc на desc и наоборот,а значит вообще не будет разницы,если у меня,к примеру в бд 5000 записей,или всего одна запись,а разница будет только от размера страницы?

Да, если выборка и сортировка использовала один и тот же индекс.

Кстати в начальном запросе я могу использовать select *,т.к в подзапросе я указываю,что я выбираю

Да, это не должно повлиять на план

@КАВ · 26.12.2023, 15:40 **[ТС]**

если указывать таблицы перед именами колонок то не перепутаете.

Это понятно,но я про то,что в случае одинаковых таблиц sqlite выдаст ошибку.

Если есть возможность отказаться от сортировки используя индекс то СУБД не будет делать сортировку. Для этого нужно построить запрос так что бы и выборка и сортировка использовала один и тот же индекс.

Да,я прочитал,что sqlite будет использовать индекс,правда не очень понятно,как это будет работать. Вот в distinct чётко написано,что будет сравниваться следующая строка колонки с предыдущей,а вот про сортировку об этом вроде не пишется,по крайней мере в соответствующем пункте,но я готов поверить,что это работает так,как Вы написали. Как понять,что будет использоваться один и тот же индекс,т.е это будет верно всегда,когда мы выбираем колонку column,при этом сортируем по column и у нас есть простой индекс по column,или составной индекс,где column первая колонка,или есть ещё какие-то моменты? Кстати я давно ещё думал,можно ли как-то sqlite подсказать,что моя ROWID будет строго больше нуля. Да,есть constraint,но я не уверен,что sqlite будет как-то оптимизировать,зная их. Просто в этом случае sqlite будет выдавать ошибку,если я выйду за пределы этого ограничения. Если бы такая возможность была,я бы смог указать sqlite,что ROWID >0,а поскольку это первичный ключ,а значит следующее значение будет всегда больше предыдущего,у sqlite,на мой взгляд,вообще не было бы проблем с сортировкой по этой колонке,а значит принцип был бы такой же,как у distinct.

Добавлено через 25 минут
Нет,тут я не прав. Даже если я укажу то,что значение больше нуля,никто не гарантирует,что,к примеру,после двойки не будет единицы (если двойка первая). Поэтому хорошо было бы,если бы в sqlite можно было бы указать,что следующее значение будет строго больше предыдущего.

Новые блоги и статьи Все статьи Все блоги /
Поиск всех путей на ориентированном графе. Linux dcc0 02.07.2026 Переработка старого кода из моей статьи. Через несколько переработок от PHP кода к C89 (надеюсь, 89). Но довольно запутанно получилось. Код для Linux. Но если убрать time и то, что с ним. . .	Сам себя обучал rest api anaschu 02.07.2026 Педагогический лайфхак: Почему чистый REST API для ученика намного круче, чем готовые библиотеки Когда мы отказались от капризного JAR-файла AnyLogic и переписали код на стандартный HttpClient,. . .	rest api anylogic - выполнение модели на своём русском сайте anaschu 02.07.2026 Как подружиться с AnyLogic Cloud API, победить провайдеров и развернуться Java-бэкенд в Docker на бесплатном хостинге: Двухдневный лог борьбы Всем привет! Хочу поделиться свежим (и довольно. . .	Где деньги лежат kumehtar 02.07.2026 Это - японская подводная лодка I-52 (тип C2, кодовое имя Momi) вышла из Японии в марте 1944 года с миссией в оккупированную немцами Францию (Лорьян). Это была одна из «Янаги»-миссий по обмену. . .
Krabik для WoW 3.3.5a, многоязычный AmbA 02.07.2026 Допилил бота, думаю что окончательно. Изменения: - добавлена многоязычность - добавлено снятие скриншотов - добавлено поддержание бафов хождения по воде (для жреца, дк и шамана) - и так, по. . .	Алиса нашла кучу ошибок компиляции и запуска в проекте, который без проблем компилировался и запускался))) anaschu 30.06.2026 Я пока посмеюся, но завтра проверю. А вообще интерсно. Дал алисе файл, в котором точно нет ошибок компиляции и запуска, и попросил их найти. Нашла кучу))) Критические ошибки, мешающие компиляции и. . .	сукцессия 16. Общий обзор, в основном что бы другие ии поняли anaschu 29.06.2026 # Передаточный документ: модель микоризной сукцессии (для нового чата) Этот документ предназначен для того, чтобы новый чат Claude мог продолжить работу без необходимости заново разбираться в. . .	сукцессия 15 неявная схема anaschu 29.06.2026 Алиса Калибровка параметров симбиотической модели: технический обзор Содержание: Введение Постановка проблемы Технические аспекты реализации Процесс внедрения изменений

@Usaga 14364 / 9465 / 1360 Регистрация: 21.01.2016 Сообщений: 35,689
	22.12.2023, 08:00
	А зачем тут вообще триггер? Запросом обычным всё это никак нельзя сделать? 0

@Swa111 919 / 292 / 58 Регистрация: 01.06.2023 Сообщений: 816
	22.12.2023, 08:04
	Я понимаю, что лень прочитать предыдущие 3 страницы. Если в кратце - можно. 0

@КАВ 17 / 5 / 0 Регистрация: 16.04.2016 Сообщений: 344
	23.12.2023, 18:19 [ТС]
	Может в sqlite есть встроенная функция,которая решает мою задачу ведь,при сортировке по убыванию и при перелистывании назад,меня тоже ждёт такая же проблема. 0

@Аватар 5393 / 1465 / 513 Регистрация: 31.05.2012 Сообщений: 5,153
	23.12.2023, 18:55
	юзер по ходу у тебя один и база не правится при просмотре. тогда при листании вперед запоминай в массив например ид от которого плясать на каждой странице . его и используй при листании назад хоть до самой 1-ой страницы. 0

@КАВ 17 / 5 / 0 Регистрация: 16.04.2016 Сообщений: 344
	23.12.2023, 23:49 [ТС]
	Я тоже про это думал,но если у меня будет много страниц,то массив может получиться большой,да и запрос может усложнится,хотя с моей идеей он тоже усложнится. Моя идея в том,чтобы при листании назад в подзапросе сортировать строки по убыванию (если сортировка по убыванию,то по возрастанию),и делать limit page_size,а в основном запросе сортировать результат по возрастанию/по убыванию (зависит от сортировки). Очень жаль,если в sqlite нет простой функции,чтобы быстро добиться того,что я хочу. 0