PW 3.0.15: New quick-tree, debug, view panels and more

[ryan]

This week's version of ProcessWire adds several new panels that provide simpler and more direct access to the page tree, page view and debug tools. New page view options also include multi-language selection and configuration support. And if that's not enough, we've also got install-time utf8mb4 support and more!

https://processwire.com/blog/posts/pw-3.0.15/

[Tom.]

Amazing update! Thanks Ryan

[Joss]

Very cute!

[asbjorn]

Great, getting better and better every week!

Just a note: The update does not show in ProcessWireUpgrade.

[LostKobrakai]

Just click the name and it's retrieving a fresh copy from github even for the same version number.

[Ivan Gretsky]

I am wondering is there a way to alter MyISAM tables to InnoDB on a working installation. Wanna try it for the increase in speed for the daily service script. Or is it a bad idea?

[netcarver]

@Ivan,

Back in 2012, I decided to compare access times of a locally hosted site both before and after I switched it over to InnoDB. At the time I was in a dialog with Ryan about the benefits of, at least, allowing InnoDB as one of the DB engine choices during install. After all these years, I am delighted to see it finally arrive.

Here's a screenshot of the side-by-side results of running 500 requests vs a, then standard, PW install - one using MyISAM and one using InnoDB.

In general, MyISAM was faster than InnoDB - but it also had a larger standard deviation in the delivery times. Don't worry about the "failures" reported above - remember that the default install back then would serve one of a set of random images and the test tool looks to see which responses matched the first response - a non-match counts as a "failure".

InnoDB did have a slightly higher throughput in terms of requests served per second.

[Ivan Gretsky]

@netcarver: So you say it's not worth the effort?

[BitPoet]

I am wondering is there a way to alter MyISAM tables to InnoDB on a working installation. Wanna try it for the increase in speed for the daily service script. Or is it a bad idea?

You can alter an existing table simply by issuing

ALTER TABLE your-table-name ENGINE=InnoDB;

Though you may want to keep the original MyISAM table around.

Here's a small bootstrap script to drop into the templates folder that converts all tables, either inplace (if $keepOld near the top is set to false) or creating a copy and keeping the MyISAM tables around postfixed with "_orig". It also writes a timestamped SQL file into the templates folder that allows you to switch back to MyISAM. Small caveat: if you select to keep your old tables, those will replace the generated InnoDB tables on restore, which means that all PW changes in between are lost - this may or may not be desired.

So to use it, simply drop it in the templates folder of the PW installation in question and run it from the command line. It will check if you MySQL version is recent enough.

<?php

/**
 * Convert all tables in PW's database from MyISAM to InnoDB
 */
 
include('../../index.php');

// Set this to false if you do not want to keep your MyISAM tables around
// with an _orig postfix
$keepOld = true;

// *****************************************************************************

$sql = "select
    substring(version(), 1, 1) as vmajor,
    substring(version(), 3, 1) as vminor
";
$stmt = $database->query($sql);

if($stmt === false) die("Something went wrong determining the version number!");

$row = $stmt->fetch(PDO::FETCH_ASSOC);
$versionOK = false;

if($row['vmajor'] > 5 || ($row['vmajor'] == 5 && $row['vminor'] >= 6))
    $versionOK = true;

if(!$versionOK) {
    die("You need MySQL 5.6 or later for InnoDB fulltext support!");
}

// Version check finished, get list of tables

$stmt = $database->prepare(
    "SELECT table_name" .
    "  FROM information_schema.tables" .
    " WHERE table_schema = '" . $config->dbName . "'" .
    "   AND lower(engine) != 'innodb'"
);

$count = 0;

if($stmt->execute()) {
    $tbls = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo "Found " . count($tbls) . " tables not running on InnoDB" . PHP_EOL;
    
    if(count($tbls) == 0) {
        echo "Nothing to do!" . PHP_EOL;
        exit;
    }
    
    $ts = strftime("%Y%m%d_%H%M%S");
    $restore = fopen("restore_to_myisam_{$ts}.sql", "w+");
    echo "- writing restore commands to file restore_to_myisam_{$ts}.sql" . PHP_EOL;
    
    // Convert each table
    foreach($tbls as $tbl) {
        $tabname = $tbl['table_name'];
        echo $tabname . PHP_EOL;
        
        if($keepOld) {
            // Create a copy of each table, convert the new table,
            // copy the content and switch table names
            
            $sql = "CREATE TABLE {$tabname}_convert LIKE $tabname";
            if($database->exec($sql) === false) continue;
            echo "... Created copy of " . $tabname . PHP_EOL;
            
            $sql = "ALTER TABLE {$tabname}_convert ENGINE=InnoDB";    
            if($database->exec($sql) === false) continue;
            echo "... Changed engine to InnoDB" . PHP_EOL;
            
            $sql = "INSERT INTO {$tabname}_convert SELECT * FROM $tabname";
            if($database->exec($sql) === false) continue;
            echo "... Copied content to InnoDB table" . PHP_EOL;
            
            $sql = "RENAME TABLE $tabname TO {$tabname}_orig," .
                   " {$tabname}_convert TO $tabname";                               
            if($database->exec($sql) === false) continue;
            echo "... Switched tables" . PHP_EOL;
            
            fputs($restore, "RENAME TABLE $tabname TO {$tabname}_innodb," .
                            " {$tabname}_orig TO $tabname;" . PHP_EOL);
            fputs($restore, "DROP TABLE {$tabname}_innodb;" . PHP_EOL);
            $count++;
            
        } else {
            // Convert tables in place
            $sql = "ALTER TABLE $tabname ENGINE=InnoDB";
            if($database->exec($sql) === false) continue;
            
            echo "... " . $tabname . " converted" . PHP_EOL;
            fputs($restore, "ALTER TABLE $tabname ENGINE=MyISAM;" . PHP_EOL);
            $count++;
        }
    }
    fclose($restore);
}

