def get_fields(model):
# returns all fields that aren't part of the PK
ks_name = model._get_keyspace()
col_family = model.column_family_name(include_keyspace=False)
with connection_manager() as con:
query = "SELECT * FROM system.schema_columns \
WHERE keyspace_name = :ks_name AND columnfamily_name = :col_family"
logger.debug("get_fields %s %s", ks_name, col_family)
tmp = con.execute(query, {'ks_name': ks_name, 'col_family': col_family}, ONE)
# Tables containing only primary keys do not appear to create
# any entries in system.schema_columns, as only non-primary-key attributes
# appear to be inserted into the schema_columns table
if not tmp.results:
return []
column_name_positon = tmp.columns.index('column_name')
validator_positon = tmp.columns.index('validator')
try:
type_position = tmp.columns.index('type')
return [Field(x[column_name_positon], x[validator_positon]) for x in tmp.results if x[type_position] == 'regular']
except ValueError:
return [Field(x[column_name_positon], x[validator_positon]) for x in tmp.results]
def create_keyspace(name, strategy_class='SimpleStrategy', replication_factor=3, durable_writes=True, **replication_values):
"""
creates a keyspace
:param name: name of keyspace to create
:param strategy_class: keyspace replication strategy class
:param replication_factor: keyspace replication factor
:param durable_writes: 1.2 only, write log is bypassed if set to False
:param **replication_values: 1.2 only, additional values to ad to the replication data map
"""
with connection_manager() as con:
keyspaces = con.execute(
"""SELECT keyspace_name FROM system.schema_keyspaces""", {})
if name not in [r[0] for r in keyspaces]:
# try the 1.2 method
replication_map = {
'class': strategy_class,
'replication_factor': replication_factor
}
replication_map.update(replication_values)
query = """
CREATE KEYSPACE {}
WITH REPLICATION = {}
""".format(name, json.dumps(replication_map).replace('"', "'"))
if strategy_class != 'SimpleStrategy':
query += " AND DURABLE_WRITES = {}".format(
'true' if durable_writes else 'false')
execute(query)
def create_keyspace(name, strategy_class='SimpleStrategy', replication_factor=3, durable_writes=True, **replication_values):
"""
creates a keyspace
:param name: name of keyspace to create
:param strategy_class: keyspace replication strategy class
:param replication_factor: keyspace replication factor
:param durable_writes: 1.2 only, write log is bypassed if set to False
:param **replication_values: 1.2 only, additional values to ad to the replication data map
"""
with connection_manager() as con:
_, keyspaces = con.execute("""SELECT keyspace_name FROM system.schema_keyspaces""", {})
if name not in [r[0] for r in keyspaces]:
#try the 1.2 method
replication_map = {
'class': strategy_class,
'replication_factor':replication_factor
}
replication_map.update(replication_values)
if strategy_class.lower() != 'simplestrategy':
# Although the Cassandra documentation states for `replication_factor`
# that it is "Required if class is SimpleStrategy; otherwise,
# not used." we get an error if it is present.
replication_map.pop('replication_factor', None)
query = """
CREATE KEYSPACE {}
WITH REPLICATION = {}
""".format(name, json.dumps(replication_map).replace('"', "'"))
if strategy_class != 'SimpleStrategy':
query += " AND DURABLE_WRITES = {}".format('true' if durable_writes else 'false')
execute(query)
def create_keyspace(name, strategy_class='SimpleStrategy', replication_factor=3, durable_writes=True, **replication_values):
"""
creates a keyspace
:param name: name of keyspace to create
:param strategy_class: keyspace replication strategy class
:param replication_factor: keyspace replication factor
:param durable_writes: 1.2 only, write log is bypassed if set to False
:param **replication_values: 1.2 only, additional values to ad to the replication data map
"""
with connection_manager() as con:
if not any([name == k.name for k in con.con.client.describe_keyspaces()]):
# if name not in [k.name for k in con.con.client.describe_keyspaces()]:
try:
#Try the 1.1 method
con.execute("""CREATE KEYSPACE {}
WITH strategy_class = '{}'
AND strategy_options:replication_factor={};""".format(name, strategy_class, replication_factor))
except CQLEngineException:
#try the 1.2 method
replication_map = {
'class': strategy_class,
'replication_factor':replication_factor
}
replication_map.update(replication_values)
query = """
CREATE KEYSPACE {}
WITH REPLICATION = {}
""".format(name, json.dumps(replication_map).replace('"', "'"))
if strategy_class != 'SimpleStrategy':
query += " AND DURABLE_WRITES = {}".format('true' if durable_writes else 'false')
con.execute(query)
def _execute_query(self):
if self._batch:
raise CQLEngineException("Only inserts, updates, and deletes are available in batch mode")
if self._result_cache is None:
self._con = connection_manager()
self._cur = self._con.execute(self._select_query(), self._where_values())
self._result_cache = [None] * self._cur.rowcount
def delete_table(model):
cf_name = model.column_family_name()
with connection_manager() as con:
try:
con.execute('drop table {};'.format(cf_name))
except CQLEngineException as ex:
#don't freak out if the table doesn't exist
if 'Cannot drop non existing column family' not in unicode(ex):
raise
def add_column_raw(column_family_with_keyspace, column, cassandra_type, keyspace):
statement = 'ALTER TABLE %s ADD %s %s' % (
column_family_with_keyspace, column, cassandra_type)#, keyspace)
# Do we need keyspace?
#"SELECT columnfamily_name from system.schema_columnfamilies WHERE keyspace_name = :ks_name",
#{'ks_name': ks_name}
from cqlengine import connection
with connection.connection_manager() as con:
tables = con.execute(statement, {})
def get_fields(model):
# returns all fields that aren't part of the PK
ks_name = model._get_keyspace()
col_family = model.column_family_name(include_keyspace=False)
with connection_manager() as con:
query = "SELECT column_name, validator FROM system.schema_columns \
WHERE keyspace_name = :ks_name AND columnfamily_name = :col_family"
logger.debug("get_fields %s %s", ks_name, col_family)
tmp = con.execute(query, {'ks_name':ks_name, 'col_family':col_family})
return [Field(x[0], x[1]) for x in tmp.results]
def drop_table(model):
# don't try to delete non existant tables
ks_name = model._get_keyspace()
with connection_manager() as con:
_, tables = con.execute(
"SELECT columnfamily_name from system.schema_columnfamilies WHERE keyspace_name = :ks_name",
{'ks_name': ks_name}
)
raw_cf_name = model.column_family_name(include_keyspace=False)
if raw_cf_name not in [t[0] for t in tables]:
return
cf_name = model.column_family_name()
execute('drop table {};'.format(cf_name))
def delete(self, columns=[]):
"""
Deletes the contents of a query
"""
# validate where clause
partition_key = self.model._primary_keys.values()[0]
if not any([c.column.db_field_name == partition_key.db_field_name for c in self._where]):
raise QueryException("The partition key must be defined on delete queries")
qs = ["DELETE FROM {}".format(self.column_family_name)]
qs += ["WHERE {}".format(self._where_clause())]
qs = " ".join(qs)
if self._batch:
self._batch.add_query(qs, self._where_values())
else:
with connection_manager() as con:
con.execute(qs, self._where_values())
def get_fields(model):
# returns all fields that aren't part of the PK
ks_name = model._get_keyspace()
col_family = model.column_family_name(include_keyspace=False)
with connection_manager() as con:
query = "SELECT * FROM system.schema_columns \
WHERE keyspace_name = :ks_name AND columnfamily_name = :col_family"
logger.debug("get_fields %s %s", ks_name, col_family)
tmp = con.execute(query, {'ks_name': ks_name, 'col_family': col_family}, ONE)
column_indices = [tmp.columns.index('column_name'), tmp.columns.index('validator')]
try:
type_index = tmp.columns.index('type')
return [Field(x[column_indices[0]], x[column_indices[1]]) for x in tmp.results if x[type_index] == 'regular']
except ValueError:
return [Field(x[column_indices[0]], x[column_indices[1]]) for x in tmp.results]
def execute(self):
opener = 'BEGIN ' + (self.batch_type + ' ' if self.batch_type else '') + ' BATCH'
if self.timestamp:
epoch = datetime(1970, 1, 1)
ts = long((self.timestamp - epoch).total_seconds() * 1000)
opener += ' USING TIMESTAMP {}'.format(ts)
query_list = [opener]
parameters = {}
for query, params in self.queries:
query_list.append(' ' + query)
parameters.update(params)
query_list.append('APPLY BATCH;')
with connection_manager() as con:
con.execute('\n'.join(query_list), parameters)
self.queries = []
def count(self):
""" Returns the number of rows matched by this query """
if self._batch:
raise CQLEngineException("Only inserts, updates, and deletes are available in batch mode")
# TODO: check for previous query execution and return row count if it exists
if self._result_cache is None:
qs = ["SELECT COUNT(*)"]
qs += ["FROM {}".format(self.column_family_name)]
if self._where:
qs += ["WHERE {}".format(self._where_clause())]
if self._allow_filtering:
qs += ["ALLOW FILTERING"]
qs = " ".join(qs)
with connection_manager() as con:
cur = con.execute(qs, self._where_values())
return cur.fetchone()[0]
else:
return len(self._result_cache)
def delete(self):
""" Deletes one instance """
if self.instance is None:
raise CQLEngineException("DML Query intance attribute is None")
field_values = {}
qs = ["DELETE FROM {}".format(self.column_family_name)]
qs += ["WHERE"]
where_statements = []
for name, col in self.model._primary_keys.items():
field_id = uuid1().hex
field_values[field_id] = col.to_database(getattr(self.instance, name))
where_statements += ['"{}" = :{}'.format(col.db_field_name, field_id)]
qs += [" AND ".join(where_statements)]
qs = " ".join(qs)
if self.batch:
self.batch.add_query(qs, field_values)
else:
with connection_manager() as con:
con.execute(qs, field_values)
def execute(self):
if len(self.queries) == 0:
# Empty batch is a no-op
return
opener = "BEGIN " + (self.batch_type + " " if self.batch_type else "") + " BATCH"
if self.timestamp:
epoch = datetime(1970, 1, 1)
ts = long((self.timestamp - epoch).total_seconds() * 1000)
opener += " USING TIMESTAMP {}".format(ts)
query_list = [opener]
parameters = {}
for query, params in self.queries:
query_list.append(" " + query)
parameters.update(params)
query_list.append("APPLY BATCH;")
with connection_manager() as con:
con.execute("\n".join(query_list), parameters)
self.queries = []
def delete_keyspace(name):
with connection_manager() as con:
_, keyspaces = con.execute("""SELECT keyspace_name FROM system.schema_keyspaces""", {})
if name in [r[0] for r in keyspaces]:
execute("DROP KEYSPACE {}".format(name))
def save(self):
"""
Creates / updates a row.
This is a blind insert call.
All validation and cleaning needs to happen
prior to calling this.
"""
if self.instance is None:
raise CQLEngineException("DML Query intance attribute is None")
assert type(self.instance) == self.model
# organize data
value_pairs = []
values = self.instance.as_dict()
# get defined fields and their column names
for name, col in self.model._columns.items():
val = values.get(name)
if val is None:
continue
value_pairs += [(col.db_field_name, val)]
# construct query string
field_names = zip(*value_pairs)[0]
field_ids = {n: uuid1().hex for n in field_names}
field_values = dict(value_pairs)
query_values = {field_ids[n]: field_values[n] for n in field_names}
qs = []
if self.instance._can_update():
qs += ["UPDATE {}".format(self.column_family_name)]
qs += ["SET"]
set_statements = []
# get defined fields and their column names
for name, col in self.model._columns.items():
if not col.is_primary_key:
val = values.get(name)
if val is None:
continue
if isinstance(col, BaseContainerColumn):
# remove value from query values, the column will handle it
query_values.pop(field_ids.get(name), None)
val_mgr = self.instance._values[name]
set_statements += col.get_update_statement(val, val_mgr.previous_value, query_values)
pass
else:
set_statements += ['"{}" = :{}'.format(col.db_field_name, field_ids[col.db_field_name])]
qs += [", ".join(set_statements)]
qs += ["WHERE"]
where_statements = []
for name, col in self.model._primary_keys.items():
where_statements += ['"{}" = :{}'.format(col.db_field_name, field_ids[col.db_field_name])]
qs += [" AND ".join(where_statements)]
# clear the qs if there are not set statements
if not set_statements:
qs = []
else:
qs += ["INSERT INTO {}".format(self.column_family_name)]
qs += ["({})".format(", ".join(['"{}"'.format(f) for f in field_names]))]
qs += ["VALUES"]
qs += ["({})".format(", ".join([":" + field_ids[f] for f in field_names]))]
qs = " ".join(qs)
# skip query execution if it's empty
# caused by pointless update queries
if qs:
if self.batch:
self.batch.add_query(qs, query_values)
else:
with connection_manager() as con:
con.execute(qs, query_values)
# delete nulled columns and removed map keys
qs = ["DELETE"]
query_values = {}
del_statements = []
for k, v in self.instance._values.items():
col = v.column
if v.deleted:
del_statements += ['"{}"'.format(col.db_field_name)]
elif isinstance(col, Map):
del_statements += col.get_delete_statement(v.value, v.previous_value, query_values)
if del_statements:
qs += [", ".join(del_statements)]
qs += ["FROM {}".format(self.column_family_name)]
qs += ["WHERE"]
where_statements = []
for name, col in self.model._primary_keys.items():
field_id = uuid1().hex
query_values[field_id] = field_values[name]
where_statements += ['"{}" = :{}'.format(col.db_field_name, field_id)]
qs += [" AND ".join(where_statements)]
qs = " ".join(qs)
if self.batch:
self.batch.add_query(qs, query_values)
else:
with connection_manager() as con:
con.execute(qs, query_values)
def delete_keyspace(name):
with connection_manager() as con:
if name in [k.name for k in con.con.client.describe_keyspaces()]:
con.execute("DROP KEYSPACE {}".format(name))
def delete_keyspace(name):
with connection_manager() as con:
_, keyspaces = con.execute("""SELECT keyspace_name FROM system.schema_keyspaces""", {})
if name in [r[0] for r in keyspaces]:
execute("DROP KEYSPACE {}".format(name))
def create_table(model, create_missing_keyspace=True):
#construct query string
cf_name = model.column_family_name()
raw_cf_name = model.column_family_name(include_keyspace=False)
#create missing keyspace
if create_missing_keyspace:
create_keyspace(model._get_keyspace())
with connection_manager() as con:
#check for an existing column family
ks_info = con.con.client.describe_keyspace(model._get_keyspace())
if not any([raw_cf_name == cf.name for cf in ks_info.cf_defs]):
qs = ['CREATE TABLE {}'.format(cf_name)]
#add column types
pkeys = []
qtypes = []
def add_column(col):
s = col.get_column_def()
if col.primary_key: pkeys.append('"{}"'.format(col.db_field_name))
qtypes.append(s)
for name, col in model._columns.items():
add_column(col)
qtypes.append('PRIMARY KEY ({})'.format(', '.join(pkeys)))
qs += ['({})'.format(', '.join(qtypes))]
# add read_repair_chance
qs += ['WITH read_repair_chance = {}'.format(model.read_repair_chance)]
qs = ' '.join(qs)
try:
con.execute(qs)
except CQLEngineException as ex:
# 1.2 doesn't return cf names, so we have to examine the exception
# and ignore if it says the column family already exists
if "Cannot add already existing column family" not in unicode(ex):
raise
#get existing index names, skip ones that already exist
ks_info = con.con.client.describe_keyspace(model._get_keyspace())
cf_defs = [cf for cf in ks_info.cf_defs if cf.name == raw_cf_name]
idx_names = [i.index_name for i in cf_defs[0].column_metadata] if cf_defs else []
idx_names = filter(None, idx_names)
indexes = [c for n,c in model._columns.items() if c.index]
if indexes:
for column in indexes:
if column.db_index_name in idx_names: continue
qs = ['CREATE INDEX {}'.format(column.db_index_name)]
qs += ['ON {}'.format(cf_name)]
qs += ['("{}")'.format(column.db_field_name)]
qs = ' '.join(qs)
try:
con.execute(qs)
except CQLEngineException as ex:
# 1.2 doesn't return cf names, so we have to examine the exception
# and ignore if it says the index already exists
if "Index already exists" not in unicode(ex):
raise
def sync_table(model, create_missing_keyspace=True):
if model.__abstract__:
raise CQLEngineException("cannot create table from abstract model")
#construct query string
cf_name = model.column_family_name()
raw_cf_name = model.column_family_name(include_keyspace=False)
ks_name = model._get_keyspace()
#create missing keyspace
if create_missing_keyspace:
create_keyspace(ks_name)
with connection_manager() as con:
tables = con.execute(
"SELECT columnfamily_name from system.schema_columnfamilies WHERE keyspace_name = :ks_name",
{'ks_name': ks_name}
)
tables = [x[0] for x in tables.results]
#check for an existing column family
if raw_cf_name not in tables:
qs = get_create_table(model)
try:
execute(qs)
except CQLEngineException as ex:
# 1.2 doesn't return cf names, so we have to examine the exception
# and ignore if it says the column family already exists
if "Cannot add already existing column family" not in unicode(ex):
raise
else:
# see if we're missing any columns
fields = get_fields(model)
field_names = [x.name for x in fields]
for name, col in model._columns.items():
if col.primary_key or col.partition_key: continue # we can't mess with the PK
if col.db_field_name in field_names: continue # skip columns already defined
# add missing column using the column def
query = "ALTER TABLE {} add {}".format(cf_name, col.get_column_def())
logger.debug(query)
execute(query)
update_compaction(model)
#get existing index names, skip ones that already exist
with connection_manager() as con:
_, idx_names = con.execute(
"SELECT index_name from system.\"IndexInfo\" WHERE table_name=:table_name",
{'table_name': raw_cf_name}
)
idx_names = [i[0] for i in idx_names]
idx_names = filter(None, idx_names)
indexes = [c for n,c in model._columns.items() if c.index]
if indexes:
for column in indexes:
if column.db_index_name in idx_names: continue
qs = ['CREATE INDEX index_{}_{}'.format(raw_cf_name, column.db_field_name)]
qs += ['ON {}'.format(cf_name)]
qs += ['("{}")'.format(column.db_field_name)]
qs = ' '.join(qs)
try:
execute(qs)
except CQLEngineException:
# index already exists
pass
def sync_table(model, create_missing_keyspace=True):
if model.__abstract__:
raise CQLEngineException("cannot create table from abstract model")
#construct query string
cf_name = model.column_family_name()
raw_cf_name = model.column_family_name(include_keyspace=False)
ks_name = model._get_keyspace()
#create missing keyspace
if create_missing_keyspace:
create_keyspace(ks_name)
with connection_manager() as con:
tables = con.execute(
"SELECT columnfamily_name from system.schema_columnfamilies WHERE keyspace_name = :ks_name",
{'ks_name': ks_name})
tables = [x[0] for x in tables.results]
#check for an existing column family
if raw_cf_name not in tables:
qs = get_create_table(model)
try:
execute(qs)
except CQLEngineException as ex:
# 1.2 doesn't return cf names, so we have to examine the exception
# and ignore if it says the column family already exists
if "Cannot add already existing column family" not in unicode(ex):
raise
else:
# see if we're missing any columns
fields = get_fields(model)
field_names = [x.name for x in fields]
for name, col in model._columns.items():
if col.primary_key or col.partition_key:
continue # we can't mess with the PK
if col.db_field_name in field_names:
continue # skip columns already defined
# add missing column using the column def
query = "ALTER TABLE {} add {}".format(cf_name,
col.get_column_def())
logger.debug(query)
execute(query)
update_compaction(model)
#get existing index names, skip ones that already exist
with connection_manager() as con:
_, idx_names = con.execute(
"SELECT index_name from system.\"IndexInfo\" WHERE table_name=:table_name",
{'table_name': raw_cf_name})
idx_names = [i[0] for i in idx_names]
idx_names = filter(None, idx_names)
indexes = [c for n, c in model._columns.items() if c.index]
if indexes:
for column in indexes:
if column.db_index_name in idx_names: continue
qs = [
'CREATE INDEX index_{}_{}'.format(raw_cf_name,
column.db_field_name)
]
qs += ['ON {}'.format(cf_name)]
qs += ['("{}")'.format(column.db_field_name)]
qs = ' '.join(qs)
try:
execute(qs)
except CQLEngineException:
# index already exists
pass