You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
524 lines
13 KiB
PHP
524 lines
13 KiB
PHP
<?php
|
|
|
|
/**
|
|
* @desc mini class to manipulate sqlite db for evocluster.
|
|
*
|
|
* @example
|
|
* <?php
|
|
* require 'bdd.php';
|
|
*
|
|
* // create bdd
|
|
* $bdd = new bdd();
|
|
*
|
|
* if (!file_exists("evocluster.sqlite"))
|
|
* $bdd->create("evocluster.sqlite");
|
|
* else
|
|
* $bdd->open("evocluster.sqlite");
|
|
*
|
|
* // declare account
|
|
* $account_demo = array(
|
|
* "name" => "demo",
|
|
* "domain" => "demo.quai13.fr",
|
|
* "bdd" => "demo_bdd",
|
|
* "replication" => "realtime");
|
|
*
|
|
* $bdd->add_account($account_demo);
|
|
*
|
|
* // declare servers
|
|
* $server1 = array("name" => "quai13-www00");
|
|
* $server2 = array("name" => "quai13-www01");
|
|
*
|
|
* // declare roles
|
|
* $bdd->add_role($account_demo['name'], 'quai13-www00', 'master');
|
|
* $bdd->add_role($account_demo['name'], 'quai13-www01', 'slave');
|
|
*
|
|
* // list domains
|
|
* $domains = $bdd->list_domains();
|
|
*
|
|
* ?>
|
|
*/
|
|
|
|
class bdd {
|
|
|
|
private $db; /* resource of a created database */
|
|
|
|
/**
|
|
* @desc Open a sqlite database in rw mode. Create it if it doesn't exist.
|
|
* @param string $db_name Name of the sqlite database
|
|
*/
|
|
public function open($db_name)
|
|
{
|
|
try {
|
|
$this->db = new SQLite3($db_name);
|
|
}
|
|
catch(Exception $e)
|
|
{
|
|
die ($e);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @desc Close the current database instance
|
|
*
|
|
* Note: php close automatically the instance on exit
|
|
*/
|
|
public function close()
|
|
{
|
|
sqlite_close($this->db);
|
|
}
|
|
|
|
/**
|
|
* @desc Install the table structure on a new sqlite database
|
|
* @param string $dn_name Name of the sqlite database
|
|
*/
|
|
public function create($db_name)
|
|
{
|
|
/*
|
|
* open a database or create it if it doesn't exist
|
|
*/
|
|
$this->open($db_name);
|
|
|
|
$database = $this->db;
|
|
|
|
|
|
/* Table Accounts */
|
|
$query = 'CREATE Table Accounts ' .
|
|
'(id INTEGER PRIMARY KEY , name TEXT, domain TEXT, bdd TEXT, replication TEXT, id_master INTEGER, id_slave INTEGER, mail TEXT)';
|
|
|
|
if (!$database->exec($query))
|
|
{
|
|
die($database->lastErrorMsg());
|
|
}
|
|
|
|
/* Table Servers */
|
|
$query = 'CREATE Table Servers ' .
|
|
'(id INTEGER PRIMARY KEY , name TEXT, ip TEXT)';
|
|
|
|
if (!$database->exec($query))
|
|
{
|
|
die($database->lastErrorMsg());
|
|
}
|
|
|
|
/* Table ServersAlias */
|
|
$query = 'CREATE Table Serveralias ' .
|
|
'(id INTEGER PRIMARY KEY , domain TEXT, alias TEXT)';
|
|
|
|
if (!$database->exec($query))
|
|
{
|
|
die($database->lastErrorMsg());
|
|
}
|
|
|
|
/* Table Roles */
|
|
$query = 'CREATE Table Roles ' .
|
|
'(id INTEGER PRIMARY KEY , name TEXT, id_account INTEGER, id_server INTEGER)';
|
|
|
|
if (!$database->exec($query))
|
|
{
|
|
die($database->lastErrorMsg());
|
|
}
|
|
}
|
|
|
|
public function get_server_from_roleid($roleid)
|
|
{
|
|
$database = $this->db;
|
|
|
|
$query = "SELECT Servers.name FROM Servers, Roles where Roles.id = '$roleid' and Roles.id_server = Servers.id";
|
|
|
|
$result = $database->query($query);
|
|
if ($result != FALSE)
|
|
{
|
|
$row = $result->fetchArray();
|
|
if (isset($row))
|
|
return $row['name'];
|
|
}
|
|
|
|
return 0;
|
|
}
|
|
|
|
/**
|
|
* @desc Get the account id of an account_name
|
|
* @param string $account_name Name of the account
|
|
* @return int 0 if account_name doesn't exist,
|
|
* id else
|
|
*/
|
|
private function get_account_id($account_name)
|
|
{
|
|
$database = $this->db;
|
|
|
|
$query = "SELECT id FROM Accounts where name = '$account_name'";
|
|
|
|
$result = $database->query($query);
|
|
if ($result != FALSE)
|
|
{
|
|
$row = $result->fetchArray();
|
|
if (isset($row))
|
|
return $row['id'];
|
|
}
|
|
|
|
return 0;
|
|
}
|
|
|
|
/**
|
|
* @desc Get account
|
|
* @param account name
|
|
* @return array
|
|
*/
|
|
public function get_account($account_name)
|
|
{
|
|
$database = $this->db;
|
|
|
|
$query = "SELECT * FROM Accounts where name = '$account_name'";
|
|
|
|
$result = $database->query($query);
|
|
if ($result != FALSE)
|
|
{
|
|
$row = $result->fetchArray();
|
|
if (isset($row))
|
|
return $row;
|
|
else
|
|
return array();
|
|
}
|
|
|
|
return array();
|
|
}
|
|
|
|
/**
|
|
* @desc Check if account_name entry exists on table Accounts
|
|
* @param string $account_name Name of the account
|
|
* @return int 1 if it exists,
|
|
* 0 else
|
|
*/
|
|
public function is_account($account_name)
|
|
{
|
|
return !!($this->get_account_id($account_name));
|
|
}
|
|
|
|
/**
|
|
* @desc Add an account to the table Accounts
|
|
* @param array {
|
|
* 'name' => "$name",
|
|
* 'domain' => "$domain",
|
|
* 'bdd' => "bdd",
|
|
* 'replication' => "replication"
|
|
* 'mail' => "gmail|evolix"
|
|
* }
|
|
* @return 1 on success,
|
|
* 0 else
|
|
*/
|
|
public function add_account($account)
|
|
{
|
|
$database = $this->db;
|
|
|
|
$name = $account["name"];
|
|
$domain = $account["domain"];
|
|
$bdd = $account["bdd"];
|
|
$replication = $account["replication"];
|
|
$mail = $account["mail"];
|
|
|
|
/* check if account exists */
|
|
if ($this->is_account($name))
|
|
return 0;
|
|
|
|
$query = "INSERT INTO Accounts (name, domain, bdd, replication, mail)
|
|
VALUES (
|
|
'$name',
|
|
'$domain',
|
|
'$bdd',
|
|
'$replication',
|
|
'$mail');";
|
|
|
|
if (!$database->exec($query))
|
|
{
|
|
die($database->lastErrorMsg());
|
|
}
|
|
return 1;
|
|
}
|
|
|
|
|
|
/**
|
|
* @desc Add an alias to the table Serveralias
|
|
* @param array {
|
|
* 'domain' => "$domain",
|
|
* 'alias' => "$alias",
|
|
* }
|
|
* @return 1 on success,
|
|
* 0 else
|
|
*/
|
|
public function add_serveralias($serveralias)
|
|
{
|
|
$database = $this->db;
|
|
|
|
$domain = $serveralias["domain"];
|
|
$alias = $serveralias["alias"];
|
|
|
|
$query = "INSERT INTO Serveralias (domain, alias)
|
|
VALUES (
|
|
'$domain',
|
|
'$alias');";
|
|
|
|
if (!$database->exec($query))
|
|
{
|
|
die($database->lastErrorMsg());
|
|
}
|
|
return 1;
|
|
}
|
|
|
|
/**
|
|
* @desc Del an alias from the table Serveralias
|
|
* @param array {
|
|
* 'domain' => "$domain",
|
|
* 'alias' => "$alias",
|
|
* }
|
|
* @return 1 on success,
|
|
* 0 else
|
|
*/
|
|
public function del_serveralias($serveralias)
|
|
{
|
|
$database = $this->db;
|
|
|
|
|
|
$domain = $serveralias['domain'];
|
|
$alias = $serveralias['alias'];
|
|
|
|
$query = "DELETE FROM Serveralias WHERE domain='$domain' AND alias='$alias';";
|
|
|
|
if (!$database->exec($query))
|
|
die($database->lastErrorMsg());
|
|
|
|
return 1;
|
|
}
|
|
|
|
/**
|
|
* @desc Get the server id of a server_name
|
|
* @param string $server_name Name of the server
|
|
* @return int 0 if server_name doesn't exist,
|
|
* id else
|
|
*/
|
|
private function get_server_id($server_name)
|
|
{
|
|
$database = $this->db;
|
|
|
|
$query = "SELECT id FROM Servers where name = '$server_name'";
|
|
|
|
$result = $database->query($query);
|
|
if ($result != FALSE)
|
|
{
|
|
$row = $result->fetchArray();
|
|
if (isset($row))
|
|
return $row['id'];
|
|
}
|
|
|
|
return 0;
|
|
}
|
|
|
|
/**
|
|
* @desc Check if server_name entry exists on table Servers
|
|
* @param string $server_name Name of the server
|
|
* @return int 1 if it exists,
|
|
* 0 else
|
|
*/
|
|
public function is_server($server_name)
|
|
{
|
|
return !!($this->get_server_id($server_name));
|
|
}
|
|
|
|
|
|
/**
|
|
* @desc Add an server to the table Servers
|
|
* @param array {
|
|
* 'name' => "$name",
|
|
* }
|
|
* @return 1 on success,
|
|
* 0 else
|
|
*/
|
|
public function add_server($server)
|
|
{
|
|
$database = $this->db;
|
|
|
|
$name = $server["name"];
|
|
|
|
/* check if server exists */
|
|
if ($this->is_server($name))
|
|
return 0;
|
|
|
|
$query = "INSERT INTO Servers (name)
|
|
VALUES (
|
|
'$name'
|
|
);";
|
|
|
|
if (!$database->exec($query))
|
|
die($database->lastErrorMsg());
|
|
|
|
return 1;
|
|
}
|
|
|
|
/**
|
|
* @desc Add a role to the table Roles
|
|
* @param string $account_name
|
|
* string $server_name
|
|
* string $role master or slave
|
|
* @return 1 on success,
|
|
* 0 else.
|
|
*/
|
|
public function add_role($account_name, $server_name, $role)
|
|
{
|
|
$database = $this->db;
|
|
|
|
/* get id_account */
|
|
$id_account = $this->get_account_id($account_name);
|
|
if ($id_account == 0)
|
|
return 0;
|
|
|
|
/* get id_server */
|
|
$id_server = $this->get_server_id($server_name);
|
|
if ($id_server == 0)
|
|
return 0;
|
|
|
|
$query = "INSERT INTO Roles (name, id_account, id_server)
|
|
VALUES (
|
|
'$role',
|
|
'$id_account',
|
|
'$id_server')";
|
|
|
|
if (!$database->exec($query))
|
|
die($database->lastErrorMsg());
|
|
|
|
$id = $database->lastInsertRowid();
|
|
|
|
/* update id_master or id_slave */
|
|
if (($role === 'master') || ($role === 'slave'))
|
|
$query = "UPDATE Accounts SET id_$role = '$id' WHERE id = '$id_account'";
|
|
|
|
if (!$database->exec($query))
|
|
die($database->lastErrorMsg());
|
|
|
|
return 1;
|
|
}
|
|
|
|
|
|
/**
|
|
* @desc List domains by server's role
|
|
* @param none
|
|
* @return list of array
|
|
* example : Array (
|
|
* [0] => Array
|
|
* (
|
|
* [Accounts.id] => 1
|
|
* [Accounts.name] => demo
|
|
* [Accounts.domain] => demo.quai13.fr
|
|
* [Accounts.bdd] => demo_bdd
|
|
* [Accounts.replication] => realtime
|
|
* [Accounts.id_master] => 1
|
|
* [Accounts.id_slave] => 2
|
|
* [Roles.id] => 1
|
|
* [Roles.name] => master
|
|
* [Roles.id_account] => 1
|
|
* [Roles.id_server] => 1
|
|
* [Servers.id] => 1
|
|
* [Servers.name] => quai13-www00
|
|
* [Servers.ip] =>
|
|
* )
|
|
* [1] => Array
|
|
* (
|
|
* ...
|
|
* )
|
|
* ...
|
|
* )
|
|
*/
|
|
public function list_domains()
|
|
{
|
|
$database = $this->db;
|
|
|
|
$query = "SELECT * FROM Accounts, Roles, Servers WHERE Accounts.id = Roles.id_account AND Roles.id_server = Servers.id";
|
|
$result = $database->query($query);
|
|
if($result != FALSE)
|
|
{
|
|
$domains = array();
|
|
$i = 0;
|
|
|
|
while($row = $result->fetchArray())
|
|
{
|
|
$domains[$i] = $row;
|
|
++$i;
|
|
}
|
|
}
|
|
else
|
|
die($error);
|
|
|
|
return $domains;
|
|
}
|
|
|
|
public function list_accounts()
|
|
{
|
|
$database = $this->db;
|
|
|
|
$query = "SELECT * FROM Accounts";
|
|
|
|
$result = $database->query($query);
|
|
if ($result != FALSE)
|
|
{
|
|
$accounts = array();
|
|
$i = 0;
|
|
|
|
while($row = $result->fetchArray())
|
|
{
|
|
$accounts[$i] = $row;
|
|
++$i;
|
|
}
|
|
}
|
|
else
|
|
die($error);
|
|
|
|
return $accounts;
|
|
}
|
|
|
|
public function list_serveralias($domain)
|
|
{
|
|
$database = $this->db;
|
|
|
|
if ($domain == NULL)
|
|
return NULL;
|
|
|
|
$query = "SELECT * FROM Serveralias WHERE domain = '$domain'";
|
|
|
|
$result = $database->query($query);
|
|
if ($result != FALSE)
|
|
{
|
|
$serveralias = array();
|
|
$i = 0;
|
|
|
|
while($row = $result->fetchArray())
|
|
{
|
|
$serveralias[$i] = $row;
|
|
++$i;
|
|
}
|
|
}
|
|
else
|
|
die($error);
|
|
|
|
return $serveralias;
|
|
}
|
|
|
|
public function is_serveralias($domain, $alias)
|
|
{
|
|
$database = $this->db;
|
|
|
|
if ($domain == NULL)
|
|
return 0;
|
|
if ($alias == NULL)
|
|
return 0;
|
|
|
|
$query = "SELECT id FROM Serveralias WHERE domain = '$domain' and alias = '$alias'";
|
|
|
|
$result = $database->query($query);
|
|
if ($result != FALSE)
|
|
{
|
|
$row = $result->fetchArray();
|
|
return !!($row['id']);
|
|
}
|
|
|
|
return 0;
|
|
}
|
|
|
|
}
|