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 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)