Commerce composer related data..

Commerce composer important tables:

PAGELAYOUT

PAGELAYOUTTYPE

PLLOCATION

PLPAGE

PLSTOREWIDGET

PLTEMPLATEREL

PLWIDGET

PLWIDGETDEF

PLWIDGETDEFDESC

PLWIDGETNVP

PLWIDGETREL

PLWIDGETSLOT

STOREPLTYPES

Some of the important queries:

Select * from PLWIDGET where PAGELAYOUT_ID in (select PAGELAYOUT_ID from PAGELAYOUT where name like 'Sample%');

Select * from PLWIDGETDEF where IDENTIFIER like 'Sample%';

Select * from PLWIDGETDEFDESC where PLWIDGETDEF_ID in (select PLWIDGETDEF_ID from PLWIDGETDEF where IDENTIFIER like 'Sample%');

Select * from PLSTOREWIDGET where PLWIDGETDEF_ID in (select PLWIDGETDEF_ID from PLWIDGETDEF where IDENTIFIER like 'Sample%');

Select * from PAGELAYOUT where name like 'Sample%';

Select * from PLWIDGETREL where PLWIDGET_ID_CHILD in (select PLWIDGET_ID

 from PLWIDGET where PAGELAYOUT_ID in (select PAGELAYOUT_ID from PAGELAYOUT where name like 'Sample%'));

Select * from PLWIDGETSLOT where PAGELAYOUT_ID in (select PAGELAYOUT_ID from PAGELAYOUT where name like 'Sample%');

Do share the related data if anything found so that it can be helpful to others tooo...

Attribute Data-Model and important tables with queries.

Attribute Data-Model

--Information related to ATTR_ID & IDENTIFIER based on this we will get the ATTR_ID with which we will be proceeding further for the next table.

select * from attr;

-- Information related to ATTRVAL_ID & ATTR_ID & IDENTIFIER based on the previous step we will be getting the ATTRVAL_ID with which we further proceed to get the catentries related to the particular value.

select * from attrval where attr_id=7000000000000009685;

-- Information related to ATTRVAL_ID  & ATTR_ID & CATENTRY_ID based on the previous step we will get the catEntries which are associated to a particular ATTR_ID

(Stores the catalog entry (CATENTRY) and attribute dictionary attribute (ATTR) relationship)

select * from catentryattr where attrval_id=7000000000000021190 and attr_id =7000000000000009685;

Attribute data model

Attribute Data Model-tables

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;