Задачка про SQL

[dil]

В одной базе данных есть две таблицы, связанные отношением 1:M. Скажем, в одной лежат посты из блога, а в другой комментарии, привязанные к постам по id.
Задача: перетащить их все в другую базу, в которой идентификаторы у постов изменятся (потому что там уже есть другие посты с теми же id, что в первой базе), но при этом сохранить привязку к ним комментариев.

С постами понятно, SELECT из первой базы и INSERT во вторую.

А вот как быть с комментариями?
Мне пока пришли в голову три идеи, но все неэффективные.

1) Для каждого поста сразу после помещения его во вторую базу, когда уже известны и старый, и новый id, найти в первой базе все комментарии по старому id и сложить их во вторую базу с новым id. Но это отдельный SQL-запрос на каждый пост, а их там дофига.

2) Сначала перенести все посты, попутно составляя табличку со старыми и новыми id, а потом переносить комментарии, заменяя id постов по этой табличке. Но если табличку держать в памяти, она получится очень нефигового размера. А в базах её создавать нельзя, правов не хватает.

3) Извлечь вместе с постами сразу все их комментарии путём LEFT OUTER JOIN. Но тогда каждый пост извлечётся целиком много раз, с каждым своим комментарием, это дикий перерасход памяти в SQL-сервере.

Как бы это сделать поэффективнее?

Оригинал этой записи в личном блоге.

Comments

[alz421]

Первый вариант. Запусти скрипт с вечера, пусть жужжит. Твое время всяко дороже машинного :)

[dmarck]

Так завести отдельное unique поле, в которое писать origin ID?

[dil]

Да не могу я его завести, правов в базах не хватает. Остаётся только или в самой программе это хранить, или какую-нибудь внешнюю базу типа sqlite создавать..

[dmarck]

temporary table тоже не дадут создать?

[dil]

Ой, а в mysql есть temporary table? Интересная идея. Не знал, я ими только в информиксе пользовался. Надо будет попробовать.

[drug123.livejournal.com]

Синтаксис оператора CREATE TABLE

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]

Но нужны соответствующие права.

[beldmit]

а) Заюзать какой-нибудь ORM системы DBIx::Class, который вытащит посты с комментами. Из базы взял - в базу положил...

б) Импортировать посты, взять новые ID, сохранить в старой базе, взять из старой базы комменты с новыми ID постов, залить.

А почему ID сохранить не удается, кстати?

[dil]

а) а как ORM это будет делать? Я бы и сам так мог, главное - узнать, как именно.

б) Менять ID в старой базе ещё менее эффективно, потому что их придётся менять не только у самих постов, а и у комментов, и у других привязанных к ним типам записей, которые хоть и экспортируются, но и портить их тоже не надо. Вдобавок и новый ID может совпасть с тем, что уже есть в старой базе у другого поста.

В новой базе уже есть посты с такими же ID, как и в старой, менять их очень напряжно.

[beldmit]

Ну вот есть там магия, которая из результатов запроса с LEFT JOIN делает структуру 1:N. В потроха не лез, мне хватает, что просто работает.

Простейший способ эмуляции совсем на коленке - вытащить LEFT JOIN-ом, и следить, какой пост импортируем.

[dil]

Ну это я уже придумал под номером 3

[dcicppin]

Взять максимальный ID поста таргетной таблицы и добавлять его к значениям сорсевых таблиц при переносе? Нумерация постов обычно от единицы и более-менее непрерывная, т.ч. результат должен выйти близкий.

[dcicppin]

Но, конечно, сработает только если можно обеспечить отсутствие инсертов на таргетной таблице в процессе переноса.

[dil]

А этого, как обычно, никто не обещал. Там autoincrement, который в любой момент может пополниться.

[beldmit]

LOCK TABLE?

[dil]

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