Draft v0 Kickback SQL transactions and orders

<?MySQL
CREATE TABLE transaction_header
(
    id_time  DATETIME NOT NULL,
    id_rand  BIGINT   NOT NULL,
    status   SMALLINT NOT NULL, # Ex: processing, aborted, committed (TODO: Make a table enumerating the possible values; use foreign key to constrain this field to valid possibilities.)
    PRIMARY  KEY (id_time, id_rand)
);

CREATE TABLE transaction_details
(
    id_time   DATETIME NOT NULL,
    id_rand   BIGINT   NOT NULL,

    # TODO: These might need to be specified differently.
    # Ideally they'd reference rows in MySQL schema's table and column tables.
    # (So far I don't see any keys on MySQL schema's table table, so we might
    # have to do something more complicated to make table references... :/ )
    table_id  INTEGER  NOT NULL,
    column_id INTEGER  NOT NULL,
    ...
    PRIMARY  KEY (id_time, id_rand, table_id, column_id),
    INDEX    index_header (id_time, id_rand),
    FOREIGN  KEY (index_header) REFERENCES transaction_header (id_time, id_rand)
);

CREATE TABLE store
(
    store_ctime         DATETIME NOT NULL,
    store_crand         BIGINT   NOT NULL,
    name                VARCHAR(128),
    ...
    PRIMARY KEY (store_ctime, store_crand),
    FOREIGN KEY (PRIMARY) REFERENCES transaction_header (id_time, id_rand)
);

CREATE TABLE order_header
(
    order_ctime      DATETIME NOT NULL,
    order_crand      BIGINT   NOT NULL,
    ref_store_ctime  DATETIME NOT NULL,
    ref_store_crand  BIGINT   NOT NULL,
    ...

    PRIMARY KEY (order_ctime, order_crand),
    INDEX   index_ref_store (ref_store_ctime, ref_store_crand),
    FOREIGN KEY (PRIMARY) REFERENCES transaction_header (id_time, id_rand),
    FOREIGN KEY (index_ref_store) REFERENCES store (store_ctime, store_crand)
);
?>




<?php
Insertion logic:

// Once you have acquired a transaction ID, you can modify the values
function acquire_transaction_id(MySQLi $conn : &$id_time, &$id_rand) : bool
{
    $salt = ...; // Hash of MAC address xor/plus/minus time of thread start, or something like that.

    assert(sizeof(int) === 8);
    $id_time = get_cpu_hnsecs(); // 64-bit int
    $seed = $id_time + $salt;
    $id_rand = get_random_int32($seed);
    $id_time_str = get_utc_datetime_iso8601_etcetc($id_time);
    // NOTE: Alternative design possibility: just store the 64-bit $id_time
    // value without converting it to a date-time. This reduces the chance of
    // calindrical calculations introducing bizarre errors into the system,
    // as dates might not always be monotonically-increasing (!!).

    // Assuming you have a $conn variable that represents your database connection
    $stmt = $conn->prepare(
        "INSERT INTO transaction
        (id_time, id_rand)
        VALUES (?, ?)"
    );

    if (false === $stmt) {
        error_log($conn->error);
        return false;
    }

    // Bind the variables to the SQL statement
    $success = $stmt->bind_param("si", $id_time_str, $id_rand);

    if (false === $success) {
        error_log($stmt->error);
        $stmt->close();
        return false;
    }

    // Execute the prepared statement
    //
    // In the (already unlikely) event that two transactions are started at the
    // exact same hectonanosecond, then the chances of them colliding will be
    // 1/2^32.
    //
    // So even in the (two-way) worst-case scenario, this loop has a (2^32 - 1)/(2^32)
    // chance of completing successfully on the first iteration.
    //
    // From the perspective of Birthday Problem analysis:
    // 0.5+sqrt(0.25 + 2*ln(2)*2^32) = 77163.24... simultaneous transactions
    // (same hnsec) would be needed to have a 50% chance of causing a collision.
    //
    // So if about 77163 threads all pick the same hectonanosecond to transact
    // upon, there is a 50% chance that this loop will have to ... *gasp* ...
    // execute more than once!
    //
    $count = 0;
    while (true) {
        $success = $stmt->execute();
        if (false !== $success) { // PHP way of checking for truthiness, lol.
            break;
        }

        // TODO: Verify that MYSQL error code 1062 is guaranteed to be the "duplicate key" error.
        // (If not, find some way to differentiate between duplication errors and other errors.)
        if ($stmt->errno !== 1062) {
            error_log($stmt->error);
            $stmt->close();
            return false;
        }

        $count++;

        $seed += $salt; // Make sure this thread and other thread aren't using same seed!
        $id_rand = get_random_int32($seed);
        if ( $count < 5 ) {
            continue;
        }

        if ( $count < 50 ) {
            $id_time++;
        } else {
            $id_time = get_cpu_hnsecs();
        }
        $id_time_str = get_utc_datetime_iso8601_etcetc($id_time);
        continue;
    }
    return true;
}

?>