Index: openacs-4/packages/adserver/adserver.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/adserver.info,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/adserver.info 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,106 @@ + + + + + Ad Server + adservers + f + t + + + + oracle + postgresql + + Jerry Asher + Janine Sisk + Serve ads to users and monitor clickthroughs + 2001-07-09 + furfly.net, LLC + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Index: openacs-4/packages/adserver/sql/CHANGES =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/sql/CHANGES,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/sql/CHANGES 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,6 @@ +* In PG, you have to specify the field type of the foreign key +* No "CREATE OR REPLACE" and the like statements in PG, object should be dropped first +* TRUNC(sysdate) changed to current_date +* Replaced triggers with functions+triggers for PG +* There "for each statement" functionality is not supported yet in PG, there will be no problem as long as the user uses the admin interface since deletions are done only once +* adserver-create.sql - changed trigger code for adv_group_count_afr_del, doesn't work since both triggers are called for each row, and data changed in first trigger does not reflect in second Index: openacs-4/packages/adserver/sql/oracle/adserver-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/sql/oracle/adserver-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/sql/oracle/adserver-create.sql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,469 @@ +-- +-- packages/adserver/sql/adserver-create.sql +-- +-- @author jerry@hollyjerry.org +-- @creation-date 2000-10-15 +-- @cvs-id $Id: adserver-create.sql,v 1.1 2002/01/30 11:27:38 roelc Exp $ +-- + +---------------------------------------- +---------------------------------------- +-- DEFINE TABLES +---------------------------------------- +---------------------------------------- + +-- a table of advertisements +create table advs ( + adv_key varchar(200) primary key, + -- this is useful for integrating with + -- third-party ad products and services + local_image_p char(1) default 't' + constraint advs_local_img_p + check (local_image_p in ('t','f')), + -- 't' indicates that target_url contains lots of html and + -- this ad should not get wrapped in the clickthrough counter. + -- This is useful for doubleclick, etc. where they've got + -- javascript and other nonsense wrapping the ad + track_clickthru_p char(1) default 't' + constraint advs_trk_clk_p + check (track_clickthru_p in ('t','f')), + -- a stub, relative to [ns_info pageroot] if local_image_p, or + -- a url if !local_image_p + adv_filename varchar(200), + target_url varchar(4000), + adv_number integer default null +); + +-- **** move the unique index into a separate tablespace +-- constraint adv_log_u unique (adv_key,entry_date) +-- using index tablespace photonet_index + +create table adv_log ( + adv_key not null references advs on delete cascade, + entry_date date not null, + display_count integer default 0, + click_count integer default 0, + unique(adv_key,entry_date) +); + +-- for publishers who want to get fancy + +create table adv_user_map ( + user_id references users on delete cascade, + adv_key references advs on delete cascade, + event_time date not null, + -- will generally be 'd' (displayed) 'c' (clicked through) + event_type char(1) +); + + +create index adv_user_map_idx on adv_user_map(user_id); + +/* + +-- commented out until ACS 4 categories gets developed. + +-- for publishers who want to get really fancy + +create table adv_categories ( + adv_key not null references advs + on delete cascade, + category_id integer not null references categories + on delete cascade, + unique(adv_key, category_id) +); + +*/ + +-- a table of advertisement properties, namely keeping the +-- track of the number of ads in the database +create table advs_properties ( + adv_count integer +); +insert into advs_properties values (0); + +-- a table of adnumbers that have been deleted and that +-- have yet to be reflected in the adv_count +create table advs_swaps ( + swap integer +); + +-------------------------------------------------- +-------------------------------------------------- +-- stuff built on top of the raw ad server layer +-------------------------------------------------- +-------------------------------------------------- + +-- this is for publishers who want to rotate ads within a group + +-- any ad can be a member of one or more adv_groups +-- this table lists each ad group (something like 'sports ads', +-- or 'pokemon ads', or 'XXX ads') and this table keeps +-- track of the number of ads in each group + +create table adv_groups ( + group_key varchar(30) not null primary key, + pretty_name varchar(50), + adv_count integer default(0), -- number of advs in group + -- need to define some rotation methods + -- sequential: show the ads to THAT user + -- in the order specified in adv_group_map + -- least-exposure-first: show the ad that has been shown the + -- least number of times that day; random: show a random ad + rotation_method char(35) default 'sequential' + constraint ad_grp_rotation_method + check (rotation_method in ( + 'sequential', + 'least-exposure-first', + 'random')) +); + + + +-- a relationship: this ad is in this group and has this adnumber. +-- Is there an oracle bug here? +-- if the second on delete cascade is present, then when this +-- script is sourced, if you type +-- delete from advs +-- then your Oracle session will crash: +-- delete from advs +-- * +-- ERROR at line 1: +-- ORA-03113: end-of-file on communication channel + +create table adv_group_map ( + group_key not null references adv_groups on delete cascade, + -- ORACLE BUG NOTE: why is this on delete cascade bad? + -- to see the oracle bug, comment out the line with the comma, + -- and uncomment the on delete cascade line + adv_key not null references advs + , + -- on delete cascade, + adv_group_number integer default null, + primary key (group_key,adv_key) +); + +-- a table of ads that were deleted from a group and has yet +-- to be reflected in that groups adv_count. +create table adv_group_swaps ( + group_key not null references adv_groups on delete cascade, + swap integer +); + + +-- This view is used to select ads for display based on the current +-- days impression count +create or replace view advs_todays_log AS +SELECT * FROM adv_log WHERE entry_date = TRUNC(sysdate); + +-- insert into advs ( +-- adv_key, local_image_p, track_clickthru_p, adv_filename, target_url +-- ) values ( +-- 'ArsDigita', 't', 't', 'arsdigita.gif', 'http://www.arsdigita.com' +-- ); + +commit; + +-------------------------------------------------- +-------------------------------------------------- +-- TRIGGERS TO MAINTAIN AD COUNT FOR ALL ADS +-------------------------------------------------- +-------------------------------------------------- + +/* + +I have several triggers defined to help me maintain the adv_count of +all ads. I need two triggers and the advs_swaps to get around the +mutating advs table; + +Some of this code and it's model is discussed here +http://www.arsdigita.com/bboard/q-and-a-fetch-msg?msg_id=000KZ0&topic_id=web%2fdb&topic= + +On insert, we find the current adv_count for all ads and use that +for the adv_number, and then we increment the adv_count. + +On deletions, two triggers run: a row trigger inserts adv_number of +the ad being deleted into the advs_swaps table. When all the row +triggers are done, a statement trigger sweeps over all the numbers to +be in the advs_swaps table and for each number it finds there, it +finds the entry with the highest adv_number and changes adv_number to +be the number in the swap table. Having done that it deletes the row +in the swap table. And repeats for the next row. + +Here's the insertion of a new ad trigger: + +*/ + +-- trigger to insert an advertisement and +-- automatically determine/maintain the highest +-- advertisement number +create or replace trigger advs_count_bfr_insert +before insert on advs +for each row +declare + top integer; +begin + -- advs_properties is guaranteed to exist + select adv_count + into top + from advs_properties + for update; + + :new.adv_number := top; + + update advs_properties + set adv_count = adv_count + 1; +end; +/ +show errors; + +-- row level trigger to "save" an intermediate +-- adnumber to be swapped for the "high" adnumber. +-- for each row to be deleted do: +create or replace trigger advs_count_afr_del_row +after delete on advs +for each row +begin + insert into advs_swaps values (:old.adv_number); +end; +/ +show errors; + +-- statement level trigger to perform the swaps. +create or replace trigger advs_count_afr_del +after delete on advs +declare + next integer; + cursor sc is select swap + from advs_swaps + order by swap desc; + s sc%rowtype; +begin + -- find the highest numbered ad + -- advs_properties is guaranteed to exist. + select adv_count + into next + from advs_properties + for update; -- do I need the for update? + + -- for each adnumber to be swapped do + for s in sc loop + + -- find the ad that has that number and renumber it + update advs + set adv_number = s.swap + where adv_number = next - 1; + + -- delete the row + delete + from advs_swaps + where swap = s.swap; + + next := next - 1; + end loop; + + -- update the highest number + update advs_properties + set adv_count = next; + +end; +/ +show errors; + + + +-------------------------------------------------- +-------------------------------------------------- +-- TRIGGERS TO MAINTAIN AD COUNT FOR GROUPED ADS +-------------------------------------------------- +-------------------------------------------------- + +/* + +I have several triggers defined to help me maintain the +adv_group_number. I need two triggers and the adv_group_swap to get +around the mutating adv_group_map table; + +On insert, we find the current adv_count for that group and use that +for the adv_group_number, and then we increment the adv_count. + +On deletions, two triggers run: a row trigger inserts adv_group_number +of the ad being deleted into the adv_group_swap table. When all the +row triggers are done, a statement trigger sweeps over all the numbers +to be in the adv_group_swaps table and for each number it finds there, +it finds the entry with the highest adv_group_number in the +adv_group_map table (for the same group) and changes adv_group_number +to be the number in the swap table. Having done that it deletes the +row in the swap table. And repeats for the next row. + +Here's the insertion of a new ad into a group trigger: + +*/ + +create or replace trigger adv_group_count_bfr_insert +before insert on adv_group_map +for each row +declare + top integer; +begin + select adv_count + into top + from adv_groups + where group_key = :new.group_key for update; + + :new.adv_group_number := top; + + update adv_groups + set adv_count = adv_count + 1 + where group_key = :new.group_key; +end; +/ +show errors; + +/* + +And here are the two triggers that maintain the top count after a row +is deleted: + +*/ + +create or replace trigger adv_group_count_afr_del_row +after delete on adv_group_map +for each row +begin + insert into adv_group_swaps (group_key, swap) + values (:old.group_key, :old.adv_group_number); +end; +/ +show errors; + +create or replace trigger adv_group_count_afr_del +after delete on adv_group_map +declare + c integer; + next integer; + cursor sc is select swap, group_key + from adv_group_swaps + order by group_key, swap desc; + s sc%ROWTYPE; +begin + select count(*) into c from adv_groups; + if c > 0 then -- guard (that should be unneeded and appears + next := -1; + + -- for each row in the swap table do + for s in sc loop + -- find the highest numbered ad in that group + select adv_count + into next + from adv_groups + where group_key = s.group_key + for update; -- do I need the for update? + + -- find the ad with that number and renumber it + update adv_group_map + set adv_group_number = s.swap + where adv_group_number = next - 1 + and group_key = s.group_key; + + -- delete the row from the swap table + delete + from adv_group_swaps + where swap = s.swap + and group_key = s.group_key; + + next := next - 1; + + -- reset the value of the highest numbered ad + update adv_groups + set adv_count = next + where group_key = s.group_key; + end loop; + end if; +exception + when no_data_found then + next := 1; -- how do I do a nop here? + when others then + rollback; +end; +/ +show errors; + + +/* +-- test cases +delete from advs; +delete from advs_properties; +delete from advs_swaps; +insert into advs_properties values (0); +insert into advs values('a', 't', 't', 'abc', 'http://abc',null); +insert into advs values('b', 't', 't', 'abc', 'http://abc',null); +insert into advs values('c', 't', 't', 'abc', 'http://abc',null); +insert into advs values('d', 't', 't', 'abc', 'http://abc',null); +insert into advs values('e', 't', 't', 'abc', 'http://abc',null); +insert into advs values('f', 't', 't', 'abc', 'http://abc',null); +insert into advs values('g', 't', 't', 'abc', 'http://abc',null); +insert into advs values('h', 't', 't', 'abc', 'http://abc',null); +insert into advs values('i', 't', 't', 'abc', 'http://abc',null); +insert into advs values('j', 't', 't', 'abc', 'http://abc',null); +insert into advs values('k', 't', 't', 'abc', 'http://abc',null); +insert into advs values('l', 't', 't', 'abc', 'http://abc',null); +insert into advs values('m', 't', 't', 'abc', 'http://abc',null); +insert into advs values('n', 't', 't', 'abc', 'http://abc',null); +insert into advs values('o', 't', 't', 'abc', 'http://abc',null); +insert into advs values('p', 't', 't', 'abc', 'http://abc',null); +insert into advs values('q', 't', 't', 'abc', 'http://abc',null); +insert into advs values('r', 't', 't', 'abc', 'http://abc',null); +insert into advs values('s', 't', 't', 'abc', 'http://abc',null); +insert into advs values('t', 't', 't', 'abc', 'http://abc',null); +insert into advs values('u', 't', 't', 'abc', 'http://abc',null); +insert into advs values('v', 't', 't', 'abc', 'http://abc',null); +insert into advs values('w', 't', 't', 'abc', 'http://abc',null); +insert into advs values('x', 't', 't', 'abc', 'http://abc',null); +insert into advs values('y', 't', 't', 'abc', 'http://abc',null); +insert into advs values('z', 't', 't', 'abc', 'http://abc',null); +select adv_key, adv_number, adv_count from advs, advs_properties; + +delete from adv_groups; +delete from adv_group_map; +delete from adv_group_swaps; + +insert into adv_groups (group_key, pretty_name, adv_count) + values('aaa', 'aaa', 0); +insert into adv_groups (group_key, pretty_name, adv_count) + values('bbb', 'bbb', 0); +insert into adv_groups (group_key, pretty_name, adv_count) + values('ccc', 'ccc', 0); +insert into adv_groups (group_key, pretty_name, adv_count) + values('ddd', 'ddd', 0); + +insert into adv_group_map (group_key, adv_key) values('aaa', 'a'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'b'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'c'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'd'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'e'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'f'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'g'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'h'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'i'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'j'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'k'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'l'); +insert into adv_group_map (group_key, adv_key) values('bbb', 'm'); +insert into adv_group_map (group_key, adv_key) values('bbb', 'n'); +insert into adv_group_map (group_key, adv_key) values('bbb', 'o'); +insert into adv_group_map (group_key, adv_key) values('bbb', 'p'); +insert into adv_group_map (group_key, adv_key) values('bbb', 'q'); +insert into adv_group_map (group_key, adv_key) values('bbb', 'r'); +insert into adv_group_map (group_key, adv_key) values('aaa', 's'); +insert into adv_group_map (group_key, adv_key) values('ccc', 't'); +insert into adv_group_map (group_key, adv_key) values('ccc', 'u'); +insert into adv_group_map (group_key, adv_key) values('ccc', 'v'); +insert into adv_group_map (group_key, adv_key) values('ccc', 'w'); +insert into adv_group_map (group_key, adv_key) values('ddd', 'x'); +insert into adv_group_map (group_key, adv_key) values('ddd', 'y'); +insert into adv_group_map (group_key, adv_key) values('ddd', 'z'); + +commit; + +*/ Index: openacs-4/packages/adserver/sql/oracle/adserver-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/sql/oracle/adserver-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/sql/oracle/adserver-drop.sql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,21 @@ +-- +-- packages/adserver/sql/adserver-drop.sql +-- +-- @author jerry@hollyjerry.org +-- @creation-date 2000-10-15 +-- @cvs-id $Id: adserver-drop.sql,v 1.1 2002/01/30 11:27:38 roelc Exp $ +-- + +drop table adv_log; +drop table adv_user_map; + +-- drop table adv_categories; +-- drop table adv_keyword_map; +drop table adv_group_map; +drop table adv_group_swaps; +drop table adv_groups; + +drop table advs_properties; +drop table advs_swaps; + +drop table advs; Index: openacs-4/packages/adserver/sql/postgresql/adserver-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/sql/postgresql/adserver-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/sql/postgresql/adserver-create.sql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,436 @@ +-- +-- packages/adserver/sql/adserver-create.sql +-- +-- @author jerry@hollyjerry.org +-- @creation-date 2000-10-15 +-- @cvs-id $Id: adserver-create.sql,v 1.1 2002/01/30 11:27:38 roelc Exp $ +-- + +---------------------------------------- +---------------------------------------- +-- DEFINE TABLES +---------------------------------------- +---------------------------------------- + +-- a table of advertisements +create table advs ( + adv_key varchar(200) primary key, + -- this is useful for integrating with + -- third-party ad products and services + local_image_p char(1) default 't' + constraint advs_local_img_p + check (local_image_p in ('t','f')), + -- 't' indicates that target_url contains lots of html and + -- this ad should not get wrapped in the clickthrough counter. + -- This is useful for doubleclick, etc. where they've got + -- javascript and other nonsense wrapping the ad + track_clickthru_p char(1) default 't' + constraint advs_trk_clk_p + check (track_clickthru_p in ('t','f')), + -- a stub, relative to [ns_info pageroot] if local_image_p, or + -- a url if !local_image_p + adv_filename varchar(200), + target_url varchar(4000), + adv_number integer default null +); + +-- **** move the unique index into a separate tablespace +-- constraint adv_log_u unique (adv_key,entry_date) +-- using index tablespace photonet_index + +create table adv_log ( + adv_key varchar(200) not null references advs on delete cascade, + entry_date date not null, + display_count integer default 0, + click_count integer default 0, + unique(adv_key,entry_date) +); + +-- for publishers who want to get fancy + +create table adv_user_map ( + user_id integer references users on delete cascade, + adv_key varchar(200) references advs on delete cascade, + event_time date not null, + -- will generally be 'd' (displayed) 'c' (clicked through) + event_type char(1) +); + + +create index adv_user_map_idx on adv_user_map(user_id); + +/* + +-- commented out until ACS 4 categories gets developed. + +-- for publishers who want to get really fancy + +create table adv_categories ( + adv_key not null references advs + on delete cascade, + category_id integer not null references categories + on delete cascade, + unique(adv_key, category_id) +); + +*/ + +-- a table of advertisement properties, namely keeping the +-- track of the number of ads in the database +create table advs_properties ( + adv_count integer +); +insert into advs_properties values (0); + +-- a table of adnumbers that have been deleted and that +-- have yet to be reflected in the adv_count +create table advs_swaps ( + swap integer +); + +-------------------------------------------------- +-------------------------------------------------- +-- stuff built on top of the raw ad server layer +-------------------------------------------------- +-------------------------------------------------- + +-- this is for publishers who want to rotate ads within a group + +-- any ad can be a member of one or more adv_groups +-- this table lists each ad group (something like 'sports ads', +-- or 'pokemon ads', or 'XXX ads') and this table keeps +-- track of the number of ads in each group + +create table adv_groups ( + group_key varchar(30) not null primary key, + pretty_name varchar(50), + adv_count integer default(0), -- number of advs in group + -- need to define some rotation methods + -- sequential: show the ads to THAT user + -- in the order specified in adv_group_map + -- least-exposure-first: show the ad that has been shown the + -- least number of times that day; random: show a random ad + rotation_method char(35) default 'sequential' + constraint ad_grp_rotation_method + check (rotation_method in ( + 'sequential', + 'least-exposure-first', + 'random')) +); + + + +-- a relationship: this ad is in this group and has this adnumber. +-- Is there an oracle bug here? +-- if the second on delete cascade is present, then when this +-- script is sourced, if you type +-- delete from advs +-- then your Oracle session will crash: +-- delete from advs +-- * +-- ERROR at line 1: +-- ORA-03113: end-of-file on communication channel + +create table adv_group_map ( + group_key varchar(30) not null references adv_groups on delete cascade, + -- ORACLE BUG NOTE: why is this on delete cascade bad? + -- to see the oracle bug, comment out the line with the comma, + -- and uncomment the on delete cascade line + adv_key varchar(200) not null references advs + , + -- on delete cascade, + adv_group_number integer default null, + primary key (group_key,adv_key) +); + +-- a table of ads that were deleted from a group and has yet +-- to be reflected in that groups adv_count. +create table adv_group_swaps ( + group_key varchar(30) not null references adv_groups on delete cascade, + swap integer +); + + +-- This view is used to select ads for display based on the current +-- days impression count +create view advs_todays_log AS +SELECT * FROM adv_log WHERE entry_date = current_date; + +-- insert into advs ( +-- adv_key, local_image_p, track_clickthru_p, adv_filename, target_url +-- ) values ( +-- 'ArsDigita', 't', 't', 'arsdigita.gif', 'http://www.arsdigita.com' +-- ); + +commit; + +-------------------------------------------------- +-------------------------------------------------- +-- TRIGGERS TO MAINTAIN AD COUNT FOR ALL ADS +-------------------------------------------------- +-------------------------------------------------- + +/* + +I have several triggers defined to help me maintain the adv_count of +all ads. I need two triggers and the advs_swaps to get around the +mutating advs table; + +Some of this code and it's model is discussed here +http://www.arsdigita.com/bboard/q-and-a-fetch-msg?msg_id=000KZ0&topic_id=web%2fdb&topic= + +On insert, we find the current adv_count for all ads and use that +for the adv_number, and then we increment the adv_count. + +On deletions, two triggers run: a row trigger inserts adv_number of +the ad being deleted into the advs_swaps table. When all the row +triggers are done, a statement trigger sweeps over all the numbers to +be in the advs_swaps table and for each number it finds there, it +finds the entry with the highest adv_number and changes adv_number to +be the number in the swap table. Having done that it deletes the row +in the swap table. And repeats for the next row. + +Here's the insertion of a new ad trigger: + +*/ + +-- trigger to insert an advertisement and +-- automatically determine/maintain the highest +-- advertisement number +create function advs_count_bfr_insert_fun() returns opaque as ' +declare + top integer; +begin + -- advs_properties is guaranteed to exist + select adv_count + into top + from advs_properties; + -- for update; + + new.adv_number := top; + + update advs_properties + set adv_count = adv_count + 1; + + return new; +end; +' language 'plpgsql'; + +create trigger advs_count_bfr_insert +before insert on advs +for each row execute procedure advs_count_bfr_insert_fun(); + +-- row level trigger to "save" an intermediate +-- adnumber to be swapped for the "high" adnumber. +-- for each row to be deleted do: +create function advs_count_afr_del_row_fun() returns opaque as ' +begin + insert into advs_swaps values (old.adv_number); + + return new; +end; +' language 'plpgsql'; + +create trigger advs_count_afr_del_row +after delete on advs +for each row execute procedure advs_count_afr_del_row_fun(); + +-- statement level trigger to perform the swaps. +create function advs_count_afr_del_fun() returns opaque as ' +declare + next integer; + s record; +begin + -- find the highest numbered ad + -- advs_properties is guaranteed to exist. + select adv_count + into next + from advs_properties; + -- for update; -- do I need the for update? + + -- for each adnumber to be swapped do + for s in select swap from advs_swaps order by swap desc loop + + -- find the ad that has that number and renumber it + update advs + set adv_number = s.swap + where adv_number = next - 1; + + -- delete the row + delete + from advs_swaps + where swap = s.swap; + + next := next - 1; + end loop; + + -- update the highest number + update advs_properties + set adv_count = next; + + return new; +end; +' language 'plpgsql'; + +create trigger advs_count_afr_del +after delete on advs for each row execute procedure advs_count_afr_del_fun(); + + + +-------------------------------------------------- +-------------------------------------------------- +-- TRIGGERS TO MAINTAIN AD COUNT FOR GROUPED ADS +-------------------------------------------------- +-------------------------------------------------- + +/* + +I have several triggers defined to help me maintain the +adv_group_number. I need two triggers and the adv_group_swap to get +around the mutating adv_group_map table; + +On insert, we find the current adv_count for that group and use that +for the adv_group_number, and then we increment the adv_count. + +On deletions, two triggers run: a row trigger inserts adv_group_number +of the ad being deleted into the adv_group_swap table. When all the +row triggers are done, a statement trigger sweeps over all the numbers +to be in the adv_group_swaps table and for each number it finds there, +it finds the entry with the highest adv_group_number in the +adv_group_map table (for the same group) and changes adv_group_number +to be the number in the swap table. Having done that it deletes the +row in the swap table. And repeats for the next row. + +Here's the insertion of a new ad into a group trigger: + +*/ + +create function adv_group_count_bfr_insert_fun() returns opaque as ' +declare + top integer; +begin + select adv_count + into top + from adv_groups + where group_key = new.group_key; -- for update; + + new.adv_group_number := top; + + update adv_groups + set adv_count = adv_count + 1 + where group_key = new.group_key; + + return new; +end; +' language 'plpgsql'; + + +create trigger adv_group_count_bfr_insert +before insert on adv_group_map +for each row execute procedure adv_group_count_bfr_insert_fun(); + +/* + +And here are the two triggers that maintain the top count after a row +is deleted: + +*/ + +create function adv_group_count_afr_del_row_fun() returns opaque as ' +begin + update adv_group_map set adv_group_number=adv_group_number-1 where adv_group_number>old.adv_group_number and group_key=old.group_key; + + update adv_groups set adv_count=adv_count-1 where group_key=old.group_key; + + return new; +end; +' language 'plpgsql'; + + +create trigger adv_group_count_afr_del_row +after delete on adv_group_map +for each row execute procedure adv_group_count_afr_del_row_fun(); + +insert into advs (adv_key, local_image_p, track_clickthru_p, adv_filename, target_url) +values ('ArsDigita', 't', 't', 'arsdigita.gif', 'http://www.arsdigita.com'); +insert into advs (adv_key, local_image_p, track_clickthru_p, adv_filename, target_url) +values ('SemiZone', 't', 't', 'semizone_ad.gif', 'http://www.semizone.com'); +insert into advs (adv_key, local_image_p, track_clickthru_p, adv_filename, target_url) +values ('Stanford', 't', 't', 'stanford_ad.gif', 'http://www.stanford.com'); +/* +-- test cases +delete from advs; +delete from advs_properties; +delete from advs_swaps; +insert into advs_properties values (0); +insert into advs values('a', 't', 't', 'abc', 'http://abc',null); +insert into advs values('b', 't', 't', 'abc', 'http://abc',null); +insert into advs values('c', 't', 't', 'abc', 'http://abc',null); +insert into advs values('d', 't', 't', 'abc', 'http://abc',null); +insert into advs values('e', 't', 't', 'abc', 'http://abc',null); +insert into advs values('f', 't', 't', 'abc', 'http://abc',null); +insert into advs values('g', 't', 't', 'abc', 'http://abc',null); +insert into advs values('h', 't', 't', 'abc', 'http://abc',null); +insert into advs values('i', 't', 't', 'abc', 'http://abc',null); +insert into advs values('j', 't', 't', 'abc', 'http://abc',null); +insert into advs values('k', 't', 't', 'abc', 'http://abc',null); +insert into advs values('l', 't', 't', 'abc', 'http://abc',null); +insert into advs values('m', 't', 't', 'abc', 'http://abc',null); +insert into advs values('n', 't', 't', 'abc', 'http://abc',null); +insert into advs values('o', 't', 't', 'abc', 'http://abc',null); +insert into advs values('p', 't', 't', 'abc', 'http://abc',null); +insert into advs values('q', 't', 't', 'abc', 'http://abc',null); +insert into advs values('r', 't', 't', 'abc', 'http://abc',null); +insert into advs values('s', 't', 't', 'abc', 'http://abc',null); +insert into advs values('t', 't', 't', 'abc', 'http://abc',null); +insert into advs values('u', 't', 't', 'abc', 'http://abc',null); +insert into advs values('v', 't', 't', 'abc', 'http://abc',null); +insert into advs values('w', 't', 't', 'abc', 'http://abc',null); +insert into advs values('x', 't', 't', 'abc', 'http://abc',null); +insert into advs values('y', 't', 't', 'abc', 'http://abc',null); +insert into advs values('z', 't', 't', 'abc', 'http://abc',null); +select adv_key, adv_number, adv_count from advs, advs_properties; + +delete from adv_groups; +delete from adv_group_map; +delete from adv_group_swaps; + +insert into adv_groups (group_key, pretty_name, adv_count) + values('aaa', 'aaa', 0); +insert into adv_groups (group_key, pretty_name, adv_count) + values('bbb', 'bbb', 0); +insert into adv_groups (group_key, pretty_name, adv_count) + values('ccc', 'ccc', 0); +insert into adv_groups (group_key, pretty_name, adv_count) + values('ddd', 'ddd', 0); + +insert into adv_group_map (group_key, adv_key) values('aaa', 'a'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'b'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'c'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'd'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'e'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'f'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'g'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'h'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'i'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'j'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'k'); +insert into adv_group_map (group_key, adv_key) values('aaa', 'l'); +insert into adv_group_map (group_key, adv_key) values('bbb', 'm'); +insert into adv_group_map (group_key, adv_key) values('bbb', 'n'); +insert into adv_group_map (group_key, adv_key) values('bbb', 'o'); +insert into adv_group_map (group_key, adv_key) values('bbb', 'p'); +insert into adv_group_map (group_key, adv_key) values('bbb', 'q'); +insert into adv_group_map (group_key, adv_key) values('bbb', 'r'); +insert into adv_group_map (group_key, adv_key) values('aaa', 's'); +insert into adv_group_map (group_key, adv_key) values('ccc', 't'); +insert into adv_group_map (group_key, adv_key) values('ccc', 'u'); +insert into adv_group_map (group_key, adv_key) values('ccc', 'v'); +insert into adv_group_map (group_key, adv_key) values('ccc', 'w'); +insert into adv_group_map (group_key, adv_key) values('ddd', 'x'); +insert into adv_group_map (group_key, adv_key) values('ddd', 'y'); +insert into adv_group_map (group_key, adv_key) values('ddd', 'z'); + +commit; + +*/ Index: openacs-4/packages/adserver/sql/postgresql/adserver-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/sql/postgresql/adserver-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/sql/postgresql/adserver-drop.sql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,34 @@ +-- +-- packages/adserver/sql/adserver-drop.sql +-- +-- @author jerry@hollyjerry.org +-- @creation-date 2000-10-15 +-- @cvs-id $Id: adserver-drop.sql,v 1.1 2002/01/30 11:27:38 roelc Exp $ +-- + +drop view advs_todays_log; +drop trigger advs_count_bfr_insert on advs; +drop function advs_count_bfr_insert_fun(); +drop trigger advs_count_afr_del_row on advs; +drop function advs_count_afr_del_row_fun(); +drop trigger advs_count_afr_del on advs; +drop function advs_count_afr_del_fun(); +drop trigger adv_group_count_bfr_insert on adv_group_map; +drop function adv_group_count_bfr_insert_fun(); +drop trigger adv_group_count_afr_del_row on adv_group_map; +drop function adv_group_count_afr_del_row_fun(); + + +drop table adv_log; +drop table adv_user_map; + +-- drop table adv_categories; +-- drop table adv_keyword_map; +drop table adv_group_map; +drop table adv_group_swaps; +drop table adv_groups; + +drop table advs_properties; +drop table advs_swaps; + +drop table advs; Index: openacs-4/packages/adserver/tcl/CHANGES =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/tcl/CHANGES,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/tcl/CHANGES 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,2 @@ +* adserver-procs.tcl - multiple queries not extracted, query names in "set query_name ", moved to adserver-procs.xql +* adserver-procs.tcl - changed outer join (+) to UNION statement Index: openacs-4/packages/adserver/tcl/adserver-ad-monitor-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/tcl/adserver-ad-monitor-procs.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/tcl/adserver-ad-monitor-procs.tcl 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,35 @@ +# /tcl/ad-monitor.tcl + +ad_library { + +Internal error monitors, beyond the static files in /SYSTEM that +external monitors such as Uptime use. + +The overall goal here is that the ad_host_administrator gets notified +if something is horribly wrong, but not more than once every 15 +minutes. + +We store the last [ns_time] (seconds since 1970) notification time in +ad_host_administrator_last_notified + + @creation-date 6 Nov 1998 + @author philg@mit.edu + @cvs-id ad-monitor.tcl,v 3.0.14.2 2000/07/25 11:27:48 ron Exp +} + +ns_share -init { set ad_host_administrator_last_notified 0 } ad_host_administrator_last_notified + +proc adserver_notify_host_administrator {subject body {log_p 0}} { + ns_share ad_host_administrator_last_notified + if $log_p { + # usually the error will be in the error log anyway + ns_log Notice "ad_notify_host_administrator: $subject\n\n$body\n\n" + } + if { [ns_time] > [expr $ad_host_administrator_last_notified + 900] } { + # more than 15 minutes have elapsed since last note + set ad_host_administrator_last_notified [ns_time] + if [catch { ns_sendmail [ad_host_administrator] [ad_system_owner] $subject $body } errmsg] { + ns_log Error "failed sending email note to [ad_host_administrator]" + } + } +} Index: openacs-4/packages/adserver/tcl/adserver-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/tcl/adserver-procs-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/tcl/adserver-procs-oracle.xql 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,67 @@ + + + + oracle8.1.6 + + + + + select site_node.url(s.node_id) + from site_nodes s, apm_packages a + where s.object_id = a.package_id + and a.package_key = 'adserver' + + + + + + + + + update adv_log + set display_count = display_count + 1 + where adv_key = :adv_key + and entry_date = trunc (sysdate) + + + + + + + + insert into adv_log + (adv_key, entry_date, display_count) + values (:adv_key, + trunc (sysdate), + (select 1 from dual + where 0 = (select count (*) + from adv_log + where adv_key = :adv_key + and entry_date = trunc (sysdate)))) + + + + + + + + insert into adv_user_map (user_id, adv_key, event_time, event_type) + values (:user_id,:adv_key,sysdate,'d') + + + + + + + + select map.adv_key, track_clickthru_p, target_url + from adv_group_map map, advs_todays_log log, advs + where rownum=1 + and group_key = :group_key + and map.adv_key = advs.adv_key + and map.adv_key = log.adv_key + order by nvl (display_count, 0) + + + + Index: openacs-4/packages/adserver/tcl/adserver-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/tcl/adserver-procs-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/tcl/adserver-procs-postgresql.xql 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,78 @@ + + + + postgresql7.1 + + + + + select site_node__url(s.node_id) + from site_nodes s, apm_packages a + where s.object_id = a.package_id + and a.package_key = 'adserver' + + + + + + + + + update adv_log + set display_count = display_count + 1 + where adv_key = :adv_key + and entry_date = trunc (current_timestamp) + + + + + + + + insert into adv_log + (adv_key, entry_date, display_count) + values (:adv_key, + trunc (current_timestamp), + (select 1 + where 0 = (select count (*) + from adv_log + where adv_key = :adv_key + and entry_date = trunc (current_timestamp)))) + + + + + + + + insert into adv_user_map (user_id, adv_key, event_time, event_type) + values (:user_id,:adv_key,current_timestamp,'d') + + + + + + + + select map.adv_key, track_clickthru_p, target_url, display_count + from adv_group_map map, advs_todays_log log, advs + where group_key = :group_key + and map.adv_key = advs.adv_key + and map.adv_key = log.adv_key + + UNION + + select map.adv_key, track_clickthru_p, target_url, 0 as display_count + from adv_group_map map, advs + where group_key = :group_key + and map.adv_key = advs.adv_key + and (select count(*) from adv_log where advs.adv_key=adv_log.adv_key)=0 + + order by display_count asc + + limit 1 + + + + + Index: openacs-4/packages/adserver/tcl/adserver-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/tcl/adserver-procs.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/tcl/adserver-procs.tcl 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,516 @@ +ad_library { + definitions for the ad server; adserver_get_ad_html is called by + .tcl, .adp, or .html pages (by filters, presumably) to generate ad + IMGs (linked to HREFs). An API for managing database queries. + + + @creation-date 11/15/2000 + @author modified 11/15/2000 by jerry@hollyjerry.org + @author modified 07/13/2000 by mchu@arsdigita.com + @cvs-id $Id: adserver-procs.tcl,v 1.1 2002/01/30 11:25:25 roelc Exp $ + +} + +############################################################ +### internal cache helper function +############################################################ +ad_proc -private adserver_cache_refresh {} { + } { + return [util_memoize {adserver_cache_refresh_mem} 300] +} + +ad_proc -private adserver_cache_refresh_mem {} { + } { + return [ad_parameter -package_id [ad_acs_adserver_id] CacheRefresh 600] +} + + +############################################################ +### external api - encapsulate other acs modules +### +### the ACS is 1% inspiration, 99% perspiration +### maybe next time, aD will shell out another 1% to develop an API +### that encapsulates one modules design from another +############################################################ + +### our package id +ad_proc -public ad_acs_adserver_id {} { + @return The object id of the adserver if it exists, 0 otherwise. + } { + return [util_memoize {ad_acs_adserver_id_mem} 300] +} + +ad_proc -private ad_acs_adserver_id_mem {} {} { + if {[db_table_exists apm_packages]} { + return [db_string acs_adserver_id_get { + select package_id from apm_packages + where package_key = 'adserver' + } -default 0] + } else { + return 0 + } +} + +### the url to get to an ad +ad_proc -public ad_acs_adserver_url {} { + @return The url of the adserver mountpoint if it exists, 0 otherwise. + } { + return [util_memoize {ad_acs_adserver_url_mem} [adserver_cache_refresh]] +} + +ad_proc -private ad_acs_adserver_url_mem {} {} { + if {[db_table_exists apm_packages]} { + return [db_string acs_adserver_mountpoint { + select site_node.url(s.node_id) + from site_nodes s, apm_packages a + where s.object_id = a.package_id + and a.package_key = 'adserver' + } -default 0] + } else { + return 0 + } +} + +ad_proc -public ad_acs_adserver_pageroot {} { + @return The pathname in the filesystem of the adserver www/ directory + } { + return [util_memoize {ad_acs_adserver_pageroot_mem} [adserver_cache_refresh]] +} + +ad_proc -private ad_acs_adserver_pageroot_mem {} {} { + return "[acs_root_dir]/packages/adserver/www" +} + +############################################################ +### basic get ad function - tries to handle all details +############################################################ + +ad_proc -public adserver_get_ad_html { + {-user_id ""} + {-method ""} + {-ad_number ""} + {-suppress_logging:boolean} + {-adv_key ""} + {group_key ""} + {extra_img_tags "border=0"} + } { + Gets an ad. Try's to make it user specific. + + If method is not supplied, it uses the natural ad + selection method of the group. Otherwise, it follows method, + which may be one of least-exposure-first, user-sequential, + or random. + + if the ad_number is not blank, it should be an integer specifying + the number of the ad within a group to be retrieved. + + extra_img_tags are not used if track_clickthru is not set + + the string $timestamp in the url will be replaced with the + current timestamp + + } { + + ############################################ + ### part one: build sql to find the right ad + ############################################ + + if {![string equal $adv_key ""]} { + set sql_query " + select track_clickthru_p, target_url + from advs + where adv_key = :adv_key + " + set query_name adserver_get_ad_by_ad_key + } elseif {[string equal $group_key ""]} { + + if {[string is integer -strict $ad_number]} { + set sql_query " + select track_clickthru_p, target_url, adv_key + from advs + where adv_number = :ad_number + " + set query_name adserver_get_ad_by_adnumber + } else { + + set adv_key [adserver_get_random_ad_key] + set query_name adserver_get_ad_by_ad_key + set sql_query " + select track_clickthru_p, target_url + from advs + where adv_key=:adv_key" + } + } else { + if {[string is integer -strict $ad_number]} { + set query_name adserver_get_ad_by_group_and_number + set sql_query " + select a.adv_key, track_clickthru_p, target_url + from advs a, adv_group_map m + where a.adv_key = m.adv_key + and adv_group_number = :ad_number + and m.group_key = :group_key + " + } else { + if {[string equal "" $method]} { + set query_name adserver_get_ad_by_method + set rotation_method [db_string ad_rotation_method " + select rotation_method + from adv_groups + where group_key=:group_key" -default ""] + + set rotation_method [string trim $rotation_method] + } else { + set rotation_method $method + } + + switch $rotation_method { + least-exposure-first { + set query_name adserver_get_ad_least_exposure_first + set sql_query " + select map.adv_key, track_clickthru_p, target_url + from adv_group_map map, advs_todays_log log, advs + where rownum=1 + and group_key = :group_key + and map.adv_key = advs.adv_key + and map.adv_key = log.adv_key (+) + order by nvl (display_count, 0)" + } + user-sequential { + set query_name adserver_get_ad_by_ad_key + if {[string equal "" $user_id]} { + set user_id [ad_get_user_id] + } + + set adv_key [adserver_get_sequential_ad_key \ + -user_id $user_id $group_key] + set sql_query " + select track_clickthru_p, target_url + from advs + where adv_key=:adv_key" + } + random - + default { + set query_name adserver_get_ad_by_ad_key + set adv_key [adserver_get_random_ad_key $group_key] + set sql_query " + select track_clickthru_p, target_url from advs + where adv_key=:adv_key" + } + } + } + } + + ########################################## + ### part two: get the ad from the db + ### check for the default case + ########################################## + + if {![db_0or1row $query_name $sql_query]} { + # couldn't even find one row, use the default ad + ns_log warning "adserver_get_ad_html asked for an ad " \ + "in the $group_key group but there aren't any (adv_key is $adv_key)" + + set target_url \ + [ad_parameter \ + -package_id [ad_acs_adserver_id] \ + DefaultAdTargetURL /] + set track_clickthru_p t + set adv_key default + } + + # normally we generate the images through a call to adimg + # wrapped in an adhref href. If track_clickthru_p is + # false, just spew out the html contained in target_url forget + # about it. This is how we deal with doubleclick and their ild + + ###################################################### + ### part three: generate the html + ### track the impression now, if necessary + ### or spit out doubleclickish url + ###################################################### + + if {[string equal $track_clickthru_p t]} { + + set h_url [adserver_href_attr \ + -suppress_logging=$suppress_logging_p \ + -adv_key $adv_key $target_url] + + set s_url [adserver_src_attr \ + -suppress_logging=$suppress_logging_p \ + -adv_key $adv_key] + + set result \ + "" + } else { + set result $target_url + + # update the impressions since this won't get called + # through adimg.tcl + db_dml adserver_defs_adv_update " + update adv_log + set display_count = display_count + 1 + where adv_key = :adv_key + and entry_date = trunc (sysdate)" + + set n_rows [db_resultrows] + + if { $n_rows == 0 } { + # there wasn't a row in the database; we can't just do + # the obvious insert because another thread might be + # executing concurrently + db_dml adv_insert " + insert into adv_log + (adv_key, entry_date, display_count) + values (:adv_key, + trunc (sysdate), + (select 1 from dual + where 0 = (select count (*) + from adv_log + where adv_key = :adv_key + and entry_date = trunc (sysdate))))" + } + + if {[ad_conn -connected_p]} { + + if {[util_memoize { + ad_parameter -package_id [ad_acs_adserver_id] DetailedPerUserLoggingP 0 + } [adserver_cache_refresh]]} { + set user_id [ad_get_user_id] + if {$user_id == 0} { + set user_id [db_null] + } + # ignore logged out clicks, for now.... + db_dml adserver_defs_adv_user_insert { + insert into adv_user_map (user_id, adv_key, event_time, event_type) + values (:user_id,:adv_key,sysdate,'d') + } + } + } + } + db_release_unused_handles + + ###################################################### + ### part four: fixup user variables + ###################################################### + + regsub -all {\$timestamp} $result [ns_httptime [ns_time]] result + return $result +} + + +############################################################ +### internal procs to help get an api helper funcions +### divide and conquer - these routines handle easier cases +############################################################ + +### pick a random ad +### dumb routine really, ads should not be randomly picked +ad_proc adserver_get_random_ad_key {{group_key ""}} { + Returns random adv key + } { + if {[string equal "" $group_key]} { + + # no group given, pick an ad at random + set n_available [db_string adserver_count_group_ads " + select adv_count + from advs_properties + " -default 0] + + set adv_key "" + # pick an ad, any ad + if { $n_available > 0} { + set pick [ns_rand $n_available] + set adv_key [db_string adserver_pick " + select adv_key + from advs + where adv_number = :pick + " -default ""] + } + + # return the ad you picked (may be "") + return $adv_key + + } else { + + # count the ads in the group + set n_available [db_string adserver_count_group_ads " + select adv_count + from adv_groups + where group_key = :group_key + " -default 0] + + # if none are present in the group, pick a random ad from all ads + if {$n_available == 0} { + ns_log warning adserver: non existent group $group_key + return [adserver_get_random_ad_key] + } + + # pick a random ad from the group + set pick [ns_rand $n_available] + + # select the ad_key for that ad + set adv_key [db_string adserver_group_get " + select adv_key + from adv_group_map + where adv_group_number = :pick + and group_key = :group_key + " -default ""] + + # if it's blank, pick a random ad from all ads + if {[string equal "" $adv_key]} { + ns_log warning adserver: empty group $group_key + return [adserver_get_random_ad_key] + } + return $adv_key + } +} + +### get the "next" ad in a sequence +ad_proc adserver_get_sequential_ad_key { {-user_id ""} group_key} { + Returns sequential adv_key + } { + if {[string equal "" $user_id]} { + set user_id [ad_get_user_id] + } + + set selection [db_0or1row adserver_adv_key { + select adv_group_number as last, + ag.adv_count max_adv_group_number + from adv_group_map grp, adv_groups ag, adv_user_map map + where user_id=:user_id + and event_time = ( + select max(event_time) + from adv_user_map map2 + where map2.user_id = :user_id + and map2.adv_key = map.adv_key + and map2.event_type = 'd' + ) + and ag.group_key = :group_key + and grp.group_key = :group_key + and grp.adv_key = map.adv_key + and map.user_id = :user_id + and map.event_type = 'd'}] + + if {!$selection} { + set adv_group_number 0 + } else { + if {$adv_group_number == [expr $max_adv_group_number - 1]} { + set adv_group_number 0 + } + } + + set key [db_string adserver_sequential_get { + select adv_key + from adv_group_map + where group_key=:group_key + and adv_group_number=:adv_group_number} -default ""] + + if {[string equal "" $key]} { + set key [adserver_get_random_ad_key] + } + + return $key + +} + +############################################################ +### helper functions to generate href and src attributes +############################################################ + +### generate the href target +ad_proc -private adserver_href_attr { + -suppress_logging:boolean + {-adv_key ""} + target_url + } { + Returns href attribute. + + } { + set ad_url "[ad_acs_adserver_url]adhref.tcl?adv_key=[ad_urlencode $adv_key]" + if {$suppress_logging_p == 1} { + append ad_url "&suppress_logging_p=1" + } + return $ad_url +} + +### generate the image src attribute +### only called when track_clickthru is set +ad_proc -private adserver_src_attr { + -suppress_logging:boolean + {-adv_key ""} + } { + Returns src attribute. + + Passes suppress_logging to adserver_image_url to build the url. + } { + set ad_url "[ad_acs_adserver_url]adimg.tcl?adv_key=[ad_urlencode $adv_key]" + if {$suppress_logging_p == 1} { + append ad_url "&suppress_logging_p=1" + } + return $ad_url +} + +################################################################### +### helper functions to build a file pathanem +################################################################### + +### concatenate two pieces of a url. Gets number of /s right. +ad_proc -private adserver_url_concat {a b} { + joins a & b, ensuring that the right number of slashes are present + } { + set as [string equal / [string range $a end end]] + set bs [string equal / [string range $b 0 0]] + if {$as && $bs} { + return $a[string trimleft $b /] + } else { + if {!$as && !$bs} { + return $a/$b + } else { + return $a$b + } + } +} + +### generate the url for the image src attribute +ad_proc -private adserver_image_url { + ad_url + } { + Builds the url to an image. + + If local_image is true then this routine builds the url to a local + image as follows: + + If the parameter BaseImagePath starts with a /, \, or it's second + char is a :, it assumes the BaseImagePath is the beginning of an + absolute hard drive pathname, and this routine just concatenates + the ad_url to the BaseImagePath. + + Otherwise, this routine builds the image path by concatenating: + [ad_acs_adserver_pageroot]/$BaseImagePath/$ad_url + + the return pathname can be returned using ns_returnfile AND not + ns_returnredirect + + } { + + set image_path [util_memoize { + ad_parameter -package_id [ad_acs_adserver_id] \ + BaseImagePath adserver + } [adserver_cache_refresh]] + + # absolute or relative? + if {[string equal [string range $image_path 0 0] /] || + [string equal [string range $image_path 0 0] \\] || + [string equal [string range $image_path 1 1] :]} { + # absolute pathname on unix, mac, or windows + set url [adserver_url_concat $image_path $ad_url] + } else { + + # local to the webserver + set url [adserver_url_concat \ + [adserver_url_concat \ + [ad_acs_adserver_pageroot] $image_path] \ + $ad_url] + } + return $url +} Index: openacs-4/packages/adserver/tcl/adserver-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/tcl/adserver-procs.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/tcl/adserver-procs.xql 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,127 @@ + + + + + + + select package_id from apm_packages + where package_key = 'adserver' + + + + + + + + + select rotation_method + from adv_groups + where group_key=:group_key + + + + + + + + select adv_count + from advs_properties + + + + + + + + + select adv_key + from advs + where adv_number = :pick + + + + + + + + + select adv_count + from advs_properties + + + + + + + + + select adv_key + from adv_group_map + where adv_group_number = :pick + and group_key = :group_key + + + + + + + + + select adv_group_number as last, + ag.adv_count max_adv_group_number + from adv_group_map grp, adv_groups ag, adv_user_map map + where user_id=:user_id + and event_time = ( + select max(event_time) + from adv_user_map map2 + where map2.user_id = :user_id + and map2.adv_key = map.adv_key + and map2.event_type = 'd' + ) + and ag.group_key = :group_key + and grp.group_key = :group_key + and grp.adv_key = map.adv_key + and map.user_id = :user_id + and map.event_type = 'd' + + + + + + + + select adv_key + from adv_group_map + where group_key=:group_key + and adv_group_number=:adv_group_number + + + + + + + select track_clickthru_p, target_url + from advs + where adv_key = :adv_key + + + + + + select track_clickthru_p, target_url, adv_key + from advs + where adv_number = :ad_number + + + + + + select a.adv_key, track_clickthru_p, target_url + from advs a, adv_group_map m + where a.adv_key = m.adv_key + and adv_group_number = :ad_number + and m.group_key = :group_key + + + + Index: openacs-4/packages/adserver/www/CHANGES =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/CHANGES,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/CHANGES 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,5 @@ +* index.tcl - changed the adserver_get_ad_html call to enable logging and follow the rotation method of the group +* adimg.tcl - changed all trunc(sysdate)'s to current_date +* adimg-postgresql.xql - changed all trunc(current_timestamp) to current_date +* adhref.tcl - moved adv_update_query, adv_insert to adhref-postgresql.xql and adhref-oracle.xql +* adhref.tcl - changed all trunc(sysdate)'s to current_date Index: openacs-4/packages/adserver/www/adhref-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/adhref-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/adhref-oracle.xql 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,43 @@ + + + + oracle8.1.6 + + + + + insert into adv_user_map (user_id, adv_key, event_time, event_type) + values (:user_id, :adv_key, sysdate, 'c') + + + + + + + + +update adv_log + set click_count = click_count + 1 + where adv_key = :adv_key + and entry_date = trunc (sysdate) + + + + + + + + insert into adv_log + (adv_key, entry_date, click_count) + values (:adv_key, + trunc (sysdate), + (select 1 from dual + where 0 = (select count (*) + from adv_log + where adv_key = :adv_key + and entry_date = trunc (sysdate))))" + db_dml adv_insert $insert_sql + + + + Index: openacs-4/packages/adserver/www/adhref-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/adhref-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/adhref-postgresql.xql 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,43 @@ + + + + postgresql7.1 + + + + + insert into adv_user_map (user_id, adv_key, event_time, event_type) + values (:user_id, :adv_key, current_timestamp, 'c') + + + + + + + + +update adv_log + set click_count = click_count + 1 + where adv_key = :adv_key + and entry_date = current_date + + + + + + + + insert into adv_log + (adv_key, entry_date, click_count) + values (:adv_key, + current_date, + (select 1 from dual + where 0 = (select count (*) + from adv_log + where adv_key = :adv_key + and entry_date = current_date)))" + db_dml adv_insert $insert_sql + + + + Index: openacs-4/packages/adserver/www/adhref.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/adhref.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/adhref.tcl 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,100 @@ +# /www/adserver/adhref.tcl + +ad_page_contract { + + this page finds the target URL that corresponds to the banner we + displayed sends bytes back to the browser instructing the browser + to redirect to that URL closes the TCP connection to the user + while this thread is still alive, logs the clickthrough + (optionally this page will not log the clickthrough, e.g., if this + is invoked from the /admin directory) + + @author philg@mit.edu + @author jerry@hollyjerry.org + @creation-date 11/24/1999 + @cvs-id $Id: adhref.tcl,v 1.1 2002/01/30 11:25:25 roelc Exp $adhref.tcl,v 3.1.6.2 2000/07/22 20:50:37 berkeley Exp +} { + adv_key + suppress_logging_p:optional +} + +# last edited November 24, 1999 to address a concurrency problem + +set adv_key [ns_urldecode $adv_key] + +if { ![info exists adv_key] || $adv_key=="" || [string equal $adv_key default]} { + set target [util_memoize { + ad_parameter -package_id [ad_acs_adserver_id] DefaultAdTargetURL adserver / + } [adserver_cache_refresh]] + ad_returnredirect $target + return +} + +set target_url [db_string adv_url_query " +select target_url + from advs + where adv_key = :adv_key" -default ""] + +if { $target_url == "" } { + set target [util_memoize { + ad_parameter -package_id [ad_acs_adserver_id] DefaultAdTargetURL adserver / + } [adserver_cache_refresh]] + ad_returnredirect $target + return +} + +ad_returnredirect $target_url + +if { [info exists suppress_logging_p] && $suppress_logging_p == 1 } { + return +} + +ns_conn close + +# we've returned to the user but let's keep this thread alive to log + +set update_sql " +update adv_log + set click_count = click_count + 1 + where adv_key = :adv_key + and entry_date = trunc (sysdate) +" + +db_dml adv_update_query $update_sql + +set n_rows [db_resultrows] + +if { $n_rows == 0 } { + + # there wasn't already a row there let's be careful in case + # another thread is executing concurrently on the 10000:1 chance + # that it is, we might lose an update but we won't generate an + # error in the error log and set off all the server monitor alarms + + set insert_sql " + insert into adv_log + (adv_key, entry_date, click_count) + values (:adv_key, + trunc (sysdate), + (select 1 from dual + where 0 = (select count (*) + from adv_log + where adv_key = :adv_key + and entry_date = trunc (sysdate))))" + db_dml adv_insert $insert_sql +} + +if [util_memoize { + ad_parameter -package_id [ad_acs_adserver_id] DetailedPerUserLoggingP adserver 0 + } [adserver_cache_refresh]] { + set user_id [ad_get_user_id] + if { $user_id == 0 } { + set user_id [db_null] + } + # we know who this user is + db_dml adv_known_user_insert " + insert into adv_user_map (user_id, adv_key, event_time, event_type) + values (:user_id, :adv_key, sysdate, 'c') + " +} + Index: openacs-4/packages/adserver/www/adhref.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/adhref.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/adhref.xql 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,14 @@ + + + + + + +select target_url + from advs + where adv_key = :adv_key + + + + + Index: openacs-4/packages/adserver/www/adimg-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/adimg-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/adimg-oracle.xql 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,42 @@ + + + + oracle8.1.6 + + + + + update adv_log + set display_count = display_count + 1 + where adv_key = :adv_key + and entry_date = trunc (sysdate) + + + + + + + + insert into adv_log + (adv_key, entry_date, display_count) + values (:adv_key, + trunc (sysdate), + (select 1 from dual + where 0 = (select count (*) + from adv_log + where adv_key = :adv_key + and entry_date = trunc (sysdate)))) + + + + + + + + insert into adv_user_map (user_id, adv_key, event_time, event_type) + values (:user_id, :adv_key, sysdate, 'd') + + + + + Index: openacs-4/packages/adserver/www/adimg-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/adimg-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/adimg-postgresql.xql 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,42 @@ + + + + postgresql7.1 + + + + + update adv_log + set display_count = display_count + 1 + where adv_key = :adv_key + and entry_date = current_date + + + + + + + + insert into adv_log + (adv_key, entry_date, display_count) + values (:adv_key, + current_date, + (select 1 + where 0 = (select count (*) + from adv_log + where adv_key = :adv_key + and entry_date = current_date))) + + + + + + + + insert into adv_user_map (user_id, adv_key, event_time, event_type) + values (:user_id, :adv_key, current_timestamp, 'd') + + + + + Index: openacs-4/packages/adserver/www/adimg.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/adimg.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/adimg.tcl 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,133 @@ +# /www/adserver/adimg.tcl + +ad_page_contract { + This page tries to find an image file to serve to the user, + serves it, closes the TCP connection to the user. + while this thread is still alive, logs the ad display + + @author philg@mit.edu + @author jerry@hollyjerry.org + @creation-date 11/24/1999 + @cvs-id adimg.tcl,v 3.2.2.4 2000/09/22 01:36:39 kevin Exp +} { + {adv_key ""} + suppress_logging_p:optional +} + +# last edited November 24, 1999 to address a concurrency problem + +set display_default_banner_p 0 + +set adv_key [ns_urldecode $adv_key] + +if { ![info exists adv_key] || $adv_key == "" || [string equal $adv_key default]} { + set display_default_banner_p 1 + set ad_filename_stub [util_memoize { + ad_parameter -package_id [ad_acs_adserver_id] DefaultAdImage adserver + } [adserver_cache_refresh]] + set local_image [util_memoize { + ad_parameter -package_id [ad_acs_adserver_id] DefaultAdLocalP adserver 1 + } [adserver_cache_refresh]] +} else { + if { [db_0or1row adv_select " + SELECT adv_filename as ad_filename_stub, + decode(local_image_p, 't', 1, 0) as local_image + FROM advs + WHERE adv_key = :adv_key"] } { + # correct vars set + } else { + set display_default_banner_p 1 + } +} + +set ad_filename [adserver_image_url $ad_filename_stub] + +if {$local_image == 1 || [string equal $local_image t] } { + + if { ![file isfile $ad_filename] } { + ns_log Error "Didn't find ad: $ad_filename" + + if {$display_default_banner_p == 1} { + # we're really in bad shape; no row exists and + # we don't have an adv_key + ns_log Error "adimg.tcl didn't find an ad matching " \ + "\"$adv_key\" AND no default file exists" + adserver_notify_host_administrator "define a default ad!" " + Define a default banner ad in [ad_system_name] + someone is requesting ads with an + invalid adv_key of \"$adv_key\" + " + } else { + # punt to the default banner + set display_default_banner_p 1 + set ad_filename_stub [util_memoize { + ad_parameter -package_id [ad_acs_adserver_id] DefaultAdImage adserver + } [adserver_cache_refresh]] + set local_image [util_memoize { + ad_parameter -package_id [ad_acs_adserver_id] DefaultAdLocalP adserver 1 + } [adserver_cache_refresh]] + set ad_filename [adserver_image_url $ad_filename_stub] + } + } + + # return the file + # the no-cache stuff ensures that Netscape browser users never get + # a cached IMG with a new target + + ns_returnfile 200 "[ns_guesstype $ad_filename]\nPragma: no-cache" \ + $ad_filename +} else { + # let the remote server provide the image + ad_returnredirect $ad_filename_stub + # Should we check for the existence of the ad on the remote host? + # For now, we don't + +} + +if { [info exists suppress_logging_p] && $suppress_logging_p == 1 } { + return +} + +# we've returned to the user but let's keep this thread alive to log + +ns_conn close + +if {$display_default_banner_p == 0} { + + db_dml adv_log_update_query " + update adv_log + set display_count = display_count + 1 + where adv_key = :adv_key + and entry_date = current_date" + + set n_rows [db_resultrows] + + if { $n_rows == 0 } { + # there wasn't a row in the database; we can't just do the obvious + # insert because another thread might be executing concurrently + db_dml adv_insert " + insert into adv_log + (adv_key, entry_date, display_count) + values (:adv_key, + current_date, + (select 1 from dual + where 0 = (select count (*) + from adv_log + where adv_key = :adv_key + and entry_date = current_date)))" + } + + if [util_memoize { + ad_parameter -package_id [ad_acs_adserver_id] DetailedPerUserLoggingP adserver 0 + } [adserver_cache_refresh]] { + set user_id [ad_get_user_id] + if { $user_id == 0 } { + set user_id [db_null] + } + # we know who this user is + db_dml adv_known_user_insert " + insert into adv_user_map (user_id, adv_key, event_time, event_type) + values (:user_id, :adv_key, sysdate, 'd')" + } +} + Index: openacs-4/packages/adserver/www/adimg.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/adimg.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/adimg.xql 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + + SELECT adv_filename as ad_filename_stub, + case when local_image_p = 't' then 1 else 0 end as local_image + FROM advs + WHERE adv_key = :adv_key + + + + + Index: openacs-4/packages/adserver/www/adtest.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/adtest.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/adtest.tcl 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,32 @@ +# /www/adserver/adtest.tcl + +ad_page_contract { + test page + + @author ron + @author jerry@hollyjerry.org + @creation-date 02/06/2000 + @cvs-id adtest.tcl,v 3.0.12.2 2000/09/22 01:36:39 kevin Exp +} { + +} + +doc_return 200 text/html " + +Adserver Test Page + + +

Ad Server Test Page

+

You should see an ad below: + +
+ +[adserver_get_ad_html "test"] + + + +" + + + + Index: openacs-4/packages/adserver/www/default.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/default.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/default.adp 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,5 @@ + +@title@ +@admin_p@ +@admin_link@ +@page_content@ Index: openacs-4/packages/adserver/www/index.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/index.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/index.tcl 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,99 @@ +# /www/adserver//index.tcl + +ad_page_contract { + + main adserver page + + @param none + @author modified 07/13/200 by mchu@arsdigita.com + @author modified 10/15/2000 jerry@hollyjerry.org + @author modified 3/20/2001 janine@furfly.net + @cvs-id index.tcl,v 3.2.2.5 2000/09/22 01:34:19 kevin Exp +} -properties { + title:onevalue + page_content:onevalue + admin_p:onevalue + admin_link:onevalue +} + +set title "Adserver: and now a word from our sponsors" +set admin_p f +set admin_link "" +set page_content " + +Thanks for visiting. Here's a sampling of our sponsors' ads. + +

+" +set package_id [ad_conn package_id] + +set ads [list] +set count 0 +db_foreach adserver_sample_groups { + select group_key, pretty_name, adv_count, rotation_method + from adv_groups + order by group_key + } { + incr count + set ad_content "" + set suppress_logging 0 + if {[ad_permission_p $package_id admin]} { + set suppress_logging 1 + set admin_p t + set admin_link " + + + + +
+ admin +
+ doc +
" + append ad_content " + + " + } + + append ad_content " +
+ [adserver_get_ad_html $group_key] +
+ " +# [adserver_get_ad_html -method random -suppress_logging=$suppress_logging $group_key] + + lappend ads $ad_content + +} + +# still show admin and doc links even if there are no ads to display +if { [ad_permission_p $package_id admin] && $count == 0 } { + append page_content " +No ads defined (and assigned to groups) right now. + + + + +
+admin +
+doc +
+

+" +} else { + append page_content [join $ads "\n

\n"] +} + +append page_content "
\n" + +ad_return_template default + Index: openacs-4/packages/adserver/www/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/index.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/index.xql 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + + select group_key, pretty_name, adv_count, rotation_method + from adv_groups + order by group_key + + + + + + Index: openacs-4/packages/adserver/www/master.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/Attic/master.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/master.adp 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,9 @@ +<%= [ad_header $title] %> +

@title@

+<%= [eval ad_context_bar [list @title@]] %> +
+ +@admin_link@ + + +<%= [ad_footer] %> \ No newline at end of file Index: openacs-4/packages/adserver/www/view-group.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/view-group.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/view-group.tcl 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,81 @@ +# /www/adserver/view-group.tcl + +ad_page_contract { + + adserver view entire group + + @param none + @author modified 07/13/200 by mchu@arsdigita.com + @author modified 10/15/2000 jerry@hollyjerry.org + @cvs-id index.tcl,v 3.2.2.5 2000/09/22 01:34:19 kevin Exp +} { + group_key:notnull,trim +} -properties { + title:onevalue + page_content:onevalue + admin_p:onevalue + admin_link:onevalue +} + +set package_id [ad_conn package_id] +ad_require_permission $package_id admin + +set admin_p t + set admin_link " + + + + +
+ admin +
+ doc +
" + +set page_content "" + +set title "Ads in group $group_key" +if {[db_0or1row adserver_group_view " + select pretty_name, adv_count, rotation_method + from adv_groups + where group_key = :group_key + "]} { + append page_content " +
+

Group $pretty_name

+
    +
  • group key: $group_key +
  • $adv_count ads in group +
  • rotation method: $rotation_method +
+ + " + db_foreach adserver_group_view_one " + select adv_key, adv_group_number + from adv_group_map + where group_key = :group_key + order by adv_group_number + " { + append page_content " +
+
+ + + + + " + } + append page_content " +
$adv_group_number  $adv_key      + [adserver_get_ad_html -suppress_logging -ad_number $adv_group_number $group_key] +
 
+
+ " +} else { + set page_content " + I am sorry, we could not find a group for \"$group_key\" + " +} + +ad_return_template default + Index: openacs-4/packages/adserver/www/view-group.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/view-group.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/view-group.xql 30 Jan 2002 11:25:25 -0000 1.1 @@ -0,0 +1,27 @@ + + + + + + + select pretty_name, adv_count, rotation_method + from adv_groups + where group_key = :group_key + + + + + + + + + select adv_key, adv_group_number + from adv_group_map + where group_key = :group_key + order by adv_group_number + + + + + + Index: openacs-4/packages/adserver/www/admin/CHANGES =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/CHANGES,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/CHANGES 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,2 @@ +* index.tcl - adv_select_query not extracted, changing the (+) outer join to a UNION statement, moving to index.xql as adv_select_query2 +* index.tcl - another query was not extracted also with adv_select_query as handle, moved to index.xql as adv_select_query1 Index: openacs-4/packages/adserver/www/admin/add-adv-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/add-adv-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/add-adv-2-oracle.xql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,20 @@ + + + + oracle8.1.6 + + + + +insert into advs (adv_key, target_url, local_image_p, track_clickthru_p, adv_filename) +select :adv_key, :target_url, :local_image_p, :track_clickthru_p, :adv_filename + from dual +where not exists (select 1 + from advs + where adv_key= :adv_key) + + + + + + Index: openacs-4/packages/adserver/www/admin/add-adv-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/add-adv-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/add-adv-2-postgresql.xql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,20 @@ + + + + postgresql7.1 + + + + +insert into advs (adv_key, target_url, local_image_p, track_clickthru_p, adv_filename) +select :adv_key, :target_url, :local_image_p, :track_clickthru_p, :adv_filename + +where not exists (select 1 + from advs + where adv_key= :adv_key) + + + + + + Index: openacs-4/packages/adserver/www/admin/add-adv-2.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/add-adv-2.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/add-adv-2.tcl 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,75 @@ +# /www/admin/adserver/add-adv-2.tcl + +ad_page_contract { + Target page for add-adv.tcl + + Inserts one row into the advs table. If a row with the same adv_key already exists, + the user is notified of the problem, unless _all_ the form inputs are identical to + the ones in the existing row. In that case or when the insert succeeds, the user is + redirected to one-adv.tcl + + @param adv_key:notnull + @param target_url + @param local_image_p + @param track_clickthru_p + @param adv_filename + + @author modified 07/13/200 by mchu@arsdigita.com + @author modified 10/15/2000 jerry@hollyjerry.org + @cvs-id add-adv-2.tcl,v 3.1.6.6 2000/10/20 23:21:51 avni Exp +} { + adv_key:notnull + target_url:html + local_image_p + track_clickthru_p + adv_filename +} -properties { + title:onevalue + page_content:onevalue + admin_link:onevalue +} + +db_dml adv_insert_query " +insert into advs (adv_key, target_url, local_image_p, track_clickthru_p, adv_filename) +select :adv_key, :target_url, :local_image_p, :track_clickthru_p, :adv_filename + from dual +where not exists (select 1 + from advs + where adv_key= :adv_key) +" + +set insert_succ_p [db_string adv_insert_check " +select count(*) + from advs + where adv_key = :adv_key + and target_url = :target_url + and local_image_p = :local_image_p + and track_clickthru_p = :track_clickthru_p + and adv_filename = :adv_filename +"] +db_release_unused_handles + +if { $insert_succ_p } { + ad_returnredirect one-adv?[export_url_vars adv_key] +} else { + set title "Adding ad failed" + set admin_link " + + + + +
+ admin +
+ doc +
" + + append page_content " +

We are sorry. The ad $adv_key could not be added. There already is an + ad with the same ad key. +

Please use the back button on your browser and change the ad key. + " + ad_return_template default + +} + Index: openacs-4/packages/adserver/www/admin/add-adv-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/add-adv-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/add-adv-2.xql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,19 @@ + + + + + + +select count(*) + from advs + where adv_key = :adv_key + and target_url = :target_url + and local_image_p = :local_image_p + and track_clickthru_p = :track_clickthru_p + and adv_filename = :adv_filename + + + + + + Index: openacs-4/packages/adserver/www/admin/add-adv-group-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/add-adv-group-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/add-adv-group-2-oracle.xql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,20 @@ + + + + oracle8.1.6 + + + + +insert into adv_groups (group_key, pretty_name, rotation_method) +select :group_key, :pretty_name, :rotation_method + from dual + where not exists (select 1 + from adv_groups + where group_key = :group_key) + + + + + + Index: openacs-4/packages/adserver/www/admin/add-adv-group-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/add-adv-group-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/add-adv-group-2-postgresql.xql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,20 @@ + + + + postgresql7.1 + + + + +insert into adv_groups (group_key, pretty_name, rotation_method) +select :group_key, :pretty_name, :rotation_method + + where not exists (select 1 + from adv_groups + where group_key = :group_key) + + + + + + Index: openacs-4/packages/adserver/www/admin/add-adv-group-2.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/add-adv-group-2.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/add-adv-group-2.tcl 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,72 @@ +# /www/admin/adserver/add-adv-group-2.tcl + +ad_page_contract { + target page for ad-adv-group.tcl + + @param group_key:notnull + @param pretty_name + @param rotation_method + + @author modified 07/13/200 by mchu@arsdigita.com + @author modified 10/15/2000 jerry@hollyjerry.org + @cvs-id add-adv-group-2.tcl,v 3.1.6.6 2000/10/20 23:21:51 avni Exp +} { + group_key:notnull,trim + pretty_name:trim + rotation_method +} -properties { + title:onevalue + page_content:onevalue + admin_link:onevalue +} + +db_dml adv_insert_query " +insert into adv_groups (group_key, pretty_name, rotation_method) +select :group_key, :pretty_name, :rotation_method + from dual + where not exists (select 1 + from adv_groups + where group_key = :group_key) +" + +# The handling of '' and null in Oracle is too weird to do this in the query +if { [empty_string_p $pretty_name] } { + set pretty_name_sql "pretty_name is null" +} else { + set pretty_name_sql "pretty_name = :pretty_name" +} +# The trim around rotation_method is necessary since it is a CHAR column, +# not a VARCHAR +set insert_succ_p [db_string adv_insert_check " +select count(*) + from adv_groups + where group_key = :group_key and $pretty_name_sql + and trim(rotation_method) = :rotation_method" ] +db_release_unused_handles + +if { $insert_succ_p } { + ad_returnredirect "one-adv-group?[export_url_vars group_key]" +} else { + set title "Adding group failed" + set admin_link " + + + + +
+ admin +
+ doc +
" + + append page_content " +

Insert_succ_p $insert_succ_p: /$group_key/$pretty_name/$rotation_method/ +

We are sorry. The group $group_key could not be added. There already is a + group with the same group name. +

Please use the back button on your browser and change the group name. + " + + ad_return_template default +} + + Index: openacs-4/packages/adserver/www/admin/add-adv-group-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/add-adv-group-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/add-adv-group-2.xql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + +select count(*) + from adv_groups + where group_key = :group_key and $pretty_name_sql + and trim(rotation_method) = :rotation_method + + + + + Index: openacs-4/packages/adserver/www/admin/add-adv-group.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/add-adv-group.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/add-adv-group.tcl 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,64 @@ +# /www/admin/adserver/add-adv-group.tcl + +ad_page_contract { + adds an adv group + + @param none + @author modified 07/13/200 by mchu@arsdigita.com + @author modified 10/15/2000 jerry@hollyjerry.org + @cvs-id add-adv-group.tcl,v 3.2.2.2 2000/07/21 03:55:59 ron Exp +} { + +} -properties { + title:onevalue + page_content:onevalue + admin_link:onevalue +} + +set title "Add a new Ad Group" +set admin_link " + + + + +
+admin +
+doc +
" + +# we'll export this to adhref and adimg so that admin actions don't +# corrupt user data +set suppress_logging_p 1 + + + +append page_content " +

+ +

+ + + + + + + +
Group Key
(no spaces, please!)
Group Pretty Name
(for your convenience)
Rotation Method
+ +

+

+ +
+ +
+

+ +" + + +ad_return_template default \ No newline at end of file Index: openacs-4/packages/adserver/www/admin/add-adv-to-group-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/add-adv-to-group-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/add-adv-to-group-2-oracle.xql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,21 @@ + + + + oracle8.1.6 + + + + +insert into adv_group_map (group_key, adv_key) +select :group_key, :adv_key + from dual + where not exists (select 1 + from adv_group_map + where adv_key = :adv_key + and group_key = :group_key) + + + + + + Index: openacs-4/packages/adserver/www/admin/add-adv-to-group-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/add-adv-to-group-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/add-adv-to-group-2-postgresql.xql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,21 @@ + + + + postgresql7.1 + + + + +insert into adv_group_map (group_key, adv_key) +select :group_key, :adv_key + + where not exists (select 1 + from adv_group_map + where adv_key = :adv_key + and group_key = :group_key) + + + + + + Index: openacs-4/packages/adserver/www/admin/add-adv-to-group-2.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/add-adv-to-group-2.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/add-adv-to-group-2.tcl 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,31 @@ +# /www/admin/adserver/add-adv-to-group-2.tcl + +ad_page_contract { + target page for add-adv-to-troup.tcl + + @param group_key:notnull + @param adv_key:notnull + + @author modified 07/13/200 by mchu@arsdigita.com + @author modified 10/15/2000 jerry@hollyjerry.org + @cvs-id add-adv-to-group-2.tcl,v 3.1.6.5 2000/10/20 23:21:51 avni Exp +} { + group_key:notnull + adv_key:notnull +} + +db_dml adv_insert_query " +insert into adv_group_map (group_key, adv_key) +select :group_key, :adv_key + from dual + where not exists (select 1 + from adv_group_map + where adv_key = :adv_key + and group_key = :group_key) +" + +# We do not check if the insert succeeded, since it can only fail if this association +# is already in ADV_GROUP_MAP + +db_release_unused_handles +ad_returnredirect one-adv-group?[export_url_vars group_key] Index: openacs-4/packages/adserver/www/admin/add-adv-to-group.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/add-adv-to-group.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/add-adv-to-group.tcl 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,51 @@ +# /www/admin/adserver/add-adv-to-group.tcl + +ad_page_contract { + @param group_key:notnull + @author modified 07/13/200 by mchu@arsdigita.com + @author modified 10/15/2000 jerry@hollyjerry.org + @cvs-id add-adv-to-group.tcl,v 3.1.6.4 2000/09/22 01:34:18 kevin Exp +} { + group_key:notnull +} -properties { + title:onevalue + page_content:onevalue + admin_link:onevalue +} + +set title "Add an ad to a group" +set admin_link " + + + + +
+admin +
+doc +
" + +# we'll export this to adhref and adimg so that admin actions don't +# corrupt user data +set suppress_logging_p 1 + +db_1row adv_pretty_name_query "select pretty_name from adv_groups where group_key = :group_key" + +set page_content " +Choose an ad to include in this Ad Group:

+

+

+" + +ad_return_template default Index: openacs-4/packages/adserver/www/admin/add-adv-to-group.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/add-adv-to-group.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/add-adv-to-group.xql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select pretty_name from adv_groups where group_key = :group_key + + + + + Index: openacs-4/packages/adserver/www/admin/add-adv.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/add-adv.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/add-adv.tcl 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,67 @@ +# /www/admin/adserver/add-adv.tcl + +ad_page_contract { + @param none + @author modified 07/13/200 by mchu@arsdigita.com + @author modified 10/15/2000 jerry@hollyjerry.org + @cvs-id add-adv.tcl,v 3.1.6.5 2000/10/20 23:21:51 avni Exp +} { + +} -properties { + title:onevalue + page_content:onevalue + admin_link:onevalue +} + +set title "Add a new Ad" +set admin_link " + + + + +
+admin +
+doc +
" + +# we'll export this to adhref and adimg so that admin actions don't +# corrupt user data +set suppress_logging_p 1 + +append page_content " +

+ +

+ + + + + + + + + + + + + + + + + + + + + +
Ad Key(no spaces please)
Link to:(a URL for the user who clicks on this banner or all of doubleclick stuff)
Local Image:Yes No(Image resides on this server)
Track Clickthru:Yes No(No for doubleclick, etc.)
Image File Location:(pathname or URL of banner GIF, blank for doubleclick, etc.)
+
+
+ +
+
+ + +" + +ad_return_template default Index: openacs-4/packages/adserver/www/admin/all-adv-groups.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/all-adv-groups.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/all-adv-groups.tcl 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,54 @@ +# /www/admin/adserver/all-adv-groups.tcl + +ad_page_contract { + @param none + @author modified 07/13/200 by mchu@arsdigita.com + @author modified 10/15/2000 jerry@hollyjerry.org + @cvs-id all-adv-groups.tcl,v 3.1.6.4 2000/09/22 01:34:18 kevin Exp +} { + +} -properties { + title:onevalue + page_content:onevalue + admin_link:onevalue +} + +set title "Manage Ad Groups" +set admin_link " + + + + +
+admin +
+doc +
" + +# we'll export this to adhref and adimg so that admin actions don't +# corrupt user data +set suppress_logging_p 1 + + +set page_content " +

+ +

+

+" + +ad_return_template default Index: openacs-4/packages/adserver/www/admin/all-advs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/all-advs.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/all-advs.tcl 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,55 @@ +# /www/admin/adserver/all-advs.tcl + +ad_page_contract { + @param none + @author modified 07/13/200 by mchu@arsdigita.com + @author modified 10/15/2000 jerry@hollyjerry.org + @cvs-id all-advs.tcl,v 3.1.6.4 2000/09/22 01:34:19 kevin Exp +} { + +} -properties { + title:onevalue + page_content:onevalue + admin_link:onevalue +} + +set title "Manage Ads" +set admin_link " + + + + +
+admin +
+doc +
" + +# we'll export this to adhref and adimg so that admin actions don't +# corrupt user data +set suppress_logging_p 1 + + +set page_content " +

+

+" + +ad_return_template default Index: openacs-4/packages/adserver/www/admin/default.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/default.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/default.adp 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,4 @@ + +@title@ +@admin_link@ +@page_content@ Index: openacs-4/packages/adserver/www/admin/delete-adv-2.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/delete-adv-2.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/delete-adv-2.tcl 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,65 @@ +# /www/admin/adserver/delete-adv-2.tcl + +ad_page_contract { + @param adv_key:notnull + @author modified 07/13/200 by mchu@arsdigita.com + @author modified 10/15/2000 jerry@hollyjerry.org + @cvs-id delete-adv-2.tcl,v 3.1.6.4 2000/09/22 01:34:19 kevin Exp +} { + adv_key:notnull +} -properties { + title:onevalue + page_content:onevalue + admin_link:onevalue +} + +set title "Deleting $adv_key" +set admin_link " + + + + +
+admin +
+doc +
" + + +append page_content " + +

+ +Transaction complete. + +" + +ad_return_template default Index: openacs-4/packages/adserver/www/admin/delete-adv-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/delete-adv-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/delete-adv-2.xql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,32 @@ + + + + + + delete from adv_log where adv_key = :adv_key + + + + + + + delete from adv_user_map where adv_key = :adv_key + + + + + + + delete from adv_group_map where adv_key = :adv_key + + + + + + + delete from advs where adv_key = :adv_key + + + + + Index: openacs-4/packages/adserver/www/admin/delete-adv.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/delete-adv.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/delete-adv.tcl 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,86 @@ +# /www/admin/adserver/delete-adv.tcl + +ad_page_contract { + @param adv_key:notnull + @author modified 07/13/200 by mchu@arsdigita.com + @author modified 10/15/2000 jerry@hollyjerry.org + @cvs-id delete-adv.tcl,v 3.2.2.5 2000/10/20 23:21:51 avni Exp +} { + adv_key:notnull +} -properties { + title:onevalue + page_content:onevalue + admin_link:onevalue +} + +set admin_link " + + + + +
+admin +
+doc +
" + +# we'll export this to adhref and adimg so that admin actions don't +# corrupt user data +set suppress_logging_p 1 + +db_1row adv_info_query " +select sum (display_count) as n_displays, + sum (click_count) as n_clicks, + min (entry_date) as first_display, + max (entry_date) as last_display, + round (max (entry_date) - min (entry_date)) as n_days, + count (*) as n_entries +from adv_log +where adv_key = :adv_key" + + + +set title "Confirm Deletion of $adv_key" + +append page_content " + +
+[adserver_get_ad_html -suppress_logging=$suppress_logging_p -adv_key $adv_key] +
+ +If what you want to do is stop showing an ad to users, you're in the +wrong place. What you should be doing instead is changing the places +that reference this ad to reference some other ad. Ads that have been +shown to users should never be deleted from the system because that +also deletes the logs. + +

+ +Here's what you'll be deleting if you delete this ad: + +

+ +

+ +If you don't want to do that, then you can simply return to ad server administration. + +

+ +However, if you only put this ad in the database for a demonstration +or experiment and never actually showed it to any users, then you can + +

+
+[export_form_vars adv_key] + +
+
+ +" + +ad_return_template default \ No newline at end of file Index: openacs-4/packages/adserver/www/admin/delete-adv.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/delete-adv.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/delete-adv.xql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,19 @@ + + + + + + +select sum (display_count) as n_displays, + sum (click_count) as n_clicks, + min (entry_date) as first_display, + max (entry_date) as last_display, + round (max (entry_date) - min (entry_date)) as n_days, + count (*) as n_entries +from adv_log +where adv_key = :adv_key + + + + + Index: openacs-4/packages/adserver/www/admin/index.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/index.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/index.tcl 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,124 @@ +# /www/adserver/admin/adserver/index.tcl + +ad_page_contract { + + main adserver admin page + + @param none + @author modified 07/13/200 by mchu@arsdigita.com + @author modified 10/15/2000 jerry@hollyjerry.org + @cvs-id index.tcl,v 3.2.2.5 2000/09/22 01:34:19 kevin Exp +} -properties { + title:onevalue + page_content:onevalue + admin_link:onevalue +} + +set admin_link " + + + + +
+admin +
+doc +
" + +set package_id [ad_conn package_id] +# set admin_p [ad_permission_p $package_id admin] +ad_require_permission $package_id admin + +set title "Adserver Administration" +set page_content " + + +Documentation for this subsystem is available at +/doc/adserver.html. + +" + +ad_return_template default Index: openacs-4/packages/adserver/www/admin/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/index.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/index.xql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,40 @@ + + + + + + + select pretty_name + from adv_groups + where group_key = :group_key + + + + + + + + select group_key, pretty_name + from adv_groups + where not group_key in (select group_key from adv_group_map) + + + + + + + + select map.group_key, advs.adv_key + from advs, adv_group_map map + where advs.adv_key = map.adv_key + +UNION + + select NULL, advs.adv_key + from advs + where 0=(select count(*) from adv_group_map where advs.adv_key = adv_group_map.adv_key) + + + + + Index: openacs-4/packages/adserver/www/admin/master.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/Attic/master.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/master.adp 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,7 @@ +<%= [ad_admin_header $title] %> +

@title@

+<%= [eval ad_context_bar [list $title]] %> +
+@admin_link@ + +<%= [ad_footer] %> \ No newline at end of file Index: openacs-4/packages/adserver/www/admin/one-adv-detailed-stats.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/one-adv-detailed-stats.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/one-adv-detailed-stats.tcl 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,93 @@ +# /www/admin/adserver/one-adv-detailed-stats.tcl + +ad_page_contract { + @param adv_key:notnull + @author modified 07/13/200 by mchu@arsdigita.com + @author modified 10/15/2000 jerry@hollyjerry.org + @cvs-id one-adv-detailed-stats.tcl,v 3.2.2.5 2000/10/20 23:21:52 avni Exp +} { + adv_key:notnull +} -properties { + title:onevalue + page_content:onevalue + admin_link:onevalue +} + + +set title "Detailed Statistics: $adv_key" +set admin_link " + + + + +
+admin +
+doc +
" + +# we'll export this to adhref and adimg so that admin actions don't +# corrupt user data +set suppress_logging_p 1 + +db_1row adv_info_query "select adv_key, adv_filename, track_clickthru_p, target_url from advs where adv_key = :adv_key" + +append page_content " +
+[adserver_get_ad_html -suppress_logging=$suppress_logging_p -adv_key $adv_key] +" + +db_1row adv_numbers_query " +select sum(display_count) as n_displays, + sum(click_count) as n_clicks, + min(entry_date) as first_display, + max(entry_date) as last_display +from adv_log +where adv_key = :adv_key" + + +ns_log NOTICE "n_clicks: $n_clicks" +ns_log NOTICE "n_displays: $n_clicks" + +append page_content "
+ +

Summary Statistics

+ +Between [util_AnsiDatetoPrettyDate $first_display] and [util_AnsiDatetoPrettyDate $last_display], this ad was + +
    +
  • displayed $n_displays times +
  • clicked on $n_clicks times +
  • clicked through [expr 100 * $n_clicks / $n_displays ]% of the time +
+ +

By Date

+ + + + +" + +set query_sql "select * +from adv_log +where adv_key = :adv_key +order by entry_date" + +db_foreach select_query $query_sql { + append page_content "\n" +} + +db_release_unused_handles + +append page_content " +
DateDisplaysClickthroughsClickthrough Rate
[util_AnsiDatetoPrettyDate $entry_date]$display_count$click_count" + if {$display_count > 0} { + append page_content "[expr 100 * $click_count / $display_count ]%" + } else { + append page_content "0%" + } + append page_content "
+

+" + +ad_return_template default Index: openacs-4/packages/adserver/www/admin/one-adv-detailed-stats.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/one-adv-detailed-stats.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/one-adv-detailed-stats.xql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,24 @@ + + + + + + select adv_key, adv_filename, track_clickthru_p, target_url from advs where adv_key = :adv_key + + + + + + + +select sum(display_count) as n_displays, + sum(click_count) as n_clicks, + min(entry_date) as first_display, + max(entry_date) as last_display +from adv_log +where adv_key = :adv_key + + + + + Index: openacs-4/packages/adserver/www/admin/one-adv-group.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/one-adv-group.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/one-adv-group.tcl 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,100 @@ +# /www/admin/adserver/one-adv-group.tcl +ad_page_contract { + @param group_key:notnull + @author modified 07/13/200 by mchu@arsdigita.com + @author modified 10/15/2000 jerry@hollyjerry.org + @cvs-id one-adv-group.tcl,v 3.1.6.3 2000/07/21 03:56:00 ron Exp +} { + group_key:notnull +} -properties { + title:onevalue + page_content:onevalue + admin_link:onevalue +} + +set title "Adserver Administration One Group" +set admin_link " + + + + +
+admin +
+doc +
" + +set selection [ns_set create] + + +db_1row adv_group_info_query " +select group_key, pretty_name +from adv_groups +where group_key = :group_key" -column_set selection + +set pretty_name [ns_set get $selection pretty_name] +set page_content "" + +set current_method [db_string r_method " + select rotation_method as current_method + from adv_groups where group_key=:group_key" -default ""] + +set current_method [string trim $current_method] + +set form " +

+ + + +[export_form_vars group_key] + + + + + + + + + +
Group Key$group_key
Group Pretty Name
(for your convenience)		 +
Rotation Method + +
+
+
+" + +# append page_content "[bt_mergepiece $form $selection] + +append page_content "

$form +

Ads in this Group

+ +These are listed in the order that they will be displayed to users. + +
    +" + +set sql_query "select adv_key +from adv_group_map +where group_key = :group_key +order by upper (adv_key)" + +db_foreach adv_select_query $sql_query { + append page_content "
  • $adv_key +   (remove) +\n" +} + +db_release_unused_handles + +append page_content "

    + +

  • Add an Ad To this Group + +
+" + +ad_return_template default + Index: openacs-4/packages/adserver/www/admin/one-adv-group.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/one-adv-group.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/one-adv-group.xql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,23 @@ + + + + + + +select group_key, pretty_name +from adv_groups +where group_key = :group_key + + + + + + + + select rotation_method as current_method + from adv_groups where group_key=:group_key + + + + + Index: openacs-4/packages/adserver/www/admin/one-adv.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/one-adv.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/one-adv.tcl 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,156 @@ +# /www/admin/adserver/one-adv.tcl + +ad_page_contract { + @param adv_key:notnull + @author modified 07/13/200 by mchu@arsdigita.com + @author modified 10/15/2000 jerry@hollyjerry.org + @cvs-id one-adv.tcl,v 3.2.2.5 2000/10/20 23:21:52 avni Exp +} { + adv_key:notnull +} -properties { + title:onevalue + page_content:onevalue + admin_link:onevalue +} + +set title "One Ad: $adv_key" +set admin_link " + + + + +
+admin +
+doc +
" + +# we'll export this to adhref and adimg so that admin actions don't +# corrupt user data +set suppress_logging_p 1 + +set selection [ns_set create] + +db_1row adv_info_query "select adv_key, + adv_filename, + local_image_p, + track_clickthru_p, + target_url +from advs +where adv_key = :adv_key" + +if {[string equal $track_clickthru_p t]} { + set clickthru_yes CHECKED + set clickthru_no "" +} else { + set clickthru_yes "" + set clickthru_no CHECKED +} + +if {[string equal $local_image_p t]} { + set local_yes CHECKED + set local_no "" +} else { + set local_yes "" + set local_no CHECKED +} + +set form " +
+ +[export_form_vars adv_key] + + + + + + + + + + + + + + + + + + + + +
Ad Key$adv_key
Link to:
Track Click Throughs: + Yes + No +
Local Image: + Yes + No +
Image File + +
+
+
+ +
+
+" + +set page_content " +
+[adserver_get_ad_html -suppress_logging=$suppress_logging_p -adv_key $adv_key] +
+ +" + +# note that we aren't at risk of dividing by zero because +# there won't be any rows in this table unless the ad +# has been displayed at least once + + +set display_count [db_string adv_display_count " +select count(*) from adv_log where adv_key=:adv_key"] + +if { $display_count > 0 } { + db_0or1row adv_info_select " + select sum (display_count) as n_displays, + sum (click_count) as n_clicks, + round (100 * (sum (click_count) /sum (display_count)), 2) as clickthrough_percent, + min (entry_date) as first_display, + max (entry_date) as last_display + from adv_log + where adv_key = :adv_key" + # we have at least one entry + append page_content " +

Summary Statistics

+ + Between [util_AnsiDatetoPrettyDate $first_display] and [util_AnsiDatetoPrettyDate $last_display], this ad was + +
    +
  • displayed $n_displays times +
  • clicked on $n_clicks times +
  • clicked through $clickthrough_percent% of the time +
+ + detailed stats + " +} else { + append page_content "

This ad has never been shown

" +} + +append page_content " + +

Ad Parameters

+ +$form +

+ +

+ +If you only inserted this ad for debugging purposes, you can +take the extreme step of deleting this ad and its associated log entries from the database.] + +
+

