NewWindow

From ADempiere ERP Wiki
Revision as of 09:44, 16 April 2007 by Kthiemann (talk | contribs) (new window as zoom target)
Jump to navigationJump to search

Return to Tutorials

Tutorial to create a new Table + Window

Create new table (DB)

Create a new table via SQL in the database. The table must contain the ADempiere default columns (italic) if you want to create an ADempiere window for the table. Example:

CREATE TABLE XX_Material (
 AD_CLIENT_ID   	NUMBER(10)      NOT NULL,
 AD_ORG_ID      	NUMBER(10)     	NOT NULL,
 ISACTIVE       	CHAR(1 BYTE)    DEFAULT 'Y'          NOT NULL,
 CREATED        	DATE            DEFAULT SYSDATE      NOT NULL,
 CREATEDBY      	NUMBER(10)      NOT NULL,
 UPDATED        	DATE            DEFAULT SYSDATE      NOT NULL,
 UPDATEDBY      	NUMBER(10)      NOT NULL,
 XX_MATERIAL_ID     	INTEGER 	NOT NULL,
 MATNR     		SMALLINT 	NOT NULL,
 COLORNR    		SMALLINT 	NOT NULL,
 NAME      		VARCHAR(100)	NOT NULL,
 NAMESHORT  		VARCHAR(100)	NOT NULL
);
ALTER TABLE XX_Material ADD PRIMARY KEY (XX_MATERIAL_ID );

Create new table (Adempiere)

Create a new entry in the window Table and Column and set for DB Table Name the name of the new created DB-table (see step 1) (XX_Material).
Press Button Create Columns from DB. That creates for each db-tablecolumn a corresponding compiere Column.
Check the automatic created Columns, especially the references (e.g. Number instead of Integer)

You need to be aware of the standard way Adempiere persists records. Every record is expected to have a an ID column whose name ends in _ID, type numeric(10,0). This column should be marked as "key column" in the Table Column record. Adempiere uses this like an object id. This is what it stores to track which records have been modified by the user, etc. If this is a detail file, then there will be an ID field of the master record, which should be marked as "parent link column".

There may be ways to make exceptions (called "multi-key" in the source, I think), but make your life as a newbie easier, and just follow this pattern.

Children tables

"Multi-key" tables in Adempiere are intended for children tables, this is, tables that depends on several parent tables (n-n relations in ER-model).

Multi-key tables don't need a unique "key column" as explained in previous paragraph. They need to reference keys (_ID's) from parent, and every parent key must be marked as "parent link column".

Please take notice that Adempiere creates the primary/unique constraint for key column, but you need to create manually the multi-key primary constraint (as well as all foreign keys).

Issues with multi-key tables: Due to the fact that multi-key tables doesn't have a key column, all functionality based on key column won't work, i.e. chat, attachments, record lock, reference to record table in requests, etc.

Generate Model

Use the class org.compiere.util.GenerateModel to generate a PO-class for new created tables. It should create a class X_XX_Material.java. Don't change this class, but you can extend it and put some logic in your extension (see example). The name of the extension-class has to be MMaterial.

Running Generate Model

In the source, there are Eclipse .launch files. But these were created with Eclipse in the many small projects mode. If you checked out one big project, then the launchers won't show up in your list. [Make them visible], then edit to change the project name and source paths passed.

When running from the command line, the arguments are

  1. Output source path
  2. output package
  3. table type, e.g. 'U' for User Defined
  4. (optional) SQL pattern to select table

In addition, you need to add the jars in lib/ to the classpath for GenerateModel. Eclipse seems to do this automatically, but from the command line you need a wrapper script.

Extending the model

Example 1: the extended model skeleton

package adempiere.model;

import org.compiere.model.XX_Material; 

public class MMaterial extends XX_Material {
	/**
	 *  Create & Load existing Persistent Object
	 *  @param ctx context
	 *  @param XX_Material_ID  The unique ID of the object
	 *  @param trxName transaction name
	 */
	public MMaterial(Properties ctx, int XX_Material_ID, String trxName) {
		super(ctx, XX_Material_ID, trxName);
		// other initializations here
	}
	
