database-connections.php

<?php
/** 
 * That namespace have the classes to the management of a SQLite3 database.
 * It have's exceptions and the main classes.
 * @author Giulliano Rossi <giulliano.scatalon.rossi@gmail.com>
 */
namespace SQLite3Connection{
	use Exception;
	use SQLite3;

	/** 
	 * That class is a exception, it's used when the SQLite3 class tries to do some action
	 * And don't have , or already have a SQLite3 database connected.
	 * ------------------------------------------------------------------------------------
	 * @var ALREADY_CONNECTED_ERR string The constant of the message to the error if the database already exists
	 * @var DATABASE_REQUESTED_ERR string The constant of the message of the error if the database is not connected 
	 */
	class DatabaseConnectionError extends Exception{
		const ALREADY_CONNECTED_ERR = "The class already have a database connected";
		const DATABASE_REQUESTED_ERR = "The class requested a inexisting database";
		
		/** 
		 * That method starts the class with a personalized way.
		 * @param int $code The default parameter of the original __construct method of the Exception class.
		 * @param bool $requested_db If the database was requested and not exists by default it's True.
		 * @return string 
		 */
		final public function __construct(bool $requested_db = True, int $code = 1){
			$this->message = $requested_db ? self::DATABASE_REQUESTED_ERR : self::ALREADY_CONNECTED_ERR;
			$this->code = $code;
		}
	}
	
	/**
	 * That class is just a normal Exception.
	 * It's used when the class tries to execute a invalid SQL file in the SQLite3 Connection.
	 * Don't have nothing at that source, because all attributtes and methods came from the hierarchy of the Exception class.
	 */
	class InvalidSQLFile extends Exception{ }

	/**
	 * That exception is used when the database file that the class's trying to connect, it's not a SQLite3 database file.
	 * This don't have nothing special, like the InvalidSQLFile.
	 */
	class InvalidDBFile extends Exception{ }

	/**
	 * Exception used when a action was tried but the connection don't 
	 */
	class RootPermissionRequired extends Exception{
		public function __construct(int $code = 0, string $message = "The class need root permissions to do that!"){
			$this->code = $code;
			$this->message = $message;
		}
	}

	/**
	 * That's the main class of the SQLite3 database connection.
	 * This class have the main methods of the management of the database.
	 * @var string|null $db_connected The path of the SQLite3 database that's the class is connected.
	 * @var bool $got_db If the class have a database connected, it starts with the false value.
	 * @var SQLite3|null $connection The main connection with the SQLite3 database, it's a object of the class SQLite3. With this the SQL querys are executeds
	 * @var bool|null $root_mode If the class have permissions for make changes at the database. If don't (False) the class will just deploy the connection and it data.
	 * @var string|null $connection_last_error The last error message that the class received.
	 */
	class SQLite3Connection{
		protected $db_connected = null;
		protected $got_db = false;
		protected $connection;
		protected $root_mode = null;

		private $connection_last_error = null;

		/**
		 * That method checks if the SQL script file is valid. It checks the file extension.
		 * @param string $path_ext The path of the file for check the extension.
		 * @return bool
		 */
		private static function check_script_ext(string $path_ext){
			$sp = explode(".", $path_ext);
			return $sp[count($sp) - 1] == "sql";
		}

		/**
		 * That static method checks if a file is a real SQLite database.
		 * It tries a connection with the class, if don't heve success, then will take it as it's not a database file.
		 *
		 * @param string $file_path
		 * @return bool
		 */
		private static function check_db_file(string $file_path){
			try{
				$db_checker = new SQLite3($file_path);
				if(!$db_checker){
					$this->connection_last_error = $db_checker->lastErrorMsg();
					return false;
				}
				else return true;
			}
			catch(Exception $e) {
				return false;
			}
		}

		/**
		 * That method initalize the class and set up the attributtes
		 * @param string|null $db_path The database file to connect the table
		 * @param bool $root_mode If will manage the database has a root user.
		 * @throws DatabaseConnectionError If the database class already have a database.
		 * @return void
		 */
		public function __construct(string $db_path = null, bool $root_mode = false){
			if($this->got_db) throw new DatabaseConnectionError(False, 1);
			$this->db_connected = $db_path;
			if(is_null($this->db_connected)){
				// sets the default values
				$this->got_db = false;
				$this->root_mode = null;
				$this->connection = null;
			}
			else{
				$this->connection = new SQLite3($this->db_connected);
				$this->root_mode = $root_mode;
				$this->got_db = true;
			}
		}

