wpdk-db.php

Go to the documentation of this file.

<?php

/*
 * [DRAFT]
 *
 * THE FOLLOWING CODE IS A DRAFT. FEEL FREE TO USE IT TO MAKE SOME EXPERIMENTS, BUT DO NOT USE IT IN ANY CASE IN
 * PRODUCTION ENVIRONMENT. ALL CLASSES AND RELATIVE METHODS BELOW CAN CHNAGE IN THE FUTURE RELEASES.
 *
 */


class WPDKDBTableStatus extends WPDKObject {
  const ALL     = 'all';
  const PUBLISH = 'publish';
  const DRAFT   = 'draft';
  const TRASH   = 'trash';

  public $__version = '1.0.1';

  public static function statuses()
  {
    $statuses = array(
      self::ALL     => __( 'All', WPDK_TEXTDOMAIN ),
      self::DRAFT   => __( 'Draft', WPDK_TEXTDOMAIN ),
      self::PUBLISH => __( 'Publish', WPDK_TEXTDOMAIN ),
      self::TRASH   => __( 'Trash', WPDK_TEXTDOMAIN ),
    );
    return $statuses;
  }

  public static function sanitizeStatus( $status, $statuses = null )
  {
    $status   = esc_attr( $status );
    $statuses = is_null( $statuses ) ? self::statuses() : $statuses;
    $allowed  = array_keys( $statuses );
    if ( !in_array( $status, $allowed ) ) {
      return false;
    }
    return $status;
  }

}

class __WPDKDBTable {

  public $primaryKey;

  public $sqlFilename;

  public $tableName;

  public function __construct( $table_name, $sql_file = '' )
  {
    global $wpdb;

    /* Add the WordPres database prefix. */
    $this->tableName = sprintf( '%s%s', $wpdb->prefix, $table_name );

    /* $sql_file must be complete of path. */
    $this->sqlFilename = $sql_file;

    /* Get the Primary key. */
    $this->primaryKey = $this->primaryKey();
  }

  public function primaryKey()
  {
    global $wpdb;

    $db = DB_NAME;

    $sql = <<< SQL
SELECT `COLUMN_NAME`
FROM `information_schema`.`COLUMNS`
WHERE (`TABLE_SCHEMA` = '{$db}')
  AND (`TABLE_NAME` = '{$this->tableName}')
  AND (`COLUMN_KEY` = 'PRI');
SQL;
    return $wpdb->get_var( $sql );
  }

  public function count( $distinct = '', $status = '' )
  {
    global $wpdb;

    $where = '';
    if ( !empty( $status ) && is_array( $status ) ) {
      if ( is_numeric( $status[key( $status )] ) ) {
        $where = sprintf( 'WHERE %s = %s', key( $status ), $status[key( $status )] );
      }
      else {
        $where = sprintf( "WHERE %s = '%s'", key( $status ), $status[key( $status )] );
      }
    }

    if ( empty( $distinct ) ) {
      $sql = <<< SQL
SELECT COUNT(*) AS count
  FROM `{$this->tableName}`
  {$where}
SQL;
      return absint( $wpdb->get_var( $sql ) );
    }
    else {
      $sql = <<< SQL
SELECT DISTINCT(`{$distinct}`),
  COUNT(*) AS count
  FROM `{$this->tableName}`
  {$where}
  GROUP BY `{$distinct}`
SQL;

      $results = $wpdb->get_results( $sql, ARRAY_A );
      $result  = array();
      foreach ( $results as $res ) {
        $result[$res[$distinct]] = $res['count'];
      }
      return $result;
    }
  }


  public function delete( $pks )
  {
    global $wpdb;

    if ( !is_array( $pks ) ) {
      $pks = array( $pks );
    }

    $ids = implode( ',', $pks );

    $sql    = <<< SQL
DELETE FROM `{$this->tableName}`
WHERE `$this->primaryKey` IN({$ids})
SQL;
    $result = $wpdb->query( $sql );

    return $result;
  }

