コード例 #1
0
    def _format_query(self, q):
        """
		Formats query, handling search conjunctions and quotes

		@param q: the query
		@type q: String

		@return: groups
		@rtype: List
		"""
        q = sql_escape(q).replace("!", "\!")
        ands = q.lower().replace('&', '').split(" and ")
        groups = []
        for a in ands:
            quotes = self.re_quoted.findall(a)
            a = a.replace('"', '')
            sub_groups = []
            for quote in quotes:
                a = a.replace(quote, '')
                quote_words = quote.split()
                sub_groups.append("(%s)" % '&'.join(quote_words))

            sub_groups += a.split()
            groups.append('|'.join(sub_groups))
        return '&'.join(groups)
コード例 #2
0
ファイル: Globber.py プロジェクト: kkszysiu/zoto-server
	def _format_query(self, q):
		"""
		Formats query, handling search conjunctions and quotes

		@param q: the query
		@type q: String

		@return: groups
		@rtype: List
		"""
		q = sql_escape(q).replace("!", "\!")
		ands = q.lower().replace('&', '').split(" and ")
		groups = []
		for a in ands:
			quotes = self.re_quoted.findall(a)
			a = a.replace('"','')
			sub_groups = []
			for quote in quotes:
				a = a.replace(quote, '')
				quote_words = quote.split()
				sub_groups.append("(%s)" % '&'.join(quote_words))
			
			sub_groups += a.split()
			groups.append('|'.join(sub_groups))
		return '&'.join(groups)
コード例 #3
0
		def create(result):
			if result[1] == True:
				return (-1, "Album [%s] already exists" % meta_info['title'])

			query_args = {
				'owner_userid': owner_userid,
				'template_options': cPickle.dumps({})
			}

			##
			## Build the fields/values
			##
			fields = ['owner_userid', 'serialized_template_options']
			values = ["%(owner_userid)s", "%(template_options)s"]
			for key, value in meta_info.items():
				if key not in self.valid_album_attrs:
					return (-1, "Invalid attribute: %s" % key)
				fields.append(key)
				values.append("%%(%s)s" % key)
				query_args[key] = utils.sql_escape(value)

			@stack
			def insert_txn(txn, field_list, value_list, args):
				##
				## Insert the new record
				##
				txn.execute("""
					INSERT INTO
						user_albums (
							%s
						) VALUES (
							%s
						)
					""" % (',\n'.join(field_list), ",\n".join(value_list)), args)
				##
				## Get the new ID
				##
				txn.execute("""
					SELECT CURRVAL('user_albums_album_id_seq') AS album_id
				""")
				args['album_id'] = txn.fetchone()['album_id']

				##
				## Add the permission record
				##
				txn.execute("""
					INSERT INTO
						user_album_permissions (
							album_id
						) VALUES (
							%(album_id)s
						)
					""", args)
				return args['album_id'] 


			d2 = self.app.db.runInteraction(insert_txn, fields, values, query_args)
			d2.addCallback(lambda _: (0, _))
			return d2
コード例 #4
0
	def extract_user_info(self, username, doc):
		"""
		Extracts all relevant user info from the .xml meta file.

		@param username: Username being processed
		@type username: String

		@param doc: dom.xml.Document object currently opened
		@type doc: dom.xml.Document
		"""
		account = doc.documentElement
		user_info = {}
		user_info['username'] = account.getAttribute('username')
		account_type = int(account.getAttribute('type'))
		if account_type == 100 or account_type == 110:
			user_info['account_type_id'] = 1
		elif account_type == 200 or account_type == 220 or account_type == 210:
			user_info['account_type_id'] = 2
		elif account_type == 300:
			user_info['account_type_id'] = 50
		else:
			user_info['account_type_id'] = 0
		user_info['account_status_id'] = int(account.getAttribute('status')) or 100
		user_info['password'] = self._get_text_node(account, 'password')
		user_info['bio'] = sql_escape(self._get_text_node(account, 'bio'))
		user_info['birthday'] = self._format_date(self._get_text_node(account, 'birthday'))
		user_info['date_created'] = self._format_date(self._get_text_node(account, 'created'))
		user_info['email'] = self._get_text_node(account, 'email')
		user_info['email_key'] = self._get_text_node(account, 'email_key')
		gender = self._get_text_node(account, 'gender').upper()
		if gender not in ("M", "F"):
			gender = "U"
		user_info['gender'] = gender
		user_info['last_login'] = self._format_date(self._get_text_node(account, 'last_login'))
		user_info['notification_preference'] = self._get_text_node(account, 'notification_preference')
		user_info['successful_invites'] = int(self._get_text_node(account, 'successful_invites'))
		user_info['partner_id'] = int(self._get_text_node(account, 'partner_id'))
		user_info['flags'] = int(self._get_text_node(account, 'flags'))
		user_info['extra_storage'] = int(self._get_text_node(account, 'extra_storage'))
		user_info['expires'] = self._format_date(self._get_text_node(account, 'expires') or "")
		if user_info['expires'] < datetime.datetime.now():
			user_info['expires'] = datetime.datetime.now() + datetime.timedelta(days=365)
	
		location = account.getElementsByTagName('location')[0]
		user_info['country'] = self._get_text_node(location, 'country')
		user_info['zip'] = self._get_text_node(location, 'zip')
	
		name = account.getElementsByTagName('name')[0]
		user_info['first_name'] = name.getAttribute('first')
		user_info['last_name'] = name.getAttribute('last')

		avatar = account.getElementsByTagName('avatar')[0]
		user_info['avatar_id'] = avatar.getAttribute('id')
		return user_info
