Launchpad itself

Merge lp:~stub/launchpad/replication into lp:launchpad

  1. replication
  2. Merge into devel
Proposed by Stuart Bishop
Status: Merged
Approved by: Tim Penhey
Approved revision: no longer in the source branch.
Merged at revision: not available
Proposed branch: lp:~stub/launchpad/replication
Merge into: lp:launchpad
Diff against target: 869 lines (+849/-0)
3 files modified
database/replication/Makefile (+3/-0)
database/replication/authdb_create.sql (+832/-0)
database/replication/authdb_drop.sql (+14/-0)
To merge this branch: bzr merge lp:~stub/launchpad/replication
Reviewer Review Type Date Requested Status
Tim Penhey (community) Approve
Review via email: mp+14738@code.launchpad.net

Commit message

Fix staging database restores

To post a comment you must log in.
Revision history for this message
Stuart Bishop (stub) wrote :

Fix staging database restores.

The staging database rebuilds have been disabled since we moved the authdb replication set master to a seperate database. Previously, the same database was the master for both of our replication sets. With the change, replication set masters are on different databases. With Slony, backups of slave databases contain good data but have a broken schema so doing a plain pg_restore no longer works.

This branch updates the staging database rebuild procedure to repair the authdb replication set schema from a known good source, taken from the production authdb replication set master.

Revision history for this message
Tim Penhey (thumper) wrote :

Looks fine to me.

