SqlExample.php

<?php
/*
 * Copyright 2019 GridGain Systems, Inc. and Contributors.
 *
 * Licensed under the GridGain Community Edition License (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     https://www.gridgain.com/products/software/community-edition/gridgain-community-edition-license
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

require_once __DIR__ . '/../vendor/autoload.php';

use Apache\Ignite\Client;
use Apache\Ignite\ClientConfiguration;
use Apache\Ignite\Exception\ClientException;
use Apache\Ignite\Cache\CacheInterface;
use Apache\Ignite\Cache\CacheConfiguration;
use Apache\Ignite\Query\SqlFieldsQuery;

// This example demonstrates GridGain usage as an SQL database:
// - connects to a node
// - creates a cache, if it doesn't exist
// - creates tables (CREATE TABLE)
// - creates indices (CREATE INDEX)
// - writes data of primitive types into the tables (INSERT INTO table)
// - reads data from the tables (SELECT ...)
// - deletes tables (DROP TABLE)
// - destroys the cache
class SqlExample {
    const ENDPOINT = '127.0.0.1:10800';

    const COUNTRY_CACHE_NAME = 'Country';
    const CITY_CACHE_NAME = 'City';
    const COUNTRY_LANGUAGE_CACHE_NAME = 'CountryLng';
    const DUMMY_CACHE_NAME = 'SqlExample_Dummy';

    public function start(): void
    {
        $client = new Client();
        try {
            $client->connect(new ClientConfiguration(SqlExample::ENDPOINT));

            $cache = $client->getOrCreateCache(
                SqlExample::DUMMY_CACHE_NAME,
                (new CacheConfiguration())->setSqlSchema('PUBLIC'));

            $this->createDatabaseObjects($cache);
            $this->insertData($cache);

            $countryCache = $client->getCache(SqlExample::COUNTRY_CACHE_NAME);
            $cityCache = $client->getCache(SqlExample::CITY_CACHE_NAME);

            $this->getMostPopulatedCities($countryCache);
            $this->getTopCitiesInThreeCountries($cityCache);
            $this->getCityDetails($cityCache, 5);

            $this->deleteDatabaseObjects($cache);
            $client->destroyCache(SqlExample::DUMMY_CACHE_NAME);
        } catch (ClientException $e) {
            echo('ERROR: ' . $e->getMessage() . PHP_EOL);
        } finally {
            $client->disconnect();
        }
    }

    public function createDatabaseObjects(CacheInterface $cache): void
    {
        $createCountryTable = 'CREATE TABLE Country (' .
            'Code CHAR(3) PRIMARY KEY,' .
            'Name CHAR(52),' .
            'Continent CHAR(50),' .
            'Region CHAR(26),' .
            'SurfaceArea DECIMAL(10,2),' .
            'IndepYear SMALLINT(6),' .
            'Population INT(11),' .
            'LifeExpectancy DECIMAL(3,1),' .
            'GNP DECIMAL(10,2),' .
            'GNPOld DECIMAL(10,2),' .
            'LocalName CHAR(45),' .
            'GovernmentForm CHAR(45),' .
            'HeadOfState CHAR(60),' .
            'Capital INT(11),' .
            'Code2 CHAR(2)' .
        ') WITH "template=partitioned, backups=1, CACHE_NAME=' . SqlExample::COUNTRY_CACHE_NAME . '"';

        $createCityTable = 'CREATE TABLE City (' .
            'ID INT(11),' .
            'Name CHAR(35),' .
            'CountryCode CHAR(3),' .
            'District CHAR(20),' .
            'Population INT(11),' .
            'PRIMARY KEY (ID, CountryCode)' .
        ') WITH "template=partitioned, backups=1, affinityKey=CountryCode, CACHE_NAME=' . SqlExample::CITY_CACHE_NAME . '"';

        $createCountryLanguageTable = 'CREATE TABLE CountryLanguage (' .
            'CountryCode CHAR(3),' .
            'Language CHAR(30),' .
            'IsOfficial CHAR(2),' .
            'Percentage DECIMAL(4,1),' .
            'PRIMARY KEY (CountryCode, Language)' .
        ') WITH "template=partitioned, backups=1, affinityKey=CountryCode, CACHE_NAME=' . SqlExample::COUNTRY_LANGUAGE_CACHE_NAME . '"';

        // create tables
        $cache->query(new SqlFieldsQuery($createCountryTable))->getAll();
        $cache->query(new SqlFieldsQuery($createCityTable))->getAll();
        $cache->query(new SqlFieldsQuery($createCountryLanguageTable))->getAll();

        // create indices
        $cache->query(new SqlFieldsQuery(
            'CREATE INDEX idx_country_code ON city (CountryCode)'))->getAll();
        $cache->query(new SqlFieldsQuery(
            'CREATE INDEX idx_lang_country_code ON CountryLanguage (CountryCode)'))->getAll();

        echo('Database objects created' . PHP_EOL);
    }

    private function insertData(CacheInterface $cache): void
    {
        $cities = [
            ['New York', 'USA', 'New York', 8008278],
            ['Los Angeles', 'USA', 'California', 3694820],
            ['Chicago', 'USA', 'Illinois', 2896016],
            ['Houston', 'USA', 'Texas', 1953631],
            ['Philadelphia', 'USA', 'Pennsylvania', 1517550],
            ['Moscow', 'RUS', 'Moscow (City)', 8389200],
            ['St Petersburg', 'RUS', 'Pietari', 4694000],
            ['Novosibirsk', 'RUS', 'Novosibirsk', 1398800],
            ['Nizni Novgorod', 'RUS', 'Nizni Novgorod', 1357000],
            ['Jekaterinburg', 'RUS', 'Sverdlovsk', 1266300],
            ['Shanghai', 'CHN', 'Shanghai', 9696300],
            ['Peking', 'CHN', 'Peking', 7472000],
            ['Chongqing', 'CHN', 'Chongqing', 6351600],
            ['Tianjin', 'CHN', 'Tianjin', 5286800],
            ['Wuhan', 'CHN', 'Hubei', 4344600]
        ];

        $cityQuery = new SqlFieldsQuery(
            'INSERT INTO City(ID, Name, CountryCode, District, Population) VALUES (?, ?, ?, ?, ?)');

        for ($i = 0; $i < count($cities); $i++) {
            $cache->query($cityQuery->setArgs($i, ...$cities[$i]))->getAll();
        }

        $countries = [
            ['USA', 'United States', 'North America', 'North America',
                9363520.00, 1776, 278357000, 77.1, 8510700.00, 8110900.00,
                'United States', 'Federal Republic', 'George W. Bush', 3813, 'US'],
            ['RUS', 'Russian Federation', 'Europe', 'Eastern Europe',
                17075400.00, 1991, 146934000, 67.2, 276608.00, 442989.00,
                'Rossija', 'Federal Republic', 'Vladimir Putin', 3580, 'RU'],
            ['CHN', 'China', 'Asia', 'Eastern Asia',
                9572900.00, -1523, 1277558000, 71.4, 982268.00, 917719.00,
                'Zhongquo', 'PeoplesRepublic', 'Jiang Zemin', 1891, 'CN']
        ];

        $countryQuery = new SqlFieldsQuery('INSERT INTO Country(' .
            'Code, Name, Continent, Region, SurfaceArea,' .
            'IndepYear, Population, LifeExpectancy, GNP, GNPOld,' .
            'LocalName, GovernmentForm, HeadOfState, Capital, Code2)' .
            'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');

        foreach ($countries as $country) {
            $cache->query($countryQuery->setArgs(...$country))->getAll();
        }

        echo('Data are inserted' . PHP_EOL);
    }

    private function getMostPopulatedCities(CacheInterface $countryCache): void
    {
        $query = new SqlFieldsQuery(
            'SELECT name, population FROM City ORDER BY population DESC LIMIT 10');

        $cursor = $countryCache->query($query);

        echo('10 Most Populated Cities:' . PHP_EOL);

        foreach ($cursor as $row) {
            echo(sprintf('    %d people live in %s%s', $row[1], $row[0], PHP_EOL));
        }
    }

    private function getTopCitiesInThreeCountries(CacheInterface $countryCache): void
    {
        $query = new SqlFieldsQuery(
            "SELECT country.name, city.name, MAX(city.population) as max_pop FROM country
            JOIN city ON city.countrycode = country.code
            WHERE country.code IN ('USA','RUS','CHN')
            GROUP BY country.name, city.name ORDER BY max_pop DESC LIMIT 3");

        $cursor = $countryCache->query($query);

        echo('3 Most Populated Cities in US, RUS and CHN:' . PHP_EOL);

        foreach ($cursor->getAll() as $row) {
            echo(sprintf('    %d people live in %s, %s%s', $row[2], $row[1], $row[0], PHP_EOL));
        }
    }

    private function getCityDetails(CacheInterface $cityCache, int $cityId): void
    {
        $query = (new SqlFieldsQuery('SELECT * FROM City WHERE id = ?'))->
            setArgs($cityId)->
            setIncludeFieldNames(true);

        $cursor = $cityCache->query($query);

        $fieldNames = $cursor->getFieldNames();

        foreach ($cursor->getAll() as $city) {
            echo('City Info:' . PHP_EOL);
            for ($i = 0; $i < count($fieldNames); $i++) {
                echo(sprintf('    %s : %s%s', $fieldNames[$i], $city[$i], PHP_EOL));
            }
        }
    }

    private function deleteDatabaseObjects($cache): void
    {
        $cache->query(new SqlFieldsQuery('DROP TABLE IF EXISTS Country'))->getAll();
        $cache->query(new SqlFieldsQuery('DROP TABLE IF EXISTS City'))->getAll();
        $cache->query(new SqlFieldsQuery('DROP TABLE IF EXISTS CountryLanguage'))->getAll();

        echo('Database objects dropped' . PHP_EOL);
    }
}

$sqlExample = new SqlExample();
$sqlExample->start();