CREATE TABLE TEST(I INT) ENGINE = MYISAM;
ALTER TABLE TEST ENGINE = INNODB;

• A .frm file gives the schema format for the table
• A .MYD file holds the actual data as a sequential file.
• A .MYI is used to store an index into the data file.

CREATE INDEX FOO ON BOB(A) USING BTREE;
or
CREATE INDEX FOO ON BOB(A) USING HASH;

1. lock-byte page. This page (at most one, and only if database bigger than 1GiB) is for storage which may be used by the OS file system to handle file locking.
2. free-list page. This page type is for pages that were at some point allocated for a Sqlite DB which through deletions have become empty/inactive are added to a list of free pages for reuse. free-list page are either trunk pages or leaf pages. Trunk pages are part of a linked-list referenced from the header as the start of the database. Each trunk page can point to 0 or more leaf pages.
3. B-tree page. This page type comes in two sub-types: B-tree table and B-tree index page types, the former being used to implement database tables, the latter for indexes on tables. Leaf pages of B-tree tables attempt to store the record data itself in the leaf up to some fixed number of bytes; B-tree index always use pointers to records rather than attempt to store things in B-tree.
4. payload page. This page type is used for pages that store the remainder of records that couldn't be fit into a B-tree table leaf node. Contains a pointer to next page in list if record still needs more pages.
5. pointer map page. Pages of this page type exist depending on the vacuum settings (used as part of multi-view concurrency control we'll talk about later) set for the database in its header. Let `J` be the number of 5 byte pointers that can be fit in the usable space of a page. Let `k \geq 0`. If incremental vacuuming or auto-vacuuming is being used, then pages `k(J+1) + 2` in the DB will be pointer map pages, and page `k(J+1) + 2` will be responsible for holding reverse pointers (say from child to parent in a B-tree) for pages `k(J+1) + 3` through `(k+1)(J+1) + 1` that might help speed up the vacuuming process for unused records and pages.

CREATE TABLE sqlite_master(
  type text,
  name text,
  tbl_name text,
  rootpage integer, // for example if 2, and block size 4096 then page 
                    // would be filled in going backwards from 8192 
                    // i.e., from end of page 1.
  sql text
);

• type - one of table, view, index, or trigger, says what kind of object the record is.
• name - internal name for this object.
• tbl_name - name given to object in create statement.
• rootpage - first page of B-tree associated with this object
• sql - SQL create statement for this item.

• sqlite_sequence table (used to keep track of auto-increment sequence)
• sqlite_stat1 to sqlite_stat4 tables used to contain statistics for tables for query optimization.

• grid-files
• partitioned hash functions
• multiple key indexes
• kd-trees
• quad trees
• R-trees.

• Store maps. Might include objects like points, houses, roads, etc...Or instead of maps, might store circuit layouts...
• Types of queries:
  • Partial match query - ask for all points matching a certain values in some specified dimensions
  • Range query - ask for all points between range of values
  • Nearest neighbor queries -- closest point to a given point
  • Where am I query -- given a point, what object am I in?

• These are often used by decision support applications where they are used to analyze information to better understand a companies operations.
• For example, a chain of stores might collect with each sale: the date and time, the store of purchase, the item, the color, and the size.
• This information could be viewed as a point in a mulitdimensional space and one might want to quickly answer aggregating queries like: number of red ties sold in each store in each month of 2005.

SELECT * FROM Points p WHERE NOT EXISTS(
SELECT * FROM Points q
WHERE (q.x-10.0)*(q.x-10.0) + (q.y-20.0)*(q.y-20.0) < 
    (p.x-10.0)*(p.x-10.0) + (p.y-20.0)*(p.y-20.0)
);