SQL QUERRIES usefull while accessing the database

SQL INNER JOIN Syntax :

SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;

OR

SELECT column_name(s) FROM table1 JOIN table2 ON table1.column_name=table2.column_name;

-- This is used to filter with the ids for the tables which have the relation

Example :

SELECT users.users_ID, Member.Member_Id FROM users INNER JOIN member ON users.Users_id=member.Member_Id;

SQL LEFT JOIN Syntax :

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;

OR

SELECT column_name(s) FROM table1 LEFT OUTER JOIN table2 ON table1.column_name=table2.column_name;

Example :

SELECT users.users_id, Orders.Orders_ID FROM Users LEFT JOIN Orders ON users.users_ID=Orders.orders_id ORDER BY users.users_id;

SQL RIGHT JOIN Syntax

SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name;

Example :

SELECT users.users_id, Orders.Orders_ID FROM Users RIGHT JOIN Orders ON users.users_ID=Orders.orders_id ORDER BY users.users_id;

SQL FULL OUTER JOIN SYNTAX

SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;

Example :

SELECT Users.Users_id, Orders.orders_id FROM users FULL OUTER JOIN Orders ON users.users_id=Orders.orders_ID ORDER BY users.users_id;

Promotions in WCS. 

Promotions provide the ability to attract the customers to purchase the items from the site and make them visit once again once the promotions are available. WCS supports 3. types of promotions which play the vital role for any site which is developed in WCS, the following are the types of promotions that WCS supports

1. Catalog-Entry level
2. Order level
3. Shipping level

Catalog-Entry Level Promotions: Under this type the WCS supports some kind of promotions that are related to the catalog,category.

For example : Buy Catalog entry A,Get Catalog Entry B free

                      Buy Catalog Entry A, Get some percentage off on the purchase of Catalog entry B.

                      Percentage off on the total amount on purchase of catalog entries fro a category.

                      Amount off on the subtotal of catalog entries of a category.

                      Buy X & Y , get a percentage off on both, etc.

Order level Promotions: Under this type of promotions we deal the promotions which are enabled during when the order is getting placed by a customer in the site.

For example : Percentage off on an order

                      Certain amount off on the order being placed

                      Free gift with an order.

Shipping level Promotions: This type of promotions deals with the shipping of the item that is being placed for order.

For Example : Free Shipping of the order

                      Discounted Shipping for an order using a selected shipping mode.

                      Discounted Shipping for an item using a selected shipping mode.

Data Model and the tables related to Promotions:

Promotion Data Model

The main tables in Promotion data model are :

1. PX_PROMOTION: this table contains Promotions and its status (i.e. Active & Inactive),Starttime and Endtime ,Code-that is is related to that particular promotion.
2. PX_POLICY: this table tells about the promotion policies.
3. PX_USAGE: this table tells about statistics  the promotion usage. also contains the ORDERS_ID of the orders to which the promotion was applied.
4. CLCD_PROMO: this table stores information about the relationship between the Calculation Framework and the promotion standalone infrastructure. Each calculation code matches one promotion in the PX_PROMOTION table.

Insertion of ACPOLCIES using SQL commands...

SQL Insertion of ACPOLCIES for a newly Created Command :

1.insert into acrescgry (ACRESCGRY_ID,RESCLASSNAME) values ((select counter from keys where tablename='acrescgry'),'com.sample.commands.PaypalForwardCmd');

2.insert into acresact (ACRESCGRY_ID, ACACTION_ID) values ((select counter from keys where tablename='acrescgry'),(select ACACTION_ID from acaction where action='Execute'));

3.insert into acresgpres (ACRESGRP_ID, ACRESCGRY_ID) values ((select ACRESGRP_ID from acresgrp where MEMBER_ID in (select orgentity_id from orgentity where orgentityname='Root Organization') and GRPNAME='AllSiteUserCmdResourceGroup'), (select counter from keys where tablename='acrescgry'));

4.UPDATE KEYS SET COUNTER = COUNTER+1 WHERE TABLENAME = 'acrescgry';

5.Commit;

Finally Check for these tables for the entries :

1. ACRESCGRY
2. ACRESACT
3. ACRESGPRES

SQL Insertion of ACPOLCIES for a newly Created VIEW :

1.insert into acaction (acaction_id, action) values ((select counter from keys where tablename='acaction'), 'PaypalForwardView');

2.insert into acactactgp (ACACTGRP_ID,ACACTION_ID) values ((SELECT ACACTGRP_ID FROM ACACTGRP WHERE GROUPNAME = 'AllSiteUsersViews' 

and member_id in (select orgentity_id from orgentity where orgentityname='Root Organization') 

), (select acaction_id from acaction where action='PaypalForwardView'));

3.UPDATE KEYS SET COUNTER = COUNTER+1 WHERE TABLENAME = 'acaction';

4.commit;

Finally Check for these tables for the entries :

1. ACACTION
2. ACACTACTGP

WCS Organisation Structures

Organization Structures

Types of Organizations in WCS are :

1. Consumer Direct Organization.
2. B2B direct Organization.
3. Demand Chain Organization.
4. Extended Sites Organization.
5. Supply Chain Organization 

1.  Consumer Direct Organization.

Consumer Direct Organization.

 

     2.  B2B direct Organization.

