simpleWordsCloud/db.php

<?php
require_once 'soundex_fr.php';
require_once 'common.php';

class DataBase
{
	const DEFAULT_DURATION = '+7 day';
	const OPTIONS_DURATION = array(
		'day' => '+1 day',
		'week' => '+7 day',
		'week2' => '+14 day',
		'month' => '+1 month'
	);
	const DEFAULT_SIZE = 3;
	const MAX_SIZE = 9;
	const CLOUD_CODE_LENGTH = 6;
	const CLOUD_CODE_MAXTRY = 10;

	private $db;
	private $cloud;

	public function __construct()
	{
		$this->init();
	}
	/**
	 * Initialize the database
	 *
	 * Store the PDO object in $db private class variable
	 */
	public function init()
	{
		require_once 'dbconfig.php';
		$dsn = "$type:dbname=$dbname;host=$host;port=$port";
		try {
			$this->db = new PDO($dsn, $username, $password);
			$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$this->db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
		} catch (PDOException $e) {
			echo $e->getMessage();
		}
	}
	/**
	 * Check if the database is initialized or not
	 * @return boolean True if db is inizialized
	 */
	public function isInit()
	{
		return isset($this->db);
	}
	/**
	 * Build the database tables, only if not existing
	 */
	public function buildTables()
	{
		$stmt = $this->db->prepare("
			CREATE TABLE IF NOT EXISTS clouds(
				id_cloud serial PRIMARY KEY,
				code     TEXT NOT NULL UNIQUE,
				size     INTEGER NOT NULL DEFAULT 3,
				text     TEXT,
				delete_t TIMESTAMP DEFAULT
					(CURRENT_TIMESTAMP + INTERVAL '" . self::DEFAULT_DURATION . "')
			);
		");
		$stmt->execute();
		$stmt = $this->db->prepare("
			CREATE TABLE IF NOT EXISTS words(
				id_word  serial PRIMARY KEY,
				word     TEXT NOT NULL,
				count    INT DEFAULT 1,
				cloud_id INT NOT NULL,
				FOREIGN KEY (cloud_id)
					REFERENCES clouds(id_cloud) ON UPDATE CASCADE
					ON DELETE CASCADE
			);
		");
		$stmt->execute();
	}
	/**
	 * Add a word to a cloud
	 *
	 * If the cloud is specified, it will be loaded and the word
	 * will be added.
	 * If the cloud isn't specified, the already loaded cloud will be used.
	 * @param string $word  Word to add
	 * @param string|int $cloud Id or code of the cloud
	 */
	public function addWord(string $word, $cloud = null)
	{
		if (empty($word)) {
			return false;
		}
		if (isset($cloud)) {
			if (!$this->loadCloud($cloud)) {
				return false;
			}
		}
		if (!$this->isCloudSet()) {
			return false;
		}

		$stmt = $this->db->prepare("
			SELECT *
			FROM   words
			WHERE  cloud_id = :cid;
		");
		$stmt->bindValue(':cid', $this->cloud['id'], PDO::PARAM_INT);
		$stmt->execute();
		while($data = $stmt->fetch()) {
			if (areWordsSimilar($data['word'], $word)) {
				$word = $data['word'];
				$wordId = $data['id_word'];
				break;
			}
		}
		if (isset($wordId)) {
			$stmt = $this->db->prepare("
				UPDATE words
				SET    count = count + 1
				WHERE  id_word = :id;
			");
			$stmt->bindValue(':id', $wordId, PDO::PARAM_INT);
			$stmt->execute();
		} else {
			$stmt = $this->db->prepare("
				INSERT INTO words(word, cloud_id)
				VALUES           (:w, :cid);
			");
			$stmt->bindValue(':w', $word, PDO::PARAM_STR);
			$stmt->bindValue(':cid', $this->cloud['id'], PDO::PARAM_INT);
			$stmt->execute();
		}
	}
	/**
	 * Get the list of the words inside the cloud
	 *
	 * If there is no $code parameter and the cloud isn't already loaded,
	 * return null.
	 *
	 * Return a list of words, orderer from the most used to the least,
	 * with the following fields:
	 * - word     => the word
	 * - count    => the number of occurences of the word
	 * - relative => the relative number of occurences,
	 *               relative to the occurence max
	 * - percent  => the percent of word in the cloud,
	 *               relative to the total number of words
	 * @param  string $code Code of the cloud
	 * @return array|null   The words list
	 */
	public function getWordsList(string $code = null)
	{
		if (isset($code)) {
			$this->loadCloudByCode($code);
		}
		if (!isset($code) && !$this->isCloudSet()) {
			return null;
		}

		$stmt = $this->db->prepare("
			SELECT *
			FROM   words
			JOIN   clouds
			ON     words.cloud_id = clouds.id_cloud
			WHERE  code = :code;
		");
		$stmt->bindValue(':code', $this->cloud['code']);
		$stmt->execute();
		$words  = [];
		$values = [];
		$total  = 0;
		$max    = 0;
		while ($data = $stmt->fetch()) {
			$words[] = array(
				'word' => $data['word'],
				'count' => $data['count'],
			);
			$values[] = $data['count'];
			$total += $data['count'];
			$max = max($max, $data['count']);
		}
		foreach ($words as $key => $word) {
			$words[$key]['relative'] = $word['count'] / $max;
			$words[$key]['percent']  = $word['count'] / $total * 100;
		}

		array_multisort($values, SORT_DESC, $words);
		return $words;
	}
	/**
	 * Create a new cloud
	 *
	 * The function will try to generate a new code for the cloud,
	 * if it fails, the function will return false, otherwise the
	 * cloud local variable will save the new cloud.
	 * @param  string $text     Text associated to the cloud.
	 * @param  int    $size     The number of words asked each time,
	 *                          must be between 1 and MAX_SIZE.
	 * @param  string $duration Duration code, from OPTIONS_DURATION.
	 * @return                  False if error, the cloud if success
	 */
	public function createCloud(
		string $text = '',
		int    $size = null,
		string $duration = null)
	{
		if (!isset($size)) {
			$size = 3;
		} elseif ($size < 1) {
			$size = 1;
		} elseif ($size > self::MAX_SIZE) {
			$size = self::MAX_SIZE;
		}
		if (!isset($duration)) {
			$duration = self::DEFAULT_DURATION;
		} elseif (!key_exists($duration, self::OPTIONS_DURATION)) {
			$duration = self::DEFAULT_DURATION;
		}

		for ($i = 0; $i < self::CLOUD_CODE_MAXTRY; $i++) {
			$code = bin2hex(random_bytes(self::CLOUD_CODE_LENGTH));
			if (!$this->loadCloudByCode($code)) {
				break;
			}
		}
		if ($this->isCloudSet()) {
			return false;
		}
		$delete = date('Y-m-d H:i:s', strtotime(self::OPTIONS_DURATION[$duration]));
		$stmt = $this->db->prepare("
			INSERT INTO clouds(code, text, size, delete_t)
			VALUES      (:code, :text, :size, :delete);
		");
		$stmt->bindValue(':code', $code, PDO::PARAM_STR);
		$stmt->bindValue(':text', $text, PDO::PARAM_STR);
		$stmt->bindValue(':size', $size, PDO::PARAM_INT);
		$stmt->bindValue(':delete', $delete, PDO::PARAM_STR);
		try {
			$stmt->execute();
		} catch (PDOEXception $e) {
			return false;
		}
		$this->cloud = array(
			'id'       => $this->db->lastInsertId(),
			'code'     => $code,
			'size'     => $size,
			'delete_t' => $delete,
			'text'     => $text,
		);
		return $this->cloud;
	}
	public function loadCloud($ref)
	{
		if (is_int($ref)) {
			return $this->loadCloudById($ref);
		} elseif (is_string($ref)) {
			return $this->loadCloudByCode($ref);
		}
	}
	/**
	 * Load the cloud by its id
	 * @param  int $id code of the cloud
	 * @return boolean True if the loading is done, False otherwise
	 */
	public function loadCloudById(int $id)
	{
		$stmt = $this->db->prepare("
			SELECT *
			FROM   clouds
			WHERE  id_cloud = :id;
		");
		$stmt->bindValue(':id', $id, PDO::PARAM_INT);
		$stmt->execute();
		if ($data = $stmt->fetch()) {
			$this->cloud = array(
				'id'   => $data['id_cloud'],
				'code' => $data['code'],
				'size' => $data['size'],
				'delete_t' => $data['delete_t'],
				'text' => $data['text'],
			);
			return true;
		}
		$this->cloud = null;
		return false;
	}
	/**
	 * Load the cloud by its code
	 * @param  string $code code of the cloud
	 * @return boolean      True if the loading is done, False otherwise
	 */
	public function loadCloudByCode(string $code)
	{
		$stmt = $this->db->prepare("
			SELECT *
			FROM   clouds
			WHERE  code = :code;
		");
		$stmt->bindValue(':code', $code, PDO::PARAM_STR);
		$stmt->execute();
		if ($data = $stmt->fetch()) {
			$this->cloud = array(
				'id'       => $data['id_cloud'],
				'code'     => $data['code'],
				'size'     => $data['size'],
				'delete_t' => $data['delete_t'],
				'text'     => $data['text'],
			);
			return true;
		}
		$this->cloud = null;
		return false;
	}
	public function isCloudSet()
	{
		return isset($this->cloud);
	}
	public function getCloudId()
	{
		if ($this->isCloudSet()) {
			return $this->cloud['id'];
		}
		return null;
	}
	public function getCloudSize()
	{
		if ($this->isCloudSet()) {
			return $this->cloud['size'];
		}
		return null;
	}

	public function getCloudText()
	{
		if ($this->isCloudSet()) {
			return $this->cloud['text'];
		}
		return null;
	}

	public function countWords()
	{
		if (!$this->isCloudSet()) {
			return null;
		}
		$stmt = $this->db->prepare("
			SELECT count(*) as count
			FROM   clouds
			JOIN   words
			ON     id_cloud = cloud_id
			WHERE  code = :code;
		");
		$stmt->bindValue(':code', $this->cloud['code'], PDO::PARAM_STR);
		$stmt->execute();
		if ($data = $stmt->fetch()) {
			return $data['count'];
		}
		return null;
	}

	public function cleanCloud()
	{
		$stmt = $this->db->prepare("
			DELETE
			FROM  clouds
			WHERE delete_t < CURRENT_TIMESTAMP;
		");
		$stmt->execute();
		return true;
	}

	public function isCloud(string $id)
	{
		$stmt = $this->db->prepare("
			SELECT *
			FROM   clouds
			WHERE  code = :id;
		");
		$stmt->bindValue(':id', $id, PDO::PARAM_STR);
		$stmt->execute();
		if ($stmt->fetch()) {
			return true;
		}
		return false;
	}
}