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)
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)
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
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
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)))
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
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
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
@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)
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
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
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
@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'",
