OpenSencillo  2016.106
Long live the simplicity of PHP
 All Data Structures Namespaces Files Functions Pages
core_sql.php
1 <?PHP
11 class mysql
12 {
13  public $DBHost;
14  public $DBName;
15  public $DBUser;
16  public $DBPass;
17  private $checksum;
18  public $con;
19 
27  public function __construct($DBHost=null,$DBName=null,$DBUser=null,$DBPass=null)
28  {
29  if(!empty($DBHost))
30  {
31  $this->DBHost = $DBHost;
32  $this->DBName = $DBName;
33  $this->DBUser = $DBUser;
34  $this->DBPass = $DBPass;
35 
36  if(($this->DBHost!='')&&($this->DBUser!='')&&($this->DBPass!='')&&($this->DBName!=''))
37  {
38  $this->checksum=md5($this->DBHost.$this->DBUser.$this->DBPass.$this->DBName);
39  }
40  $this->con = mysql_connect($this->DBHost, $this->DBUser, $this->DBPass);
41  if(! $this->con)
42  {
43  die("<b>core_sql: MySQL connection failed!</b> ".mysql_error());
44  }
45  mysql_select_db($this->DBName, $this->con);
46  }
47  }
48 
54  final public function query($sql)
55  {
56  return mysql_query($sql);
57  }
58 
64  final public function write($sql)
65  {
66  return $this->query($sql);
67  }
68 
73  final public function close()
74  {
75  return mysql_close($this->con);
76  }
77 
82  final public function test()
83  {
84  if($this->checksum==md5($this->DBHost.$this->DBUser.$this->DBPass.$this->DBName))
85  {
86  if(! $this->con)
87  {
88  return mysql_error();
89  }
90  else
91  {
92  return true;
93  }
94  }
95  }
96 
101  final public function integrity($type)
102  {
103  $handle = fopen("firststart.json", "r");
104  $contents = fread($handle, filesize($filename));
105  fclose($handle);
106  $contents = json_decode($contents,false);
107  if(md5($_SERVER['SERVER_NAME'].$_SERVER['SERVER_ADDR'].$this->DBHost.$this->DBUser.$type)!=$contents->hash)
108  {
109  die('Integrity_Error: Illegal system operation!');
110  }
111  return true;
112  }
113 }
114 
124 class mysqlEdit extends mysql
125 {
126  private $construct;
127  private $key;
128  private $table;
129  private $sql;
130  private $result;
131  private $column;
132  private $setupdate;
133  private $colout;
134  private $out;
135  private $metaout;
136  private $csum;
137  private $sizeout;
138 
144  public function newColumn($name,$type="INT")
145  {
146  $this->construct .= ' , `'.$name.'` '.strtoupper($type).'';
147  }
148 
153  public function prepareTable($name)
154  {
155  $this->table = $name;
156  }
157 
162  public function uniqueKey($keyName)
163  {
164  $this->key .= ' , UNIQUE KEY `'.$this->table.'` (`'.$keyName.'`)';
165  }
166 
171  public function createTable($name)
172  {
173  $this->table = $name;
174  $this->query('CREATE TABLE IF NOT EXISTS `'.$name.'` ( `id` INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(`id`)'.$this->construct.');');
175  $this->construct = null;
176  }
177 
182  public function openTable($name)
183  {
184  $this->table = $name;
185  $this->sql="SHOW COLUMNS FROM ".$this->DBName.".".$this->table;
186  $this->con=mysql_connect($this->DBHost,$this->DBUser,$this->DBPass);
187  mysql_select_db($this->DBName, $this->con);
188  $this->result=mysql_query($this->sql);
189  $this->column=null;
190  while($row=mysql_fetch_array($this->result))
191  {
192  $this->column.='`'.$row['Field'].'`,';
193  }
194  }
195 
201  public function insert($values)
202  {
203  $this->query('INSERT INTO '.$this->table.' ('.substr($this->column, 0, -1).') VALUES (null,'.$values.');');
204  }
205 
211  public function set($column,$value)
212  {
213  if(is_numeric($value))
214  {
215  $this->setupdate.='`'.$column.'`='.$value.',';
216  }
217  else
218  {
219  $this->setupdate.='`'.$column.'`="'.$value.'",';
220  }
221  }
222 
229  public function update($if,$sets=null)
230  {
231  $this->query('UPDATE '.$this->table.' SET '.substr($this->setupdate, 0, -1).$sets.' WHERE '.$if.';');
232  }
233 
240  public function delete($if)
241  {
242  if($if=="all")
243  {
244  $this->query('DELETE FROM `'.$this->table.'` WHERE `id`>0;');
245  }
246  else
247  {
248  $this->query('DELETE FROM `'.$this->table.'` WHERE '.$if.';');
249  }
250  }
251 
259  public function output($if="`id`>0",$order="`id` ASC",$limit=1000)
260  {
261  $this->sql="SELECT * FROM `".$this->table."` WHERE ".$if." ORDER BY ".$order." LIMIT ".$limit.";";
262  $this->con=mysql_connect($this->DBHost,$this->DBUser,$this->DBPass);
263  mysql_select_db($this->DBName, $this->con);
264  $this->result=mysql_query($this->sql);
265  $this->colout=explode(",",str_replace("`","",substr($this->column, 0, -1)));
266  $i=0;
267  $j=0;
268  $this->out = array('header'=>$this->colout,'line'=>array(array()));
269  $this->csum = md5($this->con);
270  while($row=mysql_fetch_array($this->result))
271  {
272  $i=0;
273  $j++;
274  foreach($this->colout as $val)
275  {
276  $this->out['line'][$j][$i++]=$row[$val];
277  }
278  }
279  return $this->out;
280  }
281 }
282 
286 class mysqlInterface extends mysqlEdit
287 {
288  protected $save;
289  protected $mysqli;
290  protected $connect;
291  private $default;
292 
330  public function dbCreateTable($array)
331  {
332  $foreignGenerator='';
333  foreach($array as $key=>$val)
334  {
335  foreach($val as $key_col=>$val_col)
336  {
337  $data=null;
338 
339  foreach($val_col as $key_att=>$val_att)
340  {
341  switch(strtolower($key_att))
342  {
343  case 'type':
344  $data.=strtoupper($val_col[$key_att]);
345  break;
346  case 'null':
347  $data.=($val_col[$key_att]===false ? ' NOT NULL' : '');
348  break;
349  case 'auto_increment':
350  $data.=($val_col[$key_att]===false ? '' : ' AUTO_INCREMENT');
351  break;
352  case 'primary_key':
353  $data.=($val_col[$key_att]===false ? '' : ',PRIMARY KEY ('.$key_col.')');
354  break;
355  case 'foreign_key':
356  if(is_string($val_att))
357  {
358  $this->query('SET foreign_key_checks = 1');
359  $data.=($val_col[$key_att]===false ? '' : ',FOREIGN KEY ('.$key_col.') REFERENCES '.$val_att);
360  }
361  else
362  {
363  foreach($val_att as $key_val_att=>$sub_val_att)
364  {
365  $foreignGenerator = $this->addForeignKey($key, $key_val_att, $key_col, $sub_val_att);
366  }
367  }
368  break;
369  case 'unique_key':
370  case 'unique':
371  $data.=($val_col[$key_att]===false ? '' : ',UNIQUE KEY ('.$key_col.')');
372  break;
373  }
374  }
375  $this->construct .= ',`'.$key_col.'` '.$data.'';
376  }
377  $this->save .= 'CREATE TABLE IF NOT EXISTS `'.$key.'` ('.substr($this->construct,1).');'.$foreignGenerator;
378  }
379  }
380 
388  private function addForeignKey($masterTable, $referenceTable, $master, $reference)
389  {
390  $construct = "SET foreign_key_checks=1;";
391  $construct .= "ALTER TABLE $masterTable ADD FOREIGN KEY ($master) REFERENCES $referenceTable ($reference);";
392  return $construct;
393  }
394 
405  public function insert($array,$stringRewrite=true)
406  {
407  foreach($array as $key=>$val)
408  {
409  $this->save.='INSERT INTO `'.$key.'`';
410  $col=' (';
411  $values=' VALUES (';
412  foreach($val as $sub_key=>$sub_val)
413  {
414  $col.='`'.$sub_key.'`,';
415  if((is_string($sub_val))&&($stringRewrite))
416  {
417  $values.="'".$sub_val."',";
418  }
419  else
420  {
421  $values.=$sub_val.',';
422  }
423  }
424  $col=substr($col, 0, -1);
425  $values=substr($values, 0, -1);
426  $col=$col.')';
427  $values=$values.')';
428  $this->save.=$col.$values.';';
429  }
430  }
431 
442  public function filter($def)
443  {
444  foreach($def as $key=>$val)
445  {
446  foreach($val as $sub_key=>$sub_val)
447  {
448  $this->default.=$val.'.'.$sub_val.',';
449  }
450  }
451  $this->default=substr($this->default,0,-1);
452  }
453 
474  public function update($array)
475  {
476  $this->select($array,true);
477  }
478 
507  public function select($array,$update=false)
508  {
509  if(empty($this->default))
510  {
511  $this->default='*';
512  }
513  foreach($array as $key=>$val)
514  {
515  if($update)
516  {
517  $this->save.='UPDATE `'.$key.'` ';
518  }
519  else
520  {
521  $this->save.='SELECT '.$this->default.' FROM `'.$key.'` ';
522  }
523 
524  foreach($val as $key_col=>$val_col)
525  {
526  $data=null;
527 
528  switch(strtolower($key_col))
529  {
530  case 'if':
531  case 'where':
532  case 'condition':
533  $data_condition.=' WHERE ';
534  foreach($val_col as $key_att=>$val_att)
535  {
536  switch(strtolower($key_att))
537  {
538  case '0':
539  $data_condition.=$val_att;
540  break;
541  case 'or':
542  $data_condition.=' OR '.$val_att;
543  break;
544  default:
545  $data_condition.=' AND '.$val_att;
546  break;
547  }
548  }
549  break;
550  case 'between':
551  $data_condition.=' BETWEEN '.$key_att.' AND '.$val_att;
552  break;
553  case 'set':
554  $data_set.=$key_att.'='.$val_att.',';
555  break;
556  case 'sort':
557  $data_sort=' ORDER BY ';
558  $data_sort_arr=array();
559  foreach($val_col as $key_att=>$val_att)
560  {
561  switch(strtolower($key_att))
562  {
563  case 'asc':
564  $data_sort_arr[]=$val_att.' ASC';
565  break;
566  case 'desc':
567  $data_sort_arr[]=$val_att.' DESC';
568  break;
569  }
570  }
571  $data_sort.=implode(',',$data_sort_arr);
572  break;
573  case 'like':
574  $data_like=' LIKE '.$val_col;
575  break;
576  case 'start':
577  $data_limit_start=$val_col.',';
578  break;
579  case 'limit':
580  $data_limit_max=' '.$val_col;
581  break;
582  case 'fulljoin':
583  case 'fjoin':
584  case 'full':
585  $data_join.=' FULL OUTER JOIN '.$key_att.' ON '.$key.'.'.$val_att[0].'='.$key_att.'.'.$val_att[1];
586  break;
587  case 'innerjoin':
588  case 'ijoin':
589  case 'join':
590  case 'inner':
591  $data_join.=' INNER JOIN '.$key_att.' ON '.$key.'.'.$val_att[0].'='.$key_att.'.'.$val_att[1];
592  break;
593  case 'leftjoin':
594  case 'ljoin':
595  case 'left':
596  $data_join.=' INNER JOIN '.$key_att.' ON '.$key.'.'.$val_att[0].'='.$key_att.'.'.$val_att[1];
597  break;
598  case 'rightjoin':
599  case 'rjoin':
600  case 'right':
601  $data_join.=' INNER JOIN '.$key_att.' ON '.$key.'.'.$val_att[0].'='.$key_att.'.'.$val_att[1];
602  break;
603  case 'ignore_first':
607  break;
608  case 'ignore_last':
612  break;
613  }
614  }
615  $this->save.=(isset($data_set)?' SET '.substr($data_set,0,-1):'').$data_join.$data_condition.$data_like.$data_sort.(isset($data_limit_max)? ' LIMIT '.$data_limit_start.$data_limit_max : '').';';
616  }
621  return $this->save;
622  }
623 
628  public function addQuery($sql)
629  {
630  $this->save.=$sql;
631  }
632 
636  public function config()
637  {
638  if(empty($this->DBHost))
639  {
640  $this->mysqli=array(
641  'dbhost'=>database::host,
642  'dbname'=>database::name,
643  'dbuser'=>database::user,
644  'dbpass'=>database::pass
645  );
646  }
647  else
648  {
649  $this->mysqli=array(
650  'dbhost'=>$this->DBHost,
651  'dbname'=>$this->DBName,
652  'dbuser'=>$this->DBUser,
653  'dbpass'=>$this->DBPass
654  );
655  }
656  }
657 
661  public function connect()
662  {
663  $this->config();
664  $this->connect = new mysqli($this->mysqli['dbhost'], $this->mysqli['dbuser'], $this->mysqli['dbpass'], $this->mysqli['dbname']);
665  if($this->connect->connect_errno)
666  {
667  $this->mysqli['dberror']['message'] = "Failed to connect to MySQL: (" . $this->connect->connect_errno . ") " . $this->connect->connect_error;
668  $this->mysqli['dberror']['code'] = 'mysqlInterface:001';
669  try
670  {
671  log::vd($this->mysqli);
672  }
673  catch(Exception $e)
674  {
675  var_dump($this->mysqli);
676  }
677 
678  die();
679  }
680  }
681 
686  public function validator()
687  {
688  if(!$this->connect->multi_query($this->save))
689  {
690  return false;
691  }
692  else
693  {
694  return true;
695  }
696  }
697 
703  public function debug()
704  {
705  return ($this->validator()? $this->connect->errno : 'Query is OK');
706  }
707 
712  public function execute()
713  {
714  if(!$this->connect->multi_query($this->save))
715  {
716  $this->mysqli['dberror']['query'] = $this->save;
717  $this->mysqli['dberror']['message'] = "Multi query failed: (" . $this->connect->errno . ") " . $this->connect->error;
718  $this->mysqli['dberror']['code'] = 'mysqlInterface:002';
719  try
720  {
721  log::vd($this->mysqli);
722  }
723  catch(Exception $e)
724  {
725  var_dump($this->mysqli);
726  }
727  }
728  else
729  {
730  $this->save = null;
731  }
732 
733  $result=array();
734  do
735  {
736  if($res = $this->connect->store_result())
737  {
738  while ($row = $res->fetch_array())
739  {
740  $result[] = $row;
741  }
742  $res->free();
743  }
744  }
745  while($this->connect->more_results() && $this->connect->next_result());
746 
747  return $result;
748  }
749 }
750 
751 if(empty($hash['hash']))
752 {
753  $mysql = new mysqlEdit($DBHost,$DBName,$DBUser,$DBPass);
754 }
755 else
756 {
757  $mysql = new mysqlInterface();
758  $mysql->config();
759 }
760 ?>