描述:
目前系统使用到SQLLite存储本地数据,由于数据量级在百万级以上,同时又要根据不同需求,对这百万级数据进行各种条件的排序并保存.
分析:
为了达到对不同条件排序的效果,考虑使用到持久化视图.SQLLite虽然可以创建视图,但是视图是只读的(Views are read-only in SQLite),SQLLite本身是无法创建持久化视图的.
解决:
in many cases you can use an INSTEAD OF trigger on the view to accomplish the same thing.
可以通过在视图上创建INSTEAD的触发器来实现对视图数据的修改.
SQLite创建视图的SQL语法
CREATE VIEW
sql-command ::= CREATE [TEMP | TEMPORARY] VIEW [database-name.] view-name AS select-statement
CREATE VIEW命令为一个包装好的SELECT语句命名。当创建了一个视图，它可以用于其他SELECT的FROM字句中代替表名。
若"TEMP"或"TEMPORARY"关键字出现在"CREATE"和"VIEW"之间，则创建的视图仅对打开数据库的进程可见，且在数据库关闭时自动删除。
若指定了则视图在指定的数据库中创建。同时使用和TEMP关键字会导致错误，除非是 "temp".若不声明数据库名，也不使用TEMP关键字，则视图创建于主数据库中。
你不能对视图使用COPY, DELETE, INSERT 或 UPDATE，视图在SQLite中是只读的。多数情况下你可以在视图上创建TRIGGER来达到相同目的。用DROP VIEW 命令来删除视图。
原文:
http://www.sqlite.org/lang_createview.html
The CREATE VIEW command assigns a name to a pre-packaged SELECT statement. Once the view is created, it can be used in the FROM clause of another SELECT in place of a table name.
If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "VIEW" then the view that is created is only visible to the process that opened the database and is automatically deleted when the database is closed.
If a <database-name> is specified, then the view is created in the named database. It is an error to specify both a <database-name> and the TEMP keyword on a VIEW, unless the <database-name> is "temp". If no database name is specified, and the TEMP keyword is not present, the VIEW is created in the main database.
You cannot DELETE, INSERT, or UPDATE a view. Views are read-only in SQLite. However, in many cases you can use an INSTEAD OF trigger on the view to accomplish the same thing. Views are removed with the DROP VIEW command.