db_mysql.php 13 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
<?php

/**
 * Session Management for PHP3
 *
 * Copyright (c) 1998-2000 NetUSE AG
 *                    Boris Erdmann, Kristian Koehntopp
 *
 * $Id: db_mysql.php,v 1.3 2005/03/05 16:27:30 said Exp $
 *
 */ 

class DB_Sql {
  
  /* public: connection parameters */
  var $Host     = "";
  var $Database = "";
  var $User     = "";
  var $Password = "";

  /* public: configuration parameters */
  var $Auto_Free     = 0;     ## Set to 1 for automatic mysql_free_result()
  var $Debug         = 0;     ## Set to 1 for debugging messages.
24
  var $Halt_On_Error = "no"; ## "yes" (halt with message), "no" (ignore errors quietly), "report" (ignore errror, but spit a warning)
25
26
27
28
29
  var $Seq_Table     = "db_sequence";

  /* public: result array and current row number */
  var $Record   = array();
  var $Row;
Alan Garcia's avatar
Alan Garcia committed
30
  var $num_rows;
31
32
33
34
35
36
37
38
39
40
41
42

  /* public: current error number and error text */
  var $Errno    = 0;
  var $Error    = "";

  /* public: this is an api revision, not a CVS revision. */
  var $type     = "mysql";
  var $revision = "1.2";

  /* private: link and query handles */
  var $Link_ID  = 0;
  var $Query_ID = 0;
43
  var $Query_String = "";
44
45
46
47
48
49
50
51
52
53
54
  


  /**
  * Constructor 
  */
  function DB_Sql($query = "") {
      $this->query($query);
  }

  /**
fufroma's avatar
fufroma committed
55
  * @return resource class variable Link_ID
56
57
58
59
60
61
  */
  function link_id() {
    return $this->Link_ID;
  }

  /**
fufroma's avatar
fufroma committed
62
  * @return integer class variable Query_ID
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
  */
  function query_id() {
    return $this->Query_ID;
  }

  /**
  * function for MySQL database connection management
  *
  * This function manages the connection to the MySQL database.
  *
  * @param $Database name of the database
  * @param $Host DNS of the MySQL hosting server
  * @param $User the user's name
  * @param $Password the user's password
  *
  * @return the class variable $Link_ID
  */
  function connect($Database = "", $Host = "", $User = "", $Password = "") {
    /* Handle defaults */
    if ("" == $Database)
      $Database = $this->Database;
    if ("" == $Host)
      $Host     = $this->Host;
    if ("" == $User)
      $User     = $this->User;
    if ("" == $Password)
      $Password = $this->Password;
      
    /* establish connection, select database */
    if ( 0 == $this->Link_ID ) {
    
fufroma's avatar
fufroma committed
94
      $this->Link_ID=mysql_pconnect($Host, $User, $Password);
95
96
97
98
99
      if (!$this->Link_ID) {
        $this->halt("pconnect($Host, $User, \$Password) failed.");
        return 0;
      }

fufroma's avatar
fufroma committed
100
      if (!@mysql_select_db($Database,$this->Link_ID)) {
101
102
103
104
        $this->halt("cannot use database ".$this->Database);
        return 0;
      }
    }
105
106
107

    //persistent connection don't conserve database selection
    //if needed do a correct database selection
fufroma's avatar
fufroma committed
108
    $db_connected = @mysql_fetch_array(@mysql_query("SELECT DATABASE();",$this->Link_ID));
109
    if ($db_connected[0] != $this->Database)
fufroma's avatar
fufroma committed
110
      mysql_select_db($Database,$this->Link_ID);
111
112
113
114
115
116
117
118
119
120
    
    return $this->Link_ID;
  }

  /**
  * Discard the query result 
  *
  * This function discards the last query result.
  */
  function free() {
fufroma's avatar
fufroma committed
121
      @mysql_free_result($this->Query_ID);
122
123
124
125
126
127
128
129
130
131
132
133
      $this->Query_ID = 0;
  }

