Dawn of Databases - COBOL and files

According to Wikipedia, the first use of the term database was in 1962 in a report of the System Development Corporation.
At that time, direct access storage (disk and similar devices) was just becoming available.
CODASYL, the standards body for COBOL, came up with specs for how a COBOL program might access records with in a file.
It took a while for people to realize that file access should be abstracted from the particular programming language.
It also took a while for people to abstract out the language to find records from the particular code in a programming language.

Dawn of Databases - IBM and ISAM

As part of the Apollo program, IBM developed an early database known IMS (instances of IMS are still in use to this day) in 1966.
It used a "hierarchal model" rather than a the relational model we use today, and also introduced an early query language, DL/I.
The way it stored records was highly influential for later DMBSs.
It used variations on what is known as ISAM (Indexed Sequential Access Model).
The basic idea of ISAM is to organize the data records in a sequential file according to key.
For inserts, we use overflow blocks as needed.
In the simplest design, on top of this we have a two level index: the top layer being a sparse index on (key,cylinder), then beneath this, for a particular cylinder, we would have an index on (key, block) that would map into the sequential file.
An IMS database could be thought of as a set of such files, where records from a top level file could have key values of a lower file such that the set of files could be arranged into a tree-like structure.
IBM had another product based on General Electric (1964) database called IDMS, which arrange files in a DAG-like structure rather than tree-like.

How Tables and Indexes are Implemented: IBM, Ingres, Postgres, Oracle

ISAM as described on the last slide is very specific to disk drives and even works best on old fashioned disk drives.
IBM's next generation database was known as System R (developed in San Jose) and it officially came out in 1974.
Its query language was called SEQUEL and was a precursor to modern SQL.
As part of this work, IBM introduced a new storage format, VSAM, Virtual Storage Access Method.
The idea was to abstract out the key components of the index part of ISAM and also to abstract out the implementation of the file, so that it no longer needed to be sequential.
You can view VSAM as a container format with four possible payloads: Key Sequenced Data Set (KSDS), Relative Record Data Set (RRDS), Entry Sequenced Data Set (ESDS) (non-sequential) and Linear Data Set (LDS).
The Key Sequenced Data Set which is essentially a multi-level index on a sequential file with overflow blocks, and so an abstracted out version of an ISAM file.
At Berkeley, Michael Stonebraker and Eugene Wong became interested in the work on System R in 1973 and began building their own database system, Ingres.
It supported four table types: Heap, Hash, ISAM, B-tree which specified the index on the key field of the table.
In 1979, Oracle created its first database and it was designed to be compatible as possible with System R so used a similar file implementation.
In the early 1980s, IBM created DB2 as a successor to System R.
In the 1980s, Postgres was developed as a successor of Ingres, it had a better recovery mechanism, support for geo-spatial indexes, and better concurrency control.

How Tables and Indexes are Implemented: MySQL

MySQL was originally developed in Sweden by Michael "Monty" Widenius and David Axmark in 1994-1995 based on the msql DBMS from Hughes Technologies that had been created in 1993.
The original MySQL AB company was sold to Sun in 2008, which in turn became part of Oracle in 2010.
In 2009, around the time the Sun-Oracle deal was announced, Widenius forked the MySQL code to create MariaDB.
MySQL/MariaDB supports several backend storage engines (InnoDB, MyISAM, MEMORY, ARCHIVE, etc) for tables which can be chosen using the data definition language (DDL).
You can set the storage engine in a CREATE TABLE or change it using an ALTER TABLE, For example,
```
CREATE TABLE TEST(I INT) ENGINE = MYISAM;
ALTER TABLE TEST ENGINE = INNODB;
```
Roughly the same Data Manipulation Language (DML) SQL can then be used to do select, insert, delete, updates regardless of engine, however, table capabilities, integrity, etc do vary with table type.
As of Spring 2018, MySQL 5.7.x is the latest version of MySQL, and its default storage engine in InnoDB. Prior to version 5.5, the default and original storage engine was MyISAM.

MyISAM, InnoDB, Aria, XtraDB

MyISAM tables are stored in three files that begin with the table name:
- 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.
InnoDB tables are stored in two files: a .frm and a .idb file, with the latter having the data.
MyISAM (no longer under development) was not designed to be ACID compliant (needed for transaction) and could also be crash-prone, but was good in read situation..
InnoDb was designed as ACID compliant successor to MyISAM and is now faster under most kinds of workloads.
Aria is the MariaDB variant of MyISAM and it is still under development.
XtraDB is the MariaDB variant of InnoDb and it is still under development.
MySQL support different row formats for the data file and it supports different kinds of indexes for each of these engines.

For example, one can specify the type of index in MySQL with a using clause:

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

How Tables and Indexes are Implemented: Sqlite

