def init_test_database():
# My idea is that the tests run under the same database constraints
# as production. That is, the code can only change data, not database
# structure.
admin_url = configuration.get("Database", "admin_url")
client_url = configuration.get("Database", "url")
# login, password, dbname, host, port = _extract_db_params_from_url(client_url)
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, koi.db_mapping.metadata, echo_query=False)
mainlog.debug("Initializing DB for tests")
create_root_account()
do_basic_inserts(do_sequence=False)
init_sequences(session())
add_user('dd', 'dd', 'Gandalf', 'TimeTrackModify,ModifyParameters')
session().flush()
session().expunge_all()
def init_base():
init_logging()
mainlog.setLevel(logging.INFO)
load_configuration("server.cfg")
parser = argparse.ArgumentParser(formatter_class=argparse.RawDescriptionHelpFormatter,
description='This is an Horse! migration script.',
epilog="For example --db-url {}".format(
configuration.get("Database", "admin_url")))
parser.add_argument('--db-url', default=configuration.database_url, help='Database URL')
args = parser.parse_args()
mainlog.info("Connecting to {}".format(args.db_url))
init_i18n()
from koi.db_mapping import metadata
init_db_session(args.db_url, metadata, False) # True or configuration.echo_query)
def create_root_account(self):
if not configuration.get("Database", "url"):
self._log_error("Can't read Database/url ini config file")
return
login = "******"
password = "******"
self._clear_log()
self._log("<b>Creating or recreating a root account")
try:
init_db_session(configuration.get("Database", "url"))
create_root_account(login, password)
self._log_success(
"Root account successfully reset to login:{}, password:{}".
format(login, password))
except Exception as ex:
self._log_error("Root account creation failed")
self._log_error(ex)
def base_init():
global services, json_rpc_dispatcher
init_db_session(configuration.get('Database', 'url'), metadata, False
or configuration.echo_query)
dao.set_session(session())
json_rpc_dispatcher = HorseJsonRpc()
make_server_json_server_dispatcher(
json_rpc_dispatcher,
JsonCallWrapper(ClockService(), JsonCallWrapper.CHERRYPY_MODE))
make_server_json_server_dispatcher(
json_rpc_dispatcher,
JsonCallWrapper(DocumentsService(), JsonCallWrapper.CHERRYPY_MODE))
make_server_json_server_dispatcher(
json_rpc_dispatcher,
JsonCallWrapper(IndicatorsService(), JsonCallWrapper.CHERRYPY_MODE))
services = Services()
services.register_for_server(session, Base)
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)
)
session().connection().execute(
"alter table horse.filter_queries add constraint fq_by_name unique (family,name);"
)
session().connection().execute(
"CREATE TYPE horse.ck_special_activity_type AS ENUM ('holidays','partial_activity','unemployment','sick_leave','other');"
)
session().connection().execute(
"ALTER TABLE horse.special_activities ADD COLUMN activity_type horse.ck_special_activity_type NOT NULL DEFAULT 'other';"
)
session().commit()
def alter_data():
# session().connection().execute("""
# update horse.delivery_slip_parts
# set sell_price = horse.delivery_slip_parts.quantity_out * horse.order_parts.sell_price
# from horse.order_parts
# where horse.order_parts.order_part_id = horse.delivery_slip_parts.order_part_id
# """
session().commit()
args = parser.parse_args()
init_db_session(args.db_url, metadata, True or configuration.echo_query)
alter_structure()
alter_data()
if __name__ == "__main__":
from koi.base_logging import init_logging
from koi.Configurator import init_i18n, load_configuration, configuration
init_logging("rlab.log")
init_i18n()
load_configuration()
from koi.db_mapping import metadata
from koi.datalayer.database_session import init_db_session
init_db_session(configuration.database_url, metadata, False
or configuration.echo_query)
import os
from docxtpl import DocxTemplate
from koi.dao import dao
from koi.server.json_decorator import JsonCallWrapper
from koi.doc_manager.documents_service import DocumentsService
from koi.doc_manager.client_utils import download_document
from koi.reporting.utils import make_home_file
from koi.portability import open_a_file_on_os
from koi.translators import amount_to_s
HORSE_REFERENCE = "order_confirmation_letter"
HORSE_TEMPLATE = "order_confirmation_report.docx"
HORSE_TITLE = _("Order confirmation template")
def print_order_confirmation_report(order_id):
def dirup(path):
return os.path.split(os.path.abspath(path))[0]
updir = dirup(dirup(__file__))
# sys.path.append(updir)
# sys.path.append(os.path.join(updir,'server'))
# sys.path.append(os.path.join(updir,'datalayer'))
from koi.Configurator import init_i18n, load_configuration, resource_dir
from koi.base_logging import init_logging
init_logging("test.log")
init_i18n()
# load_configuration(os.path.join( resource_dir,'test_config.cfg'))
load_configuration('config.cfg')
# load_configuration() # Default run on my PC
from koi.Configurator import mainlog, configuration
import koi.db_mapping
from koi.datalayer.database_session import init_db_session, session
init_db_session(configuration.database_url, koi.db_mapping.metadata, True
or configuration.echo_query)
from koi.db_mapping import *
from koi.dao import *
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")
db_url = args.db_url
db_admin_url = args.db_admin_url
if args.db_host:
db_url = re.sub('@.*:', '@{}:'.format(args.db_host), db_url)
db_admin_url = re.sub('@.*:', '@{}:'.format(args.db_host),
db_admin_url)
if args.reset_database:
create_blank_database(db_admin_url, db_url)
else:
parser.print_help()
exit()
# init_db_session("postgresql://stefan:@127.0.0.1:5432/horsedb", metadata, False or configuration.echo_query)
init_db_session("postgresql://stefan:@127.0.0.1:5432/test_db", metadata,
False or configuration.echo_query)
create_all_tables(session())
# add_user(100,'admin','admin','Administrator','TimeTrackModify,ModifyParameters')
employee = Employee()
employee.login = "******"
h = hashlib.md5()
h.update("admin")
employee.password = h.hexdigest()
employee.roles = RoleType.symbols()
employee.fullname = 'Administrator'
session().add(employee)
session().connection().execute(
description='This is an Horse! migration script.')
parser.add_argument('--db-url',
default=configuration.database_url,
help='Database connection URL {}'.format(
configuration.get("Database", "admin_url")))
from sqlalchemy import create_engine
from sqlalchemy.schema import CreateTable
# def dump(sql, *multiparams, **params):
# print(sql.compile(dialect=engine.dialect))
# engine = create_engine('postgresql://', strategy='mock', executor=dump)
# print(CreateTable(Operation.__table__).compile(engine))
args = parser.parse_args()
init_db_session("postgresql://*****:*****@127.0.0.1:5432/horsedb",
metadata, True or configuration.echo_query)
people = dict()
interval = 2
steps = 8
for i in range(steps):
session().connection().execute("SET search_path to horse,public")
r = session().connection().execute("""
with
part_done_in_period as (
select *
from (
select part.order_part_id, part.quantity, max(ds.creation) as done_date, sum(quantity_out) as all_out
from order_parts part
from koi.dao import dao
if __name__ == "__main__":
import sys
from PySide.QtGui import QApplication
from koi.base_logging import init_logging
from koi.Configurator import init_i18n, load_configuration, configuration
init_logging()
init_i18n()
load_configuration()
from koi.db_mapping import metadata
from koi.datalayer.database_session import init_db_session, session
init_db_session(configuration.database_url, metadata,
configuration.echo_query or False)
from datetime import timedelta
from PySide.QtCore import Slot, Qt
from PySide.QtGui import QDialog, QDialogButtonBox, QPushButton
from PySide.QtGui import QWidget, QVBoxLayout, QHBoxLayout, QLineEdit, QLabel, QHeaderView
from koi.db_mapping import OperationDefinition, OperationDefinitionPeriod
from koi.datalayer.database_session import session
from koi.gui.dialog_utils import TitleWidget, showErrorBox
from koi.gui.ProxyModel import TextLinePrototype, BooleanPrototype, DatePrototype, Prototype, MoneyPrototype, TrackingProxyModel, DBObjectActionTypes
from koi.datalayer.generic_access import blank_dto
from koi.date_parser import SimpleDateParser
from koi.gui.MetaFormDialog import MetaFormDialog
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
from datetime import datetime
from PySide.QtCore import *
from PySide.QtGui import *
# from PySide.QtGui import QDesktopServices
# from PySide.QtCore import QUrl
# from PySide.QtTest import QTest
from koi.tools.chrono import *
from koi.datalayer.sqla_mapping_base import metadata
from koi.datalayer.database_session import init_db_session,session
from koi.datalayer.connection import check_db_connection
try:
init_db_session(configuration.database_url, metadata, args.echo_sql)
except Exception as e:
mainlog.error(e)
showErrorBox( _("Can't initialize the database connection"),
_("I have tried this url : {}. And got this problem : {}").format(
configuration.database_url, str(e)))
splash.repaint()
from koi.db_mapping import Order,OrderPart
from koi.datalayer.employee_mapping import RoleType
from koi.gui.AboutBox import AboutDialog
from koi.gui.AboutDemoBox import AboutDemoDialog
from koi.EditConfigurationDialog import EditConfigurationDialog
splash_msg(_("Checking DB connection"))
check_database = check_db_connection(configuration.database_url)