[RFC]API endpoint for combined Joomla version & PHP version

[alikon]

Create an API endpoint for retrieving the Joomla version and PHP version in a combined metric. as reported on https://twitter.com/RegularLabs/status/1036170032093560832

to do

• create a new table
• populate the new table with current data
• implement the insert and update triggers
• expose the new api endponit

[alikon]

so i'm thinking about this new table:

CREATE TABLE IF NOT EXISTS `#__jstats_counter_cms_php_version` (
  `cms_version` varchar(15) NOT NULL,
  `php_version` varchar(15) NOT NULL,
  `count` INT NOT NULL,
  PRIMARY KEY (`cms_version`,`php_version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;

and for this script for the 1st population of the new table

INSERT INTO `#__jstats_counter_cms_php_version`
SELECT  cms_version, php_version , COUNT(*) as count
FROM #__jstats 
GROUP BY cms_version, php_version;

and this for the insert trigger

 IF NOT EXISTS (SELECT 1
      FROM `#__jstats_counter_cms_php_version` AS counter
      WHERE NEW.php_version = counter.php_version
        and NEW.cms_version = counter.php_cms_version
    )
    THEN
      INSERT INTO `#__jstats_counter_cms_php_version` (cms_version, php_version, count) VALUES (NEW.cms_version, NEW.php_version, 1);
    ELSE
      UPDATE `#__jstats_counter_cms_php_version`
      SET count = count + 1
      WHERE `php_version` = NEW.php_version
    and cms_version = NEW.php_cms_version
    END IF;

but still thinking about the right update trigger...

[alikon]

closing for #40