/src/install/z_install.erl
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