echo "Converted $count tables from MyISAM to InnoDB" . PHP_EOL;

Edit: incorporated @LostKobrakai's suggestion to avoid nesting if statements.

[LostKobrakai]

@BitPoet Just a suggestion: Maybe inverting those if statements could make this a bit less nested.

if($database->exec($sql) !== false){
  […]
}
// to
if($database->exec($sql) === false) continue; // or break;

[BitPoet]

@BitPoet Just a suggestion: Maybe inverting those if statements could make this a bit less nested.

Thanks, @LostKobrakai, that's a good suggestion that adds a lot of readablity. I've amended the script.

[Martijn Geerts]

Love the new panel thingy! Especially the 'side effect' that you can use it as some sort mediaquery tester while dragging the pane .

[Tom.]

Next I would love to see quick search on the page tree still showing the parents if any.

[netcarver]

@netcarver: So you say it's not worth the effort?

I like InnoDB, it has row-level locking and is ACID compliant - so I'm biased. That said, I'm not really making a call one way or the other (I know nothing about your application) I was just trying to provide the only performance data I have.

[ryan]

In my case, it's only been worth the effort for one site that I deal with that is doing constant imports all day. It makes a difference in the performance of other requests there. There are a lot of technical benefits to InnoDB relative to MyISAM for sure. But in the PW context (or most CMS contexts) it's hard to derive much value from those benefits until you run into specific needs or scale. In 15+ years and thousands of MyISAM tables around here, I've never experienced any kind of data corruption or loss associated with MyISAM tables. If starting something new I think InnoDB is worthwhile if it can be supported in your environment, but for most cases of existing PW installations I don't think it's worth the effort unless the circumstances mentioned in post are present. From what I understand, MyISAM's fulltext indexes may still be stronger than InnoDB's, though not positive that's still the case. Btw, PW will use InnoDB either way for cases where it makes the most difference. 

[Tom.]

Would also be awesome for be able to utilise the PW Panels for the PageTable field. 

[LostKobrakai]

How would this make things different to the current modal solution?

[adrian]

I have just started playing with the page tree panel and I think I'd rather see it appear beside the current view's content, rather than on top of it. That way I could leave it open and return to what I was doing - it can be a handy reference for other tasks. It would work the same way as the side menu in the Reno theme works. I'd actually like to see it remember it's open/collapsed status also. I know you are worried about the overhead of rendering a page tree on every request, but with the new page tree from 3.0.8, isn't that less of an issue now? Now it's just rendering, rather than querying? Or do I have that wrong - I actually haven't checked the source to see what's actually going on there.

I am also not sure why it has to be so wide - currently to close it you have to move your pointer to the middle of the screen to either click the panel label or the main page to make it disappear. Any reason it can't be the width of the widest page title?

Also, I have what I think is a bug. If you are on the Setup page: http://pw.dev/processwire/setup/ and you click the sitemap icon in the breadcrumbs, it loads the Page Tree fully, not in a panel.

[renobird]

Ryan,

I'm just seeing this now — doooode — super awesome!

I've been head down in some major projects the past few months, so I haven't had a chance to do much more than read the weekly blog posts.
I can't wait to dive into some of these new features. Thanks for keeping the Reno Amin Theme up-to-date with all this new stuff.

[Peter Knight]

Love these new additions too.

I keep thinking the new quick tree panel should open from the left side on the Reno Theme instead of the right.

The icon placement itself makes sense but I get a sense that in a left to right world, it would feel more natural on the left.

[teppo]

I have just started playing with the page tree panel and I think I'd rather see it appear beside the current view's content, rather than on top of it. That way I could leave it open and return to what I was doing - it can be a handy reference for other tasks.

My thoughts exactly. Currently these panels are fun, but this could increase their potential use cases exponentially

[Ivan Gretsky]

I had a question about turning the panels off. It seems it does not belong to News & Announcements, so moved it to a separate topic.

Edited March 17, 2017 by Ivan Gretsky
A question moved to another topic