<?php
/**
 * Primary keys can be a pain in the neck during multi-master replication
 * This script can ensure that the primary keys on any two different
 * postgresql databases which are to be replicated in a multi-master
 * fashion are always different. How? Just run this script on the
 * first database to covert all its sequences to even numbers and
 * run it again on the second database to convert all its sequences to
 * odd numbers. The script ajusts the increments of the sequences so
 * they would forever generate either even or odd numbers.
 *
 *
 * Used this script with bucardo's swap sync.
 *
 * Usage
 * =====
 * php update_sequences.php [OPTIONS]
 * --host <hostname>        The database host
 * --user <username>        The database username
 * --password <password>    The password of the user (unsecure right?)
 * --db <database>          The name of the database
 * --mode <mode>            The mode of conversion should be even or odd
 *
 * Note that the mode option doesn't validate.
 */

// Add the sequences you dont want affected here. The schema doesn't
// really matter.
$exclude = array(
    'client_code_seq',
    'jv_number_seq',
    'pv_number_seq',
);

for ($i = 1; $i < $argc; $i++)
{
    if(substr($argv[$i], 0, 2) == "--")
    {
        $parameter = substr($argv[$i], 2, strlen($argv[$i]) - 2);
        $i++;
        $$parameter = $argv[$i];
    }
    else
    {
        $tables[] = $argv[$i];
    }
}

$conn = pg_connect("host=$host port=5432 dbname=$db user=$user password=$password");

pg_query($conn,"BEGIN");
$sequences_result = pg_query($conn, "SELECT * FROM information_schema.sequences");
$sequences = array();
while($sequence = pg_fetch_assoc($sequences_result))
{
    if(array_search($sequence['sequence_name'], $exclude) !== false)
    {
        echo "Skipping " . $sequence['sequence_schema'] . '.' . $sequence['sequence_name'] . "\n";
        continue;
    }
    $sequence = $sequence['sequence_schema'] . '.' . $sequence['sequence_name'];
    $value = pg_query($conn, "SELECT last_value from $sequence");
    $value = pg_fetch_assoc($value);

    pg_query($conn, "ALTER SEQUENCE $sequence INCREMENT BY 2");

    if($value["last_value"] % 2 == 0 && $mode == 'odd')
    {
        pg_query($conn, "ALTER SEQUENCE $sequence RESTART WITH " . ($value['last_value'] + 1));
    }
    elseif(($value["last_value"] % 2 == 1 || $value["last_value"] == 1) && $mode == 'even')
    {
        pg_query($conn, "ALTER SEQUENCE $sequence RESTART WITH " . ($value['last_value'] + 1));
    }
}
pg_query($conn,"COMMIT");