コード例 #5
0
ファイル: Images.py プロジェクト: kkszysiu/zoto-server
	def set_attr(self, owner_userid, image_id, key, value):
		"""
		Sets an attribute of an image.
		
		@param owner_username: Username
		@type owner_username: String
		
		@param media_id: Image ID
		@type media_id: String
		
		@param key: Field to set. One of ('title', 'description', 'date', 'camera_make', 
						'camera_model', 'fstop', 'exposure_time', 'focal_length', 
						'iso_speed', 'rotate_bit', 'flash_fired', 'lat', 'lng', 'alt')
		@type key: String
		
		@param value: Value to set field to.
		@type: String

		@return: Nothing
		@rtype: Nothing
		"""
			
		owner_userid = validation.cast_integer(owner_userid, 'owner_userid')
		image_id = validation.cast_integer(image_id, 'image_id')
		validation.required(key, 'key')

		validation.oneof(key, self.attr_fields.keys(), 'key')

		if key == 'date':
			validation.isodatetime(value, 'date')
		if key == 'lat' or key == 'lng':
			value = float(value)
		if key == 'title':
			value = utils.check_n_chop(value, 30)

		self.log.debug("setting image [%s] [%s]=>[%s]" % (image_id, key, value))
		return self.app.db.runOperation("""
				select zoto_image_set_attr(%s, %s, %s, %s)
				""", (owner_userid, image_id, key, utils.sql_escape(value)))
コード例 #6
0
        def create(result):
            if result[1] == True:
                return (-1, "Set [%s] already exists" % meta_info['title'])

            ##
            ## Build the fields/values
            ##
            fields = ['owner_userid']
            values = ["%(owner_userid)s"]
            query_args = {'owner_userid': owner_userid}
            for key, value in meta_info.items():
                if key not in self.valid_set_attrs:
                    return (-1, "Invalid attribute: %s" % key)
                fields.append(key)
                values.append("%%(%s)s" % key)
                query_args[key] = utils.sql_escape(value)

            @stack
            def insert_txn(txn, field_list, value_list, info):
                txn.execute(
                    """
					INSERT INTO
						user_album_sets (
							%s
						) VALUES (
							%s
						)
					""" % (", ".join(field_list), ", ".join(value_list)), info)
                txn.execute("""
					SELECT CURRVAL('user_album_sets_set_id_seq') AS set_id
				""")
                id = txn.fetchone()['set_id']
                return (0, id)

            d2 = self.app.db.runInteraction(insert_txn, fields, values,
                                            query_args)
            d2.addErrback(lambda _: (-1, _.getErrorMessage()))
            return d2
コード例 #7
0
		def create(result):
			if result[1] == True:
				return (-1, "Set [%s] already exists" % meta_info['title'])

			##
			## Build the fields/values
			##
			fields = ['owner_userid']
			values = ["%(owner_userid)s"]
			query_args = {'owner_userid': owner_userid}
			for key, value in meta_info.items():
				if key not in self.valid_set_attrs:
					return (-1, "Invalid attribute: %s" % key)
				fields.append(key)
				values.append("%%(%s)s" % key)
				query_args[key] = utils.sql_escape(value)

			@stack
			def insert_txn(txn, field_list, value_list, info):
				txn.execute("""
					INSERT INTO
						user_album_sets (
							%s
						) VALUES (
							%s
						)
					""" % (", ".join(field_list), ", ".join(value_list)), info)
				txn.execute("""
					SELECT CURRVAL('user_album_sets_set_id_seq') AS set_id
				""")
				id = txn.fetchone()['set_id']
				return (0, id)

			d2 = self.app.db.runInteraction(insert_txn, fields, values, query_args)
			d2.addErrback(lambda _: (-1, _.getErrorMessage()))
			return d2
