def RGStatistik(az, bestaet, unfoid):
datum = str(strftime("%d.%m.%Y", localtime()))
upd = z1vrs1aa.update().where(and_(z1vrs1aa.c.az == az)).values(
bestaet=bestaet, am=datum, unfoid=unfoid)
session = Session()
session.execute(upd)
mark_changed(session)
def render(self):
dbtable_surveys = 'statistics_surveys'
info_surveys = self.getSurveysInfo()
# write to db
session = Session()
session.execute('''DELETE FROM %s;''' % dbtable_surveys)
def clean(value):
if isinstance(value, basestring):
return safe_unicode(value).strip().encode('utf-8')
return value
def pg_format(value):
if value is None:
return 'NULL'
if isinstance(value, datetime):
return "TIMESTAMP '%s'" % value.isoformat()
return "'%s'" % value
for line in info_surveys:
insert = '''INSERT INTO %s VALUES %s;''' % \
(dbtable_surveys, '(%s)' % ', '.join(map(pg_format,
map(clean, line))))
session.execute(insert)
datamanager.mark_changed(session)
transaction.get().commit()
from pprint import pformat
return "Written:\n" + pformat(info_surveys)
def render(self):
log.info('Called: write_statistics')
dbtable_surveys = 'statistics_surveys'
info_surveys = self.getSurveysInfo()
# write to db
session = Session()
session.execute('''DELETE FROM %s;''' % dbtable_surveys)
def clean(value):
if isinstance(value, basestring):
return safe_unicode(value).strip().encode('utf-8')
return value
def pg_format(value):
if value is None:
return 'NULL'
if isinstance(value, datetime):
return "TIMESTAMP '%s'" % value.isoformat()
return "'%s'" % value
for line in info_surveys:
insert = '''INSERT INTO %s VALUES %s;''' % \
(dbtable_surveys, '(%s)' % ', '.join(map(pg_format,
map(clean, line))))
session.execute(insert)
datamanager.mark_changed(session)
transaction.get().commit()
log.info('Exported statistics on {0} surveys to the DB.'.format(
len(info_surveys)))
from pprint import pformat
return "Written:\n" + pformat(info_surveys)
def enable_longer_zodb_paths(context):
session = Session()
session.execute(
"ALTER TABLE %s ALTER COLUMN zodb_path TYPE varchar(512);" %
model.SurveyTreeItem.__table__.name
)
datamanager.mark_changed(session)
def render(self):
log.info("Called: write_statistics")
dbtable_surveys = "statistics_surveys"
info_surveys = self.getSurveysInfo()
# write to db
session = Session()
session.execute("""DELETE FROM %s;""" % dbtable_surveys)
def clean(value):
if isinstance(value, basestring):
return safe_unicode(value).strip().encode("utf-8")
return value
def pg_format(value):
if value is None:
return "NULL"
if isinstance(value, datetime):
return "TIMESTAMP '%s'" % value.isoformat()
return "'%s'" % value
for line in info_surveys:
insert = """INSERT INTO %s VALUES %s;""" % (
dbtable_surveys,
"(%s)" % ", ".join(map(pg_format, map(clean, line))),
)
session.execute(insert)
datamanager.mark_changed(session)
transaction.get().commit()
log.info("Exported statistics on {0} surveys to the DB.".format(
len(info_surveys)))
from pprint import pformat
return "Written:\n" + pformat(info_surveys)
def getGrundDaten(self):
if os.environ.get('ADHOC_TEST') == "True":
from ukh.adhoc.lib.testdata import gd
return gd
d1 = select([c1unf1aa, c1prs1aa],
and_(c1prs1aa.c.prsoid == c1unf1aa.c.unfprs,
c1unf1aa.c.unfaz == self.az))
session = Session()
daten1 = session.execute(d1).fetchall()
d2 = select([avika1aa],
and_(avika1aa.c.ikkl == str(daten1[0]['prsikn'])[0:3],
avika1aa.c.ikbs == str(daten1[0]['prsikn'])[3:10]))
prsdaten = session.execute(d2).fetchall()
datensatz = {}
datensatz.update(dict(prsdaten[0]))
datensatz.update(dict(daten1[0]))
if datensatz['ikanr'] == 1:
datensatz['ikanr'] = u'Frau'
else:
datensatz['ikanr'] = u'Herr'
nd = {}
for k, v in datensatz.items():
if isinstance(v, basestring):
nd[k] = v
else:
nd[k] = str(v)
return datensatz
def add_actionplan_reference(context):
session = Session()
inspector = Inspector.from_engine(session.bind)
columns = [c['name']
for c in inspector.get_columns(ActionPlan.__table__.name)]
if 'reference' not in columns:
log.info('Adding reference column for action plans')
session.execute('ALTER TABLE action_plan ADD COLUMN reference TEXT')
def test_not_nullable(self):
import mock
from z3c.saconfig import Session
session = Session()
session.execute('CREATE TABLE account (password TEXT)')
session.execute = mock.Mock()
self.allow_empty_password()
self.assertTrue(not session.execute.called)
def test_not_nullable(self):
import mock
from z3c.saconfig import Session
session = Session()
session.execute('CREATE TABLE account (password TEXT)')
session.execute = mock.Mock()
self.allow_empty_password()
self.assertTrue(not session.execute.called)
def test_column_not_present(self):
import mock
from z3c.saconfig import Session
session = Session()
session.execute('CREATE TABLE risk (foo INT)')
session.execute = mock.Mock()
self.add_skip_evaluation_to_model()
self.assertTrue(session.execute.called)
def test_not_nullable(self):
import mock
from z3c.saconfig import Session
session = Session()
session.execute('CREATE TABLE action_plan (reference TEXT)')
session.execute = mock.Mock()
self.add_actionplan_reference()
self.assertTrue(not session.execute.called)
def test_not_nullable(self):
import mock
from z3c.saconfig import Session
session = Session()
session.execute('CREATE TABLE action_plan (reference TEXT)')
session.execute = mock.Mock()
self.add_actionplan_reference()
self.assertTrue(not session.execute.called)
def drop_constraint_no_duplicates_in_tree(context):
session = Session()
if TableExists(session, "tree"):
session.execute("ALTER TABLE tree DROP CONSTRAINT no_duplicates")
model.metadata.create_all(session.bind, checkfirst=True)
datamanager.mark_changed(session)
transaction.get().commit()
log.info("Removed the constraint `no_duplicates` from table tree.")
def increase_sessions_path_column(context):
session = Session()
if TableExists(session, "session"):
session.execute("ALTER TABLE session ALTER COLUMN zodb_path TYPE varchar(512)")
model.metadata.create_all(session.bind, checkfirst=True)
datamanager.mark_changed(session)
transaction.get().commit()
log.info("Increased the size of column zodb_path in table session.")
def enable_custom_risks_on_all_modules(context):
""" """
if not api.portal.get_registry_record("euphorie.allow_user_defined_risks"):
log.warning(
"Custom risks are not enabled. Set 'allow_user_defined_risks' to "
"true in euphorie.ini for enabling them."
)
return
portal = api.portal.get()
client = portal.client
count = 0
for country in client.objectValues():
if IClientCountry.providedBy(country):
for sector in country.objectValues():
if IClientSector.providedBy(sector):
for survey in sector.objectValues():
try:
is_new = EnableCustomRisks(survey)
count += 1
custom = getattr(survey, "custom-risks", None)
if custom:
custom.title = _(
"title_other_risks",
default="Added risks (by you)",
)
custom.description = _(
"description_other_risks",
default="In case you have identified risks not included in " # noqa: E501
"the tool, you are able to add them now:",
)
custom.question = _(
"question_other_risks",
default="<p>Would you now like to add your own defined risks " # noqa: E501
"to this tool?</p><p><strong>Important:</strong> In " # noqa: E501
"order to avoid duplicating risks, we strongly recommend you " # noqa: E501
"to go first through all the previous modules, if you have not " # noqa: E501
"done it yet.</p><p>If you don't need to add risks, please select 'No.'</p>", # noqa: E501
)
if is_new:
survey.published = (
survey.id,
survey.title,
datetime.datetime.now(),
)
except Exception as e:
log.error(
"Could not enable custom risks for module. %s" % e
)
log.info("All %d published surveys can now have custom risks." % count)
session = Session()
if TableExists(session, "tree"):
session.execute(
"UPDATE tree SET title = 'title_other_risks' WHERE zodb_path ='custom-risks'" # noqa: E501
)
model.metadata.create_all(session.bind, checkfirst=True)
datamanager.mark_changed(session)
transaction.get().commit()
log.info("Set correct title on all exisiting sessions for custom risks module.")
def test_nullable(self):
import mock
from z3c.saconfig import Session
session = Session()
session.execute('CREATE TABLE account (password TEXT NOT NULL)')
session.execute = mock.Mock()
self.allow_empty_password()
session.execute.assert_called_once_with(
'ALTER TABLE account ALTER COLUMN password DROP NOT NULL')
def add_actionplan_reference(context):
session = Session()
inspector = Inspector.from_engine(session.bind)
columns = [
c['name'] for c in inspector.get_columns(ActionPlan.__table__.name)
]
if 'reference' not in columns:
log.info('Adding reference column for action plans')
session.execute('ALTER TABLE action_plan ADD COLUMN reference TEXT')
def add_od_version_column(context):
session = Session()
if column_exists(session, 'od_link', 'version'):
return
transaction.get().commit()
session.execute('ALTER TABLE od_link ADD COLUMN version INT DEFAULT 0 NOT NULL')
datamanager.mark_changed(session)
transaction.get().commit()
log.info("Added new column 'version' to table 'od_link'")
def test_nullable(self):
import mock
from z3c.saconfig import Session
session = Session()
session.execute('CREATE TABLE action_plan (foo INT)')
session.execute = mock.Mock()
self.add_actionplan_reference()
session.execute.assert_called_once_with(
'ALTER TABLE action_plan ADD COLUMN reference TEXT')
def increase_sessions_path_column(context):
session = Session()
if TableExists(session, "session"):
session.execute(
"ALTER TABLE session ALTER COLUMN zodb_path TYPE varchar(512)")
model.metadata.create_all(session.bind, checkfirst=True)
datamanager.mark_changed(session)
transaction.get().commit()
log.info("Increased the size of column zodb_path in table session.")
def alter_time_column(context):
session = Session()
if TableExists(session, "statistics_login"):
session.execute(
"ALTER TABLE statistics_login ALTER COLUMN time SET DEFAULT CURRENT_TIMESTAMP")
model.metadata.create_all(session.bind, checkfirst=True)
datamanager.mark_changed(session)
transaction.get().commit()
log.info("Changed default for column 'time' to current timestamp")
def drop_constraint_no_duplicates_in_tree(context):
session = Session()
if TableExists(session, "tree"):
session.execute(
"ALTER TABLE tree DROP CONSTRAINT no_duplicates")
model.metadata.create_all(session.bind, checkfirst=True)
datamanager.mark_changed(session)
transaction.get().commit()
log.info("Removed the constraint `no_duplicates` from table tree.")
def test_nullable(self):
import mock
from z3c.saconfig import Session
session = Session()
session.execute('CREATE TABLE action_plan (foo INT)')
session.execute = mock.Mock()
self.add_actionplan_reference()
session.execute.assert_called_once_with(
'ALTER TABLE action_plan ADD COLUMN reference TEXT')
def test_nullable(self):
import mock
from z3c.saconfig import Session
session = Session()
session.execute('CREATE TABLE account (password TEXT NOT NULL)')
session.execute = mock.Mock()
self.allow_empty_password()
session.execute.assert_called_once_with(
'ALTER TABLE account ALTER COLUMN password DROP NOT NULL')
def alter_time_column(context):
session = Session()
if TableExists(session, "statistics_login"):
session.execute(
"ALTER TABLE statistics_login ALTER COLUMN time SET DEFAULT CURRENT_TIMESTAMP"
)
model.metadata.create_all(session.bind, checkfirst=True)
datamanager.mark_changed(session)
transaction.get().commit()
log.info("Changed default for column 'time' to current timestamp")
def add_wp_column_to_company(context):
session = Session()
if TableExists(session, "company"):
session.execute(
"ALTER TABLE company ADD workers_participated bool DEFAULT NULL")
model.metadata.create_all(session.bind, checkfirst=True)
datamanager.mark_changed(session)
transaction.get().commit()
log.info("Added new column 'workers_participated' to table 'company'")
def allow_empty_password(context):
session = Session()
inspector = Inspector.from_engine(session.bind)
columns = inspector.get_columns(Account.__table__.name)
password = [c for c in columns if c['name'] == 'password'][0]
if not password['nullable']:
log.info('Dropping NOT NULL constraint for account.password')
session.execute(
'ALTER TABLE account ALTER COLUMN password DROP NOT NULL')
datamanager.mark_changed(session)
def allow_empty_password(context):
session = Session()
inspector = Inspector.from_engine(session.bind)
columns = inspector.get_columns(Account.__table__.name)
password = [c for c in columns if c['name'] == 'password'][0]
if not password['nullable']:
log.info('Dropping NOT NULL constraint for account.password')
session.execute(
'ALTER TABLE account ALTER COLUMN password DROP NOT NULL')
datamanager.mark_changed(session)
def add_od_version_column(context):
session = Session()
if column_exists(session, "od_link", "version"):
return
transaction.get().commit()
session.execute(
"ALTER TABLE od_link ADD COLUMN version INT DEFAULT 0 NOT NULL")
datamanager.mark_changed(session)
transaction.get().commit()
log.info("Added new column 'version' to table 'od_link'")
def add_wp_column_to_company(context):
session = Session()
if TableExists(session, "company"):
session.execute(
"ALTER TABLE company ADD workers_participated bool DEFAULT NULL")
model.metadata.create_all(session.bind, checkfirst=True)
datamanager.mark_changed(session)
transaction.get().commit()
log.info("Added new column 'workers_participated' to table 'company'")
def add_column_for_existing_measures(context):
session = Session()
inspector = Inspector.from_engine(session.bind)
columns = [
c['name'] for c in inspector.get_columns(model.Risk.__table__.name)
]
if 'existing_measures' not in columns:
log.info('Adding existing_measures column for risks')
session.execute("ALTER TABLE %s ADD existing_measures TEXT" %
model.Risk.__table__.name)
datamanager.mark_changed(session)
def add_has_description_column(context):
session = Session()
if ColumnExists(session, 'tree', 'has_description'):
return
transaction.get().commit()
session.execute("ALTER TABLE tree ADD has_description bool DEFAULT 'f'")
model.metadata.create_all(session.bind, checkfirst=True)
datamanager.mark_changed(session)
transaction.get().commit()
log.info("Added new column 'has_description' to table 'tree'")
def add_skip_evaluation_to_model(context):
session = Session()
inspector = Inspector.from_engine(session.bind)
columns = [c['name']
for c in inspector.get_columns(model.Risk.__table__.name)]
if 'skip_evaluation' not in columns:
log.info('Adding skip_evaluation column for risks')
session.execute(
"ALTER TABLE %s ADD skip_evaluation BOOL DEFAULT 'f' NOT NULL" %
model.Risk.__table__.name)
datamanager.mark_changed(session)
def add_column_for_training_notes(context):
session = Session()
inspector = Inspector.from_engine(session.bind)
columns = [
c["name"] for c in inspector.get_columns(model.Risk.__table__.name)
]
if "training_notes" not in columns:
log.info("Adding training_notes column for risks")
session.execute("ALTER TABLE %s ADD training_notes TEXT" %
model.Risk.__table__.name)
datamanager.mark_changed(session)
def add_column_for_custom_risks(context):
session = Session()
inspector = Inspector.from_engine(session.bind)
columns = [c['name']
for c in inspector.get_columns(model.Risk.__table__.name)]
if 'is_custom_risk' not in columns:
log.info('Adding is_custom_risk column for risks')
session.execute(
"ALTER TABLE %s ADD is_custom_risk BOOL NOT NULL DEFAULT FALSE" %
model.Risk.__table__.name)
datamanager.mark_changed(session)
def add_columns_to_company_survey(context):
session = Session()
inspector = Inspector.from_engine(session.bind)
columns = [c["name"] for c in inspector.get_columns(model.Company.__table__.name)]
if "needs_met" not in columns:
log.info("Adding needs_met column for company")
session.execute("ALTER TABLE %s ADD needs_met BOOL " % model.Company.__table__.name)
datamanager.mark_changed(session)
if "recommend_tool" not in columns:
log.info("Adding recommend_tool column for company")
session.execute("ALTER TABLE %s ADD recommend_tool BOOL " % model.Company.__table__.name)
datamanager.mark_changed(session)
def add_column_for_custom_risks(context):
session = Session()
inspector = Inspector.from_engine(session.bind)
columns = [
c['name'] for c in inspector.get_columns(model.Risk.__table__.name)
]
if 'is_custom_risk' not in columns:
log.info('Adding is_custom_risk column for risks')
session.execute(
"ALTER TABLE %s ADD is_custom_risk BOOL NOT NULL DEFAULT FALSE" %
model.Risk.__table__.name)
datamanager.mark_changed(session)
def make_risk_id_column_nullable(context):
""" Make the risk_id column of the Risk table nullable.
This is so that the user can create custom risks. These risks don't
have dexterity counterparts in the survey, so we don't have a value for
risk_id.
"""
session = Session()
inspector = Inspector.from_engine(session.bind)
log.info('Making the risk_id column of Risk table nullable')
session.execute("ALTER TABLE %s ALTER COLUMN risk_id DROP NOT NULL;" %
model.Risk.__table__.name)
datamanager.mark_changed(session)
def PRStatistik(unfaz, dok_typ, edt_typ):
datum = str(strftime("%d.%m.%Y", localtime()))
datum1 = str(strftime("%Y%m%d", localtime()))
sql = z1vrs2aa.insert(
dict(unfaz=unfaz,
datum=datum,
datum1=datum1,
dok_typ=dok_typ,
edt_typ=edt_typ))
session = Session()
session.execute(sql)
mark_changed(session)
def removeChildren(self):
session = Session()
if self.path:
filter = sql.and_(SurveyTreeItem.session_id == self.session_id,
SurveyTreeItem.path.like(self.path + "%"),
SurveyTreeItem.id != self.id)
else:
filter = sql.and_(SurveyTreeItem.session_id == self.session_id,
SurveyTreeItem.id != self.id)
session.execute(SurveyTreeItem.__table__.delete().where(filter))
self.session.touch()
datamanager.mark_changed(session)
def add_has_description_column(context):
session = Session()
if ColumnExists(session, 'tree', 'has_description'):
return
transaction.get().commit()
session.execute(
"ALTER TABLE tree ADD has_description bool DEFAULT 'f'")
model.metadata.create_all(session.bind, checkfirst=True)
datamanager.mark_changed(session)
transaction.get().commit()
log.info("Added new column 'has_description' to table 'tree'")
def updateAbsenteePercentage(context):
from z3c.saconfig import Session
from euphorie.deployment.upgrade.utils import ColumnType
from zope.sqlalchemy import datamanager
session=Session()
if ColumnType(session, "dutch_company", "absentee_percentage")=="numeric(5,2)":
return
log.info("Changing type for dutch_company.absentee_percentage to NUMERIC(5,2)")
session.execute("ALTER TABLE dutch_company ALTER COLUMN absentee_percentage TYPE NUMERIC(5,2);")
datamanager.mark_changed(session)
def addTermsAndConditionsColumn(context):
from z3c.saconfig import Session
from euphorie.deployment.upgrade.utils import ColumnExists
from zope.sqlalchemy import datamanager
import transaction
session = Session()
if ColumnExists(session, "user", "tc_approved"):
return
log.info("Adding tc_approved column to account table")
session.execute("ALTER TABLE account ADD COLUMN tc_approved INT")
datamanager.mark_changed(session)
transaction.get().commit()
def make_risk_id_column_nullable(context):
""" Make the risk_id column of the Risk table nullable.
This is so that the user can create custom risks. These risks don't
have dexterity counterparts in the survey, so we don't have a value for
risk_id.
"""
session = Session()
inspector = Inspector.from_engine(session.bind)
log.info('Making the risk_id column of Risk table nullable')
session.execute(
"ALTER TABLE %s ALTER COLUMN risk_id DROP NOT NULL;" %
model.Risk.__table__.name
)
datamanager.mark_changed(session)
def add_columns_to_company_survey(context):
session = Session()
inspector = Inspector.from_engine(session.bind)
columns = [
c['name'] for c in inspector.get_columns(model.Company.__table__.name)
]
if 'needs_met' not in columns:
log.info('Adding needs_met column for company')
session.execute("ALTER TABLE %s ADD needs_met BOOL " %
model.Company.__table__.name)
datamanager.mark_changed(session)
if 'recommend_tool' not in columns:
log.info('Adding recommend_tool column for company')
session.execute("ALTER TABLE %s ADD recommend_tool BOOL " %
model.Company.__table__.name)
datamanager.mark_changed(session)
def getStatus(self):
# Note: Optional modules with a yes-answer are not distinguishable
# from non-optional modules, and ignored.
session_id = SessionManager.id
query = self.query % dict(sessionid=session_id)
session = Session()
result = session.execute(query).fetchall()
modules = {}
base_url = "%s/identification" % self.request.survey.absolute_url()
for row in result:
module = modules.setdefault(row.module, dict())
if "url" not in module:
module["url"] = "%s/%s" % (base_url, int(row.module))
module["path"] = row.module
if row.status != "ignore":
module["total"] = module.get("total", 0) + row.count
module[row.status] = {'count': row.count}
titles = dict(session.query(model.Module.path, model.Module.title)
.filter(model.Module.session_id == session_id)
.filter(model.Module.path.in_(modules.keys())))
for module in modules.values():
module["title"] = titles[module["path"]]
for status in ["postponed", "ok", "risk"]:
if status in module:
module[status]["width"] = int(570 *
(float(module[status]["count"]) / module["total"]))
self.status = modules.values()
self.status.sort(key=lambda m: m["path"])
def updateAbsenteePercentage(context):
from euphorie.deployment.upgrade.utils import ColumnType
from z3c.saconfig import Session
from zope.sqlalchemy import datamanager
session = Session()
if ColumnType(session, "dutch_company",
"absentee_percentage") == "numeric(5,2)":
return
log.info(
"Changing type for dutch_company.absentee_percentage to NUMERIC(5,2)")
session.execute(
"ALTER TABLE dutch_company ALTER COLUMN absentee_percentage TYPE NUMERIC(5,2);"
)
datamanager.mark_changed(session)
def removeChildren(self, excluded=[]):
if self.id not in excluded:
excluded.append(self.id)
session = Session()
if self.path:
filter = sql.and_(
SurveyTreeItem.session_id == self.session_id,
SurveyTreeItem.path.like(self.path + "%"),
sql.not_(SurveyTreeItem.id.in_(excluded)),
)
else:
filter = sql.and_(
SurveyTreeItem.session_id == self.session_id,
sql.not_(SurveyTreeItem.id.in_(excluded)),
)
removed = session.query(SurveyTreeItem).filter(filter).all()
session.execute(SurveyTreeItem.__table__.delete().where(filter))
self.session.touch()
datamanager.mark_changed(session)
return removed
def removeChildren(self, excluded=[]):
if self.id not in excluded:
excluded.append(self.id)
session = Session()
if self.path:
filter = sql.and_(
SurveyTreeItem.session_id == self.session_id,
SurveyTreeItem.path.like(self.path + "%"),
sql.not_(SurveyTreeItem.id.in_(excluded))
)
else:
filter = sql.and_(
SurveyTreeItem.session_id == self.session_id,
sql.not_(SurveyTreeItem.id.in_(excluded))
)
removed = session.query(SurveyTreeItem).filter(filter).all()
session.execute(SurveyTreeItem.__table__.delete().where(filter))
self.session.touch()
datamanager.mark_changed(session)
return removed
def add_column_to_account(context):
""" Adds a new column to the Account table which indicates whether the
account in question is a guest account, a converted guest account or
neither.
"""
session = Session()
if ColumnExists(session, "account", "account_type"):
log.info("account_type column already exists in Account table!")
return
log.info('Adding account_type column to Account table')
q = "ALTER TABLE account ADD COLUMN account_type CHARACTER varying(16)";
try:
session.execute(q)
except InternalError, e:
# There might be previous SQL queries which failed due to the
# account_type column not yet being in the Account table. For example,
# the authenticate method in authentication.py does such a query.
session.rollback()
transaction.commit()
session.execute(q)
def add_column_to_account(context):
""" Adds a new column to the Account table which indicates whether the
account in question is a guest account, a converted guest account or
neither.
"""
session = Session()
if ColumnExists(session, "account", "account_type"):
log.info("account_type column already exists in Account table!")
return
log.info('Adding account_type column to Account table')
q = "ALTER TABLE account ADD COLUMN account_type CHARACTER varying(16)"
try:
session.execute(q)
except InternalError, e:
# There might be previous SQL queries which failed due to the
# account_type column not yet being in the Account table. For example,
# the authenticate method in authentication.py does such a query.
session.rollback()
transaction.commit()
session.execute(q)
def migrateCompanyTable(context):
from z3c.saconfig import Session
from euphorie.deployment.upgrade.utils import ColumnExists
from euphorie.deployment.upgrade.utils import TableExists
from euphorie.client import model
from zope.sqlalchemy import datamanager
import transaction
session = Session()
if ColumnExists(session, "company", "referer"):
return
if TableExists(session, "company"):
log.info("Moving company table to dutch_company")
session.execute("ALTER TABLE company RENAME TO dutch_company")
session.execute(
"ALTER SEQUENCE company_id_seq RENAME TO dutch_company_id_seq")
session.execute(
"ALTER INDEX ix_company_session_id RENAME TO "
"ix_dutch_company_session_id")
model.metadata.create_all(session.bind, checkfirst=True)
datamanager.mark_changed(session)
transaction.get().commit()
log.info("Creating new company table")
def asPlainText(self, data={}):
if 'dry-run' in data:
yield "*** DRY RUN ***\n"
total_reclaimed = 0
for (studentId, unclaimed) in Session.execute("""
SELECT a.studentId,
SUM(coinsAwarded) -
COALESCE((SELECT SUM(amount) FROM coinAward ca WHERE ca.studentId = a.studentId), 0)
AS unclaimed
FROM answer a, student s
WHERE coinsAwarded > 0
AND a.studentId = s.studentId AND s.hostId = 1
AND a.timeEnd < CURDATE() - INTERVAL 2 YEAR
AND a.studentId NOT IN (SELECT DISTINCT c.studentId FROM coinAward c WHERE c.awardTime >= CURDATE() - INTERVAL 2 YEAR)
GROUP BY a.studentId
HAVING unclaimed > 0
"""):
total_reclaimed += int(unclaimed)
if 'dry-run' in data:
txId = 'DRY_RUN'
else:
txId = coin.sendTransaction(EIAS_WALLET, unclaimed, message="Auto-reclaim of awards")
# Worked, so update database
Session.add(db.CoinAward(
studentId=studentId,
amount=int(unclaimed),
walletId=EIAS_WALLET,
txId=txId,
awardTime=utcnow(), # NB: So it gets mocked in the tests
))
Session.flush()
yield "StudentId: %d Unclaimed: %d Transaction: %s\n" % (
studentId,
unclaimed,
txId,
)
yield "Total: %d\n" % total_reclaimed
def getRisks(self, module_paths):
""" Return a list of risk dicts for risks that belong to the modules
with paths as specified in module_paths.
"""
session = Session()
session_id = SessionManager.id
# First, we need to compute the actual module paths, making sure that
# skipped optional modules are excluded
# This means top-level module paths like 001 or 001002 can be replaced
# by several sub-modules paths like 001002, 001003 and 001002001
module_query = """
SELECT path
FROM tree
WHERE
session_id={0}
AND type='module'
AND skip_children='f'
and tree.path similar to '({1}%)'
ORDER BY path
""".format(session_id, "%|".join(module_paths))
module_res = session.execute(module_query).fetchall()
def nodes(paths):
paths = sorted(paths, reverse=True)
ret = []
for elem in paths:
if not [x for x in ret if x.startswith(elem)]:
ret.append(elem)
return ret
# Here we make sure that only the longest paths of sub-modules
# are used, but not the parents. Example
# (001, 002, 001001, 001003) will be turned into
# (001001, 001003, 002), since the parent 001 contains sub-modules,
# and some of those might have been de-selected, like 001002
filtered_module_paths = nodes([x[0] for x in module_res])
child_node = orm.aliased(model.Risk)
risks = session.query(
model.Module.path,
child_node.id,
child_node.path,
child_node.title,
child_node.identification,
child_node.priority,
child_node.risk_type,
child_node.zodb_path,
child_node.is_custom_risk,
child_node.postponed
).filter(
sql.and_(
model.Module.session_id == session_id,
model.Module.path.in_(filtered_module_paths),
sql.and_(
child_node.session_id == model.Module.session_id,
child_node.depth > model.Module.depth,
child_node.path.like(model.Module.path + "%")
)
)
)
def _module_path(path):
# Due to the extended query above that replaces top-module paths
# with sub-module paths (if present), we need to cut back the path
# under which we store each risk back to the original top-level
# module path
for mp in module_paths:
if path.startswith(mp):
return mp
return [{
'module_path': _module_path(risk[0]),
'id': risk[1],
'path': risk[2],
'title': risk[3],
'identification': risk[4],
'priority': risk[5],
'risk_type': risk[6],
'zodb_path': risk[7],
'is_custom_risk': risk[8],
'postponed': risk[9],
} for risk in risks]
def getModules(self):
""" Return a list of dicts of all the top-level modules and locations
belonging to this survey.
"""
session = Session()
session_id = SessionManager.id
base_url = "%s/identification" % self.request.survey.absolute_url()
profile = extractProfile(self.request.survey, SessionManager.session)
module_query = self.module_query(
sessionid=session_id,
optional_modules=len(profile) and "(%s)" % (','.join(
["'%s'" % k for k in profile.keys()])) or None
)
module_res = session.execute(module_query).fetchall()
modules_and_profiles = {}
for row in module_res:
if row[0] is not None:
if row[0].find('profile') > 0:
path = row[0][:3]
modules_and_profiles[path] = 'profile'
else:
modules_and_profiles[row[0]] = ''
module_paths = [
p[0] for p in session.execute(module_query).fetchall() if
p[0] is not None]
module_paths = modules_and_profiles.keys()
module_paths = sorted(module_paths)
parent_node = orm.aliased(model.Module)
titles = dict(session.query(model.Module.path, model.Module.title)
.filter(model.Module.session_id == session_id)
.filter(model.Module.path.in_(module_paths)))
location_titles = dict(session.query(
model.Module.path,
parent_node.title
).filter(
model.Module.session_id == session_id).filter(
model.Module.path.in_(module_paths)).filter(
sql.and_(
parent_node.session_id == session_id,
parent_node.depth < model.Module.depth,
model.Module.path.like(parent_node.path + "%")
)
))
modules = {}
toc = {}
title_custom_risks = utils.get_translated_custom_risks_title(self.request)
for path in module_paths:
number = ".".join(self.slicePath(path))
# top-level module, always include it in the toc
if len(path) == 3:
title = titles[path]
if title == 'title_other_risks':
title = title_custom_risks
toc[path] = {
'path': path,
'title': title,
'locations': [],
'number': number,
}
# If this is a profile (aka container for locations), skip
# adding to the list of modules
if modules_and_profiles[path] == 'profile':
continue
# sub-module (location) or location container
else:
if path in location_titles:
title = u"{0} - {1}".format(location_titles[path], titles[path])
toc[path[:3]]['locations'].append({
'path': path,
'title': titles[path],
'number': number,
})
else:
log.warning(
"Status: found a path for a submodule {0} for which "
"there's no location title.".format(path))
continue
modules[path] = {
'path': path,
'title': title,
'url': '%s/%s' % (base_url, '/'.join(self.slicePath(path))),
'todo': 0,
'ok': 0,
'postponed': 0,
'risk_with_measures': 0,
'risk_without_measures': 0,
'number': number,
}
self.tocdata = toc
return modules
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)
postponed = sql.select([old_tree.postponed], in_old_tree)
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 == u'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,
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(
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,
requirements, responsible, budget,
planning_start, planning_end)
SELECT new_tree.id,
action_plan.action_plan,
action_plan.prevention_plan,
action_plan.requirements,
action_plan.responsible,
action_plan.budget,
action_plan.planning_start,
action_plan.planning_end
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)
session.query(Company)\
.filter(Company.session == other)\
.update({'session_id': self.id},
synchronize_session=False)
