How to Dport proxy to work from home

Because of the gate, a simple -D alone doesn't work. Here is the magic formula:

dmb@percy:~$ ssh -v -o proxycommand="ssh gate proxy %h" \
  dbolser@login -D 23456

and that's that!

MySQL DB connection error (solved)

This error was driving me crazy:

1.20.2 installation : DB connection error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) (localhost).
Check the host, username and password and try again

because everything was correct, and there was nothing in the Apache error_log.

Turns out it was a selinux issue. I was able to connect fine after disabling selinux.

A Perl client and REST wrapper for the CiteXplore SOAP webservices

OBSOLETE! See the official EuropePMC REST API which is now used within SEQwiki in this template.



As part of my ongoing plan for world domination, I decided to write a REST wrapper for the SOAP webservice provided by CiteXplore, the EBI's Citation Database. At first I thought this might be a daunting task. However, CPAN and the nice people on freenode came to the rescue, as usual.


First, I installed SOAP::WSDL locally using my usual recipe, and then simply pointed the handy script it provides at the WSDL for the CiteXplore SOAP webservices:

wsdl2perl.pl -b CXPSC URL


This 'auto-created' the Perl client for the webservice. What impressed me most was the resulting perldoc, that told me exactly how to use the Perl client. For example, typing:

perldoc CXPSC/MyInterfaces/WSCitationImplService/WSCitationImplPort.pm


gave this output. Using the POD and CiteXplore's Web Services Reference Guide I created a simple test script that even works!


Next came the task of wrapping these functions in a REST server. Some people on freenode told me to use Mojolicious::Lite. Although it's trendy, it also seems to work with minimal fuss.

Here it is, but it isn't pretty:
https://github.com/dbolser/Perl-CiteXplore-SOAP-client/blob/master/rest_script.plx


Finally I used the newly created REST interface to pull citation data into SEQwiki using the External Data extension. Setting up the execution environment using App::perlbrew (my webhost didn't have the requisite version of Perl for Mojo) was also much easier than I imagined.

THE END

MySQL, what are the values for this column in all tables?

If you have a complex schema, you may have an ID column that is used in several tables. Lets say you want to count all values of all such columns... without having to think about which tables it occurs in? Then this is the stored procedure for you:

DELIMITER $$

DROP   PROCEDURE IF EXISTS GetAllDistinctValuesByColumnName$$
CREATE PROCEDURE           GetAllDistinctValuesByColumnName (ColName VARCHAR(255))

BEGIN

DECLARE no_more_tables INT DEFAULT 0;
DECLARE my_table VARCHAR(255); 

DECLARE cur_tables CURSOR FOR
  SELECT DISTINCT
    TABLE_NAME
  FROM
    INFORMATION_SCHEMA.COLUMNS
  WHERE
    TABLE_SCHEMA = DATABASE()
  AND
    COLUMN_NAME = ColName;

DECLARE CONTINUE HANDLER FOR NOT FOUND
  SET no_more_tables = 1;

/* Result table */
CREATE TEMPORARY TABLE temp_my_values (
  my_table VARCHAR(255) NOT NULL,
  my_value VARCHAR(255) NOT NULL,
  my_count INT UNSIGNED NOT NULL
);


OPEN cur_tables; 

FETCH cur_tables INTO my_table; 

REPEAT

SET @ugly_sql := CONCAT('
  INSERT INTO temp_my_values
  SELECT "', my_table, '", ', ColName, ', COUNT(*)
  FROM ', my_table, '
  GROUP BY ', ColName
);

-- SELECT @ugly_sql;

PREPARE mySt FROM @ugly_sql;
EXECUTE mySt;

FETCH cur_tables INTO my_table;

UNTIL no_more_tables = 1 
END REPEAT; 

CLOSE cur_tables; 

SELECT * FROM temp_my_values; 
DROP TABLE temp_my_values; 

END$$

DELIMITER ;

Getting a list of upcoming events from GCal

The aim is to email myself a list of all events in my calendar(s), along with 'days until...', to help me plan for upcoming events better.

I'm using Perl, because of CPAN.

So far, I've managed to connect and get event data.

• UPDATE, events now limited to future events.
• UPDATE, 'all' (10k) events now retrieved.
• UPDATE, the number of days remaining is now calculated.

See:
1) http://search.cpan.org/~plytle/Net-Google-Calendar-1.01/lib/Net/Google/Calendar.pm
2) http://search.cpan.org/~plytle/Net-Google-Calendar-1.01/lib/Net/Google/Calendar/Entry.pm


Here's the code:
http://github.com/dbolser/GCal-Upcoming

SQL of the day

SELECT
  TABLE_SCHEMA                                    AS "Data Base Name",
  SUM( DATA_LENGTH + INDEX_LENGTH ) / 1024 / 1024 AS "Data Base Size in MB"
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA IN
(
  SELECT
    SCHEMA_NAME
  FROM
    information_schema.SCHEMATA
  WHERE
    SCHEMA_NAME LIKE '%variation%'
)
GROUP BY
  table_schema
;

Gfail (and how to fix it)

As we all know, hate is the only legitimate emotion on the internet...

I really hate that Gmail has the audacity to assume it deserves two thirds of the screen width. Generally, I like to keep windows narrow, and normally that's fine. But noooo, Gmail is better than other webpages. Gmail is special...

The problem is, when I shrink the Gmail window down below a certain width, it decides to hide the vertical scroll bar... Google... It's a scroll bar... how could you fuck it up? They found a way!

Since I already use AdBlock* I was seeing a massively annoying empty white bar on the right of my emails, making it even more annoying that Gmail chooses to hide the scroll bar when I tried to shrink the window to remove this wasted space. Seriously... It's a fucking scroll bar! Why...

Anyway, the solution is to install the Minimalist App*, which lets you conveniently 'Hide Sidebar'. This removes all that crap on the right hand side of your emails, reclaiming about one third of the width of the Gmail window for, you know, THE CONTENT OF THE FUCKING EMAIL!!!

You can also hide all that +1 crap that they force down your throat too, and a bunch of other annoying stuff. It's really a great App!

Of course all this will break when Google decide that their current design isn't hogging enough of your screen as it is, but hopefully it can be repaired quickly by nice people like the authors of the above Apps. 

Thank you!

* Wow, I really hate that 'overlay' design on the Chrome Web Store!