Discover and create a FDA schema

09 Dec 2023 - Alexandre Morin

This article will illustrate the following flow and show the corresponding SOAP calls.

Create an external account corresponding to a FDA PostgreSQL database
Discover all the tables available in the FDA database
Choose a table and discover the schema
Annotate and save the schema

Create FDA external account

We'll create (or update) an external acount for a FDA database. You need the server, port, database and schema name, user and password.

Encrypt the password

The first step is to encrypt the password. The xtk:persist#EncryptPassword API will by used to encrypt the password.

<SOAP-ENV:Envelope xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:ns='urn:xtk:persist' xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/'>
<SOAP-ENV:Body>

<EncryptPassword xmlns='urn:xtk:persist' SOAP-ENV:encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'>
<__sessiontoken xsi:type='xsd:string'></__sessiontoken>
      
	      <strDecrypted xsi:type='xsd:string'>admin</strDecrypted>

</EncryptPassword>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

The API will return the encrypted password as follows.

<?xml version='1.0'?>
<SOAP-ENV:Envelope
	xmlns:xsd='http://www.w3.org/2001/XMLSchema'
	xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
	xmlns:ns='urn:xtk:session'
	xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/'>
	<SOAP-ENV:Body>
		<EncryptPasswordResponse
			xmlns='urn:xtk:session' SOAP-ENV:encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'>
			<pstrEncrypted xsi:type='xsd:string'>@S/U/D7xOdZfRAwf3+WRM3w==</pstrEncrypted>
		</EncryptPasswordResponse>
	</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Create the external account

Now the external account can be created using the xtk:persist#Write API. Give it a name, make sure it's active and set all connection parameters as follows. Make sure you provide the encrypted password

  <SOAP-ENV:Envelope xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:ns='urn:xtk:persist' xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/'>
    <SOAP-ENV:Body>
    
    <Write xmlns='urn:xtk:persist' SOAP-ENV:encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'>
    <__sessiontoken xsi:type='xsd:string'></__sessiontoken>
          <domDoc xsi:type='ns:Element' SOAP-ENV:encodingStyle='http://xml.apache.org/xml-soap/literalxml'>
    
            <extAccount xtkschema="nms:extAccount" 
                name="pg2" label="Postgres (2)" provider="PostgreSQL" type="7" active="1"
                server="localhost" port="" dbName="fda" dbSchema="public" 
                account="postgres" password="@S/U/D7xOdZfRAwf3+WRM3w=="
                timezone="_server_" unicodeData="1">
            </extAccount>
    
          </domDoc>
    </Write>
    </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>

The API does not return anything. Note the the name attribute is a unique key in the nms:account schema. So the same API call can be used to update the account.

Test the connection

Campaign provides the nms:extAccount#TestAccount API to test the connection. Unfortunately, this API cannot take an extAccount id, but it needs to be passed all the credentials settings again. Note that the password is still encrypted.


  <SOAP-ENV:Envelope xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:ns='urn:xtk:persist' xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/'>
    <SOAP-ENV:Body>
    
    <TestAccount xmlns='urn:xtk:persist' SOAP-ENV:encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'>
    <__sessiontoken xsi:type='xsd:string'></__sessiontoken>
    
          <param xsi:type='xsd:byte'>7</param>
          <param xsi:type='xsd:boolean'>1</param>
          <param xsi:type='xsd:string'>PostgreSQL:localhost</param>
          <param xsi:type='xsd:string'>postgres</param>
          <param xsi:type='xsd:string'>@S/U/D7xOdZfRAwf3+WRM3w==</param>
          <param xsi:type='xsd:string'>fda</param>
          <param xsi:type='xsd:string'>NChar=0;unicodeData=1;timezone=_server_;dbSchema=public;fileMethod=uploadFile;filePath=;</param>
          <param xsi:type='xsd:string'>pg2</param>
          <param xsi:type='xsd:boolean'>0</param>
          <param xsi:type='xsd:string'></param>
          <param xsi:type='xsd:string'></param>
          <param xsi:type='xsd:string'></param>
          <param xsi:type='xsd:string'></param>
      
      </TestAccount>
    </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>

This should return a string with version information.

