From 11972639df8315753123ebccdadee1f596807ad0 Mon Sep 17 00:00:00 2001 From: Antonio Gallo Date: Tue, 16 Sep 2014 08:03:29 +0000 Subject: Integrated new EasyGiant Library --- h-source/Library/Model/Tree.php | 560 +++++++++++++++++++++++++++++++++------- 1 file changed, 465 insertions(+), 95 deletions(-) (limited to 'h-source/Library/Model/Tree.php') diff --git a/h-source/Library/Model/Tree.php b/h-source/Library/Model/Tree.php index f7f95ea..849fd7d 100755 --- a/h-source/Library/Model/Tree.php +++ b/h-source/Library/Model/Tree.php @@ -2,7 +2,7 @@ // EasyGiant is a PHP framework for creating and managing dynamic content // -// Copyright (C) 2009 - 2011 Antonio Gallo +// Copyright (C) 2009 - 2014 Antonio Gallo (info@laboratoriolibero.com) // See COPYRIGHT.txt and LICENSE.txt. // // This file is part of EasyGiant @@ -28,54 +28,54 @@ class Model_Tree extends Model_Base { parent::__construct(); } - //method to create the first part of where clause - //$index: the index of $this->_tablesArray - public function createTreeWhere($index) { - if (!empty($this->on)) - { - return $this->on; - } - else - { - $whereArray = array(); - for ($i = $index; $i < (count($this->_tablesArray)-1); $i++) - { - $whereArray[] = $this->_tablesArray[$i].'.'.$this->_idFieldsArray[$i+1].'='.$this->_tablesArray[$i+1].'.'.$this->_idFieldsArray[$i+1]; - } - $whereString = !empty($whereArray) ? implode(' and ',$whereArray) : null; - return $whereString; - } - } - - //create the list of tables of the select query - //$index: the index of $this->_tablesArray - public function createTablesList($index) { - if (isset($this->from)) - { - return $this->from; - } - else - { - $tablesString = null; - for ($i = $index; $i < (count($this->_tablesArray)-1); $i++) - { - $tablesString .= $this->_tablesArray[$i] . ','; - } - $tablesString .= $this->_tablesArray[count($this->_tablesArray)-1]; - return $tablesString; - } - } +// //method to create the first part of where clause +// //$index: the index of $this->_tablesArray +// public function createTreeWhere($index) { +// if (!empty($this->on)) +// { +// return $this->on; +// } +// else +// { +// $whereArray = array(); +// for ($i = $index; $i < (count($this->_tablesArray)-1); $i++) +// { +// $whereArray[] = $this->_tablesArray[$i].'.'.$this->_idFieldsArray[$i+1].'='.$this->_tablesArray[$i+1].'.'.$this->_idFieldsArray[$i+1]; +// } +// $whereString = !empty($whereArray) ? implode(' and ',$whereArray) : null; +// return $whereString; +// } +// } - //method to create the list of fields of the select query - public function createFieldsList($index) { - $fieldsString = null; - for ($i = $index; $i < (count($this->_tablesArray)-1); $i++) - { - $fieldsString .= $this->_tablesArray[$i] . '.*,'; - } - $fieldsString .= $this->_tablesArray[count($this->_tablesArray)-1].'.*'; - return $fieldsString; - } +// //create the list of tables of the select query +// //$index: the index of $this->_tablesArray +// public function createTablesList($index) { +// if (isset($this->from)) +// { +// return $this->from; +// } +// else +// { +// $tablesString = null; +// for ($i = $index; $i < (count($this->_tablesArray)-1); $i++) +// { +// $tablesString .= $this->_tablesArray[$i] . ','; +// } +// $tablesString .= $this->_tablesArray[count($this->_tablesArray)-1]; +// return $tablesString; +// } +// } +// +// //method to create the list of fields of the select query +// public function createFieldsList($index) { +// $fieldsString = null; +// for ($i = $index; $i < (count($this->_tablesArray)-1); $i++) +// { +// $fieldsString .= $this->_tablesArray[$i] . '.*,'; +// } +// $fieldsString .= $this->_tablesArray[count($this->_tablesArray)-1].'.*'; +// return $fieldsString; +// } //method to create the where clause and the list of tables and fields of the select query @@ -84,32 +84,51 @@ class Model_Tree extends Model_Base { //return: $elements = array('tables'=>$tables,'where'=>$where,'fields'=>$fields) public function treeQueryElements($tableName,$choice = 'all') { - $index = array_search($tableName,$this->_tablesArray); - $subArray = ($choice === 'all') ? array_slice($this->_tablesArray,$index) : array($tableName); //this array is necessary to verify that the where clause makes sense - $tables = ($choice === 'all') ? $this->createTablesList($index) : $tableName; - $where = ($choice === 'all') ? $this->createTreeWhere($index) : null; - $fields = ($choice === 'all') ? $this->createFieldsList($index) : $tableName.'.*'; - - $wherePlus = $this->createWhereClause(); - - if (empty($this->on)) +// $index = array_search($tableName,$this->_tablesArray); +// $subArray = ($choice === 'all') ? array_slice($this->_tablesArray,$index) : array($tableName); //this array is necessary to verify that the where clause makes sense +// $tables = ($choice === 'all') ? $this->createTablesList($index) : $tableName; +// $where = ($choice === 'all') ? $this->createTreeWhere($index) : null; +// $fields = ($choice === 'all') ? $this->createFieldsList($index) : $tableName.'.*'; +// +// $wherePlus = $this->createWhereClause(); +// +// if (empty($this->on)) +// { +// $on = array(); +// +// if (isset($where) and isset($wherePlus)) +// { +// $where .= ' AND ' . $wherePlus; +// } +// else if (!isset($where) and isset($wherePlus)) +// { +// $where .= $wherePlus; +// } +// } +// else +// { +// $on = $where; +// $where = $wherePlus; +// } + + $where = $this->createWhereClause(); + + if (isset($where)) { - $on = array(); - - if (isset($where) and isset($wherePlus)) + if (isset($this->sWhere)) { - $where .= ' AND ' . $wherePlus; - } - else if (!isset($where) and isset($wherePlus)) - { - $where .= $wherePlus; + $where .= " AND " . $this->sWhere; } } else { - $on = $where; - $where = $wherePlus; + $where = $this->sWhere; } + + $tables = isset($this->from) ? $this->from : $tableName; + + $fields = $tableName.".*"; + $on = $this->on; return array('tables' => $tables,'where' => $where,'fields'=>$fields,'on'=>$on); } @@ -127,10 +146,37 @@ class Model_Tree extends Model_Base { public function getFields($fields = '',$choice = 'all') { $elements = $this->treeQueryElements($this->_tablesArray[0],$choice); - + $queryFields = (strcmp($fields,'') === 0) ? $elements['fields'] : $fields; - return $row = $this->db->select($elements['tables'],$queryFields,$elements['where'],$this->groupBy,$this->orderBy,$this->limit,$elements['on'],$this->using,$this->join); + $row = $this->db->select($elements['tables'],$queryFields,$elements['where'],$this->groupBy,$this->orderBy,$this->limit,$elements['on'],$this->using,$this->join); + + //convert from MySQL values + if ($this->convert) + { + if (count($row) > 0) + { + $types = array(); + + $tablesList = array_keys($row[0]); + + foreach ($tablesList as $table) + { + $types[$table] = $this->db->getTypes($table, "*", false, true); + } + + for ($i=0;$i< count ($row); $i++) + { + foreach ($tablesList as $table) + { + $row[$i][$table] = $this->convertFromMysqlT($types[$table], $row[$i][$table]); + } + + } + } + } + + return $row; } public function send() @@ -160,8 +206,8 @@ class Model_Tree extends Model_Base { //$id: the id (primary key) of the record //$fields: the comma separated list of fields that have to be extracted public function selectId($id,$fields = null) { - $tempWhere = $this->where; - $this->setWhereQueryClause(array($this->_idFieldsArray[0] => (int)$id)); + + $this->save()->clear()->setWhereQueryClause(array($this->_idFieldsArray[0] => (int)$id)); $this->using = null; @@ -174,7 +220,7 @@ class Model_Tree extends Model_Base { $values = $this->getAll('other'); } - $this->where = $tempWhere; + $this->restore(); return (count($values) > 0) ? $values[0][$this->_tablesArray[0]] : array(); @@ -218,49 +264,373 @@ class Model_Tree extends Model_Base { return $this->db->recordExists($elements['tables'],$field,$value,$elements['where'],$this->groupBy,$elements['on'],$this->using,$this->join); } -// //get the number of records of the table $this->_tablesArray[0] -// public function rowNumber() { -// return $this->recordNumber($this->_tablesArray[0]); -// } - - //method to call the delete query (overriding of the del method of Model.php) - //check the referential integrity - public function del($id = null, $whereClause = null) + //check referential integrity during delete + public function checkOnDeleteIntegrity($id = null, $whereClause = null) { - $this->queryResult = false; + $this->save(); - if (isset($whereClause)) + $result = true; + + if (count($this->foreignKeys) > 0) { - return parent::del(null,$whereClause); + foreach ($this->foreignKeys as $f) + { + if (preg_match('/^(.*?)\s(parent of)\s(.*?)\((.*?)\)(\s(on delete)\s(cascade|restrict)\s\((.*?)\))?$/i',$f,$matches)) + { + $parentKey = $matches[1]; + $childModel = $matches[3]; + $childField = $matches[4]; + + if (isset($whereClause)) + { + $this->clear()->sWhere($whereClause); + } + else + { + $this->clear()->where(array($this->_idFields=>(int)$id)); + } + + $keys = sanitizeDbDeep($this->toList($parentKey)->send()); + $this->restore(); + + if (count($keys) > 0) + { + $child = new $childModel(); + $childrenIds = $child->clear()->where(array($childField=>"in('".implode("','",$keys)."')"))->toList($child->getPrimaryKey())->send(); + + if (count($childrenIds) > 0) + { + if (isset($matches[7]) and strcmp($matches[7],"cascade") === 0) + { + foreach ($childrenIds as $childId) + { + $child->del((int)$childId); + } + + if (strcmp($matches[8],"") !== 0) + { + $this->notice .= "
".$matches[8]."
"; + } + } + else + { + $this->notice .= isset($matches[8]) ? "
".$matches[8]."
" : $this->_resultString->getString('associate'); + $result = false; + } + } + } + + } + } } - else + + return $result; + } + + //check referential integrity during insert or update + public function checkOnUpdateIntegrity($queryType) + { + $result = true; + + if (count($this->foreignKeys) > 0) { - if ($this->_onDelete === 'check' and isset($this->_reference)) + foreach ($this->foreignKeys as $f) { - if (isset($this->_reference[0]) and isset($this->_reference[1])) + if (preg_match('/^(.*?)\s(child of)\s(.*?)\((.*?)\)(\s(on update)\s(restrict)\s\((.*?)\))?$/i',$f,$matches)) { - if ($this->db->recordExists($this->_reference[0],$this->_reference[1],(int)$id)) + $childKey = $matches[1]; + $ParentModel = $matches[3]; + $ParentField = $matches[4]; + + $notice = isset($matches[8]) ? "
".$matches[8]."
" : ""; + + if (array_key_exists($childKey,$this->values)) { - $this->notice = $this->_resultString->getString('associate'); - $this->identifierValue = null; + $parent = new $ParentModel(); + $res = $parent->clear()->where(array($ParentField=>sanitizeDb($this->values[$childKey])))->send(); + + if (count($res) === 0) + { + $this->notice .= $notice; + $this->result = false; + $result = false; + } + } + else if ($queryType === "insert") + { + $this->notice .= $notice; $this->result = false; + $result = false; } - else + } + } + } + + return $result; + } + + //get the first extracted full record + public function record() + { + $res = $this->getFields($this->select); + + if (count($res) > 0) + { + return $res[0][$this->_tables]; + } + return array(); + } + + //get a single field from the first extracted record + public function field($fieldName) + { + $res = $this->save()->select($fieldName)->send(); + $this->restore(); + + if (count($res) > 0 and isset($res[0][$this->_tables][$fieldName])) + { + return $res[0][$this->_tables][$fieldName]; + } + return ""; + } + + //get the types of the fields in $this->values + public function getTypes($full = false) + { + return $types = $this->db->getTypes($this->_tables,implode(",",array_keys($this->values)),$full); + } + + //automatically set the values conditions + public function setValuesConditionsFromDbFields($queryType) + { + $fields = array_keys($this->values); + $fieldsAsString = implode(",",$fields); + + $types = $this->getTypes(true); + $fieldKeys = $this->db->getKeys($this->_tables,$fieldsAsString,true,false); + + if (count($this->values) > 0) + { + if (!$types) + { + $this->notice .= $this->_resultString->getString('not-existing-fields'); + $this->result = false; + return false; + } + else + { + $this->saveConditions("values"); + $this->saveConditions("database"); + + if (Params::$setValuesConditionsFromDbTableStruct) + { + foreach ($types as $index => $t) + { + if (preg_match('/^('.implode("|",$this->db->getCharTypes()).')\(([0-9]*?)\)$/i',$t,$matches)) + { + $this->addValuesCondition($queryType,'checkLength|'.$matches[2],$fields[$index]); + } + else if (preg_match('/^('.implode("|",$this->db->getIntegerTypes()).')/i',$t,$matches)) + { + $this->addValuesCondition($queryType,'checkInteger',$fields[$index]); + } + else if (preg_match('/^('.implode("|",$this->db->getFloatTypes()).')$/i',$t,$matches)) + { + $this->addValuesCondition($queryType,'checkNumeric',$fields[$index]); + } + else if (preg_match('/^('.implode("|",$this->db->getDateTypes()).')$/i',$t,$matches)) + { + $this->addValuesCondition($queryType,'checkIsoDate',$fields[$index]); + } + else if (preg_match('/^('.implode("|",$this->db->getEnumTypes()).')\((.*?)\)$/i',$t,$matches)) + { + $temp = array(); + $strings = explode(",",$matches[2]); + for ($i=0;$iaddValuesCondition($queryType,'checkIsStrings|'.implode(",",$temp),$fields[$index]); + } + else if (preg_match('/^('.implode("|",$this->db->getDecimalTypes()).')\((.*?)\)$/i',$t,$matches)) + { + $this->addValuesCondition($queryType,'checkDecimal|'.$matches[2],$fields[$index]); + } + } + + //set unique conditions + foreach ($fieldKeys as $index => $fk) { - return parent::del((int)$id); + if (preg_match('/^('.implode("|",$this->db->getUniqueIndexStrings()).')$/i',$fk,$matches)) + { + if ($queryType === "insert") + { + $this->addDatabaseCondition($queryType,'checkUnique',$fields[$index]); + } + else + { + $this->addDatabaseCondition($queryType,'checkUniqueCompl',$fields[$index]); + } + } } } - else + + foreach (Params::$valuesConditionsFromFormatsOfFieldsNames as $regExpr => $function) { - throw new Exception('you have forgotten to set \'$this->_reference\' or you have forgotten to set $this->_onDelete = \'nocheck\''); + foreach ($fields as $f) + { + if (preg_match($regExpr,$f,$matches)) + { + $this->addValuesCondition($queryType,$function,$f); + } + } + } } - else + } + +// print_r($fields); +// print_r($types); + + return true; + } + + //convert values of the $this->values to MySQL formats + public function convertValuesToDb() + { + if (Params::$automaticConversionToDbFormat) + { + if (isset($this->_conversionToDbObject)) { - return parent::del((int)$id); + $types = $this->getTypes(); + + if ($types) + { + $fields = array_keys($this->values); + + foreach ($types as $index => $t) + { + if (method_exists($this->_conversionToDbObject,strtolower($t))) + { + $this->values[$fields[$index]] = call_user_func(array($this->_conversionToDbObject, strtolower($t)), $this->values[$fields[$index]]); + } + } + } } } + } + + public function insert() + { + $this->db->setAutocommit(true); + + $this->notice = null; + $this->queryResult = false; + + $this->convertValuesToDb(); + + if ($this->checkOnUpdateIntegrity("insert")) + { + //set the values conditions from the table description + if ($this->setValuesConditionsFromDbFields("insert")) + { + if ($this->applyDatabaseConditions("insert",null)) + { + $this->restoreConditions("database"); + if ($this->applyValidateConditions("insert",'values')) + { + $this->restoreConditions("values"); + return parent::insert(); + } + $this->restoreConditions("values"); + } + $this->restoreConditions("database"); + } + } + return false; + + } + + public function update($id = null, $whereClause = null) + { + $this->db->setAutocommit(true); + + $this->notice = null; + $this->queryResult = false; + + $this->convertValuesToDb(); + + if ($this->checkOnUpdateIntegrity("update")) + { + //set the values conditions from the table description + if ($this->setValuesConditionsFromDbFields("update")) + { + if (!isset($id) or $this->applyDatabaseConditions("update",(int)$id)) + { + $this->restoreConditions("database"); + //check the values conditions + if ($this->applyValidateConditions("update",'values')) + { + $this->restoreConditions("values"); + return parent::update($id, $whereClause); + } + $this->restoreConditions("values"); + } + $this->restoreConditions("database"); + } + } + + return false; + } + + //method to call the delete query (overriding of the del method of Model.php) + //check the referential integrity + public function del($id = null, $whereClause = null) + { + $this->notice = null; + $this->queryResult = false; + + if ($this->checkOnDeleteIntegrity($id, $whereClause)) + { + return parent::del($id, $whereClause); + } + else + { + return false; + } +// if (isset($whereClause)) +// { +// return parent::del(null,$whereClause); +// } +// else +// { +// if ($this->_onDelete === 'check' and isset($this->_reference)) +// { +// if (isset($this->_reference[0]) and isset($this->_reference[1])) +// { +// if ($this->db->recordExists($this->_reference[0],$this->_reference[1],(int)$id)) +// { +// $this->notice = $this->_resultString->getString('associate'); +// $this->identifierValue = null; +// $this->result = false; +// } +// else +// { +// return parent::del((int)$id); +// } +// } +// else +// { +// throw new Exception('you have forgotten to set \'$this->_reference\' or you have forgotten to set $this->_onDelete = \'nocheck\''); +// } +// } +// else +// { +// return parent::del((int)$id); +// } +// } +// return false; } //method to obtain one columns from the tables $this->_tablesArray as an associative array -- cgit v1.2.3