		/**
		 * Starts a connection with a database file.
		 * @param string $database_file The path for the database.
		 * @param bool $root_mode If the database will be managed as a root user.
		 * @throws DatabaseConnectionError If the class already have a database connected
		 * @throws InvalidDBFile If the file selected is not a SQLite3 database file.
		 * @return bool
		 */
		public function connect_db(string $database_file, bool $root_mode = False){
			if($this->got_db) throw new DatabaseConnectionError(False, 1);
			if(!$this->check_db_file($database_file)) throw new InvalidDBFile("The file '$database_file' is not a SQLite3 database file!", 1);
			try{
				$this->connection = new SQLite3($database_file);
				$this->db_connected = $database_file;
				$this->root_mode = $root_mode;
				$this->got_db = true;
			}
			catch(Exception $e){ return false;}
			return true;
		}

		/**
		 * Closes a database connection.
		 * @throws DatabaseConnectionError If the class don't have any database connected!
		 * @return bool
		 */
		public function disconnect_db(){
			if(!$this->got_db) throw new DatabaseConnectionError(True, 1);
			try{
				$this->connection->close();
				$this->db_connected = null;
				$this->root_mode = null;
				$this->got_db = false;
			}
			catch(Exception $e){ return false;}
			return true;
		}

		/**
		 * Destroys the class connection.
		 */
		public function __destruct(){
			$this->connection = null;
			$this->db_connected = null;
			$this->root_mode = null;
			$this->got_db = false;
		}

		/**
		 * Formats all the database tables. It requires the root_mode.
		 * @throws DatabaseConnectionError If there's no database connected;
		 * @throws RootPermissionRequired If the connection don't have root permissions.
		 * @return bool
		 */
		public function formatTables(){
			if(!$this->got_db) throw new DatabaseConnectionError();
			if(!$this->root_mode) throw new RootPermissionRequired(1);
			try{
				$all_tbs = $this->connection->query("SELECT name FROM sqlite_master WHERE type=\"table\;");
				while($row = $all_tbs->fetchArray()){ $qr_dl = $this->connection->query("DELETE FROM " . $row['name'] . ";"); }
			}
			catch(Exception $e){ return false; }
			return true;
		}

		/**
		 * List all the tables of the database. Will return a array with the tables names, if there's no tables will return null.
		 * @param bool $escape_sequence If the method will return the sqlite_sequence as a table too.
		 * @throws DatabaseConnectionError If the class don't have a database connected.
		 * @return array|null
		 */
		public function getTables(bool $escape_sequence = true){
			if(!$this->got_db) throw new DatabaseConnectionError();
			$raw_query = $this->connection->query("SELECT name from sqlite_master where type='table';");
			$results = [];
			while($row = $raw_query->fetchArray()){
				if(!$row['name'] == "sqlite_sequence" && $escape_sequence) $results[] = $row['name'];
				else {} // do nothing
			}
			return count($results) > 0 ? $results : null;
		}

		/**
		 * Show the table columns and it types. Will return a array with the columns, if the table don't exists will return null.
		 * @param string $table The table that will get the columns.
		 * @throws DatabaseConnectionError If there's no database connected.
		 * @return array|null.
		 */
		public function describeTable(string $table){
			if(!$this->got_db) throw new DatabaseConnectionError();
			$pragma = $this->connection->query("PRAGMA table_info(\"$table\");");
			$results = [];
			while($row = $pragma->fetchArray()) $results[] = $row;
			return count($results) > 0 ? $results : null;
		}

		/**
		 * Executes a SQL file and send the changes to the database.
		 * @param string $script_path The path to the SQL script.
		 * @throws DatabaseConnectionError If there's no database connected
		 * @throws RootPermissionRequired If the connection is not in the root mode.
		 * @throws InvalidSQLFile If the file is not a SQL script.
		 * @return bool
		 */
		public function execScript(string $script_path){
			if(!$this->got_db) throw new DatabaseConnectionError();
			if(!$this->root_mode) throw new RootPermissionRequired(1);
			if(!$this->check_script_ext($script_path)) throw new InvalidSQLFile("The file '$script_path' is not a valid script file!", 1);
			try{
				$sql = file_get_contents($script_path);
				for($i = 0; $i < strlen($sql); $i++){ $exec_line = $this->connection->query($sql[$i]); }
			}
			catch(Exception $e){
				$this->connection_last_error = $e->getMessage();
				return false;
			}
			return true;
		}

		/**
		 * Return the database path connected.
		 * @return string|null
		 */
		public function get_database(){ return $this->db_connected;}

