Cleanly create SQL with SQL::Abstract::More

Tags:

Some years ago now, I had written my own wrapper around DBI to make common SQL statements easier to write. Awhile back I found SQL::Abstract and recently revisited some old code to bring it up to the modern era. I found that the related SQL::Abstract::More was almost a direct replacement for what I had written − except with more features and better debugged. Here's an example:

#!/usr/env/perl

use strict;
use warnings;

use SQL::Abstract::More;

my $sqla = SQL::Abstract::More->new;

# More maintainable with named parameters.
($stmt, @bind) = $sqla->select(
    -columns => [qw(listname id description from_email),
         'mailing_list.active|active',
         "COUNT(subscriber.person_id)|subcribercount",
    ],
    -from => [qw(mailing_list subscriber)],
    -where => {'subscriber.list_id' => {'=', \ 'mailing_list.id'},
           'subscriber.active' => {'<>', \ 0}
    },
    -group_by => "list_id",
    );

print "$stmt\n" . join(',',@bind) . "\n";

The Above example produces the code ready to pass to SQL:

    SELECT listname, id, description, from_email, 
      mailing_list.active AS active, 
      COUNT(subscriber.person_id) AS subcribercount
    FROM mailing_list, subscriber
    WHERE ( ( subscriber.active <> 0 AND 
      subscriber.list_id = mailing_list.id ) ) 
    GROUP BY list_id

After that, it's just a matter of execution:

my $sth = $dbh->prepare($stmt);
$sqla->bind_params($sth, @bind);
$sth->execute;

You could do the same thing with SQL::Abstract itself, but you have to use somewhat-undocumented features for the "as" and "group by" pieces. Specifically, you can use a scalar reference for a literal field name in the columns part, and shove the "group by" clause into the order field; but you'll have to build the group clause yourself:

use SQL::Abstract;

my $sql = SQL::Abstract->new;

my ($stmt, @bind) =
    $sql->select([qw(mailing_list subscriber)], 
                 [qw(listname id description from_email),
                  \ "mailing_list.active AS active",
                  \ "COUNT(subscriber.person_id) AS subcribercount"
                 ],
                 {'subscriber.list_id' => \ '= mailing_list.id',
                  'subscriber.active' => \ '<> 0'},
                 "asc GROUP by list_id"
    );
print "$stmt\n" . join(',',@bind) . "\n";

Altogether, SQL::Abstract::More is superior.

Mass Editing Posts' Timestamps to Attachment Dates

Tags:

I had migrated a portfolio website to WordPress, and used the Add Media from Server plugin to make the various files into attachments. I made sure to check the option that makes the attachment's date equal to the file's timestamp.

Then I added the various posts to the site, and used the Change Attachment Parent plugin to attach the various media files to their proper parent post.

The final step was to set each post's publish time to the date of that post's newest attachment. A bit of MySQL wizardry does this:

update wp_posts as pp
      inner join(
        select post_parent as pic_parent, ID as pic_id, max(post_date) as pic_date
        from wp_posts
        where post_parent>0 and post_type='attachment'
        group by post_parent
      ) as attach
      on pp.ID=attach.pic_parent
   set pp.post_date=attach.pic_date, pp.post_date_gmt=attach.pic_date;

Debugging web pages using MySQL

Tags:

mysql> <span style="text-decoration: underline;">show variables like '%log%';
</span>+-------------------------------------+---------------------------------+
| Variable_name                       | Value                           |
+-------------------------------------+---------------------------------+
| back_log                            | 50                              |
...
| general_log                         | OFF                             |
| general_log_file                    | /var/run/mysqld/mysqld.log      |
...
| log                                 | OFF                             |
| log_bin                             | OFF                             |
...
| log_error                           | /var/log/mysqld.log             |
| log_output                          | FILE                            |
...
| slow_query_log                      | OFF                             |
| slow_query_log_file                 | /var/run/mysqld/mysqld-slow.log |
| sql_log_bin                         | ON                              |
| sql_log_off                         | OFF                             |
| sql_log_update                      | ON                              |
| sync_binlog                         | 0                               |
+-------------------------------------+---------------------------------+
40 rows in set (0.00 sec)

mysql> <span style="text-decoration: underline;">SET GLOBAL general_log = 'ON';</span>
^Z
$ <span style="text-decoration: underline;">sudo less /var/run/mysqld/mysqld.log</span>
... (log entries scroll) ...
$ <span style="text-decoration: underline;">fg</span>
mysql> <span style="text-decoration: underline;">SET GLOBAL general_log = 'OFF';</span>
mysql> QUIT;
$