database - utilizar - sqlite caracteristicas

Limite el número máximo de filas de una tabla sqlite

Question

Estoy buscando implementar una especie de tabla de "registro de actividad" donde las acciones que hace un usuario se almacenan en una tabla sqlite y luego se presentan al usuario para que pueda ver la actividad más reciente que han realizado. Sin embargo, naturalmente, no creo que sea necesario mantener cada bit de la historia, por lo que me pregunto si hay una forma de configurar la tabla para comenzar a podar filas antiguas una vez que se alcanza el límite máximo establecido.

Por ejemplo, si el límite es 100, y esa es la cantidad de filas que hay actualmente en la tabla, cuando se inserta otra acción, la fila más antigua se elimina automáticamente para que siempre haya un máximo de 100 filas. ¿Hay alguna forma de configurar la tabla sqlite para hacer esto? ¿O tendría que ejecutar un trabajo cron?

Edición de clarificación : en cualquier momento dado, me gustaría mostrar las últimas 100 (por ejemplo) acciones / eventos (filas) de la tabla.

Answer 1

Hay un par de formas de restringir una tabla a 100 filas. (Por razones de brevedad, 5 filas en el código a continuación). Probado en SQLite versión 3.7.9.

Todo este código se basa en un tipo de peculiaridad en la forma en que SQLite maneja las declaraciones de tipos de datos. (De todos modos, me parece peculiar). SQLite te permite insertar disparates como 3.14159 y ''wibble'' en una columna entera desnuda. Pero le permite insertar solo enteros en una columna de integer primary key declarados o integer primary key autoincrement .

Restricción FOREIGN KEY

Use una restricción de clave externa en una tabla de números de identificación válidos para garantizar que los números de identificación estén en el rango que desea. Las restricciones de clave externa funcionan incluso en columnas autoincrementing.

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'');

La sexta inserción falla con "Error: restricción de clave externa fallida".

No creo que usar un autoincrement sea completamente seguro. En otras plataformas, una reversión dejaría un espacio en la secuencia. Si no usa una autoincrementación, puede insertar filas de forma segura seleccionando el número de identificación de "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''
);

Restricción CHECK ()

La restricción de clave principal a continuación garantiza que los números de identificación serán enteros. La restricción CHECK () garantiza que los enteros estarán en el rango correcto. Es posible que tu aplicación tenga que ocuparse de las lagunas causadas por las reversiones.

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

Answer 2

Puede crear un trigger que se active en INSERTAR, pero una forma mejor de abordarlo podría ser simplemente tener un trabajo programado que se ejecute periódicamente (por ejemplo, una vez por semana) y elimine los registros de la tabla.

Answer 3

Otra solución es precreatear 100 filas y en lugar de INSERT usar UPDATE para actualizar la fila más antigua.
Suponiendo que la tabla tiene un campo de datetime y datetime , la consulta

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

puede hacer el trabajo.

Editar: muestra las últimas 100 entradas

SELECT * FROM logtable
ORDER BY datetime DESC
LIMIT 100

Actualización : aquí hay una manera de crear 130 filas "ficticias" mediante el uso de la operación de unión:

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'');