コード例 #8
0
    def extract_user_info(self, username, doc):
        """
		Extracts all relevant user info from the .xml meta file.

		@param username: Username being processed
		@type username: String

		@param doc: dom.xml.Document object currently opened
		@type doc: dom.xml.Document
		"""
        account = doc.documentElement
        user_info = {}
        user_info['username'] = account.getAttribute('username')
        account_type = int(account.getAttribute('type'))
        if account_type == 100 or account_type == 110:
            user_info['account_type_id'] = 1
        elif account_type == 200 or account_type == 220 or account_type == 210:
            user_info['account_type_id'] = 2
        elif account_type == 300:
            user_info['account_type_id'] = 50
        else:
            user_info['account_type_id'] = 0
        user_info['account_status_id'] = int(
            account.getAttribute('status')) or 100
        user_info['password'] = self._get_text_node(account, 'password')
        user_info['bio'] = sql_escape(self._get_text_node(account, 'bio'))
        user_info['birthday'] = self._format_date(
            self._get_text_node(account, 'birthday'))
        user_info['date_created'] = self._format_date(
            self._get_text_node(account, 'created'))
        user_info['email'] = self._get_text_node(account, 'email')
        user_info['email_key'] = self._get_text_node(account, 'email_key')
        gender = self._get_text_node(account, 'gender').upper()
        if gender not in ("M", "F"):
            gender = "U"
        user_info['gender'] = gender
        user_info['last_login'] = self._format_date(
            self._get_text_node(account, 'last_login'))
        user_info['notification_preference'] = self._get_text_node(
            account, 'notification_preference')
        user_info['successful_invites'] = int(
            self._get_text_node(account, 'successful_invites'))
        user_info['partner_id'] = int(
            self._get_text_node(account, 'partner_id'))
        user_info['flags'] = int(self._get_text_node(account, 'flags'))
        user_info['extra_storage'] = int(
            self._get_text_node(account, 'extra_storage'))
        user_info['expires'] = self._format_date(
            self._get_text_node(account, 'expires') or "")
        if user_info['expires'] < datetime.datetime.now():
            user_info['expires'] = datetime.datetime.now(
            ) + datetime.timedelta(days=365)

        location = account.getElementsByTagName('location')[0]
        user_info['country'] = self._get_text_node(location, 'country')
        user_info['zip'] = self._get_text_node(location, 'zip')

        name = account.getElementsByTagName('name')[0]
        user_info['first_name'] = name.getAttribute('first')
        user_info['last_name'] = name.getAttribute('last')

        avatar = account.getElementsByTagName('avatar')[0]
        user_info['avatar_id'] = avatar.getAttribute('id')
        return user_info
