<?php namespace HashOver;// Copyright (C) 2010-2021 Jacob Barkdull// This file is part of HashOver.//// HashOver is free software: you can redistribute it and/or modify// it under the terms of the GNU Affero General Public License as// published by the Free Software Foundation, either version 3 of the// License, or (at your option) any later version.//// HashOver is distributed in the hope that it will be useful,// but WITHOUT ANY WARRANTY; without even the implied warranty of// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the// GNU Affero General Public License for more details.//// You should have received a copy of the GNU Affero General Public License// along with HashOver.  If not, see <http://www.gnu.org/licenses/>.class Database extends Secrets{	protected $setup;	protected $database;	// Initial comment data	protected $commentsTable = array (		'domain' => '',		'thread' => '',		'comment' => '',		'body' => '',		'status' => '',		'date' => '',		'name' => '',		'password' => '',		'login_id' => '',		'email' => '',		'encryption' => '',		'email_hash' => '',		'notifications' => '',		'website' => '',		'ipaddr' => '',		'likes' => 0,		'dislikes' => 0	);	public function __construct (Setup $setup)	{		// Store parameters as properties		$this->setup = $setup;		// Check if database type is SQLite		if ($this->databaseType === 'sqlite') {			// If so, construct SQLite file name			$file = sprintf ('%s/%s.sqlite',				$setup->commentsRoot, $this->databaseName			);			// Instantiate an SQLite data object			$this->database = new \PDO ('sqlite:' . $file);			// And change file permissions			@chmod ($file, 0600);		} else {			// If not, create SQL server connection statement			$connection = implode (';', array (				'host=' . $this->databaseHost,				'port=' . $this->databasePort,				'dbname=' . $this->databaseName,				'charset=' . $this->databaseCharset			));			// And create SQL server data object			$this->database = new \PDO (				// PDO driver and connection details				$this->databaseType . ':' . $connection,				// Database user as configured				$this->databaseUser,				// Database password as configured				$this->databasePassword,				// We want the number of found (matched) rows,				// not the number of changed rows				array (					\PDO::MYSQL_ATTR_FOUND_ROWS => true				)			);		}	}	// Prepares and executes an SQL statement	protected function executeStatement ($statement, $data = null)	{		// Prepare statement		$prepare = $this->database->prepare ($statement);		// Check if prepare was successful		if ($prepare !== false) {			// If so, attempt to execute statement			$execute = $prepare->execute ($data);			// And return statement object if execute was successful			if ($execute !== false) {				return $prepare;			}		}		return false;	}	// Returns a given thread or thread from setup	protected function getCommentThread ($thread)	{		// Return thread from setup if thread is auto		if ($thread === 'auto') {			return $this->setup->threadName;		}		// Otherwise, return given thread		return $thread;	}	// Creates table creation statement from array	protected function creationArray (array $columns)	{		// Initial statement		$statement = array ();		// Create a statement using specific columns		foreach ($columns as $name => $value) {			// Decide type based on value type			$type = is_numeric ($value) ? 'INTEGER' : 'TEXT';			// And add column to statement			$statement[] = sprintf ('`%s` %s', $name, $type);		}		return $statement;	}	// Reads and returns specific metadata from database	public function readMeta ($name, $thread = 'auto', $global = false)	{		// Get thread		$thread = $this->getCommentThread ($thread);		// Prepared data for statement execution		$prepared = array (			'domain' => $this->setup->website		);		// Choose statement for supported metadata		switch ($name) {			// Latest comments			case 'latest-comments': {				// Initial statement				$statements = array (					'SELECT * FROM `comments`',					'WHERE (status IS NULL OR status="approved")',					'AND domain=:domain'				);				// Check if we are getting metadata from multiple threads				if ($global === false) {					// If so, add thread condition to statement					$statements[] = 'AND thread=:thread';					// And add thread to prepared data					$prepared['thread'] = $thread;				}				// Sort comments by date				$statements[] = 'ORDER BY `date` DESC';				// Limit comments to configured maximum				$statements[] = 'LIMIT ' . $this->setup->latestMax;				break;			}			// All others, just try to read as-is			default: {				// Initial statement				$statements = array (					sprintf ('SELECT * FROM `%s`', $name),					'WHERE domain=:domain'				);				// Check if we are getting metadata from multiple threads				if ($global === false) {					// Add thread condition to statement					$statements[] = 'AND thread=:thread';					// And add thread to prepared data					$prepared['thread'] = $thread;				}				break;			}		}		// Convert statements array into string		$statement = implode (' ', $statements);		// Query statement		$results = $this->executeStatement ($statement, $prepared);		// Check if the query was successful		if ($results !== false) {			// If so, attempt to get all metadata			$fetch_all = $results->fetchAll (\PDO::FETCH_ASSOC);			// Check if metadata read successfully			if (!empty ($fetch_all)) {				// If so, return first for if metadata is page info				if ($name === 'page-info') {					return $fetch_all[0];				}				// Otherwise, return all metadata				return $fetch_all;			}		}		return false;	}	// Creates comment table if it doesn't exist	protected function createTable ($name, array $columns)	{		// Statement for creating an initial table		$statement = sprintf (			'CREATE TABLE IF NOT EXISTS `%s` (%s)',			$name, implode (', ', $columns)		);		// Execute statement		$created = $this->executeStatement ($statement);		// Throw exception on failure		if ($created === false) {			throw new \Exception (sprintf (				'Failed to create "%s" table!', $name			));		}	}	// Get formatted string of array keys	protected function formatKeys (array $data, $format, $glue = ', ')	{		// Initial formatted output		$formatted = array ();		// Add each formatted array key		foreach (array_keys ($data) as $key) {			$formatted[] = str_replace ('%s', $key, $format);		}		// And convert formatted array to string		$statement = implode ($glue, $formatted);		return $statement;	}	// Saves metadata to specific metadata JSON file	public function saveMeta ($name, array $data, $thread = 'auto')	{		// Get thread		$thread = $this->getCommentThread ($thread);		// Add website domain and thread to data		$data = array_merge (array (			'domain' => $this->setup->website,			'thread' => $thread		), $data);		// Get metadata table creation statements		$creation_statement = $this->creationArray ($data);		// Attempt to create metadata table		$this->createTable ($name, $creation_statement);		// Update data in specific columns		$save = implode (' ', array (			sprintf ('UPDATE `%s`', $name),			'SET ' . $this->formatKeys ($data, '%s=:%s'),			'WHERE domain=:domain',			'AND thread=:thread'		));		// Execute statement		$saved = $this->executeStatement ($save, $data);		// Check if we failed to update any rows		if ($saved !== false and $saved->rowCount () === 0) {			// If so, create metadata column names list			$column_names = $this->formatKeys ($data, '`%s`');			// Create metadata column values list			$column_values = $this->formatKeys ($data, ':%s');			// Insert data into specific columns			$save = sprintf (				'INSERT INTO `%s` (%s) VALUES (%s)',				$name, $column_names, $column_values			);			// Execute statement			$saved = $this->executeStatement ($save, $data);		}		// Throw exception on failure		if ($saved === false) {			throw new \Exception (				'Failed to save metadata!'			);		}	}	// Writes new or changed content to database	public function write ($action, $thread, array $data, $alt = false)	{		// Get thread		$thread = $this->getCommentThread ($thread);		// Add website domain and thread to data		$data = array_merge ($data, array (			'domain' => $this->setup->website,			'thread' => $thread		));		// Decide on an action		switch ($action) {			// Action for posting a comment			case 'insert': {				// Construct SQL statement				$query = sprintf (					// Insertion statement					'INSERT INTO `comments` VALUES (%s)',					// Get list of table columns					$this->formatKeys ($this->commentsTable, ':%s')				);				break;			}			// Action for editing a comment			case 'update': {				// Columns to query				$columns = implode (', ', array (					'body=:body',					'status=:status',					'name=:name',					'password=:password',					'email=:email',					'encryption=:encryption',					'email_hash=:email_hash',					'notifications=:notifications',					'website=:website',					'likes=:likes',					'dislikes=:dislikes'				));				// Construct SQL statement				$query = implode (' ', array (					'UPDATE `comments`',					'SET ' . $columns,					'WHERE domain=:domain',					'AND thread=:thread',					'AND comment=:comment'				));				break;			}			// Action for deleting a comment			case 'delete': {				// Check if we're actually deleting the comment				if ($alt === true) {					// If so, use delete statement					$query = implode (' ', array (						'DELETE FROM `comments`',						'WHERE domain=:domain',						'AND thread=:thread',						'AND comment=:comment'					));				} else {					// If not, use status update statement					$query = implode (' ', array (						'UPDATE `comments`',						'SET status=:status',						'WHERE domain=:domain',						'AND thread=:thread',						'AND comment=:comment'					));				}				break;			}		}		// Execute statement		$queried = $this->executeStatement ($query, $data);		// Throw exception on failure		if ($queried === false) {			throw new \Exception (				'Failed to write to database!'			);		}		return true;	}	// Check if comments table exists	public function checkThread ()	{		// Create comments table creation statements		$statement = $this->creationArray ($this->commentsTable);		// Create initial comments if it doesn't exist		$this->createTable ('comments', $statement);	}	// Queries unique rows as of a specific column an array	protected function queryColumn ($column)	{		// Select unique thread names		$results = $this->executeStatement (sprintf (			'SELECT DISTINCT `%s` FROM `comments`', $column		));		// Check if query was successful		if ($results !== false) {			// If so, fetch all rows in column			$fetch_all = $results->fetchAll (\PDO::FETCH_ASSOC);			// Return column as array			return array_column ($fetch_all, $column);		}		// Otherwise, return an empty array		return array ();	}	// Queries an array of websites	public function queryWebsites ()	{		return $this->queryColumn ('domain');	}	// Queries an array of comment threads	public function queryThreads ()	{		return $this->queryColumn ('thread');	}	// These methods are not necessary in SQL	public function addLatestComment () {}	public function removeFromLatest () {}	}