Location: PHPKode > scripts > PHPTracker > tcz-PHPTracker-ae73426/lib/PHPTracker/Persistence/Mysql.php
<?php

class PHPTracker_Persistence_Mysql implements PHPTracker_Persistence_Interface, PHPTracker_Persistence_ResetWhenForking
{
    /**
     * Config instance for this class containing database connection data.
     *
     * @var PHPTracker_Config_Interface
     */
    protected $config;

    /**
     * Lazy-initialized database connection.
     *
     * @var mysqli
     */
    protected $connection;

    /**
     * Setting up object instance with the config.
     *
     * @param PHPTracker_Config_Interface $config
     */
    public function __construct( PHPTracker_Config_Interface $config )
    {
        $this->config = $config;
    }

    /**
     * Save all accessible keys of a PHPTracker_Torrent object to be able to recreate it.
     *
     * Use info_hash property as unique key and overwrite attributes when saved
     * multiple times with the same info hash.
     *
     * @param PHPTracker_Torrent $torrent 
     */
    public function saveTorrent( PHPTracker_Torrent $torrent )
    {
        $sql = <<<SQL
INSERT INTO
    `phptracker_torrents`
SET
    `info_hash`     = :info_hash,
    `length`        = :length,
    `pieces_length` = :pieces_length,
    `pieces`        = :pieces,
    `name`          = :name,
    `path`          = :path
ON DUPLICATE KEY UPDATE
    `info_hash`     = VALUES( `info_hash` ),
    `length`        = VALUES( `length` ),
    `pieces_length` = VALUES( `pieces_length` ),
    `pieces`        = VALUES( `pieces` ),
    `name`          = VALUES( `name` ),
    `path`          = VALUES( `path` )
SQL;

        $this->query( $sql, array(
            ':info_hash'         => $torrent->info_hash,
            ':length'            => $torrent->length,
            ':pieces_length'     => $torrent->size_piece,
            ':pieces'            => $torrent->pieces,
            ':name'              => $torrent->name,
            ':path'              => $torrent->file_path,
        ) );
    }

    /**
     * Given a 20 bytes info hash, return an intialized PHPTracker_Torrent object.
     *
     * Must return null if the info hash is not found.
     *
     * @param string $info_hash
     * @return PHPTracker_Torrent 
     */
    public function getTorrent( $info_hash )
    {
        $sql = <<<SQL
SELECT
    `info_hash`,
    `length`,
    `pieces_length`,
    `pieces`,
    `name`,
    `path`
FROM
    `phptracker_torrents`
WHERE
    `info_hash` = :info_hash
    AND
    `status` = 'active'
SQL;

        $results = $this->query( $sql, array(
            ':info_hash'         => $info_hash,
        ) );

        if ( 1 == count( $results ) )
        {
            return new PHPTracker_Torrent(
                new PHPTracker_File_File( $results[0]['path'] ),
                $results[0]['pieces_length'],
                $results[0]['path'],
                $results[0]['name'],
                $results[0]['length'],
                $results[0]['pieces'],
                $results[0]['info_hash']
            );
        }
        return null;
    }

