Difference between revisions of "User:Wilt"

From Zen Cart(tm) Wiki
Jump to: navigation, search
Line 92: Line 92:
  
 
Introducton - Show some current stats on sql queries, (800+ on home page) and explain why we got this problem.
 
Introducton - Show some current stats on sql queries, (800+ on home page) and explain why we got this problem.
 +
 
reasons behind productPricing class
 
reasons behind productPricing class
 +
 
factory class - override by product type,
 
factory class - override by product type,
 +
 
how the query works to get all info about product in one go
 
how the query works to get all info about product in one go
 +
 
how to extend base query to get other info (e.g. featured product)
 
how to extend base query to get other info (e.g. featured product)
 +
 
multi product queries
 
multi product queries
 +
 
examples of query reduction e.g. home page now ~ 190 queries
 
examples of query reduction e.g. home page now ~ 190 queries
  
 
=== Categories and Modified Pre-order tree traversal ===
 
=== Categories and Modified Pre-order tree traversal ===
 
explain current adjaceny list, and why it is poor - recursive code
 
explain current adjaceny list, and why it is poor - recursive code
 +
 
MPTT and rgt/lft diag
 
MPTT and rgt/lft diag
 +
 
example query  
 
example query  
 +
 
scaleability
 
scaleability
  
Line 108: Line 117:
  
 
Problems with integrating other database driver, mysql/oracle example
 
Problems with integrating other database driver, mysql/oracle example
 +
 
Historic stuff - query defines
 
Historic stuff - query defines
 +
 
virtualisation of queries
 
virtualisation of queries
 +
 
posibilities for future - rest/soap
 
posibilities for future - rest/soap
  

Revision as of 23:58, 3 May 2006

Coupon Restrictions

This is based on v1-3-0 code, previous code was unreliable in its handling of multiple/mixed product restrictions.

Single Level Restrictions

Note, first of all that initially all products are allowed.

By single level restriction, I mean that you only have one restriction registered for a coupon. That resriction may either be product or category based.

Denying a product means that product will not be included in calculations of discount. Denying a category will mean that any products in that category will not be included in calculations of discount

Allowing a product means only that product will be included in calculations of discount. This works slightly differently from multiple restrictions, but however does provide a simple method of restricting a coupon to 1 product, wihout having to go through the process of denying all other products.

Allowing a category will mean that only products in that category will be included in calculations of discount.


Multiple level restrictions

Category only restrictions

For any product the restriction is based on the deepest sub_category found for that product. The easiest way to see this is to find the category path for the product. assume this is 1_20_25_31. The category path is searched in reverse order and the restriction table checked till a match is found. Whatever the restriction is for the found category is used.

So if our restrictions were

  • Category 1 (deny)
  • Category 20 (allow)
  • Category 25 (deny)
  • Category 31 (allow)
      • WARNING TOTAL BOLLOCKS - NEED TO REWRITE ***

If the product was in category 31, the product would be included in discount calculations If the product was in category 25, the product would not be included in discount calculations If the product was in category 31, the product would be included in discount calculations If the product was in category 31, the product would be not included in discount calculations

Product only restrictions

Note, this works differently to the situation above where there is only 1 product entry in the restriction list. For multiple product restrictions (where no category restrictions are defined) only the products that are denied have any efffect.

All products are assumed to be allowed and only those products that are denied will not be included in the discount calculation. It there fore makes little sense when adding multiple product restrictions (where there are no category restrictions) to add a product - allow restriction.

Mixed Category/Product Restriction

Things definaitely start to get very complex when considering mixed category/product restrictions. To make this easier I am just going to present a few examples which should hopefully explain how mixing product/category restrictions work.


Example 1

Deny Hardware Category Allow Matrox G200 Graphic card.

In this example all products are initially allowed, All products in the hardware category (an its sub-categories) are denied However the Matrox Card even though it is in the hardware category will be allowed

Example 2

Deny Hardware Category Allow Mouse Category Deny Intellimouse Pro

Initially all products allowed All products in the Hardware category(and sub-categories) are denied All products in Mice category are allowed (even though they form part of hardware category) Intellimouse pro is denied (even though all Mice were initially allowed)

DOCS FOR EXTENSIBLE SHOPPING CART ACTIONS

Explain here the init_cart-handler.php code for abstracting cart actions and the extra_cart_actions override directory.

Note also the new methods in the shopping cart class

  • actionUpdateProduct
  • actionAddProduct
  • actionBuyNow
  • actionMultipleAddProduct
  • actionNotify
  • actionNotifyRemove
  • actionCustomerOrder
  • actionRemoveProduct


v1.3.1 documentation

productPricing class and SQl reduction

Introducton - Show some current stats on sql queries, (800+ on home page) and explain why we got this problem.

reasons behind productPricing class

factory class - override by product type,

how the query works to get all info about product in one go

how to extend base query to get other info (e.g. featured product)

multi product queries

examples of query reduction e.g. home page now ~ 190 queries

Categories and Modified Pre-order tree traversal

explain current adjaceny list, and why it is poor - recursive code

MPTT and rgt/lft diag

example query

scaleability

Data Access Objects

Problems with integrating other database driver, mysql/oracle example

Historic stuff - query defines

virtualisation of queries

posibilities for future - rest/soap

Bindvars usage guide

Variable binding is a method of delaying the binding of a variable to an sql query, and while delaying the binding it also allows us to pass information about the variable to carry security auditing of that varaible.


What do I mean by binding. Take this sql query

$sql = "SELECT customers_email_address FROM customers WHERE customers_id = " . $customers_id

In the above the variable $customers_id is bound immediatley to the query. The problem with this is that we do not know the provenance of the variable, if it in any way derives from user input, then we have to assume that it is tainted. In the past we have overcome this by carrying out some operation on the variable first, either by forcing a type or using a sanitizing function.

e.g. the original query would have been written

$sql = "SELECT customers_email_address FROM customers WHERE customers_id = " . (int)$customers_id

if the variable had been a string we would have done zen_db_input($stringVar)

However this has some shortcomings.

First it really only works with 2 data types, e.g. strings and ints, no provision allows for proper conversion of enums, blobs, dates and other datatypes and secondly there is absolutely no provision for any conversion of datatypes that may be neccessary for different Database backends other than mySql.

With variable binding we replace any variable with a 'placeholder', again taking the example above the sql would be written as

$sql = "SELECT customers_email_address FROM customers WHERE customers_id = :customersID"

we then need to convert the sql based on each place holder

$sql = $db->bindVars($sql, ':customersID', $customersID, 'integer');

or more generally

$sql = $db->bindVars(original sql(string), placeholder(string), variable(mixed), type(string))

The original sql is passed to allow the bindVar function to replace placeholders with variable date the type can at the moment be 1 of string integer date enum

also note that you have to call $db->bindVars for each placeholder within a sql query.

It is also possible to extend the type to provide some better sanitizing.

for example for a string rather than just define the type as 'string' we can define it as 'string:regexp' where regexp is a perl regular expression to match the string against.

for enum types we can do 'enum:emum1|enum2|enum3' where everything after the : is a pipe separated list of values that the enum should match.

(note this is not fully implemented yet)

The above gives us huge felxibility in securing variables used in sql queries not only against possibel security threats, but also against our own coding errors.