def create_database(self, localhost=False):
# If in command line, skip requesting confirmation for database creation
if self.isVisible() and not self._confirm_dangerous_operation():
return
self._clear_log()
if not configuration.get("Database", "admin_url"):
self._log_error("Can't read Database/admin_url ini config file")
return
if not configuration.get("Database", "url"):
self._log_error("Can't read Database/url ini config file")
return
admin_url = configuration.get("Database", "admin_url")
local_url = configuration.get("Database", "url")
if check_postgres_connection(configuration.get("Database",
"admin_url")):
self._log("Successfuly connected to PostgreSQL server")
else:
self._log_error("Failed to connect to PostgreSQL server")
return False
self._log("<b>Creating a database")
try:
create_blank_database(configuration.get("Database", "admin_url"),
configuration.get("Database", "url"))
self._log("<b><font color='green'>Database created")
except Exception as ex:
self._log_error("Database creation failed")
self._log_error(ex)
disconnect_db()
return
def create_blank_database(admin_url, client_url):
# Do administrative level stuff
set_up_database(admin_url, client_url)
init_db_session(admin_url)
create_all_tables()
disconnect_db()
# Do client level stuff
init_db_session(client_url, metadata, False)
mainlog.info("Creating administration user/employee")
create_root_account()
do_basic_inserts()
session().commit()
# Insert some basic files
# template_id = documents_service.save_template( open( os.path.join(resource_dir, "order_confirmation_report.docx"), "rb"), "order_confirmation_report.docx")
# documents_service.update_template_description( template_id, _("Order confirmation template"), "order_confirmation_report.docx", HORSE_TEMPLATE)
def add_template(description, filename, reference):
with open(os.path.join(resource_dir, "server", filename), "rb") as f:
template_id = documents_service.save_template(f, filename)
documents_service.update_template_description(
template_id, description, filename, reference)
add_template(HORSE_TITLE_PREORDER, HORSE_TEMPLATE_PREORDER,
HORSE_REFERENCE_PREORDER)
add_template(HORSE_TITLE_ORDER_CONFIRMATION,
HORSE_TEMPLATE_ORDER_CONFIRMATION,
HORSE_REFERENCE_ORDER_CONFIRMATION)
def set_up_database(url_admin, url_client):
""" Create the very basic Koi database. That is :
the client user, the admin user, the schema, grant privileges.
:param url_admin:
:param url_client:
:return:
"""
# The administrative user must be "horse_adm"
# He must have the right to create databases and roles
# Just to be sure we're outside any connection
disconnect_db()
db_url, params = parse_db_url(url_client)
login, password, dbname, host, port = extract_db_params_from_url(db_url)
db_url, params = parse_db_url(url_admin)
login_adm, password_adm, dbname, host, port = extract_db_params_from_url(
db_url)
mainlog.info("Admin user is {}, regular user is {}".format(
login_adm, login))
db_url, params = template1_connection_parameters(url_admin)
init_db_session(db_url, params=params)
mainlog.info("creating database")
conn = db_engine().connect()
conn.execute("commit")
conn.execute("drop database if exists {}".format(dbname))
if login_adm != login:
conn.execute("drop role if exists {}".format(login))
conn.execute("CREATE ROLE {} LOGIN PASSWORD '{}'".format(
login, password))
conn.execute(
"ALTER ROLE {} SET statement_timeout = 30000".format(login))
conn.execute("commit") # Leave transaction
conn.execute("CREATE DATABASE {}".format(dbname))
conn.execute("ALTER DATABASE {} SET search_path TO {},public".format(
dbname, DATABASE_SCHEMA))
conn.close()
disconnect_db()
init_db_session(url_admin)
session().commit() # Leave SQLA's transaction
# Schema will be created for current database (i.e. horse or horse_test)
mainlog.info("Creating schema {}".format(DATABASE_SCHEMA))
session().connection().execute("create schema {}".format(DATABASE_SCHEMA))
if login_adm != login:
mainlog.info("Granting privileges to {}".format(login))
session().connection().execute("grant usage on schema {} to {}".format(
DATABASE_SCHEMA, login))
# Alter the default privileges so that every tables and sequences
# created right after will be usable by horse_clt
# Also, if one adds tables, etc. afterwards, they'll benefit from
# the privileges as well
session().connection().execute("""ALTER DEFAULT PRIVILEGES
FOR ROLE {}
IN SCHEMA {}
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO {}""".format(
login_adm, DATABASE_SCHEMA, login))
session().connection().execute("""ALTER DEFAULT PRIVILEGES
FOR ROLE {}
IN SCHEMA {}
GRANT SELECT, UPDATE ON SEQUENCES TO {}""".format(
login_adm, DATABASE_SCHEMA, login))
session().commit()
disconnect_db()
mainlog.info("Database setup complete")
def check_database(self):
self._clear_log()
url = self.url_edit.text()
self._log("<b>Checking database at {}".format(url))
service_installed = False
if platform.system() == "Windows":
cmd = ["sc", "query", self.POSTGRESQL_SERVICE_NAME]
service_installed, stdout, stderr = self._run_shell(cmd)
if check_postgres_connection(url):
self._log_success(
"Successfuly connected with the PostgreSQLserver")
if service_installed == 0:
self._log(
"The {} Windows service seems installed correctly. So the database should resist to a reboot."
.format(self.POSTGRESQL_SERVICE_NAME))
else:
self._log_error(
"I didn't find the {} service in Windows services. Did the installation complete correctly ? If this PC restarts, the database won't start, making the system unusable."
.format(self.POSTGRESQL_SERVICE_NAME))
else:
self._log_error("Unable to connect to PostgreSQL server.")
if service_installed == 0:
self._log(
"The {} service seems installed though. You should locate it in Windows services and start it"
.format(self.POSTGRESQL_SERVICE_NAME))
else:
self._log(
"The {} service is not installed. You should try to install with (see Install services button in this program) or, if that doesn't work, install it manually."
.format(self.POSTGRESQL_SERVICE_NAME))
return False
disconnect_db()
init_db_session(url, None, False)
# check = check_database_connection()
# if check == True:
# self._log_success("Successfuly connected to database")
# else:
# self._log(u"Failed to connect to database. Maybe you should create it or restore a backup ? Error was :")
# self._log_error(check)
# return False
#
# self._log("")
r = check_active_postgres_connections()
disconnect_db()
if r > 1:
self._log("There are {} other people connected".format(r))
self._log("The database seems fine".format(r))
return False
elif r == 1:
self._log("Nobody connected to the database (besides us)")
self._log("The database seems fine".format(r))
else:
self._log_error("Can't check number of connected people...")
# self._log("The database seems broken".format(r))
return False
return True
def reload_config():
disconnect_db()
base_init()
configure_server()
cherrypy.engine.restart()
cherrypy.engine.block()