Db.php 12.7 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
<?php
/***************************************************************
 *  Copyright notice
 *
 *  (c) 2010 Glowbase GmbH <support@glowbase.com>
 *  All rights reserved
 *
 *  This script is part of the TYPO3 project. The TYPO3 project is
 *  free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  The GNU General Public License can be found at
 *  http://www.gnu.org/copyleft/gpl.html.
 *
 *  This script is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  This copyright notice MUST APPEAR in all copies of the script!
 ***************************************************************/

namespace qfq;

//use qfq;

require_once(__DIR__ . '/Define.php');
require_once(__DIR__ . '/Error.php');
require_once(__DIR__ . '/Log.php');


class Db {

    public $t3_typo_db_host = "";
    /**
     * @var string
     */
    private $lastUsedDB = "";
    /**
     * @var array
     */
    private $arrDB = array();
    /**
     * @var
     */
    private $t3_typo_db_username, $t3_typo_db, $t3_typo_db_password;
    /**
     * @var Log
     */
    private $log;

    // Emulate global variable: will be set much earlier in other functions. Will be shown in error messages.
    private $fr_error;


    /**
     * Constructor:
     *
     * @param    Log   fully created for logging.
     *
     */

    public function __construct($log) {
        // CR 25.4.11: require_once does not work here. No idea why
        require(PATH_typo3conf . 'localconf.php');
        $this->t3_typo_db_host = $typo_db_host;
        $this->t3_typo_db_username = $typo_db_username;
        $this->t3_typo_db = $typo_db;
        $this->t3_typo_db_password = $typo_db_password;

        $this->log = $log;
    }

    /**
     * Set Array fr_error: setter function to set most recent values, especially fr_erro['row'].
     * Will be shown in error messages.
     *
     * @param array $fr_error uid, pid, row, column_idx, full_level
     */
    public function set_fr_error(array $fr_error) {
        $this->fr_error = $fr_error;
    }

    /**
     * doQueryKeys: See doQuery
     *                Difference: fake Array for $keys
     *
     * @param string $dbAlias
     * @param string $sql
     * @param array $result
     * @param string string $expect
     * @param string $merge
     * @return bool
     * @throws CodeReportException
     * @throws SqlReportException
     * @throws SyntaxReportException
     */
100
    public function doQuery($dbAlias, $sql, array &$result, $expect = ROW_REGULAR, $merge = MERGE_NONE) {
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
        return ($this->doQueryKeys($dbAlias, $sql, $result, $fake, $expect, $merge, MYSQL_ASSOC));
    }

