Xataface  2.0alpha2
Xataface Application Framework
 All Data Structures Namespaces Files Functions Variables Groups Pages
DB.php
Go to the documentation of this file.
1 <?php
2 /*-------------------------------------------------------------------------------
3  * Xataface Web Application Framework
4  * Copyright (C) 2005-2008 Web Lite Solutions Corp (shannah@sfu.ca)
5  *
6  * This program is free software; you can redistribute it and/or
7  * modify it under the terms of the GNU General Public License
8  * as published by the Free Software Foundation; either version 2
9  * of the License, or (at your option) any later version.
10  *
11  * This program is distributed in the hope that it will be useful,
12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14  * GNU General Public License for more details.
15  *
16  * You should have received a copy of the GNU General Public License
17  * along with this program; if not, write to the Free Software
18  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
19  *-------------------------------------------------------------------------------
20  */
31 import( 'Dataface/Application.php');
32 import('Dataface/Table.php');
33 
34 class Dataface_DB {
35  var $_db;
36  var $_fieldIndex = array();
37  var $_queryCache = array();
38  var $_parser = null;
39  var $_compiler = null;
40  var $_cache = null;
41  var $_cacheDirtyFlag = false;
42  var $app;
43  var $_matchCount;
44  var $_matches;
45  var $_insert_id;
46  var $count=0;
47  var $_fcache_base_path= null;
48  var $db_hits = 0;
49  var $cache_hits = 0;
50  var $cache_fails = 0;
51 
52  var $blobs = array(); // Blobs.
53 
54  function Dataface_DB($db=null){
55  if ( $db === null ){
56  $db = DATAFACE_DB_HANDLE;
57  }
58  $this->_db = $db;
59  $this->app =& Dataface_Application::getInstance();
60 
61  if ( @$this->app->_conf['cache_queries'] and !$this->_fcache_base_path ){
62  if ( is_writable(DATAFACE_SITE_PATH.'/templates_c') ){
63  $this->_fcache_base_path = DATAFACE_SITE_PATH.'/templates_c/query_results';
64  } else {
65  $this->_fcache_base_path = DATAFACE_PATH.'/Dataface/templates_c/query_results';
66  }
67 
68  if ( !file_exists($this->_fcache_base_path) ){
69 
70  mkdir($this->_fcache_base_path, 0700);
71  file_put_contents($this->_fcache_base_path.'/.htaccess', 'deny from all');
72  }
73  }
74 
75 
76 
77 
78 
79  }
80 
86  function _loadCache(){
87  if ( !isset($this->_cache) ){
88  $filepath = DATAFACE_CACHE_PATH.'/Dataface_DB.cache';
89  //echo "Checking cache... $filepath";
90 
91  if ( is_readable($filepath) and filemtime($filepath) > time()-1 ){
92  //echo "Cache is readable";
93  include DATAFACE_CACHE_PATH.'/Dataface_DB.cache';
94 
95  }
96  if ( isset( $cache ) ){
97  $this->_cache =& $cache;
98  } else {
99  $this->_cache = array();
100  }
101  register_shutdown_function(array(&$this, 'writeCache'));
102 
103  }
104  }
105 
109  function cache($key, $value, $lang=null){
110  if ( !isset($lang) ) $lang = $this->_app->_conf['lang'];
111  // if the language isn't set, we use the default language from the database
112  $this->_loadCache();
113  $this->_cache[$lang][$key] = $value;
114  $this->_cacheDirtyFlag = true;
115  }
116 
117 
118 
122  function &_getParser(){
123 
124  if ( !isset($this->_parser)){
125  import('SQL/Parser.php');
126  $this->_parser = new SQL_Parser(null, 'MySQL');
127  }
128  return $this->_parser;
129  }
130 
134  function &_getCompiler(){
135  if ( !isset($this->_compiler) ){
136  import('SQL/Compiler.php');
137  $this->_compiler = SQL_Compiler::newInstance('mysql');
138  }
139  return $this->_compiler;
140  }
141 
142 
152  function prepareQuery($query){
153  //echo "Preparing query $query";
154  $len = strlen($query);
155  $escaped = false;
156  $dblquoted = false;
157  $sglquoted = false;
158  $output_query = '';
159  $buffer = '';
160  $output_args = array();
161  $count = 0;
162  for ($i=0;$i<$len;$i++){
163  $skip = false;
164  switch ($query{$i}){
165  case '\\': $escaped = !$escaped;
166  break;
167  case '"' : if ( !$escaped && !$sglquoted ){
168  $dblquoted = !$dblquoted;
169  if (!$dblquoted ){
170  // double quotes are done, we can update the buffer.
171  $count++; // increment the counter for number of found strings
172  $output_args[] = $buffer;
173  $buffer = '';
174  $output_query .= '"_'.$count.'_"';
175  }
176  $skip = true;
177  }
178 
179  break;
180  case '\'' : if (!$escaped && !$dblquoted) {
181  $sglquoted = !$sglquoted;
182  if ( !$sglquoted ){
183  // double quotes are done, we can update the buffer.
184  $count++; // increment the counter for number of found strings
185  $output_args[] = $buffer;
186  $buffer = '';
187  $output_query .= '\'_'.$count.'_\'';
188  }
189  $skip = true;
190 
191  }
192  break;
193 
194 
195  }
196 
197  if ( $query{$i} != '\\' ) $escaped = false;
198  if ( $skip ) continue;
199  if ( $dblquoted || $sglquoted) {
200  $buffer .= $query{$i};
201  }
202  else $output_query .= $query{$i};
203  }
204 
205  // Now to replace all numbers
206  $this->_matchCount = 0;
207  $this->_matches = array();
208  $output_query = preg_replace_callback('/\b(-{0,1})([0-9]*\.{0,1}[0-9]+)\b/', array(&$this, '_replacePrepareDigits'), $output_query);
209  $output_args = array($output_query, $output_args, $this->_matches);
210 
211  //print_r($output_args);
212  //print_r($output_args);
213  return $output_args;
214 
215  }
216 
217  function _replacePrepareDigits($matches){
218  $this->_matches[] = $matches[1].$matches[2];
219  return ++$this->_matchCount;
220  }
221 
222  function _replaceCompileStrings($matches){
223  return $matches[1].$this->_matches[intval($matches[2])-1].$matches[3];
224  }
225 
226  function _replaceCompileDigits($matches){
227  return $this->_matches[intval($matches[1])-1];
228  }
229 
230  function _replaceBlobs($matches){
231  $blob = $this->checkoutBlob($matches[1]);
232  //if ( !is_uploaded_file($blob) ) throw new Exception(df_translate('scripts.Dataface.DB._replaceBlobs.BLOB_NOT_UPLOADED',"Attempt to load blob that is not uploaded. "), E_USER_ERROR);
233  if ( PEAR::isError($blob) ) throw new Exception($blob->toString(), E_USER_ERROR);
234 
235  return mysql_real_escape_string(file_get_contents($blob));
236  }
237 
238 
239 
240  function compilePreparedQuery($prepared_query){
241  $numArgs = count($prepared_query[1]);
242  $buffer = $prepared_query[0];
243  $this->_matches = $prepared_query[2];
244  $buffer = preg_replace_callback('/\b([0-9]+)\b/', array(&$this, '_replaceCompileDigits'), $buffer);
245 
246  $this->_matches = $prepared_query[1];
247  $buffer = preg_replace_callback('/([\'"])_(\d+)_([\'"])/', array(&$this, '_replaceCompileStrings'), $buffer);
248 
249  $buffer = preg_replace_callback('/-=-=B(\d+)=-=-/', array(&$this, '_replaceBlobs'), $buffer);
250 
251  return $buffer;
252  }
253 
254 
255 
264  function translate_query($query, $lang=null){
265  //echo "Dirty flag: ".$this->_cacheDirtyFlag;
266  if ( $lang === null ){
267  // If no language is provided use the language in the conf.ini file
268  $lang = $this->app->_conf['lang'];
269  }
270  $this->_loadCache();
271 
272  if ( strtolower(substr($query, 0, strlen('DELETE '))) == 'delete ' ){
273  return $query;
274  }
275 
276  $original_query = $query;
277  $prepared_query = $this->prepareQuery($query);
278  if ( isset( $this->_cache[$lang][$prepared_query[0]] )){
279  // we have already translated this select query and cached it!
280  // just load the query from the cache and fill in the appropriate
281  // values:
282  $prepared_query[0] = $this->_cache[$lang][$prepared_query[0]];
283  return $this->compilePreparedQuery($prepared_query);
284  }
285 
286  $query = $prepared_query[0];
287  import('Dataface/QueryTranslator.php');
288  $translator = new Dataface_QueryTranslator($lang);
289  $output = $translator->translateQuery($prepared_query[0]);
290  if (PEAR::isError($output) ){
291  //echo $output->toString();
292  throw new Exception(df_translate('scripts.Dataface.DB.translate_query.FAILED_TO_TRANSLATE', "Failed to translate query: $query.: ",array('query'=>$query)).$output->toString(), E_USER_ERROR);
293  }
294 
295  $this->cache($prepared_query[0], $output, $lang);
296  $prepared_query[0] = $output;
297  return $this->compilePreparedQuery($prepared_query);
298  }
299 
307  function query($sql, $db=null, $lang=null, $as_array=false, $enumerated=false){
308 
309  $app =& Dataface_Application::getInstance();
310 
311  $refreshModTimes=false;
312  if ( $as_array and ($isSelect = (strpos(strtolower(trim($sql)), 'select ') === 0)) ){
313  if ( ($results = $this->memcache_get($sql, $lang)) or is_array($results) ) {
314  if ( @$this->app->_conf['cache_queries_log']){
315  $fp = fopen('/tmp/querylog.log', 'a');
316  fwrite($fp, "\n[".date('Y-m-d H:i:s')."] Cached: ".$sql);
317  fclose($fp);
318  }
319  $this->cache_hits++;
320  return $results;
321  } else {
322  if ( @$this->app->_conf['cache_queries_log']){
323  $fp = fopen('/tmp/querylog.log', 'a');
324  fwrite($fp, "\n[".date('Y-m-d H:i:s')."] Failed cached: ".$sql);
325  fclose($fp);
326  }
327  $this->cache_fails++;
328  $orig_sql = $sql; // save the original sql before it is translated
329  }
330 
331  } else if ( @$app->_conf['cache_queries'] ){
332  $refreshModTimes = true;
333  }
334 
335 
336  //$fp = fopen('/tmp/querylog.log', 'a');
337  //fwrite($fp, "\n[".date('Y-m-d H:i:s')."] Uncached: ".$sql);
338  //fclose($fp);
339  $this->count++;
340 
341  if ( ( /*isset($lang) ||*/ $this->app->_conf['multilingual_content'])) {
342  if ( @$app->_conf['debug_sql'] ){
343  error_log("Before translation: ".$sql);
344  }
345  $sql = $this->translate_query($sql,$lang );
346  if ( PEAR::isError($sql) ) return $sql;
347  if ( @$app->_conf['debug_sql'] ){
348  if ( is_array($sql) ){
349  foreach ($sql as $sqli){
350  error_log("After translation: ".$sqli);
351  }
352  } else {
353  error_log("After translation: ".$sql);
354  }
355 
356  }
357 
358 
359  }
360  if ( !isset($db) ){
361  $db = $this->app->db();
362  }
363  $update_insert_id = true;
364  if ( is_array($sql) ){
365  $loopctr = 0;
366 
367  foreach ($sql as $q){
368  if ( $loopctr++ > 0 and mysql_insert_id($db) ){
369  $this->_insert_id = mysql_insert_id($db);
370  $update_insert_id = false;
371  $q = str_replace("'%%%%%__MYSQL_INSERT_ID__%%%%%'", mysql_insert_id($db), $q );
372  }
373  if ( defined('DATAFACE_DEBUG_DB') or @$app->_conf['debug_sql']) echo "Performing query: '$q' <br>";
374  $res = mysql_query($q, $db);
375 
376  }
377  } else {
378  if ( defined('DATAFACE_DEBUG_DB') ) echo "Performing query: '$sql' <br>";
379  $this->db_hits++;
380  $res = mysql_query($sql, $db);
381 
382  }
383  if ( $update_insert_id ) $this->_insert_id = mysql_insert_id($db);
384  if ( $res and $refreshModTimes) Dataface_Table::getTableModificationTimes(true);
385  if ( $as_array and $isSelect ){
386  if ( !$res ) {
387 
388  return $res;
389  }
390  // We want to return this as an array rather than a resource
391  $out = array();
392  while ( $row = ($enumerated ? mysql_fetch_row($res) : mysql_fetch_assoc($res)) ){
393  $out[] = $row;
394  }
395 
396  $this->memcache_set($orig_sql, $lang, $out);
397  @mysql_free_result($res);
398 
399  return $out;
400 
401  }
402 
403  return $res;
404  }
405 
406  function insert_id(){
407  return $this->_insert_id;
408  }
409 
410 
411  public static function &getInstance(){
412  static $instance = null;
413  if ( $instance === null ){
414  //echo "In get instance";
415  $instance = new Dataface_DB();
416  }
417 
418  return $instance;
419  }
420 
421 
426  function writeCache(){
427  //echo "in write cache...";
428  //print_r($this);
429  if ( $this->_cacheDirtyFlag ){
430  //echo "Dirty flag";
431  // The cache has been updated so we have to write it.
432  ob_start();
433  echo '<?php
434  $cache = array();
435  ';
436  foreach ($this->_cache as $lang=>$values){
437  foreach ($values as $key=>$value){
438  if ( is_array($value) ){
439  foreach ($value as $innerValue){
440  echo '$cache[\''.$lang.'\'][\''.str_replace("'", "\\'", $key).'\'][] = \''.str_replace("'", "\\'", $innerValue).'\';
441  ';
442  }
443  } else {
444  echo '$cache[\''.$lang.'\'][\''.str_replace("'", "\\'", $key).'\'] = \''.str_replace("'", "\\'", $value).'\';
445  ';
446  }
447  }
448  }
449 
450  echo '
451  ?>';
452  $contents = ob_get_contents();
453  ob_end_clean();
454  if ( !file_exists(DATAFACE_CACHE_PATH) ) @mkdir(DATAFACE_CACHE_PATH);
455  $fh = @fopen(DATAFACE_CACHE_PATH.'/Dataface_DB.cache', 'w');
456  if ( !$fh or !fwrite($fh, $contents) ){
457  error_log("Failed to write DB cache", E_USER_ERROR);
458  }
459  @fclose($fh);
460  }
461  }
462 
463 
464  function registerBlob($blobData){
465  static $id=1;
466  $this->blobs[$id++] = $blobData;
467  return $id-1;
468 
469 
470  }
471 
472  function checkoutBlob($blobID){
473  if ( !isset( $this->blobs[$blobID]) ) return PEAR::raiseError(df_translate('scripts.Dataface.DB.checkoutBlob.BLOB_DOESNT_EXIST', "Blob with ID $blobID doesn't exist. ",array('blobID'=>$blobID)), DATAFACE_E_ERROR);
474 
475  $blob = $this->blobs[$blobID];
476  unset($this->blobs[$blobID]);
477  return $blob;
478  }
479 
480  function startTransaction(){ return mysql_query('begin', df_db() ); }
481  function commitTransaction(){ return mysql_query('commit', df_db() ); }
482  function rollbackTransaction(){ return mysql_query('rollback', df_db() ); }
483 
484  function memcache_get($sql, $lang=null){
485 
486  $app =& Dataface_Application::getInstance();
487 
488  $memcache =& $app->memcache;
489 
490  if ( !@$app->_conf['cache_queries'] ) {
491 
492  return null;
493  }
494 
495  $key = $this->memcache_get_key($sql, $lang);
496 
497 
498 
499  $tables = $this->getTableDependencies($sql, $lang);
500  if ( PEAR::isError($tables) ) return null;
501  // This is a list of the tables that would cause the cache to be invalidated.
502 
503  $modification_times = Dataface_Table::getTableModificationTimes();
504  $mtime = 0;
505  foreach ( $tables as $table){
506  if ( isset($modification_times[$table]) ) $mtime = max($mtime, $modification_times[$table]);
507  else {
508  $t =& Dataface_Table::loadTable($table);
509  if ( @$t->_atts['__source_tables__'] ){
510  $ts = explode(',', $t->_atts['__source_tables__']);
511  foreach ($ts as $tst){
512  if ( isset($modification_times[trim($tst)]) ){
513  $mtime = max($mtime, $modification_times[trim($tst)]);
514  } else {
515  $mtime = time();
516  break;
517  }
518  }
519  } else {
520  //echo "$table no modified date";
521  $mtime = time();
522  break;
523  }
524  unset($t);
525  }
526  }
527 
528 
529 
530  // Now we will get the cached value if it is newer than $mtime
531  $cache_mtime = 0;
532  if ( $memcache ) $cache_mtime = $this->memcache_mtime($key);
533  else $cache_mtime = $this->fcache_mtime($key);
534  //echo "Cache time for ".$this->fcache_path($key)." is $cache_mtime";
535  //echo "[$sql : $cache_mtime : $mtime]";
536  if ( $cache_mtime > $mtime ){
537  if ( $memcache ) {
538 
539  if ( $result = $memcache->get($key) ) {
540  return unserialize($result);
541  }
542  }
543  else if (($result = $this->fcache_get($key)) !== null ){
544 
545  return unserialize($result);
546  }
547  }
548 
549 
550 
551  return null;
552  }
553 
557  function memcache_mtime($key, $set=false){
558 
559  $key .= '&-action=mtime';
560  $key = md5($key);
561  if ( DATAFACE_EXTENSION_LOADED_APC and !$set ){
562  return apc_fetch($key);
563  } else if ( DATAFACE_EXTENSION_LOADED_APC and $set ){
564  apc_store($key, time());
565  } else if ( $set ){
566 
567  $_SESSION[$key] = time();
568  } else if ( !$set and isset($_SESSION[$key])){
569 
570  return $_SESSION[$key];
571  }
572  return 0;
573 
574 
575  }
576 
577  function fcache_mtime($key){
578  if ( file_exists($this->fcache_path($key)) ){
579  //echo "Checking mtime for $key : ".$this->fcache_path($key);
580  return filemtime($this->fcache_path($key));
581  } else {
582  //echo "File does not exist : ".$this->fcache_path($key);
583  return 0;
584  }
585  }
586 
587  function fcache_get($key){
588 
589  if ( file_exists($this->fcache_path($key)) ){
590  return file_get_contents($this->fcache_path($key));
591  }
592  return null;
593  }
594 
595  function fcache_set($key, $value){
596 
597  file_put_contents($this->fcache_path($key), serialize($value));
598  }
599 
600  function fcache_path($key){
601  return $this->_fcache_base_path.'/'.md5($key);
602  }
603 
604  function memcache_get_key($sql, $lang){
605 
606  $app =& Dataface_Application::getInstance();
607 
608  $auth =& Dataface_AuthenticationTool::getInstance();
609 
610 
611  $dbname = $app->_conf['_database']['name'];
612 
613  if ( !isset($lang) ) $lang = $app->_conf['lang'];
614 
615  $key = urlencode($dbname).'?-query='.urlencode($sql).'&-lang='.urlencode($lang);
616 
617  return md5($key);
618  }
619 
620  function memcache_set($sql, $lang, $value){
621 
622  $app =& Dataface_Application::getInstance();
623  $memcache =& $app->memcache;
624  if ( !$memcache and !@$app->_conf['cache_queries'] ) return null;
625 
626  $key = $this->memcache_get_key($sql, $lang);
627  if ( $memcache ){
628  $memcache->set($key, serialize($value), false, 0);
629  $this->memcache_mtime($key, true);
630  } else if ( @$app->_conf['cache_queries'] ){
631  //echo "Setting $sql $key ".$this->fcache_path($key);
632  $this->fcache_set($key, $value);
633  }
634 
635 
636  }
637 
638  function getTableDependencies($sql, $lang=null){
639  $app =& Dataface_Application::getInstance();
640  $key = $this->memcache_get_key($sql, $lang);
641  $key .= '&-action=deps';
642  $key = md5($key);
643  if ( DATAFACE_EXTENSION_LOADED_APC && !isset($_GET['--clear-cache']) ){
644  $deps = apc_fetch($key);
645  if ( is_array($deps) ) return $deps;
646  } else if ( isset($_SESSION[$key]) && !isset($_GET['--clear-cache']) ){
647  $deps = $_SESSION[$key];
648  if ( is_array($deps) ) return $deps;
649  }
650  // We actually need to calculate the dependencies, so we will
651  // parse the SQL query.
652  import('SQL/Parser.php');
653  $parser = new SQL_Parser( null, 'MySQL');
654  $data =& $parser->parse($sql);
655  if ( PEAR::isError($data) ){
656  return $data;
657  }
658  $tables = array_unique($data['all_tables']);
659 
660  if ( @$app->_conf['cache_queries_log'] ){
661  $fp = fopen('/tmp/querylog.log', 'a');
662  fwrite($fp, "\n[".date('Y-m-d H:i:s')."] Dependencies: ".implode(',', $tables)." ".$sql);
663  fclose($fp);
664  }
665  //import('SQL/Parser/wrapper.php');
666 
667 
668  //$wrapper = new SQL_Parser_wrapper($data);
669  //$tables = $wrapper->getTableNames();
670 
671  foreach ($tables as $tid=>$table){
672  if ( preg_match('/^dataface__view_(.*)_[a-z0-9]{32}$/', $table, $matches) ){
673  $tables[$tid] = $table = $matches[1];
674  }
675  $tobj =& Dataface_Table::loadTable($table,null,true);
676  if ( is_a($tobj, 'Dataface_Table') and isset($tobj->_atts['__dependencies__']) ){
677  $deps = array_map('trim', explode(',', $tobj->_atts['__dependencies__']));
678  $tables = array_merge($tables, $deps);
679 
680  }
681  }
682 
683  if ( isset($app->_conf['__dependencies__']) ){
684  $deps = array_map('trim',explode(',', $app->_conf['__dependencies']));
685  $tables = array_merge($tables, $deps);
686  }
687 
688  $deps = array_unique($tables);
689 
690  if ( DATAFACE_EXTENSION_LOADED_APC ){
691  apc_store($key, $deps);
692  } else {
693  $_SESSION[$key] = $deps;
694  }
695 
696 
697  return $deps;
698  }
699 
700 
701 }
Generated on Fri Mar 22 2013 09:40:33 for Xataface by   doxygen 1.8.1.2