def test_w_timestamp_w_nanos(self): from google.protobuf.struct_pb2 import Value from google.cloud._helpers import UTC from google.cloud.spanner._helpers import TimestampWithNanoseconds when = TimestampWithNanoseconds(2016, 12, 20, 21, 13, 47, nanosecond=123456789, tzinfo=UTC) value_pb = self._callFUT(when) self.assertIsInstance(value_pb, Value) self.assertEqual(value_pb.string_value, when.rfc3339())
def test_w_timestamp_w_nanos(self): from google.protobuf.struct_pb2 import Value from google.cloud.proto.spanner.v1.type_pb2 import Type, TIMESTAMP from google.cloud._helpers import UTC, _datetime_to_rfc3339 from google.cloud.spanner._helpers import TimestampWithNanoseconds VALUE = TimestampWithNanoseconds(2016, 12, 20, 21, 13, 47, nanosecond=123456789, tzinfo=UTC) field_type = Type(code=TIMESTAMP) value_pb = Value(string_value=_datetime_to_rfc3339(VALUE)) parsed = self._callFUT(value_pb, field_type) self.assertIsInstance(parsed, TimestampWithNanoseconds) self.assertEqual(parsed, VALUE)
class TestSessionAPI(unittest.TestCase, _TestData): ALL_TYPES_TABLE = 'all_types' ALL_TYPES_COLUMNS = ( 'list_goes_on', 'are_you_sure', 'raw_data', 'hwhen', 'approx_value', 'eye_d', 'description', 'exactly_hwhen', ) SOME_DATE = datetime.date(2011, 1, 17) SOME_TIME = datetime.datetime(1989, 1, 17, 17, 59, 12, 345612) NANO_TIME = TimestampWithNanoseconds(1995, 8, 31, nanosecond=987654321) OTHER_NAN, = struct.unpack('<d', b'\x01\x00\x01\x00\x00\x00\xf8\xff') BYTES_1 = b'Ymlu' BYTES_2 = b'Ym9vdHM=' ALL_TYPES_ROWDATA = ( ([1], True, BYTES_1, SOME_DATE, 0.0, 19, u'dog', SOME_TIME), ([5, 10], True, BYTES_1, None, 1.25, 99, u'cat', None), ([], False, BYTES_2, None, float('inf'), 107, u'frog', None), ([], False, None, None, float('-inf'), 207, None, None), ([], False, None, None, float('nan'), 1207, None, None), ([], False, None, None, OTHER_NAN, 2000, None, NANO_TIME), ) @classmethod def setUpClass(cls): pool = BurstyPool() cls._db = Config.INSTANCE.database(DATABASE_ID, ddl_statements=DDL_STATEMENTS, pool=pool) operation = cls._db.create() operation.result(30) # raises on failure / timeout. @classmethod def tearDownClass(cls): cls._db.drop() def setUp(self): self.to_delete = [] def tearDown(self): for doomed in self.to_delete: doomed.delete() def test_session_crud(self): retry_true = RetryResult(operator.truth) retry_false = RetryResult(operator.not_) session = self._db.session() self.assertFalse(session.exists()) session.create() retry_true(session.exists)() session.delete() retry_false(session.exists)() def test_batch_insert_then_read(self): retry = RetryInstanceState(_has_all_ddl) retry(self._db.reload)() session = self._db.session() session.create() self.to_delete.append(session) batch = session.batch() batch.delete(self.TABLE, self.ALL) batch.insert(self.TABLE, self.COLUMNS, self.ROW_DATA) batch.commit() snapshot = session.snapshot(read_timestamp=batch.committed) rows = list(snapshot.read(self.TABLE, self.COLUMNS, self.ALL)) self._check_row_data(rows) def test_batch_insert_then_read_all_datatypes(self): retry = RetryInstanceState(_has_all_ddl) retry(self._db.reload)() session = self._db.session() session.create() self.to_delete.append(session) with session.batch() as batch: batch.delete(self.ALL_TYPES_TABLE, self.ALL) batch.insert(self.ALL_TYPES_TABLE, self.ALL_TYPES_COLUMNS, self.ALL_TYPES_ROWDATA) snapshot = session.snapshot(read_timestamp=batch.committed) rows = list( snapshot.read(self.ALL_TYPES_TABLE, self.ALL_TYPES_COLUMNS, self.ALL)) self._check_row_data(rows, expected=self.ALL_TYPES_ROWDATA) def test_batch_insert_or_update_then_query(self): retry = RetryInstanceState(_has_all_ddl) retry(self._db.reload)() session = self._db.session() session.create() self.to_delete.append(session) with session.batch() as batch: batch.insert_or_update(self.TABLE, self.COLUMNS, self.ROW_DATA) snapshot = session.snapshot(read_timestamp=batch.committed) rows = list(snapshot.execute_sql(self.SQL)) self._check_row_data(rows) @RetryErrors(exception=GrpcRendezvous) def test_transaction_read_and_insert_then_rollback(self): retry = RetryInstanceState(_has_all_ddl) retry(self._db.reload)() session = self._db.session() session.create() self.to_delete.append(session) with session.batch() as batch: batch.delete(self.TABLE, self.ALL) transaction = session.transaction() transaction.begin() rows = list(transaction.read(self.TABLE, self.COLUMNS, self.ALL)) self.assertEqual(rows, []) transaction.insert(self.TABLE, self.COLUMNS, self.ROW_DATA) # Inserted rows can't be read until after commit. rows = list(transaction.read(self.TABLE, self.COLUMNS, self.ALL)) self.assertEqual(rows, []) transaction.rollback() rows = list(session.read(self.TABLE, self.COLUMNS, self.ALL)) self.assertEqual(rows, []) @RetryErrors(exception=GrpcRendezvous) def test_transaction_read_and_insert_or_update_then_commit(self): retry = RetryInstanceState(_has_all_ddl) retry(self._db.reload)() session = self._db.session() session.create() self.to_delete.append(session) with session.batch() as batch: batch.delete(self.TABLE, self.ALL) with session.transaction() as transaction: rows = list(transaction.read(self.TABLE, self.COLUMNS, self.ALL)) self.assertEqual(rows, []) transaction.insert_or_update(self.TABLE, self.COLUMNS, self.ROW_DATA) # Inserted rows can't be read until after commit. rows = list(transaction.read(self.TABLE, self.COLUMNS, self.ALL)) self.assertEqual(rows, []) rows = list(session.read(self.TABLE, self.COLUMNS, self.ALL)) self._check_row_data(rows) @staticmethod def _row_data(max_index): for index in range(max_index): yield [ index, 'First%09d' % (index, ), 'Last09%d' % (index), '*****@*****.**' % (index, ), ] def _set_up_table(self, row_count): retry = RetryInstanceState(_has_all_ddl) retry(self._db.reload)() session = self._db.session() session.create() self.to_delete.append(session) with session.transaction() as transaction: transaction.delete(self.TABLE, self.ALL) transaction.insert(self.TABLE, self.COLUMNS, self._row_data(row_count)) return session, transaction.committed def test_read_w_manual_consume(self): ROW_COUNT = 4000 session, committed = self._set_up_table(ROW_COUNT) snapshot = session.snapshot(read_timestamp=committed) streamed = snapshot.read(self.TABLE, self.COLUMNS, self.ALL) retrieved = 0 while True: try: streamed.consume_next() except StopIteration: break retrieved += len(streamed.rows) streamed.rows[:] = () self.assertEqual(retrieved, ROW_COUNT) self.assertEqual(streamed._current_row, []) self.assertEqual(streamed._pending_chunk, None) def test_execute_sql_w_manual_consume(self): ROW_COUNT = 4000 session, committed = self._set_up_table(ROW_COUNT) snapshot = session.snapshot(read_timestamp=committed) streamed = snapshot.execute_sql(self.SQL) retrieved = 0 while True: try: streamed.consume_next() except StopIteration: break retrieved += len(streamed.rows) streamed.rows[:] = () self.assertEqual(retrieved, ROW_COUNT) self.assertEqual(streamed._current_row, []) self.assertEqual(streamed._pending_chunk, None) def _check_sql_results(self, snapshot, sql, params, param_types, expected): if 'ORDER' not in sql: sql += ' ORDER BY eye_d' rows = list( snapshot.execute_sql(sql, params=params, param_types=param_types)) self._check_row_data(rows, expected=expected) def test_execute_sql_w_query_param(self): session = self._db.session() session.create() self.to_delete.append(session) with session.batch() as batch: batch.delete(self.ALL_TYPES_TABLE, self.ALL) batch.insert(self.ALL_TYPES_TABLE, self.ALL_TYPES_COLUMNS, self.ALL_TYPES_ROWDATA) snapshot = session.snapshot(read_timestamp=batch.committed) # Cannot equality-test array values. See below for a test w/ # array of IDs. self._check_sql_results( snapshot, sql='SELECT eye_d FROM all_types WHERE are_you_sure = @sure', params={'sure': True}, param_types={'sure': Type(code=BOOL)}, expected=[(19, ), (99, )], ) self._check_sql_results( snapshot, sql='SELECT eye_d FROM all_types WHERE raw_data = @bytes_1', params={'bytes_1': self.BYTES_1}, param_types={'bytes_1': Type(code=BYTES)}, expected=[(19, ), (99, )], ) self._check_sql_results( snapshot, sql='SELECT eye_d FROM all_types WHERE hwhen = @hwhen', params={'hwhen': self.SOME_DATE}, param_types={'hwhen': Type(code=DATE)}, expected=[(19, )], ) self._check_sql_results( snapshot, sql=('SELECT eye_d FROM all_types WHERE approx_value >= @lower' ' AND approx_value < @upper '), params={ 'lower': 0.0, 'upper': 1.0 }, param_types={ 'lower': Type(code=FLOAT64), 'upper': Type(code=FLOAT64) }, expected=[(19, )], ) # Find -inf self._check_sql_results( snapshot, sql='SELECT eye_d FROM all_types WHERE approx_value = @pos_inf', params={'pos_inf': float('+inf')}, param_types={'pos_inf': Type(code=FLOAT64)}, expected=[(107, )], ) # Find +inf self._check_sql_results( snapshot, sql='SELECT eye_d FROM all_types WHERE approx_value = @neg_inf', params={'neg_inf': float('-inf')}, param_types={'neg_inf': Type(code=FLOAT64)}, expected=[(207, )], ) self._check_sql_results( snapshot, sql='SELECT description FROM all_types WHERE eye_d = @my_id', params={'my_id': 19}, param_types={'my_id': Type(code=INT64)}, expected=[(u'dog', )], ) self._check_sql_results( snapshot, sql='SELECT eye_d FROM all_types WHERE description = @description', params={'description': u'dog'}, param_types={'description': Type(code=STRING)}, expected=[(19, )], ) # NaNs cannot be searched for by equality. self._check_sql_results( snapshot, sql='SELECT eye_d FROM all_types WHERE exactly_hwhen = @hwhen', params={'hwhen': self.SOME_TIME}, param_types={'hwhen': Type(code=TIMESTAMP)}, expected=[(19, )], ) array_type = Type(code=ARRAY, array_element_type=Type(code=INT64)) self._check_sql_results( snapshot, sql=('SELECT description FROM all_types ' 'WHERE eye_d in UNNEST(@my_list)'), params={'my_list': [19, 99]}, param_types={'my_list': array_type}, expected=[(u'dog', ), (u'cat', )], )
class TestSessionAPI(unittest.TestCase, _TestData): ALL_TYPES_TABLE = 'all_types' ALL_TYPES_COLUMNS = ( 'list_goes_on', 'are_you_sure', 'raw_data', 'hwhen', 'approx_value', 'eye_d', 'description', 'exactly_hwhen', ) SOME_DATE = datetime.date(2011, 1, 17) SOME_TIME = datetime.datetime(1989, 1, 17, 17, 59, 12, 345612) NANO_TIME = TimestampWithNanoseconds(1995, 8, 31, nanosecond=987654321) OTHER_NAN, = struct.unpack('<d', b'\x01\x00\x01\x00\x00\x00\xf8\xff') BYTES_1 = b'Ymlu' BYTES_2 = b'Ym9vdHM=' ALL_TYPES_ROWDATA = ( ([], False, None, None, 0.0, None, None, None), ([1], True, BYTES_1, SOME_DATE, 0.0, 19, u'dog', SOME_TIME), ([5, 10], True, BYTES_1, None, 1.25, 99, u'cat', None), ([], False, BYTES_2, None, float('inf'), 107, u'frog', None), ([3, None, 9], False, None, None, float('-inf'), 207, None, None), ([], False, None, None, float('nan'), 1207, None, None), ([], False, None, None, OTHER_NAN, 2000, None, NANO_TIME), ) @classmethod def setUpClass(cls): pool = BurstyPool() cls._db = Config.INSTANCE.database( DATABASE_ID, ddl_statements=DDL_STATEMENTS, pool=pool) operation = cls._db.create() operation.result(30) # raises on failure / timeout. @classmethod def tearDownClass(cls): cls._db.drop() def setUp(self): self.to_delete = [] def tearDown(self): for doomed in self.to_delete: doomed.delete() def test_session_crud(self): retry_true = RetryResult(operator.truth) retry_false = RetryResult(operator.not_) session = self._db.session() self.assertFalse(session.exists()) session.create() retry_true(session.exists)() session.delete() retry_false(session.exists)() def test_batch_insert_then_read(self): retry = RetryInstanceState(_has_all_ddl) retry(self._db.reload)() session = self._db.session() session.create() self.to_delete.append(session) batch = session.batch() batch.delete(self.TABLE, self.ALL) batch.insert(self.TABLE, self.COLUMNS, self.ROW_DATA) batch.commit() snapshot = session.snapshot(read_timestamp=batch.committed) rows = list(snapshot.read(self.TABLE, self.COLUMNS, self.ALL)) self._check_row_data(rows) def test_batch_insert_then_read_all_datatypes(self): retry = RetryInstanceState(_has_all_ddl) retry(self._db.reload)() session = self._db.session() session.create() self.to_delete.append(session) with session.batch() as batch: batch.delete(self.ALL_TYPES_TABLE, self.ALL) batch.insert( self.ALL_TYPES_TABLE, self.ALL_TYPES_COLUMNS, self.ALL_TYPES_ROWDATA) snapshot = session.snapshot(read_timestamp=batch.committed) rows = list(snapshot.read( self.ALL_TYPES_TABLE, self.ALL_TYPES_COLUMNS, self.ALL)) self._check_row_data(rows, expected=self.ALL_TYPES_ROWDATA) def test_batch_insert_or_update_then_query(self): retry = RetryInstanceState(_has_all_ddl) retry(self._db.reload)() session = self._db.session() session.create() self.to_delete.append(session) with session.batch() as batch: batch.insert_or_update(self.TABLE, self.COLUMNS, self.ROW_DATA) snapshot = session.snapshot(read_timestamp=batch.committed) rows = list(snapshot.execute_sql(self.SQL)) self._check_row_data(rows) @RetryErrors(exception=GrpcRendezvous) def test_transaction_read_and_insert_then_rollback(self): retry = RetryInstanceState(_has_all_ddl) retry(self._db.reload)() session = self._db.session() session.create() self.to_delete.append(session) with session.batch() as batch: batch.delete(self.TABLE, self.ALL) transaction = session.transaction() transaction.begin() rows = list(transaction.read(self.TABLE, self.COLUMNS, self.ALL)) self.assertEqual(rows, []) transaction.insert(self.TABLE, self.COLUMNS, self.ROW_DATA) # Inserted rows can't be read until after commit. rows = list(transaction.read(self.TABLE, self.COLUMNS, self.ALL)) self.assertEqual(rows, []) transaction.rollback() rows = list(session.read(self.TABLE, self.COLUMNS, self.ALL)) self.assertEqual(rows, []) def _transaction_read_then_raise(self, transaction): rows = list(transaction.read(self.TABLE, self.COLUMNS, self.ALL)) self.assertEqual(len(rows), 0) transaction.insert(self.TABLE, self.COLUMNS, self.ROW_DATA) raise CustomException() @RetryErrors(exception=GrpcRendezvous) def test_transaction_read_and_insert_then_execption(self): retry = RetryInstanceState(_has_all_ddl) retry(self._db.reload)() session = self._db.session() session.create() self.to_delete.append(session) with session.batch() as batch: batch.delete(self.TABLE, self.ALL) with self.assertRaises(CustomException): session.run_in_transaction(self._transaction_read_then_raise) # Transaction was rolled back. rows = list(session.read(self.TABLE, self.COLUMNS, self.ALL)) self.assertEqual(rows, []) @RetryErrors(exception=GrpcRendezvous) def test_transaction_read_and_insert_or_update_then_commit(self): retry = RetryInstanceState(_has_all_ddl) retry(self._db.reload)() session = self._db.session() session.create() self.to_delete.append(session) with session.batch() as batch: batch.delete(self.TABLE, self.ALL) with session.transaction() as transaction: rows = list(transaction.read(self.TABLE, self.COLUMNS, self.ALL)) self.assertEqual(rows, []) transaction.insert_or_update( self.TABLE, self.COLUMNS, self.ROW_DATA) # Inserted rows can't be read until after commit. rows = list(transaction.read(self.TABLE, self.COLUMNS, self.ALL)) self.assertEqual(rows, []) rows = list(session.read(self.TABLE, self.COLUMNS, self.ALL)) self._check_row_data(rows) def _transaction_concurrency_helper(self, unit_of_work, pkey): INITIAL_VALUE = 123 NUM_THREADS = 3 # conforms to equivalent Java systest. retry = RetryInstanceState(_has_all_ddl) retry(self._db.reload)() session = self._db.session() session.create() self.to_delete.append(session) with session.batch() as batch: batch.insert_or_update( COUNTERS_TABLE, COUNTERS_COLUMNS, [[pkey, INITIAL_VALUE]]) # We don't want to run the threads' transactions in the current # session, which would fail. txn_sessions = [] for _ in range(NUM_THREADS): txn_session = self._db.session() txn_sessions.append(txn_session) txn_session.create() self.to_delete.append(txn_session) threads = [ threading.Thread( target=txn_session.run_in_transaction, args=(unit_of_work, pkey)) for txn_session in txn_sessions] for thread in threads: thread.start() for thread in threads: thread.join() keyset = KeySet(keys=[(pkey,)]) rows = list(session.read( COUNTERS_TABLE, COUNTERS_COLUMNS, keyset)) self.assertEqual(len(rows), 1) _, value = rows[0] self.assertEqual(value, INITIAL_VALUE + len(threads)) def _read_w_concurrent_update(self, transaction, pkey): keyset = KeySet(keys=[(pkey,)]) rows = list(transaction.read( COUNTERS_TABLE, COUNTERS_COLUMNS, keyset)) self.assertEqual(len(rows), 1) pkey, value = rows[0] transaction.update( COUNTERS_TABLE, COUNTERS_COLUMNS, [[pkey, value + 1]]) def test_transaction_read_w_concurrent_updates(self): PKEY = 'read_w_concurrent_updates' self._transaction_concurrency_helper( self._read_w_concurrent_update, PKEY) def _query_w_concurrent_update(self, transaction, pkey): SQL = 'SELECT * FROM counters WHERE name = @name' rows = list(transaction.execute_sql( SQL, params={'name': pkey}, param_types={'name': Type(code=STRING)}, )) self.assertEqual(len(rows), 1) pkey, value = rows[0] transaction.update( COUNTERS_TABLE, COUNTERS_COLUMNS, [[pkey, value + 1]]) def test_transaction_query_w_concurrent_updates(self): PKEY = 'query_w_concurrent_updates' self._transaction_concurrency_helper( self._query_w_concurrent_update, PKEY) def test_transaction_read_w_abort(self): retry = RetryInstanceState(_has_all_ddl) retry(self._db.reload)() session = self._db.session() session.create() trigger = _ReadAbortTrigger() with session.batch() as batch: batch.delete(COUNTERS_TABLE, self.ALL) batch.insert( COUNTERS_TABLE, COUNTERS_COLUMNS, [[trigger.KEY1, 0], [trigger.KEY2, 0]]) provoker = threading.Thread( target=trigger.provoke_abort, args=(self._db,)) handler = threading.Thread( target=trigger.handle_abort, args=(self._db,)) provoker.start() trigger.provoker_started.wait() handler.start() trigger.handler_done.wait() provoker.join() handler.join() rows = list(session.read(COUNTERS_TABLE, COUNTERS_COLUMNS, self.ALL)) self._check_row_data( rows, expected=[[trigger.KEY1, 1], [trigger.KEY2, 1]]) @staticmethod def _row_data(max_index): for index in range(max_index): yield [ index, 'First%09d' % (index,), 'Last%09d' % (max_index - index), '*****@*****.**' % (index,), ] def _set_up_table(self, row_count, db=None): if db is None: db = self._db retry = RetryInstanceState(_has_all_ddl) retry(db.reload)() session = db.session() session.create() self.to_delete.append(session) def _unit_of_work(transaction, test): transaction.delete(test.TABLE, test.ALL) transaction.insert( test.TABLE, test.COLUMNS, test._row_data(row_count)) committed = session.run_in_transaction(_unit_of_work, test=self) return session, committed def test_snapshot_read_w_various_staleness(self): from datetime import datetime from google.cloud._helpers import UTC ROW_COUNT = 400 session, committed = self._set_up_table(ROW_COUNT) all_data_rows = list(self._row_data(ROW_COUNT)) before_reads = datetime.utcnow().replace(tzinfo=UTC) # Test w/ read timestamp read_tx = session.snapshot(read_timestamp=committed) rows = list(read_tx.read(self.TABLE, self.COLUMNS, self.ALL)) self._check_row_data(rows, all_data_rows) # Test w/ min read timestamp min_read_ts = session.snapshot(min_read_timestamp=committed) rows = list(min_read_ts.read(self.TABLE, self.COLUMNS, self.ALL)) self._check_row_data(rows, all_data_rows) staleness = datetime.utcnow().replace(tzinfo=UTC) - before_reads # Test w/ max staleness max_staleness = session.snapshot(max_staleness=staleness) rows = list(max_staleness.read(self.TABLE, self.COLUMNS, self.ALL)) self._check_row_data(rows, all_data_rows) # Test w/ exact staleness exact_staleness = session.snapshot(exact_staleness=staleness) rows = list(exact_staleness.read(self.TABLE, self.COLUMNS, self.ALL)) self._check_row_data(rows, all_data_rows) # Test w/ strong strong = session.snapshot() rows = list(strong.read(self.TABLE, self.COLUMNS, self.ALL)) self._check_row_data(rows, all_data_rows) def test_multiuse_snapshot_read_isolation_strong(self): ROW_COUNT = 40 session, committed = self._set_up_table(ROW_COUNT) all_data_rows = list(self._row_data(ROW_COUNT)) strong = session.snapshot(multi_use=True) before = list(strong.read(self.TABLE, self.COLUMNS, self.ALL)) self._check_row_data(before, all_data_rows) with self._db.batch() as batch: batch.delete(self.TABLE, self.ALL) after = list(strong.read(self.TABLE, self.COLUMNS, self.ALL)) self._check_row_data(after, all_data_rows) def test_multiuse_snapshot_read_isolation_read_timestamp(self): ROW_COUNT = 40 session, committed = self._set_up_table(ROW_COUNT) all_data_rows = list(self._row_data(ROW_COUNT)) read_ts = session.snapshot(read_timestamp=committed, multi_use=True) before = list(read_ts.read(self.TABLE, self.COLUMNS, self.ALL)) self._check_row_data(before, all_data_rows) with self._db.batch() as batch: batch.delete(self.TABLE, self.ALL) after = list(read_ts.read(self.TABLE, self.COLUMNS, self.ALL)) self._check_row_data(after, all_data_rows) def test_multiuse_snapshot_read_isolation_exact_staleness(self): ROW_COUNT = 40 session, committed = self._set_up_table(ROW_COUNT) all_data_rows = list(self._row_data(ROW_COUNT)) time.sleep(1) delta = datetime.timedelta(microseconds=1000) exact = session.snapshot(exact_staleness=delta, multi_use=True) before = list(exact.read(self.TABLE, self.COLUMNS, self.ALL)) self._check_row_data(before, all_data_rows) with self._db.batch() as batch: batch.delete(self.TABLE, self.ALL) after = list(exact.read(self.TABLE, self.COLUMNS, self.ALL)) self._check_row_data(after, all_data_rows) def test_read_w_manual_consume(self): ROW_COUNT = 4000 session, committed = self._set_up_table(ROW_COUNT) snapshot = session.snapshot(read_timestamp=committed) streamed = snapshot.read(self.TABLE, self.COLUMNS, self.ALL) retrieved = 0 while True: try: streamed.consume_next() except StopIteration: break retrieved += len(streamed.rows) streamed.rows[:] = () self.assertEqual(retrieved, ROW_COUNT) self.assertEqual(streamed._current_row, []) self.assertEqual(streamed._pending_chunk, None) def test_read_w_index(self): ROW_COUNT = 2000 # Indexed reads cannot return non-indexed columns MY_COLUMNS = self.COLUMNS[0], self.COLUMNS[2] EXTRA_DDL = [ 'CREATE INDEX contacts_by_last_name ON contacts(last_name)', ] pool = BurstyPool() temp_db = Config.INSTANCE.database( 'test_read_w_index', ddl_statements=DDL_STATEMENTS + EXTRA_DDL, pool=pool) operation = temp_db.create() self.to_delete.append(_DatabaseDropper(temp_db)) # We want to make sure the operation completes. operation.result(30) # raises on failure / timeout. session, committed = self._set_up_table(ROW_COUNT, db=temp_db) snapshot = session.snapshot(read_timestamp=committed) rows = list(snapshot.read( self.TABLE, MY_COLUMNS, self.ALL, index='contacts_by_last_name')) expected = list(reversed( [(row[0], row[2]) for row in self._row_data(ROW_COUNT)])) self._check_row_data(rows, expected) def test_read_w_single_key(self): ROW_COUNT = 40 session, committed = self._set_up_table(ROW_COUNT) snapshot = session.snapshot(read_timestamp=committed) rows = list(snapshot.read( self.TABLE, self.COLUMNS, KeySet(keys=[(0,)]))) all_data_rows = list(self._row_data(ROW_COUNT)) expected = [all_data_rows[0]] self._check_row_data(rows, expected) def test_read_w_multiple_keys(self): ROW_COUNT = 40 indices = [0, 5, 17] session, committed = self._set_up_table(ROW_COUNT) snapshot = session.snapshot(read_timestamp=committed) rows = list(snapshot.read( self.TABLE, self.COLUMNS, KeySet(keys=[(index,) for index in indices]))) all_data_rows = list(self._row_data(ROW_COUNT)) expected = [row for row in all_data_rows if row[0] in indices] self._check_row_data(rows, expected) def test_read_w_limit(self): ROW_COUNT = 4000 LIMIT = 100 session, committed = self._set_up_table(ROW_COUNT) snapshot = session.snapshot(read_timestamp=committed) rows = list(snapshot.read( self.TABLE, self.COLUMNS, self.ALL, limit=LIMIT)) all_data_rows = list(self._row_data(ROW_COUNT)) expected = all_data_rows[:LIMIT] self._check_row_data(rows, expected) def test_read_w_ranges(self): ROW_COUNT = 4000 START = 1000 END = 2000 session, committed = self._set_up_table(ROW_COUNT) snapshot = session.snapshot(read_timestamp=committed, multi_use=True) all_data_rows = list(self._row_data(ROW_COUNT)) closed_closed = KeyRange(start_closed=[START], end_closed=[END]) keyset = KeySet(ranges=(closed_closed,)) rows = list(snapshot.read( self.TABLE, self.COLUMNS, keyset)) expected = all_data_rows[START:END+1] self._check_row_data(rows, expected) closed_open = KeyRange(start_closed=[START], end_open=[END]) keyset = KeySet(ranges=(closed_open,)) rows = list(snapshot.read( self.TABLE, self.COLUMNS, keyset)) expected = all_data_rows[START:END] self._check_row_data(rows, expected) open_open = KeyRange(start_open=[START], end_open=[END]) keyset = KeySet(ranges=(open_open,)) rows = list(snapshot.read( self.TABLE, self.COLUMNS, keyset)) expected = all_data_rows[START+1:END] self._check_row_data(rows, expected) open_closed = KeyRange(start_open=[START], end_closed=[END]) keyset = KeySet(ranges=(open_closed,)) rows = list(snapshot.read( self.TABLE, self.COLUMNS, keyset)) expected = all_data_rows[START+1:END+1] self._check_row_data(rows, expected) def test_execute_sql_w_manual_consume(self): ROW_COUNT = 4000 session, committed = self._set_up_table(ROW_COUNT) snapshot = session.snapshot(read_timestamp=committed) streamed = snapshot.execute_sql(self.SQL) retrieved = 0 while True: try: streamed.consume_next() except StopIteration: break retrieved += len(streamed.rows) streamed.rows[:] = () self.assertEqual(retrieved, ROW_COUNT) self.assertEqual(streamed._current_row, []) self.assertEqual(streamed._pending_chunk, None) def _check_sql_results(self, snapshot, sql, params, param_types, expected): if 'ORDER' not in sql: sql += ' ORDER BY eye_d' rows = list(snapshot.execute_sql( sql, params=params, param_types=param_types)) self._check_row_data(rows, expected=expected) def test_multiuse_snapshot_execute_sql_isolation_strong(self): ROW_COUNT = 40 SQL = 'SELECT * FROM {}'.format(self.TABLE) session, committed = self._set_up_table(ROW_COUNT) all_data_rows = list(self._row_data(ROW_COUNT)) strong = session.snapshot(multi_use=True) before = list(strong.execute_sql(SQL)) self._check_row_data(before, all_data_rows) with self._db.batch() as batch: batch.delete(self.TABLE, self.ALL) after = list(strong.execute_sql(SQL)) self._check_row_data(after, all_data_rows) def test_execute_sql_returning_array_of_struct(self): SQL = ( "SELECT ARRAY(SELECT AS STRUCT C1, C2 " "FROM (SELECT 'a' AS C1, 1 AS C2 " "UNION ALL SELECT 'b' AS C1, 2 AS C2) " "ORDER BY C1 ASC)" ) session = self._db.session() session.create() self.to_delete.append(session) snapshot = session.snapshot() self._check_sql_results( snapshot, sql=SQL, params=None, param_types=None, expected=[ [[['a', 1], ['b', 2]]], ]) def test_execute_sql_w_query_param(self): session = self._db.session() session.create() self.to_delete.append(session) with session.batch() as batch: batch.delete(self.ALL_TYPES_TABLE, self.ALL) batch.insert( self.ALL_TYPES_TABLE, self.ALL_TYPES_COLUMNS, self.ALL_TYPES_ROWDATA) snapshot = session.snapshot( read_timestamp=batch.committed, multi_use=True) # Cannot equality-test array values. See below for a test w/ # array of IDs. self._check_sql_results( snapshot, sql='SELECT eye_d FROM all_types WHERE are_you_sure = @sure', params={'sure': True}, param_types={'sure': Type(code=BOOL)}, expected=[(19,), (99,)], ) self._check_sql_results( snapshot, sql='SELECT eye_d FROM all_types WHERE raw_data = @bytes_1', params={'bytes_1': self.BYTES_1}, param_types={'bytes_1': Type(code=BYTES)}, expected=[(19,), (99,)], ) self._check_sql_results( snapshot, sql='SELECT eye_d FROM all_types WHERE hwhen = @hwhen', params={'hwhen': self.SOME_DATE}, param_types={'hwhen': Type(code=DATE)}, expected=[(19,)], ) self._check_sql_results( snapshot, sql=('SELECT eye_d FROM all_types WHERE approx_value >= @lower' ' AND approx_value < @upper '), params={'lower': 0.0, 'upper': 1.0}, param_types={ 'lower': Type(code=FLOAT64), 'upper': Type(code=FLOAT64)}, expected=[(None,), (19,)], ) # Find -inf self._check_sql_results( snapshot, sql='SELECT eye_d FROM all_types WHERE approx_value = @pos_inf', params={'pos_inf': float('+inf')}, param_types={'pos_inf': Type(code=FLOAT64)}, expected=[(107,)], ) # Find +inf self._check_sql_results( snapshot, sql='SELECT eye_d FROM all_types WHERE approx_value = @neg_inf', params={'neg_inf': float('-inf')}, param_types={'neg_inf': Type(code=FLOAT64)}, expected=[(207,)], ) self._check_sql_results( snapshot, sql='SELECT description FROM all_types WHERE eye_d = @my_id', params={'my_id': 19}, param_types={'my_id': Type(code=INT64)}, expected=[(u'dog',)], ) self._check_sql_results( snapshot, sql='SELECT description FROM all_types WHERE eye_d = @my_id', params={'my_id': None}, param_types={'my_id': Type(code=INT64)}, expected=[], ) self._check_sql_results( snapshot, sql='SELECT eye_d FROM all_types WHERE description = @description', params={'description': u'dog'}, param_types={'description': Type(code=STRING)}, expected=[(19,)], ) # NaNs cannot be searched for by equality. self._check_sql_results( snapshot, sql='SELECT eye_d FROM all_types WHERE exactly_hwhen = @hwhen', params={'hwhen': self.SOME_TIME}, param_types={'hwhen': Type(code=TIMESTAMP)}, expected=[(19,)], ) array_type = Type(code=ARRAY, array_element_type=Type(code=INT64)) self._check_sql_results( snapshot, sql=('SELECT description FROM all_types ' 'WHERE eye_d in UNNEST(@my_list)'), params={'my_list': [19, 99]}, param_types={'my_list': array_type}, expected=[(u'dog',), (u'cat',)], )