    /**
     * doQueryKeys: fires a show, select, insert, update or delete and collects result.
     *            insert, update and delete will produce a log entry.
     *            If: $expect==EXPECT_SQL_OR_STRING, '$sql' can be anything which won't be fired if it's not a SQL statement.
     *
     * @param string $dbAlias Name of Database to be used.
     * @param string $sql Select Query
     * @param array $result content depends on $sql.
     *                                    $sql='insert ...': mysql_last_insert_id will be returned in $result.
     *                                    $sql='update ...' or 'delete ----': mysql_affected_rows will be returned in $result.
     *                                $sql='select ...': all selected rows will be returned in $result.
     *                                $result will be formatted like specified in $merge.
     *                                    Attention: with EXPECT_1|EXPECT_0_1 '$result' is a one dimensional array, else a two dimensional array.
     * @param array $keys
     * @param string $expect
     * @param string $merge Applies different modes of merging - MERGE_NONE, MERGE_ROW, MERGE_ALL
     * @param int $arrayMode
     * @return bool                     true: all ok
     *                                  false:    a) Number of rows don't match $expect
     *                                        b) $expect==EXPECT_SQL_OR_STRING and $sql is not an SQL expression (instead it's a regular string) - this is not bad, just to indicate that there was no query.
     * @throws CodeReportException
     * @throws SqlReportException
     * @throws SyntaxReportException
     */
128
    public function doQueryKeys($dbAlias, $sql, array &$result, array &$keys, $expect = ROW_REGULAR, $merge = MERGE_NONE, $arrayMode = MYSQL_NUM) {
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
        $result = "";
        $tmp = "";
        $action = "";

        $this->selectDB($dbAlias);

        if ($this->fr_error["debug_level"] >= DEBUG_SQL) {
            // T3 function: debug()
//            debug(array('SQL' => $sql));
        }

        // Extract first parameter to check if it is a SQL statement
        $tmp = explode(" ", trim($sql), 2);
        $action = strtolower($tmp[0]);
        switch ($action) {
            case "show"  :
            case "select":
            case "insert":
            case "update":
            case "delete":
                break;  // SQL Statement: go further
            default:
                if ($expect == EXPECT_SQL_OR_STRING) {
                    $result = $sql;
153
                    return (false);    // nothing bad, just to indicate $sql was not a SQL statement.
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
                } else
                    throw new SyntaxReportException ("Unexpected SQL Statement: '$action'", "", __FILE__, __LINE__, array("DB:$dbAlias", "SQL:$sql"), $this->fr_error);
        }

        // Fire SQL statement
        if (!($res = mysql_query($sql))) {
            // Escape query if in AJAX mode
            $sql = (isset($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest') ? addslashes($sql) : $sql;
            throw new SqlReportException ("Did not get result for query.", $sql, __FILE__, __LINE__, $this->fr_error);
        }


        switch ($action) {
            case "show"  :
            case "select":
                $action = QUERY; // aggregate 'SLELECT' and 'SHOW' and ...
                $num_rows = mysql_num_rows($res);
                break;

            case "insert":
                $num_rows = mysql_affected_rows();
                $result = mysql_insert_id();
                break;
            case "update":
            case "delete":
                $num_rows = mysql_affected_rows();
                $result = $num_rows;
                break;

            default:
                throw new CodeReportException ("This error should be catched 20 lines above.", __FILE__, __LINE__);  // can't be happen, should already be detected earlier.
        }

        // Logging
        if ($action != QUERY) {
            // Not a query: write log and go home.
            $this->log->log_sql('db', '', $num_rows, $result, $sql);
            return (TRUE);
        } else {
            // Logging if localconf log_level >=D2
            $this->log->log_do('error', 'D2', '-', $sql);
        }

        // Check $expect against real result.
        switch ($expect) {
199
            case ROW_EXPECT_0:
200
201
                return ($num_rows == 0);
                break;
202
            case ROW_EXPECT_1:
203
                if ($num_rows != 1) return (false);
204
                break;
205
            case ROW_EXPECT_0_1:
206
                if ($num_rows > 1) return (false);
207
                break;
208
209
210
//            case ROW_EXPECT_GE_0:
//                break;
            case ROW_EXPECT_GE_1:
211
                if ($num_rows == 0) return (false);
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
                break;
            case EXPECT_SQL_OR_STRING:
                break;
            default:
                throw new CodeReportException ("Unknown 'expect' qualifier: $expect", __FILE__, __LINE__);
                break;
        }

        // Preparation to fetch all rows
        $tmp = "";
        $fieldCount = 0;

        // Fetch all rows:
        while ($row = mysql_fetch_array($res, $arrayMode)) {
            foreach ($row as $key => $value) {
                $row[$key] = stripslashes($value);
            }

            switch ($merge) {
                case MERGE_NONE:
                    $tmp[] = $row;
                    break;
                case MERGE_ROW:
                    $tmp[] = implode($row);
                    break;
                case MERGE_ALL:
                    $tmp .= implode($row);
                    break;
                default:
                    throw new CodeReportException ("Unknown 'merge' qualifier: $merge", __FILE__, __LINE__);
                    break;
            }
        }

        // Collect 'keys'
        if ($merge == MERGE_NONE) {
            $keys = array();
            $numberfields = mysql_num_fields($res);

            for ($i = 0; $i < $numberfields; $i++) {
                $keys[] = mysql_field_name($res, $i);
            }
        }

        // adjust Result Array: one or two dimensions.
        switch ($expect) {
258
            case ROW_EXPECT_0:
259
                break;
260
261
            case ROW_EXPECT_1:
            case ROW_EXPECT_0_1:
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
            case EXPECT_SQL_OR_STRING:
                $result = $tmp[0];
                break;
            default:
                $result = $tmp;
        }

        if ($merge == MERGE_ALL)
            $result = $tmp;

        mysql_free_result($res);

        return (TRUE);
    } // doQueryKeys()

    /**
     * select DB
     *
     * @param    string $dbAlias : Name of the dbname
     *
     * @return    bool        TRUE if ok, else exception.
     */
    public function selectDB($dbAlias) {

        if (!$dbAlias)
            throw new CodeReportException ("Failed: empty dbAlias", __FILE__, __LINE__);

        // If the db is still selected: do nothing.
        if ($dbAlias == $this->lastUsedDB)
            return true;

        // if the db is already open - just select it.
        if (isset($this->arrDB[$dbAlias]['link'])) {
            if (!mysql_select_db($this->arrDB[$dbAlias]['db']))
                throw new SqlReportException ("Failed: mysql_select_db($this->arrDB[$dbAlias]['db'])", "", __FILE__, __LINE__, $this->fr_error);
            return true;
        }

        $this->openDB($dbAlias);

        return true;
    } // openDB()

    /**
     * Open specified DB
     *
     * @param string $dbAlias Name of database to be opened
     * @throws SqlReportException
     */
    public function openDB($dbAlias) {
        // TYPO3 globale Variablen
// 		global $typo_db_host,$typo_db_username,$typo_db,$typo_db_password;
//		Du sollst kein global verwenden!!

        if ($dbAlias == T3) {
            $host = $this->t3_typo_db_host;
            $username = $this->t3_typo_db_username;
            $db = $this->t3_typo_db;
            $password = $this->t3_typo_db_password;
        } else {
//			require(PATH_typo3conf.'ext/formreport/ext_localconf.php');
            $host = $GLOBALS['TYPO3_CONF_VARS'][FORMREPORT][$dbAlias]['host'];
            $username = $GLOBALS['TYPO3_CONF_VARS'][FORMREPORT][$dbAlias]['username'];
            $db = $GLOBALS['TYPO3_CONF_VARS'][FORMREPORT][$dbAlias]['name'];
            $password = $GLOBALS['TYPO3_CONF_VARS'][FORMREPORT][$dbAlias]['password'];

        }

        // If 't3' is specified or the custom DB is not fully specified, take credentials from localconf.php
#		$host     = $host     ? $host 		: $typo_db_host;
#		$username = $username ? $username	: $typo_db_username;
#		$db       = $db       ? $db 		: $typo_db;
#		$password = $password ? $password 	: $typo_db_password;


        // MySQL Connect
        if (!($link = mysql_connect($host, $username, $password)))
            throw new SqlReportException ("mysql_connect($host, $username)", "", __FILE__, __LINE__, $this->fr_error);

        // Set connection charset
        if (!mysql_set_charset('utf8', $link))
            throw new SqlReportException ("mysql_set_charset('utf8', $link)", "", __FILE__, __LINE__, $this->fr_error);

        // MySQL select
        if (!mysql_select_db($db, $link))
            throw new SqlReportException ("mysql_select_db($db)", "", __FILE__, __LINE__, $this->fr_error);

        // Remember that this DB has been opened.
        $this->arrDB[$dbAlias]['link'] = $link;
        $this->arrDB[$dbAlias]['db'] = $db;

        // Remember the new DB as 'last used'
        $this->lastUsedDB = $dbAlias;

    } // selectDB()

    /**
     * closeAllDB
     *
     * @return    void
     */
    public function closeAllDB() {

        foreach ($this->arrDB as $key => $value) {
            if ($key != T3) {
                mysql_close($value);
                $arrDB[$key] = null;
            }
        }

    } // closeAllDB()
}