From d02bd8e58ac7d965252809373d2a18de9a90c05a Mon Sep 17 00:00:00 2001 From: Magnus Hagander Date: Thu, 31 Dec 2015 15:04:10 +0100 Subject: [PATCH] Move all tables to public schema Since django still does not properly support multiple schemas, it creates a lot of pain to maintain the tables in different schemas. And the planet database has a very simple schema with few tables anyway, so there is arguably no actual gain from keeping them separate. When deploying, run SQL manually to move existing tables over: ALTER TABLE admin.auditlog SET SCHEMA public; ALTER TABLE admin.auth_group SET SCHEMA public; ALTER TABLE admin.auth_group_permissions SET SCHEMA public; ALTER TABLE admin.auth_message SET SCHEMA public; ALTER TABLE admin.auth_permission SET SCHEMA public; ALTER TABLE admin.auth_user SET SCHEMA public; ALTER TABLE admin.auth_user_groups SET SCHEMA public; ALTER TABLE admin.auth_user_user_permissions SET SCHEMA public; ALTER TABLE admin.django_admin_log SET SCHEMA public; ALTER TABLE admin.django_content_type SET SCHEMA public; ALTER TABLE admin.django_session SET SCHEMA public; ALTER TABLE admin.django_site SET SCHEMA public; ALTER TABLE planet.aggregatorlog SET SCHEMA public; ALTER TABLE planet.feeds SET SCHEMA public; ALTER TABLE planet.posts SET SCHEMA public; ALTER TABLE planet.teams SET SCHEMA public; DROP SCHEMA admin; DROP SCHEMA planet; and if there is a planetadmin user, also run ALTER USER planetadmin RESET search_path; --- aggregator.py | 14 ++++---- discovery.py | 4 +-- generator.py | 24 ++++++------- hamnadmin/hamnadmin/register/models.py | 10 +++--- listsync.py | 6 ++-- logmailer.py | 4 +-- posttotwitter.py | 6 ++-- redirector/redirector.py | 2 +- schema.sql | 47 ++++++++++++-------------- synctwitter.py | 2 +- 10 files changed, 58 insertions(+), 61 deletions(-) diff --git a/aggregator.py b/aggregator.py index 1739af5..efdcd96 100755 --- a/aggregator.py +++ b/aggregator.py @@ -23,13 +23,13 @@ class Aggregator: def Update(self): feeds = self.db.cursor() - feeds.execute('SELECT id,feedurl,name,lastget,authorfilter FROM planet.feeds') + feeds.execute('SELECT id,feedurl,name,lastget,authorfilter FROM feeds') for feed in feeds.fetchall(): try: n = self.ParseFeed(feed) if n > 0: c = self.db.cursor() - c.execute("INSERT INTO planet.aggregatorlog (feed, success, info) VALUES (%(feed)s, 't', %(info)s)", { + c.execute("INSERT INTO aggregatorlog (feed, success, info) VALUES (%(feed)s, 't', %(info)s)", { 'feed': feed[0], 'info': 'Fetched %s posts.' % n, }) @@ -37,7 +37,7 @@ class Aggregator: print "Exception when parsing feed '%s': %s" % (feed[1], e) self.db.rollback() c = self.db.cursor() - c.execute("INSERT INTO planet.aggregatorlog (feed, success, info) VALUES (%(feed)s, 'f', %(info)s)", { + c.execute("INSERT INTO aggregatorlog (feed, success, info) VALUES (%(feed)s, 'f', %(info)s)", { 'feed': feed[0], 'info': 'Error: "%s"' % e, }) @@ -114,13 +114,13 @@ class Aggregator: # currently define rediculously long as 5 days d = datetime.datetime.now() - self.db.cursor().execute("UPDATE planet.feeds SET lastget=%(date)s WHERE id=%(feed)s AND NOT lastget=%(date)s", { 'date': d, 'feed': feedinfo[0]}) + self.db.cursor().execute("UPDATE feeds SET lastget=%(date)s WHERE id=%(feed)s AND NOT lastget=%(date)s", { 'date': d, 'feed': feedinfo[0]}) else: # We didn't get a Last-Modified time, so set it to the entry date # for the latest entry in this feed. Only do this if we have more # than one entry. if numadded > 0: - self.db.cursor().execute("UPDATE planet.feeds SET lastget=COALESCE((SELECT max(dat) FROM planet.posts WHERE planet.posts.feed=planet.feeds.id),'2000-01-01') WHERE planet.feeds.id=%(feed)s", {'feed': feedinfo[0]}) + self.db.cursor().execute("UPDATE feeds SET lastget=COALESCE((SELECT max(dat) FROM posts WHERE posts.feed=feeds.id),'2000-01-01') WHERE feeds.id=%(feed)s", {'feed': feedinfo[0]}) # Return the number of feeds we actually added return numadded @@ -143,11 +143,11 @@ class Aggregator: def StoreEntry(self, feedid, guid, date, link, guidisperma, title, txt): c = self.db.cursor() - c.execute("SELECT id FROM planet.posts WHERE feed=%(feed)s AND guid=%(guid)s", {'feed':feedid, 'guid':guid}) + c.execute("SELECT id FROM posts WHERE feed=%(feed)s AND guid=%(guid)s", {'feed':feedid, 'guid':guid}) if c.rowcount > 0: return 0 print "Store entry %s from feed %s" % (guid, feedid) - c.execute("INSERT INTO planet.posts (feed,guid,link,guidisperma,dat,title,txt) VALUES (%(feed)s,%(guid)s,%(link)s,%(guidisperma)s,%(date)s,%(title)s,%(txt)s)", + c.execute("INSERT INTO posts (feed,guid,link,guidisperma,dat,title,txt) VALUES (%(feed)s,%(guid)s,%(link)s,%(guidisperma)s,%(date)s,%(title)s,%(txt)s)", {'feed': feedid, 'guid': guid, 'link': link, diff --git a/discovery.py b/discovery.py index 5beeeb9..a4a4bae 100755 --- a/discovery.py +++ b/discovery.py @@ -22,7 +22,7 @@ class Aggregator: def Update(self): feeds = self.db.cursor() - feeds.execute("SELECT id,feedurl,name,blogurl FROM planet.feeds WHERE blogurl='' AND feedurl NOT LIKE '%planet%'") + feeds.execute("SELECT id,feedurl,name,blogurl FROM feeds WHERE blogurl='' AND feedurl NOT LIKE '%planet%'") for feed in feeds.fetchall(): self.DiscoverFeed(feed) self.db.commit() @@ -39,7 +39,7 @@ class Aggregator: if feed.feed.link: print "Setting feed for %s to %s" % (feedinfo[2], feed.feed.link) c = self.db.cursor() - c.execute("UPDATE planet.feeds SET blogurl='%s' WHERE id=%i" % (feed.feed.link, feedinfo[0])) + c.execute("UPDATE feeds SET blogurl='%s' WHERE id=%i" % (feed.feed.link, feedinfo[0])) except: print "Exception when processing feed for %s" % (feedinfo[2]) print feed diff --git a/generator.py b/generator.py index 8d72f5c..1e72f50 100755 --- a/generator.py +++ b/generator.py @@ -66,7 +66,7 @@ class Generator: self.db.set_client_encoding('UTF8') c = self.db.cursor() c.execute("SET TIMEZONE=GMT") - c.execute("SELECT guid,link,dat,title,txt,planet.feeds.name,blogurl,guidisperma,planet.teams.name,planet.teams.teamurl FROM planet.posts INNER JOIN planet.feeds ON planet.feeds.id=planet.posts.feed LEFT JOIN planet.teams ON planet.feeds.team = planet.teams.id WHERE planet.feeds.approved AND NOT planet.posts.hidden ORDER BY dat DESC LIMIT 30") + c.execute("SELECT guid,link,dat,title,txt,feeds.name,blogurl,guidisperma,teams.name,teams.teamurl FROM posts INNER JOIN feeds ON feeds.id=posts.feed LEFT JOIN teams ON feeds.team = teams.id WHERE feeds.approved AND NOT posts.hidden ORDER BY dat DESC LIMIT 30") for post in c.fetchall(): desc = self.TruncateAndCleanDescription(post[4]) rss.items.append(PyRSS2Gen.RSSItem( @@ -84,37 +84,37 @@ class Generator: self.items.append(PlanetPost(post[0], post[1], post[2], post[3], post[5], post[6], desc, post[8], post[9])) c.execute(""" -SELECT planet.feeds.name,blogurl,feedurl,count(*),planet.teams.name,planet.teams.teamurl,NULL,max(planet.posts.dat) FROM planet.feeds -INNER JOIN planet.posts ON planet.feeds.id=planet.posts.feed -LEFT JOIN planet.teams ON planet.teams.id=planet.feeds.team +SELECT feeds.name,blogurl,feedurl,count(*),teams.name,teams.teamurl,NULL,max(posts.dat) FROM feeds +INNER JOIN posts ON feeds.id=posts.feed +LEFT JOIN teams ON teams.id=feeds.team WHERE age(dat) < '1 month' AND approved AND NOT hidden AND NOT excludestats -GROUP BY planet.feeds.name,blogurl,feedurl,planet.teams.name,teamurl ORDER BY 4 DESC, 8 DESC, 1 LIMIT 20 +GROUP BY feeds.name,blogurl,feedurl,teams.name,teamurl ORDER BY 4 DESC, 8 DESC, 1 LIMIT 20 """) self.topposters = [PlanetFeed(feed) for feed in c.fetchall()] if len(self.topposters) < 2: self.topposters = [] c.execute(""" -SELECT NULL,NULL,NULL,NULL,planet.teams.name, teamurl, count(*) FROM -planet.feeds -INNER JOIN planet.posts ON planet.feeds.id=planet.posts.feed -INNER JOIN planet.teams ON planet.teams.id=planet.feeds.team +SELECT NULL,NULL,NULL,NULL,teams.name, teamurl, count(*) FROM +feeds +INNER JOIN posts ON feeds.id=posts.feed +INNER JOIN teams ON teams.id=feeds.team WHERE age(dat) < '1 month' AND approved AND NOT hidden AND NOT excludestats -GROUP BY planet.teams.name, teamurl ORDER BY 7 DESC, 1 LIMIT 10""") +GROUP BY teams.name, teamurl ORDER BY 7 DESC, 1 LIMIT 10""") self.topteams = [PlanetFeed(feed) for feed in c.fetchall()] if len(self.topteams) < 2: self.topteams = [] c.execute(""" -SELECT name,blogurl,feedurl,NULL,NULL,NULL,NULL FROM planet.feeds +SELECT name,blogurl,feedurl,NULL,NULL,NULL,NULL FROM feeds WHERE approved AND team IS NULL ORDER BY name,blogurl """) self.allposters = [PlanetFeed(feed) for feed in c.fetchall()] c.execute(""" SELECT feeds.name AS feedname,blogurl,feedurl,NULL,teams.name,teamurl,NULL -FROM planet.feeds INNER JOIN planet.teams ON planet.feeds.team=planet.teams.id +FROM feeds INNER JOIN teams ON feeds.team=teams.id WHERE approved ORDER BY teams.name,feeds.name,blogurl """) self.allteams = [PlanetFeed(feed) for feed in c.fetchall()] diff --git a/hamnadmin/hamnadmin/register/models.py b/hamnadmin/hamnadmin/register/models.py index 7130dec..9b918a4 100644 --- a/hamnadmin/hamnadmin/register/models.py +++ b/hamnadmin/hamnadmin/register/models.py @@ -10,7 +10,7 @@ class Team(models.Model): return "%s (%s)" % (self.name, self.teamurl) class Meta: - db_table = 'planet\".\"teams' + db_table = 'teams' class Admin: pass @@ -36,7 +36,7 @@ class Blog(models.Model): return u.email class Meta: - db_table = 'planet\".\"feeds' + db_table = 'feeds' ordering = ['approved','name'] class Admin: @@ -58,7 +58,7 @@ class Post(models.Model): return self.title class Meta: - db_table = 'planet\".\"posts' + db_table = 'posts' ordering = ['-dat'] class Admin: @@ -79,7 +79,7 @@ class AuditEntry(models.Model): return "%s (%s): %s" % (self.logtime, self.user, self.logtxt) class Meta: - db_table = 'admin\".\"auditlog' + db_table = 'auditlog' ordering = ['logtime'] class AggregatorLog(models.Model): @@ -89,7 +89,7 @@ class AggregatorLog(models.Model): info = models.TextField() class Meta: - db_table = 'planet\".\"aggregatorlog' + db_table = 'aggregatorlog' ordering = ['-ts'] def __unicode__(self): diff --git a/listsync.py b/listsync.py index 359086f..1bec00a 100755 --- a/listsync.py +++ b/listsync.py @@ -144,9 +144,9 @@ class Synchronizer: curs = self.db.cursor() curs.execute(""" -SELECT email FROM admin.auth_user -INNER JOIN planet.feeds ON admin.auth_user.username=planet.feeds.userid -WHERE planet.feeds.approved +SELECT email FROM auth_user +INNER JOIN feeds ON auth_user.username=feeds.userid +WHERE feeds.approved """) self.expected = set([r[0] for r in curs.fetchall()]) diff --git a/logmailer.py b/logmailer.py index ca20c86..b3b231f 100755 --- a/logmailer.py +++ b/logmailer.py @@ -24,8 +24,8 @@ class LogChecker(object): def Check(self): c = self.db.cursor() c.execute(""" - SELECT name,info,count(*) as num FROM planet.aggregatorlog - INNER JOIN planet.feeds ON feed=feeds.id + SELECT name,info,count(*) as num FROM aggregatorlog + INNER JOIN feeds ON feed=feeds.id WHERE success='f' AND ts > CURRENT_TIMESTAMP-'24 hours'::interval GROUP BY name,info HAVING count(*) > %s diff --git a/posttotwitter.py b/posttotwitter.py index 996b5aa..be2650d 100755 --- a/posttotwitter.py +++ b/posttotwitter.py @@ -50,7 +50,7 @@ class PostToTwitter(TwitterClient): def Run(self): c = self.db.cursor() c.execute("""SELECT posts.id, posts.title, posts.link, posts.shortlink, feeds.name, feeds.twitteruser - FROM planet.posts INNER JOIN planet.feeds ON planet.posts.feed=planet.feeds.id + FROM posts INNER JOIN feeds ON posts.feed=feeds.id WHERE approved AND age(dat) < '7 days' AND NOT (twittered OR hidden) ORDER BY dat""") for post in c.fetchall(): if post[3] and len(post[3])>1: @@ -65,7 +65,7 @@ class PostToTwitter(TwitterClient): print "Failed to shorten URL %s: %s" % (post[2], e) continue - c.execute("UPDATE planet.posts SET shortlink=%(short)s WHERE id=%(id)s", { + c.execute("UPDATE posts SET shortlink=%(short)s WHERE id=%(id)s", { 'short': short, 'id': post[0], }) @@ -96,7 +96,7 @@ class PostToTwitter(TwitterClient): continue # Flag this item as posted - c.execute("UPDATE planet.posts SET twittered='t' WHERE id=%(id)s", { 'id': post[0] }) + c.execute("UPDATE posts SET twittered='t' WHERE id=%(id)s", { 'id': post[0] }) self.db.commit() print unicode("Twittered: %s" % msg).encode('utf8') diff --git a/redirector/redirector.py b/redirector/redirector.py index 7d4a507..55dc6ce 100755 --- a/redirector/redirector.py +++ b/redirector/redirector.py @@ -35,7 +35,7 @@ def application(environ, start_response): # bother with any connection pooling. conn = psycopg2.connect(connstr) c = conn.cursor() - c.execute("SELECT link FROM planet.posts WHERE id=%(id)s", { + c.execute("SELECT link FROM posts WHERE id=%(id)s", { 'id': id }) r = c.fetchall() diff --git a/schema.sql b/schema.sql index 80c9df2..cb011bd 100644 --- a/schema.sql +++ b/schema.sql @@ -12,17 +12,14 @@ SET escape_string_warning = off; -- Name: planet; Type: SCHEMA; Schema: -; Owner: - -- -CREATE SCHEMA planet; - - -SET search_path = planet, pg_catalog; +SET search_path = pg_catalog; SET default_tablespace = ''; SET default_with_oids = false; -- --- Name: aggregatorlog; Type: TABLE; Schema: planet; Owner: -; Tablespace: +-- Name: aggregatorlog; Type: TABLE; Schema: public; Owner: -; Tablespace: -- CREATE TABLE aggregatorlog ( @@ -35,7 +32,7 @@ CREATE TABLE aggregatorlog ( -- --- Name: feeds; Type: TABLE; Schema: planet; Owner: -; Tablespace: +-- Name: feeds; Type: TABLE; Schema: public; Owner: -; Tablespace: -- CREATE TABLE feeds ( @@ -52,7 +49,7 @@ CREATE TABLE feeds ( -- --- Name: posts; Type: TABLE; Schema: planet; Owner: -; Tablespace: +-- Name: posts; Type: TABLE; Schema: public; Owner: -; Tablespace: -- CREATE TABLE posts ( @@ -71,7 +68,7 @@ CREATE TABLE posts ( -- --- Name: aggregatorlog_id_seq; Type: SEQUENCE; Schema: planet; Owner: - +-- Name: aggregatorlog_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- CREATE SEQUENCE aggregatorlog_id_seq @@ -82,14 +79,14 @@ CREATE SEQUENCE aggregatorlog_id_seq -- --- Name: aggregatorlog_id_seq; Type: SEQUENCE OWNED BY; Schema: planet; Owner: - +-- Name: aggregatorlog_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- ALTER SEQUENCE aggregatorlog_id_seq OWNED BY aggregatorlog.id; -- --- Name: feeds_id_seq; Type: SEQUENCE; Schema: planet; Owner: - +-- Name: feeds_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- CREATE SEQUENCE feeds_id_seq @@ -100,14 +97,14 @@ CREATE SEQUENCE feeds_id_seq -- --- Name: feeds_id_seq; Type: SEQUENCE OWNED BY; Schema: planet; Owner: - +-- Name: feeds_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- ALTER SEQUENCE feeds_id_seq OWNED BY feeds.id; -- --- Name: posts_id_seq; Type: SEQUENCE; Schema: planet; Owner: - +-- Name: posts_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- CREATE SEQUENCE posts_id_seq @@ -118,35 +115,35 @@ CREATE SEQUENCE posts_id_seq -- --- Name: posts_id_seq; Type: SEQUENCE OWNED BY; Schema: planet; Owner: - +-- Name: posts_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- ALTER SEQUENCE posts_id_seq OWNED BY posts.id; -- --- Name: id; Type: DEFAULT; Schema: planet; Owner: - +-- Name: id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE aggregatorlog ALTER COLUMN id SET DEFAULT nextval('aggregatorlog_id_seq'::regclass); -- --- Name: id; Type: DEFAULT; Schema: planet; Owner: - +-- Name: id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE feeds ALTER COLUMN id SET DEFAULT nextval('feeds_id_seq'::regclass); -- --- Name: id; Type: DEFAULT; Schema: planet; Owner: - +-- Name: id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE posts ALTER COLUMN id SET DEFAULT nextval('posts_id_seq'::regclass); -- --- Name: aggregatorlog_pkey; Type: CONSTRAINT; Schema: planet; Owner: -; Tablespace: +-- Name: aggregatorlog_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY aggregatorlog @@ -154,7 +151,7 @@ ALTER TABLE ONLY aggregatorlog -- --- Name: feeds_pkey; Type: CONSTRAINT; Schema: planet; Owner: -; Tablespace: +-- Name: feeds_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY feeds @@ -162,7 +159,7 @@ ALTER TABLE ONLY feeds -- --- Name: posts_pkey; Type: CONSTRAINT; Schema: planet; Owner: -; Tablespace: +-- Name: posts_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY posts @@ -170,35 +167,35 @@ ALTER TABLE ONLY posts -- --- Name: aggregatorlog_feed_idx; Type: INDEX; Schema: planet; Owner: -; Tablespace: +-- Name: aggregatorlog_feed_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: -- CREATE INDEX aggregatorlog_feed_idx ON aggregatorlog USING btree (feed); -- --- Name: aggregatorlog_feed_ts_idx; Type: INDEX; Schema: planet; Owner: -; Tablespace: +-- Name: aggregatorlog_feed_ts_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: -- CREATE INDEX aggregatorlog_feed_ts_idx ON aggregatorlog USING btree (feed, ts); -- --- Name: feeds_feddurl; Type: INDEX; Schema: planet; Owner: -; Tablespace: +-- Name: feeds_feddurl; Type: INDEX; Schema: public; Owner: -; Tablespace: -- CREATE INDEX feeds_feddurl ON feeds USING btree (feedurl); -- --- Name: feeds_name; Type: INDEX; Schema: planet; Owner: -; Tablespace: +-- Name: feeds_name; Type: INDEX; Schema: public; Owner: -; Tablespace: -- CREATE INDEX feeds_name ON feeds USING btree (name); -- --- Name: aggregatorlog_feed_fkey; Type: FK CONSTRAINT; Schema: planet; Owner: - +-- Name: aggregatorlog_feed_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY aggregatorlog @@ -206,7 +203,7 @@ ALTER TABLE ONLY aggregatorlog -- --- Name: posts_feed_fkey; Type: FK CONSTRAINT; Schema: planet; Owner: - +-- Name: posts_feed_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY posts diff --git a/synctwitter.py b/synctwitter.py index 206e94e..892de3c 100755 --- a/synctwitter.py +++ b/synctwitter.py @@ -23,7 +23,7 @@ class SyncTwitter(TwitterClient): def Run(self): # Get list of handles that should be on the list curs = self.db.cursor() - curs.execute("SELECT DISTINCT lower(twitteruser) FROM planet.feeds WHERE NOT (twitteruser IS NULL OR twitteruser='') ORDER BY lower(twitteruser)"); + curs.execute("SELECT DISTINCT lower(twitteruser) FROM feeds WHERE NOT (twitteruser IS NULL OR twitteruser='') ORDER BY lower(twitteruser)"); expected = set([r[0].replace('@','') for r in curs.fetchall()]) # Get list of current screen names the list is following -- 2.39.5