	/**
	 *  Create & Load existing Persistent Object.
	 *  @param ctx context
	 *  @param rs optional - load from current result set position (no navigation, not closed)
	 *  	if null, a new record is created.
	 *  @param trxName transaction name
	 */
	public MMaterial(Properties ctx, ResultSet rs, String trxName) {
		super(ctx, rs, trxName);
		// other initializations here
	}
	
	/**
	 *  [OPTIONAL] Called when the object load is complete
	 *  @param success success
	 */
	protected void loadComplete (boolean success)
	{
	}

	/**
	 * [OPTIONAL] Called before Save for Pre-Save Operation.
	 * @param newRecord new record
	 * @return true if record can be saved
	 */
	protected boolean beforeSave(boolean newRecord)
	{
		return true;
	}

	/**
	 * [OPTIONAL] Called after Save for Post-Save Operation
	 * @param newRecord new record
	 * @param success true if save operation was success
	 * @return if save was a success
	 */
	protected boolean afterSave (boolean newRecord, boolean success)
	{
		return success;
	}

	/**
	 * [OPTIONAL] Executed before Delete operation.
	 * @return true if record can be deleted
	 */
	protected boolean beforeDelete ()
	{
		return true;
	}
	
	/**
	 * [OPTIONAL] Executed after Delete operation.
	 * @param success true if record deleted
	 * @return true if delete is a success
	 */
	protected boolean afterDelete (boolean success)
	{
		return success;
	}
	
	/**
	 *  [OPTIONAL] A nice string representation for your object
	 *  @return String representation
	 */
	public String toString()
	{
		return super.toString();
	}
}

Example 2: a method to get a MMaterial-Object with the given materialno and colorno.

public static MMaterial get(Properties ctx, int materialno, int colorno) {
    MMaterial retValue = null;
    String sql = "SELECT * FROM XX_Material WHERE MATNR=? AND COLORNR=?";
    PreparedStatement pstmt = null;
       try {
            pstmt = DB.prepareStatement (sql, null);
            pstmt.setInt(1, materialno);
            pstmt.setInt(2, colorno);
            final ResultSet rs = pstmt.executeQuery ();
            if (rs.next ()){
               retValue = new MMaterial(ctx, rs, null);
            }
            rs.close ();
            pstmt.close ();
            pstmt = null;
       } catch (SQLException e){
            s_log.log(Level.SEVERE, sql, e);
       } 
       try {
            if (pstmt != null){
               pstmt.close ();
            }
            pstmt = null;
       } catch (SQLException e)	{
            pstmt = null;
       }
       return retValue;
}

Example 3: Adding caching support to optimize your previous get method.

/** Cache: MaterialNo_ColorNo -> MMaterial, with initialCapacity=10 and expireMinutes=default value */
private static CCache<String, MMaterial> s_cache = new CCache<String, MMaterial>("XX_Material", 10);

/**
 * Gets the MMaterial for materialno and colorno.
 * The objects are cached.
 * @param ctx context
 * @param materialno
 * @param colorno
 * @return MMaterial or null if not found
 */
