Python DBSession.query方法代码示例

# 需要导入模块: from lmkp.models.meta import DBSession [as 别名]
# 或者: from lmkp.models.meta.DBSession import query [as 别名]
 def _getGroupBy(self, Item, Tag_Group, Tag, Key, Value, group_by):
     """
     Returns
     - an array with SubQueries
     - an array with Columns to select from
     """
     subqueries = []
     columns = []
     for i, gb in enumerate(group_by):
         # first one different
         if i == 0:
             sq = Session.query(Value.value.label('v'),
                                Tag.fk_tag_group.label('group1_taggroupid')).\
                 join(Tag).\
                 join(Key).\
                 filter(Key.key == gb).\
                 subquery()
         else:
             sq = Session.query(Item.id.label('a_id'),
                                Value.value.label('v')).\
                 join(Tag_Group).\
                 join(Tag, Tag_Group.id == Tag.fk_tag_group).\
                 join(Value).\
                 join(Key).\
                 filter(Key.key == gb).\
                 subquery()
         subqueries.append(sq)
         columns.append(sq.c.v)
     return subqueries, columns

# 需要导入模块: from lmkp.models.meta import DBSession [as 别名]
# 或者: from lmkp.models.meta.DBSession import query [as 别名]
    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 = Session.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 = Session.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

# 需要导入模块: from lmkp.models.meta import DBSession [as 别名]
# 或者: from lmkp.models.meta.DBSession import query [as 别名]
        def _get_query_for_editors():
            """
            Returns a query that selects versions available to editors.
            """
            active_versions = Session.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 = Session.query(
                mappedClass.version,
                mappedClass.fk_status
            ).\
                join(Changeset).\
                join(User).\
                filter(*own_filters)
            return active_versions.union(own_versions)

# 需要导入模块: from lmkp.models.meta import DBSession [as 别名]
# 或者: from lmkp.models.meta.DBSession import query [as 别名]
def _get_extjs_config(name, config, language):

    fieldConfig = {}

    # check if translated name is available
    originalKey = Session.query(A_Key.id).filter(A_Key.key == name).filter(A_Key.fk_a_key == None).first()

    # if no original value is found in DB, return None (this cannot be selected)
    if not originalKey:
        return None

    translatedName = Session.query(A_Key).filter(A_Key.fk_a_key == originalKey).filter(A_Key.language == language).first()

    if translatedName:
        fieldConfig['name'] = str(translatedName.key)
    else:
        fieldConfig['name'] = name

    type = 'string'
    try:
        config['type']
        if config['type'] == 'Number':
            type = 'number'
        if config['type'] == 'Date':
            type = 'number'
    except KeyError:
        pass

    fieldConfig['type'] = type

    return fieldConfig

# 需要导入模块: from lmkp.models.meta import DBSession [as 别名]
# 或者: from lmkp.models.meta.DBSession import query [as 别名]
    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 = Session.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 = Session.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

# 需要导入模块: from lmkp.models.meta import DBSession [as 别名]
# 或者: from lmkp.models.meta.DBSession import query [as 别名]
    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 = Session.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 = Session.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

# 需要导入模块: from lmkp.models.meta import DBSession [as 别名]
# 或者: from lmkp.models.meta.DBSession import query [as 别名]
        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 = Session.query(Profile).filter(
                Profile.code == profile_field).first()

            # Get the initial user group
            user_group = Session.query(Group).filter(
                Group.name == "editors").first()

            # Create an activation uuid
            activation_uuid = 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
            Session.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)

# 需要导入模块: from lmkp.models.meta import DBSession [as 别名]
# 或者: from lmkp.models.meta.DBSession import query [as 别名]
    def _translate_keyvalue(original, translation, TableItem, isKey,
        locale, helptext_translation):
        """
        Helper function to insert or update a single translation for a key or a
        value
        """

        # Query the database to find english entry of key or value
        if isKey:
            english_query = Session.query(TableItem).\
                filter(TableItem.key == original).\
                filter(TableItem.fk_language == 1)
        else:
            english_query = Session.query(TableItem).\
                filter(TableItem.value == original).\
                filter(TableItem.fk_language == 1)
        eng = english_query.all()

        if eng is None or 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 for value
            if isKey:
                originalEntry = e.original
                translation_query = Session.query(TableItem).\
                    filter(TableItem.original == originalEntry).\
                    filter(TableItem.language == locale)
            else:
                originalEntry = e
                translation_query = Session.query(TableItem).\
                    filter(TableItem.original == originalEntry).\
                    filter(TableItem.language == locale)
            translation_entry = translation_query.first()

            if translation_entry is None:
                # No translation found, insert a new translation
                if isKey:
                    new_translation = TableItem(translation, e.type)
                else:
                    new_translation = TableItem(translation)
                new_translation.language = locale
                new_translation.original = originalEntry
                if helptext_translation != '':
                    new_translation.helptext = helptext_translation
                Session.add(new_translation)
            else:
                # There is already a translation, update it
                if isKey:
                    translation_entry.key = translation
                else:
                    translation_entry.value = translation
                if helptext_translation != '':
                    translation_entry.helptext = helptext_translation

        return {'success': True, 'msg': 'Translation "%s" inserted for value "%s".' % (translation, original)}

