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

Last change on this file since 723 was 689, checked in by Dominic Hargreaves, 10 years ago

add contact column

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