    /**
     * Saves peer announcement from a client.
     *
     * Majority of the parameters of this method come from GET.
     *
     * @param string $info_hash 20 bytes info hash of the announced torrent.
     * @param string $peer_id 20 bytes peer ID of the announcing peer.
     * @param string $ip Dotted IP address of the client.
     * @param integer $port Port number of the client.
     * @param integer $downloaded Already downloaded bytes.
     * @param integer $uploaded Already uploaded bytes.
     * @param integer $left Bytes left to download.
     * @param string $status Can be complete, incomplete or NULL. Incomplete is default for new rows. If once set to complete, NULL does not set it back on update.
     * @param integer $ttl Time to live in seconds meaning the time after we should consider peer offline (if no more updates come).
     */
    public function saveAnnounce( $info_hash, $peer_id, $ip, $port, $downloaded, $uploaded, $left, $status, $ttl )
    {
        $sql = <<<SQL
INSERT INTO
    `phptracker_peers`
SET
    `info_hash`           = :info_hash,
    `peer_id`             = :peer_id,
    `ip_address`          = INET_ATON( :ip ),
    `port`                = :port,
    `bytes_downloaded`    = :downloaded,
    `bytes_uploaded`      = :uploaded,
    `bytes_left`          = :left,
    `status`              = COALESCE( :status, 'incomplete' ),
    `expires`             = CURRENT_TIMESTAMP + INTERVAL :interval SECOND
ON DUPLICATE KEY UPDATE
    `ip_address`          = VALUES( `ip_address` ),
    `port`                = VALUES( `port` ),
    `bytes_downloaded`    = VALUES( `bytes_downloaded` ),
    `bytes_uploaded`      = VALUES( `bytes_uploaded` ),
    `bytes_left`          = VALUES( `bytes_left` ),
    `status`              = COALESCE( :status, `status` ),
    `expires`             = VALUES( `expires` )
SQL;
        
        $this->query( $sql, array(
            ':info_hash'    => $info_hash,
            ':peer_id'      => $peer_id,
            ':ip'           => $ip,
            ':port'         => $port,
            ':downloaded'   => $uploaded,
            ':uploaded'     => $downloaded,
            ':left'         => $left,
            ':status'       => $status,
            ':interval'     => isset( $ttl ) ? $ttl : null,
        ) );
    }

    /**
     * Return all the inf_hashes and lengths of the active arrays.
     *
     * @return array An array of arrays having keys 'info_hash' and 'length' accordingly.
     */
    public function getAllInfoHash()
    {
        $sql = <<<SQL
SELECT
    `info_hash`,
    `length`
FROM
    `phptracker_torrents`
WHERE
    `status` = 'active'
SQL;

        return $this->query( $sql, array() );
    }

    /**
     * Gets all the active peers for a torrent.
     *
     * Only considers peers which are not expired (see TTL).
     * Depending on the $compact flag, returns:
     *
     * A.
     * array(
     *  array(
     *      'peer_id' => ... // ID of the peer, if $no_peer_id is false.
     *      'ip' => ... // Dotted IP address of the peer.
     *      'port' => ... // Port number of the peer.
     *  )
     * )
     *
     * B.
     * Nx6 bytes, where each first 4 bytes represent IP address in big-endian long
     * and each last 2 bytes represent port number in big-endian short.
     *
     * @param string $info_hash Info hash of the torrent.
     * @param string $peer_id Peer ID to exclude (peer ID of the client announcing).
     * @param boolean $compact If true, compact peer list format is used.
     * @param bookean $no_peer_id If true, peer is is ommitted from non-compact peer list.
     * @return mixed
     */
    public function getPeers( $info_hash, $peer_id, $compact = false, $no_peer_id = false )
    {
        $sql = <<<SQL
SELECT
    `peer_id`,
    INET_NTOA( `ip_address` ) AS 'ip_address',
    `port`
FROM
    `phptracker_peers`
WHERE
    `info_hash`           = :info_hash
    AND
    `peer_id`             != :peer_id
    AND
    (
        `expires` IS NULL
        OR
        `expires` > CURRENT_TIMESTAMP
    )
SQL;

        $results =  $this->query( $sql, array(
            ':info_hash'    => $info_hash,
            ':peer_id'      => $peer_id,
        ) );

        if ( $compact )
        {
            $return = "";
            foreach ( $results as $row )
            {
                // We cannot select IP long from MySQL directly on 32 bits systems, because in PHP integers are signed.
                $return .= pack( 'N', ip2long( $row['ip_address'] ) );
                $return .= pack( 'n', intval( $row['port'] ) );
            }
        }
        else
        {
            $return = array();
            foreach ( $results as $row )
            {
                $peer = array(  
                    'ip'        => $row['ip_address'],
                    'port'      => $row['port'],
                );
                if ( !$no_peer_id )
                {
                    $peer['peer id'] = $row['peer_id'];
                }
                $return[] = $peer;
            }
        }
        return $return;
    }

