def list_connections(p_user):
try:
v_table = database.v_connection.Query('''
select c.conn_id as connid,
c.dbt_st_name as technology,
c.server as host,
c.port as port,
c.service as database,
c.user as dbuser
from users u
inner join connections c
on c.user_id = u.user_id
where u.user_name = '{0}'
order by conn_id
'''.format(p_user))
v_cryptor = Utils.Cryptor('omnidb', 'iso-8859-1')
for v_row in v_table.Rows:
v_row['host'] = v_cryptor.Decrypt(v_row['host'])
v_row['port'] = v_cryptor.Decrypt(v_row['port'])
v_row['database'] = v_cryptor.Decrypt(v_row['database'])
v_row['dbuser'] = v_cryptor.Decrypt(v_row['dbuser'])
print(v_table.Pretty())
except Exception as exc:
print('Error:')
print(exc)
def sign_in_automatic(request, username, pwd):
token = request.GET.get('token', '')
valid_token = custom_settings.APP_TOKEN
if valid_token and token != valid_token:
return -1
database = OmniDatabase.Generic.InstantiateDatabase(
'sqlite', '', '', settings.OMNIDB_DATABASE, '', '', '0', '', True)
table = database.v_connection.Query('''
select u.user_id,
u.password,
t.theme_id,
t.theme_name,
t.theme_type,
u.editor_font_size,
(case when u.chat_enabled is null then 1 else u.chat_enabled end) as chat_enabled,
(case when u.super_user is null then 0 else u.super_user end) as super_user,
u.csv_encoding,
u.csv_delimiter
from users u,
themes t
where u.theme_id = t.theme_id
and u.user_name = '{0}'
'''.format(username))
if len(table.Rows) > 0:
cryptor = Utils.Cryptor('omnidb', 'iso-8859-1')
pwd_decrypted = cryptor.Decrypt(table.Rows[0]['password'])
if pwd_decrypted == pwd:
#creating session key to use it
try:
request.session.save()
except Exception as exc:
request.session.create()
logger.info('User "{0}" logged in.'.format(username))
v_session = Session(
table.Rows[0]["user_id"], username, database,
table.Rows[0]["theme_name"], table.Rows[0]["theme_type"],
table.Rows[0]["theme_id"], table.Rows[0]["editor_font_size"],
int(table.Rows[0]["chat_enabled"]),
int(table.Rows[0]["super_user"]), cryptor,
request.session.session_key, table.Rows[0]["csv_encoding"],
table.Rows[0]["csv_delimiter"])
#v_session.RefreshDatabaseList()
request.session['omnidb_session'] = v_session
if not request.session.get('cryptor'):
request.session['cryptor'] = cryptor
return len(v_session.v_databases)
return -1
def sign_in(request):
v_return = {}
v_return['v_data'] = -1
v_return['v_error'] = False
v_return['v_error_id'] = -1
json_object = json.loads(request.POST.get('data', None))
username = json_object['p_username']
pwd = json_object['p_pwd']
database = OmniDatabase.Generic.InstantiateDatabase(
'sqlite', '', '', settings.OMNIDB_DATABASE, '', '', '0', '', True)
table = database.v_connection.Query('''
select u.user_id,
u.password,
t.theme_id,
t.theme_name,
t.theme_type,
u.editor_font_size,
(case when u.chat_enabled is null then 1 else u.chat_enabled end) as chat_enabled,
(case when u.super_user is null then 0 else u.super_user end) as super_user,
u.user_key
from users u,
themes t
where u.theme_id = t.theme_id
and u.user_name = '{0}'
'''.format(username))
if len(table.Rows) > 0:
cryptor = Utils.Cryptor('omnidb', 'iso-8859-1')
pwd_decrypted = cryptor.Decrypt(table.Rows[0]['password'])
if pwd_decrypted == pwd:
#creating session key to use it
request.session.save()
logger.info('User "{0}" logged in.'.format(username))
v_session = Session(table.Rows[0]["user_id"], username, database,
table.Rows[0]["theme_name"],
table.Rows[0]["theme_type"],
table.Rows[0]["theme_id"],
table.Rows[0]["editor_font_size"],
int(table.Rows[0]["chat_enabled"]),
int(table.Rows[0]["super_user"]), cryptor,
request.session.session_key)
v_session.RefreshDatabaseList()
request.session['omnidb_session'] = v_session
if not request.session.get('cryptor'):
request.session['cryptor'] = cryptor
v_return['v_data'] = len(v_session.v_databases)
return JsonResponse(v_return)
def create_superuser(p_user, p_pwd):
try:
print('Creating superuser...')
v_cryptor = Utils.Cryptor("omnidb")
database.v_connection.Execute('''
insert into users values (
(select coalesce(max(user_id), 0) + 1 from users),'{0}','{1}',1,'14',1,1,'{2}')
'''.format(p_user, v_cryptor.Encrypt(p_pwd), str(uuid.uuid4())))
print('Superuser created.')
except Exception as exc:
print('Error:')
print(exc)
def create_connection(p_username, p_technology, p_host, p_port, p_database,
p_dbuser):
try:
v_users = database.v_connection.Query('''
select *
from users
where user_name = '{0}'
'''.format(p_username))
if len(v_users.Rows) > 0:
v_technologies = database.v_connection.Query('''
select *
from db_type
where dbt_in_enabled = 1
and dbt_st_name = '{0}'
'''.format(p_technology))
if len(v_technologies.Rows) > 0:
print('Creating connection...')
v_cryptor = Utils.Cryptor('omnidb', 'iso-8859-1')
database.v_connection.Execute('''
insert into connections values (
(select coalesce(max(conn_id), 0) + 1 from connections),
{0}, '{1}', '{2}', '{3}', '{4}', '{5}',
'', '', '', '', '', '', 0, ''
)
'''.format(
v_users.Rows[0]['user_id'],
p_technology,
v_cryptor.Encrypt(p_host),
v_cryptor.Encrypt(p_port),
v_cryptor.Encrypt(p_database),
v_cryptor.Encrypt(p_dbuser),
))
print('Connection created.')
else:
print('Technology {0} does not exist.'.format(p_technology))
else:
print('User {0} does not exist.'.format(p_user))
except Exception as exc:
print('Error:')
print(exc)
def create_superuser(p_user, p_pwd):
try:
print('Creating superuser...')
v_cryptor = Utils.Cryptor('omnidb', 'iso-8859-1')
database.v_connection.Execute('''
insert into users values (
(select coalesce(max(user_id), 0) + 1 from users),'{0}','{1}',1,'14',1,1,'utf-8',';','11')
'''.format(p_user, v_cryptor.Hash(v_cryptor.Encrypt(p_pwd))))
print('Superuser created.')
#database.v_connection.Execute('''
# insert into users_channels (
# use_in_code,
# cha_in_code,
# usc_bo_silenced
# ) values (
# 1,
# 1,
# 0
# )
# '''
#)
except Exception as exc:
print('Error:')
print(exc)
def migration_main(p_old_db_file, p_interactive, p_logger):
config_object = Config.objects.all()[0]
perform_migration = False
if not config_object.mig_2_to_3_done:
perform_migration = True
elif p_interactive:
value = input(
'Target database already migrated from OmniDB 2, continue anyway? (y/n) '
)
if value.lower() == 'y':
perform_migration = True
if perform_migration:
log_message(
p_logger, 'info',
'Attempting to migrate users, connections and monitoring units and snippets from OmniDB 2 to 3...'
)
database = OmniDatabase.Generic.InstantiateDatabase(
'sqlite', '', '', p_old_db_file, '', '', '0', '')
# Check that OmniDB 2 required tables exist
migration_enabled = True
try:
database.v_connection.Query('''
select count(1)
from users
''')
except:
migration_enabled = False
try:
database.v_connection.Query('''
select count(1)
from connections
''')
except:
migration_enabled = False
if not migration_enabled:
log_message(
p_logger, 'info',
'Source database file does not contain the required tables, skipping...'
)
config_object.mig_2_to_3_done = True
config_object.save()
else:
try:
transaction.set_autocommit(False)
cryptor = Utils.Cryptor('omnidb', 'iso-8859-1')
v_users = database.v_connection.Query('''
select user_id as userid,
user_name as username,
super_user as superuser
from users
order by user_id
''')
for user in v_users.Rows:
# Try to get existing user
try:
log_message(
p_logger, 'info',
"Creating user '{0}'...".format(user['username']))
user_object = User.objects.get(
username=user['username'])
log_message(
p_logger, 'info',
"User '{0}' already exists.".format(
user['username']))
except:
# Creating the user
user_object = User.objects.create_user(
username=user['username'],
password='******',
email='',
last_login=timezone.now(),
is_superuser=user['superuser'] == 1,
first_name='',
last_name='',
is_staff=False,
is_active=True,
date_joined=timezone.now())
log_message(
p_logger, 'info',
"User '{0}' created.".format(user['username']))
# User connections
v_connections = database.v_connection.Query('''
select coalesce(dbt_st_name,'') as dbt_st_name,
coalesce(server,'') as server,
coalesce(port,'') as port,
coalesce(service,'') as service,
coalesce(user,'') as user,
coalesce(alias,'') as alias,
coalesce(ssh_server,'') as ssh_server,
coalesce(ssh_port,'') as ssh_port,
coalesce(ssh_user,'') as ssh_user,
coalesce(ssh_password,'') as ssh_password,
coalesce(ssh_key,'') as ssh_key,
coalesce(use_tunnel,0) as use_tunnel,
coalesce(conn_string,'') as conn_string
from connections
where user_id = {0}
order by dbt_st_name,
conn_id
'''.format(user['userid']))
if len(v_connections.Rows) == 0:
log_message(
p_logger, 'info',
"User '{0}' does not contain connections in the source database."
.format(user['username']))
else:
log_message(
p_logger, 'info',
"Attempting to create connections of user '{0}'..."
.format(user['username']))
for r in v_connections.Rows:
try:
v_server = cryptor.Decrypt(r["server"])
except Exception as exc:
v_server = r["server"]
try:
v_port = cryptor.Decrypt(r["port"])
except Exception as exc:
v_port = r["port"]
try:
v_service = cryptor.Decrypt(r["service"])
except Exception as exc:
v_service = r["service"]
try:
v_user = cryptor.Decrypt(r["user"])
except Exception as exc:
v_user = r["user"]
try:
v_alias = cryptor.Decrypt(r["alias"])
except Exception as exc:
v_alias = r["alias"]
try:
v_conn_string = cryptor.Decrypt(
r["conn_string"])
except Exception as exc:
v_conn_string = r["conn_string"]
#SSH Tunnel information
try:
v_ssh_server = cryptor.Decrypt(r["ssh_server"])
except Exception as exc:
v_ssh_server = r["ssh_server"]
try:
v_ssh_port = cryptor.Decrypt(r["ssh_port"])
except Exception as exc:
v_ssh_port = r["ssh_port"]
try:
v_ssh_user = cryptor.Decrypt(r["ssh_user"])
except Exception as exc:
v_ssh_user = r["ssh_user"]
try:
v_ssh_password = cryptor.Decrypt(
r["ssh_password"])
except Exception as exc:
v_ssh_password = r["ssh_password"]
try:
v_ssh_key = cryptor.Decrypt(r["ssh_key"])
except Exception as exc:
v_ssh_key = r["ssh_key"]
try:
v_use_tunnel = cryptor.Decrypt(r["use_tunnel"])
except Exception as exc:
v_use_tunnel = r["use_tunnel"]
# Check if connection already exists before creating it
conn = Connection.objects.filter(
user=user_object,
technology=Technology.objects.get(
name=r["dbt_st_name"]),
server=v_server,
port=v_port,
database=v_service,
username=v_user,
password='',
alias=v_alias,
ssh_server=v_ssh_server,
ssh_port=v_ssh_port,
ssh_user=v_ssh_user,
ssh_password=v_ssh_password,
ssh_key=v_ssh_key,
use_tunnel=v_use_tunnel == 1,
conn_string=v_conn_string,
)
if len(conn) > 0:
log_message(
p_logger, 'info',
"Skipping creation of connection with alias '{0}' because an identical connection already exists."
.format(v_alias))
else:
log_message(
p_logger, 'info',
"Creating connection with alias '{0}'...".
format(v_alias))
connection = Connection(
user=user_object,
technology=Technology.objects.get(
name=r["dbt_st_name"]),
server=v_server,
port=v_port,
database=v_service,
username=v_user,
password='',
alias=v_alias,
ssh_server=v_ssh_server,
ssh_port=v_ssh_port,
ssh_user=v_ssh_user,
ssh_password=v_ssh_password,
ssh_key=v_ssh_key,
use_tunnel=v_use_tunnel == 1,
conn_string=v_conn_string,
)
connection.save()
log_message(
p_logger, 'info',
"Connection with alias '{0}' created.".
format(v_alias))
# User snippets
log_message(
p_logger, 'info',
"Attempting to create snippets of user '{0}'...".
format(user['username']))
v_folders = database.v_connection.Query('''
select sn_id, sn_name, sn_id_parent
from snippets_nodes
where user_id = {0}
'''.format(user['userid']))
#Child texts
v_files = database.v_connection.Query('''
select st_id, st_name, sn_id_parent, st_text
from snippets_texts
where user_id = {0}
'''.format(user['userid']))
v_root = {'id': None, 'object': None}
migration_build_snippets_object_recursive(
v_folders, v_files, v_root, user_object, p_logger)
# User monitoring units
log_message(
p_logger, 'info',
"Attempting to create monitoring units of user '{0}'..."
.format(user['username']))
v_units = database.v_connection.Query('''
select title,
case type
when 'chart' then 'chart'
when 'chart_append' then 'timeseries'
when 'grid' then 'grid'
when 'graph' then 'graph'
end type,
interval,
dbt_st_name,
script_chart,
script_data
from mon_units
where user_id = {0}
'''.format(user['userid']))
for unit in v_units.Rows:
unit_object = MonUnits(
user=user_object,
technology=Technology.objects.get(
name=unit['dbt_st_name']),
script_chart=unit['script_chart'],
script_data=unit['script_data'],
type=unit['type'],
title=unit['title'],
is_default=False,
interval=unit['interval'])
unit_object.save()
log_message(
p_logger, 'info',
"Monitoring unit '{0}' created.".format(
unit['title']))
config_object.mig_2_to_3_done = True
config_object.save()
transaction.commit()
log_message(p_logger, 'info', 'Database migration finished.')
except Exception as exc:
log_message(
p_logger, 'error',
'Failed to complete migration, rolled back. Error: {0}'.
format(str(exc)))
sys.exit()
transaction.set_autocommit(True)
