Index: openacs-4/packages/ecommerce/sql/postgresql/ecommerce-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/sql/postgresql/ecommerce-create.sql,v diff -u -r1.15.2.1 -r1.15.2.2 --- openacs-4/packages/ecommerce/sql/postgresql/ecommerce-create.sql 19 Oct 2002 19:31:56 -0000 1.15.2.1 +++ openacs-4/packages/ecommerce/sql/postgresql/ecommerce-create.sql 19 Oct 2002 19:50:48 -0000 1.15.2.2 @@ -78,7 +78,7 @@ template_id integer, template_name varchar(200), template varchar(4000), - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -129,7 +129,7 @@ category_id integer, category_name varchar(100), sort_key numeric, - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -177,7 +177,7 @@ category_id integer, subcategory_name varchar(100), sort_key numeric, - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -235,7 +235,7 @@ subcategory_id integer, subsubcategory_name varchar(100), sort_key numeric, - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -372,7 +372,7 @@ -- before then, it's just not buyable) available_date timestamp(0)default now() not null, announcements varchar(4000), - announcements_expire timestamp, + announcements_expire timestamp(0), -- if there's a web site with more info about the product url varchar(300), template_id integer references ec_templates, @@ -407,7 +407,7 @@ create table ec_products_audit ( product_id integer, product_name varchar(200), - creation_date timestamp, + creation_date timestamp(0), one_line_description varchar(400), detailed_description varchar(4000), search_keywords varchar(4000), @@ -418,13 +418,13 @@ dirname varchar(200), present_p boolean default 't', active_p boolean default 't', - available_date timestamp, + available_date timestamp(0), announcements varchar(4000), - announcements_expire timestamp, + announcements_expire timestamp(0), url varchar(300), template_id integer, stock_status char(1) check (stock_status in ('o','q','m','s','i')), - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -515,11 +515,11 @@ sale_price_id integer, product_id integer, sale_price numeric, - sale_begins timestamp, - sale_ends timestamp, + sale_begins timestamp(0), + sale_ends timestamp(0), sale_name varchar(30), offer_code varchar(20), - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -566,7 +566,7 @@ create table ec_product_series_map_audit ( series_id integer, component_id integer, - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -660,7 +660,7 @@ create table ec_user_classes_audit ( user_class_id integer, user_class_name varchar(200), -- e.g., student - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -704,7 +704,7 @@ product_id integer, user_class_id integer, price numeric, - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -767,7 +767,7 @@ category_id integer, subcategory_id integer, subsubcategory_id integer, - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -846,7 +846,7 @@ user_id integer, user_class_id integer, user_class_approved_p boolean, - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -888,7 +888,7 @@ create table ec_product_links_audit ( product_a integer, product_b integer, - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -926,7 +926,7 @@ rating numeric, -- in some systems, the administrator will have to approve comments first approved_p boolean, - comment_date timestamp, + comment_date timestamp(0), last_modified timestamp(0)not null, last_modifying_user integer not null references users, modified_ip_address varchar(20) not null @@ -944,7 +944,7 @@ one_line_summary varchar(300), rating numeric, approved_p boolean, - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -981,7 +981,7 @@ product_id integer not null references ec_products, author_name varchar(100), publication varchar(100), - review_date timestamp, + review_date timestamp(0), -- in HTML format review text, display_p boolean, @@ -998,11 +998,11 @@ product_id integer, author_name varchar(100), publication varchar(100), - review_date timestamp, + review_date timestamp(0), -- in HTML format review text, display_p boolean, - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -1052,7 +1052,7 @@ product_id integer, category_id integer, publisher_favorite_p boolean, - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -1100,7 +1100,7 @@ product_id integer, subcategory_id integer, publisher_favorite_p boolean, - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -1146,7 +1146,7 @@ product_id integer, subsubcategory_id integer, publisher_favorite_p boolean, - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -1200,7 +1200,7 @@ default_value varchar(100), -- column type for oracle (i.e. text, varchar(50), integer, ...) column_type varchar(100), - creation_date timestamp, + creation_date timestamp(0), active_p boolean default 't', last_modified timestamp(0)not null, last_modifying_user integer not null references users, @@ -1212,9 +1212,9 @@ field_name varchar(100), default_value varchar(100), column_type varchar(100), - creation_date timestamp, + creation_date timestamp(0), active_p boolean default 't', - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -1257,7 +1257,7 @@ create table ec_custom_p_field_values_audit ( product_id integer, - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -1291,7 +1291,7 @@ -- often will not be known user_id integer references users, ip_address varchar(20) not null, - start_time timestamp, + start_time timestamp(0), http_user_agent varchar(4000) ); @@ -1343,11 +1343,11 @@ -- information recorded upon FSM state changes -- we need this to figure out if order is stale -- and should be offered up for removal - in_basket_date timestamp, - confirmed_date timestamp, - authorized_date timestamp, - voided_date timestamp, - expired_date timestamp, + in_basket_date timestamp(0), + confirmed_date timestamp(0), + authorized_date timestamp(0), + voided_date timestamp(0), + expired_date timestamp(0), -- base shipping, which is added to the amount charged for each item shipping_charged numeric, shipping_refunded numeric, @@ -1400,17 +1400,17 @@ -- a trigger fills address_id in automatically if it's null address_id integer references ec_addresses, shipment_date timestamp(0)not null, - expected_arrival_date timestamp, + expected_arrival_date timestamp(0), carrier varchar(50), -- e.g., 'fedex' tracking_number varchar(24), -- only if we get confirmation from carrier that the goods -- arrived on a specific date - actual_arrival_date timestamp, + actual_arrival_date timestamp(0), -- arbitrary info from carrier, e.g., 'Joe Smith signed for it' actual_arrival_detail varchar(4000), -- for things that aren't really shipped like services shippable_p boolean default 't', - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20) ); @@ -1441,13 +1441,13 @@ shipment_id integer, order_id integer, address_id integer, - shipment_date timestamp, - expected_arrival_date timestamp, + shipment_date timestamp(0), + expected_arrival_date timestamp(0), carrier varchar(50), tracking_number varchar(24), - actual_arrival_date timestamp, + actual_arrival_date timestamp(0), actual_arrival_detail varchar(4000), - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -1508,13 +1508,13 @@ style_choice varchar(4000), shipment_id integer references ec_shipments, -- this is the date that user put this item into their shopping basket - in_cart_date timestamp, - voided_date timestamp, + in_cart_date timestamp(0), + voided_date timestamp(0), voided_by integer references users, - expired_date timestamp, + expired_date timestamp(0), item_state varchar(50) default 'in_basket', -- NULL if not received back - received_back_date timestamp, + received_back_date timestamp(0), -- columns for reporting (e.g., what was done, what was made) price_charged numeric, price_refunded numeric, @@ -1707,7 +1707,7 @@ add_exp_amount_per_item numeric, add_exp_amount_by_weight numeric, default_template integer, - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -1758,7 +1758,7 @@ usps_abbrev char(2), tax_rate numeric, shipping_p boolean, - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -1851,7 +1851,7 @@ price_guarantee_policy varchar(4000), delivery_policy varchar(4000), installation_policy varchar(4000), - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -1956,7 +1956,7 @@ price_guarantee_policy varchar(4000), delivery_policy varchar(4000), installation_policy varchar(4000), - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -2050,10 +2050,10 @@ stock_status char(1) check (stock_status in ('o','q','m','s','i')), special_offer_p boolean, special_offer_html varchar(500), - offer_begins timestamp, - offer_ends timestamp, + offer_begins timestamp(0), + offer_ends timestamp(0), deleted_p boolean default 'f', - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), -- This differs from the deleted_p column! @@ -2110,14 +2110,14 @@ -- entire amount is used up (to speed up -- queries) amount_remaining_p boolean default 't', - issue_date timestamp, - authorized_date timestamp, - claimed_date timestamp, + issue_date timestamp(0), + authorized_date timestamp(0), + claimed_date timestamp(0), -- customer service rep who issued it issued_by integer references users, -- customer who purchased it purchased_by integer references users, - expires timestamp, + expires timestamp(0), user_id integer references users, -- if it's unclaimed, claim_check will be filled in, -- and user_id won't be filled in @@ -2128,7 +2128,7 @@ certificate_to varchar(100), certificate_from varchar(100), recipient_email varchar(100), - voided_date timestamp, + voided_date timestamp(0), voided_by integer references users, reason_for_void varchar(4000), last_modified timestamp(0)not null, @@ -2173,21 +2173,21 @@ gift_certificate_id integer, gift_certificate_state varchar(50), amount numeric, - issue_date timestamp, - authorized_date timestamp, + issue_date timestamp(0), + authorized_date timestamp(0), issued_by integer, purchased_by integer, - expires timestamp, + expires timestamp(0), user_id integer, claim_check varchar(50), certificate_message varchar(200), certificate_to varchar(100), certificate_from varchar(100), recipient_email varchar(100), - voided_date timestamp, + voided_date timestamp(0), voided_by integer, reason_for_void varchar(4000), - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -2226,7 +2226,7 @@ gift_certificate_id integer not null references ec_gift_certificates, order_id integer references ec_orders, amount_used numeric, - used_date timestamp, + used_date timestamp(0), amount_reinstated numeric, reinstated_date timestamp ); @@ -2251,7 +2251,7 @@ -- will have to get other info in order to identify the user create table ec_user_identification ( user_identification_id integer not null primary key, - date_added timestamp, + date_added timestamp(0), user_id integer references users, email varchar(100), first_names varchar(100), @@ -2290,7 +2290,7 @@ customer_service_rep integer references users, user_identification_id integer not null references ec_user_identification, - interaction_date timestamp, + interaction_date timestamp(0), interaction_originator varchar(20) not null, -- e.g. customer, customer-service-rep, automatic interaction_type varchar(30) not null, -- e.g. email, phone_call -- will be filled in if the customer-originated interaction is @@ -2329,7 +2329,7 @@ -- may be null if this issue isn't associated with a gift certificate gift_certificate_id integer references ec_gift_certificates, open_date timestamp(0)not null, - close_date timestamp, + close_date timestamp(0), -- customer service reps who closed the issue closed_by integer references users, -- we never really delete issues @@ -2404,7 +2404,7 @@ picklist_item varchar(100), picklist_name varchar(100), sort_key numeric, - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -2478,7 +2478,7 @@ variables varchar(1000), when_sent varchar(1000), issue_type_list varchar(100), - last_modified timestamp, + last_modified timestamp(0), last_modifying_user integer, modified_ip_address varchar(20), delete_p boolean default 'f' @@ -2550,15 +2550,15 @@ create table ec_spam_log ( spam_id integer not null primary key, - spam_date timestamp, + spam_date timestamp(0), spam_text varchar(4000), -- the following are all criteria used in choosing the users to be spammed mailing_list_category_id integer references ec_categories, mailing_list_subcategory_id integer references ec_subcategories, mailing_list_subsubcategory_id integer references ec_subsubcategories, user_class_id integer references ec_user_classes, product_id integer references ec_products, - last_visit_start_date timestamp, + last_visit_start_date timestamp(0), last_visit_end_date timestamp ); @@ -2607,11 +2607,11 @@ -- There's no need to set this for refunds. Refunds are always to be captured. to_be_captured_p boolean, inserted_date timestamp(0)not null, - authorized_date timestamp, + authorized_date timestamp(0), -- set when to_be_captured_p becomes 't'; used in cron jobs - to_be_captured_date timestamp, - marked_date timestamp, - refunded_date timestamp, + to_be_captured_date timestamp(0), + marked_date timestamp(0), + refunded_date timestamp(0), -- if the transaction failed, this will keep the cron jobs from continuing -- to retry it failed_p boolean default 'f', @@ -2663,13 +2663,13 @@ create table ec_problems_log ( problem_id integer not null primary key, - problem_date timestamp, + problem_date timestamp(0), problem_details varchar(4000), -- if it's related to an order order_id integer references ec_orders, -- if it's related to a gift certificate gift_certificate_id integer references ec_gift_certificates, - resolved_date timestamp, + resolved_date timestamp(0), resolved_by integer references users );