.gitignore000064400000000036144761607260006550 0ustar00/vendor/ /tests/ composer.lockcomposer.json000064400000000673144761607260007311 0ustar00{ "name": "boru/dhdb", "type": "library", "autoload": { "psr-4": { "boru\\dhdb\\": "src/" } }, "authors": [ { "name": "Daniel Hayes", "email": "dhayes@boruapps.com" } ], "require": { "boru/dhutils": "^1" }, "repositories": [ { "type": "composer", "url": "https://satis.boruapps.com" } ] } instructions-composer.txt000064400000000311144761607260011706 0ustar00{ "require": { "boru/dhout": "dev-master" }, "repositories": [ { "type": "composer", "url": "https://satis.boruapps.com" } ] }src/Error.php000064400000003640144761607260007155 0ustar00setMessage($message); $this->setQuery($query); $this->setParams($params); $this->setTrace($trace); } public function log() { dhGlobal::error("Query Error with query:".PHP_EOL.$this->query.PHP_EOL." "); dhGlobal::error("Message:".PHP_EOL.$this->message.PHP_EOL." "); dhGlobal::error("Stack Trace:".PHP_EOL.$this->trace.PHP_EOL." "); } /** * Get the value of message */ public function getMessage() { return $this->message; } /** * Set the value of message * * @return self */ public function setMessage($message) { $this->message = $message; return $this; } /** * Get the value of query */ public function getQuery() { return $this->query; } /** * Set the value of query * * @return self */ public function setQuery($query) { $this->query = $query; return $this; } /** * Get the value of params */ public function getParams() { return $this->params; } /** * Set the value of params * * @return self */ public function setParams($params) { $this->params = $params; return $this; } /** * Get the value of trace */ public function getTrace() { return $this->trace; } /** * Set the value of trace * * @return self */ public function setTrace($trace) { $this->trace = $trace; return $this; } }src/Query.php000064400000011546144761607260007175 0ustar00rawQueryString = $queryString; } public function getQuery($interpolate=false) { $sql = $this->driver->fromQuery($this); if($interpolate) { return $this->driver->interpolateQuery($sql,$this->value); } return $sql; } public function insert($i,$v=null) { $this->type = "insert"; return $this->column($i,$v); } public function select($s) { $this->type = "select"; return $this->column($s); } public function update($i,$v) { $this->type = "update"; return $this->column($i,$v); } public function create($t,$ifNotExists=false) { $this->type = "create"; $this->ifNotExists = $ifNotExists; return $this->table($t); } public function alter($t) { $this->type = "alter"; return $this->table($t); } public function into(...$args) { return $this->table(...$args); } public function from(...$args) { return $this->table(...$args); } public function join(...$args) { return $this->table(...$args); } public function where($c,$v=null) { return $this->condition($c,$v); } public function add($col,$type=null,$extra=null) { return $this->schemaColumn("add",$col,$type,$extra); } public function remove($col) { return $this->schemaColumn("drop",$col); } public function change($col,$newcol,$type=null,$extra=null) { return $this->schemaColumn("change",[$col,$newcol],$type,$extra); } public function addIndex($name,$cols=[],$type="key") { return $this->schemaColumn("addIndex",$name,strtolower($type),$cols); } public function key($name,$cols=[],$type="key") { return $this->schemaColumn("addIndex",$name,strtolower($type),$cols); } public function index($name,$cols=[],$type="key") { return $this->schemaColumn("addIndex",$name,strtolower($type),$cols); } public function dropIndex($name) { return $this->schemaColumn("dropIndex",$name); } public function meta($key,$value=null) { if($this->type == "create" || $this->type == "alter") { $this->tableMeta[] = ["key"=>$key,"value"=>$value]; } return $this; } public function schemaColumn($action="add",$col,$type=null,$extra=null) { $this->schemaColumns[] = ["action"=>$action,"column"=>$col,"type"=>$type, "extra"=>$extra]; return $this; } public function column($c,$v=null,$extraIfAlterOrAdd=null) { if($this->type == "create" || $this->type == "alter") { return $this->add($c,$v,$extraIfAlterOrAdd); } else { if(is_array($c)) { foreach($c as $col) { $this->columns[] = $col; } } else { $this->columns[] = $c; } if(!is_null($v)) { if(is_array($v)) { foreach($v as $val) { $this->value($v); } } else { $this->value($v); } } return $this; } } public function condition($c,$v=null) { if(is_array($c)) { foreach($c as $col) { $this->conditions[] = $c; } } else { $this->conditions[] = $c; } if(!is_null($v)) { if(is_array($v)) { foreach($v as $val) { $this->value($val); } } else { $this->value($v); } } return $this; } public function table($t,$type=null,$on=null) { $this->tables[] = ["name"=>$t,"type"=>$type,"on"=>$on]; return $this; } public function values($arr) { foreach($arr as $v) { $this->value($v); } return $this; } public function value($v,$vv=null) { if(!is_null($vv)) { $this->values[$v] = $vv; } else { if(is_array($v)) { return $this->values($v); } else { $this->values[] = $v; } } return $this; } }src/Result.php000064400000000734144761607260007343 0ustar00setTableName($tableName); } if(!is_null($file)) { $this->setFile($file); if(!is_null($this->file)) { $this->load(); } } } public function get($version=null) { $array["name"] = $this->getTableName(); $array["meta"] = $this->getMeta($version); $array["columns"] = $this->getColumns($version); $array["keys"] = $this->getKeys($version); return $array; } public function getAll() { $array["tableName"] = $this->tableName; $array["meta"] = $this->meta; $array["columns"] = $this->columns; $array["keys"] = $this->keys; $array["history"] = $this->history; $array["versions"] = $this->versions; $array["changes"] = $this->changes; return $array; } public function save($makeVersion=true) { if(!empty($this->changes) && $makeVersion) { $this->version++; $versionString = $this->getVersionString(); $this->meta("comment","dhDB::".$versionString); $this->history[$versionString] = $this->changes; $this->versions[$versionString]["columns"] = $this->columns; $this->versions[$versionString]["keys"] = $this->keys; $this->versions[$versionString]["meta"] = $this->meta; $this->changes = []; } if(!is_null($this->file)) { $this->file->write(json_encode($this->getAll(),JSON_PRETTY_PRINT)); } return $this; } public function load($clean=true) { if(!is_null($this->file)) { $data = $this->file->content(["json"=>true]); if(is_array($data)) { if(!$clean) { $this->meta = dhGlobal::getVal($data,"meta",null); $this->columns = dhGlobal::getVal($data,"columns",null); $this->keys = dhGlobal::getVal($data,"keys",null); } $this->history = dhGlobal::getVal($data,"history",null); $this->versions = dhGlobal::getVal($data,"versions",null); $this->version = 0; if(is_array($this->versions)) { foreach($this->versions as $version=>$d) { $version = intval(dhGlobal::trimString("v_",$version,dhGlobal::TRIM_START)); if($version>$this->version) { $this->version = $version; } } } } } return $this; } public function getColumnHistory($column) { $columnHistory = []; if(!empty($this->history)) { foreach($this->history as $version=>$changes) { foreach($changes as $change) { if($change["column"] == $column && strpos($change["action"],"Index") === false) { $columnHistory[$version][] = $change; } } } } return $columnHistory; } public function replayHistory($version=null) { if(!is_null($version)) { $version = $this->getVersionString($version); if(isset($this->history[$version])) { $this->replayHistoryVersion($version); return true; } return false; } else { if(!empty($this->history)) { $versions = array_keys($this->history); foreach($versions as $version) { $this->replayHistoryVersion($version); } return true; } return false; } return false; } protected function replayHistoryVersion($version) { foreach($this->history[$version] as $entry) { $this->schemaColumnEntry($entry); } } public function version($version=null) { if(is_null($version)) { return $this->version; } if($version === true) { $this->version++; } else { $this->version = $version; } return $this; } public function add($col,$type=null,$extra=null) { return $this->schemaColumn("add",$col,$type,$extra); } public function remove($col) { return $this->schemaColumn("drop",$col); } public function change($col,$newcol,$type=null,$extra=null) { return $this->schemaColumn("change",[$col,$newcol],$type,$extra); } public function addIndex($name,$cols=[],$type="key") { return $this->schemaColumn("addIndex",$name,strtolower($type),$cols); } public function key($name,$cols=[],$type="key") { return $this->schemaColumn("addIndex",$name,strtolower($type),$cols); } public function index($name,$cols=[],$type="key") { return $this->schemaColumn("addIndex",$name,strtolower($type),$cols); } public function dropIndex($name) { return $this->schemaColumn("dropIndex",$name); } public function meta($key,$value=null,$version=null) { if($version == null) { $this->meta[$key] = ["key"=>$key,"value"=>$value]; } else { $version = $this->getVersionString($version); $this->versions[$version]["meta"][$key] = ["key"=>$key,"value"=>$value]; } return $this; } protected function parseColExtra($extra,$glue=" ") { $null = dhGlobal::getVal($extra,"null",false); $collate = dhGlobal::getVal($extra,"collate",false); $charset = dhGlobal::getVal($extra,"charset",false); $default = dhGlobal::getVal($extra,"default",false); $auto_incr = dhGlobal::getVal($extra,"auto_increment",false); if(!$charset && $collate !== false) { list($charset,$unused) = explode("_",$charset,2); } $parts = []; if($charset !== false) { $parts[] ="CHARACTER SET ".$charset; } if($collate !== false) { $parts[] ="COLLATE ".$collate; } if($null) { if($default === false) { $parts[] = "DEFAULT NULL"; } else { $parts[] = 'DEFAULT "'.$default.'"'; } } else { $parts[] = "NULL NULL"; if($default !== false) { $parts[] = 'DEFAULT "'.$default.'"'; } } if($auto_incr !== false) { $parts[] = "AUTO_INCREMENT"; } return implode($glue,$parts); } public function schemaColumn($action="add",$col,$type=null,$extra=null) { $entry = ["action"=>$action,"column"=>$col,"type"=>$type, "extra"=>$extra]; return $this->schemaColumnEntry($entry); } protected function schemaColumnEntry(array $entry) { if(!is_array($this->changes)) { $this->changes = []; } $changed = $this->processEntry($entry); if($changed) { $this->changes[] = $entry; } return $this; } protected function processEntry(array $entry) { $action = $entry["action"]; $column = $entry["column"]; $type = $entry["type"]; $extra = $entry["extra"]; if($action == "add") { if(!isset($this->columns[$column])) { $this->columns[$column] = ["type"=>$type,"extra"=>$extra]; return true; } return false; } elseif($action == "drop") { if(isset($this->columns[$column])) { unset($this->columns[$column]); return true; } return false; } elseif($action == "change") { $old = $column[0]; $new = $column[1]; if(isset($this->columns[$old])) { unset($this->columns[$old]); $this->columns[$new] = ["type"=>$type,"extra"=>$extra]; return true; } if(!isset($this->columns[$new])) { $entry["action"] == "add"; return $this->processEntry($entry); } return false; } elseif($action == "dropIndex") { if(isset($this->keys[$column])) { unset($this->keys[$column]); return true; } return false; } elseif($action == "addIndex") { if(!isset($this->keys[$column])) { $this->keys[$column] = ["type"=>$type,"columns"=>$extra]; return true; } return false; } } public function toQuery($patch=false) { $query = new Query(); $table = $this->getTableName(); if($patch && $this->version>=1) { return $this->toPatchQuery(); } $query->create($table); $after = null; foreach($this->getColumns($this->version) as $col=>$entry) { //if(!is_null($after)) { //$entry["extra"]["after"] = $after; //} if(isset($entry["extra"]["after"])) { unset($entry["extra"]["after"]); } $query->add($col,$entry["type"],$entry["extra"]); $after = $col; } foreach($this->getKeys($this->version) as $name=>$entry) { $query->addIndex($name,$entry["columns"],$entry["type"]); } foreach($this->getMeta($this->version) as $key=>$entry) { $noBlank = ["auto_increment","comment"]; if(in_array($entry["key"],$noBlank) && (is_null($entry["value"]) || empty($entry["value"]))) { } else { $query->meta($entry["key"],$entry["value"]); } } return $query; } public function toPatchQuery($reorder=false) { if($this->version<=0) { return $this->toQuery(false); } $changes = 0; $savedCols = $this->getColumns($this->version); $curCols = $this->getColumns(); $savedOrder = array_keys($savedCols); $curOrder = array_keys($curCols); $columnAfter = []; $needAfter = []; foreach($savedOrder as $colname) { $columnAfter[$colname] = $this->getColumnAfter($colname,$this->version); } unset($savedOrder); foreach($curOrder as $colname) { if($columnAfter[$colname] != $this->getColumnAfter($colname)) { $needAfter[$colname] = $columnAfter[$colname]; } } unset($curOrder); $query = new Query(); $table = $this->getTableName(); $query->alter($table); foreach($curCols as $col=>$entry) { if(!isset($savedCols[$col])) { $origCol = $col; //currentColumn isn't part of the savedCols.. now we see if it changed $changed = $dropped = false; $colHist = $this->getColumnHistory($col); foreach($colHist as $ver=>$changes) { foreach($changes as $change) { if($change["action"] == "change" && $change["column"][0] == $col) { $col = $change["column"][1]; $entry = ["type"=>$change["type"],"extra"=>$change["extra"]]; $changed=true; } if($changed && $change["action"] == "drop" && $change["column"] == $col) { $changed = false; } } } if($changed) { if(isset($savedCols[$col])) { $extra = $savedCols[$col]["extra"]; if(isset($columnAfter[$col])) { $extra["after"] = $columnAfter[$col]; if(isset($needAfter[$col])) { unset($needAfter[$col]); } } $query->change($origCol,$col,$savedCols[$col]["type"],$extra); $changes++; } else { throw new \Exception("Couldn't process change for $origCol to $col.. $col not part of current columns. History fail?"); } } else { $query->remove($origCol); $changes++; } } } foreach($savedCols as $sCol=>$entry) { if(!isset($curCols[$sCol])) { $entry["extra"]["after"] = $this->getColumnAfter($sCol,$this->version); $query->add($sCol,$entry["type"],$entry["extra"]); $changes++; } else { $changed = false; if($entry["type"] != $curCols[$sCol]["type"]) { $changed = true; } else { if(!is_array($entry["extra"]) && is_array($curCols[$sCol]["extra"])) { $changed = true; } elseif(is_array($entry["extra"]) && !is_array($curCols[$sCol]["extra"])) { $changed = true; } elseif(count($entry["extra"]) !== count($curCols[$sCol]["extra"])) { $changed = true; } else { foreach($entry["extra"] as $e=>$ev) { if(!isset($curCols[$sCol]["extra"][$e]) || $curCols[$sCol]["extra"][$e] != $ev) { $changed = true; } } } } if($changed) { $entry["extra"]["after"] = $this->getColumnAfter($sCol,$this->version); if(isset($needAfter[$sCol])) { unset($needAfter[$sCol]); } $query->change($sCol,$sCol,$entry["type"],$entry["extra"]); $changes++; } } } if(!empty($needAfter)) { foreach($needAfter as $col=>$after) { $extra = $savedCols[$col]["extra"]; $extra["after"] = $after; $query->change($col,$col,$savedCols[$col]["type"],$extra); $changes++; } } //TODO: order check //$entry["extra"]["after"] = $this->getColumnAfter($sCol,$this->version); if($changes>0) { foreach($this->getMeta($this->version) as $key=>$entry) { if($entry["key"] == "auto_increment") $query->meta($entry["key"],$entry["value"]); } return $query; } return false; } protected function getColumnAfter($colName,$version=null) { $columns = $this->getColumns($version); $colKeys = array_keys($columns); $after = false; foreach($colKeys as $sCol) { if($sCol == $colName) { return $after; } $after = $sCol; } return $after; } public function jsonSerialize($array=null) { if(is_null($array)) { } return $array; } public function __toString() { return json_encode($this); } /** * Get the value of tableName */ public function getTableName() { return $this->tableName; } /** * Get the value of columns */ public function getColumns($version=null) { if($version == null) { return $this->columns; } else { $version = $this->getVersionString($version); return dhGlobal::getVal($this->versions[$version],"columns",null); } } /** * Get the value of keys */ public function getKeys($version=null) { if($version == null) { return $this->keys; } else { $version = $this->getVersionString($version); return dhGlobal::getVal($this->versions[$version],"keys",null); } } /** * Get the value of meta */ public function getMeta($version=null) { if($version == null) { return $this->meta; } else { $version = $this->getVersionString($version); return dhGlobal::getVal($this->versions[$version],"meta",null); } } /** * Get the value of versions */ public function getVersions() { return $this->versions; } /** * Get the value of history */ public function getHistory($version=null) { if($version == null) { return $this->history; } else { $version = $this->getVersionString($version); return dhGlobal::getVal($this->history,$version,null); } } /** * Get the value of changes * * @return mixed */ public function getChanges() { return $this->changes; } /** * Set the value of columns * * @return self */ public function setColumns($columns) { $this->columns = $columns; return $this; } /** * Set the value of tableName * * @return self */ public function setTableName($tableName) { $this->tableName = $tableName; return $this; } /** * Set the value of keys * * @return self */ public function setKeys($keys) { $this->keys = $keys; return $this; } /** * Set the value of meta * * @return self */ public function setMeta($meta) { $this->meta = $meta; return $this; } /** * Get the value of file * * @return mixed */ public function getFile() { return $this->file; } /** * Set the value of file * * @param File|String $file * @return self */ public function setFile($file) { if(!is_object($file)) { if(is_string($file)) { $this->file = new File(["path"=>$file]); } elseif(is_array($file)) { $this->file = new File($file); } } else { $this->file = $file; } return $this; } protected function getVersionString($version=null) { if(is_null($version)) { $version = $this->version; } if(!is_numeric($version)) { return $version; } return "v_".$version; } }src/dhDB.php000064400000016607144761607260006674 0ustar00setDriver(dhGlobal::getVal($options,"driver","Mysql")); $this->setConfig(dhGlobal::getVal($options,"config",false)); $this->initDriver(true); } public function connected() { return $this->init; } protected function initDriver($force=false) { if($force || is_null($this->driver)) { $this->init = false; if(!is_null($this->driverClass) && !is_null($this->config) && $this->config !== false) { try { $class = "\\".__NAMESPACE__.'\\db\\drivers\\'.$this->driverClass; $this->driver = new $class($this->config); if(!is_null($this->driver)) { $this->init = $this->driver->init(); } } catch (\Exception $e) { dhGlobal::error("Exception: ".$e->getMessage()); } } } } public function driver($driver=null) { if(!is_null($driver)) { return $this->setDriver($driver); } return $this->getDriver(); } public function getDumpCommand($db=null,$table=null) { $cmd = "mysqldump"; $cmd.=" --host=".$this->config["dbhost"]; $cmd.=" --user=".$this->config["dbuser"]; $cmd.=" --password=".$this->config["dbpass"]; $cmd.=" "; if(!is_null($db)) { $cmd.=$db." "; if(!is_null($table)) { $cmd.="'".$table."' "; } } return $cmd; } public function getStats() { return $this->driver()->getResults(); } /** * Returns a new Query object for ORM style operations * * @return Query */ public function makeQuery() { return new Query(); } /** * Returns the SQL for a query object. If Interpolate is true, returns the interpolated query (default, true) * * @param Query $query the query object to parse * @param boolean $interpolate wether or not to interpolate the returned sql * @return string */ public function fromQuery(Query $query,$interpolate=true) { $sql = $this->driver->fromQuery($query); if($interpolate) { return $this->driver->interpolateQuery($sql,$query->values); } return $sql; } /** * Generate a Schema for a table. Optionally provide an existing Schema for comparison * * @param string $table The tablename to parse * @param null|Schema $schema optional schema to compare * @return \boru\dhdb\Schema */ public function getTable($table,$schema=null) { return $this->driver->table($table,$schema); } /** * Run a SQL query (or Query Object) * * @param string|Query $query sql query to run * @param null|array $params params to bind to sql query * @param null|array $options additional optns (debug,exec,etc) * @return \boru\dhdb\interfaces\ResultInterface */ public function query($query,$params=[],$options=[]) { return $this->run($query,$params,$options); } /** * Run a SQL query (or Query Object) * * @param string|Query $query sql query to run * @param null|array $params params to bind to sql query * @param null|array $options additional optns (debug,exec,logErrors,etc) * @return \boru\dhdb\interfaces\ResultInterface */ public function run($query,$params=[],$options=[]) { $debug = dhGlobal::getVal($options,"debug",false); $exec = dhGlobal::getVal($options,"exec",true); $logErrors = dhGlobal::getVal($options,"logErrors",true); $this->driver->setLogErrors($logErrors); if(is_null($this->driver) || !$this->init) { return new Result(); } if(is_object($query)) { $sql = $this->fromQuery($query); if(empty($params)) { $params = $query->values; } else { if(!empty($query->values)) { $params = array_merge($query->values,$params); } } } else { $sql = $query; } if(!$exec) { if($debug) { dhGlobal::debug("query debug".PHP_EOL."Query: ".$sql.PHP_EOL." "); } return $sql; } else { $return = $this->driver->run($sql,$params); if($debug) { $stats = $this->driver->getResults(); dhGlobal::debug("query debug".PHP_EOL."Query: ".$stats["interpolated"].PHP_EOL."Exec: ".$stats["execTime"]); } return $return; } } /** * Get the value of driver * * @return \boru\dhdb\interfaces\DriverInterface */ public function getDriver() { $this->initDriver(); return $this->driver; } /** * Set the value of driver * * @param string $driver the driver class name * @return self */ public function setDriver($driver) { if(is_object($driver)) { $this->driver = $driver; } elseif(is_string($driver)) { $this->driverClass = $driver; $this->initDriver(true); } return $this; } /** * Get the value of config * * @return array */ public function getConfig() { return $this->config; } /** * Set the value of config * * @param array $config the config array * @return self */ public function setConfig($config) { $this->config = $config; return $this; } /** * Generate an array of [?,?,?,...] * * @param array $array the array to count/replace with '?' * @param string $glue optional glue to use on the returned string (default ','); * @return string */ public function generateQs($array,$glue=",") { $qarr = array_fill(0,count($array),"?"); return implode($glue,$qarr); } public static function fromVtigerConfig($configIncPhpFile,$options=[]) { $configFile = new File(["path"=>$configIncPhpFile]); $lineFilter = function($line) { if(preg_match('/^\$dbconfig/', $line)) { return $line; } }; $dbconfig = []; $data = $configFile->content(["lineFilter"=>$lineFilter]); try { eval($data); } catch (\Exception $e) { return false; } $options['config'] = [ 'dbtype'=>'mysql', "dbhost"=>$dbconfig['db_server'], "dbport"=>dhGlobal::trimString(":",$dbconfig['db_port'],dhGlobal::TRIM_START), "dbuser"=>$dbconfig['db_username'], "dbpass"=>$dbconfig['db_password'], "dbname"=>$dbconfig['db_name'], ]; return new self($options); } }src/drivers/Mysql.php000064400000017636144761607260010661 0ustar00"", "interpolated"=>"", "execTime"=>0, "error"=>false ]; protected $qStart=0; protected $qEnd=0; protected $execTime=0; protected $interpolated; protected $sql; protected $params; public function __construct($config) { $this->setConfig($config); } public function init() { if(!$this->init && !is_null($this->config) && $this->config !== false) { try { if(!isset($this->config["dbtype"])) { $this->config["dbtype"] = "mysql"; } $dsn=$this->config["dbtype"].':host='.$this->config["dbhost"].";port=".$this->config["dbport"].";dbname=".$this->config["dbname"]; $user=$this->config["dbuser"]; $passwd=$this->config["dbpass"]; $options = array( \PDO::ATTR_PERSISTENT => true, \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION ); parent::__construct($dsn, $user, $passwd, $options); $this->init = true; return true; } catch (\PDOException $e) { return false; } } return $this->init; } public function newQuery() { return new Query(); } public function newParser() { return new Parser(); } public function fromQuery($query) { $parser = new Parser($query); $parser->parse(); $sql = $parser->getQueryString(); return $sql; } public function run($sql,$arr=null,$arr2=null,$unused1=null,$unused2=null) { $this->init(); $this->failed = false; if($this->is_assoc($arr)) { $keys = ""; $values = array(); foreach($arr as $k=>$v) { $keys.=",`$k`=?"; $values[]=$v; } $keys = trim($keys,","); $sql = str_replace("::replace::",$keys,$sql); $sth = new Statement($this->prepare($sql)); foreach($values as $k=>$v) { $this->bind($sth,$k+1,$v); $last = $k+1; } if(is_array($arr2)) { foreach($arr2 as $k=>$v) { $last++; $this->bind($sth,$last,$v); } } } else { $sth = new Statement($this->prepare($sql)); if(is_array($arr)) { foreach($arr as $k=>$v) { $this->bind($sth,$k+1,$v); } } elseif($arr!=null && $arr!="") { $this->bind($sth,1,$arr); } } try { $this->set("queryStart",microtime(true)); $this->set("query",$sql); $this->set("interpolated",$this->interpolateQuery($sql,$arr)); $sth->execute(); $this->set("queryEnd",microtime(true)); $this->set("execTime",$this->get("queryEnd",0) - $this->get("queryStart",0)); $this->set("error",false); $this->set("complete",true); } catch (\PDOException $e) { $this->setError($sql,$arr,$e); return false; } catch (\Exception $e) { $this->setError($sql,$arr,$e); return false; } return $sth; } public function table($tableName,$schema=null) { $meta = []; if(is_null($schema)) { $schema = new Schema($tableName); } $sth = $this->run("SHOW TABLE STATUS LIKE ?",[$tableName]); while($row = $sth->next()) { $meta["engine"] = $row->get("Engine"); $meta["collate"] = $row->get("Collation"); if(!empty($row->get("Auto_increment"))) { $meta["auto_increment"] = $row->get("Auto_increment"); } if(!empty($row->get("Comment"))) { $meta["comment"]= $row->get("Comment"); } } foreach($meta as $k=>$v) { $schema->meta($k,$v); } $sth = $this->run("SHOW FULL FIELDS FROM `".$tableName."`"); while($row = $sth->next()) { $extra = ["null"=>true]; if(strtolower($row->get("Null")) == "no") { $extra["null"] = false; } if(!empty($row->get("Collation"))) { $extra["collate"] = $row->get("Collation"); } if(!empty($row->get("Default"))) { $extra["default"] = $row->get("Default"); } if(!empty($row->get("Extra")) && $row->get("Extra") == "auto_increment") { $extra["auto_increment"] = true; } $schema->add($row->get("Field"),$row->get("Type"),$extra); } $sth = $this->run("SHOW INDEX FROM `".$tableName."`"); $indexArr = []; while($row = $sth->next()) { if(!isset($indexArr[$row->get("Key_name")])) { if($row->get("Key_name") == "PRIMARY" && $row->get("Non_unique")<=0) { $type = "primary"; } elseif($row->get("Index_type") == "FULLTEXT") { $type="fulltext"; } elseif($row->get("Non_unique") <= 0) { $type = "unique"; } else { $type = "key"; } $indexArr[$row->get("Key_name")] = [ "name"=>$row->get("Key_name"), "type"=>$type, "extra"=>[] ]; } $indexArr[$row->get("Key_name")]["extra"][] = $row->get("Column_name").($row->get("Cardinality") == "D" ? " DESC" : ""); } foreach($indexArr as $name=>$index) { $schema->addIndex($name,$index["extra"],$index["type"]); } return $schema; } public function setError($query,$params,$exception) { $this->lastError = new Error($exception->getMessage(),$query,$params,$exception->getTraceAsString()); if($this->logErrors) { $this->lastError->log(); } } public function getError() { return $this->lastError; } private function is_assoc($array) { if(!is_array($array)) return false; return (bool)count(array_filter(array_keys($array), 'is_string')); } private function bind(&$sth,$pos,$value,$type=null) { if( is_null($type) ) { switch( true ) { case is_int($value): $type = \PDO::PARAM_INT; break; case is_bool($value): $type = \PDO::PARAM_BOOL; break; case is_null($value): $type = \PDO::PARAM_NULL; break; default: $type = \PDO::PARAM_STR; } } $sth->bindValue($pos,$value,$type); } public function interpolateQuery($query="", $params=[]) { if(empty($params)) { return $query; } $keys = array(); $values = $params; # build a regular expression for each parameter foreach ($params as $key => $value) { if (is_string($key)) { $keys[] = '/:'.$key.'/'; } else { $keys[] = '/[?]/'; } if (is_array($value)) $values[$key] = implode(',', $value); if (is_null($value)) $values[$key] = 'NULL'; } // Walk the array to see if we can add single-quotes to strings array_walk($values, function (&$v) { if (!is_numeric($v) && $v!="NULL") { $v = "\"".$v."\""; } }); $query = preg_replace($keys, $values, $query, 1, $count); return $query; } public function getResults($item=null) { if(is_null($item)) { return $this->get(); } return $this->get($item,null); } public function generateQuestions($array) { $string = ""; foreach($array as $v) { $string.=",?"; } return trim($string,","); } /** * Get the value of config */ public function getConfig() { return $this->config; } /** * Set the value of config * * @return self */ public function setConfig($config) { $this->config = $config; $this->init(); return $this; } /** * Get the value of logErrors * * @return mixed */ public function getLogErrors() { return $this->logErrors; } /** * Set the value of logErrors * * @param mixed $logErrors * @return self */ public function setLogErrors($logErrors) { $this->logErrors = $logErrors; return $this; } }src/drivers/mysql/Parser.php000064400000036453144761607260012153 0ustar00setQuery($query); } } public function parse($query=null) { if(is_null($query)) { $query = $this->getQuery(); } //raw query string if(!is_null($query->rawQueryString)) { return $this->setQueryString($query->rawQueryString); } if(isset($query->type) && !is_null($query->type)) { //select query if($query->type == "select") { return $this->parseSelect($query); } //update query if($query->type == "update") { return $this->parseUpdate($query); } //insert query if($query->type == "insert") { return $this->parseInsert($query); } //delete query //union query //create table if($query->type == "create") { return $this->parseCreateTable($query); } //alter table if($query->type == "alter") { return $this->parseAlterTable($query); } } } protected function parseSelect($query) { $sql = ""; $sql.= $this->parseColumnString($query," "); $sql.= $this->parseTableString($query," FROM "); $sql.= $this->parseConditionString($query," WHERE "); $sql.= $this->parseGroupByString($query); $sql.= $this->parseOrderByString($query); $sql.= $this->parseLimitString($query); if(!empty($sql)) { return $this->setQueryString("SELECT".$sql); } return false; } protected function parseUpdate($query) { $sql = ""; $sql.= $this->parseTableString($query,"UPDATE "); $sql.= $this->parseColumnString($query," SET "); $sql.= $this->parseConditionString($query," WHERE "); $sql.= $this->parseGroupByString($query); $sql.= $this->parseOrderByString($query); $sql.= $this->parseLimitString($query); if(!empty($sql)) { return $this->setQueryString($sql); } return false; } protected function parseInsert($query) { $sql = ""; $sql.= $this->parseTableString($query,"INSERT INTO "); $sql.= $this->parseColumnString($query," SET "); $sql.= $this->parseConditionString($query," WHERE "); $sql.= $this->parseGroupByString($query); $sql.= $this->parseOrderByString($query); $sql.= $this->parseLimitString($query); if(!empty($sql)) { return $this->setQueryString($sql); } return false; } protected function parseCreateTable($query) { $sql = ""; if($query->ifNotExists) { $sql.= $this->parseTableString($query,"CREATE TABLE IF NOT EXISTS "); } else { $sql.= $this->parseTableString($query,"CREATE TABLE "); } $sql.= " ("; $sql.= $this->parseSchemaColumnsCreate($query); $sql.= ") "; $sql.= $this->parseTableMeta($query," "); if(!empty($sql)) { return $this->setQueryString($sql); } return false; } protected function parseAlterTable($query) { $tableAlter = $this->parseTableString($query,"ALTER TABLE "); $columnAlter = $this->parseSchemaColumnsAlter($query); $metaAlter = $this->parseTableMeta($query,","); $sql = $tableAlter." "; if(!empty($columnAlter)) { $sql.=$columnAlter.", ".$metaAlter; } else { $sql.=$metaAlter; } if(!empty($columnAlter) || !empty($metaAlter)) { return $this->setQueryString($sql); } return false; } protected function parseGroupByString($query) { if(!is_null($query->groupBy)) { return " GROUP BY ".$query->groupBy; } return ""; } protected function parseOrderByString($query) { if(!is_null($query->orderBy)) { return " ORDER BY ".$query->orderBy; } return ""; } protected function parseLimitString($query) { if(!is_null($query->limit)) { return " LIMIT ".$query->limit; } return ""; } protected function parseColumnString($query,$prefix="") { if(empty($query->columns)) { return ""; } $columns = array_map([$this,'sanitizeColumnName'],$query->columns); return $prefix.implode(",",$columns); } protected function parseConditionString($query,$prefix="") { if(empty($query->conditions)) { return ""; } $conds = []; foreach($query->conditions as $cond) { $conds[] = "($cond)"; } return $prefix.implode(" AND ",$conds); } protected function parseTableString($query,$prefix="") { if(empty($query->tables)) { return ""; } $tables = $query->tables; $string = $this->sanitizeColumnName($tables[0]["name"]); array_shift($tables); if(!empty($tables)) { $tableStrings = []; foreach($tables as $table) { $tstring = ""; if(!is_null($table["type"])) { $tstring.=" ".$table["type"]." "; } else { $tstring.=","; } $tstring .= $this->sanitizeColumnName($table["name"]); if(!is_null($table["on"])) { $tstring.=" ON ".$table["on"]; } $tableStrings[] = $tstring; } $string.=implode(" ",$tableStrings); } return $prefix.$string; } public function parseSchemaColumnsCreate($query,$prefix = "") { if(empty($query->schemaColumns)) { return ""; } $coldefs = []; foreach($query->schemaColumns as $def) { $action = $def["action"]; $col = $def["column"]; $type = $def["type"]; $extra = $def["extra"]; if($action == "add") { $coldefs[] = $this->sanitizeColumnName($col)." $type".(!empty($extra) ? " ".$this->parseColExtra($extra,$type) : ""); } elseif($action == "addIndex") { $indexCols = []; if(is_array($extra)) { foreach($extra as $v) { $indexCols[] = $this->sanitizeColumnName($v); } } else { $indexCols[] = $this->sanitizeColumnName($extra); } if(empty($type) || $type == "key") { $coldefs[] = "KEY ".$this->sanitizeColumnName($col)." (".implode(",",$indexCols).")"; } elseif($type == "primary") { $coldefs[] = "PRIMARY KEY (".implode(",",$indexCols).")"; } elseif($type == "fulltext") { $coldefs[] = "FULLTEXT KEY ".$this->sanitizeColumnName($col)." (".implode(",",$indexCols).")"; } elseif($type == "unique") { $coldefs[] = "UNIQUE KEY ".$this->sanitizeColumnName($col)." (".implode(",",$indexCols).")"; } } } return implode(",",$coldefs); } public function parseSchemaColumnsAlter($query,$prefix = "") { if(empty($query->schemaColumns)) { return ""; } $out = []; foreach($query->schemaColumns as $def) { //$this->schemaColumns[] = ["action"=>$action,"column"=>$col,"type"=>$type, "extra"=>$extra]; $action = $def["action"]; $col = $def["column"]; $type = $def["type"]; $extra = $def["extra"]; if($action == "add") { $out[] = "ADD COLUMN ".$this->sanitizeColumnName($col)." $type".(!empty($extra) ? " ".$this->parseColExtra($extra,$type) : ""); } elseif($action == "drop") { $out[] = "DROP COLUMN ".$this->sanitizeColumnName($col); } elseif($action == "change") { $out[] = "CHANGE COLUMN ".$this->sanitizeColumnName($col[0])." ".$this->sanitizeColumnName($col[1])." $type".(!empty($extra) ? " ".$this->parseColExtra($extra,$type) : ""); } elseif($action == "dropIndex") { $out[] = "DROP INDEX ".$this->sanitizeColumnName($col); } elseif($action == "addIndex") { $indexCols = []; if(is_array($extra)) { foreach($extra as $v) { $indexCols[] = $this->sanitizeColumnName($v); } } else { $indexCols[] = $this->sanitizeColumnName($extra); } if(empty($type) || $type == "key") { $out[] = "ADD KEY ".$this->sanitizeColumnName($col)." (".implode(",",$indexCols).")"; } elseif($type == "primary") { $out[] = "ADD PRIMARY KEY (".implode(",",$indexCols).")"; } elseif($type == "fulltext") { $out[] = "ADD FULLTEXT INDEX ".$this->sanitizeColumnName($col)." (".implode(",",$indexCols).")"; } elseif($type == "unique") { $out[] = "ADD UNIQUE INDEX ".$this->sanitizeColumnName($col)." (".implode(",",$indexCols).")"; } } } return $prefix.implode(",",$out); } public function parseTableMeta($query,$implode=",") { //tableMeta if(empty($query->tableMeta)) { return ""; } $out = []; foreach($query->tableMeta as $def) { $key = $def["key"]; $value = $def["value"]; if(is_null($value) || empty($value)) { $out[] = $key; } else { $noEmpty = ["comment","auto_increment"]; if($key == "comment" && !empty($value)) { $out[] = $key.'="'.$value.'"'; } else { $out[] = $key."=".$value; } } } return implode($implode,$out); } protected function parseColExtra($extra,$type,$glue=" ") { if(!is_array($extra)) { if(empty($extra)) { return ""; } else { return $extra; } } $null = dhGlobal::getVal($extra,"null",false); $collate = dhGlobal::getVal($extra,"collate",false); $charset = dhGlobal::getVal($extra,"charset",false); $default = dhGlobal::getVal($extra,"default",false); $auto_incr = dhGlobal::getVal($extra,"auto_increment",false); $after = dhGlobal::getVal($extra,"after",false); if(!$charset && $collate !== false) { list($charset,$unused) = explode("_",$collate,2); } $parts = []; if($charset !== false) { $parts[] ="CHARACTER SET ".$charset; } if($collate !== false) { $parts[] ="COLLATE ".$collate; } if($null) { if($default === false) { $parts[] = "DEFAULT NULL"; } else { if($type == "timestamp" && $default == "CURRENT_TIMESTAMP") { $parts[] = 'DEFAULT CURRENT_TIMESTAMP'; } else { $parts[] = 'DEFAULT "'.$default.'"'; } } } else { $parts[] = "NOT NULL"; if($default !== false) { if($type == "timestamp" && $default == "CURRENT_TIMESTAMP") { $parts[] = 'DEFAULT CURRENT_TIMESTAMP'; } else { $parts[] = 'DEFAULT "'.$default.'"'; } } } if($auto_incr !== false) { $parts[] = "AUTO_INCREMENT"; } if($after !== false) { $parts[] = "AFTER ".$this->sanitizeColumnName($after); } return implode($glue,$parts); } public function sanitizeColumnName($colString) { if(!is_array($colString) && $colString == "*") { return $colString; } if(!is_array($colString)) { //$columns = explode(",",$colString); $columns = [$colString]; } else { $columns = $colString; } return $this->sanitize_arrayOfColumns($columns); } public function sanitize_arrayOfColumns($columns) { foreach($columns as $k=>$colString) { if(strpos($colString,"(") !== false && strpos($colString,")") !== false) { //do nothing.. it's a function or subquery, and i'm not about to go down that rabbit hole. echo "skipping $colString\n"; } elseif(strpos($colString," ") !== false) { $columns[$k] = $this->sanitize_colWithAlias($colString); } elseif(strpos($colString,".") !== false) { $columns[$k] = $this->sanitize_colWithSub($colString); } else { $columns[$k] = $this->sanitize_colName($colString); } } return implode(",",$columns); } public function sanitize_colWithAlias($colString) { $parts = preg_split('/\sas\s|\s/', strtolower($colString)); foreach($parts as $k=>$part) { if(strpos($parts[$k],".") !== false) { $parts[$k] = $this->sanitize_colWithSub($part); } else { $parts[$k] = $this->sanitize_colName($part); } } return implode(" ",$parts); } public function sanitize_colWithSub($colString) { $parts = explode(".",strtolower($colString)); foreach($parts as $k=>$part) { $parts[$k] = $this->sanitize_colName($part); } return implode(".",$parts); } public function sanitize_colName($col) { if($col == "*") { return $col; } return "`".preg_replace("/[^A-Za-z0-9\-_\.\(\)\*]/", '', $col)."`"; } public function sanitize_quoteCol($field) { if($field == "*") { return $field; } return preg_replace("/[^\`A-Za-z0-9\-_\.\(\)\*\ ]/", '', $field); } /** * Get the value of query */ public function getQuery() { return $this->query; } /** * Set the value of query * * @return self */ public function setQuery($query) { $this->query = $query; return $this; } /** * Get the value of queryString */ public function getQueryString() { return $this->queryString; } /** * Set the value of queryString * * @return self */ public function setQueryString($queryString) { $this->queryString = $queryString; return $this; } }src/drivers/mysql/Row.php000064400000002760144761607260011460 0ustar00setGetSetMainArray("fields"); if(is_array($fields) && !empty($fields)) { $this->fields = $fields; } } public function __set($field,$val) { $this->set($field,$val); } /** * Inherits trait functions from GetSetArray * ->get($key,$default=null) * ->set($key,$val='',$append=false) * ->exists($key=null) */ public function jsonSerialize($array=null) { if(is_null($array)) { $array = $this->fields; } return $array; } /** * Ignore intelephense error, php 5.6 :( */ public function offsetExists($offset) { return $this->exists($offset); } /** * Ignore intelephense error, php 5.6 :( */ public function offsetGet($offset) { return $this->get($offset); } /** * Ignore intelephense error, php 5.6 :( */ public function offsetSet($offset , $value) { $this->set($offset,$value); } /** * Ignore intelephense error, php 5.6 :( */ public function offsetUnset($offset) { $this->remove($this->$offset); } public function __toString() { return json_encode($this); } }src/drivers/mysql/Statement.php000064400000001436144761607260012654 0ustar00stmt = $stmt; } public function next($mode=\PDO::FETCH_ASSOC,$cursorOrientation = \PDO::FETCH_ORI_NEXT,$cursorOffset = 0) { if($row = $this->stmt->fetch($mode,$cursorOrientation,$cursorOffset)) { return new Row($row); } return false; } public function all() { $rows=[]; while($row=$this->next()) { $rows[] = $row; } return $rows; } public function __call($method,$args) { return call_user_func_array( [$this->stmt,$method], $args); } }src/interfaces/DriverInterface.php000064400000001047144761607260013262 0ustar00'mysql', "dbhost"=>"localhost", "dbport"=>"3306", "dbuser"=>"", "dbpass"=>"", "dbname"=>"boruservermaint", ]; use \boru\dhutils\dhGlobal; use \boru\dhdb\dhDB; use \boru\dhdb\Schema; dhGlobal::logger("debugger",dhGlobal::LOG_ALL & ~dhGlobal::LOG_TRACE,true,false); $db = dhGlobal::db($config); $tables = array(); $sql = "SHOW TABLES FROM `boruservermaint`"; $sth = $db->run($sql); while($row=$sth->next(PDO::FETCH_NUM)) { $tables[] = $row->get(0); } print_r($tables); exit(); /* $schema = new Schema("test","test.schema"); $test = $db->getTable("test",$schema); $query = $schema->toQuery(); echo $db->fromQuery($query)."\n"; echo $schema->version()."\n"; //$schema->change("first","last","varchar100"); if(($pquery = $schema->toPatchQuery()) !== false) {; echo $db->fromQuery($pquery)."\n"; } else { dhGlobal::outLine("table schema matched"); } */ /* $file = new File(["path"=>"testcomp.schema","create"=>true]); $file->write(json_encode($schema->getAll(),JSON_PRETTY_PRINT)); exit("\n"); */ /*$query = $db->makeQuery(); $query->alter("test") ->add("newcol","varchar(100)") ->change("first","last","varchar(100)"); $test = $db->run($query,null,["debug"=>true,"exec"=>false]); exit(); */ //$db = new dhDB(["driver"=>"Mysql","config"=>$config]); $query = $db->makeQuery(); $query->select(['id','username'])->from("navigator.users")->where("id=?")->where("username=?"); $test = $db->run($query,[1,'dhayes'],["debug"=>true]); while($row = $test->next()) { echo $row."\n"; } //ALTER TABLE navigator.users ADD KEY idx1 (username) , ADD UNIQUE INDEX idx2 (PASSWORD); //$query->create("boruservermaint")->column("test","bigint(19)","NOT NULL");