class InvoiceEntry(meta.BaseObject):
__tablename__ = "invoice_entry"
id = schema.Column(types.Integer(),
schema.Sequence("invoice_entry_id_seq", optional=True),
primary_key=True,
autoincrement=True)
position = schema.Column(types.Integer(), default=0)
invoice_id = schema.Column(types.Integer(),
schema.ForeignKey(Invoice.id,
onupdate="CASCADE",
ondelete="CASCADE"),
nullable=False)
invoice = orm.relationship(Invoice,
backref=orm.backref("entries",
order_by=position))
description = schema.Column(types.UnicodeText(), nullable=False)
vat = schema.Column(types.Integer(), nullable=False)
currency_id = schema.Column(types.Integer(3),
schema.ForeignKey(Currency.id,
onupdate="RESTRICT",
ondelete="RESTRICT"),
nullable=False)
currency = orm.relationship(Currency, lazy="joined")
unit_price = schema.Column(types.Numeric(precision=7, scale=2),
nullable=False)
units = schema.Column(types.Numeric(4, 2), nullable=False, default=1)
@property
def total(self):
return self.unit_price * self.units * self.currency.rate
def _fixed_lookup_fixture(self):
return [
(sqltypes.String(), sqltypes.VARCHAR()),
(sqltypes.String(1), sqltypes.VARCHAR(1)),
(sqltypes.String(3), sqltypes.VARCHAR(3)),
(sqltypes.Text(), sqltypes.TEXT()),
(sqltypes.Unicode(), sqltypes.VARCHAR()),
(sqltypes.Unicode(1), sqltypes.VARCHAR(1)),
(sqltypes.UnicodeText(), sqltypes.TEXT()),
(sqltypes.CHAR(3), sqltypes.CHAR(3)),
(sqltypes.NUMERIC, sqltypes.NUMERIC()),
(sqltypes.NUMERIC(10, 2), sqltypes.NUMERIC(10, 2)),
(sqltypes.Numeric, sqltypes.NUMERIC()),
(sqltypes.Numeric(10, 2), sqltypes.NUMERIC(10, 2)),
(sqltypes.DECIMAL, sqltypes.DECIMAL()),
(sqltypes.DECIMAL(10, 2), sqltypes.DECIMAL(10, 2)),
(sqltypes.INTEGER, sqltypes.INTEGER()),
(sqltypes.BIGINT, sqltypes.BIGINT()),
(sqltypes.Float, sqltypes.FLOAT()),
(sqltypes.TIMESTAMP, sqltypes.TIMESTAMP()),
(sqltypes.DATETIME, sqltypes.DATETIME()),
(sqltypes.DateTime, sqltypes.DATETIME()),
(sqltypes.DateTime(), sqltypes.DATETIME()),
(sqltypes.DATE, sqltypes.DATE()),
(sqltypes.Date, sqltypes.DATE()),
(sqltypes.TIME, sqltypes.TIME()),
(sqltypes.Time, sqltypes.TIME()),
(sqltypes.BOOLEAN, sqltypes.BOOLEAN()),
(sqltypes.Boolean, sqltypes.BOOLEAN()),
]
def upgrade_db(url, upgrade):
"""
Upgrade a database.
:param url: The url of the database to upgrade.
:param upgrade: The python module that contains the upgrade instructions.
"""
session, metadata = init_db(url)
class Metadata(BaseModel):
"""
Provides a class for the metadata table.
"""
pass
metadata_table = Table('metadata', metadata,
Column('key', types.Unicode(64), primary_key=True),
Column('value', types.UnicodeText(), default=None))
metadata_table.create(checkfirst=True)
mapper(Metadata, metadata_table)
version_meta = session.query(Metadata).get('version')
if version_meta is None:
# Tables have just been created - fill the version field with the most recent version
if session.query(Metadata).get('dbversion'):
version = 0
else:
version = upgrade.__version__
version_meta = Metadata.populate(key='version', value=version)
session.add(version_meta)
session.commit()
else:
version = int(version_meta.value)
if version > upgrade.__version__:
return version, upgrade.__version__
version += 1
try:
while hasattr(upgrade, 'upgrade_%d' % version):
log.debug('Running upgrade_%d', version)
try:
upgrade_func = getattr(upgrade, 'upgrade_%d' % version)
upgrade_func(session, metadata)
session.commit()
# Update the version number AFTER a commit so that we are sure the previous transaction happened
version_meta.value = str(version)
session.commit()
version += 1
except (SQLAlchemyError, DBAPIError):
log.exception(
'Could not run database upgrade script "upgrade_%s", upgrade process has been halted.',
version)
break
except (SQLAlchemyError, DBAPIError):
version_meta = Metadata.populate(key='version',
value=int(upgrade.__version__))
session.commit()
upgrade_version = upgrade.__version__
version_meta = int(version_meta.value)
session.close()
return version_meta, upgrade_version
def test_no_convert_unicode(self):
"""test no utf-8 encoding occurs"""
dialect = sqlite.dialect()
for t in (
String(convert_unicode=True),
sqltypes.CHAR(convert_unicode=True),
sqltypes.Unicode(),
sqltypes.UnicodeText(),
String(convert_unicode=True),
sqltypes.CHAR(convert_unicode=True),
sqltypes.Unicode(),
sqltypes.UnicodeText(),
):
bindproc = t.dialect_impl(dialect).bind_processor(dialect)
assert not bindproc or \
isinstance(bindproc(util.u('some string')), util.text_type)
class ActionPlan(BaseObject):
"""Action plans for a known risk."""
__tablename__ = "action_plan"
id = schema.Column(types.Integer(), primary_key=True, autoincrement=True)
risk_id = schema.Column(
types.Integer(),
schema.ForeignKey(Risk.id, onupdate="CASCADE", ondelete="CASCADE"),
nullable=False,
index=True,
)
action_plan = schema.Column(types.UnicodeText())
prevention_plan = schema.Column(types.UnicodeText())
# The column "action" is the synthesis of "action_plan" and "prevention_plan"
action = schema.Column(types.UnicodeText())
requirements = schema.Column(types.UnicodeText())
responsible = schema.Column(types.Unicode(256))
budget = schema.Column(types.Integer())
planning_start = schema.Column(types.Date())
planning_end = schema.Column(types.Date())
reference = schema.Column(types.Text())
plan_type = schema.Column(
Enum([
"measure_custom",
"measure_standard",
"in_place_standard",
"in_place_custom",
]),
nullable=False,
index=True,
default="measure_custom",
)
solution_id = schema.Column(types.Unicode(20))
used_in_training = schema.Column(
types.Boolean(),
default=True,
index=True,
)
risk = orm.relation(
Risk,
backref=orm.backref("action_plans",
order_by=id,
cascade="all, delete, delete-orphan"),
)
class Certificate(model.BaseObject):
""""""
__tablename__ = "certificate"
id = schema.Column(types.Integer(), primary_key=True, autoincrement=True)
json = schema.Column(types.UnicodeText())
secret = schema.Column(types.UnicodeText())
session_id = schema.Column(
types.Integer(),
schema.ForeignKey("session.id", onupdate="CASCADE",
ondelete="CASCADE"),
nullable=False,
index=True,
)
session = orm.relation("SurveySession", cascade="all")
@property
def json_data(self):
try:
return json.loads(self.json)
except (TypeError, ValueError):
return {}
@property
def title(self):
return self.json_data.get("title", "")
@property
def hr_date(self):
date = self.json_data.get("date", "").split("-")
if not date:
return ""
try:
date[1] = api.portal.translate(monthname_msgid(date[1]),
domain="plonelocales")
except Exception:
logger.error("Not a valid date %r", date)
return " ".join(reversed(date))
@property
def hr_date_plain(self):
date = self.json_data.get("date", "")
if not date:
return ""
return datetime.strptime(date, "%Y-%m-%d")
class DutchCompany(BaseObject):
"""Information about a Dutch company."""
__tablename__ = "dutch_company"
id = schema.Column(types.Integer(), primary_key=True, autoincrement=True)
session_id = schema.Column(
types.Integer(),
schema.ForeignKey("session.id", onupdate="CASCADE", ondelete="CASCADE"),
nullable=False,
index=True,
)
session = orm.relation(
"SurveySession",
cascade="all,delete-orphan",
single_parent=True,
backref=orm.backref("dutch_company", uselist=False, cascade="all"),
)
title = schema.Column(types.Unicode(128))
address_visit_address = schema.Column(types.UnicodeText())
address_visit_postal = schema.Column(types.Unicode(16))
address_visit_city = schema.Column(types.Unicode(64))
address_postal_address = schema.Column(types.UnicodeText())
address_postal_postal = schema.Column(types.Unicode(16))
address_postal_city = schema.Column(types.Unicode(64))
email = schema.Column(types.String(128))
phone = schema.Column(types.String(32))
activity = schema.Column(types.Unicode(64))
submitter_name = schema.Column(types.Unicode(64))
submitter_function = schema.Column(types.Unicode(64))
department = schema.Column(types.Unicode(64))
location = schema.Column(types.Unicode(64))
submit_date = schema.Column(types.Date(), default=functions.now())
employees = schema.Column(Enum([None, "40h", "max25", "over25"]))
absentee_percentage = schema.Column(types.Numeric(precision=5, scale=2))
accidents = schema.Column(types.Integer())
incapacitated_workers = schema.Column(types.Integer())
arbo_expert = schema.Column(types.Unicode(128))
works_council_approval = schema.Column(types.Date())
def load_dialect_impl(self, dialect):
""" Based on :dialect.name: determine type to be used.
`postgresql.ARRAY` is used in case `postgresql` database is used.
Otherwise `types.UnicodeText` is used.
"""
if dialect.name == 'postgresql':
self.is_postgresql = True
return dialect.type_descriptor(ARRAY(**self.kwargs))
else:
self.is_postgresql = False
self.kwargs.pop('item_type', None)
return dialect.type_descriptor(types.UnicodeText(**self.kwargs))
class JSONEncodedDict(types.TypeDecorator):
impl = types.UnicodeText(length=2**31)
def process_bind_param(self, value, dialect):
if value is not None:
return json.dumps(value)
return value
def process_result_value(self, value, dialect):
if value is not None:
return json.loads(value)
return value
class Post(Base):
__tablename__ = "posts"
__mapper_args__ = dict(order_by="date desc")
id = sqlalchemy.Column(types.Integer, primary_key=True)
category = sqlalchemy.Column(types.Unicode())
content = sqlalchemy.Column(types.UnicodeText())
date = sqlalchemy.Column(types.DateTime())
likes = sqlalchemy.Column(types.Integer)
dislikes = sqlalchemy.Column(types.Integer)
user_id = sqlalchemy.Column(types.Integer,
sqlalchemy.ForeignKey('users.id'))
user = relation(User, backref="posts")
class Comment(Base):
__tablename__ = "comments"
id = sqlalchemy.Column(types.Integer, primary_key=True)
content = sqlalchemy.Column(types.UnicodeText())
date = sqlalchemy.Column(types.DateTime())
likes = sqlalchemy.Column(types.Integer)
dislikes = sqlalchemy.Column(types.Integer)
post_id = sqlalchemy.Column(types.Integer,
sqlalchemy.ForeignKey('posts.id'))
post = relation(Post, backref="comments")
user_id = sqlalchemy.Column(types.Integer,
sqlalchemy.ForeignKey('users.id'))
user = relation(User, backref="comments")
class JSONType(types.TypeDecorator):
"""
Text column that contains a JSON data structure; a simplified version of
:class:`sqlalchemy_utils.types.json.JSONType`, which can be initialized
both from a JSON structure and a JSON string
"""
impl = types.UnicodeText(1 << 31)
cache_ok = True
def process_bind_param(self, value, dialect):
if value is not None and not isinstance(value, str):
value = json.dumps(value, cls=AfterglowSchemaEncoder)
return value
def process_result_value(self, value, dialect):
if value is not None:
value = json.loads(value)
return value
class InvoiceNote(meta.BaseObject):
__tablename__ = "invoice_note"
id = schema.Column(types.Integer(),
schema.Sequence("invoice_note_id_seq", optional=True),
primary_key=True,
autoincrement=True)
posted = schema.Column(types.DateTime(),
nullable=False,
default=functions.now())
invoice_id = schema.Column(types.Integer(),
schema.ForeignKey(Invoice.id,
onupdate="CASCADE",
ondelete="CASCADE"),
nullable=False)
invoice = orm.relationship(Invoice,
backref=orm.backref("notes", order_by=posted))
comment = schema.Column(types.UnicodeText(), nullable=False)
def _fixed_lookup_fixture(self):
return [
(sqltypes.String(), sqltypes.VARCHAR()),
(sqltypes.String(1), sqltypes.VARCHAR(1)),
(sqltypes.String(3), sqltypes.VARCHAR(3)),
(sqltypes.Text(), sqltypes.TEXT()),
(sqltypes.Unicode(), sqltypes.VARCHAR()),
(sqltypes.Unicode(1), sqltypes.VARCHAR(1)),
(sqltypes.UnicodeText(), sqltypes.TEXT()),
(sqltypes.CHAR(3), sqltypes.CHAR(3)),
(sqltypes.NUMERIC, sqltypes.NUMERIC()),
(sqltypes.NUMERIC(10, 2), sqltypes.NUMERIC(10, 2)),
(sqltypes.Numeric, sqltypes.NUMERIC()),
(sqltypes.Numeric(10, 2), sqltypes.NUMERIC(10, 2)),
(sqltypes.DECIMAL, sqltypes.DECIMAL()),
(sqltypes.DECIMAL(10, 2), sqltypes.DECIMAL(10, 2)),
(sqltypes.INTEGER, sqltypes.INTEGER()),
(sqltypes.BIGINT, sqltypes.BIGINT()),
(sqltypes.Float, sqltypes.FLOAT()),
(sqltypes.TIMESTAMP, sqltypes.TIMESTAMP()),
(sqltypes.DATETIME, sqltypes.DATETIME()),
(sqltypes.DateTime, sqltypes.DATETIME()),
(sqltypes.DateTime(), sqltypes.DATETIME()),
(sqltypes.DATE, sqltypes.DATE()),
(sqltypes.Date, sqltypes.DATE()),
(sqltypes.TIME, sqltypes.TIME()),
(sqltypes.Time, sqltypes.TIME()),
(sqltypes.BOOLEAN, sqltypes.BOOLEAN()),
(sqltypes.Boolean, sqltypes.BOOLEAN()),
(sqlite.DATE(storage_format="%(year)04d%(month)02d%(day)02d", ),
sqltypes.DATE()),
(sqlite.TIME(
storage_format="%(hour)02d%(minute)02d%(second)02d", ),
sqltypes.TIME()),
(sqlite.DATETIME(storage_format="%(year)04d%(month)02d%(day)02d"
"%(hour)02d%(minute)02d%(second)02d", ),
sqltypes.DATETIME()),
]
def test_should_unicodetext_convert_string():
assert get_field(types.UnicodeText()).type == graphene.String
class SurveySession(BaseObject):
"""Information about a user's session."""
__tablename__ = "session"
id = schema.Column(types.Integer(), primary_key=True, autoincrement=True)
brand = schema.Column(types.String(64))
account_id = schema.Column(
types.Integer(),
schema.ForeignKey(Account.id, onupdate="CASCADE", ondelete="CASCADE"),
nullable=False,
index=True,
)
last_modifier_id = schema.Column(
types.Integer(),
schema.ForeignKey(Account.id, onupdate="CASCADE", ondelete="CASCADE"),
nullable=True,
index=False,
)
last_publisher_id = schema.Column(
types.Integer(),
schema.ForeignKey(Account.id, onupdate="CASCADE", ondelete="CASCADE"),
nullable=True,
index=False,
)
group_id = schema.Column(
types.Unicode(32),
schema.ForeignKey("group.group_id"),
)
title = schema.Column(types.Unicode(512))
created = schema.Column(
types.DateTime,
nullable=False,
default=functions.now(),
)
modified = schema.Column(
types.DateTime,
nullable=False,
default=functions.now(),
)
refreshed = schema.Column(
types.DateTime,
nullable=False,
default=functions.now(),
)
published = schema.Column(
types.DateTime,
nullable=True,
default=None,
)
archived = schema.Column(
types.DateTime(timezone=True),
nullable=True,
default=None,
)
completion_percentage = schema.Column(
types.Integer,
nullable=True,
default=0,
)
zodb_path = schema.Column(types.String(512), nullable=False)
report_comment = schema.Column(types.UnicodeText())
account = orm.relation(
Account,
backref=orm.backref(
"sessions",
order_by=modified,
cascade="all, delete, delete-orphan",
),
foreign_keys=[account_id],
)
last_modifier = orm.relation(
Account,
foreign_keys=[last_modifier_id],
)
last_publisher = orm.relation(
Account,
foreign_keys=[last_publisher_id],
)
group = orm.relation(
Group,
backref=orm.backref("sessions",
order_by=modified,
cascade="all, delete, delete-orphan"),
)
migrated = schema.Column(
types.DateTime,
nullable=False,
default=functions.now(),
)
# Allow this class to be subclassed in other projects
__mapper_args__ = {
"polymorphic_identity": "euphorie",
"polymorphic_on": brand,
"with_polymorphic": "*",
}
@property
def is_archived(self):
archived = self.archived
if not archived:
return False
return archived <= localized_now()
@property
def review_state(self):
"""Check if it the published column.
If it has return 'published' otherwise return 'private'
"""
return "published" if self.published else "private"
def hasTree(self):
return bool(
Session.query(SurveyTreeItem).filter(
SurveyTreeItem.session == self).count())
def reset(self):
Session.query(SurveyTreeItem).filter(
SurveyTreeItem.session == self).delete()
self.created = self.modified = datetime.datetime.now()
def touch(self):
self.last_modifier = get_current_account()
self.modified = datetime.datetime.now()
def refresh_survey(self, survey=None):
"""Mark the session with the current date to indicate that is has been
refreshed with the latest version of the Survey (from Zope).
If survey is passed, update all titles in the tree, based on the CMS
version of the survey, i.e. update all titles of modules and risks.
Those are used in the navigation. If a title change is the only change
in the CMS, the survey session is not re-created. Therefore this
method ensures that the titles are updated where necessary.
"""
if survey:
query = Session.query(SurveyTreeItem).filter(
SurveyTreeItem.session_id == self.id)
tree = query.all()
for item in tree:
if item.zodb_path.find("custom-risks") >= 0:
continue
zodb_item = survey.restrictedTraverse(
item.zodb_path.split("/"), None)
if zodb_item and zodb_item.title != item.title:
item.title = zodb_item.title
self.refreshed = datetime.datetime.now()
def addChild(self, item):
sqlsession = Session()
query = (sqlsession.query(SurveyTreeItem.path).filter(
SurveyTreeItem.session_id == self.id).filter(
SurveyTreeItem.depth == 1).order_by(
SurveyTreeItem.path.desc()))
last = query.first()
if not last:
index = 1
else:
index = int(last[0][-3:]) + 1
item.session = self
item.depth = 1
item.path = "%03d" % index
item.parent_id = None
sqlsession.add(item)
self.touch()
return item
def children(self, filter=None):
query = (Session.query(SurveyTreeItem).filter(
SurveyTreeItem.session_id == self.id).filter(
SurveyTreeItem.depth == 1))
if filter is not None:
query = query.filter(filter)
return query.order_by(SurveyTreeItem.path)
def copySessionData(self, other):
"""Copy all user data from another session to this one."""
session = Session()
# Copy all tree data to the new session (skip_children and postponed)
old_tree = orm.aliased(SurveyTreeItem, name="old_tree")
in_old_tree = sql.and_(
old_tree.session_id == other.id,
SurveyTreeItem.zodb_path == old_tree.zodb_path,
SurveyTreeItem.profile_index == old_tree.profile_index,
)
skip_children = sql.select([old_tree.skip_children],
in_old_tree).limit(1)
postponed = sql.select([old_tree.postponed], in_old_tree).limit(1)
new_items = (session.query(SurveyTreeItem).filter(
SurveyTreeItem.session == self).filter(
sql.exists(sql.select([old_tree.id]).where(in_old_tree))))
new_items.update(
{
"skip_children": skip_children,
"postponed": postponed
},
synchronize_session=False,
)
# Mandatory modules must have skip_children=False. It's possible that
# the module was optional with skip_children=True and now after the
# update it's mandatory. So we must check and correct.
# In case a risk was marked as "always present", be sure its
# identification gets set to 'no'
preset_to_no = []
survey = getSite()["client"].restrictedTraverse(self.zodb_path)
for item in new_items.all():
if item.type == "risk":
if item.identification == "no":
preset_to_no.append(item.risk_id)
elif item.type == "module":
module = survey.restrictedTraverse(item.zodb_path.split("/"))
if not module.optional:
item.skip_children = False
# Copy all risk data to the new session
# This triggers a "Only update via a single table query is currently
# supported" error with SQLAlchemy 0.6.6
# old_risk = orm.aliased(Risk.__table__, name='old_risk')
# is_old_risk = sql.and_(in_old_tree, old_tree.id == old_risk.id)
# identification = sql.select([old_risk.identification], is_old_risk)
# new_risks = session.query(Risk)\
# .filter(Risk.session == self)\
# .filter(sql.exists(
# sql.select([SurveyTreeItem.id]).where(sql.and_(
# SurveyTreeItem.id == Risk.id,
# sql.exists([old_tree.id]).where(sql.and_(
# in_old_tree, old_tree.type == 'risk'))))))
# new_risks.update({'identification': identification},
# synchronize_session=False)
skip_preset_to_no_clause = ""
if len(preset_to_no):
skip_preset_to_no_clause = "old_risk.risk_id not in %s AND" % (str(
[str(x)
for x in preset_to_no]).replace("[", "(").replace("]", ")"))
statement = """\
UPDATE RISK
SET identification = old_risk.identification,
frequency = old_risk.frequency,
effect = old_risk.effect,
probability = old_risk.probability,
priority = old_risk.priority,
existing_measures = old_risk.existing_measures,
comment = old_risk.comment
FROM risk AS old_risk JOIN tree AS old_tree ON old_tree.id=old_risk.id, tree
WHERE tree.id=risk.id AND
%(skip_preset_to_no_clause)s
tree.session_id=%(new_sessionid)s AND
old_tree.session_id=%(old_sessionid)s AND
old_tree.zodb_path=tree.zodb_path AND
old_tree.profile_index=tree.profile_index;
""" % dict( # noqa: E501
old_sessionid=other.id,
new_sessionid=self.id,
skip_preset_to_no_clause=skip_preset_to_no_clause,
)
session.execute(statement)
statement = """\
INSERT INTO action_plan (risk_id, action_plan, prevention_plan, action,
requirements, responsible, budget, plan_type,
planning_start, planning_end,
solution_id, used_in_training)
SELECT new_tree.id,
action_plan.action_plan,
action_plan.prevention_plan,
action_plan.action,
action_plan.requirements,
action_plan.responsible,
action_plan.budget,
action_plan.plan_type,
action_plan.planning_start,
action_plan.planning_end,
action_plan.solution_id,
action_plan.used_in_training
FROM action_plan JOIN risk ON action_plan.risk_id=risk.id
JOIN tree ON tree.id=risk.id,
tree AS new_tree
WHERE tree.session_id=%(old_sessionid)d AND
new_tree.session_id=%(new_sessionid)d AND
tree.zodb_path=new_tree.zodb_path AND
tree.profile_index=new_tree.profile_index;
""" % {
"old_sessionid": other.id,
"new_sessionid": self.id,
}
session.execute(statement)
# Copy over previous session metadata. Specifically, we don't want to
# create a new modification timestamp, just because the underlying
# survey was updated.
statement = """\
UPDATE session
SET
modified = old_session.modified,
created = old_session.created,
last_modifier_id = old_session.last_modifier_id
FROM session as old_session
WHERE
old_session.id=%(old_sessionid)d AND
session.id=%(new_sessionid)d
""" % {
"old_sessionid": other.id,
"new_sessionid": self.id,
}
session.execute(statement)
session.query(Company).filter(Company.session == other).update(
{"session_id": self.id}, synchronize_session=False)
@classmethod
def get_account_filter(cls, account=None):
"""Filter only the sessions for the given account
:param acount: True means current account.
A falsish value means do not filter.
Otherwise try to interpret the user input:
a string or an int means the account_id should be that value,
an object account will be used to extract the account id,
from an iterable we will try to extract the account ids
"""
if account is True:
account = get_current_account()
if isinstance(account, Account):
account = account.id
if not account:
return False
if isinstance(account, (int, six.string_types)):
return cls.account_id == account
try:
account_ids = {getattr(item, "id", item) for item in account}
except TypeError:
log.error("Cannot understand the account parameter: %r", account)
raise
account_ids = {
item
for item in account_ids
if item and isinstance(item, (int, six.string_types))
}
if not account_ids:
return False
if len(account_ids) == 1:
for account_id in account_ids:
return cls.get_account_filter(account_id)
return cls.account_id.in_(account_ids)
@classmethod
def get_group_filter(cls, group=None):
"""Filter only the sessions for the given group
:param group: True means the current account's group.
A falsish value means do not filter.
Otherwise try to interpret the user input:
a string or an int means the group_id should be that value,
an object group will be used to extract the group id,
and from an iterable we will try to extract the group ids
"""
if group is True:
group = getattr(get_current_account(), "group_id", None)
if isinstance(group, Group):
group = group.group_id
if not group:
return False
if isinstance(group, (int, six.string_types)):
return cls.group_id == group
try:
group_ids = {getattr(item, "group_id", item) for item in group}
except TypeError:
log.error("Cannot understand the group parameter: %r", group)
raise
group_ids = {
item
for item in group_ids
if item and isinstance(item, (int, six.string_types))
}
if not group_ids:
return False
if len(group_ids) == 1:
for group_id in group_ids:
return cls.get_group_filter(group_id)
return cls.group_id.in_(group_ids)
@classmethod
def get_archived_filter(cls):
"""Filter sessions that are archived"""
return sql.or_(
cls.archived >= localized_now(),
cls.archived == None # noqa: E711
)
@classmethod
def _get_context_tools(cls, context):
"""Return the set of tools we can find under this context"""
if not context:
return set()
# Check the path relative to the client folder
if context.portal_type == "Plone Site":
context = context.client
if context.portal_type == "euphorie.survey":
return {context}
portal_type_filter = {
"portal_type": [
"euphorie.clientcountry",
"euphorie.clientsector",
"euphorie.survey",
]
}
surveys = set()
def _add_survey(container):
for obj in container.listFolderContents(portal_type_filter):
if obj.portal_type == "euphorie.survey":
surveys.add(obj)
else:
_add_survey(obj)
_add_survey(context)
return surveys
@classmethod
def get_context_filter(cls, context):
"""Filter sessions under this context using the zodb_path column"""
surveys = cls._get_context_tools(context)
if not surveys:
return False
return cls.zodb_path.in_({
safe_unicode("/".join(survey.getPhysicalPath()[-3:]))
for survey in surveys
})
@property
def tool(self):
client = api.portal.get().client
return client.restrictedTraverse(str(self.zodb_path), None)
@property
def traversed_session(self):
return self.tool.restrictedTraverse("++session++%s" % self.id)
@property
def country(self):
return str(self.zodb_path).split("/")[0]
# SQLAlchemy stuff
from sqlalchemy import Column, MetaData, Table, types, ForeignKey
from sqlalchemy import orm
# from sqlalchemy import __version__ as _sqla_version
# import pkg_resources
# _sqla_version = pkg_resources.parse_version(_sqla_version)
# Instantiate meta data manager.
dbmetadata = MetaData()
package_table = Table('package', dbmetadata,
Column('id', types.UnicodeText, primary_key=True),
Column('name', types.Unicode(255)),
Column('installed_path', types.UnicodeText()),
# Column('description', types.UnicodeText()),
# Column('notes', types.UnicodeText()),
# Column('url', types.UnicodeText()),
# Column('download_url', types.UnicodeText()),
# Column('license', types.UnicodeText()),
# Column('tags', types.UnicodeText()),
)
from sqlalchemy.orm import MapperExtension, EXT_STOP
class ReconstituteExtension(MapperExtension):
# v0.4
def populate_instance(self, mapper, selectcontext, row, instance, **flags):
# in v0.5 we can change to use on_reconstitute see
# http://www.sqlalchemy.org/docs/05/mappers.html#constructors-and-object-initialization
class Risk(SurveyTreeItem):
"""Answer to risk."""
__tablename__ = "risk"
__mapper_args__ = dict(polymorphic_identity="risk")
sql_risk_id = schema.Column(
"id",
types.Integer(),
schema.ForeignKey(SurveyTreeItem.id,
onupdate="CASCADE",
ondelete="CASCADE"),
primary_key=True,
)
risk_id = schema.Column(types.String(16), nullable=True)
risk_type = schema.Column(Enum(["risk", "policy", "top5"]),
default="risk",
nullable=False,
index=True)
#: Skip-evaluation flag. This is only used to indicate if the sector
#: set the evaluation method to `fixed`, not for policy behaviour
#: such as not evaluation top-5 risks. That policy behaviour is
#: handled via the question_filter on client views so it can be modified
#: in custom deployments.
skip_evaluation = schema.Column(types.Boolean(),
default=False,
nullable=False)
is_custom_risk = schema.Column(types.Boolean(),
default=False,
nullable=False)
identification = schema.Column(Enum([None, "yes", "no", "n/a"]))
frequency = schema.Column(types.Integer())
effect = schema.Column(types.Integer())
probability = schema.Column(types.Integer())
priority = schema.Column(Enum([None, "low", "medium", "high"]))
comment = schema.Column(types.UnicodeText())
existing_measures = schema.Column(types.UnicodeText())
training_notes = schema.Column(types.UnicodeText())
custom_description = schema.Column(types.UnicodeText())
image_data = schema.Column(types.LargeBinary())
image_data_scaled = schema.Column(types.LargeBinary())
image_filename = schema.Column(types.UnicodeText())
@memoize
def measures_of_type(self, plan_type):
query = (Session.query(ActionPlan).filter(
sql.and_(ActionPlan.risk_id == self.id),
ActionPlan.plan_type == plan_type,
).order_by(ActionPlan.id))
return query.all()
@property
def standard_measures(self):
return self.measures_of_type("measure_standard")
@property
def custom_measures(self):
return self.measures_of_type("measure_custom")
@property
def in_place_standard_measures(self):
return self.measures_of_type("in_place_standard")
@property
def in_place_custom_measures(self):
return self.measures_of_type("in_place_custom")
def test_should_unicodetext_convert_string():
assert_column_conversion(types.UnicodeText(), graphene.String)
def upgrade_db(url, upgrade):
"""
Upgrade a database.
:param url: The url of the database to upgrade.
:param upgrade: The python module that contains the upgrade instructions.
"""
if not database_exists(url):
log.warning("Database {db} doesn't exist - skipping upgrade checks".format(db=url))
return 0, 0
log.debug('Checking upgrades for DB {db}'.format(db=url))
session, metadata = init_db(url)
class Metadata(BaseModel):
"""
Provides a class for the metadata table.
"""
pass
metadata_table = Table(
'metadata', metadata,
Column('key', types.Unicode(64), primary_key=True),
Column('value', types.UnicodeText(), default=None)
)
metadata_table.create(checkfirst=True)
mapper(Metadata, metadata_table)
version_meta = session.query(Metadata).get('version')
if version_meta:
version = int(version_meta.value)
else:
# Due to issues with other checks, if the version is not set in the DB then default to 0
# and let the upgrade function handle the checks
version = 0
version_meta = Metadata.populate(key='version', value=version)
session.add(version_meta)
session.commit()
if version > upgrade.__version__:
session.remove()
return version, upgrade.__version__
version += 1
try:
while hasattr(upgrade, 'upgrade_{version:d}'.format(version=version)):
log.debug('Running upgrade_{version:d}'.format(version=version))
try:
upgrade_func = getattr(upgrade, 'upgrade_{version:d}'.format(version=version))
upgrade_func(session, metadata)
session.commit()
# Update the version number AFTER a commit so that we are sure the previous transaction happened
version_meta.value = str(version)
session.commit()
version += 1
except (SQLAlchemyError, DBAPIError):
log.exception('Could not run database upgrade script '
'"upgrade_{version:d}", upgrade process has been halted.'.format(version=version))
break
except (SQLAlchemyError, DBAPIError):
version_meta = Metadata.populate(key='version', value=int(upgrade.__version__))
session.commit()
upgrade_version = upgrade.__version__
version = int(version_meta.value)
session.remove()
return version, upgrade_version
meta.metadata,
schema.Column('id',
types.Integer,
schema.Sequence('form_seq_id', optional=True),
primary_key=True),
# Textual values
# transcription -- orthographic, obligatory
schema.Column('transcription', types.Unicode(255), nullable=False),
# phonetic transcription -- broad, optional
schema.Column('phoneticTranscription', types.Unicode(255)),
# narrow phonetic transcription -- optional
schema.Column('narrowPhoneticTranscription', types.Unicode(255)),
schema.Column('morphemeBreak', types.Unicode(255)),
schema.Column('morphemeGloss', types.Unicode(255)),
schema.Column('comments', types.UnicodeText()),
schema.Column('speakerComments', types.UnicodeText()),
schema.Column('context',
types.UnicodeText()), # describing context of utterance
# Forced choice textual values
schema.Column('grammaticality', types.Unicode(255)),
# Temporal values: only dateElicited is consciously enterable by the user
schema.Column('dateElicited', types.Date()),
schema.Column('datetimeEntered', types.DateTime()),
schema.Column('datetimeModified', types.DateTime(), default=now),
# syntacticCategoryString: OLD-generated value
schema.Column('syntacticCategoryString', types.Unicode(255)),
class Invoice(meta.BaseObject):
"""An invoice."""
__tablename__ = "invoice"
id = schema.Column(types.Integer(),
schema.Sequence("invoice_id_seq", optional=True),
primary_key=True,
autoincrement=True)
_number = schema.Column("number", types.Integer())
customer_id = schema.Column(types.Integer(),
schema.ForeignKey(Customer.id,
onupdate="CASCADE",
ondelete="CASCADE"),
nullable=False)
customer = orm.relationship(Customer, backref="invoices")
sent = schema.Column(types.Date())
payment_term = schema.Column(types.Integer(), nullable=False, default=30)
paid = schema.Column(types.Date())
note = schema.Column(types.UnicodeText())
@orm.reconstructor
def _add_acls(self):
account_id = self.customer.account_id
self.__acl__ = [(security.Allow, account_id, ("comment", "view"))]
if not self.sent:
self.__acl__.append(
(security.Allow, account_id, ("delete", "edit")))
if len(self.entries):
self.__acl__.append((security.Allow, account_id, "send"))
if self.sent and not self.paid:
self.__acl__.append((security.Allow, account_id, "mark-paid"))
@property
def due(self):
if self.sent:
return self.sent + datetime.timedelta(days=self.payment_term)
return None
def total(self, type="gross"):
assert type in ["gross", "net", "vat"]
gross = sum([entry.total for entry in self.entries])
if type == "gross":
return gross
vat = sum([v[1] for v in self.VAT()])
if type == "vat":
return vat
return gross + vat
def VAT(self):
totals = {}
for entry in self.entries:
if not entry.vat:
continue
current = entry.total
totals[entry.vat] = totals.get(entry.vat, 0) + current
for (vat, total) in totals.items():
totals[vat] = (totals[vat] * vat) / 100
return sorted(totals.items())
@synonym_for("_number")
@property
def number(self):
if not self._number:
return None
return "%s.%04d" % (self.customer.invoice_code, self._number)
def state(self):
if not self.sent:
return "unsend"
elif self.paid:
return "paid"
today = datetime.date.today()
due = self.sent + datetime.timedelta(days=self.payment_term)
if due < today:
return "overdue"
else:
return "pending"
def overdue(self):
if self.paid or not self.sent:
return None
today = datetime.date.today()
due = self.sent + datetime.timedelta(days=self.payment_term)
if due >= today:
return None
return (today - due).days
def send(self):
assert self.sent is None
self.sent = datetime.datetime.now()
self._number = self.customer.account.newInvoiceNumber()
