def test_sub_select_order(self):
sql = ("(SELECT mobile.company_id, mobile.name "
"FROM mobile "
"WHERE mobile.company_id IN "
"(SELECT company.id FROM company))")
data_source = run(sql, self.data_sources)
self.assertListEqual(data_source, [{
'name': 'xiaomi2',
'company_id': 3
}, {
'name': 'xiaomi3',
'company_id': 3
}, {
'name': 'Mix2',
'company_id': 4
}, {
'name': 'Mix2S',
'company_id': 4
}, {
'name': 'iphone4',
'company_id': 1
}, {
'name': 'iphone5',
'company_id': 1
}, {
'name': 'Mate20',
'company_id': 2
}])
def test_index_sql(self):
sql = (
'(SELECT company.name, mobile.name AS mobile_name, ceo.name AS ceo_name '
'FROM company JOIN mobile ON company.id = mobile.company_id JOIN ceo ON company.id = ceo.company_id)'
)
data_source = run(sql, self.data_sources)
self.assertListEqual(data_source, [{
'name': 'apple',
'mobile_name': 'iphone4',
'ceo_name': 'Tim Cook'
}, {
'name': 'apple',
'mobile_name': 'iphone5',
'ceo_name': 'Tim Cook'
}, {
'name': 'huawei',
'mobile_name': 'Mate20',
'ceo_name': 'renzhengfei'
}, {
'name': 'xiaomi',
'mobile_name': 'xiaomi2',
'ceo_name': 'leijun'
}, {
'name': 'xiaomi',
'mobile_name': 'xiaomi3',
'ceo_name': 'leijun'
}, {
'name': 'xiaomi',
'mobile_name': 'Mix2',
'ceo_name': 'leijun'
}, {
'name': 'xiaomi',
'mobile_name': 'Mix2S',
'ceo_name': 'leijun'
}])
def test_simple_sum_sql(self):
sql = (
"(SELECT SUM(mobile.id) AS sum_mobile_id, SUM(mobile.company_id) AS sum_company_id "
"FROM mobile)")
data_source = run(sql, self.data_sources)
self.assertListEqual(data_source, [{
'sum_company_id': 18,
'sum_mobile_id': 24
}])
def test_select_sql4(self):
sql = (
"(SELECT mobile.name AS mobile_name, company.name AS company_name, ceo.name AS ceo_name "
"FROM mobile, company, ceo "
"WHERE mobile.company_id = company.id "
"AND company.id = ceo.company_id "
"AND '1' = 'Mate20')")
data_source = run(sql, self.data_sources)
self.assertListEqual(data_source, [])
def test_join_sql2(self):
sql = (
"(SELECT company.name AS company_name, mobile.name AS mobile_name "
"FROM mobile, company "
"WHERE mobile.company_id = company.id "
"AND mobile.name = 'xiaomi3')")
data_source = run(sql, self.data_sources)
self.assertListEqual(data_source, [{
'company_name': 'xiaomi',
'mobile_name': 'xiaomi3'
}])
def test_having_sql1(self):
sql = ("(SELECT mobile.company_id, icount(*) AS count1 "
"FROM mobile GROUP BY mobile.company_id "
"HAVING COUNT(*) >= 2 AND mobile.company_id != 4)")
data_source = run(sql, self.data_sources)
self.is_same_val('company_id', data_source, [{
'count1': 2,
'company_id': 3
}, {
'count1': 2,
'company_id': 1
}])
def test_index_sql5(self):
sql = (
'(SELECT company.name '
'FROM company JOIN mobile ON company.id = mobile.company_id JOIN ceo ON company.id = ceo.company_id '
'GROUP BY company.name '
'HAVING len(company.name) = 6 '
'ORDER BY company.name DESC)')
data_source = run(sql, self.data_sources)
self.assertListEqual(data_source, [{
'name': 'xiaomi'
}, {
'name': 'huawei'
}])
def test_select_sql3(self):
sql = (
"(SELECT mobile.name AS mobile_name, company.name AS company_name, ceo.name AS ceo_name "
"FROM mobile, company, ceo "
"WHERE mobile.company_id = company.id "
"AND company.id = ceo.company_id "
"AND mobile.name = 'Mate20')")
data_source = run(sql, self.data_sources)
self.assertListEqual(data_source, [{
'company_name': 'huawei',
'mobile_name': 'Mate20',
'ceo_name': 'renzhengfei'
}])
def test_deflate2(self):
self.data_sources['company'] = []
sql = (
"(SELECT deflate(ceo.name, 'ceo_name', mobile_price.type, SUM(mobile_price.price), '0:普通,1:旗舰', 0) "
"FROM mobile, company, ceo, mobile_price "
"WHERE mobile.id = mobile_price.mobile_id "
"AND mobile.company_id = company.id "
"AND company.id = ceo.company_id "
"GROUP BY ceo.name, mobile_price.type)")
data_source = run(sql, self.data_sources)
data_source = sorted(data_source, key=lambda x: x['ceo_name'])
self.assertListEqual(data_source, [])
def test_index_sql2(self):
sql = (
'(SELECT company.name, mobile.name AS mobile_name, ceo.name AS ceo_name '
'FROM company JOIN mobile ON company.id = mobile.company_id JOIN ceo ON company.id = ceo.company_id '
'WHERE company.name = "apple")')
data_source = run(sql, self.data_sources)
self.assertListEqual(data_source, [{
'name': 'apple',
'mobile_name': 'iphone4',
'ceo_name': 'Tim Cook'
}, {
'name': 'apple',
'mobile_name': 'iphone5',
'ceo_name': 'Tim Cook'
}])
def test_group_order(self):
sql = ("(SELECT mobile.company_id "
"FROM mobile "
"GROUP BY mobile.company_id "
"ORDER BY mobile.company_id)")
data_source = run(sql, self.data_sources)
self.assertListEqual(data_source, [{
'company_id': 1
}, {
'company_id': 2
}, {
'company_id': 3
}, {
'company_id': 4
}])
def test_in_order(self):
sql = ("(SELECT mobile.company_id, mobile.name "
"FROM mobile "
"WHERE mobile.name IN ('xiaomi2', 'xiaomi3', 'Mate20'))")
data_source = run(sql, self.data_sources)
self.assertListEqual(data_source, [{
'name': 'xiaomi2',
'company_id': 3
}, {
'name': 'xiaomi3',
'company_id': 3
}, {
'name': 'Mate20',
'company_id': 2
}])
def test_rename_funcs(self):
sql = "(SELECT rename('公司,ID', company.name, company.id) FROM company)"
data_source = run(sql, self.data_sources)
self.assertListEqual(data_source, [{
'公司': 'apple',
'ID': 1
}, {
'公司': 'huawei',
'ID': 2
}, {
'公司': 'xiaomi',
'ID': 3
}, {
'公司': 'xiaomi',
'ID': 4
}])
def test_when_case_order(self):
sql = ("(SELECT "
"CASE WHEN company.name = 'xiaomi' THEN '小米' "
"WHEN company.name ='huawei' THEN '华为' "
"WHEN company.name ='apple' THEN '苹果' "
"ELSE '' END AS company_name "
"FROM company)")
data_source = run(sql, self.data_sources)
self.assertListEqual(data_source, [{
'company_name': '苹果'
}, {
'company_name': '华为'
}, {
'company_name': '小米'
}, {
'company_name': '小米'
}])
def test_group_sql4(self):
sql = ("(SELECT mobile.company_id, icount(*) AS count1 "
"FROM mobile "
"GROUP BY mobile.company_id)")
data_source = run(sql, self.data_sources)
self.is_same_val('company_id', data_source, [{
'company_id': 3,
'count1': 2
}, {
'company_id': 4,
'count1': 2
}, {
'company_id': 1,
'count1': 2
}, {
'company_id': 2,
'count1': 1
}])
def test_simple_sql1(self):
sql = ('(SELECT * '
'FROM mobile '
'WHERE mobile.company_id = 1 OR mobile.id = 1)')
data_source = run(sql, self.data_sources)
self.assertListEqual(data_source, [{
'id': 1,
'name': 'xiaomi2',
'company_id': 3
}, {
'id': 4,
'name': 'iphone4',
'company_id': 1
}, {
'id': 5,
'name': 'iphone5',
'company_id': 1
}])
def test_nested_funcs(self):
sql = ("(SELECT int(ISUM(mobile.id)) AS sum1, mobile.company_id "
"FROM mobile "
"GROUP BY mobile.company_id)")
data_source = run(sql, self.data_sources)
self.is_same_val('company_id', data_source, [{
'sum1': 3,
'company_id': 3
}, {
'sum1': 6,
'company_id': 4
}, {
'sum1': 9,
'company_id': 1
}, {
'sum1': 6,
'company_id': 2
}])
def test_not_in_order(self):
sql = ("(SELECT mobile.company_id, mobile.name "
"FROM mobile "
"WHERE mobile.name NOT IN ('xiaomi2', 'xiaomi3', 'Mate20'))")
data_source = run(sql, self.data_sources)
self.assertListEqual(data_source, [{
'name': 'Mix2',
'company_id': 4
}, {
'name': 'Mix2S',
'company_id': 4
}, {
'name': 'iphone4',
'company_id': 1
}, {
'name': 'iphone5',
'company_id': 1
}])
def test_index_sql6(self):
sql = (
'(SELECT company.name, mobile.name AS mobile_name, ceo.name AS ceo_name, mobile_price.price '
'FROM company JOIN mobile JOIN ceo ON company.id = mobile.company_id '
'JOIN mobile_price ON company.id = ceo.company_id '
'WHERE mobile_price.mobile_id = mobile.id '
'ORDER BY mobile_price.price)')
data_source = run(sql, self.data_sources)
self.assertListEqual(data_source, [{
'name': 'xiaomi',
'mobile_name': 'xiaomi2',
'ceo_name': 'leijun',
'price': 1000
}, {
'name': 'xiaomi',
'mobile_name': 'xiaomi3',
'ceo_name': 'leijun',
'price': 2000
}, {
'name': 'xiaomi',
'mobile_name': 'Mix2',
'ceo_name': 'leijun',
'price': 2000
}, {
'name': 'xiaomi',
'mobile_name': 'Mix2S',
'ceo_name': 'leijun',
'price': 2000
}, {
'name': 'apple',
'mobile_name': 'iphone4',
'ceo_name': 'Tim Cook',
'price': 5000
}, {
'name': 'apple',
'mobile_name': 'iphone5',
'ceo_name': 'Tim Cook',
'price': 6000
}, {
'name': 'huawei',
'mobile_name': 'Mate20',
'ceo_name': 'renzhengfei',
'price': 7000
}])
def test_simple_sql2(self):
sql = ("(SELECT * "
"FROM mobile "
"WHERE mobile.company_id + mobile.id > 5)")
data_source = run(sql, self.data_sources)
self.assertListEqual(data_source, [{
'id': 3,
'company_id': 4,
'name': 'Mix2'
}, {
'id': 3,
'company_id': 4,
'name': 'Mix2S'
}, {
'id': 5,
'company_id': 1,
'name': 'iphone5'
}, {
'id': 6,
'company_id': 2,
'name': 'Mate20'
}])
def test_simple_sql3(self):
sql = ("(SELECT * "
"FROM mobile "
"WHERE mobile.company_id - mobile.id < 3 "
"AND mobile.company_id - mobile.id > 0)")
data_source = run(sql, self.data_sources)
self.assertListEqual(data_source, [{
'company_id': 3,
'name': 'xiaomi2',
'id': 1
}, {
'company_id': 3,
'name': 'xiaomi3',
'id': 2
}, {
'company_id': 4,
'name': 'Mix2',
'id': 3
}, {
'company_id': 4,
'name': 'Mix2S',
'id': 3
}])
def test_deflate(self):
sql = (
"(SELECT deflate(ceo.name, 'ceo_name', mobile_price.type, SUM(mobile_price.price), '0:普通,1:旗舰', 0) "
"FROM mobile, company, ceo, mobile_price "
"WHERE mobile.id = mobile_price.mobile_id "
"AND mobile.company_id = company.id "
"AND company.id = ceo.company_id "
"GROUP BY ceo.name, mobile_price.type)")
data_source = run(sql, self.data_sources)
data_source = sorted(data_source, key=lambda x: x['ceo_name'])
self.assertListEqual(data_source, [{
'旗舰': 11000,
'普通': 0,
'ceo_name': 'Tim Cook'
}, {
'旗舰': 4000,
'普通': 3000,
'ceo_name': 'leijun'
}, {
'旗舰': 7000,
'普通': 0,
'ceo_name': 'renzhengfei'
}])
def test_index_sql3(self):
sql = (
'(SELECT company.name, mobile.name AS mobile_name, ceo.name AS ceo_name '
'FROM company JOIN mobile ON company.id = mobile.company_id JOIN ceo ON company.id = ceo.company_id '
'WHERE company.name = "xiaomi" '
'ORDER BY mobile.id DESC)')
data_source = run(sql, self.data_sources)
self.assertListEqual(data_source, [{
'name': 'xiaomi',
'mobile_name': 'Mix2',
'ceo_name': 'leijun'
}, {
'name': 'xiaomi',
'mobile_name': 'Mix2S',
'ceo_name': 'leijun'
}, {
'name': 'xiaomi',
'mobile_name': 'xiaomi3',
'ceo_name': 'leijun'
}, {
'name': 'xiaomi',
'mobile_name': 'xiaomi2',
'ceo_name': 'leijun'
}])
def test_sum_sql3(self):
sql = "(SELECT SUM(mobile.id) AS sum1 FROM mobile)"
data_source = run(sql, self.data_sources)
self.assertListEqual(data_source, [{'sum1': 24}])
def test_cale_sql(self):
sql = ('(SELECT company.id+2*3 AS id '
'FROM company '
'WHERE company.name = "apple")')
data_source = run(sql, self.data_sources)
self.assertListEqual(data_source, [{'id': 7}])
def test_simple_count_sql(self):
sql = "(SELECT COUNT(*) AS count1 FROM mobile)"
data_source = run(sql, self.data_sources)
self.assertListEqual(data_source, [{'count1': 7}])