    /**
     * Returns statistics of seeders and leechers of a torrent.
     *
     * Only considers peers which are not expired (see TTL).
     *
     * @param string $info_hash Info hash of the torrent.
     * @param string $peer_id Peer ID to exclude (peer ID of the client announcing).
     * @return array With keys 'complete' and 'incomplete' having counters for each group.
     */
    public function getPeerStats( $info_hash, $peer_id )
    {
        $sql = <<<SQL
SELECT
    COALESCE( SUM( `status` = 'complete' ), 0 ) AS 'complete',
    COALESCE( SUM( `status` != 'complete' ), 0 ) AS 'incomplete'
FROM
    `phptracker_peers`
WHERE
    `info_hash`           = :info_hash
    AND
    `peer_id`             != :peer_id
    AND
    (
        `expires` IS NULL
        OR
        `expires` > CURRENT_TIMESTAMP
    )
SQL;

        $results =  $this->query( $sql, array(
            ':info_hash'    => $info_hash,
            ':peer_id'      => $peer_id,
        ) );

        return $results[0];
    }

    /**
     * Performs a MySQL database query and returns resultset.
     *
     * @param string $query_string Query string with placeholders.
     * @param array $parameters Parameters to replace placeholders in the query string (keys => values). They get escaped.
     * @throws PHPTracker_Persistence_Error In case of DB error.
     * @return PHPTracker_Persistence_MysqlResult Could also return true if there is not resultset.
     */
    protected function query( $query_string, array $parameters )
    {
        $this->lazyConnect();

        foreach ( $parameters as &$parameter )
        {
            if ( is_null( $parameter ) )
            {
                $parameter = 'NULL';
            }
            elseif ( is_numeric( $parameter ) )
            {
                // Locale unaware number representation.
                $parameter = sprintf( '%.12F', $parameter );
                if ( false !== strpos( $parameter, '.' ) )
                {
                    $parameter = rtrim( rtrim( $parameter, '0' ), '.' );
                }
            }
            else
            {
                $parameter = "'" . mysqli_real_escape_string( $this->connection, $parameter ) . "'";
            }
        }

        $prepared_query_string = strtr( $query_string, $parameters );

        if ( false === ( $results = $this->connection->query( $prepared_query_string ) ))
        {
            throw new PHPTracker_Persistence_Error( "Error while performing mysql query.\n" . $this->connection->error . "\n" . $prepared_query_string );
        }

        if ( is_bool( $results ) )
        {
            return $results;
        }

        return new PHPTracker_Persistence_MysqlResult( $results );
    }

    /**
     * If there is no DB connection established yet, it connects and populates self::$connection attribute.
     *
     * Also "wakes up" connection if it has gone away.
     */
    protected function lazyConnect()
    {
        if ( isset( $this->connection ) )
        {
            // Connection might have gone away.
            $this->connection->ping();
            return;
        }

        list( $host, $user, $password, $database ) = $this->config->getMulti( array(
            'db_host',
            'db_user',
            'db_password',
            'db_name'
        ) );

        $this->connection = new mysqli( $host, $user, $password, $password );

        if ( mysqli_connect_errno() )
        {
            throw new PHPTracker_Persistence_Error( 'Unable to connect to mysql database: ' . mysqli_connect_error() );
        }
    }

    /**
     * If the object is used in a forked child process, this method is called after forking.
     *
     * Re-establishes the connection for the fork.
     *
     * @see PHPTracker_Persistence_ResetWhenForking
     */
    public function resetAfterForking()
    {
        $this->connection = null;
    }
}

?>
Return current item: PHPTracker