eduVPN / vpn-server-api

        return 1 === $stmt->rowCount();

    }

    public function addCertificate($userId, $commonName, $displayName, $validFrom, $validTo)

    {

        $userId = $this->getId($userId);

        $stmt = $this->db->prepare(

<<< 'SQL'

    INSERT INTO certificates 

        (common_name, user_id, display_name, valid_from, valid_to)

    VALUES

        (:common_name, :user_id, :display_name, :valid_from, :valid_to)

SQL

        );

        $stmt->bindValue(':common_name', $commonName, PDO::PARAM_STR);

        $stmt->bindValue(':user_id', $userId, PDO::PARAM_INT);

        $stmt->bindValue(':display_name', $displayName, PDO::PARAM_STR);

        $stmt->bindValue(':valid_from', $validFrom, PDO::PARAM_INT);

        $stmt->bindValue(':valid_to', $validTo, PDO::PARAM_INT);

        $stmt->execute();

        // XXX

        return 1 === $stmt->rowCount();

    }

    public function getCertificates($userId)

    {

        return $stmt->fetchAll(PDO::FETCH_ASSOC);

    }

    public function clientConnect($profileId, $commonName, $ip4, $ip6, $connectedAt)

    {

        // this query is so complex, because we want to store the user_id in the

        // log as well, not just the common_name... the user may delete the

        // certificate, or the user account may be deleted...

        $stmt = $this->db->prepare(

<<< 'SQL'

    INSERT INTO connection_log 

        (

            user_id,

            profile_id,

            common_name,

            ip4,

            ip6,

            connected_at

        ) 

    VALUES

        (

            (

                SELECT

                    u.user_id

                FROM 

                    users u, certificates c

                WHERE

                    u.id = c.user_id

                AND

                    c.common_name = :common_name

            ),                

            :profile_id, 

            :common_name,

            :ip4,

            :ip6,

        return $stmt->fetchAll(PDO::FETCH_ASSOC);

    }

    public function addUserMessage($userId, $type, $message, DateTime $dateTime)

    {

        $userId = $this->getId($userId);

        $stmt = $this->db->prepare(

<<< 'SQL'

    INSERT INTO user_messages 

        (user_id, type, message, date_time) 

    VALUES

        (:user_id, :type, :message, :date_time)

SQL

        );

        $stmt->bindValue(':user_id', $userId, PDO::PARAM_INT);

        $stmt->bindValue(':type', $type, PDO::PARAM_STR);

        $stmt->bindValue(':message', $message, PDO::PARAM_STR);

        $stmt->bindValue(':date_time', $dateTime->format('Y-m-d H:i:s'), PDO::PARAM_STR);

        $stmt->execute();

        return 1 === $stmt->rowCount();

    }

    public function init()

    {