WordPress.org

Ready to get started?Download WordPress

Forums

WP Db Abstraction
PROBLEM IN translate_limit (4 posts)

  1. parxal
    Member
    Posted 2 years ago #

    WordPress 3.3.2
    Database abstraction 1.1.4
    Using pdo sql server

    Problem : Media Library is not showing any files.

    Mysql query:
    SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'attachment' AND (wp_posts.post_status = 'inherit' OR wp_posts.post_status = 'private') ORDER BY wp_posts.post_date DESC LIMIT 0, 20

    Sql server query:
    SELECT TOP 0 * FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'attachment' AND (wp_posts.post_status = 'inherit' OR wp_posts.post_status = 'private') ORDER BY wp_posts.post_date DESC

    Function translate_limit in translations.php is not working properly, i think
    $query = str_ireplace('SELECT ', 'SELECT TOP ' . $limit_matches[1] . ' ', $query);
    should be
    $query = str_ireplace('SELECT ', 'SELECT TOP ' . $limit_matches[4] . ' ', $query);

    can anyone confirm?

    http://wordpress.org/extend/plugins/wordpress-database-abstraction/

  2. Amigo Chan
    Member
    Posted 1 year ago #

    I have follow your instruction and modify the Line 750 of translation.php

    Yes, I may see the media library. But I cannot edit or delete anything now.

    Therefore, I am still looking for solution...

  3. Amigo Chan
    Member
    Posted 1 year ago #

    Here are more detail discussion:

    http://wordpress.org/support/topic/plugin-wp-db-abstraction-not-showing-posts?replies=7

    You have to change two sections. Both in function translate_limit().

    Section 1:

    From:'/LIMIT\s*(\d+)((\s*,?\s*)(\d+)*)(;{0,1})$/is'
    To: '/LIMIT\s*(\d+)((\s*,?\s*)(\d+)*)$/is';

    Section 2:

    From: count($limit_matches) == 5
    To: count($limit_matches) >= 5

    Wish it helps!

  4. puzsol
    Member
    Posted 1 year ago #

    Personally, I found that if I just changed the == to >=, then it broke the user listing... as it was translating this SQL:
    SELECT SQL_CALC_FOUND_ROWS wp_users.ID FROM wp_users WHERE 1=1 ORDER BY user_login ASC LIMIT 20

    and interpreting it as a "true_offset", which in turn was using an array slice to get rows 20 to 20.... not good.

    Also I couldn't publish any posts (probably for a similar reason).

    So I rewrote it to:

    // Check for true offset - ie "limit 40,20"... if first is 0, or there is no comma, then we can use SQL top - else use array_slice later
            if ( count($limit_matches) >= 5 && $limit_matches[1] == '0' ) {
                $limit_matches[1] = $limit_matches[4];
            } elseif ( count($limit_matches) >= 5 && strlen($limit_matches[3]) > 0 ) {
                $true_offset = true;
            }

    So far this (and this alone), seems to have fixed it.

    Oh, also I don't know if it makes a difference, but I figure the interpretation of SQL_CALC_FOUND_ROWS was also wrong... as in mysql it returns the selected rows (limits in place) as well as counting all that match... the replacing the column list with a * is potentially wrong - if ever column numbers rather than names are used... so I modified that to:

    if (stripos($query, 'SQL_CALC_FOUND_ROWS') > 0 ) {
    			$query = str_ireplace('SQL_CALC_FOUND_ROWS ', '' , $query);
                //$sql_calc_pos = stripos($query, 'SQL_CALC_FOUND_ROWS');
                //$from_pos = stripos($query, 'FROM');
                //$query = substr_replace($query,'* ', $sql_calc_pos, ($from_pos - $sql_calc_pos));
            }

    And in the new wordpress, I wasn't getting the user counts properly as it wasn't handling the translate_specific function wasn't matching the query (as it has %"administrator"% instead of %administrator%, etc)... so that was changed to:

    if ($this->preg_original == "SELECT COUNT(NULLIF(meta_value` LIKE '%administrator%', FALSE)), "
    . "COUNT(NULLIF(meta_value LIKE '%editor%', FALSE)), "
    . "COUNT(NULLIF(meta_value LIKE '%author%', FALSE)), "
    . "COUNT(NULLIF(meta_value LIKE '%contributor%', FALSE)), "
    . "COUNT(NULLIF(meta_value LIKE '%subscriber%', FALSE)), "
    . "COUNT(*) FROM " . $this->prefix . "usermeta WHERE meta_key = '" . $this->prefix . "capabilities'"
    || $this->preg_original ==
    "SELECT COUNT(NULLIF(meta_value LIKE '%\"administrator\"%', false))"
    . ", COUNT(NULLIF(meta_value LIKE '%\"editor\"%', false))"
    . ", COUNT(NULLIF(meta_value LIKE '%\"author\"%', false))"
    . ", COUNT(NULLIF(meta_value LIKE '%\"contributor\"%', false))"
    . ", COUNT(NULLIF(meta_value LIKE '%\"subscriber\"%', false))"
    . ", COUNT(*) FROM " . $this->prefix . "usermeta WHERE meta_key = '" . $this->prefix . "capabilities'"
    ) {
    $query = "SELECT SUM(case when meta_value LIKE '%administrator%' then 1 else 0 end) as ca
    , SUM(case when meta_value LIKE '%editor%' then 1 else 0 end) as cb
    , SUM(case when meta_value LIKE '%author%' then 1 else 0 end) as cc
    , SUM(case when meta_value LIKE '%contributor%' then 1 else 0 end) as cd
    , SUM(case when meta_value LIKE '%subscriber%' then 1 else 0 end) as ce
    , COUNT(*) as c FROM " . $this->prefix . "usermeta WHERE meta_key = '" . $this->prefix . "capabilities'";
    $this->preg_data = array();
    }`

    Which I think is a more efficient query anyway.

    Sorry for posting too much here.... think of it as a brain-dump.

Topic Closed

This topic has been closed to new replies.

About this Plugin

About this Topic