Der Prozess zum manuellen Erstellen eines Suchbereichs
im Produktberater erfordert gründliche Kenntnisse über das
Katalogsubsystem und das zugeordnete Datenbankschema. Der Suchbereich besteht aus zusätzlichen Datenbanktabellen, die
Informationen aus den folgenden Standardtabellen von WebSphere Commerce enthalten:
Hinweis: Diese
Informationen gelten nicht für iSeries-Benutzer. Schauen Sie statt dessen unter Einen Suchbereich für
den Produktberater mit einer XML-Datei erstellen nach.
Die folgenden Schritte sind erforderlich, um Datenbanktabellen zu erstellen, die für die Unterstützung von Produktberatersuchen notwendig sind. Im Folgenden finden Sie Beispiel-SQL-Anweisungen zum Erstellen eines einfachen Suchbereichs, der aus Tabellen, Anzeigen und den entsprechenden Indizes besteht. Diese Anweisungen enthalten Daten wie beispielsweise Produkt- und Kategorie-IDs, die Sie durch eigene Daten ersetzen müssen. Sie werden hier zu Demonstrationszwecken angezeigt. Wenn eine SQL-Anweisung Daten zurückgibt (besonders, wenn diese Daten als Eingabe bei einem späteren Schritt verwendet werden), werden die zurückgegebenen Daten fett und in eckigen Klammern nach der SQL-Anweisung angezeigt.
Hinweis: Die Beispiele in dieser Datei sind DB2-spezifisch. Oracle-Benutzer sollten das entsprechende Beispieldienstprogramm für Oracle-spezifische SQL-Anweisungen heranziehen.
db2 "select distinct(attribute_id),language_id,attrtype_id,name
from attribute
where
catentry_id in(select catentry_id from catgpenrel where
catgroup_id=1)
order by attribute_id"
db2 create table ICT1
( PRRFNBR int not null,
PRNBR varchar(64) );
db2 create table ICTDESC1
( PRRFNBR int not null,
PRSDESC varchar(254),
AVAILABLE int not null,
PRTHMB varchar(254),
XMLDETAIL long varchar,
LANGUAGE_ID int not null,
F_COLOR char(254),
F_SIZE int,
Constraint ictd1_pk
primary key (prrfnbr,language_id) );
db2 create view ICV1_NULL as select ic.prrfnbr,
ic.prnbr,
icd.PRSDESC,
icd.AVAILABLE,
icd.PRTHMB,
icd.XMLDETAIL,
icd.LANGUAGE_ID,
icd.F_COLOR,
icd.F_SIZE,
p.ipsgnbr,
p.storeid,
p.ppprc,
p.setccurr
from ICTPRICES p, ICT1 ic, ICTDESC1 icd
where ic.PRRFNBR=p.CATENTRY_ID and
ic.PRRFNBR=icd.PRRFNBR and
p.IPSGNBR is NULL;
db2 "insert into icrootcat
(rootcategoryid,catgroup_id,pfpasync,pfreq,tablename,
lastmodified)
values
(1,1,0,1,'ICV1_','2000-06-15-14.48.25.686000')"
db2 "insert into icexplfeat
(featureid,catgroup_id,columnname,length,datatype,keysequence,
nullable,location,included,relevance)
values
(1, 1,'F_COLOR',254,'com.ibm.commerce.datatype.DsString',
0,1,1,1,0);
db2 "insert into icexplfeat
(featureid,catgroup_id,columnname,length,datatype,keysequence,
nullable,location,included,relevance)
values
(2, 1,'F_SIZE',4,'com.ibm.commerce.datatype.DsInteger',
0,1,1,1,0);
db2 "insert into icexplfeat
(featureid,catgroup_id,columnname,length,datatype,keysequence,
nullable,location,included,relevance)
values
(3, 1,'PRRFNBR',4,'com.ibm.commerce.datatype.DsInteger',
1,0,0,1,0);
db2 "insert into icexplfeat
(featureid,catgroup_id,columnname,length,datatype,keysequence,
nullable,location,included,relevance)
values
(4,1,'PRNBR',254,'com.ibm.commerce.datatype.DsString',
0,0,0,1,0);
db2 "insert into icexplfeat
(featureid,catgroup_id,columnname,length,datatype,keysequence,
nullable,location,included,relevance)
values
(5, 1,'PRSDESC',254,'com.ibm.commerce.datatype.DsString',
0,1,3,1,0);
db2 "insert into icexplfeat
(featureid,catgroup_id,columnname,length,datatype,keysequence,
nullable,location,included,relevance)
values
(6, 1,'PRTHMB',254,'com.ibm.commerce.datatype.DsImage',
0,1,3,1,0);
db2 "insert into icexplfeat
(featureid,catgroup_id,columnname,length,datatype,keysequence,
nullable,location,included,relevance)
values
(7, 1,'XMLDETAIL',32700,'com.ibm.commerce.datatype.DsURLLink',
0,1,3,1,0);
db2 "insert into icexplfeat
(featureid,catgroup_id,columnname,length,datatype,keysequence,
nullable,location,included,relevance)
values
(8, 1,'PPPRC',16,'com.ibm.commerce.datatype.DsCurrency',
0,0,2,1,0);
db2 "insert into icexplfeat
(featureid,catgroup_id,columnname,length,datatype,keysequence,
nullable,location,included,relevance)
values
(9, 1,'AVAILABLE',4,'com.ibm.commerce.datatype.DsInteger',
0,0,3,1,0);
db2 "insert into icexpldesc (featureid,language_id,name,
unitofmeasure,description,elaboration,scale,precision)
values
(1, 1,'Color','','Color',NULL,0,0);
db2 "insert into icexpldesc (featureid,language_id,name,
unitofmeasure,description,elaboration,scale,precision)
values
(2, 1,'Size','','Size',NULL,0,0);
db2 "insert into icexpldesc (featureid,language_id,name,
unitofmeasure,description,elaboration,scale,precision)
values
(3, 1,'CATENTRY_ID','','Product Reference Number',NULL,0,0);
db2 "insert into icexpldesc (featureid,language_id,name,
unitofmeasure,description,elaboration,scale,precision)
values
(4, 1,'PARTNUMBER','','ProductNumber/SKU',NULL,0,0);
db2 "insert into icexpldesc (featureid,language_id,name,
unitofmeasure,description,elaboration,scale,precision)
values
(5, 1,'SHORTDESCRIPTION','','Short Description',NULL,0,0);
db2 "insert into icexpldesc (featureid,language_id,name,
unitofmeasure,description,elaboration,scale,precision)
values
(6, 1,'THUMBNAIL','','Thumbnail Image File',NULL,0,0);
db2 "insert into icexpldesc (featureid,language_id,name,
unitofmeasure,description,elaboration,scale,precision)
values
(7, 1,'XMLDETAIL','','XML Detail',NULL,0,0);
db2 "insert into icexpldesc (featureid,language_id,name,
unitofmeasure,description,elaboration,scale,precision)
values
(8, 1,'STANDARDPRICE','','MSRP',NULL,2,15);
db2 "insert into icexpldesc (featureid,language_id,name,
unitofmeasure,description,elaboration,scale,precision)
values
(9, 1,'AVAILABLE','','Availability',NULL,0,0);
db2 "insert into ict1 (select catentry_id,partnumber
from catentry where catentry_id in (select catentry_id
from catgpenrel where catgroup_id=1))"
db2 "import from catentry.del of del insert into ict1"
db2 "insert into ictdesc1 (PRRFNBR,PRSDESC,AVAILABLE,
PRTHMB,XMLDETAIL,LANGUAGE_ID)
(select c2.catentry_id,c2.shortdescription,c2.available,
c2.thumbnail, c2.xmldetail, c2.language_id
from catentry c1, catentdesc c2
where c1.catentry_id=c2.catentry_id
and c2.language_id=-1
and c2.published=1
and (c2.catentry_id in (select catentry_id from catgpenrel
where catgroup_id=1)))"
db2 "update ictdesc1 set F_Color =
(select stringvalue from attrvalue
where ictdesc1.prrfnbr=attrvalue.catentry_id
and ictdesc1.language_id=attrvalue.language_id
and attrvalue.attribute_id in
(select attribute_id from attribute
where name = 'Color')
and attrvalue.language_id=-1
and attrvalue.catentry_id in
(select catentry_id from catgpenrel
where catgroup_id=1))
where language_id=-1"
db2 "update ictdesc1 set F_Size =
(select integervalue from attrvalue
where ictdesc1.prrfnbr=attrvalue.catentry_id
and ictdesc1.language_id=attrvalue.language_id
and attrvalue.attribute_id in
(select attribute_id from attribute
where name = 'Size')
and attrvalue.language_id=-1
and attrvalue.catentry_id in
(select catentry_id from catgpenrel
where catgroup_id=1))
where language_id=-1"
db2 "insert into ictprices(storeid, catentry_id,setccurr,ppprc)
select storeent_id, catentry_id, currency, price
from storeent, offer, offerprice,tradeposcn
where storeent_id=0
and tradeposcn_id=offer.tradeposcn_id
and offer.offer_id=offerprice.offer_id
and tradeposcn.type='S'"
![]() |