+" + +ad_return_template default Index: openacs-4/packages/adserver/www/admin/one-adv.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/one-adv.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/one-adv.xql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,39 @@ + + + + + + select adv_key, + adv_filename, + local_image_p, + track_clickthru_p, + target_url +from advs +where adv_key = :adv_key + + + + + + + +select count(*) from adv_log where adv_key=:adv_key + + + + + + + + select sum (display_count) as n_displays, + sum (click_count) as n_clicks, + round (100 * (sum (click_count) /sum (display_count)), 2) as clickthrough_percent, + min (entry_date) as first_display, + max (entry_date) as last_display + from adv_log + where adv_key = :adv_key + + + + + Index: openacs-4/packages/adserver/www/admin/remove-adv-from-group-2.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/remove-adv-from-group-2.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/remove-adv-from-group-2.tcl 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,24 @@ +# /www/admin/adserver/remove-adv-from-group-2.tcl + +ad_page_contract { + @param group_key:notnull + @param adv_key:notnull + + @author modified 07/13/200 by mchu@arsdigita.com + @author modified 10/15/2000 jerry@hollyjerry.org + @cvs-id remove-adv-from-group-2.tcl,v 3.1.6.4 2000/10/20 23:21:52 avni Exp +} { + group_key:notnull + adv_key:notnull +} + +db_dml adv_delete_query " +delete + from adv_group_map + where group_key = :group_key + and adv_key = :adv_key +" + +db_release_unused_handles + +ad_returnredirect "one-adv-group?[export_url_vars group_key]" Index: openacs-4/packages/adserver/www/admin/remove-adv-from-group-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/remove-adv-from-group-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/remove-adv-from-group-2.xql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,16 @@ + + + + + + +delete + from adv_group_map + where group_key = :group_key + and adv_key = :adv_key + + + + + + Index: openacs-4/packages/adserver/www/admin/remove-adv-from-group.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/remove-adv-from-group.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/remove-adv-from-group.tcl 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,58 @@ +# /www/admin/adserver/remove-adv-from-group.tcl + +ad_page_contract { + @param group_key:notnull + @param adv_key:notnull + + @author modified 07/13/200 by mchu@arsdigita.com + @author modified 10/15/2000 jerry@hollyjerry.org + @cvs-id remove-adv-from-group.tcl,v 3.1.6.4 2000/09/22 01:34:20 kevin Exp +} { + group_key:notnull + adv_key:notnull +} -properties { + title:onevalue + page_content:onevalue + admin_link:onevalue +} + +set title "Confirm removal of $adv_key" +set admin_link " + + + + +
+admin +
+doc +
" + +# we'll export this to adhref and adimg so that admin actions don't +# corrupt user data +set suppress_logging_p 1 + +append page_content " +

