Developing.ru

Нужно написать код работающий с различными СУБД, кто посоветует универсальный способ генерации новых целочисленых ID в базе?

Уточню. Приложение должно работать с MS SQL Server, Oracle, MySQL. Скрипты создания базы естественно будут различаться. Код не должен определять с какой именно базой он работает.
На данный момент ID генерится случайно и происходит попытка вставить запись с этим ID, в случае неудачи - нарушения целостности первичного ключа - генерится новый случайный ID снова пытаемся вставить и так в цикле до положителного результата. Основные минусы - по мере заполнения базы все чаще будем попадать на существующие ID да и случайные числа не такие уж и случайные.
Вариант который также приходил в голову. В MS SQL использовать identity колонку, а в Оракле сделать вставку ID из сиквенса, но нужно потом одним запросом получить @@Identity или текущее значение сиквенса... с этим проблемка...
Может у кого-то уже есть решение этой проблемы и он согласен поделиться этим решением?

А чем не подходит увеличиние на единицу максимального значения ID?

chur писал(а):А чем не подходит увеличиние на единицу максимального значения ID?

Дело в том, что если делают это несколько человек сразу, может произойти облом. В Oracle это не проблема - Select for update - и всё.

А вообще рекомендуется следующее -

DDMMYY + UserID + Sequence

И Sequence вычисляется как MAX + 1.

UserID можно взять как hash_value от текущего username.

DeeJayC писал(а):Дело в том, что если делают это несколько человек сразу, может произойти облом.

Это понятно. Каждый решает это по-разному. Я имел в виду только сам принцип: MAX+1 или случайное число. К тому же, если допустимо только 4-байтное целое, то использование hash_value практически невозможно.

Вообще-то это вариант, создать таблицу типа сиквенсов и лочить запись select for update'ом и потом уж наращивать счетчик. Теоретически должно работать везде. DeeJayC, спасибо за идею.

Я имел в виду только сам принцип: MAX+1 или случайное число

вечная проблема....
ессно макс+1
но если исчерпаешь диапазон значений то придётся медленным тупым перебором.....
или написать какую-то скриптяру (а-ля дефрагментатор) которая будет все айдишки сгонять вниз периодически(тёмными тоскливыми ночами :-) ).... но! если есть форэйнкисы на эту айдишку... то надо или каскадировать апдейт по ссылающимся таблицам... или не дефрагментировать :-)
лично у меня просто мах+1. считается что не достигнет предела. работает давно и в промышленных объёмах (задолго до меня сделали :-) )
ЗЫ: :-/ поздновато написал конечно..... но просто уж сказать захотелось... вдруг кто заглянет :-)

Макс + 1 - очень плохое решение сразу по двум причинам:

1) Несколько пользователей одновременно сделают Макс+1 и начнут пытаться инсертить записи с одинаковым ключом....
2) При больших объемах данных все-таки дорогостоящая операция

Увеличение на 1 как правило наиболее оптимальный способ для SQL-серверов

Дело в том, что если делают это несколько человек сразу, может произойти облом. В Oracle это не проблема - Select for update - и всё.

В Oracle и подавляющем большинстве SQL-серверов это действительно не проблема, но не потому что там есть Select for update и прочие средства для блокировок, а потому, что там есть сиквенсы (в InterBase они называются генераторами). Все пользователи для формирования определенного ключа должны использовать определенный сиквенс. Отсутствие обломов ГАРАНТИРОВАНО на 100%. Именно это и рекомендуется!

Также весьма хороший способ - это случайное число.

вечная проблема....
ессно макс+1
но если исчерпаешь диапазон значений то придётся медленным тупым перебором.....

На счет исчерпания диапазона... Гхм, я Вас умоляю!..
Дано: Ключ типа NUMBER(15); записи добавляются круглосуточно (24х7) в среднем 1 раз в секунду.
Немного арифметики... и получается 31709791 год непрерывной работы!

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

Это использование ключа типа GUID. GUID - глобальный уникальный идентификатор - 16-байтное целое. Точного алгоритма формирования псевдослучайного GUID не знаю, но он включает в себя таймстемп, использует MAC-адрес... (в инете можно найти при желании). Алгоритм стандартизован и реализован в функции:
Вот это уж ключ так ключ.

Вернемся к исходному вопросу.

Нужно написать код работающий с различными СУБД, кто посоветует универсальный способ генерации новых целочисленых ID в базе?

Задача кажется на первый взгляд попросту нереальной. Уникальным, независящим от СУБД способом работы с данными является SQL. Однако, НИОДНА, даже самая новая спецификация ANSI на SQL не поддерживает сиквенсы, которые являются безусловно оптимальным решением данной задачи. Т.е. не имеет такой специфицированной функции как, например, COUNT, которая бы вычисляла значение ключа. В результате мы имеем в одних базах Create Sequence, в других Create Generator в третьих вообще нет такого понятия. Функции обеспечивающие доступ к следующему значению из сиквенса тоже называются по разному!

Однако у нас есть два решения:
1) GUID. Но тут могут быть проблемы с поддержкой типов данных. 16-байтное целое - это не хухры-мухры. Не у всякой СУБД есть такой тип. На худой конец можно взять CHAR(16), но ключи типа CHAR мало кто любит использовать по понятным и совершенно объективным причинам.

2) Надо задуматься: "А как же сделаны эти самые, четрт побери, сиквенсы?". И сделать свой механизм их формирования с использованием одного только DМL.
Как это сделать. Завести в базе таблицу с двумя полями KEYNAME, KEYVALUE. Для создания нового сиквенса поместить в эту таблицу запись со значением KEYVALUE=<минимально возможное в данном типе>. Вот тут внимание! Ваше приложение должно содержать ОТДЕЛЬНУЮ функцию для получения очередного значения ключа. В этой функции, в отдельной транзакции, (с уровнем изоляции ReadCommited), должны следовать две операции Update...set KEYVALUE+1 и Select KEYVALUE для заданного KEYNAME. И затем COMMIT.
Если Update сделать не удалось, значит еще не завершена транзакция другого пользователя, значит надо сделать ROLLBACK, подождать случайное количество милисекунд и попробовать еще раз.

Примерно по такой же схеме работают и сиквенсы в оракле, т.е. они предоставляют только последовательный доступ пользователей к очередным значениям сиквенсов. Там все это более оптимизированно, так можно разрешать при создании сиквента до N параллельных обращений, так как Oracle как бы заранее генерит пул из N следующих значений на случайодновременного обращения к сиквенсу... У нас получилось чут менее эффективно, но зато универсально.

Все. Эврика! Используйте в Вашем приложении ODBC-подключение к внешнему DSN и общайтесь с базой только на SQL какого-нибудь раннего стандарта, который действует везде. Теперь Вы можете развернуть Вашу структуру таблиц и в DBF и на Oracle - Вашему приложению будет пофигу с чем работать...