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 alter_structure():
try:
session().connection().execute(
"alter table horse.supply_orders add column active boolean not null constraint is_true default true"
)
session().commit()
except Exception as ex:
session().rollback()
mainlog.exception(ex)
def _add_edit_order_tab_if_necessary(self,order):
chrono_start()
h = (order.customer_id, order.order_id)
mainlog.debug("New edit order panel : hash {}".format(h))
chrono_click("_add_edit_order_tab_if_necessary : 0")
p = self.stack.has_panel(EditOrderPartsWidget,h)
chrono_click("_add_edit_order_tab_if_necessary : 1")
if not p:
chrono_click("_add_edit_order_tab_if_necessary : 2")
try:
p = self._make_edit_order_parts_widget()
except Exception as ex:
mainlog.exception(ex)
showErrorBox(_("Unable to show the order"),ex=ex)
return
chrono_click("_add_edit_order_tab_if_necessary : 3")
p.reset_order(order.order_id, overwrite=True)
chrono_click("_add_edit_order_tab_if_necessary : 4")
self.stack.add_panel(p) # Add or make it visible
chrono_click("_add_edit_order_tab_if_necessary : done")
return p
# edit_order_parts_widget = self.stack.add_panel_if_necessary(EditOrderPartsWidget,order.order_id)
# if w.current_order_id != order.order_id:
# edit_order_parts_widget.reset_order(order.order_id)
# i = 0
# edit_order_parts_widget = None
# while True:
# w = self.stack.widget(i)
# if w and isinstance(w, EditOrderPartsWidget) and w.current_order_id == order.order_id:
# edit_order_parts_widget = w
# break
# elif not w:
# break
# else:
# i = i + 1
# if not edit_order_parts_widget:
# edit_order_parts_widget = self._make_edit_order_parts_widget()
# edit_order_parts_widget.reset_order(order.order_id)
# else:
# self.stack.add_panel(edit_order_parts_widget)
return edit_order_parts_widget
def delete(self, employee_id):
employee = self.find_by_id(employee_id)
try:
session().delete(employee)
session().commit()
self._reload_cache()
except IntegrityError:
session().rollback()
raise ServerException(
ServerErrors.cannot_delete_employee_because_orders)
except Exception as e:
mainlog.exception(e)
session().rollback()
raise e
def check_database_connection():
# I need DB url because I didn't find a way to get that information
# from the session(), connection()...
# Rage hard, maxi vinyl with spoken words (super rare)
mainlog.info(
"check_database_connection : Trying to connect to the database")
try:
session().connection().execute(
"SELECT count(*) from {}.employees".format(DATABASE_SCHEMA))
session().commit()
return True
except Exception as ex:
mainlog.exception(ex)
return str(ex)
def check_postgres_connection(db_url):
""" Make sure we can connect to the server.
We use the template1 schema for that, because it exists
on any postgresql server.
"""
# I need DB url because I didn't find a way to get that information
# from the session(), connection()...
db_url, params_from_url = parse_db_url(db_url)
parsed_url = urlparse(db_url)
t1_url = parsed_url.scheme + "://" + parsed_url.netloc + "/template1"
# Rage hard, maxi vinyl with spoken words (super rare)
mainlog.info("Trying to connect to PostgreSQL server...")
engine = create_engine(t1_url)
c = None
try:
c = engine.connect()
c.execute("SELECT count(*) from pg_stats")
return True
except exc.OperationalError as ex:
mainlog.exception(ex)
mainlog.error("Can't query the database !!! Is it connected ?")
finally:
# This one is rather tricky. Somehow, I have
# the impression that the select above opens
# a connection and don't close it.
# Because of that, in some cases, PostgreSQL
# is cannot proceed with some operations.
# For example, if one stays connected to template1
# he cannot do a "drop table". So it is very important
# that the connection, transaction, whatever is
# actually closed when leaving this function.
mainlog.debug("Closing conenction")
if c: c.close()
# I think this helps to better close the connection
# although SQLA's documentation is a bit unclear.
engine.dispose()
del engine
return False
def disconnect_db():
global _config
if 'session' in _config:
try:
mainlog.debug("Closing connection")
_config['session'].connection().close()
mainlog.debug("Closing session")
_config['session'].close()
mainlog.debug("done closing session")
except Exception as ex:
mainlog.exception(ex)
finally:
_config.pop('session')
if 'engine' in _config:
mainlog.debug("Dropping engine")
_config.pop('engine')
def backup_procedure(configuration):
if not configuration.get('Backup', 'backup_directory'):
raise Exception(
"Missing Backup/backup_directory in configuration file")
backup_dir = configuration.get('Backup', 'backup_directory')
try:
if not os.path.exists(backup_dir):
os.mkdir(backup_dir)
mainlog.info("Created backup directory because it was missing")
mainlog.debug("Backup directory is {}".format(backup_dir))
# We default to backup behaviour because when
# this program is run as a scheduled task, we cannot
# give parameters to it
mainlog.info("Backing up the database")
filename, bytes = dump_and_zip_database(configuration)
mainlog.info("Backing up the documents")
total_files, total_bytes, scanned_files = documents_copy_recurring(
configuration.get('DocumentsDatabase', 'documents_root'),
configuration.get('Backup', 'backup_directory'), configuration)
mainlog.info(
"Documents copy done. {} files copied ({} bytes), {} files scanned."
.format(total_files, size_to_str(total_bytes), scanned_files))
mainlog.info("Syncing the back up remotely")
rsync_export_files(filename, mainlog)
send_mail(
"Backup SUCCESS",
"The backup of was done correctly DB:{}, files: {} / {} bytes.".
format(size_to_str(bytes), total_files,
total_bytes), configuration)
except Exception as ex:
mainlog.error("Failed to complete backup")
mainlog.exception(ex)
send_mail("Backup FAILURE", "The backup of was *not* done correctly.",
configuration)
def json_rpc2(self):
chrono_start()
try:
mainlog.debug("Dispatching JSON call {}".format(
cherrypy.request.json))
result = json_rpc_dispatcher(cherrypy.request.json)
# mainlog.debug("... success! Result is {}".format(str(result)))
# Normally, exception handling is worked ou in the JsonRpc handler and
# not in cherrypy
except ServerException as ex:
mainlog.error("Intercepted error ?")
mainlog.error(cherrypy.request.json)
mainlog.exception(u"[{}] {}".format(ex.code, ex.msg))
raise ex
except Exception as ex:
mainlog.error(cherrypy.request.json)
mainlog.exception(ex)
raise ex
chrono_click()
return result
def check_active_postgres_connections():
# I need DB url because I didn't find a way to get that information
# from the session(), connection()...
# Rage hard, maxi vinyl with spoken words (super rare)
mainlog.info(
"check_active_postgres_connections : Trying to connect to the database"
)
try:
r = session().connection().execute(
"SELECT count(*) from pg_stat_activity").scalar()
mainlog.debug("Trying to connect to the database - 2")
session().commit()
mainlog.debug("Trying to connect to the database - 3")
return r
except exc.OperationalError as ex:
mainlog.exception(ex)
mainlog.error("Can't query the database !!! Is it connected ?")
return False
def drop_entity( db_engine, session, entity):
if isinstance(entity, DeclarativeMeta):
drop_entity(db_engine, session, entity.__table__)
elif isinstance(entity, Table):
entity.drop(db_engine(), checkfirst=True)
session().commit()
elif isinstance(entity, DeclEnumMeta):
entity.db_type().drop( bind=db_engine(), checkfirst=True)
try:
session().connection().execute("DROP TYPE IF EXISTS {}".format(entity.db_name()))
session().commit()
except Exception as ex:
mainlog.exception(ex)
mainlog.error("Could not : DROP TYPE {}".format(entity.db_name()))
session().rollback()
else:
raise Exception("Unrecognized entity type : {}".format(type(entity)))
session().commit()
def extend_enumeration(enumeration: DeclEnum, symbol: EnumSymbol):
# The following statement really wants to run outside of a transaction.
# SO I have to use the raw_connection stuff to escape SQLA's autoamted
# transaction management.
# See enumeration type information
# select enumtypid, typname, enumlabel from pg_enum join pg_type on pg_type.oid = pg_enum.enumtypid order by enumtypid, enumlabel;
from koi.base_logging import mainlog
from koi.datalayer.database_session import db_engine
c = db_engine().raw_connection()
cursor = c.cursor()
cursor.execute("COMMIT") # Leave any pending transaction
try:
sql = "ALTER TYPE {} ADD VALUE '{}'".format(
enumeration.db_type().impl.name, symbol.value)
mainlog.debug(" /// " + sql)
cursor.execute(sql)
except Exception as ex:
mainlog.exception(ex)
cursor.close()
c.close()
def alter_structure():
try:
session().connection().execute(
"ALTER TABLE horse.operations DROP CONSTRAINT fk_employee")
session().connection().execute(
"ALTER TABLE horse.operations DROP COLUMN employee_id")
except Exception as ex:
session().rollback()
session().connection().execute(
"ALTER TABLE horse.operations ADD employee_id INTEGER")
session().connection().execute(
"ALTER TABLE horse.operations ADD CONSTRAINT fk_employee FOREIGN KEY(employee_id) REFERENCES horse.employees (employee_id)"
)
# Commit necessary, else the next drop table hangs ad infinitum
session().commit()
try:
session().connection().execute(
"ALTER TABLE horse.task_action_reports DROP COLUMN machine_id")
session().commit()
except Exception as ex:
session().rollback()
try:
session().connection().execute(
"ALTER TABLE horse.timetracks DROP COLUMN machine_id")
session().commit()
except Exception as ex:
session().rollback()
try:
session().connection().execute(
"ALTER TABLE horse.tasks_operations DROP COLUMN machine_id")
session().commit()
except Exception as ex:
session().rollback()
try:
session().connection().execute(
"ALTER TABLE horse.tasks_operations DROP CONSTRAINT tasks_operations_operation_id_key"
)
session().commit()
except Exception as ex:
session().rollback()
Machine.__table__.drop(db_engine(), checkfirst=True)
session().commit()
try:
session().connection().execute(
"DROP SEQUENCE horse.machine_id_generator")
except Exception as ex:
print(ex)
print("###############################################")
session().rollback()
Resource.__table__.drop(db_engine(), checkfirst=True)
session().commit()
try:
session().connection().execute(
"DROP SEQUENCE horse.resource_id_generator")
except Exception as ex:
print("---------------------------------------------------")
print(ex)
mainlog.exception(ex)
session().rollback()
session().commit()
# resource_id_generator.create()
Resource.__table__.create()
session().connection().execute("GRANT ALL ON horse.resources TO horse_clt")
session().connection().execute(
"GRANT ALL ON SEQUENCE horse.resource_id_generator TO horse_clt")
session().commit()
Machine.__table__.create()
session().connection().execute("GRANT ALL ON horse.machines TO horse_clt")
# session().connection().execute("GRANT ALL ON SEQUENCE horse.machine_id_generator TO horse_clt")
session().commit()
session().connection().execute(
"ALTER TABLE horse.task_action_reports ADD machine_id INTEGER")
session().connection().execute(
"ALTER TABLE horse.task_action_reports ADD CONSTRAINT fk_machine FOREIGN KEY(machine_id) REFERENCES horse.machines (resource_id)"
)
session().commit()
session().connection().execute(
"ALTER TABLE horse.timetracks ADD machine_id INTEGER")
session().connection().execute(
"ALTER TABLE horse.timetracks ADD CONSTRAINT fk_machine FOREIGN KEY(machine_id) REFERENCES horse.machines (resource_id)"
)
session().commit()
session().connection().execute(
"ALTER TABLE horse.tasks_operations ADD machine_id INTEGER")
session().connection().execute(
"ALTER TABLE horse.tasks_operations ADD CONSTRAINT fk_machine FOREIGN KEY(machine_id) REFERENCES horse.machines (resource_id)"
)
session().connection().execute(
"ALTER TABLE horse.tasks_operations ADD CONSTRAINT unique_task_on_machine_and_operation UNIQUE(operation_id,machine_id)"
)
session().commit()
exit(-1)
if args.demo_database:
mainlog.warn(
"Creating a demonstration database with {} orders ! This will destroy the current database."
.format(args.demo_database))
create_demo_database(args.demo_database)
exit(0)
if args.reset_database:
try:
create_blank_database(configuration.get("Database", "admin_url"),
configuration.get("Database", "url"))
exit(0)
except Exception as ex:
mainlog.exception(ex)
exit(-1)
elif args.restore_backup:
try:
base, filename = os.path.split(args.restore_backup)
if filename:
filename = os.path.join(base, filename)
full_restore(configuration, base, filename, True, mainlog)
exit(0)
except Exception as ex:
mainlog.exception(ex)
exit(-1)
def print_preorder_report(order_id):
documents_service = JsonCallWrapper(DocumentsService(),
JsonCallWrapper.HTTP_MODE)
doc_id = documents_service.reference_to_document_id(HORSE_REFERENCE)
if not doc_id:
raise Exception(
_("The template document with reference <code>{}</code> was not found. Check your templates."
).format(HORSE_REFERENCE))
tpl_path = download_document(doc_id)
order = dao.order_dao.find_by_id(order_id)
if not order.preorder_label:
raise Exception(
_("Can't print a preorder report for an order that is/was not a preorder"
))
from decimal import localcontext, Decimal
with localcontext() as ctx: # decimal context
ctx.prec = 10 + 2
qtize = Decimal(10)**-2
grand_total = Decimal(0)
from koi.reporting.utils import moneyfmt
parts_table = []
for part in order.parts:
qty = Decimal(part.qty).quantize(qtize)
sell_price = Decimal(part.sell_price).quantize(qtize)
total_price = (qty * sell_price).quantize(qtize)
grand_total += total_price
parts_table.append({
'ref':
part.human_identifier,
'description':
escape_xml(part.description or ""),
'quantity':
part.qty,
'unit_price':
moneyfmt(sell_price),
'total_price':
moneyfmt(total_price)
})
context = {
'customer_name': order.customer.fullname or "",
'customer_address1': order.customer.address1 or "",
'customer_address2': order.customer.address2 or "",
'customer_country': order.customer.country or "",
'order_number': order.accounting_label or "",
'preorder_number': order.preorder_label or "",
'order_reference_customer': order.customer_order_name or "",
'total_parts': moneyfmt(grand_total),
'items': parts_table,
}
tpl = None
try:
tpl = DocxTemplate(tpl_path)
except Exception as ex:
mainlog.exception(ex)
raise Exception(
_("Unable to open the report template (reference : '{}'). You should check in the document templates : does it exist ? Is it a .docx document ?"
).format(HORSE_REFERENCE))
tpl.render(context)
doc_path = make_home_file("preorder_letter_{}.docx".format(
order.preorder_label))
tpl.save(doc_path)
os.unlink(tpl_path)
open_a_file_on_os(doc_path)
def find_suggestions(s,cursor_position):
""" returns :
* replacement_area : a (action, start index,stop index) tuple representing where
the suggestion might fit in the original s string. action is always "rep"
for the moment.
* s : an array with all possible suggestions
* needs_quoting : tells if the suggestion needs to be quoted in the
query string (so that it will correctly be parsed later on). For
example, the suggestion "aaa bbb" needs quoting because there is
a space in it.
"""
mainlog.debug(u"Finding suggestions for :'{}'".format(s))
mainlog.debug(u"Finding suggestions for :'{}^".format(" "*cursor_position))
# But we keep the spaces
s = s.upper()
lexer.input(s)
tokens = []
while True:
try:
tok = lexer.token()
except Exception as e:
mainlog.exception(e)
break
if tok and tok.lexpos <= cursor_position:
tokens.append(tok)
else:
break
last_pos = 0
if tokens:
last_pos = tokens[-1].lexpos + token_length(tokens[-1]) - 1
if len(tokens) > 0:
mainlog.debug("Last pos is pos:{} + len:{} = {}".format(tokens[-1].lexpos,token_length(tokens[-1]),last_pos))
# while tokens and tokens[-1].lexpos > cursor_position:
# # tokens[-1] starts completely after the cursor position
# # So it doesn't interest us
# mainlog.debug("Popping token {}".format(tokens[-1].value))
# t = tokens.pop()
# last_pos = t.lexpos - 1
# At this point the last token either :
# - ends before the cursor_position
# - starts before/on and ends after the cursor_position
# We don't know for sure because we don't know
# its actual end (there may be spaces after which the
# lexer hides from us...).
# last_pos is either at the end of the string
# or 1 character before the first token that doesn't interest us
# Figure out the actual end of the last token
# while last_pos > 0 and last_pos < len(s) and s[last_pos] == ' ':
# last_pos = last_pos - 1
mainlog.debug("Last token's last character is at index {}".format(last_pos))
replacement_area = None
inside_token = None
if tokens and (last_pos >= cursor_position or (cursor_position >= len(s) and s[cursor_position-1] != ' ')):
# cursor is "inside" the last token.
mainlog.debug(u"Cursor is inside last token : {} lexpos:{} cursor:{}".format(tokens[-1].value, tokens[-1].lexpos,cursor_position))
t = inside_token = tokens.pop()
# Eats useless spaces
start = t.lexpos
# while start > 0 and s[start] == ' ':
# start = start - 1
replacement_area = ( 'rep', start, max(0,last_pos) )
last_pos = t.lexpos - 1
else:
# Cursor is after the last token
if tokens:
mainlog.debug("Cursor is after the last token : {} lexpos:{}".format(tokens[-1].value, tokens[-1].lexpos))
else:
mainlog.debug("Cursor is after NO token")
replacement_area = ( 'rep', last_pos+1, cursor_position )
mainlog.debug("Tokens are {}".format(tokens))
mainlog.debug("Will parse from 0 to {}".format(last_pos+1))
try:
parser.parse(s[0:last_pos+1],lexer=lexer)
except Exception as ex:
mainlog.exception(ex)
pass
s = []
indexed_s = []
if tokens:
# mainlog.debug(str(parser.symstack))
# mainlog.debug("Lookahead = {}".format(parser.lookahead_token))
# if parser.lookahead_token.type != '$end':
# return []
s,indexed_s = figure_suggestions(parser.symstack) # tokens[0:-1])
else:
s,indexed_s = figure_suggestions([]) # tokens)
needs_quoting = s == suppliers['suppliers']
if inside_token:
key = text_search_normalize(str(inside_token.value))
# suggestions are tuples : (text, normalized text)
filtered_s = [label[1] for label in
filter( lambda label: key in label[0],
zip(indexed_s,s) )]
if filtered_s and len(filtered_s) > 0:
s = filtered_s
# mainlog.debug("Found these suggestions : {}".format(s))
return replacement_area, s, needs_quoting
def alter_structure():
ftn = full_table_name(DocumentCategory)
doc_tn = full_table_name(Document)
try:
session().connection().execute(
"ALTER TABLE {} DROP CONSTRAINT fk_category".format(doc_tn))
session().commit()
except Exception as ex:
mainlog.exception(ex)
session().rollback()
try:
session().connection().execute(
"ALTER TABLE {} DROP COLUMN document_category_id".format(doc_tn))
session().commit()
except Exception as ex:
mainlog.exception(ex)
session().rollback()
documents_quality_events_table.drop(db_engine(), checkfirst=True)
session().commit()
drop_entity(DocumentCategory)
drop_entity(QualityEvent)
drop_entity(UserClass)
drop_entity(QualityEventType)
# Creations...
create_entity(DocumentCategory)
session().connection().execute(
"ALTER TABLE {} ADD document_category_id INTEGER".format(doc_tn))
session().connection().execute(
"ALTER TABLE {} ADD CONSTRAINT fk_category FOREIGN KEY(document_category_id) REFERENCES {} (document_category_id)"
.format(doc_tn, ftn))
# Quality
create_entity(QualityEvent)
# Autoincrement implies a sequence. SQLA chooses the name of that sequence.
session().connection().execute(
"GRANT USAGE, SELECT, UPDATE ON SEQUENCE horse.quality_events_quality_event_id_seq TO horse_clt"
)
create_entity(documents_quality_events_table)
from koi.db_mapping import OrderPartStateType
extend_enumeration(OrderPartStateType, OrderPartStateType.non_conform)
create_entity(UserClass)
# Autoincrement implies a sequence. SQLA chooses the name of that sequence.
session().connection().execute(
"GRANT USAGE, SELECT, UPDATE ON SEQUENCE horse.user_classes_user_class_id_seq TO horse_clt"
)
# Fix a database issue
session().connection().execute(
"GRANT SELECT,INSERT,UPDATE,DELETE ON {} TO horse_clt".format(doc_tn))
session().commit()
session().commit()