SQL Repository Data Models

SQL Repository Data Models in ATG

•          The ATG Dynamo SQL repository can be used to connect ATG applications to a SQL database

•          The SQL repository is implemented through the atg.adapter.gsa package. (GSA stands for "Generic SQL Adapter")

•          The main Dynamo component in the SQL repository is an instance of the atg.adapter.gsa.GSARepository class, which implements the interfaces atg.repository.MutableRepository and atg.repository.content.ContentRepository and which extends the class atg.repository.RepositoryImpl

•          Whenever you need to create a new SQL repository instance, you should instantiate the atg.adapter.gsa.GSARepository class

Repository setup steps

Step1: Create a Repository Definition File

•          This template is an XML file that defines repository item descriptors and their attributes, and describes the relationship of your SQL repository to the SQL database.  While the SQL repository can represent a variety of data models, it cannot easily represent any arbitrary data model. Thus, it is usually a good idea to design the SQL repository schema before you design your SQL database schema.

Step2: Create a SQL Repository Component

•          This component’s “definitionFiles” property points to the repository definition file. 

Step3: Set up Database Tables

•          Can add tables in the same table space as ATG Platform tables or different

•          ATG provides a tool to create database tables based on repositories:

startSQLRepository –outputSQL

Data Types in Definition file of ATG, SQL data type  and Java object mappings  are shown in below table:

data-type Attribute Value	Java Object Type	Recommended SQL Data Type
string	String	VARCHAR VARCHAR or CLOB (Oracle)
big string	String	LONG VARCHAR or CLOB
date	java.util.Date	DATETIME (Sybase or MS) DATE (DB2 or Oracle)
timestamp	java.sql.Timestamp	DATETIME (Sybase or MS) DATE (Oracle 8i) TIMESTAMP (DB2 or Oracle 9i)
enumerated	String	INTEGER
boolean	Boolean	NUMERIC(1)
int	Integer	INTEGER
byte	Byte	INTEGER
binary	byte[]	LONG VARBINARY (Sybase or MS) LONG RAW or BLOB (Oracle) BLOB (DB2)
short	Short	INTEGER SMALLINT (DB2)
float	Float	FLOAT (DB2, Sybase or MS) NUMBER (Oracle)
double	Double	DOUBLE (DB2, Sybase or MS) NUMBER (Oracle)
long	Long	NUMERIC(19), BIGINT (DB2)

Table Types in ATG Repositories:

An item descriptor in an SQL repository defines a repository item type. It specifies repository item properties and the database tables and columns that store the data of those properties. Item descriptor can have different types of tables, they are:

                 1.    Primary

                 2.    Auxiliary

                 3.    Multi

 

Primary Table:

•          Each item descriptor must have one and only one primary table.

•          The primary table is specified using the type="primary" XML attribute in a <table> tag.

•          The primary table must define a column or columns that store the repository ID.

•          This column is specified using the id-column-names attribute.

                        <table name="user" type="primary" id-column-name="id">

properties...

</table>

•          Use “id-column-names” if the primary keys are more than one.

Auxiliary Table:

•          To specify a relationship of  one to one, we will use auxiliary tables. The Auxiliary table is specified using the type="auxiliary" XML attribute in a <table> tag.

•          Example: Each user has a single address. For the purposes of this example, the user information is stored in a separate table from the user’s address information.

•          The columns in the id-column-names attribute of auxiliary table must be listed in the same order as they are in the id-column-names attribute of the primary table.

         <item-descriptor name="user">

                <table name="dps_user" type="primary" id-column-names="id">

                       <property name="login" data-type="string"/>

                </table>

                <table name="dps_address"  type="auxiliary"  id-column-names="id">

                       <property name="address1"/>

                       <property name="city"/>

                       <property name="state"/>

                       <property name="zip"/>

                </table>

         </item-descriptor>

Multi Table:

•          To specify a relationship of many to one, many to many we will use auxiliary tables.

•          The Multi table is specified using the type="multi" XML attribute in a <table> tag.

•          The multi-column-name attribute ensures that the ordering of the multi-values are maintained. The column specified by the multi-column-name attribute is used for multi-valued properties of data type array, map, and list and is not used for sets (which are unordered). For map type properties, the values in the column specified by the multi-column-name attribute must be a string. For list or array type properties, these values should be an integer or numeric type, and must be sequential.

•          As with auxiliary tables, the ordering of the ID column names is important. The columns specified by this attribute must list table columns in the same order as the id-column-names attribute of the primary table.

•          The <property> tag for a multi-valued property sets the following attributes:

data-type is set to One of the following: array,set,map,list

For example:

<property ... data-type="array" ..

component-data-type is set to A primitive data type such as int, string, etc..

SQL repository does not support references to binary types

For example:

<property name="interests" column-name="interest" data-type="array" component-data-type="string"/>

component-item-type is set to The item descriptor name of the referenced repository items

For example:

<property name="..." column-name="designers"   data-type="array"

 component-item-type="user"/>

By default, null values are not allowed in multi-valued properties. You can specify to allow null values at two levels:

•          Enable all multi-valued properties in a repository to accept null values by setting the repository property allowNullValues to true

•          Allow null values for an individual property by setting its <property> tag attribute allowNullValues to true

In general, auxiliary and multi tables should not have REFERENCES constraints that point to each other. Instead, each of these tables can have a REFERENCES constraint that points to the primary table for the repository item. This limitation exists because the SQL repository processes insert and delete statements for auxiliary and multi tables in the same order. As a result, if you specify REFERENCES constraints between an auxiliary and a multi table or vice versa, a constraint error results on the insert or the delete.

Best Practices

•Define primary key of table as String or Long

•Avoid using multiple primary keys to a table (Not more than 1)

•Incase we need to have composite primary key which has more than 1 key, Add a new id column as part of the primary key

•Identify strong and week relationships and represent accordingly

•Normalize the data model and introduce redundancy where ever is required

•Highly normalized data model kills the performance