Aqui estão alguns exemplos das maneiras como a amostra pode ser estendida:
Este cenário apresenta os seguintes desafios:
A SQL a seguir é para o DB2 Universal Database. Se estiver executando Oracle, você poderá precisar modificá-la adequadamente (e lembre-se de configurar @ como o terminador de instrução):
CREATE TABLE "ADAPTER_ITEM" ( "PKEY" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE NO CACHE), "NAME" VARCHAR(20) NOT NULL, "INVOICE_KEY" VARCHAR(10) NOT NULL, "PRICE" FLOAT(53) DEFAULT 0 ) DATA CAPTURE NONE@ ALTER TABLE "ADAPTER_ITEM" ADD CONSTRAINT "ADAPTER_ITEM_PK" PRIMARY KEY ("PKEY")@
A SQL cria uma nova tabela, ADAPTER_ITEM, com quatro colunas; uma chave primária gerada automaticamente, o nome do item, uma referência à fatura à qual ele pertence e o preço do item. A nova tabela usa a INVOICE_KEY como uma chave estrangeira para a tabela ADAPTER_ITEM.
É necessário atualizar apenas a parte do fluxo que cria mensagens ADAPTERINVOICE. Clique duas vezes no nó DatabaseInput no fluxo MigratedAdapter para abrir a ESQL, navegue para a visualização Origem e role para baixo até a parte que cria a mensagem ADAPTERINVOICE:
SET Root.MRM = THE( SELECT DispatchedEvent.Usr.OBJECT_VERB AS verb, INVOICE.PKEY AS Inv:PKEY, INVOICE.ITEM AS Inv:"Item", INVOICE.QUANTITY AS Inv:QUANTITY, INVOICE.CUSTOMER AS Inv:CUSTOMER, 'Retrieve' AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.verb, CUSTOMER.PKEY AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:PKEY, CUSTOMER.FIRSTNAME AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:FIRSTNAME, CUSTOMER.LASTNAME AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:LASTNAME, CUSTOMER.ADDRESS AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADDRESS, 'Retrieve' AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.verb, ADDRESS.PKEY AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:PKEY, ADDRESS.FIRSTLINE AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:FIRSTLINE, ADDRESS.SECONDLINE AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:SECONDLINE, ADDRESS.CODE AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:CODE, ADDRESS.CITY AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:CITY, ADDRESS.COUNTRY AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:COUNTRY, CUSTOMER.CCODE AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:CCODE , 'JDBCConnector_' ||CAST(DispatchedEvent.Usr.EVENT_ID AS CHARACTER) ||'ibm_events' ||CAST(DispatchedEvent.Usr.EVENT_TIME AS CHARACTER FORMAT 'yyyyMMddhhmmss.SSS') AS Inv:ObjectEventId FROM Database.ADAPTER_INVOICE AS INVOICE, Database.ADAPTER_CUSTOMER AS CUSTOMER, Database.ADAPTER_ADDRESS AS ADDRESS WHERE INVOICE.PKEY = DispatchedEvent.Usr.OBJECT_KEY AND CUSTOMER.PKEY = INVOICE.CUSTOMER AND ADDRESS.PKEY = CUSTOMER.ADDRESS);
Inclua os itens aninhados e o atributo de tamanho para o número de itens recuperados.
(SELECT ADAPTER_ITEM.PKEY, ADAPTER_ITEM.NAME, ADAPTER_ITEM.INVOICE_KEY, ADAPTER_ITEM.PRICE FROM Database.ADAPTER_ITEM WHERE ADAPTER_ITEM.INVOICE_KEY = INVOICE.PKEY) AS Inv:ITEMS.Ite:ADAPTERITEM[],
Agora, preencha o novo atributo com o número de linhas ADAPTERITEM recuperado. Inclua o seguinte após SELECT:
CREATE FIRSTCHILD OF Root.MRM.Inv:ITEMS TYPE NameValue NAME 'size' VALUE CARDINALITY(Root.MRM.Inv:ITEMS.Ite:ADAPTERITEM[]);
Normalmente, você gravaria:
SET Root.MRM.Inv:ITEMS.size = CARDINALITY(Root.MRM.Inv:ITEMS.Ite:ADAPTERITEM[])Mas, como a mensagem é uma sequência ordenada, é necessário assegurar que o atributo seja o primeiro filho.
SET Root.MRM = THE( SELECT DispatchedEvent.Usr.OBJECT_VERB AS verb, INVOICE.PKEY AS Inv:PKEY, (SELECT ADAPTER_ITEM.PKEY, ADAPTER_ITEM.NAME, ADAPTER_ITEM.INVOICE_KEY, ADAPTER_ITEM.PRICE FROM Database.ADAPTER_ITEM WHERE ADAPTER_ITEM.INVOICE_KEY = INVOICE.PKEY) AS Inv:ITEMS.Ite:ADAPTERITEM[], INVOICE.QUANTITY AS Inv:QUANTITY, INVOICE.CUSTOMER AS Inv:CUSTOMER, 'Retrieve' AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.verb, CUSTOMER.PKEY AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:PKEY, CUSTOMER.FIRSTNAME AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:FIRSTNAME, CUSTOMER.LASTNAME AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:LASTNAME, CUSTOMER.ADDRESS AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADDRESS, 'Retrieve' AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.verb, ADDRESS.PKEY AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:PKEY, ADDRESS.FIRSTLINE AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:FIRSTLINE, ADDRESS.SECONDLINE AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:SECONDLINE, ADDRESS.CODE AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:CODE, ADDRESS.CITY AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:CITY, ADDRESS.COUNTRY AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:COUNTRY, CUSTOMER.CCODE AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:CCODE , 'JDBCConnector_' ||CAST(DispatchedEvent.Usr.EVENT_ID AS CHARACTER) ||'ibm_events' ||CAST(DispatchedEvent.Usr.EVENT_TIME AS CHARACTER FORMAT 'yyyyMMddhhmmss.SSS') AS Inv:ObjectEventId FROM Database.ADAPTER_INVOICE AS INVOICE, Database.ADAPTER_CUSTOMER AS CUSTOMER, Database.ADAPTER_ADDRESS AS ADDRESS WHERE INVOICE.PKEY = DispatchedEvent.Usr.OBJECT_KEY AND CUSTOMER.PKEY = INVOICE.CUSTOMER AND ADDRESS.PKEY = CUSTOMER.ADDRESS); CREATE FIRSTCHILD OF Root.MRM.Inv:ITEMS TYPE NameValue NAME 'size' VALUE CARDINALITY(Root.MRM.Inv:ITEMS.Ite:ADAPTERITEM[]);
Teste a nova funcionalidade incluindo um par de itens na tabela ITEM e incluindo ou modificando uma fatura que usa estes itens:
--<ScriptOptions statementTerminator="@"/> INSERT INTO ADAPTER_ITEM (NAME,INVOICE_KEY,PRICE) VALUES ('Calças Compridas', 'inv10', 2.17)@ INSERT INTO ADAPTER_ITEM (NAME,INVOICE_KEY,PRICE) VALUES ('Suéteres', 'inv10', 12.00)@ INSERT INTO ADAPTER_INVOICE VALUES ('inv10', '', 10, 'cust1')@
Insira a SQL acima em um novo script SQL no projeto Design de Dados AdapterMigration no Explorar de Projetos de Dados. Em seguida, clique com o botão direito do mouse dentro da visualização Data Project Explorer e escolha "Executar SQL". Isso fará com que o fluxo seja executado e a seguinte mensagem apareça na fila ADAPTERMIGRATION.INVOICE:
<?xml version="1.0"?> <Q1:ADAPTERINVOICE xmlns:Q1="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/ADAPTERINVOICE" xmlns:Q4="http://dbinput/amartens" xmlns:Q3="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/ADAPTERADDRESS" xmlns:Q2="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/ADAPTERCUSTOMER" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" verb="Criar"> <Q1:PKEY>inv10</Q1:PKEY> <Q1:ITEMS size="2"> <Q4:ADAPTERITEM> <PKEY>9</PKEY> <NAME>Calças Compridas</NAME> <INVOICE_KEY>inv10</INVOICE_KEY> <PRICE>2.17E+0</PRICE> </Q4:ADAPTERITEM> <Q4:ADAPTERITEM> <PKEY>10</PKEY> <NAME>Suéteres</NAME> <INVOICE_KEY>inv10</INVOICE_KEY> <PRICE>1.2E+1</PRICE> </Q4:ADAPTERITEM> </Q1:ITEMS> <Q1:QUANTITY>10</Q1:QUANTITY> <Q1:CUSTOMER>cust1</Q1:CUSTOMER> <Q1:ADAPTERCUSTOMER> <Q2:ADAPTERCUSTOMER verb="Recuperar"> <Q2:PKEY>cust1</Q2:PKEY> <Q2:FIRSTNAME>Barney</Q2:FIRSTNAME> <Q2:LASTNAME>Rubble</Q2:LASTNAME> <Q2:ADDRESS>add1</Q2:ADDRESS> <Q2:ADAPTERADDRESS> <Q3:ADAPTERADDRESS verb="Recuperar"> <Q3:PKEY>add1</Q3:PKEY> <Q3:FIRSTLINE>MP211</Q3:FIRSTLINE> <Q3:SECONDLINE>IBM Hursley Park</Q3:SECONDLINE> <Q3:CODE>SO21 2JN</Q3:CODE> <Q3:CITY>Winchester</Q3:CITY> <Q3:COUNTRY>GB</Q3:COUNTRY> </Q3:ADAPTERADDRESS> </Q2:ADAPTERADDRESS> <Q2:CCODE>Dev</Q2:CCODE> </Q2:ADAPTERCUSTOMER> </Q1:ADAPTERCUSTOMER> <Q1:ObjectEventId>JDBCConnector_160ibm_events20101108020022.046</Q1:ObjectEventId> </Q1:ADAPTERINVOICE>