PageRenderTime 55ms CodeModel.GetById 17ms app.highlight 25ms RepoModel.GetById 2ms app.codeStats 1ms

/src/install/z_install.erl

http://github.com/zotonic/zotonic
Erlang | 713 lines | 527 code | 105 blank | 81 comment | 23 complexity | 958cb84759ac41d21d8092cd9f63d1f5 MD5 | raw file
  1%% @author Marc Worrell <marc@worrell.nl>
  2%% @copyright 2009-2015 Marc Worrell
  3%%
  4%% @doc Install Zotonic, loads the datamodel into the database
  5%% Assumes the database has already been created (which normally needs superuser permissions anyway)
  6%%
  7%% CREATE DATABASE zotonic WITH OWNER = zotonic ENCODING = 'UTF8';
  8%% CREATE LANGUAGE "plpgsql";
  9
 10%% Copyright 2009-2015 Marc Worrell
 11%%
 12%% Licensed under the Apache License, Version 2.0 (the "License");
 13%% you may not use this file except in compliance with the License.
 14%% You may obtain a copy of the License at
 15%% 
 16%%     http://www.apache.org/licenses/LICENSE-2.0
 17%% 
 18%% Unless required by applicable law or agreed to in writing, software
 19%% distributed under the License is distributed on an "AS IS" BASIS,
 20%% WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 21%% See the License for the specific language governing permissions and
 22%% limitations under the License.
 23
 24-module(z_install).
 25-author("Marc Worrell <marc@worrell.nl").
 26
 27%% interface functions
 28-export([
 29         install/1,
 30
 31         hierarchy_table/0,
 32         hierarchy_index_1/0,
 33         hierarchy_index_2/0,
 34
 35         medium_log_table/0,
 36         medium_update_function/0,
 37         medium_update_trigger/0,
 38
 39         edge_log_table/0,
 40         edge_log_function/0,
 41         edge_log_trigger/0,
 42
 43         rsc_page_path_log/0,
 44         rsc_page_path_log_fki/0
 45        ]).
 46
 47-include_lib("zotonic.hrl").
 48
 49%% @doc Install the database for the given host.
 50%% @spec install(Host) -> ok
 51install(Context) ->
 52    ok = z_db:transaction(
 53           fun(Context1) ->
 54                   ok = install_sql_list(Context1, model_pgsql()),
 55                   ok = z_install_data:install(z_context:site(Context1), Context1)
 56           end,
 57           Context),
 58
 59    InstallData = fun() ->
 60                          timer:sleep(200), %% give other processes some time to start
 61
 62                          %% install the default data for the skeleton the site is based on
 63                          z_install_defaultdata:install(m_site:get(skeleton, Context), Context),
 64
 65                          %% renumber the category tree.
 66                          m_category:renumber(Context)
 67                  end,
 68    spawn(InstallData),
 69    ok.
 70
 71
 72install_sql_list(Context, Model) ->
 73    C = z_db_pgsql:get_raw_connection(Context),
 74    [ {ok, [], []} = epgsql:squery(C, Sql) || Sql <- Model ],
 75    ok.
 76
 77
 78%% @doc Return a list containing the SQL statements to build the database model
 79model_pgsql() ->
 80    [
 81
 82    % Table config
 83    % Holds all configuration keys
 84     "CREATE TABLE config
 85    (
 86      id serial NOT NULL,
 87      module character varying(80) NOT NULL DEFAULT 'zotonic'::character varying,
 88      key character varying(80) NOT NULL DEFAULT ''::character varying,
 89      value text NOT NULL DEFAULT ''::character varying,
 90      props bytea,
 91      created timestamp with time zone NOT NULL DEFAULT now(),
 92      modified timestamp with time zone NOT NULL DEFAULT now(),
 93
 94      CONSTRAINT config_pkey PRIMARY KEY (id),
 95      CONSTRAINT config_module_key_key UNIQUE (module, key)
 96    )",
 97
 98
 99    % Table module
100    % Holds install state of all known modules
101
102    "CREATE TABLE module
103    (
104      id serial NOT NULL,
105      name character varying(80) NOT NULL DEFAULT ''::character varying,
106      uri character varying(2048) NOT NULL DEFAULT ''::character varying,
107      is_active boolean NOT NULL DEFAULT false,
108      created timestamp with time zone NOT NULL DEFAULT now(),
109      modified timestamp with time zone NOT NULL DEFAULT now(),
110      schema_version int NULL,
111
112      CONSTRAINT module_pkey PRIMARY KEY (id),
113      CONSTRAINT module_name_key UNIQUE (name)
114    )",
115
116
117    % Table: rsc
118    % Holds all resources (posts, persons etc.)
119    "CREATE TABLE rsc
120    (
121      id serial NOT NULL,
122      uri character varying(2048),
123      name character varying(80),
124      page_path character varying(80),
125      is_authoritative boolean NOT NULL DEFAULT true,
126      is_published boolean NOT NULL DEFAULT false,
127      is_featured boolean NOT NULL DEFAULT false,
128      is_protected boolean NOT NULL DEFAULT false,
129      is_dependent boolean NOT NULL DEFAULT false,
130      publication_start timestamp with time zone NOT NULL DEFAULT now(),
131      publication_end timestamp with time zone NOT NULL DEFAULT '9999-06-01 00:00:00'::timestamp with time zone,
132      content_group_id int,
133      creator_id int,
134      modifier_id int,
135      version int NOT NULL DEFAULT 1,
136      category_id int NOT NULL,
137      visible_for int NOT NULL DEFAULT 1, -- 0 = public, 1 = community, 2 = group
138      slug character varying(80) NOT NULL DEFAULT ''::character varying,
139      props bytea,
140      created timestamp with time zone NOT NULL DEFAULT now(),
141      modified timestamp with time zone NOT NULL DEFAULT now(),
142
143      -- pivot fields for searching
144      pivot_category_nr int,
145      pivot_tsv tsvector,       -- texts 
146      pivot_rtsv tsvector,      -- related ids (cat, prop, rsc)
147
148      pivot_first_name character varying(100),
149      pivot_surname character varying(100),
150      pivot_gender character varying(1),
151
152      pivot_date_start timestamp with time zone,
153      pivot_date_end timestamp with time zone,
154      pivot_date_start_month_day int,  -- used for birthdays
155      pivot_date_end_month_day int,    -- used for decease dates
156
157      pivot_street character varying(120),
158      pivot_city character varying(100),
159      pivot_state character varying(50),
160      pivot_postcode character varying(30),
161      pivot_country character varying(80),
162      pivot_geocode bigint,
163      pivot_geocode_qhash bytea,
164      pivot_title character varying(100),
165
166      pivot_location_lat float,
167      pivot_location_lng float,
168
169      CONSTRAINT rsc_pkey PRIMARY KEY (id),
170      CONSTRAINT rsc_uri_key UNIQUE (uri),
171      CONSTRAINT rsc_name_key UNIQUE (name),
172      CONSTRAINT rsc_page_path_key UNIQUE (page_path)
173    )",
174    "COMMENT ON COLUMN rsc.visible_for IS '0 = public, 1 = community, 2 = group'",
175
176     "ALTER TABLE rsc ADD CONSTRAINT fk_rsc_content_group_id FOREIGN KEY (content_group_id)
177      REFERENCES rsc (id)
178      ON UPDATE CASCADE ON DELETE SET NULL",
179     "ALTER TABLE rsc ADD CONSTRAINT fk_rsc_creator_id FOREIGN KEY (creator_id)
180      REFERENCES rsc (id)
181      ON UPDATE CASCADE ON DELETE SET NULL",
182    "ALTER TABLE rsc ADD CONSTRAINT fk_rsc_modifier_id FOREIGN KEY (modifier_id)
183      REFERENCES rsc (id)
184      ON UPDATE CASCADE ON DELETE SET NULL",
185
186     "CREATE INDEX fki_rsc_content_group_id ON rsc (content_group_id)",
187     "CREATE INDEX fki_rsc_creator_id ON rsc (creator_id)",
188     "CREATE INDEX fki_rsc_modifier_id ON rsc (modifier_id)",
189     "CREATE INDEX fki_rsc_created ON rsc (created)",
190     "CREATE INDEX fki_rsc_modified ON rsc (modified)",
191
192     "CREATE INDEX rsc_pivot_tsv_key ON rsc USING gin(pivot_tsv)",
193     "CREATE INDEX rsc_pivot_rtsv_key ON rsc USING gin(pivot_rtsv)",
194
195     "CREATE INDEX rsc_pivot_category_nr ON rsc (pivot_category_nr)",
196     "CREATE INDEX rsc_pivot_surname_key ON rsc (pivot_surname)",
197     "CREATE INDEX rsc_pivot_first_name_key ON rsc (pivot_first_name)",
198     "CREATE INDEX rsc_pivot_gender_key ON rsc (pivot_gender)",
199     "CREATE INDEX rsc_pivot_date_start_key ON rsc (pivot_date_start)",
200     "CREATE INDEX rsc_pivot_date_end_key ON rsc (pivot_date_end)",
201     "CREATE INDEX rsc_pivot_date_start_month_day_key ON rsc (pivot_date_start_month_day)",
202     "CREATE INDEX rsc_pivot_date_end_month_day_key ON rsc (pivot_date_end_month_day)",
203     "CREATE INDEX rsc_pivot_city_street_key ON rsc (pivot_city, pivot_street)",
204     "CREATE INDEX rsc_pivot_country_key ON rsc (pivot_country)",
205     "CREATE INDEX rsc_pivot_postcode_key ON rsc (pivot_postcode)",
206     "CREATE INDEX rsc_pivot_geocode_key ON rsc (pivot_geocode)",
207     "CREATE INDEX rsc_pivot_title_key ON rsc (pivot_title)",
208     "CREATE INDEX rsc_pivot_location_key ON rsc (pivot_location_lat, pivot_location_lng)",
209
210    % Table: rsc_gone
211    % Tracks deleted or moved resources, adding "410 gone" support
212    % Also contains new id or new url for 301 moved permanently replies.
213    % mod_backup is needed to recover a deleted resource's content.
214
215     "CREATE TABLE rsc_gone (
216        id bigint not null,
217     new_id bigint,
218     new_uri character varying(2048),
219     version int not null,
220     uri character varying(2048),
221     name character varying(80),
222     page_path character varying(80),
223     is_authoritative boolean NOT NULL DEFAULT true,
224     creator_id bigint,
225     modifier_id bigint,
226     created timestamp with time zone NOT NULL DEFAULT now(),
227     modified timestamp with time zone NOT NULL DEFAULT now(),
228     CONSTRAINT rsc_gone_pkey PRIMARY KEY (id)
229    )",
230    "CREATE INDEX rsc_gone_name ON rsc_gone(name)",
231    "CREATE INDEX rsc_gone_page_path ON rsc_gone(page_path)",
232    "CREATE INDEX rsc_gone_modified ON rsc_gone(modified)",
233
234
235    % Table: protect
236    % By making an entry in this table we protect a rsc from being deleted.
237    % This table is maintained by the update/insert trigger.
238
239    "CREATE TABLE protect (
240        id int NOT NULL,
241
242    CONSTRAINT protect_id PRIMARY KEY (id),
243    CONSTRAINT fk_protect_id FOREIGN KEY (id)
244        REFERENCES rsc(id)
245        ON UPDATE CASCADE ON DELETE RESTRICT
246        )",
247
248    % Table: edge
249    % All relations between resources, forming a directed graph
250
251    "CREATE TABLE edge
252    (
253      id serial NOT NULL,      
254      subject_id int NOT NULL,
255      predicate_id int NOT NULL,
256      object_id int NOT NULL,
257      seq int NOT NULL DEFAULT 1000000,
258      creator_id int,
259      created timestamp with time zone NOT NULL DEFAULT now(),
260
261      CONSTRAINT edge_pkey PRIMARY KEY (id),
262      CONSTRAINT edge_ops_key UNIQUE (object_id, predicate_id, subject_id),
263      CONSTRAINT edge_spo_key UNIQUE (subject_id, predicate_id, object_id),
264      CONSTRAINT fk_edge_subject_id FOREIGN KEY (subject_id)
265        REFERENCES rsc (id)
266        ON UPDATE CASCADE ON DELETE CASCADE,
267      CONSTRAINT fk_edge_object_id FOREIGN KEY (object_id)
268        REFERENCES rsc (id)
269        ON UPDATE CASCADE ON DELETE CASCADE,
270      CONSTRAINT fk_edge_predicate_id FOREIGN KEY (predicate_id)
271        REFERENCES rsc (id)
272        ON UPDATE CASCADE ON DELETE CASCADE,
273      CONSTRAINT fk_edge_creator_id FOREIGN KEY (creator_id)
274        REFERENCES rsc (id)
275        ON UPDATE CASCADE ON DELETE SET NULL
276    )",
277
278    "CREATE INDEX fki_edge_subject_id ON edge (subject_id)",
279    "CREATE INDEX fki_edge_predicate_id ON edge (predicate_id)",
280    "CREATE INDEX fki_edge_object_id ON edge (object_id)",
281    "CREATE INDEX fki_edge_creator_id ON edge (creator_id)",
282    "CREATE INDEX edge_sp_seq_key ON edge (subject_id, predicate_id, seq)",
283
284
285    % Table medium
286    % Holds all references to media (files), used in the context of resources
287    % Every medium is a resource.
288    % The preview might have been generated from the original file and is always a jpeg.
289
290    "CREATE TABLE medium
291    (
292      id int NOT NULL,
293      filename character varying(400),
294      rootname character varying(100),
295      mime character varying(128) NOT NULL DEFAULT 'application/octet-stream'::character varying,
296      width int NOT NULL DEFAULT 0,
297      height int NOT NULL DEFAULT 0,
298      orientation int NOT NULL DEFAULT 1,
299      sha1 character varying(40),
300      size int NOT NULL DEFAULT 0,
301      preview_filename character varying(400),
302      preview_width int NOT NULL DEFAULT 0,
303      preview_height int NOT NULL DEFAULT 0,
304      is_deletable_file boolean NOT NULL DEFAULT false, 
305      is_deletable_preview boolean NOT NULL DEFAULT false, 
306      props bytea,
307      created timestamp with time zone NOT NULL DEFAULT now(),
308
309      CONSTRAINT medium_pkey PRIMARY KEY (id),
310      CONSTRAINT medium_filename_key UNIQUE (filename),
311      CONSTRAINT fk_medium_rsc_id FOREIGN KEY (id)
312        REFERENCES rsc (id)
313        ON UPDATE CASCADE ON DELETE CASCADE
314    )",
315
316    "CREATE INDEX medium_rootname_key ON medium (rootname)",
317
318    % Table: predicate_category
319    % Defines which categories are valid for a predicate as subject or object
320
321    "CREATE TABLE predicate_category
322    (
323      id serial NOT NULL,
324      is_subject boolean NOT NULL DEFAULT true,
325      predicate_id int NOT NULL,
326      category_id int NOT NULL,
327
328      CONSTRAINT predicate_category_pkey PRIMARY KEY (id),
329      CONSTRAINT predicate_category_key UNIQUE (predicate_id, is_subject, category_id),
330      CONSTRAINT fk_predicate_category_predicate_id FOREIGN KEY (predicate_id)
331        REFERENCES rsc(id)
332        ON UPDATE CASCADE
333        ON DELETE CASCADE,
334      CONSTRAINT fk_predicate_category_category_id FOREIGN KEY (category_id)
335        REFERENCES rsc(id)
336        ON UPDATE CASCADE
337        ON DELETE CASCADE
338    )",
339    
340    "CREATE INDEX fki_predicate_category_predicate_id ON predicate_category (predicate_id)",
341    "CREATE INDEX fki_predicate_category_category_id ON predicate_category (category_id)",
342
343    % Table persistent
344    % Holds persistent information coupled to an user agent.  Can be shopping cart, click history etc
345
346	"CREATE TABLE persistent 
347	 (
348	  id character varying(32) not null,
349	  props bytea,
350	  created timestamp with time zone NOT NULL DEFAULT now(),
351	  modified timestamp with time zone NOT NULL DEFAULT now(),
352	  CONSTRAINT persistent_pkey PRIMARY KEY (id)
353	)",
354	
355
356    % Table identity
357    % Identities of an user, used for authentication.  Examples are password, openid, msn, xmpp etc.
358
359    "CREATE TABLE identity
360    (
361      id serial NOT NULL,
362      rsc_id int NOT NULL,
363      type character varying(32) NOT NULL DEFAULT ''::character varying,
364      key character varying(200) NOT NULL DEFAULT ''::character varying,
365      is_unique boolean,          -- set to true when the type/key should be unique
366      is_verified boolean not null default false,
367      verify_key character varying(32),
368      propb bytea,
369      prop1 character varying(200) NOT NULL DEFAULT ''::character varying,
370      prop2 character varying(200) NOT NULL DEFAULT ''::character varying,
371      prop3 character varying(200) NOT NULL DEFAULT ''::character varying,
372      created timestamp with time zone NOT NULL DEFAULT now(),
373      modified timestamp with time zone NOT NULL DEFAULT now(),
374      visited timestamp with time zone,
375
376      CONSTRAINT auth_pkey PRIMARY KEY (id),
377      CONSTRAINT pk_auth_rsc_id FOREIGN KEY (rsc_id)
378        REFERENCES rsc (id)
379        ON UPDATE CASCADE ON DELETE CASCADE,
380      CONSTRAINT identity_type_key_unique UNIQUE (type, key, is_unique),
381      CONSTRAINT identity_verify_key_unique UNIQUE (verify_key)
382    )",
383
384    "CREATE INDEX fki_identity_rsc_id ON identity (rsc_id)",
385    "CREATE INDEX identity_type_key_key ON identity (type, key)",
386    "CREATE INDEX identity_visited_key ON identity (visited)",
387    "CREATE INDEX identity_created_key ON identity (created)",
388
389    % Email send queue and log
390    "CREATE TABLE emailq (
391        id serial NOT NULL,
392        status character varying(10) not null default 'new', -- new, sent, fail
393        retry_on timestamp with time zone default (now() + '00:10:00'::interval),
394        retry int not null default 0,
395        sender character varying(100),
396        recipient character varying(100),
397        props bytea,
398        sent timestamp with time zone,
399        created timestamp with time zone not null default now(),
400        CONSTRAINT email_pkey PRIMARY KEY (id)
401    )",
402
403    "CREATE INDEX email_recipient_key ON emailq (recipient)",
404    "CREATE INDEX email_created_key ON emailq (created)",
405    "CREATE INDEX email_status_retry_key ON emailq (status, retry_on)",
406
407    % pivot queue for rsc, all things that are updated are queued here for later full text indexing
408    "CREATE TABLE rsc_pivot_queue
409    (
410        rsc_id int NOT NULL,
411        serial int NOT NULL DEFAULT 1,
412        due timestamp with time zone,
413        is_update boolean NOT NULL default true,
414        
415        CONSTRAINT rsc_pivot_queue_pkey PRIMARY KEY (rsc_id),
416        CONSTRAINT fk_rsc_pivot_queue_rsc_id FOREIGN KEY (rsc_id)
417          REFERENCES rsc(id)
418          ON UPDATE CASCADE ON DELETE CASCADE
419    )",
420
421    "CREATE INDEX fki_rsc_pivot_queue_rsc_id ON rsc_pivot_queue (rsc_id)",
422    "CREATE INDEX fki_rsc_pivot_queue_due ON rsc_pivot_queue (is_update, due)",
423
424    % queue for slow pivoting queries, for example syncing category nrs after the categories are changed.
425    "CREATE TABLE pivot_task_queue
426    (
427        id serial NOT NULL,
428        module character varying(30) NOT NULL,
429        function character varying(30) NOT NULL,
430        key character varying(100) NOT NULL DEFAULT ''::character varying,
431        due timestamp with time zone ,
432        props bytea,
433        
434        CONSTRAINT pivot_task_queue_pkey PRIMARY KEY (id),
435        CONSTRAINT pivot_task_queue_module_funcion_key_key UNIQUE (module, function, key)
436    )
437    ",
438
439    % Update/insert trigger on rsc to fill the update queue
440    % The text indexing is delayed until the updates are stable
441    % Also checks if the rsc is set to protected, if so makes an entry in the 'protect' table.
442    "
443    CREATE FUNCTION rsc_pivot_update() RETURNS trigger AS $$
444    declare 
445        duetime timestamp;
446        do_queue boolean;
447    begin
448        if (tg_op = 'INSERT') then
449            do_queue := true;
450        elseif (new.version <> old.version or new.modified <> old.modified) then
451            do_queue := true;
452        else
453            do_queue := false;
454        end if;
455
456        if (do_queue) then
457            <<insert_update_queue>>
458            loop
459                update rsc_pivot_queue 
460                set due = (case when now() < due then now() else due end),
461                    serial = serial + 1
462                where rsc_id = new.id;
463            
464                exit insert_update_queue when found;
465            
466                begin
467                    insert into rsc_pivot_queue (rsc_id, due, is_update) values (new.id, now(), tg_op = 'UPDATE');
468                    exit insert_update_queue;
469                exception
470                    when unique_violation then
471                        -- do nothing
472                end;
473            end loop insert_update_queue;
474        end if;
475            
476        if (new.is_protected) then
477            begin
478                insert into protect (id) values (new.id);
479            exception
480                when unique_violation then
481                    -- do nothing
482            end;
483        else
484            delete from protect where id = new.id;
485        end if;
486        return null;
487    end;
488    $$ LANGUAGE plpgsql
489    ",
490    "
491    CREATE TRIGGER rsc_update_queue_trigger AFTER INSERT OR UPDATE
492    ON rsc FOR EACH ROW EXECUTE PROCEDURE rsc_pivot_update()
493    ",
494
495    % Queue for deleted medium files, periodically checked for deleting files that are not referenced anymore
496    "CREATE TABLE medium_deleted
497    (
498        id serial NOT NULL,
499        filename character varying (400) NOT NULL,
500        deleted timestamp with time zone NOT NULL default now(),
501        
502        CONSTRAINT medium_deleted_pkey PRIMARY KEY (id)
503    )",
504
505    "CREATE INDEX medium_deleted_deleted_key ON medium_deleted (deleted)",
506
507    % Update/insert trigger on medium to fill the deleted files queue
508    "
509    CREATE FUNCTION medium_delete() RETURNS trigger AS $$
510    begin
511        if (tg_op = 'DELETE') then
512            if (old.filename <> '' and old.filename is not null and old.is_deletable_file) then
513                insert into medium_deleted (filename) values (old.filename);
514            end if;
515            if (old.preview_filename <> '' and old.preview_filename is not null and old.is_deletable_preview) then
516                insert into medium_deleted (filename) values (old.preview_filename);
517            end if;
518        end if;
519        return null;
520    end;
521    $$ LANGUAGE plpgsql
522    ",
523    "
524    CREATE TRIGGER medium_deleted_trigger AFTER DELETE
525    ON medium FOR EACH ROW EXECUTE PROCEDURE medium_delete()
526    ",
527
528    %% Table with hierarchies for menus and the category tree
529    hierarchy_table(),
530    hierarchy_index_1(),
531    hierarchy_index_2(),
532
533    % Table with all uploaded filenames, used to ensure unique filenames in the upload archive
534    medium_log_table(),
535
536    % Update/insert trigger on medium to fill the deleted files queue
537    medium_update_function(),
538    medium_update_trigger(),
539
540    %% Holds administration of previous page paths
541    rsc_page_path_log(),
542    rsc_page_path_log_fki(),
543
544    %% Track deletion/insert/changes of edges
545    edge_log_table(),
546    edge_log_function(),
547    edge_log_trigger()
548    ].
549
550
551hierarchy_table() ->
552    "CREATE TABLE hierarchy (
553        name character varying (80),
554        id int NOT NULL,
555        parent_id int,
556        nr int NOT NULL DEFAULT 0,
557        lvl int NOT NULL DEFAULT 0,
558        lft int NOT NULL DEFAULT 0,
559        rght int NOT NULL DEFAULT 0,
560
561        CONSTRAINT hierarchy_pkey PRIMARY KEY (name, id),
562        CONSTRAINT fk_hierarchy_id FOREIGN KEY (id)
563          REFERENCES rsc(id)
564          ON UPDATE CASCADE ON DELETE CASCADE
565          DEFERRABLE INITIALLY DEFERRED
566    )".
567
568hierarchy_index_1() ->
569    "CREATE INDEX hierarchy_nr_key ON hierarchy (name, nr)".
570
571hierarchy_index_2() ->
572    "CREATE INDEX fki_hierarchy_id ON hierarchy (id)".
573
574
575edge_log_table() ->
576    "CREATE TABLE edge_log
577    (
578        id bigserial NOT NULL,
579        op character varying(6),
580        edge_id int not null,
581        subject_id int not null,
582        predicate_id int not null,
583        predicate character varying (80),
584        object_id int not null,
585        seq integer not null,
586        created timestamp with time zone NOT NULL default now(),
587        
588        CONSTRAINT edge_log_pkey PRIMARY KEY (id)
589    )".
590
591edge_log_function() ->
592    "
593    CREATE FUNCTION edge_update() RETURNS trigger AS $$
594    declare
595        new_predicate character varying(80);
596    begin
597        if (tg_op = 'INSERT') then
598            select into new_predicate r.name from rsc r where r.id = new.predicate_id;
599            insert into edge_log (op, edge_id, subject_id, object_id, predicate_id, predicate, seq)
600            values (tg_op, new.id, new.subject_id, new.object_id, new.predicate_id, new_predicate, new.seq);
601        elseif (tg_op = 'UPDATE') then
602            select into new_predicate r.name from rsc r where r.id = new.predicate_id;
603            insert into edge_log (op, edge_id, subject_id, object_id, predicate_id, predicate, seq)
604            values (tg_op, new.id, new.subject_id, new.object_id, new.predicate_id, new_predicate, new.seq);
605        elseif (tg_op = 'DELETE') then
606            select into new_predicate r.name from rsc r where r.id = old.predicate_id;
607            insert into edge_log (op, edge_id, subject_id, object_id, predicate_id, predicate, seq)
608            values (tg_op, old.id, old.subject_id, old.object_id, old.predicate_id, new_predicate, old.seq);
609        end if;
610        return null;
611    end;
612    $$ LANGUAGE plpgsql
613    ".
614
615
616edge_log_trigger() ->
617    "
618    CREATE TRIGGER edge_update_trigger AFTER INSERT OR UPDATE OR DELETE
619    ON edge FOR EACH ROW EXECUTE PROCEDURE edge_update()
620    ".
621
622
623medium_log_table() ->
624    "CREATE TABLE medium_log
625    (
626        id serial NOT NULL,
627        usr_id int,
628        filename character varying (400) NOT NULL,
629        created timestamp with time zone NOT NULL default now(),
630        
631        CONSTRAINT medium_log_pkey PRIMARY KEY (id),
632        CONSTRAINT medium_log_filename_key UNIQUE (filename)
633    )".
634
635
636medium_update_function() ->
637    "
638    CREATE FUNCTION medium_update() RETURNS trigger AS $$
639    declare
640        user_id integer;
641    begin
642        select into user_id r.creator_id from rsc r where r.id = new.id;
643        if (tg_op = 'INSERT') then
644            if (new.filename <> '' and new.filename is not null and new.is_deletable_file) then
645                insert into medium_log (filename, usr_id)
646                values (new.filename, user_id);
647            end if;
648            if (new.preview_filename <> '' and new.preview_filename is not null and new.is_deletable_preview) then
649                insert into medium_log (filename, usr_id)
650                values (new.preview_filename, user_id);
651            end if;
652        elseif (tg_op = 'UPDATE') then
653            if (new.filename <> '' and new.filename is not null and new.is_deletable_file and new.filename != old.filename) then
654                insert into medium_log (filename, usr_id)
655                values (new.filename, user_id);
656            end if;
657            if (new.preview_filename <> '' and new.preview_filename is not null and new.is_deletable_preview and new.preview_filename != old.preview_filename) then
658                insert into medium_log (filename, usr_id)
659                values (new.preview_filename, user_id);
660            end if;
661            -- Insert files into the medium_deleted queue table
662            if (old.filename <> '' and old.filename is not null and old.is_deletable_file and new.filename != old.filename) then
663                insert into medium_deleted (filename) values (old.filename);
664            end if;
665            if (old.preview_filename <> '' and old.preview_filename is not null and old.is_deletable_preview and new.preview_filename != old.preview_filename) then
666                insert into medium_deleted (filename) values (old.preview_filename);
667            end if;
668        end if;
669        return null;
670    end;
671    $$ LANGUAGE plpgsql
672    ".
673
674medium_update_trigger() ->
675    "
676    CREATE TRIGGER medium_update_trigger AFTER INSERT OR UPDATE
677    ON medium FOR EACH ROW EXECUTE PROCEDURE medium_update()
678    ".
679
680rsc_page_path_log() ->
681   "CREATE TABLE rsc_page_path_log ( 
682      page_path character varying(80),
683      id int not null,
684      created timestamp with time zone NOT NULL DEFAULT now(),
685      CONSTRAINT rsc_page_path_log_pkey PRIMARY KEY (page_path),
686      CONSTRAINT fk_rsc_page_path_log_id FOREIGN KEY (id) 
687        REFERENCES rsc(id)
688        ON UPDATE CASCADE ON DELETE CASCADE
689    )".
690
691rsc_page_path_log_fki() ->
692    "CREATE INDEX fki_rsc_page_path_log_id ON rsc_page_path_log (id)".
693
694
695%    -- Fulltext index of products
696%    -- TODO: Also mix in the shop product id, brand, group and properties
697%    -- TODO: Use ispell for handling typos
698%    CREATE INDEX shop_product_tsv ON shop_product USING gin(tsv);
699%    CREATE FUNCTION shop_product_trigger() RETURNS trigger AS $$ 
700%    begin
701%      new.tsv := 
702%        setweight(to_tsvector('pg_catalog.dutch', coalesce(new.title_nl,'')), 'A') || 
703%        setweight(to_tsvector('pg_catalog.dutch', coalesce(new.desc_nl,'')),  'D') ||
704%        setweight(to_tsvector('pg_catalog.english', coalesce(new.title_en,'')), 'A') || 
705%        setweight(to_tsvector('pg_catalog.english', coalesce(new.desc_en,'')),  'D'); 
706%      return new; 
707%    end 
708%    $$ LANGUAGE plpgsql; 
709%    CREATE TRIGGER tsvectorupdate_shop_product BEFORE INSERT OR UPDATE 
710%    ON shop_product FOR EACH ROW EXECUTE PROCEDURE shop_product_trigger();
711
712
713