-- -- spam.sql -- -- created January 9, 1999 by Philip Greenspun (philg@mit.edu) -- modified by Tracy Adams on Sept 22, 1999 (teadams@mit.edu) -- modified by Henry Minsky (hqm@ai.mit.edu) -- -- -- a system for spamming classes of users and keeping track of -- what the publisher said -- use this to prevent double spamming if user hits submit twice insert into acs_modules ( module_key, pretty_name, public_directory, admin_directory, site_wide_admin_directory, module_type, supports_scoping_p, documentation_url, data_model_url, description ) values ( 'spam','Spam System','/spam/','/spam/admin/','/admin/spam/','system','f', '/doc/spam.html','doc/sql/spam/sql', 'The spam system: for periodic and group mailings'); -- create an administration group for spam tracker administration begin administration_group_add ('Spam Admin Staff', 'spam', 'spam', NULL, 'f', '/spam/admin/'); end; / create sequence spam_id_sequence; create table spam_history ( spam_id integer primary key, from_address varchar(100), pathname varchar(700), title varchar(200), template_p char(1) default 'f' check (template_p in ('t','f')), -- message body text in multiple formats -- text/plain, text/aol-html, text/html body_plain clob, body_aol clob, body_html clob, -- query which over users_spammable.* to enumerate the recipients of this spam user_class_query varchar(4000), creation_date date not null, cc_emails varchar(4000), -- to which users did we send this? user_class_description varchar(4000), creation_user not null references users(user_id), creation_ip_address varchar(50) not null, send_date date, -- we'll increment this after every successful email n_sent integer default 0, -- values: unsent, sending, sent, cancelled status varchar(16), -- keep track of the last user_id we sent a copy of this spam to -- so we can resume after a server restart last_user_id_sent integer references users, begin_send_time date, finish_send_time date ); -- table for administrator to set up daily spam file locations create table daily_spam_files ( file_prefix varchar(400), subject varchar(2000), target_user_class_id integer, user_class_description varchar(4000), from_address varchar(200), template_p char(1) default 'f' check (template_p in ('t','f')), period varchar(64) default 'daily' check (period in ('daily','weekly', 'monthly', 'yearly')), day_of_week integer, day_of_month integer, day_of_year integer ); -- pl/sql proc to guess email type create table default_email_types ( pattern varchar(200), mail_type varchar(64) ); -- Here are some default values. Overriden by server startup routine in /tcl/spam-daemon.tcl insert into default_email_types (pattern, mail_type) values ('%hotmail.com', 'text/html'); insert into default_email_types (pattern, mail_type) values ('%aol.com', 'text/aol-html'); insert into default_email_types (pattern, mail_type) values ('%netscape.net', 'text/html'); -- function to guess an email type, using the default_email_types patterns table CREATE OR REPLACE FUNCTION guess_user_email_type (v_email varchar) RETURN varchar IS cursor mail_cursor is select * from default_email_types; BEGIN FOR mail_val IN mail_cursor LOOP IF upper(v_email) LIKE upper(mail_val.pattern) THEN RETURN mail_val.mail_type; END IF; END LOOP; -- default RETURN 'text/html'; END guess_user_email_type; / show errors -- Trigger on INSERT into users which guesses users preferred email type -- based on their email address CREATE OR REPLACE TRIGGER guess_email_pref_tr AFTER INSERT ON users FOR each row BEGIN UPDATE users_preferences set email_type = guess_user_email_type(:new.email) where user_id = :new.user_id; IF SQL%NOTFOUND THEN INSERT INTO users_preferences (user_id, email_type) VALUES (:new.user_id, guess_user_email_type(:new.email)); END IF; END; / show errors -- loop over all users, lookup users_prefs.email_type. -- if email_type is null, set it to default guess based on email addr. CREATE OR REPLACE PROCEDURE init_email_types IS CURSOR c1 IS SELECT up.user_id as prefs_user_id, users.email, users.user_id from users, users_preferences up WHERE users.user_id = up.user_id(+); prefs_user_id users_preferences.user_id%TYPE; BEGIN FOR c1_val IN c1 LOOP -- since we did an outer join, if the user_prefs user_id field is null, then -- no record exists, so do an insert. Else do an update IF c1_val.prefs_user_id IS NULL THEN INSERT INTO users_preferences (user_id, email_type) values (c1_val.user_id, guess_user_email_type(c1_val.email)); ELSE UPDATE users_preferences set email_type = guess_user_email_type(c1_val.email) WHERE user_id = c1_val.user_id; END IF; END LOOP; COMMIT; END init_email_types; / show errors