def parse_clickhouse_json(jsonBody, db_name, db_host):
visits_buffer = []
for i in jsonBody:
#print(i)
# inserting data into clickhouse model representation
insert_visits = Actions(
user_id=i['user_id'],
user_name = i['user_name'],
time = i['time'],
event_type = i['event_type'],
screen_name = i['screen_name'],
app_name = i['app_name'],
app_productname = i['app_productname'],
app_version = i['app_version'],
app_publisher =i['app_publisher'],
app_file =i['app_file'],
app_copyright =i['app_copyright'],
app_language = i['app_language'],
file_versioninfo =i['file_versioninfo'],
file_description = i['file_description'],
file_internalname =i['file_internalname'],
file_originalname =i['file_originalname'],
)
visits_buffer.append(insert_visits)
db = Database(db_name, db_url=db_host)
# create table to insert prepared data
db.create_table(Actions)
# insert prepared data into database
db.insert(visits_buffer)
def Insert(Bear):
date = datetime.strptime(Bear.timestamp, "%Y-%m-%d %H:%M:%S.%f")
db = Database('Honeypot',
db_url=CLICKHOUSEIP + ':' + CLICKHOUSEPORT,
username=CLICKHOUSEUSER,
password=CLICKHOUSEPASSWORD)
DBBear = BearRequests_development(
EventDate=date.date(),
RequestTime=date,
RequestPath=Bear.path,
RequestCommand=Bear.command,
RequestVersion=Bear.version,
RequestRaw=Bear.rawrequest,
ProbeName=Bear.hostname,
RequestDetectionID=Bear.isDetected,
BotIP=Bear.ip,
BotCountry=Bear.country,
BotUA=Bear.ua,
BotContinent=Bear.continent,
BotTracert=Bear.tracert,
BotDNSName=Bear.dnsname,
)
db.insert({
DBBear,
})
class UUIDFieldsTest(unittest.TestCase):
def setUp(self):
self.database = Database('test-db', log_statements=True)
def tearDown(self):
self.database.drop_database()
def test_uuid_field(self):
# Create a model
class TestModel(Model):
i = Int16Field()
f = UUIDField()
engine = Memory()
self.database.create_table(TestModel)
# Check valid values (all values are the same UUID)
values = [
'12345678-1234-5678-1234-567812345678',
'{12345678-1234-5678-1234-567812345678}',
'12345678123456781234567812345678',
'urn:uuid:12345678-1234-5678-1234-567812345678',
b'\x12\x34\x56\x78' * 4,
(0x12345678, 0x1234, 0x5678, 0x12, 0x34, 0x567812345678),
0x12345678123456781234567812345678,
UUID(int=0x12345678123456781234567812345678),
]
for index, value in enumerate(values):
rec = TestModel(i=index, f=value)
self.database.insert([rec])
for rec in TestModel.objects_in(self.database):
self.assertEqual(rec.f, UUID(values[0]))
# Check invalid values
for value in [None, 'zzz', -1, '123']:
with self.assertRaises(ValueError):
TestModel(i=1, f=value)
class CustomFieldsTest(unittest.TestCase):
def setUp(self):
self.database = Database('test-db', log_statements=True)
def tearDown(self):
self.database.drop_database()
def test_boolean_field(self):
# Create a model
class TestModel(Model):
i = Int16Field()
f = BooleanField()
engine = Memory()
self.database.create_table(TestModel)
# Check valid values
for index, value in enumerate([1, '1', True, 0, '0', False]):
rec = TestModel(i=index, f=value)
self.database.insert([rec])
self.assertEqual([
rec.f for rec in TestModel.objects_in(self.database).order_by('i')
], [True, True, True, False, False, False])
# Check invalid values
for value in [None, 'zzz', -5, 7]:
with self.assertRaises(ValueError):
TestModel(i=1, f=value)
class DateFieldsTest(unittest.TestCase):
def setUp(self):
self.database = Database('test-db', log_statements=True)
self.database.create_table(ModelWithDate)
def tearDown(self):
self.database.drop_database()
def test_ad_hoc_model(self):
self.database.insert([
ModelWithDate(date_field='2016-08-30',
datetime_field='2016-08-30 03:50:00'),
ModelWithDate(date_field='2016-08-31',
datetime_field='2016-08-31 01:30:00')
])
# toStartOfHour returns DateTime('Asia/Yekaterinburg') in my case, so I test it here to
query = 'SELECT toStartOfHour(datetime_field) as hour_start, * from $db.modelwithdate ORDER BY date_field'
results = list(self.database.select(query))
self.assertEqual(len(results), 2)
self.assertEqual(results[0].date_field, datetime.date(2016, 8, 30))
self.assertEqual(
results[0].datetime_field,
datetime.datetime(2016, 8, 30, 3, 50, 0, tzinfo=pytz.UTC))
self.assertEqual(
results[0].hour_start,
datetime.datetime(2016, 8, 30, 3, 0, 0, tzinfo=pytz.UTC))
self.assertEqual(results[1].date_field, datetime.date(2016, 8, 31))
self.assertEqual(
results[1].datetime_field,
datetime.datetime(2016, 8, 31, 1, 30, 0, tzinfo=pytz.UTC))
self.assertEqual(
results[1].hour_start,
datetime.datetime(2016, 8, 31, 1, 0, 0, tzinfo=pytz.UTC))
class DateFieldsTest(unittest.TestCase):
def setUp(self):
self.database = Database('test-db')
self.database.create_table(ModelWithDate)
def tearDown(self):
self.database.drop_database()
def test_ad_hoc_model(self):
self.database.insert([
ModelWithDate(date_field='2016-08-30', datetime_field='2016-08-30 03:50:00'),
ModelWithDate(date_field='2016-08-31', datetime_field='2016-08-31 01:30:00')
])
# toStartOfHour returns DateTime('Asia/Yekaterinburg') in my case, so I test it here to
query = 'SELECT toStartOfHour(datetime_field) as hour_start, * from $db.modelwithdate ORDER BY date_field'
results = list(self.database.select(query))
self.assertEqual(len(results), 2)
self.assertEqual(results[0].date_field, datetime.date(2016, 8, 30))
self.assertEqual(results[0].datetime_field, datetime.datetime(2016, 8, 30, 3, 50, 0, tzinfo=pytz.UTC))
self.assertEqual(results[0].hour_start, datetime.datetime(2016, 8, 30, 3, 0, 0, tzinfo=pytz.UTC))
self.assertEqual(results[1].date_field, datetime.date(2016, 8, 31))
self.assertEqual(results[1].datetime_field, datetime.datetime(2016, 8, 31, 1, 30, 0, tzinfo=pytz.UTC))
self.assertEqual(results[1].hour_start, datetime.datetime(2016, 8, 31, 1, 0, 0, tzinfo=pytz.UTC))
class SystemPartTest(unittest.TestCase):
BACKUP_DIR = '/opt/clickhouse/shadow/'
def setUp(self):
self.database = Database('test-db')
self.database.create_table(TestTable)
self.database.insert([TestTable(date_field=date.today())])
def tearDown(self):
self.database.drop_database()
def _get_backups(self):
if not os.path.exists(self.BACKUP_DIR):
return []
_, dirnames, _ = next(os.walk(self.BACKUP_DIR))
return dirnames
def test_get_all(self):
parts = SystemPart.get(self.database)
self.assertEqual(len(list(parts)), 1)
def test_get_active(self):
parts = list(SystemPart.get_active(self.database))
self.assertEqual(len(parts), 1)
parts[0].detach()
self.assertEqual(len(list(SystemPart.get_active(self.database))), 0)
def test_get_conditions(self):
parts = list(SystemPart.get(self.database, conditions="table='testtable'"))
self.assertEqual(len(parts), 1)
parts = list(SystemPart.get(self.database, conditions="table='othertable'"))
self.assertEqual(len(parts), 0)
def test_attach_detach(self):
parts = list(SystemPart.get_active(self.database))
self.assertEqual(len(parts), 1)
parts[0].detach()
self.assertEqual(len(list(SystemPart.get_active(self.database))), 0)
parts[0].attach()
self.assertEqual(len(list(SystemPart.get_active(self.database))), 1)
def test_drop(self):
parts = list(SystemPart.get_active(self.database))
parts[0].drop()
self.assertEqual(len(list(SystemPart.get_active(self.database))), 0)
def test_freeze(self):
parts = list(SystemPart.get(self.database))
# There can be other backups in the folder
prev_backups = set(self._get_backups())
parts[0].freeze()
backups = set(self._get_backups())
self.assertEqual(len(backups), len(prev_backups) + 1)
# Clean created backup
shutil.rmtree(self.BACKUP_DIR + '{0}'.format(list(backups - prev_backups)[0]))
def test_fetch(self):
# TODO Not tested, as I have no replication set
pass
class ArrayFieldsTest(unittest.TestCase):
def setUp(self):
self.database = Database('test-db')
self.database.create_table(ModelWithArrays)
def tearDown(self):
self.database.drop_database()
def test_insert_and_select(self):
instance = ModelWithArrays(
date_field='2016-08-30',
arr_str=['goodbye,', 'cruel', 'world', 'special chars: ,"\\\'` \n\t\\[]'],
arr_date=['2010-01-01'],
)
self.database.insert([instance])
query = 'SELECT * from $db.modelwitharrays ORDER BY date_field'
for model_cls in (ModelWithArrays, None):
results = list(self.database.select(query, model_cls))
self.assertEqual(len(results), 1)
self.assertEqual(results[0].arr_str, instance.arr_str)
self.assertEqual(results[0].arr_int, instance.arr_int)
self.assertEqual(results[0].arr_date, instance.arr_date)
def test_conversion(self):
instance = ModelWithArrays(
arr_int=('1', '2', '3'),
arr_date=['2010-01-01']
)
self.assertEqual(instance.arr_str, [])
self.assertEqual(instance.arr_int, [1, 2, 3])
self.assertEqual(instance.arr_date, [date(2010, 1, 1)])
def test_assignment_error(self):
instance = ModelWithArrays()
for value in (7, 'x', [date.today()], ['aaa'], [None]):
with self.assertRaises(ValueError):
instance.arr_int = value
def test_parse_array(self):
from infi.clickhouse_orm.utils import parse_array, unescape
self.assertEqual(parse_array("[]"), [])
self.assertEqual(parse_array("[1, 2, 395, -44]"), ["1", "2", "395", "-44"])
self.assertEqual(parse_array("['big','mouse','','!']"), ["big", "mouse", "", "!"])
self.assertEqual(parse_array(unescape("['\\r\\n\\0\\t\\b']")), ["\r\n\0\t\b"])
for s in ("",
"[",
"]",
"[1, 2",
"3, 4]",
"['aaa', 'aaa]"):
with self.assertRaises(ValueError):
parse_array(s)
def test_invalid_inner_field(self):
for x in (DateField, None, "", ArrayField(Int32Field())):
with self.assertRaises(AssertionError):
ArrayField(x)
class IPFieldsTest(unittest.TestCase):
def setUp(self):
self.database = Database('test-db', log_statements=True)
def tearDown(self):
self.database.drop_database()
def test_ipv4_field(self):
if self.database.server_version < (19, 17):
raise unittest.SkipTest('ClickHouse version too old')
# Create a model
class TestModel(Model):
i = Int16Field()
f = IPv4Field()
engine = Memory()
self.database.create_table(TestModel)
# Check valid values (all values are the same ip)
values = [
'1.2.3.4', b'\x01\x02\x03\x04', 16909060,
IPv4Address('1.2.3.4')
]
for index, value in enumerate(values):
rec = TestModel(i=index, f=value)
self.database.insert([rec])
for rec in TestModel.objects_in(self.database):
self.assertEqual(rec.f, IPv4Address(values[0]))
# Check invalid values
for value in [None, 'zzz', -1, '123']:
with self.assertRaises(ValueError):
TestModel(i=1, f=value)
def test_ipv6_field(self):
if self.database.server_version < (19, 17):
raise unittest.SkipTest('ClickHouse version too old')
# Create a model
class TestModel(Model):
i = Int16Field()
f = IPv6Field()
engine = Memory()
self.database.create_table(TestModel)
# Check valid values (all values are the same ip)
values = [
'2a02:e980:1e::1',
b'*\x02\xe9\x80\x00\x1e\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01',
55842696359362256756849388082849382401,
IPv6Address('2a02:e980:1e::1')
]
for index, value in enumerate(values):
rec = TestModel(i=index, f=value)
self.database.insert([rec])
for rec in TestModel.objects_in(self.database):
self.assertEqual(rec.f, IPv6Address(values[0]))
# Check invalid values
for value in [None, 'zzz', -1, '123']:
with self.assertRaises(ValueError):
TestModel(i=1, f=value)
class ArrayFieldsTest(unittest.TestCase):
def setUp(self):
self.database = Database('test-db', log_statements=True)
self.database.create_table(ModelWithArrays)
def tearDown(self):
self.database.drop_database()
def test_insert_and_select(self):
instance = ModelWithArrays(
date_field='2016-08-30',
arr_str=['goodbye,', 'cruel', 'world', 'special chars: ,"\\\'` \n\t\\[]'],
arr_date=['2010-01-01'],
)
self.database.insert([instance])
query = 'SELECT * from $db.modelwitharrays ORDER BY date_field'
for model_cls in (ModelWithArrays, None):
results = list(self.database.select(query, model_cls))
self.assertEqual(len(results), 1)
self.assertEqual(results[0].arr_str, instance.arr_str)
self.assertEqual(results[0].arr_int, instance.arr_int)
self.assertEqual(results[0].arr_date, instance.arr_date)
def test_conversion(self):
instance = ModelWithArrays(
arr_int=('1', '2', '3'),
arr_date=['2010-01-01']
)
self.assertEqual(instance.arr_str, [])
self.assertEqual(instance.arr_int, [1, 2, 3])
self.assertEqual(instance.arr_date, [date(2010, 1, 1)])
def test_assignment_error(self):
instance = ModelWithArrays()
for value in (7, 'x', [date.today()], ['aaa'], [None]):
with self.assertRaises(ValueError):
instance.arr_int = value
def test_parse_array(self):
from infi.clickhouse_orm.utils import parse_array, unescape
self.assertEqual(parse_array("[]"), [])
self.assertEqual(parse_array("[1, 2, 395, -44]"), ["1", "2", "395", "-44"])
self.assertEqual(parse_array("['big','mouse','','!']"), ["big", "mouse", "", "!"])
self.assertEqual(parse_array(unescape("['\\r\\n\\0\\t\\b']")), ["\r\n\0\t\b"])
for s in ("",
"[",
"]",
"[1, 2",
"3, 4]",
"['aaa', 'aaa]"):
with self.assertRaises(ValueError):
parse_array(s)
def test_invalid_inner_field(self):
for x in (DateField, None, "", ArrayField(Int32Field())):
with self.assertRaises(AssertionError):
ArrayField(x)
class CustomFieldsTest(unittest.TestCase):
def setUp(self):
self.database = Database('test-db')
def tearDown(self):
self.database.drop_database()
def test_boolean_field(self):
# Create a model
class TestModel(Model):
i = Int16Field()
f = BooleanField()
engine = Memory()
self.database.create_table(TestModel)
# Check valid values
for index, value in enumerate([1, '1', True, 0, '0', False]):
rec = TestModel(i=index, f=value)
self.database.insert([rec])
self.assertEqual([rec.f for rec in TestModel.objects_in(self.database).order_by('i')],
[True, True, True, False, False, False])
# Check invalid values
for value in [None, 'zzz', -5, 7]:
with self.assertRaises(ValueError):
TestModel(i=1, f=value)
def test_uuid_field(self):
# Create a model
class TestModel(Model):
i = Int16Field()
f = UUIDField()
engine = Memory()
self.database.create_table(TestModel)
# Check valid values (all values are the same UUID)
values = [
'{12345678-1234-5678-1234-567812345678}',
'12345678123456781234567812345678',
'urn:uuid:12345678-1234-5678-1234-567812345678',
'\x12\x34\x56\x78'*4,
(0x12345678, 0x1234, 0x5678, 0x12, 0x34, 0x567812345678),
0x12345678123456781234567812345678,
]
for index, value in enumerate(values):
rec = TestModel(i=index, f=value)
self.database.insert([rec])
for rec in TestModel.objects_in(self.database):
self.assertEqual(rec.f, UUID(values[0]))
# Check that ClickHouse encoding functions are supported
for rec in self.database.select("SELECT i, UUIDNumToString(f) AS f FROM testmodel", TestModel):
self.assertEqual(rec.f, UUID(values[0]))
for rec in self.database.select("SELECT 1 as i, UUIDStringToNum('12345678-1234-5678-1234-567812345678') AS f", TestModel):
self.assertEqual(rec.f, UUID(values[0]))
# Check invalid values
for value in [None, 'zzz', -1, '123']:
with self.assertRaises(ValueError):
TestModel(i=1, f=value)
class AliasFieldsTest(unittest.TestCase):
def setUp(self):
self.database = Database('test-db', log_statements=True)
self.database.create_table(ModelWithAliasFields)
def tearDown(self):
self.database.drop_database()
def test_insert_and_select(self):
instance = ModelWithAliasFields(
date_field='2016-08-30',
int_field=-10,
str_field='TEST'
)
self.database.insert([instance])
# We can't select * from table, as it doesn't select materialized and alias fields
query = 'SELECT date_field, int_field, str_field, alias_int, alias_date, alias_str, alias_func' \
' FROM $db.%s ORDER BY alias_date' % ModelWithAliasFields.table_name()
for model_cls in (ModelWithAliasFields, None):
results = list(self.database.select(query, model_cls))
self.assertEqual(len(results), 1)
self.assertEqual(results[0].date_field, instance.date_field)
self.assertEqual(results[0].int_field, instance.int_field)
self.assertEqual(results[0].str_field, instance.str_field)
self.assertEqual(results[0].alias_int, instance.int_field)
self.assertEqual(results[0].alias_str, instance.str_field)
self.assertEqual(results[0].alias_date, instance.date_field)
self.assertEqual(results[0].alias_func, 201608)
def test_assignment_error(self):
# I can't prevent assigning at all, in case db.select statements with model provided sets model fields.
instance = ModelWithAliasFields()
for value in ('x', [date.today()], ['aaa'], [None]):
with self.assertRaises(ValueError):
instance.alias_date = value
def test_wrong_field(self):
with self.assertRaises(AssertionError):
StringField(alias=123)
def test_duplicate_default(self):
with self.assertRaises(AssertionError):
StringField(alias='str_field', default='with default')
with self.assertRaises(AssertionError):
StringField(alias='str_field', materialized='str_field')
def test_default_value(self):
instance = ModelWithAliasFields()
self.assertEqual(instance.alias_str, NO_VALUE)
# Check that NO_VALUE can be assigned to a field
instance.str_field = NO_VALUE
# Check that NO_VALUE can be assigned when creating a new instance
instance2 = ModelWithAliasFields(**instance.to_dict())
class FieldsTestCase(BaseTestCase):
values_to_insert = {
'id': 1,
'timestamp': timezone.now(),
'timestamp_date': timezone.now().date(),
'string_field': 'a',
'intfield': 5,
'floatfield': 0.5,
'null_field': 'b',
# populate enum_field with the first value in enum_
'enum_field': list(enum_)[0]
}
def setUp(self):
super(FieldsTestCase, self).setUp()
self.login()
self.db = Database('test', settings.CLICKHOUSE_URL)
self.db.create_table(ClickhouseAllFields)
object1 = ClickhouseAllFields(**self.values_to_insert)
self.db.insert([object1])
def tearDown(self):
self.db.drop_table(ClickhouseAllFields)
self.db.drop_database()
def test_all_fields_exists(self):
res = self.client.get('/api/rest/allfields/')
result = res.json()['result']
# check that each key exists and stores a value
for key, val in self.values_to_insert.items():
# not comparing the values due to differences in case of datetime fields etc. that test is being done in
# test_fields anyway
self.assertIsNotNone(result[0].get(key))
def test_partial_fields(self):
res = self.client.get('/api/rest/partialfields/')
result = res.json()['result']
for key in result[0].keys():
self.assertTrue(key in PARTIAL_FIELDS)
def test_exclude_fields(self):
res = self.client.get('/api/rest/excludefields/')
result = res.json()['result']
for key in EXCLUDE_FIELDS:
self.assertFalse(key in result[0])
class MaterializedFieldsTest(unittest.TestCase):
def setUp(self):
self.database = Database('test-db')
self.database.create_table(ModelWithMaterializedFields)
def tearDown(self):
self.database.drop_database()
def test_insert_and_select(self):
instance = ModelWithMaterializedFields(
date_time_field='2016-08-30 11:00:00',
int_field=-10,
str_field='TEST')
self.database.insert([instance])
# We can't select * from table, as it doesn't select materialized and alias fields
query = 'SELECT date_time_field, int_field, str_field, mat_int, mat_date, mat_str' \
' FROM $db.%s ORDER BY mat_date' % ModelWithMaterializedFields.table_name()
for model_cls in (ModelWithMaterializedFields, None):
results = list(self.database.select(query, model_cls))
self.assertEqual(len(results), 1)
self.assertEqual(results[0].date_time_field,
instance.date_time_field)
self.assertEqual(results[0].int_field, instance.int_field)
self.assertEqual(results[0].str_field, instance.str_field)
self.assertEqual(results[0].mat_int, abs(instance.int_field))
self.assertEqual(results[0].mat_str, instance.str_field.lower())
self.assertEqual(results[0].mat_date,
instance.date_time_field.date())
def test_assignment_error(self):
# I can't prevent assigning at all, in case db.select statements with model provided sets model fields.
instance = ModelWithMaterializedFields()
for value in ('x', [date.today()], ['aaa'], [None]):
with self.assertRaises(ValueError):
instance.mat_date = value
def test_wrong_field(self):
with self.assertRaises(AssertionError):
StringField(materialized=123)
def test_duplicate_default(self):
with self.assertRaises(AssertionError):
StringField(materialized='str_field', default='with default')
with self.assertRaises(AssertionError):
StringField(materialized='str_field', alias='str_field')
def __insert__(self, models=[]):
try:
db_url = "http://{0}:{1}".format(self.args.host or "localhost",
self.args.port or 8123)
db = Database(db_name=self.args.database,
db_url=db_url,
username=self.args.username,
password=self.args.password)
ctime = datetime.datetime.now()
db.insert(models)
took = datetime.datetime.now() - ctime
print("{0} Records have been written into clickhouse , Took : {1}".
format(len(models), str(took)))
except Exception as e:
print(e)
print(traceback.format_exc())
raise e
class MaterializedFieldsTest(unittest.TestCase):
def setUp(self):
self.database = Database('test-db')
self.database.create_table(ModelWithAliasFields)
def tearDown(self):
self.database.drop_database()
def test_insert_and_select(self):
instance = ModelWithAliasFields(
date_field='2016-08-30',
int_field=-10,
str_field='TEST'
)
self.database.insert([instance])
# We can't select * from table, as it doesn't select materialized and alias fields
query = 'SELECT date_field, int_field, str_field, alias_int, alias_date, alias_str' \
' FROM $db.%s ORDER BY alias_date' % ModelWithAliasFields.table_name()
for model_cls in (ModelWithAliasFields, None):
results = list(self.database.select(query, model_cls))
self.assertEqual(len(results), 1)
self.assertEqual(results[0].date_field, instance.date_field)
self.assertEqual(results[0].int_field, instance.int_field)
self.assertEqual(results[0].str_field, instance.str_field)
self.assertEqual(results[0].alias_int, instance.int_field)
self.assertEqual(results[0].alias_str, instance.str_field)
self.assertEqual(results[0].alias_date, instance.date_field)
def test_assignment_error(self):
# I can't prevent assigning at all, in case db.select statements with model provided sets model fields.
instance = ModelWithAliasFields()
for value in ('x', [date.today()], ['aaa'], [None]):
with self.assertRaises(ValueError):
instance.alias_date = value
def test_wrong_field(self):
with self.assertRaises(AssertionError):
StringField(alias=123)
def test_duplicate_default(self):
with self.assertRaises(AssertionError):
StringField(alias='str_field', default='with default')
with self.assertRaises(AssertionError):
StringField(alias='str_field', materialized='str_field')
class TestCaseWithData(unittest.TestCase):
def setUp(self):
self.database = Database('test-db')
self.database.create_table(Person)
def tearDown(self):
self.database.drop_table(Person)
self.database.drop_database()
def _insert_and_check(self, data, count):
self.database.insert(data)
self.assertEquals(count, self.database.count(Person))
for instance in data:
self.assertEquals(self.database, instance.get_database())
def _sample_data(self):
for entry in data:
yield Person(**entry)
def load():
'''
Loads data from local JSON files into database
TODO: Load data from s3 buckets
'''
db = Database('contrail')
db.create_table(InstanceData)
fil = open('sample_data.json')
fil2 = open('spot_data.json')
d = json.load(fil)
d2 = json.load(fil2)
product_dict, on_demand_dict, reserved_dict, product_keys, on_demand_keys, reserved_keys = getAllAttributes(
d)
spot_data = getSpotData(d2, product_keys + on_demand_keys + reserved_keys)
product_attribute_data = getData(product_dict, product_keys)
on_demand_data = getData(on_demand_dict, on_demand_keys)
reserved_data = getData(reserved_dict, reserved_keys)
combineddata = {
key: on_demand_data[key] + value
for key, value in product_attribute_data.items()
}
data = {}
lst = []
for key, value in combineddata.items():
for k, v in reserved_data.items():
if k.startswith(key):
lst.append(key)
try:
data[k].append(value + v)
except (KeyError):
data[k] = value + v
if key not in set(lst):
try:
data[key].append(value)
except (KeyError):
data[key] = value
items = list(data.values()) + spot_data
for item in items:
instance = InstanceData()
for i in item:
setattr(instance, i[0], i[1])
db.insert([instance])
class SystemTest(unittest.TestCase):
def setUp(self):
self.database = Database('test-db')
def tearDown(self):
self.database.drop_database()
def test_insert_system(self):
m = SystemPart()
with self.assertRaises(DatabaseException):
self.database.insert([m])
def test_create_readonly_table(self):
with self.assertRaises(DatabaseException):
self.database.create_table(SystemTestModel)
def test_drop_readonly_table(self):
with self.assertRaises(DatabaseException):
self.database.drop_table(SystemTestModel)
class TestCaseWithData(unittest.TestCase):
def setUp(self):
self.database = Database('test-db')
self.database.create_table(Person)
def tearDown(self):
self.database.drop_table(Person)
self.database.drop_database()
def _insert_and_check(self, data, count, batch_size=1000):
self.database.insert(data, batch_size=batch_size)
self.assertEqual(count, self.database.count(Person))
for instance in data:
self.assertEqual(self.database, instance.get_database())
def _sample_data(self):
for entry in data:
yield Person(**entry)
class SystemTest(unittest.TestCase):
def setUp(self):
self.database = Database('test-db')
def tearDown(self):
self.database.drop_database()
def test_insert_system(self):
m = SystemPart()
with self.assertRaises(DatabaseException):
self.database.insert([m])
def test_create_readonly_table(self):
with self.assertRaises(DatabaseException):
self.database.create_table(SystemTestModel)
def test_drop_readonly_table(self):
with self.assertRaises(DatabaseException):
self.database.drop_table(SystemTestModel)
class ReadonlyTestCase(TestCaseWithData):
def _test_readonly_db(self, username):
self._insert_and_check(self._sample_data(), len(data))
orig_database = self.database
try:
self.database = Database(orig_database.db_name,
username=username,
readonly=True)
with self.assertRaises(DatabaseException):
self._insert_and_check(self._sample_data(), len(data))
self.assertEquals(self.database.count(Person), 100)
list(self.database.select('SELECT * from $table', Person))
with self.assertRaises(DatabaseException):
self.database.drop_table(Person)
with self.assertRaises(DatabaseException):
self.database.drop_database()
except DatabaseException as e:
if 'Unknown user' in six.text_type(e):
raise unittest.SkipTest('Database user "%s" is not defined' %
username)
else:
raise
finally:
self.database = orig_database
def test_readonly_db_with_default_user(self):
self._test_readonly_db('default')
def test_readonly_db_with_readonly_user(self):
self._test_readonly_db('readonly')
def test_insert_readonly(self):
m = ReadOnlyModel(name='readonly')
with self.assertRaises(DatabaseException):
self.database.insert([m])
def test_create_readonly_table(self):
with self.assertRaises(DatabaseException):
self.database.create_table(ReadOnlyModel)
def test_drop_readonly_table(self):
with self.assertRaises(DatabaseException):
self.database.drop_table(ReadOnlyModel)
class ClickHouseBaseBenchmark(AsyncBenchmark):
def __init__(self, model):
super(AsyncBenchmark, self).__init__()
self.db = Database('TestDB')
self.db.create_table(model)
def _insert_data(self, data):
self.db.insert(data)
def _validate_data(self, expected, table):
now = time.time()
value = int(self.db.raw('SELECT count(*) FROM {}'.format(table)))
if expected == value:
logger.info('The stored data is equal to the produced quantity.')
else:
logger.warning(
'The stored data is different to the produced quantity (expected {} != {}).'
.format(expected, value))
return (expected, value, time.time() - now)
class FixedStringFieldsTest(unittest.TestCase):
def setUp(self):
self.database = Database('test-db')
self.database.create_table(FixedStringModel)
def tearDown(self):
self.database.drop_database()
def _insert_sample_data(self):
self.database.insert([
FixedStringModel(date_field='2016-08-30', fstr_field=''),
FixedStringModel(date_field='2016-08-30'),
FixedStringModel(date_field='2016-08-31', fstr_field='foo'),
FixedStringModel(date_field='2016-08-31', fstr_field=u'לילה')
])
def _assert_sample_data(self, results):
self.assertEquals(len(results), 4)
self.assertEquals(results[0].fstr_field, '')
self.assertEquals(results[1].fstr_field, 'ABCDEFGHIJK')
self.assertEquals(results[2].fstr_field, 'foo')
self.assertEquals(results[3].fstr_field, u'לילה')
def test_insert_and_select(self):
self._insert_sample_data()
query = 'SELECT * from $table ORDER BY date_field'
results = list(self.database.select(query, FixedStringModel))
self._assert_sample_data(results)
def test_ad_hoc_model(self):
self._insert_sample_data()
query = 'SELECT * from $db.fixedstringmodel ORDER BY date_field'
results = list(self.database.select(query))
self._assert_sample_data(results)
def test_assignment_error(self):
for value in (17, 'this is too long', u'זה ארוך', None, 99.9):
with self.assertRaises(ValueError):
FixedStringModel(fstr_field=value)
class FixedStringFieldsTest(unittest.TestCase):
def setUp(self):
self.database = Database('test-db')
self.database.create_table(FixedStringModel)
def tearDown(self):
self.database.drop_database()
def _insert_sample_data(self):
self.database.insert([
FixedStringModel(date_field='2016-08-30', fstr_field=''),
FixedStringModel(date_field='2016-08-30'),
FixedStringModel(date_field='2016-08-31', fstr_field='foo'),
FixedStringModel(date_field='2016-08-31', fstr_field=u'לילה')
])
def _assert_sample_data(self, results):
self.assertEqual(len(results), 4)
self.assertEqual(results[0].fstr_field, '')
self.assertEqual(results[1].fstr_field, 'ABCDEFGHIJK')
self.assertEqual(results[2].fstr_field, 'foo')
self.assertEqual(results[3].fstr_field, u'לילה')
def test_insert_and_select(self):
self._insert_sample_data()
query = 'SELECT * from $table ORDER BY date_field'
results = list(self.database.select(query, FixedStringModel))
self._assert_sample_data(results)
def test_ad_hoc_model(self):
self._insert_sample_data()
query = 'SELECT * from $db.fixedstringmodel ORDER BY date_field'
results = list(self.database.select(query))
self._assert_sample_data(results)
def test_assignment_error(self):
for value in (17, 'this is too long', u'זה ארוך', None, 99.9):
with self.assertRaises(ValueError):
FixedStringModel(fstr_field=value)
class TestCaseWithData(unittest.TestCase):
def setUp(self):
self.database = Database('test-db', log_statements=True)
self.database.create_table(Person)
def tearDown(self):
self.database.drop_table(Person)
self.database.drop_database()
def _insert_all(self):
self.database.insert(self._sample_data())
self.assertTrue(self.database.count(Person))
def _insert_and_check(self, data, count, batch_size=1000):
self.database.insert(data, batch_size=batch_size)
self.assertEqual(count, self.database.count(Person))
for instance in data:
self.assertEqual(self.database, instance.get_database())
def _sample_data(self):
for entry in data:
yield Person(**entry)
async def get_full_depth(symbol: str, session: ClientSession,
database: Database, asset_type: AssetType):
limit = CONFIG.full_fetch_limit
if asset_type == AssetType.SPOT:
url = f"https://api.binance.com/api/v3/depth?symbol={symbol}&limit={limit}"
elif asset_type == AssetType.USD_M:
url = f"https://fapi.binance.com/fapi/v1/depth?symbol={symbol}&limit={limit}"
elif asset_type == AssetType.COIN_M:
url = f"https://dapi.binance.com/dapi/v1/depth?symbol={symbol}&limit={limit}"
async with session.get(url) as resp:
resp_json = await resp.json()
msg = DepthSnapshotMsg(**resp_json)
snapshot = DepthSnapshot(
timestamp=datetime.utcnow(),
last_update_id=msg.lastUpdateId,
bids_quantity=[pairs[1] for pairs in msg.bids],
bids_price=[pairs[0] for pairs in msg.bids],
asks_quantity=[pairs[1] for pairs in msg.asks],
asks_price=[pairs[0] for pairs in msg.asks],
symbol=asset_type.value + symbol,
)
database.insert([snapshot])
class EnumFieldsTest(unittest.TestCase):
def setUp(self):
self.database = Database('test-db')
self.database.create_table(ModelWithEnum)
self.database.create_table(ModelWithEnumArray)
def tearDown(self):
self.database.drop_database()
def test_insert_and_select(self):
self.database.insert([
ModelWithEnum(date_field='2016-08-30', enum_field=Fruit.apple),
ModelWithEnum(date_field='2016-08-31', enum_field=Fruit.orange)
])
query = 'SELECT * from $table ORDER BY date_field'
results = list(self.database.select(query, ModelWithEnum))
self.assertEqual(len(results), 2)
self.assertEqual(results[0].enum_field, Fruit.apple)
self.assertEqual(results[1].enum_field, Fruit.orange)
def test_ad_hoc_model(self):
self.database.insert([
ModelWithEnum(date_field='2016-08-30', enum_field=Fruit.apple),
ModelWithEnum(date_field='2016-08-31', enum_field=Fruit.orange)
])
query = 'SELECT * from $db.modelwithenum ORDER BY date_field'
results = list(self.database.select(query))
self.assertEqual(len(results), 2)
self.assertEqual(results[0].enum_field.name, Fruit.apple.name)
self.assertEqual(results[0].enum_field.value, Fruit.apple.value)
self.assertEqual(results[1].enum_field.name, Fruit.orange.name)
self.assertEqual(results[1].enum_field.value, Fruit.orange.value)
def test_conversion(self):
self.assertEqual(ModelWithEnum(enum_field=3).enum_field, Fruit.orange)
self.assertEqual(
ModelWithEnum(enum_field='apple').enum_field, Fruit.apple)
self.assertEqual(
ModelWithEnum(enum_field=Fruit.banana).enum_field, Fruit.banana)
def test_assignment_error(self):
for value in (0, 17, 'pear', '', None, 99.9):
with self.assertRaises(ValueError):
ModelWithEnum(enum_field=value)
def test_default_value(self):
instance = ModelWithEnum()
self.assertEqual(instance.enum_field, Fruit.apple)
def test_enum_array(self):
instance = ModelWithEnumArray(
date_field='2016-08-30',
enum_array=[Fruit.apple, Fruit.apple, Fruit.orange])
self.database.insert([instance])
query = 'SELECT * from $table ORDER BY date_field'
results = list(self.database.select(query, ModelWithEnumArray))
self.assertEqual(len(results), 1)
self.assertEqual(results[0].enum_array, instance.enum_array)
class EnumFieldsTest(unittest.TestCase):
def setUp(self):
self.database = Database('test-db')
self.database.create_table(ModelWithEnum)
self.database.create_table(ModelWithEnumArray)
def tearDown(self):
self.database.drop_database()
def test_insert_and_select(self):
self.database.insert([
ModelWithEnum(date_field='2016-08-30', enum_field=Fruit.apple),
ModelWithEnum(date_field='2016-08-31', enum_field=Fruit.orange)
])
query = 'SELECT * from $table ORDER BY date_field'
results = list(self.database.select(query, ModelWithEnum))
self.assertEqual(len(results), 2)
self.assertEqual(results[0].enum_field, Fruit.apple)
self.assertEqual(results[1].enum_field, Fruit.orange)
def test_ad_hoc_model(self):
self.database.insert([
ModelWithEnum(date_field='2016-08-30', enum_field=Fruit.apple),
ModelWithEnum(date_field='2016-08-31', enum_field=Fruit.orange)
])
query = 'SELECT * from $db.modelwithenum ORDER BY date_field'
results = list(self.database.select(query))
self.assertEqual(len(results), 2)
self.assertEqual(results[0].enum_field.name, Fruit.apple.name)
self.assertEqual(results[0].enum_field.value, Fruit.apple.value)
self.assertEqual(results[1].enum_field.name, Fruit.orange.name)
self.assertEqual(results[1].enum_field.value, Fruit.orange.value)
def test_conversion(self):
self.assertEqual(ModelWithEnum(enum_field=3).enum_field, Fruit.orange)
self.assertEqual(ModelWithEnum(enum_field='apple').enum_field, Fruit.apple)
self.assertEqual(ModelWithEnum(enum_field=Fruit.banana).enum_field, Fruit.banana)
def test_assignment_error(self):
for value in (0, 17, 'pear', '', None, 99.9):
with self.assertRaises(ValueError):
ModelWithEnum(enum_field=value)
def test_default_value(self):
instance = ModelWithEnum()
self.assertEqual(instance.enum_field, Fruit.apple)
def test_enum_array(self):
instance = ModelWithEnumArray(date_field='2016-08-30', enum_array=[Fruit.apple, Fruit.apple, Fruit.orange])
self.database.insert([instance])
query = 'SELECT * from $table ORDER BY date_field'
results = list(self.database.select(query, ModelWithEnumArray))
self.assertEqual(len(results), 1)
self.assertEqual(results[0].enum_array, instance.enum_array)
class MigrationsTestCase(unittest.TestCase):
def setUp(self):
self.database = Database('test-db', log_statements=True)
self.database.drop_table(MigrationHistory)
def tearDown(self):
self.database.drop_database()
def table_exists(self, model_class):
query = "EXISTS TABLE $db.`%s`" % model_class.table_name()
return next(self.database.select(query)).result == 1
def get_table_fields(self, model_class):
query = "DESC `%s`.`%s`" % (self.database.db_name,
model_class.table_name())
return [(row.name, row.type) for row in self.database.select(query)]
def get_table_def(self, model_class):
return self.database.raw('SHOW CREATE TABLE $db.`%s`' %
model_class.table_name())
def test_migrations(self):
# Creation and deletion of table
self.database.migrate('tests.sample_migrations', 1)
self.assertTrue(self.table_exists(Model1))
self.database.migrate('tests.sample_migrations', 2)
self.assertFalse(self.table_exists(Model1))
self.database.migrate('tests.sample_migrations', 3)
self.assertTrue(self.table_exists(Model1))
# Adding, removing and altering simple fields
self.assertEqual(self.get_table_fields(Model1), [('date', 'Date'),
('f1', 'Int32'),
('f2', 'String')])
self.database.migrate('tests.sample_migrations', 4)
self.assertEqual(self.get_table_fields(Model2),
[('date', 'Date'), ('f1', 'Int32'), ('f3', 'Float32'),
('f2', 'String'), ('f4', 'String'),
('f5', 'Array(UInt64)')])
self.database.migrate('tests.sample_migrations', 5)
self.assertEqual(self.get_table_fields(Model3), [('date', 'Date'),
('f1', 'Int64'),
('f3', 'Float64'),
('f4', 'String')])
# Altering enum fields
self.database.migrate('tests.sample_migrations', 6)
self.assertTrue(self.table_exists(EnumModel1))
self.assertEqual(self.get_table_fields(EnumModel1),
[('date', 'Date'),
('f1', "Enum8('dog' = 1, 'cat' = 2, 'cow' = 3)")])
self.database.migrate('tests.sample_migrations', 7)
self.assertTrue(self.table_exists(EnumModel1))
self.assertEqual(
self.get_table_fields(EnumModel2),
[('date', 'Date'),
('f1', "Enum16('dog' = 1, 'cat' = 2, 'horse' = 3, 'pig' = 4)")])
# Materialized fields and alias fields
self.database.migrate('tests.sample_migrations', 8)
self.assertTrue(self.table_exists(MaterializedModel))
self.assertEqual(self.get_table_fields(MaterializedModel),
[('date_time', "DateTime"), ('date', 'Date')])
self.database.migrate('tests.sample_migrations', 9)
self.assertTrue(self.table_exists(AliasModel))
self.assertEqual(self.get_table_fields(AliasModel),
[('date', 'Date'), ('date_alias', "Date")])
# Buffer models creation and alteration
self.database.migrate('tests.sample_migrations', 10)
self.assertTrue(self.table_exists(Model4))
self.assertTrue(self.table_exists(Model4Buffer))
self.assertEqual(self.get_table_fields(Model4), [('date', 'Date'),
('f1', 'Int32'),
('f2', 'String')])
self.assertEqual(self.get_table_fields(Model4Buffer),
[('date', 'Date'), ('f1', 'Int32'), ('f2', 'String')])
self.database.migrate('tests.sample_migrations', 11)
self.assertEqual(self.get_table_fields(Model4), [('date', 'Date'),
('f3', 'DateTime'),
('f2', 'String')])
self.assertEqual(self.get_table_fields(Model4Buffer),
[('date', 'Date'), ('f3', 'DateTime'),
('f2', 'String')])
self.database.migrate('tests.sample_migrations', 12)
self.assertEqual(self.database.count(Model3), 3)
data = [
item.f1 for item in self.database.select(
'SELECT f1 FROM $table ORDER BY f1', model_class=Model3)
]
self.assertListEqual(data, [1, 2, 3])
self.database.migrate('tests.sample_migrations', 13)
self.assertEqual(self.database.count(Model3), 4)
data = [
item.f1 for item in self.database.select(
'SELECT f1 FROM $table ORDER BY f1', model_class=Model3)
]
self.assertListEqual(data, [1, 2, 3, 4])
self.database.migrate('tests.sample_migrations', 14)
self.assertTrue(self.table_exists(MaterializedModel1))
self.assertEqual(self.get_table_fields(MaterializedModel1),
[('date_time', 'DateTime'), ('int_field', 'Int8'),
('date', 'Date'), ('int_field_plus_one', 'Int8')])
self.assertTrue(self.table_exists(AliasModel1))
self.assertEqual(self.get_table_fields(AliasModel1),
[('date', 'Date'), ('int_field', 'Int8'),
('date_alias', 'Date'),
('int_field_plus_one', 'Int8')])
# Codecs and low cardinality
self.database.migrate('tests.sample_migrations', 15)
self.assertTrue(self.table_exists(Model4_compressed))
if self.database.has_low_cardinality_support:
self.assertEqual(self.get_table_fields(Model2LowCardinality),
[('date', 'Date'),
('f1', 'LowCardinality(Int32)'),
('f3', 'LowCardinality(Float32)'),
('f2', 'LowCardinality(String)'),
('f4', 'LowCardinality(Nullable(String))'),
('f5', 'Array(LowCardinality(UInt64))')])
else:
logging.warning('No support for low cardinality')
self.assertEqual(self.get_table_fields(Model2),
[('date', 'Date'), ('f1', 'Int32'),
('f3', 'Float32'), ('f2', 'String'),
('f4', 'Nullable(String)'),
('f5', 'Array(UInt64)')])
if self.database.server_version >= (19, 14, 3, 3):
# Creating constraints
self.database.migrate('tests.sample_migrations', 16)
self.assertTrue(self.table_exists(ModelWithConstraints))
self.database.insert([ModelWithConstraints(f1=101, f2='a')])
with self.assertRaises(ServerError):
self.database.insert([ModelWithConstraints(f1=99, f2='a')])
with self.assertRaises(ServerError):
self.database.insert([ModelWithConstraints(f1=101, f2='x')])
# Modifying constraints
self.database.migrate('tests.sample_migrations', 17)
self.database.insert([ModelWithConstraints(f1=99, f2='a')])
with self.assertRaises(ServerError):
self.database.insert([ModelWithConstraints(f1=101, f2='a')])
with self.assertRaises(ServerError):
self.database.insert([ModelWithConstraints(f1=99, f2='x')])
if self.database.server_version >= (20, 1, 2, 4):
# Creating indexes
self.database.migrate('tests.sample_migrations', 18)
self.assertTrue(self.table_exists(ModelWithIndex))
self.assertIn('INDEX index ', self.get_table_def(ModelWithIndex))
self.assertIn('INDEX another_index ',
self.get_table_def(ModelWithIndex))
# Modifying indexes
self.database.migrate('tests.sample_migrations', 19)
self.assertNotIn('INDEX index ',
self.get_table_def(ModelWithIndex))
self.assertIn('INDEX index2 ', self.get_table_def(ModelWithIndex))
self.assertIn('INDEX another_index ',
self.get_table_def(ModelWithIndex))
from infi.clickhouse_orm import models, fields, engines
from infi.clickhouse_orm.database import Database
class Test(models.Model):
id = fields.Int64Field()
a = fields.StringField()
b = fields.StringField()
c = fields.StringField()
d = fields.StringField()
engine = engines.MergeTree('id', ('a', 'b', 'c', 'd'))
db_url = 'http://web1:8123'
db_name = 'csv_parser_db'
db_username = '******'
db_password = '******'
db = Database(db_name=db_name, db_url=db_url, username=db_username, password=db_password)
db.create_table(Test)
# Insert some data
db.insert([
Test(id=i, a=str(i), b=str(i), c=str(i), d=str(i)) for i in xrange(10, 15)
])
# Read data
for row in db.select("SELECT * FROM {}.test".format(db_name), model_class=Test):
print row.id, row.a, row.b, row.c, row.d
class DatabaseTestCase(unittest.TestCase):
def setUp(self):
self.database = Database('test_db')
self.database.create_table(Person)
def tearDown(self):
self.database.drop_table(Person)
self.database.drop_database()
def _insert_and_check(self, data, count):
self.database.insert(data)
self.assertEquals(count, self.database.count(Person))
def test_insert__generator(self):
self._insert_and_check(self._sample_data(), len(data))
def test_insert__list(self):
self._insert_and_check(list(self._sample_data()), len(data))
def test_insert__iterator(self):
self._insert_and_check(iter(self._sample_data()), len(data))
def test_insert__empty(self):
self._insert_and_check([], 0)
def test_count(self):
self.database.insert(self._sample_data())
self.assertEquals(self.database.count(Person), 100)
self.assertEquals(self.database.count(Person, "first_name = 'Courtney'"), 2)
self.assertEquals(self.database.count(Person, "birthday > '2000-01-01'"), 22)
self.assertEquals(self.database.count(Person, "birthday < '1970-03-01'"), 0)
def test_select(self):
self._insert_and_check(self._sample_data(), len(data))
query = "SELECT * FROM test_db.person WHERE first_name = 'Whitney' ORDER BY last_name"
results = list(self.database.select(query, Person))
self.assertEquals(len(results), 2)
self.assertEquals(results[0].last_name, 'Durham')
self.assertEquals(results[0].height, 1.72)
self.assertEquals(results[1].last_name, 'Scott')
self.assertEquals(results[1].height, 1.70)
def test_select_partial_fields(self):
self._insert_and_check(self._sample_data(), len(data))
query = "SELECT first_name, last_name FROM test_db.person WHERE first_name = 'Whitney' ORDER BY last_name"
results = list(self.database.select(query, Person))
self.assertEquals(len(results), 2)
self.assertEquals(results[0].last_name, 'Durham')
self.assertEquals(results[0].height, 0) # default value
self.assertEquals(results[1].last_name, 'Scott')
self.assertEquals(results[1].height, 0) # default value
def test_select_ad_hoc_model(self):
self._insert_and_check(self._sample_data(), len(data))
query = "SELECT * FROM test_db.person WHERE first_name = 'Whitney' ORDER BY last_name"
results = list(self.database.select(query))
self.assertEquals(len(results), 2)
self.assertEquals(results[0].__class__.__name__, 'AdHocModel')
self.assertEquals(results[0].last_name, 'Durham')
self.assertEquals(results[0].height, 1.72)
self.assertEquals(results[1].last_name, 'Scott')
self.assertEquals(results[1].height, 1.70)
def _sample_data(self):
for entry in data:
yield Person(**entry)
class EnginesTestCase(unittest.TestCase):
def setUp(self):
self.database = Database('test-db')
def tearDown(self):
self.database.drop_database()
def _create_and_insert(self, model_class):
self.database.create_table(model_class)
self.database.insert([
model_class(date='2017-01-01',
event_id=23423,
event_group=13,
event_count=7,
event_version=1)
])
def test_merge_tree(self):
class TestModel(SampleModel):
engine = MergeTree('date', ('date', 'event_id', 'event_group'))
self._create_and_insert(TestModel)
def test_merge_tree_with_sampling(self):
class TestModel(SampleModel):
engine = MergeTree('date', ('date', 'event_id', 'event_group'),
sampling_expr='intHash32(event_id)')
self._create_and_insert(TestModel)
def test_merge_tree_with_granularity(self):
class TestModel(SampleModel):
engine = MergeTree('date', ('date', 'event_id', 'event_group'),
index_granularity=4096)
self._create_and_insert(TestModel)
def test_replicated_merge_tree(self):
engine = MergeTree(
'date', ('date', 'event_id', 'event_group'),
replica_table_path='/clickhouse/tables/{layer}-{shard}/hits',
replica_name='{replica}')
expected = "ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/hits', '{replica}', date, (date, event_id, event_group), 8192)"
self.assertEquals(engine.create_table_sql(), expected)
def test_collapsing_merge_tree(self):
class TestModel(SampleModel):
engine = CollapsingMergeTree('date',
('date', 'event_id', 'event_group'),
'event_version')
self._create_and_insert(TestModel)
def test_summing_merge_tree(self):
class TestModel(SampleModel):
engine = SummingMergeTree('date', ('date', 'event_group'),
('event_count', ))
self._create_and_insert(TestModel)
def test_replacing_merge_tree(self):
class TestModel(SampleModel):
engine = ReplacingMergeTree('date',
('date', 'event_id', 'event_group'),
'event_uversion')
self._create_and_insert(TestModel)
def test_tiny_log(self):
class TestModel(SampleModel):
engine = TinyLog()
self._create_and_insert(TestModel)
def test_log(self):
class TestModel(SampleModel):
engine = Log()
self._create_and_insert(TestModel)
def test_memory(self):
class TestModel(SampleModel):
engine = Memory()
self._create_and_insert(TestModel)
def test_merge(self):
class TestModel1(SampleModel):
engine = TinyLog()
class TestModel2(SampleModel):
engine = TinyLog()
class TestMergeModel(MergeModel, SampleModel):
engine = Merge('^testmodel')
self.database.create_table(TestModel1)
self.database.create_table(TestModel2)
self.database.create_table(TestMergeModel)
# Insert operations are restricted for this model type
with self.assertRaises(DatabaseException):
self.database.insert([
TestMergeModel(date='2017-01-01',
event_id=23423,
event_group=13,
event_count=7,
event_version=1)
])
# Testing select
self.database.insert([
TestModel1(date='2017-01-01',
event_id=1,
event_group=1,
event_count=1,
event_version=1)
])
self.database.insert([
TestModel2(date='2017-01-02',
event_id=2,
event_group=2,
event_count=2,
event_version=2)
])
# event_uversion is materialized field. So * won't select it and it will be zero
res = self.database.select(
'SELECT *, event_uversion FROM $table ORDER BY event_id',
model_class=TestMergeModel)
res = [row for row in res]
self.assertEqual(2, len(res))
self.assertDictEqual(
{
'_table': 'testmodel1',
'date': datetime.date(2017, 1, 1),
'event_id': 1,
'event_group': 1,
'event_count': 1,
'event_version': 1,
'event_uversion': 1
}, res[0].to_dict(include_readonly=True))
self.assertDictEqual(
{
'_table': 'testmodel2',
'date': datetime.date(2017, 1, 2),
'event_id': 2,
'event_group': 2,
'event_count': 2,
'event_version': 2,
'event_uversion': 2
}, res[1].to_dict(include_readonly=True))
class NullableFieldsTest(unittest.TestCase):
def setUp(self):
self.database = Database('test-db')
self.database.create_table(ModelWithNullable)
def tearDown(self):
self.database.drop_database()
def test_nullable_datetime_field(self):
f = NullableField(DateTimeField())
epoch = datetime(1970, 1, 1, tzinfo=pytz.utc)
# Valid values
for value in (date(1970, 1, 1),
datetime(1970, 1, 1),
epoch,
epoch.astimezone(pytz.timezone('US/Eastern')),
epoch.astimezone(pytz.timezone('Asia/Jerusalem')),
'1970-01-01 00:00:00',
'1970-01-17 00:00:17',
'0000-00-00 00:00:00',
0,
'\\N'):
dt = f.to_python(value, pytz.utc)
if value == '\\N':
self.assertIsNone(dt)
else:
self.assertEqual(dt.tzinfo, pytz.utc)
# Verify that conversion to and from db string does not change value
dt2 = f.to_python(f.to_db_string(dt, quote=False), pytz.utc)
self.assertEqual(dt, dt2)
# Invalid values
for value in ('nope', '21/7/1999', 0.5):
with self.assertRaises(ValueError):
f.to_python(value, pytz.utc)
def test_nullable_uint8_field(self):
f = NullableField(UInt8Field())
# Valid values
for value in (17, '17', 17.0, '\\N'):
python_value = f.to_python(value, pytz.utc)
if value == '\\N':
self.assertIsNone(python_value)
self.assertEqual(value, f.to_db_string(python_value))
else:
self.assertEqual(python_value, 17)
# Invalid values
for value in ('nope', date.today()):
with self.assertRaises(ValueError):
f.to_python(value, pytz.utc)
def test_nullable_string_field(self):
f = NullableField(StringField())
# Valid values
for value in ('\\\\N', 'N', 'some text', '\\N'):
python_value = f.to_python(value, pytz.utc)
if value == '\\N':
self.assertIsNone(python_value)
self.assertEqual(value, f.to_db_string(python_value))
else:
self.assertEqual(python_value, value)
def test_isinstance(self):
for field in (StringField, UInt8Field, Float32Field, DateTimeField):
f = NullableField(field())
self.assertTrue(f.isinstance(field))
self.assertTrue(f.isinstance(NullableField))
for field in (Int8Field, Int16Field, Int32Field, Int64Field, UInt8Field, UInt16Field, UInt32Field, UInt64Field):
f = NullableField(field())
self.assertTrue(f.isinstance(BaseIntField))
for field in (Float32Field, Float64Field):
f = NullableField(field())
self.assertTrue(f.isinstance(BaseFloatField))
f = NullableField(NullableField(UInt32Field()))
self.assertTrue(f.isinstance(BaseIntField))
self.assertTrue(f.isinstance(NullableField))
self.assertFalse(f.isinstance(BaseFloatField))
def _insert_sample_data(self):
dt = date(1970, 1, 1)
self.database.insert([
ModelWithNullable(date_field='2016-08-30', null_str='', null_int=42, null_date=dt),
ModelWithNullable(date_field='2016-08-30', null_str='nothing', null_int=None, null_date=None),
ModelWithNullable(date_field='2016-08-31', null_str=None, null_int=42, null_date=dt),
ModelWithNullable(date_field='2016-08-31', null_str=None, null_int=None, null_date=None)
])
def _assert_sample_data(self, results):
dt = date(1970, 1, 1)
self.assertEqual(len(results), 4)
self.assertIsNone(results[0].null_str)
self.assertEqual(results[0].null_int, 42)
self.assertEqual(results[0].null_date, dt)
self.assertIsNone(results[1].null_date)
self.assertEqual(results[1].null_str, 'nothing')
self.assertIsNone(results[1].null_date)
self.assertIsNone(results[2].null_str)
self.assertEqual(results[2].null_date, dt)
self.assertEqual(results[2].null_int, 42)
self.assertIsNone(results[3].null_int)
self.assertIsNone(results[3].null_str)
self.assertIsNone(results[3].null_date)
def test_insert_and_select(self):
self._insert_sample_data()
query = 'SELECT * from $table ORDER BY date_field'
results = list(self.database.select(query, ModelWithNullable))
self._assert_sample_data(results)
def test_ad_hoc_model(self):
self._insert_sample_data()
query = 'SELECT * from $db.modelwithnullable ORDER BY date_field'
results = list(self.database.select(query))
self._assert_sample_data(results)
class DatabaseTestCase(unittest.TestCase):
def setUp(self):
self.database = Database('test-db')
self.database.create_table(Person)
def tearDown(self):
self.database.drop_table(Person)
self.database.drop_database()
def _insert_and_check(self, data, count):
self.database.insert(data)
self.assertEquals(count, self.database.count(Person))
def test_insert__generator(self):
self._insert_and_check(self._sample_data(), len(data))
def test_insert__list(self):
self._insert_and_check(list(self._sample_data()), len(data))
def test_insert__iterator(self):
self._insert_and_check(iter(self._sample_data()), len(data))
def test_insert__empty(self):
self._insert_and_check([], 0)
def test_count(self):
self.database.insert(self._sample_data())
self.assertEquals(self.database.count(Person), 100)
self.assertEquals(self.database.count(Person, "first_name = 'Courtney'"), 2)
self.assertEquals(self.database.count(Person, "birthday > '2000-01-01'"), 22)
self.assertEquals(self.database.count(Person, "birthday < '1970-03-01'"), 0)
def test_select(self):
self._insert_and_check(self._sample_data(), len(data))
query = "SELECT * FROM `test-db`.person WHERE first_name = 'Whitney' ORDER BY last_name"
results = list(self.database.select(query, Person))
self.assertEquals(len(results), 2)
self.assertEquals(results[0].last_name, 'Durham')
self.assertEquals(results[0].height, 1.72)
self.assertEquals(results[1].last_name, 'Scott')
self.assertEquals(results[1].height, 1.70)
def test_select_partial_fields(self):
self._insert_and_check(self._sample_data(), len(data))
query = "SELECT first_name, last_name FROM `test-db`.person WHERE first_name = 'Whitney' ORDER BY last_name"
results = list(self.database.select(query, Person))
self.assertEquals(len(results), 2)
self.assertEquals(results[0].last_name, 'Durham')
self.assertEquals(results[0].height, 0) # default value
self.assertEquals(results[1].last_name, 'Scott')
self.assertEquals(results[1].height, 0) # default value
def test_select_ad_hoc_model(self):
self._insert_and_check(self._sample_data(), len(data))
query = "SELECT * FROM `test-db`.person WHERE first_name = 'Whitney' ORDER BY last_name"
results = list(self.database.select(query))
self.assertEquals(len(results), 2)
self.assertEquals(results[0].__class__.__name__, 'AdHocModel')
self.assertEquals(results[0].last_name, 'Durham')
self.assertEquals(results[0].height, 1.72)
self.assertEquals(results[1].last_name, 'Scott')
self.assertEquals(results[1].height, 1.70)
def test_pagination(self):
self._insert_and_check(self._sample_data(), len(data))
# Try different page sizes
for page_size in (1, 2, 7, 10, 30, 100, 150):
# Iterate over pages and collect all intances
page_num = 1
instances = set()
while True:
page = self.database.paginate(Person, 'first_name, last_name', page_num, page_size)
self.assertEquals(page.number_of_objects, len(data))
self.assertGreater(page.pages_total, 0)
[instances.add(obj.to_tsv()) for obj in page.objects]
if page.pages_total == page_num:
break
page_num += 1
# Verify that all instances were returned
self.assertEquals(len(instances), len(data))
def _sample_data(self):
for entry in data:
yield Person(**entry)
class DecimalFieldsTest(unittest.TestCase):
def setUp(self):
self.database = Database('test-db', log_statements=True)
try:
self.database.create_table(DecimalModel)
except ServerError as e:
# This ClickHouse version does not support decimals yet
raise unittest.SkipTest(e.message)
def tearDown(self):
self.database.drop_database()
def _insert_sample_data(self):
self.database.insert([
DecimalModel(date_field='2016-08-20'),
DecimalModel(date_field='2016-08-21', dec=Decimal('1.234')),
DecimalModel(date_field='2016-08-22', dec32=Decimal('12342.2345')),
DecimalModel(date_field='2016-08-23', dec64=Decimal('12342.23456')),
DecimalModel(date_field='2016-08-24', dec128=Decimal('-4545456612342.234567')),
])
def _assert_sample_data(self, results):
self.assertEqual(len(results), 5)
self.assertEqual(results[0].dec, Decimal(0))
self.assertEqual(results[0].dec32, Decimal(17))
self.assertEqual(results[1].dec, Decimal('1.234'))
self.assertEqual(results[2].dec32, Decimal('12342.2345'))
self.assertEqual(results[3].dec64, Decimal('12342.23456'))
self.assertEqual(results[4].dec128, Decimal('-4545456612342.234567'))
def test_insert_and_select(self):
self._insert_sample_data()
query = 'SELECT * from $table ORDER BY date_field'
results = list(self.database.select(query, DecimalModel))
self._assert_sample_data(results)
def test_ad_hoc_model(self):
self._insert_sample_data()
query = 'SELECT * from decimalmodel ORDER BY date_field'
results = list(self.database.select(query))
self._assert_sample_data(results)
def test_rounding(self):
d = Decimal('11111.2340000000000000001')
self.database.insert([DecimalModel(date_field='2016-08-20', dec=d, dec32=d, dec64=d, dec128=d)])
m = DecimalModel.objects_in(self.database)[0]
for val in (m.dec, m.dec32, m.dec64, m.dec128):
self.assertEqual(val, Decimal('11111.234'))
def test_assignment_ok(self):
for value in (True, False, 17, 3.14, '20.5', Decimal('20.5')):
DecimalModel(dec=value)
def test_assignment_error(self):
for value in ('abc', u'זה ארוך', None, float('NaN'), Decimal('-Infinity')):
with self.assertRaises(ValueError):
DecimalModel(dec=value)
def test_aggregation(self):
self._insert_sample_data()
result = DecimalModel.objects_in(self.database).aggregate(m='min(dec)', n='max(dec)')
self.assertEqual(result[0].m, Decimal(0))
self.assertEqual(result[0].n, Decimal('1.234'))
def test_precision_and_scale(self):
# Go over all valid combinations
for precision in range(1, 39):
for scale in range(0, precision + 1):
f = DecimalField(precision, scale)
# Some invalid combinations
for precision, scale in [(0, 0), (-1, 7), (7, -1), (39, 5), (20, 21)]:
with self.assertRaises(AssertionError):
f = DecimalField(precision, scale)
def test_min_max(self):
# In range
f = DecimalField(3, 1)
f.validate(f.to_python('99.9', None))
f.validate(f.to_python('-99.9', None))
# In range after rounding
f.validate(f.to_python('99.94', None))
f.validate(f.to_python('-99.94', None))
# Out of range
with self.assertRaises(ValueError):
f.validate(f.to_python('99.99', None))
with self.assertRaises(ValueError):
f.validate(f.to_python('-99.99', None))
# In range
f = Decimal32Field(4)
f.validate(f.to_python('99999.9999', None))
f.validate(f.to_python('-99999.9999', None))
# In range after rounding
f.validate(f.to_python('99999.99994', None))
f.validate(f.to_python('-99999.99994', None))
# Out of range
with self.assertRaises(ValueError):
f.validate(f.to_python('100000', None))
with self.assertRaises(ValueError):
f.validate(f.to_python('-100000', None))
class NullableFieldsTest(unittest.TestCase):
def setUp(self):
self.database = Database('test-db')
self.database.create_table(ModelWithNullable)
def tearDown(self):
self.database.drop_database()
def test_nullable_datetime_field(self):
f = NullableField(DateTimeField())
epoch = datetime(1970, 1, 1, tzinfo=pytz.utc)
# Valid values
for value in (date(1970, 1, 1), datetime(1970, 1, 1), epoch,
epoch.astimezone(pytz.timezone('US/Eastern')),
epoch.astimezone(pytz.timezone('Asia/Jerusalem')),
'1970-01-01 00:00:00', '1970-01-17 00:00:17',
'0000-00-00 00:00:00', 0, '\\N'):
dt = f.to_python(value, pytz.utc)
if value == '\\N':
self.assertIsNone(dt)
else:
self.assertEqual(dt.tzinfo, pytz.utc)
# Verify that conversion to and from db string does not change value
dt2 = f.to_python(f.to_db_string(dt, quote=False), pytz.utc)
self.assertEqual(dt, dt2)
# Invalid values
for value in ('nope', '21/7/1999', 0.5):
with self.assertRaises(ValueError):
f.to_python(value, pytz.utc)
def test_nullable_uint8_field(self):
f = NullableField(UInt8Field())
# Valid values
for value in (17, '17', 17.0, '\\N'):
python_value = f.to_python(value, pytz.utc)
if value == '\\N':
self.assertIsNone(python_value)
self.assertEqual(value, f.to_db_string(python_value))
else:
self.assertEqual(python_value, 17)
# Invalid values
for value in ('nope', date.today()):
with self.assertRaises(ValueError):
f.to_python(value, pytz.utc)
def test_nullable_string_field(self):
f = NullableField(StringField())
# Valid values
for value in ('\\\\N', 'N', 'some text', '\\N'):
python_value = f.to_python(value, pytz.utc)
if value == '\\N':
self.assertIsNone(python_value)
self.assertEqual(value, f.to_db_string(python_value))
else:
self.assertEqual(python_value, value)
def test_isinstance(self):
for field in (StringField, UInt8Field, Float32Field, DateTimeField):
f = NullableField(field())
self.assertTrue(f.isinstance(field))
self.assertTrue(f.isinstance(NullableField))
for field in (Int8Field, Int16Field, Int32Field, Int64Field,
UInt8Field, UInt16Field, UInt32Field, UInt64Field):
f = NullableField(field())
self.assertTrue(f.isinstance(BaseIntField))
for field in (Float32Field, Float64Field):
f = NullableField(field())
self.assertTrue(f.isinstance(BaseFloatField))
f = NullableField(NullableField(UInt32Field()))
self.assertTrue(f.isinstance(BaseIntField))
self.assertTrue(f.isinstance(NullableField))
self.assertFalse(f.isinstance(BaseFloatField))
def _insert_sample_data(self):
dt = date(1970, 1, 1)
self.database.insert([
ModelWithNullable(date_field='2016-08-30',
null_str='',
null_int=42,
null_date=dt),
ModelWithNullable(date_field='2016-08-30',
null_str='nothing',
null_int=None,
null_date=None),
ModelWithNullable(date_field='2016-08-31',
null_str=None,
null_int=42,
null_date=dt),
ModelWithNullable(date_field='2016-08-31',
null_str=None,
null_int=None,
null_date=None)
])
def _assert_sample_data(self, results):
dt = date(1970, 1, 1)
self.assertEqual(len(results), 4)
self.assertIsNone(results[0].null_str)
self.assertEqual(results[0].null_int, 42)
self.assertEqual(results[0].null_date, dt)
self.assertIsNone(results[1].null_date)
self.assertEqual(results[1].null_str, 'nothing')
self.assertIsNone(results[1].null_date)
self.assertIsNone(results[2].null_str)
self.assertEqual(results[2].null_date, dt)
self.assertEqual(results[2].null_int, 42)
self.assertIsNone(results[3].null_int)
self.assertIsNone(results[3].null_str)
self.assertIsNone(results[3].null_date)
def test_insert_and_select(self):
self._insert_sample_data()
query = 'SELECT * from $table ORDER BY date_field'
results = list(self.database.select(query, ModelWithNullable))
self._assert_sample_data(results)
def test_ad_hoc_model(self):
self._insert_sample_data()
query = 'SELECT * from $db.modelwithnullable ORDER BY date_field'
results = list(self.database.select(query))
self._assert_sample_data(results)
class EnginesTestCase(unittest.TestCase):
def setUp(self):
self.database = Database('test-db')
def tearDown(self):
self.database.drop_database()
def _create_and_insert(self, model_class):
self.database.create_table(model_class)
self.database.insert([
model_class(date='2017-01-01', event_id=23423, event_group=13, event_count=7, event_version=1)
])
def test_merge_tree(self):
class TestModel(SampleModel):
engine = MergeTree('date', ('date', 'event_id', 'event_group'))
self._create_and_insert(TestModel)
def test_merge_tree_with_sampling(self):
class TestModel(SampleModel):
engine = MergeTree('date', ('date', 'event_id', 'event_group'), sampling_expr='intHash32(event_id)')
self._create_and_insert(TestModel)
def test_merge_tree_with_granularity(self):
class TestModel(SampleModel):
engine = MergeTree('date', ('date', 'event_id', 'event_group'), index_granularity=4096)
self._create_and_insert(TestModel)
def test_replicated_merge_tree(self):
engine = MergeTree('date', ('date', 'event_id', 'event_group'), replica_table_path='/clickhouse/tables/{layer}-{shard}/hits', replica_name='{replica}')
expected = "ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/hits', '{replica}', date, (date, event_id, event_group), 8192)"
self.assertEquals(engine.create_table_sql(), expected)
def test_collapsing_merge_tree(self):
class TestModel(SampleModel):
engine = CollapsingMergeTree('date', ('date', 'event_id', 'event_group'), 'event_version')
self._create_and_insert(TestModel)
def test_summing_merge_tree(self):
class TestModel(SampleModel):
engine = SummingMergeTree('date', ('date', 'event_group'), ('event_count',))
self._create_and_insert(TestModel)
def test_replacing_merge_tree(self):
class TestModel(SampleModel):
engine = ReplacingMergeTree('date', ('date', 'event_id', 'event_group'), 'event_uversion')
self._create_and_insert(TestModel)
def test_tiny_log(self):
class TestModel(SampleModel):
engine = TinyLog()
self._create_and_insert(TestModel)
def test_log(self):
class TestModel(SampleModel):
engine = Log()
self._create_and_insert(TestModel)
def test_memory(self):
class TestModel(SampleModel):
engine = Memory()
self._create_and_insert(TestModel)
class DatabaseTestCase(unittest.TestCase):
def setUp(self):
self.database = Database('test-db')
self.database.create_table(Person)
def tearDown(self):
self.database.drop_table(Person)
self.database.drop_database()
def _insert_and_check(self, data, count):
self.database.insert(data)
self.assertEquals(count, self.database.count(Person))
def test_insert__generator(self):
self._insert_and_check(self._sample_data(), len(data))
def test_insert__list(self):
self._insert_and_check(list(self._sample_data()), len(data))
def test_insert__iterator(self):
self._insert_and_check(iter(self._sample_data()), len(data))
def test_insert__empty(self):
self._insert_and_check([], 0)
def test_count(self):
self.database.insert(self._sample_data())
self.assertEquals(self.database.count(Person), 100)
self.assertEquals(self.database.count(Person, "first_name = 'Courtney'"), 2)
self.assertEquals(self.database.count(Person, "birthday > '2000-01-01'"), 22)
self.assertEquals(self.database.count(Person, "birthday < '1970-03-01'"), 0)
def test_select(self):
self._insert_and_check(self._sample_data(), len(data))
query = "SELECT * FROM `test-db`.person WHERE first_name = 'Whitney' ORDER BY last_name"
results = list(self.database.select(query, Person))
self.assertEquals(len(results), 2)
self.assertEquals(results[0].last_name, 'Durham')
self.assertEquals(results[0].height, 1.72)
self.assertEquals(results[1].last_name, 'Scott')
self.assertEquals(results[1].height, 1.70)
def test_select_partial_fields(self):
self._insert_and_check(self._sample_data(), len(data))
query = "SELECT first_name, last_name FROM `test-db`.person WHERE first_name = 'Whitney' ORDER BY last_name"
results = list(self.database.select(query, Person))
self.assertEquals(len(results), 2)
self.assertEquals(results[0].last_name, 'Durham')
self.assertEquals(results[0].height, 0) # default value
self.assertEquals(results[1].last_name, 'Scott')
self.assertEquals(results[1].height, 0) # default value
def test_select_ad_hoc_model(self):
self._insert_and_check(self._sample_data(), len(data))
query = "SELECT * FROM `test-db`.person WHERE first_name = 'Whitney' ORDER BY last_name"
results = list(self.database.select(query))
self.assertEquals(len(results), 2)
self.assertEquals(results[0].__class__.__name__, 'AdHocModel')
self.assertEquals(results[0].last_name, 'Durham')
self.assertEquals(results[0].height, 1.72)
self.assertEquals(results[1].last_name, 'Scott')
self.assertEquals(results[1].height, 1.70)
def test_pagination(self):
self._insert_and_check(self._sample_data(), len(data))
# Try different page sizes
for page_size in (1, 2, 7, 10, 30, 100, 150):
# Iterate over pages and collect all intances
page_num = 1
instances = set()
while True:
page = self.database.paginate(Person, 'first_name, last_name', page_num, page_size)
self.assertEquals(page.number_of_objects, len(data))
self.assertGreater(page.pages_total, 0)
[instances.add(obj.to_tsv()) for obj in page.objects]
if page.pages_total == page_num:
break
page_num += 1
# Verify that all instances were returned
self.assertEquals(len(instances), len(data))
def test_pagination_last_page(self):
self._insert_and_check(self._sample_data(), len(data))
# Try different page sizes
for page_size in (1, 2, 7, 10, 30, 100, 150):
# Ask for the last page in two different ways and verify equality
page_a = self.database.paginate(Person, 'first_name, last_name', -1, page_size)
page_b = self.database.paginate(Person, 'first_name, last_name', page_a.pages_total, page_size)
self.assertEquals(page_a[1:], page_b[1:])
self.assertEquals([obj.to_tsv() for obj in page_a.objects],
[obj.to_tsv() for obj in page_b.objects])
def test_pagination_invalid_page(self):
self._insert_and_check(self._sample_data(), len(data))
for page_num in (0, -2, -100):
with self.assertRaises(ValueError):
self.database.paginate(Person, 'first_name, last_name', page_num, 100)
def test_special_chars(self):
s = u'אבגד \\\'"`,.;éåäöšž\n\t\0\b\r'
p = Person(first_name=s)
self.database.insert([p])
p = list(self.database.select("SELECT * from $table", Person))[0]
self.assertEquals(p.first_name, s)
def _sample_data(self):
for entry in data:
yield Person(**entry)
class SystemPartTest(unittest.TestCase):
BACKUP_DIRS = ['/var/lib/clickhouse/shadow', '/opt/clickhouse/shadow/']
def setUp(self):
self.database = Database('test-db')
self.database.create_table(TestTable)
self.database.create_table(CustomPartitionedTable)
self.database.insert([TestTable(date_field=date.today())])
self.database.insert([CustomPartitionedTable(date_field=date.today(), group_field=13)])
def tearDown(self):
self.database.drop_database()
def _get_backups(self):
for dir in self.BACKUP_DIRS:
if os.path.exists(dir):
_, dirnames, _ = next(os.walk(dir))
return dirnames
raise unittest.SkipTest('Cannot find backups dir')
def test_get_all(self):
parts = SystemPart.get(self.database)
self.assertEqual(len(list(parts)), 2)
def test_get_active(self):
parts = list(SystemPart.get_active(self.database))
self.assertEqual(len(parts), 2)
parts[0].detach()
self.assertEqual(len(list(SystemPart.get_active(self.database))), 1)
def test_get_conditions(self):
parts = list(SystemPart.get(self.database, conditions="table='testtable'"))
self.assertEqual(len(parts), 1)
parts = list(SystemPart.get(self.database, conditions=u"table='custompartitionedtable'"))
self.assertEqual(len(parts), 1)
parts = list(SystemPart.get(self.database, conditions=u"table='invalidtable'"))
self.assertEqual(len(parts), 0)
def test_attach_detach(self):
parts = list(SystemPart.get_active(self.database))
self.assertEqual(len(parts), 2)
for p in parts:
p.detach()
self.assertEqual(len(list(SystemPart.get_active(self.database))), 0)
for p in parts:
p.attach()
self.assertEqual(len(list(SystemPart.get_active(self.database))), 2)
def test_drop(self):
parts = list(SystemPart.get_active(self.database))
for p in parts:
p.drop()
self.assertEqual(len(list(SystemPart.get_active(self.database))), 0)
def test_freeze(self):
parts = list(SystemPart.get(self.database))
# There can be other backups in the folder
prev_backups = set(self._get_backups())
for p in parts:
p.freeze()
backups = set(self._get_backups())
self.assertEqual(len(backups), len(prev_backups) + 2)
def test_fetch(self):
# TODO Not tested, as I have no replication set
pass
class DecimalFieldsTest(unittest.TestCase):
def setUp(self):
self.database = Database('test-db')
self.database.add_setting('allow_experimental_decimal_type', 1)
try:
self.database.create_table(DecimalModel)
except ServerError as e:
if 'Unknown setting' in e.message:
# This ClickHouse version does not support decimals yet
raise unittest.SkipTest(e.message)
else:
raise
def tearDown(self):
self.database.drop_database()
def _insert_sample_data(self):
self.database.insert([
DecimalModel(date_field='2016-08-20'),
DecimalModel(date_field='2016-08-21', dec=Decimal('1.234')),
DecimalModel(date_field='2016-08-22', dec32=Decimal('12342.2345')),
DecimalModel(date_field='2016-08-23', dec64=Decimal('12342.23456')),
DecimalModel(date_field='2016-08-24', dec128=Decimal('-4545456612342.234567')),
])
def _assert_sample_data(self, results):
self.assertEqual(len(results), 5)
self.assertEqual(results[0].dec, Decimal(0))
self.assertEqual(results[0].dec32, Decimal(17))
self.assertEqual(results[1].dec, Decimal('1.234'))
self.assertEqual(results[2].dec32, Decimal('12342.2345'))
self.assertEqual(results[3].dec64, Decimal('12342.23456'))
self.assertEqual(results[4].dec128, Decimal('-4545456612342.234567'))
def test_insert_and_select(self):
self._insert_sample_data()
query = 'SELECT * from $table ORDER BY date_field'
results = list(self.database.select(query, DecimalModel))
self._assert_sample_data(results)
def test_ad_hoc_model(self):
self._insert_sample_data()
query = 'SELECT * from decimalmodel ORDER BY date_field'
results = list(self.database.select(query))
self._assert_sample_data(results)
def test_rounding(self):
d = Decimal('11111.2340000000000000001')
self.database.insert([DecimalModel(date_field='2016-08-20', dec=d, dec32=d, dec64=d, dec128=d)])
m = DecimalModel.objects_in(self.database)[0]
for val in (m.dec, m.dec32, m.dec64, m.dec128):
self.assertEqual(val, Decimal('11111.234'))
def test_assignment_ok(self):
for value in (True, False, 17, 3.14, '20.5', Decimal('20.5')):
DecimalModel(dec=value)
def test_assignment_error(self):
for value in ('abc', u'זה ארוך', None, float('NaN'), Decimal('-Infinity')):
with self.assertRaises(ValueError):
DecimalModel(dec=value)
def test_aggregation(self):
self._insert_sample_data()
result = DecimalModel.objects_in(self.database).aggregate(m='min(dec)', n='max(dec)')
self.assertEqual(result[0].m, Decimal(0))
self.assertEqual(result[0].n, Decimal('1.234'))
def test_precision_and_scale(self):
# Go over all valid combinations
for precision in range(1, 39):
for scale in range(0, precision + 1):
f = DecimalField(precision, scale)
# Some invalid combinations
for precision, scale in [(0, 0), (-1, 7), (7, -1), (39, 5), (20, 21)]:
with self.assertRaises(AssertionError):
f = DecimalField(precision, scale)
def test_min_max(self):
# In range
f = DecimalField(3, 1)
f.validate(f.to_python('99.9', None))
f.validate(f.to_python('-99.9', None))
# In range after rounding
f.validate(f.to_python('99.94', None))
f.validate(f.to_python('-99.94', None))
# Out of range
with self.assertRaises(ValueError):
f.validate(f.to_python('99.99', None))
with self.assertRaises(ValueError):
f.validate(f.to_python('-99.99', None))
# In range
f = Decimal32Field(4)
f.validate(f.to_python('99999.9999', None))
f.validate(f.to_python('-99999.9999', None))
# In range after rounding
f.validate(f.to_python('99999.99994', None))
f.validate(f.to_python('-99999.99994', None))
# Out of range
with self.assertRaises(ValueError):
f.validate(f.to_python('100000', None))
with self.assertRaises(ValueError):
f.validate(f.to_python('-100000', None))
class SystemPartTest(unittest.TestCase):
BACKUP_DIRS = ['/var/lib/clickhouse/shadow', '/opt/clickhouse/shadow/']
def setUp(self):
self.database = Database('test-db')
self.database.create_table(TestTable)
self.database.insert([TestTable(date_field=date.today())])
def tearDown(self):
self.database.drop_database()
def _get_backups(self):
for dir in self.BACKUP_DIRS:
if os.path.exists(dir):
_, dirnames, _ = next(os.walk(dir))
return dirnames
raise unittest.SkipTest('Cannot find backups dir')
def test_get_all(self):
parts = SystemPart.get(self.database)
self.assertEqual(len(list(parts)), 1)
def test_get_active(self):
parts = list(SystemPart.get_active(self.database))
self.assertEqual(len(parts), 1)
parts[0].detach()
self.assertEqual(len(list(SystemPart.get_active(self.database))), 0)
def test_get_conditions(self):
parts = list(
SystemPart.get(self.database, conditions="table='testtable'"))
self.assertEqual(len(parts), 1)
parts = list(
SystemPart.get(self.database, conditions=u"table='othertable'"))
self.assertEqual(len(parts), 0)
def test_attach_detach(self):
parts = list(SystemPart.get_active(self.database))
self.assertEqual(len(parts), 1)
parts[0].detach()
self.assertEqual(len(list(SystemPart.get_active(self.database))), 0)
parts[0].attach()
self.assertEqual(len(list(SystemPart.get_active(self.database))), 1)
def test_drop(self):
parts = list(SystemPart.get_active(self.database))
parts[0].drop()
self.assertEqual(len(list(SystemPart.get_active(self.database))), 0)
def test_freeze(self):
parts = list(SystemPart.get(self.database))
# There can be other backups in the folder
prev_backups = set(self._get_backups())
parts[0].freeze()
backups = set(self._get_backups())
self.assertEqual(len(backups), len(prev_backups) + 1)
def test_fetch(self):
# TODO Not tested, as I have no replication set
pass
