root/trunk/wifidog-auth/sql/wifidog-postgres-schema.sql @ 872

Revision 872, 29.2 KB (checked in by fproulx, 9 years ago)

2005-12-26 Fran�ois Proulx <francois.proulx@…>

  • Tiny modifications to config.php
  • Reinserted the deprecated defines, created errors on my system
  • Fully translated FormSelectGenerator? class, Locale
  • Added comments to Utils, Locale etc..
  • Fully tested installation from scratch using blank database with installation script
  • Improved installation script a bit
  • Synched SQL for CVS schema 29 --> 33
  • Property svn:eol-style set to native
  • Property svn:keywords set to Author Date Id Revision
Line 
1--
2-- PostgreSQL database dump
3--
4
5SET client_encoding = 'UNICODE';
6SET check_function_bodies = false;
7SET client_min_messages = warning;
8
9--
10-- Name: wifidog; Type: DATABASE; Schema: -; Owner: wifidog
11--
12
13CREATE DATABASE wifidog WITH TEMPLATE = template0 ENCODING = 'UNICODE';
14
15
16\connect wifidog
17
18SET client_encoding = 'UNICODE';
19SET check_function_bodies = false;
20SET client_min_messages = warning;
21
22--
23-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
24--
25
26COMMENT ON SCHEMA public IS 'Standard public schema';
27
28
29SET search_path = public, pg_catalog;
30
31SET default_tablespace = '';
32
33SET default_with_oids = true;
34
35--
36-- Name: administrators; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
37--
38
39CREATE TABLE administrators (
40    user_id character varying(45) DEFAULT ''::character varying NOT NULL
41);
42
43
44--
45-- Name: connections; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
46--
47
48CREATE TABLE connections (
49    conn_id serial NOT NULL,
50    token character varying(32) DEFAULT ''::character varying NOT NULL,
51    token_status character varying(10) DEFAULT 'UNUSED'::character varying NOT NULL,
52    timestamp_in timestamp without time zone,
53    node_id character varying(32),
54    node_ip character varying(15),
55    timestamp_out timestamp without time zone,
56    user_id character varying(45) DEFAULT ''::character varying NOT NULL,
57    user_mac character varying(18),
58    user_ip character varying(16),
59    last_updated timestamp without time zone NOT NULL,
60    incoming bigint,
61    outgoing bigint
62);
63
64
65--
66-- Name: content; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
67--
68
69CREATE TABLE content (
70    content_id text NOT NULL,
71    content_type text NOT NULL,
72    title text,
73    description text,
74    project_info text,
75    sponsor_info text,
76    creation_timestamp timestamp without time zone DEFAULT now(),
77    is_persistent boolean DEFAULT false,
78    long_description text,
79    CONSTRAINT content_type_not_empty_string CHECK ((content_type <> ''::text))
80);
81
82
83--
84-- Name: content_display_location; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
85--
86
87CREATE TABLE content_display_location (
88    display_location text NOT NULL
89);
90
91
92--
93-- Name: content_display_log; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
94--
95
96CREATE TABLE content_display_log (
97    user_id text NOT NULL,
98    content_id text NOT NULL,
99    first_display_timestamp timestamp without time zone DEFAULT now() NOT NULL,
100    node_id text NOT NULL,
101    last_display_timestamp timestamp without time zone DEFAULT now() NOT NULL
102);
103
104
105--
106-- Name: content_group; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
107--
108
109CREATE TABLE content_group (
110    content_group_id text NOT NULL,
111    is_artistic_content boolean DEFAULT false NOT NULL,
112    is_locative_content boolean DEFAULT false NOT NULL,
113    content_changes_on_mode text DEFAULT 'ALWAYS'::text NOT NULL,
114    content_ordering_mode text DEFAULT 'RANDOM'::text NOT NULL,
115    display_num_elements integer DEFAULT 1 NOT NULL,
116    allow_repeat text DEFAULT 'YES'::text NOT NULL,
117    CONSTRAINT display_at_least_one_element CHECK ((display_num_elements > 0))
118);
119
120
121--
122-- Name: content_group_element; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
123--
124
125CREATE TABLE content_group_element (
126    content_group_element_id text NOT NULL,
127    content_group_id text NOT NULL,
128    display_order integer DEFAULT 1,
129    displayed_content_id text,
130    force_only_allowed_node boolean
131);
132
133
134--
135-- Name: content_group_element_has_allowed_nodes; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
136--
137
138CREATE TABLE content_group_element_has_allowed_nodes (
139    content_group_element_id text NOT NULL,
140    node_id text NOT NULL,
141    allowed_since timestamp without time zone DEFAULT now()
142);
143
144
145--
146-- Name: content_has_owners; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
147--
148
149CREATE TABLE content_has_owners (
150    content_id text NOT NULL,
151    user_id text NOT NULL,
152    is_author boolean DEFAULT false NOT NULL,
153    owner_since timestamp without time zone DEFAULT now()
154);
155
156
157--
158-- Name: content_rss_aggregator; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
159--
160
161CREATE TABLE content_rss_aggregator (
162    content_id text NOT NULL,
163    number_of_display_items integer DEFAULT 10 NOT NULL,
164    algorithm_strength real DEFAULT 0.75 NOT NULL,
165    max_item_age interval
166);
167
168
169--
170-- Name: content_rss_aggregator_feeds; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
171--
172
173CREATE TABLE content_rss_aggregator_feeds (
174    content_id text NOT NULL,
175    url text NOT NULL,
176    bias real DEFAULT 1 NOT NULL,
177    default_publication_interval integer,
178    title text
179);
180
181
182--
183-- Name: embedded_content; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
184--
185
186CREATE TABLE embedded_content (
187    embedded_content_id text NOT NULL,
188    embedded_file_id text,
189    fallback_content_id text,
190    parameters text,
191    attributes text
192);
193
194
195--
196-- Name: files; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
197--
198
199CREATE TABLE files (
200    files_id text NOT NULL,
201    filename text,
202    mime_type text,
203    remote_size bigint,
204    url text,
205    data_blob oid,
206    local_binary_size bigint
207);
208
209
210--
211-- Name: flickr_photostream; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
212--
213
214CREATE TABLE flickr_photostream (
215    flickr_photostream_id text NOT NULL,
216    api_key text,
217    photo_selection_mode text DEFAULT 'PSM_GROUP'::text NOT NULL,
218    user_id text,
219    user_name text,
220    tags text,
221    tag_mode character varying(10) DEFAULT 'any'::character varying,
222    group_id text,
223    random boolean DEFAULT true NOT NULL,
224    min_taken_date timestamp without time zone,
225    max_taken_date timestamp without time zone,
226    photo_batch_size integer DEFAULT 10,
227    photo_count integer DEFAULT 1,
228    display_title boolean DEFAULT true NOT NULL,
229    display_description boolean DEFAULT false NOT NULL,
230    display_tags boolean DEFAULT false NOT NULL,
231    preferred_size text,
232    requests_cache text,
233    cache_update_timestamp timestamp without time zone,
234    api_shared_secret text,
235    photo_display_mode text DEFAULT 'PDM_GRID'::text NOT NULL
236);
237
238
239--
240-- Name: iframes; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
241--
242
243CREATE TABLE iframes (
244    iframes_id text NOT NULL,
245    url text,
246    width integer,
247    height integer
248);
249
250
251--
252-- Name: langstring_entries; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
253--
254
255CREATE TABLE langstring_entries (
256    langstring_entries_id text NOT NULL,
257    langstrings_id text,
258    locales_id text,
259    value text DEFAULT ''::text
260);
261
262
263--
264-- Name: locales; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
265--
266
267CREATE TABLE locales (
268    locales_id text NOT NULL
269);
270
271
272--
273-- Name: network_has_content; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
274--
275
276CREATE TABLE network_has_content (
277    network_id text NOT NULL,
278    content_id text NOT NULL,
279    subscribe_timestamp timestamp without time zone DEFAULT now() NOT NULL,
280    display_location text DEFAULT 'portal_page'::text NOT NULL
281);
282
283
284--
285-- Name: network_stakeholders; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
286--
287
288CREATE TABLE network_stakeholders (
289    network_id text NOT NULL,
290    user_id character varying(45) NOT NULL,
291    is_admin boolean DEFAULT false NOT NULL,
292    is_stat_viewer boolean DEFAULT false NOT NULL
293);
294
295
296--
297-- Name: networks; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
298--
299
300CREATE TABLE networks (
301    network_id text NOT NULL,
302    network_authenticator_class text NOT NULL,
303    network_authenticator_params text,
304    is_default_network boolean DEFAULT false NOT NULL,
305    name text DEFAULT 'Unnamed network'::text NOT NULL,
306    creation_date date DEFAULT now() NOT NULL,
307    homepage_url text,
308    tech_support_email text,
309    validation_grace_time interval DEFAULT '00:20:00'::interval NOT NULL,
310    validation_email_from_address text DEFAULT 'validation@wifidognetwork'::text NOT NULL,
311    allow_multiple_login boolean DEFAULT false NOT NULL,
312    allow_splash_only_nodes boolean DEFAULT false NOT NULL,
313    allow_custom_portal_redirect boolean DEFAULT false NOT NULL,
314    CONSTRAINT networks_name CHECK ((name <> ''::text)),
315    CONSTRAINT networks_network_authenticator_class CHECK ((network_authenticator_class <> ''::text)),
316    CONSTRAINT networks_validation_email_from_address CHECK ((validation_email_from_address <> ''::text))
317);
318
319
320--
321-- Name: node_deployment_status; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
322--
323
324CREATE TABLE node_deployment_status (
325    node_deployment_status character varying(32) NOT NULL
326);
327
328
329--
330-- Name: node_has_content; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
331--
332
333CREATE TABLE node_has_content (
334    node_id text NOT NULL,
335    content_id text NOT NULL,
336    subscribe_timestamp timestamp without time zone DEFAULT now() NOT NULL,
337    display_location text DEFAULT 'portal_page'::text NOT NULL
338);
339
340
341--
342-- Name: node_stakeholders; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
343--
344
345CREATE TABLE node_stakeholders (
346    node_id character varying(32) NOT NULL,
347    user_id character varying(45) NOT NULL,
348    is_owner boolean DEFAULT false NOT NULL,
349    is_tech_officer boolean DEFAULT false NOT NULL
350);
351
352
353--
354-- Name: nodes; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
355--
356
357CREATE TABLE nodes (
358    node_id character varying(32) DEFAULT ''::character varying NOT NULL,
359    name text,
360    last_heartbeat_ip character varying(16),
361    last_heartbeat_timestamp timestamp without time zone DEFAULT now(),
362    creation_date date DEFAULT now(),
363    home_page_url text,
364    last_heartbeat_user_agent text,
365    description text,
366    map_url text,
367    public_phone_number text,
368    public_email text,
369    mass_transit_info text,
370    node_deployment_status character varying(32) DEFAULT 'IN_PLANNING'::character varying NOT NULL,
371    venue_type text DEFAULT 'Other'::text,
372    max_monthly_incoming bigint,
373    max_monthly_outgoing bigint,
374    quota_reset_day_of_month integer,
375    latitude numeric(16,6),
376    longitude numeric(16,6),
377    civic_number text,
378    street_name text,
379    city text,
380    province text,
381    country text,
382    postal_code text,
383    network_id text NOT NULL,
384    last_paged timestamp without time zone,
385    is_splash_only_node boolean DEFAULT false,
386    custom_portal_redirect_url text
387);
388
389
390--
391-- Name: pictures; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
392--
393
394CREATE TABLE pictures (
395    pictures_id text NOT NULL,
396    width integer,
397    height integer
398);
399
400
401--
402-- Name: schema_info; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
403--
404
405CREATE TABLE schema_info (
406    tag text NOT NULL,
407    value text
408);
409
410
411--
412-- Name: token_status; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
413--
414
415CREATE TABLE token_status (
416    token_status character varying(10) NOT NULL
417);
418
419
420--
421-- Name: user_has_content; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
422--
423
424CREATE TABLE user_has_content (
425    user_id text NOT NULL,
426    content_id text NOT NULL,
427    subscribe_timestamp timestamp without time zone DEFAULT now() NOT NULL
428);
429
430
431--
432-- Name: users; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
433--
434
435CREATE TABLE users (
436    user_id character varying(45) NOT NULL,
437    pass character varying(32) DEFAULT ''::character varying NOT NULL,
438    email character varying(255) DEFAULT ''::character varying NOT NULL,
439    account_status integer,
440    validation_token character varying(64) DEFAULT ''::character varying NOT NULL,
441    reg_date timestamp without time zone DEFAULT now() NOT NULL,
442    username text,
443    account_origin text NOT NULL,
444    never_show_username boolean DEFAULT false,
445    real_name text,
446    website text,
447    prefered_locale text,
448    CONSTRAINT check_account_origin_not_empty CHECK ((account_origin <> ''::text)),
449    CONSTRAINT check_user_not_empty CHECK (((user_id)::text <> ''::text))
450);
451
452
453--
454-- Name: venue_types; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
455--
456
457CREATE TABLE venue_types (
458    venue_type text NOT NULL
459);
460
461
462--
463-- Name: venues; Type: TABLE; Schema: public; Owner: wifidog; Tablespace:
464--
465
466CREATE TABLE venues (
467    name text NOT NULL,
468    description text
469);
470
471
472--
473-- Name: administrators_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
474--
475
476ALTER TABLE ONLY administrators
477    ADD CONSTRAINT administrators_pkey PRIMARY KEY (user_id);
478
479
480--
481-- Name: connections_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
482--
483
484ALTER TABLE ONLY connections
485    ADD CONSTRAINT connections_pkey PRIMARY KEY (conn_id);
486
487
488--
489-- Name: content_display_location_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
490--
491
492ALTER TABLE ONLY content_display_location
493    ADD CONSTRAINT content_display_location_pkey PRIMARY KEY (display_location);
494
495
496--
497-- Name: content_group_element_has_allowed_nodes_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
498--
499
500ALTER TABLE ONLY content_group_element_has_allowed_nodes
501    ADD CONSTRAINT content_group_element_has_allowed_nodes_pkey PRIMARY KEY (content_group_element_id, node_id);
502
503
504--
505-- Name: content_group_element_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
506--
507
508ALTER TABLE ONLY content_group_element
509    ADD CONSTRAINT content_group_element_pkey PRIMARY KEY (content_group_element_id);
510
511
512--
513-- Name: content_group_element_portal_display_log_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
514--
515
516ALTER TABLE ONLY content_display_log
517    ADD CONSTRAINT content_group_element_portal_display_log_pkey PRIMARY KEY (user_id, content_id, node_id);
518
519
520--
521-- Name: content_group_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
522--
523
524ALTER TABLE ONLY content_group
525    ADD CONSTRAINT content_group_pkey PRIMARY KEY (content_group_id);
526
527
528--
529-- Name: content_has_owners_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
530--
531
532ALTER TABLE ONLY content_has_owners
533    ADD CONSTRAINT content_has_owners_pkey PRIMARY KEY (content_id, user_id);
534
535
536--
537-- Name: content_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
538--
539
540ALTER TABLE ONLY content
541    ADD CONSTRAINT content_pkey PRIMARY KEY (content_id);
542
543
544--
545-- Name: content_rss_aggregator_feeds_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
546--
547
548ALTER TABLE ONLY content_rss_aggregator_feeds
549    ADD CONSTRAINT content_rss_aggregator_feeds_pkey PRIMARY KEY (content_id, url);
550
551
552--
553-- Name: content_rss_aggregator_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
554--
555
556ALTER TABLE ONLY content_rss_aggregator
557    ADD CONSTRAINT content_rss_aggregator_pkey PRIMARY KEY (content_id);
558
559
560--
561-- Name: files_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
562--
563
564ALTER TABLE ONLY files
565    ADD CONSTRAINT files_pkey PRIMARY KEY (files_id);
566
567
568--
569-- Name: flickr_photostream_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
570--
571
572ALTER TABLE ONLY flickr_photostream
573    ADD CONSTRAINT flickr_photostream_pkey PRIMARY KEY (flickr_photostream_id);
574
575
576--
577-- Name: iframes_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
578--
579
580ALTER TABLE ONLY iframes
581    ADD CONSTRAINT iframes_pkey PRIMARY KEY (iframes_id);
582
583
584--
585-- Name: langstring_entries_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
586--
587
588ALTER TABLE ONLY langstring_entries
589    ADD CONSTRAINT langstring_entries_pkey PRIMARY KEY (langstring_entries_id);
590
591
592--
593-- Name: locales_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
594--
595
596ALTER TABLE ONLY locales
597    ADD CONSTRAINT locales_pkey PRIMARY KEY (locales_id);
598
599
600--
601-- Name: network_has_content_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
602--
603
604ALTER TABLE ONLY network_has_content
605    ADD CONSTRAINT network_has_content_pkey PRIMARY KEY (network_id, content_id);
606
607
608--
609-- Name: network_stakeholders_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
610--
611
612ALTER TABLE ONLY network_stakeholders
613    ADD CONSTRAINT network_stakeholders_pkey PRIMARY KEY (network_id, user_id);
614
615
616--
617-- Name: networks_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
618--
619
620ALTER TABLE ONLY networks
621    ADD CONSTRAINT networks_pkey PRIMARY KEY (network_id);
622
623
624--
625-- Name: node_deployment_status_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
626--
627
628ALTER TABLE ONLY node_deployment_status
629    ADD CONSTRAINT node_deployment_status_pkey PRIMARY KEY (node_deployment_status);
630
631
632--
633-- Name: node_has_content_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
634--
635
636ALTER TABLE ONLY node_has_content
637    ADD CONSTRAINT node_has_content_pkey PRIMARY KEY (node_id, content_id);
638
639
640--
641-- Name: node_stakeholders_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
642--
643
644ALTER TABLE ONLY node_stakeholders
645    ADD CONSTRAINT node_stakeholders_pkey PRIMARY KEY (node_id, user_id);
646
647
648--
649-- Name: nodes_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
650--
651
652ALTER TABLE ONLY nodes
653    ADD CONSTRAINT nodes_pkey PRIMARY KEY (node_id);
654
655
656--
657-- Name: pictures_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
658--
659
660ALTER TABLE ONLY pictures
661    ADD CONSTRAINT pictures_pkey PRIMARY KEY (pictures_id);
662
663
664--
665-- Name: schema_info_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
666--
667
668ALTER TABLE ONLY schema_info
669    ADD CONSTRAINT schema_info_pkey PRIMARY KEY (tag);
670
671
672--
673-- Name: token_status_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
674--
675
676ALTER TABLE ONLY token_status
677    ADD CONSTRAINT token_status_pkey PRIMARY KEY (token_status);
678
679
680--
681-- Name: user_has_content_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
682--
683
684ALTER TABLE ONLY user_has_content
685    ADD CONSTRAINT user_has_content_pkey PRIMARY KEY (user_id, content_id);
686
687
688--
689-- Name: users_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
690--
691
692ALTER TABLE ONLY users
693    ADD CONSTRAINT users_pkey PRIMARY KEY (user_id);
694
695
696--
697-- Name: venue_types_pkey; Type: CONSTRAINT; Schema: public; Owner: wifidog; Tablespace:
698--
699
700ALTER TABLE ONLY venue_types
701    ADD CONSTRAINT venue_types_pkey PRIMARY KEY (venue_type);
702
703
704--
705-- Name: idx_connections_node_id; Type: INDEX; Schema: public; Owner: wifidog; Tablespace:
706--
707
708CREATE INDEX idx_connections_node_id ON connections USING btree (node_id);
709
710
711--
712-- Name: idx_connections_user_id; Type: INDEX; Schema: public; Owner: wifidog; Tablespace:
713--
714
715CREATE INDEX idx_connections_user_id ON connections USING btree (user_id);
716
717
718--
719-- Name: idx_connections_user_mac; Type: INDEX; Schema: public; Owner: wifidog; Tablespace:
720--
721
722CREATE INDEX idx_connections_user_mac ON connections USING btree (user_mac);
723
724
725--
726-- Name: idx_content_group_element_content_group_id; Type: INDEX; Schema: public; Owner: wifidog; Tablespace:
727--
728
729CREATE INDEX idx_content_group_element_content_group_id ON content_group_element USING btree (content_group_id);
730
731
732--
733-- Name: idx_token; Type: INDEX; Schema: public; Owner: wifidog; Tablespace:
734--
735
736CREATE INDEX idx_token ON connections USING btree (token);
737
738
739--
740-- Name: idx_token_status_and_user_id; Type: INDEX; Schema: public; Owner: wifidog; Tablespace:
741--
742
743CREATE INDEX idx_token_status_and_user_id ON connections USING btree (token_status, user_id);
744
745
746--
747-- Name: idx_unique_username_and_account_origin; Type: INDEX; Schema: public; Owner: wifidog; Tablespace:
748--
749
750CREATE UNIQUE INDEX idx_unique_username_and_account_origin ON users USING btree (username, account_origin);
751
752
753--
754-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
755--
756
757ALTER TABLE ONLY connections
758    ADD CONSTRAINT "$1" FOREIGN KEY (token_status) REFERENCES token_status(token_status);
759
760
761--
762-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
763--
764
765ALTER TABLE ONLY users
766    ADD CONSTRAINT "$1" FOREIGN KEY (prefered_locale) REFERENCES locales(locales_id) ON UPDATE CASCADE ON DELETE SET NULL;
767
768
769--
770-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
771--
772
773ALTER TABLE ONLY content
774    ADD CONSTRAINT "$1" FOREIGN KEY (title) REFERENCES content(content_id) ON UPDATE CASCADE ON DELETE RESTRICT;
775
776
777--
778-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
779--
780
781ALTER TABLE ONLY content_has_owners
782    ADD CONSTRAINT "$1" FOREIGN KEY (content_id) REFERENCES content(content_id) ON UPDATE CASCADE ON DELETE CASCADE;
783
784
785--
786-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
787--
788
789ALTER TABLE ONLY langstring_entries
790    ADD CONSTRAINT "$1" FOREIGN KEY (langstrings_id) REFERENCES content(content_id) ON UPDATE CASCADE ON DELETE CASCADE;
791
792
793--
794-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
795--
796
797ALTER TABLE ONLY content_group
798    ADD CONSTRAINT "$1" FOREIGN KEY (content_group_id) REFERENCES content(content_id) ON UPDATE CASCADE ON DELETE CASCADE;
799
800
801--
802-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
803--
804
805ALTER TABLE ONLY content_group_element
806    ADD CONSTRAINT "$1" FOREIGN KEY (content_group_element_id) REFERENCES content(content_id) ON UPDATE CASCADE ON DELETE CASCADE;
807
808
809--
810-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
811--
812
813ALTER TABLE ONLY content_group_element_has_allowed_nodes
814    ADD CONSTRAINT "$1" FOREIGN KEY (content_group_element_id) REFERENCES content_group_element(content_group_element_id) ON UPDATE CASCADE ON DELETE CASCADE;
815
816
817--
818-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
819--
820
821ALTER TABLE ONLY user_has_content
822    ADD CONSTRAINT "$1" FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;
823
824
825--
826-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
827--
828
829ALTER TABLE ONLY node_has_content
830    ADD CONSTRAINT "$1" FOREIGN KEY (node_id) REFERENCES nodes(node_id) ON UPDATE CASCADE ON DELETE CASCADE;
831
832
833--
834-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
835--
836
837ALTER TABLE ONLY network_has_content
838    ADD CONSTRAINT "$1" FOREIGN KEY (content_id) REFERENCES content(content_id) ON UPDATE CASCADE ON DELETE CASCADE;
839
840
841--
842-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
843--
844
845ALTER TABLE ONLY content_display_log
846    ADD CONSTRAINT "$1" FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;
847
848
849--
850-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
851--
852
853ALTER TABLE ONLY pictures
854    ADD CONSTRAINT "$1" FOREIGN KEY (pictures_id) REFERENCES files(files_id) ON UPDATE CASCADE ON DELETE CASCADE;
855
856
857--
858-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
859--
860
861ALTER TABLE ONLY iframes
862    ADD CONSTRAINT "$1" FOREIGN KEY (iframes_id) REFERENCES content(content_id) ON UPDATE CASCADE ON DELETE CASCADE;
863
864
865--
866-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
867--
868
869ALTER TABLE ONLY content_rss_aggregator
870    ADD CONSTRAINT "$1" FOREIGN KEY (content_id) REFERENCES content(content_id) ON UPDATE CASCADE ON DELETE CASCADE;
871
872
873--
874-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
875--
876
877ALTER TABLE ONLY content_rss_aggregator_feeds
878    ADD CONSTRAINT "$1" FOREIGN KEY (content_id) REFERENCES content_rss_aggregator(content_id) ON UPDATE CASCADE ON DELETE CASCADE;
879
880
881--
882-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
883--
884
885ALTER TABLE ONLY node_stakeholders
886    ADD CONSTRAINT "$1" FOREIGN KEY (node_id) REFERENCES nodes(node_id);
887
888
889--
890-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
891--
892
893ALTER TABLE ONLY network_stakeholders
894    ADD CONSTRAINT "$1" FOREIGN KEY (network_id) REFERENCES networks(network_id);
895
896
897--
898-- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
899--
900
901ALTER TABLE ONLY content
902    ADD CONSTRAINT "$2" FOREIGN KEY (description) REFERENCES content(content_id) ON UPDATE CASCADE ON DELETE RESTRICT;
903
904
905--
906-- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
907--
908
909ALTER TABLE ONLY content_has_owners
910    ADD CONSTRAINT "$2" FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;
911
912
913--
914-- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
915--
916
917ALTER TABLE ONLY langstring_entries
918    ADD CONSTRAINT "$2" FOREIGN KEY (locales_id) REFERENCES locales(locales_id) ON UPDATE CASCADE ON DELETE RESTRICT;
919
920
921--
922-- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
923--
924
925ALTER TABLE ONLY content_group_element
926    ADD CONSTRAINT "$2" FOREIGN KEY (content_group_id) REFERENCES content_group(content_group_id) ON UPDATE CASCADE ON DELETE CASCADE;
927
928
929--
930-- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
931--
932
933ALTER TABLE ONLY content_group_element_has_allowed_nodes
934    ADD CONSTRAINT "$2" FOREIGN KEY (node_id) REFERENCES nodes(node_id) ON UPDATE CASCADE ON DELETE CASCADE;
935
936
937--
938-- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
939--
940
941ALTER TABLE ONLY user_has_content
942    ADD CONSTRAINT "$2" FOREIGN KEY (content_id) REFERENCES content(content_id) ON UPDATE CASCADE ON DELETE CASCADE;
943
944
945--
946-- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
947--
948
949ALTER TABLE ONLY node_has_content
950    ADD CONSTRAINT "$2" FOREIGN KEY (content_id) REFERENCES content(content_id) ON UPDATE CASCADE ON DELETE CASCADE;
951
952
953--
954-- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
955--
956
957ALTER TABLE ONLY content_display_log
958    ADD CONSTRAINT "$2" FOREIGN KEY (content_id) REFERENCES content(content_id) ON UPDATE CASCADE ON DELETE CASCADE;
959
960
961--
962-- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
963--
964
965ALTER TABLE ONLY node_stakeholders
966    ADD CONSTRAINT "$2" FOREIGN KEY (user_id) REFERENCES users(user_id);
967
968
969--
970-- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
971--
972
973ALTER TABLE ONLY network_stakeholders
974    ADD CONSTRAINT "$2" FOREIGN KEY (user_id) REFERENCES users(user_id);
975
976
977--
978-- Name: $3; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
979--
980
981ALTER TABLE ONLY content
982    ADD CONSTRAINT "$3" FOREIGN KEY (project_info) REFERENCES content(content_id) ON UPDATE CASCADE ON DELETE RESTRICT;
983
984
985--
986-- Name: $3; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
987--
988
989ALTER TABLE ONLY content_group_element
990    ADD CONSTRAINT "$3" FOREIGN KEY (displayed_content_id) REFERENCES content(content_id) ON UPDATE CASCADE ON DELETE CASCADE;
991
992
993--
994-- Name: $3; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
995--
996
997ALTER TABLE ONLY content_display_log
998    ADD CONSTRAINT "$3" FOREIGN KEY (node_id) REFERENCES nodes(node_id) ON UPDATE CASCADE ON DELETE CASCADE;
999
1000
1001--
1002-- Name: $4; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
1003--
1004
1005ALTER TABLE ONLY content
1006    ADD CONSTRAINT "$4" FOREIGN KEY (sponsor_info) REFERENCES content(content_id) ON UPDATE CASCADE ON DELETE RESTRICT;
1007
1008
1009--
1010-- Name: $5; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
1011--
1012
1013ALTER TABLE ONLY content
1014    ADD CONSTRAINT "$5" FOREIGN KEY (long_description) REFERENCES content(content_id) ON UPDATE CASCADE ON DELETE RESTRICT;
1015
1016
1017--
1018-- Name: account_origin_fkey; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
1019--
1020
1021ALTER TABLE ONLY users
1022    ADD CONSTRAINT account_origin_fkey FOREIGN KEY (account_origin) REFERENCES networks(network_id) ON UPDATE CASCADE ON DELETE RESTRICT;
1023
1024
1025--
1026-- Name: administrators_ibfk_1; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
1027--
1028
1029ALTER TABLE ONLY administrators
1030    ADD CONSTRAINT administrators_ibfk_1 FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;
1031
1032
1033--
1034-- Name: display_location_fkey; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
1035--
1036
1037ALTER TABLE ONLY network_has_content
1038    ADD CONSTRAINT display_location_fkey FOREIGN KEY (display_location) REFERENCES content_display_location(display_location) ON UPDATE CASCADE ON DELETE RESTRICT;
1039
1040
1041--
1042-- Name: display_location_fkey; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
1043--
1044
1045ALTER TABLE ONLY node_has_content
1046    ADD CONSTRAINT display_location_fkey FOREIGN KEY (display_location) REFERENCES content_display_location(display_location) ON UPDATE CASCADE ON DELETE RESTRICT;
1047
1048
1049--
1050-- Name: fk_node_deployment_status; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
1051--
1052
1053ALTER TABLE ONLY nodes
1054    ADD CONSTRAINT fk_node_deployment_status FOREIGN KEY (node_deployment_status) REFERENCES node_deployment_status(node_deployment_status) ON UPDATE CASCADE ON DELETE RESTRICT;
1055
1056
1057--
1058-- Name: fk_nodes; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
1059--
1060
1061ALTER TABLE ONLY connections
1062    ADD CONSTRAINT fk_nodes FOREIGN KEY (node_id) REFERENCES nodes(node_id) ON UPDATE CASCADE ON DELETE RESTRICT;
1063
1064
1065--
1066-- Name: fk_users; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
1067--
1068
1069ALTER TABLE ONLY connections
1070    ADD CONSTRAINT fk_users FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE RESTRICT;
1071
1072
1073--
1074-- Name: fk_venue_types; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
1075--
1076
1077ALTER TABLE ONLY nodes
1078    ADD CONSTRAINT fk_venue_types FOREIGN KEY (venue_type) REFERENCES venue_types(venue_type) ON UPDATE CASCADE ON DELETE RESTRICT;
1079
1080
1081--
1082-- Name: flickr_photostream_content_fkey; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
1083--
1084
1085ALTER TABLE ONLY flickr_photostream
1086    ADD CONSTRAINT flickr_photostream_content_fkey FOREIGN KEY (flickr_photostream_id) REFERENCES content(content_id) ON UPDATE CASCADE ON DELETE CASCADE;
1087
1088
1089--
1090-- Name: network_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
1091--
1092
1093ALTER TABLE ONLY nodes
1094    ADD CONSTRAINT network_id_fkey FOREIGN KEY (network_id) REFERENCES networks(network_id) ON UPDATE CASCADE ON DELETE RESTRICT;
1095
1096
1097--
1098-- Name: network_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: wifidog
1099--
1100
1101ALTER TABLE ONLY network_has_content
1102    ADD CONSTRAINT network_id_fkey FOREIGN KEY (network_id) REFERENCES networks(network_id) ON UPDATE CASCADE ON DELETE CASCADE;
1103
1104
1105--
1106-- PostgreSQL database dump complete
1107--
1108
Note: See TracBrowser for help on using the browser.