Databases and arrays

SQLite databases and NumPy arrays are read where they are, a page of rows at a time, and a file of several tables lists them like a directory.

SQLite

make_shop_db.py

import sqlite3

db = sqlite3.connect("shop.db")
db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL)")
db.execute("CREATE TABLE customers (name TEXT, city TEXT)")
db.executemany("INSERT INTO orders (customer, amount) VALUES (?, ?)", [("ana", 9.5), ("bo", 3.25)])
db.executemany("INSERT INTO customers VALUES (?, ?)", [("ana", "Lima"), ("bo", "Oslo")])
db.commit()
db.close()

python3 make_shop_db.py
datui shop.db --table orders
datui shop.db/orders
cat shop.db | datui --table orders

Continuing from above, a database of several tables opens the home screen inside it:

datui shop.db

SQLite’s own tables (sqlite_master, sqlite_sequence, the sqlite_stat tables, a full-text index’s shadow tables) are hidden until Ctrl+A; --table opens them by name. The home screen labels a database with its tables (3 tables).

T at the table lists the database’s tables and views, each with its kind, columns and the rows ANALYZE stored, and opens another; so does Enter on a table of the SQLite tab. Nothing is counted to list them.

A table is read in place; nothing is copied.

A view is paged by position and cannot be reversed with r in SQLite (Polars does it). A sort on a column without an index has SQLite sort the rows for each page; SQLite may use temporary files in the temp directory to do so.

Columns are typed by what they declare:

SQLite lets a column hold values of any type. A column whose first 1,000 rows hold values of several types is read as text, numbers as SQLite writes them and blobs as X'0A1B'. After the open, one pass over the table checks the rest: a value further on that is not a number, in a number column, reads as null, and the Info panel’s Notes tab says how many. Dates stay text, as SQLite stores them.

The database is only read:

NumPy

make_arrays.py writes prices.npy and run.npz:

import ctypes
import zipfile


class Preamble(ctypes.LittleEndianStructure):
    _layout_ = "ms"
    _pack_ = 1  # no padding between fields
    _fields_ = [
        ("magic", ctypes.c_char * 6),
        ("major", ctypes.c_uint8),
        ("minor", ctypes.c_uint8),
        ("header_len", ctypes.c_uint16),
    ]


def npy(descr, shape, values):
    """An .npy file: the preamble, a header padded to 64 bytes, then the values."""
    header = repr({"descr": descr, "fortran_order": False, "shape": shape}).encode()
    header += b" " * (63 - (10 + len(header)) % 64) + b"\n"
    return bytes(Preamble(b"\x93NUMPY", 1, 0, len(header))) + header + bytes(values)


def f8(*values):  # little-endian float64, as "<f8" says
    return (ctypes.c_double.__ctype_le__ * len(values))(*values)


def f4(*values):  # little-endian float32, "<f4"
    return (ctypes.c_float.__ctype_le__ * len(values))(*values)


with open("prices.npy", "wb") as f:
    f.write(npy("<f8", (3,), f8(1.5, 2.5, 4.0)))
with zipfile.ZipFile("run.npz", "w") as z:
    z.writestr("weights.npy", npy("<f4", (2, 2), f4(1, 2, 3, 4)))
    z.writestr("bias.npy", npy("<f4", (2,), f4(0.5, -0.5)))

python3 make_arrays.py
datui prices.npy
datui run.npz --table weights
datui run.npz/weights

• NaT is null.

• Big-endian (>i4) and little-endian fields mix in one array.

• Fortran (column-major) order reads the same as C order.

• Padding fields (align=True) are left out, and fields at offsets (offsets, itemsize) are read where they are.

• A file shorter than its shape says shows the rows it holds; the Notes tab says so.

• A file named without .npy is known by its first bytes, \x93NUMPY.

• NaT is null.

• Big-endian (>i4) and little-endian fields mix in one array.

• Fortran (column-major) order reads the same as C order.

• Padding fields (align=True) are left out, and fields at offsets (offsets, itemsize) are read where they are.

• A file shorter than its shape says shows the rows it holds; the Notes tab says so.