123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987 |
- <?php
- require_once __DIR__.'/../constant.php';
- require_once(__ROOT__.'class'.SLASH.'Database.class.php');
- require_once(__ROOT__.'connector'.SLASH.'Oracle.class.php');
- require_once(__ROOT__.'connector'.SLASH.'Mysql.class.php');
- require_once(__ROOT__.'connector'.SLASH.'Sqlite.class.php');
- require_once(__ROOT__.'connector'.SLASH.'SqlServer.class.php');
- /**
- * Classe de gestion ORM
- * @version 2
- * @category sql
- * @author Valentin CARRUESCO
- **/
- class Entity {
- public $pdo = null,$baseUid= 'local';
- public $created,$updated,$creator,$updater,$joins,$entityLabel = '';
- public $links = array();
- public $foreignColumns = array();
- public $fieldMapping = array();
- const ACTIVE = 'published';
- const INACTIVE = 'deleted';
- public function __construct() {
- if (!isset($this->TABLE_NAME)) $this->TABLE_NAME = strtolower(get_called_class());
- $this->connect();
- $this->fields['created'] = array('type'=>'date', 'label' => 'Date de création');
- $this->fields['updated'] = array('type'=>'date', 'label' => 'Date de modification');
- $this->fields['updater'] = array('type'=>'text', 'label' => 'Modificateur');
- $this->fields['creator'] = array('type'=>'text', 'label' => 'Créateur');
- $this->joins = array();
- $this->created = time();
- $this->fieldMapping = $this->field_mapping($this->fields);
- global $myUser;
- if(is_object($myUser) && $myUser->login!='') $this->creator = $myUser->login;
- }
- public function field_mapping($fields = array()){
- $fieldMapping = array();
- $fieldTypes = FieldType::available();
- foreach($fields as $field => $type) {
- if(!is_array($type)) $type = array('type'=>$type,'column'=>$field);
- if(!isset($type['column'])) $type['column'] = $field;
- if(isset($type['link'])) $this->links[$type['column']] = $type['link'];
- $type['fieldtype'] = $type['type'];
- //conversion des field type en sql type
- if(isset($fieldTypes[$type['type']])) $type['type'] = $fieldTypes[$type['type']]->sqlType;
- $fieldMapping[$field] = $type;
- }
- return $fieldMapping;
- }
- //Connexion à la base
- public function connect() {
- $this->pdo = Database::instance($this->baseUid);
- global $databases_credentials;
- $this->connector = $databases_credentials[$this->baseUid]['connector'];
- }
- public function __toString() {
- foreach ($this->toArray() as $key => $value)
- echo $key.' : '.$value.','.PHP_EOL;
- }
- public function __sleep() {
- return array_keys($this->toArray());
- }
- public function __wakeup() {
- $this->connect();
- }
- //Comparaison de deux instances d'une même entité, retourne les champs ayant changés uniquement
- public static function compare($obj1,$obj2,$ignore=array()){
- $class = get_called_class();
- $compare = array();
- foreach ($obj1->fields as $field => $type) {
- if($field == 'updated' || $field == 'updater' || in_array($field, $ignore)) continue;
- if($obj1->$field != $obj2->$field){
- if($type=='int' && (($obj1->$field==0 && $obj2->$field =='') || ($obj2->$field=='' && $obj1->$field ==0)) ) continue;
- $compare[] = array('field'=>$field,'value1'=>$obj1->$field,'value2'=>$obj2->$field);
- }
- }
- return $compare;
- }
- public static function fields($onlyKeys = true){
- $class = get_called_class();
- $instance = new $class();
- if($onlyKeys) return array_keys($instance->fields);
- return $instance->fieldMapping;
- }
- public function toArray($decoded=false) {
- $fields = array();
- foreach ($this->fields as $field => $type) {
- $fields[$field] = $decoded ? html_entity_decode($this->$field) : $this->$field;
- }
- return $fields;
- }
- public function toText() {
- $text = array();
- foreach ($this->fields as $field => $type) {
- $value = is_object($this->$field) ? '[object]' : $this->$field;
- $text[]= $field.' = '.$value;
- }
- return implode(', ',$text);
- }
- public function fromArray($array) {
- foreach ($array as $field => $value) {
- $this->$field = $value;
- }
- }
- public function closeDatabase() {
- // $this->close();
- }
- //Libellé human readable de l'entité
- public static function entityLabel() {
- $class = get_called_class();
- $instance = new $class();
- return !empty($instance->entityLabel) ? $instance->entityLabel : $class;
- }
- public static function tableName($escapeName = false,$instance = null) {
- global $databases_credentials;
- $class = get_called_class();
- if(!isset($instance)) $instance = new $class();
- $prefix = isset($databases_credentials[$instance->baseUid]['prefix']) ? $databases_credentials[$instance->baseUid]['prefix'] : '';
- $connector = $instance->connector;
- return $escapeName ? $connector::table_escape.$prefix.$instance->TABLE_NAME.$connector::table_escape : $prefix.$instance->TABLE_NAME;
- }
- // GESTION SQL
- /**
- * Verifie l'existence de la table en base de donnée.
- * @category manipulation SQL
- * @param <String> créé la table si elle n'existe pas
- * @return true si la table existe, false dans le cas contraire
- */
- public static function checkTable($autocreate = false) {
- $class = get_called_class();
- $instance = new $class();
- $query = 'SELECT count(*) as numRows FROM sqlite_master WHERE type="table" AND name=?';
- $statement = $instance->customQuery($query, array($class::tableName(false,$instance)));
- if ($statement != false) {
- $statement = $statement->fetchArray();
- if ($statement['numRows'] == 1) $return = true;
- }
- if ($autocreate && !$return) self::create();
- return $return;
- }
- public static function install($classDirectory) {
- foreach (glob($classDirectory.SLASH.'*.class.php') as $file) {
- $infos = explode('.', basename($file));
- $class = array_shift($infos);
- require_once($classDirectory.SLASH.$class.'.class.php');
- $reflection = new ReflectionClass($class);
- if (!class_exists($class) || !method_exists($class, 'create') || $class == get_class() || $reflection->isAbstract()) continue;
- $class::create();
- }
- }
- public static function uninstall($classDirectory) {
- foreach (glob($classDirectory.SLASH.'*.class.php') as $file) {
- $infos = explode('.', basename($file));
- $class = array_shift($infos);
- require_once($classDirectory.SLASH.$class.'.class.php');
- $reflection = new ReflectionClass($class);
- if (!class_exists($class) || !method_exists($class, 'drop') || $class == get_class() || $reflection->isAbstract()) continue;
- $class::drop();
- }
- }
- /**
- * Methode de vidage de l'entité.
- * @category manipulation SQL
- * @return Aucun retour
- */
- public static function truncate() {
- $class = get_called_class();
- $instance = new $class();
- $connector = $instance->connector;
- $sql = $connector::truncate();
- $query = Entity::render($sql,array(
- 'table' => $class::tableName(false,$instance),
- 'fieldMapping' => $instance->fieldMapping
- ));
- $instance->customQuery($query);
- }
- /**
- * Methode de creation de l'entité.
- * @category manipulation SQL
- * @return Aucun retour
- */
- public static function create() {
- $class = get_called_class();
- $instance = new $class();
- $fields = array();
- $connector = $instance->connector;
- $types = $connector::types();
- $fieldMapping = $instance->field_mapping($instance->fields);
- foreach ($instance->fields(false) as $slug => $field)
- $fields[$slug] = isset($types[$field['type']]) ? $types[$field['type']] : $types['default'];
- $sql = $connector::create();
- $query = Entity::render($sql,array(
- 'table' => $class::tableName(false,$instance),
- 'fields' => $fields,
- 'fieldMapping' => $instance->fieldMapping
- ));
- $instance->customQuery($query);
- if(isset($instance->indexes)) $instance->index($instance->indexes);
- }
- public static function drop() {
- $class = get_called_class();
- $instance = new $class();
- $connector = $instance->connector;
- $sql = $connector::drop();
- $query = Entity::render($sql,array(
- 'table' => $class::tableName(false,$instance),
- 'fieldMapping' => $instance->fieldMapping
- ));
- $instance->customQuery($query);
- if(isset($instance->indexes)) $instance->index($instance->indexes,false);
- }
- /**
- * Methode d'insertion ou de modifications d'elements de l'entité.
- * @category manipulation SQL
- * @param Aucun
- * @return Aucun retour
- */
- public function save() {
- global $myUser;
- $this->updated = time();
- if(is_object($myUser) && $myUser->login!='') $this->updater = $myUser->login;
- //update
- if (isset($this->id) && $this->id > 0) {
- $fields = array();
- foreach ($this->fields as $field => $type)
- $fields[$field] = $this->{$field};
- self::change($fields,array('id'=>$this->id));
- //insert
- } else {
- $connector = $this->connector;
- $data = array();
- $fields = array();
- $i = 0;
- foreach ($this->fields as $field => $type) {
- if((is_array($type) && $type['type'] == 'key') || $type == 'key') continue;
- $data[':'.$i] = $this->{$field};
- if((is_array($type) && $type['type'] == 'boolean') || $type == 'boolean') $data[':'.$i] = $data[':'.$i] ? 1 : 0;
- $fields[$field] = ':'.$i;
- $i++;
- }
- $sql = $connector::insert();
- $query = self::render($sql,array(
- 'table' => $this->tableName(false,$this),
- 'fields' => $fields,
- 'fieldMapping' => $this->fieldMapping
- ));
- $this->customQuery($query, $data);
- }
- $this->id = !isset($this->id) || !is_numeric($this->id) ? $this->pdo->lastInsertId() : $this->id;
- }
- /**
- * Methode d'insertion massive de l'entité.
- * @category manipulation SQL
- * @param $entities tableau des entité a inserer (update non géré)
- * @param $maxLines grouper par requete de $maxLines maximum
- * @return Aucun retour
- */
- public static function saveAll($entities,$maxLines = 300) {
- global $myUser;
- if(empty($entities)) return;
- $reference = $entities[0];
- $connector = $reference->connector;
- $sql_head = $connector::insert_head();
- $sql_body = $connector::insert_body();
- $time = time();
- $login = is_object($myUser) && $myUser->login!='' ? $myUser->login : '';
- //sépare en requetes groupées de $maxLines lignes max
- $entities_groups = array_chunk($entities, $maxLines);
- $tableName = $reference->tableName(false,$reference);
- foreach ($entities_groups as $entities_group) {
- $end = count($entities_group)-1;
- $data = array();
- foreach($entities_group as $u=>$entity){
- $entity->updated = $time;
- $entity->updater = $login;
- $fields = array();
- $i = 0;
- foreach ($entity->fields as $field => $type) {
- if ($type == 'key') continue;
- $data[':'.$u.'a'.$i] = $type!='boolean' ? $entity->{$field} : ($entity->{$field} ? 1:0);
- $fields[$field] = ':'.$u.'a'.$i;
- $i++;
- }
- if($u==0){
- $query = self::render($sql_head,array(
- 'table' => $tableName,
- 'fields' => $fields,
- 'fieldMapping' => $reference->fieldMapping
- ));
- }
- $query .= self::render($sql_body,array(
- 'table' => $entity->tableName(false,$entity),
- 'fields' => $fields,
- 'fieldMapping' => $entity->fieldMapping
- ));
- if($u!=$end) $query .= ',';
- }
- $reference->customQuery($query, $data);
- }
- }
- /**
- * Méthode de modification d'éléments de l'entité.
- * @category manipulation SQL
- * @param <Array> $colonnes=>$valeurs
- * @param <Array> $colonnes (WHERE) =>$valeurs (WHERE)
- * @return Aucun retour
- */
- public static function change($columns, $columns2 = array()) {
- $class = get_called_class();
- $instance = new $class();
- $connector = $instance->connector;
- $fields = array();
- $i = 0;
- $values = array();
- foreach ($columns as $field => $value) {
- $values[':'.$i] = $value;
- $fields[$field] = ':'.$i;
- $i++;
- }
- $filters = array();
- foreach($columns2 as $key=>$value){
- $filter = array(
- 'operator' => '=',
- 'field' => $key,
- 'postoperator' => ''
- );
- if(strpos($key,':')!==false){
- $infos = explode(':',$key);
- $filter['operator'] = $infos[1];
- $filter['field'] = $infos[0];
- }
- $fieldInfos = $instance->fieldMapping[$filter['field']];
- $filter['type'] = $fieldInfos['type'];
- $filter['column'] = $fieldInfos['column'];
- $connector::processField($filter,$value,$values,$i);
- $filters[] = $filter;
- }
- $data = array(
- 'table' => $class::tableName(false,$instance),
- 'fields' => $fields,
- 'filter' => !isset($filters) || count($filters) == 0 ? null: $filters,
- 'fieldMapping' => $instance->fieldMapping
- );
- $sql = $connector::update();
- $sql = Entity::render($sql,$data);
- $instance->customQuery($sql, $values);
- }
- /**
- * Méthode de selection de tous les elements de l'entité.
- * @category manipulation SQL
- * @param <String> $ordre=null
- * @param <String> $limite=null
- * @return <Array<Entity>> $Entity
- */
- public static function populate($order = null, $limit = null,$selColumn = array('*'),$joins = 0) {
- $results = self::loadAll(array(), $order, $limit,$selColumn,$joins);
- return $results;
- }
- /**
- * Méthode de selection multiple d'elements de l'entité.
- * @category manipulation SQL
- * @param <Array> $colonnes (WHERE)
- * @param <Array> $valeurs (WHERE)
- * @param <String> $ordre=null
- * @param <String> $limite=null
- * @param <String> $operation="=" definis le type d'operateur pour la requete select
- * @return <Array<Entity>> $Entity
- */
- public static function loadAll($columns = array(), $order = null, $limit = null, $selColumn = array('*'), $joins = 0) {
- $class = get_called_class();
- $instance = new $class();
- $connector = $instance->connector;
- $values = array();
- $i=0;
- $filters = array();
- foreach($columns as $key=>$value){
- $filter = array(
- 'operator' => '=',
- 'field' => $key,
- 'postoperator' => ''
- );
- if(strpos($key,':')!==false){
- $infos = explode(':',$key);
- $filter['operator'] = $infos[1];
- $filter['field'] = $infos[0];
- }
- $fieldInfos = $instance->fieldMapping[$filter['field']];
- $filter['type'] = $fieldInfos['type'];
- $filter['column'] = $fieldInfos['column'];
- $connector::processField($filter,$value,$values,$i);
- $filters[] = $filter;
- }
- if(!empty($order)){
- foreach ($order as $key=>$clause) {
- foreach ($instance->fieldMapping as $attribute => $infos) {
- $order[$key] = str_replace( $attribute,$infos['column'],$order[$key]);
- }
- }
- }
- $tableName = $class::tableName(false,$instance);
- $data = array(
- 'table' => $tableName,
- 'selected' => $selColumn,
- 'limit' => !isset($limit) || count($limit) == 0 ? null: $limit,
- 'orderby' => !isset($order) || count($order) == 0 ? null: $order,
- 'filter' => !isset($filters) || count($filters) == 0 ? null: $filters,
- 'fieldMapping' => $instance->fieldMapping
- );
- $data['joins'] = array();
- if($joins!=0){
- foreach ($data['selected'] as $k=>$column)
- $data['selected'][$k] = $tableName.'.'.$column;
- $data = self::recursiveJoining($instance,$data,$joins);
- }
- $sql = $connector::select();
- $sql = Entity::render($sql,$data);
- return $instance->customQuery($sql, $values, true, $joins);
- }
- /**
- * Méthode privée de gestion du join récursif sur les objets liés
- * @category manipulation SQL
- * @param <Object> $instance $instance de départ
- * @param <Array> $data Tableau de construction de la requete via render()
- * @param <Int> $iterations Nombre d'iteration réecurive maximum
- * @return <Array> $data
- */
- private static function recursiveJoining($instance,$data,$iterations,$joinInstanceAlias=''){
- if($iterations==0) return $data;
- $iterations--;
- if(isset($instance->links)){
- $instanceTable = $instance::tableName();
- foreach ($instance->links as $field => $className) {
- $className = str_replace('.class.php','',basename($className));
- $linkTable = $className::tableName();
- $field2 = 'id';
- $classField = explode('.',$className);
- if(isset($classField[1]))
- list($className,$field2) = $classField;
- $alias = substr($linkTable,0,3).'_'.$field;
- $joinInstance = new $className();
- foreach ($joinInstance->fields as $key=>$type)
- $data['selected'][] = $alias.'.'.$key.' as '.$linkTable.'_join_'.$key;
- $joinTable1 = $instanceTable;
- if(!empty($joinInstanceAlias)) $joinTable1 = $joinInstanceAlias;
- $data['joins'][] = array(
- 'jointable1' => $joinTable1,
- 'jointable2' => $linkTable,
- 'jointableAlias' => $alias,
- 'field1' => $field,
- 'field2' => $field2
- );
- $data = self::recursiveJoining($joinInstance,$data,$iterations,$alias);
- }
- }
- return $data;
- }
- /**
- * Methode de comptage des éléments de l'entité.
- * @category manipulation SQL
- * @return<Integer> nombre de ligne dans l'entité'
- */
- public static function rowCount($columns = array()) {
- $values = array();
- $class = get_called_class();
- $instance = new $class();
- $connector = $instance->connector;
- $i=0;
- $values = array();
- $filters = array();
- foreach($columns as $key=>$value){
- $filter = array(
- 'operator' => '=',
- 'field' => $key,
- 'postoperator' => ''
- );
- if(strpos($key,':')!==false){
- $infos = explode(':',$key);
- $filter['operator'] = $infos[1];
- $filter['field'] = $infos[0];
- }
- $fieldInfos = $instance->fieldMapping[$filter['field']];
- $filter['type'] = $fieldInfos['type'];
- $filter['column'] = $fieldInfos['column'];
- $connector::processField($filter,$value,$values,$i);
- $filters[] = $filter;
- }
- $data = array(
- 'table' => $class::tableName(false,$instance),
- 'selected' => 'id' ,
- 'filter' => count($filters) == 0 ? null: $filters,
- 'fieldMapping' => $instance->fieldMapping
- );
- $sql = $connector::count();
- $execQuery = $instance->customQuery(Entity::render($sql,$data), $values);
- $row = $execQuery->fetch();
- return $row['number'];
- }
- public static function loadAllOnlyColumn($selColumn, $columns, $order = null, $limit = null) {
- $objects = self::loadAll($columns, $order, $limit, $selColumn);
- if (count($objects) == 0) $objects = array();
- return $objects;
- }
- /**
- * Méthode de selection unique d'élements de l'entité.
- *
- * @category manipulation SQL
- *
- * @param <Array> $colonnes (WHERE)
- * @param <Array> $valeurs (WHERE)
- * @param <String> $operation="=" definis le type d'operateur pour la requete select
- *
- * @return <Entity> $Entity ou false si aucun objet n'est trouvé en base
- */
- public static function load($columns = array(),$joins =0) {
- $objects = self::loadAll($columns, null, array('1'),array('*'),$joins);
- if (!isset($objects[0])) $objects[0] = false;
- return $objects[0];
- }
- /**
- * Méthode de selection unique d'élements de l'entité.
- * @param <Array> $colonnes (WHERE)
- * @param <Array> $valeurs (WHERE)
- * @param <String> $operation="=" definis le type d'operateur pour la requete select
- * @deprecated use byId
- * @return <Entity> $Entity ou false si aucun objet n'est trouvé en base
- */
- public static function getById($id,$joins =0 ) {
- return self::byId($id,$joins =0);
- }
- /**
- * Méthode de selection unique d'élements de l'entité.
- * @param <Array> $colonnes (WHERE)
- * @param <Array> $valeurs (WHERE)
- * @param <String> $operation="=" definis le type d'operateur pour la requete select
- * @return <Entity> $Entity ou false si aucun objet n'est trouvé en base
- */
- public static function byId($id,$joins =0 ) {
- return self::load(array('id' => $id),$joins);
- }
- //parsing des templates sql de cnnecteurs avec les filtres/columns/data...
- public static function render($sql,$data=array()) {
- //loop
- $sql = preg_replace_callback('/{{\:([^\/\:\?}]*)}}(.*?){{\/\:[^\/\:\?}]*}}/',function($matches) use ($data) {
- $tag = $matches[1];
- $sqlTpl = $matches[2];
- $sql = '';
- if(isset($data[$tag])){
- $i = 0;
- $values = $data[$tag];
- if($tag =='joins'){
- //joins
- foreach($values as $join){
- $occurence = $sqlTpl;
- foreach($join as $key=>$value){
- $occurence = str_replace(array('{{'.$key.'}}'),array($value),$occurence);
- }
- $sql.= $occurence;
- }
- }else if($tag =='filter'){
- //filters
- foreach($values as $key=>$value){
- $i++;
- $last = $i == count($values);
- $operator = $value['operator'];
- $postoperator = $value['postoperator'];
- $key = $value['column'];
- $occurence = str_replace(array('{{key}}','{{value}}','{{operator}}','{{postoperator}}'),array($key,
- $value['tag'],
- $operator,
- $postoperator),
- $sqlTpl);
- $occurence = preg_replace_callback('/{{\;}}(.*?){{\/\;}}/',function($matches) use ($last){
- return $last? '': $matches[1];
- },$occurence);
- $sql.= $occurence;
- }
- } else {
- //Autre boucles
- foreach($values as $key=>$value){
- $i++;
- $last = $i == count($values);
- $operator = isset($data['operator']) ? $data['operator'][0] : '=';
- $postoperator = isset($data['postoperator']) ? $data['postoperator'][0] : '';
- if(strpos($key,':')!==false){
- $infos = explode(':',$key);
- $key = $infos[0];
- $operator = $infos[1];
- if($operator=='IN' || $operator=='NOT IN'){
- $operator = $operator.'(';
- $postoperator = ')';
- }
- }
- $occurence = str_replace(array('{{key}}','{{value}}','{{operator}}','{{postoperator}}'),array($key,$value,$operator,$postoperator),$sqlTpl);
- $occurence = preg_replace_callback('/{{\;}}(.*?){{\/\;}}/',function($matches) use ($last){
- return $last? '': $matches[1];
- },$occurence);
- $sql.= $occurence;
- }
- }
- return $sql;
- }
- return '';
- },$sql);
- //conditions
- $sql = preg_replace_callback('/{{\?([^\/\:\?}]*)}}(.*?){{\/\?[^\/\:\?}]*}}/',function($matches) use ($data) {
- $key = $matches[1];
- $sql = $matches[2];
- return !isset($data[$key]) || (is_array($data[$key]) && count($data[$key])==0) ?'':$sql;
- },$sql);
- //simple vars
- $sql = preg_replace_callback('/{{([^\/\:\;\?}]*)}}/',function($matches) use ($data) {
- $key = $matches[1];
- return isset($data[$key])?$data[$key]:'';
- },$sql);
- return $sql;
- }
- /**
- * Methode de définition de l'éxistence d'un moins un des éléments spécifiés en base.
- *
- * @category manipulation SQL
- * @return<boolean> existe (true) ou non (false)
- */
- public static function exist($columns = array()) {
- $result = self::rowCount($columns);
- return $result != 0;
- }
- public static function deleteById($id) {
- self::delete(array('id' => $id));
- }
- /**
- * Méthode de Suppression d'elements de l'entité.
- * @category manipulation SQL
- * @param <Array> $colonnes (WHERE)
- * @param <Array> $valeurs (WHERE)
- * @param <String> $operation="=" definis le type d'operateur pour la requete select
- * @return Aucun retour
- */
- public static function delete($columns, $limit = array()) {
- $values = array();
- $class = get_called_class();
- $instance = new $class();
- $connector = $instance->connector;
- $i=0;
- $values = array();
- $filters = array();
- foreach($columns as $key=>$value){
- $filter = array(
- 'operator' => '=',
- 'field' => $key,
- 'postoperator' => ''
- );
- if(strpos($key,':')!==false){
- $infos = explode(':',$key);
- $filter['operator'] = $infos[1];
- $filter['field'] = $infos[0];
- }
- $fieldInfos = $instance->fieldMapping[$filter['field']];
- $filter['type'] = $fieldInfos['type'];
- $filter['column'] = $fieldInfos['column'];
- $connector::processField($filter,$value,$values,$i);
- $filters[] = $filter;
- }
- $data = array(
- 'table' => $class::tableName(false,$instance),
- 'limit' => count($limit) == 0 ? null: $limit,
- 'filter' => count($filters) == 0 ? null: $filters,
- 'fieldMapping' => $instance->fieldMapping
- );
- $sql = $connector::delete();
- return $instance->customQuery(Entity::render($sql,$data), $values);
- }
- /**
- * Méthode d'indexation de la ou les colonnes ciblées
- * nb : il est possible d'appeller automatiquement cette méthode sur les classes entity lors du create
- * si la classe contient l'attribut $this->indexes = array(...);
- * @category manipulation SQL
- * @param <Array> | <String> $colonne(s)
- * @param <Boolean> Mode (true : ajout, false : suppression)
- * @return Aucun retour
- */
- public static function index($columns,$mode = true){
- if(!is_array($columns)) $columns = array($columns);
- $columns = array_filter($columns);
- $class = get_called_class();
- $instance = new $class();
- $connector = $instance->connector;
- $tableName = $class::tableName(false,$instance);
- foreach($columns as $column){
- if(!is_array($column)) $column = array($column);
- $data = array(
- 'table' => $tableName,
- 'column' => '`'.implode('`,`',$column).'`',
- 'index_name' => $tableName.'_'.implode('_',$column),
- 'fieldMapping' => $instance->fieldMapping
- );
- $results = $class::staticQuery(Entity::render($connector::count_index(),$data));
- $exists = $results->fetch();
- if($mode){
- if($exists['exists'] != 1) $class::staticQuery(Entity::render($connector::create_index(),$data));
- }else{
- if($exists['exists'] > 0) $class::staticQuery(Entity::render($connector::drop_index(),$data));
- }
- }
- }
- //Génération d'une pagination
- public static function paginate($itemPerPage,$currentPage,&$query,$data,$alias=''){
- $class = get_called_class();
- $instance = new $class();
- $keys = array_keys($instance->fields, 'key');
- $key = count($keys) == 1 ? $keys[0] : 'id';
- $tableName = $class::tableName(false,$instance);
- $queryNumber = $query;
- $queryNumber = preg_replace("/(?<!\([^(\)])(SELECT.+[\n|\t]*FROM[\s\t\r\n])({{table}}|`?".$tableName."`?)(?![^(\)]*\))/iU", 'SELECT DISTINCT '.(!empty($alias) ? $alias : $tableName).'.'.$key.' FROM $2',$queryNumber);
- $queryNumber = $class::staticQuery('SELECT COUNT(*) FROM ('.$queryNumber.') number',$data)->fetch();
- $number = $queryNumber[0];
- $pageNumber = $number / $itemPerPage;
- if($currentPage >= $pageNumber) $currentPage = 0;
- $limit = ' LIMIT '.($currentPage*$itemPerPage).','.$itemPerPage;
- $query .= $limit;
- return array(
- 'pages' => $pageNumber,
- 'current' => $currentPage,
- 'total' => $number
- );
- }
- public static function provide($parameter = 'id',$join=0){
- global $_;
- $class = get_called_class();
- return !empty($_[$parameter]) ? $class::getById($_[$parameter],$join) : new $class();
- }
- // L'alias utilisé pour la colonne du join doit avoir la syntaxe [table]_join_[field]
- // Ex : address_join_street
- public static function staticQuery($query, $data = array(), $fill = false,$joins = 0) {
- $class = get_called_class();
- $instance = new $class();
- return $instance->customQuery($query, $data, $fill,$joins);
- }
- public function customQuery($query, $data = array(), $fill = false,$joins = 0) {
- $query = str_replace('{{table}}', $this->tableName(true,$this), $query);
- $mapping = $this->fieldMapping;
- $query = preg_replace_callback('/{{([^}]*)}}/si', function($match) use ($mapping){
- return isset($mapping[$match[1]]) && isset($mapping[$match[1]]['column']) ? $mapping[$match[1]]['column'] : $match[0];
- }, $query);
- try{
- if(BASE_DEBUG) self::logFile($query.' :: '.json_encode($data, JSON_UNESCAPED_UNICODE),debug_backtrace());
- $results = $this->pdo->prepare($query);
- $results->execute($data);
- if (!$results) throw new Exception(json_encode($this->pdo->errorInfo()));
- }catch(Exception $e){
- Log::put("[SQL ERROR] - Erreur : ".$e->getMessage().' - Requete : '.$query.' - Données : '.json_encode($data, JSON_UNESCAPED_UNICODE));
- if(BASE_DEBUG) self::logFile( "Erreur : ".$e->getMessage());
- throw $e;
- }
- if (!$fill) return $results;
- $class = get_class($this);
- $objects = array();
- $results = $results->fetchAll(PDO::FETCH_ASSOC);
- foreach ($results as $queryReturn) {
- $object = new $class();
- foreach ($this->fields as $field => $type) {
- $dbField = $field;
- if(is_array($type)){
- if(isset($type['column'])) $dbField = $type['column'];
- $type = $type['type'];
- }
- if (isset($queryReturn[$dbField])) {
- $object->{$field} = $queryReturn[$dbField];
- unset($queryReturn[$dbField]);
- }
- }
- if($joins>0) $object = self::recursiveJoiningFill($object,$queryReturn,$joins);
- foreach ($queryReturn as $key => $value) {
- if(!is_numeric($key)) $object->foreignColumns[$key] = $value;
- }
-
- $objects[] = $object;
- unset($object);
- }
- return $objects == null ? array() : $objects;
- }
- //Récuperation d'une/plusieurs colonne non référencée dans l'objet mais récuperée dans une requete static query
- public function foreign($key=null){
- if(!isset($key)) return $this->foreignColumns;
- return isset($this->foreignColumns[$key]) ? $this->foreignColumns[$key] : '';
- }
- //Renvois une chaine de selecteur sql devant être join
- //ex : Client::joinString('cli') --> cli.id client_join_id,cli.label client_join_label ...
- public static function joinString($prefix = ''){
- $class = get_called_class();
- $instance = new $class();
- $tableName = $class::tableName(false,$instance);
- $columns = array();
- foreach($instance->fields() as $field)
- $columns[] = $prefix.'.'.$field.' '.$tableName.'_join_'.$field.' ';
- return implode(', ',$columns);
- }
- private static function recursiveJoiningFill($object,$queryReturn,$iterations){
- if($iterations == 0) return $object;
- $iterations--;
- if(isset($object->links)){
- foreach ($object->links as $link=>$classLink) {
- $classLink = str_replace('.class.php','',basename($classLink));
- $classField = explode('.',$classLink);
- if(isset($classField[1]))
- $classLink = $classField[0];
- $instanceLink = new $classLink();
- $tableName = $classLink::tableName(false,$instanceLink);
- foreach ($instanceLink->fields as $field => $type)
- if(isset($queryReturn[$tableName.'_join_'.$field])) $instanceLink->{$field} = $queryReturn[$tableName.'_join_'.$field];
- $instanceLink = self::recursiveJoiningFill($instanceLink,$queryReturn,$iterations);
- $object->joins[$link] = $instanceLink;
- }
- }
- return $object;
- }
- /**
- * Récupere l'objet join ex : $contact->join("adress")->street; --> récupere l'attribut street de la class Adress dont l'id est spécifié dans la colonne adress de la class Contact
- * Nb : cette méthode ne fonctionne que si vous avez placé le parametre joins > 0 dans la méthode LoadALl
- * Nb : cette méthode ne fonctionne que si vous avez précisé le lien entre Contact et Adress dans la classe Contact via :
- protected $links = array(
- 'address' => 'Address'
- );
- *
- * @category manipulation SQL
- *
- * @param <Array> $colonnes (WHERE)
- * @param <Array> $valeurs (WHERE)
- * @param <String> $operation="=" definis le type d'operateur pour la requete select
- *
- * @return Aucun retour
- */
- public function join($field){
- return isset($this->joins[$field])?$this->joins[$field]:'';
- }
- public static function logFile($msg,$backtrace=null){
- if(strpos(ROOT_URL, '127.0.0.1') === false && strpos(ROOT_URL, 'dev.local') === false && strpos(ROOT_URL, 'localhost') === false ) return;
- file_put_contents(__DIR__.SLASH.'..'.SLASH.'sql.debug.sql', date('H:i:s').' | '.$msg.PHP_EOL,FILE_APPEND);
- //A décommenter pour obtenir la stacktrace d'un appel sql dans le fichier sql.debug.sql
- //if(isset($backtrace)) file_put_contents(__DIR__.SLASH.'..'.SLASH.'sql.debug.sql', json_encode($backtrace,JSON_PRETTY_PRINT).PHP_EOL,FILE_APPEND);
- }
- public static function log_executed_query($string, $data) {
- $indexed = $data==array_values($data);
- foreach($data as $k=>$v) {
- if(is_string($v)) $v="'$v'";
- if($indexed) $string=preg_replace('/\?/',$v,$string,1);
- else $string=str_replace(":$k",$v,$string);
- }
- self::logFile($string);
- }
- }
|