388 lines
8.2 KiB
PHP
388 lines
8.2 KiB
PHP
<?php
|
|
/**
|
|
* Singleton-Klasse zur Herstellung der Datenbank-Verbindung
|
|
*
|
|
* @author Christian Steinle
|
|
* @date 02.08.2016
|
|
*
|
|
* @copyright CS medien- & kommunikationssysteme (http://www.steinle-computer.de)
|
|
*/
|
|
|
|
namespace Helper;
|
|
|
|
use mysqli;
|
|
|
|
|
|
class Database
|
|
{
|
|
/**
|
|
* Name der Datenbank-Tabelle für das Entsprechende Modell
|
|
*/
|
|
const TBL_NAME = '';
|
|
|
|
/**
|
|
* Name des Datenbank-Feldes mit dem PrimaryKey
|
|
*/
|
|
const PRIMARY_KEY = '';
|
|
|
|
/**
|
|
* Sortierung der Elemente für die Methode getIndex
|
|
*/
|
|
const ORDER_BY = '';
|
|
|
|
/**
|
|
* Der Filter für den Aufruf von getIndex()
|
|
* @var string
|
|
*/
|
|
static $filter = '1=1';
|
|
|
|
/**
|
|
* Die Beziehung einer Datenbank-Tabelle zu einer anderen
|
|
* $relations = array(
|
|
* 0 => array(
|
|
* 'ownKey' => 'Spaltenname der eigenen Tabelle',
|
|
* 'foreignTable' => 'Tabellenname der "Fremd"-Tabelle',
|
|
* 'foreignKey' => 'Spaltenname der "Fremd"-Tabelle
|
|
* ),
|
|
* );
|
|
* @var array
|
|
*/
|
|
static $relations = array();
|
|
|
|
/**
|
|
* Hier werden die Daten von getIndex() gespeichert
|
|
* @var array
|
|
*/
|
|
static $data = array();
|
|
|
|
/**
|
|
* @var \mysqli|null
|
|
*/
|
|
protected static $db = null;
|
|
|
|
|
|
/**
|
|
* Konstruktor schützen, weil Singleton
|
|
* @author Christian Steinle
|
|
*/
|
|
protected function __construct()
|
|
{
|
|
}
|
|
|
|
/**
|
|
* Liefert ein mehrdimensionales, assoziatives Array mit allen Datensätzen, die zum Filter passen
|
|
* als Schlüssel der ersten Dimension dient der Wert des Primär-Schlüssels der Datenbank-Tabelle
|
|
* Legt alle Daten in self::$data ab
|
|
* @author Christian Steinle
|
|
*
|
|
* @see self::query()
|
|
* @see self::$data
|
|
*
|
|
* TODO: getIndex für static::$relations analog zu getItem
|
|
*
|
|
* @return array
|
|
*/
|
|
final public static function getIndex()
|
|
{
|
|
static::setRelations();
|
|
$sql = 'SELECT * FROM ' . static::TBL_NAME . ' WHERE ' . static::$filter . ' ' . static::ORDER_BY . ';';
|
|
self::$data = self::query($sql);
|
|
return self::$data;
|
|
}
|
|
|
|
/**
|
|
* Setzt die Variable zur Verbindung verschiedener Datenbank-Tabellen
|
|
*/
|
|
public static function setRelations()
|
|
{
|
|
static::$relations = array();
|
|
}
|
|
|
|
/**
|
|
* Liefert ein mehrdimensionales, assoziatives Array mit allen Datensätzen, die zum SQL-Query passen
|
|
* als Schlüssel der ersten Dimension dient der Wert des Primär-Schlüssels der Datenbank-Tabelle
|
|
* @author Christian Steinle
|
|
*
|
|
* @param string $sql
|
|
* @return array
|
|
*/
|
|
final protected static function query($sql)
|
|
{
|
|
self::getInstance();
|
|
$result = self::$db->query($sql);
|
|
$data = array();
|
|
|
|
while ($tmpData = $result->fetch_assoc())
|
|
{
|
|
$data[$tmpData[static::PRIMARY_KEY]] = $tmpData;
|
|
}
|
|
|
|
return $data;
|
|
}
|
|
|
|
/**
|
|
* Stellt die Datenbank-Verbindung her
|
|
* @author Christian Steinle
|
|
*
|
|
* return void
|
|
*/
|
|
final protected static function getInstance()
|
|
{
|
|
if (null === self::$db)
|
|
{
|
|
self::$db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
|
|
self::$db->set_charset('utf8');
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Macht das Update für den Eintrag in einer Datenbank-Tabelle
|
|
* @author Christian Steinle
|
|
*
|
|
* @param array $request
|
|
* @return int
|
|
*/
|
|
final public static function update(array $request)
|
|
{
|
|
static::beforeUpdate($request);
|
|
static::setRelations();
|
|
|
|
/**
|
|
* @var Database $class
|
|
*/
|
|
foreach (static::$relations as $relation)
|
|
{
|
|
$class = $relation['foreignModel'];
|
|
$request[$relation['ownKey']] = $class::update($request);
|
|
}
|
|
|
|
|
|
$primaryKey = intval($request[static::PRIMARY_KEY]);
|
|
unset($request[static::PRIMARY_KEY]);
|
|
|
|
$model = self::queryModel(static::TBL_NAME, true);
|
|
$updateData = array();
|
|
|
|
foreach ($request as $fieldName => $fieldValue)
|
|
{
|
|
if (in_array($fieldName, $model))
|
|
{
|
|
$updateData[] = $fieldName . ' = "' . self::$db->real_escape_string($fieldValue) . '"';
|
|
}
|
|
}
|
|
|
|
if (empty($updateData))
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
$sql = 'UPDATE ' . static::TBL_NAME . ' SET ' . implode(', ', $updateData) . ' WHERE ' . static::PRIMARY_KEY . ' = ' . $primaryKey . ' LIMIT 1;';
|
|
$result = self::$db->query($sql);
|
|
if ($result !== true)
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
return $primaryKey;
|
|
}
|
|
|
|
/**
|
|
* Liefert die Spalten einer Datenbank-Tabelle
|
|
* @author Christian Steinle
|
|
*
|
|
* @param string $tableName
|
|
* @param bool $fieldsOnly
|
|
* @return array
|
|
*/
|
|
final protected static function queryModel($tableName, $fieldsOnly = false)
|
|
{
|
|
self::getInstance();
|
|
$sql = 'SHOW COLUMNS FROM ' . $tableName . ';';
|
|
$result = self::$db->query($sql);
|
|
$data = array();
|
|
|
|
while ($tmpData = $result->fetch_assoc())
|
|
{
|
|
if ($fieldsOnly === true)
|
|
{
|
|
$data[] = $tmpData['Field'];
|
|
}
|
|
else
|
|
{
|
|
$data[] = $tmpData;
|
|
}
|
|
}
|
|
|
|
return $data;
|
|
}
|
|
|
|
/**
|
|
* Schreibt einen neuen Eintrag in einer Datenbank-Tabelle
|
|
* @author Christian Steinle
|
|
*
|
|
* @param array $request
|
|
* @return int
|
|
*/
|
|
final public static function insert(array $request)
|
|
{
|
|
static::beforeInsert($request);
|
|
static::setRelations();
|
|
|
|
/**
|
|
* @var Database $class
|
|
*/
|
|
foreach (static::$relations as $relation)
|
|
{
|
|
$class = $relation['foreignModel'];
|
|
$request[$relation['ownKey']] = $class::insert($request);
|
|
}
|
|
|
|
$model = self::queryModel(static::TBL_NAME, true);
|
|
$updateData = array();
|
|
|
|
foreach ($request as $fieldName => $fieldValue)
|
|
{
|
|
if (in_array($fieldName, $model))
|
|
{
|
|
$updateData[$fieldName] = '"' . self::$db->real_escape_string($fieldValue) . '"';
|
|
}
|
|
}
|
|
|
|
if (empty($updateData))
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
$keys = implode(', ', array_keys($updateData));
|
|
$values = implode(', ', $updateData);
|
|
$sql = 'INSERT INTO ' . static::TBL_NAME . ' (' . $keys . ') VALUES (' . $values . ');';
|
|
$result = self::$db->query($sql);
|
|
|
|
if ($result !== true)
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
return self::$db->insert_id;
|
|
}
|
|
|
|
/**
|
|
* Entfernt einen Eintrag aus einer Datenbank-Tabelle
|
|
* @author Christian Steinle
|
|
*
|
|
* @param int $id
|
|
* @return int
|
|
*/
|
|
final public static function delete($id)
|
|
{
|
|
static::beforeDelete($id);
|
|
static::setRelations();
|
|
|
|
if (!empty(static::$relations))
|
|
{
|
|
$data = static::getItem($id);
|
|
|
|
/**
|
|
* @var Database $class
|
|
*/
|
|
foreach (static::$relations as $relation)
|
|
{
|
|
$class = $relation['foreignModel'];
|
|
$relationID = intval($data[$relation['foreignKey']]);
|
|
$class::delete($relationID);
|
|
}
|
|
}
|
|
|
|
$query = 'DELETE FROM ' . static::TBL_NAME . ' WHERE ' . static::PRIMARY_KEY . ' = ' . $id . ' LIMIT 1;';
|
|
$result = self::$db->query($query);
|
|
|
|
return (($result) ? 1 : 0);
|
|
}
|
|
|
|
/**
|
|
* Liefert ein assoziatives Array des Datensatzes mit der übergebenen ID
|
|
* @author Christian Steinle
|
|
*
|
|
* @param int $id
|
|
* @return array
|
|
*/
|
|
final public static function getItem($id)
|
|
{
|
|
static::setRelations();
|
|
|
|
if (empty(static::$relations))
|
|
{
|
|
$sql = 'SELECT * FROM ' . static::TBL_NAME . ' WHERE ' . static::PRIMARY_KEY . ' = ' . $id . ';';
|
|
}
|
|
else
|
|
{
|
|
$tables = array(static::TBL_NAME);
|
|
$matches = array();
|
|
foreach (static::$relations as $relation)
|
|
{
|
|
$class = $relation['foreignModel'];
|
|
$tables[] = $class::TBL_NAME;
|
|
$matches[] = static::TBL_NAME . '.' . $relation['ownKey'] . ' = ' . $class::TBL_NAME . '.' . $relation['foreignKey'];
|
|
}
|
|
|
|
$sql = 'SELECT * FROM ' . implode(', ', $tables) . ' WHERE ' . static::PRIMARY_KEY . ' = ' . $id . ' AND ' . implode(' AND ', $matches);
|
|
}
|
|
|
|
$data = self::query($sql);
|
|
return (!is_array($data) || empty($data)) ? array() : current($data);
|
|
}
|
|
|
|
|
|
/**
|
|
* Setzt den Filter für die Datenbank zur späteren Verwendung
|
|
* @author Christian Steinle
|
|
*
|
|
* @param string $filter
|
|
*/
|
|
public static function setFilter($filter)
|
|
{
|
|
static::$filter = $filter;
|
|
}
|
|
|
|
|
|
/**
|
|
* Funktion, die vor dem Erstellen eines Datenbank-Eintrags ausgeführt wird
|
|
* @author Christian Steinle
|
|
*
|
|
* @param array $request
|
|
*/
|
|
protected static function beforeInsert(array &$request)
|
|
{
|
|
}
|
|
|
|
|
|
/**
|
|
* Funktion, die vor dem Löschen eines Datenbank-Eintrags ausgeführt wird
|
|
* @author Christian Steinle
|
|
*
|
|
* @param int $id
|
|
*/
|
|
protected static function beforeDelete($id)
|
|
{
|
|
}
|
|
|
|
|
|
/**
|
|
* Funktion, die vor dem Update eines Datenbank-Eintrags ausgeführt wird
|
|
* @author Christian Steinle
|
|
*
|
|
* @param array $request
|
|
*/
|
|
protected static function beforeUpdate(array &$request)
|
|
{
|
|
}
|
|
|
|
|
|
/**
|
|
* Schützen, um das Klonen des Singletons zu vermeiden
|
|
* @author Christian Steinle
|
|
*/
|
|
final protected function __clone()
|
|
{
|
|
}
|
|
} |