DBUtil.php
1 <?php
2 /**
3  * wCMF - wemove Content Management Framework
4  * Copyright (C) 2005-2020 wemove digital solutions GmbH
5  *
6  * Licensed under the terms of the MIT License.
7  *
8  * See the LICENSE file distributed with this work for
9  * additional information.
10  */
11 namespace wcmf\lib\util;
12 
13 use PDO;
20 use Laminas\Db\Adapter\Adapter;
21 
22 /**
23  * DBUtil provides database helper functions.
24  *
25  * @author ingo herwig <ingo@wemove.com>
26  */
27 class DBUtil {
28 
29  private static function createConnection($connectionParams) {
30  // connect
31  if (isset($connectionParams['dbType']) && isset($connectionParams['dbHostName']) &&
32  isset($connectionParams['dbUserName']) && isset($connectionParams['dbPassword']) &&
33  isset($connectionParams['dbName'])) {
34 
35  try {
36  $charSet = isset($connectionParams['dbCharSet']) ? $connectionParams['dbCharSet'] : 'utf8';
37  $dbType = strtolower($connectionParams['dbType']);
38 
39  // create new connection
40  $pdoParams = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];
41  // driver specific
42  switch ($dbType) {
43  case 'mysql':
44  $pdoParams[PDO::MYSQL_ATTR_USE_BUFFERED_QUERY] = true;
45  $pdoParams[PDO::MYSQL_ATTR_INIT_COMMAND] = "SET NAMES ".$charSet;
46  break;
47  case 'sqlite':
48  if (strtolower($connectionParams['dbName']) == ':memory:') {
49  $pdoParams[PDO::ATTR_PERSISTENT] = true;
50  }
51  else {
52  $connectionParams['dbName'] = FileUtil::realpath(WCMF_BASE.$connectionParams['dbName']);
53  }
54  break;
55  }
56 
57  $params = [
58  'host' => $connectionParams['dbHostName'],
59  'username' => $connectionParams['dbUserName'],
60  'password' => $connectionParams['dbPassword'],
61  'database' => $connectionParams['dbName'],
62  'driver' => 'Pdo_'.ucfirst($connectionParams['dbType']),
63  'driver_options' => $pdoParams
64  ];
65  if (!empty($connectionParams['dbPort'])) {
66  $params['port'] = $connectionParams['dbPort'];
67  }
68  $adapter = new Adapter($params);
69  $conn = $adapter->getDriver()->getConnection()->getResource();
70  return $conn;
71  }
72  catch(\Exception $ex) {
73  throw new PersistenceException("Connection to ".$connectionParams['dbHostName'].".".
74  $connectionParams['dbName']." failed: ".$ex->getMessage());
75  }
76  }
77  else {
78  throw new IllegalArgumentException("Wrong parameters for creating connection.");
79  }
80  }
81 
82  /**
83  * Execute a sql script. Execution is done inside a transaction, which is rolled back in case of failure.
84  * @param $file The filename of the sql script
85  * @param $initSection The name of the configuration section that defines the database connection
86  * @return Boolean whether execution succeeded or not.
87  */
88  public static function executeScript($file, $initSection) {
89  $logger = LogManager::getLogger(__CLASS__);
90  if (file_exists($file)) {
91  $logger->info('Executing SQL script '.$file.' ...');
92 
93  // find init params
94  $config = ObjectFactory::getInstance('configuration');
95  if (($connectionParams = $config->getSection($initSection)) === false) {
96  throw new ConfigurationException("No '".$initSection."' section given in configfile.");
97  }
98  // connect to the database
99  $conn = self::createConnection($connectionParams);
100 
101  $logger->debug('Starting transaction ...');
102  $conn->beginTransaction();
103 
104  $exception = null;
105  $fh = fopen($file, 'r');
106  if ($fh) {
107  while (!feof($fh)) {
108  $command = fgets($fh, 8192);
109  if (strlen(trim($command)) > 0) {
110  $logger->debug('Executing command: '.preg_replace('/[\n]+$/', '', $command));
111  try {
112  $conn->query($command);
113  }
114  catch(\Exception $ex) {
115  $exception = $ex;
116  break;
117  }
118  }
119  }
120  fclose($fh);
121  }
122  if ($exception == null) {
123  $logger->debug('Execution succeeded, committing ...');
124  $conn->commit();
125  }
126  else {
127  $logger->error('Execution failed. Reason'.$exception->getMessage());
128  $logger->debug('Rolling back ...');
129  $conn->rollBack();
130  }
131  $logger->debug('Finished SQL script '.$file.'.');
132  }
133  else {
134  $logger->error('SQL script '.$file.' not found.');
135  }
136  }
137 
138  /**
139  * Duplicate a database on the same server (same user). This works only for MySQL databases.
140  * @param $srcName The name of the source database
141  * @param $destName The name of the source database
142  * @param $server The name of the database server
143  * @param $user The user name
144  * @param $password The password
145  */
146  public static function copyDatabase($srcName, $destName, $server, $user, $password) {
147  $logger = LogManager::getLogger(__CLASS__);
148  if ($srcName && $destName && $server && $user) {
149  self::createDatabase($destName, $server, $user, $password);
150 
151  // setup connection
152  $conn =null;
153  try {
154  $conn = new PDO("mysql:host=$server", $user, $password);
155  }
156  catch(\Exception $ex) {
157  throw new PersistenceException("Couldn't connect to MySql: ".$ex->getMessage());
158  }
159 
160  $conn->beginTransaction();
161  try {
162  // get table list from source database
163  foreach ($conn->query("SHOW TABLES FROM ".$srcName) as $row) {
164  // create new table
165  $sqlStmt = "CREATE TABLE ".$destName.".".$row[0]." LIKE ".$srcName.".".$row[0];
166  $logger->debug($sqlStmt);
167  $result = $conn->query($sqlStmt);
168  if (!$result) {
169  throw new PersistenceException("Couldn't create table: ".$conn->errorInfo());
170  }
171 
172  // insert data
173  $sqlStmt = "INSERT INTO ".$destName.".".$row[0]." SELECT * FROM ".$srcName.".".$row[0];
174  $logger->debug($sqlStmt);
175  $result = $conn->query($sqlStmt);
176  if (!$result) {
177  throw new PersistenceException("Couldn't copy data: ".$conn->errorInfo());
178  }
179  $conn->commit();
180  }
181  } catch (\Exception $ex) {
182  $conn->rollback();
183  }
184  }
185  }
186 
187  /**
188  * Crate a database on the server. This works only for MySQL databases.
189  * @param $name The name of the source database
190  * @param $server The name of the database server
191  * @param $user The user name
192  * @param $password The password
193  */
194  public static function createDatabase($name, $server, $user, $password) {
195  $created = false;
196  if($name && $server && $user) {
197  // setup connection
198  $conn =null;
199  try {
200  $conn = new PDO("mysql:host=$server", $user, $password);
201  }
202  catch(\Exception $ex) {
203  throw new PersistenceException("Couldn't connect to MySql: ".$ex->getMessage());
204  }
205  // create database
206  $sqlStmt = "CREATE DATABASE IF NOT EXISTS ".$name;
207  $result = $conn->query($sqlStmt);
208  if ($result) {
209  $created = true;
210  }
211  if (!$created) {
212  throw new PersistenceException("Couldn't create database: ".$conn->errorInfo());
213  }
214  }
215  }
216 }
217 ?>
static executeScript($file, $initSection)
Execute a sql script.
Definition: DBUtil.php:88
PersistenceException signals an exception in the persistence service.
IllegalArgumentException signals an exception in method arguments.
ConfigurationException signals an exception in the configuration.
static copyDatabase($srcName, $destName, $server, $user, $password)
Duplicate a database on the same server (same user).
Definition: DBUtil.php:146
FileUtil provides basic support for file functionality like HTTP file upload.
Definition: FileUtil.php:22
static getLogger($name)
Get the logger with the given name.
Definition: LogManager.php:37
static getInstance($name, $dynamicConfiguration=[])
Utility classes.
Definition: namespaces.php:97
static createDatabase($name, $server, $user, $password)
Crate a database on the server.
Definition: DBUtil.php:194
DBUtil provides database helper functions.
Definition: DBUtil.php:27
LogManager is used to retrieve Logger instances.
Definition: LogManager.php:20
static realpath($path)
Realpath function that also works for non existing paths code from http://www.php....
Definition: FileUtil.php:244
ObjectFactory implements the service locator pattern by wrapping a Factory instance and providing sta...