Subversion Repositories Applications.papyrus

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
1173 jp_milcent 1
<?php
2
/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */
3
 
4
/**
5
 * Contains the DB_QueryTool_Query class
6
 *
7
 * PHP versions 4 and 5
8
 *
9
 * LICENSE: This source file is subject to version 3.0 of the PHP license
10
 * that is available through the world-wide-web at the following URI:
11
 * http://www.php.net/license/3_0.txt.  If you did not receive a copy of
12
 * the PHP License and are unable to obtain it through the web, please
13
 * send a note to license@php.net so we can mail you a copy immediately.
14
 *
15
 * @category   Database
16
 * @package    DB_QueryTool
17
 * @author     Wolfram Kriesing <wk@visionp.de>
18
 * @author     Paolo Panto <wk@visionp.de>
19
 * @author     Lorenzo Alberton <l dot alberton at quipo dot it>
20
 * @copyright  2003-2005 Wolfram Kriesing, Paolo Panto, Lorenzo Alberton
21
 * @license    http://www.php.net/license/3_0.txt  PHP License 3.0
22
 * @version    CVS: $Id: Query.php,v 1.1 2006-12-14 15:04:29 jp_milcent Exp $
23
 * @link       http://pear.php.net/package/DB_QueryTool
24
 */
25
 
26
/**
27
 * require the PEAR and DB classes
28
 */
29
require_once 'PEAR.php';
30
require_once 'DB.php';
31
 
32
/**
33
 * DB_QueryTool_Query class
34
 *
35
 * This class should be extended
36
 *
37
 * @category   Database
38
 * @package    DB_QueryTool
39
 * @author     Wolfram Kriesing <wk@visionp.de>
40
 * @author     Paolo Panto <wk@visionp.de>
41
 * @author     Lorenzo Alberton <l dot alberton at quipo dot it>
42
 * @copyright  2003-2005 Wolfram Kriesing, Paolo Panto, Lorenzo Alberton
43
 * @license    http://www.php.net/license/3_0.txt  PHP License 3.0
44
 * @link       http://pear.php.net/package/DB_QueryTool
45
 */
