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 update_name_and_description(self, document_id: int, name: str,
description: str):
if not name:
raise ServerException(ServerErrors.file_name_cannot_be_empty)
# file_name_cannot_be_empty
# raise Exception("Name cannot be empty")
mainlog.debug('Renaming doc:{} to:{} with description:{}'.format(
document_id, name, description))
try:
self._rename_file_in_storage(document_id, name)
except Exception as ex:
mainlog.error("Could not rename document {}".format(document_id))
mainlog.exception(ex)
doc = session().query(Document).filter(
Document.document_id == document_id).one()
doc.description = description or ""
doc.filename = name
audit_trail_service.record("DOCUMENT_RENAMED",
"",
document_id,
commit=False)
session().commit()
def test_self_colleague(self):
order = self._make_order()
dao.order_dao.change_order_state(
order.order_id, OrderStatusType.order_ready_for_production)
employee = self._employee()
operation = order.parts[0].operations[0]
m = Machine()
m.fullname = "Test"
m.is_active = True
m.operation_definition_id = operation.operation_definition_id
session().add(m)
session().commit()
machine_service._reset_cache()
# The reporter himself
d3 = datetime(2012, 12, 31, 9)
self.server.record_pointage_on_operation(
operation.operation_id, employee.employee_id, d3, "Nostromo",
TaskActionReportType.start_task, m.machine_id)
operation, machines, next_action_kind, colleagues = self.server.get_operation_information(
employee.employee_id, operation.operation_id)
assert len(colleagues) == 1
assert colleagues[0].reporter_id == employee.employee_id
assert colleagues[0].kind == TaskActionReportType.start_task
def save_object(self,form_data):
""" Save object hook
"""
c = recursive_defrost_into(form_data, self.mapped_klass)
session().commit()
return getattr(c, self.key_field)
def replace_template(self, template_id, file_handle, file_name):
""" A new template document replaces an old one in our database.
We ask for a file handle because we expect the file to
come from the web server (that's the way cherrypy does it).
"""
mainlog.debug(u"replace_template(): doc_id:{}, fh:{} fn:{}".format(
template_id, file_handle, file_name))
document = session().query(TemplateDocument).filter(
TemplateDocument.template_document_id == template_id).one()
document.filename = file_name
document.upload_date = date.today()
document.server_location = "DUMMY"
document.file_size = 666
document.server_location, document.file_size = self._copy_file_to_storage(
file_handle, document.document_id, file_name)
doc_id = document.document_id # save for use after session's flushed
audit_trail_service.record("TEMPLATE_REPLACED",
"",
document.document_id,
commit=False)
session().commit()
mainlog.debug("Replaced template {}".format(doc_id))
return doc_id
def _gather_data(self, begin=None, end=None):
begin, end = _standard_period(begin, end)
r = session().connection().execute(
"""select CAST( date_trunc('month',start_time) AS DATE) as m, trunc(sum(duration)) as duration
from timetracks
join tasks on tasks.task_id = timetracks.task_id
left outer join tasks_operation_definitions on tasks_operation_definitions.task_id = tasks.task_id
left outer join operation_definitions on (operation_definitions.operation_definition_id = tasks_operation_definitions.operation_definition_id)
where short_id is not null and start_time between timestamp '{}' and timestamp '{}'
group by m
order by m""".format(timestamp_to_pg(begin),
timestamp_to_pg(end))).fetchall()
indirects = [("Indirects", t[0], t[1]) for t in r]
r = session().connection().execute(
"""select CAST( date_trunc('month',start_time) AS DATE) as m, trunc(sum(duration)) as duration from timetracks
join tasks on tasks.task_id = timetracks.task_id
left outer join tasks_operations on tasks_operations.task_id = tasks.task_id
left outer join operations on tasks_operations.operation_id = operations.operation_id
left outer join operation_definitions on operation_definitions.operation_definition_id = operations.operation_definition_id
where short_id is not null and start_time between timestamp '{}' and timestamp '{}'
group by m
order by m;""".format(timestamp_to_pg(begin),
timestamp_to_pg(end))).fetchall()
directs = [("Directs", t[0], t[1]) for t in r]
x_legends, legends, array = to_arrays(directs + indirects)
self.set_data(x_legends, legends, array)
def presence_overview_for_month(self, base_date):
""" Presence overview for employees during a month
"""
day_max = calendar.monthrange(base_date.year, base_date.month)[1]
t_start = datetime(base_date.year, base_date.month, 1)
t_end = datetime(base_date.year, base_date.month, day_max, 23, 59, 59,
9999)
# So this won't select employees without timetracking
presences = session().query(DayTimeSynthesis.employee_id,
DayTimeSynthesis.presence_time,
DayTimeSynthesis.day).\
filter( DayTimeSynthesis.day.between(t_start.date(),t_end.date())).all()
# Make a 'map' of presence time
r = dict()
for p in presences:
if p.employee_id not in r:
r[p.employee_id] = [0] * day_max
r[p.employee_id][p.day.day - 1] += p.presence_time
session().commit()
return r
def apply_changes(inst, change_tracker):
""" Applies updates that were done on a change tracking DTO to the database.
:param inst: Object that was modified
:param change_tracker: Change tracker
:return:
"""
obj_track = dict()
store = dict()
# d = change_tracker.serialize_delta(inst, obj_track, obj_to_modify)
change_tracker.delta_serialize(inst, store, obj_track)
pprint("************************************************ Serialized delta")
pprint(store)
pprint(
"************************************************ / Serialized delta")
change_tracker.apply_delta_on_database_objects(store.values())
# change_tracker.apply_delta_on_database_objects(d)
session().flush()
# pprint("*** Applied delta")
# pprint(d)
key_track = change_tracker.update_delta_with_sqla_keys(store.values())
# pprint("*** Delta with copied keys ")
# pprint(d)
# pprint("*** Updating object with keys from updated delta")
# change_tracker.copy_delta_pk_in_dto(d, inst)
pprint(key_track)
change_tracker.merge_keys(obj_track, key_track)
# We have finished the round trip. Now we can mark the
# object as not changed
change_tracker.clear_changes_recursively(inst)
def create(self, fullname):
employee = Employee()
employee.fullname = fullname
session().add(employee)
session().commit()
self._reload_cache()
return employee
def who_touched_order(self, order_id, not_me_id=None):
if not not_me_id:
not_me_id = user_session.user_id
tlimit = datetime.now() - timedelta(hours=24*31)
# The order_by clause will trigger the loading of
# AuditTrail.when column. Because of that "distinct"
# on employee_id won't work. Therefore I make that
# with a subquery
subq = session().query(AuditTrail.who_id).\
join(OrderPart, OrderPart.order_id == order_id).\
filter(and_( AuditTrail.when > tlimit,
AuditTrail.who_id != not_me_id,
AuditTrail.who_id != None, # is not null
or_(and_(AuditTrail.target_id == order_id,
AuditTrail.what.in_(["CREATE_ORDER","UPDATE_ORDER","ORDER_STATE_CHANGED"])),
and_(AuditTrail.target_id == OrderPart.order_part_id,
AuditTrail.what.in_(["CREATE_ORDER_PART","UPDATE_ORDER_PART","DELETE_ORDER_PART"]))))).order_by(AuditTrail.when).subquery()
res = session().query(Employee.fullname).\
select_from(subq).\
join(Employee, Employee.employee_id == subq.c.who_id).distinct().all()
res = list(map(lambda row:row.fullname, res))
session().commit()
return res
def potential_imputable_task_for_order(self, order_accounting_label):
mainlog.debug(
"potential_imputable_task_for_order : accouting label = {}".format(
order_accounting_label))
tasks = []
order_id = session().query(Order.order_id).filter(
Order.accounting_label == order_accounting_label).scalar()
if order_id:
mainlog.debug("order id = {}".format(order_id))
q = session().query(OperationDefinition.operation_definition_id, OperationDefinition.description, TaskOnOrder.task_on_order_id).\
outerjoin(TaskOnOrder,and_(TaskOnOrder.order_id == order_id,
TaskOnOrder.operation_definition_id == OperationDefinition.operation_definition_id)).\
filter( and_(OperationDefinition.on_order == True,
OperationDefinition.imputable == True)).\
order_by(OperationDefinition.description)
for opdef_id, opdef_desc, task_id in q.all():
task = TaskOnOrderProxy(task_id, opdef_desc, order_id,
opdef_id)
tasks.append(task)
mainlog.debug(task)
return tasks
else:
return []
def pl_migration():
# q = current_session.query(Task.task_id).filter(Task.task_type != 'task_for_presence')
# current_session.query(TaskActionReports).filter(TaskActionReports.kind.in_(TaskActionReportType.start_task,TaskActionReportType.end_task)).delete(False)
# current_session.query(TimeTrack).filter(TimeTrack.task_id.in_(q)).delete(False)
# current_session.query(TaskOnOperation).delete()
# current_session.query(Task).filter(Task.task_type != 'task_for_presence').delete()
# Operation.__table__.drop(engine, checkfirst=True)
print((current_session.query(Operation).delete()))
current_session.commit()
# ProductionFile.__table__.drop(engine, checkfirst=True)
print((current_session.query(ProductionFile).delete()))
current_session.commit()
# DeliverySlipPart.__table__.drop(engine, checkfirst=True)
print((current_session.query(DeliverySlipPart).delete()))
current_session.commit()
# OrderPart.__table__.drop(engine, checkfirst=True)
print((current_session.query(OrderPart).delete()))
current_session.commit()
# Order.__table__.drop(engine, checkfirst=True)
mainlog.debug("deleting orders")
session().connection().execute(
"UPDATE gapless_seq SET gseq_value=(select max(accounting_label) from orders) WHERE gseq_name='order_id'"
)
i = 0
for order in current_session.query(Order).order_by(
desc(Order.accounting_label)).all():
current_session.delete(order)
i += 1
if i % 100 == 0:
print(i)
current_session.commit()
# print current_session.query(Order).delete()
current_session.commit()
# DeliverySlip.__table__.drop(engine, checkfirst=True)
print((current_session.query(DeliverySlip).delete()))
for delivery_slip in current_session.query(DeliverySlip).order_by(
desc(DeliverySlip.delivery_slip_id)).all():
current_session.delete(delivery_slip)
current_session.commit()
# Customer.__table__.drop(engine, checkfirst=True)
print((current_session.query(Customer).delete()))
current_session.commit()
# OperationDefinitionPeriod.__table__.drop(engine, checkfirst=True)
print((current_session.query(OperationDefinitionPeriod).delete()))
current_session.commit()
# OperationDefinition.__table__.drop(engine, checkfirst=True)
print((current_session.query(OperationDefinition).delete()))
current_session.commit()
def categories(self):
r = session().query(
*all_non_relation_columns(DocumentCategory)).order_by(
DocumentCategory.short_name).all()
session().commit()
mainlog.debug("categories() : {}".format(r))
return r
def find_previous_for_supplier(self, supply_order_id, supplier_id):
if not supply_order_id:
order_id = session().query(SupplyOrder.supply_order_id).filter(
SupplyOrder.supplier_id == supplier_id).filter(
SupplyOrder.active == True).first()
if order_id:
order_id = order_id[0]
else:
order_id = session().query(func.max(
SupplyOrder.supply_order_id)).filter(
and_(
SupplyOrder.supplier_id == supplier_id,
SupplyOrder.supply_order_id < supply_order_id)).filter(
SupplyOrder.active == True).scalar()
if not order_id:
order_id = session().query(
func.max(SupplyOrder.supply_order_id)).filter(
and_(SupplyOrder.supplier_id == supplier_id,
SupplyOrder.supply_order_id >=
supply_order_id)).filter(
SupplyOrder.active == True).scalar()
session().commit()
return order_id
def print_preorder(preorder_id):
preorder = dao.order_dao.find_by_id(preorder_id) # open transaction !
filename = make_pdf_filename("PreOrder_{}".format(preorder.preorder_label))
_make_preorder_report(preorder,filename)
session().close()
open_pdf(filename)
return True
def save(self, order_dto, parts_actions):
# mainlog.debug("-"*80)
# for so in session().query(SupplyOrder).all():
# mainlog.debug(so.accounting_label)
supply_order = recursive_defrost_into(order_dto, SupplyOrder,
['_indexed_description'])
if not supply_order.supplier_id:
raise Exception("Missing supply_order.supplier_id")
if not supply_order.tags:
supply_order.tags = []
if not supply_order.accounting_label:
supply_order.accounting_label = gaplessseq('supply_order_id')
if not supply_order.creation_date:
supply_order.creation_date = datetime.date.today()
# if not supply_order.description:
# supply_order.description = ""
session().flush() # grab the (generated) primary key
self._update_supply_order_parts(parts_actions,
supply_order) # MUST commit
# pub.sendMessage('supply_order.changed')
return supply_order.supply_order_id
def deactivate(self, supply_order_id):
# AKA logical delete
session().query(SupplyOrder).filter(
SupplyOrder.supply_order_id == supply_order_id).update(
{"active": False}, synchronize_session=False)
session().commit()
def is_name_used(self, name: str, owner_id: int, family: str) -> bool:
q = session().query(FilterQuery).filter(
and_(FilterQuery.family == family,
FilterQuery.owner_id == owner_id,
FilterQuery.name == name)).count()
session().commit()
return q > 0 # Normally it should always be 0 or 1, nothin else.
def all_on_order_part(self, commit = True):
""" All opdef that can be linked to an operation (i.e.
at the "order part level") when defining a new order
part. """
chrono_click("all_on_order_part 1")
# return session().query(OperationDefinition).\
# filter( OperationDefinition.on_operation == True).\
# order_by(OperationDefinition.description).all()
opdefs = session().query(OperationDefinition).\
filter( OperationDefinition.on_operation == True).\
options(subqueryload(OperationDefinition.periods)).\
order_by(OperationDefinition.description).all()
frozen_opdefs = []
for opdef in opdefs:
chrono_click("all_on_order_part 2 XXX ")
frozen_periods = freeze(opdef.periods, commit=False)
frozen_opdef = freeze(opdef, False, ['periods'] )
frozen_opdef.periods = frozen_periods
frozen_opdefs.append(frozen_opdef)
if commit:
session().commit()
chrono_click("all_on_order_part 2")
return frozen_opdefs
def alter_data():
# For the moment I skip this because I think that adding a role that is not
# supported by the user session might lock people out of Horse
# return
import os
import shutil
q = session().query(Document).order_by(Document.document_id).all()
for document in q:
path, filename = os.path.split(document.server_location)
new_path = documents_service._make_path_to_document(
document.document_id, document.filename)
mainlog.debug(u"Document {} -> filename:{} -- new_name:{}".format(
document.document_id, document.filename, new_path))
try:
shutil.copy(document.server_location, new_path)
document.server_location = new_path
except Exception as ex:
mainlog.error("Unable to copy !")
mainlog.exception(ex)
session().rollback()
return
session().commit()
def __call__(self, *args, **kwargs):
global mainlog
global session # FIXME That's not right : how can we be sure that's the actual session that has thrown the exception ?
try:
r = self.call_decorated(*args, **kwargs)
# mainlog.debug("RollbackDecorator.__call__ calling with args : {} kwargs :{}".format(args, kwargs))
# r = super(RollbackDecorator,self).__call__(*args,**kwargs)
# mainlog.debug("RollbackDecorator.__call__ call complete")
return r
# if self.instance:
# return self.func(self.instance,*args,**kwargs)
# else:
# return self.func(*args)
except Exception as e:
session().rollback()
if type(e) != DataException:
# I assume DataException are handled properly
mainlog.info("Rollback done because of an exception")
mainlog.exception(str(e))
log_stacktrace()
if RollbackDecorator.callback_operational_error is not None and isinstance(
e, OperationalError):
f = RollbackDecorator.callback_operational_error[0]
f(e)
raise e
def __init__(self,parent):
global dao
super(CreateNewOrderDialog,self).__init__(parent)
self.current_customer = None
title = _("Create new order")
self.setWindowTitle(title)
top_layout = QVBoxLayout()
self.title_widget = TitleWidget(title,self)
top_layout.addWidget(self.title_widget)
self.customer_plate_widget = CustomerPlateWidget(self)
self.buttons = QDialogButtonBox()
self.buttons.addButton( QDialogButtonBox.StandardButton.Cancel)
self.buttons.addButton( QDialogButtonBox.Ok)
hlayout = QHBoxLayout()
self.customer_select = AutoCompleteComboBox(None, self, None)
self.customer_select.section_width = None # [300]
view = []
keys = []
ref = []
customers = dao.customer_dao.all()
for c in customers:
view.append([c.fullname])
keys.append(c.fullname)
ref.append(c)
session().close() # FIXME bad data layer / presentation layer separation here
self.customer_select.make_model( view, keys, ref )
self.customer_plate_widget.set_customer(customers[0])
hlayout.addWidget(QLabel(_("Customer")), 0, Qt.AlignTop)
hlayout.addWidget(self.customer_select, 0, Qt.AlignTop)
hlayout.addWidget(self.customer_plate_widget, 1000, Qt.AlignTop)
hlayout.addStretch()
top_layout.addLayout(hlayout)
# hlayout = QHBoxLayout()
# self.order_select = AutoCompleteComboBox()
# self.order_select.setEnabled(False)
# self.order_select.section_width = [100,300]
# hlayout.addWidget(QLabel(_("Clone order")))
# self.enable_clone = QCheckBox()
# hlayout.addWidget(self.enable_clone)
# hlayout.addWidget(self.order_select)
# hlayout.addStretch()
# top_layout.addLayout(hlayout)
top_layout.addStretch()
top_layout.addWidget(self.buttons)
self.setLayout(top_layout)
self.buttons.accepted.connect(self.accept)
self.buttons.rejected.connect(self.reject)
# self.customer_select.activated.connect(self.customer_selected)
self.customer_select.list_view.itemSelected.connect(self.customer_selected)
def find_by_id(self, quality_event_id, commit=True):
res = session().query(QualityEvent).filter(
QualityEvent.quality_event_id == quality_event_id).one()
if commit:
session().commit()
return res
def create_all_tables():
mainlog.info("Creating all the tables and sequences")
Base.metadata.create_all(db_engine(), checkfirst=True)
session().commit()
mainlog.info("Creating all the functions in the database")
create_functions(session())
session().commit()
def find_by_id(self, supplier_id):
mainlog.debug("SupplierService.find_by_id {}".format(supplier_id))
c = all_non_relation_columns(Supplier)
supplier = session().query(*c).filter(
Supplier.supplier_id == supplier_id).one()
session().commit()
return supplier
def find_by_id(self, document_id: int):
# Polymorphic !!!
q = with_polymorphic(Document, [TemplateDocument])
doc = session().query(q).filter(
Document.document_id == document_id).one()
make_transient(doc)
session().commit()
return doc
def find_by_order_id(self, order_id):
docs = session().query(
Document.document_id, Document.filename, Document.file_size,
Document.description).join(documents_orders_table).filter(
documents_orders_table.c.order_id == order_id).all()
session().commit()
return docs
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()
def objects_list(self):
global dao
l = dao.employee_dao.all()
for employee in l:
x = employee.employee_id
x = employee.image
session().expunge(employee)
session().close()
return l
def find_recent_parts(self):
c = self._columns_for_parts()
r = session().query(*c).select_from(SupplyOrderPart).join(
SupplyOrder).join(Supplier).filter(
SupplyOrder.active == True).order_by(
desc(SupplyOrder.accounting_label),
SupplyOrderPart.position).limit(self.MAX_RESULTS).all()
session().commit()
return r