core_sql.php

<?PHP
class mysql
{
    public  $DBHost;
    public  $DBName;
    public  $DBUser;
    public  $DBPass;
    private $checksum;
    public  $con;

    public function __construct($DBHost=null,$DBName=null,$DBUser=null,$DBPass=null)
    {
        if(empty($DBHost))
        {
            $this->DBHost = $DBHost;
            $this->DBName = $DBName;
            $this->DBUser = $DBUser;
            $this->DBPass = $DBPass;
            
            if(($this->DBHost!='')&&($this->DBUser!='')&&($this->DBPass!='')&&($this->DBName!=''))
            {
                $this->checksum=md5($this->DBHost.$this->DBUser.$this->DBPass.$this->DBName);
            }
            $this->con = mysql_connect($this->DBHost, $this->DBUser, $this->DBPass);
            if(! $this->con)
            {
                die("<b>core_sql: MySQL connection failed!</b> ".mysql_error());
            }
            mysql_select_db($this->DBName, $this->con);
        }
    }
    
    final public function query($sql)
    {
        return mysql_query($sql);
    }
    
    final public function write($sql)
    {
        return $this->query($sql);
    }
    
    final public function close()
    {
        return mysql_close($this->con);
    }
    
    final public function test()
    {
        if($this->checksum==md5($this->DBHost.$this->DBUser.$this->DBPass.$this->DBName))
        {
            if(! $this->con)
            {
                return mysql_error();
            }
            else
            {
                return true;
            }
        }
    }
    
    final public function integrity($type)
    {
        $handle = fopen("firststart.json", "r");
        $contents = fread($handle, filesize($filename));
        fclose($handle);
        $contents = json_decode($contents,false);
        if(md5($_SERVER['SERVER_NAME'].$_SERVER['SERVER_ADDR'].$this->DBHost.$this->DBUser.$type)!=$contents->hash)
        {
            die('Integrity_Error: Illegal system operation!');
        }
        return true;
    }
}

class mysqlEdit extends mysql
{
    private $construct;
    private $key;
    private $table;
    private $sql;
    private $result;
    private $column;
    private $setupdate;
    private $colout;
    private $out;
    private $metaout;
    private $csum;
    private $sizeout;

    public function newColumn($name,$type="INT")
    {
        $this->construct .= ' , `'.$name.'` '.strtoupper($type).'';
    }
    
    public function prepareTable($name)
    {
        $this->table = $name;
    }
    
    public function uniqueKey($keyName)
    {
        $this->key .= ' , UNIQUE KEY `'.$this->table.'` (`'.$keyName.'`)';
    }
    
    public function createTable($name)
    {
        $this->table = $name;
        $this->query('CREATE TABLE IF NOT EXISTS `'.$name.'` ( `id` INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(`id`)'.$this->construct.');');
        $this->construct = null;
    }
    
    public function openTable($name)
    {
        $this->table = $name;
        $this->sql="SHOW COLUMNS FROM ".$this->DBName.".".$this->table;
        $this->con=mysql_connect($this->DBHost,$this->DBUser,$this->DBPass);
        mysql_select_db($this->DBName, $this->con);
        $this->result=mysql_query($this->sql);
        $this->column=null;
        while($row=mysql_fetch_array($this->result))
        {
            $this->column.='`'.$row['Field'].'`,';
        }
    }
    
    public function insert($values)
    {
        $this->query('INSERT INTO '.$this->table.' ('.substr($this->column, 0, -1).') VALUES (null,'.$values.');');
    }
    
    public function set($column,$value)
    {
        if(is_numeric($value))
        {
            $this->setupdate.='`'.$column.'`='.$value.',';
        }
        else
        {
            $this->setupdate.='`'.$column.'`="'.$value.'",';
        }
    }
    
    public function update($if,$sets=null)
    {
        $this->query('UPDATE '.$this->table.' SET '.substr($this->setupdate, 0, -1).$sets.' WHERE '.$if.';');
    }
    
    public function delete($if)
    {
        if($if=="all")
        {
            $this->query('DELETE FROM `'.$this->table.'` WHERE `id`>0;');
        }
        else
        {
            $this->query('DELETE FROM `'.$this->table.'` WHERE '.$if.';');
        }
    }
    
    public function output($if="`id`>0",$order="`id` ASC",$limit=1000)
    {
        $this->sql="SELECT * FROM `".$this->table."` WHERE ".$if." ORDER BY ".$order." LIMIT ".$limit.";";
        $this->con=mysql_connect($this->DBHost,$this->DBUser,$this->DBPass);
        mysql_select_db($this->DBName, $this->con);
        $this->result=mysql_query($this->sql);
        $this->colout=explode(",",str_replace("`","",substr($this->column, 0, -1)));
        $i=0;
        $j=0;
        $this->out = array('header'=>$this->colout,'line'=>array(array()));
        $this->csum = md5($this->con);
        while($row=mysql_fetch_array($this->result))
        {
            $i=0;
            $j++;
            foreach($this->colout as $val)
            {
                $this->out['line'][$j][$i++]=$row[$val];
            }
        }
        return $this->out;
    }
}

