v1) OR (k1 = v1 AND k2 > v2) ... ORDER BY k1, k2 LIMIT n. * The row-constructor form (k1,k2) > (v1,v2) is avoided because older MariaDB * versions don't use the index for it. * - Without a usable key: LIMIT/OFFSET, flagged as "may be inconsistent if written during backup". * * A chunk stops at the byte budget or the time deadline, whichever comes first. * * Values larger than BIG_VALUE bytes are never loaded here: the row is exported with an * empty placeholder and the value is listed in 'bigs', to be fetched separately in pieces * (see the db_value operation). This keeps memory bounded on small hosting plans. * * @package AccessNow\Replica */ namespace AccessNow\Replica\Db; defined( 'ABSPATH' ) || exit; /** * Chunked table export. */ final class Table_Exporter { const BIG_VALUE = 2097152; /** * The smallest size above which a value is fetched separately rather than in its batch. */ const MIN_CAP = 65536; /** * Connection. * * @var Connection */ private $db; /** * Writer. * * @var Sql_Writer */ private $writer; /** * Creates the exporter. * * @param Connection $db Connection. * @param int $stmt_bytes Statement cap. */ public function __construct( Connection $db, $stmt_bytes ) { $this->db = $db; $this->writer = new Sql_Writer( $db, $stmt_bytes ); } /** * Exports rows after a cursor. * * @param string $table Table. * @param array $columns Columns (from Schema_Reader::columns), generated ones are skipped. * @param array $key Paging key (kind, columns). * @param array|null $cursor Null to start; {after: [...]} or {offset: n} to continue. * @param int $budget Target SQL bytes. * @param float $deadline microtime(true) deadline. * @param array $where Extra WHERE condition: array( SQL with placeholders, values ), or empty. * @param int $avg_row Average row length hint. * @return array{sql: string, rows: int, next: array|null, first: array|null, last: array|null, bigs: array[]} */ public function chunk( $table, array $columns, array $key, $cursor, $budget, $deadline, array $where = array(), $avg_row = 0 ) { $cols = array_values( array_filter( $columns, static function ( $c ) { return empty( $c['generated'] ); } ) ); $index = array(); foreach ( $cols as $i => $c ) { $index[ $c['name'] ] = $i; } $keyset = in_array( $key['kind'], array( 'primary', 'unique' ), true ) && $key['columns']; foreach ( $key['columns'] as $kc ) { if ( ! isset( $index[ $kc ] ) ) { $keyset = false; // Key on a generated column: fall back to offsets. } } // Big values can only be fetched separately when the row can be addressed by its key. Rows // are read one buffered batch at a time, so a value is left out of its batch (and fetched in // pieces) above a cap that shrinks as the batch grows: a batch holds at most about twice the // budget in large values, and a batch with large columns is never many rows long. $has_large = false; foreach ( $cols as $c ) { $has_large = $has_large || ! empty( $c['large'] ); } $select = function ( $cap ) use ( $cols, $keyset ) { $names = array(); $name_args = array(); $large = array(); foreach ( $cols as $i => $c ) { if ( $keyset && ! empty( $c['large'] ) ) { $names[] = 'IF(OCTET_LENGTH(%i) > %d, NULL, %i)'; $name_args[] = $c['name']; $name_args[] = $cap; $name_args[] = $c['name']; $large[ $i ] = count( $cols ) + count( $large ); } else { $names[] = '%i'; $name_args[] = $c['name']; } } foreach ( $large as $i => $flag_pos ) { $names[] = '(OCTET_LENGTH(%i) > %d)'; $name_args[] = $cols[ $i ]['name']; $name_args[] = $cap; } return array( $names, $name_args, $large ); }; $max_batch = 5000; if ( $has_large ) { // Keyset tables fetch big values separately, so their batches stay short; tables that // can't (no usable key) read whole rows, so theirs are shorter still. $max_batch = $keyset ? max( 20, (int) ( ( 4 * $budget ) / self::MIN_CAP ) ) : 20; } $bigs = array(); $batch = max( 1, min( $max_batch, max( 20, (int) ( $budget / max( 200, (int) $avg_row ) ) ) ) ); $tuples = array(); $bytes = 0; $rows = 0; $first = null; $last = null; $after = ( $keyset && isset( $cursor['after'] ) && is_array( $cursor['after'] ) ) ? $cursor['after'] : null; $offset = ( ! $keyset && isset( $cursor['offset'] ) ) ? max( 0, (int) $cursor['offset'] ) : 0; $done = false; while ( true ) { $cap = max( self::MIN_CAP, min( self::BIG_VALUE, (int) ( ( 2 * $budget ) / max( 1, $batch ) ) ) ); list( $names, $name_args, $large ) = $select( $cap ); $args = $name_args; $args[] = $table; $conds = array(); if ( $where ) { $conds[] = '(' . $where[0] . ')'; $args = array_merge( $args, $where[1] ); } if ( $keyset && null !== $after ) { list( $after_sql, $after_args ) = $this->after_condition( $key['columns'], $after ); $conds[] = '(' . $after_sql . ')'; $args = array_merge( $args, $after_args ); } $order = ''; if ( $keyset ) { $order = ' ORDER BY ' . implode( ',', array_fill( 0, count( $key['columns'] ), '%i' ) ); $args = array_merge( $args, $key['columns'] ); } $sql = 'SELECT ' . implode( ',', $names ) . ' FROM %i' . ( $conds ? ' WHERE ' . implode( ' AND ', $conds ) : '' ) . $order . ' LIMIT %d' . ( $keyset ? '' : ' OFFSET %d' ); $args[] = (int) $batch; if ( ! $keyset ) { $args[] = (int) $offset; } // The answer stops at the byte budget even part-way through a batch (at least one row). $batch_rows = $this->db->batch( $sql, $args ); $fetched = 0; $cut = false; foreach ( $batch_rows as $row ) { if ( $cut ) { break; } ++$fetched; $row_bigs = array(); foreach ( $large as $i => $flag_pos ) { if ( '1' === (string) $row[ $flag_pos ] ) { $row[ $i ] = ''; // Placeholder; the real value is fetched in pieces. $row_bigs[] = $cols[ $i ]['name']; } } $tuple = $this->writer->tuple( $row, $cols ); $tuples[] = $tuple; $bytes += strlen( $tuple ) + 1; if ( $keyset ) { $last = array(); foreach ( $key['columns'] as $kc ) { $last[] = $row[ $index[ $kc ] ]; } if ( null === $first ) { $first = $last; } foreach ( $row_bigs as $column ) { $bigs[] = array( 'key' => $last, 'column' => $column, ); } } $cut = $bytes >= $budget && $fetched < $batch; } unset( $batch_rows ); $rows += $fetched; $offset += $fetched; if ( $keyset && null !== $last ) { $after = $last; } if ( $cut ) { break; // Budget reached inside the batch: continue after the last row sent. } if ( $fetched < $batch ) { $done = true; break; } if ( $bytes >= $budget || microtime( true ) >= $deadline ) { break; } // Size the next batch to the remaining budget. $per_row = max( 1, (int) ( $bytes / max( 1, $rows ) ) ); $batch = max( 1, min( $max_batch, max( 20, (int) ( ( $budget - $bytes ) / $per_row ) + 1 ) ) ); } $next = null; if ( ! $done ) { $next = $keyset ? array( 'after' => $after ) : array( 'offset' => $offset ); } return array( 'sql' => $this->writer->inserts( $table, $cols, $tuples ), 'rows' => $rows, 'next' => $next, 'first' => $first, 'last' => $last, 'bigs' => $bigs, ); } /** * Condition matching exactly one row by its key values. * * @param string[] $key_columns Key columns. * @param array $values Key values. * @return array{0: string, 1: array} SQL with placeholders, and their values. */ public function key_condition( array $key_columns, array $values ) { $ands = array(); $args = array(); foreach ( $key_columns as $j => $column ) { $ands[] = '%i = %s'; $args[] = $column; $args[] = (string) $values[ $j ]; } return array( implode( ' AND ', $ands ), $args ); } /** * Keyset condition: rows strictly after the given key values. * * @param string[] $key_columns Key columns. * @param array $values Last key values. * @return array{0: string, 1: array} SQL with placeholders, and their values. */ private function after_condition( array $key_columns, array $values ) { $ors = array(); $args = array(); $n = count( $key_columns ); for ( $i = 0; $i < $n; $i++ ) { $ands = array(); for ( $j = 0; $j < $i; $j++ ) { $ands[] = '%i = %s'; $args[] = $key_columns[ $j ]; $args[] = (string) $values[ $j ]; } $ands[] = '%i > %s'; $args[] = $key_columns[ $i ]; $args[] = (string) $values[ $i ]; $ors[] = '(' . implode( ' AND ', $ands ) . ')'; } return array( implode( ' OR ', $ors ), $args ); } }