コード例 #9
0
		@param partial_tag_name: search term
		@type partial_tag_name: String

		@return: tag names
		@rtype: List
		"""
        try:
            owner_userid = validation.cast_integer(owner_userid,
                                                   'owner_userid')
        except errors.ValidationError, ex:
            return utils.return_deferred_error(ex.value)

        partial_tag_name = partial_tag_name.lower()
        query_args = {
            'owner_userid': owner_userid,
            'partial_tag': "%s%%%%" % utils.sql_escape(partial_tag_name)
        }
        return self.app.db.query(
            """
				SELECT
					distinct t1.tag_name
				FROM
					user_image_tags t1
					JOIN user_images t2 USING (image_id)
				WHERE
					t2.owner_userid = %(owner_userid)s AND
					t1.tag_name ilike %(partial_tag)s
				ORDER BY
					tag_name asc
				""", query_args)
コード例 #10
0
ファイル: Images.py プロジェクト: kkszysiu/zoto-server
		def assign_to_user(dimensions, exif_data, data):
			# blow off the exif crap for now, just get what we care about
			info = utils.filter_exif(exif_data)
			info['original_width'], info['original_height'] = dimensions
			if not info.get('datetime_taken') or info['datetime_taken'] == "0000-00-00 00:00:00":
				info['datetime_taken'] = None
			if info['rotate_bit'] > 0:
				info['rotate_bit'] = 'T'
			else:
				info['rotate_bit'] = 'F'
			if info['flash_fired'] % 2 == 0:
				info['flash_fired'] = 'F'
			else:
				info['flash_fired'] = 'T'
			info['media_id'] = media_id
			info['owner_userid'] = owner_userid
			info['title'] = utils.sql_escape(utils.check_n_chop(title, 30))
			info['filename'] = utils.sql_escape(filename)
			info['description'] = utils.sql_escape(description)
			info['size_b'] = len(data)
			info['img_source'] = source_id
			info['license'] = None
			info['gps_location'] = None
			info['gps_altitude'] = None
			info['status'] = 0

			@stack
			def user_assignment_txn(txn, info):
				try:
					query = """
					INSERT INTO
						user_images (
							media_id,
							owner_userid,
							title,
							filename,
							description,
							date_uploaded,
							date,
							status,
							camera_make,
							camera_model,
							fstop,
							exposure_time,
							focal_length,
							iso_speed,
							rotate_bit,
							flash_fired,
							original_width,
							original_height,
							size_b,
							img_source,
							license,
							gps_location,
							gps_altitude_m,
							total_views
						) VALUES (
							%(media_id)s,
							%(owner_userid)s,
							%(title)s,
							%(filename)s,
							%(description)s,
							DEFAULT,
							%(datetime_taken)s,
							%(status)s,
							%(camera_make)s,
							%(camera_model)s,
							%(fstop)s,
							%(exposure_time)s,
							%(focal_length)s,
							%(iso_speed)s,
							%(rotate_bit)s,
							%(flash_fired)s,
							%(original_width)s,
							%(original_height)s,
							%(size_b)s,
							%(img_source)s,
							%(license)s,
							%(gps_location)s,
							%(gps_altitude)s,
							0
						)
					"""
					txn.execute(query, info)
					txn.execute("SELECT currval('user_images_image_id_seq') AS new_id")
					info['image_id'] = txn.fetchone()['new_id']

					txn.execute("""
						UPDATE
							user_images
						SET
							fulltext_index = to_tsvector('english', title || ' ' || description)
						WHERE
							image_id = %(image_id)s
						""", info)

					txn.execute("""
						INSERT INTO
							user_image_permissions (
								image_id
							) VALUES (
								%(image_id)s
							)
						""", info)
					return info['image_id']

				except psycopg2.IntegrityError, ex:
					column = str(ex).split('"', 3)[1]
					if column == 'user_image_pkey':
						self.log.warning("pkey violation")
						return media_id
					else:
						self.log.warning("error in user_insert txn: %s" % ex)
						raise ex
				except Exception, ex:
					self.log.warning(ex)
					self.log.warning("super query nova:\n%s" % txn.query)
					raise ex
コード例 #11
0
    def get_images(self, owner_userid, auth_userid, glob, limit=0, offset=0):
        """
		Gets the images associated with criteria in glob.

		@param owner_username: username of image owner
		@type owner_username: String

		@param auth_username: username of user wanting access to the image
		@type auth_username: String

		@param glob: dictionary of search criteria
		@type glob: Dictionary

		@return: Images
		@rtype: Dictionary
		"""
        self.log.debug("glob looks like [%s]" % glob)

        # validate fields
        if owner_userid:
            owner_userid = validation.cast_integer(owner_userid,
                                                   'owner_userid')
        if auth_userid:
            auth_userid = validation.cast_integer(auth_userid, 'auth_userid')

        # get info on all of this user's existing categories
        """
		
		glob = {
			"is_tagged": True,
			"simple_search_query": "canon",
			"tag_intersection": ('car','foo'),
			"tag_union": ('car', 'foo'),
			"include_comment_total": True,
			}
		"""

        select = [
            't2.username AS owner_username',
            't1.owner_userid',
            'zoto_get_latest_id(t1.image_id) AS media_id',
            't1.image_id',
            'title',
            'description',
            'date',
            'date_uploaded',
            'camera_make',
            'camera_model',
            'filename',
            'size_B',
            'img_source',
            'license',
            'gps_location[1] as lat',
            'gps_location[0] as lng',
            'gps_altitude_m as alt',
            'current_width',
            'current_height',
            'original_width',
            'original_height',
            'fstop',
            'exposure_time',
            'focal_length',
            'iso_speed',
            'flash_fired',
            'zoto_get_elapsed(date) AS time_elapsed_taken',
            'zoto_get_elapsed(date_uploaded) AS time_elapsed_uploaded',
            """CASE
					WHEN exposure_time[2]>0	THEN exposure_time[1]/coalesce(exposure_time[2], 1.0)::float
					ELSE exposure_time[1]/1
				END AS calc_exposure_time""",
            """CASE
					WHEN fstop[2]>0	THEN round(fstop[1]/coalesce(fstop[2], 1.0)::numeric, 1)::float
					ELSE fstop[1]/1
				END AS calc_fstop""",
            """CASE
					WHEN focal_length[2]>0 THEN round(focal_length[1]/coalesce(focal_length[2], 1.0)::numeric, 1)::float
					ELSE focal_length[1]/1
				END AS calc_focal_length""",
        ]
        joins = [
            'user_images t1', 'JOIN users t2 ON (t1.owner_userid = t2.userid)'
        ]
        where = []

        single = False

        query_args = {'owner_userid': owner_userid, 'auth_userid': auth_userid}
        if owner_userid:
            where.append("t1.owner_userid = %s" % owner_userid)

        if not auth_userid:
            joins.append(
                'JOIN zoto_image_public_permissions_matview perm_table USING(image_id)'
            )
            select += [
                'can_tag AS user_can_tag', 'can_comment AS user_can_comment',
                'can_print AS user_can_print',
                'can_download AS user_can_download'
            ]
        elif owner_userid != auth_userid:
            joins.append(
                'JOIN zoto_image_permissions_matview perm_table1 USING (image_id)'
            )
            joins.append(
                "JOIN zoto_member_contact_groups_array_matview perm_table2 ON (perm_table2.member_userid = %(auth_userid)s)"
            )
            select += [
                "zoto_check_perm(perm_table1.tag_flag, perm_table1.tag_groups, perm_table2.groups) AS user_can_tag",
                "zoto_check_perm(perm_table1.comment_flag, perm_table1.comment_groups, perm_table2.groups) AS user_can_comment",
                "zoto_check_perm(perm_table1.print_flag, perm_table1.print_groups, perm_table2.groups) AS user_can_print",
                "zoto_check_perm(perm_table1.download_flag, perm_table1.download_groups, perm_table2.groups) AS user_can_download"
            ]
        else:
            select += [
                'true AS user_can_tag', 'true AS user_can_comment',
                'true AS user_can_print', 'true AS user_can_download'
            ]

        if glob.has_key(
                'include_comment_total') and glob['include_comment_total']:
            select.append(
                "(select count(*) from comments where image_id = t1.image_id and visible='T') as cnt_comments"
            )

        if glob.has_key('simple_search_query'):
            #--and user_images.fulltext_index @@ to_tsquery('default', '(monkey|dog)&replicate')
            # first see if the query is ok, is it multiple words?
            self.log.debug("simple query for [%s]" %
                           glob['simple_search_query'])
            if glob['simple_search_query']:
                query_args['ssq'] = self._format_query(
                    glob['simple_search_query'])
                sub_ors = [
                    "t1.fulltext_index @@ to_tsquery('default',%(ssq)s)",
                    "t1.ft_tag_index @@ to_tsquery('default',%(ssq)s)"
                ]
                where.append("(%s)" % ' or '.join(sub_ors))

        if glob.has_key("is_tagged"):
            if glob['is_tagged'] == 1:
                where.append("t1.ft_tag_index IS NOT NULL")
            elif glob['is_tagged'] == 0:
                where.append("t1.ft_tag_index IS NULL")

        if glob.has_key('tag_intersection'):
            count = 1
            for tag in glob['tag_intersection']:
                query_args['tag_int%s' % count] = sql_escape(tag)
                where.append(
                    "EXISTS (SELECT * FROM user_image_tags WHERE user_image_tags.tag_name = %%(tag_int%s)s AND image_id = t1.image_id)"
                    % count)
                count += 1

        if glob.has_key('tag_union'):
            ors = []
            count = 1
            for tag in glob['tag_union']:
                query_args['tag_union%s' % count] = sql_escape(tag)
                ors.append("tag_name = %%(tag_union%s)s" % count)
                count += 1
            if len(ors) > 0:
                joins.append("JOIN user_image_tags USING (image_id)")
                where.append("(%s)" % " or ".join(ors))
        if glob.has_key('tag_list') and glob['tag_list']:
            select.append(
                "(SELECT zoto_array_accum(tag_name) FROM user_image_tags WHERE image_id = t1.image_id) AS tag_list"
            )

        if glob.has_key('album_id') and glob['album_id'] != -1:
            query_args['album_id'] = glob['album_id']
            where.append(
                "zoto_user_can_view_album(t1.owner_userid, %(album_id)s, %(auth_userid)s)"
            )
            where.append("album_id = %(album_id)s")
            joins.append(
                "LEFT JOIN user_album_xref_user_images USING (image_id)")
        else:
            ## PERM TABLE WHERES
            if not auth_userid:
                where.append('perm_table.can_view = true')
            elif owner_userid != auth_userid:
                where.append(
                    'zoto_check_perm(perm_table1.view_flag, perm_table1.view_groups, perm_table2.groups) = true'
                )

        order_by = ""
        order_dir = ""

        order_by = glob.get("order_by", "t1.date_uploaded")
        order_dir = glob.get("order_dir", "desc")

        # Do we want to order by date_uploaded or date taken
        if order_by and order_dir:
            if glob.has_key('date_year') and glob['date_year']:
                query_args['date_year'] = glob['date_year']
                where.append("date_part('year', date) = %(date_year)s")
                if glob.has_key('date_month') and glob['date_month']:
                    query_args['date_month'] = glob['date_month']
                    where.append("date_part('month', date) = %(date_month)s")
                    if glob.has_key('date_day') and glob['date_day']:
                        query_args['date_day'] = glob['date_day']
                        where.append("date_part('day', date) = %(date_day)s")

        if glob.has_key('count_only') and glob['count_only']:
            order_by = ""
            order_dir = ""

        if glob.has_key('count_only') and glob['count_only']:
            if not owner_userid and len(where) == 1:
                return (0, 1000)
            #if glob.has_key('tag_union') and glob['tag_union']:
            #	joins.append('LEFT JOIN user_image_tags t2 using(media_id)')
            limit = 0
            offset = 0
            select = ['count(t1.media_id) as img_count']
            single = True
            group_by = []

            def count_images(result):
                self.log.debug("=" * 40)
                self.log.debug("img_count: %s" % result['img_count'])
                self.log.debug("=" * 40)
                return result['img_count']

        #else:
        #select.append('count(tag_name) as cnt_tags')
        #joins.append('LEFT JOIN user_image_tags t2 using(media_id)')

        # Build stuff
        order_by_sql = ""
        limit_sql = ""
        offset_sql = ""
        if order_by and order_dir:
            # forces null dates to be at the bottom of the list
            if order_by == "custom":
                order_by = "user_album_xref_user_images.media_idx"

            order_by_sql = "ORDER BY %s %s" % (order_by, order_dir)

        if limit:
            limit_sql = "LIMIT %d" % limit
        if offset:
            offset_sql = "OFFSET %d" % offset

        query = """
			SELECT
				%s
			FROM
				%s
			WHERE
				%s
			%s -- order by
			%s -- limit
			%s -- offset
		""" % (",\n".join(select), '\n'.join(joins), ' AND\n'.join(where),
         order_by_sql, limit_sql, offset_sql)

        self.log.debug("get_images():\n%s" % query)

        d = self.app.db.query(query, query_args, single)
        if glob.has_key('count_only') and glob['count_only']:
            d.addCallback(count_images)

        @stack
        def format_result(result):
            if result:
                return (0, result)
            else:
                return (0, [])

        d.addCallback(format_result)
        d.addErrback(lambda _: (-1, _.getErrorMessage()))
        return d
コード例 #12
0
ファイル: Globber.py プロジェクト: kkszysiu/zoto-server
	def get_images(self, owner_userid, auth_userid, glob, limit=0, offset=0):
		"""
		Gets the images associated with criteria in glob.

		@param owner_username: username of image owner
		@type owner_username: String

		@param auth_username: username of user wanting access to the image
		@type auth_username: String

		@param glob: dictionary of search criteria
		@type glob: Dictionary

		@return: Images
		@rtype: Dictionary
		"""
		self.log.debug("glob looks like [%s]" % glob)
		
		# validate fields
		if owner_userid:
			owner_userid = validation.cast_integer(owner_userid, 'owner_userid')
		if auth_userid:
			auth_userid = validation.cast_integer(auth_userid, 'auth_userid')

		# get info on all of this user's existing categories
		"""
		
		glob = {
			"is_tagged": True,
			"simple_search_query": "canon",
			"tag_intersection": ('car','foo'),
			"tag_union": ('car', 'foo'),
			"include_comment_total": True,
			}
		"""
		
		select = [
				't2.username AS owner_username',
				't1.owner_userid',
				'zoto_get_latest_id(t1.image_id) AS media_id',
				't1.image_id',
				'title',
				'description',
				'date',
				'date_uploaded',
				'camera_make',
				'camera_model',
				'filename',
				'size_B',
				'img_source',
				'license',
				'gps_location[1] as lat',
				'gps_location[0] as lng',
				'gps_altitude_m as alt',
				'current_width', 
				'current_height',
				'original_width',
				'original_height',
				'fstop',
				'exposure_time',
				'focal_length',
				'iso_speed',
				'flash_fired',
				'zoto_get_elapsed(date) AS time_elapsed_taken',
				'zoto_get_elapsed(date_uploaded) AS time_elapsed_uploaded',
				"""CASE
					WHEN exposure_time[2]>0	THEN exposure_time[1]/coalesce(exposure_time[2], 1.0)::float
					ELSE exposure_time[1]/1
				END AS calc_exposure_time""",
				"""CASE
					WHEN fstop[2]>0	THEN round(fstop[1]/coalesce(fstop[2], 1.0)::numeric, 1)::float
					ELSE fstop[1]/1
				END AS calc_fstop""",
				"""CASE
					WHEN focal_length[2]>0 THEN round(focal_length[1]/coalesce(focal_length[2], 1.0)::numeric, 1)::float
					ELSE focal_length[1]/1
				END AS calc_focal_length""",


		]
		joins = [
			'user_images t1',
			'JOIN users t2 ON (t1.owner_userid = t2.userid)'
			]
		where = [
			]

		single = False

		query_args = {
			'owner_userid': owner_userid,
			'auth_userid': auth_userid
		}
		if owner_userid:
			where.append("t1.owner_userid = %s" % owner_userid)


		if not auth_userid:
			joins.append('JOIN zoto_image_public_permissions_matview perm_table USING(image_id)')
			select += ['can_tag AS user_can_tag', 'can_comment AS user_can_comment', 'can_print AS user_can_print', 'can_download AS user_can_download']
		elif owner_userid != auth_userid:
			joins.append('JOIN zoto_image_permissions_matview perm_table1 USING (image_id)')
			joins.append("JOIN zoto_member_contact_groups_array_matview perm_table2 ON (perm_table2.member_userid = %(auth_userid)s)")
			select += [
				"zoto_check_perm(perm_table1.tag_flag, perm_table1.tag_groups, perm_table2.groups) AS user_can_tag",
				"zoto_check_perm(perm_table1.comment_flag, perm_table1.comment_groups, perm_table2.groups) AS user_can_comment",
				"zoto_check_perm(perm_table1.print_flag, perm_table1.print_groups, perm_table2.groups) AS user_can_print",
				"zoto_check_perm(perm_table1.download_flag, perm_table1.download_groups, perm_table2.groups) AS user_can_download"
			]
		else:
			select += ['true AS user_can_tag', 'true AS user_can_comment', 'true AS user_can_print', 'true AS user_can_download']

		if glob.has_key('include_comment_total') and glob['include_comment_total']:
			select.append("(select count(*) from comments where image_id = t1.image_id and visible='T') as cnt_comments")

		if glob.has_key('simple_search_query'):
			#--and user_images.fulltext_index @@ to_tsquery('default', '(monkey|dog)&replicate')
			# first see if the query is ok, is it multiple words?
			self.log.debug("simple query for [%s]" % glob['simple_search_query'])
			if glob['simple_search_query']:
				query_args['ssq'] = self._format_query(glob['simple_search_query'])
				sub_ors = [
					"t1.fulltext_index @@ to_tsquery('english',%(ssq)s)",
					"t1.ft_tag_index @@ to_tsquery('english',%(ssq)s)"
				]
				where.append("(%s)" % ' or '.join(sub_ors))

		if glob.has_key("is_tagged"):
			if glob['is_tagged'] == 1:
				where.append("t1.ft_tag_index IS NOT NULL")
			elif glob['is_tagged'] == 0:
				where.append("t1.ft_tag_index IS NULL")

		if glob.has_key('tag_intersection'):
			count = 1
			for tag in glob['tag_intersection']:
				query_args['tag_int%s' % count] = sql_escape(tag)
				where.append("EXISTS (SELECT * FROM user_image_tags WHERE user_image_tags.tag_name = %%(tag_int%s)s AND image_id = t1.image_id)" % count)
				count += 1

		if glob.has_key('tag_union'):
			ors = []
			count = 1
			for tag in glob['tag_union']:
				query_args['tag_union%s' % count] = sql_escape(tag)
				ors.append("tag_name = %%(tag_union%s)s" % count)
				count += 1
			if len(ors) > 0:
				joins.append("JOIN user_image_tags USING (image_id)")
				where.append("(%s)" % " or ".join(ors))
		if glob.has_key('tag_list') and glob['tag_list']:
			select.append("(SELECT zoto_array_accum(tag_name) FROM user_image_tags WHERE image_id = t1.image_id) AS tag_list")

		if glob.has_key('album_id') and glob['album_id'] != -1:
			query_args['album_id'] = glob['album_id']
			where.append("zoto_user_can_view_album(t1.owner_userid, %(album_id)s, %(auth_userid)s)")
			where.append("album_id = %(album_id)s")
			joins.append("LEFT JOIN user_album_xref_user_images USING (image_id)")
		else:
			## PERM TABLE WHERES
			if not auth_userid:
				where.append('perm_table.can_view = true')
			elif owner_userid != auth_userid:
				where.append('zoto_check_perm(perm_table1.view_flag, perm_table1.view_groups, perm_table2.groups) = true')


		order_by = ""
		order_dir = ""

		order_by = glob.get("order_by", "t1.date_uploaded")
		order_dir = glob.get("order_dir", "desc")

		
		# Do we want to order by date_uploaded or date taken
		if order_by and order_dir:
			if glob.has_key('date_year') and glob['date_year']:
				query_args['date_year'] = glob['date_year']
				where.append("date_part('year', date) = %(date_year)s")
				if glob.has_key('date_month') and glob['date_month']:
					query_args['date_month'] = glob['date_month']
					where.append("date_part('month', date) = %(date_month)s")
					if glob.has_key('date_day') and glob['date_day']:
						query_args['date_day'] = glob['date_day']
						where.append("date_part('day', date) = %(date_day)s")

		if glob.has_key('count_only') and glob['count_only']:
			order_by = ""
			order_dir = ""

		if glob.has_key('count_only') and glob['count_only']:
			if not owner_userid and len(where) == 1:
				return (0, 1000)
			#if glob.has_key('tag_union') and glob['tag_union']:
			#	joins.append('LEFT JOIN user_image_tags t2 using(media_id)')
			limit = 0
			offset = 0
			select = ['count(t1.media_id) as img_count']
			single = True
			group_by = []
			def count_images(result):
				self.log.debug("="*40)
				self.log.debug("img_count: %s" % result['img_count'])
				self.log.debug("="*40)
				return result['img_count']
		#else:
			#select.append('count(tag_name) as cnt_tags')
			#joins.append('LEFT JOIN user_image_tags t2 using(media_id)')

		# Build stuff
		order_by_sql = ""
		limit_sql = ""
		offset_sql = ""
		if order_by and order_dir:
			# forces null dates to be at the bottom of the list
			if order_by == "custom":
				order_by = "user_album_xref_user_images.media_idx"
			
			order_by_sql = "ORDER BY %s %s" % (order_by, order_dir)

		if limit:
			limit_sql = "LIMIT %d" % limit 
		if offset:
			offset_sql = "OFFSET %d" % offset 

		query = """
			SELECT
				%s
			FROM
				%s
			WHERE
				%s
			%s -- order by
			%s -- limit
			%s -- offset
		""" % (",\n".join(select), '\n'.join(joins), ' AND\n'.join(where), order_by_sql, limit_sql, offset_sql)

		self.log.debug("get_images():\n%s" % query)

		d = self.app.db.query(query, query_args, single)
		if glob.has_key('count_only') and glob['count_only']:
			d.addCallback(count_images)
		@stack
		def format_result(result):
			if result:
				return (0, result)
			else:
				return (0, [])
		d.addCallback(format_result)
		d.addErrback(lambda _: (-1, _.getErrorMessage()))
		return d
コード例 #13
0
		@param partial_tag_name: search term
		@type partial_tag_name: String

		@return: tag names
		@rtype: List
		"""
		try:
			owner_userid = validation.cast_integer(owner_userid, 'owner_userid')
		except errors.ValidationError, ex:
			return utils.return_deferred_error(ex.value)

		partial_tag_name = partial_tag_name.lower()
		query_args = {
			'owner_userid': owner_userid,
			'partial_tag': "%s%%%%" % utils.sql_escape(partial_tag_name)
		}
		return self.app.db.query("""
				SELECT
					distinct t1.tag_name
				FROM
					user_image_tags t1
					JOIN user_images t2 USING (image_id)
				WHERE
					t2.owner_userid = %(owner_userid)s AND
					t1.tag_name ilike %(partial_tag)s
				ORDER BY
					tag_name asc
				""", query_args)

	@zapi("Find all tags that start with 'partial_string'",