We did some work in this area:
The issues were the following
1. If you pass a string that is larger than type of db field, transaction
crashes
2. UI forms are not necessary same as db entities. UI forms are persisted
through services db entities or services. It is hard to tie UI forms with
actual persistence to ensure safety of (1). Best approach may be to have
conservative defaults.
3. For text fields and text areas UI should display the max size and prevent
additional entry.
4. Concrete DB Info is based on db type as per fieldtype(dbtype).xml
There were a couple of realizations.. Ofbiz data dictionary is nicely done. A
field usually means the same thing and has same dimension if present in
multiple tables. e.g. partyId is same (maxsize 20) and so is comments(maxsize
255). There are very few anomalies. These were
# field to list of 'entity.size', if size varies per entity
entitysize.accountNumber=EftAccount:255,PartyCarrierAccount:20,PartyGroup:100,PayrollPreference:60
entitysize.cardNumber=CreditCard:255,GiftCard:255,GiftCardFulfillment:255,ValueLinkFulfillment:60
entitysize.city=PostalAddress:100,ZipSalesRuleLookup:60,ZipSalesTaxLookup:60
entitysize.contentId=CommEventContentAssoc:20,Content:20,ContentApproval:20,ContentAssoc:20,ContentAttribute:20,ContentMetaData:20,ContentPurpose:20,ContentRevision:20,ContentRevisionItem:20,ContentRole:20,CustRequestContent:20,OrderContent:20,PartyContent:20,PartyResume:20,ProdConfItemContent:20,ProductCategoryContent:20,ProductContent:20,ServerHit:255,ServerHitBin:255,SubscriptionResource:20,SurveyResponseAnswer:20,WebPage:20,WebSiteContent:20,WebSitePathAlias:20,WebSitePublishPoint:20,WorkEffortContent:20
entitysize.countryCode=CountryCapital:20,CountryCode:20,CountryTeleCode:20,TelecomNumber:10
entitysize.description=AccommodationClass:255,AccommodationMapType:255,AccommodationSpot:255,AcctgTrans:255,AcctgTransEntry:255,AcctgTransEntryType:255,AcctgTransType:255,Agreement:255,AgreementItemType:255,AgreementTerm:255,AgreementType:255,BenefitType:255,BillingAccount:255,BudgetItemType:255,BudgetReviewResultType:255,BudgetScenario:255,BudgetType:255,CharacterSet:255,CommContentAssocType:255,CommunicationEventPrpTyp:255,CommunicationEventPurpose:255,CommunicationEventType:255,CommunicationEventWorkEff:255,ConfigOptionProductOption:255,ContactList:255,ContactListType:255,ContactMechPurposeType:255,ContactMechType:255,Container:255,ContainerType:255,Content:255,ContentAssocPredicate:255,ContentAssocType:255,ContentOperation:255,ContentPurposeType:255,ContentType:255,CostComponentCalc:255,CostComponentType:255,CurrencyDimension:255,CustRequest:255,CustRequestCategory:255,CustRequestItem:255,CustRequestResolution:255,CustRequestType:255,CustomMethod:255,CustomMethodType:255,DataResourceType:255,DataSource:255,DataSourceType:255,DataTemplateType:255,DateDimension:255,DeductionType:255,Deliverable:255,DeliverableType:255,DepreciationMethod:255,DocumentType:255,EmplLeave:255,EmplLeaveType:255,EmplPositionClassType:255,EmplPositionType:255,EmploymentAppSourceType:255,Enumeration:255,EnumerationType:255,Example:255,ExampleFeature:255,ExampleFeatureApplType:255,ExampleItem:255,ExampleType:255,Facility:255,FacilityGroup:255,FacilityGroupType:255,FacilityType:255,FinAccountTransType:255,FinAccountType:255,FixedAssetIdentType:255,FixedAssetProductType:255,FixedAssetStdCostType:255,FixedAssetType:255,GeoAssocType:255,GeoType:255,GlAccount:255,GlAccountClass:255,GlAccountGroup:255,GlAccountGroupType:255,GlAccountType:255,GlFiscalType:255,GlReconciliation:255,GlResourceType:255,GlXbrlClass:255,GoodIdentificationType:255,InventoryItemDetail:255,InventoryItemLabel:255,InventoryItemLabelType:255,InventoryItemType:255,Invoice:255,InvoiceItem:255,InvoiceItemType:255,InvoiceTerm:255,InvoiceType:255,MetaDataPredicate:255,MimeType:255,MrpEventType:255,NeedType:255,OagisMessageErrorInfo:2147483647,OrderAdjustment:255,OrderAdjustmentType:255,OrderBlacklistType:255,OrderContentType:255,OrderItemAssocType:255,OrderItemPriceInfo:255,OrderItemType:255,OrderTerm:255,OrderType:255,Party:2147483647,PartyClassification:255,PartyClassificationGroup:255,PartyClassificationType:255,PartyContentType:255,PartyNeed:255,PartyQualType:255,PartyRelationshipType:255,PartyTimeTracker:60,PartyType:255,PaymentMethod:255,PaymentMethodType:255,PaymentType:255,PerfRatingType:255,PerfReviewItemType:255,PeriodType:255,Picklist:255,PortalPage:255,PortalPortlet:255,PriorityType:255,ProdCatalogCategoryType:255,ProdConfItemContentType:255,Product:255,ProductAssocType:255,ProductCategory:255,ProductCategoryContentType:255,ProductCategoryType:255,ProductConfig:255,ProductConfigConfig:255,ProductConfigItem:255,ProductConfigOption:255,ProductConfigOptionIactn:255,ProductContentType:255,ProductEnumerationType:255,ProductFeature:255,ProductFeatureApplType:255,ProductFeatureCategory:255,ProductFeatureGroup:255,ProductFeatureIactnType:255,ProductFeatureType:255,ProductGeo:255,ProductMaintType:255,ProductManufacturingRule:255,ProductMeterType:255,ProductPriceActionType:255,ProductPricePurpose:255,ProductPriceRule:255,ProductPriceType:255,ProductStoreGroup:255,ProductStoreGroupType:255,ProductTag:100,ProductTagFieldAttribute:100,ProductTagStyle:100,ProductType:255,QuantityBreakType:255,Quote:255,QuoteAdjustment:255,QuoteType:255,RateType:255,RejectionReason:255,Requirement:255,RequirementType:255,ResponsibilityType:255,ReturnAdjustment:255,ReturnAdjustmentType:255,ReturnHeaderType:255,ReturnItem:255,ReturnItemType:255,ReturnReason:255,ReturnType:255,RoleType:255,SaleType:255,SalesOpportunity:2147483647,SalesOpportunityHistory:2147483647,SalesOpportunityStage:255,SecurityGroup:255,SecurityPermission:255,SegmentGroup:255,SegmentGroupType:255,ServerHitType:255,ShipmentBoxType:255,ShipmentContactMechType:255,ShipmentMethodType:255,ShipmentType:255,ShippingDocument:255,ShoppingList:255,ShoppingListType:255,SkillType:255,StatusItem:255,StatusType:255,Subscription:255,SubscriptionResource:255,SubscriptionType:255,SupplierPrefOrder:255,SupplierProductFeature:100,SupplierRatingType:255,Survey:255,SurveyApplType:255,SurveyQuestion:255,SurveyQuestionCategory:255,SurveyQuestionOption:255,SurveyQuestionType:255,Tag:100,TagFieldAttribute:100,TagStyle:100,TaxAuthorityAssocType:255,TaxAuthorityRateProduct:255,TaxAuthorityRateType:255,TechDataCalendar:255,TechDataCalendarExcDay:255,TechDataCalendarExcWeek:255,TechDataCalendarWeek:255,TermType:255,TerminationReason:255,TerminationType:255,Testing:255,TestingNode:255,TestingType:255,TrackingCode:255,TrackingCodeType:255,TrainingClassType:255,UnemploymentClaim:255,Uom:255,UomType:255,UserAgentMethodType:255,UserAgentType:255,UserPrefGroupType:255,VarianceReason:255,VisualTheme:255,WebPreferenceType:255,WebSiteContentType:255,WorkEffort:255,WorkEffortAssocType:255,WorkEffortContentType:255,WorkEffortGoodStandardType:255,WorkEffortPurposeType:255,WorkEffortType:255,WorkReqFulfType:255
entitysize.extension=DataTemplateType:60,FacilityContactMech:10,PartyContactMech:255
entitysize.idCode=ProductFeature:20,SupplierProductFeature:20,ZipSalesRuleLookup:60
entitysize.idValue=FixedAssetIdent:255,GoodIdentification:60
entitysize.mapKey=AddressMatchMap:255,ContentAssoc:100,WebSitePathAlias:100
entitysize.pinNumber=GiftCard:255,GiftCardFulfillment:255,ValueLinkFulfillment:60
entitysize.referenceId=OagisMessageErrorInfo:60,OagisMessageInfo:60,SurveyResponse:255
entitysize.serviceName=Content:255,JobSandbox:100,ProductStoreShipmentMeth:255,ServiceSemaphore:100
entitysize.sessionId=CatalinaSession:60,Visit:255
entitysize.taxable=Product:1,ZipSalesRuleLookup:60
entitysize.title=PartyQual:60,ProductStore:100,ProductTag:100,ProductTagFieldAttribute:100,ProductTagStyle:100
entitysize.transactionId=OrderHeader:60,PosTerminalLog:20
Here is what we did.
- Crawled our target database postgres to build columnid to maxsize mapping for
all entities and put it into a properties file. From our ofbiz fork late last
year there were 1400 column names with with unambiguous maxsize and about 17
ambiguous columns size (as mentioned above). For ambiguous column names we
assumed the max size was lower of the possible values. e.g. accountNumber has
maxsize 60(from PayrollPreference not EftAccount)
- Our UI forms could use default column names and not care about db entities,
services perisistence etc.
So there if UI had these 3 columns.
<field name="partyId"><text ...></field>
<field name="comments"><textarea ...></field>
<field name="accountNumber"><text ...></field>
The model forms take into account that partyId means maxsize 20, comments 255
and accountNumber 60 and the generation is changed accordingly. Properties are
cached through UtilProperties(UtilCache) so efficient.
The nice thing is that by basing max size on column id, not entity and column
id, maxsize value add is more or less transparent.
We only auto apply maxsize if form does not explicitly specify. in a small
number of cases it is better to override defaults by explicitly specifying.
e.g. crawler gave
carrierRestrictionDesc=2147483647
this is for
<field name="carrierRestrictionDesc" type="very-long"></field>
This is text type. One could do something more reasonable for Text types.. they
a small number in any case.
Attached is the file we used to generate. Feel free to use it as you see fit.
it generates FieldSize.properties (if there max size per column name is not
ambiguous) and FieldSizeAnomaly.properties for columns where data dictionary
has anomalous meaning.. (likely bugs)
Harmeet
----- Original Message -----
From: "Jacques Le Roux" <[email protected]>
To: [email protected]
Sent: Saturday, May 9, 2009 5:56:33 PM GMT -05:00 US/Canada Eastern
Subject: Re: Field max size based on DB field info
It seems that nothing happenned (no answers, etc.) after this message, please
why ?
Thanks
Jacques
From: "BJ Freeman" <[email protected]>
> Up till now I assumed the DB field info was fed back into the entities
> when field size as defined.
> questions:
> Does doing this break any models or design goals?
> Is there a reason this DB field info is not used in the ModelFormField.java
>
package com.emforium.tools;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import java.util.TreeSet;
// call this class to generate the db sizes of all the char/varchar strings.
// this size map is used inside html model forms to default to a maximum size
for text fields
public class GenDataDictionary {
public static void main(String[] args) throws Exception {
GenDataDictionary gen = new GenDataDictionary();
try {
gen.open();
gen.collectEntitiesAndFields();
} finally {
gen.done();
}
gen.writeConfig();
}
private Connection connection;
private DatabaseMetaData dbmeta;
// these are stored in primary location
private Map<String,Integer> fieldSize = new TreeMap<String,Integer>();
private Map<String,Integer> entityFieldSize = new
TreeMap<String,Integer>();
// these are stored as details
private Map<String,Set<String>> fieldEntitySizeSet = new
TreeMap<String,Set<String>>();
private Map<String,Set<Integer>> fieldSizeSet = new
TreeMap<String,Set<Integer>>();
void writeConfig() throws Exception {
PrintWriter conf = new
PrintWriter("./config/FieldSize.properties");
conf.println("# field and maximum size for the field");
writeMap(conf,"",fieldSize);
conf.flush();
conf.close();
PrintWriter entityFieldConf = new
PrintWriter("./config/EntityFieldSize.properties");
entityFieldConf.println("# entity.field to maximum size for the
field");
writeMap(entityFieldConf,"",entityFieldSize);
entityFieldConf.flush();
entityFieldConf.close();
if ( fieldSizeSet.size() > 0 || fieldEntitySizeSet.size() > 0 )
{
PrintWriter detailsConf = new
PrintWriter("./config/FieldSizeAnomaly.properties");
detailsConf.println("# field to list of sizes available
for field");
writeMap(detailsConf,"size.",fieldSizeSet);
detailsConf.println("# field to list of 'entity.size',
if size varies per entity");
writeMap(detailsConf,"entitysize.",fieldEntitySizeSet);
detailsConf.flush();
detailsConf.close();
}
}
private void writeMap(PrintWriter prt, String prefix, Map<String,?>
map) {
for ( Map.Entry<String, ?> entry : map.entrySet() ) {
String key = entry.getKey();
Object value = entry.getValue();
if ( value instanceof Iterable ) {
StringBuffer buf = null;
@SuppressWarnings("unchecked")
Iterator<Object> iter =
((Iterable)value).iterator();
while ( iter.hasNext() ) {
if ( buf == null )
buf = new StringBuffer();
else
buf.append(",");
buf.append(String.valueOf(iter.next()));
}
prt.println(prefix+key+"="+buf.toString());
} else {
prt.println(prefix+key+"="+String.valueOf(value));
}
}
}
void open() throws Exception {
//connection =
getConnection("org.postgresql.Driver","jdbc:postgresql://server1/ofbiz_hb_081122","postgres","postgres");
// TODO dmay: The server connection details shouldnt be hardcoded
here
connection =
getConnection("org.postgresql.Driver","jdbc:postgresql://127.0.0.1/ofbiz_local","ofbiz","ofbiz");
dbmeta = connection.getMetaData();
}
void done() throws Exception {
if ( connection != null ) {
connection.close();
connection = null;
}
}
Connection getConnection(String driver,String url, String userid, String
password) throws Exception {
Class.forName(driver);
return DriverManager.getConnection(url,userid,password);
}
// convert from db to ofbiz application names
private String dbNameToOfbizName(String table,boolean tableType) {
// convert to entity
StringBuffer buf = new StringBuffer();
boolean first = true;
for ( String part : table.split("_") ) {
if ( tableType || !first )
buf.append(Character.toUpperCase(part.charAt(0)));
else
buf.append(Character.toLowerCase(part.charAt(0)));
buf.append(part.substring(1).toLowerCase());
first = false;
}
return buf.toString();
}
// create map that holds a set of values
private <K,V>void addToMultiMap(Map<K,Set<V>> map,K key, V value) {
Set<V> set = map.get(key);
if ( set == null ) {
set = new TreeSet<V>();
map.put(key, set);
}
set.add(value);
}
// remove if map has a set with only single entry
private <K>void removeSingleEntry(Map<K,? extends Collection<?>>
map,Map<K,? extends Collection<?>> associatedMap) {
List<K> list = new ArrayList<K>();
for ( Map.Entry<K,? extends Collection<?>> entry : map.entrySet() ) {
if ( entry.getValue().size() <= 1 )
list.add(entry.getKey());
}
for ( K key : list ) {
map.remove(key);
associatedMap.remove(key);
}
}
void collectEntitiesAndFields() throws Exception {
List<String> tables = new ArrayList<String>();
ResultSet rs = dbmeta.getTables(null,null,null,new String[] { "TABLE"
});
while(rs.next()) {
tables.add(rs.getString("TABLE_NAME"));
}
rs.close();
for ( String table : tables ) {
String entity = dbNameToOfbizName(table,true);
rs = dbmeta.getColumns(null,null,table,null);
while (rs.next()) {
String colName = rs.getString("COLUMN_NAME");
int colType = rs.getInt("DATA_TYPE");
int colSize = rs.getInt("COLUMN_SIZE");
// care only about strings
if ( colType != Types.CHAR
&& colType != Types.VARCHAR
//&& colType != Types.LONGVARCHAR
)
{
continue;
}
String field = dbNameToOfbizName(colName,false);
entityFieldSize.put(entity+"."+field, colSize);
addToMultiMap(fieldEntitySizeSet,field,entity+":"+colSize);
addToMultiMap(fieldSizeSet,field,colSize);
}
rs.close();
}
// create max size map
for ( Map.Entry<String,Set<Integer>> entry : fieldSizeSet.entrySet() ) {
TreeSet<Integer> sizeSet = (TreeSet<Integer>)entry.getValue();
fieldSize.put(entry.getKey(), sizeSet.first());
}
// remove from 'fieldEntitySizeSet' and 'fieldSizeSet' if is only one
size per entity
removeSingleEntry(fieldSizeSet,fieldEntitySizeSet);
}
}
