Оптимальный способ хранения в БД последовательности объектов...

[Cyrax]

Каждая запись таблицы содержит информацию о некотором объекте. Все эти объекты упорядочены. Т.е. в базе данных необходимо хранить информацию о порядке следования объектов.
Один из вариантов - в каждой записи хранить идентификатор предыдущего объекта. Но в этом случае довольно долго получать объект по его номеру, особенно, когда объектов в таблице много.
Другой вариант - вместо идентификатора родителя указывать порядковый номер объекта. Здесь возникает проблема изменения порядковых номеров всех следующих объектов при добавлении или удалении текущего объекта (также долго при большом числе объектов).

Есть ли более эффективные способы хранения в БД последовательности ?

[Tonal]

Главный вопрос - зачем нужна и откуда берётся эта упорядоченность? На что она влияет?
Что должно происходить при удалении объекта? А при добавлении? Что при изменении?
Нужно ли предусмотреть пакетное добавление/изменение/удаление?

От ответов на эти вопросы напрямую зависит дизайн. :-)

[Cyrax]

При удалении некоторого объекта номера всех следующих за ним декрементируются, при добавлении нового объекта на i-ю позицию номера всех объектов, начиная с i-й инкрементируются.
Т.е. все объекты, содержащиеся в таблице, должны быть упорядочены и каждый объект должен соответствовать некоторому номеру. (Соответствовать - значит должна быть возможность по номеру получить объект.) Максимальный номер соответствует количеству объектов в таблице. И каждому номеру, меньшему максимального, должен соответствовать некоторый объект.

Частые операции с такой последовательностью объектов:
1. Добавление нового объекта на i-ю позицию (все остальные объекты смещаются на одну позицию вперёд)
2. Удаление i-го объекта
3. Перемещение некоторого объекта с i-й на j-ю позицию

[Tonal]

Ещё раз.
Зачем нужны эти номера и эта упорядоченность?
Какие бизнес-задачи с её помощью решаются?
Эти номера, это некие хенделы, которые отдаются стороннему процессу (как тот же PID в никсе или HWND в винде)?
Или у тебя для каждого номера какие-то ресурсы зарезервированы  или железяки?
Или это номера официальных документов?
Или юзер мышкой подвигал, и хочет чтобы на завтра всё в таком же положении и осталось?
Или ещё что-то?

Для всех этих альтернатив решения могут существенно различаться, т.к. Но все они подходят под твой ответ.

[Cyrax]

Это нужно только для отображения пользователю. Причём порядок и состав отображаемых объектов определяется не желанием пользователя, а некоторыми утверждёнными наверху документами. Соответственно, для всех пользователей состав и порядок объектов будет один и тот же. Изменяется же состав и порядок лицами, имеющими достаточный уровень прав...

[Tonal]

Тогда добавь поле индекса и не мучайся.
Сортируй по нему выборку - это и будет твой порядок.
При удалении просто можно ничего не делать.
При добавлении - перенумеровываешь все с нужного индекса (1 запрос) потом вставляешь новую запись.
Обмен тоже элементарно делается.

Да, я бы, в административном интерфейсе, где порядок меняется, сделал просто:
Пользователь меняет порядок на форме как угодно, а по кнопке сохранить в базу скопом записывается новый порядок.

[Cyrax]

Тогда добавь поле индекса и не мучайся.

PK с порядковым номером объекта ?

Сортируй по нему выборку - это и будет твой порядок.

При удалении просто можно ничего не делать.

Это если я все объекты махом читаю. Но если мне нужно по индексу (номеру) прочитать объект, то получу кашу, поскольку при удалении не декрементирую все последующие индексы (номера).
Напрашивается и при удалении, и при добавлении изменять индексы последующих объектов.

При добавлении - перенумеровываешь все с нужного индекса (1 запрос) потом вставляешь новую запись.

Если поле уникально (PK), то одним запросом вряд ли обойтись...

[Tonal]

PK - порядковый номер - это круто! :-)))

PK, обычно делают генерящимся самой базой и пользователям никак не показывают - дабы не провоцировать. Почитай доки, например на ibase.ru - там где то было про ключи.
Да и вообще почитай доки.