  /** 
  * Perform a query 
  * 
  * This function performs the MySQL query described in the string parameter
  *
  * @param a string describing the MySQL query   
  * @return the $Query_ID class variable (null if fails)
  */
  function query($Query_String) {
134
135
    global $debug_alternc;

136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
    /* No empty queries, please, since PHP4 chokes on them. */
    if ($Query_String == "")
      /* The empty query string is passed on from the constructor,
       * when calling the class without a query, e.g. in situations
       * like these: '$db = new DB_Sql_Subclass;'
       */
      return 0;

    if (!$this->connect()) {
      return 0; /* we already complained in connect() about that. */
    };

    # New query, discard previous result.
    if ($this->Query_ID) {
      $this->free();
151
      $this->Query_String = $Query_String;
152
153
154
155
156
    }

    if ($this->Debug)
      printf("Debug: query = %s<br />\n", $Query_String);

157
    $debug_chrono_start = microtime(true);
fufroma's avatar
fufroma committed
158
    $this->Query_ID = @mysql_query($Query_String,$this->Link_ID);
159
    $debug_chrono_start = (microtime(true) - $debug_chrono_start)*1000;
160
    $this->Row   = 0;
fufroma's avatar
fufroma committed
161
162
    $this->Errno = mysql_errno();
    $this->Error = mysql_error();
alban's avatar
alban committed
163
164
165
166
167
168
169
    if( 0 != $this->Errno ){
        if( defined("THROW_EXCEPTIONS") && THROW_EXCEPTIONS ){
            throw new \Exception("Mysql query failed : $this->Error");
        }
        $this->halt("SQL Error: ".$Query_String);
        return FALSE;
    }
170
171
172
173
    if (!$this->Query_ID) {
      $this->halt("Invalid SQL: ".$Query_String);
    }

174
175
    if (isset($debug_alternc)) {
      $debug_alternc->add("SQL Query : (".substr($debug_chrono_start,0,5)." ms)\t $Query_String");
176
      $debug_alternc->nb_sql_query++;
Alan Garcia's avatar
Alan Garcia committed
177
      $debug_alternc->tps_sql_query += $debug_chrono_start;
178
179
    }

180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
    # Will return nada if it fails. That's fine.
    return $this->Query_ID;
  }

  /**
  * walk result set 
  *
  * This function tests if a new record is available in the current
  * query result.
  *
  * @return TRUE if a new record is available
  */
  function next_record() {
    if (!$this->Query_ID) {
      $this->halt("next_record called with no query pending.");
      return 0;
    }

fufroma's avatar
fufroma committed
198
    $this->Record = @mysql_fetch_array($this->Query_ID);
199
    $this->Row   += 1;
fufroma's avatar
fufroma committed
200
201
    $this->Errno  = mysql_errno();
    $this->Error  = mysql_error();
202
203
204
205
206
207
208
209
210
211
212
213
214
215

    $stat = is_array($this->Record);
    if (!$stat && $this->Auto_Free) {
      $this->free();
    }
    return $stat;
  }

  /**
  *
  * public: position in result set 
  */

  function seek($pos = 0) {
fufroma's avatar
fufroma committed
216
    $status = @mysql_data_seek($this->Query_ID, $pos);
217
218
219
220
221
222
223
224
225
    if ($status)
      $this->Row = $pos;
    else {
      $this->halt("seek($pos) failed: result has ".$this->num_rows()." rows");

      /* half assed attempt to save the day, 
       * but do not consider this documented or even
       * desireable behaviour.
       */
fufroma's avatar
fufroma committed
226
      @mysql_data_seek($this->Query_ID, $this->num_rows());
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
      $this->Row = $this->num_rows;
      return 0;
    }

    return 1;
  }

  /* public: table locking */
  function lock($table, $mode="write") {
    $this->connect();
    
    $query="lock tables ";
    if (is_array($table)) {
      while (list($key,$value)=each($table)) {
        if ($key=="read" && $key!=0) {
          $query.="$value read, ";
        } else {
          $query.="$value $mode, ";
        }
      }
      $query=substr($query,0,-2);
    } else {
      $query.="$table $mode";
    }
fufroma's avatar
fufroma committed
251
    $res = @mysql_query($query, $this->Link_ID);
252
253
254
255
256
257
258
259
260
261
    if (!$res) {
      $this->halt("lock($table, $mode) failed.");
      return 0;
    }
    return $res;
  }
  
