def __init__(self, table, schema=None, database=None):
self.schema = schema
self.schema_rating_reject = None
if database:
com = CatalogDB(db=database)
if database is not 'catalog':
# Se o catalogo a ser lido nao esta no banco de dados de catalogo
# e necessario informar em qual esquema esta as tabelas de Rating e Reject
try:
self.schema_rating_reject = settings.SCHEMA_RATING_REJECT
except:
raise (
"The table is in a different schema of the catalog database, the rating and reject tables are not available in this schema. To solve this add the variable SCHEMA_RATING_REJECT to the settings pointing to the schema where the rating and reject tables are available."
)
else:
com = CatalogDB()
self.db = com.database
if not self.db.table_exists(table, schema=self.schema):
raise Exception("Table or view %s.%s does not exist" %
(self.schema, table))
# Desabilitar os warnings na criacao da tabela
with warnings.catch_warnings():
warnings.simplefilter("ignore", category=sa_exc.SAWarning)
table = self.db.get_table_obj(table, schema=self.schema)
# Nome das colunas originais na tabela
self.columns = [column.key for column in table.columns]
self.table = table.alias('a')
def __init__(self, table, schema=None, database=None):
self.schema = schema
if database:
com = CatalogDB(db=database)
else:
com = CatalogDB()
self.db = com.database
if not self.db.table_exists(table, schema=self.schema):
raise Exception("Table or view %s.%s does not exist" %
(self.schema, table))
self.table = self.db.get_table_obj(table, schema=self.schema)
self.str_columns = None
def create_table_as(self, database, table, stm, schema=None):
self.logger.info("Create new table %s" % table)
self.logger.debug("Database: %s" % database)
self.logger.debug("Schema: %s" % schema)
self.logger.debug("Table: %s" % table)
self.logger.debug("Query: %s" % str(stm))
catalog = CatalogDB(db=database)
# Verifica se a tabela nao existe
if catalog.table_exists(table, schema):
raise Exception("Table %s already exists." % table)
catalog.create_table_as(table=table, stm=stm, schema=schema)
self.logger.info("Table created successfully.")
def __init__(self, table, schema=None, database=None):
self.schema = schema
if database:
com = CatalogDB(db=database)
else:
com = CatalogDB()
self.db = com.database
if not self.db.table_exists(table, schema=self.schema):
raise Exception("Table or view %s.%s does not exist" %
(self.schema, table))
# Desabilitar os warnings na criacao da tabela
with warnings.catch_warnings():
warnings.simplefilter("ignore", category=sa_exc.SAWarning)
self.table = self.db.get_table_obj(table, schema=self.schema)
self.str_columns = None
def create_table(self, name, columns_type):
self.logger.info('Create table with SqlAlchemy')
self.logger.debug('Table name: %s' % name)
self.db = CatalogDB()
cols = list()
for col in columns_type:
cols.append(columns_type.get(col))
try:
table = self.db.create_table(name, columns=cols)
self.logger.debug('Table %s Created' % name)
return table
except Exception as e:
raise e
def get_class_tree_by_group(self, request):
"""
Este metodo retorna uma tree, com todos os produtos de um grupo. estes produtos esto
agrupados por suas classes.
necessario o parametro group que o internal name da tabela Group
ex: catalog/get_class_tree_by_group/group='targets'
"""
group = request.query_params.get('group', None)
groupin = request.query_params.get('group__in', None)
if not group and not groupin:
# TODO retornar execpt que o group e obrigatorio
return Response({
'success': False,
'msg': 'Necessario passar o parametro group.'
})
groups = None
if groupin is not None:
groups = groupin.split(',')
# Usando Filter_Queryset e aplicado os filtros listados no filterbackend
queryset = self.filter_queryset(self.get_queryset())
# Search
prd_display_name = request.query_params.get('search', None)
if prd_display_name:
queryset = self.queryset.filter(prd_display_name__icontains=prd_display_name)
# Bookmark
bookmarked = request.query_params.get('bookmark', None)
if bookmarked:
# Recuperar todos os catalogos marcados como favorito pelo usuario logado
bookmarkeds = BookmarkProduct.objects.filter(owner=request.user.pk)
if bookmarkeds.count() > 0:
ids = bookmarkeds.values_list('product', flat=True)
queryset = self.queryset.filter(pk__in=ids)
# Caso tenha mais de um group passado por parametro o no principal sera o grupo
nodeGroup = dict()
# Esse dicionario vai receber os nos principais que sao as classes.
classes = dict()
# Instancia do banco de Catalogo
catalog_db = CatalogDB()
for row in queryset:
# Internal name da classe
class_name = str(row.prd_class.pcl_name)
# Verifica se ja existe um no para essa classe
# se nao existir cria um no e adiciona ao dict classes
if classes.get(class_name) is None:
classes.update({
class_name: dict({
"text": "%s" % row.prd_class.pcl_display_name,
"expanded": False,
"children": list(),
"pgr_name": str(row.prd_class.pcl_group.pgr_name),
"leaf": False,
})
})
# Cria um dict usando o Serializer setado neste Viewset
catalog = self.get_serializer(row).data
# O Catalogo e editavel se o usuario logado for = o owner do produto
editable = False
if row.prd_owner and request.user.pk == row.prd_owner.pk:
editable = True
# Checar se o Catalog Existe no Banco de Dados
# TODO essa solucao e temporaria enquanto nao temos um garbage colector para apagar as tabelas
# nao registradas.
table_exist = False
iconcls = 'x-fa fa-exclamation-triangle color-orange'
try:
if row.tbl_database == 'catalog' or row.tbl_database is None:
if catalog_db.table_exists(schema=row.tbl_schema, table=row.tbl_name):
iconcls = 'no-icon'
table_exist = True
else:
iconcls = 'no-icon'
table_exist = True
except Exception as e:
print(e)
pass
# Adiciono os atributos que serao usados pela interface
# esse dict vai ser um no filho de um dos nos de classe.
catalog.update({
"text": row.prd_display_name,
"leaf": True,
"iconCls": iconcls,
"bookmark": None,
"editable": editable,
"tableExist": table_exist,
"description": row.prd_description,
"external_catalog": False
})
try:
bookmark = BookmarkProduct.objects.get(product=row.id, owner=request.user.pk)
catalog.update({
"bookmark": bookmark.pk,
"iconCls": "x-fa fa-star color-icon-starred",
"starred": True
})
except ObjectDoesNotExist:
pass
# pega o no da classe e adiciona este no de catalogo como filho.
dclass = classes.get(class_name)
dclass.get('children').append(catalog)
if groups is not None:
# Model Product Group
productgroup = row.prd_class.pcl_group
group_name = productgroup.pgr_name
# Verifica se ja existe um no para esse grupo
if nodeGroup.get(group_name) is None:
nodeGroup.update({
group_name: dict({
"text": str(productgroup.pgr_display_name),
"expanded": False,
"children": list()
})
})
result = dict({
'success': True,
'expanded': True,
'children': list()
})
# Se tiver mais de um grupo, as classes vao ficar como subdiretorio do grupo.
external_catalogs_vizier = None
if groups is not None:
external_catalogs_vizier = self.get_external_catalogs()
for class_name in classes:
c = classes.get(class_name)
group_name = c.get('pgr_name')
if group_name in nodeGroup:
nodeGroup.get(group_name).get('children').append(c)
for group_name in nodeGroup:
nodeg = nodeGroup.get(group_name)
# Se ja tiver o grupo External Catalog adiciona os catalogos do vizier como children
if group_name == 'external_catalogs' and external_catalogs_vizier is not None:
from pprint import pprint
# pprint(external_catalogs_vizier.get('children')[0].get('children'))
nodeg['children'].append(external_catalogs_vizier.get('children')[0])
result.get('children').append(nodeg)
# Adiciona Catalogos Externos ex: Vizier
if 'external_catalogs' in groups and 'external_catalogs' not in nodeGroup:
result.get('children').append(external_catalogs_vizier)
else:
# Se tiver apenas um grupo basta retornar as classes
for class_name in classes:
result.get('children').append(classes.get(class_name))
return Response(result)
class ImportTargetListCSV:
def __init__(self):
# Get an instance of a logger
self.logger = logging.getLogger('product_import')
self.csv_separator = ','
self.require_properties = list(['ra', 'dec'])
self.database = 'catalog'
self.schema = None
self.user = None
self.product_class = None
self.internal_name = ''
self.display_name = ''
self.is_public = False
self.releases = list()
self.description = ''
self.csv_data = ''
self.have_headers = False
self.headers = list()
self._last_data_line = 0
self._table_data = list()
self._columns_type = dict({
'id': dict({
'property': 'meta_id',
'type': 'int',
'primary_key': True,
'ucd': 'meta.id;meta.main'
}),
'ra': dict({
'property': 'ra',
'type': 'float',
'nullable': False,
'ucd': 'pos.eq.ra;meta.main'
}),
'dec': dict({
'property': 'dec',
'type': 'float',
'nullable': False,
'ucd': 'pos.eq.dec;meta.main'
})
})
def start_import(self, user_id, data):
self.logger.info('IMPORT TARGET LIST CSV')
self.logger.debug(data)
# Recuperar o Usuario pelo ID
user = self.set_user(user_id)
self.logger.debug('User: %s' % user.username)
# Recuperar a Classe do produto
product_class = self.set_product_class(data.get('class').strip().lower())
self.logger.debug('Product Class: %s - %s' % (product_class.pk, product_class.pcl_display_name))
# Gerar um internal name para ser usado como nome da tabela
internal_name = self.set_internal_name(data.get('name'))
self.logger.debug('Internal Name: %s' % internal_name)
# Display Name
self.display_name = data.get('displayName').strip()
self.logger.debug('Display Name: %s' % self.display_name)
# Is Public
self.is_public = bool(data.get('isPublic'))
self.logger.debug('Is Public: %s' % self.is_public)
# Releases
self.releases = data.get('releases')
self.logger.debug('Releases: %s' % self.releases)
# Description
self.description = data.get('description', None)
# CSV Data
# retirar o escape do caracter \n e remove o \n do final
tmp_data = data.get('csvData').replace('\\n', '\n').strip('\n')
self.csv_data = tmp_data
self.logger.debug('CSV Data: %s' % self.csv_data)
# Parse and Valid CSV Data o retorno sera os dados da tabela
self._table_data = self.parse_csv_data(self.csv_data)
# Depois de fazer o cast nos types de todos os valores e os dados terem passados
# nas validacoes basicas descobrir o tipo de dados de cada coluna usando a
# primeira linha de dados
self._columns_type = self.get_columns_type(self._table_data[0])
# tendo os dados e o nome das colunas pode ser criado a tabela com SqlAlchemy
self.table = self.create_table(self.internal_name, self._columns_type)
# Inserir os dados na nova tabela
self.populate_table(self.table, self._table_data)
# Registrar a nova tabela como produto.
self.product = self.register_new_table_as_product(self.user, self.internal_name, self.display_name,
self.database, self.schema,
self.internal_name, self.product_class, self.releases,
self.description)
return self.product
def set_user(self, user_id):
try:
self.user = User.objects.get(pk=user_id)
return self.user
except User.DoesNotExist as e:
self.logger.error('User Not Valid user_id: %s' % user_id)
raise e
def set_product_class(self, class_name):
try:
self.product_class = ProductClass.objects.get(pcl_name=class_name)
return self.product_class
except User.DoesNotExist as e:
acls = list()
for cls in ProductClass.objects.all():
acls.append(cls.pcl_name)
raise Exception('It is class is not available. these are available: %s' % (', '.join(acls)))
def set_internal_name(self, internal_name):
"""
Cria um internal name que possa ser usado como nome de tabela
:param internal_name:
:return:
"""
# troca espacos por '_', converte para lowercase, remove espacos do final
name = internal_name.replace(' ', '_').lower().strip().strip('\n')
# Retirar qualquer caracter que nao seja alfanumerico exceto '_'
name = ''.join(e for e in name if e.isalnum() or e == '_')
# Limitar a 40 characteres
self.internal_name = name[: 40]
return self.internal_name
def parse_csv_data(self, csv_data):
self.logger.info('Starting Parse CSV')
# Descobir os headers do csv
lines = csv_data.split('\n')
self.logger.debug('Lines: %s' % len(lines))
if len(lines) == 0:
raise Exception('No data in csv')
self.headers = self.get_headers(lines[0])
self.logger.debug('Headers: %s', self.headers)
# TODO se tiver headers deve remover essa linha antes de passar para o metodo
# que retorna os dados.
if self.have_headers:
pass
rows = self.parse_rows(self.headers, lines)
# TODO remover esse Debug
self.logger.debug('ROWS: %s' % rows)
return rows
def get_headers(self, first_line):
self.logger.info('Get Headers')
self.logger.debug('First Line: %s' % first_line)
headers = []
tmp_headers = []
first_line = first_line.replace('/n', '')
# separar as colunas
cols = first_line.split(self.csv_separator)
count_headers = len(cols)
# testar se as colunas sao strings
for col in cols:
col = self.cast_value_type(col)
if isinstance(col, str):
tmp_headers.append(col.lower().strip().strip('\n'))
# Se nao houver nome de colunas na primeira linha usar as required_properties
if len(tmp_headers) == 0:
self.have_headers = False
tmp_headers = self.require_properties
else:
self.have_headers = True
# tem nome de colunas na primeira linha
# TODO testar com nome de colunas
# TODO as colunas nao podem ter caracters especiais apenas alphanumericos
pass
headers = tmp_headers
return headers
def parse_rows(self, headers, lines):
self.logger.info('Parse Rows')
self.logger.debug('Have Headers: %s', self.have_headers)
rows = list()
# Para cada linha validar
for line in lines:
self.logger.debug('ROW: %s' % line)
values = line.split(self.csv_separator)
# Incrementar o contador de linhas para ajudar nas mensagens de erro
self._last_data_line = self._last_data_line + 1
row = dict({})
for index, value in enumerate(values, start=0):
# Tratar o campo header
header = self.headers[index].strip().replace('\n', '').lower()
# Tratar o Valor, tentar descorbrir o tipo e convertelo.
value = self.cast_value_type(value)
# Validar cada valor de acordo com a sua coluna.
value = self.check_is_valid(header, value)
self.logger.debug('Header: %s Value: %s' % (header, value))
row.update({
header: value
})
rows.append(row)
return rows
def cast_value_type(self, value):
try:
value = value.replace('\n', '').strip()
value = ast.literal_eval(value)
except:
value = str(value)
return value
def get_columns_type(self, first_row):
self.logger.info('Extract Column types from first data row')
self.logger.debug(first_row)
for property in first_row:
# Verificar se a coluna ja nao tem tipo padrao definido
if property not in self._columns_type:
# se nao tiver descobrir o tipo de dados e criar um dict com o nome da
# coluna e
pass
# tclass = type(first_row.get(property))
# if isinstance(tclass, float):
# self.logger.debug(self._columns_type)
#
# self._columns_type.append(dict({
# property: dict({
# 'property': property,
# 'type_name': '<type_name>'
# }))
self.logger.debug(self._columns_type)
return self._columns_type
def get_associations(self):
associations = list()
for column in self._columns_type:
c = self._columns_type[column]
associations.append(dict({
'property': c.get('property'),
'ucd': c.get('ucd')
}))
return associations
# %%%%%%%%%%%%%%%%%%%%%%%%%% Create Table %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
def create_table(self, name, columns_type):
self.logger.info('Create table with SqlAlchemy')
self.logger.debug('Table name: %s' % name)
self.db = CatalogDB()
cols = list()
for col in columns_type:
cols.append(columns_type.get(col))
try:
table = self.db.create_table(name, columns=cols)
self.logger.debug('Table %s Created' % name)
return table
except Exception as e:
raise e
def populate_table(self, table, data):
self.logger.info('Populate Table')
try:
start = time.time()
self.db.engine.execute(table.insert(), data)
duration = time.time() - start
self.logger.info('Insert %s rows in %s' % (len(data), '{:.2f} seconds'.format(duration)))
except Exception as e:
raise e
# %%%%%%%%%%%%%%%%%%%%%%%%%% Register new Product %%%%%%%%%%%%%%%%%%%%%%%%%%
def register_new_table_as_product(self, user, internal_name, display_name, database, schema, tablename,
product_class,
releases=None, description=None):
self.logger.info('Register the new table as a product')
associations = self.get_associations()
self.logger.debug('Associations: %s' % associations)
# Dados para o registro
data = list([{
'process_id': None,
'name': internal_name,
'display_name': display_name,
'database': database,
'schema': schema,
'table': tablename,
'filter': None,
'releases': releases,
'fields': list([]),
'association': associations,
'type': 'catalog',
'class': product_class.pcl_name,
'description': description
}])
self.logger.debug('Data: %s' % data)
# Registar o novo produto
import_product = Import()
import_product.user = user
import_product.owner = user
import_product.site = None
import_product.process = None
import_product.import_products(data)
# Retornar o Produto que foi registrado.
newproduct = Product.objects.get(prd_name=self.internal_name)
self.logger.info('New Product as Registered: %s - %s' %(newproduct.pk, newproduct.prd_display_name))
return newproduct
# %%%%%%%%%%%%%%%%%%%%%%%%%% Validacoes por colunas %%%%%%%%%%%%%%%%%%%%%%%%%%
def check_is_valid(self, header, value):
"""
Aplicar validacoes especificas por colunas especificas.
:param header:
:param value:
:return:
"""
if header == 'ra':
value = self.check_ra(value)
elif header == 'dec':
value = self.check_dec(value)
return value
def check_ra(self, value):
"""
Valores para RA devem ser Float e estar entre 0 - 365
:param value:
:return:
"""
message = ('Error on line %s, %s is an invalid value for RA. '
'Should be Float between 0 and 360 degrees' % (self._last_data_line, value))
if isinstance(value, float) or isinstance(value, int):
# Ra negativo tentar converter para 0-360
if value < 0 and value > -180:
value = value + 360
if value > 360:
raise Exception(message)
if value >= 0 and value <= 360:
# Formatar casas decimais
# return float("{0:.5f}".format(value))
return value
else:
self.logger.debug(message)
raise Exception(message)
else:
self.logger.debug(message)
raise Exception(message)
def check_dec(self, value):
"""
Valores para Dec devem ser Float e estar entre -90 a 90
:param value:
:return:
"""
message = ('Error on line %s, %s is an invalid value for Dec. '
'Should be Float between -90 and 90 degrees' % (self._last_data_line, value))
if isinstance(value, float) or isinstance(value, int):
if value >= -90 and value <= 90:
# return float("{0:.5f}".format(value))
return value
else:
self.logger.debug(message)
raise Exception(message)
else:
self.logger.debug(message)
raise Exception(message)
def register_mask(self, data):
if not self.db:
con = CatalogDB()
self.db = con.database
if not self.db.table_exists(data.get('table'),
schema=data.get('schema', None)):
raise Exception("Table or view %s.%s does not exist" %
(data.get('schema', None), data.get('table')))
# Recuperar a classe do produto
cls = self.get_product_class(data.get('class'))
# Recuperar o filtro
filter = self.get_filter(data.get('filter'))
tbl_info = data.get('ora_table_info', None)
tbl_rows = None
tbl_num_columns = None
tbl_size = None
if tbl_info:
tbl_rows = tbl_info.get('n_imported_rows', None)
tbl_num_columns = tbl_info.get('n_imported_columns', None)
tbl_size = tbl_info.get('table_size_in_bytes', None)
# Data do produto caso o produto tenha processo a data do produto = data de start do processo
date = None
if self.process is not None:
date = self.process.epr_start_date
product, created = Mask.objects.update_or_create(
prd_owner=self.owner,
prd_name=data.get('name'),
tbl_schema=data.get('schema', None),
tbl_name=data.get('table'),
defaults={
"prd_process_id": self.process,
"prd_class": cls,
"prd_display_name": data.get('display_name'),
"prd_product_id": data.get('product_id', None),
"prd_version": data.get('version', None),
"prd_description": data.get('description', None),
"prd_date": date,
"prd_is_public": data.get('is_public', True),
"msk_filter": filter,
"tbl_rows": tbl_rows,
"tbl_num_columns": tbl_num_columns,
"tbl_size": tbl_size,
})
if product:
add_release = True
if 'releases' in data and len(data.get('releases')) > 0:
self.product_release(product, data.get('releases'))
add_release = False
# Registrar O produto a seus respectivos Tags
if 'fields' in data:
self.product_tag(product, data.get('fields'), add_release)
else:
raise Exception(
"A failure has occurred and it was not possible to register the product '%s'."
% (data.get('name')))
def register_catalog(self, data):
"""
Registra produtos do tipo Catalog, apos a criacao do model catalog,
e executado o registro das colunas do catalogo.
Args:
data:
Returns:
"""
# Instancia do banco de catalogo
# Recupera a instancia de banco de dados enviada pela requisicao ou utiliza o catalog como default
database = data.get('database', 'catalog')
if not self.db:
con = CatalogDB(db=database)
self.db = con.database
if not self.db.table_exists(data.get('table'),
schema=data.get('schema', None)):
raise Exception("Table or view %s.%s does not exist" %
(data.get('schema', None), data.get('table')))
# Recuperar a quantidade de linhas da tabela
count = self.db.get_count(data.get('table'),
schema=data.get('schema', None))
# Recuperar a classe do produto
cls = self.get_product_class(data.get('class'))
tbl_info = data.get('ora_table_info', None)
tbl_rows = None
tbl_num_columns = None
tbl_size = None
if tbl_info:
tbl_rows = tbl_info.get('n_imported_rows', None)
tbl_num_columns = tbl_info.get('n_imported_columns', None)
tbl_size = tbl_info.get('table_size_in_bytes', None)
# Data do produto caso o produto tenha processo a data do produto = data de start do processo
date = None
if self.process is not None:
date = self.process.epr_start_date
product, created = Catalog.objects.update_or_create(
prd_owner=self.owner,
prd_name=data.get('name'),
tbl_database=data.get('database', None),
tbl_schema=data.get('schema', None),
tbl_name=data.get('table'),
defaults={
"prd_process_id": self.process,
"prd_class": cls,
"prd_display_name": data.get('display_name'),
"prd_product_id": data.get('product_id', None),
"prd_version": data.get('version', None),
"prd_description": data.get('description', None),
"prd_date": date,
"prd_is_public": data.get('is_public', True),
"tbl_rows": tbl_rows,
"tbl_num_columns": tbl_num_columns,
"tbl_size": tbl_size,
"ctl_num_objects": count,
})
if product:
add_release = True
if 'releases' in data and len(data.get('releases')) > 0:
self.product_release(product, data.get('releases'))
add_release = False
# Registrar O produto a seus respectivos Tags
if 'fields' in data:
self.product_tag(product, data.get('fields'), add_release)
# Registar as colunas do catalogo
self.register_catalog_content(product, data, created)
return True
else:
raise Exception(
"A failure has occurred and it was not possible to register the product '%s'."
% (data.get('name')))