def action (req, fields):
req.write (InstDB.header ("Initialize database"))
if InstDB.Cur.execute ("SHOW tables") == 0:
for fname in os.listdir (InstDB.BasePath + os.sep + "sql"):
if fname[-4:] != ".sql": continue
req.write ("Executing SQL in '%s'<br>\n" % fname)
f = open (InstDB.BasePath + os.sep + "sql" + os.sep + fname, "r")
sql = f.read ()
# Split text file into individual queries
for query in string.split (sql, ';')[:-1]:
InstDB.Cur.execute (query)
# Import categories file
f = open (InstDB.BasePath + os.sep + "sql" + os.sep + "categories.txt", "r")
for line in f.readlines():
fields = string.split (line, "\t")
InstDB.Cur.execute ("INSERT INTO Category (CategoryName, CategoryDescr)"
" VALUES (%s, %s)", (fields[0], fields[1]))
# Import licenses file
f = open (InstDB.BasePath + os.sep + "sql" + os.sep + "licenses.txt", "r")
for line in f.readlines():
fields = string.split (line, "\t")
InstDB.Cur.execute ("INSERT INTO Licenses (LicenseName, LicenseDescr,"
" LicenseURL) VALUES (%s, %s, %s)",
(fields[0], fields[1], fields[2]))
else:
req.write ("Database already contains tables, nothing done")
req.write (InstDB.footer ())
def action(req, fields):
id = int(fields["ItemId"])
req.write(InstDB.header("Edit patch file details"))
InstDB.Cur.execute("SELECT UserId FROM PatchInfo WHERE PatchId=%s", id)
row = InstDB.Cur.fetchone()
if not row:
InstDB.error(req, "Patch file with ID '%d' not found" % id)
req.write(InstDB.footer())
return
# Do some security checks
if not InstDB.User:
InstDB.error(req, "Must login to edit patch information")
req.write(InstDB.footer())
return
if not "Admin" in InstDB.User["Flags"] \
and row["UserId"] != InstDB.User["UserId"]:
InstDB.error(req, "You don't have permission to edit that info")
req.write(InstDB.footer())
return
edit_patch_item(id, fields, req)
req.write(InstDB.footer())
def action(req, fields):
global MaxNewsItems
req.write(InstDB.header("Menu"))
box = InstDB.html_box()
req.write(box.start("About"))
req.write(InstDB.IncFile("about.inc"))
req.write(box.end())
req.write("<p>\n")
InstDB.Cur.execute(
"""\
SELECT DATE_FORMAT(N.Date,'%%a, %%Y-%%m-%%d %%H:%%i:%%s') AS Date,
N.UserId, U.UserName, N.Title, N.Content FROM News AS N, Users AS U
WHERE N.UserId=U.UserId ORDER BY N.Date DESC LIMIT %s""", MaxNewsItems)
for entry in InstDB.Cur.fetchall():
req.write("""\
<p>
<font class=newstitle>%s</font><br>
<table width="100%%" border=0 cellpadding=4 cellspacing=0><tr class=lightbg><td>
Submitted by <a href="patches.py?Action=User&UserId=%s">%s</a> on %s
<p>
%s
</td></tr></table>
""" % (entry["Title"], entry["UserId"], entry["UserName"], entry["Date"],
entry["Content"]))
req.write(InstDB.footer())
def action(req, fields):
req.write(InstDB.header("Software/Credits"))
box = InstDB.html_box()
req.write(box.start("Software/Credits"))
req.write(InstDB.IncFile("software.inc"))
req.write(box.end())
req.write(InstDB.footer())
def action(req, fields):
Type = fields.get("Type", "Patch")
if Type == "Inst": s = "Instruments"
elif Type == "Sample": s = "Samples"
else: s = "Patch Files"
req.write(InstDB.header("List " + s))
if Type == "Inst": list_insts(req, fields)
elif Type == "Sample": list_samples(req, fields)
else: list_patches(req, fields)
req.write(InstDB.footer())
def action (req, fields):
topic = fields.get ("Topic", None)
mini = fields.get ("Mini", '0')
if mini != '0': mini = True
else: mini = False
title = topic_titles.get (topic, "Help")
req.write (InstDB.header (title, mini))
if not topic_titles.get (topic, None):
InstDB.error (req, "Help topic not found!")
req.write (InstDB.footer (mini))
return
box = InstDB.html_box ()
req.write (box.start (title))
req.write (InstDB.IncFile ("%s.inc" % topic))
req.write (box.end ())
req.write (InstDB.footer (mini))
def action(req, fields):
id = int(fields.get("ItemId", 0))
req.write(InstDB.header("Patch file details"))
InstDB.Cur.execute("SELECT State,UserId FROM PatchInfo WHERE PatchId=%s",
id)
row = InstDB.Cur.fetchone()
if not row:
InstDB.error(req, "Patch file with ID '%d' not found" % id)
req.write(InstDB.footer())
return
# See if we are allowed to view this patch
if row["State"] != "Active" \
and (not InstDB.User or not (row["UserId"] == InstDB.User["UserId"]
or "Admin" in InstDB.User["Flags"])):
InstDB.error(req, "You don't have permission to view that info")
req.write(InstDB.footer())
return
show_patch_item(id, fields, req)
req.write(InstDB.footer())
def action(req, fields):
req.write(InstDB.header("Edit profile"))
if not InstDB.AssertLogin(req):
req.write(InstDB.footer())
return
itemCells = UsersHelper.edit_cell_format
itemFields = InstDB.cells_to_fields(itemCells)
errmsg = None
# Was Save button clicked? - Update field values
if fields.get("Save", None):
data = fields
password = fields.get("Password", "")
verify = fields.get("PassVerify", "")
email = fields.get("UserEmail", "")
if len(password) > 0 and len(password) < 5:
errmsg = "Passwords must be 5-32 characters, leave blank for no change"
if not errmsg and password != verify:
errmsg = "Password and Verify don't match"
if not errmsg:
n = string.find(email, "@")
n2 = string.find(email, " ")
if n <= 0 or n == len(email) - 1 or n2 >= 0:
errmsg = "A valid email address is required"
if not errmsg:
update = UsersHelper.sql_update(itemFields, fields)
if password: update.setnesc["Password"] = '******' \
% MySQLdb.escape_string (password + InstDB.FuKraXor)
update.where = ("UserId=%d" % InstDB.User["UserId"], )
# Use login if UserName not set
if not fields.get("UserName", None):
update.setnesc["UserName"] = "******"
if update.set.get("UserName"): del update.set["UserName"]
InstDB.Cur.execute(update.query())
req.write("<font size=+1>User profile updated</font>\n<p>\n")
else:
req.write('<font class="error">%s</font><p>\n' % errmsg)
else: # First page load?
sel = UsersHelper.sql_select(itemFields)
sel.where.insert(0, "UserId=%d" % InstDB.User["UserId"])
InstDB.Cur.execute(sel.query())
data = InstDB.Cur.fetchone()
tab = (("Password", "<input type=password name=Password>"),
("Verify", "<input type=password name=PassVerify>"))
tab += tuple(UsersHelper.form_cell_vals(itemCells, data))
tab += (("<input type=submit name=Save value=\"Save\">\n", ), )
# Display form
box = InstDB.html_box()
req.write(box.start("User Profile"))
req.write(
"<table border=0 cellpadding=8>\n<tr>\n<td valign=top bgcolor=#408ee6>\n"
)
req.write("<form action=\"patches.py?Action=profile\" method=POST>\n")
split = InstDB.tabsplits(tab)
req.write(split.render())
req.write("</form>\n")
req.write("</td>\n<td valign=top>\n")
req.write("<vr>\n</td>\n<td valign=top>\n")
req.write(InstDB.IncFile("profile.inc"))
req.write("</td>\n</tr>\n</table>\n")
req.write(box.end())
req.write(InstDB.footer())
def action(req, fields):
id = int(fields.get("PatchId", 0))
filetype = fields.get("Type", None)
if filetype != "zip": filetype = "CRAM"
itemFields = ("FileName", "PatchType", "ZipSize", "CramSize")
sel = PatchHelper.sql_select(itemFields)
sel.fields.append("DATEDIFF(NOW(), PatchInfo.DateImported) AS ImportDays"
) # Days since imported
sel.where.insert(0, "PatchInfo.State = 'Active'")
sel.where.insert(0, "PatchInfo.PatchId=%d" % id)
InstDB.Cur.execute(sel.query())
PatchRow = InstDB.Cur.fetchone()
if not PatchRow:
req.write(InstDB.header("Download Error"))
InstDB.error(req, "Instrument file with ID '%d' not found" % id)
req.write(InstDB.footer())
return
# For now, we just select a random mirror, unless imported less than 2 days ago, then we use default mirror
if (PatchRow["ImportDays"] > 2):
InstDB.Cur.execute(
"SELECT MirrorId, BaseURL FROM Mirrors"
" WHERE FIND_IN_SET('Active', Flags) && FIND_IN_SET(%s, Flags)"
" ORDER BY RAND() LIMIT 1", filetype)
else: # Just use first active mirror with file type available (usually Mirror 1, unless its inactive)
InstDB.Cur.execute(
"SELECT MirrorId, BaseURL FROM Mirrors WHERE"
" FIND_IN_SET('Active', Flags) && FIND_IN_SET(%s, Flags)"
" ORDER BY MirrorId LIMIT 1", filetype)
mirror = InstDB.Cur.fetchone()
if not mirror:
req.write(InstDB.header("Download Error"))
InstDB.error(req,
"No active mirror found for requested instrument file")
req.write(InstDB.footer())
return
# Update PatchInfo file download count for file type and set some vars
if filetype == "zip":
filesize = PatchRow["ZipSize"]
fileext = "zip"
InstDB.Cur.execute(
"UPDATE PatchInfo SET ZipClicks=ZipClicks+1"
" WHERE PatchId=%s", id)
else:
filesize = PatchRow["CramSize"]
fileext = "cram"
InstDB.Cur.execute(
"UPDATE PatchInfo SET CramClicks=CramClicks+1"
" WHERE PatchId=%s", id)
# Update Mirror download count and download size counters
InstDB.Cur.execute(
"UPDATE Mirrors SET DownloadClicks=DownloadClicks+1,"
"DownloadSize=DownloadSize+%s,DownloadTotal=DownloadTotal+%s"
" WHERE MirrorId=%s", (filesize, filesize, mirror["MirrorId"]))
# If user logged in, mark file as downloaded
if InstDB.User:
InstDB.Cur.execute(
"SELECT COUNT(*) AS count FROM FileMarks"
" WHERE UserId=%s && PatchId=%s", (InstDB.User['UserId'], id))
if InstDB.Cur.fetchone()["count"] == 0:
InstDB.Cur.execute(
"INSERT FileMarks (UserId, PatchId, MarkFlags)"
" VALUES(%s,%s,'Downloaded')", (InstDB.User['UserId'], id))
else:
InstDB.Cur.execute(
"UPDATE FileMarks SET MarkFlags=CONCAT(MarkFlags, \",Downloaded\"),"
"MarkTime=Null WHERE UserId=%s && PatchId=%s",
(InstDB.User['UserId'], id))
# Redirect to file to download
if req.proto_num >= 1001:
req.status = apache.HTTP_TEMPORARY_REDIRECT
else:
req.status = apache.HTTP_MOVED_TEMPORARILY
# FIXME - Does redirect Location: URL need to be escaped???
req.headers_out["Location"] = "%s/%s.%s.%s" \
% (mirror["BaseURL"], PatchRow["FileName"], PatchRow["PatchType"], fileext)
req.send_http_header()
def action (req, fields):
msg = fields.get ("Msg", None)
if msg == "Registered":
req.write (InstDB.header ("Email confirmation"))
req.write (InstDB.IncFile ("activated.inc"))
req.write (InstDB.footer ())
return
elif msg == "Reset":
req.write (InstDB.header ("Email confirmation"))
req.write ('<font class="title">Password has been reset and will be'
' emailed to you shortly.</font>\n')
req.write (InstDB.footer ())
return
login = fields.get ("Login", None)
hashkey = fields.get ("HashKey", None)
if not login or not hashkey:
req.write (InstDB.header ("Email confirmation"))
InstDB.error (req, "Invalid confirmation data")
req.write (InstDB.footer ())
return
InstDB.Cur.execute ("SELECT UserId FROM Users WHERE Login=%s", login)
row = InstDB.Cur.fetchone ()
if not row:
req.write (InstDB.header ("Email confirmation"))
InstDB.error (req, "Invalid confirmation data")
req.write (InstDB.footer ())
return
userid = row["UserId"]
InstDB.Cur.execute ("SELECT ConfirmAction FROM Confirm"
" WHERE UserId=%s && HashKey=%s", (userid, hashkey))
row = InstDB.Cur.fetchone ()
if not row:
req.write (InstDB.header ("Email confirmation"))
InstDB.error (req, "Email confirmation not found, perhaps it expired?")
req.write (InstDB.footer ())
return
if row["ConfirmAction"] == "Register":
InstDB.Cur.execute ("UPDATE Users SET Flags='' WHERE UserId=%s", userid)
if InstDB.EnableForums:
retval = phpbb.activate_user (login) # Activate the forum account
if retval != 0:
raise RuntimeWarning, "Failed to activate PHPBB user '%s' (retval=%d)" % (login, retval)
# Redirect to confirm page with Msg=Registered parameter
if req.proto_num >= 1001:
req.status = apache.HTTP_TEMPORARY_REDIRECT
else: req.status = apache.HTTP_MOVED_TEMPORARILY
req.headers_out["Location"] = "patches.py?Action=confirm&Msg=Registered"
req.send_http_header()
elif row["ConfirmAction"] == "ResetPass":
newpass = ""
for i in range (0, 8):
newpass += random.choice ("ABCDEFGHIJKLMNPQRSTUVWXYZ"
"abcdefghjkmnpqrstuvwxyz23456789")
InstDB.Cur.execute ("UPDATE Users SET Password=SHA1(%s)"
" WHERE UserId=%s", (newpass + InstDB.FuKraXor, userid))
f = open (InstDB.IncludePath + os.sep + "newpass.msg")
email = f.read () % {
"Login" : login,
"Password" : newpass }
# Queue email task
InstDB.Cur.execute ("INSERT INTO Queue (Type, Status, UserId, Content)"
" VALUES ('Email', 'Queued', %s, %s)",
(userid, email))
# Redirect to confirm page with Msg=Reset parameter
if req.proto_num >= 1001:
req.status = apache.HTTP_TEMPORARY_REDIRECT
else: req.status = apache.HTTP_MOVED_TEMPORARILY
req.headers_out["Location"] = "patches.py?Action=confirm&Msg=Reset"
req.send_http_header()
# Remove the confirmation, we done with it
InstDB.Cur.execute ("DELETE FROM Confirm WHERE UserId=%s && HashKey=%s",
(userid, hashkey))
def action(req, fields):
req.write(InstDB.header("Registration"))
itemCells = UsersHelper.edit_cell_format
errmsg = None
recaptcha_error = None
# Was Register button clicked? - Save field values
if fields.get("Register", None):
data = fields
login = fields.get("Login", "")
password = fields.get("Password", "")
verify = fields.get("PassVerify", "")
email = fields.get("UserEmail", "")
captcha_challenge = fields.get("recaptcha_challenge_field", "")
captcha_response = fields.get("recaptcha_response_field", "")
if not login: errmsg = "Login name is required"
elif not verify_chars(login, InstDB.LoginAllowChars):
errmsg = "Only these characters are allowed in login: '******'" \
% InstDB.LoginAllowChars
elif login and InstDB.Cur.execute \
("SELECT 1 FROM Users WHERE Login=%s", login) == 1:
errmsg = "The login '%s' is already used" % (cgi.escape(login))
if not errmsg and len(password) < 5:
errmsg = "A password with 5-32 characters is required"
if not errmsg and password != verify:
errmsg = "Password and Verify don't match"
if not errmsg:
n = string.find(email, "@")
n2 = string.find(email, " ")
if n <= 0 or n == len(email) - 1 or n2 >= 0:
errmsg = "A valid email address is required"
if not errmsg and not verify_chars(email, InstDB.EmailAllowChars):
errmsg = "Only these characters are allowed in email address: '%s'" \
% InstDB.EmailAllowChars
if not errmsg and InstDB.Cur.execute \
("SELECT Login FROM Users WHERE UserEmail=%s", email) == 1:
existLogin = InstDB.Cur.fetchone()["Login"]
errmsg = "Login '%s' is already registered with that email address." \
% existLogin
if not errmsg:
resp = recaptcha_submit(captcha_challenge, captcha_response,
InstDB.ReCaptchaPrvKey,
req.connection.remote_ip)
if not resp.is_valid:
errmsg = "Are you human? reCAPTCHA response incorrect."
recaptcha_error = resp.error_code
if not errmsg:
itemFields = InstDB.cells_to_fields(
InstDB.delete_cells(itemCells, "UserName"))
insert = UsersHelper.sql_insert(itemFields, fields)
insert.set["Login"] = login
insert.set["Flags"] = "Confirm"
insert.setnesc["Password"] = '******' \
% MySQLdb.escape_string (password + InstDB.FuKraXor)
insert.setnesc["DateCreated"] = "NULL"
insert.set["LastLogin"] = 0
# Use login if UserName not set
username = fields.get("UserName", None)
if not username: username = login
insert.set["UserName"] = username
InstDB.Cur.execute(insert.query())
userid = InstDB.DB.insert_id()
confirm.new("Register", userid, login)
# Add new account for phpbb forums
if InstDB.EnableForums:
retval = phpbb.add_user(login, password, email)
if retval != 0:
raise RuntimeWarning, "Failed to add user '%s' to PHPBB forums (retval=%d)" % (
login, retval)
req.write('<font class="title">Registration successful, an email'
' will be sent shortly with directions on activating'
' your account.</font>\n')
req.write(InstDB.footer())
return
else:
InstDB.error(req, errmsg)
req.write("<p>\n")
else:
data = {"UserFlags": ('EmailNews', 'NotifyMessage', 'NotifyRatings')}
tab = (("Login", '<input type=text name=Login value="%s">' %
cgi.escape(data.get("Login", ""))),
("Password", "<input type=password name=Password>"),
("Verify", "<input type=password name=PassVerify>"))
tab += tuple(UsersHelper.form_cell_vals(itemCells, data))
tab += ((recaptcha_display(InstDB.ReCaptchaPubKey, False,
recaptcha_error), ), )
tab += (('<input type=submit name=Register value="Register">\n'
'<a href="patches.py?Action=help&Topic=privacy&Mini=1"'
' target="_blank">Privacy Policy</a>', ), )
# Display register form
box = InstDB.html_box()
req.write(box.start("User Registration"))
req.write("<table border=0 cellpadding=8>\n<tr>\n<td valign=top>\n")
req.write("<form action=\"patches.py?Action=register\" method=POST>\n")
split = InstDB.tabsplits(tab)
split.tableattr = 'bgcolor="#408ee6"'
split.cellspacing = 4
req.write(split.render())
req.write("</form>\n")
req.write("</td>\n<td valign=top>\n")
req.write(InstDB.IncFile("register.inc"))
req.write("</td>\n</tr>\n</table>\n")
req.write(box.end())
req.write(InstDB.footer())
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 action(req, fields):
if not InstDB.User or "Admin" not in InstDB.User["Flags"]:
req.write(InstDB.header("Admin only"))
InstDB.error(req,
"Administrator privileges required for this function")
req.write(InstDB.footer())
return
req.write(InstDB.header("Email news"))
subject = fields.get("Subject", None)
msg = fields.get("Message", None)
# Message submitted?
if subject and msg:
message = "Subject: " + subject + "\r\n\r\n" + msg
query = "SELECT UserId FROM Users WHERE !FIND_IN_SET('NoLogin', Flags)"
allUsers = fields.get("AllUsers", None)
if not allUsers:
query += " && FIND_IN_SET('EmailNews', UserFlags)"
InstDB.Cur.execute(query)
for row in InstDB.Cur.fetchall():
# Queue email task
InstDB.Cur.execute(
"INSERT INTO Queue (Type, Status, UserId, Content)"
" VALUES ('Email', 'Queued', %s, %s)",
(row["UserId"], message))
if allUsers: allstr = "ALL"
else: allstr = "EmailNews"
req.write('<font class="title">Sent message to %s users.</font>\n' %
allstr)
req.write(InstDB.footer())
return
box = InstDB.html_box()
req.write(box.start("Email news"))
req.write("""
<form action="patches.py?Action=emailnews" method=post>
<table border=0 cellpadding=8>
<tr>
<td><b>Subject:</b></td>
<td><input type=text name=Subject size=64></td>
</tr>
<tr>
<td><b>All users:</b></td>
<td><input type=checkbox name=AllUsers> Email <b>ALL</b> users?</td>
</tr>
<tr>
<td colspan=2>
<textarea name=Message rows=16 cols=80>
</textarea>
</td>
</tr>
<tr>
<td colspan=2>
<input type=submit name=Submit value=Submit>
</td>
</tr>
</table>
</form>
""")
req.write(box.end())
req.write(InstDB.footer())
def action(req, fields):
req.write(InstDB.header("Patch submission"))
if not InstDB.AssertLogin(req):
req.write(InstDB.footer())
return
req.write("""
Upload files to <a href="ftp://sounds.resonance.org/incoming/">ftp://sounds.resonance.org/incoming/</a>.
It is recommended that you read the <a href="patches.py?Action=help&Topic=submit">Content Submission Help</a>.
<p>
""")
# Check if any incoming files have been selected
Files = fields.getlist("Files")
for fname in Files:
(evil, fname) = os.path.split(fname) # Make sure no path of evil
if not os.path.isfile(InstDB.IncomingPath + os.sep + fname):
InstDB.error(req, "File not found '%s'" % cgi.escape(fname))
else:
# Create new import queue task (don't activate yet)
InstDB.Cur.execute(
"INSERT INTO Queue"
" (Type, Status, FileName, UserId)"
" VALUES ('Import', 'Queued', %s, %s)",
(fname, InstDB.User["UserId"]))
# Get list of files in incoming directory
file_list = os.listdir(InstDB.IncomingPath)
file_list.sort()
box = InstDB.html_box()
box.tableattr = 'width="100%"'
req.write(box.start("Incoming files"))
req.write("<form action=\"patches.py?Action=submit\" method=POST>\n")
if file_list:
table = InstDB.tabular(("Import", "File", "Size", "Date"))
table.tableattr = 'width="100%"'
for file in file_list:
check = "<input type=checkbox name=Files value=\"%s\">" \
% cgi.escape (file)
stats = os.stat(InstDB.IncomingPath + "/" + file)
req.write(
table.addrow((check, cgi.escape(file),
InstDB.pretty_size(stats.st_size),
time.ctime(stats.st_mtime))))
req.write(table.end())
else:
req.write("<b>No files in incoming directory</b><br>\n")
req.write(box.end())
req.write("<center><input type=submit name=FileSelect"
" value=\"Import and/or Refresh\"></center>\n")
req.write("</form>\n")
req.write("<p><p>\n")
# Get queued tasks
box = InstDB.html_box()
box.tableattr = 'width="100%"'
req.write(box.start("Your queued tasks"))
sel = SqlHelpers.Select()
sel.fields = ("QueueId", "Type", "Status", "UserId", "FileName", "Date")
sel.tables = ("Queue", )
sel.where = ("Type in ('Import','Activate')", )
sel.orderby = "QueueId"
InstDB.Cur.execute(sel.query())
table = InstDB.tabular(
("Position", "File Name", "Action", "Status", "Start Time"))
table.tableattr = 'width="100%"'
index = 0
any = False
for row in InstDB.Cur.fetchall():
if row["UserId"] == InstDB.User["UserId"]:
any = True
if row["Status"] != "Error":
ndxval = index
status = row["Status"]
else:
ndxval = "N/A"
status = '<font class="Error">' + row["Status"] + '</font>'
req.write(
table.addrow((ndxval, cgi.escape(row["FileName"]), row["Type"],
status, row["Date"])))
if row["Status"] != "Error":
index += 1
if any: req.write(table.end())
else: req.write("<b>No queued tasks</b><br>\n")
req.write(box.end())
req.write("<p><p>\n")
# Any activation form data submitted? Group fields by patch ID.
patchFields = {}
if fields.get("Activate", None):
for field in fields.list:
# Fields have "P<PatchId>_" prefix
match = re.match("P([0-9]{1,10})_(.+)", field.name)
if match:
patchId = match.group(1)
if not patchFields.get(patchId, None):
patchFields[patchId] = {}
patchFields[patchId][match.group(2)] = fields[field.name]
# Files pending activation
box = InstDB.html_box()
box.tableattr = 'width="100%"'
req.write(box.start("Files pending activation"))
InstDB.Cur.execute(
"SELECT PatchId, PatchType FROM PatchInfo"
" WHERE UserId=%s && State='Imported'", InstDB.User["UserId"])
pendRows = InstDB.Cur.fetchall()
if pendRows:
activateCells = PatchHelper.edit_cell_format
activateFields = InstDB.cells_to_fields(activateCells)
displayedOne = False
for pendRow in pendRows:
pFields = patchFields.get(str(pendRow["PatchId"]), None)
patchId = int(pendRow["PatchId"])
activate = pFields and pFields.get("Activate", None)
errorMsg = ""
if pFields: # Form data submitted for this patch?
pFields["FileName"] = pFields["FileName"].strip()
if not pFields["FileName"]:
errorMsg = "File name is required"
# Check if filename already used
if not errorMsg:
InstDB.Cur.execute(
"SELECT COUNT(*) AS count FROM PatchInfo"
" WHERE PatchId != %s && FileName=%s"
" && PatchType=%s",
(patchId, pFields["FileName"], pendRow["PatchType"]))
if int(InstDB.Cur.fetchone()["count"]) > 0:
errorMsg = "File name already in use"
if pFields:
update = PatchHelper.sql_update(activateFields, pFields)
update.set["FileName"] = pFields["FileName"]
update.where = ("PatchInfo.PatchId=%d" % patchId, )
if activate and not errorMsg: update.set["State"] = "Activating"
InstDB.Cur.execute(update.query())
props_update(patchId, pFields) # Update extra properties
# Queue activation task
if activate and not errorMsg:
InstDB.Cur.execute(
"INSERT INTO Queue"
" (Type, Status, UserId, ItemId, FileName)"
" VALUES ('Activate', 'Queued', %s, %s, %s)",
(InstDB.User["UserId"], patchId, "%s.%s" %
(pFields["FileName"], pendRow["PatchType"])))
if (not activate or errorMsg) and not displayedOne:
req.write(
"<form action=\"patches.py?Action=submit\" method=POST>\n")
displayedOne = True
if errorMsg:
req.write('<font class="error">%s</font><br>\n' %
cgi.escape(errorMsg))
if not activate or errorMsg:
sel = PatchHelper.sql_select(activateFields)
sel.fields.insert(0, "PatchInfo.PatchId")
sel.where.insert(0, "PatchInfo.PatchId=%d" % patchId)
InstDB.Cur.execute(sel.query())
row = InstDB.Cur.fetchone()
display_patch(req, row, pFields)
if displayedOne:
req.write("<input type=submit name=Activate" " value=\"Update\">\n")
req.write("</form>\n")
else:
req.write("<b>No files pending activation</b><br>\n")
req.write(box.end())
req.write(InstDB.footer())
def action(req, fields):
req.write(InstDB.header("Reset password"))
login = fields.get("Login", None)
email = fields.get("Email", None)
if login or email:
if login:
InstDB.Cur.execute(
"SELECT UserId, Login, UserEmail FROM Users"
" WHERE Login=%s", login)
else:
InstDB.Cur.execute(
"SELECT UserId, Login, UserEmail FROM Users"
" WHERE UserEmail=%s", email)
row = InstDB.Cur.fetchone()
if not row:
InstDB.error(req, "Sorry, no account matches the given input.")
req.write(InstDB.footer())
return
userid = row["UserId"]
emailaddr = row["UserEmail"]
login = row["Login"]
InstDB.Cur.execute(
"SELECT COUNT(*) AS count FROM Confirm"
" WHERE UserId=%s && ConfirmAction='ResetPass'", userid)
if InstDB.Cur.fetchone()["count"]:
InstDB.error(req, "Password reset confirmation already sent.")
req.write(
'<p>\nIf you do not receive it soon, feel free to'
' email the <a href="mailto:[email protected]">admin</a>'
', or wait a day for this confirmation to expire.')
req.write(InstDB.footer())
return
confirm.new('ResetPass', userid, login)
req.write('<font class="title">An email will be sent soon'
' to the email address you registered with, containing'
' directions on how to reset your password.</font>\n')
req.write(InstDB.footer())
return
box = InstDB.html_box()
req.write(box.start("Reset password"))
req.write("""
<form action="patches.py?Action=resetpass" method=post>
<table border=0 cellpadding=8>
<tr>
<td align="center" bgcolor="#4070b0">
<b>Login</b><input type=text name=Login size=16><br>
or<br>
<b>Email</b><input type=text name=Email size=16>
<p>
<input type=submit value="Reset password">
<p>
</td>
<td>
Enter your account login <b>OR</b> email address. A confirmation email
will be sent to your registered email address with directions
for resetting your password.
</td>
</tr>
</table>
</form>
""")
req.write(box.end())
req.write(InstDB.footer())
def action(req, fields):
sums = fields.get("Md5Sums", None)
req.write(InstDB.header("MD5 Find"))
# Form submitted?
if sums:
table = InstDB.tabular(["MD5", "Label", "FileName"])
table.tableattr = 'width="100%"'
reObj = re.compile("[0-9a-fA-F]{32}")
for line in sums.split('\n'):
if not reObj.match(line): continue
md5 = line[:32]
label = line[32:].strip()
InstDB.Cur.execute(
"SELECT PatchId, FileName FROM Files WHERE Md5=%s", md5)
result = InstDB.Cur.fetchone()
if result:
filelink = '<a href="patches.py?Action=item&ItemId=%d">%s</a>' \
% (result["PatchId"], result["FileName"])
else:
filelink = "Not found"
req.write(table.addrow([md5, label, filelink]))
req.write(table.end())
req.write(InstDB.footer())
return
box = InstDB.html_box()
req.write(box.start("MD5 Find"))
req.write("""
<form action="" method=post>
<table border=0 cellpadding=8>
<tr>
<td>
Enter one MD5 signature per line optionally followed by white space and a
file name (or any other text). The MD5 signatures will be searched for in the
database. The correct text output can be obtained from programs like <b>md5sum</b> and
others (see Wikipedia <a href="http://en.wikipedia.org/wiki/Md5sum">Md5sum</a>).<br>
Example: <i>752091450e308127a4d70c7623dd4e37 Fluid.sf2</i><br>
</td>
</tr>
<tr>
<td>
<textarea name=Md5Sums rows=16 cols=80>
</textarea>
</td>
</tr>
<tr>
<td>
<input type=submit name=Submit value=Submit>
</td>
</tr>
</table>
</form>
""")
req.write(box.end())
req.write(InstDB.footer())
def action(req, fields):
req.write(InstDB.header("Browse by Category"))
# Get count of active patch files for primary category
InstDB.Cur.execute(
"SELECT CategoryId, COUNT(*) AS UseCount FROM PatchInfo" +
" WHERE State='Active' GROUP BY CategoryId")
UseCounts = {}
for row in InstDB.Cur.fetchall():
UseCounts[row["CategoryId"]] = row["UseCount"]
# Get count of active patch files for secondary category
InstDB.Cur.execute("SELECT CategoryId2, COUNT(*) AS UseCount"
" FROM PatchInfo WHERE CategoryId2 != 0"
" && State='Active' GROUP BY CategoryId2")
for row in InstDB.Cur.fetchall():
oldcount = UseCounts.get(row["CategoryId2"], 0)
UseCounts[row["CategoryId2"]] = oldcount + row["UseCount"]
# Get count of active patch files for 3rd category
InstDB.Cur.execute("SELECT CategoryId3, COUNT(*) AS UseCount"
" FROM PatchInfo WHERE CategoryId3 != 0"
" && State='Active' GROUP BY CategoryId3")
for row in InstDB.Cur.fetchall():
oldcount = UseCounts.get(row["CategoryId3"], 0)
UseCounts[row["CategoryId3"]] = oldcount + row["UseCount"]
# Select all categories (including sub categories)
InstDB.Cur.execute("SELECT CategoryId, CategoryName FROM Category"
" ORDER BY CategoryName")
req.write("<table border=0 cellpadding=4 cellspacing=0>\n")
# Loop over categories, displaying only master categories and summing
# sub category use counts
count = 0
dispcount = 0
rows = InstDB.Cur.fetchall()
for i in range(0, len(rows)):
row = rows[i]
# Next row or None if last
if i + 1 < len(rows): nextRow = rows[i + 1]
else: nextRow = None
# Store ID and name of master category
if string.find(row["CategoryName"], ':') == -1:
id = row["CategoryId"]
name = row["CategoryName"]
count += UseCounts.get(row["CategoryId"], 0)
# Next row is a sub category? - yes: still processing a master category
if nextRow and string.find(nextRow["CategoryName"], ':') != -1:
continue
if dispcount % 4 == 0:
if dispcount != 0: req.write("</tr>\n")
req.write("<tr>\n")
(width, height) = image_sizes.get(name, [100, 100])
req.write("""\
<td align="center">
<a href="patches.py?Action=list&Category=%s">
<img src="images/category/%s.png" alt="Category Image" border=0 width=%d height=%d><br>
<b>%s</b> (%d)
</a>
</td>
""" % (id, name, width, height, name, count))
count = 0
dispcount += 1
if dispcount % 4 != 1: req.write("</tr>\n")
req.write("</table>")
req.write(InstDB.footer())