B2B direct Organization.

  

    3. Demand Chain Organization.

Demand Chain Organization.

    4. Extended Sites Organization.

Extended Sites Organization.

    

     5. Supply Chain Organization.

Supply Chain Organization.

    

Access Bean 

    

     WCS commands interact with access beans rather than directly with the entity beans. Access beans behave like Java beans, & hide all enterprise bean-specific programming interfaces from the client.

Types of Access beans are:

1. Copy Helper  methods.
2. Finder methods.
3. EJB methods.

There are 2 types of  Copy Helper methods:

1. refreshCopyHelper()
2. commitCopyHelper()

• refreshCopyHelper():  refresh copy helper is treated as "find for update” and it locks that particular row unless or until you are done with entire transaction. So you read something with refreshcopyHelper and you keep doing n no of things and when you are finally done then only these locks are released.

          refreshCopyHelper reads the database to update the access bean.

• commitCopyHelper():   commitCopyHelper() takes the current state of the access bean and updates the database with it.

         Updates the database with the contents of the access bean.  

The Design patterns Present in WCS

Design patterns

The Design patterns Present in WCS:

1. Model-View-Controller design pattern.
2. Display design patterns.
3. Command design patterns.

Model-View-Controller design pattern:

The model-view-controller (MVC) design pattern specifies that an application consist of a data model, presentation information, and control information.

The model (for example, the data information) contains only the pure application data; it contains no logic describing how to present the data to a user.

The view (for example, the presentation information) presents the model's data to the user. The view knows how to access the model's data, but it does not know what this data means or what the user can do to manipulate it.

Finally, the controller (for example, the control information) exists between the view and the model. It listens to events triggered by the view (or another external source) and executes the appropriate reaction to these events. In most cases, the reaction is to call a method on the model. Since the view and the model are connected through a notification mechanism, the result of this action is then automatically reflected in the view.

Command design pattern:

WebSphere Commerce Server accepts requests from browser-based thin-client applications; from applications such as the Sales Center; and remote applications. For example, a request may come from a remote procurement system, or from another commerce server.

• Task command: These implement discrete pieces of a business task such as address verification or ensuring passwords comply to the defined password policies. For example, during executing of the Registration controller command a callout may be made to a task command to validate the address entered.
• Controller command: These implement business tasks such as user registration and allocation of inventory. Use these to implement high-level process logic for the business task.

Display design pattern:

Display pages return a response to a client. Typically, display pages are implemented as JSP pages. In order to support multiple device types, a URL access to a page should use a view name, not the name of the actual JSP file.

The main rationale behind this level of indirection is that the JSP page represents a view. The ability to select the appropriate view (for example, based on locale, device type, or other data in the request context) is highly desirable, especially since a single request often has multiple possible views. Consider the example of two shoppers requesting the home page of a store, one shopper using a typical Web browser and the other using a cellular phone. Clearly, the same home page should not be displayed to each shopper. The Web controller's responsibility is to accept the request, then based upon information in the command registration framework, determine the view that each shopper receives.

Access Control Policies

          An Access Control Policies authorizes a group of users to perform a set of actions on a set of resources.

An access control policy has 4 elements, they are:

1. User.
2. Action.
3. Resource.
4. Relationship.

User:  The Users are the people that use the system. For access control purposes, users must be grouped into relevant access groups. 

Action: Actions are the activities that users can perform on the resource.

Resource: Resources are the entities that are protected. A resource group might include business objects like contract or order, or a set of related commands.

Relationship: Relationships are the relationship between the user and the resource. Access control policies may require that a relationship between the user and the resource be satisfied. Each resource class can have a set of relationships associated with it. Each resource can have a set of users that fulfill each relationship.

There are 2 types of access control policies, they are:

1. Groupable standard policies(policy type-2).
2. Groupable template policies(policy type-3).

     A Groupable standard policy is applied, once, at organizations that subscribe to a policy group that contains the policy.

     A Groupable template policies are dynamic in nature in that they have an access group that is scoped, when the system is running, to the organization that owns the resource.

The table updated in access control poliies are:

1. ACACTION: Stores actions that are to be performed on the resources in the system.
2. ACPOLICY: Stores all the access control policies in a system.
3. ACRESGRP: Stores all access control resource groups in a system.
4. ACPOLDESC: Stores local specific information for ACPOLICY.
5. ACRELATION: Relation between resources and members in system.
6. ACRESREL: Associates a resource and relationships that it supports.

        WebSphere Commerce allows you to determine, through access control, which tasks a particular user, be they customers, buyers, administrators, distributors, manufacturers, or suppliers, can perform in relation to your business.

To facilitate database management and ensure security, access to WebSphere Commerce must be restricted to specific individuals and organizations. The process of restricting access is referred to as access control or authorization. Authorization can be defined as security guidelines that:

Allow or deny a user of a system access to the resources managed by a system.

Specify what actions the user can perform on each resource.

The authorization model for WebSphere Commerce is based upon the enforcement of access control policies. Access control policies are enforced by the access control Policy Manager. In general, when a user attempts to access a protected resource, the access control policy manager first determines what access control policies are applicable for that user and then, based upon the applicable access control policies, it determines if the user is allowed to perform the requested operation on the given resource.

NOTE: The Site Administrator manages the access control policies that apply to a site or store.