Changes and updates to bitcommerce

Created by: spiderr, Last modification: 23 Sep 2009 (19:34 UTC)
Changes to the BitcommercePackage

  • create common_inc.php file to provide central place for shared functions. Have made good headway on massive reduction of duplicate functions in includes/ and admin/includes/
  • create new core CommerceProduct class for central query location and remove all possible embedded queries related to products, parituclarly listing queries in sideboxes
  • Migrate ))$_SESSIONcustomer_id>'customer_id'(( to $gBitUser->isRegistered()
  • Replace all $_GET and $_POST to $_REQUEST - seems to be some issues with this en masse do to very poor HTML form naming conventions
  • remove all traces of zencart sideboxes modules and move to bitwaver mod_/.tpl file pairs. control and rendering now uses bitweaver central layout mechanism
  • fixed lots and lots of notices and then gave up as they are endless. Still plan on making it to run bitcommerce with all error_reporting on.
  • big move and merge of pages tpl files to matching modules/pages/ directory
  • nuke all individual requires of currencies.php, now in common_inc.php
  • added bulk currency import
  • BIG file re-arrangement and move includes/modules/pages directory to the top level pages/ directory. The ~70 directories in pages are what need to be converted to smarty templates to make bitweaver cool.
  • make sure failed payflowpro transactions are logged
  • add telephone to address_book entries and in order history
  • pull out the top of the hard coding of modules, and make any directory in includes/modules/ show up in the admin menu, also allow the .php file to reside *inside* a directory of the same name (allows for proprietary modules to be easily kept in spearate source server)

Schema changes

This is an ongoing list of *any* changes to *any* DataDict array in a schema_inc in *any* package.

15-SEP-2009 bitcommerce major refactor of order and shopping cart, move to database only carts (no more session junk) /spider

ALTER TABLE com_products RENAME products_price_sorter TO lowest_purchase_price;

DELETE FROM com_customers_basket_att;
DELETE FROM com_customers_basket;

ALTER TABLE com_customers_basket RENAME products_id TO products_key;
ALTER TABLE com_customers_basket ADD products_id INT NOT NULL;
ALTER TABLE com_customers_basket ADD CONSTRAINT com_customers_basket_products_ref FOREIGN KEY (products_id) REFERENCES com_products(products_id);
ALTER TABLE com_customers_basket DROP customers_basket_date_added;
ALTER TABLE com_customers_basket ADD date_added TIMESTAMP;
ALTER TABLE com_customers_basket ADD cookie VARCHAR(64);

ALTER TABLE com_customers_basket_att DROP COLUMN customers_id;
ALTER TABLE com_customers_basket_att DROP COLUMN products_id;
ALTER TABLE com_customers_basket_att ADD customers_basket_id int NOT NULL;
ALTER TABLE com_customers_basket_att ADD CONSTRAINT com_customers_basket_att_ref FOREIGN KEY (customers_basket_id) REFERENCES com_customers_basket(customers_basket_id);

ALTER TABLE com_customers_basket_att DROP products_options_sort_order;
ALTER TABLE com_customers_basket_att RENAME COLUMN products_options_id TO products_options_key;
ALTER TABLE com_customers_basket_att ADD products_options_id INT NOT NULL;
ALTER TABLE com_customers_basket_att RENAME products_options_value_id TO products_options_values_id;
ALTER TABLE com_customers_basket RENAME customers_basket_quantity TO products_quantity;
01-AUG-2007 bitcommerce trim products_options_html_attributes field name /lsces

ALTER TABLE com_products_options
RENAME products_options_html_attributes TO products_options_html_attrib
04-APR-2007 bitcommerce add commission payment table /spider

CREATE TABLE com_commissions_payments ( commissions_payment_id SERIAL PRIMARY KEY, payee_user_id INT NOT NULL, payer_user_id INT NOT NULL, period_start_date BIGINT NOT NULL, period_end_date BIGINT NOT NULL, payment_date BIGINT NOT NULL, payment_amount NUMERIC(15,2) NOT NULL, payment_method VARCHAR(250) NOT NULL, payment_reference_number VARCHAR(250) NOT NULL, payment_note TEXT, CONSTRAINT com_commissions_payments_payee_ref FOREIGN KEY (payee_user_id) REFERENCES users_users(user_id), CONSTRAINT com_commissions_payments_payer_ref FOREIGN KEY (payer_user_id) REFERENCES users_users(user_id) );
04-FEB-2007 bitcommerce Add html attributes field for options controller to provide a handy place to place javascript /spider

ALTER TABLE com_products_options ADD products_options_html_attributes TEXT;
06-JAN-2007 bitcommerce Massive normailization and simplication of the products options/values/attributes mess into simple "Product Options" with a table for the options (still called product_attributes for now) and a mapping table, com_products_options_map /spider

ALTER TABLE com_products_options ADD PRIMARY KEY(products_options_id);
ALTER TABLE com_products_options_values ADD PRIMARY KEY(products_options_values_id);

CREATE TABLE com.com_products_attributes_normal ( products_attributes_id SERIAL, products_id INT4 NOT NULL, products_options_id INT4 NOT NULL, products_options_values_id INT4 NOT NULL, options_values_price NUMERIC, price_prefix VARCHAR(1), products_options_sort_order INT4, product_attribute_is_free SMALLINT, products_attributes_wt FLOAT8, products_attributes_wt_pfix VARCHAR(1), attributes_display_only SMALLINT, attributes_default SMALLINT, attributes_discounted SMALLINT DEFAULT 1 NOT NULL, attributes_image VARCHAR(64), attributes_price_base_inc SMALLINT DEFAULT 1 NOT NULL, attributes_price_onetime NUMERIC, attributes_price_factor NUMERIC, attributes_pf_offset NUMERIC, attributes_pf_onetime NUMERIC, attributes_pf_onetime_offset NUMERIC, attributes_qty_prices TEXT, attributes_qty_prices_onetime TEXT, attributes_price_words NUMERIC, attributes_price_words_free INT2, attributes_price_letters NUMERIC, attributes_price_letters_free INT2, attributes_required SMALLINT , CONSTRAINT "prod_attr_products_id_ref" FOREIGN KEY ( "products_id" ) REFERENCES "com_products"( "products_id" ), CONSTRAINT "prod_attr_options_id_ref" FOREIGN KEY ( "products_options_id" ) REFERENCES "com_products_options"( "products_options_id" ), CONSTRAINT "prod_attr_options_val_id_ref" FOREIGN KEY ( "products_options_values_id" ) REFERENCES "com_products_options_values"( "products_options_values_id" ), PRIMARY KEY (products_options_values_id) );

CREATE TABLE com.com_products_options_map (products_id INT NOT NULL, products_options_values_id INT NOT NULL, override_price NUMERIC );
ALTER TABLE com_products_options_map ADD PRIMARY KEY (products_id,products_options_values_id);
CREATE INDEX com_products_options_map_prod_idx ON com_products_options_map(products_id);
CREATE INDEX com_products_options_map_att_idx ON com_products_options_map(products_options_values_id);

INSERT INTO com_products_attributes_normal (SELECT * FROM com_products_attributes WHERE products_attributes_id IN (select products_attributes_id FROM (select distinct options_id, options_values_id, MIN(products_attributes_id) AS products_attributes_id FROM com_products_attributes group by options_id, options_values_id) AS sub));

UPDATE com_products_attributes_normal SET products_attributes_id = nextval('com_products_attributes_normal_products_attributes_id_seq');

INSERT INTO com.com_products_options_map (SELECT cpa.products_id, cpan.products_options_values_id FROM com_products_attributes cpa INNER JOIN com_products_attributes_normal cpan ON(cpa.options_id=cpan.products_options_id AND cpa.options_values_id=cpan.products_options_values_id));

-- Swap the new and the old
ALTER TABLE com_products_attributes RENAME TO com_products_attributes_old;
ALTER TABLE com_products_attributes_normal RENAME TO com_products_attributes;
ALTER TABLE com_products_attributes DROP COLUMN products_id;

ALTER TABLE com_products_attributes ADD products_options_values_name VARCHAR(64);
UPDATE com_products_attributes SET products_options_values_name=(SELECT products_options_values_name FROM com_products_options_values cpov WHERE cpov.products_options_values_id=com_products_attributes.products_options_values_id);
ALTER TABLE com_products_attributes ALTER products_options_values_name SET NOT NULL;
ALTER TABLE com_products_options_values RENAME TO drop_com_products_options_values;
ALTER TABLE com_prd_opt_val_to_prd_opt RENAME TO drop_com_prd_opt_val_to_prd_opt;
03-SEP-2006 bitcommerce add user_id to com_orders_status_history /spider

ALTER TABLE com_orders_status_history ADD COLUMN user_id INT;
ALTER TABLE com_orders_status_history ADD CONSTRAINT com_orders_status_history_user_ref FOREIGN KEY (user_id) REFERENCES users_users(user_id);
06-APR-2006 bitcommerce Prefix renaming /spider
There was much table prefix madness, so I hardcoded "com_" to schema_inc and database_tables.php, BITCOMMERCE_DB_PREFIX now defaults to BIT_DB_PREFIX if not defined, and is intended to be for placing commerce in a separate schema. install should be working again as expected. The only people affected are those who overrode the default BITCOMMERCE_DB_PREFIX with a custom value, such as for a separate schema. If your old custom BITCOMMERCE_DB_PREFIX was "finance`.`com", the new value is just "finance`.`"
20-FEB-2006 bitcommerce removed SQL reserved words /lsces

ALTER TABLE com.com_counter_history RENAME month TO ch_month;
ALTER TABLE com_currencies RENAME value TO currency_value;
ALTER TABLE com_orders_total RENAME value TO orders_value;
ALTER TABLE com_sessions RENAME value TO sess_value;
ALTER TABLE com_authorizenet RENAME "time" TO az_time;
10-FEB-2006 bitcommerce add products_commission /spider

ALTER TABLE com_products ADD COLUMN products_commission NUMERIC(15,2);
ALTER TABLE com_orders_products ADD COLUMN products_commision NUMERIC(15,2);
23-NOV-2005 bitcommerce add products_manufacturers_model from gilesw gilesw

ALTER TABLE com_products ADD products_manufacturers_model VARCHAR(32);
02-NOV-2005 bitcommerce add suppliers info from gilesw spiderr

CREATE TABLE com_suppliers ( suppliers_id SERIAL, suppliers_name VARCHAR(32), suppliers_image VARCHAR(64), date_added TIMESTAMP, last_modified TIMESTAMP, PRIMARY KEY (suppliers_id) );
CREATE TABLE com_suppliers_info ( suppliers_id INT4, languages_id INT4, suppliers_url VARCHAR(255), url_clicked INT4, date_last_click TIMESTAMP , CONSTRAINT supp_info_supp_id_ref FOREIGN KEY ( suppliers_id ) REFERENCES com_suppliers( suppliers_id ) );
ALTER TABLE com_products ADD column suppliers_id INTEGER;
ALTER TABLE com_products ADD products_barcode INTEGER;
ALTER TABLE com_products ADD CONSTRAINT prod_supp_id_ref FOREIGN KEY ( suppliers_id ) REFERENCES com_suppliers( suppliers_id );
CREATE INDEX sup_name_zen_idx ON com_suppliers (suppliers_name) ;
02-NOV-2005 bitcommerce add shipping code for automated processes that might need it, add Cost Of Goods Sold column for orders spiderr

ALTER TABLE com_orders ADD column shipping_method_code VARCHAR(255);
ALTER TABLE com_orders_products ADD column products_cogs NUMERIC;
08-OCT-2005 bitcommerce add telephone for address entires and order history spiderr

ALTER TABLE com_address_book ADD COLUMN entry_telephone varchar(32);
ALTER TABLE com_orders ADD COLUMN delivery_telephone varchar(32);
ALTER TABLE com_orders ADD COLUMN billing_telephone varchar(32);
08-OCT-2005 bitcommerce add customer id to payflowpro logging spiderr

ALTER TABLE com_pubs_credit_card_log ADD COLUMN customers_id int;
UPDATE com_pubs_credit_card_log set customers_id = (SELECT customers_id FROM com_orders WHERE orders_id=com_pubs_credit_card_log.orders_id);
ALTER TABLE com_pubs_credit_card_log ALTER customers_id SET not null;


by Kozuch, 08 Feb 2007 (22:19 UTC)
I am happy to see bitcommerce package makes progress(es)! (:mrgreen:)

missing columns

by nexe, 22 Nov 2007 (05:58 UTC)
the following columns are missing from com_products using the latest HEAD release please reply with what type they are suppose to be.

  Page 1 of 1  1