source: trunk/npemap.org.uk/schema_postgres.sql @ 665

Last change on this file since 665 was 665, checked in by Nick Burch, 11 years ago

Handy AGGREGATE function

File size: 3.3 KB
Line 
1-- NPE postcode collector schema
2
3
4-- Different Sources of the Postcode
5CREATE SEQUENCE s_sources;
6CREATE TABLE sources (
7        id integer NOT NULL DEFAULT NEXTVAL('s_sources'),
8        name character varying(30) NOT NULL,
9        CONSTRAINT pk_sources PRIMARY KEY (id)
10);
11INSERT INTO sources VALUES (0, 'NPE Postcode web submission');
12INSERT INTO sources VALUES (1, 'FreeThePostcode.org Importer');
13INSERT INTO sources VALUES (2, 'NPEMap Mirror Script');
14INSERT INTO sources VALUES (3, 'Dracos.co.uk Postbox Importer');
15SELECT SETVAL('s_sources',3);
16
17
18-- ID sequence
19--  so we can have a unique way to reference a postcode submission
20CREATE SEQUENCE s_postcodes;
21
22-- Our submitted postcodes
23CREATE TABLE postcodes (
24        id integer NOT NULL DEFAULT NEXTVAL('s_postcodes'),
25    outward character varying(5) NOT NULL,
26    inward character varying(3),
27    raw_postcode_outward character varying(30) NOT NULL,
28    raw_postcode_inward character varying(30),
29
30        -- Constraints ensure we get a matching pair
31    easting integer,
32    northing integer,
33        ie_easting integer,
34        ie_northing integer,
35
36    ip inet,
37    created_at timestamp without time zone NOT NULL default NOW(),
38    source integer NOT NULL,
39    deleted boolean NOT NULL default 'f',
40    delete_reason integer,
41    user_agent character varying,
42
43        CONSTRAINT pk_postcode PRIMARY KEY (id),
44        CONSTRAINT fk_postcode_source FOREIGN KEY (source) REFERENCES sources (id),
45        CONSTRAINT c_postcode_easting CHECK (CASE WHEN easting IS NULL then 1 else 0 END + CASE WHEN ie_easting IS NULL then 1 else 0 END = 1),
46        CONSTRAINT c_postcode_northing CHECK (CASE WHEN northing IS NULL then 1 else 0 END + CASE WHEN ie_northing IS NULL then 1 else 0 END = 1),
47        CONSTRAINT c_postcode_same_en CHECK ( (easting IS NULL AND northing IS NULL) OR (easting IS NOT NULL AND northing IS NOT NULL) )
48);
49
50-- Bad postcode reports
51CREATE TABLE bad_postcodes (
52    postcode integer NOT NULL,
53    ip inet,
54    reason character varying,
55    reporter_email character varying,
56    created_at timestamp without time zone NOT NULL default NOW(),
57    actioned boolean NOT NULL default 'f',
58
59    CONSTRAINT fk_postcode_id FOREIGN KEY (postcode) REFERENCES postcodes (id)
60);
61
62-- Interest in future plans
63CREATE TABLE interest (
64    email character varying,
65    scotland boolean default false,
66    northernireland boolean default false,
67    ip inet,
68    created_at timestamp without time zone NOT NULL default NOW()
69);
70
71CREATE sequence s_delete_reasons;
72
73-- Reasons for deletion
74CREATE TABLE delete_reasons (
75    id integer NOT NULL DEFAULT NEXTVAL('s_delete_reasons'),
76    reason character varying
77);
78
79-- Districts
80CREATE TABLE districts (
81    outward character varying(4) NOT NULL,
82    district character varying,
83    county character varying,
84
85    CONSTRAINT pk_district PRIMARY KEY (outward)
86);
87
88CREATE INDEX postcode_easting ON postcodes (easting);
89CREATE INDEX postcode_northing ON postcodes (northing);
90CREATE INDEX postcode_outward ON postcodes (outward);
91CREATE INDEX postcode_inward ON postcodes (inward);
92CREATE INDEX postcode_postcode ON postcodes (outward,inward);
93
94CREATE INDEX i_postcode_source ON postcodes (source);
95CREATE INDEX i_bad_postcodes_postcode ON bad_postcodes (postcode);
96
97-- So we can do fancy stuff
98CREATE AGGREGATE to_array (
99  sfunc = array_append,
100  basetype = anyelement,
101  stype = anyarray,
102  initcond = '{}'
103);
Note: See TracBrowser for help on using the repository browser.