Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/usr/bin/php
- <?php
- // dev
- error_reporting(-1);
- ini_set('display_errors', 'On');
- // sin limite de memoria
- ini_set('memory_limit', -1);
- // ayuda
- $help = <<<HELP;
- sql-chunk
- Split a table or more into subtables
- Options
- -F / --server-from
- PDO query string to read from
- mandatory
- i.e "mysql:host=localhost;dbname=test;user=root;pass=p0wn"
- * for simplicity optional nonstandar user and pass has been added to dsn
- -T / --server-to
- PDO query string to write to
- Optional default server-from
- * for simplicity optional nonstandar user and pass has been added to dsn
- -t / --table
- Table name prefix to read from
- mandatory
- -o / --old-count
- Read table count
- Optional default 1
- * if one reads from %table, if more read from %table%sep%i
- -n / --new-count
- Write table count
- Optional default 1
- * if one writes to %table, if more write to %table%sep%i
- -s / --separator
- Separator between table name and index
- Optional default empty
- -k / --key
- Split key
- Optional default id
- -c / --clean
- Wether to clean or not original table
- * DELETE rows if it's being used as destination or TRUNCATE if not used)
- Examples
- Copy every row from table posts to tables posts_(%row % 100) and truncate posts when finished (localhost)
- sql-chunk -t posts -s _ -n 100 -c
- Copy every row from table posts_%range{0,99} to tables posts_(%row % 1000) and delete old rows from posts_%range{0,99} when
- finished, reading from computer1 and writing to computer2
- sql-chunk -t posts -s _ -o 100 -n 1000 -c -F "mysql://computer1" -T "mysql://computer2"
- HELP;
- // obtener opciones
- $opt = getopt('F:T:t:o:n:s:k:c', array(
- 'server-from::',
- 'server-to::',
- 'table:',
- 'old-count::',
- 'new-count::',
- 'separator::',
- 'key',
- 'clean'
- ));
- // mapear short-opts a long-opts
- $map = array(
- 'F' => 'server-from', 'T' => 'server-to', 't' => 'table',
- 'o' => 'old-count', 'n' => 'new-count', 's' => 'separator', 'k' => 'key',
- 'c' => 'clean'
- );
- foreach ($map as $key => $value) {
- if (isset($opt[$key])) {
- $opt[$value] = $opt[$key];
- }
- }
- unset($map, $key, $value);
- // table y server-from son mandatorios, imprimir ayuda
- if (!isset($opt['table'], $opt['server-from'])) {
- print $help;
- exit;
- }
- // no hay server-to, default server-from
- if (!isset($opt['server-to'])) {
- $opt['server-to'] = $opt['server-from'];
- }
- // no hay old-count, default 1
- if (!isset($opt['old-count'])) {
- $opt['old-count'] = 1;
- }
- // no hay new-count, default 1
- if (!isset($opt['new-count'])) {
- $opt['new-count'] = 1;
- }
- // old-count y new-count son iguales... nada que hacer
- if ($opt['old-count'] == $opt['new-count']) {
- print 'Old and new table count are equal, nothing to do';
- exit;
- }
- // no hay separador, usar vacio
- if (!isset($opt['separator'])) {
- $opt['separator'] = '';
- }
- // no hay shard key, usar id
- if (!isset($opt['key'])) {
- $opt['key'] = 'id';
- }
- // hay que limpiar?
- $clean = isset($opt['clean']);
- // conecta a un servidor
- function connect($dsn) {
- // dsn no standar
- $re = '/(user|pass)=([^;]+)/';
- // extraer user y pass
- $count = preg_match_all($re, $dsn, $match);
- if ($count !== 2) {
- print 'Invalid dsn string' . "\n";
- exit;
- }
- ${$match[1][0]} = $match[2][0];
- ${$match[1][1]} = $match[2][1];
- // dsn limpio
- $cleanDsn = preg_replace($re, '', $dsn);
- $cleanDsn = trim($cleanDsn, ';');
- try {
- $connection = @new PDO($cleanDsn, $user, $pass);
- } catch (PDOException $e) {
- print 'Cannot connect to ' . $dsn . "\n";
- exit;
- }
- return $connection;
- }
- // devuelve el nombre de una tabla basado en el separador y el tamano del shard
- function getTable($table, $separator, $tableNo) {
- if ($count > 1) {
- // si es mas de una tabla usar $table$separator$tableNumber
- return $tableName . $separator . $tableNo;
- } else {
- // si es una sola tabla usar $table
- return $table;
- }
- }
- // conectar a los servidores de lectura y escritura
- $read = connect($opt['server-from']);
- $write = connect($opt['server-to']);
- for ($i = 0; $i < $opt['old-count']; ++$i) {
- // recorrer las tablas de lectura
- $fromTableName = getTable($opt['table'], $opt['separator'], $i);
- print 'Reading from ' . $fromTableName . "\n";
- $query = $read->query('SELECT * FROM ' . $fromTableName);
- if (!$query) {
- // el query de lectura fallo, salir
- print 'Read query failed' . "\n";
- exit;
- }
- foreach ($query as $row) {
- if (!isset($row[$opt['key']])) {
- // no existe la key de shard, salir
- print 'Invalid key, no such column ' . $opt['key'] . "\n";
- exit;
- }
- // preparar query para insertar
- $toTableName = getTable($opt['table'], $opt['separator'], $row[$opt['key']] % $opt['new-count']);
- print 'Writing to ' . $toTableName . "\n";
- $columns = join(', ', array_keys($row));
- $values = join(', ', array_map(function($value) {
- return "'" . addslashes($value) . "'";
- }, $row));
- $insert = $write->query('INSERT INTO ' . $toTableName . ' (' . $columns . ') VALUES (' . $values . ')');
- if (!$insert) {
- // fallo el insert, lo registramos pero no salimos
- print 'Failed insert for key ' . $row[$opt['key']] . "\n";
- }
- }
- if ($clean) {
- // hay que limpiar
- print 'Cleaning ' . $fromTableName . "\n";
- if ($opt['old-count'] == 1) {
- // si es una sola tabla de lectura, borrarla entera
- $read->query('TRUNCATE TABLE ' . $fromTableName);
- } else {
- // si son mas de una borrar solo los rows que no corresponden
- $delete = $read->query("DELETE FROM ' . $fromTableName . ' WHERE " . $opt['key'] . " % " . $opt['new-count'] . " != " . $i);
- if (!$delete) {
- print 'Failed remove for table ' . $i . "\n";
- }
- }
- }
- }
- print 'Ready the chicken' . "\n";
Add Comment
Please, Sign In to add comment