		/**
		 * Return the raw connection
		 * @return SQLite3|null
		 */
		public function get_handler(){ return $this->connection; }

		/**
		 * Return connection status
		 * @return bool
		 */
		public function get_db_connected(){ return $this->got_db;}

		/**
		 * Return the connection mode
		 * @return bool|null
		 */
		public function get_mode(){ return $this->root_mode; }

		/**
		 * Return the last error message.
		 * @return string
		 */
		public function get_last_error(){ return $this->connection_last_error; }
	}
}

namespace MySQLConnection{
	use Exception;
	use mysqli;

	/**
	 * The same thing in the SQLite3 database connection. It will be throwed when the MySQL connection is connected or don't.
	 * @var string ALREADY_CONNECTED_ERR The message that will be throwed with the exception, the default message if it's connected.
	 * @var string CONNECTION_REQUESTED_ERR The message that will be throwed with the exception, the default message if it's not connected.
	 */
	class MySQLConnectionError extends Exception{
		const ALREADY_CONNECTED_ERR = "The MySQL class already have a connection!";
		const CONNECTION_REQUESTED_ERR = "The MySQL class don't have the requested connection!";

		/**
		 * Starts the class with the choice of the message of the two types of that exception.
		 * @param int $code The default exception code, by default it's 1
		 * @param bool $missing_connection If it was a error because is not connected or not, by default it's true.
		 */
		public function __construct(int $code = 1, bool $missing_connection = True){
			$msg = $missing_connection ? self::CONNECTION_REQUESTED_ERR : self::ALREADY_CONNECTED_ERR;
			parent::__construct($msg, $code);
		}
	}

	/**
	 * That class does the same thing then the MySQLConnectionError, however that exception will be throwed when the 
	 * class don't have a database in the MySQL connection.
	 * @var string GOT_DB_ERR The message that will be throwed if the connection have a database connected.
	 * @var string MISSING_DB_ERR The message that will be throwed if the connection don't have a database connection.
	 */
	class MySQLDatabaseError extends Exception{
		const GOT_DB_ERR = "The MySQL connection already have a MySQL database!";
		const MISSING_DB_ERR = "The MySQL connection don't have a database connected!";

		/**
		 * Starts the class in the same way the MySQLConnectionError, with a decision between the two types of that exception.
		 * @param int $code The default exception code. By default it's 1;
		 * @param bool $missing_database If the connection class don't have the requested database.
		 */
		public function __construct(int $code = 1, bool $missing_database = true){
			$msg = $missing_database ? self::MISSING_DB_ERR : self::GOT_DB_ERR;
			parent::__construct($msg, $code);
		}
	}

	/**
	 * Exception throwed when the selected SQL script is invalid
	 */
	class InvalidSQLScript extends Exception{}

	/**
	 * The main connection class, that manages and contains the main data in the database.
	 * @var string|null $host_connection The host of the connection that the mysqli class is connected. It is null when there's no connection.
	 * @var string|null $db_connected The database that the class is connected. It's null when there's no database connected
	 * @var bool $got_connection If the class have a connection.
	 * @var int|null $port The port using in the connection.
	 * @var bool|null $root_mode If the connection can change the data and the structure of the database.
	 * @var mysqli $con_handler The connection handler, a object of the mysqli class of the PHP.
	 * @var string|null $last_error The last error message that the class received.
	 * @var string|null $username The user that's connected
	 */
	class MySQLConnection{
		private $host_connection;
		private $db_connected;
		protected $got_connection;
		private $port;
		protected $root_mode;
		protected $con_handler;
		public $last_error;
		private $username;

		/**
		 * Checks if the file extension of a SQL script is valid.
		 * @param string $script_path The script path.
		 * @return bool
		 */
		private static function checkScript(string $script_path){
			$sp = explode(".", $script_path);
			return $sp[count($sp) - 1] == "sql";
		}

		/**
		 * Starts a MySQL connection.
		 * @param string $host The host to connect. by default is '127.0.0.1'
		 * @param int $port The port using. By default is 3306
		 * @param string|null $db The database using. By default is null, for not connect to a database already.
		 * @param string $username The name of the user to make the connection.
		 * @param string $passwd The user password, that will not be saved in the attributtes. By default it is a empty string.
		 * @throws MySQLConnectionError If the class already have a connection.
		 * @return bool
		 */
		public function connect_mysql(string $host = "127.0.0.1", int $port = 3306, string $db = null, string $username, string $passwd = ""){
			if($this->got_connection) throw new MySQLConnectionError(1, False);
			try{
				$this->con_handler = new mysqli($host, $username, $passwd, $db, $port);
				$this->db_connected = $db;
				$this->host_connection = $host;
				$this->port = $port;
				$this->$username = $username;
				$this->got_connection = True;
			}
			catch(Exception $e){
				$this->last_error = $e->getMessage();
				return false;
			}
			return true;
		}