46
class DB_QueryTool_Query
47
{
48
    // {{{ class vars
49
 
50
    /**
51
     * @var string  the name of the primary column
52
     */
53
    var $primaryCol = 'id';
54
 
55
    /**
56
     * @var string  the current table the class works on
57
     */
58
    var $table      = '';
59
 
60
    /**
61
     * @var string  the name of the sequence for this table
62
     */
63
    var $sequenceName = null;
64
 
65
    /**
66
     * @var object  the db-object, a PEAR::DB instance
67
     */
68
    var $db = null;
69
 
70
    /**
71
     * @var string  the where condition
72
     * @access private
73
     */
74
    var $_where = '';
75
 
76
    /**
77
     * @var string  the order condition
78
     * @access private
79
     */
80
    var $_order = '';
81
 
82
    /**
83
     * @var    string  the having definition
84
     * @access private
85
     */
86
    var $_having = '';
87
 
88
    /**
89
     * @var array   contains the join content
90
     *              the key is the join type, for now we have 'default' and 'left'
91
     *              inside each key 'table' contains the table
92
     *                          key 'where' contains the where clause for the join
93
     * @access private
94
     */
95
    var $_join = array();
96
 
97
    /**
98
     * @var    string  which column to index the result by
99
     * @access private
100
     */
101
    var $_index = null;
102
 
103
    /**
104
     * @var    string  the group-by clause
105
     * @access private
106
     */
107
    var $_group = '';
108
 
109
    /**
110
     * @var    array   the limit
111
     * @access private
112
     */
113
    var $_limit = array();
114
 
115
    /**
116
     * @var    string  type of result to return
117
     * @access private
118
     */
119
    var $_resultType = 'none';
120
 
121
    /**
122
     * @var    array   the metadata temporary saved
123
     * @access private
124
     */
125
    var $_metadata = array();
126
 
127
    /**
128
     * @var    string
129
     * @access private
130
     */
131
    var $_lastQuery = null;
132
 
133
    /**
134
     * @var    string   the rows that shall be selected
135
     * @access private
136
     */
137
    var $_select = '*';
138
 
139
    /**
140
     * @var    string   the rows that shall not be selected
141
     * @access private
142
     */
143
    var $_dontSelect = '';
144
 
145
    /**
146
     * @var array  this array saves different modes in which this class works
147
     *             i.e. 'raw' means no quoting before saving/updating data
148
     * @access private
149
     */
150
    var $options = array(
151
        'raw'      =>  false,
152
        'verbose'  =>  true,    // set this to false in a productive environment
153
                                // it will produce error-logs if set to true
154
        'useCache' =>  false,
155
        'logFile'  =>  false,
156
    );
157
 
158
    /**
159
     * this array contains information about the tables
160
     * those are
161
     * - 'name' => the real table name
162
     * - 'shortName' => the short name used, so that when moving the table i.e.
163
     *                  onto a provider's db and u have to rename the tables to
164
     *                  longer names this name will be relevant, i.e. when
165
     *                  autoJoining, i.e. a table name on your local machine is:
166
     *                  'user' but online it has to be 'applName_user' then the
167
     *                  shortName will be used to determine if a column refers to
168
     *                  another table, if the colName is 'user_id', it knows the
169
     *                  shortName 'user' refers to the table 'applName_user'
170
     */
171
    var $tableSpec = array();
172
 
173
    /**
174
     * this is the regular expression that shall be used to find a table's shortName
175
     * in a column name, the string found by using this regular expression will be removed
176
     * from the column name and it will be checked if it is a table name
177
     * i.e. the default '/_id$/' would find the table name 'user' from the column name 'user_id'
178
     */
179
    var $_tableNameToShortNamePreg = '/^.*_/';
180
 
181
    /**
182
     * @var array this array caches queries that have already been built once
183
     *            to reduce the execution time
184
     */
185
    var $_queryCache = array();
186
 
187
    /**
188
     * The object that contains the log-instance
189
     */
190
    var $_logObject = null;
191
 
192
    /**
193
     * Some internal data the logging needs
194
     */
195
    var $_logData = array();
196
 
197
    // }}}
198
    // {{{ __construct()
199
 
200
    /**
201
     * this is the constructor, as it will be implemented in ZE2 (php5)
202
     *
203
     * @version    2002/04/02
204
     * @access     public
205
     * @author     Wolfram Kriesing <wk@visionp.de>
206
     * @param      object  db-object
207
     */
208
/*
209
    function __construct($dsn=false, $options=array())
210
    {
211
        if (!isset($options['autoConnect'])) {
212
            $autoConnect = true;
213
        } else {
214
            $autoConnect = $options['autoConnect'];
215
        }
216
        if (isset($options['errorCallback'])) {
217
            $this->setErrorCallback($options['errorCallback']);
218
        }
219
        if (isset($options['errorSetCallback'])) {
220
            $this->setErrorSetCallback($options['errorSetCallback']);
221
        }
222
        if (isset($options['errorLogCallback'])) {
223
            $this->setErrorLogCallback($options['errorLogCallback']);
224
        }
225
 
226
        if ($autoConnect && $dsn) {
227
            $this->connect($dsn, $options);
228
        }
229
        //we would need to parse the dsn first ... i dont feel like now :-)
230
        // oracle has all column names in upper case
231
//FIXXXME make the class work only with upper case when we work with oracle
232
        //if ($this->db->phptype=='oci8' && !$this->primaryCol) {
233
        //    $this->primaryCol = 'ID';
234
        //}
235
 
236
        if ($this->sequenceName == null) {
237
            $this->sequenceName = $this->table;
238
        }
239
    }
240
*/
241
 
242
    // }}}
243
    // {{{ DB_QueryTool_Query()
244
 
245
    /**
246
     * @version    2002/04/02
247
     * @access     public
248
     * @author     Wolfram Kriesing <wk@visionp.de>
249
     * @param mixed $dsn DSN string, DSN array or DB object
250
     * @param array $options
251
     */
252
    function DB_QueryTool_Query($dsn=false, $options=array())
253
    {
254
        //$this->__construct($dsn, $options);
255
        if (!isset($options['autoConnect'])) {
256
            $autoConnect = true;
257
        } else {
258
            $autoConnect = $options['autoConnect'];
259
            unset($options['autoConnect']);
260
        }
261
        if (isset($options['errorCallback'])) {
262
            $this->setErrorCallback($options['errorCallback']);
263
            unset($options['errorCallback']);
264
        }
265
        if (isset($options['errorSetCallback'])) {
266
            $this->setErrorSetCallback($options['errorSetCallback']);
267
            unset($options['errorSetCallback']);
268
        }
269
        if (isset($options['errorLogCallback'])) {
270
            $this->setErrorLogCallback($options['errorLogCallback']);
271
            unset($options['errorLogCallback']);
272
        }
273
        if ($autoConnect && $dsn) {
274
            $this->connect($dsn, $options);
275
        }
276
        if (is_null($this->sequenceName)) {
277
            $this->sequenceName = $this->table;
278
        }
279
    }
280
 
281
    // }}}
282
    // {{{ connect()
283
 
284
    /**
285
     * use this method if you want to connect manually
286
     * @param mixed $dsn DSN string, DSN array or MDB object
287
     * @param array $options
288
     */
289
    function connect($dsn, $options=array())
290
    {
291
        if (is_object($dsn)) {
292
            $res = $this->db =& $dsn;
293
        } else {
294
            $res = $this->db = DB::connect($dsn, $options);
295
        }
296
        if (DB::isError($res)) {
297
// FIXXME what shall we do here?
298
            $this->_errorLog($res->getUserInfo());
299
        } else {
300
            $this->db->setFetchMode(DB_FETCHMODE_ASSOC);
301
        }
302
    }
303
 
304
    // }}}
305
    // {{{ getDbInstance()
306
 
307
    /**
308
     * @return reference to current DB instance
309
     */
310
    function &getDbInstance()
311
    {
312
        return $this->db;
313
    }
314
 
315
    // }}}
316
    // {{{ setDbInstance()
317
 
318
    /**
319
     * Setup using an existing connection.
320
     * this also sets the DB_FETCHMODE_ASSOC since this class
321
     * needs this to be set!
322
     *
323
     * @param object a reference to an existing DB-object
324
     * @return void
325
     */
326
    function setDbInstance(&$dbh)
327
    {
328
        $this->db =& $dbh;
329
        $this->db->setFetchMode(DB_FETCHMODE_ASSOC);
330
    }
331
 
332
    // }}}
333
    // {{{ get()
334
 
335
    /**
336
     * get the data of a single entry
337
     * if the second parameter is only one column the result will be returned
338
     * directly not as an array!
339
     *
340
     * @version    2002/03/05
341
     * @access     public
342
     * @author     Wolfram Kriesing <wk@visionp.de>
343
     * @param      integer the id of the element to retrieve
344
     * @param      string  if this is given only one row shall be returned, directly, not an array
345
     * @return     mixed   (1) an array of the retrieved data
346
     *                     (2) if the second parameter is given and its only one column,
347
     *                         only this column's data will be returned
348
     *                     (3) false in case of failure
349
     */
350
    function get($id, $column='')
351
    {
352
        $table = $this->table;
353
        $getMethod = 'getRow';
354
        if ($column && !strpos($column, ',')) {   // if only one column shall be selected
355
            $getMethod = 'getOne';
356
        }
357
        // we dont use 'setSelect' here, since this changes the setup of the class, we
358
        // build the query directly
359
        // if $column is '' then _buildSelect selects '*' anyway, so that's the same behaviour as before
360
        $query['select'] = $this->_buildSelect($column);
361
        $query['where']  = $this->_buildWhere($this->table.'.'.$this->primaryCol.'='.$id);
362
        $queryString = $this->_buildSelectQuery($query);
363
 
364
        return $this->returnResult($this->execute($queryString,$getMethod));
365
    }
366
 
367
    // }}}
368
    // {{{ getMultiple()
369
 
370
    /**
371
     * gets the data of the given ids
372
     *
373
     * @version    2002/04/23
374
     * @access     public
375
     * @author     Wolfram Kriesing <wk@visionp.de>
376
     * @param      array   this is an array of ids to retreive
377
     * @param      string  the column to search in for
378
     * @return     mixed   an array of the retreived data, or false in case of failure
379
     *                       when failing an error is set in $this->_error
380
     */
381
    function getMultiple($ids, $column='')
382
    {
383
        $col = $this->primaryCol;
384
        if ($column) {
385
            $col = $column;
386
        }
387
// FIXXME if $ids has no table.col syntax and we are using joins, the table better be put in front!!!
388
        $ids = $this->_quoteArray($ids);
389
 
390
        $query['where'] = $this->_buildWhere($col.' IN ('.implode(',', $ids).')');
391
        $queryString = $this->_buildSelectQuery($query);
392
 
393
        return $this->returnResult($this->execute($queryString));
394
    }
395
 
396
    // }}}
397
    // {{{ getAll()
398
 
399
    /**
400
     * get all entries from the DB
401
     * for sorting use setOrder!!!, the last 2 parameters are deprecated
402
     *
403
     * @version    2002/03/05
404
     * @access     public
405
     * @author     Wolfram Kriesing <wk@visionp.de>
406
     * @param      int     to start from
407
     * @param      int     the number of rows to show
408
     * @param      string  the DB-method to use, i dont know if we should leave this param here ...
409
     * @return     mixed   an array of the retreived data, or false in case of failure
410
     *                       when failing an error is set in $this->_error
411
     */
412
    function getAll($from=0,$count=0,$method='getAll')
413
    {
414
        $query = array();
415
        if ($count) {
416
            $query = array('limit' => array($from, $count));
417
        }
418
        return $this->returnResult($this->execute($this->_buildSelectQuery($query), $method));
419
    }
420
 
421
    // }}}
422
    // {{{ getCol()
423
 
424
    /**
425
     * this method only returns one column, so the result will be a one dimensional array
426
     * this does also mean that using setSelect() should be set to *one* column, the one you want to
427
     * have returned a most common use case for this could be:
428
     *      $table->setSelect('id');
429
     *      $ids = $table->getCol();
430
     * OR
431
     *      $ids = $table->getCol('id');
432
     * so ids will be an array with all the id's
433
     *
434
     * @version    2003/02/25
435
     * @access     public
436
     * @author     Wolfram Kriesing <wk@visionp.de>
437
     * @param      string  the column that shall be retreived
438
     * @param      int     to start from
439
     * @param      int     the number of rows to show
440
     * @return     mixed   an array of the retreived data, or false in case of failure
441
     *                     when failing an error is set in $this->_error
442
     */
443
    function getCol($column=null, $from=0, $count=0)
444
    {
445
        $query = array();
446
        if (!is_null($column)) {
447
            // by using _buildSelect() i can be sure that the table name will not be ambigious
448
            // i.e. in a join, where all the joined tables have a col 'id'
449
            // _buildSelect() will put the proper table name in front in case there is none
450
            $query['select'] = $this->_buildSelect($column);
451
        }
452
        if ($count) {
453
            $query['limit'] = array($from,$count);
454
        }
455
        return $this->returnResult($this->execute($this->_buildSelectQuery($query), 'getCol'));
456
    }
457
 
458
    // }}}
459
    // {{{ getCount()
460
 
461
    /**
462
     * get the number of entries
463
     *
464
     * @version    2002/04/02
465
     * @access     public
466
     * @author     Wolfram Kriesing <wk@visionp.de>
467
     * @param
468
     * @return     mixed   an array of the retreived data, or false in case of failure
469
     *                       when failing an error is set in $this->_error
470
     */
471
    function getCount()
472
    {
473
/* the following query works on mysql
474
SELECT count(DISTINCT image.id) FROM image2tree
475
RIGHT JOIN image ON image.id = image2tree.image_id
476
the reason why this is needed - i just wanted to get the number of rows that do exist if the result is grouped by image.id
477
the following query is what i tried first, but that returns the number of rows that have been grouped together
478
for each image.id
479
SELECT count(*) FROM image2tree
480
RIGHT JOIN image ON image.id = image2tree.image_id GROUP BY image.id
481
 
482
so that's why we do the following, i am not sure if that is standard SQL and absolutley correct!!!
483
*/
484
 
485
//FIXXME see comment above if this is absolutely correct!!!
486
        if ($group = $this->_buildGroup()) {
487
            $query['select'] = 'COUNT(DISTINCT '.$group.')';
488
            $query['group'] = '';
489
        } else {
490
            $query['select'] = 'COUNT(*)';
491
        }
492
 
493
        $query['order'] = '';   // order is not of importance and might freak up the special group-handling up there, since the order-col is not be known
494
/*# FIXXME use the following line, but watch out, then it has to be used in every method, or this
495
# value will be used always, simply try calling getCount and getAll afterwards, getAll will return the count :-)
496
# if getAll doesn't use setSelect!!!
497
*/
498
        //$this->setSelect('count(*)');
499
        $queryString = $this->_buildSelectQuery($query, true);
500
 
501
        return ($res = $this->execute($queryString, 'getOne')) ? $res : 0;
502
    }
503
 
504
    // }}}
505
    // {{{ getDefaultValues()
506
 
507
    /**
508
     * return an empty element where all the array elements do already exist
509
     * corresponding to the columns in the DB
510
     *
511
     * @version    2002/04/05
512
     * @access     public
513
     * @author     Wolfram Kriesing <wk@visionp.de>
514
     * @return     array   an empty, or pre-initialized element
515
     */
516
    function getDefaultValues()
517
    {
518
        $ret = array();
519
        // here we read all the columns from the DB and initialize them
520
        // with '' to prevent PHP-warnings in case we use error_reporting=E_ALL
521
        foreach ($this->metadata() as $aCol=>$x) {
522
            $ret[$aCol] = '';
523
        }
524
        return $ret;
525
    }
526
 
527
    // }}}
528
    // {{{ getEmptyElement()
529
 
530
    /**
531
     * this is just for BC
532
     * @deprecated
533
     */
534
    function getEmptyElement()
535
    {
536
        $this->getDefaultValues();
537
    }
538
 
539
    // }}}
540
    // {{{ getQueryString()
541
 
542
    /**
543
     * Render the current query and return it as a string.
544
     *
545
     * @return string the current query
546
     */
547
    function getQueryString()
548
    {
549
        $ret = $this->_buildSelectQuery();
550
        if (is_string($ret)) {
551
            $ret = trim($ret);
552
        }
553
        return $ret;
554
    }
555
 
556
    // }}}
557
    // {{{ save()
558
 
559
    /**
560
     * save data, calls either update or add
561
     * if the primaryCol is given in the data this method knows that the
562
     * data passed to it are meant to be updated (call 'update'), otherwise it will
563
     * call the method 'add'.
564
     * If you dont like this behaviour simply stick with the methods 'add'
565
     * and 'update' and ignore this one here.
566
     * This method is very useful when you have validation checks that have to
567
     * be done for both adding and updating, then you can simply overwrite this
568
     * method and do the checks in here, and both cases will be validated first.
569
     *
570
     * @version    2002/03/11
571
     * @access     public
572
     * @author     Wolfram Kriesing <wk@visionp.de>
573
     * @param      array   contains the new data that shall be saved in the DB
574
     * @return     mixed   the data returned by either add or update-method
575
     */
576
    function save($data)
577
    {
578
        if (!empty($data[$this->primaryCol])) {
579
            return $this->update($data);
580
        }
581
        return $this->add($data);
582
    }
583
 
584
    // }}}
585
    // {{{ update()
586
 
587
    /**
588
     * update the member data of a data set
589
     *
590
     * @version    2002/03/06
591
     * @access     public
592
     * @author     Wolfram Kriesing <wk@visionp.de>
593
     * @param      array   contains the new data that shall be saved in the DB
594
     *                     the id has to be given in the field with the key 'ID'
595
     * @return     mixed   true on success, or false otherwise
596
     */
597
    function update($newData)
598
    {
599
        $query = array();
600
        // do only set the 'where' part in $query, if a primary column is given
601
        // if not the default 'where' clause is used
602
        if (isset($newData[$this->primaryCol])) {
603
            $query['where'] = $this->primaryCol.'='.$newData[$this->primaryCol];
604
        }
605
        $newData = $this->_checkColumns($newData, 'update');
606
        $values = array();
607
        $raw = $this->getOption('raw');
608
        foreach ($newData as $key => $aData) {         // quote the data
609
            //$values[] = "{$this->table}.$key=". ($raw ? $aData : $this->db->quote($aData));
610
            $values[] = "$key=". ($raw ? $aData : $this->db->quote($aData));
611
        }
612
 
613
        $query['set'] = implode(',', $values);
614
//FIXXXME _buildUpdateQuery() seems to take joins into account, whcih is bullshit here
615
        $updateString = $this->_buildUpdateQuery($query);
616
#print '$updateString = '.$updateString;
617
        return $this->execute($updateString, 'query') ? true : false;
618
    }
619
 
620
    // }}}
621
    // {{{ add()
622
 
623
    /**
624
     * add a new member in the DB
625
     *
626
     * @version    2002/04/02
627
     * @access     public
628
     * @author     Wolfram Kriesing <wk@visionp.de>
629
     * @param      array   contains the new data that shall be saved in the DB
630
     * @return     mixed   the inserted id on success, or false otherwise
631
     */
632
    function add($newData)
633
    {
634
        // if no primary col is given, get next sequence value
635
        if (empty($newData[$this->primaryCol])) {
636
            if ($this->primaryCol) {    // do only use the sequence if a primary column is given
637
                                        // otherwise the data are written as given
638
                $id = $this->db->nextId($this->sequenceName);
639
                $newData[$this->primaryCol] = (int)$id;
640
            } else {
641
                // if no primary col is given return true on success
642
                $id = true;
643
            }
644
        } else {
645
            $id = $newData[$this->primaryCol];
646
        }
647
 
648
        //unset($newData[$this->primaryCol]);
649
 
650
        $newData = $this->_checkColumns($newData, 'add');
651
        $newData = $this->_quoteArray($newData);
652
 
653
        $query = sprintf(   'INSERT INTO %s (%s) VALUES (%s)',
654
                            $this->table,
655
                            implode(', ', array_keys($newData)),
656
                            implode(', ', $newData)
657
                       );
658
        return $this->execute($query, 'query') ? $id : false;
659
    }
660
 
661
    // }}}
662
    // {{{ addMultiple()
663
 
664
    /**
665
     * adds multiple new members in the DB
666
     *
667
     * @version    2002/07/17
668
     * @access     public
669
     * @author     Wolfram Kriesing <wk@visionp.de>
670
     * @param      array   contains an array of new data that shall be saved in the DB
671
     *                     the key-value pairs have to be the same for all the data!!!
672
     * @return     mixed   the inserted ids on success, or false otherwise
673
     */
674
    function addMultiple($data)
675
    {
676
        if (!sizeof($data)) {
677
            return false;
678
        }
679
        // the inserted ids which will be returned or if no primaryCol is given
680
        // we return true by default
681
        $retIds = $this->primaryCol ? array() : true;
682
        $allData = array();     // each row that will be inserted
683
        foreach ($data as $key => $aData) {
684
            $aData = $this->_checkColumns($aData, 'add');
685
            $aData = $this->_quoteArray($aData);
686
 
687
            if (empty($aData[$this->primaryCol])) {
688
                if ($this->primaryCol) {    // do only use the sequence if a primary column is given
689
                                            // otherwise the data are written as given
690
                    $retIds[] = $id = (int)$this->db->nextId($this->sequenceName);
691
                    $aData[$this->primaryCol] = $id;
692
                }
693
            } else {
694
                $retIds[] = $aData[$this->primaryCol];
695
            }
696
            $allData[] = '('.implode(', ', $aData).')';
697
        }
698
 
699
        $query = sprintf(   'INSERT INTO %s (%s) VALUES %s',
700
                            $this->table,
701
                            implode(', ', array_keys($aData)), // use the keys of the last element built
702
                            implode(', ', $allData)
703
                        );
704
        return $this->execute($query, 'query') ? $retIds : false;
705
    }
706
 
707
    // }}}
708
    // {{{ remove()
709
 
710
   /**
711
     * removes a member from the DB
712
     *
713
     * @version    2002/04/08
714
     * @access     public
715
     * @author     Wolfram Kriesing <wk@visionp.de>
716
     * @param      mixed   integer/string - the value of the column that shall be removed
717
     *                       array   - multiple columns that shall be matched, the second parameter will be ignored
718
     * @param      string  the column to match the data against, only if $data is not an array
719
     * @return     boolean
720
     */
721
    function remove($data, $whereCol='')
722
    {
723
        $raw = $this->getOption('raw');
724
 
725
        if (is_array($data)) {
726
//FIXXME check $data if it only contains columns that really exist in the table
727
            $wheres = array();
728
            foreach ($data as $key => $val) {
729
                $wheres[] = $key.'='. ($raw ? $val : $this->db->quote($val));
730
            }
731
            $whereClause = implode(' AND ',$wheres);
732
        } else {
733
            if (empty($whereCol)) {
734
                $whereCol = $this->primaryCol;
735
            }
736
            $whereClause = $whereCol.'='. ($raw ? $data : $this->db->quote($data));
737
        }
738
 
739
        $query = sprintf(   'DELETE FROM %s WHERE %s',
740
                            $this->table,
741
                            $whereClause
742
                            );
743
        return $this->execute($query, 'query') ? true : false;
744
// i think this method should return the ID's that it removed, this way we could simply use the result
745
// for further actions that depend on those id ... or? make stuff easier, see ignaz::imail::remove
746
    }
747
 
748
    // }}}
749
    // {{{ removeAll()
750
 
751
    /**
752
     * empty a table
753
     *
754
     * @version    2002/06/17
755
     * @access     public
756
     * @author     Wolfram Kriesing <wk@visionp.de>
757
     * @return
758
     */
759
    function removeAll()
760
    {
761
        $query = 'DELETE FROM '.$this->table;
762
        return $this->execute($query, 'query') ? true : false;
763
    }
764
 
765
    // }}}
766
    // {{{ removeMultiple()
767
 
768
    /**
769
     * remove the datasets with the given ids
770
     *
771
     * @version    2002/04/24
772
     * @access     public
773
     * @author     Wolfram Kriesing <wk@visionp.de>
774
     * @param      array   the ids to remove
775
     * @return
776
     */
777
    function removeMultiple($ids, $colName='')
778
    {
779
        if (empty($colName)) {
780
            $colName = $this->primaryCol;
781
        }
782
        $ids = $this->_quoteArray($ids);
783
 
784
        $query = sprintf(   'DELETE FROM %s WHERE %s IN (%s)',
785
                            $this->table,
786
                            $colName,
787
                            implode(',', $ids)
788
                        );
789
        return $this->execute($query, 'query') ? true : false;
790
    }
791
 
792
    // }}}
793
    // {{{ removePrimary()
794
 
795
    /**
796
     * removes a member from the DB and calls the remove methods of the given objects
797
     * so all rows in another table that refer to this table are erased too
798
     *
799
     * @version    2002/04/08
800
     * @access     public
801
     * @author     Wolfram Kriesing <wk@visionp.de>
802
     * @param      integer the value of the primary key
803
     * @param      string  the column name of the tables with the foreign keys
804
     * @param      object  just for convinience, so nobody forgets to call this method
805
     *                       with at least one object as a parameter
806
     * @return     boolean
807
     */
808
    function removePrimary($id, $colName, $atLeastOneObject)
809
    {
810
        $argCounter = 2;    // we have 2 parameters that need to be given at least
811
        // func_get_arg returns false and a warning if there are no more parameters, so
812
        // we suppress the warning and check for false
813
        while ($object = @func_get_arg($argCounter++)) {
814
//FIXXXME let $object also simply be a table name
815
            if (!$object->remove($id, $colName)) {
816
//FIXXXME do this better
817
                $this->_errorSet("Error removing '$colName=$id' from table {$object->table}.");
818
               return false;
819
            }
820
        }
821
 
822
        return ($this->remove($id) ? true : false);
823
    }
824
 
825
    // }}}
826
    // {{{ setLimit()
827
 
828
    /**
829
     * @param integer $from
830
     * @param integer $count
831
     */
832
    function setLimit($from=0, $count=0)
833
    {
834
        if ($from==0 && $count==0) {
835
            $this->_limit = array();
836
        } else {
837
            $this->_limit = array($from, $count);
838
        }
839
    }
840
 
841
    // }}}
842
    // {{{ getLimit()
843
 
844
    /**
845
     * @return array
846
     */
847
    function getLimit()
848
    {
849
        return $this->_limit;
850
    }
851
 
852
    // }}}
853
    // {{{ setWhere()
854
 
855
    /**
856
     * sets the where condition which is used for the current instance
857
     *
858
     * @version    2002/04/16
859
     * @access     public
860
     * @author     Wolfram Kriesing <wk@visionp.de>
861
     * @param      string  the where condition, this can be complete like 'X=7 AND Y=8'
862
     */
863
    function setWhere($whereCondition='')
864
    {
865
        $this->_where = $whereCondition;
866
//FIXXME parse the where condition and replace ambigious column names, such as "name='Deutschland'" with "country.name='Deutschland'"
867
// then the users dont have to write that explicitly and can use the same name as in the setOrder i.e. setOrder('name,_net_name,_netPrefix_prefix');
868
    }
869
 
870
    // }}}
871
    // {{{ getWhere()
872
 
873
    /**
874
     * gets the where condition which is used for the current instance
875
     *
876
     * @version    2002/04/22
877
     * @access     public
878
     * @author     Wolfram Kriesing <wk@visionp.de>
879
     * @return     string  the where condition, this can be complete like 'X=7 AND Y=8'
880
     */
881
    function getWhere()
882
    {
883
        return $this->_where;
884
    }
885
 
886
    // }}}
887
    // {{{ addWhere()
888
 
889
    /**
890
     * only adds a string to the where clause
891
     *
892
     * @version    2002/07/22
893
     * @access     public
894
     * @author     Wolfram Kriesing <wk@visionp.de>
895
     * @param      string  the where clause to add to the existing one
896
     * @param      string  the condition for how to concatenate the new where clause
897
     *                       to the existing one
898
     */
899
    function addWhere($where, $condition='AND')
900
    {
901
        if ($this->getWhere()) {
902
            $where = $this->getWhere().' '.$condition.' '.$where;
903
        }
904
        $this->setWhere($where);
905
    }
906
 
907
    // }}}
908
    // {{{ addWhereSearch()
909
 
910
    /**
911
     * add a where-like clause which works like a search for the given string
912
     * i.e. calling it like this:
913
     *     $this->addWhereSearch('name', 'otto hans')
914
     * produces a where clause like this one
915
     *     LOWER(name) LIKE "%otto%hans%"
916
     * so the search finds the given string
917
     *
918
     * @version    2002/08/14
919
     * @access     public
920
     * @author     Wolfram Kriesing <wk@visionp.de>
921
     * @param      string  the column to search in for
922
     * @param      string  the string to search for
923
     */
924
    function addWhereSearch($column, $string, $condition='AND')
925
    {
926
        // if the column doesn't contain a tablename use the current table name
927
        // in case it is a defined column to prevent ambiguous rows
928
        if (strpos($column, '.') === false) {
929
            $meta = $this->metadata();
930
            if (isset($meta[$column])) {
931
                $column = $this->table.".$column";
932
            }
933
        }
934
 
935
        $string = $this->db->quote('%'.str_replace(' ', '%', strtolower($string)).'%');
936
        $this->addWhere("LOWER($column) LIKE $string", $condition);
937
    }
938
 
939
    // }}}
940
    // {{{ setOrder()
941
 
942
    /**
943
     * sets the order condition which is used for the current instance
944
     *
945
     * @version    2002/05/16
946
     * @access     public
947
     * @author     Wolfram Kriesing <wk@visionp.de>
948
     * @param      string  the where condition, this can be complete like 'X=7 AND Y=8'
949
     * @param      boolean sorting order (TRUE => ASC, FALSE => DESC)
950
     */
951
    function setOrder($orderCondition='', $desc=false)
952
    {
953
        $this->_order = $orderCondition .($desc ? ' DESC' : '');
954
    }
955
 
956
    // }}}
957
    // {{{ addOrder()
958
 
959
    /**
960
     * Add a order parameter to the query.
961
     *
962
     * @version    2003/05/28
963
     * @access     public
964
     * @author     Wolfram Kriesing <wk@visionp.de>
965
     * @param      string  the where condition, this can be complete like 'X=7 AND Y=8'
966
     * @param      boolean sorting order (TRUE => ASC, FALSE => DESC)
967
     */
968
    function addOrder($orderCondition='', $desc=false)
969
    {
970
        $order = $orderCondition .($desc ? ' DESC' : '');
971
        if ($this->_order) {
972
            $this->_order = $this->_order.','.$order;
973
        } else {
974
            $this->_order = $order;
975
        }
976
    }
977
 
978
    // }}}
979
    // {{{ getOrder()
980
 
981
    /**
982
     * gets the order condition which is used for the current instance
983
     *
984
     * @version    2002/05/16
985
     * @access     public
986
     * @author     Wolfram Kriesing <wk@visionp.de>
987
     * @return     string  the order condition, this can be complete like 'ID,TIMESTAMP DESC'
988
     */
989
    function getOrder()
990
    {
991
        return $this->_order;
992
    }
993
 
994
    // }}}
995
    // {{{ setHaving()
996
 
997
    /**
998
     * sets the having definition
999
     *
1000
     * @version    2003/06/05
1001
     * @access     public
1002
     * @author     Johannes Schaefer <johnschaefer@gmx.de>
1003
     * @param      string  the having definition
1004
     */
1005
    function setHaving($having='')
1006
    {
1007
        $this->_having = $having;
1008
    }
1009
 
1010
    // }}}
1011
    // {{{ getHaving()
1012
 
1013
    /**
1014
     * gets the having definition which is used for the current instance
1015
     *
1016
     * @version    2003/06/05
1017
     * @access     public
1018
     * @author     Johannes Schaefer <johnschaefer@gmx.de>
1019
     * @return     string  the having definition
1020
     */
1021
    function getHaving()
1022
    {
1023
        return $this->_having;
1024
    }
1025
 
1026
    // }}}
1027
    // {{{ addHaving()
1028
 
1029
    /**
1030
     * Extend the current having clause. This is very useful, when you are building
1031
     * this clause from different places and don't want to overwrite the currently
1032
     * set having clause, but extend it.
1033
     *
1034
     * @param string this is a having clause, i.e. 'column' or 'table.column' or 'MAX(column)'
1035
     * @param string the connection string, which usually stays the default, which is ',' (a comma)
1036
     */
1037
    function addHaving($what='*', $connectString=' AND ')
1038
    {
1039
        if ($this->_having) {
1040
            $this->_having = $this->_having.$connectString.$what;
1041
        } else {
1042
            $this->_having = $what;
1043
        }
1044
    }
1045
 
1046
    // }}}
1047
    // {{{ setJoin()
1048
 
1049
    /**
1050
     * sets a join-condition
1051
     *
1052
     * @version    2002/06/10
1053
     * @access     public
1054
     * @author     Wolfram Kriesing <wk@visionp.de>
1055
     * @param      mixed   either a string or an array that contains
1056
     *                       the table(s) to join on the current table
1057
     * @param      string  the where clause for the join
1058
     */
1059
    function setJoin($table=null, $where=null, $joinType='default')
1060
    {
1061
//FIXXME make it possible to pass a table name as a string like this too 'user u'
1062
// where u is the string that can be used to refer to this table in a where/order
1063
// or whatever condition
1064
// this way it will be possible to join tables with itself, like setJoin(array('user u','user u1'))
1065
// this wouldnt work yet, but for doing so we would need to change the _build methods too!!!
1066
// because they use getJoin('tables') and this simply returns all the tables in use
1067
// but don't take care of the mentioned syntax
1068
 
1069
        if (is_null($table) || is_null($where)) {   // remove the join if not sufficient parameters are given
1070
            $this->_join[$joinType] = array();
1071
            return;
1072
        }
1073
/* this causes problems if we use the order-by, since it doenst know the name to order it by ... :-)
1074
        // replace the table names with the internal name used for the join
1075
        // this way we can also join one table multiple times if it will be implemented one day
1076
        $this->_join[$table] = preg_replace('/'.$table.'/','j1',$where);
1077
*/
1078
        $this->_join[$joinType][$table] = $where;
1079
    }
1080
 
1081
    // }}}
1082
    // {{{ setJoin()
1083
 
1084
    /**
1085
     * if you do a left join on $this->table you will get all entries
1086
     * from $this->table, also if there are no entries for them in the joined table
1087
     * if both parameters are not given the left-join will be removed
1088
     * NOTE: be sure to only use either a right or a left join
1089
     *
1090
     * @version    2002/07/22
1091
     * @access     public
1092
     * @author     Wolfram Kriesing <wk@visionp.de>
1093
     * @param      string  the table(s) to be left-joined
1094
     * @param      string  the where clause for the join
1095
     */
1096
    function setLeftJoin($table=null, $where=null)
1097
    {
1098
        $this->setJoin($table, $where, 'left');
1099
    }
1100
 
1101
    // }}}
1102
    // {{{ addLeftJoin()
1103
 
1104
    /**
1105
     * @param string the table to be left-joined
1106
     * @param string the where clause for the join
1107
     * @param string the join type
1108
     */
1109
    function addLeftJoin($table, $where, $type='left')
1110
    {
1111
        // init value, to prevent E_ALL-warning
1112
        if (!isset($this->_join[$type]) || !$this->_join[$type]) {
1113
            $this->_join[$type] = array();
1114
        }
1115
        $this->_join[$type][$table] = $where;
1116
    }
1117
 
1118
    // }}}
1119
    // {{{ setRightJoin()
1120
 
1121
    /**
1122
     * see setLeftJoin for further explaination on what a left/right join is
1123
     * NOTE: be sure to only use either a right or a left join
1124
//FIXXME check if the above sentence is necessary and if sql doesnt allow the use of both
1125
     *
1126
     * @see        setLeftJoin()
1127
     * @version    2002/09/04
1128
     * @access     public
1129
     * @author     Wolfram Kriesing <wk@visionp.de>
1130
     * @param      string  the table(s) to be right-joined
1131
     * @param      string  the where clause for the join
1132
     */
1133
    function setRightJoin($table=null, $where=null)
1134
    {
1135
        $this->setJoin($table, $where, 'right');
1136
    }
1137
 
1138
    // }}}
1139
    // {{{ getJoin()
1140
 
1141
    /**
1142
     * gets the join-condition
1143
     *
1144
     * @access public
1145
     * @param  string  [null|''|'table'|'tables'|'right'|'left']
1146
     * @return array   gets the join parameters
1147
     */
1148
    function getJoin($what=null)
1149
    {
1150
        // if the user requests all the join data or if the join is empty, return it
1151
        if (is_null($what) || empty($this->_join)) {
1152
            return $this->_join;
1153
        }
1154
 
1155
        $ret = array();
1156
        switch (strtolower($what)) {
1157
            case 'table':
1158
            case 'tables':
1159
                foreach ($this->_join as $aJoin) {
1160
                    if (count($aJoin)) {
1161
                        $ret = array_merge($ret, array_keys($aJoin));
1162
                    }
1163
                }
1164
                break;
1165
            case 'right':   // return right-join data only
1166
            case 'left':    // return left join data only
1167
                if (count($this->_join[$what])) {
1168
                    $ret = array_merge($ret, $this->_join[$what]);
1169
                }
1170
                break;
1171
        }
1172
        return $ret;
1173
    }
1174
 
1175
    // }}}
1176
    // {{{ addJoin()
1177
 
1178
    /**
1179
     *   adds a table and a where clause that shall be used for the join
1180
     *   instead of calling
1181
     *       setJoin(array(table1,table2),'<where clause1> AND <where clause2>')
1182
     *   you can also call
1183
     *       setJoin(table1,'<where clause1>')
1184
     *       addJoin(table2,'<where clause2>')
1185
     *   or where it makes more sense is to build a query which is made out of a
1186
     *   left join and a standard join
1187
     *       setLeftJoin(table1,'<where clause1>')
1188
     *       // results in ... FROM $this->table LEFT JOIN table ON <where clause1>
1189
     *       addJoin(table2,'<where clause2>')
1190
     *       // results in ...  FROM $this->table,table2 LEFT JOIN table ON <where clause1> WHERE <where clause2>
1191
     *
1192
     * @access     public
1193
     * @param      string the table to be joined
1194
     * @param      string the where clause for the join
1195
     * @param      string the join type
1196
     */
1197
    function addJoin($table, $where, $type='default')
1198
    {
1199
        if ($table == $this->table) {
1200
            return;  //skip. Self joins are not supported.
1201
        }
1202
        // init value, to prevent E_ALL-warning
1203
        if (!isset($this->_join[$type]) || !$this->_join[$type]) {
1204
            $this->_join[$type] = array();
1205
        }
1206
        $this->_join[$type][$table] = $where;
1207
    }
1208
 
1209
    // }}}
1210
    // {{{ setTable()
1211
 
1212
    /**
1213
     * sets the table this class is currently working on
1214
     *
1215
     * @version    2002/07/11
1216
     * @access     public
1217
     * @author     Wolfram Kriesing <wk@visionp.de>
1218
     * @param      string  the table name
1219
     */
1220
    function setTable($table)
1221
    {
1222
        $this->table = $table;
1223
    }
1224
 
1225
    // }}}
1226
    // {{{ getTable()
1227
 
1228
    /**
1229
     * gets the table this class is currently working on
1230
     *
1231
     * @version    2002/07/11
1232
     * @access     public
1233
     * @author     Wolfram Kriesing <wk@visionp.de>
1234
     * @return     string  the table name
1235
     */
1236
    function getTable()
1237
    {
1238
        return $this->table;
1239
    }
1240
 
1241
    // }}}
1242
    // {{{ setGroup()
1243
 
1244
    /**
1245
     * sets the group-by condition
1246
     *
1247
     * @version    2002/07/22
1248
     * @access     public
1249
     * @author     Wolfram Kriesing <wk@visionp.de>
1250
     * @param      string  the group condition
1251
     */
1252
    function setGroup($group='')
1253
    {
1254
        $this->_group = $group;
1255
//FIXXME parse the condition and replace ambigious column names, such as "name='Deutschland'" with "country.name='Deutschland'"
1256
// then the users dont have to write that explicitly and can use the same name as in the setOrder i.e. setOrder('name,_net_name,_netPrefix_prefix');
1257
    }
1258
 
1259
    // }}}
1260
    // {{{ getGroup()
1261
 
1262
    /**
1263
     *   gets the group condition which is used for the current instance
1264
     *
1265
     * @version    2002/07/22
1266
     * @access     public
1267
     * @author     Wolfram Kriesing <wk@visionp.de>
1268
     * @return     string  the group condition
1269
     */
1270
    function getGroup()
1271
    {
1272
        return $this->_group;
1273
    }
1274
 
1275
    // }}}
1276
    // {{{ setSelect()
1277
 
1278
    /**
1279
     * limit the result to return only the columns given in $what
1280
     * @param string fields that shall be selected
1281
     */
1282
    function setSelect($what='*')
1283
    {
1284
        $this->_select = $what;
1285
    }
1286
 
1287
    // }}}
1288
    // {{{ addSelect()
1289
 
1290
    /**
1291
     * add a string to the select part of the query
1292
     *
1293
     * add a string to the select-part of the query and connects it to an existing
1294
     * string using the $connectString, which by default is a comma.
1295
     * (SELECT xxx FROM - xxx is the select-part of a query)
1296
     *
1297
     * @version    2003/01/08
1298
     * @access     public
1299
     * @author     Wolfram Kriesing <wk@visionp.de>
1300
     * @param      string  the string that shall be added to the select-part
1301
     * @param      string  the string to connect the new string with the existing one
1302
     * @return     void
1303
     */
1304
    function addSelect($what='*', $connectString=',')
1305
    {
1306
        // if the select string is not empty add the string, otherwise simply set it
1307
        if ($this->_select) {
1308
            $this->_select = $this->_select.$connectString.$what;
1309
        } else {
1310
            $this->_select = $what;
1311
        }
1312
    }
1313
 
1314
    // }}}
1315
    // {{{ getSelect()
1316
 
1317
    /**
1318
     * @return     string
1319
     */
1320
    function getSelect()
1321
    {
1322
        return $this->_select;
1323
    }
1324
 
1325
    // }}}
1326
    // {{{ setDontSelect()
1327
 
1328
    /**
1329
     * @param     string
1330
     */
1331
    function setDontSelect($what='')
1332
    {
1333
        $this->_dontSelect = $what;
1334
    }
1335
 
1336
    // }}}
1337
    // {{{ getDontSelect()
1338
 
1339
    /**
1340
     * @return     string
1341
     */
1342
    function getDontSelect()
1343
    {
1344
        return $this->_dontSelect;
1345
    }
1346
 
1347
    // }}}
1348
    // {{{ reset()
1349
 
1350
    /**
1351
     * reset all the set* settings; with no parameter given, it resets them all
1352
     *
1353
     * @version    2002/09/16
1354
     * @access     public
1355
     * @author     Wolfram Kriesing <wk@visionp.de>
1356
     * @return     void
1357
     */
1358
    function reset($what=array())
1359
    {
1360
        if (!sizeof($what)) {
1361
            $what = array(
1362
                'select',
1363
                'dontSelect',
1364
                'group',
1365
                'having',
1366
                'limit',
1367
                'where',
1368
                'index',
1369
                'order',
1370
                'join',
1371
                'leftJoin',
1372
                'rightJoin'
1373
            );
1374
        }
1375
 
1376
        foreach ($what as $aReset) {
1377
            $this->{'set'.ucfirst($aReset)}();
1378
        }
1379
    }
1380
 
1381
    // }}}
1382
    // {{{ setOption()
1383
 
1384
    /**
1385
     * set mode the class shall work in
1386
     * currently we have the modes:
1387
     * 'raw'   does not quote the data before building the query
1388
     *
1389
     * @version    2002/09/17
1390
     * @access     public
1391
     * @author     Wolfram Kriesing <wk@visionp.de>
1392
     * @param      string      the mode to be set
1393
     * @param      mixed       the value of the mode
1394
     * @return     void
1395
     */
1396
    function setOption($option, $value)
1397
    {
1398
        $this->options[strtolower($option)] = $value;
1399
    }
1400
 
1401
    // }}}
1402
    // {{{ getOption()
1403
 
1404
    /**
1405
     * @return     string
1406
     */
1407
    function getOption($option)
1408
    {
1409
        return $this->options[strtolower($option)];
1410
    }
1411
 
1412
    // }}}
1413
    // {{{ _quoteArray()
1414
 
1415
    /**
1416
     * quotes all the data in this array if we are not in raw mode!
1417
     * @param array
1418
     */
1419
    function _quoteArray($data)
1420
    {
1421
        if (!$this->getOption('raw')) {
1422
            foreach ($data as $key => $val) {
1423
                $data[$key] = $this->db->quote($val);
1424
            }
1425
        }
1426
        return $data;
1427
    }
1428
 
1429
    // }}}
1430
    // {{{ _checkColumns()
1431
 
1432
    /**
1433
     * checks if the columns which are given as the array's indexes really exist
1434
     * if not it will be unset anyway
1435
     *
1436
     * @version    2002/04/16
1437
     * @access     public
1438
     * @author     Wolfram Kriesing <wk@visionp.de>
1439
     * @param      string  the actual message, first word should always be the method name,
1440
     *                       to build the message like this: className::methodname
1441
     * @param      integer the line number
1442
     */
1443
    function _checkColumns($newData, $method='unknown')
1444
    {
1445
        if (!$meta = $this->metadata()) {   // if no metadata available, return data as given
1446
            return $newData;
1447
        }
1448
 
1449
        foreach ($newData as $colName => $x) {
1450
            if (!isset($meta[$colName])) {
1451
                $this->_errorLog("$method, column {$this->table}.$colName doesnt exist, value was removed before '$method'",__LINE__);
1452
                unset($newData[$colName]);
1453
            } else {
1454
                // if the current column exists, check the length too, not to write content that is too long
1455
                // prevent DB-errors here
1456
                // do only check the data length if this field is given
1457
                if (isset($meta[$colName]['len']) && ($meta[$colName]['len'] != -1) &&
1458
                    ($oldLength=strlen($newData[$colName])) > $meta[$colName]['len']
1459
                ) {
1460
                    $this->_errorLog("_checkColumns, had to trim column '$colName' from $oldLength to ".
1461
                                        $meta[$colName]['DATA_LENGTH'].' characters.', __LINE__);
1462
                    $newData[$colName] = substr($newData[$colName], 0, $meta[$colName]['len']);
1463
                }
1464
            }
1465
        }
1466
        return $newData;
1467
    }
1468
 
1469
    // }}}
1470
    // {{{ debug()
1471
 
1472
    /**
1473
     * overwrite this method and i.e. print the query $string
1474
     * to see the final query
1475
     *
1476
     * @param      string  the query mostly
1477
     */
1478
    function debug($string){}
1479
 
1480
    //
1481
    //
1482
    //  ONLY ORACLE SPECIFIC, not very nice since it is DB dependent, but we need it!!!
1483
    //
1484
    //
1485
 
1486
    // }}}
1487
    // {{{ metadata()
1488
 
1489
    /**
1490
     * !!!! query COPIED FROM db_oci8.inc - from PHPLIB !!!!
1491
     *
1492
     * @access   public
1493
     * @see
1494
     * @version  2001/09
1495
     * @author   PHPLIB
1496
     * @param
1497
     * @return
1498
     */
1499
    function metadata($table='')
1500
    {
1501
        // is there an alias in the table name, then we have something like this: 'user ua'
1502
        // cut of the alias and return the table name
1503
        if (strpos($table, ' ') !== false) {
1504
            $split = explode(' ', trim($table));
1505
            $table = $split[0];
1506
        }
1507
 
1508
        $full = false;
1509
        if (empty($table)) {
1510
            $table = $this->table;
1511
        }
1512
        // to prevent multiple selects for the same metadata
1513
        if (isset($this->_metadata[$table])) {
1514
            return $this->_metadata[$table];
1515
        }
1516
 
1517
// FIXXXME use oci8 implementation of newer PEAR::DB-version
1518
        if ($this->db->phptype == 'oci8') {
1519
            $count = 0;
1520
            $id    = 0;
1521
            $res   = array();
1522
 
1523
            //# This is a RIGHT OUTER JOIN: "(+)", if you want to see, what
1524
            //# this query results try the following:
1525
            //// $table = new Table; $this->db = new my_DB_Sql; // you have to make
1526
            ////                                          // your own class
1527
            //// $table->show_results($this->db->query(see query vvvvvv))
1528
            ////
1529
            $res = $this->db->getAll("SELECT T.column_name,T.table_name,T.data_type,".
1530
                "T.data_length,T.data_precision,T.data_scale,T.nullable,".
1531
                "T.char_col_decl_length,I.index_name".
1532
                " FROM ALL_TAB_COLUMNS T,ALL_IND_COLUMNS I".
1533
                " WHERE T.column_name=I.column_name (+)".
1534
                " AND T.table_name=I.table_name (+)".
1535
                " AND T.table_name=UPPER('$table') ORDER BY T.column_id");
1536
 
1537
            if (DB::isError($res)) {
1538
                //$this->_errorSet($res->getMessage());
1539
                // i think we only need to log here, since this method is never used
1540
                // directly for the user's functionality, which means if it fails it
1541
                // is most probably an appl error
1542
                $this->_errorLog($res->getUserInfo());
1543
                return false;
1544
            }
1545
            foreach ($res as $key=>$val) {
1546
                $res[$key]['name'] = $val['COLUMN_NAME'];
1547
            }
1548
        } else {
1549
            if (!is_object($this->db)) {
1550
                return false;
1551
            }
1552
            $res = $this->db->tableinfo($table);
1553
            if (DB::isError($res)) {
1554
                $this->_errorSet($res->getUserInfo());
1555
                return false;
1556
            }
1557
        }
1558
 
1559
        $ret = array();
1560
        foreach ($res as $key => $val) {
1561
            $ret[$val['name']] = $val;
1562
        }
1563
        $this->_metadata[$table] = $ret;
1564
        return $ret;
1565
    }
1566
 
1567
 
1568
 
1569
    //
1570
    //  methods for building the query
1571
    //
1572
 
1573
    // }}}
1574
    // {{{ _buildFrom()
1575
 
1576
    /**
1577
     * build the from string
1578
     *
1579
     * @access     private
1580
     * @return     string  the string added after FROM
1581
     */
1582
    function _buildFrom()
1583
    {
1584
        $from = $this->table;
1585
        $join = $this->getJoin();
1586
 
1587
        if (!$join) {  // no join set
1588
            return $from;
1589
        }
1590
        // handle the standard join thingy
1591
        if (isset($join['default']) && count($join['default'])) {
1592
            $from .= ','.implode(',',array_keys($join['default']));
1593
        }
1594
 
1595
        // handle left/right joins
1596
        foreach (array('left', 'right') as $joinType) {
1597
            if (isset($join[$joinType]) && count($join[$joinType])) {
1598
                foreach($join[$joinType] as $table => $condition) {
1599
                    // replace the _TABLENAME_COLUMNNAME by TABLENAME.COLUMNNAME
1600
                    // since oracle doesnt work with the _TABLENAME_COLUMNNAME which i think is strange
1601
// FIXXME i think this should become deprecated since the setWhere should not be used like this: '_table_column' but 'table.column'
1602
                    $regExp = '/_('.$table.')_([^\s]+)/';
1603
                    $where = preg_replace($regExp, '$1.$2', $condition);
1604
 
1605
                    // add the table name before any column that has no table prefix
1606
                    // since this might cause "unambiguous column" errors
1607
                    if ($meta = $this->metadata()) {
1608
                        foreach ($meta as $aCol=>$x) {
1609
                            // this covers the LIKE,IN stuff: 'name LIKE "%you%"'  'id IN (2,3,4,5)'
1610
                            $condition = preg_replace('/\s'.$aCol.'\s/', " {$this->table}.$aCol ", $condition);
1611
                            // replace also the column names which are behind a '='
1612
                            // and do this also if the aCol is at the end of the where clause
1613
                            // that's what the $ is for
1614
                            $condition = preg_replace('/=\s*'.$aCol.'(\s|$)/', "={$this->table}.$aCol ", $condition);
1615
                            // replace if colName is first and possibly also if at the beginning of the where-string
1616
                            $condition = preg_replace('/(^\s*|\s+)'.$aCol.'\s*=/', "$1{$this->table}.$aCol=", $condition);
1617
                        }
1618
                    }
1619
                    $from .= ' '.strtoupper($joinType).' JOIN '.$table.' ON '.$condition;
1620
                }
1621
            }
1622
        }
1623
        return $from;
1624
    }
1625
 
1626
    // }}}
1627
    // {{{ getTableShortName()
1628
 
1629
    /**
1630
     *   this method gets the short name for a table
1631
     *
1632
     *   get the short name for a table, this is needed to properly build the
1633
     *   'AS' parts in the select query
1634
     * @param  string  the real table name
1635
     * @return string  the table's short name
1636
     */
1637
    function getTableShortName($table)
1638
    {
1639
        $tableSpec = $this->getTableSpec(false);
1640
        if (isset($tableSpec[$table]['shortName']) && $tableSpec[$table]['shortName']) {
1641
//print "$table ... ".$tableSpec[$table]['shortName'].'<br>';
1642
            return $tableSpec[$table]['shortName'];
1643
        }
1644
 
1645
        $possibleTableShortName = preg_replace($this->_tableNameToShortNamePreg, '', $table);
1646
//print "$table ... $possibleTableShortName<br>";
1647
        return $possibleTableShortName;
1648
    }
1649
 
1650
    // }}}
1651
    // {{{ getTableSpec()
1652
 
1653
    /**
1654
     * gets the tableSpec either indexed by the short name or the name
1655
     * returns the array for the tables given as parameter or if no
1656
     * parameter given for all tables that exist in the tableSpec
1657
     *
1658
     * @param      array   table names (not the short names!)
1659
     * @param      boolean if true the table is returned indexed by the shortName
1660
     *                       otherwise indexed by the name
1661
     * @return     array   the tableSpec indexed
1662
     */
1663
    function getTableSpec($shortNameIndexed=true, $tables=array())
1664
    {
1665
        $newSpec = array();
1666
        foreach ($this->tableSpec as $aSpec) {
1667
            if (sizeof($tables)==0 || in_array($aSpec['name'],$tables)) {
1668
                if ($shortNameIndexed) {
1669
                    $newSpec[$aSpec['shortName']] = $aSpec;
1670
                } else {
1671
                    $newSpec[$aSpec['name']] = $aSpec;
1672
                }
1673
            }
1674
        }
1675
        return $newSpec;
1676
    }
1677
 
1678
    // }}}
1679
    // {{{ _buildSelect()
1680
 
1681
    /**
1682
     *   build the 'SELECT <what> FROM ... 'for a select
1683
     *
1684
     * @version    2002/07/11
1685
     * @access     public
1686
     * @author     Wolfram Kriesing <wk@visionp.de>
1687
     * @param      string      if given use this string
1688
     * @return     string      the what-clause
1689
     */
1690
    function _buildSelect($what=null)
1691
    {
1692
        // what has preference, that means if what is set it is used
1693
        // this is only because the methods like 'get' pass an individually built value, which
1694
        // is supposed to be used, but usually it's generically build using the 'getSelect' values
1695
        if (empty($what) && $this->getSelect()) {
1696
            $what = $this->getSelect();
1697
        }
1698
 
1699
        //
1700
        // replace all the '*' by the real column names, and take care of the dontSelect-columns!
1701
        //
1702
        $dontSelect = $this->getDontSelect();
1703
        $dontSelect = $dontSelect ? explode(',', $dontSelect) : array(); // make sure dontSelect is an array
1704
 
1705
        // here we will replace all the '*' and 'table.*' by all the columns that this table
1706
        // contains. we do this so we can easily apply the 'dontSelect' values.
1707
        // and so we can also handle queries like: 'SELECT *,count() FROM ' and 'SELECT table.*,x FROM ' too
1708
        if (strpos($what, '*') !== false) {
1709
            // subpattern 1 get all the table names, that are written like this: 'table.*' including '*'
1710
            // for '*' the tablename will be ''
1711
            preg_match_all('/([^,]*)(\.)?\*\s*(,|$)/U', $what, $res);
1712
//print "$what ... ";print_r($res);print "<br>";
1713
            $selectAllFromTables = array_unique($res[1]); // make the table names unique, so we do it all just once for each table
1714
            $tables = array();
1715
            if (in_array('', $selectAllFromTables)) { // was there a '*' ?
1716
                // get all the tables that we need to process, depending on if joined or not
1717
                $tables = $this->getJoin() ?
1718
                                array_merge($this->getJoin('tables'), array($this->table)) : // get the joined tables and this->table
1719
                                array($this->table);        // create an array with only this->table
1720
            } else {
1721
                $tables = $selectAllFromTables;
1722
            }
1723
 
1724
            $cols = array();
1725
            foreach ($tables as $aTable) {      // go thru all the tables and get all columns for each, and handle 'dontSelect'
1726
                if ($meta = $this->metadata($aTable)) {
1727
                    foreach ($meta as $colName => $x) {
1728
                        // handle the dontSelect's
1729
                        if (in_array($colName, $dontSelect) || in_array("$aTable.$colName", $dontSelect)) {
1730
                            continue;
1731
                        }
1732
 
1733
                        // build the AS clauses
1734
                        // put " around them to enable use of reserved words, i.e. SELECT table.option as option FROM...
1735
                        // and 'option' actually is a reserved word, at least in mysql
1736
                        // put double quotes around them, since pgsql doesnt work with single quotes
1737
                        // but don't do this for ibase because it doesn't work!
1738
                        if ($aTable == $this->table) {
1739
                            if ($this->db->phptype == 'ibase') {
1740
                                $cols[$aTable][] = $this->table. '.' .$colName . ' AS '. $colName;
1741
                            } else {
1742
                                $cols[$aTable][] = $this->table. '.' .$colName . ' AS "'. $colName .'"';
1743
                            }
1744
                        } else {
1745
                            $cols[$aTable][] = "$aTable.$colName AS \"_".$this->getTableShortName($aTable)."_$colName\"";
1746
                        }
1747
                    }
1748
                }
1749
            }
1750
 
1751
            // put the extracted select back in the $what
1752
            // that means replace 'table.*' by the i.e. 'table.id AS _table_id'
1753
            // or if it is the table of this class replace 'table.id AS id'
1754
            if (in_array('', $selectAllFromTables)) {
1755
                $allCols = array();
1756
                foreach ($cols as $aTable) {
1757
                    $allCols[] = implode(',', $aTable);
1758
                }
1759
                $what = preg_replace('/(^|,)\*($|,)/', '$1'.implode(',',$allCols).'$2', $what);
1760
                // remove all the 'table.*' since we have selected all anyway (because there was a '*' in the select)
1761
                $what = preg_replace('/[^,]*(\.)?\*\s*(,|$)/U', '', $what);
1762
            } else {
1763
                foreach ($cols as $tableName => $aTable) {
1764
                    if (is_array($aTable) && sizeof($aTable)) {
1765
                        // replace all the 'table.*' by their select of each column
1766
                        $what = preg_replace('/(^|,)\s*'.$tableName.'\.\*\s*($|,)/', '$1'.implode(',',$aTable).'$2', $what);
1767
                    }
1768
                }
1769
            }
1770
        }
1771
 
1772
        if ($this->getJoin()) {
1773
            // replace all 'column' by '$this->table.column' to prevent ambigious errors
1774
            $metadata = $this->metadata();
1775
            if (is_array($metadata)) {
1776
                foreach ($metadata as $aCol => $x) {
1777
                    // handle ',id as xid,MAX(id),id' etc.
1778
// FIXXME do this better!!!
1779
                    $what = preg_replace(  "/(^|,|\()(\s*)$aCol(\)|\s|,|as|$)/i",
1780
                                            // $2 is actually just to keep the spaces, is not really
1781
                                            // necessary, but this way the test works independent of this functionality here
1782
                                            "$1$2{$this->table}.$aCol$3",
1783
                                            $what);
1784
                }
1785
            }
1786
            // replace all 'joinedTable.columnName' by '_joinedTable_columnName'
1787
            // this actually only has an effect if there was no 'table.*' for 'table'
1788
            // if that was there, then it has already been done before
1789
            foreach ($this->getJoin('tables') as $aTable) {
1790
                if ($meta = $this->metadata($aTable)) {
1791
                    foreach ($meta as $aCol=>$x) {
1792
                        // dont put the 'AS' behind it if there is already one
1793
                        if (preg_match("/$aTable.$aCol\s*as/i",$what)) {
1794
                            continue;
1795
                        }
1796
                        // this covers a ' table.colName ' surrounded by spaces, and replaces it by ' table.colName AS _table_colName'
1797
                        $what = preg_replace('/\s'.$aTable.'.'.$aCol.'\s/', " $aTable.$aCol AS _".$this->getTableShortName($aTable)."_$aCol ", $what);
1798
                        // replace also the column names which are behind a ','
1799
                        // and do this also if the aCol is at the end that's what the $ is for
1800
                        $what = preg_replace('/,\s*'.$aTable.'.'.$aCol.'(,|\s|$)/', ",$aTable.$aCol AS _".$this->getTableShortName($aTable)."_$aCol$1", $what);
1801
                        // replace if colName is first and possibly also if at the beginning of the where-string
1802
                        $what = preg_replace('/(^\s*|\s+)'.$aTable.'.'.$aCol.'\s*,/', "$1$aTable.$aCol AS _".$this->getTableShortName($aTable)."_$aCol,", $what);
1803
                    }
1804
                }
1805
            }
1806
        }
1807
        return $what;
1808
    }
1809
 
1810
    // }}}
1811
    // {{{ _buildWhere()
1812
 
1813
    /**
1814
     * Build WHERE clause
1815
     *
1816
     * @param  string $where WHERE clause
1817
     * @return string $where WHERE clause after processing
1818
     * @access private
1819
     */
1820
    function _buildWhere($where='')
1821
    {
1822
        $where = trim($where);
1823
        $originalWhere = $this->getWhere();
1824
        if ($originalWhere) {
1825
            if (!empty($where)) {
1826
                $where = $originalWhere.' AND '.$where;
1827
            } else {
1828
                $where = $originalWhere;
1829
            }
1830
        }
1831
        $where = trim($where);
1832
 
1833
        if ($join = $this->getJoin()) {     // is join set?
1834
            // only those where conditions in the default-join have to be added here
1835
            // left-join conditions are added behind 'ON', the '_buildJoin()' does that
1836
            if (isset($join['default']) && count($join['default'])) {
1837
                // we have to add this join-where clause here
1838
                // since at least in mysql a query like: select * from tableX JOIN tableY ON ...
1839
                // doesnt work, may be that's even SQL-standard...
1840
                if (!empty($where)) {
1841
                    $where = implode(' AND ', $join['default']).' AND '.$where;
1842
                } else {
1843
                    $where = implode(' AND ', $join['default']);
1844
                }
1845
            }
1846
            // replace the _TABLENAME_COLUMNNAME by TABLENAME.COLUMNNAME
1847
            // since oracle doesnt work with the _TABLENAME_COLUMNNAME which i think is strange
1848
// FIXXME i think this should become deprecated since the setWhere should not be used like this: '_table_column' but 'table.column'
1849
            $regExp = '/_('.implode('|', $this->getJoin('tables')).')_([^\s]+)/';
1850
            $where = preg_replace($regExp, '$1.$2', $where);
1851
            // add the table name before any column that has no table prefix
1852
            // since this might cause "unambigious column" errors
1853
            if ($meta = $this->metadata()) {
1854
                foreach ($meta as $aCol => $x) {
1855
                    // this covers the LIKE,IN stuff: 'name LIKE "%you%"'  'id IN (2,3,4,5)'
1856
                    $where = preg_replace('/\s'.$aCol.'\s/', " {$this->table}.$aCol ", $where);
1857
                    // replace also the column names which are behind a '='
1858
                    // and do this also if the aCol is at the end of the where clause
1859
                    // that's what the $ is for
1860
                    $where = preg_replace('/([=<>])\s*'.$aCol.'(\s|$)/', "$1{$this->table}.$aCol ", $where);
1861
                    // replace if colName is first and possibly also if at the beginning of the where-string
1862
                    $where = preg_replace('/(^\s*|\s+)'.$aCol.'\s*([=<>])/', "$1{$this->table}.$aCol$2", $where);
1863
                }
1864
            }
1865
        }
1866
        return $where;
1867
    }
1868
 
1869
    // }}}
1870
    // {{{ _buildOrder()
1871
 
1872
    /**
1873
     *
1874
     *
1875
     * @version    2002/07/11
1876
     * @access     public
1877
     * @author     Wolfram Kriesing <wk@visionp.de>
1878
     * @param
1879
     * @return
1880
     */
1881
    function _buildOrder()
1882
    {
1883
        $order = $this->getOrder();
1884
        // replace 'column' by '$this->table.column' if the column is defined for $this->table
1885
        if ($meta = $this->metadata()) {
1886
            foreach ($meta as $aCol=>$x) {
1887
                $order = preg_replace('/(^\s*|\s+|,)'.$aCol.'\s*(,)?/U', "$1{$this->table}.$aCol$2", $order);
1888
            }
1889
        }
1890
        return $order;
1891
    }
1892
 
1893
    // }}}
1894
    // {{{ _buildGroup()
1895
 
1896
    /**
1897
     *   Build the group-clause, replace 'column' by 'table.column'.
1898
     *
1899
     * @access public
1900
     * @param void
1901
     * @return string the rendered group clause
1902
     */
1903
    function _buildGroup()
1904
    {
1905
        $group = $this->getGroup();
1906
        // replace 'column' by '$this->table.column' if the column is defined for $this->table
1907
        if ($meta = $this->metadata()) {
1908
            foreach ($meta as $aCol => $x) {
1909
                $group = preg_replace('/(^\s*|\s+|,)'.$aCol.'\s*(,)?/U', "$1{$this->table}.$aCol$2", $group);
1910
            }
1911
        }
1912
        return $group;
1913
    }
1914
 
1915
    // }}}
1916
    // {{{ _buildHaving()
1917
 
1918
    /**
1919
     *
1920
     * @version    2003/06/05
1921
     * @access     public
1922
     * @author     Johannes Schaefer <johnschaefer@gmx.de>
1923
     * @param
1924
     * @return string the having clause
1925
     */
1926
    function _buildHaving()
1927
    {
1928
        $having = $this->getHaving();
1929
        // replace 'column' by '$this->table.column' if the column is defined for $this->table
1930
        if ($meta = $this->metadata()) {
1931
            foreach ($meta as $aCol => $x) {
1932
                $having = preg_replace('/(^\s*|\s+|,)'.$aCol.'\s*(,)?/U',"$1{$this->table}.$aCol$2",$having);
1933
            }
1934
        }
1935
        return $having;
1936
    }
1937
 
1938
    // }}}
1939
    // {{{ _buildHaving()
1940
 
1941
    /**
1942
     *
1943
     * @version    2002/07/11
1944
     * @access     public
1945
     * @author     Wolfram Kriesing <wk@visionp.de>
1946
     * @param      array   this array contains the elements of the query,
1947
     *                       indexed by their key, which are: 'select','from','where', etc.
1948
     * @param      boolean whether this method is called via getCount() or not.
1949
     * @return
1950
     */
1951
    function _buildSelectQuery($query=array(), $isCalledViaGetCount = false)
1952
    {
1953
/*FIXXXME finish this
1954
        $cacheKey = md5(serialize(????));
1955
        if (isset($this->_queryCache[$cacheKey])) {
1956
            $this->_errorLog('using cached query',__LINE__);
1957
            return $this->_queryCache[$cacheKey];
1958
        }
1959
*/
1960
        $where = isset($query['where']) ? $query['where'] : $this->_buildWhere();
1961
        if ($where) {
1962
            $where = 'WHERE '.$where;
1963
        }
1964
        $order = isset($query['order']) ? $query['order'] : $this->_buildOrder();
1965
        if ($order) {
1966
            $order = 'ORDER BY '.$order;
1967
        }
1968
        $group = isset($query['group']) ? $query['group'] : $this->_buildGroup();
1969
        if ($group) {
1970
            $group = 'GROUP BY '.$group;
1971
        }
1972
        $having = isset($query['having']) ? $query['having'] : $this->_buildHaving();
1973
        if ($having) {
1974
            $having = 'HAVING '.$having;
1975
        }
1976
        $queryString = sprintf( 'SELECT %s FROM %s %s %s %s %s',
1977
                                isset($query['select']) ? $query['select'] : $this->_buildSelect(),
1978
                                isset($query['from']) ? $query['from'] : $this->_buildFrom(),
1979
                                $where,
1980
                                $group,
1981
                                $having,
1982
                                $order
1983
                                );
1984
        // $query['limit'] has preference!
1985
        $limit = isset($query['limit']) ? $query['limit'] : $this->_limit;
1986
        if (!$isCalledViaGetCount && @$limit[1]) {    // is there a count set?
1987
            $queryString=$this->db->modifyLimitQuery($queryString,$limit[0],$limit[1]);
1988
            if (DB::isError($queryString)) {
1989
                $this->_errorSet('DB_QueryTool::db::modifyLimitQuery failed '.$queryString->getMessage());
1990
                $this->_errorLog($queryString->getUserInfo());
1991
                return false;
1992
            }
1993
        }
1994
//        $this->_queryCache[$cacheKey] = $queryString;
1995
        return $queryString;
1996
    }
1997
 
1998
    // }}}
1999
    // {{{ _buildUpdateQuery()
2000
 
2001
    /**
2002
     * this simply builds an update query.
2003
     *
2004
     * @param  array   the parameter array might contain the following indexes
2005
     *         'where'     the where clause to be added, i.e.
2006
     *                     UPDATE table SET x=1 WHERE y=0
2007
     *                     here the 'where' part simply would be 'y=0'
2008
     *         'set'       the actual data to be updated
2009
     *                     in the example above, that would be 'x=1'
2010
     * @return string the resulting query
2011
     */
2012
    function _buildUpdateQuery($query=array())
2013
    {
2014
        $where = isset($query['where']) ? $query['where'] : $this->_buildWhere();
2015
        if ($where) {
2016
            $where = 'WHERE '.$where;
2017
        }
2018
 
2019
        $updateString = sprintf('UPDATE %s SET %s %s',
2020
                                $this->table,
2021
                                $query['set'],
2022
                                $where
2023
                            );
2024
        return $updateString;
2025
    }
2026
 
2027
    // }}}
2028
    // {{{ execute()
2029
 
2030
    /**
2031
     *
2032
     * @version    2002/07/11
2033
     * @access     public
2034
     * @author     Wolfram Kriesing <wk@visionp.de>
2035
     * @param
2036
     * @param      string  query method
2037
     * @return     boolean
2038
     */
2039
    function execute($query=null, $method='getAll')
2040
    {
2041
        $this->writeLog();
2042
        if (is_null($query)) {
2043
            $query = $this->_buildSelectQuery();
2044
        }
2045
        $this->writeLog('query built: '.$query);
2046
 
2047
// FIXXME on ORACLE this doesnt work, since we return joined columns as _TABLE_COLNAME and the _ in front
2048
// doesnt work on oracle, add a letter before it!!!
2049
        $this->_lastQuery = $query;
2050
 
2051
        $this->debug($query);
2052
        $this->writeLog('start query');
2053
        if (DB::isError($res = $this->db->$method($query))) {
2054
            $this->writeLog('end query (failed)');
2055
            if ($this->getOption('verbose')) {
2056
                $this->_errorSet($res->getMessage());
2057
            } else {
2058
                $this->_errorLog($res->getMessage());
2059
            }
2060
            $this->_errorLog($res->getUserInfo(), __LINE__);
2061
            return false;
2062
        } else {
2063
            $this->writeLog('end query');
2064
        }
2065
        $res = $this->_makeIndexed($res);
2066
        return $res;
2067
    }
2068
 
2069
    // }}}
2070
    // {{{ writeLog()
2071
 
2072
    /**
2073
     *   Write events to the logfile.
2074
     *
2075
     *   It does some additional work, like time measuring etc. to
2076
     *   see some additional info
2077
     *
2078
     */
2079
    function writeLog($text='START')
2080
    {
2081
//its still really a quicky.... 'refactor' (nice word) that
2082
        if (!isset($this->options['logfile'])) {
2083
            return;
2084
        }
2085
 
2086
        include_once 'Log.php';
2087
        if (!class_exists('Log')) {
2088
            return;
2089
        }
2090
        if (!$this->_logObject) {
2091
            $this->_logObject =& Log::factory('file', $this->options['logfile']);
2092
        }
2093
 
2094
        if ($text==='start query' || $text==='end query') {
2095
            $bytesSent = $this->db->getAll("SHOW STATUS like 'Bytes_sent'");
2096
            $bytesSent = $bytesSent[0]['Value'];
2097
        }
2098
        if ($text==='START') {
2099
            $startTime = split(' ', microtime());
2100
            $this->_logData['startTime'] = $startTime[1] + $startTime[0];
2101
        }
2102
        if ($text==='start query') {
2103
            $this->_logData['startBytesSent'] = $bytesSent;
2104
            $startTime = split(' ', microtime());
2105
            $this->_logData['startQueryTime'] = $startTime[1] + $startTime[0];
2106
            return;
2107
        }
2108
        if ($text==='end query') {
2109
            $text .= ' result size: '.((int)$bytesSent-(int)$this->_logData['startBytesSent']).' bytes';
2110
            $endTime = split(' ', microtime());
2111
            $endTime = $endTime[1] + $endTime[0];
2112
            $text .= ', took: '.(($endTime - $this->_logData['startQueryTime'])).' seconds';
2113
        }
2114
        if (strpos($text, 'query built')===0) {
2115
            $endTime = split(' ', microtime());
2116
            $endTime = $endTime[1] + $endTime[0];
2117
            $this->writeLog('query building took: '.(($endTime - $this->_logData['startTime'])).' seconds');
2118
        }
2119
        $this->_logObject->log($text);
2120
 
2121
        if (strpos($text, 'end query')===0) {
2122
            $endTime = split(' ', microtime());
2123
            $endTime = $endTime[1] + $endTime[0];
2124
            $text = 'time over all: '.(($endTime - $this->_logData['startTime'])).' seconds';
2125
            $this->_logObject->log($text);
2126
        }
2127
    }
2128
 
2129
    // }}}
2130
    // {{{ returnResult()
2131
 
2132
    /**
2133
     * Return the chosen result type
2134
     *
2135
     * @version    2004/04/28
2136
     * @access     public
2137
     * @param object reference
2138
     * @return mixed
2139
     */
2140
    function returnResult(&$result)
2141
    {
2142
        if ($this->_resultType == 'none') {
2143
            return $result;
2144
        }
2145
        if ($result === false) {
2146
            return false;
2147
        }
2148
        //what about allowing other (custom) result types?
2149
        switch (strtolower($this->_resultType)) {
2150
            case 'object':  return new DB_QueryTool_Result_Object($result);
2151
            case 'array':
2152
            default:        return new DB_QueryTool_Result($result);
2153
        }
2154
    }
2155
 
2156
    // }}}
2157
    // {{{ _makeIndexed()
2158
 
2159
    /**
2160
     *
2161
     * @version    2002/07/11
2162
     * @access     public
2163
     * @author     Wolfram Kriesing <wk@visionp.de>
2164
     * @param      mixed
2165
     * @return     mixed
2166
     */
2167
    function &_makeIndexed(&$data)
2168
    {
2169
        // we can only return an indexed result if the result has a number of columns
2170
        if (is_array($data) && sizeof($data) && $key = $this->getIndex()) {
2171
            // build the string to evaluate which might be made up out of multiple indexes of a result-row
2172
            $evalString = '$val[\''.implode('\'].\',\'.$val[\'',explode(',',$key)).'\']';   //"
2173
 
2174
            $indexedData = array();
2175
//FIXXME actually we also need to check ONCE if $val is an array, so to say if $data is 2-dimensional
2176
            foreach ($data as $val) {
2177
                eval("\$keyValue = $evalString;");  // get the actual real (string-)key (string if multiple cols are used as index)
2178
                $indexedData[$keyValue] = $val;
2179
            }
2180
            unset($data);
2181
            return $indexedData;
2182
        }
2183
 
2184
        return $data;
2185
    }
2186
 
2187
    // }}}
2188
    // {{{ setIndex()
2189
 
2190
    /**
2191
     * format the result to be indexed by $key
2192
     * NOTE: be careful, when using this you should be aware, that if you
2193
     * use an index which's value appears multiple times you may loose data
2194
     * since a key cant exist multiple times!!
2195
     * the result for a result to be indexed by a key(=columnName)
2196
     * (i.e. 'relationtoMe') which's values are 'brother' and 'sister'
2197
     * or alike normally returns this:
2198
     *     $res['brother'] = array('name'=>'xxx')
2199
     *     $res['sister'] = array('name'=>'xxx')
2200
     * but if the column 'relationtoMe' contains multiple entries for 'brother'
2201
     * then the returned dataset will only contain one brother, since the
2202
     * value from the column 'relationtoMe' is used
2203
     * and which 'brother' you get depends on a lot of things, like the sortorder,
2204
     * how the db saves the data, and whatever else
2205
     *
2206
     * you can also set indexes which depend on 2 columns, simply pass the parameters like
2207
     * 'table1.id,table2.id' it will be used as a string for indexing the result
2208
     * and the index will be built using the 2 values given, so a possible
2209
     * index might be '1,2' or '2108,29389' this way you can access data which
2210
     * have 2 primary keys. Be sure to remember that the index is a string!
2211
     *
2212
     * @version    2002/07/11
2213
     * @access     public
2214
     * @author     Wolfram Kriesing <wk@visionp.de>
2215
     * @param
2216
     * @return
2217
     */
2218
    function setIndex($key=null)
2219
    {
2220
        if ($this->getJoin()) { // is join set?
2221
            // replace TABLENAME.COLUMNNAME by _TABLENAME_COLUMNNAME
2222
            // since this is only the result-keys can be used for indexing :-)
2223
            $regExp = '/('.implode('|', $this->getJoin('tables')).')\.([^\s]+)/';
2224
            $key = preg_replace($regExp, '_$1_$2', $key);
2225
 
2226
            // remove the table name if it is in front of '<$this->table>.columnname'
2227
            // since the key doesnt contain it neither
2228
            if ($meta = $this->metadata()) {
2229
                foreach ($meta as $aCol => $x) {
2230
                    $key = preg_replace('/'.$this->table.'\.'.$aCol.'/', $aCol, $key);
2231
                }
2232
            }
2233
        }
2234
        $this->_index = $key;
2235
    }
2236
 
2237
    // }}}
2238
    // {{{ getIndex()
2239
 
2240
    /**
2241
     *
2242
     * @version    2002/07/11
2243
     * @access     public
2244
     * @author     Wolfram Kriesing <wk@visionp.de>
2245
     * @param
2246
     * @return
2247
     */
2248
    function getIndex()
2249
    {
2250
        return $this->_index;
2251
    }
2252
 
2253
    // }}}
2254
    // {{{ useResult()
2255
 
2256
    /**
2257
     * Choose the type of the returned result
2258
     *
2259
     * @version    2004/04/28
2260
     * @access     public
2261
     * @param string $type  ['array' | 'object' | 'none']
2262
     *             For BC reasons, $type=true is equal to 'array',
2263
     *             $type=false is equal to 'none'
2264
     */
2265
    function useResult($type='array')
2266
    {
2267
        if ($type === true) {
2268
            $type = 'array';
2269
        } elseif ($type === false) {
2270
            $type = 'none';
2271
        }
2272
        switch (strtolower($type)) {
2273
            case 'array':
2274
                $this->_resultType = 'array';
2275
                require_once 'DB/QueryTool/Result.php';
2276
                break;
2277
            case 'object':
2278
                $this->_resultType = 'object';
2279
                require_once 'DB/QueryTool/Result/Object.php';
2280
                break;
2281
            default:
2282
                $this->_resultType = 'none';
2283
        }
2284
    }
2285
 
2286
    // }}}
2287
    // {{{ setErrorCallback()
2288
 
2289
    /**
2290
     * set both callbacks
2291
     * @param string
2292
     */
2293
    function setErrorCallback($param='')
2294
    {
2295
        $this->setErrorLogCallback($param);
2296
        $this->setErrorSetCallback($param);
2297
    }
2298
 
2299
    // }}}
2300
    // {{{ setErrorLogCallback()
2301
 
2302
    /**
2303
     * @param string
2304
     */
2305
    function setErrorLogCallback($param='')
2306
    {
2307
        $errorLogCallback = &PEAR::getStaticProperty('DB_QueryTool','_errorLogCallback');
2308
        $errorLogCallback = $param;
2309
    }
2310
 
2311
    // }}}
2312
    // {{{ setErrorSetCallback()
2313
 
2314
    /**
2315
     * @param string
2316
     */
2317
    function setErrorSetCallback($param='')
2318
    {
2319
        $errorSetCallback = &PEAR::getStaticProperty('DB_QueryTool','_errorSetCallback');
2320
        $errorSetCallback = $param;
2321
    }
2322
 
2323
    // }}}
2324
    // {{{ _errorLog()
2325
 
2326
    /**
2327
     * sets error log and adds additional info
2328
     *
2329
     * @version    2002/04/16
2330
     * @access     public
2331
     * @author     Wolfram Kriesing <wk@visionp.de>
2332
     * @param      string  the actual message, first word should always be the method name,
2333
     *                     to build the message like this: className::methodname
2334
     * @param      integer the line number
2335
     */
2336
    function _errorLog($msg, $line='unknown')
2337
    {
2338
        $this->_errorHandler('log', $msg, $line);
2339
/*
2340
        if ($this->getOption('verbose') == true)
2341
        {
2342
            $this->_errorLog(get_class($this)."::$msg ($line)");
2343
            return;
2344
        }
2345
 
2346
        if ($this->_errorLogCallback)
2347
            call_user_func($this->_errorLogCallback, $msg);
2348
*/
2349
    }
2350
 
2351
    // }}}
2352
    // {{{ _errorSet()
2353
 
2354
    /**
2355
     * @param      string
2356
     * @param      string
2357
     */
2358
    function _errorSet($msg, $line='unknown')
2359
    {
2360
        $this->_errorHandler('set', $msg, $line);
2361
    }
2362
 
2363
    // }}}
2364
    // {{{ _errorHandler()
2365
 
2366
    /**
2367
     * @param
2368
     * @param      string
2369
     * @param      string
2370
     */
2371
    function _errorHandler($logOrSet, $msg, $line='unknown')
2372
    {
2373
/* what did i do this for?
2374
        if ($this->getOption('verbose') == true)
2375
        {
2376
            $this->_errorHandler($logOrSet, get_class($this)."::$msg ($line)");
2377
            return;
2378
        }
2379
*/
2380
 
2381
        $msg = get_class($this)."::$msg ($line)";
2382
 
2383
        $logOrSet = ucfirst($logOrSet);
2384
        $callback = &PEAR::getStaticProperty('DB_QueryTool','_error'.$logOrSet.'Callback');
2385
        //var_dump($callback);
2386
        //if ($callback)
2387
        //    call_user_func($callback, $msg);
2388
//        else
2389
//          ?????
2390
 
2391
    }
2392
 
2393
    // }}}
2394
}
2395
?>