| @@ -1,0 +1,290 @@ | ||
| 1 | +<?php | |
| 2 | +/** | |
| 3 | + * @package VikBooking | |
| 4 | + * @subpackage core | |
| 5 | + * @author E4J s.r.l. | |
| 6 | + * @copyright Copyright (C) 2021 E4J s.r.l. All Rights Reserved. | |
| 7 | + * @license http://www.gnu.org/licenses/gpl-2.0.html GNU/GPL | |
| 8 | + * @link https://vikwp.com | |
| 9 | + */ | |
| 10 | + | |
| 11 | +// No direct access | |
| 12 | +defined('ABSPATH') or die('No script kiddies please!'); | |
| 13 | + | |
| 14 | +/** | |
| 15 | + * SQL Backup export rule. | |
| 16 | + * | |
| 17 | + * @since 1.5 | |
| 18 | + */ | |
| 19 | +class VBOBackupExportRuleSql extends VBOBackupExportRule | |
| 20 | +{ | |
| 21 | + /** | |
| 22 | + * An array of SQL statements. | |
| 23 | + * | |
| 24 | + * @var array | |
| 25 | + */ | |
| 26 | + protected $queries = []; | |
| 27 | + | |
| 28 | + /** | |
| 29 | + * The database table. | |
| 30 | + * | |
| 31 | + * @var string | |
| 32 | + */ | |
| 33 | + protected $table; | |
| 34 | + | |
| 35 | + /** | |
| 36 | + * The information of the database columns. | |
| 37 | + * | |
| 38 | + * @var object | |
| 39 | + */ | |
| 40 | + private $columns; | |
| 41 | + | |
| 42 | + /** | |
| 43 | + * Indicates the maximum number of rows under the same INSERT. | |
| 44 | + * | |
| 45 | + * @var integer | |
| 46 | + */ | |
| 47 | + private $maxRowsPerInsert = 100; | |
| 48 | + | |
| 49 | + /** | |
| 50 | + * Returns the rules instructions. | |
| 51 | + * | |
| 52 | + * @return mixed | |
| 53 | + */ | |
| 54 | + public function getData() | |
| 55 | + { | |
| 56 | + return $this->queries; | |
| 57 | + } | |
| 58 | + | |
| 59 | + /** | |
| 60 | + * Configures the rule to work according to the specified data. | |
| 61 | + * | |
| 62 | + * @param string $data The database table name. | |
| 63 | + * | |
| 64 | + * @return void | |
| 65 | + */ | |
| 66 | + protected function setup($data) | |
| 67 | + { | |
| 68 | + $dbo = JFactory::getDbo(); | |
| 69 | + | |
| 70 | + // register table | |
| 71 | + $this->table = $data; | |
| 72 | + | |
| 73 | + // get all the columns of the table to export | |
| 74 | + $this->columns = $dbo->getTableColumns($this->table, $typeOnly = false); | |
| 75 | + | |
| 76 | + // get total count of records | |
| 77 | + $count = $this->getCount(); | |
| 78 | + | |
| 79 | + if (!$count) | |
| 80 | + { | |
| 81 | + // nothing to export | |
| 82 | + return; | |
| 83 | + } | |
| 84 | + | |
| 85 | + // get current database prefix | |
| 86 | + $prefix = $dbo->getPrefix(); | |
| 87 | + | |
| 88 | + // shows the table CREATE statement that creates the given table | |
| 89 | + $createLookup = $dbo->getTableCreate($this->table); | |
| 90 | + | |
| 91 | + // check whether the current drivers supports a tool to return the statement that | |
| 92 | + // was used to create the database table | |
| 93 | + if (isset($createLookup[$this->table])) | |
| 94 | + { | |
| 95 | + // extract statement from create lookup and replace prefix | |
| 96 | + $create = preg_replace("/`{$prefix}(vik(?:booking|channelmanager)_(?:[a-z0-9_]+))`/i", '`#__$1`', $createLookup[$this->table]); | |
| 97 | + | |
| 98 | + // register query to recreate the table from scratch | |
| 99 | + $this->registerQuery("DROP TABLE IF EXISTS `{$this->table}`"); | |
| 100 | + $this->registerQuery($create); | |
| 101 | + | |
| 102 | + // we don't need to alter the auto increment because it is already included | |
| 103 | + // within the create table statement | |
| 104 | + $alter_auto_increment = false; | |
| 105 | + } | |
| 106 | + else | |
| 107 | + { | |
| 108 | + // cannot fetch create table statment, truncate the table and assume (or "hope") | |
| 109 | + // that the database structure is the same | |
| 110 | + $this->registerQuery("TRUNCATE TABLE `$this->table`"); | |
| 111 | + | |
| 112 | + // update auto increment after copying all the records | |
| 113 | + $alter_auto_increment = true; | |
| 114 | + } | |
| 115 | + | |
| 116 | + $insertQuery = $dbo->getQuery(true); | |
| 117 | + | |
| 118 | + // prepare INSERT query | |
| 119 | + $insertQuery->insert($dbo->qn($this->table)); | |
| 120 | + | |
| 121 | + $app = JFactory::getApplication(); | |
| 122 | + | |
| 123 | + /** | |
| 124 | + * Trigger event to allow third party plugins to choose what are the columns to dump | |
| 125 | + * and whether the table should be skipped or not. | |
| 126 | + * | |
| 127 | + * Fires while attaching a rule to dump some SQL statements. | |
| 128 | + * | |
| 129 | + * @param array &$columns An associative array of supported database table columns, | |
| 130 | + * where the key is the column name and the value is a nested | |
| 131 | + * array holding the column information. | |
| 132 | + * @param string $table The name of the database table. | |
| 133 | + * | |
| 134 | + * @return boolean False to avoid including the table into the backup. | |
| 135 | + * | |
| 136 | + * @since 1.5 | |
| 137 | + */ | |
| 138 | + $results = $app->triggerEvent('onBeforeBackupDumpSqlVikBooking', [&$this->columns, $this->table]); | |
| 139 | + | |
| 140 | + if (in_array(false, $results, true)) | |
| 141 | + { | |
| 142 | + // a third-party plugin decided to skip the table | |
| 143 | + return; | |
| 144 | + } | |
| 145 | + | |
| 146 | + // iterate the columns | |
| 147 | + foreach ($this->columns as $column => $type) | |
| 148 | + { | |
| 149 | + $insertQuery->columns($dbo->qn($column)); | |
| 150 | + } | |
| 151 | + | |
| 152 | + // create SELECT query | |
| 153 | + $selectQuery = $dbo->getQuery(true)->select('*')->from($dbo->qn($this->table)); | |
| 154 | + | |
| 155 | + $offset = 0; | |
| 156 | + | |
| 157 | + while ($offset < $count) | |
| 158 | + { | |
| 159 | + $dbo->setQuery($selectQuery, $offset, $this->maxRowsPerInsert); | |
| 160 | + $dbo->execute(); | |
| 161 | + | |
| 162 | + $rows = $dbo->loadObjectList(); | |
| 163 | + | |
| 164 | + // clear previous values | |
| 165 | + $insertQuery->clear('values'); | |
| 166 | + | |
| 167 | + foreach ($rows as $row) | |
| 168 | + { | |
| 169 | + $values = array(); | |
| 170 | + | |
| 171 | + foreach ($this->columns as $k => $type) | |
| 172 | + { | |
| 173 | + if (!isset($row->{$k})) | |
| 174 | + { | |
| 175 | + // use NULL operator | |
| 176 | + $values[] = 'NULL'; | |
| 177 | + } | |
| 178 | + else | |
| 179 | + { | |
| 180 | + // escape the value | |
| 181 | + $values[] = $dbo->q($row->{$k}); | |
| 182 | + } | |
| 183 | + } | |
| 184 | + | |
| 185 | + $insertQuery->values(implode(',', $values)); | |
| 186 | + } | |
| 187 | + | |
| 188 | + // register query | |
| 189 | + $this->registerQuery((string) $insertQuery); | |
| 190 | + | |
| 191 | + // increase offset | |
| 192 | + $offset += $this->maxRowsPerInsert; | |
| 193 | + | |
| 194 | + // free space | |
| 195 | + unset($rows); | |
| 196 | + } | |
| 197 | + | |
| 198 | + // fetch table auto increment | |
| 199 | + if ($alter_auto_increment && ($ai = $this->getAutoIncrement($this->table))) | |
| 200 | + { | |
| 201 | + $this->registerQuery("ALTER TABLE `{$this->table}` AUTO_INCREMENT = {$ai}"); | |
| 202 | + } | |
| 203 | + } | |
| 204 | + | |
| 205 | + /** | |
| 206 | + * Helper method used to register the query inside the buffer. | |
| 207 | + * | |
| 208 | + * @param string $query The query to register. | |
| 209 | + * | |
| 210 | + * @return void | |
| 211 | + */ | |
| 212 | + protected function registerQuery($query) | |
| 213 | + { | |
| 214 | + /** | |
| 215 | + * @wponly | |
| 216 | + * | |
| 217 | + * While escaping a SQL string, WordPress replaces any "%" character with a random hash. | |
| 218 | + * Normally that hash is automatically unescaped while executing the query, but in our case, | |
| 219 | + * since we are dumping the query for later use, we have to manually unescape it, otherwise | |
| 220 | + * the WordPress website that is going to import the dump won't be able to revert the hash | |
| 221 | + * back, because the latter changes at every page loading. | |
| 222 | + * | |
| 223 | + * @since 1.6 | |
| 224 | + */ | |
| 225 | + if (VBOPlatformDetection::isWordPress()) | |
| 226 | + { | |
| 227 | + $query = JFactory::getDbo()->remove_placeholder_escape($query); | |
| 228 | + } | |
| 229 | + | |
| 230 | + if (!preg_match("/;$/", $query)) | |
| 231 | + { | |
| 232 | + $query .= ';'; | |
| 233 | + } | |
| 234 | + | |
| 235 | + $this->queries[] = $query; | |
| 236 | + } | |
| 237 | + | |
| 238 | + /** | |
| 239 | + * Counts the total number of rows inside the table. | |
| 240 | + * | |
| 241 | + * @return integer | |
| 242 | + */ | |
| 243 | + private function getCount() | |
| 244 | + { | |
| 245 | + $dbo = JFactory::getDbo(); | |
| 246 | + | |
| 247 | + // count rows | |
| 248 | + $q = $dbo->getQuery(true)->select('COUNT(1)')->from($dbo->qn($this->table)); | |
| 249 | + | |
| 250 | + $dbo->setQuery($q); | |
| 251 | + $dbo->execute(); | |
| 252 | + | |
| 253 | + return (int) $dbo->loadResult(); | |
| 254 | + } | |
| 255 | + | |
| 256 | + /** | |
| 257 | + * Fetches the correct auto increment to set for the given table. | |
| 258 | + * | |
| 259 | + * @return mixed The auto increment on success, null otherwise. | |
| 260 | + */ | |
| 261 | + private function getAutoIncrement() | |
| 262 | + { | |
| 263 | + $dbo = JFactory::getDbo(); | |
| 264 | + | |
| 265 | + $pk = null; | |
| 266 | + | |
| 267 | + // look for the primary key | |
| 268 | + foreach ($this->columns as $column => $info) | |
| 269 | + { | |
| 270 | + if ($info->Extra === 'auto_increment') | |
| 271 | + { | |
| 272 | + $pk = $column; | |
| 273 | + } | |
| 274 | + } | |
| 275 | + | |
| 276 | + if (!$pk) | |
| 277 | + { | |
| 278 | + // no primary keys with auto increment | |
| 279 | + return null; | |
| 280 | + } | |
| 281 | + | |
| 282 | + // fetch highest ID | |
| 283 | + $q = $dbo->getQuery(true)->select('MAX(' . $dbo->qn($pk) . ')')->from($dbo->qn($this->table)); | |
| 284 | + | |
| 285 | + $dbo->setQuery($q); | |
| 286 | + $dbo->execute(); | |
| 287 | + | |
| 288 | + return (int) $dbo->loadResult() + 1; | |
| 289 | + } | |
| 290 | +} | |