  function unlock() {
    $this->connect();

fufroma's avatar
fufroma committed
262
    $res = @mysql_query("unlock tables", $this->Link_ID);
263
264
265
266
267
268
269
270
271
272
    if (!$res) {
      $this->halt("unlock() failed.");
      return 0;
    }
    return $res;
  }


  /* public: evaluate the result (size, width) */
  function affected_rows() {
fufroma's avatar
fufroma committed
273
    return @mysql_affected_rows($this->Link_ID);
274
275
276
  }

  function num_rows() {
fufroma's avatar
fufroma committed
277
    return @mysql_num_rows($this->Query_ID);
278
279
280
  }

  function num_fields() {
fufroma's avatar
fufroma committed
281
    return @mysql_num_fields($this->Query_ID);
282
283
284
285
286
287
288
289
290
291
292
  }

  /* public: shorthand notation */
  function nf() {
    return $this->num_rows();
  }

  function np() {
    print $this->num_rows();
  }

fufroma's avatar
fufroma committed
293
294
  /**
   * @param string $Name
fufroma's avatar
fufroma committed
295
   * @return integer
fufroma's avatar
fufroma committed
296
   */
297
298
299
300
301
302
303
304
305
  function f($Name) {
    return $this->Record[$Name];
  }

  function p($Name) {
    print $this->Record[$Name];
  }

  function lastid() {
fufroma's avatar
fufroma committed
306
  	return @mysql_insert_id($this->Link_ID);
307
308
309
310
311
312
313
314
315
316
317
  }

  /* public: sequence numbers */
  function nextid($seq_name) {
    $this->connect();

    if ($this->lock($this->Seq_Table)) {
      /* get sequence number (locked) and increment */
      $q  = sprintf("select nextid from %s where seq_name = '%s'",
                $this->Seq_Table,
                $seq_name);
fufroma's avatar
fufroma committed
318
319
      $id  = @mysql_query($q, $this->Link_ID);
      $res = @mysql_fetch_array($id);
320
321
322
323
324
325
326
327
      
      /* No current value, make one */
      if (!is_array($res)) {
        $currentid = 0;
        $q = sprintf("insert into %s values('%s', %s)",
                 $this->Seq_Table,
                 $seq_name,
                 $currentid);
fufroma's avatar
fufroma committed
328
        @mysql_query($q, $this->Link_ID);
329
330
331
332
333
334
335
336
      } else {
        $currentid = $res["nextid"];
      }
      $nextid = $currentid + 1;
      $q = sprintf("update %s set nextid = '%s' where seq_name = '%s'",
               $this->Seq_Table,
               $nextid,
               $seq_name);
fufroma's avatar
fufroma committed
337
      @mysql_query($q, $this->Link_ID);
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
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
      $this->unlock();
    } else {
      $this->halt("cannot lock ".$this->Seq_Table." - has it been created?");
      return 0;
    }
    return $nextid;
  }

