Привет.

Еще один момент нашел и не могу придумать как обойти и понять баг это, или фича :(
есть табличка с PK (number) + альтернативный уникальный кей (char (24)).
по алгоритму нужно проапдейтить поля по альтернативному ключу и если записи нет, то вставить новую.
но при использовании insert or replace у меня выполняется апдейт PK(!) а это в общем случае ломает связи.
PK я сделал number чтобы экономить на размере внешних ключей т.к. у меня естественный кей tth весит 24 байта.

Пока пришлось сделать вместо insert or replace костыль:

1. insert + через sqlite3_last_insert_rowid достаем рожденный tth_id
2. ловить исключение при insert (есть дубликат по полю TTH)
и выполнять update, но потом вынужден делать дополнительный select по tth чтобы достать tth_id

Вопросы
1. Как заставить работать insert or replace
2. Если 1 сделать нельзя, то как избавиться от дополнительного select и вернуть tth_id сразу из update
что-то вроде аналога returning из oracle есть?

Упрощенный демо-пример (версия sqlite последняя 3.8.5 под виндой)


--
~PPA() {} //

Разработчик sqlite ответил, что тут бага нет.
база сначала удаляет запись, а потом делает вставку новой
хотя я думаю что это не очень эффективно.

у кого под рукой другие сервера есть которые умеют insert or replace + AUTOINCREMENT
они поступают аналогично?

White Owl,

В базе хранится много атрибутов с привязкой к этому tth_id (50-200 штук для каждого файла)
если внешний кей станет вместо int-а char(24) то думаю что база необосновано распухнет.
нужно кстати провести эксперимент - отпишу в тему как сделаю.

White Owl,

База после вакума размером 7440232448 байт

select count(*) from fly_mediainfo;
105593000

Запустил конвертацию на ночь - утром отпишу результат.

Доброе утро.
Размер базы стал
15 006 498 816
что-то даже больше чем я ожидал... (наверно из-за того что i_fly_mediainfo2 сделал не уникальным -
уникальность не получается т.к. у мастре-таблицы естественный кей tth + file_size)
такая потеря по диску это разве замечательно?


Y:\sqlite-test\db>sqlite3 FlylinkDCServer.sqlite < convert-tth.sql
.show
echo: on
explain: off
headers: off
mode: list
nullvalue: ""
output: stdout
separator: "|"
stats: off
width:

PRAGMA journal_mode = OFF;
off
Run Time: real 51.050 user 0.000000 sys 0.452403

select count(*) from fly_mediainfo;
105593000
Run Time: real 1.472 user 0.187201 sys 1.263608
Run Time: real 0.000 user 0.000000 sys 0.000000
Error: near line 8: no such table: fly_mediainfo2
create table fly_mediainfo2(
tth char(24),
stream_type number,
channel number,
dic_attr_id number,
dic_value_id number,
attr_value text);
Run Time: real 0.186 user 0.000000 sys 0.187201
insert into fly_mediainfo2(tth,stream_type,channel,dic_attr_id,dic_value_id,attr_value)
select (select tth from fly_file where id = fly_file_id) tth, stream_type,channel,dic_attr_id,dic_value_id,attr_value from fly_mediainfo;
Run Time: real 573.914 user 225.764647 sys 28.454582
drop table fly_mediainfo;
Run Time: real 72.244 user 7.176046 sys 6.739243
create index i_fly_mediainfo2 on fly_mediainfo2(tth,dic_attr_id,stream_type,channel);
Run Time: real 720.701 user 487.939928 sys 51.137128
vacuum;
Run Time: real 1568.755 user 267.869317 sys 181.382363