def get_local_instance(cls):
"""
возвращает инстанс локального хранилища данных(Postgresql)
:rtype : object Postgresql()
:return:
"""
return DatabaseService.get_local_instance()
def get_structure_rows_number(cls, source, structure, cols):
"""
возвращает примерное кол-во строк в запросе селекта для планирования
:param source:
:param structure:
:param cols:
:return:
"""
return DatabaseService.get_structure_rows_number(
source, structure, cols)
def get_database_info(source):
""" Возвращает таблицы истоника данных
:type source: Datasource
"""
tables = DatabaseService.get_tables(source)
if settings.USE_REDIS_CACHE:
return RedisSourceService.get_tables(source, tables)
else:
return {"db": source.db, "host": source.host, "tables": tables}
def get_rows_info(cls, source, cols):
"""
Получение списка значений указанных колонок и таблиц в выбранном источнике данных
:param source: Datasource
:param cols: list
:return: list
"""
structure = RedisSourceService.get_active_tree_structure(source)
return DatabaseService.get_rows(source, cols, structure)
def get_rows_query_for_loading_task(cls, source, structure, cols):
"""
Получение предзапроса данных указанных
колонок и таблиц для селери задачи
:param source:
:param structure:
:param cols:
:return:
"""
rows_query = DatabaseService.get_rows_query(source, cols, structure)
return rows_query
def check_connection(post):
""" Проверяет подключение
"""
conn_info = {
'host': get_utf8_string(post.get('host')),
'login': get_utf8_string(post.get('login')),
'password': get_utf8_string(post.get('password')),
'db': get_utf8_string(post.get('db')),
'port': get_utf8_string(post.get('port')),
'conn_type': get_utf8_string(post.get('conn_type')),
}
return DatabaseService.get_connection_by_dict(conn_info)
def processing(self):
"""
Создание триггеров в БД пользователя
"""
tables_info = self.context['tables_info']
source = Datasource.objects.get(id=self.context['source_id'])
db_instance = DatabaseService.get_source_instance(source)
sep = db_instance.get_separator()
remote_table_create_query = db_instance.remote_table_create_query()
remote_triggers_create_query = db_instance.remote_triggers_create_query(
)
connection = db_instance.connection
cursor = connection.cursor()
for table, columns in tables_info.iteritems():
table_name = '_etl_datasource_cdc_{0}'.format(table)
tables_str = "('{0}')".format(table_name)
cdc_cols_query = db_instance.db_map.cdc_cols_query.format(
tables_str, source.db, 'public')
cursor.execute(cdc_cols_query)
fetched_cols = cursor.fetchall()
existing_cols = {k: v for (k, v) in fetched_cols}
required_indexes = {
k.format(table_name): v
for k, v in REQUIRED_INDEXES.iteritems()
}
cols_str = ''
new = ''
old = ''
cols = ''
for col in columns:
name = col['name']
new += 'NEW.{0}, '.format(name)
old += 'OLD.{0}, '.format(name)
cols += ('{name}, '.format(name=name))
cols_str += ' {sep}{name}{sep} {typ},'.format(sep=sep,
name=name,
typ=col['type'])
# если таблица существует
if existing_cols:
# удаление primary key, если он есть
primary_query = db_instance.get_primary_key(
table_name, source.db)
cursor.execute(primary_query)
primary = cursor.fetchall()
if primary:
primary_name = primary[0][0]
del_pr_query = db_instance.delete_primary_query(
table_name, primary_name)
cursor.execute(del_pr_query)
# добавление недостающих колонок, не учитывая cdc-колонки
new_came_cols = [(x['name'], x["type"]) for x in columns]
diff_cols = [
x for x in new_came_cols if x[0] not in existing_cols
]
if diff_cols:
add_cols_str = """
alter table {0} {1}
""".format(
table_name, ', '.join([
'add column {0} {1}'.format(x[0], x[1])
for x in diff_cols
]))
cursor.execute(add_cols_str)
connection.commit()
# проверка cdc-колонок на существование и типы
cdc_required_types = db_instance.db_map.cdc_required_types
add_col_q = db_instance.db_map.add_column_query
del_col_q = db_instance.db_map.del_column_query
for cdc_k, v in cdc_required_types.iteritems():
if not cdc_k in existing_cols:
cursor.execute(
add_col_q.format(table_name, cdc_k, v["type"],
v["nullable"]))
else:
# если типы не совпадают
if not existing_cols[cdc_k].startswith(v["type"]):
cursor.execute(del_col_q.format(table_name, cdc_k))
cursor.execute(
add_col_q.format(table_name, cdc_k, v["type"],
v["nullable"]))
connection.commit()
# проверяем индексы на колонки и существование,
# лишние индексы удаляем
indexes_query = db_instance.db_map.indexes_query.format(
tables_str, source.db)
cursor.execute(indexes_query)
exist_indexes = cursor.fetchall()
index_cols_i, index_name_i = 1, 2
create_index_q = db_instance.db_map.create_index_query
drop_index_q = db_instance.db_map.drop_index_query
allright_index_names = []
for index in exist_indexes:
index_name = index[index_name_i]
if index_name not in required_indexes:
cursor.execute(
drop_index_q.format(index_name, table_name))
else:
index_cols = sorted(
index_name[index_cols_i].split(','))
if index_cols != required_indexes[index_name]:
cursor.execute(
drop_index_q.format(index_name, table_name))
cursor.execute(
create_index_q.format(
index_name, table_name,
','.join(required_indexes[index_name]),
source.db))
allright_index_names.append(index_name)
diff_indexes_names = [
x for x in required_indexes
if x not in allright_index_names
]
for d_index in diff_indexes_names:
cursor.execute(
create_index_q.format(
d_index, table_name,
','.join(required_indexes[d_index])))
connection.commit()
# если таблица не существует
else:
# создание таблицы у юзера
cursor.execute(
remote_table_create_query.format(table_name, cols_str))
# создание индексов
create_index_q = db_instance.db_map.create_index_query
for index_name, index_cols in required_indexes.iteritems():
cursor.execute(
create_index_q.format(index_name, table_name,
','.join(index_cols), source.db))
connection.commit()
trigger_commands = remote_triggers_create_query.format(
orig_table=table,
new_table=table_name,
new=new,
old=old,
cols=cols)
# multi queries of mysql, delimiter $$
for query in trigger_commands.split('$$'):
cursor.execute(query)
connection.commit()
cursor.close()
connection.close()
self.next_task_params = (GENERATE_DIMENSIONS, load_dimensions, {
'checksum': self.key,
'user_id': self.user_id,
'source_id': self.context['source_id'],
'cols': self.context['cols'],
'col_types': self.context['col_types'],
'dataset_id': self.context['dataset_id'],
})
def get_columns_info(cls, source, tables):
"""
Получение информации по колонкам
Args:
source(`Datasource`): источник
tables(list): Список имен таблиц
Returns:
list: Список словарей с информацией о дереве. Подробрый формат
ответа см. `RedisSourceService.get_final_info`
"""
col_records, index_records, const_records = (
DatabaseService.get_columns_info(source, tables))
stat_records = DatabaseService.get_stats_info(source, tables)
cols, indexes, foreigns = DatabaseService.processing_records(
source, col_records, index_records, const_records)
if not settings.USE_REDIS_CACHE:
return []
RedisSourceService.insert_columns_info(source, tables, cols, indexes,
foreigns, stat_records)
# выбранные ранее таблицы в редисе
active_tables = RedisSourceService.get_active_list(
source.user_id, source.id)
# работа с деревьями
if not active_tables:
trees, without_bind = TableTreeRepository.build_trees(
tuple(tables), source)
sel_tree = TablesTree.select_tree(trees)
remains = without_bind[sel_tree.root.val]
else:
# достаем структуру дерева из редиса
structure = RedisSourceService.get_active_tree_structure(source)
# строим дерево
sel_tree = TablesTree.build_tree_by_structure(structure)
ordered_nodes = TablesTree.get_tree_ordered_nodes([
sel_tree.root,
])
tables_info = RedisSourceService.info_for_tree_building(
ordered_nodes, tables, source)
# перестраиваем дерево
remains = TablesTree.build_tree([
sel_tree.root,
], tuple(tables), tables_info)
# таблица без связи
last = RedisSourceService.insert_remains(source, remains)
# сохраняем дерево
structure = TablesTree.get_tree_structure(sel_tree.root)
ordered_nodes = TablesTree.get_tree_ordered_nodes([
sel_tree.root,
])
RedisSourceService.insert_tree(structure, ordered_nodes, source)
return RedisSourceService.get_final_info(ordered_nodes, source, last)
def get_remote_triggers_create_query(cls, source):
"""
возвращает запрос на создание триггеров в БД клиента
"""
return DatabaseService.get_remote_triggers_create_query(source)
def get_remote_table_create_query(cls, source):
"""
возвращает запрос на создание таблицы в БД клиента
"""
return DatabaseService.get_remote_table_create_query(source)
def get_source_connection(cls, source):
"""
Получить объект соединения источника данных
:type source: Datasource
"""
return DatabaseService.get_connection(source)
def get_table_insert_query(cls, local_instance, source_table_name):
return DatabaseService.get_table_insert_query(local_instance,
source_table_name)
def get_table_create_query(cls, local_instance, key_str, cols_str):
return DatabaseService.get_table_create_query(local_instance, key_str,
cols_str)
def get_separator(cls, source):
return DatabaseService.get_separator(source)