<?xml version='1.0'?>
<SOAP-ENV:Envelope
	xmlns:xsd='http://www.w3.org/2001/XMLSchema'
	xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
	xmlns:ns='urn:nms:extAccount'
	xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/'>
	<SOAP-ENV:Body>
		<TestAccountResponse
			xmlns='urn:nms:extAccount' SOAP-ENV:encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'>
			<pstrServer xsi:type='xsd:string'>localhost</pstrServer>
			<pstrDbmsVer xsi:type='xsd:string'>Database server version 'PostgreSQL 12.2, compiled by Visual C++ build 1914, 64-bit'.</pstrDbmsVer>
			<pstrWarehouse xsi:type='xsd:string'></pstrWarehouse>
			<pstrTestDuration xsi:type='xsd:string'>Test connection took: 0 ms</pstrTestDuration>
		</TestAccountResponse>
	</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Query the external account

You can also use a query to get information about the external account.

<SOAP-ENV:Envelope xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:ns='urn:xtk:persist' xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/'>
<SOAP-ENV:Body>

<ExecuteQuery xmlns='urn:xtk:queryDef' SOAP-ENV:encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'>
<__sessiontoken xsi:type='xsd:string'></__sessiontoken>

      <entity xsi:type='ns:Element' SOAP-ENV:encodingStyle='http://xml.apache.org/xml-soap/literalxml'>
        <queryDef fullLoad="true" operation="get" schema="nms:extAccount" startPath="/" xtkschema="xtk:queryDef">
          <select>
            <node expr="@id"/>
            <node expr="@name"/>
            <node expr="@label"/>
            <node expr="@provider"/>
            <node expr="@active"/>
            <node expr="@server"/>
            <node expr="@port"/>
            <node expr="@dbName"/>
            <node expr="@dbSchema"/>
            <node expr="@account"/>
            <node expr="@password"/>
            <node expr="@timezone"/>
            <node expr="@unicodeData"/>
          </select>
          <where>
            <condition expr="@name='pg2'"/>
          </where>
        </queryDef>
      </entity>

</ExecuteQuery>


</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

It will return something like this

<?xml version='1.0'?>
<SOAP-ENV:Envelope
	xmlns:xsd='http://www.w3.org/2001/XMLSchema'
	xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
	xmlns:ns='urn:xtk:queryDef'
	xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/'>
	<SOAP-ENV:Body>
		<ExecuteQueryResponse
			xmlns='urn:xtk:queryDef' SOAP-ENV:encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'>
			<pdomOutput xsi:type='ns:Element' SOAP-ENV:encodingStyle='http://xml.apache.org/xml-soap/literalxml'>
				<extAccount account="postgres" active="1" dbName="fda" dbSchema="public" id="4510" label="Postgres (2)" name="pg2" password="@S/U/D7xOdZfRAwf3+WRM3w==" port="" provider="PostgreSQL" server="localhost" timezone="_server_" unicodeData="1"></extAccount>
			</pdomOutput>
		</ExecuteQueryResponse>
	</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Discover the tables

Now you can discover the tables available in the FDA database. The xtk:sqlSchema#BuildTableList API will return a list of tables.

<SOAP-ENV:Envelope xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:ns='urn:xtk:persist' xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/'>
<SOAP-ENV:Body>

<BuildTableList xmlns='urn:xtk:queryDef' SOAP-ENV:encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'>
<__sessiontoken xsi:type='xsd:string'></__sessiontoken>

			<param xsi:type='xsd:string'>nms:extAccount:pg2</param>
			<param xsi:type='xsd:string'>%%</param>

</BuildTableList>

</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Which returns the following. In this example, I only have one table named x.

  <?xml version='1.0'?>
  <SOAP-ENV:Envelope
    xmlns:xsd='http://www.w3.org/2001/XMLSchema'
    xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
    xmlns:ns='urn:xtk:sqlSchema'
    xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/'>
    <SOAP-ENV:Body>
      <BuildTableListResponse
        xmlns='urn:xtk:sqlSchema' SOAP-ENV:encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'>
        <pdomTableList xsi:type='ns:Element' SOAP-ENV:encodingStyle='http://xml.apache.org/xml-soap/literalxml'>
          <sqlSchema>
            <table name="public.x"/>
          </sqlSchema>
        </pdomTableList>
      </BuildTableListResponse>
    </SOAP-ENV:Body>
  </SOAP-ENV:Envelope>

Create the schema

Schema creation is a 3-step process

First, create a sql schema by discovering the table structure in the FDA database
Second, modify the schema if necessary and save it as a source schema
Finally, build the final schema

Discover the table structure

We'll call the xtk:builder#GenerateSchema API. It's the API behind the schema creation wizard. This API takes high level parameters and return an XML document which is the future schema. Note that everything happens in memory. At this point, nothing is created in the database.

The rationale is that the discovery may not discover anything, so we return a source schema, give you a chance to modify it before it is persisted.

<SOAP-ENV:Envelope xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:ns='urn:xtk:persist' xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/'>
<SOAP-ENV:Body>

