ERP 2.50:Developers Guide/How To Add Columns To a Table
| Languages: |
Contents |
How to add columns to an existing table
Objective
Now we want to extend the table created in the previous section to include a "valid to" column which will store the last date the salary is valid.
At this point there are two different possibilities, the first one is to include this column in the same module that the table was defined in. But it is also possible we want to add this column in a different module, this would be done in case we want to distribute it in a different module because we are not the owners of the main one or just because it makes sense to split it.
Modularity
In this how to we make use of the same module created in the How to create a table section. The important thing to remember is that we will be using the DB_Prefix defined for the module. New columns in existing tables need to be prefixed with EM_ and the DB_Prefix defined for the module.
Create new columns in dictionary
Adding them to the same module than their table
Let's extend the existent HR_SALARY adding a new ValidTo column. As this new column is wanted to be in the same module than the table we don't need to follow any specific naming convention regarding modules.
| Column name | Type | Length | Note |
|---|---|---|---|
| VALIDTO | DATE | Date that this salary is valid to. |
To create the above table within the database, use one of the following ALTER TABLE statements depending on the DB you are using:
PostgreSQL
ALTER TABLE HR_SALARY ADD COLUMN VALIDTO timestamp without time zone;
Oracle
ALTER TABLE HR_SALARY ADD (VALIDTO DATE);
Adding them to a different module than their table
Let's extend the existent HR_SALARY adding a new ValidTo column in a different module. Let's suppose that we already have a module and its DBPrefix is HR2'. In this case the name of the column must be like EM_HR2_VALIDTO, EM stands for External Module, HR2 is the external module's DBPrefix, and finally we add the desired name for the column.
| Column name | Type | Length | Note |
|---|---|---|---|
| EM_HR2_VALIDTO | DATE | Date that this salary is valid to. |
To create the above table within the database, use one of the following ALTER TABLE statements depending on the DB you are using:
PostgreSQL
ALTER TABLE HR_SALARY ADD COLUMN EM_HR2_VALIDTO timestamp without time zone;
Oracle
ALTER TABLE HR_SALARY ADD (EM_HR2_VALIDTO DATE);
Registering the column within the Application Dictionary
The following steps register the newly created column within the Openbravo ERP Application Dictionary.
For this purpose, first log into Openbravo ERP using a username with access to System Administrator role. Navigate to Application Dictionary || Tables and Columns and search the table you want to add the new column to, in this case HR_SALARY. Now press Create columns from DB button to create the columns that exist in database but do not exist in Application Dictionary yet, it will add a new entry in the Column tab.
Now run Synchronize Terminology process (Application Dictionary || Synchronize Terminology) to set elements to the new columns.
Configuring a column
Now we have our new column in Application Dictionary, we can switch to the Column tab to see it.
The Create columns from DB process tries to populate all the fields according with the information in database, but in some occasions it is required to manually update these generated values.
Let's review the most important fields in this tab (for more information see the AD_Column table description):
- Module
- It is the module the column belongs to. By default it is the same one as the table that contains the column is, but in case the column starts with EM followed by an existent DBPrefix that module is taken as the column's one.
- Name
- the name is used by the Data Access Layer and in REST webservices. For specific columns (audit info, client/organization, active) it is important to be precise in the naming. See here for more information.
- DB Column Name
- It is the physical name for the column in database. It is a good practice to use lower case in this name and distinguish different words using capital letters. In the VALIDTO example the name would be ValidTo.
- Application Element
- The element linked to this column. For for information about elements read the element section of the Developers Guide.
- Description and Help/Comment
- This fields are directly taken from the element when Synchronize terminology is executed, so in case they need modified edit the element instead of these fields directly.
- Length
- Maximum size of the data to be stored in this column.
- Reference, Validation, Min. Value, Max. Value, Reference Search Key and Process
- Reference define the type of data the column stores and it also determines the way it will be displayed in the UI. The rest of fields here are displayed depending on the reference type. For further explanations on references and the other fields read the reference section in the Developers Guide.
Notice that the tabledir reference can not be used in a new column added to a table of core. Instead of tabledir you should use the table reference.
- Default Value
- The default value the column will take. It can be a static value for example: Y, N; a session variable which is marked surrounding the variable name with @, for example @AD_Org_ID@ default value will set the current organization; or a SQL clause to define it the default value looks like @SQL=SELECT VALUE FROM TABLE.
- isParent
- The isParent column is used to flag the column has being a reference to the parent in a parent-child relation. It defines business object structures in Openbravo. For example the C_OrderLine table has a column c_order_id (refering to the C_Order table) with isParent flagged. This means that the Order business object consists of the Order header (the parent, the C_Order table itself) and also the OrderLine (the child, the C_OrderLine table). See here for more details on how business object structures are defined.
| Languages: |
ERP 2.50:Developers Guide/How To Create a Table | ERP 2.50:Developers Guide/How To Add a Constraint