def testWhereRewriteWithRelated(self):
"""Test WHERE when pseudonym value exists in two different tables."""
schema = test_util.GetCarsSchema()
# add 'related' field just for this test
for field in schema:
if field['name'] == 'Make':
field['related'] = 'cars_name'
break
# this value determined by running the test, not by manual calc
ciphertext = 'sspWKAH/NKuUyX8ji1mmSw=='
# test 1, use table_id
table_id = _TABLE_ID
master_key = test_util.GetMasterKey()
as_clause = query_lib._AsClause({})
stack = [
util.FieldToken('Make'),
util.StringLiteralToken('"Hello"'),
util.OperatorToken('==', 2)
]
where_clause_1 = query_lib._WhereClause(stack,
as_clause=as_clause,
schema=schema,
nsquare=_TEST_NSQUARE,
master_key=master_key,
table_id=table_id)
rewritten_sql_1 = where_clause_1.Rewrite()
self.assertEqual(
rewritten_sql_1,
'WHERE (%sMake == "%s")' % (util.PSEUDONYM_PREFIX, ciphertext))
# test 2, change table_id, query should be same as test #1
table_id = _TABLE_ID + '_other'
master_key = test_util.GetMasterKey()
as_clause = query_lib._AsClause({})
stack = [
util.FieldToken('Make'),
util.StringLiteralToken('"Hello"'),
util.OperatorToken('==', 2)
]
where_clause_2 = query_lib._WhereClause(stack,
as_clause=as_clause,
schema=schema,
nsquare=_TEST_NSQUARE,
master_key=master_key,
table_id=table_id)
rewritten_sql_2 = where_clause_2.Rewrite()
self.assertEqual(
rewritten_sql_2,
'WHERE (%sMake == "%s")' % (util.PSEUDONYM_PREFIX, ciphertext))
# verify different tables were used
self.assertNotEqual(where_clause_1.table_id, where_clause_2.table_id)
# and verify that same WHERE query="literal" was generated
self.assertEqual(rewritten_sql_1, rewritten_sql_2)
def testGroupByRewrite(self):
test_schema = test_util.GetCarsSchema()
as_clause = query_lib._AsClause({})
within_clause = query_lib._WithinClause({})
select_clause = query_lib._SelectClause(
[["1"]], as_clause=as_clause, within_clause=within_clause, schema=test_schema, nsquare=_TEST_NSQUARE
)
select_clause.Rewrite()
fields = [util.FieldToken("Price")]
clause = query_lib._GroupByClause(
fields, schema=test_schema, nsquare=_TEST_NSQUARE, select_clause=select_clause
)
self.assertRaises(bigquery_client.BigqueryInvalidQueryError, clause.Rewrite)
fields = [util.FieldToken("Invoice_Price"), util.FieldToken("Make")]
clause = query_lib._GroupByClause(
fields, schema=test_schema, nsquare=_TEST_NSQUARE, select_clause=select_clause
)
self.assertRaises(bigquery_client.BigqueryInvalidQueryError, clause.Rewrite)
fields = [util.FieldToken("Make")]
clause = query_lib._GroupByClause(
fields, schema=test_schema, nsquare=_TEST_NSQUARE, select_clause=select_clause
)
self.assertEqual(clause.Rewrite(), "GROUP BY %sMake" % util.PSEUDONYM_PREFIX)
fields = [util.FieldToken("Year")]
clause = query_lib._GroupByClause(
fields, schema=test_schema, nsquare=_TEST_NSQUARE, select_clause=select_clause
)
self.assertEqual(clause.Rewrite(), "GROUP BY Year")
fields = []
clause = query_lib._GroupByClause(
fields, schema=test_schema, nsquare=_TEST_NSQUARE, select_clause=select_clause
)
self.assertEqual(clause.Rewrite(), "")
def testHavingRewrite(self):
schema = test_util.GetCarsSchema()
master_key = test_util.GetMasterKey()
as_clause = query_lib._AsClause({})
stack = [util.FieldToken("SUM(Year)"), 1, util.OperatorToken("<", 2)]
having_clause = query_lib._HavingClause(
stack, as_clause=as_clause, schema=schema, nsquare=_TEST_NSQUARE, master_key=master_key, table_id=_TABLE_ID
)
self.assertEqual(having_clause.Rewrite(), "HAVING (SUM(Year) < 1)")
stack = [
1000,
util.AggregationQueryToken(
"TO_BASE64(STRING(PAILLIER_SUM(FROM_BASE64(" + util.HOMOMORPHIC_INT_PREFIX + "Invoice_Price), '0')))"
),
util.OperatorToken("==", 2),
]
having_clause = query_lib._HavingClause(
stack, as_clause=as_clause, schema=schema, nsquare=_TEST_NSQUARE, master_key=master_key, table_id=_TABLE_ID
)
self.assertRaises(bigquery_client.BigqueryInvalidQueryError, having_clause.Rewrite)
stack = [
util.FieldToken("GROUP_CONCAT(" + util.PSEUDONYM_PREFIX + "Model)"),
util.BuiltInFunctionToken("len"),
5,
util.OperatorToken(">", 2),
]
having_clause = query_lib._HavingClause(
stack, as_clause=as_clause, schema=schema, nsquare=_TEST_NSQUARE, master_key=master_key, table_id=_TABLE_ID
)
self.assertRaises(bigquery_client.BigqueryInvalidQueryError, having_clause.Rewrite)
stack = []
having_clause = query_lib._HavingClause(
stack, as_clause=as_clause, schema=schema, nsquare=_TEST_NSQUARE, master_key=master_key, table_id=_TABLE_ID
)
self.assertEqual(having_clause.Rewrite(), "")
def testAsConstructColumnNames(self):
alias = {0: 'a'}
columns = [[util.FieldToken('b')], [1, 2, util.OperatorToken('+', 2)]]
as_clause = query_lib._AsClause(alias)
self.assertEqual(as_clause.ConstructColumnNames(columns),
[{
'name': 'a'
}, {
'name': '(1 + 2)'
}])
def testHavingRewrite(self):
schema = test_util.GetCarsSchema()
master_key = test_util.GetMasterKey()
as_clause = query_lib._AsClause({})
stack = [util.FieldToken('SUM(Year)'), 1, util.OperatorToken('<', 2)]
having_clause = query_lib._HavingClause(stack,
as_clause=as_clause,
schema=schema,
nsquare=_TEST_NSQUARE,
master_key=master_key,
table_id=_TABLE_ID)
self.assertEqual(having_clause.Rewrite(), 'HAVING (SUM(Year) < 1)')
stack = [
1000,
util.AggregationQueryToken(
'TO_BASE64(BYTES(PAILLIER_SUM(FROM_BASE64(' +
util.HOMOMORPHIC_INT_PREFIX + 'Invoice_Price), \'0\')))'),
util.OperatorToken('==', 2)
]
having_clause = query_lib._HavingClause(stack,
as_clause=as_clause,
schema=schema,
nsquare=_TEST_NSQUARE,
master_key=master_key,
table_id=_TABLE_ID)
self.assertRaises(bigquery_client.BigqueryInvalidQueryError,
having_clause.Rewrite)
stack = [
util.FieldToken('GROUP_CONCAT(' + util.PSEUDONYM_PREFIX +
'Model)'),
util.BuiltInFunctionToken('len'), 5,
util.OperatorToken('>', 2)
]
having_clause = query_lib._HavingClause(stack,
as_clause=as_clause,
schema=schema,
nsquare=_TEST_NSQUARE,
master_key=master_key,
table_id=_TABLE_ID)
self.assertRaises(bigquery_client.BigqueryInvalidQueryError,
having_clause.Rewrite)
stack = []
having_clause = query_lib._HavingClause(stack,
as_clause=as_clause,
schema=schema,
nsquare=_TEST_NSQUARE,
master_key=master_key,
table_id=_TABLE_ID)
self.assertEqual(having_clause.Rewrite(), '')
def testGroupByRewrite(self):
test_schema = test_util.GetCarsSchema()
as_clause = query_lib._AsClause({})
within_clause = query_lib._WithinClause({})
select_clause = query_lib._SelectClause([['1']],
as_clause=as_clause,
within_clause=within_clause,
schema=test_schema,
nsquare=_TEST_NSQUARE)
select_clause.Rewrite()
fields = [util.FieldToken('Price')]
clause = query_lib._GroupByClause(fields,
schema=test_schema,
nsquare=_TEST_NSQUARE,
select_clause=select_clause)
self.assertRaises(bigquery_client.BigqueryInvalidQueryError,
clause.Rewrite)
fields = [util.FieldToken('Invoice_Price'), util.FieldToken('Make')]
clause = query_lib._GroupByClause(fields,
schema=test_schema,
nsquare=_TEST_NSQUARE,
select_clause=select_clause)
self.assertRaises(bigquery_client.BigqueryInvalidQueryError,
clause.Rewrite)
fields = [util.FieldToken('Make')]
clause = query_lib._GroupByClause(fields,
schema=test_schema,
nsquare=_TEST_NSQUARE,
select_clause=select_clause)
self.assertEqual(clause.Rewrite(),
'GROUP BY %sMake' % util.PSEUDONYM_PREFIX)
fields = [util.FieldToken('Year')]
clause = query_lib._GroupByClause(fields,
schema=test_schema,
nsquare=_TEST_NSQUARE,
select_clause=select_clause)
self.assertEqual(clause.Rewrite(), 'GROUP BY Year')
fields = []
clause = query_lib._GroupByClause(fields,
schema=test_schema,
nsquare=_TEST_NSQUARE,
select_clause=select_clause)
self.assertEqual(clause.Rewrite(), '')
def testWhereRewrite(self):
schema = test_util.GetCarsSchema()
master_key = test_util.GetMasterKey()
as_clause = query_lib._AsClause({})
stack = [
util.FieldToken('Make'),
util.StringLiteralToken('"Hello"'),
util.OperatorToken('==', 2)
]
where_clause = query_lib._WhereClause(stack,
as_clause=as_clause,
schema=schema,
nsquare=_TEST_NSQUARE,
master_key=master_key,
table_id=_TABLE_ID)
self.assertEqual(
where_clause.Rewrite(),
'WHERE (%sMake == "HS57DHbh2KlkqNJREmu1wQ==")' %
util.PSEUDONYM_PREFIX)
stack = [
util.FieldToken('Model'),
util.StringLiteralToken('"A"'),
util.OperatorToken('contains', 2)
]
where_clause = query_lib._WhereClause(stack,
as_clause=as_clause,
schema=schema,
nsquare=_TEST_NSQUARE,
master_key=master_key,
table_id=_TABLE_ID)
self.assertEqual(
where_clause.Rewrite(),
'WHERE (%sModel contains to_base64(left(bytes(sha1(concat(left('
'%sModel, 24), \'yB9HY2qv+DI=\'))), 8)))' %
(util.SEARCHWORDS_PREFIX, util.SEARCHWORDS_PREFIX))
stack = []
where_clause = query_lib._WhereClause(stack,
as_clause=as_clause,
schema=schema,
nsquare=_TEST_NSQUARE,
master_key=master_key,
table_id=_TABLE_ID)
self.assertEqual(where_clause.Rewrite(), '')
def testWhereRewrite(self):
schema = test_util.GetCarsSchema()
master_key = test_util.GetMasterKey()
as_clause = query_lib._AsClause({})
stack = [util.FieldToken("Make"), util.StringLiteralToken('"Hello"'), util.OperatorToken("==", 2)]
where_clause = query_lib._WhereClause(
stack, as_clause=as_clause, schema=schema, nsquare=_TEST_NSQUARE, master_key=master_key, table_id=_TABLE_ID
)
self.assertEqual(where_clause.Rewrite(), 'WHERE (%sMake == "HS57DHbh2KlkqNJREmu1wQ==")' % util.PSEUDONYM_PREFIX)
stack = [util.FieldToken("Model"), util.StringLiteralToken('"A"'), util.OperatorToken("contains", 2)]
where_clause = query_lib._WhereClause(
stack, as_clause=as_clause, schema=schema, nsquare=_TEST_NSQUARE, master_key=master_key, table_id=_TABLE_ID
)
self.assertEqual(
where_clause.Rewrite(),
"WHERE (%sModel contains to_base64(left(bytes(sha1(concat(left("
"%sModel, 24), 'yB9HY2qv+DI='))), 8)))" % (util.SEARCHWORDS_PREFIX, util.SEARCHWORDS_PREFIX),
)
stack = []
where_clause = query_lib._WhereClause(
stack, as_clause=as_clause, schema=schema, nsquare=_TEST_NSQUARE, master_key=master_key, table_id=_TABLE_ID
)
self.assertEqual(where_clause.Rewrite(), "")
def testAsConstructColumnNames(self):
alias = {0: "a"}
columns = [[util.FieldToken("b")], [1, 2, util.OperatorToken("+", 2)]]
as_clause = query_lib._AsClause(alias)
self.assertEqual(as_clause.ConstructColumnNames(columns), [{"name": "a"}, {"name": "(1 + 2)"}])
