diff -Naur ubercart/payment/uc_credit/uc_credit.install ubercart_pgsqlfix3/payment/uc_credit/uc_credit.install --- ubercart/payment/uc_credit/uc_credit.install Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/payment/uc_credit/uc_credit.install Tue Feb 5 12:39:21 2008 @@ -24,24 +24,24 @@ break; case 'pgsql': db_query("CREATE TABLE {uc_payment_credit} ( - credit_id integer NOT NULL, + credit_id serial NOT NULL, order_id integer NOT NULL, cc_type varchar(255) NOT NULL, cc_owner varchar(255) NOT NULL, cc_number varchar(255) NOT NULL, - cc_exp_month smallint NOT NULL, - cc_exp_year smallint NOT NULL, + cc_exp_month varchar(255) NOT NULL, + cc_exp_year varchar(255) NOT NULL, cc_cvv varchar(255) NOT NULL, cc_bank varchar(255) NOT NULL, PRIMARY KEY (credit_id) );"); - db_query("CREATE INDEX {uc_payment_credit}_order_id ON {uc_payment_credit} (order_id)"); + db_query("CREATE INDEX {uc_payment_credit}_order_id ON {uc_payment_credit} (order_id)"); break; } } function uc_credit_uninstall() { - db_query("DROP TABLE IF EXISTS {uc_payment_credit}"); + db_query("DROP TABLE {uc_payment_credit}"); variable_del('uc_credit_checkout_process'); variable_del('uc_credit_checkout_no_store'); variable_del('uc_credit_fail_message'); diff -Naur ubercart/payment/uc_payment/uc_payment.install ubercart_pgsqlfix3/payment/uc_payment/uc_payment.install --- ubercart/payment/uc_payment/uc_payment.install Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/payment/uc_payment/uc_payment.install Tue Feb 5 12:39:21 2008 @@ -42,25 +42,26 @@ break; case 'pgsql': db_query("CREATE TABLE {uc_payment_check} ( - check_id integer NOT NULL, + check_id serial NOT NULL, order_id integer NOT NULL, clear_date integer NOT NULL, PRIMARY KEY (check_id) );"); - db_query("CREATE INDEX {uc_payment_check}_order_id ON {uc_payment_check} (order_id)"); + db_query("CREATE INDEX {uc_payment_check}_order_id ON {uc_payment_check} (order_id)"); db_query("CREATE TABLE {uc_payment_cod} ( order_id integer NOT NULL, delivery_month smallint NOT NULL, delivery_day smallint NOT NULL, delivery_year smallint NOT NULL, + PRIMARY KEY (order_id) );"); db_query("CREATE TABLE {uc_payment_other} ( - order_id mediumint(9) NOT NULL, + order_id integer NOT NULL, description varchar(64) NOT NULL, - PRIMARY KEY order_id (order_id) + PRIMARY KEY (order_id) );"); db_query("CREATE TABLE {uc_payment_receipts} ( - receipt_id integer NOT NULL, + receipt_id serial NOT NULL, order_id integer NOT NULL, method varchar(32) NOT NULL, amount decimal(10,2) NOT NULL, @@ -70,21 +71,33 @@ received integer NOT NULL, PRIMARY KEY (receipt_id) );"); - db_query("CREATE INDEX {uc_payment_receipts}_order_id ON {uc_payment_receipts} (order_id)"); + db_query("CREATE INDEX {uc_payment_receipts}_order_id ON {uc_payment_receipts} (order_id)"); db_query("INSERT INTO {uc_order_statuses} (order_status_id, title, state, weight, locked) VALUES ('payment_received', '". $t('Payment received') ."', 'payment_received', 10, 1);"); break; } } function uc_payment_uninstall() { - db_query("DROP TABLE IF EXISTS {uc_payment_check}"); - db_query("DROP TABLE IF EXISTS {uc_payment_cod}"); - db_query("DROP TABLE IF EXISTS {uc_payment_other}"); - db_query("DROP TABLE IF EXISTS {uc_payment_receipts}"); + switch($GLOBALS['db_type']){ + case 'mysql': + case 'mysqli': + db_query("DROP TABLE IF EXISTS {uc_payment_check}"); + db_query("DROP TABLE IF EXISTS {uc_payment_cod}"); + db_query("DROP TABLE IF EXISTS {uc_payment_other}"); + db_query("DROP TABLE IF EXISTS {uc_payment_receipts}"); + db_query("DELETE FROM {sequences} WHERE name LIKE '{uc_payment_receipts}_receipt_id'"); + db_query("DELETE FROM {sequences} WHERE name LIKE '{uc_payment_check}_check_id'"); + break; + case 'pgsql': + db_query("DROP TABLE {uc_payment_check}"); + db_query("DROP TABLE {uc_payment_cod}"); + db_query("DROP TABLE {uc_payment_other}"); + db_query("DROP TABLE {uc_payment_receipts}"); + break; + } + db_query("DELETE FROM {variable} WHERE name LIKE 'uc_pg_%%'"); db_query("DELETE FROM {variable} WHERE name LIKE 'uc_payment_method_%%'"); - db_query("DELETE FROM {sequences} WHERE name LIKE '{uc_payment_receipts}_receipt_id'"); - db_query("DELETE FROM {sequences} WHERE name LIKE '{uc_payment_check}_check_id'"); variable_del('uc_payment_tracking'); variable_del('uc_payment_deleting'); variable_del('uc_payment_logging'); diff -Naur ubercart/payment/uc_paypal/uc_paypal.install ubercart_pgsqlfix3/payment/uc_paypal/uc_paypal.install --- ubercart/payment/uc_paypal/uc_paypal.install Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/payment/uc_paypal/uc_paypal.install Tue Feb 5 12:39:21 2008 @@ -44,7 +44,7 @@ * Implementation of hook_uninstall(). */ function uc_paypal_uninstall() { - db_query("DROP TABLE IF EXISTS {uc_payment_paypal_ipn}"); + db_query("DROP TABLE {uc_payment_paypal_ipn}"); } function uc_paypal_update_1() { diff -Naur ubercart/shipping/uc_flatrate/uc_flatrate.install ubercart_pgsqlfix3/shipping/uc_flatrate/uc_flatrate.install --- ubercart/shipping/uc_flatrate/uc_flatrate.install Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/shipping/uc_flatrate/uc_flatrate.install Tue Feb 5 12:39:21 2008 @@ -25,17 +25,17 @@ db_query("CREATE TABLE {uc_flatrate_products} ( vid integer NOT NULL default 0, nid integer NOT NULL default 0, - mid mediumint(9) unsigned NOT NULL default 0, + mid integer NOT NULL default 0, rate decimal(10,2) NOT NULL default 0.00, PRIMARY KEY (vid, mid) );"); db_query("CREATE TABLE {uc_flatrate_methods} ( - mid mediumint(9) unsigned NOT NULL default 0, + mid serial NOT NULL, title varchar(255) NOT NULL default '', label varchar(255) NOT NULL default '', base_rate decimal(10,2) NOT NULL default 0.00, product_rate decimal(10,2) NOT NULL default 0.00, - PRIMARY KEY (`mid`) + PRIMARY KEY (mid) );"); db_query("CREATE INDEX {uc_flatrate_methods}_mid_idx ON {uc_flatrate_methods}(mid)"); break; diff -Naur ubercart/shipping/uc_quote/uc_quote.install ubercart_pgsqlfix3/shipping/uc_quote/uc_quote.install --- ubercart/shipping/uc_quote/uc_quote.install Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/shipping/uc_quote/uc_quote.install Tue Feb 5 12:39:21 2008 @@ -63,7 +63,7 @@ city varchar(255) NOT NULL default '', zone integer NOT NULL default 0, postal_code varchar(255) NOT NULL default '', - country integer NOT NULL default '', + country integer NOT NULL default 0, PRIMARY KEY (tid) );"); db_query("CREATE TABLE {uc_quote_product_locations} ( @@ -76,7 +76,7 @@ city varchar(255) NOT NULL default '', zone integer NOT NULL default 0, postal_code varchar(255) NOT NULL default '', - country integer NOT NULL default '', + country integer NOT NULL default 0, PRIMARY KEY (nid) );"); db_query("CREATE TABLE {uc_order_quotes} ( @@ -85,7 +85,7 @@ accessorials varchar(255) NOT NULL default '', rate decimal(10,2) NOT NULL default 0.00, quote_form text NOT NULL default '', - UNIQUE KEY oid_quote_method (oid,method) + PRIMARY KEY (oid,method) );"); break; } diff -Naur ubercart/shipping/uc_shipping/uc_shipping.install ubercart_pgsqlfix3/shipping/uc_shipping/uc_shipping.install --- ubercart/shipping/uc_shipping/uc_shipping.install Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/shipping/uc_shipping/uc_shipping.install Tue Feb 5 12:39:21 2008 @@ -60,7 +60,7 @@ break; case 'pgsql': db_query("CREATE TABLE {uc_shipments} ( - sid integer NOT NULL default 0, + sid serial NOT NULL, order_id integer NOT NULL default 0, o_first_name varchar(255) NOT NULL default '', o_last_name varchar(255) NOT NULL default '', @@ -79,7 +79,7 @@ d_city varchar(255) NOT NULL default '', d_zone integer NOT NULL default 0, d_postal_code varchar(255) NOT NULL default '', - d_country integer UNSIGNED NOT NULL default 0, + d_country integer NOT NULL default 0, shipping_method varchar(255) NOT NULL default '', accessorials varchar(255) NOT NULL default '', carrier varchar(255) NOT NULL default '', @@ -91,13 +91,13 @@ PRIMARY KEY (sid) );"); db_query("CREATE TABLE {uc_packages} ( - package_id integer NOT NULL default 0, + package_id serial NOT NULL, order_id integer NOT NULL default 0, shipping_type varchar(255) NOT NULL default '', pkg_type varchar(255) NOT NULL default '', length float NULL, width float NULL, - height float NULL, + height float NULL, length_units varchar(255) NULL, value decimal(10,2) NULL, sid integer NULL, @@ -108,7 +108,7 @@ db_query("CREATE TABLE {uc_packaged_products} ( package_id integer NOT NULL default 0, order_product_id integer NOT NULL default 0, - qty ionteger NOT NULL default 0, + qty integer NOT NULL default 0, PRIMARY KEY (package_id, order_product_id) );"); break; diff -Naur ubercart/shipping/uc_weightquote/uc_weightquote.install ubercart_pgsqlfix3/shipping/uc_weightquote/uc_weightquote.install --- ubercart/shipping/uc_weightquote/uc_weightquote.install Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/shipping/uc_weightquote/uc_weightquote.install Tue Feb 5 12:39:21 2008 @@ -14,7 +14,7 @@ break; case 'pgsql': db_query("CREATE TABLE {uc_weightquote_products} ( - vid integer NOT NULL default 0 + vid integer NOT NULL default 0, nid integer NOT NULL default 0, rate float NOT NULL default 0, PRIMARY KEY (vid) diff -Naur ubercart/uc_attribute/uc_attribute.install ubercart_pgsqlfix3/uc_attribute/uc_attribute.install --- ubercart/uc_attribute/uc_attribute.install Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/uc_attribute/uc_attribute.install Tue Feb 5 12:39:21 2008 @@ -64,23 +64,21 @@ break; case 'pgsql': db_query("CREATE TABLE {uc_attributes} ( - aid integer NOT NULL default 0, - name varchar(255) NOT NULL default ''. + aid serial NOT NULL, + name varchar(255) NOT NULL default '', ordering smallint NOT NULL default 0, PRIMARY KEY (aid) );"); - db_query("CREATE INDEX {uc_attributes}_aid ON {uc_attributes} (aid)"); db_query("CREATE TABLE {uc_attribute_options} ( aid integer NOT NULL default 0, - oid integer NOT NULL default 0, + oid serial NOT NULL, name varchar(255) NOT NULL default '', cost decimal(10,2) NOT NULL default 0.00, price decimal(10,2) NOT NULL default 0.00, - weight float NOT NULL default 0,0, + weight float NOT NULL default 0.0, ordering smallint NOT NULL default 0, PRIMARY KEY (oid) );"); - db_query("CREATE INDEX {uc_attribute_options}_oid ON {uc_attributes_options} (oid)"); db_query("CREATE TABLE {uc_class_attributes} ( pcid varchar(32) NOT NULL default '', aid integer NOT NULL default 0, @@ -118,7 +116,8 @@ db_query("CREATE TABLE {uc_product_adjustments} ( nid integer NOT NULL default 0, combination varchar(255) NOT NULL default '', - model varchar(255) NOT NULL default '' + model varchar(255) NOT NULL default '', + PRIMARY KEY (nid, combination) );"); break; } diff -Naur ubercart/uc_attribute/uc_attribute.module ubercart_pgsqlfix3/uc_attribute/uc_attribute.module --- ubercart/uc_attribute/uc_attribute.module Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/uc_attribute/uc_attribute.module Tue Feb 5 12:39:21 2008 @@ -146,7 +146,7 @@ /** * Implementation of hook_form_alter(). - * +* * Attach option selectors to the form with the "Add to Cart" button. */ function uc_attribute_form_alter($form_id, &$form){ @@ -175,14 +175,24 @@ if (in_array($node->type, array_keys(uc_product_node_info()))){ switch($op){ case 'insert': - db_query("INSERT IGNORE INTO {uc_product_attributes} (nid, aid, ordering, required, default_option) SELECT %d, aid, ordering, required, default_option FROM {uc_class_attributes} WHERE pcid = '%s'", $node->nid, $node->type); - db_query("INSERT IGNORE INTO {uc_product_options} (nid, oid, cost, price, weight, ordering) SELECT %d, oid, cost, price, weight, ordering FROM {uc_class_attribute_options} WHERE pcid = '%s'", $node->nid, $node->type); - break; + switch ($GLOBALS['db_type']){ + case 'mysqli': + case 'mysql': + db_query("INSERT IGNORE INTO {uc_product_attributes} (nid, aid, ordering, required, default_option) SELECT %d, aid, ordering, required, default_option FROM {uc_class_attributes} WHERE pcid = '%s'", $node->nid, $node->type); + db_query("INSERT IGNORE INTO {uc_product_options} (nid, oid, cost, price, weight, ordering) SELECT %d, oid, cost, price, weight, ordering FROM {uc_class_attribute_options} WHERE pcid = '%s'", $node->nid, $node->type); + break; + case 'pgsql': + db_query("INSERT INTO {uc_product_attributes} (nid, aid, ordering, required, default_option) SELECT %d, aid, ordering, required, default_option FROM {uc_class_attributes} WHERE pcid = '%s'", $node->nid, $node->type); + db_query("INSERT INTO {uc_product_options} (nid, oid, cost, price, weight, ordering) SELECT %d, oid, cost, price, weight, ordering FROM {uc_class_attribute_options} WHERE pcid = '%s'", $node->nid, $node->type); + break; + } + + break; case 'delete': db_query("DELETE FROM {uc_product_options} WHERE nid = %d", $node->nid); db_query("DELETE FROM {uc_product_adjustments} WHERE nid = %d", $node->nid); db_query("DELETE FROM {uc_product_attributes} WHERE nid = %d", $node->nid); - break; + break; case 'update index': $output = ''; $attributes = uc_product_get_attributes($node->nid); @@ -197,7 +207,7 @@ while ($adjustment = db_fetch_object($result)){ $output .= '

'. $adjustment->model ."

\n"; } - return $output; + return $output; } } } @@ -294,7 +304,7 @@ * @see uc_attribute_form */ function uc_attribute_default(){ - $result = db_query("SELECT a.aid, a.name, a.ordering, COUNT(ao.oid) AS options FROM {uc_attributes} AS a LEFT JOIN {uc_attribute_options} AS ao ON a.aid = ao.aid GROUP BY a.aid ORDER BY a.ordering, a.name"); + $result = db_query("SELECT a.aid, a.name, a.ordering, COUNT(ao.oid) AS options FROM {uc_attributes} AS a LEFT JOIN {uc_attribute_options} AS ao ON a.aid = ao.aid GROUP BY a.aid, a.name, a.ordering ORDER BY a.ordering, a.name"); $header = array(t('Name'), t('# of Options'), t('Order'), array('data' => t('Operations'), 'colspan' => '3')); $rows = array(); while($attr = db_fetch_object($result)){ @@ -601,7 +611,7 @@ } // Get list of attributes NOT associated with this node. - $result = db_query("SELECT a.aid, a.name FROM {uc_attributes} AS a LEFT JOIN {uc_attribute_options} AS ao ON a.aid = ao.aid GROUP BY aid ORDER BY a.name"); + $result = db_query("SELECT a.aid, a.name FROM {uc_attributes} AS a LEFT JOIN {uc_attribute_options} AS ao ON a.aid = ao.aid GROUP BY a.aid, a.name ORDER BY a.name"); $other_attr = array(); while ($attr = db_fetch_object($result)){ if (!in_array($attr->aid, $chosen_aids)){ @@ -944,7 +954,7 @@ $full_attributes = array(); $values = array(); while ($prod_attr = db_fetch_object($result)){ - $query_select .= " ao$i.aid AS aid$i, ao$i.name AS name$i, ao$i.oid AS oid$i,"; + $query_select .= " ao$i.aid AS aid$i, ao$i.name AS name$i, ao$i.oid AS oid$i, po$i.ordering,"; $query_from .= " ({uc_product_options} AS po$i LEFT JOIN {uc_attribute_options} AS ao$i ON po$i.oid = ao$i.oid AND po$i.nid = %d),"; $values[] = $nid; $query_where .= " ao$i.aid = ". $prod_attr->aid ." AND"; diff -Naur ubercart/uc_cart/uc_cart.install ubercart_pgsqlfix3/uc_cart/uc_cart.install --- ubercart/uc_cart/uc_cart.install Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/uc_cart/uc_cart.install Tue Feb 5 12:39:21 2008 @@ -30,6 +30,7 @@ } } + function uc_cart_uninstall() { db_query("DROP TABLE {uc_cart_products}"); db_query("DELETE FROM {variable} WHERE name LIKE 'uc_pane_%%'"); diff -Naur ubercart/uc_cart_links/uc_cart_links.install ubercart_pgsqlfix3/uc_cart_links/uc_cart_links.install --- ubercart/uc_cart_links/uc_cart_links.install Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/uc_cart_links/uc_cart_links.install Tue Feb 5 12:39:21 2008 @@ -16,7 +16,8 @@ db_query("CREATE TABLE {uc_cart_link_clicks} ( cart_link_id varchar(32) NOT NULL default 0, clicks integer NOT NULL default 0, - last_click integer NOT NULL default 0 + last_click integer NOT NULL default 0, + PRIMARY KEY (cart_link_id) )"); db_query("CREATE INDEX {uc_cart_link_clicks}_cart_link_id ON {uc_cart_link_clicks} (cart_link_id)"); break; diff -Naur ubercart/uc_catalog/uc_catalog.install ubercart_pgsqlfix3/uc_catalog/uc_catalog.install --- ubercart/uc_catalog/uc_catalog.install Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/uc_catalog/uc_catalog.install Tue Feb 5 12:39:21 2008 @@ -31,7 +31,8 @@ ) /*!40100 DEFAULT CHARACTER SET UTF8 */ ;"); break; case 'pgsql': - db_query("CREATE TABLE IF NOT EXISTS {uc_catalog_images} ( + // There will be an error if the table already exists, but this should be OK. + db_query("CREATE TABLE {uc_catalog_images} ( fid integer NOT NULL default 0, tid integer NOT NULL default 0, filename varchar(255) NOT NULL default '', diff -Naur ubercart/uc_catalog/uc_catalog.module ubercart_pgsqlfix3/uc_catalog/uc_catalog.module --- ubercart/uc_catalog/uc_catalog.module Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/uc_catalog/uc_catalog.module Tue Feb 5 12:39:21 2008 @@ -912,20 +912,47 @@ $order = 'p.ordering, n.title'; } $product_types = array_keys(uc_product_node_info()); - $sql = 'SELECT DISTINCT(n.nid), n.sticky, n.title, n.created, p.model + + $sql_count = ''; + switch ($GLOBALS['db_type']) { + case 'mysql': + case 'mysqli': + $sql = 'SELECT DISTINCT(n.nid), n.sticky, n.title, n.created, p.model FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid INNER JOIN {uc_products} AS p ON n.vid = p.vid WHERE tn.tid = %d AND n.status = 1 AND n.type IN ("'. implode('","', $product_types) .'") ORDER BY '. $order; - $sql_count = 'SELECT COUNT(DISTINCT(n.nid)) + $sql_count = 'SELECT COUNT(DISTINCT(n.nid)) FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid INNER JOIN {uc_products} AS p ON n.nid = p.nid WHERE tn.tid = %d AND n.status = 1 AND n.type IN ("'. implode('","', $product_types) .'")'; + break; + case 'pgsql': + $sql = "SELECT DISTINCT(n.nid), n.sticky, n.title, n.created, p.model + FROM {node} n + INNER JOIN {term_node} tn ON n.nid = tn.nid + INNER JOIN {uc_products} AS p ON n.vid = p.vid + WHERE tn.tid = %d AND n.status = 1 + AND n.type IN ('". implode("','", $product_types) ."') + ORDER BY ". $order; + $sql_count = "SELECT DISTINCT n.nid, COUNT(*) + FROM {node} n + INNER JOIN {term_node} tn ON n.nid = tn.nid + INNER JOIN {uc_products} AS p ON n.nid = p.nid + WHERE tn.tid = %d + AND n.status = 1 + AND n.type IN ('". implode("','", $product_types) ."') + GROUP BY n.nid"; + break; + } + + + $sql = db_rewrite_sql($sql); $sql_count = db_rewrite_sql($sql_count); $catalog->products = array(); @@ -996,14 +1023,31 @@ $gridwidth = $gridwidth * $gridlines; } - $sql = 'SELECT DISTINCT(n.nid), n.title - FROM {node} n - INNER JOIN {uc_products} AS p ON n.nid = p.nid - WHERE n.status = 1 - AND n.sticky = 1 - AND n.type IN ("'. implode('","', $product_types) .'") - ORDER BY p.sell_price ASC - LIMIT '.$gridwidth; + $sql = ""; + switch ($GLOBALS['db_type']) { + case 'mysql': + case 'mysqli': + $sql = 'SELECT DISTINCT(n.nid), n.title + FROM {node} n + INNER JOIN {uc_products} AS p ON n.nid = p.nid + WHERE n.status = 1 + AND n.sticky = 1 + AND n.type IN ("'. implode('","', $product_types) .'") + ORDER BY p.sell_price ASC + LIMIT '.$gridwidth; + break; + case 'pgsql': + $sql = "SELECT DISTINCT(n.nid), n.title + FROM {node} n + INNER JOIN {uc_products} AS p ON n.nid = p.nid + WHERE n.status = 1 + AND n.sticky = 1 + AND n.type IN ('". implode("','", $product_types) ."') + ORDER BY p.sell_price ASC + LIMIT ".$gridwidth; + break; + } + $result = db_query($sql); while ($node = db_fetch_object($result)){ $catalog->products[] = $node->nid; diff -Naur ubercart/uc_file/uc_file.install ubercart_pgsqlfix3/uc_file/uc_file.install --- ubercart/uc_file/uc_file.install Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/uc_file/uc_file.install Tue Feb 5 12:39:21 2008 @@ -12,60 +12,72 @@ ) /*!40100 DEFAULT CHARACTER SET UTF8 COLLATE utf8_unicode_ci */ "); db_query("CREATE TABLE {uc_file_products} ( `pfid` mediumint(9) NOT NULL, - `fid` mediumint(9) NOT NULL, + `fid` mediumint(9) NOT NULL, `model` varchar(255) default NULL, - `description` VARCHAR(255) default NULL, - `shippable` BOOL NOT NULL, + `description` VARCHAR(255) default NULL, + `shippable` BOOL NOT NULL, KEY pfid (pfid), - KEY fid (fid) + KEY fid (fid) ) /*!40100 DEFAULT CHARACTER SET UTF8 COLLATE utf8_unicode_ci */ "); db_query("CREATE TABLE {uc_file_users} ( `fid` mediumint(9) NOT NULL, - `uid` mediumint(9) NOT NULL, - `pfid` mediumint(9) default NULL, + `uid` mediumint(9) NOT NULL, + `pfid` mediumint(9) default NULL, `key` varchar(32) NOT NULL, - `granted` int(11) NOT NULL, - `accessed` smallint(5) unsigned NOT NULL default 0, - `addresses` text, + `granted` int(11) NOT NULL, + `accessed` smallint(5) unsigned NOT NULL default 0, + `addresses` text, KEY fid (fid), - KEY uid (uid) + KEY uid (uid) ) /*!40100 DEFAULT CHARACTER SET UTF8 COLLATE utf8_unicode_ci */ "); break; case 'pgsql': - db_query("CREATE TABLE {uc_files} ( - fid integer NOT NULL, - filename varchar(255) NOT NULL - );"); - db_query("CREATE INDEX {uc_files}_fid ON {uc_files} (fid)"); - db_query("CREATE TABLE {uc_file_products} ( - pfid integer NOT NULL, - fid integer NOT NULL, - model varchar(255) default NULL, - description varchar(255) default NULL, - shippable boolean NOT NULL - );"); - db_query("CREATE INDEX {uc_file_products}_pfid ON {uc_file_products} (pfid)"); - db_query("CREATE INDEX {uc_file_products}_fid ON {uc_file_products} (fid)"); - db_query("CREATE TABLE {uc_file_users} ( - fid integer NOT NULL, - uid integer NOT NULL, - pfid integer default NULL, - key varchar(32) NOT NULL, - granted integer NOT NULL, - accessed smallint NOT NULL default 0, - addresses text - );"); - db_query("CREATE INDEX {uc_file_users}_pfid ON {uc_file_users} (pfid)"); - db_query("CREATE INDEX {uc_file_users}_uid ON {uc_file_users} (uid)"); + db_query("CREATE TABLE {uc_files} ( + fid serial NOT NULL, + filename varchar(255) NOT NULL, + PRIMARY KEY (fid) + );"); + db_query("CREATE INDEX {uc_files}_fid ON {uc_files} (fid)"); + db_query("CREATE TABLE {uc_file_products} ( + pfid integer NOT NULL, + fid integer NOT NULL, + model varchar(255) default NULL, + description varchar(255) default NULL, + shippable boolean NOT NULL + );"); + db_query("CREATE INDEX {uc_file_products}_pfid ON {uc_file_products} (pfid)"); + db_query("CREATE INDEX {uc_file_products}_fid ON {uc_file_products} (fid)"); + db_query("CREATE TABLE {uc_file_users} ( + fid integer NOT NULL, + uid integer NOT NULL, + pfid integer default NULL, + key varchar(32) NOT NULL, + granted integer NOT NULL, + accessed smallint NOT NULL default 0, + addresses text + );"); + db_query("CREATE INDEX {uc_file_users}_pfid ON {uc_file_users} (pfid)"); + db_query("CREATE INDEX {uc_file_users}_uid ON {uc_file_users} (uid)"); break; } } function uc_file_uninstall() { - db_query("DROP TABLE IF EXISTS {uc_files}"); - db_query("DROP TABLE IF EXISTS {uc_file_products}"); - db_query("DROP TABLE IF EXISTS {uc_file_users}"); - db_query("DELETE FROM {uc_product_features} WHERE fid = 'file'"); + switch($GLOBALS['db_type']){ + case 'mysqli': + case 'mysql': + db_query("DROP TABLE IF EXISTS {uc_files}"); + db_query("DROP TABLE IF EXISTS {uc_file_products}"); + db_query("DROP TABLE IF EXISTS {uc_file_users}"); + db_query("DELETE FROM {uc_product_features} WHERE fid = 'file'"); + break; + case 'pgsql': + db_query("DROP TABLE {uc_files}"); + db_query("DROP TABLE {uc_file_products}"); + db_query("DROP TABLE {uc_file_users}"); + db_query("DELETE FROM {uc_product_features} WHERE fid = 'file'"); + break; + } } function uc_file_update_1() { diff -Naur ubercart/uc_file/uc_file.module ubercart_pgsqlfix3/uc_file/uc_file.module --- ubercart/uc_file/uc_file.module Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/uc_file/uc_file.module Tue Feb 5 12:39:21 2008 @@ -107,8 +107,8 @@ break; case 'form': if (user_access('administer users') && (is_null($category) || $category == 'account')) { - $file_downloads = db_query("SELECT * FROM {uc_file_users} as u INNER JOIN {uc_files} as f ON u.fid = f.fid WHERE `uid` = %d",$account->uid); - $files = db_query("SELECT * FROM {uc_files} ORDER BY `filename` ASC"); + $file_downloads = db_query("SELECT * FROM {uc_file_users} as u INNER JOIN {uc_files} as f ON u.fid = f.fid WHERE uid = %d",$account->uid); + $files = db_query("SELECT * FROM {uc_files} ORDER BY filename ASC"); $available_downloads = array(); $available_files = array(); while ($file_download = db_fetch_object($file_downloads)) { @@ -153,7 +153,7 @@ } if (!empty($edit['add_file'])) { foreach ($edit['add_file'] as $fid) { - $pfid = db_result(db_query("SELECT `pfid` FROM {uc_file_products} WHERE `fid` = %d", $fid)); + $pfid = db_result(db_query("SELECT pfid FROM {uc_file_products} WHERE fid = %d", $fid)); _user_table_action('allow',$fid,$account->uid,$pfid); } } @@ -306,7 +306,7 @@ function theme_uc_file_downloads_token($file_downloads) { $output = ''; foreach ($file_downloads as $file_download) { - $filename = basename(db_result(db_query("SELECT filename FROM {uc_files} WHERE `fid` = %d",$file_download->fid))); + $filename = basename(db_result(db_query("SELECT filename FROM {uc_files} WHERE fid = %d",$file_download->fid))); $download_url = url('download/'.$file_download->fid.'/'.$file_download->key,NULL,NULL,TRUE); $output .= ''.$download_url.''."\n"; } @@ -404,7 +404,16 @@ $pfid = $form_values['pfid']; db_query("DELETE FROM {uc_file_products} WHERE pfid = %d",$pfid); } - db_query("INSERT INTO {uc_file_products} (pfid, fid, model, description, shippable) VALUES (%d, %d, '%s', '%s', %d)",$pfid,$fid,$form_values['uc_file_model'],$form_values['uc_file_description'],$shippable); + + switch ($GLOBALS['db_type']){ + case 'mysqli': + case 'mysql': + db_query("INSERT INTO {uc_file_products} (pfid, fid, model, description, shippable) VALUES (%d, %d, '%s', '%s', %d)",$pfid,$fid,$form_values['uc_file_model'],$form_values['uc_file_description'],$shippable); + break; + case 'pgsql': + db_query("INSERT INTO {uc_file_products} (pfid, fid, model, description, shippable) VALUES (%d, %d, '%s', '%s', '%d')",$pfid,$fid,$form_values['uc_file_model'],$form_values['uc_file_description'],$shippable); + break; + } $data = array( 'pfid' => $pfid, @@ -615,7 +624,7 @@ switch ($form_values['action']) { case 'uc_file_delete': //Delete selected files foreach ($file_ids as $file_id) { - $filename = db_result(db_query("SELECT filename FROM {uc_files} WHERE `fid` = %d",$file_id)); + $filename = db_result(db_query("SELECT filename FROM {uc_files} WHERE fid = %d",$file_id)); $filename = (substr($filename,-1) == "/") ? $filename.' ('.t('directory').')' : $filename; $file_list[] = $filename; } @@ -844,7 +853,7 @@ array('data' => t('Description'), 'field' => 'p.description'), array('data' => t('Downloads'), 'field' => 'u.accessed'), ); - $sql = "SELECT `granted`, `filename`, `accessed`, `description`, `key`, f.fid FROM {uc_file_users} as u LEFT JOIN {uc_files} as f ON u.fid = f.fid LEFT JOIN {uc_file_products} as p ON p.pfid = u.pfid WHERE uid = %d"; + $sql = "SELECT granted, filename, accessed, description, key, f.fid FROM {uc_file_users} as u LEFT JOIN {uc_files} as f ON u.fid = f.fid LEFT JOIN {uc_file_products} as p ON p.pfid = u.pfid WHERE uid = %d"; $count_query = "SELECT COUNT(*) FROM {uc_file_users} WHERE uid = $uid"; $files = pager_query($sql.tablesort_sql($header), UC_FILE_PAGER_SIZE, 0, $count_query, $uid); @@ -931,7 +940,7 @@ $ip = $_SERVER['REMOTE_ADDR']; $message_admin = t('Please contact the site administrator if this message has been received in error.'); $message_user = ($user->uid) ? t("The user %username ", array('%username' => $user->name)) : t('The IP address %ip ', array('%ip' => $ip)); - $file_download = db_fetch_object(db_query("SELECT * FROM {uc_file_users} WHERE `fid` = %d AND `key` = '%s'",$fid,$key)); + $file_download = db_fetch_object(db_query("SELECT * FROM {uc_file_users} WHERE fid = %d AND key = '%s'",$fid,$key)); $request_cache = cache_get('uc_file_'.$ip); $requests = ($request_cache) ? $request_cache->data+1 : 1; @@ -977,7 +986,7 @@ _file_download_deny(); } } - $filename = db_result(db_query("SELECT filename FROM {uc_files} WHERE `fid` = %d",$fid)); + $filename = db_result(db_query("SELECT filename FROM {uc_files} WHERE fid = %d",$fid)); watchdog('uc_file',$message_user.t('has started download of the file %filename. ', array('%filename' => basename($filename))),WATCHDOG_NOTICE); _file_download_transfer($file_download,$ip,$file_download->fid); } @@ -1010,7 +1019,7 @@ * The file id of the file to transfer */ function _file_download_transfer($file_user, $ip, $fid) { - $file = db_result(db_query("SELECT filename FROM {uc_files} WHERE `fid` = %d",$fid)); + $file = db_result(db_query("SELECT filename FROM {uc_files} WHERE fid = %d",$fid)); $file_path = variable_get('uc_file_base_dir', NULL)."/".$file; if (!is_file($file_path)) { @@ -1096,11 +1105,11 @@ $file_dir = dirname($filename); if (!db_result(db_query("SELECT fid FROM {uc_files} WHERE filename = '%s'",$file_dir."/")) && $file_dir != ".") { $fid = db_next_id('{uc_files}_fid'); - db_query("INSERT INTO {uc_files} (`fid`, `filename`) VALUES (%d, '%s')",$fid,$file_dir."/"); + db_query("INSERT INTO {uc_files} (fid, filename) VALUES (%d, '%s')",$fid,$file_dir."/"); } if (!db_result(db_query("SELECT fid FROM {uc_files} WHERE filename = '%s'",$filename))) { $fid = db_next_id('{uc_files}_fid'); - db_query("INSERT INTO {uc_files} (`fid`, `filename`) VALUES (%d, '%s')",$fid,$filename); + db_query("INSERT INTO {uc_files} (fid, filename) VALUES (%d, '%s')",$fid,$filename); } if (!is_null($fid)) { $file_object = db_fetch_object(db_query("SELECT * FROM {uc_files} WHERE fid = %d",$fid)); @@ -1115,7 +1124,7 @@ } break; case 'remove': //Remove a specific file id (arg1 = file id to delete, arg2 = TRUE = recursively delete directories, arg 3 = TRUE = delete associated rows/files) - if (!is_null($arg1) && $filename = db_result(db_query("SELECT filename FROM {uc_files} WHERE `fid` = %d",$arg1))) { + if (!is_null($arg1) && $filename = db_result(db_query("SELECT filename FROM {uc_files} WHERE fid = %d",$arg1))) { $dir = variable_get('uc_file_base_dir', NULL); $sub_fids = ($arg2) ? _get_dir_file_ids($arg1,TRUE) : _get_dir_file_ids($arg1); $selected_fid = (is_dir($dir."/".$filename) && !$arg2) ? array() : array($arg1); @@ -1124,11 +1133,11 @@ foreach ($fids as $fid) { if ($arg3) { - $filename = db_result(db_query("SELECT filename FROM {uc_files} WHERE `fid` = %d",$fid)); - $pfids = db_query("SELECT `pfid` FROM {uc_file_products} WHERE `fid` = %d",$fid); + $filename = db_result(db_query("SELECT filename FROM {uc_files} WHERE fid = %d",$fid)); + $pfids = db_query("SELECT pfid FROM {uc_file_products} WHERE fid = %d",$fid); while ($pfid = db_fetch_object($pfids)) { - db_query("DELETE FROM {uc_product_features} WHERE `pfid` = %d AND `fid` = 'file'",$pfid->pfid); - db_query("DELETE FROM {uc_file_products} WHERE `pfid` = %d",$pfid->pfid); + db_query("DELETE FROM {uc_product_features} WHERE pfid = %d AND fid = 'file'",$pfid->pfid); + db_query("DELETE FROM {uc_file_products} WHERE pfid = %d",$pfid->pfid); } if (is_dir($dir."/".$filename)) { rmdir($dir."/".$filename); @@ -1138,7 +1147,7 @@ } _user_table_action('remove',$fid); } - db_query("DELETE FROM {uc_files} WHERE `fid` = %d",$fid); + db_query("DELETE FROM {uc_files} WHERE fid = %d",$fid); } } break; @@ -1151,7 +1160,7 @@ if (is_file(variable_get('uc_file_base_dir', NULL)."/".$file->filename)) { continue; } - db_query("DELETE FROM {uc_files} WHERE `fid` = %d",$file->fid); + db_query("DELETE FROM {uc_files} WHERE fid = %d",$file->fid); } break; default: @@ -1266,7 +1275,7 @@ $output = array(); foreach ($fids as $fid) { - $filename = db_result(db_query("SELECT filename FROM {uc_files} WHERE `fid` = %d",$fid)); + $filename = db_result(db_query("SELECT filename FROM {uc_files} WHERE fid = %d",$fid)); if (substr($filename,-1) == "/") { $dir_fids[$fid] = $filename; } @@ -1319,8 +1328,8 @@ foreach ($fids as $fid) { $values = array($arg1,$arg2,$arg3,"",$granted,0,serialize(array())); $hash = _generate_hash($values); - db_query("INSERT INTO {uc_file_users} (`fid`, `uid`, `pfid`, `key`, `granted`, `accessed`, `addresses`) VALUES (%d, %d, %d, '%s', %d, %d, '%s')",$fid,$arg2,$arg3,$hash,$granted,0,serialize(array())); - $output[] = db_fetch_object(db_query("SELECT * FROM {uc_file_users} WHERE `uid` = %d AND `key` = '%s'",$arg2,$hash)); + db_query("INSERT INTO {uc_file_users} (fid, uid, pfid, key, granted, accessed, addresses) VALUES (%d, %d, %d, '%s', %d, %d, '%s')",$fid,$arg2,$arg3,$hash,$granted,0,serialize(array())); + $output[] = db_fetch_object(db_query("SELECT * FROM {uc_file_users} WHERE uid = %d AND key = '%s'",$arg2,$hash)); } } return (!is_null($output)) ? $output : FALSE; @@ -1334,22 +1343,22 @@ $accessed = $arg1->accessed+1; $values = array($arg1->fid,$arg1->uid,$arg1->pfid,$arg1->key,$arg1->granted,$accessed,serialize($addresses)); $hash = _generate_hash($values); - db_query("UPDATE {uc_file_users} SET `accessed` = %d, `addresses` = '%s', `key` = '%s' WHERE `fid` = %d AND `uid` = %d AND `key` = '%s'",$accessed,serialize($addresses),$hash,$arg1->fid,$arg1->uid,$arg1->key); + db_query("UPDATE {uc_file_users} SET accessed = %d, addresses = '%s', key = '%s' WHERE fid = %d AND uid = %d AND key = '%s'",$accessed,serialize($addresses),$hash,$arg1->fid,$arg1->uid,$arg1->key); } break; case 'remove': //arg1 = file id, arg2 = user id, $arg3 = key if (!is_null($arg1) || !is_null($arg2)) { if (!is_null($arg1) && is_null($arg2) && is_null($arg3)) { //Remove a file from download - db_query("DELETE FROM {uc_file_users} WHERE `fid` = %d",$arg1); + db_query("DELETE FROM {uc_file_users} WHERE fid = %d",$arg1); } if (is_null($arg1) && !is_null($arg2) && is_null($arg3)) { //Remove a user's downloads - db_query("DELETE FROM {uc_file_users} WHERE `uid` = %d",$arg2); + db_query("DELETE FROM {uc_file_users} WHERE uid = %d",$arg2); } if (!is_null($arg1) && !is_null($arg2) && is_null($arg3)) { //Remove a certain files from a user - db_query("DELETE FROM {uc_file_users} WHERE `fid` = %d AND `uid` = %d",$arg1,$arg2); + db_query("DELETE FROM {uc_file_users} WHERE fid = %d AND uid = %d",$arg1,$arg2); } if (is_null($arg1) && !is_null($arg2) && !is_null($arg3)) { //Remove a certain file from a user - db_query("DELETE FROM {uc_file_users} WHERE `uid` = %d AND `key` = '%s'",$arg2,$arg3); + db_query("DELETE FROM {uc_file_users} WHERE uid = %d AND key = '%s'",$arg2,$arg3); } } break; diff -Naur ubercart/uc_importer/uc_importer.module ubercart_pgsqlfix3/uc_importer/uc_importer.module --- ubercart/uc_importer/uc_importer.module Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/uc_importer/uc_importer.module Tue Feb 5 12:39:21 2008 @@ -844,8 +844,18 @@ if (isset($category_data->parent)){ $parent = (string)$category_data->parent[0]->data(); } - db_query("INSERT IGNORE INTO {term_hierarchy} (tid, parent) VALUES (%d, %d)", $id_map['categories'][(string)$category_data->id[0]->data()], $id_map['categories'][$parent]); + + switch ($GLOBALS['db_type']){ + case 'mysqli': + case 'mysql': + db_query("INSERT IGNORE INTO {term_hierarchy} (tid, parent) VALUES (%d, %d)", $id_map['categories'][(string)$category_data->id[0]->data()], $id_map['categories'][$parent]); + break; + case 'pgsql': + db_query("INSERT INTO {term_hierarchy} (tid, parent) VALUES (%d, %d)", $id_map['categories'][(string)$category_data->id[0]->data()], $id_map['categories'][$parent]); + break; + } } + module_invoke_all('xml_importer', 'category', taxonomy_get_term($id_map['categories'][(string)$category_data->id[0]->data()]), $category_data, $store, $id_map); } } diff -Naur ubercart/uc_order/uc_order.install ubercart_pgsqlfix3/uc_order/uc_order.install --- ubercart/uc_order/uc_order.install Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/uc_order/uc_order.install Tue Feb 5 12:39:21 2008 @@ -115,9 +115,9 @@ break; case 'pgsql': db_query("CREATE TABLE {uc_orders} ( - order_id integer NOT NULL default 0, + order_id serial NOT NULL, uid integer NOT NULL default 0, - order_status smallint NOT NULL default 0, + order_status varchar(32) NOT NULL default 0, order_total decimal(10,2) NOT NULL default 0.00, primary_email varchar(96) NOT NULL default '', delivery_first_name varchar(32) NOT NULL default '', @@ -127,7 +127,7 @@ delivery_street1 varchar(64) NOT NULL default '', delivery_street2 varchar(64) NOT NULL default '', delivery_city varchar(32) NOT NULL default '', - delivery_zone integer NOT NULL defualt 0, + delivery_zone integer NOT NULL default 0, delivery_postal_code varchar(10) NOT NULL default '', delivery_country integer NOT NULL default 0, billing_first_name varchar(32) NOT NULL default '', @@ -141,6 +141,7 @@ billing_postal_code varchar(10) NOT NULL default '', billing_country integer NOT NULL default 0, payment_method varchar(32) NOT NULL default 0, + data text NOT NULL default '', created integer NOT NULL default 0, modified integer NOT NULL default 0, PRIMARY KEY (order_id) @@ -148,7 +149,7 @@ db_query("CREATE INDEX {uc_orders}_uid ON {uc_orders} (uid)"); db_query("CREATE INDEX {uc_orders}_order_status ON {uc_orders} (order_status)"); db_query("CREATE TABLE {uc_order_admin_comments} ( - comment_id integer NOT NULL default 0, + comment_id serial NOT NULL, order_id integer NOT NULL default 0, uid integer NOT NULL default 0, message text NOT NULL default '', @@ -157,10 +158,10 @@ );"); db_query("CREATE INDEX {uc_order_admin_comments}_order_id ON {uc_order_admin_comments} (order_id)"); db_query("CREATE TABLE {uc_order_comments} ( - comment_id integer NOT NULL default 0, + comment_id serial NOT NULL, order_id integer NOT NULL default 0, uid integer NOT NULL default 0, - order_status smallint NOT NULL default 0, + order_status varchar(32) NOT NULL default 0, notified smallint NOT NULL default 0, message text NOT NULL default '', created integer NOT NULL default 0, @@ -168,7 +169,7 @@ );"); db_query("CREATE INDEX {uc_order_comments}_order_id ON {uc_order_comments} (order_id)"); db_query("CREATE TABLE {uc_order_line_items} ( - line_item_id integer NOT NULL default 0, + line_item_id serial NOT NULL, order_id integer NOT NULL default 0, type varchar(32) NOT NULL default '', title varchar(128) NOT NULL default '', @@ -178,7 +179,7 @@ );"); db_query("CREATE INDEX {uc_order_line_items}_order_id ON {uc_order_line_items} (order_id)"); db_query("CREATE TABLE {uc_order_log} ( - order_log_id integer NOT NULL default 0, + order_log_id serial NOT NULL, order_id integer NOT NULL default 0, uid integer NOT NULL default 0, changes text NOT NULL default '', @@ -187,7 +188,7 @@ );"); db_query("CREATE INDEX {uc_order_log}_order_id ON {uc_order_log} (order_id)"); db_query("CREATE TABLE {uc_order_products} ( - order_product_id integer NOT NULL default 0, + order_product_id serial NOT NULL, order_id integer NOT NULL default 0, nid integer NOT NULL default 0, title varchar(128) NOT NULL default '', diff -Naur ubercart/uc_order/uc_order.module ubercart_pgsqlfix3/uc_order/uc_order.module --- ubercart/uc_order/uc_order.module Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/uc_order/uc_order.module Tue Feb 5 12:39:21 2008 @@ -1541,7 +1541,7 @@ array('data' => t('Total'), 'field' => 'o.order_total') ); - $result = pager_query("SELECT o.order_id, o.created, os.title, SUM(op.qty) AS products, o.order_total AS total FROM {uc_orders} AS o LEFT JOIN {uc_order_statuses} AS os ON o.order_status = os.order_status_id LEFT JOIN {uc_order_products} AS op ON o.order_id = op.order_id WHERE o.uid = %d AND o.order_status IN ". uc_order_status_list('general', TRUE) ." GROUP BY o.order_id". tablesort_sql($header), 20, 0, "SELECT COUNT(*) FROM {uc_orders} WHERE uid = %d AND order_status NOT IN ". uc_order_status_list('specific', TRUE), $uid); + $result = pager_query("SELECT o.order_id, o.created, os.title, SUM(op.qty) AS products, o.order_total AS total FROM {uc_orders} AS o LEFT JOIN {uc_order_statuses} AS os ON o.order_status = os.order_status_id LEFT JOIN {uc_order_products} AS op ON o.order_id = op.order_id WHERE o.uid = %d AND o.order_status IN ". uc_order_status_list('general', TRUE) ." GROUP BY o.order_id, o.created, os.title, o.order_total". tablesort_sql($header), 20, 0, "SELECT COUNT(*) FROM {uc_orders} WHERE uid = %d AND order_status NOT IN ". uc_order_status_list('specific', TRUE), $uid); // Build a table based on the customer's orders. while ($order = db_fetch_object($result)){ diff -Naur ubercart/uc_product/uc_product.install ubercart_pgsqlfix3/uc_product/uc_product.install --- ubercart/uc_product/uc_product.install Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/uc_product/uc_product.install Tue Feb 5 12:39:21 2008 @@ -56,13 +56,13 @@ PRIMARY KEY (pcid) );"); db_query("CREATE TABLE {uc_product_features} ( - pfid integer NOT NULL default 0, + pfid serial NOT NULL, nid integer NOT NULL default 0, fid varchar(32) NOT NULL, description text, - PRIMARY KEY (pfid) + PRIMARY KEY (pfid) );"); - db_query("CREATE INDEX {uc_product_features}_nid ON {uc_product_features} (nid)"); + db_query("CREATE INDEX {uc_product_features}_nid ON {uc_product_features} (nid)"); db_query("CREATE TABLE {uc_products} ( vid integer NOT NULL default 0, nid integer NOT NULL default 0, @@ -80,7 +80,7 @@ default_qty smallint NOT NULL default 1, unique_hash varchar(32) NOT NULL default '', ordering smallint NOT NULL default 0, - shippable smallint NOT NULL default 1, + shippable smallint NOT NULL default 1, PRIMARY KEY (vid) );"); break; diff -Naur ubercart/uc_product_kit/uc_product_kit.install ubercart_pgsqlfix3/uc_product_kit/uc_product_kit.install --- ubercart/uc_product_kit/uc_product_kit.install Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/uc_product_kit/uc_product_kit.install Tue Feb 5 12:39:21 2008 @@ -19,14 +19,14 @@ break; case 'pgsql': db_query("CREATE TABLE {uc_product_kits} ( - vid integer NOT NULLl default 0, + vid integer NOT NULL default 0, nid integer NOT NULL default 0, product_id integer NOT NULL default 0, mutable smallint NOT NULL default 0, qty smallint NOT NULL default 0, discount float NOT NULL default 0.0, data text NOT NULL default '', - orderint smallint NOT NULL default 0, + ordering smallint NOT NULL default 0, PRIMARY KEY (vid, product_id) );"); break; diff -Naur ubercart/uc_reports/uc_reports.install ubercart_pgsqlfix3/uc_reports/uc_reports.install --- ubercart/uc_reports/uc_reports.install Wed Dec 31 18:00:00 1969 +++ ubercart_pgsqlfix3/uc_reports/uc_reports.install Tue Feb 5 12:39:21 2008 @@ -0,0 +1,29 @@ + t('Average'), 'field' => 'average'), ); $csv_rows[] = array(t('#'),t('Customer'),t('Username'),t('Orders'),t('Products'),t('Total'),t('Average')); - - $sql = "SELECT u.uid, u.name, ou.$first_name, ou.$last_name, (SELECT COUNT(DISTINCT(order_id)) FROM {uc_orders} as o WHERE o.uid = u.uid AND o.order_status IN $order_statuses) as orders, (SELECT SUM(qty) FROM {uc_order_products} as ps LEFT JOIN {uc_orders} as os ON ps.order_id = os.order_id WHERE os.order_status IN $order_statuses AND os.uid = u.uid) as products, (SELECT SUM(ot.order_total) FROM {uc_orders} as ot WHERE ot.uid = u.uid AND ot.order_status IN $order_statuses) as total, ROUND((SELECT SUM(ot.order_total) FROM {uc_orders} as ot WHERE ot.uid = u.uid AND ot.order_status IN $order_statuses)/(SELECT COUNT(DISTINCT(order_id)) FROM {uc_orders} as o WHERE o.uid = u.uid AND o.order_status IN $order_statuses),2) as average FROM {users} as u LEFT JOIN {uc_orders} as ou ON u.uid = ou.uid WHERE u.uid > 0 GROUP BY u.uid"; - $sql_count = "SELECT COUNT(DISTINCT(u.uid)) FROM {users} as u LEFT JOIN {uc_orders} as ou ON u.uid = ou.uid WHERE u.uid > 0"; + + + $sql = ""; + $sql_count = ""; + + switch ($GLOBALS['db_type']){ + case 'mysqli': + case 'mysql': + $sql = "SELECT u.uid, u.name, ou.$first_name, ou.$last_name, (SELECT COUNT(DISTINCT(order_id)) FROM {uc_orders} as o WHERE o.uid = u.uid AND o.order_status IN $order_statuses) as orders, (SELECT SUM(qty) FROM {uc_order_products} as ps LEFT JOIN {uc_orders} as os ON ps.order_id = os.order_id WHERE os.order_status IN $order_statuses AND os.uid = u.uid) as products, (SELECT SUM(ot.order_total) FROM {uc_orders} as ot WHERE ot.uid = u.uid AND ot.order_status IN $order_statuses) as total, ROUND((SELECT SUM(ot.order_total) FROM {uc_orders} as ot WHERE ot.uid = u.uid AND ot.order_status IN $order_statuses)/(SELECT COUNT(DISTINCT(order_id)) FROM {uc_orders} as o WHERE o.uid = u.uid AND o.order_status IN $order_statuses),2) as average FROM {users} as u LEFT JOIN {uc_orders} as ou ON u.uid = ou.uid WHERE u.uid > 0 GROUP BY u.uid"; + $sql_count = "SELECT COUNT(DISTINCT(u.uid)) FROM {users} as u LEFT JOIN {uc_orders} as ou ON u.uid = ou.uid WHERE u.uid > 0"; + break; + case 'pgsql': + $sql = "SELECT u.uid, u.name, ou.$first_name, ou.$last_name, (SELECT COUNT(DISTINCT(order_id)) FROM {uc_orders} as o WHERE o.uid = u.uid AND o.order_status IN $order_statuses) as orders, (SELECT SUM(qty) FROM {uc_order_products} as ps LEFT JOIN {uc_orders} as os ON ps.order_id = os.order_id WHERE os.order_status IN $order_statuses AND os.uid = u.uid) as products, (SELECT SUM(ot.order_total) FROM {uc_orders} as ot WHERE ot.uid = u.uid AND ot.order_status IN $order_statuses) as total, ROUND((SELECT SUM(ot.order_total) FROM {uc_orders} as ot WHERE ot.uid = u.uid AND ot.order_status IN $order_statuses)/(SELECT COUNT(DISTINCT(order_id)) FROM {uc_orders} as o WHERE o.uid = u.uid AND o.order_status IN $order_statuses),2) as average FROM {users} as u LEFT JOIN {uc_orders} as ou ON u.uid = ou.uid WHERE u.uid > 0 GROUP BY u.uid, u.name, ou.$first_name, ou.$last_name"; + $sql_count = "SELECT COUNT(DISTINCT(u.uid)) FROM {users} as u LEFT JOIN {uc_orders} as ou ON u.uid = ou.uid WHERE u.uid > 0"; + break; + } + $customers = pager_query($sql.tablesort_sql($header), $page_size, 0, $sql_count); while ($customer = db_fetch_array($customers)) { @@ -226,7 +240,17 @@ array('data' => t('Revenue'), 'field' => 'revenue', 'sort' => 'desc'), ); $csv_rows[] = array(t('#'),t('Product'),t('Views'),t('Sold'),t('Revenue')); - $sql = "SELECT n.nid, n.title, c.totalcount, (SELECT SUM(qty) FROM {uc_order_products} as p LEFT JOIN {uc_orders} as o ON p.order_id = o.order_id WHERE o.order_status IN $order_statuses AND p.nid = n.nid) as sold, (SELECT (SUM(p2.price*p2.qty)) FROM {uc_order_products} as p2 LEFT JOIN {uc_orders} as o2 ON p2.order_id = o2.order_id WHERE o2.order_status IN $order_statuses AND p2.nid = n.nid) as revenue FROM {node} as n LEFT JOIN {node_counter} as c ON n.nid = c.nid WHERE type IN (".implode(",",$product_types).") GROUP BY n.nid DESC"; + + $sql = ""; + switch ($GLOBALS['db_type']){ + case 'mysqli': + case 'mysql': + $sql = "SELECT n.nid, n.title, c.totalcount, (SELECT SUM(qty) FROM {uc_order_products} as p LEFT JOIN {uc_orders} as o ON p.order_id = o.order_id WHERE o.order_status IN $order_statuses AND p.nid = n.nid) as sold, (SELECT (SUM(p2.price*p2.qty)) FROM {uc_order_products} as p2 LEFT JOIN {uc_orders} as o2 ON p2.order_id = o2.order_id WHERE o2.order_status IN $order_statuses AND p2.nid = n.nid) as revenue FROM {node} as n LEFT JOIN {node_counter} as c ON n.nid = c.nid WHERE type IN (".implode(",",$product_types).") GROUP BY n.nid DESC"; + break; + case 'pgsql': + $sql = "SELECT n.nid, n.title, c.totalcount, (SELECT SUM(qty) FROM {uc_order_products} as p LEFT JOIN {uc_orders} as o ON p.order_id = o.order_id WHERE o.order_status IN $order_statuses AND p.nid = n.nid) as sold, (SELECT (SUM(p2.price*p2.qty)) FROM {uc_order_products} as p2 LEFT JOIN {uc_orders} as o2 ON p2.order_id = o2.order_id WHERE o2.order_status IN $order_statuses AND p2.nid = n.nid) as revenue FROM {node} as n LEFT JOIN {node_counter} as c ON n.nid = c.nid WHERE type IN (".implode(",",$product_types).") GROUP BY n.nid"; + break; + } } else { $header = array( @@ -236,7 +260,17 @@ array('data' => t('Revenue'), 'field' => 'revenue', 'sort' => 'desc'), ); $csv_rows[] = array(t('#'),t('Product'),t('Sold'),t('Revenue')); - $sql = "SELECT n.nid, n.title, (SELECT SUM(qty) FROM {uc_order_products} as p LEFT JOIN {uc_orders} as o ON p.order_id = o.order_id WHERE o.order_status IN $order_statuses AND p.nid = n.nid) as sold, (SELECT (SUM(p2.price*p2.qty)) FROM {uc_order_products} as p2 LEFT JOIN {uc_orders} as o2 ON p2.order_id = o2.order_id WHERE o2.order_status IN $order_statuses AND p2.nid = n.nid) as revenue FROM {node} as n WHERE type IN (".implode(",",$product_types).") GROUP BY n.nid DESC"; + + + switch ($GLOBALS['db_type']){ + case 'mysqli': + case 'mysql': + $sql = "SELECT n.nid, n.title, (SELECT SUM(qty) FROM {uc_order_products} as p LEFT JOIN {uc_orders} as o ON p.order_id = o.order_id WHERE o.order_status IN $order_statuses AND p.nid = n.nid) as sold, (SELECT (SUM(p2.price*p2.qty)) FROM {uc_order_products} as p2 LEFT JOIN {uc_orders} as o2 ON p2.order_id = o2.order_id WHERE o2.order_status IN $order_statuses AND p2.nid = n.nid) as revenue FROM {node} as n WHERE type IN (".implode(",",$product_types).") GROUP BY n.nid DESC"; + break; + case 'pgsql': + $sql = "SELECT n.nid, n.title, (SELECT SUM(qty) FROM {uc_order_products} as p LEFT JOIN {uc_orders} as o ON p.order_id = o.order_id WHERE o.order_status IN $order_statuses AND p.nid = n.nid) as sold, (SELECT (SUM(p2.price*p2.qty)) FROM {uc_order_products} as p2 LEFT JOIN {uc_orders} as o2 ON p2.order_id = o2.order_id WHERE o2.order_status IN $order_statuses AND p2.nid = n.nid) as revenue FROM {node} as n WHERE type IN (".implode(",",$product_types).") GROUP BY n.nid, n.title"; + break; + } } $sql_count = "SELECT COUNT(nid) FROM {node} WHERE type IN (".implode(",",$product_types).")"; @@ -375,7 +409,7 @@ array('data' => t('Order statuses'), 'colspan' => 2), ); $rows = array(); - $order_statuses = db_query("SELECT s.title, COUNT(o.order_status) FROM {uc_orders} as o LEFT JOIN {uc_order_statuses} as s ON s.order_status_id = o.order_status GROUP BY order_status ORDER BY weight ASC"); + $order_statuses = db_query("SELECT s.title, s.weight, COUNT(o.order_status) FROM {uc_orders} as o LEFT JOIN {uc_order_statuses} as s ON s.order_status_id = o.order_status GROUP BY order_status, s.title, s.weight ORDER BY weight ASC"); while ($order_status = db_fetch_array($order_statuses)) { if (!empty($order_status['title'])) { @@ -799,26 +833,26 @@ $csv_data = cache_get('uc_reports_'.$report_id.'_'.$user_id,'cache'); if (!$csv_data || $user_id != $user_check) { - drupal_set_message(t("The CSV data could not be retreived. It's possible the data might have expired. Refresh the report page and try to retrieve the CSV file again."),'error'); - drupal_not_found(); - exit(); + drupal_set_message(t("The CSV data could not be retreived. It's possible the data might have expired. Refresh the report page and try to retrieve the CSV file again."),'error'); + drupal_not_found(); + exit(); } else { ob_end_clean(); - $http_headers = array ( - 'Pragma: no-cache', - 'Expires: 0', - 'Cache-Control: no-cache, must-revalidate', - 'Cache-Control: private', - 'Content-Transfer-Encoding: binary', - 'Content-Length:' . strlen($csv_data->data), - 'Content-Disposition: attachment; filename="'.$report_id.'.csv"', - 'Content-Type: text/csv' - ); - foreach ($http_headers as $header) { - $header = preg_replace('/\r?\n(?!\t| )/', '', $header); - drupal_set_header($header); - } + $http_headers = array ( + 'Pragma: no-cache', + 'Expires: 0', + 'Cache-Control: no-cache, must-revalidate', + 'Cache-Control: private', + 'Content-Transfer-Encoding: binary', + 'Content-Length:' . strlen($csv_data->data), + 'Content-Disposition: attachment; filename="'.$report_id.'.csv"', + 'Content-Type: text/csv' + ); + foreach ($http_headers as $header) { + $header = preg_replace('/\r?\n(?!\t| )/', '', $header); + drupal_set_header($header); + } print $csv_data->data; exit(); @@ -834,31 +868,53 @@ * The amount of time over which to count sales (e.g. [1] day, month, year) * @return: * An associative array containing information about sales: -* 'date' => A string representing the day counting was started -* 'income' => The total revenue that occurred during the time period -* 'total' => The total number of orders completed during the time period -* 'average' => The average revenue produced for each order +* 'date' => A string representing the day counting was started +* 'income' => The total revenue that occurred during the time period +* 'total' => The total number of orders completed during the time period +* 'average' => The average revenue produced for each order */ function _uc_reports_get_sales($time, $period = 'day') { $output = array(); $output['income'] = 0; $order_statuses = _uc_reports_order_statuses(); - - switch ($period) { - default: - case 'day': - $output['date'] = format_date($time,'custom',"n").'-'.format_date($time,'custom',"j"); - $orders = db_query("SELECT o.order_total FROM {uc_orders} as o WHERE o.order_status IN $order_statuses AND FROM_UNIXTIME(created) LIKE \"".format_date($time,'custom',"Y")."-".format_date($time,'custom',"m")."-".format_date($time,'custom',"d")."%%\""); - break; - case 'month': - $output['date'] = format_date($time,'custom',"n"); - $orders = db_query("SELECT o.order_total FROM {uc_orders} as o WHERE o.order_status IN $order_statuses AND FROM_UNIXTIME(created) LIKE \"".format_date($time,'custom',"Y")."-".format_date($time,'custom',"m")."%%\""); + + switch ($GLOBALS['db_type']){ + case 'mysqli': + case 'mysql': + switch ($period) { + default: + case 'day': + $output['date'] = format_date($time,'custom',"n").'-'.format_date($time,'custom',"j"); + $orders = db_query("SELECT o.order_total FROM {uc_orders} as o WHERE o.order_status IN $order_statuses AND FROM_UNIXTIME(created) LIKE \"".format_date($time,'custom',"Y")."-".format_date($time,'custom',"m")."-".format_date($time,'custom',"d")."%%\""); + break; + case 'month': + $output['date'] = format_date($time,'custom',"n"); + $orders = db_query("SELECT o.order_total FROM {uc_orders} as o WHERE o.order_status IN $order_statuses AND FROM_UNIXTIME(created) LIKE \"".format_date($time,'custom',"Y")."-".format_date($time,'custom',"m")."%%\""); + break; + case 'year': + $output['date'] = format_date($time,'custom',"Y"); + $orders = db_query("SELECT o.order_total FROM {uc_orders} as o WHERE o.order_status IN $order_statuses AND FROM_UNIXTIME(created) LIKE \"".format_date($time,'custom',"Y")."-%%\""); + break; + } + case 'pgsql': + switch ($period) { + default: + case 'day': + $output['date'] = format_date($time,'custom',"n").'-'.format_date($time,'custom',"j"); + $orders = db_query("SELECT o.order_total FROM {uc_orders} as o WHERE o.order_status IN $order_statuses AND FROM_UNIXTIME(created) LIKE '".format_date($time,'custom',"Y")."-".format_date($time,'custom',"m")."-".format_date($time,'custom',"d")."%%'"); + break; + case 'month': + $output['date'] = format_date($time,'custom',"n"); + $orders = db_query("SELECT o.order_total FROM {uc_orders} as o WHERE o.order_status IN $order_statuses AND FROM_UNIXTIME(created) LIKE '".format_date($time,'custom',"Y")."-".format_date($time,'custom',"m")."%%'"); + break; + case 'year': + $output['date'] = format_date($time,'custom',"Y"); + $orders = db_query("SELECT o.order_total FROM {uc_orders} as o WHERE o.order_status IN $order_statuses AND FROM_UNIXTIME(created) LIKE '".format_date($time,'custom',"Y")."-%%'"); + break; + } break; - case 'year': - $output['date'] = format_date($time,'custom',"Y"); - $orders = db_query("SELECT o.order_total FROM {uc_orders} as o WHERE o.order_status IN $order_statuses AND FROM_UNIXTIME(created) LIKE \"".format_date($time,'custom',"Y")."-%%\""); } - + while ($order = db_fetch_object($orders)) { $output['income'] += $order->order_total; } diff -Naur ubercart/uc_roles/uc_roles.install ubercart_pgsqlfix3/uc_roles/uc_roles.install --- ubercart/uc_roles/uc_roles.install Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/uc_roles/uc_roles.install Tue Feb 5 12:39:21 2008 @@ -29,24 +29,24 @@ break; case 'pgsql': db_query("CREATE TABLE {uc_roles_products} ( - pfid integer NOT NULL, - nid integer NOT NULL, - model varchar(255) default NULL, - rid integer NOT NULL, - duration smallint default NULL, - granularity varchar(32) default NULL, - shippable boolean NOT NULL - );"); + pfid integer NOT NULL, + nid integer NOT NULL, + model varchar(255) default NULL, + rid integer NOT NULL, + duration smallint default NULL, + granularity varchar(32) default NULL, + shippable boolean NOT NULL + );"); db_query("CREATE INDEX {uc_roles_products}_pfid ON {uc_roles_products} (pfid)"); db_query("CREATE INDEX {uc_roles_products}_nid ON {uc_roles_products} (nid)"); db_query("CREATE INDEX {uc_roles_products}_model ON {uc_roles_products} (model)"); db_query("CREATE INDEX {uc_roles_products}_rid ON {uc_roles_products} (rid)"); db_query("CREATE TABLE {uc_roles_expirations} ( - uid integer NOT NULL, - rid integer NOT NULL, - expiration integer NOT NULL, - notified smallint default NULL - );"); + uid integer NOT NULL, + rid integer NOT NULL, + expiration integer NOT NULL, + notified smallint default NULL + );"); db_query("CREATE INDEX {uc_roles_expirations}_uid ON {uc_roles_expirations} (uid)"); db_query("CREATE INDEX {uc_roles_expirations}_rid ON {uc_roles_expirations} (rid)"); break; @@ -54,8 +54,17 @@ } function uc_roles_uninstall() { - db_query("DROP TABLE IF EXISTS {uc_roles_products}"); - db_query("DROP TABLE IF EXISTS {uc_roles_expirations}"); + switch($GLOBALS['db_type']){ + case 'mysqli': + case 'mysql': + db_query("DROP TABLE IF EXISTS {uc_roles_products}"); + db_query("DROP TABLE IF EXISTS {uc_roles_expirations}"); + break; + case 'pgsql': + db_query("DROP TABLE {uc_roles_products}"); + db_query("DROP TABLE {uc_roles_expirations}"); + break; + } } function uc_roles_update_1() { @@ -87,4 +96,4 @@ } return $ret; -} \ No newline at end of file +} diff -Naur ubercart/uc_roles/uc_roles.module ubercart_pgsqlfix3/uc_roles/uc_roles.module --- ubercart/uc_roles/uc_roles.module Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/uc_roles/uc_roles.module Tue Feb 5 12:39:21 2008 @@ -645,8 +645,17 @@ $pfid = $form_values['pfid']; db_query("DELETE FROM {uc_roles_products} WHERE pfid = %d",$pfid); } - db_query("INSERT INTO {uc_roles_products} (pfid, nid, model, rid, duration, granularity, shippable) VALUES (%d, %d, '%s', %d, %d, '%s', %d)",$pfid,$form_values['nid'],$model,$form_values['uc_roles_role'],$duration,$granularity,$shippable); + switch ($GLOBALS['db_type']){ + case 'mysqli': + case 'mysql': + db_query("INSERT INTO {uc_roles_products} (pfid, nid, model, rid, duration, granularity, shippable) VALUES (%d, %d, '%s', %d, %d, '%s', %d)",$pfid,$form_values['nid'],$model,$form_values['uc_roles_role'],$duration,$granularity,$shippable); + break; + case 'pgsql': + db_query("INSERT INTO {uc_roles_products} (pfid, nid, model, rid, duration, granularity, shippable) VALUES (%d, %d, '%s', %d, %d, '%s', '%d')",$pfid,$form_values['nid'],$model,$form_values['uc_roles_role'],$duration,$granularity,($shippable ? 't' : 'f')); + break; + } + $data = array( 'pfid' => $pfid, 'nid' => $form_values['nid'], diff -Naur ubercart/uc_stock/uc_stock.install ubercart_pgsqlfix3/uc_stock/uc_stock.install --- ubercart/uc_stock/uc_stock.install Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/uc_stock/uc_stock.install Tue Feb 5 12:39:21 2008 @@ -18,17 +18,25 @@ case 'pgsql': db_query("CREATE TABLE {uc_product_stock} ( sku varchar(255) NOT NULL default '', - nid integer NOT NULL default 0, - active smallint NOT NULL default 0, - stock integer NOT NULL default 0, - threshold integer NOT NULL default 0, + nid integer NOT NULL default 0, + active smallint NOT NULL default 0, + stock integer NOT NULL default 0, + threshold integer NOT NULL default 0, PRIMARY KEY (sku) );"); - db_query("CREATE INDEX {uc_product_stock}_nid ON {uc_product_stock} (nid)"); + db_query("CREATE INDEX {uc_product_stock}_nid ON {uc_product_stock} (nid)"); break; } } function uc_stock_uninstall() { - db_query("DROP TABLE IF EXISTS {uc_product_stock}"); -} \ No newline at end of file + switch($GLOBALS['db_type']){ + case 'mysqli': + case 'mysql': + db_query("DROP TABLE IF EXISTS {uc_product_stock}"); + break; + case 'pgsql': + db_query("DROP TABLE {uc_product_stock}"); + break; + } +} diff -Naur ubercart/uc_stock/uc_stock.module ubercart_pgsqlfix3/uc_stock/uc_stock.module --- ubercart/uc_stock/uc_stock.module Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/uc_stock/uc_stock.module Tue Feb 5 12:39:21 2008 @@ -195,21 +195,21 @@ case t('Save changes'): foreach ($form_values['models'] as $index => $model) { if ($form_values['active_'.$index]) { - if (db_result(db_query("SELECT nid FROM {uc_product_stock} WHERE `sku` = '%s'",$model))) { - db_query("UPDATE {uc_product_stock} SET `active` = %d, `stock` = %d, `threshold` = %d WHERE `sku` = '%s'",$form_values['active_'.$index],$form_values['stock_'.$index],$form_values['threshold_'.$index],$model); + if (db_result(db_query("SELECT nid FROM {uc_product_stock} WHERE sku = '%s'",$model))) { + db_query("UPDATE {uc_product_stock} SET active = %d, stock = %d, threshold = %d WHERE sku = '%s'",$form_values['active_'.$index],$form_values['stock_'.$index],$form_values['threshold_'.$index],$model); } else { - db_query("INSERT INTO {uc_product_stock} (`sku`, `nid`, `active`, `stock`, `threshold`) VALUES ('%s', %d, %d, %d, %d)",$model,$form_values['nid'],$form_values['active_'.$index],$form_values['stock_'.$index],$form_values['threshold_'.$index]); + db_query("INSERT INTO {uc_product_stock} (sku, nid, active, stock, threshold) VALUES ('%s', %d, %d, %d, %d)",$model,$form_values['nid'],$form_values['active_'.$index],$form_values['stock_'.$index],$form_values['threshold_'.$index]); } } else { - db_query("DELETE FROM {uc_product_stock} WHERE `sku` = '%s'",$model); + db_query("DELETE FROM {uc_product_stock} WHERE sku = '%s'",$model); } } drupal_set_message(t("Stock changes saved for %product_name",array('%product_name' => $node->title))); break; case t('Disable'): - db_query("DELETE FROM {uc_product_stock} WHERE `nid` = %d",$form_values['nid']); + db_query("DELETE FROM {uc_product_stock} WHERE nid = %d",$form_values['nid']); drupal_set_message(t("Stock disabled for %product_name",array('%product_name' => $node->title))); break; } @@ -232,8 +232,8 @@ array('data' => t('')), ); $csv_rows[] = array(t('SKU'),t('Product'),t('Stock'),t('Threshold')); - $sql = "SELECT s.nid,`sku`,`title`,`stock`,`threshold` FROM {uc_product_stock} as s LEFT JOIN {node} as n ON s.nid = n.nid WHERE active = 1"; - $sql .= ($show_threshold) ? " AND `threshold` >= `stock`" : ""; + $sql = "SELECT s.nid,sku,title,stock,threshold FROM {uc_product_stock} as s LEFT JOIN {node} as n ON s.nid = n.nid WHERE active = 1"; + $sql .= ($show_threshold) ? " AND threshold >= stock" : ""; $stock_levels = pager_query($sql.tablesort_sql($header), $page_size, 0, NULL); while ($stock = db_fetch_object($stock_levels)) { @@ -326,7 +326,7 @@ */ function uc_stock_adjust($sku, $qty) { $op = ($qty < 0) ? "-" : "+"; - db_query("UPDATE {uc_product_stock} SET `stock` = `stock` $op %d WHERE `sku` = '%s'", (int) abs($qty), $sku); + db_query("UPDATE {uc_product_stock} SET stock = stock $op %d WHERE sku = '%s'", (int) abs($qty), $sku); } /** @@ -337,7 +337,7 @@ * The associated stock level with the particular SKU or FALSE if not */ function uc_stock_level($sku) { - return db_result(db_query("SELECT `stock` FROM {uc_product_stock} WHERE `sku` = '%s'",$sku)); + return db_result(db_query("SELECT stock FROM {uc_product_stock} WHERE sku = '%s'",$sku)); } /** @@ -356,7 +356,7 @@ else { $skus = array($node->model); if (module_exists('uc_attribute')) { - $models = db_query("SELECT `model` FROM {uc_product_adjustments} WHERE `nid` = %d", $node->nid); + $models = db_query("SELECT model FROM {uc_product_adjustments} WHERE nid = %d", $node->nid); while ($model = db_fetch_object($models)) { if (!in_array($model,$skus)) { $skus[] = $model->model; @@ -378,7 +378,7 @@ * SKU doesn't have a stock level in the table */ function uc_stock_sku_fields($id, $sku) { - $stock = db_fetch_object(db_query("SELECT `active`, `stock`, `threshold` FROM {uc_product_stock} WHERE `sku` = '%s'",$sku)); + $stock = db_fetch_object(db_query("SELECT active, stock, threshold FROM {uc_product_stock} WHERE sku = '%s'",$sku)); if (is_null($stock)) { return FALSE; } diff -Naur ubercart/uc_store/uc_store.install ubercart_pgsqlfix3/uc_store/uc_store.install --- ubercart/uc_store/uc_store.install Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/uc_store/uc_store.install Tue Feb 5 12:39:21 2008 @@ -38,13 +38,13 @@ version integer NOT NULL default '0', PRIMARY KEY (country_id) );"); - db_query("CREATE INDEX {uc_countries}_country_name ON {uc_countries} (country_name)"); + db_query("CREATE INDEX {uc_countries}_country_name ON {uc_countries} (country_name)"); db_query("CREATE INDEX {uc_countries}_country_id ON {uc_countries} (country_id)"); db_query("CREATE TABLE {uc_zones} ( - zone_id integer NOT NULL default 0, + zone_id serial NOT NULL, zone_country_id integer NOT NULL default 0, zone_code varchar(32) NOT NULL default '', - zone_name varchar(25) NOT NULL default '', + zone_name varchar(255) NOT NULL default '', PRIMARY KEY (zone_id) );"); db_query("CREATE INDEX {uc_zones}_zone_code ON {uc_zones} (zone_code)"); @@ -71,7 +71,15 @@ db_query("DROP TABLE {uc_store_footers}"); db_query("DELETE FROM {variable} WHERE name LIKE 'user_initials_%%'"); db_query("DELETE FROM {variable} WHERE name LIKE 'uc_address_format_%%'"); - db_query("DELETE FROM {sequences} WHERE name LIKE '{uc_zones}_zone_id'"); + + switch ($GLOBALS['db_type']) + { + case 'mysqli': + case 'mysql': + db_query("DELETE FROM {sequences} WHERE name LIKE '{uc_zones}_zone_id'"); + break; + } + variable_del('uc_store_prev_time'); variable_del('uc_store_site_id'); variable_del('uc_store_opt_in'); diff -Naur ubercart/uc_store/uc_store.module ubercart_pgsqlfix3/uc_store/uc_store.module --- ubercart/uc_store/uc_store.module Thu Jan 31 12:26:22 2008 +++ ubercart_pgsqlfix3/uc_store/uc_store.module Tue Feb 5 12:39:21 2008 @@ -446,7 +446,26 @@ } $vars['currency'] = variable_get('uc_currency_code', 'USD'); - $result = db_query("SELECT COUNT(DISTINCT o.order_id) AS orders, SUM(o.order_total) AS sales FROM {uc_orders} AS o WHERE o.order_status = 'completed' GROUP BY o.order_status"); + + $count_query = ''; + switch ($GLOBALS['db_type']) { + case 'mysql': + case 'mysqli': + $count_query = "SELECT COUNT(DISTINCT o.order_id) AS orders, SUM(o.order_total) AS sales " + ."FROM {uc_orders} AS o " + ."WHERE o.order_status = 'completed' " + ."GROUP BY o.order_status"; + break; + case 'pgsql': + $count_query = "SELECT DISTINCT o.order_id, COUNT(o.order_id) as orders, SUM(o.order_total) AS sales " + ."FROM {uc_orders} AS o " + ."WHERE o.order_status = 'completed' " + ."GROUP BY o.order_status"; + break; + } + + $result = db_query($count_query); + if ($stats = db_fetch_array($result)){ if (variable_get('uc_store_opt_in_orders', TRUE)){ $vars['orders'] = $stats['orders']; @@ -690,11 +709,28 @@ ."{users} AS u ON o.uid = u.uid WHERE o.uid > 0 AND " ."o.order_status IN ". uc_order_status_list('general', TRUE) ." ORDER BY billing_last_name ASC"; - $count_query = "SELECT COUNT(DISTINCT o.uid, o.billing_first_name, " - ."o.billing_last_name, u.mail) FROM {uc_orders} AS o " - ."LEFT JOIN {users} AS u ON o.uid = u.uid WHERE o.uid > 0 " - ."AND o.order_status IN ". uc_order_status_list('general', TRUE) - ." ORDER BY billing_last_name ASC, billing_first_name ASC"; + + $count_query = ""; + switch ($GLOBALS['db_type']) { + case 'mysql': + case 'mysqli': + $count_query = "SELECT COUNT(DISTINCT o.uid, o.billing_first_name, " + ."o.billing_last_name, u.mail) FROM {uc_orders} AS o " + ."LEFT JOIN {users} AS u ON o.uid = u.uid WHERE o.uid > 0 " + ."AND o.order_status IN ". uc_order_status_list('general', TRUE) + ." ORDER BY billing_last_name ASC, billing_first_name ASC"; + break; + case 'pgsql': + $count_query = "SELECT DISTINCT o.uid, o.billing_last_name, o.billing_first_name, " + ."COUNT(*) " + ."FROM {uc_orders} AS o " + ."LEFT JOIN {users} AS u ON o.uid = u.uid WHERE o.uid > 0 " + ."AND o.order_status IN ". uc_order_status_list('general', TRUE) + ." GROUP BY o.uid, o.billing_last_name, o.billing_first_name " + ."ORDER BY billing_last_name ASC, billing_first_name ASC"; + break; + } + $message = t('The following users on your site have placed orders:'); } @@ -848,11 +884,30 @@ ."{users} AS u ON o.uid = u.uid WHERE o.uid > 0 AND " ."o.order_status IN ". uc_order_status_list('general', TRUE) . $where ." ORDER BY o.billing_last_name ASC"; - $count_query = "SELECT COUNT(DISTINCT o.uid, o.billing_first_name, " - ."o.billing_last_name, u.mail) FROM {uc_orders} AS o " - ."LEFT JOIN {users} AS u ON o.uid = u.uid WHERE o.uid > 0 AND " - ."o.order_status IN ". uc_order_status_list('general', TRUE) - . $where ." ORDER BY o.billing_last_name ASC"; + + + $count_query = ''; + switch ($GLOBALS['db_type']) { + case 'mysql': + case 'mysqli': + $count_query = "SELECT COUNT(DISTINCT o.uid, o.billing_first_name, " + ."o.billing_last_name, u.mail) FROM {uc_orders} AS o " + ."LEFT JOIN {users} AS u ON o.uid = u.uid WHERE o.uid > 0 AND " + ."o.order_status IN ". uc_order_status_list('general', TRUE) + . $where ." ORDER BY o.billing_last_name ASC"; + break; + case 'pgsql': + $count_query = "SELECT DISTINCT o.uid, o.billing_first_name, " + ."o.billing_last_name, u.mail, COUNT(*) " + ."FROM {uc_orders} AS o " + ."LEFT JOIN {users} AS u ON o.uid = u.uid WHERE o.uid > 0 AND " + ."o.order_status IN ". uc_order_status_list('general', TRUE) + . $where + ."GROUP BY o.uid, o.billing_first_name, o.billing_last_name, u.mail " + ."ORDER BY o.billing_last_name ASC"; + break; + } + $message = t('Search returned the following results:'); $output .= uc_store_customers($message, $query, $count_query, 100); @@ -2386,14 +2441,32 @@ $type = 'billing'; } - $result = db_query("SELECT DISTINCT ". $type ."_first_name AS first_name, " + switch ($GLOBALS['db_type']){ + case 'mysqli': + case 'mysql': + $result = db_query("SELECT DISTINCT ". $type ."_first_name AS first_name, " . $type ."_last_name AS last_name, ". $type ."_phone AS phone, " . $type ."_company AS company, ". $type ."_street1 AS street1, " . $type ."_street2 AS street2, ". $type ."_city AS city, " . $type ."_zone AS zone, ". $type ."_postal_code AS postal_code, " . $type ."_country AS country FROM {uc_orders} WHERE uid = %d " - ."AND order_status IN ". uc_order_status_list('general', TRUE) - ." ORDER BY created DESC", $uid); + ."AND order_status IN ". uc_order_status_list('general', TRUE) + ." ORDER BY created DESC", $uid); + break; + case 'pgsql': + // In pgsql, ORDER BY requires the field being sorted by to be in the SELECT list. + // But if we have the 'created' column in the SELECT list, the DISTINCT is + // rather useless. So for pgsql we will just sort addresses alphabetically. + $result = db_query("SELECT DISTINCT ". $type ."_first_name AS first_name, " + . $type ."_last_name AS last_name, ". $type ."_phone AS phone, " + . $type ."_company AS company, ". $type ."_street1 AS street1, " + . $type ."_street2 AS street2, ". $type ."_city AS city, " + . $type ."_zone AS zone, ". $type ."_postal_code AS postal_code, " + . $type ."_country AS country FROM {uc_orders} WHERE uid = %d " + ."AND order_status IN ". uc_order_status_list('general', TRUE) + ." ORDER BY ". $type ."_street1 DESC", $uid); + break; + } $addresses = array(); while ($address = db_fetch_array($result)) { diff -Naur ubercart/uc_taxes/uc_taxes.install ubercart_pgsqlfix3/uc_taxes/uc_taxes.install --- ubercart/uc_taxes/uc_taxes.install Thu Jan 31 12:26:23 2008 +++ ubercart_pgsqlfix3/uc_taxes/uc_taxes.install Tue Feb 5 12:39:21 2008 @@ -17,7 +17,7 @@ break; case 'pgsql': db_query("CREATE TABLE {uc_taxes} ( - id integer NOT NULL default 0, + id serial NOT NULL, name varchar(255) NOT NULL default '', rate float NOT NULL default 0, taxed_product_types text NOT NULL default '',