Category Archives: Allgemein

Timezones and PostgreSQL

After the aforementioned talk (and in preparation to the next installment of it) I checked how to do the MySQL-Example in PostgreSQL. It’s easily possible, but it takes a bit more time to get around to it. But hey, it’s a tool for grown-ups, isn’t it? 😉

Again we create the table using this snippet:

CREATE TABLE datetime(
  zeit timestamp without time zone,
  zone character varying(255)
)

INSERT INTO datetime (zeit, zone)
VALUES
    ('2014-03-04 12:23:34','Europe/Berlin'),
    ('2016-05-03 23:12:23','Europe/Busingen'),
    ('2016-05-03 23:12:23','America/Chicago')
;

So to select all entries that are before 14:00H in UTC-Timezone in PostgreSQL we’ll use this query:

SELECT * FROM datetime 
WHERE 
    extract(HOUR FROM zeit AT TIME ZONE zone AT TIME ZONE 'UTC')
    <= 14;

Here we tell PostgreSQL to interpret the value in zeit as localtime in timezone zone and then to convert that to timezone UTC. It will output this:

2014-03-04 12:23:34 | Europe/Berlin
2016-05-03 23:12:23 | America/Chicago

Composer and self-signed certificates

Today I wanted to add a package from our internal satis-repository to a composer.json-file. Easy thing!

I added the satis-server as repo to the composer.json like this:

    {
      "repositories": [{
        "type": "composer",
        "url": "https://example.com/satis",
      }]
    }

Fair enough! That’s it! Run composer and be happy:

$ composer require vendor/package

[Composer\Downloader\TransportException]
  The "https:/example.com/satis/packages.json" file could not be downloaded: SSL operation failed with code 1. OpenSSL Error messages:
  error:14090086:SSL routines:ssl3_get_server_certificate:certificate verify failed
  Failed to enable crypto
  failed to open stream: operation failed


require [--dev] [--prefer-source] [--prefer-dist] [--no-plugins] [--no-progress] [--no-update] [--update-no-dev] [--update-with-dependencies] [--ignore-platform-reqs] [--sort-packages] [-o|--optimize-autoloader] [-a|--classmap-authoritative] [--] [<packages>]...

WTF…???

Ah, yes! The servers certificate is signed by our internal Root-CA. As it’s an internal server that’s what we do. But how to get composer to know that? It took me a while. Adding the Root-Certificate to OpenSSL didn’t bring the expected results (might be due to Homebrew) as didn’t adding the cert to PHPs keystore (might be due to some weird setup on my machine).

But there surely has to be a way of getting satis (or Toran) up and running with self-signed certs!

There were many hacks around that disabled certificate validation altogether but that’s not what I wanted. And after a short question on Twitter I got a few ideas. Alexander Tureks idea was great: Adding “verify-peer” : “false” to the ssl-options of the repository. Sadly that didn’t do the trick. Finally Jordi Boggiano gave me a hint to a feature I hadn’t found in the composer-docs before: Add the RootCA-Certificate to the ssl-options of the repository (and to the project).

So now my composer.json looks like this:

{
  "repositories": [{
    "type": "composer",
    "url": "https://example.com/satis",
    "options" : {
      "ssl" : {
        "cafile" : "myrootca.crt"
      }
    }
  }]
}

The file “myrootca.crt” is a PEM-file that only contains the root-certificate. You can get it by calling openssl x509 -in <(openssl s_client -connect example.com:443 -prexit 2>/dev/null) > myrootca.crt. And myrootca.crt needs to be on the same level as the composer.json in your project.

Thanks Jordi for the fast response!

Hope that helps someone 😉

On inclusiveness

I’m from a country that has a – let’s describe it euphemistically – interesting history of inclusiveness. And perhaps that is why I’m rather riven towards the current discussion about inclusiveness and Codes of Conduct.

When I started to read about whether or not it would be a good idea for a conference to select certain speakers I pricket my ears. How can a conference be inclusive when such persons are allowed to speak there. I could definitely understand that people that think of themselves as better than others or that openly despise different groups of people are not what we want at conferences or usergroup-events.

Continue reading On inclusiveness

Testing code with phpunit on travis-ci for PHP 5.5 and PHP7

I’ve recently added a pull-request to Zend\Ldap and realized that some of the tests were failing on PHP7. Reason was that Error-handling has changed in PHP7 and the used PHPUnit-version 4.x didn’t know how to handle that. So I needed a version of PHPUnit that understood that, which is PHPUnit 5.x. Sadly the minimum PHP-Version for that is PHP5.6 so now the tests in PHP5.5 where failing.

How to handle that as there’s no way to tell composer to install a certain package-version when a certain PHP-Version is found and a different package-version for another PHP-version (At least I’m not aware of one – if you know of one please let me know!)

So what to do? As the tests are executed on Travis-CI I decided to rewrite the compser.json-file when the tests are executed for PHP5.5.

And here’s how I did that:

For Zend\Ldap we have this snippet in our .travis.yml-file:

before_install:
  - if [[ ${TRAVIS_PHP_VERSION:0:3} == "5.5" ]]; then composer require --dev --no-update phpunit/phpunit ~4; fi

install:  
  - travis_retry composer install --no-interaction --ignore-platform-reqs

The interesting part is the before_install part which checks whether the first three characters of the PHP-Version are 5.5 and then requires PHPUnit version 4. The default in the composer.json is PHPUnit version 5 so as soon as Zend\Ldap drops support for PHP5.5 we don’t have to remember to change that file, it just phases out.

After that PHPUnit 4 is installed and the tests run smoothly in all currently supported PHP-Versions (Actually they currently don’t, but that’s a different story and has nothing to do with the different PHPUnit-versions 😉 )

Update
Thanks to Abdul Malik Ikhsan I’ve found out about another way to require a more recent version of PHPUnit.

Thanks!

A link to your Call for Papers

You have an upcoming conference for which you run a Call for Papers (CfP)? Then why not let everyone know by adding a link to where we can find more informations?

Something like the following could be parsed automatically and would allow easier finding of your CfP:

<link rel="cfp" href="[Link to your CfP-page]" data-closes="[ISO-Date of the end of the CfP]"/>

This could then look like this:

<link rel="cfp" href="https://example.com/cfp" data-closes="2015-12-24 12:00:00+02:00" />

That way websites displaying CfPs could fetch the information that there is a CfP from your main website and you would not need to provide extra informations about a CfP on a different place.

And while we’re at it: Your Event surely has a geographical location, so why not add that one via the following resource?

<link rel="venue" href="geo:[latitude],[longitude]"/>

It uses a geo-URI as specified in RFC5870 and can therefore also been read automatically to check for the venue of your event.

Alternatively you can include information about your event or the location using metadata as defined by schema.org. That way you can add even more informations about your event or the location than using a simple link. Thanks to Jurian Sluiman for reminding me!