From defb2cf06a9b5db9429ca368d8c2af127a5c9c3d Mon Sep 17 00:00:00 2001 From: eunhhu Date: Mon, 19 Oct 2026 01:53:40 +0100 Subject: 027's 5c: money as i64 minor units shown by .Nd: NUM_TEXT's decimal form takes digits (bits 20-24 as FIXED's), an integer's last N digits written after a point (1234 at two is 12.34, -5 -0.05, 0 0.00; width and zeros after the sign), verification allowing digits on FIXED and DECIMAL alone; unit test: ten rows of formats_and_held (-2^63 at two, 25 digits, a number's whole 1234, its 12.5 a limit); TPC-C's money i64 cents and its rates ten-thousandths (tables, messages, load, the transactions), SQLite's INTEGER columns and int64 bindings alike, the comparison now exact (no 1e-9 tolerance): after 20,000 transactions every row of every table the same (grouped SQLite too) and 3.3.2 holding on both; counts: b45 against b44 BFS without batches +0.09%, the rest within 0.001%; the bank's queries (s44 and s45 in turns, six each) in_credit -0.59%, per_branch -0.34%, ranks -0.20%, the rest within 0.01%, the seven answers the same bytes; 008 (NUM_TEXT's digits, a message row's i64 cells), 027 and 092 --- benchmarks/tpcc/data.py | 28 +++++++++++++++------------- benchmarks/tpcc/run.py | 27 ++++++++++++--------------- benchmarks/tpcc/sqlite-tpcc.c | 24 ++++++++++++------------ benchmarks/tpcc/tpcc-load.c | 4 ++-- benchmarks/tpcc/tpcc.aup | 30 +++++++++++++++++------------- 5 files changed, 58 insertions(+), 55 deletions(-) (limited to 'benchmarks') diff --git a/benchmarks/tpcc/data.py b/benchmarks/tpcc/data.py index d8a4ccb..82ebb07 100644 --- a/benchmarks/tpcc/data.py +++ b/benchmarks/tpcc/data.py @@ -1,13 +1,15 @@ """TPC-C's initial database (clause 4.3.3.1) for W warehouses, as rows of the records in tpcc.aup (each composite key packed into one number, as there), and the same rows for SQLite's tables with their own keys. Texts the -transactions only carry along are left out (see tpcc.aup).""" +transactions only carry along are left out (see tpcc.aup). Money is in +cents and a rate in ten-thousandths, integers (i64s in tpcc.aup, INTEGER in +SQLite).""" import random import struct ITEMS, CUSTOMERS, ORDERS, DELIVERED = 100000, 3000, 3000, 2100 -# Each table's fields in record order (tpcc.aup), all numbers. +# Each table's fields in record order (tpcc.aup), numbers but for the i64s in INTEGERS. FIELDS = { 'warehouses': ('id', 'tax', 'ytd'), 'districts': ('id', 'tax', 'ytd', 'next_order', 'next_delivery'), @@ -18,6 +20,7 @@ FIELDS = { 'new_orders': ('id',), 'lines': ('id', 'item', 'supply', 'delivery', 'quantity', 'amount'), } +INTEGERS = {'tax', 'ytd', 'discount', 'balance', 'ytd_payment', 'price', 'amount'} def district_key(w, d): @@ -36,13 +39,12 @@ def generate(warehouses, seed=1, now=1.7e12): """The tables as lists of tuples in FIELDS order.""" rng = random.Random(seed) tables = {name: [] for name in FIELDS} - tables['items'] = [(i, round(rng.uniform(1, 100), 2)) for i in range(1, ITEMS + 1)] + tables['items'] = [(i, rng.randint(100, 10000)) for i in range(1, ITEMS + 1)] for w in range(1, warehouses + 1): - tables['warehouses'].append((w, round(rng.uniform(0, 0.2), 4), 300000.0)) + tables['warehouses'].append((w, rng.randint(0, 2000), 30000000)) tables['stock'].extend((w * 100000 + i, rng.randint(10, 100), 0, 0, 0) for i in range(1, ITEMS + 1)) for d in range(1, 11): - tables['districts'].append((district_key(w, d), round(rng.uniform(0, 0.2), 4), 30000.0, ORDERS + 1, - DELIVERED + 1)) + tables['districts'].append((district_key(w, d), rng.randint(0, 2000), 3000000, ORDERS + 1, DELIVERED + 1)) owners = list(range(1, CUSTOMERS + 1)) rng.shuffle(owners) last = {} @@ -55,14 +57,14 @@ def generate(warehouses, seed=1, now=1.7e12): tables['new_orders'].append((key,)) for n in range(1, count + 1): tables['lines'].append((key * 16 + n, rng.randint(1, ITEMS), w, now if delivered else 0, 5, - 0 if delivered else round(rng.uniform(0.01, 9999.99), 2))) + 0 if delivered else rng.randint(1, 999999))) for c in range(1, CUSTOMERS + 1): - tables['customers'].append((customer_key(w, d, c), round(rng.uniform(0, 0.5), 4), - 0 if rng.random() < 0.1 else 1, -10.0, 10.0, 1, 0, last[c])) + tables['customers'].append((customer_key(w, d, c), rng.randint(0, 5000), + 0 if rng.random() < 0.1 else 1, -1000, 1000, 1, 0, last[c])) return tables -def packed(rows): - """Rows in a list's stored form (each number's binary64, little-endian).""" - width = len(rows[0]) - return struct.pack(f'<{width * len(rows)}d', *(v for row in rows for v in row)) +def packed(table, rows): + """A table's rows in a list's stored form (each number's binary64, each i64's two's complement, little-endian).""" + row = '<' + ''.join('q' if field in INTEGERS else 'd' for field in FIELDS[table]) + return b''.join(struct.pack(row, *r) for r in rows) diff --git a/benchmarks/tpcc/run.py b/benchmarks/tpcc/run.py index 377d555..0c68552 100644 --- a/benchmarks/tpcc/run.py +++ b/benchmarks/tpcc/run.py @@ -50,13 +50,13 @@ sys.path.insert(0, str(HERE)) import data # noqa: E402 SCHEMA = ''' -CREATE TABLE warehouse (w_id INTEGER PRIMARY KEY, w_tax REAL, w_ytd REAL); -CREATE TABLE district (d_w_id INTEGER, d_id INTEGER, d_tax REAL, d_ytd REAL, d_next_o_id INTEGER, d_next_delivery INTEGER, +CREATE TABLE warehouse (w_id INTEGER PRIMARY KEY, w_tax INTEGER, w_ytd INTEGER); +CREATE TABLE district (d_w_id INTEGER, d_id INTEGER, d_tax INTEGER, d_ytd INTEGER, d_next_o_id INTEGER, d_next_delivery INTEGER, PRIMARY KEY (d_w_id, d_id)) WITHOUT ROWID; -CREATE TABLE customer (c_w_id INTEGER, c_d_id INTEGER, c_id INTEGER, c_discount REAL, c_credit INTEGER, c_balance REAL, - c_ytd_payment REAL, c_payment_cnt INTEGER, c_delivery_cnt INTEGER, c_last_order INTEGER, +CREATE TABLE customer (c_w_id INTEGER, c_d_id INTEGER, c_id INTEGER, c_discount INTEGER, c_credit INTEGER, c_balance INTEGER, + c_ytd_payment INTEGER, c_payment_cnt INTEGER, c_delivery_cnt INTEGER, c_last_order INTEGER, PRIMARY KEY (c_w_id, c_d_id, c_id)) WITHOUT ROWID; -CREATE TABLE item (i_id INTEGER PRIMARY KEY, i_price REAL); +CREATE TABLE item (i_id INTEGER PRIMARY KEY, i_price INTEGER); CREATE TABLE stock (s_w_id INTEGER, s_i_id INTEGER, s_quantity INTEGER, s_ytd INTEGER, s_order_cnt INTEGER, s_remote_cnt INTEGER, PRIMARY KEY (s_w_id, s_i_id)) WITHOUT ROWID; CREATE TABLE orders (o_w_id INTEGER, o_d_id INTEGER, o_id INTEGER, o_c_id INTEGER, o_entry_d REAL, o_carrier_id INTEGER, @@ -64,10 +64,10 @@ CREATE TABLE orders (o_w_id INTEGER, o_d_id INTEGER, o_id INTEGER, o_c_id INTEGE CREATE TABLE new_order (no_w_id INTEGER, no_d_id INTEGER, no_o_id INTEGER, PRIMARY KEY (no_w_id, no_d_id, no_o_id)) WITHOUT ROWID; CREATE TABLE order_line (ol_w_id INTEGER, ol_d_id INTEGER, ol_o_id INTEGER, ol_number INTEGER, ol_i_id INTEGER, - ol_supply_w_id INTEGER, ol_delivery_d REAL, ol_quantity INTEGER, ol_amount REAL, + ol_supply_w_id INTEGER, ol_delivery_d REAL, ol_quantity INTEGER, ol_amount INTEGER, PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number)) WITHOUT ROWID; CREATE TABLE history (h_c_w_id INTEGER, h_c_d_id INTEGER, h_c_id INTEGER, h_w_id INTEGER, h_d_id INTEGER, h_date REAL, - h_amount REAL); + h_amount INTEGER); ''' ORDERS = 10000000 # an order's key is its district's times this, plus its number @@ -142,7 +142,7 @@ class Pure: continue chunk = max(1, (1400000 // 8) // len(data.FIELDS[name]) - 1) for at in range(0, len(rows), chunk): - status, body = self.call('POST', f'/inputs/load_{name}', data.packed(rows[at:at + chunk]), + status, body = self.call('POST', f'/inputs/load_{name}', data.packed(name, rows[at:at + chunk]), 'application/octet-stream') assert status == 200, (name, status, body[:200]) @@ -254,19 +254,16 @@ def asked(pure, name, page=200000): return rows -def same(a, b): - return a == b or abs(a - b) <= 1e-9 * max(1.0, abs(a), abs(b)) - - def compare(table, pure_rows, sqlite_rows_): - """Rows that differ between the two (a table's rows in key order; history's sorted).""" + """Rows that differ between the two (a table's rows in key order; history's sorted): every value compared is an + integer (money in cents, Pure 027's stage 5), so equal exactly.""" if table in ('lines',): # a line's delivery time differs; whether it has one must not pure_rows = [(*r[:-1], r[-1] > 0) for r in pure_rows] sqlite_rows_ = [(*r[:-1], r[-1] > 0) for r in sqlite_rows_] pure_rows, sqlite_rows_ = sorted(pure_rows), sorted(sqlite_rows_) if len(pure_rows) != len(sqlite_rows_): return [('count', len(pure_rows), len(sqlite_rows_))] - return [(p, s) for p, s in zip(pure_rows, sqlite_rows_) if not all(same(x, y) for x, y in zip(p, s))] + return [(p, s) for p, s in zip(pure_rows, sqlite_rows_) if p != s] def device_written(directory): @@ -294,7 +291,7 @@ def consistency(pure, db, sqlite_only=False): sides = [] if sqlite_only else [('pure', asked(pure, 'district_check'))] for system, districts in sides + [('sqlite', db.execute(DISTRICTS[1]).fetchall())]: for key, next_order, newest, newest_new, oldest_new, new_orders, ordered, lines, ytd, district_ytd in districts: - if not same(ytd, district_ytd): + if ytd != district_ytd: failures.append((system, '3.3.2.1', ytd, district_ytd)) if not next_order - 1 == newest == newest_new: failures.append((system, '3.3.2.2', key, next_order, newest, newest_new)) diff --git a/benchmarks/tpcc/sqlite-tpcc.c b/benchmarks/tpcc/sqlite-tpcc.c index f497ae5..fb969fc 100644 --- a/benchmarks/tpcc/sqlite-tpcc.c +++ b/benchmarks/tpcc/sqlite-tpcc.c @@ -16,7 +16,7 @@ * commit, as pure-serve shows views from the last save (docs/research/106). * Departures as in tpcc.aup: customers by number, no texts carried along, * a customer's last order kept in c_last_order, Delivery a district a - * transaction. With TRANSACTIONS each terminal stops after that many; the + * transaction; money in cents and rates in ten-thousandths, as INTEGERs. With TRANSACTIONS each terminal stops after that many; the * inputs are drawn in tpcc-load's order. SQLITE_PRAGMAS, when set, is run on * each connection after its own (mmap_size, cache_size). Prints one JSON line * as tpcc-load does, with the CPU seconds of the run, the peak resident @@ -180,13 +180,13 @@ static bool read_only(Terminal *t, int kind, int w, int d, uint64_t *r) { sqlite3_bind_int(s, 1, w), sqlite3_bind_int(s, 2, d), sqlite3_bind_int(s, 3, nurand(r, 1023, 1, 3000, 259)); if (!step(s, &row) || !row) return false; sqlite3_int64 order = sqlite3_column_int64(s, 1); - (void)sqlite3_column_double(s, 0); + (void)sqlite3_column_int64(s, 0); sqlite3_reset(s); s = t->lines_of; sqlite3_reset(s); sqlite3_bind_int(s, 1, w), sqlite3_bind_int(s, 2, d), sqlite3_bind_int64(s, 3, order); int result; - while ((result = sqlite3_step(s)) == SQLITE_ROW) (void)sqlite3_column_double(s, 3); + while ((result = sqlite3_step(s)) == SQLITE_ROW) (void)sqlite3_column_int64(s, 3); sqlite3_reset(s); return result == SQLITE_DONE; } @@ -239,7 +239,7 @@ static bool body(Terminal *t, int kind, int w, int d, uint64_t *r, bool *undo) { *undo = unused; return unused; } - double price = sqlite3_column_double(s, 0); + sqlite3_int64 price = sqlite3_column_int64(s, 0); /* cents */ sqlite3_reset(s); s = t->stock_of; sqlite3_reset(s); @@ -259,7 +259,7 @@ static bool body(Terminal *t, int kind, int w, int d, uint64_t *r, bool *undo) { sqlite3_reset(s); sqlite3_bind_int(s, 1, w), sqlite3_bind_int(s, 2, d), sqlite3_bind_int64(s, 3, order), sqlite3_bind_int(s, 4, n); sqlite3_bind_int(s, 5, item), sqlite3_bind_int(s, 6, supply), sqlite3_bind_int(s, 7, quantity); - sqlite3_bind_double(s, 8, quantity * price); + sqlite3_bind_int64(s, 8, quantity * price); ok = ok && RUN(s); } s = t->order_add; @@ -281,23 +281,23 @@ static bool body(Terminal *t, int kind, int w, int d, uint64_t *r, bool *undo) { do cw = uniform(r, 1, warehouses); while (cw == w); cd = uniform(r, 1, 10); } - double amount = uniform(r, 100, 500000) / 100.0; + sqlite3_int64 amount = uniform(r, 100, 500000); /* cents */ sqlite3_stmt *s = t->warehouse_pay; sqlite3_reset(s); - sqlite3_bind_int(s, 1, w), sqlite3_bind_double(s, 2, amount); + sqlite3_bind_int(s, 1, w), sqlite3_bind_int64(s, 2, amount); ok = RUN(s); s = t->district_pay; sqlite3_reset(s); - sqlite3_bind_int(s, 1, w), sqlite3_bind_int(s, 2, d), sqlite3_bind_double(s, 3, amount); + sqlite3_bind_int(s, 1, w), sqlite3_bind_int(s, 2, d), sqlite3_bind_int64(s, 3, amount); ok = ok && RUN(s); s = t->customer_pay; sqlite3_reset(s); - sqlite3_bind_int(s, 1, cw), sqlite3_bind_int(s, 2, cd), sqlite3_bind_int(s, 3, c), sqlite3_bind_double(s, 4, amount); + sqlite3_bind_int(s, 1, cw), sqlite3_bind_int(s, 2, cd), sqlite3_bind_int(s, 3, c), sqlite3_bind_int64(s, 4, amount); ok = ok && RUN(s) && sqlite3_changes(t->db) == 1; s = t->history_add; sqlite3_reset(s); sqlite3_bind_int(s, 1, cw), sqlite3_bind_int(s, 2, cd), sqlite3_bind_int(s, 3, c), sqlite3_bind_int(s, 4, w); - sqlite3_bind_int(s, 5, d), sqlite3_bind_double(s, 6, now_ms()), sqlite3_bind_double(s, 7, amount); + sqlite3_bind_int(s, 5, d), sqlite3_bind_double(s, 6, now_ms()), sqlite3_bind_int64(s, 7, amount); ok = ok && RUN(s); } else { /* DELIVERY, one district */ int carrier = uniform(r, 1, 10); @@ -332,11 +332,11 @@ static bool body(Terminal *t, int kind, int w, int d, uint64_t *r, bool *undo) { sqlite3_reset(s); sqlite3_bind_int(s, 1, w), sqlite3_bind_int(s, 2, d), sqlite3_bind_int64(s, 3, order); ok = ok && step(s, &row) && row; - double total = ok ? sqlite3_column_double(s, 0) : 0; + sqlite3_int64 total = ok ? sqlite3_column_int64(s, 0) : 0; sqlite3_reset(s); s = t->customer_deliver; sqlite3_reset(s); - sqlite3_bind_int(s, 1, w), sqlite3_bind_int(s, 2, d), sqlite3_bind_int(s, 3, c), sqlite3_bind_double(s, 4, total); + sqlite3_bind_int(s, 1, w), sqlite3_bind_int(s, 2, d), sqlite3_bind_int(s, 3, c), sqlite3_bind_int64(s, 4, total); ok = ok && RUN(s); } } diff --git a/benchmarks/tpcc/tpcc-load.c b/benchmarks/tpcc/tpcc-load.c index 176ec1e..d3cee99 100644 --- a/benchmarks/tpcc/tpcc-load.c +++ b/benchmarks/tpcc/tpcc-load.c @@ -118,8 +118,8 @@ static void prepare(Connection *c) { do cw = uniform(r, 1, warehouses); while (cw == w); cd = uniform(r, 1, 10); } - double amount = uniform(r, 100, 500000) / 100.0; - length = snprintf(body, sizeof(body), "{\"w\":%d,\"d\":%d,\"cw\":%d,\"cd\":%d,\"c\":%d,\"amount\":%.2f}", w, d, cw, + int amount = uniform(r, 100, 500000); /* cents */ + length = snprintf(body, sizeof(body), "{\"w\":%d,\"d\":%d,\"cw\":%d,\"cd\":%d,\"c\":%d,\"amount\":%d}", w, d, cw, cd, customer, amount); path = "/inputs/payment"; } else if (c->kind == DELIVERY) { diff --git a/benchmarks/tpcc/tpcc.aup b/benchmarks/tpcc/tpcc.aup index b3e75aa..a67dc2e 100644 --- a/benchmarks/tpcc/tpcc.aup +++ b/benchmarks/tpcc/tpcc.aup @@ -3,6 +3,10 @@ # w * 10 + d, a customer district * 3000 + c, an order district * 10^7 + o, # an order line order * 16 + number, a stock item w * 100000 + i. # +# Money is an i64 of cents and a rate (tax, discount) an i64 of +# ten-thousandths (the specification's decimals, exactly: Pure 027's +# stage 5), as SQLite's INTEGER columns hold them. +# # Where this departs from the specification: a customer is always chosen by # number (never by last name), texts the transactions only carry along # (names, addresses, data) are left out, a customer keeps its last order, @@ -11,29 +15,29 @@ record Warehouse { id = 0 - tax = 0 - ytd = 0 + tax: i64 + ytd: i64 } record District { id = 0 - tax = 0 - ytd = 0 + tax: i64 + ytd: i64 next_order = 3001 next_delivery = 2101 } record Customer { id = 0 - discount = 0 + discount: i64 credit = 1 - balance = -10 - ytd_payment = 10 + balance: i64 = -1000 + ytd_payment: i64 = 1000 payments = 1 deliveries = 0 last_order = 0 } record Item { id = 0 - price = 0 + price: i64 } record Stock { id = 0 @@ -59,13 +63,13 @@ record Line { supply = 0 delivery = 0 quantity = 0 - amount = 0 + amount: i64 } record History { customer = 0 district = 0 date = 0 - amount = 0 + amount: i64 } record Ordered { item = 0 @@ -183,7 +187,7 @@ on new_order { } count = count + 1 lines.add(Line { id: order * 16 + count, item: l.item, supply: l.supply, quantity: l.quantity, - amount: l.quantity * price }) + amount: i64(l.quantity) * price }) } orders.add(Order { id: order, customer: customer.id, entry: now, lines: count, local: local }) new_orders.add(NewOrder { id: order }) @@ -198,7 +202,7 @@ input payment = message { cw: u32 cd: u32 c: u32 - amount: number + amount: i64 } on payment { let warehouse = warehouses[payment.w] @@ -227,7 +231,7 @@ on delivery { new_orders.remove(new_orders[order]) let o = orders[order] o.carrier = delivery.carrier - let total = 0 + let total = i64(0) for l in lines[order * 16 .. order * 16 + 16] { l.delivery = now total = total + l.amount -- cgit v1.2.3