Learn more about Russian war crimes in Ukraine.

UNIX as a SQL database

UNIX is full of tables. When we talk about “processes”, we’re really referring to “rows in a process table.” When we talk about “file descriptors”, we’re really referring to “rows in a per-process file descriptor table”. There are other tables, too: a global file table, an inode table, routing tables, a mount table, page tables, and other tables I don’t know about.

These “tables” are custom in-memory data structures, but can be understood relationally. Here’s a simplified description of them in SQL.

-- One row = one process
CREATE TABLE process (
  pid INT PRIMARY KEY,
  -- ...
);

-- Links processes to files via descriptors.
-- In reality, may be implemented as per-process tables (blocks of memory).
CREATE TABLE descriptor (
  pid INT FOREIGN KEY process (pid),
  file_descriptor INT,
  file_id INT FOREIGN KEY file (id)
  PRIMARY KEY (pid, file_descriptor)
);

-- Global file table
CREATE TABLE file (
  id INT AUTOINCREMENT PRIMARY KEY,
  offset_bytes INT NOT NULL,
  -- ...
  inode_number NOT NULL FOREIGN KEY inode_cache (inode_number)
);

-- A cache of inodes on disk
CREATE TABLE inode_cache (
  inode_number INT PRIMARY KEY,
  -- ...
);

CREATE TABLE pages (
  pid INT FOREIGN KEY process (pid),
  virtual_page BITSTRING,
  physical_frame BITSTRING FOREIGN KEY ...,
  PRIMARY KEY (pid, virtual_page)
);

What can computers do? What are the limits of mathematics? And just how busy can a busy beaver be? This year, I’m writing Busy Beavers, a unique interactive book on computability theory. You and I will take a practical and modern approach to answering these questions — or at least learning why some questions are unanswerable!

It’s only $19, and you can get 50% off if you find the discount code ... Not quite. Hackers use the console!

After months of secret toil, I and Andrew Carr released Everyday Data Science, a unique interactive online course! You’ll make the perfect glass of lemonade using Thompson sampling. You’ll lose weight with differential equations. And you might just qualify for the Olympics with a bit of statistics!

It’s $29, but you can get 50% off if you find the discount code ... Not quite. Hackers use the console!

More by Jim

Tagged . All content copyright James Fisher 2017. This post is not associated with my employer. Found an error? Edit this page.