X-Git-Url: https://git.openstreetmap.org/rails.git/blobdiff_plain/d5f02968f6f2dd153c74844e818a4d2b2ae24991..82ac68a26e5faa200952929ae33fb47aab98003b:/db/structure.sql diff --git a/db/structure.sql b/db/structure.sql index 6b34868e1..6abd8c753 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -2,16 +2,12 @@ -- PostgreSQL database dump -- --- Dumped from database version 9.5.3 --- Dumped by pg_dump version 9.5.3 - SET statement_timeout = 0; SET lock_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; -SET row_security = off; -- -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: - @@ -27,20 +23,6 @@ CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; --- --- Name: btree_gist; Type: EXTENSION; Schema: -; Owner: - --- - -CREATE EXTENSION IF NOT EXISTS btree_gist WITH SCHEMA public; - - --- --- Name: EXTENSION btree_gist; Type: COMMENT; Schema: -; Owner: - --- - -COMMENT ON EXTENSION btree_gist IS 'support for indexing common datatypes in GiST'; - - SET search_path = public, pg_catalog; -- @@ -124,33 +106,6 @@ CREATE TYPE user_status_enum AS ENUM ( ); --- --- Name: maptile_for_point(bigint, bigint, integer); Type: FUNCTION; Schema: public; Owner: - --- - -CREATE FUNCTION maptile_for_point(bigint, bigint, integer) RETURNS integer - LANGUAGE c STRICT - AS '$libdir/libpgosm', 'maptile_for_point'; - - --- --- Name: tile_for_point(integer, integer); Type: FUNCTION; Schema: public; Owner: - --- - -CREATE FUNCTION tile_for_point(integer, integer) RETURNS bigint - LANGUAGE c STRICT - AS '$libdir/libpgosm', 'tile_for_point'; - - --- --- Name: xid_to_int4(xid); Type: FUNCTION; Schema: public; Owner: - --- - -CREATE FUNCTION xid_to_int4(xid) RETURNS integer - LANGUAGE c IMMUTABLE STRICT - AS '$libdir/libpgosm', 'xid_to_int4'; - - SET default_tablespace = ''; SET default_with_oids = false; @@ -162,9 +117,9 @@ SET default_with_oids = false; CREATE TABLE acls ( id integer NOT NULL, address inet, - k character varying(255) NOT NULL, - v character varying(255), - domain character varying(255) + k character varying NOT NULL, + v character varying, + domain character varying ); @@ -226,8 +181,8 @@ ALTER SEQUENCE changeset_comments_id_seq OWNED BY changeset_comments.id; CREATE TABLE changeset_tags ( changeset_id bigint NOT NULL, - k character varying(255) DEFAULT ''::character varying NOT NULL, - v character varying(255) DEFAULT ''::character varying NOT NULL + k character varying DEFAULT ''::character varying NOT NULL, + v character varying DEFAULT ''::character varying NOT NULL ); @@ -283,10 +238,10 @@ CREATE TABLE changesets_subscribers ( CREATE TABLE client_applications ( id integer NOT NULL, - name character varying(255), - url character varying(255), - support_url character varying(255), - callback_url character varying(255), + name character varying, + url character varying, + support_url character varying, + callback_url character varying, key character varying(50), secret character varying(50), user_id integer, @@ -327,8 +282,8 @@ ALTER SEQUENCE client_applications_id_seq OWNED BY client_applications.id; CREATE TABLE current_node_tags ( node_id bigint NOT NULL, - k character varying(255) DEFAULT ''::character varying NOT NULL, - v character varying(255) DEFAULT ''::character varying NOT NULL + k character varying DEFAULT ''::character varying NOT NULL, + v character varying DEFAULT ''::character varying NOT NULL ); @@ -375,7 +330,7 @@ CREATE TABLE current_relation_members ( relation_id bigint NOT NULL, member_type nwr_enum NOT NULL, member_id bigint NOT NULL, - member_role character varying(255) NOT NULL, + member_role character varying NOT NULL, sequence_id integer DEFAULT 0 NOT NULL ); @@ -386,8 +341,8 @@ CREATE TABLE current_relation_members ( CREATE TABLE current_relation_tags ( relation_id bigint NOT NULL, - k character varying(255) DEFAULT ''::character varying NOT NULL, - v character varying(255) DEFAULT ''::character varying NOT NULL + k character varying DEFAULT ''::character varying NOT NULL, + v character varying DEFAULT ''::character varying NOT NULL ); @@ -440,8 +395,8 @@ CREATE TABLE current_way_nodes ( CREATE TABLE current_way_tags ( way_id bigint NOT NULL, - k character varying(255) DEFAULT ''::character varying NOT NULL, - v character varying(255) DEFAULT ''::character varying NOT NULL + k character varying DEFAULT ''::character varying NOT NULL, + v character varying DEFAULT ''::character varying NOT NULL ); @@ -519,13 +474,13 @@ ALTER SEQUENCE diary_comments_id_seq OWNED BY diary_comments.id; CREATE TABLE diary_entries ( id bigint NOT NULL, user_id bigint NOT NULL, - title character varying(255) NOT NULL, + title character varying NOT NULL, body text NOT NULL, created_at timestamp without time zone NOT NULL, updated_at timestamp without time zone NOT NULL, latitude double precision, longitude double precision, - language_code character varying(255) DEFAULT 'en'::character varying NOT NULL, + language_code character varying DEFAULT 'en'::character varying NOT NULL, visible boolean DEFAULT true NOT NULL, body_format format_enum DEFAULT 'markdown'::format_enum NOT NULL ); @@ -601,7 +556,7 @@ CREATE TABLE gps_points ( CREATE TABLE gpx_file_tags ( gpx_id bigint DEFAULT 0 NOT NULL, - tag character varying(255) NOT NULL, + tag character varying NOT NULL, id bigint NOT NULL ); @@ -633,12 +588,12 @@ CREATE TABLE gpx_files ( id bigint NOT NULL, user_id bigint NOT NULL, visible boolean DEFAULT true NOT NULL, - name character varying(255) DEFAULT ''::character varying NOT NULL, + name character varying DEFAULT ''::character varying NOT NULL, size bigint, latitude double precision, longitude double precision, "timestamp" timestamp without time zone NOT NULL, - description character varying(255) DEFAULT ''::character varying NOT NULL, + description character varying DEFAULT ''::character varying NOT NULL, inserted boolean NOT NULL, visibility gpx_visibility_enum DEFAULT 'public'::gpx_visibility_enum NOT NULL ); @@ -664,7 +619,7 @@ 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 ( @@ -673,6 +628,7 @@ CREATE TABLE issue_comments ( commenter_user_id integer, body text, created_at timestamp without time zone NOT NULL, + reassign boolean, updated_at timestamp without time zone NOT NULL ); @@ -697,7 +653,7 @@ 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 ( @@ -706,10 +662,13 @@ CREATE TABLE issues ( reportable_id integer NOT NULL, reported_user_id integer NOT NULL, status integer, + issue_type character varying, resolved_at timestamp without time zone, resolved_by integer, created_at timestamp without time zone NOT NULL, - updated_at timestamp without time zone NOT NULL + updated_at timestamp without time zone NOT NULL, + updated_by integer, + report_count integer DEFAULT 0 ); @@ -737,9 +696,9 @@ ALTER SEQUENCE issues_id_seq OWNED BY issues.id; -- CREATE TABLE languages ( - code character varying(255) NOT NULL, - english_name character varying(255) NOT NULL, - native_name character varying(255) + code character varying NOT NULL, + english_name character varying NOT NULL, + native_name character varying ); @@ -750,7 +709,7 @@ CREATE TABLE languages ( CREATE TABLE messages ( id bigint NOT NULL, from_user_id bigint NOT NULL, - title character varying(255) NOT NULL, + title character varying NOT NULL, body text NOT NULL, sent_on timestamp without time zone NOT NULL, message_read boolean DEFAULT false NOT NULL, @@ -787,8 +746,8 @@ ALTER SEQUENCE messages_id_seq OWNED BY messages.id; CREATE TABLE node_tags ( node_id bigint NOT NULL, version bigint NOT NULL, - k character varying(255) DEFAULT ''::character varying NOT NULL, - v character varying(255) DEFAULT ''::character varying NOT NULL + k character varying DEFAULT ''::character varying NOT NULL, + v character varying DEFAULT ''::character varying NOT NULL ); @@ -814,7 +773,7 @@ CREATE TABLE nodes ( -- CREATE TABLE note_comments ( - id integer NOT NULL, + id bigint NOT NULL, note_id bigint NOT NULL, visible boolean NOT NULL, created_at timestamp without time zone NOT NULL, @@ -849,7 +808,7 @@ ALTER SEQUENCE note_comments_id_seq OWNED BY note_comments.id; -- CREATE TABLE notes ( - id integer NOT NULL, + id bigint NOT NULL, latitude integer NOT NULL, longitude integer NOT NULL, tile bigint NOT NULL, @@ -885,7 +844,7 @@ ALTER SEQUENCE notes_id_seq OWNED BY notes.id; CREATE TABLE oauth_nonces ( id integer NOT NULL, - nonce character varying(255), + nonce character varying, "timestamp" integer, created_at timestamp without time zone, updated_at timestamp without time zone @@ -932,9 +891,9 @@ CREATE TABLE oauth_tokens ( allow_write_api boolean DEFAULT false NOT NULL, allow_read_gpx boolean DEFAULT false NOT NULL, allow_write_gpx boolean DEFAULT false NOT NULL, - callback_url character varying(255), + callback_url character varying, verifier character varying(20), - scope character varying(255), + scope character varying, valid_to timestamp without time zone, allow_write_notes boolean DEFAULT false NOT NULL ); @@ -965,10 +924,10 @@ ALTER SEQUENCE oauth_tokens_id_seq OWNED BY oauth_tokens.id; CREATE TABLE redactions ( id integer NOT NULL, - title character varying(255), + title character varying, description text, - created_at timestamp without time zone NOT NULL, - updated_at timestamp without time zone NOT NULL, + created_at timestamp without time zone, + updated_at timestamp without time zone, user_id bigint NOT NULL, description_format format_enum DEFAULT 'markdown'::format_enum NOT NULL ); @@ -1001,7 +960,7 @@ CREATE TABLE relation_members ( relation_id bigint DEFAULT 0 NOT NULL, member_type nwr_enum NOT NULL, member_id bigint NOT NULL, - member_role character varying(255) NOT NULL, + member_role character varying NOT NULL, version bigint DEFAULT 0 NOT NULL, sequence_id integer DEFAULT 0 NOT NULL ); @@ -1013,8 +972,8 @@ CREATE TABLE relation_members ( CREATE TABLE relation_tags ( relation_id bigint DEFAULT 0 NOT NULL, - k character varying(255) DEFAULT ''::character varying NOT NULL, - v character varying(255) DEFAULT ''::character varying NOT NULL, + k character varying DEFAULT ''::character varying NOT NULL, + v character varying DEFAULT ''::character varying NOT NULL, version bigint NOT NULL ); @@ -1034,7 +993,7 @@ CREATE TABLE relations ( -- --- Name: reports; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: reports; Type: TABLE; Schema: public; Owner: - -- CREATE TABLE reports ( @@ -1071,7 +1030,7 @@ ALTER SEQUENCE reports_id_seq OWNED BY reports.id; -- CREATE TABLE schema_migrations ( - version character varying(255) NOT NULL + version character varying NOT NULL ); @@ -1118,8 +1077,8 @@ ALTER SEQUENCE user_blocks_id_seq OWNED BY user_blocks.id; CREATE TABLE user_preferences ( user_id bigint NOT NULL, - k character varying(255) NOT NULL, - v character varying(255) NOT NULL + k character varying NOT NULL, + v character varying NOT NULL ); @@ -1130,9 +1089,9 @@ CREATE TABLE user_preferences ( CREATE TABLE user_roles ( id integer NOT NULL, user_id bigint NOT NULL, + role user_role_enum NOT NULL, created_at timestamp without time zone, updated_at timestamp without time zone, - role user_role_enum NOT NULL, granter_id bigint NOT NULL ); @@ -1163,7 +1122,7 @@ ALTER SEQUENCE user_roles_id_seq OWNED BY user_roles.id; CREATE TABLE user_tokens ( id bigint NOT NULL, user_id bigint NOT NULL, - token character varying(255) NOT NULL, + token character varying NOT NULL, expiry timestamp without time zone NOT NULL, referer text ); @@ -1193,36 +1152,36 @@ ALTER SEQUENCE user_tokens_id_seq OWNED BY user_tokens.id; -- CREATE TABLE users ( - email character varying(255) NOT NULL, + email character varying NOT NULL, id bigint NOT NULL, - pass_crypt character varying(255) NOT NULL, + pass_crypt character varying NOT NULL, creation_time timestamp without time zone NOT NULL, - display_name character varying(255) DEFAULT ''::character varying NOT NULL, + display_name character varying DEFAULT ''::character varying NOT NULL, data_public boolean DEFAULT false NOT NULL, description text DEFAULT ''::text NOT NULL, home_lat double precision, home_lon double precision, home_zoom smallint DEFAULT 3, nearby integer DEFAULT 50, - pass_salt character varying(255), + pass_salt character varying, image_file_name text, email_valid boolean DEFAULT false NOT NULL, - new_email character varying(255), - creation_ip character varying(255), - languages character varying(255), + new_email character varying, + creation_ip character varying, + languages character varying, status user_status_enum DEFAULT 'pending'::user_status_enum NOT NULL, terms_agreed timestamp without time zone, consider_pd boolean DEFAULT false NOT NULL, - preferred_editor character varying(255), + auth_uid character varying, + preferred_editor character varying, terms_seen boolean DEFAULT false NOT NULL, - auth_uid character varying(255), description_format format_enum DEFAULT 'markdown'::format_enum NOT NULL, - image_fingerprint character varying(255), + image_fingerprint character varying, changesets_count integer DEFAULT 0 NOT NULL, traces_count integer DEFAULT 0 NOT NULL, diary_entries_count integer DEFAULT 0 NOT NULL, image_use_gravatar boolean DEFAULT false NOT NULL, - image_content_type character varying(255), + image_content_type character varying, auth_provider character varying ); @@ -1264,8 +1223,8 @@ CREATE TABLE way_nodes ( CREATE TABLE way_tags ( way_id bigint DEFAULT 0 NOT NULL, - k character varying(255) NOT NULL, - v character varying(255) NOT NULL, + k character varying NOT NULL, + v character varying NOT NULL, version bigint NOT NULL ); @@ -1596,7 +1555,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 +1563,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 +1667,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 @@ -1797,7 +1756,7 @@ CREATE INDEX changeset_tags_id_idx ON changeset_tags USING btree (changeset_id); -- Name: changesets_bbox_idx; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX changesets_bbox_idx ON changesets USING gist (min_lat, max_lat, min_lon, max_lon); +CREATE INDEX changesets_bbox_idx ON changesets USING btree (min_lat, max_lat, min_lon, max_lon); -- @@ -1976,33 +1935,40 @@ 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_issue_comments_on_commenter_user_id; Type: INDEX; Schema: public; Owner: - -- CREATE INDEX index_issue_comments_on_commenter_user_id ON issue_comments USING btree (commenter_user_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_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,14 +1998,14 @@ 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_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_reporter_user_id; Type: INDEX; Schema: public; Owner: - -- CREATE INDEX index_reports_on_reporter_user_id ON reports USING btree (reporter_user_id); @@ -2446,7 +2412,7 @@ ALTER TABLE ONLY gpx_files -- ALTER TABLE ONLY issue_comments - ADD CONSTRAINT issue_comments_commenter_user_id FOREIGN KEY (commenter_user_id) REFERENCES users(id); + ADD CONSTRAINT issue_comments_commenter_user_id FOREIGN KEY (commenter_user_id) REFERENCES users(id) ON DELETE CASCADE; -- @@ -2454,7 +2420,7 @@ ALTER TABLE ONLY issue_comments -- ALTER TABLE ONLY issue_comments - ADD CONSTRAINT issue_comments_issue_id_fkey FOREIGN KEY (issue_id) REFERENCES issues(id); + ADD CONSTRAINT issue_comments_issue_id_fkey FOREIGN KEY (issue_id) REFERENCES issues(id) ON DELETE CASCADE; -- @@ -2462,7 +2428,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,7 +2556,7 @@ 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; -- @@ -2590,7 +2564,7 @@ ALTER TABLE ONLY reports -- ALTER TABLE ONLY reports - ADD CONSTRAINT reports_reporter_user_id_fkey FOREIGN KEY (reporter_user_id) REFERENCES users(id); + ADD CONSTRAINT reports_reporter_user_id_fkey FOREIGN KEY (reporter_user_id) REFERENCES users(id) ON DELETE CASCADE; -- @@ -2685,7 +2659,7 @@ ALTER TABLE ONLY ways -- PostgreSQL database dump complete -- -SET search_path TO "$user", public; +SET search_path TO "$user",public; INSERT INTO schema_migrations (version) VALUES ('1'); @@ -2763,8 +2737,6 @@ 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'); @@ -2783,9 +2755,13 @@ 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 ('20150818224516'); + +INSERT INTO schema_migrations (version) VALUES ('20160822153055'); + +INSERT INTO schema_migrations (version) VALUES ('20160822153115'); -INSERT INTO schema_migrations (version) VALUES ('20150526130032'); +INSERT INTO schema_migrations (version) VALUES ('20160822153153'); INSERT INTO schema_migrations (version) VALUES ('21');