Difference between pages "NewWindow" and "File:TTH BuchführungsdimensionenTabDimensionGeschäftspartner 360LTS.png"

From ADempiere
(Difference between pages)
Jump to: navigation, search
This Wiki is read-only for reference purposes to avoid broken links.
(Tips)
 
 
Line 1: Line 1:
[[Tutorials|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:
 
<code>
 
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 );
 
</code>
 
== 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).<br>
 
Press Button Create Columns from DB. That creates for each db-tablecolumn a corresponding compiere Column.<br>
 
Check the automatic created Columns, especially the references (e.g. Number instead of Integer)<br>
 
 
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.
 
 
== Start from the beginning ==
 
A very good introduction by [[User:Kthiemann|Karsten Thiemann]]
 
 
The persistance layer of ADempiere consists (mainly) of the ''PO.java'' which is an abstract class and defines methods like load(), save(), delete(), get_ID(), ...
 
 
Every persistent object (a table in the database) has to extend PO.java in order to get all the persistance 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.compiere.util.GenerateModel'' (before ADempiere 3.3.0) or now with ''org.adempiere.util.GenerateModel''.
 
 
Now we generate not only a X_* class but also an interface ''I_*'' which is implemented by the X_* class
 
.
 
That is all what you need to have a full functional (in terms of persistency) persistent
 
object.
 
 
(The complete thread there: http://sourceforge.net/forum/forum.php?thread_id=1835133&forum_id=610546).
 
 
== 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. ( The class org.compiere.util.GenerateModel is rename to org.adempiere.util.GenerateModel starting from version 3.3 and above. )
 
 
=== 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.  [[http://dev.eclipse.org/newslists/news.eclipse.newcomer/msg14778.html Make them visible]], then edit to change the project name and source paths passed.
 
 
When running from the command line, the arguments are
 
# Output source path
 
# output package
 
# table type, e.g. 'U' for User Defined
 
# (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 ==
 
 
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 is leaving 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 crated, with the responsability of extending the AD_Org_Info table.<br>
 
The name of the model 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 wont work. In case you want to learn more about how does ADempiere handles the M* classes, take a look at MTable.java class, method getClass(String).<br>
 
This implies 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).
 
 
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.<br>
 
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).<br>
 
 
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.<br>
 
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...<br>
 
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.<br>
 
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
 
* 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)
 
* Virtual column
 
** [https://sourceforge.net/forum/message.php?msg_id=4682365 sf.net message]
 
** Not necessary to include WHERE clause as part of Virtual column SQL statement.
 
<pre>
 
SELECT IsActive, XX_Material_ID, MatNr, ColorNr, Name
 
  , (COLORNR + MATNR) AS T_Integer
 
  , AD_Client_ID, AD_Org_ID, Created, CreatedBy, Updated, UpdatedBy
 
 
FROM XX_Material
 
 
WHERE XX_Material.AD_Client_ID IN (0,1000000)
 
  AND XX_Material.AD_Org_ID IN(0,1000000)
 
  AND XX_Material.XX_Material_ID NOT IN ( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 1000007
 
  AND AD_User_ID <> 100
 
  AND IsActive = 'Y' )
 
</pre>
 
It's a SELECT [all fields comma seperated] from TABLENAME WHERE [Client/Org][PrivateAccess]. If we have a virtual column the SQL statement (in this case (COLORNR + MATNR)) is just included in the comma seperated field list. 
 
 
 
[[Category:Developer documentation]] [[Category:HowTo]]
 

Latest revision as of 05:16, 14 April 2012