388 lines
9.1 KiB
PHP
388 lines
9.1 KiB
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;
|
|
}
|
|
}
|