SQLite的：如何限制基于所述时间戳的行数？ [英] SQLite: How to limit the number of rows based on the timestamp?

问题描述

我成功地用下面的 BEFORE INSERT 触发限制存储在SQLite数据库表中的行数的地点的。数据库表作为在Android应用程序缓存。

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

同时，我添加了第二个触发，让我插入或更新行。 - 关于这一主题的讨论可以在<一个被发现href="http://stackoverflow.com/questions/11686645/android-sqlite-insert-update-table-columns-to-keep-the-identifier">another螺纹。第二个触发需要的查看在每个插入被执行。

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

由于一个插入不再对 TABLE执行的地点的，但在查看的 locations_view 的，上面的触发器确实不再起作用。如果我申请上的查看触发下面的错误信息被抛出。

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)

问：

如何更改上面的触发器，观察每个插入在查看 - 或者你推荐另一种方式限制行数？我想preFER来处理这种操作的数据库中，而不是我的客户端上运行的前端code。

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.

性能问题：

虽然，限制器（上述触发）做工一般 - 它执行不到最优！实际上，数据库操作需要很长时间，一个ANR提高。据我所看到的，其原因是，该限幅器被称为每时间插入发生。为了优化设置，大宗插入应包装成一个事务和限制器应正确后执行。这可能吗？如果你想帮助，请将有关大宗插入到<一个优化的意见href="http://stackoverflow.com/questions/11686645/android-sqlite-insert-update-table-columns-to-keep-the-identifier">original问题。关于限制器意见，欢迎在这里。

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.

推荐答案

这类型的触发器应该很好地工作与另一个一起。这个问题似乎是，在SQL被不必要地引用了 _id 字段。这是选择的文本字符串_id的每一行，并进行比较，为相同的文本字符串。

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.

删除周围'_ ID（无论是在删除和亚报价 - SELECT ）应该解决这个问题。

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

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