source: subversion/applications/utils/osmosis/trunk/package/script/pgsql_simple_schema_0.6.sql @ 22769

Last change on this file since 22769 was 22769, checked in by bretth, 7 years ago

The current pgsnapshot code still isn't compatible with PostgreSQL 8.3. Making yet another attempt to keep it compatible by eliminating any array->set functionality.

File size: 3.7 KB
Line 
1-- Database creation script for the simple PostgreSQL schema.
2
3-- Drop all tables if they exist.
4DROP TABLE IF EXISTS actions;
5DROP TABLE IF EXISTS users;
6DROP TABLE IF EXISTS nodes;
7DROP TABLE IF EXISTS ways;
8DROP TABLE IF EXISTS way_nodes;
9DROP TABLE IF EXISTS relations;
10DROP TABLE IF EXISTS relation_members;
11DROP TABLE IF EXISTS schema_info;
12
13-- Drop all stored procedures if they exist.
14DROP FUNCTION IF EXISTS osmosisUpdate();
15
16
17-- Create a table which will contain a single row defining the current schema version.
18CREATE TABLE schema_info (
19    version integer NOT NULL
20);
21
22
23-- Create a table for users.
24CREATE TABLE users (
25    id int NOT NULL,
26    name text NOT NULL
27);
28
29
30-- Create a table for nodes.
31CREATE TABLE nodes (
32    id bigint NOT NULL,
33    version int NOT NULL,
34    user_id int NOT NULL,
35    tstamp timestamp without time zone NOT NULL,
36    changeset_id bigint NOT NULL,
37    tags hstore
38);
39-- Add a postgis point column holding the location of the node.
40SELECT AddGeometryColumn('nodes', 'geom', 4326, 'POINT', 2);
41
42
43-- Create a table for ways.
44CREATE TABLE ways (
45    id bigint NOT NULL,
46    version int NOT NULL,
47    user_id int NOT NULL,
48    tstamp timestamp without time zone NOT NULL,
49    changeset_id bigint NOT NULL,
50    tags hstore,
51    nodes bigint[]
52);
53
54
55-- Create a table for representing way to node relationships.
56CREATE TABLE way_nodes (
57    way_id bigint NOT NULL,
58    node_id bigint NOT NULL,
59    sequence_id int NOT NULL
60);
61
62
63-- Create a table for relations.
64CREATE TABLE relations (
65    id bigint NOT NULL,
66    version int NOT NULL,
67    user_id int NOT NULL,
68    tstamp timestamp without time zone NOT NULL,
69    changeset_id bigint NOT NULL,
70    tags hstore
71);
72
73-- Create a table for representing relation member relationships.
74CREATE TABLE relation_members (
75    relation_id bigint NOT NULL,
76    member_id bigint NOT NULL,
77    member_type character(1) NOT NULL,
78    member_role text NOT NULL,
79    sequence_id int NOT NULL
80);
81
82
83-- Configure the schema version.
84INSERT INTO schema_info (version) VALUES (6);
85
86
87-- Add primary keys to tables.
88ALTER TABLE ONLY schema_info ADD CONSTRAINT pk_schema_info PRIMARY KEY (version);
89
90ALTER TABLE ONLY users ADD CONSTRAINT pk_users PRIMARY KEY (id);
91
92ALTER TABLE ONLY nodes ADD CONSTRAINT pk_nodes PRIMARY KEY (id);
93
94ALTER TABLE ONLY ways ADD CONSTRAINT pk_ways PRIMARY KEY (id);
95
96ALTER TABLE ONLY way_nodes ADD CONSTRAINT pk_way_nodes PRIMARY KEY (way_id, sequence_id);
97
98ALTER TABLE ONLY relations ADD CONSTRAINT pk_relations PRIMARY KEY (id);
99
100ALTER TABLE ONLY relation_members ADD CONSTRAINT pk_relation_members PRIMARY KEY (relation_id, sequence_id);
101
102
103-- Add indexes to tables.
104CREATE INDEX idx_nodes_geom ON nodes USING gist (geom);
105
106CREATE INDEX idx_way_nodes_node_id ON way_nodes USING btree (node_id);
107
108CREATE INDEX idx_relation_members_member_id_and_type ON relation_members USING btree (member_id, member_type);
109
110
111-- Cluster tables by geographical location.
112CLUSTER nodes USING idx_nodes_geom;
113
114
115-- Create the function that provides "unnest" functionality while remaining compatible with 8.3.
116CREATE OR REPLACE FUNCTION unnest_bbox_way_nodes() RETURNS void AS $$
117DECLARE
118        previousId ways.id%TYPE;
119        currentId ways.id%TYPE;
120        result bigint[];
121        wayNodeRow way_nodes%ROWTYPE;
122        wayNodes ways.nodes%TYPE;
123BEGIN
124        FOR wayNodes IN SELECT bw.nodes FROM bbox_ways bw LOOP
125                FOR i IN 1 .. array_upper(wayNodes, 1) LOOP
126                        INSERT INTO bbox_way_nodes (id) VALUES (wayNodes[i]);
127                END LOOP;
128        END LOOP;
129END;
130$$ LANGUAGE plpgsql;
131
132
133-- Create customisable hook function that is called within the replication update transaction.
134CREATE FUNCTION osmosisUpdate() RETURNS void AS $$
135DECLARE
136BEGIN
137END;
138$$ LANGUAGE plpgsql;
Note: See TracBrowser for help on using the repository browser.