  /* public: return table metadata */
  function metadata($table='',$full=false) {
    $res   = array();

    /*
     * Due to compatibility problems with Table we changed the behavior
     * of metadata();
     * depending on $full, metadata returns the following values:
     *
     * - full is false (default):
     * $result[]:
     *   [0]["table"]  table name
     *   [0]["name"]   field name
     *   [0]["type"]   field type
     *   [0]["len"]    field length
     *   [0]["flags"]  field flags
     *
     * - full is true
     * $result[]:
     *   ["num_fields"] number of metadata records
     *   [0]["table"]  table name
     *   [0]["name"]   field name
     *   [0]["type"]   field type
     *   [0]["len"]    field length
     *   [0]["flags"]  field flags
     *   ["meta"][field name]  index of field named "field name"
     *   The last one is used, if you have a field name, but no index.
     *   Test:  if (isset($result['meta']['myfield'])) { ...
     */

    // if no $table specified, assume that we are working with a query
    // result
    if ($table) {
      $this->connect();
      $id = @mysql_list_fields($this->Database, $table);
      if (!$id)
        $this->halt("Metadata query failed.");
    } else {
      $id = $this->Query_ID; 
      if (!$id)
        $this->halt("No query specified.");
    }
 
    $count = @mysql_num_fields($id);

    // made this IF due to performance (one if is faster than $count if's)
    if (!$full) {
      for ($i=0; $i<$count; $i++) {
        $res[$i]["table"] = @mysql_field_table ($id, $i);
        $res[$i]["name"]  = @mysql_field_name  ($id, $i);
        $res[$i]["type"]  = @mysql_field_type  ($id, $i);
        $res[$i]["len"]   = @mysql_field_len   ($id, $i);
        $res[$i]["flags"] = @mysql_field_flags ($id, $i);
      }
    } else { // full
      $res["num_fields"]= $count;
    
      for ($i=0; $i<$count; $i++) {
        $res[$i]["table"] = @mysql_field_table ($id, $i);
        $res[$i]["name"]  = @mysql_field_name  ($id, $i);
        $res[$i]["type"]  = @mysql_field_type  ($id, $i);
        $res[$i]["len"]   = @mysql_field_len   ($id, $i);
        $res[$i]["flags"] = @mysql_field_flags ($id, $i);
        $res["meta"][$res[$i]["name"]] = $i;
      }
    }

    // free the result only if we were called on a table
fufroma's avatar
fufroma committed
414
    if ($table) @mysql_free_result($id);
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
    return $res;
  }

/********************************************************************************************************/
// AJOUT PERSO : TEST

/* public: return table metadata 
  function retourneNameField($this->Query_ID,$full=false) {
    $count = 0;
    $id    = 0;
    $res   = array();

    
    $count = @mysql_num_fields($this->Query_ID);

    // made this IF due to performance (one if is faster than $count if's)
    if (!$full) {
      for ($i=0; $i<$count; $i++) {
        $res[$i]["table"] = @mysql_field_table ($id, $i);
        $res[$i]["name"]  = @mysql_field_name  ($id, $i);
        $res[$i]["type"]  = @mysql_field_type  ($id, $i);
        $res[$i]["len"]   = @mysql_field_len   ($id, $i);
        $res[$i]["flags"] = @mysql_field_flags ($id, $i);
      }
    } else { // full
      $res["num_fields"]= $count;
    
      for ($i=0; $i<$count; $i++) {
        $res[$i]["table"] = @mysql_field_table ($id, $i);
        $res[$i]["name"]  = @mysql_field_name  ($id, $i);
        $res[$i]["type"]  = @mysql_field_type  ($id, $i);
        $res[$i]["len"]   = @mysql_field_len   ($id, $i);
        $res[$i]["flags"] = @mysql_field_flags ($id, $i);
        $res["meta"][$res[$i]["name"]] = $i;
      }
    }
    
    // free the result only if we were called on a table
    if ($table) @mysql_free_result($id);
    return $res;
  }*/

/********************************************************************************************************/
  /* private: error handling */
  function halt($msg) {
fufroma's avatar
fufroma committed
460
461
    $this->Error = @mysql_error($this->Link_ID);
    $this->Errno = @mysql_errno($this->Link_ID);
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
    if ($this->Halt_On_Error == "no")
      return;

    $this->haltmsg($msg);

    if ($this->Halt_On_Error != "report")
      die("Session halted.");
  }

  function haltmsg($msg) {
    printf("</td></tr></table><b>Database error:</b> %s<br />\n", $msg);
    printf("<b>MySQL Error</b>: %s (%s)<br />\n",
      $this->Errno,
      $this->Error);
  }

  function table_names() {
    $this->query("SHOW TABLES");
    $i=0;
Alan Garcia's avatar
Alan Garcia committed
481
    $return=array();
Alan Garcia's avatar
Alan Garcia committed
482
    while ($info=mysqli_fetch_row($this->Query_ID))
483
484
485
486
487
488
489
490
491
492
     {
      $return[$i]["table_name"]= $info[0];
      $return[$i]["tablespace_name"]=$this->Database;
      $return[$i]["database"]=$this->Database;
      $i++;
     }
   return $return;
  }
}
?>