Ограничить таблицу sqlite Максимальное количество строк

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

Например, если предел равен 100 и сколько строк в настоящее время находится в таблице, когда вставлено другое действие, самая старая строка автоматически удаляется, поэтому всегда должно быть не более 100 строк. Есть ли способ настроить таблицу sqlite для этого? Или мне нужно запустить работу cron?

Редактирование разъяснений. В любой момент я хотел бы отображать последние 100 (например) действий/событий (строк) таблицы.

Ответ 1

Другое решение состоит в том, чтобы предварительно создать 100 строк, а вместо INSERT использовать UPDATE для обновления самой старой строки.
Предполагая, что таблица имеет поле datetime, запрос

UPDATE ...
WHERE datetime = (SELECT min(datetime) FROM logtable)

может выполнить задание.

Изменить: отобразить последние 100 записей

SELECT * FROM logtable
ORDER BY datetime DESC
LIMIT 100

Обновить: вот способ создания 130 "dummy" строк с помощью операции соединения:

CREATE TABLE logtable (time TIMESTAMP, msg TEXT);
INSERT INTO logtable DEFAULT VALUES;
INSERT INTO logtable DEFAULT VALUES;
-- insert 2^7 = 128 rows
INSERT INTO logtable SELECT NULL, NULL FROM logtable, logtable, logtable,
   logtable, logtable, logtable, logtable;
UPDATE logtable SET time = DATETIME('now');

Ответ 2

Существует несколько способов ограничить таблицу до 100 строк. (Для краткости, 5 строк в коде ниже). Протестировано в SQLite версии 3.7.9.

Весь этот код опирается на своего рода причуду в том, как SQLite обрабатывает объявления типа данных. (Мне кажется, это причудливо.) SQLite позволяет вставлять глупость, например 3.14159, и "wibble" в столбец с целым числом. Но он позволяет вставлять только целые числа в объявленный столбец integer primary key или integer primary key autoincrement.

Ограничение FOREIGN KEY

Используйте ограничение внешнего ключа для таблицы допустимых номеров идентификаторов, чтобы гарантировать, что номера идентификаторов находятся в нужном вам диапазоне. Ограничения внешнего ключа работают даже на автоинкрементных столбцах.

pragma foreign_keys=on;
create table row_numbers (n integer primary key);

insert into row_numbers values (1);
insert into row_numbers values (2);
insert into row_numbers values (3);
insert into row_numbers values (4);
insert into row_numbers values (5);

create table test_row_numbers (
  row_id integer primary key autoincrement,
  other_columns varchar(35) not null,
  foreign key (row_id) references row_numbers (n)
);

insert into test_row_numbers (other_columns) values ('s');
insert into test_row_numbers (other_columns) values ('s');
insert into test_row_numbers (other_columns) values ('s');
insert into test_row_numbers (other_columns) values ('s');
insert into test_row_numbers (other_columns) values ('s');

Шестой вставке не удается выполнить команду "Ошибка: ограничение внешнего ключа".

Я не думаю, что использование автоинкремента абсолютно безопасно. На других платформах откат оставит зазор в последовательности. Если вы не используете автоинкремент, вы можете безопасно вставлять строки, выбирая номер идентификатора из "row_numbers".

insert into test_row_numbers values
(
  (select min(n) 
   from row_numbers 
   where n not in 
     (select row_id from test_row_numbers)), 
  's'
);

Ограничение CHECK()

Ограничение первичного ключа ниже гарантирует, что номера идентификаторов будут целыми числами. Ограничение CHECK() гарантирует, что целые числа будут в правильном диапазоне. Возможно, ваше приложение будет иметь дело с пробелами, вызванными откатами.

create table test_row_numbers (
  row_id integer primary key autoincrement,
  other_columns varchar(35) not null,
  check (row_id between 1 and 5)
);

Ответ 3

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