SQLite Forum

Are Views just stored SQL Strings or something more?
Login

Are Views just stored SQL Strings or something more?

(1) By Rob (rgarnett1955) on 2020-05-31 23:17:42 [link] [source]

Hi,

I know views are stored in the sqlite_master table as:

type view: name "myname" tbl_name "myname" rootpage 0 sql "mysqlString"

Is this all they are or is there some way the compilation of the view is retained/stored for the next time it is run?

I know some databases store Views in raw and compiled to speed things up e.g MS Access (I think) SQL Server

Best regards

Rob

(2) By Richard Hipp (drh) on 2020-05-31 23:42:41 in reply to 1 [link] [source]

The view is stored in the sqlite_master table as plain text - the same text that you entered to create the view in the first place. Each time the schema changes in any way, or when the database connection is first opened, the sqlite_master table is parsed and at that point the VIEW is converted into an internal representation that is used for subsequent queries.

This design has advantages over trying to store views in a binary format:

  1. The SQLite database file format is easy to define in a single document.

  2. We can freely change the internal representation for better performance, new features, or just to fix bugs, without breaking backwards compatibility with legacy databases.

(3) By doug (doug9forester) on 2020-06-01 17:27:49 in reply to 2 [link] [source]

I have been using MS Access for eons and have wanted to convert my applications to Sqlite. Aside from the obvious missing forms and reports (which are starting to be addressed with MD and other changes), Sqlite doesn't have saved queries, except --- wait for it --- something called a view! A view is a very simple, one-table query.

Is is a big step to provide the ability to add WHERE and ORDER BY to the view definition? And then the real bonus: add JOIN capability? With those things, you suddenly get a viable saved-query system, I think, at very little additional cost. And what about parameterized queries? Is that too big a step?

(4.1) By Larry Brasfield (LarryBrasfield) on 2020-06-01 17:33:37 edited from 4.0 in reply to 3 [link] [source]

The view is better considered to be an arbitrary SELECT query. Anything that could be expressed with such a query can be made into a view.

Try it!

I once tried to include parameters in a view, to be bound to values when the view was used in a query, but SQLite rejects it. As far as I could see from the documentation, it should have been allowed. But it is not.

(5) By doug (doug9forester) on 2020-06-01 17:43:30 in reply to 4.1 [source]

Sorry, I missed that it's a full select, joins and all.

(6) By Keith Medcalf (kmedcalf) on 2020-06-01 17:58:30 in reply to 3 [link] [source]

The only thing you cannot put in a view is parameters. Otherwise anything that you can put in a "query" (as in that returns results) is valid in a view. There are no restrictions other than the fact that a view cannot be parameterized.

What is "MD" -- do you mead Medical Doctor or Make Directory, or something else?

(7) By anonymous on 2020-06-01 18:15:05 in reply to 6 [link] [source]

While a view cannot be parameterized, it is possible to fake it using a "magnet" virtual table; I have seen this suggested in the mailing list once, and I have also implemented it. The "magnet" virtual table merely returns a single row with the value it is constrained to have by the query (if the value isn't constrained, then it is an error). For example, then you can write:

create view a(b,c) as select value*value,value from magnet(value);
select b from a where c=5;
select b from a where c=6;
However, you cannot use table-valued-function syntax with views, since views don't have hidden columns (adding HIDDEN in the list of column names before AS is an error; HIDDEN and DEFAULT are the two things it might make sense to accept in that list (in addition to the column names), though).

(8) By doug (doug9forester) on 2020-06-01 18:15:19 in reply to 6 [link] [source]

MD is Markdown. Should it be MU (Markup)? What's the difference?

So I just created a view with a set of joins. Worked like magic!

I am now going to try to create some views for my Qt application. I thought Qt documentation said views are not supported. But it's sql, right? Ought to work.

Looking at the CREATE TABLE (syntax diagram) definition again after looking at CREATE VIEW. I see that you can create a table composed of JOINs. What does that even mean? It doesn't seem right that I can define a table that is composed of JOINs of other tables.

(9) By Larry Brasfield (LarryBrasfield) on 2020-06-01 18:23:32 in reply to 8 [link] [source]

You have not created a table that way. It is a read-only "view" that acts like a table in some ways, such as possibly being incorporated in larger select queries (much as a sub-select may be), and does not act like a table in other ways.

(10) By Richard Damon (RichardDamon) on 2020-06-01 18:42:46 in reply to 8 [link] [source]

Markdown is one particular method to 'Mark up' text to indicate formatting. (Like HTML is a markup language designed for web pages). Part of the reason for it being called Mark DOWN is that it is designed to be a minimalistic format, that is reasonably readable even without needing to apply the indicated Markups.

(11) By Keith Medcalf (kmedcalf) on 2020-06-01 18:45:14 in reply to 8 [link] [source]

Presumably you mean CREATE TABLE ... AS <query>

This is SYNTACTIC SUGAR (the same thing but sweeter) as:

CREATE TABLE ...;
INSERT INTO <table> <query>;

That is, instead of running the two statements:

CREATE TABLE t1 (i);
INSERT INTO t1 SELECT i FROM t0;

you can say in one statement (with suger on top):

CREATE TABLE t1 AS SELECT i FROM t0;

This creates a new table t1 from the contents of table t0 at the instant in time at which the statement is executed. Subsequent changes to t0 ARE NOT reflected in t1.

Contrast with:

CREATE VIEW t1 AS SELECT i FROM t0;

where the contents of t1 represent the contents of t0 at the time the view is used and changes to t0 are reflected in view t1.