def query_nested_struct_field(instance_id, database_id):
"""Query a table using nested field access on a STRUCT parameter. """
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
song_info_type = param_types.Struct([
param_types.StructField('SongName', param_types.STRING),
param_types.StructField(
'ArtistNames',
param_types.Array(
param_types.Struct([
param_types.StructField('FirstName', param_types.STRING),
param_types.StructField('LastName', param_types.STRING)
])))
])
song_info = ('Imagination', [('Elena', 'Campbell'), ('Hannah', 'Harris')])
with database.snapshot() as snapshot:
results = snapshot.execute_sql(
"SELECT SingerId, @song_info.SongName "
"FROM Singers WHERE "
"STRUCT<FirstName STRING, LastName STRING>"
"(FirstName, LastName) "
"IN UNNEST(@song_info.ArtistNames)",
params={'song_info': song_info},
param_types={'song_info': song_info_type})
for row in results:
print(u'SingerId: {} SongName: {}'.format(*row))
def query_with_array_of_struct(instance_id, database_id):
"""Query a table using an array of STRUCT parameters. """
# [START spanner_create_user_defined_struct]
name_type = param_types.Struct([
param_types.StructField('FirstName', param_types.STRING),
param_types.StructField('LastName', param_types.STRING)
])
# [END spanner_create_user_defined_struct]
# [START spanner_create_array_of_struct_with_data]
band_members = [("Elena", "Campbell"), ("Gabriel", "Wright"),
("Benjamin", "Martinez")]
# [END spanner_create_array_of_struct_with_data]
# [START spanner_query_data_with_array_of_struct]
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
with database.snapshot() as snapshot:
results = snapshot.execute_sql(
"SELECT SingerId FROM Singers WHERE "
"STRUCT<FirstName STRING, LastName STRING>"
"(FirstName, LastName) IN UNNEST(@names)",
params={'names': band_members},
param_types={'names': param_types.Array(name_type)})
for row in results:
print(u'SingerId: {}'.format(*row))
def test_it(self):
from google.cloud.spanner_v1.proto import type_pb2
from google.cloud.spanner_v1 import param_types
expected = type_pb2.Type(
code=type_pb2.ARRAY,
array_element_type=type_pb2.Type(code=type_pb2.INT64))
found = param_types.Array(param_types.INT64)
self.assertEqual(found, expected)
def test_it(self):
from google.cloud.spanner_v1 import Type
from google.cloud.spanner_v1 import TypeCode
from google.cloud.spanner_v1 import param_types
expected = Type(code=TypeCode.ARRAY,
array_element_type=Type(code=TypeCode.INT64))
found = param_types.Array(param_types.INT64)
self.assertEqual(found, expected)
def query(instance, db):
spanner_client = spanner.Client()
instance = spanner_client.instance(instance)
db = instance.database(db)
print('Db: %s' % (db.name))
#now = datetime.now()
with db.snapshot(multi_use=True) as txn:
q = """\
SELECT
bso.id
FROM
bso@{FORCE_INDEX=BsoLastModified}
WHERE
bso.userid = @userid
AND bso.collection = @coll
AND bso.modified > @modified
AND bso.modified <= CURRENT_TIMESTAMP()
%s
ORDER BY
bso.modified DESC,
bso.id ASC
""" % ('AND bso.ttl > @modified' if INCLUDE_TTL_IN_BSOLM else '')
print('QQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQ')
print(q)
params = dict(userid=USERID,
coll=COLL,
modified=datetime.strptime(MODIFIED, FORMAT))
ptypes = dict(userid=param_types.STRING,
coll=param_types.INT64,
modified=param_types.TIMESTAMP)
result = txn.execute_sql(
q,
params=params,
param_types=ptypes,
query_mode=enums.ExecuteSqlRequest.QueryMode.PROFILE)
rows = list(result)
print(result.stats)
print(len(rows))
ids = [row[0] for row in rows]
print('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')
print(repr([id.encode('utf-8') for id in ids][:200]))
print('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')
# XXX: do I need ORDER BY here?
# This index is ordered by modified DESC
# We either sort by:
# Sorting::Index -> sortindex (Python: + id desc)
# Sorting::Newest -> modified desc (Python: + id asc)
# Sorting::Oldest -> modified asc (Python: + id asc)
# Sorting::None
# only the Python is sorting by the additional id
# sort. syncstorage-rs inherited *not* doing this from the go
# version, which doesn't do it.
#
# Sort by index was only added recently:
# Date: Sun Jul 15 23:40:54 2018 -0700
# https://github.com/mozilla-services/server-syncstorage/commit/a0f8117
# https://bugzilla.mozilla.org/show_bug.cgi?id=1464008
#
# https://cloud.google.com/spanner/docs/query-syntax
# "UNNEST destroys the order of elements in the input ARRAY. Use
# the optional WITH OFFSET clause to return a second column with
# the array element indexes (see below)."
q = """\
SELECT
bso.userid,
bso.collection,
bso.id,
bso.sortindex,
bso.modified,
bso.payload,
bso.ttl
FROM
bso
WHERE
bso.userid = @userid
AND bso.collection = @coll
AND bso.id in UNNEST(@ids)
%s
ORDER BY
bso.modified DESC,
bso.id ASC
""" % ('' if INCLUDE_TTL_IN_BSOLM else 'AND bso.ttl > @modified')
params = dict(userid=USERID,
coll=COLL,
ids=ids,
modified=datetime.strptime(MODIFIED, FORMAT))
ptypes = dict(userid=param_types.STRING,
coll=param_types.INT64,
ids=param_types.Array(param_types.STRING),
modified=param_types.TIMESTAMP)
result = txn.execute_sql(
q,
params=params,
param_types=ptypes,
query_mode=enums.ExecuteSqlRequest.QueryMode.PROFILE)
rows = list(result)
print(result.stats)
print(len(rows))
COMPARE_TO_TABLE_SCAN = True
if not COMPARE_TO_TABLE_SCAN:
return
print('BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB')
q = """\
SELECT
bso.userid,
bso.collection,
bso.id,
bso.sortindex,
bso.modified,
bso.payload,
bso.ttl
FROM
bso%s
WHERE
bso.userid = @userid
AND bso.collection = @coll
AND bso.modified > @modified
AND bso.modified <= CURRENT_TIMESTAMP()
AND bso.ttl > @modified
ORDER BY
bso.modified DESC,
bso.id ASC
""" % ('@{FORCE_INDEX=BsoLastModified}' if _FORCE_BSOLM_INDEX else '')
params = dict(userid=USERID,
coll=COLL,
modified=datetime.strptime(MODIFIED, FORMAT))
ptypes = dict(userid=param_types.STRING,
coll=param_types.INT64,
modified=param_types.TIMESTAMP)
result = txn.execute_sql(
q,
params=params,
param_types=ptypes,
query_mode=enums.ExecuteSqlRequest.QueryMode.PROFILE)
rows = list(result)
print(result.stats)
print(len(rows))