SQLite:如何根据时间戳限制行数? [英] SQLite: How to limit the number of rows based on the timestamp?

问题描述

我成功地使用了以下 BEFORE INSERT 触发器来限制存储在 SQLite 数据库表 locations 中的行数.数据库表充当 Android 应用程序中的缓存.

CREATE TRIGGER 'trigger_locations_insert'在插入位置"之前WHEN ( SELECT count(*) FROM 'locations' ) >'100'开始从位置"中删除_id"不在(SELECT '_id' FROM 'locations' ORDER BY 'modified_at' DESC LIMIT '100');结尾

与此同时，我添加了第二个触发器，允许我INSERT OR UPDATE 行.- 关于该主题的讨论可以在 another thread 中找到.第二个触发器需要一个 VIEW，在它上面执行每个 INSERT.

CREATE VIEW 'locations_view' AS SELECT * FROM 'locations';

因为 INSERT 不再在 TABLE locations 上执行，而是在 VIEW locations_view 上执行，上面的触发器不再起作用.如果我在 VIEW 上应用触发器，则会抛出以下错误消息.

Failure 1(无法在视图上创建 BEFORE 触发器:main.locations_view)

问题:

如何更改上述触发器以观察 VIEW 上的每个 INSERT - 或者您是否推荐另一种方法来限制行数?我更愿意在数据库中处理这种操作，而不是在我的客户端上运行前端代码.

<小时>

性能问题:

虽然，限制器(上面的触发器)一般都可以工作 - 它的性能不如最佳！实际上，数据库操作花费的时间太长以至于引发了 ANR.据我所知，原因是限制器被调用每次 INSERT 发生.为了优化设置，批量 INSERT 应该被包装到一个事务中，并且限制器应该在之后立即执行.这可能吗?如果您愿意提供帮助，请将有关批量 INSERT 的优化注释放入 原始问题.欢迎在此评论限制器.

解决方案

这种类型的触发器应该与另一种触发器配合使用.问题似乎是 SQL 不必要地引用了 _id 字段.它为每一行选择文字字符串_id"并将其与相同的文字字符串进行比较.

删除 '_id' 周围的引号(在 DELETE 和子 SELECT 中)应该可以解决问题.>

I successfully used the following BEFORE INSERT trigger to limit the number of rows stored in the SQLite database table locations. The database table acts as a cache in an Android application.

CREATE TRIGGER 'trigger_locations_insert'
BEFORE INSERT ON 'locations'
WHEN ( SELECT count(*) FROM  'locations' ) > '100'
  BEGIN
    DELETE FROM 'locations' WHERE '_id' NOT IN
    (
      SELECT '_id' FROM 'locations' ORDER BY 'modified_at' DESC LIMIT '100' 
    );
  END

Meanwhile, I added a second trigger that allows me to INSERT OR UPDATE rows. - The discussion on that topic can be found in another thread. The second trigger requires a VIEW on which each INSERTis executed.

CREATE VIEW 'locations_view' AS SELECT * FROM 'locations';

Since an INSERT is no longer executed on the TABLE locations but on the VIEW locations_view, the above trigger does no longer work. If I apply the trigger on the VIEW the following error message is thrown.

Failure 1 (cannot create BEFORE trigger on view: main.locations_view)

Question:

How can I change the above trigger to observe each INSERT on the VIEW - or do you recommend another way to limit the number of rows? I would prefer to handle this kind of operation within the database, rather then running frontend code on my client.

---

Performance issues:

Although, the limiter (the above trigger) works in general - it performs less then optimal! Actually, the database actions take so long that an ANR is raised. As far as I can see, the reason is, that the limiter is called every time an INSERT happens. To optimize the setup, the bulk INSERT should be wrapped into a transaction and the limiter should perform right after. Is this possible? If you like to help, please place optimization comments concerning the bulk INSERT into the original question. Comments regarding the limiter are welcome here.

解决方案

This type of trigger should work fine in conjunction with the other one. The problem appears to be that the SQL is unnecessarily quoting the _id field. It is selecting the literal string "_id" for every row and comparing that to the same literal string.

Removing the quotes around '_id' (both in the DELETE and in the sub-SELECT) should fix the problem.

这篇关于SQLite:如何根据时间戳限制行数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！