def delete_range(env, start, end):
"""
Deletes range of records and attributes of them from DB.
"""
if start == '' or end == '':
return False
try:
start = int(start)
end = int(end)
except:
return False
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute("SELECT id FROM downloader_downloaded "\
"WHERE timestamp>=%s AND timestamp<=%s", (start, end))
ids = []
for (id, ) in cursor:
ids.append(id)
for id in ids:
cursor.execute(
"DELETE FROM downloader_downloaded_attributes "
"WHERE downloaded=%s", (id, ))
cursor.execute("DELETE FROM downloader_downloaded WHERE " \
" timestamp>=%s AND timestamp<=%s", (start, end))
return db.commit()
def delete_range(env, start, end):
"""
Deletes range of records and attributes of them from DB.
"""
if start == '' or end == '':
return False
try:
start = int(start)
end = int(end)
except:
return False
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute("SELECT id FROM downloader_downloaded "\
"WHERE timestamp>=%s AND timestamp<=%s", (start, end))
ids = []
for (id,) in cursor:
ids.append(id)
for id in ids:
cursor.execute("DELETE FROM downloader_downloaded_attributes "
"WHERE downloaded=%s", (id,))
cursor.execute("DELETE FROM downloader_downloaded WHERE " \
" timestamp>=%s AND timestamp<=%s", (start, end))
return db.commit()
def get_files(env):
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute("SELECT id FROM downloader_file "
"WHERE deleted IS NULL "
"ORDER BY name, timestamp")
files = []
for (id, ) in cursor:
files.append(File(env, id, db=db))
return files
def get_files(env):
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute("SELECT id FROM downloader_file "
"WHERE deleted IS NULL "
"ORDER BY name, timestamp")
files = []
for (id,) in cursor:
files.append(File(env, id, db=db))
return files
def fetch_downloads_list(env, req=None, sort='timestamp', desc=False, \
per_page=None, page=1, get_range=False, \
count=False, \
filter=None, filter_id=None, range=None):
"""
Fetch list of downloads in needed sort and with needed offset and limit.
"""
# Count offset
if per_page == None:
offset = 0
q_limit = ' '
else:
offset = (page - 1) * per_page
q_limit = ' LIMIT %d OFFSET %d ' % (per_page, offset)
if desc:
desc = ' DESC'
else:
desc = ''
db = env.get_db_cnx()
cursor = db.cursor()
# Get count of all records
cursor.execute("SELECT count(id) FROM downloader_downloaded")
row = cursor.fetchone()
rec_count = row[0]
# Filter
if filter_id:
filter_id = int(filter_id)
else:
filter_id = 0
if filter == 'category':
q_fil_join = " LEFT OUTER JOIN downloader_file ON " + \
" downloader_file.id=c.file " +\
" LEFT OUTER JOIN downloader_release ON " + \
" downloader_release.id=downloader_file.release "
q_fil_where = ' AND category=%d ' % (filter_id, )
elif filter == 'release':
q_fil_join = " LEFT OUTER JOIN downloader_file ON " + \
" downloader_file.id=c.file "
q_fil_where = ' AND downloader_file.release=%d ' % (filter_id, )
elif filter == 'file':
q_fil_join = ' '
q_fil_where = ' AND c.file=%d ' % (filter_id, )
else:
q_fil_join = ' '
q_fil_where = ' '
# Range
if isinstance(range, tuple) and len(range) == 2:
min_val, max_val = range
q_rng_where = (" AND c.timestamp>= %d " +
" AND c.timestamp<= %d ") % \
(min_val, max_val)
else:
q_rng_where = ' '
# Fetech sorted piece of data
q_attr_from = ''
q_attr_on = ''
q_attr_where = ' c.id > %s '
q_attr_sort = 0
if sort == 'timestamp':
sort = 'c.timestamp'
elif sort == 'file_name':
sort = 'c.name'
else:
q_attr_from = " LEFT OUTER JOIN " \
" downloader_downloaded_attributes AS a "
q_attr_on = " ON a.downloaded=c.id "
q_attr_where = " a.name=%s " \
"OR a.name='' OR a.name IS NULL "
q_attr_sort = sort
sort = 'a.value'
# What to fetch
if get_range:
q_get_rng_select = ' min(c.timestamp), max(c.timestamp) '
elif count:
q_get_rng_select = ' count(c.id) '
else:
q_get_rng_select = ' c.id '
"""
env.log.info(
"SELECT %s "
"FROM (downloader_downloaded AS d JOIN downloader_file AS f ON " \
" f.id=d.file) AS c %s %s %s "
"WHERE %s %s %s "
" ORDER BY %s %s" %
(q_get_rng_select, q_attr_from, q_attr_on, q_fil_join, \
q_attr_where, q_fil_where, q_rng_where, sort + desc, q_limit) % \
(q_attr_sort,)
)
"""
cursor.execute(
"SELECT %s "
"FROM (downloader_downloaded AS d JOIN downloader_file AS f ON " \
" f.id=d.file) AS c %s %s %s "
"WHERE %s AND c.deleted IS NULL %s %s "
" ORDER BY %s %s" %
(q_get_rng_select, q_attr_from, q_attr_on, q_fil_join, \
q_attr_where, q_fil_where, q_rng_where, sort + desc, q_limit),
(q_attr_sort,)
)
if get_range:
row = cursor.fetchone()
return (row[0], row[1])
elif count:
row = cursor.fetchone()
return row[0]
else:
dwn_list = []
for row in cursor:
dwn_list.append(DownloadData(env, req, id=row[0]))
return (rec_count, dwn_list)
def fetch_downloads_list(env, req=None, sort='timestamp', desc=False, \
per_page=None, page=1, get_range=False, \
count=False, \
filter=None, filter_id=None, range=None):
"""
Fetch list of downloads in needed sort and with needed offset and limit.
"""
# Count offset
if per_page == None:
offset = 0
q_limit = ' '
else:
offset = (page - 1) * per_page
q_limit = ' LIMIT %d OFFSET %d ' % (per_page, offset)
if desc:
desc = ' DESC'
else:
desc = ''
db = env.get_db_cnx()
cursor = db.cursor()
# Get count of all records
cursor.execute("SELECT count(id) FROM downloader_downloaded")
row = cursor.fetchone()
rec_count = row[0]
# Filter
if filter_id:
filter_id = int(filter_id)
else:
filter_id = 0
if filter == 'category':
q_fil_join = " LEFT OUTER JOIN downloader_file ON " + \
" downloader_file.id=c.file " +\
" LEFT OUTER JOIN downloader_release ON " + \
" downloader_release.id=downloader_file.release "
q_fil_where = ' AND category=%d ' % (filter_id,)
elif filter == 'release':
q_fil_join = " LEFT OUTER JOIN downloader_file ON " + \
" downloader_file.id=c.file "
q_fil_where = ' AND downloader_file.release=%d ' % (filter_id,)
elif filter == 'file':
q_fil_join = ' '
q_fil_where = ' AND c.file=%d ' % (filter_id,)
else:
q_fil_join = ' '
q_fil_where = ' '
# Range
if isinstance(range, tuple) and len(range) == 2:
min_val, max_val = range
q_rng_where = (" AND c.timestamp>= %d " +
" AND c.timestamp<= %d ") % \
(min_val, max_val)
else:
q_rng_where = ' '
# Fetech sorted piece of data
q_attr_from = ''
q_attr_on = ''
q_attr_where = ' c.id > %s '
q_attr_sort = 0
if sort == 'timestamp':
sort = 'c.timestamp'
elif sort == 'file_name':
sort = 'c.name'
else:
q_attr_from = " LEFT OUTER JOIN " \
" downloader_downloaded_attributes AS a "
q_attr_on = " ON a.downloaded=c.id "
q_attr_where = " a.name=%s " \
"OR a.name='' OR a.name IS NULL "
q_attr_sort = sort
sort = 'a.value'
# What to fetch
if get_range:
q_get_rng_select = ' min(c.timestamp), max(c.timestamp) '
elif count:
q_get_rng_select = ' count(c.id) '
else:
q_get_rng_select = ' c.id '
"""
env.log.info(
"SELECT %s "
"FROM (downloader_downloaded AS d JOIN downloader_file AS f ON " \
" f.id=d.file) AS c %s %s %s "
"WHERE %s %s %s "
" ORDER BY %s %s" %
(q_get_rng_select, q_attr_from, q_attr_on, q_fil_join, \
q_attr_where, q_fil_where, q_rng_where, sort + desc, q_limit) % \
(q_attr_sort,)
)
"""
cursor.execute(
"SELECT %s "
"FROM (downloader_downloaded AS d JOIN downloader_file AS f ON " \
" f.id=d.file) AS c %s %s %s "
"WHERE %s AND c.deleted IS NULL %s %s "
" ORDER BY %s %s" %
(q_get_rng_select, q_attr_from, q_attr_on, q_fil_join, \
q_attr_where, q_fil_where, q_rng_where, sort + desc, q_limit),
(q_attr_sort,)
)
if get_range:
row = cursor.fetchone()
return (row[0], row[1])
elif count:
row = cursor.fetchone()
return row[0]
else:
dwn_list = []
for row in cursor:
dwn_list.append(DownloadData(env, req, id=row[0]))
return (rec_count, dwn_list)