Sqlite was originally developed by D. Richard Hipp in 2000 at General Dynamics for use in embedded systems for guided missile destroyers.
Sqlite databases are stored as a single file and the SQL syntax and semantics were loosely based off Postgresql 6.5.
Sqlite databases come in two main formats which are still seen in the wild, Sqlite 2, based on gdbm, (pre-2004 - don't use), and Sqlite 3.
The Sqlite 3 database format was developed with partial support from Aol.
A Sqlite 3 database is organized as a sequence of pages.
The first page in such a file begins with a 100 byte header, followed by a B-tree page header, followed by the rest of the root node of sqlite_master B-tree table.
The first 16 bytes of the 100 byte header contains the string "SQLite format 3\000".
This is followed by a two byte number which specifies the page size, which must be a power of two between 512, and 65536 (if these two bytes contain the number 1).
Two important properties stored in the 100 byte file header are the logging method used by the database, and the page number of start of free space list.

Sqlite Page Types

A Sqlite 3 page can be of one of five types:
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.

sqlite_master B-tree table

The sqlite_master table stores records which would conform to the following scheme:

CREATE TABLE sqlite_master(
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);

For a given sqlite_master records, the semantics of its columns are:
- 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 objectin create statement.
- rootpage - first page of B-tree associated with this object
- sql - SQL create statement for this item.
In addition, to records for any user defined tables, indexes, views, and triggers, the sqlite_master will typically have records for the following internal tables:
- 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.

Quiz

Which of the following statements is true?

Insert in a B-tree can be done in `O(log n)` time where `n` is the number of records stored in the B-tree.
The terms inverted index and clustered index are synonymous.
Extensible hash functions are required to store fewer than 10,000 items.

Multidimensional Data

All indexes considered so far are on one field. Say salary.
We now consider indexes and file structures for more than one field. Say first name, last name.
There are several techniques for such indexes and files that we will consider:
- grid-files
- partitioned hash functions
- multiple key indexes
- kd-trees
- quad trees
- R-trees.

Application Needing Multidimensional Data

Geographics Information Systems
- 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 neighbour queries - closest point to a given point
  - Where am I query -- given a point, what object am I in?

More Applications

DataCube
- 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.

SQL for Multidimensional Queries

Might have a table of Points with `x` and `y` attributes.

A query might look for the closest point to `(10,20)`:

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

Another possible query: Have a table of Rectangle and ask for all tuples containing a point.

Using Conventional Indexes

To do a range query like find all points between `300 lt x lt 400` and `500 lt y lt 600`, we could have a B-tree index on `x` and `y`.
Suppose 1/10 of records satisfy the above condition on `x` and 1/10 satisfy the condition on `y`.
So 1/100 of points are in the rectangle.
Suppose 100 is the number of records that can be held in a block. If the original file had 1,000,000 records, then it would fit in 10,000 blocks
We could do a range query on x to retrieve records pointers for x in desired range and do the same for y.
We can then intersect these lists of pointers... And look up each record.
Unfortunately, each record is likely to be on a different block.
So we'd have to look up (`1/100 times mbox(# of records)`) blocks to do this. i.e., 10,000 blocks.
But this is the size of the whole file
...so doing a range query on `x` or `y` hasn't saved us anything over a table scan.

Hash-Like Structures for Multidimensional Data and Indexes

Grid Files
Partitioned hashing

Grid Files

A grid file is a index structure on more than one attribute.
We split each attribute dimension by a set of grid lines.
For Points table example might have lines `x= 100, 200, 300, 400, ...` and `y=100, 200, 300, 400...`.
To implement the grids lines, we could imagine we have two functions: xgrids(x) and ygrids(y).
xgrids(x) (ygrids(y) operates in a similar fashion) given a value `x` returns the three grid lines satisfying `x_{i-1} lt x_i le x lt x_{i+1}`.
These two functions are specified at index creation time.
Given xgrids(x) and ygrids(y) we then have a hash function `h(x,y)` which first computes `(x_i, y_i)`.
This gives a rectangle that (x,y) belongs to: `(x_i,x_{i+1}] times (y_j,y_{j+1}]`.
The hash function then computes based on `(x_i,y_j)` a single block number in the table.
We can extend this idea in a similar fashion if have more than two dimensions.
Each bucket has a set of pointers to records of Points in that rectangle.
If a bucket is too full, we use overflow blocks.
So to look up a point, we compute `h(x,y)` to get a block number, then looks for the record in this bucket.
To insert into a grid file, we just determine the rectangle the Point to be inserted belongs to, and add a pointer to the corresponding bucket.
Note we can implement neighborhood lookup by using xgrids and ygrids to find a set of rectangles that cover the neighborhood we want, then search through all the points in that neighborhood.

Partitioned Hash Files

A Partitioned Hash Table is a hash table on several attribute `A_1, A_2, ...`; however, we use a special kind of hash function such that the first `k_1` bits output determined by `A_1` only, the next `k_2` bits by `A_2` only, ...
The buckets mapped to by the hash function are such that they are laid out by dimensions. So if we fix say `k_1=a`, we can easily compute all the hash blocks that have `k_1 = a`.
Unlike a grid-file nearby points might be hashed to completely different buckets, so can't do nearest neighbor style queries.
The data in a partitioned hash table will tend to be more uniformly dispersed and so we are less likely to need overflow buckets.
Partitioned hash table are useful if we only care about partial match lookups (specify some attributes, leave others unspecified).

Storage Engines for DBMSs, Multidimensional Data

Outline

Introduction