#

# Copyright 2022 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.

#

from helpers.sql_helper import TableNames, Query, TestData

from pygridgain import Client

# establish connection

client = Client()

with client.connect('127.0.0.1', 10800):

# create tables

for query in [

Query.COUNTRY_CREATE_TABLE,

Query.CITY_CREATE_TABLE,

Query.LANGUAGE_CREATE_TABLE,

]:

client.sql(query)

# create indices

for query in [Query.CITY_CREATE_INDEX, Query.LANGUAGE_CREATE_INDEX]:

client.sql(query)

# load data

for row in TestData.COUNTRY:

client.sql(Query.COUNTRY_INSERT, query_args=row)

for row in TestData.CITY:

client.sql(Query.CITY_INSERT, query_args=row)

for row in TestData.LANGUAGE:

client.sql(Query.LANGUAGE_INSERT, query_args=row)

# 10 most populated cities (with pagination)

with client.sql('SELECT name, population FROM City ORDER BY population DESC LIMIT 10') as cursor:

print('Most 10 populated cities:')

for row in cursor:

print(row)

# Most 10 populated cities:

# ['Mumbai (Bombay)', 10500000]

# ['Shanghai', 9696300]

# ['New York', 8008278]

# ['Peking', 7472000]

# ['Delhi', 7206704]

# ['Chongqing', 6351600]

# ['Tianjin', 5286800]

# ['Calcutta [Kolkata]', 4399819]

# ['Wuhan', 4344600]

# ['Harbin', 4289800]

print('-' * 20)

# 10 most populated cities in 3 countries (with pagination and header row)

MOST_POPULATED_IN_3_COUNTRIES = '''

SELECT country.name as country_name, city.name as city_name, MAX(city.population) AS max_pop FROM country

JOIN city ON city.countrycode = country.code

WHERE country.code IN ('USA','IND','CHN')

GROUP BY country.name, city.name ORDER BY max_pop DESC LIMIT 10

'''

with client.sql(MOST_POPULATED_IN_3_COUNTRIES, include_field_names=True) as cursor:

print('Most 10 populated cities in USA, India and China:')

table_str_pattern = '{:15}\t| {:20}\t| {}'

print(table_str_pattern.format(*next(cursor)))

print('*' * 50)

for row in cursor:

print(table_str_pattern.format(*row))

# Most 10 populated cities in USA, India and China:

# COUNTRY_NAME | CITY_NAME | MAX_POP

# **************************************************

# India | Mumbai (Bombay) | 10500000

# China | Shanghai | 9696300

# United States | New York | 8008278

# China | Peking | 7472000

# India | Delhi | 7206704

# China | Chongqing | 6351600

# China | Tianjin | 5286800

# India | Calcutta [Kolkata] | 4399819

# China | Wuhan | 4344600

# China | Harbin | 4289800

print('-' * 20)

# Show city info

with client.sql('SELECT * FROM City WHERE id = ?', query_args=[3802], include_field_names=True) as cursor:

field_names = next(cursor)

field = list(*cursor)

print('City info:')

for field_name, field_value in zip(field_names * len(field), field):

print(f'{field_name}: {field_value}')

# City info:

# ID: 3802

# NAME: Detroit

# COUNTRYCODE: USA

# DISTRICT: Michigan

# POPULATION: 951270

# Clean up

for table_name in TableNames:

result = client.sql(Query.DROP_TABLE.format(table_name.value))