Ты так и не сказал, зачем тебе нужно обращение по порядковому номеру.
Если как PK - то не делай так - будет больно. :-)
Если тебе нужно не все, а упорядоченный кусок, то в SQL-е есть соответствующие инструкции в select-е (FIRST, SKIP для Firebird-а).

Ну а для перенумеровки действительно 1 запрос (параметр :index):

Код:

update table1 t set t.num = t.num+1
where t.num >= (
  select first 1 t2.num from (
    select first :index t3.num from table1 t3 order by num
  ) t2
  order by t2.num desc
)

[Cyrax]

PK - порядковый номер - это круто!

По всякому делают. Не надо так удивляться. PK без автоинкремента тоже делают. И БД их не генерирует автоматически...

Ты так и не сказал, зачем тебе нужно обращение по порядковому номеру.

Вполне может пригодиться.
К примеру, модель данных в Qt (обеспечивая свои представления объектами) может при изменении объектов в БД другим пользователем обновить свои локальные копии, считав из БД только изменённые объекты (поддержка актуальности).
Либо модель данных может извлекать из БД объекты синхронно с запросами со стороны своих представлений, не храня их (объектов) локальные копии.

Если тебе нужно не все, а упорядоченный кусок, то в SQL-е есть соответствующие инструкции в select-е (FIRST, SKIP для Firebird-а).

Во всех запросах желательно обойтись стандартным SQL, поскольку предполагается возможность работы как с MySQL, так и с SQL Server'ом...

По поводу запроса - почему не так:

Код:

update table1 t set t.num = t.num+1
where t.num >= :index

где :index - номер нового объекта.
Судя по всему, под :index ты понимаешь не порядковый номер объекта...

[Tonal]

PK - порядковый номер - это круто!

По всякому делают. Не надо так удивляться. PK без автоинкремента тоже делают. И БД их не генерирует автоматически...

При определённых условиях можно таблицу и без PK сделать. Но ты можешь привести внятные  обоснования этому?

К тому же я не удивляюсь - видел и не такое. Просто смешно. Так что я должен сказать тебе спасибо - поднял настроение.

Ты так и не сказал, зачем тебе нужно обращение по порядковому номеру.

Вполне может пригодиться.

Почему бы не привязать к каждому объекту по воздушному шарику?
Может пригодиться. А представь как будет выглядеть сервер - кайф!

К примеру, модель данных в Qt (обеспечивая свои представления объектами) может при изменении объектов в БД другим пользователем обновить свои локальные копии, считав из БД только изменённые объекты (поддержка актуальности).
Либо модель данных может извлекать из БД объекты синхронно с запросами со стороны своих представлений, не храня их (объектов) локальные копии.

Здесь ты говоришь о идентификации объектов. Обычно, это решается неизменными первичными ключами, генерирующимися на сервере. Все современные движки бд поддерживают это в том или другом виде.
Любое другое решение требует очень серьёзного обоснования.

Если тебе нужно не все, а упорядоченный кусок, то в SQL-е есть соответствующие инструкции в select-е (FIRST, SKIP для Firebird-а).

Во всех запросах желательно обойтись стандартным SQL, поскольку предполагается возможность работы как с MySQL, так и с SQL Server'ом...

Это довольно разные сервера. С разной поддержкой стандарта.
Или придётся использовать сильно ограниченное подмножество SQL, или писать свои запросы под каждый сервер.

По поводу запроса - почему не так:

Код:

update table1 t set t.num = t.num+1
where t.num >= :index

где :index - номер нового объекта.
Судя по всему, под :index ты понимаешь не порядковый номер объекта...

Именно порядковый номер - мой запрос правильно отработает даже в том случае, если в нумерации есть дырки. а твой, только если нумерация непрерывна

Короче не смешивай несвязанные вещи - проще будет.

[Cyrax]

По поводу настроения: хорошо, если оно у тебя сохранится таким к концу ветки...

При определённых условиях можно таблицу и без PK сделать. Но ты можешь привести внятные  обоснования этому?

