X-Git-Url: https://git.openstreetmap.org./rails.git/blobdiff_plain/d5f02968f6f2dd153c74844e818a4d2b2ae24991..80a241f7985fec6ec853e48a25823446b59b7682:/db/structure.sql?ds=sidebyside diff --git a/db/structure.sql b/db/structure.sql index 6b34868e1..eac2acaaf 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -2,8 +2,8 @@ -- PostgreSQL database dump -- --- Dumped from database version 9.5.3 --- Dumped by pg_dump version 9.5.3 +-- Dumped from database version 9.5.7 +-- Dumped by pg_dump version 9.5.7 SET statement_timeout = 0; SET lock_timeout = 0; @@ -187,6 +187,18 @@ CREATE SEQUENCE acls_id_seq ALTER SEQUENCE acls_id_seq OWNED BY acls.id; +-- +-- Name: ar_internal_metadata; Type: TABLE; Schema: public; Owner: - +-- + +CREATE TABLE ar_internal_metadata ( + key character varying NOT NULL, + value character varying, + created_at timestamp without time zone NOT NULL, + updated_at timestamp without time zone NOT NULL +); + + -- -- Name: changeset_comments; Type: TABLE; Schema: public; Owner: - -- @@ -550,6 +562,16 @@ CREATE SEQUENCE diary_entries_id_seq ALTER SEQUENCE diary_entries_id_seq OWNED BY diary_entries.id; +-- +-- Name: diary_entry_subscriptions; Type: TABLE; Schema: public; Owner: - +-- + +CREATE TABLE diary_entry_subscriptions ( + user_id bigint NOT NULL, + diary_entry_id bigint NOT NULL +); + + -- -- Name: friends; Type: TABLE; Schema: public; Owner: - -- @@ -664,14 +686,14 @@ ALTER SEQUENCE gpx_files_id_seq OWNED BY gpx_files.id; -- --- Name: issue_comments; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: issue_comments; Type: TABLE; Schema: public; Owner: - -- CREATE TABLE issue_comments ( id integer NOT NULL, - issue_id integer, - commenter_user_id integer, - body text, + issue_id integer NOT NULL, + user_id integer NOT NULL, + body text NOT NULL, created_at timestamp without time zone NOT NULL, updated_at timestamp without time zone NOT NULL ); @@ -697,17 +719,20 @@ ALTER SEQUENCE issue_comments_id_seq OWNED BY issue_comments.id; -- --- Name: issues; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: issues; Type: TABLE; Schema: public; Owner: - -- CREATE TABLE issues ( id integer NOT NULL, reportable_type character varying NOT NULL, reportable_id integer NOT NULL, - reported_user_id integer NOT NULL, + reported_user_id integer, status integer, + assigned_role user_role_enum NOT NULL, resolved_at timestamp without time zone, resolved_by integer, + updated_by integer, + reports_count integer DEFAULT 0, created_at timestamp without time zone NOT NULL, updated_at timestamp without time zone NOT NULL ); @@ -1034,14 +1059,15 @@ CREATE TABLE relations ( -- --- Name: reports; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: reports; Type: TABLE; Schema: public; Owner: - -- CREATE TABLE reports ( id integer NOT NULL, issue_id integer, - reporter_user_id integer, - details text, + user_id integer, + details text NOT NULL, + category character varying NOT NULL, created_at timestamp without time zone NOT NULL, updated_at timestamp without time zone NOT NULL ); @@ -1467,6 +1493,14 @@ ALTER TABLE ONLY acls ADD CONSTRAINT acls_pkey PRIMARY KEY (id); +-- +-- Name: ar_internal_metadata_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY ar_internal_metadata + ADD CONSTRAINT ar_internal_metadata_pkey PRIMARY KEY (key); + + -- -- Name: changeset_comments_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- @@ -1571,6 +1605,14 @@ ALTER TABLE ONLY diary_entries ADD CONSTRAINT diary_entries_pkey PRIMARY KEY (id); +-- +-- Name: diary_entry_subscriptions_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY diary_entry_subscriptions + ADD CONSTRAINT diary_entry_subscriptions_pkey PRIMARY KEY (user_id, diary_entry_id); + + -- -- Name: friends_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- @@ -1596,7 +1638,7 @@ ALTER TABLE ONLY gpx_files -- --- Name: issue_comments_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: issue_comments_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY issue_comments @@ -1604,7 +1646,7 @@ ALTER TABLE ONLY issue_comments -- --- Name: issues_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: issues_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY issues @@ -1708,7 +1750,7 @@ ALTER TABLE ONLY relations -- --- Name: reports_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: reports_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY reports @@ -1976,33 +2018,54 @@ CREATE UNIQUE INDEX index_client_applications_on_key ON client_applications USIN -- --- Name: index_issue_comments_on_commenter_user_id; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: index_client_applications_on_user_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_client_applications_on_user_id ON client_applications USING btree (user_id); + + +-- +-- Name: index_diary_entry_subscriptions_on_diary_entry_id; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX index_issue_comments_on_commenter_user_id ON issue_comments USING btree (commenter_user_id); +CREATE INDEX index_diary_entry_subscriptions_on_diary_entry_id ON diary_entry_subscriptions USING btree (diary_entry_id); -- --- Name: index_issue_comments_on_issue_id; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: index_issue_comments_on_issue_id; Type: INDEX; Schema: public; Owner: - -- CREATE INDEX index_issue_comments_on_issue_id ON issue_comments USING btree (issue_id); -- --- Name: index_issues_on_reportable_id_and_reportable_type; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: index_issue_comments_on_user_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_issue_comments_on_user_id ON issue_comments USING btree (user_id); + + +-- +-- Name: index_issues_on_reportable_id_and_reportable_type; Type: INDEX; Schema: public; Owner: - -- CREATE INDEX index_issues_on_reportable_id_and_reportable_type ON issues USING btree (reportable_id, reportable_type); -- --- Name: index_issues_on_reported_user_id; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: index_issues_on_reported_user_id; Type: INDEX; Schema: public; Owner: - -- CREATE INDEX index_issues_on_reported_user_id ON issues USING btree (reported_user_id); +-- +-- Name: index_issues_on_updated_by; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_issues_on_updated_by ON issues USING btree (updated_by); + + -- -- Name: index_note_comments_on_body; Type: INDEX; Schema: public; Owner: - -- @@ -2032,17 +2095,24 @@ CREATE UNIQUE INDEX index_oauth_tokens_on_token ON oauth_tokens USING btree (tok -- --- Name: index_reports_on_issue_id; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: index_oauth_tokens_on_user_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_oauth_tokens_on_user_id ON oauth_tokens USING btree (user_id); + + +-- +-- Name: index_reports_on_issue_id; Type: INDEX; Schema: public; Owner: - -- CREATE INDEX index_reports_on_issue_id ON reports USING btree (issue_id); -- --- Name: index_reports_on_reporter_user_id; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: index_reports_on_user_id; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX index_reports_on_reporter_user_id ON reports USING btree (reporter_user_id); +CREATE INDEX index_reports_on_user_id ON reports USING btree (user_id); -- @@ -2401,6 +2471,22 @@ ALTER TABLE ONLY diary_entries ADD CONSTRAINT diary_entries_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id); +-- +-- Name: diary_entry_subscriptions_diary_entry_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY diary_entry_subscriptions + ADD CONSTRAINT diary_entry_subscriptions_diary_entry_id_fkey FOREIGN KEY (diary_entry_id) REFERENCES diary_entries(id); + + +-- +-- Name: diary_entry_subscriptions_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY diary_entry_subscriptions + ADD CONSTRAINT diary_entry_subscriptions_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id); + + -- -- Name: friends_friend_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- @@ -2442,19 +2528,19 @@ ALTER TABLE ONLY gpx_files -- --- Name: issue_comments_commenter_user_id; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: issue_comments_issue_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY issue_comments - ADD CONSTRAINT issue_comments_commenter_user_id FOREIGN KEY (commenter_user_id) REFERENCES users(id); + ADD CONSTRAINT issue_comments_issue_id_fkey FOREIGN KEY (issue_id) REFERENCES issues(id) ON DELETE CASCADE; -- --- Name: issue_comments_issue_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: issue_comments_user_id; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY issue_comments - ADD CONSTRAINT issue_comments_issue_id_fkey FOREIGN KEY (issue_id) REFERENCES issues(id); + ADD CONSTRAINT issue_comments_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; -- @@ -2462,7 +2548,15 @@ ALTER TABLE ONLY issue_comments -- ALTER TABLE ONLY issues - ADD CONSTRAINT issues_reported_user_id_fkey FOREIGN KEY (reported_user_id) REFERENCES users(id); + ADD CONSTRAINT issues_reported_user_id_fkey FOREIGN KEY (reported_user_id) REFERENCES users(id) ON DELETE CASCADE; + + +-- +-- Name: issues_updated_by_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY issues + ADD CONSTRAINT issues_updated_by_fkey FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE CASCADE; -- @@ -2582,15 +2676,15 @@ ALTER TABLE ONLY relations -- ALTER TABLE ONLY reports - ADD CONSTRAINT reports_issue_id_fkey FOREIGN KEY (issue_id) REFERENCES issues(id); + ADD CONSTRAINT reports_issue_id_fkey FOREIGN KEY (issue_id) REFERENCES issues(id) ON DELETE CASCADE; -- --- Name: reports_reporter_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: reports_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY reports - ADD CONSTRAINT reports_reporter_user_id_fkey FOREIGN KEY (reporter_user_id) REFERENCES users(id); + ADD CONSTRAINT reports_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; -- @@ -2687,191 +2781,102 @@ ALTER TABLE ONLY ways SET search_path TO "$user", public; -INSERT INTO schema_migrations (version) VALUES ('1'); - -INSERT INTO schema_migrations (version) VALUES ('10'); - -INSERT INTO schema_migrations (version) VALUES ('11'); - -INSERT INTO schema_migrations (version) VALUES ('12'); - -INSERT INTO schema_migrations (version) VALUES ('13'); - -INSERT INTO schema_migrations (version) VALUES ('14'); - -INSERT INTO schema_migrations (version) VALUES ('15'); - -INSERT INTO schema_migrations (version) VALUES ('16'); - -INSERT INTO schema_migrations (version) VALUES ('17'); - -INSERT INTO schema_migrations (version) VALUES ('18'); - -INSERT INTO schema_migrations (version) VALUES ('19'); - -INSERT INTO schema_migrations (version) VALUES ('2'); - -INSERT INTO schema_migrations (version) VALUES ('20'); - -INSERT INTO schema_migrations (version) VALUES ('20100513171259'); - -INSERT INTO schema_migrations (version) VALUES ('20100516124737'); - -INSERT INTO schema_migrations (version) VALUES ('20100910084426'); - -INSERT INTO schema_migrations (version) VALUES ('20101114011429'); - -INSERT INTO schema_migrations (version) VALUES ('20110322001319'); - -INSERT INTO schema_migrations (version) VALUES ('20110508145337'); - -INSERT INTO schema_migrations (version) VALUES ('20110521142405'); - -INSERT INTO schema_migrations (version) VALUES ('20110925112722'); - -INSERT INTO schema_migrations (version) VALUES ('20111116184519'); - -INSERT INTO schema_migrations (version) VALUES ('20111212183945'); - -INSERT INTO schema_migrations (version) VALUES ('20120123184321'); - -INSERT INTO schema_migrations (version) VALUES ('20120208122334'); - -INSERT INTO schema_migrations (version) VALUES ('20120208194454'); - -INSERT INTO schema_migrations (version) VALUES ('20120214210114'); - -INSERT INTO schema_migrations (version) VALUES ('20120219161649'); - -INSERT INTO schema_migrations (version) VALUES ('20120318201948'); - -INSERT INTO schema_migrations (version) VALUES ('20120328090602'); - -INSERT INTO schema_migrations (version) VALUES ('20120404205604'); - -INSERT INTO schema_migrations (version) VALUES ('20120808231205'); - -INSERT INTO schema_migrations (version) VALUES ('20121005195010'); - -INSERT INTO schema_migrations (version) VALUES ('20121012044047'); - -INSERT INTO schema_migrations (version) VALUES ('20121119165817'); - -INSERT INTO schema_migrations (version) VALUES ('20121202155309'); - -INSERT INTO schema_migrations (version) VALUES ('20121203124841'); - -INSERT INTO schema_migrations (version) VALUES ('20130328184137'); - -INSERT INTO schema_migrations (version) VALUES ('20131029121300'); - -INSERT INTO schema_migrations (version) VALUES ('20131212124700'); - -INSERT INTO schema_migrations (version) VALUES ('20140115192822'); - -INSERT INTO schema_migrations (version) VALUES ('20140117185510'); - -INSERT INTO schema_migrations (version) VALUES ('20140210003018'); - -INSERT INTO schema_migrations (version) VALUES ('20140507110937'); - -INSERT INTO schema_migrations (version) VALUES ('20140519141742'); - -INSERT INTO schema_migrations (version) VALUES ('20150110152606'); - -INSERT INTO schema_migrations (version) VALUES ('20150111192335'); - -INSERT INTO schema_migrations (version) VALUES ('20150222101847'); - -INSERT INTO schema_migrations (version) VALUES ('20150516073616'); - -INSERT INTO schema_migrations (version) VALUES ('20150526130032'); - -INSERT INTO schema_migrations (version) VALUES ('21'); - -INSERT INTO schema_migrations (version) VALUES ('22'); - -INSERT INTO schema_migrations (version) VALUES ('23'); - -INSERT INTO schema_migrations (version) VALUES ('24'); - -INSERT INTO schema_migrations (version) VALUES ('25'); - -INSERT INTO schema_migrations (version) VALUES ('26'); - -INSERT INTO schema_migrations (version) VALUES ('27'); - -INSERT INTO schema_migrations (version) VALUES ('28'); - -INSERT INTO schema_migrations (version) VALUES ('29'); - -INSERT INTO schema_migrations (version) VALUES ('3'); - -INSERT INTO schema_migrations (version) VALUES ('30'); - -INSERT INTO schema_migrations (version) VALUES ('31'); - -INSERT INTO schema_migrations (version) VALUES ('32'); - -INSERT INTO schema_migrations (version) VALUES ('33'); - -INSERT INTO schema_migrations (version) VALUES ('34'); - -INSERT INTO schema_migrations (version) VALUES ('35'); - -INSERT INTO schema_migrations (version) VALUES ('36'); - -INSERT INTO schema_migrations (version) VALUES ('37'); - -INSERT INTO schema_migrations (version) VALUES ('38'); - -INSERT INTO schema_migrations (version) VALUES ('39'); - -INSERT INTO schema_migrations (version) VALUES ('4'); - -INSERT INTO schema_migrations (version) VALUES ('40'); - -INSERT INTO schema_migrations (version) VALUES ('41'); - -INSERT INTO schema_migrations (version) VALUES ('42'); - -INSERT INTO schema_migrations (version) VALUES ('43'); - -INSERT INTO schema_migrations (version) VALUES ('44'); - -INSERT INTO schema_migrations (version) VALUES ('45'); - -INSERT INTO schema_migrations (version) VALUES ('46'); - -INSERT INTO schema_migrations (version) VALUES ('47'); - -INSERT INTO schema_migrations (version) VALUES ('48'); - -INSERT INTO schema_migrations (version) VALUES ('49'); - -INSERT INTO schema_migrations (version) VALUES ('5'); - -INSERT INTO schema_migrations (version) VALUES ('50'); - -INSERT INTO schema_migrations (version) VALUES ('51'); - -INSERT INTO schema_migrations (version) VALUES ('52'); - -INSERT INTO schema_migrations (version) VALUES ('53'); - -INSERT INTO schema_migrations (version) VALUES ('54'); - -INSERT INTO schema_migrations (version) VALUES ('55'); - -INSERT INTO schema_migrations (version) VALUES ('56'); - -INSERT INTO schema_migrations (version) VALUES ('57'); - -INSERT INTO schema_migrations (version) VALUES ('6'); - -INSERT INTO schema_migrations (version) VALUES ('7'); - -INSERT INTO schema_migrations (version) VALUES ('8'); +INSERT INTO "schema_migrations" (version) VALUES +('1'), +('10'), +('11'), +('12'), +('13'), +('14'), +('15'), +('16'), +('17'), +('18'), +('19'), +('2'), +('20'), +('20100513171259'), +('20100516124737'), +('20100910084426'), +('20101114011429'), +('20110322001319'), +('20110508145337'), +('20110521142405'), +('20110925112722'), +('20111116184519'), +('20111212183945'), +('20120123184321'), +('20120208122334'), +('20120208194454'), +('20120214210114'), +('20120219161649'), +('20120318201948'), +('20120328090602'), +('20120404205604'), +('20120808231205'), +('20121005195010'), +('20121012044047'), +('20121119165817'), +('20121202155309'), +('20121203124841'), +('20130328184137'), +('20131212124700'), +('20140115192822'), +('20140117185510'), +('20140210003018'), +('20140507110937'), +('20140519141742'), +('20150110152606'), +('20150111192335'), +('20150222101847'), +('20150818224516'), +('20160822153055'), +('20161002153425'), +('20161011010929'), +('20170222134109'), +('21'), +('22'), +('23'), +('24'), +('25'), +('26'), +('27'), +('28'), +('29'), +('3'), +('30'), +('31'), +('32'), +('33'), +('34'), +('35'), +('36'), +('37'), +('38'), +('39'), +('4'), +('40'), +('41'), +('42'), +('43'), +('44'), +('45'), +('46'), +('47'), +('48'), +('49'), +('5'), +('50'), +('51'), +('52'), +('53'), +('54'), +('55'), +('56'), +('57'), +('6'), +('7'), +('8'), +('9'); -INSERT INTO schema_migrations (version) VALUES ('9');