def create_upsert_statement(self,
table,
fields,
constraint_fields,
statement_string=False,
coalesce=None):
"""returns update statement"""
insert_fields, insert_statement = self.create_insert_statement(
table, fields)
update_statement = self._create_on_duplicate_key_update_statement(
fields,
constraint_fields,
table=table,
coalesce=coalesce,
)
sql = SqlClause(phrases=[insert_statement, update_statement],
phrase_indents=0,
phrase_separator='\n')
if statement_string:
sql = sql.get_sql_clause()
return insert_fields, sql
def _create_join_clauses(all_tables, all_join_keys):
first = True
join_clauses = list()
for table, schema_join_key in zip(all_tables, all_join_keys):
if not first:
next_clause = SqlClause(header="INNER JOIN",
phrases=["{}".format(table)])
join_clauses.append(next_clause)
next_clause = SqlClause(header="ON",
phrases=[
"{}.{} = {}.{}".format(
previous_table,
previous_schema_join_key,
table, schema_join_key)
])
join_clauses.append(next_clause)
else:
next_clause = SqlClause(header="FROM",
phrases=["{}".format(table)])
first = False
previous_table = table
previous_schema_join_key = schema_join_key
join_clauses.append(next_clause)
return join_clauses
def create_create_table_statement(cls,
table,
fields,
primary_key=None,
unique_keys=None,
statement_string=False,
if_not_exists=False):
"""returns create table statement"""
schema_lines = ['{0} {1}'.format(field, datatype) for field, datatype in list(fields.items())]
if primary_key:
#primary key is list, but pass dictionary since create_constraints requires list of lists
schema_lines.extend(cls.create_constraint_lines('primary', [['', primary_key]]))
schema_lines.extend(cls.create_constraint_lines('unique', unique_keys))
if if_not_exists:
create_table_header = 'CREATE TABLE IF NOT EXISTS {} ('
else:
create_table_header = 'CREATE TABLE {} ('
if statement_string:
return [], SqlClause(header=create_table_header.format(table),
phrases=schema_lines,
footer=')').get_sql_clause()
else:
return [], SqlClause(header=create_table_header.format(table),
phrases=schema_lines,
footer=')')
def create_drop_table_statement(table, statement_string=False):
"""stuff"""
if statement_string:
return [], SqlClause(header='DROP TABLE IF EXISTS {}'.format(
table)).get_sql_clause()
else:
return [], SqlClause(
header='DROP TABLE IF EXISTS {}'.format(table))
def create_truncate_table_statement(table, statement_string=False):
"""stuff"""
sql_clause = SqlClause(header='TRUNCATE TABLE `{0}`'.format(table))
# Convert to string
if statement_string:
sql_clause = sql_clause.get_sql_clause()
return [], sql_clause
def create_truncate_table_statement(table, statement_string=False):
"""stuff"""
sql_clause = SqlClause(header='TRUNCATE TABLE `{0}`'.format(table))
# Convert to string
if statement_string:
sql_clause = sql_clause.get_sql_clause()
return [], sql_clause
def create_composite_sql_table_delete_statement(self,
component_schema_ids,
join_key,
row_deleted_field,
time_cutoff_field=None,
where_phrases=None):
all_tables = list()
all_join_keys = list()
all_clauses = list()
output_fields = list()
if where_phrases is None:
where_phrases = list()
all_join_keys.append(join_key)
for schema_id in component_schema_ids:
component = self.component_schemas[schema_id]
schema = component["config"]
field_renames = component["field_renames"]
reverse_field_renames = {
value: key
for key, value in list(field_renames.items())
}
schema_join_key = reverse_field_renames.get(join_key, join_key)
table = schema.table
all_tables.append(table)
all_join_keys.append(schema_join_key)
delete_clause = SqlClause(
header="DELETE", phrases=["{table}.*".format(table=self.table)])
all_clauses.append(delete_clause)
all_clauses.extend(
self._create_join_clauses([self.table] + all_tables,
all_join_keys))
if row_deleted_field:
where_phrases.extend([
"{}.{} = 1".format(table, row_deleted_field)
for table in all_tables
])
where_fields, where_clauses = self._create_where_clauses(
all_tables, time_cutoff_field, where_phrases)
output_fields.extend(where_fields)
all_clauses.extend(where_clauses)
return output_fields, SqlClause(phrases=all_clauses,
phrase_indents=0,
phrase_separator="\n")
def create_upsert_statement(self, table, fields, statement_string=False):
"""returns update statement"""
insert_fields, insert_statement = self.create_insert_statement(table, fields)
update_statement = self._create_on_duplicate_key_update_statement(fields)
sql = SqlClause(phrases=[insert_statement, update_statement],
phrase_indents=0,
phrase_separator='\n')
if statement_string:
sql = sql.get_sql_clause()
return insert_fields, sql
def create_insert_statement(table, fields, statement_string=False):
"""returns insert statement"""
insert_clause = SqlClause(
header='INSERT INTO {} ('.format(table),
footer=')',
phrases=['`{0}`'.format(field) for field in fields])
insert_values = 'VALUES (' + ', '.join(['%s'] * len(fields)) + ')'
if statement_string:
return fields, SqlClause(phrases=[insert_clause, insert_values],
phrase_indents=0,
phrase_separator='\n').get_sql_clause()
else:
return fields, SqlClause(phrases=[insert_clause, insert_values],
phrase_indents=0,
phrase_separator='\n')
def create_update_statement(table,
fields,
where_fields=None,
statement_string=False):
"""
returns update statement
where_fields : list of dictionaries with format:
dictionary {"field_name": "field1", "operator": "<"}
"""
update_field_phrases = [field + ' = %s' for field in fields]
update_clause = SqlClause(header='UPDATE {}'.format(table))
update_values = SqlClause(header='SET', phrases=update_field_phrases)
phrases = [update_clause, update_values]
# Statement fields are fields + where_fields
statement_fields = list(fields)
if where_fields is not None:
where_phrase = list()
for field in where_fields:
# Field values is an explicit override in a where clause,
# used for more fine grained control of where_fields
# Does not add a field to statement_fields.
where_phrase.append(' '.join([
field['field_name'], field['operator'],
field.get('field_value', '%s')
]))
where_phrase = SqlClause(header='WHERE',
phrases=where_phrase,
phrase_separator=' AND\n')
for field in where_fields:
if field.get('field_value') is None:
statement_fields.append(field["field_name"])
phrases.append(where_phrase)
if statement_string:
return statement_fields, SqlClause(
phrases=phrases, phrase_indents=0,
phrase_separator='\n').get_sql_clause()
else:
return statement_fields, SqlClause(phrases=phrases,
phrase_indents=0,
phrase_separator='\n')
def _create_on_duplicate_key_update_statement(cls, fields, coalesce=None):
"""returns update statement for upsert"""
coalesce_string = cls.COALESCE_MAP[coalesce]
sql_obj = SqlClause(header='ON DUPLICATE KEY UPDATE',
phrases=[
'`{}` = '.format(field) +
coalesce_string.format(field)
for field in fields
])
return sql_obj
def _create_on_duplicate_key_update_statement(cls,
fields,
constraint_fields,
coalesce=None,
table=None):
"""returns update statement for upsert"""
coalesce_string = cls.COALESCE_MAP[coalesce]
header = ','.join(constraint_fields)
header = 'ON CONFLICT (' + header + ') DO UPDATE SET '
sql_obj = SqlClause(header=header,
phrases=[
'{} = '.format(field) +
coalesce_string.format(field, table=table)
for field in fields
])
return sql_obj
def _create_where_clauses(all_tables, time_cutoff_field, where_phrases):
where_clauses = list()
output_fields = list()
if time_cutoff_field:
greatest_list = [
table + "." + time_cutoff_field for table in all_tables
]
greatest_list.append("0")
where_phrases.append("GREATEST({greatest_phrase}) >= (%s)".format(
greatest_phrase=", ".join(greatest_list)))
output_fields.append(time_cutoff_field)
if where_phrases:
where_clause = SqlClause(
header="WHERE",
phrases=["({})".format(phrase) for phrase in where_phrases],
phrase_separator=' AND\n')
where_clauses.append(where_clause)
return output_fields, where_clauses
def create_composite_sql_table_update_statement(self,
component_schema_ids,
join_key,
time_cutoff_field=None,
where_phrases=None):
all_fields = list()
all_mapped_fields = list()
all_tables = list()
all_join_keys = list()
all_clauses = list()
output_fields = list()
if where_phrases is None:
where_phrases = list()
for schema_id in component_schema_ids:
component = self.component_schemas[schema_id]
schema = component["config"]
ignored_fields = set(component["ignored_fields"])
field_renames = component["field_renames"]
table = schema.table
# NOTE we dont update the schema_join_key
reverse_field_renames = {
value: key
for key, value in list(field_renames.items())
}
schema_join_key = reverse_field_renames.get(join_key, join_key)
fields, mapped_fields = list(
zip(*[("{}.{}".format(table, field),
field_renames.get(field, field))
for field in schema.fields if field not in ignored_fields
and field != schema_join_key]))
all_fields.extend(fields)
all_mapped_fields.extend(mapped_fields)
all_tables.append(table)
all_join_keys.append(schema_join_key)
update_clause = SqlClause(
header="UPDATE {table}".format(table=self.table),
phrases=all_mapped_fields)
all_clauses.append(update_clause)
for table, schema_join_key in zip(all_tables, all_join_keys):
next_clause = SqlClause(header="INNER JOIN",
phrases=["{}".format(table)])
all_clauses.append(next_clause)
next_clause = SqlClause(header="ON",
phrases=[
"{}.{} = {}.{}".format(
self.table, join_key, table,
schema_join_key)
])
all_clauses.append(next_clause)
set_clause = SqlClause(
header="SET",
phrases=[
"{}.{} = VALUES({})".format(self.table, mapped_field, field)
for field, mapped_field in zip(all_fields, all_mapped_fields)
])
all_clauses.append(set_clause)
where_fields, where_clauses = self._create_where_clauses(
all_tables, time_cutoff_field, where_phrases)
output_fields.extend(where_fields)
all_clauses.extend(where_clauses)
return output_fields, SqlClause(phrases=all_clauses,
phrase_indents=0,
phrase_separator="\n")
def create_composite_sql_table_upsert_statement(self,
component_schema_ids,
join_key,
time_cutoff_field=None,
where_phrases=None):
all_fields = list()
all_mapped_fields = list()
all_tables = list()
all_join_keys = list()
all_clauses = list()
output_fields = list()
if where_phrases is None:
where_phrases = list()
for schema_id in component_schema_ids:
component = self.component_schemas[schema_id]
schema = component["config"]
ignored_fields = set(component["ignored_fields"])
field_renames = component["field_renames"]
reverse_field_renames = {
value: key
for key, value in list(field_renames.items())
}
schema_join_key = reverse_field_renames.get(join_key, join_key)
table = schema.table
# NOTE we ignore the join key in the fields
table_fields, mapped_fields = list(
zip(*[((table, field), field_renames.get(field, field))
for field in schema.fields if field not in ignored_fields
and field != schema_join_key]))
all_fields.extend(table_fields)
all_mapped_fields.extend(mapped_fields)
all_tables.append(table)
all_join_keys.append(schema_join_key)
# Add the join key to fields ONCE
all_fields.append((table, schema_join_key))
all_mapped_fields.append(join_key)
insert_clause = SqlClause(
header="INSERT INTO {table} (".format(table=self.table),
footer=')',
phrases=all_mapped_fields)
all_clauses.append(insert_clause)
select_clause = SqlClause(header="SELECT",
phrases=[
"{}.{}".format(table, field)
for table, field in all_fields
])
all_clauses.append(select_clause)
all_clauses.extend(self._create_join_clauses(all_tables,
all_join_keys))
where_fields, where_clauses = self._create_where_clauses(
all_tables, time_cutoff_field, where_phrases)
output_fields.extend(where_fields)
all_clauses.extend(where_clauses)
duplicate_update_clause = SqlClause(
header="ON DUPLICATE KEY UPDATE",
phrases=[
"{} = VALUES({})".format(mapped_field, table_field[1]) for
table_field, mapped_field in zip(all_fields, all_mapped_fields)
])
all_clauses.append(duplicate_update_clause)
return output_fields, SqlClause(phrases=all_clauses,
phrase_indents=0,
phrase_separator="\n")
