1<?php 2 3namespace dokuwiki\plugin\struct\meta; 4 5/** 6 * Class SchemaBuilder 7 * 8 * This class builds and updates the schema definitions for our tables. This includes CREATEing and ALTERing 9 * the actual data tables as well as updating the meta information in our meta data tables. 10 * 11 * To use, simply instantiate a new object of the Builder and run the build() method on it. 12 * 13 * Note: even though data tables use a data_ prefix in the database, this prefix is internal only and should 14 * never be passed as $table anywhere! 15 * 16 * @package dokuwiki\plugin\struct\meta 17 */ 18class SchemaBuilder { 19 20 /** 21 * @var array The posted new data for the schema 22 * @see Schema::AdminEditor() 23 */ 24 protected $data = array(); 25 26 protected $user; 27 28 /** 29 * @var string The table name associated with the schema 30 */ 31 protected $table = ''; 32 33 /** 34 * @var Schema the previously valid schema for this table 35 */ 36 protected $oldschema; 37 38 /** @var int the ID of the newly created schema */ 39 protected $newschemaid = 0; 40 41 /** @var \helper_plugin_struct_db */ 42 protected $helper; 43 44 /** @var \helper_plugin_sqlite|null */ 45 protected $sqlite; 46 47 /** @var int the time for which this schema should be created - default to time() can be overriden for tests */ 48 protected $time = 0; 49 50 /** 51 * SchemaBuilder constructor. 52 * 53 * @param string $table The table's name 54 * @param array $data The defining of the table (basically what get's posted in the schema editor form) 55 * @see Schema::AdminEditor() 56 */ 57 public function __construct($table, $data) { 58 $this->table = $table; 59 $this->data = $data; 60 $this->oldschema = new Schema($table, 0, $data['islookup']); 61 62 $this->helper = plugin_load('helper', 'struct_db'); 63 $this->sqlite = $this->helper->getDB(); 64 $this->user = $_SERVER['REMOTE_USER']; 65 } 66 67 /** 68 * Create the new schema 69 * 70 * @param int $time when to create this schema 0 for now 71 * @return bool|int the new schema id on success 72 */ 73 public function build($time=0) { 74 $this->time = $time; 75 $this->fixLabelUniqueness(); 76 77 $this->sqlite->query('BEGIN TRANSACTION'); 78 $ok = true; 79 // create the data table if new schema 80 if(!$this->oldschema->getId()) { 81 if($this->oldschema->isLookup()) { 82 $ok = $this->newLookupTable(); 83 } else { 84 $ok = $this->newDataTable(); 85 } 86 } 87 88 // create a new schema 89 $ok = $ok && $this->newSchema(); 90 91 // update column info 92 $ok = $ok && $this->updateColumns(); 93 $ok = $ok && $this->addColumns(); 94 95 if (!$ok) { 96 $this->sqlite->query('ROLLBACK TRANSACTION'); 97 return false; 98 } 99 $this->sqlite->query('COMMIT TRANSACTION'); 100 101 return $this->newschemaid; 102 } 103 104 /** 105 * Makes sure all labels in the schema to save are unique 106 */ 107 protected function fixLabelUniqueness() { 108 $labels = array(); 109 110 if(isset($this->data['cols'])) foreach($this->data['cols'] as $idx => $column) { 111 $this->data['cols'][$idx]['label'] = $this->fixLabel($column['label'], $labels); 112 } 113 114 if(isset($this->data['new'])) foreach($this->data['new'] as $idx => $column) { 115 $this->data['new'][$idx]['label'] = $this->fixLabel($column['label'], $labels); 116 } 117 } 118 119 /** 120 * Creates a unique label from the given one 121 * 122 * @param string $wantedlabel 123 * @param array $labels list of already assigned labels (will be filled) 124 * @return string 125 */ 126 protected function fixLabel($wantedlabel, &$labels) { 127 $wantedlabel = trim($wantedlabel); 128 $fixedlabel = $wantedlabel; 129 $idx = 1; 130 while(isset($labels[utf8_strtolower($fixedlabel)])) { 131 $fixedlabel = $wantedlabel.$idx++; 132 } 133 // did we actually do a rename? apply it. 134 if($fixedlabel != $wantedlabel) { 135 msg(sprintf($this->helper->getLang('duplicate_label'), $wantedlabel, $fixedlabel), -1); 136 $this->data['cols']['label'] = $fixedlabel; 137 } 138 $labels[utf8_strtolower($fixedlabel)] = 1; 139 return $fixedlabel; 140 } 141 142 /** 143 * Creates a new schema 144 */ 145 protected function newSchema() { 146 if(!$this->time) $this->time = time(); 147 148 $config = $this->data['config'] ?: '{}'; 149 150 /** @noinspection SqlResolve */ 151 $sql = "INSERT INTO schemas (tbl, ts, islookup, user, config) VALUES (?, ?, ?, ?, ?)"; 152 $this->sqlite->query($sql, $this->table, $this->time, (int) $this->oldschema->isLookup(), $this->user, $config); 153 $res = $this->sqlite->query('SELECT last_insert_rowid()'); 154 $this->newschemaid = $this->sqlite->res2single($res); 155 $this->sqlite->res_close($res); 156 if(!$this->newschemaid) return false; 157 return true; 158 } 159 160 /** 161 * Updates all the existing column infos and adds them to the new schema 162 */ 163 protected function updateColumns() { 164 foreach($this->oldschema->getColumns() as $column) { 165 $oldEntry = $column->getType()->getAsEntry(); 166 $oldTid = $column->getTid(); 167 $newEntry = $oldEntry; 168 $newTid = $oldTid; 169 $sort = $column->getSort(); 170 if(isset($this->data['cols'][$column->getColref()])){ 171 // todo I'm not too happy with this hardcoded here - we should probably have a list of fields at one place 172 $newEntry['config'] = $this->data['cols'][$column->getColref()]['config']; 173 $newEntry['label'] = $this->data['cols'][$column->getColref()]['label']; 174 $newEntry['ismulti'] = $this->data['cols'][$column->getColref()]['ismulti']; 175 $newEntry['class'] = $this->data['cols'][$column->getColref()]['class']; 176 $sort = $this->data['cols'][$column->getColref()]['sort']; 177 $enabled = (bool) $this->data['cols'][$column->getColref()]['isenabled']; 178 179 // when the type definition has changed, we create a new one 180 if(array_diff_assoc($oldEntry, $newEntry)) { 181 $ok = $this->sqlite->storeEntry('types', $newEntry); 182 if(!$ok) return false; 183 $res = $this->sqlite->query('SELECT last_insert_rowid()'); 184 if(!$res) return false; 185 $newTid = $this->sqlite->res2single($res); 186 $this->sqlite->res_close($res); 187 if ($oldEntry['ismulti'] == false && $newEntry['ismulti'] == '1') { 188 $this->migrateSingleToMulti($this->oldschema->getTable(), $column->getColref()); 189 } 190 } 191 } else { 192 $enabled = false; // no longer there for some reason 193 } 194 195 // add this type to the schema columns 196 $schemaEntry = array( 197 'sid' => $this->newschemaid, 198 'colref' => $column->getColref(), 199 'enabled' => $enabled, 200 'tid' => $newTid, 201 'sort' => $sort 202 ); 203 $ok = $this->sqlite->storeEntry('schema_cols', $schemaEntry); 204 if(!$ok) return false; 205 } 206 return true; 207 } 208 209 /** 210 * Write the latest value from an entry in a data_ table to the corresponding multi_table 211 * 212 * @param string $table 213 * @param int $colref 214 */ 215 protected function migrateSingleToMulti($table, $colref) { 216 /** @noinspection SqlResolve */ 217 $sqlSelect = "SELECT pid, rev, col$colref AS value FROM data_$table WHERE latest = 1"; 218 $res = $this->sqlite->query($sqlSelect); 219 $valueSet = $this->sqlite->res2arr($res); 220 $this->sqlite->res_close($res); 221 $valueString = array(); 222 $arguments = array(); 223 foreach ($valueSet as $values) { 224 if (blank($values['value']) || trim($values['value']) == '') { 225 continue; 226 } 227 $valueString[] = "(?, ?, ?, ?, ?)"; 228 $arguments = array_merge($arguments, array($colref, $values['pid'], $values['rev'], 1, $values['value'])); 229 } 230 if (empty($valueString)) { 231 return; 232 } 233 $valueString = join(',', $valueString); 234 /** @noinspection SqlResolve */ 235 $sqlInsert = "INSERT OR REPLACE INTO multi_$table (colref, pid, rev, row, value) VALUES $valueString"; 236 $this->sqlite->query($sqlInsert, $arguments); 237 } 238 239 /** 240 * Adds new columns to the new schema 241 * 242 * @return bool 243 */ 244 protected function addColumns() { 245 if(!isset($this->data['new'])) return true; 246 247 $colref = count($this->oldschema->getColumns())+1; 248 249 foreach($this->data['new'] as $column) { 250 if(!$column['isenabled']) continue; // we do not add a disabled column 251 252 // todo this duplicates the hardcoding as in the function above 253 $newEntry = array(); 254 $newEntry['config'] = $column['config']; 255 $newEntry['label'] = $column['label']; 256 $newEntry['ismulti'] = $column['ismulti']; 257 $newEntry['class'] = $column['class']; 258 $sort = $column['sort']; 259 260 261 // only save if the column got a name 262 if(!$newEntry['label']) continue; 263 264 // add new column to the data table 265 if(!$this->addDataTableColumn($colref)) { 266 return false; 267 } 268 269 // save the type 270 $ok = $this->sqlite->storeEntry('types', $newEntry); 271 if(!$ok) return false; 272 $res = $this->sqlite->query('SELECT last_insert_rowid()'); 273 if(!$res) return false; 274 $newTid = $this->sqlite->res2single($res); 275 $this->sqlite->res_close($res); 276 277 278 // add this type to the schema columns 279 $schemaEntry = array( 280 'sid' => $this->newschemaid, 281 'colref' => $colref, 282 'enabled' => true, 283 'tid' => $newTid, 284 'sort' => $sort 285 ); 286 $ok = $this->sqlite->storeEntry('schema_cols', $schemaEntry); 287 if(!$ok) return false; 288 $colref++; 289 } 290 291 return true; 292 } 293 294 /** 295 * Create a completely new data table with no columns yet also create the appropriate 296 * multi value table for the schema 297 * 298 * @todo how do we want to handle indexes? 299 * @return bool 300 */ 301 protected function newDataTable() { 302 $ok = true; 303 304 $tbl = 'data_' . $this->table; 305 $sql = "CREATE TABLE $tbl ( 306 pid NOT NULL, 307 rev INTEGER NOT NULL, 308 latest BOOLEAN NOT NULL DEFAULT 0, 309 PRIMARY KEY(pid, rev) 310 )"; 311 $ok = $ok && (bool) $this->sqlite->query($sql); 312 313 $tbl = 'multi_' . $this->table; 314 $sql = "CREATE TABLE $tbl ( 315 colref INTEGER NOT NULL, 316 pid NOT NULL, 317 rev INTEGER NOT NULL, 318 latest INTEGER NOT NULL DEFAULT 0, 319 row INTEGER NOT NULL, 320 value, 321 PRIMARY KEY(colref, pid, rev, row) 322 );"; 323 $ok = $ok && (bool) $this->sqlite->query($sql); 324 325 return $ok; 326 } 327 328 /** 329 * Creates a new lookup table with no columns 330 * 331 * This is basically the same as @see newDataTable() but sets 332 * different primary keys and types 333 * 334 * @return bool 335 */ 336 protected function newLookupTable() { 337 $ok = true; 338 339 $tbl = 'data_' . $this->table; 340 $sql = "CREATE TABLE $tbl ( 341 pid INTEGER PRIMARY KEY, 342 rev INTEGER NOT NULL DEFAULT 0, 343 latest BOOLEAN NOT NULL DEFAULT 1 344 )"; 345 $ok = $ok && (bool) $this->sqlite->query($sql); 346 347 $tbl = 'multi_' . $this->table; 348 $sql = "CREATE TABLE $tbl ( 349 colref INTEGER NOT NULL, 350 pid INTEGER NOT NULL, 351 rev INTEGER NOT NULL DEFAULT 0, 352 latest INTEGER NOT NULL DEFAULT 0, 353 row INTEGER NOT NULL, 354 value, 355 PRIMARY KEY(colref, pid, row) 356 );"; 357 $ok = $ok && (bool) $this->sqlite->query($sql); 358 359 return $ok; 360 } 361 362 /** 363 * Add an additional column to the existing data table 364 * 365 * @param int $index the new column index to add 366 * @return bool 367 */ 368 protected function addDataTableColumn($index) { 369 $tbl = 'data_' . $this->table; 370 $sql = " ALTER TABLE $tbl ADD COLUMN col$index DEFAULT ''"; 371 if(! $this->sqlite->query($sql)) { 372 return false; 373 } 374 return true; 375 } 376 377 /** 378 * @param string $user 379 * @return SchemaBuilder 380 */ 381 public function setUser($user) { 382 $this->user = $user; 383 return $this; 384 } 385 386 387} 388