From 0a275722479ff31d6fbc4e737bc97da95a2b563e Mon Sep 17 00:00:00 2001 From: Andrew Ruthven Date: Mon, 22 Jun 2009 21:32:05 +1200 Subject: [PATCH] Many changes to make SQL work with PostgreSQL. --- services/command/feedparse.py | 14 +++++++++----- services/command/newsite.py | 31 +++++++++++++++++++++++-------- services/command/previewsite.py | 6 +++++- whoisi/model.py | 4 ++-- whoisi/utils/fast_history.py | 10 +++++----- 5 files changed, 44 insertions(+), 21 deletions(-) diff --git a/services/command/feedparse.py b/services/command/feedparse.py index 465a238..db31986 100644 --- a/services/command/feedparse.py +++ b/services/command/feedparse.py @@ -217,7 +217,7 @@ class FeedUpdateDatabaseCommand(BaseCommand): SELECT id, site_id, title, link, entry_id, added, published, updated, summary, content, display_cache FROM site_history where site_id = %s """ print(" getting current entries for %d" % self.site_id) - d = self.dcm.runQuery(q, self.site_id) + d = self.dcm.runQuery(q, [ self.site_id ]) d.addCallback(self.gotEntries) d.addErrback(self.error) @@ -415,9 +415,9 @@ class FeedUpdateDatabaseCommand(BaseCommand): # If new_site is in the state, we're adding a new site so set # the flag so that we know these sites were added when the # site was first added. - on_new = 0 + on_new = False if self.state.has_key("new_site"): - on_new = 1 + on_new = True t = datetime.utcnow() @@ -428,7 +428,8 @@ class FeedUpdateDatabaseCommand(BaseCommand): def insertInteraction(self, trans, *args): trans.execute(*args) - return trans.lastrowid + trans.execute("SELECT CURRVAL('site_history_id_seq')") + return trans.fetchone()[0] def maybeUpdateEntry(self, entry, old_entry): published = None @@ -503,11 +504,14 @@ class FeedUpdateDatabaseCommand(BaseCommand): q = "UPDATE site set " + update + "feed_type = %s, title = %s, last_poll = %s WHERE id = %s" args.extend([self.feed["version"], self.feed["title"], datetime.utcnow(), self.site_id]) - d = self.dcm.runQuery(q, args); + d = self.dcm.runInteraction(self.newInteraction, q, args); d.addCallback(self.done) d.addErrback(self.error) + def newInteraction(self, trans, *args): + trans.execute(*args) + def done(self, *args): print(" done.") self.state["site_history_new_ids"] = self.insert_ids diff --git a/services/command/newsite.py b/services/command/newsite.py index 87a8592..57e87e1 100644 --- a/services/command/newsite.py +++ b/services/command/newsite.py @@ -97,7 +97,7 @@ class NewSiteSetup(BaseCommand): SELECT person_id, url, status, data, track_info FROM new_site WHERE id = %s """ - d = self.dcm.runQuery(q, self.id) + d = self.dcm.runQuery(q, [ self.id ]) d.addCallback(self.gotNewSite) d.addErrback(self.error) @@ -469,7 +469,8 @@ class NewSiteCreate(BaseCommand): def newSiteInteraction(self, trans, *args): trans.execute(*args) - return trans.lastrowid + trans.execute("SELECT CURRVAL('site_id_seq')") + return trans.fetchone()[0] def siteSetupDone(self, results): self.state["site_id"] = results @@ -515,12 +516,17 @@ class NewSiteAudit(BaseCommand): referer=ti.get("referer", None) follower=ti.get("follower", None) - d = self.dcm.runQuery(q, (t, "add", "site", state["site_id"], follower, referer, remoteip, useragent)) + d = self.dcm.runInteraction(self.newInteraction, q, (t, "add", "site", state["site_id"], follower, referer, remoteip, useragent)) d.addCallback(self.done) d.addErrback(self.error) return self.d + def newInteraction(self, trans, *args): + trans.execute(*args) + trans.execute("SELECT CURRVAL('change_audit_id_seq')") + return trans.fetchone()[0] + def done(self, *args, **kw): self.d.callback(None) @@ -544,13 +550,18 @@ class NewSiteDone(BaseCommand): q = """ UPDATE new_site SET status = 'done', site_id = %s where id = %s """ - d = self.dcm.runQuery(q, (state["site_id"], state["id"])) + d = self.dcm.runInteraction(self.newSiteInteraction, q, (state["site_id"], state["id"])) + d.addCallback(self.done) d.addErrback(self.error) return self.d - def done(self, *args, **kw): + def newSiteInteraction(self, trans, *args): + trans.execute(*args) + return trans.lastrowid + + def done(self, result, *args, **kw): site_history_new_ids = self.state.get("site_history_new_ids", []) self.d.callback(dict(site_id=self.state["site_id"], site_history_new_ids=site_history_new_ids, @@ -575,7 +586,7 @@ class NewSiteError: UPDATE new_site SET status = 'pick_url', data = %s WHERE id = %s """ - d = self.dcm.runQuery(q, (data, id)) + d = self.dcm.runInteraction(self.newInteraction, q, (data, id)) d.addCallback(self.updateDone) else: @@ -601,13 +612,17 @@ class NewSiteError: else: error = "internal" - d = self.dcm.runQuery(q, (error, id)) + d = self.dcm.runInteraction(self.newInteraction, q, (error, id)) d.addCallback(self.updateDoneError) return self.d + def newInteraction(self, trans, *args): + trans.execute(*args) + return trans.lastrowid + def updateDoneError(self, *args, **kw): self.d.errback(self.orig_failure) - def updateDone(self, *args, **kw): + def updateDone(self, result, *args, **kw): self.d.callback(dict(site_id=None, type=None)) diff --git a/services/command/previewsite.py b/services/command/previewsite.py index 7b4db59..df3838f 100644 --- a/services/command/previewsite.py +++ b/services/command/previewsite.py @@ -70,12 +70,16 @@ class PreviewSiteDone(BaseCommand): UPDATE new_site SET status = 'preview_done', data = %s where id = %s """ - d = self.dcm.runQuery(q, (data, state["id"])) + d = self.dcm.runInteraction(self.newInteraction, q, (data, state["id"])) d.addCallback(self.done) d.addErrback(self.error) return self.d + def newInteraction(self, trans, *args): + trans.execute(*args) + return trans.lastrowid + def done(self, *args, **kw): self.d.callback(None) diff --git a/whoisi/model.py b/whoisi/model.py index 86b40fd..1bee310 100644 --- a/whoisi/model.py +++ b/whoisi/model.py @@ -153,7 +153,7 @@ class Person(SQLObject): This uses raw mysql syntax to generate a random person ID. """ c = Person._connection - result = c.queryAll("select id from person order by rand() limit 1") + result = c.queryAll("select id from person order by random() limit 1") return result[0][0] class Name(SQLObject): @@ -232,7 +232,7 @@ class SiteHistory(SQLObject): summary = UnicodeCol(default=None) content = UnicodeCol(default=None) display_cache = UnicodeCol(default=None) - on_new = BoolCol(default=0) # set to one when this is an item that + on_new = BoolCol(default=False) # set to one when this is an item that # was added when the site was created. def getText(self): diff --git a/whoisi/utils/fast_history.py b/whoisi/utils/fast_history.py index 412f18b..feb6da6 100644 --- a/whoisi/utils/fast_history.py +++ b/whoisi/utils/fast_history.py @@ -78,7 +78,7 @@ site_history, site, person, follow_person WHERE site_history.site_id = site.id AND -site_history.on_new = 0 +site_history.on_new = False AND site.is_removed is NULL AND @@ -151,7 +151,7 @@ site_history.id > %s AND site_history.site_id = site.id AND -site_history.on_new = 0 +site_history.on_new = False AND site.is_removed is NULL AND @@ -176,7 +176,7 @@ site_history, site, follow_person WHERE site_history.site_id = site.id AND -site_history.on_new = 0 +site_history.on_new = False AND site.is_removed is NULL AND @@ -216,7 +216,7 @@ site_history, site, person, name WHERE site_history.site_id = site.id AND -site_history.on_new = 0 +site_history.on_new = False AND site.is_removed is NULL AND @@ -291,7 +291,7 @@ site.is_removed is NULL AND site_history.site_id = site.id AND -site_history.on_new = 0 +site_history.on_new = False %s ORDER BY site_history.id -- 2.30.2