Project

General

Profile

1 2 Manuela
<?php
2
/**
3
 *
4
 * @category        framework
5
 * @package         database
6
 * @copyright       WebsiteBaker Org. e.V.
7
 * @link            http://websitebaker.org/
8
 * @license         http://www.gnu.org/licenses/gpl.html
9
 * @platform        WebsiteBaker 2.8.3
10
 * @requirements    PHP 5.3.6 and higher
11
 * @version         $Id$
12
 * @filesource      $HeadURL$
13
 * @lastmodified    $Date$
14
 *
15
 */
16
/*
17
Database class
18
This class will be used to interface between the database
19
and the Website Baker code
20
*/
21
22
23
    define('DATABASE_CLASS_LOADED', true);
24
    // define the old mysql consts for Backward compatibility
25
    if (!defined('MYSQL_ASSOC')) {
26
        define('MYSQL_ASSOC',                 1);
27
        define('MYSQL_NUM',                   2);
28
        define('MYSQL_BOTH',                  3);
29
        define('MYSQL_CLIENT_COMPRESS',      32);
30
        define('MYSQL_CLIENT_IGNORE_SPACE', 256);
31
        define('MYSQL_CLIENT_INTERACTIVE', 1024);
32
        define('MYSQL_CLIENT_SSL',         2048);
33
    }