+[adserver_get_ad_html -suppress_logging=$suppress_logging_p -adv_key $adv_key] +
+

+This won't remove the ad from the system. You're only deleting the +association between the group $group_key ([db_string adv_name_query "select pretty_name from adv_groups where group_key = :group_key"]) and this ad. + +

+ +

+ +[export_form_vars group_key adv_key] + +
+ +
+
+ +" + +db_release_unused_handles + +ad_return_template default Index: openacs-4/packages/adserver/www/admin/remove-adv-from-group.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/remove-adv-from-group.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/remove-adv-from-group.xql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + select pretty_name from adv_groups where group_key = :group_key + + + + + Index: openacs-4/packages/adserver/www/admin/update-adv-group.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/update-adv-group.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/update-adv-group.tcl 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,24 @@ +# /www/admin/adserver/update-adv-group.tcl + +ad_page_contract { + @param pretty_name + @param group_key:notnull + + @author modified 07/13/200 by mchu@arsdigita.com + @author modified 10/15/2000 jerry@hollyjerry.org + @cvs-id update-adv-group.tcl,v 3.2.2.5 2000/10/20 23:21:53 avni Exp +} { + pretty_name + rotation_method + group_key:notnull +} + +db_dml adv_group_update_query " +update adv_groups set pretty_name = :pretty_name, + rotation_method=:rotation_method + where group_key = :group_key" + +db_release_unused_handles + +ad_returnredirect "one-adv-group?[export_url_vars group_key]" + Index: openacs-4/packages/adserver/www/admin/update-adv-group.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/update-adv-group.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/update-adv-group.xql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,14 @@ + + + + + + +update adv_groups set pretty_name = :pretty_name, + rotation_method=:rotation_method + where group_key = :group_key + + + + + Index: openacs-4/packages/adserver/www/admin/update-adv.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/update-adv.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/update-adv.tcl 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,36 @@ +# /www/admin/adserver/update-adv.tcl + +ad_page_contract { + @param target_url + @param track_clickthru_p + @param adv_filename + @param local_image_p + @param adv_key:notnull + + @author modified 07/13/200 by mchu@arsdigita.com + @author modified 10/15/2000 jerry@hollyjerry.org + @cvs-id update-adv.tcl,v 3.2.2.4 2000/10/20 23:21:53 avni Exp +} { + target_url:allhtml + track_clickthru_p + adv_filename + local_image_p + adv_key:notnull +} + +db_dml adv_update_query " +update advs set + target_url = :target_url, + track_clickthru_p = :track_clickthru_p, + adv_filename = :adv_filename, + local_image_p = :local_image_p +where adv_key = :adv_key" + +db_release_unused_handles + +ad_returnredirect one-adv?[export_url_vars adv_key] + + + + + Index: openacs-4/packages/adserver/www/admin/update-adv.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/admin/update-adv.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/admin/update-adv.xql 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,17 @@ + + + + + + +update advs set + target_url = :target_url, + track_clickthru_p = :track_clickthru_p, + adv_filename = :adv_filename, + local_image_p = :local_image_p +where adv_key = :adv_key + + + + + Index: openacs-4/packages/adserver/www/ads/arsdigita.gif =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/ads/arsdigita.gif,v diff -u Binary files differ Index: openacs-4/packages/adserver/www/doc/index.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/www/doc/index.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/adserver/www/doc/index.adp 30 Jan 2002 11:27:38 -0000 1.1 @@ -0,0 +1,87 @@ + + + +/adserver system + + + +

