Support PostgreSQL

[lippserd]

resolves #10

[dequbed]

Ohai, some feedback if I may: currently this module's view crashes on setups that don't have MySQL-PDO enabled in library/X509/Controller.php since PDO::MYSQL_ATTR_INIT_COMMAND doesn't exist:

Uncaught Error: Undefined class constant 'MYSQL_ATTR_INIT_COMMAND' in /usr/share/webapps/icingaweb2/modules/x509/library/X509/Controller.php:30
Stack trace:
#0 /usr/share/webapps/icingaweb2/modules/x509/application/controllers/UsageController.php(28): Icinga\Module\X509\Controller->getDb()
#1 /usr/share/webapps/icingaweb2/library/vendor/Zend/Controller/Action.php(507): Icinga\Module\X509\Controllers\UsageController->indexAction()
#2 /usr/share/webapps/icingaweb2/library/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch()
#3 /usr/share/webapps/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch()
#4 /usr/share/webapps/icingaweb2/library/Icinga/Application/Web.php(300): Zend_Controller_Front->dispatch()
#5 /usr/share/webapps/icingaweb2/library/Icinga/Application/webrouter.php(99): Icinga\Application\Web->dispatch()
#6 /usr/share/webapps/icingaweb2/public/index.php(4): require_once('/usr/share/weba...')
#7 {main}
thrown

As I'm using PostgreSQL for storage I would assume that that setting is a no-op anyway and simply commenting out the above two lines makes the view work so far as expected.

Otherwise this branch appears to work quite well with PostgreSQL 12.4; so far everything including giving multiple CIDR-prefixes and multiple ports in one job checks all ports on all addresses as expected.

[lippserd]

Thanks for the feedback. Very appreciated. The exception has been fixed.

[gibsonc]

So far it is working very well. The only issue I have found is if you try order by Expires you get an exception. It would seem unix_timestamp() is MySQL specific?

SQLSTATE[42883]: Undefined function: 7 ERROR: function unix_timestamp() does not exist LINE 1: ...ficate_id WHERE ccl.order = $1 ORDER BY CASE WHEN UNIX_TIMES... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

[lippserd]

Note: The Director hooks don't work with PostgreSQL yet.

[KKeXX]

So far it is working very well. The only issue I have found is if you try order by Expires you get an exception. It would seem unix_timestamp() is MySQL specific?

SQLSTATE[42883]: Undefined function: 7 ERROR: function unix_timestamp() does not exist LINE 1: ...ficate_id WHERE ccl.order = $1 ORDER BY CASE WHEN UNIX_TIMES... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

The UNIX_TIMESTAMP() function does not exist in PgSQL. A Solution could be to create the function in PgSQL:

CREATE OR REPLACE FUNCTION UNIX_TIMESTAMP() RETURNS integer
AS $$
BEGIN
    RETURN extract(epoch FROM now());
END;
$$ LANGUAGE plpgsql;

Then certificates can be sorted by Expires.

[kajla]

Hi, Any news about it?

[lippserd]

@raviks789 Please rebase and test.