database/php/irreco_webdb_database.php

   1 <?php
   2
   3 /*
   4  * irreco - Ir Remote Control
   5  * Copyright (C) 2007,2008  Arto Karppinen (arto.karppinen@iki.fi),
   6  * Joni Kokko (t5kojo01@students.oamk.fi),
   7  * Sami Mäki (kasmra@xob.kapsi.fi),
   8  * Harri Vattulainen (t5vaha01@students.oamk.fi)
   9  *
  10  * This program is free software; you can redistribute it and/or
  11  * modify it under the terms of the GNU General Public License
  12  * as published by the Free Software Foundation; either version 2
  13  * of the License, or (at your option) any later version.
  14  *
  15  * This program is distributed in the hope that it will be useful,
  16  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  17  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  18  * GNU General Public License for more details.
  19  *
  20  * You should have received a copy of the GNU General Public License
  21  * along with this program; if not, write to the Free Software Foundation,
  22  * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
  23  */
  24
  25 require_once 'MDB2.php';
  26 require_once 'irreco_webdb_log.php';
  27
  28 class IrrecoWebdbDatabase
  29 {
  30         private $db;
  31         private $log;
  32         private $prepared_query;
  33         private $previous_result_set;
  34
  35         function __construct($dsn)
  36         {
  37                 $options = array('debug' => 2,
  38                                  'result_buffering' => false);
  39
  40                 IrrecoLog::$database->log('Connecting to "' . $dsn . '".');
  41                 $this->db = MDB2::factory($dsn, $options);
  42
  43                 $this->db->setFetchMode(MDB2_FETCHMODE_OBJECT);
  44                 if (PEAR::isError($this->db)) {
  45                         IrrecoLog::critical($this->db->getMessage());
  46                 }
  47         }
  48
  49         function __destruct() {
  50                 if (is_object($this->db)) {
  51                         $this->db->disconnect();
  52                         $this->db = NULL;
  53                 }
  54         }
  55
  56         /**
  57          * Run Query on database. Die on error.
  58          */
  59         private function query($query)
  60         {
  61                 /* Make sure that we dont collect multiple result
  62                    sets in memory. */
  63                 if (is_object($this->previous_result_set)) {
  64                         $this->previous_result_set->free();
  65                         $this->previous_result_set = NULL;
  66                 }
  67
  68                 IrrecoLog::$database->log('Query: "' . $query . '"');
  69                 $result = $this->db->query($query);
  70                 if (PEAR::isError($result)) {
  71                         IrrecoLog::critical($result->getMessage());
  72                 } else {
  73                         $this->previous_result_set = $result;
  74                         return $result;
  75                 }
  76         }
  77
  78         /**
  79          * Prepare Query on database. Die on error.
  80          */
  81         private function prepare($query, $types, $prepare)
  82         {
  83                 /* Make sure that we dont prepare several queries. */
  84                 if (is_object($this->prepared_query)) {
  85                         $this->prepared_query->free();
  86                         $this->prepared_query = NULL;
  87                 }
  88
  89                 IrrecoLog::$database->log('Prepare: "' . $query . '"');
  90                 $result = $this->db->prepare($query/*, $types, $prepare*/);
  91                 if (PEAR::isError($result)) {
  92                         IrrecoLog::critical($result->getMessage());
  93                 } else {
  94                         $this->prepared_query = $result;
  95                         return $result;
  96                 }
  97         }
  98
  99         /**
 100          * Execute Query on database. Die on error.
 101          */
 102         private function execute($args)
 103         {
 104                 /* Make sure that we dont collect multiple result
 105                    sets in memory. */
 106                 if (is_object($this->previous_result_set)) {
 107                         $this->previous_result_set->free();
 108                         $this->previous_result_set = NULL;
 109                 }
 110
 111                 IrrecoLog::$database->log('Execute: "' . serialize($args) . '"');
 112                 $result = $this->prepared_query->execute($args);
 113                 if (PEAR::isError($result)) {
 114                         IrrecoLog::critical($result->getMessage());
 115                 } else {
 116                         $this->previous_result_set = $result;
 117                         return $result;
 118                 }
 119         }
 120
 121         private function lockTable($table)
 122         {
 123                 $this->query('LOCK TABLE ' .
 124                              $this->db->quoteIdentifier($table, 'text').
 125                              ' WRITE');
 126                 /*
 127                 $this->prepare('LOCK TABLE ? WRITE',
 128                                array('text'), MDB2_PREPARE_RESULT);
 129                 $this->execute($table);
 130                 */
 131         }
 132
 133         private function unlockTables()
 134         {
 135                 $this->query('UNLOCK TABLES');
 136         }
 137
 138         /**
 139          * Add user to database
 140          */
 141         function addUser($name, $email, $passwd, $table)
 142         {
 143                 IrrecoLog::$database->log('Add user: "' . $name .
 144                                 '" to table "' . $table . '".');
 145
 146                 /* Lock table */
 147                 $this->lockTable($table);
 148
 149                 /* Check for same name already at DB */
 150                 $data = $this->getNameId($table, $name);
 151                 if ($data != "") {
 152                         IrrecoLog::$database->log('Name already at database.');
 153                         return FALSE;
 154                 }
 155
 156                 /* insert data */
 157                 $this->query("INSERT INTO " .
 158                                 $this->db->quoteIdentifier($table, 'text').
 159                                 " VALUES (NULL, ".
 160                                 $this->db->quote($name, 'text'). ",
 161                                 ". $this->db->quote($email, 'text'). ",
 162                                 ". $this->db->quote($passwd, 'text').
 163                                 ", NOW(), NOW() )" );
 164
 165                 /* Unlock table */
 166                 $this->unlockTables();
 167
 168                 IrrecoLog::$database->log('Done.');
 169
 170                 return TRUE;
 171         }
 172
 173
 174         /**
 175          * Add User agent data to database
 176          */
 177         function addUserAgentData($user_agent)
 178         {
 179                 IrrecoLog::$database->log("Client:" . $_SERVER['HTTP_USER_AGENT']);
 180                 $result = $this->query('SELECT user_agent AS '.
 181                                         'user_agent FROM user_agent WHERE date = CURDATE() '.
 182                                         'AND user_agent = '. $this->db->quote($user_agent, 'text'));
 183                 $rows = $result->fetchAll();
 184
 185                 if($user_agent != $rows[0]->user_agent) {
 186                         $this->query('INSERT INTO user_agent (user_agent, date, count) VALUES('.
 187                                         $this->db->quote($user_agent, 'text').', CURDATE(), 1)');
 188                 }
 189                 else{
 190                         $this->query('UPDATE user_agent SET count = count+1 WHERE user_agent = '.
 191                                         $this->db->quote($user_agent, 'text').' AND date= CURDATE()');
 192                 }
 193
 194         }
 195
 196
 197         /**
 198          * Add configuration to database
 199          *
 200          * @param string backend Name for Backend. e.g. "IRTrans Transceiver"
 201          * @param string category Name for Category. e.g. "Tv"
 202          * @param string manufacturer Name for Manufacturer. e.g. "Sony"
 203          * @param string model Name for Model. e.g. "Sony"
 204          * @param string user Username
 205          * @param string password sha1-hash of password
 206          * @param string file_hash sha1-hash of file_data
 207          * @param string file_name Name for File
 208          * @param string file_data Content of file
 209          * @return string
 210          */
 211         function uploadConfiguration($backend,
 212                                      $category,
 213                                      $manufacturer,
 214                                      $model,
 215                                      $user,
 216                                      $password,
 217                                      $file_hash,
 218                                      $file_name,
 219                                      $file_data)
 220         {
 221                 /* Check if username matches up to password and get id */
 222                 $user_id = $this->checkUsernameAndPassword($user, $password);
 223                 if($user_id == NULL) {
 224                         return('username and password mismatch.');
 225                 }
 226
 227                 /* Check if file hash matches with sha1 sum of file data */
 228                 if(sha1($file_data) != $file_hash) {
 229                         return('file hash and file data mismatch.');
 230                 }
 231
 232                 /* get id for backend, category and manufacturer */
 233                 /* create new if needed */
 234                 /* ?delete new id's if adding data fails? */
 235                 $backend_id = $this->createNameId('backend', $backend);
 236                 $category_id = $this->createNameId('category', $category);
 237                 $manufacturer_id = $this->createNameId('manufacturer',
 238                                                       $manufacturer);
 239
 240                 /* insert data */
 241                 $this->lockTable('file');
 242
 243                 if ($this->fileExists($file_hash, $file_name)) {
 244                         $this->unlockTables();
 245                         return('file already exist.');
 246                 }
 247
 248                 $this->query('INSERT INTO file VALUES ( '.
 249                              $this->db->quote($file_hash, 'text'). ', '.
 250                              $this->db->quote($file_name, 'text'). ', '.
 251                              $this->db->quote($file_data, 'text'). ', '.
 252                              'NOW(), NOW(), 0)');
 253
 254                 /* Unlock table */
 255                 $this->unlockTables();
 256
 257                 /* Create configuration */
 258                 $this->lockTable('configuration');
 259                 $this->query("INSERT INTO configuration VALUES (NULL, ".
 260                              $this->db->quote($user_id, 'text'). ", ".
 261                              $this->db->quote($backend_id, 'text'). ", ".
 262                              $this->db->quote($category_id, 'text'). ", ".
 263                              $this->db->quote($manufacturer_id, 'text'). ", ".
 264                              $this->db->quote($model, 'text'). ", ".
 265                              $this->db->quote($file_hash, 'text'). ", ".
 266                              $this->db->quote($file_name, 'text'). ")");
 267
 268                 /* Unlock table */
 269                 $this->unlockTables();
 270
 271                 IrrecoLog::$database->log('configuration added successfully.');
 272
 273                 return('configuration added successfully');
 274         }
 275
 276         function dumpResultToLog($result)
 277         {
 278                 IrrecoLog::$database->log('Dumping result set.');
 279
 280                 $i = 1;
 281                 while ($row = $result->fetchRow()) {
 282                         IrrecoLog::$database->log(' Row ' . $i++ . ': ' .
 283                                                   serialize($row));
 284                 }
 285
 286                 IrrecoLog::$database->log('Done.');
 287         }
 288
 289         /**
 290          * Check username and password.
 291          */
 292         function checkUsernameAndPassword($name, $password)
 293         {
 294                 $result = $this->query(
 295                         'SELECT id AS id FROM user '.
 296                         'WHERE name LIKE '. $this->db->quote($name, 'text').' '.
 297                         'AND password = '. $this->db->quote($password, 'text'));
 298                 $row = $result->fetchRow();
 299
 300                 if (is_object($row)) {
 301                         $id = $row->id;
 302                         IrrecoLog::$database->log(
 303                                         'username matches up to password');
 304                 } else {
 305                         $id = NULL;
 306                         IrrecoLog::$database->log(
 307                                         'username and password mismatch');
 308                 }
 309                 return $id;
 310         }
 311         /**
 312          * Check if file already exist
 313          *
 314          *
 315          * @return TRUE if file exist
 316          */
 317         function fileExists($file_hash, $file_name)
 318         {
 319                 $result = $this->query(
 320                 'SELECT hash AS hash, name AS name FROM file '.
 321                 'WHERE hash = '. $this->db->quote($file_hash, 'text'). ' '.
 322                 'AND name = '. $this->db->quote($file_name, 'text'));
 323                 $row = $result->fetchRow();
 324
 325                 if (is_object($row)) {
 326                         IrrecoLog::$database->log('file exist');
 327                         return TRUE;
 328                 }
 329                 IrrecoLog::$database->log('file not exist');
 330                 return FALSE;
 331         }
 332
 333         /**
 334          * Get ID of name.
 335          */
 336         function getNameId($table, $name)
 337         {
 338                 /* Attempt to fetch category. */
 339                 $result = $this->query(
 340                         'SELECT id AS id, name AS name FROM '.
 341                         $this->db->quoteIdentifier($table, 'text') . ' ' .
 342                         'WHERE name LIKE ' .
 343                         $this->db->quote($name, 'text'));
 344                 $row = $result->fetchRow();
 345
 346                 if (is_object($row)) {
 347                         $id = $row->id;
 348                         IrrecoLog::$database->log(
 349                                 'Id for name "' . $name .'" in table "' .
 350                                 $table . '" is "' . $id . '".', PEAR_LOG_DEBUG);
 351                 } else {
 352                         $id = NULL;
 353                 }
 354                 return $id;
 355         }
 356
 357         /**
 358          * Create ID for name if it does not exist, or get old ID of name.
 359          */
 360         function createNameId($table, $name)
 361         {
 362                 IrrecoLog::$database->log('Get id for name "' . $name .
 363                                           '" in table "' . $table . '".');
 364
 365                 // Lock the table
 366                 $this->lockTable($table);
 367
 368                 // Create category if it does not exists.
 369                 $id = $this->getNameId($table, $name);
 370                 if ($id != NULL) {
 371                         $this->unlockTables();
 372                         return $id;
 373                 } else {
 374                         $this->query('INSERT INTO ' .
 375                                      $this->db->quoteIdentifier($table, 'text').
 376                                      ' (name) VALUES(' .
 377                                      $this->db->quote($name, 'text') . ')');
 378                         $id = $this->db->lastInsertID();
 379                 }
 380
 381                 $this->unlockTables();
 382                 IrrecoLog::$database->log('Category ID: "' . $id . '".');
 383                 return $id;
 384         }
 385
 386         /**
 387          * Get list of categories that have configurations in them.
 388          *
 389          * @return Two dimensional array with category rows.
 390          */
 391         function getCategoryList()
 392         {
 393                 $result = $this->query(
 394                         'SELECT category.id AS id, '.
 395                         'category.name AS name FROM category '.
 396                         'RIGHT JOIN configuration '.
 397                         'ON category.id = configuration.category '.
 398                         'INNER JOIN file ON '.
 399                         'configuration.file_hash = file.hash AND '.
 400                         'configuration.file_name = file.name '.
 401                         'GROUP BY category.name');
 402                 $rows = $result->fetchAll();
 403                 return $rows;
 404         }
 405
 406         /**
 407          * Get whole list of all categories. Even those that don't
 408          * have configurations in them.
 409          *
 410          * @return Two dimensional array with all category rows.
 411          */
 412         function getWholeCategoryList()
 413         {
 414                 $result = $this->query(
 415                         'SELECT category.id AS id, '.
 416                         'category.name AS name FROM category '.
 417                         'GROUP BY category.name');
 418                 $rows = $result->fetchAll();
 419                 return $rows;
 420         }
 421
 422         /**
 423          * Get list of manufacturers that have configurations in them.
 424          *
 425          * @return Two dimensional array with manufacturer rows for category.
 426          */
 427         function getManufacturerList($category)
 428         {
 429                 IrrecoLog::$database->log('Category name: "' . $category . '".');
 430                 $result = $this->query(
 431                         'SELECT manufacturer.id AS id, '.
 432                         'manufacturer.name AS name FROM manufacturer '.
 433                         'RIGHT JOIN configuration '.
 434                         'ON manufacturer.id = configuration.manufacturer '.
 435                         'WHERE manufacturer.id IN '.
 436                         '(SELECT manufacturer FROM configuration '.
 437                         'INNER JOIN file ON '.
 438                         'configuration.file_hash = file.hash AND '.
 439                         'configuration.file_name = file.name '.
 440                         'RIGHT JOIN category '.
 441                         'ON category.id = configuration.category '.
 442                         "WHERE category.name = ".
 443                         $this->db->quote($category, 'text').") ".
 444                         'GROUP BY manufacturer.name');
 445                 $rows = $result->fetchAll();
 446                 return $rows;
 447         }
 448
 449         /**
 450          * Get list of all manufacturers. Even those that don't have
 451          * configurations in them.
 452          *
 453          * @return Two dimensional array with manufacturer rows.
 454          */
 455         function getWholeManufacturerList()
 456         {
 457                 IrrecoLog::$database->log('Get all manufacturers: "' . '".');
 458                 $result = $this->query(
 459                         'SELECT manufacturer.id AS id, '.
 460                         'manufacturer.name AS name FROM manufacturer '.
 461                         'GROUP BY manufacturer.name');
 462                 $rows = $result->fetchAll();
 463                 return $rows;
 464         }
 465
 466         /**
 467          * Get list of Models by manufacturer in selected category.
 468          *
 469          * @return Two dimensional array
 470          */
 471         function getModelList($category,$manufacturer)
 472         {
 473                 IrrecoLog::$database->log('Category name: "' .
 474                                           $category . '".');
 475                 IrrecoLog::$database->log('Manufacturer name: "' .
 476                                           $manufacturer . '".');
 477                 $result = $this->query(
 478                         'SELECT configuration.id AS id, '.
 479                         'configuration.model AS model FROM configuration '.
 480                         'RIGHT JOIN file ON '.
 481                         'configuration.file_hash = file.hash AND '.
 482                         'configuration.file_name = file.name '.
 483                         "WHERE configuration.category = ".
 484                         "(SELECT id FROM category WHERE name = ".
 485                         $this->db->quote($category, 'text').") ".
 486                         "AND configuration.manufacturer = ".
 487                         "(SELECT id FROM manufacturer WHERE name = ".
 488                         $this->db->quote($manufacturer, 'text').") ".
 489                         'GROUP BY configuration.model');
 490                 $rows = $result->fetchAll();
 491                 return $rows;
 492         }
 493
 494         /**
 495          * Get list of Configurations for Model
 496          *
 497          * @return Two dimensional array
 498          */
 499         function getConfigs($model)
 500         {
 501                 IrrecoLog::$database->log('Model name: "'.$model.'"');
 502
 503                 $result = $this->query(
 504                         'SELECT configuration.id AS id, '.
 505                         'user.name AS user FROM configuration '.
 506                         'LEFT JOIN user ON user.id = configuration.user '.
 507                         'RIGHT JOIN file ON '.
 508                         'configuration.file_hash = file.hash AND '.
 509                         'configuration.file_name = file.name '.
 510                         "WHERE configuration.model = ".
 511                         $this->db->quote($model, 'text')." ".
 512                         'ORDER BY user.name, file.uploaded DESC');
 513
 514                 $rows = $result->fetchAll();
 515
 516                 return $rows;
 517         }
 518
 519         /**
 520          * Get list of Configurations for Model and manufacturer
 521          *
 522          * @return Two dimensional array
 523          */
 524         function getConfigurations($manufacturer, $model)
 525         {
 526                 IrrecoLog::$database->log('Model name: "'.$model.'"');
 527
 528                 $result = $this->query(
 529                         'SELECT configuration.id AS id, '.
 530                         'user.name AS user FROM configuration '.
 531                         'LEFT JOIN user ON user.id = configuration.user '.
 532                         'RIGHT JOIN file ON '.
 533                         'configuration.file_hash = file.hash AND '.
 534                         'configuration.file_name = file.name '.
 535                         "WHERE configuration.model = ".
 536                         $this->db->quote($model, 'text')." ".
 537                         "AND configuration.manufacturer = ".
 538                         "(SELECT id FROM manufacturer WHERE name = ".
 539                         $this->db->quote($manufacturer, 'text').") ".
 540                         'ORDER BY user.name, file.uploaded DESC');
 541
 542                 $rows = $result->fetchAll();
 543
 544                 return $rows;
 545         }
 546
 547         /**
 548          * Get all data from category
 549          *
 550          * @return Two dimensional array
 551          */
 552         function getConfiguration($id)
 553         {
 554                 IrrecoLog::$database->log('Category id: "'.$id.'"');
 555
 556                 $result = $this->query(
 557                         'SELECT configuration.id AS id, '.
 558                         'user.name AS user, '.
 559                         'backend.name AS backend, '.
 560                         'category.name AS category, '.
 561                         'manufacturer.name AS manufacturer, '.
 562                         'configuration.model AS model, '.
 563                         'configuration.file_hash AS file_hash, '.
 564                         'configuration.file_name AS file_name, '.
 565                         'file.uploaded AS uploaded, '.
 566                         'file.download_count AS download_count '.
 567                         'FROM configuration RIGHT JOIN user '.
 568                         'ON configuration.user = user.id '.
 569                         'RIGHT JOIN backend ON '.
 570                         'configuration.backend = backend.id '.
 571                         'RIGHT JOIN category ON '.
 572                         'configuration.category = category.id '.
 573                         'RIGHT JOIN manufacturer ON '.
 574                         'configuration.manufacturer = manufacturer.id '.
 575                         'RIGHT JOIN file ON '.
 576                         'configuration.file_hash = file.hash AND '.
 577                         'configuration.file_name = file.name '.
 578                         "WHERE configuration.id = ".
 579                         $this->db->quote($id, 'text'));
 580
 581                 $rows = $result->fetchAll();
 582
 583                 return $rows;
 584         }
 585
 586         /**
 587          * Get file data by hash and name
 588          *
 589          * @return array
 590          */
 591         function getFileData($hash,$name)
 592         {
 593                 IrrecoLog::$database->log('Hash: "' .
 594                                           $hash . '".');
 595                 IrrecoLog::$database->log('Name: "' .
 596                                           $name . '".');
 597                 $result = $this->query(
 598                         'SELECT file.data AS data '.
 599                         "FROM file WHERE file.hash = ".
 600                         $this->db->quote($hash, 'text')." ".
 601                         "AND file.name = ".
 602                         $this->db->quote($name, 'text'));
 603                 $rows = $result->fetchAll();
 604
 605                 //Update download_count. (timestamp will be updated
 606                 //automatically)
 607
 608                 $this->lockTable('file');
 609                 $this->query(
 610                         "UPDATE file SET download_count = download_count + 1 ".
 611                         "WHERE file.hash = ".
 612                         $this->db->quote($hash, 'text')." ".
 613                         "AND file.name = ".
 614                         $this->db->quote($name, 'text'));
 615                 $this->unlockTables();
 616
 617                 return $rows;
 618         }
 619
 620         /**
 621          * Create new Theme
 622          *
 623          *returns theme_id or 0
 624          * @return int
 625          */
 626         function createNewTheme($name, $comment, $preview_button,
 627                                 $folder, $user, $password)
 628         {
 629                 IrrecoLog::$database->log('Theme name: "'.$name.'" '.
 630                                           'Comment: "'.$comment.'" '.
 631                                           'Previewbutton: "'.$preview_button.'" '.
 632                                           'Folder: "'.$folder.'" '.
 633                                           'User: "'.$user.'" ');
 634
 635                 /* Check if username matches up to password and get id */
 636                 $user_id = $this->checkUsernameAndPassword($user, $password);
 637                 if($user_id == NULL) {
 638                         return('username and password mismatch.');
 639                 }
 640
 641                 $result = $this->query(
 642                         'INSERT INTO irreco.theme ( '.
 643                         'id, name, user_id, comment, preview_button, folder, '.
 644                         'uploaded, modified, downloaded, download_count) '.
 645                         'VALUES ( NULL, '.$this->db->quote($name, 'text').', '.
 646                         $this->db->quote($user_id, 'text').', '.
 647                         $this->db->quote($comment, 'text').', '.
 648                         $this->db->quote($preview_button, 'text').', '.
 649                         $this->db->quote($folder, 'text').', '.
 650                         'NOW(), NOW(), NOW(), 0)');
 651
 652                 $theme = $this->query(
 653                         'SELECT id AS id FROM irreco.theme '.
 654                         'WHERE uploaded = (SELECT MAX(uploaded) '.
 655                         'FROM irreco.theme)');
 656                 $row = $theme->fetchAll();
 657
 658                 return intval($row[0]->id);
 659         }
 660
 661         /**
 662          * Set Theme downloadable
 663          *
 664          * @param int id id-number for theme.
 665          * @param bool downloadable
 666          * @param string user Username
 667          * @param string password sha1-hash of password
 668          * @return int
 669          */
 670         function setThemeDownloadable($id, $downloadable, $user, $password)
 671         {
 672                 /* Check if username matches up to password and get id */
 673                 $user_id = $this->checkUsernameAndPassword($user, $password);
 674                 if($user_id == NULL) {
 675                         return('username and password mismatch.');
 676                 }
 677
 678                 $success = $this->query('UPDATE theme SET downloadable = '.
 679                                 $this->db->quote($downloadable, 'text').
 680                                 ' WHERE theme.id = '.
 681                                 $this->db->quote($id, 'text'));
 682                 return 1;
 683         }
 684
 685         /**
 686          * Get list of themes
 687          *
 688          * @return Two dimensional array.
 689          */
 690         function getThemeList()
 691         {
 692                 $result = $this->query(
 693                         'SELECT id AS id, name AS name FROM theme '.
 694                         'WHERE downloadable = 1 AND '.
 695                         'uploaded IN (SELECT MAX(uploaded) '.
 696                         'FROM theme WHERE downloadable = 1 '.
 697                         'GROUP BY name) ORDER BY name');
 698                 $rows = $result->fetchAll();
 699
 700                 if ($rows[0]->name == NULL) {
 701                         return('Database is empty.');
 702                 }
 703                 return $rows;
 704         }
 705
 706         /**
 707          * Get all data from theme
 708          *
 709          * Returns name, user, comment, preview_button, folder,
 710          * uploaded, modified, downloaded, download_count
 711          * @return Two dimensional array
 712          */
 713         function getTheme($id)
 714         {
 715                 IrrecoLog::$database->log('Theme id: "'.$id.'"');
 716
 717                 $result = $this->query(
 718                         'SELECT theme.id AS id, '.
 719                         'theme.name AS name, '.
 720                         'user.name AS user, '.
 721                         'theme.comment AS comment, '.
 722                         'theme.preview_button AS preview_button, '.
 723                         'theme.folder AS folder, '.
 724                         'theme.uploaded AS uploaded, '.
 725                         'theme.modified AS modified, '.
 726                         'theme.downloaded AS downloaded, '.
 727                         'theme.download_count AS download_count '.
 728                         'FROM theme RIGHT JOIN user '.
 729                         'ON theme.user_id = user.id '.
 730                         "WHERE theme.id = ".
 731                         $this->db->quote($id, 'text'));
 732
 733                 $rows = $result->fetchAll();
 734
 735                 return $rows;
 736         }
 737
 738         /**
 739          * Get Versions od Theme by name
 740          *
 741          * @return Two dimensional array.
 742          */
 743         function getThemeVersionsByName($name)
 744         {
 745                 $result = $this->query(
 746                         'SELECT id AS id, uploaded AS uploaded FROM theme '.
 747                         "WHERE name = ".$this->db->quote($name, 'text')." ".
 748                         'AND downloadable = 1 '.
 749                         'ORDER BY uploaded DESC');
 750                 $rows = $result->fetchAll();
 751                 return $rows;
 752         }
 753
 754         /**
 755          * Add button to Theme
 756          *
 757          *returns button_id or 0
 758          * @return int
 759          */
 760         function addButtonToTheme($name, $allow_text, $text_format_up,
 761                                 $text_format_down, $text_padding,
 762                                 $text_h_align, $text_v_align,
 763                                 $image_up_hash, $image_up_name, $image_up,
 764                                 $image_down_hash, $image_down_name, $image_down,
 765                                 $folder, $theme_id, $user, $password)
 766         {
 767                 IrrecoLog::$database->log('Button name: "'.$name.'" '.
 768                                 'allow_text: "'.$allow_text.'" '.
 769                                 'text_format_up: "'.$text_format_up.'" '.
 770                                 'text_format_down: "'.$text_format_down.'" '.
 771                                 'text_padding: "'.$text_padding.'" '.
 772                                 'text_h_align: "'.$text_h_align.'" '.
 773                                 'text_v_align: "'.$text_v_align.'" '.
 774                                 'image_up_hash: "'.$image_up_hash.'" '.
 775                                 'image_up_name: "'.$image_up_name.'" '.
 776                                 'image_down_hash: "'.$image_down_hash.'" '.
 777                                 'image_down_name: "'.$image_down_name.'" '.
 778                                 'folder: "'.$folder.'" '.
 779                                 'theme_id: "'.$theme_id.'" '.
 780                                 'User: "'.$user.'" ');
 781
 782                 /* Check if username matches up to password and get id */
 783                 $user_id = $this->checkUsernameAndPassword($user, $password);
 784                 if($user_id == NULL) {
 785                         return('username and password mismatch.');
 786                 }
 787
 788                 /* Check if file hash matches with sha1 sum of file data */
 789                 if(sha1($image_up) != $image_up_hash ||
 790                    sha1($image_down) != $image_down_hash) {
 791                         return('file hash and file data mismatch.');
 792                 }
 793
 794                 /* insert data */
 795                 $this->lockTable('file');
 796
 797                 if (!$this->fileExists($image_up_hash, $image_up_name)) {
 798                         $this->query('INSERT INTO file VALUES ( '.
 799                              $this->db->quote($image_up_hash, 'text'). ', '.
 800                              $this->db->quote($image_up_name, 'text'). ', '.
 801                              $this->db->quote($image_up, 'text'). ', '.
 802                              'NOW(), NOW(), 0)');
 803                 }
 804                 if (!$this->fileExists($image_down_hash, $image_down_name)) {
 805                         $this->query('INSERT INTO file VALUES ( '.
 806                              $this->db->quote($image_down_hash, 'text'). ', '.
 807                              $this->db->quote($image_down_name, 'text'). ', '.
 808                              $this->db->quote($image_down, 'text'). ', '.
 809                              'NOW(), NOW(), 0)');
 810                 }
 811                 $allow_text_string = 'FALSE';
 812                 if ($allow_text == TRUE) {
 813                         $allow_text_string = 'TRUE';
 814                 }
 815
 816                 /* Unlock table */
 817                 $this->unlockTables();
 818
 819                 $button = $this->query(
 820                         'SELECT id AS id FROM irreco.button '.
 821                         'WHERE theme_id = '.
 822                         $this->db->quote($theme_id, 'text').' AND '.
 823                         'name = '.$this->db->quote($name, 'text'));
 824
 825                 $row = $button->fetchAll();
 826
 827                 if ($row[0]->id == NULL) {
 828                         $result = $this->query(
 829                         'INSERT INTO irreco.button ( '.
 830                         'id, name, allow_text, text_format_up, '.
 831                         'text_format_down, text_padding, '.
 832                         'text_h_align, text_v_align, image_up_hash, '.
 833                         'image_up_name, image_down_hash, image_down_name, '.
 834                         'folder, theme_id) '.
 835                         'VALUES ( NULL, '.$this->db->quote($name, 'text').', '.
 836                         $allow_text_string.', '.
 837                         $this->db->quote($text_format_up, 'text').', '.
 838                         $this->db->quote($text_format_down, 'text').', '.
 839                         $this->db->quote($text_padding, 'text').', '.
 840                         $this->db->quote($text_h_align, 'text').', '.
 841                         $this->db->quote($text_v_align, 'text').', '.
 842                         $this->db->quote($image_up_hash, 'text').', '.
 843                         $this->db->quote($image_up_name, 'text').', '.
 844                         $this->db->quote($image_down_hash, 'text').', '.
 845                         $this->db->quote($image_down_name, 'text').', '.
 846                         $this->db->quote($folder, 'text').', '.
 847                         $this->db->quote($theme_id, 'text').')');
 848
 849                         IrrecoLog::$database->log('Button added');
 850
 851                         $button = $this->query(
 852                         'SELECT id AS id FROM irreco.button '.
 853                         'WHERE theme_id = '.
 854                         $this->db->quote($theme_id, 'text').' AND '.
 855                         'name = '.$this->db->quote($name, 'text'));
 856
 857                         $row = $button->fetchAll();
 858                 } else {
 859                         $result = $this->query(
 860                         'UPDATE irreco.button SET '.
 861                         'allow_text = '.$allow_text_string.', '.
 862                         'text_format_up = '.
 863                         $this->db->quote($text_format_up, 'text').', '.
 864                         'text_format_down = '.
 865                         $this->db->quote($text_format_down, 'text').', '.
 866                         'text_padding = '.
 867                         $this->db->quote($text_padding, 'text').', '.
 868                         'text_h_align = '.
 869                         $this->db->quote($text_h_align, 'text').', '.
 870                         'text_v_align = '.
 871                         $this->db->quote($text_v_align, 'text').', '.
 872                         'image_up_hash = '.
 873                         $this->db->quote($image_up_hash, 'text').', '.
 874                         'image_up_name = '.
 875                         $this->db->quote($image_up_name, 'text').', '.
 876                         'image_down_hash = '.
 877                         $this->db->quote($image_down_hash, 'text').', '.
 878                         'image_down_name = '.
 879                         $this->db->quote($image_down_name, 'text').', '.
 880                         'folder = '.$this->db->quote($folder, 'text').' '.
 881                         'WHERE theme_id = '.
 882                         $this->db->quote($theme_id, 'text').' AND '.
 883                         'name = '.$this->db->quote($name, 'text'));
 884
 885                         IrrecoLog::$database->log('Button updated');
 886                 }
 887
 888                 return intval($row[0]->id);
 889         }
 890
 891         /**
 892          * Get list of buttons for theme
 893          *
 894          * @return Two dimensional array.
 895          */
 896         function getButtonList($theme_id)
 897         {
 898                 $result = $this->query(
 899                         'SELECT id AS id, name AS name FROM button '.
 900                         'WHERE theme_id = '.
 901                         $this->db->quote($theme_id, 'text').' '.
 902                         'ORDER BY name');
 903                 $rows = $result->fetchAll();
 904                 return $rows;
 905         }
 906
 907         /**
 908          * Get all data from Button
 909          *
 910          * Returns name, allow_text, text_format_up, text_format_down,
 911          * text_padding, image_up_hash, image_up_name, base64 encoded image_up,
 912          * image_down_hash, image_down_name, base64 encoded image_down, folder,
 913          * theme_id
 914          * @param int id id-number for button.
 915          * @return struct
 916          */
 917         function getButton($id)
 918         {
 919                 IrrecoLog::$database->log('Button id: "'.$id.'"');
 920
 921                 $result = $this->query('SELECT name AS name, '.
 922                                        'allow_text AS allow_text, '.
 923                                        'text_format_up AS text_format_up, '.
 924                                        'text_format_down AS text_format_down, '.
 925                                        'text_padding AS text_padding, '.
 926                                        'text_h_align AS text_h_align, '.
 927                                        'text_v_align AS text_v_align, '.
 928                                        'image_up_hash AS image_up_hash, '.
 929                                        'image_up_name AS image_up_name, '.
 930                                        'image_down_hash AS image_down_hash, '.
 931                                        'image_down_name AS image_down_name, '.
 932                                        'folder AS folder, '.
 933                                        'theme_id AS theme_id '.
 934                                        'FROM button WHERE id = '.
 935                                         $this->db->quote($id, 'text'));
 936                 $rows = $result->fetchAll();
 937
 938                 $image_up = $this->getFileData($rows[0]->image_up_hash,
 939                                                $rows[0]->image_up_name);
 940
 941                 $image_down = $this->getFileData($rows[0]->image_down_hash,
 942                                                  $rows[0]->image_down_name);
 943
 944                 //Set the type of a variable for text-format
 945                 settype($rows[0]->text_format_up, "string");
 946                 settype($rows[0]->text_format_down, "string");
 947
 948                 settype ( $rows[0]->allow_text , "boolean");
 949                 $array = array();
 950                 $array['name'] = $rows[0]->name;
 951                 $array['allow_text'] = $rows[0]->allow_text;
 952                 $array['text_format_up'] = $rows[0]->text_format_up;
 953                 $array['text_format_down'] = $rows[0]->text_format_down;
 954                 $array['text_padding'] = intval($rows[0]->text_padding);
 955                 $array['text_h_align'] = doubleval($rows[0]->text_h_align);
 956                 $array['text_v_align'] = doubleval($rows[0]->text_v_align);
 957                 $array['image_up_hash'] = $rows[0]->image_up_hash;
 958                 $array['image_up_name'] = $rows[0]->image_up_name;
 959                 $array['image_up'] = base64_encode($image_up[0]->data);
 960                 $array['image_down_hash'] = $rows[0]->image_down_hash;
 961                 $array['image_down_name'] = $rows[0]->image_down_name;
 962                 $array['image_down'] = base64_encode($image_down[0]->data);
 963                 $array['folder'] = $rows[0]->folder;
 964
 965                 $theme_id = $rows[0]->theme_id;
 966
 967                 $last_button = $this->query('SELECT MAX(id) AS id '.
 968                                             'FROM button WHERE theme_id = '.
 969                                             $theme_id);
 970                 $button = $last_button->fetchAll();
 971
 972                 if($button[0]->id == $id)
 973                 {
 974                         $this->query('UPDATE theme SET download_count = '.
 975                                      'download_count + 1 WHERE id = '.
 976                                      $theme_id);
 977                 }
 978
 979                 return $array;
 980         }
 981
 982         /**
 983          * Get preview button
 984          *
 985          * Returns image-data
 986          * @param int id id-number for theme.
 987          * @return string
 988          */
 989         function getPreviewButton($theme_id)
 990         {
 991                 IrrecoLog::$database->log('Theme id: "'.$theme_id.'"');
 992
 993                 $result = $this->query(
 994                         'SELECT button.image_up_hash AS hash, '.
 995                         'button.image_up_name AS name FROM button '.
 996                         'WHERE button.name = (SELECT preview_button '.
 997                         'FROM theme WHERE id = '.
 998                         $this->db->quote($theme_id, 'text').') AND '.
 999                         'button.theme_id = '.
1000                         $this->db->quote($theme_id, 'text'));
1001
1002                 $rows = $result->fetchAll();
1003
1004                 $data = $this->getFileData($rows[0]->hash, $rows[0]->name);
1005
1006                 return $data[0]->data;
1007         }
1008         /**
1009          * Add background to Theme
1010          *
1011          *returns bg_id or 0
1012          * @return int
1013          */
1014         function addBgToTheme($name, $image_hash, $image_name, $image,
1015                               $folder, $theme_id, $user, $password)
1016         {
1017                 IrrecoLog::$database->log('Bg name: "'.$name.'" '.
1018                                           'image_hash: "'.$image_up_hash.'" '.
1019                                           'image_name: "'.$image_up_name.'" '.
1020                                           'folder: "'.$folder.'" '.
1021                                           'theme_id: "'.$theme_id.'" '.
1022                                           'User: "'.$user.'" ');
1023
1024                 /* Check if username matches up to password and get id */
1025                 $user_id = $this->checkUsernameAndPassword($user, $password);
1026                 if($user_id == NULL) {
1027                         return('username and password mismatch.');
1028                 }
1029
1030                 /* Check if file hash matches with sha1 sum of file data */
1031                 if(sha1($image) != $image_hash) {
1032                         return('file hash and file data mismatch.');
1033                 }
1034
1035                 /* insert data */
1036                 $this->lockTable('file');
1037
1038                 if (!$this->fileExists($image_hash, $image_name)) {
1039                         $this->query('INSERT INTO file VALUES ( '.
1040                              $this->db->quote($image_hash, 'text'). ', '.
1041                              $this->db->quote($image_name, 'text'). ', '.
1042                              $this->db->quote($image, 'text'). ', '.
1043                              'NOW(), NOW(), 0)');
1044                 }
1045
1046                 /* Unlock table */
1047                 $this->unlockTables();
1048
1049                 $bg = $this->query(
1050                         'SELECT id AS id FROM irreco.bg '.
1051                         'WHERE theme_id = '.
1052                         $this->db->quote($theme_id, 'text').' AND '.
1053                         'name = '.$this->db->quote($name, 'text'));
1054                 $row = $bg->fetchAll();
1055
1056                 if ($row[0]->id == NULL) {
1057                         $result = $this->query(
1058                         'INSERT INTO irreco.bg ( '.
1059                         'id, name, image_hash, image_name, folder, theme_id) '.
1060                         'VALUES ( NULL, '.$this->db->quote($name, 'text').', '.
1061                         $this->db->quote($image_hash, 'text').', '.
1062                         $this->db->quote($image_name, 'text').', '.
1063                         $this->db->quote($folder, 'text').', '.
1064                         $this->db->quote($theme_id, 'text').')');
1065
1066                         if ($result == 1) {
1067                                 IrrecoLog::$database->log('BG added');
1068                         }
1069
1070                         $bg = $this->query(
1071                         'SELECT id AS id FROM irreco.bg '.
1072                         'WHERE theme_id = '.
1073                         $this->db->quote($theme_id, 'text').' AND '.
1074                         'name = '.$this->db->quote($name, 'text'));
1075
1076                         $row = $bg->fetchAll();
1077
1078                 } else {
1079                         $result = $this->query(
1080                         'UPDATE irreco.bg SET '.
1081                         'image_hash = '.
1082                         $this->db->quote($image_hash, 'text').', '.
1083                         'image_name = '.
1084                         $this->db->quote($image_name, 'text').', '.
1085                         'folder = '.$this->db->quote($folder, 'text').' '.
1086                         'WHERE theme_id = '.
1087                         $this->db->quote($theme_id, 'text').' AND '.
1088                         'name = '.$this->db->quote($name, 'text'));
1089
1090                         if ($result == 1) {
1091                                 IrrecoLog::$database->log('BG updated');
1092                         }
1093                 }
1094
1095
1096
1097                 return intval($row[0]->id);
1098         }
1099
1100         /**
1101          * Get list of backgrounds for theme
1102          *
1103          * @return Two dimensional array.
1104          */
1105         function getBgList($theme_id)
1106         {
1107                 $result = $this->query(
1108                         'SELECT id AS id, name AS name FROM bg '.
1109                         'WHERE theme_id = '.
1110                         $this->db->quote($theme_id, 'text').' '.
1111                         'ORDER BY name');
1112                 $rows = $result->fetchAll();
1113                 return $rows;
1114         }
1115
1116         /**
1117          * Get all data from Background
1118          *
1119          * Returns name, image_hash, image_name,
1120          * folder and base64 encoded image_data
1121          * @param int id id-number for background.
1122          * @return struct
1123          */
1124         function getBg($id)
1125         {
1126                 IrrecoLog::$database->log('Bg id: "'.$theme_id.'"');
1127
1128                 $result = $this->query('SELECT name AS name, '.
1129                                        'image_hash AS hash, '.
1130                                        'image_name AS file_name, '.
1131                                        'folder AS folder '.
1132                                        'FROM bg WHERE id = '.
1133                                         $this->db->quote($id, 'text'));
1134                 $rows = $result->fetchAll();
1135
1136                 $data = $this->getFileData($rows[0]->hash, $rows[0]->file_name);
1137
1138                 $array = array();
1139                 $array['name'] = $rows[0]->name;
1140                 $array['image_hash'] = $rows[0]->hash;
1141                 $array['image_name'] = $rows[0]->file_name;
1142                 $array['image_data'] = base64_encode($data[0]->data);
1143                 $array['folder'] = $rows[0]->folder;
1144
1145                 return $array;
1146         }
1147
1148         /**
1149          * Login to database
1150          *
1151          * @param string user Username
1152          * @param string password sha1-hash of password
1153          * @return boolean
1154          */
1155         function loginToDB($user, $password)
1156         {
1157
1158                 /* Check if username matches up to password and get id */
1159                 $user_id = $this->checkUsernameAndPassword($user, $password);
1160                 if($user_id == NULL)
1161                 {
1162                         return FALSE;
1163                 }
1164
1165                 return TRUE;
1166         }
1167 }
1168
1169 ?>