34
35
class database {
36
37
    private $db_handle  = null; // readonly from outside
38
    private $db_name    = '';
39
    private $connected  = false;
40
    private $sCharset   = '';
41
    private $error      = '';
42
    private $error_no   = array();
43
    private $error_type = '';
44
    private $message    = array();
45
    private $sActionFile  = '';
46
47
48
    // Set DB_URL
49
    function __construct($url = '') {
50
        // Connect to database
51
        if (!$this->connect()) {
52
            throw new DatabaseException($this->get_error());
53
        }
54
    }
55
56
    // Connect to the database   DB_CHARSET
57
    function connect() {
58
59
        $this->sCharset = strtolower(preg_replace('/[^a-z0-9]/i', '', (defined('DB_CHARSET') ? DB_CHARSET : '')));
60
61
        if (defined('DB_PORT')) {
62
            $port = DB_PORT;
63
        } else {
64
            $port = ini_get('mysqli.default_port');
65
        }
66
        if (!($this->db_handle = mysqli_connect(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME, $port))) {
67
            $this->connected = false;
68
            $this->error = mysqli_connect_error();
69
        } else {
70
            if ($this->sCharset) {
71
                @mysqli_query($this->db_handle, 'SET NAMES '.$this->sCharset);
72
                mysqli_set_charset($this->db_handle, $this->sCharset);
73
            }
74
            $this->db_name = DB_NAME;
75
            $this->connected = true;
76
        }
77
        return $this->connected;
78
    }
79
80
    // Disconnect from the database
81
    function disconnect() {
82
        if($this->connected==true) {
83
            mysqli_close();
84
            return true;
85
        } else {
86
            return false;
87
        }
88
    }
89
90
    // Run a query
91
    function query($statement) {
92
        $mysql = new mysql($this->db_handle);
93
        $mysql->query($statement);
94
        $this->set_error($mysql->error());
95
        if($mysql->error()) {
96
            return null;
97
        } else {
98
            return $mysql;
99
        }
100
    }
101
102
    // Gets the first column of the first row
103
    function get_one( $statement )
104
    {
105
        $fetch_row = mysqli_fetch_array(mysqli_query($this->db_handle, $statement) );
106
        $result = $fetch_row[0];
107
        $this->set_error(null);
108
        if(mysqli_error($this->db_handle)) {
109
            $this->set_error(mysqli_error($this->db_handle));
110
            return null;
111
        } else {
112
            return $result;
113
        }
114
    }
115
116
    // Set the DB error
117
    function set_error($message = null) {
118
        $this->error = $message;
119
        $this->error_type = 'unknown';
120
        if ($message!=''){
121
        }
122
    }
123
124
    // Return true if there was an error
125
    function is_error() {
126
        return (!empty($this->error)) ? true : false;
127
    }
128
129
    // Return the error
130
    function get_error() {
131
        return $this->error;
132
    }
133
    // Return the errno
134
    function get_errno() {
135
        return $this->is_error() ? mysqli_errno($this->db_handle) : 0;
136
    }
137
/**
138
 * default Getter for some properties
139
 * @param string $sPropertyName
140
 * @return mixed NULL on error or missing property
141
 */
142
    public function __get($sPropertyName)
143
    {
144
        switch ($sPropertyName):
145
            case 'db_handle':
146
            case 'DbHandle':
147
                $retval = $this->db_handle;
148
                break;
149
            case 'db_name':
150
            case 'DbName':
151
                $retval = $this->db_name;
152
                break;
153
            default:
154
                $retval = null;
155
                break;
156
        endswitch;
157
        return $retval;
158
    } // __get()
159
/**
160
 * Escapes special characters in a string for use in an SQL statement
161
 * @param string $unescaped_string
162
 * @return string
163
 */
164
    public function escapeString($unescaped_string)
165
    {
166
        return mysqli_real_escape_string($this->db_handle, $unescaped_string);
167
    }
168
/**
169
 * Last inserted Id
170
 * @return bool|int false on error, 0 if no record inserted
171
 */
172
    public function getLastInsertId()
173
    {
174
        return mysqli_insert_id($this->db_handle);
175
    }
176
177
/*
178
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
179
 * @param string $field_name: name of the field to seek for
180
 * @return bool: true if field exists
181
 */
182
    public function field_exists($table_name, $field_name)
183
    {
184
        $bRetval = false;
185
        $aMatches = array();
186
        $sql = 'DESCRIBE `'.$table_name.'` `'.$field_name.'` ';
187
        if (($oQuery = $this->query($sql))) {
188
            while (($aRecord = $oQuery->fetchRow(MYSQLI_ASSOC))) {
189
                $aMatches[] = $aRecord['Field'];
190
            }
191
            $bRetval = in_array($field_name, $aMatches);
192
        }
193
        return $bRetval;
194
    }
195
196
/*
197
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
198
 * @param string $index_name: name of the index to seek for
199
 * @return bool: true if field exists
200
 */
201
    public function index_exists($table_name, $index_name, $number_fields = 0)
202
    {
203
        $number_fields = intval($number_fields);
204
        $keys = 0;
205
        $sql = 'SHOW INDEX FROM `'.$table_name.'`';
206
        if (($res_keys = $this->query($sql))) {
207
            while (($rec_key = $res_keys->fetchRow(MYSQLI_ASSOC))) {
208
                if ($rec_key['Key_name'] == $index_name ) {
209
                    $keys++;
210
                }
211
            }
212
        }
213
        if ( $number_fields == 0 ) {
214
            return ($keys != $number_fields);
215
        } else {
216
            return ($keys == $number_fields);
217
        }
218
    }
219
/*
220
    public function index_exists1($sTableName, $sIndexName, $number_fields = 0){
221
      $sql  = 'SHOW INDEX FROM `'.$sTableName.'` WHERE `Column_name`= \''.$sIndexName.'\'';
222
    }
223
*/
224
/*
225
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
226
 * @param string $field_name: name of the field to add
227
 * @param string $description: describes the new field like ( INT NOT NULL DEFAULT '0')
228
 * @return bool: true if successful, otherwise false and error will be set
229
 */
230
    public function field_add($table_name, $field_name, $description)
231
    {
232
        if( !$this->field_exists($table_name, $field_name) )
233
        { // add new field into a table
234
            $sql = 'ALTER TABLE `'.$table_name.'` ADD '.$field_name.' '.$description.' ';
235
            $query = $this->query($sql);
236
            $this->set_error(mysqli_error($this->db_handle));
237
            if( !$this->is_error() )
238
            {
239
                return ( $this->field_exists($table_name, $field_name) ) ? true : false;
240
            }
241
        }else
242
        {
243
            $this->set_error('field \''.$field_name.'\' already exists');
244
        }
245
        return false;
246
    }
247
248
/*
249
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
250
 * @param string $field_name: name of the field to add
251
 * @param string $description: describes the new field like ( INT NOT NULL DEFAULT '0')
252
 * @return bool: true if successful, otherwise false and error will be set
253
 */
254
    public function field_modify($table_name, $field_name, $description)
255
    {
256
        $retval = false;
257
        if( $this->field_exists($table_name, $field_name) )
258
        { // modify a existing field in a table
259
            $sql  = 'ALTER TABLE `'.$table_name.'` MODIFY `'.$field_name.'` '.$description;
260
            $retval = ( $this->query($sql) ? true : false);
261
            $this->set_error(mysqli_error($this->db_handle));
262
        }
263
        return $retval;
264
    }
265
266
/*
267
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
268
 * @param string $field_name: name of the field to remove
269
 * @return bool: true if successful, otherwise false and error will be set
270
 */
271
    public function field_remove($table_name, $field_name)
272
    {
273
        $retval = false;
274
        if( $this->field_exists($table_name, $field_name) )
275
        { // modify a existing field in a table
276
            $sql  = 'ALTER TABLE `'.$table_name.'` DROP `'.$field_name.'`';
277
            $retval = ( $this->query($sql) ? true : false );
278
        }
279
        return $retval;
280
    }
281
282
/*
283
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
284
 * @param string $index_name: name of the new index (empty string for PRIMARY)
285
 * @param string $field_list: comma seperated list of fields for this index
286
 * @param string $index_type: kind of index (PRIMARY, UNIQUE, KEY, FULLTEXT)
287
 * @return bool: true if successful, otherwise false and error will be set
288
 */
289
    public function index_add($table_name, $index_name, $field_list, $index_type = 'KEY')
290
    {
291
       $retval = false;
292
       $field_list = explode(',', (str_replace(' ', '', $field_list)));
293
       $number_fields = sizeof($field_list);
294
       $field_list = '`'.implode('`,`', $field_list).'`';
295
       $index_name = (($index_type == 'PRIMARY') ? $index_type : $index_name);
296
       if ( $this->index_exists($table_name, $index_name, $number_fields) ||
297
            $this->index_exists($table_name, $index_name))
298
       {
299
           $sql  = 'ALTER TABLE `'.$table_name.'` ';
300
           $sql .= 'DROP INDEX `'.$index_name.'`';
301
           if (!$this->query($sql)) { return false; }
302
       }
303
       $sql  = 'ALTER TABLE `'.$table_name.'` ';
304
       $sql .= 'ADD '.$index_type.' ';
305
       $sql .= (($index_type == 'PRIMARY') ? 'KEY ' : '`'.$index_name.'` ');
306
       $sql .= '( '.$field_list.' ); ';
307
       if ($this->query($sql)) { $retval = true; }
308
       return $retval;
309
    }
310
311
/*
312
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
313
 * @param string $field_name: name of the field to remove
314
 * @return bool: true if successful, otherwise false and error will be set
315
 */
316
    public function index_remove($table_name, $index_name)
317
    {
318
        $retval = false;
319
        if ($this->index_exists($table_name, $index_name)) {
320
        // modify a existing field in a table
321
            $sql  = 'ALTER TABLE `'.$table_name.'` DROP INDEX `'.$index_name.'`';
322
            $retval = ( $this->query($sql) ? true : false );
323
        }
324
        return $retval;
325
    }
326
327
    public function setSqlImportActionFile ( $sCallingScript ){
328
       $this->sActionFile = $sCallingScript;
329
        trigger_error('Deprecated function call: '.__CLASS__.'::'.__METHOD__, E_USER_DEPRECATED);
330
    }
331
332
/**
333
 * Import a standard *.sql dump file
334
 * @param string $sSqlDump link to the sql-dumpfile
335
 * @param string $sTablePrefix
336
 * @param mixed $mAction
337
 *        (bool)true => upgrade (default)
338
 *        (bool)false => install
339
 *        or command (install|uninstall|upgrade) as string
340
 *        or calling script as string
341
 * @param string $sTblEngine
342
 * @param string $sTblCollation
343
 * @return boolean true if import successful
344
 */
345
    public function SqlImport(
346
        $sSqlDump,
347
        $sTablePrefix  = '',
348
        $mAction       = true,
349
        $sTblEngine    = 'MyISAM',
350
        $sTblCollation = 'utf8_unicode_ci'
351
    ) {
352
        $iCount = 0;
353
        $sSqlBuffer  = '';
354
        $bRetval     = true;
355
        $this->error = '';
356
        // detect requested action
357
        if (is_string($mAction)) {
358
            // search for valid command string in $mAction
359
            $sAction = strtolower(preg_replace(
360
                '/^.*?(uninstall|install|upgrade)(\.[^\.]+)?$/is',
361
                '$1',
362
                $mAction,
363
                -1,
364
                $iCount
365
            ));
366
            $sAction = $iCount ? $sAction : 'upgrade';
367
        } else if (is_bool($mAction)) {
368
            // on boolean request select true='upgrade' or false='install'
369
            $sAction = $mAction ? 'upgrade' : 'install';
370
        } else {
371
            // select 'upgrade' if no valid command found
372
            $sAction = 'upgrade';
373
        }
374
        // extract charset from given collation
375
        $aTmp = preg_split('/_/', $sTblCollation, null, PREG_SPLIT_NO_EMPTY);
376
        $sCharset = $aTmp[0];
377
        // define placeholders
378
        $aSearch[] = '/\{TABLE_PREFIX\}/';                                        /* step 0 */
379
        $aSearch[] = '/\{FIELD_CHARSET\}/';                                       /* step 1 */
380
        $aSearch[] = '/\{FIELD_COLLATION\}/';                                     /* step 2 */
381
        $aSearch[] = '/\{TABLE_ENGINE\}/';                                        /* step 3 */
382
        $aSearch[] = '/\{TABLE_ENGINE=([a-zA-Z_0-9]*)\}/';                        /* step 4 */
383
        $aSearch[] = '/\{CHARSET\}/';                                             /* step 5 */
384
        $aSearch[] = '/\{COLLATION\}/';                                           /* step 6 */
385
        // define replacements
386
        $aReplace[] = $sTablePrefix;                                              /* step 0 */
387
        $aReplace[] = ' CHARACTER SET {CHARSET}';                                 /* step 1 */
388
        $aReplace[] = ' COLLATE {COLLATION}';                                     /* step 2 */
389
        $aReplace[] = ' {TABLE_ENGINE='.$sTblEngine.'}';                          /* step 3 */
390
        $aReplace[] = ' ENGINE=$1 DEFAULT CHARSET={CHARSET} COLLATE={COLLATION}'; /* step 4 */
391
        $aReplace[] = $sCharset;                                                  /* step 5 */
392
        $aReplace[] = $sTblCollation;                                             /* step 6 */
393
        // read file into an array
394
        if (($aSql = file( $sSqlDump, FILE_SKIP_EMPTY_LINES ))) {
395
            if (sizeof($aSql) > 0) {
396
                // remove possible BOM from file
397
                $aSql[0] = preg_replace('/^[\xAA-\xFF]{3}/', '', $aSql[0]);
398
                // replace placeholders by replacements over the whole file
399
                $aSql = preg_replace($aSearch, $aReplace, $aSql);
400
            } else { $aSql = false; }
401
        }
402
403
        while ((bool)$aSql) {
404
            $sSqlLine = trim(array_shift($aSql));
405
            if (!preg_match('/^[\-\/]+.*/', $sSqlLine)) {
406
                $sSqlBuffer .= ' '.$sSqlLine;
407
                if ((substr($sSqlBuffer,-1,1) == ';')) {
408
                    if (
409
                        // drop tables on install or uninstall
410
                        preg_match('/^\s*DROP TABLE IF EXISTS/siU', $sSqlBuffer) &&
411
                        ($sAction == 'install' || $sAction == 'uninstall')
412
                    ) {
413
                        if (!$this->query($sSqlBuffer)) {
414
                            $aSql = $bRetval = false;
415
                            break;
416
                        }
417
                   } else if (
418
                        // create and alter tables on install or upgrade
419
                        (preg_match('/^\s*CREATE TABLE/siU', $sSqlBuffer) ||
420
                         preg_match('/^\s*ALTER TABLE/siU', $sSqlBuffer)) &&
421
                        ($sAction == 'install' || $sAction == 'upgrade')
422
                    ) {
423
                        if (!$this->query($sSqlBuffer))
424
                        {
425
                            switch ($this->get_errno()):
426
                                case 0: // no error
427
                                case 1060:
428
                                case 1061:
429
                                    break;
430
                                default: // all other errors
431
                                    $aSql = $bRetval = false;
432
                                    break;
433
                            endswitch;
434
                        }
435
                    } else if (
436
                        // insert default data on install
437
                        (preg_match('/^\s*INSERT INTO /siU', $sSqlBuffer)) &&
438
                        ( $sAction == 'install' )
439
                    ) {
440
                        if (!$this->query($sSqlBuffer)) {
441
                            $aSql = $bRetval = false;
442
                            break;
443
                        }
444
                    }
445
                    // clear buffer for next statement
446
                    $sSqlBuffer = '';
447
                }
448
            }
449
        }
450
        return $bRetval;
451
    }
452
453
/**
454
 * retuns the type of the engine used for requested table
455
 * @param string $table name of the table, including prefix
456
 * @return boolean/string false on error, or name of the engine (myIsam/InnoDb)
457
 */
458
    public function getTableEngine($table)
459
    {
460
        $retVal = false;
461
        $mysqlVersion = mysqli_get_server_info($this->db_handle);
462
        $engineValue = (version_compare($mysqlVersion, '5.0') < 0) ? 'Type' : 'Engine';
463
        $sql = "SHOW TABLE STATUS FROM " . $this->db_name . " LIKE '" . $table . "'";
464
        if(($result = $this->query($sql))) {
465
            if(($row = $result->fetchRow(MYSQLI_ASSOC))) {
466
                $retVal = $row[$engineValue];
467
            }
468
        }
469
        return $retVal;
470
    }
471
472
473
} /// end of class database
474
475
define('MYSQL_SEEK_FIRST', 0);
476
define('MYSQL_SEEK_LAST', -1);
477
define('MYSQLI_SEEK_FIRST', 0);
478
define('MYSQLI_SEEK_LAST', -1);
479
480
class mysql {
481
482
    private $db_handle = null;
483
    private $result = null;
484
    private $error = '';
485
486
    public function __construct($handle) {
487
        $this->db_handle = $handle;
488
    }
489
/**
490
 * query sql statement
491
 * @param  string $statement
492
 * @return object
493
 * @throws WbDatabaseException
494
 */
495
    public function query($sStatement)
496
    {
497
        $this->result = @mysqli_query($this->db_handle, $sStatement);
498
        if (defined('DEBUG')&& DEBUG && ($this->result === false)) {
499
            if (DEBUG) {
500
                throw new DatabaseException(mysqli_error($this->db_handle));
501
            } else {
502
                throw new DatabaseException('Error in SQL-Statement');
503
            }
504
        }
505
        $this->error = mysqli_error($this->db_handle);
506
        return $this->result;
507
    }
508
509
    // Fetch num rows
510
    public function numRows() {
511
        return mysqli_num_rows($this->result);
512
    }
513
514
    // Fetch row  $typ = MYSQLI_ASSOC, MYSQLI_NUM, MYSQLI_BOTH
515
    public function fetchRow($typ = MYSQLI_BOTH) {
516
        return mysqli_fetch_array($this->result, $typ);
517
    }
518
/**
519
 * fetchAssoc
520
 * @return array with assotiative indexes
521
 * @description get current record and increment pointer
522
 */
523
    public function fetchAssoc()
524
    {
525
        return mysqli_fetch_assoc($this->result);
526
    }
527
/**
528
 * fetchArray
529
 * @param  int $iType MYSQL_ASSOC(default) | MYSQL_BOTH | MYSQL_NUM
530
 * @return array of current record
531
 * @description get current record and increment pointer
532
 */
533
    public function fetchArray($iType = MYSQLI_ASSOC)
534
    {
535
        if ($iType < MYSQLI_ASSOC || $iType > MYSQLI_BOTH) {
536
            $iType = MYSQLI_ASSOC;
537
        }
538
        return mysqli_fetch_array($this->result, $iType);
539
    }
540
/**
541
 * fetchObject
542
 * @param  string $sClassname Name of the class to use. Is no given use stdClass
543
 * @param  string $aParams    optional array of arguments for the constructor
544
 * @return object
545
 * @description get current record as an object and increment pointer
546
 */
547
    public function fetchObject($sClassName = 'stdClass', array $aParams = [])
548
    {
549
        if ($sClassName === 'stdClass' || !$sClassName) {
550
            $oRetval = mysqli_fetch_object($this->result, 'stdClass');
551
        } elseif (class_exists($sClassName)) {
552
            $oRetval = mysqli_fetch_object($this->result, $sClassName, $aParams);
553
        } else {
554
            throw new DatabaseException('Class <'.$sClassName.'> not available on request of mysqli_fetch_object()');
555
        }
556
        return $oRetval;
557
    }
558
/**
559
 * fetchAll
560
 * @param  int $iType MYSQL_ASSOC(default) | MYSQL_NUM
561
 * @return array of rows
562
 * @description get all records of the result set
563
 */
564
    public function fetchAll($iType = MYSQLI_ASSOC)
565
    {
566
        $iType = $iType != MYSQLI_NUM ? MYSQLI_ASSOC : MYSQLI_NUM;
567
568
        if (function_exists('mysqli_fetch_all')) { # Compatibility layer with PHP < 5.3
569
            $aRetval = mysqli_fetch_all($this->result, $iType);
570
        } else {
571
            for ($aRetval = array(); ($aTmp = mysqli_fetch_array($this->result, $iType));) { $aRetval[] = $aTmp; }
572
        }
573
        return $aRetval;
574
    }
575
576
    public function rewind()
577
    {
578
        return $this->seekRow();
579
    }
580
581
    public function seekRow( $position = MYSQLI_SEEK_FIRST )
582
    {
583
        $pmax = $this->numRows() - 1;
584
        $offset = (($position < 0 || $position > $pmax) ? $pmax : $position);
585
        return mysqli_data_seek($this->result, $offset);
586
    }
587
588
    // Get error
589
    public function error() {
590
        if(isset($this->error)) {
591
            return $this->error;
592
        } else {
593
            return null;
594
        }
595
    }
596
597
} // end of class mysql
598
599
class DatabaseException extends AppException {}
600
601
/* this function is placed inside this file temporarely until a better place is found */
602
/*  function to update a var/value-pair(s) in table ****************************
603
 *  nonexisting keys are inserted
604
 *  @param string $table: name of table to use (without prefix)
605
 *  @param mixed $key:    a array of key->value pairs to update
606
 *                        or a string with name of the key to update
607
 *  @param string $value: a sting with needed value, if $key is a string too
608
 *  @return bool:  true if any keys are updated, otherwise false
609
 */
610
    function db_update_key_value($table, $key, $value = '')
611
    {
612
        global $database;
613
        if( !is_array($key))
614
        {
615
            if( trim($key) != '' )
616
            {
617
                $key = array( trim($key) => trim($value) );
618
            } else {
619
                $key = array();
620
            }
621
        }
622
        $retval = true;
623
        foreach( $key as $index=>$val)
624
        {
625
            $index = strtolower($index);
626
            $sql = 'SELECT COUNT(*) FROM `'.TABLE_PREFIX.$table.'` WHERE `name` = \''.$index.'\' ';
627
            if (intval($database->get_one($sql))>0)
628
            {
629
                $sql = 'UPDATE ';
630
                $sql_where = 'WHERE `name` = \''.$index.'\'';
631
            } else {
632
                $sql = 'INSERT INTO ';
633
                $sql_where = '';
634
            }
635
            $sql .= '`'.TABLE_PREFIX.$table.'` ';
636
            $sql .= 'SET `name` = \''.$index.'\', ';
637
            $sql .= '`value` = \''.$val.'\' '.$sql_where;
638
            if (!$database->query($sql) )
639
            {
640
                $retval = false;
641
            }
642
        }
643
        return $retval;
644
    }