public static MMaterial get(Properties ctx, int materialno, int colorno) {
    // check cache
    String key = "" + materialno + "_" + colorno;
    MMaterial retValue = s_cache.get(key);
    if (retValue != null)
       return retValue;
    
    String sql = "SELECT * FROM XX_Material WHERE MATNR=? AND COLORNR=?";
    PreparedStatement pstmt = null;
       try {
            // Create the prepared statement
            // Note: when you are querying objects that will be cached it is recommended 
            //       to query "outside transaction" (trxName=null), 
            //       That's why this kind of static getters does not have trxName parameter
            pstmt = DB.prepareStatement (sql, null);
            // Fill the parameters
            pstmt.setInt(1, materialno);
            pstmt.setInt(2, colorno);
            // Execute the query
            final ResultSet rs = pstmt.executeQuery ();
            // Get the first result if exist
            if (rs.next()){
               retValue = new MMaterial(ctx, rs, null);
            }
            // To protect the integrity of your cache check if you get more than one result for your query.
            // This situation should be avoided !
            // Another quick way to avoid this is using database unique constraints.
            if (rs.next()) {
               // TODO: do something here: log a warning, throw an exception, set retValue = null etc
            }
            // Don't forget to close the ResultSet and the PreparedStatement
            rs.close ();
            pstmt.close ();
            pstmt = null;
       } catch (SQLException e) {
            // handle the SQLException
            s_log.log(Level.SEVERE, sql, e);
       }
       // Don't forget to close the PreparedStatement (again!)
       // This situation happens when an exception was thrown in the previous try/catch block
       try {
            if (pstmt != null){
               pstmt.close ();
            }
            pstmt = null;
       } catch (SQLException e)	{
            pstmt = null;
       }
       
       // Add the result (if any) to cache
       if (retValue != null)
            s_cache.put(key, retValue);
       
       // Finally we are returning the resulting MMaterial object
       return retValue;
}

Create an ADempiere window

Create a new entry in the window Window Tab & Field with the name „XX_Material“. In the tab Tab create a new entry with the name „Material“ and select „XX_Material_XX_Material“ as it's table.
Use the button Create Fields to create fields for all columns of the table. In the tab Field Sequence you can choose the sequence of the fields in the new window and in the tab Field you can select some view-related attributes. Set the read-only flag for „XX_Material_ID“ (the id should be unchangeble).

The Window type should be "Maintain". If the type is "Transaction", you'll need a "processed" field in the table.

Create a Reference and Zoom Target

If you want to have a drop-down-box with values of the new table in other windows (like the drop-downs for bpartner-locations...) you need to create a Reference for the new table. Create a new entry in the window Reference with the name „XX_Material“ and select „Table Validation“ as Validation Type.
In the tab Table Validation select „XX_Material_XX_Material“ as table, „XX_Material_ID as Key Column and „Name“ as Display Column. The last one selects the column, that is used for the drop-down values. Remark: Close all windows (Reference and Window Tab & field) before you proceed. ADempiere does not update the selectable references if you don't...
Now you can create a field with the name „Material“ in a ADempiere window of your choice and select „XX_Material_ID_Material“ as it's Column. After this, you will see a drop-down-box with all values of the name-column in that window.
To let the user zoom to your new window from the dropdown (rightclick and select zoom) you have to go back to the window 'Table and Column' and select your new table. From the 'window' drop down field select the new created window „XX_Material“. You may want to have a different window for purchase transactions (see table C_Order). That's it - if the zoom doesn't work you have to clear the cache or restart the client.

You are done!

New Quick Functionality

Now, you don't need to know SQL, or have a SQL tool to create a new table.

In 3.1.5 was added a functionality to "Copy Columns From Other Table" in Table window.

Then you can replace the first two steps by:

  • Create new table (Adempiere)
  • Choose a similar table and run the process "Copy Columns From Table" with the chosen table
  • Navigate to columns tab and make the necessary modifications
  • Push "Synchronize columns" button and the table will be created in database

It's good that in a later phase you optimize the database adding the corresponding foreign keys.


Tips

  • The creating Element is Mandatory: You can use Elements from owned or ADempiere Dictionary.
  • The Menu Name is derived from the Window Name.
  • The Field info is derived from Element.
  • Synchronize Terminology.
  • What about Explicit and Implicit Parents?
    • If the Child Table has one Parent: Link automatically; no action is required.
    • If the Child Table has two or no Parents: Explicit Link is required (Tab: Link Column)
  • If you are not going to need beforeSave and afterSave methods generating model is not necessary.
    • Some things don't work without the generated model X_ class, like change log