def _get_active_pending_versions(self, mappedClass, uid):
"""
Returns the current active version and the pending version to review
"""
# TODO: Is this still needed?
def _check_mandatory_keys():
mandatory_keys = get_mandatory_keys(self.request, 'a')
log.debug(mandatory_keys)
# Get the current active version number
av = DBSession.query(
mappedClass.version
). \
filter(mappedClass.identifier == uid). \
filter(mappedClass.fk_status == 2). \
first()
active_version = av.version if av is not None else None
# Get the lowest pending version
pv = DBSession.query(min(mappedClass.version)). \
filter(mappedClass.identifier == uid). \
filter(mappedClass.fk_status == 1). \
first()
pending_version = pv.version if pv is not None else None
# Some logging
log.debug("active version: %s" % active_version)
log.debug("pending version: %s" % pending_version)
return active_version, pending_version
def get_comparison(
self, mappedClass, uid, ref_version_number, new_version_number):
"""
Function to do the actual comparison and return a json
"""
recalculated = False
if (ref_version_number == 0
or (new_version_number == 1 and ref_version_number == 1)):
ref_object = None
ref_version_number = None
else:
# Get the reference object
ref_object = self.protocol.read_one_by_version(
self.request, uid, ref_version_number, geometry='full',
translate=False
)
# Check to see if the new version is based directly on the ref version
new_previous_version = DBSession.query(
mappedClass.previous_version
). \
filter(mappedClass.identifier == uid). \
filter(mappedClass.version == new_version_number). \
first()
if (ref_object is None and new_previous_version is not None or
new_version_number == 1 or
ref_version_number == new_version_number or
(new_previous_version is not None
and new_previous_version.previous_version
== ref_version_number)):
# Show the new version as it is in the database
new_object = self.protocol.read_one_by_version(
self.request, uid, new_version_number, geometry='full',
translate=False
)
else:
# Query the diff of the new version to apply to the ref version
new_diff_query = DBSession.query(
Changeset.diff
). \
join(mappedClass). \
filter(mappedClass.identifier == uid). \
filter(mappedClass.version == new_version_number). \
first()
new_diff = json.loads(new_diff_query.diff)
# Get the reference object
new_object = self.protocol.read_one_by_version(
self.request, uid, ref_version_number, geometry='full',
translate=False
)
# Apply the diff to the ref_object
new_object = self.recalc(mappedClass, new_object, new_diff)
recalculated = True
return [ref_object, new_object], recalculated
def succeed():
"""
"""
# Request all submitted values
profile_field = self.request.POST.get("profile")
username_field = self.request.POST.get("username")
firstname_field = self.request.POST.get("firstname")
lastname_field = self.request.POST.get("lastname")
password_field = self.request.POST.get("password")
email_field = self.request.POST.get("email")
# Get the selected profile
selected_profile = DBSession.query(Profile).filter(
Profile.code == profile_field).first()
# Get the initial user group
user_group = DBSession.query(Group).filter(
Group.name == "editors").first()
# Create an activation uuid
activation_uuid = str(uuid.uuid4())
# Create a new user
new_user = User(username_field,
password_field,
email_field,
firstname=firstname_field,
lastname=lastname_field,
activation_uuid=activation_uuid,
registration_timestamp=datetime.now())
# Set the user profile
new_user.profiles = [selected_profile]
new_user.groups = [user_group]
# Commit the new user
DBSession.add(new_user)
activation_dict = {
"firstname":
new_user.firstname,
"lastname":
new_user.lastname,
"activation_link":
"http://%s/users/activate?uuid=%s&username="******"%s" % (self.request.environ['HTTP_HOST'], activation_uuid,
new_user.username)
}
email_text = render(
get_customized_template_path(self.request,
'emails/account_activation.mak'),
activation_dict, self.request)
self._send_email([email_field], _(u"Activate your Account"),
email_text)
return render_to_response(
get_customized_template_path(self.request,
'users/registration_success.mak'),
{}, self.request)
def _get_query_for_editors():
"""
Returns a query that selects versions available to editors.
"""
active_versions = DBSession.query(
mappedClass.version,
mappedClass.fk_status
). \
filter(mappedClass.identifier == uid). \
filter(or_(
mappedClass.fk_status == 2, mappedClass.fk_status == 3))
own_filters = and_(
mappedClass.identifier == uid,
not_(mappedClass.fk_status == 2),
not_(mappedClass.fk_status == 3),
User.username == self.request.user.username)
own_versions = DBSession.query(
mappedClass.version,
mappedClass.fk_status
). \
join(Changeset). \
join(User). \
filter(*own_filters)
return active_versions.union(own_versions)
def _get_group_by(self, group_by, langs):
"""
Returns
- an array with SubQueries
- an array with Columns to select from
"""
subqueries = []
columns = []
for i, group_key in enumerate(group_by):
# first one different
if i == 0:
subquery = DBSession.query(
self.db_value.value.label('v'),
self.db_tag.fk_tag_group.label('tg_id')
). \
join(
self.db_tag,
self.db_tag.fk_value == self.db_value.id). \
join(self.db_key, self.db_key.id == self.db_tag.fk_key). \
filter(self.db_key.key == group_key). \
filter(self.db_key.fk_language == None)
else:
subquery = DBSession.query(
self.db_item.id.label('item_id'),
self.db_value.value.label('v'),
). \
join(
self.db_taggroup,
self.db_taggroup.fk_item == self.db_item.id). \
join(
self.db_tag,
self.db_taggroup.id == self.db_tag.fk_tag_group). \
join(
self.db_value,
self.db_value.id == self.db_tag.fk_value). \
join(self.db_key, self.db_key.id == self.db_tag.fk_key). \
filter(self.db_key.key == group_key). \
filter(self.db_key.fk_language == None)
for l in langs:
__, value_translation = self.protocol._get_translatedKV(
l[1], self.db_key, self.db_value)
subquery = subquery.add_columns(
value_translation.c.value_translated.label(l[0]))
subquery = subquery. \
outerjoin(
value_translation,
value_translation.c.value_original_id ==
self.db_value.id)
subquery = subquery.subquery()
subqueries.append(subquery)
columns.append(subquery.c.v)
for l in langs:
columns.append(subquery.c[l[0]])
return subqueries, columns
def main(argv=sys.argv):
if len(argv) != 2:
usage(argv)
config_uri = argv[1]
setup_logging(config_uri)
settings = get_appsettings(config_uri)
engine = engine_from_config(settings, 'sqlalchemy.')
DBSession.configure(bind=engine)
Base.metadata.create_all(engine)
add_sql_triggers(engine)
add_initial_values(engine, settings)
def _get_metadata(self, mappedClass, uid, refVersion, newVersion):
refTimestamp = newTimestamp = None
refUserid = newUserid = None
refUsername = newUsername = None
refQuery = DBSession.query(
Changeset.timestamp,
User.id.label('userid'),
User.username
). \
join(mappedClass). \
join(User, Changeset.fk_user == User.id). \
filter(mappedClass.identifier == uid). \
filter(mappedClass.version == refVersion). \
first()
if refQuery is not None:
refTimestamp = refQuery.timestamp
refUserid = refQuery.userid
refUsername = refQuery.username
newQuery = DBSession.query(
Changeset.timestamp,
User.id.label('userid'),
User.username
). \
join(mappedClass). \
join(User, Changeset.fk_user == User.id). \
filter(mappedClass.identifier == uid). \
filter(mappedClass.version == newVersion). \
first()
if newQuery is not None:
newTimestamp = newQuery.timestamp
newUserid = newQuery.userid
newUsername = newQuery.username
metadata = {
'ref_version': refVersion,
'ref_timestamp': str(refTimestamp),
'ref_userid': refUserid,
'ref_username': refUsername,
'new_version': newVersion,
'new_timestamp': str(newTimestamp),
'new_userid': newUserid,
'new_username': newUsername,
'identifier': uid,
'type': mappedClass.__table__.name,
}
return metadata
def add_user(request):
if 'groups' in request.params:
requested_groups = request.POST.getall('groups')
else:
raise HTTPBadRequest("Missing group parameter")
if 'username' in request.params:
username = request.params['username']
else:
raise HTTPBadRequest("Missing username parameter")
if 'email' in request.params:
email = request.params['email']
else:
raise HTTPBadRequest("Missing email parameter")
if 'password' in request.params:
password = request.params['password']
else:
raise HTTPBadRequest("Missing password parameter")
# Check email
email_query = DBSession.query(User).filter(User.email == email)
try:
email_query.one()
raise HTTPBadRequest(
'There already exists a user with this email address')
except NoResultFound:
pass
# Check groups
groups = DBSession.query(Group).filter(
Group.name.in_(requested_groups)).all()
if len(groups) == 0:
raise HTTPBadRequest("Invalid group parameter")
if not _user_exists(User.username, username):
# Create an activation uuid
activation_uuid = uuid.uuid4()
# Create a new user
new_user = User(username=username,
password=password,
email=email,
activation_uuid=activation_uuid,
registration_timestamp=datetime.now())
new_user.groups = groups
return {"success": True, "msg": "New user created successfully."}
else:
request.response.status = 400
return {"success": False, "msg": "User exists."}
def _get_attribute_functions(self, attributes):
"""
Returns
- an array with SubQueries
- an array with Columns to select from
"""
subqueries = []
columns = []
for attr in attributes:
function = attributes[attr]
if function == 'sum':
sq = DBSession.query(
self.db_item.id.label('item_id'),
cast(self.db_value.value, Float).label('v')
). \
join(self.db_taggroup). \
join(
self.db_tag,
self.db_taggroup.id == self.db_tag.fk_tag_group). \
join(
self.db_value,
self.db_value.id == self.db_tag.fk_value). \
join(self.db_key, self.db_key.id == self.db_tag.fk_key). \
filter(self.db_key.key == attr). \
subquery()
subqueries.append(sq)
columns.append(func.sum(sq.c.v))
elif function == 'count' or function == 'count distinct':
if attr == 'Activity' or attr == 'Stakeholder':
columns.append(func.count())
else:
sq = DBSession.query(
self.db_item.id.label('item_id'),
self.db_value.value.label('v')
). \
join(self.db_taggroup). \
join(
self.db_tag,
self.db_taggroup.id == self.db_tag.fk_tag_group). \
join(self.db_value). \
join(self.db_key). \
filter(self.db_key.key == attr). \
subquery()
subqueries.append(sq)
if (function == 'count distinct'):
columns.append(func.count(distinct(sq.c.v)))
else:
columns.append(func.count(sq.c.v))
return subqueries, columns
def user_profile_json(request):
"""
This function returns a JSON with information about a user.
Depending on the rights of the current user it also contains the
email address and information about whether the current user has
permission to edit this data or not.
"""
ret = {'success': True, 'editable': False}
# try to find requested user
try:
user = DBSession.query(User).filter(
User.username == request.matchdict['userid']).one()
except NoResultFound:
ret['success'] = False
ret['msg'] = 'There is no user with this username.'
return ret
# collect very basic information (so far just username and -id)
ret["data"] = {'username': user.username, 'userid': user.id}
# if current user is admin, also show email
if isinstance(has_permission('administer', request.context, request),
ACLAllowed):
ret['data']['email'] = user.email
# if requested user is also current user, also show email and allow to edit
if authenticated_userid(request) == user.username:
ret['data']['email'] = user.email
ret['editable'] = True
return ret
def get_translated_db_keys(mappedClass, db_keys, db_lang):
"""
Returns a query array with original and translated keys from the database.
"""
if len(db_keys) == 0:
return []
translation = aliased(mappedClass)
q = DBSession.query(
mappedClass.key.label('original'),
translation.key.label('translation')
). \
join(translation, mappedClass.translations). \
filter(mappedClass.key.in_(db_keys)). \
filter(mappedClass.original == None). \
filter(translation.language == db_lang). \
all()
if q is not None:
return q
# If nothing found, return None
return []
def get_profiles():
"""
Never return the global profile!
"""
profiles = DBSession.query(Profile.code, Profile.code).all()
ret = [p for p in profiles if p[0] != 'global']
return ret
def get_languages(all=False):
"""
By default do not return language with locale 'code'
"""
# TODO: This does not necessarily belong here. Also, all the stuff needed
# for each view (languages, profiles, keys, ...) should be loaded in a
# single request for performance reasons.
languages = DBSession.query(Language.locale, Language.local_name).all()
return [l for l in languages if l[0] != 'code']
def _translate_category(original, translation, TableItem, locale):
"""
Helper function to insert or update a single translation for a
category.
"""
# Query the database to find the english entry of the category
english_query = DBSession.query(TableItem). \
filter(TableItem.name == original). \
filter(TableItem.fk_language == 1)
eng = english_query.all()
if len(eng) == 0:
return {
'success': False,
'msg': 'No english value found for "%s", translation "%s" not '
'inserted.' % (original, translation)
}
for e in eng:
# Check if translation already exists
translation_query = DBSession.query(TableItem). \
filter(TableItem.original == e). \
filter(TableItem.language == locale)
translation_entry = translation_query.first()
if translation_entry is None:
# Insert a new translation
new_translation = TableItem(translation)
new_translation.language = locale
new_translation.original = e
DBSession.add(new_translation)
else:
# Update an existing translation
translation_entry.name = translation
return {
'success': True,
'msg': 'Translation "%s" inserted for value "%s".' % (
translation, original)
}
def _cast_to_number(self, key):
"""
Returns True if the given key has number values
"""
q = DBSession.query(cast(self.db_value.value, Float)). \
join(self.db_tag). \
join(self.db_key). \
filter(self.db_key.key == key)
try:
q.all()
return True
except:
return False
def language_store(request):
data = []
langs = DBSession.query(Language).all()
for l in langs:
data.append({
'locale': l.locale,
'english_name': l.english_name,
'local_name': l.local_name
})
ret = {}
ret['data'] = data
ret['success'] = True
ret['total'] = len(langs)
return ret
def _review_check_involvement(self, mappedClass, identifier, version):
"""
Function to check if an item can be reviewed through involvements or
not.
Assumptions: Involvements can only be reviewed from Activity side.
mappedClass: The class where a review of the version is to be made
through the involvement
identifier: The identifier of the item to review through the
involvement
version: The version of the item to review through the involvement
"""
if mappedClass == Stakeholder:
"""
The Stakeholder CANNOT be reviewed if:
[-1] The Stakeholder does not exist.
[-2] There is no active version of the Stakeholder.
The Stakeholder CAN be reviewed if:
[1] There exists an active version of the Stakeholder.
"""
q = DBSession.query(Stakeholder.fk_status). \
filter(Stakeholder.identifier == identifier). \
all()
if q is None:
# The Stakeholder does not exist
return -1
for s in q:
if s.fk_status == 2:
# There exists an active version of the Stakeholder.
return 1
# There is no active version of the Stakeholder.
return -2
elif mappedClass == Activity:
"""
The Activity cannot be reviewed from Stakeholder side
[-3]
"""
return -3
return 0
def _user_already_exists(node, value):
"""
Validates the username input field and makes sure that the username does
not contain special chars and does not yet exist in the database.
"""
username_pattern = re.compile("^[a-zA-Z0-9\.\-_]+$")
if username_pattern.search(value) is None:
raise colander.Invalid(
node, "Username '%s' contains invalid characters." % value)
if DBSession.query(User).filter(User.username == value).count() > 0:
raise colander.Invalid(node, "Username '%s' already exists." % value)
return None
def approve(self):
"""
User moderation: approve newly activated users.
"""
# Get the URL parameters
user_uuid = self.request.params.get("user")
user_username = self.request.params.get("name")
# Try to the user, who must not yet be approved
user = DBSession.query(User).filter(
and_(User.uuid == user_uuid, User.username == user_username,
User.is_approved == False)).first()
# Raise a BadRequest if no user is found
if user is None:
raise HTTPBadRequest(
"User is already approved or does not exist in the database.")
# Set the is_approved attribute to TRUE
user.is_approved = True
conf_dict = {
"firstname": user.firstname,
"lastname": user.lastname,
"host": "http://%s" % self.request.environ['HTTP_HOST']
}
email_text = render(get_customized_template_path(
self.request, 'emails/account_approval_confirmation.mak'),
conf_dict,
request=self.request)
# Send the email
self._send_email([user.email], "Account confirmation on %s" %
"http://%s" % self.request.environ['HTTP_HOST'],
email_text)
# Return the username to the template
return render_to_response(
get_customized_template_path(self.request,
'users/approval_successful.mak'),
{'username': user_username}, self.request)
def reset(self):
if self.request.params.get('came_from') is not None:
came_from = self.request.params.get('came_from')
else:
came_from = self.request.route_url('map_view')
# Make sure the user is not logged in
principals = effective_principals(self.request)
if "system.Authenticated" in principals:
return HTTPFound(location=came_from)
username = self.request.params.get("username")
user = DBSession.query(User).filter(User.username == username).first()
if user is None:
msg = _(u"No registered user found with this email address.")
return render_to_response(
get_customized_template_path(self.request,
'users/reset_password_form.mak'),
{
'came_from': came_from,
'warning': msg
}, self.request)
new_password = user.set_new_password()
body = render(
get_customized_template_path(self.request,
'emails/reset_password.mak'), {
'user': user.username,
'new_password': new_password
}, self.request)
self._send_email([user.email], _(u"Password reset"), body)
return render_to_response(
get_customized_template_path(self.request,
'users/reset_password_success.mak'),
{}, self.request)
def _get_available_profiles(self):
"""
Returns a list of tuples with available profiles. Each tuple contains
the profile code and profile name.
"""
# Get a list of available profiles
available_profiles = []
# Query all profiles
for p in DBSession.query(Profile).all():
# Insert "global" profile always on top
if p.code == 'global':
profile = _processProfile(self.request, p, True)
if profile is not None:
available_profiles.insert(
0, (profile['profile'], profile['name']))
else:
profile = _processProfile(self.request, p)
if profile is not None:
available_profiles.append(
(profile['profile'], profile['name']))
return available_profiles
def _user_exists(filterColumn, filterAttr):
if DBSession.query(User).filter(filterColumn == filterAttr).count() > 0:
return True
return False
def user_update(request):
"""
This function updates user information and sends back a JSON with 'success'
(true/false) and 'msg'
User must be logged in, information can only be changed by the user himself
and if application is not running in demo mode and username is in ignored.
"""
ret = {'success': False}
# List of usernames which cannot be changed when in demo mode
ignored_demo_usernames = ['editor', 'moderator']
mode = None
if 'lmkp.mode' in request.registry.settings:
if str(request.registry.settings['lmkp.mode']).lower() == 'demo':
mode = 'demo'
username = request.POST['username'] if 'username' in request.POST else None
email = request.POST['email'] if 'email' in request.POST else None
new_password = request.POST['new_password1'] \
if 'new_password1' in request.POST else None
old_password = request.POST['old_password'] \
if 'old_password' in request.POST else None
if username and (email or (new_password and old_password)):
# Return error message if in demo mode and username one of the ignored
if (mode is not None and mode == 'demo'
and username in ignored_demo_usernames):
ret['msg'] = 'You are not allowed to change this user in demo '
'mode.'
return ret
# try to find requested user
try:
user = DBSession.query(User).filter(
User.username == username).one()
# check privileges (only current user can update his own
# information)
if authenticated_userid(request) == user.username:
# do the update (so far only email)
if email:
user.email = email
import transaction
transaction.commit()
ret['success'] = True
ret['msg'] = 'Information successfully updated.'
# do password update
elif new_password and old_password:
# check old password first
if User.check_password(username, old_password):
user.password = new_password
import transaction
transaction.commit()
ret['success'] = True
ret['msg'] = 'Password successfully updated.'
else:
ret['msg'] = 'Wrong password.'
else:
ret['msg'] = 'You do not have the right to update this '
'information.'
return ret
except NoResultFound:
ret['msg'] = 'There is no user with this username.'
return ret
return ret
def account(self):
"""
Shows user account details to registered users.
"""
_ = self.request.translate
userid = authenticated_userid(self.request)
# Define a colander Schema for the self registration
class Schema(colander.Schema):
username = colander.SchemaNode(
colander.String(),
missing=None,
widget=deform.widget.TextInputWidget(
readonly=True,
readonly_template='readonly/customTextinputReadonly'),
title=_('Username'))
password = colander.SchemaNode(
colander.String(),
validator=colander.Length(min=5),
widget=deform.widget.CheckedPasswordWidget(size=20),
title=_('Password'))
firstname = colander.SchemaNode(colander.String(),
missing=None,
title=_('First Name'))
lastname = colander.SchemaNode(colander.String(),
missing=None,
title=_('Last Name'))
email = colander.SchemaNode(
colander.String(),
missing=None,
widget=deform.widget.TextInputWidget(
readonly=True,
readonly_template='readonly/customTextinputReadonly'),
title=_('Valid Email'),
)
schema = Schema()
deform.Form.set_default_renderer(mako_renderer)
form = deform.Form(
schema,
buttons=(deform.Button(title=_(u'Update'),
css_class='btn btn-primary'), ),
use_ajax=True)
# Get the user data
user = DBSession.query(User).filter(User.username == userid).first()
data = {
'username': user.username,
'firstname': user.firstname,
'lastname': user.lastname,
'email': user.email
}
def succeed():
# Request all submitted values
firstname_field = self.request.POST.get("firstname")
lastname_field = self.request.POST.get("lastname")
password_field = self.request.POST.get("password")
# Update user fields
user.firstname = firstname_field
user.lastname = lastname_field
if password_field is not None and password_field != '':
user.password = password_field
return Response('<div class="alert alert-success">%s</div>' %
_('Your user settings were updated.'))
ret = self._render_form(form, success=succeed, appstruct=data)
if not isinstance(ret, Response):
self._handle_parameters()
ret['profile'] = get_current_profile(self.request)
ret['locale'] = get_current_locale(self.request)
ret['username'] = user.username
return render_to_response(
get_customized_template_path(self.request,
'users/account_form.mak'), ret,
self.request)
return ret
def activate(self):
"""
"""
activation_uuid = self.request.params.get("uuid")
username = self.request.params.get("username")
if validate_uuid(activation_uuid) is False:
raise HTTPBadRequest('Invalid UUID')
# Get the user
user = DBSession.query(User).filter(
and_(User.activation_uuid == activation_uuid,
User.username == username, User.is_active == False)).first()
# Raise a BadRequest if no user is found
if user is None:
raise HTTPBadRequest('User not found or already active.')
# A timedelta of 48 hours equals 2 days
delta = timedelta(hours=48)
# Create a timezone info
tz = psycopg2.tz.FixedOffsetTimezone(offset=0, name="UTC")
# Check if the registration timestamp is not older than 48 hours
if (datetime.now(tz) - delta) > user.registration_timestamp:
raise HTTPBadRequest("Activation link has been expired.")
# Set the user active and set the activation uuid to NULL
user.is_active = True
user.activation_uuid = None
approval_dict = {
"username":
user.username,
"firstname":
user.firstname,
"lastname":
user.lastname,
"email":
user.email,
"profiles":
",".join([p.code for p in user.profiles]),
"approval_link":
"http://%s/users/approve?user=%s&name=%s" %
(self.request.environ['HTTP_HOST'], user.uuid, user.username)
}
# Send an email to all moderators of the profile in which the user
# registered.
email_text = render(get_customized_template_path(
self.request, 'emails/account_approval_request.mak'),
approval_dict,
request=self.request)
# Determine profile. Each user should only have one profile when
# registering!
profiles = [p.code for p in user.profiles]
if len(profiles) == 0:
profile = get_default_profile(self.request)
else:
profile = profiles[0]
# Find moderators of this profile
moderators = DBSession.query(User). \
join(users_groups). \
join(Group). \
join(users_profiles). \
join(Profile). \
filter(Group.name == 'moderators'). \
filter(Profile.code == profile)
# A list with email addresses the email is sent to
email_addresses = []
for m in moderators.all():
email_addresses.append(m.email)
if len(email_addresses) == 0:
# If no moderator, try to contact the administrators
for admin_user in DBSession.query(User).join(users_groups).join(
Group).filter(func.lower(Group.name) == 'administrators'):
email_addresses.append(admin_user.email)
log.debug(
"No moderator found for profile %s. Approval emails will be "
"sent to administrators: %s" % (profile, email_addresses))
else:
log.debug(
"Approval emails will be sent to moderators of %s profile: %s"
% (profile, email_addresses))
# Send the email
self._send_email(email_addresses,
"User %s requests approval" % user.username,
email_text)
return render_to_response(
get_customized_template_path(self.request,
'users/activation_successful.mak'),
{'username': user.username}, self.request)