class mysqlInterface extends mysqlEdit
{
    protected $save;
    protected $mysqli;
    protected $connect;
    private $default;
    
    public function dbCreateTable($array)
    {
        $foreignGenerator='';
        foreach($array as $key=>$val)
        {
            foreach($val as $key_col=>$val_col)
            {
                $data=null;
                
                foreach($val_col as $key_att=>$val_att)
                {
                    switch(strtolower($key_att))
                    {
                        case 'type':
                            $data.=strtoupper($val_col[$key_att]);
                            break;
                        case 'null':
                            $data.=($val_col[$key_att]===false ? ' NOT NULL' : '');
                            break;
                        case 'auto_increment':
                            $data.=($val_col[$key_att]===false ? '' : ' AUTO_INCREMENT');
                            break;
                        case 'primary_key':
                            $data.=($val_col[$key_att]===false ? '' : ',PRIMARY KEY ('.$key_col.')');
                            break;
                        case 'foreign_key':
                            if(is_string($val_att))
                            {
                                $this->query('SET foreign_key_checks = 1');
                                $data.=($val_col[$key_att]===false ? '' : ',FOREIGN KEY ('.$key_col.') REFERENCES '.$val_att);
                            }
                            else
                            {
                                foreach($val_att as $key_val_att=>$sub_val_att)
                                {
                                    $foreignGenerator = $this->addForeignKey($key, $key_val_att, $key_col, $sub_val_att);
                                }
                            }
                            break;
                        case 'unique_key':
                        case 'unique':
                            $data.=($val_col[$key_att]===false ? '' : ',UNIQUE KEY ('.$key_col.')');
                            break;
                    }
                }
                $this->construct .= ',`'.$key_col.'` '.$data.'';
            }
            $this->save .= 'CREATE TABLE IF NOT EXISTS `'.$key.'` ('.substr($this->construct,1).');'.$foreignGenerator;
        }
    }
    
    private function addForeignKey($masterTable, $referenceTable, $master, $reference)
    {
        $construct = "SET foreign_key_checks=1;";
        $construct .= "ALTER TABLE $masterTable ADD FOREIGN KEY ($master) REFERENCES $referenceTable ($reference);";
        return $construct;
    }
    
    public function insert($array,$stringRewrite=true)
    {
        foreach($array as $key=>$val)
        {
            $this->save.='INSERT INTO `'.$key.'`';
            $col=' (';
            $values=' VALUES (';
            foreach($val as $sub_key=>$sub_val)
            {
                $col.=$sub_key.',';
                if((is_string($sub_val))&&($stringRewrite))
                {
                    $values.="'".$sub_val."',";
                }
                else
                {
                    $values.=$sub_val.',';
                }
            }
            $col=substr($col, 0, -1);
            $values=substr($values, 0, -1);
            $col=$col.')';
            $values=$values.')';
            $this->save.=$col.$values.';';
        }
    }
    
    public function filter($def)
    {
        foreach($def as $key=>$val)
        {
            foreach($val as $sub_key=>$sub_val)
            {
                $this->default.=$val.'.'.$sub_val.',';
            }
        }
        $this->default=substr($this->default,0,-1);
    }
    
    public function update($array)
    {
        $this->select($array,true);
    }
    
