Index: openacs-4/packages/related-items/related-items.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/related-items/related-items.info,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/related-items/related-items.info 14 Jun 2004 10:23:00 -0000 1.1 @@ -0,0 +1,27 @@ + + + + + Related Items + Related Items + f + t + related + + + Jeff Davis + Higher level interface for designating related items. + Xarg + Higher level interface for designating related items. + 0 + + + + + + + + + + + Index: openacs-4/packages/related-items/sql/postgresql/relations-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/related-items/sql/postgresql/Attic/relations-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/related-items/sql/postgresql/relations-create.sql 14 Jun 2004 10:23:00 -0000 1.1 @@ -0,0 +1,83 @@ +-- Related items +-- +-- Relate items the openacs way! For publicly visible related items (like relating forum post to a bug) +-- +-- Copyright (C) 2003 Jeff Davis +-- @author Jeff Davis +-- @creation-date 10/22/2003 +-- +-- @cvs-id $Id: relations-create.sql,v 1.1 2004/06/14 10:23:00 jeffd Exp $ +-- +-- This is free software distributed under the terms of the GNU Public +-- License. Full text of the license is available from the GNU Project: +-- http://www.fsf.org/copyleft/gpl.html + +create table content_rels ( + rel_id integer references acs_rels(rel_id) +); + +comment on table content_rels is 'content_rels is for higher level content relations supported by the related-items package. This is a stupid table -- just there to feed the table gods. DonB rescue me from this absurdity.'; + +select acs_object_type__create_type( + 'content_rel', + 'Related content', + 'Related content', + 'relationship', + 'content_rels', + 'rel_id', + 'content_rel', + 'f', + 'content_rel__title', + null +); + +insert into acs_rel_types (rel_type, object_type_one, role_one, min_n_rels_one, max_n_rels_one, object_type_two, role_two, min_n_rels_two, max_n_rels_two) +values ('content_rel','acs_object',null,0,null,'acs_object',null,0,null); + + +create or replace function content_rel__new (integer,integer,integer,integer,integer,varchar) +returns integer as ' +declare + new_rel_id alias for $1; -- default null + object_id_one alias for $2; + object_id_two alias for $3; + context_id alias for $4; -- default null + creation_user alias for $5; -- default null + creation_ip alias for $6; -- default null + v_rel_id acs_rels.rel_id%TYPE; +begin + v_rel_id := acs_rel__new(new_rel_id, ''content_rel'',object_id_one, object_id_two, context_id, creation_user, creation_ip); + + insert into content_rels(rel_id) values (v_rel_id); + + return v_rel_id; + +end;' language 'plpgsql'; + +select define_function_args('content_rel__new','rel_id,object_id_one,object_id_two,context_id,creation_user,creation_ip'); + + +create or replace function content_rel__delete(integer) +returns integer as ' +declare + rel_id alias for $1; +begin + PERFORM acs_object__delete(rel_id); + + return 0; +end;' language 'plpgsql'; + +select define_function_args('content_rel__delete','rel_id'); + + +create or replace function content_rel__title(integer) +returns varchar as ' +declare + rel_id alias for $1; + v_title acs_objects.title%TYPE; +begin + select title into v_title from acs_objects where object_id = rel_id and object_type = ''content_rel''; + return v_title; +end;' language 'plpgsql'; + +select define_function_args('content_rel__title','rel_id'); Index: openacs-4/packages/related-items/sql/postgresql/relations-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/related-items/sql/postgresql/Attic/relations-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/related-items/sql/postgresql/relations-drop.sql 14 Jun 2004 10:23:00 -0000 1.1 @@ -0,0 +1,40 @@ +-- Related Items +-- +-- Drop the relations data package and tables. +-- +-- Copyright (C) 2003 Jeff Davis +-- @author Jeff Davis davis@xarg.net +-- @creation-date 10/22/2003 +-- +-- @cvs-id $Id: relations-drop.sql,v 1.1 2004/06/14 10:23:00 jeffd Exp $ +-- +-- This is free software distributed under the terms of the GNU Public +-- License. Full text of the license is available from the GNU Project: +-- http://www.fsf.org/copyleft/gpl.html + +delete from acs_rel_types where rel_type = 'content_rel'; + +create or replace function tmp_content_relations_delete () +returns integer as ' +declare + coll_rec RECORD; +begin + for coll_rec in select object_id + from acs_objects + where object_type = ''content_rel'' + loop + PERFORM acs_object__delete (coll_rec.object_id); + end loop; + + return 1; +end; ' language 'plpgsql'; + +select tmp_content_relations_delete (); +drop function tmp_content_relations_delete (); + +select acs_object_type__drop_type('content_rel', 'f'); +drop table content_rels; + +select drop_package('content_rel'); + + Index: openacs-4/packages/related-items/tcl/relation-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/related-items/tcl/relation-procs.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/related-items/tcl/relation-procs.tcl 14 Jun 2004 10:23:00 -0000 1.1 @@ -0,0 +1,42 @@ +# /packages/related-items/tcl/relation-procs.tcl +ad_library { + TCL library for the related items + + @author Jeff Davis + + @creation-date 10/23/2003 + @cvs-id $Id: relation-procs.tcl,v 1.1 2004/06/14 10:23:00 jeffd Exp $ +} + +namespace eval relation {} + +ad_proc -public relation::get_related { + -object_id + -datasource +} { + sets a multirow with the related items for object_id + + @return number of related items + the multirow is created as side effect + + @author Jeff Davis davis@xarg.net + @creation-date 2004-01-30 +} { + # here we pull out all rels where object_one or object_two matches and + # return the information for the object which is not the one we are + # are querying on + db_multirow $datasource related { + SELECT ar.rel_id, + (case when ar.object_id_one = :object_id then ar.object_id_two else ar.object_id_one end) as object_id, + to_char(ro.creation_date,'YYYY-MM-DD HH24:MI') as related_on, + coalesce(o1.title,'? '||o1.object_type||' '||o1.object_id) as object_title, + person__name(ro.creation_user) as name + FROM content_rels r, acs_objects o1, acs_rels ar, acs_objects ro + WHERE ( (ar.object_id_one = :object_id and o1.object_id = ar.object_id_two) + or ( ar.object_id_two = :object_id and o1.object_id = ar.object_id_one) ) + and ar.rel_id = r.rel_id + and ro.object_id = r.rel_id + } + + return [template::multirow size $datasource] +} + Index: openacs-4/packages/related-items/www/index.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/related-items/www/index.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/related-items/www/index.adp 14 Jun 2004 10:23:00 -0000 1.1 @@ -0,0 +1,5 @@ + + @title@ + @context@ + + Index: openacs-4/packages/related-items/www/index.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/related-items/www/index.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/related-items/www/index.tcl 14 Jun 2004 10:23:00 -0000 1.1 @@ -0,0 +1,62 @@ +# /packages/cop-ui/www/related/index.tcl +ad_page_contract { + Display the recently related items + + @author Jeff Davis (davis@xarg.net) + @creation-date 11/12/2003 + + @cvs-id $Id: index.tcl,v 1.1 2004/06/14 10:23:00 jeffd Exp $ +} { + {orderby "related_on,desc"} +} + +set user_id [auth::refresh_login] + +set title "Related items" + +set context [list {related items}] + +set elements { + object_one_title { + label {Item1} + display_template {@related.object_one_title@} + } + object_two_title { + label {Item2} + display_template {@related.object_two_title@} + } + related_on { + label {Added} + } + name { + label {By} + link_url_col user_url + } +} + +set packages [cop::util::packages -node_id [ad_conn node_id]] + +template::list::create \ + -name related \ + -multirow related \ + -elements $elements \ + -orderby { + object_one_title { orderby lower(o1.title) } + object_two_title { orderby lower(o2.title) } + related_on { orderby ro.creation_date } + name { orderby {lower(person__name(ro.creation_user))}} + } + +db_multirow -extend {extra user_url} related related " + SELECT to_char(ro.creation_date,'YYYY-MM-DD HH24:MI') as related_on, coalesce(o1.title,'? '||o1.object_type||o1.object_id) as object_one_title, o2.title as object_two_title, person__name(ro.creation_user) as name, object_id_one, object_id_two + FROM cop_rels r, acs_objects o1, acs_objects o2, acs_rels ar, acs_objects ro + WHERE o1.object_id = ar.object_id_one + and o2.object_id = ar.object_id_two + and ar.rel_id = r.rel_id + and ro.object_id = r.rel_id + and ( o1.package_id in ([join $packages ,]) + or o2.package_id in ([join $packages ,])) + [template::list::orderby_clause -orderby -name "related"]" { + set user_url [acs_community_member_url -user_id $user_id] + set extra foo + } Index: openacs-4/packages/related-items/www/relate.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/related-items/www/Attic/relate.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/related-items/www/relate.adp 14 Jun 2004 10:23:00 -0000 1.1 @@ -0,0 +1,5 @@ + + @title@ + @context@ + + Index: openacs-4/packages/related-items/www/relate.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/related-items/www/Attic/relate.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/related-items/www/relate.tcl 14 Jun 2004 10:23:00 -0000 1.1 @@ -0,0 +1,108 @@ +# /packages/cop-ui/www/related/relate.tcl +ad_page_contract { + Relate object_id. + Requires registration. + + @author Jeff Davis davis@xarg.net + @creation-date 10/30/2003 + @cvs-id $Id: relate.tcl,v 1.1 2004/06/14 10:23:00 jeffd Exp $ +} { + object_one:integer,notnull + {orderby "clipboard,desc"} +} + +set user_id [auth::require_login] + +set admin_p [permission::permission_p -object_id [ad_conn package_id] -privilege admin] + +set title "Relate" +if {![empty_string_p $object_one]} { + set object_name [db_string object_name {select acs_object__name(:object_one);} -default {}] + if {![empty_string_p object_name]} { + append title " to $object_name" + } else { + append title " to object $object_on" + } +} + + +set context [list [list ./ relate] {relate object}] + +set elements { + type { + label {Type} + display_template "@relate.pretty_name@" + } + object_title { + label {Item} + } + clipboard { + label {Clipboard} + } + clipped { + label {Clipped on} + display_template "@relate.clipped;noquote@" + html {align right} + } +} + +#lappend elements extra { +# label {Debug} +#} + +set bulk [list "Relate" relation-add] + + +template::list::create \ + -name relate \ + -multirow relate \ + -key object_id \ + -elements $elements \ + -orderby { + type { + orderby lower(t.pretty_name),x.clipped + } + object_title { + orderby lower(x.object_title),x.clipped + } + clipboard { + orderby lower(x.clipboard),x.clipped + } + clipped { + orderby x.clipped + } + } -filters { + object_one {} + } -bulk_actions $bulk -bulk_action_export_vars object_one + +set now [clock_to_ansi [clock seconds]] + +db_multirow -extend extra relate relate " + SELECT * FROM ( + SELECT t.pretty_name,o.object_id, co.title as clipboard, coalesce(o.title,'? '|| o.object_type || o.object_id) as object_title, to_char(cm.clipped_on,'YYYY-MM-DD HH24:MI:SS') as clipped + FROM acs_objects o, cop_clipboards c, acs_objects co, cop_clipboard_object_map cm, acs_object_types t + WHERE c.owner_id = :user_id + and cm.clipboard_id = c.clipboard_id + and o.object_id = cm.object_id + and co.object_id = c.clipboard_id + and cm.object_id != :object_one + and t.object_type = o.object_type + UNION ALL + SELECT t.pretty_name, v.object_id, 'viewed', coalesce(o.title,'? ' || o.object_type || o.object_id) as object_title, to_char(v.last_viewed,'YYYY-MM-DD HH24:MI:SS') + FROM cop_object_views v, acs_objects o, acs_object_types t + WHERE o.object_id = v.object_id + and v.viewer_id = :user_id + and v.object_id != :object_one + and t.object_type = o.object_type + ) x + WHERE not exists ( + SELECT 1 + FROM acs_rels + WHERE rel_type = 'cop_rel' + and ( (object_id_one = :object_one and object_id_two = x.object_id) + or (object_id_one = x.object_id and object_id_two = :object_one))) + [template::list::orderby_clause -orderby -name "relate"]" { + set clipped [regsub -all { } [util::age_pretty -hours_limit 0 -mode_2_fmt "%X %a" -mode_3_fmt "%x" -timestamp_ansi $clipped -sysdate_ansi $now] {\ }] + } + + Index: openacs-4/packages/related-items/www/relation-add.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/related-items/www/Attic/relation-add.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/related-items/www/relation-add.tcl 14 Jun 2004 10:23:00 -0000 1.1 @@ -0,0 +1,27 @@ +# /packages/cop-ui/www/related/relate.tcl +ad_page_contract { + Relate object_id to object_two (can be multiple) + Requires registration. + + @author Jeff Davis davis@xarg.net + @creation-date 10/30/2003 + @cvs-id $Id: relation-add.tcl,v 1.1 2004/06/14 10:23:00 jeffd Exp $ +} { + object_one:integer,notnull + object_id:multiple,integer,notnull +} + +set user_id [auth::require_login] + +set vars [list \ + [list object_id_one $object_one] \ + [list creation_user $user_id] \ + [list creation_ip [ad_conn peeraddr]] \ + ] + +foreach object $object_id { + ns_log DEBUG "JCD: relating $object to $object_one ($object_id)" + package_exec_plsql -var_list [concat $vars [list [list object_id_two $object]]] cop_rel new +} + +ad_returnredirect -message "Added [llength $object_id] related items" relate?object_one=$object_one