#

# 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 pprint import pprint

from helpers.converters import obj_to_dict

from helpers.sql_helper import TableNames, Query, TestData

from pygridgain import Client

from pygridgain.datatypes.prop_codes import PROP_NAME, PROP_QUERY_ENTITIES

# 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)

# examine the storage

result = client.get_cache_names()

pprint(result)

# ['SQL_PUBLIC_CITY', 'SQL_PUBLIC_COUNTRY', 'SQL_PUBLIC_COUNTRYLANGUAGE']

city_cache = client.get_or_create_cache('SQL_PUBLIC_CITY')

pprint(city_cache.settings[PROP_NAME])

# 'SQL_PUBLIC_CITY'

pprint(city_cache.settings[PROP_QUERY_ENTITIES])

# [{'field_name_aliases': [{'alias': 'DISTRICT', 'field_name': 'DISTRICT'},

# {'alias': 'POPULATION', 'field_name': 'POPULATION'},

# {'alias': 'COUNTRYCODE', 'field_name': 'COUNTRYCODE'},

# {'alias': 'ID', 'field_name': 'ID'},

# {'alias': 'NAME', 'field_name': 'NAME'}],

# 'key_field_name': None,

# 'key_type_name': 'SQL_PUBLIC_CITY_081f37cc8ac72b10f08ab1273b744497_KEY',

# 'query_fields': [{'default_value': None,

# 'is_key_field': True,

# 'is_notnull_constraint_field': False,

# 'name': 'ID',

# 'precision': -1,

# 'scale': -1,

# 'type_name': 'java.lang.Integer'},

# {'default_value': None,

# 'is_key_field': False,

# 'is_notnull_constraint_field': False,

# 'name': 'NAME',

# 'precision': 35,

# 'scale': -1,

# 'type_name': 'java.lang.String'},

# {'default_value': None,

# 'is_key_field': True,

# 'is_notnull_constraint_field': False,

# 'name': 'COUNTRYCODE',

# 'precision': 3,

# 'scale': -1,

# 'type_name': 'java.lang.String'},

# {'default_value': None,

# 'is_key_field': False,

# 'is_notnull_constraint_field': False,

# 'name': 'DISTRICT',

# 'precision': 20,

# 'scale': -1,

# 'type_name': 'java.lang.String'},

# {'default_value': None,

# 'is_key_field': False,

# 'is_notnull_constraint_field': False,

# 'name': 'POPULATION',

# 'precision': -1,

# 'scale': -1,

# 'type_name': 'java.lang.Integer'}],

# 'query_indexes': [],

# 'table_name': 'CITY',

# 'value_field_name': None,

# 'value_type_name': 'SQL_PUBLIC_CITY_081f37cc8ac72b10f08ab1273b744497'}]

print('-' * 20)

with city_cache.scan() as cursor:

for line in next(cursor):

pprint(obj_to_dict(line))

# {'COUNTRYCODE': 'USA',

# 'ID': 3793,

# 'type_name': 'SQL_PUBLIC_CITY_081f37cc8ac72b10f08ab1273b744497_KEY'}

# {'DISTRICT': 'New York',

# 'NAME': 'New York',

# 'POPULATION': 8008278,

# 'type_name': 'SQL_PUBLIC_CITY_081f37cc8ac72b10f08ab1273b744497'}

print('-' * 20)

with client.sql('SELECT _KEY, _VAL FROM CITY WHERE ID = ?', query_args=[1890]) as cursor:

for line in next(cursor):

pprint(obj_to_dict(line))

# {'COUNTRYCODE': 'CHN',

# 'ID': 1890,

# 'type_name': 'SQL_PUBLIC_CITY_081f37cc8ac72b10f08ab1273b744497_KEY'}

# {'DISTRICT': 'Shanghai',

# 'NAME': 'Shanghai',

# 'POPULATION': 9696300,

# 'type_name': 'SQL_PUBLIC_CITY_081f37cc8ac72b10f08ab1273b744497'}

# clean up

for table_name in TableNames:

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