def install(options): from profiteer import sync as sync_module """Installs the system""" # Setup database connection if config.get('db_username') == "username": if config.get('db_password') == "password": print(cli_f.shell_text("[y]No database login[/y]")) print(""" Access to the database has not yet been setup. Open config.json and fill in values for db_host, db_username, db_password and db_name. You can optionally also create values for test and mock databases too. These will allow you to run unit tests involving the database and to trial code against mock data. When ready, run this program again.""") return False # Test database connection try: cursor = database_f.get_cursor() except Exception: print(cli_f.shell_text("[r]Database not accessible[/r]")) print(""" The login details for the database are incorrect. Double check the db_host, db_username, db_password and db_name fields in config.json When ready, run this program again to retry the connection.""") return False print(cli_f.shell_text("[g]Connected to database[/g]")) # Install database o = sync_module.main(fix=True, show_fixes=False, print_output=False) # Insert admin query = """UPDATE users SET password = '******' WHERE id = 1;""".format(user.encode_password('password', 'K*WJgU&j8M) ZT?=J_T-TUfH9*lY#!>@')) try: cursor.execute(query) except Exception as e: raise Exception("Database error: %s\nQuery: %s" % (str(e.args[0]).replace("\n",""), query)) print(cli_f.shell_text("[g]Database installed[/g]")) return True
def get_custom_cursor(username, password, host, dbname, dictionaries=True): try: connection = bpgsql3.connect(None, username, password, host, dbname) except Exception: if username == "" or password == "": print(cli_f.shell_text("[r]The database entries in the config.json file are blank[/r]")) raise return connection.cursor(dictionaries)
def check_table(cursor, table_info, fix=False, show_fixes=False, die_queitly=False): """A wrapper for the check table function, simply so we can have some decent exception handling""" # Run some checks has_pk = False for f in table_info['Fields']: if f.primary_key: has_pk = True if not has_pk: return cli_f.shell_text("''%s'' has [r]no primary key[/r]" % table_info['Name']) # Now to actually build it try: return check_table_fields(cursor, table_info, fix=fix, show_fixes=show_fixes) except Exception: if die_queitly: return cli_f.shell_text("ERROR: Was not able to create [r]%s[/r]" % table_info['Name']) else: print("Error on table: %s" % table_info['Name']) raise
def backup(options): """Backs up the database""" file_path = "{}/backup/{}_{}_{}.sql".format( sys.path[0], datetime.date.today().day, datetime.date.today().month, datetime.date.today().year, ) args = "-h {host} -U {user} {db}".format( host = config.get("db_host"), user = config.get("db_username"), db = config.get("db_name") ) os.system("/Library/PostgreSQL/bin/pg_dump {} -f {}".format(args, file_path)) if options.verbose: print(cli_f.shell_text("[g]Database backed up[/g]")) return file_path
def check_table_fields(cursor, table_info, fix=False, show_fixes=False): """Compares the table to the item, if fix is enabled then it changes things, if not then it just issues warnings""" surplus_columns = [] missing_columns = [] broken_columns = [] surplus_indexes = [] missing_indexes = [] surplus_foreign = [] missing_foreign = [] broken_foreign = [] fixes = [] found_columns = [] found_indexes = [] found_foreign = [] # Get the table OID query = "SELECT c.oid FROM pg_catalog.pg_class c WHERE c.relname = '{0}'".format(table_info['Name']) try: cursor.execute(query) except Exception as e: raise Exception("Database error: %s\nQuery: %s" % (str(e.args[0]).replace("\n",""), query)) table_oid = None for row in cursor: table_oid = row['oid'] # Get columns query = "SELECT column_default, data_type, column_name FROM INFORMATION_SCHEMA.COLUMNS where table_name = '%s'" % table_info['Name'] try: cursor.execute(query) except Exception as e: raise Exception("Database error: %s\nQuery: %s" % (str(e.args[0]).replace("\n",""), query)) # Full list of fields # {'character_octet_length': 1073741824, 'table_schema': 'public', 'character_maximum_length': 50, 'ordinal_position': 1, 'udt_catalog': 'rob3', 'scope_catalog': None, 'collation_catalog': None, 'domain_schema': None, 'maximum_cardinality': None, 'identity_increment': None, 'is_self_referencing': 'NO', 'is_generated': 'NEVER', 'domain_name': None, 'is_updatable': 'YES', 'dtd_name': '1', 'identity_minimum': None, 'table_name': 'artefacts', 'collation_name': None, 'numeric_scale': None, 'domain_catalog': None, 'data_type': 'character varying', 'generation_expression': None, 'numeric_precision_radix': None, 'interval_precision': None, 'scope_name': None, 'collation_schema': None, 'column_default': "''::character varying", 'udt_name': 'varchar', 'numeric_precision': None, 'identity_cycle': None, 'identity_start': None, 'udt_schema': 'pg_catalog', 'character_set_name': None, 'scope_schema': None, 'interval_type': None, 'identity_generation': None, 'character_set_schema': None, 'is_nullable': 'NO', 'is_identity': 'NO', 'datetime_precision': None, 'character_set_catalog': None, 'table_catalog': 'rob3', 'column_name': 'name', 'identity_maximum': None} # List we may care about # 'character_maximum_length': 50, # 'data_type': 'character varying' # 'column_default': "''::character varying" # 'is_nullable': 'NO' # column_name': 'name' # No rows means the table is not there if cursor.rowcount == 0: if fix: o = create_table(cursor, table_info, show_fixes) return o else: if show_fixes: o = create_table(cursor, table_info, show_fixes) return "\n".join([o, cli_f.shell_text("[r]Table missing[/r] (%s)" % table_info['Name'])]) else: return cli_f.shell_text("[r]Table missing[/r] (%s)" % table_info['Name']) # We're not remaking the table, lets check indexes field_list = [f.name for f in table_info['Fields']] for row in cursor: # Find out if this column should exist if row["column_name"] in field_list: found_columns.append(row["column_name"]) # Now to make sure it's configured correctly field_def = table_info['Fields'][field_list.index(row["column_name"])] fixes.extend(field_def.check_default(row['column_default'], table_info)) fixes.extend(field_def.check_type(row['data_type'], table_info)) # Not in field list else: surplus_columns.append(row['column_name']) fixes.append("alter table %s drop %s" % (table_info['Name'], row["column_name"])) # Any leftover properties? for f in table_info["Fields"]: if f.name not in found_columns: missing_columns.append(f.name) fixes.append("alter table %s add column %s" % (table_info['Name'], f.create_column())) # Any indexes? - Query comes from PhpPgAdmin query = """SELECT c2.relname AS indname, i.indisprimary, i.indisunique, pg_get_indexdef(i.indexrelid) AS inddef, obj_description(c.oid, 'pg_index') AS idxcomment FROM pg_class c, pg_class c2, pg_index i WHERE c.relname = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid""" % table_info['Name'] try: cursor.execute(query) except Exception as e: raise Exception("Database error: %s\nQuery: %s" % (str(e.args[0]).replace("\n",""), query)) for row in cursor: if row["indisprimary"] == True: continue real_index_name = row["indname"].replace('%s_index_' % table_info['Name'], '') if real_index_name in table_info['Indexes']: found_indexes.append(real_index_name) else: surplus_indexes.append(real_index_name) fixes.append("drop index %s;" % (row["indname"])) # Any leftover indexes? for i_name, i_content in table_info['Indexes'].items(): if not (i_name in found_indexes): missing_indexes.append(i_name) fixes.append("create index %s_index_%s on %s (%s);" % (table_info['Name'], i_name, table_info['Name'], i_content)) # Foreign keys? query = """SELECT conname, pg_catalog.pg_get_constraintdef(r.oid, true) as condef FROM pg_catalog.pg_constraint r WHERE r.conrelid = '{0}' AND r.contype = 'f'""".format(table_oid) try: cursor.execute(query) except Exception as e: raise Exception("Database error: %s\nQuery: %s" % (str(e.args[0]).replace("\n",""), query)) for row in cursor: x = foreign_key_re.search(row["condef"]) if x != None: found = False for f in table_info["Fields"]: if f.name == x.groups()[0]: found = True if f.foreign_table == "" and f.foreign_col == "": surplus_foreign.append(f.name) fixes.append("ALTER TABLE {0} DROP CONSTRAINT {1};".format(table_info['Name'], row['conname'])) elif f.foreign_table != x.groups()[1] or f.foreign_col != x.groups()[2]: # Wrong foreign broken_foreign.append(f.name) raise Exception("No fix created") # fixes.append("ALTER TABLE {0} ADD CONSTRAINT fk{1} FOREIGN KEY ({1}) REFERENCES {2} ({3});".format(table_info['Name'], f.name, f.foreign_table, f.foreign_col)) else: found_foreign.append(f.name) if not found: missing_foreign.append(f.name) raise Exception("No fix created") for f in table_info["Fields"]: if f.foreign_table != "" or f.foreign_col != "": if f.name not in found_foreign: missing_foreign.append(f.name) fixes.append("ALTER TABLE {0} ADD CONSTRAINT fk{1} FOREIGN KEY ({1}) REFERENCES {2} ({3});".format(table_info['Name'], f.name, f.foreign_table, f.foreign_col)) # Are we green? if len(fixes) == 0: return cli_f.shell_text("[g]%s is correct[/g]" % table_info['Name']) else: output = [] # Columns if fix: for f in fixes: try: cursor.execute(f) except Exception as e: if "REFERENCES" in f: print(f) raise Exception("Database error: %s\nQuery: %s" % (str(e.args[0]).replace("\n",""), query)) pass else: print("ERROR") print(fixes) print("") print(f) print("") raise if show_fixes: print("\n".join(fixes)) output.append(cli_f.shell_text("%s fixed" % table_info['Name'])) return "\n".join(output) else: for c in surplus_columns: output.append(cli_f.shell_text("[r]column surplus in %s[/r] (%s)" % (table_info['Name'], c))) for c in missing_columns: output.append(cli_f.shell_text("[r]column missing in %s[/r] (%s)" % (table_info['Name'], c))) for c in broken_columns: output.append(cli_f.shell_text("[r]column broken in %s[/r] (%s)" % (table_info['Name'], c))) # Indexes for i in surplus_indexes: output.append(cli_f.shell_text("[r]index surplus in %s[/r] (%s)" % (table_info['Name'], i))) for i in missing_indexes: output.append(cli_f.shell_text("[r]index missing in %s[/r] (%s)" % (table_info['Name'], i))) # Foreign keys for f in broken_foreign: output.append(cli_f.shell_text("[r]bad foreign in %s[/r] (%s)" % (table_info['Name'], f))) for f in missing_foreign: output.append(cli_f.shell_text("[r]missing foreign for %s[/r] (%s)" % (table_info['Name'], f))) for f in surplus_foreign: output.append(cli_f.shell_text("[r]surplus foreign in %s[/r] (%s)" % (table_info['Name'], f))) if show_fixes: output.append(" Fixes:") for f in fixes: output.append(f) output.append("") return "\n".join(output) return cli_f.shell_text("[g]%s is correct[/g]" % table_info['Name'])
def create_table(cursor, table_info, show_fixes=False): """Creates a table from the item""" query = "drop table %s;" % table_info['Name'] try: cursor.execute(query) except Exception as e: pass query = ["create table %s" % table_info['Name']] query.append("(") # for prop, prop_type in my_item.property_dict.items(): pks = [] for f in table_info['Fields']: if f.primary_key: pks.append(f.name) query.append("%s," % f.create_column()) query.append("primary key (%s)" % (",".join(pks))) query.append(")") # Bring it all together query = "\n".join(query) if show_fixes: output = [query] else: try: cursor.execute("BEGIN") cursor.execute(query) except Exception as e: cursor.execute("ROLLBACK") raise Exception("Database error: %s\nQuery: %s" % (str(e.args[0]).replace("\n",""), query)) output = [cli_f.shell_text('%s created [g]successfully[/g]' % table_info['Name'])] # Default data if "Data" in table_info: for q in table_info['Data']: if show_fixes: output.append(q) else: try: cursor.execute(q) except Exception as e: raise Exception("Database error: %s\nQuery: %s" % (str(e.args[0]).replace("\n",""), q)) # Any indexes? - Query comes from PhpPgAdmin index_queries = [] found_indexes = [] missing_indexes = [] surplus_indexes = [] query = """SELECT c2.relname AS indname, i.indisprimary, i.indisunique, pg_get_indexdef(i.indexrelid) AS inddef, obj_description(c.oid, 'pg_index') AS idxcomment FROM pg_class c, pg_class c2, pg_index i WHERE c.relname = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid""" % table_info['Name'] try: cursor.execute(query) except Exception as e: cursor.execute("ROLLBACK") raise Exception("Database error: %s\nQuery: %s" % (str(e.args[0]).replace("\n",""), query)) for row in cursor: if row["indisprimary"] == True: continue real_index_name = row["indname"].replace('%s_index_' % table_info['Name'], '') if real_index_name in table_info['Indexes']: found_indexes.append(real_index_name) else: surplus_indexes.append(real_index_name) index_queries.append("drop index %s;" % (row["indname"])) # Any leftover indexes? for i_name, i_content in table_info['Indexes'].items(): if not (i_name in found_indexes): missing_indexes.append(i_name) index_queries.append("create index %s_index_%s on %s (%s);" % (table_info['Name'], i_name, table_info['Name'], i_content)) for query in index_queries: if show_fixes: output.append(query) else: try: cursor.execute(query) except Exception as e: cursor.execute("ROLLBACK") raise Exception("Database error: %s\nQuery: %s" % (str(e.args[0]).replace("\n",""), query)) # TODO make it so the correct index is listed, currently it lists only the last iterated index output.append(cli_f.shell_text('index_%s added to %s [g]successfully[/g]' % (i_name, table_info['Name']))) cursor.execute("COMMIT") return "\n".join(output)