PageRenderTime 169ms CodeModel.GetById 80ms app.highlight 48ms RepoModel.GetById 33ms app.codeStats 0ms

/system/db/db.mysql.php

https://github.com/danboy/Croissierd
PHP | 797 lines | 465 code | 174 blank | 158 comment | 113 complexity | 35ab95f66eb1fdf80a2a1d2df873f1b9 MD5 | raw file
  1<?php
  2/*
  3=====================================================
  4 ExpressionEngine - by EllisLab
  5-----------------------------------------------------
  6 http://expressionengine.com/
  7-----------------------------------------------------
  8 Copyright (c) 2003 - 2010 EllisLab, Inc.
  9=====================================================
 10 THIS IS COPYRIGHTED SOFTWARE
 11 PLEASE READ THE LICENSE AGREEMENT
 12 http://expressionengine.com/docs/license.html
 13=====================================================
 14 File: db.mysql.php
 15-----------------------------------------------------
 16 Purpose: SQL database abstraction: MySQL
 17=====================================================
 18*/
 19
 20
 21if ( ! defined('EXT'))
 22{
 23    exit('Invalid file request');
 24}
 25
 26
 27//---------------------------------------    
 28//    DB Cache Class
 29//---------------------------------------
 30
 31// This object gets serialized and cached.
 32// It provides a simple mechanism to store queries
 33// that are portable as objects
 34
 35class DB_Cache {
 36
 37    var $result   = array();
 38    var $row      = array();
 39    var $num_rows = 0;
 40    var $q_count  = 0;
 41}
 42// END CLASS
 43
 44
 45
 46//---------------------------------------    
 47//	DB Class
 48//---------------------------------------
 49
 50
 51class DB {
 52
 53    // Public variables
 54
 55    var $hostname       	= 'localhost';
 56    var $username      		= 'root';
 57    var $password      		= '';
 58    var $database       	= '';
 59    var $prefix         	= 'exp_';       // Table prefix
 60    var $conntype       	= 1;            // 1 = persistent.  0 = non
 61    var $cache_dir      	= 'db_cache/';  // Cache directory/path with trailing slash.
 62    var $debug          	= 0;            // Manually turns on debugging
 63    var $enable_cache   	= TRUE;         // true/false Enables query caching
 64	var $error_footer		= '';			// This is used by the update script
 65	var $error_header		= '';
 66
 67    // Private variables. 
 68
 69    var $exp_prefix     	= 'exp_';
 70    var $cache_path     	= '';
 71    var $cache_file     	= '';
 72    var $sql_table      	= '';
 73    var $insert_id      	= '';
 74    var $q_count        	= 0;
 75    var $affected_rows  	= 0;
 76    var $conn_id        	= FALSE;
 77    var $query_id       	= FALSE;
 78    var $fetch_fields   	= FALSE;
 79    var $cache_enabled		= FALSE;
 80    var $field_names    	= array();
 81    var $tables_list		= array();
 82    var $show_queries		= FALSE;		// Enables queries to be shown for debugging
 83    var $queries			= array();		// Stores the queries
 84    var $server_info		= '';			// MySQL Server Info, like version
 85
 86
 87    /** ---------------------------------------    
 88    /**  Constructor
 89    /** ---------------------------------------*/
 90
 91    function DB($settings)
 92    {
 93        global $PREFS;
 94        
 95		$db_settings = array(
 96								'hostname', 
 97								'username',
 98								'password',
 99								'database',
100								'conntype',
101								'prefix',
102								'debug',
103								'show_queries',
104								'enable_cache'
105							);
106       
107		foreach ($db_settings as $item)
108		{
109			if (isset($settings[$item]))
110			{
111				$this->$item = $settings[$item];
112			}
113		}
114                
115		if ($this->prefix != '' && substr($this->prefix, -1) != '_')
116		{
117            $this->prefix .= '_';
118		}
119    }
120    /* END */
121
122
123    /** ---------------------------------------    
124    /**  Forces a Reconnect On Next Query
125    /** ---------------------------------------*/
126    
127    function reconnect()
128    {    
129		if (function_exists('mysql_ping'))
130		{
131			if (mysql_ping($this->conn_id) === FALSE)
132			{
133				$this->conn_id = FALSE;
134			}
135		}
136    }
137    /* END */
138    
139    
140	/** ---------------------------------------    
141    /**  Connect to database
142    /** ---------------------------------------*/
143    
144    function db_connect($select_db = TRUE)
145    {    
146        $this->conn_id = ($this->conntype == 0) ?
147          @mysql_connect ($this->hostname, $this->username, $this->password):
148          @mysql_pconnect($this->hostname, $this->username, $this->password);
149        
150        if ( ! $this->conn_id)
151        {            
152            return FALSE;        
153        }
154        
155        if ($select_db == TRUE)
156        {
157			if ( ! $this->select_db())
158			{
159				return FALSE;	
160			}
161        }
162        
163        $this->server_info = @mysql_get_server_info();
164        
165        return TRUE;
166    }
167    /* END */
168
169
170    /** ---------------------------------------    
171    /**  Select database
172    /** ---------------------------------------*/
173
174    function select_db()
175    {
176        if ( ! @mysql_select_db($this->database, $this->conn_id))
177        {            
178            return FALSE;
179        }
180        
181        return TRUE;
182	}
183	/* END */
184
185
186    /** ---------------------------------------    
187    /**  Close database connection
188    /** ---------------------------------------*/
189
190    function db_close()
191    {
192        if ($this->conn_id)
193            mysql_close($this->conn_id);
194    }         
195    /* END */
196    
197	/** ---------------------------------------    
198    /**  Enable SQL Query Caching
199    /** ---------------------------------------*/
200    
201    function enable_cache()
202    {
203    	global $PREFS;
204    	
205    	if ($this->enable_cache == TRUE)
206        {
207        	$this->cache_enabled = TRUE;
208			$this->cache_dir	 = rtrim(PATH_CACHE.$this->cache_dir, '/').'/';
209			
210			// We limit the total number of cache files in order to
211			// keep some sanity with large sites or ones that get
212			// hit by overambitious crawlers.
213			if ($dh = @opendir($this->cache_dir))
214			{
215				$i = 0;
216				while (false !== (readdir($dh)))
217				{
218					$i++;
219				}
220				
221			 	//$max = ( ! $PREFS->ini('max_caches') OR ! is_numeric($PREFS->ini('max_caches')) OR $PREFS->ini('max_caches') > 1000) ? 100 : $PREFS->ini('max_caches');
222	
223				if ($i > 150)
224				{
225					$this->delete_directory($this->cache_dir);
226				}
227			}
228        }
229    }
230    /* END */
231    
232
233    /** ---------------------------------------    
234    /**  DB Query
235    /** ---------------------------------------*/
236    
237    function query($sql)
238    { 
239		if ($sql == '')
240			return;
241
242		$sql = trim($sql);
243		$this->affected_rows	= 0;
244		$this->insert_id 		= 0;
245
246		// Store the query for debugging
247        
248        if ($this->show_queries == TRUE)
249        {
250        	$this->queries[] = $sql;
251        }
252           
253        // Verify table prefix and replace if necessary.
254            
255        if ($this->prefix != $this->exp_prefix)
256        { 
257           $sql = preg_replace("/(\W)".$this->exp_prefix."(\S+?)/", "\\1".$this->prefix."\\2", $sql);
258
259			// If the custom prefix includes 'exp_' the above can sometimes cause partial doubling.
260			// This is a quick fix to prevent this from causing errors in 1.x.
261			if (strncmp($this->prefix, 'exp_', 4) == 0)
262			{
263				$sql = str_replace($this->prefix.str_replace('exp_', '', $this->prefix), $this->prefix, $sql);
264			}
265        }
266        
267        /**
268         *	The Cache Cannot be enabled until AFTER the Input class is insantiated.
269         */
270        if ($this->enable_cache == TRUE && $this->cache_enabled == FALSE && isset($GLOBALS['IN']) && is_object($GLOBALS['IN']))
271        {
272        	$this->enable_cache();
273        }
274                        
275        if ($this->cache_enabled == TRUE)
276        {
277        	global $IN;
278        
279			// The URI being requested will become the name of the cache directory
280					
281			$this->cache_path = ($IN->URI == '') ? $this->cache_dir.md5('index').'/' : $this->cache_path = $this->cache_dir.md5($IN->URI).'/';
282					
283			// Convert the SQL query into a hash.  This will become the cache file name.
284		
285			$this->cache_file = md5($sql);
286	
287			// Is this query a read type?  
288			// If so, return the previously cached data if it exists and bail out.
289			
290			if (stristr($sql, 'SELECT'))
291			{
292				if (FALSE !== ($cache = $this->get_cache()))
293				{
294					return $cache;
295				}
296			}
297		}
298        
299        // Connect to the DB if we haven't done so on a previous query
300        
301        if ( ! $this->conn_id)    
302        {        
303			if ( ! $this->db_connect(0))
304			{
305				exit("Database Error:  Unable to connect to your database. Your database appears to be turned off or the database connection settings in your config file are not correct. Please contact your hosting provider if the problem persists.");
306			}
307			
308			if ( ! $this->select_db())
309			{
310				exit("Database Error:  Unable to select your database");
311			}
312        }
313
314        // Execute the query
315                
316        if ( ! $this->query_id = mysql_query($sql, $this->conn_id))
317        {
318            if ($this->debug)
319            {
320                return $this->db_error("MySQL ERROR:", $this->conn_id, $sql);
321            }
322          
323			return FALSE;
324        }
325
326        // Increment the query counter
327        
328        $this->q_count++;
329
330        // Determine if the query is one of the 'write' types. If so, gather the
331        // affected rows and insert ID, and delete the existing cache file.
332
333        $qtypes = array('INSERT', 'UPDATE', 'DELETE', 'CREATE', 'ALTER', 'DROP', 'REPLACE', 'GRANT', 'REVOKE', 'LOCK', 'UNLOCK', 'TRUNCATE');
334		
335		if (preg_match("#^(".implode('|', $qtypes).")#i", $sql, $qmatches))
336		{
337			$type = $qmatches[1];
338			
339			$this->affected_rows = mysql_affected_rows($this->conn_id);
340			
341			if ($type == 'INSERT' || $type == 'REPLACE')
342			{
343				$this->insert_id = mysql_insert_id($this->conn_id);
344			}
345			
346			// Delete the cache file since the data in it is no longer current.
347			
348			if ($this->cache_enabled == TRUE)
349			{
350				$this->delete_cache();
351			}
352
353			// Bail out.  We are done
354			if ($type == 'INSERT' OR $type == 'UPDATE' OR $type == 'DELETE')
355			{
356				return ($this->affected_rows == 0 AND $this->insert_id == 0) ? FALSE : TRUE;     
357			}
358			else
359			{
360				return TRUE;
361			}
362        }
363        
364        // Fetch the field names, but only if explicitly requested
365        // We use this in our SQL utilities functions
366        
367        if ($this->fetch_fields == TRUE)
368        { 
369            $this->field_names = array();
370            
371            while ($field = mysql_fetch_field($this->query_id))
372            {
373                $this->field_names[] = $field->name;       
374            }
375         }
376
377        // Fetch the result of the query and assign it to an array.
378        // I know, the result *is* an array.  But we want our own
379        // numerically indexed array so we can cache it.
380
381        $i = 0;
382        $result = array();
383        while ($row = mysql_fetch_array($this->query_id, MYSQL_ASSOC)) 
384        {                                    
385            $result[$i] = $row;
386            $i++;
387        }
388        
389        // Free the result.  Optional with MySQL, but might as well be thorough
390        
391        mysql_free_result($this->query_id);
392
393        // Instantiate the cache super-class and assign the data 
394        // to it if a subsequent query hasn't already done so
395        
396		$DBC = new DB_Cache;
397		$DBC->result   = $result;
398		$DBC->row      = (isset($result['0'])) ? $result['0'] : array();
399		$DBC->num_rows = $i;
400	
401        // Serialize the class and store it in a cache file
402        
403        if ($this->cache_enabled == TRUE)
404        {
405            $this->store_cache(serialize($DBC));
406        }
407            
408        // Assign the query count to the super-class.  
409        // The query count only applies to non-cached queries,
410        // so we add it after the class has already been cached.
411        
412        $DBC->q_count = $this->q_count;
413        $DBC->fields  = $this->field_names;
414        
415        // Return it    
416        return $DBC;        
417    }
418    /* END */
419
420
421    /** ---------------------------------------    
422    /**  Fetch SQL tables
423    /** ---------------------------------------*/
424
425    function fetch_tables()
426    {      
427    	if (sizeof($this->tables_list) > 0)
428    	{	
429    		return $this->tables_list;	
430    	}
431    
432        if ( ! $this->conn_id)    
433        {
434			if ( ! $this->db_connect(0))
435			{
436				exit("Database Error:  Unable to connect to your database. Your database appears to be turned off or the database connection settings in your config file are not correct. Please contact your hosting provider if the problem persists.");
437			}
438			
439			if ( ! $this->select_db())
440			{
441				exit("Database Error:  Unable to select your database");
442			}
443        }
444        
445        // mysql_list_tables() was depreciated, so we switched to using
446        // this query, which should work. -Paul
447        
448		// We use $this->prefix as query() will not match the like escaped exp_prefix.
449        $query = $this->query("SHOW TABLES FROM `{$this->database}` LIKE '".$this->escape_like_str($this->prefix)."%'"); 
450        
451        if ($query->num_rows > 0)
452        {
453        	foreach($query->result as $row)
454        	{
455        		$this->tables_list[]  = array_shift($row);
456        	}
457        }
458        
459        return $this->tables_list;
460    }
461    /* END */
462    
463    
464    /** ---------------------------------------    
465    /**  Determine if a table exists
466    /** ---------------------------------------*/
467
468    function table_exists($table_name)
469    {
470		if ($this->prefix != $this->exp_prefix)
471        { 
472			$table_name = preg_replace("/".$this->exp_prefix."(\S+?)/", $this->prefix."\\1", $table_name);
473        }
474    
475		if ( ! in_array($table_name, $this->fetch_tables()))
476		{
477			return FALSE;
478		}
479		
480		return TRUE;
481	}
482    /* END */
483
484
485    /** ---------------------------------------    
486    /**  Cache a query
487    /** ---------------------------------------*/
488
489    function store_cache($object)
490    {
491        $dirs = array(PATH_CACHE.'db_cache', substr($this->cache_path, 0, -1));
492        
493        foreach ($dirs as $dir)
494        {       
495			if ( ! @is_dir($dir))
496			{
497				if ( ! @mkdir($dir, 0777))
498				{
499					return;
500				}
501				
502				if ($dir == PATH_CACHE.'db_cache' && $fp = @fopen($dir.'/index.html', 'wb'))
503				{
504					fclose($fp);					
505				}
506				
507				@chmod($dir, 0777);            
508			}
509        }
510	      
511        if ( ! $fp = @fopen($this->cache_path.$this->cache_file, 'wb'))
512            return;
513
514        flock($fp, LOCK_EX);
515        fwrite($fp, $object);
516        flock($fp, LOCK_UN);
517        fclose($fp);
518        
519		@chmod($this->cache_path.$this->cache_file, 0777);            
520    }
521    /* END */
522
523
524    /** ---------------------------------------    
525    /**  Retreive a cached query
526    /** ---------------------------------------*/
527
528    function get_cache()
529    {            
530        if ( ! @is_dir($this->cache_path))
531            return false;    
532        
533        if ( ! file_exists($this->cache_path.$this->cache_file))
534            return false;
535        
536        if ( ! $fp = @fopen($this->cache_path.$this->cache_file, 'rb'))
537            return false;
538
539        flock($fp, LOCK_SH);
540        
541        $cachedata = @fread($fp, filesize($this->cache_path.$this->cache_file));
542        
543        flock($fp, LOCK_UN);
544        fclose($fp);
545        
546        if ( ! is_string($cachedata)) return FALSE;
547        
548		return unserialize($cachedata);            
549    }
550    /* END */
551    
552
553    /** ---------------------------------------    
554    /**  Delete cache files
555    /** ---------------------------------------*/
556
557    function delete_cache()
558    {    
559        if ( ! @is_dir($this->cache_path))
560            return FALSE;
561    
562        if ( ! $fp = @opendir($this->cache_path)) 
563        { 
564			return FALSE;
565        } 
566        
567        while (false !== ($file = @readdir($fp))) 
568        {
569             if ($file != "."  AND  $file != "..")
570             {
571                if ( ! @unlink($this->cache_path.$file))
572                {
573					return FALSE;
574                }
575            }
576        }
577        
578		if ( ! @rmdir($this->cache_path))
579		{
580			return FALSE;
581		}
582                
583        closedir($fp); 
584    }
585    /* END */
586
587
588    /** -----------------------------------------
589    /**  Delete Direcories
590    /** -----------------------------------------*/
591
592    function delete_directory($path, $del_root = FALSE)
593    {
594        if ( ! $current_dir = @opendir($path))
595        {
596        	return;
597        }
598        
599        while($filename = @readdir($current_dir))
600        {        
601            if (@is_dir($path.'/'.$filename) and ($filename != "." and $filename != ".."))
602            {
603                $this->delete_directory($path.'/'.$filename, TRUE);
604            }
605            elseif($filename != "." and $filename != "..")
606            {
607                @unlink($path.'/'.$filename);
608            }
609        }
610        
611        closedir($current_dir);
612        
613        if ($del_root == TRUE)
614        {
615            @rmdir($path);
616        }
617    }
618    /* END */
619
620    /** ---------------------------------------    
621    /**  MySQL escape string
622    /** ---------------------------------------*/
623
624    function escape_str($str, $like = FALSE)    
625    {    
626    	if (is_array($str))
627    	{
628    		foreach($str as $key => $val)
629    		{
630    			$str[$key] = $this->escape_str($val, $like);
631    		}
632    		
633    		return $str;
634    	}
635
636		if (function_exists('mysql_real_escape_string') AND is_resource($this->conn_id))
637		{
638			$str =  mysql_real_escape_string(stripslashes($str), $this->conn_id);
639		}
640		elseif (function_exists('mysql_escape_string'))
641    	{
642			$str = mysql_escape_string(stripslashes($str));
643		}
644		else
645		{
646        	$str = addslashes(stripslashes($str));
647    	}
648    	
649    	if ($like === TRUE)
650    	{
651    		$replace_characters = array('%', '_');
652			$escaped_characters = array('\\%', '\\_');
653			
654			$str = str_replace($replace_characters, $escaped_characters, $str);
655    	}
656    	
657    	return $str;
658    }
659    /* END */
660    
661    /** ---------------------------------------    
662    /**  MySQL escape plus LIKE wildcards
663    /** ---------------------------------------*/
664
665    function escape_like_str($str)    
666    {    
667    	return $this->escape_str($str, TRUE);
668	}    
669
670    /** ---------------------------------------    
671    /**  Error Message
672    /** ---------------------------------------*/
673    
674    function db_error($msg, $id="", $sql="") 
675    {    
676        if ($this->error_header != '')
677        {
678        	$msg = $this->error_header.$msg;
679        }    
680    
681        if ($id) 
682        { 
683            $msg .= "<br /><br />";
684            $msg .= "Error Number: " . mysql_errno($id);
685            $msg .= "<br /><br />";
686            $msg .= "Description: "  . mysql_error($id);
687        }
688        
689        if ($sql)
690            $msg .= "<br /><br />Query: ".$sql;
691         
692        if ($this->error_footer != '')
693        {
694        	$msg .= $this->error_footer;
695        }
696        
697        exit($msg);
698    }    
699  
700  
701    /** ---------------------------------------    
702    /**  Write an INSERT string
703    /** ---------------------------------------*/
704
705    // This function simplifies the process of writing database inserts.  
706    // It returns a correctly formatted SQL insert string.
707    //
708    // Example:
709    //
710    //  $data = array('name' => $name, 'email' => $email, 'url' => $url);
711    //
712    //  $str = $DB->insert_string('exp_weblog', $data);
713    //
714    //  Produces:  INSERT INTO exp_weblog (name, email, url) VALUES ('Joe', 'joe@joe.com', 'www.joe.com')
715
716    function insert_string($table, $data, $addslashes = FALSE)
717    {
718        $fields = '';      
719        $values = '';
720        
721        if (stristr($table, '.'))
722        {
723        	$x = explode('.', $table, 3);
724        	$table = $x['0'].'`.`'.$x['1'];
725        }
726        
727        foreach($data as $key => $val) 
728        {
729            $fields .= '`' . $key . '`, ';
730            $val = ($addslashes === TRUE) ? addslashes($val) : $val;
731            $values .= "'".$this->escape_str($val)."'".', ';
732        }
733        
734        $fields = preg_replace( "/, $/" , "" , $fields);
735        $values = preg_replace( "/, $/" , "" , $values);
736
737        return 'INSERT INTO `'.$table.'` ('.$fields.') VALUES ('.$values.')';
738    }    
739    /* END */
740
741
742    /** ---------------------------------------    
743    /**  Write an UPDATE string
744    /** ---------------------------------------*/
745
746    // This function simplifies the process of writing database updates.  
747    // It returns a correctly formatted SQL update string.
748    //
749    // Example:
750    //
751    //  $data = array('name' => $name, 'email' => $email, 'url' => $url);
752    //
753    //  $str = $DB->update_string('exp_weblog', $data, "author_id = '1'");
754	//
755    //  Produces:  UPDATE exp_weblog SET name = 'Joe', email = 'joe@joe.com', url = 'www.joe.com' WHERE author_id = '1'
756
757    function update_string($table, $data, $where)
758    {
759        if ($where == '')
760            return false;
761    
762        $str  = '';
763        $dest = '';
764        
765        if (stristr($table, '.'))
766        {
767        	$x = explode('.', $table, 3);
768        	$table = $x['0'].'`.`'.$x['1'];
769        }
770        
771        foreach($data as $key => $val) 
772        {
773            $str .= '`'.$key."` = '".$this->escape_str($val)."', ";
774        }
775
776        $str = preg_replace( "/, $/" , "" , $str);
777        
778        if (is_array($where))
779        {
780            foreach ($where as $key => $val)
781            {
782                $dest .= $key." = '".$this->escape_str($val)."' AND ";
783            }
784            
785            $dest = preg_replace( "/AND $/" , "" , $dest);
786        }
787        else
788            $dest = $where;
789
790        return 'UPDATE `'.$table.'` SET '.$str.' WHERE '.$dest;        
791    }    
792    /* END */
793
794
795}
796// END CLASS
797?>