Sunday 28 February 2016

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



ATG SQL Repository API



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
 

 

0 comments:

Post a Comment

 
;