PostgreSQL helpers for manipulating tables and sequences

PostgreSql.php

<?php

/**
 * Helper class.
 *
 * This class provides some static methods that are helpful with 
 * postgres like manually manipulating sequences and dropping
 * tables cascaded.
 *
 * It also provides the ability to create a batch insert (COPY)
 * by creating a instance of it per ActiveRecord.
 *
 * @author John Fragkoulis <[email protected]>
 */
class PostgreSql
{
    private static $_db;

    /**
     * [tableName description]
     * @param  [type] $model [description]
     * @return [type]        [description]
     */
    public static function tableName($model)
    {
        $table = $model->tableName();
        if (static::db()->tablePrefix !== null && strpos($table,'{{') !== false)
            return preg_replace('/\{\{(.*?)\}\}/', static::db()->tablePrefix.'$1', $table);
        else
            return $table;
    }

    /**
     * [truncate description]
     * @param  [type] $tables [description]
     * @return [type]        [description]
     */
    public static function truncate($tables)
    {   
        if (!is_array($tables)) {
            $tables = [$tables];
        }

        foreach ($tables as $table) {
            if ($table instanceof CActiveRecord)
                $table = $table->tableName();

            static::db()->createCommand()->setText("TRUNCATE TABLE {$table} CASCADE")->execute();
            static::resetSequence($table);
        }
    }

    /**
     * [getSequence description]
     * @param  [type] $table [description]
     * @return [type]        [description]
     */
    public static function getSequence($table)
    {
        if (!static::sequenceExists($table))
            return null;
        
        if ($table instanceof CActiveRecord)
            $table = $table->tableName();

        // currval && Yii's lastInsertId cause error with psql session
        // $text = "SELECT currval(pg_get_serial_sequence('$table', 'id'))";
        $text = "SELECT last_value FROM {$table}_id_seq";
        return static::db()->createCommand()->setText($text)->queryScalar();
    }

    /**
     * [nextSequence description]
     * @param  [type] $table [description]
     * @return [type]        [description]
     */
    public static function nextSequence($table)
    {
        if (!static::sequenceExists($table))
            return null;

        if ($table instanceof CActiveRecord)
            $table = $table->tableName();

        $text = "SELECT nextval(pg_get_serial_sequence('$table', 'id'))";
        $id = static::db()->createCommand($text)->queryScalar();
        // Yii::log("$table => $id");
        return $id;
    }

    /**
     * [setSequence description]
     * @param [type] $table [description]
     * @param [type] $value [description]
     */
    public static function setSequence($table, $value, $is_called=true, $return=false)
    {
        if (!static::sequenceExists($table))
            return;

        if ($table instanceof CActiveRecord)
            $table = $table->tableName();

        $is_called = $is_called ? 'true' : 'false';
        $text = "SELECT setval(pg_get_serial_sequence('$table', 'id'), $value, $is_called)";
        if ($return)
            return $text;
        else
            static::db()->createCommand()->setText($text)->execute();
    }

    /**
     * [resetSequence description]
     * @param  [type] $table [description]
     * @return [type]        [description]
     */
    public static function resetSequence($table, $clear = true)
    {
        if (!static::sequenceExists($table))
            return;

        if ($clear) {
            $max = 1;
        } else {
            // Get the max id used and set the sequence to that 
            $max = Yii::app()->db->createCommand()
                ->select('max(id)')
                ->from($table)
                ->queryScalar();

            if ($max === null) {
                $clear = true;
                $max = 1;
            }
        }
        static::setSequence($table, $max, !$clear);
    }

    /**
     * [sequenceExists description]
     * @param  [type] $table [description]
     * @return [type]        [description]
     */
    public static function sequenceExists($table)
    {
        if ($table instanceof CActiveRecord)
            $table = $table->tableName();

        $res = static::db()->createCommand()
            ->setText("SELECT 1 FROM pg_class where relname = '{$table}_id_seq'")
            ->queryScalar();

        return $res == 1;
    }

    /**
     * [boolean description]
     * @param  [type] $value [description]
     * @return [type]        [description]
     */
    public static function boolean($value)
    {
        return $value === true ? 't' : 'f';
    }

    /**
     * [escapeValue description]
     * @param  [type] $subject [description]
     * @return [type]          [description]
     */
    public static function escapeValue($subject) 
    {
        static $search = [
            "\b" => "\\b",
            "\n" => "\\n",
            "\r" => "\\r",
            "\f" => "\\f",
            "\t" => "\\t",
            "\v" => "\\v",
            "\digits" => "\\digits",
            "\xdigits" => "\\xdigits",
            "\\" => "\\\\"
        ];
        return strtr($subject, $search);
    }

    /**
     * [sanitizeValue description]
     * @param  [type] $value [description]
     * @return [type]        [description]
     */
    public static function sanitizeValue($value)
    {
        if (is_bool($value)) {
            $value = static::boolean($value);
        } elseif ($value === null || $value === '') {
            $value = '\N';
        } elseif (!is_array($value)) {
            $value = static::escapeValue($value);
        }
        return $value;
    }

    /**
     * [getDb description]
     * @return [type] [description]
     */
    public static function db()
    {
        if (static::$_db === null) {
            static::$_db = Yii::app()->db;
        }
        return static::$_db;
    }
}