NewWindow

From ADempiere ERP Wiki

Jump to: navigation, search

Return to Tutorials

Contents

Tutorial to create a new Table + Window

  • This tutorial teaches you how to create a new Window in the ADempiere application. It works with the Application Dictionary (AD) which resolves the MVC (Model-View-Controller) needs of the table. This allow minimal touching of sourcecode in creating anything new in the system to avoid introducing bugs and ensure low cost maintenance of the system. A later section here will teach you how to extend beyond the normal AD capability.

Creating a new table (DB)

  • Firstly we need to create our new table in the database.
  • The table must contain the ADempiere default columns (italic)
    • This is necessary if you want the Application Dictionary to recognise your table for all its related AD functionality such as AD_Window and AD_PrintFormat.
  • Following is an example SQL script to create a table

CREATE TABLE XX_Material (
 AD_CLIENT_ID   	NUMERIC(10)     NOT NULL,
 AD_ORG_ID      	NUMERIC(10)     NOT NULL,
 ISACTIVE       	CHAR(1)         DEFAULT 'Y' NOT NULL,
 CREATED        	DATE            DEFAULT NOW() NOT NULL,
 CREATEDBY      	NUMERIC(10)     NOT NULL,
 UPDATED        	DATE            DEFAULT NOW() NOT NULL,
 UPDATEDBY      	NUMERIC(10)     NOT NULL,
 XX_MATERIAL_ID     	NUMERIC(10,0) 	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 );

Activating the new table in the AD

Now we have to make that new table in our database appear in the AD.

  1. Login as SystemAdmin in ADempiere
  2. Open Table and Column window
  3. Create a New record
  4. Type into DB Table Name, the name of the newly created DB-table i.e XX_Material.
  5. Click on the button Create Columns from DB.
    1. This creates for each db-tablecolumn a corresponding AD Column.
    2. Check the automatic created Columns, especially the ID references (e.g. Number instead of Integer)

Notes

  • 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.
    • The name of the key column appears to need to be tablename_ID > When adding tables from Adempiere using the "Sync columns" function, this may not be obvious.

(If it is not, inserting records may work great, but updates will give an error: org.postgresql.util.PSQLException: ERROR: column "tablename_id" does not exist; State=42703; ErrorCode=0)

  • 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 (child table to a parent table such as OrderLine to Order), then there will be an ID field of the master record, which should be marked as "parent link column".

Children Tables

  • There may be ways to make exceptions (called "multi-key" in the source), but make your life as a newbie easier, and just follow this pattern.
  • "Multi-key" tables in Adempiere are intended for children tables, which are 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 before. 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.

Understanding the Persistence Layer

This was adapted from a posting by Karsten Thiemann.

  • The persistence layer of ADempiere consists (mainly) of the Persistence Object (PO.java) which is an abstract class and defines methods like load(), save(), delete(), get_ID(), ...
  • Every Persistent Object (otherwise a table in the database) has to extend PO.java in order to get all the persistence functionality (in some cases you don't need this but it never hurts..)
  • To ease the extending, and because of the fact that most needed methods are just set and get methods (the X_* classes are simply POJOs), ADempiere offers a class to generate X_* classes that extend the PO.java for all tables (objects) defined in the application dictionary (AD).
  • This is done with org.adempiere.util.GenerateModel.
    • Previously org.compiere.util.GenerateModel (before ADempiere 3.3.0)
  • Now we generate not only the setter/getter X_* class but also an interface I_* for it to implement.
  • An alternative for generating the X_*/M_* classes is shown here.

That is all what you need to have a full functional (in terms of persistency) Persistent Object.

Generate Model

Now you need to create your Persistence Object for your new table in order to access it for CRUD (Create, Read, Update, Delete) and other modelling functions.

  • Run the class org.adempiere.util.GenerateModel class. It should create a class X_XX_Material.java. Since this is a generated class, you cannot change this class.
  • You can extend it and put some logic in your extension (see example). The name of the extension-class usually starts with an M prefix i.e. MMaterial. If the prefix of the new table has 2 or less letters it is removed. To avoid conflicts with other model classes it is recommended to use a prefix of 3 or more letters. XYZ_Material would result in MXYZMaterial.

See this article for more in depth information on Model classes lookups.

Running Generate Model

Image:EclipseLaunch.jpg

  • 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
  • Also check that you have 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.

See also

You may want to read GenerateModel to get further information.

Extending the model

  • Extending PO classes is usefull when you need to do some kind of validation for a screen or tab. For example of the needs for Localization-Brazil project is that there's a Government Federal Inscription number that has to be validated when the user exits the screen. This inscription number is needed in the organization screen and in the business partner screen.
  • To handle it on organization screen, a table called LBR_Org_Info was created, with the responsibility of extending the AD_Org_Info table.
  • The name of the model (PO class) created by GenerateModel.java is X_LBR_Org_Info. The name for the class that extends that model class has to be stripped of all the underlines, so it looks like MLBROrgInfo, if you do it in a different way, it won't work.
  • For an idea of how ADempiere handles the M* classes, take a look at MTable.java class, method getClass(String).
  • if the first prefix of the table has 2 or less letters it is stripped from the class name. This means that the table names after their prefixes have to be unique because C_BPartner and XY_BPartner would both result in MBPartner PO classes (so XY_SpecialBPartner would be the way to go). To avoid this problem it is recommended to use a prefix of 3 or more letters.

Example 1: the extended model skeleton

package adempiere.model;

import org.compiere.model.X_XX_Material; 

public class MMaterial extends X_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).
To change the display properties (read-only, displayed, mandatory) of some fields see Display properties of columns and fields for more details.

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.

  • Go go "Reference" item in the Application Dictionary Menu
  • 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.

See An Example of Reference Table with Dynamic Validation

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.

TIP: A good table to copy from is M_FreightCategory - it has the 12 most common columns used in Adempiere tables.

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. >> This is a process that can be run. It may Overwrite changes you have made to window tab/field names (if they are centrally maintained and based on dictionary elements).
  • 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
  • Use prefixes of 3 or more letters for customizations (e.g. LBR_) - this is a recommended practice to avoid collisions of classes - Adempiere cut the prefix in the model class when < 3 letters - when >= 3 letters the prefix is preserved (e.g. X_LBR_Org_Info)
  • When naming your columns pay attention to case. The id columns should end in uppercase _ID.
  • Do not use isapproved in custom tables unless you are implementing documents (Order, Invoice etc.)
  • See Virtual_Columns to add columns not included in the underlying table.

See Also

Personal tools