-- -- ecommerce.sql -- -- by eveander@arsdigita.com, April 1999 -- -- Besides the tables defined here, you also need to import -- zip_codes, which contains the following columns: -- ZIP_CODE NOT NULL VARCHAR2(10) -- STATE_CODE CHAR(2) -- CITY_NAME VARCHAR2(32) -- COUNTY_NAME VARCHAR2(32) -- LONGITUDE NUMBER(9,6) -- LATITUDE NUMBER(9,6) -- Each table in ecommerce has a column for user_id, ip_address, -- creation_date and last_modified to assist in auditing user -- inserts, updates, and deletes. -- Audit tables store information about entries in the main ec_ tables. -- They have an column for each column of the main table, plus -- a column for marking when a logged entry was for a deleted row. -- product display templates create sequence ec_template_id_sequence start with 2; -- I should have named this product_templates because now we -- have other kinds of templates. create table ec_templates ( template_id integer not null primary key, template_name varchar(200), template varchar(4000), last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create table ec_templates_audit ( template_id integer, template_name varchar(200), template varchar(4000), last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -- A trigger is used to move information from the main table to -- the audit table create or replace trigger ec_templates_audit_tr before update or delete on ec_templates for each row begin insert into ec_templates_audit ( template_id, template_name, template, last_modified, last_modifying_user, modified_ip_address ) values ( :old.template_id, :old.template_name, :old.template, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors -- This inserts the default template into the ec_templates table insert into ec_templates ( template_id, template_name, template, last_modified, last_modifying_user, modified_ip_address ) values ( 1,'Default', '
' || CHR(10) || '' || CHR(10) || '
| ' || CHR(10) || '' || CHR(10) || '<%= [ec_add_to_cart_link $db $product_id] %>' || CHR(10) || ' | ' || CHR(10) || '
' || CHR(10) || '<%= $detailed_description %>' || CHR(10) || CHR(10) || '<%= [ec_display_product_purchase_combinations $db $product_id] %>' || CHR(10) || CHR(10) || '<%= [ec_product_links_if_they_exist $db $product_id] %>' || CHR(10) || CHR(10) || '<%= [ec_professional_reviews_if_they_exist $db $product_id] %>' || CHR(10) || CHR(10) || '<%= [ec_customer_comments $db $product_id $comments_sort_by] %>' || CHR(10) || CHR(10) || '
' || CHR(10) || CHR(10) || '<%= [ec_mailing_list_link_for_a_product $db $product_id] %>' || CHR(10) || CHR(10) || '<%= [ec_footer $db] %>' || CHR(10) || '' || CHR(10) || '