В моей БД 5 таблиц имеют PK из двух полей, 5 - из 3 и 1 - из 4. Т.е. половина всех таблиц не имеют PK, генерируемых самой БД. Собственно, если приведёшь везкие доводы в пользу введения автоинкрементируемых одиночных PK, то соглашусь...

Почему бы не привязать к каждому объекту по воздушному шарику?
Может пригодиться. А представь как будет выглядеть сервер - кайф!

Это не серьёзно. Это же не экзотика какая-то. Возможность получения только i-го объекта подразумевается самой предметной областью. Хоть в данный момент эта возможность и не требуется, но незначительные изменения в функциональности клиентской программы могут потребовать (для обеспечения эффективности) поддержку этой возможности базой данных.
К примеру, при добавлении возможности редактирования свойств объекта не в общей таблице (где отображаются все объекты), а на отдельной форме напрашивается запрос только одного объекта, номер которого, скажем, указывает пользователь, а не программа определяет по PK.
Да и других похожих ситуаций может быть уйма, которые завтра-послезавтра, возможно, нужно будет реализовать.
Изменение же только клиентского приложения всегда проще и безопаснее, чем изменение базы данных...

Здесь ты говоришь о идентификации объектов. Обычно, это решается неизменными первичными ключами, генерирующимися на сервере. Все современные движки бд поддерживают это в том или другом виде.

Имеешь ввиду репликацию ?

Это довольно разные сервера. С разной поддержкой стандарта.
Или придётся использовать сильно ограниченное подмножество SQL, или писать свои запросы под каждый сервер.

В первом случае можно будет работать с любой современной SQL-СУБД. Того подмножества SQL, возможно, будет и достаточно.

Именно порядковый номер - мой запрос правильно отработает даже в том случае, если в нумерации есть дырки. а твой, только если нумерация непрерывна

Да, но если я на поле num поставлю unique, запрос работать не будет. А уникальными номера должны быть...

[Tonal]

Это же твоя БД. Я не знаю задач, которые ты решаешь, поэтому не могу привести "везкие" доводы. :-)

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

В любом случае - выбор за тобой.

[Cyrax]

Tonal, как одним запросом проинкрементировать уникальные поля ?

[Tonal]

Я не большой знаток синтаксиса мускула и скруля, но можно попробовать как-то так:

Код:

update table1 t
right join (
  select t4.id from table1 t4 where t4.num >= (
    select first 1 t2.num from (
      select first :index t3.num from table1 t3 order by num
    ) t2
    order by t2.num desc
  ) order by num desc) t5 on t5.id = t.id
set t.num = t.num + 1

Вроде на каком-то из них я это видел, но оно не стандартно.

На Firebird можно использовать execute block:

Код:

execute block(:index integer)
declare 
as
begin
  for select t.id from table1 t where t.num >= (
    select first 1 t2.num from (
      select first :index t3.num from table1 t3 order by num
    ) t2
    order by t2.num desc
  ) into :id as cursor cur do
    update table1 t set t.num = t.num + 1 where current of cur;
end

Ну и на любом из них можно написать ХП - тоже один запрос.

[WW]

2Cyrax:
Поверьте, если возникают такие задачи - значит где-то ошибка в проектировании. БД НЕ ДОЛЖНА выполнять ТАКИЕ функции. Это задачи чисто клиента. БД должна хранить данные и уметь манипулировать ими быстро и качественно. Представь ситуацию: пользователей N*1000 и все они активно изменяют данные (insert + delete). Вы представляете КАК будет тормозить Ваша программа?
Я Вам неоднократно предлагал изучить теорию БД, в т.ч. и про КЛАСТЕРНЫЕ индексы.
Если Вы требуете от БД делать вещи, которые ей не свойственны - ищите ошибку в проектировании системы.
Есть HeaderView, которые автоматом нумеруют ваши строки, а есть кластерный индекс, который, в общем случае, гарантирует порядок следования записей в таблице (отличается в реализации разных вендоров), на крайняк - есть order by по этому самому полю.
Не делайте этого!!! Просто представьте, как вас будут ругать те, кто будет потом поддерживать Ваше решение и пытаться его масштабировать.