review: Approve

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'database/replication/Makefile'
2--- database/replication/Makefile 2009-11-03 08:40:02 +0000
3+++ database/replication/Makefile 2009-11-12 03:24:15 +0000
4@@ -90,6 +90,9 @@
5 # with security.py anyway.
6 pg_restore --dbname=lpmain_staging_new \
7 --no-acl --exit-on-error ${STAGING_DUMP}
8+ psql -q -d lpmain_staging_new -f authdb_drop.sql
9+ psql -q -d lpmain_staging_new -f authdb_create.sql \
10+ 2>&1 | grep -v _sl || true
11 # Uninstall Slony-I if it is installed - a pg_dump of a DB with
12 # Slony-I installed isn't usable without this step.
13 LPCONFIG=${NEW_STAGING_CONFIG} ./repair-restored-db.py
14
15=== added file 'database/replication/authdb_create.sql'
16--- database/replication/authdb_create.sql 1970-01-01 00:00:00 +0000
17+++ database/replication/authdb_create.sql 2009-11-12 03:24:15 +0000
18@@ -0,0 +1,832 @@
19+-- Copyright 2009 Canonical Ltd. This software is licensed under the
20+-- GNU Affero General Public License version 3 (see the file LICENSE).
21+
22+-- Generated by:
23+-- pg_dump --format=p --schema-only --no-owner --no-privileges \
24+-- --table=Account --table=AccountPassword --table=AuthToken \
25+-- --table=EmailAddress --table=OpenIDAssociation \
26+-- --table=OpenIDAuthorization --table=OpenIDNonce \
27+-- --table=OpenIDRPSummary --table=ValidPersonCache \
28+-- --table=ValidPersonOrTeamCache launchpad_prod_4
29+
30+--
31+-- PostgreSQL database dump
32+--
33+
34+SET client_encoding = 'UTF8';
35+SET standard_conforming_strings = off;
36+SET check_function_bodies = false;
37+SET client_min_messages = warning;
38+SET escape_string_warning = off;
39+
40+SET search_path = public, pg_catalog;
41+
42+SET default_tablespace = '';
43+
44+SET default_with_oids = false;
45+
46+--
47+-- Name: account; Type: TABLE; Schema: public; Owner: -; Tablespace:
48+--
49+
50+CREATE TABLE account (
51+ id integer NOT NULL,
52+ date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
53+ creation_rationale integer NOT NULL,
54+ status integer NOT NULL,
55+ date_status_set timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
56+ displayname text NOT NULL,
57+ openid_identifier text DEFAULT generate_openid_identifier() NOT NULL,
58+ status_comment text,
59+ old_openid_identifier text
60+);
61+
62+
63+--
64+-- Name: TABLE account; Type: COMMENT; Schema: public; Owner: -
65+--
66+
67+COMMENT ON TABLE account IS 'An account that may be used for authenticating to Canonical or other systems.';
68+
69+
70+--
71+-- Name: COLUMN account.status; Type: COMMENT; Schema: public; Owner: -
72+--
73+
74+COMMENT ON COLUMN account.status IS 'The status of the account.';
75+
76+
77+--
78+-- Name: COLUMN account.date_status_set; Type: COMMENT; Schema: public; Owner: -
79+--
80+
81+COMMENT ON COLUMN account.date_status_set IS 'When the status was last changed.';
82+
83+
84+--
85+-- Name: COLUMN account.displayname; Type: COMMENT; Schema: public; Owner: -
86+--
87+
88+COMMENT ON COLUMN account.displayname IS 'Name to display when rendering information about this account.';
89+
90+
91+--
92+-- Name: COLUMN account.openid_identifier; Type: COMMENT; Schema: public; Owner: -
93+--
94+
95+COMMENT ON COLUMN account.openid_identifier IS 'The key used to construct an OpenID identity URL for this account.';
96+
97+
98+--
99+-- Name: COLUMN account.status_comment; Type: COMMENT; Schema: public; Owner: -
100+--
101+
102+COMMENT ON COLUMN account.status_comment IS 'The comment on the status of the account.';
103+
104+
105+--
106+-- Name: COLUMN account.old_openid_identifier; Type: COMMENT; Schema: public; Owner: -
107+--
108+
109+COMMENT ON COLUMN account.old_openid_identifier IS 'The previous openid_identifier, used for transitions to the current openid_identifier.';
110+
111+
112+--
113+-- Name: account_id_seq; Type: SEQUENCE; Schema: public; Owner: -
114+--
115+
116+CREATE SEQUENCE account_id_seq
117+ INCREMENT BY 1
118+ NO MAXVALUE
119+ NO MINVALUE
120+ CACHE 1;
121+
122+
123+--
124+-- Name: account_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
125+--
126+
127+ALTER SEQUENCE account_id_seq OWNED BY account.id;
128+
129+
130+--
131+-- Name: accountpassword; Type: TABLE; Schema: public; Owner: -; Tablespace:
132+--
133+
134+CREATE TABLE accountpassword (
135+ id integer NOT NULL,
136+ account integer NOT NULL,
137+ password text NOT NULL
138+);
139+
140+
141+--
142+-- Name: TABLE accountpassword; Type: COMMENT; Schema: public; Owner: -
143+--
144+
145+COMMENT ON TABLE accountpassword IS 'A password used to authenticate an Account.';
146+
147+
148+--
149+-- Name: COLUMN accountpassword.password; Type: COMMENT; Schema: public; Owner: -
150+--
151+
152+COMMENT ON COLUMN accountpassword.password IS 'SSHA digest encrypted password.';
153+
154+
155+--
156+-- Name: accountpassword_id_seq; Type: SEQUENCE; Schema: public; Owner: -
157+--
158+
159+CREATE SEQUENCE accountpassword_id_seq
160+ INCREMENT BY 1
161+ NO MAXVALUE
162+ NO MINVALUE
163+ CACHE 1;
164+
165+
166+--
167+-- Name: accountpassword_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
168+--
169+
170+ALTER SEQUENCE accountpassword_id_seq OWNED BY accountpassword.id;
171+
172+
173+--
174+-- Name: authtoken; Type: TABLE; Schema: public; Owner: -; Tablespace:
175+--
176+
177+CREATE TABLE authtoken (
178+ id integer NOT NULL,
179+ date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
180+ date_consumed timestamp without time zone,
181+ token_type integer NOT NULL,
182+ token text NOT NULL,
183+ requester integer,
184+ requester_email text,
185+ email text NOT NULL,
186+ redirection_url text
187+);
188+
189+
190+--
191+-- Name: TABLE authtoken; Type: COMMENT; Schema: public; Owner: -
192+--
193+
194+COMMENT ON TABLE authtoken IS 'AuthToken stores one time tokens used by the authentication service for validating email addresses and other tasks that require verifying an email address is valid such as password recovery and account merging. This table will be cleaned occasionally to remove expired tokens. Expiry time is not yet defined.';
195+
196+
197+--
198+-- Name: COLUMN authtoken.date_created; Type: COMMENT; Schema: public; Owner: -
199+--
200+
201+COMMENT ON COLUMN authtoken.date_created IS 'The timestamp that this request was made.';
202+
203+
204+--
205+-- Name: COLUMN authtoken.date_consumed; Type: COMMENT; Schema: public; Owner: -
206+--
207+
208+COMMENT ON COLUMN authtoken.date_consumed IS 'The date and time when this token was consumed. It''s NULL if it hasn''t been consumed yet.';
209+
210+
211+--
212+-- Name: COLUMN authtoken.token_type; Type: COMMENT; Schema: public; Owner: -
213+--
214+
215+COMMENT ON COLUMN authtoken.token_type IS 'The type of request, as per dbschema.TokenType.';
216+
217+
218+--
219+-- Name: COLUMN authtoken.token; Type: COMMENT; Schema: public; Owner: -
220+--
221+
222+COMMENT ON COLUMN authtoken.token IS 'The token (not the URL) emailed used to uniquely identify this request. This token will be used to generate a URL that when clicked on will continue a workflow.';
223+
224+
225+--
226+-- Name: COLUMN authtoken.requester; Type: COMMENT; Schema: public; Owner: -
227+--
228+
229+COMMENT ON COLUMN authtoken.requester IS 'The Account that made this request. This will be null for password recovery requests.';
230+
231+
232+--
233+-- Name: COLUMN authtoken.requester_email; Type: COMMENT; Schema: public; Owner: -
234+--
235+
236+COMMENT ON COLUMN authtoken.requester_email IS 'The email address that was used to login when making this request. This provides an audit trail to help the end user confirm that this is a valid request. It is not a link to the EmailAddress table as this may be changed after the request is made. This field will be null for password recovery requests.';
237+
238+
239+--
240+-- Name: COLUMN authtoken.email; Type: COMMENT; Schema: public; Owner: -
241+--
242+
243+COMMENT ON COLUMN authtoken.email IS 'The email address that this request was sent to.';
244+
245+
246+--
247+-- Name: authtoken_id_seq; Type: SEQUENCE; Schema: public; Owner: -
248+--
249+
250+CREATE SEQUENCE authtoken_id_seq
251+ INCREMENT BY 1
252+ NO MAXVALUE
253+ NO MINVALUE
254+ CACHE 1;
255+
256+
257+--
258+-- Name: authtoken_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
259+--
260+
261+ALTER SEQUENCE authtoken_id_seq OWNED BY authtoken.id;
262+
263+
264+--
265+-- Name: emailaddress; Type: TABLE; Schema: public; Owner: -; Tablespace:
266+--
267+
268+CREATE TABLE emailaddress (
269+ id integer NOT NULL,
270+ email text NOT NULL,
271+ person integer,
272+ status integer NOT NULL,
273+ date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
274+ account integer,
275+ CONSTRAINT emailaddress__is_linked__chk CHECK (((person IS NOT NULL) OR (account IS NOT NULL)))
276+);
277+
278+
279+--
280+-- Name: COLUMN emailaddress.email; Type: COMMENT; Schema: public; Owner: -
281+--
282+
283+COMMENT ON COLUMN emailaddress.email IS 'An email address used by a Person. The email address is stored in a casesensitive way, but must be case insensitivly unique.';
284+
285+
286+--
287+-- Name: emailaddress_id_seq; Type: SEQUENCE; Schema: public; Owner: -
288+--
289+
290+CREATE SEQUENCE emailaddress_id_seq
291+ INCREMENT BY 1
292+ NO MAXVALUE
293+ NO MINVALUE
294+ CACHE 1;
295+
296+
297+--
298+-- Name: emailaddress_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
299+--
300+
301+ALTER SEQUENCE emailaddress_id_seq OWNED BY emailaddress.id;
302+
303+
304+--
305+-- Name: openidassociation; Type: TABLE; Schema: public; Owner: -; Tablespace:
306+--
307+
308+CREATE TABLE openidassociation (
309+ server_url character varying(2047) NOT NULL,
310+ handle character varying(255) NOT NULL,
311+ secret bytea,
312+ issued integer,
313+ lifetime integer,
314+ assoc_type character varying(64),
315+ CONSTRAINT secret_length_constraint CHECK ((length(secret) <= 128))
316+);
317+
318+
319+--
320+-- Name: openidauthorization; Type: TABLE; Schema: public; Owner: -; Tablespace:
321+--
322+
323+CREATE TABLE openidauthorization (
324+ id integer NOT NULL,
325+ account integer NOT NULL,
326+ client_id text,
327+ date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
328+ date_expires timestamp without time zone NOT NULL,
329+ trust_root text NOT NULL
330+);
331+
332+
333+--
334+-- Name: openidauthorization_id_seq; Type: SEQUENCE; Schema: public; Owner: -
335+--
336+
337+CREATE SEQUENCE openidauthorization_id_seq
338+ INCREMENT BY 1
339+ NO MAXVALUE
340+ NO MINVALUE
341+ CACHE 1;
342+
343+
344+--
345+-- Name: openidauthorization_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
346+--
347+
348+ALTER SEQUENCE openidauthorization_id_seq OWNED BY openidauthorization.id;
349+
350+
351+--
352+-- Name: openidnonce; Type: TABLE; Schema: public; Owner: -; Tablespace:
353+--
354+
355+CREATE TABLE openidnonce (
356+ server_url character varying(2047) NOT NULL,
357+ "timestamp" integer NOT NULL,
358+ salt character(40) NOT NULL
359+);
360+
361+
362+--
363+-- Name: TABLE openidnonce; Type: COMMENT; Schema: public; Owner: -
364+--
365+
366+COMMENT ON TABLE openidnonce IS 'Nonces for our OpenID consumer.';
367+
368+
369+--
370+-- Name: openidrpsummary; Type: TABLE; Schema: public; Owner: -; Tablespace:
371+--
372+
373+CREATE TABLE openidrpsummary (
374+ id integer NOT NULL,
375+ account integer NOT NULL,
376+ openid_identifier text NOT NULL,
377+ trust_root text NOT NULL,
378+ date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
379+ date_last_used timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
380+ total_logins integer DEFAULT 1 NOT NULL
381+);
382+
383+
384+--
385+-- Name: TABLE openidrpsummary; Type: COMMENT; Schema: public; Owner: -
386+--
387+
388+COMMENT ON TABLE openidrpsummary IS 'The summary of the activity between a person and an RP.';
389+
390+
391+--
392+-- Name: COLUMN openidrpsummary.account; Type: COMMENT; Schema: public; Owner: -
393+--
394+
395+COMMENT ON COLUMN openidrpsummary.account IS 'The account who used the RP.';
396+
397+
398+--
399+-- Name: COLUMN openidrpsummary.openid_identifier; Type: COMMENT; Schema: public; Owner: -
400+--
401+
402+COMMENT ON COLUMN openidrpsummary.openid_identifier IS 'The OpenID identifier used to login.';
403+
404+
405+--
406+-- Name: COLUMN openidrpsummary.trust_root; Type: COMMENT; Schema: public; Owner: -
407+--
408+
409+COMMENT ON COLUMN openidrpsummary.trust_root IS 'The trust root for the RP';
410+
411+
412+--
413+-- Name: COLUMN openidrpsummary.date_created; Type: COMMENT; Schema: public; Owner: -
414+--
415+
416+COMMENT ON COLUMN openidrpsummary.date_created IS 'The creation date of this summary; the first time the person used the RP.';
417+
418+
419+--
420+-- Name: COLUMN openidrpsummary.date_last_used; Type: COMMENT; Schema: public; Owner: -
421+--
422+
423+COMMENT ON COLUMN openidrpsummary.date_last_used IS 'The date the RP was last used.';
424+
425+
426+--
427+-- Name: COLUMN openidrpsummary.total_logins; Type: COMMENT; Schema: public; Owner: -
428+--
429+
430+COMMENT ON COLUMN openidrpsummary.total_logins IS 'The total number of times the RP was used by the person.';
431+
432+
433+--
434+-- Name: openidrpsummary_id_seq; Type: SEQUENCE; Schema: public; Owner: -
435+--
436+
437+CREATE SEQUENCE openidrpsummary_id_seq
438+ INCREMENT BY 1
439+ NO MAXVALUE
440+ NO MINVALUE
441+ CACHE 1;
442+
443+
444+--
445+-- Name: openidrpsummary_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
446+--
447+
448+ALTER SEQUENCE openidrpsummary_id_seq OWNED BY openidrpsummary.id;
449+
450+
451+--
452+-- Name: validpersoncache; Type: VIEW; Schema: public; Owner: -
453+--
454+
455+CREATE VIEW validpersoncache AS
456+ SELECT emailaddress.person AS id FROM emailaddress, account WHERE ((((emailaddress.account = account.id) AND (emailaddress.person IS NOT NULL)) AND (emailaddress.status = 4)) AND (account.status = 20));
457+
458+
459+--
460+-- Name: VIEW validpersoncache; Type: COMMENT; Schema: public; Owner: -
461+--
462+
463+COMMENT ON VIEW validpersoncache IS 'A materialized view listing the Person.ids of all valid people (but not teams).';
464+
465+
466+--
467+-- Name: validpersonorteamcache; Type: VIEW; Schema: public; Owner: -
468+--
469+
470+CREATE VIEW validpersonorteamcache AS
471+ SELECT person.id FROM ((person LEFT JOIN emailaddress ON ((person.id = emailaddress.person))) LEFT JOIN account ON ((emailaddress.account = account.id))) WHERE ((person.teamowner IS NOT NULL) OR ((account.status = 20) AND (emailaddress.status = 4)));
472+
473+
474+--
475+-- Name: id; Type: DEFAULT; Schema: public; Owner: -
476+--
477+
478+ALTER TABLE account ALTER COLUMN id SET DEFAULT nextval('account_id_seq'::regclass);
479+
480+
481+--
482+-- Name: id; Type: DEFAULT; Schema: public; Owner: -
483+--
484+
485+ALTER TABLE accountpassword ALTER COLUMN id SET DEFAULT nextval('accountpassword_id_seq'::regclass);
486+
487+
488+--
489+-- Name: id; Type: DEFAULT; Schema: public; Owner: -
490+--
491+
492+ALTER TABLE authtoken ALTER COLUMN id SET DEFAULT nextval('authtoken_id_seq'::regclass);
493+
494+
495+--
496+-- Name: id; Type: DEFAULT; Schema: public; Owner: -
497+--
498+
499+ALTER TABLE emailaddress ALTER COLUMN id SET DEFAULT nextval('emailaddress_id_seq'::regclass);
500+
501+
502+--
503+-- Name: id; Type: DEFAULT; Schema: public; Owner: -
504+--
505+
506+ALTER TABLE openidauthorization ALTER COLUMN id SET DEFAULT nextval('openidauthorization_id_seq'::regclass);
507+
508+
509+--
510+-- Name: id; Type: DEFAULT; Schema: public; Owner: -
511+--
512+
513+ALTER TABLE openidrpsummary ALTER COLUMN id SET DEFAULT nextval('openidrpsummary_id_seq'::regclass);
514+
515+
516+--
517+-- Name: account_openid_identifier_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
518+--
519+
520+ALTER TABLE ONLY account
521+ ADD CONSTRAINT account_openid_identifier_key UNIQUE (openid_identifier);
522+
523+
524+--
525+-- Name: account_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
526+--
527+
528+ALTER TABLE ONLY account
529+ ADD CONSTRAINT account_pkey PRIMARY KEY (id);
530+
531+
532+--
533+-- Name: accountpassword_account_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
534+--
535+
536+ALTER TABLE ONLY accountpassword
537+ ADD CONSTRAINT accountpassword_account_key UNIQUE (account);
538+
539+
540+--
541+-- Name: accountpassword_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
542+--
543+
544+ALTER TABLE ONLY accountpassword
545+ ADD CONSTRAINT accountpassword_pkey PRIMARY KEY (id);
546+
547+
548+--
549+-- Name: authtoken__token__key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
550+--
551+
552+ALTER TABLE ONLY authtoken
553+ ADD CONSTRAINT authtoken__token__key UNIQUE (token);
554+
555+
556+--
557+-- Name: authtoken_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
558+--
559+
560+ALTER TABLE ONLY authtoken
561+ ADD CONSTRAINT authtoken_pkey PRIMARY KEY (id);
562+
563+
564+--
565+-- Name: emailaddress_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
566+--
567+
568+ALTER TABLE ONLY emailaddress
569+ ADD CONSTRAINT emailaddress_pkey PRIMARY KEY (id);
570+
571+
572+--
573+-- Name: openidassociation_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
574+--
575+
576+ALTER TABLE ONLY openidassociation
577+ ADD CONSTRAINT openidassociation_pkey PRIMARY KEY (server_url, handle);
578+
579+
580+--
581+-- Name: openidauthorization_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
582+--
583+
584+ALTER TABLE ONLY openidauthorization
585+ ADD CONSTRAINT openidauthorization_pkey PRIMARY KEY (id);
586+
587+
588+--
589+-- Name: openidnonce_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
590+--
591+
592+ALTER TABLE ONLY openidnonce
593+ ADD CONSTRAINT openidnonce_pkey PRIMARY KEY (server_url, "timestamp", salt);
594+
595+
596+--
597+-- Name: openidrpsummary__account__trust_root__openid_identifier__key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
598+--
599+
600+ALTER TABLE ONLY openidrpsummary
601+ ADD CONSTRAINT openidrpsummary__account__trust_root__openid_identifier__key UNIQUE (account, trust_root, openid_identifier);
602+
603+
604+--
605+-- Name: openidrpsummary_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
606+--
607+
608+ALTER TABLE ONLY openidrpsummary
609+ ADD CONSTRAINT openidrpsummary_pkey PRIMARY KEY (id);
610+
611+
612+--
613+-- Name: account__old_openid_identifier__idx; Type: INDEX; Schema: public; Owner: -; Tablespace:
614+--
615+
616+CREATE INDEX account__old_openid_identifier__idx ON account USING btree (old_openid_identifier);
617+
618+
619+--
620+-- Name: authtoken__date_consumed__idx; Type: INDEX; Schema: public; Owner: -; Tablespace:
621+--
622+
623+CREATE INDEX authtoken__date_consumed__idx ON authtoken USING btree (date_consumed);
624+
625+
626+--
627+-- Name: authtoken__date_created__idx; Type: INDEX; Schema: public; Owner: -; Tablespace:
628+--
629+
630+CREATE INDEX authtoken__date_created__idx ON authtoken USING btree (date_created);
631+
632+
633+--
634+-- Name: authtoken__requester__idx; Type: INDEX; Schema: public; Owner: -; Tablespace:
635+--
636+
637+CREATE INDEX authtoken__requester__idx ON authtoken USING btree (requester);
638+
639+
640+--
641+-- Name: emailaddress__account__key; Type: INDEX; Schema: public; Owner: -; Tablespace:
642+--
643+
644+CREATE UNIQUE INDEX emailaddress__account__key ON emailaddress USING btree (account) WHERE ((status = 4) AND (account IS NOT NULL));
645+
646+
647+--
648+-- Name: INDEX emailaddress__account__key; Type: COMMENT; Schema: public; Owner: -
649+--
650+
651+COMMENT ON INDEX emailaddress__account__key IS 'Ensures that an Account only has one preferred email address';
652+
653+
654+--
655+-- Name: emailaddress__lower_email__key; Type: INDEX; Schema: public; Owner: -; Tablespace:
656+--
657+
658+CREATE INDEX emailaddress__lower_email__key ON emailaddress USING btree (lower(email));
659+
660+
661+--
662+-- Name: emailaddress__person__key; Type: INDEX; Schema: public; Owner: -; Tablespace:
663+--
664+
665+CREATE UNIQUE INDEX emailaddress__person__key ON emailaddress USING btree (person) WHERE ((status = 4) AND (person IS NOT NULL));
666+
667+
668+--
669+-- Name: INDEX emailaddress__person__key; Type: COMMENT; Schema: public; Owner: -
670+--
671+
672+COMMENT ON INDEX emailaddress__person__key IS 'Ensures that a Person only has one preferred email address';
673+
674+
675+--
676+-- Name: emailaddress__person__status__idx; Type: INDEX; Schema: public; Owner: -; Tablespace:
677+--
678+
679+CREATE INDEX emailaddress__person__status__idx ON emailaddress USING btree (person, status);
680+
681+
682+--
683+-- Name: openidauthorixation__account__troot__expires__client_id__idx; Type: INDEX; Schema: public; Owner: -; Tablespace:
684+--
685+
686+CREATE INDEX openidauthorixation__account__troot__expires__client_id__idx ON openidauthorization USING btree (account, trust_root, date_expires, client_id);
687+
688+
689+--
690+-- Name: openidauthorixation__account__trust_root__key; Type: INDEX; Schema: public; Owner: -; Tablespace:
691+--
692+
693+CREATE UNIQUE INDEX openidauthorixation__account__trust_root__key ON openidauthorization USING btree (account, trust_root) WHERE (client_id IS NULL);
694+
695+
696+--
697+-- Name: openidauthorization__account__client_id__trust_root__key; Type: INDEX; Schema: public; Owner: -; Tablespace:
698+--
699+
700+CREATE UNIQUE INDEX openidauthorization__account__client_id__trust_root__key ON openidauthorization USING btree (account, client_id, trust_root) WHERE (client_id IS NOT NULL);
701+
702+
703+--
704+-- Name: openidrpsummary__openid_identifier__idx; Type: INDEX; Schema: public; Owner: -; Tablespace:
705+--
706+
707+CREATE INDEX openidrpsummary__openid_identifier__idx ON openidrpsummary USING btree (openid_identifier);
708+
709+
710+--
711+-- Name: openidrpsummary__trust_root__idx; Type: INDEX; Schema: public; Owner: -; Tablespace:
712+--
713+
714+CREATE INDEX openidrpsummary__trust_root__idx ON openidrpsummary USING btree (trust_root);
715+
716+
717+--
718+-- Name: _sl_logtrigger_200; Type: TRIGGER; Schema: public; Owner: -
719+--
720+
721+CREATE TRIGGER _sl_logtrigger_200
722+ AFTER INSERT OR DELETE OR UPDATE ON account
723+ FOR EACH ROW
724+ EXECUTE PROCEDURE _sl.logtrigger('_sl', '200', 'kvvvvvvvv');
725+
726+
727+--
728+-- Name: _sl_logtrigger_201; Type: TRIGGER; Schema: public; Owner: -
729+--
730+
731+CREATE TRIGGER _sl_logtrigger_201
732+ AFTER INSERT OR DELETE OR UPDATE ON accountpassword
733+ FOR EACH ROW
734+ EXECUTE PROCEDURE _sl.logtrigger('_sl', '201', 'kvv');
735+
736+
737+--
738+-- Name: _sl_logtrigger_274; Type: TRIGGER; Schema: public; Owner: -
739+--
740+
741+CREATE TRIGGER _sl_logtrigger_274
742+ AFTER INSERT OR DELETE OR UPDATE ON emailaddress
743+ FOR EACH ROW
744+ EXECUTE PROCEDURE _sl.logtrigger('_sl', '274', 'kvvvvv');
745+
746+
747+--
748+-- Name: _sl_logtrigger_335; Type: TRIGGER; Schema: public; Owner: -
749+--
750+
751+CREATE TRIGGER _sl_logtrigger_335
752+ AFTER INSERT OR DELETE OR UPDATE ON openidauthorization
753+ FOR EACH ROW
754+ EXECUTE PROCEDURE _sl.logtrigger('_sl', '335', 'kvvvvv');
755+
756+
757+--
758+-- Name: _sl_logtrigger_337; Type: TRIGGER; Schema: public; Owner: -
759+--
760+
761+CREATE TRIGGER _sl_logtrigger_337
762+ AFTER INSERT OR DELETE OR UPDATE ON openidrpsummary
763+ FOR EACH ROW
764+ EXECUTE PROCEDURE _sl.logtrigger('_sl', '337', 'kvvvvvv');
765+
766+
767+--
768+-- Name: _sl_logtrigger_438; Type: TRIGGER; Schema: public; Owner: -
769+--
770+
771+CREATE TRIGGER _sl_logtrigger_438
772+ AFTER INSERT OR DELETE OR UPDATE ON authtoken
773+ FOR EACH ROW
774+ EXECUTE PROCEDURE _sl.logtrigger('_sl', '438', 'kvvvvvvvv');
775+
776+
777+--
778+-- Name: _sl_logtrigger_439; Type: TRIGGER; Schema: public; Owner: -
779+--
780+
781+CREATE TRIGGER _sl_logtrigger_439
782+ AFTER INSERT OR DELETE OR UPDATE ON openidassociation
783+ FOR EACH ROW
784+ EXECUTE PROCEDURE _sl.logtrigger('_sl', '439', 'kkvvvv');
785+
786+
787+--
788+-- Name: _sl_logtrigger_445; Type: TRIGGER; Schema: public; Owner: -
789+--
790+
791+CREATE TRIGGER _sl_logtrigger_445
792+ AFTER INSERT OR DELETE OR UPDATE ON openidnonce
793+ FOR EACH ROW
794+ EXECUTE PROCEDURE _sl.logtrigger('_sl', '445', 'kkk');
795+
796+
797+--
798+-- Name: set_date_status_set_t; Type: TRIGGER; Schema: public; Owner: -
799+--
800+
801+CREATE TRIGGER set_date_status_set_t
802+ BEFORE UPDATE ON account
803+ FOR EACH ROW
804+ EXECUTE PROCEDURE set_date_status_set();
805+
806+
807+--
808+-- Name: accountpassword_account_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
809+--
810+
811+ALTER TABLE ONLY accountpassword
812+ ADD CONSTRAINT accountpassword_account_fkey FOREIGN KEY (account) REFERENCES account(id) ON DELETE CASCADE;
813+
814+
815+--
816+-- Name: authtoken__requester__fk; Type: FK CONSTRAINT; Schema: public; Owner: -
817+--
818+
819+ALTER TABLE ONLY authtoken
820+ ADD CONSTRAINT authtoken__requester__fk FOREIGN KEY (requester) REFERENCES account(id);
821+
822+
823+--
824+-- Name: emailaddress__account__fk; Type: FK CONSTRAINT; Schema: public; Owner: -
825+--
826+
827+ALTER TABLE ONLY emailaddress
828+ ADD CONSTRAINT emailaddress__account__fk FOREIGN KEY (account) REFERENCES account(id) ON DELETE SET NULL;
829+
830+
831+--
832+-- Name: openidauthorization__account__fk; Type: FK CONSTRAINT; Schema: public; Owner: -
833+--
834+
835+ALTER TABLE ONLY openidauthorization
836+ ADD CONSTRAINT openidauthorization__account__fk FOREIGN KEY (account) REFERENCES account(id);
837+
838+
839+--
840+-- Name: openidrpsummary_account_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
841+--
842+
843+ALTER TABLE ONLY openidrpsummary
844+ ADD CONSTRAINT openidrpsummary_account_fkey FOREIGN KEY (account) REFERENCES account(id);
845+
846+
847+--
848+-- PostgreSQL database dump complete
849+--
850+
851
852=== added file 'database/replication/authdb_drop.sql'
853--- database/replication/authdb_drop.sql 1970-01-01 00:00:00 +0000
854+++ database/replication/authdb_drop.sql 2009-11-12 03:24:15 +0000
855@@ -0,0 +1,14 @@
856+-- Copyright 2009 Canonical Ltd. This software is licensed under the
857+-- GNU Affero General Public License version 3 (see the file LICENSE).
858+
859+SET client_min_messages=ERROR;
860+
861+-- Drop everything in the authdb replication set.
862+DROP TABLE IF EXISTS Account CASCADE;
863+DROP TABLE IF EXISTS AccountPassword CASCADE;
864+DROP TABLE IF EXISTS AuthToken CASCADE;
865+DROP TABLE IF EXISTS EmailAddress CASCADE;
866+DROP TABLE IF EXISTS OpenIDAssociation CASCADE;
867+DROP TABLE IF EXISTS OpenIDAuthorization CASCADE;
868+DROP TABLE IF EXISTS OpenIDNonce CASCADE;
869+DROP TABLE IF EXISTS OpenIDRPSummary;