← Back to branch summary

~hexmode/+junk/main

« back to all changes in this revision

Viewing changes to install-files/apps/sqlitemanager1.2.0/include/sql.class.php

  • Committer: Mark A. Hershberger
  • Date: 2008-01-05 19:38:56 UTC
  • Revision ID: hershberger@spawn-xp-20080105193856-6rnzgwa4nehue3qj
initial commit

Show diffs side-by-side

added added

removed removed

Lines of Context:
install-files/apps/sqlitemanager1.2.0/include/sql.class.php
 
1
<?php
 
2
/**
 
3
* Web based SQLite management
 
4
* @package SQLiteManager
 
5
* @author Fr�d�ric HENNINOT
 
6
* @version $Id: sql.class.php,v 1.62 2006/04/14 15:16:52 freddy78 Exp $ $Revision: 1.62 $
 
7
*/
 
8
 
 
9
class sql {
 
10
 
 
11
        /**
 
12
        * @access public
 
13
        * @var objet
 
14
        * Class instance of database connection
 
15
        */
 
16
        var $connId;
 
17
 
 
18
        /**
 
19
        * @access private
 
20
        * @var string
 
21
        * current query
 
22
        */
 
23
        var $query;
 
24
 
 
25
        /**
 
26
        * @access private
 
27
        * @var bool
 
28
        * if true, all query are journalised into file
 
29
        */
 
30
        var $journalised;
 
31
 
 
32
        /**
 
33
        * @access private
 
34
        * @var string
 
35
        * filename where query are store when journalised
 
36
        */
 
37
        var $journalFile;
 
38
 
 
39
        /**
 
40
        * @access public
 
41
        * @var bool
 
42
        * true if query return data
 
43
        */
 
44
        var $withReturn;
 
45
 
 
46
        /**
 
47
        * @access public
 
48
        * @var bool
 
49
        * true if the current query content are multiple
 
50
        */
 
51
        var $multipleQuery;
 
52
 
 
53
        /**
 
54
        * Last query Execution Time (msec)
 
55
        *
 
56
        * @access public
 
57
        * @var integer
 
58
        */
 
59
        var $queryTime;
 
60
 
 
61
        /**
 
62
        * @access private
 
63
        * @var integer
 
64
        * Number lines has been changes by the current query
 
65
        */
 
66
        var $changesLine;
 
67
 
 
68
        /**
 
69
        * @access private
 
70
        * @var integer
 
71
        * Number of query into the curent multiple query
 
72
        */
 
73
        var $nbQuery;
 
74
 
 
75
        /**
 
76
        * @access private
 
77
        * @var bool
 
78
        * true if has an error in the current query
 
79
        */
 
80
        var $error;
 
81
 
 
82
        /**
 
83
        * @access private
 
84
        * @var integer
 
85
        * Error line number for multiple query
 
86
        */
 
87
        var $lineError;
 
88
 
 
89
        /**
 
90
        * @access private
 
91
        * @var string
 
92
        * SQLite error message
 
93
        */
 
94
        var $errorMessage;
 
95
 
 
96
        /**
 
97
        * @access private
 
98
        * @var string
 
99
        * SQLite query executed when error
 
100
        */
 
101
        var $errorQuery;
 
102
 
 
103
        /**
 
104
        * Class constructor
 
105
        *
 
106
        * @access public
 
107
        * @param object $handle instance of connection class
 
108
        * @param string $query
 
109
        * @param string $journal filename of the journalise file
 
110
        */
 
111
        function sql(&$handle, $query, $journal=''){
 
112
                $this->connId = $handle;
 
113
                $this->query = trim($query);
 
114
                if(eregi('^(select|explain|pragma)[[:space:]]', $this->query)) $this->withReturn = true;
 
115
                else $this->withReturn = false;
 
116
                if($journal != ''){
 
117
                        $this->journalised = true;
 
118
                        $this->journalFile = $journal;
 
119
                } else $this->journalised = false;
 
120
                return;
 
121
        }
 
122
 
 
123
        /**
 
124
        * Verify and exec query
 
125
        *
 
126
        * @access public
 
127
        */
 
128
        function verify($autocommit=true){
 
129
                if(is_resource($this->connId->connId) || is_object($this->connId->connId)){
 
130
                        if(!empty($GLOBALS['attachDbList'])){
 
131
                                // attachment of all database
 
132
                                foreach($GLOBALS['attachDbList'] as $attachDbId) {
 
133
                                        $attachQuery = 'ATTACH DATABASE '.quotes($GLOBALS['attachInfo'][$attachDbId]['location']).' AS '.quotes($GLOBALS['attachInfo'][$attachDbId]['name']).';';
 
134
                                        $this->execQuery($attachQuery);
 
135
                                }
 
136
                        }
 
137
                        if($this->query != ''){
 
138
                                $parsing =& new ParsingQuery($this->query, ((isset($_POST['sqltype']))? $_POST['sqltype'] : 1 ));
 
139
                                $tabQuery = $parsing -> convertQuery();
 
140
                                if(!is_array($tabQuery)){
 
141
                                        $this->multipleQuery = false;
 
142
                                        $this->query = $tabQuery;
 
143
                                        if(!$result = $this->execQuery($tabQuery)) {    
 
144
                                                $this->addChanges();
 
145
                                        }
 
146
                                        return $result;
 
147
                                } else {
 
148
                                        $time=0;
 
149
                                        $this->multipleQuery = true;
 
150
                                        $this->connId->connId->query('BEGIN TRANSACTION;');
 
151
                                        $error = false;
 
152
                                        $lineNum = 1;
 
153
                                        $this->changesLine = $queryNum = 0;
 
154
                                        foreach($tabQuery as $query){
 
155
                                                if(!empty($query) && substr(trim($query), 0, 1)!='#'){
 
156
                                                        if($this->_checkBeginQuery($query)){
 
157
                                                                $queryNum++;
 
158
                                                                if(isset($commitafter) && $commitafter){
 
159
                                                                        $this->connId->connId->query('COMMIT TRANSACTION;');
 
160
                                                                        $this->connId->connId->query('BEGIN TRANSACTION;');
 
161
                                                                        $commitafter=false;
 
162
                                                                }
 
163
                                                        }
 
164
                                                        if($this->_checkBeginQuery($query, 'CREATE|DROP') && !eregi('^create[[:space:]]database', $query)) {
 
165
                                                                if ($autocommit) $commitafter = true;
 
166
                                                        }
 
167
                                                        if($this->execQuery($query)){
 
168
                                                                $error = true;
 
169
                                                                $this->lineError[] = $lineNum;
 
170
                                                        }
 
171
                                                        $time += $this->queryTime;
 
172
                                                        $this->addChanges();
 
173
                                                        $lineNum++;
 
174
                                                }
 
175
                                                if($error) break;
 
176
                                        }
 
177
                                        if($error) {
 
178
                                                $this->connId->connId->query('ROLLBACK TRANSACTION;');
 
179
                                        } else {
 
180
                                                $this->connId->connId->query('COMMIT TRANSACTION;');
 
181
                                        }
 
182
                                        $this->error = $error;
 
183
                                        $this->withReturn = false;
 
184
                                        $this->nbQuery = $queryNum;
 
185
                                }
 
186
                        } else {
 
187
                                $this->error = true;
 
188
                                $this->errorMessage = $GLOBALS['traduct']->get(64);
 
189
                        }
 
190
                } else {
 
191
                        $this->error = true;
 
192
                        $this->errorMessage = $GLOBALS['traduct']->get(65);
 
193
                }
 
194
                $this->queryTime = (isset($time)?$time:0);
 
195
                return $this->error;
 
196
        }
 
197
        
 
198
        function addChanges() {
 
199
                $tempChanges = $this->connId->connId->changes();
 
200
                $this->changesLine += $tempChanges;
 
201
                return $tempChanges;
 
202
        }
 
203
 
 
204
        /**
 
205
        * Exec, manage error and journalised
 
206
        *
 
207
        * @access public
 
208
        * @param string $string query
 
209
        */
 
210
        function execQuery($query){
 
211
                $queryExec = $this->cleanup($query);
 
212
                $this->errorQuery = '';
 
213
                $this->queryLog($queryExec);
 
214
                if(!eregi('^create[[:space:]]database', $queryExec)){
 
215
                        $GLOBALS['phpSQLiteError'] = '';
 
216
                        set_error_handler('phpSQLiteErrorHandling');
 
217
                        if($this->connId->getResId($queryExec)){
 
218
                                $this->error = false;
 
219
                        } else {
 
220
                                $this->error = true;
 
221
                                $this->errorQuery = $queryExec;
 
222
                                $this->errorMessage = '<table style="color: red;"><tr><td>'.$GLOBALS['traduct']->get(9).' :</td><td>'.$this->connId->connId->getError().'</td></tr>';
 
223
                                if($GLOBALS['phpSQLiteError'] != '') $this->errorMessage .= '<tr><td>&nbsp;</td><td>'.$GLOBALS['phpSQLiteError'].'</td></tr>';
 
224
                                if(strstr($GLOBALS['phpSQLiteError'],'syntax error') && $this->multipleQuery)
 
225
                                        $this->errorMessage .= '<tr><td valign="top"><pre class="error_query">Query :</pre></td><td><pre class="error_query">'.htmlentities($this->errorQuery, ENT_NOQUOTES, $GLOBALS['charset']).'</pre></td></tr>';
 
226
                                $this->errorMessage .= '</table>';
 
227
                        }
 
228
                        $this->queryTime = $this->connId->queryTime;
 
229
                        restore_error_handler();
 
230
                } else {
 
231
                        // emulating 'CREATE DATABASE'
 
232
                        preg_match('/CREATE[[:space:]]DATABASE(.*)/i', $queryExec, $result);
 
233
                        $newDatabase = $result[1];
 
234
                        if(strrpos($newDatabase, ';')) $newDatabase = substr($newDatabase, 0, strrpos($newDatabase, ';'));
 
235
                        if(eregi('[[:space:]]as[[:space:]]', $newDatabase)){
 
236
                                preg_match('/(.*)[[:space:]]AS(.*)/i', $newDatabase, $result);
 
237
                                $newDatabaseName = trim($result[1]);
 
238
                                $newDatabaseFilename = trim($result[2]);
 
239
                        } else {
 
240
                                $newDatabaseName = $newDatabaseFilename = trim($newDatabase);
 
241
                        }
 
242
                        unset($GLOBALS['workDb']);
 
243
                        include_once INCLUDE_LIB.'SQLiteDbConnect.class.php';
 
244
                        $tempdir = dirname($newDatabaseFilename);
 
245
                        if($tempdir == '.') $newDatabaseFilename = DEFAULT_DB_PATH . $newDatabaseFilename;
 
246
                        $GLOBALS['workDb'] = &new SQLiteDbConnect($newDatabaseFilename);
 
247
                        $GLOBALS['workDb']->includeUDF();
 
248
                        $this->connId = $GLOBALS['workDb'];
 
249
 
 
250
                        $query = 'INSERT INTO database (name, location) VALUES ('.quotes($newDatabaseName).', '.quotes(DEFAULT_DB_PATH.$newDatabaseFilename).')';
 
251
                        if(!$GLOBALS['db']->query($query)) {
 
252
                                $error = true;
 
253
                                $this->errorQuery = $query;
 
254
                                $message .= '<li><span style="color: red; font-size: 11px">'.$GLOBALS['traduct']->get(100).'</span></li>';
 
255
                        } else {
 
256
                                if(DEBUG) $GLOBALS['dbsel'] = $GLOBALS['db']->last_insert_id();
 
257
                                else $GLOBALS['dbsel'] = @$GLOBALS['db']->last_insert_id();
 
258
                                echo "<script type=\"text/javascript\">parent.left.location='left.php?dbsel=".$GLOBALS['dbsel']."';</script>";
 
259
                        }
 
260
                }
 
261
                return $this->error;
 
262
        }
 
263
 
 
264
 
 
265
        /**
 
266
        * Cleanup POST query
 
267
        * and convert MySQL type into SQLite type
 
268
        *
 
269
        * @access public
 
270
        * @param array $data data table
 
271
        * @param string $width width of the end table (px or %)
 
272
        */
 
273
        function cleanup($query){
 
274
                $query = stripslashes($query);
 
275
                if(!isset($_POST['sqltype'])) $_POST['sqltype']=1;
 
276
                if($_POST['sqltype']==2){
 
277
                        $query = str_replace("\'", "''", $query);
 
278
                        $query = str_replace("\\\"", "\"\"", $query);
 
279
                }
 
280
                return $query;
 
281
        }
 
282
 
 
283
        /**
 
284
        * Manual query Form
 
285
        *
 
286
        * @access public
 
287
        * @param string $query
 
288
        */
 
289
        function getForm($query){
 
290
                //Mozilla textarea bigger than IE one
 
291
                $mozIE = (!strstr($_SERVER["HTTP_USER_AGENT"],'IE'));
 
292
          echo '<!-- sql.class.php : getForm() -->'."\n";
 
293
                echo '<div align="center">';
 
294
                echo '  <form name="sql" action="main.php?dbsel='.$GLOBALS['dbsel'].'&amp;table='.$GLOBALS['table'].'" method="POST" ENCTYPE="multipart/form-data" target="main">
 
295
                                <table class="Insert" cellspacing="0" cellpadding="0">
 
296
                                <thead>
 
297
                                        <tr>
 
298
                                                <td class="Browse" colspan="2">'.$GLOBALS['traduct']->get(66).'</b></td>
 
299
                                        </tr>
 
300
                                </thead>
 
301
                                <tr>
 
302
                                        <td class="DisplayQuery">
 
303
                                                <div>
 
304
                                                <textarea wrap="hard" name="DisplayQuery" cols="80" rows="'.(TEXAREA_NB_ROWS-$mozIE).'" onfocus="document.sql.DisplayQuery.select();">'.$this->cleanup($query).'</textarea>
 
305
                                        </div>
 
306
                                        <div align="left">'.$GLOBALS['traduct']->get(67).' :
 
307
                                                        <input type="file" size="35" class="file" name="sqlFile">       
 
308
                                                        <input type="hidden" name="action" value="sql">
 
309
                                                </div>
 
310
                                        </td>';
 
311
                if(!empty($GLOBALS['table'])) {
 
312
                        echo '<td align="center" style="padding-top: 1px;" valign="top">'.$this->GetColumnSelect().'</td>';
 
313
                }
 
314
                echo '  </tr>
 
315
                                </table>'."\n";
 
316
                echo '  <div style="padding:5px;" align="center">'.$GLOBALS['traduct']->get(68).'
 
317
                                        <input type="radio" name="sqltype" value=1 '.(((!isset($_POST['sqltype'])) || ($_POST['sqltype']==1))? 'checked="checked"' : '' ).'> - MySQL
 
318
                                <input type="radio" name="sqltype" value="2"'.(((isset($_POST['sqltype'])) && ($_POST['sqltype']==2))? ' checked="checked"' : '' ).'><br/>
 
319
                                        <input class="button" type="submit" value="'.$GLOBALS['traduct']->get(69).'">
 
320
                                </div>'."\n";
 
321
                echo '  </form>'."\n".'</div>';
 
322
        if($this->connId->connId->getVersion()!=3) {
 
323
            echo '<table class="Tip"><tr><th class="TipTitle">';
 
324
            echo $GLOBALS['traduct']->get(224);
 
325
            echo '</th></tr><tr><td class="TipBody">';
 
326
            echo "SELECT php('sprinf','%02d',MyColumn) FROM TABLE;</br>"."\n";
 
327
            echo '</td></tr></table>';
 
328
        }
 
329
    echo '</body>'."\n";
 
330
    echo '</html>';
 
331
        }
 
332
 
 
333
        /**
 
334
        * Verify if the result can be modify or deleted
 
335
        * if true, return the table name else return false
 
336
        *
 
337
        * @access public
 
338
        * @param string $query
 
339
        */
 
340
        function checkAccessResult($query){
 
341
                if(eregi('EXPLAIN|JOIN|GROUP[[:space:]]', $query)) return false;
 
342
                $match = 'WHERE|ORDER|LIMIT';
 
343
                if(eregi($match, $query)) preg_match('/FROM(.*)('.$match.')/i', $query, $result);
 
344
                else preg_match('/FROM(.*)/i', $query, $result);
 
345
                if(isset($result[1])) {
 
346
                        $listTable = trim($result[1]);
 
347
                        $posEnd = strrpos($listTable, ';');
 
348
                        if($posEnd) $listTable = substr($listTable, 0, $posEnd);
 
349
                } else $listTable = '';
 
350
                $GLOBALS['TableListImpact'] = $listTable;
 
351
                if(strpos($listTable, ',')) return false;
 
352
                $tableNAme = trim($listTable);
 
353
                if($res = $this->connId->getResId('SELECT type FROM sqlite_master WHERE name LIKE '.quotes($tableNAme)));
 
354
                if(@$this->connId->connId->fetch_single() != 'table') {
 
355
                        return false;
 
356
                } else return $tableNAme;
 
357
        }
 
358
 
 
359
        /**
 
360
        * Log string into journal file
 
361
        *
 
362
        * @access public
 
363
        * @param string $string
 
364
        */
 
365
        function queryLog($string){
 
366
                if($this->journalised){
 
367
                        $fp = fopen($this->journalFile, 'a+');
 
368
                        fwrite($fp, $string."\n");
 
369
                        fclose($fp);
 
370
                }
 
371
        }
 
372
 
 
373
        /**
 
374
        * Verify if the string param is a start of string
 
375
        * and if param motif is set, check if the start query content this
 
376
        *
 
377
        * @access public
 
378
        * @param string $req query
 
379
        * @param string $motif
 
380
        */
 
381
        function _checkBeginQuery($req, $motif=NULL){
 
382
                if(preg_match('/^\s*(select|insert|update|delete|create|drop|replace|pragma)\s/i', $req)) {
 
383
                        if(strlen($motif) != '') {
 
384
                                return preg_match('/^\s*('.$motif.')\s/i', $req) == 1;
 
385
                        }
 
386
                return true;
 
387
                }
 
388
                return false;
 
389
        }
 
390
 
 
391
        /**
 
392
        * Display result when the query is multiple
 
393
        *
 
394
        * @access public
 
395
        */
 
396
        function DisplayMultipleResult(){
 
397
                echo '
 
398
                        <table width="60%" align="center">
 
399
                                <tr><td bgcolor="lightblue" >'.$this->nbQuery.' '.$GLOBALS['traduct']->get(70).' '.$this->queryTime.' '.$GLOBALS['traduct']->get(214).'</td></tr>
 
400
                                <tr><td bgcolor="#CCCCCC"><span class="sqlsyntaxe">&nbsp;'.$this->changesLine.' '.$GLOBALS['traduct']->get(71).'</span></td></tr>
 
401
                        </table>';
 
402
                return;
 
403
        }
 
404
 
 
405
        /**
 
406
        * Get column of table select for SQL
 
407
        *
 
408
        * @access private
 
409
        */
 
410
        function GetColumnSelect(){
 
411
                $query = 'PRAGMA table_info('.brackets($GLOBALS['table']).');';
 
412
                $tableInfoTable = array();
 
413
                $out = '';
 
414
                if($this->connId->getResId($query)){
 
415
                        $tableInfoTable = $this->connId->getArray();
 
416
                }
 
417
                if(!empty($tableInfoTable)){
 
418
                        $optionList = array();
 
419
                        foreach($tableInfoTable as $columnInfo){
 
420
                                $optionList[] = '<option value="'.brackets($GLOBALS['table']).'.'.brackets($columnInfo['name']).'">'.brackets($GLOBALS['table']).'.'.brackets($columnInfo['name']).'</option>';
 
421
                        }
 
422
                        $out = '<div><select name="columnTable" size="'.TEXAREA_NB_ROWS.'" multiple="multiple">'."\n".implode("\n", $optionList).'</select></div>'."\n";
 
423
                        $out .= '<div><input name="insertButton" class="button" type="button" value="'.$GLOBALS['traduct']->get(75).'" onClick="insertColumn();"></div>';
 
424
                }
 
425
                return $out;
 
426
        }
 
427
}
 
428
 
 
429
?>