  public function groupBy( $column, $order_by = true, $order = 'ASC' )
  {
    global $wpdb;

    $sql_order = $order_by ? sprintf( 'ORDER BY `%s` %s', $column, $order ) : '';

    $sql     = <<< SQL
SELECT `$column`
  FROM `{$this->tableName}`
  GROUP BY `$column`
  {$sql_order}
SQL;
    $results = $wpdb->get_results( $sql, ARRAY_A );
    $result  = array();
    foreach ( $results as $res ) {
      if ( !empty( $res[$column] ) ) {
        $result[] = $res[$column];
      }
    }
    return $result;
  }

  public function map( $source_row, $destination_object )
  {
    if ( is_object( $source_row ) ) {
      foreach ( $source_row as $field => $value ) {
        $destination_object->$field = $value;
        if ( method_exists( $destination_object, 'column_' . $field ) ) {
          call_user_func( array(
                               $destination_object,
                               'column_' . $field
                          ), $value );
        }
      }
      return $destination_object;
    }
    return false;
  }

  public function select( $id = false, $order_by = '', $order = 'ASC', $where = '' )
  {
    global $wpdb;

    $sql_where = '';
    if ( !empty( $id ) ) {
      if ( is_array( $id ) ) {
        $id = implode( ',', $id );
      }
      $sql_where = sprintf( ' AND %s IN(%s)', $this->primaryKey, $id );
    }

    if ( !empty( $where ) ) {
      $sql_where = sprintf( '%s AND %s ', $sql_where, $where );
    }

    $order_by = empty( $order_by ) ? $this->primaryKey : $order_by;

    $sql = <<< SQL
SELECT * FROM `{$this->tableName}`
WHERE 1 {$sql_where}
ORDER BY {$order_by} {$order}
SQL;

    $rows = $wpdb->get_results( $sql, OBJECT_K );

    return $rows;
  }

  public function _select( $query = null, $order_by = '', $order = 'ASC' )
  {
    global $wpdb;

    /* Sanitize order by. */
    $order_by = empty( $order_by ) ? $this->primaryKey : $order_by;

    /* Build where condiction from an obejct (single record). */
    $where = $this->where( $query );

    $sql = <<<SQL
SELECT * FROM {$this->tableName}
WHERE 1 = 1

{$where}

ORDER BY {$order_by} {$order}
SQL;

    $results = $wpdb->get_results( $sql, OBJECT_K );

    return $results;
  }

  public function update()
  {
    global $wpdb;

    // Hide database warning and error
    $wpdb->hide_errors();
    $wpdb->suppress_errors();

    // Buffering
    ob_start();

    if ( !empty( $this->sqlFilename ) && !empty( $this->tableName ) ) {
      if ( !function_exists( 'dbDelta' ) ) {
        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
      }
      $content = file_get_contents( $this->sqlFilename );
      if ( empty( $content ) ) {
        ob_end_clean();
        return false;
      }

      // Replace table name
      $sql = str_replace( '%s', $this->tableName, $content );

      // Remove comments
      $pattern = '@(([\'"]).*?[^\\\]\2)|((?:\#|--).*?$|/\*(?:[^/*]|/(?!\*)|\*(?!/)|(?R))*\*\/)\s*|(?<=;)\s+@ms';
      /*
       * Commented version
       *
       * $sqlComments = '@
       *     (([\'"]).*?[^\\\]\2) # $1 : Skip single & double quoted expressions
       *     |(                   # $3 : Match comments
       *         (?:\#|--).*?$    # - Single line comments
       *         |                # - Multi line (nested) comments
       *          /\*             #   . comment open marker
       *             (?: [^/*]    #   . non comment-marker characters
       *                 |/(?!\*) #   . ! not a comment open
       *                 |\*(?!/) #   . ! not a comment close
       *                 |(?R)    #   . recursive case
       *             )*           #   . repeat eventually
       *         \*\/             #   . comment close marker
       *     )\s*                 # Trim after comments
       *     |(?<=;)\s+           # Trim after semi-colon
       *     @msx';
       *
       */
      $sql_sanitize = trim( preg_replace( $pattern, '$1', $sql ) );
      preg_match_all( $pattern, $sql, $comments );

      // Only commnets
      //$extractedComments = array_filter( $comments[ 3 ] );

      // Execute delta
      @dbDelta( $sql_sanitize );

      // Clear error
      global $EZSQL_ERROR;
      $EZSQL_ERROR = array();
    }
    ob_end_clean();
    return true;
  }