    public function select($array,$update=false)
    {
        if(empty($this->default))
        {
            $this->default='*';
        }
        foreach($array as $key=>$val)
        {
            if($update)
            {
                $this->save.='UPDATE `'.$key.'` ';
            }
            else
            {
                $this->save.='SELECT '.$this->default.' FROM `'.$key.'` ';
            }
            
            foreach($val as $key_col=>$val_col)
            {
                $data=null;
        
                switch(strtolower($key_col))
                {
                    case 'if':
                    case 'where':
                    case 'condition':
                        $data_condition.=' WHERE ';
                        foreach($val_col as $key_att=>$val_att)
                        {
                            switch(strtolower($key_att))
                            {
                                case '0':
                                    $data_condition.=$val_att;
                                    break;
                                case 'or':
                                    $data_condition.=' OR '.$val_att;
                                    break;
                                default:
                                    $data_condition.=' AND '.$val_att;
                                    break;
                            }
                        }
                    break;
                    case 'between':
                        $data_condition.=' BETWEEN '.$key_att.' AND '.$val_att;
                    break;
                    case 'set':
                        $data_set.=$key_att.'='.$val_att.',';
                    break;
                    case 'sort':
                        $data_sort=' ORDER BY ';
                        foreach($val_col as $key_att=>$val_att)
                        {
                            switch(strtolower($key_att))
                            {
                                case 'asc':
                                    $data_sort.=$val_att.' ASC';
                                    break;
                                case 'desc':
                                    $data_sort.=$val_att.' DESC';
                                    break;
                            }
                        }
                    break;
                    case 'like':
                        $data_like=' LIKE '.$val_col;
                    break;
                    case 'start':
                        $data_limit_start=$val_col.',';
                    break;
                    case 'limit':
                        $data_limit_max=' '.$val_col;
                    break;
                    case 'fulljoin':
                    case 'fjoin':
                    case 'full':
                        $data_join.=' FULL OUTER JOIN '.$key_att.' ON '.$key.'.'.$val_att[0].'='.$key_att.'.'.$val_att[1];
                    break;
                    case 'innerjoin':
                    case 'ijoin':
                    case 'join':
                    case 'inner':
                        $data_join.=' INNER JOIN '.$key_att.' ON '.$key.'.'.$val_att[0].'='.$key_att.'.'.$val_att[1];
                    break;
                    case 'leftjoin':
                    case 'ljoin':
                    case 'left':
                        $data_join.=' INNER JOIN '.$key_att.' ON '.$key.'.'.$val_att[0].'='.$key_att.'.'.$val_att[1];
                    break;
                    case 'rightjoin':
                    case 'rjoin':
                    case 'right':
                        $data_join.=' INNER JOIN '.$key_att.' ON '.$key.'.'.$val_att[0].'='.$key_att.'.'.$val_att[1];
                    break;
                    case 'ignore_first':
                    break;
                    case 'ignore_last':
                    break;
                }
            }
            $this->save.=(isset($data_set)?' SET '.substr($data_set,0,-1):'').$data_condition.$data_like.$data_sort.(isset($data_limit_max)? ' LIMIT '.$data_limit_start.$data_limit_max : '').';';
        }
        return $this->save;
    }
    
    public function config()
    {
        if(empty($this->DBHost))
        {
            $this->mysqli=array(
                'dbhost'=>database::host,
                'dbname'=>database::name,
                'dbuser'=>database::user,
                'dbpass'=>database::pass
            );
        }
        else
        {
            $this->mysqli=array(
                'dbhost'=>$this->DBHost,
                'dbname'=>$this->DBName,
                'dbuser'=>$this->DBUser,
                'dbpass'=>$this->DBPass
            );
        }
    }
    
    public function connect()
    {
        $this->config();
        $this->connect = new mysqli($this->mysqli['dbhost'], $this->mysqli['dbuser'], $this->mysqli['dbpass'], $this->mysqli['dbname']);
        if($this->connect->connect_errno)
        {
            $this->mysqli['dberror']['message'] = "Failed to connect to MySQL: (" . $this->connect->connect_errno . ") " . $this->connect->connect_error;
            $this->mysqli['dberror']['code']    = 'mysqlInterface:001';
            try 
            {
                log::vd($this->mysqli);
            } 
            catch(Exception $e) 
            {
                var_dump($this->mysqli);
            }
            
            die();
        }
    }
    
    public function validator()
    {
        if(!$this->connect->multi_query($this->save))
        {
            return false;
        }
        else
        {
            return true;
        }
    }
    
    public function debug()
    {
        return ($this->validator()? $this->connect->errno : 'Query is OK');
    }
    
    public function execute()
    {
        if(!$this->connect->multi_query($this->save))
        {
            $this->mysqli['dberror']['query']   = $this->save;
            $this->mysqli['dberror']['message'] = "Multi query failed: (" . $this->connect->errno . ") " . $this->connect->error;
            $this->mysqli['dberror']['code']    = 'mysqlInterface:002';
            try 
            {
                log::vd($this->mysqli);
            } 
            catch(Exception $e)
            {
                var_dump($this->mysqli);
            }
        }
        else 
        {
            $this->save = null;
        }
        
        $result=array();
        do 
        {
            if($res = $this->connect->store_result())
            {
                while ($row = $res->fetch_array())
                {
                    $result[] = $row;
                }
                $res->free();
            }
        }
        while($this->connect->more_results() && $this->connect->next_result());
        
        return $result;
    }
}

if(empty($hash['hash']))
{
    $mysql = new mysqlEdit($DBHost,$DBName,$DBUser,$DBPass);
}
else
{
    $mysql = new mysqlInterface();
    $mysql->config();
}
?>