def list_patches(req, fields):
OrderBy = fields.get("OrderBy", None)
Category = int(fields.get("Category", 0))
CategoryExact = int(fields.get("CategoryExact", 0))
OrderDir = int(fields.get("OrderDir", 0))
if OrderDir < 0 or OrderDir > 2: OrderDir = 0
Offset = int(fields.get("Offset", 0))
Count = int(fields.get("Count", 50))
if Count <= 5: Count = 5
elif Count > InstDB.MaxListItems: Count = InstDB.MaxListItems
itemFields = list(PatchHelper.list_format)
# Category specified?
if Category:
CategoryIds = (str(Category), )
if not CategoryExact: # Exact category NOT specified?
InstDB.Cur.execute("SELECT CategoryName FROM Category"
" WHERE CategoryId=%s", Category) # Name
res = InstDB.Cur.fetchone()
if res: # Get list of sub categories (if any)
InstDB.Cur.execute(
"SELECT CategoryId FROM Category"
" WHERE LEFT(CategoryName, %s) = %s",
(len(res["CategoryName"]) + 1, res["CategoryName"] + ':'))
for row in InstDB.Cur.fetchall():
CategoryIds += (str(row["CategoryId"]), )
# Remove category from item fields
if "CategoryName" in itemFields:
itemFields.remove("CategoryName")
# Create SQL query
sel = PatchHelper.sql_select(itemFields)
# Make sure order by field is valid
if OrderBy and not PatchHelper.field_searchable(OrderBy): OrderBy = None
if not OrderBy: OrderBy = "FileName"
sel.orderby = PatchHelper.sql_order_field(OrderBy, OrderDir)
sel.limit = "%d,%d" % (Offset, Count)
sel.where.append("PatchInfo.State = 'Active'")
sel.calc_found_rows = True
if Category:
# Match categories (1st, 2nd or 3rd)
s = string.join(CategoryIds, ',')
sel.where.insert(
0, "(PatchInfo.CategoryId IN (%s)"
" || PatchInfo.CategoryId2 IN (%s)"
" || PatchInfo.CategoryId3 IN (%s))" % (s, s, s))
if not "PatchInfo.CategoryId" in sel.fields:
sel.fields += ("PatchInfo.CategoryId", )
InstDB.Cur.execute(sel.query())
rows = InstDB.Cur.fetchall()
# Get total number of matching items
InstDB.Cur.execute("SELECT FOUND_ROWS() AS Total")
row = InstDB.Cur.fetchone()
total = int(row["Total"])
# Re URL-ify the input fields
pager_urlstr = urlencode(
(("Action", "list"), ("Type", "Patch"), ("Category", Category),
("OrderBy", OrderBy), ("OrderDir", OrderDir), ("Count", Count)))
pager_urlstr = cgi.escape(pager_urlstr)
# Display the pager bar
InstDB.show_pager(req, pager_urlstr, Offset, Count, total)
# Create clickable titles that sort output
urlstr = urlencode((("Action", "list"), ("Type", "Patch"),
("Category", Category), ("Count", Count)))
urlstr = cgi.escape(urlstr)
sortList = []
for field in itemFields:
if PatchHelper.field_searchable(field):
sortList += ((field, PatchHelper.field_title(field)), )
else:
sortList += (PatchHelper.field_title(field), )
titles = sort_titles(sortList, urlstr, OrderBy, OrderDir, PatchHelper)
if rows:
table = InstDB.tabular(titles)
table.tableattr = 'width="100%"'
for row in rows:
req.write(table.addrow(PatchHelper.html_vals(itemFields, row)))
req.write(table.end())
else:
req.write("<b>No files match criteria</b>\n")
# Display the pager bar
InstDB.show_pager(req, pager_urlstr, Offset, Count, total)
def action(req, fields):
text = fields.get("Text", "")
offset = int(fields.get("Offset", 0))
count = int(fields.get("Count", 20))
exceeded = 0 # set to 1 if max_results is exceeded
try:
InstDB.Cur.execute("DROP TABLE _scores")
InstDB.Cur.execute("DROP TABLE _total_scores")
except MySQLdb.OperationalError:
pass
# Create temporary scoring table
InstDB.Cur.execute("""\
CREATE TEMPORARY TABLE _scores (
pid INT UNSIGNED NOT NULL,
rating INT UNSIGNED NOT NULL,
flag SMALLINT UNSIGNED NOT NULL,
INDEX (pid))""")
# Create temporary total scores table
InstDB.Cur.execute("""\
CREATE TEMPORARY TABLE _total_scores (
pid INT UNSIGNED NOT NULL,
rating INT UNSIGNED NOT NULL,
flags SMALLINT UNSIGNED NOT NULL,
INDEX (pid),
INDEX (rating))""")
# Insert scores for matches to Patch Name or FileName
rows = InstDB.Cur.execute(
"""\
INSERT INTO _scores
SELECT PatchId, %s, %s FROM PatchInfo
WHERE INSTR(PatchName, %s) || INSTR(FileName, %s)
LIMIT %s""", (patch_name_score, patch_name_score, text, text, max_results))
if rows == max_results: exceeded = 1
# Insert scores for matches to Patch properties (each property ++)
rows = InstDB.Cur.execute(
"""\
INSERT INTO _scores
SELECT ItemProps.ItemId, COUNT(*) * %s, %s FROM ItemProps, Items
WHERE ItemProps.ItemId=Items.ItemId && Items.ItemType='Patch'
&& INSTR(PropValue, %s) GROUP BY ItemProps.ItemId LIMIT %s""",
(patch_prop_score, patch_prop_score, text, max_results))
if rows == max_results: exceeded = 1
# Insert scores for matches to instrument names (each instrument ++)
rows = InstDB.Cur.execute(
"""\
INSERT INTO _scores
SELECT PatchId, COUNT(*) * %s, %s FROM InstInfo
WHERE INSTR(InstName, %s)
GROUP BY PatchId LIMIT %s""",
(inst_name_score, inst_name_score, text, max_results))
if rows == max_results: exceeded = 1
# Insert scores for matches to instrument properties (each property ++)
rows = InstDB.Cur.execute(
"""\
INSERT INTO _scores
SELECT InstInfo.PatchId, COUNT(*) * %s, %s FROM ItemProps, Items, InstInfo
WHERE ItemProps.ItemId=Items.ItemId && Items.ItemType='Inst'
&& INSTR(PropValue, %s) && ItemProps.ItemId=InstInfo.InstId
GROUP BY InstInfo.PatchId LIMIT %s""",
(inst_prop_score, inst_prop_score, text, max_results))
if rows == max_results: exceeded = 1
# Insert scores for matches to sample names (each sample ++)
rows = InstDB.Cur.execute(
"""\
INSERT INTO _scores
SELECT PatchId, COUNT(*) * %s, %s FROM SampleInfo
WHERE INSTR(SampleName, %s)
GROUP BY PatchId LIMIT %s""",
(sample_name_score, sample_name_score, text, max_results))
if rows == max_results: exceeded = 1
# Insert scores for matches to sample properties (each property ++)
rows = InstDB.Cur.execute(
"""\
INSERT INTO _scores
SELECT SampleInfo.PatchId, COUNT(*) * %s, %s FROM ItemProps, Items, SampleInfo
WHERE ItemProps.ItemId=Items.ItemId && Items.ItemType='Sample'
&& INSTR(PropValue, %s) && ItemProps.ItemId=SampleInfo.SampleId
GROUP BY SampleInfo.PatchId LIMIT %s""",
(sample_prop_score, sample_prop_score, text, max_results))
if rows == max_results: exceeded = 1
# Sum up all the ratings into temp total ratings table
InstDB.Cur.execute("""\
INSERT INTO _total_scores
SELECT pid, SUM(rating), BIT_OR(flag) FROM _scores, PatchInfo
WHERE pid = PatchInfo.PatchId && PatchInfo.State='Active'
GROUP BY pid""")
# Drop temp ratings table
InstDB.Cur.execute("DROP TABLE _scores")
# Get max rating
InstDB.Cur.execute("SELECT MAX(rating) AS max_rating,"
" COUNT(*) AS total_count FROM _total_scores")
row = InstDB.Cur.fetchone()
max_rating = row["max_rating"]
total_count = row["total_count"]
itemCells = (("FileNameLink", ), ("PatchName", ),
("DownloadLink", "PatchType", "FileSize"),
("Rating", "DateImported"), ("CategoryName", "LicenseName"),
("Version", "WebSiteLink", "Email"))
# convert sequence of sequence of strings to list of string fields
itemFields = []
for cell in itemCells:
for field in cell:
itemFields += (field, )
sel = PatchHelper.sql_select(itemFields) # Create SELECT query
sel.where.insert(0, "PatchInfo.State = 'Active'")
sel.where.insert(0, "_total_scores.pid=PatchInfo.PatchId")
sel.tables += ("_total_scores", )
sel.orderby = "rating DESC"
sel.fields += ("pid", "rating", "flags")
sel.limit = "%d, %d" % (offset, count)
InstDB.Cur.execute(sel.query())
req.write(InstDB.header("Search results"))
if total_count == 0:
req.write("<b>No matches to '%s'</b>\n" % (cgi.escape(text)))
req.write(InstDB.footer())
InstDB.Cur.execute("DROP TABLE _total_scores")
return
if exceeded: warn = " (results truncated due to excessive matches)"
else: warn = ""
if total_count == 1:
matchstr = "Found 1 match to '%s'%s\n" % (cgi.escape(text), warn)
else: matchstr = "Found %d matches to '%s'%s\n" \
% (total_count, cgi.escape (text), warn)
req.write("<b>" + matchstr + "</b>\n")
# Re URL-ify the input fields
urlstr = urlencode(
(("Action", "search"), ("Text", text), ("Count", count)))
urlstr = cgi.escape(urlstr)
# Display the result pager bar
InstDB.show_pager(req, urlstr, offset, count, total_count)
req.write("<p>\n")
# loop over matches
for row in InstDB.Cur.fetchall():
score = int(float(row["rating"]) / float(max_rating) * 100.0)
if score == 0: score = 1
scoreStr = "Score %d (%s/%s)" % (score, row["rating"], max_rating)
# box start for result item
box = InstDB.html_box()
box.fontsize = "+0"
box.tableattr = 'width="640"'
req.write(box.start(scoreStr))
tab = PatchHelper.cell_vals(itemCells, row)
split = InstDB.tabsplits(tab)
split.tableattr = 'width="100%"'
req.write(split.render())
# Create array of matching info to display (Type, Name, Value)
matches = ()
flags = int(row["flags"])
esctext = InstDB.sqlescape(text)
if flags & patch_prop_score: # Patch property matches?
query = """\
SELECT '%s' AS type, P.PropName AS name, I.PropValue AS value
FROM ItemProps AS I, Props AS P
WHERE I.ItemId=%d && INSTR(I.PropValue, '%s') && I.PropId=P.PropId""" \
% ("Patch Property", int (row["pid"]), esctext)
matches = query_matches(query, matches, text)
if flags & inst_name_score: # Instrument name matches?
query = "SELECT '%s' AS type, '%s' AS name, InstName AS value" \
" FROM InstInfo WHERE PatchId=%d && INSTR(InstName, '%s')" \
% ("Instrument", "Name", int (row["pid"]), esctext)
matches = query_matches(query, matches, text)
if flags & inst_prop_score: # Instrument property matches?
query = """\
SELECT '%s' AS type, Props.PropName AS name, ItemProps.PropValue AS value
FROM ItemProps, Props, Items, InstInfo
WHERE Items.ItemType='Inst' && INSTR(ItemProps.PropValue, '%s')
&& ItemProps.ItemId=InstInfo.InstId && InstInfo.PatchId=%d
&& ItemProps.ItemId=Items.ItemId && ItemProps.PropId=Props.PropId""" \
% ("Instrument Property", esctext, int (row["pid"]))
matches = query_matches(query, matches, text)
if flags & sample_name_score: # Sample name matches?
query = "SELECT '%s' AS type, '%s' AS name, SampleName AS value" \
" FROM SampleInfo WHERE PatchId=%d" \
" && INSTR(SampleName, '%s')" \
% ("Sample", "Name", int (row["pid"]), esctext)
matches = query_matches(query, matches, text)
if flags & sample_prop_score: # Sample property matches?
query = """\
SELECT '%s' AS type, Props.PropName AS name, ItemProps.PropValue AS value
FROM ItemProps, Props, Items, SampleInfo
WHERE Items.ItemType='Sample' && INSTR(ItemProps.PropValue, '%s')
&& ItemProps.ItemId=SampleInfo.SampleId && SampleInfo.PatchId=%d
&& ItemProps.ItemId=Items.ItemId && ItemProps.PropId=Props.PropId""" \
% ("Sample Property", esctext, int (row["pid"]))
matches = query_matches(query, matches, text)
if matches:
table = InstDB.tabular(("Item", "Field", "Value"))
table.tableattr = 'width="100%"'
table.cellspacing = 2
for m in matches:
req.write(table.addrow(m))
req.write(table.end())
req.write(box.end())
req.write("<p>\n")
req.write("<p>\n")
InstDB.show_pager(req, urlstr, offset, count, total_count)
req.write(InstDB.footer())
InstDB.Cur.execute("DROP TABLE _total_scores")
def list_samples(req, fields):
OrderBy = fields.get("OrderBy", None)
OrderDir = int(fields.get("OrderDir", 0))
if OrderDir < 0 or OrderDir > 2: OrderDir = 0
Offset = int(fields.get("Offset", 0))
Count = int(fields.get("Count", 50))
PatchId = int(fields.get("PatchId", 0))
if Count <= 5: Count = 5
elif Count > InstDB.MaxListItems: Count = InstDB.MaxListItems
InstDB.Cur.execute("SELECT State, UserId FROM PatchInfo WHERE PatchId=%s",
PatchId)
row = InstDB.Cur.fetchone()
if not row:
InstDB.error(req, "Specified patch file not found")
return
# Only allow list if patch is active, user is admin or user owns imported file
if row["State"] != "Active":
if not InstDB.User or ("Admin" not in InstDB.User["Flags"] and \
(row["State"] != 'Imported' or InstDB.User["UserId"] != row["UserId"])):
InstDB.error(req, "Permission denied to specified patch file")
return
itemFields = SampleHelper.list_format
extraFields = ("State", )
if PatchId: extraFields += ("PatchId", )
# Create SQL query
sel = SampleHelper.sql_select(itemFields + extraFields)
# Make sure order by field is valid
if OrderBy and not SampleHelper.field_searchable(OrderBy): OrderBy = None
if not OrderBy: OrderBy = "SampleName"
sel.orderby = SampleHelper.sql_order_field(OrderBy, OrderDir)
sel.limit = "%d,%d" % (Offset, Count)
if PatchId: sel.where.insert(0, "PatchInfo.PatchId=%d" % PatchId)
sel.calc_found_rows = True
InstDB.Cur.execute(sel.query())
rows = InstDB.Cur.fetchall()
# Get total number of matching items
InstDB.Cur.execute("SELECT FOUND_ROWS() AS Total")
row = InstDB.Cur.fetchone()
total = int(row["Total"])
# Re URL-ify the input fields
pager_urlstr = urlencode(
(("Action", "list"), ("Type", "Sample"), ("OrderBy", OrderBy),
("OrderDir", OrderDir), ("Count", Count), ("PatchId", PatchId)))
pager_urlstr = cgi.escape(pager_urlstr)
# Display the pager bar
InstDB.show_pager(req, pager_urlstr, Offset, Count, total)
# Create clickable titles that sort output
urlstr = urlencode((("Action", "list"), ("Type", "Sample"),
("Count", Count), ("PatchId", PatchId)))
urlstr = cgi.escape(urlstr)
sortList = []
for field in itemFields:
if SampleHelper.field_searchable(field):
sortList += ((field, SampleHelper.field_title(field)), )
else:
sortList += (SampleHelper.field_title(field), )
titles = sort_titles(sortList, urlstr, OrderBy, OrderDir, SampleHelper)
table = InstDB.tabular(titles)
table.tableattr = 'width="100%"'
for row in rows:
req.write(table.addrow(SampleHelper.html_vals(itemFields, row)))
req.write(table.end())
# Display the pager bar
InstDB.show_pager(req, pager_urlstr, Offset, Count, total)