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)"}])