def removeIdentity(self, identityName):
"""Delete an identity
"""
sql = """DELETE FROM twisted_identities WHERE identity_name = '%s';
DELETE FROM twisted_perspectives WHERE identity_name = '%s'""" %\
(adbapi.safe(identityName), adbapi.safe(identityName) )
return self.runOperation(sql)
def articleRequest(self, group, index, id = None):
if id is not None:
sql = """
SELECT postings.article_index, articles.message_id, articles.header, articles.body
FROM groups,postings LEFT OUTER JOIN articles
ON articles.message_id = '%s'
WHERE groups.name = '%s'
AND groups.group_id = postings.group_id
""" % (adbapi.safe(id), adbapi.safe(group))
else:
sql = """
SELECT postings.article_index, articles.message_id, articles.header, articles.body
FROM groups,articles LEFT OUTER JOIN postings
ON postings.article_id = articles.article_id
WHERE postings.article_index = %d
AND postings.group_id = groups.group_id
AND groups.name = '%s'
""" % (index, adbapi.safe(group))
return self.dbpool.runQuery(sql).addCallback(
lambda result: (
result[0][0],
result[0][1],
StringIO.StringIO(result[0][2] + '\r\n' + result[0][3])
)
)
def get_member_tickets(self, callback, error_handler, cardno=None, studyno=None):
if not self.event_id:
return None
cardno = adbapi.safe(str(cardno))
event = adbapi.safe(str(self.event_id))
if cardno:
column = "Felt20"
value = cardno
elif studyno:
column = "Felt11"
value = studyno
else:
return
query = (
"SELECT "
+ "sfl.FUnique AS ticket_id, "
+ "sfl.VareNr AS eventno, "
+ "medlemp.felt20 AS cardno "
+ "FROM sfl, sfh, medlemp "
+ "WHERE sfh.Debitor = medlemp.felt00 "
+ "AND sfl.SFHUnique = sfh.FUnique "
+ "AND medlemp.{0} = '{1}' ".format(column, value)
+ "AND sfl.VareNr = '{0}' ".format(event)
)
q = self.winkas_db.runQuery(query, ())
q.addCallbacks(callback, error_handler)
def changePassword(self, identityName, hashedPassword, callback=None, errback=None):
passwd = base64.encodestring(hashedPassword)
sql = """UPDATE twisted_identities
SET password = '******'
WHERE identity_name = '%s'""" %\
(adbapi.safe(passwd), adbapi.safe(identityName) )
return self.runOperation(sql).addCallbacks(callback, errback)
def removePerspective(self, identityName, perspectiveName, callback=None, errback=None):
"""Delete a perspective for an identity
"""
sql = """DELETE FROM twisted_perspectives
WHERE identity_name = '%s'
AND perspective_name = '%s'""" %\
(adbapi.safe(identityName), adbapi.safe(perspectiveName))
return self.runOperation(sql).addCallbacks(callback, errback)
def addIdentity(self, identity):
"""Store an identity in the database.
"""
passwd = base64.encodestring(identity.hashedPassword)
username = identity.name
createIdentity = "INSERT INTO twisted_identities VALUES ('%s', '%s')" % (adbapi.safe(username), adbapi.safe(passwd) )
s = [createIdentity]
for (svcname, pname) in identity.keyring.keys():
# note, we don't actually know perspective type at this point...
s.append("INSERT INTO twisted_perspectives VALUES ('%s', '%s', '%s', NULL)" %
(adbapi.safe(username), adbapi.safe(pname), adbapi.safe(svcname)) )
sql = string.join(s, '; \n')
return self.runOperation(sql)
def getPerspectives(self, identity_name):
"""Get the perspectives for an identity. Used by the web admin interface.
"""
sql="""SELECT identity_name, perspective_name, service_name
FROM twisted_perspectives
WHERE identity_name = '%s'""" % adbapi.safe(identity_name)
return self.runQuery(sql)
def get_query(self, component):
return ''' SELECT COUNT(*)
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME="%s"
''' % adbapi.safe(
component.split(NAME_SPLITTER)[-1]
)
def escape_string(self, text):
"""Escape a string for use in an SQL statement. The default
implementation escapes ' with '' and \ with \\. Redefine this
function in a subclass if your database server uses different
escaping rules.
"""
return adbapi.safe(text)
def articleExistsRequest(self, id):
sql = """
SELECT COUNT(message_id) FROM articles
WHERE message_id = '%s'
""" % (adbapi.safe(id),)
return self.dbpool.runQuery(sql).addCallback(
lambda result: bool(result[0][0])
)
def get_member_used(self, callback, error_handler, cardno=None, studyno=None):
if not self.event_id:
return None
cardno = adbapi.safe(str(cardno))
event = adbapi.safe(str(self.event_id))
if cardno:
column = "cardno"
value = cardno
elif studyno:
column = "studyno"
value = studyno
else:
return
query = "SELECT 1 FROM used WHERE `{0}` = '{1}' AND `eventno` = '{2}'".format(column, value, event)
q = self.ticket_db.runQuery(query, ())
q.addCallbacks(callback, error_handler)
def listGroupRequest(self, group):
sql = """
SELECT postings.article_index FROM postings,groups
WHERE postings.group_id = groups.group_id
AND groups.name = '%s'
""" % (adbapi.safe(group),)
return self.dbpool.runQuery(sql).addCallback(
lambda results, group = group: (group, [res[0] for res in results])
)
def headRequest(self, group, index):
sql = """
SELECT postings.article_index, articles.message_id, articles.header
FROM groups,articles LEFT OUTER JOIN postings
ON postings.article_id = articles.article_id
WHERE postings.article_index = %d
AND postings.group_id = groups.group_id
AND groups.name = '%s'
""" % (index, adbapi.safe(group))
return self.dbpool.runQuery(sql).addCallback(lambda result: result[0])
def get_query(self, component):
return '''
SELECT
count(table_name) table_count,
sum(data_length + index_length) size,
sum(data_length) data_size,
sum(index_length) index_size
FROM
information_schema.TABLES
WHERE
table_schema = "%s"
''' % adbapi.safe(component.split(NAME_SPLITTER)[-1])
def xhdrRequest(self, group, low, high, header):
sql = """
SELECT articles.header
FROM groups,postings,articles
WHERE groups.name = '%s' AND postings.group_id = groups.group_id
AND postings.article_index >= %d
AND postings.article_index <= %d
""" % (adbapi.safe(group), low, high)
return self.dbpool.runQuery(sql).addCallback(
lambda results: [
(i, Article(h, None).getHeader(h)) for (i, h) in results
]
)
def getIdentityRequest(self, name):
"""get the identity from the database with the specified name.
"""
sql = """
SELECT twisted_identities.identity_name,
twisted_identities.password,
twisted_perspectives.perspective_name,
twisted_perspectives.service_name
FROM twisted_identities,
twisted_perspectives
WHERE twisted_identities.identity_name = twisted_perspectives.identity_name
AND twisted_identities.identity_name = '%s'
""" % adbapi.safe(name)
return self.runQuery(sql).addCallbacks(self._cbIdentity)
def groupRequest(self, group):
sql = """
SELECT groups.name,
COUNT(postings.article_index),
COALESCE(MAX(postings.article_index), 0),
COALESCE(MIN(postings.article_index), 0),
groups.flags
FROM groups LEFT OUTER JOIN postings
ON postings.group_id = groups.group_id
WHERE groups.name = '%s'
GROUP BY groups.name, groups.flags
""" % (adbapi.safe(group), )
return self.dbpool.runQuery(sql).addCallback(
lambda results: tuple(results[0]))
def bodyRequest(self, group, index):
sql = """
SELECT postings.article_index, articles.message_id, articles.body
FROM groups,articles LEFT OUTER JOIN postings
ON postings.article_id = articles.article_id
WHERE postings.article_index = %d
AND postings.group_id = groups.group_id
AND groups.name = '%s'
""" % (index, adbapi.safe(group))
return self.dbpool.runQuery(sql).addCallback(
lambda result: result[0]
).addCallback(
# result is a tuple of (index, id, body)
lambda result: (result[0], result[1], StringIO.StringIO(result[2]))
)
def groupRequest(self, group):
sql = """
SELECT groups.name,
COUNT(postings.article_index),
COALESCE(MAX(postings.article_index), 0),
COALESCE(MIN(postings.article_index), 0),
groups.flags
FROM groups LEFT OUTER JOIN postings
ON postings.group_id = groups.group_id
WHERE groups.name = '%s'
GROUP BY groups.name, groups.flags
""" % (adbapi.safe(group),)
return self.dbpool.runQuery(sql).addCallback(
lambda results: tuple(results[0])
)
def bodyRequest(self, group, index):
sql = """
SELECT postings.article_index, articles.message_id, articles.body
FROM groups,articles LEFT OUTER JOIN postings
ON postings.article_id = articles.article_id
WHERE postings.article_index = %d
AND postings.group_id = groups.group_id
AND groups.name = '%s'
""" % (index, adbapi.safe(group))
return self.dbpool.runQuery(sql).addCallback(
lambda result: result[0]
).addCallback(
# result is a tuple of (index, id, body)
lambda result: (result[0], result[1], StringIO.StringIO(result[2]))
)
def xoverRequest(self, group, low, high):
sql = """
SELECT postings.article_index, articles.header
FROM articles,postings,groups
WHERE postings.group_id = groups.group_id
AND groups.name = '%s'
AND postings.article_id = articles.article_id
%s
%s
""" % (adbapi.safe(group),
low is not None and "AND postings.article_index >= %d" % (low, )
or "", high is not None and "AND postings.article_index <= %d" %
(high, ) or "")
return self.dbpool.runQuery(sql).addCallback(
lambda results: [[id] + Article(header, None).overview()
for (id, header) in results])
def xoverRequest(self, group, low, high):
sql = """
SELECT postings.article_index, articles.header
FROM articles,postings,groups
WHERE postings.group_id = groups.group_id
AND groups.name = '%s'
AND postings.article_id = articles.article_id
%s
%s
""" % (
adbapi.safe(group),
low is not None and "AND postings.article_index >= %d" % (low,) or "",
high is not None and "AND postings.article_index <= %d" % (high,) or "",
)
return self.dbpool.runQuery(sql).addCallback(
lambda results: [[id] + Article(header, None).overview() for (id, header) in results]
)
def makeOverviewSQL():
res = ''
for o in OVERVIEW_FMT:
res = res + """\n INSERT INTO overview (header) VALUES ('%s');\n""" % (
adbapi.safe(o), )
return res
def makeGroupSQL(groups):
res = ''
for g in groups:
res = res + """\n INSERT INTO groups (name) VALUES ('%s');\n""" % (
adbapi.safe(g), )
return res
def _doPost(self, transaction, article):
# Get the group ids
groups = article.getHeader('Newsgroups').split()
if not len(groups):
raise NNTPError('Missing Newsgroups header')
sql = """
SELECT name, group_id FROM groups
WHERE name IN (%s)
""" % (', '.join([("'%s'" % (adbapi.safe(group), ))
for group in groups]), )
transaction.execute(sql)
result = transaction.fetchall()
# No relevant groups, bye bye!
if not len(result):
raise NNTPError('None of groups in Newsgroup header carried')
# Got some groups, now find the indices this article will have in each
sql = """
SELECT groups.group_id, COALESCE(MAX(postings.article_index), 0) + 1
FROM groups LEFT OUTER JOIN postings
ON postings.group_id = groups.group_id
WHERE groups.group_id IN (%s)
GROUP BY groups.group_id
""" % (', '.join([("%d" % (id, )) for (group, id) in result]), )
transaction.execute(sql)
indices = transaction.fetchall()
if not len(indices):
raise NNTPError('Internal server error - no indices found')
# Associate indices with group names
gidToName = dict([(b, a) for (a, b) in result])
gidToIndex = dict(indices)
nameIndex = []
for i in gidToName:
nameIndex.append((gidToName[i], gidToIndex[i]))
# Build xrefs
xrefs = socket.gethostname().split()[0]
xrefs = xrefs + ' ' + ' '.join([('%s:%d' % (group, id))
for (group, id) in nameIndex])
article.putHeader('Xref', xrefs)
# Hey! The article is ready to be posted! God damn f'in finally.
sql = """
INSERT INTO articles (message_id, header, body)
VALUES ('%s', '%s', '%s')
""" % (adbapi.safe(article.getHeader('Message-ID')),
adbapi.safe(article.textHeaders()), adbapi.safe(article.body))
transaction.execute(sql)
# Now update the posting to reflect the groups to which this belongs
for gid in gidToName:
sql = """
INSERT INTO postings (group_id, article_id, article_index)
VALUES (%d, (SELECT last_value FROM articles_article_id_seq), %d)
""" % (gid, gidToIndex[gid])
transaction.execute(sql)
return len(nameIndex)
def addEmptyIdentity(self, identityName, hashedPassword, callback=None, errback=None):
"""Create an empty identity (no perspectives). Used by web admin interface.
"""
passwd = base64.encodestring(hashedPassword)
sql = "INSERT INTO twisted_identities VALUES ('%s', '%s')" % (adbapi.safe(identityName), adbapi.safe(passwd))
return self.runOperation(sql).addCallbacks(callback, errback)
def makeOverviewSQL():
res = ''
for o in OVERVIEW_FMT:
res = res + """\n INSERT INTO overview (header) VALUES ('%s');\n""" % (adbapi.safe(o),)
return res
def makeGroupSQL(groups):
res = ''
for g in groups:
res = res + """\n INSERT INTO groups (name) VALUES ('%s');\n""" % (adbapi.safe(g),)
return res
def get_query(self, component):
return ''' SELECT COUNT(*)
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME="%s"
''' % adbapi.safe(component.split(NAME_SPLITTER)[-1])
def addPerspective(self, identityName, perspectiveName, serviceName, callback=None, errback=None):
"""Add a perspective by name to an identity.
"""
sql = "INSERT INTO twisted_perspectives VALUES ('%s', '%s', '%s', NULL)" %\
(adbapi.safe(identityName), adbapi.safe(perspectiveName), adbapi.safe(serviceName))
return self.runOperation(sql).addCallbacks(callback, errback)
def _doPost(self, transaction, article):
# Get the group ids
groups = article.getHeader('Newsgroups').split()
if not len(groups):
raise NNTPError('Missing Newsgroups header')
sql = """
SELECT name, group_id FROM groups
WHERE name IN (%s)
""" % (', '.join([("'%s'" % (adbapi.safe(group),)) for group in groups]),)
transaction.execute(sql)
result = transaction.fetchall()
# No relevant groups, bye bye!
if not len(result):
raise NNTPError('None of groups in Newsgroup header carried')
# Got some groups, now find the indices this article will have in each
sql = """
SELECT groups.group_id, COALESCE(MAX(postings.article_index), 0) + 1
FROM groups LEFT OUTER JOIN postings
ON postings.group_id = groups.group_id
WHERE groups.group_id IN (%s)
GROUP BY groups.group_id
""" % (', '.join([("%d" % (id,)) for (group, id) in result]),)
transaction.execute(sql)
indices = transaction.fetchall()
if not len(indices):
raise NNTPError('Internal server error - no indices found')
# Associate indices with group names
gidToName = dict([(b, a) for (a, b) in result])
gidToIndex = dict(indices)
nameIndex = []
for i in gidToName:
nameIndex.append((gidToName[i], gidToIndex[i]))
# Build xrefs
xrefs = socket.gethostname().split()[0]
xrefs = xrefs + ' ' + ' '.join([('%s:%d' % (group, id)) for (group, id) in nameIndex])
article.putHeader('Xref', xrefs)
# Hey! The article is ready to be posted! God damn f'in finally.
sql = """
INSERT INTO articles (message_id, header, body)
VALUES ('%s', '%s', '%s')
""" % (
adbapi.safe(article.getHeader('Message-ID')),
adbapi.safe(article.textHeaders()),
adbapi.safe(article.body)
)
transaction.execute(sql)
# Now update the posting to reflect the groups to which this belongs
for gid in gidToName:
sql = """
INSERT INTO postings (group_id, article_id, article_index)
VALUES (%d, (SELECT last_value FROM articles_article_id_seq), %d)
""" % (gid, gidToIndex[gid])
transaction.execute(sql)
return len(nameIndex)
def escape(self, s):
return "'%s'" % safe(s)