		/**
		 * Connects the MySQL connection with a database.
		 * @param string $db The database to connect.
		 * @throws MySQLDatabaseError If there's a database connected already.
		 * @throws MySQLConnectionError If there's no MySQL connection.
		 * @return bool
		 */
		public function connect_db(string $db){
			if($this->got_connection) throw new MySQLDatabaseError(1, False);
			try{
				$this->con_handler->query("USE $db");
				$this->db_connected = $db;
			}
			catch(Exception $e){
				$this->last_error = $e->getMessage();
				return false;
			}
			return true;
		}

		/**
		 * Starts the class with the values of the connection, but if the host or another value is null, then it will start
		 * the class without the connection.
		 * @param string|null $host The host to connect, by default it's null
		 * @param string|null $user The user to connect to the host, by default it's null
		 * @param string|null $passwd The user passwd, also null by default
		 * @param string|null $db The database to connect already, it's null by default.
		 * @param int|null $port The port using at the connection, by default it's 3306
		 */
		public function __construct(string $host = null, string $user = null, string $passwd = null, string $db = null, int $port = 3306){
			if(is_null($host)){
				$this->host_connection = null;
				$this->username = null;
				$this->db_connected = null;
				$this->con_handler = new mysqli();  // empty class object
				$this->got_connection = false;
			}
			else{
				if($this->got_connection) throw new MySQLConnectionError(1, false);
				$this->con_handler = new mysqli($host, $user, $passwd, $db, $port);
				$this->host_connection = $host;
				$this->username = $user;
				$this->db_connected = $db;
				$this->port = $port;
				$this->got_connection = true;
			}
		}

		/**
		 * That class closes a connection with a MySQL host
		 * @throws MySQLConnectionError If there's no connection already
		 * @return bool
		 */
		public function close_connection(){
			if(!$this->got_connection) throw new MySQLConnectionError();
			try{
				$this->con_handler->close();
				$this->host_connection = null;
				$this->port = null;
				$this->username = null;
				$this->got_connection = false;
				$this->db_connected = null;
			}
			catch(Exception $e){
				$this->last_error = $e->getMessage();
				return false;
			}
			return true;
		}

		/**
		 * List all the databases at a host and put they names in a array.
		 * @throws MySQLConnectionError If there's no connection MySQL 
		 * @return array|null
		 */
		public function get_dbs(){
			if(!$this->got_connection) throw new MySQLConnectionError();
			$dbs = $this->con_handler->query("SHOW DATABASES;");
			$results = [];
			while($row = $dbs->fetch_array()) $results[] = $dbs;
			return count($results) > 0 ? $results : null;
		}

		/**
		 * List all the tables of the database at the connection.
		 * @throws MySQLDatabaseError If there's no database selected
		 * @throws MySQLConnectionError If there's no connection
		 * @return array|null
		 *
		 */
		public function get_tbs(){
			if(!$this->got_connection) throw new MySQLConnectionError();
			if(is_null($this->db_connected)) throw new MySQLDatabaseError();
			$all_tbs = $this->con_handler->query("show tables;");
			$results = [];
			while($row = $all_tbs) $results[] = $row;
			return count($results) > 0 ? $results : null;
		}

		/**
		 * List all the columns of a table in the database.
		 * @throws MySQLConnectionError If there's no connection to a MySQL host.
		 * @throws MySQLDatabaseError If there's no database selected.
		 * @param string $table The table to get the columns
		 * @return array|null
		 */
		public function describeTable(string $table){
			if(!$this->got_connection) throw new MySQLConnectionError();
			if(is_null($this->db_connected)) throw new MySQLDatabaseError();
			$columns = $this->con_handler->query("describe $table;");
			$results = [];
			while($row = $columns) $results[] = $row;
			return count($results) > 0 ? $results : null;
		}

		/**
		 * Return the host connected
		 * @return string|null
		 */
		public function getHost(){ return $this->host_connection; }

		/**
		 * Return the connection handler
		 * @return mysqli|null
		 */
		public function getCon(){ return $this->con_handler; }

		/**
		 * Return the database connected
		 * @return string|null
		 */
		public function getDB(){ return $this->db_connected; }

		/**
		 * Return User connected
		 * @return string|null
		 */
		public function getUser(){ return $this->username; }
	}
}