CHAPTER 6 – Other SQLite Tricks

There are still a few things untold about SQLite--for example, what the method is to query the database structure. The answer is easy--by using the following query: SELECT * FROM sqlite_master This returns one element per database object (table, index, and trigger) with the following information: type of object, the name of the object, the table to which the object is linked (only useful for indexes and triggers), an ID, and the SQL DDL query to create the object. When executed on our example, the result is shown in Table 6.14. Table 6.14 sqlite_master Dump Type Name Table ID SQL DDL table document document 3 CREATE TABLE document ( id INTEGER PRIMARY KEY, title, intro, body ) table dictionary dictionary 4 CREATE TABLE dictionary ( id INTEGER PRIMARY KEY, word ) table lookup lookup 5 CREATE TABLE lookup ( document_id INTEGER, word_id INTEGER, position INTEGER ) index word dictionary 6 CREATE UNIQUE INDEX word ON dictionary(word) trigger index_new document 0 CREATE TRIGGER index_new AFTER INSERT ON document BEGIN SELECT php_index(new.id, new.title, new.intro, new.body); END The last thing to discuss are views, an SQL feature to simplify user-land queries. For example, if we want to create a view called "document_body_id" that contains only the id and body fields of the document table, we can execute the following query: CREATE VIEW document_id_body AS SELECT id, body FROM document; After the view is created, you can use it in SQL queries just like it was a real table. For example, the following query uses the view to return the ID and body fields of the first two record of our document table: SELECT * FROM document_id_body LIMIT 2; Of course, in this case, it doesn't really make sense to create a view on one table only, but it does make sense to create a view over a complex query that joins multiple tables. Another original idea of views was that you can assign permissions to specific views as though they were tables, but of course, that doesn't make sense with SQLite, which doesn't know anything about per- missions except for permissions on the file system where the database file resides.

6.3.3.16 Words of Wisdom At last, here are some words of wisdom from the author of the SQLite engine, which he uses instead of a copyright notice: May you do good and not evil. May you find forgiveness for yourself and forgive others. May you share freely, never taking more than you give. -- D. Richard Hipp