# 需要导入模块: from lmkp.models.meta import DBSession [as 别名]
# 或者: from lmkp.models.meta.DBSession import query [as 别名]
def get_translated_keys(request, global_config, Key, Value):

    # get keys already in database. their fk_a_key must be None (= original)
    db_keys = []
    for db_key in Session.query(Key.key).filter(Key.fk_key == None).all():
        db_keys.append(db_key.key)

    # get values already in database. their fk_a_value must be None
    # (= original)
    db_values = []
    for db_value in Session.query(Value.value).filter(
            Value.fk_value == None).all():
        db_values.append(db_value.value)

    extObject = []
    # Do the translation work from custom configuration format to an
    # ExtJS configuration object.
    fields = global_config['fields']

    localizer = get_localizer(request)

    lang = Session.query(Language).filter(
        Language.locale == localizer.locale_name).first()

    if lang is None:
        lang = Language(1, 'English', 'English', 'en')

    # First process the mandatory fields
    for (name, config) in fields['mandatory'].iteritems():

        currObject = _get_admin_scan(Key, Value, name, config, lang, True,
                                     False)
        extObject.append(currObject)

    # Then process the optional fields
    for (name, config) in fields['optional'].iteritems():

        # check if the field stems from global or local yaml
        local = False
        try:
            config['local']
            local = True
        except KeyError:
            pass

        currObject = _get_admin_scan(
            Key, Value, name, config, lang, False, local)
        extObject.append(currObject)

    ret = {}
    ret['success'] = True
    ret['children'] = extObject
    return ret

# 需要导入模块: from lmkp.models.meta import DBSession [as 别名]
# 或者: from lmkp.models.meta.DBSession import query [as 别名]
    def _get_metadata(self, mappedClass, uid, refVersion, newVersion):

        refTimestamp = newTimestamp = None
        refUserid = newUserid = None
        refUsername = newUsername = None

        refQuery = Session.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 = Session.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

# 需要导入模块: from lmkp.models.meta import DBSession [as 别名]
# 或者: from lmkp.models.meta.DBSession import query [as 别名]
def insert_landmatrix(request):
    """
    Inserts the landmatrix data into an empty, i.e. freshly populated database
    and sets all activities immediately active.
    """

    rootdir = os.path.dirname(os.path.dirname(__file__))

    a = activity_wrapper(request, "%s/documents/landmatrix/%s" % (rootdir, 'landmatrix_activities.json'), 'active')
    s = stakeholder_wrapper(request, "%s/documents/landmatrix/%s" % (rootdir, 'landmatrix_stakeholders.json'), 'active')
    
    """
    Post-processing. Set second version of Activities (with Involvements)
    to 'active'. Also establish link between user1 and profile 'global'.
    """
    # Set all Activities with version 1 to 'inactive' (fk_status = 3)
    Session.query(Activity).filter(Activity.version == 1).\
        update({Activity.fk_status: 3})
    # Set all Activities with version 2 to 'active' (fk_status = 2)
    Session.query(Activity).filter(Activity.version == 2).\
        update({Activity.fk_status: 2})
    # Establish link between profile 'global' and user1
    user1 = Session.query(User).filter(User.username == 'user1').first()
    global_profile = Session.query(Profile).filter(Profile.code == 'global').first()
    user1.profiles = [global_profile]
    # Establish link between profile 'LA' and user2
    user2 = Session.query(User).filter(User.username == 'user2').first()
    la_profile = Session.query(Profile).filter(Profile.code == 'LA').first()
    user2.profiles = [la_profile]
    
    return {'success': True, 'activities': a, 'stakeholders': s}

# 需要导入模块: from lmkp.models.meta import DBSession [as 别名]
# 或者: from lmkp.models.meta.DBSession import query [as 别名]
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 = Session.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 = Session.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."}

# 需要导入模块: from lmkp.models.meta import DBSession [as 别名]
# 或者: from lmkp.models.meta.DBSession import query [as 别名]
 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 = Session.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 = Session.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

# 需要导入模块: from lmkp.models.meta import DBSession [as 别名]
# 或者: from lmkp.models.meta.DBSession import query [as 别名]
def comment_delete(request):

    _ = request.translate

    """
    Delete an existing comment
    """
    ret = {'success': False}

    # check if id of comment is available
    if request.POST['id'] is None:
        ret['message'] = 'No comment id provided.'
        return ret

    # check if user has permissions to delete comments
    if not isinstance(has_permission(
            'moderate', request.context, request), ACLAllowed):
        ret['message'] = 'Permission denied.'
        return ret

    # query comment
    comment = Session.query(Comment).get(request.POST['id'])
    if comment is None:
        ret['message'] = 'Comment not found.'
        return ret

    Session.delete(comment)
    ret['message'] = _('Comment successfully deleted.')

    # if we've come this far, set success to 'True'
    ret['success'] = True

    return ret

# 需要导入模块: from lmkp.models.meta import DBSession [as 别名]
# 或者: from lmkp.models.meta.DBSession import query [as 别名]
    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(getTemplatePath(self.request, 'users/reset_password_form.mak'), {
                                      'came_from': came_from,
                                      'warning': msg
                                      }, self.request)

        new_password = user.set_new_password()

        body = render(getTemplatePath(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(getTemplatePath(self.request, 'users/reset_password_success.mak'), {}, self.request)