View source for Aperi Database R2 Extensibility

=== Introduction ===

This page provides information about how make a change to the Aperi database. The sections within this page are organized as a series of HOWTOs to help you get started.

It is recommended that you become familiar with [http://www.eclipse.org/aperi/tech/Aperi_Database_Component_Design.doc Database Component Design (MS Word)] document before making changes to the database. 

=== How do you coordinate a database schema change? ===

When planning a change to the database, you (the requester) should perform the following steps:

* Analyze the application requirements
* Draft the database changes on schema, helper, mapper, detectability with the help of the entire design team
* Analyze the impact of the changes on the whole of Aperi

The change request will be reviewed by the design team (led by the database team lead) and approved by a set deadline. If needed, the team might request consultation from subject matter experts.

Once approved, the requester is responsible for:

* Implementating a change
* Resolving any resource issues that might occur
* Testing the new schema file(s) by using them to replace the corresponding files in a proper version of Aperi
* Making an overall build with all the changes to ensure nothing breaks 
* Sending out a note to the aperi-dev mailing list highlighting the change

=== How do you analyze the impact of a database schema change? ===

Use the infomation in this section to help determine if changes you make to the database schema that will impact Aperi.

The following change will typically not have an impact on Aperi:

* Add a table, view, column, index (including primary key). Note that a new name for any of these database elemtns might conflict with existing ones. For example, the table name T_TSM_SERVER conflicts with T_RES_SERVER. See the Helper documentation in the [http://www.eclipse.org/aperi/tech/Aperi_Database_Component_Design.doc Database Component Design (MS Word)] for more information. 

The following changes might have an impact on Aperi:

* Delete a table, view, column 
* Change the data type of a column 
* Change the encoding of data 
* Delete a primary key, index 
* Change table data population 

Notes:

* Changes to the data type of a column are not recommended. 
* You might need to regenerate a dependent view if a table definition is changed.

=== How do I determine which schema file to change?  ===

Aperi's database schema files (*.sql) use the following the naming convention:

<pre>
AlterVersionPVRRMMSS.sql, where
            PP = 00 for Aperi, others for individual vendor //to allow for multiple vendors, change from the TPC version which had only one digit..
            V = Version of product (1 digit)
            RR = Release of product (2 digits)
            MM = Modification of product (2 digits)
            SS = Sub-modification of product (2 digits)
</pre>

This naming convention enables the Aperi project to make a rolling set of changes. In this way, any version of Aperi can be upgraded to the next version by applying the right set of schema version files. 

The order of processing the schema files will be as follows: 

# Aperi schema files will be applied in increasing order of version, release, and modification. 
# Vendor extensions will be applied in increasing order of version, release and modification. 

The first version of Aperi is 5. Based on that, the schema file for the initial contribution was AlterVersion005000000.sql; for the R2 contribution, the schema file was AlterVersion005000002.sql. 

Each release of Aperi will have its own schema file. Note that it is very important not to update a schema file from an older release. If you do not know the schema file for a release, please send an email to the aperi-dev mailing list to request that information.

=== How do you change the schema file? ===

When adding a table definition to a schema file or making a change, please keep the following in mind: 

* Use vendor-neutral SQL files in the install. 
* Terminate each SQL statement by a ; on a new line. 
* Start comments starts with #. 
* Represent data types in a vendor-neutral fashion. While CHAR and VARCHAR are the same for all vendors, we use vendor-neutral notations for most others. In the case of DB2: {0} = integer, {1} = smallint, {3} = timestamp. See the top of the schema file for details. 
* Please add comments for your column definitions. 
* Update foreign key constraints in the file, but leave them commented out because we do not support foreign keys yet. However, note that foreign key constraint information is necessary to generate the ER diagram for visualization. 
* Do not specify schema name. 
* Add a blank for each line not ending with a separator because the installer removes new lines. 
* Append the schema change to the change log as seen below: 

<pre>
# Change Log:
# Date            Bug       Author          Description
# -------------------------------------------------------------------------------------------------
# Mar 20, 2006    28087     mlamb   	Change the default Out Of Band Fabric discovery time
…..
# May 12, 2006    28594     fiebrich      	 update T_RES_CONFIG_DATA with extended indication filter list
# May 15, 2006    28697     hwassman      Add the view T_VIEW_COMPUTER_ATTRS
# May 16, 2006    28728     ashmic	  Modified T_VIEW_COMPUTER_ATTRS: 
#			  	 Added " " (space) between NODE_HOSTED_ID and FROM
</pre>

Below is an example of a sample table definition to be added to a schema file.

<pre>
###############################################################
## TABLE: T_RES_SWITCH_ATTRIBUTE_SNAPSHOT
## Entity representing historical snapshot for T_RES_SWITCH_ATTRIBUTE
## FOREIGN KEYS: SWITCH_WWN
###############################################################
CREATE TABLE T_RES_SWITCH_ATTRIBUTE_SNAPSHOT
(SWITCH_WWN          CHAR(16) NOT NULL, # WWN of switch
 VERSION             VARCHAR(255),      # Version of the switch
 DETECTABLE          {1},               # Switch detectable or not
 OPERATIONAL_STATUS  {0}) {121}         # Switch operational status
; 

ALTER TABLE T_RES_SWITCH_ATTRIBUTE_SNAPSHOT ADD PRIMARY KEY (SWITCH_WWN)
;

# ALTER TABLE T_RES_SWITCH_ATTRIBUTE_SNAPSHOT ADD FOREIGN KEY
# FK_SWITCH_WWN FOREIGN KEY (SWITCH_WWN) REFERENCES T_RES_SWITCH 
# (SWITCH_WWN)
</pre>

=== How do you generate helpers? ===

A database table/view (for example, T_RES_SERVER) has 3 helper fiiles
* tables/TResServerTable.java  : abstract class
* objects/Server.java  : supporting retrieve, insert, delete, update
* sql/SqlServer.java : for sql statements (generate only once when the table /view is added)

Typically, the first two segments of the table name are removed for object and SQL file names, unless the table is specified in the LegacyTables parameter of file Sample.cfg. 

Note: Avoid helper name conflict to avoid unnecessary complexity. For example, use '''T_RES_''' for consistency and check for possible conflict between  T_VIEW_ *   and T_RES_ *�

All new table/views and most other changes require that you generate new helpers. Always generate new helpers for tables/views, then compare with them existing helpers. If they are different, check them in.

Use the following steps as guidelines when generating helpers:
#Go to the following directory: org.eclipse.aperi.common/src
#Note that in that "Root" directory in sample.cfg (normally Root=c:\\temp), you will have to create three directories objects, SQL, and tables.
#Issue the following command from wksp:org.eclipse.aperi.common: java org.eclipse.aperi.build.DbGenerateClasses ../Aperi/device/build/sample.cfg  TABLE1 TABLE2 TABLE3 ….
Note: The table names must be in upper case in the previous command invocation

An example sample.cfg is shown below:

<pre>
#configuration file for DbGenerateClasses and Member apps
#the JDBC Driver to use
Driver=org.apache.derby.jdbc.ClientDriver
#the url of the database to use
DbUrl=jdbc:derby://localhost:1527/Aperi
#database user name
User=Aperi
#password for user
Password=Aperi
#schema for tables, must be in UPPERCASE
Schema=APERI    
#Normalized fields
NormalFields=VENDOR_ID MODEL_ID CREATION_CLASS_NAME_ID SYSTEM_CREATION_CLASS_NAME_ID CLASS_NAME_ID SYSTEM_NAMES_ID
#Tables that have normalized fields
NormalTables=T_RES_VENDOR T_RES_MODEL T_RES_CREATION_CLASS_NAME T_RES_SYSTEM_NAMES
#Tables that are mapped to objects using just first segment
#Normal: T_RES_ZONE maps to Zone ; Legacy: T_STAT_ZONE maps to StatZone
LegacyTables=T_AGGREGATOR T_ALERT_DEFINITION T_ALERT_EMAIL T_ALERT_LOG T_BACKUP_ACTION T_BATCH_REPORT T_CB_SETTINGS T_CHART_SETTINGS T_CONFIG_SETTINGS T_EXTENSION_POLICY T_FILE_TYPE T_FOUND_COMPUTER T_FOUND_FILESYSTEM T_GROUP T_GROUP2MASK T_GROUP2RES T_IDENTIFIER T_KEYS T_QUOTA_DEFINITION T_REPORT_PREFS T_RUN_JOBS T_RUNS T_SAVED_REPORT T_SCHEDULE T_SCRIPT T_SNMP_COMMUNITY T_STAT_COMPUTER T_STAT_DEFINITION T_STAT_DIRECTORY T_STORM_SETTINGS T_UPGRADE_LOG T_USER_PREFERENCES T_VIEW_GROUP2RES T_PRF_TIMESTAMP T_PRF_SWITCH T_VIEW_SWITCH2PORT T_VIEW_PORT2PORT T_VIEW_ZONE2MEMBER
#Root: where the output is placed
Root=c:\\temp
</pre>