/adserver system

+ +part of the ArsDigita Community System by ArsDigita and others. + +
+ + + +Remember that the underlying rationale for the ad server is set forth in +Chapter 3 of "the +book". + +

+ +The major feature of the adserver not covered by the book is that +there is a notion of ad groups. For example, if there are four ads +that you'd like a user to see in sequence, you can make them part of a +group and then make all the pages in a section of a site reference +that group. The page need only call adserver_get_ad_html +with the group_key as an argument and it will get back a +reference to the next appropriate ad from that group. + +

+ +Groups can be used for management or for selection of ads. For +instance, you may have the Amazon group, indicating ads placed +by Amazon.com. You may have a sports group, indicating ads +that are related to sports. You might have a frontpage group, +for ads that should appear on the frontpage. Ads can be members of +more than one group. + +

+ +Within a group, ads can be chosed randomly, in sequential order, or +the least seen ad can be the next ad chosen. + +GIF or JPEG files for ads are stored in /ads. + +

+The basic html for an ad looks like: +

+
+<a href="/adserver/adhref?adv_key=pfizer">
+<img src="/adserver/adimg?adv_key=pfizer">
+</a>
+
+
+These references can be created for you in many ways: +
    + +
  • adserver_get_ad_html group_key returns an ad from +the group using the group selection mechanism + +
  • adserver_get_ad_html -adv_key [adserver_get_random_ad_key +group_key] returns a random ad from the group. + +
  • adserver_get_ad_html -adv_key +[adserver_get_random_ad_key] returns a random ad from the entire +collection. + +
+ +

+ +If the ad server gets confused, it tries to always serve up something +to fill the space. It looks for [ad_parameters +DefaultAd] and [ad_parameters DefaultTargetUrl]. +If it can't find those, it notifies the site administrator to define +them. + +<%= [ad_footer] %> + +