<GenerateSchema xmlns='urn:xtk:queryDef' SOAP-ENV:encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'>
<__sessiontoken xsi:type='xsd:string'></__sessiontoken>

      <param xsi:type='xsi:element' SOAP-ENV:encodingStyle='http://xml.apache.org/xml-soap/literalxml'>
        <tmp type="extView" sqltable="public.x" removePrefix="0" advanced="0">
        </tmp>
      </param>

	    <param xsi:type='xsi:element' SOAP-ENV:encodingStyle='http://xml.apache.org/xml-soap/literalxml'>
        <srcSchema dataSource="nms:extAccount:pg2" img="xtk:schema.png" label="NewAPI" mappingType="sql" name="new2" namespace="cus" xtkschema="xtk:srcSchema">
        </srcSchema>
      </param>

</GenerateSchema>

</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Which returns

<?xml version='1.0'?>
<SOAP-ENV:Envelope
	xmlns:xsd='http://www.w3.org/2001/XMLSchema'
	xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
	xmlns:ns='urn:xtk:builder'
	xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/'>
	<SOAP-ENV:Body>
		<GenerateSchemaResponse
			xmlns='urn:xtk:builder' SOAP-ENV:encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'>
			<pdomSchema xsi:type='ns:Element' SOAP-ENV:encodingStyle='http://xml.apache.org/xml-soap/literalxml'>

      <srcSchema img="xtk:schema.png" label="NewAPI" mappingType="sql" name="new2" namespace="cus" view="true" xtkschema="xtk:srcSchema">
					<element dataSource="nms:extAccount:pg2" label="NewAPI" name="new2" sqltable="public.x">
						<attribute advanced="false" label="i" name="i" sqlname="i" type="long"/>
					</element>
				</srcSchema>

        </pdomSchema>
		</GenerateSchemaResponse>
	</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

As you can see a src schema is returned. In this case, my table only had one column named "i" which is a long.

Create the schema

Let's extract the srcSchema object from the result of the previous API call. We can modify this XML document and, for example, define links, labels, keys, etc. Typically, we'll create at least a key as follows

  <srcSchema img="xtk:schema.png" label="NewAPI" mappingType="sql" name="new2" namespace="cus" view="true" xtkschema="xtk:srcSchema">
  <element dataSource="nms:extAccount:pg2" label="NewAPI" name="new2" sqltable="public.x">
    <key name="pk">
      <keyfield xpath="@i"/>
    </key>
    <attribute advanced="false" label="i" name="i" sqlname="i" type="long"/>
  </element>
</srcSchema>

When happy with the document, it's time to save it and persist the src schema in the database. This can be done with a simple xtk:persist#Write API call.

  <SOAP-ENV:Envelope xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:ns='urn:xtk:persist' xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/'>
    <SOAP-ENV:Body>
    
    <Write xmlns='urn:xtk:persist' SOAP-ENV:encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'>
    <__sessiontoken xsi:type='xsd:string'></__sessiontoken>
    
          <domDoc xsi:type='ns:Element' SOAP-ENV:encodingStyle='http://xml.apache.org/xml-soap/literalxml'>
    
            <srcSchema img="xtk:schema.png" label="NewAPI" mappingType="sql" name="new2" namespace="cus" view="true" xtkschema="xtk:srcSchema">
              <element dataSource="nms:extAccount:pg2" label="NewAPI" name="new2" sqltable="public.x">
                <key name="pk">
                  <keyfield xpath="@i"/>
                </key>
                <attribute advanced="false" label="i" name="i" sqlname="i" type="long"/>
              </element>
            </srcSchema>
    
          </domDoc>
    
    </Write>
    
    
    </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>

This API call does not return anything. But you can check in the database, the schema is there.

Build the schema

The last step is very important and consists of building a xtk:schema from the xtk:srcSchema which has just been created. We can use the xtk:builder#BuildSchemaFromId API call.

  <SOAP-ENV:Envelope xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:ns='urn:xtk:persist' xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/'>
    <SOAP-ENV:Body>
    
    <BuildSchemaFromId xmlns='urn:xtk:builder' SOAP-ENV:encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'>
    <__sessiontoken xsi:type='xsd:string'></__sessiontoken>
    
      <schemaId>cus:new2</schemaId>
    
    </BuildSchemaFromId>
    
    
    </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>

Adobe Campaign JavaScript SDK

Discover and create a FDA schema

Create FDA external account

Encrypt the password

Create the external account

Test the connection

Query the external account

Discover the tables

Create the schema

Discover the table structure

Create the schema

Build the schema