Training with PDO and SQL

create_user_kata.php

<?php

$db = new PDO('mysql:unix_socket=/tmp/mysql.sock;dbname=mybdd', 'root', 'root');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/**
CREATE SCHEMA `mybdd`;
CREATE TABLE `users` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `firstname` VARCHAR(255) NOT NULL,
    `lastname` VARCHAR(255) NOT NULL,
    `password` CHAR(60) BINARY NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `email` (`email`)
)
ENGINE = InnoDB,
DEFAULT CHARSET = utf8;

CREATE TABLE `logs` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `entity` VARCHAR(255) NOT NULL,
    `entity_id` INT UNSIGNED NOT NULL,
    `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB,
DEFAULT CHARSET = utf8;
*/

function count_users() {
    global $db;

    $result = $db->query('SELECT COUNT(*) FROM users');
    return (int) $result->fetchColumn();
}

function create_user($firstname, $lastname, $email) {
    global $db;

    $db->beginTransaction();

    if (is_exists_user_by_fullname($firstname . ' ' . $lastname)) {
        $db->rollback();
        return;
    }

    $stmt = $db->prepare('INSERT INTO users (`firstname`, `lastname`, `email`, `password`, `created_at`)
        VALUES (:firstname, :lastname, :email, :password, :created_at)');

    $result = $stmt->execute(
        array(
            'firstname'  => $firstname,
            'lastname'   => $lastname,
            'email'      => $email,
            'password'   => generate_password(),
            'created_at' => date('Y-m-d H:i:s'),
        )
    );

    log_action('user', $db->lastInsertId());

    if ((int) $result !== 1) {
        throw new \RuntimeException('Cannot create user');
    }

    $db->commit();
}

function is_exists_user_by_fullname($fullname) {
    global $db;

    $row = $db->query('SELECT COUNT(*) FROM users WHERE CONCAT(firstname, " ", lastname) = ' . $db->quote($fullname));
    return (int) $row->fetchColumn();
}

function generate_password() {
    return substr(base64_encode(sha1(uniqid(true))), 0, 60);
}

function log_action($entity, $entityId) {
    global $db;

    $stmt = $db->prepare('INSERT INTO `logs` (`entity`, `entity_id`, `created_at`)
        VALUES (:entity, :entity_id, :created_at)');

    $result = $stmt->execute(array('entity' => $entity, 'entity_id' => $entityId, 'created_at' => date('Y-m-d H:i:s')));
    if ((int) $result === 0) {
        throw new \RuntimeException('Cannot log action');
    }
}

function delete_all_users() {
    global $db;

    $db->exec('DELETE FROM users');
}

if (count_users() === 0) {
    create_user('Kevin', 'Le Brun', 'lebrun.k@gmail.com');
    create_user('Kevin', 'Le Brun', 'kevin@kevinlebrun.fr');
}

$rows = $db->query('SELECT * FROM users');
while($user = $rows->fetch(PDO::FETCH_ASSOC)) {
    var_dump($user);
}

delete_all_users();