def open(request): db_type = 'sqlite' if request.method == "GET": form = OpenSQliteForm() elif request.method == "POST": db_type = request.POST.get('db_type') if db_type == 'sqlite': form = OpenSQliteForm() if settings.files_explorer: selected_file = request.POST.get('selected_file') dbName = os.path.join(request.POST.get('current_path'), selected_file) if selected_file == '': messages.add_message( request, messages.INFO, "Please select a sqlite database file.") else: form = OpenSQliteForm(request.POST, request.FILES) if form.is_valid(): BASE_DIR = os.path.dirname( os.path.dirname(os.path.abspath(__file__))) dbName = BASE_DIR + '/smart4.sqlite' if dbName != '': engineURL = 'sqlite:///' + dbName con_string = engineURL elif db_type == 'postgresql': form = OpenPostgresForm(request.POST) if form.is_valid(): host = form.cleaned_data.get('db_host') dbName = form.cleaned_data.get('db_name') user = form.cleaned_data.get('db_user') password = form.cleaned_data.get('db_password') con_string = 'postgresql://{2}:{3}@{0}/{1}'.format( host, dbName, user, password) request.session['engineURL'] = con_string request.session['db_type'] = db_type request.session['db_name'] = dbName reflector = Reflector(con_string) error = reflector.reflectTables() if error: messages.add_message(request, messages.WARNING, error) else: cols, tks, data, table_key = update(reflector) return redirect('mainapp:dashboard') return render( request, 'mainapp/open.html', { 'form': form, 'files_explorer': settings.files_explorer, 'directory_content': get_folder_content("/"), 'db_type': db_type })
def get_collar_reference_tables_in_json(request): engineURL = request.session.get('engineURL') reflector = Reflector(engineURL) reflector.reflectTables() data = tb_data(reflector, table_key='OG_SMDH_SYSTEM') content = {'collars': [], 'references': []} for row in data: if row[1] == 'definition (collar)': content['collars'].append(row[0]) if row[1] == 'reference': content['references'].append(row[0]) return JsonResponse({'content': content})
def verify(request, table_key): engineURL = request.session.get('engineURL') reflector = Reflector(engineURL) reflector.reflectTables() exist = reflector.exist_table(table_key) if not exist: msg = "Is not posible verify bugs on: '{}', this table does not exist.".format( table_key) messages.add_message(request, messages.WARNING, msg) else: errors = check_bugs(reflector, table_key) messages.add_message(request, messages.WARNING, errors) return redirect( reverse('mainapp:reflector', kwargs={'table_key': table_key}))
def test_json(request): engineURL = request.session.get('engineURL') table_key = request.GET.get("tk") reflector = Reflector(engineURL) db_type = request.session.get('db_type') content = depend(db_type, reflector, table_key) # content = depend(db_type,reflector,"collar") # content = {'hola':{"lolo":{"lola":"null"}}} return JsonResponse({'content': content})
def remove_table(request): engineURL = request.session.get('engineURL') reflector = Reflector(engineURL) reflector.reflectTables() if request.method == 'POST': tbl = request.POST.get('tbl') db_type = request.session.get('db_type') removeOnCascade(db_type, reflector, tbl) reflector.reflectTables() return redirect('mainapp:reflector')
def get_reflector(request): engineURL = request.session.get('engineURL') reflector = Reflector(engineURL) reflector.reflectTables() return reflector
def add_table(request): if request.method in ['GET', 'POST']: RowFormset = formset_factory(FormTableColumn, extra=1, max_num=15) db_type = request.session.get('db_type') if db_type == "sqlite" or db_type == "postgresql": # con_string = 'sqlite:///{}.sqlite'.format(request.COOKIES.get('db')) # con_string = 'postgresql://postgres@localhost/{}'.format(request.COOKIES.get('db')) con_string = request.session.get('engineURL') eng, meta = og_connect(con_string) if request.method == 'GET': form = AddTableForm() return render(request, 'mainapp/add_table.html', { 'ref': 'dashboard', 'form': form, 'formset': RowFormset }) elif request.method == 'POST': form = AddTableForm(request.POST) formset = RowFormset(request.POST) if form.is_valid() and formset.is_valid(): table_name = form.cleaned_data.get('table_name') reflector = get_reflector(request) exist = reflector.exist_table(table_name) if exist: msg = "The table '{}', already exist.".format(table_name) messages.add_message(request, messages.INFO, msg) return render(request, 'mainapp/add_table.html', { 'form': form, 'formset': formset }) formset_cols = {} # formset for fform in formset: name = fform.cleaned_data.get('name') tb_type = fform.cleaned_data.get('tb_type') if tb_type == 'String': tb_type = String elif tb_type == 'Float': tb_type = Float elif tb_type == "Integer": tb_type = Integer nullable = fform.cleaned_data.get('nullable') formset_cols[name] = { 'coltypes': tb_type, 'nullable': nullable } table_type = form.cleaned_data.get('table_type') # insert assay_certificate, rock_catalog, other_reference table types if table_type == 'assay_certificate' or table_type == 'rock_catalog' or table_type == 'other_reference': # defaults on template client side: # cols = {'Au': {'coltypes': Float,'nullable': True}} # on assay_certificate key=SampleID # on rock_catalog key=RockID # on other_reference key='' table_key = request.POST.get('ftable_key') cols = formset_cols og_references(eng, meta, table_name=table_name, key=str(table_key), cols=cols) elif table_type == 'assay' or table_type == 'litho': # on this tables, collar foreignkey: collar.BHID table_reference = request.POST.get('table_reference') raise Error for column in meta.tables[table_reference].columns: if column.primary_key: pk = column.key cols = { pk: { 'coltypes': String, 'nullable': False, 'foreignkey': { 'column': '{}.{}'.format(table_reference, pk), 'ondelete': 'CASCADE', 'onupdate': 'CASCADE' } } } cols.update(formset_cols) og_add_interval(eng, meta, table_name=table_name, cols=cols) # other_interval elif table_type == 'other_interval': # on this tables, collar foreignkey: dbsuffix+_collar.BHID collar_reference = request.POST.get('collar_reference') raise Error if collar_reference and collar_reference.endswith('_collar'): m = re.search("_collar", collar_reference) dbsuffix = collar_reference[:m.start()] elif collar_reference == 'collar': dbsuffix = '' table_reference = request.POST.get('table_reference') for column in meta.tables[table_reference].columns: if column.primary_key: pk = column.key cols = { pk: { 'coltypes': String, 'nullable': False, 'foreignkey': { 'column': '{}.{}'.format(table_reference, pk), 'ondelete': 'CASCADE', 'onupdate': 'CASCADE' } } } cols.update(formset_cols) og_add_interval(eng, meta, table_name=table_name, cols=cols, dbsuffix=dbsuffix) try: execute(eng, meta) except exc.NoReferencedTableError: msg = "Please verify: there are tables really does not exists or are wrong." messages.add_message(request, messages.WARNING, msg) return redirect(reverse('mainapp:reflector')) except: raise # -Register table on system table: OG_SMDH_SYSTEM------------------------# og_register_table = 'OG_SMDH_SYSTEM' if table_type == 'other_interval' or table_type == 'assay' or table_type == 'litho': tbtype = 'interval' elif table_type == 'assay_certificate' or table_type == 'rock_catalog' or table_type == 'other_reference': tbtype = 'reference' tdata = {'Table': table_name, 'Type': tbtype, 'Comments': ''} reflector = Reflector(con_string) reflector.reflectTables() table = reflector.getOg_table(og_register_table) Base = declarative_base() generic_object = type(str(og_register_table), (Base, ), defineObject(table)) session = reflector.make_session() Object_table = generic_object(**tdata) session.add(Object_table) try: session.commit() # session.flush() except: session.rollback() finally: session.close() # end register else: return render(request, 'mainapp/add_table.html', { 'form': form, 'formset': formset }) return redirect('mainapp:reflector')
def reflector(request, table_key=''): engineURL = request.session.get('engineURL') if not engineURL: messages.add_message(request, messages.WARNING, message="Please open a database.") return redirect('mainapp:open') reflector = Reflector(engineURL) # try: can raise AttributeError error = reflector.reflectTables() if error: messages.add_message(request, messages.WARNING, error) return redirect('mainapp:open') if table_key != '': exist = reflector.exist_table(table_key) if not exist: msg = "Please verify that the table: '{}' does not exist.".format( table_key) messages.add_message(request, messages.WARNING, msg) table_key = '' if request.method == 'POST': pks = request.POST.getlist('checkbox_delete') for i, pk in enumerate(pks): pks[i] = pk.split(',') table_key = str(request.POST['tablename']) Base = declarative_base() table = reflector.getOg_table(str(table_key)) object_table = type(str(table_key), (Base, ), defineObject(table)) if pks: session = reflector.make_session() for pk in pks: query = session.query(object_table).get(pk) session.delete(query) try: session.commit() except exc.IntegrityError as err: # DETAIL: Key (SampleID)=(120) is still referenced from table "assay". if "Key" and "is still referenced from table" in str(err): m = re.search('(DETAIL:)[\w|\s|\(|\)\|=|"]+\W', str(err)) m = str(m.group(0)).partition("DETAIL:") messages.add_message(request, messages.WARNING, m[2]) session.rollback() else: messages.add_message( request, messages.WARNING, "A unexpected error has been happened") session.rollback() except: messages.add_message( request, messages.WARNING, "A unexpected error has been happened") session.rollback() finally: session.close() cols, tks, data, table_key = update(reflector, table_key) return render(request, 'mainapp/reflector.html', { 'tks': tks, 'cols': cols, 'data': data, 'table_key': table_key })
def generic_add(request, table_key, oid=None): engineURL = request.session.get('engineURL') reflector = Reflector(engineURL) reflector.reflectTables() exist = reflector.exist_table(table_key) if not exist: msg = "Please verify that the table: '{}' does not exist.".format( table_key) messages.add_message(request, messages.WARNING, msg) return redirect('mainapp:reflector') table = reflector.getOg_table(str(table_key)) fields = fields_generator(table) generic_model = create_model('generic', attrs=fields) class MyGenericModelForm(MyModelForm): class Meta: model = generic_model fields = '__all__' def __init__(self, *args, **kwargs): super(MyGenericModelForm, self).__init__(*args, **kwargs) for field in self.fields.values(): field.widget.attrs.update({'class': 'form-control'}) def clean(self): super(MyGenericModelForm, self).clean() if 'FROM' and 'TO' in self.fields.keys(): _from = self.cleaned_data.get('FROM') _to = self.cleaned_data.get('TO') if _from >= _to: raise forms.ValidationError( {'FROM': "FROM can't be greather or igual than TO"}) if request.method == "POST": Base = declarative_base() generic_object = type(str(table_key), (Base, ), defineObject(table)) form = MyGenericModelForm(request.POST) if "update" in request.POST: oid = request.POST.get('oid') pks = eval(str(oid)) action = ("update", pks) elif "insert" in request.POST: action = ("insert", ) if form.is_valid(): data = form.cleaned_data # Example: # Object_table = surveytable(BHID = 3.7, at = '2.0', az = 14.0, dip = 14.0 ,Comments = 'hello') # session.add(Object_table) session = reflector.make_session() if "update" in request.POST: Base = declarative_base() query = session.query(generic_object).get(pks) value = query.__dict__.copy() del value['_sa_instance_state'] form = MyGenericModelForm(request.POST, initial=value) if form.has_changed(): for k in form.changed_data: query.__setattr__(k, data[k]) else: Object_table = generic_object(**data) session.add(Object_table) try: session.commit() # session.flush() except exc.IntegrityError as err: # (psycopg2.IntegrityError) insert or update on table "assay" violates foreign key constraint "chk_bhid" # DETAIL: Key (BHID)=(fddf) is not present in table "collar". session.rollback() if "violates foreign key constraint" in str(err): m = re.search('(DETAIL:).+\W', str(err)) m = str(m.group(0)).partition("DETAIL:") messages.add_message(request, messages.WARNING, m[2]) messages.add_message( request, messages.INFO, 'Please verify all foreign key constraints.') return render(request, 'mainapp/row_add.html', { 'form': form, 'table_key': table_key, 'action': action }) # postgresql UNIQUE constraint error elif "duplicate key value violates unique constraint" in str( err): m = re.search('(DETAIL:).+\W', str(err)) m = str(m.group(0)).partition("DETAIL:") messages.add_message(request, messages.WARNING, m[2]) messages.add_message( request, messages.INFO, 'Please verify all unique constraints.') return render(request, 'mainapp/row_add.html', { 'form': form, 'table_key': table_key, 'action': action }) # sqlite UNIQUE constraint error elif "UNIQUE constraint failed" in str(err): m = re.search('(UNIQUE).+\[SQL', str(err)) m = str(m.group(0)).partition("UNIQUE") m = str(m[1]) + (str(m[2]).strip('[SQL')) messages.add_message(request, messages.WARNING, m) messages.add_message( request, messages.INFO, 'Duplicate key value violates unique constraint.') return render(request, 'mainapp/row_add.html', { 'form': form, 'table_key': table_key, 'action': action }) else: messages.add_message(request, messages.WARNING, str(err)) return render(request, 'mainapp/row_add.html', { 'form': form, 'table_key': table_key, 'action': action }) except: raise finally: session.close() return redirect( reverse('mainapp:reflector', kwargs={'table_key': table_key})) else: return render(request, 'mainapp/row_add.html', { 'form': form, 'table_key': table_key, 'action': action }) elif oid is not None and request.method == "GET": pks = oid.split(',') Base = declarative_base() object_table = type(str(table_key), (Base, ), defineObject(table)) if pks: session = reflector.make_session() try: query = session.query(object_table).get(pks) except exc.InvalidRequestError as err: messages.add_message(request, messages.WARNING, str(err)) return redirect( reverse('mainapp:reflector', kwargs={'table_key': table_key})) if query: value = query.__dict__.copy() del value['_sa_instance_state'] model = generic_model(**value) form = MyGenericModelForm(instance=model) session.close() action = ("update", pks) else: msg = "Please verify: The row you try to update does not exist." messages.add_message(request, messages.WARNING, msg) return redirect( reverse('mainapp:reflector', kwargs={'table_key': table_key})) # -------------------------------- else: action = ("insert", ) form = MyGenericModelForm() return render(request, 'mainapp/row_add.html', { 'form': form, 'table_key': table_key, 'action': action })
def new(request): if request.method == "GET": form = NewForm() return render( request, 'mainapp/new.html', { 'form': form, 'ref': 'new', 'files_explorer': settings.files_explorer, 'directory_content': get_folder_content("/") }) elif request.method == "POST": form = NewForm(request.POST) if form.is_valid(): db_type = form.cleaned_data.get('db_type') dbname_to_create = form.cleaned_data.get('name') if db_type == 'sqlite': con_string = 'sqlite:///{}.sqlite'.format( os.path.join(request.POST.get('current_path'), dbname_to_create)) elif db_type == 'postgresql': try: con_string = pg_create(user='******', password='******', dbname_to_create=dbname_to_create) # database "lm" already exists except exc.ProgrammingError as err: if "already exists" in str(err): messages.add_message( request, messages.WARNING, 'Database "%s" already exists.' % (dbname_to_create)) messages.add_message( request, messages.INFO, 'Please verify all postgres database names.') return redirect('mainapp:new') error = False try: eng, meta = og_connect(con_string) # Create drillhole definition tables in the metadata, collar and survey. og_create_dhdef(eng, meta) except AssertionError as err: if db_type == 'sqlite': messages.add_message( request, messages.WARNING, 'Database "%s" already exists on path: %s.' % (dbname_to_create, request.POST.get('current_path'))) else: messages.add_message(request, messages.WARNING, str(err)) error = True except exc.OperationalError as err: if "unable to open database file" in str(err): messages.add_message( request, messages.WARNING, 'Unable to create sqlite database file "%s.sqlite" on path: %s.' % (dbname_to_create, request.POST.get('current_path'))) else: messages.add_message(request, messages.WARNING, str(err)) error = True except: raise if error: return redirect('mainapp:new') og_system(eng, meta) og_references(eng, meta, table_name='assay_certificate', key='SampleID', cols={'Au': { 'coltypes': Float, 'nullable': True }}) og_references( eng, meta, table_name='rock_catalog', key='RockID', cols={'Description': { 'coltypes': String, 'nullable': True }}) og_add_interval(eng, meta, table_name='assay', cols={ 'SampleID': { 'coltypes': String, 'nullable': False, 'foreignkey': { 'column': 'assay_certificate.SampleID', 'ondelete': 'CASCADE', 'onupdate': 'CASCADE' } } }) og_add_interval(eng, meta, table_name='litho', cols={ 'RockID': { 'coltypes': String, 'nullable': True, 'foreignkey': { 'column': 'rock_catalog.RockID', 'ondelete': 'CASCADE', 'onupdate': 'CASCADE' } } }) execute(eng, meta) # -Register tables on system table: OG_SMDH_SYSTEM------------------------# table_key = 'OG_SMDH_SYSTEM' tdata = [{ 'Table': 'survey', 'Type': 'definition (survey)', 'Comments': '' }, { 'Table': 'collar', 'Type': 'definition (collar)', 'Comments': '' }, { 'Table': 'assay_certificate', 'Type': 'reference', 'Comments': '' }, { 'Table': 'rock_catalog', 'Type': 'reference', 'Comments': '' }, { 'Table': 'assay', 'Type': 'interval', 'Comments': '' }, { 'Table': 'litho', 'Type': 'interval', 'Comments': '' }] reflector = Reflector(con_string) reflector.reflectTables() table = reflector.getOg_table(table_key) Base = declarative_base() generic_object = type(str(table_key), (Base, ), defineObject(table)) session = reflector.make_session() for data in tdata: Object_table = generic_object(**data) session.add(Object_table) try: session.commit() # session.flush() except: session.rollback() finally: session.close() # -END----------------------# request.session['engineURL'] = con_string request.session['db_type'] = db_type request.session['db_name'] = dbname_to_create return redirect('mainapp:dashboard')