simpleWordsCloud/db.php

388 lines
9.1 KiB
PHP
Raw Permalink Normal View History

2020-11-15 21:51:28 +01:00
<?php
require_once 'soundex_fr.php';
require_once 'common.php';
2020-11-22 15:09:31 +01:00
2020-11-15 21:51:28 +01:00
class DataBase
{
2020-11-16 21:31:59 +01:00
const DEFAULT_DURATION = '+7 day';
const OPTIONS_DURATION = array(
'day' => '+1 day',
'week' => '+7 day',
2020-11-23 11:01:37 +01:00
'week2' => '+14 day',
2020-11-16 21:31:59 +01:00
'month' => '+1 month'
);
2020-11-23 11:01:37 +01:00
const DEFAULT_SIZE = 3;
const MAX_SIZE = 9;
const CLOUD_CODE_LENGTH = 6;
const CLOUD_CODE_MAXTRY = 10;
2020-11-16 21:31:59 +01:00
2020-11-15 21:51:28 +01:00
private $db;
2020-11-25 22:13:59 +01:00
private $cloud;
2020-11-15 21:51:28 +01:00
public function __construct()
{
$this->init();
}
2020-11-25 22:13:59 +01:00
/**
* Initialize the database
*
* Store the PDO object in $db private class variable
*/
2020-11-15 21:51:28 +01:00
public function init()
{
2020-11-22 15:09:31 +01:00
require_once 'dbconfig.php';
2020-11-25 19:43:40 +01:00
$dsn = "$type:dbname=$dbname;host=$host;port=$port";
2020-11-15 21:51:28 +01:00
try {
2020-11-22 15:09:31 +01:00
$this->db = new PDO($dsn, $username, $password);
2020-11-15 21:51:28 +01:00
$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
2020-11-22 15:09:31 +01:00
$this->db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
2020-11-15 21:51:28 +01:00
} catch (PDOException $e) {
echo $e->getMessage();
}
}
2020-11-25 22:13:59 +01:00
/**
* Check if the database is initialized or not
* @return boolean True if db is inizialized
*/
2020-11-15 21:51:28 +01:00
public function isInit()
{
return isset($this->db);
}
2020-11-25 22:13:59 +01:00
/**
* Build the database tables, only if not existing
*/
2020-11-15 21:51:28 +01:00
public function buildTables()
{
2020-11-16 21:31:59 +01:00
$stmt = $this->db->prepare("
CREATE TABLE IF NOT EXISTS clouds(
2020-11-22 15:09:31 +01:00
id_cloud serial PRIMARY KEY,
2020-11-16 21:31:59 +01:00
code TEXT NOT NULL UNIQUE,
size INTEGER NOT NULL DEFAULT 3,
text TEXT,
delete_t TIMESTAMP DEFAULT
2020-11-22 15:09:31 +01:00
(CURRENT_TIMESTAMP + INTERVAL '" . self::DEFAULT_DURATION . "')
2020-11-16 21:31:59 +01:00
);
");
2020-11-15 21:51:28 +01:00
$stmt->execute();
2020-11-16 21:31:59 +01:00
$stmt = $this->db->prepare("
CREATE TABLE IF NOT EXISTS words(
2020-11-22 15:09:31 +01:00
id_word serial PRIMARY KEY,
2020-11-16 21:31:59 +01:00
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
);
");
2020-11-15 21:51:28 +01:00
$stmt->execute();
}
2020-11-25 22:13:59 +01:00
/**
* 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)
2020-11-15 21:51:28 +01:00
{
if (empty($word)) {
return false;
}
2020-11-25 22:13:59 +01:00
if (isset($cloud)) {
if (!$this->loadCloud($cloud)) {
return false;
}
}
if (!$this->isCloudSet()) {
2020-11-15 21:51:28 +01:00
return false;
}
2020-11-25 22:13:59 +01:00
$stmt = $this->db->prepare("
SELECT *
FROM words
WHERE cloud_id = :cid;
");
2020-11-25 22:13:59 +01:00
$stmt->bindValue(':cid', $this->cloud['id'], PDO::PARAM_INT);
$stmt->execute();
while($data = $stmt->fetch()) {
if (areWordsSimilar($data['word'], $word)) {
$word = $data['word'];
2020-11-23 22:32:31 +01:00
$wordId = $data['id_word'];
break;
}
}
2020-11-23 22:32:31 +01:00
if (isset($wordId)) {
2020-11-16 21:31:59 +01:00
$stmt = $this->db->prepare("
UPDATE words
SET count = count + 1
WHERE id_word = :id;
");
2020-11-15 21:51:28 +01:00
$stmt->bindValue(':id', $wordId, PDO::PARAM_INT);
$stmt->execute();
} else {
2020-11-16 21:31:59 +01:00
$stmt = $this->db->prepare("
INSERT INTO words(word, cloud_id)
VALUES (:w, :cid);
");
2020-11-15 21:51:28 +01:00
$stmt->bindValue(':w', $word, PDO::PARAM_STR);
2020-11-25 22:13:59 +01:00
$stmt->bindValue(':cid', $this->cloud['id'], PDO::PARAM_INT);
2020-11-15 21:51:28 +01:00
$stmt->execute();
}
}
2020-11-26 17:03:27 +01:00
/**
* 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)
2020-11-15 21:51:28 +01:00
{
2020-11-26 17:03:27 +01:00
if (isset($code)) {
$this->loadCloudByCode($code);
}
if (!isset($code) && !$this->isCloudSet()) {
return null;
}
2020-11-16 21:31:59 +01:00
$stmt = $this->db->prepare("
SELECT *
FROM words
JOIN clouds
ON words.cloud_id = clouds.id_cloud
2020-11-26 17:03:27 +01:00
WHERE code = :code;
2020-11-16 21:31:59 +01:00
");
2020-11-26 17:03:27 +01:00
$stmt->bindValue(':code', $this->cloud['code']);
2020-11-15 21:51:28 +01:00
$stmt->execute();
2020-11-23 11:01:37 +01:00
$words = [];
2020-11-15 21:51:28 +01:00
$values = [];
2020-11-23 11:01:37 +01:00
$total = 0;
$max = 0;
2020-11-15 21:51:28 +01:00
while ($data = $stmt->fetch()) {
2020-11-23 11:01:37 +01:00
$words[] = array(
'word' => $data['word'],
'count' => $data['count'],
);
2020-11-15 21:51:28 +01:00
$values[] = $data['count'];
$total += $data['count'];
2020-11-23 11:01:37 +01:00
$max = max($max, $data['count']);
}
foreach ($words as $key => $word) {
$words[$key]['relative'] = $word['count'] / $max;
2020-11-26 17:03:27 +01:00
$words[$key]['percent'] = $word['count'] / $total * 100;
2020-11-15 21:51:28 +01:00
}
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
*/
2020-11-16 21:31:59 +01:00
public function createCloud(
string $text = '',
int $size = null,
2020-11-16 21:31:59 +01:00
string $duration = null)
2020-11-15 21:51:28 +01:00
{
2020-11-16 21:31:59 +01:00
if (!isset($size)) {
$size = 3;
} elseif ($size < 1) {
$size = 1;
} elseif ($size > self::MAX_SIZE) {
$size = self::MAX_SIZE;
2020-11-16 21:31:59 +01:00
}
if (!isset($duration)) {
$duration = self::DEFAULT_DURATION;
2020-11-25 22:35:28 +01:00
} elseif (!key_exists($duration, self::OPTIONS_DURATION)) {
2020-11-16 21:31:59 +01:00
$duration = self::DEFAULT_DURATION;
}
for ($i = 0; $i < self::CLOUD_CODE_MAXTRY; $i++) {
$code = bin2hex(random_bytes(self::CLOUD_CODE_LENGTH));
2020-11-26 17:03:27 +01:00
if (!$this->loadCloudByCode($code)) {
break;
}
}
2020-11-26 17:03:27 +01:00
if ($this->isCloudSet()) {
return false;
}
2020-11-26 17:03:27 +01:00
$delete = date('Y-m-d H:i:s', strtotime(self::OPTIONS_DURATION[$duration]));
2020-11-16 21:31:59 +01:00
$stmt = $this->db->prepare("
INSERT INTO clouds(code, text, size, delete_t)
2020-11-26 17:03:27 +01:00
VALUES (:code, :text, :size, :delete);
2020-11-16 21:31:59 +01:00
");
$stmt->bindValue(':code', $code, PDO::PARAM_STR);
$stmt->bindValue(':text', $text, PDO::PARAM_STR);
$stmt->bindValue(':size', $size, PDO::PARAM_INT);
2020-11-26 17:03:27 +01:00
$stmt->bindValue(':delete', $delete, PDO::PARAM_STR);
2020-11-15 21:51:28 +01:00
try {
$stmt->execute();
} catch (PDOEXception $e) {
return false;
}
2020-11-25 22:28:36 +01:00
$this->cloud = array(
'id' => $this->db->lastInsertId(),
'code' => $code,
2020-11-25 22:28:36 +01:00
'size' => $size,
2020-11-26 17:03:27 +01:00
'delete_t' => $delete,
2020-11-25 22:28:36 +01:00
'text' => $text,
);
return $this->cloud;
2020-11-15 21:51:28 +01:00
}
2020-11-25 22:13:59 +01:00
public function loadCloud($ref)
{
if (is_int($ref)) {
return $this->loadCloudById($ref);
} elseif (is_string($ref)) {
return $this->loadCloudByCode($ref);
}
}
2020-11-25 22:28:36 +01:00
/**
* Load the cloud by its id
* @param int $id code of the cloud
* @return boolean True if the loading is done, False otherwise
*/
2020-11-25 22:13:59 +01:00
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'],
2020-11-25 22:28:36 +01:00
'text' => $data['text'],
2020-11-25 22:13:59 +01:00
);
return true;
}
$this->cloud = null;
return false;
}
2020-11-25 22:28:36 +01:00
/**
* Load the cloud by its code
* @param string $code code of the cloud
* @return boolean True if the loading is done, False otherwise
*/
2020-11-25 22:13:59 +01:00
public function loadCloudByCode(string $code)
{
$stmt = $this->db->prepare("
SELECT *
FROM clouds
2020-11-25 22:28:36 +01:00
WHERE code = :code;
2020-11-25 22:13:59 +01:00
");
$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'],
2020-11-25 22:28:36 +01:00
'text' => $data['text'],
2020-11-25 22:13:59 +01:00
);
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;
}
2020-11-25 23:24:43 +01:00
public function getCloudSize()
2020-11-16 21:31:59 +01:00
{
2020-11-25 23:24:43 +01:00
if ($this->isCloudSet()) {
return $this->cloud['size'];
2020-11-16 21:31:59 +01:00
}
2020-11-25 23:24:43 +01:00
return null;
2020-11-16 21:31:59 +01:00
}
2020-11-25 23:24:43 +01:00
public function getCloudText()
2020-11-16 21:31:59 +01:00
{
2020-11-25 23:24:43 +01:00
if ($this->isCloudSet()) {
return $this->cloud['text'];
2020-11-16 21:31:59 +01:00
}
return null;
}
2020-11-25 23:24:43 +01:00
public function countWords()
2020-11-23 11:01:37 +01:00
{
2020-11-25 23:24:43 +01:00
if (!$this->isCloudSet()) {
return null;
}
2020-11-23 11:01:37 +01:00
$stmt = $this->db->prepare("
SELECT count(*) as count
FROM clouds
JOIN words
ON id_cloud = cloud_id
WHERE code = :code;
");
2020-11-25 23:24:43 +01:00
$stmt->bindValue(':code', $this->cloud['code'], PDO::PARAM_STR);
2020-11-23 11:01:37 +01:00
$stmt->execute();
if ($data = $stmt->fetch()) {
return $data['count'];
}
return null;
}
2020-11-15 21:51:28 +01:00
public function cleanCloud()
{
2020-11-16 21:31:59 +01:00
$stmt = $this->db->prepare("
DELETE
FROM clouds
WHERE delete_t < CURRENT_TIMESTAMP;
");
2020-11-15 21:51:28 +01:00
$stmt->execute();
return true;
}
public function isCloud(string $id)
{
2020-11-16 21:31:59 +01:00
$stmt = $this->db->prepare("
SELECT *
FROM clouds
WHERE code = :id;
");
2020-11-15 21:51:28 +01:00
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->execute();
if ($stmt->fetch()) {
return true;
}
return false;
}
}