  public function where( $query, $prefix = '' )
  {
    $result = '';

    if ( !is_null( $query ) ) {

      /* Sanitize prefix. */
      if ( !empty( $prefix ) ) {
        $prefix = rtrim( $prefix, '.' ) . '.';
      }

      $desc  = $query->desc();
      $stack = array();

      /* Database type to be numeric. */
      $numeric = array(
        'bigint',
        'int',
        'decimal'
      );

      foreach ( $query as $property => $value ) {
        if ( isset( $desc[$property] ) && !is_null( $value ) ) {

          /* Remove `(` from type. */
          $type = $desc[$property]->Type;
          $pos  = strpos( $type, '(' );
          $type = ( false === $pos ) ? $type : substr( $type, 0, $pos );

          /* Check for numeric and string. */
          // TODO Implement array support too when value is an array
          if ( in_array( $type, $numeric ) ) {
            $stack[] = sprintf( 'AND %s%s = %s', $prefix, $property, $value );
          }
          else {
            $stack[] = sprintf( 'AND %s%s = \'%s\'', $prefix, $property, $value );
          }
        }
      }

      if ( !empty( $stack ) ) {
        $result = implode( ' ', $stack );
      }
    }

    return $result;
  }

  public function deleteWherePrimaryKey( $id )
  {
    _deprecated_function( __METHOD__, '1.0.0', 'delete()' );
    $this->delete( $id );
  }

  public function selectWhereID( $id, $object, $output = OBJECT )
  {
    _deprecated_function( __METHOD__, '1.0.0', 'select()' );
    $this->select( $id );
  }

}

class WPDKDBTableRow {

  const NULL_VALUE = '!NULL!';

  public $table;

  public function __construct( __WPDKDBTable $dbtable, $pk = null )
  {
    $this->table = $dbtable;

    if ( !is_null( $pk ) ) {
      if ( is_numeric( $pk ) ) {
        $this->initByID( $pk );
      }
      elseif ( is_array( $pk ) ) {
        /* @todo */
      }
      elseif ( is_object( $pk ) ) {
        /* @todo */
      }
    }
  }

  private function initByID( $pk )
  {
    global $wpdb;

    /* @todo You can use $this->table->selectWithID( $id ); insetad */

    $sql = sprintf( 'SELECT * FROM %s WHERE %s = %s', $this->table->tableName, $this->table->primaryKey, $pk );

    /* Override. */
    $sql = $this->get_sql( $pk, $sql );

    /* Notify to all */
    $sql = apply_filters( 'wpdk_db_table_' . $this->table->tableName . '_sql', $sql );

    $row = $wpdb->get_row( $sql );

    if ( !is_null( $row ) ) {
      foreach ( $row as $property => $value ) {
        $this->$property = $value;
        if ( method_exists( $this, 'column_' . $property ) ) {
          call_user_func( array( $this, 'column_' . $property ), $value );
        }
      }
      return $row;
    }
    return false;
  }

  public function get_sql( $pk, $sql )
  {
    return $sql;
  }

  public static function getInstance( __WPDKDBTable $dbtable, $pk = null )
  {
    $instance = new WPDKDBTableRow( $dbtable, $pk );
    return $instance;
  }

  public function desc()
  {
    global $wpdb;
    $sql = sprintf( 'DESC %s', $this->table->tableName );

    $result = $wpdb->get_results( $sql, OBJECT_K );

    return $result;
  }

  public function defaults()
  {
    /* Override */
    return array();
  }
}